"""08. D2 · Sobre cerrado de Cantabria (S/0004/25), con la implementación 1 (tabla sc de 05_screens.sql).
Reglas: DEFINICIONES_PREVIAS.md §6 (sellado ee85d2f5…). Escribe interno/CANTABRIA_SOBRE_CERRADO.md (lotes y empresas: interno)
e interno/08_cantabria.json (agregados). Lo publicable lo escribe el ejecutor a mano desde los agregados."""
import duckdb, json, time, hashlib, glob, os
con = duckdb.connect('/c/interno/col.duckdb', read_only=True)
VI, VF = '2019-01-01', '2025-03-31'
con.execute(f"""CREATE TEMP TABLE amb AS
SELECT sc.id_canonical, sc.licitacion_id, sc.anio, sc.fref, sc.cpv, sc.tipo, sc.pct_s3, sc.s3, sc.rango, sc.sep, sc.baja_cond,
  sc.p_rango, sc.p_sep, sc.p_baja, sc.pos_cant = 1 doce, sc.excluido, b.expediente, b.organo_nombre, b.adjudicatario_nombre, b.num_ofertas
FROM sc JOIN b USING (id_canonical)
WHERE sc.en_c AND sc.m_cant AND sc.fref BETWEEN DATE '{VI}' AND DATE '{VF}'""")
con.execute(f"""CREATE TEMP TABLE ctl AS SELECT id_canonical, anio, pct_s3 FROM sc
WHERE en_c AND m_obras AND NOT en_cantabria AND NOT excluido AND fref BETWEEN DATE '{VI}' AND DATE '{VF}'""")
q = lambda s: con.execute(s).fetchall()
ag = dict(
    ambito_lotes=q("SELECT count(*) FROM amb")[0][0], ambito_senalados=q("SELECT count(*) FROM amb WHERE pct_s3 >= 0.8")[0][0],
    ambito_licitaciones=q("SELECT count(DISTINCT licitacion_id) FROM amb")[0][0],
    ambito_licitaciones_senaladas=q("SELECT count(DISTINCT licitacion_id) FROM amb WHERE pct_s3 >= 0.8")[0][0],
    doce_lotes=q("SELECT count(*) FROM amb WHERE doce")[0][0], doce_senalados=q("SELECT count(*) FROM amb WHERE doce AND pct_s3 >= 0.8")[0][0],
    doce_licitaciones=q("SELECT count(DISTINCT licitacion_id) FROM amb WHERE doce")[0][0],
    otros_cantabria_lotes=q("SELECT count(*) FROM amb WHERE NOT excluido")[0][0],
    otros_cantabria_senalados=q("SELECT count(*) FROM amb WHERE NOT excluido AND pct_s3 >= 0.8")[0][0],
    controles_lotes=q("SELECT count(*) FROM ctl")[0][0], controles_senalados=q("SELECT count(*) FROM ctl WHERE pct_s3 >= 0.8")[0][0])
por_anio = q("""SELECT a.anio, count(*) FILTER (WHERE doce), count(*) FILTER (WHERE doce AND pct_s3 >= 0.8),
  count(*) FILTER (WHERE NOT excluido), count(*) FILTER (WHERE NOT excluido AND pct_s3 >= 0.8),
  (SELECT count(*) FROM ctl c WHERE c.anio = a.anio), (SELECT count(*) FROM ctl c WHERE c.anio = a.anio AND c.pct_s3 >= 0.8)
  FROM amb a GROUP BY 1 ORDER BY 1""")
# AUC exploratorio (sin etiqueta: el expediente no está resuelto) de las 12 empresas frente a los controles, por año
pares = num = 0.0
for (anio,) in q("SELECT DISTINCT anio FROM amb WHERE doce"):
    P = [r[0] for r in q(f"SELECT s3 FROM amb WHERE doce AND anio = {anio}")]
    C = sorted(r[0] for r in q(f"SELECT s.s3 FROM ctl JOIN sc s USING (id_canonical) WHERE ctl.anio = {anio}"))
    import bisect
    for x in P:
        lo = bisect.bisect_left(C, x); hi = bisect.bisect_right(C, x)
        num += lo + 0.5 * (hi - lo); pares += len(C)
ag['auc_exploratorio_doce_vs_controles'] = num / pares if pares else None
md5s = {os.path.basename(f): hashlib.md5(open(f, 'rb').read()).hexdigest() for f in sorted(glob.glob('/c/sql/*.sql') + glob.glob('/c/py/*.py'))}
json.dump(dict(agregado=ag, por_anio=por_anio, md5_codigo=md5s), open('/c/interno/08_cantabria.json', 'w'), indent=1, default=str)

