scripts/loaders/miami/sesir.ts

// Load FLDOE SESIR (School Environmental Safety Incident Report) school-level
// counts → incident rates per 100 students: total, violence, bullying,
// weapons, drugs. Category membership is by incident-type name (listed in
// the load notes); every type counts toward the total. The file also carries
// a "SCHOOL TOTAL" row per school; it is excluded from the sum (the
// 2026-09-16 review found it doubled the total) and used as a cross-check —
// the load fails if the summed types disagree with it for any school.
// Denominator: the same year's October enrollment.
// Run:  npx tsx scripts/loaders/miami/sesir.ts

import fs from "node:fs";
import { prisma, requireSource, startDataLoad, finishDataLoad, padSchoolNumber, parseCell, schoolUniverse, upsertFacts, sheetRows, runMain, SOURCES_ROOT, type Fact } from "./_lib";

const SCRIPT_PATH = "scripts/loaders/miami/sesir.ts";
const CATS: Record<string, RegExp> = {
  // SEXUAL HARASSMENT is a harassment incident (bullying category), not a violent one; only sexual assault, battery and
  // other sexual offenses count as violence (review 2026-09-16b: the bare SEXUAL pattern put 68 harassment incidents in both).
  sesir_violence_per_100: /BATTERY|FIGHTING|HOMICIDE|KIDNAPPING|PHYSICAL ATTACK|ROBBERY|SEXUAL (ASSAULT|BATTERY|OFFENSE)|THREAT/,
  sesir_bullying_per_100: /BULLYING|HARASSMENT|HAZING/,
  sesir_weapons_per_100: /WEAPON/,
  sesir_drugs_per_100: /ALCOHOL|DRUG|TOBACCO/,
};

async function main() {
  const dirs = fs.readdirSync(SOURCES_ROOT).filter((d) => /^fldoe_sesir_school_\d{4}-\d{2}$/.test(d)).sort();
  const universe = await schoolUniverse();
  for (const sourceId of dirs) {
    const year = sourceId.slice(-7);
    const { filePath, prov } = requireSource(sourceId);
    const { rows } = sheetRows(filePath, "Schools");
    const h = rows.findIndex((r) => String(r?.[0] ?? "").trim().toLowerCase() === "district #");
    if (h < 0) throw new Error(`${sourceId}: header not found`);
    const enr = new Map((await prisma.miamiSchoolYearMetric.findMany({ where: { metricKey: "enrollment_oct", year, subgroup: "ALL", suppressed: false }, select: { schoolNumber: true, value: true } })).map((r) => [r.schoolNumber, r.value ?? 0]));
    const counts = new Map<string, Record<string, number>>();
    const published = new Map<string, number>();   // the file's own SCHOOL TOTAL row
    const types = new Set<string>();
    let dade = 0;
    for (const r of rows.slice(h + 2)) { // row h+1 is the second header line
      if (String(r[0] ?? "").trim().replace(/\.0$/, "") !== "13") continue;
      dade++;
      const num = padSchoolNumber(r[2]);
      if (!num || !universe.has(num)) continue;
      const type = String(r[4] ?? "").trim().toUpperCase();
      const total = parseCell(r[5]).value ?? 0;
      if (/^SCHOOL TOTAL$/.test(type)) { published.set(num, total); continue; }
      types.add(type);
      const c = counts.get(num) ?? { sesir_total_per_100: 0, sesir_violence_per_100: 0, sesir_bullying_per_100: 0, sesir_weapons_per_100: 0, sesir_drugs_per_100: 0 };
      c.sesir_total_per_100 += total;
      for (const [k, re] of Object.entries(CATS)) if (re.test(type)) c[k] += total;
      counts.set(num, c);
    }
    // Cross-check: the summed incident types must equal the file's SCHOOL TOTAL row.
    const disagree = [...counts].filter(([num, c]) => published.has(num) && Math.abs(c.sesir_total_per_100 - published.get(num)!) > 0.5).map(([num, c]) => `${num}: types ${c.sesir_total_per_100} vs total ${published.get(num)}`);
    if (disagree.length) throw new Error(`${sourceId}: summed incident types disagree with the SCHOOL TOTAL row for ${disagree.length} schools (e.g. ${disagree.slice(0, 5).join("; ")})`);
    const facts: Fact[] = [];
    for (const [num, c] of counts) {
      const e = enr.get(num);
      if (!e) continue;
      for (const [k, v] of Object.entries(c)) facts.push({ schoolNumber: num, year, metricKey: k, value: (v / e) * 100, denominator: e });
    }
    const load = await startDataLoad({ sourceId, sourceUrl: prov.url, sha256: prov.sha256, bytes: prov.bytes, yearCovered: year, scriptPath: SCRIPT_PATH,
      notes: `Dade rows ${dade}; SCHOOL TOTAL rows ${published.size} (excluded from the sum; summed types agree for every school); incident types: ${[...types].sort().join(" | ")}; categories: ${Object.entries(CATS).map(([k, re]) => `${k}=${re.source}`).join("; ")}` });
    const res = await upsertFacts(facts, load.id);
    await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: 0 });
    console.log(`[sesir ${year}] ${res.written} facts for ${counts.size} schools; ${types.size} incident types`);
  }
}

runMain(main);