"""Check supplier invoices against open PO lines. Same data in, same flags out, every time.

Run:  python3 check_invoices.py invoices.json open-po-export.xlsx
Needs: openpyxl (pip install openpyxl). Claude's code tool already has it.

In:   invoices.json    a list of invoices in the ps-invoice-v1 shape (one entry per file)
      open-po-export   the open PO lines from your ERP, as .xlsx
Out:  invoice-check.xlsx, next to the JSON file, with four sheets:
      Flags, Send to supplier, All lines, Rules

No AI runs in this script. Change the rules below, not the code further down.
"""
import json
import re
import sys
from datetime import date
from pathlib import Path

# ---------------------------------------------------------------- RULES (change these, in one place)
# Column names in your PO export. Left side: what this script calls it. Right side: your export's header.
PO_COLUMNS = {
    "po": "PO Number", "line": "Line", "supplier": "Supplier", "our_part": "Our Part",
    "supplier_part": "Supplier Part", "description": "Description", "order_qty": "Order Qty",
    "received_qty": "Received Qty", "uom": "UOM", "unit_price": "Unit Price", "freight_terms": "Freight Terms",
}
# Units. Each unit maps to a base unit and how many base units it holds. C = 100 each, M = 1000 each.
UNITS = {
    "EA": ("EA", 1), "EACH": ("EA", 1), "E": ("EA", 1), "PC": ("EA", 1), "PCS": ("EA", 1),
    "C": ("EA", 100), "M": ("EA", 1000),
    "SHEET": ("SHEET", 1), "SHT": ("SHEET", 1), "SH": ("SHEET", 1),
    "LB": ("LB", 1), "CWT": ("LB", 100), "TON": ("LB", 2000),
    "FT": ("FT", 1),
}
PRICE_TOLERANCE = 0.005   # dollars per PO unit. Anything over the PO price by more than this is flagged.
MONEY_TOLERANCE = 0.01    # dollars. For "do the lines add up to the total" checks.


class BadData(Exception):
    pass


# ---------------------------------------------------------------- read and check the inputs
def norm(s):
    return re.sub(r"[^A-Z0-9]", "", str(s).upper()) if s not in (None, "") else ""


def supplier_key(name):
    words = [w for w in re.sub(r"[^a-z0-9 ]", " ", str(name or "").lower()).split()
             if w not in {"inc", "llc", "co", "corp", "company", "ltd", "the"}]
    return " ".join(words)


def need_number(v, where, allow_null=True):
    if v is None and allow_null:
        return None
    if isinstance(v, bool) or not isinstance(v, (int, float)):
        raise BadData(f"{where} must be a number. It says {json.dumps(v)}.")
    return float(v)


def load_invoices(path):
    try:
        data = json.loads(Path(path).read_text())
    except json.JSONDecodeError as e:
        raise BadData(f"{path} is not valid JSON ({e}).")
    if isinstance(data, dict):
        data = [data]
    if not isinstance(data, list) or not data:
        raise BadData("The JSON must be a list of invoices.")
    for i, inv in enumerate(data, 1):
        tag = inv.get("source_file") or f"invoice {i}"
        if inv.get("format") != "ps-invoice-v1":
            raise BadData(f"{tag}: format must be \"ps-invoice-v1\".")
        for k in ("supplier", "invoice_number", "lines"):
            if inv.get(k) in (None, "", []):
                raise BadData(f"{tag}: {k} is missing.")
        for k in ("subtotal", "tax", "total"):
            inv[k] = need_number(inv.get(k), f"{tag}: {k}")
        for ln in inv["lines"]:
            w = f"{tag}, line {ln.get('line')}"
            for k in ("qty", "unit_price", "ext_price"):
                ln[k] = need_number(ln.get(k), f"{w}: {k}")
            if ln["ext_price"] is None and (ln["qty"] is None or ln["unit_price"] is None):
                raise BadData(f"{w}: needs ext_price, or both qty and unit_price.")
    return data


