/* ============================================================================
   COMANO 0003 - Integracion integral del reporte de alerta Fiscalia
   Fuentes: expedientes de convivencia, actas, asistencia, rendimiento academico,
            incidencias graves y reportes SISEVE.
   ============================================================================ */

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

CREATE TABLE IF NOT EXISTS conv_reporte_siseve (
  reporte_siseve_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_estudiante INT UNSIGNED NULL,
  fecha_registro DATE NOT NULL,
  codigo_caso VARCHAR(80) NULL,
  tipo_caso VARCHAR(160) NOT NULL,
  descripcion_hecho TEXT NOT NULL,
  acciones_realizadas TEXT NULL,
  estado ENUM('REGISTRADO','EN_SEGUIMIENTO','DERIVADO','CERRADO') NOT NULL DEFAULT 'REGISTRADO',
  archivo_referencia VARCHAR(255) NULL,
  observacion TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_siseve_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo),
  CONSTRAINT conv_fk_siseve_estudiante FOREIGN KEY (id_estudiante) REFERENCES estudiante(id_estudiante),
  INDEX conv_idx_siseve_fecha (fecha_registro),
  INDEX conv_idx_siseve_estudiante (id_estudiante, fecha_registro)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_documento_externo_fuente (
  documento_fuente_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  documento_externo_id INT UNSIGNED NOT NULL,
  fuente_origen ENUM('EXPEDIENTE','ACTA','ASISTENCIA','RENDIMIENTO','INCIDENCIA','SISEVE') NOT NULL,
  descripcion VARCHAR(220) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_doc_fuente_doc FOREIGN KEY (documento_externo_id) REFERENCES conv_documento_externo(documento_externo_id),
  UNIQUE KEY conv_uk_doc_fuente (documento_externo_id, fuente_origen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conv_reporte_proteccion_evidencia (
  evidencia_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reporte_proteccion_id BIGINT UNSIGNED NOT NULL,
  periodo_reporte_id SMALLINT UNSIGNED NOT NULL,
  solicitud_reporte_id INT UNSIGNED NOT NULL,
  fuente_origen ENUM('EXPEDIENTE','ACTA','ASISTENCIA','RENDIMIENTO','INCIDENCIA','SISEVE') NOT NULL,
  fuente_id VARCHAR(80) NOT NULL,
  fecha_evento DATE NULL,
  id_anio_lectivo SMALLINT UNSIGNED NULL,
  periodo_academico_id SMALLINT UNSIGNED NULL,
  periodo_academico VARCHAR(100) NULL,
  id_estudiante INT UNSIGNED NULL,
  dni_estudiante VARCHAR(20) NULL,
  estudiante VARCHAR(220) NOT NULL,
  nivel VARCHAR(80) NULL,
  grado VARCHAR(80) NULL,
  seccion VARCHAR(30) NULL,
  nivel_orden TINYINT UNSIGNED NULL,
  grado_orden SMALLINT UNSIGNED NULL,
  seccion_orden SMALLINT UNSIGNED NULL,
  progenitores_apoderados TEXT NULL,
  direccion_reporte TEXT NULL,
  celulares_familia TEXT NULL,
  categoria_proteccion_id SMALLINT UNSIGNED NULL,
  categoria_codigo VARCHAR(60) NULL,
  categoria_nombre VARCHAR(180) NULL,
  nivel_alerta VARCHAR(30) NULL,
  resumen_caso TEXT NULL,
  acciones_realizadas TEXT NULL,
  estado_atencion ENUM('IDENTIFICADO','EN_ATENCION_IE','DERIVADO','EN_SEGUIMIENTO','CERRADO') NOT NULL DEFAULT 'IDENTIFICADO',
  documento_referencia VARCHAR(255) NULL,
  observacion_reservada TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT conv_fk_rpe_reporte FOREIGN KEY (reporte_proteccion_id) REFERENCES conv_reporte_proteccion(reporte_proteccion_id) ON DELETE CASCADE,
  CONSTRAINT conv_fk_rpe_periodo FOREIGN KEY (periodo_reporte_id) REFERENCES conv_periodo_reporte_proteccion(periodo_reporte_id),
  CONSTRAINT conv_fk_rpe_solicitud FOREIGN KEY (solicitud_reporte_id) REFERENCES conv_solicitud_reporte_proteccion(solicitud_reporte_id),
  CONSTRAINT conv_fk_rpe_estudiante FOREIGN KEY (id_estudiante) REFERENCES estudiante(id_estudiante),
  CONSTRAINT conv_fk_rpe_categoria FOREIGN KEY (categoria_proteccion_id) REFERENCES conv_categoria_proteccion_estudiante(categoria_proteccion_id),
  UNIQUE KEY conv_uk_rpe_fuente (reporte_proteccion_id, fuente_origen, fuente_id, categoria_proteccion_id),
  INDEX conv_idx_rpe_periodo (periodo_reporte_id, fuente_origen),
  INDEX conv_idx_rpe_estudiante (id_estudiante, fuente_origen),
  INDEX conv_idx_rpe_orden_rendimiento (reporte_proteccion_id, fuente_origen, periodo_academico_id, nivel_orden, grado_orden, seccion_orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE conv_reporte_proteccion_evidencia
  ADD COLUMN IF NOT EXISTS periodo_academico_id SMALLINT UNSIGNED NULL AFTER id_anio_lectivo,
  ADD COLUMN IF NOT EXISTS periodo_academico VARCHAR(100) NULL AFTER periodo_academico_id,
  ADD COLUMN IF NOT EXISTS nivel_orden TINYINT UNSIGNED NULL AFTER seccion,
  ADD COLUMN IF NOT EXISTS grado_orden SMALLINT UNSIGNED NULL AFTER nivel_orden,
  ADD COLUMN IF NOT EXISTS seccion_orden SMALLINT UNSIGNED NULL AFTER grado_orden;

SET @idx_exists := (
  SELECT COUNT(1)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'conv_reporte_proteccion_evidencia'
    AND INDEX_NAME = 'conv_idx_rpe_orden_rendimiento'
);
SET @idx_sql := IF(
  @idx_exists = 0,
  'CREATE INDEX conv_idx_rpe_orden_rendimiento ON conv_reporte_proteccion_evidencia (reporte_proteccion_id, fuente_origen, periodo_academico_id, nivel_orden, grado_orden, seccion_orden)',
  'SELECT 1'
);
PREPARE idx_stmt FROM @idx_sql;
EXECUTE idx_stmt;
DEALLOCATE PREPARE idx_stmt;

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'ACTA', 'Actas de compromiso formativo relacionadas con asistencia, rendimiento, convivencia, objetos no permitidos y salud.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'ASISTENCIA', 'Registros de inasistencias, tardanzas y asistencia escolar vinculados al periodo solicitado.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'RENDIMIENTO', 'Resultados del primer bimestre y otros periodos con riesgo academico alto o critico.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'INCIDENCIA', 'Incidencias y ocurrencias graves o de violencia registradas en tutoria y convivencia.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'SISEVE', 'Reportes SISEVE registrados por violencia escolar o hechos relacionados.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'EXPEDIENTE', 'Expedientes de convivencia escolar y proteccion que requieren reporte externo.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

CREATE OR REPLACE VIEW vw_conv_evidencias_fiscalia AS
SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.ruta_archivo AS documento_referencia,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  'EXPEDIENTE' AS fuente_origen,
  CAST(exp.expediente_id AS CHAR) AS fuente_id,
  COALESCE(ecp.fecha_deteccion, exp.fecha_hecho, exp.fecha_apertura) AS fecha_evento,
  al.id_anio_lectivo AS id_anio_lectivo,
  NULL AS periodo_academico_id,
  NULL AS periodo_academico,
  exp.estudiante_id AS 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,
  FIELD(COALESCE(vm.id_nivel, ''), 'INI', 'PRI', 'SEC') AS nivel_orden,
  vm.id_grado AS grado_orden,
  vm.id_seccion AS seccion_orden,
  COALESCE(fam.familiares, '') AS progenitores_apoderados,
  COALESCE(NULLIF(p.direccion, ''), fam.direcciones, '') AS direccion_reporte,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cp.categoria_proteccion_id,
  cp.codigo AS categoria_codigo,
  cp.nombre AS categoria_nombre,
  cp.nivel_alerta,
  LEFT(CONCAT_WS('\n',
    CONCAT('Expediente ', exp.codigo_expediente, ' - ', cp.nombre),
    CONCAT('Prioridad: ', exp.prioridad, ' | Nivel: ', exp.nivel_proteccion),
    NULLIF(exp.motivo_apertura, ''),
    NULLIF(exp.descripcion_proteccion, ''),
    NULLIF(ecp.descripcion_valoracion, '')
  ), 1500) AS resumen_caso,
  (
    SELECT GROUP_CONCAT(CONCAT(DATE(ap.fecha_accion), ' - ', REPLACE(ap.tipo_accion, '_', ' '), ': ', ap.descripcion) ORDER BY ap.fecha_accion SEPARATOR '\n')
    FROM conv_accion_proteccion_estudiante ap
    WHERE ap.expediente_id = exp.expediente_id
  ) AS acciones_realizadas,
  CASE
    WHEN exp.estado IN ('Cerrado', 'Archivado') THEN 'CERRADO'
    WHEN EXISTS (
      SELECT 1 FROM conv_accion_proteccion_estudiante ap
      WHERE ap.expediente_id = exp.expediente_id
        AND ap.tipo_accion IN ('DERIVACION_ALIADO', 'REPORTE_UGEL', 'REPORTE_MINISTERIO_PUBLICO')
    ) THEN 'DERIVADO'
    WHEN EXISTS (SELECT 1 FROM conv_accion_proteccion_estudiante ap WHERE ap.expediente_id = exp.expediente_id) THEN 'EN_SEGUIMIENTO'
    ELSE 'IDENTIFICADO'
  END AS estado_atencion,
  exp.nivel_confidencialidad AS observacion_reservada
FROM conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
INNER JOIN conv_documento_externo de ON de.documento_externo_id = srp.documento_externo_id
INNER JOIN conv_expediente_convivencia exp ON COALESCE(exp.fecha_hecho, exp.fecha_apertura) BETWEEN prp.fecha_inicio AND prp.fecha_fin
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
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 = exp.estudiante_id 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 ASC SEPARATOR '; ') AS familiares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular ASC SEPARATOR '; ') AS celulares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion ASC SEPARATOR '; ') AS direcciones
  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 = exp.estudiante_id
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND exp.requiere_reporte_externo = 1
  AND exp.estado NOT IN ('Cerrado', 'Archivado')
  AND (
    srp.autoridad_origen LIKE '%Fiscal%'
    OR de.entidad_referenciada LIKE '%Fiscal%'
    OR srp.autoridad_origen LIKE '%Ministerio Publico%'
    OR srp.autoridad_origen LIKE '%Ministerio Público%'
    OR de.entidad_referenciada LIKE '%Ministerio Publico%'
    OR de.entidad_referenciada LIKE '%Ministerio Público%'
  )

UNION ALL

SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.ruta_archivo AS documento_referencia,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  'ACTA' AS fuente_origen,
  CAST(a.id_acta AS CHAR) AS fuente_id,
  a.fecha AS fecha_evento,
  a.id_anio_lectivo,
  NULL AS periodo_academico_id,
  NULL AS periodo_academico,
  a.id_estudiante,
  COALESCE(p.numero_documento, a.dni_estudiante) AS dni_estudiante,
  COALESCE(NULLIF(p.nombre_completo, ''), a.estudiante_nombre, CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)) AS estudiante,
  COALESCE(vm.nivel, ne.descripcion, a.id_nivel, '') AS nivel,
  COALESCE(vm.grado_descripcion, g.descripcion, '') AS grado,
  COALESCE(vm.seccion, s.nombre_corto, '') AS seccion,
  FIELD(COALESCE(vm.id_nivel, a.id_nivel, ''), 'INI', 'PRI', 'SEC') AS nivel_orden,
  COALESCE(vm.id_grado, a.id_grado) AS grado_orden,
  COALESCE(vm.id_seccion, a.id_seccion) AS seccion_orden,
  COALESCE(fam.familiares, a.apoderado_nombre, '') AS progenitores_apoderados,
  COALESCE(NULLIF(p.direccion, ''), fam.direcciones, '') AS direccion_reporte,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cp.categoria_proteccion_id,
  cp.codigo AS categoria_codigo,
  cp.nombre AS categoria_nombre,
  cp.nivel_alerta,
  LEFT(CONCAT_WS('\n',
    CONCAT('Acta ', m.codigo, ' N. ', a.numero_acta, ': ', m.titulo),
    CONCAT('Situacion observada: ', a.situacion_observada),
    CONCAT('Reflexion formativa: ', COALESCE(a.reflexion_formativa, '')),
    CONCAT('Meta de mejora: ', COALESCE(a.meta_mejora, ''))
  ), 1500) AS resumen_caso,
  LEFT(CONCAT_WS('\n',
    CONCAT('Compromisos del estudiante: ', COALESCE(a.compromisos_estudiante, '')),
    CONCAT('Compromisos de la familia: ', COALESCE(a.compromisos_familia, '')),
    CONCAT('Accion reparadora: ', COALESCE(a.accion_reparadora, '')),
    CONCAT('Evidencias esperadas: ', COALESCE(a.evidencias_esperadas, ''))
  ), 1500) AS acciones_realizadas,
  CASE WHEN a.estado = 'CERRADO' THEN 'CERRADO' ELSE 'EN_SEGUIMIENTO' END AS estado_atencion,
  CONCAT('Acta vinculada al Oficio 028. Modelo ', m.codigo) AS observacion_reservada
