"""Local-file only. No API call, no inference. Lists the cells of 'Données brutes' whose letter came from the
recovery pass (a second protocol), by reading the already-written result logs. Output: recovered_cells.csv"""
import json, glob, re, csv, os, hashlib
import pandas as pd
RES = "<private>/eval/results"
F = "satcove-consensus-benchmark-2026-10-22.xlsx"
Q = {json.loads(l)["id"]: json.loads(l) for l in open("<private>/eval/data/questions-3000.jsonl")}
def pl(t, n):
    if not t or not n: return None
    L = "ABCDEFGHIJ"[:n]; c = re.sub(r'^[*_`"\'(\[\s]+', "", t).upper(); m = re.match(r'^([' + L + r'])[ \t]*(?:$|\n|[.:)\]*,])', c)
    return m.group(1) if m else None
prim = {}
for f in sorted(glob.glob(f"{RES}/divergence-questions-3000-main*.jsonl") + glob.glob(f"{RES}/divergence-questions-3000-gemini*.jsonl")):
    for l in open(f):
        r = json.loads(l)
        for m, t in (r.get("raw") or {}).items():
            L = pl(t, r["n_choices"])
            if L: prim[(r["id"], m)] = L
rec = {}; proto = {}
for l in open(f"{RES}/divergence-questions-3000-recovery.jsonl"):
    r = json.loads(l)
    for m, t in (r.get("raw") or {}).items():
        L = pl(t, r["n_choices"])
        if L: rec[(r["id"], m)] = L; proto[(r["id"], m)] = r["protocol"][m]
d = pd.read_excel(F, sheet_name="Données brutes")
names = {"claude": "Claude", "gpt": "GPT", "gemini": "Gemini", "mistral": "Mistral", "perplexity": "Perplexity", "grok": "Grok"}
COL = {"Claude": "E", "GPT": "F", "Gemini": "G", "Mistral": "H", "Perplexity": "I", "Grok": "J"}
rows = []; problems = []
for _, r in d.iterrows():
    for k, nm in names.items():
        v = r[nm]
        if not isinstance(v, str): continue
        p = prim.get((r.Question, k)); c = rec.get((r.Question, k))
        if p and c: problems.append(("both", r.Question, k))
        if p is None and c is None: problems.append(("no_source", r.Question, k))
        if p is not None and p != v: problems.append(("primary_mismatch", r.Question, k))
        if p is None and c is not None:
            if c != v: problems.append(("recovery_mismatch", r.Question, k))
            rows.append((r.Question, nm, COL[nm] + str(_ + 2), v, c, proto[(r.Question, k)]))
print("problems:", problems[:10], len(problems))
pd.DataFrame(rows, columns=["question_id", "model", "cell_reference", "final_letter", "recovered_letter", "recovery_method"]).to_csv("recovered_cells.csv", index=False)
print("recovered cells:", len(rows))
print("xlsx sha256", hashlib.sha256(open(F, "rb").read()).hexdigest())