geo / gis-geoserver

package com.sigem.gis.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.client.RestTemplate;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@RestController
@RequestMapping("/api/import")
public class SncImportController {

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

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

    @Autowired
    private RestTemplate restTemplate;

    @GetMapping("/snc/init-schema")
    public String initSncSchema() {
        try {
            System.out.println("Iniciando inicializacion forzada de esquema SNC...");

            // Eliminar versiones previas para asegurar tipos TEXT
            gisJdbcTemplate.execute("DROP TABLE IF EXISTS public.snc_catalog_mapping CASCADE");
            gisJdbcTemplate.execute("DROP TABLE IF EXISTS public.snc_raw_distritos CASCADE");
            gisJdbcTemplate.execute("DROP TABLE IF EXISTS public.snc_raw_departamentos CASCADE");

            // Recrear Tablas con tipos TEXT según histórico y requerimiento
            gisJdbcTemplate.execute("CREATE TABLE public.snc_raw_distritos (" +
                    "id SERIAL PRIMARY KEY, " +
                    "objectid integer, " +
                    "codigo text, " +
                    "nom_dist text, " +
                    "cod_dist text, " +
                    "cod_dpto text, " +
                    "area_km2 numeric(15,6), " +
                    "geom geometry(MultiPolygon, 4326)" +
                    ")");

            gisJdbcTemplate.execute("CREATE TABLE public.snc_raw_departamentos (" +
                    "id SERIAL PRIMARY KEY, " +
                    "objectid integer, " +
                    "nom_dpto text, " +
                    "cod_dpto text, " +
                    "geom geometry(MultiPolygon, 4326)" +
                    ")");

            // Tabla de Mapeo Permanente
            gisJdbcTemplate.execute("CREATE TABLE public.snc_catalog_mapping (" +
                    "id SERIAL PRIMARY KEY, " +
                    "entidad_id text, " +
                    "dpto_snc text, " +
                    "dist_snc text" +
                    ")");

            return "OK: Esquema SNC recreado exitosamente (Tipo TEXT verificado).";
        } catch (Exception e) {
            e.printStackTrace();
            return "ERR Schema: " + e.getMessage();
        }
    }

    @GetMapping("/snc/import-departamentos")
    public String importDepartamentos() {
        try {
            initSncSchema();
            com.fasterxml.jackson.databind.ObjectMapper mapper = new com.fasterxml.jackson.databind.ObjectMapper();
            java.io.File dptoFile = new java.io.File("/yvyape/proyectos/sigem-gis/snc_ly_dpto.json");

            if (!dptoFile.exists())
                return "ERR: Archivo snc_ly_dpto.json no encontrado.";

            Map<String, Object> data = mapper.readValue(dptoFile, Map.class);
            List<Map<String, Object>> features = (List<Map<String, Object>>) data.get("features");

            gisJdbcTemplate.execute("TRUNCATE TABLE public.snc_raw_departamentos");
            List<Object[]> batch = new ArrayList<>();
            for (Map<String, Object> f : features) {
                Map<String, Object> p = (Map<String, Object>) f.get("properties");
                batch.add(new Object[] {
                        p.containsKey("objectid") ? Integer.parseInt(p.get("objectid").toString()) : null,
                        p.get("nom_dpto").toString().trim(),
                        String.valueOf(p.get("dpto")),
                        mapper.writeValueAsString(f.get("geometry"))
                });
            }
            gisJdbcTemplate.batchUpdate(
                    "INSERT INTO public.snc_raw_departamentos (objectid, nom_dpto, cod_dpto, geom) VALUES (?, ?, ?, ST_Multi(ST_SetSRID(ST_GeomFromGeoJSON(?), 4326)))",
                    batch);

            return "OK: Departamentos importados exitosamente. Cantidad: " + batch.size();
        } catch (Exception e) {
            e.printStackTrace();
            return "ERR Dpto: " + e.getMessage();
        }
    }

