package org.ultramine.mods.hawkeye.database;

import gnu.trove.map.TIntObjectMap;
import gnu.trove.map.hash.TIntObjectHashMap;
import net.minecraftforge.common.MinecraftForge;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Queue;
import java.util.Timer;
import java.util.TimerTask;

import org.ultramine.mods.hawkeye.DataType;
import org.ultramine.mods.hawkeye.HawkEye;
import org.ultramine.mods.hawkeye.entry.DataEntry;
import org.ultramine.mods.hawkeye.util.HawkUtil;
import org.ultramine.server.ConfigurationHandler;
import org.ultramine.server.UltramineServerConfig.DatabaseConf;
import org.ultramine.server.util.GlobalExecutors;

import com.google.common.collect.Queues;

import cpw.mods.fml.common.eventhandler.EventPriority;
import cpw.mods.fml.common.eventhandler.SubscribeEvent;

/**
 * Handler for everything to do with the database. All queries except searching
 * goes through this class.
 * 
 * @author oliverw92
 */
public class DataManager extends TimerTask
{
	private static final Queue<DataEntry> queue = Queues.newConcurrentLinkedQueue();
	private static ConnectionManager connections;
	public static Timer loggingTimer = null;
	public static Timer cleanseTimer = null;
	public static final HashMap<String, Integer> dbPlayers = new HashMap<String, Integer>();
	private static final TIntObjectMap<String> dbPlayersBack = new TIntObjectHashMap<String>();

	/**
	 * Initiates database connection pool, checks tables, starts cleansing
	 * utility Throws an exception if it is unable to complete setup
	 * 
	 * @param instance
	 * @throws Exception
	 */
	public DataManager(HawkEye instance) throws Exception
	{
		DatabaseConf db = ConfigurationHandler.getServerConfig().databases.get(HawkEye.instance.config.database.database);
		if(db == null)
			throw new Exception("Database not found");
		connections = new ConnectionManager(db.url, db.username, db.password, db.maxConnections);
		getConnection().close();

		//Check tables and update player/world lists
		if(!checkTables())
			throw new Exception();
		if(!updateDbLists())
			throw new Exception();

		//Start cleansing utility
		try
		{
			new CleanseUtil();
		}
		catch(Exception e)
		{
			HawkUtil.error(e.getMessage());
			HawkUtil.error("Unable to start cleansing utility - check your cleanse age");
		}

		//Start logging timer
		loggingTimer = new Timer();
		loggingTimer.scheduleAtFixedRate(this, 2000, 2000);
		MinecraftForge.EVENT_BUS.register(this);
	}

	/**
	 * Closes down all connections
	 */
	public static void close()
	{
		connections.close();
		if(cleanseTimer != null)
			cleanseTimer.cancel();
		if(loggingTimer != null)
			loggingTimer.cancel();
	}
	
	public static void addEntry(DataEntry entry)
	{
//		MinecraftForge.EVENT_BUS.post(entry);
		handleDataEntry(entry);
	}

//	@SubscribeEvent(priority = EventPriority.LOWEST)
	public static void handleDataEntry(DataEntry entry)
	{
		if(!HawkEye.instance.config.isLogged(entry.getType()))
			return;

		//Check world ignore list
		if(HawkEye.instance.config.ignoreWorlds.contains(entry.getWorld()))
			return;

		queue.add(entry);
	}

	/**
	 * Retrieves an entry from the database
	 * 
	 * @param id
	 *            id of entry to return
	 * @return
	 */
	public static DataEntry getEntry(int id)
	{
		JDCConnection conn = null;
		try
		{
			conn = getConnection();
			ResultSet res = conn.createStatement().executeQuery("SELECT * FROM `" + HawkEye.instance.config.database.dataTable + "` WHERE `data_id` = " + id);
			res.next();
			return createEntryFromRes(res, false);
		}
		catch(Exception ex)
		{
			HawkUtil.error("Unable to retrieve data entry from MySQL Server: " + ex);
		}
		finally
		{
			conn.close();
		}
		return null;
	}

	/**
	 * Deletes an entry from the database
	 * 
	 * @param dataid
	 *            id to delete
	 */
	public static void deleteEntry(long dataid)
	{
		GlobalExecutors.cachedIO().execute(new DeleteEntry(dataid));
	}

	public static void deleteEntries(List<?> entries)
	{
		GlobalExecutors.cachedIO().execute(new DeleteEntry(entries));
	}

	/**
	 * Get a players name from the database player list
	 * 
	 * @param id
	 * @return player name
	 */
	public static String getPlayer(int id)
	{
		return dbPlayersBack.get(id);
	}

	/**
	 * Returns a database connection from the pool
	 * 
	 * @return {JDCConnection}
	 */
	public static JDCConnection getConnection()
	{
		try
		{
			return connections.getConnection();
		}
		catch(final SQLException ex)
		{
			HawkUtil.error("Error whilst attempting to get connection: " + ex);
			return null;
		}
	}

