// NYC enrollment, capacity and utilization — analysis packet.
//
// Writes data/analysis/nyc-enrollment-capacity.json, the committed audit trail
// behind docs/design/10_nyc_enrollment_capacity_utilization.md and the page that
// doc plans. Anything that displays these figures reads the packet; it does not
// recompute them.
//
//   npx tsx scripts/analysis/nyc-capacity-packet.ts                    regenerate
//   npx tsx scripts/analysis/nyc-capacity-packet.ts --check            diff against the
//                                                                      committed file,
//                                                                      exit 1 on drift
//   npx tsx scripts/analysis/nyc-capacity-packet.ts --as-of=2026-09-09 override the stamp
//   npx tsx scripts/analysis/nyc-capacity-packet.ts --refresh          re-download the
//                                                                      cached spreadsheet
//
// No database. This script reads six published files plus the committed parse of
// the SCA's PDFs (data/cities/nyc/reference/parsed/), and runs with no
// DATABASE_URL set. The Blue Book parsing lives in
// scripts/loaders/_bluebook.ts and is reused here rather than restated — the
// per-building roll-up in particular (doc §3.2: a school split across buildings
// gets one row per building, so reading any single row misstates the school).
//
// Four conventions, each of which moves the numbers:
//
//  1. UNIVERSE. School-grain figures cover organizations whose org code resolves
//     to exactly one DBN in our school universe — the 2019-20 School Locations
//     directory plus every year of the DOE Demographic Snapshot across its three
//     publication vintages. That is 99.6-99.7% of organizations carrying an
//     enrollment. The six or seven dropped each year are named in
//     `universe.by_year[].dropped`, and `universe.sensitivity_all_orgs` repeats
//     the headline statistics without the filter: measured, it moves the share
//     under 50% by at most 0.1 pp and the median not at all.
//
//  2. QUANTILES. Percentiles use the exclusive definition that Python's
//     statistics.quantiles applies by default, which is what produced the
//     percentile table in doc §4.1. The inclusive definition used elsewhere in
//     this repo can differ from it after rounding at p90, so the two are not
//     interchangeable.
//
//  3. BUILDING GRAIN. A building's enrollment and capacity repeat on every
//     organization row it hosts, so the figures are deduped by building id.
//     `rollUpBuildings` keeps the FIRST row it sees for a building, and in
//     2013-14 and 2016-17 that row is sometimes an annex-style row carrying no
//     capacity — 13 and 10 buildings. Rows with complete building figures are
//     therefore handed to it first. Where two rows for one building are both
//     complete they never disagree; `qa.building_row_consistency` checks that.
//
//  4. IRREGULAR PANEL. Twelve school years, 2013-14 to 2025-26, with one gap:
//     2020-21, which the SCA never published. Seven come from Socrata; the
//     other five are read from the SCA's own PDFs (pipeline_nyc/parse/bluebook.py)
//     and reach this script through the same gate the loader checks, so the
//     packet and the warehouse see one panel. `meta.missing_years` carries the
//     gap, so a time-series figure breaks its line at 2020-21.
//
// Direction is NEUTRAL throughout. Utilization is how full a building is, not a
// rating of the school inside it, and target capacity is the SCA's planning
// construct rather than a count of how many children fit.

import fs from "node:fs";
import path from "node:path";
import { fetchSocrataAll, fetchAndCache, readSheet } from "../loaders/_fetch";
import { readRecoveredYears } from "../loaders/_bluebook_recovered";
import {
  SNAPSHOT_YEAR,
  buildDbnIndex,
  groupBySnapshot,
  num,
  resolveDbn,
  rollUpBuildings,
  rollUpOrgs,
  schoolYearForSnapshot,
  utilizationAgreement,
  type BlueBookRow,
  type BuildingRecord,
  type OrgRecord,
} from "../loaders/_bluebook";

const OUT = path.join(process.cwd(), "data", "analysis", "nyc-enrollment-capacity.json");

// ── sources ──────────────────────────────────────────────────────────────────
const BLUEBOOK = "gkd7-3vk7"; // live: enrollment, target capacity, published utilization
const BLUEBOOK_HIST_ORG = "q9xk-w9iv"; // retired 2015: the pre-2014 capacity formula
const DIRECTORY = "wg9x-4ke6"; // 2019-20 School Locations — the DBN seed
const DEMO_2013_2018 = "s52a-8aq6";
const DEMO_2018_2022 = "c7ru-d68s";
const DEMO_XLSX_URL =
  "https://infohub.nyced.org/docs/default-source/default-document-library/" +
  "demographic-snapshot-2021-22-to-2025-26-public.xlsx";
const socrataUrl = (id: string) => `https://data.cityofnewyork.us/resource/${id}.json`;

const DECOMP_FROM = "2018-19";
const DECOMP_TO = "2025-26";
// The school years strictly between the two endpoints that the panel lacks.
// Only 2020-21 is missing, because the SCA never published it (doc §2), so the
// span can be run segment by segment as well as end to end. `segments` splits
// each consecutive published pair, which is what lets the page say whether the
// endpoint reading hides a reversal instead of merely noting that it might.
const DECOMP_SPAN_UNPUBLISHED = ["2020-21"];
const DISTRICT_YEAR = "2025-26";
const HISTORICAL_YEAR = "2013-14"; // the only year the retired capacity series covers

