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) {
        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.");

        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");

        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;

        try {
            gisJdbcTemplate.execute("CREATE EXTENSION IF NOT EXISTS postgres_fdw");
            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));

            // REGLA 23 ACTUALIZADA: Join OBLIGATORIO por CCC = INM_CTACATASTRAL
            String tableLotes = "public.e" + entidadId + "_lotes_conccc";
            String viewName = "vw_lotes_morosidad_" + entidadId;
            String viewWms = "vw_lotes_wms_" + entidadId;
            String sqlJoin = 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", viewName, tableLotes, schemaName);
            
            gisJdbcTemplate.execute(sqlJoin);
            gisJdbcTemplate.execute(sqlJoin.replace(viewName, viewWms));

            try {
                geoServerService.publishLayer(viewName, viewName, null, boundNo, boundSe);
                enableMvt(viewName);
                geoServerService.truncateCache(viewName);
                geoServerService.publishLayer(viewWms, viewWms, "morosidad_style_wms", boundNo, boundSe);
                geoServerService.truncateCache(viewWms);
            } catch (Exception e) { System.err.println("Advertencia GS: " + e.getMessage()); }

        } catch (Exception e) { throw new RuntimeException("Error FDW: " + 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();
        }
        return defaultValue;
    }

    public void enableMvt(String layerName) {
        // Hablar con host interno proyecto-geoserver-1
        String geoserverUrl = "http://proyecto-geoserver-1: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>application/x-protobuf</string></mimeFormats></GeoServerLayer>";
        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);
            conn.setConnectTimeout(2000);
            try (java.io.OutputStream os = conn.getOutputStream()) { os.write(xmlBody.getBytes()); }
            conn.getResponseCode();
        } catch (Exception e) { System.err.println("Error MVT: " + e.getMessage()); }
    }
}