#!/usr/bin/env python3
"""Weekly work center load check for the Acumatica "MR Open Operations" export.

Usage:
    python3 load_check.py EXPORT.xlsx HOURS.xlsx [--map column-map.json] [--out-dir DIR]

Needs openpyxl (pip install openpyxl).
EXPORT's file name must contain the as-of date as YYYY-MM-DD.
Columns are found by the names in column-map.json (row 1 of the "Data" sheet),
never by position. This is a check only: it moves and suggests nothing.
"""
import argparse
import datetime as dt
import html
import json
import os
import re
import sys

COUNTED = {"planned": "Planned", "released": "Released", "in process": "In Process"}
FIELDS = ["production_nbr", "operation_id", "work_center", "inventory_id",
          "qty_complete", "qty_remaining", "run_hours_remaining", "setup_hours",
          "start_date", "end_date", "status"]


def stop(msg):
    print("STOPPED: " + msg, file=sys.stderr)
    sys.exit(1)


def text(v):
    return "" if v is None else str(v).strip()


def num(v):
    """Return float, or None if blank / not a number. Never fills in a value."""
    if v is None or isinstance(v, bool):
        return None
    if isinstance(v, (int, float)):
        return float(v)
    s = str(v).strip().replace(",", "")
    if not s:
        return None
    try:
        return float(s)
    except ValueError:
        return None


def as_date(v):
    if isinstance(v, dt.datetime):
        return v.date()
    if isinstance(v, dt.date):
        return v
    s = text(v)
    for fmt in ("%Y-%m-%d", "%m/%d/%Y", "%Y-%m-%d %H:%M:%S"):
        try:
            return dt.datetime.strptime(s, fmt).date()
        except ValueError:
            pass
    return None


def monday(d):
    return d - dt.timedelta(days=d.weekday())


def fmt_date(d):
    return d.isoformat() if d else ""


def fmt_h(x):
    return f"{x:,.2f}".rstrip("0").rstrip(".") if x else "0"


def load_workbook(path):
    try:
        import openpyxl
    except ImportError:
        stop("The openpyxl package is not installed. Run: pip install openpyxl")
    try:
        return openpyxl.load_workbook(path, data_only=True)
    except Exception as e:
        stop(f"Cannot open {path}: {e}")


