import json, re, csv, os
from collections import defaultdict, Counter
mm=json.load(open("mm_permits.json"))
def cat(r):
    ht=r.get("Housing_Type") or ""; pt=r.get("PermitType") or ""; st=r.get("PermitSubType") or ""
    if ht in ("AADU","DADU") or st=="ADU": return "ADU"
    if ht=="SFR W/ AADU": return "SFR + ADU"
    if "TOWNHOUSE" in ht or pt=="NEW CONSTRUCTION TOWNHOME": return "Townhouse"
    if "SMALL LOT" in ht: return "Small lot (ITK)"
    if "GARDEN COURT" in ht: return "Garden court (ITK)"
    if "DUPLEX" in ht: return "Duplex"
    if "TRIPLEX" in ht: return "Triplex"
    if pt=="BUILDING MULTIFAMILY RESIDENTIAL": return f"Multifamily ({r.get('NUM_NEW_UNITS')} units)"
    return "Other"
def street(addr):
    return re.sub(r"^\d+[A-Z]?\s+","",addr or "").strip()
projects=defaultdict(list)
for r in mm:
    key=r.get("PARENT_PROJECT_NO") or f"{street(r['SITE_ADDR'])}|{r.get('APPLICANT_NAME')}"
    projects[key].append(r)
STATUS_ORDER=["APPLIED","INCOMPLETE","IN REVIEW","REQUEST FOR INFO","RTI HOLD","AMENDMENT IN REVIEW","READY TO ISSUE","ISSUED","FINALED","EXPIRED","VOIDED OR WITHDRAWN","DENIED"]
out=[]
for key,ps in projects.items():
    ps.sort(key=lambda r:r["APPLIED"])
    cats=Counter(cat(r) for r in ps)
    statuses=Counter(r["STATUS"] for r in ps)
    units=sum((r.get("NUM_NEW_UNITS") or 0) for r in ps)
    # phase = least-advanced active status
    active=[s for s in statuses if s not in ("VOIDED OR WITHDRAWN","EXPIRED","DENIED")]
    phase=min(active,key=lambda s:STATUS_ORDER.index(s) if s in STATUS_ORDER else 99) if active else list(statuses)[0]
    addrs=sorted({r["SITE_ADDR"] for r in ps})
    out.append({
        "project_key":key if key.startswith("PFC") or key.startswith("PRJ") else "",
        "category":", ".join(f"{k} x{v}" if v>1 else k for k,v in cats.most_common()),
        "permits":len(ps),
        "new_units":units,
        "address":addrs[0] + (f" (+{len(addrs)-1} more)" if len(addrs)>1 else ""),
        "neighborhood":ps[0].get("Neighborhood"),
        "zoning":ps[0].get("Zoning_Description"),
        "phase":phase,
        "status_mix":", ".join(f"{k} x{v}" if v>1 else k for k,v in statuses.most_common()),
        "first_applied":ps[0]["APPLIED"],
        "last_applied":ps[-1]["APPLIED"],
        "issued":min((r["ISSUED"] for r in ps if r.get("ISSUED")),default=""),
        "applicant":ps[0].get("APPLICANT_NAME"),
        "owner":ps[0].get("OWNER_NAME"),
        "job_value":sum((r.get("JOBVALUE") or 0) for r in ps),
        "description":(ps[0].get("DESCRIPTION") or "")[:160],
        "permit_nos":", ".join(r["PERMIT_NO"] for r in ps),
        "lat":ps[0].get("lat"),"lon":ps[0].get("lon"),
    })
out.sort(key=lambda p:p["last_applied"],reverse=True)
print("projects:",len(out))
print("by category (projects):",Counter(p["category"].split(" x")[0].split(",")[0] for p in out).most_common())
print("by phase:",Counter(p["phase"] for p in out).most_common())
multi=[p for p in out if not p["category"].startswith("ADU") and not p["category"].startswith("SFR + ADU")]
print("\nmulti-unit projects:",len(multi),"units:",sum(p["new_units"] for p in multi))
for p in multi:
    print(f"{p['last_applied']} | {p['phase']:16} | {p['category']:28} | {p['new_units']:>3}u | {p['address']:32} | {p['neighborhood']:16} | {p['applicant']}")
json.dump(out,open("mm_projects.json","w"))
PROJ=os.path.dirname(os.path.abspath(__file__))
os.makedirs(PROJ,exist_ok=True)
with open(f"{PROJ}/middle_housing_projects.csv","w",newline="") as fh:
    w=csv.DictWriter(fh,fieldnames=list(out[0].keys())); w.writeheader(); w.writerows(out)
cols=["PERMIT_NO","STATUS","APPLIED","ISSUED","FINALED","PermitType","PermitSubType","Housing_Type","NUM_NEW_UNITS","SITE_ADDR","Neighborhood","Zoning_Description","PARENT_PROJECT_NO","PARENT_PERMIT_NO","APPLICANT_NAME","OWNER_NAME","CONTRACTOR_NAME","JOBVALUE","NEW_FLOOR_AREA","NUM_STORIES","DESCRIPTION","SITE_APN","lat","lon"]
with open(f"{PROJ}/middle_housing_permits.csv","w",newline="") as fh:
    w=csv.DictWriter(fh,fieldnames=cols,extrasaction="ignore"); w.writeheader(); w.writerows(sorted(mm,key=lambda r:r["APPLIED"],reverse=True))