/*
 * Created on Aug 25, 2003
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package DatabaseCommunication;
import java.sql.*;
import DataClasses.*;
import java.io.*;
import java.util.*;

/**
 * @author student
 *
 * This class is being used for managing users in the application database.
 * 
 */
public class UserManager {
	private Connection con;

	
	/**
	 * This is the constructor for UserManager.java
	 */
	public UserManager() {
		try {
			Properties p = new Properties();
			p.load(new FileInputStream("stocktrading.txt"));
			String url = p.getProperty("MYSQLURL");
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url, "root", "password");
			System.out.println(
				"Now connected to the Stock Trading database from User manager.");
		} catch (Exception e) {
			System.out.println(
				"User Manager constructor exception : " + e.toString());
			;

		}

	}

	/**
	 * This method is being used for adding a user to the application database.
	 * @param user The user to be added.
	 * @return boolean Specifies whether the adding was sucessful.
	 */
	public boolean insertUser(User_Data user) {
		String acc = String.valueOf(user.getAccount());
		String insert =
			"INSERT INTO user (UserName, NcbId, Type, Account)"
				+ " VALUES"
				+ "('"
				+ user.getUserName()
				+ "', "
				+ user.getNcbId()
				+ ", '"
				+ user.getType()
				+ "', '"
				+ acc
				+ "' );";
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(insert);
			insertStocksInterested(user);
		} catch (Exception e) {
			System.out.println(
				"Error in User manager insertUser :" + e.toString());
			return false;
		}
		return true;
	}

	/**
	 * This method is being used for updating user details in the
	 * application database.
	 * @param user The user details to be updated.
	 * @return boolean Specifies whether the update was successful.
	 */
	public boolean updateUser(User_Data user) {
		String acc = String.valueOf(user.getAccount());
		String update =
			"UPDATE user SET Account='"
				+ acc
				+ "' WHERE UserName='"
				+ user.getUserName()
				+ "';";
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(update);
			updateStocksInterested(user);
		} catch (Exception e) {
			System.out.println(
				"Error in User manager updateUser :" + e.toString());
			return false;
		}
		return true;
	}
	/**
	 * This method is being used for updating the interested stocks for the
	 * specified user.
	 * @param user The user for whom the interested stocks are updated.
	 * @return boolean Specifies whether the update was successful.
	 */
	private boolean updateStocksInterested(User_Data user) {
		Stock_Data[] stocks = user.getStocksInterested();
		int count = stocks.length;
		try {
			Statement stmt = con.createStatement();
			System.out.println(
				"User name in updateStockInterested :" + user.getUserName());
			String delete =
				"DELETE FROM stockinterested WHERE UserName='"
					+ user.getUserName()
					+ "';";
			stmt.executeUpdate(delete);
			for (int i = 0; i < count; i++) {
				Stock_Data tempstock = stocks[i];
				String insert =
					"INSERT INTO stockinterested (UserName, StockCode)"
						+ " VALUES"
						+ "('"
						+ user.getUserName()
						+ "', '"
						+ tempstock.getStockCode()
						+ "' );";

				stmt.executeUpdate(insert);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println(
				"Error in User manager updateStocksInterested :"
					+ e.toString());
			return false;
		}
		return true;
	}
	
	/**
	 * This method is being used for adding interested stocks for a user.
	 * @param user The user for whom the interested stocks are being added.
	 * @return boolean Specifies whether the adding was successful.
	 */
	private boolean insertStocksInterested(User_Data user) {
		Stock_Data[] stocks = user.getStocksInterested();
		int count = stocks.length;
		try {
			Statement stmt = con.createStatement();
			for (int i = 0; i < count; i++) {
				Stock_Data tempstock = stocks[i];
				String insert =
					"INSERT INTO stockinterested (UserName, StockCode)"
						+ " VALUES"
						+ "('"
						+ user.getUserName()
						+ "', '"
						+ tempstock.getStockCode()
						+ "' );";

				stmt.executeUpdate(insert);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println(
				"Error in UserManager insertStocksInterested :" + e.toString());
			return false;
		}
		return true;
	}

	/**
	 * This method is being used for updating the owned stocks.
	 * @param user The user who owns the stocks.
	 * @return boolean Specifies whether the update was successful.
	 */
	public boolean updateStocksOwned(User_Data user) {
		StockOwnership_Data[] owned = user.getStocksOwned();
		if (owned == null) {
			return false;
		}
		int count = owned.length;
		try {
			Statement stmt = con.createStatement();
			System.out.println(
				"User name in updateStockOwnership :" + user.getUserName());
			String delete =
				"DELETE FROM stockownership WHERE UserName='"
					+ user.getUserName()
					+ "';";
			stmt.executeUpdate(delete);
			for (int i = 0; i < count; i++) {
				StockOwnership_Data tempstock = owned[i];

				double totalprice =
					Double.valueOf(tempstock.getTotalCost()).doubleValue();
				double lastprice =
					Double
						.valueOf(tempstock.getLastPurchaseAmount())
						.doubleValue();

				System.out.println("inserting into stockOwnership table..");
				String insert =
					"INSERT INTO stockownership (UserName, StockCode, TotalQty, TotalPrice, LastPurchaseDate, LastPurchaseAmount, LastPurchaseQty)"
						+ " VALUES"
						+ "( '"
						+ user.getUserName()
						+ "', '"
						+ tempstock.getStockCode()
						+ "', "
						+ Integer.parseInt(tempstock.getQuantityOwned())
						+ ", "
						+ totalprice
						+ ", '"
						+ tempstock.getLastPurchasedDate()
						+ "', "
						+ lastprice
						+ ", "
						+ Integer.parseInt(tempstock.getLastPurchaseQty())
						+ " );";

				stmt.executeUpdate(insert);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println(
				"Error in User manager updateStocksOwned :" + e.toString());
			return false;
		}

		return true;
	}

	/**
	 * This method is being used for retrieving user information.
	 * @param user The user fro whom the data has to be retrieved.
	 * @return boolean Specifies whether the retrieval was successful.
	 */
	public boolean readUser(User_Data user) {
		try {
			Statement stmt = con.createStatement();
			String query =
				"SELECT * FROM user WHERE UserName='"
					+ user.getUserName()
					+ "';";
			ResultSet rs = stmt.executeQuery(query);
			boolean isFilled = rs.next();

			if (!isFilled)
				return false;

			user.setUserName(rs.getString(1));
			user.setNcbId(Integer.parseInt(rs.getString(2)));
			user.setType(rs.getString(3));
			double d = (Double.valueOf(rs.getString(4))).doubleValue();
			user.setAccount(d);
			stmt.close();
		} catch (Exception e) {
			System.out.println(
				"Error in User manager readUser :" + e.toString());
			return false;
		}
		return true;
	}

	/**
	 * This method is being used for retrieving the interested stocks for
	 * a particular user.
	 * @param user The user for whom the interested stocks are to be
	 * retrieved.
	 * @return boolean Specifies whether the retrieval was successful.
	 */
	public boolean readStocksInterested(User_Data user) {
		try {
			Statement stmt = con.createStatement();
			String query =
				"SELECT StockCode FROM stockinterested WHERE UserName='"
					+ user.getUserName()
					+ "';";
			System.out.println("String : " + query);
			ResultSet rs = stmt.executeQuery(query);
			int counter = 0;
			Stock_Data[] interested;
			while (rs.next()) {
				counter++;
				System.out.println("CTR: " + counter);
			}
			rs.close();
			interested = new Stock_Data[counter];
			ResultSet rs2 = stmt.executeQuery(query);
			for (int i = 0; i < interested.length; i++) {
				rs2.next();
				Stock_Data temp = new Stock_Data();
				temp.setStockCode(rs2.getString(1));
				System.out.println("Stock Code : " + temp.getStockCode());
				interested[i] = temp;
			}
			rs2.close();
			stmt.close();
			Statement s = con.createStatement();
			for (int j = 0; j < interested.length; j++) {
				Stock_Data temp = interested[j];
				String qry =
					"SELECT * FROM stock WHERE StockCode='"
						+ temp.getStockCode()
						+ "';";
				System.out.println("String : " + qry);
				ResultSet rs3 = s.executeQuery(qry);
				rs3.next();
				temp.setCompanyName(rs3.getString(2));
				temp.setWeekHigh(rs3.getString(3));
				temp.setWeekLow(rs3.getString(4));
				temp.setBaseValue(rs3.getString(5));
				temp.setEarningsPerShare(rs3.getString(6));
				temp.setDividendsPerShare(rs3.getString(7));
				rs3.close();
			}
			user.setStocksInterested(interested);
			s.close();
		} catch (Exception e) {
			System.out.println(
				"Error in User manager readStocksInterested :" + e.toString());
			return false;
		}
		return true;
	}

	/**
	 * This method is being used for retrieving the stocks owned for a particular
	 * user.
	 * @param user The user for whom the owned stocks are to be retrieved.
	 * @return boolean Specifies whether the retreival was successful.
	 */
	public boolean readStocksOwned(User_Data user) {
		try {
			Statement stmt = con.createStatement();
			String query =
				"SELECT * FROM stockownership WHERE UserName='"
					+ user.getUserName()
					+ "';";
			System.out.println("String : " + query);
			ResultSet rs = stmt.executeQuery(query);
			int counter = 0;
			while (rs.next()) {
				counter++;
				System.out.println("CTR: " + counter);
			}
			rs.close();
			StockOwnership_Data[] owned = new StockOwnership_Data[counter];
			//Statement s = con.createStatement();
			ResultSet rs2 = stmt.executeQuery(query);
			System.out.println("Excecuted query " + rs2.isLast());
			for (int i = 0; i < owned.length; i++) {
				rs2.next();
				System.out.println(
					"After rs2.next() "
						+ rs2.isLast()
						+ " "
						+ rs2.getFetchSize());
				System.out.println(rs2.getString(1));
				System.out.println(rs2.getString(2));
				System.out.println(rs2.getString(3));
				System.out.println(rs2.getString(4));
				System.out.println(rs2.getString(5));
				System.out.println(rs2.getString(6));
				System.out.println(rs2.getString(7));
				System.out.println(rs2.getString(8));

				StockOwnership_Data temp =
					new StockOwnership_Data(
						rs2.getString(3),
						null,
						rs2.getString(4),
						rs2.getString(5),
						rs2.getTimestamp(6),
						rs2.getString(7),
						rs2.getString(8));
				System.out.println("Stock Code : " + temp.getStockCode());
				owned[i] = temp;
			}
			for (int j = 0; j < owned.length; j++) {
				StockOwnership_Data temp = owned[j];
				String qry =
					"SELECT CompanyName FROM stock WHERE StockCode='"
						+ temp.getStockCode()
						+ "';";
				System.out.println("String : " + qry);
				ResultSet rs3 = stmt.executeQuery(qry);
				rs3.next();
				temp.setStockName(rs3.getString(1));
				rs3.close();
			}
			user.setStocksOwned(owned);
			rs2.close();
			stmt.close();
		} catch (SQLException e) {
			System.out.println(
				"Error in User manager readStocksOwned :" + e.toString());
			return false;
		}
		return true;
	}
}