package com.jens.rhasspy.shoppinglist;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;

public class ShoppingListEntry
{
	ShoppingList parentList;
	Product product;
	float amount;
	Unit unit;
	
	public ShoppingList getParentList()
	{
		return parentList;
	}

	public void setParentList(ShoppingList parentList)
	{
		this.parentList = parentList;
	}

	public Product getProduct()
	{
		return product;
	}

	public void setProduct(Product product)
	{
		this.product = product;
	}

	public float getAmount()
	{
		return amount;
	}

	public void setAmount(float amount)
	{
		this.amount = amount;
	}

	public Unit getUnit()
	{
		return unit;
	}

	public void setUnit(Unit unit)
	{
		this.unit = unit;
	}

	public boolean create()
	{	
		PreparedStatement preparedStmt = null;
		Connection conn = null;
		try
		{			
			if(amount <= 0 && unit != null && !unit.isDummy)
				return false;
			
			conn = DatabaseHandler.getInstance().getConnection();
			
			if(!combinationExists(this.getParentList(), this.getProduct(), this.getUnit()))
			{
				String parentQuery = "INSERT INTO listEntries (listId, productId, amount, unit) VALUES (?, ?, ?, ?)";
				preparedStmt = conn.prepareStatement(parentQuery, Statement.RETURN_GENERATED_KEYS);
				
				preparedStmt.setLong(1, this.getParentList().getId());
				preparedStmt.setLong(2, this.getProduct().getId());
				preparedStmt.setFloat(3, this.getAmount());
				preparedStmt.setLong(4, this.getUnit().getId());
	
				Miscellaneous.logEvent(preparedStmt.toString(), 5);
				
				long numAffectedRows = preparedStmt.executeUpdate();
			    Miscellaneous.logEvent("AMOUNT OF UPDATED ROWS: " + numAffectedRows, 5);
			    ResultSet rs = preparedStmt.getGeneratedKeys();
			    if (numAffectedRows > 0)
			    {
			    	rs.close();
					preparedStmt.close();
					return true;
			    }
			}
			else
			{
				return updateAmount(false);
			}
		}
		catch(Exception e)
		{
			Miscellaneous.logEvent(Diverse.getStackTraceAsString(e), 1);
		}
		finally
		{
			try
			{
				if(preparedStmt != null && !preparedStmt.isClosed())
					preparedStmt.close();
			}
			catch (SQLException e)
			{
			}
		}
		
		return false;
	}

	public boolean delete()
	{	
		PreparedStatement preparedStmt = null;
		Connection conn = null;
		try
		{			
			if(amount <= 0 && unit != null && !unit.isDummy)
				return false;
			
			conn = DatabaseHandler.getInstance().getConnection();
			
			if(this.getAmount() == 0 || this.getUnit().isDummy())
			{
				String parentQuery = "DELETE FROM listEntries WHERE listId=? AND productId=?";
				preparedStmt = conn.prepareStatement(parentQuery, Statement.RETURN_GENERATED_KEYS);
				
				preparedStmt.setLong(1, this.getParentList().getId());
				preparedStmt.setLong(2, this.getProduct().getId());
	
				Miscellaneous.logEvent(preparedStmt.toString(), 5);
				
				long numAffectedRows = preparedStmt.executeUpdate();
			    Miscellaneous.logEvent("AMOUNT OF UPDATED ROWS: " + numAffectedRows, 5);
//			    ResultSet rs = preparedStmt.getGeneratedKeys();
	//		    if (numAffectedRows > 0)
	//		    {
					preparedStmt.close();
					return true;
	//		    }
			}
			else
			{
				if(combinationExists(this.getParentList(), this.getProduct(), this.getUnit()))
					return updateAmount(true);
				else
					return true;	// Combination to be deleted does not exist. So what?
			}
		}
		catch(Exception e)
		{
			Miscellaneous.logEvent(Diverse.getStackTraceAsString(e), 1);
		}
		finally
		{
			try
			{
				if(preparedStmt != null && !preparedStmt.isClosed())
					preparedStmt.close();
			}
			catch (SQLException e)
			{
			}
		}
		
		return false;
	}

