package com.erp.service;

import com.erp.common.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 데이터플로우 실행 서비스
 *
 * Node.js executeDataAction 로직을 Spring Boot 로 포팅.
 * - Main DB : JdbcTemplate 동적 SQL (테이블/컬럼이 런타임 변수이므로 MyBatis XML 사용 불가)
 * - External DB : 외부 JDBC 연결 + INSERT only (UPDATE/DELETE 미구현 — Node 동일)
 */
@Service
@Slf4j
public class DataflowExecutionService extends BaseService {

    @Autowired
    private TableManagementService tableManagementService;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${app.db-password-secret:default-fallback-key-change-in-production}")
    private String dbPasswordSecret;

    // ══════════════════════════════════════════════════════════════
    // Public API
    // ══════════════════════════════════════════════════════════════

    /**
     * 데이터 액션 실행 (진입점)
     * params: { tableName, data, actionType, connection, companyCode }
     */
    public Map<String, Object> executeDataAction(Map<String, Object> params) {
        String tableName  = (String) params.get("table_name");
        @SuppressWarnings("unchecked")
        Map<String, Object> data       = (Map<String, Object>) params.get("data");
        String actionType = (String) params.get("action_type");
        @SuppressWarnings("unchecked")
        Map<String, Object> connection = (Map<String, Object>) params.get("connection");
        String companyCode = (String) params.get("company_code");

        if (tableName == null || tableName.isBlank()) throw new IllegalArgumentException("tableName 은 필수입니다.");
        if (data == null)                               throw new IllegalArgumentException("data 는 필수입니다.");
        if (actionType == null || actionType.isBlank()) throw new IllegalArgumentException("actionType 은 필수입니다.");

        log.info("데이터 액션 실행 시작: {} on {}", actionType, tableName);

        Map<String, Object> result;
        if (connection != null && !isZeroId(connection.get("id"))) {
            result = executeExternalDatabaseAction(tableName, data, actionType, connection);
        } else {
            result = executeMainDatabaseAction(tableName, data, actionType, companyCode);
        }

        log.info("데이터 액션 실행 완료: {} on {}", actionType, tableName);
        return result;
    }

    // ══════════════════════════════════════════════════════════════
    // Main DB
    // ══════════════════════════════════════════════════════════════

    private Map<String, Object> executeMainDatabaseAction(
            String tableName, Map<String, Object> data, String actionType, String companyCode) {

        // company_code 자동 주입
        Map<String, Object> enriched = new LinkedHashMap<>(data);
        if (companyCode != null && !companyCode.isBlank()) {
            enriched.put("company_code", companyCode);
        }

        String action = actionType.toLowerCase(Locale.ROOT);

        // UNIQUE 제약 검증 (INSERT/UPDATE/UPSERT)
        if (List.of("insert", "update", "upsert").contains(action)) {
            List<String> violations = tableManagementService.validateUniqueConstraints(
                    tableName, enriched, companyCode, null);
            if (!violations.isEmpty()) {
                throw new IllegalStateException("중복된 값이 존재합니다: " + String.join(", ", violations));
            }
        }

        return switch (action) {
            case "insert" -> doInsert(tableName, enriched);
            case "update" -> doUpdate(tableName, enriched);
            case "upsert" -> doUpsert(tableName, enriched, companyCode);
            case "delete" -> doDelete(tableName, enriched);
            default       -> throw new IllegalArgumentException("지원하지 않는 액션 타입: " + actionType);
        };
    }

    // ─── INSERT ──────────────────────────────────────────────────

    @Transactional
    public Map<String, Object> doInsert(String tableName, Map<String, Object> data) {
        String safeTable = sanitize(tableName);
        Map<String, String> colTypes = getColumnTypes(safeTable);

        // 존재하는 컬럼만 필터링
        List<String> cols = data.keySet().stream()
                .filter(colTypes::containsKey)
                .collect(Collectors.toList());

        if (cols.isEmpty()) throw new IllegalArgumentException("저장할 유효한 컬럼이 없습니다. 테이블: " + safeTable);

        String colNames     = cols.stream().map(c -> "\"" + sanitize(c) + "\"").collect(Collectors.joining(", "));
        String placeholders = cols.stream().map(c -> "?").collect(Collectors.joining(", "));
        Object[] values     = cols.stream().map(data::get).toArray();

        String sql = String.format("INSERT INTO \"%s\" (%s) VALUES (%s) RETURNING *", safeTable, colNames, placeholders);

        log.debug("INSERT SQL: {}", sql);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, values);

