/**
 * /connectivity — IXPs, cloud regions, carrier hotels and per-metro interconnection density. Cable landing stations
 * have no connector yet and are returned empty with a note.
 */
import type { ConnectivityOverview } from "@dci/core";
import { pg, cloudRegionCols, facilityJoins, facilitySummaryCols } from "../lib/sql.js";
import { int, reqStr, type Row } from "../lib/rows.js";
import { cloudRegionSummary, facilitySummary, ixpSummary } from "../lib/dto.js";

export const CONNECTIVITY_NOTE = "IXP facility / operator counts come from published IXP↔facility links (facility_ixps); IXPs have no coordinates of their own and are placed at their metro reference point on maps. Carrier hotels = facility_type carrier_hotel or ≥ 20 carriers published on the facility. Submarine cable landing stations are empty until a licensed connector exists — never inferred. Cloud regions are associated with a market, not hosted at a specific facility unless a source says so.";

export async function connectivityOverview(): Promise<ConnectivityOverview> {
  const sql = pg();
  const [ixps, cloudRegions, carrierHotels, byMetro] = await Promise.all([
    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,
        (select count(distinct f.operator_id)::int from facility_ixps fx join facilities f on f.id = fx.facility_id where fx.ixp_id = x.id and f.operator_id is not null) as operator_count,
        m.id as met_id, m.slug as met_slug, m.name as met_name, m.lat, m.lng
      from ixps x left join metros m on m.id = x.metro_id
      order by x.network_count desc nulls last, facility_count desc, x.name limit 100`,
    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.status <> 'retired' order by pr.name, r.country_iso2, r.code limit 2000`,
    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and (f.facility_type = 'carrier_hotel' or coalesce(f.carriers_count, 0) >= 20) order by f.carriers_count desc nulls last, f.completeness desc, f.name limit 50`,
    sql<Row[]>`select * from (select m.id, m.slug, m.name, m.country_iso2,
        (select count(*)::int from ixps x where x.metro_id = m.id) as ixps,
        (select count(*)::int from cloud_regions r where r.metro_id = m.id and r.status <> 'retired') as cloud_regions,
        (select count(*)::int from facilities f where f.metro_id = m.id and f.merged_into is null and (coalesce(f.carriers_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id and t.role in ('carrier', 'network')))) as facilities_with_carriers,
        (select count(*)::int from facilities f where f.metro_id = m.id and f.merged_into is null) as facilities
      from metros m
      where exists (select 1 from ixps x where x.metro_id = m.id) or exists (select 1 from cloud_regions r where r.metro_id = m.id) or exists (select 1 from facilities f where f.metro_id = m.id and f.merged_into is null and (coalesce(f.carriers_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id)))) t order by t.ixps + t.cloud_regions + t.facilities_with_carriers desc, t.facilities desc limit 50`,
  ]);
  return {
    ixps: ixps.map((r) => ({ ...ixpSummary(r), operatorCount: int(r.operator_count) })),
    cloudRegions: cloudRegions.map(cloudRegionSummary),
    carrierHotels: carrierHotels.map(facilitySummary),
    landingStations: [],
    byMetro: byMetro.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), countryIso2: reqStr(r.country_iso2), ixps: int(r.ixps), cloudRegions: int(r.cloud_regions), facilitiesWithCarriers: int(r.facilities_with_carriers), facilities: int(r.facilities) })),
    note: CONNECTIVITY_NOTE,
  };
}