/* ============================================================================
   COMANO 0003 - Complemento Oficio Circular N. 028-2026
   Fuente: oficio_028_2026_reportes_bienestar_mysql.sql y oficio circular 028.
   Objetivo: integrar la fuente CITACION al reporte de bienestar/Fiscalia para
             cubrir el inciso l) inconcurrencia de padres a citaciones.
   ============================================================================ */

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

ALTER TABLE conv_documento_externo_fuente
  MODIFY fuente_origen ENUM('EXPEDIENTE','ACTA','ASISTENCIA','CITACION','RENDIMIENTO','INCIDENCIA','SISEVE') NOT NULL;

ALTER TABLE conv_reporte_proteccion_evidencia
  MODIFY fuente_origen ENUM('EXPEDIENTE','ACTA','ASISTENCIA','CITACION','RENDIMIENTO','INCIDENCIA','SISEVE') NOT NULL;

INSERT IGNORE INTO conv_documento_externo_fuente (documento_externo_id, fuente_origen, descripcion)
SELECT documento_externo_id, 'CITACION',
       'Citaciones institucionales a padres, madres o apoderados no atendidas sin justificacion, segun inciso l del Oficio 028-2026.'
FROM conv_documento_externo
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

UPDATE conv_documento_externo
SET ruta_archivo = 'storage/documentos_externos/oficio_circular_028_2026_grsm_dre_dugel_h.pdf',
    resumen = 'Solicita remitir nombres y apellidos de estudiantes, progenitores y/o apoderados, direccion y numero de celular de estudiantes inmersos en casos de riesgo, desproteccion familiar, violencia, repitencia, desercion, inasistencias injustificadas, consumo de sustancias, rendimiento deficiente, embarazo precoz, autolesiones, adiccion a juegos/celular, intentos de suicidio, otros hechos lesivos e inconcurrencia familiar.'
WHERE numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

UPDATE conv_solicitud_reporte_proteccion
SET datos_solicitados = 'Nombres y apellidos de estudiantes; nombres y apellidos de progenitores y/o apoderados; direccion; numero de celular; tipo de caso segun incisos a-l del Oficio 028-2026; acciones de atencion y seguimiento.',
    finalidad = 'Atender el interes superior de ninas, ninos y adolescentes y cumplir la disposicion del Ministerio Publico canalizada por UGEL Huallaga.'
WHERE codigo_solicitud = 'SOL-UGEL-MP-028-2026';

UPDATE conv_periodo_reporte_proteccion prp
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = prp.solicitud_reporte_id
SET prp.fecha_fin = '2026-12-15',
    prp.fecha_limite_envio = '2026-12-15'
WHERE srp.codigo_solicitud = 'SOL-UGEL-MP-028-2026'
  AND prp.numero_reporte = 4
  AND YEAR(prp.fecha_inicio) = 2026;

CREATE OR REPLACE VIEW vw_conv_evidencias_fiscalia_citaciones 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,
  'CITACION' AS fuente_origen,
  CAST(cf.citacion_id AS CHAR) AS fuente_id,
  DATE(cf.fecha_programada) AS fecha_evento,
  COALESCE(vm.id_anio_lectivo, al.id_anio_lectivo) AS id_anio_lectivo,
  NULL AS periodo_academico_id,
  NULL AS periodo_academico,
  cf.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(citfam.familiares, fam.familiares, '') AS progenitores_apoderados,
  COALESCE(NULLIF(p.direccion, ''), citfam.direcciones, fam.direcciones, '') AS direccion_reporte,
  COALESCE(citfam.celulares, 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('Citacion no atendida sin justificacion. Fecha programada: ', DATE_FORMAT(cf.fecha_programada, '%d/%m/%Y %H:%i')),
    CONCAT('Tipo: ', REPLACE(cf.tipo_citacion, '_', ' '), ' | Medio: ', REPLACE(cf.medio_notificacion, '_', ' ')),
    CONCAT('Motivo: ', cf.motivo),
    CASE WHEN exp.codigo_expediente IS NOT NULL THEN CONCAT('Expediente vinculado: ', exp.codigo_expediente) ELSE NULL END
  ), 1500) AS resumen_caso,
  LEFT(CONCAT_WS('\n',
    CONCAT('Estado de citacion: ', REPLACE(cf.estado, '_', ' ')),
    CASE WHEN cf.justificacion_inasistencia IS NOT NULL AND cf.justificacion_inasistencia <> '' THEN CONCAT('Justificacion registrada: ', cf.justificacion_inasistencia) ELSE 'Sin justificacion registrada.' END,
    CASE WHEN resp.id_persona IS NOT NULL THEN CONCAT('Responsable: ', COALESCE(NULLIF(resp.nombre_completo, ''), CONCAT_WS(' ', resp.apellido_paterno, resp.apellido_materno, resp.nombres))) ELSE NULL END,
    CASE WHEN cf.observacion IS NOT NULL AND cf.observacion <> '' THEN CONCAT('Observacion: ', cf.observacion) ELSE NULL END
  ), 1500) AS acciones_realizadas,
  'IDENTIFICADO' AS estado_atencion,
  'Inconcurrencia de padres/apoderados a citacion institucional vinculada al Oficio 028-2026.' 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_citacion_familia cf ON DATE(cf.fecha_programada) BETWEEN prp.fecha_inicio AND prp.fecha_fin