def read_hours(path):
    wb = load_workbook(path)
    ws = wb["Hours"] if "Hours" in wb.sheetnames else wb[wb.sheetnames[0]]
    rows = list(ws.iter_rows(values_only=True))
    hdr = wk = None
    for i, r in enumerate(rows):
        cells = [text(c).lower() for c in r]
        if hdr is None and "work center" in cells and "hours per day" in cells:
            hdr = (i, cells.index("work center"), cells.index("hours per day"))
        if wk is None and "week of" in cells and "working days" in cells:
            wk = (i, cells.index("week of"), cells.index("working days"))
    if hdr is None:
        stop(f"{path}: cannot find a 'Work Center' / 'Hours per Day' header row.")
    if wk is None:
        stop(f"{path}: cannot find a 'Week of' / 'Working days' header row.")
    centers = {}
    order = []
    for r in rows[hdr[0] + 1:]:
        name = text(r[hdr[1]]).upper() if len(r) > hdr[1] else ""
        if not name:
            break
        h = num(r[hdr[2]]) if len(r) > hdr[2] else None
        if h is None:
            stop(f"{path}: work center {name} has no valid hours per day.")
        if name in centers:
            stop(f"{path}: work center {name} is listed twice.")
        centers[name] = h
        order.append(name)
    weeks = []
    for r in rows[wk[0] + 1:]:
        d = as_date(r[wk[1]]) if len(r) > wk[1] else None
        if d is None:
            if all(c is None for c in r):
                continue
            break
        days = num(r[wk[2]]) if len(r) > wk[2] else None
        if days is None:
            stop(f"{path}: week of {d} has no valid working days.")
        if d.weekday() != 0:
            stop(f"{path}: 'Week of' {d} is not a Monday.")
        weeks.append((d, days))
    if len(weeks) != 3:
        print(f"Note: hours sheet has {len(weeks)} weeks (expected 3); showing all of them.")
    return centers, order, weeks


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("export")
    ap.add_argument("hours")
    ap.add_argument("--map", default=os.path.join(os.path.dirname(os.path.abspath(__file__)), "column-map.json"))
    ap.add_argument("--out-dir", default=None)
    a = ap.parse_args()

    export_name = os.path.basename(a.export)
    m = re.search(r"(\d{4})-(\d{2})-(\d{2})", export_name)
    if not m:
        stop(f"No as-of date (YYYY-MM-DD) in the export file name '{export_name}'.")
    try:
        asof = dt.date(int(m[1]), int(m[2]), int(m[3]))
    except ValueError:
        stop(f"The date in the file name '{export_name}' is not a real date.")
    asof_week = monday(asof)

    try:
        with open(a.map, encoding="utf-8") as f:
            cmap = json.load(f)
    except (OSError, ValueError) as e:
        stop(f"Cannot read column map {a.map}: {e}")
    for k in FIELDS:
        if k not in cmap:
            stop(f"column-map.json has no entry for '{k}'.")
        if cmap[k] is None:
            stop(f"column-map.json maps '{k}' to null: that column is missing from the export.")

    centers, center_order, weeks = read_hours(a.hours)

    wb = load_workbook(a.export)
    if "Data" not in wb.sheetnames:
        stop(f"{export_name} has no sheet named 'Data'.")
    rows = list(wb["Data"].iter_rows(values_only=True))
    if not rows:
        stop(f"The Data sheet in {export_name} is empty.")
    header = [text(c) for c in rows[0]]
    idx = {}
    for k in FIELDS:
        name = text(cmap[k])
        n = header.count(name)
        if n == 0:
            stop(f"Column '{name}' (mapped for {k}) is not in row 1 of the Data sheet in {export_name}.")
        if n > 1:
            stop(f"Column '{name}' (mapped for {k}) appears {n} times in row 1 of the Data sheet.")
        idx[k] = header.index(name)

    def get(r, k):
        i = idx[k]
        return r[i] if i < len(r) else None

    week_starts = [w for w, _ in weeks]
    load = {c: {w: 0.0 for w in week_starts} for c in center_order}
    ops_in = {c: {w: [] for w in week_starts} for c in center_order}
    late, aside, outside = [], [], []
    seen = set()
    n_rows = 0

    for xl_row, r in enumerate(rows[1:], start=2):
        if all(c is None or text(c) == "" for c in r):
            continue
        n_rows += 1
        prod, op = text(get(r, "production_nbr")), text(get(r, "operation_id"))
        wc = text(get(r, "work_center")).upper()
        status = text(get(r, "status"))
        base = {"excel_row": xl_row, "production_nbr": prod, "operation_id": op,
                "work_center": wc, "status": status}

        def set_aside(reason):
            aside.append({**base, "reason": reason})

        if not prod or not op:
            set_aside("production number or operation ID is blank")
            continue
        key = (prod, op)
        if key in seen:
            set_aside("same production number and operation ID as an earlier row")
            continue
        seen.add(key)
        if status.lower() not in COUNTED:
            set_aside(f"status '{status}' is not counted")
            continue
        qrem = num(get(r, "qty_remaining"))
        if qrem is None:
            set_aside("remaining quantity is blank or not a number")
            continue
        if qrem <= 0:
            set_aside("remaining quantity is 0 or less")
            continue
        if wc not in centers:
            set_aside(f"work center '{wc}' is not in the hours sheet")
            continue
        run = num(get(r, "run_hours_remaining"))
        if run is None:
            set_aside("run hours remaining is blank")
            continue
        qdone = num(get(r, "qty_complete"))
        if qdone is None:
            set_aside("quantity complete is blank or not a number")
            continue
        setup = num(get(r, "setup_hours"))
        if qdone == 0 and setup is None:
            set_aside("quantity complete is 0 but setup hours is blank")
            continue
        start = as_date(get(r, "start_date"))
        if start is None:
            set_aside("start date is blank or not a date")
            continue
        end = as_date(get(r, "end_date"))
        hours = run + (setup if qdone == 0 else 0.0)
        op_rec = {**base, "inventory_id": text(get(r, "inventory_id")), "hours": round(hours, 6),
                  "start_date": fmt_date(start), "end_date": fmt_date(end)}
        wk = monday(start)
        if wk < asof_week:
            wk = asof_week
            late.append(op_rec)
        if wk not in load[wc]:
            outside.append(op_rec)
            continue
        load[wc][wk] += hours
        ops_in[wc][wk].append(op_rec)

    # Build grid
    grid, over_cells = [], []
    for c in center_order:
        cells = []
        for w, days in weeks:
            avail = centers[c] * days
            hrs = round(load[c][w], 6)
            pct = (hrs / avail * 100) if avail > 0 else None
            over = hrs > avail + 1e-9
            cell = {"week": fmt_date(w), "hours_loaded": hrs, "hours_available": avail,
                    "percent": None if pct is None else round(pct, 1), "over": over}
            cells.append(cell)
            if over:
                over_cells.append({"work_center": c, "week": fmt_date(w), "hours_loaded": hrs,
                                   "hours_available": avail, "percent": cell["percent"],
                                   "operations": ops_in[c][w]})
        grid.append({"work_center": c, "hours_per_day": centers[c], "weeks": cells})

    result = {
        "export_file": export_name, "as_of": fmt_date(asof), "as_of_week": fmt_date(asof_week),
        "export_row_count": n_rows,
        "weeks": [{"week_of": fmt_date(w), "working_days": d} for w, d in weeks],
        "grid": grid, "over_cells": over_cells, "started_late": late,
        "set_aside": aside, "start_outside_shown_weeks": outside,
    }

    out_dir = a.out_dir or os.path.dirname(os.path.abspath(a.export))
    stamp = fmt_date(asof)
    json_path = os.path.join(out_dir, f"load-check-{stamp}.json")
    html_path = os.path.join(out_dir, f"load-check-{stamp}.html")
    with open(json_path, "w", encoding="utf-8") as f:
        json.dump(result, f, indent=2)
    with open(html_path, "w", encoding="utf-8") as f:
        f.write(render(result, weeks))

    print(f"Wrote {html_path}\nWrote {json_path}")
    if over_cells:
        print("Over 100%:")
        for o in over_cells:
            print(f"  {o['work_center']}  week of {o['week']}: {fmt_h(o['hours_loaded'])} of "
                  f"{fmt_h(o['hours_available'])} h ({o['percent']}%)")
    else:
        print("No work center week is over 100%.")
    if outside:
        print(f"Note: {len(outside)} counted operation(s) start after the weeks shown and are not in the grid (see JSON).")


