spb/datacenterindex

1import type { CountryDetail, CountrySummary, EnergyContext } from "@dci/core";2import { pg, mwExpr, yearExpr, facilityView, knownMwAgg, countedAgg, projectLive, facilityJoins, facilitySummaryCols, projectJoins, projectSummaryCols, cloudRegionCols, AI_LEVELS, type Sql } from "../lib/sql.js";3import { int, num, reqStr, str, type Row } from "../lib/rows.js";4import { growthSeries, statusBreakdown, typeBreakdown, cloudRegionSummary, facilitySummary, projectSummary, ixpSummary, round2, share } from "../lib/dto.js";5import { findCountry } from "../lib/resolve.js";6import { listFacilities } from "./facilities.js";7import { operatorsForScope } from "./operators.js";8import { gridConstraintsFor, listMetros } from "./metros.js";9import { projectsWhere } from "./projects.js";10import { eventsForCountry } from "./events.js";11import { rankingPositions } from "./rankings.js";12import { coverageRow, dimAggCols, pipelineBreakdown, scopeFor } from "../lib/pipeline.js";13import { claimsFor } from "../lib/quality.js";1415export const ENERGY_NOTE = "National grid averages (renewable share, generation) describe the country's electricity mix, not the electricity a facility contracts; utility/grid MW on facilities are supply figures, not IT load.";1617function countrySummary(r: Row): CountrySummary {18  const n = int(r.facility_count);19  const withMw = int(r.with_mw);20  return {21    iso2: reqStr(r.iso2),22    iso3: str(r.iso3),23    slug: reqStr(r.slug),24    name: reqStr(r.name),25    region: str(r.region),26    subregion: str(r.subregion),27    population: num(r.population),28    gdpUsd: num(r.gdp_usd),29    facilityCount: n,30    operationalCount: int(r.operational),31    constructionCount: int(r.construction),32    plannedCount: int(r.planned),33    knownMw: round2(num(r.known_mw)),34    constructionMw: round2(num(r.construction_mw)),35    plannedMw: round2(num(r.planned_mw)),36    operatorCount: int(r.operator_count),37    cloudRegionCount: int(r.cloud_region_count),38    hyperscaleCount: int(r.hyperscale),39    aiCount: int(r.ai),40    projectCount: int(r.project_count),41    projectPlannedMw: round2(num(r.project_planned_mw)),42    projectConstructionMw: round2(num(r.project_construction_mw)),43    ixpCount: int(r.ixp_count),44    mwCoverage: n ? share(withMw, n) : 0,45    lat: num(r.lat),46    lng: num(r.lng),47  };48}4950const SELECT = (sql: Sql) => sql`51  select c.iso2, c.iso3, c.slug, c.name, c.region, c.subregion, c.population, c.gdp_usd, c.lat, c.lng, c.electricity_twh, c.renewable_share, c.stats_year, c.stats,52    coalesce(fa.facilities, 0) as facility_count, coalesce(fa.operational, 0) as operational, coalesce(fa.construction, 0) as construction, coalesce(fa.planned, 0) as planned,53    fa.known_mw, fa.construction_mw, fa.planned_mw, coalesce(fa.operators, 0) as operator_count, coalesce(fa.hyperscale, 0) as hyperscale, coalesce(fa.ai, 0) as ai, coalesce(fa.with_mw, 0) as with_mw,54    coalesce(cr.n, 0) as cloud_region_count, coalesce(pj.n, 0) as project_count, pj.planned_mw as project_planned_mw, pj.construction_mw as project_construction_mw, coalesce(ix.n, 0) as ixp_count55  from countries c56  left join (select f.country_iso2, ${dimAggCols(sql)} from ${facilityView(sql)} f where f.country_iso2 is not null group by f.country_iso2) fa on fa.country_iso2 = c.iso257  left join (select country_iso2, count(*)::int as n from cloud_regions where status <> 'retired' group by 1) cr on cr.country_iso2 = c.iso258  left join (select country_iso2, count(*)::int as n from ixps group by 1) ix on ix.country_iso2 = c.iso259  left join (select p.country_iso2, count(*)::int as n,60      sum(p.planned_mw) filter (where p.status in ('rumored','proposed','announced','permitting','approved','delayed'))::float as planned_mw,61      sum(p.planned_mw) filter (where p.status = 'under_construction')::float as construction_mw62    from projects p where ${projectLive(sql)} group by 1) pj on pj.country_iso2 = c.iso2`;6364export async function listCountries(opts: { all?: boolean; region?: string } = {}): Promise<CountrySummary[]> {65  const sql = pg();66  const rows = await sql<Row[]>`${SELECT(sql)}67    where ${opts.all ? sql`true` : sql`(coalesce(fa.facilities, 0) > 0 or coalesce(cr.n, 0) > 0)`}68      and ${opts.region ? sql`(c.region ilike ${opts.region} or c.subregion ilike ${opts.region})` : sql`true`}69    order by coalesce(fa.facilities, 0) desc, coalesce(cr.n, 0) desc, c.name`;70  return rows.map(countrySummary);71}7273async function energyContext(base: Row): Promise<EnergyContext> {74  const sql = pg();75  const stats = (base.stats as Record<string, unknown> | null) ?? {};76  let sourceName: string | null = null;77  let sourceUrl: string | null = null;78  const es = stats.energySource;79  if (es && typeof es === "object") { sourceName = str((es as Record<string, unknown>).name); sourceUrl = str((es as Record<string, unknown>).url); }80  else if (typeof es === "string") sourceName = es;81  if (!sourceName) {82    const rows = await sql<Row[]>`select s.name, p.url from provenance p left join sources s on s.id = p.source_id where p.entity_type = 'country' and p.entity_id = ${String(base.iso2)} and p.is_current and p.field in ('renewableShare', 'electricityTwh', 'renewable_share', 'electricity_twh') order by p.last_observed desc limit 1`;83    if (rows[0]) { sourceName = str(rows[0].name); sourceUrl = str(rows[0].url); }84  }85  return { renewableShare: num(base.renewable_share), electricityTwh: num(base.electricity_twh), statsYear: num(base.stats_year), gridCarbonIntensity: null, sourceName, sourceUrl, note: ENERGY_NOTE };86}8788export async function getCountryDetail(slugOrIso2: string, fPage = 1): Promise<CountryDetail | null> {89  const sql = pg();90  const base = await findCountry(slugOrIso2);91  if (!base) return null;92  const iso2 = String(base.iso2);93  const scope = scopeFor(sql, { countryIso2: iso2 });94  const [sumRows, topOperators, metros, cloudRegionRows, recentProjects, recentEvents, growthRows, statusRows, typeRows, invRows, rankings, facilities, ixpRows, gridConstraints, energy, aiFacRows, aiPrjRows, pipeline, coverage, claims] = await Promise.all([95    sql<Row[]>`${SELECT(sql)} where c.iso2 = ${iso2}`,96    operatorsForScope({ countryIso2: iso2 }, 10),97    listMetros({ country: iso2 }),98    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.country_iso2 = ${iso2} order by pr.name, r.code`,99    projectsWhere(sql`${projectLive(sql)} and p.country_iso2 = ${iso2}`, 10),100    eventsForCountry(iso2, 20),101    sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${countedAgg(sql)})::int as n, sum(${knownMwAgg(sql)})::float as mw from ${facilityView(sql)} f where f.country_iso2 = ${iso2} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,102    sql<Row[]>`select status, count(*)::int as n from facilities where country_iso2 = ${iso2} and merged_into is null group by status`,103    sql<Row[]>`select facility_type, count(*)::int as n from facilities where country_iso2 = ${iso2} and merged_into is null group by facility_type`,104    sql<Row[]>`select sum(p.investment_usd)::float as inv from projects p where ${projectLive(sql)} and p.country_iso2 = ${iso2} and p.status not in ('cancelled') and (p.investment_scope is null or p.investment_scope in ('facility', 'campus', 'building'))`,105    rankingPositions("countries", [iso2, String(base.slug)]),106    listFacilities({ countryIso2: iso2, page: fPage, per_page: 50, sort: "mw", order: "desc" }),107    sql<Row[]>`select x.id, x.slug, x.name, x.name_long, x.city, x.country_iso2, x.website, x.network_count, (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count, m.id as met_id, m.slug as met_slug, m.name as met_name108      from ixps x left join metros m on m.id = x.metro_id where x.country_iso2 = ${iso2} order by x.network_count desc nulls last, x.name limit 500`,109    gridConstraintsFor({ countryIso2: iso2 }),110    energyContext(base),111    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and f.country_iso2 = ${iso2} and f.ai_evidence = any(${AI_LEVELS}) order by ${mwExpr(sql)} desc nulls last, f.name limit 20`,112    sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} and p.country_iso2 = ${iso2} and (p.ai_evidence = any(${AI_LEVELS}) or p.is_ai) order by p.planned_mw desc nulls last, p.last_update desc limit 20`,113    pipelineBreakdown(scope),114    coverageRow(iso2, String(base.name), String(base.slug), scope),115    claimsFor("country", iso2),116  ]);117  const summary = countrySummary(sumRows[0] ?? { ...base, facility_count: 0 });118  return {119    ...summary,120    ixps: ixpRows.map(ixpSummary),121    gridConstraints,122    energy,123    aiFacilities: aiFacRows.map(facilitySummary),124    aiProjects: aiPrjRows.map(projectSummary),125    pipeline,126    coverage,127    claims,128    topOperators,129    metros,130    cloudRegions: cloudRegionRows.map(cloudRegionSummary),131    recentProjects,132    recentEvents,133    growth: growthSeries(growthRows),134    statusBreakdown: statusBreakdown(statusRows),135    typeBreakdown: typeBreakdown(typeRows),136    announcedInvestmentUsd: num(invRows[0]?.inv),137    renewableShare: num(base.renewable_share),138    electricityTwh: num(base.electricity_twh),139    rankings,140    facilities: facilities.items,141  };142}143144/** Countries matching a text for search (name / iso / slug). */145export async function searchCountries(text: string, limit = 3): Promise<Array<{ iso2: string; slug: string; name: string; facilityCount: number; exact: boolean }>> {146  const sql = pg();147  const up = text.toUpperCase();148  const rows = await sql<Row[]>`149    select c.iso2, c.slug, c.name, (select count(*)::int from facilities f where f.country_iso2 = c.iso2 and f.merged_into is null) as n,150      (lower(c.name) = lower(${text}) or c.iso2 = ${up} or c.iso3 = ${up} or c.slug = lower(${text})) as exact151    from countries c152    where c.name ilike ${"%" + text + "%"} or c.iso2 = ${up} or c.iso3 = ${up} or c.slug = lower(${text})153    order by exact desc, n desc, c.name limit ${limit}`;154  return rows.map((r) => ({ iso2: reqStr(r.iso2), slug: reqStr(r.slug), name: reqStr(r.name), facilityCount: int(r.n), exact: r.exact === true }));155}156