	/**
	 * Creates a {@link DataEntry} from the inputted {ResultSet}
	 * 
	 * @param res
	 * @return returns a {@link DataEntry}
	 * @throws SQLException
	 */
	public static DataEntry createEntryFromRes(ResultSet res, boolean subdata) throws Exception
	{
		DataType type = DataType.fromId(res.getInt("action"));
		DataEntry entry = (DataEntry) type.getEntryClass().newInstance();
		String player = DataManager.getPlayer(res.getInt("player_id"));
		entry.setPlayer(player == null ? "[#null]" : player);
		entry.setDate(res.getTimestamp("date").getTime());
		entry.setDataId(res.getLong("data_id"));
		entry.setType(DataType.fromId(res.getInt("action")));
		entry.interpretSqlData(res.getString("data"));
		entry.setWorld(res.getInt("world_id"));
		entry.setX(res.getInt("x"));
		entry.setY(res.getInt("y"));
		entry.setZ(res.getInt("z"));
		if(subdata)
			entry.setSubData(res.getBytes("subdata"));
		return entry;
	}

	/**
	 * Adds a player to the database
	 */
	private boolean addPlayer(String name)
	{
		name = name.toLowerCase();
		JDCConnection conn = null;
		PreparedStatement stmnt = null;
		ResultSet keys = null;
		try
		{
			HawkUtil.debug("Attempting to add player '" + name + "' to database");
			conn = getConnection();
			stmnt = conn.prepareStatement("INSERT IGNORE INTO `" + HawkEye.instance.config.database.playerTable + "` (player) VALUES ('" + name + "');", Statement.RETURN_GENERATED_KEYS);
			stmnt.executeUpdate();
			keys = stmnt.getGeneratedKeys();

			if(!keys.next())
				return false; //impossible??
			int id = keys.getInt(1);
			
			dbPlayers.put(name, id);
			dbPlayersBack.put(id, name);
		}
		catch(SQLException ex)
		{
			HawkUtil.error("Unable to add player to database: " + ex);
			return false;
		}
		finally
		{
			if(keys != null)
				try {
					keys.close();
				} catch(SQLException ignored){}
			if(stmnt != null)
				try {
					stmnt.close();
				} catch(SQLException ignored){}
			conn.close();
		}
		return true;
	}

	/**
	 * Updates world and player local lists
	 * 
	 * @return true on success, false on failure
	 */
	private boolean updateDbLists()
	{
		JDCConnection conn = null;
		Statement stmnt = null;
		try
		{
			conn = getConnection();
			stmnt = conn.createStatement();
			ResultSet res = stmnt.executeQuery("SELECT * FROM `" + HawkEye.instance.config.database.playerTable + "`;");
			while(res.next())
			{
				String name = res.getString("player").toLowerCase();
				int id = res.getInt("player_id");
				dbPlayers.put(name, id);
				dbPlayersBack.put(id, name);
			}
		}
		catch(SQLException ex)
		{
			HawkUtil.error("Unable to update local data lists from database: ", ex);
			return false;
		}
		finally
		{
			try
			{
				if(stmnt != null)
					stmnt.close();
				conn.close();
			}
			catch(SQLException ex)
			{
				HawkUtil.error("Unable to close SQL connection: ", ex);
			}

		}
		return true;
	}

	/**
	 * Checks that all tables are up to date and exist
	 * 
	 * @return true on success, false on failure
	 */
	private boolean checkTables()
	{
		JDCConnection conn = null;
		Statement stmnt = null;
		try
		{
			conn = getConnection();
			stmnt = conn.createStatement();
			DatabaseMetaData dbm = conn.getMetaData();

			//Check if tables exist
			if(!JDBCUtil.tableExists(dbm, HawkEye.instance.config.database.playerTable))
			{
				HawkUtil.info("Table `" + HawkEye.instance.config.database.playerTable + "` not found, creating...");
				stmnt.execute(
						"CREATE TABLE IF NOT EXISTS `" + HawkEye.instance.config.database.playerTable
						+ "` (`player_id` int(11) NOT NULL AUTO_INCREMENT, "
						+ "`player` varchar(255) NOT NULL, "
						+ "PRIMARY KEY (`player_id`), UNIQUE KEY `player` (`player`) );");
			}
			if(!JDBCUtil.tableExists(dbm, HawkEye.instance.config.database.dataTable))
			{
				HawkUtil.info("Table `" + HawkEye.instance.config.database.dataTable + "` not found, creating...");
				stmnt.execute(
						"CREATE TABLE IF NOT EXISTS `"
						+ HawkEye.instance.config.database.dataTable
						+ "` (`data_id` int(11) NOT NULL AUTO_INCREMENT, "
						+ "`date` timestamp NOT NULL, "
						+ "`player_id` int(11) NOT NULL, "
						+ "`action` int(11) NOT NULL, "
						+ "`world_id` int(11) NOT NULL, "
						+ "`x` double NOT NULL, "
						+ "`y` double NOT NULL, "
						+ "`z` double NOT NULL, "
						+ "`data` varchar(500) DEFAULT NULL, "
						+ "PRIMARY KEY (`data_id`), KEY `player_action_world` (`player_id`,`action`,`world_id`), KEY `x_y_z` (`x`,`y`,`z` ));");
			}
			//
			if(!JDBCUtil.tableExists(dbm, HawkEye.instance.config.database.subdataTable))
			{
				HawkUtil.info("Table `" + HawkEye.instance.config.database.subdataTable + "` not found, creating...");
				stmnt.execute(
						"CREATE TABLE IF NOT EXISTS `"
						+ HawkEye.instance.config.database.subdataTable
						+ "` (`data_id` int(11) NOT NULL, "
						+ "`subdata` blob NOT NULL, "
						+ "PRIMARY KEY (`data_id`));");
			}
		}
		catch(SQLException ex)
		{
			HawkUtil.error("Error checking HawkEye tables: " + ex);
			return false;
		}
		finally
		{
			try
			{
				if(stmnt != null)
					stmnt.close();
				conn.close();
			}
			catch(SQLException ex)
			{
				HawkUtil.error("Unable to close SQL connection: " + ex);
			}

		}
		return true;
	}

