/**
 * /time-machine — yearly frames of the index as it would have looked from published opening dates. Frames only
 * include facilities WITH an opening date, so every year is a lower bound; coverage is stated.
 */
import type { TimeMachine, TimeMachineFrame } from "@dci/core";
import { pg, facilityView, knownMwAgg, countedAgg, projectLive, yearExpr } from "../lib/sql.js";
import { int, num, type Row } from "../lib/rows.js";
import { round2, share } from "../lib/dto.js";

export 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.";
export const RELIABLE_MIN_FACILITIES = 20;

export async function timeMachine(): Promise<TimeMachine> {
  const sql = pg();
  const known = knownMwAgg(sql), counted = countedAgg(sql);
  const openedYear = yearExpr(sql, sql`f.opened_on`);
  const [byYear, cov, ann, con] = await Promise.all([
    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`,
    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`,
    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`,
    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`,
  ]);
  const opened = new Map<number, { n: number; mw: number | null }>();
  for (const r of byYear) { const y = int(r.year); if (y) opened.set(y, { n: int(r.n), mw: num(r.mw) }); }
  const annMap = new Map<number, number>(ann.map((r) => [int(r.year), int(r.n)]));
  const conMap = new Map<number, number>(con.map((r) => [int(r.year), int(r.n)]));
  const nowYear = new Date().getUTCFullYear();
  const years = [...opened.keys(), ...annMap.keys(), ...conMap.keys()].filter((y) => y >= 1950 && y <= nowYear + 10);
  const frames: TimeMachineFrame[] = [];
  let earliestReliableYear = 0;
  if (years.length) {
    const from = Math.min(...years);
    const to = Math.max(nowYear, ...years.filter((y) => y <= nowYear));
    let cumN = 0, cumMw = 0, sawMw = false;
    for (let y = from; y <= to; y++) {
      const o = opened.get(y);
      if (o) { cumN += o.n; if (o.mw != null) { cumMw += o.mw; sawMw = true; } }
      if (!earliestReliableYear && cumN >= RELIABLE_MIN_FACILITIES) earliestReliableYear = y;
      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 });
    }
  }
  return { frames, earliestReliableYear: earliestReliableYear || (frames.at(-1)?.year ?? nowYear), openingDateCoverage: share(int(cov[0]?.dated), int(cov[0]?.n)), note: TIME_MACHINE_NOTE };
}