-- 00. Cobertura de num_ofertas, oferta_min/max, ofertas_anormales y PBL. unified_20261018, adjudicaciones no menores 2018-2025.
-- Sin etiqueta de cártel: nada en este fichero toca empresas ni expedientes.
CREATE OR REPLACE TEMP TABLE a AS
SELECT source, anio_ref anio, tipo_contrato, num_ofertas, oferta_min_eur mn, oferta_max_eur mx, ofertas_anormales an,
  imp_lic_eur pbl, imp_adj_eur adj, imp_lic_reparto rep,
  CASE
    WHEN procedimiento = 'Acuerdo_marco' OR rol_am IN ('basado','basado_sda')
      OR regexp_matches(coalesce(procedimiento_native,''), '(?i)basado|derivado de acuerdo|espec[ií]fico en el marco|adjudicado en el marco|derivado de asociaci') THEN 'basado'
    WHEN tramitacion = 'emergencia' OR regexp_matches(coalesce(procedimiento_native,''), '(?i)emergencia') THEN 'emergencia'
    WHEN procedimiento = 'Negociado_sin_publicidad' THEN 'nsp'
    WHEN procedimiento = 'Abierto' AND regexp_matches(coalesce(procedimiento_native,''), '(?i)simplificado') THEN 'abierto_simplificado'
    WHEN procedimiento = 'Abierto' THEN 'abierto'
    WHEN procedimiento = 'Restringido' THEN 'restringido'
    WHEN procedimiento = 'Negociado_publicidad' THEN 'negociado_publicidad'
    WHEN procedimiento IN ('Dialogo_competitivo','Asociacion_innovacion') THEN 'dialogo_innovacion'
    WHEN procedimiento IS NULL THEN 'sin_procedimiento' ELSE 'otro' END clase
FROM u WHERE unidad = 'adjudicacion' AND NOT coalesce(es_menor, FALSE) AND anio_ref BETWEEN 2018 AND 2025;
--;
-- @ 1. Por fuente (todas las fuentes con algún num_ofertas): % de lotes con cada dato
SELECT source, count(*) lotes, round(100.0*count(num_ofertas)/count(*),1) pct_n, round(100.0*count(mn)/count(*),1) pct_min,
  round(100.0*count(mx)/count(*),1) pct_max, round(100.0*count(*) FILTER (WHERE mn IS NOT NULL AND mx IS NOT NULL)/count(*),1) pct_min_y_max,
  round(100.0*count(an)/count(*),1) pct_anormales, round(100.0*count(*) FILTER (WHERE pbl>0)/count(*),1) pct_pbl
FROM a GROUP BY 1 HAVING count(num_ofertas) > 0 ORDER BY 2 DESC;
--;
-- @ 2. PLACSP por año
SELECT anio, count(*) lotes, round(100.0*count(num_ofertas)/count(*),1) pct_n, round(100.0*count(mn)/count(*),1) pct_min,
  round(100.0*count(mx)/count(*),1) pct_max, round(100.0*count(*) FILTER (WHERE mn IS NOT NULL AND mx IS NOT NULL)/count(*),1) pct_min_y_max,
  round(100.0*count(an)/count(*),1) pct_anormales, round(100.0*count(*) FILTER (WHERE an)/nullif(count(an),0),1) pct_anormales_si,
  round(100.0*count(*) FILTER (WHERE pbl>0)/count(*),1) pct_pbl,
  count(*) FILTER (WHERE num_ofertas>=3 AND mn>0 AND mx>0) n3_minmax
FROM a WHERE source='PLACSP' GROUP BY 1 ORDER BY 1;
--;
-- @ 3. PLACSP por clase de procedimiento (2018-2025)
SELECT clase, count(*) lotes, round(100.0*count(num_ofertas)/count(*),1) pct_n, round(100.0*count(*) FILTER (WHERE mn IS NOT NULL AND mx IS NOT NULL)/count(*),1) pct_min_y_max,
  round(100.0*count(an)/count(*),1) pct_anormales, round(100.0*count(*) FILTER (WHERE pbl>0)/count(*),1) pct_pbl,
  count(*) FILTER (WHERE num_ofertas>=3 AND mn>0 AND mx>0) n3_minmax
FROM a WHERE source='PLACSP' GROUP BY 1 ORDER BY 2 DESC;
--;
-- @ 4. PLACSP por tipo de contrato (2018-2025)
SELECT tipo_contrato, count(*) lotes, round(100.0*count(num_ofertas)/count(*),1) pct_n, round(100.0*count(*) FILTER (WHERE mn IS NOT NULL AND mx IS NOT NULL)/count(*),1) pct_min_y_max,
  round(100.0*count(an)/count(*),1) pct_anormales, round(100.0*count(*) FILTER (WHERE pbl>0)/count(*),1) pct_pbl,
  count(*) FILTER (WHERE num_ofertas>=3 AND mn>0 AND mx>0) n3_minmax
