-- Parche 078
-- Registra la especialidad principal del docente mediante un catalogo normalizado.
-- El catalogo diferencia las areas que se repiten entre niveles y conserva el
-- codigo CNEB para poder relacionarlo con la carga pedagogica sin depender del
-- texto visible. El parche es idempotente y no reemplaza especialidades que ya
-- hayan sido confirmadas por un usuario.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS docente_especialidad_catalogo (
  id_especialidad_docente SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  codigo VARCHAR(100) NOT NULL,
  nombre VARCHAR(180) NOT NULL,
  id_nivel_referencial CHAR(3) NULL,
  codigo_area_cneb VARCHAR(80) NULL,
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  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_especialidad_docente),
  UNIQUE KEY uq_docente_especialidad_codigo (codigo),
  KEY idx_docente_especialidad_nivel_estado (id_nivel_referencial, estado, orden),
  CONSTRAINT fk_docente_especialidad_nivel
    FOREIGN KEY (id_nivel_referencial) REFERENCES nivel_educativo (id_nivel)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO docente_especialidad_catalogo (
  codigo,
  nombre,
  id_nivel_referencial,
  codigo_area_cneb,
  orden,
  estado,
  creado_en,
  actualizado_en
) VALUES
  ('INI_EDUCACION_INICIAL', 'Educación Inicial', 'INI', NULL, 10, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  ('PRI_EDUCACION_PRIMARIA', 'Educación Primaria', 'PRI', NULL, 20, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  ('SEC_GESTION_EDUCATIVA', 'Gestión educativa', 'SEC', NULL, 30, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  ('SEC_TUTORIA', 'Tutoría', 'SEC', 'TUTORIA', 40, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre),
  id_nivel_referencial = VALUES(id_nivel_referencial),
  codigo_area_cneb = VALUES(codigo_area_cneb),
  orden = VALUES(orden),
  estado = 1,
  actualizado_en = CURRENT_TIMESTAMP;

-- Cada area posee una fila por nivel. El prefijo evita mezclar, por ejemplo,
-- Educacion Fisica de Primaria con Educacion Fisica de Secundaria.
INSERT INTO docente_especialidad_catalogo (
  codigo,
  nombre,
  id_nivel_referencial,
  codigo_area_cneb,
  orden,
  estado,
  creado_en,
  actualizado_en
)
SELECT
  CONCAT(
    UPPER(TRIM(ac.id_nivel)),
    '_',
    REPLACE(UPPER(TRIM(ac.codigo_cneb)), ' ', '_')
  ) AS codigo,
  MAX(COALESCE(NULLIF(TRIM(ac.nombre_oficial), ''), NULLIF(TRIM(ac.nombre), ''), 'Área curricular')) AS nombre,
  ac.id_nivel AS id_nivel_referencial,
  REPLACE(UPPER(TRIM(ac.codigo_cneb)), ' ', '_') AS codigo_area_cneb,
  LEAST(65000, 100 + MIN(ac.id_area)) AS orden,
  1 AS estado,
  CURRENT_TIMESTAMP AS creado_en,
  CURRENT_TIMESTAMP AS actualizado_en
FROM area_curricular ac
WHERE ac.activo = 1
  AND COALESCE(ac.es_transversal, 0) = 0
  AND NULLIF(TRIM(ac.codigo_cneb), '') IS NOT NULL
GROUP BY
  ac.id_nivel,
  REPLACE(UPPER(TRIM(ac.codigo_cneb)), ' ', '_')
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre),
  id_nivel_referencial = VALUES(id_nivel_referencial),
  codigo_area_cneb = VALUES(codigo_area_cneb),
  orden = VALUES(orden),
  estado = 1,
  actualizado_en = CURRENT_TIMESTAMP;

-- MySQL y MariaDB no comparten la misma cobertura de ADD ... IF NOT EXISTS.
-- information_schema + PREPARE permite aplicar el parche en ambas variantes.
SET @comano_078_sql := (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `docente` ADD COLUMN `id_especialidad_docente` SMALLINT UNSIGNED NULL AFTER `id_nivel`',
    'DO 1'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'docente'
    AND COLUMN_NAME = 'id_especialidad_docente'
);
PREPARE comano_078_stmt FROM @comano_078_sql;
EXECUTE comano_078_stmt;
DEALLOCATE PREPARE comano_078_stmt;

SET @comano_078_sql := (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `docente` ADD INDEX `idx_docente_especialidad` (`id_especialidad_docente`)',
    'DO 1'
  )
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'docente'
    AND INDEX_NAME = 'idx_docente_especialidad'
);
PREPARE comano_078_stmt FROM @comano_078_sql;
EXECUTE comano_078_stmt;
DEALLOCATE PREPARE comano_078_stmt;

SET @comano_078_sql := (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `docente` ADD CONSTRAINT `fk_docente_especialidad` FOREIGN KEY (`id_especialidad_docente`) REFERENCES `docente_especialidad_catalogo` (`id_especialidad_docente`) ON UPDATE CASCADE ON DELETE RESTRICT',
    'DO 1'
  )
  FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'docente'
    AND COLUMN_NAME = 'id_especialidad_docente'
    AND REFERENCED_TABLE_NAME = 'docente_especialidad_catalogo'
    AND REFERENCED_COLUMN_NAME = 'id_especialidad_docente'
);
PREPARE comano_078_stmt FROM @comano_078_sql;
EXECUTE comano_078_stmt;
DEALLOCATE PREPARE comano_078_stmt;

