-- populate db with alex's tables and test result
--  tables from email [new tables] Monday April 5
--  entries from email [no subject] Wednesday April 7

-- re-arranged things a bit...




CREATE TABLE Person (
CID INTEGER,
Name CHAR(20),
PhoneNum CHAR(15),
DOB DATE,
Password CHAR(20),
Email CHAR(40),
isAdmin INTEGER,
PRIMARY KEY (Cid),
CHECK(isAdmin = 0 OR isAdmin = 1) );

CREATE TABLE Ticket (
Ticket_ID INTEGER,
ticket_price INTEGER,
PRIMARY KEY (Ticket_ID) );

CREATE TABLE Airport (
Airport_ID CHAR(4),
City CHAR(20),
Country CHAR(20),
PRIMARY KEY (Airport_ID) );

CREATE TABLE Plane (
Tail_No INTEGER,
Model_ID CHAR(20),
BusinessCap INTEGER,
EconomyCap INTEGER,
PRIMARY KEY (Tail_No) ) ;

-- **NOTE: not sure if this is correct, does seat to both departure flightnum and arrives flightnum for a key?

CREATE TABLE FlightDeparture (
Flight_No INTEGER,
Depart_timestamp TIMESTAMP,
Airport_ID CHAR(4) NOT NULL,
Tail_No INTEGER NOT NULL,
PRIMARY KEY (Flight_No),
FOREIGN KEY (Tail_No) REFERENCES Plane,
FOREIGN KEY (Airport_ID) REFERENCES Airport ) ;

CREATE TABLE FlightArrival (
Flight_No INTEGER,
Arrive_timestamp TIMESTAMP,
Airport_ID CHAR(4) NOT NULL,
Tail_No INTEGER NOT NULL,
PRIMARY KEY (Flight_No),
FOREIGN KEY (Tail_No) REFERENCES Plane,
FOREIGN KEY (Airport_ID) REFERENCES Airport ) ;

CREATE TABLE Seat (
IsTaken INTEGER,
IsBusinessClass INTEGER,
Seat_No INTEGER,
Flight_no INTEGER,
PRIMARY KEY (Seat_No, Flight_No),
FOREIGN KEY (Flight_no) REFERENCES FlightDeparture
        ON DELETE CASCADE,
CHECK (IsTaken = 0 OR isTaken = 1),
CHECK (isbusinessclass = 0 OR isbusinessclass = 1) );

CREATE TABLE TicketForPerson (
Ticket_ID INTEGER,
CID INTEGER NOT NULL,
PRIMARY KEY (Ticket_ID),
FOREIGN KEY (Cid) REFERENCES Person
    ON DELETE SET NULL,
FOREIGN KEY (Ticket_ID) REFERENCES Ticket );


CREATE TABLE PaysFor (
Payment_date DATE,
Payment_ID INTEGER,
CID INTEGER NOT NULL,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (CID) REFERENCES Person);


CREATE TABLE TransactTicket (
Ticket_ID INTEGER,
Payment_ID INTEGER NOT NULL,
PRIMARY KEY (Ticket_ID),
FOREIGN KEY (Payment_ID) REFERENCES PaysFor
    ON DELETE SET NULL,
FOREIGN KEY (Ticket_ID) REFERENCES Ticket );


CREATE TABLE TicketForSeat (
Ticket_ID INTEGER,
Seat_No INTEGER,
Flight_No INTEGER,
FOREIGN KEY (Ticket_ID) REFERENCES Ticket,
FOREIGN KEY (Seat_No, Flight_no) REFERENCES Seat,
PRIMARY KEY (Ticket_ID, Seat_No) );












/*April 5 2:00PM*/
/* CID start with 20001; Ticket_ID start with 10001; Payment_ID start with 30001; */
/* FlightNo start with 101; TailNo start with 201; SeatNo start with 1 */



