--Script to Create GoodReads database
--created 4/7/2015 Anthony Prantow


DROP TABLE orderline CASCADE CONSTRAINTS;
DROP TABLE invoice CASCADE CONSTRAINTS;
DROP TABLE inventory_line CASCADE CONSTRAINTS;
DROP TABLE product CASCADE CONSTRAINTS;
DROP TABLE Customer CASCADE CONSTRAINTS;
DROP TABLE Employee CASCADE CONSTRAINTS;

drop sequence e_id_seq;
drop sequence c_id_seq;
drop sequence p_id_seq;
drop sequence inv_id_seq;
drop sequence i_id_seq;
drop sequence ol_id_seq;


Create Table Employee
(e_id NUMBER(5),
e_last VARCHAR2(30),
e_first VARCHAR2(30),
e_mi CHAR(1),
e_birthdate DATE,
e_address VARCHAR2(30),
e_zip VARCHAR2(10),
e_phone VARCHAR2(10),
e_position VARCHAR2(30),
CONSTRAINT customer_e_id_pk PRIMARY KEY (e_id));

CREATE TABLE Customer
(c_id NUMBER(5),
c_last VARCHAR2(30),
c_first VARCHAR2(30),
c_mi CHAR(1),
c_birthdate DATE,
c_address VARCHAR2(30),
c_zip VARCHAR2(10),
c_phone VARCHAR2(10),
c_userid VARCHAR2(50),
c_password VARCHAR2(15),
CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));

CREATE TABLE Product
(p_id NUMBER(5),
p_name VARCHAR2(20),
p_price NUMBER(6,2),
CONSTRAINT Product_p_id_pk PRIMARY KEY (p_id);

CREATE TABLE Inventory_line
(inv_id NUMBER(5),
inv_quant NUMBER(6),
p_id NUMBER(5),
CONSTRAINT Inventory_line_p_id_fk FOREIGN KEY (p_id) REFERENCES Product(p_id),
CONSTRAINT Inventory_line_inv_id_pk PRIMARY KEY(inv_id));

CREATE TABLE Invoices
(i_id NUMBER(5),
c_id NUMBER(5),
e_id NUMBER(5),
inv_id NUMBER(5),
i_quant NUMBER(5) NOT NULL,
CONSTRAINT Invoices_c_id_fk FOREIGN KEY (c_id) REFERENCES Customer(c_id),
CONSTRAINT Invoices_e_id_fk FOREIGN KEY (e_id) REFERENCES Employee(e_id),
CONSTRAINT Invoices_inv_id_fk FOREIGN KEY (inv_id) REFERENCES Inventory_line(inv_id),
CONSTRAINT Invoices_i_id_pk PRIMARY KEY(i_id));

CREATE TABLE Order_line
(ol_id NUMBER(5),
p_id NUMBER(5),
ol_quant NUMBER(6),
ol_odate DATE,
ol_rdate DATE,
CONSTRAINT Order_line_p_id_fk FOREIGN KEY (p_id) REFERENCES Product(p_id),
CONSTRAINT Invoices_ol_id_pk PRIMARY KEY(ol_id));

--Creating sequences
create sequence e_id_seq
start with 1
increment by 1;

create sequence c_id_seq
start with 1
increment by 1;

create sequence p_id_seq
start with 1
increment by 1;

create sequence inv_id_seq
start with 1
increment by 1;

create sequence i_id_seq
start with 1
increment by 1;

create sequence ol_id_seq
start with 1
increment by 1;

--inserting records into Employee
INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Grant', 'Tomir', 'R', to_date('12/13/1967', 'mm/dd/yyyy'), '9815 Square Dr.', '33408', '904558599', 'Cashier');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'William', 'Snorgz', 'Z', to_date('07/25/1989', 'mm/dd/yyyy'), '215 Maple Dr.', '30928', '904669742', 'Cashier');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Tara', 'Hardlum', 'W', to_date('01/06/1954', 'mm/dd/yyyy'), '623 Shady Ave.', '38452', '851558566', 'Manager');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Robert', 'Mooln', 'S', to_date('10/17/1972', 'mm/dd/yyyy'), '8940 Gay St.', '34512', '985554572', 'Supervisor');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Jim', 'Route', 'B', to_date('07/06/1985', 'mm/dd/yyyy'), '4215 Circle Dr.', '35608', '907854599', 'Janitor');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Mark', 'Gooden', 'H', to_date('04/12/1991', 'mm/dd/yyyy'), '26 Oak Dr.', '37452', '912123599', 'Cashier');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Janessa', 'Freel', 'W', to_date('05/09/1984', 'mm/dd/yyyy'), '94 Apple Ave.', '37986', '865554215', 'Stock Manager');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Trevor', 'Klotz', 'C', to_date('11/27/1977', 'mm/dd/yyyy'), '885 Happy Ave.', '37859', '704556565', 'IT');