def load_po_lines(path):
    from openpyxl import load_workbook
    ws = load_workbook(path, data_only=True).active
    rows = list(ws.iter_rows(values_only=True))
    want = PO_COLUMNS["po"]
    head_i = next((i for i, r in enumerate(rows) if r and want in [str(c).strip() if c else "" for c in r]), None)
    if head_i is None:
        raise BadData(f"No header row with \"{want}\" in {path}. Fix PO_COLUMNS at the top of this script.")
    head = [str(c).strip() if c else "" for c in rows[head_i]]
    idx = {}
    for key, name in PO_COLUMNS.items():
        if name not in head:
            raise BadData(f"Column \"{name}\" is not in {path}. Fix PO_COLUMNS at the top of this script.")
        idx[key] = head.index(name)
    out = []
    for r in rows[head_i + 1:]:
        if not r or not r[idx["po"]] or r[idx["line"]] is None:  # skips blank and footer rows
            continue
        rec = {k: r[i] for k, i in idx.items()}
        for k in ("order_qty", "received_qty", "unit_price"):
            rec[k] = need_number(rec[k], f"PO {rec['po']} line {rec['line']}: {PO_COLUMNS[k]}", allow_null=False)
        out.append(rec)
    return out


# ---------------------------------------------------------------- the checks
def unit_factor(inv_uom, po_uom):
    """How many PO units are in one invoice unit. None if the units cannot be converted."""
    a, b = UNITS.get(norm(inv_uom)), UNITS.get(norm(po_uom))
    if norm(inv_uom) == norm(po_uom) and norm(inv_uom):
        return 1.0
    if not a or not b or a[0] != b[0]:
        return None
    return a[1] / b[1]


def m(v):
    return f"${v:,.2f}"


def q(v):
    return f"{v:,.4f}".rstrip("0").rstrip(".")


def p(v):
    whole, dec = f"{v:,.4f}".split(".")
    return f"${whole}.{dec.rstrip('0').ljust(2, '0')}"