INSERT INTO Person VALUES(20001,'Ji Zhuang','111-111-1111','11-Jan-1971','Pwd001','email1@hotmail.com',1);
-- also admin
INSERT INTO Person VALUES(20002,'Bob Joysi','222-222-2222','12-Feb-1972','Pwd002','email2@hotmail.com',0);
INSERT INTO Person VALUES(20003,'Joy Steph','333-333-3333','13-Mar-1973','Pwd003','email3@hotmail.com',0);
INSERT INTO Person VALUES(20004,'James Be','444-444-4444','14-Apr-1974','Pwd004','email4@hotmail.com',0);
INSERT INTO Person VALUES(20005,'Rich Ohm','555-555-5555','15-May-1975','Pwd005','email5@hotmail.com',0);
INSERT INTO Person VALUES(20006,'Jenn Blah','666-666-6666','16-Jun-1976','Pwd006','email6@hotmail.com',0);
INSERT INTO Person VALUES(20007,'Jackie Pie','777-777-7777','17-Jul-1977','Pwd007','email7@hotmail.com',0);
INSERT INTO Person VALUES(20008,'Mary Hihie','888-888-8888','18-Aug-1978','Pwd008','email8@hotmail.com',0);
INSERT INTO Person VALUES(20009,'Zoey Zeba','999-999-9999','19-Sep-1979','Pwd009','email9@hotmail.com',0);
INSERT INTO Person VALUES(20010,'Rosie Roe','900-900-9000','20-Oct-1980','Pwd010','email10@hotmail.com',0);
INSERT INTO Person VALUES(20011,'Alex Pereira','778-555-1234','23-JUL-1989','Password','alexp@hotmail.com',1);

INSERT INTO Person VALUES(99999,'FLIGHT 101 DUDE','778-555-1334','21-JUL-1489','Password','aDUDE@hotmail.com',0);
-- DONE

INSERT INTO Ticket VALUES(10001,349);
INSERT INTO Ticket VALUES(10002,399);
INSERT INTO Ticket VALUES(10003,1299);
INSERT INTO Ticket VALUES(10004,1299);
INSERT INTO Ticket VALUES(10005,1199);
INSERT INTO Ticket VALUES(10006,230);
INSERT INTO Ticket VALUES(10007,450);
INSERT INTO Ticket VALUES(10008,199);
INSERT INTO Ticket VALUES(10009,199);
INSERT INTO Ticket VALUES(10010,199);
-- new:
INSERT INTO Ticket VALUES(10011, 199);
INSERT INTO Ticket VALUES(10012, 599);
INSERT INTO Ticket VALUES(10013, 999);
INSERT INTO Ticket VALUES(10014, 449);
-- DONE

INSERT INTO Airport VALUES('YVR','Vancouver','CANADA');
INSERT INTO Airport VALUES('SEA','Seattle','UNITED STATES');
INSERT INTO Airport VALUES('PVG','Shanghai','CHINA');
INSERT INTO Airport VALUES('SFO','San Francisco','UNITED STATES');
INSERT INTO Airport VALUES('JFK','New York','UNITED STATES');
INSERT INTO Airport VALUES('NAY','Beijing','CHINA');
-- DONE


INSERT INTO Plane VALUES(201, 'Boeing 999', 5, 10);
INSERT INTO Plane VALUES(202, 'Airbus A99', 5, 10);
INSERT INTO Plane VALUES(203, 'Galaxy G99', 5, 10);
INSERT INTO Plane VALUES(204, 'Sovereign S99', 5, 10);
INSERT INTO Plane VALUES(205, 'Intrepid 35', 5, 10);
INSERT INTO Plane VALUES(206, 'Boeing 777', 5, 10);
-- DONE



INSERT INTO FlightDeparture  VALUES(101,'11-MAY-2010 08:10:00 AM','YVR',201);
-- Not Added
INSERT INTO FlightArrival  VALUES(101, '11-MAY-2010 7:40:00 PM','PGV',201);
-- Could not be added foreign key error

INSERT INTO FlightDeparture  VALUES(102,'10-MAR-2011 03:10:00 PM','YVR',202);
INSERT INTO FlightArrival  VALUES(102,'10-MAR-2011 04:40:00 PM','SEA',202);

