scripts/loaders/philly/futurereadypa_longformat_attendance.ts

// Load attendance from the OLDER Future Ready PA Index datafiles (2018-19,
// 2019-20, 2020-21), which use a LONG format — sheets "Schools A to L"/"Schools L to Z"
// (2018-19) or "Schools A - M"/"Schools M - Z" (2020-21), one row per
// (school, DataElement, DisplayValue) — instead of the wide <Measure>_<Subgroup>
// columns the 2021-22+ files use (scripts/loaders/philly/futurereadypa_performance.ts).
//
// Only the attendance element is loaded: "Percent Regular Attendance (<Subgroup>)"
// — PDE's earlier name for what the 2021-22+ files call
// PercentPersistentAttendance (students attending 90%+ of enrolled days).
// ASSUMPTION (documented on the methodology page): same definition under a
// renamed indicator; PDE's Future Ready documentation describes both as the
// share of students with 90%+ attendance. Stored under the same metric_key,
// attendance_persistence_rate. "IS" (insufficient sample) → suppressed.
//
// Run:  npx tsx scripts/loaders/philly/futurereadypa_longformat_attendance.ts futurereadypa_performance_2018-19 2018-19

import * as XLSX from "xlsx";
import {
  prisma,
  startDataLoad,
  finishDataLoad,
  readLatestProvenance,
  readLatestSourcePath,
  batched,
  buildPdeToUlcs,
  observationYear,
} from "./_lib";
import type { PhillySubgroup } from "@prisma/client";

const SOURCE_ID = process.argv[2];
const YEAR_COVERED = process.argv[3];
const SCRIPT_PATH = "scripts/loaders/philly/futurereadypa_longformat_attendance.ts";
if (!SOURCE_ID || !YEAR_COVERED || !SOURCE_ID.endsWith(YEAR_COVERED)) {
  throw new Error("usage: futurereadypa_longformat_attendance.ts <source_id ending in year> <YYYY-YY>");
}
const METRIC_KEY = "attendance_persistence_rate";
const ELEMENT_PREFIX = "Percent Regular Attendance";
// LAGGING indicator (PDE glossary): the workbook's report year minus one is
// the year the students were actually in school. See _lib.ts.
const OBS_YEAR = observationYear(METRIC_KEY, YEAR_COVERED);

const SUBGROUP_MAP: Record<string, PhillySubgroup> = {
  "All Student": "ALL",
  "American Indian/Alaska Native": "AMER_INDIAN_AK_NATIVE",
  Asian: "ASIAN",
  "Hawaiian/Pacific Islander": "HAWAIIAN_PAC_ISL",
  Black: "BLACK",
  Hispanic: "HISPANIC",
  White: "WHITE",
  "2 or More Races": "TWO_OR_MORE_RACES",
  "Economically Disadvantaged": "ECON_DISADV",
  "English Learner": "ELL",
  "Students with Disabilities": "IEP",
};
// "Insufficient Sample" is the 2020-21 workbook's spelling of "IS" (groups of
// fewer than 20 students, per PDE's reporting guidelines).
const SUPPRESS = new Set(["IS", "INSUFFICIENT SAMPLE", "--", "INS", "*", "", "N/A", "NA"]);

/** "Percent Regular Attendance  (English Learner)" → "English Learner" | null.
 *  Excludes "ESSA Goal ..." elements (targets, not measured values). */
function parseElement(el: string): string | null {
  const norm = el.replace(/\s+/g, " ").trim();
  if (!norm.startsWith(ELEMENT_PREFIX)) return null;
  const m = /\(([^)]+)\)\s*$/.exec(norm);
  return m ? m[1].trim() : null;
}

function toNumber(v: unknown): { value: number | null; suppressed: boolean } {
  if (v === null || v === undefined) return { value: null, suppressed: false };
  const s = String(v).trim().replace(/%$/, "");
  if (SUPPRESS.has(s.toUpperCase())) return { value: null, suppressed: s !== "" };
  const n = Number.parseFloat(s);
  return Number.isFinite(n) ? { value: n, suppressed: false } : { value: null, suppressed: true };
}

