public class MySQL {

    private static MySQL instance;

    private String host, database, user, password;
    private int port;
    private static Connection con;

    private ExecutorService executor;
    private Plugin plugin;

    public MySQL(Plugin plugin, String host, String database, String user, String password, int port) {
        this.plugin = plugin;
        this.executor = Executors.newCachedThreadPool();
        this.host = host;
        this.database = database;
        this.user = user;
        this.password = password;
        this.port = port;

        MySQL.instance = this;
    }

    public void update(PreparedStatement stmt) {
        if (isConnected()) {
            executor.execute(() -> queryUpdate(stmt));
        }
    }

    public void update(String stmt) {
        if (isConnected()) {
            executor.execute(() -> queryUpdate(stmt));
        }
    }

    public void query(PreparedStatement stmt, Consumer<ResultSet> consumer) {
        if (isConnected()) {
            this.executor.execute(() -> {
                ResultSet rs = query(stmt);
                Bukkit.getScheduler().runTask(this.plugin, () -> {
                    consumer.accept(rs);
                });
            });
        }
    }

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

    private void queryUpdate(String qry) {
        if (this.isConnected()) {
            try (PreparedStatement stmt = con.prepareStatement(qry)) {
                this.queryUpdate(stmt);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public ResultSet query(String qry) {
        if (isConnected()) {
            try {
                return query(con.prepareStatement(qry));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private void queryUpdate(PreparedStatement stmt) {
        if (this.isConnected()) {
            try {
                stmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public ResultSet query(PreparedStatement stmt) {
        if (this.isConnected()) {
            try {
                return stmt.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static ResultSet getResult(String qry) {
        ResultSet rs = null;
        try {
            Statement st = con.createStatement();
            rs = st.executeQuery(qry);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    private boolean isConnected() {
        try {
            if (this.con == null || !this.con.isValid(10) || this.con.isClosed()) {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

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

    public void connect() {
        try {
            con = DriverManager.getConnection("jdbc:mysql://" + this.host + ":" + this.port + "/" + this.database, this.user, this.password);
            Bukkit.getConsoleSender().sendMessage("§8[§aMySQL§8] §aEine Verbindung zur MySQL Datenbank wurde hergestellt");
        } catch (SQLException e) {
            Bukkit.getConsoleSender().sendMessage("§8[§aMySQL§8] §cKonnte keine Verbindung zur MySQL Datenbank herstellen, Fehler§8: §e" + e.getMessage());
        }
    }

    public Connection getConnection() {
        if (this.isConnected()) {
            return con;
        }
        return null;
    }

    public static PreparedStatement getStatement(String sql) {
        try {
            return con.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static MySQL getInstance() {
        return instance;
    }
}