// to compile this program you need to include hb15.zip
// in the CLASSPATH environment variable

// We need to import the java.sql package to use JDBC
import java.security.Policy.Parameters;
import java.sql.*;

// for reading from the command line
import java.io.*;

// for the login window
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;

// for Format.printf() and Parameters.add()
import com.braju.format.*;
/* CON */

/*
 * This class implements a graphical login window and a simple text
 * interface for interacting with the branch table
 */
public class branch implements ActionListener
{
	// command line reader                                                           Creates an InputStreamReader that uses the default charset.           in, the "standard" input stream, already open and ready to supply input data, ie keyboard input.
	private BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
	// BufferedReader reads text from a character-input stream, buffering characters so as to provide for the efficient reading of chars, arrays, lines.
	// Buffer size may be specified, or a default size (large enough for most purposes) is used.
	// Each read request made of a Reader causes a corresponding read request to be made of the underlying character or byte stream. Advisable to swap a BufferedReader around any Reader whose read[] operations may be costly.


	/* - A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.
     - A Connection object's databse is able to provide info describing its tables, SQL grammar, procedures, etc, obtained with the getMetaData method.
     - When configuring Connection, JDBC apps should use the appropriate Connection method such as setAutoCommit or setTransactionIsolation.
     - Apps should not invoke SQL commands directly to change the connecction's config when  there is a JDB method already available.
     - By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement.
     - If auto-commit mode has been disabled, the method "commit" must be called explicitly in order to commit changes, or else changes will not be saved.

	 */private Connection con;


	 // user is allowed 3 login attempts
	 private int loginAttempts = 0;

	 // components of the login window
	 // JTextField is a lightweight component that allows the editing of a single line of text.
	 private JTextField usernameField;
	 private JPasswordField passwordField;
	 private JFrame mainFrame;


