/**
 * One-time backfill: geocode existing profiles that have location data but no coordinates.
 *
 * Usage: DATABASE_URL=... GEONAMES_USERNAME=... npx tsx scripts/backfill-coordinates.ts
 *
 * GeoNames free tier: ~1000 requests/hour. Script adds a 4s delay between API calls.
 */
import { drizzle } from 'drizzle-orm/node-postgres';
import pg from 'pg';
import { profiles } from '../src/db/schema/index.js';
import { sql } from 'drizzle-orm';
import { geocodeCity } from '../src/lib/geocode.js';
import { resolveCountryCode } from '../src/lib/country-centroids.js';

const DATABASE_URL = process.env.DATABASE_URL;
const GEONAMES_USERNAME = process.env.GEONAMES_USERNAME ?? 'gxjansen';

if (!DATABASE_URL) {
  console.error('DATABASE_URL is required');
  process.exit(1);
}

const pool = new pg.Pool({ connectionString: DATABASE_URL });
const db = drizzle(pool);

async function main() {
  const rows = await db
    .select({
      did: profiles.did,
      locationCity: profiles.locationCity,
      locationCountry: profiles.locationCountry,
      countryCode: profiles.countryCode,
    })
    .from(profiles)
    .where(
      sql`${profiles.latitude} IS NULL AND (${profiles.countryCode} IS NOT NULL OR ${profiles.locationCountry} IS NOT NULL)`,
    );

  console.log(`Found ${rows.length} profiles to backfill`);

  let updated = 0;
  let skipped = 0;

  for (const row of rows) {
    const code = resolveCountryCode(row.countryCode, row.locationCountry);
    if (!code) {
      skipped++;
      continue;
    }

    const city = row.locationCity?.trim() ?? '';
    const coords = await geocodeCity(city, code, GEONAMES_USERNAME);

    if (coords) {
      await db
        .update(profiles)
        .set({ latitude: coords.lat, longitude: coords.lng })
        .where(sql`${profiles.did} = ${row.did}`);
      updated++;
      console.log(
        `  ${row.did}: ${city || '(country only)'}, ${code} -> ${coords.lat}, ${coords.lng}`,
      );
    } else {
      skipped++;
    }

    // Rate limit: only delay for city geocodes (API calls), not country-only (local)
    if (city) {
      await new Promise((r) => setTimeout(r, 4000));
    }
  }

  console.log(`Done: ${updated} updated, ${skipped} skipped`);
  await pool.end();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});