/**
 * Global infrastructure pulse — deterministic counts over a time window, computed from events and entity dates.
 * Nothing here is scored or weighted: every figure is a count or a sum of published MW figures.
 */
import type { Pulse } from "@dci/core";
import { pg, eventCols, eventJoins, projectLive, type Fragment, type Sql } from "../lib/sql.js";
import { int, num, reqStr, str, type Row } from "../lib/rows.js";
import { round2 } from "../lib/dto.js";
import { toEventDtos } from "./events.js";

export type PulseWindow = Pulse["window"];
const WINDOW_INTERVAL: Record<PulseWindow, string> = { "24h": "24 hours", "7d": "7 days", "30d": "30 days" };

export const PULSE_METHODOLOGY = "Deterministic counts over the window: events with review_status ≠ rejected; projects by created_at / construction_started_on; facilities by opened_on / first_seen; MW figures are sums of published site-scoped values on the records (no estimates). operatorsNewMarkets = operators whose first facility or project in a metro (or country when no metro) falls inside the window and who had none there before. Hidden (false-positive) and merged projects are excluded.";

function partialDate(sql: Sql, col: Fragment, fallback: Fragment): Fragment {
  return sql`(case when ${col} ~ '^\\d{4}-\\d{2}-\\d{2}' then ${col}::date when ${col} ~ '^\\d{4}-\\d{2}$' then (${col} || '-01')::date when ${col} ~ '^\\d{4}$' then (${col} || '-01-01')::date else ${fallback} end)`;
}

