#!/usr/bin/env python3
"""KAR-824 Ground-Truth-Verifikation gegen Master-Prompt §2.
Liest alle 9 Referenzdateien mit openpyxl data_only (keine Makros, keine Links).
Jede §2-Zahl wird nachgemessen; Abweichung > 0.01 = FAIL.
"""
import re
import sys
from pathlib import Path

import openpyxl

BASE = Path("/root/aria/work/qaf-compare-kar824/input")
TOL = 0.01

results = []


def check(name, actual, expected, tol=TOL):
    if isinstance(expected, (int, float)) and isinstance(actual, (int, float)):
        ok = abs(actual - expected) <= tol
    else:
        ok = actual == expected
    results.append((ok, name, actual, expected))
    return ok


def fnum(v):
    return float(v) if v is not None else None


COST_TAB = re.compile(r"^\d+_2")

print("=== 2.1 BMW_DETAIL_TABS_G60 ===", flush=True)
bmw = {}
for key, fname in [("basis", "100_G60_DP_QAF_Basis_24_10_BMW.xlsm"),
                   ("repricing", "20260508_BMW_QAF_G60_DP_RePricing_HO.xlsm")]:
    wb = openpyxl.load_workbook(BASE / fname, data_only=True, read_only=True, keep_links=False)
    names = wb.sheetnames
    cost = [n for n in names if COST_TAB.match(n.strip())]
    agg = {}
    dj41 = {}
    for n in cost:
        ws = wb[n]
        for col in ("W", "AT", "BF", "DJ"):
            v = ws[f"{col}41"].value
            if isinstance(v, (int, float)):
                agg[col] = agg.get(col, 0.0) + float(v)
                if col == "DJ":
                    dj41[n] = float(v)
    inp = wb["INPUT"]["C19"].value
    bmw[key] = {"names": names, "cost": cost, "agg": agg, "dj41": dj41, "c19": inp}
    wb.close()
    print(f"  {key}: {len(names)} sheets, {len(cost)} Kostenreiter, INPUT!C19={inp!r}")

check("BMW Basis: 240 Sheets", len(bmw["basis"]["names"]), 240)
check("BMW RePricing: 241 Sheets", len(bmw["repricing"]["names"]), 241)
check("BMW Basis: 112 Kostenreiter", len(bmw["basis"]["cost"]), 112)
check("BMW RePricing: 112 Kostenreiter", len(bmw["repricing"]["cost"]), 112)
check("BMW Basis INPUT!C19", bmw["basis"]["c19"], "EUR")
check("BMW RePricing INPUT!C19", bmw["repricing"]["c19"], "EUR")

exact = set(bmw["basis"]["cost"]) & set(bmw["repricing"]["cost"])
only_b = set(bmw["basis"]["cost"]) - set(bmw["repricing"]["cost"])
only_r = set(bmw["repricing"]["cost"]) - set(bmw["basis"]["cost"])
check("BMW: 111 exakte Namensmatches", len(exact), 111)
check("BMW: Alias links = {'75_2_a'}", only_b, {"75_2_a"})
check("BMW: Alias rechts = {'75_2a'}", only_r, {"75_2a"})

check("ΣDJ41 Basis = 384,62", round(bmw["basis"]["agg"]["DJ"], 2), 384.62)
check("ΣDJ41 RePricing = 505,13", round(bmw["repricing"]["agg"]["DJ"], 2), 505.13)
check("ΔDJ41 = +120,51", round(bmw["repricing"]["agg"]["DJ"] - bmw["basis"]["agg"]["DJ"], 2), 120.51)
dpct = (bmw["repricing"]["agg"]["DJ"] - bmw["basis"]["agg"]["DJ"]) / bmw["basis"]["agg"]["DJ"] * 100
check("ΔDJ41 % = 31,33", round(dpct, 2), 31.33)
check("ΣBF41 Basis = 304,11", round(bmw["basis"]["agg"]["BF"], 2), 304.11)
check("ΣBF41 RePricing = 337,01", round(bmw["repricing"]["agg"]["BF"], 2), 337.01)
check("ΣW41 Basis = 255,75", round(bmw["basis"]["agg"]["W"], 2), 255.75)
check("ΣW41 RePricing = 242,75", round(bmw["repricing"]["agg"]["W"], 2), 242.75)
check("ΣAT41 Basis = 16,94", round(bmw["basis"]["agg"]["AT"], 2), 16.94)
check("ΣAT41 RePricing = 49,74", round(bmw["repricing"]["agg"]["AT"], 2), 49.74)