E = html.escape


def render(res, weeks):
    css = ("body{font-family:system-ui,sans-serif;margin:24px;color:#222}"
           "table{border-collapse:collapse;margin:8px 0 20px}"
           "th,td{border:1px solid #bbb;padding:5px 10px;text-align:left;font-size:14px}"
           "th{background:#eee}td.n{text-align:right}"
           ".over{background:#fde8e8;color:#b00020;font-weight:700}"
           "h2{margin-top:28px;font-size:18px}.muted{color:#666}")
    o = [f"<!doctype html><html><head><meta charset='utf-8'><title>Load check {E(res['as_of'])}</title>"
         f"<style>{css}</style></head><body><h1>Load check</h1>"]
    o.append("<h2>1. Load by work center and week</h2><table><tr><th>Work center</th>")
    for w in res["weeks"]:
        o.append(f"<th>Week of {E(w['week_of'])}<br><span class='muted'>{fmt_h(w['working_days'])} working days</span></th>")
    o.append("</tr>")
    for row in res["grid"]:
        o.append(f"<tr><th>{E(row['work_center'])}</th>")
        for c in row["weeks"]:
            pct = "n/a" if c["percent"] is None else f"{c['percent']:.1f}%"
            txt = f"{fmt_h(c['hours_loaded'])} of {fmt_h(c['hours_available'])} h &middot; {pct}"
            if c["over"]:
                o.append(f"<td class='over'>{txt} &middot; OVER</td>")
            else:
                o.append(f"<td>{txt}</td>")
        o.append("</tr>")
    o.append("</table>")

    o.append("<h2>2. Operations in each OVER cell</h2>")
    if not res["over_cells"]:
        o.append("<p>No cell is over 100%.</p>")
    for oc in res["over_cells"]:
        o.append(f"<h3>{E(oc['work_center'])}, week of {E(oc['week'])}: "
                 f"{fmt_h(oc['hours_loaded'])} of {fmt_h(oc['hours_available'])} h</h3>")
        o.append("<table><tr><th>Production nbr.</th><th>Operation</th><th>Item</th>"
                 "<th>Hours</th><th>Start date</th><th>End date</th></tr>")
        for p in oc["operations"]:
            o.append(f"<tr><td>{E(p['production_nbr'])}</td><td>{E(p['operation_id'])}</td>"
                     f"<td>{E(p['inventory_id'])}</td><td class='n'>{fmt_h(p['hours'])}</td>"
                     f"<td>{E(p['start_date'])}</td><td>{E(p['end_date'])}</td></tr>")
        o.append("</table>")

    o.append("<h2>3. Started late</h2>")
    if not res["started_late"]:
        o.append("<p>None.</p>")
    else:
        o.append("<table><tr><th>Production nbr.</th><th>Operation</th><th>Work center</th><th>Item</th>"
                 "<th>Hours</th><th>Start date</th><th>End date</th></tr>")
        for p in res["started_late"]:
            o.append(f"<tr><td>{E(p['production_nbr'])}</td><td>{E(p['operation_id'])}</td>"
                     f"<td>{E(p['work_center'])}</td><td>{E(p['inventory_id'])}</td>"
                     f"<td class='n'>{fmt_h(p['hours'])}</td><td>{E(p['start_date'])}</td>"
                     f"<td>{E(p['end_date'])}</td></tr>")
        o.append("</table>")

    o.append("<h2>4. Rows set aside</h2>")
    if not res["set_aside"]:
        o.append("<p>None.</p>")
    else:
        o.append("<table><tr><th>Excel row</th><th>Production nbr.</th><th>Operation</th>"
                 "<th>Work center</th><th>Status</th><th>Reason</th></tr>")
        for p in res["set_aside"]:
            o.append(f"<tr><td class='n'>{p['excel_row']}</td><td>{E(p['production_nbr'])}</td>"
                     f"<td>{E(p['operation_id'])}</td><td>{E(p['work_center'])}</td>"
                     f"<td>{E(p['status'])}</td><td>{E(p['reason'])}</td></tr>")
        o.append("</table>")

    o.append(f"<p class='muted'>Export: {E(res['export_file'])} &middot; as of {E(res['as_of'])} "
             f"&middot; {res['export_row_count']} rows in the export.</p></body></html>")
    return "".join(o)


if __name__ == "__main__":
    main()
