import net.astrocoder.dev.Core;
import net.astrocoder.dev.core.PlayerProfile;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.logging.Level;

public class Database {

    String user = "";
    String database = "";
    String password = "";
    String port = "";
    String hostname = "";
    Connection c = null;

    public Database(String user, String password, String database, String port, String hostname) {
        this.user = user;
        this.database = database;
        this.password = password;
        if(port == null){
            this.port = "3306";
        }else{
            this.port = port;
        }
        this.hostname = hostname;
    }

    /**
     * Open mysql connection
     * @return
     */
    public Connection open() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            c = DriverManager.getConnection("jdbc:mysql://" + hostname + ":" + port + "/" + database + "?autoReconnect=true", user, password);
            System.out.println("MySQL connection successful");
            Core.getInstance().getLogger().log(Level.INFO, "MySQL connection successful");
            return c;
        } catch (SQLException e) {
            Core.getInstance().getLogger().log(Level.SEVERE, "MySQL connection has been aborted");
            Core.getInstance().getLogger().log(Level.SEVERE, "Reason:" + e.getMessage());
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC Driver not found!");
        }
        return c;
    }

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

    /**
     * Close mysql connection
     */
    public Connection close(Connection c){
        try{
            c.close();
        } catch (SQLException e) {
            Core.getInstance().getLogger().log(Level.SEVERE, "MySQL can't close connection");
            e.printStackTrace();
        }
        return c;
    }

    /**
     * Get all tables together and setup one by one
     */
    public void setupTables(){
        setupProfiles();
    }

    /**
     * Setup the hub player profiles
     */
    public void setupProfiles(){
        try {
            PreparedStatement profiles = c
                    .prepareStatement("CREATE TABLE IF NOT EXISTS `Core`(`id` int(11) NOT NULL auto_increment,"
                            + " `UUID` varchar(255) NOT NULL,"
                            + " `PlayTime` int(255) NOT NULL,"
                            + " `Coins` bigint(255) NOT NULL,"
                            + " `Spins` bigint(255) NOT NULL,"
                            + "`PlayersVisible` BOOLEAN NOT NULL,"
                            + "`Chat` BOOLEAN NOT NULL,"
                            + "`ParticlesEnable` BOOLEAN NOT NULL,"
                            + "`NumberJoins` int(10) NOT NULL,"
                            + "`NumberKicks` int(10) NOT NULL,"
                            + "`Level` bigint(155) NOT NULL,"
                            + " PRIMARY KEY(`id`));");
            profiles.execute();
            profiles.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Try to create a new player profile
     * @param uuid
     * @return
     */
    public boolean createPlayerProfile(UUID uuid){
        try
        {
            String coreStatement = "SELECT * FROM Core WHERE UUID = '" + uuid + "';";
            ResultSet result = this.c.createStatement().executeQuery(coreStatement);
            if (result.next()) {
                //PlayerDat.addPlayerDat(new PlayerDat(uuid, new Date(), result.getLong("PlayTime"), result.getBoolean("PlayersVisible"), result.getBoolean("Chat"), result.getInt("Points"), result.getInt("NumberBans"), result.getInt("NumberJoins"), result.getInt("NumberKicks"), result.getInt("NumberReports")));
                PlayerProfile.addPlayerProfile(new PlayerProfile(
                        uuid,
                        new Date(),
                        result.getLong("PlayTime"),
                        result.getLong("Coins"),
                        result.getLong("Spins"),
                        result.getBoolean("PlayersVisible"),
                        result.getBoolean("Chat"),
                        result.getBoolean("ParticlesEnable"),
                        result.getInt("NumberJoins"),
                        result.getInt("NumberKicks"),
                        result.getInt("Level")));

            } else {
                //PlayerDat.addPlayerDat(new PlayerDat(uuid, new Date(), 0L, true, true, 0, 0, 1, 0, 0));
                PlayerProfile.addPlayerProfile(new PlayerProfile(uuid, new Date(), 0L,0L,0L,true,true,true,0,0,0));
            }
        }
        catch (SQLException e)
        {
            System.out.println("Error while trying to create PlayerDat! Reason: " + e.getMessage());
            return false;
        }
        return true;
    }

    /**
     * Update all data from player profile to the sql database
     * @param playerProfile
     * @return
     */
    public boolean updatePlayerProfileSQL(PlayerProfile playerProfile)
    {
        try
        {
            /*
             PlayerProfile.addPlayerProfile(new PlayerProfile(
                        uuid,
                        new Date(),
                        result.getLong("PlayTime"),
                        result.getLong("Coins"),
                        result.getLong("Spins"),
                        result.getBoolean("PlayersVisible"),
                        result.getBoolean("Chat"),
                        result.getBoolean("ParticlesEnable"),
                        result.getInt("NumberJoins"),
                        result.getInt("NumberKicks")));
             */
            String coreStatement = "SELECT * FROM Core WHERE UUID = '" + playerProfile.getUUID() + "'";
            ResultSet result = this.c.createStatement().executeQuery(coreStatement);
            if (result.next())
            {
                String coreUpdate = "UPDATE Core SET PlayTime = " + playerProfile.getPlayTime()
                        + ", Coins = " + playerProfile.getCoins()
                        + ", Spins = " + playerProfile.getSpins()
                        + ", PlayersVisible = " + playerProfile.isPlayersVisible()
                        + ", Chat = " + playerProfile.hasChat()
                        + ", ParticlesEnable = " + playerProfile.isParticles()
                        + ", NumberJoins = " + playerProfile.getNumberJoins()
                        + ", Level = " + playerProfile.getLevel()
                        + ", NumberKicks = " + playerProfile.getNumberKicks() + " " //Caso de erro ver aqui, falta virgula
                        + " WHERE UUID = '"
                        + playerProfile.getUUID() + "';";
                this.c.createStatement().executeUpdate(coreUpdate);
            }
            else
            {
                String coreUpdate = "INSERT INTO Core (UUID, PlayTime, Coins, Spins, PlayersVisible, Chat, ParticlesEnable, NumberJoins, NumberKicks, Level) VALUES ('"
                        + playerProfile.getUUID() + "', "
                        + playerProfile.getPlayTime() + ", "
                        + playerProfile.getCoins() + ", "
                        + playerProfile.getSpins() + ", "
                        + playerProfile.isPlayersVisible() + ", "
                        + playerProfile.hasChat() + ", "
                        + playerProfile.isParticles() + ", "
                        + playerProfile.getNumberJoins() + ", "
                        + playerProfile.getNumberKicks() + ", "
                        + playerProfile.getLevel() + "); ";
                this.c.createStatement().executeUpdate(coreUpdate);
            }
        }
        catch (SQLException e)
        {
            System.out.println("Error while trying to update the MySQL! Reason: " + e.getMessage());
            return false;
        }
        return true;
    }

    /**
     * Create a sql query
     * @param query
     * @return
     */
    public ResultSet querySQL(String query) {
        Connection c = null;

        if (checkConnection()) {
            c = getConnection();
        } else {
            c = open();
        }

        Statement s = null;

        try {
            s = c.createStatement();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        ResultSet ret = null;

        try {
            ret = s.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        close(c);

        return ret;
    }

    /**
     * Check is there are connection
     * @return
     */
    private boolean checkConnection() {
        return c != null;
    }

    /**
     * Update sql query
     * @param update
     */
    public void updateSQL(String update) {

        Connection c = null;

        if (checkConnection()) {
            c = getConnection();
        } else {
            c = open();
        }

        Statement s = null;

        try {
            s = c.createStatement();
            s.executeUpdate(update);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        open();

    }

    /**
     * Get Coins Top
     * @return
     * @throws SQLException
     */
    public List<String> getCoinsTopPlayers() throws SQLException{
        List<String> result = new ArrayList<String>();
        Statement s = c.createStatement();
        if(s != null){
            ResultSet rs = s.executeQuery("SELECT UUID FROM `Core` ORDER BY `Coins`");
            rs.findColumn("UUID");
            if(rs.last()){
                result.add(rs.getString("UUID"));
            }if(rs.previous()){
                result.add(rs.getString("UUID"));
            }if(rs.previous()){
                result.add(rs.getString("UUID"));
            }
        }
        return result;
    }

    /**
     * Get Spins Top
     * @return
     * @throws SQLException
     */
    public List<String> getSpinsTopPlayers() throws SQLException{
        List<String> result = new ArrayList<String>();
        Statement s = c.createStatement();
        if(s != null){
            ResultSet rs = s.executeQuery("SELECT UUID FROM `Core` ORDER BY `Spins`");
            rs.findColumn("UUID");
            if(rs.last()){
                result.add(rs.getString("UUID"));
            }if(rs.previous()){
                result.add(rs.getString("UUID"));
            }if(rs.previous()){
                result.add(rs.getString("UUID"));
            }
        }
        return result;
    }

}