def parse_cost_tab_key(name):
    m = re.match(r"^(\d+)_2(.*)$", name.strip())
    if not m:
        return None
    station = int(m.group(1))
    rest = m.group(2).strip()
    paren = re.search(r"\((\d+)\)", rest)
    letter = re.search(r"_?([A-Za-z])$", rest)
    if paren:
        inst = "p" + paren.group(1)
    elif letter:
        inst = "l" + letter.group(1).lower()
    else:
        inst = ""
    return (station, inst)


for key in ("basis", "repricing"):
    keys = {}
    collisions = []
    for n in bmw[key]["cost"]:
        k = parse_cost_tab_key(n)
        if k in keys:
            collisions.append((keys[k], n))
        keys[k] = n
    check(f"BMW {key}: 112 eindeutige Keys, 0 Kollisionen", (len(keys), len(collisions)), (112, 0))

check("Key 75_2_a == Key 75_2a", parse_cost_tab_key("75_2_a"), parse_cost_tab_key("75_2a"))
check("Key 8_2 (2) = (8,'p2')", parse_cost_tab_key("8_2 (2)"), (8, "p2"))
check("Key 82_2 = (82,'')", parse_cost_tab_key("82_2"), (82, ""))
check("Key 08_2 = (8,'')", parse_cost_tab_key("08_2"), (8, ""))

# Top-5 Treiber nach |ΔDJ41| über strukturierte Keys
kb = {parse_cost_tab_key(n): v for n, v in bmw["basis"]["dj41"].items()}
kr = {parse_cost_tab_key(n): v for n, v in bmw["repricing"]["dj41"].items()}
deltas = {k: kr.get(k, 0) - kb.get(k, 0) for k in set(kb) | set(kr)}
top5 = sorted(deltas.items(), key=lambda kv: -abs(kv[1]))[:5]
name_by_key = {parse_cost_tab_key(n): n for n in bmw["basis"]["dj41"]}
top5_named = [(name_by_key.get(k, str(k)), round(d, 2)) for k, d in top5]
print(f"  Top-5 Treiber: {top5_named}")
expected_top5 = {"61_2": 14.35, "06_2": 10.93, "8_2 (2)": 3.30, "83_2": 2.90, "08_2": 2.89}
check("Top-5 Treiber-Namen", {n for n, _ in top5_named}, set(expected_top5))
for n, d in top5_named:
    if n in expected_top5:
        check(f"Treiber {n} Δ={expected_top5[n]}", d, expected_top5[n])