    @GetMapping("/snc/import-distritos")
    public String importDistritos() {
        try {
            initSncSchema();
            com.fasterxml.jackson.databind.ObjectMapper mapper = new com.fasterxml.jackson.databind.ObjectMapper();
            java.io.File distFile = new java.io.File("/yvyape/proyectos/sigem-gis/snc_ly_dist.json");

            if (!distFile.exists())
                return "ERR: Archivo snc_ly_dist.json no encontrado.";

            Map<String, Object> data = mapper.readValue(distFile, Map.class);
            List<Map<String, Object>> features = (List<Map<String, Object>>) data.get("features");

            gisJdbcTemplate.execute("TRUNCATE TABLE public.snc_raw_distritos");
            List<Object[]> batch = new ArrayList<>();
            for (Map<String, Object> f : features) {
                Map<String, Object> p = (Map<String, Object>) f.get("properties");
                String nomDist = p.get("nom_dist") != null ? p.get("nom_dist").toString().trim() : "SIN NOMBRE";
                String codDist = p.get("cod_dist") != null ? p.get("cod_dist").toString() : "";
                String codDpto = p.get("cod_dpto") != null ? p.get("cod_dpto").toString() : "";
                String codigo = p.get("codigo") != null ? p.get("codigo").toString().trim() : (codDpto + codDist);
                Double area = 0.0;
                if (p.get("area_km2") != null) {
                    try {
                        area = Double.parseDouble(p.get("area_km2").toString());
                    } catch (Exception e) {
                    }
                }

                batch.add(new Object[] {
                        p.containsKey("objectid") && p.get("objectid") != null
                                ? Integer.parseInt(p.get("objectid").toString())
                                : null,
                        codigo,
                        nomDist,
                        codDist,
                        codDpto,
                        area,
                        mapper.writeValueAsString(f.get("geometry"))
                });
            }
            gisJdbcTemplate.batchUpdate(
                    "INSERT INTO public.snc_raw_distritos (objectid, codigo, nom_dist, cod_dist, cod_dpto, area_km2, geom) VALUES (?, ?, ?, ?, ?, ?, ST_Multi(ST_SetSRID(ST_GeomFromGeoJSON(?), 4326)))",
                    batch);

            return "OK: Distritos importados exitosamente. Cantidad: " + batch.size();
        } catch (Exception e) {
            e.printStackTrace();
            return "ERR Dist: " + e.getMessage();
        }
    }

