package incheon.cmm.g2f.layer.service.impl;

import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.List;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.springframework.dao.DataAccessException;
import org.springframework.transaction.annotation.Transactional;

import incheon.cmm.g2f.layer.domain.LayerType;
import incheon.cmm.g2f.layer.service.G2FLayerService;
import incheon.cmm.g2f.layer.vo.*;
import incheon.cmm.g2f.util.CoordinateUtils;
import org.geotools.api.referencing.FactoryException;
import org.geotools.api.referencing.crs.CoordinateReferenceSystem;
import org.geotools.api.referencing.crs.ProjectedCRS;
import org.geotools.api.referencing.operation.TransformException;
import org.geotools.geometry.jts.ReferencedEnvelope;
import org.geotools.map.MapContent;
import org.geotools.ows.ServiceException;
import org.geotools.ows.wmts.WebMapTileServer;
import org.geotools.ows.wmts.map.WMTSMapLayer;
import org.geotools.ows.wmts.model.WMTSLayer;
import org.geotools.referencing.CRS;
import org.geotools.renderer.lite.StreamingRenderer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;

import lombok.RequiredArgsConstructor;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import incheon.cmm.g2f.layer.mapper.G2FLayerMapper;

import javax.imageio.ImageIO;

/**
 * G2F 공통 레이어 서비스 구현체
 */
@Service
@RequiredArgsConstructor
public class G2FLayerServiceImpl implements G2FLayerService {

    private static final Logger logger = LoggerFactory.getLogger(G2FLayerServiceImpl.class);

    private static final int BATCH_SIZE = 1000; // 청크 배치 크기

    private final G2FLayerMapper mapper;
    private final ObjectMapper objectMapper;

    final int ABS_MAX = 4000;
    final int DEFAULT_MAX = 1920;

    @Value("${gis.server.url}")
    private String baseUrl;

    @Value("${gis.server.prefix}")
    private String prefix;

    @Override
    @Transactional
    public LayerEditResultVO editLayer(LayerEditRequestDTO request, String currentUserId) {
        logger.info("레이어 편집 시작 - layerId: {}, userId: {}", request.getLayerId(), currentUserId);

        // 레이어 메타데이터 조회 및 검증
        Map<String, Object> layerMeta = null;
        if(request.getLayerType().equals(LayerType.TASK)){
            layerMeta = mapper.selectTaskLayerMetadata(request.getLayerId());
        } else if (request.getLayerType().equals(LayerType.USER)) {
            layerMeta = mapper.selectUserLayerMetadata(request.getLayerId());
        }
        if (layerMeta == null) {
            throw new IllegalArgumentException("레이어를 찾을 수 없습니다: " + request.getLayerId());
        }

        // 권한 검증 - 레이어 소유자만 편집 가능 -> 백단 검사 제거
//        String ownerId = (String) layerMeta.get("user_id");
//        if (!currentUserId.equals(ownerId)) {
//            throw new SecurityException("레이어 편집 권한이 없습니다. 소유자: " + ownerId + ", 요청자: " + currentUserId);
//        }

        String lyrPhysNm = (String) layerMeta.get("lyr_phys_nm");
        if (lyrPhysNm == null || !lyrPhysNm.contains(".")) {
            throw new IllegalArgumentException("레이어 물리 명은 '스키마.테이블' 형식이어야 합니다: " + lyrPhysNm);
        }
        String[] lyrPhysNmSplit = lyrPhysNm.split("\\.");
        String schemaName = lyrPhysNmSplit[0];
        String tableName = lyrPhysNmSplit[1];
        Integer srid = (Integer) layerMeta.get("cntm");

        logger.debug("대상 테이블: {}.{}, 좌표계: {}", schemaName, tableName, srid);

        LayerEditResultVO result = new LayerEditResultVO();
        result.setLayerId(request.getLayerId());

        // 1. Added 피처 처리 (청크 배치)
        List<FeatureVO> addedFeatures = request.getChanges().getAdded();
        if (addedFeatures != null && !addedFeatures.isEmpty()) {
            int addedCount = processAddedFeatures(addedFeatures, schemaName, tableName, srid);
            result.addAddedCount(addedCount);
            logger.info("Added 피처 처리 완료: {} 개", addedCount);
        }

        // 2. Modified 피처 처리 (청크 배치)
        List<FeatureVO> modifiedFeatures = request.getChanges().getModified();
        if (modifiedFeatures != null && !modifiedFeatures.isEmpty()) {
            int modifiedCount = processModifiedFeatures(modifiedFeatures, schemaName, tableName, srid);
            result.addModifiedCount(modifiedCount);
            logger.info("Modified 피처 처리 완료: {} 개", modifiedCount);
        }

        // 3. Deleted 피처 처리 (청크 배치)
        List<Integer> deletedIds = request.getChanges().getDeleted();
        if (deletedIds != null && !deletedIds.isEmpty()) {
            int deletedCount = processDeletedFeatures(deletedIds, schemaName, tableName);
            result.addDeletedCount(deletedCount);
            logger.info("Deleted 피처 처리 완료: {} 개", deletedCount);
        }

        logger.info("레이어 편집 완료 - Added: {}, Modified: {}, Deleted: {}",
                   result.getProcessed().getAdded(),
                   result.getProcessed().getModified(),
                   result.getProcessed().getDeleted());

        return result;
    }