// ── flags ────────────────────────────────────────────────────────────────────
const argv = process.argv.slice(2);
function flagValue(name: string): string | null {
  const prefix = `--${name}=`;
  const inline = argv.find((a) => a.startsWith(prefix));
  if (inline) return inline.slice(prefix.length);
  const i = argv.indexOf(`--${name}`);
  if (i >= 0 && i + 1 < argv.length && !argv[i + 1].startsWith("--")) return argv[i + 1];
  return null;
}
const CHECK = argv.includes("--check");
// --check re-downloads the cached spreadsheet: a stale cache would hide an
// upstream restatement, which is the one thing --check exists to catch.
const REFRESH = argv.includes("--refresh") || CHECK;
const AS_OF_OVERRIDE = flagValue("as-of") ?? process.env.NYC_PACKET_AS_OF ?? null;

// ── statistics ───────────────────────────────────────────────────────────────
/**
 * Round half to even, which is what the published tables in the doc use —
 * they came out of Python, whose formatting rounds ties to the even digit,
 * while JavaScript's `Math.round` rounds them up.
 *
 * The difference is confined to exact ties, and exact ties are ordinary here
 * because a share is a small ratio of counts: 3 of District 20's 48 schools is
 * exactly 6.25%, which reads 6.2 under one convention and 6.3 under the other.
 * Half-even is also the unbiased choice — half-up drags every tie in one
 * direction — so the packet uses it and the doc's numbers reproduce.
 */
function roundTo(x: number, digits: number): number {
  const f = 10 ** digits;
  const scaled = x * f;
  const low = Math.floor(scaled);
  const frac = scaled - low;
  const n = frac > 0.5 ? low + 1 : frac < 0.5 ? low : low % 2 === 0 ? low : low + 1;
  return n / f;
}
const r1 = (n: number) => roundTo(n, 1);
const r3 = (n: number) => roundTo(n, 3);
const sum = (xs: number[]) => xs.reduce((a, b) => a + b, 0);

/**
 * Exclusive quantile — the default of Python's `statistics.quantiles`, and the
 * definition behind the published percentile table (doc §4.1). Position is
 * p × (n + 1) in 1-based terms, the bracketing index is clamped into [1, n-1],
 * and the value is interpolated linearly between the two order statistics.
 */
function quantileExclusive(sorted: readonly number[], p: number): number {
  const n = sorted.length;
  if (n === 0) return NaN;
  if (n === 1) return sorted[0];
  const pos = p * (n + 1);
  const j = Math.min(Math.max(Math.floor(pos), 1), n - 1);
  const frac = pos - j;
  return sorted[j - 1] + frac * (sorted[j] - sorted[j - 1]);
}

type Distribution = {
  n: number;
  p10: number | null;
  p25: number | null;
  median: number | null;
  p75: number | null;
  p90: number | null;
  share_under_50: number | null;
  share_under_33: number | null;
  share_over_100: number | null;
  sum_over_sum: number | null;
  enrollment: number;
  capacity: number;
};

/**
 * The distribution block every grain reports. `utilizations` are integer
 * percents as the source states them (published at building grain, summed then
 * divided at school grain); `enrollment`/`capacity` are the totals behind
 * `sum_over_sum`, which is a student-weighted figure and not the median.
 *
 * Thresholds are strict and one-sided, matching the doc: under 50, under 33.3
 * (a third), over 100.
 */
function distribution(utilizations: number[], enrollment: number, capacity: number): Distribution {
  const s = [...utilizations].sort((a, b) => a - b);
  const n = s.length;
  const share = (pred: (x: number) => boolean) => (n ? r1((100 * s.filter(pred).length) / n) : null);
  return {
    n,
    p10: n ? r1(quantileExclusive(s, 0.1)) : null,
    p25: n ? r1(quantileExclusive(s, 0.25)) : null,
    median: n ? r1(quantileExclusive(s, 0.5)) : null,
    p75: n ? r1(quantileExclusive(s, 0.75)) : null,
    p90: n ? r1(quantileExclusive(s, 0.9)) : null,
    share_under_50: share((x) => x < 50),
    share_under_33: share((x) => x < 33.3),
    share_over_100: share((x) => x > 100),
    sum_over_sum: capacity > 0 ? r1((100 * enrollment) / capacity) : null,
    enrollment,
    capacity,
  };
}

// ── shapes the packet is assembled from ──────────────────────────────────────
/** A building with complete figures: enrollment, capacity and a published utilization. */
type CompleteBuilding = { id: string; enrollment: number; capacity: number; utilization: number };

/**
 * Buildings with complete figures, from `rollUpBuildings`.
 *
 * `rollUpBuildings` keeps the first row it encounters for a building id, so the
 * rows are ordered complete-first: otherwise an annex-style row carrying no
 * capacity wins the id and the building drops out of the grain entirely (13
 * buildings in 2013-14, 10 in 2016-17 — measured, see the header).
 */
function completeBuildings(rows: readonly BlueBookRow[]): {
  records: Map<string, BuildingRecord>;
  complete: CompleteBuilding[];
} {
  const isComplete = (r: BlueBookRow) => {
    const e = num(r.bldg_enroll);
    const c = num(r.target_bldg_cap);
    return e !== null && e > 0 && c !== null && c > 0;
  };
  const ordered = [...rows.filter(isComplete), ...rows.filter((r) => !isComplete(r))];
  const records = rollUpBuildings(ordered);
  const complete: CompleteBuilding[] = [];
  for (const b of records.values()) {
    if (b.enrollment === null || b.enrollment <= 0) continue;
    if (b.targetCapacity === null || b.targetCapacity <= 0) continue;
    if (b.utilizationPct === null) continue;
    complete.push({
      id: b.buildingId,
      enrollment: b.enrollment,
      capacity: b.targetCapacity,
      utilization: b.utilizationPct,
    });
  }
  complete.sort((a, b) => (a.id < b.id ? -1 : a.id > b.id ? 1 : 0));
  return { records, complete };
}

