package incheon.uis.uld.service.impl;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import incheon.cmm.g2f.layer.mapper.G2FLayerMapper;
import incheon.uis.uld.mapper.G2FLayerMapperX;
import incheon.uis.uld.mapper.UisFileUploadMapper;
import incheon.uis.uld.service.GeoJsonSyncService;
import incheon.uis.uld.service.SyncMode;
import incheon.uis.uld.service.UisFileUploadService;
import incheon.uis.uld.service.UisGdalConversionService;
import incheon.uis.uld.vo.FileUploadVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.io.File;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicLong;

@Service
@RequiredArgsConstructor
@Slf4j
public class UisFileUploadServiceImpl implements UisFileUploadService {
    @Autowired
    private G2FLayerMapper mapper;
    @Autowired
    private G2FLayerMapperX g2fLayerMapperX;

    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    @Autowired(required = false)
    private UisFileUploadMapper fileUploadMapper;
    @Autowired
    private UisGdalConversionService gadlConversionService;
    @Autowired
    DataSource dataSource;
    @Autowired(required = false)
    private GeoJsonSyncService geoJsonSyncService;
    @Autowired
    private G2FLayerMapper g2fLayerMapper;   // 기존에 사용하시던 Mapper (bean 등록되어 있어야 함)

    @Autowired
    private PlatformTransactionManager txManager;

    private TransactionTemplate txReqNew;

    @Value("${geojson.sync.enabled:true}")
    private boolean syncEnabled;

    // ===== [ADD] shapefile 경로/정책 프로퍼티 =====
    @Value("${uis.fileStorePath}")
    private String shapefileBasePath;  // 예) /home/incheon/env/apps/resources/uis/ums/shp/

    @Value("${Globals.fileUpload.maxUploadSize}")
    private long shapefileMaxSize;
    @Value("${uis.schema.name:icuis}")
    private String schemaNm;

    @Value("${Globals.allowed.extensions:zip}")
    private String allowedExtensionsProp;  // 기본 zip (콤마로 다중 허용 가능)

    private final AtomicLong id = new AtomicLong();

    @PostConstruct
    private void __initTxTemplate() {
        txReqNew = new TransactionTemplate(txManager);
        txReqNew.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 테이블별 독립 트랜잭션
        txReqNew.setReadOnly(false);
    }

    // 그대로 교체 (핵심은 !previewOnly 블록에서만 메타/DB 작업)
    @Override
    public List<String> uploadFile(MultipartFile file, FileUploadVO vo,
                                   List<String> shapeColumns, String firstColumn, String secondColumn,
                                   String actionType, String pkColumn, String tableName,
                                   boolean previewOnly) throws Exception {

        if (file == null || file.isEmpty()) return Collections.emptyList();

        // 확장자/사이즈 검증
        String originalFileName = file.getOriginalFilename();
        if (!isAllowedExtension(originalFileName)) {
            throw new IllegalArgumentException("허용되지 않는 확장자입니다. (허용: " + allowedExtensionsProp + ")");
        }
        if (file.getSize() > shapefileMaxSize) {
            throw new IllegalArgumentException("파일 용량 초과: " + file.getSize() + " bytes (최대 " + shapefileMaxSize + ")");
        }

        // 저장 디렉토리: {base}/YYYY/MM/
        Path ymDir = ensureYearMonthDir();

        // 저장 파일명: shp_tmp{yyyyMMddHHmmss}.zip
        String stored = buildTargetZipName();

        // 최종 저장 경로
        Path targetZipPath = ymDir.resolve(stored);

        // 업로드 저장 (원샷 저장)
        file.transferTo(targetZipPath.toFile());

        // (참고) unzip 경로 규칙: {base}/YYYY/MM/{파일명_확장자제외}/
        String unzipDir = ymDir.resolve(stored.substring(0, stored.lastIndexOf('.'))).toString();

        // 변환 호출 (기존 로직 유지) : fullPath=zip 경로, stored=저장파일명
        String fullPath = targetZipPath.toString();
        List<String> geojsonPaths = gadlConversionService.convertToGeoJson(
                fullPath, stored, vo.getFilecoordinate(), vo.getEncoding(),
                shapeColumns, firstColumn, secondColumn
        );

        // previewOnly==true 이면 DB/메타 저장/동기화 수행하지 않음 (기존 규칙 유지)
        if (!previewOnly) {
            SyncMode mode = toMode(actionType);
            String pk = (pkColumn == null || pkColumn.isBlank()) ? "gid" : pkColumn;
            String overrideTable = (tableName == null || tableName.isBlank()) ? null : tableName;

            // 필요한 경우에만 DB 동기화(현재 주석 유지 가능)
            // Map<String, String> report = geoJsonSyncService.syncFiles(geojsonPaths, 4326, mode, pk, overrideTable);

            // 파일 메타 저장 (경로/파일명만 신규 규칙으로 반영)
            vo.setId(id.incrementAndGet());
            vo.setFileName(stored);
            vo.setOriginalFileName(originalFileName);
            vo.setFileType(file.getContentType());
            vo.setFilePath(fullPath);
            vo.setSize(file.getSize());

            vo.setDwnldFileNm(originalFileName);
            vo.setSrvrStrgFileNm(stored);
            vo.setAtchFileExtnNm(getExt(originalFileName));
            // unzipDir을 별도로 보관하려면 아래 라인을 unzipDir로 교체 가능
            vo.setFldrPathNm(fullPath);
            vo.setOrgnlFileSz(file.getSize());
            vo.setRmrkCn(null);
            vo.setUseYn("Y");
            vo.setFrstRegId("ADMIN");
            vo.setLastMdfcnId("ADMIN");
            if (geojsonPaths != null && !geojsonPaths.isEmpty()) {
                vo.setGeoJsonPath(String.join(",", geojsonPaths));
            }
            if (fileUploadMapper != null) {
                try { fileUploadMapper.save(vo);
                } catch (Exception e) {
                    log.error("fileUploadMapper.save 실패, vo={}", vo, e);
                }
            }
        }

        return (geojsonPaths != null) ? geojsonPaths : Collections.emptyList();
    }


    // === [ADD-ONLY] 미리보기 결과 저장 엔트리 ===
    // === [ADD-ONLY] 미리보기 결과 저장 엔트리 (receiver: columns/features/srid 규격) ===