async function main() {
  const filePath = readLatestSourcePath(SOURCE_ID);
  const prov = readLatestProvenance(SOURCE_ID);
  if (!filePath || !prov) throw new Error(`No fetched source for ${SOURCE_ID}`);
  console.log(`[load] ${SOURCE_ID} (report year ${YEAR_COVERED} → stored as ${OBS_YEAR}) file: ${filePath}`);

  const phillySchools = await prisma.phillySchool.findMany({
    select: { ulcsCode: true, aunCode: true, paCode: true, name: true },
  });
  const pdeToUlcs = buildPdeToUlcs(phillySchools);

  console.log("[load] reading workbook (large long-format file; this takes a minute)…");
  const wb = XLSX.readFile(filePath, { cellDates: false, dense: true });
  const sheetNames = wb.SheetNames.filter((n) => /^Schools\b/i.test(n));
  console.log(`[load] school sheets: ${sheetNames.join(" | ")}`);

  const dataLoad = await startDataLoad({
    sourceId: SOURCE_ID,
    sourceUrl: prov.url,
    sha256: prov.sha256,
    bytes: prov.bytes,
    yearCovered: YEAR_COVERED,
    scriptPath: SCRIPT_PATH,
    notes: `long-format attendance only ('${ELEMENT_PREFIX} (<subgroup>)' → ${METRIC_KEY}); lagging indicator stored under ${OBS_YEAR}`,
  });

  type Fact = { schoolUlcs: string; subgroup: PhillySubgroup; value: number | null; suppressed: boolean };
  const facts = new Map<string, Fact>();
  let phillyRows = 0;
  const unknownSubgroup = new Set<string>();
  for (const name of sheetNames) {
    const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(wb.Sheets[name], { defval: null });
    console.log(`[load] sheet "${name}": ${rows.length} rows`);
    for (const r of rows) {
      const el = r["DataElement"];
      if (typeof el !== "string") continue;
      const sgLabel = parseElement(el);
      if (!sgLabel) continue;
      const aun = String(r["AUN"] ?? "").trim();
      const schl = String(r["Schl"] ?? "").trim();
      const ulcs = pdeToUlcs.get(`${aun}-${schl}`);
      if (!ulcs) continue;
      const sg = SUBGROUP_MAP[sgLabel];
      if (!sg) { unknownSubgroup.add(sgLabel); continue; }
      phillyRows++;
      const { value, suppressed } = toNumber(r["DisplayValue"]);
      facts.set(`${ulcs}|${sg}`, { schoolUlcs: ulcs, subgroup: sg, value, suppressed });
    }
  }
  if (unknownSubgroup.size) console.warn(`[load] unmapped subgroup labels: ${[...unknownSubgroup].join("; ")}`);
  console.log(`[load] Philly attendance rows: ${phillyRows}; distinct (school × subgroup) facts: ${facts.size}`);

  let suppressed = 0;
  await batched([...facts.values()], 200, async (batch) => {
    for (const f of batch) {
      await prisma.phillySchoolYearMetric.upsert({
        where: {
          schoolUlcs_year_metricKey_subgroup_populationCut: {
            schoolUlcs: f.schoolUlcs, year: OBS_YEAR, metricKey: METRIC_KEY,
            subgroup: f.subgroup, populationCut: "n/a",
          },
        },
        update: { value: f.value, suppressed: f.suppressed, sourceLoadId: dataLoad.id },
        create: {
          schoolUlcs: f.schoolUlcs, year: OBS_YEAR, metricKey: METRIC_KEY,
          subgroup: f.subgroup, populationCut: "n/a",
          value: f.value, suppressed: f.suppressed, sourceLoadId: dataLoad.id,
        },
      });
      if (f.suppressed) suppressed++;
    }
  }, 8);
  await finishDataLoad(dataLoad.id, { inserted: facts.size - suppressed, updated: 0, suppressed });
  console.log(`[load] done — ${facts.size} facts (${suppressed} suppressed)`);
}

main()
  .catch((e) => { console.error(e); process.exit(1); })
  .finally(() => prisma.$disconnect());