/**
 * Buildings hosting more than one organization with students in them.
 *
 * Counted from the raw rows rather than `BuildingRecord.orgCodes`, which admits
 * an organization whose enrollment in the building is zero. The two definitions
 * differ by 0.5-0.6 pp; `qa.multi_org_definition` carries both so the choice is
 * visible rather than implied.
 */
function multiOrgBuildings(rows: readonly BlueBookRow[], ids: ReadonlySet<string>): number {
  const occupants = new Map<string, Set<string>>();
  for (const r of rows) {
    const b = (r.bldg_id ?? "").trim();
    const org = (r.org_id ?? "").trim().toUpperCase();
    const e = num(r.org_enroll);
    if (!b || !org || e === null || e <= 0 || !ids.has(b)) continue;
    const bucket = occupants.get(b);
    if (bucket) bucket.add(org);
    else occupants.set(b, new Set([org]));
  }
  let multi = 0;
  for (const set of occupants.values()) if (set.size > 1) multi += 1;
  return multi;
}

/**
 * Does this organization describe how full a building is?
 *
 * Students and seats are both required, and the students strictly. `rollUpOrgs`
 * reports an organization that reported no enrollment against a real capacity as
 * 0% utilized, which is an organization that did not report rather than an empty
 * school; admitting them adds 20 schools at 0% to 2013-14 and moves the share
 * under 50% by a point.
 */
function hasUtilization(o: OrgRecord | undefined): boolean {
  return (
    !!o &&
    o.enrollment !== null &&
    o.enrollment > 0 &&
    o.targetCapacity !== null &&
    o.targetCapacity > 0 &&
    o.utilizationPct !== null
  );
}

/** Organizations carrying students and seats — the school-grain population before the DBN filter. */
function orgsWithUtilization(orgs: Iterable<OrgRecord>): OrgRecord[] {
  return [...orgs].filter(hasUtilization);
}

function schoolDistribution(orgs: readonly OrgRecord[]): Distribution {
  return distribution(
    orgs.map((o) => o.utilizationPct as number),
    sum(orgs.map((o) => o.enrollment as number)),
    sum(orgs.map((o) => o.targetCapacity as number)),
  );
}

// ── the DBN universe the school grain is filtered to ─────────────────────────
type DemoSocrataRow = { dbn?: string; year?: string; total_enrollment?: string };
type DirectoryRow = { system_code?: string };
type DemoXlsxRow = { DBN?: unknown; Year?: unknown; "Total Enrollment"?: unknown };

async function loadDbnUniverse(): Promise<{
  dbns: Set<string>;
  parts: { source: string; dbns: number }[];
}> {
  const parts: { source: string; dbns: number }[] = [];
  const dbns = new Set<string>();
  const add = (raw: unknown) => {
    const d = String(raw ?? "").trim().toUpperCase();
    if (d.length >= 3) dbns.add(d);
  };

  const directory = await fetchSocrataAll<DirectoryRow>(DIRECTORY);
  const before0 = dbns.size;
  for (const r of directory) if (r.system_code) add(r.system_code);
  parts.push({ source: `socrata:${DIRECTORY}`, dbns: dbns.size - before0 });

  for (const id of [DEMO_2013_2018, DEMO_2018_2022]) {
    const rows = await fetchSocrataAll<DemoSocrataRow>(id);
    const before = dbns.size;
    // An enrollment is required, matching the probe: a row without one is not
    // evidence that the school reported in that year.
    for (const r of rows) if (r.dbn && r.year && num(r.total_enrollment) !== null) add(r.dbn);
    parts.push({ source: `socrata:${id}`, dbns: dbns.size - before });
  }

  const xlsxPath = await fetchAndCache(DEMO_XLSX_URL, REFRESH);
  const sheet = readSheet<DemoXlsxRow>(xlsxPath, "School");
  const before = dbns.size;
  for (const r of sheet) {
    if (r.DBN && r.Year && num(r["Total Enrollment"]) !== null) add(r.DBN);
  }
  parts.push({ source: "infohub:demographic-snapshot-2021-22-to-2025-26", dbns: dbns.size - before });

  if (dbns.size < 2000) {
    throw new Error(
      `DBN universe came back with only ${dbns.size} schools, which is far short of the ~2,236 ` +
        `measured in doc §3.1. One of the four inputs returned less than it should — regenerating ` +
        `on a short universe would silently restate every school-grain figure in this packet.`,
    );
  }
  return { dbns, parts };
}

// ── diffing, for --check ─────────────────────────────────────────────────────
const isRecord = (v: unknown): v is Record<string, unknown> =>
  typeof v === "object" && v !== null && !Array.isArray(v);

