#!/usr/bin/env python3
"""Build a weekly open-orders dashboard from an Infor SyteLine
"Customer Order Lines" Excel export.

Usage:
    python3 infor_dashboard.py mrmw-co-lines-YYYY-MM-DD.xlsx
        [--map infor-column-map.json] [--out-dir .]

The date in the export's file name is treated as "today".
Columns are found by the names in the map file, never by position.
"""
import argparse
import json
import re
import sys
from collections import OrderedDict
from datetime import date, datetime, timedelta
from decimal import Decimal, InvalidOperation
from html import escape
from pathlib import Path

import openpyxl

FIELDS = ["order", "line", "release", "customer_name", "item", "status",
          "qty_ordered", "qty_shipped", "due_date", "unit_price"]
NUM_RE = re.compile(r"^-?\d+(\.\d+)?$")
FILE_RE = re.compile(r"^mrmw-co-lines-(\d{4})-(\d{2})-(\d{2})\.xlsx$")


class Stop(Exception):
    """A plain-words problem that ends the run without making a page."""


# ---------- reading ----------------------------------------------------

def load_map(path):
    try:
        m = json.loads(Path(path).read_text())
    except FileNotFoundError:
        raise Stop(f"Column map not found: {path}")
    except json.JSONDecodeError as e:
        raise Stop(f"Column map is not valid JSON: {e}")
    for f in FIELDS:
        if not m.get(f):
            raise Stop(f"The column map has no column name for '{f}'. "
                       f"Fix infor-column-map.json (no page made).")
    if not isinstance(m.get("header_row"), int):
        raise Stop("The column map has no header_row number (no page made).")
    for k in ("open_statuses", "not_firm_statuses", "closed_statuses"):
        m[k] = [str(s).strip() for s in (m.get(k) or [])]
    return m


def today_from_name(path):
    mt = FILE_RE.match(Path(path).name)
    if not mt:
        raise Stop(f"Cannot read the date from the file name "
                   f"'{Path(path).name}'. Expected mrmw-co-lines-YYYY-MM-DD.xlsx.")
    try:
        return date(*map(int, mt.groups()))
    except ValueError:
        raise Stop(f"The date in the file name '{Path(path).name}' is not a real date.")


def find_columns(ws, m):
    hr = m["header_row"]
    names = {}
    for cell in ws[hr]:
        if cell.value is None:
            continue
        names.setdefault(str(cell.value).strip(), []).append(cell.column - 1)
    cols = {}
    for f in FIELDS:
        want = m[f]
        hits = names.get(want)
        if not hits:
            raise Stop(f"Column '{want}' (needed for {f}) was not found in "
                       f"row {hr} of the export. No page made.")
        if len(hits) > 1:
            raise Stop(f"Column '{want}' (needed for {f}) appears more than "
                       f"once in row {hr}. No page made.")
        cols[f] = hits[0]
    return cols


# ---------- cell parsing (never guess) ---------------------------------

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


def parse_number(v):
    """Return Decimal or None when blank/unreadable."""
    if blank(v) or isinstance(v, bool):
        return None
    if isinstance(v, (int, float)):
        return Decimal(repr(v))
    if isinstance(v, Decimal):
        return v
    s = str(v).strip()
    if NUM_RE.match(s):
        try:
            return Decimal(s)
        except InvalidOperation:
            return None
    return None


def parse_due(v):
    """Return (date or None, problem text or None)."""
    if blank(v):
        return None, "due date is blank"
    if isinstance(v, datetime):
        return v.date(), None
    if isinstance(v, date):
        return v, None
    if isinstance(v, str):
        try:
            return datetime.strptime(v.strip(), "%m/%d/%Y").date(), None
        except ValueError:
            pass
    return None, f"due date '{v}' cannot be read"


