USE akrcsist_bd0003_2026;

CREATE TABLE IF NOT EXISTS tipo_persona_ie (
  id_tipo_persona TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  codigo VARCHAR(30) NOT NULL,
  descripcion VARCHAR(100) NOT NULL,
  estado TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id_tipo_persona),
  UNIQUE KEY uq_tipo_persona_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO tipo_persona_ie (id_tipo_persona, codigo, descripcion, estado) VALUES
  (1, 'ESTUDIANTE', 'Estudiante', 1),
  (2, 'DOCENTE', 'Docente', 1),
  (3, 'ADMINISTRATIVO', 'Personal administrativo', 1),
  (4, 'PADRE_FAMILIA', 'Padre de familia', 1),
  (5, 'APODERADO', 'Apoderado', 1),
  (6, 'ESPECIALISTA_UGEL', 'Especialista de la UGEL', 1),
  (7, 'PARTICULAR', 'Persona particular', 1)
ON DUPLICATE KEY UPDATE descripcion = VALUES(descripcion), estado = VALUES(estado);

ALTER TABLE persona
  ADD COLUMN IF NOT EXISTS id_tipo_persona TINYINT UNSIGNED NULL AFTER id_persona,
  ADD KEY IF NOT EXISTS idx_persona_tipo_persona (id_tipo_persona);

ALTER TABLE persona
  ADD CONSTRAINT fk_persona_tipo_persona_ie FOREIGN KEY IF NOT EXISTS (id_tipo_persona) REFERENCES tipo_persona_ie(id_tipo_persona);

CREATE TABLE IF NOT EXISTS persona_tipo (
  id_persona INT UNSIGNED NOT NULL,
  id_tipo_persona TINYINT UNSIGNED NOT NULL,
  observacion VARCHAR(180) NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id_persona, id_tipo_persona),
  KEY idx_persona_tipo_tipo (id_tipo_persona),
  CONSTRAINT fk_persona_tipo_persona FOREIGN KEY (id_persona) REFERENCES persona(id_persona),
  CONSTRAINT fk_persona_tipo_tipo FOREIGN KEY (id_tipo_persona) REFERENCES tipo_persona_ie(id_tipo_persona)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO persona_tipo (id_persona, id_tipo_persona, observacion)
SELECT e.id_persona, 1, 'Clasificacion automatica desde estudiante'
FROM estudiante e;

INSERT IGNORE INTO persona_tipo (id_persona, id_tipo_persona, observacion)
SELECT d.id_persona, 2, 'Clasificacion automatica desde docente'
FROM docente d;

INSERT IGNORE INTO persona_tipo (id_persona, id_tipo_persona, observacion)
SELECT DISTINCT f.id_persona_familiar, IF(MAX(f.es_apoderado) = 1, 5, 4), 'Clasificacion automatica desde familia'
FROM familiar_estudiante f
GROUP BY f.id_persona_familiar;

UPDATE persona p
LEFT JOIN estudiante e ON e.id_persona = p.id_persona
LEFT JOIN docente d ON d.id_persona = p.id_persona
LEFT JOIN (
  SELECT id_persona_familiar, MAX(es_apoderado) es_apoderado
  FROM familiar_estudiante
  GROUP BY id_persona_familiar
) fam ON fam.id_persona_familiar = p.id_persona
SET p.id_tipo_persona =
  CASE
    WHEN e.id_persona IS NOT NULL THEN 1
    WHEN d.id_persona IS NOT NULL THEN 2
    WHEN fam.id_persona_familiar IS NOT NULL AND fam.es_apoderado = 1 THEN 5
    WHEN fam.id_persona_familiar IS NOT NULL THEN 4
    ELSE COALESCE(p.id_tipo_persona, 7)
  END
WHERE p.id_tipo_persona IS NULL;

ALTER TABLE matricula
  ADD COLUMN IF NOT EXISTS tipo_matricula_comano ENUM('CONTINUIDAD','NUEVO_CAMBIO_NIVEL','TRASLADO_MISMO_ANIO','TRASLADO_CAMBIO_ANIO') NOT NULL DEFAULT 'CONTINUIDAD' AFTER cod_tipo_matricula,
  ADD COLUMN IF NOT EXISTS institucion_origen VARCHAR(180) NULL AFTER tipo_matricula_comano,
  ADD COLUMN IF NOT EXISTS codigo_modular_origen VARCHAR(20) NULL AFTER institucion_origen,
  ADD COLUMN IF NOT EXISTS fecha_traslado DATE NULL AFTER codigo_modular_origen,
  ADD COLUMN IF NOT EXISTS documento_sustento VARCHAR(180) NULL AFTER fecha_traslado,
  ADD KEY IF NOT EXISTS idx_matricula_tipo_comano (tipo_matricula_comano);

