package incheon.ags.uis.link.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Oracle DB 직접 조회 핸들러 (시설물 연계용)
 *
 * @author jiwon
 * @since 2025-12-29
 */
@Component
@Slf4j
public class OracleHandler {

    private final DataSource oracleDataSource;

    public OracleHandler(@Qualifier("oracleDataSource") DataSource oracleDataSource) {
        this.oracleDataSource = oracleDataSource;
    }

    /**
     * 특정 테이블의 데이터 조회 (미리보기용 - 최대 10건)
     *
     * @param tableName 테이블명
     * @return 조회된 데이터 목록
     */
    public List<Map<String, Object>> selectTableDataPreview(String tableName) {
        return selectTableDataPreview(tableName, 10);
    }

    /**
     * 특정 테이블의 데이터 조회 (미리보기용)
     *
     * @param tableName 테이블명
     * @param limit     조회 건수
     * @return 조회된 데이터 목록
     */
    public List<Map<String, Object>> selectTableDataPreview(String tableName, int limit) {
        List<Map<String, Object>> list = new ArrayList<>();

        // WTL_CMETA_P 테이블 전용 쿼리
        String sql = "SELECT " +
                "      a.OBJECTID     AS gid" +
                "    , a.FTC          AS ftc" +
                "    , a.SJNO         AS sjno" +
                "    , a.GUCD         AS gucd" +
                "    , a.DCD          AS dcd" +
                "    , a.SOCD         AS socd" +
                "    , a.DST          AS dst" +
                "    , a.DIP          AS dip" +
                "    , a.MNGT         AS mngt" +
                "    , a.SUJUN_NO     AS sujun_no" +
                "    , a.BYMD         AS bymd" +
                "    , a.IDN          AS idn" +
                "    , a.SGUCD        AS sgucd" +
                "    , a.SDCD         AS sdcd" +
                "    , a.UPDATE_DTE   AS update_dte" +
                "    , a.YMD          AS ymd" +
                "    , a.ADDR         AS addr" +
                "    , a.KEGI_TYPE    AS kegi_type" +
                "    , a.KEGI_GB      AS kegi_gb" +
                "    , a.CYMD         AS cymd" +
                "    , a.CWI          AS cwi" +
                "    , a.KEGI_NO      AS kegi_no" +
                "    , SDE.ST_AsText(a.shape) AS geom " +
                "    , SDE.ST_SRID(a.shape) AS srid " +
                "FROM " + tableName + " a " +
                "WHERE ROWNUM <= ? " +
                "ORDER BY a.OBJECTID DESC";

        try (Connection conn = oracleDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, limit);

            try (ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData md = rs.getMetaData();
                int colCnt = md.getColumnCount();

                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= colCnt; i++) {
                        String columnName = md.getColumnName(i).toLowerCase();
                        Object value = rs.getObject(i);

                        // Oracle 특수 타입을 Java 표준 타입으로 변환
                        if (value != null) {
                            // CLOB → String
                            if (value instanceof oracle.sql.CLOB) {
                                try {
                                    oracle.sql.CLOB clob = (oracle.sql.CLOB) value;
                                    value = clob.stringValue();
                                } catch (Exception e) {
                                    log.warn("CLOB 변환 실패: {}", e.getMessage());
                                    value = null;
                                }
                            }
                            // TIMESTAMP → String
                            else if (value instanceof oracle.sql.TIMESTAMP) {
                                try {
                                    oracle.sql.TIMESTAMP timestamp = (oracle.sql.TIMESTAMP) value;
                                    value = timestamp.stringValue();
                                } catch (Exception e) {
                                    log.warn("TIMESTAMP 변환 실패: {}", e.getMessage());
                                    value = null;
                                }
                            }
                            // DATE → String
                            else if (value instanceof oracle.sql.DATE) {
                                try {
                                    oracle.sql.DATE date = (oracle.sql.DATE) value;
                                    value = date.stringValue();
                                } catch (Exception e) {
                                    log.warn("DATE 변환 실패: {}", e.getMessage());
                                    value = null;
                                }
                            }
                        }

                        row.put(columnName, value);
                    }
                    list.add(row);
                }
            }

            log.info("Oracle에서 {} 테이블 조회 완료: {} 건", tableName, list.size());

        } catch (Exception e) {
            log.error("Oracle 조회 중 오류 발생: {}", e.getMessage(), e);
        }

        return list;
    }

    /**
     * 특정 테이블의 전체 데이터 조회
     *
     * @param tableName 테이블명
     * @return 조회된 데이터 목록
     */
    public List<Map<String, Object>> selectAllTableData(String tableName) {
        List<Map<String, Object>> list = new ArrayList<>();

        String sql = "SELECT " +
                "      a.OBJECTID     AS gid" +
                "    , a.FTC          AS ftc" +
                "    , a.SJNO         AS sjno" +
                "    , a.GUCD         AS gucd" +
                "    , a.DCD          AS dcd" +
                "    , a.SOCD         AS socd" +
                "    , a.DST          AS dst" +
                "    , a.DIP          AS dip" +
                "    , a.MNGT         AS mngt" +
                "    , a.SUJUN_NO     AS sujun_no" +
                "    , a.BYMD         AS bymd" +
                "    , a.IDN          AS idn" +
                "    , a.SGUCD        AS sgucd" +
                "    , a.SDCD         AS sdcd" +
                "    , a.UPDATE_DTE   AS update_dte" +
                "    , a.YMD          AS ymd" +
                "    , a.ADDR         AS addr" +
                "    , a.KEGI_TYPE    AS kegi_type" +
                "    , a.KEGI_GB      AS kegi_gb" +
                "    , a.CYMD         AS cymd" +
                "    , a.CWI          AS cwi" +
                "    , a.KEGI_NO      AS kegi_no" +
                "    , SDE.ST_AsText(a.shape) AS geom " +
                "    , SDE.ST_SRID(a.shape) AS srid " +
                "FROM " + tableName + " a " +
                "ORDER BY a.OBJECTID DESC";

        try (Connection conn = oracleDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            ResultSetMetaData md = rs.getMetaData();
            int colCnt = md.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= colCnt; i++) {
                    String columnName = md.getColumnName(i).toLowerCase();
                    Object value = rs.getObject(i);

                    // Oracle 특수 타입을 Java 표준 타입으로 변환
                    if (value != null) {
                        // CLOB → String
                        if (value instanceof oracle.sql.CLOB) {
                            try {
                                oracle.sql.CLOB clob = (oracle.sql.CLOB) value;
                                value = clob.stringValue();
                            } catch (Exception e) {
                                log.warn("CLOB 변환 실패: {}", e.getMessage());
                                value = null;
                            }
                        }
                        // TIMESTAMP → String
                        else if (value instanceof oracle.sql.TIMESTAMP) {
                            try {
                                oracle.sql.TIMESTAMP timestamp = (oracle.sql.TIMESTAMP) value;
                                value = timestamp.stringValue();
                            } catch (Exception e) {
                                log.warn("TIMESTAMP 변환 실패: {}", e.getMessage());
                                value = null;
                            }
                        }
                        // DATE → String
                        else if (value instanceof oracle.sql.DATE) {
                            try {
                                oracle.sql.DATE date = (oracle.sql.DATE) value;
                                value = date.stringValue();
                            } catch (Exception e) {
                                log.warn("DATE 변환 실패: {}", e.getMessage());
                                value = null;
                            }
                        }
                    }

                    row.put(columnName, value);
                }
                list.add(row);
            }

            log.info("Oracle에서 {} 테이블 전체 조회 완료: {} 건", tableName, list.size());

        } catch (Exception e) {
            log.error("Oracle 전체 조회 중 오류 발생: {}", e.getMessage(), e);
        }

        return list;
    }

    /**
     * 테이블 데이터 건수 조회
     *
     * @param tableName 테이블명
     * @return 데이터 건수
     */
    public int countTableData(String tableName) {
        String sql = "SELECT COUNT(*) FROM " + tableName;

        try (Connection conn = oracleDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);  // 컬럼 인덱스 사용 (문자셋 문제 회피)
            }

        } catch (Exception e) {
            log.error("Oracle 건수 조회 중 오류 발생: {}", e.getMessage(), e);
        }

        return 0;
    }
}