package me.trent;

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

/**
 * Created by trent on 6/1/2017.
 */
public class DB {

    public static String ip = "75.102.176.13";
    public static String db = "s9_RPG";
    public static String user = "u9_J33pHBGgR4";
    public static String pass = "QFzA1TcdzEII9L4A";
    public static String url = "jdbc:mysql://";

    public static Connection connection;

    public static void start() {
        try {
            connection = DriverManager.getConnection("jdbc:mysql://" + ip + "/" + db + "?user=" + user + "&password=" + pass);
            System.out.print("\n\n\n\n CONNECTED \n\n\n\n");

            //    connection = DriverManager.getConnection("jdbc:mysql://");
            for (String table : tables){
                if (!isTable(table)){
                    if (table.equals("verify_data")){
                        addTable("verify_data", "playerUUID", 100);
                    }
                    if (table.equals("verify_queue")){
                        addTable("verify_queue", "uuid", 100);
                    }
                }
            }

            for (String column : discordColumns){
                if (!isColumn("verify_data", column)){
                    addColumn("verify_data", column, "varchar(100)");
                }
            }

            for (String column : queueColumns){
                if (!isColumn("verify_queue", column)){
                    addColumn("verify_queue", column, "varchar(100)");
                }
            }

            //   hologram.current_id = readResultSetInt("SELECT current_id from hologram_data");
        }catch(SQLException e){
            e.printStackTrace();
            System.out.print("\n\n\n");
            // Main.getInstance().getUtils().broadcast("&cERROR:&A DATABASE CONNECTION NOT FOUND... CHECK DEDI HOST. &bDisabling plugin....");
           // Bukkit.getPluginManager().disablePlugin(Main.getInstance());
        }
    }

    public static boolean isColumn(String table, String column){
        boolean a = false;
        ResultSet r;
        try {
            DatabaseMetaData m = connection.getMetaData();
            r = m.getColumns(null, null, table, column);
            if (!r.next()){
                a = false;
            }else{
                a = true;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return a;
    }
    public static boolean isTable(String table){
        boolean a = false;
        ResultSet r;
        try {
            DatabaseMetaData m = connection.getMetaData();
            r = m.getTables(null, null, table, null);
            if (!r.next()){
                a = false;
            }else{
                a = true;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return a;
    }

    public static String[] discordColumns = {"discordTag"};
    public static String[] queueColumns = {"code"};
    public static String[] tables = {"verify_data", "verify_queue"};

    public static void addTable(String tableName, String defaultColumn, int amount){
        PreparedStatement s = preparedStatement("CREATE TABLE "+tableName+" ("+defaultColumn+" VARCHAR("+amount+") NOT NULL)");
        update(s);
    }

    public static void addColumn(String tablename, String column, String type){
        PreparedStatement s = preparedStatement("ALTER TABLE "+tablename+" add "+column+" "+type+" NOT NULL");
        update(s);
    }

    public static PreparedStatement preparedStatement(String query) {
        PreparedStatement s = null;
        try {
            s = connection.prepareStatement(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }
    public static boolean checkResultSet(String query, String lookfor){
        ResultSet s;
        boolean a = false;
        try{
            s = preparedStatement(query).executeQuery(query);
            while (s.next()) {
                if (s.getString(lookfor) != null) {
                    a = true;
                }else{
                    a = false;
                }
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return a;
    }
    public static String readResultSet(String query){
        String returned = "";
        ResultSet s;
        try{
            s = preparedStatement(query).executeQuery(query);
            while(s.next()){
                returned = s.getString(1);
            }
        }catch(SQLException e){
            e.printStackTrace();
            returned = "";
        }
        return returned;
    }
    public static int readResultSetInt(String query){
        int returned = 0;
        ResultSet s;
        try{
            s = preparedStatement(query).executeQuery(query);
            while(s.next()){
                returned = s.getInt(1);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return returned;
    }
    public static double readResultSetDouble(String query){
        double returned = 0;
        ResultSet s;
        try{
            s = preparedStatement(query).executeQuery(query);
            while(s.next()){
                returned = s.getDouble(1);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return returned;
    }

    public static void update(PreparedStatement statement) {
        try {
            try {
                statement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (NullPointerException e) {}
        } finally {
            try {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } catch (NullPointerException e) {}
        }
    }

    public static void removeRow(String table, String column, String where_equals){
        try {
            PreparedStatement s;
            s = preparedStatement("DELETE FROM " + table + " WHERE " + column + " = ?");
            s.setString(1, where_equals);
            s.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }
    }

    public static void setColumn(String table, String column, String value, String where, String where_equals){
        PreparedStatement s;
        if (where_equals == null && where == null){
            //  s = preparedStatement("UPDATE "+table+" SET "+column+"=?");
            s = preparedStatement("INSERT IGNORE INTO "+table+" ("+column+") VALUES (?) ON DUPLICATE KEY UPDATE "+column+"=?");
        }else{
            s = preparedStatement("UPDATE "+table+" SET "+column+"=? WHERE "+where+"='"+where_equals+"'");
        }
        try {
            s.setString(1, value);
            if (where_equals == null && where == null){
                s.setString(2, value);
            }
            update(s);
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    public static void setColumnForList(String table, String column, List<String> value, String where, String where_equals){
        PreparedStatement s;
        if (where_equals == null && where == null){
            //  s = preparedStatement("UPDATE "+table+" SET "+column+"=?");
            s = preparedStatement("INSERT IGNORE INTO "+table+" ("+column+") VALUES (?) ON DUPLICATE KEY UPDATE "+column+"=?");
        }else{
            s = preparedStatement("UPDATE "+table+" SET "+column+"=? WHERE "+where+"='"+where_equals+"'");
        }
        try {
            String goodValue = "";
            for (String strings : value){
                if (goodValue.equalsIgnoreCase("")){
                    //is blank, new place
                    goodValue = strings;
                }else{
                    //is not blank, keep adding them with commas
                    goodValue = goodValue+","+strings;
                }
            }
            s.setString(1, goodValue);
            if (where_equals == null && where == null){
                s.setString(2, goodValue);
            }
            update(s);
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    public static void setColumn(String table, String column, int value, String where, String where_equals){
        PreparedStatement s;
        if (where_equals == null && where == null){
            //  s = preparedStatement("UPDATE "+table+" SET "+column+"=?");
            s = preparedStatement("INSERT IGNORE INTO "+table+" ("+column+") VALUES (?) ON DUPLICATE KEY UPDATE "+column+"=?");
        }else{
            s = preparedStatement("UPDATE "+table+" SET "+column+"=? WHERE "+where+"='"+where_equals+"'");
        }
        try {
            s.setInt(1, value);
            if (where_equals == null && where == null){
                s.setInt(2, value);
            }
            update(s);
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}