ALTER TABLE nota_estudiante
  MODIFY COLUMN observacion TEXT NULL,
  ADD COLUMN IF NOT EXISTS conclusion_descriptiva TEXT NULL AFTER nota_numerica,
  ADD COLUMN IF NOT EXISTS origen_registro ENUM('MANUAL','SIAGIE','MIGRACION') NOT NULL DEFAULT 'MANUAL' AFTER conclusion_descriptiva,
  ADD COLUMN IF NOT EXISTS codigo_area_siagie VARCHAR(30) NULL AFTER origen_registro,
  ADD COLUMN IF NOT EXISTS codigo_competencia_siagie VARCHAR(30) NULL AFTER codigo_area_siagie;

CREATE TABLE IF NOT EXISTS siagie_importacion (
  id_siagie_importacion INT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_periodo_academico SMALLINT UNSIGNED NULL,
  id_aula INT UNSIGNED NULL,
  nivel_codigo CHAR(3) NULL,
  grado_texto VARCHAR(40) NULL,
  seccion_texto VARCHAR(20) NULL,
  archivo_nombre VARCHAR(220) NOT NULL,
  archivo_ruta VARCHAR(320) NULL,
  total_filas INT UNSIGNED NOT NULL DEFAULT 0,
  total_notas INT UNSIGNED NOT NULL DEFAULT 0,
  total_insertadas INT UNSIGNED NOT NULL DEFAULT 0,
  total_actualizadas INT UNSIGNED NOT NULL DEFAULT 0,
  total_observadas INT UNSIGNED NOT NULL DEFAULT 0,
  estado ENUM('PROCESADO','PROCESADO_CON_OBSERVACIONES','ERROR') NOT NULL DEFAULT 'PROCESADO',
  resumen TEXT NULL,
  id_usuario INT UNSIGNED NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id_siagie_importacion),
  KEY idx_siagie_importacion_anio_periodo (id_anio_lectivo, id_periodo_academico),
  KEY idx_siagie_importacion_aula (id_aula),
  CONSTRAINT fk_siagie_importacion_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo),
  CONSTRAINT fk_siagie_importacion_periodo FOREIGN KEY (id_periodo_academico) REFERENCES periodo_academico(id_periodo_academico),
  CONSTRAINT fk_siagie_importacion_aula FOREIGN KEY (id_aula) REFERENCES aula(id_aula),
  CONSTRAINT fk_siagie_importacion_usuario FOREIGN KEY (id_usuario) REFERENCES usuario_sistema(id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS siagie_importacion_detalle (
  id_siagie_detalle BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_siagie_importacion INT UNSIGNED NOT NULL,
  hoja VARCHAR(100) NOT NULL,
  fila_excel INT UNSIGNED NULL,
  dni VARCHAR(20) NULL,
  codigo_estudiante_siagie VARCHAR(30) NULL,
  estudiante VARCHAR(260) NULL,
  area_nombre VARCHAR(180) NULL,
  competencia_codigo VARCHAR(30) NULL,
  competencia_nombre VARCHAR(300) NULL,
  nota_literal VARCHAR(5) NULL,
  conclusion_descriptiva TEXT NULL,
  estado ENUM('IMPORTADO','ACTUALIZADO','OBSERVADO') NOT NULL DEFAULT 'IMPORTADO',
  observacion VARCHAR(300) NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id_siagie_detalle),
  KEY idx_siagie_det_importacion (id_siagie_importacion),
  KEY idx_siagie_det_dni (dni),
  CONSTRAINT fk_siagie_det_importacion FOREIGN KEY (id_siagie_importacion) REFERENCES siagie_importacion(id_siagie_importacion) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS asistencia_personal_ie (
  id_asistencia_personal BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  fecha DATE NOT NULL,
  id_persona INT UNSIGNED NOT NULL,
  tipo_personal ENUM('DOCENTE','ADMINISTRATIVO','OTRO') NOT NULL DEFAULT 'DOCENTE',
  hora_ingreso TIME NULL,
  hora_salida TIME NULL,
  estado ENUM('PUNTUAL','TARDANZA','FALTA','PERMISO','JUSTIFICADO') NOT NULL DEFAULT 'PUNTUAL',
  observacion VARCHAR(260) NULL,
  registrado_por INT UNSIGNED NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_asistencia_personal),
  UNIQUE KEY uq_asistencia_personal_fecha (fecha, id_persona),
  KEY idx_asistencia_personal_estado (fecha, estado),
  CONSTRAINT fk_asistencia_personal_persona FOREIGN KEY (id_persona) REFERENCES persona(id_persona),
  CONSTRAINT fk_asistencia_personal_usuario FOREIGN KEY (registrado_por) REFERENCES usuario_sistema(id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg31 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  meta_prevista_aprobacion_ugel TINYINT UNSIGNED NULL,
  meta_lograda_aprobacion_ugel TINYINT UNSIGNED NULL,
  meta_prevista_aprobacion_ie TINYINT UNSIGNED NULL,
  meta_lograda_aprobacion_ie TINYINT UNSIGNED NULL,
  meta_prevista_publicacion TINYINT UNSIGNED NULL,
  meta_lograda_publicacion TINYINT UNSIGNED NULL,
  meta_prevista_semanas_lectivas SMALLINT UNSIGNED NULL,
  meta_lograda_semanas_lectivas SMALLINT UNSIGNED NULL,
  meta_prevista_semanas_gestion SMALLINT UNSIGNED NULL,
  meta_lograda_semanas_gestion SMALLINT UNSIGNED NULL,
  meta_prevista_semanas_laborables SMALLINT UNSIGNED NULL,
  meta_lograda_semanas_laborables SMALLINT UNSIGNED NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg31_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg32 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  prevista_plan_matricula TINYINT(1) NULL,
  lograda_plan_matricula TINYINT(1) NULL,
  prevista_publicacion_vacantes TINYINT(1) NULL,
  lograda_publicacion_vacantes TINYINT(1) NULL,
  prevista_registro_siagie TINYINT(1) NULL,
  lograda_registro_siagie TINYINT(1) NULL,
  prevista_nomina_regular TINYINT(1) NULL,
  lograda_nomina_regular TINYINT(1) NULL,
  prevista_nomina_adicional TINYINT(1) NULL,
  lograda_nomina_adicional TINYINT(1) NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg32_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg41 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg41_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg42 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg42_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg43 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg43_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg44 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg44_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg45 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg45_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg51 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg51_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg52 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg52_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg53 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg53_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg54 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg54_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg55 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg55_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg36 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg36_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg35 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg35_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg34 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  valores_json LONGTEXT NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo),
  CONSTRAINT fk_pg34_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_pg33 (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  modalidad_secundaria VARCHAR(3) NOT NULL DEFAULT 'JER',
  meta_prevista_personal BIGINT UNSIGNED NULL,
  meta_lograda_personal BIGINT UNSIGNED NULL,
  meta_prevista_reportes BIGINT UNSIGNED NULL,
  meta_lograda_reportes BIGINT UNSIGNED NULL,
  meta_prevista_inicial BIGINT UNSIGNED NULL,
  meta_lograda_inicial BIGINT UNSIGNED NULL,
  meta_prevista_primaria BIGINT UNSIGNED NULL,
  meta_lograda_primaria BIGINT UNSIGNED NULL,
  meta_prevista_secundaria_jer BIGINT UNSIGNED NULL,
  meta_lograda_secundaria_jer BIGINT UNSIGNED NULL,
  meta_prevista_secundaria_jec BIGINT UNSIGNED NULL,
  meta_lograda_secundaria_jec BIGINT UNSIGNED NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo, modalidad_secundaria),
  CONSTRAINT fk_pg33_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_indicador_analisis (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  indicador_codigo VARCHAR(12) NOT NULL,
  id_nivel VARCHAR(3) NOT NULL,
  principales_logros MEDIUMTEXT NULL,
  principales_dificultades MEDIUMTEXT NULL,
  acciones_mejora MEDIUMTEXT NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo, indicador_codigo, id_nivel),
  CONSTRAINT fk_indicador_analisis_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_is13_resultado (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_nivel VARCHAR(3) NOT NULL,
  grado TINYINT UNSIGNED NOT NULL,
  area_codigo VARCHAR(30) NOT NULL,
  anio_1 DECIMAL(5,2) NULL,
  anio_2 DECIMAL(5,2) NULL,
  anio_3 DECIMAL(5,2) NULL,
  anio_4 DECIMAL(5,2) NULL,
  meta DECIMAL(5,2) NULL,
  logrado DECIMAL(5,2) NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo, id_nivel, grado, area_codigo),
  KEY idx_is13_resultado_nivel_grado (id_anio_lectivo, id_nivel, grado),
  CONSTRAINT fk_is13_resultado_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ie_compromiso_gestion_is14_resultado (
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_nivel VARCHAR(3) NOT NULL,
  grado TINYINT UNSIGNED NOT NULL,
  area_codigo VARCHAR(30) NOT NULL,
  anio_1 DECIMAL(5,2) NULL,
  anio_2 DECIMAL(5,2) NULL,
  anio_3 DECIMAL(5,2) NULL,
  anio_4 DECIMAL(5,2) NULL,
  meta DECIMAL(5,2) NULL,
  logrado DECIMAL(5,2) NULL,
  actualizado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_anio_lectivo, id_nivel, grado, area_codigo),
  KEY idx_is14_resultado_nivel_grado (id_anio_lectivo, id_nivel, grado),
  CONSTRAINT fk_is14_resultado_anio FOREIGN KEY (id_anio_lectivo)
    REFERENCES anio_lectivo(id_anio_lectivo)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS estudiante_riesgo_academico (
  id_riesgo BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_matricula INT UNSIGNED NOT NULL,
  id_periodo_academico SMALLINT UNSIGNED NULL,
  cantidad_competencias_b TINYINT UNSIGNED NOT NULL DEFAULT 0,
  cantidad_competencias_c TINYINT UNSIGNED NOT NULL DEFAULT 0,
  total_competencias_evaluadas SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  nivel_riesgo ENUM('BAJO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'BAJO',
  recomendacion TEXT NULL,
  actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_riesgo),
  UNIQUE KEY uq_riesgo_matricula_periodo (id_matricula, id_periodo_academico),
  KEY idx_riesgo_periodo_nivel (id_periodo_academico, nivel_riesgo),
  CONSTRAINT fk_riesgo_matricula FOREIGN KEY (id_matricula) REFERENCES matricula(id_matricula),
  CONSTRAINT fk_riesgo_periodo FOREIGN KEY (id_periodo_academico) REFERENCES periodo_academico(id_periodo_academico)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW vw_comano_personas AS
SELECT
  p.id_persona,
  p.id_tipo_persona,
  tp.codigo AS tipo_codigo,
  tp.descripcion AS tipo_persona,
  td.descripcion AS tipo_documento,
  p.numero_documento,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS nombre_completo,
  p.apellido_paterno,
  p.apellido_materno,
  p.nombres,
  p.id_sexo,
  p.fecha_nacimiento,
  p.celular,
  p.correo,
  p.direccion
FROM persona p
LEFT JOIN tipo_persona_ie tp ON tp.id_tipo_persona = p.id_tipo_persona
LEFT JOIN tipo_documento td ON td.id_tipo_documento = p.id_tipo_documento;

CREATE OR REPLACE VIEW vw_comano_matriculados AS
SELECT
  m.id_matricula,
  m.id_anio_lectivo,
  al.anio,
  a.id_nivel,
  ne.descripcion AS nivel,
  a.id_grado,
  g.nombre_corto AS grado,
  g.descripcion AS grado_descripcion,
  a.id_seccion,
  s.nombre_corto AS seccion,
  e.id_estudiante,
  e.codigo_estudiante_siagie AS codigo_estudiante,
  p.numero_documento AS dni,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  m.id_estado_matricula,
  em.descripcion AS estado_matricula,
  m.tipo_matricula_comano,
  ap.numero_documento AS dni_apoderado,
  COALESCE(NULLIF(ap.nombre_completo, ''), CONCAT_WS(' ', ap.apellido_paterno, ap.apellido_materno, ap.nombres)) AS apoderado,
  m.fecha_matricula
FROM matricula m
INNER JOIN anio_lectivo al ON al.id_anio_lectivo = m.id_anio_lectivo
INNER JOIN aula a ON a.id_aula = m.id_aula
INNER JOIN nivel_educativo ne ON ne.id_nivel = a.id_nivel
INNER JOIN grado g ON g.id_grado = a.id_grado
INNER JOIN seccion s ON s.id_seccion = a.id_seccion
INNER JOIN estado_matricula em ON em.id_estado_matricula = m.id_estado_matricula
INNER JOIN estudiante e ON e.id_estudiante = m.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN persona ap ON ap.id_persona = m.id_apoderado;

CREATE OR REPLACE VIEW vw_comano_notas_detalle AS
SELECT
  n.id_nota,
  n.id_matricula,
  m.id_anio_lectivo,
  pa.id_periodo_academico,
  pa.descripcion AS periodo,
  aul.id_nivel,
  ne.descripcion AS nivel,
  g.nombre_corto AS grado,
  s.nombre_corto AS seccion,
  p.numero_documento AS dni,
  e.codigo_estudiante_siagie AS codigo_estudiante,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  ar.nombre AS area,
  ar.codigo_cneb AS area_codigo,
  c.descripcion AS competencia,
  c.codigo_cneb AS competencia_codigo,
  n.nota_literal,
  n.conclusion_descriptiva,
  n.observacion,
  n.origen_registro
FROM nota_estudiante n
INNER JOIN matricula m ON m.id_matricula = n.id_matricula
INNER JOIN aula aul ON aul.id_aula = m.id_aula
INNER JOIN nivel_educativo ne ON ne.id_nivel = aul.id_nivel
INNER JOIN grado g ON g.id_grado = aul.id_grado
INNER JOIN seccion s ON s.id_seccion = aul.id_seccion
INNER JOIN estudiante e ON e.id_estudiante = m.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
INNER JOIN periodo_academico pa ON pa.id_periodo_academico = n.id_periodo_academico
INNER JOIN area_curricular ar ON ar.id_area = n.id_area
LEFT JOIN competencia c ON c.id_competencia = n.id_competencia;

CREATE OR REPLACE VIEW vw_comano_riesgo_academico AS
SELECT
  m.id_matricula,
  m.id_anio_lectivo,
  pa.id_periodo_academico,
  pa.descripcion AS periodo,
  a.id_nivel,
  ne.descripcion AS nivel,
  g.nombre_corto AS grado,
  s.nombre_corto AS seccion,
  p.numero_documento AS dni,
  e.codigo_estudiante_siagie AS codigo_estudiante,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  SUM(n.nota_literal = 'B') AS competencias_b,
  SUM(n.nota_literal = 'C') AS competencias_c,
  COUNT(n.id_nota) AS total_competencias,
  CASE
    WHEN SUM(n.nota_literal = 'C') >= 4 OR (SUM(n.nota_literal = 'C') >= 2 AND SUM(n.nota_literal = 'B') >= 4) THEN 'CRITICO'
    WHEN SUM(n.nota_literal = 'C') >= 2 OR SUM(n.nota_literal = 'B') >= 6 THEN 'ALTO'
    WHEN SUM(n.nota_literal = 'C') = 1 OR SUM(n.nota_literal = 'B') >= 3 THEN 'MEDIO'
    ELSE 'BAJO'
  END AS nivel_riesgo
FROM matricula m
INNER JOIN aula a ON a.id_aula = m.id_aula
INNER JOIN nivel_educativo ne ON ne.id_nivel = a.id_nivel
INNER JOIN grado g ON g.id_grado = a.id_grado
INNER JOIN seccion s ON s.id_seccion = a.id_seccion
INNER JOIN estudiante e ON e.id_estudiante = m.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
INNER JOIN nota_estudiante n ON n.id_matricula = m.id_matricula
INNER JOIN periodo_academico pa ON pa.id_periodo_academico = n.id_periodo_academico
GROUP BY m.id_matricula, pa.id_periodo_academico;

CREATE OR REPLACE VIEW vw_comano_asistencia_personal AS
SELECT
  ap.id_asistencia_personal,
  ap.fecha,
  ap.tipo_personal,
  ap.estado,
  ap.hora_ingreso,
  ap.hora_salida,
  p.numero_documento AS dni,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS persona,
  ap.observacion
FROM asistencia_personal_ie ap
INNER JOIN persona p ON p.id_persona = ap.id_persona;