"""QBO -> Xero conversion engine (work sample).
Reads the QBO exports in data/, maps the chart, tax codes, contacts and items, finds the QBO clean-up items, proposes the
pre-cutover journals, writes every Xero import file to results/xero_import/ and proves the Xero balances equal QBO account by account."""
import csv, os, re, json, datetime as dt
from collections import defaultdict, Counter
HERE = os.path.dirname(os.path.abspath(__file__)); RES = os.path.join(HERE, "results"); IMP = os.path.join(RES, "xero_import")
os.makedirs(IMP, exist_ok=True)
rd = lambda p: list(csv.DictReader(open(os.path.join(HERE, p))))
def w(path, rows, fields=None):
    with open(os.path.join(RES, path), "w", newline="") as f:
        x = csv.DictWriter(f, fieldnames=fields or list(rows[0].keys())); x.writeheader(); x.writerows(rows)
c2 = lambda v: round(v + 1e-9, 2)
CONV = dt.date(2026, 9, 30)
ACC = rd("data/qbo_accounts.csv"); GL = rd("data/qbo_general_ledger_jun-sep.csv"); TBX = rd("data/qbo_trial_balance_30sep2026.csv"); TB0X = rd("data/qbo_trial_balance_30jun2026.csv")
CUS = rd("data/qbo_customers.csv"); SUP = rd("data/qbo_suppliers.csv"); ITM = rd("data/qbo_products_services.csv")
ARA = rd("data/qbo_ar_aging_detail_30sep2026.csv"); APA = rd("data/qbo_ap_aging_detail_30sep2026.csv"); STM = {r["Account"]: float(r["Statement_Balance"]) for r in rd("data/bank_statement_balances_30sep2026.csv")}
for g in GL: g["Amount"] = float(g["Amount"])
tbv = lambda r: c2(float(r["Debit"] or 0) - float(r["Credit"] or 0))
TB = defaultdict(float, {r["Account"]: tbv(r) for r in TBX}); TB0 = defaultdict(float, {r["Account"]: tbv(r) for r in TB0X})
# ------------------------------------------------------------------ 1. target chart (trimmed from the Xero AU standard chart)
XC = [("090", "Business Cheque Account", "BANK", "BAS Excluded"), ("091", "Business Savings Account", "BANK", "BAS Excluded"), ("092", "Business Visa", "BANK", "BAS Excluded"),
 ("610", "Accounts Receivable", "CURRENT", "BAS Excluded"), ("615", "Undeposited payments (clearing)", "CURRENT", "BAS Excluded"), ("620", "Prepayments", "CURRENT", "BAS Excluded"),
 ("630", "Inventory", "INVENTORY", "BAS Excluded"), ("631", "Work in progress", "CURRENT", "BAS Excluded"), ("632", "Rental bond", "CURRENT", "BAS Excluded"),
 ("710", "Plant and equipment", "FIXED", "GST on Capital"), ("711", "Less accumulated depreciation on plant and equipment", "FIXED", "BAS Excluded"),
 ("720", "Motor vehicles", "FIXED", "GST on Capital"), ("721", "Less accumulated depreciation on motor vehicles", "FIXED", "BAS Excluded"),
 ("730", "Computer equipment", "FIXED", "GST on Capital"), ("731", "Less accumulated depreciation on computer equipment", "FIXED", "BAS Excluded"),
 ("800", "Accounts Payable", "CURRLIAB", "BAS Excluded"), ("820", "GST", "CURRLIAB", "BAS Excluded"), ("825", "PAYG Withholdings Payable", "CURRLIAB", "BAS Excluded"),
 ("826", "Superannuation Payable", "CURRLIAB", "BAS Excluded"), ("827", "Accrued expenses", "CURRLIAB", "BAS Excluded"), ("835", "Customer deposits", "CURRLIAB", "BAS Excluded"),
 ("840", "Historical Adjustment", "CURRLIAB", "BAS Excluded"), ("850", "Suspense", "CURRLIAB", "BAS Excluded"), ("855", "Conversion clearing (open items)", "CURRLIAB", "BAS Excluded"), ("860", "Rounding", "CURRLIAB", "BAS Excluded"),
 ("880", "Owner A Drawings", "EQUITY", "BAS Excluded"), ("900", "Equipment loan", "TERMLIAB", "BAS Excluded"), ("901", "Vehicle finance", "TERMLIAB", "BAS Excluded"),
 ("960", "Retained Earnings", "EQUITY", "BAS Excluded"), ("970", "Owner A Share Capital", "EQUITY", "BAS Excluded"),
 ("200", "Joinery sales", "REVENUE", "GST on Income"), ("201", "Shopfitting contracts", "REVENUE", "GST on Income"), ("202", "Installation labour", "REVENUE", "GST on Income"),
 ("203", "Design and drafting fees", "REVENUE", "GST on Income"), ("205", "Discounts given", "REVENUE", "GST on Income"), ("260", "Other revenue", "REVENUE", "GST on Income"),
 ("270", "Interest income", "OTHERINCOME", "Input Taxed"), ("310", "Timber and board", "DIRECTCOSTS", "GST on Expenses"), ("311", "Hardware and fittings", "DIRECTCOSTS", "GST on Expenses"),
 ("312", "Benchtops (outsourced)", "DIRECTCOSTS", "GST on Expenses"), ("313", "Subcontractors", "DIRECTCOSTS", "GST on Expenses"), ("314", "Freight and cartage", "DIRECTCOSTS", "GST on Expenses"),
 ("315", "Equipment hire", "DIRECTCOSTS", "GST on Expenses"), ("400", "Advertising and marketing", "EXPENSE", "GST on Expenses"), ("404", "Bank fees", "EXPENSE", "GST Free Expenses"),
 ("405", "Merchant fees", "EXPENSE", "GST on Expenses"), ("408", "Cleaning", "EXPENSE", "GST on Expenses"), ("412", "Consulting and accounting", "EXPENSE", "GST on Expenses"),
 ("416", "Depreciation", "DEPRECIATN", "BAS Excluded"), ("420", "Entertainment", "EXPENSE", "GST on Expenses"), ("429", "General expenses", "EXPENSE", "GST on Expenses"),
 ("433", "Insurance", "EXPENSE", "GST on Expenses"), ("434", "Workcover", "EXPENSE", "GST Free Expenses"), ("437", "Interest expense", "EXPENSE", "GST Free Expenses"),
 ("441", "Legal expenses", "EXPENSE", "GST on Expenses"), ("445", "Light, power, heating", "EXPENSE", "GST on Expenses"), ("449", "Motor vehicle expenses", "EXPENSE", "GST on Expenses"),
 ("450", "Motor vehicle registration and insurance", "EXPENSE", "GST Free Expenses"), ("453", "Office expenses", "EXPENSE", "GST on Expenses"), ("461", "Printing and stationery", "EXPENSE", "GST on Expenses"),
 ("469", "Rent", "EXPENSE", "GST on Expenses"), ("473", "Repairs and maintenance", "EXPENSE", "GST on Expenses"), ("474", "Small tools", "EXPENSE", "GST on Expenses"),
 ("477", "Wages and salaries", "EXPENSE", "BAS Excluded"), ("478", "Superannuation", "EXPENSE", "BAS Excluded"), ("479", "Staff amenities", "EXPENSE", "GST on Expenses"),
 ("480", "Protective clothing", "EXPENSE", "GST on Expenses"), ("481", "Training", "EXPENSE", "GST on Expenses"), ("485", "Subscriptions and software", "EXPENSE", "GST on Expenses"),
 ("489", "Telephone and internet", "EXPENSE", "GST on Expenses"), ("493", "Travel - national", "EXPENSE", "GST on Expenses"), ("494", "Waste removal", "EXPENSE", "GST on Expenses"),
 ("495", "Licences and permits", "EXPENSE", "GST Free Expenses"), ("496", "Donations", "EXPENSE", "GST Free Expenses"), ("497", "Fines and penalties", "EXPENSE", "BAS Excluded")]