FROM conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
INNER JOIN conv_documento_externo de ON de.documento_externo_id = srp.documento_externo_id
INNER JOIN tut_acta_compromiso a ON a.fecha BETWEEN prp.fecha_inicio AND prp.fecha_fin
INNER JOIN tut_acta_modelo m ON m.id_modelo = a.id_modelo
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.codigo = CASE
  WHEN m.codigo = 'M02' THEN 'FALTAS_INJUSTIFICADAS'
  WHEN m.codigo = 'M03' THEN 'RENDIMIENTO_DEFICIENTE'
  WHEN m.codigo = 'M04' THEN 'MALTRATO_VIOLENCIA'
  WHEN m.codigo = 'M06' THEN 'ADICCION_JUEGOS_CELULAR'
  WHEN m.codigo = 'M07' THEN 'HECHOS_LESIVOS'
  ELSE 'HECHOS_LESIVOS'
END
LEFT JOIN estudiante e ON e.id_estudiante = a.id_estudiante
LEFT JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN nivel_educativo ne ON ne.id_nivel = a.id_nivel
LEFT JOIN grado g ON g.id_grado = a.id_grado
LEFT JOIN seccion s ON s.id_seccion = a.id_seccion
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = a.id_estudiante AND vm.id_anio_lectivo = a.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 ASC SEPARATOR '; ') AS familiares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular ASC SEPARATOR '; ') AS celulares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion ASC SEPARATOR '; ') AS direcciones
  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 = a.id_estudiante
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND (
    srp.autoridad_origen LIKE '%Fiscal%'
    OR de.entidad_referenciada LIKE '%Fiscal%'
    OR srp.autoridad_origen LIKE '%Ministerio Publico%'
    OR srp.autoridad_origen LIKE '%Ministerio Público%'
    OR de.entidad_referenciada LIKE '%Ministerio Publico%'
    OR de.entidad_referenciada LIKE '%Ministerio Público%'
  )

