scripts/loaders/miami/ell.ts

// Load FLDOE ELL membership (code LY, by school × race) → a share interval.
// pct_ell_lower_bound includes only visible counts; pct_ell_upper_bound adds
// at most 9 students per hidden race cell, capped by October membership.
// pct_ell is exact only when the interval collapses; otherwise it is withheld.
// The file lists only schools with LY students (no statewide row is all
// zeros in any fetched year), so a directory school with October membership
// and no row is served pct_ell = 0; the load note lists them.
// Run:  npx tsx scripts/loaders/miami/ell.ts

import fs from "node:fs";
import { prisma, requireSource, startDataLoad, finishDataLoad, padSchoolNumber, parseCell, schoolUniverse, upsertFacts, sheetRows, runMain, SOURCES_ROOT, type Fact } from "./_lib";
import { ellBounds } from "./ell-bounds";

const SCRIPT_PATH = "scripts/loaders/miami/ell.ts";
const norm = (s: unknown) => String(s ?? "").toLowerCase().replace(/[^a-z0-9]+/g, " ").trim();

async function main() {
  const dirs = fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_ell_membership_\d{4}-\d{2}$/.test(d)).sort();
  const universe = await schoolUniverse();
  for (const sourceId of dirs) {
    const year = sourceId.slice(-7);
    const { filePath, prov } = requireSource(sourceId);
    const { rows } = sheetRows(filePath, "School");
    const h = rows.findIndex((r) => norm(r?.[0]) === "district");
    if (h < 0) throw new Error(`${sourceId}: district header not found`);
    const header = rows[h].map(norm);
    const iSch = header.indexOf("school");
    const races = [/^white$/, /^black or african american$/, /^hispanic latino$/, /^asian$/, /^native hawaiian or other pacific islander$/, /^american indian or alaska native$/, /^two or more races$/];
    const raceCols = races.map((pattern) => header.findIndex((c) => pattern.test(c)));
    if (iSch < 0 || raceCols.some((i) => i < 0)) throw new Error(`${sourceId}: school/race columns not found`);
    const enr = new Map((await prisma.miamiSchoolYearMetric.findMany({ where: { metricKey: "enrollment_oct", year, subgroup: "ALL", suppressed: false }, select: { schoolNumber: true, value: true } })).map((r) => [r.schoolNumber, r.value ?? 0]));
    const facts: Fact[] = [];
    let dade = 0, suppressedCells = 0, uncertainSchools = 0;
    const seen = new Set<string>();
    const add = (schoolNumber: string, total: number, bounds: ReturnType<typeof ellBounds>) => {
      facts.push({ schoolNumber, year, metricKey: "pct_ell_lower_bound", value: bounds.lower, denominator: total });
      facts.push({ schoolNumber, year, metricKey: "pct_ell_upper_bound", value: bounds.upper, denominator: total });
      facts.push({ schoolNumber, year, metricKey: "pct_ell", value: bounds.exact, suppressed: bounds.exact === null, denominator: total });
    };
    for (const r of rows.slice(h + 1)) {
      if (String(r[0] ?? "").trim().replace(/\.0$/, "") !== "13") continue;
      dade++;
      const num = padSchoolNumber(r[iSch]);
      if (!num || !universe.has(num)) continue;
      const total = enr.get(num);
      if (!total) continue;
      if (seen.has(num)) throw new Error(`${sourceId}: duplicate school ${num}`);
      seen.add(num);
      const bounds = ellBounds(raceCols.map((i) => parseCell(r[i])), total);
      suppressedCells += bounds.hidden;
      if (bounds.exact === null) uncertainSchools++;
      add(num, total, bounds);
    }
    if (dade === 0) throw new Error(`${sourceId}: no Miami-Dade rows; refusing to infer zero ELL counts`);
    const absent = [...enr].filter(([num, total]) => total > 0 && !seen.has(num)).map(([num]) => num).sort();
    for (const num of absent) add(num, enr.get(num)!, ellBounds([], enr.get(num)!));
    const load = await startDataLoad({ sourceId, sourceUrl: prov.url, sha256: prov.sha256, bytes: prov.bytes, yearCovered: year, scriptPath: SCRIPT_PATH, notes: `Dade rows ${dade}; hidden race cells ${suppressedCells}; schools with interval-valued ELL share ${uncertainSchools} (pct_ell withheld, lower/upper bounds served); enrolled schools absent from the zero-omitting file served exact 0: ${absent.length} (${absent.join(" ")})` });
    const res = await upsertFacts(facts, load.id);
    await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: res.suppressed });
    console.log(`[ell ${year}] ${res.written} facts; ${uncertainSchools} schools with ELL intervals; ${absent.length} absent-file exact zeros`);
  }
}

runMain(main);