-- DROP TABLES IN REVERSE ORDER FOR CREATION

DROP TABLE SUPPLIERPARTS;
DROP TABLE ORDERLINE;
DROP TABLE BOAT;
DROP TABLE RENTAL;
DROP TABLE CLASSSIZE;
DROP TABLE ORDERS;
DROP TABLE PARTS;
DROP TABLE SUPPLIER;
DROP TABLE DEALER;
DROP TABLE MAINTENANCE;
DROP TABLE CUSTOMER;

--(in order to create FK, u need to reference that id and table name)
-- Customer table (DONE)
CREATE TABLE CUSTOMER (
CustID NUMBER(9) PRIMARY KEY,
CustLName VARCHAR2(30) CONSTRAINT nn_Cust_lname NOT NULL,
CustFName VARCHAR2(30),
CustPCode VARCHAR2(15),
CustEmail VARCHAR2(30)  CONSTRAINT nn_Cust_email NOT NULL 
CONSTRAINT Cust_email UNIQUE,
CustDOB DATE);

-- Maintenance table (DONE)
CREATE TABLE MAINTENANCE (
MainID NUMBER(9) PRIMARY KEY,
MainFaultData VARCHAR2(30),
MainFaultOccurenceDate DATE,
MainFaultServiceDate DATE,
MainFaultDetails VARCHAR2(50),
MainActionTaken VARCHAR2(15),
MainActionDate DATE,
MainActionDetails VARCHAR2(50),
Mainpriority VARCHAR2(15),
FOREIGN KEY (boat_id) REFERENCES BOAT(boat_id));

-- Dealer table (Done)
CREATE TABLE DEALER (
DealID NUMBER(9) PRIMARY KEY,
DealName VARCHAR2(30) NOT NULL,
DealAddress VARCHAR2(30),
DealPCode VARCHAR2(15),
DealTelephone NUMBER (11),
DealEmail VARCHAR2(30) NOT NULL);

-- Supplier table (Done)
CREATE TABLE SUPPLIER (
SuppID NUMBER(9) PRIMARY KEY,
SuppName VARCHAR2(30) NOT NULL,
SuppAddress VARCHAR2(30),
SuppPCode VARCHAR2(15),
SuppTelephone NUMBER (11),
SuppEmail VARCHAR2(30) NOT NULL);

-- Parts table (Done)
CREATE TABLE PARTS (
PartID NUMBER(9) PRIMARY KEY,
PartName VARCHAR2(30) NOT NULL,
PartDescription VARCHAR2(30),
PartPrice NUMBER(6,2));

-- Orders table (Done)
CREATE TABLE ORDERS (
OrdID NUMBER(9) PRIMARY KEY,
OrdReceipt VARCHAR2(100) NOT NULL,
OrdDeliveryDate DATE,
OrdDate DATE);

-- ClassSize table (DONE)
CREATE TABLE CLASSSIZE (
ClassSizeID VARCHAR2(15) PRIMARY KEY,
FullDayPrice NUMBER(6,2),
HalfDayPrice NUMBER(6,2));

-- Rental (Done)
CREATE TABLE RENTAL (
RentID NUMBER(9) PRIMARY KEY,
RentDate DATE,
RentPeriod DATE,
RentAmountPaid NUMBER(6,2),
RentReservationDate DATE,
RentReservationDuration DATETIME,
RentDateBackDue DATE,
RentDateBackActual DATE,
CONSTRAINT fk_rental_boat FOREIGN KEY (boat_id) REFERENCES BOAT(boat_id),
CONSTRAINT fk_rental_customer FOREIGN KEY (CustID) REFERENCES CUSTOMER(CustID),
CONSTRAINT fk_rental_classsize FOREIGN KEY (ClassSizeID) REFERENCES CLASSSIZE(ClassSizeID));


-- Boat (Done)
CREATE TABLE BOAT (
boat_id NUMBER(9) PRIMARY KEY,
boatType VARCHAR2(30),
boatPrice NUMBER(6,2),
boatLastServiceIssue DATE,
boatModel VARCHAR2(30),
boatSaleDate DATE,
boatSalePrice NUMBER(6,2),
boatPurchaseDate DATE,
boatPurchasePrice NUMBER(6,2),
CONSTRAINT fk_boat_supplier FOREIGN KEY (SuppID) REFERENCES SUPPLIER(SuppID),
CONSTRAINT fk_boat_dealer FOREIGN KEY (DealID) REFERENCES DEALER(DealID),
CONSTRAINT fk_boat_classsize FOREIGN KEY (ClassSizeID) REFERENCES CLASSSIZE(ClassSizeID));


-- OrderLine (Done)
CREATE TABLE ORDERLINE (
PartID  NUMBER(9),
OrdID  NUMBER(9),
OrderLineQuantity NUMBER (11),
CONSTRAINT pk_orderline PRIMARY KEY (PartID, OrdID),
CONSTRAINT fk_orderline_parts FOREIGN KEY (PartID) REFERENCES PARTS(PartID),
CONSTRAINT fk_orderline_orders FOREIGN KEY (OrdID) REFERENCES ORDERS(OrdID));

-- SupplierParts (Done)
CREATE TABLE SUPPLIERPARTS (
SuppID  NUMBER(9),
PartID NUMBER(9),
CONSTRAINT pk_supplierparts PRIMARY KEY (SuppID, PartID),
CONSTRAINT fk_supplierparts_suppliers FOREIGN KEY (SuppID) REFERENCES SUPPLIER(SuppID),
CONSTRAINT fk_supplierparts_parts FOREIGN KEY (PartID) REFERENCES PARTS(PartID));