    @GetMapping("/snc/generate-mapping")
    public String generateSncMapping() {
        try {
            System.out.println("Iniciando Generación de Mapeo Soberano (SIGEM vs SNC)...");

            // 1. Limpiar mapeo actual
            gisJdbcTemplate.execute("TRUNCATE TABLE public.snc_catalog_mapping");

            // 2. Obtener TODAS las entidades del SIGEM (.254) incluyendo niv_entidad
            String sqlEntidades = "SELECT entidad, nombre, niv_entidad FROM public.entidades";
            List<Map<String, Object>> entities = masterJdbcTemplate.queryForList(sqlEntidades);

            // 3. Obtener Catálogo SNC local
            List<Map<String, Object>> sncDistricts = gisJdbcTemplate.queryForList(
                    "SELECT nom_dist, cod_dist, cod_dpto FROM public.snc_raw_distritos");

            int recordsCreated = 0;

            for (Map<String, Object> entity : entities) {
                int eid = ((Number) entity.get("entidad")).intValue();
                int nivEntidad = (entity.get("niv_entidad") != null) ? ((Number) entity.get("niv_entidad")).intValue()
                        : 0;
                String nombreSigemNorm = normalizeName(entity.get("nombre").toString());
                String dptoTarget = null;

                // REGLA 1: CASO ESPECIAL ASUNCIÓN (ENTIDAD 0)
                if (eid == 0) {
                    for (Map<String, Object> snc : sncDistricts) {
                        if ("A".equals(snc.get("cod_dpto"))) {
                            insertMapping("0", "A", snc.get("cod_dist").toString());
                            recordsCreated++;
                        }
                    }
                    continue; // Procesado
                }

                // REGLA 2: MUNICIPALIDADES (NONT-LEVEL 22) - DETERMINACIÓN DE DPTO POR RANGO
                if (eid != 0 && nivEntidad != 22) {
                    if (eid >= 100 && eid < 200)
                        dptoTarget = "B";
                    else if (eid >= 200 && eid < 300)
                        dptoTarget = "C";
                    else if (eid >= 300 && eid < 400)
                        dptoTarget = "D";
                    else if (eid >= 400 && eid < 500)
                        dptoTarget = "E";
                    else if (eid >= 500 && eid < 600)
                        dptoTarget = "F";
                    else if (eid >= 600 && eid < 700)
                        dptoTarget = "G";
                    else if (eid >= 700 && eid < 800)
                        dptoTarget = "H";
                    else if (eid >= 800 && eid < 900)
                        dptoTarget = "I";
                    else if (eid >= 900 && eid < 1000)
                        dptoTarget = "J";
                    else if (eid >= 1000 && eid < 1100)
                        dptoTarget = "K";
                    else if (eid >= 1100 && eid < 1200)
                        dptoTarget = "L";
                    else if (eid >= 1200 && eid < 1300)
                        dptoTarget = "M";
                    else if (eid >= 1300 && eid < 1400)
                        dptoTarget = "N";
                    else if (eid >= 1400 && eid < 1500)
                        dptoTarget = "S";
                    else if (eid >= 1500 && eid < 1600)
                        dptoTarget = "P";
                    else if (eid >= 1600 && eid < 1700)
                        dptoTarget = "R";
                    else if (eid >= 1700 && eid < 1800)
                        dptoTarget = "Q";

                    if (dptoTarget != null) {
                        boolean found = false;
                        for (Map<String, Object> snc : sncDistricts) {
                            if (dptoTarget.equals(snc.get("cod_dpto"))) {
                                String sncNameNom = normalizeName(snc.get("nom_dist").toString());
                                if (nombreSigemNorm.equals(sncNameNom)) {
                                    insertMapping(String.valueOf(eid), dptoTarget, snc.get("cod_dist").toString());
                                    found = true;
                                    recordsCreated++;
                                    break;
                                }
                            }
                        }
                        // REGLA 4: TRATAMIENTO DE EXCEPCIONES (MANTENER VACÍO SI NO HAY MATCH EXACTO)
                        if (!found) {
                            insertMapping(String.valueOf(eid), dptoTarget, null);
                            recordsCreated++;
                        }
                    }
                }
            }

            return "OK: Mapeo Soberano generado. Registros en tabla: " + recordsCreated;
        } catch (Exception e) {
            e.printStackTrace();
            return "ERR Mapping: " + e.getMessage();
        }
    }

    @GetMapping("/snc/list-null-mappings")
    public List<Map<String, Object>> listNullMappings() {
        try {
            // Unimos el mapeo (GIS) con los nombres de las entidades (Master)
            List<Map<String, Object>> nullMappings = gisJdbcTemplate.queryForList(
                    "SELECT entidad_id, dpto_snc FROM public.snc_catalog_mapping WHERE dist_snc IS NULL ORDER BY dpto_snc, entidad_id");

            List<Map<String, Object>> entities = masterJdbcTemplate
                    .queryForList("SELECT entidad, nombre FROM public.entidades");
            Map<String, String> entityNames = new java.util.HashMap<>();
            for (Map<String, Object> e : entities) {
                entityNames.put(String.valueOf(e.get("entidad")), String.valueOf(e.get("nombre")));
            }

            for (Map<String, Object> mapping : nullMappings) {
                String eid = (String) mapping.get("entidad_id");
                mapping.put("nombre_sigem", entityNames.getOrDefault(eid, "N/A"));
            }

            return nullMappings;
        } catch (Exception e) {
            return List.of(Map.of("error", e.getMessage()));
        }
    }