    /**
     * Added 피처 청크 배치 처리
     */
    @Transactional
    public int processAddedFeatures(List<FeatureVO> features, String schemaName, String tableName, Integer srid) {
        int totalProcessed = 0;

        // 테이블 메타데이터 미리 조회
        List<Map<String, Object>> tableColumns = mapper.selectTableColumns(schemaName, tableName);

        for (int i = 0; i < features.size(); i += BATCH_SIZE) {
            int endIndex = Math.min(i + BATCH_SIZE, features.size());
            List<FeatureVO> chunk = features.subList(i, endIndex);

            // FeatureVO -> Map 변환
            List<Map<String, Object>> testFeatures = convertToTestFeatures(chunk);

            Map<String, Object> params = new HashMap<>();
            params.put("schemaName", schemaName);
            params.put("tableName", tableName);
            params.put("features", testFeatures);
            params.put("srid", srid);
            params.put("columns", tableColumns); // 메타데이터 추가

            // INSERT 수행 및 RETURNING *으로 저장된 모든 컬럼 정보 반환
            List<Map<String, Object>> insertedRows = mapper.batchInsertLayerFeatures(params);
            int insertedCount = insertedRows != null ? insertedRows.size() : 0;
            totalProcessed += insertedCount;
            
            tableColumns.add(Map.of(
        	    "column_name", "gid",
        	    "data_type", "bigint",
        	    "is_nullable", "NO"
        	));
            params.put("columns", tableColumns); // 메타데이터 추가
            
            // RETURNING * 결과를 이용하여 features 정보 업데이트
            if (insertedRows != null && !insertedRows.isEmpty()) {
                for (int rowIdx = 0; rowIdx < insertedRows.size() && rowIdx < testFeatures.size(); rowIdx++) {
                    Map<String, Object> insertedRow = insertedRows.get(rowIdx);
                    Map<String, Object> feature = testFeatures.get(rowIdx);
                    @SuppressWarnings("unchecked")
                    Map<String, Object> properties = (Map<String, Object>) feature.get("properties");

                    // 저장된 모든 컬럼 정보를 properties에 매핑 (geom 제외)
                    for (Map.Entry<String, Object> entry : insertedRow.entrySet()) {
                        String columnName = entry.getKey();
                        Object value = entry.getValue();
                        // geom은 binary 형태이므로 제외 (geometryString으로 이미 있음)
                        if (!"geom".equals(columnName) && value != null) {
                            properties.put(columnName, value);
                        }
                    }
                }
            }

            // 이력 처리 - 업데이트된 features 정보로 이력 저장
            insertHistoryAdded(schemaName, tableName, params);

            logger.debug("Added 청크 처리: {}/{} 완료", endIndex, features.size());
        }

        return totalProcessed;
    }

