package com.sigem.gis;

import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SncMappingTool {

    static class Entity {
        String id;
        String name;
        boolean active;
        String sncCode = "N/A";
        String sncDept = "N/A";

        public Entity(String id, String name, boolean active) {
            this.id = id;
            this.name = name;
            this.active = active;
        }
    }

    static class SncDist {
        String code;
        String dept;
        String name;

        public SncDist(String code, String dept, String name) {
            this.code = code;
            this.dept = dept;
            this.name = name;
        }
    }

    public static void main(String[] args) {
        try {
            System.out.println("Iniciando Reporte Comparativo SIGEM vs SNC...");

            // 1. Obtener Entidades de .254
            List<Entity> sigemEntities = getSigemEntities();
            System.out.println("Entidades SIGEM recuperadas: " + sigemEntities.size());

            // 2. Obtener Distritos de SNC (via WFS)
            List<SncDist> sncDistricts = getSncDistricts();
            System.out.println("Distritos SNC recuperados: " + sncDistricts.size());

            // 3. Generar SQL Completo
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("DELETE FROM public.snc_catalog_mapping;\n");
            sqlBuilder.append("INSERT INTO public.snc_catalog_mapping (entidad_id, dpto_snc, dist_snc) VALUES \n");
            
            for (int i = 0; i < sncDistricts.size(); i++) {
                SncDist sd = sncDistricts.get(i);
                // Lógica de ID: Buscar match o generar ID administrativo
                Entity match = findMatch(sd.name, sigemEntities);
                String id = (match != null) ? match.id : "99" + sd.dept + sd.code;
                
                sqlBuilder.append(String.format("('%s', '%s', %s)", id, sd.dept, sd.code));
                if (i < sncDistricts.size() - 1) sqlBuilder.append(",");
                if (i % 5 == 4) sqlBuilder.append("\n");
            }
            sqlBuilder.append(";\n");

            System.out.println("--- SQL GENERADO (Copia y pega o inyecta) ---\n");
            System.out.println(sqlBuilder.toString());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static List<Entity> getSigemEntities() throws Exception {
        List<Entity> list = new ArrayList<>();
        Class.forName("org.postgresql.Driver");
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.254:5432/sigemweb", "postgres", "x25yvaga2017")) {
            try (Statement st = conn.createStatement()) {
                ResultSet rs = st.executeQuery("SELECT entidad, nombre, activo FROM public.entidades ORDER BY entidad");
                while (rs.next()) {
                    list.add(new Entity(rs.getString("entidad"), rs.getString("nombre"), rs.getBoolean("activo")));
                }
            }
        }
        return list;
    }

    private static List<SncDist> getSncDistricts() throws Exception {
        List<SncDist> list = new ArrayList<>();
        HttpClient client = HttpClient.newHttpClient();
        HttpRequest request = HttpRequest.newBuilder()
                .uri(URI.create("https://www.catastro.gov.py/geoserver/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=snc:ly_dist&maxFeatures=500&outputFormat=application/json"))
                .build();

        HttpResponse<String> response = client.send(request, HttpResponse.BodyHandlers.ofString());
        String body = response.body();

        // Parseo manual simple para evitar dependencias externas de JSON (Regex)
        Pattern p = Pattern.compile("\\{\"type\":\"Feature\".*?\"properties\":\\{(.*?)\\}\\}");
        Matcher m = p.matcher(body);
        while (m.find()) {
            String props = m.group(1);
            String code = extract(props, "cod_dist");
            String dept = extract(props, "cod_dpto");
            String name = extract(props, "nom_dist");
            list.add(new SncDist(code, dept, name));
        }
        return list;
    }

    private static String extract(String props, String key) {
        Pattern p = Pattern.compile("\"" + key + "\":\"?(.*?)\"?[,\\}]");
        Matcher m = p.matcher(props);
        if (m.find()) return m.group(1).trim();
        return "N/A";
    }

    private static Entity findMatch(String sncName, List<Entity> entities) {
        String cleanSnc = sncName.toUpperCase().trim();
        for (Entity e : entities) {
            String cleanSigem = e.name.toUpperCase().replace("MUNICIPALIDAD DE ", "").trim();
            if (cleanSnc.contains(cleanSigem) || cleanSigem.contains(cleanSnc)) return e;
        }
        return null;
    }
}