// Pure parsing for the SDP Facilities Master Plan extract. No Prisma, no IO —
// `facilities.ts` does that. Split out so the grain rules can be unit-tested
// without a database (tests/philly-facilities-transform.test.ts).
//
// Three grain rules, each the opposite of a first reading. All three are
// measured in docs/cities/philly/06_enrollment_capacity_utilization.md §3.
//
//   1. SITE is the grain capacity is published at, and a site hosting a
//      district school plus its Pre-K programme (or a co-located charter)
//      repeats the SAME capacity, enrollment and utilization on every
//      programme's row. 91 of 328 sites. Counting rows double-weights them.
//
//   2. School ↔ site is MANY-TO-MANY. 32 schools sit on more than one site;
//      91 sites host more than one programme. A foreign key on either side
//      would drop rows, so the relation needs a join table.
//
//   3. BUILDING is a third grain — floor area, year built, condition, use type
//      and a second capacity figure. 47 of the 285 sites with a building hold
//      more than one, and they are not all schoolhouses: John Bartram's site
//      (S022) carries a Garage (16,000 sq ft) and its Main Classroom Building
//      (270,000); its fieldhouse sits on a separate site. Any floor-area
//      statistic has to sum the CLASSROOM-use buildings per site; reading one
//      building's area as the site's silently substitutes a garage for a
//      school. The extract also repeats a building once per programme on its
//      site, so every per-site sum below dedupes on building_id first.
//
// NOTE the numerator: Philadelphia's published utilization INCLUDES the PreK
// allotment — seats allotted to the site's early-childhood programmes, not enrolled
// children. NYC's Blue Book counts enrolled 3-K/Pre-K students. The two cities'
// figures are on different bases, and no common-basis figure is computed;
// wherever they appear together the numerator, the denominator and the Pre-K
// basis are named (doc §4.2).

export type SiteRow = Record<string, string>;
export type BuildingRow = Record<string, string>;

/**
 * Extract cells are strings; "" and "-" are both absent, and 0 is not.
 * Thousands separators and a currency sign are dropped: replacement_value
 * arrives as "$9,639,300". A cell of nothing but those is absent.
 */
export function int(v: string | undefined): number | null {
  if (v === undefined) return null;
  const s = v.trim();
  if (s === "" || s === "-") return null;
  const n = Number(s.replace(/[$,]/g, "") || NaN);
  return Number.isFinite(n) ? Math.round(n) : null;
}

export function float(v: string | undefined): number | null {
  if (v === undefined) return null;
  const s = v.trim();
  if (s === "" || s === "-") return null;
  const n = Number(s.replace(/[$,]/g, "") || NaN);
  return Number.isFinite(n) ? n : null;
}

/**
 * Collapse (site × programme) rows to one record per site — rule 1.
 *
 * Every programme row on a site carries identical figures, so any row would do
 * for capacity; the District-sector row is preferred because it also carries
 * the school level and the district's recommendation.
 */
export function dedupeSites(rows: readonly SiteRow[]): Map<string, SiteRow> {
  const bySite = new Map<string, SiteRow[]>();
  for (const r of rows) {
    const id = (r.site_id ?? "").trim();
    if (!id) continue;
    const bucket = bySite.get(id);
    if (bucket) bucket.push(r);
    else bySite.set(id, [r]);
  }
  const out = new Map<string, SiteRow>();
  for (const [id, group] of bySite) {
    out.set(id, group.find((r) => (r.sector ?? "").trim() === "District") ?? group[0]);
  }
  return out;
}

/**
 * Collapse the building cube to one record per building — the same hazard as
 * rule 1, one grain down.
 *
 * The building cube carries `ulcs_code`, so a building on a site hosting more
 * than one programme is emitted once PER PROGRAMME: 502 extract rows are 399
 * distinct buildings, and 94 building ids repeat (up to four times). The
 * repeats are identical on every field except `ulcs_code`, which no building
 * figure uses, so the first row of each group is a lossless choice.
 *
 * Counting rows instead of buildings overstates the stock by 26% and, worse,
 * summing `building_capacity` over rows double-counts the seats of any
 * building on a multi-programme site.
 */
