scripts/loaders/philly/metric_definitions.ts

// Seed philly_metric_definitions with all v1 metric keys.
// Idempotent — re-running upserts.
//
// Run:  npx tsx scripts/loaders/philly/metric_definitions.ts

import { prisma } from "./_lib";
import type {
  MetricDomain,
  MetricUnit,
  MetricDirection,
} from "@prisma/client";

type Def = {
  key: string;
  displayName: string;
  domain: MetricDomain;
  subdomain?: string;
  sourceName: string;
  sourceUrl: string;
  unit: MetricUnit;
  direction: MetricDirection;
  description: string;
  derived?: boolean;
};

const PDE_URL = "https://www.pa.gov/agencies/education/data-and-reporting/assessment-reporting";
const FRPI_URL = "https://futurereadypa.org/Home/DataFiles";
const SDP_PSSA_URL = "https://opendataphilly.org/datasets/sdp-pssa-keystone-performance/";
const SDP_PERF_URL = "https://opendataphilly.org/datasets/sdp-school-performance/";
const SDP_DEMOG_URL = "https://opendataphilly.org/datasets/sdp-school-enrollment-and-demographics/";
const PSES_URL = "https://opendataphilly.org/datasets/sdp-philly-school-experience-survey/";

const METRICS: Def[] = [];

// ── PSSA per-grade (3-8) × {ela, math} ──
// Granular SDP source has school × grade × subgroup; ingested into a single
// metric_key per (grade, subject) with subgroup carried separately.
for (const grade of [3, 4, 5, 6, 7, 8] as const) {
  for (const subject of ["ela", "math"] as const) {
    const ord = `${grade}${grade === 3 ? "rd" : "th"}`;
    METRICS.push({
      key: `pssa_grade${grade}_${subject}_proficiency`,
      displayName: `${ord} grade ${subject.toUpperCase()} proficiency (PSSA)`,
      domain: "PERFORMANCE",
      subdomain: "Test Scores",
      sourceName: "SDP PSSA & Keystone (via OpenDataPhilly CDN)",
      sourceUrl: SDP_PSSA_URL,
      unit: "PERCENT",
      direction: "HIGHER_BETTER",
      description: `Share of ${ord} grade test-takers scoring Proficient or Advanced on the PA System of School Assessment ${subject.toUpperCase()} exam.`,
    });
  }
}

// ── PSSA all-grades aggregate (for Outliers/Movers default view) ──
for (const subject of ["ela", "math"] as const) {
  METRICS.push({
    key: `pssa_all_${subject}_proficiency`,
    displayName: `PSSA ${subject.toUpperCase()} proficiency (all tested grades)`,
    domain: "PERFORMANCE",
    subdomain: "Test Scores",
    sourceName: "Future Ready PA Index (PDE)",
    sourceUrl: FRPI_URL,
    unit: "PERCENT",
    direction: "HIGHER_BETTER",
    description: `Share of test-takers across all PSSA grades 3-8 scoring Proficient or Advanced on the ${subject.toUpperCase()} exam. Covers all Philly schools (district + charter).`,
  });
}

// ── Keystone HS subjects ──
for (const subj of ["algebra", "literature", "biology"] as const) {
  const display = { algebra: "Algebra I", literature: "Literature", biology: "Biology" }[subj];
  METRICS.push({
    key: `keystone_${subj}_proficiency`,
    displayName: `Keystone ${display} proficiency`,
    domain: "PERFORMANCE",
    subdomain: "Test Scores",
    sourceName: "Future Ready PA Index (PDE)",
    sourceUrl: FRPI_URL,
    unit: "PERCENT",
    direction: "HIGHER_BETTER",
    description: `Share of students scoring Proficient or Advanced on the Keystone ${display} end-of-course exam. Latest-attempt, all-students rule (per Philly O.5).`,
  });
}

