scripts/loaders/philly/futurereadypa_performance.ts

// Load Future Ready PA Index Performance file → philly_school_year_metrics.
// Source: docs/cities/philly/sources.yaml entries `futurereadypa_performance_<year>`.
//
// The file is WIDE-FORMAT: each (measure × subgroup) is its own column.
// Schema sample (per findings.md F.2):
//   PercentProficientorAdvancedonELALiterature_AllStudent
//   PercentProficientorAdvancedonELALiterature_Black
//   MeetingAnnualAcademicGrowthExpectations_PVAASMathematicsAlgebra1_AllStudent
//   ...
// We unpivot these into one row per (school × year × metric_key × subgroup).
//
// Covers school-WIDE grain for all 302 Philly schools (district + charter +
// CTC + cyber + special-ed). For grade-by-grade detail of SDP-district +
// alt schools, see sdp_pssa_keystone.ts.
//
// Run:  npx tsx scripts/loaders/philly/futurereadypa_performance.ts [source_id] [year]

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

// Multi-year: `npx tsx scripts/loaders/philly/futurereadypa_performance.ts [source_id] [year]`
// e.g. futurereadypa_performance_2023-24 2023-24. Defaults keep the original
// 2024-25 behavior. Prior-year files share the wide <Measure>_<Subgroup>
// schema (verified from first-fetch findings before loading).
const SOURCE_ID = process.argv[2] ?? "futurereadypa_performance_2024-25";
const YEAR_COVERED = process.argv[3] ?? "2024-25";
if (!/^futurereadypa_performance_\d{4}-\d{2}$/.test(SOURCE_ID) || !/^\d{4}-\d{2}$/.test(YEAR_COVERED)) {
  throw new Error(`usage: futurereadypa_performance.ts <futurereadypa_performance_YYYY-YY> <YYYY-YY> (got ${SOURCE_ID} ${YEAR_COVERED})`);
}
if (!SOURCE_ID.endsWith(YEAR_COVERED)) {
  throw new Error(`source id ${SOURCE_ID} does not end with year ${YEAR_COVERED} — refusing to load mismatched year`);
}
const SCRIPT_PATH = "scripts/loaders/philly/futurereadypa_performance.ts";
const SHEETS_TO_READ = [
  "State Assessment Measures",   // PSSA all-grades + Keystone + PVAAS
  "School On Track Measures",    // chronic absenteeism + persistent attendance
  "College Career Measures",     // graduation rate (and v2: AP, dual enrollment, etc.)
];
// PDE suppresses groups of fewer than 20 students (reporting guidelines):
// "IS" / "Insufficient Sample" in the older workbooks. Any other non-numeric
// token is also stored as suppressed; the full QA censuses every token.
const SUPPRESS_VALUES = new Set(["IS", "INSUFFICIENT SAMPLE", "--", "INS", "*", ""]);

// Map Future Ready column-name suffixes (after underscore) to our PhillySubgroup enum.
const SUBGROUP_MAP: Record<string, PhillySubgroup> = {
  AllStudent: "ALL",
  AmericanIndianAlaskaNative: "AMER_INDIAN_AK_NATIVE",
  Asian: "ASIAN",
  HawaiianPacificIslander: "HAWAIIAN_PAC_ISL",
  Black: "BLACK",
  Hispanic: "HISPANIC",
  White: "WHITE",
  "2orMoreRaces": "TWO_OR_MORE_RACES",
  EconomicallyDisadvantaged: "ECON_DISADV",
  EnglishLearner: "ELL",
  StudentswithDisabilities: "IEP",
  CombinedEthnicity: "COMBINED_ETHNICITY",
};

