package com.erp.service;

import com.erp.common.BaseService;
import com.erp.util.PasswordEncryption;
import jakarta.annotation.PostConstruct;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 다중 커넥션 쿼리 서비스.
 * - connectionId=0 → 메인 DB (DataSource)
 * - connectionId>0 → 외부 DB (external_db_connection 테이블 참조, JDBC 동적 연결)
 */
@Service
@Slf4j
public class MultiConnectionService extends BaseService {

    @Autowired private ExternalDbConnectionService externalDbConnectionService;
    @Autowired private DataSource dataSource;

    @Value("${encryption.secret-key:ilshin-erp-encryption-key-2024}")
    private String encryptionSecretKey;

    private PasswordEncryption passwordEncryption;

    @PostConstruct
    public void init() {
        this.passwordEncryption = new PasswordEncryption(encryptionSecretKey);
    }

    // ══════════════════════════════════════════════════════════════════════
    // 테이블 목록
    // ══════════════════════════════════════════════════════════════════════

    public List<Map<String, Object>> getTablesFromConnection(int connectionId) {
        if (connectionId == 0) {
            return getMainDbTables();
        }
        return externalDbConnectionService.getTables(connectionId);
    }

    public List<Map<String, Object>> getBatchTablesWithColumns(int connectionId) {
        List<Map<String, Object>> tables = getTablesFromConnection(connectionId);
        for (Map<String, Object> table : tables) {
            String tableName = str(table, "table_name");
            if (tableName != null) {
                try {
                    List<Map<String, Object>> cols = getColumnsFromConnection(connectionId, tableName);
                    table.put("column_count", cols.size());
                } catch (Exception e) {
                    log.warn("컬럼 수 조회 실패 (table={}): {}", tableName, e.getMessage());
                    table.put("column_count", 0);
                }
            }
        }
        return tables;
    }

    public List<Map<String, Object>> getColumnsFromConnection(int connectionId, String tableName) {
        if (connectionId == 0) {
            return getMainDbColumns(tableName);
        }
        return externalDbConnectionService.getTableColumns(connectionId, tableName);
    }

    // ══════════════════════════════════════════════════════════════════════
    // 데이터 조회 (SELECT with conditions)
    // ══════════════════════════════════════════════════════════════════════

    public List<Map<String, Object>> fetchDataFromConnection(int connectionId, Map<String, Object> body) {
        String tableName = requireTableName(body);
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> conditions =
                (List<Map<String, Object>>) body.getOrDefault("conditions", Collections.emptyList());

        String sql = "SELECT * FROM " + sanitizeIdentifier(tableName) + buildWhere(conditions);
        List<Object> values = extractValues(conditions);
        return connectionId == 0 ? queryMainDb(sql, values) : queryExtDb(connectionId, sql, values);
    }

    // ══════════════════════════════════════════════════════════════════════
    // 데이터 삽입
    // ══════════════════════════════════════════════════════════════════════

    public Map<String, Object> insertDataToConnection(int connectionId, Map<String, Object> body) {
        String tableName = requireTableName(body);
        @SuppressWarnings("unchecked")
        Map<String, Object> data = (Map<String, Object>) body.get("data");

        List<String> cols = new ArrayList<>(data.keySet());
        String colList = cols.stream().map(this::sanitizeIdentifier).collect(Collectors.joining(", "));
        String placeholders = cols.stream().map(c -> "?").collect(Collectors.joining(", "));
        String sql = "INSERT INTO " + sanitizeIdentifier(tableName)
                + " (" + colList + ") VALUES (" + placeholders + ")";
        List<Object> values = cols.stream().map(data::get).collect(Collectors.toList());

        int rows = connectionId == 0 ? dmlMainDb(sql, values) : dmlExtDb(connectionId, sql, values);

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("rows_affected", rows);
        result.put("table_name", tableName);
        result.put("data", data);
        return result;
    }