print("\n=== 2.2 QAF_LEGACY_DE_SUMMARY ===", flush=True)
legacy_files = {
    "Kiekert FTL": ("Vergabe_Kiekert_20230214_QAF_H2_eO_11Mio_FTL_ZV_fin.xlsx",
                    {"M5": "Systemschloss eÖ", "M8": "FTL ZV", "P11": 4.8211, "P12": 1.3321,
                     "P13": 6.1532, "P24": 9.0236, "P29": 9.0236}),
    "Kiekert HT": ("Vergabe_Kiekert_20230314_QAF_H2_eO_11Mio_HT_ZV_MKS_fin.xlsx",
                   {"M5": "Systemschloss eÖ", "M8": "TSH ZV MKS", "P11": 5.2650, "P12": 1.3321,
                    "P13": 6.5971, "P24": 9.5706, "P29": 9.5706}),
    "Brose A16 FT": ("Vergabe2023_Brose_Anlage_16_QAF_B_Schloss_FT_links_12_Mio_Sz.xlsx",
                     {"I8": "5A69441", "M8": "Fahrertür links", "P11": 5.2984, "P12": 1.3504,
                      "P13": 6.6489, "P29": 7.6804}),
    "Brose A18 HT": ("Vergabe2023_Brose_Anlage_18_QAF_B_Schloss_HT_links_12_Mio_Sz.xlsx",
                     {"I8": "5A69451", "M8": "Hintertür links", "P11": 5.3754, "P12": 1.3504,
                      "P13": 6.7259, "P29": 7.6461}),
}
for label, (fname, exp) in legacy_files.items():
    wb = openpyxl.load_workbook(BASE / fname, data_only=True, read_only=True, keep_links=False)
    names = wb.sheetnames
    has = all(s in names for s in ("Zusammenfassung", "Material", "Fertigungskosten"))
    check(f"{label}: Pflicht-Sheets vorhanden", has, True)
    zs = wb["Zusammenfassung"]
    b4 = zs["B4"].value
    check(f"{label}: B4 QAF Version 8.x", bool(b4 and "8" in str(b4) and "version" in str(b4).lower()), True)
    check(f"{label}: P10 Header EUR", zs["P10"].value, "EUR")
    check(f"{label}: C26 Bestellwährung EUR", zs["C26"].value, "EUR")
    for cell, want in exp.items():
        v = zs[cell].value
        if isinstance(want, float):
            check(f"{label}: {cell} = {want}", round(fnum(v), 4) if v is not None else None, want, tol=0.0001)
        else:
            got = str(v).strip() if v is not None else None
            ok_val = got == want if cell != "M8" else (got is not None and want in got)
            results.append((ok_val, f"{label}: {cell} ≈ {want!r}", got, want))
    if label.startswith("Kiekert"):
        check(f"{label}: I8 leer", zs["I8"].value in (None, ""), True)
    print(f"  {label}: B4={b4!r}")
    wb.close()

print("\n=== 2.3 QAF_V9_SUMMARY ===", flush=True)
v9_files = {
    "Brose FT ZV": ("aktuell_Brose_QAF_B-Schloss_FT_links_ZV_20260324.xlsx",
                    {"I8": "5A69441", "P11": 5.9353, "P12": 1.9610, "P13": 7.8963,
                     "P24": 9.1084, "P30": 8.0268, "P28": -1.0816}),
    "Brose HT MKS": ("aktuell_Brose_QAF_B-Schloss_HT_links_3.MKS_Kisi_20260324.xlsx",
                     {"I8": "5A8FBD3", "P11": 6.4751, "P12": 2.0259, "P13": 8.5010,
                      "P24": 9.7419, "P30": 8.6015}),
}
for label, (fname, exp) in v9_files.items():
    wb = openpyxl.load_workbook(BASE / fname, data_only=True, read_only=True, keep_links=False)
    names = wb.sheetnames
    has = all(s in names for s in ("SUMMARY", "MATERIAL", "MANUFACTURING COSTS", "A1"))
    check(f"{label}: Pflicht-Sheets vorhanden", has, True)
    sm = wb["SUMMARY"]
    b4 = sm["B4"].value
    check(f"{label}: B4 QAF Version 9.x", bool(b4 and "9" in str(b4) and "version" in str(b4).lower()), True)
    check(f"{label}: P10 Header EUR", sm["P10"].value, "EUR")
    check(f"{label}: C25 Bestellwährung EUR (nicht C26!)", sm["C25"].value, "EUR")
    for cell, want in exp.items():
        v = sm[cell].value
        if isinstance(want, float):
            check(f"{label}: {cell} = {want}", round(fnum(v), 4) if v is not None else None, want, tol=0.0001)
        else:
            check(f"{label}: {cell} = {want!r}", str(v).strip() if v is not None else None, want)
    print(f"  {label}: B4={b4!r}, M5={sm['M5'].value!r}, M8={sm['M8'].value!r}")
    wb.close()

print("\n" + "=" * 70)
fails = [r for r in results if not r[0]]
for ok, name, actual, expected in results:
    mark = "✓" if ok else "✗ FAIL"
    if not ok:
        print(f"{mark}  {name}: IST={actual!r} SOLL={expected!r}")
print(f"\n{len(results) - len(fails)}/{len(results)} PASS, {len(fails)} FAIL")
sys.exit(1 if fails else 0)