package com.battlecraft.zonedabone.battlesnitch.controllers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.bukkit.ChatColor;
import org.bukkit.Material;
import org.bukkit.block.Block;
import org.bukkit.entity.Player;

import com.battlecraft.zonedabone.battlesnitch.BattleSnitch;

public class MYSQLController {

	public Connection con;

	final String new_table_snitches = "CREATE  TABLE `battlesnitch`.`snitches` ("
			+ "`idsnitches` INT NOT NULL AUTO_INCREMENT ,"
			+ "`x` INT NOT NULL ,"
			+ "`y` INT NOT NULL ,"
			+ "`z` INT NOT NULL ,"
			+ "`world` VARCHAR(45) NOT NULL ,"
			+ "PRIMARY KEY (`idsnitches`) );";

	final String new_table_blocks = "CREATE  TABLE `battlesnitch`.`blocks` ("
			+ "`idblocks` INT NOT NULL ," + "`x` INT NOT NULL ,"
			+ "`y` INT NOT NULL ," + "`z` INT NOT NULL ,"
			+ "`world` VARCHAR(45) NOT NULL ,"
			+ "PRIMARY KEY (`idblocks`,x,y,z,world) );";

	final String new_table_snitched = "CREATE  TABLE `battlesnitch`.`snitched` ("
			+ "`idsnitched` INT NOT NULL ,"
			+ "`player` VARCHAR(45) NOT NULL ,"
			+ "`time` MEDIUMTEXT  NULL ,"
			+ "PRIMARY KEY (`idsnitched`, `player`));";

	public boolean DEBUG;
	public String URL;
	public String PORT;
	public String USERNAME;
	public String PASSWORD;
	public BattleSnitch plugin;

	/**
	 * Initializes the mysql controller and calls load()
	 *
	 * @param url
	 *            the url for the databse
	 * @param port
	 *            the port for the database
	 * @param username
	 *            the username to use for the database
	 * @param password
	 *            the password to use for the database
	 * @param debug
	 *            whether or not to print debug statements
	 * @param instance
	 *            the BattleSnitch plugin instanciating this
	 */
	public MYSQLController(String url, String port, String username,
			String password, boolean debug, BattleSnitch instance) {
		DEBUG = debug;
		URL = url;
		PORT = port;
		USERNAME = username;
		PASSWORD = password;
		plugin = instance;
		load();
	}