INSERT INTO FlightDeparture  VALUES(103,'9-FEB-2011 12:10:00 AM','YVR',202);
INSERT INTO FlightArrival  VALUES(103,'10-FEB-2011 6:30:00 AM','JFK',202);

INSERT INTO FlightDeparture  VALUES(104,'8-JAN-2011 03:10:00 PM','YVR',203);
INSERT INTO FlightArrival  VALUES(104,'8-JAN-2011 10:30:00 PM','SFO',203);

INSERT INTO FlightDeparture  VALUES(105,'7-DEC-2010 03:10:00 PM','YVR',203);
INSERT INTO FlightArrival  VALUES(105,'8-DEC-2010 12:40:00 AM','NAY',203);

INSERT INTO FlightDeparture  VALUES(106,'30-MAY-2010 03:10:00 PM','PVG',204);
INSERT INTO FlightArrival  VALUES(106,'31-MAY-2010 5:40:00 PM','YVR',204);

INSERT INTO FlightDeparture  VALUES(107,'25-MAR-2011 03:10:00 PM','SEA',205);
INSERT INTO FlightArrival  VALUES(107,'25-MAR-2011 04:40:00 PM','YVR',205);

INSERT INTO FlightDeparture  VALUES(108,'11-JUN-2010 11:10:00 PM','YVR',205);
INSERT INTO FlightArrival  VALUES(108,'12-JUN-2010 10:40:00 AM','PVG',205);

INSERT INTO FlightDeparture  VALUES(109,'10-JUL-2011 03:10:00 PM','YVR',205);
INSERT INTO FlightArrival  VALUES(109,'10-JUL-2011 04:40:00 PM','SEA',205);

INSERT INTO FlightDeparture  VALUES(110,'9-DEC-2011 8:10:00 PM','YVR',206);
INSERT INTO FlightArrival  VALUES(110,'10-DEC-2011 1:30:00 AM','JFK',206);
-- DONE ^^^^



-- **** All seats added except those on flight 101

INSERT INTO Seat VALUES(1,1,1,101);
-- bus - taken!
INSERT INTO Seat VALUES(0,1,2,101);
-- bus
INSERT INTO Seat VALUES(0,1,3,101);
-- bus
INSERT INTO Seat VALUES(0,1,4,101);
-- bus
INSERT INTO Seat VALUES(0,1,5,101);
-- bus
INSERT INTO Seat VALUES(0,0,6,101);
INSERT INTO Seat VALUES(0,0,7,101);
INSERT INTO Seat VALUES(1,0,8,101);
INSERT INTO Seat VALUES(1,0,9,101);
INSERT INTO Seat VALUES(1,0,10,101);
INSERT INTO Seat VALUES(0,0,11,101);
INSERT INTO Seat VALUES(0,0,12,101);
INSERT INTO Seat VALUES(0,0,13,101);
INSERT INTO Seat VALUES(0,0,14,101);
INSERT INTO Seat VALUES(0,0,15,101);

INSERT INTO Seat VALUES(0,1,1,102);
-- bus
INSERT INTO Seat VALUES(1,1,2,102);
-- bus - taken!
INSERT INTO Seat VALUES(1,1,3,102);
-- bus - taken!
INSERT INTO Seat VALUES(0,1,4,102);
-- bus

INSERT INTO Seat VALUES(0,0,6,102);
INSERT INTO Seat VALUES(0,0,7,102);
INSERT INTO Seat VALUES(0,0,8,102);
INSERT INTO Seat VALUES(0,0,9,102);
INSERT INTO Seat VALUES(0,0,10,102);
INSERT INTO Seat VALUES(0,0,11,102);
INSERT INTO Seat VALUES(0,0,12,102);
INSERT INTO Seat VALUES(0,0,13,102);
INSERT INTO Seat VALUES(0,0,14,102);
INSERT INTO Seat VALUES(0,0,15,102);