// Map measure-name prefix (before _<subgroup>) to our metric_key.
// We deliberately only pull metrics we've registered in metric_definitions —
// other columns (ESSA goal, statewide average, annual progress flags) are
// metadata, not first-class metrics for the analysis engine.
const MEASURE_MAP: Record<string, string> = {
  // ── State Assessment Measures sheet ──
  PercentProficientorAdvancedonMathematicsAlgebra1: "keystone_algebra_proficiency",
  MeetingAnnualAcademicGrowthExpectations_PVAASMathematicsAlgebra1: "pvaas_growth_math_algebra1",
  PercentProficientorAdvancedonELALiterature: "keystone_literature_proficiency",
  MeetingAnnualAcademicGrowthExpectations_PVAASELALiterature: "pvaas_growth_ela_literature",
  PercentProficientorAdvancedonScienceBiology: "keystone_biology_proficiency",
  MeetingAnnualAcademicGrowthExpectations_PVAASScienceBiology: "pvaas_growth_science_biology",

  // ── School On Track Measures sheet ──
  // NOTE: PercentChronicAbsenteeism column is published in the file shape but
  // values are NULL statewide for the 2024-25 file (confirmed empirically).
  // PDE actually reports the metric as PercentPersistentAttendance (the
  // complement). For analytical comparability, we store the persistent rate
  // as-is under a separate metric_key; conversion to chronic_absent semantics
  // happens at display time if needed.
  // YEAR: this is a LAGGING indicator (PDE glossary) — the 2024-25 workbook's
  // value describes 2023-24 attendance. Stored under the observation year via
  // STATE_METRIC_YEAR_OFFSET (_lib.ts); every other measure keeps the report year.
  PercentPersistentAttendance: "attendance_persistence_rate",

  // ── College Career Measures sheet ──
  // Future Ready reports the most recent 4-year cohort rate at the school-year
  // grain (e.g. 2024-25 report = the cohort that graduated through 2023-24).
  // SDP's graduation loader writes the same metric_key but indexed by cohort
  // start year. The two don't conflict on (school, year, metric, subgroup) for
  // any actual data point. For v1 this is acceptable — analysis can filter to
  // whichever indexing convention matters. v2 will normalize.
  PercentGraduation4YearCohort: "graduation_rate_4yr",
};

type Row = Record<string, unknown>;

/** Parse a column name like "PercentProficientorAdvancedonELALiterature_Black"
 *  into a (measure_key, subgroup_suffix) pair, or null if it's not a
 *  recognized metric × subgroup column.
 *
 *  Note: some measure prefixes themselves contain an underscore (PVAAS ones
 *  like "MeetingAnnualAcademicGrowthExpectations_PVAASMathematicsAlgebra1"),
 *  so we can't just split on `_`. We try every registered measure prefix.
 */
function parseColumn(col: string): { measureKey: string; subgroup: PhillySubgroup } | null {
  for (const prefix of Object.keys(MEASURE_MAP)) {
    if (col.startsWith(prefix + "_")) {
      const suffix = col.slice(prefix.length + 1);
      const sg = SUBGROUP_MAP[suffix];
      if (sg) {
        return { measureKey: MEASURE_MAP[prefix], subgroup: sg };
      }
    }
  }
  return null;
}

