#!/usr/bin/env python3
"""
Read out/apex-orders-2026-q3.xlsx back and check it.

Apex Instruments is fictional. Every name, figure, and contact is demo data.

openpyxl does not calculate, and Excel has never opened this file, so no formula
has a cached result. This script therefore contains a small formula evaluator:
it parses each formula, resolves the ranges against the values actually stored in
the file, and computes the answer. Those answers are compared against totals
recomputed from scratch in plain Python from the input columns only. Three things
have to agree: the formula, the independent recomputation, and clean-report.json.

    python3 verify-workbook.py        exits non-zero on the first failure
"""
import json, os, re, sys, zipfile
from datetime import datetime, date
from xml.etree import ElementTree

from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string, get_column_letter

HERE = os.path.dirname(os.path.abspath(__file__))
XLSX = os.path.join(HERE, "out", "apex-orders-2026-q3.xlsx")
REPORT = json.load(open(os.path.join(HERE, "data", "clean-report.json"), encoding="utf-8"))

FAILS, CHECKS = [], []


def ok(label, detail=""):
    CHECKS.append(label)
    print("  ok    %-58s %s" % (label, detail))


def check(cond, label, detail=""):
    if cond:
        ok(label, detail)
    else:
        FAILS.append(label)
        print("  FAIL  %-58s %s" % (label, detail))


def near(a, b, tol=0.02):
    return a is not None and b is not None and abs(float(a) - float(b)) <= tol


# ======================================================== formula evaluator ==
TOKEN = re.compile(r"""
    (?P<sheetref>'[^']+'!\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?)
  | (?P<ref>\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?)
  | (?P<num>\d+\.?\d*)
  | (?P<str>"[^"]*")
  | (?P<func>[A-Z]+(?=\())
  | (?P<op>[-+*/(),])
""", re.X)