    @Override
    public int saveFromPreview(
            List<Map<String,Object>> geojsonList,
            String coordinate,
            List<String> tableNameList,
            String actionType,
            String pkColumn,   // 무시: 메타에서만 PK 결정
            Integer srid
    ) {
        if (geojsonList == null || geojsonList.isEmpty()) {
            throw new IllegalArgumentException("저장할 GeoJSON 목록이 비어 있습니다.");
        }

        final int sourceSrid = 3857;
        final int targetSrid = (srid != null && srid > 0) ? srid : sourceSrid;

        String finalTable = "";
        if (tableNameList != null && !tableNameList.isEmpty() && tableNameList.get(0) != null) {
            finalTable = tableNameList.get(0).trim();
        }
        if (finalTable.isBlank()) {
            finalTable = extractFirstNonBlankNameFromGeojsonList(geojsonList);
        }
        if (finalTable == null || finalTable.isBlank()) {
            throw new IllegalArgumentException("저장할 테이블명이 비어 있습니다. (화면 입력 또는 GeoJSON.name 필요)");
        }

        String schemaName = schemaNm;
        String pureTable  = finalTable;
        if (finalTable.contains(".")) {
            String[] sp = finalTable.split("\\.", 2);
            schemaName = sp[0].trim();
            pureTable  = sp[1].trim();
        }
        if (pureTable.isBlank()) {
            throw new IllegalArgumentException("테이블명이 비어 있습니다. (schema.table 형식 점검)");
        }

        schemaName = schemaName.replace("\"", "").toLowerCase();
        pureTable  = pureTable.replace("\"", "").toLowerCase();

        String geomColumn = "geom";
        try {
            String resolved = resolveGeometryColumn(schemaName, pureTable);
            if (resolved != null && !resolved.isBlank()) geomColumn = resolved;
        } catch (Exception ignore) {
            geomColumn = "geom";
        }

        // name 별 그룹핑
        Map<String, List<Map<String, Object>>> rowsByTable = new LinkedHashMap<>();
        for (Map<String, Object> oneRoot : geojsonList) {
            if (oneRoot == null) continue;

            String tableHint = extractTableNameFromRoot(oneRoot);
            if (tableHint == null || tableHint.isBlank()) tableHint = pureTable;
            tableHint = sanitizeTableName(tableHint);

            // 미리보기에서 내려온 구조(geometryString / properties)를 그대로 펼침
            List<Map<String, Object>> rowsOfThisRoot = extractRowsDirectlyFromRoot(oneRoot);
            if (rowsOfThisRoot != null && !rowsOfThisRoot.isEmpty()) {
                rowsByTable.computeIfAbsent(tableHint, k -> new ArrayList<>()).addAll(rowsOfThisRoot);
            }
        }

        if (rowsByTable.isEmpty()) {
            throw new IllegalArgumentException("저장할 데이터가 없습니다. (모든 FeatureCollection이 비어있음)");
        }

        // ★ 액션 고정 분기 (자동 판단 X)
        final String normalizedAction = normalizeActionType(actionType); // INSERT / UPDATE / DELETE만 인정
        final boolean isInsertRoute = "INSERT".equals(normalizedAction);
        final boolean isUpdateRoute = "UPDATE".equals(normalizedAction);
        final boolean isDeleteRoute = "DELETE".equals(normalizedAction);

        int totalAffected = 0;

        for (Map.Entry<String, List<Map<String, Object>>> entry : rowsByTable.entrySet()) {
            final String tablePerGroup = entry.getKey();               // ex) rdl_bejn_p
            final List<Map<String, Object>> rowsPerGroup = entry.getValue();
            if (rowsPerGroup == null || rowsPerGroup.isEmpty()) continue;

            // 4-1) 대상 테이블 컬럼 메타 조회
            final List<Map<String, Object>> dbColumnsRaw;
            try {
                dbColumnsRaw = g2fLayerMapper.uisSelectTableColumns(schemaName, tablePerGroup);
            } catch (Exception e) {
                log.error("메타조회 실패: {}.{}", schemaName, tablePerGroup, e);
                throw e;
            }
            if (dbColumnsRaw == null || dbColumnsRaw.isEmpty()) {
                System.out.println("[메타없음] " + schemaName + "." + tablePerGroup);
                continue;
            }

            // 4-2) PK 컬럼 결정: isPrimaryKey='YES' 만 인정 (fallback 없음)
            final String pkColumnDb = resolvePkFromMeta(dbColumnsRaw);

            // DELETE/UPDATE는 PK 없으면 진행 불가
            if ((isUpdateRoute || isDeleteRoute) && (pkColumnDb == null || pkColumnDb.isBlank())) {
                System.out.println("[SKIP] " + normalizedAction + "인데 PK 미발견 → " + schemaName + "." + tablePerGroup);
                continue;
            }

            // 4-3) DB 컬럼 케이스 매핑
            Map<String, String> colCaseMap = new HashMap<>();
            for (Map<String, Object> c : dbColumnsRaw) {
                Object colObj = c.get("column_name");
                if (colObj == null) continue;
                String original = String.valueOf(colObj);
                colCaseMap.put(original.toLowerCase(Locale.ROOT), original);
            }

            // 4-4) NOT NULL(필수) 컬럼 수집 (geom/OBJECTID 제외)
            final Set<String> requiredColsLower = new HashSet<>();
            for (Map<String, Object> c : dbColumnsRaw) {
                String col = String.valueOf(c.get("column_name"));
                String isNull = String.valueOf(c.getOrDefault("is_nullable", "YES")); // YES/NO
                if (col == null) continue;
                if (col.equalsIgnoreCase(geomColumn)) continue;
                if (col.equalsIgnoreCase("OBJECTID")) continue;
                if (col.equalsIgnoreCase("IDN")) continue;
                if ("NO".equalsIgnoreCase(isNull)) {
                    requiredColsLower.add(col.toLowerCase(Locale.ROOT));
                }
            }

            // 4-5) mapper에 넘길 columns (메타 그대로, geom/OBJECTID 제외)
            List<Map<String, Object>> columnsParamDb = new ArrayList<>();
            for (Map<String, Object> c : dbColumnsRaw) {
                Object colObj = c.get("column_name");
                if (colObj == null) continue;
                String col = String.valueOf(colObj);
                if (col.equalsIgnoreCase(geomColumn)) continue;
                if (col.equalsIgnoreCase("OBJECTID")) continue;

                Map<String, Object> one = new LinkedHashMap<>();
                one.put("column_name", col);
                Object dt = c.get("data_type");
                one.put("data_type", (dt != null) ? String.valueOf(dt) : "varchar");
                columnsParamDb.add(one);
            }

            // ★ INSERT 경로에서는 PK 컬럼도 columns에 반드시 포함되도록 보장 (NOT NULL 방지)
            if (isInsertRoute && pkColumnDb != null && !pkColumnDb.isBlank()) {
                boolean hasPkInColumns = false;
                for (Map<String, Object> m : columnsParamDb) {
                    if (pkColumnDb.equalsIgnoreCase(String.valueOf(m.get("column_name")))) {
                        hasPkInColumns = true; break;
                    }
                }
                if (!hasPkInColumns) {
                    String pkType = "varchar";
                    for (Map<String,Object> m : dbColumnsRaw) {
                        if (pkColumnDb.equalsIgnoreCase(String.valueOf(m.get("column_name")))) {
                            Object dt = m.get("data_type");
                            pkType = (dt != null) ? String.valueOf(dt) : "varchar";
                            break;
                        }
                    }
                    Map<String,Object> pkColMeta = new LinkedHashMap<>();
                    pkColMeta.put("column_name", pkColumnDb);
                    pkColMeta.put("data_type", pkType);
                    columnsParamDb.add(0, pkColMeta); // 앞에 추가
                }
            }

            // 4-6) featuresParamDb 구성 (+ UPDATE 시 geometry 보강 대상 수집)
            List<Map<String, Object>> featuresParamDb = new ArrayList<>();
            List<Object> needBackfillPkList = new ArrayList<>(); // UPDATE에서 geometry 없는 PK 모음

            int skippedByNoGeometry = 0;
            int skippedByRequired   = 0; // 모니터링용
            int skippedByNoPk       = 0;

            // ★ DELETE용 PK 수집
            List<Object> deletePkValues = new ArrayList<>();

            for (Map<String, Object> row : rowsPerGroup) {
                if (row == null) continue;

                // 공통: props → DB 컬럼명 케이스로 매핑
                Map<String, Object> mappedProps = new LinkedHashMap<>();
                Object propsObj = row.get("properties");
                if (propsObj instanceof Map<?, ?> props) {
                    for (Map.Entry<?, ?> e : props.entrySet()) {
                        Object kObj = e.getKey();
                        if (!(kObj instanceof String k)) continue;
                        String dbKey = colCaseMap.get(k.toLowerCase(Locale.ROOT));
                        if (dbKey != null
                                && !dbKey.equalsIgnoreCase(geomColumn)
                                && !dbKey.equalsIgnoreCase("OBJECTID")) {
                            Object val = e.getValue();
                            mappedProps.put(dbKey, val);
                        }
                    }
                }

                if (isDeleteRoute) {
                    // DELETE: PK만 뽑아서 목록에 추가
                    Object pkVal = null;
                    if (pkColumnDb != null) {
                        if (mappedProps.containsKey(pkColumnDb)) {
                            pkVal = mappedProps.get(pkColumnDb);
                        } else if (propsObj instanceof Map<?, ?> props) {
                            for (Map.Entry<?, ?> e : props.entrySet()) {
                                Object kObj = e.getKey();
                                if (kObj instanceof String k && k.equalsIgnoreCase(pkColumnDb)) {
                                    pkVal = e.getValue(); break;
                                }
                            }
                        }
                    }
                    if (pkVal != null && !(pkVal instanceof String && ((String) pkVal).trim().isEmpty())) {
                        deletePkValues.add(pkVal);
                    } else {
                        skippedByNoPk++;
                    }
                    continue; // 다음 row
                }

                if (isUpdateRoute) {
                    // ===== UPDATE =====
                    if (pkColumnDb == null || pkColumnDb.isBlank()) {
                        skippedByNoPk++;
                        continue;
                    }

                    Object pkVal = null;
                    if (mappedProps.containsKey(pkColumnDb)) {
                        pkVal = mappedProps.get(pkColumnDb);
                    } else if (propsObj instanceof Map<?, ?> props) {
                        for (Map.Entry<?, ?> e : props.entrySet()) {
                            Object kObj = e.getKey();
                            if (kObj instanceof String k && k.equalsIgnoreCase(pkColumnDb)) {
                                pkVal = e.getValue();
                                break;
                            }
                        }
                    }
                    if (pkVal == null || (pkVal instanceof String && ((String) pkVal).trim().isEmpty())) {
                        skippedByNoPk++;
                        continue;
                    }

                    String geometryString = null;
                    Object g = row.get("geometryString");
                    if (g != null) {
                        geometryString = String.valueOf(g).trim();
                        if (geometryString.isEmpty() || "null".equalsIgnoreCase(geometryString)) geometryString = null;
                    }
                    if (geometryString == null) {
                        Object alt = row.get("geometry");
                        if (alt != null) {
                            geometryString = String.valueOf(alt).trim();
                            if (geometryString.isEmpty() || "null".equalsIgnoreCase(geometryString)) geometryString = null;
                        }
                    }

                    Map<String, Object> oneFeature = new HashMap<>();
                    // ★ 확장 UPDATE는 gid 자리에 '동적 PK 값'을 넣어 사용
                    oneFeature.put("gid", pkVal);
                    oneFeature.put("properties", mappedProps);
                    oneFeature.put("pkColumn", pkColumnDb);

                    if (geometryString != null) {
                        oneFeature.put("geometryString", geometryString);
                    } else {
                        needBackfillPkList.add(pkVal); // 나중에 DB geom 보강
                    }

                    // ★ UPDATE에서는 PK 변경 방지
                    mappedProps.remove(pkColumnDb);

                    featuresParamDb.add(oneFeature);

                } else if (isInsertRoute) {
                    // ===== INSERT =====
                    String geometryString = null;
                    Object g = row.get("geometryString");
                    if (g != null) {
                        geometryString = String.valueOf(g).trim();
                        if (geometryString.isEmpty() || "null".equalsIgnoreCase(geometryString)) geometryString = null;
                    }
                    if (geometryString == null) {
                        Object alt = row.get("geometry");
                        if (alt != null) {
                            geometryString = String.valueOf(alt).trim();
                            if (geometryString.isEmpty() || "null".equalsIgnoreCase(geometryString)) geometryString = null;
                        }
                    }
                    if (geometryString == null) {
                        skippedByNoGeometry++;
                        continue;
                    }

                    // INSERT: null/빈문자열 속성 제거
                    Iterator<Map.Entry<String, Object>> it = mappedProps.entrySet().iterator();
                    while (it.hasNext()) {
                        Map.Entry<String, Object> en = it.next();
                        Object v = en.getValue();
                        if (v == null) {
                            it.remove();
                        } else if (v instanceof String && ((String) v).trim().isEmpty()) {
                            it.remove();
                        }
                    }
                    // ★ INSERT에서는 PK 제거하지 않음 (NOT NULL 대비)

                    // 모니터링만: NOT NULL인데 값이 빠진 컬럼이 있는지
                    boolean missingRequired = false;
                    if (!requiredColsLower.isEmpty()) {
                        for (String reqLower : requiredColsLower) {
                            String caseKey = colCaseMap.getOrDefault(reqLower, reqLower);
                            Object v = mappedProps.get(caseKey);
                            if (v == null || (v instanceof String && ((String) v).trim().isEmpty())) {
                                missingRequired = true;
                                break;
                            }
                        }
                    }
                    if (missingRequired) {
                        skippedByRequired++;
                    }

                    Map<String, Object> oneFeature = new HashMap<>();
                    oneFeature.put("geometryString", geometryString);
                    oneFeature.put("properties", mappedProps);
                    oneFeature.put("pkColumn", pkColumnDb);
                    featuresParamDb.add(oneFeature);
                }
            }

            // UPDATE: geometryString 비어있는 항목들, 기존 DB geom으로 보강
            if (isUpdateRoute && !needBackfillPkList.isEmpty()) {
                Map<Object, String> backfilled = loadExistingGeomAsGeoJSON(
                        schemaName, tablePerGroup, pkColumnDb, geomColumn, needBackfillPkList
                );
                Iterator<Map<String, Object>> it = featuresParamDb.iterator();
                while (it.hasNext()) {
                    Map<String, Object> f = it.next();
                    if (!f.containsKey("geometryString")) {
                        Object gid = f.get("gid");
                        String gj = backfilled.get(gid);
                        if (gj != null && !gj.isBlank()) {
                            f.put("geometryString", gj);
                        } else {
                            // 끝내 보강 실패 → 스킵
                            skippedByNoGeometry++;
                            it.remove();
                        }
                    }
                }
            }

            // UPDATE: 속성 교집합만 남기기(메퍼 validColumns 보조)
            if (isUpdateRoute) {
                List<Set<String>> propKeySets = new ArrayList<>();
                for (Map<String, Object> f : featuresParamDb) {
                    @SuppressWarnings("unchecked")
                    Map<String, Object> props = (Map<String, Object>) f.get("properties");

                    Set<String> keys = new HashSet<>();
                    if (props != null) {
                        for (String k : props.keySet()) {
                            if (k == null) continue;
                            if (k.equalsIgnoreCase(pkColumnDb)) continue;
                            if (k.equalsIgnoreCase(geomColumn)) continue;
                            if (k.equalsIgnoreCase("OBJECTID")) continue;
                            keys.add(k.toLowerCase(Locale.ROOT));
                        }
                    }
                    propKeySets.add(keys);
                }

                Set<String> commonLower = new HashSet<>();
                if (!propKeySets.isEmpty()) {
                    commonLower.addAll(propKeySets.get(0));
                    for (int i = 1; i < propKeySets.size(); i++) {
                        commonLower.retainAll(propKeySets.get(i));
                    }
                }

                if (commonLower.isEmpty()) {
                    for (Map<String, Object> f : featuresParamDb) {
                        f.put("properties", new LinkedHashMap<String, Object>());
                    }
                    columnsParamDb = new ArrayList<>();
                } else {
                    List<Map<String, Object>> newColumns = new ArrayList<>();
                    for (Map<String, Object> c : dbColumnsRaw) {
                        Object colObj = c.get("column_name");
                        if (colObj == null) continue;
                        String col = String.valueOf(colObj);

                        if (col.equalsIgnoreCase(geomColumn)) continue;
                        if (col.equalsIgnoreCase("OBJECTID")) continue;
                        if (pkColumnDb != null && col.equalsIgnoreCase(pkColumnDb)) continue;

                        if (commonLower.contains(col.toLowerCase(Locale.ROOT))) {
                            Map<String, Object> one = new LinkedHashMap<>();
                            one.put("column_name", col);
                            Object dt = c.get("data_type");
                            one.put("data_type", (dt != null) ? String.valueOf(dt) : "varchar");
                            newColumns.add(one);
                        }
                    }
                    columnsParamDb = newColumns;

                    for (Map<String, Object> f : featuresParamDb) {
                        @SuppressWarnings("unchecked")
                        Map<String, Object> props = (Map<String, Object>) f.get("properties");
                        Map<String, Object> filtered = new LinkedHashMap<>();
                        if (props != null) {
                            for (String k : props.keySet()) {
                                String lower = k.toLowerCase(Locale.ROOT);
                                if (commonLower.contains(lower)) {
                                    filtered.put(k, props.get(k));
                                }
                            }
                        }
                        f.put("properties", filtered);
                    }

                    if (!featuresParamDb.isEmpty()) {
                        @SuppressWarnings("unchecked")
                        Map<String, Object> firstProps = (Map<String, Object>) featuresParamDb.get(0).get("properties");
                        if (firstProps == null) {
                            firstProps = new LinkedHashMap<>();
                            featuresParamDb.get(0).put("properties", firstProps);
                        }
                        Set<String> firstLower = new HashSet<>();
                        for (String k : firstProps.keySet()) firstLower.add(k.toLowerCase(Locale.ROOT));
                        for (String lower : commonLower) {
                            if (!firstLower.contains(lower)) {
                                String original = colCaseMap.getOrDefault(lower, lower);
                                firstProps.put(original, null);
                            }
                        }
                    }
                }
            }

            if (!isDeleteRoute && featuresParamDb.isEmpty()) {
                System.out.println("[features 비어있음] " + schemaName + "." + tablePerGroup
                        + " (rows:" + rowsPerGroup.size()
                        + ", no-geom:" + skippedByNoGeometry
                        + ", no-required:" + skippedByRequired
                        + ", no-pk:" + skippedByNoPk + ")");
                continue;
            }

            // 최종 경로/건수 로깅
            System.out.println("[DECISION] route=" + normalizedAction
                    + " table=" + schemaName + "." + tablePerGroup
                    + " features=" + (isDeleteRoute ? deletePkValues.size() : featuresParamDb.size())
                    + " columns=" + columnsParamDb.size()
                    + " pk=" + pkColumnDb);

            // 4-7) mapper 호출
            try {
                if (isUpdateRoute) {
                    Map<String, Object> params = new HashMap<>();
                    params.put("schemaName", schemaName);
                    params.put("tableName",  tablePerGroup);
                    params.put("srid",       targetSrid);
                    params.put("columns",    columnsParamDb);
                    params.put("features",   featuresParamDb);
                    params.put("x_pk_column", pkColumnDb);

                    int updated = g2fLayerMapperX.batchUpdateLayerFeatures(params);
                    totalAffected += updated;
                    try { tryInsertHistory("UPDATE", schemaName, tablePerGroup, updated);
                    } catch (Exception ignore) {
                        log.warn(
                                "히스토리 INSERT 실패 (무시됨) action=UPDATE, schema={}, table={}, data={}",
                                schemaName, tablePerGroup, updated, ignore
                        );
                    }

                } else if (isInsertRoute) {
                    Map<String, Object> params = new HashMap<>();
                    params.put("schemaName", schemaName);
                    params.put("tableName",  tablePerGroup);
                    params.put("srid",       targetSrid);
                    params.put("columns",    columnsParamDb);
                    params.put("features",   featuresParamDb);

                    List<Map<String, Object>> insertedList = g2fLayerMapper.batchInsertLayerFeatures(params);
                    int inserted = (insertedList != null) ? insertedList.size() : 0;
                    totalAffected += inserted;
                    try {
                        tryInsertHistory("INSERT", schemaName, tablePerGroup, inserted);
                    } catch (Exception ignore) {
                        log.warn(
                                "히스토리 INSERT 실패 (무시됨) action=UPDATE, schema={}, table={}, data={}",
                                schemaName, tablePerGroup, inserted, ignore
                        );
                    }

                } else if (isDeleteRoute) {
                    if (deletePkValues.isEmpty()) {
                        System.out.println("[SKIP] DELETE pk 비어있음 → " + schemaName + "." + tablePerGroup);
                        continue;
                    }
                    Map<String,Object> delParams = new HashMap<>();
                    delParams.put("schemaName", schemaName);
                    delParams.put("tableName",  tablePerGroup);
                    delParams.put("x_pk_column", pkColumnDb);
                    delParams.put("pkValues",    deletePkValues);

                    int deleted = g2fLayerMapperX.batchSoftDeleteByPk(delParams); // use_yn='Y'
                    totalAffected += deleted;
                    try {
                        tryInsertHistory("DELETE", schemaName, tablePerGroup, deleted);
                    } catch (Exception ignore) {
                        log.warn(
                                "히스토리 INSERT 실패 (무시됨) action=UPDATE, schema={}, table={}, data={}",
                                schemaName, tablePerGroup, deleted, ignore
                        );
                    }
                }

            } catch (Exception ex) {

                log.warn("[batch" + normalizedAction + " 실패] "
                        + schemaName + "." + tablePerGroup + " : " + ex);
                throw ex;
            }
        }

        return totalAffected;
    }



