"""Apply an ordered rule list to card statement lines. First matching rule wins; anything unmatched goes to a review queue,
and lines over a rule's threshold are flagged. Usage: python categorise.py card_statement.csv rules.csv -> categorised.xlsx"""
import csv, re, sys
from collections import defaultdict
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
def main(stmt, rules_fn):
    rules = sorted(csv.DictReader(open(rules_fn)), key=lambda r: int(r["priority"])); out = []; tot = defaultdict(float); hits = defaultdict(int)
    for t in csv.DictReader(open(stmt)):
        d = t["Description"].upper(); amt = -float(t["Amount"]); rule = next((r for r in rules if (r["pattern"] in d if r["match_type"] == "contains" else re.search(r["pattern"], d))), None)
        cat = rule["category"] if rule else "REVIEW"; flag = ""
        if rule and rule["note"].startswith("Over 500") and amt > 500: flag = "Receipt check (over $500)"
        elif rule and rule["note"]: flag = rule["note"]
        if not rule: flag = "No rule matched"
        out.append([t["Date"], t["Description"], round(amt, 2), cat, rule["priority"] if rule else "", flag]); tot[cat] += amt
        if rule: hits[rule["priority"]] += 1
    wb = Workbook(); ws = wb.active; ws.title = "Categorised"; ws.append(["Date", "Description", "Amount", "Category", "Rule #", "Flag"]); [ws.append(o) for o in out]
    s = wb.create_sheet("By category"); s.append(["Category", "Lines", "Total"])
    for k in sorted(tot, key=lambda k: -tot[k]): s.append([k, sum(1 for o in out if o[3] == k), round(tot[k], 2)])
    q = wb.create_sheet("Review queue"); q.append(["Date", "Description", "Amount", "Suggested action"])
    for o in out:
        if o[3] == "REVIEW": q.append([o[0], o[1], o[2], "Add a rule or code by hand"])
    rs = wb.create_sheet("Rules"); rs.append(["Priority", "Match", "Pattern", "Category", "Note", "Lines matched"]); [rs.append([r["priority"], r["match_type"], r["pattern"], r["category"], r["note"], hits[r["priority"]]]) for r in rules]
    for sh in wb:
        for c in sh[1]: c.font = Font(bold=True, color="FFFFFF"); c.fill = PatternFill("solid", fgColor="1F4E79")
        for col, w in zip("ABCDEF", [12, 36, 12, 26, 10, 30]): sh.column_dimensions[col].width = w
    wb.save("categorised.xlsx")
    auto = sum(1 for o in out if o[3] != "REVIEW"); print(f"{len(out)} lines, {auto} auto-categorised ({auto / len(out):.0%}), {len(out) - auto} to review, {sum(1 for o in out if o[5].startswith('Receipt'))} receipt checks")
if __name__ == "__main__": main(*(sys.argv[1:3] if len(sys.argv) > 2 else ["card_statement.csv", "rules.csv"]))