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

/**
 * 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;

    // --- 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 (?, ?)";

    // --- Test Configuration ---
    // The duration of the test in seconds.
    private static final int TEST_DURATION_SECONDS = 30;

    /**
     * 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;
        }

        // 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;

        System.out.println("Starting multi-threaded prepared statement test...");
        System.out.println("-----------------------------------------------------------------");
        System.out.println("  NUM_THREADS: " + NUM_THREADS);
        System.out.println("  STATEMENTS_PER_THREAD: " + STATEMENTS_PER_THREAD);
        
        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 four parameters.
            for (boolean useServer : useServerPrepStmts) {
                for (boolean cachePrep : cachePrepStmts) {
                    for (boolean useBulk : useBulkStmtsForInserts) {
                        for (boolean rewriteBatch : rewriteBatchedStatements) {
                            
                            // 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(useBulk));
                            props.setProperty("rewriteBatchedStatements", String.valueOf(rewriteBatch));

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

                            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 prepared_stmt_count, Com_stmt_prepare, and Handler_prepare...");
                while (latch.getCount() > 0) {
                    long preparedStmtCount = getPreparedStmtCount();
                    long comStmtPrepare = getComStmtPrepareCount();
                    long handlerPrepare = getHandlerPrepareCount();
                    System.out.println("  Current prepared_stmt_count: " + preparedStmtCount + 
                                       ", Com_stmt_prepare: " + comStmtPrepare + 
                                       ", Handler_prepare: " + handlerPrepare);
                    Thread.sleep(1000); // 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.
            for (int i = 0; i < NUM_THREADS; i++) {
                executor.submit(() -> {
                    try (Connection workerConn = DriverManager.getConnection(DB_URL, props)) {
                        try (PreparedStatement ps = workerConn.prepareStatement(SELECT_SQL)) {
                            for (int j = 0; j < STATEMENTS_PER_THREAD; j++) {
                                ps.setString(1, "hello from thread " + Thread.currentThread().getId() + " " + j);
                                ps.executeQuery();
                                Thread.sleep(50); // 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(",");
    }

    /**
     * Queries the MariaDB server for the current global Prepared_stmt_count status.
     * This method opens a separate, temporary connection to get the count.
     * @return The current global Prepared_stmt_count as a long, or -1 if an error occurs.
     */
    private static long getPreparedStmtCount() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW GLOBAL STATUS LIKE 'prepared_stmt_count'")) {
            if (rs.next()) {
                return rs.getLong("Value");
            }
        } catch (SQLException e) {
            // Error fetching the count is expected if the server is under heavy load.
            return -1;
        }
        return -1;
    }
    
    /**
     * Queries the MariaDB server for the global Com_stmt_prepare status.
     * @return The global Com_stmt_prepare count as a long, or -1 if an error occurs.
     */
    private static long getComStmtPrepareCount() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW GLOBAL STATUS LIKE 'Com_stmt_prepare'")) {
            if (rs.next()) {
                return rs.getLong("Value");
            }
        } catch (SQLException e) {
            return -1;
        }
        return -1;
    }

    /**
     * Queries the MariaDB server for the global Handler_prepare status.
     * @return The global Handler_prepare count as a long, or -1 if an error occurs.
     */
    private static long getHandlerPrepareCount() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW GLOBAL STATUS LIKE 'Handler_prepare'")) {
            if (rs.next()) {
                return rs.getLong("Value");
            }
        } catch (SQLException e) {
            return -1;
        }
        return -1;
    }
}