INSERT INTO EMPLOYEE VALUES
(e_id_seq.nextVal, 'Chris', 'Thomas', 'P', to_date('03/03/1987', 'mm/dd/yyyy'), '15 Pine Dr.', '31245', '304558665', 'Cashier');


--inserting records into Customer
INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Clarissa', 'yeeman', 'D', to_date('11/10/1969', 'mm/dd/yyyy'), '34 Pickle St.', '33418', '354558685', 'Cyee', 'p1ckles');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Robert', 'Zoit', 'S', to_date('01/10/1987', 'mm/dd/yyyy'), '456 Square Dr.', '34518', '454558444', 'Rzoi', 'rip.tide');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'O', 'Simpson', 'J', to_date('02/07/1978', 'mm/dd/yyyy'), '854 Square Dr.', '38718', '784558597', 'Osim', 'g0ody');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Fred', 'Flinstone', 'Z', to_date('05/23/1989', 'mm/dd/yyyy'), '912 Square Dr.', '39878', '124558785', 'Ffli', 'wo0o0o.');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Trevon', 'Mitchell', 'Q', to_date('02/17/1998', 'mm/dd/yyyy'), '987 Square Dr.', '31118', '654557655', 'Tmit', '.*.*.');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Mark', 'Quixote', 'L', to_date('08/29/1965', 'mm/dd/yyyy'), '452 Square Dr.', '32458', '999558557', 'Mqui', 'h3ll0');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Jesus', 'Hernandez', 'N', to_date('18/10/1985', 'mm/dd/yyyy'), '1565 Square Dr.', '36587', '454558785', 'Jher', '0r@nges');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Toni', 'Lolyman', 'F', to_date('05/30/1964', 'mm/dd/yyyy'), '5523 Square Dr.', '37577', '844558785', 'Tlol', 'hunt3r');

INSERT INTO CUSTOMER VALUES
(c_id_seq.nextVal, 'Rebecca', 'Miney', 'J', to_date('07/21/1976', 'mm/dd/yyyy'), '7584 Square Dr.', '36945', '784556854', 'Rmin', '99bottl3s');


--inserting records into product
INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'The Hobbit', 15.99);

INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'Catch 22', 12.99);

INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'Brave New World', 19.99);

INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'Fahrenheit 451', 11.99);

INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'Deliverance', 9.99);

INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'The Catcher in the Rye', 14.99);

INSERT INTO PRODUCT VALUES
(p_id_seq.nextVal, 'The Great Gatsby', 11.99);


--inserting records into inventory line
INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 1235, 1);

INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 3465, 2);

INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 6887, 3);

INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 1254, 4);

INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 2854, 5);

INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 9875, 6);

INSERT INTO INVENTORY_LINE VALUES
(inv_id_seq.nextVal, 924, 7);


--inserting records into invoices
INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3,

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 3, 1, 2);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 9, 9, 2, 1);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 4, 2, 3);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 2, 6, 4);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 1, 9, 10);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 1, 6, 1);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 5, 9, 1);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 6, 2, 5);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 7, 6, 1);

INSERT INTO INVOICES VALUES
(i_id_seq.nextVal, 3, 9, 1, 1);


--inserting records into order_line
INSERT INTO ORDER_LINE VALUES
(ol_id_seq.nextVal, 2, 1000, to_date('04/02/2015', 'mm/dd/yyyy'), to_date('07/07/2015', 'mm/dd/yyyy'));

INSERT INTO ORDER_LINE VALUES
(ol_id_seq.nextVal, 1, 1600, to_date('04/02/2015', 'mm/dd/yyyy'), to_date('07/07/2015', 'mm/dd/yyyy'));

INSERT INTO ORDER_LINE VALUES
(ol_id_seq.nextVal, 5, 2000, to_date('02/12/2015', 'mm/dd/yyyy'), to_date('02/23/2015', 'mm/dd/yyyy'));

INSERT INTO ORDER_LINE VALUES
(ol_id_seq.nextVal, 4, 6000, to_date('02/12/2015', 'mm/dd/yyyy'), to_date('02/23/2015', 'mm/dd/yyyy'));

INSERT INTO ORDER_LINE VALUES
(ol_id_seq.nextVal, 3, 2500, to_date('02/12/2015', 'mm/dd/yyyy'), to_date('02/23/2015', 'mm/dd/yyyy'));

INSERT INTO ORDER_LINE VALUES
(ol_id_seq.nextVal, 6, 4000, to_date('01/01/2015', 'mm/dd/yyyy'), to_date('01/19/2015', 'mm/dd/yyyy'));

COMMIT;