package Util;

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

import javax.servlet.http.HttpSession;

import org.json.simple.JSONObject;

import Beans.R2UserBean;
import djondb.BSONArrayObj;
import djondb.BSONObj;
import djondb.DjondbConnection;
import djondb.DjondbConnectionManager;
import djondb.LibraryException;

public class djonDBUtil {
	public static final String DBNAME = "myReceipts";
	public static final String USERS_COLLECTION_NAME = "users";
	public static final String MERCHANTS_COLLECTION_NAME = "merchants";
	public static final String USER_RECEIPTS_FIELDNAME = "receipts";
	public static final String USER_RECEIPT_ID_FIELDNAME = "rid";
	public static final String USER_RECEIPT_CARD_FIELDNAME = "card";
	public static final String USER_RECEIPT_MERCHANT_FIELDNAME = "merchantName";
	public static final String USER_RECEIPT_PURCHASEDATE_FIELDNAME = "purchaseDate";
	public static final String USER_RECEIPT_TOTAL_FIELDNAME = "total";
	public static final String USER_RECEIPT_IMG_FIELDNAME = "img";
	public static final String USER_RECEIPT_ITEMS_FIELDNAME = "items";
	public static final String USER_ITEM_NAME_FIELDNAME = "name";
	public static final String USER_ITEM_PRICE_FIELDNAME = "price";
	public static final String USER_ITEM_QTY_FIELDNAME = "qty";
	public static final String USER_ITEM_TAXRATE_FIELDNAME = "taxRate";
	public static final String USER_CARDS_FIELDNAME = "cards";
	public static final String MERCHANT_NAME_FIELDNAME = "name";
	public static final String MERCHANT_ID_FIELDNAME = "id";
	public static final String MERCHANT_CATEGORY_FIELDNAME = "category";
	public static final String MERCHANT_ADDRESS1_FIELDNAME = "address1";
	public static final String MERCHANT_ADDRESS2_FIELDNAME = "address2";
	public static final String MERCHANT_CITY_FIELDNAME = "city";
	public static final String MERCHANT_STATE_FIELDNAME = "state";
	public static final String MERCHANT_ZIP_FIELDNAME = "zipcode";
	public static final String MERCHANT_PHONE_FIELDNAME = "phone";
	
	public static DjondbConnection openConn()
	{
		DjondbConnection c = DjondbConnectionManager.getConnection("localhost");
		if(c.open())
		{
			return c;
		}
		else
		{
			return null;
		}
	}
	
	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 BSONObj getCurrentUser(final HttpSession session, final DjondbConnection c)
	{
		BSONObj user = null;
		if(session != null)
		{
			R2UserBean userbean = (R2UserBean)session.getAttribute("currentSessionUser");
			if(userbean != null)
			{
				StringBuilder sb = new StringBuilder();
				sb.append("$'email' == '").append(userbean.getEmail()).append("'");
				sb.append(" and $'_id' == '").append(userbean.getDocID()).append("'");
				BSONArrayObj userArr = c.find(djonDBUtil.DBNAME, djonDBUtil.USERS_COLLECTION_NAME, sb.toString());
				if(userArr != null)
				{
					user = userArr.get(0);
				}
			}
		}
		return user;
	}
	
	public static String getMerchantType(final DjondbConnection c, final String MerchantName)
	{
		String ret = MerchantName;
		if(c != null)
		{
			BSONArrayObj merchants = c.find(DBNAME, MERCHANTS_COLLECTION_NAME, "$'name' == '" + MerchantName + "'");
			BSONObj merchant = merchants.get(0);
			if(merchant != null)
			{
				ret = merchant.getString(MERCHANT_CATEGORY_FIELDNAME);
			}
		}
		return ret;
	}
	
	// default format YYYY-MM-DD HH:MM:SS
	public static int getYear(final String date)
	{
		return Integer.parseInt(date.substring(0, 4));
	}
	
	public static int getMonth(final String date)
	{
		return Integer.parseInt(date.substring(5, 7));
	}
	
	public static BSONArrayObj getReceipts(final String email)
	{
		BSONArrayObj receipts = new BSONArrayObj();
		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())
		{
		BSONObj receipt = new BSONObj();
		receipt.add("rid", i++);
		receipt.add("card", rs.getObject("CardID").toString());
		receipt.add("merchantName", rs.getString("MerchantName"));
		receipt.add("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);
		BSONArrayObj items = new BSONArrayObj();
		double total = 0.0;
		if(rs2 != null)
		{
		while(rs2.next())
		{
		BSONObj item = new BSONObj();
		double price = rs2.getDouble("ItemPrice");
		double qty = rs2.getInt("ItemQty");
		double tax = rs2.getDouble("ItemTaxRate");
		item.add("name", rs2.getString("ItemName"));
		item.add("price", price);
		item.add("qty", qty);
		item.add("taxRate", tax);
		total += price * qty * (1 + tax);
		items.add(item);
		}
		}
		receipt.add("total", total);
		receipt.add("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.toChar());
		return receipts;
	}
	
	public static BSONArrayObj getMerchants()
	{
		BSONArrayObj merchants = new BSONArrayObj();
		String sql = "SELECT MerchantID, MerchantName, CategoryName, Address1, Address2, City, State, Zip, Phone FROM r2merchant, r2category WHERE r2merchant.CategoryID=r2category.CategoryID";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			con = ConnectionManager.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs != null)
			{
				while(rs.next())
				{
					
					BSONObj merchant = new BSONObj();
					merchant.add(MERCHANT_ID_FIELDNAME, rs.getObject("MerchantID").toString());
					merchant.add(MERCHANT_NAME_FIELDNAME, rs.getObject("MerchantName").toString());
					merchant.add(MERCHANT_CATEGORY_FIELDNAME, rs.getObject("CategoryName").toString());
					merchant.add(MERCHANT_ADDRESS1_FIELDNAME, rs.getObject("Address1").toString());
					merchant.add(MERCHANT_ADDRESS2_FIELDNAME, rs.getObject("Address2").toString());
					merchant.add(MERCHANT_CITY_FIELDNAME, rs.getObject("City").toString());
					merchant.add(MERCHANT_STATE_FIELDNAME, rs.getObject("State").toString());
					merchant.add(MERCHANT_ZIP_FIELDNAME, rs.getObject("Zip").toString());
					merchant.add(MERCHANT_PHONE_FIELDNAME, rs.getObject("Phone").toString());
					merchants.add(merchant);
				}
			}
		}
		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(merchants.toChar());
		return merchants;
	}
	
	public static void main(final String[] args) throws LibraryException
	{
		//Loader.initialize();
		//getReceipts("song.x.guo@oracle.com");
		getMerchants();
	}
	public static double round(double value, int places) {
	    if (places < 0) throw new IllegalArgumentException();

	    long factor = (long) Math.pow(10, places);
	    value = value * factor;
	    long tmp = Math.round(value);
	    return (double) tmp / factor;
	}
}
