index.js

const fs = require("fs");
const https = require("https");
const path = require("path");
const { parse } = require("csv-parse/sync");
const { v7: uuidv7 } = require("uuid");

const CSV_FILE = "cities.csv";
const SQL_FILE = "cities.sql";
const CSV_URL =
  "https://www.istat.it/storage/codici-unita-amministrative/Elenco-comuni-italiani.csv";

// Helper to download a file
function downloadFile(url, dest) {
  return new Promise((resolve, reject) => {
    const file = fs.createWriteStream(dest);
    https
      .get(url, (response) => {
        if (response.statusCode !== 200) {
          reject(new Error(`Failed to get '${url}' (${response.statusCode})`));
          return;
        }
        response.pipe(file);
        file.on("finish", () => {
          file.close(resolve);
        });
      })
      .on("error", (err) => {
        fs.unlink(dest, () => reject(err));
      });
  });
}

async function main() {
  // Download CSV if not exists
  if (!fs.existsSync(CSV_FILE)) {
    console.log(`Downloading ${CSV_FILE}...`);
    await downloadFile(CSV_URL, CSV_FILE);
    console.log("Download completed.");
  }

  // Read CSV (latin1 encoding)
  const csvContent = fs.readFileSync(CSV_FILE, { encoding: "latin1" });

  // Parse CSV
  const records = parse(csvContent, {
    delimiter: ";",
    skip_empty_lines: true,
  });

  // Remove header
  records.shift();

  // Prepare SQL
  const values = [];
  for (const row of records) {
    // Defensive: skip rows that are too short
    if (row.length < 12) continue;
    const id = uuidv7();
    const city = row[6].replace(/'/g, "''");
    const province = row[11].replace(/'/g, "''");
    const region = row[10].replace(/'/g, "''");
    values.push(`('${id}', '${city}', '${province}', '${region}')`);
  }

  const sqlHeader = "INSERT INTO cities (id, city, province, region) VALUES\n";
  const sqlBody = values.join(",\n") + ";\n";

  fs.writeFileSync(SQL_FILE, sqlHeader + sqlBody, { encoding: "utf-8" });
  console.log(`Created ${SQL_FILE} with ${values.length} records.`);
}

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