class Model(object):
    """Lazily evaluates the formulas stored in a workbook."""

    def __init__(self, wb):
        self.wb = wb
        self.cache = {}
        self.stack = []

    # ---- cell access -----------------------------------------------------
    def cell(self, sheet, ref):
        ref = ref.replace("$", "")
        key = (sheet, ref)
        if key in self.cache:
            return self.cache[key]
        if key in self.stack:
            raise ValueError("circular reference at %s!%s" % key)
        v = self.wb[sheet][ref].value
        if isinstance(v, str) and v.startswith("="):
            self.stack.append(key)
            try:
                v = self.eval(v[1:], sheet)
            finally:
                self.stack.pop()
        self.cache[key] = v
        return v

    def area(self, sheet, ref):
        ref = ref.replace("$", "")
        if ":" not in ref:
            return [self.cell(sheet, ref)]
        a, b = ref.split(":")
        c1, r1 = re.match(r"([A-Z]+)(\d+)", a).groups()
        c2, r2 = re.match(r"([A-Z]+)(\d+)", b).groups()
        out = []
        for ci in range(column_index_from_string(c1), column_index_from_string(c2) + 1):
            for ri in range(int(r1), int(r2) + 1):
                out.append(self.cell(sheet, "%s%d" % (get_column_letter(ci), ri)))
        return out

    # ---- parser ----------------------------------------------------------
    def eval(self, src, sheet):
        # Re-entrant: a formula that reads a cell holding another formula recurses
        # straight back into here, so the parser state has to be stacked.
        saved = (getattr(self, "toks", None), getattr(self, "i", 0), getattr(self, "sheet", None))
        self.toks = [m for m in TOKEN.finditer(src)]
        self.i = 0
        self.sheet = sheet
        try:
            v = self._expr()
            if self.i != len(self.toks):
                raise ValueError("trailing tokens in %r" % src)
            return v
        finally:
            self.toks, self.i, self.sheet = saved

    def _peek(self):
        return self.toks[self.i] if self.i < len(self.toks) else None

    def _expr(self):
        v = self._term()
        while True:
            t = self._peek()
            if t and t.lastgroup == "op" and t.group() in "+-":
                self.i += 1
                r = self._term()
                v = (v or 0) + (r or 0) if t.group() == "+" else (v or 0) - (r or 0)
            else:
                return v

    def _term(self):
        v = self._atom()
        while True:
            t = self._peek()
            if t and t.lastgroup == "op" and t.group() in "*/":
                self.i += 1
                r = self._atom()
                v = (v or 0) * (r or 0) if t.group() == "*" else (v or 0) / float(r)
            else:
                return v

    def _atom(self):
        t = self._peek()
        if t is None:
            raise ValueError("unexpected end of formula")
        self.i += 1
        g, s = t.lastgroup, t.group()
        if g == "num":
            return float(s)
        if g == "str":
            return s[1:-1]
        if g == "sheetref":
            sh, ref = s.split("!")
            return self._resolve(sh.strip("'"), ref)
        if g == "ref":
            return self._resolve(self.sheet, s)
        if g == "func":
            self.i += 1                       # consume the opening paren
            args = self._args()
            return self._call(s, args)
        if s == "(":
            v = self._expr()
            self.i += 1                       # consume the closing paren
            return v
        if s == "-":
            return -(self._atom() or 0)
        raise ValueError("unexpected token %r" % s)

    def _resolve(self, sheet, ref):
        return self.area(sheet, ref) if ":" in ref else self.cell(sheet, ref)

    def _args(self):
        """Read arguments up to the matching close paren. Ranges stay as lists."""
        args = []
        while True:
            t = self._peek()
            if t and t.lastgroup == "op" and t.group() == ")":
                self.i += 1
                return args
            args.append(self._expr())
            t = self._peek()
            if t and t.lastgroup == "op" and t.group() == ",":
                self.i += 1

    # ---- functions -------------------------------------------------------
    @staticmethod
    def _match(value, criteria):
        if isinstance(criteria, str):
            m = re.match(r"^(<=|>=|<>|<|>)\s*(.+)$", criteria)
            if m:
                op, rhs = m.group(1), m.group(2)
                try:
                    a, b = float(value), float(rhs)
                except (TypeError, ValueError):
                    return False
                return {"<": a < b, ">": a > b, "<=": a <= b,
                        ">=": a >= b, "<>": a != b}[op]
            return str(value).strip().lower() == criteria.strip().lower()
        return value == criteria

    def _call(self, name, a):
        nums = lambda xs: [x for x in xs if isinstance(x, (int, float))]
        if name == "SUM":
            flat = []
            for x in a:
                flat.extend(x if isinstance(x, list) else [x])
            return round(sum(nums(flat)), 10)
        if name == "COUNTA":
            return sum(1 for x in a[0] if x is not None and x != "")
        if name == "MAX":
            return max(nums(a[0]))
        if name == "ROUND":
            return round(float(a[0]), int(a[1]))
        if name == "IFERROR":
            return a[0]
        if name == "COUNTIF":
            return sum(1 for x in a[0] if self._match(x, a[1]))
        if name == "SUMIF":
            rng, crit, sums = a[0], a[1], a[2]
            return round(sum(s for r, s in zip(rng, sums)
                             if self._match(r, crit) and isinstance(s, (int, float))), 10)
        if name == "TEXT":
            v, fmt = a[0], a[1]
            if isinstance(v, (datetime, date)):
                return v.strftime({"yyyy-mm": "%Y-%m", "yyyy-mm-dd": "%Y-%m-%d"}[fmt])
            raise ValueError("TEXT on a non date: %r" % (v,))
        if name == "MATCH":
            needle, hay = a[0], a[1]
            for i, x in enumerate(hay):
                if x == needle or near(x, needle) if isinstance(x, (int, float)) and isinstance(needle, (int, float)) else x == needle:
                    return i + 1
            raise ValueError("MATCH found nothing")
        if name == "INDEX":
            return a[0][int(a[1]) - 1]
        raise ValueError("no evaluator for %s" % name)


# ================================================================ load =======
print("Reading %s" % os.path.relpath(XLSX, HERE))
check(os.path.exists(XLSX), "workbook exists")
wb = load_workbook(XLSX)          # formulas, not cached values
M = Model(wb)

# ---------------------------------------------------------- 1. structure ----
print("\nSTRUCTURE")
WANT = ["Read me", "Summary", "By month", "By product", "Clean data", "Rejected rows"]
check(wb.sheetnames == WANT, "six sheets, in order", ", ".join(wb.sheetnames))