-- Puntaje de las areas efectivamente asignadas en el mismo anio del docente.
-- Primero se consideran las horas; ante ausencia o empate se usa el numero de
-- asignaciones y finalmente el codigo para obtener siempre el mismo resultado.
DROP TEMPORARY TABLE IF EXISTS tmp_comano_078_area_puntaje;
CREATE TEMPORARY TABLE tmp_comano_078_area_puntaje (
  id_docente INT UNSIGNED NOT NULL,
  codigo_catalogo VARCHAR(100) NOT NULL,
  total_horas DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_asignaciones INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id_docente, codigo_catalogo)
) ENGINE=InnoDB;

INSERT INTO tmp_comano_078_area_puntaje (
  id_docente,
  codigo_catalogo,
  total_horas,
  total_asignaciones
)
SELECT
  d.id_docente,
  CONCAT(
    UPPER(TRIM(d.id_nivel)),
    '_',
    REPLACE(UPPER(TRIM(ac.codigo_cneb)), ' ', '_')
  ) AS codigo_catalogo,
  SUM(CASE WHEN dac.horas_semanales > 0 THEN dac.horas_semanales ELSE 0 END) AS total_horas,
  COUNT(*) AS total_asignaciones
FROM docente d
INNER JOIN docente_area_cargo dac
  ON dac.id_docente = d.id_docente
 AND dac.id_anio_lectivo = d.id_anio_lectivo
 AND dac.estado = 1
INNER JOIN area_curricular ac
  ON ac.id_area = dac.id_area
 AND ac.id_nivel = d.id_nivel
 AND ac.activo = 1
 AND COALESCE(ac.es_transversal, 0) = 0
WHERE NULLIF(TRIM(ac.codigo_cneb), '') IS NOT NULL
GROUP BY
  d.id_docente,
  d.id_nivel,
  REPLACE(UPPER(TRIM(ac.codigo_cneb)), ' ', '_');

DROP TEMPORARY TABLE IF EXISTS tmp_comano_078_area_dominante;
CREATE TEMPORARY TABLE tmp_comano_078_area_dominante (
  id_docente INT UNSIGNED NOT NULL,
  codigo_catalogo VARCHAR(100) NOT NULL,
  PRIMARY KEY (id_docente)
) ENGINE=InnoDB;

INSERT INTO tmp_comano_078_area_dominante (id_docente, codigo_catalogo)
SELECT
  candidato.id_docente,
  candidato.codigo_catalogo
FROM tmp_comano_078_area_puntaje candidato
WHERE NOT EXISTS (
  SELECT 1
  FROM tmp_comano_078_area_puntaje mejor
  WHERE mejor.id_docente = candidato.id_docente
    AND (
      mejor.total_horas > candidato.total_horas
      OR (
        mejor.total_horas = candidato.total_horas
        AND mejor.total_asignaciones > candidato.total_asignaciones
      )
      OR (
        mejor.total_horas = candidato.total_horas
        AND mejor.total_asignaciones = candidato.total_asignaciones
        AND mejor.codigo_catalogo < candidato.codigo_catalogo
      )
    )
);