    // ══════════════════════════════════════════════════════════════════════
    // 데이터 수정
    // ══════════════════════════════════════════════════════════════════════

    public List<Map<String, Object>> updateDataToConnection(int connectionId, Map<String, Object> body) {
        String tableName = requireTableName(body);
        @SuppressWarnings("unchecked")
        Map<String, Object> data = (Map<String, Object>) body.get("data");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> conditions =
                (List<Map<String, Object>>) body.getOrDefault("conditions", Collections.emptyList());

        List<String> setCols = new ArrayList<>(data.keySet());
        String setClause = setCols.stream()
                .map(c -> sanitizeIdentifier(c) + " = ?")
                .collect(Collectors.joining(", "));
        String sql = "UPDATE " + sanitizeIdentifier(tableName)
                + " SET " + setClause + buildWhere(conditions);

        List<Object> values = new ArrayList<>(setCols.stream().map(data::get).collect(Collectors.toList()));
        values.addAll(extractValues(conditions));

        int rows = connectionId == 0 ? dmlMainDb(sql, values) : dmlExtDb(connectionId, sql, values);

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("rows_affected", rows);
        result.put("table_name", tableName);
        return List.of(result);
    }

    // ══════════════════════════════════════════════════════════════════════
    // 데이터 삭제
    // ══════════════════════════════════════════════════════════════════════

    public List<Map<String, Object>> deleteDataFromConnection(int connectionId, Map<String, Object> body) {
        String tableName = requireTableName(body);
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> conditions =
                (List<Map<String, Object>>) body.getOrDefault("conditions", Collections.emptyList());
        int maxDeleteCount = toInt(body.getOrDefault("max_delete_count", 100));

        List<Object> values = extractValues(conditions);
        String countSql = "SELECT COUNT(*) FROM " + sanitizeIdentifier(tableName) + buildWhere(conditions);
        int count = connectionId == 0
                ? countMainDb(countSql, values)
                : countExtDb(connectionId, countSql, values);

        if (count > maxDeleteCount) {
            throw new IllegalArgumentException(
                    "삭제 대상 건수(" + count + ")가 최대 허용 건수(" + maxDeleteCount + ")를 초과합니다.");
        }

        String sql = "DELETE FROM " + sanitizeIdentifier(tableName) + buildWhere(conditions);
        int rows = connectionId == 0 ? dmlMainDb(sql, values) : dmlExtDb(connectionId, sql, values);

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("rows_affected", rows);
        result.put("table_name", tableName);
        return List.of(result);
    }

    // ══════════════════════════════════════════════════════════════════════
    // 자기 자신 테이블 작업 검증
    // ══════════════════════════════════════════════════════════════════════

    public Map<String, Object> validateSelfOperation(Map<String, Object> body) {
        String tableName = requireTableName(body);
        String operation = body.get("operation").toString();
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> conditions =
                (List<Map<String, Object>>) body.getOrDefault("conditions", Collections.emptyList());

        List<Object> values = extractValues(conditions);
        String countSql = "SELECT COUNT(*) FROM " + sanitizeIdentifier(tableName) + buildWhere(conditions);
        int affectedCount = countMainDb(countSql, values);

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("valid", true);
        result.put("affected_count", affectedCount);
        result.put("table_name", tableName);
        result.put("operation", operation);
        result.put("message", "검증이 완료되었습니다. 영향받는 행: " + affectedCount + "건");
        return result;
    }

    // ══════════════════════════════════════════════════════════════════════
    // 메인 DB JDBC 유틸
    // ══════════════════════════════════════════════════════════════════════

    private List<Map<String, Object>> getMainDbTables() {
        String sql = "SELECT table_name FROM information_schema.tables " +
                "WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name";
        return queryMainDb(sql, Collections.emptyList());
    }