XB = {c: dict(code=c, name=n, type=t, tax=x) for c, n, t, x in XC}
# ------------------------------------------------------------------ 2. account mapping: ordered rules on the QBO name, then the detail type
RULES = [(r"^ANZ Business Cheque", "090"), (r"Saver|Savings", "091"), (r"Visa|Mastercard|Amex", "092"), (r"^Undeposited", "615"), (r"Accounts Receivable", "610"),
 (r"^Prepa", "620"), (r"Stock on hand|Inventory", "630"), (r"Work in progress", "631"), (r"bond", "632"),
 (r"^Plant.*Accumulated|depn.*plant", "711"), (r"^Plant", "710"), (r"^Motor vehicles:Accum", "721"), (r"^Motor vehicles$", "720"), (r"depn - computers|Computer.*Accum", "731"), (r"^Computer", "730"),
 (r"Accounts Payable", "800"), (r"^GST", "820"), (r"PAYG", "825"), (r"Superannuation Payable", "826"), (r"Accrued", "827"), (r"Customer deposits", "835"),
 (r"Equipment loan", "900"), (r"Vehicle finance", "901"), (r"Opening Balance Equity|Retained Earnings", "960"), (r"drawings", "880"), (r"Share capital", "970"),
 (r"Joinery sales", "200"), (r"Shopfitting", "201"), (r"Installation", "202"), (r"Design", "203"), (r"Discount", "205"), (r"Interest income", "270"), (r"Sales - other|Other income", "260"),
 (r"Timber", "310"), (r"Hardware", "311"), (r"Benchtop", "312"), (r"Subcontract", "313"), (r"Freight", "314"), (r"Equipment hire", "315"),
 (r"Advertising|Marketing", "400"), (r"Bank fees", "404"), (r"Merchant", "405"), (r"Cleaning", "408"), (r"Accounting", "412"), (r"^Depreciation", "416"), (r"Entertainment", "420"),
 (r"General expenses", "429"), (r"^Insurance", "433"), (r"Workcover", "434"), (r"Interest expense", "437"), (r"Legal", "441"), (r"Electricity|Gas", "445"),
 (r"Rego", "450"), (r"^Motor Vehicle", "449"), (r"Office|Postage", "453"), (r"Printing", "461"), (r"^Rent", "469"), (r"Repairs & maint", "473"), (r"Small tools", "474"),
 (r"Wages", "477"), (r"^Superannuation$", "478"), (r"amenities", "479"), (r"Protective", "480"), (r"Training", "481"), (r"Subscriptions|Software", "485"),
 (r"Telephone|Mobile|Internet", "489"), (r"^Travel", "493"), (r"Waste", "494"), (r"Licences", "495"), (r"Donations", "496"), (r"Fines", "497"), (r"^Rounding", "860"),
 (r"Uncategorised|Ask My Accountant", "850")]
