/**
 * /power — grid constraints, power / grid events, large loads (utility / grid MW published on facilities, current
 * grid claims, ≥ 200 MW planned projects) and national energy context. Utility / grid MW are supply-side figures,
 * never IT load; national grid averages never describe a facility's contracted electricity.
 */
import type { EnergyContext, PowerOverview } from "@dci/core";
import { pg, facilityView, knownMwAgg, countedAgg, gridConstraintCols, gridConstraintJoins, projectLive, POWER_EVENT_TYPES, GRID_EVENT_TYPES, OPERATIONAL_SET } from "../lib/sql.js";
import { int, num, reqStr, str, type Row } from "../lib/rows.js";
import { gridConstraintDto, gridConstraintFromEvent, round2 } from "../lib/dto.js";
import { eventsWhere } from "./events.js";

export const ENERGY_NOTE = "National grid averages (renewable share, total generation) describe the country's electricity system, not the electricity a given facility contracts (PPAs, on-site generation, utility tariffs). Grid carbon intensity is null until a reliable public source is connected.";
export const POWER_NOTE = "utilityCapacityMw / gridConnectionMw are supply-side figures (power available or contracted from the utility / grid) — never IT load and never comparable with itCapacityMw. Large loads list published site-scoped figures only (utility capacity, grid connection, planned load ≥ 200 MW on live projects). Grid constraints combine curated public reports (grid_constraints) with detected grid / utility / power-agreement events. Substations, power plants and transmission lines have no connector yet and are not shown; `utilities` lists operators whose name says energy / power / electric AND that appear as tenants or in events — empty otherwise.";

export function energyContext(r: Row): EnergyContext {
  return { renewableShare: num(r.renewable_share), electricityTwh: num(r.electricity_twh), statsYear: num(r.stats_year), gridCarbonIntensity: null, sourceName: str(r.energy_source_name), sourceUrl: str(r.energy_source_url), note: ENERGY_NOTE };
}

