CREATE TABLE PRICE (
PRICE_CODE INTEGER NOT NULL UNIQUE,
PRICE_DESCRIPTION CHAR(15) NOT NULL,
PRICE_RENTFEE VARCHAR(3) NOT NULL,
PRICE_DAILYLATEFEE VARCHAR(3) NOT NULL,
PRIMARY KEY (PRICE_CODE));

CREATE TABLE MOVIE (
MOVIE_NUM INTEGER NOT NULL UNIQUE,
MOVIE_TITLE VARCHAR(35) NOT NULL,
MOVIE_YEAR INTEGER NOT NULL,
MOVIE_COST NUMBER NOT NULL,
MOVIE_GENRE CHAR(6) NOT NULL,
PRICE_CODE INTEGER,
PRIMARY KEY (MOVIE_NUM),
FOREIGN KEY (PRICE_CODE) references PRICE(PRICE_CODE)
);

CREATE TABLE VIDEO (
VID_NUM INTEGER NOT NULL UNIQUE,
VID_INDATE DATE NOT NULL,
MOVIE_NUM INTEGER NOT NULL,
PRIMARY KEY (VID_NUM),
FOREIGN KEY (MOVIE_NUM) references MOVIE(MOVIE_NUM)
);

CREATE TABLE DETAILRENTAL (
RENT_NUM INTEGER NOT NULL,
VID_NUM INTEGER NOT NULL,
DETAIL_FEE NUMBER NOT NULL,
DETAIL_DUEDATE DATE NOT NULL,
DETAIL_RETURNDATE DATE,
DETAIL_DAILYLATEFEE NUMBER NOT NULL,
PRIMARY KEY (RENT_NUM, VID_NUM),
FOREIGN KEY (RENT_NUM) references RENTAL(RENT_NUM),
FOREIGN KEY (VID_NUM) references VIDEO(VID_NUM),
CONSTRAINT VID_UI1 UNIQUE (RENT_NUM, VID_NUM)
);

CREATE TABLE RENTAL (
RENT_NUM INTEGER NOT NULL UNIQUE,
RENT_DATE DATE NOT NULL,
MEM_NUM INTEGER NOT NULL,
PRIMARY KEY (RENT_NUM),
FOREIGN KEY (MEM_NUM) references MEMBERSHIP(MEM_NUM)
);

CREATE TABLE MEMBERSHIP (
MEM_NUM INTEGER NOT NULL UNIQUE,
MEM_FNAME VARCHAR(15) NOT NULL,
MEM_LNAME VARCHAR(15) NOT NULL,
MEM_STREET VARCHAR(35) NOT NULL,
MEM_CITY CHAR(15) NOT NULL,
MEM_STATE CHAR(2) NOT NULL,
MEM_ZIP VARCHAR(5) NOT NULL,
MEM_BALANCE NUMBER,
PRIMARY KEY (MEM_NUM)
);







INSERT INTO PRICE VALUES('1', 'Standard', '2', '1');
INSERT INTO PRICE VALUES('2', 'New Release', '3.5', '3');
INSERT INTO PRICE VALUES('3', 'Discount', '1.5', '1');
INSERT INTO PRICE VALUES('4', 'Weekly Special', '1', '.5');

INSERT INTO MOVIE VALUES('1234', 'The Ceasar Family Christmas', '2016', '39.95', 'FAMILY', '2');
INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', '2013', '59.95', 'ACTION', '1');
INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', '2017', '59.95', 'DRAMA', '2');
INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', '2016', '29.95', 'COMEDY', '2');
INSERT INTO MOVIE VALUES('1238', 'Constant Companion', '2017', '89.95', 'DRAMA', ' ');
INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', '2007', '25.49', 'DRAMA', '3');
INSERT INTO MOVIE VALUES('1245', 'Time to Burn', '2014', '45.49', 'ACTION', '1');
INSERT INTO MOVIE VALUES('1246', 'What He Doesn`t Know', '2015', '58.29', 'COMEDY', '1');