TYPE_OK = {"Bank": {"BANK"}, "Credit Card": {"BANK"}, "Accounts receivable (A/R)": {"CURRENT"}, "Other Current Assets": {"CURRENT", "INVENTORY"}, "Fixed Assets": {"FIXED"},
           "Accounts payable (A/P)": {"CURRLIAB"}, "Other Current Liabilities": {"CURRLIAB"}, "Long Term Liabilities": {"TERMLIAB"}, "Equity": {"EQUITY"},
           "Income": {"REVENUE", "CURRLIAB"}, "Other Income": {"OTHERINCOME", "REVENUE"}, "Cost of Goods Sold": {"DIRECTCOSTS"}, "Expenses": {"EXPENSE", "DEPRECIATN", "CURRLIAB"}}
AMAP = []
for a in ACC:
    code = next((c for p, c in RULES if re.search(p, a["Account"], re.I)), "850")
    typ_ok = XB[code]["type"] in TYPE_OK[a["Type"]]
    qtax = a["Default_Tax_Code"]; cls = XB[code]["type"]
    std = "GST on Income" if qtax == "GST" and cls in ("REVENUE", "OTHERINCOME") else ("GST on Expenses" if qtax == "GST" else "BAS Excluded")
    note = []
    if a["Active"] == "No" and abs(TB[a["Account"]]) >= .005: note.append("Inactive in QBO but holds a balance")
    if sum(1 for b in ACC if next((c for p, c in RULES if re.search(p, b["Account"], re.I)), "850") == code) > 1: note.append("Merged with other QBO accounts")
    if ":" in a["Account"]: note.append("QBO sub-account flattened")
    if code == "850": note.append("Holding account: must be empty at cutover")
    AMAP.append(dict(qbo_account=a["Account"], qbo_type=a["Type"], active=a["Active"], qbo_tax=qtax or "(none)", xero_code=code, xero_name=XB[code]["name"], xero_type=cls,
                     xero_tax=XB[code]["tax"], tax_change="Yes" if XB[code]["tax"] != std and qtax == "GST" and code != "850" else "No", type_ok=int(typ_ok), balance_30sep=c2(TB[a["Account"]]), note="; ".join(note)))
AM = {m["qbo_account"]: m for m in AMAP}
w("account_map.csv", AMAP)
# ------------------------------------------------------------------ 3. GL checks: roll-forward 30 Jun + movements = 30 Sep, and GST codes on the period lines
mov = defaultdict(float)
for g in GL:
    if g["Date"] > "2026-06-30": mov[g["Account"]] += g["Amount"]
ROLL = [dict(account=a["Account"], tb_30jun=c2(TB0[a["Account"]]), movement=c2(mov[a["Account"]]), rolled=c2(TB0[a["Account"]] + mov[a["Account"]]), tb_30sep=c2(TB[a["Account"]]),
             diff=c2(TB0[a["Account"]] + mov[a["Account"]] - TB[a["Account"]])) for a in ACC]
w("tb_rollforward.csv", ROLL)
TAXREV = [dict(qbo_account=m["qbo_account"], qbo_default=m["qbo_tax"], xero_tax=m["xero_tax"], lines_jul_sep=sum(1 for g in GL if g["Account"] == m["qbo_account"] and g["Date"] > "2026-06-30"),
               gst_on_lines=0.0, action="Set the Xero default; GST on past lines is nil, so figures already reported do not change; confirm with your BAS agent or accountant") for m in AMAP if m["tax_change"] == "Yes"]