INSERT INTO Seat VALUES(0,1,1,103);
-- bus
INSERT INTO Seat VALUES(0,1,2,103);
-- bus
INSERT INTO Seat VALUES(1,1,3,103);
-- bus - taken!
INSERT INTO Seat VALUES(0,1,4,103);
-- bus - taken!
INSERT INTO Seat VALUES(0,1,5,103);
-- bus
INSERT INTO Seat VALUES(0,0,6,103);
INSERT INTO Seat VALUES(0,0,7,103);
INSERT INTO Seat VALUES(0,0,8,103);
INSERT INTO Seat VALUES(0,0,9,103);
INSERT INTO Seat VALUES(0,0,10,103);
INSERT INTO Seat VALUES(0,0,11,103);
INSERT INTO Seat VALUES(0,0,12,103);
INSERT INTO Seat VALUES(0,0,13,103);
INSERT INTO Seat VALUES(0,0,14,103);
INSERT INTO Seat VALUES(0,0,15,103);

INSERT INTO Seat VALUES(0,1,1,104);
-- bus
INSERT INTO Seat VALUES(0,1,2,104);
-- bus
INSERT INTO Seat VALUES(0,1,3,104);
-- bus
INSERT INTO Seat VALUES(1,1,4,104);
-- bus - taken!
INSERT INTO Seat VALUES(0,1,5,104);
-- bus
INSERT INTO Seat VALUES(0,0,6,104);
INSERT INTO Seat VALUES(0,0,7,104);
-- taken!
INSERT INTO Seat VALUES(0,0,8,104);
INSERT INTO Seat VALUES(0,0,9,104);
INSERT INTO Seat VALUES(0,0,10,104);
INSERT INTO Seat VALUES(0,0,11,104);
INSERT INTO Seat VALUES(0,0,12,104);
INSERT INTO Seat VALUES(0,0,13,104);
INSERT INTO Seat VALUES(0,0,14,104);
INSERT INTO Seat VALUES(0,0,15,104);


INSERT INTO Seat VALUES(0,1,1,105);
-- bus
INSERT INTO Seat VALUES(0,1,2,105);
-- bus
INSERT INTO Seat VALUES(0,1,3,105);
-- bus
INSERT INTO Seat VALUES(0,1,4,105);
-- bus
INSERT INTO Seat VALUES(1,1,5,105);
-- bus - taken!
INSERT INTO Seat VALUES(0,0,6,105);
INSERT INTO Seat VALUES(0,0,7,105);
INSERT INTO Seat VALUES(0,0,8,105);
-- taken!
INSERT INTO Seat VALUES(0,0,9,105);
INSERT INTO Seat VALUES(0,0,10,105);
INSERT INTO Seat VALUES(0,0,11,105);
INSERT INTO Seat VALUES(0,0,12,105);
INSERT INTO Seat VALUES(0,0,13,105);
INSERT INTO Seat VALUES(0,0,14,105);
INSERT INTO Seat VALUES(0,0,15,105);

INSERT INTO Seat VALUES(0,1,1,106);
-- bus
INSERT INTO Seat VALUES(0,1,2,106);
-- bus
INSERT INTO Seat VALUES(0,1,3,106);
-- bus
INSERT INTO Seat VALUES(0,1,4,106);
-- bus
INSERT INTO Seat VALUES(0,1,5,106);
-- bus
INSERT INTO Seat VALUES(1,0,6,106);
-- taken!
INSERT INTO Seat VALUES(0,0,7,106);
INSERT INTO Seat VALUES(0,0,8,106);
INSERT INTO Seat VALUES(0,0,9,106);
INSERT INTO Seat VALUES(0,0,10,106);
INSERT INTO Seat VALUES(0,0,11,106);
INSERT INTO Seat VALUES(0,0,12,106);
INSERT INTO Seat VALUES(0,0,13,106);
INSERT INTO Seat VALUES(0,0,14,106);
INSERT INTO Seat VALUES(0,0,15,106);

