package incheon.ags.mrb.upload.service;

import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.geotools.api.feature.Property;
import org.geotools.api.feature.simple.SimpleFeature;
import org.geotools.api.feature.simple.SimpleFeatureType;
import org.geotools.api.feature.type.GeometryDescriptor;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.feature.FeatureIterator;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.Point;
import org.locationtech.jts.geom.MultiPoint;
import org.locationtech.jts.geom.LineString;
import org.locationtech.jts.geom.MultiLineString;
import org.locationtech.jts.geom.Polygon;
import org.locationtech.jts.geom.MultiPolygon;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;

import com.fasterxml.jackson.databind.JsonNode;

import incheon.ags.mrb.upload.mapper.ProcessFileMapper;
import incheon.ags.mrb.upload.vo.FileUploadRequestDTO;
import incheon.com.security.vo.LoginVO;

@Service
public class DataInsertionService {

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

    private final ProcessFileMapper processFileMapper;
    private final SqlSessionFactory sqlSessionFactory;
    private final FileProcessUtilService fileProcessUtilService;

    public DataInsertionService(ProcessFileMapper processFileMapper,
            SqlSessionFactory sqlSessionFactory,
            FileProcessUtilService fileProcessUtilService) {
        this.processFileMapper = processFileMapper;
        this.sqlSessionFactory = sqlSessionFactory;
        this.fileProcessUtilService = fileProcessUtilService;
    }

    /**
     * 현재 로그인된 사용자 ID를 조회한다.
     * 
     * @return 사용자 ID, 없으면 "system"
     */
    private String getCurrentUserId() {
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        if (authentication != null && authentication.getPrincipal() instanceof LoginVO) {
            LoginVO loginVO = (LoginVO) authentication.getPrincipal();
            return loginVO.getUserId();
        }
        return "system"; // 인증 정보가 없을 때 기본값
    }

    public Map<String, Object> insertData(String tableName, int srid,
            SimpleFeatureCollection collection,
            FileUploadRequestDTO dto) {
        SimpleFeatureType schema = collection.getSchema();
        GeometryDescriptor geometryDescriptor = schema.getGeometryDescriptor();

        String spceTy = null;
        if (geometryDescriptor != null) {
            Class<?> binding = geometryDescriptor.getType().getBinding();

            if (Point.class.isAssignableFrom(binding) || MultiPoint.class.isAssignableFrom(binding)) {
                spceTy = "POINT";
            } else if (MultiLineString.class.isAssignableFrom(binding)) {
                spceTy = "MULTILINESTRING"; // MultiLineString 별도 구분
            } else if (LineString.class.isAssignableFrom(binding)) {
                spceTy = "LINESTRING";
            } else if (Polygon.class.isAssignableFrom(binding) || MultiPolygon.class.isAssignableFrom(binding)) {
                spceTy = "POLYGON";
            }
        }

        // 현재 로그인한 사용자 ID 설정
        String currentUserId = getCurrentUserId();

        // 그룹 분류 코드 (화면에서 필수로 선택됨)
        String lyrClsfCd = dto.getLayerGroupCategory();

        Map<String, Object> layerInfo = fileProcessUtilService.createLayerInfo(
                dto, srid, currentUserId, "icmrb.", tableName, lyrClsfCd, spceTy);
        processFileMapper.insertUserLayer(layerInfo);

        int insertedCount = 0;
        long startTime = System.currentTimeMillis();

        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            ProcessFileMapper batchMapper = sqlSession.getMapper(ProcessFileMapper.class);

            try (FeatureIterator<SimpleFeature> features = collection.features()) {
                while (features.hasNext()) {
                    SimpleFeature feature = features.next();
                    Geometry geom = (Geometry) feature.getDefaultGeometry();
                    String wkt = geom.toText();

                    StringBuilder columnNames = new StringBuilder();
                    StringBuilder columnValues = new StringBuilder();

                    for (Property prop : feature.getProperties()) {
                        if (!(prop.getValue() instanceof Geometry) && prop.getValue() != null) {
                            String rawPropName = prop.getName().getLocalPart();

                            if (rawPropName == null || rawPropName.trim().isEmpty()) {
                                continue;
                            }

                            String propName = prop.getName().getLocalPart().toLowerCase();

                            if (propName.equals("gid") || propName.equals("geom")) {
                                continue;
                            }

                            if (columnNames.length() > 0) {
                                columnNames.append(", ");
                                columnValues.append(", ");
                            }

                            String propValue = prop.getValue().toString().replace("'", "''");
                            columnNames.append("\"").append(propName).append("\"");
                            columnValues.append("'").append(propValue).append("'");
                        }
                    }

                    batchMapper.insertShapefileFeature(tableName, srid, wkt.trim(),
                            columnNames.toString(),
                            columnValues.toString());
                    insertedCount++;

                    if (insertedCount % FileProcessUtilService.BATCH_COMMIT_INTERVAL == 0) {
                        sqlSession.flushStatements();
                        sqlSession.commit();
                        sqlSession.clearCache();

                        long currentTime = System.currentTimeMillis();
                        long elapsedTime = currentTime - startTime;
                        double avgTimePerRecord = (double) elapsedTime / insertedCount;
                        logger.debug(String.format(
                                "Processed: %d features | 경과시간: %s | 평균: %.2f ms/record",
                                insertedCount,
                                fileProcessUtilService.formatDuration(elapsedTime),
                                avgTimePerRecord));
                    }
                }
                sqlSession.flushStatements();
                sqlSession.commit();
                sqlSession.clearCache();
            }
        }