	 /*
	  * constructs login window and loads JDBC driver
	  */
	 public branch()
	 {
		 mainFrame = new JFrame("User Login");

		 JLabel usernameLabel = new JLabel("Enter username: ");
		 JLabel passwordLabel = new JLabel("Enter password: ");

		 usernameField = new JTextField(10);
		 passwordField = new JPasswordField(10);
		 passwordField.setEchoChar('*');

		 JButton loginButton = new JButton("Log In");

		 JPanel contentPane = new JPanel();
		 mainFrame.setContentPane(contentPane);


		 // layout components using the GridBag layout manager

		 GridBagLayout gb = new GridBagLayout();
		 GridBagConstraints c = new GridBagConstraints();

		 contentPane.setLayout(gb);
		 contentPane.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));

		 // place the username label
		 c.gridwidth = GridBagConstraints.RELATIVE;
		 c.insets = new Insets(10, 10, 5, 0);
		 gb.setConstraints(usernameLabel, c);
		 contentPane.add(usernameLabel);

		 // place the text field for the username
		 c.gridwidth = GridBagConstraints.REMAINDER;
		 c.insets = new Insets(10, 0, 5, 10);
		 gb.setConstraints(usernameField, c);
		 contentPane.add(usernameField);

		 // place password label
		 c.gridwidth = GridBagConstraints.RELATIVE;
		 c.insets = new Insets(0, 10, 10, 0);
		 gb.setConstraints(passwordLabel, c);
		 contentPane.add(passwordLabel);

		 // place the password field
		 c.gridwidth = GridBagConstraints.REMAINDER;
		 c.insets = new Insets(0, 0, 10, 10);
		 gb.setConstraints(passwordField, c);
		 contentPane.add(passwordField);

		 // place the login button
		 c.gridwidth = GridBagConstraints.REMAINDER;
		 c.insets = new Insets(5, 10, 10, 10);
		 c.anchor = GridBagConstraints.CENTER;
		 gb.setConstraints(loginButton, c);
		 contentPane.add(loginButton);

		 // register password field and OK button with action event handler
		 passwordField.addActionListener(this);
		 loginButton.addActionListener(this);

		 // anonymous inner class for closing the window
		 mainFrame.addWindowListener(new WindowAdapter()
		 {
			 public void windowClosing(WindowEvent e)
			 {
				 System.exit(0);
			 }
		 });

		 // size the window to obtain a best fit for the components
		 mainFrame.pack();

		 // center the frame
		 Dimension d = mainFrame.getToolkit().getScreenSize();
		 Rectangle r = mainFrame.getBounds();
		 mainFrame.setLocation( (d.width - r.width)/2, (d.height - r.height)/2 );

		 // make the window visible
		 mainFrame.setVisible(true);

		 // place the cursor in the text field for the username
		 usernameField.requestFocus();

		 try
		 {
			 // Load the Oracle JDBC driver
			 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		 }
		 catch (SQLException ex)
		 {
			 System.out.println("Message: " + ex.getMessage());
			 System.exit(-1);
		 }
	 }


	 /*
	  * connects to Oracle database named ug using user supplied username and password
	  */
	 private boolean connect(String username, String password)
	 {
		 String connectURL = "jdbc:oracle:thin:@dbhost.ugrad.cs.ubc.ca:1521:ug";
		 											// url: a databse url of the form "jdbc:subprotocol:subname".
					 try
					 {
						 		/*The basic service for managing a set of JDBC drivers is called DriverManager.
						 		 Will attempt to load the driver classes referenced in the "jdbc.drivers" system property. This allows a user to
						 		 customize the JDBC Drivers used by their app.
						 		 getConnection and getDrivers: must include META-INF/services/java.sql.Driver. This file contains the name of the JDBC drivers implementation of java.sql.Diver, ie to load my.sql.Driver class, the META-INF/services/java.sql.Driver file would contain:
						 		 my.sql.Driver. When the method getConnection is called, the DriverManager will attempt to locate a suitable driver form amongst those loaded at initialization and those loaded explicit using the same classloader as the current applet or application.
						 		*/
								 con = DriverManager.getConnection(connectURL,username,password);
								 		// getConnection attempts to establish a connection to the given database URL. DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.
								 		// url: a databse url of the form "jdbc:subprotocol:subname".
								 System.out.println("\nConnected to Oracle!");
								 return true;
					 }
					 catch (SQLException ex)
					 {
								 System.out.println("Message: " + ex.getMessage());
								 return false;
					 }
	 }


	 /*
	  * event handler for login window
	  */
	 public void actionPerformed(ActionEvent e)
	 {
		 if ( connect(usernameField.getText(), String.valueOf(passwordField.getPassword())) )
		 {
			 // if the username and password are valid,
			 // remove the login window and display a text menu
			 mainFrame.dispose();
			 showMenu();
		 }
		 else
		 {
			 loginAttempts++;

			 if (loginAttempts >= 3)
			 {
				 mainFrame.dispose();
				 System.exit(-1);
			 }
			 else
			 {
				 // clear the password
				 passwordField.setText("");
			 }
		 }

	 }


	 /*
	  * displays simple text interface
	  */
	 private void showMenu()
	 {
		 int choice;
		 boolean quit;

		 quit = false;

		 try
		 {
			 // disable auto commit mode
			 con.setAutoCommit(false);

			 while (!quit)
			 {
				 System.out.print("\n\nPlease choose one of the following: \n");
				 System.out.print("1.  Insert branch\n");
				 System.out.print("2.  Delete branch\n");
				 System.out.print("3.  Update branch\n");
				 System.out.print("4.  Show branch\n");
				 System.out.print("5.  Quit\n>> ");

				 choice = Integer.parseInt(in.readLine());

				 System.out.println(" ");

				 switch(choice)
				 {
				 case 1:  insertBranch(); break;
				 case 2:  deleteBranch(); break;
				 case 3:  updateBranch(); break;
				 case 4:  showBranch(); break;
				 case 5:  quit = true;
				 }
			 }

			 con.close();
			 in.close();
			 System.out.println("\nGood Bye!\n\n");
			 System.exit(0);
		 }
		 catch (IOException e)
		 {
			 System.out.println("IOException!");

			 try
			 {
				 con.close();
				 System.exit(-1);
			 }
			 catch (SQLException ex)
			 {
				 System.out.println("Message: " + ex.getMessage());
			 }
		 }
		 catch (SQLException ex)
		 {
			 System.out.println("Message: " + ex.getMessage());
		 }
	 }


	 /*
	  * inserts a Person. Note the original function did not have any parameters; I'm unsure whether to pass these variables into
	  * this function or just do in.readline() directly from the prompt.
	  */
	 private void insertPerson(int CID, String Name, String PhoneNum, String DOB, String Password, String Email, int isAdmin)
	 {

		 PreparedStatement  ps;
		 /*PreparedStatement is an object that represents a precompiled SQL statement.
		  * A SQL statement is precompiled and stored in "ps", which can be used to efficiently execute it couple times.
		  * The methods setShort, setString for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter.
		  * I.e. if the IN parameter has SQL type INTEGER, then the method setInt should be used.  If arbitrary parameter type conversions are required, the method
		  * setObject should be used with a target SQL type.
		  * In the following example of setting a parameter, con represents an active connection:

   PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                     SET SALARY = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)


		  */

		 try
		 {
			 ps = con.prepareStatement("INSERT INTO branch VALUES (?,?,?,?,?,?,?)");


			// System.out.print("\nCID: ");
			 CID = Integer.parseInt(in.readLine()); 	// Since this is Integer .
			 ps.setInt(1, CID);										// This must be ps.setInt

			// System.out.print("\nName: ");
			 Name = in.readLine();
			 ps.setString(2, Name);

			// System.out.print("\nPhoneNum ");
			 PhoneNum = in.readLine();
			 ps.setString(3, PhoneNum);

			 DOB = in.readLine();
			 ps.setString(4, DOB);

			 Password = in.readLine();
			 ps.setString(5, Password);

			 Email = in.readLine();
			 ps.setString(6, Email);

			 isAdmin = Integer.parseInt( in.readLine());
			 ps.setInt(6, isAdmin);

			 ps.executeUpdate();  // Executes  ps = con.prepareStatement("INSERT INTO branch VALUES (?,?,?,?,?,?,?)");
			 											// Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

//			 Returns:
//			 either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing
//			 ps.ex

			 // commit work
			 con.commit();

			 ps.close();
		 }
		 catch (IOException e)
		 {
			 System.out.println("IOException!");
		 }
		 catch (SQLException ex)
		 {
			 System.out.println("Message: " + ex.getMessage());
			 try
			 {
				 // undo the insert
				 con.rollback();

			 }
			 catch (SQLException ex2)
			 {
				 System.out.println("Message: " + ex2.getMessage());
				 System.exit(-1);
			 }
		 }
	 }


	 /*
	  * deletes a branch
	  */
	 private void deleteBranch()
	 {
		 int                bid;
		 PreparedStatement  ps;

		 try
		 {
			 ps = con.prepareStatement("DELETE FROM branch WHERE branch_id = ?");

			 System.out.print("\nBranch ID: ");
			 bid = Integer.parseInt(in.readLine());
			 ps.setInt(1, bid);

			 int rowCount = ps.executeUpdate();

			 if (rowCount == 0)
			 {
				 System.out.println("\nBranch " + bid + " does not exist!");
			 }

			 con.commit();

			 ps.close();
		 }
		 catch (IOException e)
		 {
			 System.out.println("IOException!");
		 }
		 catch (SQLException ex)
		 {
			 System.out.println("Message: " + ex.getMessage());

			 try
			 {
				 con.rollback();
			 }
			 catch (SQLException ex2)
			 {
				 System.out.println("Message: " + ex2.getMessage());
				 System.exit(-1);
			 }
		 }
	 }


	 /*
	  * updates the name of a branch
	  */
	 private void updateBranch()
	 {
		 int                bid;
		 String             bname;
		 PreparedStatement  ps;

		 try
		 {
			 ps = con.prepareStatement("UPDATE branch SET branch_name = ? WHERE branch_id = ?");

			 System.out.print("\nBranch ID: ");
			 bid = Integer.parseInt(in.readLine());
			 ps.setInt(2, bid);

			 System.out.print("\nBranch Name: ");
			 bname = in.readLine();
			 ps.setString(1, bname);

			 int rowCount = ps.executeUpdate();
			 if (rowCount == 0)
			 {
				 System.out.println("\nBranch " + bid + " does not exist!");
			 }

			 con.commit();

			 ps.close();
		 }
		 catch (IOException e)
		 {
			 System.out.println("IOException!");
		 }
		 catch (SQLException ex)
		 {
			 System.out.println("Message: " + ex.getMessage());

			 try
			 {
				 con.rollback();
			 }
			 catch (SQLException ex2)
			 {
				 System.out.println("Message: " + ex2.getMessage());
				 System.exit(-1);
			 }
		 }
	 }


	 /*
	  * display information about branches
	  */
	 private void showBranch()
	 {
		 String     bid;
		 String     bname;
		 String     baddr;
		 String     bcity;
		 String     bphone;
		 Statement  stmt;
		 ResultSet  rs;

		 try
		 {
			 stmt = con.createStatement();
			 rs = stmt.executeQuery("SELECT * FROM branch");

			 // get info on ResultSet
			 /* An object that can be used to get info about the types and properties of the columns in a ResultSet object.
			  * The following code creates the ResultSet object rs, creates the ResultSetMetaData object rsmd, and uses rsmd to
			  * find out how many columns rs has and whether the first column in rs can be used in a WHERE clause.
			  *
			  * ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and
			  * only from the first row to the last row.
			  */
			 ResultSetMetaData rsmd = rs.getMetaData();

			 // get number of columns
			 int numCols = rsmd.getColumnCount();


			 // Java doesn't support variable number of
			 // arguments so we need to store them in a special
			 // Parameters structure. To add arguments to a Parameters
			 // object use the add() method,
			 // e.g. p.add(parameter1).add(parameter2).add(parameter3). . .
			 Parameters p = new Parameters();

			 // display column names;
			 for (int i = 0; i < numCols; i++)
			 {
				 // get column name and print it

				 // The Format class provides the static printf() method
				 // which behaves exactly like the printf() in
				 // the C programming language. So for the line below
				 // the text will be left aligned; it will also have a
				 // minimum and maximum width of 15 characters.
				 Format.printf("%-15.15s", p.add(rsmd.getColumnName(i+1)));
			 }

			 System.out.println(" ");

			 while(rs.next())
			 {
				 // for display purposes get everything from Oracle
				 // as a string

				 // simplified output formatting; truncation may occur

				 bid = rs.getString("branch_id");
				 Format.printf("%-15.15s", p.add(bid));

				 bname = rs.getString("branch_name");
				 Format.printf("%-15.15s", p.add(bname));

				 baddr = rs.getString("branch_addr");
				 if (rs.wasNull())
				 {
					 Format.printf("%-15.15s", p.add(" "));
				 }
				 else
				 {
					 Format.printf("%-15.15s", p.add(baddr));
				 }

				 bcity = rs.getString("branch_city");
				 Format.printf("%-15.15s", p.add(bcity));

				 bphone = rs.getString("branch_phone");
				 if (rs.wasNull())
				 {
					 Format.printf("%-15.15s\n", p.add(" "));
				 }
				 else
				 {
					 Format.printf("%-15.15s\n", p.add(bphone));
				 }
			 }

			 // close the statement;
			 // the ResultSet will also be closed
			 stmt.close();
		 }
		 catch (SQLException ex)
		 {
			 System.out.println("Message: " + ex.getMessage());
		 }
	 }


	 public static void main(String args[])
	 {
		 branch b = new branch();
	 }
}