package incheon.ags.mrb.upload.service.impl;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.nio.file.DirectoryStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.nio.file.StandardOpenOption;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

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

import org.geotools.api.feature.simple.SimpleFeatureType;
import org.geotools.api.feature.type.AttributeDescriptor;
import org.geotools.api.feature.type.GeometryDescriptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.fasterxml.jackson.databind.JsonNode;

import incheon.ags.mrb.upload.service.FileProcessUtilService;
import incheon.ags.mrb.upload.vo.FileUploadRequestDTO;

import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.compress.archivers.zip.ZipArchiveInputStream;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;

@Service
public class FileProcessUtilServiceImpl implements FileProcessUtilService {

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

    @Override
    public Map<String, Object> createLayerInfo(FileUploadRequestDTO dto, int srid,
            String userId, String workspacePrefix,
            String tableName, String lyrClsfCd, String spceTy) {
        Map<String, Object> layerInfo = new HashMap<>();
        String layerName = dto.getLayerName() != null ? dto.getLayerName() : dto.getFile().getOriginalFilename();
        String lyrPhysNm = workspacePrefix + tableName;

        layerInfo.put("userId", userId);
        layerInfo.put("userLyrNm", layerName);
        layerInfo.put("userLyrExpln", dto.getLayerDescription());
        layerInfo.put("lyrPhysNm", lyrPhysNm);
        layerInfo.put("lyrSrvcPrefix", "incheon");
        layerInfo.put("lyrSrvcNm", tableName);
        layerInfo.put("lyrClsfCd", lyrClsfCd);
        layerInfo.put("cntm", 3857);
        layerInfo.put("spceTy", spceTy);

        return layerInfo;
    }

    @Override
    public String generateUniqueTableName() {
        String uniqueId = UUID.randomUUID().toString().replace("-", "").substring(0, 8);
        return "ul_" + uniqueId;
    }

    @Override
    public int extractSridFromCoordinate(String filecoordinate) {
        if (filecoordinate == null || filecoordinate.isEmpty()) {
            return 3857;
        }
        if (filecoordinate.startsWith("EPSG:")) {
            return Integer.parseInt(filecoordinate.substring(5));
        }
        return 0;
    }

    @Override
    public String buildColumnsSql(List<String> columns) {
        StringBuilder builder = new StringBuilder();
        for (String col : columns) {
            if (builder.length() > 0)
                builder.append(", ");
            builder.append('"').append(col.trim()).append("\" VARCHAR(80)");
        }
        return builder.toString();
    }

    @Override
    public List<Map<String, String>> extractRowsFromMultipartFile(MultipartFile file,
            String encoding,
            List<String> columns,
            boolean isWKT) throws IOException {
        List<Map<String, String>> rows = new ArrayList<>();

        try (BufferedReader br = new BufferedReader(
                new InputStreamReader(file.getInputStream(), Charset.forName(encoding)))) {
            String headerLine = br.readLine();
            if (headerLine == null) {
                return rows;
            }

            // BOM 제거 (UTF-8 한정)
            if ("UTF-8".equalsIgnoreCase(encoding.trim()) && headerLine.startsWith("\uFEFF")) {
                headerLine = headerLine.substring(1);
            }

            // EUC-KR 인코딩 파일에 BOM 있을 경우 처리 (필요 시)
            if ("EUC-KR".equalsIgnoreCase(encoding.trim()) && headerLine.startsWith("\uFEFF")) {
                headerLine = headerLine.substring(1);
            }

            String[] headers = parseCsvLineWithQuotes(headerLine).toArray(new String[0]);
            String line;
            while ((line = br.readLine()) != null) {
                List<String> values = parseCsvLineWithQuotes(line);
                Map<String, String> rowMap = new HashMap<>();

                for (int i = 0; i < headers.length; i++) {
                    String key = headers[i].trim();
                    if (columns.contains(key)) {
                        String value = i < values.size() ? values.get(i).trim() : "";
                        rowMap.put(key, value);
                    }
                }
                rows.add(rowMap);
            }
        }

        return rows;
    }

    public List<String> parseCsvLineWithQuotes(String line) {
        List<String> result = new ArrayList<>();
        boolean inQuotes = false;
        StringBuilder sb = new StringBuilder();

        for (char c : line.toCharArray()) {
            if (c == '"') {
                inQuotes = !inQuotes;
            } else if (c == ',' && !inQuotes) {
                result.add(sb.toString());
                sb.setLength(0);
            } else {
                sb.append(c);
            }
        }
        result.add(sb.toString());
        return result;
    }

    @Override
    public String extractSpaceType(String wkt) {
        if (wkt == null || wkt.isEmpty()) {
            return null;
        }

        int idx = wkt.indexOf('(');
        if (idx > 0) {
            return wkt.substring(0, idx).toUpperCase();
        }

        return wkt.toUpperCase();
    }

