#!/usr/bin/env python3
"""Mesquite Ridge Metal Works - four-week work center load check.

Usage: python3 load_check.py [folder] [--what-if [file.json]]
Reads, from the folder (default: current folder), the newest file matching
  mrmw-co-lines-YYYY-MM-DD.xlsx, mrmw-current-operations.xlsx, mrmw-work-center-hours.xlsx
and writes load-check-YYYY-MM-DD.html there. Input files are only read.
With --what-if (default file what-if.json, looked up in the folder unless a path is given)
the page shows today's grid beside a what-if grid and is written to
load-check-YYYY-MM-DD-what-if-<name>.html. What-if shape:
  {"name": "...", "move_orders": [{"order": "48210", "new_due_date": "2026-12-02"}],
   "extra_hours": [{"wc": "MILL", "week_of": "2026-11-23", "hours": 10}]}
Columns are found by header name on row 2; row 1 is a note.
"""
import re, sys, glob, os, json, argparse
from datetime import datetime, date, timedelta
from decimal import Decimal, InvalidOperation
from html import escape
import openpyxl

HEADER_ROW = 2
WEEKS = 4
STATUS_OK = "Ordered"


def read_table(path, required):
    ws = openpyxl.load_workbook(path, data_only=True).worksheets[0]
    rows = list(ws.iter_rows(values_only=True))
    if len(rows) < HEADER_ROW:
        sys.exit(f"{path}: no header row on row {HEADER_ROW}")
    names = [str(h).strip() if h is not None else "" for h in rows[HEADER_ROW - 1]]
    missing = [c for c in required if c not in names]
    if missing:
        sys.exit(f"{path}: column(s) not found on row {HEADER_ROW}: {missing}. Found: {names}")
    idx = {n: i for i, n in enumerate(names) if n}
    out = []
    for n, r in enumerate(rows[HEADER_ROW:], start=HEADER_ROW + 1):
        if all(v is None or (isinstance(v, str) and not v.strip()) for v in r):
            continue  # fully blank row
        out.append((n, {c: (r[idx[c]] if idx[c] < len(r) else None) for c in required}))
    return out


def to_num(v):
    """Number from int/float/text like '40.000'; None if not readable."""
    if v is None or isinstance(v, bool):
        return None
    if isinstance(v, (int, float)):
        return Decimal(str(v))
    s = str(v).strip().replace(",", "")
    if not s:
        return None
    try:
        return Decimal(s)
    except InvalidOperation:
        return None


def to_date(v):
    """Real date, or text m/d/yyyy. Anything else -> None (never guess)."""
    if isinstance(v, datetime):
        return v.date()
    if isinstance(v, date):
        return v
    if isinstance(v, str):
        m = re.fullmatch(r"\s*(\d{1,2})/(\d{1,2})/(\d{4})\s*", v)
        if m:
            try:
                return date(int(m[3]), int(m[1]), int(m[2]))
            except ValueError:
                return None
    return None


def clean_int(v):
    n = to_num(v)
    return str(int(n)) if n is not None and n == n.to_integral_value() else str(v).strip()


def round_half_up(x):
    return int(Decimal(str(x)).quantize(Decimal("1"), rounding="ROUND_HALF_UP"))


