package net.benjidial.nswp;

import org.bukkit.entity.Player;
import org.bukkit.Location;
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;

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 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 {
    createWBacks();
    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 {
    createWBacks();
    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 start) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM " + waypointTableName(player) + " WHERE name LIKE ? ESCAPE '!' ORDER BY world, name ASC"
    );
    statement.setString(1, "%" + start.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 {
    if (lookupWaypoint(player, waypoint.name) != null)
      player.sendMessage("There is already a waypoint with that name.");
    else {
      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();
      player.sendMessage("Waypoint added.");
    }
  }

  public static void deleteWaypoint(Player player, String name) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(
      "DELETE FROM " + waypointTableName(player) + " WHERE name = ?"
    );
    statement.setString(1, name);
    if (statement.executeUpdate() == 0)
      player.sendMessage("No waypoint with that name.");
    else
      player.sendMessage("Waypoint deleted.");
  }
}