package me.mrdarkness462.thepit.pluginsupport.storage;

import me.mrdarkness462.thepit.pluginfiles.Settings;
import me.mrdarkness462.thepit.thepitfeatures.playersync.InventoryConverter;
import me.mrdarkness462.thepit.thepitfeatures.playersync.SyncType;
import org.bukkit.entity.Player;

import java.sql.*;
import java.util.List;

public class MySQL implements Database {
    private Connection connection;
    private Settings settings = new Settings();
    private String host = settings.DB_host();
    private int port = settings.DB_port();
    private String database = settings.DB_database();
    private String ssl = settings.DB_ssl();
    private String username = settings.DB_username();
    private String password = settings.DB_password();
    private static MySQL instance = new MySQL();
    private InventoryConverter inventoryConverter = new InventoryConverter();

    private MySQL() {
        if (!connected()) {
            connect();
        }
    }

    public void connect() {
        try {
            if (connection != null && !connection.isClosed()) {
                return;
            }
            synchronized (this) {
                if (connection != null && !connection.isClosed()) {
                    return;
                }
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database + "?autoReconnect=true&user=" + username + "&password=" + password + "&useSSL=" + ssl);
            }
        } catch (SQLException | ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    public boolean connected() {
        return connection != null;
    }

    public void close() {
        if (connected()) {
            try {
                connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public int getInt(Player p, String stats, String table) {
        if (!connected()) {
            connect();
        }
        try {
            ResultSet rs = connection.createStatement().executeQuery("SELECT " + stats + " FROM " + table + " WHERE UUID = '" + p.getUniqueId() + "';");
            if (rs.next()) {
                return rs.getInt(stats);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public void setInt(Player p, int value, String stats, String table) {
        if (!connected()) {
            connect();
        }
        if (hasAccount(p, table)) {
            try {
                connection.createStatement().executeUpdate("UPDATE " + table + " SET " + stats + " = '" + value + "' WHERE UUID = '" + p.getUniqueId() + "';");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public String getString(Player p, String stats, String table) {
        if (!connected()) {
            connect();
        }
        try {
            ResultSet rs = connection.createStatement().executeQuery("SELECT " + stats + " FROM " + table + " WHERE UUID = '" + p.getUniqueId() + "';");
            if (rs.next()) {
                return rs.getString(stats);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public void setString(Player p, String value, String stats, String table) {
        if (!connected()) {
            connect();
        }
        if (hasAccount(p, table)) {
            try {
                connection.createStatement().executeUpdate("UPDATE " + table + " SET " + stats + " = '" + value + "' WHERE UUID = '" + p.getUniqueId() + "';");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public void update(Player p, String sql, String table) {
        if (!connected()) {
            connect();
        }
        if (hasAccount(p, table)) {
            try {
                connection.createStatement().executeUpdate("UPDATE " + table + " " + sql + " WHERE UUID = '" + p.getUniqueId() + "';");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public void createTable(String table, List<String> columns) {
        if (!connected()) {
            connect();
        }
        try {
            connection.createStatement().execute("CREATE TABLE IF NOT EXISTS `" + table + "` (" + String.join(", ", columns) + ");");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public boolean hasAccount(Player p, String table) {
        if (!connected()) {
            connect();
        }
        ResultSet rs = null;
        Statement statement = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (statement != null) {
                rs = statement.executeQuery("SELECT UUID FROM " + table + " WHERE UUID = '" + p.getUniqueId() + "';");
                return rs.next();
            }
            return false;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    statement.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public void addColumn(String column, String type, String def, String table) {
        if (!connected()) {
            connect();
        }
        try {
            connection.createStatement().execute("ALTER TABLE `" + table + "` ADD COLUMN `" + column + "` " + type + " NOT NULL default '" + def + "';");
        } catch (SQLException ignored) {
        }
    }

    public void addColumn(String column, String type, String table) {
        if (!connected()) {
            connect();
        }
        try {
            connection.createStatement().execute("ALTER TABLE `" + table + "` ADD COLUMN `" + column + "` " + type + ";");
        } catch (SQLException ignored) {
        }
    }

    public void deletePlayer(Player p, String table) {
        if (!connected()) {
            connect();
        }
        try {
            connection.createStatement().execute("DELETE FROM " + table + " WHERE UUID = '" + p.getUniqueId() + "';");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void createPlayer(String table, String columns, String values) {
        if (!connected()) {
            connect();
        }
        try {
            connection.createStatement().execute("INSERT INTO " + table + " (" + columns + ") VALUES (" + values + ");");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void savePlayer(Player p) {
        if (!connected()) {
            connect();
        }
        String pInv = SyncType.PLAYER_INVENTORY.name() + " = '" + inventoryConverter.getString(p.getInventory()) + "', ";
        String pEch = SyncType.PLAYER_ENDERCHEST.name() + " = '" + inventoryConverter.getString(p.getEnderChest()) + "', ";
        String pExp = SyncType.PLAYER_XP.name() + " = " + p.getTotalExperience() + " ";
        String sync = pInv + pEch + pExp;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE ThePitPlayerSync SET " + sync + "WHERE UUID = '" + p.getUniqueId() + "';");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

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