export function dedupeBuildings(rows: readonly BuildingRow[]): Map<string, BuildingRow> {
  const out = new Map<string, BuildingRow>();
  for (const r of rows) {
    const id = (r.building_id ?? "").trim();
    if (!id || out.has(id)) continue;
    out.set(id, r);
  }
  return out;
}

/**
 * The building ids whose repeated rows differ on anything but `ulcs_code`.
 *
 * `dedupeBuildings` keeps the first row of each group, which is lossless only
 * while the repeats are identical. On the 2024-25 extract they are — 94
 * repeated ids, and only `ulcs_code` varies — so this returns []. A non-empty
 * result means the source changed shape and the first-row choice would now
 * drop information; callers throw rather than guess.
 */
export function buildingRepeatConflicts(
  rows: readonly BuildingRow[],
  ignore: readonly string[] = ["ulcs_code"],
): string[] {
  const byId = new Map<string, BuildingRow[]>();
  for (const r of rows) {
    const id = (r.building_id ?? "").trim();
    if (!id) continue;
    const bucket = byId.get(id);
    if (bucket) bucket.push(r);
    else byId.set(id, [r]);
  }
  const conflicts: string[] = [];
  for (const [id, group] of byId) {
    if (group.length < 2) continue;
    const keys = new Set(group.flatMap((r) => Object.keys(r)));
    for (const k of ignore) keys.delete(k);
    const first = group[0];
    if (group.some((r) => [...keys].some((k) => (r[k] ?? "").trim() !== (first[k] ?? "").trim()))) {
      conflicts.push(id);
    }
  }
  return conflicts.sort();
}

/** Every distinct (site, school) pair — rule 2. Rows with no ULCS are dropped. */
export function siteProgramLinks(rows: readonly SiteRow[]): Array<{ siteId: string; ulcsCode: string }> {
  const seen = new Set<string>();
  const out: Array<{ siteId: string; ulcsCode: string }> = [];
  for (const r of rows) {
    const siteId = (r.site_id ?? "").trim();
    const ulcsCode = (r.ulcs_code ?? "").trim();
    if (!siteId || !ulcsCode) continue;
    const key = `${siteId}\u0000${ulcsCode}`;
    if (seen.has(key)) continue;
    seen.add(key);
    out.push({ siteId, ulcsCode });
  }
  return out;
}

/**
 * Classroom-use floor area summed per site, each building counted once — rule 3.
 *
 * Dedupes on building_id itself, so raw extract rows and deduplicated records
 * give the same answer. Summing the raw rows inflates 83 of the 253 sites
 * carrying classroom area, because a building on a multi-programme site is
 * emitted once per programme.
 */
export function classroomAreaBySite(buildings: readonly BuildingRow[]): Map<string, number> {
  const out = new Map<string, number>();
  for (const b of dedupeBuildings(buildings).values()) {
    if (!(b.use_type ?? "").trim().startsWith("Classroom")) continue;
    const siteId = (b.site_id ?? "").trim();
    const area = int(b.gross_sqft);
    if (!siteId || area === null || area <= 0) continue;
    out.set(siteId, (out.get(siteId) ?? 0) + area);
  }
  return out;
}

/**
 * The alternate capacity basis: `BLDGCAPACITY` summed per site, each building
 * counted once.
 *
 * Dedupes on building_id itself, so raw extract rows and deduplicated records
 * give the same answer. Summing the raw rows would inflate 79 of the 236 sites
 * carrying a figure — 68 exactly doubled, 7 tripled, and 4 by a fraction where
 * only one of a site's two capacity-bearing buildings is linked to both of its
 * programmes. The deduplicated sum equals the dashboard's own
 * `Sum([BLDGCAPACITY])` per site on all 236, because the field lives in a table
 * keyed by building rather than in the per-programme Buildings table.
 *
 * Kept so the denominator sensitivity can be shown, never served as "the"
 * capacity. The app labels it "Current Capacity (OLD)" (2017) and documents
 * nothing more about how it was measured (doc §6.5).
 */
