Term Project (Actual)

term.sql

/* Term Project SQL - Greg Swaim & Robert Moore*/
DROP TABLE PAYROLL_ITEM CASCADE CONSTRAINTS;
DROP TABLE PAYROLL_PAYMENT CASCADE CONSTRAINTS;
DROP TABLE PAYROLL CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE_SCHEDULE CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE_TYPE CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE VENDOR CASCADE CONSTRAINTS;
DROP TABLE PURCHASE_ORDER CASCADE CONSTRAINTS;
DROP TABLE PURCHASE_ORDER_ITEM CASCADE CONSTRAINTS;
DROP TABLE HOURS_OF_OPERATION CASCADE CONSTRAINTS;
DROP TABLE RECEIVING_ORDER CASCADE CONSTRAINTS;
DROP TABLE CLOSED_HOLIDAY CASCADE CONSTRAINTS;
DROP TABLE RECEIVED_ITEM CASCADE CONSTRAINTS;
DROP TABLE STOCK CASCADE CONSTRAINTS;
DROP TABLE ITEM CASCADE CONSTRAINTS;
DROP TABLE FOOD_ITEM CASCADE CONSTRAINTS;
DROP TABLE BOOK_ITEM CASCADE CONSTRAINTS;
DROP TABLE MONO_ITEM CASCADE CONSTRAINTS;
DROP TABLE ACCOMMODATION CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER_ORDER CASCADE CONSTRAINTS;
DROP TABLE RESERVATION_TRANSACTION CASCADE CONSTRAINTS;
DROP TABLE RESERVATION CASCADE CONSTRAINTS;
DROP TABLE ORDER_TRANSACTION CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE BUSINESS CASCADE CONSTRAINTS;
DROP TABLE RESTAURANT_STORE CASCADE CONSTRAINTS;
DROP TABLE MONO_STORE CASCADE CONSTRAINTS;
DROP TABLE GIFT_STORE CASCADE CONSTRAINTS;
DROP TABLE BOOK_STORE CASCADE CONSTRAINTS;
DROP TABLE HOTEL_STORE CASCADE CONSTRAINTS;
DROP TABLE SHIFT CASCADE CONSTRAINTS;
DROP TABLE ADJUST_STOCK CASCADE CONSTRAINTS;
DROP TABLE ADJUSTED_STOCK_ITEM CASCADE CONSTRAINTS;
DROP TABLE SHIPMENT CASCADE CONSTRAINTS;
/* Create The Tables */
   
CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER PRIMARY KEY,
    CUSTOMER_FIRST_NAME VARCHAR2(25) NOT NULL,
    CUSTOMER_LAST_NAME VARCHAR2(25) NOT NULL,
    CUSTOMER_ADDRESS_STREET1 VARCHAR2(75),
    CUSTOMER_ADDRESS_STREET2 VARCHAR2(75),
    CUSTOMER_CITY VARCHAR2(50),
    CUSTOMER_STATE VARCHAR2(20),
    CUSTOMER_POSTALCODE VARCHAR2(20),
    CUSTOMER_COUNTRY VARCHAR2(30),
    CUSTOMER_EMAIL VARCHAR2(100),
    CUSTOMER_PHONE_NUMBER NUMBER(11) NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    CREATE INDEX customer_firstName_index ON CUSTOMER(CUSTOMER_FIRST_NAME);
    CREATE INDEX customer_lastName_index  ON CUSTOMER(CUSTOMER_LAST_NAME);
    CREATE INDEX customer_phoneNumber_index ON CUSTOMER(CUSTOMER_PHONE_NUMBER);
    CREATE INDEX customer_email_index ON CUSTOMER(CUSTOMER_EMAIL);
 