    @GetMapping("/snc/update-mapping")
    public String updateMapping(@RequestParam String entidadId, @RequestParam String distSnc,
            @RequestParam(required = false) String dptoSnc) {
        try {
            // Intentar UPDATE
            int rows = gisJdbcTemplate.update(
                    "UPDATE public.snc_catalog_mapping SET dist_snc = ? WHERE entidad_id = ?",
                    distSnc, entidadId);

            if (rows == 0 && dptoSnc != null) {
                // Si no existe y tenemos DPTO, hacemos INSERT
                gisJdbcTemplate.update(
                        "INSERT INTO public.snc_catalog_mapping (entidad_id, dpto_snc, dist_snc) VALUES (?, ?, ?)",
                        entidadId, dptoSnc, distSnc);
                return "OK: Mapeo creado (INJECT) para entidad " + entidadId;
            }

            return rows > 0 ? "OK: Mapeo actualizado para entidad " + entidadId
                    : "WARN: No se encontró la entidad y no se especificó dptoSnc";
        } catch (Exception e) {
            return "ERR Update: " + e.getMessage();
        }
    }

    @GetMapping("/snc/check-entity/{id}")
    public Map<String, Object> checkEntity(@PathVariable int id) {
        try {
            return masterJdbcTemplate.queryForMap(
                    "SELECT entidad, nombre, niv_entidad, activo FROM public.entidades WHERE entidad = ?", id);
        } catch (Exception e) {
            return Map.of("error", e.getMessage());
        }
    }

    private void insertMapping(String eid, String dpto, String dist) {
        gisJdbcTemplate.update(
                "INSERT INTO public.snc_catalog_mapping (entidad_id, dpto_snc, dist_snc) VALUES (?, ?, ?)",
                eid, dpto, dist);
    }

    private String normalizeName(String name) {
        if (name == null)
            return "";
        String s = name.toUpperCase();
        s = s.replace("MUNICIPALIDAD DE ", "");
        s = s.replace("MUNICIPALIDAD ", "");
        s = s.replace("CIUDAD DE ", "");
        s = java.text.Normalizer.normalize(s, java.text.Normalizer.Form.NFD);
        s = s.replaceAll("[\\p{InCombiningDiacriticalMarks}]", "");
        return s.trim();
    }

    private String findBestMatch(String sncName, List<Map<String, Object>> entities) {
        String cleanSnc = normalize(sncName);
        for (Map<String, Object> e : entities) {
            String cleanSigem = normalize(String.valueOf(e.get("nombre")));
            if (cleanSnc.equals(cleanSigem) || cleanSnc.contains(cleanSigem) || cleanSigem.contains(cleanSnc)) {
                return String.valueOf(e.get("entidad"));
            }
        }
        return null;
    }

    private String normalize(String s) {
        if (s == null)
            return "";
        return s.toUpperCase()
                .replace("\u00c1", "A") // Á
                .replace("\u00c9", "E") // É
                .replace("\u00cd", "I") // Í
                .replace("\u00d3", "O") // Ó
                .replace("\u00da", "U") // Ú
                .replace("\u00d1", "N") // Ñ
                .replace("MUNICIPALIDAD DE ", "")
                .replace("MUNICIPALIDAD ", "")
                .replace("CIUDAD DE ", "")
                .trim();
    }

    @GetMapping("/snc/clear-mapping")
    public String clearMapping() {
        try {
            gisJdbcTemplate.execute("TRUNCATE TABLE public.snc_catalog_mapping");
            return "OK: Tabla snc_catalog_mapping vaciada. Lista para reconstrucción dirigida.";
        } catch (Exception e) {
            return "ERR: " + e.getMessage();
        }
    }

    @GetMapping("/snc/list-departamentos")
    public List<Map<String, Object>> listDepartamentos() {
        try {
            return gisJdbcTemplate
                    .queryForList(
                            "SELECT id, objectid, nom_dpto, cod_dpto FROM public.snc_raw_departamentos ORDER BY cod_dpto");
        } catch (Exception e) {
            Map<String, Object> err = new HashMap<>();
            err.put("error", e.getMessage());
            return List.of(err);
        }
    }

