package Util;

import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.http.HttpSession;

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

import Beans.R2UserBean;

import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;

public class MongoDBUtil {
	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_ID_FIELDNAME = "_id";
	public static final String USER_FNAME_FIELDNAME = "fname";
	public static final String USER_LNAME_FIELDNAME = "lname";
	public static final String USER_EMAIL_FIELDNAME = "email";
	public static final String USER_ADDRESS1_FIELDNAME = "address1";
	public static final String USER_ADDRESS2_FIELDNAME = "address2";
	public static final String USER_CITY_FIELDNAME = "city";
	public static final String USER_STATE_FIELDNAME = "state";
	public static final String USER_ZIP_FIELDNAME = "zipcode";
	public static final String USER_PHONE_FIELDNAME = "phone";
	public static final String USER_COUNTRY_FIELDNAME = "country";
	public static final String USER_PASSWORD_FIELDNAME = "password";
	
	public static final String RECEIPT_ID_FIELDNAME = "rid";
	public static final String RECEIPT_CARD_FIELDNAME = "card";
	public static final String RECEIPT_MERCHANT_FIELDNAME = "merchantName";
	public static final String RECEIPT_PURCHASEDATE_FIELDNAME = "purchaseDate";
	public static final String RECEIPT_TOTAL_FIELDNAME = "total";
	public static final String RECEIPT_IMG_FIELDNAME = "img";
	public static final String RECEIPT_ITEMS_FIELDNAME = "items";
	public static final String ITEM_NAME_FIELDNAME = "name";
	public static final String ITEM_PRICE_FIELDNAME = "price";
	public static final String ITEM_QTY_FIELDNAME = "qty";
	public static final String 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 final String HOST = "localhost";
	public static final int PORT = 27017;
	
	public static void addDocument(final DB db, final String tbName, final BasicDBObject doc)
	{
		DBCollection collection = db.getCollection(tbName);
		collection.insert(doc);
	}
	
	public static boolean addUser(final BasicDBObject doc)
	{
		boolean ret = false;
		MongoClient mongoClient;
		try {
			mongoClient = new MongoClient("localhost" , 27017);
			DB db = mongoClient.getDB(MongoDBUtil.DBNAME);
			addDocument(db, USERS_COLLECTION_NAME, doc);
			mongoClient.close();
			ret = true;
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			return ret;
		}
	}
	
	public static MongoClient openConn() throws UnknownHostException
	{
		MongoClient mongoClient = new MongoClient(HOST , PORT);
		return mongoClient;
	}
	
	public static void colseConn(final MongoClient c)
	{
		if(c != null)
		{
			c.close();
		}
	}
	
	public static DBObject getCurrentUser(final HttpSession session, final MongoClient c)
	{
		DB db = c.getDB(MongoDBUtil.DBNAME);
		DBObject user = null;
		if(session != null)
		{
			R2UserBean userbean = (R2UserBean)session.getAttribute("currentSessionUser");
			if(userbean != null)
			{
				BasicDBObject searchQuery = new BasicDBObject();
				searchQuery.put(MongoDBUtil.USER_EMAIL_FIELDNAME, userbean.getEmail());
				//searchQuery.put(MongoDBUtil.USER_PASSWORD_FIELDNAME, userbean.getPassword());
				//searchQuery.put(MongoDBUtil.USER_ID_FIELDNAME, userbean.getDocID());
				
				DBCollection users = db.getCollection(MongoDBUtil.USERS_COLLECTION_NAME);
				DBCursor cursor = users.find(searchQuery);
				if(cursor.hasNext())
				{
					//System.err.println(userbean.getDocID());
					user = cursor.next();
				}
			}
		}
		return user;
	}
	
	public static String getMerchantType(final MongoClient c, final String MerchantName)
	{
		String ret = MerchantName;
		if(c != null)
		{
			DB db = c.getDB(MongoDBUtil.DBNAME);
			if(db != null)
			{
				DBCollection merchants = db.getCollection(MongoDBUtil.MERCHANTS_COLLECTION_NAME);
				BasicDBObject searchQuery = new BasicDBObject();
				searchQuery.put(MERCHANT_NAME_FIELDNAME, MerchantName);
				DBCursor cursor = merchants.find(searchQuery);
				if(cursor.hasNext())
				{
					DBObject merchant = cursor.next();
					ret = merchant.get(MERCHANT_CATEGORY_FIELDNAME).toString();
				}
			}
		}
		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 JSONArray getMerchants()
	{
		JSONArray merchants = new JSONArray();
		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())
				{
					
					JSONObject merchant = new JSONObject();
					merchant.put(MERCHANT_ID_FIELDNAME, rs.getObject("MerchantID").toString());
					merchant.put(MERCHANT_NAME_FIELDNAME, rs.getObject("MerchantName").toString());
					merchant.put(MERCHANT_CATEGORY_FIELDNAME, rs.getObject("CategoryName").toString());
					merchant.put(MERCHANT_ADDRESS1_FIELDNAME, rs.getObject("Address1").toString());
					merchant.put(MERCHANT_ADDRESS2_FIELDNAME, rs.getObject("Address2").toString());
					merchant.put(MERCHANT_CITY_FIELDNAME, rs.getObject("City").toString());
					merchant.put(MERCHANT_STATE_FIELDNAME, rs.getObject("State").toString());
					merchant.put(MERCHANT_ZIP_FIELDNAME, rs.getObject("Zip").toString());
					merchant.put(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.toJSONString());
		return merchants;
	}
	
	public static void main(final String[] args)
	{
		getMerchants();
	}
}
