package de.uhcsg.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;
import org.bukkit.entity.Player;

import de.uhcsg.main.Main;

public class MySQL
{
	private static String host = "";
	private static String database = "";
	private static String user = "";
	private static String password = "";

	public MySQL() {

		connect();
	}

	public MySQL(String host, String databse, String user) {
		this.host = host;
		this.database = database;
		this.user = user;

		connect();
	}

	private static Connection connection;

  public static Connection getConnection()
  {
    return connection;
  }

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

  public static void connect()
  {
    if (!isConnected()) {
      try
      {
        connection = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/" + database + "?autoReconnect=true", user, password);
      }
      catch (SQLException ex)
      {
        ex.printStackTrace();
      }
    }
  }

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

  public static void createTableIfNotExists()
  {
    if (isConnected()) {
      try
      {
        PreparedStatement ps = getConnection().prepareStatement("CREATE TABLE IF NOT EXISTS Uhcsg (Spielername VARCHAR(100), UUID VARCHAR(100), Kills INT(100), Tode INT(100), Siege INT(100), Niederlagen INT(100), Unentschieden INT(100))");
        ps.executeUpdate();
        ps.close();
      }
      catch (SQLException ex)
      {
        ex.printStackTrace();
      }
    }
  }

  public static boolean isUserExisting(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, p.getUniqueId().toString());
      ResultSet result = ps.executeQuery();
      boolean user = result.next();
      result.close();
      ps.close();
      return user;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return false;
  }

  public static boolean isUserExisting(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      boolean user = result.next();
      result.close();
      ps.close();
      return user;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return false;
  }

  public static void registerPlayer(Player p)
  {
    if (isUserExisting(p)) {
      return;
    }
    try
    {
      PreparedStatement ps = connection.prepareStatement("INSERT INTO Uhcsg (Spielername, UUID, Kills, Tode, Siege, Niederlagen, Unentschieden) VALUES (?, ?, ?, ?, ?, ?, ?)");
      ps.setString(1, p.getName());
      ps.setString(2, p.getUniqueId().toString());
      ps.setInt(3, 0);
      ps.setInt(4, 0);
      ps.setInt(5, 0);
      ps.setInt(6, 0);
      ps.setInt(7, 0);
      ps.execute();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }

  public static void setPlayerName(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("UPDATE Uhcsg SET Spielername = ? WHERE UUID = ?");
      ps.setString(1, p.getName());
      ps.setString(2, p.getUniqueId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }

  public static String getPlayer(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      result.next();
      String name = result.getString("Spielername");
      result.close();
      ps.close();
      return name;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return "null";
  }

  public static int getKills(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, p.getUniqueId().toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int kills = result.getInt("Kills");
      result.close();
      ps.close();
      return kills;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getKills(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int kills = result.getInt("Kills");
      result.close();
      ps.close();
      return kills;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getDeaths(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, p.getUniqueId().toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int deaths = result.getInt("Tode");
      result.close();
      ps.close();
      return deaths;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getDeaths(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int deaths = result.getInt("Tode");
      result.close();
      ps.close();
      return deaths;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static double getKD(Player p)
  {
    try
    {
      PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps1.setString(1, p.getUniqueId().toString());
      ResultSet result1 = ps1.executeQuery();
      result1.next();
      double kills = result1.getInt("Kills");
      result1.close();
      ps1.close();
      PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps2.setString(1, p.getUniqueId().toString());
      ResultSet result2 = ps2.executeQuery();
      result2.next();
      double deaths = result2.getInt("Tode");
      result2.close();
      ps2.close();
      if ((kills == 0.0D) && (deaths == 0.0D)) {
        return 0.0D;
      }
      if (deaths == 0.0D) {
        return kills;
      }
      return Math.round(kills / deaths * 100.0D) / 100.0D;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1.0D;
  }

  public static double getKD(UUID uuid)
  {
    try
    {
      PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps1.setString(1, uuid.toString());
      ResultSet result1 = ps1.executeQuery();
      result1.next();
      double kills = result1.getInt("Kills");
      result1.close();
      ps1.close();
      PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps2.setString(1, uuid.toString());
      ResultSet result2 = ps2.executeQuery();
      result2.next();
      double deaths = result2.getInt("Tode");
      result2.close();
      ps2.close();
      if ((kills == 0.0D) && (deaths == 0.0D)) {
        return 0.0D;
      }
      if (deaths == 0.0D) {
        return kills;
      }
      return Math.round(kills / deaths * 100.0D) / 100.0D;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1.0D;
  }

  public static int getGamesPlayed(Player p)
  {
    try
    {
      PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps1.setString(1, p.getUniqueId().toString());
      ResultSet result1 = ps1.executeQuery();
      result1.next();
      int wins = result1.getInt("Siege");
      result1.close();
      ps1.close();
      PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps2.setString(1, p.getUniqueId().toString());
      ResultSet result2 = ps2.executeQuery();
      result2.next();
      int looses = result2.getInt("Niederlagen");
      result2.close();
      ps2.close();
      PreparedStatement ps3 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps3.setString(1, p.getUniqueId().toString());
      ResultSet result3 = ps3.executeQuery();
      result3.next();
      int undecideds = result3.getInt("Unentschieden");
      result3.close();
      ps3.close();
      return wins + looses + undecideds;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getGamesPlayed(UUID uuid)
  {
    try
    {
      PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps1.setString(1, uuid.toString());
      ResultSet result1 = ps1.executeQuery();
      result1.next();
      int wins = result1.getInt("Siege");
      result1.close();
      ps1.close();
      PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps2.setString(1, uuid.toString());
      ResultSet result2 = ps2.executeQuery();
      result2.next();
      int looses = result2.getInt("Niederlagen");
      result2.close();
      ps2.close();
      PreparedStatement ps3 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps3.setString(1, uuid.toString());
      ResultSet result3 = ps3.executeQuery();
      result3.next();
      int undecideds = result3.getInt("Unentschieden");
      result3.close();
      ps3.close();
      return wins + looses + undecideds;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getWins(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, p.getUniqueId().toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int wins = result.getInt("Siege");
      result.close();
      ps.close();
      return wins;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getWins(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int wins = result.getInt("Siege");
      result.close();
      ps.close();
      return wins;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getLooses(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, p.getUniqueId().toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int looses = result.getInt("Niederlagen");
      result.close();
      ps.close();
      return looses;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getLooses(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int looses = result.getInt("Niederlagen");
      result.close();
      ps.close();
      return looses;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getUndecideds(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, p.getUniqueId().toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int wins = result.getInt("Unentschieden");
      result.close();
      ps.close();
      return wins;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static int getUndecideds(UUID uuid)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps.setString(1, uuid.toString());
      ResultSet result = ps.executeQuery();
      result.next();
      int wins = result.getInt("Unentschieden");
      result.close();
      ps.close();
      return wins;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1;
  }

  public static double getWL(Player p)
  {
    try
    {
      PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps1.setString(1, p.getUniqueId().toString());
      ResultSet result1 = ps1.executeQuery();
      result1.next();
      double wins = result1.getInt("Siege");
      result1.close();
      ps1.close();
      PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps2.setString(1, p.getUniqueId().toString());
      ResultSet result2 = ps2.executeQuery();
      result2.next();
      double looses = result2.getInt("Niederlagen");
      result2.close();
      ps2.close();
      if ((wins == 0.0D) && (looses == 0.0D)) {
        return 0.0D;
      }
      if (looses == 0.0D) {
        return wins;
      }
      return Math.round(wins / looses * 100.0D) / 100.0D;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1.0D;
  }

  public static double getWL(UUID uuid)
  {
    try
    {
      PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps1.setString(1, uuid.toString());
      ResultSet result1 = ps1.executeQuery();
      result1.next();
      double wins = result1.getInt("Siege");
      result1.close();
      ps1.close();
      PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Uhcsg WHERE UUID = ?");
      ps2.setString(1, uuid.toString());
      ResultSet result2 = ps2.executeQuery();
      result2.next();
      double looses = result2.getInt("Niederlagen");
      result2.close();
      ps2.close();
      if ((wins == 0.0D) && (looses == 0.0D)) {
        return 0.0D;
      }
      if (looses == 0.0D) {
        return wins;
      }
      return Math.round(wins / looses * 100.0D) / 100.0D;
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return -1.0D;
  }

  public static int getRank(Player p)
  {
    int rank = -1;
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg ORDER BY Siege DESC");
      ResultSet result = ps.executeQuery();
      while (result.next())
      {
        String uuid2 = result.getString("UUID");
        if (uuid2.equalsIgnoreCase(p.getUniqueId().toString()))
        {
          rank = result.getRow();
          break;
        }
      }
      result.close();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return rank;
  }

  public static int getRank(UUID uuid)
  {
    int rank = -1;
    try
    {
      PreparedStatement ps = connection.prepareStatement("SELECT * FROM Uhcsg ORDER BY Siege DESC");
      ResultSet result = ps.executeQuery();
      while (result.next())
      {
        String uuid2 = result.getString("UUID");
        if (uuid2.equalsIgnoreCase(uuid.toString()))
        {
          rank = result.getRow();
          break;
        }
      }
      result.close();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return rank;
  }

  public static void addKill(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("UPDATE Uhcsg SET Kills = ? WHERE uuid = ?");
      ps.setInt(1, getKills(p) + 1);
      ps.setString(2, p.getUniqueId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }

  public static void addDeath(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("UPDATE Uhcsg SET Tode = ? WHERE uuid = ?");
      ps.setInt(1, getDeaths(p) + 1);
      ps.setString(2, p.getUniqueId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }

  public static void addWin(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("UPDATE Uhcsg SET Siege = ? WHERE uuid = ?");
      ps.setInt(1, getWins(p) + 1);
      ps.setString(2, p.getUniqueId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }

  public static void addLoose(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("UPDATE Uhcsg SET Niederlagen = ? WHERE uuid = ?");
      ps.setInt(1, getLooses(p) + 1);
      ps.setString(2, p.getUniqueId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }

  public static void addUndecided(Player p)
  {
    try
    {
      PreparedStatement ps = connection.prepareStatement("UPDATE Uhcsg SET Unentschieden = ? WHERE uuid = ?");
      ps.setInt(1, getUndecideds(p) + 1);
      ps.setString(2, p.getUniqueId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
  }
}