    private String normalizeActionType(String actionType) {
        if (actionType == null) return "INSERT";
        String t = actionType.trim().toLowerCase(Locale.ROOT);
        // 업데이트 계열
        if (t.equals("수정") || t.contains("update") || t.equals("upd") || t.equals("u")
                || t.contains("edit") || t.contains("변경")) {
            return "UPDATE";
        }
        // (참고) 삭제는 나중에 쓸 수 있게 남겨둠
        if (t.equals("삭제") || t.contains("delete") || t.equals("del") || t.equals("d")) {
            return "DELETE";
        }
        // 저장/등록/기본
        return "INSERT";
    }



    /**
     * information_schema 메타에서 PK 컬럼명 추출
     * - isPrimaryKey = 'YES' 인 첫 컬럼만 반환
     * - 없으면 null (UPDATE는 스킵)
     */
    // ② PK 추출 헬퍼: isPrimaryKey / isprimarykey / is_pk 모두 대응
    private String resolvePkFromMeta(List<Map<String, Object>> cols) {
        if (cols == null) return null;
        for (Map<String, Object> c : cols) {
            Object flagObj = c.get("isPrimaryKey");
            if (flagObj == null) flagObj = c.get("isprimarykey");
            if (flagObj == null) flagObj = c.get("is_pk");
            if (flagObj == null) flagObj = c.get("pk");
            String flag = flagObj == null ? "" : String.valueOf(flagObj).trim();
            boolean isPk = "YES".equalsIgnoreCase(flag) || "Y".equalsIgnoreCase(flag)
                    || "TRUE".equalsIgnoreCase(flag) || "1".equals(flag);
            if (isPk) return String.valueOf(c.get("column_name")).trim();
        }
        return null;
    }