INNER JOIN estudiante e ON e.id_estudiante = cf.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
LEFT JOIN conv_expediente_convivencia exp ON exp.expediente_id = cf.expediente_id
LEFT JOIN persona resp ON resp.id_persona = cf.responsable_id_persona
LEFT JOIN anio_lectivo al ON YEAR(cf.fecha_programada) = al.anio AND al.estado = 1
LEFT JOIN vw_comano_matriculados vm ON vm.id_estudiante = cf.id_estudiante AND vm.id_anio_lectivo = al.id_anio_lectivo
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.codigo = 'INCONCURRENCIA_PADRES'
LEFT JOIN (
  SELECT
    cfp.citacion_id,
    GROUP_CONCAT(DISTINCT CONCAT(COALESCE(NULLIF(pp.nombre_completo, ''), CONCAT_WS(' ', pp.apellido_paterno, pp.apellido_materno, pp.nombres)), ' [', cfp.condicion, ']') ORDER BY cfp.condicion SEPARATOR '; ') AS familiares,
    GROUP_CONCAT(DISTINCT NULLIF(pp.celular, '') ORDER BY pp.celular SEPARATOR '; ') AS celulares,
    GROUP_CONCAT(DISTINCT NULLIF(pp.direccion, '') ORDER BY pp.direccion SEPARATOR '; ') AS direcciones
  FROM conv_citacion_familia_participante cfp
  LEFT JOIN persona pp ON pp.id_persona = cfp.id_persona
  GROUP BY cfp.citacion_id
) citfam ON citfam.citacion_id = cf.citacion_id
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 = cf.id_estudiante
WHERE prp.activo = 1
  AND srp.estado = 'VIGENTE'
  AND cf.estado = 'NO_ASISTIO_INJUSTIFICADO'
  AND de.numero_documento = 'OFICIO CIRCULAR N. 028-2026-GRSM-DRE-DUGEL-H';

CREATE OR REPLACE VIEW vw_conv_bienestar_oficio028_resumen AS
SELECT
  rp.reporte_proteccion_id,
  rp.codigo_reporte,
  rp.estado,
  prp.numero_reporte,
  prp.nombre AS periodo,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  ev.fuente_origen,
  COALESCE(cp.literal, '') AS literal,
  COALESCE(ev.categoria_nombre, cp.nombre, ev.categoria_codigo, ev.fuente_origen) AS categoria,
  COUNT(*) AS total_evidencias,
  COUNT(DISTINCT ev.id_estudiante) AS total_estudiantes
FROM conv_reporte_proteccion rp
INNER JOIN conv_periodo_reporte_proteccion prp ON prp.periodo_reporte_id = rp.periodo_reporte_id
INNER JOIN conv_solicitud_reporte_proteccion srp ON srp.solicitud_reporte_id = rp.solicitud_reporte_id
INNER JOIN conv_reporte_proteccion_evidencia ev ON ev.reporte_proteccion_id = rp.reporte_proteccion_id
LEFT JOIN conv_categoria_proteccion_estudiante cp ON cp.categoria_proteccion_id = ev.categoria_proteccion_id
WHERE srp.codigo_solicitud = 'SOL-UGEL-MP-028-2026'
GROUP BY
  rp.reporte_proteccion_id, rp.codigo_reporte, rp.estado,
  prp.numero_reporte, prp.nombre, prp.fecha_inicio, prp.fecha_fin, prp.fecha_limite_envio,
  ev.fuente_origen, cp.literal, categoria;