"""ServiceM8 setup engine for the sample plumbing business. Reads data/, writes results/ and results/servicem8_import/, and tests itself against truth/.
Steps: client matching across Xero, the job sheet and the backflow register; job categories by rule; a 12-month billing check (jobs vs Xero invoices);
pricebook rebuild from the supplier's 2026 price file with a margin check; backflow recurring-job schedule; job templates from history; open jobs; go-live gates."""
import csv, os, re, json, datetime as dt, statistics as stx
from collections import defaultdict, Counter
HERE = os.path.dirname(os.path.abspath(__file__)); D = dt.date.fromisoformat
rd = lambda p: list(csv.DictReader(open(os.path.join(HERE, p))))
def W(p, head, rows):
    os.makedirs(os.path.dirname(os.path.join(HERE, p)), exist_ok=True)
    with open(os.path.join(HERE, p), "w", newline="") as f: w = csv.writer(f); w.writerow(head); w.writerows(rows)
XC = rd("data/xero_contacts_export.csv"); XI = rd("data/xero_invoices_export.csv"); JS = rd("data/job_sheet_oct25-sep26.csv"); BF = rd("data/backflow_register.csv")
PLc = rd("data/current_price_list.csv"); SUP = rd("data/supplier_price_file_2026.csv"); STAFF = rd("data/staff.csv")
GO = dt.date(2026, 10, 12)          # go-live Monday; exports taken Friday 9 Oct
RATE = dict(std=145.0, ah=220.0, co=99.0, coah=180.0)
# ================================================================ 1 clients
BIZ = r"\b(pty|ltd|property|realty|rentals|leasing|strata|homes|constructions|building|renovations|developments|cafe|dental|physio|bakery|childcare|gym|medical|pharmacy|vet|studio|bar|restaurant|laundromat|pilates|florist|group|co|partners|services|management)\b"
ABR = {"st": "street", "rd": "road", "ave": "avenue", "pde": "parade", "gr": "grove", "ct": "court", "cres": "crescent"}
def nname(s):
    s = s.strip().lower()
    if "," in s: a, b = s.split(",", 1); s = b.strip() + " " + a.strip()
    s = re.sub(r"\bpty\.?\s*ltd\.?|\bp/l\b", "", s); s = re.sub(r"[^a-z0-9 ]", " ", s); return re.sub(r"\s+", " ", s).strip()
def nstreet(s, sub):
    w = re.sub(r"[^a-z0-9 ]", " ", s.lower()).split(); w = [ABR.get(x, x) for x in w]; return " ".join(w) + "|" + sub.strip().lower()
digits = lambda p: re.sub(r"\D", "", p or "")
REC = []
for x in XC: REC.append(dict(src="Xero contacts", rid=x["ContactID"], name=x["ContactName"], email=x["EmailAddress"].strip().lower(), phone=digits(x["PhoneNumber"]), addr=nstreet(x["POAddressLine1"], x["POCity"]),
                             street=x["POAddressLine1"], suburb=x["POCity"], pc=x["POPostalCode"]))
for b in BF: REC.append(dict(src="Backflow register", rid=b["Device ID"], name=b["Owner (as written)"], email="", phone=digits(b["Owner phone"]), addr="", street=b["Site address"], suburb=b["Suburb"], pc=b["Postcode"]))
for j in JS: REC.append(dict(src="Job sheet", rid=j["Job #"], name=j["Billing name"], email=j["Billing email"].strip().lower(), phone=digits(j["Billing phone"]), addr=nstreet(j["Site address"], j["Suburb"]) if not re.search(BIZ, j["Billing name"].lower()) else "",
                             street=j["Site address"], suburb=j["Suburb"], pc=j["Postcode"], site=nstreet(j["Site address"], j["Suburb"])))
par = list(range(len(REC)))
def f(i):
    while par[i] != i: par[i] = par[par[i]]; i = par[i]
    return i
RULE = {}
def u(a, b, why):
    ra, rb = f(a), f(b)
    if ra != rb: par[rb] = ra; RULE[(min(a, b), max(a, b))] = why
