#!/usr/bin/env python3
"""Weekly open orders dashboard from an Acumatica export.

Usage:  python3 open_orders_dashboard.py MR-Open-Sales-Lines-YYYY-MM-DD.xlsx

Reads column-map.json from the same folder as the export, finds every column
by its header name (never by position), and writes dashboard-YYYY-MM-DD.html
and dashboard-YYYY-MM-DD.json next to the export.

Needs only the Python standard library (no openpyxl, no internet).
"""
import datetime as dt
import html
import json
import re
import sys
import zipfile
import xml.etree.ElementTree as ET
from decimal import Decimal, InvalidOperation
from pathlib import Path

NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
      "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships"}
REL = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id"
HOLD_STATUS = "On Hold"          # status that counts as "on hold"
DUE_WINDOW_DAYS = 6              # due this week = as-of date .. as-of + 6
REQUIRED_KEYS = ["order_type", "order_nbr", "line_nbr", "customer_name",
                 "inventory_id", "open_qty", "unit_price", "ship_date", "status"]
BUILTIN_DATE_FMTS = set(range(14, 23)) | set(range(45, 48))


def die(msg):
    sys.exit(f"ERROR: {msg}")


# ---------------------------------------------------------------- xlsx reader
def col_index(ref):
    n = 0
    for ch in re.match(r"[A-Z]+", ref).group():
        n = n * 26 + ord(ch) - 64
    return n


def is_date_format(code):
    code = re.sub(r'"[^"]*"|\[[^\]]*\]|\\.', "", code.lower())
    return bool(re.search(r"[dmyh]", code)) and not re.search(r"^[#0.,?_* ]+$", code)


def read_sheet(path, sheet_name):
    """Return {excel_row_number: {col_index: value}} for the named sheet.
    Values: str, Decimal, or datetime.date (for date-formatted cells)."""
    with zipfile.ZipFile(path) as z:
        names = set(z.namelist())
        wb = ET.fromstring(z.read("xl/workbook.xml"))
        rels = ET.fromstring(z.read("xl/_rels/workbook.xml.rels"))
        targets = {r.get("Id"): r.get("Target") for r in rels}
        sheets = {s.get("name"): targets[s.get(REL)] for s in wb.find("m:sheets", NS)}
        if sheet_name not in sheets:
            die(f"sheet '{sheet_name}' not found in the export "
                f"(sheets present: {', '.join(sheets)})")
        t = sheets[sheet_name].lstrip("/")
        if not t.startswith("xl/"):
            t = "xl/" + t

        shared = []
        if "xl/sharedStrings.xml" in names:
            for si in ET.fromstring(z.read("xl/sharedStrings.xml")).findall("m:si", NS):
                shared.append("".join(x.text or "" for x in si.iter("{%s}t" % NS["m"])))

        date_style = set()
        if "xl/styles.xml" in names:
            st = ET.fromstring(z.read("xl/styles.xml"))
            custom = {int(n.get("numFmtId")): n.get("formatCode")
                      for n in st.findall("m:numFmts/m:numFmt", NS)}
            for i, xf in enumerate(st.findall("m:cellXfs/m:xf", NS)):
                fid = int(xf.get("numFmtId", 0))
                if fid in BUILTIN_DATE_FMTS or (fid in custom and is_date_format(custom[fid])):
                    date_style.add(i)

        rows = {}
        for row in ET.fromstring(z.read(t)).iter("{%s}row" % NS["m"]):
            rn = int(row.get("r"))
            cells = {}
            for c in row.findall("m:c", NS):
                typ, ref = c.get("t"), c.get("r")
                v = c.find("m:v", NS)
                if typ == "inlineStr":
                    val = "".join(x.text or "" for x in c.iter("{%s}t" % NS["m"]))
                elif v is None or v.text is None:
                    continue
                elif typ == "s":
                    val = shared[int(v.text)]
                elif typ in ("str", "e"):
                    val = v.text
                elif typ == "b":
                    val = v.text
                else:
                    try:
                        val = Decimal(v.text)
                    except InvalidOperation:
                        val = v.text
                    if isinstance(val, Decimal) and int(c.get("s", 0)) in date_style:
                        val = dt.date(1899, 12, 30) + dt.timedelta(days=int(val))
                cells[col_index(ref)] = val
            rows[rn] = cells
        return rows


