#!/usr/bin/env python3
"""
Reconcile an EXO invoice export against the invoices imported into an Evolution
tenant.

Usage:
    python3 reconcile.py <exo_export.csv> <evo_dump.tsv> [out_prefix]

exo_export.csv : headerless EXO recon export, 6 cols
                 invoice-no, debtor-id, name, date d/m/Y, order-ref, gross
                 NB amounts carry thousands separators ("3,248.11") -- strip
                 them before float() or you silently score the row as 0.00.
evo_dump.tsv   : reference, date, balance, total, tax, clientid, id
                 (SELECT ... FROM invoices WHERE sourceRef LIKE 'MYOB_EXO:%')
                 NB the provenance key moved out of externalRef/externalTenant
                 into invoices.sourceRef ('MYOB_EXO:<SEQNO>') on 30/07/2026 --
                 those two columns belong to Xero. Older notes/dumps that
                 filter externalTenant='MYOB_EXO' now return zero rows.

Match key: EXO invoice-no == invoices.reference. CSV gross == invoices.balance
(imported invoices land unpaid, so balance is the full gross).
"""
import csv, sys, collections

def money(s):
    """Parse an EXO money string.

    Two traps, both of which fail silently as 0.00 if you hand-roll this:
      * thousands separators -- "3,248.11"
      * accounting negatives in parentheses -- "(108.00)" means -108.00.
        EXO writes every credit note and return this way. Scoring them 0.00
        makes a row that matches exactly look like a full-value mismatch.
    """
    s = (s or "").strip().replace(",", "").replace("$", "")
    if s in ("", "-"): return 0.0
    neg = s.startswith("(") and s.endswith(")")
    if neg: s = s[1:-1].strip()
    try: return -float(s) if neg else float(s)
    except ValueError: return 0.0

def d2iso(s):
    try:
        d, m, y = s.strip().split("/")
        return f"{int(y):04d}-{int(m):02d}-{int(d):02d}"
    except Exception:
        return None

def main(csv_path, evo_path, prefix="recon"):
    csvmap = {}
    malformed = []
    with open(csv_path, newline="", encoding="utf-8-sig") as f:
        for r in csv.reader(f):
            if not r or not r[0].strip(): continue
            ref = r[0].strip()
            debtor = r[1].strip() if len(r) > 1 else ""
            name = r[2].strip() if len(r) > 2 else ""
            iso = d2iso(r[3]) if len(r) > 3 else None
            amt = money(r[5]) if len(r) > 5 else 0.0
            if not debtor.isdigit():
                malformed.append((ref, debtor, name, iso, amt))
            csvmap[ref] = (iso, amt, debtor, name)

    evo = {}
    for line in open(evo_path):
        p = line.rstrip("\n").split("\t")
        if len(p) < 7: continue
        evo[p[0].strip()] = (p[1], money(p[2]), money(p[3]), money(p[4]), p[5], p[6])

    cs, es = set(csvmap), set(evo)
    only_csv, only_evo, both = cs - es, es - cs, cs & es

    tot_csv = sum(csvmap[k][1] for k in cs)
    tot_evo = sum(evo[k][1] for k in es)
    print(f"EXO export : {len(cs):5d} invoices  ${tot_csv:>13,.2f}")
    print(f"Evolution  : {len(es):5d} invoices  ${tot_evo:>13,.2f}")
    print(f"{'':11s}  {'':5s}             ${tot_evo - tot_csv:>13,.2f}  net")
    print()

    s_only_csv = sum(csvmap[k][1] for k in only_csv)
    s_only_evo = sum(evo[k][1] for k in only_evo)
    print(f"In EXO, not in Evolution : {len(only_csv):5d}  ${s_only_csv:>12,.2f}")
    print(f"In Evolution, not in EXO : {len(only_evo):5d}  ${s_only_evo:>12,.2f}")
    print(f"Matched on invoice no    : {len(both):5d}")

    mism = []
    for k in both:
        c, e = csvmap[k][1], evo[k][1]
        if abs(c - e) > 0.005:
            mism.append((k, c, e, e - c, evo[k][0]))
    print(f"  of which amount differs: {len(mism):5d}  net ${sum(m[3] for m in mism):>12,.2f}")
    print()

    def bucket(label, keys, get_iso, get_amt):
        b = collections.Counter(); a = collections.Counter()
        for k in keys:
            iso = get_iso(k) or "0000-00-00"
            per = "pre 2026-07-01" if iso < "2026-07-01" else "2026-07-01 onward"
            b[per] += 1; a[per] += get_amt(k)
        print(f"{label}:")
        for p in sorted(b): print(f"   {p:18s} {b[p]:5d}  ${a[p]:>12,.2f}")
        print()

    bucket("In EXO, not in Evolution -- by date", only_csv,
           lambda k: csvmap[k][0], lambda k: csvmap[k][1])
    bucket("In Evolution, not in EXO -- by date", only_evo,
           lambda k: evo[k][0], lambda k: evo[k][1])

    if malformed:
        print(f"EXO rows with a non-numeric debtor id: {len(malformed)}")
        for ref, deb, name, iso, amt in sorted(malformed, key=lambda x: x[0]):
            print(f"   {ref}  debtor={deb!r:24s} name={name!r:28s} {iso}  ${amt:,.2f}")
        print()

    with open(f"{prefix}_missing_from_evo.csv", "w", newline="") as f:
        w = csv.writer(f); w.writerow(["exo_invoice_no","date","debtor_id","name","gross"])
        for k in sorted(only_csv, key=lambda k: (csvmap[k][0] or "", k)):
            iso, amt, deb, name = csvmap[k]
            w.writerow([k, iso, deb, name, f"{amt:.2f}"])

    with open(f"{prefix}_evo_only.csv", "w", newline="") as f:
        w = csv.writer(f); w.writerow(["reference","date","balance","clientid","invoice_id"])
        for k in sorted(only_evo, key=lambda k: (evo[k][0], k)):
            v = evo[k]; w.writerow([k, v[0], f"{v[1]:.2f}", v[4], v[5]])

    with open(f"{prefix}_amount_mismatch.csv", "w", newline="") as f:
        w = csv.writer(f); w.writerow(["exo_invoice_no","date","exo_gross","evo_balance","delta"])
        for k, c, e, d, dt in sorted(mism, key=lambda x: -abs(x[3])):
            w.writerow([k, dt, f"{c:.2f}", f"{e:.2f}", f"{d:.2f}"])

    print(f"wrote {prefix}_missing_from_evo.csv / _evo_only.csv / _amount_mismatch.csv")

if __name__ == "__main__":
    if len(sys.argv) < 3:
        print(__doc__); sys.exit(1)
    main(sys.argv[1], sys.argv[2], sys.argv[3] if len(sys.argv) > 3 else "recon")