VoLCA EF 3.1 scores vs the official BAFU results

"""Compare VoLCA EF 3.1 scores on BAFU 2026v1 against the official BAFU LCIA Results xlsx.

Oracle: BAFU-2026 v1 LCIA Results.xlsx (sheet 'BAFU_2026 v1'), columns:
IPCC GWP100, Ecological Scarcity 2021 (UBP), EF 3.1 (25 categories).

Scores the four loaded EF 3.1 collections per category:
  - JRC ILCD 'EF-3.1'
  - SimaPro-adapted 1.0, 1.03, and 1.05 (the export BAFU computed the oracle with).
Reports relative-deviation statistics vs the oracle, and - with --report - a
self-contained HTML état des lieux (heatmap + one diagonal scatter per indicator +
four "biggest gaps" tables), all pivoted on the BAFU oracle as the reference.

Usage:
    uv run bafu_oracle_compare.py --xlsx <path to BAFU-2026 v1 LCIA Results.xlsx> \
        [--url http://localhost:8080] [--db bafu-2026v1] \
        [--sample 0] [--seed 42] [--out report.csv] [--report etat_des_lieux.html]

    --sample 0 scores the full matched panel (recommended for the report).

https://www.volca.run/docs/python/
"""

import argparse
import csv
import io
import random
import sys
from collections import defaultdict

from openpyxl import load_workbook
from volca import Client, VoLCAError

BASE_URL = "http://localhost:8080"
DB = "bafu-2026v1"

# oracle column index (0-based) -> (adapted method name, JRC method name)
EF_COLS = {
    26: ("Acidification", "Acidification"),
    27: ("Climate change", "Climate change"),
    28: ("Climate change - Biogenic", "Climate change-Biogenic"),
    29: ("Climate change - Fossil", "Climate change-Fossil"),
    30: ("Climate change - Land use and LU change", "Climate change-Land use and land use change"),
    31: ("Ecotoxicity, freshwater", "Ecotoxicity, freshwater"),
    # NOTE: the oracle's (inorganics)=col32 and (organics)=col33 columns hold
    # SWAPPED data (verified: VoLCA's chemically-correct metal=inorganic split
    # matches col33, and organics match col32). Pair accordingly.
    33: ("Ecotoxicity, freshwater - inorganics", "Ecotoxicity, freshwater_inorganics"),
    32: ("Ecotoxicity, freshwater - organics", "Ecotoxicity, freshwater_organics"),
    34: ("Eutrophication, freshwater", "Eutrophication, freshwater"),
    35: ("Eutrophication, marine", "Eutrophication marine"),
    36: ("Eutrophication, terrestrial", "Eutrophication, terrestrial"),
    37: ("Human toxicity, cancer", "Human toxicity, cancer"),
    38: ("Human toxicity, cancer - inorganics", "Human toxicity, cancer_inorganics"),
    39: ("Human toxicity, cancer - organics", "Human toxicity, cancer_organics"),
    40: ("Human toxicity, non-cancer", "Human toxicity, non-cancer"),
    41: ("Human toxicity, non-cancer - inorganics", "Human toxicity, non-cancer_inorganics"),
    42: ("Human toxicity, non-cancer - organics", "Human toxicity, non-cancer_organics"),
    43: ("Ionising radiation", "Ionising radiation, human health"),
    44: ("Land use", "Land use"),
    45: ("Ozone depletion", "Ozone depletion"),
    46: ("Particulate matter", "EF-particulate Matter"),
    47: ("Photochemical ozone formation", "Photochemical ozone formation - human health"),
    48: ("Resource use, fossils", "Resource use, fossils"),
    49: ("Resource use, minerals and metals", "Resource use, minerals and metals"),
    50: ("Water use", "Water use"),
}
GWP_COL = 4  # IPCC 2021 GWP100, informational cross-check vs Climate change

