"""Clean, standardise and de-duplicate input/contacts_raw_combined.csv into a HubSpot-ready contact import.
Outputs: output/hubspot_contacts_import.csv, output/change_log.csv, output/crm_cleanup_report.xlsx.
Does not read truth.json; that file is only used by verify_dedupe.py to check the result."""
import csv, re, datetime as dt
from collections import defaultdict, Counter
from pathlib import Path
H = Path(__file__).parent
raw = list(csv.DictReader(open(H / "input/contacts_raw_combined.csv", encoding="utf-8")))
changes, exceptions, removed = [], [], []
def log(rid, field, before, after, rule):
    if (before or "") != (after or ""): changes.append([rid, field, before, after, rule])
STATE_MAP = {"vic": "VIC", "victoria": "VIC", "nsw": "NSW", "newsouthwales": "NSW", "qld": "QLD", "queensland": "QLD", "sa": "SA", "southaustralia": "SA",
             "wa": "WA", "westernaustralia": "WA", "tas": "TAS", "tasmania": "TAS", "nt": "NT", "northernterritory": "NT", "act": "ACT"}
PC_RANGE = {"VIC": [(3000, 3999), (8000, 8999)], "NSW": [(1000, 2599), (2619, 2899), (2921, 2999)], "ACT": [(200, 299), (2600, 2618), (2900, 2920)], "QLD": [(4000, 4999), (9000, 9999)],
            "SA": [(5000, 5999)], "WA": [(6000, 6999)], "TAS": [(7000, 7999)], "NT": [(800, 999)]}
AREA = {"VIC": "3", "TAS": "3", "NSW": "2", "ACT": "2", "QLD": "7", "SA": "8", "WA": "8", "NT": "8"}
STAGE_MAP = {"lead": "Lead", "newlead": "Lead", "mql": "Marketing Qualified Lead", "marketingqualified": "Marketing Qualified Lead", "sql": "Sales Qualified Lead",
             "salesqualified": "Sales Qualified Lead", "opportunity": "Opportunity", "opp": "Opportunity", "customer": "Customer", "cust": "Customer", "client": "Customer",
             "customeractive": "Customer", "subscriber": "Subscriber", "newsletteronly": "Subscriber"}
STAGE_ORDER = ["Subscriber", "Lead", "Marketing Qualified Lead", "Sales Qualified Lead", "Opportunity", "Customer"]
NAME_FIX = {"Macleod": "MacLeod", "Mcdonald": "McDonald", "O'brien": "O'Brien", "O'connor": "O'Connor"}
ACRO = {"Ceo": "CEO", "Hr": "HR", "Ap": "AP"}
JUNK = {"test", "testtest", "asdf", "x", "na", "deleteme", ""}
EMAIL_RE = re.compile(r"^[a-z0-9._%+-]+@[a-z0-9-]+(\.[a-z0-9-]+)*\.[a-z]{2,}$")
FREEMAIL = {"inboxmail.example"}
def key(s): return re.sub(r"[^a-z]", "", (s or "").lower())
def proper(s):
    s = re.sub(r"\s+", " ", (s or "").strip())
    if s and (s.isupper() or s.islower()):
        s = " ".join("-".join(p.capitalize() for p in w.split("-")) for w in s.split(" "))
        s = re.sub(r"'([a-z])", lambda m: "'" + m.group(1).upper(), s)
        s = " ".join(NAME_FIX.get(w, w) for w in s.split(" "))
    return s
def clean_phone(v, state, rid, field):
    d = re.sub(r"\D", "", v or "")
    if not d: return ""
    if d.startswith("61"): d = "0" + d[2:]
    if len(d) == 8 and state in AREA:
        d = "0" + AREA[state] + d; exceptions.append([rid, field, v, "Area code missing: inferred 0" + AREA[state] + " from state " + state, "Info"])
    if len(d) != 10 or not d.startswith("0"):
        exceptions.append([rid, field, v, "Phone number could not be parsed: cleared", "Fix at source"]); return ""
    return f"+61 {d[1:4]} {d[4:7]} {d[7:]}" if d.startswith("04") else f"+61 {d[1]} {d[2:6]} {d[6:]}"
def parse_date(s):
    for f in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y"):
        try: return dt.datetime.strptime(s.strip(), f).date()
        except Exception: pass
    return None