    /**
     * Modified 피처 청크 배치 처리
     */
    @Transactional
    public int processModifiedFeatures(List<FeatureVO> features, String schemaName, String tableName, Integer srid) {
        int totalProcessed = 0;

        // 테이블 메타데이터 미리 조회
        List<Map<String, Object>> tableColumns = mapper.selectTableColumns(schemaName, tableName);

        for (int i = 0; i < features.size(); i += BATCH_SIZE) {
            int endIndex = Math.min(i + BATCH_SIZE, features.size());
            List<FeatureVO> chunk = features.subList(i, endIndex);

            // FeatureVO -> Map 변환
            List<Map<String, Object>> testFeatures = convertToTestUpdateFeatures(chunk);

            Map<String, Object> params = new HashMap<>();
            params.put("schemaName", schemaName);
            params.put("tableName", tableName);
            params.put("features", testFeatures);
            params.put("srid", srid);
            params.put("columns", tableColumns); // 메타데이터 추가

            int updatedCount = mapper.batchUpdateLayerFeatures(params);
            totalProcessed += updatedCount;

            // 이력 처리
            insertHistoryModified(schemaName, tableName, params);

            logger.debug("Modified 청크 처리: {}/{} 완료", endIndex, features.size());
        }

        return totalProcessed;
    }

    /**
     * Deleted 피처 청크 배치 처리
     */
    @Transactional
    public int processDeletedFeatures(List<Integer> gids, String schemaName, String tableName) {
        int totalProcessed = 0;

        for (int i = 0; i < gids.size(); i += BATCH_SIZE) {
            int endIndex = Math.min(i + BATCH_SIZE, gids.size());
            List<Integer> chunk = gids.subList(i, endIndex);

            Map<String, Object> params = new HashMap<>();
            params.put("schemaName", schemaName);
            params.put("tableName", tableName);
            params.put("gids", chunk);

            int deletedCount = mapper.deleteLayerFeatures(params);
            totalProcessed += deletedCount;

            // 이력 처리
            insertHistoryDeleted(schemaName, tableName, params);

            logger.debug("Deleted 청크 처리: {}/{} 완료", endIndex, gids.size());
        }

        return totalProcessed;
    }

    /**
     * FeatureVO -> Map 변환 (Added용)
     */
    private List<Map<String, Object>> convertToTestFeatures(List<FeatureVO> features) {
        List<Map<String, Object>> testFeatures = new ArrayList<>();

        for (FeatureVO feature : features) {
            Map<String, Object> testFeature = new HashMap<>();

            // geometry -> geometryString 변환
            try {
                String geometryString = objectMapper.writeValueAsString(feature.getGeometry());
                testFeature.put("geometryString", geometryString);
            } catch (JsonProcessingException e) {
                throw new RuntimeException("지오메트리 변환 실패", e);
            }

            // properties 설정
            testFeature.put("properties", feature.getProperties());

            testFeatures.add(testFeature);
        }

        return testFeatures;
    }

    /**
     * FeatureVO -> Map 변환 (Modified용)
     */
    private List<Map<String, Object>> convertToTestUpdateFeatures(List<FeatureVO> features) {
        List<Map<String, Object>> testFeatures = new ArrayList<>();

        for (FeatureVO feature : features) {
            Map<String, Object> testFeature = new HashMap<>();

            // gid 설정 (Modified에서는 필수)
            testFeature.put("gid", feature.getId());

            // geometry 결정 (modified.geometry 우선, 없으면 기본 geometry)
            Object geometry = null;
            Map<String, Object> properties = null;

            if (feature.getModified() != null) {
                geometry = feature.getModified().getGeometry();
                properties = feature.getModified().getProperties();
            }

            // modified 정보가 없으면 기본값 사용
            if (geometry == null) {
                geometry = feature.getGeometry();
            }
            if (properties == null) {
                properties = feature.getProperties();
            }

            // geometry -> geometryString 변환
            try {
                String geometryString = objectMapper.writeValueAsString(geometry);
                testFeature.put("geometryString", geometryString);
            } catch (JsonProcessingException e) {
                throw new RuntimeException("지오메트리 변환 실패", e);
            }

            // properties 설정
            testFeature.put("properties", properties);

            testFeatures.add(testFeature);
        }

        return testFeatures;
    }