JRC_COLLECTION = "EF-3.1"
V10_COLLECTION = "Environmental Footprint 3.1 (adapted 1.0)"
V103_COLLECTION = "Environmental Footprint 3.1 (adapted 1.03)"
# BAFU/KBOB's own method export (the one the oracle was computed with) - the reference.
V105_COLLECTION = "Environmental Footprint 3.1 (adapted 1.05)"

# Methods compared against the BAFU oracle reference, in report column order.
# key = row field holding that method's per-category value.
COMPARISONS = [("JRC", "jrc"), ("1.0", "v10"), ("1.03", "v103"), ("1.05", "v105")]


def load_oracle(xlsx):
    wb = load_workbook(xlsx, read_only=True)
    ws = wb["BAFU_2026 v1"]
    rows = ws.iter_rows(values_only=True)
    next(rows)
    next(rows)  # skip 2 header rows
    oracle = {}
    for row in rows:
        if row[0] is None:
            continue
        oracle[str(row[0]).strip()] = row
    return oracle


def check_server(client):
    """Verify the VoLCA server is running before doing any work."""
    try:
        client.get_version()
    except Exception:
        print(f"ERROR: VoLCA server not reachable at {client.base_url}. Start it first.")
        sys.exit(1)


def load_activities(client):
    return {
        f"{a.activity_name} - {a.location}": (a.process_id, a.product_unit)
        for a in client.search_activities(limit=20000)
    }


# factor to multiply an oracle value by, to express it per 1 <volca unit>
# (oracle unit, volca unit) -> factor
UNIT_FACTORS = {
    ("m", "km"): 1000.0,
    ("MJ", "kWh"): 3.6,
    ("personkm", "pkm"): 1.0,
    ("tonkm", "tkm"): 1.0,
    ("Item(s)", "unit"): 1.0,
    ("m2year", "m2a"): 1.0,
    ("m3year", "m3a"): 1.0,
    ("kg-day", "kg day"): 1.0,
}


def unit_factor(oracle_unit, volca_unit):
    if oracle_unit == volca_unit:
        return 1.0
    return UNIT_FACTORS.get((oracle_unit, volca_unit))


def get_impacts_batch(client, pids, collection, chunk=300):
    """pid -> {methodName: score}, scored in chunks via the batch endpoint."""
    out = {}
    for i in range(0, len(pids), chunk):
        part = pids[i : i + chunk]
        for sa in client.score_activities(part, collection=collection).results:
            out[sa.process_id] = {res.method_name: res.score for res in sa.impacts.results}
        print(f"  {collection}: {min(i + chunk, len(pids))}/{len(pids)}", file=sys.stderr)
    return out


def synthesize_v10_ecotox(scores):
    """The adapted 1.0 export splits freshwater-ecotoxicity organics into two USEtox
    parts where 1.03/1.05 keep a single 'organics' category. Rebuild the merged
    organics name so v10 lookups resolve, summing only when both parts are present
    (no silent zero). The freshwater total is rebuilt later by fix_ecotox_totals from
    organics + inorganics - the decomposition the oracle itself uses."""
    for d in scores.values():
        p1 = d.get("Ecotoxicity, freshwater - organics - p.1")
        p2 = d.get("Ecotoxicity, freshwater - organics - p.2")
        if p1 is not None and p2 is not None:
            d["Ecotoxicity, freshwater - organics"] = p1 + p2
    return scores


def rel_dev(volca, oracle):
    if volca is None:
        return None
    if oracle == 0:
        return None if abs(volca) < 1e-12 else float("inf")
    return volca / oracle - 1