    @GetMapping("/snc/list-distritos")
    public List<Map<String, Object>> listDistritos() {
        try {
            return gisJdbcTemplate.queryForList(
                    "SELECT id, objectid, codigo, nom_dist, cod_dist, cod_dpto, area_km2 FROM public.snc_raw_distritos ORDER BY cod_dpto, cod_dist");
        } catch (Exception e) {
            Map<String, Object> err = new HashMap<>();
            err.put("error", e.getMessage());
            return List.of(err);
        }
    }

    @GetMapping("/snc/list-distritos-grouped")
    public Map<String, List<String>> listDistritosGrouped() {
        try {
            List<Map<String, Object>> list = gisJdbcTemplate.queryForList(
                    "SELECT nom_dist, cod_dist, cod_dpto FROM public.snc_raw_distritos ORDER BY cod_dpto, nom_dist");
            Map<String, List<String>> grouped = new LinkedHashMap<>();
            for (Map<String, Object> r : list) {
                String dpto = String.valueOf(r.get("cod_dpto"));
                String distStr = r.get("cod_dist") + ": " + r.get("nom_dist");
                grouped.computeIfAbsent(dpto, k -> new ArrayList<>()).add(distStr);
            }
            return grouped;
        } catch (Exception e) {
            return Map.of("error", List.of(e.getMessage()));
        }
    }

    @GetMapping("/snc/table-info/{tableName}")
    public List<Map<String, Object>> getTableInfo(@PathVariable String tableName) {
        try {
            return gisJdbcTemplate.queryForList(
                    "SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = ? ORDER BY ordinal_position",
                    tableName);
        } catch (Exception e) {
            return List.of(Map.of("error", e.getMessage()));
        }
    }

    @GetMapping("/snc/deep-cleanup")
    public String deepCleanup() {
        try {
            StringBuilder sb = new StringBuilder();

            // 1. Eliminar tablas de lotes y parcelas
            List<String> tables = gisJdbcTemplate.queryForList(
                    "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND (tablename LIKE 'e%_lotes_activos' OR tablename LIKE 'e%_parcelas_activas')",
                    String.class);
            for (String table : tables) {
                gisJdbcTemplate.execute("DROP TABLE IF EXISTS public." + table + " CASCADE");
            }
            sb.append("Tablas eliminadas: ").append(tables.size()).append(". ");

            // 2. Eliminar esquemas FDW
            List<String> schemas = gisJdbcTemplate.queryForList(
                    "SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'fdw_%'",
                    String.class);
            for (String schema : schemas) {
                gisJdbcTemplate.execute("DROP SCHEMA IF EXISTS " + schema + " CASCADE");
            }
            sb.append("Esquemas FDW eliminados: ").append(schemas.size()).append(". ");

            // 3. Eliminar servidores FDW
            List<String> servers = gisJdbcTemplate.queryForList(
                    "SELECT srvname FROM pg_catalog.pg_foreign_server WHERE srvname LIKE 'srv_%'",
                    String.class);
            for (String server : servers) {
                gisJdbcTemplate.execute("DROP SERVER IF EXISTS " + server + " CASCADE");
            }
            sb.append("Servidores FDW eliminados: ").append(servers.size()).append(". ");

            return "OK: Saneamiento Total Completado. " + sb.toString();
        } catch (Exception e) {
            return "ERR Cleanup: " + e.getMessage();
        }
    }

    @GetMapping("/snc/list-tables")
    public List<String> listTables() {
        try {
            return gisJdbcTemplate.queryForList(
                    "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' ORDER BY tablename",
                    String.class);
        } catch (Exception e) {
            return List.of("ERR: " + e.getMessage());
        }
    }

    @GetMapping("/snc/list-mapping")
    public List<Map<String, Object>> listMapping() {
        try {
            String sql = "SELECT m.entidad_id, e.nombre, m.dpto_snc, m.dist_snc " +
                    "FROM public.snc_catalog_mapping m " +
                    "LEFT JOIN snc_raw_distritos d ON m.dist_snc = d.cod_dist AND m.dpto_snc = d.cod_dpto " +
                    "ORDER BY m.entidad_id";
            // Nota: El join con 'entidades' remoto debe hacerse con cuidado o mostrar solo
            // el ID guardado.
            // Para fines de validación inmediata, consultaremos lo que hay en la tabla de
            // mapeo local.
            return gisJdbcTemplate.queryForList("SELECT * FROM public.snc_catalog_mapping ORDER BY entidad_id");
        } catch (Exception e) {
            return List.of(Map.of("error", e.getMessage()));
        }
    }

