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.ArrayList;
import java.util.Calendar;

public class ShoppingList
{
	long id;
	Calendar creationTime;
	String comment;
	ArrayList<ShoppingListEntry> entries = new ArrayList<>();	
	
	public long getId()
	{
		return id;
	}
	public void setId(long id)
	{
		this.id = id;
	}
	public Calendar getCreationTime()
	{
		return creationTime;
	}
	public void setCreationTime(Calendar creationTime)
	{
		this.creationTime = creationTime;
	}
	public String getComment()
	{
		return comment;
	}
	public void setComment(String comment)
	{
		this.comment = comment;
	}

	public boolean create()
	{	
		PreparedStatement preparedStmt = null;
		Connection conn = null;
		try
		{
			conn = DatabaseHandler.getInstance().getConnection();
			
			String parentQuery = "INSERT INTO lists (id, creationTime, comment) VALUES (?, ?, ?)";
			preparedStmt = conn.prepareStatement(parentQuery, Statement.RETURN_GENERATED_KEYS);
			
			preparedStmt.setLong(1, this.getId());
			preparedStmt.setLong(2, this.getCreationTime().getTimeInMillis());
			if(comment != null)
				preparedStmt.setString(3, this.getComment());
			else
				preparedStmt.setNull(3, java.sql.Types.VARCHAR);

			Miscellaneous.logEvent(preparedStmt.toString(), 5);
			
			long numAffectedRows = preparedStmt.executeUpdate();
		    Miscellaneous.logEvent("AMOUNT OF UPDATED ROWS: " + numAffectedRows, 5);
		    ResultSet rs = preparedStmt.getGeneratedKeys();
		    if (numAffectedRows > 0)
		    {
		    	if (rs.next())
			    {
			    	this.setId(rs.getInt(1));
					rs.close();
			    	Miscellaneous.logEvent("INSERT-ID: " + String.valueOf(this.getId()), 5);
					preparedStmt.close();
					return true;
			    }
		    }
		}
		catch(Exception e)
		{
			Miscellaneous.logEvent(Diverse.getStackTraceAsString(e), 1);
		}
		finally
		{
			try
			{
				if(preparedStmt != null && !preparedStmt.isClosed())
					preparedStmt.close();
			}
			catch (SQLException e)
			{
			}
		}
		
		return false;
	}
	
	public static ShoppingList getMostRecentList()
	{
		ShoppingList resultList = null;
		
		PreparedStatement preparedStmt = null;
		try
		{
			Connection conn = DatabaseHandler.getInstance().getConnection();
	
			String query = "SELECT * FROM lists ORDER BY creationTime DESC LIMIT 0,1";
			
			preparedStmt = conn.prepareStatement(query);
			
			Miscellaneous.logEvent(preparedStmt.toString(), 5);
			
	        ResultSet res = preparedStmt.executeQuery();
	        
	        if (res.next())
	        {
	        	resultList = new ShoppingList();
	        	resultList.setId(res.getLong("id"));
	        	resultList.setCreationTime(Diverse.calendarFromLong(res.getLong("creationTime")));
	        	resultList.setComment(res.getString("comment"));
	        }
	        
	        res.close();
			preparedStmt.close();
			
			if(resultList == null)
				resultList = createNewList();
			
        	// Read entries
			if(resultList.getEntries() == null)
				resultList.setEntries(new ArrayList<>());
			resultList.getEntries().clear();
			
			query = "SELECT * FROM listEntries WHERE listId=?";
			
			preparedStmt = conn.prepareStatement(query);
			preparedStmt.setLong(1, resultList.getId());
			
			Miscellaneous.logEvent(preparedStmt.toString(), 5);
			
	        res = preparedStmt.executeQuery();
	        
	        while (res.next())
	        {
	        	ShoppingListEntry entry = new ShoppingListEntry();
	        	entry.setParentList(resultList);
	        	entry.setProduct(Product.getById(res.getLong("productId")));
	        	entry.setAmount(res.getFloat("amount"));
	        	entry.setUnit(Unit.getById(res.getLong("unit")));
	        	resultList.getEntries().add(entry);
	        }
	        
	        res.close();
			preparedStmt.close();
		}
		catch(Exception e)
		{
			Miscellaneous.logEvent(Diverse.getStackTraceAsString(e), 1);
		}
		finally
		{
			try
			{
				if(preparedStmt != null && !preparedStmt.isClosed())
					preparedStmt.close();
			}
			catch (SQLException e)
			{
			}
		}
		
		if(resultList == null)
			resultList = createNewList();
		
        return resultList;
	}
	
	public boolean sendViaEmail()
	{
		StringBuilder shoppingListString = new StringBuilder();

		if(comment != null)
			shoppingListString.append("(" + comment + ")" + Diverse.lineSeparator);
		
		ArrayList<StoreType> storeTypesInUse = new ArrayList<>();
		for(ShoppingListEntry entry : this.entries)
		{
			if(!storeTypesInUse.contains(entry.getProduct().getStoreType()))
			{
				storeTypesInUse.add(entry.getProduct().getStoreType());
			}
		}
		
		for(StoreType st : storeTypesInUse)
		{		
			shoppingListString.append(st.getName() + Diverse.lineSeparator + "=======================" + Diverse.lineSeparator);			
			
			for(ShoppingListEntry entry : this.entries)
			{
				if(entry.getProduct().getStoreType().equals(st))
				{
					if(entry.getUnit().isDummy)
						shoppingListString.append(entry.getProduct().getName() + Diverse.lineSeparator);
					else
						shoppingListString.append(String.valueOf(entry.getAmount()) + " " + entry.getUnit().getAbbreviation() + " " + entry.getProduct().getName() + Diverse.lineSeparator);
				}
			}
			
			shoppingListString.append(Diverse.lineSeparator);
		}
		
		Miscellaneous.SmtpSimple smtp = new Miscellaneous.SmtpSimple();
		try
		{
			boolean result = true;
			for(String address : Settings.notificationToAddresses)
			{
				if(!smtp.sendEmail(Settings.notificationMailServer, Settings.notificationMailServerPort, Settings.notificationEncryptionType, Settings.notificationAuthenticate, Settings.notificationUsername, Settings.notificationPassword, Settings.notificationFromAddress, address, "Einkaufsliste", shoppingListString.toString()))
				{
					result = false;
				}
			}
			
			if(result)
			{
				Miscellaneous.logEvent("Successfully sent shopping list to all recipients via server " + Settings.notificationMailServer, 2);
				return true;
			}
			else
				Miscellaneous.logEvent("Error sending shopping list.", 2);
		}
		catch (Exception e)
		{
			Miscellaneous.logEvent("Error sending shopping list: " + Diverse.getStackTraceAsString(e), 2);
		}
		
		return false;
	}
	
	public ArrayList<ShoppingListEntry> getEntries()
	{
		return entries;
	}
	public void setEntries(ArrayList<ShoppingListEntry> entries)
	{
		this.entries = entries;
	}
	
	public static ShoppingList createNewList()
	{
		ShoppingList returnList = new ShoppingList();
		returnList.setCreationTime(Calendar.getInstance());
		if(returnList.create())
			return returnList;
		else
			return null;
	}
	@Override
	public String toString()
	{
		return "Liste erstellt am " + Miscellaneous.formatDate(getCreationTime().getTime());
	}
}