scripts/loaders/miami/essa_ppe.ts

// Load FLDOE ESSA school per-pupil expenditures → per_pupil_expenditure_school
// (school costs, all funds), per_pupil_expenditure_school_federal, and
// per_pupil_expenditure_total (school + district indirect). Column headers
// repeat ("State and Local Funds" appears under two group headings), so the
// loader reads by position after locating the header row (findings.md F.8).
// Run:  npx tsx scripts/loaders/miami/essa_ppe.ts [year ...]

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

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

async function main() {
  const years = process.argv.slice(2).length
    ? process.argv.slice(2)
    : fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_essa_ppe_\d{4}-\d{2}$/.test(d)).map((d) => d.replace("fldoe_essa_ppe_", "")).sort();
  const universe = await schoolUniverse();
  for (const year of years) {
    const sourceId = `fldoe_essa_ppe_${year}`;
    const { filePath, prov } = requireSource(sourceId);
    const { rows } = sheetRows(filePath);
    // Header spelling changed in 2023-24 ("District Number/School Number" → "District Code/School Code");
    // the money columns are positional in every year: 4-6 school costs (state+local, federal, total),
    // 7-9 district indirect (same three), 10 total.
    let h = -1;
    for (let i = 0; i < Math.min(12, rows.length); i++) {
      const c0 = String(rows[i]?.[0] ?? ""), c2 = String(rows[i]?.[2] ?? "");
      if (/^district (code|number)$/i.test(c0.trim()) && /^school (code|number)$/i.test(c2.trim())) { h = i; break; }
    }
    if (h < 0) throw new Error(`${sourceId}: header row not found`);
    const header = rows[h].map((c) => String(c ?? "").replace(/\s+/g, " ").trim());
    if (header.length < 11 || !/state, local and federal/i.test(header[6])) throw new Error(`${sourceId}: unexpected header ${JSON.stringify(header)}`);
    const facts: Fact[] = [];
    let dade = 0, unknown = 0;
    for (const r of rows.slice(h + 1)) {
      if (String(r[0] ?? "").trim().replace(/\.0$/, "") !== "13") continue;
      dade++;
      const num = padSchoolNumber(r[2]);
      if (!num) continue;
      if (!universe.has(num)) { unknown++; continue; }
      const school = parseCell(r[6]), fed = parseCell(r[5]), total = parseCell(r[10]);
      if (school.value !== null) facts.push({ schoolNumber: num, year, metricKey: "per_pupil_expenditure_school", value: school.value });
      if (fed.value !== null) facts.push({ schoolNumber: num, year, metricKey: "per_pupil_expenditure_school_federal", value: fed.value });
      if (total.value !== null) facts.push({ schoolNumber: num, year, metricKey: "per_pupil_expenditure_total", value: total.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: 0 }, `Dade rows ${dade}; not in directory ${unknown}`);
    console.log(`[ppe ${year}] ${res.written} facts; Dade rows ${dade}; not in directory ${unknown}`);
  }
}

runMain(main);