recs = []
for r in raw:
    rid = r["Record ID"]
    fn0, ln0 = r["First Name"], r["Last Name"]
    if key(fn0 + ln0) in JUNK or (key(fn0) in JUNK and not r["Company"].strip()):
        removed.append([rid, r["Source"], f"{fn0} {ln0}".strip(), r["Email"], "Test/junk entry (no real name, no company)"]); continue
    c = {"rid": rid, "source": r["Source"]}
    fn = re.sub(r"^(dr|mr|mrs|ms|miss)\.?\s+", "", fn0.strip(), flags=re.I)
    if fn != fn0.strip(): log(rid, "First Name", fn0, fn, "Removed title (Dr/Mr/Ms/Mrs)")
    ln = ln0
    if not ln.strip() and " " in fn.strip():
        fn, ln = fn.strip().rsplit(" ", 1); log(rid, "Last Name", ln0, ln, "Split full name from First Name field")
    for fld, before, val in (("First Name", fn, fn), ("Last Name", ln, ln)):
        v = proper(val); log(rid, fld, before, v, "Trimmed / name case"); c[fld] = v
    e0 = r["Email"]; e = e0.strip().lower().replace("mailto:", "").strip("<> ").strip()
    log(rid, "Email", e0, e, "Lower-case, trimmed, removed mailto:/brackets")
    if e and not EMAIL_RE.match(e):
        exceptions.append([rid, "Email", e0, "Invalid email format: cleared (contact kept)", "Find correct email"]); log(rid, "Email", e, "", "Invalid email cleared"); e = ""
    c["Email"] = e
    s0 = r["State"]; st = STATE_MAP.get(key(s0), ""); log(rid, "State/Region", s0, st, "State to standard abbreviation"); c["State/Region"] = st
    if s0.strip() and not st: exceptions.append([rid, "State", s0, "Unrecognised state", "Check"])
    for fld, src in (("Phone Number", "Phone"), ("Mobile Phone Number", "Mobile")):
        v = clean_phone(r[src], st, rid, fld); log(rid, fld, r[src], v, "Phone to +61 international format"); c[fld] = v
    co0 = r["Company"]; co = re.sub(r"\s+", " ", co0.strip())
    co = re.sub(r"\s*(pty\.?\s*ltd\.?|p/l)$", "", co, flags=re.I)
    co = proper(co) if co.isupper() else co
    co = (co + " Pty Ltd") if co else ""; log(rid, "Company Name", co0, co, "Company suffix to 'Pty Ltd', trimmed, case"); c["Company Name"] = co
    jt = " ".join(ACRO.get(w, w) for w in proper(r["Job Title"]).split(" ")) if r["Job Title"].strip() else ""
    log(rid, "Job Title", r["Job Title"], jt, "Job title case"); c["Job Title"] = jt
    ci = proper(r["City"]); log(rid, "City", r["City"], ci, "City case"); c["City"] = ci
    p0 = r["Postcode"]; pc = re.sub(r"\.0$", "", p0.strip())
    pc = pc.zfill(4) if pc.isdigit() else ""
    log(rid, "Postal Code", p0, pc, "Postcode to 4 digits (restored leading zero / removed .0)"); c["Postal Code"] = pc
    if pc and st and not any(a <= int(pc) <= b for a, b in PC_RANGE[st]): exceptions.append([rid, "Postal Code", p0, f"Postcode outside {st} ranges", "Check"])
    lc = STAGE_MAP.get(key(r["Lifecycle Stage"]), ""); log(rid, "Lifecycle Stage", r["Lifecycle Stage"], lc, "Mapped to HubSpot lifecycle stage"); c["Lifecycle Stage"] = lc
    d = parse_date(r["Last Activity"]); c["date"] = d
    log(rid, "Last Activity", r["Last Activity"], d.isoformat() if d else "", "Date to YYYY-MM-DD")
    recs.append(c)
# ---- de-duplicate (union-find on three match rules) ----
par = list(range(len(recs))); why = {}
def find(i):
    while par[i] != i: par[i] = par[par[i]]; i = par[i]
    return i
def union(i, j, rule):
    a, b = find(i), find(j)
    if a != b: par[b] = a; why[(min(i, j), max(i, j))] = rule
RULES = [("R1 same email", lambda c: c["Email"] or None),
         ("R2 same phone + surname", lambda c: (c["Phone Number"], key(c["Last Name"])) if c["Phone Number"] else None),
         ("R3 same full name + company", lambda c: (key(c["First Name"]), key(c["Last Name"]), key(c["Company Name"])) if c["Company Name"] and c["First Name"] else None)]