w("tax_review.csv", TAXREV)
# ------------------------------------------------------------------ 4. contacts: one Xero contact per real business across both QBO lists
def norm(n):
    n = n.lower().replace("&", " and ").replace(".", " "); n = re.sub(r"\b(pty|ltd|limited|p/l)\b", " ", n); n = re.sub(r"[^a-z0-9 ]", " ", n); return " ".join(n.split())
CT = [dict(qid=c["Customer_ID"], role="Customer", name=c["Customer"], abn=c["ABN"].replace(" ", ""), email=c["Email"].lower(), phone=c["Phone"], active=c["Active"]) for c in CUS] + \
     [dict(qid=s["Supplier_ID"], role="Supplier", name=s["Supplier"], abn=s["ABN"].replace(" ", ""), email=s["Email"].lower(), phone=s["Phone"], active=s["Active"]) for s in SUP]
par = {c["qid"]: c["qid"] for c in CT}; why = {}
def find(x):
    while par[x] != x: par[x] = par[par[x]]; x = par[x]
    return x
def unite(a, b, r):
    A, B = find(a), find(b)
    if A == B: return
    keep, drop = sorted([A, B], key=lambda q: (q[0] != "C", int(q[1:])))
    par[drop] = keep; why.setdefault(b, r); why.setdefault(a, r)
by = defaultdict(list)
for c in CT:
    if c["abn"]: by[("abn", c["abn"])].append(c)
for k, v in by.items():
    for c in v[1:]: unite(v[0]["qid"], c["qid"], "Same ABN")
CB_ = {c["qid"]: c for c in CT}
byn = defaultdict(list)
for c in CT: byn[norm(c["name"])].append(c)
BLOCK = 0
for k, v in byn.items():
    for c in v[1:]:
        a = v[0]
        if a["abn"] and c["abn"] and a["abn"] != c["abn"]: BLOCK += 1; continue    # different ABNs never merge
        unite(a["qid"], c["qid"], "Same name (normalised)")
grp = defaultdict(list)
for c in CT: grp[find(c["qid"])].append(c)
CMAP = []; XCON = []
for root, cs in grp.items():
    k = CB_[root]; cs = sorted(cs, key=lambda q: (q["qid"] != root, q["qid"]))
    nm = k["name"] if k["name"] == k["name"].strip() and not k["name"].isupper() else cs[0]["name"]
    nm = re.sub(r"\s+", " ", nm); roles = sorted({c["role"] for c in cs})
    act = any(c["active"] == "Yes" for c in cs)
    XCON.append({"*ContactName": nm, "AccountNumber": root, "EmailAddress": next((c["email"] for c in cs if c["email"]), ""), "PhoneNumber": next((c["phone"] for c in cs if c["phone"]), ""),
                 "TaxNumber": next((c["abn"] for c in cs if c["abn"]), ""), "Roles": " + ".join(roles), "Status": "Active" if act else "Archive after import", "QBO records": len(cs)})
    for c in cs:
        CMAP.append(dict(qbo_id=c["qid"], role=c["role"], qbo_name=c["name"], abn=c["abn"], xero_contact=nm, xero_key=root, action="Keep" if c["qid"] == root else "Merge into " + root,
                         rule="" if c["qid"] == root else why.get(c["qid"], "Same name (normalised)")))
CMB = {c["qbo_id"]: c for c in CMAP}
w("contact_map.csv", CMAP); w("xero_import/xero_contacts.csv", [{k: v for k, v in x.items() if k not in ("Roles", "Status", "QBO records")} for x in XCON]); w("xero_contacts_review.csv", XCON)
# ------------------------------------------------------------------ 5. items
XIT = [{"*ItemCode": i["SKU"], "ItemName": i["Name"][:50], "SalesUnitPrice": i["Sales_Price"], "SalesAccount": AM[i["Income_Account"]]["xero_code"], "SalesTaxRate": "GST on Income",
        "PurchasesAccount": AM[i["Expense_Account"]]["xero_code"] if i["Expense_Account"] else "", "PurchasesTaxRate": "GST on Expenses" if i["Expense_Account"] else "", "QBO active": i["Active"]} for i in ITM]
w("items_map.csv", XIT); w("xero_import/xero_items.csv", [{k: v for k, v in x.items() if k != "QBO active"} for x in XIT if x["QBO active"] == "Yes"])
# ------------------------------------------------------------------ 6. clean-up items found in QBO
# 6a. payments stuck in Undeposited Funds while the bank feed also added the same money as income
UF = defaultdict(float); ufrows = {}
for g in GL:
    if g["Account"] == "Undeposited Funds":
        if g["Type"] == "Payment": ufrows[g["Txn"]] = g