UNION ALL

SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.ruta_archivo AS documento_referencia,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  'ASISTENCIA' AS fuente_origen,
  CONCAT(att.id_matricula, '-', prp.periodo_reporte_id) AS fuente_id,
  att.ultima_fecha AS fecha_evento,
  att.id_anio_lectivo,
  NULL AS periodo_academico_id,
  NULL AS periodo_academico,
  att.id_estudiante,
  att.dni_estudiante,
  att.estudiante,
  att.nivel,
  att.grado,
  att.seccion,
  FIELD(COALESCE(att.id_nivel, ''), 'INI', 'PRI', 'SEC') AS nivel_orden,
  att.id_grado AS grado_orden,
  att.id_seccion AS seccion_orden,
  COALESCE(fam.familiares, '') AS progenitores_apoderados,
  COALESCE(att.direccion, fam.direcciones, '') AS direccion_reporte,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cp.categoria_proteccion_id,
  cp.codigo AS categoria_codigo,
  cp.nombre AS categoria_nombre,
  cp.nivel_alerta,
  CONCAT('Registros de asistencia en el periodo: ', att.faltas, ' falta(s), ', att.tardanzas, ' tardanza(s), ', att.salidas, ' salida(s).') AS resumen_caso,
  CONCAT('Fechas observadas: ', att.fechas_observadas) AS acciones_realizadas,
  'IDENTIFICADO' AS estado_atencion,
  'Asistencia vinculada al Oficio 028.' AS observacion_reservada
