"""Find likely duplicate supplier bills before payment.
Rules: same normalised supplier AND (same normalised invoice number, OR same amount within 5 cents and dates within 7 days).
Usage: python find_duplicates.py ap_bills.csv -> duplicate_pairs.csv"""
import csv, re, sys, datetime, itertools
def norm_sup(s): return re.sub(r"\b(pty|ltd|limited|co)\b", "", s.lower()).replace(".", "").strip()
def norm_num(s): return re.sub(r"^(inv)", "", re.sub(r"[^a-z0-9]", "", s.lower()))
def main(fn):
    rows = list(csv.DictReader(open(fn))); pairs = []
    by_sup = {}
    for r in rows: by_sup.setdefault(norm_sup(r["supplier"]), []).append(r)
    for sup, group in by_sup.items():
        for a, b in itertools.combinations(group, 2):
            same_num = norm_num(a["invoice_number"]) == norm_num(b["invoice_number"])
            da, db = (datetime.date.fromisoformat(x["invoice_date"]) for x in (a, b))
            close = abs(float(a["amount"]) - float(b["amount"])) <= 0.05 and abs((da - db).days) <= 7
            if same_num or close:
                pairs.append([a["bill_id"], b["bill_id"], a["supplier"], b["supplier"], a["invoice_number"], b["invoice_number"], a["amount"], b["amount"],
                              "Same invoice number" if same_num else "Same amount, dates within 7 days", "Hold second bill until checked"])
    with open("duplicate_pairs.csv", "w", newline="") as f:
        w = csv.writer(f); w.writerow(["bill_a", "bill_b", "supplier_a", "supplier_b", "number_a", "number_b", "amount_a", "amount_b", "reason", "action"]); w.writerows(pairs)
    print(f"{len(rows)} bills checked, {len(pairs)} likely duplicate pairs, ${sum(float(p[7]) for p in pairs):,.2f} held")
if __name__ == "__main__": main(sys.argv[1] if len(sys.argv) > 1 else "ap_bills.csv")