package com.erp.provisioning;

import com.erp.tenant.TenantDbSettings;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.regex.Pattern;

/**
 * CREATE DATABASE / DROP DATABASE 를 superuser 로 실행.
 *
 * ★ 보안 강화 (2026-04-24 Codex 리뷰 반영):
 *   - DB 이름은 ^[a-z][a-z0-9_]{{2,40}}$ 화이트리스트 재검증 (Controller 와 2중 방어).
 *   - pg_terminate_backend 는 PreparedStatement 로 파라미터 바인딩.
 *   - CREATE/DROP DATABASE 는 PG 문법상 파라미터 바인딩 불가 → 검증된 이름만 큰따옴표로 quote.
 *
 * ★ 복구 강화:
 *   - dropDatabase 는 3회 재시도 + 기하급수 백오프 (300ms, 600ms, 1.2s).
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class DatabaseCreator {

    private static final Pattern SAFE_DB_NAME = Pattern.compile("^[a-z][a-z0-9_]{2,40}$");

    private final TenantDbSettings settings;

    public void createDatabase(String dbName) throws SQLException {
        requireSafeDbName(dbName);
        try (Connection c = adminConnection()) {
            c.setAutoCommit(true);
            try (Statement s = c.createStatement()) {
                s.execute("CREATE DATABASE \"" + dbName + "\" WITH TEMPLATE template0 ENCODING 'UTF8'");
            }
            log.info("[Provisioning] CREATE DATABASE: {}", dbName);
        }
    }

    /** 보상 롤백. 재시도 3회 + 백오프. 실패 기록은 남기지만 호출자에게 예외 던지지 않음 (이미 실패 처리 중이므로). */
    public void dropDatabase(String dbName) {
        try {
            requireSafeDbName(dbName);
        } catch (IllegalArgumentException e) {
            log.error("[Provisioning] drop skipped — unsafe name: {}", dbName);
            return;
        }

        long backoffMs = 300;
        for (int attempt = 1; attempt <= 3; attempt++) {
            try (Connection c = adminConnection()) {
                c.setAutoCommit(true);
                try (PreparedStatement p = c.prepareStatement(
                        "SELECT pg_terminate_backend(pid) FROM pg_stat_activity " +
                                "WHERE datname = ? AND pid <> pg_backend_pid()")) {
                    p.setString(1, dbName);
                    p.execute();
                }
                try (Statement s = c.createStatement()) {
                    s.execute("DROP DATABASE IF EXISTS \"" + dbName + "\"");
                }
                log.warn("[Provisioning] ROLLBACK — dropped DB: {} (attempt {})", dbName, attempt);
                return;
            } catch (SQLException e) {
                log.warn("[Provisioning] drop attempt {}/3 failed for {}: {}", attempt, dbName, e.getMessage());
                if (attempt < 3) {
                    try { Thread.sleep(backoffMs); } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        return;
                    }
                    backoffMs *= 2;
                }
            }
        }
        log.error("[Provisioning] drop FAILED after 3 attempts: {} — manual cleanup required", dbName);
    }

    private Connection adminConnection() throws SQLException {
        String url = String.format("jdbc:postgresql://%s:%d/postgres", settings.host(), settings.port());
        return DriverManager.getConnection(url, settings.username(), settings.password());
    }

    private static void requireSafeDbName(String name) {
        if (name == null || !SAFE_DB_NAME.matcher(name).matches()) {
            throw new IllegalArgumentException("unsafe db name: " + name);
        }
    }
}