    @Override
    public List<TaskLayerVO> selectTaskLayerList(TaskLayerSearchRequestDTO searchVO, Set<String> removedGroupCd) {
        if(removedGroupCd.isEmpty()){
            return mapper.selectTaskLayerList(searchVO);
        } else {
            return mapper.selectTaskLayerListNotInGroupCd(searchVO, removedGroupCd);
        }

    }

    @Override
    public long selectTaskLayerListTotCnt(TaskLayerSearchRequestDTO searchVO, Set<String> removedGroupCd) {
        if(removedGroupCd.isEmpty()){
            return mapper.selectTaskLayerListTotCnt(searchVO);
        } else {
            return mapper.selectTaskLayerListNotInGroupCdTotCnt(searchVO, removedGroupCd);
        }
    }

    @Override
    public TaskLayerVO selectTaskLayerById(int taskLyrId) {
        return mapper.selectTaskLayerById(taskLyrId);
    }

    @Override
    public List<FlightPhotoLyrVO> selectFlightPhotoLayerList(FlightPhotoLayerSearchRequestDTO searchVO, Boolean hasPermission) {
        return mapper.selectFlightPhotoLayerList(searchVO, hasPermission);
    }

    @Override
    public long selectFlightPhotoLayerListTotCnt(FlightPhotoLayerSearchRequestDTO searchVO, Boolean hasPermission) {
        return mapper.selectFlightPhotoLayerListTotCnt(searchVO, hasPermission);
    }

    @Override
    public FlightPhotoLyrVO selectFlightPhotoLayerById(int flightLyrId) {
        return mapper.selectFlightPhotoLayerById(flightLyrId);
    }

    private List<FlightPhotoLyrVO> selectFlightPhotoLayerInIds(Set<Integer> flightLyrIds) {
        return mapper.selectFlightPhotoLayerInIds(flightLyrIds);
    }

    @Override
    public void validateFlightPhotoLayer(FlightPhotoLayerDownloadRequestDTO request) {
        double bboxW = request.getMaxx() - request.getMinx();
        double bboxH = request.getMaxy() - request.getMiny();

        if (bboxW <= 0.0 || bboxH <= 0.0) {
            throw new IllegalArgumentException("잘못된 범위: min/max 값이 유효하지 않습니다.");
        }

        double ratio = bboxW / bboxH;
        int width;
        int height;
        if (request.getWidth() == null && request.getHeight() == null) {
            if (ratio >= 1) {
                width = DEFAULT_MAX;
                height = (int) Math.round(DEFAULT_MAX / ratio);
            } else {
                height = DEFAULT_MAX;
                width = (int) Math.round(DEFAULT_MAX * ratio);
            }
        } else if (request.getWidth() != null && request.getHeight() == null) {
            width = request.getWidth();
            height = (int) Math.round(request.getWidth() / ratio);
        } else if (request.getWidth() == null && request.getHeight() != null) {
            height = request.getHeight();
            width = (int) Math.round(request.getHeight() * ratio);
        } else {
            width = request.getWidth();
            height = request.getHeight();
        }
        if (width < 256 || height < 256) {
            throw new IllegalArgumentException("width/height 값이 유효하지 않습니다.");
        }
        if (width > ABS_MAX || height > ABS_MAX) {
            throw new IllegalArgumentException("width/height 값은 4000을 넘을 수 없습니다..");
        }

        List<FlightPhotoLyrVO> flightPhotoLyrList = selectFlightPhotoLayerInIds(request.getLayerIds());
        Set<Integer> foundIds = flightPhotoLyrList.stream()
                .map(FlightPhotoLyrVO::getFlightPhotoLyrId)
                .collect(Collectors.toSet());
        for (Integer id : request.getLayerIds()) {
            if (!foundIds.contains(id)) {
                throw new IllegalArgumentException("존재하지 않는 항공 사진 레이어 ID: " + id);
            }
        }

        CoordinateReferenceSystem crs;
        try {
            crs = CRS.decode("urn:ogc:def:crs:EPSG::" + request.getSrid());
            if (crs instanceof ProjectedCRS) {
                crs = CoordinateUtils.createChangedTowgs84CRS((ProjectedCRS) crs);
            }
        } catch (FactoryException e) {
            throw new IllegalArgumentException("유효하지 않은 좌표계입니다: " + request.getSrid());
        }
    }

