import java.io.Console;
import java.sql.*;

/**
 *
 * @author Payton M. Dunning &
 * @author Evan J. Overweg
 *
 * Java program that interacts with a series of mySQL tables that resemble a very simple banking system.
 * This program allows the user to either transfer money between a saving and checking account, or view all account under a specific customer.
 */
public class bank {

	Connection conn = null;
	Statement sttmnt = null;
	ResultSet rSet = null;

	/** Connects the user to the MTU database pmdunnin in order to use the simple banking system.
	 * @return - Returns a 1 on success. Otherwise, returns a 0 if the connection fails.
	 */
	public int connectDB() {
		String username = "";
		String password = "";

		// Gets the user's username and password for use in connecting to the database.
		try {
			Console console = System.console();

			if(console == null) {
				System.out.println("There was an issue with the console. Please run the rerun the program in terminal");
				System.out.println("For example: java -cp '.:SQL JAR FILE PATH HERE' bank FUNCTION_NAME ARGUMENTS");
				return 0;
			}

			// Gets the user's username and password.
			username = console.readLine("Please enter in your username:");
			password = String.valueOf(console.readPassword("Please enter in your password:"));

		} catch (Exception exc1) {
			exc1.printStackTrace();
		}

		// Uses the given username and password to connect to the bank system's database.
		try {
			conn = DriverManager.getConnection("jdbc:mysql://classdb.it.mtu.edu/pmdunnin", username, password);

		} catch (SQLException exc2) {
			System.out.println(exc2.getMessage());
			exc2.printStackTrace();
			return 1;
		}

		return 1;
	}

	/**
	 * Disconnects the user from the database server.
	 */
	public void disconnect() {

		try {
			conn.close();

		} catch (SQLException exc) {
			System.out.println("SQLException: " + exc.getMessage());
			System.out.println("SQLState: " + exc.getSQLState());
			System.out.println("VendorError: " + exc.getErrorCode());
		}
	}

	/**
	 * Transfers the given amount of money, balanceAmount, from a given saving account into a given saving account.
	 * @param savingAccountNum - The account number for the saving account that will have money transfered into it.
	 * @param checkingAccountNum - The account number for the checking account that will have money transfered from it.
	 * @param balanceAmount - The amount of money to be transfered.
	 * @return - Returns a 1 on successful transfer. Returns 0 if the transfer fails at any point.
	 */
	public int transfer(String savingAccountNum, String checkingAccountNum, double balanceAmount) {

		PreparedStatement statement = null;
		ResultSet rSet = null;
		int rowCount;
		//Console transferConsole = System.console();

		double oldSavingBalance = 0;
		double oldCheckingBalance = 0;
		double newSavingBalance = 0;
		double newCheckingBalance = 0;

		//System.out.println("Transfer Test 1");
		// Start Transaction:
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
		} catch (SQLException exc3) {
			exc3.printStackTrace();
			return 0;
		}

		//System.out.println("Transfer Test 2");
		// Checks if savingAccountNum is valid.  If it is, see if it has enough money in it to execute the transfer.
		try {
			statement = conn.prepareStatement("SELECT * FROM saving WHERE account_number = ?");
			statement.setString(1, savingAccountNum);
			rSet = statement.executeQuery();
			// .next returns FALSE if at the last. In this case, it means that the account doesn't exist.
			if( rSet.next() ) {
				oldSavingBalance = rSet.getDouble(3);
				System.out.println("\nSaving Account Valid, Current Balance: " + oldSavingBalance);

				if ( oldSavingBalance < balanceAmount) {
					System.out.println("This savings account does not have enough money in it to complete the transfer.");
					conn.rollback();
					return 0;
				}

			} else {
				System.out.println("The given savings account does not exist. Please use a valid account number.");
				conn.rollback();
				return 0;
			}

		} catch (SQLException exc) {
			// Handle any errors
			System.out.println("SQLException: " + exc.getMessage());
			System.out.println("SQLState: " + exc.getSQLState());
			System.out.println("VendorError: " + exc.getErrorCode());
		}

		// Reset statement and rSet just to be safe;
		statement = null;
		rSet = null;

		// Checks if checkingAccountNum is valid.
		try {
			statement = conn.prepareStatement("SELECT * FROM checking WHERE account_number = ?");
			statement.setString(1, checkingAccountNum);
			rSet = statement.executeQuery();

			if (rSet.next() ) {
				oldCheckingBalance = rSet.getDouble(3);
				System.out.println("\nChecking Account Valid, Current Balance: " + oldCheckingBalance);
			} else {
				System.out.println("The given checkings account does not exist. Please use a valid account number.");
				conn.rollback();
				return 0;
			}

		} catch (SQLException exc) {
			// Handle any errors
				System.out.println("SQLException: " + exc.getMessage());
				System.out.println("SQLState: " + exc.getSQLState());
				System.out.println("VendorError: " + exc.getErrorCode());
		}

		// Reset statement and rSet just to be safe;
			statement = null;
			rSet = null;

		// Once input has been checked, begin performing the actual transfer.
		try {
			statement = conn.prepareStatement("UPDATE checking SET balance = balance + ? WHERE account_number = ?");
			statement.setDouble(1, balanceAmount);
			statement.setString(2, checkingAccountNum);
			int rowsUpdated = statement.executeUpdate();

			if (rowsUpdated != 1) {
				System.out.println("Something went wrong with transfering money to the checking account. Please try again later.");
				conn.rollback();
				return 0;
			}

		} catch (SQLException exc) {
			// Handle any errors
			System.out.println("SQLException: " + exc.getMessage());
			System.out.println("SQLState: " + exc.getSQLState());
			System.out.println("VendorError: " + exc.getErrorCode());
		}

