scripts/loaders/philly/sdp_graduation.ts

// Load SDP graduation rates CSV → philly_school_year_metrics.
// Source: sdp_graduation_school_2014-2025 (single CSV covering 2010-11 cohort
// onwards, with 4/5/6-year graduation rates × subgroup).
//
// Cohort convention: `cohort` field is the 9th-grade-start school year.
// 4-year graduation rate for cohort 2017-18 → graduated by 2020-21 school
// year. We index metrics by the cohort year (matches SDP/SPREE convention).
//
// Run:  npx tsx scripts/loaders/philly/sdp_graduation.ts

import * as fs from "node:fs";
import { parse } from "csv-parse/sync";
import {
  prisma,
  startDataLoad,
  finishDataLoad,
  readLatestProvenance,
  readLatestSourcePath,
  batched,
} from "./_lib";
import type { PhillySubgroup } from "@prisma/client";

const SOURCE_ID = "sdp_graduation_school_2014-2025";
const SCRIPT_PATH = "scripts/loaders/philly/sdp_graduation.ts";

const GROUP_MAP: Record<string, PhillySubgroup> = {
  "All Students": "ALL",
  "American Indian/Alaskan Native": "AMER_INDIAN_AK_NATIVE",
  "Asian": "ASIAN",
  "Native Hawaiian/Pacific Islander": "HAWAIIAN_PAC_ISL",
  "Black/African American": "BLACK",
  "Hispanic/Latino": "HISPANIC",
  "White": "WHITE",
  "Multi Racial/Other": "TWO_OR_MORE_RACES",
  "Economically Disadvantaged": "ECON_DISADV",
  "Not Economically Disadvantaged": "NOT_ECON_DISADV",
  "EL": "ELL",
  "Non-EL": "NOT_ELL",
  "Has IEP": "IEP",
  "Does Not Have IEP": "NOT_IEP",
  "Female": "FEMALE",
  "Male": "MALE",
  "Non-Binary": "NON_BINARY",
};

function rateTypeToMetric(rateType: string): string | null {
  const t = rateType.trim();
  if (t === "4-Year Graduation Rate") return "graduation_rate_4yr";
  if (t === "5-Year Graduation Rate") return "graduation_rate_5yr";
  if (t === "6-Year Graduation Rate") return "graduation_rate_6yr";
  return null;
}

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

type Row = Record<string, string>;

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

  const phillySchools = await prisma.phillySchool.findMany({ select: { ulcsCode: true } });
  const ulcsSet = new Set(phillySchools.map((s) => s.ulcsCode));
  console.log(`[load] philly_schools universe: ${ulcsSet.size}`);

  const rows = parse(fs.readFileSync(filePath, "utf-8"), {
    columns: true,
    skip_empty_lines: true,
    trim: true,
  }) as Row[];
  console.log(`[load] CSV rows: ${rows.length.toLocaleString()}`);

  const dataLoad = await startDataLoad({
    sourceId: SOURCE_ID,
    sourceUrl: prov.url,
    sha256: prov.sha256,
    bytes: prov.bytes,
    yearCovered: "2010-11..2021-22",
    scriptPath: SCRIPT_PATH,
  });

  type Fact = {
    schoolUlcs: string;
    year: string;
    metricKey: string;
    subgroup: PhillySubgroup;
    value: number | null;
    denom: number | null;
    suppressed: boolean;
  };
  const facts: Fact[] = [];
  let hits = 0;
  let skipped = 0;

  for (const r of rows) {
    const ulcs = r["schoolid_ulcs"]?.trim();
    if (!ulcs || !ulcsSet.has(ulcs)) { skipped++; continue; }
    const mk = rateTypeToMetric(r["rate_type"] ?? "");
    if (!mk) { skipped++; continue; }
    const subgroup = GROUP_MAP[r["subgroup"]?.trim() ?? ""];
    if (!subgroup) { skipped++; continue; }
    // cohort is "2017-2018" — normalize to "2017-18" canonical form
    const cohort = (r["cohort"] ?? "").trim();
    const m = cohort.match(/^(\d{4})-\d{2}(\d{2})$/);
    if (!m) { skipped++; continue; }
    const year = `${m[1]}-${m[2]}`;
    hits++;
    facts.push({
      schoolUlcs: ulcs,
      year,
      metricKey: mk,
      subgroup,
      value: toNumber(r["score"]),
      denom: toNumber(r["denom"]),
      suppressed: toNumber(r["score"]) === null,
    });
  }
  console.log(`[load] hits=${hits} skipped=${skipped} facts=${facts.length.toLocaleString()}`);

  let suppressed = 0;
  await batched(facts, 200, async (batch) => {
    for (const f of batch) {
      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",
          },
        },
        update: { value: f.value, denominator: f.denom, 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,
          denominator: f.denom,
          suppressed: f.suppressed,
          sourceLoadId: dataLoad.id,
        },
      });
      if (f.suppressed) suppressed++;
    }
  }, 8);

  await finishDataLoad(dataLoad.id, { inserted: facts.length - suppressed, updated: 0, suppressed });
  console.log(`[load] done — ${facts.length.toLocaleString()} facts (${suppressed.toLocaleString()} suppressed)`);
}

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