scripts/loaders/miami/graduation.ts

// Load FLDOE federal uniform graduation rates (race file + category file)
// → graduation_rate_4yr by subgroup, cohort size as the denominator.
// Rows carrying a Virtual Provider Number are provider breakouts and are
// skipped (the school's own row has none).
// Run:  npx tsx scripts/loaders/miami/graduation.ts [year ...]

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

const SCRIPT_PATH = "scripts/loaders/miami/graduation.ts";
const norm = (s: unknown) => String(s ?? "").toLowerCase().replace(/[^a-z0-9/]+/g, " ").trim();
const GROUPS: [string, MiamiSubgroup][] = [
  ["total", "ALL"], ["white", "WHITE"], ["black or african american", "BLACK"], ["hispanic/latino", "HISPANIC"], ["asian", "ASIAN"],
  ["american indian or alaska native", "AMER_INDIAN_AK_NATIVE"], ["two or more races", "TWO_OR_MORE_RACES"], ["native hawaiian or other pacific islander", "HAWAIIAN_PAC_ISL"],
  ["ese", "SWD"], ["free/reduced lunch", "ECON_DISADV"], ["ell", "ELL"], ["migrant", "MIGRANT"], ["at risk", "AT_RISK"], ["male", "MALE"], ["female", "FEMALE"],
];

async function loadFile(sourceId: string, year: string, universe: Set<string>) {
  const { filePath, prov } = requireSource(sourceId);
  const { rows } = sheetRows(filePath);
  const h = rows.findIndex((r) => norm(r?.[0]) === "district number");
  if (h < 0) throw new Error(`${sourceId}: header not found`);
  const header = rows[h].map(norm);
  const iSch = header.indexOf("school number"), iProv = header.indexOf("virtual provider number");
  const cols: { sg: MiamiSubgroup; iRate: number; iCohort: number }[] = [];
  for (const [label, sg] of GROUPS) {
    const iRate = header.findIndex((c) => c === `${label} graduation rate` || c === `${label} federal graduation rate`);
    const iCohort = header.findIndex((c) => c === `${label} cohort`);
    if (iRate >= 0 && iCohort >= 0) cols.push({ sg, iRate, iCohort });
  }
  const facts: Fact[] = [];
  let dade = 0, skippedProvider = 0;
  for (const r of rows.slice(h + 1)) {
    if (String(r[0] ?? "").trim().replace(/\.0$/, "") !== "13") continue;
    dade++;
    if (iProv >= 0 && r[iProv] !== null && String(r[iProv]).trim() !== "") { skippedProvider++; continue; }
    const num = padSchoolNumber(r[iSch]);
    if (!num || !universe.has(num)) continue;
    for (const c of cols) {
      const rate = parseCell(r[c.iRate]), cohort = parseCell(r[c.iCohort]);
      if (rate.value === null && !rate.suppressed) continue;
      facts.push({ schoolNumber: num, year, metricKey: "graduation_rate_4yr", subgroup: c.sg, value: rate.value, suppressed: rate.suppressed, denominator: cohort.value });
    }
  }
  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);
  await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: res.suppressed }, `Dade rows ${dade}; provider breakout rows skipped ${skippedProvider}; subgroups ${cols.map((c) => c.sg).join(",")}`);
  console.log(`[graduation ${year} ${sourceId.includes("race") ? "race" : "category"}] ${res.written} facts (${res.suppressed} suppressed); Dade rows ${dade}; provider rows skipped ${skippedProvider}`);
}

async function main() {
  const dirs = fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_fed_grad_rate_(race|category)_\d{4}-\d{2}$/.test(d)).sort();
  const wanted = process.argv.slice(2);
  const universe = await schoolUniverse();
  for (const sid of dirs) {
    const year = sid.slice(-7);
    if (wanted.length && !wanted.includes(year)) continue;
    await loadFile(sid, year, universe);
  }
}

runMain(main);