lista = q("""SELECT licitacion_id, id_canonical, anio, expediente, organo_nombre, adjudicatario_nombre, doce, num_ofertas,
  round(pct_s3, 4), round(s3, 4), round(rango, 4), round(sep, 4), round(baja_cond, 4) FROM amb ORDER BY pct_s3 >= 0.8 DESC, licitacion_id, id_canonical""")
pct = lambda a, b: f"{100*a/b:.1f} %" if b else "—"
L = ["# CANTABRIA · SOBRE CERRADO (S/0004/25) — INTERNO: contiene lotes y empresas. No se publica hasta la resolución.\n",
     f"Generado {time.strftime('%Y-%m-%d %H:%M:%S')} UTC por `py/08_cantabria.py` sobre `unified_20261018`, con las reglas de "
     "`DEFINICIONES_PREVIAS.md` §6 (md5 ee85d2f57ed9084207ba8aa549af3e8f, sellado 21-oct-2026 19:08:55 CEST).\n",
     "## Predicción\n",
     "- **Ámbito**: lotes de PC (≥ 3 ofertas, mín y máx coherentes, con dispersión y precio competido, mercado ≥ 20 lotes) de obras "
     "(tipo Obras o CPV 45) en Cantabria (NUTS ES13 o provincia), fecha de referencia 1-1-2019 a 31-3-2025.",
     "- **Señalado** = percentil de S3 dentro de su mercado (CPV × tipo de órgano × año, nacional) ≥ 0,80. S3 = media de los "
     "percentiles orientados de RANGO (↓), SEP (↓) y BAJA_COND (↓).",
     "- **Criterio de acierto** (copiado de §6): R = lotes de PC del ámbito que la resolución identifique como afectados (los de las "
     "licitaciones citadas; si no hay lista, los ganados por las sancionadas en el periodo y mercado acreditados). **Acierto**: ≥ 60 % de R "
     "señalado y tasa de señalados en controles ≤ 25 %. **Acierto débil**: AUC de S3 (R frente a controles, por año, bootstrap de §4) "
     "≥ 0,65 con LB > 0,50. **Fallo**: en otro caso. Archivo sin infracción: se informa la tasa de señalados entre los lotes de las 12 "
     "empresas como falsos positivos. Se publica sea cual sea.\n",
     "## Cifras agregadas\n",
     f"- Ámbito: {ag['ambito_lotes']} lotes de {ag['ambito_licitaciones']} licitaciones; señalados {ag['ambito_senalados']} "
     f"({pct(ag['ambito_senalados'], ag['ambito_lotes'])}) en {ag['ambito_licitaciones_senaladas']} licitaciones.",
     f"- Lotes de las 12 empresas (V2 de c06): {ag['doce_lotes']} ({ag['doce_licitaciones']} licitaciones); señalados {ag['doce_senalados']} "
     f"({pct(ag['doce_senalados'], ag['doce_lotes'])}).",
     f"- Otros lotes del ámbito (sin empresas de ningún expediente): {ag['otros_cantabria_lotes']}; señalados {ag['otros_cantabria_senalados']} "
     f"({pct(ag['otros_cantabria_senalados'], ag['otros_cantabria_lotes'])}).",
     f"- Controles (obras fuera de Cantabria, misma ventana): {ag['controles_lotes']}; señalados {ag['controles_senalados']} "
     f"({pct(ag['controles_senalados'], ag['controles_lotes'])}).",
     f"- AUC exploratorio (no es etiqueta) de S3, 12 empresas frente a controles, por año: {ag['auc_exploratorio_doce_vs_controles']:.3f}.\n",
     "| Año | Lotes 12 empresas | señalados | otros Cantabria | señalados | controles | señalados |", "|---|---|---|---|---|---|---|"]
for r in por_anio:
    L.append("| " + " | ".join(str(x) for x in r) + " |")
L += ["\n## Código (md5)\n"] + [f"- `{k}` {v}" for k, v in md5s.items()]
L += ["\n## Lista completa del ámbito (señalados primero)\n",
      "| licitación | id lote | año | expediente | órgano | adjudicatario | 12 empresas | nº ofertas | pct S3 | S3 | RANGO | SEP | BAJA_COND | señalado |",
      "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|"]
for r in lista:
    s = [str('' if x is None else x).replace('|', '¦') for x in r]
    L.append("| " + " | ".join(s) + f" | {'SÍ' if r[8] is not None and r[8] >= 0.8 else 'no'} |")
open('/c/interno/CANTABRIA_SOBRE_CERRADO.md', 'w').write("\n".join(L) + "\n")
print(json.dumps(ag), por_anio)