function diffPaths(committed: unknown, fresh: unknown, at = "$", out: string[] = []): string[] {
  if (out.length >= 40) return out;
  if (isRecord(committed) && isRecord(fresh)) {
    for (const k of new Set([...Object.keys(committed), ...Object.keys(fresh)])) {
      if (!(k in committed)) out.push(`${at}.${k}: absent from the committed file`);
      else if (!(k in fresh)) out.push(`${at}.${k}: no longer produced`);
      else diffPaths(committed[k], fresh[k], `${at}.${k}`, out);
    }
    return out;
  }
  if (Array.isArray(committed) && Array.isArray(fresh)) {
    if (committed.length !== fresh.length) {
      out.push(`${at}: length ${committed.length} → ${fresh.length}`);
      return out;
    }
    for (let i = 0; i < committed.length; i++) diffPaths(committed[i], fresh[i], `${at}[${i}]`, out);
    return out;
  }
  if (committed !== fresh) {
    out.push(`${at}: committed ${JSON.stringify(committed)} → regenerated ${JSON.stringify(fresh)}`);
  }
  return out;
}

// ── build ────────────────────────────────────────────────────────────────────
async function build() {
  // Socrata carries seven years; the other five come from the committed parse
  // of the SCA's own PDFs, through the same gate the loader checks. Both must
  // see one panel — a year in the warehouse and not in the packet is a figure
  // the page and the database disagree about.
  const recovered = readRecoveredYears();
  const socrataRows = await fetchSocrataAll<BlueBookRow>(BLUEBOOK);
  const bb = [...socrataRows, ...recovered.flatMap((r) => r.rows)];
  const hist = await fetchSocrataAll<Record<string, string>>(BLUEBOOK_HIST_ORG);
  const { dbns, parts } = await loadDbnUniverse();
  const dbnIndex = buildDbnIndex(dbns);

  const bySnapshot = groupBySnapshot(bb);
  // schoolYearForSnapshot throws on an unmapped vintage rather than guessing
  // from the posting date; the lag runs three to eighteen months (doc §2).
  const snapshots = [...bySnapshot.keys()].sort();
  const yearOf = new Map(snapshots.map((s) => [s, schoolYearForSnapshot(s)]));
  const years = [...yearOf.values()].sort();
  const snapshotOf = new Map([...yearOf].map(([s, y]) => [y, s]));

  const maxDataAsOf = bb
    .map((r) => (r.data_as_of ?? "").trim())
    .filter(Boolean)
    .sort()
    .at(-1);
  const generated_at = AS_OF_OVERRIDE ?? maxDataAsOf ?? null;

  // ── by_year ───────────────────────────────────────────────────────────────
  const by_year: Record<string, unknown> = {};
  const universe_by_year: Record<string, unknown> = {};
  const sensitivity_all_orgs: Record<string, unknown> = {};
  const utilization_agreement: Record<string, unknown> = {};
  const matchedByYear = new Map<string, OrgRecord[]>();
  const completeByYear = new Map<string, CompleteBuilding[]>();
  let inconsistentBuildingRows = 0;
  const multiOrgAlternative: Record<string, number> = {};

  for (const year of years) {
    const snapshot = snapshotOf.get(year) as string;
    const rows = bySnapshot.get(snapshot) as BlueBookRow[];
    const orgs = rollUpOrgs(rows);

    // School grain. Filter to organizations whose org code resolves to exactly
    // one DBN; an ambiguous code is dropped rather than assigned (doc §3.1).
    const withUtil = orgsWithUtilization(orgs.values());
    const matched: OrgRecord[] = [];
    const dropped: { org_code: string; name: string | null; reason: string }[] = [];
    for (const o of withUtil) {
      const res = resolveDbn(o.orgCode, dbnIndex);
      if (res.kind === "resolved") matched.push(o);
      else dropped.push({ org_code: o.orgCode, name: o.name, reason: res.kind });
    }
    dropped.sort((a, b) => (a.org_code < b.org_code ? -1 : 1));
    matchedByYear.set(year, matched);

    // Building grain.
    const { records, complete } = completeBuildings(rows);
    completeByYear.set(year, complete);
    const ids = new Set(complete.map((b) => b.id));
    const multi = multiOrgBuildings(rows, ids);
    multiOrgAlternative[year] = [...records.values()].filter(
      (b) => ids.has(b.buildingId) && b.orgCodes.length > 1,
    ).length;

    // Do two complete rows for the same building ever disagree? (They do not.)
    const seen = new Map<string, string>();
    for (const r of rows) {
      const id = (r.bldg_id ?? "").trim();
      const e = num(r.bldg_enroll);
      const c = num(r.target_bldg_cap);
      const u = num(r.target_bldg_util);
      if (!id || e === null || e <= 0 || c === null || c <= 0) continue;
      const key = `${e}|${c}|${u}`;
      const prior = seen.get(id);
      if (prior === undefined) seen.set(id, key);
      else if (prior !== key) inconsistentBuildingRows += 1;
    }

    by_year[year] = {
      snapshot,
      school: schoolDistribution(matched),
      building: {
        ...distribution(
          complete.map((b) => b.utilization),
          sum(complete.map((b) => b.enrollment)),
          sum(complete.map((b) => b.capacity)),
        ),
        multi_org_buildings: multi,
        multi_org_share: complete.length ? r1((100 * multi) / complete.length) : null,
      },
    };

    const orgsTotal = orgs.size;
    universe_by_year[year] = {
      organizations: orgsTotal,
      with_enrollment_and_capacity: withUtil.length,
      matched: matched.length,
      match_rate: withUtil.length ? r1((100 * matched.length) / withUtil.length) : null,
      dropped,
    };
    sensitivity_all_orgs[year] = schoolDistribution(withUtil);
    utilization_agreement[year] = utilizationAgreement(orgs.values());
  }

  // ── decomposition ─────────────────────────────────────────────────────────
  // Continuing buildings only, so seats added in NEW buildings sit outside it.
  // Additive split of the change in Σenrollment / Σcapacity: the enrollment
  // effect holds capacity at the first year, the capacity effect is the
  // remainder. Building grain, because a building is what gets emptied or
  // closed; a school moving between buildings is not a change in seats.
  // One span, run on the buildings complete in BOTH of its endpoint years. The
  // continuing set is a function of the pair, so a sub-span is not a restriction
  // of the whole-span set and the segments below do not have to sum to it.
  function splitSpan(from: string, to: string) {
    const a = new Map((completeByYear.get(from) ?? []).map((b) => [b.id, b]));
    const z = new Map((completeByYear.get(to) ?? []).map((b) => [b.id, b]));
    const ids = [...a.keys()].filter((id) => z.has(id)).sort();
    const e0 = sum(ids.map((id) => (a.get(id) as CompleteBuilding).enrollment));
    const e1 = sum(ids.map((id) => (z.get(id) as CompleteBuilding).enrollment));
    const c0 = sum(ids.map((id) => (a.get(id) as CompleteBuilding).capacity));
    const c1 = sum(ids.map((id) => (z.get(id) as CompleteBuilding).capacity));
    const u0 = (100 * e0) / c0;
    const u1 = (100 * e1) / c1;
    const counterfactual = (100 * e1) / c0; // later students in earlier seats
    return {
      from,
      to,
      ids,
      fromSize: a.size,
      toSize: z.size,
      e0,
      e1,
      c0,
      c1,
      u0,
      u1,
      counterfactual,
    };
  }

  const whole = splitSpan(DECOMP_FROM, DECOMP_TO);
  const { ids: continuing, e0, e1, c0, c1, u0, u1, counterfactual } = whole;
  const fromB = { size: whole.fromSize };
  const toB = { size: whole.toSize };

  // The same split on every consecutive published pair across the span, so the
  // endpoint reading can be checked rather than trusted.
  const spanYears = years.filter((y) => y >= DECOMP_FROM && y <= DECOMP_TO);
  const segments = spanYears
    .slice(0, -1)
    .map((y, i) => splitSpan(y, spanYears[i + 1]))
    .map((g) => ({
    from: g.from,
    to: g.to,
    continuing_buildings: g.ids.length,
    utilization_from_pct: r1(g.u0),
    utilization_to_pct: r1(g.u1),
    utilization_change_pp: r1(g.u1 - g.u0),
    enrollment_effect_pp: r1(g.counterfactual - g.u0),
    capacity_effect_pp: r1(g.u1 - g.counterfactual),
    enrollment_change_pct: r1(100 * (g.e1 / g.e0 - 1)),
    capacity_change_pct: r1(100 * (g.c1 / g.c0 - 1)),
  }));

  const decomposition = {
    from: DECOMP_FROM,
    to: DECOMP_TO,
    grain: "building",
    continuing_buildings: continuing.length,
    buildings_from: fromB.size,
    buildings_to: toB.size,
    enrollment_from: e0,
    enrollment_to: e1,
    enrollment_change_pct: r1(100 * (e1 / e0 - 1)),
    capacity_from: c0,
    capacity_to: c1,
    capacity_change_pct: r1(100 * (c1 / c0 - 1)),
    utilization_from_pct: r1(u0),
    utilization_to_pct: r1(u1),
    utilization_change_pp: r1(u1 - u0),
    enrollment_effect_pp: r1(counterfactual - u0),
    capacity_effect_pp: r1(u1 - counterfactual),
    span_years: 7,
    span_published_between: spanYears.slice(1, -1),
    span_unpublished: DECOMP_SPAN_UNPUBLISHED,
    span_note:
      `${DECOMP_FROM} and ${DECOMP_TO} are 7 school years apart and the panel carries ` +
      `${spanYears.length - 2} of the 6 years in between — every one except ` +
      `${DECOMP_SPAN_UNPUBLISHED.join(", ")}, which the SCA never published. Four of those ` +
      "intermediate years are not on NYC Open Data and are read from the SCA's own PDFs " +
      "(pipeline_nyc/parse/bluebook.py). `segments` is the path between the endpoints, so the " +
      "endpoint figures can be read against it.",
    segments,
    segments_note:
      "Each segment is computed on the buildings complete in both of ITS endpoint years, so the " +
      "continuing sets differ from segment to segment and the segment effects are not required to " +
      "sum to the whole-span effects. The 2019-20 → 2021-22 segment spans the unpublished " +
      "2020-21 and is two years wide; every other segment is one.",
    note:
      "Capacity fell over the period, which raised utilization; the decline is enrollment. " +
      "Continuing buildings only, so seats in buildings opened after 2018-19 are outside this.",
    rounding_note:
      "The two effects are additive before rounding (unrounded: -14.240 and +2.485 against a total " +
      "of -11.755). To one decimal they read -14.2 and +2.5, which sum to -11.7 against a printed " +
      "total of -11.8; the 0.1 pp is display rounding, not a residual term.",
  };

  // ── by_district ───────────────────────────────────────────────────────────
  // geo_dist is the GEOGRAPHIC district of the building, which is not the
  // administrative district for D75, D79 or charters (doc §3.1). These are
  // therefore districts of place, not of governance.
  const districtOrgs = new Map<string, OrgRecord[]>();
  let unassigned = 0;
  for (const o of matchedByYear.get(DISTRICT_YEAR) ?? []) {
    if (o.geoDistrict === null) {
      unassigned += 1;
      continue;
    }
    const key = String(o.geoDistrict);
    const bucket = districtOrgs.get(key);
    if (bucket) bucket.push(o);
    else districtOrgs.set(key, [o]);
  }
  const districts: Record<string, { schools: number; utilization_pct: number; share_under_50: number }> = {};
  for (const key of [...districtOrgs.keys()].sort((a, b) => Number(a) - Number(b))) {
    const os = districtOrgs.get(key) as OrgRecord[];
    const e = sum(os.map((o) => o.enrollment as number));
    const c = sum(os.map((o) => o.targetCapacity as number));
    districts[key] = {
      schools: os.length,
      utilization_pct: r1((100 * e) / c),
      share_under_50: r1((100 * os.filter((o) => (o.utilizationPct as number) < 50).length) / os.length),
    };
  }
  const by_district = {
    year: DISTRICT_YEAR,
    grain: "school",
    statistic: "Σenrollment / Σcapacity within the district, and the share of its schools under 50%",
    schools_without_a_district: unassigned,
    districts,
    ranked_by_utilization: Object.keys(districts).sort(
      (a, b) => districts[a].utilization_pct - districts[b].utilization_pct,
    ),
  };

  // ── two capacity definitions ──────────────────────────────────────────────
  // The retired series is the pre-2014 formula on the same schools and year, so
  // the comparison isolates the denominator. Its columns are named differently;
  // they are mapped onto the live shape so the same roll-up applies.
  const histRows: BlueBookRow[] = hist.map((r) => ({
    org_id: r.org_id,
    organization_name: r.organization_name,
    bldg_id: r.bldg_id,
    geo_dist: r.geo_dist,
    org_enroll: r.enroll,
    org_target_cap: r.historical_capacity,
    org_target_util: r.historical_utilization,
  }));
  const historical = rollUpOrgs(histRows);
  const target = rollUpOrgs(bySnapshot.get(snapshotOf.get(HISTORICAL_YEAR) as string) as BlueBookRow[]);
  const bothBases = [...target.keys()]
    .filter((code) => {
      const h = historical.get(code);
      const t = target.get(code);
      return !!h && !!t && h.targetCapacity !== null && t.targetCapacity !== null;
    })
    .sort();
  const ratios = bothBases.map(
    (code) =>
      (target.get(code) as OrgRecord).targetCapacity! / (historical.get(code) as OrgRecord).targetCapacity!,
  );
  // The ratio needs a capacity on both bases; the utilization comparison needs
  // students as well, on both bases, for the reason in `hasUtilization`.
  const bothUtil = bothBases.filter(
    (code) => hasUtilization(target.get(code)) && hasUtilization(historical.get(code)),
  );
  const gaps = bothUtil.map(
    (code) =>
      (target.get(code) as OrgRecord).utilizationPct! - (historical.get(code) as OrgRecord).utilizationPct!,
  );
  const sortedRatios = [...ratios].sort((a, b) => a - b);
  const sortedGaps = [...gaps].sort((a, b) => a - b);
  const capacity_definitions = {
    year: HISTORICAL_YEAR,
    schools_on_both_bases: bothBases.length,
    schools_with_both_utilizations: bothUtil.length,
    // Each statistic carries its OWN n. The two universes differ — a school can
    // have a capacity on both bases but a utilization on only one (it needs an
    // enrollment too). A bare pair of counts beside four unlabelled statistics
    // makes it easy to attach a statistic to the wrong universe, so the label
    // travels with the number instead.
    ratio_target_over_historical: {
      n: bothBases.length,
      median: r3(quantileExclusive(sortedRatios, 0.5)),
      p10: r3(quantileExclusive(sortedRatios, 0.1)),
      p90: r3(quantileExclusive(sortedRatios, 0.9)),
    },
    utilization_gap_pp: {
      n: bothUtil.length,
      median: r1(quantileExclusive(sortedGaps, 0.5)),
      p10: r1(quantileExclusive(sortedGaps, 0.1)),
      p90: r1(quantileExclusive(sortedGaps, 0.9)),
    },
    share_under_50_n: bothUtil.length,
    share_under_50_target: r1(
      (100 * bothUtil.filter((c) => (target.get(c) as OrgRecord).utilizationPct! < 50).length) /
        bothUtil.length,
    ),
    share_under_50_historical: r1(
      (100 * bothUtil.filter((c) => (historical.get(c) as OrgRecord).utilizationPct! < 50).length) /
        bothUtil.length,
    ),
    note:
      "Same schools, same year, two capacity formulas. The capacity RATIO is over the " +
      "schools carrying both capacity figures; the utilization gap and both under-50% " +
      "shares are over the smaller set that also carries a utilization on each basis, " +
      "which needs an enrollment as well. A figure naming no denominator is therefore " +
      "ambiguous.",
  };

  // ── qa ────────────────────────────────────────────────────────────────────
  // Check (iv) of the validation spec: does the published utilization equal the
  // reconstruction. For a single-building organization the comparison is
  // unambiguous; a split-site organization publishes a figure per building row,
  // so "matches any of them" is the weaker test and is reported apart.
  const singleBuildingWorst = Math.min(
    ...years.map((y) => {
      const t = utilization_agreement[y] as ReturnType<typeof utilizationAgreement>;
      return t.singleBuilding.n ? (100 * t.singleBuilding.within1pp) / t.singleBuilding.n : 0;
    }),
  );
  const worstMatchRate = Math.min(
    ...years.map((y) => (universe_by_year[y] as { match_rate: number }).match_rate),
  );
  // Additivity is a property of the unrounded effects. Checking the rounded
  // fields instead would report the 0.1 pp of display rounding as a defect.
  const additivity = Math.abs((counterfactual - u0) + (u1 - counterfactual) - (u1 - u0));
  const checks = [
    {
      check: "iv_published_utilization_reconstructed",
      pass: singleBuildingWorst >= 100,
      detail: `single-building organizations within 1 pp of the published figure, worst year ${r1(singleBuildingWorst)}%`,
    },
    {
      check: "panel_years_mapped",
      pass: years.length === Object.keys(SNAPSHOT_YEAR).length
        && snapshots.length === Object.keys(SNAPSHOT_YEAR).length,
      detail: `${snapshots.length} snapshots, ${years.length} school years; an unmapped vintage throws rather than guessing`,
    },
    {
      check: "join_match_rate",
      pass: worstMatchRate >= 99.5,
      detail: `org code → DBN, worst year ${r1(worstMatchRate)}% of organizations carrying students and seats`,
    },
    {
      check: "building_row_consistency",
      pass: inconsistentBuildingRows === 0,
      detail: `${inconsistentBuildingRows} buildings where two complete rows disagree on enrollment, capacity or utilization`,
    },
    {
      check: "decomposition_additive",
      pass: additivity <= 1e-9,
      detail: `unrounded enrollment and capacity effects sum to the total change within ${additivity.toExponential(1)} pp`,
    },
  ];
  const qa = {
    verdict: checks.every((c) => c.pass) ? "PASS" : "FAIL",
    checks,
    utilization_agreement,
    multi_org_definition: {
      reported: "organizations with more than 0 students in the building",
      alternative: "organizations with a non-null enrollment in the building, zero included",
      alternative_counts: multiOrgAlternative,
    },
    not_verified: [
      "No second publisher states NYC school capacity; doc §5.2 tests it against the room inventory, which is the same publisher.",
      "2020-21 cannot be checked because the SCA never published it (doc §2).",
      "The five years read from the SCA PDFs are checked against Socrata only by proxy: run on the 2025-26 PDF, the parser reproduces the published 2025-26 extract on every row key and every organization-grain enrollment, target capacity and utilization (doc §2.1). The report prints no zero in the PK-capacity and cluster-room columns, so on about 2% of rows those read absent where Socrata reads 0.",
      "There is no public closure list for NYC, so nothing here supports a predictive claim (doc §6).",
    ],
  };

  // ── packet ────────────────────────────────────────────────────────────────
  const packet = {
    meta: {
      generated_at,
      generated_at_basis: AS_OF_OVERRIDE
        ? "--as-of / NYC_PACKET_AS_OF override"
        : `max data_as_of in ${BLUEBOOK}; a wall clock would churn the committed diff`,
      script: "scripts/analysis/nyc-capacity-packet.ts",
      years,
      missing_years: ["2020-21"],
      recovered_years: recovered.map((r) => ({
        year: r.schoolYear,
        snapshot: r.snapshot,
        rows: r.rows.length,
        source: "SCA PDF, parsed by pipeline_nyc/parse/bluebook.py under a two-parser gate",
      })),
      panel_note:
        "Twelve school years, 2013-14 to 2025-26, with one gap: 2020-21, which the SCA never " +
        "published. Seven come from NYC Open Data; the other five are parsed from the SCA's own " +
        "PDFs and carry the same check (iv). A time-series figure plots twelve points and breaks " +
        "the line at 2020-21.",
      snapshot_year: Object.fromEntries(snapshots.map((s) => [s, yearOf.get(s) as string])),
      snapshot_year_note:
        "data_as_of identifies the vintage and is not a school year: for the seven Socrata vintages it " +
        "is a posting date, three to eighteen months after the count, and for the five PDF years it is " +
        "the audited-register date the report states. The school year is measured against the DOE " +
        "Demographic Snapshot (doc §2), not read off the date.",
      universe_note:
        "School grain covers organizations whose org code resolves to exactly one DBN in the 2019-20 " +
        "School Locations directory plus every year of the DOE Demographic Snapshot. Building grain " +
        "covers every building with an enrollment, a target capacity and a published utilization.",
      capacity_note:
        "Target capacity is the SCA's planning construct, not a fire-code occupancy limit. It counts " +
        "Pre-K rooms, each at up to 18 seats full-day or 36 half-day (capped by the room's area ÷ 35 " +
        "sq ft), and enrollment includes 3-K and Pre-K students, so Pre-K is on both sides of the " +
        "ratio; the Blue Book's separate PK capacity column restates the Pre-K share and is not " +
        "added to it. Charters in private space are absent from the Blue Book, so any citywide " +
        "figure is a DOE-buildings figure.",
      direction_note:
        "Every measure here is NEUTRAL. Utilization describes how full a building is; it does not rank " +
        "the school in it.",
      quantile_method:
        "exclusive (the default of Python's statistics.quantiles), which is what produced the " +
        "percentile table in doc §4.1",
      sources: [
        {
          id: BLUEBOOK,
          url: socrataUrl(BLUEBOOK),
          name: "Enrollment Capacity And Utilization Reports (SCA Blue Book)",
          rows: socrataRows.length,
          role: "enrollment, target capacity and published utilization at both grains, seven school years",
        },
        {
          id: "sca_bluebook_pdf",
          url: "https://www.nycsca.org/Community/Capital-Plan-Reports-Data",
          name: "SCA Enrollment, Capacity & Utilization Report (the annual PDF)",
          rows: recovered.reduce((n, r) => n + r.rows.length, 0),
          role:
            "the five school years Socrata does not carry — " +
            `${recovered.map((r) => r.schoolYear).join(", ")}. Parsed by ` +
            "pipeline_nyc/parse/bluebook.py under a two-parser gate and committed to " +
            "data/cities/nyc/reference/parsed/",
        },
        {
          id: BLUEBOOK_HIST_ORG,
          url: socrataUrl(BLUEBOOK_HIST_ORG),
          name: "Enrollment Capacity And Utilization Reports - Historical by Organization",
          rows: hist.length,
          role: "the pre-2014 capacity formula, 2013-14 only — the second denominator",
        },
        ...parts.map((p) => ({
          id: p.source,
          url: p.source.startsWith("socrata:") ? socrataUrl(p.source.slice("socrata:".length)) : DEMO_XLSX_URL,
          name:
            p.source === `socrata:${DIRECTORY}`
              ? "2019-20 School Locations"
              : "Demographic Snapshot (school level)",
          dbns_contributed: p.dbns,
          role: "DBN universe the org-code join resolves against",
        })),
      ],
      source_dependency_note:
        "The InfoHub spreadsheet is the only input without a Socrata copy, and its filename carries " +
        "its year range, so it rolls annually. When it 404s this script fails rather than rebuilding a " +
        "shorter universe, because a shorter universe restates every school-grain figure quietly.",
      qa_verdict: qa.verdict,
    },
    universe: {
      dbns: dbns.size,
      org_codes: dbnIndex.size,
      ambiguous_org_codes: [...dbnIndex.entries()].filter(([, v]) => v.length > 1).map(([k]) => k).sort(),
      by_year: universe_by_year,
      sensitivity_all_orgs,
      sensitivity_note:
        "sensitivity_all_orgs drops the DBN filter and keeps every organization with students and " +
        "seats. It is the same population plus the six or seven unmatched organizations a year, and " +
        "it is reported so the filter's effect is measurable rather than assumed.",
    },
    by_year,
    decomposition,
    by_district,
    capacity_definitions,
    qa,
  };
  return packet;
}

