/* ============================================================================
   COMANO 0003 - Mejora de convivencia por Oficio Circular N. 092-2025
   Fuente: C:\Users\Alvert\Downloads\mejora_bd_convivencia_ie0003_oficio092.sql
   Integracion: akrcsist_bd0003_2026, tablas conv_* y datos reales del sistema.
   Objetivo: registrar, valorar y reportar estudiantes en riesgo, desproteccion,
             convivencia critica y solicitudes externas UGEL / Ministerio Publico.
   ============================================================================ */

USE akrcsist_bd0003_2026;
SET NAMES utf8mb4;
SET time_zone = '-05:00';

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_comano_add_column_if_not_exists $$
CREATE PROCEDURE sp_comano_add_column_if_not_exists(
  IN p_table_name VARCHAR(128),
  IN p_column_name VARCHAR(128),
  IN p_column_definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table_name
      AND COLUMN_NAME = p_column_name
  ) THEN
    SET @sql_add_column = CONCAT('ALTER TABLE `', p_table_name, '` ADD COLUMN ', p_column_definition);
    PREPARE stmt_add_column FROM @sql_add_column;
    EXECUTE stmt_add_column;
    DEALLOCATE PREPARE stmt_add_column;
  END IF;
END $$

DELIMITER ;

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'fecha_hecho',
  "`fecha_hecho` DATE NULL AFTER `fecha_apertura`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'prioridad',
  "`prioridad` ENUM('BAJA','MEDIA','ALTA','CRITICA') NOT NULL DEFAULT 'MEDIA' AFTER `estado`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'fuente_deteccion',
  "`fuente_deteccion` ENUM('DOCENTE','TUTOR','DIRECCION','FAMILIA','ESTUDIANTE','ALIADO','UGEL','MINISTERIO_PUBLICO','OTRO') NULL AFTER `prioridad`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'nivel_proteccion',
  "`nivel_proteccion` ENUM('CONVIVENCIA','RIESGO_DESPROTECCION','DESPROTECCION_FAMILIAR','VIOLENCIA','SALUD_MENTAL','URGENCIA') NOT NULL DEFAULT 'CONVIVENCIA' AFTER `fuente_deteccion`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'nivel_confidencialidad',
  "`nivel_confidencialidad` ENUM('NORMAL','RESERVADO','ESTRICTO') NOT NULL DEFAULT 'RESERVADO' AFTER `nivel_proteccion`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'requiere_reporte_externo',
  "`requiere_reporte_externo` TINYINT(1) NOT NULL DEFAULT 0 AFTER `nivel_confidencialidad`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'fecha_comunicacion_familia',
  "`fecha_comunicacion_familia` DATE NULL AFTER `requiere_reporte_externo`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'fecha_notificacion_comite',
  "`fecha_notificacion_comite` DATE NULL AFTER `fecha_comunicacion_familia`"
);

CALL sp_comano_add_column_if_not_exists(
  'conv_expediente_convivencia',
  'descripcion_proteccion',
  "`descripcion_proteccion` TEXT NULL AFTER `fecha_notificacion_comite`"
);

DROP PROCEDURE IF EXISTS sp_comano_add_column_if_not_exists;