// ── PVAAS growth (O.7 — first-class v1) ──
const PVAAS_SUBJECTS = [
  { key: "pvaas_growth_math_algebra1", display: "Algebra I" },
  { key: "pvaas_growth_ela_literature", display: "ELA/Literature" },
  { key: "pvaas_growth_science_biology", display: "Science/Biology" },
];
for (const p of PVAAS_SUBJECTS) {
  METRICS.push({
    key: p.key,
    displayName: `PVAAS growth — ${p.display}`,
    domain: "PERFORMANCE",
    subdomain: "Growth",
    sourceName: "Future Ready PA Index (PDE)",
    sourceUrl: FRPI_URL,
    unit: "INDEX",
    direction: "HIGHER_BETTER",
    description: `Pennsylvania Value-Added Assessment System (PVAAS) growth index for ${p.display}. Indicates whether students grew more or less than expected. Distinct from proficiency rate (status).`,
  });
}

// ── Graduation ──
for (const yr of [4, 5, 6] as const) {
  METRICS.push({
    key: `graduation_rate_${yr}yr`,
    displayName: `${yr}-year cohort graduation rate`,
    domain: "PERFORMANCE",
    subdomain: "Graduation",
    sourceName:
      yr === 4
        ? "SDP Graduation Rates (district + alt) + Future Ready PA Index (charter coverage)"
        : "SDP Graduation Rates",
    sourceUrl: SDP_PERF_URL,
    unit: "PERCENT",
    direction: "HIGHER_BETTER",
    description: `${yr}-year cohort graduation rate for the high-school cohort starting 9th grade in the named year.`,
  });
}

// ── Chronic absenteeism ──
METRICS.push({
  key: "chronic_absenteeism_rate",
  displayName: "Chronic absenteeism rate",
  domain: "CLIMATE",
  subdomain: "Attendance",
  sourceName: "Future Ready PA Index (PDE)",
  sourceUrl: FRPI_URL,
  unit: "PERCENT",
  direction: "LOWER_BETTER",
  description:
    "Share of students absent ≥10% of enrolled days. SDP also publishes a complementary 'attendance ≥90%' metric (attendance_rate_above_90) for district + alt schools.",
});

METRICS.push({
  key: "attendance_rate_above_90",
  displayName: "Attendance ≥90% rate",
  domain: "CLIMATE",
  subdomain: "Attendance",
  sourceName: "SDP Student Attendance",
  sourceUrl: SDP_PERF_URL,
  unit: "PERCENT",
  direction: "HIGHER_BETTER",
  description:
    "Share of students attending ≥90% of enrolled days, per school per year. The complement approximates chronic absenteeism but isn't identical.",
});

METRICS.push({
  key: "attendance_persistence_rate",
  displayName: "Regular attendance rate (state)",
  domain: "CLIMATE",
  subdomain: "Attendance",
  sourceName: "Future Ready PA Index (PDE)",
  sourceUrl: FRPI_URL,
  unit: "PERCENT",
  direction: "HIGHER_BETTER",
  description:
    "The state's Regular Attendance indicator: share of students enrolled 90+ school days who attended ≥90% of them (PercentPersistentAttendance in the 2021-22+ workbooks). Shown by attendance year — PDE calls it a lagging indicator, so its 2024-25 workbook reports 2023-24 attendance. Covers every public school, including charters. PDE does not populate its chronic-absenteeism column for individual Philadelphia schools.",
});

METRICS.push({
  key: "average_daily_attendance",
  displayName: "Average daily attendance",
  domain: "CLIMATE",
  subdomain: "Attendance",
  sourceName: "SDP Student Attendance",
  sourceUrl: SDP_PERF_URL,
  unit: "PERCENT",
  direction: "HIGHER_BETTER",
  description: "Share of enrolled days that students attended on average, per school per year.",
});