def check(invoices, po_lines):
    flags, all_lines = [], []
    by_po = {}
    for pl in po_lines:
        by_po.setdefault(norm(pl["po"]), []).append(pl)

    # duplicates: same invoice number and same PO or same supplier. First file (by name) is kept.
    invoices = sorted(invoices, key=lambda v: (str(v.get("source_file") or ""), str(v["invoice_number"])))
    seen, skip = {}, set()
    for i, inv in enumerate(invoices):
        for j in seen.get(norm(inv["invoice_number"]), []):
            first = invoices[j]
            a, b = first.get("source_file") or "one file", inv.get("source_file") or "another file"
            if (norm(first.get("po_number")) and norm(first.get("po_number")) == norm(inv.get("po_number"))) \
                    or supplier_key(first["supplier"]) == supplier_key(inv["supplier"]):
                skip.add(i)
                amt = inv["total"] if inv["total"] is not None else sum(line_amount(x) for x in inv["lines"])
                flags.append(dict(
                    kind="Duplicate invoice", send="Supplier", supplier=inv["supplier"], invoice=inv["invoice_number"],
                    po=inv.get("po_number") or "", line="", part="",
                    billed=b, po_says=f"Same as {a}", amount=amt,
                    say=(f"Invoice {inv['invoice_number']} reached us twice, as two copies of the same bill. "
                         f"We will pay it once, {m(amt)}. Tell us if these are two separate bills.")))
                break
        seen.setdefault(norm(inv["invoice_number"]), []).append(i)

    for i, inv in enumerate(invoices):
        tag = dict(supplier=inv["supplier"], invoice=inv["invoice_number"], po=inv.get("po_number") or "")
        dup = i in skip
        lines_total = round(sum(line_amount(x) for x in inv["lines"]), 2)
        if not dup and inv["total"] is not None and abs(lines_total + (inv["tax"] or 0) - inv["total"]) > MONEY_TOLERANCE:
            gap = round(inv["total"] - lines_total - (inv["tax"] or 0), 2)
            flags.append(dict(kind="Check the read", send="You", line="", part="", billed=m(inv["total"]),
                              po_says=f"Lines plus tax = {m(lines_total + (inv['tax'] or 0))}", amount=abs(gap),
                              say="The lines do not add up to the invoice total. A charge may be missing from the JSON. "
                                  "Check the invoice before you trust the other flags.", **tag))
        pls = by_po.get(norm(inv.get("po_number")), [])
        if not dup and not inv.get("po_number"):
            flags.append(dict(kind="No PO number", send="Supplier", line="", part="", billed=m(inv["total"] or lines_total),
                              po_says="", amount=inv["total"] or lines_total,
                              say=f"Invoice {inv['invoice_number']} has no PO number. Please send it again with our PO number.", **tag))
        elif not dup and not pls:
            flags.append(dict(kind="PO not on the open PO list", send="You", line="", part="", billed=m(inv["total"] or lines_total),
                              po_says="", amount=inv["total"] or lines_total,
                              say=f"PO {inv['po_number']} is not on the open PO export. It may be closed, or the number is wrong.", **tag))
        for ln in inv["lines"]:
            row = dict(file=inv.get("source_file") or "", supplier=inv["supplier"], invoice=inv["invoice_number"],
                       po=inv.get("po_number") or "", line=ln.get("line"), part=ln.get("our_part") or ln.get("supplier_part") or "",
                       description=ln.get("description") or "", qty=ln["qty"], uom=ln.get("uom") or "",
                       price=ln["unit_price"], amount=line_amount(ln), result="OK", note="")
            all_lines.append(row)
            if dup:
                row["result"] = "Duplicate copy, not checked"
                continue
            if not pls:
                row["result"] = "No PO to check"
                continue
            match = find_po_line(ln, pls)
            if match is None:
                if line_amount(ln) == 0:
                    row["note"] = "Zero charge, not on PO"
                    continue
                row["result"] = "Not on the PO"
                flags.append(dict(kind="Charge not on the PO", send="Supplier", line=ln.get("line"),
                                  part=ln.get("description") or ln.get("supplier_part") or "", billed=m(line_amount(ln)),
                                  po_says="Not on the PO", amount=line_amount(ln),
                                  say=(f"Invoice {inv['invoice_number']}, line {ln.get('line')} ({ln.get('description')}, "
                                       f"{m(line_amount(ln))}) is not on our PO {inv.get('po_number')}. Please remove it, "
                                       f"or send the approval for it."), **tag))
                continue
            row["note"] = f"PO line {match['line']}"
            if ln["qty"] is None or ln["unit_price"] is None:
                row["result"] = "Check by hand"
                flags.append(dict(kind="Check by hand", send="You", line=ln.get("line"), part=match["our_part"],
                                  billed=m(line_amount(ln)), po_says="", amount=0,
                                  say="The invoice line has no quantity or no unit price, so it cannot be checked.", **tag))
                continue
            f = unit_factor(ln.get("uom"), match["uom"])
            if f is None:
                row["result"] = "Unit differs"
                flags.append(dict(kind="Unit differs", send="You", line=ln.get("line"), part=match["our_part"],
                                  billed=f"{q(ln['qty'])} {ln.get('uom')} at {p(ln['unit_price'])}",
                                  po_says=f"{q(match['order_qty'])} {match['uom']} at {p(match['unit_price'])}", amount=0,
                                  say=f"Unit {ln.get('uom')} cannot be changed to {match['uom']} with the units table. Check by hand.", **tag))
                continue
            qty_po = round(ln["qty"] * f, 6)
            price_po = ln["unit_price"] / f
            if f != 1:
                row["note"] += f"; {q(ln['qty'])} {ln['uom']} = {q(qty_po)} {match['uom']} at {p(price_po)}"
            problems = []
            over = price_po - match["unit_price"]
            if over > PRICE_TOLERANCE:
                amt = round(over * qty_po, 2)
                problems.append("Price over PO")
                flags.append(dict(kind="Price over PO", send="Supplier", line=ln.get("line"), part=match["our_part"],
                                  billed=f"{p(price_po)} per {match['uom']}", po_says=f"{p(match['unit_price'])} per {match['uom']}",
                                  amount=amt,
                                  say=(f"Invoice {inv['invoice_number']}, line {ln.get('line')} ({match['our_part']}): billed "
                                       f"{p(price_po)} per {match['uom']}. Our PO {match['po']} price is {p(match['unit_price'])}. "
                                       f"That is {p(over)} over on {q(qty_po)}, so {m(amt)}. Please send a credit memo "
                                       f"for {m(amt)} or a corrected invoice."), **tag))
            if qty_po - match["received_qty"] > 1e-9:
                extra = qty_po - match["received_qty"]
                amt = round(extra * match["unit_price"], 2)
                problems.append("Billed more than received")
                flags.append(dict(kind="Billed more than received", send="Supplier", line=ln.get("line"), part=match["our_part"],
                                  billed=f"{q(qty_po)} {match['uom']}", po_says=f"{q(match['received_qty'])} received",
                                  amount=amt,
                                  say=(f"Invoice {inv['invoice_number']}, line {ln.get('line')} ({match['our_part']}): billed "
                                       f"{q(qty_po)}, we have received {q(match['received_qty'])} on PO {match['po']}. "
                                       f"We will pay for {q(match['received_qty'])} now. Please send a corrected invoice "
                                       f"or a credit for {m(amt)}, and bill the other {q(extra)} when they arrive."), **tag))
            if problems:
                row["result"] = ", ".join(problems)
    order = {"Duplicate invoice": 0, "Price over PO": 1, "Billed more than received": 2, "Charge not on the PO": 3}
    flags.sort(key=lambda fl: (fl["send"] != "Supplier", fl["supplier"], str(fl["invoice"]), order.get(fl["kind"], 9), str(fl["line"])))
    unique = [inv for i, inv in enumerate(invoices) if i not in skip]
    return flags, all_lines, unique, len(invoices)


