-- ================================================================
-- GEOSTARK NOMINA - VALIDACION V8.5 CORRIDA VIGENTE POR PERIODO
-- No modifica datos. Compatible MariaDB/MySQL.
-- Objetivo: detectar periodos con varias corridas y confirmar cual es
-- la ultima corrida no anulada que debe avanzar/cerrarse.
-- ================================================================

SELECT 'Periodos con mas de una corrida no anulada' AS revision,
       p.codigo AS periodo,
       COUNT(nc.id) AS corridas_no_anuladas,
       MAX(nc.id) AS corrida_vigente_sugerida,
       GROUP_CONCAT(CONCAT('#', nc.id, ' ', nc.estado) ORDER BY nc.id DESC SEPARATOR ' | ') AS corridas
FROM periodos_nomina p
JOIN nomina_corridas nc ON nc.periodo_id = p.id AND nc.estado <> 'ANULADA'
GROUP BY p.id, p.codigo
HAVING COUNT(nc.id) > 1
ORDER BY p.fecha_desde DESC, p.codigo;

SELECT 'Corridas superadas - no cerrar' AS revision,
       p.codigo AS periodo,
       nc.id AS corrida_id,
       nc.estado,
       nc.fecha_calculo,
       ult.latest_id AS corrida_vigente,
       COALESCE(t.total_neto,0) AS total_neto,
       COALESCE(v.total_vacaciones,0) AS total_vacaciones_a010
FROM nomina_corridas nc
JOIN periodos_nomina p ON p.id = nc.periodo_id
JOIN (
    SELECT periodo_id, MAX(id) AS latest_id
    FROM nomina_corridas
    WHERE estado <> 'ANULADA'
    GROUP BY periodo_id
) ult ON ult.periodo_id = nc.periodo_id
LEFT JOIN (
    SELECT corrida_id, SUM(neto_pagar) AS total_neto
    FROM nomina_detalle
    GROUP BY corrida_id
) t ON t.corrida_id = nc.id
LEFT JOIN (
    SELECT m.corrida_id, SUM(m.monto) AS total_vacaciones
    FROM nomina_movimientos m
    JOIN conceptos_nomina c ON c.id = m.concepto_id
    WHERE c.codigo = 'A-010'
    GROUP BY m.corrida_id
) v ON v.corrida_id = nc.id
WHERE nc.estado <> 'ANULADA'
  AND nc.id < ult.latest_id
ORDER BY p.fecha_desde DESC, nc.id DESC;

SELECT 'Corrida vigente por periodo' AS revision,
       p.codigo AS periodo,
       nc.id AS corrida_id,
       nc.estado,
       nc.fecha_calculo,
       COALESCE(t.trabajadores,0) AS trabajadores,
       COALESCE(t.total_asig,0) AS total_asignaciones,
       COALESCE(t.total_ded,0) AS total_deducciones,
       COALESCE(t.total_neto,0) AS total_neto,
       COALESCE(v.total_vacaciones,0) AS total_vacaciones_a010
FROM periodos_nomina p
JOIN (
    SELECT periodo_id, MAX(id) AS latest_id
    FROM nomina_corridas
    WHERE estado <> 'ANULADA'
    GROUP BY periodo_id
) ult ON ult.periodo_id = p.id
JOIN nomina_corridas nc ON nc.id = ult.latest_id
LEFT JOIN (
    SELECT corrida_id,
           COUNT(DISTINCT persona_id) AS trabajadores,
           SUM(total_asignaciones) AS total_asig,
           SUM(total_deducciones) AS total_ded,
           SUM(neto_pagar) AS total_neto
    FROM nomina_detalle
    GROUP BY corrida_id
) t ON t.corrida_id = nc.id
LEFT JOIN (
    SELECT m.corrida_id, SUM(m.monto) AS total_vacaciones
    FROM nomina_movimientos m
    JOIN conceptos_nomina c ON c.id = m.concepto_id
    WHERE c.codigo = 'A-010'
    GROUP BY m.corrida_id
) v ON v.corrida_id = nc.id
ORDER BY p.fecha_desde DESC, p.codigo;

SELECT 'Periodos cerrados sin corrida vigente cerrada' AS revision,
       p.codigo AS periodo,
       p.estado AS estado_periodo,
       nc.id AS corrida_vigente,
       nc.estado AS estado_corrida_vigente
FROM periodos_nomina p
JOIN (
    SELECT periodo_id, MAX(id) AS latest_id
    FROM nomina_corridas
    WHERE estado <> 'ANULADA'
    GROUP BY periodo_id
) ult ON ult.periodo_id = p.id
JOIN nomina_corridas nc ON nc.id = ult.latest_id
WHERE p.estado = 'CERRADO'
  AND nc.estado <> 'CERRADA'
ORDER BY p.fecha_desde DESC;
