CREATE OR REPLACE VIEW vw_gene_busqueda_cobertura AS
SELECT
  p.id_empresa,
  p.id_caso,
  p.id AS id_plan,
  COUNT(pf.id) AS fuentes_planificadas,
  SUM(CASE WHEN pf.estado = 'cubierta' THEN 1 ELSE 0 END) AS fuentes_cubiertas,
  ROUND(100 * SUM(CASE WHEN pf.estado = 'cubierta' THEN 1 ELSE 0 END) / NULLIF(COUNT(pf.id),0), 2) AS cobertura_porcentaje
FROM gene_busqueda_planes p
LEFT JOIN gene_busqueda_plan_fuentes pf ON pf.id_plan = p.id AND pf.id_empresa = p.id_empresa
GROUP BY p.id_empresa, p.id_caso, p.id;

CREATE OR REPLACE VIEW vw_gene_caso_resumen AS
SELECT
  c.id_empresa,
  c.id AS id_caso,
  c.codigo,
  c.titulo,
  c.estado,
  COUNT(DISTINCT p.id) AS personas,
  COUNT(DISTINCT d.id) AS documentos,
  COUNT(DISTINCT a.id) AS afirmaciones,
  COUNT(DISTINCT co.id) AS contradicciones_abiertas,
  COUNT(DISTINCT be.id) AS busquedas
FROM gene_casos c
LEFT JOIN gene_personas p ON p.id_caso = c.id AND p.id_empresa = c.id_empresa AND p.eliminado_en IS NULL
LEFT JOIN gene_documentos d ON d.id_caso = c.id AND d.id_empresa = c.id_empresa
LEFT JOIN gene_afirmaciones a ON a.id_caso = c.id AND a.id_empresa = c.id_empresa
LEFT JOIN gene_contradicciones co ON co.id_caso = c.id AND co.id_empresa = c.id_empresa AND co.estado IN ('abierta','en_revision','reabierta')
LEFT JOIN gene_busqueda_ejecuciones be ON be.id_caso = c.id AND be.id_empresa = c.id_empresa
GROUP BY c.id_empresa, c.id, c.codigo, c.titulo, c.estado;