# ------------------------------------------------------------------- parsing
def clean(v):
    return v.strip() if isinstance(v, str) else v


def blank(v):
    return v is None or (isinstance(v, str) and v == "")


def parse_date(v):
    """Date part only. Accepts a real date, or text like '11/20/2026 12:00 AM'.
    Returns None if blank or not a date."""
    if isinstance(v, dt.datetime):
        return v.date()
    if isinstance(v, dt.date):
        return v
    if not isinstance(v, str) or not v:
        return None
    m = re.match(r"^(\d{1,2})/(\d{1,2})/(\d{4})(?:\s.*)?$", v) or None
    try:
        if m:
            return dt.date(int(m[3]), int(m[1]), int(m[2]))
        m = re.match(r"^(\d{4})-(\d{2})-(\d{2})(?:[ T].*)?$", v)
        if m:
            return dt.date(int(m[1]), int(m[2]), int(m[3]))
    except ValueError:
        pass
    return None


def parse_num(v):
    if isinstance(v, Decimal):
        return v
    if isinstance(v, str) and v:
        try:
            d = Decimal(v.replace(",", ""))
            return d if d.is_finite() else None
        except InvalidOperation:
            return None
    return None


def money(d):
    return f"${d:,.2f}"


def num_text(d):
    d = d.normalize()
    return f"{d:f}"


def line_text(v):
    """Order/line numbers as text without a trailing .0 on whole numbers."""
    if isinstance(v, Decimal):
        return num_text(v)
    return "" if v is None else str(v)


