// Philadelphia enrollment / capacity / utilization packet — the analysis behind
// the Philadelphia capacity page. Writes data/analysis/philly-enrollment-capacity.json;
// the page renders ONLY numbers from that file.
//
// Source is the COMMITTED extract of SDP's Facilities Data Dashboard (Qlik app
// 2d47abaf-02e1-4671-adba-ea661203eda5, anonymous engine API). No network, no
// database: this script reads the two parsed CSVs and the snapshot's
// provenance.json, plus the committed NYC and Miami-Dade packets for the
// cross-city figures (cross_city, and NYC's capacity-definition swing), and
// nothing else, so it reproduces byte-for-byte from a clean checkout. It throws
// if either packet has no SY2024-25 school-grain block, and --check drifts
// whenever either packet's figures change: regenerate this packet after
// regenerating either of them.
//
// Findings and caveats: docs/cities/philly/06_enrollment_capacity_utilization.md
// Probe:                docs/cities/philly/probe/facilities.py
//
// THREE GRAINS, and mixing them corrupts the data (doc §3.3):
//   site      — capacity, enrollment, PreK allotment, utilization, Program Change
//   building  — floor area, year built, condition, use type, BLDGCAPACITY
//   school    — ULCS code; school and site are many-to-many (doc §3.1)
// This packet is at SITE grain, except the split-campus sensitivity (one unit per
// school) and the NYC and Miami-Dade rows of cross_city (school grain, read from
// their own packets). Buildings enter only via a sum-per-site roll-up over
// DISTINCT buildings (denominator_sensitivity): the building extract repeats a
// building once per programme on its site (502 rows, 399 buildings), so the
// roll-up goes through _facilities.buildingCapacityBySite, which dedupes on
// building_id. They are never joined onto the site cube as a dimension.
//
// Run:    npx tsx scripts/analysis/philly-capacity-packet.ts
// Check:  npx tsx scripts/analysis/philly-capacity-packet.ts --check
//         (regenerates in memory, diffs against the committed JSON, exits 1 on drift)

import fs from "node:fs";
import path from "node:path";
import { parse } from "csv-parse/sync";
import { buildingCapacityBySite, buildingRepeatConflicts } from "../loaders/philly/_facilities";

const ROOT = process.cwd();
const PARSED = path.join(ROOT, "data", "cities", "philly", "reference", "parsed");
const SITES_CSV = path.join(PARSED, "sdp_facilities_sites.csv");
const BUILDINGS_CSV = path.join(PARSED, "sdp_facilities_buildings.csv");
const SOURCE_DIR = path.join(ROOT, "data", "cities", "philly", "sources", "sdp_facilities_dashboard");
const OUT = path.join(ROOT, "data", "analysis", "philly-enrollment-capacity.json");
// NYC's two-capacity-definition sensitivity, the comparison
// denominator_sensitivity.note draws (doc §6.5). Read from the committed NYC
// packet rather than restated here, so it cannot go stale.
const NYC_PACKET = path.join(ROOT, "data", "analysis", "nyc-enrollment-capacity.json");
// Miami-Dade's school-grain utilization block, for the cross-city comparison only.
const MIAMI_PACKET = path.join(ROOT, "data", "analysis", "miami-enrollment.json");

const VINTAGE = "2024-25";
const SECTOR_DISTRICT = "District";
const SECTOR_PREK = "Pre-K";
const CLOSE_PREFIX = "Close"; // the district's own Program Change vocabulary
const LIFT_CUTS = [33, 50, 66, 75] as const;
// Size comparison and ablation (doc §4.3), on enrollment including allotted
// Pre-K seats — the utilization numerator.
const SIZE_CUT = 300;
const ABLATION_SPLIT = 400;
// The utilization cut the split-campus sensitivity re-reads: the one doc §4.3 and
// the page's Fig 3 headline.
const MERGED_LIFT_CUT = 66;

// Fisher two-sided p-values as measured by the probe (scipy.stats.fisher_exact) and
// published in doc §4.3. Carried, not recomputed — see decision_lift.p_value_note.
const FISHER_P: Record<(typeof LIFT_CUTS)[number], number> = {
  33: 0.0043,
  50: 0.00093,
  66: 0.0012,
  75: 0.013,
};

// The same, for the size comparison, the ablation (doc §4.3) and the split-campus
// sensitivity (doc §4.1), measured with scipy.stats.fisher_exact on the distinct
// sites below. Each is keyed to the 2×2 table it was measured on — [flagged
// Close, flagged other, rest Close, rest other] — and buildPacket throws if the
// counts it derives differ, so a carried p cannot outlive its table.
type CarriedP = { table: [number, number, number, number]; p: number };
const CARRIED_P: Record<"size" | "ablation_under_400" | "ablation_400_plus" | "split_campus_merged", CarriedP> = {
  size: { table: [12, 35, 4, 164], p: 3.2e-6 }, // enrollment < 300
  ablation_under_400: { table: [9, 29, 4, 49], p: 0.037 }, // utilization < 50
  ablation_400_plus: { table: [3, 35, 0, 86], p: 0.027 }, // utilization < 66
  split_campus_merged: { table: [14, 91, 2, 105], p: 0.0015 }, // utilization < 66
};

// ── CSV row shapes (the extract's real column names) ─────────────────────────
type SiteRow = {
  site_id: string; ulcs_code: string; site_name: string; sector: string;
  school_level: string; program_change: string; utilization_band: string;
  capacity: string; enrollment: string; prek_allotment: string;
  enroll_with_allotment: string; utilization: string; project_recommendation: string;
};
type BuildingRow = {
  building_id: string; building_name: string; site_id: string; site_name: string;
  ulcs_code: string; use_type: string; status: string; gross_sqft: string;
  year_built: string; floor_count: string; lot_size: string; building_capacity: string;
  building_fci: string; facility_condition_index: string; replacement_value: string;
};

