/**
 * Backfill facilities.country_iso2 from coordinates (offline Natural Earth polygons) for rows ingested before
 * ingest/country-lookup.ts existed, then assign metros where missing. Idempotent; writes provenance rows.
 *   DATABASE_URL=… node node_modules/tsx/dist/cli.mjs scripts/backfill-country.ts [--dry-run]
 */
import { getSql, closeDb } from "@dci/db";
import { stableId } from "@dci/core";
import { countryFromPoint } from "../apps/worker/src/ingest/country-lookup.js";

const dry = process.argv.includes("--dry-run");
const sql = getSql();
const rows = await sql<{ id: string; lat: number; lng: number; slug: string }[]>`select id, lat, lng, slug from facilities where country_iso2 is null and lat is not null and merged_into is null`;
const known = new Set((await sql<{ iso2: string }[]>`select iso2 from countries`).map((r) => r.iso2));
let fixed = 0, unresolved = 0;
const byCountry = new Map<string, number>();
for (const r of rows) {
  const iso = countryFromPoint(Number(r.lat), Number(r.lng));
  if (!iso || !known.has(iso)) { unresolved++; continue; }
  byCountry.set(iso, (byCountry.get(iso) ?? 0) + 1);
  if (!dry) {
    await sql`update facilities set country_iso2 = ${iso}, updated_at = now() where id = ${r.id}`;
    await sql`insert into provenance (id, entity_type, entity_id, field, value, source_id, connector_id, url, confidence, is_estimate, method, note)
      values (${stableId("provenance", `facility|${r.id}|country_iso2|derived-geo`)}, 'facility', ${r.id}, 'country_iso2', ${sql.json(iso)}, 'src_derived', 'derived', ${"https://www.datacenterindex.io/methodology#geolocation"}, 'moderate', false, 'derived:point-in-country', 'Natural Earth 1:50m admin-0 polygons (public domain)')
      on conflict do nothing`;
  }
  fixed++;
}
if (!dry) {
  await sql`insert into sources (id, connector_id, name, domain, kind, priority, license, attribution) values ('src_derived', 'derived', 'Derived (DataCenterIndex)', 'www.datacenterindex.io', 'registry', 4, 'Derived from indexed coordinates + Natural Earth (public domain)', 'DataCenterIndex') on conflict do nothing`;
  // metro assignment for rows without metro: nearest metro within its radius
  const m = await sql`update facilities f set metro_id = (
      select mm.id from metros mm where mm.country_iso2 = f.country_iso2
        and 6371.0088 * 2 * asin(sqrt(power(sin(radians(mm.lat - f.lat) / 2), 2) + cos(radians(f.lat)) * cos(radians(mm.lat)) * power(sin(radians(mm.lng - f.lng) / 2), 2))) <= mm.radius_km
      order by 6371.0088 * 2 * asin(sqrt(power(sin(radians(mm.lat - f.lat) / 2), 2) + cos(radians(f.lat)) * cos(radians(mm.lat)) * power(sin(radians(mm.lng - f.lng) / 2), 2))) limit 1)
    where f.metro_id is null and f.lat is not null and f.country_iso2 is not null and f.merged_into is null
      and exists (select 1 from metros mm where mm.country_iso2 = f.country_iso2)`;
  console.log(`metros assigned: ${m.count}`);
}
console.log(`candidates ${rows.length} · fixed ${fixed} · unresolved ${unresolved}${dry ? " (dry run)" : ""}`);
console.log([...byCountry.entries()].sort((a, b) => b[1] - a[1]).slice(0, 12).map(([k, v]) => `${k}:${v}`).join(" "));
await closeDb();