    private boolean hasPkInRows(List<Map<String, Object>> rows, String pkCol) {
        if (rows == null || rows.isEmpty() || pkCol == null || pkCol.isBlank()) return false;
        for (Map<String, Object> row : rows) {
            Object propsObj = row.get("properties");
            if (propsObj instanceof Map<?, ?> props) {
                for (Map.Entry<?, ?> e : props.entrySet()) {
                    Object k = e.getKey();
                    if (k instanceof String ks && ks.equalsIgnoreCase(pkCol)) {
                        Object v = e.getValue();
                        if (v != null && !(v instanceof String s && s.trim().isEmpty())) return true;
                    }
                }
            }
        }
        return false;
    }





    // 모든 geometry 타입을 안전하게 JSON 문자열로 뽑아 rows 형태로 만든다.
    @SuppressWarnings("unchecked")
    private List<Map<String, Object>> extractRowsDirectlyFromRoot(Map<String, Object> root) {
        List<Map<String, Object>> out = new ArrayList<>();
        if (root == null) return out;

        Object featuresObj = root.get("features");
        if (!(featuresObj instanceof List<?> features)) return out;

        for (Object fo : features) {
            if (!(fo instanceof Map<?, ?> feat)) continue;

            // 1) geometry → geometryString (Map이든 String이든 전부 JSON 문자열화)
            Object geomNode = feat.get("geometry");
            if (geomNode == null) continue;

            String geometryString;
            if (geomNode instanceof String s) {
                geometryString = s;
            } else {
                geometryString = toJsonString(geomNode); // 이미 클래스에 있는 유틸 사용
            }
            if (geometryString == null || geometryString.isBlank() || "null".equalsIgnoreCase(geometryString)) {
                continue; // geometry 실질 없음
            }

            // 2) properties 그대로 보존(맵만 보장)
            Map<String, Object> props = null;
            Object propsObj = feat.get("properties");
            if (propsObj instanceof Map<?, ?> p) {
                props = new LinkedHashMap<>();
                for (Map.Entry<?, ?> e : p.entrySet()) {
                    Object k = e.getKey();
                    if (k instanceof String ks) props.put(ks, e.getValue());
                }
            } else {
                props = new LinkedHashMap<>();
            }

            Map<String, Object> row = new LinkedHashMap<>();
            row.put("geometryString", geometryString);
            row.put("properties", props);
            out.add(row);
        }

        return out;
    }



