-- ================================================================
-- GEOSTARK NOMINA - VALIDACION V7.3.2 RECONSTRUCCION PRESTACIONES
-- No modifica datos. Compatible MariaDB/MySQL.
-- ================================================================

SELECT 'Parametros legales activos' AS revision, COUNT(*) AS total
FROM nomina_parametros_legales
WHERE activo = 1;

SELECT 'Tasas interes BCV importadas' AS revision,
       COUNT(*) AS registros,
       MIN(periodo_ym) AS desde_mes,
       MAX(periodo_ym) AS hasta_mes
FROM nomina_tasas_interes_bcv;

SELECT 'Corridas cerradas disponibles para historial salarial' AS revision, COUNT(*) AS total
FROM nomina_corridas
WHERE estado='CERRADA';

SELECT 'Historial salarial sincronizado' AS revision, COUNT(*) AS total,
       SUM(CASE WHEN tasa_bcv_usada IS NULL OR tasa_bcv_usada <= 0 THEN 1 ELSE 0 END) AS sin_tasa_bcv
FROM nomina_salario_historial;

SELECT 'Trabajadores activos sin fecha ingreso valida' AS revision, COUNT(*) AS total
FROM personas
WHERE IFNULL(estatus_laboral,'ACTIVO') = 'ACTIVO'
  AND (
        fecha_ingreso IS NULL
     OR CAST(fecha_ingreso AS CHAR) = ''
     OR CAST(fecha_ingreso AS CHAR) = '0000-00-00'
     OR CAST(fecha_ingreso AS CHAR) > CURDATE()
  );

SELECT id, cedula, nombre, apellido, fecha_ingreso, estatus_laboral
FROM personas
WHERE IFNULL(estatus_laboral,'ACTIVO') = 'ACTIVO'
  AND (
        fecha_ingreso IS NULL
     OR CAST(fecha_ingreso AS CHAR) = ''
     OR CAST(fecha_ingreso AS CHAR) = '0000-00-00'
     OR CAST(fecha_ingreso AS CHAR) > CURDATE()
  )
ORDER BY apellido, nombre;

SELECT 'Eventos automaticos prestaciones' AS revision,
       COUNT(*) AS total,
       SUM(CASE WHEN tasa_bcv_usada IS NULL OR tasa_bcv_usada <= 0 THEN 1 ELSE 0 END) AS sin_tasa_bcv,
       SUM(CASE WHEN fuente_salario LIKE '%HISTORIAL%' THEN 1 ELSE 0 END) AS desde_historial,
       SUM(CASE WHEN fuente_salario='CARGO' OR fuente_salario='REFERENCIAL' THEN 1 ELSE 0 END) AS desde_cargo_referencial
FROM nomina_prestaciones_eventos
WHERE estado='REGISTRADO'
  AND tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL');

SELECT origen_registro, fuente_salario, COUNT(*) AS total,
       SUM(monto_bs) AS total_bs,
       SUM(CASE WHEN tasa_bcv_usada IS NULL OR tasa_bcv_usada <= 0 THEN 1 ELSE 0 END) AS sin_tasa_bcv
FROM nomina_prestaciones_eventos
WHERE estado='REGISTRADO'
GROUP BY origen_registro, fuente_salario
ORDER BY total DESC;

SELECT 'Intereses prestaciones registrados' AS revision,
       COUNT(*) AS registros,
       ROUND(SUM(interes_bs), 2) AS total_interes_bs,
       SUM(CASE WHEN tasa_id IS NULL OR tasa_id <= 0 THEN 1 ELSE 0 END) AS sin_tasa_id
FROM nomina_prestaciones_intereses
WHERE estado='REGISTRADO';

SELECT 'Intereses con tasa_id inexistente' AS revision, COUNT(*) AS total
FROM nomina_prestaciones_intereses i
LEFT JOIN nomina_tasas_interes_bcv t ON t.id = i.tasa_id
WHERE i.estado='REGISTRADO'
  AND (i.tasa_id IS NULL OR t.id IS NULL);

SELECT 'Backup v7.3.2 eventos' AS revision,
       CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name='backup_v732_nomina_prestaciones_eventos')
            THEN (SELECT COUNT(*) FROM backup_v732_nomina_prestaciones_eventos)
            ELSE 0 END AS total;

SELECT 'Backup v7.3.2 intereses' AS revision,
       CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name='backup_v732_nomina_prestaciones_intereses')
            THEN (SELECT COUNT(*) FROM backup_v732_nomina_prestaciones_intereses)
            ELSE 0 END AS total;