for k, why in (("email", "Same email"), ("phone", "Same phone")):
    first = {}
    for i, r in enumerate(REC):
        v = r[k]
        if v: (u(first[v], i, why) if v in first else first.__setitem__(v, i))
first = {}
for i, r in enumerate(REC):          # same normalised name at the same address (residential home = job site)
    n = nname(r["name"]); key = (n, r["addr"]) if r["addr"] else None
    if key: (u(first[key], i, "Same name and address") if key in first else first.__setitem__(key, i))
first = {}
for i, r in enumerate(REC):          # business names: same normalised name is enough
    n = nname(r["name"])
    if re.search(BIZ, r["name"].lower()) or len(n.split()) >= 3: (u(first[n], i, "Same business name") if n in first else first.__setitem__(n, i))
by_addr = defaultdict(list)
for i, r in enumerate(REC):          # "J Smith" at the address where "John Smith" lives
    if r["addr"]: by_addr[r["addr"]].append(i)
for a, ix in by_addr.items():
    for i in ix:
        n = nname(REC[i]["name"]).split()
        if len(n) == 2 and len(n[0]) == 1:
            for k in ix:
                m = nname(REC[k]["name"]).split()
                if len(m) == 2 and len(m[0]) > 1 and m[1] == n[1] and m[0][0] == n[0]: u(k, i, "Initial, surname and address"); break
cl = defaultdict(list)
for i in range(len(REC)): cl[f(i)].append(i)
def best_name(ix):
    xs = [REC[i]["name"].strip() for i in ix if REC[i]["src"] == "Xero contacts"] or [REC[i]["name"].strip() for i in ix]
    ok = [n for n in xs if "," not in n and not n.isupper() and not n.islower() and "  " not in n and not re.match(r"^[A-Z] ", n)]
    c = Counter(ok or xs).most_common(); top = max(c, key=lambda t: (t[1], "Pty Ltd" in t[0]))
    return top[0]
CLIENTS = []; rid2c = {}
for root, ix in sorted(cl.items(), key=lambda t: min(t[1])):
    cid = f"C{len(CLIENTS)+1:04d}"; nm = best_name(ix)
    xr = [REC[i] for i in ix if REC[i]["src"] == "Xero contacts"]; pr = xr[0] if xr else REC[ix[0]]
    em = Counter(REC[i]["email"] for i in ix if REC[i]["email"]).most_common(1); ph = Counter(REC[i]["phone"] for i in ix if REC[i]["phone"]).most_common(1)
    sites = sorted({(REC[i]["street"], REC[i]["suburb"], REC[i]["pc"]) for i in ix if REC[i]["src"] != "Xero contacts"} - {(pr["street"], pr["suburb"], pr["pc"])})
    kind = "Property manager" if re.search(r"property|realty|rentals|leasing|strata", nm.lower()) else "Builder" if re.search(r"homes|constructions|building group|renovations|developments", nm.lower()) else \
           "Commercial" if "pty ltd" in nm.lower() else "Residential"
    p = ph[0][0] if ph else ""; pf = f"{p[:4]} {p[4:7]} {p[7:]}" if len(p) == 10 else p
    CLIENTS.append(dict(id=cid, name=nm, email=em[0][0] if em else "", phone=pf, street=pr["street"], suburb=pr["suburb"], pc=pr["pc"], kind=kind, records=len(ix),
                        xero=sum(1 for i in ix if REC[i]["src"] == "Xero contacts"), jobs=sum(1 for i in ix if REC[i]["src"] == "Job sheet"), devices=sum(1 for i in ix if REC[i]["src"] == "Backflow register"),
                        sites=sites, xero_ids=[REC[i]["rid"] for i in ix if REC[i]["src"] == "Xero contacts"]))
    for i in ix: rid2c[REC[i]["rid"]] = cid
