/* ============================================================================
   COMANO 0003 - Alertas de vencimiento para reportes a Fiscalia
   Regla: avisar en el año actual cuando falten 7 dias o menos para remitir
          un reporte solicitado por Fiscalia/Ministerio Publico y no exista
          reporte en estado ENVIADO. Los vencidos pendientes siguen visibles.
   ============================================================================ */

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

CREATE OR REPLACE VIEW vw_conv_alertas_reporte_fiscalia AS
SELECT
  prp.periodo_reporte_id,
  srp.solicitud_reporte_id,
  srp.codigo_solicitud,
  de.numero_documento,
  de.entidad_emisora,
  de.entidad_referenciada,
  srp.autoridad_origen,
  prp.numero_reporte,
  prp.nombre AS periodo,
  prp.fecha_inicio,
  prp.fecha_fin,
  prp.fecha_limite_envio,
  YEAR(prp.fecha_limite_envio) AS anio_reporte,
  DATEDIFF(prp.fecha_limite_envio, CURDATE()) AS dias_restantes,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM conv_reporte_proteccion rp
      WHERE rp.periodo_reporte_id = prp.periodo_reporte_id
        AND rp.estado = 'ENVIADO'
    ) THEN 'ENVIADO'
    WHEN DATEDIFF(prp.fecha_limite_envio, CURDATE()) < 0 THEN 'VENCIDO'
    WHEN DATEDIFF(prp.fecha_limite_envio, CURDATE()) = 0 THEN 'VENCE_HOY'
    WHEN DATEDIFF(prp.fecha_limite_envio, CURDATE()) BETWEEN 1 AND 7 THEN 'FALTA_UNA_SEMANA'
    ELSE 'PROGRAMADO'
  END AS estado_alerta,
  CASE
    WHEN YEAR(prp.fecha_limite_envio) = YEAR(CURDATE())
      AND DATEDIFF(prp.fecha_limite_envio, CURDATE()) <= 7
      AND NOT EXISTS (
        SELECT 1
        FROM conv_reporte_proteccion rp
        WHERE rp.periodo_reporte_id = prp.periodo_reporte_id
          AND rp.estado = 'ENVIADO'
      )
    THEN 1 ELSE 0
  END AS requiere_aviso,
  (
    SELECT rp.codigo_reporte
    FROM conv_reporte_proteccion rp
    WHERE rp.periodo_reporte_id = prp.periodo_reporte_id
    ORDER BY rp.fecha_elaboracion DESC, rp.reporte_proteccion_id DESC
    LIMIT 1
  ) AS ultimo_codigo_reporte,
  (
    SELECT rp.estado
    FROM conv_reporte_proteccion rp
    WHERE rp.periodo_reporte_id = prp.periodo_reporte_id
    ORDER BY rp.fecha_elaboracion DESC, rp.reporte_proteccion_id DESC
    LIMIT 1
  ) AS ultimo_estado_reporte,
  (
    SELECT COUNT(*)
    FROM conv_reporte_proteccion_evidencia ev
    INNER JOIN conv_reporte_proteccion rp ON rp.reporte_proteccion_id = ev.reporte_proteccion_id
    WHERE rp.periodo_reporte_id = prp.periodo_reporte_id
      AND rp.reporte_proteccion_id = (
        SELECT rp2.reporte_proteccion_id
        FROM conv_reporte_proteccion rp2
        WHERE rp2.periodo_reporte_id = prp.periodo_reporte_id
        ORDER BY rp2.fecha_elaboracion DESC, rp2.reporte_proteccion_id DESC
        LIMIT 1
      )
  ) AS total_estudiantes_ultimo_reporte
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
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%'
  );