/**
 * Shared "what is around this point" helper (facilities, projects, IXPs, cloud regions, metros) — haversine on a bbox
 * pre-filter, no PostGIS. Layers without a connector (cable landing stations, substations, power plants) are returned
 * EMPTY with a note: nothing is ever faked.
 */
import type { NearbyInfrastructure } from "@dci/core";
import { pg, facilityJoins, facilitySummaryCols, projectJoins, projectSummaryCols, cloudRegionCols, haversineFor, withinBbox, projectLive, type Fragment } from "./sql.js";
import { num, reqStr, round, type Row } from "./rows.js";
import { cloudRegionSummary, facilitySummary, ixpSummary, projectSummary } from "./dto.js";

export const NEARBY_TYPES = ["facilities", "projects", "ixps", "cloud_regions", "metros"] as const;
export type NearbyType = (typeof NEARBY_TYPES)[number];
export const NEARBY_MAX_RADIUS_KM = 200;
export const NEARBY_NOTE = "Distances are great-circle (haversine) from the centre; city-level coordinates are approximate (see geoPrecision). IXPs without published coordinates are placed at their metro reference point (lat/lng from the metro, flagged in `note`). Cable landing stations, substations and power plants are empty until a licensed connector for those layers exists — they are never inferred.";

export interface NearbyOptions {
  lat: number;
  lng: number;
  radiusKm?: number;
  types?: NearbyType[];
  /** exclude the entity the query is made for */
  excludeFacilityId?: string | null;
  excludeProjectId?: string | null;
  limitPerType?: number;
}

export async function nearby(o: NearbyOptions): Promise<NearbyInfrastructure> {
  const sql = pg();
  const radiusKm = Math.min(NEARBY_MAX_RADIUS_KM, Math.max(0.1, o.radiusKm ?? 25));
  const types = new Set<NearbyType>(o.types?.length ? o.types : NEARBY_TYPES);
  const limit = Math.min(500, Math.max(1, o.limitPerType ?? 50));
  const { lat, lng } = o;
  const fDist = haversineFor(sql, lat, lng, sql`f.lat`, sql`f.lng`);
  const pDist = haversineFor(sql, lat, lng, sql`p.lat`, sql`p.lng`);
  const rDist = haversineFor(sql, lat, lng, sql`r.lat`, sql`r.lng`);
  const mDist = haversineFor(sql, lat, lng, sql`m.lat`, sql`m.lng`);
  const xLat: Fragment = sql`coalesce(xm.lat, xm2.lat)`;
  const xLng: Fragment = sql`coalesce(xm.lng, xm2.lng)`;
  const xDist = haversineFor(sql, lat, lng, xLat, xLng);
  const none = Promise.resolve([] as Row[]);
  const [facs, prjs, ixps, crs, mets] = await Promise.all([
    types.has("facilities")
      ? sql<Row[]>`select ${facilitySummaryCols(sql)}, ${fDist} as distance_km from facilities f ${facilityJoins(sql)}
          where f.merged_into is null and f.lat is not null and f.lng is not null ${o.excludeFacilityId ? sql`and f.id <> ${o.excludeFacilityId}` : sql``}
            and ${withinBbox(sql, lat, lng, radiusKm, sql`f.lat`, sql`f.lng`)} and ${fDist} <= ${radiusKm}
          order by ${fDist} asc limit ${limit}`
      : none,
    types.has("projects")
      ? sql<Row[]>`select ${projectSummaryCols(sql)}, ${pDist} as distance_km from projects p ${projectJoins(sql)}
          where ${projectLive(sql)} and p.lat is not null and p.lng is not null ${o.excludeProjectId ? sql`and p.id <> ${o.excludeProjectId}` : sql``}
            and ${withinBbox(sql, lat, lng, radiusKm, sql`p.lat`, sql`p.lng`)} and ${pDist} <= ${radiusKm}
          order by ${pDist} asc limit ${limit}`
      : none,
    types.has("ixps")
      ? 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,
            ${xLat} as lat, ${xLng} as lng, ${xDist} as distance_km, (xm.id is not null) as via_metro,
            coalesce(xm.id, xm2.id) as met_id, coalesce(xm.slug, xm2.slug) as met_slug, coalesce(xm.name, xm2.name) as met_name
          from ixps x
          left join metros xm on xm.id = x.metro_id
          left join lateral (select m2.id, m2.slug, m2.name, m2.lat, m2.lng from metros m2 where x.metro_id is null and x.country_iso2 = m2.country_iso2 and x.city is not null and (lower(m2.name) = lower(x.city) or exists (select 1 from unnest(m2.aliases) a where lower(a) = lower(x.city))) limit 1) xm2 on true
          where ${xLat} is not null and ${withinBbox(sql, lat, lng, radiusKm, xLat, xLng)} and ${xDist} <= ${radiusKm}
          order by ${xDist} asc limit ${limit}`
      : none,
    types.has("cloud_regions")
      ? sql<Row[]>`select ${cloudRegionCols(sql)}, ${rDist} as distance_km from cloud_regions r join operators pr on pr.id = r.provider_id
          where r.lat is not null and r.lng is not null and ${withinBbox(sql, lat, lng, radiusKm, sql`r.lat`, sql`r.lng`)} and ${rDist} <= ${radiusKm}
          order by ${rDist} asc limit ${limit}`
      : none,
    types.has("metros")
      ? sql<Row[]>`select m.id, m.slug, m.name, m.country_iso2, ${mDist} as distance_km from metros m
          where ${withinBbox(sql, lat, lng, radiusKm, sql`m.lat`, sql`m.lng`)} and ${mDist} <= ${radiusKm}
          order by ${mDist} asc limit ${limit}`
      : none,
  ]);
  const d = (r: Row) => round(num(r.distance_km), 2) ?? 0;
  const ixpViaMetro = ixps.filter((r) => r.via_metro === true).length;
  return {
    center: { lat, lng },
    radiusKm,
    facilities: facs.map((r) => ({ ...facilitySummary(r), distanceKm: d(r) })),
    projects: prjs.map((r) => ({ ...projectSummary(r), distanceKm: d(r) })),
    ixps: ixps.map((r) => ({ ...ixpSummary(r), lat: num(r.lat), lng: num(r.lng), distanceKm: d(r) })),
    cloudRegions: crs.map((r) => ({ ...cloudRegionSummary(r), distanceKm: d(r) })),
    metros: mets.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), countryIso2: reqStr(r.country_iso2), distanceKm: d(r) })),
    landingStations: [],
    substations: [],
    powerPlants: [],
    note: `${NEARBY_NOTE}${ixpViaMetro ? ` ${ixpViaMetro} IXP${ixpViaMetro > 1 ? "s" : ""} placed at metro coordinates (no published IXP location).` : ""}`,
  };
}

export function parseNearbyTypes(v: string | undefined): NearbyType[] | undefined {
  if (!v) return undefined;
  const out = v.split(",").map((s) => s.trim().toLowerCase().replace(/-/g, "_")).filter((s): s is NearbyType => (NEARBY_TYPES as readonly string[]).includes(s));
  return out.length ? out : undefined;
}