C = {c["id"]: c for c in CLIENTS}
dupx = [c for c in CLIENTS if c["xero"] > 1]
print("records", len(REC), "clients", len(CLIENTS), "xero dup groups", len(dupx), "extra xero records", sum(c["xero"] - 1 for c in dupx))
# ================================================================ 2 job categories
CATR = [("Backflow testing", r"backflow|testable device"), ("Inspection and quotes", r"quote|cctv|inspect|pre-purchase"), ("Roof and stormwater", r"gutter|downpipe|stormwater|roof|box gutter"), ("Gas fitting", r"\bgas\b(?!.*\bhws\b)|cooktop|\bbbq\b|heater"), ("Hot water", r"\bhws\b|hot water|\bhw\b|tempering|relief valve|pilot"),
        ("Blocked drains", r"block|drain|sewer|choke|roots|\bjet\b"), ("Burst and leaking pipes", r"burst|leak under|leak in wall|water meter|water leak"),
        ("Bathroom and kitchen", r"reno|rough-in|fit-off|relocate|laundry tub|dishwasher"), ("Leaking taps and toilets", r"\btap|toilet|cistern|mixer|\bwasher"),
        ("Maintenance (property manager)", r"\bpm\b|tenant|routine plumbing check")]
def cat_of(desc):
    d = desc.lower()
    if re.search(r"\bpm\b|tenant", d): return "Maintenance (property manager)", "PM keyword"
    for c, rx in CATR:
        if re.search(rx, d): return c, rx
    return "Review", ""
for j in JS:
    j["cat"], j["rule"] = cat_of(j["Job description"]); j["cid"] = rid2c[j["Job #"]]; j["d"] = D(j["Date"])
    h = int(j["Start"][:2]); j["ah"] = h >= 18 or h < 7 or j["d"].weekday() >= 5
# ================================================================ 3 billing check
INV = {}
for l in XI:
    i = INV.setdefault(l["InvoiceNumber"], dict(no=l["InvoiceNumber"], cid=rid2c[l["ContactID"]], xid=l["ContactID"], date=D(l["InvoiceDate"]), ref=l["Reference"], lines=[], status=l["Status"]))
    i["lines"].append((l["Description"], float(l["Quantity"]), float(l["UnitAmount"]), l["AccountCode"]))
for i in INV.values(): i["total"] = round(sum(q * p for _, q, p, _ in i["lines"]), 2); i["sig"] = tuple(sorted((d_, q, p) for d_, q, p, _ in i["lines"]))
done = [j for j in JS if j["Status"] == "Done"]; jd = {j["Job #"]: j for j in JS}
match = {}; used = set(); BC = []
for i in sorted(INV.values(), key=lambda i: (i["date"], i["no"])):
    if i["ref"] and i["ref"] in jd:
        if i["ref"] in match: BC.append((i["no"], i["ref"], "Invoiced twice (same job reference)")); used.add(i["no"]); continue
        match[i["ref"]] = (i["no"], "Reference"); used.add(i["no"])
# invoices without a reference that copy an already matched invoice for the same client within 30 days
byc = defaultdict(list)
for i in INV.values(): byc[i["cid"]].append(i)
inv_of_job = {}
for jn, (no, _) in match.items(): inv_of_job[jn] = no
for i in sorted(INV.values(), key=lambda i: (i["date"], i["no"])):
    if i["no"] in used or i["ref"]: continue
    cands = sorted([j for j in done if j["cid"] == i["cid"] and j["Job #"] not in match and 0 <= (i["date"] - j["d"]).days <= 14], key=lambda j: ((i["date"] - j["d"]).days, j["Job #"]))
    twin = [k for k in byc[i["cid"]] if k["no"] != i["no"] and k["no"] in used and k["sig"] == i["sig"] and 0 < (i["date"] - k["date"]).days <= 30]
    hit = None
    lab = [q for d_, q, p, _ in i["lines"] if d_.startswith("Labour")]; mat = sum(q * p for d_, q, p, _ in i["lines"] if d_ == "Materials")
    for j in cands:   # the job whose hours and materials fit the invoice lines
        if (not lab or abs(lab[0] - float(j["Hours"])) < .01) and abs(mat - float(j["Materials cost"] or 0)) < .01: hit = j; break
    if hit is None and twin: BC.append((i["no"], next(jn for jn, no in inv_of_job.items() if no == twin[0]["no"]), "Invoiced twice (copy of an earlier invoice)")); used.add(i["no"]); continue
    if hit is None and cands and not [q for d_, q, p, _ in i["lines"] if d_.startswith("Labour")]: hit = cands[0]
    if hit is not None: match[hit["Job #"]] = (i["no"], "Client, date and hours"); inv_of_job[hit["Job #"]] = i["no"]; used.add(i["no"])
