DROP TABLE Venue;
DROP TABLE Coach;
DROP TABLE Achievement;
DROP TABLE Booking;
DROP TABLE TrainingItems;
DROP TABLE Client;

CREATE TABLE Venue(
venueId VARCHAR(20) NOT NULL PRIMARY KEY,
vName CHAR(20) NOT NULL,
telephoneNumber INT NOT NULL,
vAddress VARCHAR(50) NOT NULL,
postcode INT NOT NULL
);

CREATE TABLE TrainingItems(
trainingItemsId VARCHAR(20) NOT NULL PRIMARY KEY,
tType VARCHAR(20) NOT NULL
);

CREATE TABLE Client(
clientId VARCHAR(20) NOT NULL PRIMARY KEY,
fName VARCHAR(20) NOT NULL,
lName VARCHAR(20) NOT NULL,
phoneNumber INT NOT NULL,
email VARCHAR(30) NOT NULL
);

CREATE TABLE Achievement(
achievementId VARCHAR(20) NOT NULL PRIMARY KEY,
aName CHAR(20) NOT NULL,
aType VARCHAR(20) NOT NULL,
aDateAchieved DATE NOT NULL
);

CREATE TABLE Booking(
bookingId VARCHAR(20) NOT NULL PRIMARY KEY,
dateOfBooking DATE NOT NULL,
startTime TIME NOT NULL,
totalHoursBooked INT NOT NULL,
totalCost MONEY NOT NULL,
totalPaid MONEY NOT NULL,
paymentStatus VARCHAR(20) NOT NULL,
completionStatus VARCHAR(20) NOT NULL,
travellingTime DECIMAL(10,2) NOT NULL,
clientId VARCHAR(20) FOREIGN KEY REFERENCES Client
);

CREATE TABLE Coach(
coachId VARCHAR(20) NOT NULL PRIMARY KEY,
cName CHAR(20) NOT NULL,
cEmail VARCHAR(30) NOT NULL,
cPhoneNumber INT NOT NULL,
cAddress VARCHAR(50) NOT NULL,
postcode INT NOT NULL,
achievementId VARCHAR(20) FOREIGN KEY REFERENCES Achievement,
bookingId VARCHAR(20) FOREIGN KEY REFERENCES Booking,
venueId VARCHAR(20) FOREIGN KEY REFERENCES Venue,
trainingItemsId VARCHAR(20) FOREIGN KEY REFERENCES trainingItems
);

INSERT INTO Achievement
VALUES ('a1', 'Hollow Slaying', 'Lieutenant', '1/1/0487');
INSERT INTO Achievement
VALUES ('a2', 'Magic', 'PhD', '8/7/1867');
INSERT INTO Achievement
VALUES ('a3', 'Sharpshooting', 'PhD', '6/18/2015');
INSERT INTO Achievement
VALUES ('a4', 'Programming', 'PhD', '9/16/2017');

INSERT INTO Booking
VALUES ('b1', '10/18/2017', '10:00', '2', '1000', '300', 'Partial', 'Complete', '1.2', '1');
INSERT INTO Booking
VALUES ('b2', '10/17/2017', '11:00', '3', '500', '500', 'Paid', 'Incomplete', '0', '2');
INSERT INTO Booking
VALUES ('b3', '10/16/2017', '12:00', '4', '300', '0', 'Not Paid', 'Incomplete', '2', '3');
INSERT INTO Booking
VALUES ('b4', '10/15/2017', '13:00', '2', '100', '50', 'Partial', 'Complete', '0.5', '4');

INSERT INTO Venue
VALUES ('v1', 'Sokyoku', '29573817', 'Sokyoku Hill, Soul Society', '6728');
INSERT INTO Venue
VALUES ('v2', 'Kuoh Academy', '29573817', 'Kuoh, Japan', '9268');
INSERT INTO Venue
VALUES ('v3', 'Bonyari High', '29573817', 'Bonyari, Japan', '2245');
INSERT INTO Venue
VALUES ('v4', 'Blacktown Library', '29573817', 'Blacktown, Sydney', '2148');

INSERT INTO TrainingItems
VALUES ('t1', 'Slaying');
INSERT INTO TrainingItems
VALUES ('t2', 'Not Science');
INSERT INTO TrainingItems
VALUES ('t3', 'Sports');
INSERT INTO TrainingItems
VALUES ('t4', 'Science');

INSERT INTO Client
VALUES ('1', 'Lirin', 'Kinamoto', '0758392010', 'captain@eliteenforcers.com');
INSERT INTO Client
VALUES ('2', 'Akeno', 'Himejima', '0867463529', 'best@girl.com');
INSERT INTO Client
VALUES ('3', 'Chitoge', 'Kirisaki', '0987654321', 'successor@beehive.com');
INSERT INTO Client
VALUES ('4', 'Hiten', 'Bhudia', '0584939587', 'goblinbarrel@gmail.com');

INSERT INTO Coach
VALUES ('c1', 'Akimei Izayumi', 'akimeiizayumi@gmail.com', '0293857382', '1 Kido St, Soul Society', '0014', 'a1', 'b1', 'v1', 't1');
INSERT INTO Coach
VALUES ('c2', 'Rosuvaise Wang', 'rosuvaise<3@gmail.com', '0858392948', '654 Demonic St, Valhalla', '0100', 'a2', 'b2', 'v2', 't2');
INSERT INTO Coach
VALUES ('c3', 'Seishiro Tsugumi', 'ojousama@gmail.com', '0843986043', '2 Black Tiger st, Bonyari', '0001', 'a3', 'b3', 'v3', 't3');
INSERT INTO Coach
VALUES ('c4', 'Dylan Wang', 'gibberish69@gmail.com', '0968472658', '4 Trash Can, Sydney', '6996', 'a4', 'b4', 'v4', 't4');

/*
SELECT * FROM Venue;
SELECT * FROM Coach;
SELECT * FROM Achievement;
SELECT * FROM TrainingItems;
SELECT * FROM Booking;
SELECT * FROM Client;
*/

--a
SELECT c.fName, c.lName, b.dateOfBooking
FROM Client c, Booking b
WHERE c.clientId = b.clientId
ORDER BY c.fName;

SELECT Client.fName, Client.lName, Booking.dateOfBooking
FROM Client
FULL OUTER JOIN Booking ON Client.clientId = Booking.clientId;

--b
SELECT c.cName, b.totalHoursBooked
FROM Coach c, Booking b
WHERE c.bookingId = b.bookingId
ORDER BY c.cName;

SELECT Coach.cName, Booking.totalHoursBooked
FROM Coach
FULL OUTER JOIN Booking ON Coach.bookingId = Booking.bookingId;

--c
SELECT c.cName, b.dateOfBooking, v.vName, t.fName
FROM Coach c, Booking b, Venue v, Client t
WHERE c.bookingId = b.bookingId
AND c.venueId = v.venueId
AND t.clientId = b.bookingId
AND b.dateOfBooking = '10/1/2017'
ORDER BY c.cName;

--d
SELECT c.cName, b.startTime, v.vName, t.trainingItemsId
FROM Coach c, Booking b, Venue v, TrainingItems t
WHERE c.bookingId = b.bookingId
AND c.venueId = v.venueId
AND c.trainingItemsId = t.trainingItemsId
AND b.dateOfBooking = '10/1/2017'
AND b.startTime = '10:00'
ORDER BY c.cName;