def key_part(v):
    """Normalise Line/Release so 1, 1.0 and '1' match."""
    if blank(v):
        return ""
    n = parse_number(v)
    if n is not None:
        return str(int(n)) if n == n.to_integral_value() else str(n)
    return str(v).strip()


# ---------- classification ---------------------------------------------

def classify(ws, cols, m, today):
    open_lines, aside, planned = [], [], []
    seen = {}
    total_rows = 0
    empty_rows = 0
    for row in ws.iter_rows(min_row=m["header_row"] + 1):
        vals = [c.value for c in row]
        if all(blank(v) for v in vals):
            empty_rows += 1
            continue
        total_rows += 1
        xl = row[0].row

        def get(f):
            i = cols[f]
            return vals[i] if i < len(vals) else None

        order = "" if blank(get("order")) else re.sub(r"\s+", "", str(get("order")))
        line, rel = key_part(get("line")), key_part(get("release"))
        status = "" if blank(get("status")) else str(get("status")).strip()
        rec = dict(xl=xl, order=order, line=line, release=rel,
                   customer=("" if blank(get("customer_name")) else str(get("customer_name")).strip()),
                   item=("" if blank(get("item")) else str(get("item")).strip()),
                   status=status)

        # Rule 1: same Order + Line + Release twice -> keep first
        key = (order, line, rel)
        if key in seen:
            aside.append((xl, rec, f"Duplicate of row {seen[key]} "
                                   f"(same Order + Line + Release)"))
            continue
        seen[key] = xl

        qo, qs = parse_number(get("qty_ordered")), parse_number(get("qty_shipped"))
        price = parse_number(get("unit_price"))
        due, due_problem = parse_due(get("due_date"))
        rec["due"], rec["price"] = due, price

        if status in m["closed_statuses"]:
            aside.append((xl, rec, f"Closed status '{status}'"))
            continue
        is_open = status in m["open_statuses"]
        is_planned = status in m["not_firm_statuses"]
        if not (is_open or is_planned):
            aside.append((xl, rec, f"Status '{status}' is not in the column map"
                          if status else "Status is blank"))
            continue

        problems = []
        if qo is None:
            problems.append("Ordered qty is blank or not a number")
        if qs is None:
            problems.append("Shipped qty is blank or not a number")
        if qo is not None and qs is not None:
            rec["qty"] = qo - qs
            if rec["qty"] <= 0:
                problems.append(f"open qty is {rec['qty'].normalize():f} (not above 0)")
        if price is None:
            problems.append("Unit Price is blank or not a number")
        if is_open and due_problem:
            problems.append(due_problem)

        if problems:
            aside.append((xl, rec, "; ".join(problems)))
            continue
        rec["value"] = rec["qty"] * price
        if is_planned:
            planned.append(rec)
            aside.append((xl, rec, f"Planned, not firm (status '{status}'): "
                                   f"counted separately, not in totals"))
        else:
            open_lines.append(rec)
    aside.sort(key=lambda t: t[0])
    return open_lines, aside, planned, total_rows, empty_rows


# ---------- formatting -------------------------------------------------

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


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


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


def bucket(due, today):
    if due < today:
        return "Past due"
    if due <= today + timedelta(days=6):
        return "This week"
    if due <= today + timedelta(days=13):
        return "Next week"
    return "Later"


BUCKETS = ["Past due", "This week", "Next week", "Later"]


def bucket_range(name, today):
    return {"Past due": f"before {today}",
            "This week": f"{today} to {today + timedelta(days=6)}",
            "Next week": f"{today + timedelta(days=7)} to {today + timedelta(days=13)}",
            "Later": f"{today + timedelta(days=14)} or later"}[name]