def line_amount(ln):
    if ln.get("ext_price") is not None:
        return float(ln["ext_price"])
    return round(ln["qty"] * ln["unit_price"], 2)


def find_po_line(ln, pls):
    """Match any part number on the invoice line to our part or the supplier part on the PO."""
    for v in (norm(ln.get("our_part")), norm(ln.get("supplier_part"))):
        if not v:
            continue
        for key in ("our_part", "supplier_part"):
            hits = [pl for pl in pls if norm(pl[key]) == v]
            if hits:
                return hits[0]
    return None


# ---------------------------------------------------------------- write the workbook
def write_book(out, flags, all_lines, unique, n_files):
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Font, PatternFill
    head_fill, head_font = PatternFill("solid", fgColor="1F3A5F"), Font(bold=True, color="FFFFFF")
    flag_fill = PatternFill("solid", fgColor="FCE8E6")
    wb = Workbook()

    def sheet(ws, title, cols, rows, widths, money_cols=(), wrap_cols=()):
        ws.title = title
        for i, c in enumerate(cols, 1):
            cell = ws.cell(row=3, column=i, value=c); cell.fill = head_fill; cell.font = head_font
        for r, row in enumerate(rows, 4):
            for i, v in enumerate(row, 1):
                cell = ws.cell(row=r, column=i, value=v)
                if i in money_cols: cell.number_format = '"$"#,##0.00'
                if i in wrap_cols: cell.alignment = Alignment(wrap_text=True, vertical="top")
                else: cell.alignment = Alignment(vertical="top")
        for i, w in enumerate(widths, 1):
            ws.column_dimensions[chr(64 + i)].width = w
        ws.freeze_panes = "A4"

    supplier_flags = [f for f in flags if f["send"] == "Supplier"]
    billed = round(sum(i["total"] if i["total"] is not None else sum(line_amount(x) for x in i["lines"]) for i in unique), 2)
    in_question = round(sum(f["amount"] for f in supplier_flags), 2)
    summary = (f"{n_files} files, {len(unique)} invoices, {m(billed)} billed. {len(supplier_flags)} flags to send, "
               f"{m(in_question)} in question. {len(flags) - len(supplier_flags)} for you to check.")

    ws = wb.active
    sheet(ws, "Flags", ["Flag", "Send to", "Supplier", "Invoice", "PO", "Line", "Part", "Billed", "PO says", "Dollars", "What to say"],
          [[f["kind"], f["send"], f["supplier"], f["invoice"], f["po"], f["line"], f["part"], f["billed"], f["po_says"], f["amount"], f["say"]]
           for f in flags], [24, 10, 26, 11, 17, 6, 20, 26, 24, 12, 70], money_cols=(10,), wrap_cols=(11,))
    ws["A1"] = summary; ws["A1"].font = Font(bold=True, size=12)
    ws["A2"] = f"Made by check_invoices.py on {date.today().isoformat()}. No AI in this step."
    ws["A2"].font = Font(italic=True, color="666666")
    last = 4 + len(flags)
    ws.cell(row=last, column=9, value="In question").font = Font(bold=True)
    c = ws.cell(row=last, column=10, value=in_question); c.font = Font(bold=True); c.number_format = '"$"#,##0.00'

    groups = {}
    for f in supplier_flags:
        groups.setdefault(f["supplier"], []).append(f)
    sheet(wb.create_sheet(), "Send to supplier", ["Supplier", "Invoices", "Dollars in question", "What to say"],
          [[s, ", ".join(sorted({str(f["invoice"]) for f in fs})), round(sum(f["amount"] for f in fs), 2),
            "\n".join(f"{n}. {f['say']}" for n, f in enumerate(fs, 1))] for s, fs in groups.items()],
          [28, 20, 18, 110], money_cols=(3,), wrap_cols=(4,))
    wb["Send to supplier"]["A1"] = "One row per supplier. Every number comes from the Flags sheet."
    wb["Send to supplier"]["A1"].font = Font(bold=True)

    ws3 = wb.create_sheet()
    sheet(ws3, "All lines", ["File", "Supplier", "Invoice", "PO", "Line", "Part", "Description", "Qty", "Unit", "Price", "Amount", "Result", "Note"],
          [[r["file"], r["supplier"], r["invoice"], r["po"], r["line"], r["part"], r["description"], r["qty"], r["uom"],
            r["price"], r["amount"], r["result"], r["note"]] for r in all_lines],
          [34, 26, 11, 17, 6, 16, 32, 8, 7, 10, 12, 26, 46], money_cols=(10, 11))
    ws3["A1"] = "Every invoice line, OK or not."; ws3["A1"].font = Font(bold=True)
    for r in range(4, 4 + len(all_lines)):
        if ws3.cell(row=r, column=12).value not in ("OK",):
            for col in range(1, 14): ws3.cell(row=r, column=col).fill = flag_fill

    ws4 = wb.create_sheet()
    sheet(ws4, "Rules", ["Unit", "Base unit", "Base units in one"], [[k, v[0], v[1]] for k, v in UNITS.items()], [12, 12, 18])
    ws4["A1"] = f"Price tolerance {PRICE_TOLERANCE} per PO unit. Change rules at the top of check_invoices.py."
    ws4["A1"].font = Font(bold=True)
    wb.save(out)
    return summary


def main(argv):
    if len(argv) != 3:
        print("Usage: python3 check_invoices.py invoices.json open-po-export.xlsx")
        return 2
    try:
        import openpyxl  # noqa: F401
    except ImportError:
        print("STOPPED. This script needs openpyxl to read and write Excel files. Run: pip install openpyxl")
        return 1
    try:
        invoices = load_invoices(argv[1])
        po_lines = load_po_lines(argv[2])
    except BadData as e:
        print(f"STOPPED. Bad data, nothing was checked. {e}")
        return 1
    flags, all_lines, unique, n_files = check(invoices, po_lines)
    out = Path(argv[1]).with_name("invoice-check.xlsx")
    summary = write_book(out, flags, all_lines, unique, n_files)
    print(summary)
    for f in flags:
        print(f"- {f['kind']}: {f['supplier']} {f['invoice']} line {f['line'] or '-'}  {m(f['amount'])}")
    print(f"Wrote {out.name}")
    return 0


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