scripts/loaders/miami/derived.ts

// Derived closure-track metrics, recomputed from validated inputs:
//   utilization_permanent_pct  = enrollment_oct / fish_capacity_permanent (same year)
//   enrollment_change_5yr_pct  = (enrollment_oct[y] / enrollment_oct[y-5] - 1) * 100
//   k_to_g5_ratio              = enrollment_oct_gKG / enrollment_oct_g5 (schools with both > 0)
//   students_per_teacher       = enrollment_oct / teacher_fte (same year)
// For 2025-26, where the state FISH report is not yet out, permanent capacity
// falls back to the latest FISH year, then the board book, then the GIS layer;
// the fallback used is recorded in the load notes.
// Run:  npx tsx scripts/loaders/miami/derived.ts

import { prisma, startDataLoad, finishDataLoad, stageLoadOperation, upsertFacts, shiftSchoolYear, runMain, type Fact } from "./_lib";

const SCRIPT_PATH = "scripts/loaders/miami/derived.ts";

type Cell = { schoolNumber: string; year: string; metricKey: string; value: number | null; suppressed: boolean };

async function main() {
  const keys = ["enrollment_oct", "enrollment_oct_gKG", "enrollment_oct_g5", "fish_capacity_permanent", "fish_capacity_permanent_abc", "fish_capacity_permanent_gis", "teacher_fte"];
  const cells = (await prisma.miamiSchoolYearMetric.findMany({
    where: { metricKey: { in: keys }, subgroup: "ALL" },
    select: { schoolNumber: true, year: true, metricKey: true, value: true, suppressed: true },
  })) as Cell[];
  const idx = new Map<string, Cell>();
  for (const c of cells) idx.set(`${c.schoolNumber}|${c.year}|${c.metricKey}`, c);
  const get = (n: string, y: string, k: string) => {
    const c = idx.get(`${n}|${y}|${k}`);
    return c && !c.suppressed && c.value !== null ? c.value : null;
  };
  const years = [...new Set(cells.filter((c) => c.metricKey === "enrollment_oct").map((c) => c.year))].sort();
  const fishYears = [...new Set(cells.filter((c) => c.metricKey === "fish_capacity_permanent").map((c) => c.year))].sort();
  const latestFish = fishYears[fishYears.length - 1];
  const schools = [...new Set(cells.map((c) => c.schoolNumber))];

  const facts: Fact[] = [];
  let fallbackFish = 0, fallbackAbc = 0, fallbackGis = 0;
  for (const n of schools) {
    for (const y of years) {
      const enr = get(n, y, "enrollment_oct");
      if (enr === null) continue;
      // utilization
      let cap = get(n, y, "fish_capacity_permanent");
      if (cap === null && fishYears.includes(y) === false) {
        if (get(n, latestFish, "fish_capacity_permanent") !== null && y > latestFish) { cap = get(n, latestFish, "fish_capacity_permanent"); fallbackFish++; }
        else if (get(n, y, "fish_capacity_permanent_abc") !== null) { cap = get(n, y, "fish_capacity_permanent_abc"); fallbackAbc++; }
        else if (get(n, "2024-25", "fish_capacity_permanent_gis") !== null && y > "2023-24") { cap = get(n, "2024-25", "fish_capacity_permanent_gis"); fallbackGis++; }
      }
      if (cap !== null && cap > 0) facts.push({ schoolNumber: n, year: y, metricKey: "utilization_permanent_pct", value: (enr / cap) * 100, denominator: cap });
      // five-year change
      const prev = get(n, shiftSchoolYear(y, -5), "enrollment_oct");
      if (prev !== null && prev > 0) facts.push({ schoolNumber: n, year: y, metricKey: "enrollment_change_5yr_pct", value: (enr / prev - 1) * 100, denominator: prev });
      // pipeline ratio
      const k = get(n, y, "enrollment_oct_gKG"), g5 = get(n, y, "enrollment_oct_g5");
      if (k !== null && g5 !== null && g5 > 0 && k > 0) facts.push({ schoolNumber: n, year: y, metricKey: "k_to_g5_ratio", value: k / g5, denominator: g5 });
      // staffing
      const t = get(n, y, "teacher_fte");
      if (t !== null && t > 0) facts.push({ schoolNumber: n, year: y, metricKey: "students_per_teacher", value: enr / t, denominator: t });
    }
  }
  const load = await startDataLoad({ sourceId: "derived", scriptPath: SCRIPT_PATH,
    notes: `inputs: ${keys.join(", ")}; years ${years[0]}..${years[years.length - 1]}; FISH years ${fishYears.join(",")}; capacity fallbacks for years after ${latestFish}: latest FISH ${fallbackFish}, board book ${fallbackAbc}, GIS ${fallbackGis}` });
  // miami_schools.latest_enrollment = the school's latest unsuppressed October count.
  let latestUpdated = 0;
  for (const n of schools) {
    const ys = years.filter((y) => get(n, y, "enrollment_oct") !== null);
    if (!ys.length) continue;
    stageLoadOperation(load.id, (tx) => tx.miamiSchool.update({ where: { schoolNumber: n }, data: { latestEnrollment: Math.round(get(n, ys[ys.length - 1], "enrollment_oct")!) } }));
    latestUpdated++;
  }
  const res = await upsertFacts(facts, load.id);
  await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: 0 });
  console.log(`[derived] ${res.written} facts; capacity fallbacks — latest FISH: ${fallbackFish}, board book: ${fallbackAbc}, GIS: ${fallbackGis}; latest_enrollment set on ${latestUpdated} schools`);
}

runMain(main);