def table(headers, rows, num_cols=(), foot=None):
    h = "".join(f"<th{' class=n' if i in num_cols else ''}>{escape(x)}</th>"
                for i, x in enumerate(headers))
    body = "".join("<tr>" + "".join(
        f"<td{' class=n' if i in num_cols else ''}>{escape(str(c))}</td>"
        for i, c in enumerate(r)) + "</tr>" for r in rows)
    if foot:
        body += "<tr class=tot>" + "".join(
            f"<td{' class=n' if i in num_cols else ''}>{escape(str(c))}</td>"
            for i, c in enumerate(foot)) + "</tr>"
    return f"<table><thead><tr>{h}</tr></thead><tbody>{body}</tbody></table>"


CSS = """
body{font:15px/1.45 -apple-system,Segoe UI,Helvetica,Arial,sans-serif;margin:0;
background:#f4f5f7;color:#1c2330}
main{max-width:1100px;margin:0 auto;padding:24px}
h1{font-size:24px;margin:0 0 4px}h2{font-size:17px;margin:32px 0 8px}
.sub{color:#5b6472;margin:0 0 20px}
.tiles{display:grid;grid-template-columns:repeat(4,1fr);gap:12px}
.tile{background:#fff;border:1px solid #dde1e7;border-radius:8px;padding:14px 16px}
.tile .l{font-size:13px;color:#5b6472}.tile .v{font-size:28px;font-weight:650;
font-variant-numeric:tabular-nums}.tile.late .v{color:#b3261e}
table{width:100%;border-collapse:collapse;background:#fff;border:1px solid #dde1e7;
border-radius:8px;font-size:14px}
th,td{padding:7px 10px;border-bottom:1px solid #eceef2;text-align:left}
th{background:#eef0f4;font-weight:600}.n{text-align:right;font-variant-numeric:tabular-nums}
tr.tot td{font-weight:650;background:#f7f8fa}
.note{color:#5b6472;font-size:13px;margin:4px 0 8px}
.check{margin-top:32px;padding:12px 16px;background:#fff;border:1px solid #dde1e7;
border-left:4px solid #2e7d32;border-radius:6px;font-variant-numeric:tabular-nums}
.check.bad{border-left-color:#b3261e}
@media(max-width:700px){.tiles{grid-template-columns:repeat(2,1fr)}}
"""


# ---------- page -------------------------------------------------------

