import os as _os; _os.chdir(_os.environ.get("MIAMI_PROBE_DIR", "data/cities/miami/sources/_probe"))
import json, pandas as pd, numpy as np
d = json.load(open("gis/layer_0.json"))
g = pd.DataFrame([f["attributes"] for f in d["features"]])
g["ID"] = g.ID.astype(str).str.strip().str.zfill(4)
g["MODIFIED"] = pd.to_datetime(g.MODIFIEDDATE, unit="ms")
print("records:", len(g)); print("TYPE counts:\n", g.TYPE.value_counts().to_string())
print("MODIFIEDDATE range:", g.MODIFIED.min(), "→", g.MODIFIED.max())
print("CAPACITY>0:", (g.CAPACITY>0).sum(), " ENROLLMNT>0:", (g.ENROLLMNT>0).sum())
core = g[g.TYPE.isin(["E","M","S"]) & (g.CAPACITY>0) & (g.ENROLLMNT>0)].copy()
core["util"] = core.ENROLLMNT/core.CAPACITY
print("\nE/M/S schools with both fields:", len(core))
print(core.util.describe(percentiles=[.1,.25,.5,.75,.9]).round(3).to_string())
for t in [1/3, .5, 2/3, .8]:
    print(f"  share below {t:.2f}: {(core.util<t).mean():.3f}  (n={(core.util<t).sum()})")
print(f"  share at/above 1.00: {(core.util>=1).mean():.3f} (n={(core.util>=1).sum()})")
print("  district-wide enrollment/capacity (sum/sum):", round(core.ENROLLMNT.sum()/core.CAPACITY.sum(),3))
print("\nby TYPE:\n", core.groupby("TYPE").util.agg(["count","median", lambda s:(s<.5).mean()]).round(3).to_string())
# vintage: compare ENROLLMNT to CCD 2023-24
c = pd.read_csv("ccd/mdc_directory_enrollment_2324.csv", dtype={"ST_SCHID": str})
c["ID"] = c.ST_SCHID.str.replace("FL-13-","")
j = core.merge(c[["ID","SCH_NAME","enr","CHARTER_TEXT"]], on="ID", how="left")
print("\njoined to CCD on 4-digit ID:", j.enr.notna().sum(), "of", len(j), "; charter in joined:", (j.CHARTER_TEXT=="Yes").sum())
jj = j[j.enr.notna() & (j.enr>0)]
print("GIS ENROLLMNT / CCD 2023-24 enrollment: median ratio", round((jj.ENROLLMNT/jj.enr).median(),3), "; pearson r", round(np.corrcoef(jj.ENROLLMNT, jj.enr)[0,1],3))
print("share with |ratio-1|<0.02:", round(((jj.ENROLLMNT/jj.enr-1).abs()<0.02).mean(),3))
# capacity definition: compare with ABC book perm FISH cap for the nine closed schools
abc = {"4341":("Parkway ES",154,478),"4541":("Rainbow Park ES",149,542),"0081":("Lenora B. Smith ES",275,754),"5931":("Phillis Wheatley ES",142,620),
       "3541":("Robert Russa Moton ES",123,679),"4461":("Pine Villa ES",240,834),"0073":("Mandarin Lakes K-8",571,1508),"6521":("Miami Springs MS",564,1307),"6781":("Richmond Heights MS",270,914)}
print("\nNine closed schools: GIS CAPACITY/ENROLLMNT vs ABC book 2025-26 enrollment / permanent FISH capacity")
for k,(n,e,cap) in abc.items():
    r = g[g.ID==k]
    if len(r): 
        r=r.iloc[0]; print(f"  {k} {n:22s} GIS cap {r.CAPACITY:5.0f} enr {r.ENROLLMNT:5.0f} | ABC perm FISH {cap:5d} enr {e:4d} | cap ratio {r.CAPACITY/cap:.2f}")
    else: print(f"  {k} {n}: not in GIS layer")
core.to_csv("gis/mdc_gis_ems_utilization.csv", index=False)