"""Step 8: merge permits + county parcels + assessor + rental registry (+ lookups, Redfin when present),
infer each unit's status, group into projects, write ui/data/data.js (window.MH_DATA).

A "unit" here is a county tax parcel (one townhouse lot, one house+ADU lot, one plex parcel...). `units` is the
dwelling count on it.
Usage: python3 pipeline/build.py
"""
import collections, datetime, glob, json, os, re, statistics
from common import ROOT, CACHE, load, norm_addr
from enrich_assessor import parse as parse_assessor
import universe

TODAY = datetime.date.today()
ENTITY = re.compile(r"\b(LLC|L L C|INC|CORP|CORPORATION|CO|LP|LLP|LTD|HOLDINGS?|PROPERTIES|PROPERTY|HOMES|DEVELOPMENT|DEV|"
                    r"CONSTRUCTION|BUILDERS?|INVESTMENTS?|PARTNERS|GROUP|VENTURES|ENTERPRISES|LAND TRUST|HOUSING|COMPANY|ASSOCIATES)\b")
ARMS = lambda s: (s["price"] or 0) >= 50000 and "QUIT" not in s["type"] and not s.get("bulk")
PIPELINE = {"APPLIED", "IN REVIEW", "REQUEST FOR INFO", "RTI HOLD", "READY TO ISSUE", "AMENDMENT CCL"}


def d(s):
    return datetime.date.fromisoformat(s) if s else None


def is_entity(name):
    return bool(name and ENTITY.search(name.upper()))


def same_site(mail, q):
    """Owner's mailing address is the parcel itself -> owner-occupied."""
    if not mail or not q.get("situs_num"):
        return False
    m = norm_addr(mail)
    street = (q.get("situs_street") or "").split()
    return m.startswith(str(q["situs_num"]).strip() + " ") and bool(street) and street[0] in m.split()[1:4]


def units_on_parcel(ptype, q):
    code = q.get("property_use_cd") or ""
    if code.startswith("13") and code[2:].isdigit() and int(code[2:]) > 0:
        return int(code[2:])
    return {"Duplex": 2, "Triplex": 3, "Fourplex": 4, "House + ADU": 2}.get(ptype, 1)


def short_name(s):
    s = (s or "").strip()
    s = re.sub(r"\b(DIV|DIVISION|PH|PHASE)\b.*$", lambda m: m[0], s)
    return s.title().replace("Llc", "LLC").replace("Pud", "PUD").replace("Itk", "ITK").replace("Ii", "II") if s else ""


def name_tokens(n):
    return {t for t in re.findall(r"[A-Z]{3,}", (n or "").upper())} - {"LLC", "INC", "AND", "THE", "TRUST", "LIVING", "REVOCABLE"}


def zone_class(z):
    """Raw zoning strings (city permit layer for Bellingham, county parcel layer elsewhere) -> a few broad groups."""
    z = (z or "").upper()
    if not z:
        return "Unknown"
    if any(k in z for k in ("URBAN VILLAGE", "COMMERCIAL", " COM", "COMM", "IND", "OFFICE", "MIXED USE", "DOWNTOWN")):
        return "Commercial / mixed-use / urban village"
    if "RURAL" in z or re.search(r"1DU/\d+AC|CLSTR", z):
        return "Rural/county code (often stale after annexation)"
    if any(k in z for k in ("MULTI", "DUPLEX", "RM ", "OVER 3 UNITS", "MIX DENSITY", "MULTI-FAM")):
        return "Residential multi"
    if any(k in z for k in ("SINGLE", "SGL", "RSF", "URBAN RES", "RL DEN", "RES")):
        return "Residential single"
    return "Other"


