import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * Manage connection to database and perform SQL statements.
 */
public class BankingSystem {
	// Connection properties
	private static String driver;
	private static String url;
	private static String username;
	private static String password;

	// JDBC Objects
	private static Connection con;
	private static Statement stmt;
	private static ResultSet rs;

	// other global variables
	private static String activeAcc = "A";
	private static String inactiveAcc = "I";

	/**
	 * Initialize database connection given properties file.
	 * @param filename name of properties file
	 */
	public static void init(String filename) {
		try {
			Properties props = new Properties();					// Create a new Properties object
			FileInputStream input = new FileInputStream(filename);	// Create a new FileInputStream object using our filename parameter
			props.load(input);										// Load the file contents into the Properties object
			driver = props.getProperty("jdbc.driver");				// Load the driver
			url = props.getProperty("jdbc.url");					// Load the url
			username = props.getProperty("jdbc.username");			// Load the username
			password = props.getProperty("jdbc.password");			// Load the password
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Test database connection.
	 */
	public static void testConnection() {
		System.out.println(":: TEST - CONNECTING TO DATABASE");
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);
			con.close();
			System.out.println(":: TEST - SUCCESSFULLY CONNECTED TO DATABASE");
			} catch (Exception e) {
				System.out.println(":: TEST - FAILED CONNECTED TO DATABASE");
				e.printStackTrace();
			}
	  }

	/**
	 * Create a new customer.
	 * @param name customer name
	 * @param gender customer gender
	 * @param age customer age
	 * @param pin customer pin
	 * Prints out message that customer has been added and also prints out the customer's id.
	 */
	public static void newCustomer(String name, String gender, String age, String pin)
	{
		Statement stmt = con.createStatement();
		String query = "SELECT ID FROM P1.Customer (INSERT INTO P1.EMPLOYEE VALUES (DEFAULT,'" + name + "','" + gender + "','" + age + "','" + pin + "'))";
		ResultSet rs = stmt.executeQuery(query);

		while(rs.next()){
			int id = rs.getInt(1);
			System.out.println(":: CUSTOMER ID: " + id + ".");
		}

		rs.close();
		stmt.close();

		System.out.println(":: CREATE NEW CUSTOMER - SUCCESS");

		//ids++;

	}

	/**
	 * Open a new account.
	 * @param id customer id
	 * @param type type of account
	 * @param amount initial deposit amount
	 */
	public static void openAccount(String id, String type, String amount)
	{

		Statement stmt = con.createStatement();
		String query = "SELECT Number FROM P1.Account (INSERT INTO P1.ACCOUNT VALUES (DEFAULT,'" + id + "','" + amount + "','" + type + "', DEFAULT))";
		ResultSet rs = stmt.executeQuery(query);

		while(rs.next()){
			int accNum = rs.getInt(1);
			System.out.println(":: ACCOUNT NUMBER: " + accNum + ".");
		}

		rs.close();
		stmt.close();

		System.out.println(":: OPEN ACCOUNT - SUCCESS");

		//numbers++;
	}

	/**
	 * Close an account.
	 * @param accNum account number
	 */
	public static void closeAccount(String accNum)
	{
		Statement stmt = con.createStatement();
		String query = "UPDATE P1.ACCOUNT SET BALANCE = 0, STATUS = '" + inactiveAcc + "' WHERE NUMBER = '" + accNum + "'";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: CLOSE ACCOUNT - SUCCESS");
	}

	/**
	 * Deposit into an account.
	 * @param accNum account number
	 * @param amount deposit amount
	 */
	public static void deposit(String accNum, String amount)
	{
		Statement stmt = con.createStatement();
		String query = "UPDATE P1.ACCOUNT SET BALANCE = BALANCE + " + amount + " WHERE NUMBER = '" + accNum + "'";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: DEPOSIT - SUCCESS");
	}

	/**
	 * Withdraw from an account.
	 * @param accNum account number
	 * @param amount withdraw amount
	 */
	public static void withdraw(String accNum, String amount)
	{
		Statement stmt = con.createStatement();
		String query = "UPDATE P1.ACCOUNT SET BALANCE = BALANCE - " + amount + " WHERE NUMBER = '" + accNum + "'";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: WITHDRAW - SUCCESS");
	}

	/**
	 * Transfer amount from source account to destination account.
	 * @param srcAccNum source account number
	 * @param destAccNum destination account number
	 * @param amount transfer amount
	 */
	public static void transfer(String srcAccNum, String destAccNum, String amount)
	{
		Statement stmt = con.createStatement();
		String query = "UPDATE P1.ACCOUNT SET BALANCE = BALANCE - " + amount + " WHERE NUMBER = '" + srcAccNum + "'";
		String query2 = "UPDATE P1.ACCOUNT SET BALANCE = BALANCE + " + amount + " WHERE NUMBER = '" + destAccNum + "'";
		ResultSet rs = stmt.executeQuery(query);
		ResultSet rs2 = stmt.executeQuery(query2);

		rs.close();
		rs2.close();
		stmt.close();

		System.out.println(":: TRANSFER - SUCCESS");
	}

	/**
	 * Display account summary.
	 * @param accNum account number
	 * Displays account number, balance, and total balance for all accounts under the same customer id.
	 */
	public static void accountSummary(String accNum)
	{
		Statement stmt = con.createStatement();
		String query = "SELECT Number, Balance, COUNT(Balance) AS Total Balance FROM P1.Account WHERE ID = (SELECT ID FROM P1.Account WHERE Number = " + accNum + ") AND Type = '" + activeAcc "'";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: ACCOUNT SUMMARY - SUCCESS");
	}

	/**
	 * Display account summary for admins.
	 * @param custID customer id
	 * Displays account number, balance, and total balance for all accounts under the same customer id.
	 */
	public static void adminAccountSummary(String custID)
	{
		Statement stmt = con.createStatement();
		String query = "SELECT Number, Balance, COUNT(Balance) AS Total FROM P1.Account WHERE ID = " + custID + ") AND Type = '" + activeAcc "'";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: ACCOUNT SUMMARY - SUCCESS");
	}


	/**
	 * Display Report A - Customer Information with Total Balance in Decreasing Order.
	 */
	public static void reportA()
	{
		Statement stmt = con.createStatement();
		String query = "SELECT Customer.ID, Customer.Name, Customer.Age, Customer.Gender, COUNT(Account.Balance) AS Total FROM P1.Customer INNER JOIN P1.Account ON Balance.ID = Customer.ID ORDER BY Total DESC";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: REPORT A - SUCCESS");
	}

	/**
	 * Display Report B - Customer Information with Total Balance in Decreasing Order.
	 * @param min minimum age
	 * @param max maximum age
	 */
	public static void reportB(String min, String max)
	{
		Statement stmt = con.createStatement();
		String query = "SELECT Customer.Age, COUNT(Account.Balance) AS Total FROM P1.Account INNER JOIN P1.Customer ON Balance.ID = Customer.ID WHERE Customer.Age >= '" + min + "' AND Customer.Age <= '" + max + "' GROUP BY Customer.Age ORDER BY Total DESC";
		ResultSet rs = stmt.executeQuery(query);

		rs.close();
		stmt.close();

		System.out.println(":: REPORT B - SUCCESS");
	}
}