import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.Properties;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/**
 * A utility class to test the behavior of prepared statements
 * in MariaDB Connector/J with different connection parameters.
 *
 * This code requires a MariaDB database and the corresponding
 * Connector/J JAR file in the classpath.
 *
 * To test with a specific connector version (e.g., 2.7.5), ensure
 * only that version's JAR is in the classpath. Repeat for 3.4.1.
 *
 * Configuration is read from a file named config.properties.
 */
public class MariaDBPreparedStmtTest {

    // --- Configuration Variables (Loaded from file) ---
    private static String DB_URL;
    private static String DB_USER;
    private static String DB_PASSWORD;
    private static int NUM_THREADS;
    private static int STATEMENTS_PER_THREAD;
    private static String[] connectorVersions;
    private static int TEST_DURATION_SECONDS;
    private static String[] monitorStatusVariables;

    // --- SQL Queries ---
    private static final String SELECT_SQL = "SELECT ? AS result_value";
    private static final String INSERT_SQL = "INSERT INTO test_table (id, name) VALUES (?, ?)";

    /**
     * The main method to orchestrate the test cases. It iterates through
     * different connector versions and all combinations of the specified
     * connection parameters.
     * @param args Command line arguments (not used)
     */
    public static void main(String[] args) {
        // Load configuration from file.
        try {
            loadConfig();
        } catch (IOException e) {
            System.err.println("Error loading configuration file: " + e.getMessage());
            return;
        }

        // Print all configuration variables as a header.
        printConfigHeader();

        // All possible boolean values for the parameters.
        boolean[] booleanValues = {true, false};

        // Parameters to be tested.
        boolean[] useServerPrepStmts = booleanValues;
        boolean[] cachePrepStmts = booleanValues;
        boolean[] useBulkStmtsForInserts = booleanValues;
        boolean[] rewriteBatchedStatements = booleanValues;
        boolean[] useBulkStmts = booleanValues;

        System.out.println("Starting multi-threaded prepared statement test...");
        System.out.println("-----------------------------------------------------------------");
        
        for (String version : connectorVersions) {
            System.out.println("\n*** Testing with Connector/J version " + version + " ***");
            System.out.println("Please ensure the correct JAR is in your classpath.");

            // Loop through all combinations of the five parameters.
            for (boolean useServer : useServerPrepStmts) {
                for (boolean cachePrep : cachePrepStmts) {
                    for (boolean useBulkInsert : useBulkStmtsForInserts) {
                        for (boolean rewriteBatch : rewriteBatchedStatements) {
                            for (boolean useBulk : useBulkStmts) {
                            
                                // Construct the properties for the connection.
                                Properties props = new Properties();
                                props.setProperty("user", DB_USER);
                                props.setProperty("password", DB_PASSWORD);
                                props.setProperty("useServerPrepStmts", String.valueOf(useServer));
                                props.setProperty("cachePrepStmts", String.valueOf(cachePrep));
                                props.setProperty("useBulkStmtsForInserts", String.valueOf(useBulkInsert));
                                props.setProperty("rewriteBatchedStatements", String.valueOf(rewriteBatch));
                                props.setProperty("useBulkStmts", String.valueOf(useBulk));

                                System.out.printf(
                                    "\nTesting -> useServerPrepStmts=%b, cachePrepStmts=%b, useBulkStmtsForInserts=%b, rewriteBatchedStatements=%b, useBulkStmts=%b\n",
                                    useServer, cachePrep, useBulkInsert, rewriteBatch, useBulk
                                );

                                runMultiThreadedTest(props);
                            }
                        }
                    }
                }
            }
        }
        System.out.println("\n-----------------------------------------------------------------");
        System.out.println("Multi-threaded prepared statement test finished.");
    }