CREATE TABLE IF NOT EXISTS conv_documento_externo (
  documento_externo_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  institucion_id INT UNSIGNED NOT NULL,
  tipo_documento ENUM('OFICIO_CIRCULAR','OFICIO','DIRECTIVA','MEMORANDO','OTRO') NOT NULL DEFAULT 'OFICIO',
  numero_documento VARCHAR(120) NOT NULL,
  fecha_documento DATE NULL,
  entidad_emisora VARCHAR(180) NOT NULL,
  entidad_referenciada VARCHAR(180) NULL,
  asunto VARCHAR(250) NOT NULL,
  referencia VARCHAR(200) NULL,
  resumen TEXT NULL,
  ruta_archivo VARCHAR(255) NULL,
  codigo_verificacion VARCHAR(150) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_doc_ext_ie FOREIGN KEY (institucion_id) REFERENCES conv_institucion_educativa(institucion_id),
  UNIQUE KEY conv_uk_doc_ext_numero_entidad (numero_documento, entidad_emisora)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_solicitud_reporte_proteccion (
  solicitud_reporte_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  documento_externo_id INT UNSIGNED NOT NULL,
  institucion_id INT UNSIGNED NOT NULL,
  codigo_solicitud VARCHAR(80) NOT NULL UNIQUE,
  fecha_solicitud DATE NOT NULL,
  entidad_solicitante VARCHAR(180) NOT NULL,
  autoridad_origen VARCHAR(180) NULL,
  fundamento_legal TEXT NULL,
  descripcion_solicitud TEXT NOT NULL,
  datos_solicitados TEXT NOT NULL,
  finalidad VARCHAR(250) NOT NULL,
  estado ENUM('VIGENTE','ATENDIDA','VENCIDA','ARCHIVADA') NOT NULL DEFAULT 'VIGENTE',
  observacion TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_srp_doc_ext FOREIGN KEY (documento_externo_id) REFERENCES conv_documento_externo(documento_externo_id),
  CONSTRAINT conv_fk_srp_ie FOREIGN KEY (institucion_id) REFERENCES conv_institucion_educativa(institucion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_periodo_reporte_proteccion (
  periodo_reporte_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  solicitud_reporte_id INT UNSIGNED NOT NULL,
  numero_reporte TINYINT UNSIGNED NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  fecha_inicio DATE NOT NULL,
  fecha_fin DATE NOT NULL,
  fecha_limite_envio DATE NOT NULL,
  descripcion VARCHAR(255) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT conv_fk_prp_solicitud FOREIGN KEY (solicitud_reporte_id) REFERENCES conv_solicitud_reporte_proteccion(solicitud_reporte_id),
  UNIQUE KEY conv_uk_prp_solicitud_numero (solicitud_reporte_id, numero_reporte)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_categoria_proteccion_estudiante (
  categoria_proteccion_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(80) NOT NULL UNIQUE,
  literal CHAR(1) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  descripcion TEXT NULL,
  tipo_caso ENUM('VIOLENCIA','ASISTENCIA','ACADEMICO','SALUD','SALUD_MENTAL','FAMILIAR','CONDUCTUAL','DERECHOS','OTRO') NOT NULL DEFAULT 'OTRO',
  nivel_alerta ENUM('BAJO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  requiere_derivacion TINYINT(1) NOT NULL DEFAULT 0,
  requiere_reserva TINYINT(1) NOT NULL DEFAULT 1,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  orden SMALLINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_supuesto_riesgo_desproteccion (
  supuesto_riesgo_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(80) NOT NULL UNIQUE,
  nombre VARCHAR(220) NOT NULL,
  descripcion TEXT NOT NULL,
  nivel_referencia ENUM('RIESGO_LEVE','RIESGO_MODERADO','RIESGO_ALTO') NOT NULL DEFAULT 'RIESGO_MODERADO',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  orden SMALLINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_categoria_proteccion_norma (
  categoria_proteccion_id SMALLINT UNSIGNED NOT NULL,
  norma_id INT UNSIGNED NOT NULL,
  observacion VARCHAR(255) NULL,
  PRIMARY KEY (categoria_proteccion_id, norma_id),
  CONSTRAINT conv_fk_cpn_categoria FOREIGN KEY (categoria_proteccion_id) REFERENCES conv_categoria_proteccion_estudiante(categoria_proteccion_id),
  CONSTRAINT conv_fk_cpn_norma FOREIGN KEY (norma_id) REFERENCES conv_norma_convivencia(norma_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_categoria_proteccion_situacion (
  categoria_proteccion_id SMALLINT UNSIGNED NOT NULL,
  situacion_id INT UNSIGNED NOT NULL,
  observacion VARCHAR(255) NULL,
  PRIMARY KEY (categoria_proteccion_id, situacion_id),
  CONSTRAINT conv_fk_cps_categoria FOREIGN KEY (categoria_proteccion_id) REFERENCES conv_categoria_proteccion_estudiante(categoria_proteccion_id),
  CONSTRAINT conv_fk_cps_situacion FOREIGN KEY (situacion_id) REFERENCES conv_situacion_acta(situacion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_expediente_categoria_proteccion (
  expediente_categoria_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expediente_id INT UNSIGNED NOT NULL,
  categoria_proteccion_id SMALLINT UNSIGNED NOT NULL,
  fecha_deteccion DATE NOT NULL,
  nivel_valoracion ENUM('BAJO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  requiere_reporte_ugel TINYINT(1) NOT NULL DEFAULT 1,
  requiere_reporte_ministerio_publico TINYINT(1) NOT NULL DEFAULT 0,
  descripcion_valoracion TEXT NULL,
  registrado_por_persona_id INT UNSIGNED NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_ecp_exp FOREIGN KEY (expediente_id) REFERENCES conv_expediente_convivencia(expediente_id),
  CONSTRAINT conv_fk_ecp_cat FOREIGN KEY (categoria_proteccion_id) REFERENCES conv_categoria_proteccion_estudiante(categoria_proteccion_id),
  CONSTRAINT conv_fk_ecp_reg FOREIGN KEY (registrado_por_persona_id) REFERENCES persona(id_persona),
  UNIQUE KEY conv_uk_ecp_exp_cat (expediente_id, categoria_proteccion_id),
  INDEX conv_idx_ecp_cat_nivel (categoria_proteccion_id, nivel_valoracion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_expediente_supuesto_riesgo (
  expediente_supuesto_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expediente_id INT UNSIGNED NOT NULL,
  supuesto_riesgo_id SMALLINT UNSIGNED NOT NULL,
  fecha_identificacion DATE NOT NULL,
  observacion TEXT NULL,
  CONSTRAINT conv_fk_esr_exp FOREIGN KEY (expediente_id) REFERENCES conv_expediente_convivencia(expediente_id),
  CONSTRAINT conv_fk_esr_sup FOREIGN KEY (supuesto_riesgo_id) REFERENCES conv_supuesto_riesgo_desproteccion(supuesto_riesgo_id),
  UNIQUE KEY conv_uk_esr_exp_sup (expediente_id, supuesto_riesgo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_citacion_familia (
  citacion_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expediente_id INT UNSIGNED NULL,
  id_estudiante INT UNSIGNED NOT NULL,
  fecha_emision DATE NOT NULL,
  fecha_programada DATETIME NOT NULL,
  tipo_citacion ENUM('ACADEMICA','CONVIVENCIA','TUTORIA','DIRECCION','COMITE_BIENESTAR','PROTECCION','OTRO') NOT NULL DEFAULT 'CONVIVENCIA',
  motivo TEXT NOT NULL,
  medio_notificacion ENUM('ESQUELA','TELEFONO','WHATSAPP','CORREO','PRESENCIAL','OTRO') NOT NULL DEFAULT 'ESQUELA',
  estado ENUM('PROGRAMADA','ASISTIO','NO_ASISTIO_JUSTIFICADO','NO_ASISTIO_INJUSTIFICADO','REPROGRAMADA','CANCELADA') NOT NULL DEFAULT 'PROGRAMADA',
  justificacion_inasistencia TEXT NULL,
  responsable_id_persona INT UNSIGNED NULL,
  observacion TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_cf_exp FOREIGN KEY (expediente_id) REFERENCES conv_expediente_convivencia(expediente_id),
  CONSTRAINT conv_fk_cf_estudiante FOREIGN KEY (id_estudiante) REFERENCES estudiante(id_estudiante),
  CONSTRAINT conv_fk_cf_responsable FOREIGN KEY (responsable_id_persona) REFERENCES persona(id_persona),
  INDEX conv_idx_cf_est_fecha (id_estudiante, fecha_programada),
  INDEX conv_idx_cf_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_citacion_familia_participante (
  citacion_participante_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  citacion_id BIGINT UNSIGNED NOT NULL,
  id_persona INT UNSIGNED NULL,
  condicion ENUM('PROGENITOR','APODERADO','TUTOR_LEGAL','OTRO') NOT NULL DEFAULT 'APODERADO',
  asistio TINYINT(1) NOT NULL DEFAULT 0,
  justifico TINYINT(1) NOT NULL DEFAULT 0,
  observacion VARCHAR(255) NULL,
  CONSTRAINT conv_fk_cfp_citacion FOREIGN KEY (citacion_id) REFERENCES conv_citacion_familia(citacion_id),
  CONSTRAINT conv_fk_cfp_persona FOREIGN KEY (id_persona) REFERENCES persona(id_persona),
  INDEX conv_idx_cfp_citacion (citacion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_accion_proteccion_estudiante (
  accion_proteccion_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expediente_id INT UNSIGNED NOT NULL,
  fecha_accion DATETIME NOT NULL,
  tipo_accion ENUM('DIALOGO_ESTUDIANTE','COMUNICACION_FAMILIA','CITACION_FAMILIA','VISITA_DOMICILIARIA','DERIVACION_ALIADO','REGISTRO_SISEVE','REPORTE_UGEL','REPORTE_MINISTERIO_PUBLICO','SEGUIMIENTO','OTRO') NOT NULL,
  descripcion TEXT NOT NULL,
  resultado TEXT NULL,
  responsable_id_persona INT UNSIGNED NULL,
  aliado_id INT UNSIGNED NULL,
  documento_referencia VARCHAR(150) NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_ape_exp FOREIGN KEY (expediente_id) REFERENCES conv_expediente_convivencia(expediente_id),
  CONSTRAINT conv_fk_ape_resp FOREIGN KEY (responsable_id_persona) REFERENCES persona(id_persona),
  CONSTRAINT conv_fk_ape_aliado FOREIGN KEY (aliado_id) REFERENCES conv_institucion_aliada(aliado_id),
  INDEX conv_idx_ape_exp_fecha (expediente_id, fecha_accion),
  INDEX conv_idx_ape_tipo_fecha (tipo_accion, fecha_accion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_acceso_expediente_confidencial (
  acceso_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expediente_id INT UNSIGNED NOT NULL,
  id_usuario INT UNSIGNED NULL,
  id_persona INT UNSIGNED NULL,
  fecha_acceso DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accion ENUM('VER','IMPRIMIR','EXPORTAR','MODIFICAR','ELIMINAR_LOGICO') NOT NULL,
  motivo TEXT NOT NULL,
  ip_origen VARCHAR(45) NULL,
  CONSTRAINT conv_fk_acc_exp FOREIGN KEY (expediente_id) REFERENCES conv_expediente_convivencia(expediente_id),
  CONSTRAINT conv_fk_acc_usuario FOREIGN KEY (id_usuario) REFERENCES usuario_sistema(id_usuario),
  CONSTRAINT conv_fk_acc_persona FOREIGN KEY (id_persona) REFERENCES persona(id_persona),
  INDEX conv_idx_acc_exp_fecha (expediente_id, fecha_acceso)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_reporte_proteccion (
  reporte_proteccion_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  solicitud_reporte_id INT UNSIGNED NOT NULL,
  periodo_reporte_id SMALLINT UNSIGNED NOT NULL,
  institucion_id INT UNSIGNED NOT NULL,
  codigo_reporte VARCHAR(80) NOT NULL UNIQUE,
  fecha_elaboracion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  fecha_envio DATETIME NULL,
  enviado_a VARCHAR(180) NULL,
  medio_envio ENUM('MESA_PARTES','CORREO','SISTEMA','FISICO','OTRO') NULL,
  estado ENUM('BORRADOR','APROBADO','ENVIADO','OBSERVADO','ARCHIVADO') NOT NULL DEFAULT 'BORRADOR',
  elaborado_por_persona_id INT UNSIGNED NULL,
  aprobado_por_persona_id INT UNSIGNED NULL,
  oficio_remision VARCHAR(120) NULL,
  observacion TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_rp_solicitud FOREIGN KEY (solicitud_reporte_id) REFERENCES conv_solicitud_reporte_proteccion(solicitud_reporte_id),
  CONSTRAINT conv_fk_rp_periodo FOREIGN KEY (periodo_reporte_id) REFERENCES conv_periodo_reporte_proteccion(periodo_reporte_id),
  CONSTRAINT conv_fk_rp_ie FOREIGN KEY (institucion_id) REFERENCES conv_institucion_educativa(institucion_id),
  CONSTRAINT conv_fk_rp_elaborado FOREIGN KEY (elaborado_por_persona_id) REFERENCES persona(id_persona),
  CONSTRAINT conv_fk_rp_aprobado FOREIGN KEY (aprobado_por_persona_id) REFERENCES persona(id_persona),
  INDEX conv_idx_rp_periodo_estado (periodo_reporte_id, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_reporte_proteccion_detalle (
  reporte_detalle_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reporte_proteccion_id BIGINT UNSIGNED NOT NULL,
  expediente_id INT UNSIGNED NOT NULL,
  id_estudiante INT UNSIGNED NOT NULL,
  categoria_proteccion_id SMALLINT UNSIGNED NOT NULL,
  resumen_caso TEXT NULL,
  acciones_realizadas TEXT NULL,
  estado_atencion ENUM('IDENTIFICADO','EN_ATENCION_IE','DERIVADO','EN_SEGUIMIENTO','CERRADO') NOT NULL DEFAULT 'IDENTIFICADO',
  observacion_reservada TEXT NULL,
  CONSTRAINT conv_fk_rpd_reporte FOREIGN KEY (reporte_proteccion_id) REFERENCES conv_reporte_proteccion(reporte_proteccion_id),
  CONSTRAINT conv_fk_rpd_exp FOREIGN KEY (expediente_id) REFERENCES conv_expediente_convivencia(expediente_id),
  CONSTRAINT conv_fk_rpd_estudiante FOREIGN KEY (id_estudiante) REFERENCES estudiante(id_estudiante),
  CONSTRAINT conv_fk_rpd_categoria FOREIGN KEY (categoria_proteccion_id) REFERENCES conv_categoria_proteccion_estudiante(categoria_proteccion_id),
  UNIQUE KEY conv_uk_rpd_rep_exp_cat (reporte_proteccion_id, expediente_id, categoria_proteccion_id),
  INDEX conv_idx_rpd_est_cat (id_estudiante, categoria_proteccion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO conv_documento_gestion
  (institucion_id, anio_lectivo_id, tipo_documento, titulo, finalidad, responsable, version_documento, fecha_documento, archivo_referencia, estado)
SELECT 1, NULL, 'Oficio circular',
       'Oficio Circular N. 092-2025-GRSM-DRE-DUGEL-H - Solicitud de datos de estudiantes en situacion de riesgo',
       'Registrar, sistematizar y reportar informacion reservada de estudiantes en riesgo, desproteccion, violencia, asistencia, salud, rendimiento e inconcurrencia familiar.',
       'Direccion / Comite de Gestion del Bienestar', '2025', '2025-08-22',
       '034-2025685757-OFICIO CIRCULAR N. 092-2025-GRSM-DRE-DUGEL-H.pdf',
       'Vigente'
WHERE NOT EXISTS (
  SELECT 1 FROM conv_documento_gestion
  WHERE titulo LIKE 'Oficio Circular N. 092-2025-GRSM-DRE-DUGEL-H%'
);

INSERT INTO conv_base_legal (documento_id, norma, descripcion, orden)
SELECT d.documento_id, 'Articulo 138 del Codigo de los Ninos y Adolescentes',
       'El Fiscal de Familia vela por el respeto de los derechos y garantias de ninas, ninos y adolescentes, promoviendo acciones legales, judiciales o extrajudiciales.', 1
FROM conv_documento_gestion d
WHERE d.titulo LIKE 'Oficio Circular N. 092-2025-GRSM-DRE-DUGEL-H%'
  AND NOT EXISTS (
    SELECT 1 FROM conv_base_legal bl
    WHERE bl.documento_id = d.documento_id
      AND bl.norma = 'Articulo 138 del Codigo de los Ninos y Adolescentes'
  );

INSERT INTO conv_base_legal (documento_id, norma, descripcion, orden)
SELECT d.documento_id, 'Decreto Legislativo N. 1297 - Proteccion de ninas, ninos y adolescentes sin cuidados parentales o en riesgo de perderlos',
       'Sustenta la intervencion en procedimientos por riesgo o desproteccion familiar.', 2
FROM conv_documento_gestion d
WHERE d.titulo LIKE 'Oficio Circular N. 092-2025-GRSM-DRE-DUGEL-H%'
  AND NOT EXISTS (
    SELECT 1 FROM conv_base_legal bl
    WHERE bl.documento_id = d.documento_id
      AND bl.norma LIKE 'Decreto Legislativo N. 1297%'
  );

INSERT INTO conv_documento_externo (
  institucion_id, tipo_documento, numero_documento, fecha_documento, entidad_emisora,
  entidad_referenciada, asunto, referencia, resumen, ruta_archivo, codigo_verificacion, activo
)
SELECT 1, 'OFICIO_CIRCULAR', 'OFICIO CIRCULAR N. 092-2025-GRSM-DRE-DUGEL-H', '2025-08-22',
       'UGEL Huallaga', 'Ministerio Publico - Fiscalia Provincial Civil y Familia de Huallaga',
       'Solicito datos de los estudiantes, progenitores y/o apoderados',
       'Oficio N. 668-2025-MP-FPC y F-HUALLAGA',
       'Solicita remitir nombres y apellidos de estudiantes, progenitores y/o apoderados, direccion y numero de celular en casos de riesgo, violencia, desproteccion, asistencia, salud, rendimiento, autolesiones, intentos de suicidio e inconcurrencia familiar.',
       '034-2025685757-OFICIO CIRCULAR N. 092-2025-GRSM-DRE-DUGEL-H.pdf',
       '142372daP58d1G4aa6Bae2fpf55473ae708b',
       1
WHERE NOT EXISTS (
  SELECT 1 FROM conv_documento_externo
  WHERE numero_documento = 'OFICIO CIRCULAR N. 092-2025-GRSM-DRE-DUGEL-H'
    AND entidad_emisora = 'UGEL Huallaga'
);

INSERT INTO conv_solicitud_reporte_proteccion (
  documento_externo_id, institucion_id, codigo_solicitud, fecha_solicitud,
  entidad_solicitante, autoridad_origen, fundamento_legal, descripcion_solicitud,
  datos_solicitados, finalidad, estado, observacion
)
SELECT de.documento_externo_id, 1, 'SOL-UGEL-MP-092-2025', '2025-08-22',
       'UGEL Huallaga', 'Ministerio Publico - Fiscalia Provincial Civil y Familia de Huallaga',
       'Articulo 138 del Codigo de los Ninos y Adolescentes y Decreto Legislativo N. 1297.',
       'Remitir reportes actualizados de estudiantes inmersos en supuestos de riesgo o desproteccion familiar y otros hechos lesivos.',
       'Nombres y apellidos de estudiantes; nombres y apellidos de progenitores y/o apoderados; direccion; numero de celular; tipo de caso; acciones de atencion y seguimiento.',
       'Atender el interes superior de ninas, ninos y adolescentes y facilitar la intervencion de las instancias competentes.',
       'VIGENTE',
       'Informacion de tratamiento reservado por incluir datos personales y situaciones sensibles.'
FROM conv_documento_externo de
WHERE de.numero_documento = 'OFICIO CIRCULAR N. 092-2025-GRSM-DRE-DUGEL-H'
  AND NOT EXISTS (
    SELECT 1 FROM conv_solicitud_reporte_proteccion
    WHERE codigo_solicitud = 'SOL-UGEL-MP-092-2025'
  );

INSERT INTO conv_periodo_reporte_proteccion (solicitud_reporte_id, numero_reporte, nombre, fecha_inicio, fecha_fin, fecha_limite_envio, descripcion, activo)
SELECT srp.solicitud_reporte_id, 1, 'Primer reporte: casos hasta julio 2025', '2025-01-01', '2025-07-31', '2025-08-29',
       'Casos suscitados hasta julio, con fecha limite de remision 29 de agosto de 2025.', 1
FROM conv_solicitud_reporte_proteccion srp
WHERE srp.codigo_solicitud = 'SOL-UGEL-MP-092-2025'
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre), fecha_inicio = VALUES(fecha_inicio), fecha_fin = VALUES(fecha_fin), fecha_limite_envio = VALUES(fecha_limite_envio), descripcion = VALUES(descripcion), activo = VALUES(activo);

INSERT INTO conv_periodo_reporte_proteccion (solicitud_reporte_id, numero_reporte, nombre, fecha_inicio, fecha_fin, fecha_limite_envio, descripcion, activo)
SELECT srp.solicitud_reporte_id, 2, 'Segundo reporte: agosto, setiembre y octubre 2025', '2025-08-01', '2025-10-31', '2025-10-31',
       'Casos suscitados en agosto, setiembre y octubre, con fecha limite de remision 31 de octubre de 2025.', 1
FROM conv_solicitud_reporte_proteccion srp
WHERE srp.codigo_solicitud = 'SOL-UGEL-MP-092-2025'
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre), fecha_inicio = VALUES(fecha_inicio), fecha_fin = VALUES(fecha_fin), fecha_limite_envio = VALUES(fecha_limite_envio), descripcion = VALUES(descripcion), activo = VALUES(activo);

INSERT INTO conv_periodo_reporte_proteccion (solicitud_reporte_id, numero_reporte, nombre, fecha_inicio, fecha_fin, fecha_limite_envio, descripcion, activo)
SELECT srp.solicitud_reporte_id, 3, 'Tercer reporte: noviembre y diciembre 2025', '2025-11-01', '2025-12-31', '2025-12-15',
       'Casos suscitados en noviembre y diciembre, con fecha limite de remision 15 de diciembre de 2025.', 1
FROM conv_solicitud_reporte_proteccion srp
WHERE srp.codigo_solicitud = 'SOL-UGEL-MP-092-2025'
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre), fecha_inicio = VALUES(fecha_inicio), fecha_fin = VALUES(fecha_fin), fecha_limite_envio = VALUES(fecha_limite_envio), descripcion = VALUES(descripcion), activo = VALUES(activo);

INSERT INTO conv_institucion_aliada (nombre, proposito, direccion, actor_clave, contacto, estado)
SELECT 'UGEL Huallaga', 'Recepciona reportes institucionales y brinda asistencia tecnica a directores de la jurisdiccion.', 'Saposoa - Huallaga - San Martin', 'Director de UGEL Huallaga', NULL, 1
WHERE NOT EXISTS (SELECT 1 FROM conv_institucion_aliada WHERE nombre = 'UGEL Huallaga');

INSERT INTO conv_institucion_aliada (nombre, proposito, direccion, actor_clave, contacto, estado)
SELECT 'Ministerio Publico - Fiscalia Provincial Civil y Familia de Huallaga', 'Interviene en procedimientos por riesgo o desproteccion familiar y tutela de derechos de ninas, ninos y adolescentes.', 'Saposoa - Huallaga', 'Fiscal Provincial Civil y de Familia', NULL, 1
WHERE NOT EXISTS (SELECT 1 FROM conv_institucion_aliada WHERE nombre = 'Ministerio Publico - Fiscalia Provincial Civil y Familia de Huallaga');

INSERT INTO conv_categoria_proteccion_estudiante
(codigo, literal, nombre, descripcion, tipo_caso, nivel_alerta, requiere_derivacion, requiere_reserva, activo, orden)
VALUES
('MALTRATO_VIOLENCIA', 'a', 'Maltrato fisico, psicologico, acoso, abuso o violencia sexual', 'Situacion de maltrato fisico, psicologico, acoso, abuso o violencia sexual en agravio de estudiantes.', 'VIOLENCIA', 'CRITICO', 1, 1, 1, 1),
('REPITENCIA_DESERCION', 'b', 'Reiterada repitencia y desercion escolar', 'Casos de reiterada repitencia, desercion, abandono o riesgo de interrupcion de estudios.', 'ACADEMICO', 'ALTO', 1, 1, 1, 2),
('FALTAS_INJUSTIFICADAS', 'c', 'Reiteradas faltas injustificadas', 'Inasistencias reiteradas sin justificacion presentada por la familia dentro del plazo establecido.', 'ASISTENCIA', 'ALTO', 1, 1, 1, 3),
('SUSTANCIAS_TOXICAS', 'd', 'Consumo de sustancias toxicas', 'Consumo o indicios de consumo de alcohol, drogas u otras sustancias toxicas.', 'SALUD', 'CRITICO', 1, 1, 1, 4),
('DESAMPARO_DERECHOS', 'e', 'Desamparo o violacion de derechos del nino y adolescente', 'Situacion de desamparo u otros hechos que impliquen vulneracion de derechos.', 'DERECHOS', 'CRITICO', 1, 1, 1, 5),
('RENDIMIENTO_DEFICIENTE', 'f', 'Rendimiento escolar deficiente', 'Bajo rendimiento academico persistente que requiere acompanamiento familiar e institucional.', 'ACADEMICO', 'MEDIO', 0, 1, 1, 6),
('EMBARAZO_PRECOZ', 'g', 'Embarazo precoz', 'Caso de estudiante gestante o maternidad adolescente que requiere proteccion, continuidad educativa y no discriminacion.', 'SALUD', 'ALTO', 1, 1, 1, 7),
('AUTOLESIONES_CORTES', 'h', 'Cortes o autolesiones', 'Cortes o lesiones que el estudiante se realiza en alguna parte de su cuerpo.', 'SALUD_MENTAL', 'CRITICO', 1, 1, 1, 8),
('ADICCION_JUEGOS_CELULAR', 'i', 'Adiccion a juegos electronicos y celular', 'Uso problematico o adictivo de juegos electronicos y celular que afecta aprendizaje, convivencia o bienestar.', 'CONDUCTUAL', 'ALTO', 1, 1, 1, 9),
('INTENTO_SUICIDIO', 'j', 'Intentos de suicidio', 'Ideacion, intento o conducta suicida reportada u observada.', 'SALUD_MENTAL', 'CRITICO', 1, 1, 1, 10),
('HECHOS_LESIVOS', 'k', 'Otros hechos lesivos', 'Otros hechos que lesionen derechos, integridad, bienestar, continuidad educativa o convivencia.', 'OTRO', 'ALTO', 1, 1, 1, 11),
('INCONCURRENCIA_PADRES', 'l', 'Inconcurrencia de padres a citaciones', 'Padres, madres o apoderados no concurren a citaciones realizadas por la IE sin justificar su inasistencia.', 'FAMILIAR', 'MEDIO', 0, 1, 1, 12)
ON DUPLICATE KEY UPDATE
  literal = VALUES(literal),
  nombre = VALUES(nombre),
  descripcion = VALUES(descripcion),
  tipo_caso = VALUES(tipo_caso),
  nivel_alerta = VALUES(nivel_alerta),
  requiere_derivacion = VALUES(requiere_derivacion),
  requiere_reserva = VALUES(requiere_reserva),
  activo = VALUES(activo),
  orden = VALUES(orden);

INSERT INTO conv_supuesto_riesgo_desproteccion
(codigo, nombre, descripcion, nivel_referencia, activo, orden)
VALUES
('VIOLENCIA_NO_GRAVE', 'Violencia fisica o psicologica no grave', 'Violencia fisica o psicologica en agravio de nina, nino o adolescente que no constituya situacion grave segun tabla de valoracion de riesgo.', 'RIESGO_MODERADO', 1, 1),
('AUSENTISMO_ABANDONO', 'Desercion, ausentismo o abandono escolar', 'Desercion escolar, ausentismo esporadico o abandono escolar sin razones justificadas.', 'RIESGO_MODERADO', 1, 2),
('CONDUCTA_RIESGO_AUTO_TERCEROS', 'Conducta de riesgo hacia si mismo o terceros', 'Incapacidad o imposibilidad de controlar situaciones conductuales que puedan conllevar riesgo, peligro inminente de hacerse dano o hacer dano a terceras personas.', 'RIESGO_ALTO', 1, 3),
('DESCUIDO_NEGLIGENCIA', 'Descuido o negligencia familiar', 'Descuido o negligencia que pone en riesgo leve el desarrollo integral de la nina, nino o adolescente.', 'RIESGO_MODERADO', 1, 4),
('TRABAJO_INFANTIL_RIESGO', 'Trabajo infantil con afectacion de derechos', 'Trabajo infantil en situacion de calle o aquel que suponga afectacion de derechos sin revestir gravedad segun tabla de valoracion de riesgo.', 'RIESGO_MODERADO', 1, 5)
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre), descripcion = VALUES(descripcion), nivel_referencia = VALUES(nivel_referencia), activo = VALUES(activo), orden = VALUES(orden);

INSERT INTO conv_categoria_situacion (nombre, descripcion)
SELECT 'Proteccion, desproteccion y reporte externo', 'Situaciones de riesgo, derechos, proteccion y reportes solicitados por UGEL o Ministerio Publico.'
WHERE NOT EXISTS (
  SELECT 1 FROM conv_categoria_situacion WHERE nombre = 'Proteccion, desproteccion y reporte externo'
);

INSERT INTO conv_situacion_acta (tipo_acta_id, categoria_situacion_id, nombre, descripcion, estado)
SELECT NULL, cs.categoria_situacion_id, x.nombre, x.descripcion, 1
FROM conv_categoria_situacion cs
JOIN (
  SELECT 'Desercion o abandono escolar' nombre, 'Interrupcion o abandono de la trayectoria educativa sin justificacion suficiente.' descripcion
  UNION ALL SELECT 'Repitencia reiterada', 'Situacion academica de repitencia reiterada que requiere intervencion preventiva y familiar.'
  UNION ALL SELECT 'Consumo de sustancias toxicas', 'Consumo o indicios de consumo de alcohol, drogas u otras sustancias toxicas.'
  UNION ALL SELECT 'Desamparo o vulneracion de derechos', 'Situacion familiar o social que amenaza o afecta el ejercicio de derechos del estudiante.'
  UNION ALL SELECT 'Rendimiento escolar deficiente', 'Bajo rendimiento persistente que requiere seguimiento pedagogico, tutorial y familiar.'
  UNION ALL SELECT 'Embarazo precoz', 'Estudiante gestante o madre adolescente que requiere continuidad educativa, soporte y no discriminacion.'
  UNION ALL SELECT 'Autolesiones o cortes', 'Cortes o lesiones autoinfligidas que requieren atencion inmediata, reserva y derivacion.'
  UNION ALL SELECT 'Adiccion a juegos electronicos y celular', 'Uso problematico de juegos electronicos o celular que afecta bienestar, aprendizaje o convivencia.'
  UNION ALL SELECT 'Intento de suicidio', 'Ideacion, intento o conducta suicida que requiere actuacion urgente, reserva y derivacion especializada.'
  UNION ALL SELECT 'Inconcurrencia de padres a citaciones', 'Padres, madres o apoderados no asisten a citaciones institucionales sin justificacion.'
  UNION ALL SELECT 'Trabajo infantil con afectacion de derechos', 'Trabajo infantil o situacion de calle que supone afectacion de derechos.'
  UNION ALL SELECT 'Descuido o negligencia familiar', 'Descuido familiar que afecta o amenaza el desarrollo integral del estudiante.'
) x
WHERE cs.nombre = 'Proteccion, desproteccion y reporte externo'
  AND NOT EXISTS (SELECT 1 FROM conv_situacion_acta s WHERE s.nombre = x.nombre);

INSERT IGNORE INTO conv_categoria_proteccion_norma (categoria_proteccion_id, norma_id, observacion)
SELECT c.categoria_proteccion_id, n.norma_id, 'Vinculada a buen trato y no agresion.'
FROM conv_categoria_proteccion_estudiante c
JOIN conv_norma_convivencia n ON n.numero = 5
WHERE c.codigo = 'MALTRATO_VIOLENCIA';

INSERT IGNORE INTO conv_categoria_proteccion_norma (categoria_proteccion_id, norma_id, observacion)
SELECT c.categoria_proteccion_id, n.norma_id, 'Vinculada a asistencia diaria.'
FROM conv_categoria_proteccion_estudiante c
JOIN conv_norma_convivencia n ON n.numero = 1
WHERE c.codigo = 'FALTAS_INJUSTIFICADAS';

INSERT IGNORE INTO conv_categoria_proteccion_norma (categoria_proteccion_id, norma_id, observacion)
SELECT c.categoria_proteccion_id, n.norma_id, 'Vinculada al cumplimiento de responsabilidades academicas.'
FROM conv_categoria_proteccion_estudiante c
JOIN conv_norma_convivencia n ON n.numero = 4
WHERE c.codigo = 'RENDIMIENTO_DEFICIENTE';

INSERT IGNORE INTO conv_categoria_proteccion_norma (categoria_proteccion_id, norma_id, observacion)
SELECT c.categoria_proteccion_id, n.norma_id, 'Vinculada al uso responsable de objetos y tecnologia.'
FROM conv_categoria_proteccion_estudiante c
JOIN conv_norma_convivencia n ON n.numero = 8
WHERE c.codigo = 'ADICCION_JUEGOS_CELULAR';

INSERT IGNORE INTO conv_categoria_proteccion_norma (categoria_proteccion_id, norma_id, observacion)
SELECT c.categoria_proteccion_id, n.norma_id, 'Vinculada a la corresponsabilidad familia-escuela.'
FROM conv_categoria_proteccion_estudiante c
JOIN conv_norma_convivencia n ON n.numero = 10
WHERE c.codigo = 'INCONCURRENCIA_PADRES';

INSERT IGNORE INTO conv_categoria_proteccion_situacion (categoria_proteccion_id, situacion_id, observacion)
SELECT c.categoria_proteccion_id, s.situacion_id, x.observacion
FROM conv_categoria_proteccion_estudiante c
JOIN (
  SELECT 'MALTRATO_VIOLENCIA' codigo, 'Agresion fisica' situacion, 'Agresion fisica.' observacion
  UNION ALL SELECT 'MALTRATO_VIOLENCIA', 'Agresion verbal', 'Agresion verbal o psicologica.'
  UNION ALL SELECT 'REPITENCIA_DESERCION', 'Desercion o abandono escolar', 'Desercion escolar.'
  UNION ALL SELECT 'REPITENCIA_DESERCION', 'Repitencia reiterada', 'Repitencia reiterada.'
  UNION ALL SELECT 'FALTAS_INJUSTIFICADAS', 'Inasistencias injustificadas', 'Inasistencia injustificada.'
  UNION ALL SELECT 'SUSTANCIAS_TOXICAS', 'Consumo de sustancias toxicas', 'Consumo de sustancias.'
  UNION ALL SELECT 'DESAMPARO_DERECHOS', 'Desamparo o vulneracion de derechos', 'Vulneracion de derechos.'
  UNION ALL SELECT 'RENDIMIENTO_DEFICIENTE', 'Rendimiento escolar deficiente', 'Rendimiento deficiente.'
  UNION ALL SELECT 'EMBARAZO_PRECOZ', 'Embarazo precoz', 'Embarazo precoz.'
  UNION ALL SELECT 'AUTOLESIONES_CORTES', 'Autolesiones o cortes', 'Autolesiones.'
  UNION ALL SELECT 'ADICCION_JUEGOS_CELULAR', 'Adiccion a juegos electronicos y celular', 'Uso problematico de celular o juegos electronicos.'
  UNION ALL SELECT 'INTENTO_SUICIDIO', 'Intento de suicidio', 'Intento de suicidio.'
  UNION ALL SELECT 'INCONCURRENCIA_PADRES', 'Inconcurrencia de padres a citaciones', 'Inconcurrencia familiar.'
) x ON x.codigo = c.codigo
JOIN conv_situacion_acta s ON s.nombre = x.situacion;

CREATE OR REPLACE VIEW vw_conv_reporte_ugel_estudiantes_riesgo AS
SELECT
  exp.expediente_id,
  exp.codigo_expediente,
  exp.fecha_apertura AS fecha_registro,
  COALESCE(exp.fecha_hecho, exp.fecha_apertura) AS fecha_hecho,
  exp.estado AS estado_expediente,
  exp.prioridad,
  exp.nivel_proteccion,
  exp.nivel_confidencialidad,
  e.id_estudiante,
  p.numero_documento AS dni_estudiante,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  COALESCE(vm.nivel, '') AS nivel,
  COALESCE(vm.grado_descripcion, vm.grado, '') AS grado,
  COALESCE(vm.seccion, '') AS seccion,
  COALESCE(NULLIF(p.direccion, ''), fam.direcciones_familia, '') AS direccion_reporte,
  COALESCE(fam.familiares, vm.apoderado, '') AS progenitores_apoderados,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cats.categorias_reporte,
  cats.niveles_alerta,
  LEFT(COALESCE(exp.descripcion_proteccion, exp.motivo_apertura), 500) AS descripcion_resumida,
  cats.ultima_fecha_deteccion,
  exp.requiere_reporte_externo
FROM conv_expediente_convivencia exp
INNER JOIN estudiante e ON e.id_estudiante = exp.estudiante_id
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN conv_anio_lectivo cal ON cal.anio_lectivo_id = exp.anio_lectivo_id
LEFT JOIN anio_lectivo al ON al.anio = cal.anio
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = e.id_estudiante AND vm.id_anio_lectivo = al.id_anio_lectivo
LEFT JOIN (
  SELECT
    fe.id_estudiante,
    GROUP_CONCAT(DISTINCT CONCAT(COALESCE(NULLIF(fp.nombre_completo, ''), CONCAT_WS(' ', fp.apellido_paterno, fp.apellido_materno, fp.nombres)), ' [', par.descripcion, ']') ORDER BY fe.es_apoderado DESC, par.descripcion SEPARATOR '; ') AS familiares,
    GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular SEPARATOR '; ') AS celulares,
    GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion SEPARATOR '; ') AS direcciones_familia
  FROM familiar_estudiante fe
  INNER JOIN persona fp ON fp.id_persona = fe.id_persona_familiar
  INNER JOIN parentesco par ON par.id_parentesco = fe.id_parentesco
  GROUP BY fe.id_estudiante
) fam ON fam.id_estudiante = e.id_estudiante
INNER JOIN (
  SELECT
    ecp.expediente_id,
    GROUP_CONCAT(DISTINCT CONCAT(cp.literal, ') ', cp.nombre) ORDER BY cp.orden SEPARATOR '; ') AS categorias_reporte,
    GROUP_CONCAT(DISTINCT cp.nivel_alerta ORDER BY FIELD(cp.nivel_alerta, 'CRITICO','ALTO','MEDIO','BAJO') SEPARATOR '; ') AS niveles_alerta,
    MAX(ecp.fecha_deteccion) AS ultima_fecha_deteccion
  FROM conv_expediente_categoria_proteccion ecp
  INNER JOIN conv_categoria_proteccion_estudiante cp ON cp.categoria_proteccion_id = ecp.categoria_proteccion_id
  GROUP BY ecp.expediente_id
) cats ON cats.expediente_id = exp.expediente_id
WHERE exp.requiere_reporte_externo = 1;

CREATE OR REPLACE VIEW vw_conv_alertas_prioritarias_proteccion AS
SELECT
  exp.expediente_id,
  exp.codigo_expediente,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  COALESCE(vm.nivel, '') AS nivel,
  COALESCE(vm.grado_descripcion, vm.grado, '') AS grado,
  COALESCE(vm.seccion, '') AS seccion,
  cp.nombre AS categoria,
  cp.nivel_alerta,
  exp.estado,
  exp.prioridad,
  exp.fecha_apertura AS fecha_registro,
  DATEDIFF(CURDATE(), exp.fecha_apertura) AS dias_abierto,
  CASE
    WHEN cp.nivel_alerta = 'CRITICO' THEN 'ATENCION INMEDIATA Y DERIVACION'
    WHEN cp.nivel_alerta = 'ALTO' THEN 'SEGUIMIENTO PRIORITARIO'
    ELSE 'SEGUIMIENTO FORMATIVO'
  END AS recomendacion_gestion
FROM conv_expediente_convivencia exp
INNER JOIN estudiante e ON e.id_estudiante = exp.estudiante_id
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN conv_anio_lectivo cal ON cal.anio_lectivo_id = exp.anio_lectivo_id
LEFT JOIN anio_lectivo al ON al.anio = cal.anio
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = e.id_estudiante AND vm.id_anio_lectivo = al.id_anio_lectivo
INNER JOIN conv_expediente_categoria_proteccion ecp ON ecp.expediente_id = exp.expediente_id
INNER JOIN conv_categoria_proteccion_estudiante cp ON cp.categoria_proteccion_id = ecp.categoria_proteccion_id
WHERE exp.estado NOT IN ('Cerrado','Archivado')
  AND cp.nivel_alerta IN ('ALTO','CRITICO');

CREATE OR REPLACE VIEW vw_conv_inconcurrencia_padres_citaciones AS
SELECT
  cf.citacion_id,
  exp.expediente_id,
  exp.codigo_expediente,
  e.id_estudiante,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  COALESCE(vm.nivel, '') AS nivel,
  COALESCE(vm.grado_descripcion, vm.grado, '') AS grado,
  COALESCE(vm.seccion, '') AS seccion,
  cf.fecha_emision,
  cf.fecha_programada,
  cf.tipo_citacion,
  cf.medio_notificacion,
  cf.estado,
  cf.motivo,
  cf.justificacion_inasistencia,
  COALESCE(NULLIF(pr.nombre_completo, ''), CONCAT_WS(' ', pr.apellido_paterno, pr.apellido_materno, pr.nombres)) AS responsable
FROM conv_citacion_familia cf
LEFT JOIN conv_expediente_convivencia exp ON exp.expediente_id = cf.expediente_id
INNER JOIN estudiante e ON e.id_estudiante = cf.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = e.id_estudiante AND vm.id_anio_lectivo = (SELECT MAX(id_anio_lectivo) FROM anio_lectivo WHERE estado = 1)
LEFT JOIN persona pr ON pr.id_persona = cf.responsable_id_persona
WHERE cf.estado = 'NO_ASISTIO_INJUSTIFICADO';

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_conv_marcar_expediente_reporte_proteccion $$
CREATE PROCEDURE sp_conv_marcar_expediente_reporte_proteccion(
  IN p_expediente_id INT UNSIGNED,
  IN p_categoria_codigo VARCHAR(80),
  IN p_nivel_valoracion VARCHAR(20),
  IN p_fecha_deteccion DATE,
  IN p_descripcion_valoracion TEXT,
  IN p_registrado_por_persona_id INT UNSIGNED
)
BEGIN
  DECLARE v_categoria_id SMALLINT UNSIGNED;
  DECLARE v_nivel VARCHAR(20);

  SELECT categoria_proteccion_id
    INTO v_categoria_id
  FROM conv_categoria_proteccion_estudiante
  WHERE codigo = p_categoria_codigo
    AND activo = 1
  LIMIT 1;

  IF v_categoria_id IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'La categoria de proteccion indicada no existe o esta inactiva.';
  END IF;

  SET v_nivel = UPPER(COALESCE(p_nivel_valoracion, 'MEDIO'));
  IF v_nivel NOT IN ('BAJO','MEDIO','ALTO','CRITICO') THEN
    SET v_nivel = 'MEDIO';
  END IF;

  INSERT INTO conv_expediente_categoria_proteccion (
    expediente_id, categoria_proteccion_id, fecha_deteccion, nivel_valoracion,
    requiere_reporte_ugel, requiere_reporte_ministerio_publico, descripcion_valoracion,
    registrado_por_persona_id
  ) VALUES (
    p_expediente_id, v_categoria_id, COALESCE(p_fecha_deteccion, CURDATE()), v_nivel,
    1,
    CASE WHEN v_nivel IN ('ALTO','CRITICO') THEN 1 ELSE 0 END,
    p_descripcion_valoracion,
    p_registrado_por_persona_id
  )
  ON DUPLICATE KEY UPDATE
    fecha_deteccion = VALUES(fecha_deteccion),
    nivel_valoracion = VALUES(nivel_valoracion),
    requiere_reporte_ugel = VALUES(requiere_reporte_ugel),
    requiere_reporte_ministerio_publico = VALUES(requiere_reporte_ministerio_publico),
    descripcion_valoracion = VALUES(descripcion_valoracion),
    registrado_por_persona_id = VALUES(registrado_por_persona_id);

  UPDATE conv_expediente_convivencia
  SET requiere_reporte_externo = 1,
      prioridad = CASE WHEN v_nivel = 'CRITICO' THEN 'CRITICA' WHEN v_nivel = 'ALTO' THEN 'ALTA' ELSE prioridad END,
      nivel_confidencialidad = CASE WHEN v_nivel IN ('ALTO','CRITICO') THEN 'ESTRICTO' ELSE nivel_confidencialidad END,
      nivel_proteccion = CASE
        WHEN p_categoria_codigo IN ('INTENTO_SUICIDIO','AUTOLESIONES_CORTES') THEN 'SALUD_MENTAL'
        WHEN p_categoria_codigo IN ('DESAMPARO_DERECHOS') THEN 'RIESGO_DESPROTECCION'
        WHEN p_categoria_codigo IN ('MALTRATO_VIOLENCIA') THEN 'VIOLENCIA'
        ELSE nivel_proteccion
      END
  WHERE expediente_id = p_expediente_id;
END $$

DROP PROCEDURE IF EXISTS sp_conv_generar_reporte_proteccion_periodo $$
CREATE PROCEDURE sp_conv_generar_reporte_proteccion_periodo(
  IN p_periodo_reporte_id SMALLINT UNSIGNED,
  IN p_elaborado_por_persona_id INT UNSIGNED
)
BEGIN
  DECLARE v_solicitud_id INT UNSIGNED;
  DECLARE v_institucion_id INT UNSIGNED DEFAULT 1;
  DECLARE v_numero_reporte TINYINT UNSIGNED;
  DECLARE v_codigo_reporte VARCHAR(80);
  DECLARE v_reporte_id BIGINT UNSIGNED;

  SELECT solicitud_reporte_id, numero_reporte
    INTO v_solicitud_id, v_numero_reporte
  FROM conv_periodo_reporte_proteccion
  WHERE periodo_reporte_id = p_periodo_reporte_id
  LIMIT 1;

  IF v_solicitud_id IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'El periodo de reporte no existe.';
  END IF;

  SET v_codigo_reporte = CONCAT('REP-IE0003-UGEL-MP-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', LPAD(v_numero_reporte, 2, '0'));

  INSERT INTO conv_reporte_proteccion (
    solicitud_reporte_id, periodo_reporte_id, institucion_id, codigo_reporte,
    fecha_elaboracion, estado, elaborado_por_persona_id, observacion
  ) VALUES (
    v_solicitud_id, p_periodo_reporte_id, v_institucion_id, v_codigo_reporte,
    NOW(), 'BORRADOR', p_elaborado_por_persona_id,
    'Reporte generado automaticamente a partir de expedientes marcados para reporte externo.'
  );

  SET v_reporte_id = LAST_INSERT_ID();

  INSERT IGNORE INTO conv_reporte_proteccion_detalle (
    reporte_proteccion_id, expediente_id, id_estudiante, categoria_proteccion_id,
    resumen_caso, acciones_realizadas, estado_atencion, observacion_reservada
  )
  SELECT
    v_reporte_id,
    exp.expediente_id,
    exp.estudiante_id,
    ecp.categoria_proteccion_id,
    LEFT(COALESCE(exp.descripcion_proteccion, exp.motivo_apertura), 1000) AS resumen_caso,
    (
      SELECT GROUP_CONCAT(CONCAT(DATE_FORMAT(ape.fecha_accion, '%d/%m/%Y'), ': ', ape.tipo_accion, ' - ', LEFT(ape.descripcion, 120)) SEPARATOR ' | ')
      FROM conv_accion_proteccion_estudiante ape
      WHERE ape.expediente_id = exp.expediente_id
    ) AS acciones_realizadas,
    CASE
      WHEN exp.estado = 'Cerrado' THEN 'CERRADO'
      WHEN exp.estado = 'Derivado' THEN 'DERIVADO'
      WHEN exp.estado = 'En seguimiento' THEN 'EN_SEGUIMIENTO'
      ELSE 'IDENTIFICADO'
    END AS estado_atencion,
    'Informacion reservada. Uso exclusivo institucional, UGEL y Ministerio Publico segun solicitud.'
  FROM conv_expediente_convivencia exp
  INNER JOIN conv_expediente_categoria_proteccion ecp ON ecp.expediente_id = exp.expediente_id
  INNER JOIN conv_periodo_reporte_proteccion prp ON prp.periodo_reporte_id = p_periodo_reporte_id
  WHERE exp.requiere_reporte_externo = 1
    AND DATE(COALESCE(exp.fecha_hecho, exp.fecha_apertura)) BETWEEN prp.fecha_inicio AND prp.fecha_fin;

  SELECT v_reporte_id AS reporte_proteccion_id, v_codigo_reporte AS codigo_reporte;
END $$

DELIMITER ;