unused = [i for i in INV.values() if i["no"] not in used]
nk = lambda s: re.sub(r"\s+", " ", s.strip().lower())
NDEV = Counter((nk(b["Site address"]), nk(b["Suburb"])) for b in BF)
CHECK = []
for j in done:
    m = match.get(j["Job #"]); i = INV[m[0]] if m else None
    exp_rate, exp_co = (RATE["ah"], RATE["coah"]) if j["ah"] else (RATE["std"], RATE["co"])
    hrs = float(j["Hours"]); mats = float(j["Materials cost"] or 0)
    if j["cat"] == "Backflow testing": est = round(165.0 * max(1, NDEV.get((nk(j["Site address"]), nk(j["Suburb"])), 0)) + mats, 2)
    else: est = round(hrs * exp_rate + exp_co + mats, 2)
    if not i: st, gap = "Not invoiced", est
    else:
        lab = [(d_, q, p) for d_, q, p, _ in i["lines"] if d_.startswith("Labour")]
        if j["ah"] and lab and "after hours" not in lab[0][0]: st, gap = "After-hours job billed at standard rate", round(hrs * (RATE["ah"] - RATE["std"]) + RATE["coah"] - RATE["co"], 2)
        else: st, gap = "OK", 0.0
    CHECK.append(dict(job=j["Job #"], date=j["d"], cid=j["cid"], client=C[j["cid"]]["name"], cat=j["cat"], tech=j["Assigned"], hours=hrs, ah=int(j["ah"]), inv=i["no"] if i else "", how=m[1] if m else "",
                      inv_total=i["total"] if i else 0.0, est=est, status=st, gap=gap))
for no, jn, why in BC:
    CHECK.append(dict(job=jn, date=jd[jn]["d"], cid=jd[jn]["cid"], client=C[jd[jn]["cid"]]["name"], cat=jd[jn]["cat"], tech=jd[jn]["Assigned"], hours=float(jd[jn]["Hours"]), ah=int(jd[jn]["ah"]), inv=no, how="Duplicate",
                      inv_total=INV[no]["total"], est=0.0, status=why, gap=-INV[no]["total"]))
sc = Counter(r["status"] for r in CHECK); print("billing", dict(sc), "unused invoices", len(unused))
# ================================================================ 4 pricebook
sup = {s["Supplier code"]: s for s in SUP}
def tier(cost): return (1.0, .42) if cost < 20 else (.6, .32) if cost < 100 else (.4, .24) if cost < 500 else (.25, .17)
def nm_key(s): return re.sub(r"\s+", " ", s.replace("*", "").replace("OLD ", "").strip().lower())
PB = []; seen = {}
for p in PLc:
    code = p["Item code"]; s = sup.get(code)
    new_cost = float(s["Trade price ex GST"]) if s else float(p["Cost"]); old_cost = float(p["Cost"]); sell = float(p["Sell (ex GST)"])
    mk, floor = tier(new_cost)
    margin = round((sell - new_cost) / sell, 4) if sell else 0
    prop = round(round(new_cost * (1 + mk) * 2) / 2, 2) if code else sell
    st = "Service (fixed price)" if not code else "Discontinued by supplier" if s and s["Status"] == "Discontinued" else "Below margin floor" if margin < floor else "OK"
    PB.append(dict(code=code, name=p["Item name"], group=p["Group"], old_cost=old_cost, new_cost=new_cost, sell=sell, margin=margin, floor=floor if code else 0, markup=mk if code else 0, proposed=prop, status=st,
                   supplier_desc=s["Description"] if s else "", updated=p["Last updated"]))
# duplicates: same supplier code twice; keep the row whose name matches the supplier description
byc_ = defaultdict(list)
for r in PB:
    if r["code"]: byc_[r["code"]].append(r)