dep_txn = defaultdict(list)
for g in GL: dep_txn[g["Txn"]].append(g)
# a payment is deposited if a Deposit drew it out; QBO links deposits to payments, here via the batch amounts in date order
open_uf = []; pend = []
evs = sorted([g for g in GL if g["Account"] == "Undeposited Funds" and g["Date"] > "2026-06-30"], key=lambda g: (g["Date"], int(g["Txn"])))
for g in evs:
    if g["Type"] == "Payment": pend.append(g)
    elif g["Type"] == "Deposit":
        tot = -g["Amount"]; took = []; s = 0
        for p in sorted([p for p in pend if p["Date"] < g["Date"]], key=lambda p: (p["Date"], int(p["Txn"]))):
            pass
        # find the subset: all pending older payments except those whose sum would overshoot; deposits in this file sweep every pending payment but the stuck ones
        cand = [p for p in pend if p["Date"] < g["Date"]]; total = c2(sum(p["Amount"] for p in cand))
        if abs(total - tot) < .005: took = cand
        else:
            # drop the smallest set of payments that explains the difference (the ones not deposited)
            diff = c2(total - tot); took = list(cand)
            for p in sorted(cand, key=lambda p: p["Date"]):
                if abs(p["Amount"] - diff) < .005: took.remove(p); break
            else:
                import itertools
                for k in (2, 3, 4):
                    hit = next((cmb for cmb in itertools.combinations(cand, k) if abs(sum(x["Amount"] for x in cmb) - diff) < .005), None)
                    if hit:
                        for x in hit: took.remove(x)
                        break
        for p in took: pend.remove(p)
UFOPEN = pend
bank_income = [g for g in GL if g["Type"] == "Deposit" and g["Account"] in ("Joinery sales", "Shopfitting contracts", "Installation labour", "Design & drafting fees", "Uncategorised Income")]
UFX = []
for p in UFOPEN:
    age = (CONV - dt.date.fromisoformat(p["Date"])).days
    gross = p["Amount"]; hit = None
    for g in bank_income:
        lines = dep_txn[g["Txn"]]; bank = sum(l["Amount"] for l in lines if l["Account"] == "ANZ Business Cheque")
        if g["Name"] == p["Name"] and abs(bank - gross) < .005 and 0 <= (dt.date.fromisoformat(g["Date"]) - dt.date.fromisoformat(p["Date"])).days <= 5: hit = g; break
    UFX.append(dict(txn=p["Txn"], date=p["Date"], customer=p["Name"], amount=c2(gross), age_days=age, status="Banked twice: also added to income by the bank feed" if hit else ("Not yet banked (recent)" if age <= 7 else "Not banked after 7 days: check"),
                    bank_feed_txn=hit["Txn"] if hit else "", income_account=hit["Account"] if hit else "", income_ex_gst=c2(-hit["Amount"]) if hit else 0.0,
                    gst=c2(-sum(l["Amount"] for l in dep_txn[hit["Txn"]] if l["Account"] == "GST Payable")) if hit else 0.0))
w("undeposited_funds.csv", UFX)
# 6b. Uncategorised Expense lines coded by payee rule
PAYEE = [(r"AMPOL|BP ", "Motor Vehicle:Fuel"), (r"BUNNINGS", "Hardware & fittings"), (r"OFFICEWORKS", "Office expenses"), (r"TELSTRA", "Telephone"), (r"TOTAL TOOLS", "Small tools"),
         (r"AUST POST", "Postage"), (r"ADOBE", "Software"), (r"GOOGLE", "Advertising")]
UNC = []
for g in GL:
    if g["Account"] == "Uncategorised Expense":
        acc = next((a for p, a in PAYEE if re.search(p, g["Name"])), "")
        UNC.append(dict(txn=g["Txn"], date=g["Date"], payee=g["Name"], amount=c2(g["Amount"]), proposed_account=acc, xero_code=AM[acc]["xero_code"] if acc else "850",
                        ex_gst=c2(g["Amount"] / 1.1) if acc else 0.0, gst=c2(g["Amount"] - c2(g["Amount"] / 1.1)) if acc else 0.0,
                        status="Recode by payee rule (tax invoice to be on file)" if acc else "Owner to identify: stays in Suspense until coded"))
w("uncategorised.csv", UNC)
# ------------------------------------------------------------------ 7. pre-cutover journals (posted in QBO before the final TB is taken)
J = []
for u in UFX:
    if u["bank_feed_txn"]:
        J += [dict(journal="J1", line_account=u["income_account"], amount=u["income_ex_gst"], memo=f"Reverse bank-feed income: payment txn {u['txn']} already in Undeposited Funds"),
              dict(journal="J1", line_account="GST Payable", amount=u["gst"], memo=f"GST on the same receipt counted twice (txn {u['bank_feed_txn']})"),
              dict(journal="J1", line_account="Undeposited Funds", amount=-u["amount"], memo=f"Clear payment txn {u['txn']} (money already in the bank)")]
