-- ==========================================================
-- GEOSTARK NOMINA - VALIDACION V7.3.1 BLINDAJE PRESTACIONES - SIN DATE CERO
-- No modifica datos. Compatible MariaDB/MySQL.
-- ==========================================================

SELECT 'Trabajadores activos sin fecha de 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) > DATE_FORMAT(CURDATE(), '%Y-%m-%d'));

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) > DATE_FORMAT(CURDATE(), '%Y-%m-%d'))
ORDER BY apellido, nombre;

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

SELECT persona_id, COUNT(*) AS registros,
       MIN(fecha_desde) AS desde,
       MAX(fecha_hasta) AS hasta,
       MAX(tasa_bcv_usada) AS ultima_tasa_bcv
FROM nomina_salario_historial
GROUP BY persona_id
ORDER BY registros DESC, persona_id;

SELECT 'Eventos prestaciones registrados' AS revision, tipo, COUNT(*) AS registros, ROUND(SUM(monto_bs),2) AS total_bs
FROM nomina_prestaciones_eventos
WHERE estado='REGISTRADO'
GROUP BY tipo
ORDER BY tipo;

SELECT 'Intereses prestaciones registrados' AS revision,
       COUNT(*) AS registros,
       ROUND(SUM(interes_bs),2) AS total_interes_bs,
       MIN(periodo_ym) AS desde_mes,
       MAX(periodo_ym) AS hasta_mes
FROM nomina_prestaciones_intereses
WHERE estado='REGISTRADO';

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

SELECT 'Snapshots trabajador huerfanos' AS revision, COUNT(*) AS total
FROM nomina_corrida_trabajador_snapshot s
LEFT JOIN nomina_corridas c ON c.id=s.corrida_id
WHERE c.id IS NULL;

SELECT 'Snapshots conceptos huerfanos' AS revision, COUNT(*) AS total
FROM nomina_corrida_conceptos_snapshot s
LEFT JOIN nomina_corridas c ON c.id=s.corrida_id
WHERE c.id IS NULL;

SELECT 'Snapshots calendario huerfanos' AS revision, COUNT(*) AS total
FROM nomina_corrida_calendario_snapshot s
LEFT JOIN nomina_corridas c ON c.id=s.corrida_id
WHERE c.id IS NULL;

SELECT 'Posibles textos UTF/mojibake personas' AS revision, COUNT(*) AS total
FROM personas
WHERE nombre LIKE '%Ã%' OR apellido LIKE '%Ã%' OR direccion LIKE '%Ã%' OR departamento LIKE '%Ã%' OR area LIKE '%Ã%'
   OR nombre LIKE '%Â%' OR apellido LIKE '%Â%' OR direccion LIKE '%Â%' OR departamento LIKE '%Â%' OR area LIKE '%Â%';

SELECT 'Eventos con tasa BCV no registrada' AS revision, COUNT(*) AS total
FROM nomina_prestaciones_eventos
WHERE estado='REGISTRADO'
  AND tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL','AJUSTE_HISTORICO')
  AND IFNULL(tasa_bcv_usada,0)=0;
