package dev.deeve.containeraudit;

import org.bukkit.entity.Player;
import org.bukkit.plugin.java.JavaPlugin;
import org.bukkit.inventory.ItemStack;
import org.bukkit.Location;
import org.bukkit.Bukkit;

import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.ResultSet;

public class Database {
  static Connection connection;
  static Logger logger;

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

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

  public static void createTables() throws SQLException {
    PreparedStatement chestEventCreate = connection.prepareStatement(
      "CREATE TABLE IF NOT EXISTS chest_events (" +
        "id         INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "player     TEXT NOT NULL, " +
        "x          REAL NOT NULL, " +
        "y          REAL NOT NULL, " +
        "z          REAL NOT NULL, " +
        "block      TEXT NOT NULL," +
        "count      INTEGER NOT NULL," +
        "event_type TEXT NOT NULL," +
        "timestamp  INTEGER NOT NULL" +
      ")"
    );
    chestEventCreate.executeUpdate();
    
    PreparedStatement chestCreate = connection.prepareStatement(
      "CREATE TABLE IF NOT EXISTS chests (" +
        "id         INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "actor      TEXT NOT NULL, " +
        "action     TEXT NOT NULL," +
        "x          REAL NOT NULL, " +
        "y          REAL NOT NULL, " +
        "z          REAL NOT NULL," +
        "timestamp  INTEGER NOT NULL" +
      ")"
    );
    
    chestCreate.executeUpdate();
    
    PreparedStatement viewCreate = connection.prepareStatement(
    		"CREATE VIEW IF NOT EXISTS chest_block_events AS " +
    		"SELECT * FROM chests AS placed_chest FULL OUTER JOIN chests AS broke_chest ON " +
    		"broke_chest.action = \"BREAK\" AND placed_chest.action = \"PLACE\" AND " +
    		" placed_chest.x = broke_chest.x AND placed_chest.y = broke_chest.y AND placed_chest.z = broke_chest.z AND "
    		+ "broke_chest.timestamp = (SELECT MIN(timestamp) FROM chests WHERE timestamp > placed_chest.timestamp) WHERE " +
    		" placed_chest.action = \"PLACE\" OR broke_chest.action = \"BREAK\";"
    );
    
    viewCreate.executeUpdate();
    
    // software engineering is dead
    PreparedStatement shmiewShmeate = connection.prepareStatement("CREATE VIEW IF NOT EXISTS chest_event_owned AS SELECT chest_events.id, chest_events.player, chest_events.x, chest_events.y, chest_events.z, chest_events.timestamp AS event_timestamp, action, block, `count`, chests.timestamp AS chest_placed_timestamp, chests.actor AS chest_owner FROM chest_events LEFT JOIN chests  ON chests.timestamp = (SELECT MAX(timestamp) FROM chests WHERE chest_events.x = x AND chest_events.y = y AND chest_events.z = z AND chests.action = \"PLACE\" AND chest_events.timestamp > timestamp)");
    shmiewShmeate.executeUpdate();
    
    		
  }
  
  public static void logInventoryEvent(String player, boolean pickup, Location loc, ItemStack stack) {
	  try {
		  PreparedStatement statement = connection.prepareStatement(
				  "INSERT INTO chest_events (player, x, y, z, block, count, event_type, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
		  
		  statement.setString(1, player);
		  statement.setInt(2, loc.getBlockX());
		  statement.setInt(3, loc.getBlockY());
		  statement.setInt(4, loc.getBlockZ());
		  statement.setString(5, stack.getType().getKey().toString());
		  statement.setInt(6, stack.getAmount());
		  statement.setString(7, pickup ? "PICKUP" : "PLACE");
		  statement.setLong(8, Instant.now().getEpochSecond());
		  
		  statement.executeUpdate();
		  
	  } catch (SQLException ex) {
		  logger.log(Level.SEVERE, "Error occured when inserting into chest_events");
	      ex.printStackTrace();
	  }
	  
  }
  
  public static void logBlockEvent(String player, String type, Location loc) {
	  try {
		  PreparedStatement statement = connection.prepareStatement(
				  "INSERT INTO chests (actor, action, x, y, z, timestamp) VALUES (?, ?, ?, ?, ?, ?)");
		  
		  statement.setString(1, player);
		  statement.setString(2, type);
		  statement.setInt(3, loc.getBlockX());
		  statement.setInt(4, loc.getBlockY());
		  statement.setInt(5, loc.getBlockZ());
		  statement.setLong(6, Instant.now().getEpochSecond());
		  
		  statement.executeUpdate();
		  
	  } catch (SQLException ex) {
		  logger.log(Level.SEVERE, "Error occured when inserting into chests");
		  ex.printStackTrace();
	  }
  }
}