FROM conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
INNER JOIN conv_documento_externo de ON de.documento_externo_id = srp.documento_externo_id
INNER JOIN (
  SELECT
    ae.id_matricula,
    m.id_anio_lectivo,
    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,
    vm.id_nivel,
    vm.id_grado,
    vm.id_seccion,
    p.direccion,
    MIN(ae.fecha) AS primera_fecha,
    MAX(ae.fecha) AS ultima_fecha,
    SUM(ea.codigo = 'FALTA') AS faltas,
    SUM(ea.codigo = 'TARDANZA') AS tardanzas,
    SUM(ea.codigo = 'SALIDA') AS salidas,
    GROUP_CONCAT(DISTINCT CONCAT(ae.fecha, ': ', ea.descripcion) ORDER BY ae.fecha SEPARATOR '; ') AS fechas_observadas
  FROM asistencia_estudiante ae
  INNER JOIN estado_asistencia ea ON ea.id_estado_asistencia = ae.id_estado_asistencia
  INNER JOIN matricula m ON m.id_matricula = ae.id_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 vw_comano_matriculados vm ON vm.id_matricula = m.id_matricula
  WHERE ea.codigo IN ('FALTA', 'TARDANZA', 'SALIDA')
  GROUP BY ae.id_matricula, m.id_anio_lectivo, e.id_estudiante, p.numero_documento, estudiante, vm.nivel, vm.grado_descripcion, vm.grado, vm.seccion, vm.id_nivel, vm.id_grado, vm.id_seccion, p.direccion
) att ON att.primera_fecha <= prp.fecha_fin AND att.ultima_fecha >= prp.fecha_inicio
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.codigo = 'FALTAS_INJUSTIFICADAS'
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 ASC SEPARATOR '; ') AS familiares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular ASC SEPARATOR '; ') AS celulares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion ASC SEPARATOR '; ') AS direcciones
  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 = att.id_estudiante
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND (
    srp.autoridad_origen LIKE '%Fiscal%'
    OR de.entidad_referenciada LIKE '%Fiscal%'
    OR srp.autoridad_origen LIKE '%Ministerio Publico%'
    OR srp.autoridad_origen LIKE '%Ministerio Público%'
    OR de.entidad_referenciada LIKE '%Ministerio Publico%'
    OR de.entidad_referenciada LIKE '%Ministerio Público%'
  )

