package Juno2;


import Juno2.PositionVO;
import Juno2.LoginVO;
import java.util.HashMap;
import Juno2.AccountVO;
import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.util.*;
import java.util.Date;
import javax.naming.*;


public class CustomerDBDAO implements CmdDAO {


	static private LinkedList connectionPool = null;


	static private DataSource dataSource;
	static private boolean useJDBC20 = true;
	static private String driverClass;
	static private String connClass;
	static private String loginID;
	static private String loginPW;
	static private short connCount = 0;


	//// Because we are using newInstance() to create this object we must have a
	//// zero parm constructor


	public CustomerDBDAO() {}


	//// Should there be a return value for init()?


	public void init(ResourceBundle rb) {


		driverClass = rb.getString("CustomerDBDriverClass");
		connClass = rb.getString("CustomerDBConnectionClass");
		loginID = rb.getString("CustomerDBConnIDClass");
		loginPW = rb.getString("CustomerDBConnPSWClass");


		String poolingOption = rb.getString("useJDBC20Pooling");
		if (!poolingOption.equals("true")) {
			useJDBC20 = false;
		}


		if (useJDBC20) {
			System.out.println("Using JDBC 2.0 Connection Pooling");
			try {
				Context ctx = new InitialContext();
				String jndiName = rb.getString("CustomerDBjndiName");
				System.out.println("using data source of " + jndiName);
				dataSource = (DataSource) ctx.lookup(jndiName);
				//dataSource = (DataSource) ctx.lookup("jdbc/JupiterDB");
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else { // manual connection pooling
			System.out.println("Using home grown Connection Pooling");
			if (connectionPool == null) {
				connectionPool = new LinkedList();
				//System.out.println("***** CustomerDAO instantiating LinkedList *****");
			}
		}
	}


	private final static Connection getConnection() {
		Connection conn = null;


		if (useJDBC20) {
			try {
				conn = dataSource.getConnection(loginID, loginPW);
				//conn = dataSource.getConnection("db2inst1", "db2inst1");
			} catch (Exception e) {
				System.err.println(
					"Connection Pool SQLException : " + e + ":" + e.getMessage());
				// e.printStackTrace();
			}


		} else {


			synchronized (connectionPool) {
				if (connectionPool.size() > 0)
					conn = (Connection) connectionPool.removeFirst();
			}


			if (conn == null) //// did'nt get one from list - build one
				{


				//// build a new connection
				// System.out.println("*** CustomerDAO - Building new Connection");
				try {
					// Load the JDBC driver


					Class.forName(driverClass).newInstance();
					conn = DriverManager.getConnection(connClass, loginID, loginPW);


					System.out.println("*** CustomerDAO... created Connection " + ++connCount);
					//// how validate connection?
				} catch (SQLException ex) {
					System.err.println("SQLException: " + ex + ":" + ex.getMessage());
				} catch (Exception ex2) {
					System.err.println("Exception: " + ex2 + ":" + ex2.getMessage());
				}
			}
			////        else
			////        {
			////            validate conn before returning
			////			System.out.println("Getting Connection: "+conn);
			////			System.out.println("connectionPool = "+connectionPool);
			////			System.out.println("CustomerDAO reusing Connection, " + connectionPool.size() + " connections left in pool.");
			////        }


		}


		return conn;
	}


	private final static boolean releaseConnection(Connection conn) {
		
		//// should have some way of revalidation a connection before addding it back to list
		if(conn == null)
			return true;


		////System.out.println("****** Releasing connection " + conn.toString());			
		
		if (useJDBC20) {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			synchronized (connectionPool) {
				connectionPool.addLast(conn);
			}


			////System.out.println("CustomerDAO released Connection, " + connectionPool.size() + " connections left in pool.");
		}


		return (true);
	}


	public LoginVO getLogin(String custid, String passwd) {


		LoginVO loginreq = new LoginVO();


		Connection conn = getConnection();
		short iAttempt = 0;
		while (conn == null && iAttempt < 2) {
			System.err.println("No connection available for login !!" + ++iAttempt);
			conn = getConnection();
		}


		//System.out.println("***CustomerDAO Custid= "+custid+" Passwd= "+passwd);
		try {


			Statement stmt = conn.createStatement();
			ResultSet rset =
				stmt.executeQuery(
					"SELECT CUSTID, PASSWORD, CUSTFNAME, CUSTLNAME FROM CUSTOMER WHERE CUSTID="
						+ "'"
						+ custid
						+ "'"
						+ "AND PASSWORD="
						+ "'"
						+ passwd
						+ "'");


			while (rset.next()) {
				//System.out.println("*** CustomerDAO got a result set");
				loginreq.setUserId(rset.getString("CUSTID"));
				loginreq.setUserPassword(rset.getString("PASSWORD"));
				loginreq.setFirstName(rset.getString("CUSTFNAME"));
				loginreq.setLastName(rset.getString("CUSTLNAME"));
				loginreq.setLoginState(true);
			}


		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex + ":" + ex.getMessage());
			loginreq.setLoginState(false);
			loginreq.setUserId(null);
			loginreq.setUserPassword(null);
			releaseConnection(conn);
			return loginreq;
		} catch (Exception ex2) {
			System.err.println("Exception: " + ex2 + ":" + ex2.getMessage());
			loginreq.setLoginState(false);
			loginreq.setUserId(null);
			loginreq.setUserPassword(null);
			releaseConnection(conn);
			return loginreq;
		}


		releaseConnection(conn);
		//System.out.println("*** CustomerDAO...returning loginreq...");
		return loginreq;
	}


	public HashMap getAccountList(String userId) {


		HashMap hmap = new HashMap();


		Connection conn = getConnection();


		short iAttempt = 0;
		while (conn == null) {
			System.err.println("No connection available for AL !! " + ++iAttempt);
			conn = getConnection();
		}


		// System.out.println("AccountList connection: "+conn);
		try {


			Statement stmt = conn.createStatement();


			//query database
			// System.out.println("***Ready to Query DBMS for AccountList***");
			// System.out.println("**userid to query DBMS is: "+userId);
			ResultSet rset =
				stmt.executeQuery(
					"SELECT CUSTID, ACCOUNT, ACCTDESC, ACCTBAL FROM ACCOUNT WHERE CUSTID="
						+ "'"
						+ userId
						+ "'");


			while (rset.next()) {
				// System.out.println("***Walking through result set for AccountList***");
				AccountVO acctinfo = new AccountVO();
				acctinfo.setCustId(rset.getString("CUSTID"));
				acctinfo.setAccountDescription(rset.getString("ACCTDESC"));
				acctinfo.setAccountId(rset.getString("ACCOUNT"));
				acctinfo.setAccountBalance(rset.getFloat("ACCTBAL"));
				hmap.put(acctinfo.getAccountId(), acctinfo);
			}
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex + ":" + ex.getMessage());
			releaseConnection(conn);
			return hmap;


		} catch (Exception ex2) {
			System.err.println("Exception: " + ex2 + ":" + ex2.getMessage());
			releaseConnection(conn);
			return hmap;
		}
		releaseConnection(conn);
		// System.out.println("***Hashmap from AccountList: "+hmap);
		return hmap;
	}


