// 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: deleteFlight(); break;
				case 7: deleteTicket(); break;
				case 8: queryFlights( DepartureAirport,  DeptFromDate,  DeptToDate,
						 ArrivalAirport,  ArrivalFromDate,  ArrivalToDate); break;
				case 8:  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 deleteTicket(String Ticket_ID) {

		PreparedStatement  psDeleteTicket;
		try
		{
			psDeleteTicket = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID="+Ticket_ID);
			psDeleteTicket.executeUpdate();
			con.commit();
			psDeleteTicket.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Message: " + ex.getMessage());

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

	}

	/*Returns the Flight_Num as a String*/
	private String queryFlights(	String DepartureAirport, String DeptFromDate, String DeptToDate,
											String ArrivalAirport, String ArrivalFromDate, String ArrivalToDate,
											int SortByPrice, int SortBySeatsRemaining){

		PreparedStatement psFlightNumber;
		try
		{
			//psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
			psFlightNumber = con.prepareStatement(	"SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, t.ticket_price, s.IsBusinessClass"+
																							"FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+
																							"WHERE t.Ticket_ID = ts.Ticket_ID " +
																							"AND ts.Seat_No = s.Seat_No AND" +
																							"s.Flight_No = fd.Flight_No AND " +
																							"s.Flight_No = fa.Flight_No AND" +
																							"fd.Airport_ID = "+"DepartureAirport"+" AND" +
																							"fa.Airport_ID = "+"ArrivalAirport"+" AND"+
																							"fa.Arrive_Timestamp > "+"ArriveFromDate "+"AND"+
																							"fa.Arrive_Timestamp < "+"ArriveToDate "+"AND"+
																							"fd.Depart_Timestamp  >"+" DeptFromDate "+"AND"+
																							"fd.Depart_Timestamp < "+"DeptToDate "+"AND"+
																							"s.IsTaken = 0");


			psFlightNumber.executeUpdate();

			con.commit();
			psFlightNumber.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Message: " + ex.getMessage());

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

		String FlightNum;


		return FlightNum;


		/*incomplete*/
	}

	private void deleteFlight(int Flight_No) {
		PreparedStatement  psDeleteFlight;
		PreparedStatement  psUpdateTickets;


		try
		{
			//psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
			psDeleteFlight = con.prepareStatement("DELETE FROM Flight WHERE Flight_No ="+Flight_No);

			psUpdateTickets = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID= (SELECT T2.TicketID"+
					"FROM Ticket T2, TicketForSeat, Seat, FlightDeparture, FlightArrival"+
					"WHERE T2.TicketID = TicketForSeat.TicketID AND"+
					"TicketForSeat.Seat_No = Seat.Seat_No AND TicketForSeat.Flight_No = Seat.Flight_No AND Seat.Flight_No = FlightDeparture.Flight_No AND  Seat.Flight_No = FlightArrival.Flight_No");

			psDeleteFlight.executeUpdate();
			psUpdateTickets.executeUpdate();

			con.commit();
			psDeleteFlight.close();
			psUpdateTickets.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Message: " + ex.getMessage());

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


	private void insertPerson(int CID, String Name, String PhoneNum, String DOB, String Password, String Email, int isAdmin)
	{

		PreparedStatement  ps;

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

			ps.setInt(1, CID);										// This must be ps.setInt
			ps.setString(2, Name);
			ps.setString(3, PhoneNum);
			ps.setString(4, DOB);
			ps.setString(5, Password);
			ps.setString(6, Email);
			ps.setInt(6, isAdmin);

			ps.executeUpdate();
			con.commit();

			ps.close();
		}
		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(int Ticket_ID, int CID, String Payment_date, int Payment_ID, int Seat_No, int Flight_No)
	{

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

			psUpdateSeat = con.prepareStatement("UPDATE Seat S SET S.IsTaken = 1 WHERE S.Seat_No = "+Seat_No+" and S.Flight_No ="+Flight_No);

			psNewTicketForPerson.executeUpdate();
			psNewPaysFor.executeUpdate();
			psNewTransactTicket.executeUpdate();
			psNewTicket.executeUpdate();
			psNewTicketForSeat.executeUpdate();
			psUpdateSeat.executeUpdate();
			// commit work
			con.commit();

			psNewTicketForPerson.close();
			psNewPaysFor.close();
			psNewTransactTicket.close();
			psNewTicket.close();
			psNewTicketForSeat.close();
			psUpdateSeat.close();

		}
		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(String Email, String Password)
	{

		PreparedStatement  psDeletePerson;


		try
		{
			//psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
			psDeletePerson = con.prepareStatement("DELETE FROM Person WHERE Email LIKE '%" +Email + "%' and Password LIKE '%" +Password+"%'");

			int rowCount = psDeletePerson.executeUpdate();

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

			con.commit();

			psDeletePerson.close();
		}
		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(String Name, String OldEmail, String OldPassword, String Email, String Password, String PhoneNum, String DOB)
	{
		PreparedStatement ps;
		try
		{
			ps = con.prepareStatement("UPDATE Person P SET P.Name = ?, P.Email = ?, P.Password = ?, P.PhoneNum = ?, P.DOB = ? WHERE P.OldEmail = ? and P.OldPassword = ?");
			ps.setString(1, Name);
			ps.setString(2, Email);
			ps.setString(3, Password);
			ps.setString(4, PhoneNum);
			ps.setString(5, DOB);
			ps.setString(6, OldEmail);
			ps.setString(7, OldPassword);

			con.commit();

			ps.close();
		}

		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 < numFlightsBooked; 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(FlightDepartsFrom__TimeStamp));

				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.getInt("FlightDepartsFrom.Flight_No");
				Format.printf("%-15.15s", p.add(FlightDepartsFrom__Flight_No));

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

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

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

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

				Person__CID = rs.getInt("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();
		}
	}