for code, rs in byc_.items():
    if len(rs) > 1:
        keep = min(rs, key=lambda r: (nm_key(r["name"]) != r["supplier_desc"].lower(), "  " in r["name"], r["name"].islower(), r["name"].startswith("OLD "), "*" in r["name"]))
        for r in rs:
            if r is not keep: r["status"] = "Duplicate (remove)"; r["dup_of"] = code
for r in PB:
    r["final_price"] = r["proposed"] if r["status"] in ("Below margin floor",) else (r["sell"] if r["status"] in ("OK", "Service (fixed price)") else None)
    r["xero_account"] = "200" if r["group"] == "Services" else "210"
LAB = [("LAB-STD", "Labour per hour", 145.0, "200"), ("LAB-AH", "Labour per hour (after hours)", 220.0, "200"), ("CO-STD", "Call-out fee", 99.0, "200"), ("CO-AH", "Call-out fee (after hours)", 180.0, "200")]
pbc = Counter(r["status"] for r in PB); print("pricebook", dict(pbc))
# ================================================================ 5 backflow schedule
bf_jobs = defaultdict(list)
for j in JS:
    if j["Status"] == "Done" and j["cat"] == "Backflow testing": bf_jobs[nstreet(j["Site address"], j["Suburb"])].append(j["d"])
SITES = defaultdict(list)
for b in BF: SITES[nstreet(b["Site address"], b["Suburb"])].append(b)
testers = [s["Name"] for s in STAFF if s["Backflow accredited"] == "Y"]
BFS = []
for k, (site, devs) in enumerate(sorted(SITES.items())):
    last_reg = max(D(b["Last test date"]) for b in devs); last_js = max(bf_jobs.get(site, [dt.date(2000, 1, 1)]))
    last = max(last_reg, last_js); due = last + dt.timedelta(days=365)
    st = "Overdue" if due < GO else "Due Oct-Dec 2026" if due < dt.date(2027, 1, 1) else "Due 2027"
    first = max(due, GO + dt.timedelta(days=(k % 10)))   # overdue sites booked over the first two weeks
    for b in devs:
        BFS.append(dict(dev=b["Device ID"], site=b["Site address"], suburb=b["Suburb"], cid=rid2c[b["Device ID"]], client=C[rid2c[b["Device ID"]]]["name"], typ=b["Device type"], size=b["Size"], reg_last=D(b["Last test date"]),
                        js_last=last_js if last_js.year > 2000 else "", last=last, due=due, status=st, updated=int(last_js > last_reg), first=first, tester=testers[k % len(testers)], devices_at_site=len(devs)))
bsc = Counter(b["status"] for b in BFS); print("backflow", dict(bsc), "register dates updated", sum(b["updated"] for b in BFS), "sites", len(SITES))
# ================================================================ 6 job templates from history
CHK = {r["job"]: r for r in CHECK if r["how"] != "Duplicate"}
TPL = []
FORMS = {"Backflow testing": "Backflow test report", "Gas fitting": "Gas compliance checklist", "Hot water": "Hot water install checklist", "Burst and leaking pipes": "Water damage photos and notes",
         "Bathroom and kitchen": "Pre-start safety (SWMS)", "Roof and stormwater": "Working at heights pre-start", "Blocked drains": "Drain clear report", "Maintenance (property manager)": "Tenant attendance report",
         "Leaking taps and toilets": "Job completion checklist", "Inspection and quotes": "Site inspection notes"}
SUBT = {"Blocked drains": [("Blocked drain: jet and clear", r"jet|sewer|choke|roots"), ("Blocked drain or toilet: clear", r"")],
        "Hot water": [("Hot water: replace unit", r"replace hot water unit"), ("Hot water: valves and tempering", r"tempering|relief valve"), ("Hot water: fault-find and repair", r"")],
        "Leaking taps and toilets": [("Toilet suite: replace", r"replace toilet suite"), ("Toilet and cistern: repair", r"toilet|cistern"), ("Shower mixer: repair", r"shower mixer"), ("Tap and washer: repair", r"")],
        "Burst and leaking pipes": [("Burst or leaking pipe: find and repair", r"")],
        "Gas fitting": [("Gas leak: make safe", r"gas leak"), ("Gas appliance: install or connect", r"install|connect"), ("Gas compliance check", r"compliance"), ("Gas heater: service or repair", r"")],
        "Backflow testing": [("Backflow: annual test", r"")],
        "Bathroom and kitchen": [("Renovation: rough-in", r"rough-in|reno"), ("Renovation: fit-off", r"fit-off"), ("Fixture or appliance: connect", r"")],
        "Roof and stormwater": [("Gutter and downpipe", r"gutter|downpipe"), ("Stormwater pit: clear", r"stormwater"), ("Roof or box gutter leak", r"")],
        "Maintenance (property manager)": [("Tenant leak: attend", r"leak"), ("Property manager work order", r"")],
        "Inspection and quotes": [("Quote visit", r"quote"), ("CCTV or pre-purchase inspection", r"")]}
