spb/datacenterindex

1/**2 * /time-machine — yearly frames of the index as it would have looked from published opening dates. Frames only3 * include facilities WITH an opening date, so every year is a lower bound; coverage is stated.4 */5import type { TimeMachine, TimeMachineFrame } from "@dci/core";6import { pg, facilityView, knownMwAgg, countedAgg, projectLive, yearExpr } from "../lib/sql.js";7import { int, num, type Row } from "../lib/rows.js";8import { round2, share } from "../lib/dto.js";910export const TIME_MACHINE_NOTE = "Frames only include facilities with a published opening date (coverage given); facilities without a date are absent from every frame, so early years are lower bounds. knownMw is the cumulative known operational MW of dated facilities (containment-aware, published figures only). announced / construction count live projects by announcement / construction-start year.";11export const RELIABLE_MIN_FACILITIES = 20;1213export async function timeMachine(): Promise<TimeMachine> {14  const sql = pg();15  const known = knownMwAgg(sql), counted = countedAgg(sql);16  const openedYear = yearExpr(sql, sql`f.opened_on`);17  const [byYear, cov, ann, con] = await Promise.all([18    sql<Row[]>`select ${openedYear} as year, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status in ('operational', 'partially_operational', 'expansion'))::float as mw from ${facilityView(sql)} f where f.opened_on ~ '^\\d{4}' group by 1 order by 1`,19    sql<Row[]>`select count(*) filter (where ${counted})::int as n, count(*) filter (where ${counted} and f.opened_on ~ '^\\d{4}')::int as dated from ${facilityView(sql)} f`,20    sql<Row[]>`select ${yearExpr(sql, sql`p.announced_on`)} as year, count(*)::int as n from projects p where ${projectLive(sql)} and p.announced_on ~ '^\\d{4}' group by 1`,21    sql<Row[]>`select ${yearExpr(sql, sql`p.construction_started_on`)} as year, count(*)::int as n from projects p where ${projectLive(sql)} and p.construction_started_on ~ '^\\d{4}' group by 1`,22  ]);23  const opened = new Map<number, { n: number; mw: number | null }>();24  for (const r of byYear) { const y = int(r.year); if (y) opened.set(y, { n: int(r.n), mw: num(r.mw) }); }25  const annMap = new Map<number, number>(ann.map((r) => [int(r.year), int(r.n)]));26  const conMap = new Map<number, number>(con.map((r) => [int(r.year), int(r.n)]));27  const nowYear = new Date().getUTCFullYear();28  const years = [...opened.keys(), ...annMap.keys(), ...conMap.keys()].filter((y) => y >= 1950 && y <= nowYear + 10);29  const frames: TimeMachineFrame[] = [];30  let earliestReliableYear = 0;31  if (years.length) {32    const from = Math.min(...years);33    const to = Math.max(nowYear, ...years.filter((y) => y <= nowYear));34    let cumN = 0, cumMw = 0, sawMw = false;35    for (let y = from; y <= to; y++) {36      const o = opened.get(y);37      if (o) { cumN += o.n; if (o.mw != null) { cumMw += o.mw; sawMw = true; } }38      if (!earliestReliableYear && cumN >= RELIABLE_MIN_FACILITIES) earliestReliableYear = y;39      frames.push({ year: y, facilities: cumN, knownMw: sawMw ? round2(cumMw) : null, announced: annMap.get(y) ?? 0, construction: conMap.get(y) ?? 0, opened: o?.n ?? 0 });40    }41  }42  return { frames, earliestReliableYear: earliestReliableYear || (frames.at(-1)?.year ?? nowYear), openingDateCoverage: share(int(cov[0]?.dated), int(cov[0]?.n)), note: TIME_MACHINE_NOTE };43}44