merged = {n: [str(r) for r in wb[n].merged_cells.ranges] for n in wb.sheetnames}
total_merged = sum(len(v) for v in merged.values())
check(total_merged == 0, "no merged cells anywhere, so none inside a data range",
      "%d found" % total_merged)

wsC, wsS, wsM, wsP, wsX, wsR = (wb["Clean data"], wb["Summary"], wb["By month"],
                                wb["By product"], wb["Rejected rows"], wb["Read me"])
N = REPORT["counts"]["rowsOut"]
LAST = N + 1
check(wsC.max_row == LAST, "Clean data holds every imported row",
      "%d rows under the header" % (wsC.max_row - 1))
check(wsC.freeze_panes == "C2", "Clean data header is frozen", str(wsC.freeze_panes))
check(wsC.auto_filter.ref == "A1:V%d" % LAST, "Clean data auto filter covers the table",
      str(wsC.auto_filter.ref))
check(wsX.auto_filter.ref is not None, "Rejected rows has a filter too", str(wsX.auto_filter.ref))

# column widths
missing_w, tight = [], []
# Row ranges are the table bodies only. Notes that sit below a table are wrapped or
# are meant to run across the empty columns beside them.
for ws, r0, r1 in ((wsC, 2, LAST), (wsX, 5, 4 + len(REPORT["rejects"]["rows"])),
                   (wsM, 4, 8), (wsP, 4, 11)):
    for ci in range(1, ws.max_column + 1):
        L = get_column_letter(ci)
        w = ws.column_dimensions[L].width
        if not w:
            missing_w.append("%s!%s" % (ws.title, L))
            continue
        longest = 0
        for ri in range(r0, min(r1, 600) + 1):
            c = ws["%s%d" % (L, ri)]
            if isinstance(c.value, str) and not c.value.startswith("=") and not c.alignment.wrap_text:
                longest = max(longest, len(c.value))
        if longest > w:
            tight.append("%s!%s width %.0f, longest value %d" % (ws.title, L, w, longest))
check(not missing_w, "every used column has an explicit width", "%d columns sized" % (
    wsC.max_column + wsX.max_column + wsM.max_column + wsP.max_column))
check(not tight, "no unwrapped text is wider than its column", "; ".join(tight))

# number formats
check(wsC["B2"].number_format == "yyyy-mm-dd", "dates carry a date format", wsC["B2"].number_format)
check(wsC["J2"].number_format == '"$"#,##0.00', "money carries a currency format", wsC["J2"].number_format)
check(wsC["K2"].number_format == "0.0%", "discount carries a percent format", wsC["K2"].number_format)
check(isinstance(wsC["B2"].value, (datetime, date)), "dates are real dates, not text",
      type(wsC["B2"].value).__name__)
check(isinstance(wsC["I2"].value, int), "quantity is a whole number", repr(wsC["I2"].value))

named = set(wb._named_styles) if hasattr(wb, "_named_styles") else set()
names = {s.name if hasattr(s, "name") else s for s in wb._named_styles}
check(len([n for n in names if str(n).startswith("apex")]) >= 10,
      "named styles are registered", "%d apex styles" % len([n for n in names if str(n).startswith("apex")]))

# charts
check(len(wsM._charts) == 1 and wsM._charts[0].tagname == "lineChart",
      "By month carries a native line chart",
      "%s, %d series" % (wsM._charts[0].tagname, len(wsM._charts[0].series)) if wsM._charts else "none")
check(len(wsP._charts) == 1 and wsP._charts[0].tagname == "barChart",
      "By product carries a native bar chart",
      "%s, %d series" % (wsP._charts[0].tagname, len(wsP._charts[0].series)) if wsP._charts else "none")

cf = [str(r) for r in wsP.conditional_formatting]
cf_rules = sum(len(x.rules) for x in wsP.conditional_formatting)
check(cf_rules >= 2, "By product margin column is conditionally formatted",
      "%d rules on %s" % (cf_rules, ", ".join(cf)))

