-- 01. Base de trabajo (interno/col.duckdb, tabla b): lotes adjudicados no menores de la PLACSP, 2014-2025, con identidad
-- del órgano (tipo v3.2) y del adjudicatario, clase de procedimiento (la de P8), PBL del lote fiable, y marcas de etiqueta
-- (lotes de cártel V1/V2/V3 de P8 c06 y exclusión de controles). Solo columnas derivadas; el dato no se toca.
CREATE OR REPLACE TABLE b0 AS
SELECT source, id_canonical, licitacion_id, lote_id, expediente, organo_nombre, organo_nif, dir3, adjudicatario_nombre,
  adjudicatario_nif, adjudicatario_nif_raw, procedimiento, procedimiento_native, tramitacion, rol_am, tipo_contrato,
  coalesce(cpv_division,'??') cpv, num_ofertas, oferta_min_eur mn, oferta_max_eur mx, ofertas_anormales an,
  imp_lic_eur pbl, imp_lic_reparto rep, imp_adj_eur adj, anio_ref anio, coalesce(fecha_adj, fecha_formalizacion, fecha_pub) fref,
  nuts, ccaa, provincia
FROM u WHERE source = 'PLACSP' AND unidad = 'adjudicacion' AND NOT coalesce(es_menor, FALSE) AND anio_ref BETWEEN 2014 AND 2025;
--;
CREATE OR REPLACE TEMP TABLE o1 AS SELECT DISTINCT o.organo_nif, o.organo_nombre, o.dir3, o.organo_clave FROM idn_organos_filas o
  WHERE o.organo_nombre IN (SELECT DISTINCT organo_nombre FROM b0) OR o.organo_nombre IS NULL;
--;
CREATE OR REPLACE TEMP TABLE a1 AS SELECT DISTINCT a.adjudicatario_nif_raw, a.adjudicatario_nif, a.adjudicatario_nombre, a.empresa_clave,
  a.persona_fisica, a.nombre_ute FROM idn_adjudicatarios_filas a WHERE a.adjudicatario_nombre IN (SELECT DISTINCT adjudicatario_nombre FROM b0);
--;
CREATE OR REPLACE TEMP TABLE nl AS SELECT licitacion_id, count(*) n_adj_lic, count(DISTINCT coalesce(lote_id,'')) n_lotes_lic FROM b0 GROUP BY 1;
--;
CREATE OR REPLACE TABLE b AS
SELECT b.*, o.organo_clave, coalesce(t.tipo,'sin_tipo') tipo, a.empresa_clave, coalesce(a.persona_fisica, FALSE) persona_fisica,
  coalesce(a.nombre_ute, FALSE) nombre_ute, nl.n_adj_lic, nl.n_lotes_lic,
  CASE
    WHEN b.procedimiento = 'Acuerdo_marco' OR b.rol_am IN ('basado','basado_sda')
      OR regexp_matches(coalesce(b.procedimiento_native,''), '(?i)basado|derivado de acuerdo|espec[ií]fico en el marco|adjudicado en el marco|derivado de asociaci') THEN 'basado'
    WHEN b.tramitacion = 'emergencia' OR regexp_matches(coalesce(b.procedimiento_native,''), '(?i)emergencia') THEN 'emergencia'
    WHEN b.procedimiento = 'Negociado_sin_publicidad' THEN 'nsp'
    WHEN b.procedimiento = 'Abierto' AND regexp_matches(coalesce(b.procedimiento_native,''), '(?i)simplificado') THEN 'abierto_simplificado'
    WHEN b.procedimiento = 'Abierto' THEN 'abierto'
    WHEN b.procedimiento = 'Restringido' THEN 'restringido'
    WHEN b.procedimiento = 'Negociado_publicidad' THEN 'negociado_publicidad'
    WHEN b.procedimiento IN ('Dialogo_competitivo','Asociacion_innovacion') THEN 'dialogo_innovacion'
    WHEN b.procedimiento IS NULL THEN 'sin_procedimiento' ELSE 'otro' END clase,
  -- PBL del lote fiable: reparto por lote o licitación con una sola adjudicación (regla de P8)
  CASE WHEN b.pbl > 0 AND (b.rep = 'por_lote' OR nl.n_adj_lic = 1) THEN b.pbl END pbl_lote
FROM b0 b
LEFT JOIN o1 o ON o.organo_nif IS NOT DISTINCT FROM b.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM b.organo_nombre AND o.dir3 IS NOT DISTINCT FROM b.dir3
LEFT JOIN idn_organos_tipo t ON t.organo_clave = o.organo_clave
LEFT JOIN a1 a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM b.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM b.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM b.adjudicatario_nombre
LEFT JOIN nl USING (licitacion_id);
--;
-- @ la unión no multiplica filas
SELECT (SELECT count(*) FROM b) b, (SELECT count(*) FROM b0) b0, (SELECT count(DISTINCT id_canonical) FROM b) ids, (SELECT count(*) FROM b WHERE organo_clave IS NULL) sin_organo, (SELECT count(*) FROM b WHERE tipo='sin_tipo') sin_tipo;
--;
-- Etiqueta (P8 c06, misma construcción de ids): V1/V2/V3 por expediente
CREATE OR REPLACE TABLE lab AS SELECT DISTINCT exp, var, source, id_canonical FROM read_parquet('/p8/salidas/c06_lotes_cartel.parquet') WHERE source='PLACSP';
--;
-- Exclusión de controles: cualquier lote (cualquier fecha) ganado por una empresa de cualquier expediente (NIF, UTE por socios o por nombre) o de una licitación citada
CREATE OR REPLACE TEMP TABLE cf AS SELECT * FROM read_csv('/c/interno/emp.csv', header=true, all_varchar=true);
--;
CREATE OR REPLACE TABLE m AS
SELECT DISTINCT b.id_canonical, cf.exp FROM b JOIN cf ON b.empresa_clave = cf.nif
UNION SELECT DISTINCT b.id_canonical, cf.exp FROM b JOIN idn_ute_socios us ON us.ute_clave = b.empresa_clave JOIN cf ON cf.nif = us.socio_nif
UNION SELECT DISTINCT b.id_canonical, x.exp FROM b JOIN (SELECT DISTINCT n.adjudicatario_nombre, cf.exp FROM (SELECT DISTINCT adjudicatario_nombre FROM b WHERE nombre_ute) n
   JOIN cf ON regexp_matches(lower(strip_accents(coalesce(n.adjudicatario_nombre,''))), cf.rx)) x ON b.nombre_ute AND b.adjudicatario_nombre = x.adjudicatario_nombre;
--;
CREATE OR REPLACE TABLE excl AS SELECT DISTINCT id_canonical FROM m UNION SELECT DISTINCT id_canonical FROM b JOIN (SELECT DISTINCT licitacion_id FROM b JOIN lab USING (id_canonical) WHERE lab.var='V3') USING (licitacion_id);
--;
-- @ etiqueta en la base
SELECT exp, var, count(*) lotes, count(b.id_canonical) en_base FROM lab LEFT JOIN b USING (id_canonical) GROUP BY ALL ORDER BY 1,2;
--;
-- @ exclusión
SELECT (SELECT count(*) FROM excl) excl, (SELECT count(*) FROM m) m_filas, (SELECT count(DISTINCT id_canonical) FROM lab) lab_ids, (SELECT count(*) FROM lab WHERE id_canonical NOT IN (SELECT id_canonical FROM excl)) lab_fuera_excl;