    @Override
    public WebMapTileServer createWebMapTileServer() throws ServiceException, IOException {
        URL capabilities = new URL(baseUrl + "/map/wmts?service=WMTS&request=GetCapabilities&version=1.0.0");
        WebMapTileServer wmts = new WebMapTileServer(capabilities);
        return wmts;
    }

    public void writeFlightPhotoLayerZip(OutputStream out, FlightPhotoLayerDownloadRequestDTO request, Boolean hasPermission, WebMapTileServer wmts)
            throws IOException {

        double bboxW = request.getMaxx() - request.getMinx();
        double bboxH = request.getMaxy() - request.getMiny();

        double ratio = bboxW / bboxH;
        int width;
        int height;
        if (request.getWidth() == null && request.getHeight() == null) {
            if (ratio >= 1) {
                width = DEFAULT_MAX;
                height = (int) Math.round(DEFAULT_MAX / ratio);
            } else {
                height = DEFAULT_MAX;
                width = (int) Math.round(DEFAULT_MAX * ratio);
            }
        } else if (request.getWidth() != null && request.getHeight() == null) {
            width = request.getWidth();
            height = (int) Math.round(request.getWidth() / ratio);
        } else if (request.getWidth() == null && request.getHeight() != null) {
            height = request.getHeight();
            width = (int) Math.round(request.getHeight() * ratio);
        } else {
            width = request.getWidth();
            height = request.getHeight();
        }

        List<FlightPhotoLyrVO> flightPhotoLyrList = selectFlightPhotoLayerInIds(request.getLayerIds());
        Set<Integer> foundIds = flightPhotoLyrList.stream()
                .map(FlightPhotoLyrVO::getFlightPhotoLyrId)
                .collect(Collectors.toSet());
        for (Integer id : request.getLayerIds()) {
            if (!foundIds.contains(id)) {
                throw new IllegalArgumentException("존재하지 않는 항공 사진 레이어 ID: " + id);
            }
        }

        CoordinateReferenceSystem crs;
        try {
            crs = CRS.decode("urn:ogc:def:crs:EPSG::" + request.getSrid());
            if (crs instanceof ProjectedCRS) {
                crs = CoordinateUtils.createChangedTowgs84CRS((ProjectedCRS) crs);
            }
        } catch (FactoryException e) {
            throw new IllegalArgumentException("유효하지 않은 좌표계입니다: " + request.getSrid());
        }

        Rectangle paintArea = new Rectangle(0, 0, width, height);

        ZipOutputStream zipOut = new ZipOutputStream(out);
        try {
            ReferencedEnvelope requestedEnvelope;
            if (CRS.getAxisOrder(crs).equals(CRS.AxisOrder.NORTH_EAST)) {
                requestedEnvelope = new ReferencedEnvelope(request.getMiny(), request.getMaxy(), request.getMinx(), request.getMaxx(), crs);
            } else {
                requestedEnvelope = new ReferencedEnvelope(request.getMinx(), request.getMaxx(), request.getMiny(), request.getMaxy(), crs);
            }
            for (FlightPhotoLyrVO layer : flightPhotoLyrList) {

                if (!hasPermission && !layer.getOtsdRlsEn()) {
                    String raw = layer.getFlightPhotoLyrNm() == null ? "layer" : layer.getFlightPhotoLyrNm();
                    String safe = raw.replaceAll("[\\\\/:*?\"<>|\\s]+", "_");
                    String entryName = String.format("%s.txt", safe);
                    zipOut.putNextEntry(new ZipEntry(entryName));
                    try {
                        String msg = "권한이 없어 이 항공사진을 열람할 수 없습니다.";
                        zipOut.write(msg.getBytes(StandardCharsets.UTF_8));
                    } finally {
                        zipOut.closeEntry();
                    }
                    zipOut.flush();
                    continue;
                }

                BufferedImage image = null;
                MapContent mapContent = null;
                Graphics2D g = null;

                ReferencedEnvelope layerEnvelope;
                try {
                    CoordinateReferenceSystem layerCRS = CRS.decode("urn:ogc:def:crs:EPSG::" + layer.getCntm(), true);
                    layerEnvelope = requestedEnvelope.transform(layerCRS, true);
                } catch (FactoryException e) {
                    throw new IllegalArgumentException("유효하지 않은 좌표계입니다: " + request.getSrid(), e);
                } catch (TransformException e) {
                    throw new IllegalArgumentException("좌표 변환에 실패했습니다.", e);
                }

                try {
                    if (layer.getLyrSrvcPrefix() == null || layer.getLyrSrvcPrefix().isEmpty()) {
                        layer.setLyrSrvcPrefix(prefix);
                    }
                    WMTSLayer layerInfo = wmts.getCapabilities()
                            .getLayer(layer.getLyrSrvcPrefix() + ":" + layer.getLyrSrvcNm());
                    if (layerInfo == null) {
                        String raw = layer.getFlightPhotoLyrNm() == null ? "layer" : layer.getFlightPhotoLyrNm();
                        String safe = raw.replaceAll("[\\\\/:*?\"<>|\\s]+", "_");
                        String entryName = String.format("%s.txt", safe);
                        zipOut.putNextEntry(new ZipEntry(entryName));
                        try {
                            String msg = "요청한 레이어를 찾을 수 없습니다. 문제가 지속되면 관리자에게 문의해 주세요.";
                            logger.error(
                                    "Flight photo layer not found (layerIdentifier={}, layerName={})",
                                    layer.getLyrSrvcPrefix() + ":" + layer.getLyrSrvcNm(),
                                    layer.getFlightPhotoLyrNm()
                            );
                            zipOut.write(msg.getBytes(StandardCharsets.UTF_8));
                        } finally {
                            zipOut.closeEntry();
                            zipOut.flush();
                        }
                        continue;
                    }

                    WMTSMapLayer mapLayer = new WMTSMapLayer(wmts, layerInfo);

                    mapContent = new MapContent();
                    mapContent.getViewport().setBounds(layerEnvelope);
                    mapContent.getViewport().setScreenArea(paintArea);
                    mapContent.getViewport().setMatchingAspectRatio(false);
                    mapContent.addLayer(mapLayer);

                    StreamingRenderer renderer = new StreamingRenderer();
                    renderer.setMapContent(mapContent);

                    image = new BufferedImage(width, height, BufferedImage.TYPE_INT_ARGB);
                    g = image.createGraphics();
                    g.setRenderingHint(RenderingHints.KEY_INTERPOLATION, RenderingHints.VALUE_INTERPOLATION_BILINEAR);

                    renderer.paint(g, paintArea, layerEnvelope);

                    String raw = layer.getFlightPhotoLyrNm() == null ? "layer" : layer.getFlightPhotoLyrNm();
                    String safe = raw.replaceAll("[\\\\/:*?\"<>|\\s]+", "_");
                    String entryName = String.format("%s.png", safe);

                    zipOut.putNextEntry(new ZipEntry(entryName));
                    try {
                        ImageIO.write(image, "png", zipOut);
                    } finally {
                        zipOut.closeEntry();
                    }

                    zipOut.flush();

                } catch (IOException e) {
                    logger.error("Layer render failed: id=" + layer.getFlightPhotoLyrId(), e);
                    String raw = layer.getFlightPhotoLyrNm() == null ? "layer" : layer.getFlightPhotoLyrNm();
                    String safe = raw.replaceAll("[\\\\/:*?\"<>|\\s]+", "_");
                    String entryName = String.format("%s.txt", safe);
                    zipOut.putNextEntry(new ZipEntry(entryName));
                    try {
                        String msg = "서버 내부 오류가 발생했습니다, 문제가 계속되면 시스템 관리자에게 문의해 주세요.";
                        zipOut.write(msg.getBytes(StandardCharsets.UTF_8));
                    } finally {
                        zipOut.closeEntry();
                        zipOut.flush();
                    }
                } finally {
                    if (g != null) g.dispose();
                    if (mapContent != null) mapContent.dispose();
                }
            }
        } catch (IOException ioe) {
            logger.error("Streaming zip failed", ioe);
            zipOut.putNextEntry(new ZipEntry("error.txt"));
            try {
                String msg = "서버 내부 오류가 발생했습니다, 문제가 계속되면 시스템 관리자에게 문의해 주세요.";
                zipOut.write(msg.getBytes(StandardCharsets.UTF_8));
            } finally {
                zipOut.closeEntry();
            }
            zipOut.flush();
            throw ioe;
        } finally {
            zipOut.finish();
            zipOut.flush();
        }
    }