    private List<Map<String, Object>> getMainDbColumns(String tableName) {
        String sql = "SELECT column_name, data_type, is_nullable, column_default, " +
                "character_maximum_length FROM information_schema.columns " +
                "WHERE table_schema = 'public' AND table_name = ? ORDER BY ordinal_position";
        return queryMainDb(sql, List.of(tableName));
    }

    private List<Map<String, Object>> queryMainDb(String sql, List<Object> values) {
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            bindValues(ps, values);
            try (ResultSet rs = ps.executeQuery()) {
                return toList(rs);
            }
        } catch (SQLException e) {
            log.error("메인 DB 쿼리 실패: {}", e.getMessage());
            throw new RuntimeException("메인 DB 쿼리 중 오류가 발생했습니다: " + e.getMessage(), e);
        }
    }

    private int dmlMainDb(String sql, List<Object> values) {
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            bindValues(ps, values);
            return ps.executeUpdate();
        } catch (SQLException e) {
            log.error("메인 DB DML 실패: {}", e.getMessage());
            throw new RuntimeException("메인 DB DML 중 오류가 발생했습니다: " + e.getMessage(), e);
        }
    }

    private int countMainDb(String sql, List<Object> values) {
        List<Map<String, Object>> rows = queryMainDb(sql, values);
        if (rows.isEmpty()) return 0;
        Object v = rows.get(0).values().iterator().next();
        return v instanceof Number ? ((Number) v).intValue() : 0;
    }

    // ══════════════════════════════════════════════════════════════════════
    // 외부 DB JDBC 유틸
    // ══════════════════════════════════════════════════════════════════════

    private List<Map<String, Object>> queryExtDb(int connectionId, String sql, List<Object> values) {
        Map<String, Object> connInfo = getExtConnWithPassword(connectionId);
        try (Connection c = openExtConn(connInfo);
             PreparedStatement ps = c.prepareStatement(sql)) {
            bindValues(ps, values);
            try (ResultSet rs = ps.executeQuery()) {
                return toList(rs);
            }
        } catch (SQLException e) {
            log.error("외부 DB 쿼리 실패: {}", e.getMessage());
            throw new RuntimeException("외부 DB 쿼리 중 오류가 발생했습니다: " + e.getMessage(), e);
        }
    }

    private int dmlExtDb(int connectionId, String sql, List<Object> values) {
        Map<String, Object> connInfo = getExtConnWithPassword(connectionId);
        try (Connection c = openExtConn(connInfo);
             PreparedStatement ps = c.prepareStatement(sql)) {
            bindValues(ps, values);
            return ps.executeUpdate();
        } catch (SQLException e) {
            log.error("외부 DB DML 실패: {}", e.getMessage());
            throw new RuntimeException("외부 DB DML 중 오류가 발생했습니다: " + e.getMessage(), e);
        }
    }

    private int countExtDb(int connectionId, String sql, List<Object> values) {
        List<Map<String, Object>> rows = queryExtDb(connectionId, sql, values);
        if (rows.isEmpty()) return 0;
        Object v = rows.get(0).values().iterator().next();
        return v instanceof Number ? ((Number) v).intValue() : 0;
    }

    private Map<String, Object> getExtConnWithPassword(int connectionId) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", connectionId);
        Map<String, Object> connInfo = sqlSession.selectOne("externalDbConnection.getExternalDbConnectionInfoWithPassword", params);
        if (connInfo == null) {
            throw new NoSuchElementException("연결 정보를 찾을 수 없습니다. (id=" + connectionId + ")");
        }
        String encrypted = str(connInfo, "password");
        if (encrypted != null && !encrypted.equals("***ENCRYPTED***")) {
            try {
                connInfo.put("_decryptedPwd", passwordEncryption.decrypt(encrypted));
            } catch (Exception e) {
                log.warn("비밀번호 복호화 실패 (id={}): {}", connectionId, e.getMessage());
                throw new RuntimeException("비밀번호 복호화에 실패했습니다.");
            }
        }
        return connInfo;
    }

    private Connection openExtConn(Map<String, Object> connInfo) throws SQLException {
        String host = str(connInfo, "host");
        int port = toIntMap(connInfo, "port", 5432);
        String database = str(connInfo, "database_name");
        String url = String.format("jdbc:postgresql://%s:%d/%s", host, port, database);

        Properties props = new Properties();
        props.setProperty("user", str(connInfo, "username"));
        String pwd = str(connInfo, "_decryptedPwd");
        if (pwd == null) pwd = str(connInfo, "password");
        props.setProperty("password", pwd != null ? pwd : "");
        props.setProperty("connect_timeout", "30");
        props.setProperty("socket_timeout", "30");
        return DriverManager.getConnection(url, props);
    }

    // ══════════════════════════════════════════════════════════════════════
    // SQL 빌더
    // ══════════════════════════════════════════════════════════════════════

    private String buildWhere(List<Map<String, Object>> conditions) {
        if (conditions == null || conditions.isEmpty()) return "";
        List<String> clauses = conditions.stream()
                .map(c -> sanitizeIdentifier((String) c.get("column"))
                        + " " + sanitizeOperator((String) c.getOrDefault("operator", "="))
                        + " ?")
                .collect(Collectors.toList());
        return " WHERE " + String.join(" AND ", clauses);
    }

    private List<Object> extractValues(List<Map<String, Object>> conditions) {
        if (conditions == null) return Collections.emptyList();
        return conditions.stream().map(c -> c.get("value")).collect(Collectors.toList());
    }

    private String sanitizeIdentifier(String name) {
        if (name == null || name.isBlank()) {
            throw new IllegalArgumentException("식별자가 null이거나 비어 있습니다.");
        }
        if (!name.matches("[a-zA-Z0-9_.]+")) {
            throw new IllegalArgumentException("유효하지 않은 식별자: " + name);
        }
        return name;
    }

    private String sanitizeOperator(String op) {
        if (op == null) return "=";
        Set<String> allowed = Set.of("=", "!=", "<>", "<", ">", "<=", ">=",
                "LIKE", "ILIKE", "IN", "NOT IN", "IS NULL", "IS NOT NULL");
        if (!allowed.contains(op.toUpperCase())) {
            throw new IllegalArgumentException("허용되지 않는 연산자: " + op);
        }
        return op;
    }

    // ══════════════════════════════════════════════════════════════════════
    // JDBC 유틸
    // ══════════════════════════════════════════════════════════════════════

    private void bindValues(PreparedStatement ps, List<Object> values) throws SQLException {
        for (int i = 0; i < values.size(); i++) {
            ps.setObject(i + 1, values.get(i));
        }
    }

    private List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int colCount = meta.getColumnCount();
        List<Map<String, Object>> rows = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> row = new LinkedHashMap<>();
            for (int i = 1; i <= colCount; i++) {
                row.put(meta.getColumnLabel(i), rs.getObject(i));
            }
            rows.add(row);
        }
        return rows;
    }

    // ══════════════════════════════════════════════════════════════════════
    // 일반 유틸
    // ══════════════════════════════════════════════════════════════════════

    private String requireTableName(Map<String, Object> body) {
        Object t = body.get("table_name");
        if (t == null || t.toString().isBlank()) {
            throw new IllegalArgumentException("테이블명이 입력되지 않았습니다.");
        }
        return t.toString();
    }

    private String str(Map<String, Object> m, String key) {
        Object v = m.get(key);
        return v != null ? v.toString() : null;
    }

    private int toInt(Object v) {
        if (v == null) return 0;
        try { return Integer.parseInt(v.toString()); } catch (Exception e) { return 0; }
    }

    private int toIntMap(Map<String, Object> m, String key, int def) {
        Object v = m.get(key);
        if (v == null) return def;
        try { return Integer.parseInt(v.toString()); } catch (Exception e) { return def; }
    }
}