pairs_rule = []
for rule, kf in RULES:
    seen = {}
    for i, c in enumerate(recs):
        k = kf(c)
        if k is None: continue
        if k in seen:
            if find(seen[k]) != find(i): pairs_rule.append(rule)
            union(seen[k], i, rule)
        else: seen[k] = i
clusters = defaultdict(list)
for i in range(len(recs)): clusters[find(i)].append(i)
FIELDS = ["First Name", "Last Name", "Email", "Phone Number", "Mobile Phone Number", "Company Name", "Job Title", "City", "State/Region", "Postal Code"]
out, merges = [], []
for root, idx in clusters.items():
    grp = sorted((recs[i] for i in idx), key=lambda c: (c["date"] or dt.date.min), reverse=True)
    win = dict(grp[0]); filled = []
    work = [c["Email"] for c in grp if c["Email"] and c["Email"].split("@")[1] not in FREEMAIL]
    if work and win["Email"] != work[0]:
        if win["Email"]: win["alt"] = win["Email"]
        win["Email"] = work[0]; filled.append("Email (work address preferred)")
    alts = sorted({c["Email"] for c in grp if c["Email"] and c["Email"] != win["Email"]})
    for f in FIELDS:
        if not win[f]:
            v = next((c[f] for c in grp if c[f]), "")
            if v: win[f] = v; filled.append(f)
    win["Lifecycle Stage"] = max((c["Lifecycle Stage"] for c in grp if c["Lifecycle Stage"]), key=STAGE_ORDER.index, default="")
    win["ids"] = [c["rid"] for c in grp]; win["alts"] = alts; win["sources"] = sorted({c["source"] for c in grp})
    if len(grp) > 1:
        rules = sorted({why.get((min(a, b), max(a, b))) for a in idx for b in idx if why.get((min(a, b), max(a, b)))})
        merges.append([win["rid"], "; ".join(c["rid"] for c in grp[1:]), len(grp), " + ".join(r.split(" ", 1)[0] for r in rules), "; ".join(r.split(" ", 1)[1] for r in rules),
                       f"{win['First Name']} {win['Last Name']}", ", ".join(filled) or "none", win["Lifecycle Stage"]])
    if not win["Email"]: exceptions.append([win["rid"], "Email", "", "No email after merge: HubSpot cannot de-duplicate this contact on re-import", "Find email"])
    if not win["Phone Number"] and not win["Mobile Phone Number"]: exceptions.append([win["rid"], "Phone Number", "", "No phone number", "Optional"])
    out.append(win)
out.sort(key=lambda c: (c["Last Name"].lower(), c["First Name"].lower()))
HS = ["First Name", "Last Name", "Email", "Phone Number", "Mobile Phone Number", "Company Name", "Job Title", "City", "State/Region", "Postal Code", "Country/Region",
      "Lifecycle Stage", "Legacy Record IDs", "Legacy Alternate Email", "Legacy Last Activity Date", "Legacy Data Source"]
def row(c):
    return [c["First Name"], c["Last Name"], c["Email"], c["Phone Number"], c["Mobile Phone Number"], c["Company Name"], c["Job Title"], c["City"], c["State/Region"],
            c["Postal Code"], "Australia", c["Lifecycle Stage"], "; ".join(c["ids"]), "; ".join(c["alts"]), c["date"].isoformat() if c["date"] else "", "; ".join(c["sources"])]
with open(H / "output/hubspot_contacts_import.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f); w.writerow(HS); [w.writerow(row(c)) for c in out]
with open(H / "output/change_log.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f); w.writerow(["Record ID", "Field", "Before", "After", "Rule"]); w.writerows(changes)
import pickle
pickle.dump({"raw": raw, "out": [row(c) for c in out], "HS": HS, "changes": changes, "exceptions": exceptions, "removed": removed, "merges": merges,
             "pairs_rule": Counter(pairs_rule)}, open(H / "build_state.pkl", "wb"))
print(f"{len(raw)} rows -> removed {len(removed)} junk -> {len(recs)} -> {len(out)} contacts; {len(merges)} merge groups; {len(changes)} changes; {len(exceptions)} exceptions")
print(Counter(pairs_rule), Counter(e[3].split(':')[0] for e in exceptions))