def infer(u, q, a, reg, fin, permit_owners=()):
    """Status for a finished unit. Returns (status, confidence, [evidence])."""
    ev = []
    owner = (a or {}).get("owner") or q.get("title_owner_name_full") or ""
    mail = (a or {}).get("mail") or " ".join(x for x in (q.get("title_owner_add_full"), q.get("title_owner_city"), q.get("title_owner_state")) if x)
    occ = same_site(mail, q)
    sales = (a or {}).get("sales", [])
    after = [s for s in sales if ARMS(s) and fin and d(s["date"]) >= fin - datetime.timedelta(days=270)]
    qc = [s for s in sales if fin and d(s["date"]) >= fin - datetime.timedelta(days=270) and "QUIT" in s["type"] and is_entity(s["grantee"])]
    months = (TODAY - fin).days / 30.4 if fin else None
    adu = u["type"] in ("ADU", "AADU", "DADU", "House + ADU")
    multi = u["units"] > 1

    if reg:
        ev.append(f"On city rental registry ({reg['LICENSE_NO']}, {reg.get('NUMBER_OF_UNITS') or '?'} unit(s), contact: {reg.get('CONTACT_NAME') or '?'})"
                  + (f"; first inspection {reg['first_inspection']}" if reg.get("first_inspection") else ""))
    if after:
        s = after[0]
        ev.append(f"New-construction sale {s['date']}: {s['grantor']} → {s['grantee']}, ${s['price']:,.0f} ({s['type']})")
        if len(after) > 1:
            ev.append(f"Resold {len(after) - 1}× since; last {after[-1]['date']} ${after[-1]['price']:,.0f}")
    bulk = [s for s in sales if s.get("bulk") and fin and d(s["date"]) >= fin - datetime.timedelta(days=270)]
    for s in bulk[:1]:
        ev.append(f"Bulk sale {s['date']}: {s['grantor']} → {s['grantee']}, ${s['price']:,.0f} for {s['bulk']} parcels together (portfolio sale)")
    for s in qc[:2]:
        ev.append(f"$0 quit-claim {s['date']}: {s['grantor']} → {s['grantee']} (internal transfer)")
    ev.append(f"Owner now: {owner or '?'}; mailing: {mail or '?'}" + (" (= site → lives there)" if occ else ""))

    if reg and not occ:
        return "rented", "confirmed", ev
    if reg and occ:
        ev.append("Owner lives on site and part of the property is registered as a rental (ADU or other unit rented).")
        return ("sold_owner" if after else "owner_occ"), "confirmed", ev
    if after:
        if occ:
            return "sold_owner", "confirmed", ev
        if is_entity(owner) or owner:
            return "sold_investor", "likely", ev
        return "unknown", "guess", ev
    pt = name_tokens(owner)
    if permit_owners and not adu and pt and not (qc and is_entity(owner)) and not any(pt & name_tokens(po) for po in permit_owners):
        ev.append(f"Current owner isn't the permit owner ({'; '.join(sorted(set(permit_owners))[:2])}) → it was sold"
                  + ("; no arm's-length sale on record" if a else "; sale price not loaded yet") + ".")
        if occ:
            return "sold_owner", "likely", ev
        return "sold_investor", "likely" if not is_entity(owner) else "guess", ev
    if is_entity(owner):
        if qc or multi or (months is not None and months > 6):
            why = "internal quit-claim to holding entity" if qc else ("multi-unit parcel owned by an entity" if multi else f"builder entity still owns it {months:.0f} months after final")
            ev.append(f"No sale since final; {why} → likely build-to-rent.")
            return "held_rental", "likely", ev
        ev.append(f"Finaled {months:.0f} months ago, still owned by builder entity, no sale yet → probably listed or about to be.")
        return "unsold_new", "guess", ev
    if occ:
        if adu or multi:
            ev.append("Owner lives on site; the ADU/other unit's use (rented, family, STR) is unknown.")
        return "owner_occ", "confirmed" if not adu else "likely", ev
    if owner:
        ev.append("Individual owner who doesn't live there, no sale since completion → probably rented.")
        return "owner_away", "guess", ev
    return "unknown", "guess", ev


