// 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.*;
import java.util.Vector;

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

/* 1) cheapest Ticket
2) AND AND
3) isAdmin changed to 7
4) show the Flight_No
5) Name and Password for reservation
6) Query execution: ";" needed?
7) Price in Ticket must be added.  === is this added in the tables yet?
9) Column count for the Bottom function is wrong. */

/*
 * This class implements a graphical login window and a simple text
 * interface for interacting with the branch table
 */
public class testtickles 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 testtickles()
    {
        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)
                {


                }
            }

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

    /* Deleting a Ticket actually sets the Ticket's Canceled status to True (1).
     *
     */
    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);
            }
        }
    }

    /*Find the Number of Empty Seats Remaining of a Particular Class on a Particular Flight
    SELECT  COUNT(Seat.Seat_No)
    FROM        Seat
    WHERE   Seat.Flight_No = 102 AND Seat.IsBusinessClass = 1 AND Seat.IsTaken = 0 */

    private int emptySeatsonFlight(int Flight_No, int isBusinessClass){
        int SeatsRemaining = 0;

        Statement stmt;
        ResultSet rs;
        try
        {

            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT COUNT(Seat.Seat_No) FROM Seat WHERE Seat.Flight_No = "+Flight_No+" (AND isBusinessClass = "+ isBusinessClass +" AND Seat.IsTaken = 0");

            ResultSetMetaData rsmd1 = rs.getMetaData();
            SeatsRemaining=Integer.parseInt(rs.getString(1));
            return SeatsRemaining;


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

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


    /*Returns the a tableData object of all the flights that satisfies the criteria  given.*/
    private tableData queryFlights(
            String DepartureAirport,
            String DeptFromTimeStamp, String DeptToTimeStamp,
            String ArrivalAirport, String ArriveFromTimeStamp, String ArriveToTimeStamp, // else do query 1
            int SortByPrice,    // If this is 1, do query 2
            int BusinessClassQuery, // If this is 1, do query 3
            int EconomyClassQuery, // If this is 1, do query 4
            int CheapestTicketQuery,
            int ExpensiveTicketQuery) throws SQLException{ // If this is 1, do query 5

        Vector<String> FlightsResult = new Vector<String>();
        Vector<String> FlightsResultSortByPrice = new Vector<String>();
        Vector<String> FlightsResultBusiness = new Vector<String>();
        Vector<String> FlightsResultEconomy = new Vector<String>();
        Vector<String> FlightsResultCheapest = new Vector<String>();
        Vector<String> FlightsResultExpensive = new Vector<String>();

        Statement stmt1;
        Statement stmt2;
        Statement stmt3;
        Statement stmt4;
        Statement stmt5;
        Statement stmt6;

        ResultSet rs1;
        ResultSet rs2;
        ResultSet rs3;
        ResultSet rs4;
        ResultSet rs5;
        ResultSet rs6;

        int columnCount_1;
        int columnCount_2;
        int columnCount_3;
        int columnCount_4;
        int columnCount_5;
        int columnCount_6;

        int rowCount1 = 0;
        int rowCount2 =0;
        int rowCount3 =0;
        int rowCount4 =0;
        int rowCount5 =0;
        int rowCount6 =0;

        String[] tableHeader1;
        String[] tableHeader2;
        String[] tableHeader3;
        String[] tableHeader4;
        String[] tableHeader5;
        String[] tableHeader6;


        stmt1 = con.createStatement();
        stmt2 = con.createStatement();
        stmt3 = con.createStatement();
        stmt4 = con.createStatement();
        stmt5 = con.createStatement();
        stmt6 = con.createStatement();

        /*QuickSort*/
        rs1 = stmt1.executeQuery("SELECT fa.Flight_No, fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass "+
                "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa "+ // From
                "WHERE t.Ticket_ID = ts.Ticket_ID " +   // Where
                "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 > "+ ArriveFromTimeStamp+" AND"+
                "fa.Arrive_Timestamp < "+ArriveToTimeStamp+" AND"+
                "fd.Depart_Timestamp  >"+DeptFromTimeStamp+" AND"+
                "fd.Depart_Timestamp < "+DeptToTimeStamp+" AND"+
        " s.IsTaken = 0");

        /*Sort by Price */
        rs2 = stmt2.executeQuery(" SELECT fa.Flight_No, fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass "+
                "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
                "WHERE t.Ticket_ID = ts.Ticket_ID " +   // Where
                "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 > "+ ArriveFromTimeStamp+" AND "+
                "fa.Arrive_Timestamp < "+ArriveToTimeStamp+" AND "+
                "fd.Depart_Timestamp  > "+DeptFromTimeStamp+" AND "+
                "fd.Depart_Timestamp < "+DeptToTimeStamp+" AND "+
        "s.IsTaken = 0 SORT BY t.ticket_price ");

        /*Business Class Only */
        rs3 = stmt3.executeQuery("SELECT fa.Flight_No, fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
                "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
                "WHERE t.Ticket_ID = ts.Ticket_ID " +   // Where
                "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 > "+ ArriveFromTimeStamp+" AND "+
                "fa.Arrive_Timestamp < "+ArriveToTimeStamp+" AND "+
                "fd.Depart_Timestamp  >"+DeptFromTimeStamp+" AND "+
                "fd.Depart_Timestamp < "+DeptToTimeStamp+" AND "+
        "s.IsTaken = 0 AND s.IsBusinessClass = 1 SORT BY t.ticket_price ");

        /*Economy Class Only */
        rs4 = stmt4.executeQuery("SELECT fa.Flight_No, fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
                "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
                "WHERE t.Ticket_ID = ts.Ticket_ID " +   // Where
                "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 > "+ ArriveFromTimeStamp+" AND "+
                "fa.Arrive_Timestamp < "+ArriveToTimeStamp+" AND "+
                "fd.Depart_Timestamp  >"+DeptFromTimeStamp+" AND "+
                "fd.Depart_Timestamp < "+DeptToTimeStamp+" AND "+
        "s.IsTaken = 0 AND s.IsBusinessClass = 0 SORT BY t.ticket_price ");

        /*Cheapest Ticket out of All Flights*/
        rs5 = stmt5.executeQuery("SELECT fa.Flight_No, fd.Depart_Timestamp, fd.Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass" +
        "WHERE Ticket_Price = (SELECT min(ticket_price) FROM ticket");

        /*Expensive Ticket out of All Flights*/
        rs6 = stmt5.executeQuery("SELECT fa.Flight_No, fd.Depart_Timestamp, fd.Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass" +
        "WHERE Ticket_Price = (SELECT max(ticket_price) FROM ticket");

        ResultSetMetaData rsmd1 = rs1.getMetaData();
        ResultSetMetaData rsmd2 = rs2.getMetaData();
        ResultSetMetaData rsmd3 = rs3.getMetaData();
        ResultSetMetaData rsmd4 = rs4.getMetaData();
        ResultSetMetaData rsmd5 = rs5.getMetaData();
        ResultSetMetaData rsmd6 = rs6.getMetaData();

        columnCount_1 = rsmd1.getColumnCount();
        columnCount_2 = rsmd2.getColumnCount();
        columnCount_3 = rsmd3.getColumnCount();
        columnCount_4 = rsmd4.getColumnCount();
        columnCount_5 = rsmd5.getColumnCount();
        columnCount_6 = rsmd6.getColumnCount();

        while(rs1.next())
        {
            for(int i=1;i<columnCount_1+1;i++){
                FlightsResult.add(rs1.getString(i));
                rowCount1++;
            }
        }

        while(rs2.next())
        {
            for(int i=1;i<columnCount_2+1;i++){
                FlightsResultSortByPrice.add(rs2.getString(i));
                rowCount2++;
            }
        }

        while(rs3.next())
        {
            for(int i=1;i<columnCount_3+1;i++){
                FlightsResultBusiness.add(rs3.getString(i));
                rowCount3++;
            }
        }

        while(rs4.next())
        {
            for(int i=1;i<columnCount_4+1;i++){
                FlightsResultEconomy.add(rs4.getString(i));
                rowCount4++;
            }
        }

        while(rs5.next())
        {
            for(int i=1;i<columnCount_5+1;i++){
                FlightsResultCheapest.add(rs5.getString(i));
                rowCount5++;
            }
        }

        while(rs6.next())
        {
            for(int i=1;i<columnCount_6+1;i++){
                FlightsResultCheapest.add(rs6.getString(i));
                rowCount6++;
            }
        }

        tableHeader1 = new String[columnCount_1];
        for (int i=0; i<columnCount_1; i++) {tableHeader1[i] = rsmd1.getColumnName(i+1);}

        tableHeader2 = new String[columnCount_2];
        for (int i2=0; i2<columnCount_1; i2++) {tableHeader2[i2] = rsmd2.getColumnName(i2+1);}

        tableHeader3 = new String[columnCount_3];
        for (int i3=0; i3<columnCount_1; i3++) {tableHeader3[i3] = rsmd3.getColumnName(i3+1);}

        tableHeader4 = new String[columnCount_4];
        for (int i4=0; i4<columnCount_1; i4++) {tableHeader4[i4] = rsmd4.getColumnName(i4+1);}

        tableHeader5 = new String[columnCount_5];
        for (int i5=0; i5<columnCount_1; i5++) {tableHeader5[i5] = rsmd5.getColumnName(i5+1);}

        tableHeader6 = new String[columnCount_5];
        for (int i6=0; i6<columnCount_1; i6++) {tableHeader5[i6] = rsmd5.getColumnName(i6+1);}


    if(SortByPrice==1){
        tableData FlightsResutSortByPriceData= new tableData(tableHeader2, columnCount_2, rowCount2, FlightsResultSortByPrice);
        return FlightsResutSortByPriceData;
    }
    else if(BusinessClassQuery==1){
        tableData BusinessClassQueryData= new tableData(tableHeader3, columnCount_3, rowCount3, FlightsResultBusiness);
        return BusinessClassQueryData;
    }
    else if(EconomyClassQuery==1){
        tableData EconomyClassData = new tableData(tableHeader4, columnCount_4, rowCount4, FlightsResultEconomy);
        return EconomyClassData;
    }
    else if(CheapestTicketQuery==1){
        tableData CheapestTicketData = new tableData(tableHeader5, columnCount_5, rowCount5, FlightsResultCheapest);
        return CheapestTicketData;
    }
    else if(ExpensiveTicketQuery==1){
        tableData ExpensiveTicketData = new tableData(tableHeader6, columnCount_6, rowCount6, FlightsResultCheapest);
        return ExpensiveTicketData;
    }
    else{
        tableData FlightsResultData= new tableData(tableHeader1, columnCount_1, rowCount1, FlightsResult);
        return FlightsResultData;
    }


}

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

    try
    {
        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 = "+Flight_No+" AND Seat.Flight_No = FlightDeparture.Flight_No AND  Seat.Flight_No = FlightArrival.Flight_No"); //and and

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

/* SELECT Name, Email, PhoneNum, DOB
FROM Person, TicketForPerson tp, TickerForSeat ts, Seat s
WHERE Person.cid = tp.cid AND tp.ticket_ID = ts.ticket_ID AND ts.seat_no = s.seat_no AND s.isbusinessclass = 1 ;
 */
public tableData firstClassPassengers() throws SQLException{

    Vector<String> firstClass = new Vector<String>();
    int columnCount = 0;
    int rowCount=0;

    String[] tableHeader;

    Statement stmt;
    ResultSet rs;


    stmt = con.createStatement();
    rs = stmt.executeQuery("SELECT Name, Email,  PhoneNum, DOB " +
            "FROM Person, TicketForPerson tp, TickerForSeat ts, Seat s " +
            "WHERE Person.cid = tp.cid AND tp.ticket_ID = ts.ticket_ID AND ts.seat_no = s.seat_no AND s.isbusinessclass = 1");

    ResultSetMetaData rsmd = rs.getMetaData();
    columnCount = rsmd.getColumnCount();

    while(rs.next())
    {
        for(int i=1;i<columnCount+1;i++){
            firstClass .add(rs.getString(i));
        }
        rowCount++;
    }

    tableHeader = new String[columnCount];

    for (int i=0; i<columnCount; i++) {tableHeader[i] = rsmd.getColumnName(i+1);}

    tableData firstClassData= new tableData(tableHeader, columnCount, rowCount, firstClass);
    return firstClassData;

}




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(7, isAdmin);        // changed to 7

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

/*Assuming we know Email, Password, and Flight_No */

private void insertReservation(int Ticket_ID, int Email, int Price, int Password, String Payment_date, int Payment_ID, int Seat_No, int Flight_No)
{

    PreparedStatement  psNewTicketForPerson;
    PreparedStatement  psNewPaysFor;
    PreparedStatement  psNewTransactTicket;
    PreparedStatement  psNewTicket;
    PreparedStatement  psNewTicketForSeat;
    PreparedStatement  psUpdateSeat;
    Statement stmt;
    ResultSet rs;


    try
    {

        stmt = con.createStatement();
        rs = stmt .executeQuery("SELECT Person.CID FROM Person WHERE Person.Email = "+Email+" AND Person.Password= "+Password);
        int CID =  rs.getInt("CID");    //will this work? its getInt


        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);
        psNewTicket.setInt(1, Price);

        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();
        stmt.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 deletePerson(String Email, String Password)
{

    PreparedStatement  psDeletePerson;


    try
    {
        //psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
        psDeletePerson = con.prepareStatement("DELETE FROM Person WHERE Email = " +Email + " and Password = " +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 tableData showReservations(String Email, String Password) throws SQLException
{
    Vector<String> personvector = new Vector<String>();
    int columnCount=0;
    int rowCount=0;

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

    String[] tableHeader;

    Statement  stmt;
    ResultSet  rs;

    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 Person.Password = "+Password+" AND Person.Email = "+Email+" AND 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");

    ResultSetMetaData rsmd = rs.getMetaData();
    Parameters p = new Parameters();
    columnCount = rsmd.getColumnCount();

    while(rs.next())
    {

        FlightDepartsFrom__TimeStamp = rs.getString("FlightDepartsFrom.TimeStamp");
        FlightArrivesAt__TimeStamp = rs.getString("FlightArrivesAt.TimeStamp");
        FlightDepartsFrom__Airport_ID = rs.getString("FlightDepartsFrom__Airport_ID");
        FlightArrivesAt__Airport_ID = rs.getString("FlightArrivesAt.Airport_ID");
        FlightDepartsFrom__Flight_No = rs.getString("FlightDepartsFrom.Flight_No");
        Seat__Seat_No = rs.getString("Seat.Seat_No");
        Seat__IsBusinessClass = rs.getString("Seat.IsBusinessClass");
        Ticket__Ticket_ID = rs.getString("Ticket.Ticket_ID");
        PaysFor__Payment_ID = rs.getString("PaysFor.Payment_ID");
        Person__CID = rs.getString("Person.CID");
        Person__Email = rs.getString("Person.Email");
        Person__PhoneNum = rs.getString("Person.PhoneNum");
        Person__DOB = rs.getString("Person.DOB");
        Person__Name = rs.getString("Person.Name");

        personvector.add(FlightDepartsFrom__TimeStamp);
        personvector.add(FlightArrivesAt__TimeStamp);
        personvector.add(FlightDepartsFrom__Airport_ID);
        personvector.add(FlightArrivesAt__Airport_ID);
        personvector.add(FlightDepartsFrom__Flight_No);
        personvector.add(Seat__Seat_No);
        personvector.add(Seat__IsBusinessClass);
        personvector.add(Ticket__Ticket_ID);
        personvector.add(PaysFor__Payment_ID);
        personvector.add(Person__CID);
        personvector.add(Person__Email);
        personvector.add(Person__PhoneNum);
        personvector.add(Person__DOB);
        personvector.add(Person__Name);

        rowCount++;
        stmt.close();
    }

    tableHeader = new String[columnCount];
    for (int i=0; i<columnCount; i++) {tableHeader[i] = rsmd.getColumnName(i+1);}

    tableData queryFlightsData = new tableData(tableHeader, columnCount, rowCount, personvector);
    return queryFlightsData;

}


public static void main(String args[])
{
    new testtickles();
}
}