-- 03. Recuentos de positivas y controles por cártel y año para la potencia (sin screens). Población «ok» y, aparte,
-- la del compuesto (sin sin_dispersion ni precio_no_competido). Mercado afectado = regla MERCADO de P8 c06.
CREATE OR REPLACE TABLE mk AS
SELECT p.id_canonical,
  p.cpv IN ('15','03') m_alim,
  p.tipo = 'age' AND regexp_matches(lower(strip_accents(coalesce(p.organo_nombre,''))), 'carreteras') m_carr,
  p.tipo = 'age' AND regexp_matches(lower(strip_accents(coalesce(p.organo_nombre,''))), 'defensa|ejercito|jaemale|mando de apoyo logistico|parque y centro|armada|intendencia|jefatura de asuntos economicos|direccion general de armamento|mando de ingenieros|acuartelamiento|base aerea|regimiento|brigada') m_mil,
  (coalesce(p.nuts,'') LIKE 'ES13%' OR regexp_matches(lower(strip_accents(coalesce(p.provincia,''))), 'cantabria')) AND (p.tipo_contrato = 'Obras' OR p.cpv = '45') m_cant,
  (p.tipo_contrato = 'Obras' OR p.cpv = '45') m_obras,
  (coalesce(p.nuts,'') LIKE 'ES13%' OR regexp_matches(lower(strip_accents(coalesce(p.provincia,''))), 'cantabria')) en_cantabria
FROM p;
--;
CREATE OR REPLACE TABLE et AS
WITH pos AS (SELECT exp, id_canonical FROM lab WHERE var IN ('V2','V3') GROUP BY ALL)
SELECT p.id_canonical, p.anio, p.licitacion_id, p.organo_clave, p.motivo0,
  (p.mn = p.mx) sin_disp, coalesce(abs(p.mn - p.pbl_lote) <= 0.001*p.pbl_lote, FALSE) pnc,
  mk.*, x.id_canonical IS NOT NULL excluido,
  max(CASE WHEN pos.exp='S/0016/21' THEN 1 ELSE 0 END) pos_alim, max(CASE WHEN pos.exp='S/0013/19' THEN 1 ELSE 0 END) pos_carr,
  max(CASE WHEN pos.exp='S/0008/21' THEN 1 ELSE 0 END) pos_mil, max(CASE WHEN pos.exp='S/0004/25' THEN 1 ELSE 0 END) pos_cant
FROM p JOIN mk USING (id_canonical) LEFT JOIN excl x USING (id_canonical) LEFT JOIN pos USING (id_canonical)
GROUP BY ALL;
--;
-- @ positivas y controles por cártel y año (población ok; entre paréntesis lo que queda para el compuesto)
WITH g AS (
  SELECT 'alimentos' c, anio, pos_alim=1 pos, (NOT excluido AND m_alim) ctl, * FROM et WHERE motivo0='ok'
  UNION ALL SELECT 'militar', anio, pos_mil=1, (NOT excluido AND m_mil), * FROM et WHERE motivo0='ok'
  UNION ALL SELECT 'carreteras', anio, pos_carr=1, (NOT excluido AND m_carr), * FROM et WHERE motivo0='ok'
  UNION ALL SELECT 'cantabria', anio, pos_cant=1, (NOT excluido AND m_obras AND NOT en_cantabria), * FROM et WHERE motivo0='ok')
SELECT c, anio, count(*) FILTER (WHERE pos) pos_ok, count(*) FILTER (WHERE pos AND NOT sin_disp AND NOT pnc) pos_comp,
  count(DISTINCT licitacion_id) FILTER (WHERE pos AND NOT sin_disp AND NOT pnc) pos_lics, count(DISTINCT organo_clave) FILTER (WHERE pos AND NOT sin_disp AND NOT pnc) pos_org,
  count(*) FILTER (WHERE ctl) ctl_ok, count(*) FILTER (WHERE ctl AND NOT sin_disp AND NOT pnc) ctl_comp, count(DISTINCT organo_clave) FILTER (WHERE ctl AND NOT sin_disp AND NOT pnc) ctl_org
FROM g GROUP BY ALL HAVING count(*) FILTER (WHERE pos) > 0 ORDER BY 1, 2;