        return buildResult("insert", tableName, result);
    }

    // ─── UPDATE ──────────────────────────────────────────────────

    @Transactional
    public Map<String, Object> doUpdate(String tableName, Map<String, Object> data) {
        String safeTable = sanitize(tableName);

        // 기본키 조회
        String pkColumn = resolvePrimaryKey(safeTable);
        Object pkValue  = data.get(pkColumn);
        if (pkValue == null && pkValue != Integer.valueOf(0)) {
            throw new IllegalStateException("UPDATE를 위한 기본키 값이 필요합니다 (" + pkColumn + ")");
        }

        // 기본키 제외한 업데이트 데이터
        Map<String, String> colTypes = getColumnTypes(safeTable);
        List<String> setCols = data.keySet().stream()
                .filter(k -> !k.equals(pkColumn))
                .filter(colTypes::containsKey)
                .collect(Collectors.toList());

        if (setCols.isEmpty()) throw new IllegalArgumentException("수정할 컬럼이 없습니다.");

        String setClause = setCols.stream()
                .map(c -> "\"" + sanitize(c) + "\" = ?")
                .collect(Collectors.joining(", "));
        List<Object> params = new ArrayList<>(setCols.stream().map(data::get).collect(Collectors.toList()));
        params.add(pkValue);

        String sql = String.format("UPDATE \"%s\" SET %s WHERE \"%s\" = ? RETURNING *",
                safeTable, setClause, sanitize(pkColumn));

        log.debug("UPDATE SQL: {}", sql);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, params.toArray());

        return buildResult("update", tableName, result);
    }

    // ─── UPSERT ──────────────────────────────────────────────────

    @Transactional
    public Map<String, Object> doUpsert(String tableName, Map<String, Object> data, String companyCode) {
        try {
            return doInsert(tableName, data);
        } catch (Exception insertEx) {
            log.info("UPSERT: INSERT 실패, UPDATE 시도. 원인: {}", insertEx.getMessage());
            return doUpdate(tableName, data);
        }
    }

    // ─── DELETE ──────────────────────────────────────────────────

    @Transactional
    public Map<String, Object> doDelete(String tableName, Map<String, Object> data) {
        String safeTable = sanitize(tableName);
        Object id = data.get("id");
        if (id == null) throw new IllegalArgumentException("DELETE를 위한 id 가 필요합니다.");

        String sql = String.format("DELETE FROM \"%s\" WHERE id = ? RETURNING *", safeTable);

        log.debug("DELETE SQL: {}", sql);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, id);

        return buildResult("delete", tableName, result);
    }

    // ══════════════════════════════════════════════════════════════
    // External DB
    // ══════════════════════════════════════════════════════════════

    private Map<String, Object> executeExternalDatabaseAction(
            String tableName, Map<String, Object> data, String actionType, Map<String, Object> connection) {

        Object connId = connection.get("id");
        log.info("외부 DB 액션 실행: connection.id={}, table={}, action={}", connId, tableName, actionType);

        String action = actionType.toLowerCase(Locale.ROOT);

        // Node 코드와 동일: UPDATE/DELETE 는 미구현
        if ("update".equals(action)) throw new UnsupportedOperationException(
                "UPDATE 액션은 아직 지원되지 않습니다. 조건 설정이 필요합니다.");
        if ("delete".equals(action)) throw new UnsupportedOperationException(
                "DELETE 액션은 아직 지원되지 않습니다. 조건 설정이 필요합니다.");

        // 연결 정보 조회
        Map<String, Object> connInfo = sqlSession.selectOne("dataflowExecution.selectExternalConnection", Map.of("connection_id", connId));
        if (connInfo == null) throw new IllegalArgumentException("커넥션을 찾을 수 없습니다: " + connId);

        String dbType = String.valueOf(connInfo.get("db_type")).toLowerCase(Locale.ROOT);
        String host   = String.valueOf(connInfo.get("host"));
        int    port   = toInt(connInfo.get("port"), 5432);
        String dbName = String.valueOf(connInfo.get("database_name"));
        String user   = String.valueOf(connInfo.get("username"));
        String rawPwd = String.valueOf(connInfo.get("password"));
        String password = decryptPassword(rawPwd);

        String jdbcUrl = buildJdbcUrl(dbType, host, port, dbName);
        log.info("외부 DB 연결: {}", jdbcUrl.replaceAll("password=.*", "password=***"));

        try (Connection conn = DriverManager.getConnection(jdbcUrl, user, password)) {
            List<Map<String, Object>> result = switch (action) {
                case "insert" -> externalInsert(conn, tableName, data);
                default       -> throw new IllegalArgumentException("지원하지 않는 액션 타입: " + actionType);
            };

            Map<String, Object> out = buildResult(action, tableName, result);
            out.put("connection", connInfo.get("connection_name"));
            return out;
        } catch (SQLException e) {
            log.error("외부 DB 연결/쿼리 오류: {}", e.getMessage());
            throw new RuntimeException("외부 DB 액션 실행 실패: " + e.getMessage(), e);
        }
    }

    /** 외부 DB에 INSERT 실행 (RETURNING * 미지원 DB 대응) */
    private List<Map<String, Object>> externalInsert(Connection conn, String tableName, Map<String, Object> data)
            throws SQLException {
        String safeTable = sanitize(tableName);
        List<String> cols = new ArrayList<>(data.keySet());
        cols.removeIf(c -> sanitize(c).isBlank());

        String colNames     = cols.stream().map(c -> "\"" + sanitize(c) + "\"").collect(Collectors.joining(", "));
        String placeholders = cols.stream().map(c -> "?").collect(Collectors.joining(", "));
        String sql = String.format("INSERT INTO \"%s\" (%s) VALUES (%s)", safeTable, colNames, placeholders);

        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            int idx = 1;
            for (String col : cols) {
                ps.setObject(idx++, data.get(col));
            }
            ps.executeUpdate();

            Map<String, Object> row = new LinkedHashMap<>(data);
            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) row.put("id", rs.getObject(1));
            }
            return List.of(row);
        }
    }

    // ══════════════════════════════════════════════════════════════
    // Helpers
    // ══════════════════════════════════════════════════════════════

    private Map<String, Object> buildResult(String action, String tableName, List<Map<String, Object>> rows) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("success",      true);
        result.put("action",       action);
        result.put("table_name",    tableName);
        result.put("data",         rows);
        result.put("affected_rows", rows.size());
        return result;
    }

    private String resolvePrimaryKey(String safeTable) {
        List<String> pks = sqlSession.selectList("dataflowExecution.selectPrimaryKeys", Map.of("table_name", safeTable));
        if (pks == null || pks.isEmpty()) throw new IllegalStateException("테이블 " + safeTable + "의 기본키를 찾을 수 없습니다.");
        return pks.get(0);
    }

    /** 테이블 컬럼 이름 → DB 타입 맵 (PostgreSQL information_schema) */
    private Map<String, String> getColumnTypes(String safeTable) {
        String sql = "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ?";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, safeTable);
        Map<String, String> map = new LinkedHashMap<>();
        for (Map<String, Object> row : rows) {
            map.put(String.valueOf(row.get("column_name")), String.valueOf(row.get("data_type")));
        }
        return map;
    }

    /** AES-256-CBC 복호화 (Node PasswordEncryption.decrypt 호환) — 실패 시 원문 반환 */
    private String decryptPassword(String encrypted) {
        if (encrypted == null || !encrypted.contains(":")) return encrypted;
        try {
            String[] parts = encrypted.split(":", 2);
            byte[] iv   = hexToBytes(parts[0]);
            byte[] cipherBytes = hexToBytes(parts[1]);

            // scrypt(secret, "salt", 32) 대신 SHA-256 기반 키 유도 (호환성 제한 — 동일 SECRET_KEY 환경에서만 정상 동작)
            byte[] keyBytes = deriveKey(dbPasswordSecret);
            SecretKey key = new SecretKeySpec(keyBytes, "AES");

            Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            cipher.init(Cipher.DECRYPT_MODE, key, new IvParameterSpec(iv));
            return new String(cipher.doFinal(cipherBytes), StandardCharsets.UTF_8);
        } catch (Exception e) {
            log.warn("외부 DB 비밀번호 복호화 실패, 원문 사용 시도: {}", e.getMessage());
            return encrypted;
        }
    }

    /** Node scrypt(password, "salt", 32) 근사 구현 (SHA-256 기반, 32바이트 키 생성) */
    private byte[] deriveKey(String secret) throws Exception {
        java.security.MessageDigest md = java.security.MessageDigest.getInstance("SHA-256");
        return md.digest((secret + "salt").getBytes(StandardCharsets.UTF_8));
    }

    private byte[] hexToBytes(String hex) {
        int len = hex.length();
        byte[] data = new byte[len / 2];
        for (int i = 0; i < len; i += 2) {
            data[i / 2] = (byte) ((Character.digit(hex.charAt(i), 16) << 4)
                    + Character.digit(hex.charAt(i + 1), 16));
        }
        return data;
    }

    private String buildJdbcUrl(String dbType, String host, int port, String dbName) {
        return switch (dbType) {
            case "mysql"    -> String.format("jdbc:mysql://%s:%d/%s", host, port, dbName);
            case "oracle"   -> String.format("jdbc:oracle:thin:@%s:%d:%s", host, port, dbName);
            case "mssql", "sqlserver" -> String.format("jdbc:sqlserver://%s:%d;databaseName=%s", host, port, dbName);
            default         -> String.format("jdbc:postgresql://%s:%d/%s", host, port, dbName); // postgresql default
        };
    }

    private boolean isZeroId(Object id) {
        if (id == null) return true;
        if (id instanceof Number) return ((Number) id).intValue() == 0;
        return "0".equals(id.toString());
    }

    private int toInt(Object val, int def) {
        if (val instanceof Number) return ((Number) val).intValue();
        try { return Integer.parseInt(String.valueOf(val)); } catch (Exception e) { return def; }
    }

    /** SQL injection 방지 (TableManagementService.sanitize 와 동일) */
    private String sanitize(String name) {
        if (name == null) return "";
        return name.replaceAll("[^a-zA-Z0-9_]", "");
    }
}