function toNumber(v: unknown): number | null {
  if (v === null || v === undefined) return null;
  const s = String(v).trim();
  if (SUPPRESS_VALUES.has(s.toUpperCase())) return null;
  const n = Number.parseFloat(s);
  return Number.isFinite(n) ? n : null;
}

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}. Run: make philly-discover SOURCE=${SOURCE_ID}`,
    );
  }
  console.log(`[load] file: ${filePath}`);
  console.log(`[load] sha256: ${prov.sha256.slice(0, 12)}... (${prov.bytes.toLocaleString()} bytes)`);

  // Pull the universe of Philly ULCS codes + AUN+PA mapping (loaded by P0)
  const phillySchools = await prisma.phillySchool.findMany({
    select: { ulcsCode: true, aunCode: true, paCode: true, name: true },
  });
  console.log(`[load] philly_schools universe: ${phillySchools.length}`);
  // PDE-side composite key → ULCS code
  const pdeToUlcs = buildPdeToUlcs(phillySchools);

  const wb = XLSX.readFile(filePath, { cellDates: false });

  const dataLoad = await startDataLoad({
    sourceId: SOURCE_ID,
    sourceUrl: prov.url,
    sha256: prov.sha256,
    bytes: prov.bytes,
    yearCovered: YEAR_COVERED,
    scriptPath: SCRIPT_PATH,
  });

  // Accumulate facts (school × metric × subgroup → value) before bulk upsert.
  type Fact = {
    schoolUlcs: string;
    metricKey: string;
    year: string; // observation year (report year for most measures; see observationYear)
    subgroup: PhillySubgroup;
    value: number | null;
    suppressed: boolean;
  };
  const facts: Fact[] = [];
  let phillyHits = 0;
  let nonPhillySkipped = 0;
  let unmatchedAunSchl = 0;

  for (const sheetName of SHEETS_TO_READ) {
    const sheet = wb.Sheets[sheetName];
    if (!sheet) {
      console.warn(`[load] missing sheet: ${sheetName}`);
      continue;
    }
    const rows = XLSX.utils.sheet_to_json<Row>(sheet, { defval: null });
    console.log(`[load] sheet "${sheetName}": ${rows.length} rows`);

    // Discover the metric columns once
    const cols = Object.keys(rows[0] ?? {});
    const metricCols: { col: string; measureKey: string; subgroup: PhillySubgroup }[] = [];
    for (const c of cols) {
      const p = parseColumn(c);
      if (p) metricCols.push({ col: c, measureKey: p.measureKey, subgroup: p.subgroup });
    }
    console.log(`[load]   ${metricCols.length} recognized metric × subgroup columns`);

    for (const r of rows) {
      const aun = String(r["AUN"] ?? "").trim();
      const schl = String(r["Schl"] ?? "").trim();
      if (!aun || !schl) continue;
      const ulcs = pdeToUlcs.get(`${aun}-${schl}`);
      if (!ulcs) {
        // Not in our Philly universe; statewide file contains every PA school.
        if (aun.startsWith("1265")) unmatchedAunSchl++;  // SDP AUN but not in our directory
        else nonPhillySkipped++;
        continue;
      }
      phillyHits++;

      for (const mc of metricCols) {
        const raw = r[mc.col];
        const v = toNumber(raw);
        const suppressed = v === null && raw !== null && raw !== undefined && String(raw).trim() !== "";
        facts.push({
          schoolUlcs: ulcs,
          metricKey: mc.measureKey,
          year: observationYear(mc.measureKey, YEAR_COVERED),
          subgroup: mc.subgroup,
          value: v,
          suppressed,
        });
      }
    }
  }

  console.log(`[load] phillyHits=${phillyHits} nonPhillySkipped=${nonPhillySkipped} unmatchedSdpRow=${unmatchedAunSchl}`);
  console.log(`[load] total facts to upsert: ${facts.length.toLocaleString()}`);

  let suppressed = 0;
  await batched(facts, 200, async (batch) => {
    for (const f of batch) {
      try {
        const result = await prisma.phillySchoolYearMetric.upsert({
          where: {
            schoolUlcs_year_metricKey_subgroup_populationCut: {
              schoolUlcs: f.schoolUlcs,
              year: f.year,
              metricKey: f.metricKey,
              subgroup: f.subgroup,
              populationCut: "n/a",  // sentinel — Future Ready doesn't ship Acct/Actual cuts
            },
          },
          update: {
            value: f.value,
            suppressed: f.suppressed,
            sourceLoadId: dataLoad.id,
          },
          create: {
            schoolUlcs: f.schoolUlcs,
            year: f.year,
            metricKey: f.metricKey,
            subgroup: f.subgroup,
            populationCut: "n/a",
            value: f.value,
            suppressed: f.suppressed,
            sourceLoadId: dataLoad.id,
          },
        });
        // Count each suppressed fact ONCE (an earlier version double-counted,
        // which produced negative rows_inserted in philly_data_loads).
        if (f.suppressed || (result.value === null && result.suppressed)) suppressed++;
      } catch (e) {
        // FK errors usually mean a metric_key isn't registered — log & continue
        if (e instanceof Error && e.message.includes("Foreign key constraint")) {
          console.warn(`[load] FK fail: ${f.metricKey} → metric_definitions missing? Skipping.`);
        } else {
          throw e;
        }
      }
    }
  }, 8);

  // The counts above are loose (`inserted` = touched). Be honest about that.
  await finishDataLoad(dataLoad.id, {
    inserted: facts.length - suppressed,
    updated: 0,
    suppressed,
  });
  console.log(`[load] done — ${facts.length.toLocaleString()} facts upserted (${suppressed.toLocaleString()} suppressed)`);
}

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