SELECT 'criterios_huerfanos' AS control, COUNT(*) AS total
FROM gene_hipotesis_criterios c
LEFT JOIN gene_hipotesis h ON h.id=c.id_hipotesis AND h.id_empresa=c.id_empresa AND h.id_caso=c.id_caso
WHERE h.id IS NULL;

SELECT 'revisiones_huerfanas' AS control, COUNT(*) AS total
FROM gene_hipotesis_revisiones r
LEFT JOIN gene_hipotesis h ON h.id=r.id_hipotesis AND h.id_empresa=r.id_empresa AND h.id_caso=r.id_caso
WHERE h.id IS NULL;

SELECT 'criterios_peso_invalido' AS control, COUNT(*) AS total
FROM gene_hipotesis_criterios
WHERE peso<=0 OR peso>100;

SELECT 'criterios_manual_con_referencia' AS control, COUNT(*) AS total
FROM gene_hipotesis_criterios
WHERE tipo_referencia='manual' AND referencia_id IS NOT NULL;

SELECT 'criterios_referencia_sin_id' AS control, COUNT(*) AS total
FROM gene_hipotesis_criterios
WHERE tipo_referencia<>'manual' AND referencia_id IS NULL;

SELECT 'hipotesis_puntuacion_invalida' AS control, COUNT(*) AS total
FROM gene_hipotesis
WHERE puntuacion_calculada IS NOT NULL AND (puntuacion_calculada<0 OR puntuacion_calculada>100);

SELECT 'hipotesis_conclusion_sin_revision' AS control, COUNT(*) AS total
FROM gene_hipotesis h
LEFT JOIN gene_hipotesis_revisiones r ON r.id_hipotesis=h.id AND r.id_empresa=h.id_empresa AND r.id_caso=h.id_caso AND r.evento='revision_humana'
WHERE h.estado='convertida_conclusion' AND h.ultima_revision_por IS NOT NULL AND r.id IS NULL;
