/** Cloud regions: provider operator (hyperscaler/cloud) + unique (provider, code). Launch/announce/update events. */
import { sql } from "@dci/db";
import { cleanText, newId, slugify, type NormalizedCloudRegion } from "@dci/core";
import { addRef, bump, safeCountry, uniqueSlug, type IngestContext, type Tx } from "./common.js";
import { recordEvent } from "./events.js";
import { validGeo } from "./geo.js";
import { upsertKey } from "./keys.js";
import { assignMetro } from "./metros.js";
import { resolveOperator } from "./operators.js";
import { writeProvenance } from "./provenance.js";

export async function ingestCloudRegion(tx: Tx, ctx: IngestContext, cr: NormalizedCloudRegion): Promise<void> {
  const code = cleanText(cr.code);
  const name = cleanText(cr.name) ?? code;
  if (!code || !name) throw new Error(`cloud_region ${cr.key}: code and name are required`);
  const url = cr.provenance.url || cr.sourceUrl || ctx.doc?.url || "";
  if (!url) throw new Error(`cloud_region ${cr.key}: provenance url is required`);
  const provider = await resolveOperator(tx, ctx, { name: cr.providerName, roleHint: "cloud" });
  const country = await safeCountry(tx, ctx, cr.countryIso2);
  const geo = validGeo(cr.geo) ? cr.geo : null;
  const metro = await assignMetro(tx, { lat: geo?.lat, lng: geo?.lng, city: cr.city, countryIso2: country });
  const status = cr.status ?? "operational";

  const existing = (await tx.execute(sql`select * from cloud_regions where provider_id = ${provider.id} and lower(code) = lower(${code}) limit 1`))[0] ?? null;
  const id = existing ? String(existing.id) : newId("cloud_region");
  if (!existing) {
    const providerSlug = (await tx.execute(sql`select slug from operators where id = ${provider.id}`))[0]?.slug;
    const slug = await uniqueSlug(tx, "cloud_regions", `${String(providerSlug ?? slugify(provider.name))}-${code}`);
    await tx.execute(sql`insert into cloud_regions (id, slug, provider_id, code, name, city, region_name, country_iso2, metro_id, lat, lng, geo_precision, availability_zones, launched_on, status, is_sovereign, source_url, external_ids)
      values (${id}, ${slug}, ${provider.id}, ${code}, ${name}, ${cleanText(cr.city)}, ${cleanText(cr.regionName)}, ${country ?? metro.countryIso2}, ${metro.metroId}, ${geo?.lat ?? null}, ${geo?.lng ?? null}, ${geo?.precision ?? "city"},
        ${cr.availabilityZones ?? null}, ${cr.launchedOn ?? null}, ${status}, ${!!cr.isSovereign}, ${cr.sourceUrl ?? null}, ${JSON.stringify(cr.externalIds ?? {})}::jsonb)`);
    ctx.stats.created++;
    await recordEvent(tx, ctx, {
      entityType: "cloud_region",
      entityId: id,
      eventType: status === "announced" ? "cloud_region_announced" : "cloud_region_launched",
      title: status === "announced" ? `${provider.name} announces ${name} (${code}) cloud region` : `${provider.name} ${name} (${code}) cloud region indexed`,
      summary: [cr.city ? `${cr.city}${country ? `, ${country}` : ""}.` : null, cr.availabilityZones ? `${cr.availabilityZones} availability zones.` : null, cr.launchedOn ? `Launched ${cr.launchedOn}.` : null].filter(Boolean).join(" ") || null,
      newValue: { code, name, status },
      significance: status === "announced" ? 70 : 45,
      confidence: cr.provenance.confidence,
      effectiveDate: cr.launchedOn ?? null,
      url,
      countryIso2: country ?? metro.countryIso2,
      operatorId: provider.id,
      metroId: metro.metroId,
    });
  } else {
    const prevStatus = String(existing.status);
    const changed = prevStatus !== status || (existing.availability_zones ?? null) !== (cr.availabilityZones ?? null) || (existing.launched_on ?? null) !== (cr.launchedOn ?? null) || String(existing.name) !== name;
    await tx.execute(sql`update cloud_regions set name = ${name}, city = coalesce(${cleanText(cr.city)}, city), region_name = coalesce(${cleanText(cr.regionName)}, region_name), country_iso2 = coalesce(${country}, country_iso2),
        metro_id = coalesce(${metro.metroId}, metro_id), lat = coalesce(${geo?.lat ?? null}, lat), lng = coalesce(${geo?.lng ?? null}, lng), geo_precision = case when ${geo != null} then ${geo?.precision ?? "city"} else geo_precision end,
        availability_zones = coalesce(${cr.availabilityZones ?? null}, availability_zones), launched_on = coalesce(${cr.launchedOn ?? null}, launched_on), status = ${status}, is_sovereign = is_sovereign or ${!!cr.isSovereign},
        source_url = coalesce(${cr.sourceUrl ?? null}, source_url), external_ids = external_ids || ${JSON.stringify(cr.externalIds ?? {})}::jsonb, updated_at = now()
      where id = ${id}`);
    if (changed) ctx.stats.updated++;
    else ctx.stats.unchanged++;
    if (prevStatus !== status) {
      await recordEvent(tx, ctx, {
        entityType: "cloud_region",
        entityId: id,
        eventType: status === "operational" && prevStatus === "announced" ? "cloud_region_launched" : "cloud_region_updated",
        title: status === "operational" && prevStatus === "announced" ? `${provider.name} ${name} (${code}) is now live` : `${provider.name} ${name} (${code}): status ${prevStatus} → ${status}`,
        summary: `Status changed from ${prevStatus} to ${status}.`,
        oldValue: prevStatus,
        newValue: status,
        significance: status === "operational" ? 80 : 60,
        confidence: cr.provenance.confidence,
        effectiveDate: cr.launchedOn ?? null,
        url,
        countryIso2: country ?? (existing.country_iso2 as string | null),
        operatorId: provider.id,
        metroId: metro.metroId ?? (existing.metro_id as string | null),
      });
    } else if (changed) {
      await recordEvent(tx, ctx, {
        entityType: "cloud_region",
        entityId: id,
        eventType: "cloud_region_updated",
        title: `${provider.name} ${name} (${code}) updated`,
        summary: `Availability zones: ${existing.availability_zones ?? "—"} → ${cr.availabilityZones ?? "—"}; launch: ${existing.launched_on ?? "—"} → ${cr.launchedOn ?? "—"}.`,
        oldValue: { availabilityZones: existing.availability_zones ?? null, launchedOn: existing.launched_on ?? null, name: existing.name },
        newValue: { availabilityZones: cr.availabilityZones ?? null, launchedOn: cr.launchedOn ?? null, name },
        significance: 25,
        confidence: cr.provenance.confidence,
        url,
        countryIso2: country ?? (existing.country_iso2 as string | null),
        operatorId: provider.id,
        metroId: metro.metroId ?? (existing.metro_id as string | null),
      });
    }
  }
  await upsertKey(tx, ctx, cr.key, "cloud_region", id);
  addRef(ctx, "cloud_region", id);
  bump(ctx, "cloud_region");
  await writeProvenance(
    tx,
    ctx,
    "cloud_region",
    id,
    [
      { field: "code", value: code },
      { field: "name", value: name },
      { field: "providerName", value: cr.providerName },
      { field: "city", value: cr.city ?? null },
      { field: "countryIso2", value: cr.countryIso2 ?? null },
      { field: "geo", value: geo ? { lat: geo.lat, lng: geo.lng, precision: geo.precision } : null },
      { field: "availabilityZones", value: cr.availabilityZones ?? null },
      { field: "launchedOn", value: cr.launchedOn ?? null },
      { field: "status", value: status },
      { field: "isSovereign", value: cr.isSovereign ?? null },
    ].map((f) => ({ ...f, provenance: cr.provenance })),
    cr.key,
  );
}