INSERT INTO Seat VALUES(0,1,1,107);
-- bus
INSERT INTO Seat VALUES(0,1,2,107);
-- bus
INSERT INTO Seat VALUES(0,1,3,107);
-- bus
INSERT INTO Seat VALUES(0,1,4,107);
-- bus
INSERT INTO Seat VALUES(0,1,5,107);
-- bus
INSERT INTO Seat VALUES(0,0,6,107);
INSERT INTO Seat VALUES(1,0,7,107);
-- taken!
INSERT INTO Seat VALUES(0,0,8,107);
INSERT INTO Seat VALUES(0,0,9,107);
INSERT INTO Seat VALUES(0,0,10,107);
INSERT INTO Seat VALUES(0,0,11,107);
INSERT INTO Seat VALUES(0,0,12,107);
INSERT INTO Seat VALUES(0,0,13,107);
INSERT INTO Seat VALUES(0,0,14,107);
INSERT INTO Seat VALUES(0,0,15,107);

INSERT INTO Seat VALUES(0,1,1,108);
-- bus
INSERT INTO Seat VALUES(0,1,2,108);
-- bus
INSERT INTO Seat VALUES(0,1,3,108);
-- bus
INSERT INTO Seat VALUES(0,1,4,108);
-- bus
INSERT INTO Seat VALUES(0,1,5,108);
-- bus
INSERT INTO Seat VALUES(0,0,6,108);
INSERT INTO Seat VALUES(0,0,7,108);
INSERT INTO Seat VALUES(1,0,8,108);
-- taken!
INSERT INTO Seat VALUES(0,0,9,108);
INSERT INTO Seat VALUES(0,0,10,108);
INSERT INTO Seat VALUES(0,0,11,108);
INSERT INTO Seat VALUES(0,0,12,108);
INSERT INTO Seat VALUES(0,0,13,108);
INSERT INTO Seat VALUES(0,0,14,108);
INSERT INTO Seat VALUES(0,0,15,108);

INSERT INTO Seat VALUES(0,1,1,109);
-- bus
INSERT INTO Seat VALUES(0,1,2,109);
-- bus
INSERT INTO Seat VALUES(0,1,3,109);
-- bus
INSERT INTO Seat VALUES(0,1,4,109);
-- bus
INSERT INTO Seat VALUES(0,1,5,109);
-- bus
INSERT INTO Seat VALUES(0,0,6,109);
INSERT INTO Seat VALUES(0,0,7,109);
INSERT INTO Seat VALUES(0,0,8,109);
INSERT INTO Seat VALUES(1,0,9,109);
-- taken!
INSERT INTO Seat VALUES(0,0,10,109);
INSERT INTO Seat VALUES(0,0,11,109);
INSERT INTO Seat VALUES(0,0,12,109);
INSERT INTO Seat VALUES(0,0,13,109);
INSERT INTO Seat VALUES(0,0,14,109);


INSERT INTO Seat VALUES(0,1,1,110);
-- bus
INSERT INTO Seat VALUES(0,1,2,110);
-- bus
INSERT INTO Seat VALUES(0,1,3,110);
-- bus
INSERT INTO Seat VALUES(0,1,4,110);
-- bus
INSERT INTO Seat VALUES(0,1,5,110);
-- bus
INSERT INTO Seat VALUES(0,0,6,110);
INSERT INTO Seat VALUES(0,0,7,110);
INSERT INTO Seat VALUES(0,0,8,110);
INSERT INTO Seat VALUES(0,0,9,110);
INSERT INTO Seat VALUES(1,0,10,110);
-- taken!
INSERT INTO Seat VALUES(0,0,11,110);
INSERT INTO Seat VALUES(0,0,12,110);
INSERT INTO Seat VALUES(0,0,13,110);
INSERT INTO Seat VALUES(0,0,14,110);
INSERT INTO Seat VALUES(0,0,15,110);