    /**
     * Executes the multi-threaded test for a given set of connection properties.
     * @param props The connection properties for the test.
     */
    private static void runMultiThreadedTest(Properties props) {
        // Create a fixed-size thread pool.
        ExecutorService executor = Executors.newFixedThreadPool(NUM_THREADS);

        // A flag to signal the monitoring thread to stop.
        CountDownLatch latch = new CountDownLatch(1);

        // A separate thread to monitor the prepared_stmt_count.
        Thread monitorThread = new Thread(() -> {
            try {
                System.out.println("  Monitoring status variables...");
                while (latch.getCount() > 0) {
                    // Dynamically build and print the monitoring output.
                    String statusOutput = Arrays.stream(monitorStatusVariables)
                                                .map(var -> var + ": " + getGlobalStatusVariable(var))
                                                .collect(Collectors.joining(", "));
                    System.out.println("  Current " + statusOutput);
                    Thread.sleep(100); // Check every second.
                }
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        });

        // Use a single connection to perform table setup and teardown.
        try (Connection conn = DriverManager.getConnection(DB_URL, props)) {
            try (Statement stmt = conn.createStatement()) {
                System.out.println("  Main thread: Dropping and creating test table...");
                stmt.execute("DROP TABLE IF EXISTS test_table");
                stmt.execute("CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50))");
            }

            monitorThread.start();
            
            // Submit tasks to the thread pool with a unique index for each thread.
            for (int i = 0; i < NUM_THREADS; i++) {
                final int threadIndex = i;
                executor.submit(() -> {
                    try (Connection workerConn = DriverManager.getConnection(DB_URL, props)) {
                        // Test both SELECT and INSERT statements.
                        try (PreparedStatement psSelect = workerConn.prepareStatement(SELECT_SQL);
                             PreparedStatement psInsert = workerConn.prepareStatement(INSERT_SQL)) {
                            
                            // Set auto-commit to false for batching.
                            workerConn.setAutoCommit(false);
                            
                            for (int j = 0; j < STATEMENTS_PER_THREAD; j++) {
                                // Execute a SELECT statement.
                                psSelect.setString(1, "hello from thread " + Thread.currentThread().getId() + " " + j);
                                psSelect.executeQuery();

                                // Add an INSERT statement to the batch with a unique ID.
                                int uniqueId = (threadIndex * STATEMENTS_PER_THREAD) + j;
                                psInsert.setInt(1, uniqueId);
                                psInsert.setString(2, "test_name_" + uniqueId);
                                psInsert.addBatch();
                            }
                            
                            // Execute the batch.
                            psInsert.executeBatch();
                            workerConn.commit();
                            
                            // Reset auto-commit to true.
                            workerConn.setAutoCommit(true);

                            Thread.sleep(500); // Simulate some work.
                        }
                    } catch (SQLException | InterruptedException e) {
                        System.err.println("  Thread " + Thread.currentThread().getId() + " failed: " + e.getMessage());
                    }
                });
            }
            
            executor.shutdown();
            try {
                // Wait for all tasks to complete or a timeout.
                if (!executor.awaitTermination(TEST_DURATION_SECONDS, TimeUnit.SECONDS)) {
                    System.out.println("  Test timed out. Forcing shutdown.");
                    executor.shutdownNow();
                }
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        } catch (SQLException e) {
            System.err.println("  Initial setup failed: " + e.getMessage());
        } finally {
            // Signal the monitoring thread to stop.
            latch.countDown();
            try {
                monitorThread.join();
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
            // Drop the table after all threads have finished.
            try (Connection cleanupConn = DriverManager.getConnection(DB_URL, props);
                 Statement cleanupStmt = cleanupConn.createStatement()) {
                System.out.println("  Main thread: Cleaning up test table...");
                cleanupStmt.execute("DROP TABLE IF EXISTS test_table");
            } catch (SQLException e) {
                System.err.println("  Cleanup failed: " + e.getMessage());
            }
        }
    }

    /**
     * Loads configuration variables from a config.properties file.
     */
    private static void loadConfig() throws IOException {
        Properties prop = new Properties();
        try (FileInputStream fis = new FileInputStream("config.properties")) {
            prop.load(fis);
        }

        DB_URL = prop.getProperty("db.url");
        DB_USER = prop.getProperty("db.user");
        DB_PASSWORD = prop.getProperty("db.password");
        NUM_THREADS = Integer.parseInt(prop.getProperty("test.threads"));
        STATEMENTS_PER_THREAD = Integer.parseInt(prop.getProperty("test.statements.per.thread"));
        connectorVersions = prop.getProperty("connector.versions").split(",");
        TEST_DURATION_SECONDS = Integer.parseInt(prop.getProperty("test.duration.seconds"));
        monitorStatusVariables = Arrays.stream(prop.getProperty("monitor.status.variables").split(","))
                                       .map(String::trim)
                                       .toArray(String[]::new);
    }

    /**
     * Prints all the configuration variables at the beginning of the test.
     */
    private static void printConfigHeader() {
        System.out.println("Configuration Loaded:");
        System.out.println("---------------------------------");
        System.out.println("  Database URL: " + DB_URL);
        System.out.println("  Database User: " + DB_USER);
        System.out.println("  Test Duration: " + TEST_DURATION_SECONDS + " seconds");
        System.out.println("  Threads: " + NUM_THREADS);
        System.out.println("  Statements Per Thread: " + STATEMENTS_PER_THREAD);
        System.out.print("  Connector Versions: ");
        for (String version : connectorVersions) {
            System.out.print(version + " ");
        }
        System.out.println();
        System.out.print("  Monitored Status Variables: ");
        for (String var : monitorStatusVariables) {
            System.out.print(var + " ");
        }
        System.out.println();
        System.out.println("---------------------------------");
    }

    /**
     * Queries the MariaDB server for the value of a global status variable.
     * This method opens a separate, temporary connection to get the count.
     * @param variableName The name of the global status variable to query.
     * @return The current value of the status variable, or -1 if an error occurs.
     */
    private static long getGlobalStatusVariable(String variableName) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW GLOBAL STATUS LIKE '" + variableName + "'")) {
            if (rs.next()) {
                return rs.getLong("Value");
            }
        } catch (SQLException e) {
            System.err.println("  Error fetching status variable '" + variableName + "': " + e.getMessage());
            return -1;
        }
        return -1;
    }
}