// ── Out-of-school suspensions — derived from the distribution per F.10 ──
METRICS.push(
  {
    key: "oss_pct_any",
    displayName: "OSS — % students with any suspension",
    domain: "CLIMATE",
    subdomain: "Discipline",
    sourceName: "SDP Out-of-School Suspensions",
    sourceUrl: SDP_PERF_URL,
    unit: "PERCENT",
    direction: "LOWER_BETTER",
    description: "Share of students with one or more out-of-school suspensions in the school year (= 100 − % with zero).",
    derived: true,
  },
  {
    key: "oss_pct_multiple",
    displayName: "OSS — % students with 2+ suspensions",
    domain: "CLIMATE",
    subdomain: "Discipline",
    sourceName: "SDP Out-of-School Suspensions",
    sourceUrl: SDP_PERF_URL,
    unit: "PERCENT",
    direction: "LOWER_BETTER",
    description: "Share of students with two or more out-of-school suspensions in the school year.",
    derived: true,
  },
  {
    key: "oss_pct_chronic",
    displayName: "OSS — % students with 4+ suspensions",
    domain: "CLIMATE",
    subdomain: "Discipline",
    sourceName: "SDP Out-of-School Suspensions",
    sourceUrl: SDP_PERF_URL,
    unit: "PERCENT",
    direction: "LOWER_BETTER",
    description: "Heavy-tail OSS share — students suspended 4 or more times in the school year.",
    derived: true,
  },
);

// ── Demographic shares (covariates for the residual-z engine) ──
const DEMO_METRICS: { key: string; display: string }[] = [
  { key: "enrollment", display: "Enrollment" },
  { key: "pct_econ_disadv", display: "% Economically Disadvantaged" },
  { key: "pct_english_learner", display: "% English Learner" },
  { key: "pct_special_ed", display: "% Special Education (IEP)" },
  { key: "pct_female", display: "% Female" },
  { key: "pct_male", display: "% Male" },
  { key: "pct_amer_indian", display: "% American Indian / Alaska Native" },
  { key: "pct_asian", display: "% Asian" },
  { key: "pct_native_hawaiian", display: "% Native Hawaiian / Pacific Islander" },
  { key: "pct_black", display: "% Black" },
  { key: "pct_hispanic", display: "% Hispanic" },
  { key: "pct_white", display: "% White" },
  { key: "pct_two_or_more_races", display: "% Two or More Races" },
];
for (const d of DEMO_METRICS) {
  METRICS.push({
    key: d.key,
    displayName: d.display,
    domain: "DEMOGRAPHICS",
    sourceName: "SDP Enrollment & Demographics (with Future Ready Fast Facts as charter fallback pre-2019)",
    sourceUrl: SDP_DEMOG_URL,
    unit: d.key === "enrollment" ? "COUNT" : "PERCENT",
    direction: "NEUTRAL",
    description:
      d.key === "enrollment"
        ? "Total student enrollment at the school in the school year."
        : `${d.display} of total student enrollment at the school in the school year.`,
  });
}

// ── PSES survey rollups (O.6 — teacher + student first-class) ──
const PSES_DOMAINS = [
  "school_climate",
  "instructional_environment",
  "school_leadership",
  "professional_capacity",
  "family_engagement",
  "dei",
];
for (const respondent of ["teacher", "student"] as const) {
  for (const dom of PSES_DOMAINS) {
    METRICS.push({
      key: `survey_${respondent}_${dom}`,
      displayName: `PSES ${respondent} — ${dom.replace(/_/g, " ")}`,
      domain: "CLIMATE",
      subdomain: `PSES ${respondent}`,
      sourceName: "Philly School Experience Survey (SDP Research)",
      sourceUrl: PSES_URL,
      unit: "PERCENT",
      direction: "HIGHER_BETTER",
      description: `PSES rollup score for the ${dom.replace(/_/g, " ")} domain, ${respondent} respondents. v1 first-class metric per Philly O.6.`,
      derived: true,
    });
  }
}

async function main() {
  console.log(`[seed] upserting ${METRICS.length} Philly metric definitions`);
  for (const def of METRICS) {
    await prisma.phillyMetricDefinition.upsert({
      where: { key: def.key },
      update: def,
      create: def,
    });
  }
  console.log(`[seed] done — ${METRICS.length} metrics in philly_metric_definitions`);
}

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