export function buildingCapacityBySite(buildings: readonly BuildingRow[]): Map<string, number> {
  const out = new Map<string, number>();
  for (const b of dedupeBuildings(buildings).values()) {
    const siteId = (b.site_id ?? "").trim();
    const cap = int(b.building_capacity);
    if (!siteId || cap === null || cap <= 0) continue;
    out.set(siteId, (out.get(siteId) ?? 0) + cap);
  }
  return out;
}

/**
 * Validation check (iv): the published utilization must equal
 * (enrollment + PreK allotment) / capacity. It does, exactly, for every site
 * with all three parts — so any failure means the source's definition changed
 * and the loader should refuse to load rather than serve a redefined metric.
 */
export function utilizationAgreement(sites: Iterable<SiteRow>): {
  checked: number;
  within: number;
  maxDiff: number;
} {
  let checked = 0;
  let within = 0;
  let maxDiff = 0;
  for (const r of sites) {
    const cap = int(r.capacity);
    const enr = int(r.enroll_with_allotment);
    const util = float(r.utilization);
    if (!cap || enr === null || util === null) continue;
    checked++;
    const diff = Math.abs(util - enr / cap);
    if (diff <= 0.005) within++;
    if (diff > maxDiff) maxDiff = diff;
  }
  return { checked, within, maxDiff };
}

/**
 * Validation check (v): on every site, the dashboard's Site Enroll w/ Allotment
 * must equal its enrollment plus its PreK allotment (a blank allotment counts
 * as 0). It does on all 285 sites carrying either figure. The school roll-up
 * below reads `enrollment` and `prek_allotment` separately and adds them, so it
 * leans on this identity; a failure means the extract changed shape.
 */
export function allotmentIdentity(sites: Iterable<SiteRow>): { checked: number; failing: string[] } {
  let checked = 0;
  const failing: string[] = [];
  for (const r of sites) {
    const enr = int(r.enrollment);
    const withAllotment = int(r.enroll_with_allotment);
    if (enr === null && withAllotment === null) continue;
    checked++;
    if (enr === null || withAllotment !== enr + (int(r.prek_allotment) ?? 0)) {
      failing.push((r.site_id ?? "").trim());
    }
  }
  return { checked, failing: failing.sort() };
}

/**
 * Validation check (vi): the school roll-up against the district's own figure.
 *
 * Check (iv) is per site, so it cannot see a roll-up error. Where a school's
 * capacity sits on exactly one of its linked sites, the dashboard's published
 * utilization for that site already divides the whole school by the whole
 * school's capacity, so the school-grain figure must reproduce it within 0.005.
 * On the 2024-25 extract that is 220 of the 223 directory schools with a
 * figure, and all 220 agree; a roll-up that summed enrollment across sites
 * would agree for 200, so the check tells the two rules apart. A missing
 * published figure on that site counts as a failure — it is a shape the
 * extract does not contain. The other three schools (2210, 7440, 8770) have
 * capacity on two sites, and each of those sites' published figure divides
 * the whole school by one campus, so there is nothing to compare.
 */
export function schoolSiteAgreement(
  sites: ReadonlyMap<string, SiteRow>,
  links: ReadonlyArray<{ siteId: string; ulcsCode: string }>,
  facts: ReadonlyArray<{ ulcsCode: string; utilizationPct: number }>,
): { checked: number; within: number; failing: string[] } {
  const capacitySites = new Map<string, string[]>();
  for (const { siteId, ulcsCode } of links) {
    if ((int(sites.get(siteId)?.capacity) ?? 0) <= 0) continue;
    if (!capacitySites.has(ulcsCode)) capacitySites.set(ulcsCode, []);
    capacitySites.get(ulcsCode)!.push(siteId);
  }
  let checked = 0;
  let within = 0;
  const failing: string[] = [];
  for (const f of facts) {
    const one = capacitySites.get(f.ulcsCode);
    if (!one || one.length !== 1) continue;
    checked++;
    const published = float(sites.get(one[0])?.utilization);
    if (published !== null && Math.abs(f.utilizationPct / 100 - published) <= 0.005) within++;
    else failing.push(f.ulcsCode);
  }
  return { checked, within, failing: failing.sort() };
}