INSERT INTO TicketForPerson VALUES(10001, 20001);
INSERT INTO TicketForPerson VALUES(10002, 20002);
INSERT INTO TicketForPerson VALUES(10003, 20003);
INSERT INTO TicketForPerson VALUES(10004, 20004);
INSERT INTO TicketForPerson VALUES(10005, 20005);
INSERT INTO TicketForPerson VALUES(10006, 20006);
INSERT INTO TicketForPerson VALUES(10007, 20007);
INSERT INTO TicketForPerson VALUES(10008, 20008);
INSERT INTO TicketForPerson VALUES(10009, 20009);
INSERT INTO TicketForPerson VALUES(10010, 20010);
-- new:
INSERT INTO TicketForPerson VALUES(10011, 20011);
INSERT INTO TicketForPerson VALUES(10012, 20011);
INSERT INTO TicketForPerson VALUES(10013, 20011);
INSERT INTO TicketForPerson VALUES(10014, 20011);

INSERT INTO TicketForPerson VALUES(99999, 99999);
-- DONE


INSERT INTO PaysFor VALUES('12-JUL-2009',30001,20001);
INSERT INTO PaysFor VALUES('12-AUG-2009',30002,20002);
INSERT INTO PaysFor VALUES('12-SEP-2009',30003,20003);
INSERT INTO PaysFor VALUES('12-MAY-2009',30004,20004);
INSERT INTO PaysFor VALUES('12-JUN-2009',30005,20005);
INSERT INTO PaysFor VALUES('12-OCT-2009',30006,20006);
INSERT INTO PaysFor VALUES('12-DEC-2009',30007,20007);
INSERT INTO PaysFor VALUES('12-FEB-2009',30008,20008);
INSERT INTO PaysFor VALUES('12-APR-2009',30009,20009);
INSERT INTO PaysFor VALUES('12-AUG-2009',30010,20010);
-- new:
INSERT INTO PaysFor VALUES('14-AUG-2008',30011,20011);
INSERT INTO PaysFor VALUES('17-DEC-2008',30012,20011);
INSERT INTO PaysFor VALUES('2-JAN-2010',30013,20011);
INSERT INTO PaysFor VALUES('31-JAN-2010',30014,20011);
-- DONE


INSERT INTO TransactTicket VALUES(10001,30001);
INSERT INTO TransactTicket VALUES(10002,30002);
INSERT INTO TransactTicket VALUES(10003,30003);
INSERT INTO TransactTicket VALUES(10004,30004);
INSERT INTO TransactTicket VALUES(10005,30005);
INSERT INTO TransactTicket VALUES(10006,30006);
INSERT INTO TransactTicket VALUES(10007,30007);
INSERT INTO TransactTicket VALUES(10008,30008);
INSERT INTO TransactTicket VALUES(10009,30009);
INSERT INTO TransactTicket VALUES(10010,30010);
-- new:
INSERT INTO TransactTicket VALUES(10011,30011);
INSERT INTO TransactTicket VALUES(10012,30012);
INSERT INTO TransactTicket VALUES(10013,30013);
INSERT INTO TransactTicket VALUES(10014,30014);
-- DONE



INSERT INTO TicketForSeat VALUES(10001,1,101);
INSERT INTO TicketForSeat VALUES(10002,2,102);
INSERT INTO TicketForSeat VALUES(10003,3,103);
INSERT INTO TicketForSeat VALUES(10004,4,104);
INSERT INTO TicketForSeat VALUES(10005,5,105);
INSERT INTO TicketForSeat VALUES(10006,6,106);
INSERT INTO TicketForSeat VALUES(10007,7,107);
INSERT INTO TicketForSeat VALUES(10008,8,108);
INSERT INTO TicketForSeat VALUES(10009,9,109);
INSERT INTO TicketForSeat VALUES(10010,10,110);
-- new:
INSERT INTO TicketForSeat VALUES(10011,3,102);
INSERT INTO TicketForSeat VALUES(10012,4,103);
INSERT INTO TicketForSeat VALUES(10013,7,104);
INSERT INTO TicketForSeat VALUES(10014,8,105);
-- DONE