package net.sf.l2j.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Logger;

import net.sf.l2j.gameserver.model.L2ItemInstance;
import net.sf.l2j.Config;
import net.sf.l2j.L2DatabaseFactory;

public final class SQLHandler
{
	private static final String SELECT_REMOVED_ITEMS_SQL = "SELECT object_id, count FROM items WHERE object_id = ?";
	private static final String DELETE_REMOVED_ITEMS_SQL = "DELETE FROM items WHERE object_id = ?";
	private static final String UPDATE_REMOVED_ITEMS_SQL = "UPDATE items SET count = ? WHERE object_id = ?";
	private static final String INSERT_NEW_ITEMS_SQL = "INSERT INTO items VALUES (?,?,?,?,?,?,?,?,?,null,0,0)";
	
    private static Logger _log = Logger.getLogger(SQLHandler.class.getName());

    public boolean removeItems (List<L2ItemInstance> removeItems)
	{	
		long beginTime = System.currentTimeMillis();
		Connection con = null;
		
		try
		{		
			con = L2DatabaseFactory.getInstance().getConnection();

			con.setAutoCommit(false);
			for (Iterator<L2ItemInstance> it = removeItems.iterator(); it.hasNext(); )
			{
				L2ItemInstance item = it.next();

				PreparedStatement statement = con.prepareStatement(SELECT_REMOVED_ITEMS_SQL);
				statement.setInt(1, item.getObjectId());
				ResultSet res = statement.executeQuery();
				if (res.next())
				{
					if (res.getInt("count") == item.getCount())
					{
						PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
						statement2.setInt(1, item.getObjectId());
						statement2.execute();
					}
					else
					{
						PreparedStatement statement2 = con.prepareStatement(UPDATE_REMOVED_ITEMS_SQL);
						statement2.setInt(1, item.getCount());
						statement2.setInt(2, item.getObjectId());
						statement2.execute();
					}
				}
				else
				{
					PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
					statement2.setInt(1, item.getObjectId());
					statement2.execute();
				}
			}
			con.commit();
		}
		catch (Exception e1)
		{
			try
			{
				if (con != null)
					con.rollback();
			}
			catch (SQLException e2)
			{
				_log.warning("Unable to rollback transaction!");
				return false;
			}
			_log.warning("Error removing Objects from DB. Transaction rollback.");
			return false;
		}
		finally
		{
			try
			{
				if (con != null)
					con.close();
			}
			catch (SQLException e3)
			{
				_log.warning("Unable to close connection!");
				return false;
			}
			validateExecutionTime(beginTime);
		}
		return true;
	}

    public boolean addItems (List<L2ItemInstance> addItems)
	{	
		long beginTime = System.currentTimeMillis();
		Connection con = null;
		
		try
		{		
			con = L2DatabaseFactory.getInstance().getConnection();

			con.setAutoCommit(false);
			for (Iterator<L2ItemInstance> it = addItems.iterator(); it.hasNext(); )
			{
				L2ItemInstance item = it.next();

				PreparedStatement statement = con.prepareStatement(INSERT_NEW_ITEMS_SQL);
				statement.setInt(1, item.getOwnerId());
				statement.setInt(2, item.getObjectId());
				statement.setInt(3, item.getItemId());
				statement.setInt(4, item.getCount());
				statement.setInt(5, item.getEnchantLevel());
				statement.setString(6, item.getLocation().toString());
				statement.setInt(7, item.getEquipSlot());
				statement.setInt(8, item.getPriceToSell());
				statement.setInt(9, item.getPriceToBuy());
				statement.execute();
			}
			con.commit();
		}
		catch (Exception e1)
		{
			try
			{
				if (con != null)
					con.rollback();
			}
			catch (SQLException e2)
			{
				_log.warning("Unable to rollback transaction!");
				return false;
			}
			_log.warning("Error inserting Objects into DB. Transaction rollback.");
			return false;
		}
		finally
		{
			try
			{
				if (con != null)
					con.close();
			}
			catch (SQLException e3)
			{
				_log.warning("Unable to close connection!");
				return false;
			}
			validateExecutionTime(beginTime);
		}
		return true;
	}
    
    public boolean exchangeItems(List<L2ItemInstance> addItems, List<L2ItemInstance> removeItems)
    {
		long beginTime = System.currentTimeMillis();
		Connection con = null;
		
		try
		{		
			con = L2DatabaseFactory.getInstance().getConnection();

			con.setAutoCommit(false);
			for (Iterator<L2ItemInstance> it = removeItems.iterator(); it.hasNext(); )
			{
				L2ItemInstance item = it.next();

				PreparedStatement statement = con.prepareStatement(SELECT_REMOVED_ITEMS_SQL);
				statement.setInt(1, item.getObjectId());
				ResultSet res = statement.executeQuery();
				if (res.next())
				{
					if (res.getInt("count") == item.getCount())
					{
						PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
						statement2.setInt(1, item.getObjectId());
						statement2.execute();
					}
					else
					{
						PreparedStatement statement2 = con.prepareStatement(UPDATE_REMOVED_ITEMS_SQL);
						statement2.setInt(1, item.getCount());
						statement2.setInt(2, item.getObjectId());
						statement2.execute();
					}
				}
				else
				{
					PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
					statement2.setInt(1, item.getObjectId());
					statement2.execute();
				}
			}

			for (Iterator<L2ItemInstance> it = addItems.iterator(); it.hasNext(); )
			{
				L2ItemInstance item = it.next();

				PreparedStatement statement = con.prepareStatement(INSERT_NEW_ITEMS_SQL);
				statement.setInt(1, item.getOwnerId());
				statement.setInt(2, item.getObjectId());
				statement.setInt(3, item.getItemId());
				statement.setInt(4, item.getCount());
				statement.setInt(5, item.getEnchantLevel());
				statement.setString(6, item.getLocation().toString());
				statement.setInt(7, item.getEquipSlot());
				statement.setInt(8, item.getPriceToSell());
				statement.setInt(9, item.getPriceToBuy());
				statement.execute();
			}
			con.commit();
		}
		catch (Exception e1)
		{
			try
			{
				if (con != null)
					con.rollback();
			}
			catch (SQLException e2)
			{
				_log.warning("Unable to rollback transaction!");
				return false;
			}
			_log.warning("Error exchanging Objects in DB. Transaction rollback.");
			return false;
		}
		finally
		{
			try
			{
				if (con != null)
					con.close();
			}
			catch (SQLException e3)
			{
				_log.warning("Unable to close connection!");
				return false;
			}
			validateExecutionTime(beginTime);
		}
		return true;    }
    
    private void validateExecutionTime(long beginTime)

	{
    	long endTime=System.currentTimeMillis();
    	
    	if(Config.SQL_LOG_EXEC_TIME_MAX <= (endTime - beginTime))
    	{
    		if (Config.DEBUG)   // this part dumps data if we want it (DEBUG==True)
    		{
    			_log.warning("exchangeItems() method Execution start: " + new SimpleDateFormat("HH:mm:ss:SSS").format(beginTime));
    			_log.warning("exchangeItems() method Execution start: " + new SimpleDateFormat("HH:mm:ss:SSS").format(endTime));
    			_log.warning("Command Execution Time: " + (endTime - beginTime) + " milliseconds.");
    		}
    		
    		// and here we can add some serious handling if we need to 
    		// (like reinitializing C3P0 or some in-depth analysys of why connections took so long to execute)
    	}
	}
}