def tpl_of(cat, desc):
    for name, rx in SUBT.get(cat, []):
        if not rx or re.search(rx, desc.lower()): return name
    return ""
for j in JS: j["tpl"] = tpl_of(j["cat"], j["Job description"])
for cat in FORMS:
  for tname, _ in SUBT[cat]:
    js = [j for j in JS if j["tpl"] == tname]; dn = [j for j in js if j["Status"] == "Done"]
    vals = [INV[CHK[j["Job #"]]["inv"]]["total"] for j in dn if CHK.get(j["Job #"]) and CHK[j["Job #"]]["inv"]]
    TPL.append(dict(tpl=tname, cat=cat, jobs=len(js), done=len(dn), per_month=round(len(js) / 12, 1), median_hours=stx.median([float(j["Hours"]) for j in dn]) if dn else 0, ah_share=round(sum(j["ah"] for j in dn) / max(len(dn), 1), 3),
                    avg_invoice=round(stx.mean(vals), 2) if vals else 0, form=FORMS[cat], badge={"Maintenance (property manager)": "Property manager", "Backflow testing": "Backflow"}.get(cat, "After hours" if cat in ("Burst and leaking pipes", "Blocked drains") else ""),
                    licence="Gas fitting" if cat == "Gas fitting" else "Backflow accredited" if cat == "Backflow testing" else "Licensed plumber" if cat in ("Hot water", "Bathroom and kitchen") else "Any"))
# ================================================================ 7 open jobs at go-live
OPEN = []
for j in JS:
    stm = {"Booked": "Work Order", "Quote sent": "Quote", "Quote accepted": "Work Order"}.get(j["Status"])
    if not stm or (j["Status"] != "Booked" and (GO - j["d"]).days > 60): continue
    if j["Status"] == "Quote accepted" and any(k["Status"] == "Done" and k["cid"] == j["cid"] and k["d"] >= j["d"] for k in JS): continue
    OPEN.append(dict(job=j["Job #"], date=j["d"], cid=j["cid"], client=C[j["cid"]]["name"], site=f'{j["Site address"]}, {j["Suburb"]} {j["Postcode"]}', contact=j["Site contact"], desc=j["Job description"], cat=j["cat"], tech=j["Assigned"], status=stm, old=j["Status"]))
print("open jobs", Counter(o["status"] for o in OPEN))
# ================================================================ write results
W("results/client_matching.csv", ["source", "record_id", "name_as_written", "client_id", "client_name"], [[r["src"], r["rid"], r["name"], rid2c[r["rid"]], C[rid2c[r["rid"]]]["name"]] for r in REC])
W("results/clients.csv", ["client_id", "name", "kind", "email", "phone", "street", "suburb", "postcode", "records", "xero_records", "jobs", "devices", "job_sites", "xero_contact_ids"],
  [[c["id"], c["name"], c["kind"], c["email"], c["phone"], c["street"], c["suburb"], c["pc"], c["records"], c["xero"], c["jobs"], c["devices"], len(c["sites"]), " ".join(c["xero_ids"])] for c in CLIENTS])