# ------------------------------------ 2. recompute the truth independently ---
print("\nINDEPENDENT RECOMPUTATION FROM THE STORED INPUT COLUMNS")
rows = []
for r in range(2, LAST + 1):
    rows.append({
        "date": wsC["B%d" % r].value,
        "product": wsC["H%d" % r].value,
        "qty": wsC["I%d" % r].value,
        "price": wsC["J%d" % r].value,
        "disc": wsC["K%d" % r].value,
        "ucost": wsC["N%d" % r].value,
        "ship": wsC["Q%d" % r].value,
        "channel": wsC["R%d" % r].value,
    })
for x in rows:
    x["gross"] = x["qty"] * x["price"]
    x["net"] = round(x["gross"] * (1 - x["disc"]), 2)
    x["cost"] = x["qty"] * x["ucost"]
    x["month"] = x["date"].strftime("%Y-%m")

T = {
    "orders": len(rows),
    "units": sum(x["qty"] for x in rows),
    "gross": round(sum(x["gross"] for x in rows), 2),
    "net": round(sum(x["net"] for x in rows), 2),
    "cost": round(sum(x["cost"] for x in rows), 2),
    "ship": round(sum(x["ship"] for x in rows), 2),
}
T["discount"] = round(T["gross"] - T["net"], 2)
T["margin"] = round(T["net"] - T["cost"], 2)
print("  recomputed: %d orders, %d units, net $%s, margin $%s" % (
    T["orders"], T["units"], "{:,.2f}".format(T["net"]), "{:,.2f}".format(T["margin"])))

R = REPORT["totals"]
for k in ("orders", "units", "gross", "net", "cost", "discount", "margin"):
    check(near(T[k], R[k]), "clean-report.json agrees on %s" % k,
          "%s vs %s" % (T[k], R[k]))

# ------------------------------------------- 3. evaluate the real formulas ---
print("\nSUMMARY FORMULAS, EVALUATED FROM THE FILE")
EXPECT = [
    ("B5",  "Orders imported",       T["orders"]),
    ("B6",  "Units sold",            T["units"]),
    ("B7",  "Gross revenue",         T["gross"]),
    ("B8",  "Discount given",        T["discount"]),
    ("B9",  "Net revenue",           T["net"]),
    ("B10", "Cost of goods",         T["cost"]),
    ("B11", "Gross margin",          T["margin"]),
    ("B12", "Gross margin percent",  T["margin"] / T["net"]),
    ("E5",  "Average order value",   T["net"] / T["orders"]),
    ("E6",  "Average discount",      T["discount"] / T["gross"]),
    ("E7",  "Partner net revenue",   round(sum(x["net"] for x in rows if x["channel"] == "Partner"), 2)),
    ("E8",  "Direct net revenue",    round(sum(x["net"] for x in rows if x["channel"] == "Direct"), 2)),
    ("E9",  "Shipping billed",       T["ship"]),
    ("E10", "Largest single line",   max(x["net"] for x in rows)),
    ("E11", "Rows held back",        REPORT["counts"]["rowsRejected"]),
    ("E12", "Rows read from source", REPORT["counts"]["rowsIn"]),
    ("B17", "Lines under 40 percent margin",
     sum(1 for p in REPORT["byProduct"] if p["marginPct"] < 0.4)),
    ("B20", "Check, Summary less By month",   0.0),
    ("B21", "Check, Summary less By product", 0.0),
    ("B22", "Check, no row lost",             0.0),
]
for ref, label, want in EXPECT:
    got = M.cell("Summary", ref)
    check(near(got, want, 0.02), "Summary!%-4s %s" % (ref, label),
          "formula %s -> %s, expected %s" % (wsS[ref].value, round(float(got), 4), round(float(want), 4)))

best_month = max(REPORT["byMonth"], key=lambda m: m["net"])["label"]
best_line = max(REPORT["byProduct"], key=lambda p: p["net"])["product"]
check(M.cell("Summary", "B15") == best_month, "Summary!B15 strongest month", str(M.cell("Summary", "B15")))
check(M.cell("Summary", "B16") == best_line, "Summary!B16 largest product line", str(M.cell("Summary", "B16")))

