package incheon.product.geoview2d.download.repository;

import incheon.com.cmm.exception.BusinessException;
import incheon.product.common.config.GeoViewProperties;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.regex.Pattern;

/**
 * 공간 데이터 스트리밍 Repository.
 * JDBC 직접 접근으로 대용량 데이터를 메모리 효율적으로 처리한다.
 */
@Slf4j
@Repository
public class SpatialDataRepository {

    private static final Pattern SAFE_NAME = Pattern.compile("^[a-zA-Z0-9_.가-힣]+$");

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Resource(name = "geoViewProperties")
    private GeoViewProperties geoViewProperties;

    /**
     * 지오메트리 컬럼명 조회.
     */
    public String selectGeometryColumnName(String tableName) {
        validateTableName(tableName);
        String[] parts = tableName.split("\\.");
        String schema = parts.length > 1 ? parts[0] : "public";
        String table = parts.length > 1 ? parts[1] : parts[0];

        String sql = "SELECT f_geometry_column FROM geometry_columns WHERE f_table_schema = ? AND f_table_name = ?";
        List<String> result = jdbcTemplate.queryForList(sql, String.class, schema, table);
        return result.isEmpty() ? "geom" : result.get(0);
    }

    /**
     * Shapefile 다운로드 데이터 조회 (WKT).
     */
    public List<Map<String, Object>> selectShapefileData(String tableName, String geomColumn, List<String> columns) {
        validateTableName(tableName);
        String columnList = buildColumnList(columns);
        String sql = String.format("SELECT ST_AsText(%s) AS wkt_geometry, ST_SRID(%s) AS geometry_srid, %s FROM %s",
                geomColumn, geomColumn, columnList, tableName);
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 스트리밍 데이터 처리 (Excel/CSV용).
     */
    public void streamData(String tableName, String geomColumn, List<String> columns, Consumer<Map<String, Object>> rowConsumer) {
        validateTableName(tableName);
        String columnList = buildColumnList(columns);
        int fetchSize = geoViewProperties.getDownload().getStreamingFetchSize();
        String sql = String.format("SELECT %s FROM %s", columnList, tableName);

        jdbcTemplate.execute((Connection con) -> {
            boolean originalAutoCommit = con.getAutoCommit();
            con.setAutoCommit(false);
            try (PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                ps.setFetchSize(fetchSize);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        rowConsumer.accept(convertRow(rs));
                    }
                }
            } finally {
                con.setAutoCommit(originalAutoCommit);
            }
            return null;
        });
    }

    /**
     * DXF 스트리밍 (WKT 지오메트리 포함).
     */
    public void streamDxfData(String tableName, String geomColumn, List<String> columns, Consumer<Map<String, Object>> rowConsumer) {
        validateTableName(tableName);
        String columnList = buildColumnList(columns);
        int fetchSize = geoViewProperties.getDownload().getStreamingFetchSize();
        String sql = String.format("SELECT ST_AsText(%s) AS wkt_geometry, %s FROM %s", geomColumn, columnList, tableName);

        jdbcTemplate.execute((Connection con) -> {
            boolean originalAutoCommit = con.getAutoCommit();
            con.setAutoCommit(false);
            try (PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                ps.setFetchSize(fetchSize);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        rowConsumer.accept(convertRow(rs));
                    }
                }
            } finally {
                con.setAutoCommit(originalAutoCommit);
            }
            return null;
        });
    }

    /**
     * GeoJSON 스트리밍 (PostGIS 네이티브 JSON 생성).
     */
    public void streamGeoJsonFeatures(String tableName, String geomColumn, List<String> columns, Consumer<String> featureConsumer) {
        validateTableName(tableName);
        validateColumnNames(columns);
        int fetchSize = geoViewProperties.getDownload().getStreamingFetchSize();
        StringBuilder propsBuilder = new StringBuilder();
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) propsBuilder.append(" || ',' || ");
            String col = columns.get(i);
            propsBuilder.append("'\"").append(col).append("\":' || COALESCE(to_json(\"").append(col).append("\")::text, 'null')");
        }

        String sql = String.format(
                "SELECT '{\"type\":\"Feature\",\"geometry\":' || ST_AsGeoJSON(%s)::text || ',\"properties\":{' || %s || '}}' AS feature FROM %s",
                geomColumn, propsBuilder, tableName);

        jdbcTemplate.execute((Connection con) -> {
            boolean originalAutoCommit = con.getAutoCommit();
            con.setAutoCommit(false);
            try (PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                ps.setFetchSize(fetchSize);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        featureConsumer.accept(rs.getString("feature"));
                    }
                }
            } finally {
                con.setAutoCommit(originalAutoCommit);
            }
            return null;
        });
    }

    /**
     * 페이징 데이터 조회.
     */
    public List<Map<String, Object>> selectData(String tableName, List<String> columns, int offset, int limit) {
        validateTableName(tableName);
        String columnList = buildColumnList(columns);
        String sql = String.format("SELECT %s FROM %s LIMIT %d OFFSET %d", columnList, tableName, limit, offset);
        return jdbcTemplate.queryForList(sql);
    }

    private Map<String, Object> convertRow(ResultSet rs) throws java.sql.SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Map<String, Object> row = new LinkedHashMap<>();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            row.put(meta.getColumnLabel(i), rs.getObject(i));
        }
        return row;
    }

    private String buildColumnList(List<String> columns) {
        if (columns == null || columns.isEmpty()) return "*";
        validateColumnNames(columns);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) sb.append(", ");
            sb.append("\"").append(columns.get(i)).append("\"");
        }
        return sb.toString();
    }

    private void validateColumnNames(List<String> columns) {
        if (columns == null) return;
        for (String col : columns) {
            if (!SAFE_NAME.matcher(col).matches()) {
                throw new BusinessException("유효하지 않은 컬럼명: " + col, HttpStatus.BAD_REQUEST);
            }
        }
    }

    private void validateTableName(String tableName) {
        if (tableName == null || !SAFE_NAME.matcher(tableName.replace(".", "")).matches()) {
            throw new BusinessException("유효하지 않은 테이블명: " + tableName, HttpStatus.BAD_REQUEST);
        }
    }
}