"""A1 · muestra de lectura (sin modelos): 160 licitaciones de carburantes + 40 de electricidad, PLACSP, 2023-2025, licitadas,
NOT es_copia, sin exclusión v2, SIN cantidad en el objeto (reglas v2), con PPT, con importe comparable sin IVA > 0, sin acuerdo
marco (topes), fuera Canarias/Ceuta/Melilla. Orden por md5('precios-a1-20261021'||licitacion_id); estratos año × tipo de
órgano × basado (sí/no), reparto proporcional por mayores restos. Documentos: PPT vigentes de la última versión (≤ 2) y PCAP (1).
Salidas: /p/priv/muestra_200.csv, /p/priv/muestra_docs.csv, /p/salidas/10_muestra_resumen.csv (sin ids)."""
import duckdb, csv, collections, hashlib
SEM = 'precios-a1-20261021'; CUOTA = {'carburantes': 160, 'electricidad': 40}
con = duckdb.connect()
con.execute("CREATE VIEW a AS SELECT * FROM read_parquet('/p/priv/a1_base.parquet')")
pob = con.execute("""SELECT mercado, licitacion_id, min(anio_ref) anio, any_value(tipo_organo) tipo,
   bool_or(rol_am IN ('basado','basado_sda')) basado, count(*) contratos
 FROM a WHERE NOT es_copia AND source='PLACSP' AND NOT menor AND anio_ref BETWEEN 2023 AND 2025 AND NOT excluido_v2
   AND cant_v2 IS NULL AND ppt AND imp > 0 AND rol_am IS DISTINCT FROM 'acuerdo' AND coalesce(prov_ine,'') NOT IN ('35','38','51','52')
   AND licitacion_id IS NOT NULL
 GROUP BY 1, 2""").fetchall()
h = lambda x: hashlib.md5((SEM + x).encode()).hexdigest()
sel = []
res = []
for mk, cuota in CUOTA.items():
    P = sorted([r for r in pob if r[0] == mk], key=lambda r: h(r[1]))
    est = collections.defaultdict(list)
    for r in P: est[(r[2], r[3], r[4])].append(r)
    N = len(P); ideal = {k: cuota * len(v) / N for k, v in est.items()}
    asig = {k: int(v) for k, v in ideal.items()}
    for k in sorted(ideal, key=lambda k: (-(ideal[k] - asig[k]), str(k)))[:cuota - sum(asig.values())]: asig[k] += 1
    for k, v in est.items():
        sel += [(mk, r[1], r[2], r[3], r[4], r[5], h(r[1])) for r in v[:asig[k]]]
        res.append((mk, k[0], k[1], k[2], len(v), asig[k]))
sel.sort(key=lambda r: (r[0] != 'carburantes', r[6]))
con.execute("CREATE TEMP TABLE s (mercado VARCHAR, licitacion_id VARCHAR, anio INT, tipo VARCHAR, basado BOOLEAN, contratos INT, hash VARCHAR)")
con.executemany("INSERT INTO s VALUES (?,?,?,?,?,?,?)", sel)
con.execute("""CREATE TEMP TABLE docs AS SELECT s.mercado, s.hash, d.licitacion_id, d.tipo_documento, d.url, d.nombre, d.orden,
   row_number() OVER (PARTITION BY d.licitacion_id, d.tipo_documento ORDER BY d.orden, d.url) k
 FROM s JOIN read_parquet('/d/unified_20261018.documentos/documentos.parquet') d USING (licitacion_id)
 WHERE d.tipo_documento IN ('pliego_tecnico','pliego_administrativo') AND coalesce(d.en_ultima_version, TRUE) AND coalesce(d.vigente, TRUE)""")
con.execute("COPY (SELECT * EXCLUDE (k) FROM docs WHERE (tipo_documento='pliego_tecnico' AND k<=2) OR (tipo_documento='pliego_administrativo' AND k=1) ORDER BY mercado!='carburantes', hash, tipo_documento DESC, orden) TO '/p/priv/muestra_docs.csv' (HEADER)")
with open('/p/priv/muestra_200.csv', 'w', newline='') as f:
    w = csv.writer(f); w.writerow(['n', 'mercado', 'licitacion_id', 'anio', 'tipo_organo', 'basado', 'contratos', 'hash'])
    for i, r in enumerate(sel, 1): w.writerow([i] + list(r))
with open('/p/salidas/10_muestra_resumen.csv', 'w', newline='') as f:
    w = csv.writer(f); w.writerow(['mercado', 'anio', 'tipo_organo', 'basado', 'poblacion_licitaciones', 'muestra']); w.writerows(sorted(res, key=str))
print('población', collections.Counter(r[0] for r in pob), 'muestra', collections.Counter(r[0] for r in sel), 'contratos', sum(r[5] for r in sel))
print(con.execute("SELECT mercado, tipo_documento, count(*), count(DISTINCT licitacion_id), count(DISTINCT regexp_extract(url,'https?://([^/]+)',1)) FROM docs WHERE (tipo_documento='pliego_tecnico' AND k<=2) OR k=1 GROUP BY ALL").fetchall())
print(con.execute("SELECT regexp_extract(url,'https?://([^/]+)',1), count(*) FROM docs GROUP BY 1").fetchall())