    @GetMapping("/snc/mass-start")
    public String startMassImport() {
        new Thread(() -> {
            try {
                System.out.println(">>> ORQUESTADOR MASIVO: Iniciando Migración Nacional...");
                String sql = "SELECT entidad_id, dpto_snc, dist_snc FROM public.snc_catalog_mapping WHERE dist_snc IS NOT NULL";
                List<Map<String, Object>> mappingList = gisJdbcTemplate.queryForList(sql);

                int current = 0;
                int total = mappingList.size();

                for (Map<String, Object> map : mappingList) {
                    current++;
                    String eid = (String) map.get("entidad_id");
                    String dpto = (String) map.get("dpto_snc");
                    String dist = String.valueOf(map.get("dist_snc"));

                    System.out.println(String.format("[%d/%d] PROCESANDO ENTIDAD %s (SNC: %s-%s)", current, total, eid,
                            dpto, dist));
                    try {
                        importDistrict(eid, dpto, dist, false);
                    } catch (Exception e) {
                        System.err.println("!!! FALLO CRÍTICO EN ENTIDAD " + eid + ": " + e.getMessage());
                    }
                }
                System.out.println(">>> MIGRACIÓN MASIVA NACIONAL FINALIZADA CON ÉXITO.");
            } catch (Exception e) {
                System.err.println("!!! ERROR LÓGICO EN ORQUESTADOR: " + e.getMessage());
                e.printStackTrace();
            }
        }).start();
        return "OK: Proceso de migración masiva nacional iniciado en segundo plano. Monitoree logs del servidor.";
    }