-- Un director sin carga curricular se identifica como Gestion educativa.
UPDATE docente d
INNER JOIN docente_especialidad_catalogo especialidad
  ON especialidad.codigo = 'SEC_GESTION_EDUCATIVA'
 AND especialidad.estado = 1
SET d.id_especialidad_docente = especialidad.id_especialidad_docente
WHERE d.id_especialidad_docente IS NULL
  AND d.id_nivel = 'SEC'
  AND NOT EXISTS (
    SELECT 1
    FROM tmp_comano_078_area_puntaje puntaje
    WHERE puntaje.id_docente = d.id_docente
  )
  AND d.tipo_docente = 1;

-- En Primaria, una sola area evidencia una especialidad concreta (por ejemplo,
-- Educacion Fisica). Quienes cubren varias areas son docentes de Primaria.
UPDATE docente d
INNER JOIN (
  SELECT id_docente, MIN(codigo_catalogo) AS codigo_catalogo
  FROM tmp_comano_078_area_puntaje
  GROUP BY id_docente
  HAVING COUNT(*) = 1
) unica
  ON unica.id_docente = d.id_docente
INNER JOIN docente_especialidad_catalogo especialidad
  ON especialidad.codigo = unica.codigo_catalogo
 AND especialidad.estado = 1
SET d.id_especialidad_docente = especialidad.id_especialidad_docente
WHERE d.id_especialidad_docente IS NULL
  AND d.id_nivel = 'PRI';

UPDATE docente d
INNER JOIN docente_especialidad_catalogo especialidad
  ON especialidad.codigo = 'PRI_EDUCACION_PRIMARIA'
 AND especialidad.estado = 1
SET d.id_especialidad_docente = especialidad.id_especialidad_docente
WHERE d.id_especialidad_docente IS NULL
  AND d.id_nivel = 'PRI';

UPDATE docente d
INNER JOIN docente_especialidad_catalogo especialidad
  ON especialidad.codigo = 'INI_EDUCACION_INICIAL'
 AND especialidad.estado = 1
SET d.id_especialidad_docente = especialidad.id_especialidad_docente
WHERE d.id_especialidad_docente IS NULL
  AND d.id_nivel = 'INI';

-- En Secundaria se conserva como especialidad el area con mayor carga.
UPDATE docente d
INNER JOIN tmp_comano_078_area_dominante dominante
  ON dominante.id_docente = d.id_docente
INNER JOIN docente_especialidad_catalogo especialidad
  ON especialidad.codigo = dominante.codigo_catalogo
 AND especialidad.estado = 1
SET d.id_especialidad_docente = especialidad.id_especialidad_docente
WHERE d.id_especialidad_docente IS NULL
  AND d.id_nivel = 'SEC';

-- Si solo existe una asignacion de Tutoria sin area CNEB, se conserva esa
-- especialidad. No se aplica a docentes que ya recibieron otra clasificacion.
UPDATE docente d
INNER JOIN docente_especialidad_catalogo especialidad
  ON especialidad.codigo = 'SEC_TUTORIA'
 AND especialidad.estado = 1
SET d.id_especialidad_docente = especialidad.id_especialidad_docente
WHERE d.id_especialidad_docente IS NULL
  AND d.id_nivel = 'SEC'
  AND EXISTS (
    SELECT 1
    FROM docente_area_cargo dac
    INNER JOIN area_curricular ac ON ac.id_area = dac.id_area
    WHERE dac.id_docente = d.id_docente
      AND dac.id_anio_lectivo = d.id_anio_lectivo
      AND dac.estado = 1
      AND ac.id_nivel = 'SEC'
      AND UPPER(TRIM(COALESCE(ac.nombre_oficial, ac.nombre, ''))) LIKE 'TUTOR%'
  );

DROP TEMPORARY TABLE IF EXISTS tmp_comano_078_area_dominante;
DROP TEMPORARY TABLE IF EXISTS tmp_comano_078_area_puntaje;

SET @comano_078_sql := NULL;