spb/datacenterindex

1import type { FacilitySummary, IxpDetail as IxpDetailContract, IxpSummary } from "@dci/core";2import { pg, facilityJoins, facilitySummaryCols, haversineExpr, withinBbox, likePattern } from "../lib/sql.js";3import { num, record, reqIso, reqStr, round, str, type Row } from "../lib/rows.js";4import { facilitySummary, ixpSummary } from "../lib/dto.js";5import { findBySlugOrId } from "../lib/resolve.js";6import { eventsForEntity } from "./events.js";7import { provenanceFor } from "./facilities.js";8import { buildSourceHistory, documentVersionsFor } from "../lib/source-history.js";910/** IXP columns joined with the metro (ixps have no coordinates of their own — the metro reference point stands in). */11const COLS = "x.id, x.slug, x.name, x.name_long, x.city, x.country_iso2, x.website, x.network_count, xm.id as met_id, xm.slug as met_slug, xm.name as met_name, xm.lat as lat, xm.lng as lng";1213export async function listIxps(f: { country?: string; metroId?: string; q?: string }): Promise<IxpSummary[]> {14  const sql = pg();15  const rows = await sql<Row[]>`16    select ${sql.unsafe(COLS)}, (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count17    from ixps x left join metros xm on xm.id = x.metro_id18    where ${f.country ? sql`x.country_iso2 = ${f.country.toUpperCase()}` : sql`true`}19      and ${f.metroId ? sql`x.metro_id = ${f.metroId}` : sql`true`}20      and ${f.q ? sql`(x.name ilike ${likePattern(f.q)} or x.name_long ilike ${likePattern(f.q)})` : sql`true`}21    order by x.network_count desc nulls last, x.name limit 2000`;22  return rows.map(ixpSummary);23}2425/** Contract IxpDetail + the extra fields the web already reads. */26export interface IxpDetail extends IxpDetailContract {27  countryName: string | null;28  regionContinent: string | null;29  updatedAt: string;30}3132export const IXP_COORDS_NOTE = "This IXP has no published coordinates in the index; lat/lng and the nearby facilities use its metro reference point. Traffic figures are not indexed (no licensed source).";3334export async function getIxp(idOrSlug: string): Promise<IxpDetail | null> {35  const sql = pg();36  const row = await findBySlugOrId("ixps", idOrSlug);37  if (!row) return null;38  const id = String(row.id);39  const metroId = str(row.metro_id);40  const [main, facs, metroRows, countryRows, provenance, events, versions] = await Promise.all([41    sql<Row[]>`select ${sql.unsafe(COLS)}, (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count from ixps x left join metros xm on xm.id = x.metro_id where x.id = ${id}`,42    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} join facility_ixps fx on fx.facility_id = f.id where fx.ixp_id = ${id} and f.merged_into is null order by f.name limit 200`,43    metroId ? sql<Row[]>`select id, slug, name, lat, lng from metros where id = ${metroId}` : Promise.resolve([] as Row[]),44    row.country_iso2 ? sql<Row[]>`select name from countries where iso2 = ${String(row.country_iso2)}` : Promise.resolve([] as Row[]),45    provenanceFor("ixp", id),46    eventsForEntity("ixp", id, 50),47    documentVersionsFor("ixp", id),48  ]);49  const metro = metroRows[0] ?? null;50  const lat = metro ? num(metro.lat) : null, lng = metro ? num(metro.lng) : null;51  const facilities = facs.map(facilitySummary);52  const linkedIds = facilities.map((f) => f.id);53  let nearbyFacilities: Array<FacilitySummary & { distanceKm: number }> = [];54  if (lat != null && lng != null) {55    const dist = haversineExpr(sql, lat, lng);56    const rows = await sql<Row[]>`select ${facilitySummaryCols(sql)}, ${dist} as distance_km from facilities f ${facilityJoins(sql)}57      where f.merged_into is null and f.lat is not null and ${withinBbox(sql, lat, lng, 25, sql`f.lat`, sql`f.lng`)} and ${dist} <= 25 ${linkedIds.length ? sql`and f.id <> all(${linkedIds})` : sql``}58      order by ${dist} asc limit 20`;59    nearbyFacilities = rows.map((r) => ({ ...facilitySummary(r), distanceKm: round(num(r.distance_km), 2) ?? 0 }));60  }61  const opMap = new Map<string, { id: string; slug: string; name: string; facilityCount: number }>();62  for (const f of facilities) if (f.operator) { const cur = opMap.get(f.operator.id) ?? { ...f.operator, facilityCount: 0 }; cur.facilityCount++; opMap.set(f.operator.id, cur); }63  return {64    ...ixpSummary(main[0] ?? { ...row, facility_count: facs.length }),65    metro: metro ? { id: reqStr(metro.id), slug: reqStr(metro.slug), name: reqStr(metro.name) } : null,66    lat,67    lng,68    facilities,69    operators: [...opMap.values()].sort((a, b) => b.facilityCount - a.facilityCount || a.name.localeCompare(b.name)),70    nearbyFacilities,71    trafficNote: metro ? IXP_COORDS_NOTE : null,72    externalIds: record(row.external_ids),73    sourceHistory: buildSourceHistory(provenance, events, versions),74    countryName: countryRows[0] ? String(countryRows[0].name) : null,75    regionContinent: str(row.region_continent),76    updatedAt: reqIso(row.updated_at),77  };78}79