UNION ALL

SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.ruta_archivo AS documento_referencia,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  'RENDIMIENTO' AS fuente_origen,
  CONCAT(r.id_matricula, '-', r.id_periodo_academico) AS fuente_id,
  COALESCE(pa.fecha_fin, prp.fecha_fin) AS fecha_evento,
  r.id_anio_lectivo,
  pa.id_periodo_academico AS periodo_academico_id,
  pa.descripcion AS periodo_academico,
  e.id_estudiante,
  r.dni AS dni_estudiante,
  r.estudiante,
  r.nivel,
  r.grado,
  r.seccion,
  FIELD(r.id_nivel, 'INI', 'PRI', 'SEC') AS nivel_orden,
  au_r.id_grado AS grado_orden,
  au_r.id_seccion AS seccion_orden,
  COALESCE(fam.familiares, '') AS progenitores_apoderados,
  COALESCE(NULLIF(p.direccion, ''), fam.direcciones, '') AS direccion_reporte,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cp.categoria_proteccion_id,
  cp.codigo AS categoria_codigo,
  cp.nombre AS categoria_nombre,
  CASE WHEN r.nivel_riesgo IN ('CRITICO', 'ALTO') THEN 'ALTO' ELSE cp.nivel_alerta END AS nivel_alerta,
  CONCAT('Rendimiento academico ', pa.descripcion, ': ', r.competencias_b, ' competencia(s) en B y ', r.competencias_c, ' competencia(s) en C. Nivel de riesgo: ', r.nivel_riesgo, '.') AS resumen_caso,
  'Requiere comunicacion a familia, seguimiento tutorial, recuperacion pedagogica y toma de decisiones oportunas.' AS acciones_realizadas,
  'IDENTIFICADO' AS estado_atencion,
  'Rendimiento academico vinculado al Oficio 028.' AS observacion_reservada
