-- Part 1; add drops so that we can keep re-running this file.
DROP TABLE Reservation;
DROP TABLE TravelAgent;
DROP TABLE Cruise;
DROP TABLE Company;
DROP TABLE Customer;
DROP SEQUENCE customerID_seq;
DROP SEQUENCE cruiseID_seq;
DROP SEQUENCE travelAgentID_seq;
DROP SEQUENCE reservationID_seq;

-- Part 2; Set up schema and insert data.
CREATE TABLE Customer(
	customerID NUMBER,
	firstName VARCHAR2(15),
	lastName VARCHAR2(15),
	address VARCHAR2(30),
	phone NUMBER(10),
	age NUMBER(3),
	CONSTRAINT Customer_pk PRIMARY KEY(customerID),
	CONSTRAINT Customer_U1 UNIQUE(firstName, lastName, phone),
	CONSTRAINT Customer_phoneNumber_val CHECK (phone IS NOT NULL)
);
CREATE SEQUENCE customerID_seq;

CREATE TABLE Company(
	companyName VARCHAR2(15),
	stockSymbol CHAR(4),
	website VARCHAR2(40),
	CONSTRAINT Company_pk PRIMARY KEY(companyName),
	CONSTRAINT Company_U1 UNIQUE(stockSymbol)
);

CREATE TABLE Cruise(
	cruiseID NUMBER,
	cruiseName VARCHAR2(25),
	departurePort VARCHAR2(20),
	days NUMBER(2),
	companyName VARCHAR2(15),
	shipName VARCHAR2(30),
	price NUMBER(7, 2),
	CONSTRAINT Cruise_pk PRIMARY KEY(cruiseID),
	CONSTRAINT Cruise_companyName_fk FOREIGN KEY (companyName) REFERENCES Company(companyName) ON DELETE SET NULL
);
CREATE SEQUENCE cruiseID_seq;

CREATE TABLE TravelAgent(
	travelAgentID NUMBER,
	firstName VARCHAR2(15),
	lastName VARCHAR2(20),
	title VARCHAR2(15),
	salary NUMBER(7, 2),
	CONSTRAINT TravelAgent_pk PRIMARY KEY(travelAgentID),
	CONSTRAINT TravelAgent_title_val CHECK (title IN ('Assistant', 'Agent', 'Manager'))
);
CREATE SEQUENCE travelAgentID_seq;

CREATE TABLE Reservation(
	reservationID NUMBER,
	customerID NUMBER,
	cruiseID NUMBER,
	travelAgentID NUMBER,
	travelDate DATE,
	CONSTRAINT Reservation_pk PRIMARY KEY(reservationID),
	CONSTRAINT Reservation_customerID_fk FOREIGN KEY (customerID) REFERENCES Customer(customerID) ON DELETE CASCADE,
	CONSTRAINT Reservation_cruiseID_fk FOREIGN KEY (cruiseID) REFERENCES Cruise(cruiseID) ON DELETE CASCADE,
	CONSTRAINT Reservation_travelAgentID_fk FOREIGN KEY (travelAgentID) REFERENCES TravelAgent(travelAgentID) ON DELETE CASCADE
);
CREATE SEQUENCE reservationID_seq;

-- Customer values
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Dylan', 'Ward', '42 Elm Place', 8915367188, 22);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Austin', 'Ross', '657 Redondo Ave.', 1233753684, 25);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Lisa', 'Powell', '5 Jefferson Ave.', 6428369619, 17);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Brian', 'Martin', '143 Cambridge Ave.', 5082328798, 45);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Nicole', 'White', '77 Massachusetts Ave.', 6174153059, 29);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Tyler', 'Garcia', '175 Forest St.', 9864752346, 57);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Anna', 'Allen', '35 Tremont St.', 8946557732, 73);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Michael', 'Sanchez', '9 Washington Court', 1946825344, 18);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Justin', 'Myers', '98 Lake Hill Drive', 7988641411, 26);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Bruce', 'Clark', '100 Main St.', 2324648888, 68);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Rachel', 'Lee', '42 Oak St.', 2497873464, 19);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Kelly', 'Gray', '1414 Cedar St.', 9865553232, 82);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Madison', 'Young', '8711 Meadow St.', 4546667821, 67);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Ashley', 'Powell', '17 Valley Drive', 2123043923, 20);
INSERT INTO Customer VALUES (customerID_seq.nextval, 'Joshua', 'Davis', '1212 8th St.', 7818914567, 18);

