// 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.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.*;


/*
 * 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
    private BufferedReader in = new BufferedReader(new InputStreamReader(System.in));

    private Connection con;

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

    // components of the login window
    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";
      String connectURL = "jdbc:oracle:thin:@localhost:1521:ug";

      try
      {
	con = DriverManager.getConnection(connectURL,username,password);

	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 Person\n");
		System.out.print("2.  Delete Person\n");
		System.out.print("3.  Update Person\n");
		System.out.print("4.  showReservations\n");
		System.out.print("5. insertReservation\n");
		System.out.print("6.  Quit\n>> ");

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

		System.out.println(" ");

		switch(choice)
		{
		   case 1:  insertPerson(); break;
		   case 2:  deletePerson(); break;
		   case 3:  updatePerson(); break;
		   case 4:  showReservations(); break;
		   case 5:  insertReservation(); break;
		   case 6:  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());
	}
    }


	 private void insertPerson()
	 {

		 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 Person VALUES (?,?,?,?,?,?,?)");


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

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

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

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

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

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

			 int 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);
				 }
			 }
	 }


	 private void insertReservation()
	 {

		 PreparedStatement  psNewTicketForPerson;
		 PreparedStatement  psNewPaysFor;
		 PreparedStatement  psNewTransactTicket;
		 PreparedStatement  psNewTicket;
		 PreparedStatement  psNewTicketForSeat;
		 PreparedStatement  psUpdateSeat;

		 try
		 {
			 psNewTicketForPerson = con.prepareStatement("INSERT INTO TicketForPerson VALUES (?,?)");
			 psNewTicketForPerson.setInt(1, Ticket_ID);	// int
			 psNewTicketForPerson.setInt(2, CID);			// int

			 psNewPaysFor = con.prepareStatement("INSERT INTO PaysFor VALUES (?,?,?)");
			 psNewPaysFor.setString(1, Payment_date);	// String
			 psNewPaysFor.setInt(2, Payment_ID);				// int
			 psNewPaysFor.setINt(3,CID);								// int

			 psNewTransactTicket = con.prepareStatement("INSERT INTO TransactTicket VALUES (?,?)");
			 psNewTransactTicket.setInt(1,Ticket_ID);
			 psNewTransactTicket.setInt(2, Payment_ID);

			 psNewTicket = con.prepareStatement("INSERT INTO Ticket VALUES (?)");
			 psNewTicket.setInt(1,Ticket_ID);

			 psNewTicketForSeat = con.prepareStatement("INSERT INTO TicketForSeat VALUES (?,?,?)");
			 psNewTicketForSeat.setInt(1,Ticket_ID);
			 psNewTicketForSeat.setInt(2,Seat_No);
			 psNewTicketForSeat.setInt(3,Flight_No);



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

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

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

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

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

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

			 ps.executeUpdate();  // Executes  ps = con.prepareStatement("INSERT INTO branch VALUES (?,?,?,?,?,?,?)");

			 // 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 deletePerson()
    {
	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 updatePerson()
    {
	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 showReservations()
    {

     String FlightDepartsFrom__TimeStamp;
     String FlightArrivesAt__Timestamp;
     String FlightDepartsFrom__Airport_ID;
     String FlightArrivesAt__Airport_ID;
     int FlightDepartsFrom__Flight_No;
     int Seat__Seat_No;
     int Seat__IsBusinessClass;
     int Ticket__Ticket_ID;
     int PaysFor__Payment_ID;
     int Person__CID;
     String Person__Email;
     String Person__PhoneNum;
     String Person__DOB;
     String Person__Name;

	Statement  stmt;
	ResultSet  rs;

	try
	{
	  stmt = con.createStatement();
	  rs = stmt.executeQuery("SELECT FlightDepartsFrom.TimeStamp, FlightArrivesAt.Timestamp, FlightDepartsFrom.Airport_ID, FlightArrivesAt.Airport_ID, FlightDepartsFrom.Flight_No, Seat.Seat_No, Seat.IsBusinessClass, Ticket.Ticket_ID, PaysFor.Payment_ID, Person.CID, Person.Email, Person.PhoneNum, Person.DOB, Person.Name \" +
	  		"FROM Person, TicketForPerson, PaysFor, TransactTicket, Ticket, TicketForSeat, Seat, FlightDeparture, FlightArrival, Airport \" +
	  		"WHERE TicketForPerson.CID = Person.CID AND \" +
	  		"TicketForPerson.Ticket_ID = Ticket.Ticket_ID AND \" +
	  		"PaysFor.CID = Person.CID AND \" +
	  		"TransactTicket.Payment_ID = PaysFor.Payment_ID AND \" +
	  		"TransactTicket.Ticket_ID = Ticket.Ticket_ID AND \" +
	  		"TicketForSeat.Ticket_ID = Ticket.Ticket_ID AND \" +
	  		"TicketForSeat.Seat_no = Seat.Seat_no AND \" +
	  		"TicketForSeat.Flight_no = Seat.Flight_no AND \" +
	  		"Seat.Flight_no = FlightDeparture.Flight_no AND \" +
	  		"FlightDeparture.Tail_no = Plane.Tail_no AND \" +
	  		"FlightDeparture.Airport_ID = Airport.Airport_ID AND \" +
	  		"FlightArrival.Tail_no = Plane.Tail_no AND \" +
	  		"FlightArrival.Airport_ID = Airport.Airport_ID");

	  // get info on ResultSet
	  ResultSetMetaData rsmd = rs.getMetaData();

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

	  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

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

	      FlightArrivesAt__TimeStamp = rs.getString("FlightArrivesAt.TimeStamp");
	      Format.printf("%-15.15s", p.add(FlightArrivesAt__TimeStamp));

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

	      FlightArrivesAt__Airport_ID = rs.getString("FlightArrivesAt.Airport_ID");
	      Format.printf("%-15.15s", p.add(FlightArrivesAt__Airport_ID));

	      FlightDepartsFrom__Flight_No = rs.getInteger("FlightDepartsFrom.Flight_No");
	      Format.printf("%-15.15s", p.add(FlightDepartsFrom__Flight_No));

	      Seat__Seat_No = rs.getInteger("Seat.Seat_No");
	      Format.printf("%-15.15s", p.add(Seat__Seat_No));

	      Seat__IsBusinessClass = rs.getInteger("Seat.IsBusinessClass");
	      Format.printf("%-15.15s", p.add(Seat__IsBusinessClass));

	      Ticket__Ticket_ID = rs.getInteger("Ticket.Ticket_ID");
	      Format.printf("%-15.15s", p.add(Ticket__Ticket_ID));

	      PaysFor__Payment_ID = rs.getInteger("PaysFor.Payment_ID");
	      Format.printf("%-15.15s", p.add(PaysFor__Payment_ID));

	      Person__CID = rs.getInteger("Person.CID");
	      Format.printf("%-15.15s", p.add(Person__CID));

	      Person__Email = rs.getString("Person.Email");
	      Format.printf("%-15.15s", p.add(Person__Email));

	      Person__PhoneNum = rs.getString("Person.PhoneNum");
	      Format.printf("%-15.15s", p.add(Person__PhoneNum));

	      Person__DOB = rs.getString("Person.DOB");
	      Format.printf("%-15.15s", p.add(Person__DOB));

	      Person__Name = rs.getString("Person.Name");
	      Format.printf("%-15.15s", p.add(Person__Name));


	  // 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();
    }
}