FROM conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
INNER JOIN conv_documento_externo de ON de.documento_externo_id = srp.documento_externo_id
INNER JOIN periodo_academico pa ON pa.fecha_inicio <= prp.fecha_fin AND pa.fecha_fin >= prp.fecha_inicio
INNER JOIN vw_comano_riesgo_academico r ON r.id_periodo_academico = pa.id_periodo_academico
INNER JOIN matricula m ON m.id_matricula = r.id_matricula
INNER JOIN aula au_r ON au_r.id_aula = m.id_aula
INNER JOIN estudiante e ON e.id_estudiante = m.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.codigo = 'RENDIMIENTO_DEFICIENTE'
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 ASC SEPARATOR '; ') AS familiares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular ASC SEPARATOR '; ') AS celulares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion ASC SEPARATOR '; ') AS direcciones
  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
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND r.nivel_riesgo IN ('ALTO', 'CRITICO')
  AND (
    srp.autoridad_origen LIKE '%Fiscal%'
    OR de.entidad_referenciada LIKE '%Fiscal%'
    OR srp.autoridad_origen LIKE '%Ministerio Publico%'
    OR srp.autoridad_origen LIKE '%Ministerio Público%'
    OR de.entidad_referenciada LIKE '%Ministerio Publico%'
    OR de.entidad_referenciada LIKE '%Ministerio Público%'
  )

UNION ALL

SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.ruta_archivo AS documento_referencia,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  'INCIDENCIA' AS fuente_origen,
  CAST(i.id_incidencia AS CHAR) AS fuente_id,
  i.fecha AS fecha_evento,
  i.id_anio_lectivo,
  NULL AS periodo_academico_id,
  NULL AS periodo_academico,
  ie.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, ne.descripcion, i.id_nivel, '') AS nivel,
  COALESCE(vm.grado_descripcion, g.descripcion, '') AS grado,
  COALESCE(vm.seccion, s.nombre_corto, '') AS seccion,
  FIELD(COALESCE(vm.id_nivel, i.id_nivel, ''), 'INI', 'PRI', 'SEC') AS nivel_orden,
  COALESCE(vm.id_grado, i.id_grado) AS grado_orden,
  COALESCE(vm.id_seccion, i.id_seccion) AS seccion_orden,
  COALESCE(fam.familiares, '') AS progenitores_apoderados,
  COALESCE(NULLIF(p.direccion, ''), fam.direcciones, '') AS direccion_reporte,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cp.categoria_proteccion_id,
  cp.codigo AS categoria_codigo,
  cp.nombre AS categoria_nombre,
  cp.nivel_alerta,
  LEFT(CONCAT_WS('\n',
    CONCAT('Incidencia N. ', i.numero_ficha, ' - ', i.nivel_atencion),
    i.descripcion_hecho,
    CONCAT('Reportante: ', COALESCE(i.reportante_nombre, ''))
  ), 1500) AS resumen_caso,
  LEFT(CONCAT_WS('\n',
    CONCAT('Compromisos: ', COALESCE(i.compromisos, '')),
    CONCAT('Acciones restaurativas: ', COALESCE(i.acciones_restaurativas, '')),
    CONCAT('Seguimiento tutorial: ', COALESCE(i.seguimiento_tutorial, ''))
  ), 1500) AS acciones_realizadas,
  CASE WHEN i.estado = 'CERRADO' THEN 'CERRADO' ELSE 'EN_SEGUIMIENTO' END AS estado_atencion,
  'Incidencia vinculada al Oficio 028.' AS observacion_reservada
