-- 02. Población y recuentos de etiqueta para la potencia (sin calcular ningún screen: solo filtros de la población).
CREATE OR REPLACE TABLE p AS
SELECT b.*,
  CASE WHEN anio NOT BETWEEN 2018 AND 2025 THEN 'fuera_ventana'
       WHEN tipo_contrato NOT IN ('Obras','Servicios','Suministros') THEN 'al_alza_tipo'
       WHEN persona_fisica THEN 'persona_fisica'
       WHEN coalesce(num_ofertas,0) < 3 THEN 'menos_de_3'
       WHEN NOT coalesce(mn > 0 AND mx > 0, FALSE) THEN 'sin_min_max'
       WHEN mn > mx OR NOT coalesce(adj > 0, FALSE) OR adj < mn*0.995 OR adj > mx*1.005 THEN 'incoherente'
       WHEN coalesce(pbl_lote > 0 AND mn > pbl_lote*1.001, FALSE) THEN 'sobre_pbl'
       ELSE 'ok' END motivo0
FROM b;
--;
-- @ embudo de la población (2018-2025; ventana ampliada 2014-2017 aparte)
SELECT motivo0, count(*) n FROM p GROUP BY 1 ORDER BY 2 DESC;
--;
-- @ etiqueta: lotes V2 ∪ V3 por expediente y año en la población base (motivo0 = ok), y en la ventana ampliada 2014-2025
WITH pos AS (SELECT DISTINCT exp, id_canonical FROM lab WHERE var IN ('V2','V3'))
SELECT pos.exp, p.anio, count(*) FILTER (WHERE p.motivo0='ok') ok_2018_25,
  count(*) FILTER (WHERE p.motivo0='fuera_ventana' AND p.tipo_contrato IN ('Obras','Servicios','Suministros') AND NOT p.persona_fisica AND p.num_ofertas>=3 AND p.mn>0 AND p.mx>0 AND p.mn<=p.mx AND p.adj BETWEEN p.mn*0.995 AND p.mx*1.005 AND NOT coalesce(p.pbl_lote>0 AND p.mn>p.pbl_lote*1.001,false)) ok_2014_17,
  count(*) todos, count(DISTINCT p.licitacion_id) FILTER (WHERE p.motivo0='ok') lics_ok, count(DISTINCT p.organo_clave) FILTER (WHERE p.motivo0='ok') organos_ok,
  count(*) FILTER (WHERE p.motivo0='ok' AND p.mn = p.mx) ok_sin_disp, count(*) FILTER (WHERE p.motivo0='ok' AND abs(p.mn-p.pbl_lote) <= 0.001*p.pbl_lote) ok_pnc
FROM pos JOIN p USING (id_canonical) GROUP BY ALL ORDER BY 1,2;
--;
-- @ positivas por expediente: motivo de exclusión (todas las fechas)
WITH pos AS (SELECT DISTINCT exp, id_canonical FROM lab WHERE var IN ('V2','V3'))
SELECT pos.exp, p.motivo0, count(*) FROM pos JOIN p USING (id_canonical) GROUP BY ALL ORDER BY 1,3 DESC;