START TRANSACTION;

UPDATE aula au
INNER JOIN docente_area_cargo da
        ON da.id_docente = au.id_docente_tutor
       AND da.id_anio_lectivo = au.id_anio_lectivo
       AND da.estado = 1
INNER JOIN area_curricular ar
        ON ar.id_area = da.id_area
SET au.id_docente_tutor = NULL
WHERE au.id_anio_lectivo = 2026
  AND au.id_nivel = 'PRI'
  AND au.estado = 1
  AND (
      UPPER(COALESCE(ar.codigo_cneb, '')) = 'EDUCACION_FISICA'
      OR UPPER(COALESCE(ar.nombre, '')) LIKE '%FISICA%'
  );

DELETE dt
FROM docente_tutoria dt
INNER JOIN docente_area_cargo da
        ON da.id_docente = dt.id_docente
       AND da.id_anio_lectivo = dt.id_anio_lectivo
       AND da.estado = 1
INNER JOIN aula au
        ON au.id_aula = da.id_aula
       AND au.id_nivel = 'PRI'
INNER JOIN area_curricular ar
        ON ar.id_area = da.id_area
WHERE dt.id_anio_lectivo = 2026
  AND dt.id_nivel = 'PRI'
  AND (
      UPPER(COALESCE(ar.codigo_cneb, '')) = 'EDUCACION_FISICA'
      OR UPPER(COALESCE(ar.nombre, '')) LIKE '%FISICA%'
  );

INSERT INTO docente_tutoria (
    id_docente,
    id_anio_lectivo,
    id_nivel,
    id_grado,
    id_seccion,
    turno,
    observaciones,
    estado
)
SELECT
    au.id_docente_tutor,
    au.id_anio_lectivo,
    au.id_nivel,
    au.id_grado,
    au.id_seccion,
    CONVERT(UNHEX('4D61C3B1616E61') USING utf8mb4),
    'Tutor asignado por regla primaria COMANO',
    1
FROM aula au
WHERE au.id_anio_lectivo = 2026
  AND au.id_nivel = 'PRI'
  AND au.estado = 1
  AND au.id_docente_tutor IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM docente_area_cargo da
      INNER JOIN area_curricular ar ON ar.id_area = da.id_area
      INNER JOIN aula au_area ON au_area.id_aula = da.id_aula
      WHERE da.id_docente = au.id_docente_tutor
        AND da.id_anio_lectivo = au.id_anio_lectivo
        AND da.estado = 1
        AND au_area.id_nivel = 'PRI'
        AND (
            UPPER(COALESCE(ar.codigo_cneb, '')) = 'EDUCACION_FISICA'
            OR UPPER(COALESCE(ar.nombre, '')) LIKE '%FISICA%'
        )
  )
ON DUPLICATE KEY UPDATE
    id_docente = VALUES(id_docente),
    turno = CONVERT(UNHEX('4D61C3B1616E61') USING utf8mb4),
    observaciones = VALUES(observaciones),
    estado = 1,
    actualizado_en = CURRENT_TIMESTAMP;

COMMIT;