package net.benjidial.nswp;

import org.bukkit.entity.Player;
import org.bukkit.Location;
import org.bukkit.OfflinePlayer;
import org.bukkit.Bukkit;

import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.UUID;

public class Database {
  static Connection connection;

  public static void connect(String path) throws SQLException {
    connection = DriverManager.getConnection("jdbc:sqlite:" + path);
  }

  static String playerID(Player player) {
    return player.getUniqueId().toString().replaceAll("-", "");
  }

  static OfflinePlayer idToPlayer(String id) {
    return Bukkit.getOfflinePlayer(UUID.fromString(
      id.substring( 0,  8) + "-" + id.substring( 8, 12) + "-" +
      id.substring(12, 16) + "-" + id.substring(16, 20) + "-" +
      id.substring(20, 32)
    ));
  }

  static String waypointTableName(Player player) {
    return "waypoints_" + playerID(player);
  }

  public static void createWaypointTable(Player player) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "CREATE TABLE IF NOT EXISTS " + waypointTableName(player) + " (" +
        "name  TEXT PRIMARY KEY, " +
        "world TEXT NOT NULL, " +
        "x     REAL NOT NULL, " +
        "y     REAL NOT NULL, " +
        "z     REAL NOT NULL, " +
        "pitch REAL NOT NULL, " +
        "yaw   REAL NOT NULL" +
      ")"
    );
    statement.executeUpdate();
  }

  static void createWBacks() throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "CREATE TABLE IF NOT EXISTS wbacks (" +
        "player_uuid TEXT PRIMARY KEY, " +
        "world       TEXT NOT NULL, " +
        "x           REAL NOT NULL, " +
        "y           REAL NOT NULL, " +
        "z           REAL NOT NULL, " +
        "pitch       REAL NOT NULL, " +
        "yaw         REAL NOT NULL" +
      ")"
    );
    statement.executeUpdate();
  }

  public static Location getWBack(Player player) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM wbacks WHERE player_uuid = ?"
    );
    statement.setString(1, playerID(player));
    ResultSet results = statement.executeQuery();
    if (!results.next())
      return null;
    return cursorToLocation(results);
  }

  public static void setWBack(Player player, Location location) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "REPLACE INTO wbacks(player_uuid, world, x, y, z, pitch, yaw) VALUES(?, ?, ?, ?, ?, ?, ?)"
    );
    statement.setString(1, playerID(player));
    statement.setString(2, location.getWorld().getName());
    statement.setDouble(3, location.getX());
    statement.setDouble(4, location.getY());
    statement.setDouble(5, location.getZ());
    statement.setDouble(6, location.getPitch());
    statement.setDouble(7, location.getYaw());
    statement.executeUpdate();
  }

  static Location cursorToLocation(ResultSet results) throws SQLException {
    return new Location(
      Bukkit.getWorld(results.getString("world")),
      results.getDouble("x"),
      results.getDouble("y"),
      results.getDouble("z"),
      (float)results.getDouble("yaw"),
      (float)results.getDouble("pitch")
    );
  }

  static Waypoint cursorToWaypoint(ResultSet results) throws SQLException {
    return new Waypoint(
      results.getString("name"),
      cursorToLocation(results)
    );
  }

  public static ArrayList<Waypoint> searchWaypoints(Player player, String searchString) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM " + waypointTableName(player) + " WHERE name LIKE ? ESCAPE '!' ORDER BY world, name ASC"
    );
    statement.setString(1, "%" + searchString.replace("!", "!!").replace("_", "!_") + "%");
    ResultSet results = statement.executeQuery();
    ArrayList<Waypoint> list = new ArrayList<Waypoint>();
    while (results.next())
      list.add(cursorToWaypoint(results));
    return list;
  }

  public static Waypoint lookupWaypoint(Player player, String name) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM " + waypointTableName(player) + " WHERE name = ?"
    );
    statement.setString(1, name);
    ResultSet results = statement.executeQuery();
    if (!results.next())
      return null;
    return cursorToWaypoint(results);
  }

  public static void addWaypoint(Player player, Waypoint waypoint) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "INSERT INTO " + waypointTableName(player) + "(name, world, x, y, z, pitch, yaw) " +
      "VALUES(?, ?, ?, ?, ?, ?, ?)"
    );
    statement.setString(1, waypoint.name);
    statement.setString(2, waypoint.location.getWorld().getName());
    statement.setDouble(3, waypoint.location.getX());
    statement.setDouble(4, waypoint.location.getY());
    statement.setDouble(5, waypoint.location.getZ());
    statement.setDouble(6, waypoint.location.getPitch());
    statement.setDouble(7, waypoint.location.getYaw());
    statement.executeUpdate();
  }

  public static void deleteWaypoint(Player player, String name) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "DELETE FROM " + waypointTableName(player) + " WHERE name = ?"
    );
    statement.setString(1, name);
    statement.executeUpdate();
  }

  public static void createTPATable() throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "CREATE TABLE IF NOT EXISTS tpa_allowed (" +
        "from_id TEXT NOT NULL, " +
        "to_id   TEXT NOT NULL" +
      ")"
    );
    statement.executeUpdate();
  }

  public static boolean isTPAAllowed(Player from, Player to) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM tpa_allowed WHERE from_id = ? AND to_id = ?"
    );
    statement.setString(1, playerID(from));
    statement.setString(2, playerID(to));
    ResultSet results = statement.executeQuery();
    return results.next();
  }

  public static void allowTPA(Player from, Player to) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "INSERT INTO tpa_allowed(from_id, to_id) VALUES(?, ?)"
    );
    statement.setString(1, playerID(from));
    statement.setString(2, playerID(to));
    statement.executeUpdate();
  }

  public static int disallowTPA(String fromName, Player to) throws SQLException {
    //this is kinda scrungled but i think it should work.

    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM tpa_allowed WHERE to_id = ?"
    );
    statement.setString(1, playerID(to));
    ResultSet table = statement.executeQuery();

    int removed = 0;

    while (table.next()) {
      String fromID = table.getString("from_id");

      if (idToPlayer(fromID).getName().equals(fromName)) {
        PreparedStatement deleteStatement = connection.prepareStatement(
          "DELETE FROM tpa_allowed WHERE from_id = ? AND to_id = ?"
        );
        deleteStatement.setString(1, fromID);
        deleteStatement.setString(2, playerID(to));
        deleteStatement.executeUpdate();
        ++removed;
      }
    }

    return removed;
  }

  public static ArrayList<String> getOnlineTPADisallowedTo(Player to) throws SQLException {

    ArrayList<String> results = new ArrayList<>();
    for (Player player : Bukkit.getOnlinePlayers())
      if (!isTPAAllowed(player, to))
        results.add(player.getName());

    return results;

  }

  public static ArrayList<String> getTPAAllowedTo(Player to) throws SQLException {

    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM tpa_allowed WHERE to_id = ?"
    );
    statement.setString(1, playerID(to));
    ResultSet table = statement.executeQuery();

    ArrayList<String> results = new ArrayList<>();
    while (table.next()) {
      String name = idToPlayer(table.getString("from_id")).getName();
      //there is no way to disallow a player whose name is not know. but, to allow a player,
      //they have to be online, and so their name should be known.
      if (name != null)
        results.add(name);
    }

    return results;

  }

  public static ArrayList<String> getOnlineTPAAllowedFrom(Player from) throws SQLException {

    ArrayList<String> results = new ArrayList<>();
    for (Player player : Bukkit.getOnlinePlayers())
      if (isTPAAllowed(from, player))
        results.add(player.getName());

    return results;

  }
}