/** Idempotent reference-data seeding (countries + metros). Safe to run on every deploy. */
import { sql, textArray, type Db } from "@dci/db";
import { COUNTRY_SEEDS } from "./countries.js";
import { METRO_SEEDS } from "./metros.js";

export interface SeedResult {
  countries: number;
  metros: number;
}

export async function seedCountries(db: Db): Promise<number> {
  let n = 0;
  await db.transaction(async (tx) => {
    for (const c of COUNTRY_SEEDS) {
      await tx.execute(sql`insert into countries (iso2, iso3, slug, name, region, subregion, lat, lng)
        values (${c.iso2}, ${c.iso3}, ${c.slug}, ${c.name}, ${c.region}, ${c.subregion}, ${c.lat}, ${c.lng})
        on conflict (iso2) do update set iso3 = excluded.iso3, slug = excluded.slug, name = excluded.name, region = excluded.region, subregion = excluded.subregion,
          lat = coalesce(countries.lat, excluded.lat), lng = coalesce(countries.lng, excluded.lng), updated_at = now()`);
      n++;
    }
  });
  return n;
}

export async function seedMetros(db: Db): Promise<number> {
  let n = 0;
  await db.transaction(async (tx) => {
    for (const m of METRO_SEEDS) {
      await tx.execute(sql`insert into metros (id, slug, name, country_iso2, region_name, lat, lng, radius_km, aliases, description)
        values (${m.id}, ${m.slug}, ${m.name}, ${m.countryIso2}, ${m.regionName}, ${m.lat}, ${m.lng}, ${m.radiusKm}, ${textArray(m.aliases)}::text[], ${m.description})
        on conflict (id) do update set slug = excluded.slug, name = excluded.name, country_iso2 = excluded.country_iso2, region_name = excluded.region_name, lat = excluded.lat, lng = excluded.lng,
          radius_km = excluded.radius_km, aliases = excluded.aliases, description = coalesce(metros.description, excluded.description), updated_at = now()`);
      n++;
    }
  });
  return n;
}

export async function seedReferenceData(db: Db): Promise<SeedResult> {
  const countries = await seedCountries(db);
  const metros = await seedMetros(db);
  return { countries, metros };
}

export { COUNTRY_SEEDS, METRO_SEEDS };