import os as _os; _os.chdir(_os.environ.get("MIAMI_PROBE_DIR", "data/cities/miami/sources/_probe"))
import pandas as pd, sys
base = "ccd/unz_ccd_sch_029_2324_w_1a_073124/ccd_sch_029_2324_w_1a_073124.csv"
d = pd.read_csv(base, dtype=str, low_memory=False, encoding="latin1")
print("directory columns:", list(d.columns))
fl = d[(d.ST=="FL")]
mdc = fl[fl.LEA_NAME.str.contains("DADE", case=False, na=False)]
print("\nLEA rows containing DADE:", mdc[["LEAID","ST_LEAID","LEA_NAME"]].drop_duplicates().to_dict("records"))
m = fl[fl.ST_LEAID=="FL-13"]
print("\nMiami-Dade (ST_LEAID FL-13) schools:", len(m), " LEAID:", m.LEAID.unique())
for col in ["SY_STATUS_TEXT","SCH_TYPE_TEXT","CHARTER_TEXT","LEVEL","VIRTUAL_TEXT","UPDATED_STATUS_TEXT"]:
    if col in m: print("\n", col, "\n", m[col].value_counts(dropna=False).to_string())
open_ = m[m.SY_STATUS_TEXT.str.contains("Open|New|Reopened|Changed", na=False)]
print("\nopen-ish schools:", len(open_))
print("\ncrosstab charter x level (open-ish):\n", pd.crosstab(open_.CHARTER_TEXT, open_.LEVEL).to_string())
print("\ncrosstab charter x SCH_TYPE (open-ish):\n", pd.crosstab(open_.CHARTER_TEXT, open_.SCH_TYPE_TEXT).to_string())
print("\nST_SCHID samples:", m.ST_SCHID.head(8).tolist())
print("NCESSCH samples:", m.NCESSCH.head(4).tolist())
print("grade span cols:", [c for c in m.columns if c.startswith("G_") or c in ("GSLO","GSHI")][:30])
print("GSLO/GSHI sample:", m[["GSLO","GSHI"]].drop_duplicates().head(15).values.tolist())
# state school number pattern
st = m.ST_SCHID.str.extract(r"FL-13-(\d+)")[0]
print("state school number length dist:", st.str.len().value_counts().to_dict())
print("charter school-number ranges:", pd.to_numeric(open_[open_.CHARTER_TEXT=="Yes"].ST_SCHID.str.extract(r"FL-13-(\d+)")[0]).describe()[["min","25%","50%","75%","max"]].to_dict())
print("non-charter school-number ranges:", pd.to_numeric(open_[open_.CHARTER_TEXT=="No"].ST_SCHID.str.extract(r"FL-13-(\d+)")[0]).describe()[["min","25%","50%","75%","max"]].to_dict())

ch = pd.read_csv("ccd/unz_ccd_sch_059_2324_l_1a_073124/ccd_sch_059_2324_l_1a_073124.csv", dtype=str, low_memory=False, encoding="latin1")
print("\ncharacteristics columns:", list(ch.columns))
chm = ch[ch.ST_LEAID=="FL-13"]
print("char rows for MDC:", len(chm))
for col in ["TITLEI_STATUS_TEXT","MAGNET_TEXT","VIRTUAL_TEXT","SHARED_TIME","NSLP_STATUS_TEXT"]:
    if col in chm: print("\n", col, "\n", chm[col].value_counts(dropna=False).to_string())

lu = pd.read_csv("ccd/unz_ccd_sch_033_2324_l_1a_073124/ccd_sch_033_2324_l_1a_073124.csv", dtype=str, low_memory=False, encoding="latin1")
print("\nlunch columns:", list(lu.columns))
lum = lu[lu.ST_LEAID=="FL-13"]
print("lunch rows for MDC:", len(lum))
for col in ["LUNCH_PROGRAM","DATA_GROUP"]:
    if col in lum: print("\n", col, "\n", lum[col].value_counts(dropna=False).head(12).to_string())
print(lum.head(6).to_string())