package incheon.uis.uld.service;

import org.apache.ibatis.binding.BindingException;

import java.util.*;

/**
 * 레이어 동기화용 동적 SQL Provider
 * 업로드된 레이어 데이터를 DB에 UPDATE하는 SQL 생성
 */
public class LayerSyncSqlProvider {

    /**
     * 배치 UPDATE SQL 생성 (CASE WHEN 방식)
     * 필요 파라미터(@Param("param")):
     *  - schemaName : String (예: icuis)
     *  - tableName  : String (예: swl_pipe_l)
     *  - pkColumn   : String (예: ftc 또는 복합키: ftc,idn)
     *  - srid       : Integer (예: 5186)
     *  - columns    : List<Map> (각 map: column_name, data_type)
     *  - features   : List<Map> (각 map: gid(PK값), geometryString, properties:Map)
     */
    public String batchUpdateFeatures(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = quoteIdent(safeIdent((String) p.get("tableName")));
        final String pkColumnRaw = (String) p.get("pkColumn");
        final int srid = toInt(p.get("srid"), 5186);           // 대상 좌표계 (DB 저장용)
        final int sourceSrid = toInt(p.get("sourceSrid"), 5186); // 소스 좌표계 (GeoJSON 좌표계, 기본값 5186)

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) p.get("features");