for u in UNC:
    if u["proposed_account"]:
        J += [dict(journal="J2", line_account=u["proposed_account"], amount=u["ex_gst"], memo=f"Recode {u['payee']} ({u['date']})"), dict(journal="J2", line_account="GST Payable", amount=u["gst"], memo=f"GST credit on {u['payee']}"),
              dict(journal="J2", line_account="Uncategorised Expense", amount=-u["amount"], memo=f"Clear {u['payee']}")]
obe = TB["Opening Balance Equity"]
if abs(obe) >= .005: J += [dict(journal="J3", line_account="Opening Balance Equity", amount=-obe, memo="Clear Opening Balance Equity (left by an old opening balance)"), dict(journal="J3", line_account="Retained Earnings", amount=obe, memo="Accountant to confirm the reclass to retained earnings")]
for j_ in ("J1", "J2", "J3"): assert abs(sum(x["amount"] for x in J if x["journal"] == j_)) < .005, j_
w("pre_cutover_journals.csv", J)
ADJ = defaultdict(float, TB)
for x in J: ADJ[x["line_account"]] += x["amount"]
ADJ = {k: c2(v) for k, v in ADJ.items()}
# ------------------------------------------------------------------ 8. Xero conversion balances, open items, bank items
uncl = [g for g in GL if g["Account"] in STM and g["Cleared"] == ""]
OPEN_AR = []; OPEN_AP = []
inv_acc = {}
for g in GL:
    if g["Type"] in ("Invoice", "Credit Memo") and g["Account"] in AM and AM[g["Account"]]["xero_type"] == "REVENUE": inv_acc.setdefault(g["Num"], AM[g["Account"]]["xero_code"])
for x in ARA:
    amt = float(x["Open_Balance"]); key = CMB[x["Customer_ID"]]["xero_key"]; nm = CMB[x["Customer_ID"]]["xero_contact"]; t = x["Transaction_Type"]
    OPEN_AR.append({"*ContactName": nm, "*InvoiceNumber": x["Num"] if t != "Unapplied payment" else f"UNAPPLIED-{x['Num']}", "*InvoiceDate": x["Date"], "*DueDate": x["Due_Date"],
                    "*Description": ("Balance owing at 30 Sep 2026 on QBO " if t == "Invoice" else "Open credit at 30 Sep 2026: QBO ") + t.lower() + " " + x["Num"], "*Quantity": 1,
                    "*UnitAmount": c2(abs(amt)), "*AccountCode": "855", "*TaxType": "BAS Excluded", "Type": "Invoice" if amt > 0 else "Credit note", "QBO contact": x["Customer"], "QBO ID": x["Customer_ID"],
                    "Remapped": "Yes" if CMB[x["Customer_ID"]]["action"] != "Keep" else "No", "Signed": c2(amt)})
for x in APA:
    amt = -float(x["Open_Balance"]); nm = CMB[x["Supplier_ID"]]["xero_contact"]; t = x["Transaction_Type"]
    OPEN_AP.append({"*ContactName": nm, "*InvoiceNumber": x["Num"], "*InvoiceDate": x["Date"], "*DueDate": x["Due_Date"], "*Description": f"Balance owing at 30 Sep 2026 on QBO {t.lower()} {x['Num']}",
                    "*Quantity": 1, "*UnitAmount": c2(abs(amt)), "*AccountCode": "855", "*TaxType": "BAS Excluded", "Type": "Bill" if amt < 0 else "Supplier credit note", "QBO contact": x["Supplier"],
                    "QBO ID": x["Supplier_ID"], "Remapped": "Yes" if CMB[x["Supplier_ID"]]["action"] != "Keep" else "No", "Signed": c2(amt)})
