import type { FacilitySummary, IxpDetail as IxpDetailContract, IxpSummary } from "@dci/core";
import { pg, facilityJoins, facilitySummaryCols, haversineExpr, withinBbox, likePattern } from "../lib/sql.js";
import { num, record, reqIso, reqStr, round, str, type Row } from "../lib/rows.js";
import { facilitySummary, ixpSummary } from "../lib/dto.js";
import { findBySlugOrId } from "../lib/resolve.js";
import { eventsForEntity } from "./events.js";
import { provenanceFor } from "./facilities.js";
import { buildSourceHistory, documentVersionsFor } from "../lib/source-history.js";

/** IXP columns joined with the metro (ixps have no coordinates of their own — the metro reference point stands in). */
const 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";

export async function listIxps(f: { country?: string; metroId?: string; q?: string }): Promise<IxpSummary[]> {
  const sql = pg();
  const rows = await 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 ${f.country ? sql`x.country_iso2 = ${f.country.toUpperCase()}` : sql`true`}
      and ${f.metroId ? sql`x.metro_id = ${f.metroId}` : sql`true`}
      and ${f.q ? sql`(x.name ilike ${likePattern(f.q)} or x.name_long ilike ${likePattern(f.q)})` : sql`true`}
    order by x.network_count desc nulls last, x.name limit 2000`;
  return rows.map(ixpSummary);
}

/** Contract IxpDetail + the extra fields the web already reads. */
export interface IxpDetail extends IxpDetailContract {
  countryName: string | null;
  regionContinent: string | null;
  updatedAt: string;
}

export 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).";

export async function getIxp(idOrSlug: string): Promise<IxpDetail | null> {
  const sql = pg();
  const row = await findBySlugOrId("ixps", idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const metroId = str(row.metro_id);
  const [main, facs, metroRows, countryRows, provenance, events, versions] = await Promise.all([
    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}`,
    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`,
    metroId ? sql<Row[]>`select id, slug, name, lat, lng from metros where id = ${metroId}` : Promise.resolve([] as Row[]),
    row.country_iso2 ? sql<Row[]>`select name from countries where iso2 = ${String(row.country_iso2)}` : Promise.resolve([] as Row[]),
    provenanceFor("ixp", id),
    eventsForEntity("ixp", id, 50),
    documentVersionsFor("ixp", id),
  ]);
  const metro = metroRows[0] ?? null;
  const lat = metro ? num(metro.lat) : null, lng = metro ? num(metro.lng) : null;
  const facilities = facs.map(facilitySummary);
  const linkedIds = facilities.map((f) => f.id);
  let nearbyFacilities: Array<FacilitySummary & { distanceKm: number }> = [];
  if (lat != null && lng != null) {
    const dist = haversineExpr(sql, lat, lng);
    const rows = await sql<Row[]>`select ${facilitySummaryCols(sql)}, ${dist} as distance_km from facilities f ${facilityJoins(sql)}
      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``}
      order by ${dist} asc limit 20`;
    nearbyFacilities = rows.map((r) => ({ ...facilitySummary(r), distanceKm: round(num(r.distance_km), 2) ?? 0 }));
  }
  const opMap = new Map<string, { id: string; slug: string; name: string; facilityCount: number }>();
  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); }
  return {
    ...ixpSummary(main[0] ?? { ...row, facility_count: facs.length }),
    metro: metro ? { id: reqStr(metro.id), slug: reqStr(metro.slug), name: reqStr(metro.name) } : null,
    lat,
    lng,
    facilities,
    operators: [...opMap.values()].sort((a, b) => b.facilityCount - a.facilityCount || a.name.localeCompare(b.name)),
    nearbyFacilities,
    trafficNote: metro ? IXP_COORDS_NOTE : null,
    externalIds: record(row.external_ids),
    sourceHistory: buildSourceHistory(provenance, events, versions),
    countryName: countryRows[0] ? String(countryRows[0].name) : null,
    regionContinent: str(row.region_continent),
    updatedAt: reqIso(row.updated_at),
  };
}