		// Reset statement and rSet just to be safe;
			statement = null;
			rSet = null;

		try {
			statement = conn.prepareStatement("UPDATE saving SET balance = balance - ? WHERE account_number = ?");
			statement.setDouble(1, balanceAmount);
			statement.setString(2, savingAccountNum);
			int rowsUpdated = statement.executeUpdate();

			if (rowsUpdated != 1) {
				System.out.println("Something went wrong with transfering money from the saving account. Please try again later.");
				conn.rollback();
				return 0;
			}

		} catch (SQLException exc) {
			// Handle any errors
			System.out.println("SQLException: " + exc.getMessage());
			System.out.println("SQLState: " + exc.getSQLState());
			System.out.println("VendorError: " + exc.getErrorCode());
		}


		try {
			conn.commit();
		} catch (SQLException exc) {
			// Handle any errors
			System.out.println("SQLException: " + exc.getMessage());
			System.out.println("SQLState: " + exc.getSQLState());
			System.out.println("VendorError: " + exc.getErrorCode());
		}

		System.out.println("\nTransfer Completed!\n");

		newCheckingBalance = oldCheckingBalance + balanceAmount;
		newSavingBalance = oldSavingBalance - balanceAmount;
		// Displays the balances of the saving and checking accounts before and after the transfer.
		System.out.println("| Pre-Transfer Savings Account Balance = " + oldSavingBalance);
		System.out.println("| Pre-Transfer Checkings Account Balance = " + oldCheckingBalance);
		System.out.println("| Post-Transfer Savings Account Balance = " + newSavingBalance);
		System.out.println("| Post-Transfer Checkings Account Balance = " + newCheckingBalance + "\n");

		return 1;
	}

	public int display(int customerID) {
		try {
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
        } catch (SQLException exc3) {
            exc3.printStackTrace();
            return 0;
        }

        //
        try {
            //first confirm that customer_id exists

            PreparedStatement statement = conn.prepareStatement("SELECT * FROM customer WHERE id = 13");
           // statement.setString(1, Integer.toString(customerID));
        	//PreparedStatement statement = conn.prepareStatement("SELECT * FROM customer");
            ResultSet rSet = statement.executeQuery();
            System.out.println("Fetch Size = " + rSet.getFetchSize());
            /*if(rSet.getFetchSize() != 1)
            {

                //customer doesn't exist
                System.out.println("Customer ID "+ customerID +" not found");
                conn.rollback();
            }
            else
            {*/
            	if(rSet.next()){
                	System.out.println("Customer Name: " + rSet.getString(1));
                }
                //checking account statement
                statement = conn.prepareStatement("SELECT balance FROM checking WHERE customer_id = ?");
                statement.setString(1, Integer.toString(customerID));
                rSet = statement.executeQuery();
                if(rSet.getFetchSize() < 1) {
                    System.out.printf("No checking accounts found for customer %d", customerID);
                    conn.rollback();
                }
                else {
                    while(rSet.next())
                    {
                        System.out.println(rSet.getString("balance"));
                    }
                }

                //savings account statement
                statement = conn.prepareStatement("SELECT balance FROM saving WHERE customer_id = ?");
                statement.setString(1, Integer.toString(customerID));
                rSet = statement.executeQuery();
                if(rSet.getFetchSize() < 1) {
                    System.out.printf("No saving accounts found for customer %d", customerID);
                    conn.rollback();
                }
                else {
                    //print saving account balances here
                    while(rSet.next())
                    {
                        System.out.println(rSet.getString("balance"));
                    }
                }
                conn.commit();
            //}

            //System.out.println("Test");
        } catch (SQLException exc4) {
            // Handle any errors
            System.out.println("SQLException: " + exc4.getMessage());
            System.out.println("SQLState: " + exc4.getSQLState());
            System.out.println("VendorError: " + exc4.getErrorCode());
        }

        return 1;


	}

	public static void main(String args[]) {
		bank mtuBank = new bank();
		//Console mainConsole = System.console();
		int customerID;
		String savingAccount = "";
		String checkingAccount = "";
		String functionSelect = "";
		double balanceAmount;

		if (args.length < 2) {
			System.out.println("Incomplete program arguments. Please run program again.");

		} else {

		functionSelect = args[0];
		mtuBank.connectDB();

		//mainConsole.printf("\n Function Check:\n" + functionSelect + "\n\n");

		// Runs the display function based on command line input.
		if ( functionSelect.equals("display")) {
			System.out.println("\nDisplay Selected");
			//mainConsole.printf("\n Display Check \n");
			//mainConsole.flush();
			customerID = Integer.parseInt(args[1]);
			mtuBank.display(customerID);

		// Runs the transfer function based on command line input.
		} else if (functionSelect.equals("transfer") && args.length >= 4) {
			System.out.println("\nTransfer Selected");
			//mainConsole.printf("\n Transfer Check \n");
			//mainConsole.flush();
			savingAccount = args[1];
			checkingAccount = args[2];
			balanceAmount = Double.parseDouble(args[3]);
			mtuBank.transfer(savingAccount, checkingAccount, balanceAmount);

		}
		//mainConsole.printf("\n\n" + "args length:" + args.length + "\n\n");

		//mainConsole.printf("\nPrint Check\n\n");
		//mainConsole.flush();

		mtuBank.disconnect();

		}
	}
}