        // 복합 PK 파싱 및 타입 매핑
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    // PK 컬럼의 데이터 타입 조회
                    String dataType = findType(columns, trimmed);
                    pkColumnTypes.put(trimmed, dataType);
                }
            }
        }

        if (pkColumns.isEmpty()) {
            throw new BindingException("[LayerSyncSqlProvider] pkColumn이 비어있습니다.");
        }

        if (features == null || features.isEmpty()) {
            String firstPk = quoteIdent(safeIdent(pkColumns.get(0)));
            return "UPDATE " + schema + "." + table + " SET " + firstPk + " = " + firstPk + " WHERE 1=0";
        }

        // 첫 번째 feature의 properties에 있는 키만 갱신 대상 (PK 컬럼 및 시스템 컬럼 제외)
        @SuppressWarnings("unchecked")
        Map<String, Object> firstProps = (Map<String, Object>) features.get(0).get("properties");
        Set<String> validColumns = new LinkedHashSet<>();
        Set<String> pkColumnSet = new HashSet<>(pkColumns);

        // 시스템에서 자동 관리하는 컬럼 (UPDATE 대상에서 제외)
        Set<String> excludeColumns = new HashSet<>(Arrays.asList(
            "last_mdfcn_dt", "last_mdfcn_id",   // 수정자/수정일시 - 별도 처리
            "frst_reg_dt", "frst_reg_id"         // 등록자/등록일시 - 변경 안함
        ));

        if (firstProps != null && columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                // PK 컬럼, 시스템 컬럼은 UPDATE 대상에서 제외
                if (colName != null && firstProps.containsKey(colName)
                    && !pkColumnSet.contains(colName) && !excludeColumns.contains(colName)) {
                    validColumns.add(colName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(schema).append(".").append(table).append(" SET ");

        // 1) GEOMETRY 컬럼 (CASE WHEN)
        // sourceSrid -> srid로 좌표 변환 (같으면 SetSRID만, 다르면 Transform)
        sql.append("\"geom\" = CASE ");
        for (int i = 0; i < features.size(); i++) {
            sql.append("WHEN ");
            appendPkCondition(sql, pkColumns, pkColumnTypes, i);
            if (sourceSrid == srid) {
                // 좌표계가 같으면 변환 없이 SRID만 설정
                sql.append(" THEN ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                   .append(i).append("].geometryString})), ").append(srid).append(") ");
            } else {
                // 좌표계가 다르면 ST_Transform으로 변환
                sql.append(" THEN ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                   .append(i).append("].geometryString})), ").append(sourceSrid).append("), ").append(srid).append(") ");
            }
        }
        sql.append("ELSE \"geom\" END");

        // 2) 속성 컬럼 (각각 CASE WHEN) - PK 컬럼 제외
        if (!validColumns.isEmpty()) {
            for (String colName : validColumns) {
                String dataType = findType(columns, colName);
                String qCol = quoteIdent(safeIdent(colName));

                sql.append(", ").append(qCol).append(" = CASE ");
                for (int i = 0; i < features.size(); i++) {
                    sql.append("WHEN ");
                    appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                    sql.append(" THEN #{param.features[").append(i).append("].properties.").append(colName).append("}");
                    if (needsTypeCast(dataType)) {
                        sql.append("::").append(dataType);
                    }
                    sql.append(" ");
                }
                sql.append("ELSE ").append(qCol).append(" END");
            }
        }

        // 3) 시스템 컬럼: last_mdfcn_id (세션 사용자), last_mdfcn_dt (현재 시간)
        sql.append(", \"last_mdfcn_id\" = #{param.sessionUserId}");
        sql.append(", \"last_mdfcn_dt\" = NOW()");

        // 3) WHERE 조건 (복합 PK 지원)
        sql.append(" WHERE ");
        if (pkColumns.size() == 1) {
            // 단일 PK: IN 절 사용
            String pkColName = pkColumns.get(0);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" IN (");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append("#{param.features[").append(i).append("].properties.").append(pkColName).append("}");
                if (needsTypeCast(pkType)) {
                    sql.append("::").append(pkType);
                }
            }
            sql.append(")");
        } else {
            // 복합 PK: OR 조건 사용
            sql.append("(");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(" OR ");
                sql.append("(");
                appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                sql.append(")");
            }
            sql.append(")");
        }

        return sql.toString();
    }

    /**
     * 복합 PK 조건 생성 (pk1 = val1 AND pk2 = val2)
     * 각 PK 컬럼의 데이터 타입에 맞게 캐스팅 추가
     */
    private void appendPkCondition(StringBuilder sql, List<String> pkColumns,
                                   Map<String, String> pkColumnTypes, int featureIndex) {
        for (int j = 0; j < pkColumns.size(); j++) {
            if (j > 0) sql.append(" AND ");
            String pkColName = pkColumns.get(j);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);

            sql.append(pkCol).append(" = #{param.features[").append(featureIndex)
               .append("].properties.").append(pkColName).append("}");

            // 숫자/날짜 타입인 경우 캐스팅 추가
            if (needsTypeCast(pkType)) {
                sql.append("::").append(pkType);
            }
        }
    }

    /**
     * 타입 캐스팅이 필요한지 확인
     * varchar, text, character varying은 캐스팅 불필요
     */
    private boolean needsTypeCast(String dataType) {
        if (dataType == null) return false;
        String lower = dataType.toLowerCase();
        return !lower.equals("varchar") && !lower.equals("text") && !lower.equals("character varying");
    }

    /**
     * 이력 테이블 INSERT SQL 생성
     * INSERT INTO 테이블명_h SELECT *, stts_cd FROM 테이블명 WHERE PK조건
     *
     * @param sttsCd 상태코드: 'C'=신규(Create), 'U'=수정(Update), 'D'=삭제(Delete)
     */
    public String insertHistory(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = safeIdent((String) p.get("tableName"));
        final String pkColumnRaw = (String) p.get("pkColumn");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) p.get("features");

        // 복합 PK 파싱 및 타입 매핑
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    String dataType = findType(columns, trimmed);
                    pkColumnTypes.put(trimmed, dataType);
                }
            }
        }

        if (pkColumns.isEmpty() || features == null || features.isEmpty()) {
            return "SELECT 1 WHERE 1=0"; // no-op
        }

        // 원본 테이블의 컬럼 목록 생성 (gid 포함, geom 포함)
        StringBuilder colList = new StringBuilder();
        colList.append("\"gid\", \"geom\"");
        if (columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null) {
                    colList.append(", ").append(quoteIdent(safeIdent(colName)));
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        // INSERT INTO schema.table_h (컬럼들, stts_cd)
        sql.append("INSERT INTO ").append(schema).append(".").append(quoteIdent(table + "_h"))
           .append(" (").append(colList).append(", \"stts_cd\") ");

        // SELECT 컬럼들, #{param.sttsCd} FROM schema.table WHERE PK조건
        sql.append("SELECT ").append(colList).append(", #{param.sttsCd} ");
        sql.append("FROM ").append(schema).append(".").append(quoteIdent(table)).append(" ");

        // WHERE 조건
        sql.append("WHERE ");
        if (pkColumns.size() == 1) {
            String pkColName = pkColumns.get(0);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" IN (");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append("#{param.features[").append(i).append("].properties.").append(pkColName).append("}");
                if (needsTypeCast(pkType)) {
                    sql.append("::").append(pkType);
                }
            }
            sql.append(")");
        } else {
            sql.append("(");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(" OR ");
                sql.append("(");
                appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                sql.append(")");
            }
            sql.append(")");
        }

        return sql.toString();
    }

    /**
     * Soft Delete SQL 생성 (use_yn = 'N' 으로 UPDATE)
     * 필요 파라미터(@Param("param")):
     *  - schemaName : String
     *  - tableName  : String
     *  - pkColumn   : String (복합키: ftc,idn)
     *  - columns    : List<Map> (각 map: column_name, data_type)
     *  - features   : List<Map> (각 map: properties:Map - PK값 포함)
     *  - sessionUserId : String (수정자 ID)
     */
    public String softDeleteFeatures(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = quoteIdent(safeIdent((String) p.get("tableName")));
        final String pkColumnRaw = (String) p.get("pkColumn");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) p.get("features");

        // 복합 PK 파싱 및 타입 매핑
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    String dataType = findType(columns, trimmed);
                    pkColumnTypes.put(trimmed, dataType);
                }
            }
        }

        if (pkColumns.isEmpty()) {
            throw new BindingException("[LayerSyncSqlProvider] pkColumn이 비어있습니다.");
        }

        if (features == null || features.isEmpty()) {
            String firstPk = quoteIdent(safeIdent(pkColumns.get(0)));
            return "UPDATE " + schema + "." + table + " SET " + firstPk + " = " + firstPk + " WHERE 1=0";
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(schema).append(".").append(table).append(" SET ");

        // use_yn = 'N' (Soft Delete)
        sql.append("\"use_yn\" = 'N'");

        // 수정자/수정일시
        sql.append(", \"last_mdfcn_id\" = #{param.sessionUserId}");
        sql.append(", \"last_mdfcn_dt\" = NOW()");

        // WHERE 조건 (복합 PK 지원)
        sql.append(" WHERE ");
        if (pkColumns.size() == 1) {
            // 단일 PK: IN 절 사용
            String pkColName = pkColumns.get(0);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" IN (");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append("#{param.features[").append(i).append("].properties.").append(pkColName).append("}");
                if (needsTypeCast(pkType)) {
                    sql.append("::").append(pkType);
                }
            }
            sql.append(")");
        } else {
            // 복합 PK: OR 조건 사용
            sql.append("(");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(" OR ");
                sql.append("(");
                appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                sql.append(")");
            }
            sql.append(")");
        }

        return sql.toString();
    }

    /**
     * PK로 기존 데이터 조회 SQL 생성 (use_yn 포함)
     * 복합 PK의 경우 콤마로 연결된 문자열 + use_yn 반환
     */
    public String selectExistingByPk(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = quoteIdent(safeIdent((String) p.get("tableName")));
        final String pkColumnRaw = (String) p.get("pkColumn");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) p.get("features");

        // 복합 PK 파싱
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    pkColumnTypes.put(trimmed, findType(columns, trimmed));
                }
            }
        }

        if (pkColumns.isEmpty() || features == null || features.isEmpty()) {
            return "SELECT NULL AS pk_value, NULL AS use_yn WHERE 1=0";
        }

        // PK 컬럼들을 CONCAT으로 연결 + use_yn 반환
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT CONCAT_WS(',', ");
        for (int j = 0; j < pkColumns.size(); j++) {
            if (j > 0) sql.append(", ");
            sql.append(quoteIdent(safeIdent(pkColumns.get(j))));
        }
        sql.append(") AS pk_value, \"use_yn\" ");
        sql.append("FROM ").append(schema).append(".").append(table).append(" ");
        sql.append("WHERE ");

        // PK 조건 (use_yn 조건 없이 모든 데이터 조회)
        if (pkColumns.size() == 1) {
            String pkColName = pkColumns.get(0);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" IN (");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append("#{param.features[").append(i).append("].properties.").append(pkColName).append("}");
                if (needsTypeCast(pkType)) sql.append("::").append(pkType);
            }
            sql.append(")");
        } else {
            sql.append("(");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(" OR ");
                sql.append("(");
                appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                sql.append(")");
            }
            sql.append(")");
        }

        return sql.toString();
    }

    /**
     * 삭제된 데이터 재활성화 + UPDATE SQL 생성
     * use_yn = 'N' -> 'Y'로 변경하면서 받아온 값으로 UPDATE
     */
    public String reactivateFeatures(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = quoteIdent(safeIdent((String) p.get("tableName")));
        final String pkColumnRaw = (String) p.get("pkColumn");
        final int srid = toInt(p.get("srid"), 5186);
        final int sourceSrid = toInt(p.get("sourceSrid"), 5186);

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) p.get("features");

        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    pkColumnTypes.put(trimmed, findType(columns, trimmed));
                }
            }
        }

        if (pkColumns.isEmpty() || features == null || features.isEmpty()) {
            String firstPk = quoteIdent(safeIdent(pkColumns.isEmpty() ? "id" : pkColumns.get(0)));
            return "UPDATE " + schema + "." + table + " SET " + firstPk + " = " + firstPk + " WHERE 1=0";
        }

        // 첫 번째 feature의 properties에서 갱신 대상 컬럼 추출
        @SuppressWarnings("unchecked")
        Map<String, Object> firstProps = (Map<String, Object>) features.get(0).get("properties");
        Set<String> validColumns = new LinkedHashSet<>();
        Set<String> pkColumnSet = new HashSet<>(pkColumns);
        Set<String> excludeColumns = new HashSet<>(Arrays.asList(
            "last_mdfcn_dt", "last_mdfcn_id", "frst_reg_dt", "frst_reg_id", "gid", "use_yn"
        ));

        if (firstProps != null && columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null && firstProps.containsKey(colName)
                    && !pkColumnSet.contains(colName) && !excludeColumns.contains(colName)) {
                    validColumns.add(colName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(schema).append(".").append(table).append(" SET ");

        // use_yn = 'Y' (재활성화)
        sql.append("\"use_yn\" = 'Y'");

        // GEOMETRY 컬럼 (CASE WHEN)
        sql.append(", \"geom\" = CASE ");
        for (int i = 0; i < features.size(); i++) {
            sql.append("WHEN ");
            appendPkCondition(sql, pkColumns, pkColumnTypes, i);
            if (sourceSrid == srid) {
                sql.append(" THEN ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                   .append(i).append("].geometryString})), ").append(srid).append(") ");
            } else {
                sql.append(" THEN ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                   .append(i).append("].geometryString})), ").append(sourceSrid).append("), ").append(srid).append(") ");
            }
        }
        sql.append("ELSE \"geom\" END");

        // 속성 컬럼들
        for (String colName : validColumns) {
            String dataType = findType(columns, colName);
            String qCol = quoteIdent(safeIdent(colName));
            sql.append(", ").append(qCol).append(" = CASE ");
            for (int i = 0; i < features.size(); i++) {
                sql.append("WHEN ");
                appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                sql.append(" THEN #{param.features[").append(i).append("].properties.").append(colName).append("}");
                if (needsTypeCast(dataType)) sql.append("::").append(dataType);
                sql.append(" ");
            }
            sql.append("ELSE ").append(qCol).append(" END");
        }

        // 수정자/수정일시
        sql.append(", \"last_mdfcn_id\" = #{param.sessionUserId}");
        sql.append(", \"last_mdfcn_dt\" = NOW()");

        // WHERE 조건 (use_yn = 'N'인 것만)
        sql.append(" WHERE \"use_yn\" = 'N' AND ");
        if (pkColumns.size() == 1) {
            String pkColName = pkColumns.get(0);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" IN (");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append("#{param.features[").append(i).append("].properties.").append(pkColName).append("}");
                if (needsTypeCast(pkType)) sql.append("::").append(pkType);
            }
            sql.append(")");
        } else {
            sql.append("(");
            for (int i = 0; i < features.size(); i++) {
                if (i > 0) sql.append(" OR ");
                sql.append("(");
                appendPkCondition(sql, pkColumns, pkColumnTypes, i);
                sql.append(")");
            }
            sql.append(")");
        }

        return sql.toString();
    }

    /**
     * 진짜 신규 INSERT SQL 생성 (gid 제외 - DB 시퀀스로 자동 생성)
     * 단일 feature INSERT (Controller에서 반복 호출)
     * PK 컬럼을 제외하고 null인 값은 INSERT 대상에서 제외
     */
    public String insertNewFeature(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = quoteIdent(safeIdent((String) p.get("tableName")));
        final String pkColumnRaw = (String) p.get("pkColumn");
        final int srid = toInt(p.get("srid"), 5186);
        final int sourceSrid = toInt(p.get("sourceSrid"), 5186);

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        Map<String, Object> feature = (Map<String, Object>) p.get("feature");

        if (feature == null || columns == null) {
            return "SELECT 1 WHERE 1=0";
        }

        @SuppressWarnings("unchecked")
        Map<String, Object> props = (Map<String, Object>) feature.get("properties");

        // PK 컬럼 파싱 (ftc, idn 등)
        Set<String> pkColumnSet = new HashSet<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumnSet.add(trimmed);
                }
            }
        }

        // 컬럼 목록 구성 (gid 제외, geom 포함)
        List<String> insertColumns = new ArrayList<>();
        insertColumns.add("geom");

        // 시스템 컬럼은 별도 처리
        Set<String> systemColumns = new HashSet<>(Arrays.asList("gid", "frst_reg_dt", "frst_reg_id", "last_mdfcn_dt", "last_mdfcn_id"));

        for (Map<String, Object> col : columns) {
            String colName = (String) col.get("column_name");
            if (colName != null && !systemColumns.contains(colName) && props != null && props.containsKey(colName)) {
                // 값이 null인 경우 INSERT 대상에서 제외 (idn 포함 - DB 시퀀스로 자동 생성)
                Object value = props.get(colName);
                if (value != null) {
                    insertColumns.add(colName);
                }
            }
        }

        // 등록자/등록일시, 수정자/수정일시 추가
        insertColumns.add("frst_reg_id");
        insertColumns.add("frst_reg_dt");
        insertColumns.add("last_mdfcn_id");
        insertColumns.add("last_mdfcn_dt");

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schema).append(".").append(table).append(" (");

        // 컬럼 목록
        for (int i = 0; i < insertColumns.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append(quoteIdent(safeIdent(insertColumns.get(i))));
        }
        sql.append(") VALUES (");

        // 값 목록
        for (int i = 0; i < insertColumns.size(); i++) {
            if (i > 0) sql.append(", ");
            String colName = insertColumns.get(i);

            if ("geom".equals(colName)) {
                if (sourceSrid == srid) {
                    sql.append("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.feature.geometryString})), ").append(srid).append(")");
                } else {
                    sql.append("ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.feature.geometryString})), ")
                       .append(sourceSrid).append("), ").append(srid).append(")");
                }
            } else if ("frst_reg_id".equals(colName) || "last_mdfcn_id".equals(colName)) {
                sql.append("#{param.sessionUserId}");
            } else if ("frst_reg_dt".equals(colName) || "last_mdfcn_dt".equals(colName)) {
                sql.append("NOW()");
            } else {
                String dataType = findType(columns, colName);
                sql.append("#{param.feature.properties.").append(colName).append("}");
                if (needsTypeCast(dataType)) sql.append("::").append(dataType);
            }
        }
        sql.append(")");

        return sql.toString();
    }

    /**
     * 신규 INSERT용 이력 테이블 INSERT SQL 생성
     * INSERT한 데이터를 바로 이력 테이블에 기록
     */
    public String insertHistoryForNew(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = safeIdent((String) p.get("tableName"));
        final String pkColumnRaw = (String) p.get("pkColumn");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        Map<String, Object> feature = (Map<String, Object>) p.get("feature");

        if (feature == null) {
            return "SELECT 1 WHERE 1=0";
        }

        // 복합 PK 파싱
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    pkColumnTypes.put(trimmed, findType(columns, trimmed));
                }
            }
        }

        if (pkColumns.isEmpty()) {
            return "SELECT 1 WHERE 1=0";
        }

        // 원본 테이블의 컬럼 목록 생성
        StringBuilder colList = new StringBuilder();
        colList.append("\"gid\", \"geom\"");
        if (columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null) {
                    colList.append(", ").append(quoteIdent(safeIdent(colName)));
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schema).append(".").append(quoteIdent(table + "_h"))
           .append(" (").append(colList).append(", \"stts_cd\") ");
        sql.append("SELECT ").append(colList).append(", #{param.sttsCd} ");
        sql.append("FROM ").append(schema).append(".").append(quoteIdent(table)).append(" ");

        // WHERE 조건 (방금 INSERT한 데이터 - PK로 조회)
        sql.append("WHERE ");
        for (int j = 0; j < pkColumns.size(); j++) {
            if (j > 0) sql.append(" AND ");
            String pkColName = pkColumns.get(j);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" = #{param.feature.properties.").append(pkColName).append("}");
            if (needsTypeCast(pkType)) sql.append("::").append(pkType);
        }

        return sql.toString();
    }

    /**
     * 이력 테이블에 원본 데이터 직접 INSERT (배치용)
     * 프론트에서 넘어온 features 데이터를 직접 이력 테이블에 INSERT
     * gid는 원본 테이블에서 서브쿼리로 조회
     * geom은 geometryString으로 생성
     * 속성 컬럼은 properties에서 직접 바인딩
     */
    public String insertHistoryDirect(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = safeIdent((String) p.get("tableName"));
        final String pkColumnRaw = (String) p.get("pkColumn");
        final int srid = toInt(p.get("srid"), 5186);
        final int sourceSrid = toInt(p.get("sourceSrid"), 5186);

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) p.get("features");

        // 복합 PK 파싱
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    pkColumnTypes.put(trimmed, findType(columns, trimmed));
                }
            }
        }

        if (pkColumns.isEmpty() || features == null || features.isEmpty()) {
            return "SELECT 1 WHERE 1=0"; // no-op
        }

        // 첫 번째 feature의 properties에서 컬럼 목록 추출
        @SuppressWarnings("unchecked")
        Map<String, Object> firstProps = (Map<String, Object>) features.get(0).get("properties");
        List<String> propColumns = new ArrayList<>();
        if (firstProps != null && columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null && firstProps.containsKey(colName)) {
                    propColumns.add(colName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();

        // UNION ALL로 여러 건 INSERT
        for (int i = 0; i < features.size(); i++) {
            if (i > 0) sql.append(" UNION ALL ");

            if (i == 0) {
                // 첫 번째에서만 INSERT INTO 구문 추가
                sql.append("INSERT INTO ").append(schema).append(".").append(quoteIdent(table + "_h"))
                   .append(" (\"gid\", \"geom\"");
                for (String colName : propColumns) {
                    sql.append(", ").append(quoteIdent(safeIdent(colName)));
                }
                sql.append(", \"stts_cd\") ");
            }

            sql.append("SELECT ");

            // gid: 서브쿼리로 원본 테이블에서 조회
            sql.append("(SELECT \"gid\" FROM ").append(schema).append(".").append(quoteIdent(table))
               .append(" WHERE ");
            for (int j = 0; j < pkColumns.size(); j++) {
                if (j > 0) sql.append(" AND ");
                String pkColName = pkColumns.get(j);
                String pkCol = quoteIdent(safeIdent(pkColName));
                String pkType = pkColumnTypes.get(pkColName);
                sql.append(pkCol).append(" = #{param.features[").append(i).append("].properties.").append(pkColName).append("}");
                if (needsTypeCast(pkType)) sql.append("::").append(pkType);
            }
            sql.append("), ");

            // geom: geometryString으로 생성
            if (sourceSrid == srid) {
                sql.append("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                   .append(i).append("].geometryString})), ").append(srid).append(")");
            } else {
                sql.append("ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.features[")
                   .append(i).append("].geometryString})), ").append(sourceSrid).append("), ").append(srid).append(")");
            }

            // 속성 컬럼들: properties에서 직접 바인딩
            for (String colName : propColumns) {
                String dataType = findType(columns, colName);
                sql.append(", #{param.features[").append(i).append("].properties.").append(colName).append("}");
                if (needsTypeCast(dataType)) sql.append("::").append(dataType);
            }

            // stts_cd
            sql.append(", #{param.sttsCd}");
        }

        return sql.toString();
    }

    /**
     * 신규 INSERT용 이력 테이블에 원본 데이터 직접 INSERT (단건용)
     * INSERT된 단건 데이터를 이력 테이블에 저장
     * gid는 방금 INSERT한 데이터에서 서브쿼리로 조회
     */
    public String insertHistoryDirectForNew(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = safeIdent((String) p.get("tableName"));
        final String pkColumnRaw = (String) p.get("pkColumn");
        final int srid = toInt(p.get("srid"), 5186);
        final int sourceSrid = toInt(p.get("sourceSrid"), 5186);

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        Map<String, Object> feature = (Map<String, Object>) p.get("feature");

        if (feature == null) {
            return "SELECT 1 WHERE 1=0";
        }

        // 복합 PK 파싱
        List<String> pkColumns = new ArrayList<>();
        Map<String, String> pkColumnTypes = new HashMap<>();
        if (pkColumnRaw != null && !pkColumnRaw.isBlank()) {
            for (String pk : pkColumnRaw.split(",")) {
                String trimmed = pk.trim();
                if (!trimmed.isEmpty()) {
                    pkColumns.add(trimmed);
                    pkColumnTypes.put(trimmed, findType(columns, trimmed));
                }
            }
        }

        if (pkColumns.isEmpty()) {
            return "SELECT 1 WHERE 1=0";
        }

        // feature의 properties에서 컬럼 목록 추출
        @SuppressWarnings("unchecked")
        Map<String, Object> props = (Map<String, Object>) feature.get("properties");
        List<String> propColumns = new ArrayList<>();
        if (props != null && columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null && props.containsKey(colName)) {
                    propColumns.add(colName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schema).append(".").append(quoteIdent(table + "_h"))
           .append(" (\"gid\", \"geom\"");
        for (String colName : propColumns) {
            sql.append(", ").append(quoteIdent(safeIdent(colName)));
        }
        sql.append(", \"stts_cd\") ");

        sql.append("SELECT ");

        // gid: 서브쿼리로 방금 INSERT한 데이터에서 조회
        sql.append("(SELECT \"gid\" FROM ").append(schema).append(".").append(quoteIdent(table))
           .append(" WHERE ");
        for (int j = 0; j < pkColumns.size(); j++) {
            if (j > 0) sql.append(" AND ");
            String pkColName = pkColumns.get(j);
            String pkCol = quoteIdent(safeIdent(pkColName));
            String pkType = pkColumnTypes.get(pkColName);
            sql.append(pkCol).append(" = #{param.feature.properties.").append(pkColName).append("}");
            if (needsTypeCast(pkType)) sql.append("::").append(pkType);
        }
        sql.append("), ");

        // geom: geometryString으로 생성
        if (sourceSrid == srid) {
            sql.append("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.feature.geometryString})), ")
               .append(srid).append(")");
        } else {
            sql.append("ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.feature.geometryString})), ")
               .append(sourceSrid).append("), ").append(srid).append(")");
        }

        // 속성 컬럼들: properties에서 직접 바인딩
        for (String colName : propColumns) {
            String dataType = findType(columns, colName);
            sql.append(", #{param.feature.properties.").append(colName).append("}");
            if (needsTypeCast(dataType)) sql.append("::").append(dataType);
        }

        // stts_cd
        sql.append(", #{param.sttsCd}");

        return sql.toString();
    }

    /**
     * 신규 INSERT SQL 생성 + RETURNING gid
     * INSERT 후 생성된 gid를 반환
     */
    public String insertNewFeatureReturning(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = quoteIdent(safeIdent((String) p.get("tableName")));
        final int srid = toInt(p.get("srid"), 5186);
        final int sourceSrid = toInt(p.get("sourceSrid"), 5186);

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");
        @SuppressWarnings("unchecked")
        Map<String, Object> feature = (Map<String, Object>) p.get("feature");

        if (feature == null || columns == null) {
            return "SELECT NULL::bigint";
        }

        @SuppressWarnings("unchecked")
        Map<String, Object> props = (Map<String, Object>) feature.get("properties");

        // 컬럼 목록 구성 (gid 제외, geom 포함)
        List<String> insertColumns = new ArrayList<>();
        insertColumns.add("geom");

        // 시스템 컬럼은 별도 처리
        Set<String> systemColumns = new HashSet<>(Arrays.asList("gid", "frst_reg_dt", "frst_reg_id", "last_mdfcn_dt", "last_mdfcn_id"));

        for (Map<String, Object> col : columns) {
            String colName = (String) col.get("column_name");
            if (colName != null && !systemColumns.contains(colName) && props != null && props.containsKey(colName)) {
                // 값이 null인 경우 INSERT 대상에서 제외 (idn 포함 - DB 시퀀스로 자동 생성)
                Object value = props.get(colName);
                if (value != null) {
                    insertColumns.add(colName);
                }
            }
        }

        // 등록자/등록일시, 수정자/수정일시 추가
        insertColumns.add("frst_reg_id");
        insertColumns.add("frst_reg_dt");
        insertColumns.add("last_mdfcn_id");
        insertColumns.add("last_mdfcn_dt");

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schema).append(".").append(table).append(" (");

        // 컬럼 목록
        for (int i = 0; i < insertColumns.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append(quoteIdent(safeIdent(insertColumns.get(i))));
        }
        sql.append(") VALUES (");

        // 값 목록
        for (int i = 0; i < insertColumns.size(); i++) {
            if (i > 0) sql.append(", ");
            String colName = insertColumns.get(i);

            if ("geom".equals(colName)) {
                if (sourceSrid == srid) {
                    sql.append("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.feature.geometryString})), ").append(srid).append(")");
                } else {
                    sql.append("ST_Transform(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{param.feature.geometryString})), ")
                       .append(sourceSrid).append("), ").append(srid).append(")");
                }
            } else if ("frst_reg_id".equals(colName) || "last_mdfcn_id".equals(colName)) {
                sql.append("#{param.sessionUserId}");
            } else if ("frst_reg_dt".equals(colName) || "last_mdfcn_dt".equals(colName)) {
                sql.append("NOW()");
            } else {
                String dataType = findType(columns, colName);
                sql.append("#{param.feature.properties.").append(colName).append("}");
                if (needsTypeCast(dataType)) sql.append("::").append(dataType);
            }
        }
        sql.append(") RETURNING \"gid\"");

        return sql.toString();
    }

    /**
     * gid로 원본 테이블에서 SELECT하여 이력 테이블에 INSERT
     * INSERT 후 실제 DB 데이터로 이력 저장
     */
    public String insertHistoryByGid(Map<String, Object> root) {
        Map<String, Object> p = getParam(root);

        final String schema = quoteIdent(safeIdent((String) p.get("schemaName")));
        final String table = safeIdent((String) p.get("tableName"));

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) p.get("columns");

        // 원본 테이블의 컬럼 목록 생성 (gid 포함, geom 포함)
        StringBuilder colList = new StringBuilder();
        colList.append("\"gid\", \"geom\"");
        if (columns != null) {
            for (Map<String, Object> col : columns) {
                String colName = (String) col.get("column_name");
                if (colName != null) {
                    colList.append(", ").append(quoteIdent(safeIdent(colName)));
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        // INSERT INTO schema.table_h (컬럼들, stts_cd)
        sql.append("INSERT INTO ").append(schema).append(".").append(quoteIdent(table + "_h"))
           .append(" (").append(colList).append(", \"stts_cd\") ");

        // SELECT 컬럼들, #{param.sttsCd} FROM schema.table WHERE gid = #{param.gid}
        sql.append("SELECT ").append(colList).append(", #{param.sttsCd} ");
        sql.append("FROM ").append(schema).append(".").append(quoteIdent(table)).append(" ");
        sql.append("WHERE \"gid\" = #{param.gid}");

        return sql.toString();
    }

    // ======== Helper Methods ========

    @SuppressWarnings("unchecked")
    private static Map<String, Object> getParam(Map<String, Object> root) {
        Object direct = root.get("param");
        if (direct instanceof Map) return (Map<String, Object>) direct;
        return root;
    }

    private static int toInt(Object o, int def) {
        if (o == null) return def;
        if (o instanceof Number n) return n.intValue();
        try {
            return Integer.parseInt(o.toString().trim());
        } catch (NumberFormatException e) {
            return def;
        }
    }

    private static String findType(List<Map<String, Object>> columns, String colName) {
        if (columns == null) return "varchar";
        for (Map<String, Object> c : columns) {
            String name = String.valueOf(c.get("column_name"));
            if (colName.equalsIgnoreCase(name)) {
                Object dt = c.get("data_type");
                String t = (dt == null) ? "varchar" : String.valueOf(dt);
                return (t.isBlank() ? "varchar" : t);
            }
        }
        return "varchar";
    }

    /** identifier 화이트리스트: 영문/숫자/_ 만 허용 */
    private static String safeIdent(String raw) {
        if (raw == null) return "";
        return raw.replaceAll("[^A-Za-z0-9_]", "");
    }

    /** "ident" 로 감싸기 */
    private static String quoteIdent(String ident) {
        if (ident == null || ident.isBlank()) return "\"\"";
        return "\"" + ident + "\"";
    }
}