package test;

import java.sql.*;
import java.util.HashSet;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class App implements Runnable {

    private final String DB_URL  = "jdbc:mysql://localhost:3306/test?user=root&password=dev";

    private final ExecutorService executorService = Executors.newFixedThreadPool(2);

    public static void main(String[] args) {
        new App().run();
    }

    protected Connection createConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    public void run() {
        Connection connection = null;
        try {
            connection = createConnection();

            System.out.println("--------------------------------------");
            System.out.println("connection.getAutoCommit() = " + connection.getAutoCommit());
            System.out.println("connection.getClientInfo() = " + connection.getClientInfo());
            System.out.println("connection.getMetaData() = " + connection.getMetaData());
            System.out.println("connection.getCatalog() = " + connection.getCatalog());
            System.out.println("connection.getTransactionIsolation() = " + connection.getTransactionIsolation());
            System.out.println("--------------------------------------");

            Integer[] isolationTypes = checkTransactionIsolation(connection);
            checkSavePoints(connection);

            for (Integer transactionType: isolationTypes) {
                transactionTest(transactionType);
            }
            executorService.shutdown();
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                System.err.println(e);
            }
        }
    }

    private void checkSavePoints(Connection connection) {
        try {
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            Savepoint savepoint = connection.setSavepoint();
            try {
                statement.execute("insert into users(id, firstName, lastName) values(0, 'maxim', 'borkunov')");
                statement.execute("insert into users(id, firstName, lastName) values(0, 'alexey', 'voroshin')");
            } catch (SQLException e) { // duplicate id exception
                System.err.println(e);
                connection.rollback(savepoint);
                connection.releaseSavepoint(savepoint);
            } finally {
                connection.commit();
            }
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    private Integer[] checkTransactionIsolation(Connection connection) {
        Set<Integer> available = new HashSet<Integer>();
        try {
            int defaultTransactionIsolation = connection.getTransactionIsolation();
            for (int i : new int[] {0, 1, 2, 4, 8}) {
                try {
                    connection.setTransactionIsolation(i);
                    available.add(i);
                } catch (SQLException e) {
                    System.out.println(e);
                }
            }
            System.out.println("available types = " + available);
            connection.setTransactionIsolation(defaultTransactionIsolation);
            return available.toArray(new Integer[] {available.size()});
        } catch (SQLException e) {
            System.err.println(e);
        }
        return new Integer[0];
    }


    private void transactionTest(final int transactionIsolation) {
        System.out.println("transactionIsolation = " + transactionIsolation);

        int clients = 2;
        final boolean[] finished = new boolean[clients];

        for (int i = 0; i < 2; i++) {
            executorService.execute(new Runnable() {
                private int number;
                Runnable setClientNumber(int number) {
                    this.number = number;
                    return this;
                }
                public void run() {
                    Connection connection = null;
                    try {
                        connection = createConnection();
                        connection.setTransactionIsolation(transactionIsolation);
                        connection.setAutoCommit(false);

                        Statement statement = connection.createStatement();
                        showUsers(statement);
                        System.out.println("#" + number + " ----- ");
                        statement.execute("insert into users(firstName, lastName) values('hello', 'bye')");
                        try {
                            Thread.sleep(Math.round(Math.random() * 1000));
                        } catch (InterruptedException e) {
                            System.err.println(e);
                        }
                        showUsers(statement);
                        statement.execute("update users set firstName = '" + this.number + "'");
                        connection.commit();
                    } catch (SQLException e) {
                        System.err.println(transactionIsolation);
                        e.printStackTrace();
                    } finally {
                        finished[number] = true;
                        try {
                            if (connection != null)
                                connection.close();
                        } catch (SQLException e) {
                            System.err.println(e);
                        }
                    }
                }

                private void showUsers(Statement statement) throws SQLException {
                    if (statement.execute("select * from users")) {
                        ResultSet result = statement.getResultSet();
                        while (result.next()) {
                            int id = result.getInt("id");
                            String firstName = result.getString("firstName");
                            System.out.println("Client #" + this.number + " id:" + id + ", firstName: " + firstName);
                        }
                    }
                }
            }.setClientNumber(i));
        }

        boolean exit;

        do  {
            exit = true;
            for (int i = 0; i < clients; i++) {
                if (!finished[i]) {
                    exit = false;
                }
            }
            try {
                Thread.sleep(10);
            } catch (InterruptedException ignored) {}
        } while(!exit);

        System.out.println("test finished, transaction isolation: " + transactionIsolation);
        System.out.println("\n\n");
    }
}