import { Request, Response } from "express";
import pool from "../config/db";
import axios from "axios";

export const municipalityController = {
    /**
     * GET /api/municipalities/search?q=lliria[&limit=10]
     * Busca primero en tabla local municipalities_es,
     * si hay pocos resultados cae en Nominatim (OSM) y cachea.
     */
    search: async (req: Request, res: Response): Promise<void> => {
        const q = String(req.query.q || "").trim();
        const limit = Math.min(parseInt(String(req.query.limit || "10")), 20);

        if (q.length < 2) {
            res.json({ success: true, data: [], source: "empty" });
            return;
        }

        try {
            // 1. Buscar en caché local
            const [rows] = await (pool as any).query(
                `SELECT id, name, ascii_name, province, autonomy, lat, lng, population
         FROM municipalities_es
         WHERE name LIKE ? OR ascii_name LIKE ?
         ORDER BY population DESC, name ASC
         LIMIT ?`,
                [`%${q}%`, `%${q}%`, limit]
            ) as any[];

            if ((rows as any[]).length >= 3) {
                res.json({ success: true, data: rows, source: "cache" });
                return;
            }

            // 2. Fallback a Nominatim (OpenStreetMap) para municipios no cacheados
            const url =
                `https://nominatim.openstreetmap.org/search` +
                `?q=${encodeURIComponent(q)}` +
                `&countrycodes=es&featuretype=settlement&format=json` +
                `&limit=8&addressdetails=1&extratags=1&accept-language=es`;

            const nominatimRes = await axios.get(url, {
                timeout: 6000,
                headers: { "User-Agent": "LliriaPropertiesScraper/1.0 (contact@lliria.local)" },
            });

            const results: any[] = (nominatimRes.data as any[]).map((item: any) => {
                const addr = item.address || {};
                const name =
                    addr.city || addr.town || addr.village || addr.hamlet ||
                    addr.municipality || item.display_name.split(",")[0];
                return {
                    name,
                    ascii_name: name,
                    display_name: item.display_name,
                    lat: parseFloat(item.lat),
                    lng: parseFloat(item.lon),
                    province: addr.county || addr.state_district || addr.province || "",
                    autonomy: addr.state || addr.region || "",
                    population: parseInt(item.extratags?.population || "0") || 0,
                    source: "nominatim",
                };
            });

            // 3. Cachear resultados nuevos en municipalities_es para próximas búsquedas
            for (const m of results) {
                try {
                    await (pool as any).query(
                        `INSERT INTO municipalities_es (name, ascii_name, province, autonomy, lat, lng, population, source)
             VALUES (?, ?, ?, ?, ?, ?, ?, 'nominatim')
             ON DUPLICATE KEY UPDATE lat=VALUES(lat), lng=VALUES(lng), province=VALUES(province)`,
                        [m.name, m.ascii_name, m.province, m.autonomy, m.lat, m.lng, m.population]
                    );
                } catch {
                    // Ignorar errores de duplicado
                }
            }

            // Combinar con resultados locales (evitar duplicados por nombre)
            const localNames = new Set((rows as any[]).map((r: any) => r.name.toLowerCase()));
            const combined = [
                ...(rows as any[]),
                ...results.filter(r => !localNames.has(r.name.toLowerCase())),
            ].slice(0, limit);

            res.json({ success: true, data: combined, source: "mixed" });
        } catch (err: any) {
            console.error("[MunicipalityController] Error:", err.message);
            // Devolver lo que hay en caché aunque falle Nominatim
            try {
                const [cached] = await (pool as any).query(
                    `SELECT * FROM municipalities_es WHERE name LIKE ? OR ascii_name LIKE ? LIMIT ?`,
                    [`%${q}%`, `%${q}%`, limit]
                ) as any[];
                res.json({ success: true, data: cached, source: "cache_fallback" });
            } catch {
                res.status(500).json({ success: false, error: "Error en búsqueda de municipios" });
            }
        }
    },

    /**
     * GET /api/municipalities/stats
     * Devuelve estadísticas de la tabla de municipios
     */
    stats: async (_req: Request, res: Response): Promise<void> => {
        try {
            const [[row]] = await (pool as any).query(
                `SELECT COUNT(*) AS total,
                COUNT(DISTINCT autonomy) AS autonomies,
                SUM(CASE WHEN source='geonames' THEN 1 ELSE 0 END) AS from_geonames,
                SUM(CASE WHEN source='nominatim' THEN 1 ELSE 0 END) AS from_nominatim
         FROM municipalities_es`
            ) as any[];
            res.json({ success: true, data: row });
        } catch (err) {
            res.status(500).json({ success: false, error: "Error" });
        }
    },
};