    /**
     * 이력 테이블 존재 여부 확인
     */
    private boolean existsHistoryTable(String schemaName, String tableName) {
        try {
            Boolean exists = mapper.checkHistoryTableExists(schemaName, tableName);
            return exists != null && exists;
        } catch (DataAccessException e) {
            logger.debug("이력 테이블 존재 확인 실패: {}.{}", schemaName, tableName, e);
            return false;
        }
    }

    /**
     * Added 피처 이력 저장 (STTS_CD='C')
     * 이력 저장 실패해도 본 트랜잭션에 영향 없음
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void insertHistoryAdded(String schemaName, String tableName, Map<String, Object> params) {
        try {
            String historyTableName = tableName + "_h";
            if (existsHistoryTable(schemaName, historyTableName)) {
                Map<String, Object> historyParams = new HashMap<>(params);
                historyParams.put("tableName", historyTableName);

                int insertedCount = mapper.batchInsertHistoryAdded(historyParams);
                logger.debug("Added 이력 저장 완료: {}.{} - {} 건", schemaName, historyTableName, insertedCount);
            }
        } catch (DataAccessException e) {
            logger.warn("Added 이력 저장 실패 (무시) - table: {}.{}_h",
                       schemaName, tableName, e);
        }
    }

    /**
     * Modified 피처 이력 저장 (STTS_CD='U')
     * 이력 저장 실패해도 본 트랜잭션에 영향 없음
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void insertHistoryModified(String schemaName, String tableName, Map<String, Object> params) {
        try {
            String historyTableName = tableName + "_h";
            if (existsHistoryTable(schemaName, historyTableName)) {
                Map<String, Object> historyParams = new HashMap<>(params);
                historyParams.put("tableName", historyTableName);

                int insertedCount = mapper.batchInsertHistoryModified(historyParams);
                logger.debug("Modified 이력 저장 완료: {}.{} - {} 건", schemaName, historyTableName, insertedCount);
            }
        } catch (DataAccessException e) {
            logger.warn("Modified 이력 저장 실패 (무시) - table: {}.{}_h",
                       schemaName, tableName, e);
        }
    }

    /**
     * Deleted 피처 이력 저장 (STTS_CD='D')
     * 이력 저장 실패해도 본 트랜잭션에 영향 없음
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void insertHistoryDeleted(String schemaName, String tableName, Map<String, Object> params) {
        try {
            String historyTableName = tableName + "_h";
            if (existsHistoryTable(schemaName, historyTableName)) {
                Map<String, Object> historyParams = new HashMap<>();
                historyParams.put("schemaName", schemaName);
                historyParams.put("tableName", historyTableName);
                historyParams.put("gids", params.get("gids"));

                int insertedCount = mapper.batchInsertHistoryDeleted(historyParams);
                logger.debug("Deleted 이력 저장 완료: {}.{} - {} 건", schemaName, historyTableName, insertedCount);
            }
        } catch (DataAccessException e) {
            logger.warn("Deleted 이력 저장 실패 (무시) - table: {}.{}_h",
                       schemaName, tableName, e);
        }
    }
}