        long endTime = System.currentTimeMillis();
        long totalTime = endTime - startTime;
        double avgTimePerRecord = (double) totalTime / insertedCount;

        logger.debug("==========================================");
        logger.debug("데이터 삽입 완료!");
        logger.debug(String.format("총 처리 건수: %d features", insertedCount));
        logger.debug(String.format("총 소요 시간: %s", fileProcessUtilService.formatDuration(totalTime)));
        logger.debug(String.format("평균 처리 시간: %.2f ms/record", avgTimePerRecord));
        logger.debug(String.format("처리 속도: %.2f records/sec", 1000.0 / avgTimePerRecord));
        logger.debug("==========================================");

        Map<String, Object> result = new HashMap<>();
        result.put("insertedCount", insertedCount);
        result.put("success", true);

        return result;
    }

    public Map<String, Object> insertCsvXyData(FileUploadRequestDTO dto,
            String safeTableName,
            int srid,
            List<String> allColumns,
            String xColumnName,
            String yColumnName) throws IOException {
        // 현재 로그인한 사용자 ID 설정
        String currentUserId = getCurrentUserId();

        // 그룹 분류 코드 (화면에서 필수로 선택됨)
        String lyrClsfCd = dto.getLayerGroupCategory();

        // 1. 레이어 정보 먼저 삽입 (Shapefile과 동일)
        // ✅ 사용자가 선택한 SRID 사용 (하드코딩 제거)
        Map<String, Object> layerInfo = fileProcessUtilService.createLayerInfo(
                dto, srid, currentUserId, "icmrb.", safeTableName, lyrClsfCd, "POINT");
        processFileMapper.insertUserLayer(layerInfo);
        logger.debug("레이어 정보 등록 완료: " + safeTableName);

        // 2. 그 다음 배치 데이터 삽입
        String encoding = dto.getEncoding() != null ? dto.getEncoding() : "UTF-8";
        List<Map<String, String>> rows = fileProcessUtilService.extractRowsFromMultipartFile(
                dto.getFile(), encoding, allColumns, false);
        int insertedCount = 0;

        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            ProcessFileMapper batchMapper = sqlSession.getMapper(ProcessFileMapper.class);

            for (Map<String, String> row : rows) {
                double x = Double.parseDouble(row.get(xColumnName));
                double y = Double.parseDouble(row.get(yColumnName));

                StringBuilder columnNames = new StringBuilder();
                StringBuilder columnValues = new StringBuilder();

                for (String column : allColumns) {
                    if (columnNames.length() > 0) {
                        columnNames.append(", ");
                        columnValues.append(", ");
                    }

                    columnNames.append("\"").append(column).append("\"");
                    String value = row.get(column);
                    if (value == null || value.isEmpty()) {
                        columnValues.append("NULL");
                    } else {
                        columnValues.append("'").append(value.replace("'", "''")).append("'");
                    }
                }

                batchMapper.insertRecord(safeTableName, srid, x, y,
                        columnNames.toString(), columnValues.toString());
                insertedCount++;

                if (insertedCount % FileProcessUtilService.BATCH_COMMIT_INTERVAL == 0) {
                    sqlSession.flushStatements();
                    sqlSession.commit();
                    sqlSession.clearCache();
                    logger.debug("Processed: " + insertedCount + " records");
                }
            }
            sqlSession.flushStatements();
            sqlSession.commit();
            sqlSession.clearCache();
            logger.debug("데이터 삽입 완료: " + insertedCount + " records");

        }

        Map<String, Object> result = new HashMap<>();
        result.put("insertedCount", insertedCount);
        result.put("success", true);
        return result;
    }

    public Map<String, Object> insertCsvWktData(FileUploadRequestDTO dto,
            String safeTableName,
            int srid,
            List<String> dbColumns,
            String wktColumnName,
            List<String> readColumns) throws IOException {
        String encoding = dto.getEncoding() != null ? dto.getEncoding() : "UTF-8";
        List<Map<String, String>> rows = fileProcessUtilService.extractRowsFromMultipartFile(
                dto.getFile(), encoding, readColumns, true);

        // 공간 타입 먼저 추출
        String spceTyValue = null;
        if (!rows.isEmpty()) {
            String firstWkt = rows.get(0).get(wktColumnName);
            if (firstWkt != null) {
                spceTyValue = fileProcessUtilService.extractSpaceType(firstWkt.trim());
            }
        }

        // 현재 로그인한 사용자 ID 설정
        String currentUserId = getCurrentUserId();

        // 그룹 분류 코드 (화면에서 필수로 선택됨)
        String lyrClsfCd = dto.getLayerGroupCategory();

        // 1. 레이어 정보 먼저 삽입 (Shapefile과 동일)
        // ✅ 사용자가 선택한 SRID 사용 (하드코딩 제거)
        Map<String, Object> layerInfo = fileProcessUtilService.createLayerInfo(
                dto, srid, currentUserId, "icmrb.", safeTableName, lyrClsfCd, spceTyValue);
        processFileMapper.insertUserLayer(layerInfo);
        logger.debug("레이어 정보 등록 완료: " + safeTableName);

        // 2. 그 다음 배치 데이터 삽입
        int insertedCount = 0;
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            ProcessFileMapper batchMapper = sqlSession.getMapper(ProcessFileMapper.class);

            for (Map<String, String> row : rows) {
                String wktValue = row.get(wktColumnName);
                if (wktValue == null) {
                    throw new RuntimeException("WKT 컬럼 값이 null입니다. 행 데이터: " + row.toString());
                }

                StringBuilder columnNames = new StringBuilder();
                StringBuilder columnValues = new StringBuilder();

                for (String column : dbColumns) {
                    if (columnNames.length() > 0) {
                        columnNames.append(", ");
                        columnValues.append(", ");
                    }
                    columnNames.append("\"").append(column).append("\"");
                    String value = row.get(column);
                    if (value == null || value.isEmpty()) {
                        columnValues.append("NULL");
                    } else {
                        columnValues.append("'").append(value.replace("'", "''")).append("'");
                    }
                }

                batchMapper.insertWktRecord(safeTableName, srid, wktValue.trim(),
                        columnNames.toString(), columnValues.toString());
                insertedCount++;

                if (insertedCount % FileProcessUtilService.BATCH_COMMIT_INTERVAL == 0) {
                    sqlSession.flushStatements();
                    sqlSession.commit();
                    sqlSession.clearCache();
                    logger.debug("Processed: " + insertedCount + " records");
                }
            }
            sqlSession.flushStatements();
            sqlSession.commit();
            sqlSession.clearCache();
            logger.debug("데이터 삽입 완료: " + insertedCount + " records");

        }

        Map<String, Object> result = new HashMap<>();
        result.put("insertedCount", insertedCount);
        result.put("success", true);
        return result;
    }

    public Map<String, Object> insertDxfData(String tableName, JsonNode featuresArray, FileUploadRequestDTO dto) {
        int insertedCount = 0;
        int srid = fileProcessUtilService.extractSridFromCoordinate(dto.getFilecoordinate());

        // 현재 로그인한 사용자 ID 설정
        String currentUserId = getCurrentUserId();

        // 그룹 분류 코드 (화면에서 필수로 선택됨)
        String lyrClsfCd = dto.getLayerGroupCategory();

        // 레이어 정보 등록
        // ✅ 사용자가 선택한 SRID 사용 (하드코딩 제거)
        String spceTyValue = fileProcessUtilService
                .extractGeometryTypeFromGeoJson(featuresArray.get(0).get("geometry"));
        Map<String, Object> layerInfo = fileProcessUtilService.createLayerInfo(
                dto, srid, currentUserId, "icmrb.", tableName, lyrClsfCd, spceTyValue);
        processFileMapper.insertUserLayer(layerInfo);
        logger.debug("레이어 정보 등록 완료: " + tableName);

        // 데이터 삽입
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            ProcessFileMapper batchMapper = sqlSession.getMapper(ProcessFileMapper.class);

            for (JsonNode feature : featuresArray) {
                JsonNode geometry = feature.get("geometry");
                JsonNode properties = feature.get("properties");
                String wkt = fileProcessUtilService.convertGeoJsonGeometryToWKT(geometry);

                // Properties 처리
                StringBuilder columnNames = new StringBuilder();
                StringBuilder columnValues = new StringBuilder();

                if (properties != null) {
                    Iterator<String> fieldNames = properties.fieldNames();
                    while (fieldNames.hasNext()) {
                        String fieldName = fieldNames.next();
                        String fieldValue = properties.get(fieldName).asText();

                        if (fieldName.equalsIgnoreCase("gid") || fieldName.equalsIgnoreCase("geom")) {
                            continue;
                        }

                        if (columnNames.length() > 0) {
                            columnNames.append(", ");
                            columnValues.append(", ");
                        }

                        columnNames.append("\"").append(fieldName.toLowerCase()).append("\"");
                        if (fieldValue == null || fieldValue.isEmpty()) {
                            columnValues.append("NULL");
                        } else {
                            columnValues.append("'").append(fieldValue.replace("'", "''")).append("'");
                        }
                    }
                }

                batchMapper.insertShapefileFeature(tableName, srid, wkt,
                        columnNames.toString(), columnValues.toString());
                insertedCount++;

                if (insertedCount % FileProcessUtilService.BATCH_COMMIT_INTERVAL == 0) {
                    sqlSession.flushStatements();
                    sqlSession.commit();
                    sqlSession.clearCache();
                    logger.debug("Processed: " + insertedCount + " records");
                }
            }

            // 남은 데이터 커밋
            sqlSession.flushStatements();
            sqlSession.commit();
            sqlSession.clearCache();
            logger.debug("데이터 삽입 완료: " + insertedCount + " records");

        }

        Map<String, Object> result = new HashMap<>();
        result.put("insertedCount", insertedCount);
        return result;
    }
}