scripts/loaders/miami/ccd_staff.ts

// Load NCES CCD school staff (teacher FTE) for Miami-Dade → teacher_fte.
// CCD codes missing / not-applicable values as negatives (skipped). A reported
// zero at an operating school is a non-report (60 of 535 in 2023-24, mostly
// charter schools whose network reports staff elsewhere) and is served as
// suppressed rather than as 0. Small positive FTEs are kept as reported; the
// metric note warns that charter-network reporting makes the derived
// students-per-teacher ratio unreliable above ~40.
// Source: nces_ccd_staff_<YYYY-YY>.  Run: npx tsx scripts/loaders/miami/ccd_staff.ts [year ...]

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

const SCRIPT_PATH = "scripts/loaders/miami/ccd_staff.ts";

async function main() {
  const years = process.argv.slice(2).length
    ? process.argv.slice(2)
    : fs.readdirSync(SOURCES_ROOT).filter((d) => /^nces_ccd_staff_\d{4}-\d{2}$/.test(d)).map((d) => d.replace("nces_ccd_staff_", "")).sort();
  const universe = await schoolUniverse();
  for (const year of years) {
    const sourceId = `nces_ccd_staff_${year}`;
    const { filePath, prov } = requireSource(sourceId);
    const rows = await streamZipCsv(filePath, /ccd_sch_059_.*\.csv$/i, (r) => r.ST_LEAID === "FL-13");
    const facts: Fact[] = [];
    let zeros = 0;
    for (const r of rows) {
      const num = padSchoolNumber(String(r.ST_SCHID).replace("FL-13-", ""));
      if (!num || !universe.has(num)) continue;
      const n = Number.parseFloat(r.TEACHERS);
      if (!Number.isFinite(n) || n < 0) continue; // CCD uses negative codes for missing/not applicable
      if (n === 0) { zeros++; facts.push({ schoolNumber: num, year, metricKey: "teacher_fte", value: null, suppressed: true }); continue; }
      facts.push({ schoolNumber: num, year, metricKey: "teacher_fte", value: n, suppressed: false });
    }
    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 }, `TEACHERS = 0 served as not reported (suppressed): ${zeros}`);
    console.log(`[ccd_staff ${year}] ${res.written} schools with teacher FTE (${zeros} reported 0 → suppressed)`);
  }
}

runMain(main);