package incheon.ags.mrb.upload.service;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.StringWriter;
import java.nio.charset.Charset;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.geotools.api.referencing.FactoryException;
import org.geotools.api.referencing.operation.TransformException;
import org.geotools.geojson.geom.GeometryJSON;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.locationtech.jts.geom.Envelope;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKTReader;
import org.geotools.api.referencing.crs.CoordinateReferenceSystem;
import org.geotools.api.referencing.operation.MathTransform;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.time.LocalDate;
import java.time.format.DateTimeParseException;
import java.util.regex.Pattern;

import com.fasterxml.jackson.databind.ObjectMapper;

import incheon.ags.mrb.main.exception.ErrorCode;
import incheon.ags.mrb.main.exception.RecipeException;
import incheon.ags.mrb.upload.mapper.ProcessFileMapper;
import incheon.ags.mrb.upload.service.util.CoordinateValidationUtil;
import incheon.ags.mrb.upload.vo.FileUploadRequestDTO;

@Service
public class ProcessCsvFileService {

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

    private final ProcessFileMapper processFileMapper;
    private final FileProcessUtilService fileProcessUtilService;
    private final TableCreationService tableCreationService;
    private final DataInsertionService dataInsertionService;
    private static final String TARGET_EPSG = "EPSG:3857";
    @Autowired private ObjectMapper objectMapper;

    @Autowired
    public ProcessCsvFileService(ProcessFileMapper processFileMapper,
            FileProcessUtilService fileProcessUtilService,
            TableCreationService tableCreationService,
            DataInsertionService dataInsertionService) {
        this.processFileMapper = processFileMapper;
        this.fileProcessUtilService = fileProcessUtilService;
        this.tableCreationService = tableCreationService;
        this.dataInsertionService = dataInsertionService;
    }

    // 내부 상태 전달용 DTO (로직 간소화를 위해 유지)
    private static class GeoJsonPreviewResult {
        private Map<String, Object> geoJson;
        private final Envelope envelope = new Envelope();
        private final Envelope originalEnvelope = new Envelope();
        private boolean outOfBounds = false;
        private boolean invalidCoordinate = false;
        private boolean sridMismatch = false;
        private int geoJsonFeatures = 0;
    }