# ---------------------------------------------------------------------- main
def main():
    if len(sys.argv) != 2:
        die("usage: python3 open_orders_dashboard.py <export.xlsx>")
    export = Path(sys.argv[1])
    if not export.is_file():
        die(f"file not found: {export}")
    m = re.fullmatch(r"MR-Open-Sales-Lines-(\d{4})-(\d{2})-(\d{2})\.xlsx", export.name)
    if not m:
        die(f"file name '{export.name}' does not match MR-Open-Sales-Lines-YYYY-MM-DD.xlsx")
    try:
        asof = dt.date(int(m[1]), int(m[2]), int(m[3]))
    except ValueError:
        die(f"the date in the file name '{export.name}' is not a real date")
    stamp = asof.isoformat()
    week_end = asof + dt.timedelta(days=DUE_WINDOW_DAYS)

    map_path = export.resolve().parent / "column-map.json"
    if not map_path.is_file():
        die(f"column-map.json not found next to the export ({map_path})")
    cmap = json.loads(map_path.read_text(encoding="utf-8"))
    fields = cmap.get("fields", {})
    for k in REQUIRED_KEYS:
        if k not in fields:
            die(f"column-map.json has no entry for '{k}' under \"fields\"")
    types_counted = [s.strip() for s in cmap.get("order_types_counted", [])]
    open_statuses = [s.strip() for s in cmap.get("open_statuses", [])]

    rows = read_sheet(export, cmap.get("sheet", "Data"))
    if not rows:
        die("the sheet is empty")
    header_rn = min(rows)
    header = {}
    for ci, v in rows[header_rn].items():
        name = clean(v)
        if isinstance(name, str) and name:
            header.setdefault(name, []).append(ci)
    col = {}
    for key in REQUIRED_KEYS:
        name = fields[key].strip()
        if name not in header:
            die(f"column '{name}' (map entry '{key}') is missing from the export")
        if len(header[name]) > 1:
            die(f"column '{name}' (map entry '{key}') appears more than once in the export")
        col[key] = header[name][0]

    kept, aside, seen = [], [], set()
    total_rows = 0
    for rn in sorted(r for r in rows if r > header_rn):
        raw = {k: clean(rows[rn].get(ci)) for k, ci in col.items()}
        if all(blank(v) for v in rows[rn].values() if not isinstance(v, str) or v.strip()):
            continue  # completely empty row: not a data row
        total_rows += 1
        otype = "" if raw["order_type"] is None else str(raw["order_type"])
        order = line_text(raw["order_nbr"])
        line = line_text(raw["line_nbr"])

        def skip(reason):
            aside.append({"excel_row": rn, "order": order, "line": line, "reason": reason})

        if otype not in types_counted:
            skip(f"order type '{otype}' is not counted"); continue
        key = (order, line)
        if key in seen:
            skip("duplicate order and line (first one kept)"); continue
        seen.add(key)
        status = "" if raw["status"] is None else str(raw["status"])
        if status not in open_statuses:
            skip(f"status '{status}' is not an open status"); continue
        qty = parse_num(raw["open_qty"])
        if qty is None:
            skip("open quantity is blank or not a number"); continue
        if qty <= 0:
            skip("open quantity is 0 or less"); continue
        ship = parse_date(raw["ship_date"])
        if ship is None:
            skip("ship date is blank or not a date"); continue
        price = parse_num(raw["unit_price"])
        if price is None:
            skip("unit price is blank or not a number"); continue
        kept.append({
            "order": order, "line": line, "customer": str(raw["customer_name"] or ""),
            "item": str(raw["inventory_id"] or ""), "status": status,
            "open_qty": qty, "unit_price": price, "open_value": qty * price,
            "ship_date": ship,
        })

    def total(rs):
        return {"lines": len(rs), "value": sum((r["open_value"] for r in rs), Decimal(0))}

    late = sorted((r for r in kept if r["ship_date"] < asof),
                  key=lambda r: (r["ship_date"], r["order"], r["line"]))
    for r in late:
        r["days_late"] = (asof - r["ship_date"]).days
    week = [r for r in kept if asof <= r["ship_date"] <= week_end]
    hold = [r for r in kept if r["status"] == HOLD_STATUS]
    summary = {"open": total(kept), "late": total(late),
               "due_this_week": total(week), "on_hold": total(hold)}

    by_cust = {}
    for r in kept:
        by_cust.setdefault(r["customer"], []).append(r)
    cust_rows = []
    for name in sorted(by_cust, key=lambda s: (s.casefold(), s)):
        rs = by_cust[name]
        o, l = total(rs), total([r for r in rs if r["ship_date"] < asof])
        cust_rows.append({"customer": name, "open_lines": o["lines"], "open_value": o["value"],
                          "late_lines": l["lines"], "late_value": l["value"]})

    # ---------------------------------------------------------------- JSON
    def jmoney(d):
        return float(d.quantize(Decimal("0.01")))

    out = {
        "export_file": export.name, "as_of": stamp, "rows_in_export": total_rows,
        "due_this_week_window": [asof.isoformat(), week_end.isoformat()],
        "summary": {k: {"lines": v["lines"], "value": jmoney(v["value"])} for k, v in summary.items()},
        "by_customer": [{**c, "open_value": jmoney(c["open_value"]),
                         "late_value": jmoney(c["late_value"])} for c in cust_rows],
        "late_lines": [{"order": r["order"], "line": r["line"], "customer": r["customer"],
                        "item": r["item"], "open_qty": float(r["open_qty"]),
                        "open_value": jmoney(r["open_value"]),
                        "ship_date": r["ship_date"].isoformat(), "days_late": r["days_late"]}
                       for r in late],
        "set_aside": aside,
    }
    json_path = export.resolve().parent / f"dashboard-{stamp}.json"
    json_path.write_text(json.dumps(out, indent=2), encoding="utf-8")

    # ---------------------------------------------------------------- HTML
    e = html.escape

    def tile(label, d):
        return (f'<div class="tile"><div class="lab">{e(label)}</div>'
                f'<div class="big">{d["lines"]:,}</div><div class="sub">lines</div>'
                f'<div class="val">{money(d["value"])}</div></div>')

    def table(head, body, num_cols=(), empty="None."):
        if not body:
            return f"<p class='none'>{e(empty)}</p>"
        th = "".join(f'<th class="{"n" if i in num_cols else ""}">{e(h)}</th>'
                     for i, h in enumerate(head))
        trs = "".join("<tr>" + "".join(
            f'<td class="{"n" if i in num_cols else ""}">{e(str(c))}</td>'
            for i, c in enumerate(r)) + "</tr>" for r in body)
        return f"<table><thead><tr>{th}</tr></thead><tbody>{trs}</tbody></table>"

    cust_html = table(
        ["Customer", "Open lines", "Open value", "Late lines", "Late value"],
        [[c["customer"], c["open_lines"], money(c["open_value"]), c["late_lines"],
          money(c["late_value"])] for c in cust_rows], (1, 2, 3, 4), "No open lines.")
    late_html = table(
        ["Order", "Line", "Customer", "Item", "Open qty", "Open value", "Ship date", "Days late"],
        [[r["order"], r["line"], r["customer"], r["item"], num_text(r["open_qty"]),
          money(r["open_value"]), r["ship_date"].isoformat(), r["days_late"]] for r in late],
        (4, 5, 7), "No late lines.")
    aside_html = table(
        ["Excel row", "Order", "Line", "Reason"],
        [[a["excel_row"], a["order"], a["line"], a["reason"]] for a in aside], (0,),
        "No rows set aside.")

    page = f"""<!DOCTYPE html>
<html lang="en"><head><meta charset="utf-8">
<title>Open Orders Dashboard {stamp}</title>
<style>
@page {{ size: letter portrait; margin: 0.4in; }}
body {{ font: 13px/1.35 -apple-system, "Segoe UI", Arial, sans-serif; color: #1a1a1a; max-width: 960px; margin: 16px auto; padding: 0 12px; }}
h1 {{ font-size: 20px; margin: 0 0 10px; }}
h2 {{ font-size: 14px; margin: 16px 0 4px; border-bottom: 1px solid #999; padding-bottom: 2px; }}
.tiles {{ display: flex; gap: 8px; }}
.tile {{ flex: 1; border: 1px solid #999; border-radius: 4px; padding: 6px 10px; }}
.lab {{ font-weight: 600; font-size: 12px; }}
.big {{ font-size: 24px; font-weight: 700; display: inline; }}
.sub {{ display: inline; color: #555; }}
.val {{ font-size: 16px; }}
table {{ border-collapse: collapse; width: 100%; }}
th, td {{ padding: 2px 6px; border-bottom: 1px solid #ddd; text-align: left; }}
th {{ background: #eef2f6; border-bottom: 1px solid #888; }}
.n {{ text-align: right; font-variant-numeric: tabular-nums; }}
thead {{ display: table-header-group; }} tr {{ page-break-inside: avoid; }}
.none {{ color: #555; margin: 4px 0; }}
.foot {{ margin-top: 14px; padding-top: 6px; border-top: 1px solid #999; color: #333; }}
@media print {{ body {{ margin: 0; max-width: none; font-size: 10.5px; }} .big {{ font-size: 20px; }} }}
</style></head><body>
<h1>Open Orders Dashboard</h1>
<div class="tiles">
{tile("Open", summary["open"])}
{tile("Late (ship date before " + stamp + ")", summary["late"])}
{tile("Due this week (" + asof.isoformat() + " to " + week_end.isoformat() + ")", summary["due_this_week"])}
{tile("On hold", summary["on_hold"])}
</div>
<h2>By customer</h2>
{cust_html}
<h2>Late lines</h2>
{late_html}
<h2>Rows set aside</h2>
{aside_html}
<p class="foot">Export file: {e(export.name)} | As-of date: {stamp} | Rows in export: {total_rows}</p>
</body></html>
"""
    html_path = export.resolve().parent / f"dashboard-{stamp}.html"
    html_path.write_text(page, encoding="utf-8")

    print(f"Wrote {html_path}\nWrote {json_path}")
    for k, v in summary.items():
        print(f"  {k}: {v['lines']} lines, {money(v['value'])}")
    print(f"  rows in export: {total_rows}; set aside: {len(aside)}")


if __name__ == "__main__":
    main()
