spb/datacenterindex

1import type { GridConstraintDTO, MetroDetail, MetroSummary } from "@dci/core";2import { pg, mwExpr, yearExpr, cloudRegionCols, likePattern, facilityView, knownMwAgg, countedAgg, projectLive, facilityJoins, facilitySummaryCols, eventCols, eventJoins, gridConstraintCols, gridConstraintJoins, AI_LEVELS, GRID_EVENT_TYPES, OPERATIONAL_SET, type Fragment, type Sql } from "../lib/sql.js";3import { int, iso, num, reqStr, str, strArray, type Row } from "../lib/rows.js";4import { cloudRegionSummary, facilitySummary, gridConstraintDto, gridConstraintFromEvent, growthSeries, round2, share } from "../lib/dto.js";5import { findBySlugOrId } from "../lib/resolve.js";6import { listFacilities } from "./facilities.js";7import { operatorsForScope } from "./operators.js";8import { projectsWhere } from "./projects.js";9import { eventsForMetro, toEventDtos } from "./events.js";10import { rankingPositions } from "./rankings.js";11import { concentration, coverageRow, dimAggCols, momentum, pipelineBreakdown, scopeFor } from "../lib/pipeline.js";12import { claimsFor } from "../lib/quality.js";1314function metroSummary(r: Row): MetroSummary {15  const n = int(r.facility_count);16  return {17    id: reqStr(r.id),18    slug: reqStr(r.slug),19    name: reqStr(r.name),20    countryIso2: reqStr(r.country_iso2),21    countryName: str(r.country_name) ?? undefined,22    regionName: str(r.region_name),23    lat: num(r.lat) ?? 0,24    lng: num(r.lng) ?? 0,25    facilityCount: n,26    operationalCount: int(r.operational),27    constructionCount: int(r.construction),28    plannedCount: int(r.planned),29    knownMw: round2(num(r.known_mw)),30    constructionMw: round2(num(r.construction_mw)),31    plannedMw: round2(num(r.planned_mw)),32    operatorCount: int(r.operator_count),33    cloudRegionCount: int(r.cloud_region_count),34    ixpCount: int(r.ixp_count),35    projectCount: int(r.project_count),36    projectPlannedMw: round2(num(r.project_planned_mw)),37    aiCount: int(r.ai),38    mwCoverage: n ? share(int(r.with_mw), n) : 0,39  };40}4142const SELECT = (sql: Sql) => sql`43  select m.id, m.slug, m.name, m.country_iso2, c.name as country_name, m.region_name, m.lat, m.lng, m.aliases, m.description, m.updated_at,44    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,45    fa.known_mw, fa.construction_mw, fa.planned_mw, coalesce(fa.operators, 0) as operator_count, coalesce(fa.with_mw, 0) as with_mw, coalesce(fa.ai, 0) as ai,46    coalesce(cr.n, 0) as cloud_region_count, coalesce(ix.n, 0) as ixp_count, coalesce(pj.n, 0) as project_count, pj.mw as project_planned_mw47  from metros m48  left join countries c on c.iso2 = m.country_iso249  left join (select f.metro_id, ${dimAggCols(sql)} from ${facilityView(sql)} f where f.metro_id is not null group by f.metro_id) fa on fa.metro_id = m.id50  left join (select metro_id, count(*)::int as n from cloud_regions where metro_id is not null and status <> 'retired' group by 1) cr on cr.metro_id = m.id51  left join (select metro_id, count(*)::int as n from ixps where metro_id is not null group by 1) ix on ix.metro_id = m.id52  left join (select p.metro_id, count(*)::int as n, sum(p.planned_mw) filter (where p.status in ('rumored','proposed','announced','permitting','approved','delayed','under_construction'))::float as mw from projects p where p.metro_id is not null and ${projectLive(sql)} group by p.metro_id) pj on pj.metro_id = m.id`;5354export async function listMetros(f: { country?: string; q?: string; limit?: number } = {}): Promise<MetroSummary[]> {55  const sql = pg();56  const conds: Fragment[] = [sql`true`];57  if (f.country) conds.push(sql`m.country_iso2 = ${f.country.toUpperCase()}`);58  if (f.q) conds.push(sql`(m.name ilike ${likePattern(f.q)} or similarity(m.name, ${f.q}) > 0.35 or exists (select 1 from unnest(m.aliases) a where a ilike ${likePattern(f.q)}))`);59  const rows = await sql<Row[]>`${SELECT(sql)} where ${conds.reduce<Fragment>((a, c) => sql`${a} and ${c}`, sql`true`)}60    order by coalesce(fa.facilities, 0) desc, coalesce(cr.n, 0) desc, m.name limit ${f.limit ?? 1000}`;61  return rows.map(metroSummary);62}6364const CONSTRAINT_KINDS: Array<[RegExp, MetroDetail["constraints"][number]["kind"]]> = [65  [/\b(moratorium|pause on|halt(ed|s)? (new )?(data ?cent|approvals)|ban on)\b/i, "moratorium"],66  [/\b(grid (constraint|capacity|connection)|interconnection (queue|delay)|transmission)\b/i, "grid"],67  [/\b(power (shortage|constraint|crunch|capacity|availability)|electricity (shortage|constraint)|megawatts? (short|unavailable)|energy (crunch|shortage))\b/i, "power"],68  [/\b(water (usage|shortage|restriction|consumption|permit))\b/i, "water"],69  [/\b(land (scarcity|shortage|constraint|price|availability)|zoning|rezoning denied|land[- ]use)\b/i, "land"],70];7172async function metroConstraints(metroId: string, name: string, aliases: string[], limit = 10): Promise<MetroDetail["constraints"]> {73  const sql = pg();74  const terms = [name, ...aliases].filter(Boolean);75  const nameCond = terms.length ? terms.map((t) => sql`(n.title ilike ${likePattern(t)} or n.summary ilike ${likePattern(t)})`).reduce<Fragment>((a, c) => sql`${a} or ${c}`, sql`false`) : sql`false`;76  const rows = await sql<Row[]>`77    select n.title, n.summary, n.url, n.published_at, n.created_at, s.name as source_name78    from news_items n left join sources s on s.id = n.source_id79    where (n.metro_id = ${metroId} or n.mentions->>'metroId' = ${metroId} or n.mentions->'metros' ? ${metroId} or n.mentions->'cities' ?| ${terms}::text[] or (${nameCond}))80      and (n.title ~* '(moratorium|grid|power|electricity|substation|water|zoning|land|transmission|interconnection)' or n.summary ~* '(moratorium|grid (constraint|capacity)|power (shortage|constraint|crunch)|water (usage|shortage|restriction)|zoning|land (scarcity|shortage|constraint))')81    order by n.published_at desc nulls last, n.created_at desc limit 40`;82  const out: MetroDetail["constraints"] = [];83  for (const r of rows) {84    const text = `${reqStr(r.title)} ${reqStr(r.summary)}`;85    const kind = CONSTRAINT_KINDS.find(([re]) => re.test(text))?.[1];86    if (!kind) continue;87    out.push({ kind, summary: reqStr(r.title), url: reqStr(r.url), sourceName: reqStr(r.source_name, "news"), date: iso(r.published_at) ?? iso(r.created_at) });88    if (out.length >= limit) break;89  }90  return out;91}9293/** grid_constraints rows + grid / utility / power events for a metro or a country, deduplicated by event id. */94export async function gridConstraintsFor(scope: { metroId?: string; countryIso2?: string }, limit = 30): Promise<GridConstraintDTO[]> {95  const sql = pg();96  const gCond = scope.metroId ? sql`g.metro_id = ${scope.metroId}` : scope.countryIso2 ? sql`g.country_iso2 = ${scope.countryIso2}` : sql`true`;97  const eCond = scope.metroId ? sql`(e.metro_id = ${scope.metroId} or (e.entity_type = 'facility' and e.entity_id in (select id from facilities where metro_id = ${scope.metroId})))` : scope.countryIso2 ? sql`e.country_iso2 = ${scope.countryIso2}` : sql`true`;98  const [rows, evRows] = await Promise.all([99    sql<Row[]>`select ${gridConstraintCols(sql)} from grid_constraints g ${gridConstraintJoins(sql)} where ${gCond} order by g.effective_date desc nulls last, g.created_at desc limit ${limit}`,100    sql<Row[]>`select ${eventCols(sql)} from events e ${eventJoins(sql)} where ${eCond} and e.event_type = any(${GRID_EVENT_TYPES}) and e.review_status <> 'rejected' order by e.detected_at desc limit ${limit}`,101  ]);102  const out = rows.map(gridConstraintDto);103  const seen = new Set(out.map((g) => g.eventId).filter(Boolean));104  for (const e of await toEventDtos(evRows)) { if (seen.has(e.id)) continue; seen.add(e.id); out.push(gridConstraintFromEvent(e)); }105  return out.slice(0, limit);106}107108export async function getMetroDetail(idOrSlug: string, fPage = 1): Promise<MetroDetail | null> {109  const sql = pg();110  const base = await findBySlugOrId("metros", idOrSlug);111  if (!base) return null;112  const id = String(base.id);113  const aliases = strArray(base.aliases);114  const scope = scopeFor(sql, { metroId: id });115  const [sumRows, operators, cloudProviderRows, cloudRegionRows, ixpRows, facilities, projects, recentEvents, constraints, growthRows, rankings, conc, mom, pipeline, gridConstraints, aiRows, openingRows, coverage, claims] = await Promise.all([116    sql<Row[]>`${SELECT(sql)} where m.id = ${id}`,117    operatorsForScope({ metroId: id }, 15),118    sql<Row[]>`select pr.id, pr.slug, pr.name, count(*)::int as n from cloud_regions r join operators pr on pr.id = r.provider_id where r.metro_id = ${id} group by 1, 2, 3 order by n desc, pr.name`,119    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.metro_id = ${id} order by pr.name, r.code`,120    sql<Row[]>`select id, slug, name, network_count from ixps where metro_id = ${id} order by network_count desc nulls last, name`,121    listFacilities({ metroId: id, page: fPage, per_page: 50, sort: "mw", order: "desc" }),122    projectsWhere(sql`${projectLive(sql)} and p.metro_id = ${id}`, 20),123    eventsForMetro(id, 20),124    metroConstraints(id, String(base.name), aliases),125    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.metro_id = ${id} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,126    rankingPositions("metros", [id, String(base.slug)]),127    concentration(scope),128    momentum(scope),129    pipelineBreakdown(scope),130    gridConstraintsFor({ metroId: id }),131    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and f.metro_id = ${id} and f.ai_evidence = any(${AI_LEVELS}) order by ${mwExpr(sql)} desc nulls last, f.name limit 20`,132    sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${countedAgg(sql)})::int as n, sum(${knownMwAgg(sql)}) filter (where f.status = any(${OPERATIONAL_SET}))::float as mw from ${facilityView(sql)} f where f.metro_id = ${id} and f.opened_on ~ '^\\d{4}' group by 1 order by 1`,133    coverageRow(id, String(base.name), String(base.slug), scope),134    Promise.all([claimsFor("metro", id), claimsFor("market", id)]).then(([a, b]) => [...a, ...b]),135  ]);136  const summary = metroSummary(sumRows[0] ?? { ...base, facility_count: 0 });137  return {138    ...summary,139    concentration: conc,140    momentum: mom,141    pipeline,142    gridConstraints,143    aiFacilities: aiRows.map(facilitySummary),144    openingTimeline: openingRows.filter((r) => int(r.year) > 0).map((r) => ({ year: int(r.year), opened: int(r.n), openedMw: round2(num(r.mw)) })),145    coverage,146    claims,147    aliases,148    description: str(base.description),149    operators,150    cloudProviders: cloudProviderRows.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), regionCount: int(r.n) })),151    cloudRegions: cloudRegionRows.map(cloudRegionSummary),152    ixps: ixpRows.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), networkCount: num(r.network_count) })),153    facilities: facilities.items,154    projects,155    recentEvents,156    constraints,157    growth: growthSeries(growthRows),158    rankings,159  };160}161