	/**
	 * Loads the database and creates the database and tables. Do not run
	 * directly.
	 *
	 * @return
	 */
	public boolean load() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			if (DEBUG)
				System.out.println("Got Driver");
		} catch (ClassNotFoundException e1) {
			System.err.println("Failed getting driver");
			e1.printStackTrace();
			return false;
		}
		String strStmt = "CREATE DATABASE IF NOT EXISTS battlesnitch";
		try {
			con = DriverManager.getConnection("jdbc:mysql://" + URL + ":"
					+ PORT, USERNAME, PASSWORD);
			Statement st = con.createStatement();
			st.executeUpdate(strStmt);
			if (DEBUG)
				System.out.println("Creating db");
		} catch (SQLException e) {
			System.err.println("Failed creating db: " + strStmt);
			e.printStackTrace();
			return false;
		}
		try {
			con = DriverManager.getConnection("jdbc:mysql://" + URL + ":"
					+ PORT + "/battlesnitch", USERNAME, PASSWORD);
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		}

		createTable("desc snitches", new_table_snitches, null);
		createTable("desc blocks", new_table_blocks, null);
		createTable("desc snitched", new_table_snitched, null);
		try {
			con.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return true;
	}

	/**
	 * Creates a table with the specified index if none exists. Do not run
	 * directly.
	 *
	 * @param sql_table_exists
	 *            The query to execute to check if the table exists
	 * @param sql_create_table
	 *            The statement to execute to make the table
	 * @param sql_create_index
	 *            The statement to execute to make the index, or null to make
	 *            none
	 * @return
	 */
	private boolean createTable(String sql_table_exists,
			String sql_create_table, String sql_create_index) {
		String strStmt;
		strStmt = sql_table_exists;
		// / Check to see if our table exists;
		boolean table_exists = false;
		try {
			Statement st = con.createStatement();
			st.executeUpdate(strStmt);
			if (DEBUG)
				System.out.println("table exists");
			table_exists = true;
		} catch (SQLException e) {
			if (DEBUG)
				System.out.println("table does not exist");
		}
		// / If the table exists nothing left to do
		if (table_exists)
			return true;
		// / Create our table and index
		strStmt = sql_create_table;
		Statement st = null;
		int result = 0;
		try {
			st = con.createStatement();
			result = st.executeUpdate(strStmt);
			if (DEBUG)
				System.out.println("Created Table with stmt=" + strStmt);

			if (sql_create_index != null) {
				try {
					st = con.createStatement();
					st.executeUpdate(sql_create_index);
					if (DEBUG)
						System.out.println("Created Index");
				} catch (Exception e) {
					if (DEBUG)
						System.err.println("Failed in creating Index");
					return false;
				}
			}
		} catch (SQLException e) {
			if (DEBUG)
				System.err.println("Failed in creating Table " + strStmt
						+ "   result=" + result);
			e.printStackTrace();
			return false;
		}

		return true;
	}

	/**
	 * returns the database ID associated with a block, or 0 if none exists
	 *
	 * @param block
	 *            the block to look up in the database
	 * @return the database ID of that block
	 */
	public int getID(Block block) {
		int x = block.getX();
		int y = block.getY();
		int z = block.getZ();
		String world = block.getWorld().getName();
		try {
			PreparedStatement ps = con
					.prepareStatement("SELECT idsnitches FROM snitches WHERE x = ? AND y = ? AND z = ? and world = ?");
			ps.setInt(1, x);
			ps.setInt(2, y);
			ps.setInt(3, z);
			ps.setString(4, world);
			ResultSet rs = ps.executeQuery();
			if (!rs.next()) {
				return 0;
			}
			return (rs.getInt("idsnitches"));
		} catch (SQLException e) {
			System.out.println("ERROR");
			e.printStackTrace();
			return 0;
		}
	}

	/**
	 * Creates the database records for a given block. Will wipe old records for
	 * this block.
	 *
	 * @param block
	 *            The block to create the records for
	 * @return
	 */
	public boolean makeSnitch(Block block) {
		if (getID(block) == 0) {
			int x = block.getX();
			int y = block.getY();
			int z = block.getZ();
			String world = block.getWorld().getName();
			try {
				PreparedStatement ps = con
						.prepareStatement("INSERT INTO snitches (x,y,z,world) VALUES (?,?,?,?)");
				ps.setInt(1, x);
				ps.setInt(2, y);
				ps.setInt(3, z);
				ps.setString(4, world);
				ps.execute();
				con.commit();
				int id = getID(block);
				for (int xs = x - 5; xs <= x + 5; xs++) {
					for (int ys = y - 5; ys <= y + 5; ys++) {
						for (int zs = z - 5; zs <= z + 5; zs++) {
							ps = con.prepareStatement("INSERT INTO blocks (idblocks,x,y,z,world) VALUES (?,?,?,?,?)");
							ps.setInt(1, id);
							ps.setInt(2, xs);
							ps.setInt(3, ys);
							ps.setInt(4, zs);
							ps.setString(5, world);
							ps.execute();
						}
					}
				}
				con.commit();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		} else {
			breakSnitch(block);
			return makeSnitch(block);
		}
	}

	/**
	 * removes records for a given block
	 *
	 * @param block
	 *            the block to remove records from
	 * @return
	 */
	public boolean breakSnitch(Block block) {
		int id = getID(block);
		if (id != 0) {
			try {
				PreparedStatement ps = con
						.prepareStatement("DELETE FROM blocks WHERE idblocks = ?");
				ps.setInt(1, id);
				ps.execute();
				ps = con.prepareStatement("DELETE FROM snitched WHERE idsnitched = ?");
				ps.setInt(1, id);
				ps.execute();
				ps = con.prepareStatement("DELETE FROM snitches WHERE idsnitches = ?");
				ps.setInt(1, id);
				ps.execute();
				con.commit();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		} else {
			return false;
		}
	}

	/**
	 * check if a player is in a snitch zone in a certain block, and if so
	 * records it
	 *
	 * @param player
	 *            the name of the player to check
	 * @param block
	 *            the block the player's head is in
	 */
	public void checkBlock(String player, Block block) {
		int x = block.getX();
		int y = block.getY();
		int z = block.getZ();
		String world = block.getWorld().getName();
		try {
			PreparedStatement ps = con
					.prepareStatement("SELECT idblocks FROM blocks WHERE x=? and y=? and z=? and world=?");
			ps.setInt(1, x);
			ps.setInt(2, y);
			ps.setInt(3, z);
			ps.setString(4, world);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ps = con.prepareStatement("INSERT INTO snitched VALUES (?,?,?) ON DUPLICATE KEY UPDATE time=?");
				ps.setInt(1, rs.getInt(1));
				ps.setString(2, player);
				ps.setLong(3, System.currentTimeMillis());
				ps.setLong(4, System.currentTimeMillis());
				ps.execute();
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
	}

	/**
	 * Shows a player the history of a given block
	 *
	 * @param player
	 *            the player to check for
	 * @param block
	 *            the block to check
	 */
	public void checkHistory(Player player, Block block) {
		int id = getID(block);
		if (id != 0) {
			try {
				PreparedStatement ps = con
						.prepareStatement("SELECT count(*) FROM snitched WHERE idsnitched = ?");
				ps.setInt(1, id);
				ResultSet rs = ps.executeQuery();
				rs.next();
				if (rs.getInt(1) == 0) {
					player.sendMessage(ChatColor.GOLD
							+ "Nobody has been here in the past week.");
				} else {
					if (rs.getInt(1) == 1) {
						player.sendMessage(ChatColor.GOLD
								+ "1 person has been here in the past week:");
					} else {
						player.sendMessage(ChatColor.GOLD + "" + rs.getInt(1)
								+ " people have been here in the past week:");
					}
					ps = con.prepareStatement("SELECT player FROM snitched WHERE idsnitched = ?");
					ps.setInt(1, id);
					rs = ps.executeQuery();
					while (rs.next()) {
						player.sendMessage(ChatColor.GOLD + rs.getString(1));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
				return;
			}
		} else {
			makeSnitch(block);
			player.sendMessage(ChatColor.GOLD
					+ "This jukebox is now a snitch block.");
		}
	}

	/**
	 * Cleans out records older than a week
	 */
	public void cleanRecords() {
		try {
			PreparedStatement ps = con
					.prepareStatement("DELETE FROM snitched WHERE time < ?");
			ps.setLong(1, System.currentTimeMillis() - 604800000);
			ps.execute();
		} catch (SQLException e) {
			return;
		}
	}
}