import type { CloudRegionDetail as CloudRegionDetailContract, CloudRegionSummary, FacilitySummary } from "@dci/core";
import { pg, andAll, cloudRegionCols, facilityJoins, facilitySummaryCols, mwExpr, type Fragment } from "../lib/sql.js";
import { record, reqIso, str, type Row } from "../lib/rows.js";
import { cloudRegionSummary, facilitySummary } from "../lib/dto.js";
import { findBySlugOrId } from "../lib/resolve.js";
import { eventsForEntity } from "./events.js";
import { provenanceFor } from "./facilities.js";

export async function listCloudRegions(f: { provider?: string; country?: string; metroId?: string; status?: string }): Promise<CloudRegionSummary[]> {
  const sql = pg();
  const c: Fragment[] = [sql`true`];
  if (f.provider) c.push(sql`(pr.slug = ${f.provider} or pr.id = ${f.provider})`);
  if (f.country) c.push(sql`r.country_iso2 = ${f.country.toUpperCase()}`);
  if (f.metroId) c.push(sql`r.metro_id = ${f.metroId}`);
  if (f.status) c.push(sql`r.status = ${f.status}`);
  const rows = await sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where ${andAll(sql, c)} order by pr.name, r.country_iso2, r.code limit 2000`;
  return rows.map(cloudRegionSummary);
}

/** Contract CloudRegionDetail + the extra fields the web already reads. */
export interface CloudRegionDetail extends CloudRegionDetailContract {
  regionName: string | null;
  countryName: string | null;
  facilitiesInMetro: FacilitySummary[];
  updatedAt: string;
}

export const MARKET_ASSOCIATION_NOTE = "Region associated with market, not hosted at a specific facility";

export async function getCloudRegion(idOrSlug: string): Promise<CloudRegionDetail | null> {
  const sql = pg();
  const row = await findBySlugOrId("cloud_regions", idOrSlug);
  if (!row) return null;
  const id = String(row.id);
  const metroId = str(row.metro_id);
  const providerId = String(row.provider_id);
  const [main, siblings, facs, hosts, metroRows, countryRows, events, provenance] = await Promise.all([
    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.id = ${id}`,
    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.provider_id = ${providerId} and r.id <> ${id} and r.country_iso2 is not distinct from ${str(row.country_iso2)} order by r.code limit 12`,
    metroId
      ? sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and f.metro_id = ${metroId} order by ${mwExpr(sql)} desc nulls last, f.name limit 12`
      : Promise.resolve([] as Row[]),
    // explicit hosting link only: a cloud tenancy of this provider at a facility in the region's metro / country
    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} join facility_tenants t on t.facility_id = f.id and t.role = 'cloud' and t.operator_id = ${providerId}
      where f.merged_into is null and ${metroId ? sql`f.metro_id = ${metroId}` : row.country_iso2 ? sql`f.country_iso2 = ${String(row.country_iso2)}` : sql`false`} order by f.name limit 50`,
    metroId ? sql<Row[]>`select id, slug, name 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[]),
    eventsForEntity("cloud_region", id, 50),
    provenanceFor("cloud_region", id),
  ]);
  const base = cloudRegionSummary(main[0] ?? row);
  const hostFacilities = hosts.map(facilitySummary);
  const marketFacilities = facs.map(facilitySummary);
  const announcedProv = provenance.find((p) => p.field === "announcedOn" && typeof p.value === "string");
  const announcedOn = announcedProv ? String(announcedProv.value) : base.status === "announced" && base.launchedOn ? base.launchedOn : null;
  return {
    ...base,
    metro: metroRows[0] ? { id: String(metroRows[0].id), slug: String(metroRows[0].slug), name: String(metroRows[0].name) } : null,
    hostFacilities,
    marketFacilities,
    siblingRegions: siblings.map(cloudRegionSummary),
    announcedOn,
    events,
    provenance,
    externalIds: record(row.external_ids),
    note: hostFacilities.length ? `Hosting publicly verified for ${hostFacilities.length} facilit${hostFacilities.length > 1 ? "ies" : "y"} (cloud tenancy recorded at the facility); marketFacilities lists the other facilities of the market.` : MARKET_ASSOCIATION_NOTE,
    regionName: str(row.region_name),
    countryName: countryRows[0] ? String(countryRows[0].name) : null,
    facilitiesInMetro: marketFacilities,
    updatedAt: reqIso(row.updated_at),
  };
}