package com.sigem.gis.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class FdwService {

    @Autowired
    private GeoServerService geoServerService;

    @Autowired
    @Qualifier("masterJdbcTemplate")
    private JdbcTemplate masterJdbcTemplate;

    @Autowired
    @Qualifier("gisJdbcTemplate")
    private JdbcTemplate gisJdbcTemplate;

    public void setupFdw(String entidadId) {
        // 1. Obtener datos de la entidad desde el Directorio Maestro (.254)
        String sqlEntidad = "SELECT sigem_site, sigem_dbname, boundno, boundse, latlong, zoom, maxzoom, minzoom " +
                "FROM public.entidades WHERE entidad = ?";
        List<Map<String, Object>> entidades = masterJdbcTemplate.queryForList(sqlEntidad, Integer.parseInt(entidadId));

        if (entidades.isEmpty()) {
            throw new RuntimeException("Entidad " + entidadId + " no encontrada en el directorio maestro.");
        }

        Map<String, Object> data = entidades.get(0);
        String sigemSite = (String) data.get("sigem_site");
        String sigemDbname = (String) data.get("sigem_dbname");
        String boundNo = (String) data.get("boundno");
        String boundSe = (String) data.get("boundse");

        // Log de parámetros recuperados (Regla 5)
        System.out.println("Configurando Entidad " + entidadId + ": Zoom=" + data.get("zoom") + ", Bounds=" + boundNo
                + "/" + boundSe);

        // ... (lógica de extracción de host/port/user/pass igual) ...
        String host = extractParam(sigemSite, "host", "127.0.0.1");
        String port = extractParam(sigemSite, "port", "5432");
        String user = extractParam(sigemSite, "user", "postgres");
        String pass = extractParam(sigemSite, "password", "x25yvaga2017");

        String serverName = "srv_mun_" + entidadId;
        String schemaName = "fdw_" + entidadId;

        // 2. Ejecutar comandos DDL en el servidor PostGIS (.123)
        try {
            // ... (verificación de infraestructura fdw igual hasta la creación de vistas)
            // ...
            String checkSql = "SELECT count(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = 'usuarios'";
            Integer count = gisJdbcTemplate.queryForObject(checkSql, Integer.class, schemaName);

            if (count == null || count == 0) {
                // (creación de server, user mapping y esquema igual)
                gisJdbcTemplate.execute("DROP SERVER IF EXISTS " + serverName + " CASCADE");
                gisJdbcTemplate.execute(String.format(
                        "CREATE SERVER %s FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '%s', port '%s', dbname '%s')",
                        serverName, host, port, sigemDbname));
                gisJdbcTemplate.execute(
                        String.format("CREATE USER MAPPING FOR sigem_user SERVER %s OPTIONS (user '%s', password '%s')",
                                serverName, user, pass));
                gisJdbcTemplate.execute("CREATE SCHEMA IF NOT EXISTS " + schemaName);
                gisJdbcTemplate.execute(String.format(
                        "IMPORT FOREIGN SCHEMA public LIMIT TO (v_liq_entidad_totalxobjeto, v_liq_entidad_percentiles, usuarios, ventanas_usuario) FROM SERVER %s INTO %s",
                        serverName, schemaName));
            }

            // 3. SIEMPRE Crear o Refrescar Vistas de Unión (JOIN)
            String tableLotes = "public.e" + entidadId + "_lotes_conccc";

            // Vista de Auditoría (MVT) - LIBERADA (Sin LIMIT)
            String viewLotesName = "vw_lotes_morosidad_" + entidadId;
            gisJdbcTemplate.execute(String.format(
                    "CREATE OR REPLACE VIEW public.%s AS SELECT l.*, m.inm_ficha, m.inm_ctacatastral, m.trb_total_deuda, m.trb_total_pago, m.ultimo_pago FROM %s l LEFT JOIN %s.v_liq_entidad_totalxobjeto m ON l.ccc = m.inm_ctacatastral",
                    viewLotesName, tableLotes, schemaName));

            // Vista PNG FULL (WMS) - SIN LIMIT
            String viewWmsName = "vw_lotes_wms_" + entidadId;
            gisJdbcTemplate.execute(String.format(
                    "CREATE OR REPLACE VIEW public.%s AS SELECT l.*, m.inm_ficha, m.inm_ctacatastral, m.trb_total_deuda, m.trb_total_pago, m.ultimo_pago FROM %s l LEFT JOIN %s.v_liq_entidad_totalxobjeto m ON l.ccc = m.inm_ctacatastral",
                    viewWmsName, tableLotes, schemaName));

            // 4. Sincronización con GeoServer
            try {
                // Publicar capa Auditoría (MVT)
                geoServerService.publishLayer(viewLotesName, viewLotesName, null, boundNo, boundSe);
                enableMvt(viewLotesName);
                geoServerService.truncateCache(viewLotesName);

                // Publicar capa PNG FULL (WMS) con Estilo
                geoServerService.publishLayer(viewWmsName, viewWmsName, "morosidad_style_wms", boundNo, boundSe);
                geoServerService.truncateCache(viewWmsName);
            } catch (Exception e) {
                System.err.println("Advertencia GS: " + e.getMessage());
            }

            System.out.println("Sincronización completa para Entidad " + entidadId);

        } catch (Exception e) {
            System.err.println("Error configurando FDW o Vistas: " + e.getMessage());
            throw new RuntimeException("Fallo en la automatización SIG: " + e.getMessage(), e);
        }

    }

    private String extractParam(String siteParam, String key, String defaultValue) {
        if (siteParam != null && siteParam.contains(key + "=")) {
            String[] parts = siteParam.split(key + "=");
            if (parts.length > 1) {
                return parts[1].split(" ")[0].trim();
            }
        }
        if (key.equals("host") && siteParam != null && !siteParam.contains("=")) {
            return siteParam.trim();
        }
        return defaultValue;
    }

    /**
     * Habilita Vector Tiles (PBF) para una capa específica en el GeoServer local.
     */
    public void enableMvt(String layerName) {
        // Usar host interno 'geoserver' y password correcto 'geoserver'
        String geoserverUrl = "http://localhost:8080/geoserver/gwc/rest/layers/sigem:" + layerName + ".xml";
        String auth = "admin:geoserver";
        String encodedAuth = java.util.Base64.getEncoder().encodeToString(auth.getBytes());

        String xmlBody = "<GeoServerLayer><enabled>true</enabled><mimeFormats>" +
                "<string>image/png</string><string>image/jpeg</string>" +
                "<string>application/x-protobuf</string></mimeFormats></GeoServerLayer>";

        System.out.println("Enviando configuración MVT para: " + layerName);

        try {
            java.net.URL url = new java.net.URL(geoserverUrl);
            java.net.HttpURLConnection conn = (java.net.HttpURLConnection) url.openConnection();
            conn.setRequestMethod("POST");
            conn.setRequestProperty("Authorization", "Basic " + encodedAuth);
            conn.setRequestProperty("Content-Type", "text/xml");
            conn.setDoOutput(true);

            // Establecer timeouts agresivos (1s) para no bloquear el LOGIN
            conn.setConnectTimeout(1000);
            conn.setReadTimeout(1000);

            try (java.io.OutputStream os = conn.getOutputStream()) {
                os.write(xmlBody.getBytes());
            }

            int code = conn.getResponseCode();
            System.out.println("GeoServer GWC Response para " + layerName + ": " + code);
        } catch (Exception e) {
            System.err.println("Error habilitando MVT para " + layerName + ": " + e.getMessage());
        }
    }
}