export async function powerOverview(): Promise<PowerOverview> {
  const sql = pg();
  const known = knownMwAgg(sql), counted = countedAgg(sql);
  const [gcRows, gridEvents, powerEvents, loadsF, loadsC, loadsP, energy, utilities] = await Promise.all([
    sql<Row[]>`select ${gridConstraintCols(sql)} from grid_constraints g ${gridConstraintJoins(sql)} order by g.effective_date desc nulls last, g.created_at desc limit 20`,
    eventsWhere(sql`e.event_type = any(${GRID_EVENT_TYPES})`, 30),
    eventsWhere(sql`e.event_type = any(${POWER_EVENT_TYPES})`, 30),
    sql<Row[]>`select f.id, f.slug, f.name, f.country_iso2, f.utility_capacity_mw, f.grid_connection_mw, m.id as met_id, m.slug as met_slug, m.name as met_name,
        (select s.name from provenance p join sources s on s.id = p.source_id where p.entity_type = 'facility' and p.entity_id = f.id and p.is_current and p.field in ('utilityCapacityMw', 'gridConnectionMw') order by p.is_winner desc, p.last_observed desc limit 1) as source_name,
        (select p.url from provenance p where p.entity_type = 'facility' and p.entity_id = f.id and p.is_current and p.field in ('utilityCapacityMw', 'gridConnectionMw') order by p.is_winner desc, p.last_observed desc limit 1) as url
      from facilities f left join metros m on m.id = f.metro_id
      where f.merged_into is null and (f.utility_capacity_mw is not null or f.grid_connection_mw is not null)
      order by greatest(coalesce(f.utility_capacity_mw, 0), coalesce(f.grid_connection_mw, 0)) desc limit 50`,
    sql<Row[]>`select k.predicate, k.value, k.url, s.name as source_name, f.id, f.slug, f.name, f.country_iso2, m.id as met_id, m.slug as met_slug, m.name as met_name
      from claims k join facilities f on f.id = k.subject_id and k.subject_type = 'facility' left join metros m on m.id = f.metro_id left join sources s on s.id = k.source_id
      where k.status = 'current' and k.predicate in ('grid_connection_mw', 'utility_capacity_mw') and k.value is not null and f.merged_into is null
        and ((k.predicate = 'grid_connection_mw' and f.grid_connection_mw is null) or (k.predicate = 'utility_capacity_mw' and f.utility_capacity_mw is null))
      order by k.value desc limit 50`,
    sql<Row[]>`select p.id, p.slug, p.name, p.country_iso2, p.planned_mw, p.source_url, m.id as met_id, m.slug as met_slug, m.name as met_name,
        (select s.name from provenance pr join sources s on s.id = pr.source_id where pr.entity_type = 'project' and pr.entity_id = p.id and pr.is_current and pr.field = 'plannedMw' order by pr.is_winner desc, pr.last_observed desc limit 1) as source_name
      from projects p left join metros m on m.id = p.metro_id where ${projectLive(sql)} and p.planned_mw >= 200 order by p.planned_mw desc limit 50`,
    sql<Row[]>`select c.iso2, c.name, c.slug, c.renewable_share, c.electricity_twh, c.stats_year,
        (select s.name from provenance p join sources s on s.id = p.source_id where p.entity_type = 'country' and p.entity_id = c.iso2 and p.is_current and p.field in ('renewableShare', 'electricityTwh') limit 1) as energy_source_name,
        (select p.url from provenance p where p.entity_type = 'country' and p.entity_id = c.iso2 and p.is_current and p.field in ('renewableShare', 'electricityTwh') limit 1) as energy_source_url,
        fa.n as facilities, fa.known_mw
      from countries c join (select f.country_iso2, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status = any(${OPERATIONAL_SET}))::float as known_mw from ${facilityView(sql)} f where f.country_iso2 is not null group by 1) fa on fa.country_iso2 = c.iso2
      where c.renewable_share is not null or c.electricity_twh is not null
      order by fa.n desc limit 60`,
    sql<Row[]>`select o.id, o.slug, o.name, o.kind, (select count(*)::int from facility_tenants t where t.operator_id = o.id) as tenancies, (select count(*)::int from facilities f where f.operator_id = o.id and f.merged_into is null) as facility_count
      from operators o where o.name ~* '(energy|power|electric|utility|utilities)'
        and (exists (select 1 from facility_tenants t where t.operator_id = o.id) or exists (select 1 from events e where e.operator_id = o.id or (e.entity_type = 'operator' and e.entity_id = o.id)))
      order by tenancies desc, o.name limit 50`,
  ]);
  const gridConstraints = [...gcRows.map(gridConstraintDto), ...gridEvents.map(gridConstraintFromEvent)];
  const seen = new Set<string>();
  const deduped = gridConstraints.filter((g) => { const k = g.eventId ?? g.id; if (seen.has(k)) return false; seen.add(k); return true; }).slice(0, 30);
  const metro = (r: Row) => (r.met_id ? { id: reqStr(r.met_id), slug: reqStr(r.met_slug), name: reqStr(r.met_name) } : null);
  const largeLoads: PowerOverview["largeLoads"] = [];
  for (const r of loadsF) {
    const fac = { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) };
    const u = num(r.utility_capacity_mw), g = num(r.grid_connection_mw);
    if (u != null) largeLoads.push({ facility: fac, project: null, kind: "utility_capacity", mw: u, countryIso2: str(r.country_iso2), metro: metro(r), sourceName: str(r.source_name), url: str(r.url) });
    if (g != null) largeLoads.push({ facility: fac, project: null, kind: "grid_connection", mw: g, countryIso2: str(r.country_iso2), metro: metro(r), sourceName: str(r.source_name), url: str(r.url) });
  }
  for (const r of loadsC) largeLoads.push({ facility: { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) }, project: null, kind: reqStr(r.predicate) === "grid_connection_mw" ? "grid_connection" : "utility_capacity", mw: num(r.value) ?? 0, countryIso2: str(r.country_iso2), metro: metro(r), sourceName: str(r.source_name), url: str(r.url) });
  for (const r of loadsP) largeLoads.push({ facility: null, project: { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) }, kind: "planned_load", mw: num(r.planned_mw) ?? 0, countryIso2: str(r.country_iso2), metro: metro(r), sourceName: str(r.source_name), url: str(r.source_url) });
  largeLoads.sort((a, b) => b.mw - a.mw);
  return {
    gridConstraints: deduped,
    powerEvents,
    largeLoads: largeLoads.slice(0, 50),
    countryEnergy: energy.map((r) => ({ iso2: reqStr(r.iso2), name: reqStr(r.name), slug: reqStr(r.slug), energy: energyContext(r), knownMw: round2(num(r.known_mw)), facilities: int(r.facilities) })),
    utilities: utilities.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), facilityCount: int(r.facility_count), kind: str(r.kind) })),
    note: POWER_NOTE,
  };
}