W("results/xero_duplicates.csv", ["client_id", "keep_name", "xero_contact_ids", "rule"], [[c["id"], c["name"], " ".join(c["xero_ids"]), ""] for c in dupx])
W("results/job_sites.csv", ["client_id", "client", "site_address", "suburb", "postcode"], [[c["id"], c["name"], s[0], s[1], s[2]] for c in CLIENTS if c["kind"] in ("Property manager", "Builder", "Commercial") for s in c["sites"]])
W("results/jobs_categorised.csv", ["job", "date", "client_id", "description", "category", "rule", "status", "after_hours", "template"], [[j["Job #"], j["Date"], j["cid"], j["Job description"], j["cat"], j["rule"], j["Status"], int(j["ah"]), j["tpl"]] for j in JS])
W("results/billing_check.csv", ["job", "date", "client_id", "client", "category", "assigned", "hours", "after_hours", "invoice", "matched_by", "invoice_total", "expected_estimate", "status", "gap"],
  [[r["job"], r["date"], r["cid"], r["client"], r["cat"], r["tech"], r["hours"], r["ah"], r["inv"], r["how"], r["inv_total"], r["est"], r["status"], r["gap"]] for r in CHECK])
W("results/invoices.csv", ["invoice", "date", "client_id", "xero_contact", "reference", "total", "status", "used"], [[i["no"], i["date"], i["cid"], i["xid"], i["ref"], i["total"], i["status"], int(i["no"] in used)] for i in INV.values()])
W("results/pricebook.csv", ["code", "name", "group", "old_cost", "new_cost", "sell_now", "margin_now", "floor", "markup", "proposed_price", "status", "xero_account", "supplier_desc", "last_updated"],
  [[r["code"], r["name"], r["group"], r["old_cost"], r["new_cost"], r["sell"], r["margin"], r["floor"], r["markup"], r["proposed"], r["status"], r["xero_account"], r["supplier_desc"], r["updated"]] for r in PB])
W("results/backflow_schedule.csv", ["device", "site", "suburb", "client_id", "client", "type", "size", "register_last", "job_sheet_last", "last_test", "next_due", "status", "register_updated", "first_booking", "tester", "devices_at_site"],
  [[b["dev"], b["site"], b["suburb"], b["cid"], b["client"], b["typ"], b["size"], b["reg_last"], b["js_last"], b["last"], b["due"], b["status"], b["updated"], b["first"], b["tester"], b["devices_at_site"]] for b in BFS])
W("results/job_templates.csv", ["template", "category", "jobs", "done", "per_month", "median_hours", "after_hours_share", "avg_invoice", "form", "badge", "licence"], [[t[k] for k in ("tpl", "cat", "jobs", "done", "per_month", "median_hours", "ah_share", "avg_invoice", "form", "badge", "licence")] for t in TPL])
W("results/open_jobs.csv", ["job", "date", "client_id", "client", "site", "site_contact", "description", "category", "assigned", "servicem8_status", "old_status"], [[o[k] for k in ("job", "date", "cid", "client", "site", "contact", "desc", "cat", "tech", "status", "old")] for o in OPEN])
# ServiceM8 import files (column names to be mapped to the current import template before loading)
W("results/servicem8_import/clients.csv", ["Company Name", "Contact First", "Contact Last", "Email", "Mobile", "Billing Address", "Suburb", "State", "Postcode", "Badge", "Xero Contact IDs"],
  [[c["name"], c["name"].split()[0] if c["kind"] == "Residential" else "", " ".join(c["name"].split()[1:]) if c["kind"] == "Residential" else "", c["email"], c["phone"], c["street"], c["suburb"], "VIC", c["pc"],
    "Property manager" if c["kind"] == "Property manager" else "", " ".join(c["xero_ids"])] for c in CLIENTS])
W("results/servicem8_import/materials.csv", ["Item Number", "Name", "Cost", "Price", "Tax", "Xero Account"],
  [[r["code"], r["name"].strip(), r["new_cost"], r["final_price"], "GST on Income", r["xero_account"]] for r in PB if r["final_price"] is not None] + [[a, b, 0, c, "GST on Income", d] for a, b, c, d in LAB])
W("results/servicem8_import/recurring_backflow.csv", ["Client", "Job Address", "Category", "Description", "First Date", "Repeat", "Assigned", "Devices"],
  [[b["client"], f'{b["site"]}, {b["suburb"]}', "Backflow testing", f'Annual backflow test: {b["devices_at_site"]} device(s)', b["first"], "Every 12 months", b["tester"], b["devices_at_site"]] for b in {(x["site"], x["suburb"]): x for x in BFS}.values()])