	protected boolean updateAmount(boolean delete)
	{
		String query = "SELECT * FROM listEntries WHERE listId=? AND productId=? AND unit=?";
		
		try
		{
			Connection conn = DatabaseHandler.getInstance().getConnection();
			
			PreparedStatement preparedStmt = conn.prepareStatement(query);
			preparedStmt.setLong(1, this.getParentList().getId());
			preparedStmt.setLong(2, this.getProduct().getId());
			preparedStmt.setLong(3, this.getUnit().getId());
			
			Miscellaneous.logEvent(preparedStmt.toString(), 5);
			
	        ResultSet res = preparedStmt.executeQuery();
	        
	        if(res.next())
	        {
	        	float currentAmount = res.getFloat("amount");
	        	
	        	boolean condition = true;
	        	if(delete)
	        		condition = currentAmount - this.getAmount() > 0;
	        		
	        	if(condition)
	        	{
	        		// Update the amount
	        		String updateQuery  = "UPDATE listEntries set amount=? WHERE listId=? AND productId=? AND unit=?";
					preparedStmt = conn.prepareStatement(updateQuery);
					
					if(delete)
						preparedStmt.setFloat(1, currentAmount - this.getAmount());
					else
						preparedStmt.setFloat(1, currentAmount + this.getAmount());

					preparedStmt.setLong(2, this.getParentList().getId());
					preparedStmt.setLong(3, this.getProduct().getId());
					preparedStmt.setLong(4, this.getUnit().getId());
		
					Miscellaneous.logEvent(preparedStmt.toString(), 5);
					
					long numAffectedRows = preparedStmt.executeUpdate();
				    Miscellaneous.logEvent("AMOUNT OF UPDATED ROWS: " + numAffectedRows, 5);
//				    ResultSet rs = preparedStmt.getGeneratedKeys();
				    if (numAffectedRows > 0)
				    {
				    	Miscellaneous.logEvent("Amount added to existing entry.", 2);
//				    	rs.close();
						preparedStmt.close();
						return true;
				    }					    
	        	}
	        	else
	        	{
	        		// Resulting sum is < 0 -> Remove the entry completely
	        		String updateQuery  = "DELETE FROM listEntries WHERE listId=? AND productId=? AND unit=?";
					preparedStmt = conn.prepareStatement(updateQuery);
					
					preparedStmt.setLong(1, this.getParentList().getId());
					preparedStmt.setLong(2, this.getProduct().getId());
					preparedStmt.setLong(3, this.getUnit().getId());
		
					Miscellaneous.logEvent(preparedStmt.toString(), 5);
					
					long numAffectedRows = preparedStmt.executeUpdate();
				    Miscellaneous.logEvent("AMOUNT OF UPDATED ROWS: " + numAffectedRows, 5);
//				    ResultSet rs = preparedStmt.getGeneratedKeys();
				    if (numAffectedRows > 0)
				    {
				    	Miscellaneous.logEvent("Resulting amount < 0, entry deleted.", 2);
//				    	rs.close();
						preparedStmt.close();
						return true;
				    }
				    else
						preparedStmt.close();
	        	}
	        }
	        
	        res.close();
		}
		catch(Exception e)
		{
			Miscellaneous.logEvent(Diverse.getStackTraceAsString(e), 2);
		}
		
		return false;
	}

	private boolean combinationExists(ShoppingList parentList, Product product, Unit unit)
	{
		String query = "SELECT count(listId) as combinationAmount FROM listEntries WHERE listId=? AND productId=? AND unit=?";
		long entryAmount = 0;
		
		try
		{
			PreparedStatement preparedStmt = DatabaseHandler.getInstance().getConnection().prepareStatement(query);
			preparedStmt.setLong(1, parentList.getId());
			preparedStmt.setLong(2, product.getId());
			preparedStmt.setLong(3, unit.getId());
			
			Miscellaneous.logEvent(preparedStmt.toString(), 5);
			
	        ResultSet res = preparedStmt.executeQuery();
	        
	        if(res.next())
	        	entryAmount = res.getLong("combinationAmount");
	        
	        res.close();
	        preparedStmt.close();
		}
		catch(Exception e)
		{
			Miscellaneous.logEvent(Diverse.getStackTraceAsString(e), 1);
		}
		
		if(entryAmount > 0)
			return true;
		
		return false;
	}

	@Override
	public String toString()
	{
		if(this.getUnit() != null)
		{
			if(!this.getUnit().isDummy())
				return String.valueOf(getAmount()) + " " + getUnit().getMainName() + " " + getProduct().getName();
		}
		
		return getProduct().getName();
	}
	
	
}
