scripts/loaders/philly/master_school_list.ts

// Load SDP Master School List CSV → philly_schools table.
// Source: docs/cities/philly/sources.yaml entry `sdp_master_school_list_2024-25`.
//
// Prereq: pipeline_philly/discover.py has run for that source_id, so the file
// + provenance live under data/cities/philly/sources/.../<YYYY-MM-DD>/.
//
// Run:  npx tsx scripts/loaders/philly/master_school_list.ts
// Idempotent: upserts on ulcs_code.

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

const SOURCE_ID = "sdp_master_school_list_2024-25";
const YEAR_COVERED = "2024-25";
const SCRIPT_PATH = "scripts/loaders/philly/master_school_list.ts";

// Governance value → PhillySchoolType enum.
// Master List ships {District, Charter, Contracted}. We map onto PDE's
// 5-value taxonomy (per F.5) by collapsing Master List's Renaissance into
// CHARTER (carrying is_renaissance as a derived boolean per F.11).
function mapSchoolType(
  governance: string,
  schoolReportingCategory: string | null,
): PhillySchoolType {
  const g = governance.trim().toLowerCase();
  const cat = (schoolReportingCategory ?? "").toLowerCase();
  if (cat.includes("cyber")) return "CYBER";
  if (cat.includes("compctc") || cat.includes("cte")) return "COMPCTC";
  if (cat.includes("special education")) return "SPECIALED";
  if (g === "district") return "REGULAR";
  if (g === "charter") return "CHARTER";
  if (g === "contracted") return "CONTRACTED";
  return "REGULAR";
}

function mapAdmission(admissionType: string | null): PhillyAdmissionCategory | null {
  if (!admissionType) return null;
  const t = admissionType.trim().toLowerCase();
  if (t === "catchment") return "CATCHMENT";
  if (t.startsWith("citywide")) return "CITYWIDE_LOTTERY";
  if (t.startsWith("special admission") || t === "special-admission") return "SPECIAL_ADMISSION";
  if (t.startsWith("criteria")) return "CRITERIA_BASED";
  if (t.startsWith("charter")) return "CHARTER_LOTTERY";
  if (t === "cte" || t.includes("career")) return "CTE";
  if (t.includes("alternative")) return "ALTERNATIVE";
  return "OTHER";
}

function mapEssa(designation: string | null): PhillyESSADesignation | null {
  if (!designation) return null;
  const d = designation.trim().toUpperCase();
  if (["DFLT", "TSI", "CSI", "ATSI", "ACSI"].includes(d)) {
    return d as PhillyESSADesignation;
  }
  return null;
}

function parseGps(gps: string | null): { lat: number | null; lng: number | null } {
  if (!gps) return { lat: null, lng: null };
  const parts = gps.split(",").map((s) => s.trim());
  if (parts.length !== 2) return { lat: null, lng: null };
  const lat = Number.parseFloat(parts[0]);
  const lng = Number.parseFloat(parts[1]);
  return {
    lat: Number.isFinite(lat) ? lat : null,
    lng: Number.isFinite(lng) ? lng : null,
  };
}

function emptyToNull(v: string | undefined | null): string | null {
  if (v === undefined || v === null) return null;
  const t = String(v).trim();
  return t === "" || t.toLowerCase() === "null" ? null : t;
}