def category_stats(cat_rows, key):
    devs = sorted(abs(r[key]) for r in cat_rows if r[key] is not None and r[key] != float("inf"))
    if not devs:
        return "n/a", "n/a"
    med = devs[len(devs) // 2]
    p90 = devs[int(len(devs) * 0.9)] if len(devs) > 1 else devs[-1]
    return f"{med:9.2%}", f"{p90:9.2%}"


# ------------------------------------------------------------------- report ---


def pct_vs_ref(value, ref):
    """relative gap of `value` against the oracle reference `ref` (base = ref)."""
    if value is None or ref is None or ref == 0:
        return None
    return value / ref - 1


def median(xs):
    xs = sorted(xs)
    return xs[len(xs) // 2] if xs else None


def heat_color(v):
    """green (0) -> yellow (~10%) -> red (>=30%) background for a median |gap|."""
    if v is None:
        return "#e5e7eb", "#6b7280"
    t = min(v / 0.30, 1.0)
    if t < 0.5:  # green -> yellow
        f = t / 0.5
        r, g, b = int(26 + f * (250 - 26)), int(150 + f * (204 - 150)), int(80 + f * (92 - 80))
    else:  # yellow -> red
        f = (t - 0.5) / 0.5
        r, g, b = int(250 + f * (215 - 250)), int(204 + f * (48 - 204)), int(92 + f * (39 - 92))
    fg = "#111827" if (0.299 * r + 0.587 * g + 0.114 * b) > 150 else "#f9fafb"
    return f"#{r:02x}{g:02x}{b:02x}", fg


def scatter_svg(indicators, rows_by_ind):
    """One diagonal scatter per indicator: x = oracle value, y = each method."""
    import math

    import matplotlib

    matplotlib.use("Agg")
    import matplotlib.pyplot as plt

    colors = {"JRC": "#e0602c", "1.0": "#8a7fbd", "1.03": "#3aa0d1", "1.05": "#111827"}
    n = len(indicators)
    ncol = 4
    nrow = math.ceil(n / ncol)
    fig, axes = plt.subplots(nrow, ncol, figsize=(4.2 * ncol, 3.6 * nrow))
    axes = axes.flatten()
    for ax, (label, jrc_name) in zip(axes, indicators):
        rows = rows_by_ind.get(jrc_name, [])
        allpos = []
        for lbl, field in COMPARISONS:
            xs, ys = [], []
            for r in rows:
                x, y = r["oracle"], r[field]
                if x is not None and y is not None and x > 0 and y > 0:
                    xs.append(x)
                    ys.append(y)
            if xs:
                ax.scatter(
                    xs, ys, s=8, alpha=0.45, color=colors[lbl], label=lbl,
                    edgecolors="none", rasterized=True,
                )
                allpos += xs + ys
        if allpos:
            lo, hi = min(allpos), max(allpos)
            ax.plot([lo, hi], [lo, hi], "--", color="#9ca3af", lw=0.9, zorder=0)
            ax.set_xscale("log")
            ax.set_yscale("log")
        ax.set_title(label, fontsize=9)
        ax.tick_params(labelsize=6)
        ax.set_xlabel("oracle", fontsize=7)
    for ax in axes[n:]:
        ax.axis("off")
    handles, labels = axes[0].get_legend_handles_labels()
    fig.legend(handles, labels, loc="upper right", fontsize=10, markerscale=1.6, ncol=4)
    fig.suptitle("Chaque méthode (y) vs l'oracle BAFU (x) - sur la diagonale = accord", fontsize=13)
    fig.tight_layout(rect=(0, 0, 1, 0.98))
    buf = io.StringIO()
    fig.savefig(buf, format="svg", dpi=120)  # rasterized points keep the SVG compact
    plt.close(fig)
    svg = buf.getvalue()
    return svg[svg.index("<svg") :]


def fmt(x):
    if x is None:
        return " - "
    if x == 0:
        return "0"
    a = abs(x)
    return f"{x:.3g}" if 1e-3 <= a < 1e6 else f"{x:.2e}"


def fmt_gap(ratio):
    """method/oracle shown as a signed % within an order of magnitude, else a fold-change ×N/÷N.
    ratio == 0 means the method zeroes out a category the oracle scores - an
    infinite fold-change (÷∞), the maximal divergence."""
    if ratio == 0:
        return "÷∞"
    if 0.1 <= ratio <= 10:
        return f"{ratio - 1:+.0%}"
    return f"×{ratio:.0f}" if ratio > 10 else f"÷{1 / ratio:.0f}"


NUMERIC_FIELDS = ("oracle", "jrc", "v10", "v103", "v105", "jrc_dev", "v103_dev", "v105_dev")


def read_rows_csv(path):
    """reload the per-(product, category) rows a prior run wrote, for --report-from."""
    def num(s):
        if s is None or s == "":
            return None
        try:
            return float(s)
        except ValueError:
            return None

    with open(path, newline="") as f:
        out = []
        for r in csv.DictReader(f):
            for k in NUMERIC_FIELDS:
                if k in r:
                    r[k] = num(r[k])
            out.append(r)
    return out


ECOTOX_TOTAL = "Ecotoxicity, freshwater"
ECOTOX_INORG = "Ecotoxicity, freshwater_inorganics"
ECOTOX_ORG = "Ecotoxicity, freshwater_organics"


def fix_ecotox_totals(rows):
    """The 1.05 SimaPro export's bare 'Ecotoxicity, freshwater' category is under-populated
    (5920 CFs vs 61k for organics), so its scored total collapses to ~0. Rebuild the adapted
    totals as organics + inorganics - the decomposition the oracle itself uses - so the adapted
    methods aren't spuriously tiny against the oracle. oracle/JRC totals already equal inorg+org;
    the oracle reference is left untouched. Idempotent: safe to apply to already-corrected rows."""
    sub = defaultdict(dict)
    for r in rows:
        if r["category"] in (ECOTOX_INORG, ECOTOX_ORG):
            sub[r["product"]][r["category"]] = r
    for r in rows:
        if r["category"] != ECOTOX_TOTAL:
            continue
        parts = sub.get(r["product"])
        if not parts or ECOTOX_INORG not in parts or ECOTOX_ORG not in parts:
            continue
        ino, org = parts[ECOTOX_INORG], parts[ECOTOX_ORG]
        for field in ("v10", "v103", "v105"):
            a, b = ino.get(field), org.get(field)
            r[field] = (a + b) if (a is not None and b is not None) else None
    return rows


def build_report(rows, path, meta):
    fix_ecotox_totals(rows)
    indicators = [(ad, jrc) for _, (ad, jrc) in sorted(EF_COLS.items())]
    label_of = {jrc: ad for ad, jrc in indicators}
    rows_by_ind = {}
    for r in rows:
        rows_by_ind.setdefault(r["category"], []).append(r)

    # --- heatmap: median |method/oracle - 1| per (indicator, comparison method) ---
    heat = {}
    for ad, jrc in indicators:
        rr = rows_by_ind.get(jrc, [])
        heat[jrc] = {}
        for lbl, field in COMPARISONS:
            gaps = [abs(g) for g in (pct_vs_ref(r[field], r["oracle"]) for r in rr) if g is not None]
            heat[jrc][lbl] = median(gaps)

    head = "".join(f"<th>{lbl}</th>" for lbl, _ in COMPARISONS)
    hrows = ""
    for ad, jrc in indicators:
        cells = ""
        for lbl, _ in COMPARISONS:
            v = heat[jrc][lbl]
            bg, fg = heat_color(v)
            txt = " - " if v is None else f"{v:.0%}"
            flag = " ⚠" if (v is not None and v > 0.10) else ""
            cells += f'<td style="background:{bg};color:{fg}">{txt}{flag}</td>'
        n = len(rows_by_ind.get(jrc, []))
        hrows += f'<tr><td class="ind">{ad}</td><td class="n">{n}</td>{cells}</tr>'
    heatmap_html = (
        f'<table class="heat"><thead><tr><th>indicateur</th><th>n</th>{head}</tr></thead>'
        f"<tbody>{hrows}</tbody></table>"
    )

    # --- scatter (one per indicator) ---
    scatter_html = scatter_svg(indicators, rows_by_ind)

    # --- four "biggest gaps" tables, pivoted on the oracle ---
    # A %-diff is only meaningful when both sides are comparable. Rank among pairs
    # that share the same sign (LCA water use etc. can go net-negative; a % across a
    # sign flip is nonsense) and where the larger side reaches the indicator's median
    # magnitude (so trace-flow pairs - negligible for the product - don't crowd out
    # real divergences). Ranked by fold-change so a ÷20 counts like a ×20. Dropped
    # pairs are counted, never silently hidden.
    floor = {}
    for ad, jrc in indicators:
        mags = [abs(r["oracle"]) for r in rows_by_ind.get(jrc, []) if r["oracle"] not in (None, 0)]
        floor[jrc] = median(mags) or 0

    tables_html = ""
    for lbl, field in COMPARISONS:
        entries, dropped = [], 0
        for r in rows:
            ref, oth = r["oracle"], r[field]
            if ref is None or oth is None or ref == 0:
                continue
            if (ref > 0) != (oth > 0) or max(abs(ref), abs(oth)) < floor.get(r["category"], 0):
                dropped += 1
                continue
            ratio = oth / ref
            # same-sign guaranteed above, so ratio >= 0; ratio == 0 (other zeroes the
            # category) is an infinite fold-change, ranked at the top rather than as agreement.
            fold = max(ratio, 1 / ratio) if ratio > 0 else float("inf")
            entries.append((fold, r["product"], label_of.get(r["category"], r["category"]), ref, oth, ratio))
        entries.sort(reverse=True)
        body = ""
        for _, prod, ind, ref, other, ratio in entries[:40]:
            body += (
                f"<tr><td>{prod}</td><td>{ind}</td>"
                f'<td class="num">{fmt(ref)}</td><td class="num">{fmt(other)}</td>'
                f'<td class="num gap">{fmt_gap(ratio)}</td></tr>'
            )
        n = len(entries)
        within = sum(1 for e in entries if abs(e[5] - 1) <= 0.10)
        noise = f", {dropped} non comparables écartées" if dropped else ""
        share = f"{within} à ≤10 % ({within / n:.0%}), top 40 des plus gros écarts{noise}" if n else "aucune paire comparable"
        tables_html += (
            f'<h3>oracle vs {lbl} <span class="sub"> - {n} paires, {share}</span></h3>'
            '<table class="gaps"><thead><tr><th>produit</th><th>indicateur</th>'
            f"<th>oracle</th><th>{lbl}</th><th>écart (base oracle)</th></tr></thead>"
            f"<tbody>{body}</tbody></table>"
        )

    html = f"""<!doctype html><html lang="fr"><head><meta charset="utf-8">
<title>BAFU 2026v1 - état des lieux EF 3.1</title>
<style>
 body{{font-family:-apple-system,Segoe UI,Roboto,sans-serif;margin:24px;color:#111827;background:#fff;line-height:1.4}}
 h1{{font-size:22px}} h2{{font-size:17px;margin-top:34px;border-bottom:2px solid #e5e7eb;padding-bottom:4px}}
 h3{{font-size:14px;margin-top:22px}} .sub{{font-weight:400;color:#6b7280;font-size:12px}}
 .lead{{color:#374151;max-width:60em}}
 table{{border-collapse:collapse;font-size:12px}} td,th{{padding:3px 7px;border:1px solid #e5e7eb}}
 .heat td,.heat th{{text-align:center}} .heat .ind{{text-align:left}} .heat .n{{color:#9ca3af}}
 .heat td:first-child{{background:#f9fafb}}
 .gaps td.num{{text-align:right;font-variant-numeric:tabular-nums}} .gaps td.gap{{font-weight:600}}
 .gaps tbody tr:nth-child(even){{background:#f9fafb}}
 svg{{max-width:100%;height:auto}}
 .meta{{color:#6b7280;font-size:12px}}
</style></head><body>
<h1>BAFU 2026v1 - état des lieux EF 3.1 (4 méthodes vs oracle)</h1>
<p class="lead">Comparaison des quatre collections EF 3.1 (JRC, adaptées 1.0, 1.03, 1.05) contre
l'<b>oracle officiel BAFU</b>, pris comme référence. La série <i>1.05</i> est l'export exact avec
lequel BAFU a calculé l'oracle : son écart à l'oracle est donc le <b>résidu de reproduction</b> du
moteur - proche de 0 partout, sauf sur les écarts déjà diagnostiqués comme justifiés (écotoxicité -
émissions long-terme exclues de l'export ; ozone photochimique - archétype NOx). Les séries
<i>JRC / 1.0 / 1.03</i> ajoutent la dérive entre versions de méthode (le chrome VI de la JRC, l'eau…).</p>
<p class="meta">{meta}</p>

<h2>1 · Synthèse - médiane de l'écart absolu vs l'oracle, par indicateur × méthode</h2>
<p class="lead">Vert = accord, rouge = écart, ⚠ marque les cases &gt; 10 %. La colonne <i>1.05</i>
proche de 0 confirme que le moteur reproduit fidèlement l'oracle ; ses seules cases rouges
sont les résidus justifiés. Les écarts <i>JRC / 1.0 / 1.03</i> ajoutent la dérive de méthode.</p>
{heatmap_html}

<h2>2 · Nuages diagonaux - un par indicateur</h2>
{scatter_html}

<h2>3 · Plus gros écarts - les quatre méthodes contre l'oracle</h2>
<p class="lead">Une ligne par (produit, indicateur), triée par écart décroissant, en base oracle (au-delà d'un
ordre de grandeur, l'écart est noté ×N ou ÷N). Ne sont classées que les paires <b>comparables</b> :
<b>de même signe</b> (un % à travers un changement de signe - l'eau nette peut être négative en ACV -
n'a pas de sens) et dont le plus grand des deux côtés atteint la magnitude médiane de l'indicateur
(sinon le flux est en trace pour ce produit). Le nombre de paires non comparables écartées est indiqué.</p>
{tables_html}
</body></html>"""
    with open(path, "w") as f:
        f.write(html)
    return path


def panel_meta(rows):
    n_prod = len({r["product"] for r in rows})
    return f"{n_prod} produits × {len(EF_COLS)} indicateurs · panel apparié oracle ∩ bafu-2026v1"


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--xlsx", required=True, help="path to 'BAFU-2026 v1 LCIA Results.xlsx'")
    ap.add_argument("--url", default=BASE_URL, help="VoLCA server base URL")
    ap.add_argument("--db", default=DB, help="loaded database name")
    ap.add_argument("--sample", type=int, default=0, help="products to score (0 = full matched panel)")
    ap.add_argument("--seed", type=int, default=42)
    ap.add_argument("--out", default="bafu_oracle_compare.csv")
    ap.add_argument("--report", default=None, help="write a self-contained HTML état des lieux")
    ap.add_argument("--report-from", default=None, help="build the report from an existing CSV (no scoring)")
    args = ap.parse_args()

    if args.report_from:
        rows = read_rows_csv(args.report_from)
        target = args.report or "etat_des_lieux.html"
        print(f"rapport écrit : {build_report(rows, target, panel_meta(rows))}")
        return

    client = Client(base_url=args.url, db=args.db)
    check_server(client)
    client.load_database(args.db)  # no-op if already loaded
    oracle = load_oracle(args.xlsx)
    acts = load_activities(client)
    matched = sorted(set(oracle) & set(acts))
    print(f"oracle products: {len(oracle)}, volca activities: {len(acts)}, matched: {len(matched)}")
    unmatched = set(oracle) - set(acts)
    if unmatched:
        print(f"unmatched oracle products: {len(unmatched)} (e.g. {sorted(unmatched)[:5]})")

    if args.sample and 0 < args.sample < len(matched):
        random.seed(args.seed)
        sample = random.sample(matched, args.sample)
    else:
        sample = matched

    work = []
    skipped_unit = []
    for name in sample:
        pid, volca_unit = acts[name]
        factor = unit_factor(str(oracle[name][3]), volca_unit)
        if factor is None:
            print(f"[skip] {name}: no unit factor {oracle[name][3]} -> {volca_unit}", file=sys.stderr)
            skipped_unit.append(name)
            continue
        work.append((name, pid, factor))

    pids = sorted({pid for _, pid, _ in work})
    jrc_scores = get_impacts_batch(client, pids, JRC_COLLECTION)
    v103_scores = get_impacts_batch(client, pids, V103_COLLECTION)

    def optional(collection, synth=None):
        try:
            s = get_impacts_batch(client, pids, collection)
            return synth(s) if synth else s
        except VoLCAError:
            print(f"[info] collection '{collection}' not loaded - skipping", file=sys.stderr)
            return {}

    v105_scores = optional(V105_COLLECTION)
    v10_scores = optional(V10_COLLECTION, synthesize_v10_ecotox)

    rows = []
    skipped_score = []
    for name, pid, factor in work:
        jrc, v103 = jrc_scores.get(pid), v103_scores.get(pid)
        v105, v10 = v105_scores.get(pid, {}), v10_scores.get(pid, {})
        if jrc is None or v103 is None:
            print(f"[skip] {name}: no score returned for pid {pid}", file=sys.stderr)
            skipped_score.append(name)
            continue
        orow = oracle[name]
        for col, (ad_name, jrc_name) in EF_COLS.items():
            oval = orow[col]
            if oval is None:
                continue
            oval = float(oval) * factor
            if abs(oval) < 1e-12:
                continue
            rows.append(
                {
                    "product": name,
                    "category": jrc_name,
                    "oracle": oval,
                    "jrc": jrc.get(jrc_name),
                    "v10": v10.get(ad_name),
                    "v103": v103.get(ad_name),
                    "v105": v105.get(ad_name),
                    "jrc_dev": rel_dev(jrc.get(jrc_name), oval),
                    "v103_dev": rel_dev(v103.get(ad_name), oval),
                    "v105_dev": rel_dev(v105.get(ad_name), oval),
                }
            )

    skipped = len(skipped_unit) + len(skipped_score)
    print(f"\nscored {len(sample) - skipped}/{len(sample)} products ({skipped} skipped, see stderr)")
    if not rows:
        print("no rows to report - every product was skipped", file=sys.stderr)
        sys.exit(1)

    fix_ecotox_totals(rows)
    for r in rows:  # keep the vs-oracle deviations consistent with the rebuilt total
        if r["category"] == ECOTOX_TOTAL:
            o = r["oracle"]
            r["v103_dev"] = rel_dev(r["v103"], o)
            r["v105_dev"] = rel_dev(r["v105"], o)

    with open(args.out, "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=rows[0].keys())
        w.writeheader()
        w.writerows(rows)

    # per-category summary vs oracle (median/p90 of |dev|)
    print(f"\n{'category':<45} {'n':>4}  {'JRC med':>9} {'JRC p90':>9}  {'103 med':>9} {'103 p90':>9}  {'105 med':>9} {'105 p90':>9}")
    for col, (_, jrc_name) in EF_COLS.items():
        cat_rows = [r for r in rows if r["category"] == jrc_name]
        jm, j9 = category_stats(cat_rows, "jrc_dev")
        am, a9 = category_stats(cat_rows, "v103_dev")
        vm, v9 = category_stats(cat_rows, "v105_dev")
        print(f"{jrc_name:<45} {len(cat_rows):>4}  {jm} {j9}  {am} {a9}  {vm} {v9}")

    if args.report:
        out = build_report(rows, args.report, panel_meta(rows))
        print(f"\nrapport écrit : {out}")


if __name__ == "__main__":
    main()