scripts/loaders/miami/absenteeism.ts

// Load FLDOE "Absent 21+ Days and Absent 10% or More" (2017-18 →) and the
// older "Absent 21+ Days" files (≤ 2016-17) → chronic_absenteeism_rate (10%
// definition) and absent_21plus_rate, with their denominators. Values in the
// files are fractions; stored as percent.
// Run:  npx tsx scripts/loaders/miami/absenteeism.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/absenteeism.ts";
const norm = (s: unknown) => String(s ?? "").toLowerCase().replace(/[^a-z0-9%]+/g, " ").trim();

async function main() {
  const dirs = fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_absent21_(10pct_)?\d{4}-\d{2}$/.test(d));
  const wanted = process.argv.slice(2);
  const universe = await schoolUniverse();
  for (const sourceId of dirs.sort()) {
    const year = sourceId.slice(-7);
    if (wanted.length && !wanted.includes(year)) continue;
    const { filePath, prov } = requireSource(sourceId);
    const { sheetNames } = sheetRows(filePath);
    const sheet = sheetNames.find((n) => /school/i.test(n)) ?? sheetNames[sheetNames.length - 1];
    const { rows } = sheetRows(filePath, sheet);
    const h = rows.findIndex((r) => norm(r?.[0]) === "district");
    if (h < 0) throw new Error(`${sourceId}: header row not found in sheet ${sheet}`);
    const header = rows[h].map(norm);
    const find = (re: RegExp) => header.findIndex((c) => re.test(c));
    const iDist = 0, iSch = find(/^school$/) >= 0 ? find(/^school$/) : 2;
    const iEnr = find(/total enrollment|^enrollments?$/);
    const iN21 = find(/^(of students )?absent 21 or more days$/), iP21 = find(/^% of students absent 21|^% absent 21/);
    const iEnr10 = find(/enrolled 10 or more days/), iN10 = find(/^(of students )?absent 10% or more$/), iP10 = find(/^% of students absent 10%/);
    const facts: Fact[] = [];
    let dade = 0, unknown = 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) continue;
      if (!universe.has(num)) { unknown++; continue; }
      const enr = parseCell(r[iEnr]), p21 = parseCell(r[iP21]);
      if (p21.value !== null || p21.suppressed) facts.push({ schoolNumber: num, year, metricKey: "absent_21plus_rate", value: p21.value === null ? null : p21.value * 100, suppressed: p21.suppressed, denominator: enr.value });
      void iN21;
      if (iP10 >= 0) {
        const enr10 = parseCell(r[iEnr10]), p10 = parseCell(r[iP10]);
        if (p10.value !== null || p10.suppressed) facts.push({ schoolNumber: num, year, metricKey: "chronic_absenteeism_rate", value: p10.value === null ? null : p10.value * 100, suppressed: p10.suppressed, denominator: enr10.value });
        void iN10;
      }
    }
    const load = await startDataLoad({ sourceId, sourceUrl: prov.url, sha256: prov.sha256, bytes: prov.bytes, yearCovered: year, scriptPath: SCRIPT_PATH,
      notes: `${sourceId === "fldoe_absent21_2016-17" ? "FLDOE marks this file as inaccurate due to missed reporting timelines. " : ""}Dade rows ${dade}; not in directory ${unknown}; denominators are Survey 5 annual enrollment` });
    const res = await upsertFacts(facts, load.id);
    await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: res.suppressed });
    console.log(`[absenteeism ${year}] ${res.written} facts (${res.suppressed} suppressed); Dade rows ${dade}; unknown ${unknown}`);
  }
}

runMain(main);