/**
 * School-grain capacity, ONLY where attribution is unambiguous.
 *
 * A school whose site is linked to another school in `links` cannot have that
 * site's capacity attributed to it alone — the figure is the site's. Those
 * schools are returned in `shared` and get no school-grain fact; the site
 * record still carries the number. That test runs FIRST: a shared site's
 * enrollment is the total of every programme on it (Strawberry Mansion's S109
 * reads 542 = its own 307 plus two One Bright Ray programmes), so no roll-up
 * can be read off it.
 *
 * "Another school" means another code in `links`. The loader passes only links
 * to schools in the directory (the Master School List), so a site shared only
 * with programmes the directory does not carry is attributed to the school it
 * hosts. On the 2024-25 extract those programmes are all Pre-K: 71 of the 223
 * directory schools with a figure sit on a site shared only with Pre-K
 * programmes, and the Pre-K seats allotted there are in their numerator.
 *
 * A school on several sites of its own (23 of the 223 directory schools with a
 * figure in SY2024-25) is rolled up the way the dashboard's own per-school
 * measure does it (doc §3.2): capacity SUMMED over its sites; Pre-K allotment
 * SUMMED over its sites (on the 2024-25 extract no school carries an
 * allotment on more than one site, so the sum is that one site's); enrollment
 * counted ONCE — the dashboard repeats the whole school's enrollment on every
 * one of its site rows, so summing multiplies a school by its site count
 * (Walter Saul: 4 × 416 = 1,664). A school whose own sites disagree on
 * enrollment is returned in `conflicts`, and the loader then refuses the
 * whole load, writing nothing. A blank capacity adds
 * nothing and an explicit 0 counts; a school with no capacity, a total of 0,
 * or no enrollment gets no fact rather than a zero.
 */
export function schoolFacts(
  sites: ReadonlyMap<string, SiteRow>,
  links: ReadonlyArray<{ siteId: string; ulcsCode: string }>,
): {
  facts: Array<{ ulcsCode: string; capacity: number; enrollment: number; prek: number; utilizationPct: number }>;
  shared: string[];
  conflicts: string[];
} {
  const sitesPerSchool = new Map<string, string[]>();
  const schoolsPerSite = new Map<string, string[]>();
  for (const { siteId, ulcsCode } of links) {
    if (!sitesPerSchool.has(ulcsCode)) sitesPerSchool.set(ulcsCode, []);
    sitesPerSchool.get(ulcsCode)!.push(siteId);
    if (!schoolsPerSite.has(siteId)) schoolsPerSite.set(siteId, []);
    schoolsPerSite.get(siteId)!.push(ulcsCode);
  }

  const facts: Array<{ ulcsCode: string; capacity: number; enrollment: number; prek: number; utilizationPct: number }> = [];
  const shared: string[] = [];
  const conflicts: string[] = [];
  for (const [ulcsCode, siteIds] of sitesPerSchool) {
    if (siteIds.some((s) => (schoolsPerSite.get(s)?.length ?? 0) > 1)) {
      shared.push(ulcsCode);
      continue;
    }
    let capacity = 0;
    let prek = 0;
    let sawCapacity = false;
    const enrollments = new Set<number>();
    for (const s of siteIds) {
      const r = sites.get(s);
      if (!r) continue;
      const c = int(r.capacity);
      if (c !== null) { capacity += c; sawCapacity = true; }
      const e = int(r.enrollment);
      if (e !== null) enrollments.add(e);
      prek += int(r.prek_allotment) ?? 0;
    }
    if (enrollments.size > 1) {
      conflicts.push(ulcsCode);
      continue;
    }
    if (!sawCapacity || capacity === 0 || enrollments.size === 0) continue;
    const enrollment = [...enrollments][0] + prek;
    facts.push({ ulcsCode, capacity, enrollment, prek, utilizationPct: (100 * enrollment) / capacity });
  }
  return { facts, shared: shared.sort(), conflicts: conflicts.sort() };
}