-- Company values
INSERT INTO Company VALUES ('Carnival', 'CRVL', 'http://www.carnival.com');
INSERT INTO Company VALUES ('Celebrity', 'CELB', 'http://www.celebritycruises.com');
INSERT INTO Company VALUES ('NCL', 'NCLC', 'http://www.ncl.com');
INSERT INTO Company VALUES ('Princess', 'PRCS', 'http://www.princess.com');

-- Cruise values
INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'Mexico', 'Miami', 7, 'NCL', 'Norwegian Pearl', 799.00);
INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'New England', 'Boston', 7, 'NCL', 'Norwegian Jewel', 895.75);
INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'ABC Islands', 'Miami', 4, 'Celebrity', 'Equinox', 450.50);
INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'Hawaii', 'San Francisco', 14, 'Princess', 'Crown Princess', 2310.00);
INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'Panama Canal', 'Miami', 10, 'Carnival', 'Carnival Spirit', 1432.99);

-- TravelAgent values
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Chloe', 'Rodriguez', 'Assistant', 31750.00);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Ben', 'Wilson', 'Agent', 47000.22);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Mia', 'Smith', 'Manager', 75250.00);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Noah', 'Williams', 'Assistant', 32080.90);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Liam', 'Brown', 'Manager', 60500.75);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Mason', 'Jones', 'Manager', 79000.00);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Olivia', 'Miller', 'Agent', 54000.50);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Sofia', 'Davis', 'Agent', 45000.00);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Jason', 'Garcia', 'Manager', 52025.95);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Emily', 'Johnson', 'Assistant', 22000.50);
INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Ethan', 'Elm', 'Agent', 27044.52);

-- Reservations values
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 12, 1, 2, '9-Nov-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 14, 4, 5, '21-Jan-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 4, 1, '11-Dec-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 9, 5, 4, '31-Aug-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 13, 1, 2, '10-Apr-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 4, 6, '29-Jul-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 2, 2, 2, '17-May-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 4, 1, 10, '11-Apr-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 10, 5, 3, '3-Jun-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 3, 9, '15-Oct-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 1, 2, 7, '8-Mar-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 4, 7, '24-Nov-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 8, 1, 1, '3-Aug-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 15, 5, 10, '13-Dec-18');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 4, 3, 7, '6-Feb-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 6, 4, 5, '12-Aug-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 14, 2, 8, '22-Jun-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 11, 5, 9, '1-Feb-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 7, 4, 8, '15-Mar-19');
INSERT INTO Reservation VALUES (reservationID_seq.nextval, 14, 4, 3, '28-Feb-19');

-- Part 3; Queries.
-- 3a
UPDATE Cruise SET price = ROUND(price * 1.15, 2) WHERE departurePort = 'Miami';
SELECT cruiseName, departurePort, shipName, TO_CHAR(price, '$99,999.99') AS NEWPRICE FROM Cruise;

-- 3b
SELECT DISTINCT Cruise.CompanyName, Cruise.cruiseName FROM Cruise
    JOIN Reservation ON Cruise.cruiseID = Reservation.cruiseID
    JOIN Customer ON Reservation.customerID = Customer.customerID
    WHERE Customer.Age > 50 AND Cruise.departurePort = 'Miami'
    ORDER BY Cruise.CompanyName ASC;

-- 3c
SELECT companyName || ' ' || Company.stockSymbol AS CruiseCompany, Cruise.cruiseName, Reservation.travelDate FROM Reservation
    NATURAL JOIN Cruise
    NATURAL JOIN Company
    WHERE Cruise.Days >= 10 AND Reservation.travelDate >= '1-JAN-2019'
    ORDER BY Cruise.cruiseName ASC, Reservation.travelDate ASC;

-- 3d
SELECT travelAgent.firstName, travelAgent.lastName, travelAgent.title FROM travelAgent
    LEFT JOIN Reservation ON travelAgent.travelAgentID = Reservation.travelAgentID
    WHERE Reservation.travelAgentID IS NULL;