def build_page(src_name, today, open_lines, aside, planned, total_rows, empty_rows):
    n_open = len(open_lines)
    val_open = sum((r["value"] for r in open_lines), Decimal(0))
    past = sorted((r for r in open_lines if r["due"] < today),
                  key=lambda r: (r["due"], r["order"], r["line"], r["release"], r["xl"]))
    val_past = sum((r["value"] for r in past), Decimal(0))

    # by due bucket
    agg = OrderedDict((b, [0, Decimal(0)]) for b in BUCKETS)
    for r in open_lines:
        a = agg[bucket(r["due"], today)]
        a[0] += 1
        a[1] += r["value"]
    bucket_rows = [(b, bucket_range(b, today), agg[b][0], money(agg[b][1]))
                   for b in BUCKETS]

    # by customer
    cust = {}
    for r in open_lines:
        c = cust.setdefault(r["customer"] or "(no name)", [0, Decimal(0)])
        c[0] += 1
        c[1] += r["value"]
    cust_rows = [(k, v[0], money(v[1])) for k, v in
                 sorted(cust.items(), key=lambda kv: (-kv[1][1], kv[0]))]

    past_rows = [(r["xl"], r["order"], r["line"], r["release"], r["customer"],
                  r["item"], fdate(r["due"]), (today - r["due"]).days,
                  qty(r["qty"]), money(r["price"]), money(r["value"]))
                 for r in past]

    pl_val = sum((r["value"] for r in planned), Decimal(0))
    aside_rows = [(xl, r["order"], r["line"], r["release"], r["status"], reason)
                  for xl, r, reason in aside]

    ok = total_rows == n_open + len(aside)
    check = (f"Check: rows in export {total_rows} = open lines {n_open} + "
             f"rows set aside {len(aside)} "
             f"({n_open + len(aside)}) - {'OK' if ok else 'DOES NOT MATCH'}.")
    if empty_rows:
        check += f" ({empty_rows} completely empty row(s) below the header were ignored.)"

    def tile(label, value, cls=""):
        return (f'<div class="tile {cls}"><div class="l">{escape(label)}</div>'
                f'<div class="v">{escape(str(value))}</div></div>')

    parts = [
        f"<h1>Open Orders Dashboard</h1>",
        f'<p class="sub">As of {today} (date taken from file name {escape(src_name)}). '
        f"Open = status in the open list, open qty above 0, and a readable due date.</p>",
        '<div class="tiles">',
        tile("Open lines", n_open), tile("Open value", money(val_open)),
        tile("Past due lines", len(past), "late"),
        tile("Past due value", money(val_past), "late"), "</div>",
        "<h2>Open orders by when they are due</h2>",
        table(["When due", "Dates", "Open lines", "Open value"], bucket_rows, (2, 3),
              ("Total", "", n_open, money(val_open))),
        "<h2>Open orders by customer</h2>",
        table(["Customer", "Open lines", "Open value"], cust_rows, (1, 2),
              ("Total", n_open, money(val_open))) if cust_rows
        else '<p class="note">No open lines.</p>',
        "<h2>Past due lines (oldest due date first)</h2>",
        table(["Excel row", "Order", "Line", "Release", "Customer", "Item", "Due date",
               "Days late", "Open qty", "Unit price", "Open value"],
              past_rows, (7, 8, 9, 10)) if past_rows
        else '<p class="note">No past due lines.</p>',
        "<h2>Planned, not firm</h2>",
        '<p class="note">Not included in any total above.</p>',
        table(["Planned lines", "Planned value"], [(len(planned), money(pl_val))], (0, 1)),
        "<h2>Rows set aside</h2>",
        '<p class="note">Every row not counted as an open line, by Excel row number.</p>',
        table(["Excel row", "Order", "Line", "Release", "Status", "Reason"], aside_rows)
        if aside_rows else '<p class="note">None.</p>',
        f'<div class="check{"" if ok else " bad"}">{escape(check)}</div>',
    ]
    return ("<!DOCTYPE html><html lang=en><head><meta charset=utf-8>"
            f"<meta name=viewport content='width=device-width,initial-scale=1'>"
            f"<title>Open Orders Dashboard {today}</title><style>{CSS}</style></head>"
            f"<body><main>{''.join(parts)}</main></body></html>"), ok


# ---------- main -------------------------------------------------------

def main(argv=None):
    ap = argparse.ArgumentParser(description=__doc__,
                                 formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("export", help="mrmw-co-lines-YYYY-MM-DD.xlsx")
    ap.add_argument("--map", default="infor-column-map.json")
    ap.add_argument("--out-dir", default=".")
    a = ap.parse_args(argv)
    try:
        m = load_map(a.map)
        today = today_from_name(a.export)
        try:
            wb = openpyxl.load_workbook(a.export, data_only=True)
        except FileNotFoundError:
            raise Stop(f"Export file not found: {a.export}")
        except Exception as e:
            raise Stop(f"Cannot open the export as an Excel file: {e}")
        ws = wb.worksheets[0]
        cols = find_columns(ws, m)
        open_lines, aside, planned, total, empty = classify(ws, cols, m, today)
        html, ok = build_page(Path(a.export).name, today, open_lines, aside,
                              planned, total, empty)
    except Stop as e:
        print(f"STOPPED: {e}", file=sys.stderr)
        return 2
    out = Path(a.out_dir) / f"open-orders-dashboard-{today}.html"
    out.write_text(html, encoding="utf-8")
    print(f"Wrote {out}")
    print(f"Rows {total} = open {len(open_lines)} + set aside {len(aside)} "
          f"-> {'OK' if ok else 'MISMATCH'}")
    return 0 if ok else 1


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