// ── helpers ──────────────────────────────────────────────────────────────────
const txt = (v: string | undefined) => (v ?? "").trim();
function num(v: string | undefined): number | null {
  const t = txt(v);
  if (!t || t === "-") return null;
  const n = Number(t.replace(/[$,]/g, ""));
  return Number.isFinite(n) ? n : null;
}
const sum = (xs: number[]) => xs.reduce((a, b) => a + b, 0);
const r1 = (n: number) => Math.round(n * 10) / 10;
const r3 = (n: number) => Math.round(n * 1000) / 1000;
// A one-decimal figure printed at its stored precision, so 25 reads 25.0 beside 24.7.
const f1 = (n: number | null) => (n === null ? "n/a" : n.toFixed(1));
// The page's p-value format (fmtP): below 0.0001 it prints the bound, not the digits.
const pText = (p: number) => (p < 1e-4 ? "p < 0.0001" : `p = ${String(p)}`);
const WORDS = ["no", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"];
const word = (n: number) => WORDS[n] ?? String(n);
const cap1 = (t: string) => t.charAt(0).toUpperCase() + t.slice(1);

// Quantile on the EXCLUSIVE (Weibull / R type-6) convention: position p*(n+1) in
// 1-indexed order. This is the default of Python's statistics.quantiles, which the
// probe used (`st.quantiles(u, n=100)`), so the packet reproduces the published
// table rather than a differently-interpolated near-miss. The median is identical
// under every convention; p10/p25/p75/p90 are not.
function quantileExclusive(sorted: number[], p: number): number {
  if (!sorted.length) return NaN;
  if (sorted.length === 1) return sorted[0];
  const pos = p * (sorted.length + 1) - 1;
  if (pos <= 0) return sorted[0];
  if (pos >= sorted.length - 1) return sorted[sorted.length - 1];
  const lo = Math.floor(pos), hi = Math.ceil(pos);
  return lo === hi ? sorted[lo] : sorted[lo] + (sorted[hi] - sorted[lo]) * (pos - lo);
}
function medianOf(sorted: number[]): number {
  if (!sorted.length) return NaN;
  const m = sorted.length >> 1;
  return sorted.length % 2 ? sorted[m] : (sorted[m - 1] + sorted[m]) / 2;
}
const share = (xs: number[], pred: (x: number) => boolean) =>
  xs.length ? r1((100 * xs.filter(pred).length) / xs.length) : null;

// The published table prints p10/p25/p75/p90 as integers and the median to one
// decimal; reproduced here so the page shows the doc's figures unchanged.
function distributionOf(util: number[], enrollTotal: number, capacityTotal: number) {
  const s = [...util].sort((a, b) => a - b);
  return {
    n: s.length,
    p10: s.length ? Math.round(quantileExclusive(s, 0.1)) : null,
    p25: s.length ? Math.round(quantileExclusive(s, 0.25)) : null,
    median: s.length ? r1(medianOf(s)) : null,
    p75: s.length ? Math.round(quantileExclusive(s, 0.75)) : null,
    p90: s.length ? Math.round(quantileExclusive(s, 0.9)) : null,
    share_under_50: share(s, (x) => x < 50),
    share_under_33: share(s, (x) => x < 33),
    share_over_100: share(s, (x) => x > 100),
    sum_over_sum: capacityTotal ? r1((100 * enrollTotal) / capacityTotal) : null,
  };
}

// Close rate on each side of a split, and their ratio. A null lift means no unit
// on the other side carries a Close recommendation: the ratio is unbounded.
function liftOf<T>(universe: T[], flag: (x: T) => boolean, isClose: (x: T) => boolean) {
  const flagged = universe.filter(flag);
  const rest = universe.filter((x) => !flag(x));
  const a = flagged.filter(isClose).length;
  const c = rest.filter(isClose).length;
  const flaggedRate = flagged.length ? a / flagged.length : null;
  const restRate = rest.length ? c / rest.length : null;
  return {
    flagged_n: flagged.length,
    flagged_close_n: a,
    flagged_close_rate: flaggedRate === null ? null : r1(100 * flaggedRate),
    rest_n: rest.length,
    rest_close_n: c,
    rest_close_rate: restRate === null ? null : r1(100 * restRate),
    lift: flaggedRate !== null && restRate ? Math.round((flaggedRate / restRate) * 100) / 100 : null,
  };
}

function carriedP(key: keyof typeof CARRIED_P, l: ReturnType<typeof liftOf>): number {
  const { table, p } = CARRIED_P[key];
  const got = [l.flagged_close_n, l.flagged_n - l.flagged_close_n, l.rest_close_n, l.rest_n - l.rest_close_n];
  if (got.some((v, i) => v !== table[i])) {
    throw new Error(`CARRIED_P.${key}: the 2×2 table is now ${got.join("/")}, not the ${table.join("/")} its Fisher p (${p}) was measured on — re-measure it with scipy.stats.fisher_exact and update CARRIED_P`);
  }
  return p;
}

// ── load ─────────────────────────────────────────────────────────────────────
function readCsv<T>(file: string): T[] {
  return parse(fs.readFileSync(file), { columns: true, skip_empty_lines: true, bom: true }) as T[];
}

function latestSourceDir(): string {
  const dirs = fs.readdirSync(SOURCE_DIR, { withFileTypes: true })
    .filter((d) => d.isDirectory() && /^\d{4}-\d{2}-\d{2}$/.test(d.name))
    .map((d) => d.name).sort();
  if (!dirs.length) throw new Error(`no dated snapshot under ${SOURCE_DIR}`);
  return dirs[dirs.length - 1];
}

type NycCapacityDefinitions = {
  year: string;
  share_under_50_n: number;
  share_under_50_target: number;
  share_under_50_historical: number;
};
// The fields read from the two other packets' school-grain blocks.
type NycSchoolYear = {
  n: number; median: number; share_under_50: number; share_under_33: number;
  share_over_100: number; sum_over_sum: number;
};
type NycPacket = { capacity_definitions: NycCapacityDefinitions; by_year: Record<string, { school?: NycSchoolYear }> };
type MiamiUtilization = {
  year: string; n: number; headcount: { p50: number };
  thresholds_headcount: { below_33: number; below_50: number; at_or_above_100: number };
  district_sum_headcount_pct: number;
};

type Provenance = {
  source_id?: string; url?: string; retrieved_at?: string; sha256?: string;
  publisher?: string; format?: string; qlik_app_id?: string;
  rows_sites?: number; rows_buildings?: number; utilization_definition?: string;
};

// ── the site universe ────────────────────────────────────────────────────────
// A site hosting a school plus its Pre-K program (or a co-located charter) appears
// as several rows carrying IDENTICAL capacity / enrollment / utilization figures —
// 91 of 328 sites in this extract. Counting rows double-weights them, so the unit
// is the DISTINCT SITE. Where a site_id has several rows the District row is
// preferred, because school_level and Program Change DO vary across the programs on
// one site even though the capacity figures do not.
type Site = {
  site_id: string;
  row: SiteRow;
  program_rows: SiteRow[];
  capacity: number;
  enroll_with_allotment: number;
  utilization: number; // percent, recomputed (PreK is inside the numerator)
};

function buildSites(rows: SiteRow[]) {
  const bySite = new Map<string, SiteRow[]>();
  for (const r of rows) {
    const id = txt(r.site_id);
    if (!id) continue;
    const bucket = bySite.get(id);
    if (bucket) bucket.push(r); else bySite.set(id, [r]);
  }
  const distinct = [...bySite.entries()].sort((a, b) => (a[0] < b[0] ? -1 : 1)).map(([site_id, program_rows]) => ({
    site_id,
    program_rows,
    row: program_rows.find((r) => txt(r.sector) === SECTOR_DISTRICT) ?? program_rows[0],
  }));

  const district: Site[] = [];
  for (const d of distinct) {
    if (txt(d.row.sector) !== SECTOR_DISTRICT) continue;
    const capacity = num(d.row.capacity);
    const ewa = num(d.row.enroll_with_allotment);
    if (capacity === null || capacity <= 0 || ewa === null) continue;
    district.push({ ...d, capacity, enroll_with_allotment: ewa, utilization: (ewa / capacity) * 100 });
  }
  // Sites whose only programmes are Pre-K: the capacity SDP gives them is capacity
  // for Pre-K space. Quoted in the cross-city note, never in a distribution.
  const prekOnlyCapacities = distinct
    .filter((d) => d.program_rows.every((r) => txt(r.sector) === SECTOR_PREK))
    .map((d) => num(d.row.capacity))
    .filter((c): c is number => c !== null && c > 0)
    .sort((a, b) => a - b);
  return { distinct_site_count: distinct.length, district, prekOnlyCapacities };
}

// ── build the packet ─────────────────────────────────────────────────────────
function buildPacket() {
  const siteRows = readCsv<SiteRow>(SITES_CSV);
  const buildingRows = readCsv<BuildingRow>(BUILDINGS_CSV);
  const snapshot = latestSourceDir();
  const provPath = path.join(SOURCE_DIR, snapshot, "provenance.json");
  const prov = JSON.parse(fs.readFileSync(provPath, "utf8")) as Provenance;

  const { distinct_site_count, district, prekOnlyCapacities } = buildSites(siteRows);
  const util = district.map((s) => s.utilization);

  // 1. Distribution, district sites, SY2024-25
  const overall = distributionOf(util, sum(district.map((s) => s.enroll_with_allotment)), sum(district.map((s) => s.capacity)));

  const byLevelMap = new Map<string, Site[]>();
  for (const s of district) {
    const lvl = txt(s.row.school_level) || "(unstated)";
    const bucket = byLevelMap.get(lvl);
    if (bucket) bucket.push(s); else byLevelMap.set(lvl, [s]);
  }
  const by_school_level = [...byLevelMap.entries()]
    .sort((a, b) => b[1].length - a[1].length || (a[0] < b[0] ? -1 : 1))
    .map(([school_level, set]) => ({
      school_level,
      ...distributionOf(set.map((s) => s.utilization), sum(set.map((s) => s.enroll_with_allotment)), sum(set.map((s) => s.capacity))),
    }));

  // 2. Co-location — the direct analogue of NYC's multi-organization buildings
  const colocated = district.filter((s) => s.program_rows.length > 1);
  const co_location = {
    n_sites: district.length,
    sites_hosting_more_than_one_program: colocated.length,
    share: r1((100 * colocated.length) / district.length),
    max_programs_on_one_site: Math.max(...district.map((s) => s.program_rows.length)),
    note: "A site hosting a district school plus its Pre-K program, or plus a co-located charter, publishes one set of capacity/enrollment figures across several program rows. The distribution above counts each site once.",
  };

  // 3. Does low utilization track the district's own closure recommendation?
  const isClose = (s: Site) => txt(s.row.program_change).startsWith(CLOSE_PREFIX);
  const closeN = district.filter(isClose).length;
  const cuts = LIFT_CUTS.map((cut) => ({
    cut,
    ...liftOf(district, (s) => s.utilization < cut, isClose),
    p_value: FISHER_P[cut],
  }));
  // Size, on the same distinct sites. The probe's (school, site) rows are 219 over
  // these 215 sites, because four sites hosting two district schools enter once
  // per school; counting rows would weight those four sites twice.
  const sizeLift = liftOf(district, (s) => s.enroll_with_allotment < SIZE_CUT, isClose);
  const size = {
    predictor: `enrollment including allotted Pre-K seats (the utilization numerator) under ${SIZE_CUT}`,
    ...sizeLift,
    p_value: carriedP("size", sizeLift),
  };
  // Ablation: does utilization still separate within a size band?
  const small = district.filter((s) => s.enroll_with_allotment < ABLATION_SPLIT);
  const large = district.filter((s) => s.enroll_with_allotment >= ABLATION_SPLIT);
  const smallLift = liftOf(small, (s) => s.utilization < 50, isClose);
  const largeLift = liftOf(large, (s) => s.utilization < 66, isClose);
  const ablation = [
    { stratum: `enrollment including allotted Pre-K seats under ${ABLATION_SPLIT}`, n: small.length, close_n: small.filter(isClose).length, cut: 50, ...smallLift, p_value: carriedP("ablation_under_400", smallLift) },
    { stratum: `enrollment including allotted Pre-K seats of ${ABLATION_SPLIT} or more`, n: large.length, close_n: large.filter(isClose).length, cut: 66, ...largeLift, p_value: carriedP("ablation_400_plus", largeLift) },
  ];
  const decision_lift = {
    universe: "distinct district sites carrying a capacity, an enrollment and a published Program Change",
    n: district.length,
    close_n: closeN,
    base_rate: r1((100 * closeN) / district.length),
    close_definition: 'Program Change beginning with "Close" — the district\'s own vocabulary: Close, Close + Repurpose, Close, Modernize, + Repurpose.',
    cuts,
    size,
    ablation,
    p_value_note: `Fisher two-sided p-values are carried from docs/cities/philly/06_enrollment_capacity_utilization.md §4.3 (and §4.1 for the split-campus sensitivity), where they were measured with scipy.stats.fisher_exact. They are not recomputed here. The counts behind the utilization-cut p-values are in Table 2. For the size comparison, the comparisons within enrollment including allotted Pre-K seats under ${ABLATION_SPLIT} and ${ABLATION_SPLIT} or more, and the split-campus sensitivity, the script that builds these figures stops if the counts move from the ones the p-values were measured on. A null lift means no site on the other side of the cut carries a Close recommendation, so the ratio is unbounded.`,
    interpretation: `Both the predictor and the reference come from the same publication, so this measures how closely the district's recommendation tracks its own stated criterion, not whether an outside observer could have predicted the list. Size is the stronger single predictor: on the same ${district.length} sites, enrollment including allotted Pre-K seats under ${SIZE_CUT} carries a ${String(size.lift)}x lift (${String(size.flagged_close_rate)}% of ${size.flagged_n} sites against ${String(size.rest_close_rate)}% of ${size.rest_n}, ${pText(size.p_value)}; doc §4.3).`,
  };

  // 3b. Split campuses (doc §4.1). A district school with capacity on two sites of
  // its own appears above as two sites, each dividing the WHOLE school's enrollment
  // by that one site's capacity: the dashboard repeats a school's enrollment on
  // every one of its sites (doc §3.2). The packet keeps SDP's published per-site
  // figures; this measures what one unit per such school (enrollment once, Pre-K
  // seats and capacity summed — the loader's school roll-up) would change.
  const sitesOfSchool = new Map<string, Map<string, Site>>();
  for (const s of district) {
    for (const r of s.program_rows) {
      const u = txt(r.ulcs_code);
      if (!u || txt(r.sector) !== SECTOR_DISTRICT) continue;
      const bucket = sitesOfSchool.get(u) ?? new Map<string, Site>();
      bucket.set(s.site_id, s);
      sitesOfSchool.set(u, bucket);
    }
  }
  const splitSchools = [...sitesOfSchool.entries()]
    .filter(([, m]) => m.size > 1)
    .sort((a, b) => (a[0] < b[0] ? -1 : 1))
    .map(([ulcs, m]) => ({ ulcs, sites: [...m.values()] }));
  type Unit = { utilization: number; enroll_with_allotment: number; capacity: number; close: boolean };
  const splitSiteIds = new Set(splitSchools.flatMap((sc) => sc.sites.map((s) => s.site_id)));
  const units: Unit[] = district
    .filter((s) => !splitSiteIds.has(s.site_id))
    .map((s) => ({ utilization: s.utilization, enroll_with_allotment: s.enroll_with_allotment, capacity: s.capacity, close: isClose(s) }));
  let countedMoreThanOnce = 0;
  for (const { ulcs, sites } of splitSchools) {
    // Merging is only well defined when each site's figures are this school's alone
    // (its other programmes are Pre-K, which carry no K-12 enrollment), the sites
    // agree on the school's enrollment, and they carry one recommendation.
    const foreign = sites.flatMap((s) => s.program_rows.filter((r) => txt(r.ulcs_code) !== ulcs && txt(r.sector) !== SECTOR_PREK).map((r) => `${s.site_id}/${txt(r.ulcs_code)}`));
    const enrollments = new Set(sites.map((s) => num(s.row.enrollment)));
    const enrollment = enrollments.size === 1 ? [...enrollments][0] : null;
    const changes = new Set(sites.map((s) => txt(s.row.program_change)));
    if (foreign.length || enrollment === null || changes.size !== 1) {
      throw new Error(`split campus ${ulcs} (${sites.map((s) => s.site_id).join(", ")}) cannot be merged into one unit: other programmes ${foreign.join(", ") || "none"}, enrollments ${[...enrollments].join("/")}, ${changes.size} distinct Program Changes`);
    }
    const enrollWithAllotment = enrollment + sum(sites.map((s) => num(s.row.prek_allotment) ?? 0));
    const capacity = sum(sites.map((s) => s.capacity));
    countedMoreThanOnce += sum(sites.map((s) => s.enroll_with_allotment)) - enrollWithAllotment;
    units.push({ utilization: (enrollWithAllotment / capacity) * 100, enroll_with_allotment: enrollWithAllotment, capacity, close: isClose(sites[0]) });
  }
  const merged = distributionOf(units.map((u) => u.utilization), sum(units.map((u) => u.enroll_with_allotment)), sum(units.map((u) => u.capacity)));
  const mergedLiftCounts = liftOf(units, (u) => u.utilization < MERGED_LIFT_CUT, (u) => u.close);
  const mergedLift = { cut: MERGED_LIFT_CUT, ...mergedLiftCounts, p_value: carriedP("split_campus_merged", mergedLiftCounts) };
  const publishedLift = cuts.find((c) => c.cut === MERGED_LIFT_CUT)!;
  const splitSites = splitSchools.flatMap((sc) => sc.sites);
  const splitClose = splitSites.filter(isClose).length;
  const splitChanges = [...new Set(splitSites.map((s) => txt(s.row.program_change)))].sort();
  const split_campus_sensitivity = {
    schools: splitSchools.map((sc) => ({ ulcs_code: sc.ulcs, sites: sc.sites.map((s) => s.site_id) })),
    students_counted_more_than_once: countedMoreThanOnce,
    merged,
    merged_lift: mergedLift,
    note: `${cap1(word(splitSchools.length))} district schools have classroom capacity on more than one site of their own: ${splitSchools.map((sc) => `${sc.ulcs} (${sc.sites.map((s) => s.site_id).join(", ")})`).join(", ")}. The dashboard repeats a school's whole enrollment on each of its sites, so each of those ${word(splitSites.length)} published site figures divides the whole school by one site's capacity, and the distribution above, which keeps SDP's published per-site basis, counts ${countedMoreThanOnce.toLocaleString("en-US")} students more than once. Merged into one unit per school — enrollment once, Pre-K seats and capacity summed — the ${merged.n} units read a median of ${f1(merged.median)}%, ${f1(merged.share_under_50)}% under 50%, ${f1(merged.share_under_33)}% under 33%, ${f1(merged.share_over_100)}% over 100% and ${f1(merged.sum_over_sum)}% overall, against ${f1(overall.median)}%, ${f1(overall.share_under_50)}%, ${f1(overall.share_under_33)}%, ${f1(overall.share_over_100)}% and ${f1(overall.sum_over_sum)}% on the ${overall.n} sites as published. The Close lift under ${MERGED_LIFT_CUT}% reads ${String(mergedLift.lift)}x on ${mergedLift.flagged_n} units (${pText(mergedLift.p_value)}) against ${String(publishedLift.lift)}x on ${publishedLift.flagged_n} sites. ${splitClose ? `${cap1(word(splitClose))} of the ${word(splitSites.length)} sites carry a Close recommendation.` : `None of the ${word(splitSites.length)} sites carries a Close recommendation (${splitChanges.join(", ")}), so no Close count changes.`}`,
  };

  // 4. Two capacity denominators (doc §6.5): Site Capacity against the dashboard's
  // older, 2017-labelled per-building capacity. Kept as data for the doc; of this
  // block the page renders only building_extract.distinct_buildings.
  // The building extract repeats a building once per programme on its site,
  // identical on every other column, so the per-site sum counts each building
  // once. Refuse an extract whose repeats have started to differ: then no single
  // row speaks for the building, and the first-row choice would drop data.
  const conflicts = buildingRepeatConflicts(buildingRows);
  if (conflicts.length) {
    throw new Error(`building rows repeat with differing figures, so a per-site sum is ambiguous: ${conflicts.slice(0, 10).join(", ")}`);
  }
  const bldCap = buildingCapacityBySite(buildingRows);
  const matched = district.filter((s) => (bldCap.get(s.site_id) ?? 0) > 0);
  const ratioOf = (s: Site) => bldCap.get(s.site_id)! / s.capacity;
  const ratios = matched.map(ratioOf).sort((a, b) => a - b);
  const utilSite = matched.map((s) => s.utilization);
  const utilBld = matched.map((s) => (s.enroll_with_allotment / bldCap.get(s.site_id)!) * 100);
  const nSite50 = utilSite.filter((x) => x < 50).length;
  const nBld50 = utilBld.filter((x) => x < 50).length;
  const matchedEnroll = sum(matched.map((s) => s.enroll_with_allotment));
  const siteSeats = sum(matched.map((s) => s.capacity));
  const bldSeats = sum(matched.map((s) => bldCap.get(s.site_id)!));

  // What summing the extract's ROWS would give: a building counted once per
  // programme on its site. Reported as the number of sites it would change, never
  // used for a figure.
  const rowSum = new Map<string, number>();
  for (const b of buildingRows) {
    const id = txt(b.site_id);
    const c = num(b.building_capacity);
    if (!id || c === null || c <= 0) continue;
    rowSum.set(id, (rowSum.get(id) ?? 0) + c);
  }
  const buildingIds = buildingRows.map((b) => txt(b.building_id)).filter(Boolean);
  const idCounts = new Map<string, number>();
  for (const id of buildingIds) idCounts.set(id, (idCounts.get(id) ?? 0) + 1);
  const buildingsPerSite = new Map<string, Set<string>>();
  for (const b of buildingRows) {
    const id = txt(b.site_id), bid = txt(b.building_id);
    if (!id || !bid) continue;
    const set = buildingsPerSite.get(id);
    if (set) set.add(bid); else buildingsPerSite.set(id, new Set([bid]));
  }
  const buildingsOnASite = new Set([...buildingsPerSite.values()].flatMap((set) => [...set])).size;

  const ratioByLevel = new Map<string, number[]>();
  for (const s of matched) {
    const lvl = txt(s.row.school_level) || "(unstated)";
    const bucket = ratioByLevel.get(lvl);
    if (bucket) bucket.push(ratioOf(s)); else ratioByLevel.set(lvl, [ratioOf(s)]);
  }

  const nycPacket = JSON.parse(fs.readFileSync(NYC_PACKET, "utf8")) as NycPacket;
  const nycCap = nycPacket.capacity_definitions;
  const nyc_reference = {
    year: nycCap.year,
    n: nycCap.share_under_50_n,
    share_under_50_target: nycCap.share_under_50_target,
    share_under_50_historical: nycCap.share_under_50_historical,
    swing_pp: r1(Math.abs(nycCap.share_under_50_historical - nycCap.share_under_50_target)),
    source: "data/analysis/nyc-enrollment-capacity.json → capacity_definitions",
  };

  const site_capacity = {
    basis: "Site Capacity — the figure the district publishes and plans on",
    median_utilization: r1(medianOf([...utilSite].sort((a, b) => a - b))),
    share_under_50: share(utilSite, (x) => x < 50),
    n_under_50: nSite50,
    sum_over_sum: r1((100 * matchedEnroll) / siteSeats),
  };
  const summed_building_capacity = {
    basis: "BLDGCAPACITY summed over the distinct buildings on the site (the dashboard's \"Current Capacity (OLD)\")",
    median_utilization: r1(medianOf([...utilBld].sort((a, b) => a - b))),
    share_under_50: share(utilBld, (x) => x < 50),
    n_under_50: nBld50,
    sum_over_sum: r1((100 * matchedEnroll) / bldSeats),
  };
  const ratio_building_over_site = {
    p10: r3(quantileExclusive(ratios, 0.1)),
    p25: r3(quantileExclusive(ratios, 0.25)),
    median: r3(medianOf(ratios)),
    p75: r3(quantileExclusive(ratios, 0.75)),
    p90: r3(quantileExclusive(ratios, 0.9)),
    share_over_1_2: share(ratios, (x) => x > 1.2),
    share_under_0_9: share(ratios, (x) => x < 0.9),
  };
  // The difference of the two shares as printed, so the page's "X against Y, a
  // difference of Z" always adds up — the same way nyc_reference.swing_pp is taken.
  const under_50_swing_pp = r1((summed_building_capacity.share_under_50 ?? NaN) - (site_capacity.share_under_50 ?? NaN));
  const denominator_sensitivity = {
    n_matched_sites: matched.length,
    site_capacity,
    summed_building_capacity,
    under_50_swing_pp,
    ratio_building_over_site,
    ratio_by_school_level: [...ratioByLevel.entries()]
      .sort((a, b) => b[1].length - a[1].length || (a[0] < b[0] ? -1 : 1))
      .map(([school_level, rs]) => ({ school_level, n: rs.length, median_ratio: r3(medianOf([...rs].sort((a, b) => a - b))) })),
    full_universe_site_capacity_reference: {
      n: overall.n,
      median_utilization: overall.median,
      share_under_50: overall.share_under_50,
      note: `The two bases are compared on the ${matched.length} sites carrying both. The headline distribution covers ${overall.n} sites, ${overall.n - matched.length} of which carry no BLDGCAPACITY; on that wider set the site-capacity basis reads ${String(overall.median)}% median and ${String(overall.share_under_50)}% under 50%.`,
    },
    building_extract: {
      rows: buildingRows.length,
      distinct_buildings: idCounts.size,
      repeated_buildings: [...idCounts.values()].filter((n) => n > 1).length,
      max_rows_per_building: Math.max(...idCounts.values()),
      buildings_on_a_site: buildingsOnASite,
      buildings_with_no_site: idCounts.size - buildingsOnASite,
      sites_with_a_building: buildingsPerSite.size,
      sites_with_more_than_one_building: [...buildingsPerSite.values()].filter((set) => set.size > 1).length,
      sites_with_building_capacity: bldCap.size,
      sites_a_row_sum_inflates: [...rowSum.entries()].filter(([id, v]) => v !== bldCap.get(id)).length,
      note: "The extract carries one row per (building, programme): a building on a site hosting several programmes repeats, identical on every column but ulcs_code. Summing rows would count such a building once per programme. Every figure above counts each building once; sites_a_row_sum_inflates is the number of sites whose sum a row sum would change.",
    },
    nyc_reference,
    note: `On the ${matched.length} district sites carrying both figures, BLDGCAPACITY summed over each site's distinct buildings reads a median ${String(summed_building_capacity.median_utilization)}% and ${String(summed_building_capacity.share_under_50)}% of sites under 50% (${nBld50}), against ${String(site_capacity.median_utilization)}% and ${String(site_capacity.share_under_50)}% (${nSite50}) on Site Capacity: the choice of denominator moves the under-50% share by ${String(under_50_swing_pp)} points. In aggregate the two bases are close — enrollment over summed capacity is ${String(summed_building_capacity.sum_over_sum)}% on the building basis and ${String(site_capacity.sum_over_sum)}% on the site basis — but site by site they are not: building over site capacity runs from ${String(ratio_building_over_site.p10)} at p10 to ${String(ratio_building_over_site.p90)} at p90. The dashboard names BLDGCAPACITY "Current Capacity (OLD)", labels its OLD figures 2017, and divides a SY2016-17 enrollment count by it; SDP documents nothing more about how either capacity figure was measured. Dividing ${VINTAGE} enrollment by it, as here, is a pairing the dashboard never makes, so part of the site-by-site difference may be capacity that changed after 2017 rather than a difference of definition. Treat it as an alternative denominator that shows the sensitivity, not a rival estimate. NYC's two-formula ambiguity (target vs historical capacity), measured within one year (${nyc_reference.year}, ${nyc_reference.n.toLocaleString("en-US")} schools), moves its under-50% share by ${String(nyc_reference.swing_pp)} points. That is a comparison of sensitivities, not of levels: the two cities count Pre-K on different bases (Philadelphia adds allotted seats; NYC counts enrolled Pre-K children against a target capacity that includes Pre-K rooms), and the two are readings of different school years (Philadelphia ${VINTAGE}, NYC ${nyc_reference.year}).`,
  };

  // 5. What the district recommended
  const mixMap = new Map<string, number>();
  for (const s of district) {
    const k = txt(s.row.program_change) || "(none)";
    mixMap.set(k, (mixMap.get(k) ?? 0) + 1);
  }
  const recommendation_mix = [...mixMap.entries()]
    .sort((a, b) => b[1] - a[1] || (a[0] < b[0] ? -1 : 1))
    .map(([program_change, n]) => ({
      program_change,
      n,
      share: r1((100 * n) / district.length),
      is_close: program_change.startsWith(CLOSE_PREFIX),
    }));

  // 6. Internal check: the published Site Utilization against the recomputed one
  let maxAbsDiff = 0;
  let checked = 0;
  for (const s of district) {
    const published = num(s.row.utilization);
    if (published === null) continue;
    checked += 1;
    maxAbsDiff = Math.max(maxAbsDiff, Math.abs(published * 100 - s.utilization));
  }

  // 7. The three cities on one school year. Each figure is read from its own
  // committed packet; nothing is restated here. Miami-Dade's packet carries counts,
  // so its shares are count ÷ n. The cuts differ slightly as published and are
  // recorded per row.
  const nycSchool = nycPacket.by_year[VINTAGE]?.school;
  if (!nycSchool) {
    throw new Error(`${NYC_PACKET} has no by_year["${VINTAGE}"].school block; the cross-city comparison needs NYC on Philadelphia's school year`);
  }
  const miami = (JSON.parse(fs.readFileSync(MIAMI_PACKET, "utf8")) as { utilization?: MiamiUtilization }).utilization;
  if (!miami || miami.year !== VINTAGE) {
    throw new Error(`${MIAMI_PACKET} utilization block is for ${miami?.year ?? "no year"}, not ${VINTAGE}; the cross-city comparison needs Miami-Dade on Philadelphia's school year`);
  }
  const miamiShare = (k: number) => r1((100 * k) / miami.n);
  const cross_city = {
    year: VINTAGE,
    rows: [
      {
        city: "Philadelphia",
        grain: "site",
        universe: "Distinct District-sector sites carrying a capacity and an enrollment",
        n: overall.n,
        median: overall.median,
        share_under_50: overall.share_under_50,
        share_under_33: overall.share_under_33,
        share_over_100: overall.share_over_100,
        sum_over_sum: overall.sum_over_sum,
        cuts: { under_33: "<33", over_100: ">100" },
        numerator: "K-12 October 1 enrollment plus the Pre-K seats allotted to the site",
        denominator: "SDP site capacity",
        source: "data/analysis/philly-enrollment-capacity.json → distribution.district_sites",
      },
      {
        city: "Miami-Dade",
        grain: "school",
        universe: "District-run regular schools (K-12 general education, not charter) carrying an October headcount and a permanent FISH capacity, K-8, 6-12 and K-12 schools included",
        n: miami.n,
        median: miami.headcount.p50,
        share_under_50: miamiShare(miami.thresholds_headcount.below_50),
        share_under_33: miamiShare(miami.thresholds_headcount.below_33),
        share_over_100: miamiShare(miami.thresholds_headcount.at_or_above_100),
        sum_over_sum: miami.district_sum_headcount_pct,
        cuts: { under_33: "<33.33", over_100: ">=100" },
        numerator: "FLDOE Survey 2 (October) headcount, PK-12: enrolled Pre-K children included",
        denominator: "Permanent FISH capacity",
        source: "data/analysis/miami-enrollment.json → utilization",
      },
      {
        city: "NYC",
        grain: "school",
        universe: "Schools whose org code resolves to exactly one DBN, in DOE buildings, co-located charters (about 110) and District 75 (about 60) included",
        n: nycSchool.n,
        median: nycSchool.median,
        share_under_50: nycSchool.share_under_50,
        share_under_33: nycSchool.share_under_33,
        share_over_100: nycSchool.share_over_100,
        sum_over_sum: nycSchool.sum_over_sum,
        cuts: { under_33: "<33.3 (integer percents)", over_100: ">100" },
        numerator: "Blue Book enrollment, the audited register: enrolled 3-K and Pre-K children included",
        denominator: "SCA target capacity, Pre-K rooms included",
        source: `data/analysis/nyc-enrollment-capacity.json → by_year["${VINTAGE}"].school`,
      },
    ],
  };
  const [cp, cm, cn] = cross_city.rows;
  const cross_city_note = `All three on SY${VINTAGE}. Philadelphia: median ${String(cp.median)}%, ${String(cp.share_under_50)}% under 50%, ${String(cp.share_under_33)}% under a third, ${String(cp.sum_over_sum)}% overall (${cp.n} district sites). Miami-Dade: median ${String(cm.median)}%, ${String(cm.share_under_50)}%, ${String(cm.share_under_33)}%, ${String(cm.sum_over_sum)}% (${cm.n} district-run regular schools). NYC: median ${String(cn.median)}%, ${String(cn.share_under_50)}%, ${String(cn.share_under_33)}%, ${String(cn.sum_over_sum)}% (${cn.n.toLocaleString("en-US")} schools in DOE buildings). The denominators differ — SDP site capacity, permanent FISH (Florida Inventory of School Houses) capacity, SCA (NYC School Construction Authority) target capacity — and so do the universes and the Pre-K basis: Philadelphia adds allotted Pre-K seats to K-12 enrollment, Miami-Dade and NYC count enrolled Pre-K children (NYC also 3-K), and NYC's target capacity includes Pre-K rooms.${prekOnlyCapacities.length ? ` SDP assigns site capacity to Pre-K space as well: its ${word(prekOnlyCapacities.length)} Pre-K-only sites carry site capacities of ${prekOnlyCapacities[0]} to ${prekOnlyCapacities[prekOnlyCapacities.length - 1]}.` : ""} Whether Miami-Dade's FISH capacity counts Pre-K stations is not established. Name the numerator, the denominator and the year whenever the three appear together.`;

  return {
    meta: {
      generated_at: prov.retrieved_at ?? null,
      generated_at_note: "The extract's retrieval timestamp from provenance.json, not the time this script ran — the packet is a pure function of the committed CSVs, the snapshot's provenance.json and the committed NYC and Miami-Dade packets, so it reproduces byte-for-byte.",
      source: {
        source_id: prov.source_id ?? "sdp_facilities_dashboard",
        publisher: prov.publisher ?? null,
        url: prov.url ?? null,
        format: prov.format ?? null,
        qlik_app_id: prov.qlik_app_id ?? null,
        snapshot: snapshot,
        sha256: prov.sha256 ?? null,
        extract: [
          "data/cities/philly/reference/parsed/sdp_facilities_sites.csv",
          "data/cities/philly/reference/parsed/sdp_facilities_buildings.csv",
        ],
        rows_sites: prov.rows_sites ?? siteRows.length,
        rows_buildings: prov.rows_buildings ?? buildingRows.length,
      },
      vintage: VINTAGE,
      single_vintage: true,
      single_vintage_reason:
        "The School District of Philadelphia (SDP) publishes one facilities vintage, SY2024-25. The app's School Year field is a cross-join onto the student table, so a site's capacity appears under both labels with identical figures (335 of the 342 (ULCS, site) pairs carrying a capacity appear under both labels; 0 differ) — no trend and no enrollment-vs-capacity decomposition of the NYC kind is possible from this source.",
      grain: "distinct site (site_id), deduplicated across program rows, District sector only",
      utilization_definition:
        prov.utilization_definition ??
        "Site Utilization = (Site Enrollment + Site Pre-K Allotment) / Site Capacity, October 1 snapshot",
      prek_note:
        "The dashboard adds the Pre-K seats allotted to a site's early-childhood programmes to its K-12 October 1 enrollment ('students enrolled (and allotted seats for PreK)'). NYC's Blue Book enrollment and Miami-Dade's October (FLDOE Survey 2) headcount, PK-12, count enrolled Pre-K children instead (NYC also 3-K), and NYC's target capacity includes Pre-K rooms, so the three are not on one Pre-K basis.",
      quantile_method:
        "exclusive (Weibull / R type-6), the default of Python's statistics.quantiles used by docs/cities/philly/probe/facilities.py. p10/p25/p75/p90 are rounded to integers and the median to one decimal, as published in doc §4.1.",
      direction: "NEUTRAL — utilization is reported, not ranked. Nothing here scores a school.",
      universe_note: `The universe narrows in two steps: ${siteRows.length} site rows resolve to ${distinct_site_count} distinct sites, because a site hosting several programs publishes one set of capacity and enrollment figures across several rows; ${district.length} of those ${distinct_site_count} sites are District-sector sites carrying both a capacity and an enrollment, and those ${district.length} are the universe of every figure here. The ${distinct_site_count - district.length} distinct sites not carried forward are Charter, Alternative or Pre-K sector, or carry no capacity or no enrollment. The 342 in the note that SDP publishes one facilities vintage is a different count at a different grain — distinct (ULCS code, site) pairs carrying a capacity anywhere in the app, across every sector and both School Year labels (doc §2) — and is quoted there only to show that the two labels carry identical capacity figures. Charter coverage in this extract is thin, so any district-wide figure is a district-sites figure.`,
      cross_city_note,
      checks: {
        published_vs_recomputed_utilization: {
          n: checked,
          max_abs_difference_pp: Number(maxAbsDiff.toPrecision(3)),
          note: "The published Site Utilization equals (K-12 enrollment + allotted Pre-K seats) ÷ site capacity to floating-point tolerance, confirming the allotment sits inside the numerator (doc §6.1).",
        },
        share_under_33_threshold: {
          note: "The under-33% share counts sites strictly under 33%. The method doc's measurement script uses < 33.3; no site falls between the two, so both give the same count.",
        },
      },
    },
    distribution: { district_sites: overall, by_school_level, split_campus_sensitivity },
    co_location,
    decision_lift,
    denominator_sensitivity,
    recommendation_mix,
    cross_city,
  };
}

// ── main ─────────────────────────────────────────────────────────────────────
function main() {
  const check = process.argv.slice(2).includes("--check");
  const packet = buildPacket();
  const text = JSON.stringify(packet, null, 1) + "\n";

  if (check) {
    if (!fs.existsSync(OUT)) {
      console.error(`[check] MISSING ${OUT} — run: npx tsx scripts/analysis/philly-capacity-packet.ts`);
      process.exit(1);
    }
    const onDisk = fs.readFileSync(OUT, "utf8");
    if (onDisk !== text) {
      const a = onDisk.split("\n"), b = text.split("\n");
      const shown: string[] = [];
      for (let i = 0; i < Math.max(a.length, b.length) && shown.length < 20; i++) {
        if (a[i] !== b[i]) shown.push(`  line ${i + 1}\n    committed: ${a[i] ?? "<eof>"}\n    regenerated: ${b[i] ?? "<eof>"}`);
      }
      console.error(`[check] DRIFT — ${OUT} does not match a fresh run:\n${shown.join("\n")}`);
      process.exit(1);
    }
    console.log(`[check] OK — ${OUT} matches a fresh run (${packet.distribution.district_sites.n} district sites).`);
    return;
  }

  fs.mkdirSync(path.dirname(OUT), { recursive: true });
  fs.writeFileSync(OUT, text);
  const d = packet.distribution.district_sites;
  console.log(`[packet] wrote ${OUT}`);
  console.log(`  vintage ${packet.meta.vintage} (single vintage), generated_at ${String(packet.meta.generated_at)}`);
  console.log(`  district sites n=${d.n} p10 ${String(d.p10)} p25 ${String(d.p25)} median ${String(d.median)} p75 ${String(d.p75)} p90 ${String(d.p90)} <50% ${String(d.share_under_50)}% <33% ${String(d.share_under_33)}% >100% ${String(d.share_over_100)}% sum/sum ${String(d.sum_over_sum)}%`);
  for (const l of packet.distribution.by_school_level) console.log(`    ${l.school_level.padEnd(24)} n=${String(l.n).padStart(3)} median ${String(l.median)} <50% ${String(l.share_under_50)}%`);
  console.log(`  co-location ${packet.co_location.sites_hosting_more_than_one_program}/${packet.co_location.n_sites} (${packet.co_location.share}%)`);
  console.log(`  close base rate ${packet.decision_lift.base_rate}% (${packet.decision_lift.close_n}/${packet.decision_lift.n})`);
  for (const c of packet.decision_lift.cuts) console.log(`    util<${c.cut}%: n=${String(c.flagged_n).padStart(3)} close ${String(c.flagged_close_rate)}% rest ${String(c.rest_close_rate)}% lift ${String(c.lift)}x p=${c.p_value}`);
  const sz = packet.decision_lift.size;
  console.log(`    enrollment<${SIZE_CUT}: n=${String(sz.flagged_n).padStart(3)} close ${String(sz.flagged_close_rate)}% rest ${String(sz.rest_close_rate)}% lift ${String(sz.lift)}x p=${sz.p_value}`);
  for (const a of packet.decision_lift.ablation) console.log(`    within ${a.stratum} (n=${a.n}): util<${a.cut}%: n=${String(a.flagged_n).padStart(3)} close ${String(a.flagged_close_rate)}% rest ${String(a.rest_close_rate)}% lift ${a.lift === null ? "unbounded" : `${a.lift}x`} p=${a.p_value}`);
  const sc = packet.distribution.split_campus_sensitivity;
  console.log(`  split campuses ${sc.schools.map((s) => s.ulcs_code).join(", ")}: ${sc.students_counted_more_than_once} students counted more than once; merged n=${sc.merged.n} median ${String(sc.merged.median)} <50% ${String(sc.merged.share_under_50)}% >100% ${String(sc.merged.share_over_100)}% sum/sum ${String(sc.merged.sum_over_sum)}%; util<${sc.merged_lift.cut}% lift ${String(sc.merged_lift.lift)}x on ${sc.merged_lift.flagged_n}`);
  const ds = packet.denominator_sensitivity;
  console.log(`  denominator (n=${ds.n_matched_sites}): site capacity median ${String(ds.site_capacity.median_utilization)}% <50% ${String(ds.site_capacity.share_under_50)}% | summed building capacity median ${String(ds.summed_building_capacity.median_utilization)}% <50% ${String(ds.summed_building_capacity.share_under_50)}%`);
  console.log(`  ratio building/site capacity: median ${ds.ratio_building_over_site.median} (p10 ${ds.ratio_building_over_site.p10}, p90 ${ds.ratio_building_over_site.p90})`);
  console.log(`  under-50% swing ${ds.under_50_swing_pp} pts (NYC ${ds.nyc_reference.swing_pp}, ${ds.nyc_reference.year}); building extract ${ds.building_extract.rows} rows = ${ds.building_extract.distinct_buildings} buildings, a row sum would inflate ${ds.building_extract.sites_a_row_sum_inflates} of ${ds.building_extract.sites_with_building_capacity} sites`);
  console.log(`  utilization check: max |published - recomputed| = ${packet.meta.checks.published_vs_recomputed_utilization.max_abs_difference_pp}pp over ${packet.meta.checks.published_vs_recomputed_utilization.n} sites`);
  console.log(`  cross-city, SY${packet.cross_city.year}:`);
  for (const r of packet.cross_city.rows) console.log(`    ${r.city.padEnd(12)} n=${String(r.n).padStart(4)} median ${String(r.median)} <50% ${String(r.share_under_50)}% <1/3 ${String(r.share_under_33)}% over-100 ${String(r.share_over_100)}% (${r.cuts.over_100}) sum/sum ${String(r.sum_over_sum)}%`);
}

main();