function parseIntOrNull(v: string | undefined | null): number | null {
  if (v === undefined || v === null || String(v).trim() === "") return null;
  const n = Number.parseInt(String(v), 10);
  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}. Run: python pipeline_philly/discover.py --source ${SOURCE_ID}`,
    );
  }
  console.log(`[load] file: ${filePath}`);
  console.log(`[load] sha256: ${prov.sha256.slice(0, 12)}... (${prov.bytes.toLocaleString()} bytes)`);

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

  // City filter: Master List should be Philly-only already, but defensive
  const phillyRows = rows.filter((r) => {
    const c = (r["City"] || "").trim().toUpperCase();
    // Allow null/empty (some directory entries omit city); only exclude clearly-non-Philly
    return c === "" || c === "PHILADELPHIA";
  });
  if (phillyRows.length !== rows.length) {
    console.log(`[load] filtered to ${phillyRows.length} rows where City=Philadelphia (excluded ${rows.length - phillyRows.length})`);
  }

  const dataLoad = await startDataLoad({
    sourceId: SOURCE_ID,
    sourceUrl: prov.url,
    sha256: prov.sha256,
    bytes: prov.bytes,
    yearCovered: YEAR_COVERED,
    scriptPath: SCRIPT_PATH,
    notes: `Loaded ${phillyRows.length} Philly schools from Master List`,
  });

  let inserted = 0;
  let updated = 0;

  await batched(phillyRows, 25, async (batch) => {
    for (const r of batch) {
      const ulcsCode = emptyToNull(r["ULCS Code"]);
      const aunCode = emptyToNull(r["AUN Code"]);
      const name = emptyToNull(r["School Name (ULCS)"]) ?? emptyToNull(r["Publication Name"]);
      if (!ulcsCode || !aunCode || !name) continue;

      const governance = emptyToNull(r["Governance"]) ?? "Unknown";
      const schoolReportingCategory = emptyToNull(r["School Reporting Category"]);
      // Renaissance Charter status is recorded in `Major Intervention`, not
      // `School Reporting Category` (empirical, verified 2026-06-03; 17 schools
      // in the 2024-25 file carry it as "Renaissance Charter"). Override the
      // F.11 finding text in docs/cities/philly/findings.md with this detail.
      const majorIntervention = emptyToNull(r["Major Intervention"]);
      const isRenaissance =
        (majorIntervention ?? "").toLowerCase() === "renaissance charter";

      const { lat, lng } = parseGps(emptyToNull(r["GPS Location"]));

      const fields = {
        aunCode,
        paCode: emptyToNull(r["PA Code"]),
        srcSchoolId: emptyToNull(r["SRC School ID"]),
        ncesCode: emptyToNull(r["NCES Code"]),
        name,
        publicationName: emptyToNull(r["Publication Name"]),
        abbreviatedName: emptyToNull(r["Abbreviated Name"]),
        schoolType: mapSchoolType(governance, schoolReportingCategory),
        governance,
        isRenaissance,
        schoolReportingCategory,
        managementOrganization: emptyToNull(r["Management Organization"]),
        admissionType: mapAdmission(emptyToNull(r["Admission Type"])),
        schoolLevel: emptyToNull(r["School Level"]),
        currentGradeSpan: emptyToNull(r["Current Grade Span Served"]),
        yearOpened: parseIntOrNull(r["Year Opened"]),
        learningNetwork: emptyToNull(r["Learning Network"]),
        cityCouncilDistrict: emptyToNull(r["City Council District"]),
        latitude: lat,
        longitude: lng,
        streetAddress: emptyToNull(r["Street Address"]),
        city: emptyToNull(r["City"]),
        state: emptyToNull(r["State"]),
        zipCode: emptyToNull(r["Zip Code"]),
        // Master List only marks the CSI cohort; other ESSA tiers come from
        // Future Ready when that loader runs. Don't clobber an existing value
        // (set by an earlier Future Ready load) with null.
        ...(mapEssa(emptyToNull(r["Federal Accountability Designation"])) !== null
          ? { essaDesignation: mapEssa(emptyToNull(r["Federal Accountability Designation"])) }
          : {}),
        titleI: emptyToNull(r["Title I Designation"]) === "1",
        majorIntervention,
        majorInterventionYear: emptyToNull(r["Major Intervention Year"]),
        communitySchoolCohort: emptyToNull(r["Community School Cohort"]),
        cteStatus: emptyToNull(r["CTE Status"]),
        alternateEducationType: emptyToNull(r["Alternate Education Type"]),
        schoolLeaderName: emptyToNull(r["School Leader Name"]),
      } as const;

      const result = await prisma.phillySchool.upsert({
        where: { ulcsCode },
        update: fields,
        create: { ulcsCode, ...fields },
      });
      // Prisma upsert returns the row; we can't tell insert from update from
      // the return value alone. Count both as upserted.
      if (result.createdAt.getTime() === result.updatedAt.getTime()) inserted++;
      else updated++;
    }
  });

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

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