package incheon.cmm.g2f.download.repository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import java.util.LinkedHashMap;
import java.util.Map;
import java.util.function.Consumer;

@Repository
public class DownloadDataRepository {

	private final JdbcTemplate jdbcTemplate;

	@Autowired
	public DownloadDataRepository(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/*
	 * ============================================================ 1) geometry 컬럼
	 * 조회 ============================================================
	 */
	public String selectGeometryColumnName(String schemaName, String tableName) {
		validateTableName(schemaName);
		validateTableName(tableName);

		String sql = """
				    SELECT f_geometry_column
				    FROM geometry_columns
				    WHERE f_table_schema = ? AND f_table_name = ?
				    LIMIT 1
				""";

		return jdbcTemplate.queryForObject(sql, String.class, schemaName, tableName);
	}

	/*
	 * ============================================================ 2) 기존 Shapefile
	 * 방식 (전체 SELECT) ============================================================
	 */
	public List<Map<String, Object>> selectShapefileDownloadData(String fullTableName, String geomColumn) {
		validateTableName(fullTableName);
		validateColumnName(geomColumn);

		String sql = String.format("""
				    SELECT
				        ST_AsBinary(%s) AS wkb_geometry,
				        ST_AsText(%s) AS wkt_geometry,
				        ST_SRID(%s) AS geometry_srid,
				        *
				    FROM %s
				    WHERE %s IS NOT NULL
				""", geomColumn, geomColumn, geomColumn, fullTableName, geomColumn);

		return jdbcTemplate.queryForList(sql);
	}

	public void streamExcelDownloadData(String fullTableName, String geomColumn,
			Consumer<Map<String, Object>> rowConsumer) {
		validateTableName(fullTableName);
		validateColumnName(geomColumn);

		String sql = String.format("""
				    SELECT
				        ST_AsBinary(%s) AS wkb_geometry,
				        ST_AsText(%s) AS wkt_geometry,
				        ST_SRID(%s) AS geometry_srid,
				        *
				    FROM %s
				    WHERE %s IS NOT NULL
				""", geomColumn, geomColumn, geomColumn, fullTableName, geomColumn);

		jdbcTemplate.query(con -> {
			con.setAutoCommit(false); // ⭐ PostgreSQL streaming 필수

			PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ps.setFetchSize(50_000);
			return ps;
		}, rs -> {
			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));
			}

			rowConsumer.accept(row);
		});
	}

	/*
	 * ============================================================ 3)
	 * Excel/CSV/GeoJSON Streaming 방식 메모리 절대 안 먹는 방식
	 * ============================================================
	 */
	public void streamDownloadData(String fullTableName, String geomColumn, long offset, long limit,
			Consumer<Map<String, Object>> rowConsumer) {
		validateTableName(fullTableName);
		validateColumnName(geomColumn);

		String sql = String.format("""
				    SELECT
				        ST_AsText(%s) as wkt_geometry,
				        ST_SRID(%s) as geometry_srid,
				        *
				    FROM %s
				    WHERE %s IS NOT NULL
				    OFFSET %d
				    LIMIT %d
				""", geomColumn, geomColumn, fullTableName, geomColumn, offset, limit);

		jdbcTemplate.query(sql, (RowCallbackHandler) rs -> {
			Map<String, Object> row = convertRow(rs);
			rowConsumer.accept(row);
		});
	}

	/*
	 * ============================================================ ResultSet → Map
	 * 변환 ============================================================
	 */
	private Map<String, Object> convertRow(ResultSet rs) throws SQLException {
		Map<String, Object> row = new HashMap<>();
		ResultSetMetaData meta = rs.getMetaData();
		int count = meta.getColumnCount();

		for (int i = 1; i <= count; i++) {
			String key = meta.getColumnLabel(i);
			Object value = rs.getObject(i);
			row.put(key, value);
		}
		return row;
	}

	/*
	 * ============================================================ 4) GeoJSON
	 * Feature Streaming (DB에서 JSON 생성)
	 * ============================================================
	 */
	public void streamGeoJsonFeature(String fullTableName, String geomColumn, Consumer<String> featureConsumer) {
		validateTableName(fullTableName);
		validateColumnName(geomColumn);

		String sql = String.format("""
				    SELECT jsonb_build_object(
				        'type', 'Feature',
				        'geometry', ST_AsGeoJSON(%s)::jsonb,
				        'properties', to_jsonb(t) - '%s'
				    )::text AS feature
				    FROM %s t
				    WHERE %s IS NOT NULL
				""", geomColumn, geomColumn, fullTableName, geomColumn);

		jdbcTemplate.query(con -> {
			con.setAutoCommit(false); // PostgreSQL streaming
			PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ps.setFetchSize(10_000);
			return ps;
		}, rs -> {
			featureConsumer.accept(rs.getString("feature"));
		});
	}

	/*
	 * ============================================================ Validation
	 * ============================================================
	 */
	private void validateTableName(String tableName) {
		if (tableName == null || tableName.trim().isEmpty())
			throw new IllegalArgumentException("테이블명이 비어있습니다.");

		if (!tableName.matches("^[a-zA-Z0-9_.가-힣]+$"))
			throw new IllegalArgumentException("유효하지 않은 테이블명 형식: " + tableName);
	}

	private void validateColumnName(String columnName) {
		if (columnName == null || columnName.trim().isEmpty())
			throw new IllegalArgumentException("컬럼명이 비어있습니다.");

		if (!columnName.matches("^[a-zA-Z0-9_가-힣]+$"))
			throw new IllegalArgumentException("유효하지 않은 컬럼명 형식: " + columnName);
	}

	public void streamShapefileData(String fullTableName, String geomColumn,
			Consumer<Map<String, Object>> rowConsumer) {
		validateTableName(fullTableName);
		validateColumnName(geomColumn);

		String sql = String.format("""
				    SELECT
				        ST_AsBinary(%s) AS wkb_geometry,
				        ST_AsText(%s) AS wkt_geometry,
				        ST_SRID(%s) AS geometry_srid,
				        *
				    FROM %s
				    WHERE %s IS NOT NULL
				""", geomColumn, geomColumn, geomColumn, fullTableName, geomColumn);

		jdbcTemplate.query(con -> {
			con.setAutoCommit(false);
			PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ps.setFetchSize(10_000); // Shapefile은 10K 단위로
			return ps;
		}, rs -> {
			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));
			}

			rowConsumer.accept(row);
		});
	}

	public void streamShapefileDxfData(String fullTableName, String geomColumn,
			Consumer<Map<String, Object>> rowConsumer) {
		validateTableName(fullTableName);
		validateColumnName(geomColumn);

		String sql = String.format("""
				SELECT
				ST_AsBinary(%s) AS wkb_geometry,
				ST_AsText(%s) AS wkt_geometry,
				ST_SRID(%s) AS geometry_srid,
				*
				FROM %s
				WHERE %s IS NOT NULL
				""", geomColumn, geomColumn, geomColumn, fullTableName, geomColumn);

		jdbcTemplate.query(con -> {
			con.setAutoCommit(false);
			PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ps.setFetchSize(10_000);
			return ps;
		}, rs -> {
			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));
			}

			rowConsumer.accept(row);
		});
	}
}