// Backfill latitude/longitude for existing properties that predate geocoding.
//
//   node scripts/backfill-geocode.mjs          # dev   (uses DATABASE_URL)
//   node scripts/backfill-geocode.mjs --prod   # prod  (uses PROD_DATABASE_URL)
//
// Geocodes every property missing coordinates via OpenStreetMap Nominatim,
// throttled to 1 request/second per Nominatim's usage policy. Safe to re-run —
// it only touches rows where latitude or longitude is null.
import { readFileSync } from "node:fs"
import { config } from "dotenv"
import pg from "pg"

config({ path: ".env.local", quiet: true })

const useProd = process.argv.includes("--prod")
const url = useProd ? process.env.PROD_DATABASE_URL : process.env.DATABASE_URL
if (!url) {
  console.error(`[backfill-geocode] ${useProd ? "PROD_DATABASE_URL" : "DATABASE_URL"} is not set.`)
  process.exit(1)
}

function resolveSsl() {
  if (useProd) {
    try {
      return { rejectUnauthorized: true, ca: readFileSync("ca-certificate.crt", "utf8") }
    } catch {
      return { rejectUnauthorized: false }
    }
  }
  return process.env.DATABASE_SSL === "disable" ? false : { rejectUnauthorized: false }
}

const USER_AGENT =
  process.env.GEOCODER_USER_AGENT ||
  `PropertyManagementNetwork/1.0 (${process.env.NEXT_PUBLIC_APP_URL || "https://propertymanagement.network"})`

const sleep = (ms) => new Promise((r) => setTimeout(r, ms))

async function geocode(a) {
  if (!a.address_line1 && !a.city && !a.postal_code) return null
  const street = [a.address_line1, a.address_line2].filter(Boolean).join(" ").trim()
  const params = new URLSearchParams({ format: "jsonv2", limit: "1" })
  if (street) params.set("street", street)
  if (a.city) params.set("city", a.city)
  if (a.state) params.set("state", a.state)
  if (a.postal_code) params.set("postalcode", a.postal_code)
  params.set("country", a.country || "US")
  try {
    const res = await fetch(`https://nominatim.openstreetmap.org/search?${params.toString()}`, {
      headers: { "User-Agent": USER_AGENT, Accept: "application/json" },
    })
    if (!res.ok) return null
    const json = await res.json()
    const first = Array.isArray(json) ? json[0] : null
    if (!first) return null
    const lat = Number(first.lat)
    const lon = Number(first.lon)
    return Number.isFinite(lat) && Number.isFinite(lon) ? { lat, lon } : null
  } catch {
    return null
  }
}

const pool = new pg.Pool({ connectionString: url, ssl: resolveSsl() })

const { rows } = await pool.query(
  `select id, name, address_line1, address_line2, city, state, postal_code, country
     from properties
    where latitude is null or longitude is null`
)

console.log(`[backfill-geocode] ${rows.length} propert${rows.length === 1 ? "y" : "ies"} to geocode (${useProd ? "PROD" : "dev"})`)

let ok = 0
for (const r of rows) {
  const g = await geocode(r)
  const label = r.name || r.address_line1 || r.city || r.id
  if (g) {
    await pool.query(`update properties set latitude = $1, longitude = $2 where id = $3`, [g.lat, g.lon, r.id])
    ok++
    console.log(`  ✓ ${label} → ${g.lat.toFixed(4)}, ${g.lon.toFixed(4)}`)
  } else {
    console.log(`  · ${label}: no match`)
  }
  await sleep(1100) // Nominatim: max 1 request/second
}

console.log(`[backfill-geocode] done — ${ok}/${rows.length} geocoded`)
await pool.end()