-- Cleans up any tables that could interfere with the new tables
DROP TABLE RR_MAINTENANCE;
DROP TABLE RR_RENTALS CASCADE CONSTRAINTS;
DROP TABLE RR_SOLD_BIKES CASCADE CONSTRAINTS;
DROP TABLE RR_MANUFACTURERS CASCADE CONSTRAINTS;
DROP TABLE RR_RESERVATIONS CASCADE CONSTRAINTS;
DROP TABLE RR_BIKE_MODELS CASCADE CONSTRAINTS;
DROP TABLE RR_BIKES CASCADE CONSTRAINTS;
DROP TABLE RR_CUSTOMERS CASCADE CONSTRAINTS;
DROP TABLE RR_DEALERS;

--Table Creation
CREATE TABLE RR_MANUFACTURERS
(
  manufacturer_id int NOT NULL PRIMARY KEY,
  address varchar2(40),
  postcode varchar(10),
  telephone varchar(15)
);

CREATE TABLE RR_BIKE_MODELS
(
  bike_model_id int NOT NULL PRIMARY KEY,
  model varchar2(15) NOT NULL,
  classification varchar2(15) NOT NULL,
  bike_size varchar2(5) NOT NULL,
  manufacturer_id int NOT NULL,
  CONSTRAINT rr_man_fk FOREIGN KEY(manufacturer_id) REFERENCES RR_MANUFACTURERS(manufacturer_id)
);

CREATE TABLE RR_BIKES
(
  bike_id int NOT NULL PRIMARY KEY,
  bike_model_id int NOT NULL,
  buy_price NUMBER(8,2),
  CONSTRAINT rr_bmid_fk FOREIGN KEY(bike_model_id) REFERENCES RR_BIKE_MODELS(bike_model_id)
);

CREATE TABLE RR_MAINTENANCE
(
  maintenance_id int NOT NULL PRIMARY KEY,
  fault_details varchar(255) NOT NULL,
  action_taken varchar(255) NOT NULL,
  bike_id int NOT NULL,
  CONSTRAINT rr_mbik_fk FOREIGN KEY(bike_id) REFERENCES RR_BIKES(bike_id)
);

CREATE TABLE RR_CUSTOMERS
(
  customer_id int NOT NULL PRIMARY KEY,
  first_name varchar(20),
  last_name varchar(20),
  address varchar(255) NOT NULL,
  postcode varchar(8) NOT NULL,
  phone_number varchar(31) NOT NULL -- theoretical maximum size for a phone number
);

CREATE TABLE RR_RENTALS
(
  rental_id int NOT NULL PRIMARY KEY,
  date_time_out date NOT NULL,
  date_time_in date,
  date_time_due date NOT NULL,
  amount_paid NUMBER(8,2),
  bike_id int NOT NULL,
  customer_id int NOT NULL,
  CONSTRAINT rr_rlbik_fk FOREIGN KEY(bike_id) REFERENCES RR_BIKES(bike_id),
  CONSTRAINT rr_rlcus_fk FOREIGN KEY(customer_id) REFERENCES RR_CUSTOMERS(customer_id)
);

CREATE TABLE RR_RESERVATIONS
(
  reservation_id int NOT NULL PRIMARY KEY,
  date_time_out date NOT NULL,
  date_time_in date NOT NULL,
  bike_id int NOT NULL,
  customer_id int NOT NULL,
  CONSTRAINT rr_rvbik_fk FOREIGN KEY(bike_id) REFERENCES RR_BIKES(bike_id),
  CONSTRAINT rr_rvcus_fk FOREIGN KEY(customer_id) REFERENCES RR_CUSTOMERS(customer_id)
);

CREATE TABLE RR_DEALERS
(
  dealer_id int NOT NULL PRIMARY KEY,
  dealer_name varchar(100) NOT NULL,
  address varchar(255) NOT NULL,
  email varchar(255),
  phone_number varchar(31)
);

CREATE TABLE RR_SOLD_BIKES
(
  sale_price NUMBER(8,2), -- 8 digits total, 2 decimal places
  bike_id int NOT NULL,
  dealer_id int NOT NULL,
  CONSTRAINT rr_sbbik_fk FOREIGN KEY(bike_id) REFERENCES RR_BIKES(bike_id), --bike_id
  CONSTRAINT rr_sbdea_fk FOREIGN KEY(dealer_id) REFERENCES RR_DEALERS(dealer_id), --dealer_id
  CONSTRAINT sold_bike_id PRIMARY KEY (bike_id,dealer_id) -- creates composite key
);

-- Manufacturers example data
insert into rr_manufacturers (manufacturer_id, address, postcode, telephone) values (1, '12 hillside close','sk13 2eb','01538755392');
insert into rr_manufacturers (manufacturer_id, address, postcode, telephone) values (2, '17 lowerbarn road','sk13 2dz','01964095376');
insert into rr_manufacturers (manufacturer_id, address, postcode, telephone) values (3, '40 gladstone street','sk13 8ne','013323553376');
insert into rr_manufacturers (manufacturer_id, address, postcode, telephone) values (4, '29 berry street','m1 2ar','013425332476');
insert into rr_manufacturers (manufacturer_id, address, postcode, telephone) values (5, '2 millers view','st10 8ep','079640323455');
insert into rr_manufacturers (manufacturer_id, address, postcode, telephone) values (6, '13 hillside close','sk13 8ne','079640952346');

