package requestHandlers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Calendar;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.json.JSONArray;
import org.json.JSONObject;

import Beans.R2UserBean;
import Util.ConnectionManager;
import Util.DBUtil;

public class getSpendingCurve implements RequestHandler{
	private String sql = "SELECT SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS total, Month(PurchaseDate) AS month " +
            			 "FROM R2LineItem, R2Receipt " +
                         "WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND " +
                         "R2LineItem.UserID =? AND " +
                         "Year(PurchaseDate)=? " +
                         "GROUP BY month " +
                         "ORDER BY month";
	private String sqlCategorySpending = "SELECT SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS sum, R2Category.CategoryName AS category, Month(PurchaseDate) AS month " +
								 "FROM R2LineItem, R2Receipt, R2user, R2Merchant, R2Category " + 
								 "WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND " +
								 "R2Receipt.UserID=R2User.UserID AND " +
								 "R2User.UserID=? AND " +
								 "R2Merchant.MerchantID=R2Receipt.MerchantID AND " +
								 "R2Category.CategoryID=R2Merchant.CategoryID AND " +
								 "Year(PurchaseDate)=? " +
								 "GROUP BY R2Category.CategoryName, month " +
								 "ORDER BY month";
	private String sqlCategories = "SELECT CategoryName FROM R2Category";
	private final int NUM_OF_MONTH = 12;
	@Override
	public void processRequest(Object user, HttpServletRequest req,
			HttpServletResponse res, Object[] services, Object log)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		PrintWriter out = res.getWriter();
		Connection con = null;
		PreparedStatement stmt = null;
		PreparedStatement stmtCategorySpending = null;
		Statement stmtCategories = null;
		ResultSet rs = null;
		ResultSet rsCategorySpending = null;
		ResultSet rsCategories = null;
		JSONArray series = new JSONArray();
		try
		{
			HttpSession session = req.getSession(false);
			if(session != null)
			{
				R2UserBean userbean = (R2UserBean)session.getAttribute("currentSessionUser");
				if(userbean != null)
				{
					int year = Calendar.getInstance().get(Calendar.YEAR);
					try
					{
						year = Integer.parseInt(req.getParameter("year"));
					}catch(NumberFormatException nfe)
					{
						System.err.println(nfe);
						year = Calendar.getInstance().get(Calendar.YEAR);
					}
					con = ConnectionManager.getConnection();
					int index = 1;
					stmt = con.prepareStatement(sql);
					stmt.setInt(index++, userbean.getUserID());
					stmt.setInt(index++, year);
					//System.out.println("sql = " + stmt.toString());
					rs = stmt.executeQuery();
					if(rs != null)
					{
						JSONObject json = new JSONObject();
						json.put("name", "Total");
						JSONArray record = new JSONArray();
						for(int i=1; i<=NUM_OF_MONTH; ++i)
						{
							record.put(0.0);
						}
						while(rs.next())
						{
							int m = rs.getInt("month");
							record.put(m-1, rs.getDouble("total"));
							//System.out.println(m + " : " + rs.getDouble("total"));
						}
						json.put("data", record);
						series.put(json);
						
						// init for each category
						stmtCategories = con.createStatement();
						rsCategories = stmtCategories.executeQuery(sqlCategories);
						if(rsCategories != null)
						{
							while(rsCategories.next())
							{
								json = new JSONObject();
								json.put("name", rsCategories.getString(1));
								record = new JSONArray();
								for(int i=1; i<=NUM_OF_MONTH; ++i)
								{
									record.put(0.0);
								}
								json.put("data", record);
								series.put(json);
							}
							
							stmtCategorySpending = con.prepareStatement(sqlCategorySpending);
							index = 1;
							stmtCategorySpending.setInt(index++, userbean.getUserID());
							stmtCategorySpending.setInt(index++, year);
							//System.out.println(stmtCategorySpending);
							rsCategorySpending = stmtCategorySpending.executeQuery();
							if(rsCategorySpending != null)
							{
								while(rsCategorySpending.next())
								{
									double sum = rsCategorySpending.getDouble(1);
									String category = rsCategorySpending.getString(2);
									int m = rsCategorySpending.getInt(3);
									// this is awkward and ugly, but for present only
									for(int j=0; j<series.length(); ++j)
									{
										JSONObject temp = series.getJSONObject(j);
										if(category.equalsIgnoreCase(temp.get("name").toString()))
										{
											JSONArray tempArr = temp.getJSONArray("data");
											//System.out.println(category + ":" + m + ":" + sum);
											tempArr.put(m-1, sum);
										}
									}
								}
							}
						}
					}
				}
			}
		}
		catch (Throwable theException) 	    
		{
		     System.out.println(theException); 
		}
		finally
		{
			System.out.println("serias json:");
			System.out.println(series);
			DBUtil.closeResultSet(rs);
			DBUtil.closeResultSet(rsCategorySpending);
			DBUtil.closeResultSet(rsCategories);

			DBUtil.closeStmt(stmt);
			DBUtil.closeStmt(stmtCategorySpending);
			DBUtil.closeStmt(stmtCategories);
			DBUtil.closeConnection(con);
			out.print(series);
			out.close();
		}
	}

}