W("results/servicem8_import/open_jobs.csv", ["Client", "Job Address", "Site Contact", "Status", "Category", "Description", "Date", "Assigned", "Old Job #"],
  [[o["client"], o["site"], o["contact"], o["status"], o["cat"], o["desc"], o["date"], o["tech"], o["job"]] for o in OPEN])
# ================================================================ tests against truth
TC = {r["record_id"]: r["true_client"] for r in rd("truth/clients.csv")}
grp = defaultdict(set); tgrp = defaultdict(set)
for r in REC: grp[rid2c[r["rid"]]].add(TC[r["rid"]]); tgrp[TC[r["rid"]]].add(rid2c[r["rid"]])
false_merge = sum(1 for v in grp.values() if len(v) > 1); split = sum(1 for v in tgrp.values() if len(v) > 1)
TJ = {r["job"]: r for r in rd("truth/jobs.csv")}
cat_ok = sum(1 for j in JS if j["cat"] == TJ[j["Job #"]]["category"]); review = sum(1 for j in JS if j["cat"] == "Review"); cat_wrong = len(JS) - cat_ok - review
fu = {r["job"] for r in CHECK if r["status"] == "Not invoiced"}; tu = {k for k, v in TJ.items() if v["unbilled"] == "1"}
fa = {r["job"] for r in CHECK if r["status"].startswith("After-hours")}; ta = {k for k, v in TJ.items() if v["after_hours_billed_standard"] == "1"}
fd = {r["job"] for r in CHECK if r["status"].startswith("Invoiced twice")}; td = {k for k, v in TJ.items() if v["invoiced_twice"] == "1"}
TP = rd("truth/pricebook.csv"); tdup = sum(1 for r in TP if r["duplicate_of"]); fdup = sum(1 for r in PB if r["status"] == "Duplicate (remove)")
dup_right = sum(1 for r, t in zip(PB, TP) if (r["status"] == "Duplicate (remove)") == bool(t["duplicate_of"]))
tdisc = sum(1 for r in TP if r["discontinued"] == "1"); fdisc = sum(1 for r in PB if r["status"] == "Discontinued by supplier")
T = dict(records=len(REC), true_clients=len({v for v in TC.values()}), clients=len(CLIENTS), false_merges=false_merge, split_clients=split,
         jobs=len(JS), cat_correct=cat_ok, cat_review=review, cat_wrong=cat_wrong,
         unbilled=dict(true=len(tu), found=len(fu & tu), false=len(fu - tu)), after_hours=dict(true=len(ta), found=len(fa & ta), false=len(fa - ta)), double=dict(true=len(td), found=len(fd & td), false=len(fd - td)),
         price_dups=dict(true=tdup, found=fdup, rows_right=dup_right, rows=len(PB)), discontinued=dict(true=tdisc, found=fdisc), unused_invoices=len(unused))
json.dump(T, open(os.path.join(HERE, "results/engine_test.json"), "w"), indent=1)
print(json.dumps(T))
if os.environ.get("DBG"):
    for k, v in grp.items():
        if len(v) > 1: print("FALSE MERGE", k, C[k]["name"], v, [ (r["src"], r["name"]) for r in REC if rid2c[r["rid"]] == k][:8])
    conf = Counter((TJ[j["Job #"]]["category"], j["cat"]) for j in JS if j["cat"] not in (TJ[j["Job #"]]["category"], "Review"))
    for (a, b), n in conf.most_common(12): print(n, a, "->", b, [j["Job description"] for j in JS if TJ[j["Job #"]]["category"] == a and j["cat"] == b][:4])
    for r, t in zip(PB, TP):
        if (r["status"] == "Duplicate (remove)") != bool(t["duplicate_of"]): print("DUP", r["code"], repr(r["name"]), r["status"], repr(t["name"]), t["duplicate_of"])
    print("missed unbilled", tu - fu, "false", fu - tu, "missed AH", list(ta - fa)[:5])