-- ================================================================
-- GEOSTARK NOMINA - VALIDACION V7.3.3 POST SINCRONIZACION
-- No modifica datos. Compatible MariaDB/MySQL.
-- ================================================================

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

SELECT 'Tasas interés BCV importadas' AS revision, COUNT(*) AS total, MIN(periodo_ym) AS desde, MAX(periodo_ym) AS hasta
FROM nomina_tasas_interes_bcv;

SELECT 'Historial salarial sincronizado' AS revision, COUNT(*) AS total
FROM nomina_salario_historial;

SELECT 'Historial salarial con tasa BCV en cero' AS revision, COUNT(*) AS total
FROM nomina_salario_historial
WHERE IFNULL(tasa_bcv_usada,0)=0;

SELECT 'Eventos prestaciones registrados' AS revision, COUNT(*) AS total
FROM nomina_prestaciones_eventos;

SELECT 'Eventos automáticos sin tasa BCV congelada' AS revision, COUNT(*) AS total
FROM nomina_prestaciones_eventos
WHERE tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL')
  AND IFNULL(tasa_bcv_usada,0)=0;

SELECT 'Eventos automáticos desde historial' AS revision, COUNT(*) AS total
FROM nomina_prestaciones_eventos
WHERE tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL')
  AND fuente_salario LIKE '%HISTORIAL%';

SELECT 'Eventos viejos V7_1 / fuente CARGO' AS revision, COUNT(*) AS total
FROM nomina_prestaciones_eventos
WHERE origen_registro LIKE '%V7_1%'
   OR fuente_salario = 'CARGO';

SELECT 'Intereses registrados' AS revision, COUNT(*) AS total, ROUND(SUM(interes_bs),2) AS total_interes_bs
FROM nomina_prestaciones_intereses;

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.tasa_id IS NOT NULL AND t.id IS NULL;

SELECT 'Trabajadores activos sin fecha ingreso válida' 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 fecha_ingreso > 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 fecha_ingreso > CURDATE()
  )
ORDER BY apellido, nombre;
