package Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

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

import djondb.DjondbConnection;
import djondb.DjondbConnectionManager;

public class djsonDBUtil {
	public static final String DBNAME = "myReceipts";
	public static final String USERS_COLLECTION_NAME = "users";
	public static DjondbConnection openConn()
	{
		return null;
		/*DjondbConnection c = null;
		try 
		{
	         Loader.initialize();
	         c = DjondbConnectionManager.getConnection("localhost");
	         c.open();
		}
		catch(LibraryException e)
		{
			e.printStackTrace();
		}
		finally
		{
			return c;
		}*/
	}
	
	public static void closeConn(final DjondbConnection c)
	{
		if(c != null)
		{
			c.close();
			DjondbConnectionManager.releaseConnection(c);
		}
	}
	
	public static boolean addUser(final JSONObject json)
	{
		return insert(DBNAME, USERS_COLLECTION_NAME, json);
	}
	
	public static boolean insert(final String db, final String namespace, final JSONObject json)
	{
		DjondbConnection c = openConn();
		boolean ret = false;
		if(c != null)
		{
			ret = c.insert(db, namespace, json.toJSONString());
			closeConn(c);
		}
		return ret;
	}
	
	public static JSONArray getReceipts(final String email)
	{
		JSONArray receipts = new JSONArray();
		String sql = "SELECT ReceiptID, CardID, PurchaseDate, MerchantName FROM R2Receipt, R2Merchant WHERE R2Merchant.MerchantID=R2Receipt.MerchantID AND UserID = (SELECT UserID FROM R2User WHERE Email=?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try
		{
			con = ConnectionManager.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, email);
			rs = stmt.executeQuery();
			if(rs != null)
			{
				int i=0;
				while(rs.next())
				{
					JSONObject receipt = new JSONObject();
					receipt.put("rid", i++);
					receipt.put("card", rs.getObject("CardID").toString());
					receipt.put("merchantName", rs.getString("MerchantName"));
					receipt.put("purchaseDate", rs.getDate("PurchaseDate").toString());
					String sql2 = "SELECT ItemName, ItemPrice, ItemQty, ItemTaxRate FROM r2lineitem where ReceiptID=" + rs.getObject("ReceiptID").toString() + " AND UserID = (SELECT UserID FROM R2User WHERE Email='" + email + "')";
					//System.out.println(sql2);
					Statement stmt2 = con.createStatement();
					ResultSet rs2 = stmt2.executeQuery(sql2);
					JSONArray items = new JSONArray();
					double total = 0.0;
					if(rs2 != null)
					{
						while(rs2.next())
						{
							JSONObject item = new JSONObject();
							double price = rs2.getDouble("ItemPrice");
							double qty = rs2.getInt("ItemQty");
							double tax = rs2.getDouble("ItemTaxRate");
							item.put("name", rs2.getString("ItemName"));
							item.put("price", price);
							item.put("qty", qty);
							item.put("taxRate", tax);
							total += price * qty * (1 + tax);
							items.add(item);
						}
					}
					receipt.put("total", total);
					receipt.put("items", items);
					DBUtil.closeResultSet(rs2);
					DBUtil.closeStmt(stmt2);
					receipts.add(receipt);
				}
			}
		}
		catch (Throwable theException) 	    
		{
		     System.out.println(theException); 
		}
		finally
		{
			/*System.out.println("ret json:");
			System.out.println(ret);*/
			DBUtil.closeResultSet(rs);
			DBUtil.closeStmt(stmt);
			DBUtil.closeConnection(con);
		}
		System.out.println(receipts.toJSONString());
		return receipts;
	}
}