    @Override
    public Path createTempDirectory() throws IOException {
        return Files.createTempDirectory("shapefile_");
    }

    @Override
    public void extractZipFile(MultipartFile zipFile, Path destDir) throws IOException {
        List<String> encodings = Arrays.asList("UTF-8", "EUC-KR", "CP949");
        // List<String> encodings = Arrays.asList(Charset.defaultCharset().name());

        IOException lastException = null;

        for (String encoding : encodings) {
            try {
                try (ZipArchiveInputStream zis = new ZipArchiveInputStream(
                        new BufferedInputStream(zipFile.getInputStream()), encoding, false)) {

                    ZipArchiveEntry entry;
                    byte[] buffer = new byte[8192];
                    boolean hasEntry = false;

                    while ((entry = zis.getNextEntry()) != null) {
                        hasEntry = true;
                        if (entry.isDirectory())
                            continue;

                        String fileName = Paths.get(entry.getName()).getFileName().toString();

                        Path targetFile = destDir.resolve(fileName);

                        if (!targetFile.normalize().startsWith(destDir.normalize())) {
                            throw new IOException("잘못된 경로 포함: " + entry.getName());
                        }

                        try (BufferedOutputStream bos = new BufferedOutputStream(
                                Files.newOutputStream(targetFile,
                                        StandardOpenOption.CREATE,
                                        StandardOpenOption.TRUNCATE_EXISTING))) {
                            int len;
                            while ((len = zis.read(buffer)) > 0) {
                                bos.write(buffer, 0, len);
                            }
                            bos.flush();
                        }
                    }

                    if (hasEntry) {
                        return;
                    }
                }
            } catch (IllegalArgumentException e) {
                lastException = new IOException(encoding + " 인코딩 실패", e);
                continue;
            } catch (IOException e) {
                lastException = e;
            }
        }

        throw new IOException("압축해제를 실패했습니다 , 인코딩 형식을 확인하세요", lastException);
    }

    @Override
    public Path findShapeFile(Path directory) throws IOException {
        try (DirectoryStream<Path> stream = Files.newDirectoryStream(directory, "*.shp")) {
            for (Path file : stream) {
                return file;
            }
        }
        return null;
    }

    @Override
    public String getGeometryType(GeometryDescriptor geomDesc) {
        if (geomDesc == null)
            return "GEOMETRY";
        return geomDesc.getType().getName().getLocalPart().toUpperCase();
    }

    @Override
    public String generateColumnsSqlFromSchema(SimpleFeatureType schema) {
        StringBuilder columnsSql = new StringBuilder();

        for (AttributeDescriptor attr : schema.getAttributeDescriptors()) {
            String name = attr.getLocalName();

            if (!name.equals("the_geom")) {

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

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

                String columnName = name.toLowerCase();
                String columnType = getPostgreSQLType(attr.getType().getBinding());
                columnsSql.append("\"").append(columnName).append("\" ").append(columnType);
            }
        }

        return columnsSql.toString();
    }

    @Override
    public String getPostgreSQLType(Class<?> javaType) {
        if (String.class.isAssignableFrom(javaType))
            return "VARCHAR";
        if (Integer.class.isAssignableFrom(javaType))
            return "INTEGER";
        if (Long.class.isAssignableFrom(javaType))
            return "BIGINT";
        if (Short.class.isAssignableFrom(javaType))
            return "SMALLINT";
        if (Double.class.isAssignableFrom(javaType))
            return "DOUBLE PRECISION";
        if (Float.class.isAssignableFrom(javaType))
            return "REAL";
        if (Boolean.class.isAssignableFrom(javaType))
            return "BOOLEAN";
        if (java.sql.Timestamp.class.isAssignableFrom(javaType))
            return "TIMESTAMP";
        if (java.sql.Date.class.isAssignableFrom(javaType))
            return "DATE";
        if (java.util.Date.class.isAssignableFrom(javaType))
            return "TIMESTAMP";
        if (java.math.BigDecimal.class.isAssignableFrom(javaType))
            return "NUMERIC";
        if (Object.class.equals(javaType) || javaType == null)
            return "VARCHAR";
        throw new IllegalArgumentException("Unsupported attribute type: " + javaType.getName());
    }

    @Override
    public String formatDuration(long milliseconds) {
        long seconds = milliseconds / 1000;
        long minutes = seconds / 60;
        long hours = minutes / 60;

        if (hours > 0) {
            return String.format("%d시간 %d분 %d초", hours, minutes % 60, seconds % 60);
        } else if (minutes > 0) {
            return String.format("%d분 %d초", minutes, seconds % 60);
        } else {
            return String.format("%.2f초", milliseconds / 1000.0);
        }
    }