	/**
	 * Empty the {@link DataEntry} queue into the database
	 */
	@Override
	public void run()
	{
		if(queue.isEmpty())
			return;
		JDCConnection conn = getConnection();
		PreparedStatement st1 = null;
		PreparedStatement st2 = null;
		PreparedStatement st3 = null;
		PreparedStatement st4 = null;
		PreparedStatement st5 = null;
		try
		{
			String dtable = HawkEye.instance.config.database.dataTable;
//			st1 = conn.prepareStatement("INSERT INTO `"+dtable+"` (date, player_id, action, world_id, x, y, z, data, data_id) VALUES (?,?,?,?,?,?,?,?,?);", Statement.NO_GENERATED_KEYS);
//			st2 = conn.prepareStatement("INSERT INTO `"+dtable+"` (date, player_id, action, world_id, x, y, z, data, data_id) VALUES (?,?,?,?,?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS);
			st3 = conn.prepareStatement("INSERT INTO `"+dtable+"` (date, player_id, action, world_id, x, y, z, data) VALUES (?,?,?,?,?,?,?,?);", Statement.NO_GENERATED_KEYS);
			st4 = conn.prepareStatement("INSERT INTO `"+dtable+"` (date, player_id, action, world_id, x, y, z, data) VALUES (?,?,?,?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS);
			st5 = conn.prepareStatement("INSERT INTO `" + HawkEye.instance.config.database.subdataTable + "` (`data_id`, `subdata`) VALUES (?, ?);");
			while(!queue.isEmpty())
			{
				DataEntry entry = queue.poll();

				//Sort out player IDs
				String player = entry.getPlayer().toLowerCase();
				if(!dbPlayers.containsKey(player) && !addPlayer(player))
				{
					HawkUtil.debug("Player '" + player + "' not found, skipping entry");
					continue;
				}

				//If player ID is unable to be found, continue
				if(player == null || dbPlayers.get(player) == null)
				{
					HawkUtil.debug("No player found, skipping entry");
					continue;
				}

				//
				byte[] data = entry.getSubData();
				boolean usedata = HawkEye.instance.config.general.logBlocksSubData && data != null;
				//
				
				PreparedStatement st;
				//If we are re-inserting we need to also insert the data ID
				if(entry.getDataId() > 0)
				{
					throw new UnsupportedOperationException();
//					st = usedata ? st2 : st1;
//					st.setInt(9, entry.getDataId());
				}
				else
				{
					st = usedata ? st4 : st3;
				}
				st.setTimestamp(1, new Timestamp(entry.getDate()));
				st.setInt(2, dbPlayers.get(player));
				st.setInt(3, entry.getType().getId());
				st.setInt(4, entry.getWorld());
				st.setDouble(5, entry.getX());
				st.setDouble(6, entry.getY());
				st.setDouble(7, entry.getZ());
				st.setString(8, entry.getSqlData());
				st.executeUpdate();

				if(usedata)
				{
					ResultSet keys = st.getGeneratedKeys();

					if(!keys.next())
						continue; //impossible??
					long key = keys.getLong(1);
					keys.close();

					st5.setLong(1, key);
					st5.setBytes(2, data);
					st5.executeUpdate();
				}
			}
		}
		catch(Exception ex)
		{
			HawkUtil.error("Exception in data execution", ex);
		}
		finally
		{
			try
			{
				if(st1 != null) st1.close();
				if(st2 != null) st2.close();
				if(st3 != null) st3.close();
				if(st4 != null) st4.close();
				if(st5 != null) st5.close();
			}
			catch(Exception ex)
			{
				HawkUtil.error("Unable to close SQL statement: ", ex);
			}
			
			try
			{
				if(conn != null)
					conn.close();
			}
			catch(Exception ex)
			{
				HawkUtil.error("Unable to close SQL connection: ", ex);
			}
		}
	}
}
