import java.io.*;
import java.sql.*;
import java.util.*;

public class banking
{
	String name,acc_no,balance,query;
	Scanner in = new Scanner(System.in);

	public void account_creation()
	{
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection conn=DriverManager.getConnection("jdbc:odbc:embed_db","","");
			Statement statement = conn.createStatement();
			System.out.print("Enter the Account Holder's Name");
			name=in.nextLine();
			query="INSERT INTO Accounts VALUES(\""+name+"\",\""+acc_no+"\",\"0\")";
			ResultSet rset=statement.executeQuery(query);
			System.out.println("Account Created Successfully");
			query="SELECT MAX(acc_no) FROM Accounts";
			rset=statement.executeQuery(query);
			System.out.println("Your Account Number is "+rset.getInt(1));
		}
		catch (Exception E)
		{
			System.out.print("\n\nACCOUNT NOT CREATED\n\n");
			E.printStackTrace();
		}
	}

	public void deposit()
	{
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection conn=DriverManager.getConnection("jdbc:odbc:embed_db","","");
			Statement statement = conn.createStatement();
			System.out.print("Enter the Account Number");
			acc_no=in.nextLine();
			System.out.print("Enter the amount to be deposited:");
			balance=in.nextLine();
			query="UPDATE Accounts SET balance=balance+"+balance+"where acc_no="+acc_no;
			ResultSet rset=statement.executeQuery(query);
			System.out.println("Balance Updated Successfully.");
			query="SELECT balance FROM Accounts WHERE acc_no="+acc_no;
			rset=statement.executeQuery(query);
			System.out.println("Your New Balance is "+rset.getInt(1));
		}
		catch (Exception E)
		{
			System.out.print("\n\nTRANSACTION FAILED\n\n");
			E.printStackTrace();
		}

	}

	public void withdraw()
	{

		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection conn=DriverManager.getConnection("jdbc:odbc:embed_db","","");
			Statement statement = conn.createStatement();
			System.out.print("Enter the Account Number");
			acc_no=in.nextLine();
			System.out.print("Enter the amount to be withdrawn:");
			balance=in.nextLine();
			query="SELECT balance FROM Accounts WHERE acc_no="+acc_no;
			ResultSet rset=statement.executeQuery(query);
			if(rset.getInt(1)>Integer.parseInt(balance))
			{
				query="UPDATE Accounts SET balance=balance-"+balance+"where acc_no="+acc_no;
				rset=statement.executeQuery(query);
				query="SELECT balance FROM Accounts WHERE acc_no="+acc_no;
				rset=statement.executeQuery(query);
				System.out.println("Your New Balance is "+rset.getInt(1));
			}
			else
			{
				System.out.println("INSUFFICIENT BALANCE");
			}
		}
		catch (Exception E)
		{
			System.out.print("\n\nTRANSACTION FAILED\n\n");
			E.printStackTrace();
		}

	}

	public void view()
	{

		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection conn=DriverManager.getConnection("jdbc:odbc:embed_db","","");
			Statement statement = conn.createStatement();
			System.out.print("Enter the Account Number");
			acc_no=in.nextLine();
			query="SELECT * FROM Accounts WHERE acc_no="+acc_no;
			ResultSet rset=statement.executeQuery(query);
			System.out.println("Account Number:"+rset.getInt(1)+"\nAccount Holder Name:"+rset.getString(2)+"\nAccount Balance:"+rset.getInt(3));
		}
		catch(Exception E)
		{
			System.out.println("Invalid Account Number");
			E.printStackTrace();
		}
	}

	public static void main(String[] args)
	{
		System.out.print("  ___            _   _               __  __                                       _        ___         _             \r\n | _ ) __ _ _ _ | |_(_)_ _  __ _    |  \\/  |__ _ _ _  __ _ __ _ ___ _ __  ___ _ _| |_     / __|_  _ __| |_ ___ _ __  \r\n | _ \\/ _` | ' \\| / / | ' \\/ _` |   | |\\/| / _` | ' \\/ _` / _` / -_) '  \\/ -_) ' \\  _|    \\__ \\ || (_-<  _/ -_) '  \\ \r\n |___/\\__,_|_||_|_\\_\\_|_||_\\__, |   |_|  |_\\__,_|_||_\\__,_\\__, \\___|_|_|_\\___|_||_\\__|    |___/\\_, /__/\\__\\___|_|_|_|\r\n                           |___/                          |___/                                |__/                  ");
		banking b = new banking();
		Scanner in = new Scanner(System.in);
		int choice;
		System.out.println("1.Account Creation\n2.Deposit\n3.Withdrawal\n4.View\n5.Exit");
		do
		{
			System.out.print("Enter your choice(1-5):");
			choice=in.nextInt();

			switch(choice)
			{
				case 1 : b.account_creation();
						 break;
				case 2 : b.deposit();
						 break;
				case 3 : b.withdraw();
						 break;
				case 4 : b.view();
						 break;
				case 5 : return;
				default:System.out.println("INVALID CHOICE");
			}

		}while(true);
	}
}