    @Override
    public void cleanupTempDirectory(Path tempDir) {
        try {
            Files.walk(tempDir)
                    .sorted(Comparator.reverseOrder())
                    .map(Path::toFile)
                    .forEach(file -> {
                        if (file.delete()) {
                            logger.debug("삭제됨: {}", file.getName());
                        } else {
                            logger.warn("삭제 실패: {}", file.getName());
                        }
                    });
        } catch (IOException e) {
            logger.warn("임시 파일 정리 실패", e);
        }
    }

    @Override
    public Path saveMultipartFileToTemp(MultipartFile file, Path tempDir) throws IOException {
        String originalFilename = file.getOriginalFilename();
        String extension = originalFilename.substring(originalFilename.lastIndexOf('.'));
        String tempFileName = "dxf_input_" + UUID.randomUUID().toString() + extension;
        Path tempFilePath = tempDir.resolve(tempFileName);

        Files.copy(file.getInputStream(), tempFilePath, StandardCopyOption.REPLACE_EXISTING);
        return tempFilePath;
    }

    @Override
    public String generateColumnsFromFirstFeature(JsonNode firstFeature) {
        StringBuilder columnsSql = new StringBuilder();
        JsonNode properties = firstFeature.get("properties");

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

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

                if (columnsSql.length() > 0)
                    columnsSql.append(", ");

                JsonNode valueNode = properties.get(fieldName);
                String columnType = detectJsonNodeType(valueNode);

                columnsSql.append("\"").append(fieldName.toLowerCase()).append("\" ").append(columnType);
            }
        }

        return columnsSql.toString();
    }

    private String detectJsonNodeType(JsonNode node) {
        if (node == null || node.isNull())
            return "TEXT";
        if (node.isInt() || node.isLong())
            return "BIGINT"; // 안전하게 BIGINT
        if (node.isDouble() || node.isFloat())
            return "DOUBLE PRECISION";
        if (node.isBoolean())
            return "BOOLEAN";

        // 문자열인 경우 내용 분석 (선택 사항 - 여기선 JSON 타입만 신뢰)
        // String text = node.asText();
        // if (isInteger(text)) return "BIGINT";
        // if (isDouble(text)) return "DOUBLE PRECISION";

        return "TEXT";
    }

    @Override
    public String extractGeometryTypeFromGeoJson(JsonNode geometryNode) {
        if (geometryNode == null || geometryNode.get("type") == null) {
            return null;
        }

        return geometryNode.get("type").asText().toUpperCase();
    }

    @Override
    public String convertGeoJsonGeometryToWKT(JsonNode geometryNode) {
        if (geometryNode == null || geometryNode.isNull()) {
            return "POINT(0 0)";
        }

        String type = geometryNode.path("type").asText();
        if (type == null || type.isBlank()) {
            return "POINT(0 0)";
        }

        JsonNode coordinates = geometryNode.get("coordinates");

        return switch (type.toUpperCase()) {
            case "POINT" -> "POINT(" + formatPosition(coordinates) + ")";
            case "LINESTRING" -> "LINESTRING(" + formatCoordinateList(coordinates) + ")";
            case "POLYGON" -> "POLYGON(" + formatPolygonRings(coordinates) + ")";
            case "MULTIPOINT" -> "MULTIPOINT(" + formatMultiPoint(coordinates) + ")";
            case "MULTILINESTRING" -> "MULTILINESTRING(" + formatMultiLineString(coordinates) + ")";
            case "MULTIPOLYGON" -> "MULTIPOLYGON(" + formatMultiPolygon(coordinates) + ")";
            default -> throw new IllegalArgumentException("지원하지 않는 지오메트리 타입: " + type);
        };
    }

    private String formatCoordinateList(JsonNode coordinates) {
        if (coordinates == null || !coordinates.isArray() || coordinates.size() == 0) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < coordinates.size(); i++) {
            if (i > 0)
                builder.append(", ");
            builder.append(formatPosition(coordinates.get(i)));
        }
        return builder.toString();
    }

    private String formatPolygonRings(JsonNode rings) {
        if (rings == null || !rings.isArray() || rings.size() == 0) {
            return "()";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < rings.size(); i++) {
            if (i > 0)
                builder.append(", ");
            builder.append("(").append(formatCoordinateList(rings.get(i))).append(")");
        }
        return builder.toString();
    }

    private String formatMultiPoint(JsonNode points) {
        if (points == null || !points.isArray() || points.size() == 0) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < points.size(); i++) {
            if (i > 0)
                builder.append(", ");
            builder.append("(").append(formatPosition(points.get(i))).append(")");
        }
        return builder.toString();
    }

    private String formatMultiLineString(JsonNode lines) {
        if (lines == null || !lines.isArray() || lines.size() == 0) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < lines.size(); i++) {
            if (i > 0)
                builder.append(", ");
            builder.append("(").append(formatCoordinateList(lines.get(i))).append(")");
        }
        return builder.toString();
    }

    private String formatMultiPolygon(JsonNode polygons) {
        if (polygons == null || !polygons.isArray() || polygons.size() == 0) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < polygons.size(); i++) {
            if (i > 0)
                builder.append(", ");
            JsonNode polygon = polygons.get(i);
            builder.append("(").append(formatPolygonRings(polygon)).append(")");
        }
        return builder.toString();
    }

    private String formatPosition(JsonNode position) {
        if (position == null || !position.isArray() || position.size() < 2) {
            return "0 0";
        }
        StringBuilder builder = new StringBuilder();
        builder.append(position.get(0).asDouble());
        builder.append(" ");
        builder.append(position.get(1).asDouble());
        if (position.size() > 2) {
            builder.append(" ");
            builder.append(position.get(2).asDouble());
        }
        return builder.toString();
    }

    @Override
    public String detectCsvColumnTypesAndBuildSql(MultipartFile file, String encoding, List<String> targetColumns)
            throws IOException {
        Map<String, String> columnTypes = new HashMap<>();
        for (String col : targetColumns) {
            columnTypes.put(col, null);
        }

        try (BufferedReader br = new BufferedReader(
                new InputStreamReader(file.getInputStream(), Charset.forName(encoding)))) {
            String headerLine = br.readLine();
            if (headerLine == null)
                return buildColumnsSql(targetColumns);

            if ("UTF-8".equalsIgnoreCase(encoding.trim()) && headerLine.startsWith("\uFEFF")) {
                headerLine = headerLine.substring(1);
            }

            List<String> headers = parseCsvLineWithQuotes(headerLine);

            Map<String, Integer> headerIndex = new HashMap<>();
            for (int i = 0; i < headers.size(); i++) {
                headerIndex.put(headers.get(i).trim(), i);
            }

            String line;
            int rowCount = 0;
            int maxRowsToScan = 100;

            while ((line = br.readLine()) != null && rowCount < maxRowsToScan) {
                List<String> values = parseCsvLineWithQuotes(line);

                for (String col : targetColumns) {
                    Integer idx = headerIndex.get(col);
                    if (idx == null || idx >= values.size())
                        continue;

                    String value = values.get(idx).trim();
                    if (value.isEmpty())
                        continue;

                    String currentType = columnTypes.get(col);
                    String detectedType = detectStringType(value);

                    if ("TEXT".equals(currentType))
                        continue;

                    if ("TEXT".equals(detectedType)) {
                        columnTypes.put(col, "TEXT");
                    } else if ("DATE".equals(detectedType)) {
                        if (currentType == null || "BIGINT".equals(currentType)
                                || "DOUBLE PRECISION".equals(currentType)) {
                            columnTypes.put(col, "DATE");
                        }
                    } else if ("DOUBLE PRECISION".equals(detectedType)) {
                        if (currentType == null || "BIGINT".equals(currentType)) {
                            columnTypes.put(col, "DOUBLE PRECISION");
                        }
                    } else if ("BIGINT".equals(detectedType)) {
                        if (currentType == null) {
                            columnTypes.put(col, "BIGINT");
                        }
                    }
                }
                rowCount++;
            }
        }

        StringBuilder sb = new StringBuilder();
        for (String col : targetColumns) {
            if (sb.length() > 0)
                sb.append(", ");
            String type = columnTypes.get(col);
            if (type == null)
                type = "VARCHAR(255)";

            sb.append("\"").append(col).append("\" ").append(type);
        }
        return sb.toString();
    }

    private static final Pattern NUMERIC_PATTERN = Pattern.compile("^-?\\d+(\\.\\d+)?$");
    private static final Pattern DATE_PATTERN = Pattern.compile("^\\d{4}[-/]\\d{1,2}[-/]\\d{1,2}$");

    private String detectStringType(String value) {
        if (value == null || value.isEmpty())
            return null;
        String trimVal = value.trim();

        // 1. 숫자 체크
        if (NUMERIC_PATTERN.matcher(trimVal).matches()) {
            try {
                if (trimVal.contains(".")) {
                    Double.parseDouble(trimVal);
                    return "DOUBLE PRECISION";
                } else {
                    Long.parseLong(trimVal);
                    return "BIGINT";
                }
            } catch (NumberFormatException e) {
                // 숫자가 너무 커서 Long 범위를 벗어나는 경우 등은 VARCHAR로 처리
                return "VARCHAR";
            }
        }

        // 2. 날짜 체크
        if (DATE_PATTERN.matcher(trimVal).matches()) {
            try {
                LocalDate.parse(trimVal.replace("/", "-"));
                return "DATE";
            } catch (DateTimeParseException e) {
                throw new RuntimeException("유효하지 않은 날짜 데이터가 발견되었습니다: " + trimVal);
            }
        }

        return "TEXT";
    }
}