"""v1 exploratory analysis. Inputs: the xlsx (read-only, sheet 'Données brutes') + recovered_cells.csv (derived from local logs, no API).
Writes: metrics_no_recovery.json, recovery_impact.csv, jury_sensitivity.csv, source_by_topic.csv, equal_footprint.csv"""
import pandas as pd, numpy as np, math, json, hashlib, collections
F = "satcove-consensus-benchmark-2026-10-22.xlsx"
AI = ["Claude", "GPT", "Gemini", "Mistral", "Perplexity", "Grok"]
raw = pd.read_excel(F, sheet_name="Données brutes")
rc = pd.read_csv("recovered_cells.csv"); REC = set(zip(rc.question_id, rc.model))
ans = raw[["Question", "Source", "Matière", "Bonne réponse"] + AI].copy()
final = ans.copy()
prim = ans.copy()
for q, m in REC:
    prim.loc[prim.Question == q, m] = np.nan
def wil(k, n, z=1.96):
    if n == 0: return (None, None)
    p = k / n; c = (p + z * z / 2 / n) / (1 + z * z / n); h = z * math.sqrt(p * (1 - p) / n + z * z / 4 / n / n) / (1 + z * z / n)
    return round(100 * (c - h), 1), round(100 * (c + h), 1)
def pct(k, n): return round(100 * k / n, 1) if n else None
def vote(df, models=AI, min_jury=4, require_all=False):
    """per question: jury size, majority (None on tie), unanimous. Only models in `models` vote."""
    out = []
    for _, r in df.iterrows():
        v = [r[a] for a in models if isinstance(r[a], str)]
        n = len(v)
        if n < min_jury or (require_all and n < len(models)):
            continue
        c = collections.Counter(v).most_common(); tie = len(c) > 1 and c[1][1] == c[0][1]
        out.append(dict(Question=r.Question, Source=r.Source, Matière=r["Matière"], key=r["Bonne réponse"], n=n, maj=None if tie else c[0][0], tie=tie, un=c[0][1] == n,
                        share=c[0][1] / n))
    V = pd.DataFrame(out)
    if len(V): V["maj_ok"] = np.where(V.tie, np.nan, (V.maj == V.key).astype(float))
    return V
def summ(V):
    if not len(V): return dict(questions=0)
    m = V[~V.tie]; k = int(m.maj_ok.sum()); u = V[V.un]; uw = int((u.maj != u.key).sum())
    lo, hi = wil(k, len(m)); ulo, uhi = wil(uw, len(u))
    return dict(questions=len(V), ties=int(V.tie.sum()), majorities=len(m), majority_correct=k, majority_accuracy_pct=pct(k, len(m)), majority_ci95=[lo, hi],
                majority_accuracy_ties_as_failures_pct=pct(k, len(V)), unanimous=len(u), unanimous_wrong=uw, unanimous_wrong_pct=pct(uw, len(u)), unanimous_wrong_ci95=[ulo, uhi])
def model_scores(df, models=AI, restrict=None):
    d = df if restrict is None else df[df.Question.isin(restrict)]
    res = {}
    for a in models:
        s = d[a].dropna(); k = int((s == d.loc[s.index, "Bonne réponse"]).sum()); lo, hi = wil(k, len(s))
        res[a] = dict(answered=len(s), correct=k, accuracy_pct=pct(k, len(s)), ci95=[lo, hi])
    return res
# ---------- sanity: final view reproduces the xlsx aggregates
Vf = vote(final); Vp = vote(prim)
chk = summ(Vf); assert chk["questions"] == 2500 and chk["majorities"] == 2303 and chk["majority_correct"] == 1775 and chk["unanimous"] == 1221 and chk["unanimous_wrong"] == 73, chk
sha = hashlib.sha256(open(F, "rb").read()).hexdigest()
# ---------- metrics_no_recovery.json
P533 = set(ans.Question[ans.Perplexity.notna()])
Vp_bysize = {str(n): summ(Vp[Vp.n == n]) for n in (4, 5, 6)}
fc = Vp[Vp.un & (Vp.maj != Vp.key)]
primary_perp = {a: v for a, v in model_scores(prim, restrict=P533).items()}
all6 = prim[prim.Question.isin(P533)].dropna(subset=AI)
M = {"source_file": F, "source_sha256": sha, "view": "primary protocol only: letters of 'Données brutes' minus the 654 recovered cells (recovered_cells.csv)",
     "note": "Jury size = number of models with a valid primary-protocol letter for that question; questions with fewer than 4 are excluded.",
     "recovered_cells_removed": len(rc),
     "overall": summ(Vp), "by_jury_size": Vp_bysize,
     "false_consensus_by_jury_size": {str(n): dict(unanimous=int(Vp[(Vp.n == n) & Vp.un].shape[0]), unanimous_wrong=int(fc[fc.n == n].shape[0])) for n in (4, 5, 6)},
     "model_scores_primary_only_own_footprint": model_scores(prim),
     "perplexity_533": dict(questions_perplexity_asked=len(P533), model_scores_primary_only_on_533=primary_perp,
                            questions_where_all_six_have_primary_letter=len(all6), model_scores_on_those=model_scores(all6, restrict=set(all6.Question)),
                            jury6_metrics=summ(vote(all6, AI, require_all=True))),
     "final_view_for_comparison": summ(Vf)}