FROM conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
INNER JOIN conv_documento_externo de ON de.documento_externo_id = srp.documento_externo_id
INNER JOIN tut_incidencia i ON i.fecha BETWEEN prp.fecha_inicio AND prp.fecha_fin
INNER JOIN tut_incidencia_estudiante ie ON ie.id_incidencia = i.id_incidencia
INNER JOIN estudiante e ON e.id_estudiante = ie.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.codigo = CASE WHEN i.nivel_atencion = 'VIOLENCIA_FISICA' THEN 'MALTRATO_VIOLENCIA' ELSE 'HECHOS_LESIVOS' END
LEFT JOIN nivel_educativo ne ON ne.id_nivel = i.id_nivel
LEFT JOIN grado g ON g.id_grado = i.id_grado
LEFT JOIN seccion s ON s.id_seccion = i.id_seccion
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = ie.id_estudiante AND vm.id_anio_lectivo = i.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 ASC SEPARATOR '; ') AS familiares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular ASC SEPARATOR '; ') AS celulares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion ASC SEPARATOR '; ') AS direcciones
  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 = ie.id_estudiante
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND i.nivel_atencion IN ('GRAVE', 'VIOLENCIA_FISICA')
  AND (
    srp.autoridad_origen LIKE '%Fiscal%'
    OR de.entidad_referenciada LIKE '%Fiscal%'
    OR srp.autoridad_origen LIKE '%Ministerio Publico%'
    OR srp.autoridad_origen LIKE '%Ministerio Público%'
    OR de.entidad_referenciada LIKE '%Ministerio Publico%'
    OR de.entidad_referenciada LIKE '%Ministerio Público%'
  )

UNION ALL

SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.ruta_archivo AS documento_referencia,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  'SISEVE' AS fuente_origen,
  CAST(sv.reporte_siseve_id AS CHAR) AS fuente_id,
  sv.fecha_registro AS fecha_evento,
  sv.id_anio_lectivo,
  NULL AS periodo_academico_id,
  NULL AS periodo_academico,
  sv.id_estudiante,
  p.numero_documento AS dni_estudiante,
  COALESCE(NULLIF(p.nombre_completo, ''), CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres), 'Estudiante no individualizado') AS estudiante,
  COALESCE(vm.nivel, '') AS nivel,
  COALESCE(vm.grado_descripcion, vm.grado, '') AS grado,
  COALESCE(vm.seccion, '') AS seccion,
  FIELD(COALESCE(vm.id_nivel, ''), 'INI', 'PRI', 'SEC') AS nivel_orden,
  vm.id_grado AS grado_orden,
  vm.id_seccion AS seccion_orden,
  COALESCE(fam.familiares, '') AS progenitores_apoderados,
  COALESCE(NULLIF(p.direccion, ''), fam.direcciones, '') AS direccion_reporte,
  COALESCE(fam.celulares, '') AS celulares_familia,
  cp.categoria_proteccion_id,
  cp.codigo AS categoria_codigo,
  cp.nombre AS categoria_nombre,
  cp.nivel_alerta,
  LEFT(CONCAT_WS('\n',
    CONCAT('SISEVE: ', COALESCE(sv.codigo_caso, 'Sin codigo'), ' - ', sv.tipo_caso),
    sv.descripcion_hecho,
    CONCAT('Estado: ', sv.estado)
  ), 1500) AS resumen_caso,
  sv.acciones_realizadas AS acciones_realizadas,
  CASE WHEN sv.estado = 'CERRADO' THEN 'CERRADO' WHEN sv.estado = 'DERIVADO' THEN 'DERIVADO' ELSE 'EN_SEGUIMIENTO' END AS estado_atencion,
  COALESCE(sv.archivo_referencia, 'Reporte SISEVE vinculado al Oficio 028.') AS observacion_reservada
FROM conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
INNER JOIN conv_documento_externo de ON de.documento_externo_id = srp.documento_externo_id
INNER JOIN conv_reporte_siseve sv ON sv.fecha_registro BETWEEN prp.fecha_inicio AND prp.fecha_fin
LEFT JOIN estudiante e ON e.id_estudiante = sv.id_estudiante
LEFT JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.codigo = 'MALTRATO_VIOLENCIA'
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = sv.id_estudiante AND vm.id_anio_lectivo = sv.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 ASC SEPARATOR '; ') AS familiares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.celular, '') ORDER BY fp.celular ASC SEPARATOR '; ') AS celulares,
         GROUP_CONCAT(DISTINCT NULLIF(fp.direccion, '') ORDER BY fp.direccion ASC SEPARATOR '; ') AS direcciones
  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 = sv.id_estudiante
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND (
    srp.autoridad_origen LIKE '%Fiscal%'
    OR de.entidad_referenciada LIKE '%Fiscal%'
    OR srp.autoridad_origen LIKE '%Ministerio Publico%'
    OR srp.autoridad_origen LIKE '%Ministerio Público%'
    OR de.entidad_referenciada LIKE '%Ministerio Publico%'
    OR de.entidad_referenciada LIKE '%Ministerio Público%'
  );