CREATE TABLE BUSINESS(
    BUSINESS_ID NUMBER PRIMARY KEY,
    BUSINESS_TYPE VARCHAR2(1) NOT NULL,
    BUSINESS_NAME VARCHAR2(30) NOT NULL
);
CREATE TABLE ITEM(
    ITEM_ID NUMBER PRIMARY KEY,
    ITEM_NAME VARCHAR2(60) NOT NULL,
    ITEM_PRICE NUMBER(13,2) NOT NULL,
    ITEM_TYPE VARCHAR2(1),
    ITEM_REORDER_STOCK NUMBER DEFAULT 0,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    CREATE INDEX item_name_index ON ITEM(ITEM_NAME);
    CREATE INDEX item_type_index ON ITEM(ITEM_TYPE);
 
  
CREATE TABLE STOCK(
    STOCK_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL,
    COGS_PRICE NUMBER(10,2) NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
 
    ALTER TABLE STOCK
    ADD CONSTRAINT fk_stock_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
    ALTER TABLE STOCK
    ADD CONSTRAINT fk_stock_to_item
    FOREIGN KEY (FK_ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
 
 
CREATE TABLE EMPLOYEE_TYPE(
    EMPLOYEE_TYPE_ID NUMBER PRIMARY KEY,
    EMPLOYEE_TYPE_NAME VARCHAR2(50) NOT NULL
);
   
CREATE TABLE EMPLOYEE(
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    FK_EMPLOYEE_TYPE_ID NUMBER NOT NULL,
    EMPLOYEE_FIRST_NAME VARCHAR2(30) NOT NULL,
    EMPLOYEE_LAST_NAME VARCHAR2(30) NOT NULL,
    EMPLOYEE_HRLY_WAGE NUMBER(6,2) NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    ALTER TABLE EMPLOYEE
    ADD CONSTRAINT fk_employee_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
    ALTER TABLE EMPLOYEE
    ADD CONSTRAINT fk_employee_to_employee_type
    FOREIGN KEY (FK_EMPLOYEE_TYPE_ID)
    REFERENCES EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID);
 
    CREATE INDEX employee_firstName_index ON EMPLOYEE(EMPLOYEE_FIRST_NAME);
    CREATE INDEX employee_lastName_index ON EMPLOYEE(EMPLOYEE_LAST_NAME);
 
CREATE TABLE SHIPMENT(
    SHIPMENT_ID NUMBER PRIMARY KEY,
    TRACKING_NUMBER VARCHAR2(100) NOT NULL,
    WEIGHT_KG NUMBER(5) NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
CREATE TABLE SHIFT(
    SHIFT_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    CLOCK_IN DATE NOT NULL,
    CLOCK_OUT DATE
);
    ALTER TABLE SHIFT
    ADD CONSTRAINT fk_shift_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
 
CREATE TABLE PAYROLL (
    PAYROLL_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
 
    ALTER TABLE PAYROLL
    ADD CONSTRAINT fk_payroll_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
   
CREATE TABLE PAYROLL_PAYMENT(
    PAYROLL_PAYMENT_ID NUMBER PRIMARY KEY,
    FK_PAYROLL_ID NUMBER NOT NULL,
    FK_EMPLOYEE_ID NUMBER NOT NULL
);
    ALTER TABLE PAYROLL_PAYMENT
    ADD CONSTRAINT fk_payroll_payment_to_payroll
    FOREIGN KEY (FK_PAYROLL_ID)
    REFERENCES PAYROLL(PAYROLL_ID);
   
    ALTER TABLE PAYROLL_PAYMENT
    ADD CONSTRAINT fk_payroll_payment_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
   
CREATE TABLE PAYROLL_ITEM(
    PAYROLL_ITEM_ID NUMBER PRIMARY KEY,
    FK_PAYROLL_PAYMENT_ID NUMBER NOT NULL,
    PAYROLL_ITEM_DESCRIPTION VARCHAR2(60) NOT NULL,
    PAYROLL_ITEM_AMOUNT NUMBER (12,2) NOT NULL
);
 
    ALTER TABLE PAYROLL_ITEM
    ADD CONSTRAINT fk_payroll_item_to_payroll_payment
    FOREIGN KEY (FK_PAYROLL_PAYMENT_ID)
    REFERENCES PAYROLL_PAYMENT(PAYROLL_PAYMENT_ID);
 
CREATE TABLE HOURS_OF_OPERATION (
    HOURS_OF_OPERATION_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    DAY_OF_WEEK NUMBER NOT NULL,
    START_TIME DATE NOT NULL,
    STOP_TIME DATE NOT NULL
);
    ALTER TABLE HOURS_OF_OPERATION
    ADD CONSTRAINT fk_hours_of_operation_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE EMPLOYEE_SCHEDULE(
    EMPLOYEE_SCHEDULE_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_HOURS_OF_OPERATION_ID NUMBER NOT NULL
);
 
    ALTER TABLE EMPLOYEE_SCHEDULE
    ADD CONSTRAINT fk_employee_schedule_to_hours_of_opreation
    FOREIGN KEY (FK_HOURS_OF_OPERATION_ID)
    REFERENCES HOURS_OF_OPERATION(HOURS_OF_OPERATION_ID);
   
    ALTER TABLE EMPLOYEE_SCHEDULE
    ADD CONSTRAINT fk_employee_schedule_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);   
   
CREATE TABLE VENDOR(
    VENDOR_ID NUMBER PRIMARY KEY,
    VENDOR_NAME VARCHAR2(60),
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
 
CREATE TABLE PURCHASE_ORDER(
    PURCHASE_ORDER_ID NUMBER PRIMARY KEY,
    FK_VENDOR_ID NUMBER,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_BUSINESS_ID NUMBER NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    ALTER TABLE PURCHASE_ORDER
    ADD CONSTRAINT fk_purchase_order_to_vendor
    FOREIGN KEY (FK_VENDOR_ID)
    REFERENCES VENDOR(VENDOR_ID);
 
    ALTER TABLE PURCHASE_ORDER
    ADD CONSTRAINT fk_purchase_order_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
   
    ALTER TABLE PURCHASE_ORDER
    ADD CONSTRAINT fk_purchase_order_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
   
CREATE TABLE PURCHASE_ORDER_ITEM(
    PURCHASE_ORDER_ITEM_ID NUMBER PRIMARY KEY,
    FK_PURCHASE_ORDER_ID NUMBER NOT NULL,
    ADJUST_STOCK_QTY NUMBER NOT NULL,
    COGS_PRICE_EA NUMBER(10,2) NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL
);
    ALTER TABLE PURCHASE_ORDER_ITEM
    ADD CONSTRAINT fk_purchase_order_item_to_purchase_order
    FOREIGN KEY (FK_PURCHASE_ORDER_ID)
    REFERENCES PURCHASE_ORDER(PURCHASE_ORDER_ID);
 
    ALTER TABLE PURCHASE_ORDER_ITEM
    ADD CONSTRAINT fk_purchase_order_item_to_item
    FOREIGN KEY (FK_ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
   
CREATE TABLE RECEIVING_ORDER(
    RECEIVING_ORDER_ID NUMBER PRIMARY KEY,
    FK_PURCHASE_ORDER_ID NUMBER NOT NULL,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_BUSINESS_ID NUMBER NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    ALTER TABLE RECEIVING_ORDER
    ADD CONSTRAINT fk_receiving_order_to_purchase_order
    FOREIGN KEY (FK_PURCHASE_ORDER_ID)
    REFERENCES PURCHASE_ORDER(PURCHASE_ORDER_ID);
   
    ALTER TABLE RECEIVING_ORDER
    ADD CONSTRAINT fk_receiving_order_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);   
   
    ALTER TABLE RECEIVING_ORDER
    ADD CONSTRAINT fk_receiving_order_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);       
 
CREATE TABLE CLOSED_HOLIDAY (
    CLOSED_HOLIDAY_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL
);
    ALTER TABLE CLOSED_HOLIDAY
    ADD CONSTRAINT fk_closed_holiday_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE RECEIVED_ITEM(
    RECEIVED_ITEM NUMBER PRIMARY KEY,
    FK_RECEIVING_ORDER_ID NUMBER NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL,
    FK_STOCK_ID NUMBER NOT NULL
);
 
    ALTER TABLE RECEIVED_ITEM
    ADD CONSTRAINT fk_received_item_to_receiving_order
    FOREIGN KEY (FK_RECEIVING_ORDER_ID)
    REFERENCES RECEIVING_ORDER(RECEIVING_ORDER_ID);
   
    ALTER TABLE RECEIVED_ITEM
    ADD CONSTRAINT fk_received_item_to_item_id
    FOREIGN KEY (FK_ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
 
    ALTER TABLE RECEIVED_ITEM
    ADD CONSTRAINT fk_received_item_to_stock
    FOREIGN KEY (FK_STOCK_ID)
    REFERENCES STOCK(STOCK_ID);
   
CREATE TABLE FOOD_ITEM(
    ITEM_ID NUMBER PRIMARY KEY
);
   
    ALTER TABLE FOOD_ITEM
    ADD CONSTRAINT FOOD_ITEM_is_item
    FOREIGN KEY (ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
 
CREATE TABLE BOOK_ITEM(
    ITEM_ID NUMBER PRIMARY KEY
);
    ALTER TABLE BOOK_ITEM
    ADD CONSTRAINT BOOK_ITEM_is_item
    FOREIGN KEY (ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
 
CREATE TABLE MONO_ITEM(
    ITEM_ID NUMBER PRIMARY KEY
);
    ALTER TABLE MONO_ITEM
    ADD CONSTRAINT MONO_ITEM_is_item
    FOREIGN KEY (ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
 
CREATE TABLE ACCOMMODATION (
    ACCOMMODATION_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    ACCOMMODATION_PRICE NUMBER(13,2) NOT NULL,
    ACCOMMODATION_TITLE VARCHAR2(20) NOT NULL,
    ACCOMMODATION_DESCRIPTION VARCHAR2(500) NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
 
   ALTER TABLE ACCOMMODATION
   ADD CONSTRAINT fk_accommodation_to_business_id
   FOREIGN KEY (FK_BUSINESS_ID)
   REFERENCES BUSINESS(BUSINESS_ID);  
 
CREATE TABLE CUSTOMER_ORDER(
    CUSTOMER_ORDER_ID NUMBER PRIMARY KEY,
    FK_SHIPMENT_ID NUMBER,
    FK_CUSTOMER_ID NUMBER,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
   ALTER TABLE CUSTOMER_ORDER
   ADD CONSTRAINT fk_customer_order_to_shipment
   FOREIGN KEY (FK_SHIPMENT_ID)
   REFERENCES SHIPMENT(SHIPMENT_ID);  
 
   ALTER TABLE CUSTOMER_ORDER
   ADD CONSTRAINT fk_customer_order_to_customer
   FOREIGN KEY (FK_CUSTOMER_ID)
   REFERENCES CUSTOMER(CUSTOMER_ID);  
 
CREATE TABLE RESERVATION (
    RESERVATION_ID NUMBER PRIMARY KEY,
    FK_CUSTOMER_ID NUMBER NOT NULL,
    FK_ACCOMMODATION_ID NUMBER NOT NULL,
    CHECKIN_DATE DATE DEFAULT SYSDATE NOT NULL,
    CHECKOUT_DATE DATE DEFAULT SYSDATE NOT NULL,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    ALTER TABLE RESERVATION
    ADD CONSTRAINT fk_reservation_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);

    ALTER TABLE RESERVATION
    ADD CONSTRAINT fk_reservation_to_customer
    FOREIGN KEY (FK_CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID);
   
   ALTER TABLE RESERVATION
   ADD CONSTRAINT fk_reservation_to_accommodation
   FOREIGN KEY (FK_ACCOMMODATION_ID)
   REFERENCES ACCOMMODATION(ACCOMMODATION_ID);    
 
CREATE TABLE RESERVATION_TRANSACTION (
    RESERVATION_TRANSACTION_ID NUMBER PRIMARY KEY,
    FK_RESERVATION_ID NUMBER NOT NULL
);
 
   ALTER TABLE RESERVATION_TRANSACTION
   ADD CONSTRAINT fk_reservation_transaction_to_reservation
   FOREIGN KEY (FK_RESERVATION_ID)
   REFERENCES RESERVATION(RESERVATION_ID);  
 
CREATE TABLE ORDER_TRANSACTION(
    ORDER_TRANSACTION_ID NUMBER PRIMARY KEY,
    FK_CUSTOMER_ORDER_ID NUMBER NOT NULL,
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
 
    ALTER TABLE ORDER_TRANSACTION
    ADD CONSTRAINT fk_order_transaction_to_customer_order
    FOREIGN KEY (FK_CUSTOMER_ORDER_ID)
    REFERENCES CUSTOMER_ORDER(CUSTOMER_ORDER_ID);
 
CREATE TABLE RESTAURANT_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
 
    ALTER TABLE RESTAURANT_STORE
    ADD CONSTRAINT restaurant_to_business
    FOREIGN KEY (BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE MONO_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
 
    ALTER TABLE MONO_STORE
    ADD CONSTRAINT mono_store_to_business
    FOREIGN KEY (BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE GIFT_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
 
    ALTER TABLE GIFT_STORE
    ADD CONSTRAINT gift_store_to_business
    FOREIGN KEY (BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE BOOK_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
 
    ALTER TABLE BOOK_STORE
    ADD CONSTRAINT book_store_to_business
    FOREIGN KEY (BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE HOTEL_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
 
    ALTER TABLE HOTEL_STORE
    ADD CONSTRAINT hotel_store_to_business
    FOREIGN KEY (BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
 
CREATE TABLE ADJUST_STOCK(
    ADJUST_STOCK_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_CUSTOMER_ORDER_ID NUMBER,
    FK_BUSINESS_ID NUMBER NOT NULL,
    ADJUSTED_STOCK_DESCRIPTION VARCHAR2(60),
    TIMESTAMP_CREATED DATE DEFAULT SYSDATE
);
    ALTER TABLE ADJUST_STOCK
    ADD CONSTRAINT fk_adjust_stock_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID)
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
   
    ALTER TABLE ADJUST_STOCK
    ADD CONSTRAINT fk_adjust_stock_to_customer_order
    FOREIGN KEY (FK_CUSTOMER_ORDER_ID)
    REFERENCES CUSTOMER_ORDER(CUSTOMER_ORDER_ID);
 
    ALTER TABLE ADJUST_STOCK
    ADD CONSTRAINT fk_adjust_stock_to_business
    FOREIGN KEY (FK_BUSINESS_ID)
    REFERENCES BUSINESS(BUSINESS_ID);
   
/*
THIS IS A LEDGER
MATH_OPERATION 1 is ADDITION
MATH_OPERATION 2 is SUBTRACTION
 
*/
CREATE TABLE ADJUSTED_STOCK_ITEM(
    ADJUSTED_STOCK_ITEM_ID NUMBER PRIMARY KEY,
    FK_ADJUST_STOCK_ID NUMBER,
    FK_ITEM_ID NUMBER NOT NULL,
    MATH_OPERATION NUMBER(1) NOT NULL
);
 
    ALTER TABLE ADJUSTED_STOCK_ITEM
    ADD CONSTRAINT fk_adjusted_stock_item_to_stock_id
    FOREIGN KEY (FK_ADJUST_STOCK_ID)
    REFERENCES ADJUST_STOCK(ADJUST_STOCK_ID);
   
    ALTER TABLE ADJUSTED_STOCK_ITEM
    ADD CONSTRAINT fk_adjusted_stock_item_to_item_id
    FOREIGN KEY (FK_ITEM_ID)
    REFERENCES ITEM(ITEM_ID);
 
 
 
 
 
 
   
-- Some Sample Data
 
--TABLES to place 5-10 items into

INSERT INTO CUSTOMER (    CUSTOMER_ID,    CUSTOMER_FIRST_NAME,    CUSTOMER_LAST_NAME,    CUSTOMER_ADDRESS_STREET1,    CUSTOMER_ADDRESS_STREET2,    CUSTOMER_CITY,    CUSTOMER_STATE,    CUSTOMER_POSTALCODE,    CUSTOMER_COUNTRY,    CUSTOMER_EMAIL,    CUSTOMER_PHONE_NUMBER)    VALUES(1,'Stanly','Stucko','125 N Doug','Apt#84','Stuckersville','OR','97471','US','[email protected]','15415321234');
INSERT INTO CUSTOMER (    CUSTOMER_ID,    CUSTOMER_FIRST_NAME,    CUSTOMER_LAST_NAME,    CUSTOMER_ADDRESS_STREET1,    CUSTOMER_ADDRESS_STREET2,    CUSTOMER_CITY,    CUSTOMER_STATE,    CUSTOMER_POSTALCODE,    CUSTOMER_COUNTRY,    CUSTOMER_EMAIL,    CUSTOMER_PHONE_NUMBER)    VALUES(2,'Stacey','Stinker','114 S Main','1/2','Santa Barbra','CA','90210','US','[email protected]','19072321234');
INSERT INTO CUSTOMER (    CUSTOMER_ID,    CUSTOMER_FIRST_NAME,    CUSTOMER_LAST_NAME,    CUSTOMER_ADDRESS_STREET1,    CUSTOMER_ADDRESS_STREET2,    CUSTOMER_CITY,    CUSTOMER_STATE,    CUSTOMER_POSTALCODE,    CUSTOMER_COUNTRY,    CUSTOMER_EMAIL,    CUSTOMER_PHONE_NUMBER)    VALUES(3,'Steven','Spierner','84 N Tang Ave','Orange Department','Suckersville','UT','93120','US','[email protected]','15021231456');
INSERT INTO CUSTOMER (    CUSTOMER_ID,    CUSTOMER_FIRST_NAME,    CUSTOMER_LAST_NAME,    CUSTOMER_ADDRESS_STREET1,    CUSTOMER_ADDRESS_STREET2,    CUSTOMER_CITY,    CUSTOMER_STATE,    CUSTOMER_POSTALCODE,    CUSTOMER_COUNTRY,    CUSTOMER_EMAIL,    CUSTOMER_PHONE_NUMBER)    VALUES(4,'Staunchy','StuckinMine','12 Coal Sucker Ave','Mine #b','Wasilla','AK','99171','US','[email protected]','19071123456');
INSERT INTO CUSTOMER (    CUSTOMER_ID,    CUSTOMER_FIRST_NAME,    CUSTOMER_LAST_NAME,    CUSTOMER_ADDRESS_STREET1,    CUSTOMER_ADDRESS_STREET2,    CUSTOMER_CITY,    CUSTOMER_STATE,    CUSTOMER_POSTALCODE,    CUSTOMER_COUNTRY,    CUSTOMER_EMAIL,    CUSTOMER_PHONE_NUMBER)    VALUES(5,'Obert','Qdiddle','11 Sanity Ln','Patient #2932','Quebec','MN','GB12AC','CA','[email protected]','25415221234');





/* 
 Tables without example data

INSERT INTO PAYROLL (     PAYROLL_ID,    FK_BUSINESS_ID)    VALUES(1,'');

INSERT INTO PAYROLL_PAYMENT (    PAYROLL_PAYMENT_ID,    FK_PAYROLL_ID,    FK_EMPLOYEE_ID)    VALUES(1,'','');

INSERT INTO PAYROLL_ITEM (    PAYROLL_ITEM_ID,    FK_PAYROLL_PAYMENT_ID,    PAYROLL_ITEM_DESCRIPTION,    PAYROLL_ITEM_AMOUNT)    VALUES(1,'','','','');

INSERT INTO HOURS_OF_OPERATION (    HOURS_OF_OPERATION_ID,    FK_BUSINESS_ID,    DAY_OF_WEEK,    START_TIME,    STOP_TIME)    VALUES(1,'','','','');

INSERT INTO EMPLOYEE_SCHEDULE (    EMPLOYEE_SCHEDULE_ID,    FK_EMPLOYEE_ID,    FK_HOURS_OF_OPERATION_ID)    VALUES(1,'','');

INSERT INTO PURCHASE_ORDER (    PURCHASE_ORDER_ID,    FK_VENDOR_ID,    FK_EMPLOYEE_ID,    FK_BUSINESS_ID)    VALUES(1,'','','');

INSERT INTO PURCHASE_ORDER_ITEM (    PURCHASE_ORDER_ITEM_ID,    FK_PURCHASE_ORDER_ID,    ADJUST_STOCK_QTY,    FK_ITEM_ID)    VALUES(1,'','','');

INSERT INTO RECEIVING_ORDER (    RECEIVING_ORDER_ID,    FK_PURCHASE_ORDER_ID,    FK_EMPLOYEE_ID,    FK_BUSINESS_ID)    VALUES(1,'','','');

INSERT INTO CLOSED_HOLIDAY (    CLOSED_HOLIDAY_ID,    FK_BUSINESS_ID)    VALUES(1,'');

INSERT INTO RECEIVED_ITEM (    RECEIVED_ITEM,    FK_RECEIVING_ORDER_ID,    FK_ITEM_ID,    FK_STOCK_ID)    VALUES(1,'','','');


INSERT INTO RESERVATION_TRANSACTION (    RESERVATION_TRANSACTION_ID,    FK_RESERVATION_ID)    VALUES(1,'');

INSERT INTO ORDER_TRANSACTION (    ORDER_TRANSACTION_ID,    FK_CUSTOMER_ORDER_ID)    VALUES(1,'');
*/
 
 
 
INSERT INTO VENDOR (VENDOR_ID,VENDOR_NAME) VALUES(1,'Shrine Factory');   
INSERT INTO VENDOR (VENDOR_ID,VENDOR_NAME) VALUES(2,'Wal-Mart');
INSERT INTO VENDOR (VENDOR_ID,VENDOR_NAME) VALUES(3,'KnickKnack Distribution');
INSERT INTO VENDOR (VENDOR_ID,VENDOR_NAME) VALUES(4,'ACME Distribution GMHQ');
INSERT INTO VENDOR (VENDOR_ID,VENDOR_NAME) VALUES(5,'Kings and Queens Corp'); 

INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (1,'SilentMonk');
INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (2,'Monk');
INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (3, 'Manager');
INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (4, 'Nun');
INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (5, 'ShiftManager');
INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (6, 'GeneralLabor');
INSERT INTO EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID,EMPLOYEE_TYPE_NAME) VALUES (7, 'Cook');
 
 
INSERT INTO BUSINESS (BUSINESS_ID,BUSINESS_TYPE,BUSINESS_NAME) VALUES(1,'M','Mono Crap Store - Produce');
INSERT INTO BUSINESS (BUSINESS_ID,BUSINESS_TYPE,BUSINESS_NAME) VALUES(2,'R','Grange Fine Dining');
INSERT INTO BUSINESS (BUSINESS_ID,BUSINESS_TYPE,BUSINESS_NAME) VALUES(3,'H','Buckfast Abbey');
INSERT INTO BUSINESS (BUSINESS_ID,BUSINESS_TYPE,BUSINESS_NAME) VALUES(4,'B','Books and Books');
INSERT INTO BUSINESS (BUSINESS_ID,BUSINESS_TYPE,BUSINESS_NAME) VALUES(5,'G','Gifts and Cigars');

INSERT INTO MONO_STORE(BUSINESS_ID) VALUES(1);
INSERT INTO RESTAURANT_STORE(BUSINESS_ID) VALUES(2);
INSERT INTO BOOK_STORE(BUSINESS_ID) VALUES(4);
INSERT INTO HOTEL_STORE(BUSINESS_ID) VALUES(3);
INSERT INTO GIFT_STORE(BUSINESS_ID) VALUES(5);

INSERT INTO EMPLOYEE (EMPLOYEE_ID,FK_BUSINESS_ID,FK_EMPLOYEE_TYPE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,EMPLOYEE_HRLY_WAGE) VALUES(1,1,2,'Lee','Sin',7.90);
INSERT INTO EMPLOYEE (EMPLOYEE_ID,FK_BUSINESS_ID,FK_EMPLOYEE_TYPE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,EMPLOYEE_HRLY_WAGE) VALUES(2,2,7,'Kennen','Shortstuff',8.90);
INSERT INTO EMPLOYEE (EMPLOYEE_ID,FK_BUSINESS_ID,FK_EMPLOYEE_TYPE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,EMPLOYEE_HRLY_WAGE) VALUES(3,3,1,'Rammus','Stormal',9.90);
INSERT INTO EMPLOYEE (EMPLOYEE_ID,FK_BUSINESS_ID,FK_EMPLOYEE_TYPE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,EMPLOYEE_HRLY_WAGE) VALUES(4,4,1,'Mundo','DoesWhatWants',10.90);
INSERT INTO EMPLOYEE (EMPLOYEE_ID,FK_BUSINESS_ID,FK_EMPLOYEE_TYPE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,EMPLOYEE_HRLY_WAGE) VALUES(5,5,1,'Shaco','Sneakpants',11.90);


INSERT INTO SHIFT (SHIFT_ID, FK_EMPLOYEE_ID, CLOCK_IN, CLOCK_OUT) VALUES(1,2,TO_DATE('06/01/2019 4:00', 'mm/dd/yyyy hh24:mi'),TO_DATE('06/01/2019 21:02', 'mm/dd/yyyy hh24:mi'));
INSERT INTO SHIFT (SHIFT_ID, FK_EMPLOYEE_ID, CLOCK_IN, CLOCK_OUT) VALUES(2,2,TO_DATE('06/02/2019 4:01', 'mm/dd/yyyy hh24:mi'),TO_DATE('06/02/2019 21:03', 'mm/dd/yyyy hh24:mi'));
INSERT INTO SHIFT (SHIFT_ID, FK_EMPLOYEE_ID, CLOCK_IN, CLOCK_OUT) VALUES(3,2,TO_DATE('06/03/2019 3:52', 'mm/dd/yyyy hh24:mi'),TO_DATE('06/03/2019 21:01', 'mm/dd/yyyy hh24:mi'));
INSERT INTO SHIFT (SHIFT_ID, FK_EMPLOYEE_ID, CLOCK_IN, CLOCK_OUT) VALUES(4,2,TO_DATE('06/04/2019 4:02', 'mm/dd/yyyy hh24:mi'),TO_DATE('06/04/2019 21:00', 'mm/dd/yyyy hh24:mi'));


INSERT INTO ITEM (ITEM_ID,ITEM_NAME,ITEM_PRICE,ITEM_TYPE,ITEM_REORDER_STOCK) VALUES(1,'Shrine of SQL',10.50,'M',1);
INSERT INTO ITEM (ITEM_ID,ITEM_NAME,ITEM_PRICE,ITEM_TYPE,ITEM_REORDER_STOCK) VALUES(2,'Eatable Sludge',0.10,'R',2);
INSERT INTO ITEM (ITEM_ID,ITEM_NAME,ITEM_PRICE,ITEM_TYPE,ITEM_REORDER_STOCK) VALUES(3,'Bath Robe',159.85,'H',2);
INSERT INTO ITEM (ITEM_ID,ITEM_NAME,ITEM_PRICE,ITEM_TYPE,ITEM_REORDER_STOCK) VALUES(4,'Book of SQL',85.00,'B',50);
INSERT INTO ITEM (ITEM_ID,ITEM_NAME,ITEM_PRICE,ITEM_TYPE,ITEM_REORDER_STOCK) VALUES(5,'Train Beer Cozy',4.50,'G',51);

--- These items did not come from a purchase order - a nun smuggled them in.
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(1,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(2,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(3,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(4,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(5,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(6,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(7,1,1,7.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(8,2,2,0.01);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(9,2,2,0.01);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(10,3,3,50.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(11,3,3,50.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(12,3,3,50.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(13,4,4,1.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(14,5,5,2.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(15,5,5,2.00);
INSERT INTO STOCK (    STOCK_ID,    FK_BUSINESS_ID,    FK_ITEM_ID,COGS_PRICE)    VALUES(16,5,5,2.00);

INSERT INTO SHIPMENT (    SHIPMENT_ID,TRACKING_NUMBER,WEIGHT_KG,TIMESTAMP_CREATED)    VALUES(1,'1Z123ADFG2323dd23d23g',0.5,to_date('6/1/2019','mm/dd/yyyy'));
INSERT INTO CUSTOMER_ORDER (    CUSTOMER_ORDER_ID,    FK_SHIPMENT_ID,    FK_CUSTOMER_ID,TIMESTAMP_CREATED)    VALUES(1,1,1,to_date('6/1/2019','mm/dd/yyyy')); 
INSERT INTO ADJUST_STOCK (    ADJUST_STOCK_ID,    FK_EMPLOYEE_ID,    FK_CUSTOMER_ORDER_ID,    FK_BUSINESS_ID,    ADJUSTED_STOCK_DESCRIPTION,TIMESTAMP_CREATED)    VALUES(1,1,1,1,'Order',to_date('6/1/2019','mm/dd/yyyy'));
INSERT INTO ADJUSTED_STOCK_ITEM (    ADJUSTED_STOCK_ITEM_ID,    FK_ADJUST_STOCK_ID,    FK_ITEM_ID,    MATH_OPERATION)    VALUES(1,1,1,2);

INSERT INTO CUSTOMER_ORDER (    CUSTOMER_ORDER_ID,    FK_SHIPMENT_ID,    FK_CUSTOMER_ID)    VALUES(2,NULL,2); 
INSERT INTO ADJUST_STOCK (    ADJUST_STOCK_ID,    FK_EMPLOYEE_ID,    FK_CUSTOMER_ORDER_ID,    FK_BUSINESS_ID,    ADJUSTED_STOCK_DESCRIPTION)    VALUES(2,5,2,5,'Sale');
INSERT INTO ADJUSTED_STOCK_ITEM (    ADJUSTED_STOCK_ITEM_ID,    FK_ADJUST_STOCK_ID,    FK_ITEM_ID,    MATH_OPERATION)    VALUES(2,2,5,2);
INSERT INTO ADJUSTED_STOCK_ITEM (    ADJUSTED_STOCK_ITEM_ID,    FK_ADJUST_STOCK_ID,    FK_ITEM_ID,    MATH_OPERATION)    VALUES(3,2,5,2);


 -- Speciality Items (at the moment these don't have much special attributes - but this is where we would put those)
INSERT INTO MONO_ITEM(ITEM_ID) VALUES(1);
INSERT INTO FOOD_ITEM(ITEM_ID) VALUES(2);
INSERT INTO BOOK_ITEM(ITEM_ID) VALUES(4);


 
--VALID VENDOR
INSERT INTO PURCHASE_ORDER (PURCHASE_ORDER_ID,FK_VENDOR_ID, FK_EMPLOYEE_ID, FK_BUSINESS_ID) VALUES ( 2,1,2,1);
--NO VENDOR
INSERT INTO PURCHASE_ORDER (PURCHASE_ORDER_ID, FK_EMPLOYEE_ID, FK_BUSINESS_ID) VALUES ( 3,2,1);



INSERT INTO ACCOMMODATION (ACCOMMODATION_ID,FK_BUSINESS_ID,ACCOMMODATION_PRICE,ACCOMMODATION_TITLE,ACCOMMODATION_DESCRIPTION) 
    VALUES(1,3,129.75,'Suite 1','A 3br 2bath forest view with Hot Tub on private balcony 2 king bed and 1 queen bed');

INSERT INTO ACCOMMODATION (ACCOMMODATION_ID,FK_BUSINESS_ID,ACCOMMODATION_PRICE,ACCOMMODATION_TITLE,ACCOMMODATION_DESCRIPTION) 
    VALUES(2,3,133.75,'Suite 2','A 4br 2bath forest view with Hot Tub on private balcony 3 king beds and 1 queen bed');

INSERT INTO ACCOMMODATION (ACCOMMODATION_ID,FK_BUSINESS_ID,ACCOMMODATION_PRICE,ACCOMMODATION_TITLE,ACCOMMODATION_DESCRIPTION) 
    VALUES(3,3,80.75,'Room 3','A 1br 1bath forest view with 1 queen bed');

INSERT INTO ACCOMMODATION (ACCOMMODATION_ID,FK_BUSINESS_ID,ACCOMMODATION_PRICE,ACCOMMODATION_TITLE,ACCOMMODATION_DESCRIPTION) 
    VALUES(4,3,80.75,'Room 4','A 1br 1bath forest view with 1 queen bed');

INSERT INTO ACCOMMODATION (ACCOMMODATION_ID,FK_BUSINESS_ID,ACCOMMODATION_PRICE,ACCOMMODATION_TITLE,ACCOMMODATION_DESCRIPTION) 
    VALUES(5,3,80.75,'Room 5','A 1br 1bath forest view with 1 queen bed');

INSERT INTO RESERVATION (RESERVATION_ID,FK_CUSTOMER_ID,FK_ACCOMMODATION_ID,CHECKIN_DATE,CHECKOUT_DATE,FK_EMPLOYEE_ID) 
    VALUES(1,1,1,to_date('6/1/2019','mm/dd/yyyy'),to_date('6/14/2019','mm/dd/yyyy'),3);
INSERT INTO RESERVATION (RESERVATION_ID,FK_CUSTOMER_ID,FK_ACCOMMODATION_ID,CHECKIN_DATE,CHECKOUT_DATE,FK_EMPLOYEE_ID) 
    VALUES(2,2,2,to_date('6/3/2019','mm/dd/yyyy'),to_date('6/4/2019','mm/dd/yyyy'),3);
INSERT INTO RESERVATION (RESERVATION_ID,FK_CUSTOMER_ID,FK_ACCOMMODATION_ID,CHECKIN_DATE,CHECKOUT_DATE,FK_EMPLOYEE_ID) 
    VALUES(3,3,3,to_date('6/3/2019','mm/dd/yyyy'),to_date('6/4/2019','mm/dd/yyyy'),3);


-- Part 1
--A customer makes purchases in two of the stores.  You need to track which of the stores the purchase was made in, as well as the product, the number of products sold
--(ie. 5 cinnamon candles), and the price of the product.  You will also need to update the inventory of the store to reflect the purchase.
-- THIS IS POST INVENTORY UPDATE -You can't past tense and  current tense at same time / paradox
-- ASSUMING you want   StoreName,ProductName, Individual Unit Price of each item

SELECT BUSINESS.BUSINESS_NAME,ITEM.ITEM_NAME,ITEM.ITEM_PRICE  FROM ADJUST_STOCK 
    JOIN ADJUSTED_STOCK_ITEM ON ADJUSTED_STOCK_ITEM.FK_ADJUST_STOCK_ID=ADJUST_STOCK.ADJUST_STOCK_ID
    JOIN BUSINESS ON ADJUST_STOCK.FK_BUSINESS_ID=BUSINESS.BUSINESS_ID
    JOIN ITEM ON ADJUSTED_STOCK_ITEM.FK_ITEM_ID = ITEM.ITEM_ID;



-- Part 2
--Display the total number of sales for a single store for the month of June 2019.  
--This query should include the store name, and the total sales.

SELECT BUSINESS.BUSINESS_NAME,TO_CHAR(SUM(ITEM.ITEM_PRICE),'$9,999.99') AS TOTAL_SALES FROM ADJUST_STOCK 
    JOIN ADJUSTED_STOCK_ITEM ON ADJUSTED_STOCK_ITEM.FK_ADJUST_STOCK_ID=ADJUST_STOCK.ADJUST_STOCK_ID
    JOIN BUSINESS ON BUSINESS.BUSINESS_ID=ADJUST_STOCK.FK_BUSINESS_ID
    JOIN ITEM ON ITEM.ITEM_ID=ADJUSTED_STOCK_ITEM.FK_ITEM_ID
    WHERE FK_CUSTOMER_ORDER_ID IN 
        (SELECT CUSTOMER_ORDER_ID FROM CUSTOMER_ORDER WHERE TIMESTAMP_CREATED BETWEEN to_date('6/1/2019','mm/dd/yyyy') AND to_date('6/30/2019','mm/dd/yyyy'))
    GROUP BY BUSINESS.BUSINESS_NAME;

---A new employee is hired for the Grange Restaurant.  You will need to track their hours and their pay rate per hour.  Calculate their salary for the week of June 1 through June 8.
--- part    of 3

SELECT EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,TO_CHAR(EMPLOYEE_HRLY_WAGE,'$9,999.99') AS HRLY_WAGE ,SUM(ROUND(((CLOCK_OUT-CLOCK_IN)*24))) AS HOURS_WORKED,  TO_CHAR(SUM(ROUND(((CLOCK_OUT-CLOCK_IN)*24)))*EMPLOYEE_HRLY_WAGE,'$9,999.99') AS SALARY
      FROM EMPLOYEE 
    JOIN SHIFT ON SHIFT.FK_EMPLOYEE_ID=EMPLOYEE.EMPLOYEE_ID  
    WHERE FK_BUSINESS_ID=(SELECT BUSINESS_ID FROM BUSINESS WHERE BUSINESS_NAME LIKE '%Grange%')
    GROUP BY EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,EMPLOYEE_HRLY_WAGE;
    

--4) A Visitor makes a reservation for June 1, 2019 to June 14, 2019 for one of the Guest Houses, and a monk must be assigned for each of those weeks.  
--Display the Visitor name, the Guest house, and the monk assigned for each week.
--1-7
--8-14
SELECT ACCOMMODATION.ACCOMMODATION_TITLE,RESERVATION.CHECKIN_DATE,RESERVATION.CHECKOUT_DATE,CUSTOMER.CUSTOMER_FIRST_NAME,CUSTOMER.CUSTOMER_LAST_NAME,EMPLOYEE.EMPLOYEE_FIRST_NAME,EMPLOYEE.EMPLOYEE_LAST_NAME FROM RESERVATION
    JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEE_ID=RESERVATION.FK_EMPLOYEE_ID
    JOIN CUSTOMER ON CUSTOMER.CUSTOMER_ID=RESERVATION.FK_CUSTOMER_ID
    JOIN ACCOMMODATION ON ACCOMMODATION.ACCOMMODATION_ID=RESERVATION.FK_ACCOMMODATION_ID
    WHERE RESERVATION.CHECKIN_DATE BETWEEN to_date('6/1/2019','mm/dd/yyyy') AND to_date('6/13/2019','mm/dd/yyyy');

--5) The Abbot wants to know how many distinct guests have reservations at the abbey from June 1 to August 31 in 2019.  Even if I have stayed at the abbey twice, 
--I should only be counted once.

SELECT COUNT(DISTINCT FK_CUSTOMER_ID) AS GUESTS FROM RESERVATION
    WHERE RESERVATION.CHECKIN_DATE BETWEEN to_date('6/1/2019','mm/dd/yyyy') AND to_date('8/31/2019','mm/dd/yyyy');

--6) The Abbott wants to know how many rooms do NOT have reservations at the abbey on Saturday, June 8, 2019.
SELECT count(*) AS AVAILABLE_ACCOMMODATIONS FROM ACCOMMODATION WHERE ACCOMMODATION_ID NOT IN (SELECT FK_ACCOMMODATION_ID FROM RESERVATION WHERE to_date('6/8/2019','mm/dd/yyyy')
    BETWEEN CHECKIN_DATE AND CHECKOUT_DATE);

--7) The Abbott wants to know all room numbers, as well as those customer names that are staying at the monastery on Friday, June 7, 2019.  
--Not all rooms will be filled….
SELECT ACCOMMODATION.ACCOMMODATION_TITLE,CUSTOMER_FIRST_NAME,CUSTOMER_LAST_NAME,RESERVATION.CHECKIN_DATE FROM ACCOMMODATION
    LEFT JOIN RESERVATION ON RESERVATION.FK_ACCOMMODATION_ID = ACCOMMODATION.ACCOMMODATION_ID 
        AND (to_date('6/7/2019','mm/dd/yyyy') BETWEEN RESERVATION.CHECKIN_DATE AND RESERVATION.CHECKOUT_DATE)
    LEFT JOIN CUSTOMER ON RESERVATION.FK_CUSTOMER_ID = CUSTOMER.CUSTOMER_ID;

--8) The Abbot would like to know if there are any visitors that have made repeat visits (MORE THAN 1) so they can direct market efforts toward them.  
    
    SELECT CUSTOMER.CUSTOMER_FIRST_NAME,CUSTOMER.CUSTOMER_LAST_NAME,NUMBER_OF_STAYS FROM
        (SELECT FK_CUSTOMER_ID,(COUNT(*)) AS NUMBER_OF_STAYS  FROM RESERVATION
        GROUP BY FK_CUSTOMER_ID)
        LEFT JOIN CUSTOMER ON CUSTOMER.CUSTOMER_ID=FK_CUSTOMER_ID
    WHERE NUMBER_OF_STAYS > 1;

--9) Each week inventory must be taken to determine if anything needs to be reordered.  
--Using a single SQL query, determine which products are in need of reordering.  
--This means you have to calculate not only what you have in stock, but what you have sold this week for a particular product.

SELECT ITEM_ID,ITEM_NAME,QTY_IN_STOCK,(ITEM_REORDER_STOCK-QTY_IN_STOCK) AS REORDER_AMOUNT FROM ITEM
   LEFT JOIN (SELECT FK_ITEM_ID,COUNT(*) AS QTY_IN_STOCK FROM STOCK
    GROUP BY FK_ITEM_ID) ON FK_ITEM_ID=ITEM.ITEM_ID
    WHERE QTY_IN_STOCK < ITEM_REORDER_STOCK;
    

--10) Write a stored procedure to take in a product ID and a delivery amount and price and insert it into the database.  
--This will require updating the inventory count.

    -- This is Magic because it did not get ordered (purchase order) and it was not received, but its appearing in inventory
    -- Price is not neccessary - because the price is set by the ITEM not by whatever this is.
    -- P.S. I don't know how to pass an array in stored procedure - 276?? :)
   
     
    CREATE OR REPLACE PROCEDURE magic_delivery(itemID IN NUMBER,qty IN NUMBER, cogsPrice IN DECIMAL, businessID IN NUMBER)
    IS
         is_item_id_valid NUMBER;
         is_business_id_valid NUMBER;
         is_price_valid NUMBER;
         altered_rows NUMBER;
         next_stock_id NUMBER;
         
    BEGIN
        DBMS_OUTPUT.PUT_LINE('STARTING PROCEDURE ITEM:' || itemID );
        -- VALIDATE ITEM_ID
            SELECT count(*) INTO is_item_id_valid FROM ITEM WHERE ITEM_ID=itemID;
            IF (is_item_id_valid = 0) THEN
                altered_rows :=0;
                DBMS_OUTPUT.PUT_LINE('Invalid Item: ' || itemID );
               RETURN;
            END IF;
        -- VALIDATE BUSINESS_ID
        SELECT count(*) INTO is_business_id_valid FROM BUSINESS WHERE BUSINESS_ID=businessID;
        IF (is_business_id_valid = 0) THEN
                altered_rows :=0;
                DBMS_OUTPUT.PUT_LINE('Invalid Business: ' || businessID );
               RETURN;
            END IF;
        -- VALIDATE QTY
        IF (qty <= 0) THEN
            DBMS_OUTPUT.PUT_LINE('Invalid Quantity: ' || qty );
            RETURN;
        END IF;
        -- VALIDATE PRICE
         IF (cogsPrice <= 0) THEN
            DBMS_OUTPUT.PUT_LINE('Invalid Price: ' || cogsPrice );
            RETURN;
        END IF;
        
        -- VALIDATION COMPLETE - START WORK
        FOR i IN REVERSE 1..QTY LOOP
            SELECT (MAX(STOCK_ID)+1) INTO next_stock_id FROM STOCK;
           -- DBMS_OUTPUT.PUT_LINE('DEBUG: ' ||' next_stock:'|| next_stock_id ||' business:'|| businessID ||' item:'|| itemID || ' cogsPrice:'||cogsPrice );
            INSERT INTO STOCK (STOCK_ID,FK_BUSINESS_ID, FK_ITEM_ID,COGS_PRICE) VALUES(next_stock_id,businessID,itemID,cogsPrice);
        END LOOP;
    COMMIT;
    END;
/    
    
EXECUTE magic_delivery(1,100,99.99,1);
-- NOW TO VERIFY
SELECT count(*) AS SHOULD_BE_100 FROM STOCK WHERE COGS_PRICE=99.99;