"""Map a ShopLine ERP open-orders export to the twin's open-orders.csv.

Usage: uv run --project ../twin-flow python map_orders.py [export.xlsx] [shop.yaml]
Defaults: the newest mrmw-open-orders-*.xlsx in this folder, and mesquite-ridge-shop.yaml.
Writes open-orders.csv and rows-not-used.csv next to the script. Inputs are never changed.
"""
import csv
import re
import sys
from datetime import date, datetime
from pathlib import Path

import openpyxl
import yaml

HERE = Path(__file__).resolve().parent
ORDER_COLS = ["work_order_id", "customer", "part", "qty", "start_date", "due_date", "priority"]
SKIP_COLS = ["export_row", "so_no", "line", "part", "reason"]
MONTHS = {m: i for i, m in enumerate(
    ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"], 1)}


def _valid(y, m, d):
    try:
        return date(y, m, d)
    except ValueError:
        return None


def parse_date(v):
    """Return a date, or None when the value is blank or cannot be read with certainty."""
    if v is None:
        return None
    if isinstance(v, datetime):
        return v.date()
    if isinstance(v, date):
        return v
    s = str(v).strip()
    if not s:
        return None
    if m := re.fullmatch(r"(\d{4})-(\d{1,2})-(\d{1,2})(?:[ T].*)?", s):  # ISO
        return _valid(int(m[1]), int(m[2]), int(m[3]))
    if m := re.fullmatch(r"(\d{1,2})/(\d{1,2})/(\d{4})", s):  # US, as in the report's run stamp
        return _valid(int(m[3]), int(m[1]), int(m[2]))
    if m := re.fullmatch(r"([A-Za-z]{3})[A-Za-z]*\.? (\d{1,2}),? (\d{4})", s):  # Nov 20, 2026
        mo = MONTHS.get(m[1].lower())
        return _valid(int(m[3]), mo, int(m[2])) if mo else None
    if m := re.fullmatch(r"(\d{1,2})-(\d{1,2})-(\d{2})", s):  # 2-digit year: try every reading
        a, b, c = (int(x) for x in m.groups())
        reads = {r for r in (_valid(2000 + c, a, b),   # mm-dd-yy
                             _valid(2000 + c, b, a),   # dd-mm-yy
                             _valid(2000 + a, b, c))   # yy-mm-dd
                 if r}
        return reads.pop() if len(reads) == 1 else None  # ambiguous or impossible: never guess
    return None


def parse_qty(v):
    """Whole number, allowing trailing words like 'EA'. None if not a clean whole number."""
    if v is None:
        return None
    if isinstance(v, bool):
        return None
    if isinstance(v, (int, float)):
        return int(v) if float(v).is_integer() else None
    m = re.fullmatch(r"\s*(-?\d+(?:\.0+)?)(?:\s+[A-Za-z][\w.]*)*\s*", str(v))
    return int(float(m[1])) if m else None


def as_int(v):
    try:
        f = float(str(v).strip())
    except (TypeError, ValueError):
        return None
    return int(f) if f.is_integer() else None


def main():
    args = sys.argv[1:]
    export = Path(args[0]) if args else max(HERE.glob("mrmw-open-orders-*.xlsx"), key=lambda p: p.name)
    shop = Path(args[1]) if len(args) > 1 else HERE / "mesquite-ridge-shop.yaml"
    routed = {str(r["part"]).replace(" ", "").upper()
              for r in yaml.safe_load(shop.read_text())["routing"]}

    ws = openpyxl.load_workbook(export, data_only=False).active
    header_row = next(r[0].row for r in ws.iter_rows() if str(r[0].value).strip() == "SO No")
    cols = {str(c.value).strip(): i for i, c in enumerate(ws[header_row]) if c.value is not None}

    orders, skipped, seen, lines = [], [], set(), 0
    for row in ws.iter_rows(min_row=header_row + 1):
        g = lambda name: row[cols[name]].value  # noqa: E731
        so, ln = as_int(g("SO No")), as_int(g("Ln"))
        if so is None or ln is None:  # blank, total and footer lines
            continue
        lines += 1
        part = re.sub(r"\s+", "", str(g("Item No") or "")).upper()
        due = parse_date(g("Req Ship"))
        start = parse_date(g("Release"))
        ordered, shipped = parse_qty(g("Order Qty")), parse_qty(g("Qty Shipped"))
        open_qty = ordered - shipped if ordered is not None and shipped is not None else None

        key = (so, ln)
        reason = None
        if str(g("Status") or "").strip().lower() == "closed":
            reason = "Status is Closed"
        elif key in seen:
            reason = "Duplicate of an earlier row with the same SO No and Ln"
        elif part not in routed:
            reason = "Part not in the routing of the shop file"
        elif due is None:
            reason = "Due date is blank" if g("Req Ship") in (None, "") \
                else f"Due date is not a readable date: {g('Req Ship')!r}"
        elif open_qty is None:
            reason = "Open quantity cannot be worked out (quantity is not a whole number)"
        elif open_qty <= 0:
            reason = "Open quantity is 0 or less"
        elif start is None:
            reason = f"Start date is blank or not a readable date: {g('Release')!r}"
        seen.add(key)

        if reason:
            skipped.append([row[0].row, so, ln, part, reason])
            continue
        orders.append([f"SO-{so}-{ln}", g("Customer"), part, open_qty, start.isoformat(),
                       due.isoformat(), 1 if str(g("Hot") or "").strip().upper() == "Y" else 0])

    for name, cols_, rows in (("open-orders.csv", ORDER_COLS, orders),
                              ("rows-not-used.csv", SKIP_COLS, skipped)):
        with open(HERE / name, "w", newline="") as f:
            w = csv.writer(f)
            w.writerow(cols_)
            w.writerows(rows)

    print(f"Order lines in export: {lines}")
    print(f"Written to open-orders.csv: {len(orders)}")
    print(f"Written to rows-not-used.csv: {len(skipped)}")
    for r in skipped:
        print(f"  row {r[0]}: SO-{r[1]}-{r[2]} {r[3]} - {r[4]}")


if __name__ == "__main__":
    main()
