package me.mrcookies.helper.database;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import me.mrcookies.helper.utils.References;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;

public class MySQL {

    private static final HikariConfig hcConfig = new HikariConfig();
    private static final HikariDataSource hds;

    static {
        hcConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
        hcConfig.setJdbcUrl("jdbc:mysql://" + References.host + ":" + References.port + "/" + References.database);
        hcConfig.setUsername(References.username);
        hcConfig.setPassword(References.password);
        hcConfig.addDataSourceProperty("cachePrepStmts", "true");
        hcConfig.addDataSourceProperty("prepStmtCacheSize", "250");
        hcConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        hcConfig.setConnectionTimeout(30000);
        hcConfig.setMaximumPoolSize(50);
        hcConfig.setLeakDetectionThreshold(60 * 1000);
        hcConfig.setAutoCommit(true);
        hds = new HikariDataSource(hcConfig);
    }

    private static Connection getConnection() {
        try {
            return hds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void initialize() {

        Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");

        try {
            connection.prepareStatement("SELECT 1 FROM members LIMIT 1").executeQuery().close();
            connection.close();
        } catch (final SQLException e) {
            try {
                PreparedStatement ps = connection.prepareStatement(
                        "CREATE TABLE IF NOT EXISTS `members` (\n" +
                                "  `id` int auto_increment primary key,\n" +
                                "  `username` text,\n" +
                                "  `id_long` bigint\n" +
                                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                );

                ps.executeUpdate();
                ps.close();
                connection.close();
                System.out.println("Helper > Database structure created...");
            } catch (final SQLException e1) {
                e1.printStackTrace();

                try {
                    connection.close();
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }
            }
        }
    }


    public String getString(String table, String key, String where, String value) {
        Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");

        try {
            final PreparedStatement ps = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
            ps.setString(1, value);
            final ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String result = rs.getString(key);
                rs.close();
                ps.close();
                connection.close();
                return result;
            }
            connection.close();
        } catch (final SQLException e) {
            e.printStackTrace();

            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return null;
    }

    public boolean getBool(String table, String key, String where, String value) {
        Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");

        try {
            final PreparedStatement ps = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
            ps.setString(1, value);
            final ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                boolean result = rs.getBoolean(key);
                rs.close();
                ps.close();
                connection.close();
                return result;
            }
            connection.close();
        } catch (final SQLException e) {
            e.printStackTrace();

            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return false;
    }

    public MySQL setString(String table, String key, String value, String where, String wherevalue) {
        Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
        try {
            PreparedStatement ps;
            final PreparedStatement check = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
            check.setString(1, wherevalue);

            if (check.executeQuery().next()) {
                ps = connection.prepareStatement(String.format("UPDATE %s SET %s = ? WHERE %s = ?", table, key, where));
                ps.setString(1, value);
                ps.setString(2, wherevalue);
            } else {
                ps = connection.prepareStatement(String.format("INSERT INTO %s (%s, %s) VALUES (?, ?)", table, where, key));
                ps.setString(1, wherevalue);
                ps.setString(2, value);
                check.close();
            }

            ps.execute();
            ps.close();
            connection.close();
        } catch (final SQLException e) {
            e.printStackTrace();

            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

        return this;
    }

    public MySQL setBool(String table, String key, boolean value, String where, String wherevalue) {
        Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");

        try {
            PreparedStatement ps;
            final PreparedStatement check = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
            check.setString(1, wherevalue);

            if (check.executeQuery().next()) {
                ps = connection.prepareStatement(String.format("UPDATE %s SET %s = ? WHERE %s = ?", table, key, where));
                ps.setInt(1, value ? 1 : 0);
                ps.setString(2, wherevalue);
            } else {
                ps = connection.prepareStatement(String.format("INSERT INTO %s (%s, %s) VALUES (?, ?)", table, where, key));
                ps.setString(1, wherevalue);
                ps.setInt(2, value ? 1 : 0);
            }

            check.close();

            ps.execute();
            ps.close();
            connection.close();
        } catch (final SQLException e) {
            e.printStackTrace();

            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

        return this;
    }

    public MySQL dropEntry(String table, String where, String wherevalue) {
        Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");

        try {
            PreparedStatement ps;
            final PreparedStatement check = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
            check.setString(1, wherevalue);

            if (check.executeQuery().next()) {
                ps = connection.prepareStatement(String.format("DELETE FROM %s WHERE %s = ?", table, where));
                ps.setString(1, wherevalue);
                ps.execute();
                ps.close();
            }

            check.close();
            connection.close();
        } catch (final SQLException e) {
            e.printStackTrace();

            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

        return this;
    }

}