INSERT INTO VIDEO VALUES('54321', '18-JUN-17', '1234');
INSERT INTO VIDEO VALUES('54324', '18-JUN-17', '1234');
INSERT INTO VIDEO VALUES('54325', '18-JUN-17', '1234');
INSERT INTO VIDEO VALUES('34341', '22-JAN-16', '1235');
INSERT INTO VIDEO VALUES('34342', '22-JAN-16', '1235');
INSERT INTO VIDEO VALUES('34366', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('34367', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('34368', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('34369', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('44392', '21-OCT-17', '1237');
INSERT INTO VIDEO VALUES('44397', '21-OCT-17', '1237');
INSERT INTO VIDEO VALUES('59237', '14-FEB-18', '1237');
INSERT INTO VIDEO VALUES('61388', '25-JAN-16', '1239');
INSERT INTO VIDEO VALUES('61353', '28-JAN-15', '1245');
INSERT INTO VIDEO VALUES('61354', '28-JAN-15', '1245');
INSERT INTO VIDEO VALUES('61367', '30-JUL-17', '1246');
INSERT INTO VIDEO VALUES('61369', '30-JUL-17', '1246');

INSERT INTO DETAILRENTAL VALUES('1001', '34342', '2', '04-MAR-18', 'O2-MAR-18', ' ');
INSERT INTO DETAILRENTAL VALUES('1001', '61353', '2', '04-MAR-18', 'O3-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1002', '59237', '3.5', '04-MAR-18', 'O4-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1003', '54325', '3.5', '04-MAR-18', 'O9-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1003', '61369', '2', '06-MAR-18', 'O9-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1003', '61388', '0', '06-MAR-18', 'O9-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1004', '44392', '3.5', '05-MAR-18', 'O7-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1004', '34367', '3.5', '05-MAR-18', 'O7-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1004', '34341', '2', '07-MAR-18', 'O7-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1005', '34342', '2', '07-MAR-18', 'O5-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1005', '44397', '3.5', '05-MAR-18', 'O5-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1006', '34366', '3.5', '05-MAR-18', 'O4-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1006', '61367', '2', '07-MAR-18', ' ', '1');
INSERT INTO DETAILRENTAL VALUES('1007', '34368', '3.5', '05-MAR-18', ' ', '3');
INSERT INTO DETAILRENTAL VALUES('1008', '34369', '3.5', '05-MAR-18', 'O5-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1009', '54324', '3.5', '05-MAR-18', ' ', '3');
INSERT INTO DETAILRENTAL VALUES('1001', '34366', '3.5', '04-MAR-18', 'O2-MAR-18', '3');

INSERT INTO RENTAL VALUES('1001', '01-MAR-18', '103');
INSERT INTO RENTAL VALUES('1002', '01-MAR-18', '105');
INSERT INTO RENTAL VALUES('1003', '02-MAR-18', '102');
INSERT INTO RENTAL VALUES('1004', '02-MAR-18', '110');
INSERT INTO RENTAL VALUES('1005', '02-MAR-18', '111');
INSERT INTO RENTAL VALUES('1006', '02-MAR-18', '107');
INSERT INTO RENTAL VALUES('1007', '02-MAR-18', '104');
INSERT INTO RENTAL VALUES('1008', '03-MAR-18', '105');
INSERT INTO RENTAL VALUES('1009', '03-MAR-18', '111');

INSERT INTO MEMBERSHIP VALUES('102', 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', '37136', '11');
INSERT INTO MEMBERSHIP VALUES('103', 'Curt', 'Knight', '4025 Cornell Court', 'Flatgap', 'KY', '41219', '6');
INSERT INTO MEMBERSHIP VALUES('104', 'Jamal', 'Melendez', '788 East 145th Avenue', 'Quebeck', 'TN', '38579', '0');
INSERT INTO MEMBERSHIP VALUES('105', 'Iva', 'Mcclain', '6045 Musket Ball Circle', 'Summit', 'KY', '42783', '15');
INSERT INTO MEMBERSHIP VALUES('106', 'Miranda', 'Parks', '4469 Maxwell Place', 'Germantown', 'TN', '38183', '0');
INSERT INTO MEMBERSHIP VALUES('107', 'Rosario', 'Elliot', '7578 Danner Avenue', 'Columbia', 'TN', '38402', '5');
INSERT INTO MEMBERSHIP VALUES('108', 'Mattie', 'Guy', '4390 Evergreen Street', 'Lily', 'KY', '40740', '0');
INSERT INTO MEMBERSHIP VALUES('109', 'Clint', 'Ochoa', '1711 Elm Street', 'Greeneville', 'TN', '37745', '10');
INSERT INTO MEMBERSHIP VALUES('110', 'Lewis', 'Rosales', '4524 Southwind Circle', 'Counce', 'TN', '38326', '0');
INSERT INTO MEMBERSHIP VALUES('111', 'Stacy', 'Mann', '2789 East Cook Avenue', 'Murfreesboro', 'TN', '37132', '8');
INSERT INTO MEMBERSHIP VALUES('112', 'Luis', 'Trujillo', '7267 Melvin Avenue', 'Heiskell', 'TN', '37754', '3');
INSERT INTO MEMBERSHIP VALUES('113', 'Minnie', 'Gonzales', '6430 Vasili Drive', 'Williston', 'TN', '38076', '0');

-- Using the tables that were created in Case 1 and the data that was loaded into those tables in Case 2, create a trigger named trg_late_return that will write the correct value to DETAIL_ DAYSLATE in the DETAILRENTAL table whenever a video is returned. The trigger should execute as a BEFORE trigger when the DETAIL_RETURNDATE or DETAIL_DUEDATE attributes are updated. The trigger should satisfy the following conditions:
--      If the return date is null, then the days late should also be null.
--      If the return date is not null, then the days late should determine if the video is returned late.
--      If the return date is noon of the day after the due date or earlier, then the video is not considered late, and the days late should have a value of zero (0).
--      If the return date is past noon of the day after the due date, then the video is considered late, so the number of days late must be calculated and stored.

CREATE OR REPLACE TRIGGER TRG_LATE_RETURN
BEFORE INSERT OR UPDATE OF DETAIL_RETURNDATE, DETAIL_DUEDATE ON DETAILRENTAL
FOR EACH ROW
BEGIN 
    IF :NEW.DETAIL_RETURNDATE IS NULL THEN
       :NEW.DETAIL_DAYSLATE IS NULL;
    IF :NEW.DETAIL_RETURNDATE <= :NEW.DETAIL_DUEDATE OR :NEW.DETAIL_RETURNDATE = :NEW.DETAIL_DUEDATE + 1 AND TO_CHAR(:NEW.DETAIL_RETURNDATE, 'HH12:MI') <= '12:00pm' THEN :NEW.DETAIL_DAYSLATE = 0;
    ELSE 
        :NEW.DETAIL_DAYSLATE = (:NEW.DETAIL_RETURNDATE - :NEW.DETAIL_DUEDATE);
    END IF;
END;