    // ========================================================================
    // 1. CSV (X/Y 좌표) 업로드 처리
    // ========================================================================
    public Map<String, Object> processCsvXyFile(FileUploadRequestDTO dto) throws IOException {
        String safeTableName = fileProcessUtilService.generateUniqueTableName();
        String filecoordinate = dto.getFilecoordinate();
        String xColumnName = dto.getFirstColumn();
        String yColumnName = dto.getSecondColumn();
        int srid = fileProcessUtilService.extractSridFromCoordinate(filecoordinate);

        // 제외할 컬럼 필터링
        List<String> allColumns = Arrays.asList(dto.getFileColumnList().split(","))
                .stream()
                .map(String::trim)
                .filter(col -> !col.isEmpty())
                .filter(col -> !col.equalsIgnoreCase("gid") && !col.equalsIgnoreCase("geom"))
                .collect(Collectors.toList());

        // 타입 추론 및 SQL 생성 (MultipartFile 재사용 필요)
        String columnsSql = fileProcessUtilService.detectCsvColumnTypesAndBuildSql(dto.getFile(), dto.getEncoding(),
                allColumns);

        // 테이블 생성
        tableCreationService.createCsvTable(safeTableName, srid, columnsSql);

        // 데이터 삽입
        Map<String, Object> insertResult = dataInsertionService.insertCsvXyData(dto, safeTableName, srid, allColumns,
                xColumnName, yColumnName);

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

    // ========================================================================
    // 2. CSV (WKT) 업로드 처리
    // ========================================================================
    public Map<String, Object> processCsvWktFile(FileUploadRequestDTO dto) throws IOException {
        String safeTableName = fileProcessUtilService.generateUniqueTableName();
        String wktColumnName = dto.getWktColumn().trim().replace(",", "");
        int srid = fileProcessUtilService.extractSridFromCoordinate(dto.getFilecoordinate());

        List<String> originalColumns = Arrays.asList(dto.getFileColumnList().split(","))
                .stream()
                .map(String::trim)
                .filter(col -> !col.isEmpty())
                .filter(col -> !col.equalsIgnoreCase("gid") && !col.equalsIgnoreCase("geom"))
                .collect(Collectors.toList());

        List<String> dbColumns = originalColumns.stream()
                .filter(col -> !col.equalsIgnoreCase(wktColumnName))
                .collect(Collectors.toList());

        // 타입 추론 및 SQL 생성
        String columnsSql = fileProcessUtilService.detectCsvColumnTypesAndBuildSql(dto.getFile(), dto.getEncoding(),
                dbColumns);

        // 테이블 생성
        tableCreationService.createCsvwktTable(safeTableName, srid, columnsSql);

        // 데이터 삽입
        Map<String, Object> insertResult = dataInsertionService.insertCsvWktData(dto, safeTableName, srid, dbColumns,
                wktColumnName, originalColumns);

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

    // ========================================================================
    // 3. CSV (X/Y) 미리보기
    // ========================================================================
    public Map<String, Object> previewCsvFile(
            MultipartFile currentPreviewFile,
            int limit,
            String coordinate,
            String firstColumn,
            String secondColumn,
            String encoding) throws Exception {

        // 1. 변환 로직 실행
        GeoJsonPreviewResult preview = csvToGeoJson(currentPreviewFile, firstColumn, secondColumn, limit, coordinate,
                encoding);

        // 2. 결과 검증 및 예외 발생 (이제 Map을 리턴하지 않고 Exception을 던짐)
        validatePreviewResult(preview, coordinate);

        // 3. 성공 데이터 반환 (geojson 키가 포함된 Map)
        Map<String, Object> result = new HashMap<>();
        result.put("geojson", preview.geoJson);
        return result;
    }

    // ========================================================================
    // 4. CSV (WKT) 미리보기
    // ========================================================================
    public Map<String, Object> previewCsvWktFile(
            MultipartFile currentPreviewFile,
            int limit,
            String coordinate,
            String wktColumn,
            String encoding) throws Exception {

        // 1. 변환 로직 실행
        GeoJsonPreviewResult preview = csvWktToGeoJson(currentPreviewFile, wktColumn, limit, coordinate, encoding);

        // 2. 결과 검증 및 예외 발생
        validatePreviewResult(preview, coordinate);

        // 3. 성공 데이터 반환
        Map<String, Object> result = new HashMap<>();
        result.put("geojson", preview.geoJson);
        return result;
    }

    // ========================================================================
    // 공통 검증 로직 (CSV, WKT 공용)
    // ========================================================================
    private void validatePreviewResult(GeoJsonPreviewResult preview, String coordinate) {
        // 파싱 실패
        if (preview.geoJson == null) {
            throw new RecipeException(ErrorCode.FILE_PARSE_FAILED, "파일에서 유효한 데이터를 읽지 못했습니다.");
        }

        // 유효한 좌표 없음
        if (preview.geoJsonFeatures == 0 || preview.invalidCoordinate) {
            logger.warn("좌표 검증 실패 - features: {}, invalidCoordinate: {}", preview.geoJsonFeatures,
                    preview.invalidCoordinate);
            throw new RecipeException(ErrorCode.INVALID_COORDINATE,
                    "미리보기 좌표를 해석할 수 없습니다.\n좌표 컬럼의 데이터 형식과 좌표계를 다시 확인해주세요.");
        }

        // 대한민국 영역 이탈 (3857 변환 후 체크)
        if (preview.outOfBounds || (!preview.envelope.isNull() &&
                (!CoordinateValidationUtil.isWithinKorea(preview.envelope)
                        || !CoordinateValidationUtil.isReasonableEnvelope(preview.envelope)))) {
            throw new RecipeException(ErrorCode.OUT_OF_KOREA_BOUNDS,
                    "선택한 좌표가 대한민국 지도 서비스 영역을 벗어났습니다.\n올바른 좌표계를 선택했는지 확인해주세요.");
        }

        // 원본 좌표계와 데이터 범위 불일치 (SRID 체크)
        if (preview.sridMismatch) {
            int srid = fileProcessUtilService.extractSridFromCoordinate(coordinate);
            String message = (srid > 0)
                    ? String.format("선택한 좌표계(EPSG:%d)와 데이터의 실제 좌표 범위가 일치하지 않습니다.", srid)
                    : "선택한 좌표계와 데이터의 실제 좌표 범위가 일치하지 않습니다.";
            throw new RecipeException(ErrorCode.INVALID_COORDINATE, message);
        }
    }

    // ========================================================================
    // Helper: 값 타입 변환 (String -> Number, Boolean, Date)
    // ========================================================================
    // 지수 표현(E) 및 실수형 접미사(f, d) 등 포함한 숫자 패턴
    private static final Pattern NUMERIC_PATTERN = Pattern
            .compile("^[-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?[fFdD]?$");
    private static final Pattern DATE_PATTERN = Pattern.compile("^\\d{4}[-/]\\d{1,2}[-/]\\d{1,2}$");

    private Object tryParseValue(String value) {
        if (value == null || value.isBlank()) {
            return value;
        }
        String trimVal = value.trim();

        // 1. Boolean
        if ("true".equalsIgnoreCase(trimVal) || "false".equalsIgnoreCase(trimVal)) {
            return Boolean.parseBoolean(trimVal);
        }

        // 2. 숫자 형식 체크 및 변환
        if (NUMERIC_PATTERN.matcher(trimVal).matches()) {
            try {
                String numStr = trimVal;
                // BigDecimal 파싱을 위해 접미사(f, F, d, D) 제거
                char lastChar = numStr.charAt(numStr.length() - 1);
                if ("fFdD".indexOf(lastChar) >= 0 && !Character.isDigit(lastChar)) {
                    numStr = numStr.substring(0, numStr.length() - 1);
                }

                java.math.BigDecimal bd = new java.math.BigDecimal(numStr);

                // 소수점이 없거나(0이거나), stripTrailingZeros 후 scale이 0이면 정수
                if (bd.scale() <= 0 || bd.stripTrailingZeros().scale() <= 0) {
                    try {
                        long lVal = bd.longValueExact();
                        if (lVal >= Integer.MIN_VALUE && lVal <= Integer.MAX_VALUE) {
                            return (int) lVal;
                        }
                        return lVal;
                    } catch (ArithmeticException e) {
                        // Long 범위 초과 -> 문자열 반환 (toPlainString)
                        return bd.toPlainString();
                    }
                } else {
                    // 실수 -> Double 유효 범위 체크
                    double dVal = bd.doubleValue();
                    if (Double.isFinite(dVal) && !Double.isNaN(dVal)) {
                        return dVal;
                    }
                    return bd.toPlainString();
                }

            } catch (Exception e) {
                // 파싱 실패 시 문자열로 유지
                return trimVal;
            }
        }

        // 3. 날짜 형식 체크 및 변환 (YYYY-MM-DD 또는 YYYY/MM/DD)
        if (DATE_PATTERN.matcher(trimVal).matches()) {
            String dateVal = trimVal.replace("/", "-");
            try {
                return LocalDate.parse(dateVal);
            } catch (DateTimeParseException e) {
                // 날짜 파싱 실패 시 문자열로 유지
                return trimVal;
            }
        }

        return value;
    }

    // ========================================================================
    // Helper: CSV -> GeoJSON 변환
    // ========================================================================
    private GeoJsonPreviewResult csvToGeoJson(MultipartFile file, String xCol, String yCol, int limit,
            String coordinate, String encoding) throws Exception {
        GeoJsonPreviewResult preview = new GeoJsonPreviewResult();
        List<Map<String, Object>> features = new ArrayList<>();

        MathTransform transform = createTransform(coordinate);
        String charsetName = (encoding != null && !encoding.isEmpty()) ? encoding : "UTF-8";

        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(file.getInputStream(), Charset.forName(charsetName)))) {

            String headerLine = reader.readLine();
            if (headerLine == null) {
                return preview; // 빈 파일
            }

            if (headerLine.startsWith("\uFEFF")) {
                headerLine = headerLine.substring(1);
            }

            List<String> headerList = fileProcessUtilService.parseCsvLineWithQuotes(headerLine);
            String[] headers = headerList.toArray(new String[0]);
            int xIndex = -1;
            int yIndex = -1;

            for (int i = 0; i < headers.length; i++) {
                String cleanHeader = headers[i].trim();
                if (cleanHeader.equalsIgnoreCase(xCol.trim())) {
                    xIndex = i;
                }
                if (cleanHeader.equalsIgnoreCase(yCol.trim())) {
                    yIndex = i;
                }
            }

            if (xIndex == -1 || yIndex == -1) {
                throw new RecipeException(ErrorCode.INVALID_INPUT_VALUE,
                        String.format("CSV 파일에서 지정된 컬럼을 찾을 수 없습니다. (요청: X='%s', Y='%s')", xCol, yCol));
            }

            String line;
            int count = 0;
            while ((line = reader.readLine()) != null && count < limit) {
                List<String> valueList = fileProcessUtilService.parseCsvLineWithQuotes(line);
                String[] values = valueList.toArray(new String[0]);

                if (xIndex >= values.length || yIndex >= values.length) {
                    continue;
                }

                try {
                    String xStr = values[xIndex].trim();
                    String yStr = values[yIndex].trim();

                    if (xStr.isEmpty() || yStr.isEmpty())
                        continue;

                    // 좌표값도 BigDecimal을 사용하여 안전하게 파싱 (지수 형태 허용)
                    java.math.BigDecimal xBd = new java.math.BigDecimal(xStr);
                    java.math.BigDecimal yBd = new java.math.BigDecimal(yStr);

                    double x = xBd.doubleValue();
                    double y = yBd.doubleValue();

                    preview.originalEnvelope.expandToInclude(x, y);

                    double outX = x;
                    double outY = y;

                    // 좌표 변환
                    if (transform != null) {
                        double[] src = new double[] { x, y };
                        double[] dest = new double[2];
                        transform.transform(src, 0, dest, 0, 1);
                        outX = dest[0];
                        outY = dest[1];
                    }

                    if (!Double.isFinite(outX) || !Double.isFinite(outY)
                            || !CoordinateValidationUtil.isReasonableCoordinate(outX, outY)) {
                        preview.outOfBounds = true;
                        preview.invalidCoordinate = true;
                        break;
                    }

                    preview.envelope.expandToInclude(outX, outY);

                    Map<String, Object> feature = new HashMap<>();
                    feature.put("type", "Feature");
                    feature.put("geometry", Map.of(
                            "type", "Point",
                            "coordinates", Arrays.asList(outX, outY)));

                    Map<String, Object> properties = new HashMap<>();
                    for (int i = 0; i < headers.length; i++) {
                        if (i != xIndex && i != yIndex && i < values.length) {
                            // 타입 변환 적용
                            properties.put(headers[i], tryParseValue(values[i]));
                        }
                    }
                    feature.put("properties", properties);

                    features.add(feature);
                    count++;

                } catch (NumberFormatException e) {
                    // 숫자 파싱 실패는 무시하고 다음 행 진행하거나, 에러 플래그 설정
                    continue;
                } catch (Exception e) {
                    preview.invalidCoordinate = true;
                    break;
                }
            }
        }

        int srid = fileProcessUtilService.extractSridFromCoordinate(coordinate);
        if (srid > 0 && !CoordinateValidationUtil.isEnvelopeConsistentWithSrid(preview.originalEnvelope, srid)) {
            preview.sridMismatch = true;
        }

        if (!features.isEmpty()) {
            preview.geoJsonFeatures = features.size();
            preview.geoJson = Map.of(
                    "type", "FeatureCollection",
                    "features", features);
        }

        return preview;
    }