--Bike Model example data
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('1','XM3','Mountain','M','1');
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('2','XB1','BMX','S','1');
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('3','XR7','Road','L','6');
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('4','XC2','Child','XS','2');
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('5','XM2','Mountain','M','3');
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('6','XM3','Mountain','M','4');
insert into rr_bike_models (bike_model_id , model, classification, bike_size, manufacturer_id) values ('7','XM3','Mountain','M','5');

--Bikes example data
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('1', '1', '153.50');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('2', '1', '120');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('3', '2', '89.99');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('4', '3', '120.50');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('5', '4', '327.99');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('6', '5', '410');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('7', '6', '253');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('8', '7', '160');
insert into rr_bikes (bike_id, bike_model_id, buy_price) values ('9', '7', '78.90');

--Maintenance example data
insert into rr_maintenance (maintenance_id, fault_details, action_taken, bike_id) values ('34','brake pads need replacing','ordered new pads','1');
insert into rr_maintenance (maintenance_id, fault_details, action_taken, bike_id) values ('12','brake cable snapped','installed new cable','3');
insert into rr_maintenance (maintenance_id, fault_details, action_taken, bike_id) values ('67','puncture in front tyre','replaced inner tube','5');
insert into rr_maintenance (maintenance_id, fault_details, action_taken, bike_id) values ('35','chain loose','took a link out of the chain','4');
insert into rr_maintenance (maintenance_id, fault_details, action_taken, bike_id) values ('09','sprocket snapped','replaced sprocket and crank set','1');

--Customer example data
insert into rr_customers (customer_id, first_name, last_name, address, postcode, phone_number) values ('1','Billy','Jean','99 sommer street','GH1 9DF','07534436432');
insert into rr_customers (customer_id, first_name, last_name, address, postcode, phone_number) values ('2','Berk','Gulajnidfiojuiei','78 ebernezzer street','SK1 9TH','07546457888');
insert into rr_customers (customer_id, first_name, last_name, address, postcode, phone_number) values ('3','Sam','Robinson','04 station road','SK8 9OL','07653334455');
insert into rr_customers (customer_id, first_name, last_name, address, postcode, phone_number) values ('4','Farmer','Ted','54 king street','OL6 8FF','07784456445');
insert into rr_customers (customer_id, first_name, last_name, address, postcode, phone_number) values ('5','Postman','Pat','02 faubel road','ST10 2TH','07797964566');

--Rentals example data
insert into RR_RENTALS (rental_id, date_time_out, date_time_in, date_time_due, amount_paid, bike_id, customer_id) values ('1',TO_DATE('2015-01-01 12:40','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2015-01-10 11:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2015-01-12 12:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'), '240','2','1');

--Reservations example data
insert into RR_RESERVATIONS (reservation_id, date_time_out, date_time_in, bike_id, customer_id) values ('1',TO_DATE('2014-01-01 12:40','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2015-01-12 12:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),'2','1');
insert into RR_RESERVATIONS (reservation_id, date_time_out, date_time_in, bike_id, customer_id) values ('2',TO_DATE('2015-02-01 10:20','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2015-02-12 12:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),'4','2');
insert into RR_RESERVATIONS (reservation_id, date_time_out, date_time_in, bike_id, customer_id) values ('3',TO_DATE('2014-12-03 05:10','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2015-12-10 12:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),'1','3');
insert into RR_RESERVATIONS (reservation_id, date_time_out, date_time_in, bike_id, customer_id) values ('4',TO_DATE('2014-11-05 09:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2014-11-12 12:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),'2','4');
insert into RR_RESERVATIONS (reservation_id, date_time_out, date_time_in, bike_id, customer_id) values ('5',TO_DATE('2015-01-04 13:50','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),TO_DATE('2015-01-12 12:00','YYYY-MM-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN'),'5','5');

--Dealers example data
insert into rr_dealers (dealer_id, dealer_name, address, email, phone_number) values ('1','Alan Stoneman','45 sommer street','AStone@hotmail.com','07534436432');
insert into rr_dealers (dealer_id, dealer_name, address, email, phone_number) values ('2','Simon Pegg','56 kinder close','SimonP@hotmail.com','07562354267');
insert into rr_dealers (dealer_id, dealer_name, address, email, phone_number) values ('3','Richard Warhurst','1 high street','RickWarhurst@hotmail.com','07743254795');
insert into rr_dealers (dealer_id, dealer_name, address, email, phone_number) values ('4','Shaun Haze','6 terrace grove','Haze@hotmail.com','07843266842');
insert into rr_dealers (dealer_id, dealer_name, address, email, phone_number) values ('5','Stuart Oakley','tredcroff street','SOakley@hotmail.com','07843264223');

--Sold Bikes example data
insert into rr_sold_bikes (sale_price, bike_id, dealer_id) values ('100', '6','1');
insert into rr_sold_bikes (sale_price, bike_id, dealer_id) values ('200', '9','2');
insert into rr_sold_bikes (sale_price, bike_id, dealer_id) values ('30', '8','2');