// ── main ─────────────────────────────────────────────────────────────────────
async function main() {
  const packet = await build();
  const serialized = JSON.stringify(packet, null, 1);

  if (CHECK) {
    if (!fs.existsSync(OUT)) {
      console.error(`[check] ${OUT} does not exist; run the script without --check first.`);
      process.exit(1);
    }
    const committed = JSON.parse(fs.readFileSync(OUT, "utf-8")) as unknown;
    const drift = diffPaths(committed, JSON.parse(serialized) as unknown);
    if (drift.length === 0) {
      console.log(`[check] ${path.relative(process.cwd(), OUT)} matches the live sources.`);
      return;
    }
    console.error(`[check] ${drift.length} difference(s) between the committed packet and the live sources:`);
    for (const d of drift) console.error(`  ${d}`);
    console.error(
      "\nThe upstream data has been restated, or a convention in this script has changed. Re-read " +
        "docs/design/10_nyc_enrollment_capacity_utilization.md before regenerating: the doc quotes " +
        "these numbers and would need the same revision.",
    );
    process.exit(1);
  }

  fs.mkdirSync(path.dirname(OUT), { recursive: true });
  fs.writeFileSync(OUT, serialized);
  console.log(`[packet] wrote ${path.relative(process.cwd(), OUT)} (${(serialized.length / 1024).toFixed(0)} KB)`);
  console.log(`  as of ${packet.meta.generated_at} — ${packet.meta.generated_at_basis}`);
  console.log(`  QA ${packet.qa.verdict}: ${packet.qa.checks.filter((c) => c.pass).length}/${packet.qa.checks.length} checks`);
  for (const c of packet.qa.checks) if (!c.pass) console.log(`    FAILED ${c.check}: ${c.detail}`);
  console.log(`  ${"year".padEnd(9)}${"n".padStart(6)}${"median".padStart(8)}${"<50%".padStart(8)}${"<33%".padStart(8)}${">100%".padStart(8)}${"Σ/Σ".padStart(8)}`);
  for (const year of packet.meta.years) {
    const s = (packet.by_year as Record<string, { school: Distribution }>)[year].school;
    console.log(
      `  ${year.padEnd(9)}${String(s.n).padStart(6)}${String(s.median).padStart(8)}` +
        `${String(s.share_under_50).padStart(8)}${String(s.share_under_33).padStart(8)}` +
        `${String(s.share_over_100).padStart(8)}${String(s.sum_over_sum).padStart(8)}`,
    );
  }
  const d = packet.decomposition;
  console.log(
    `  decomposition ${d.from} → ${d.to}: ${d.continuing_buildings} buildings, ` +
      `${d.utilization_change_pp} pp = ${d.enrollment_effect_pp} enrollment ${d.capacity_effect_pp >= 0 ? "+" : ""}${d.capacity_effect_pp} capacity`,
  );
}

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