-- GeneaCase Sprint 4: QA de aislamiento y coherencia probatoria.
SELECT 'AFIRMACIONES_CRUZADAS_EMPRESA_CASO' AS prueba, COUNT(*) AS total_errores
FROM gene_afirmaciones a
JOIN gene_casos c ON c.id=a.id_caso
WHERE a.id_empresa<>c.id_empresa;

SELECT 'EVIDENCIAS_CITA_CRUZADA' AS prueba, COUNT(*) AS total_errores
FROM gene_evidencias e
JOIN gene_afirmaciones a ON a.id=e.id_afirmacion
JOIN gene_citas c ON c.id=e.id_cita
WHERE e.id_empresa<>a.id_empresa OR e.id_empresa<>c.id_empresa
   OR e.id_caso<>a.id_caso OR e.id_caso<>c.id_caso;

SELECT 'CONTRADICCIONES_ITEMS_CRUZADOS' AS prueba, COUNT(*) AS total_errores
FROM gene_contradiccion_items ci
JOIN gene_contradicciones co ON co.id=ci.id_contradiccion
JOIN gene_afirmaciones a ON a.id=ci.id_afirmacion
WHERE co.id_empresa<>a.id_empresa OR co.id_caso<>a.id_caso;

SELECT 'HIPOTESIS_PRUEBAS_CRUZADAS' AS prueba, COUNT(*) AS total_errores
FROM gene_hipotesis_pruebas hp
JOIN gene_hipotesis h ON h.id=hp.id_hipotesis
WHERE hp.id_empresa<>h.id_empresa;

SELECT 'ACEPTADAS_SIN_EVIDENCIA' AS prueba, COUNT(*) AS total_observaciones
FROM gene_afirmaciones a
LEFT JOIN gene_evidencias e ON e.id_afirmacion=a.id AND e.id_empresa=a.id_empresa AND e.id_caso=a.id_caso
WHERE a.estado='aceptada'
GROUP BY a.id
HAVING COUNT(e.id)=0;