json.dump(M, open("metrics_no_recovery.json", "w"), indent=2, ensure_ascii=False)
# ---------- recovery_impact.csv
rows = []; unF = Vf[Vf.un].Question; fcF = Vf[Vf.un & (Vf.maj != Vf.key)].Question
recq = collections.defaultdict(set)
for q, m in REC: recq[m].add(q)
for a in AI:
    pa = prim[prim[a].notna()]; ra = ans[ans.Question.isin(recq[a]) & ans[a].notna()]; fa = ans[ans[a].notna()]
    kp = int((pa[a] == pa["Bonne réponse"]).sum()); kr = int((ra[a] == ra["Bonne réponse"]).sum()); kf = int((fa[a] == fa["Bonne réponse"]).sum())
    rows.append(dict(model=a, primary_answers=len(pa), primary_correct=kp, primary_accuracy_pct=pct(kp, len(pa)), recovered_answers=len(ra), recovered_correct=kr,
                     recovered_accuracy_pct=pct(kr, len(ra)), final_answers=len(fa), final_correct=kf, final_accuracy_pct=pct(kf, len(fa)),
                     effect_on_final_accuracy_pp=round(pct(kf, len(fa)) - pct(kp, len(pa)), 1),
                     unanimous_questions_with_recovered_answer_from_this_model=int(unF.isin(recq[a]).sum()),
                     false_consensus_with_recovered_answer_from_this_model=int(fcF.isin(recq[a]).sum())))
anyrec = set(rc.question_id)
rows.append(dict(model="ANY_MODEL", recovered_answers=len(rc), unanimous_questions_with_recovered_answer_from_this_model=int(unF.isin(anyrec).sum()),
                 false_consensus_with_recovered_answer_from_this_model=int(fcF.isin(anyrec).sum()), unanimous_total=len(unF), false_consensus_total=len(fcF)))
pd.DataFrame(rows).to_csv("recovery_impact.csv", index=False)
# ---------- jury_sensitivity.csv
def block(name, view, V):
    s = summ(V); return dict(scenario=name, view=view, **{k: (json.dumps(v) if isinstance(v, list) else v) for k, v in s.items()})
J = []
for view, df, V in (("primary_only", prim, Vp), ("final_with_recovery", final, Vf)):
    for n in (4, 5, 6): J.append(block(f"jury_size_{n}", view, V[V.n == n]))
    five = ["Claude", "GPT", "Gemini", "Mistral", "Grok"]
    J.append(block("constant_jury_5_without_Perplexity", view, vote(df, five, require_all=True)))
    J.append(block("constant_jury_6_on_Perplexity_533", view, vote(df[df.Question.isin(P533)], AI, require_all=True)))
pd.DataFrame(J).to_csv("jury_sensitivity.csv", index=False)
# ---------- source_by_topic.csv
T = []
for (s, m), g in Vf.groupby(["Source", "Matière"]):
    sf = summ(g); gp = Vp[(Vp.Source == s) & (Vp["Matière"] == m)]; sp = summ(gp)
    T.append(dict(source=s, topic=m, questions=len(g), flag="SMALL_SAMPLE" if len(g) < 30 else "", mean_agreement_pct=round(100 * g.share.mean(), 1),
                  final_majorities=sf["majorities"], final_majority_accuracy_pct=sf["majority_accuracy_pct"], final_majority_ci95=json.dumps(sf["majority_ci95"]),
                  final_unanimous=sf["unanimous"], final_unanimous_flag="SMALL_SAMPLE" if sf["unanimous"] < 30 else "", final_unanimous_wrong=sf["unanimous_wrong"], final_unanimous_wrong_pct=sf["unanimous_wrong_pct"],
                  primary_questions=sp.get("questions", 0), primary_majority_accuracy_pct=sp.get("majority_accuracy_pct"), primary_unanimous=sp.get("unanimous"), primary_unanimous_wrong=sp.get("unanimous_wrong")))
pd.DataFrame(T).to_csv("source_by_topic.csv", index=False)
# ---------- equal_footprint.csv
E = []
five = ["Claude", "GPT", "Gemini", "Mistral", "Grok"]
for view, df in (("primary_only", prim), ("final_with_recovery", final)):
    c5 = df.dropna(subset=five); sc = model_scores(c5, five)
    for a, v in sc.items(): E.append(dict(comparison="5_models_common_questions", view=view, common_questions=len(c5), model=a, **{k: (json.dumps(x) if isinstance(x, list) else x) for k, x in v.items()}))
    s = summ(vote(c5, five, require_all=True)); E.append(dict(comparison="5_models_common_questions", view=view, common_questions=len(c5), model="MAJORITY_OF_5", answered=s["majorities"], correct=s["majority_correct"], accuracy_pct=s["majority_accuracy_pct"], ci95=json.dumps(s["majority_ci95"])))
    c6 = df[df.Question.isin(P533)].dropna(subset=AI); sc = model_scores(c6, AI)
    for a, v in sc.items(): E.append(dict(comparison="6_models_perplexity_questions", view=view, common_questions=len(c6), model=a, **{k: (json.dumps(x) if isinstance(x, list) else x) for k, x in v.items()}))
    s = summ(vote(c6, AI, require_all=True)); E.append(dict(comparison="6_models_perplexity_questions", view=view, common_questions=len(c6), model="MAJORITY_OF_6", answered=s["majorities"], correct=s["majority_correct"], accuracy_pct=s["majority_accuracy_pct"], ci95=json.dumps(s["majority_ci95"])))
pd.DataFrame(E).to_csv("equal_footprint.csv", index=False)
print("OK sha", sha)