import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.function.Consumer;

import org.bukkit.Bukkit;
import org.bukkit.plugin.Plugin;


public class AsyncMySQL {
    private ExecutorService executor;
    private Plugin plugin;
    private MySQL sql;

    public AsyncMySQL(Plugin owner, String host, int port, String user, String password, String database) {
        try {
            this.sql = new MySQL(host, port, user, password, database);
            this.executor = Executors.newCachedThreadPool();
            this.plugin = owner;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void update(PreparedStatement statement) {
        this.executor.execute(() -> this.sql.queryUpdate(statement));
    }

    public void update(String statement) {
        this.executor.execute(() -> this.sql.queryUpdate(statement));
    }

    public void query(PreparedStatement statement, Consumer<ResultSet> consumer) {
        executor.execute(() -> {
            ResultSet result = this.sql.query(statement);
            Bukkit.getScheduler().runTask(plugin, () -> consumer.accept(result));
        });
    }

    public void query(String statement, Consumer<ResultSet> consumer) {
        this.executor.execute(() -> {
            ResultSet result = this.sql.query(statement);
            Bukkit.getScheduler().runTask(this.plugin, () -> consumer.accept(result));
        });
    }

    public PreparedStatement prepare(String query) {
        try {
            return this.sql.getConnection().prepareStatement(query);
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public MySQL getMySQL() {
        return this.sql;
    }

    public static class MySQL {
        private String host;
        private String user;
        private String password;
        private String database;
        private int port;
        private Connection conn;

        public MySQL(String host, int port, String user, String password, String database) throws Exception {
            this.host = host;
            this.port = port;
            this.user = user;
            this.password = password;
            this.database = database;
            this.openConnection();
        }

        public void queryUpdate(String query) {
            checkConnection();
            try (PreparedStatement statement = conn.prepareStatement(query)) {
               queryUpdate(statement);
            } catch (Exception e) {
               e.printStackTrace();
            }
         }

        public void queryUpdate(PreparedStatement statement) {
            this.checkConnection();
            try {
                try {
                    statement.executeUpdate();
                }
                catch (Exception e) {
                    e.printStackTrace();
                    try {
                        statement.close();
                    }
                    catch (Exception e2) {
                        e2.printStackTrace();
                    }
                }
            }
            finally {
                try {
                    statement.close();
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        public ResultSet query(String query) {
            this.checkConnection();
            try {
                return this.query(this.conn.prepareStatement(query));
            }
            catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }

        public ResultSet query(PreparedStatement statement) {
            this.checkConnection();
            try {
                return statement.executeQuery();
            }
            catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }

        public Connection getConnection() {
            return this.conn;
        }

        private void checkConnection() {
            try {
                if (this.conn == null || !this.conn.isValid(10) || this.conn.isClosed()) {
                    this.openConnection();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }

        public Connection openConnection() throws Exception {
            Class.forName("com.mysql.jdbc.Driver");
            this.conn = DriverManager.getConnection("jdbc:mysql://" + this.host + ":" + this.port + "/" + this.database, this.user, this.password);
            return this.conn;
        }

        public void closeConnection() {
            try {
                try {
                    this.conn.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                    this.conn = null;
                }
            }
            finally {
                this.conn = null;
            }
        }
    }

}