w("open_ar.csv", OPEN_AR); w("open_ap.csv", OPEN_AP)
keep = lambda r: {k: v for k, v in r.items() if k.startswith("*")}
w("xero_import/xero_sales_invoices.csv", [keep(r) for r in OPEN_AR if r["Type"] == "Invoice"]); w("xero_import/xero_sales_credit_notes.csv", [keep(r) for r in OPEN_AR if r["Type"] == "Credit note"])
w("xero_import/xero_bills.csv", [keep(r) for r in OPEN_AP if r["Type"] == "Bill"]); w("xero_import/xero_supplier_credit_notes.csv", [keep(r) for r in OPEN_AP if r["Type"] != "Bill"])
BANKX = [dict(bank=g["Account"], xero_code=AM[g["Account"]]["xero_code"], date=g["Date"], type=g["Type"], name=g["Name"], amount=c2(g["Amount"]), memo=g["Memo"], qbo_txn=g["Txn"]) for g in uncl]
w("bank_unreconciled.csv", BANKX)
w("xero_import/xero_bank_items_before_conversion.csv", [{"*Date": b["date"], "*Amount": b["amount"], "Payee": b["name"] or b["type"], "Description": f"QBO {b['type']} txn {b['qbo_txn']} not on the statement at 30 Sep", "Reference": b["qbo_txn"], "Bank": b["xero_code"], "AccountCode": "855"} for b in BANKX])
byx = defaultdict(float)
for a, v in ADJ.items(): byx[AM[a]["xero_code"]] += v
byx = {k: c2(v) for k, v in byx.items()}
ar_tot = c2(sum(r["Signed"] for r in OPEN_AR)); ap_tot = c2(sum(r["Signed"] for r in OPEN_AP))
bank_items = defaultdict(float)
for b in BANKX: bank_items[b["xero_code"]] += b["amount"]
CBAL = []
for c, n, t, x in XC:
    adj = byx.get(c, 0.0)
    if c == "610": ent, why_ = 0.0, "Xero builds this from the imported open invoices and credit notes"
    elif c == "800": ent, why_ = 0.0, "Xero builds this from the imported open bills and supplier credits"
    elif c in ("090", "091", "092"): ent, why_ = c2(adj - bank_items[c]), "Bank statement balance at 30 Sep; the unreconciled items are imported dated before 1 Oct"
    elif c == "855": ent, why_ = c2(byx.get("610", 0) + byx.get("800", 0) + sum(bank_items.values())), "Opposite of every open item and unreconciled bank item: nets to zero once they are imported"
    else: ent, why_ = adj, "Adjusted QBO balance"
    CBAL.append(dict(code=c, name=n, type=t, adjusted_qbo=c2(adj), conversion_balance=ent, why=why_))
assert abs(sum(r["conversion_balance"] for r in CBAL)) < .005, sum(r["conversion_balance"] for r in CBAL)
w("conversion_balances.csv", CBAL)
w("xero_import/xero_conversion_balances.csv", [{"Code": r["code"], "Account": r["name"], "Debit": r["conversion_balance"] if r["conversion_balance"] > 0 else "", "Credit": -r["conversion_balance"] if r["conversion_balance"] < 0 else ""}
                                               for r in CBAL if abs(r["conversion_balance"]) >= .005])
w("xero_import/xero_chart_of_accounts.csv", [{"*Code": c, "*Name": n, "*Type": t, "*Tax Code": x} for c, n, t, x in XC])
# ------------------------------------------------------------------ 9. simulated Xero trial balance after every import, compared with adjusted QBO
XTB = defaultdict(float)
for r in CBAL: XTB[r["code"]] += r["conversion_balance"]
for r in OPEN_AR: XTB["610"] += r["Signed"]; XTB["855"] -= r["Signed"]
for r in OPEN_AP: XTB["800"] += r["Signed"]; XTB["855"] -= r["Signed"]
for b in BANKX: XTB[b["xero_code"]] += b["amount"]; XTB["855"] -= b["amount"]
TIE = [dict(code=c, name=n, qbo_adjusted=c2(byx.get(c, 0.0)), xero_after_import=c2(XTB[c]), diff=c2(XTB[c] - byx.get(c, 0.0))) for c, n, t, x in XC]
w("tb_tieout.csv", TIE)
# ------------------------------------------------------------------ 10. answer-key comparison
T = lambda p: rd("truth/" + p)
tm = {r["qbo"]: r for r in T("truth_account_map.csv")}
acc_ok = sum(1 for m in AMAP if tm[m["qbo_account"]]["xero"] == m["xero_code"])
tax_true = {k for k, v in tm.items() if v["tax_fix"]}; tax_found = {m["qbo_account"] for m in AMAP if m["tax_change"] == "Yes"}
dups = T("truth_duplicates.csv"); dup_ok = sum(1 for d in dups if find(d["dup"]) == find(d["keep"]))
both = T("truth_both.csv"); both_ok = sum(1 for b in both if find(b["customer"]) == find(b["supplier"]))
look = T("truth_lookalikes.csv"); look_ok = sum(1 for l in look if find(l["a"]) != find(l["b"]))
true_pairs = {frozenset((d["dup"], d["keep"])) for d in dups} | {frozenset((b["customer"], b["supplier"])) for b in both}
merged = [c for c in CMAP if c["action"] != "Keep"]; false_merge = sum(1 for c in merged if frozenset((c["qbo_id"], c["xero_key"])) not in true_pairs)
ufd = T("truth_uf_double.csv"); uf_found = {u["txn"] for u in UFX if u["bank_feed_txn"]}; uf_true = {u["txn"] for u in ufd}
unc = {r["txn"]: r for r in T("truth_uncategorised.csv")}; unc_ok = sum(1 for u in UNC if unc[u["txn"]]["account"] == u["proposed_account"])
E = dict(accounts=dict(items=len(AMAP), agree=acc_ok), tax=dict(true=len(tax_true), found=len(tax_found & tax_true), false=len(tax_found - tax_true)),
         duplicates=dict(items=len(dups), found=dup_ok, false_merges=false_merge), both=dict(items=len(both), found=both_ok), lookalikes=dict(items=len(look), kept_apart=look_ok, abn_blocks=BLOCK),
         uf_double=dict(true=len(uf_true), found=len(uf_found & uf_true), false=len(uf_found - uf_true)), uncategorised=dict(items=len(UNC), agree=unc_ok),
         rollforward_diffs=sum(1 for r in ROLL if abs(r["diff"]) >= .005), tie_diffs=sum(1 for t in TIE if abs(t["diff"]) >= .005), ar=ar_tot, ap=ap_tot,
         contacts_in=len(CT), contacts_out=len(XCON), sum_conversion=c2(sum(r["conversion_balance"] for r in CBAL)), journals=len(J), uf_open=len(UFX))
