-- Parche 060
-- Sincroniza registros academicos cuando un estudiante fue cambiado de aula/seccion.
-- Ejecutar una sola vez en el servidor despues de subir los archivos actualizados.
-- Es idempotente: no duplica notas ni reemplaza notas ya existentes en la matricula activa.

CREATE TABLE IF NOT EXISTS evaluacion_area_exoneracion (
  id_exoneracion BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_matricula INT UNSIGNED NOT NULL,
  id_area INT UNSIGNED NOT NULL,
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  motivo VARCHAR(220) NULL,
  fuente VARCHAR(40) NOT NULL DEFAULT 'MANUAL',
  estado TINYINT(1) NOT NULL DEFAULT 1,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_exoneracion),
  UNIQUE KEY uq_eval_exon_matricula_area_year (id_matricula, id_area, id_anio_lectivo),
  KEY idx_eval_exon_area_year (id_area, id_anio_lectivo, estado),
  KEY idx_eval_exon_matricula (id_matricula, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO nota_estudiante (
  id_matricula,
  id_periodo_academico,
  id_area,
  id_competencia,
  nota_literal,
  nota_numerica,
  conclusion_descriptiva,
  observacion,
  origen_registro,
  codigo_area_siagie,
  codigo_competencia_siagie,
  fecha_registro,
  fecha_actualizacion
)
SELECT
  vigente.id_matricula,
  n.id_periodo_academico,
  n.id_area,
  n.id_competencia,
  n.nota_literal,
  n.nota_numerica,
  n.conclusion_descriptiva,
  n.observacion,
  n.origen_registro,
  n.codigo_area_siagie,
  n.codigo_competencia_siagie,
  n.fecha_registro,
  n.fecha_actualizacion
FROM nota_estudiante n
INNER JOIN matricula anterior ON anterior.id_matricula = n.id_matricula
INNER JOIN (
  SELECT
    id_estudiante,
    id_anio_lectivo,
    MIN(id_matricula) AS id_matricula
  FROM matricula
  WHERE id_estado_matricula = 1
  GROUP BY id_estudiante, id_anio_lectivo
  HAVING COUNT(*) = 1
) vigente
  ON vigente.id_estudiante = anterior.id_estudiante
 AND vigente.id_anio_lectivo = anterior.id_anio_lectivo
WHERE n.id_matricula <> vigente.id_matricula
  AND NOT EXISTS (
    SELECT 1
    FROM nota_estudiante nx
    WHERE nx.id_matricula = vigente.id_matricula
      AND nx.id_periodo_academico = n.id_periodo_academico
      AND nx.id_area = n.id_area
      AND nx.id_competencia <=> n.id_competencia
  );

INSERT INTO estudiante_riesgo_academico (
  id_matricula,
  id_periodo_academico,
  cantidad_competencias_b,
  cantidad_competencias_c,
  total_competencias_evaluadas,
  nivel_riesgo,
  recomendacion
)
SELECT
  vigente.id_matricula,
  r.id_periodo_academico,
  r.cantidad_competencias_b,
  r.cantidad_competencias_c,
  r.total_competencias_evaluadas,
  r.nivel_riesgo,
  r.recomendacion
FROM estudiante_riesgo_academico r
INNER JOIN matricula anterior ON anterior.id_matricula = r.id_matricula
INNER JOIN (
  SELECT
    id_estudiante,
    id_anio_lectivo,
    MIN(id_matricula) AS id_matricula
  FROM matricula
  WHERE id_estado_matricula = 1
  GROUP BY id_estudiante, id_anio_lectivo
  HAVING COUNT(*) = 1
) vigente
  ON vigente.id_estudiante = anterior.id_estudiante
 AND vigente.id_anio_lectivo = anterior.id_anio_lectivo
WHERE r.id_matricula <> vigente.id_matricula
  AND NOT EXISTS (
    SELECT 1
    FROM estudiante_riesgo_academico rx
    WHERE rx.id_matricula = vigente.id_matricula
      AND rx.id_periodo_academico <=> r.id_periodo_academico
  );

INSERT INTO evaluacion_area_exoneracion (
  id_matricula,
  id_area,
  id_anio_lectivo,
  motivo,
  fuente,
  estado,
  creado_en,
  actualizado_en
)
SELECT
  vigente.id_matricula,
  ex.id_area,
  ex.id_anio_lectivo,
  ex.motivo,
  ex.fuente,
  ex.estado,
  ex.creado_en,
  ex.actualizado_en
FROM evaluacion_area_exoneracion ex
INNER JOIN matricula anterior ON anterior.id_matricula = ex.id_matricula
INNER JOIN (
  SELECT
    id_estudiante,
    id_anio_lectivo,
    MIN(id_matricula) AS id_matricula
  FROM matricula
  WHERE id_estado_matricula = 1
  GROUP BY id_estudiante, id_anio_lectivo
  HAVING COUNT(*) = 1
) vigente
  ON vigente.id_estudiante = anterior.id_estudiante
 AND vigente.id_anio_lectivo = anterior.id_anio_lectivo
WHERE ex.id_matricula <> vigente.id_matricula
  AND ex.estado = 1
  AND NOT EXISTS (
    SELECT 1
    FROM evaluacion_area_exoneracion ex2
    WHERE ex2.id_matricula = vigente.id_matricula
      AND ex2.id_area = ex.id_area
      AND ex2.id_anio_lectivo = ex.id_anio_lectivo
  );

INSERT IGNORE INTO nota_transversal_docente (
  id_anio_lectivo,
  id_periodo_academico,
  id_aula,
  id_docente,
  id_area_fuente,
  id_matricula,
  id_area_transversal,
  id_competencia_transversal,
  nota_literal,
  conclusion_descriptiva,
  observacion,
  creado_en,
  actualizado_en
)
SELECT
  nt.id_anio_lectivo,
  nt.id_periodo_academico,
  aula_vigente.id_aula,
  nt.id_docente,
  nt.id_area_fuente,
  vigente.id_matricula,
  nt.id_area_transversal,
  nt.id_competencia_transversal,
  nt.nota_literal,
  nt.conclusion_descriptiva,
  nt.observacion,
  nt.creado_en,
  nt.actualizado_en
FROM nota_transversal_docente nt
INNER JOIN matricula anterior ON anterior.id_matricula = nt.id_matricula
INNER JOIN (
  SELECT
    id_estudiante,
    id_anio_lectivo,
    MIN(id_matricula) AS id_matricula
  FROM matricula
  WHERE id_estado_matricula = 1
  GROUP BY id_estudiante, id_anio_lectivo
  HAVING COUNT(*) = 1
) vigente
  ON vigente.id_estudiante = anterior.id_estudiante
 AND vigente.id_anio_lectivo = anterior.id_anio_lectivo
INNER JOIN matricula aula_vigente ON aula_vigente.id_matricula = vigente.id_matricula
WHERE nt.id_matricula <> vigente.id_matricula
   OR nt.id_aula <> aula_vigente.id_aula;

DELETE nt
FROM nota_transversal_docente nt
INNER JOIN matricula anterior ON anterior.id_matricula = nt.id_matricula
INNER JOIN (
  SELECT
    id_estudiante,
    id_anio_lectivo,
    MIN(id_matricula) AS id_matricula
  FROM matricula
  WHERE id_estado_matricula = 1
  GROUP BY id_estudiante, id_anio_lectivo
  HAVING COUNT(*) = 1
) vigente
  ON vigente.id_estudiante = anterior.id_estudiante
 AND vigente.id_anio_lectivo = anterior.id_anio_lectivo
INNER JOIN matricula aula_vigente ON aula_vigente.id_matricula = vigente.id_matricula
INNER JOIN nota_transversal_docente nx
  ON nx.id_periodo_academico = nt.id_periodo_academico
 AND nx.id_aula = aula_vigente.id_aula
 AND nx.id_docente = nt.id_docente
 AND nx.id_area_fuente = nt.id_area_fuente
 AND nx.id_matricula = vigente.id_matricula
 AND nx.id_competencia_transversal = nt.id_competencia_transversal
WHERE (nt.id_matricula <> vigente.id_matricula OR nt.id_aula <> aula_vigente.id_aula)
  AND nx.id_nota_transversal <> nt.id_nota_transversal;