FROM a WHERE source='PLACSP' GROUP BY 1 ORDER BY 2 DESC;
--;
-- @ 5. PLACSP año × tipo (obras, servicios, suministros): lotes con >= 3 ofertas y min y max > 0
SELECT anio, count(*) FILTER (WHERE tipo_contrato='Obras' AND num_ofertas>=3 AND mn>0 AND mx>0) obras,
  count(*) FILTER (WHERE tipo_contrato='Servicios' AND num_ofertas>=3 AND mn>0 AND mx>0) servicios,
  count(*) FILTER (WHERE tipo_contrato='Suministros' AND num_ofertas>=3 AND mn>0 AND mx>0) suministros,
  round(100.0*count(*) FILTER (WHERE tipo_contrato IN ('Obras','Servicios','Suministros') AND num_ofertas>=3 AND mn>0 AND mx>0)
    / nullif(count(*) FILTER (WHERE tipo_contrato IN ('Obras','Servicios','Suministros') AND num_ofertas>=3),0),1) pct_de_los_3mas
FROM a WHERE source='PLACSP' GROUP BY 1 ORDER BY 1;
--;
-- @ 6. Coherencia (PLACSP, min y max > 0): min <= max, min <= adj <= max (tolerancia 0,5 %), min <= PBL, max <= PBL
SELECT count(*) lotes,
  round(100.0*avg(CASE WHEN mn <= mx THEN 1 ELSE 0 END),2) pct_min_le_max,
  round(100.0*avg(CASE WHEN adj>0 AND mn <= adj*1.005 AND adj <= mx*1.005 THEN 1 ELSE 0 END) FILTER (WHERE adj>0),2) pct_min_adj_max,
  round(100.0*avg(CASE WHEN abs(adj-mn) <= 0.005*mn THEN 1 ELSE 0 END) FILTER (WHERE adj>0),2) pct_adj_igual_min,
  round(100.0*avg(CASE WHEN abs(adj-mn*1.21) <= 0.005*mn*1.21 THEN 1 ELSE 0 END) FILTER (WHERE adj>0),2) pct_adj_igual_min_x121,
  round(100.0*avg(CASE WHEN adj > mx*1.005 THEN 1 ELSE 0 END) FILTER (WHERE adj>0),2) pct_adj_sobre_max,
  round(100.0*avg(CASE WHEN adj < mn*0.995 THEN 1 ELSE 0 END) FILTER (WHERE adj>0),2) pct_adj_bajo_min,
  round(100.0*avg(CASE WHEN mn <= pbl*1.001 THEN 1 ELSE 0 END) FILTER (WHERE pbl>0),2) pct_min_le_pbl,
  round(100.0*avg(CASE WHEN mx <= pbl*1.001 THEN 1 ELSE 0 END) FILTER (WHERE pbl>0),2) pct_max_le_pbl,
  round(100.0*avg(CASE WHEN mn > pbl*1.21*1.001 THEN 1 ELSE 0 END) FILTER (WHERE pbl>0),2) pct_min_sobre_pbl_x121,
  round(100.0*avg(CASE WHEN mn = mx THEN 1 ELSE 0 END),2) pct_min_igual_max
FROM a WHERE source='PLACSP' AND mn>0 AND mx>0;
--;
-- @ 7. Coherencia por tipo (O/S/S frente al resto), con >= 3 ofertas
SELECT tipo_contrato IN ('Obras','Servicios','Suministros') oss, count(*) lotes,
  round(100.0*avg(CASE WHEN mn <= mx THEN 1 ELSE 0 END),2) pct_min_le_max,
  round(100.0*avg(CASE WHEN adj>0 AND mn <= adj*1.005 AND adj <= mx*1.005 THEN 1 ELSE 0 END) FILTER (WHERE adj>0),2) pct_min_adj_max,
  round(100.0*avg(CASE WHEN mn <= pbl*1.001 THEN 1 ELSE 0 END) FILTER (WHERE pbl>0),2) pct_min_le_pbl,
  round(100.0*avg(CASE WHEN abs(mn-pbl) <= 0.001*pbl THEN 1 ELSE 0 END) FILTER (WHERE pbl>0),2) pct_min_en_pbl_01,
  round(100.0*avg(CASE WHEN mn = mx THEN 1 ELSE 0 END),2) pct_min_igual_max,
  approx_quantile((mx-mn)/mn, 0.5) rango_p50, approx_quantile((mx-mn)/mn, 0.9) rango_p90, approx_quantile((mx-mn)/mn, 0.99) rango_p99
FROM a WHERE source='PLACSP' AND mn>0 AND mx>0 AND num_ofertas>=3 GROUP BY 1;
--;
-- @ 8. Desiertos con min/max (PLACSP) y num_ofertas < 3 con min y max distintos (rareza)
SELECT count(*) FILTER (WHERE num_ofertas=1 AND mn<>mx) n1_min_distinto_max, count(*) FILTER (WHERE num_ofertas=2) n2,
  count(*) FILTER (WHERE num_ofertas IS NULL AND mn IS NOT NULL) sin_n_con_min, count(*) FILTER (WHERE mn<=0 OR mx<=0) min_o_max_no_positivo
FROM a WHERE source='PLACSP';
--;
-- @ 9. imp_lic_reparto en PLACSP con >= 3 ofertas y min/max (de dónde sale el PBL del lote)
SELECT rep, count(*) n, count(*) FILTER (WHERE pbl>0) con_pbl FROM a WHERE source='PLACSP' AND num_ofertas>=3 AND mn>0 AND mx>0 GROUP BY 1 ORDER BY 2 DESC;