scripts/loaders/miami/membership.ts

// Load FLDOE "Membership by District and School by Grade" (Survey 2, October)
// → miami_school_year_metrics: enrollment_oct (total) and enrollment_oct_g<grade>.
//
// One source per year: fldoe_membership_by_school_grade_<YYYY-YY>. Sheet
// order, header row, and blank/zero conventions differ by year
// (findings.md F.3); the header row is located by its first cell.
// Suppression tokens ('*', '**') on a grade cell → value null, suppressed.
// The school total comes from the "…Total Enrollment/Membership" sheet
// (present in every year; unsuppressed there), else from the sum of grade cells.
//
// Run:  npx tsx scripts/loaders/miami/membership.ts [year ...]   (default: every fetched year)

import fs from "node:fs";
import {
  requireSource, startDataLoad, finishDataLoad, padSchoolNumber, parseCell, TokenCensus,
  schoolUniverse, upsertFacts, sheetRows, findHeaderRow, runMain, SOURCES_ROOT, type Fact,
} from "./_lib";

const SCRIPT_PATH = "scripts/loaders/miami/membership.ts";
const GRADES = ["PK", "KG", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"];

function fetchedYears(): string[] {
  return fs.readdirSync(SOURCES_ROOT)
    .filter((d) => /^fldoe_membership_by_school_grade_\d{4}-\d{2}$/.test(d))
    .map((d) => d.replace("fldoe_membership_by_school_grade_", ""))
    .sort();
}

async function loadYear(year: string, universe: Set<string>) {
  const sourceId = `fldoe_membership_by_school_grade_${year}`;
  const { filePath, prov } = requireSource(sourceId);
  const { rows: schoolRows, sheetNames } = sheetRows(filePath, "School");
  const h = findHeaderRow(schoolRows, "District #");
  // Header spelling varies by year ("District #" vs "DISTRICT #"); compare case-insensitively.
  const header = schoolRows[h].map((c) => (c === null ? "" : String(c).replace(/\.0$/, "").trim().toUpperCase()));
  const iDist = header.indexOf("DISTRICT #");
  const iSch = header.indexOf("SCHOOL #");
  const gradeIdx: Record<string, number> = {};
  for (const g of GRADES) {
    const i = header.findIndex((c) => c.toUpperCase() === g);
    if (i >= 0) gradeIdx[g] = i;
  }
  if (iDist < 0 || iSch < 0 || Object.keys(gradeIdx).length < GRADES.length) {
    throw new Error(`${sourceId}: unexpected header ${JSON.stringify(header)}`);
  }
  // Total sheet (name varies: "Schl Total Membership" 2016-17, "School Total Membership" 2017-18,
  // "Schl/School Total Enrollment" from 2018-19); its value column is "Total Membership" or "# of Students".
  const totalSheet = sheetNames.find((n) => /total (enrollment|membership)/i.test(n));
  if (!totalSheet) throw new Error(`${sourceId}: no total sheet among ${JSON.stringify(sheetNames)}`);
  const totals = new Map<string, { value: number | null; suppressed: boolean }>();
  if (totalSheet) {
    const { rows: tRows } = sheetRows(filePath, totalSheet);
    const th = findHeaderRow(tRows, "District #");
    const tHeader = tRows[th].map((c) => (c === null ? "" : String(c).trim().toUpperCase()));
    const tDist = tHeader.indexOf("DISTRICT #");
    const tSch = tHeader.indexOf("SCHOOL #");
    const tVal = tHeader.findIndex((c) => /students|membership/i.test(c));
    if (tDist < 0 || tSch < 0 || tVal < 0) throw new Error(`${sourceId}: unexpected total-sheet header ${JSON.stringify(tHeader)}`);
    for (const r of tRows.slice(th + 1)) {
      if (String(r[tDist] ?? "").replace(/\.0$/, "").trim() !== "13") continue;
      const num = padSchoolNumber(r[tSch]);
      if (!num) continue;
      const p = parseCell(r[tVal]);
      totals.set(num, { value: p.value, suppressed: p.suppressed });
    }
  }

  const census = new TokenCensus();
  const facts: Fact[] = [];
  let dadeRows = 0;
  let unknown = 0;
  const unknownExamples: string[] = [];
  for (const r of schoolRows.slice(h + 1)) {
    if (String(r[iDist] ?? "").replace(/\.0$/, "").trim() !== "13") continue;
    dadeRows++;
    const num = padSchoolNumber(r[iSch]);
    if (!num) continue;
    if (!universe.has(num)) {
      unknown++;
      if (unknownExamples.length < 8) unknownExamples.push(`${num} ${String(r[iSch + 1] ?? "")}`);
      continue;
    }
    // Grade cells: a number, blank (grade not served → 0), or a token ('*', '**').
    // The token means "fewer than 10 students" in most years, but the 2022-23
    // file uses '*' for grades a school does not serve at all. Decide per row
    // from the data: if the numeric grade cells already sum to the school's
    // published total, the token cells hold nobody (0, not suppressed);
    // otherwise they hide students and stay suppressed.
    const parsed = GRADES.map((g) => ({ g, p: parseCell(r[gradeIdx[g]]) }));
    for (const { g, p } of parsed) census.add(p.token, g);
    const numericSum = parsed.reduce((s, { p }) => s + (p.value ?? 0), 0);
    const t = totals.get(num);
    const tokensAreZero = parsed.some(({ p }) => p.suppressed) && t?.value !== null && t?.value !== undefined && Math.abs(numericSum - t.value) < 0.5;
    let sum = 0;
    let anySuppressed = false;
    for (const { g, p } of parsed) {
      const suppressed = p.suppressed && !tokensAreZero;
      if (suppressed) anySuppressed = true;
      const v = p.value ?? (suppressed ? null : 0);
      facts.push({ schoolNumber: num, year, metricKey: `enrollment_oct_g${g}`, value: v, suppressed });
      if (v !== null) sum += v;
    }
    if (t && t.value !== null) {
      facts.push({ schoolNumber: num, year, metricKey: "enrollment_oct", value: t.value, suppressed: false });
    } else if (!anySuppressed) {
      facts.push({ schoolNumber: num, year, metricKey: "enrollment_oct", value: sum, suppressed: false });
    } else {
      // Total unavailable and at least one grade suppressed: the sum would be a floor, so mark suppressed.
      facts.push({ schoolNumber: num, year, metricKey: "enrollment_oct", value: null, suppressed: true });
    }
  }
  const load = await startDataLoad({ sourceId, sourceUrl: prov.url, sha256: prov.sha256, bytes: prov.bytes, yearCovered: year, scriptPath: SCRIPT_PATH });
  const res = await upsertFacts(facts, load.id);
  const note = `Dade rows ${dadeRows}; not in directory ${unknown}${unknownExamples.length ? ` (e.g. ${unknownExamples.join("; ")})` : ""}; total sheet: ${totalSheet ?? "none"}; tokens: ${census}`;
  await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: res.suppressed }, note);
  console.log(`[membership ${year}] ${res.written} facts (${res.suppressed} suppressed); ${note}`);
}

async function main() {
  const years = process.argv.slice(2).length ? process.argv.slice(2) : fetchedYears();
  const universe = await schoolUniverse();
  console.log(`[membership] universe ${universe.size} schools; years: ${years.join(", ")}`);
  for (const y of years) await loadYear(y, universe);
}

runMain(main);