	public HashMap getPositionList(String userId, String accountId) {


		HashMap hmap = new HashMap();
		// Connect to the database and load JDBC driver
		//System.out.println("*** CustomerDAO...get connection for position list***");
		Connection conn = getConnection();
		short iAttempt = 0;
		while (conn == null && iAttempt < 10) {
			System.err.println("No connection available for Pos !! " + ++iAttempt);
			conn = getConnection();
		}


		try {
			// System.out.println("***Creating Statement for position list...");
			Statement stmt = conn.createStatement();


			//query database
			// System.out.println("*** About to query DBMS for position list, usiing AccountId : "+accountId);
			ResultSet rset =
				stmt.executeQuery(
					"SELECT ACCOUNT, SYMBOL, NUMSHARES, PCPRICE, COSTBASE FROM POSITIONS WHERE ACCOUNT= "
						+ "'"
						+ accountId
						+ "'");
			// Iterate through the resultset
			while (rset.next()) {
				PositionVO pos = new PositionVO();
				pos.setAccountId(rset.getString("ACCOUNT"));
				pos.setSymbol(rset.getString("SYMBOL"));
				pos.setNumberOfShares(rset.getInt("NUMSHARES"));
				pos.setPreviousClosePrice(rset.getFloat("PCPRICE"));
				pos.setCostBasis(rset.getFloat("COSTBASE"));
				hmap.put(pos.getSymbol(), pos);
				// System.out.println("Retrieved postion "+  pos.getSymbol());
			}


		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex + ":" + ex.getMessage());
			releaseConnection(conn);
			return hmap;
		} catch (Exception ex2) {
			System.err.println("Exception: " + ex2 + ":" + ex2.getMessage());
			releaseConnection(conn);
			return hmap;
		}
		releaseConnection(conn);
		// System.out.println("Hashmap being returned : "+hmap);
		return hmap;
	}


	public boolean sellPosition(String sessionUserId, String accountId, EquityVO evo) {


		String sym = evo.getSymbol();
		Date date = evo.getDate();
		float quote = evo.getPrice();
		String desc = evo.getDescription();


		// System.out.println ("START OF SELL: SYMBOL = "+"'"+sym+"'"+" Account = "+_acctid);
		if (sym.equals("CASH")) {
			//	   System.out.println ("RETURNING FALSE - SYMBOL = CASH");
			return true; // DO NOTHING...CAN'T SELL CASH POSITION
		}


		// Connect to the database and load JDBC driver
		Connection conn = getConnection();
		short iAttempt = 0;
		while (conn == null && iAttempt < 10) {
			System.err.println("No connection available for Sell !! " + ++iAttempt);
			conn = getConnection();
		}


		try {


			Statement stmt = conn.createStatement();
			ResultSet rset =
				stmt.executeQuery(
					"SELECT NUMSHARES FROM POSITIONS WHERE ACCOUNT= "
						+ "'"
						+ accountId
						+ "'"
						+ "AND SYMBOL = "
						+ "'"
						+ sym
						+ "'");
			rset.next();


			float posworth = 0;
			int numshares = rset.getInt("NUMSHARES");
			posworth = numshares * quote;
			String updateString1 =
				"UPDATE POSITIONS SET NUMSHARES = 0 WHERE ACCOUNT= "
					+ "'"
					+ accountId
					+ "'"
					+ "AND SYMBOL ="
					+ "'"
					+ sym
					+ "'";
			// System.out.println ("About to update Sold position, set numshares = 0 "+updateString1);
			stmt.executeUpdate(updateString1);
			String updateString2 =
				"UPDATE POSITIONS SET COSTBASE = COSTBASE + "
					+ posworth
					+ ", NUMSHARES = NUMSHARES +"
					+ posworth
					+ " WHERE ACCOUNT= "
					+ "'"
					+ accountId
					+ "'"
					+ " AND SYMBOL ="
					+ " 'CASH'";


			stmt.executeUpdate(updateString2);
			String updateString3 =
				"INSERT INTO SELLPOSITION VALUES("
					+ "'"
					+ accountId
					+ "'"
					+ ", "
					+ "'"
					+ sym
					+ "'"
					+ ", "
					+ quote
					+ ", "
					+ "'"
					+ desc
					+ "'"
					+ ", "
					+ "'"
					+ date.toString()
					+ "'"
					+ ")";
			stmt.executeUpdate(updateString3);
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex + ":" + ex.getMessage());
			releaseConnection(conn);
			return false;
		} catch (Exception ex2) {
			System.out.println("Exception: " + ex2 + ":" + ex2.getMessage());
			releaseConnection(conn);
			return false;
		}
		// System.out.println ("*** COMPLETED SELL POSITION - returning TRUE");
		releaseConnection(conn);
		return true;
	}



	////public void toString() {
	////	System.out.println(this.getClass().getName());
	////}
}