    @GetMapping("/snc/{entityId}/{dpto}/{dist}")
    public String importDistrict(
            @PathVariable String entityId,
            @PathVariable String dpto,
            @PathVariable String dist,
            @RequestParam(defaultValue = "true") boolean processFdw) {

        try {
            String tableName = "public.e" + entityId + "_lotes_activos";

            // 1. Garantizar existencia de la tabla e Iniciar Limpieza
            createSncTableIfNotExists(tableName);
            gisJdbcTemplate.execute("TRUNCATE TABLE " + tableName + " CASCADE");

            // 2. Construcción Robusta de URL (Regla 28)
            String url = org.springframework.web.util.UriComponentsBuilder
                    .fromHttpUrl("https://www.catastro.gov.py/geoserver/ows")
                    .queryParam("service", "WFS")
                    .queryParam("version", "1.0.0")
                    .queryParam("request", "GetFeature")
                    .queryParam("typeName", "snc:parcelas_activas")
                    .queryParam("outputFormat", "application/json")
                    .queryParam("srsName", "EPSG:4326")
                    .queryParam("cql_filter", String.format("dpto='%s' AND dist='%s'", dpto, dist))
                    .build()
                    .toUriString();

            Map<String, Object> response = restTemplate.getForObject(url, Map.class);
            if (response == null) {
                System.err.println("!!! Respuesta NULL para Entidad " + entityId);
                return "ERR: Respuesta NULL del SNC";
            }

            List<Map<String, Object>> features = (List<Map<String, Object>>) response.get("features");
            if (features == null || features.isEmpty()) {
                System.out.println("--- CERO CARGAS para " + entityId + " (Filtro: " + dpto + "-" + dist + ")");
                return "WARN: No se encontraron registros";
            }

            com.fasterxml.jackson.databind.ObjectMapper mapper = new com.fasterxml.jackson.databind.ObjectMapper();
            List<Object[]> batchArgs = new ArrayList<>();

            for (Map<String, Object> feature : features) {
                Map<String, Object> props = (Map<String, Object>) feature.get("properties");

                // Intento resiliente de captura de geometría (Regla 28)
                Object shapeObj = props.get("shape");
                if (shapeObj == null) {
                    shapeObj = feature.get("geometry");
                }

                String ccatastral = (String) props.get("ccatastral");
                Integer tc = (Integer) props.get("tipo_cuenta");
                Object padronObj = props.get("padron");
                String padronStr = padronObj != null ? String.valueOf(padronObj) : "";

                // REGLA 26: Normalización Universal de Cartografía SNC (Actualizada)
                String snc_cuenta = "";
                if (tc != null && tc == 0) {
                    // 1. Zona Urbana (tipo_cuenta = 0): Substring(ccatastral, 4) eliminando ceros
                    if (ccatastral != null && ccatastral.length() >= 4) {
                        snc_cuenta = ccatastral.substring(3).replaceAll("^0+", "").replaceAll("[^a-zA-Z0-9]", "");
                    } else if (!padronStr.isEmpty()) {
                        snc_cuenta = padronStr.replaceAll("^0+", "");
                    }
                } else if (tc != null && tc == 1) {
                    // 2. Zona Rural (tipo_cuenta = 1): padron::text (sin modificaciones)
                    snc_cuenta = padronStr;
                }

                try {
                    String geomJson = mapper.writeValueAsString(shapeObj);
                    if (shapeObj == null)
                        continue;

                    batchArgs.add(new Object[] {
                            props.get("id"), props.get("objectid"), props.get("id_parcela"),
                            props.get("dpto"), props.get("dist"), props.get("padron"),
                            props.get("zona"), props.get("mz"), props.get("lote"),
                            props.get("finca"), props.get("nro_matricula"),
                            ccatastral, props.get("obs"),
                            props.get("mz_agr"), props.get("lote_agr"),
                            props.get("tipo_pavim"), tc,
                            props.get("hectareas"), props.get("superficie_tierra"),
                            props.get("superficie_edificado"), props.get("valor_tierra"),
                            props.get("valor_edificado"), props.get("tipo"),
                            props.get("referencia"), props.get("clave_comparacion"),
                            geomJson, snc_cuenta, ccatastral
                    });
                } catch (Exception e) {
                }
            }

            String insertSql = "INSERT INTO " + tableName + " (" +
                    "id_snc, objectid, id_parcela, dpto, dist, padron, zona, mz, lote, " +
                    "finca, nro_matricula, ccatastral, obs, mz_agr, lote_agr, tipo_pavim, " +
                    "tipo_cuenta, hectareas, superficie_tierra, superficie_edificado, " +
                    "valor_tierra, valor_edificado, tipo_parcela, referencia, clave_comparacion, " +
                    "geom, snc_cuenta, ccc) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_SetSRID(ST_GeomFromGeoJSON(?), 4326)), 3)), ?, ?)";

            gisJdbcTemplate.batchUpdate(insertSql, batchArgs);
            System.out.println("+++ EXITO: " + entityId + " -> Inyectados " + batchArgs.size() + " registros.");

            if (processFdw) {
                processFdwAndViews(entityId);
            }

            return "OK: " + entityId + " (" + features.size() + " recs)";
        } catch (Exception e) {
            System.err.println("!!! FALLO en Importación de " + entityId + ": " + e.getMessage());
            return "ERR: " + entityId + " -> " + e.getMessage();
        }
    }

