scripts/loaders/philly/compute_percentiles.ts

// Compute comparison_group_percentile + citywide_percentile for every
// (school × year × metric × subgroup × population_cut) row in
// philly_school_year_metrics.
//
// citywide_percentile: rank within all default-included Philly schools for the
//   same (metric, year, subgroup, population_cut), expressed as a fraction in
//   [0, 1].
// comparison_group_percentile: rank within the school's stored peer group
//   (from philly_comparison_groups.methodology_notes JSON, k=DEFAULT_K), same
//   (year, metric, subgroup, population_cut). Schools without a peer group
//   get null.
//
// For metrics where lower is better (e.g., OSS rates), we invert so a high
// percentile always means "better outcomes."
//
// Mirrors scripts/loaders/compute-percentiles.ts (NYC). Same midrank
// algorithm + same minimum-5-peers floor for peer percentiles.
//
// Run:  npx tsx scripts/loaders/philly/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)!;
let pool: pg.Pool;
function makePool() {
  const u = new URL(directUrl);
  // Honor `?sslmode=disable` (local/dev Postgres has no TLS); otherwise keep
  // the permissive TLS Supabase needs.
  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,
    connectionTimeoutMillis: 15_000,
    idleTimeoutMillis: 10_000,
    statement_timeout: 60_000,
  });
}
pool = makePool();

async function query<T = unknown>(sql: string, params?: unknown[]): Promise<T[]> {
  let attempts = 0;
  while (true) {
    try {
      const r = await pool.query(sql, params);
      return r.rows as T[];
    } catch (e) {
      attempts++;
      if (attempts >= 4) throw e;
      const msg = (e as Error).message;
      console.warn(`  query err (att ${attempts}): ${msg.slice(0, 80)} — reconnecting`);
      try { await pool.end(); } catch { /* ignore */ }
      pool = makePool();
      await new Promise((r) => setTimeout(r, 2000 * attempts));
    }
  }
}

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;
  const 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 rankMid = (i + j) / 2 + 1; // 1-based midrank
    const pct = n === 1 ? 0.5 : (rankMid - 1) / (n - 1);
    for (let k = i; k <= j; k++) out.set(sorted[k].t, pct);
    i = j + 1;
  }
  return out;
}

async function main() {
  // Get each school's peer group (default k linked into philly_schools).
  const schoolRows = await query<{
    ulcs_code: string;
    methodology_notes: { peers: string[] } | null;
    include_in_default_comparisons: boolean;
  }>(
    `SELECT s.ulcs_code,
            cg.methodology_notes::jsonb AS methodology_notes,
            s.include_in_default_comparisons
       FROM philly_schools s
       LEFT JOIN philly_comparison_groups cg ON cg.id = s.comparison_group_id`,
  );
  const peerMap = new Map<string, Set<string>>();
  const includedSchools = new Set<string>();
  for (const s of schoolRows) {
    if (s.include_in_default_comparisons) includedSchools.add(s.ulcs_code);
    if (s.methodology_notes && Array.isArray(s.methodology_notes.peers)) {
      peerMap.set(s.ulcs_code, new Set([s.ulcs_code, ...s.methodology_notes.peers]));
    }
  }
  console.log(`Schools with peer groups: ${peerMap.size}`);
  console.log(`Schools in default comparisons: ${includedSchools.size}`);

  const combos = await query<{ metric_key: string; year: string; subgroup: string; population_cut: string }>(
    `SELECT DISTINCT metric_key, year, subgroup::text AS subgroup, population_cut
       FROM philly_school_year_metrics
      WHERE value IS NOT NULL AND suppressed = false`,
  );
  console.log(`(metric, year, subgroup, population_cut) combos: ${combos.length}`);

  const metricsList = await query<{ key: string; direction: string }>(
    `SELECT key, direction FROM philly_metric_definitions`,
  );
  const directionByKey = new Map(metricsList.map((m) => [m.key, m.direction]));

  let updated = 0;
  let comboIdx = 0;
  for (const { metric_key, year, subgroup, population_cut } of combos) {
    comboIdx++;
    const rows = await query<{ id: string; school_ulcs: string; value: number }>(
      `SELECT id, school_ulcs, value::float8 AS value
         FROM philly_school_year_metrics
        WHERE metric_key = $1 AND year = $2 AND subgroup::text = $3
          AND population_cut = $4
          AND value IS NOT NULL AND suppressed = false`,
      [metric_key, year, subgroup, population_cut],
    );
    if (rows.length === 0) continue;

    const direction = directionByKey.get(metric_key) ?? "HIGHER_BETTER";
    const sign = direction === "LOWER_BETTER" ? -1 : 1;

    // Citywide rank (only default-included schools)
    const citywideRows = rows.filter((r) => includedSchools.has(r.school_ulcs));
    const cityRanks = rank(citywideRows, (r) => sign * r.value);

    const byUlcs = new Map(rows.map((r) => [r.school_ulcs, r]));
    const updatesById: Array<{ id: string; cg: number | null; city: number | null }> = [];
    for (const r of rows) {
      const peers = peerMap.get(r.school_ulcs);
      let cg: number | null = null;
      if (peers) {
        const inPeers = [...peers]
          .map((u) => byUlcs.get(u))
          .filter((x): x is { id: string; school_ulcs: string; value: number } => !!x);
        if (inPeers.length >= 5) {
          const ranks = rank(inPeers, (x) => sign * x.value);
          const me = inPeers.find((p) => p.school_ulcs === r.school_ulcs);
          if (me) cg = ranks.get(me) ?? null;
        }
      }
      const city = cityRanks.get(r) ?? null;
      updatesById.push({ id: r.id, cg, city });
    }

    const batchSize = 300;
    for (let i = 0; i < updatesById.length; i += batchSize) {
      const batch = updatesById.slice(i, i + batchSize);
      const ids = batch.map((u) => u.id);
      const cgCases = batch
        .map((u) => `WHEN '${u.id}' THEN ${u.cg === null ? "NULL" : `${u.cg}::float8`}`)
        .join(" ");
      const cityCases = batch
        .map((u) => `WHEN '${u.id}' THEN ${u.city === null ? "NULL" : `${u.city}::float8`}`)
        .join(" ");
      const sql = `
        UPDATE philly_school_year_metrics
        SET comparison_group_percentile = (CASE id ${cgCases} END)::float8,
            citywide_percentile = (CASE id ${cityCases} END)::float8
        WHERE id = ANY($1::text[])
      `;
      await query(sql, [ids]);
      updated += batch.length;
    }
    if (comboIdx % 100 === 0) {
      console.log(`  ${comboIdx}/${combos.length} combos done, ${updated.toLocaleString()} rows updated`);
    }
  }

  console.log(`Updated percentiles on ${updated.toLocaleString()} rows`);
  await pool.end();
}

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