scripts/loaders/miami/compute_percentiles.ts

// Compute comparison_group_percentile + citywide_percentile for every
// (school × year × metric × subgroup) row of an OUTCOME metric
// (domain PERFORMANCE or CLIMATE) in miami_school_year_metrics. Enrollment,
// capacity and spending metrics are direction-neutral and are not ranked.
// Same midrank algorithm and minimum-5-peers floor as the NYC and Philly
// scripts; direction-adjusted so a high percentile always means better.
// Run:  npx tsx scripts/loaders/miami/compute_percentiles.ts

import path from "node:path";
import { config as loadEnv } from "dotenv";
loadEnv({ path: path.join(process.cwd(), ".env.local") });
import pg from "pg";

const directUrl = (process.env.DIRECT_URL ?? process.env.POSTGRES_URL_NON_POOLING ?? process.env.DATABASE_URL ?? process.env.POSTGRES_URL)!;
function makePool() {
  const u = new URL(directUrl);
  const ssl = u.searchParams.get("sslmode") === "disable" ? false : ({ rejectUnauthorized: false } as const);
  return new pg.Pool({ user: decodeURIComponent(u.username), password: decodeURIComponent(u.password), host: u.hostname, port: parseInt(u.port || "5432", 10), database: u.pathname.slice(1), ssl, max: 2, statement_timeout: 60_000 });
}
const pool = makePool();
const query = async <T = unknown>(sql: string, params?: unknown[]) => (await pool.query(sql, params)).rows as T[];

function rank<T>(items: T[], valueOf: (t: T) => number): Map<T, number> {
  const sorted = items.map((t, i) => ({ t, v: valueOf(t), i })).sort((a, b) => a.v - b.v);
  const n = sorted.length, out = new Map<T, number>();
  let i = 0;
  while (i < n) { let j = i; while (j + 1 < n && sorted[j + 1].v === sorted[i].v) j++; const mid = (i + j) / 2 + 1; const pct = n === 1 ? 0.5 : (mid - 1) / (n - 1); for (let k = i; k <= j; k++) out.set(sorted[k].t, pct); i = j + 1; }
  return out;
}

async function main() {
  const schools = await query<{ school_number: string; notes: { peers: string[] } | null; include: boolean }>(`SELECT s.school_number, cg.methodology_notes::jsonb AS notes, s.include_in_default_comparisons AS include FROM miami_schools s LEFT JOIN miami_comparison_groups cg ON cg.id = s.comparison_group_id`);
  const peerMap = new Map<string, Set<string>>(), included = new Set<string>();
  for (const s of schools) { if (s.include) included.add(s.school_number); if (s.notes?.peers) peerMap.set(s.school_number, new Set([s.school_number, ...s.notes.peers])); }
  const combos = await query<{ metric_key: string; year: string; subgroup: string; direction: string }>(`SELECT DISTINCT m.metric_key, m.year, m.subgroup::text AS subgroup, d.direction::text AS direction FROM miami_school_year_metrics m JOIN miami_metric_definitions d ON d.key = m.metric_key WHERE d.domain IN ('PERFORMANCE','CLIMATE') AND d.direction <> 'NEUTRAL' AND m.value IS NOT NULL AND m.suppressed = false`);
  console.log(`[pct] peer groups ${peerMap.size}; default universe ${included.size}; combos ${combos.length}`);
  // Every percentile is recomputed from the current served cells, so the columns are cleared first: a cell that is
  // now suppressed, or whose metric/year no longer ranks, must not keep the percentile of an earlier run
  // (review 2026-09-16b: 144 suppressed 2018-19 survey cells still carried percentiles).
  const cleared = await query<{ n: number }>(`WITH u AS (UPDATE miami_school_year_metrics SET comparison_group_percentile = NULL, citywide_percentile = NULL WHERE comparison_group_percentile IS NOT NULL OR citywide_percentile IS NOT NULL RETURNING 1) SELECT count(*)::int AS n FROM u`);
  console.log(`[pct] cleared ${Number(cleared[0]?.n ?? 0).toLocaleString()} previously ranked rows`);
  let updated = 0;
  for (const c of combos) {
    const rows = await query<{ id: string; school_number: string; value: number }>(`SELECT id, school_number, value::float8 AS value FROM miami_school_year_metrics WHERE metric_key = $1 AND year = $2 AND subgroup::text = $3 AND value IS NOT NULL AND suppressed = false`, [c.metric_key, c.year, c.subgroup]);
    if (!rows.length) continue;
    const sign = c.direction === "LOWER_BETTER" ? -1 : 1;
    const cityRanks = rank(rows.filter((r) => included.has(r.school_number)), (r) => sign * r.value);
    const byNum = new Map(rows.map((r) => [r.school_number, r]));
    const updates: { id: string; cg: number | null; city: number | null }[] = [];
    for (const r of rows) {
      let cg: number | null = null;
      const peers = peerMap.get(r.school_number);
      if (peers) { const inPeers = [...peers].map((p) => byNum.get(p)).filter((x): x is typeof r => !!x); if (inPeers.length >= 5) { const rk = rank(inPeers, (x) => sign * x.value); cg = rk.get(inPeers.find((p) => p.school_number === r.school_number)!) ?? null; } }
      updates.push({ id: r.id, cg, city: cityRanks.get(r) ?? null });
    }
    for (let i = 0; i < updates.length; i += 300) {
      const b = updates.slice(i, i + 300);
      await query(`UPDATE miami_school_year_metrics SET comparison_group_percentile = (CASE id ${b.map((u) => `WHEN '${u.id}' THEN ${u.cg === null ? "NULL" : `${u.cg}::float8`}`).join(" ")} END)::float8, citywide_percentile = (CASE id ${b.map((u) => `WHEN '${u.id}' THEN ${u.city === null ? "NULL" : `${u.city}::float8`}`).join(" ")} END)::float8 WHERE id = ANY($1::text[])`, [b.map((u) => u.id)]);
      updated += b.length;
    }
  }
  console.log(`[pct] updated ${updated.toLocaleString()} rows`);
  await pool.end();
}

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