package study.batchquartz;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.sql.DataSource;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.jdbc.support.JdbcTransactionManager;

/// U46 每个测试方法的独有数据库：建库 → 官方 schema → 测试 → 删除。
/// 生产库与并行会话的库（study_u46_* 之外）不被触碰。
final class LabDatabase implements AutoCloseable {

    static final String HOST = System.getenv().getOrDefault("LAB_DB_HOST", "127.0.0.1");
    static final int PORT = Integer.parseInt(System.getenv().getOrDefault("LAB_DB_PORT", "3306"));
    static final String USER = System.getenv().getOrDefault("LAB_DB_USER", "root");
    static final String PASSWORD = System.getenv().getOrDefault("LAB_DB_PASSWORD", "");
    static final String NAME_PREFIX = "study_u46_";

    static final String ADMIN_URL = "jdbc:mysql://" + HOST + ":" + PORT
            + "/?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=utf8";

    static String urlFor(String name) {
        return "jdbc:mysql://" + HOST + ":" + PORT + "/" + name
                + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=utf8";
    }

    /// 每个实例一个独有库名：同一 JVM 内多个测试互不覆盖，结束时各自删除。
    final String name = NAME_PREFIX + Long.toString(System.currentTimeMillis(), 36)
            + "_" + UUID.randomUUID().toString().substring(0, 8);
    final String url = urlFor(this.name);
    final HikariDataSource dataSource;
    final JdbcTransactionManager transactionManager;

    LabDatabase(String... schemas) throws SQLException {
        try (Connection connection = DriverManager.getConnection(ADMIN_URL, USER, PASSWORD);
                Statement statement = connection.createStatement()) {
            statement.execute("CREATE DATABASE `" + this.name
                    + "` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci");
        }
        this.dataSource = new HikariDataSource();
        this.dataSource.setJdbcUrl(this.url);
        this.dataSource.setUsername(USER);
        this.dataSource.setPassword(PASSWORD);
        this.dataSource.setMaximumPoolSize(6);
        this.transactionManager = new JdbcTransactionManager(this.dataSource);
        for (String schema : schemas) {
            ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
            populator.addScript(new ClassPathResource(schema));
            // Quartz 官方脚本用 # 注释；Spring 默认只认 --，必须显式设置，否则整段脚本被当 SQL。
            populator.setCommentPrefixes("#", "--");
            populator.execute(this.dataSource);
        }
    }

    DataSource dataSource() {
        return this.dataSource;
    }

    JdbcTransactionManager transactionManager() {
        return this.transactionManager;
    }

    long count(String sql, Object... args) throws SQLException {
        try (Connection connection = this.dataSource.getConnection();
                PreparedStatement statement = connection.prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            try (ResultSet resultSet = statement.executeQuery()) {
                resultSet.next();
                return resultSet.getLong(1);
            }
        }
    }

    List<Map<String, Object>> rows(String sql, Object... args) throws SQLException {
        try (Connection connection = this.dataSource.getConnection();
                PreparedStatement statement = connection.prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            try (ResultSet resultSet = statement.executeQuery()) {
                List<Map<String, Object>> rows = new ArrayList<>();
                int columns = resultSet.getMetaData().getColumnCount();
                while (resultSet.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 1; i <= columns; i++) {
                        row.put(resultSet.getMetaData().getColumnLabel(i), resultSet.getObject(i));
                    }
                    rows.add(row);
                }
                return rows;
            }
        }
    }

    void execute(String sql) throws SQLException {
        try (Connection connection = this.dataSource.getConnection();
                Statement statement = connection.createStatement()) {
            statement.execute(sql);
        }
    }

    @Override
    public void close() {
        this.dataSource.close();
        try (Connection connection = DriverManager.getConnection(ADMIN_URL, USER, PASSWORD);
                Statement statement = connection.createStatement()) {
            statement.execute("DROP DATABASE IF EXISTS `" + this.name + "`");
        }
        catch (SQLException e) {
            throw new IllegalStateException("U46 清理失败：库 " + this.name + " 未删除", e);
        }
    }
}