json.dump(E, open(os.path.join(RES, "engine_test.json"), "w"), indent=1)
print(json.dumps(E))
# ------------------------------------------------------------------ steer22: 24 months of history -> Xero comparatives
# Oct 2024 - Jun 2026 go in as one manual journal per month (P&L lines, offset to 840 Historical Adjustment), and 840 is cleared to
# Retained Earnings at each 30 June, so the balance sheet at the conversion date is unchanged. Jul-Sep 2026 reach Xero through the conversion balances.
import calendar
PLM = rd("data/qbo_profit_and_loss_by_month_oct2024-sep2026.csv"); MCOLS = [k for k in PLM[0] if k != "Account"]
glm = defaultdict(float)
for g in GL:
    if g["Date"] > "2026-06-30": glm[(g["Account"], g["Date"][:7])] += float(g["Amount"])
tie3 = [(r["Account"], k, c2(float(r[k]) - glm[(r["Account"], k)])) for r in PLM for k in MCOLS if k >= "2026-07"]
HJ = []; jn = 0
for k in MCOLS:
    if k >= "2026-07": continue
    y, m = map(int, k.split("-")); dte = dt.date(y, m, calendar.monthrange(y, m)[1]).isoformat(); jn += 1; ref = f"H{jn:02d}"
    lines = [(AM[r["Account"]]["xero_code"], r["Account"], c2(float(r[k]))) for r in PLM if abs(float(r[k])) >= .005]
    byc = defaultdict(float)
    for code, _, v in lines: byc[code] += v
    for code, v in sorted(byc.items()): HJ.append(dict(journal=ref, date=dte, code=code, account=XB[code]["name"], amount=c2(v), memo=f"Comparative: QBO P&L {k}"))
    HJ.append(dict(journal=ref, date=dte, code="840", account=XB["840"]["name"], amount=-c2(sum(byc.values())), memo=f"Offset for comparative {k}"))
    if m == 6:
        bal = c2(-sum(h["amount"] for h in HJ if h["code"] == "840")); jn += 1
        HJ.append(dict(journal=f"H{jn:02d}", date=dte, code="840", account=XB["840"]["name"], amount=bal, memo=f"Year-end: clear 840 for FY{str(y)[2:]}"))
        HJ.append(dict(journal=f"H{jn:02d}", date=dte, code="960", account=XB["960"]["name"], amount=-bal, memo=f"Year-end: FY{str(y)[2:]} comparatives to Retained Earnings"))
w("xero_import/xero_comparative_journals_oct2024-jun2026.csv", [{"*Narration": f"Comparatives {h['journal']}", "*Date": h["date"], "Description": h["memo"], "*AccountCode": h["code"], "*TaxRate": "BAS Excluded", "*Amount": h["amount"]} for h in HJ])
w("history_journals.csv", HJ)
jb = defaultdict(float)
for h in HJ: jb[h["journal"]] += h["amount"]
H840 = {k: c2(sum(h["amount"] for h in HJ if h["code"] == "840" and h["date"] <= k)) for k in ("2025-06-30", "2026-06-30")}
HIST = dict(months=len(MCOLS), comparative_months=sum(1 for k in MCOLS if k < "2026-07"), accounts=len(PLM), journals=len(jb), journal_lines=len(HJ),
            unbalanced=sum(1 for v in jb.values() if abs(v) >= .005), gl_months_tied=len({k for _, k, _ in tie3}), gl_tie_diffs=sum(1 for *_, d in tie3 if abs(d) >= .005), h840=H840,
            fy25_profit=c2(-sum(float(r[k]) for r in PLM for k in MCOLS if "2024-07" <= k <= "2025-06")), fy26_profit=c2(-sum(float(r[k]) for r in PLM for k in MCOLS if "2025-07" <= k <= "2026-06")))
json.dump(HIST, open(os.path.join(RES, "history_check.json"), "w"), indent=1); print("history", HIST)