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 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 Achievement(
achievementId VARCHAR(20) NOT NULL PRIMARY KEY,
aName CHAR(20) NOT NULL,
aType VARCHAR(20) NOT NULL,
aDateAchieved DATE NOT NULL
);

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