    /**
     * UPDATE용: geometryString이 비어온 경우, DB의 기존 geom을 GeoJSON으로 읽어와 보강한다.
     */
    private Map<Object, String> loadExistingGeomAsGeoJSON(
            String schemaName,
            String tableName,
            String pkColumn,
            String geomColumn,
            Collection<Object> pkValues
    ) {
        if (pkValues == null || pkValues.isEmpty()) return Collections.emptyMap();

        // 따옴표/케이스 정리
        final String qSchema = "\"" + schemaName.replace("\"", "") + "\"";
        final String qTable  = "\"" + tableName.replace("\"", "") + "\"";
        final String qPk     = "\"" + pkColumn.replace("\"", "") + "\"";
        final String qGeom   = "\"" + geomColumn.replace("\"", "") + "\"";

        String placeholders = pkValues.stream().map(v -> "?").collect(java.util.stream.Collectors.joining(","));
        String sql = "SELECT " + qPk + " AS pk, ST_AsGeoJSON(" + qGeom + ") AS gj "
                + "FROM " + qSchema + "." + qTable + " "
                + "WHERE " + qPk + " IN (" + placeholders + ")";

        Map<Object, String> out = new HashMap<>();
        java.sql.Connection conn = null;
        java.sql.PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(sql);

            int idx = 1;
            for (Object v : pkValues) {
                ps.setObject(idx++, v);
            }

            rs = ps.executeQuery();
            while (rs.next()) {
                Object pk = rs.getObject("pk");
                String gj = rs.getString("gj"); // null 가능
                if (pk != null && gj != null && !gj.isBlank()) {
                    out.put(pk, gj);
                }
            }
        } catch (Exception e) {
            log.error("geom 보강 실패: {}.{}", schemaName, tableName, e);
        } finally {
            try {
                if (rs != null) rs.close();
            } catch (Exception e) {
                log.warn("ResultSet close 실패", e);
            }
            try {
                if (ps != null) ps.close();
            } catch (Exception e) {
                log.warn("PreparedStatement close 실패", e);
            }
            try {
                if (conn != null) conn.close();
            } catch (Exception e) {
                log.warn("Connection close 실패", e);
            }
        }
        return out;
    }


    @SuppressWarnings("unchecked")
    private String extractGeometryStringFromRow(Map<String, Object> row) {
        // 1) 대표 후보 키들 우선
        String[] keys = { "geometryString", "geometryJson", "geometry", "geom", "the_geom", "shape" };
        for (String k : keys) {
            Object v = row.get(k);
            String s = normalizeGeometryToString(v);
            if (s != null) return s;
        }

        // 2) 혹시 다른 키로 들어왔을 수 있으니 모든 값 스캔
        for (Object v : row.values()) {
            String s = normalizeGeometryToString(v);
            if (s != null) return s;
        }

        return null;
    }

    private String normalizeGeometryToString(Object v) {
        if (v == null) return null;
        if (v instanceof String s) {
            s = s.trim();
            if (s.isEmpty() || "null".equalsIgnoreCase(s)) return null;
            // 문자열인데 geometry처럼 보이면 채택
            // (간단 검증: "type" 포함 + coordinates|geometries 포함)
            String lower = s.toLowerCase(Locale.ROOT);
            if (lower.contains("\"type\"") && (lower.contains("\"coordinates\"") || lower.contains("\"geometries\""))) {
                return s;
            }
            // 이미 올바른 GeoJSON 문자열이라면 위 조건으로 충분, 아니면 패스
            return null;
        } else if (v instanceof Map<?,?> m) {
            // Map 형태면 GeoJSON 구조인지 확인
            Object type = m.get("type");
            if (type != null) {
                // coordinates / geometries 중 하나라도 있으면 GeoJSON으로 간주
                if (m.containsKey("coordinates") || m.containsKey("geometries")) {
                    String json = toJsonString(m);
                    if (json != null && !json.isBlank() && !"null".equalsIgnoreCase(json)) return json;
                }
            }
        }
        // 기타 POJO → JSON 시도
        String json = toJsonString(v);
        if (json != null) {
            String lower = json.toLowerCase(Locale.ROOT);
            if (lower.contains("\"type\"") && (lower.contains("\"coordinates\"") || lower.contains("\"geometries\""))) {
                return json;
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    private Map<String, Object> extractPropertiesMap(Map<String, Object> row) {
        String[] keys = { "properties", "props", "attributes", "attrs", "fields" };
        for (String k : keys) {
            Object v = row.get(k);
            if (v instanceof Map<?,?> m) {
                // 키를 String으로 강제 캐스팅
                Map<String, Object> out = new LinkedHashMap<>();
                for (Map.Entry<?, ?> e : m.entrySet()) {
                    Object kk = e.getKey();
                    if (kk instanceof String) out.put((String) kk, e.getValue());
                }
                return out;
            }
        }
        return null;
    }


    // ──────────────────────────────────────────────────────────────────────

    private static String toJson(ObjectMapper om, Object val) {
        if (val == null) return null;
        if (val instanceof String) return (String) val;
        if (val instanceof JsonNode) return ((JsonNode) val).toString();
        try {
            return om.writeValueAsString(val);
        } catch (JsonProcessingException e) {
            throw new IllegalArgumentException("JSON 직렬화 실패: " + val, e);
        }
    }

    // ──────────────────────────────────────────────────────────────────────
// 테이블/컬럼명 sanitize (허용: 영문/숫자/언더스코어만, 소문자 통일)
// ──────────────────────────────────────────────────────────────────────
    private static String sanitizeTableName(String raw) {
        if (raw == null) return null;
        // 따옴표, 공백, 점 등 제거하고 소문자/언더스코어만 허용
        String s = raw.trim().replace("\"", "").replace("`", "");
        s = s.replace('.', '_').replaceAll("[^a-zA-Z0-9_]", "_");
        return s.toLowerCase();
    }
    private static String sanitizeColumnName(String raw) {
        if (raw == null) return null;
        String s = raw.trim().replace("\"", "").replace("`", "");
        s = s.replace('.', '_').replaceAll("[^a-zA-Z0-9_]", "_");
        return s;
    }

    // ──────────────────────────────────────────────────────────────────────
// DB 컬럼 메타 (최소 필드만)
// ──────────────────────────────────────────────────────────────────────
    private static final class ColumnMeta {
        final String name;       // DB 실제 컬럼명(원본 케이스)
        final String dataType;   // information_schema.data_type
        final boolean nullable;  // true/false
        final boolean primary;   // PK 여부

        ColumnMeta(String name, String dataType, boolean nullable, boolean primary) {
            this.name = name;
            this.dataType = dataType;
            this.nullable = nullable;
            this.primary = primary;
        }
    }



    /**
     * 단일 GeoJSON root(Map)에서 테이블 힌트(name)를 추출.
     * 1) root.name
     * 2) features[0].properties.name
     * 순으로 시도하고, 없으면 null 반환.
     */
    private String extractTableNameFromRoot(Map<String, Object> root) {
        if (root == null) return null;

        Object name = root.get("name");
        if (name instanceof String s && !s.isBlank()) {
            return s.trim();
        }

        Object featuresObj = root.get("features");
        if (featuresObj instanceof List<?> features && !features.isEmpty()) {
            Object f0 = features.get(0);
            if (f0 instanceof Map<?, ?> feat) {
                Object props = ((Map<?, ?>) feat).get("properties");
                if (props instanceof Map<?, ?> p) {
                    Object n2 = ((Map<?, ?>) p).get("name");
                    if (n2 instanceof String s2 && !s2.isBlank()) {
                        return s2.trim();
                    }
                }
            }
        }
        return null;
    }



    /* ================== 아래는 이 메소드 안에서만 쓰는 작은 유틸 ================== */

    /** geojsonList에서 root.name 또는 properties.name 중 첫 유효값 반환 */
    private String extractFirstNonBlankNameFromGeojsonList(List<Map<String, Object>> geojsonList) {
        for (Map<String, Object> root : geojsonList) {
            if (root == null) continue;

            // 1) root.name
            Object name = root.get("name");
            if (name instanceof String s && !s.isBlank()) return s.trim();

            // 2) features[0].properties.name (있으면)
            Object featuresObj = root.get("features");
            if (featuresObj instanceof List<?> features && !features.isEmpty()) {
                Object f0 = features.get(0);
                if (f0 instanceof Map<?,?> feat) {
                    Object props = feat.get("properties");
                    if (props instanceof Map<?,?> p) {
                        Object n2 = p.get("name");
                        if (n2 instanceof String s2 && !s2.isBlank()) return s2.trim();
                    }
                }
            }
        }
        return null;
    }




    protected String resolveGeometryColumn(String schema, String table) {
        try {
            if (g2fLayerMapper == null) return null;
            java.lang.reflect.Method m = g2fLayerMapper.getClass().getMethod("selectGeometryColumn", String.class, String.class);
            Object ret = m.invoke(g2fLayerMapper, schema, table);
            return ret != null ? String.valueOf(ret) : null;
        } catch (Exception ignore) {
            return null;
        }
    }

    protected int resolveGeometrySrid(String schema, String table, String geomCol) {
        try {
            if (g2fLayerMapper == null) return 0;
            java.lang.reflect.Method m = g2fLayerMapper.getClass().getMethod("selectGeometrySrid", String.class, String.class, String.class);
            Object ret = m.invoke(g2fLayerMapper, schema, table, geomCol);
            return ret instanceof Number ? ((Number) ret).intValue() : 0;
        } catch (Exception ignore) {
            return 0;
        }
    }


    /* 문자열 처리 유틸 (아래 둘 같이 붙여 넣기) */
    private static String str(Object o) {
        return str(o, "");
    }
    private static String str(Object o, String def) {
        if (o == null) return def;
        String s = String.valueOf(o).trim();
        return s.isEmpty() ? def : s;
    }


    // === [ADD-ONLY] FeatureCollection[] → List<Map> 평탄화 ===
    @SuppressWarnings("unchecked")
    private List<Map<String, Object>> flattenPreviewGeojson(List<Map<String, Object>> geojsonList) {
        List<Map<String, Object>> rows = new ArrayList<>();
        for (Map<String, Object> fc : geojsonList) {
            if (fc == null) continue;
            Object type = fc.get("type");
            if (type == null || !"FeatureCollection".equalsIgnoreCase(String.valueOf(type))) continue;

            Object featsObj = fc.get("features");
            if (!(featsObj instanceof List<?> feats)) continue;

            for (Object fo : feats) {
                if (!(fo instanceof Map<?,?> f)) continue;

                Map<String, Object> row = new HashMap<>();
                // properties
                Object propsObj = f.get("properties");
                if (propsObj instanceof Map<?,?> p) {
                    Map<String,Object> props = new HashMap<>();
                    p.forEach((k,v)-> props.put(String.valueOf(k), v));
                    row.put("properties", props);
                } else {
                    row.put("properties", Collections.emptyMap());
                }
                // geometry (원문 JSON 문자열 형태로 넘김)
                Object geomObj = f.get("geometry");
                if (geomObj != null) {
                    row.put("geometryGeoJson", toJsonString(geomObj));
                } else {
                    row.put("geometryGeoJson", null);
                }
                rows.add(row);
            }
        }
        return rows;
    }

    // ──────────────────────────────────────────────────────────────────────
// mapper에 넘길 columns 파라미터 형태로 변환
//  - name: DB 컬럼명(원본 케이스 유지)
//  - isPk: 기본키 여부
//  - jdbcType: 대략적 매핑 (필요시 XML에서 사용)
// ──────────────────────────────────────────────────────────────────────
    private List<Map<String, Object>> toColumnParam(List<ColumnMeta> metas) {
        List<Map<String, Object>> cols = new ArrayList<>();
        for (ColumnMeta m : metas) {
            Map<String, Object> col = new HashMap<>();
            col.put("name", m.name);
            col.put("isPk", m.primary);
            // 간단 매핑 (필요시 보강)
            String jdbcType;
            String t = m.dataType == null ? "" : m.dataType.toLowerCase(Locale.ROOT);
            if (t.contains("int")) jdbcType = "INTEGER";
            else if (t.contains("double") || t.contains("float") || t.contains("real")) jdbcType = "DOUBLE";
            else if (t.contains("numeric") || t.contains("decimal")) jdbcType = "NUMERIC";
            else if (t.contains("date") || t.contains("time")) jdbcType = "TIMESTAMP";
            else jdbcType = "VARCHAR";
            col.put("jdbcType", jdbcType);
            cols.add(col);
        }
        return cols;
    }

    // ──────────────────────────────────────────────────────────────────────
// geometry 노드를 ST_GeomFromGeoJSON(?)에 넣을 문자열로
// ──────────────────────────────────────────────────────────────────────
    private String toGeometryString(Object geomNode, ObjectMapper om) {
        if (geomNode == null) return null;
        if (geomNode instanceof String s) {
            String trimmed = s.trim();
            return (trimmed.isEmpty() || "null".equalsIgnoreCase(trimmed)) ? null : trimmed;
        }
        try {
            return om.writeValueAsString(geomNode);
        } catch (Exception e) {
            return null;
        }
    }

    // ──────────────────────────────────────────────────────────────────────
// featuresParam 생성: 각 row별로 (geometryString, srid, properties Map)
//  - columns 순서는 XML에서 foreach로 보장된다고 가정
// ──────────────────────────────────────────────────────────────────────
    private List<Map<String, Object>> buildFeaturesParam(
            List<Map<String, Object>> rows,
            List<ColumnMeta> dbColumns,
            int srid,
            ObjectMapper om
    ) {
        // 컬럼명(소문자) -> 원본 케이스 매핑
        Map<String, String> originalCaseByLower = new HashMap<>();
        for (ColumnMeta m : dbColumns) {
            originalCaseByLower.put(m.name.toLowerCase(Locale.ROOT), m.name);
        }

        List<Map<String, Object>> features = new ArrayList<>();
        for (Map<String, Object> row : rows) {
            if (row == null) continue;

            Object geomNode =
                    row.get("geometry") != null ? row.get("geometry")
                            : row.get("geom") != null ? row.get("geom")
                            : row.get("GEOMETRY"); // 들어오는 키 케이스 가변 대응

            String geometryString = toGeometryString(geomNode, om);
            if (geometryString == null) continue; // geometry 필수

            // properties만 추림 (DB에 존재하는 컬럼만)
            Map<String, Object> props = new HashMap<>();
            Object propsObj = row.get("properties");
            if (propsObj instanceof Map<?, ?> pm) {
                for (ColumnMeta m : dbColumns) {
                    Object v = pm.get(m.name);
                    if (v == null) {
                        // 입력이 전부 소문자/대문자일 수 있어 보정
                        v = pm.get(m.name.toLowerCase(Locale.ROOT));
                        if (v == null) v = pm.get(m.name.toUpperCase(Locale.ROOT));
                    }
                    if (v != null) props.put(m.name, v);
                }
            } else {
                // properties가 없고 row 평면에 섞여있는 경우 대응
                for (ColumnMeta m : dbColumns) {
                    Object v = row.get(m.name);
                    if (v == null) v = row.get(m.name.toLowerCase(Locale.ROOT));
                    if (v == null) v = row.get(m.name.toUpperCase(Locale.ROOT));
                    if (v != null) props.put(m.name, v);
                }
            }

            Map<String, Object> one = new HashMap<>();
            one.put("geometryString", geometryString);
            one.put("srid", srid);
            one.put("properties", props);
            features.add(one);
        }
        return features;
    }



    /** MyBatis에 statement 존재 여부 */
    private boolean hasStatement(String statementId) {
        try {
            Configuration cfg = sqlSessionFactory.getConfiguration();
            cfg.getMappedStatement(statementId);
            return true;
        } catch (Exception ignore) {
            return false;
        }
    }

    /** 매퍼에 정의돼 있으면 히스토리 insert, 없으면 조용히 스킵 */
    private void tryInsertHistory(String action, String schemaName, String tableName, int rowCount) {
        Map<String, Object> hist = new HashMap<>();
        hist.put("schemaName", schemaName);
        hist.put("tableName", tableName);
        hist.put("actionType", action);   // INSERT / UPDATE / DELETE
        hist.put("rowCount", rowCount);

        String ns = "incheon.cmm.g2f.layer.mapper.G2FLayerMapper";

        // 1) action 에 따라 1순위 statement 결정
        String primaryId = null;
        if ("INSERT".equalsIgnoreCase(action) || "I".equalsIgnoreCase(action)) {
            primaryId = ns + ".batchInsertHistoryAdded";
        } else if ("UPDATE".equalsIgnoreCase(action) || "U".equalsIgnoreCase(action)) {
            primaryId = ns + ".batchInsertHistoryModified";
        } else if ("DELETE".equalsIgnoreCase(action) || "D".equalsIgnoreCase(action)) {
            primaryId = ns + ".batchInsertHistoryDeleted";
        }

        // 2) 1순위 statement가 있으면 그걸 먼저 시도
        if (primaryId != null && hasStatement(primaryId)) {
            try {
                sqlSessionTemplate.insert(primaryId, hist);
            } catch (Exception ignore) {
                log.debug(
                        "히스토리 INSERT 실패 (무시됨) statementId={}, hist={}",
                        primaryId, hist, ignore
                );
            }
            return;
        }

        // 3) 혹시 모를 호환용 fallback (기존 프로젝트용)
        String[] fallbackCandidates = {
                ns + ".batchInsertHistoryModified",
                ns + ".batchInsertHistoryDeleted",
                ns + ".batchInsertHistoryAdded"
        };

        for (String id : fallbackCandidates) {
            if (hasStatement(id)) {
                try {
                    sqlSessionTemplate.insert(id, hist);
                } catch (Exception e) {
                    log.debug(
                            "히스토리 INSERT 실패 (fallback) statementId={}, hist={}",
                            id, hist, e
                    );
                }
                return;
            }
        }
        // 여전히 아무 것도 없으면 조용히 패스
    }



    // 객체 → JSON 문자열 (null 안전)
    private String toJsonString(Object obj) {
        try {
            if (obj == null) return null;
            if (obj instanceof String s) return s;
            com.fasterxml.jackson.databind.ObjectMapper om = new com.fasterxml.jackson.databind.ObjectMapper();
            return om.writeValueAsString(obj);
        } catch (Exception e) {
            return null;
        }
    }



    private String getExt(String name) {
        int i = name.lastIndexOf('.');
        return (i > -1) ? name.substring(i+1) : "";
    }

    private SyncMode toMode(String actionType) {
        if (actionType == null) return SyncMode.UPSERT;
        return switch (actionType.trim()) {
            case "신규" -> SyncMode.INSERT_ONLY;
            case "수정" -> SyncMode.UPDATE_ONLY;
            case "삭제" -> SyncMode.DELETE_ONLY;
            default -> SyncMode.UPSERT;
        };
    }

    // ===== [CHANGED] 더 이상 사용하지 않음 (호출 대비 no-op)
    private void createFolder() { /* no-op */ }

    private String makeSafeFileName(String name) {
        if (name == null) return "unnamed";
        String only = Paths.get(name).getFileName().toString();
        return only.replaceAll("[\\/:*?\"<>|]", "_");
    }
    private String addUuidSuffix(String fileName) {
        int dot = fileName.lastIndexOf('.');
        String base = (dot > 0) ? fileName.substring(0, dot) : fileName;
        String ext  = (dot > 0) ? fileName.substring(dot) : "";
        String uid  = UUID.randomUUID().toString().replace("-", "").substring(0,8);
        return base + "__" + uid + ext;
    }

    // === [ADD] 테이블명 추출 & 그룹핑 헬퍼 ===============================

    /** row 맵에서 테이블명 키를 안전하게 추출 (소문자 정규화) */
    private static String extractTableName(Map<String, Object> row) {
        if (row == null) return null;

        Object t = null;
        // 프로젝트별로 쓰던 키들 대응 (있는 것 우선 사용)
        if (row.containsKey("tableName")) t = row.get("tableName");
        else if (row.containsKey("TABLE_NAME")) t = row.get("TABLE_NAME");
        else if (row.containsKey("table")) t = row.get("table");
        else if (row.containsKey("_tableName")) t = row.get("_tableName");

        if (t == null) return null;
        String name = String.valueOf(t).trim();
        if (name.isEmpty()) return null;
        return name.toLowerCase(); // 실제 DB가 소문자 테이블이면 소문자로 정규화
    }

    /** 기존 params(List<Map>)를 테이블명별로 쪼갠다 */
    private static Map<String, List<Map<String, Object>>> splitByTable(List<Map<String, Object>> params) {
        Map<String, List<Map<String, Object>>> grouped = new java.util.LinkedHashMap<>();
        if (params == null) return grouped;

        for (Map<String, Object> row : params) {
            String table = extractTableName(row);
            if (table == null) {
                // 테이블명이 없으면 스킵(혹은 공통 로그)
                continue;
            }
            grouped.computeIfAbsent(table, k -> new java.util.ArrayList<>()).add(row);
        }
        return grouped;
    }
    // =====================================================================


    // ===== [ADD] 경로/검증 유틸 =====

    /** {base}/YYYY/MM 디렉토리 생성 후 Path 반환 */
    private Path ensureYearMonthDir() {
        LocalDateTime now = LocalDateTime.now();
        String yyyy = now.format(DateTimeFormatter.ofPattern("yyyy"));
        String mm   = now.format(DateTimeFormatter.ofPattern("MM"));

        Path base = Paths.get(normalizeDir(shapefileBasePath));
        Path ym   = base.resolve(yyyy).resolve(mm);
        ym.toFile().mkdirs();
        return ym;
    }

    private static String normalizeDir(String dir) {
        if (dir == null || dir.isBlank()) return "";
        return dir.trim();
    }

    private boolean isAllowedExtension(String filename) {
        String ext = getExt(filename).toLowerCase(Locale.ROOT);
        if (ext.isEmpty()) return false;
        if (allowedExtensionsProp == null || allowedExtensionsProp.isBlank()) return "zip".equals(ext);
        for (String one : allowedExtensionsProp.split(",")) {
            if (ext.equals(one.trim().toLowerCase(Locale.ROOT))) return true;
        }
        return false;
    }

    /** 요청 포맷: shp_tmp{yyyyMMddHHmmss}.zip */
    private String buildTargetZipName() {
        String ts = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
        return "shp_tmp" + ts + ".zip";
    }
}