scripts/loaders/miami/school_events.ts

// Load the hand-curated board-action table
// (data/cities/miami/reference/school_events.csv) → miami_school_events.
// Every row cites its document; the CSV is the audit trail and is replaced
// wholesale on each run (delete + insert), so edits to the CSV are the only
// way to change an event.
// Run:  npx tsx scripts/loaders/miami/school_events.ts

import fs from "node:fs";
import path from "node:path";
import { parse } from "csv-parse/sync";
import { prisma, runMain } from "./_lib";
import { assertEventSchoolIdentity } from "./event-school-identity";
import type { MiamiEventAction } from "@prisma/client";

const CSV = path.join(process.cwd(), "data", "cities", "miami", "reference", "school_events.csv");

async function main() {
  const rows = parse(fs.readFileSync(CSV, "utf-8"), { columns: true, skip_empty_lines: true }) as Record<string, string>[];
  const schools = await prisma.miamiSchool.findMany({ select: { schoolNumber: true, name: true } });
  const names = new Map(schools.map((school) => [school.schoolNumber, school.name]));
  const data = [];
  for (const r of rows) {
    // Optional source name records which source identities have been checked. A
    // mismatch stops the whole replacement before any existing event is deleted.
    assertEventSchoolIdentity(r.school_number, r.source_school_name || "", names.get(r.school_number));
    if (!names.has(r.school_number)) { console.warn(`[events] ${r.school_number} not in directory; skipped`); continue; }
    data.push({
      schoolNumber: r.school_number,
      schoolYear: r.school_year,
      action: r.action as MiamiEventAction,
      detail: r.detail || null,
      sourceDocUrl: r.source_doc_url || null,
      sourcePage: r.source_page ? Number.parseInt(r.source_page, 10) : null,
      boardItem: r.board_item || null,
      decidedAt: r.decided_at ? new Date(`${r.decided_at}T00:00:00Z`) : null,
    });
  }
  // Replace the table atomically: a failure mid-way leaves the previous rows in place.
  await prisma.$transaction([prisma.miamiSchoolEvent.deleteMany({}), prisma.miamiSchoolEvent.createMany({ data })]);
  console.log(`[events] loaded ${data.length} events from ${CSV}`);
}

runMain(main);