"""Migration engine: matches QuickBooks Online customers and recurring templates to Jobber clients, properties and jobs,
reconciles every job's rate, checks three months of billing from both systems, finds roster clashes, pulls access codes out of
notes, builds the Google Calendar events (.ics per cleaner), the Drive folder plan and the cutover waves, then tests itself
against the answer key in truth/. Reads data/ only; truth/ is used in the test section at the end."""
import csv, os, re, json, difflib, datetime as dt
from collections import defaultdict, Counter
HERE = os.path.dirname(os.path.abspath(__file__)); os.makedirs(os.path.join(HERE, "results/calendar"), exist_ok=True)
rd = lambda p: list(csv.DictReader(open(os.path.join(HERE, p))))
def W(name, rows, head=None):
    with open(os.path.join(HERE, "results", name), "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=head or list(rows[0].keys())); w.writeheader(); w.writerows(rows)
D = dt.date.fromisoformat
CL = rd("data/jobber_clients.csv"); PR = rd("data/jobber_properties.csv"); JB = rd("data/jobber_recurring_jobs.csv"); VI = rd("data/jobber_visits_aug_oct.csv")
JI = rd("data/jobber_invoices_aug_oct.csv"); TM = rd("data/jobber_team.csv"); QC = rd("data/qbo_customers.csv"); QT = rd("data/qbo_recurring_templates.csv"); QI = rd("data/qbo_invoices_aug_oct.csv")
CB = {c["client_id"]: c for c in CL}; PB = {p["property_id"]: p for p in PR}; JBB = {j["job_number"]: j for j in JB}; QCB = {q["qbo_id"]: q for q in QC}; TMB = {t["user_id"]: t for t in TM}
MONTHS = ["2026-08", "2026-09", "2026-10"]
# ---------------------------------------------------------------- 1 customers -> clients
def nname(s):
    s = s.lower().strip(); s = re.sub(r"^the\s+", "", s)
    s = re.sub(r"\s*(-\s*accounts|\(new\)|pty\.? ?ltd\.?|p/l)\s*", " ", s); s = re.sub(r"[^a-z0-9 ]", " ", s)
    return re.sub(r"\s+", " ", s).strip()
by_name = defaultdict(list)
for c in CL: by_name[nname(c["company_name"])].append(c["client_id"])
dom = lambda e: e.split("@")[-1].lower() if e else ""
cmap = []
for q in QC:
    if q["parent"]: continue
    n = nname(q["display_name"]); hit = by_name.get(n, []); rule = "N1 same name" if len(hit) == 1 else ""
    if not rule and q["email"]:
        cands = [c for c in CL if dom(c["email"]) == dom(q["email"])]
        best = max(cands, key=lambda c: difflib.SequenceMatcher(None, nname(c["company_name"]), n).ratio(), default=None)
        if best and difflib.SequenceMatcher(None, nname(best["company_name"]), n).ratio() >= .85: hit = [best["client_id"]]; rule = "E1 same email, name 0.85+"
    if not rule:
        sc = sorted(((difflib.SequenceMatcher(None, k, n).ratio(), v[0]) for k, v in by_name.items()), reverse=True)
        if sc and sc[0][0] >= .93 and (len(sc) < 2 or sc[0][0] - sc[1][0] > .04): hit = [sc[0][1]]; rule = "F1 name 0.93+, clear winner"
    cmap.append(dict(qbo_id=q["qbo_id"], display_name=q["display_name"], email=q["email"], client_id=hit[0] if rule else "", client=CB[hit[0]]["company_name"] if rule else "", rule=rule or "No match"))
first = {}
for r in sorted(cmap, key=lambda r: r["qbo_id"]):
    if not r["client_id"]: r["action"] = "Review"; continue
    if r["client_id"] in first: r["action"] = f"Merge into {first[r['client_id']]}"
    else: first[r["client_id"]] = r["qbo_id"]; r["action"] = "Keep"
Q2C = {r["qbo_id"]: r["client_id"] for r in cmap}
# ---------------------------------------------------------------- 2 sub-customers -> properties
AB = {"st": "street", "rd": "road", "pde": "parade", "ave": "avenue", "hwy": "highway"}
def naddr(s):
    s = re.sub(r"[^a-z0-9/ ]", " ", s.lower()); return " ".join(AB.get(w, w) for w in s.split())
props_by_client = defaultdict(list)
for p in PR: props_by_client[p["client_id"]].append(p)
pmap = []
for q in QC:
    if not q["parent"]: continue
    cid = Q2C.get(q["parent"], ""); a = naddr(q["display_name"]); hit = [p for p in props_by_client[cid] if naddr(f"{p['street']} {p['city']}") == a]
    pmap.append(dict(qbo_id=q["qbo_id"], display_name=q["display_name"], parent=q["parent"], client_id=cid, property_id=hit[0]["property_id"] if len(hit) == 1 else "", rule="Address, normalised" if len(hit) == 1 else "No match"))
S2P = {r["qbo_id"]: r["property_id"] for r in pmap}
def policy_month(j):  # contract basis per month (4-weekly: per visit)
    r = float(j["unit_price"])
    return round(r * int(j["visits_per_week"]) * 52 / 12, 2) if j["frequency"] == "Weekly" else r
# ---------------------------------------------------------------- 3 templates -> jobs
jobs_at = defaultdict(list)
for j in JB: jobs_at[(j["property_id"], j["title"].lower())].append(j)
tmap = []
for t in QT:
    pid = S2P.get(t["customer_id"], ""); svc = t["description"].split(" at ")[0].strip().lower(); js = jobs_at.get((pid, svc), [])
    rule = "Property + service"
    if len(js) > 1:  # an ended job and its replacement at one address: the template belongs to the job whose monthly amount it equals
        rule = "Property + service + amount"; amt = float(t["amount"])
        js = sorted(js, key=lambda j: (abs(policy_month(j) - amt) > .5, j["status"] != "Active"))
    pick = js[0] if js else None
    tmap.append(dict(template_id=t["template_id"], template_name=t["template_name"], property_id=pid, service=svc, job=pick["job_number"] if pick else "", rule=rule if pick else ("Not in Jobber" if pid else "Property not matched")))
T2J = {r["template_id"]: r["job"] for r in tmap}; QTB = {t["template_id"]: t for t in QT}
tmpl_of = defaultdict(list)
for t in QT:
    if T2J[t["template_id"]] and t["active"] == "true": tmpl_of[T2J[t["template_id"]]].append(t)
# ---------------------------------------------------------------- 4 rate reconciliation
def runs_in(j, ym):
    y, m = map(int, ym.split("-")); a = D(j["first_visit"]); d = dt.date(y, m, 1); n = 0
    while d.month == m:
        if (d - a).days % 28 == 0: n += 1
        d += dt.timedelta(1)
    return n
rec = []
for j in JB:
    ts = tmpl_of.get(j["job_number"], []); auto = j["invoicing"].startswith("Auto"); jm = policy_month(j); qm = round(sum(float(t["amount"]) for t in ts), 2)
    first_t = float(ts[0]["amount"]) if ts else 0.0
    if j["status"] != "Active": st = "Inactive in Jobber - confirm" if ts else "Archived, no template"
    elif len(ts) >= 2: st = "Duplicate in QBO"
    elif auto and ts: st = "Double-billing risk"
    elif auto: st = "Billed in Jobber"
    elif not ts: st = "Not billed"
    elif j["frequency"] == "Weekly" and abs(first_t - float(j["unit_price"]) * int(j["visits_per_week"]) * 4) < .5: st = "Basis mismatch"
    elif abs(first_t - jm) >= .5: st = "Rate mismatch"
    else: st = "Matched"
    p = PB[j["property_id"]]; c = CB[p["client_id"]]
    rec.append(dict(job=j["job_number"], client=c["company_name"], property=f"{p['street']}, {p['city']}", service=j["title"], frequency=j["frequency"], visits_per_week=j["visits_per_week"], rate=j["unit_price"],
                    jobber_status=j["status"], jobber_invoicing="Auto" if auto else "Outside Jobber", jobber_monthly=jm if j["status"] == "Active" else 0, qbo_templates=" ".join(t["template_id"] for t in ts),
                    qbo_monthly=qm, difference=round(qm - (jm if j["status"] == "Active" else 0), 2) if (ts or j["status"] == "Active") else 0, status=st))
for t in QT:
    if not T2J[t["template_id"]]:
        pid = S2P.get(t["customer_id"], ""); p = PB.get(pid, {}); c = CB.get(p.get("client_id", ""), {})
        rec.append(dict(job="", client=c.get("company_name", ""), property=f"{p.get('street','')}, {p.get('city','')}", service=t["description"].split(" at ")[0], frequency=t["interval"], visits_per_week="", rate="",
                        jobber_status="Not in Jobber", jobber_invoicing="", jobber_monthly=0, qbo_templates=t["template_id"], qbo_monthly=float(t["amount"]), difference=float(t["amount"]), status="Missing in Jobber"))
W("rate_reconciliation.csv", rec)
# ---------------------------------------------------------------- 5 billing check Aug-Oct
jinv = defaultdict(float); qinv = defaultdict(float)
prev = lambda d: (D(d).replace(day=1) - dt.timedelta(1)).strftime("%Y-%m")
for i in JI: jinv[(i["job"], prev(i["date"]))] += float(i["amount"])
for i in QI: qinv[(T2J.get(i["template_id"]) or i["template_id"], i["date"][:7])] += float(i["amount"])
bill = []
for j in JB:
    for ym in MONTHS:
        exp = 0.0
        if j["status"] == "Active": exp = round(runs_in(j, ym) * float(j["unit_price"]), 2) if j["frequency"] == "Every 4 weeks" else policy_month(j)
        jb, qb = round(jinv[(j["job_number"], ym)], 2), round(qinv[(j["job_number"], ym)], 2)
        runs = runs_in(j, ym) if j["frequency"] == "Every 4 weeks" and j["status"] == "Active" else ""
        if exp or jb or qb: bill.append(dict(job=j["job_number"], month=ym, runs=runs, expected=exp, billed_jobber=jb, billed_qbo=qb, gap=round(jb + qb - exp, 2)))
for t in QT:
    if not T2J[t["template_id"]]:
        for ym in MONTHS:
            qb = round(qinv[(t["template_id"], ym)], 2)
            if qb: bill.append(dict(job=t["template_id"], month=ym, runs="", expected=0, billed_jobber=0, billed_qbo=qb, gap=qb))
W("billing_check.csv", bill)
# ---------------------------------------------------------------- 6 roster clashes
def iv(j):
    h, m = map(int, j["start_time"].split(":")); s = h * 60 + m; return s, s + int(float(j["duration_hours"]) * 60)
def wks(j):
    if j["frequency"] == "Weekly": return {0, 1, 2, 3}
    w = ((D(j["first_visit"]) - dt.date(2026, 6, 29)).days // 7) % 4
    return {w} if j["frequency"] == "Every 4 weeks" else {0}
slots = defaultdict(list)
for j in JB:
    if j["status"] != "Active": continue
    for d in j["days"].split():
        for w in wks(j): slots[(j["assigned_to"], d, w)].append(j)
clash = {}
for k, js in slots.items():
    js = sorted(js, key=lambda j: iv(j)[0])
    for a_i, a in enumerate(js):
        for b in js[a_i + 1:]:
            if iv(b)[0] < iv(a)[1]:
                key = tuple(sorted((a["job_number"], b["job_number"])))
                clash.setdefault(key, dict(job_a=key[0], job_b=key[1], cleaner=a["assigned_to"], day=k[1], a_window="%s-%02d:%02d" % (JBB[key[0]]["start_time"], *divmod(iv(JBB[key[0]])[1], 60)),
                                           b_window="%s-%02d:%02d" % (JBB[key[1]]["start_time"], *divmod(iv(JBB[key[1]])[1], 60)), overlap_min=min(iv(a)[1], iv(b)[1]) - iv(b)[0]))
CLASH = list(clash.values()); W("roster_clashes.csv", CLASH, ["job_a", "job_b", "cleaner", "day", "a_window", "b_window", "overlap_min"])
clash_jobs = {x for c in CLASH for x in (c["job_a"], c["job_b"])}
load = Counter()
for j in JB:
    if j["status"] == "Active": load[j["assigned_to"]] += float(j["duration_hours"]) * (int(j["visits_per_week"]) if j["frequency"] == "Weekly" else .25)
W("cleaner_load.csv", [dict(cleaner=t["user_id"], name=t["first_name"], region=t["region"], contracted_hours=t["contracted_hours"], rostered_hours=round(load[t["user_id"]], 2),
                            jobs=sum(1 for j in JB if j["status"] == "Active" and j["assigned_to"] == t["user_id"])) for t in TM])
# ---------------------------------------------------------------- 7 access notes
CODE = re.compile(r"(alarm|code|key ?safe|lockbox|keypad)[^0-9;]{0,24}(\d{4,6})", re.I)
acc = []
for p in PR:
    m = CODE.search(p["notes"]); red = CODE.sub(lambda x: x.group(0).replace(x.group(2), "[in 02 Site Access]"), p["notes"])
    acc.append(dict(property_id=p["property_id"], has_code=1 if m else 0, calendar_note=red, moved_to="02 Site Access (office only)" if m else ""))
W("access_notes.csv", acc); ACC = {a["property_id"]: a for a in acc}
# ---------------------------------------------------------------- 8 waves, gates, calendar, Drive
REC = {r["job"]: r for r in rec if r["job"]}
BLOCK = {"Duplicate in QBO": "G2", "Double-billing risk": "G1", "Not billed": "G3", "Rate mismatch": "G4", "Basis mismatch": "G4", "Inactive in Jobber - confirm": "G3"}
blocked_cleaner = {JBB[j]["assigned_to"] for j, r in REC.items() if r["status"] in BLOCK} | {JBB[j]["assigned_to"] for j in clash_jobs}
pilot = sorted([t["user_id"] for t in TM if t["user_id"] not in blocked_cleaner and t["region"] == "North" and int(t["user_id"][2:]) <= 52], key=lambda c: -load[c])[:2]
WAVES = [("Pilot", "2026-12-07"), ("Wave 1 North", "2027-01-11"), ("Wave 2 East", "2027-01-18"), ("Wave 3 South", "2027-01-25"), ("Wave 4 West", "2027-02-01")]
def wave_of(j):
    if j["assigned_to"] in pilot: return "Pilot"
    return {"North": "Wave 1 North", "East": "Wave 2 East", "South": "Wave 3 South", "West": "Wave 4 West"}[[k for k, v in REGION.items() if PB[j["property_id"]]["city"] in v][0]]
REGION = {"North": ["Preston", "Coburg", "Brunswick", "Thornbury", "Northcote", "Reservoir", "Heidelberg", "Essendon", "Bundoora", "Epping"],
          "East": ["Box Hill", "Doncaster", "Ringwood", "Camberwell", "Kew", "Hawthorn", "Blackburn", "Glen Waverley", "Burwood", "Mitcham"],
          "South": ["St Kilda", "Brighton", "Moorabbin", "Cheltenham", "Mentone", "Clayton", "Oakleigh", "Bentleigh", "Dandenong", "Frankston"],
          "West": ["Footscray", "Sunshine", "Williamstown", "Altona", "Werribee", "Point Cook", "Yarraville", "Maribyrnong", "Deer Park", "Laverton"]}
BYDAY = {"MO": 0, "TU": 1, "WE": 2, "TH": 3, "FR": 4}
cal = []; ics = defaultdict(list)
for j in JB:
    if j["status"] != "Active": continue
    wv = wave_of(j); w0 = D(dict(WAVES)[wv]); p = PB[j["property_id"]]; c = CB[p["client_id"]]
    if j["frequency"] == "Weekly": rr = f"FREQ=WEEKLY;BYDAY={','.join(j['days'].split())}"; d0 = w0 + dt.timedelta(BYDAY[j["days"].split()[0]])
    elif j["frequency"] == "Every 4 weeks":
        rr = f"FREQ=WEEKLY;INTERVAL=4;BYDAY={j['days']}"; d0 = D(j["first_visit"])
        while d0 < w0: d0 += dt.timedelta(28)
    else:
        rr = f"FREQ=MONTHLY;BYDAY=1{j['days']}"; d0 = w0
        while not (d0.weekday() == BYDAY[j["days"]] and d0.day <= 7): d0 += dt.timedelta(1)
    s, e = iv(j); folder = f"Clients/{c['company_name']}/{p['property_name']} - {p['city']}"
    title = f"{j['title']} - {c['company_name']} ({p['city']})"
    desc = f"{j['title']}. Checklist: {folder}/01 Scope & Checklist. Notes: {ACC[p['property_id']]['calendar_note'] or '-'}. Access details: {folder}/02 Site Access (office will share)."
    cal.append(dict(job=j["job_number"], wave=wv, calendar=f"{j['assigned_to']} {TMB[j['assigned_to']]['first_name']} roster", title=title, first_date=d0.isoformat(), start=j["start_time"], end="%02d:%02d" % divmod(e, 60),
                    rrule=rr, location=f"{p['street']}, {p['city']} VIC", folder=folder, description=desc, clash="Yes" if j["job_number"] in clash_jobs else ""))
    dts = lambda d, m: f"{d.strftime('%Y%m%d')}T{m // 60:02d}{m % 60:02d}00"
    ics[j["assigned_to"]].append(f"BEGIN:VEVENT\r\nUID:{j['job_number']}@roster\r\nDTSTART;TZID=Australia/Melbourne:{dts(d0, s)}\r\nDTEND;TZID=Australia/Melbourne:{dts(d0, e)}\r\nRRULE:{rr}\r\nSUMMARY:{title}\r\nLOCATION:{p['street']}\\, {p['city']} VIC\r\nDESCRIPTION:{desc.replace(',', chr(92) + ',')}\r\nEND:VEVENT")
W("calendar_build.csv", cal)
for k, evs in ics.items():
    open(os.path.join(HERE, "results/calendar", f"{k}.ics"), "w").write("BEGIN:VCALENDAR\r\nVERSION:2.0\r\nPRODID:-//sample build//roster//EN\r\n" + "\r\n".join(evs) + "\r\nEND:VCALENDAR\r\n")
SUBF = [("01 Scope & Checklist", "Viewer", "Editor", "-"), ("02 Site Access", "-", "Editor", "-"), ("03 Agreements", "-", "Editor", "Viewer"), ("04 Billing", "-", "Editor", "Editor"), ("05 Visit Reports", "Contributor (form uploads)", "Editor", "Viewer")]
drive = []
for c in CL:
    drive.append(dict(path=f"Clients/{c['company_name']}", level="Client", cleaner="-", office="Editor", bookkeeper="Viewer"))
    for p in props_by_client[c["client_id"]]:
        base = f"Clients/{c['company_name']}/{p['property_name']} - {p['city']}"; drive.append(dict(path=base, level="Location", cleaner="-", office="Editor", bookkeeper="-"))
        for n, cl_, of, bk in SUBF: drive.append(dict(path=f"{base}/{n}", level="Folder", cleaner=cl_, office=of, bookkeeper=bk))
W("drive_folders.csv", drive)
gates = []
GN = {"G1": "No location billed by both Jobber and QBO", "G2": "No duplicate QBO templates", "G3": "Every location billed once, and nothing billed for ended work", "G4": "Rates agreed (Jobber = QBO)", "G5": "No roster clashes", "G6": "No access codes in calendar text"}
for wv, d0 in WAVES:
    js = [j for j in JB if j["status"] == "Active" and wave_of(j) == wv]; ids = {j["job_number"] for j in js}
    inact = [j for j in JB if j["status"] != "Active" and REC[j["job_number"]]["status"] == "Inactive in Jobber - confirm" and wave_of(j) == wv]
    cnt = Counter(BLOCK[REC[j]["status"]] for j in ids if REC[j]["status"] in BLOCK); cnt["G3"] += len(inact)
    cnt["G5"] = sum(1 for c in CLASH if c["job_a"] in ids or c["job_b"] in ids)
    cnt["G6"] = sum(1 for cc in cal if cc["job"] in ids and CODE.search(cc["description"]))
    for g in ["G1", "G2", "G3", "G4", "G5", "G6"]:
        gates.append(dict(wave=wv, go_live=d0, gate=g, rule=GN[g], open_items=cnt[g], result="BLOCKED" if cnt[g] else "PASS"))
for r in rec:
    if r["job"]: r["wave"] = wave_of(JBB[r["job"]])
    else:
        pid = S2P.get(QTB[r["qbo_templates"]]["customer_id"], ""); r["wave"] = {"North": "Wave 1 North", "East": "Wave 2 East", "South": "Wave 3 South", "West": "Wave 4 West"}[[k for k, v in REGION.items() if PB[pid]["city"] in v][0]]
for c in CLASH: c["wave_a"] = wave_of(JBB[c["job_a"]]); c["wave_b"] = wave_of(JBB[c["job_b"]])
W("rate_reconciliation.csv", rec); W("roster_clashes.csv", CLASH)
for c in cal: c["code_in_text"] = 1 if CODE.search(c["description"] + c["title"] + c["location"]) else 0
W("calendar_build.csv", cal)
W("go_live_gates.csv", gates); W("customer_map.csv", cmap); W("property_map.csv", pmap); W("template_map.csv", tmap)
# ---------------------------------------------------------------- 9 regression test against the answer key
rt = lambda p: list(csv.DictReader(open(os.path.join(HERE, "truth", p))))
T = {}
tj = {r["jid"]: r["status"] for r in rt("truth_jobs.csv")}; got = {r["job"]: r["status"] for r in rec if r["job"]}
T["job_status"] = dict(items=len(tj), agree=sum(got.get(k) == v for k, v in tj.items()), confusion=dict(Counter(f"{v} -> {got.get(k)}" for k, v in tj.items() if got.get(k) != v)))
tc = {r["qbo_id"]: r["cid"] for r in rt("truth_customers.csv")}
T["customers"] = dict(items=len(tc), agree=sum(Q2C.get(k) == v for k, v in tc.items()), wrong_client=sum(1 for k, v in tc.items() if Q2C.get(k) and Q2C.get(k) != v))
tt = {r["template_id"]: r["jid"] for r in rt("truth_templates.csv")}
T["templates"] = dict(items=len(tt), agree=sum(T2J.get(k, "") == v for k, v in tt.items()))
tcode = {r["pid"]: int(r["has_code"]) for r in rt("truth_codes.csv")}
T["access_codes"] = dict(items=sum(tcode.values()), found=sum(1 for k, v in tcode.items() if v and ACC[k]["has_code"]), false_flags=sum(1 for k, v in tcode.items() if not v and ACC[k]["has_code"]))
tcl = {tuple(sorted((r["a"], r["b"]))) for r in rt("truth_clashes.csv")}
T["clashes"] = dict(items=len(tcl), found=len(tcl & set(clash)), extra=len(set(clash) - tcl))
tb = {(r["jid"], r["month"]): r for r in rt("truth_billing.csv")}; gb = {(b["job"], b["month"]): b for b in bill}
T["billing"] = dict(items=len(tb), agree=sum(1 for k, v in tb.items() if abs(float(v["should_bill"]) - gb.get(k, {}).get("expected", 0)) < .005 and abs(float(v["jobber"]) - gb.get(k, {}).get("billed_jobber", 0)) < .005))
T["calendar_codes"] = dict(items=len(cal), leaked=sum(1 for c in cal if CODE.search(c["description"])))
json.dump(T, open(os.path.join(HERE, "results/engine_test.json"), "w"), indent=1)
print(json.dumps(T)); print("status", dict(Counter(r["status"] for r in rec))); print("pilot", pilot, "gates", [(g["wave"], g["gate"], g["open_items"]) for g in gates if g["open_items"]])
print("billing gap +", round(sum(b["gap"] for b in bill if b["gap"] > 0), 2), "-", round(sum(b["gap"] for b in bill if b["gap"] < 0), 2), "drive", len(drive), "events", len(cal))