print("\nBY MONTH FORMULAS")
for i, m in enumerate(REPORT["byMonth"]):
    r = 5 + i
    for col, key in (("B", "orders"), ("C", "units"), ("D", "gross"), ("F", "net"),
                     ("G", "cost"), ("H", "margin")):
        got = M.cell("By month", "%s%d" % (col, r))
        check(near(got, m[key]), "By month!%s%d %s %s" % (col, r, m["label"], key),
              "%s vs %s" % (round(float(got), 2), m[key]))
check(near(M.cell("By month", "F8"), T["net"]), "By month total ties to net revenue",
      str(M.cell("By month", "F8")))

print("\nBY PRODUCT FORMULAS")
for i, p in enumerate(REPORT["byProduct"]):
    r = 5 + i
    for col, key in (("B", "orders"), ("C", "units"), ("F", "net"), ("G", "cost"),
                     ("H", "margin"), ("I", "marginPct"), ("J", "share")):
        got = M.cell("By product", "%s%d" % (col, r))
        check(near(got, p[key], 0.02 if col not in "IJ" else 1e-6),
              "By product!%s%d %s %s" % (col, r, p["product"], key),
              "%s vs %s" % (round(float(got), 6), p[key]))
check(near(M.cell("By product", "F11"), T["net"]), "By product total ties to net revenue",
      str(M.cell("By product", "F11")))

print("\nROW LEVEL FORMULAS, SPOT CHECKED")
for r in (2, 3, LAST // 2, LAST - 1, LAST):
    x = rows[r - 2]
    check(near(M.cell("Clean data", "L%d" % r), x["gross"]), "Clean data!L%d gross" % r)
    check(near(M.cell("Clean data", "M%d" % r), x["net"]), "Clean data!M%d net revenue" % r)
    check(near(M.cell("Clean data", "O%d" % r), x["cost"]), "Clean data!O%d cost" % r)
    check(M.cell("Clean data", "C%d" % r) == x["month"], "Clean data!C%d month key" % r,
          str(M.cell("Clean data", "C%d" % r)))

# ------------------------------------------------- 4. rejected rows sheet ----
print("\nROWS HELD BACK")
rej = REPORT["rejects"]["rows"]
listed = [wsX["A%d" % r].value for r in range(5, 5 + len(rej))]
check(listed == [x["line"] for x in rej], "every held back row is listed, in source order",
      "%d rows" % len(listed))
check(wsX["A%d" % (5 + len(rej))].value is None, "and nothing after the last one")
codes = {wsX["B%d" % (5 + i)].value for i in range(len(rej))}
check(codes == {x["code"] for x in rej}, "every reason code appears", ", ".join(sorted(codes)))
check(REPORT["counts"]["rowsOut"] + REPORT["counts"]["rowsRejected"] == REPORT["counts"]["rowsIn"],
      "rows in equals rows out plus rows held back",
      "%d = %d + %d" % (REPORT["counts"]["rowsIn"], REPORT["counts"]["rowsOut"],
                        REPORT["counts"]["rowsRejected"]))

# -------------------------------- 5. would Excel open it without complaint ---
print("\nFILE INTEGRITY")
z = zipfile.ZipFile(XLSX)
check(z.testzip() is None, "zip container is intact")
bad = []
for name in z.namelist():
    if name.endswith((".xml", ".rels")):
        try:
            ElementTree.fromstring(z.read(name))
        except ElementTree.ParseError as e:
            bad.append("%s: %s" % (name, e))
check(not bad, "every XML part parses", "%d parts" % len([n for n in z.namelist() if n.endswith((".xml", ".rels"))]))
need = ["xl/workbook.xml", "xl/styles.xml", "xl/worksheets/sheet1.xml",
        "xl/charts/chart1.xml", "xl/charts/chart2.xml"]
check(all(n in z.namelist() for n in need), "the parts Excel looks for are present",
      ", ".join(n for n in need if n in z.namelist()))
wb2 = load_workbook(XLSX)          # a second clean load, nothing left half written
check(wb2.sheetnames == WANT, "reloads cleanly a second time")

# ----------------------------------------------------------------- result ---
print("\n%d checks, %d failures" % (len(CHECKS) + len(FAILS), len(FAILS)))
if FAILS:
    for f in FAILS:
        print("  FAILED: %s" % f)
    sys.exit(1)
print("Workbook verified.")