/**
 * Pobla la tabla municipalities_es con todos los municipios de España
 * Fuente: OpenDataSoft / GeoNames (datos públicos, sin API key)
 * Uso: npx ts-node src/scripts/populateMunicipalities.ts
 */
import "dotenv/config";
import axios from "axios";
import mysql from "mysql2/promise";

// Mapeo de codes GeoNames admin1 → Comunidad Autónoma española
const ADMIN1_MAP: Record<string, string> = {
    "07": "Islas Baleares",
    "27": "La Rioja",
    "29": "Comunidad de Madrid",
    "31": "Región de Murcia",
    "32": "Navarra",
    "34": "Asturias",
    "39": "Cantabria",
    "51": "Andalucía",
    "52": "Aragón",
    "53": "Canarias",
    "54": "Castilla-La Mancha",
    "55": "Castilla y León",
    "56": "Cataluña",
    "57": "Extremadura",
    "58": "Galicia",
    "59": "País Vasco",
    "60": "Comunitat Valenciana",
    "CE": "Ceuta",
    "ML": "Melilla",
};

const BASE_URL =
    "https://public.opendatasoft.com/api/explore/v2.1/catalog/datasets/geonames-all-cities-with-a-population-1000/records";

async function populate() {
    const conn = await mysql.createConnection({
        host: process.env.DB_HOST || "localhost",
        port: Number(process.env.DB_PORT) || 3306,
        user: process.env.DB_USER || "root",
        password: process.env.DB_PASSWORD || "",
        database: process.env.DB_NAME || "lliria_properties",
    });

    console.log("Conectado a BD. Iniciando descarga de municipios de España...");
    console.log("Fuente: OpenDataSoft / GeoNames (datos públicos)");

    let offset = 0;
    const limit = 100;
    let totalCount = Infinity;
    let inserted = 0;
    let skipped = 0;

    while (offset < totalCount) {
        const params = new URLSearchParams({
            where: 'cou_name_en="Spain"',
            limit: String(limit),
            offset: String(offset),
            fields: "name,ascii_name,admin1_code,admin1_name,coordinates,population",
            timezone: "UTC",
        });

        try {
            const { data } = await axios.get(`${BASE_URL}?${params.toString()}`, {
                timeout: 20000,
                headers: { "User-Agent": "LliriaPropertiesScraper/1.0 (contact@example.com)" },
            });

            totalCount = data.total_count;

            for (const record of data.results as any[]) {
                if (!record.coordinates || !record.name) { skipped++; continue; }

                const { lat, lon } = record.coordinates;
                const admin1Code = String(record.admin1_code || "").padStart(2, "0");
                const autonomy = record.admin1_name || ADMIN1_MAP[admin1Code] || record.admin1_code || "";
                const asciiName = record.ascii_name || record.name;
                const population = parseInt(record.population) || 0;

                try {
                    await conn.query(
                        `INSERT INTO municipalities_es (name, ascii_name, autonomy, lat, lng, population, source)
             VALUES (?, ?, ?, ?, ?, ?, 'geonames')
             ON DUPLICATE KEY UPDATE name=VALUES(name), autonomy=VALUES(autonomy), lat=VALUES(lat), lng=VALUES(lng), population=VALUES(population)`,
                        [record.name, asciiName, autonomy, parseFloat(lat), parseFloat(lon), population]
                    );
                    inserted++;
                } catch {
                    skipped++;
                }
            }

            offset += limit;
            process.stdout.write(`\r  → ${Math.min(offset, totalCount)} / ${totalCount} registros procesados (${inserted} insertados, ${skipped} omitidos)...`);

            // Respetar rate limit (2 peticiones/seg máx)
            await new Promise(r => setTimeout(r, 500));

        } catch (err: any) {
            console.error(`\nError en offset ${offset}:`, err.message);
            console.log("  Reintentando en 5s...");
            await new Promise(r => setTimeout(r, 5000));
        }
    }

    await conn.end();
    console.log(`\n\n✓ Completado: ${inserted} municipios importados, ${skipped} omitidos.`);
    console.log("  Ya puedes buscar cualquier municipio de España desde la vista de Zonas.");
}

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