/**
 * Load US ZIP / city / state into MySQL from GeoNames postal data.
 * Source: https://download.geonames.org/export/zip/US.zip
 */
import fs from "node:fs";
import path from "node:path";
import { execSync } from "node:child_process";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();
const cacheDir = path.join(process.cwd(), "prisma", "data");
const zipPath = path.join(cacheDir, "US.zip");
const txtPath = path.join(cacheDir, "US.txt");
const url = "https://download.geonames.org/export/zip/US.zip";

async function ensureFile() {
  fs.mkdirSync(cacheDir, { recursive: true });
  if (fs.existsSync(txtPath)) return;
  if (!fs.existsSync(zipPath)) {
    console.log("Downloading GeoNames US postal codes…");
    const res = await fetch(url);
    if (!res.ok) throw new Error(`Download failed: ${res.status} ${res.statusText}`);
    const buf = Buffer.from(await res.arrayBuffer());
    fs.writeFileSync(zipPath, buf);
  }
  console.log("Extracting US.txt…");
  execSync(
    `powershell -NoProfile -Command "Expand-Archive -Force -LiteralPath '${zipPath.replace(/'/g, "''")}' -DestinationPath '${cacheDir.replace(/'/g, "''")}'"`,
    { stdio: "inherit" }
  );
  if (!fs.existsSync(txtPath)) {
    throw new Error("US.txt missing after extract");
  }
}

function parseRows() {
  const text = fs.readFileSync(txtPath, "utf8");
  const seen = new Set<string>();
  const rows: {
    zip: string;
    city: string;
    stateCode: string;
    stateName: string;
    county: string | null;
    latitude: number | null;
    longitude: number | null;
  }[] = [];
  for (const line of text.split(/\r?\n/)) {
    if (!line.trim()) continue;
    const c = line.split("\t");
    const zip = (c[1] || "").trim();
    const city = (c[2] || "").trim();
    const stateName = (c[3] || "").trim();
    const stateCode = (c[4] || "").trim().toUpperCase();
    const county = (c[5] || "").trim() || null;
    const latitude = c[9] ? Number(c[9]) : null;
    const longitude = c[10] ? Number(c[10]) : null;
    if (!zip || !city || !stateCode || stateCode.length !== 2) continue;
    const key = `${zip}|${city.toLowerCase()}|${stateCode}`;
    if (seen.has(key)) continue;
    seen.add(key);
    rows.push({
      zip,
      city,
      stateCode,
      stateName,
      county,
      latitude: Number.isFinite(latitude) ? latitude : null,
      longitude: Number.isFinite(longitude) ? longitude : null,
    });
  }
  return rows;
}

async function main() {
  await ensureFile();
  const rows = parseRows();
  console.log(`Parsed ${rows.length} unique ZIP/city/state rows`);
  console.log("Clearing us_zip_codes…");
  await prisma.$executeRawUnsafe("DELETE FROM us_zip_codes");
  const batch = 400;
  for (let i = 0; i < rows.length; i += batch) {
    const chunk = rows.slice(i, i + batch);
    const values = chunk
      .map((r) => {
        const esc = (s: string | null) =>
          s == null ? "NULL" : `'${s.replace(/\\/g, "\\\\").replace(/'/g, "''")}'`;
        return `(${esc(r.zip)}, ${esc(r.city)}, ${esc(r.stateCode)}, ${esc(r.stateName)}, ${esc(r.county)}, ${
          r.latitude ?? "NULL"
        }, ${r.longitude ?? "NULL"})`;
      })
      .join(",");
    await prisma.$executeRawUnsafe(
      `INSERT INTO us_zip_codes (zip, city, stateCode, stateName, county, latitude, longitude) VALUES ${values}`
    );
    console.log(`Inserted ${Math.min(i + batch, rows.length)} / ${rows.length}`);
  }
  const countRows = await prisma.$queryRawUnsafe<[{ c: bigint }]>("SELECT COUNT(*) AS c FROM us_zip_codes");
  const stateRows = await prisma.$queryRawUnsafe<[{ c: bigint }]>(
    "SELECT COUNT(DISTINCT stateCode) AS c FROM us_zip_codes"
  );
  console.log(`Done. ${countRows[0].c} rows, ${stateRows[0].c} state codes.`);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });