scripts/loaders/miami/school_grades.ts

// Load FLDOE School Grades files → school-grade components, points, the
// letter (as an index), and the economic-disadvantage share used as a
// covariate. One source per year: fldoe_school_grades_<YYYY-YY>.
// Components are blank (not '*') when n<10 (findings.md F.6).
// Run:  npx tsx scripts/loaders/miami/school_grades.ts [year ...]

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

const SCRIPT_PATH = "scripts/loaders/miami/school_grades.ts";
const norm = (s: unknown) => String(s ?? "").toLowerCase().replace(/[^a-z0-9]+/g, " ").trim();
// header (normalized, startsWith) → metric key
const COMPONENTS: [string, string][] = [
  ["english language arts achievement", "ela_achievement"],
  ["english language arts learning gains of the lowest 25", "learning_gains_ela_l25"],
  ["english language arts learning gains", "learning_gains_ela"],
  ["mathematics achievement", "math_achievement"],
  ["mathematics learning gains of the lowest 25", "learning_gains_math_l25"],
  ["mathematics learning gains", "learning_gains_math"],
  ["science achievement", "science_achievement"],
  ["social studies achievement", "social_studies_achievement"],
  ["middle school acceleration", "middle_school_acceleration"],
  ["college and career acceleration", "college_career_acceleration"],
  ["percent of total possible points", "school_grade_points_pct"],
  ["percent of economically disadvantaged students", "pct_econ_disadv"],
];
const LETTER_INDEX: Record<string, number> = { A: 4, B: 3, C: 2, D: 1, F: 0 };

async function main() {
  const years = process.argv.slice(2).length
    ? process.argv.slice(2)
    : fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_school_grades_\d{4}-\d{2}$/.test(d)).map((d) => d.replace("fldoe_school_grades_", "")).sort();
  const latestAvailable = fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_school_grades_\d{4}-\d{2}$/.test(d)).map((d) => d.replace("fldoe_school_grades_", "")).sort().at(-1);
  const universe = await schoolUniverse();
  for (const year of years) {
    const sourceId = `fldoe_school_grades_${year}`;
    const { filePath, prov } = requireSource(sourceId);
    // 2022-23 splits schools across Elementary / Middle / High / Combination sheets whose column
    // layouts differ (acceleration, graduation and college-readiness columns appear only on some),
    // so every sheet is read with its own header and column indexes. Other years use one sheet.
    const { sheetNames } = sheetRows(filePath);
    const endYear = 2000 + Number(year.slice(5));
    const facts: Fact[] = [];
    let dade = 0, unknown = 0, letters = 0, sheetsRead = 0;
    const gradeUpdates: { num: string; letter: string }[] = [];
    const compsFound = new Set<string>();
    for (const sh of sheetNames) {
    const { rows: r } = sheetRows(filePath, sh);
    const h = r.findIndex((x) => norm(x?.[0]) === "district number");
    if (h < 0) continue;
    sheetsRead++;
    const rows = r;
    const header = rows[h].map(norm);
    const col = (prefix: string) => header.findIndex((c) => c.startsWith(prefix));
    const iDist = col("district number"), iSch = col("school number"), iProv = col("virtual provider number");
    const iGrade = header.findIndex((c) => c === `grade ${endYear}` || c === `informational baseline grade ${endYear}`);
    const iCharter = col("charter school"), iTitle = col("title i");
    const comps = COMPONENTS.map(([prefix, key]) => [col(prefix), key] as const).filter(([i]) => i >= 0);
    for (const [, k] of comps) compsFound.add(k);
    for (const r of rows.slice(h + 1)) {
      if (String(r[iDist] ?? "").trim().replace(/\.0$/, "") !== "13") continue;
      dade++;
      if (iProv >= 0 && r[iProv] !== null && String(r[iProv]).trim() !== "") continue; // virtual-provider breakout rows
      const num = padSchoolNumber(r[iSch]);
      if (!num) continue;
      if (!universe.has(num)) { unknown++; continue; }
      for (const [i, key] of comps) {
        const p = parseCell(r[i]);
        if (p.value === null && !p.suppressed) continue; // blank = not reported (n<10 or not applicable)
        facts.push({ schoolNumber: num, year, metricKey: key, value: p.value, suppressed: p.suppressed });
      }
      if (iGrade >= 0) {
        const letter = String(r[iGrade] ?? "").trim().toUpperCase();
        if (letter in LETTER_INDEX) { facts.push({ schoolNumber: num, year, metricKey: "school_grade_letter_index", value: LETTER_INDEX[letter] }); letters++; gradeUpdates.push({ num, letter }); }
      }
      void iCharter; void iTitle;
    }
    }
    if (sheetsRead === 0) throw new Error(`${sourceId}: header row not found in any sheet`);
    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);
    // Refresh the school record in the same transaction as its source facts.
    if (year === latestAvailable) {
      for (const g of gradeUpdates) stageLoadOperation(load.id, (tx) => tx.miamiSchool.update({ where: { schoolNumber: g.num }, data: { currentSchoolGrade: g.letter } }));
    }
    await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: res.suppressed }, `Dade rows ${dade}; not in directory ${unknown}; letter grades ${letters}; sheets ${sheetsRead}; components found: ${[...compsFound].join(",")}`);
    console.log(`[school_grades ${year}] ${res.written} facts; Dade rows ${dade}; letters ${letters}; unknown ${unknown}`);

  }
}

runMain(main);