export async function pulse(window: PulseWindow = "24h"): Promise<Pulse> {
  const sql = pg();
  const since = sql`(now() - ${WINDOW_INTERVAL[window]}::interval)`;
  const sinceDate = sql`(now() - ${WINDOW_INTERVAL[window]}::interval)::date`;
  const conDate = partialDate(sql, sql`p.construction_started_on`, sql`null::date`);
  const openedDate = partialDate(sql, sql`f.opened_on`, sql`null::date`);
  const fFirst = sql`coalesce(${partialDate(sql, sql`f.opened_on`, sql`null::date`)}, f.first_seen::date)`;
  const pFirst = partialDate(sql, sql`p.announced_on`, sql`p.created_at::date`);
  const [ev, pr, fa, markets, major, byCountry, byOperator, sinceRow] = await Promise.all([
    sql<Row[]>`select count(*)::int as total,
        count(*) filter (where e.event_type = 'facility_opened')::int as opened_ev,
        count(*) filter (where e.event_type = 'cloud_region_announced')::int as cloud_ann,
        count(*) filter (where e.event_type = 'power_agreement')::int as power,
        count(*) filter (where e.event_type in ('grid_constraint', 'utility_event'))::int as grid,
        count(*) filter (where e.event_type = 'acquisition')::int as acq,
        count(*) filter (where e.event_type = 'investment_announced')::int as fin,
        count(*) filter (where e.event_type = 'facility_discovered')::int as discovered,
        count(*) filter (where e.event_type in ('capacity_changed', 'planned_capacity_changed'))::int as cap,
        count(distinct e.project_id) filter (where e.project_id is not null and (e.event_type = 'construction_started' or (e.event_type = 'project_status_changed' and e.new_value::text ilike '%under_construction%')))::int as con_ev
      from events e where e.review_status <> 'rejected' and e.detected_at >= ${since}`,
    sql<Row[]>`select
        count(*) filter (where p.created_at >= ${since})::int as new_n, sum(p.planned_mw) filter (where p.created_at >= ${since})::float as new_mw,
        count(*) filter (where ${conDate} >= ${sinceDate})::int as con_n, sum(p.planned_mw) filter (where ${conDate} >= ${sinceDate})::float as con_mw
      from projects p where ${projectLive(sql)}`,
    sql<Row[]>`select count(*) filter (where ${openedDate} >= ${sinceDate} and f.status in ('operational', 'partially_operational', 'expansion'))::int as opened, count(*) filter (where f.first_seen >= ${since})::int as indexed from facilities f where f.merged_into is null`,
    sql<Row[]>`
      with entries as (
        select f.operator_id, f.metro_id, f.country_iso2, ${fFirst} as d from facilities f where f.merged_into is null and f.operator_id is not null and (f.metro_id is not null or f.country_iso2 is not null)
        union all
        select p.operator_id, p.metro_id, p.country_iso2, ${pFirst} from projects p where ${projectLive(sql)} and p.operator_id is not null and (p.metro_id is not null or p.country_iso2 is not null)
      ), firsts as (
        select operator_id, metro_id, country_iso2, min(d) as first_d from entries group by 1, 2, 3
      )
      select o.id, o.slug, o.name, m.id as met_id, m.slug as met_slug, m.name as met_name, fs.country_iso2, fs.first_d
      from firsts fs join operators o on o.id = fs.operator_id left join metros m on m.id = fs.metro_id
      where fs.first_d >= ${sinceDate}
        and not exists (select 1 from firsts f2 where f2.operator_id = fs.operator_id and f2.first_d < ${sinceDate} and (f2.metro_id is not distinct from fs.metro_id) and (fs.metro_id is not null or f2.country_iso2 is not distinct from fs.country_iso2))
      order by fs.first_d desc, o.name limit 25`,
    sql<Row[]>`select ${eventCols(sql)} from events e ${eventJoins(sql)} where e.review_status <> 'rejected' and e.detected_at >= ${since} and e.significance >= 75 order by e.significance desc, e.detected_at desc limit 15`,
    sql<Row[]>`select c.iso2, c.name, c.slug, coalesce(ev.n, 0) as events, coalesce(pj.n, 0) as new_projects
      from countries c
      left join (select country_iso2, count(*)::int as n from events where review_status <> 'rejected' and detected_at >= ${since} and country_iso2 is not null group by 1) ev on ev.country_iso2 = c.iso2
      left join (select p.country_iso2, count(*)::int as n from projects p where ${projectLive(sql)} and p.created_at >= ${since} and p.country_iso2 is not null group by 1) pj on pj.country_iso2 = c.iso2
      where coalesce(ev.n, 0) > 0 or coalesce(pj.n, 0) > 0 order by events desc, new_projects desc, c.name limit 15`,
    sql<Row[]>`select o.id, o.slug, o.name, coalesce(ev.n, 0) as events, coalesce(pj.n, 0) as new_projects
      from operators o
      left join (select operator_id, count(*)::int as n from events where review_status <> 'rejected' and detected_at >= ${since} and operator_id is not null group by 1) ev on ev.operator_id = o.id
      left join (select p.operator_id, count(*)::int as n from projects p where ${projectLive(sql)} and p.created_at >= ${since} and p.operator_id is not null group by 1) pj on pj.operator_id = o.id
      where coalesce(ev.n, 0) > 0 or coalesce(pj.n, 0) > 0 order by events desc, new_projects desc, o.name limit 15`,
    sql<Row[]>`select ${since} as since`,
  ]);
  const e = ev[0] ?? {}, p = pr[0] ?? {}, f = fa[0] ?? {};
  return {
    window,
    since: new Date(String(sinceRow[0]?.since ?? Date.now())).toISOString(),
    newProjects: int(p.new_n),
    projectsEnteredConstruction: Math.max(int(p.con_n), int(e.con_ev)),
    facilitiesOpened: Math.max(int(f.opened), int(e.opened_ev)),
    newlyAnnouncedMw: round2(num(p.new_mw)),
    constructionStartedMw: round2(num(p.con_mw)),
    operatorsNewMarkets: markets.map((r) => ({ operator: { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) }, market: r.met_id ? { id: reqStr(r.met_id), slug: reqStr(r.met_slug), name: reqStr(r.met_name) } : null, countryIso2: str(r.country_iso2) })),
    cloudRegionsAnnounced: int(e.cloud_ann),
    powerAgreements: int(e.power),
    gridConstraintEvents: int(e.grid),
    acquisitions: int(e.acq),
    financingEvents: int(e.fin),
    newFacilitiesIndexed: Math.max(int(f.indexed), int(e.discovered)),
    capacityChanges: int(e.cap),
    eventsTotal: int(e.total),
    majorEvents: await toEventDtos(major),
    byCountry: byCountry.map((r) => ({ iso2: reqStr(r.iso2), name: reqStr(r.name), slug: reqStr(r.slug), events: int(r.events), newProjects: int(r.new_projects) })),
    byOperator: byOperator.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), events: int(r.events), newProjects: int(r.new_projects) })),
  };
}