scripts/loaders/miami/srs_assessments.ts

// Load FLDOE State Report of Schools (SRS) assessment files → per-grade /
// per-subject proficiency (percent level 3 or above) with the number of
// students as the denominator. One file per subject × grade × administration;
// spring files only (pipeline_miami/discover_srs.py registers them).
// Run:  npx tsx scripts/loaders/miami/srs_assessments.ts

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/srs_assessments.ts";
const norm = (s: unknown) => String(s ?? "").toLowerCase().replace(/[^a-z0-9]+/g, " ").trim();

/** source_id → { metricKey, year }. Handles both the discovered naming and the three first-fetch ids. */
function classify(sourceId: string): { metricKey: string; year: string } | null {
  let m = /^fldoe_srs_(fast_ela|fast_math|science)_(g\d+)_spring(\d{4})$/.exec(sourceId);
  if (m) return { metricKey: `${m[1]}_${m[2]}_proficiency`, year: `${Number(m[3]) - 1}-${m[3].slice(2)}` };
  m = /^fldoe_srs_eoc_(\w+)_spring(\d{4})$/.exec(sourceId);
  if (m) return { metricKey: `eoc_${m[1]}_proficiency`, year: `${Number(m[2]) - 1}-${m[2].slice(2)}` };
  const legacy: Record<string, { metricKey: string; year: string }> = {
    "fldoe_fast_ela_g3_srs_spring2025": { metricKey: "fast_ela_g3_proficiency", year: "2024-25" },
    "fldoe_best_alg1_srs_spring2025": { metricKey: "eoc_alg1_proficiency", year: "2024-25" },
    "fldoe_science_g5_srs_spring2023": { metricKey: "science_g5_proficiency", year: "2022-23" },
  };
  return legacy[sourceId] ?? null;
}

async function main() {
  const dirs = fs.readdirSync(SOURCES_ROOT).filter((d) => classify(d) !== null).sort();
  const universe = await schoolUniverse();
  for (const sourceId of dirs) {
    const { metricKey, year } = classify(sourceId)!;
    let src;
    try { src = requireSource(sourceId); } catch { console.warn(`[srs] ${sourceId}: not fetched; skipped`); continue; }
    const { rows } = sheetRows(src.filePath);
    // The header row is the first row containing a "District Number" cell.
    // 2024+ files put it in column 0; the 2023 science files put "Grade"
    // in column 0 and "District Number" in column 1, so locate the column
    // rather than assuming it.
    const h = rows.findIndex((r) => Array.isArray(r) && r.some((c) => norm(c) === "district number"));
    if (h < 0) { console.warn(`[srs] ${sourceId}: header not found; skipped`); continue; }
    const header = rows[h].map(norm);
    const iDist = header.findIndex((c) => c === "district number");
    const iSch = header.findIndex((c) => c === "school number");
    const iN = header.findIndex((c) => c.startsWith("number of students"));
    const iP = header.findIndex((c) => c.startsWith("percentage in level 3") || c.startsWith("percent in level 3") || c.includes("level 3 or above"));
    if (iSch < 0 || iP < 0) { console.warn(`[srs] ${sourceId}: columns not found in ${JSON.stringify(header)}; skipped`); continue; }
    const facts: Fact[] = [];
    let dade = 0;
    for (const r of rows.slice(h + 1)) {
      if (String(r[iDist] ?? "").trim().replace(/\.0$/, "") !== "13") continue;
      dade++;
      const num = padSchoolNumber(r[iSch]);
      if (!num || !universe.has(num)) continue;
      const p = parseCell(r[iP]), n = iN >= 0 ? parseCell(r[iN]) : { value: null };
      if (p.value === null && !p.suppressed) continue;
      facts.push({ schoolNumber: num, year, metricKey, value: p.value, suppressed: p.suppressed, denominator: n.value });
    }
    const load = await startDataLoad({ sourceId, sourceUrl: src.prov.url, sha256: src.prov.sha256, bytes: src.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}`);
    console.log(`[srs] ${sourceId.padEnd(40)} ${metricKey.padEnd(28)} ${year} ${res.written} facts (${res.suppressed} suppressed)`);
  }
}

runMain(main);