def load_what_if(path, start, week_starts, capacity, counted, co_orders):
    try:
        with open(path, encoding="utf-8") as f:
            wi = json.load(f)
    except (OSError, ValueError) as e:
        sys.exit(f"Cannot read what-if file {path}: {e}")
    if not isinstance(wi, dict) or not isinstance(wi.get("name"), str) or not wi["name"].strip():
        sys.exit("what-if: needs a text \"name\"")
    unknown = set(wi) - {"name", "move_orders", "extra_hours"}
    if unknown:
        sys.exit(f"what-if: unknown key(s) {sorted(unknown)}")
    moves, extras = {}, {}
    for m in wi.get("move_orders") or []:
        order = re.sub(r"\s+", "", str(m.get("order", "")))
        try:
            nd = datetime.strptime(str(m.get("new_due_date", "")), "%Y-%m-%d").date()
        except ValueError:
            sys.exit(f"what-if: new_due_date must be YYYY-MM-DD: {m!r}")
        if order not in co_orders:
            sys.exit(f"what-if: order {order!r} is not in the export")
        if order in moves:
            sys.exit(f"what-if: order {order} is moved twice")
        moves[order] = nd
    for e in wi.get("extra_hours") or []:
        wc = str(e.get("wc", "")).strip()
        if wc not in capacity:
            sys.exit(f"what-if: work center {wc!r} is not in the hours file")
        try:
            wk = datetime.strptime(str(e.get("week_of", "")), "%Y-%m-%d").date()
        except ValueError:
            sys.exit(f"what-if: week_of must be YYYY-MM-DD: {e!r}")
        if wk not in week_starts:
            sys.exit(f"what-if: week_of {wk} is not the first day of one of the four weeks "
                     f"({', '.join(str(w) for w in week_starts)})")
        h = to_num(e.get("hours"))
        if h is None:
            sys.exit(f"what-if: hours not a number: {e!r}")
        extras[(wc, week_starts.index(wk))] = extras.get((wc, week_starts.index(wk)), Decimal(0)) + h
    notes = []
    for order, nd in moves.items():
        lines = [L for L in counted if L["order"] == order]
        if lines:
            for L in lines:
                notes.append(f"moved {L['order']}/{L['line']}/{L['rel']} due {L['due']} to {nd}")
        else:
            notes.append(f"order {order} has no counted lines, so moving it to {nd} changes nothing")
    for (wc, i), h in extras.items():
        notes.append(f"added {h:f} h to {wc} week {i+1} (week of {week_starts[i]})")
    if not notes:
        notes.append("changed nothing (both lists empty)")
    return wi["name"].strip(), moves, extras, notes


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("folder", nargs="?", default=".")
    ap.add_argument("--what-if", nargs="?", const="what-if.json", dest="what_if")
    args = ap.parse_args()
    folder = args.folder
    co_files = sorted(glob.glob(os.path.join(folder, "mrmw-co-lines-*.xlsx")))
    if not co_files:
        sys.exit("No mrmw-co-lines-YYYY-MM-DD.xlsx in " + folder)
    co_path = co_files[-1]
    m = re.search(r"(\d{4}-\d{2}-\d{2})\.xlsx$", co_path)
    if not m:
        sys.exit("Cannot read export date from file name " + co_path)
    stamp = m[1]
    start = datetime.strptime(stamp, "%Y-%m-%d").date()
    if start.weekday() != 0:
        print(f"NOTE: export date {stamp} is not a Monday; weeks run 7 days from it.")
    week_starts = [start + timedelta(days=7 * i) for i in range(WEEKS)]
    horizon_end = start + timedelta(days=7 * WEEKS)

    wc_rows = read_table(os.path.join(folder, "mrmw-work-center-hours.xlsx"), ["WC", "Hours a week"])
    capacity, wc_order = {}, []
    for n, r in wc_rows:
        wc = str(r["WC"]).strip()
        hrs = to_num(r["Hours a week"])
        if hrs is None or hrs <= 0:
            sys.exit(f"Work center hours row {n}: Hours a week not a positive number: {r['Hours a week']!r}")
        capacity[wc] = hrs
        wc_order.append(wc)

    ops_rows = read_table(os.path.join(folder, "mrmw-current-operations.xlsx"),
                          ["Item", "Operation", "WC", "Setup Hrs", "Machine Hours Per Piece", "Efficiency"])
    routing = {}
    for n, r in ops_rows:
        s, mh, ef = to_num(r["Setup Hrs"]), to_num(r["Machine Hours Per Piece"]), to_num(r["Efficiency"])
        if s is None or mh is None or ef is None or ef <= 0:
            sys.exit(f"Operations row {n}: Setup Hrs / Machine Hours Per Piece / Efficiency not usable "
                     f"({r['Setup Hrs']!r}, {r['Machine Hours Per Piece']!r}, {r['Efficiency']!r})")
        routing.setdefault(str(r["Item"]).strip(), []).append(
            dict(op=clean_int(r["Operation"]), wc=str(r["WC"]).strip(), setup=s, mh=mh, eff=ef))

    co_rows = read_table(co_path, ["Order", "Line", "Release", "Item", "Status", "Ordered", "Shipped", "Due Date"])
    total_rows = len(co_rows)
    seen, counted, not_counted = {}, [], []
    for n, r in co_rows:
        order = re.sub(r"\s+", "", str(r["Order"] if r["Order"] is not None else ""))
        key = (order, clean_int(r["Line"]), clean_int(r["Release"]))
        label = "/".join(key)
        if key in seen:
            not_counted.append((n, label, f"Duplicate of Excel row {seen[key]}"))
            continue
        seen[key] = n
        status = str(r["Status"] or "").strip()
        if status != STATUS_OK:
            not_counted.append((n, label, f"Status is {status or 'blank'}, not {STATUS_OK}"))
            continue
        o, s = to_num(r["Ordered"]), to_num(r["Shipped"])
        if o is None or s is None:
            not_counted.append((n, label, f"Quantity not readable (Ordered={r['Ordered']!r}, Shipped={r['Shipped']!r})"))
            continue
        qty = o - s
        if qty <= 0:
            not_counted.append((n, label, f"Open qty is {qty}, not above 0"))
            continue
        due = to_date(r["Due Date"])
        if due is None:
            why = "No due date" if r["Due Date"] in (None, "") else f"Due date not readable ({r['Due Date']!r})"
            not_counted.append((n, label, why))
            continue
        counted.append(dict(row=n, order=order, line=key[1], rel=key[2], item=str(r["Item"] or "").strip(),
                            qty=qty, due=due))

    def compute(due_override, extra):
        """Hours per WC/week, with optional due-date overrides {order: date} and extra capacity."""
        cap = {wc: [capacity[wc] + extra.get((wc, i), Decimal(0)) for i in range(WEEKS)] for wc in wc_order}
        hours = {wc: [Decimal(0)] * WEEKS for wc in wc_order}
        detail, no_routing, skipped_ops, beyond = {}, [], [], []
        for ln in counted:
            ops = routing.get(ln["item"])
            if not ops:
                no_routing.append(ln)
                continue
            due = due_override.get(ln["order"], ln["due"])
            if due >= horizon_end:
                beyond.append((ln, due))
                week = None
            else:
                week = max(0, (due - start).days // 7)
            for op in ops:
                h = op["setup"] + ln["qty"] * op["mh"] * 100 / op["eff"]
                if op["wc"] not in capacity:
                    skipped_ops.append((ln, op, h))
                    continue
                if week is None:
                    continue
                hours[op["wc"]][week] += h
                detail.setdefault((op["wc"], week), []).append(dict(ln=ln, op=op["op"], h=h))
        return dict(hours=hours, cap=cap, detail=detail, no_routing=no_routing,
                    skipped_ops=skipped_ops, beyond=beyond)

    def pct(R, wc, i):
        return round_half_up(R["hours"][wc][i] / R["cap"][wc][i] * 100)

    base = compute({}, {})
    wi = None
    if args.what_if:
        wi_path = args.what_if if os.path.isabs(args.what_if) or os.path.exists(args.what_if) \
            else os.path.join(folder, args.what_if)
        co_orders = {re.sub(r"\s+", "", str(r["Order"] or "")) for _, r in co_rows}
        wi_name, moves, extras, wi_notes = load_what_if(wi_path, start, week_starts, capacity, counted, co_orders)
        wi = compute(moves, extras)
        # a what-if never changes which lines are skipped or lack routing; only grid numbers move

    # ---- HTML
    def wk_label(i):
        a = week_starts[i]; b = a + timedelta(days=6)
        return f"Week {i+1}<br><span class='sub'>{a:%b %d} - {b:%b %d}</span>"

    def fh(x):
        return f"{x:,.2f}"

    css = """body{font-family:Arial,Helvetica,sans-serif;margin:24px;color:#222}
table{border-collapse:collapse;margin:8px 0 20px}th,td{border:1px solid #bbb;padding:6px 10px;text-align:right}
th{background:#eee}td.l,th.l{text-align:left}.sub{font-weight:normal;font-size:12px;color:#555}
.red{background:#f4b6b6}.amber{background:#ffe08a}.big{font-size:18px;font-weight:bold}
.ok{color:#064}.bad{color:#a00;font-weight:bold}h2{margin-top:28px}
.changed{outline:3px solid #1747c4;outline-offset:-3px}.tag{display:block;font-size:12px;color:#1747c4;font-weight:bold}
.banner{background:#e8eefc;border:1px solid #1747c4;padding:8px 12px;margin:12px 0}
.side{display:flex;gap:32px;flex-wrap:wrap;align-items:flex-start}"""
    title = f"Load check {stamp}" + (f" - what-if: {wi_name}" if wi else "")
    o = [f"<!DOCTYPE html><html><head><meta charset='utf-8'><title>{escape(title)}</title><style>{css}</style></head><body>",
         f"<h1>Mesquite Ridge Metal Works - load check</h1><p>Export date {stamp}. Four weeks, Monday to Sunday. "
         "Past-due lines are in week 1. Sample data.</p>"]
    if wi:
        o.append(f"<div class='banner'><b>What-if \"{escape(wi_name)}\":</b> " + "; ".join(escape(n) for n in wi_notes) +
                 ". For this check only; the export and other files are not changed. "
                 "Changed cells have a blue border.</div>")

    def grid(R, ref=None):
        reds = []
        g = ["<table><tr><th class='l'>Work center</th>"] + [f"<th>{wk_label(i)}</th>" for i in range(WEEKS)] + ["</tr>"]
        for wc in wc_order:
            g.append(f"<tr><th class='l'>{escape(wc)}</th>")
            for i in range(WEEKS):
                p = pct(R, wc, i)
                cls = "red" if p > 100 else "amber" if p >= 90 else ""
                if cls == "red":
                    reds.append((wc, i, p))
                tag = ""
                if ref is not None and (R["hours"][wc][i] != ref["hours"][wc][i] or R["cap"][wc][i] != ref["cap"][wc][i]):
                    cls += " changed"
                    tag = f"<span class='tag'>CHANGED, was {pct(ref, wc, i)}%</span>"
                g.append(f"<td class='{cls}'><span class='big'>{p}%</span><br>{fh(R['hours'][wc][i])} h of "
                         f"{fh(R['cap'][wc][i])} h{tag}</td>")
            g.append("</tr>")
        g.append("</table>")
        return "".join(g), reds

    def red_detail(R, reds):
        out = []
        if not reds:
            out.append("<p>No red cells.</p>")
        for wc, i, p in reds:
            items = sorted(R["detail"][(wc, i)], key=lambda d: -d["h"])
            out.append(f"<h3>{escape(wc)}, week {i+1} ({week_starts[i]:%b %d}): {fh(R['hours'][wc][i])} h of "
                       f"{fh(R['cap'][wc][i])} h, {p}%</h3>"
                       "<table><tr><th class='l'>Order</th><th>Line</th><th>Release</th><th class='l'>Item</th>"
                       "<th>Operation</th><th>Hours</th></tr>")
            for d in items:
                L = d["ln"]
                out.append(f"<tr><td class='l'>{escape(L['order'])}</td><td>{L['line']}</td><td>{L['rel']}</td>"
                           f"<td class='l'>{escape(L['item'])}</td><td>{escape(d['op'])}</td><td>{fh(d['h'])}</td></tr>")
            out.append("</table>")
        return "".join(out)

    gb, reds_b = grid(base)
    if wi:
        gw, reds_w = grid(wi, ref=base)
        o.append("<h2>Load by work center and week</h2><div class='side'>"
                 f"<div><h3>Today</h3>{gb}</div><div><h3>What-if: {escape(wi_name)}</h3>{gw}</div></div>")
    else:
        o.append("<h2>Load by work center and week</h2>" + gb)
    o.append("<p class='sub'>Red: over 100%. Amber: 90 to 100%. Color uses the rounded whole-number load %.</p>")

    o.append("<h2>Order lines behind each red cell" + (" - today" if wi else "") + "</h2>" + red_detail(base, reds_b))
    if wi:
        o.append(f"<h2>Order lines behind each red cell - what-if: {escape(wi_name)}</h2>" + red_detail(wi, reds_w))

    skipped_ops, no_routing = base["skipped_ops"], base["no_routing"]
    o.append("<h2>Operations skipped: work center not in the hours file</h2>")
    if skipped_ops:
        o.append("<table><tr><th class='l'>Order</th><th>Line</th><th>Release</th><th class='l'>Item</th>"
                 "<th>Operation</th><th class='l'>WC</th><th>Hours not counted</th></tr>")
        for L, op, h in skipped_ops:
            o.append(f"<tr><td class='l'>{escape(L['order'])}</td><td>{L['line']}</td><td>{L['rel']}</td>"
                     f"<td class='l'>{escape(L['item'])}</td><td>{escape(op['op'])}</td>"
                     f"<td class='l'>{escape(op['wc'])}</td><td>{fh(h)}</td></tr>")
        o.append("</table>")
    else:
        o.append("<p>None.</p>")

    o.append("<h2>No routing, hours unknown</h2>")
    if no_routing:
        o.append("<table><tr><th>Excel row</th><th class='l'>Order</th><th>Line</th><th>Release</th>"
                 "<th class='l'>Item</th><th>Open qty</th><th>Due</th></tr>")
        for L in no_routing:
            o.append(f"<tr><td>{L['row']}</td><td class='l'>{escape(L['order'])}</td><td>{L['line']}</td>"
                     f"<td>{L['rel']}</td><td class='l'>{escape(L['item'])}</td><td>{L['qty']:f}</td><td>{L['due']}</td></tr>")
        o.append("</table>")
    else:
        o.append("<p>None.</p>")

    for label, R in (("", base),) + ((f" (what-if: {wi_name})", wi),) if wi else (("", base),):
        if R["beyond"]:
            o.append(f"<h2>Counted but due after the four weeks, not in the grid{escape(label)}</h2><ul>")
            for L, due in R["beyond"]:
                o.append(f"<li>Row {L['row']}: {escape(L['order'])}/{L['line']}/{L['rel']} {escape(L['item'])} due {due}</li>")
            o.append("</ul>")

    o.append("<h2>Order lines not counted</h2>")
    if not_counted:
        o.append("<table><tr><th>Excel row</th><th class='l'>Order/Line/Release</th><th class='l'>Reason</th></tr>")
        for n, label, why in not_counted:
            o.append(f"<tr><td>{n}</td><td class='l'>{escape(label)}</td><td class='l'>{escape(why)}</td></tr>")
        o.append("</table>")
    else:
        o.append("<p>None.</p>")

    ok = total_rows == len(counted) + len(not_counted)
    o.append(f"<h2>Check</h2><p class='{'ok' if ok else 'bad'}'>Rows in export {total_rows} = lines counted "
             f"{len(counted)} + lines not counted {len(not_counted)} "
             f"({'OK' if ok else 'DOES NOT BALANCE'})</p></body></html>")

    if wi:
        slug = re.sub(r"[^A-Za-z0-9]+", "-", wi_name).strip("-").lower() or "what-if"
        out = os.path.join(folder, f"load-check-{stamp}-what-if-{slug}.html")
    else:
        out = os.path.join(folder, f"load-check-{stamp}.html")
    with open(out, "w", encoding="utf-8") as f:
        f.write("\n".join(o))
    print("Wrote", out)
    print(f"Rows {total_rows} = counted {len(counted)} + not counted {len(not_counted)}")
    if wi:
        print("What-if:", "; ".join(wi_notes))
    for tag, R, reds in (("today", base, reds_b),) + ((("what-if", wi, reds_w),) if wi else ()):
        print(f"[{tag}]")
        for wc in wc_order:
            print(" ", wc.ljust(6), " ".join(f"{pct(R,wc,i):>4}%({R['hours'][wc][i]:.1f}/{R['cap'][wc][i]:.0f}h)" for i in range(WEEKS)))
        for wc, i, p in reds:
            print(f"  RED {wc} week {i+1}: {p}%")
    return 0 if ok else 1


if __name__ == "__main__":
    sys.exit(main())