def main():
    permits, parcels, link, tracked, parcel_only = universe.build()
    assessor = {}  # parse cached pages directly so a build works mid-fetch
    for pid in tracked:
        fn = os.path.join(CACHE, "assessor", f"{pid}.html")
        if os.path.exists(fn):
            assessor[pid] = parse_assessor(open(fn).read())
    # One deed recorded on several parcels = bulk/portfolio sale; its price is for all of them, so keep it out of
    # per-unit prices (still shown in the sales table, flagged).
    deed_n = collections.Counter((s["date"], s["grantor"], s["grantee"], s["price"]) for a in assessor.values() for s in a["sales"] if s["price"])
    for a in assessor.values():
        for s in a["sales"]:
            n = deed_n[(s["date"], s["grantor"], s["grantee"], s["price"])]
            if n > 1:
                s["bulk"] = n
    reg_rows = load("rental_registry.json")
    first_insp = {}
    for r in load("rental_inspections.json"):
        if r.get("ACTIVITYID") and r.get("COMPLETED_DATE"):
            first_insp[r["ACTIVITYID"]] = min(first_insp.get(r["ACTIVITYID"], "9999"), r["COMPLETED_DATE"])
    reg_by_apn, reg_by_addr = {}, {}
    for r in reg_rows:
        if (r.get("STATUS") or "").upper() not in ("ACTIVE", "ISSUED", "RENEWED", "PENDING"):
            continue
        r["first_inspection"] = first_insp.get(r["LICENSE_NO"])
        if r.get("SITE_APN"):
            reg_by_apn[r["SITE_APN"]] = r
        reg_by_addr[norm_addr(r.get("SITE_ADDR"))] = r

    # optional manual / browser lookups: data/lookups/*.json  ({"prop_id"|"address", "date", "source", "url", "events":[...], "notes"})
    lookups = collections.defaultdict(list)
    for fn in glob.glob(os.path.join(ROOT, "data", "lookups", "*.json")):
        for rec in (lambda x: x if isinstance(x, list) else [x])(json.load(open(fn))):
            key = rec.get("prop_id") or norm_addr(rec.get("address"))
            lookups[key].append(rec)
    redfin = json.load(open(os.path.join(ROOT, "data", "redfin.json"))) if os.path.exists(os.path.join(ROOT, "data", "redfin.json")) else {}

    # ---- permits per parcel
    pp = collections.defaultdict(list)
    for p in permits:
        if p["PERMIT_NO"] in link:
            pp[link[p["PERMIT_NO"]]].append(p)

    units = {}
    for pid in tracked:
        q = parcels[pid]
        a = assessor.get(pid)
        ps = sorted(pp.get(pid, []), key=lambda p: p["APPLIED"] or "")
        live = [p for p in ps if p["STATUS"] not in ("EXPIRED", "VOIDED OR WITHDRAWN")]
        if ps:
            types = collections.Counter(universe.permit_type(p) for p in (live or ps))
            ptype = types.most_common(1)[0][0]
            fins = [p["FINALED"] for p in live if p["STATUS"] == "FINALED" and p["FINALED"]]
            n_units = sum(max(p.get("NUM_NEW_UNITS") or 0, 0) for p in live) or units_on_parcel(ptype, q)
        else:
            ptype = universe.parcel_type(q)
            fins = [f"{q['yr_blt']}-07-01"] if q.get("yr_blt") else []
            n_units = units_on_parcel(ptype, q)
        addr = " ".join(x for x in (q.get("situs_num"), q.get("situs_street_prefix"), q.get("situs_street")) if x).strip()
        if q.get("situs_unit"):
            addr += f" #{q['situs_unit'].strip().lstrip('#').strip()}"
        if not addr and ps:
            addr = ps[0]["SITE_ADDR"].strip()
        u = dict(pid=pid, addr=addr.title(), city=universe.city(q) or "Bellingham", type=ptype, units=n_units,
                 src="permit" if ps else "parcel",
                 lat=(ps[0].get("lat") if ps and ps[0].get("lat") else q.get("lat")), lon=(ps[0].get("lon") if ps and ps[0].get("lon") else q.get("lon")),
                 apn=q.get("TTRRSSXXXYYY"), zoning=(q.get("zoning_description") or q.get("zoning") or "").strip(),
                 nhood=(ps[0].get("Neighborhood") or "").title() if ps else "", subdiv=short_name(q.get("subdv_description")),
                 use=(q.get("property_use_description") or "").strip(), yr=q.get("yr_blt"), sqft=q.get("sqft_la") or None,
                 lot_ac=q.get("legal_acreage"), value=(a or {}).get("market_value") or q.get("market"),
                 land=q.get("market_land_val"), owner=(a or {}).get("owner") or q.get("title_owner_name_full"),
                 mail=(a or {}).get("mail") or q.get("title_owner_add_full"), assessor=q.get("Hyperlink"),
                 sales=(a or {}).get("sales", []), improvements=(a or {}).get("improvements", []),
                 permits=[p["PERMIT_NO"] for p in ps], finaled=min(fins) if fins else None, have_assessor=bool(a))
        # status
        reg = reg_by_apn.get(u["apn"]) or reg_by_addr.get(norm_addr(addr))
        if live and not fins:
            st = live[-1]["STATUS"]
            u["status"], u["conf"] = ("construction", "confirmed") if st == "ISSUED" else ("pipeline", "confirmed")
            u["evidence"] = [f"Permit {live[-1]['PERMIT_NO']} status {st}"]
        elif ps and not live:
            u["status"], u["conf"], u["evidence"] = "expired", "confirmed", ["All permits expired or withdrawn"]
        else:
            u["status"], u["conf"], u["evidence"] = infer(u, q, a, reg, d(u["finaled"]), [p["OWNER_NAME"] for p in ps if p.get("OWNER_NAME")])
            if not a:
                u["evidence"].append("Assessor page not fetched yet; status from county parcel owner only.")
        if reg:
            u["registry"] = {k: reg.get(k) for k in ("LICENSE_NO", "NUMBER_OF_UNITS", "UNIT_TYPE", "CONTACT_NAME", "first_inspection")}
        # first new-construction sale
        fin = d(u["finaled"])
        arms = [s for s in u["sales"] if ARMS(s) and fin and d(s["date"]) >= fin - datetime.timedelta(days=270)]
        if arms:
            s = arms[0]
            u["sale"] = dict(date=s["date"], price=s["price"], buyer=s["grantee"], seller=s["grantor"],
                             days_after_final=(d(s["date"]) - fin).days)
            if u["sqft"]:
                u["sale"]["ppsf"] = round(s["price"] / u["sqft"])
        u["builder"] = (ps[0].get("OWNER_NAME") if ps else None) or (arms[0]["grantor"] if arms else None) or (u["owner"] if is_entity(u["owner"]) else None)
        u["builder"] = short_name(u["builder"]) if u["builder"] else None
        lk = lookups.get(pid, []) + lookups.get(norm_addr(addr), [])
        if lk:
            u["lookups"] = sorted(lk, key=lambda r: r.get("date", ""))
        rf = redfin.get(str(pid)) or redfin.get(norm_addr(addr))
        if rf:
            u["redfin"] = rf
            finished = u["status"] not in ("pipeline", "construction", "expired")
            if rf["sold"] and rf.get("sold_date") and rf.get("price"):
                u["evidence"].insert(0, f"Redfin/MLS: sold {rf['sold_date']} for ${rf['price']:,.0f}"
                                        + (f" ({rf['beds']:g} bd / {rf['baths']:g} ba, {rf['sqft']:,} sf)" if rf.get("beds") and rf.get("baths") and rf.get("sqft") else ""))
                # county deeds lag (or aren't fetched yet): use the MLS sale when we have no deed for it
                if finished and not u.get("sale") and fin and d(rf["sold_date"]) >= fin - datetime.timedelta(days=270):
                    u["sale"] = dict(date=rf["sold_date"], price=rf["price"], source="Redfin/MLS",
                                     days_after_final=(d(rf["sold_date"]) - fin).days)
                    sf = rf.get("sqft") or u["sqft"]
                    if sf:
                        u["sale"]["ppsf"] = round(rf["price"] / sf)
                if finished and u["status"] not in ("sold_owner", "sold_investor", "rented"):
                    u["status"], u["conf"] = "sold_recent", "confirmed"
            elif not rf["sold"]:
                u["evidence"].insert(0, (f"Redfin: listed for sale at ${rf['price']:,.0f}" if rf.get("price") else "Redfin: listed for sale")
                                     + (f", {rf['days_listed']} days on market" if rf.get("days_listed") else "") + f" (seen {rf['seen']})")
                if finished:
                    u["status"], u["conf"] = "for_sale", "confirmed"
        units[pid] = u

    # ---- projects
    # A "project" is a development: new-construction parcels group by their recorded plat (county subdivision code),
    # so a builder's townhomes + cottages filed as dozens of separate permits land in one project. ADUs on older
    # lots (old plats are whole neighborhoods) stay grouped by their city permit project instead.
    ADUISH = ("ADU", "AADU", "DADU", "House + ADU")
    pkey = lambda p: p.get("PARENT_PROJECT_NO") or p["PERMIT_NO"]

    def dev_key(u):
        q = parcels[u["pid"]]
        if q.get("subdv_cd") and (q.get("yr_blt") or 0) >= 2014 and u["type"] not in ADUISH:
            return f"{u['city']}|{q['subdv_cd'].strip()}"
        return None

    owner_of, pk_to_dev = {}, {}
    for p in sorted(permits, key=lambda p: p["APPLIED"] or ""):
        pid = link.get(p["PERMIT_NO"])
        if pid in units and pid not in owner_of:
            k = dev_key(units[pid]) or pk_to_dev.get(pkey(p)) or pkey(p)
            owner_of[pid] = k
            pk_to_dev.setdefault(pkey(p), k)
    for pid in sorted(parcel_only):
        u = units[pid]
        owner_of[pid] = dev_key(u) or f"{u['city']}|{u['type']}|{pid}"
    projects = {}
    for pid, k in owner_of.items():
        projects.setdefault(k, dict(id=k, permits=[], pids=[], proj_nos=set()))["pids"].append(pid)
    for p in sorted(permits, key=lambda p: p["APPLIED"] or ""):
        k = pk_to_dev.get(pkey(p)) or pkey(p)
        pr = projects.setdefault(k, dict(id=k, permits=[], pids=[], proj_nos=set()))
        pr["permits"].append(p)
        if p.get("PARENT_PROJECT_NO"):
            pr["proj_nos"].add(p["PARENT_PROJECT_NO"])
    for pr in projects.values():
        pr["src"] = "permit" if pr["permits"] else "parcel"

    out_projects = []
    for key, pr in projects.items():
        us = [units[pid] for pid in pr["pids"]]
        ps = pr["permits"]
        live = [p for p in ps if p["STATUS"] not in ("EXPIRED", "VOIDED OR WITHDRAWN")] or ps
        if not us and not ps:
            continue
        types = collections.Counter(u["type"] for u in us) or collections.Counter(universe.permit_type(p) for p in live)
        subdivs = collections.Counter(u["subdiv"] for u in us if u["subdiv"])
        first = live[0] if live else None
        name = subdivs.most_common(1)[0][0] if subdivs else ""
        addr0 = (us[0]["addr"] if us else first["SITE_ADDR"].title().strip())
        streets = sorted({re.sub(r"^\d+\S*\s+", "", u["addr"].split(" #")[0]) for u in us})
        if not name or "|" not in key and len(us) <= 2:
            name = addr0 if len(us) <= 1 else f"{addr0} +{len(us) - 1}"
        dates = lambda k: sorted(p[k] for p in live if p.get(k))
        sales = [u["sale"] for u in us if u.get("sale")]
        lat = [u["lat"] for u in us if u.get("lat")] or [p["lat"] for p in live if p.get("lat")]
        lon = [u["lon"] for u in us if u.get("lon")] or [p["lon"] for p in live if p.get("lon")]
        if not lat:
            continue
        builders = collections.Counter(u["builder"] for u in us if u.get("builder"))
        pj = dict(
            id=key, src=pr["src"], name=name, city=us[0]["city"] if us else "Bellingham",
            type=types.most_common(1)[0][0], types=dict(types), streets=streets[:6],
            parcels=len(us), units=(sum(max(p.get("NUM_NEW_UNITS") or 0, 0) for p in live) if ps else 0) or sum(u["units"] for u in us),
            lat=statistics.fmean(lat), lon=statistics.fmean(lon),
            nhood=(first.get("Neighborhood") or "").title() if first else "", zoning=(first.get("Zoning_Description") if first else None) or (us[0]["zoning"] if us else ""),
            builder=(short_name(collections.Counter(p["OWNER_NAME"] for p in live if p.get("OWNER_NAME")).most_common(1)[0][0])
                     if any(p.get("OWNER_NAME") for p in live) else (builders.most_common(1)[0][0] if builders else None)),
            contractor=(short_name(collections.Counter(p["CONTRACTOR_NAME"] for p in live if p.get("CONTRACTOR_NAME")).most_common(1)[0][0])
                        if any(p.get("CONTRACTOR_NAME") for p in live) else None),
            applicant=short_name(first.get("APPLICANT_NAME")) if first and first.get("APPLICANT_NAME") else None,
            applied=(dates("APPLIED") or [None])[0], issued=(dates("ISSUED") or [None])[0],
            finaled_first=(dates("FINALED") or [None])[0], finaled_last=(dates("FINALED") or [None])[-1],
            yr=min((u["yr"] for u in us if u.get("yr")), default=None),
            zone_class=zone_class((first.get("Zoning_Description") if first else None) or (us[0]["zoning"] if us else "")),
            status=dict(collections.Counter(u["status"] for u in us)) if us else {"pipeline": 1},
            jobvalue=sum(p.get("JOBVALUE") or 0 for p in live) or None, fees=sum(p.get("FEES_PAID") or 0 for p in live) or None,
            new_sf=sum(p.get("NEW_FLOOR_AREA") or 0 for p in live) or None,
            sales_n=len(sales), sale_median=statistics.median([s["price"] for s in sales]) if sales else None,
            ppsf_median=statistics.median([s["ppsf"] for s in sales if s.get("ppsf")]) if any(s.get("ppsf") for s in sales) else None,
            pids=pr["pids"], proj_nos=sorted(pr["proj_nos"]),
            permits=[dict(no=p["PERMIT_NO"], addr=p["SITE_ADDR"].strip().title(), status=p["STATUS"], type=universe.permit_type(p),
                          applied=p["APPLIED"], issued=p["ISSUED"], finaled=p["FINALED"], units=p.get("NUM_NEW_UNITS"),
                          sf=p.get("NEW_FLOOR_AREA"), stories=p.get("NUM_STORIES"), value=p.get("JOBVALUE"), fees=p.get("FEES_PAID"),
                          beds=[p.get("Num_Studios") or 0, p.get("Num_1Beds") or 0, p.get("Num_2Beds") or 0, p.get("Num_3Beds") or 0, p.get("Num_4Beds") or 0],
                          desc=(p.get("DESCRIPTION") or "").strip(), scope=(p.get("SCOPEOFWORK") or "").strip(),
                          owner=p.get("OWNER_NAME"), contractor=p.get("CONTRACTOR_NAME"), applicant=p.get("APPLICANT_NAME"),
                          side=[s["PERMIT_NO"] + " " + (s["PermitSubType"] or "").lower() for s in p.get("side_permits", [])])
                     for p in ps],
        )
        out_projects.append(pj)

    for u in units.values():
        u["project"] = owner_of.get(u["pid"])

    data = dict(generated=TODAY.isoformat(), redfin_seen=max((r.get("seen", "") for r in redfin.values()), default=None), projects=sorted(out_projects, key=lambda p: -(p["units"] or 0)),
                units=list(units.values()),
                counts=dict(units=len(units), projects=len(out_projects), assessor=sum(1 for u in units.values() if u["have_assessor"])))
    os.makedirs(os.path.join(ROOT, "ui", "data"), exist_ok=True)
    with open(os.path.join(ROOT, "ui", "data", "data.js"), "w") as fh:
        fh.write("window.MH_DATA = ")
        json.dump(data, fh, separators=(",", ":"), default=str)
        fh.write(";\n")
    print(f"{len(out_projects)} projects, {len(units)} parcels ({data['counts']['assessor']} with assessor data)")
    print("status:", collections.Counter(u["status"] for u in units.values()).most_common())
    print("by city:", collections.Counter(u["city"] for u in units.values()).most_common())


if __name__ == "__main__":
    main()