-- GeneaCase Sprint 19: QA de mapa geográfico y rutas migratorias.
-- Todas las consultas deben devolver cero.

SELECT 'ubicaciones_huerfanas_caso' AS control,COUNT(*) AS errores
FROM gene_mapa_ubicaciones u
LEFT JOIN gene_casos c ON c.id=u.id_caso AND c.id_empresa=u.id_empresa
WHERE c.id IS NULL;

SELECT 'ubicaciones_persona_cruzada' AS control,COUNT(*) AS errores
FROM gene_mapa_ubicaciones u
JOIN gene_personas p ON p.id=u.id_persona
WHERE u.id_persona IS NOT NULL
  AND (p.id_empresa<>u.id_empresa OR p.id_caso<>u.id_caso);

SELECT 'ubicaciones_evento_cruzado' AS control,COUNT(*) AS errores
FROM gene_mapa_ubicaciones u
JOIN gene_eventos e ON e.id=u.id_evento
WHERE u.id_evento IS NOT NULL
  AND (e.id_empresa<>u.id_empresa OR e.id_caso<>u.id_caso);

SELECT 'ubicaciones_coordenadas_invalidas' AS control,COUNT(*) AS errores
FROM gene_mapa_ubicaciones
WHERE latitud<-90 OR latitud>90 OR longitud<-180 OR longitud>180;

SELECT 'ubicaciones_fingerprint_invalido' AS control,COUNT(*) AS errores
FROM gene_mapa_ubicaciones
WHERE fingerprint NOT REGEXP '^[0-9a-f]{64}$';

SELECT 'ubicaciones_confirmadas_sin_revision' AS control,COUNT(*) AS errores
FROM gene_mapa_ubicaciones
WHERE estado IN ('confirmada','refutada')
  AND (revisado_por IS NULL OR revisado_en IS NULL OR requiere_revision_humana<>0);

SELECT 'rutas_huerfanas_caso' AS control,COUNT(*) AS errores
FROM gene_mapa_rutas r
LEFT JOIN gene_casos c ON c.id=r.id_caso AND c.id_empresa=r.id_empresa
WHERE c.id IS NULL;

SELECT 'rutas_persona_cruzada' AS control,COUNT(*) AS errores
FROM gene_mapa_rutas r
JOIN gene_personas p ON p.id=r.id_persona
WHERE r.id_persona IS NOT NULL
  AND (p.id_empresa<>r.id_empresa OR p.id_caso<>r.id_caso);

SELECT 'rutas_confirmadas_sin_revision' AS control,COUNT(*) AS errores
FROM gene_mapa_rutas
WHERE estado IN ('confirmada','refutada')
  AND (revisado_por IS NULL OR revisado_en IS NULL OR requiere_revision_humana<>0);

SELECT 'puntos_ruta_cruzados' AS control,COUNT(*) AS errores
FROM gene_mapa_ruta_puntos rp
JOIN gene_mapa_rutas r ON r.id=rp.id_ruta
JOIN gene_mapa_ubicaciones u ON u.id=rp.id_ubicacion
WHERE r.id_empresa<>rp.id_empresa OR r.id_caso<>rp.id_caso
   OR u.id_empresa<>rp.id_empresa OR u.id_caso<>rp.id_caso;

SELECT 'rutas_con_menos_de_dos_puntos' AS control,COUNT(*) AS errores
FROM (
    SELECT r.id
    FROM gene_mapa_rutas r
    LEFT JOIN gene_mapa_ruta_puntos rp ON rp.id_ruta=r.id
    WHERE r.estado<>'archivada'
    GROUP BY r.id
    HAVING COUNT(rp.id)<2
) x;

SELECT 'puntos_orden_duplicado' AS control,COUNT(*) AS errores
FROM (
    SELECT id_ruta,orden,COUNT(*) total
    FROM gene_mapa_ruta_puntos
    GROUP BY id_ruta,orden
    HAVING COUNT(*)>1
) x;