import os as _os; _os.chdir(_os.environ.get("MIAMI_PROBE_DIR", "data/cities/miami/sources/_probe"))
import pandas as pd
d = pd.read_csv("ccd/unz_ccd_sch_029_2324_w_1a_073124/ccd_sch_029_2324_w_1a_073124.csv", dtype=str, low_memory=False, encoding="latin1")
d = d[d.ST_LEAID=="FL-13"][["ST_SCHID","NCESSCH","SCH_NAME","SY_STATUS_TEXT","SCH_TYPE_TEXT","CHARTER_TEXT","LEVEL","GSLO","GSHI"]]
m = pd.read_csv("ccd/mdc_membership_2324.csv", dtype=str)
tot = m[m.TOTAL_INDICATOR=="Education Unit Total"][["ST_SCHID","STUDENT_COUNT"]].copy()
tot["enr"] = pd.to_numeric(tot.STUDENT_COUNT, errors="coerce")
x = d.merge(tot[["ST_SCHID","enr"]], on="ST_SCHID", how="left")
x = x[x.SY_STATUS_TEXT.isin(["Open","New","Added"])]
print("open schools:", len(x), " with enrollment:", x.enr.notna().sum(), " enrollment sum:", int(x.enr.sum()))
print("\nEnrollment by charter flag:\n", x.groupby("CHARTER_TEXT").enr.agg(["count","sum","median"]).to_string())
reg = x[(x.CHARTER_TEXT=="No") & (x.SCH_TYPE_TEXT=="Regular School")]
print("\nDistrict-run regular brick-and-mortar schools:", len(reg), "enrollment sum:", int(reg.enr.sum()))
bins = [-1, 0, 100, 200, 300, 400, 500, 750, 1000, 2000, 5000]
labels = ["0","1-100","101-200","201-300","301-400","401-500","501-750","751-1000","1001-2000","2001+"]
reg["bucket"] = pd.cut(reg.enr, bins=bins, labels=labels)
print("\nDistrict regular schools by size bucket x level:\n", pd.crosstab(reg.bucket, reg.LEVEL, margins=True).to_string())
ch = x[(x.CHARTER_TEXT=="Yes")]
ch["bucket"] = pd.cut(ch.enr, bins=bins, labels=labels)
print("\nCharter schools by size bucket x level:\n", pd.crosstab(ch.bucket, ch.LEVEL, margins=True).to_string())
print("\nDistrict regular schools under 300 students (excluding 0):")
small = reg[(reg.enr>0)&(reg.enr<300)].sort_values("enr")
print(small[["ST_SCHID","SCH_NAME","LEVEL","GSLO","GSHI","enr"]].to_string(index=False))
print("\nDistrict regular ES/K-8 under 400:", int(((reg.LEVEL=="Elementary")&(reg.enr>0)&(reg.enr<400)).sum()), "of", int((reg.LEVEL=="Elementary").sum()))
print("District regular MS under 500:", int(((reg.LEVEL=="Middle")&(reg.enr>0)&(reg.enr<500)).sum()), "of", int((reg.LEVEL=="Middle").sum()))
print("District regular HS under 800:", int(((reg.LEVEL=="High")&(reg.enr>0)&(reg.enr<800)).sum()), "of", int((reg.LEVEL=="High").sum()))
# race composition district-wide
r = m[m.TOTAL_INDICATOR=="Derived - Subtotal by Race/Ethnicity and Sex minus Adult Education Count"].copy()
r["n"] = pd.to_numeric(r.STUDENT_COUNT, errors="coerce")
comp = r.groupby("RACE_ETHNICITY").n.sum()
print("\nRace/ethnicity composition (all MDC schools):\n", (comp/comp.sum()*100).round(1).to_string())
# grade PK-12 totals
g = m[m.TOTAL_INDICATOR=="Subtotal 4 - By Grade"].copy(); g["n"]=pd.to_numeric(g.STUDENT_COUNT, errors="coerce")
print("\nBy grade:\n", g.groupby("GRADE").n.sum().astype(int).to_string())
x.to_csv("ccd/mdc_directory_enrollment_2324.csv", index=False)