    private void createSncTableIfNotExists(String tableName) {
        String sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "id SERIAL PRIMARY KEY, " +
                "id_snc TEXT, " +
                "objectid INTEGER, " +
                "id_parcela TEXT, " +
                "dpto TEXT, " +
                "dist TEXT, " +
                "padron TEXT, " +
                "zona TEXT, " +
                "mz TEXT, " +
                "lote TEXT, " +
                "finca TEXT, " +
                "nro_matricula TEXT, " +
                "ccatastral TEXT, " +
                "obs TEXT, " +
                "mz_agr TEXT, " +
                "lote_agr TEXT, " +
                "tipo_pavim TEXT, " +
                "tipo_cuenta INTEGER, " +
                "hectareas NUMERIC, " +
                "superficie_tierra NUMERIC, " +
                "superficie_edificado NUMERIC, " +
                "valor_tierra NUMERIC, " +
                "valor_edificado NUMERIC, " +
                "tipo_parcela TEXT, " +
                "referencia TEXT, " +
                "clave_comparacion TEXT, " +
                "snc_cuenta TEXT, " +
                "ccc TEXT, " +
                "geom geometry(MultiPolygon, 4326)" +
                ")";
        gisJdbcTemplate.execute(sql);
        gisJdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_" + tableName.replace(".", "_") + "_geom ON "
                + tableName + " USING GIST(geom)");
        gisJdbcTemplate.execute(
                "CREATE INDEX IF NOT EXISTS idx_" + tableName.replace(".", "_") + "_ccc ON " + tableName + "(ccc)");
    }

    private void processFdwAndViews(String entityId) {
        try {
            Map<String, Object> data = masterJdbcTemplate.queryForMap(
                    "SELECT activo, sigem_site, sigem_dbname FROM public.entidades WHERE entidad = ?",
                    Integer.parseInt(entityId));

            if (Boolean.TRUE.equals(data.get("activo"))) {
                String site = (String) data.get("sigem_site");
                String db = (String) data.get("sigem_dbname");

                String host = "localhost";
                String port = "5432";

                // Extraer host
                Pattern pHost = Pattern.compile("host=([^\\s]+)");
                Matcher mHost = pHost.matcher(site != null ? site : "");
                if (mHost.find()) {
                    host = mHost.group(1);
                }

                // Extraer port
                Pattern pPort = Pattern.compile("port=([^\\s]+)");
                Matcher mPort = pPort.matcher(site != null ? site : "");
                if (mPort.find()) {
                    port = mPort.group(1);
                }

                // Extraer password
                String pass = "x25yvaga2017"; // fallback
                Pattern pPass = Pattern.compile("password=([^\\s]+)");
                Matcher mPass2 = pPass.matcher(site != null ? site : "");
                if (mPass2.find()) {
                    pass = mPass2.group(1);
                }

                String fdwSchema = "fdw_" + entityId;
                gisJdbcTemplate.execute("CREATE EXTENSION IF NOT EXISTS postgres_fdw");
                gisJdbcTemplate.execute("DROP SCHEMA IF EXISTS " + fdwSchema + " CASCADE");
                gisJdbcTemplate.execute("DROP SERVER IF EXISTS srv_" + entityId + " CASCADE");
                gisJdbcTemplate.execute("CREATE SCHEMA " + fdwSchema);
                gisJdbcTemplate.execute(String.format(
                        "CREATE SERVER srv_%s FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '%s', port '%s', dbname '%s')",
                        entityId, host, port, db));
                gisJdbcTemplate.execute(String.format(
                        "CREATE USER MAPPING FOR current_user SERVER srv_%s OPTIONS (user 'postgres', password '%s')",
                        entityId, pass));
                gisJdbcTemplate.execute(String.format(
                        "IMPORT FOREIGN SCHEMA public LIMIT TO (v_liq_entidad_totalxobjeto, usuarios) FROM SERVER srv_%s INTO %s",
                        entityId, fdwSchema));

                gisJdbcTemplate.execute("CREATE OR REPLACE VIEW public.vw_lotes_morosidad_" + entityId + " AS " +
                        "SELECT lot.*, liq.inm_ficha, liq.inm_ctacatastral, liq.trb_total_deuda, liq.trb_total_pago, liq.ultimo_pago "
                        +
                        "FROM public.e" + entityId + "_lotes_activos lot " +
                        "LEFT JOIN " + fdwSchema
                        + ".v_liq_entidad_totalxobjeto liq ON lot.snc_cuenta = REPLACE(liq.inm_ctacatastral, '-', '')");
            }
        } catch (Exception e) {
            System.err.println("Skip FDW for " + entityId + ": " + e.getMessage());
        }
    }
}