scripts/loaders/miami/fish_los.ts

// Load the parsed FLDOE FISH Level of Service tables
// (data/cities/miami/reference/parsed/fldoe_fish_los_dade_<year>.csv, produced by
// pipeline_miami/parse/fish_los.py with its two-parser gate) →
// fish_capacity_school, fish_capacity_permanent, cofte, utilization_school_capacity_pct.
//
// A school number can map to two facilities (an old and a new building); the
// facility with the larger school capacity is kept and the other logged.
// The latest year's permanent capacity also refreshes miami_schools.fish_capacity_permanent.
//
// Run:  npx tsx scripts/loaders/miami/fish_los.ts [year ...]

import fs from "node:fs";
import path from "node:path";
import { parse } from "csv-parse/sync";
import { requireSource, startDataLoad, finishDataLoad, stageLoadOperation, schoolUniverse, upsertFacts, runMain, SOURCES_ROOT, type Fact } from "./_lib";

const SCRIPT_PATH = "scripts/loaders/miami/fish_los.ts";
const PARSED = path.join(process.cwd(), "data", "cities", "miami", "reference", "parsed");

async function main() {
  const years = process.argv.slice(2).length
    ? process.argv.slice(2)
    : fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_fish_los_dade_\d{4}-\d{2}$/.test(d)).map((d) => d.replace("fldoe_fish_los_dade_", "")).sort();
  const latestAvailable = fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_fish_los_dade_\d{4}-\d{2}$/.test(d)).map((d) => d.replace("fldoe_fish_los_dade_", "")).sort().at(-1);
  const universe = await schoolUniverse();
  let updated = 0;
  let latestPermYear: string | null = null;
  for (const year of years) {
    const sourceId = `fldoe_fish_los_dade_${year}`;
    const { prov } = requireSource(sourceId);
    const gate = JSON.parse(fs.readFileSync(path.join(PARSED, `${sourceId}.gate.json`), "utf-8")) as { gate: { pass: boolean }; util_arith_failures: number };
    if (!gate.gate.pass || gate.util_arith_failures > 0) throw new Error(`${sourceId}: parser gate did not pass; refusing to load`);
    const rows = parse(fs.readFileSync(path.join(PARSED, `${sourceId}.csv`), "utf-8"), { columns: true, skip_empty_lines: true }) as Record<string, string>[];
    const best = new Map<string, Record<string, string>>();
    const dupes: string[] = [];
    for (const r of rows) {
      const num = r.school_number;
      if (!num || !universe.has(num)) continue;
      const cap = Number(r.school_capacity);
      if (!(cap > 0)) continue;
      const prev = best.get(num);
      if (prev) {
        dupes.push(`${num}: kept "${Number(prev.school_capacity) >= cap ? prev.name : r.name}"`);
        if (cap <= Number(prev.school_capacity)) continue;
      }
      best.set(num, r);
    }
    const facts: Fact[] = [];
    const perm = new Map<string, number>();
    for (const [num, r] of best) {
      facts.push({ schoolNumber: num, year, metricKey: "fish_capacity_school", value: Number(r.school_capacity) });
      facts.push({ schoolNumber: num, year, metricKey: "fish_capacity_permanent", value: Number(r.permanent_capacity) });
      facts.push({ schoolNumber: num, year, metricKey: "cofte", value: Number(r.cofte) });
      facts.push({ schoolNumber: num, year, metricKey: "utilization_school_capacity_pct", value: Number(r.utilization_pct), denominator: Number(r.school_capacity) });
      perm.set(num, Number(r.permanent_capacity));
    }
    const load = await startDataLoad({ sourceId, sourceUrl: prov.url, sha256: prov.sha256, bytes: prov.bytes, yearCovered: year, scriptPath: SCRIPT_PATH,
      notes: `parsed table ${sourceId}.csv (two-parser gate PASS); ${best.size} schools with capacity; duplicates resolved: ${dupes.length}` });
    const res = await upsertFacts(facts, load.id);
    if (year === latestAvailable) {
      for (const [num, cap] of perm) stageLoadOperation(load.id, (tx) => tx.miamiSchool.update({ where: { schoolNumber: num }, data: { fishCapacityPermanent: cap } }));
      updated = perm.size;
      latestPermYear = year;
    }
    await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: 0 });
    console.log(`[fish ${year}] ${res.written} facts for ${best.size} schools; duplicates: ${dupes.join(" | ") || "none"}`);
  }
  console.log(latestPermYear ? `[fish] refreshed fish_capacity_permanent on ${updated} school records from ${latestPermYear}` : `[fish] school records not refreshed (latest available year ${latestAvailable} not in this run)`);
}

runMain(main);