    // ========================================================================
    // Helper: CSV WKT -> GeoJSON 변환
    // ========================================================================
    private GeoJsonPreviewResult csvWktToGeoJson(MultipartFile file, String wktColumn, int limit, String coordinate,
            String encoding)
            throws FactoryException, IOException, ParseException {
        GeoJsonPreviewResult preview = new GeoJsonPreviewResult();
        List<Map<String, Object>> features = new ArrayList<>();

        WKTReader wktReader = new WKTReader();
        GeometryJSON geometryJSON = new GeometryJSON();
        MathTransform transform = createTransform(coordinate);
        String charsetName = (encoding != null && !encoding.isEmpty()) ? encoding : "UTF-8";

        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(file.getInputStream(), Charset.forName(charsetName)))) {

            String headerLine = reader.readLine();
            if (headerLine == null) {
                return preview;
            }

            if (headerLine.startsWith("\uFEFF")) {
                headerLine = headerLine.substring(1);
            }

            List<String> headerList = fileProcessUtilService.parseCsvLineWithQuotes(headerLine);
            String[] headers = headerList.toArray(new String[0]);

            int wktIndex = -1;
            for (int i = 0; i < headers.length; i++) {
                if (headers[i].trim().equalsIgnoreCase(wktColumn.trim())) {
                    wktIndex = i;
                    break;
                }
            }

            if (wktIndex == -1) {
                throw new RecipeException(ErrorCode.INVALID_INPUT_VALUE, "CSV 파일에서 지정된 WKT 컬럼을 찾을 수 없습니다.");
            }

            String line;
            int successCount = 0;

            while ((line = reader.readLine()) != null && successCount < limit) {
                try {
                    List<String> valueList = fileProcessUtilService.parseCsvLineWithQuotes(line);
                    String[] values = valueList.toArray(new String[0]);

                    if (wktIndex >= values.length)
                        continue;

                    String wktValue = values[wktIndex].trim();
                    if (wktValue.isEmpty() || wktValue.equalsIgnoreCase("null"))
                        continue;

                    Geometry geometry = wktReader.read(wktValue);
                    if (geometry == null || geometry.isEmpty())
                        continue;

                    preview.originalEnvelope.expandToInclude(geometry.getEnvelopeInternal());

                    // 좌표 변환
                    Geometry transformedGeometry = transform != null ? JTS.transform(geometry, transform) : geometry;

                    if (transformedGeometry == null || transformedGeometry.isEmpty()) {
                        preview.invalidCoordinate = true;
                        break;
                    }

                    Envelope geomEnvelope = transformedGeometry.getEnvelopeInternal();
                    if (!CoordinateValidationUtil.isReasonableEnvelope(geomEnvelope)) {
                        preview.outOfBounds = true;
                        preview.invalidCoordinate = true;
                        break;
                    }
                    preview.envelope.expandToInclude(geomEnvelope);

                    Map<String, Object> feature = new HashMap<>();
                    feature.put("type", "Feature");

                    try (StringWriter writer = new StringWriter()) {
                        geometryJSON.write(transformedGeometry, writer);
                        Map<String, Object> geoJsonGeom = objectMapper.readValue(writer.toString(), Map.class);
                        feature.put("geometry", geoJsonGeom);
                    }

                    Map<String, Object> properties = new HashMap<>();
                    for (int i = 0; i < headers.length; i++) {
                        if (i != wktIndex && i < values.length) {
                            // 타입 변환 적용
                            properties.put(headers[i].trim(), tryParseValue(values[i].trim()));
                        }
                    }
                    feature.put("properties", properties);

                    features.add(feature);
                    successCount++;

                } catch (IOException | RuntimeException | TransformException | ParseException e) {
                    logger.warn("WKT 파싱 중 오류 발생 (진행함): {}", e.getMessage());
                    continue;
                }
            }
        }

        int srid = fileProcessUtilService.extractSridFromCoordinate(coordinate);
        if (srid > 0 && !CoordinateValidationUtil.isEnvelopeConsistentWithSrid(preview.originalEnvelope, srid)) {
            preview.sridMismatch = true;
        }

        if (!features.isEmpty()) {
            preview.geoJsonFeatures = features.size();
            preview.geoJson = Map.of("type", "FeatureCollection", "features", features);
        }
        return preview;
    }

    private MathTransform createTransform(String coordinate) throws FactoryException {
        String source = (coordinate == null || coordinate.isBlank()) ? TARGET_EPSG : coordinate.trim();
        CoordinateReferenceSystem sourceCRS = CRS.decode(source, true);
        CoordinateReferenceSystem targetCRS = CRS.decode(TARGET_EPSG, true);
        if (CRS.equalsIgnoreMetadata(sourceCRS, targetCRS)) {
            return null;
        }
        return CRS.findMathTransform(sourceCRS, targetCRS, true);
    }
}