rem  +---------------------------------------------------------------------+
rem  |  MovieProductionCompany.SQL                                         |
rem  |                                                                     |
rem  |  Create tables for the Movie Production Company Database            |
rem  |                                                                     |
rem  |                                                                     |
rem  +---------------------------------------------------------------------+
rem

rem | Drop table before creating and inserting data


DROP TABLE MovieScriptInventory CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create MovieScriptInventory Table                                    |
rem + ---------------------------------------------------------------------+

CREATE TABLE MovieScriptInventory (
    script_inventory_id INT PRIMARY KEY,
    script_inventory_name VARCHAR(255) NOT NULL
);

INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (1, 'The Shawshank Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (2, 'The Avengers Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (3, 'The Dark Knight Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (4, 'Fiction Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (5, 'The Lord of the Rings Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (6, 'Forrest Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (7, 'The Silence Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (8, ' Matrix Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (9, 'Ryan Inventory');
INSERT INTO MovieScriptInventory (script_inventory_id, script_inventory_name) 
VALUES (10, 'Inception Inventory');

DROP TABLE Movie CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create Movie Table                                                 |
rem  +---------------------------------------------------------------------+

CREATE TABLE Movie (
    movie_id INT NOT NULL,
    title VARCHAR(255) NOT NULL UNIQUE,
    avg_rating INT NOT NULL,
    date_of_release DATE NOT NULL,
    duration INT NOT NULL,
    script_inventory_id INT NOT NULL,
    director VARCHAR(255),
    in_production CHAR(1),
    PRIMARY KEY (movie_id),
    FOREIGN KEY (script_inventory_id) REFERENCES MovieScriptInventory(script_inventory_id) ON DELETE CASCADE,
    CONSTRAINT avg_rating_constraint_violated CHECK (
        avg_rating > 0
        and avg_rating < 11
    ),
    CONSTRAINT duration_constraint_violated CHECK (duration > 0)
);

INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (1, 'The Shawshank Redemption', 9, TO_DATE('1994-09-14', 'yyyy-mm-dd'), 142, 1, 'Frank Darabont', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (2, 'The Godfather', 9, TO_DATE('1972-03-24', 'yyyy-mm-dd'), 175, 2, 'Francis Ford Coppola', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (3, 'The Dark Knight', 9, TO_DATE('2008-07-14', 'yyyy-mm-dd'), 152, 3, 'Christopher Nolan', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (4, 'Pulp Fiction', 8, TO_DATE('1994-09-10', 'yyyy-mm-dd'), 154, 4, 'Quentin Tarantino', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (5, 'The Lord of the Rings: The Return of the King', 9, TO_DATE('2003-12-17', 'yyyy-mm-dd'), 201, 5, 'Peter Jackson', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (6, 'Forrest Gump', 8, TO_DATE('1994-06-23', 'yyyy-mm-dd'), 142, 6, 'Robert Zemeckis', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (7, 'The Matrix', 8, TO_DATE('1999-03-31', 'yyyy-mm-dd'), 136, 7, 'Lana Wachowski', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (8, 'The Silence of the Lambs', 8, TO_DATE('1991-02-01', 'yyyy-mm-dd'), 118, 8, 'Jonathan Demme', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (9, 'Star Wars: Episode IV - A New Hope', 8, TO_DATE('2021-07-25', 'yyyy-mm-dd'), 121, 9, 'George Lucas', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (10, 'Inception', 8, TO_DATE('2010-07-08', 'yyyy-mm-dd'), 148, 10, 'Christopher Nolan', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (11, 'The Lion King', 7, TO_DATE('2022-07-25', 'yyyy-mm-dd'), 88, 1, 'George Lucas', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (12, 'Jurassic Park', 7, TO_DATE('1993-06-09', 'yyyy-mm-dd'), 127, 2, 'Steven Spielberg', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (13, 'Titanic', 8, TO_DATE('1997-12-19', 'yyyy-mm-dd'), 194, 3, 'James Cameron', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (14, 'Avatar', 7, TO_DATE('2009-12-18', 'yyyy-mm-dd'), 162, 4, 'James Cameron', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (15, 'The Avengers', 7, TO_DATE('2012-04-25', 'yyyy-mm-dd'), 143, 5, 'Joss Whedon', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (16, 'The Social Network', 8, TO_DATE('2010-09-24', 'yyyy-mm-dd'), 120, 6, 'David Fincher', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (17, 'The Departed', 8, TO_DATE('2006-10-06', 'yyyy-mm-dd'), 151, 7, 'Martin Scorsese', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (18, 'The Godfather: Part II', 9, TO_DATE('1974-12-20', 'yyyy-mm-dd'), 202, 8, 'Francis Ford Coppola', 'N');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (19, 'The Shawshank Redemption II', 9, TO_DATE('1996-09-20', 'yyyy-mm-dd'), 142, 9, 'Frank Darabont', 'Y');
INSERT INTO Movie (movie_id, title, avg_rating, date_of_release, duration, script_inventory_id, director, in_production)
VALUES (20, 'The Lord of the Rings: The Fellowship of the Ring', 8, TO_DATE('2001-12-19', 'yyyy-mm-dd'), 178, 10, 'Peter Jackson', 'Y');


DROP TABLE Rating CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create Rating Table                                                |
rem  +---------------------------------------------------------------------+

CREATE TABLE Rating (
    rating_id INT PRIMARY KEY,
    movie_id INT,
    rating NUMBER,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id),
    CONSTRAINT rating_constraint_violated CHECK (
            rating > 0
            and rating < 11
    )
);

INSERT INTO Rating (rating_id, movie_id, rating) VALUES (1, 1, 4);
INSERT INTO Rating (rating_id, movie_id, rating) VALUES (2, 2, 3);
INSERT INTO Rating (rating_id, movie_id, rating) VALUES (3, 3, 5);
INSERT INTO Rating (rating_id, movie_id, rating) VALUES (4, 4, 2);
INSERT INTO Rating (rating_id, movie_id, rating) VALUES (5, 5, 4);
INSERT INTO Rating (rating_id, movie_id, rating) VALUES (6, 1, 4);


CREATE TABLE Songs (
    song_id INT NOT NULL,
    song_name VARCHAR(255) NOT NULL,
    singer_name VARCHAR(255) NOT NULL,
    movie_id INT NOT NULL,
    PRIMARY KEY (song_id),
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE
);


DROP TABLE Songs CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create Songs Table                                                 |
rem  +---------------------------------------------------------------------+

CREATE TABLE Songs (
    song_id INT NOT NULL,
    song_name VARCHAR(255) NOT NULL,
    singer_name VARCHAR(255) NOT NULL,
    movie_id INT NOT NULL,
    PRIMARY KEY (song_id),
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE
);

INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (1, 'Shape of You', 'Ed Sheeran', 1);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (2, 'Billie Jean', 'Michael Jackson', 2);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (3, 'Bohemian Rhapsody', 'Queen', 3);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (4, 'Stairway to Heaven', 'Led Zeppelin', 4);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (5, 'Sweet Child O Mine', 'Guns N Roses', 5);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (6, 'Smells Like Teen Spirit', 'Nirvana', 6);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (7, 'Hotel California', 'Eagles', 7);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (8, 'Dont Stop Believin', 'Journey', 8);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (9, 'Thriller', 'Michael Jackson', 9);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (10, 'Wonderwall', 'Oasis', 10);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (11, 'Uptown Funk', 'Mark Ronson ft. Bruno Mars', 1);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (12, 'Smooth', 'Santana ft. Rob Thomas', 2);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (13, 'Livin on a Prayer', 'Bon Jovi', 3);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (14, 'Hello', 'Adele', 4);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (15, 'I Will Always Love You', 'Whitney Houston', 5);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (16, 'Every Breath You Take', 'The Police', 6);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (17, 'Eye of the Tiger', 'Survivor', 7);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (18, 'Nothing Else Matters', 'Metallica', 8);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (19, 'Let It Be', 'The Beatles', 9);
INSERT INTO Songs (song_id, song_name, singer_name, movie_id)
VALUES (20, 'What a Wonderful World', 'Louis Armstrong', 10);


DROP TABLE Genre CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create Genre Table                                                 |
rem  +---------------------------------------------------------------------+

CREATE TABLE Genre (
    genre_id INT NOT NULL,
    genre_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (genre_id)
);

INSERT INTO Genre (genre_id, genre_name) VALUES (1, 'Action');
INSERT INTO Genre (genre_id, genre_name) VALUES (2, 'Comedy');
INSERT INTO Genre (genre_id, genre_name) VALUES (3, 'Drama');
INSERT INTO Genre (genre_id, genre_name) VALUES (4, 'Romance');
INSERT INTO Genre (genre_id, genre_name) VALUES (5, 'Thriller');
INSERT INTO Genre (genre_id, genre_name) VALUES (6, 'Adventure');
INSERT INTO Genre (genre_id, genre_name) VALUES (7, 'Science Fiction');
INSERT INTO Genre (genre_id, genre_name) VALUES (8, 'Fantasy');
INSERT INTO Genre (genre_id, genre_name) VALUES (9, 'Musical');
INSERT INTO Genre (genre_id, genre_name) VALUES (10, 'Animation');


DROP TABLE TaggedWith CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create TaggedWith Table                                            |
rem  +---------------------------------------------------------------------+

CREATE TABLE TaggedWith (
    movie_id INT NOT NULL,
    genre_id INT NOT NULL,
    PRIMARY KEY (movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES Genre(genre_id) ON DELETE CASCADE
);

INSERT INTO TaggedWith (movie_id, genre_id) VALUES (1, 4);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (1, 3);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (20, 4);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (2, 3);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (3, 4);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (3, 3);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (12, 4);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (5, 4);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (5, 3);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (13, 9);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (6, 1);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (6, 5);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (11, 1);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (7, 5);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (8, 6);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (8, 7);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (13, 8);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (9, 6);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (9, 7);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (14, 8);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (10, 1);
INSERT INTO TaggedWith (movie_id, genre_id) VALUES (10, 2);


DROP TABLE SiteLocation CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create SiteLocation Table                                          |
rem  +---------------------------------------------------------------------+

CREATE TABLE SiteLocation (
    location_id INT NOT NULL,
    location_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    PRIMARY KEY (location_id)
);

INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (1, 'Central Park', '123 Main St, New York, NY 10019');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (2, 'Lincoln Memorial', '2 Lincoln Memorial Cir NW, Washington, DC 20037');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (3, 'Golden Gate Bridge', 'Golden Gate Bridge, San Francisco, CA 94129');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (4, 'The Bean', 'Cloud Gate, Chicago, IL 60601');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (5, 'Niagara Falls', 'Niagara Falls State Park, Niagara Falls, NY 14303');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (6, 'Mount Rushmore', '13000 SD-244, Keystone, SD 57751');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (7, 'The Alamo', '300 Alamo Plaza, San Antonio, TX 78205');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (8, 'Grand Canyon', 'Grand Canyon Village, AZ 86023');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (9, 'Niagara Falls', 'Niagara Falls, ON L2G 3Y9, Canada');
INSERT INTO SiteLocation (location_id, location_name, address)
VALUES (10, 'Redwood National and State Parks', '1111 2nd St, Crescent City, CA 95531');

DROP TABLE Building CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create Building Table                                              |
rem  +---------------------------------------------------------------------+

CREATE TABLE Building (
    building_id INT NOT NULL,
    building_name VARCHAR(255) NOT NULL,
    purpose VARCHAR(20) NOT NULL,
    location_id INT NOT NULL,
    PRIMARY KEY (building_id),
    FOREIGN KEY (location_id) REFERENCES SiteLocation(location_id) ON DELETE CASCADE,
    CONSTRAINT purpose_constraint_violated CHECK (
        purpose IN (
            'production',
            'post-production',
            'office',
            'studio',
            'storage',
            'visual effects',
            'color grading',
            'sound design',
            'mixing'
        )
    )
);

INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (1, 'MGM Studios', 'studio', 1);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (2, 'Sony Pictures', 'studio', 2);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (3, 'Pinewood Studios', 'studio', 3);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (4, 'Warner Bros. Studios', 'studio', 4);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (5, 'Fox Studios', 'studio', 5);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (6, 'Universal Studios', 'studio', 6);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (7, 'Paramount Studios', 'studio', 7);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (8, 'ABC Studios', 'studio', 8);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (9, 'Columbia Pictures', 'studio', 9);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (10, 'Marvel Studios', 'studio', 10);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (11, 'DreamWorks Animation', 'visual effects', 1);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (12, 'Walt Disney Animation Studios', 'visual effects', 2);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (13, 'Industrial Light and Magic', 'visual effects', 3);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (14, 'Rhythm and Hues Studios', 'visual effects', 4);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (15, 'Pixar Animation Studios', 'visual effects', 5);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (16, 'MPC Film', 'visual effects', 6);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (17, 'Double Negative', 'visual effects', 7);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (18, 'Sony Pictures Imageworks', 'visual effects', 8);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (19, 'Framestore', 'visual effects', 9);
INSERT INTO Building (building_id, building_name, purpose, location_id)
VALUES (20, 'Technicolor SA', 'color grading', 10);


DROP TABLE ShotAt CASCADE CONSTRAINTS;
rem  +---------------------------------------------------------------------+
rem  |  Create ShotAt Table                                                |
rem  +---------------------------------------------------------------------+

CREATE TABLE ShotAt (
    location_id INT NOT NULL,
    movie_id INT NOT NULL,
    PRIMARY KEY (location_id, movie_id),
    FOREIGN KEY (location_id) REFERENCES SiteLocation(location_id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE
);

INSERT INTO ShotAt (location_id, movie_id) VALUES (1, 1);
INSERT INTO ShotAt (location_id, movie_id) VALUES (2, 1);
INSERT INTO ShotAt (location_id, movie_id) VALUES (2, 2);
INSERT INTO ShotAt (location_id, movie_id) VALUES (3, 2);
INSERT INTO ShotAt (location_id, movie_id) VALUES (4, 3);
INSERT INTO ShotAt (location_id, movie_id) VALUES (4, 4);
INSERT INTO ShotAt (location_id, movie_id) VALUES (5, 5);
INSERT INTO ShotAt (location_id, movie_id) VALUES (6, 6);
INSERT INTO ShotAt (location_id, movie_id) VALUES (7, 7);
INSERT INTO ShotAt (location_id, movie_id) VALUES (7, 8);
INSERT INTO ShotAt (location_id, movie_id) VALUES (7, 9);
INSERT INTO ShotAt (location_id, movie_id) VALUES (8, 10);
INSERT INTO ShotAt (location_id, movie_id) VALUES (9, 11);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 12);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 13);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 14);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 15);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 16);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 17);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 18);
INSERT INTO ShotAt (location_id, movie_id) VALUES (10, 19);


DROP TABLE PostProductionDoneIn CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create PostProductionDoneIn Table                                    |
rem + ---------------------------------------------------------------------+

CREATE TABLE PostProductionDoneIn (
    movie_id INT NOT NULL,
    building_id INT NOT NULL,
    PRIMARY KEY (movie_id, building_id),
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE,
    FOREIGN KEY (building_id) REFERENCES Building(building_id) ON DELETE CASCADE
);

INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (1, 1);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (2, 2);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (3, 3);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (4, 4);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (5, 5);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (6, 6);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (7, 7);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (8, 8);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (9, 9);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (10, 10);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (11, 11);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (12, 12);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (13, 13);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (14, 14);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (15, 15);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (16, 16);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (17, 17);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (18, 18);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (19, 19);
INSERT INTO PostProductionDoneIn (movie_id, building_id) VALUES (20, 20);


DROP TABLE Employees CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create Employees Table                                               |
rem + ---------------------------------------------------------------------+

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    employee_name VARCHAR(255) NOT NULL,
    designation VARCHAR(255) NOT NULL,
    employee_level VARCHAR(255) DEFAULT 'Production Assistant',
    phone_number VARCHAR(10) NOT NULL UNIQUE,
    hourly_pay FLOAT DEFAULT 0,
    PRIMARY KEY (employee_id),
    CONSTRAINT designatoin_constraint_violated CHECK (
        designation IN (
            'choreographer',
            'security',
            'sound engineer',
            'makeup artist',
            'electrician',
            'janitor',
            'manager'
        )
    ),
    CONSTRAINT hourly_pay_constraint_violated CHECK (hourly_pay >= 0)
);

INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (101, 'John Doe', 'electrician', '1234567890', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (102, 'Jane Smith', 'makeup artist', '2345678901', 60.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (103, 'Bob Johnson', 'janitor', '3456789012', 45.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (104, 'Sara Lee', 'sound engineer', '4567890123', 25.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (105, 'Mike Tyson', 'security', '5678901234', 30.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (106, 'Lisa Brown', 'choreographer', '6789012345', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (107, 'David Kim', 'manager', '7890123456', 40.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (108, 'Jessica Lee', 'electrician', '8901234567', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (109, 'Jason Lee', 'makeup artist', '9012345678', 60.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (110, 'Emily Johnson', 'janitor', '0123456789', 60.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (111, 'Alex Lee', 'sound engineer', '1234509876', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (112, 'Alice Kim', 'security', '2345609876', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (113, 'Brian Kim', 'choreographer', '3456709876', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (114, 'Cathy Lee', 'manager', '4567809876', 60.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (115, 'Daniel Lee', 'electrician', '5678909876', 60.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (116, 'Ellen Kim', 'makeup artist', '6789019876', 50.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (117, 'Frank Lee', 'janitor', '7890129876', 40.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (118, 'Grace Kim', 'sound engineer', '8901239876', 40.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (119, 'Henry Lee', 'security', '9012349876', 60.00);
INSERT INTO Employees (employee_id, employee_name, designation, phone_number, hourly_pay)
VALUES (120, 'Ivy Kim', 'choreographer', '0123450987', 60.00);


DROP TABLE Payroll CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create Payroll Table                                                 |
rem + ---------------------------------------------------------------------+

CREATE TABLE Payroll (
    payroll_id INT NOT NULL,
    employee_id INT NOT NULL,
    hours_worked FLOAT NOT NULL,
    work_date DATE NOT NULL,
    PRIMARY KEY (payroll_id),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE,
    CONSTRAINT hours_constraint_violated CHECK (hours_worked > 0 and hours_worked <= 12)
);


INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (1, 101, 8.00, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (2, 102, 7.50, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (3, 103, 6.00, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (4, 104, 9.50, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (5, 105, 4.00, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (6, 106, 8.00, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (7, 107, 7.50, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (8, 108, 6.00, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (9, 109, 9.50, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (10, 110, 4.00, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (11, 111, 8.00, TO_DATE('2022-03-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (12, 112, 7.50, TO_DATE('2022-03-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (13, 101, 8.00, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (14, 102, 7.50, TO_DATE('2022-03-01', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (15, 103, 8.00, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (16, 104, 7.50, TO_DATE('2022-03-02', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (17, 105, 8.00, TO_DATE('2022-03-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (18, 106, 7.50, TO_DATE('2022-03-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (19, 107, 8.00, TO_DATE('2022-03-04', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (20, 108, 7.50, TO_DATE('2022-03-04', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (21, 109, 8.00, TO_DATE('2022-03-05', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (22, 112, 7.50, TO_DATE('2022-03-06', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (23, 113, 8.00, TO_DATE('2022-03-07', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (24, 114, 7.50, TO_DATE('2022-03-07', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (25, 115, 8.00, TO_DATE('2022-03-08', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (26, 116, 8.00, TO_DATE('2022-02-12', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (27, 117, 6.50, TO_DATE('2022-02-13', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (28, 118, 7.00, TO_DATE('2022-02-14', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (29, 119, 9.50, TO_DATE('2022-02-15', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date)VALUES (30, 120, 6.00, TO_DATE('2022-02-16', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (31, 110, 7.50, TO_DATE('2022-03-05', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (32, 111, 8.00, TO_DATE('2022-03-06', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (33, 111, 8.00, TO_DATE('2022-11-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (34, 110, 10.00, TO_DATE('2022-11-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (35, 114, 6.00, TO_DATE('2022-11-03', 'yyyy-mm-dd'));
INSERT INTO Payroll (payroll_id, employee_id, hours_worked, work_date) VALUES (36, 114, 4.00, TO_DATE('2022-03-08', 'yyyy-mm-dd'));

DROP TABLE SponsoringCompany CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create SponsoringCompany Table                                       |
rem + ---------------------------------------------------------------------+

CREATE TABLE SponsoringCompany (
    company_id INT NOT NULL,
    company_name VARCHAR(255) NOT NULL UNIQUE,
    num_movies_produced INT DEFAULT 0,
    PRIMARY KEY (company_id)
);

INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (1, 'Universal Pictures');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (2, 'Warner Bros. Pictures');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (3, 'Walt Disney Pictures');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (4, 'Paramount Pictures');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (5, '20th Century Fox');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (6, 'Sony Pictures');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (7, 'Lionsgate');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (8, 'DreamWorks Pictures');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (9, 'Miramax');
INSERT INTO SponsoringCompany (company_id, company_name)
VALUES (10, 'New Line Cinema');

DROP TABLE Manages CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create Manages Table                                                 |
rem + ---------------------------------------------------------------------+

CREATE TABLE Manages (
    employee_id INT NOT NULL,
    location_id INT NOT NULL,
    PRIMARY KEY (employee_id, location_id),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES SiteLocation(location_id)
);

INSERT INTO Manages (employee_id, location_id) VALUES (101, 1);
INSERT INTO Manages (employee_id, location_id) VALUES (102, 2);
INSERT INTO Manages (employee_id, location_id) VALUES (103, 3);
INSERT INTO Manages (employee_id, location_id) VALUES (104, 4);
INSERT INTO Manages (employee_id, location_id) VALUES (105, 5);
INSERT INTO Manages (employee_id, location_id) VALUES (106, 6);
INSERT INTO Manages (employee_id, location_id) VALUES (107, 7);
INSERT INTO Manages (employee_id, location_id) VALUES (108, 8);
INSERT INTO Manages (employee_id, location_id) VALUES (109, 9);
INSERT INTO Manages (employee_id, location_id) VALUES (110, 10);
INSERT INTO Manages (employee_id, location_id) VALUES (101, 2);
INSERT INTO Manages (employee_id, location_id) VALUES (102, 3);
INSERT INTO Manages (employee_id, location_id) VALUES (103, 4);
INSERT INTO Manages (employee_id, location_id) VALUES (104, 5);
INSERT INTO Manages (employee_id, location_id) VALUES (105, 6);
INSERT INTO Manages (employee_id, location_id) VALUES (106, 7);
INSERT INTO Manages (employee_id, location_id) VALUES (107, 8);
INSERT INTO Manages (employee_id, location_id) VALUES (108, 9);
INSERT INTO Manages (employee_id, location_id) VALUES (109, 10);
INSERT INTO Manages (employee_id, location_id) VALUES (110, 1);


DROP TABLE Artist CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create Artist Table                                                  |
rem + ---------------------------------------------------------------------+
CREATE TABLE Artist (
  artist_id INT PRIMARY KEY,
  artist_name VARCHAR(255) NOT NULL,	
  date_of_birth DATE NOT NULL,
  gender VARCHAR(1) NOT NULL,
  address VARCHAR(255),
  CONSTRAINT gender_constraint_violated CHECK (gender in ('M', 'F', 'T'))
);

INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (1, 'Tom Hanks', to_date('07-09-1956', 'MM-DD-YYYY'), 'M', 'Los Angeles, California');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (2, 'Meryl Streep', to_date('06-22-1949', 'MM-DD-YYYY'), 'F', 'Denton, Texas');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (3, 'Denzel Washington', to_date('12-28-1954', 'MM-DD-YYYY'), 'M', 'Denton, Texas');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (4, 'Scarlett Johansson', to_date('11-22-1984', 'MM-DD-YYYY'), 'F', 'Denton, Texas');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (5, 'Robert De Niro', to_date('08-17-1943', 'MM-DD-YYYY'), 'M', 'Denton, Texas');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (6, 'Natalie Portman', to_date('06-09-1981', 'MM-DD-YYYY'), 'F', 'Jerusalem, Israel');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (7, 'Leonardo DiCaprio', to_date('11-11-1974', 'MM-DD-YYYY'), 'M', 'Los Angeles, California');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (8, 'Saoirse Ronan', to_date('04-12-1994', 'MM-DD-YYYY'), 'F', 'Denton, Texas');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (9, 'Brad Pitt', to_date('12-18-1963', 'MM-DD-YYYY'), 'M', 'Shawnee, Oklahoma');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (10, 'Emma Stone', to_date('11-06-1988', 'MM-DD-YYYY'), 'F', 'Scottsdale, Arizona');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (11, 'George Clooney', to_date('05-06-1961', 'MM-DD-YYYY'), 'M', 'Lexington, Kentucky');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (12, 'Anne Hathaway', to_date('11-12-1982', 'MM-DD-YYYY'), 'F', 'Denton, Texas');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (13, 'Johnny Depp', to_date('06-09-1963', 'MM-DD-YYYY'), 'M', 'Owensboro, Kentucky');
INSERT INTO Artist (artist_id, artist_name, date_of_birth, gender, address) VALUES (14, 'Keira Knightley', to_date('03-26-1985', 'MM-DD-YYYY'), 'F', 'London, England');




DROP TABLE getsPaidBy CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create getsPaidBy Table                                              |
rem + ---------------------------------------------------------------------+

CREATE TABLE getsPaidBy (
    artist_id INT NOT NULL,
    company_id INT NOT NULL,
    PRIMARY KEY (artist_id, company_id),
    FOREIGN KEY (artist_id) REFERENCES Artist(artist_id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES SponsoringCompany(company_id) ON DELETE CASCADE
);

INSERT INTO getsPaidBy (artist_id, company_id) VALUES (1, 4);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (1, 5);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (2, 3);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (2, 4);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (2, 5);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (3, 1);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (3, 2);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (3, 3);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (3, 4);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (4, 1);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (4, 2);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (4, 3);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (4, 4);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (5, 1);
INSERT INTO getsPaidBy (artist_id, company_id) VALUES (5, 2);


DROP TABLE Produces CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create Produces Table                                                |
rem + ---------------------------------------------------------------------+

CREATE TABLE Produces (
    company_id INT NOT NULL,
    movie_id INT NOT NULL,
    PRIMARY KEY (company_id, movie_id),
    FOREIGN KEY (company_id) REFERENCES SponsoringCompany(company_id) ON DELETE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE
);

INSERT INTO Produces (company_id, movie_id) VALUES (1, 1);
INSERT INTO Produces (company_id, movie_id) VALUES (2, 3);
INSERT INTO Produces (company_id, movie_id) VALUES (3, 6);
INSERT INTO Produces (company_id, movie_id) VALUES (4, 9);
INSERT INTO Produces (company_id, movie_id) VALUES (5, 12);
INSERT INTO Produces (company_id, movie_id) VALUES (6, 14);
INSERT INTO Produces (company_id, movie_id) VALUES (7, 16);
INSERT INTO Produces (company_id, movie_id) VALUES (8, 18);
INSERT INTO Produces (company_id, movie_id) VALUES (9, 19);
INSERT INTO Produces (company_id, movie_id) VALUES (10, 20);
INSERT INTO Produces (company_id, movie_id) VALUES (2, 1);
INSERT INTO Produces (company_id, movie_id) VALUES (3, 2);
INSERT INTO Produces (company_id, movie_id) VALUES (4, 3);
INSERT INTO Produces (company_id, movie_id) VALUES (5, 4);
INSERT INTO Produces (company_id, movie_id) VALUES (6, 5);
INSERT INTO Produces (company_id, movie_id) VALUES (7, 6);
INSERT INTO Produces (company_id, movie_id) VALUES (8, 7);
INSERT INTO Produces (company_id, movie_id) VALUES (9, 8);
INSERT INTO Produces (company_id, movie_id) VALUES (10, 9);
INSERT INTO Produces (company_id, movie_id) VALUES (1, 10);


DROP TABLE ActsIn CASCADE CONSTRAINTS;
rem + ---------------------------------------------------------------------+
rem | Create ActsIn Table                                                  |
rem + ---------------------------------------------------------------------+

CREATE TABLE ActsIn (
    movie_id INT NOT NULL,
    artist_id INT NOT NULL,
    PRIMARY KEY (movie_id, artist_id),
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE,
    FOREIGN KEY (artist_id) REFERENCES Artist(artist_id) ON DELETE CASCADE
);

INSERT INTO ActsIn (movie_id, artist_id) VALUES (1, 1);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (2, 2);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (3, 3);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (4, 4);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (5, 5);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (6, 6);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (7, 7);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (8, 8);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (9, 9);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (10, 10);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (12, 14);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (7, 14);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (19, 13);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (2, 12);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (15, 13);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (4, 10);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (8, 11);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (18, 12);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (13, 12);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (5, 12);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (16, 2);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (16, 3);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (16, 4);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (16, 5);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (16, 8);
INSERT INTO ActsIn (movie_id, artist_id) VALUES (16, 12);


-- FUNCTION 1

drop function get_total_payroll_amount;
CREATE OR REPLACE FUNCTION get_total_payroll_amount(
    eid IN EMPLOYEES.employee_id%type,
    start_date IN PAYROLL.work_date%type,
    end_date IN PAYROLL.work_date%type
)
RETURN NUMBER
IS
total_pay NUMBER;
BEGIN
    SELECT SUM(p.hours_worked*e.hourly_pay) INTO total_pay
    FROM Payroll p, Employees e
    WHERE p.employee_id = eid
    AND p.employee_id = e.employee_id
    AND p.work_date BETWEEN start_date AND end_date ;
    dbms_output.put_line(total_pay);
    RETURN total_pay;
END;
/
VAR TOTAL NUMBER;
EXECUTE :TOTAL:=get_total_payroll_amount(109, '01-MAR-22', '03-NOV-22');
print TOTAL;


-- FUNCTION 2
DROP FUNCTION GET_TOTAL_NUMBER_OF_SONGS;
CREATE OR REPLACE FUNCTION get_total_number_of_songs(
    mid IN MOVIE.movie_id%type
)
RETURN NUMBER
IS
SONGS_COUNT NUMBER;
BEGIN    
    SELECT COUNT(*) INTO SONGS_COUNT
    FROM Songs s
    WHERE s.movie_id = mid;
    
    RETURN SONGS_COUNT;
END;
/
VAR song_count NUMBER;
EXECUTE :song_count:=get_total_number_of_songs(1);
print song_count;

-- FUNCTION 3
DROP FUNCTION GET_MOVIE_SHOT_LOCATIONS;
CREATE FUNCTION GET_MOVIE_SHOT_LOCATIONS(
    MID IN MOVIE.MOVIE_ID%TYPE
) 
RETURN VARCHAR2
IS
LOCATIONS VARCHAR2(1000);
BEGIN
    SELECT
        LISTAGG(LOCATION_NAME,', ') INTO LOCATIONS
    FROM
        SITELOCATION
        INNER JOIN SHOTAT
        ON SITELOCATION.LOCATION_ID = SHOTAT.LOCATION_ID
    WHERE
        SHOTAT.MOVIE_ID = MID;
    RETURN LOCATIONS;
END;
/
VAR location VARCHAR2(1000);
EXECUTE :location:=GET_MOVIE_SHOT_LOCATIONS(1);
print location;


-- FUNCTION 4
DROP FUNCTION get_movies_produced;

CREATE FUNCTION GET_MOVIES_PRODUCED(
    CID INT
) RETURN VARCHAR2 IS
    MOVIES_PRODUCED VARCHAR2(1000);
BEGIN
    SELECT
        LISTAGG(M.TITLE,
        ', ') INTO MOVIES_PRODUCED
    FROM
        PRODUCES P,
        MOVIE    M
    WHERE
        COMPANY_ID = CID
        AND P.MOVIE_ID = M.MOVIE_ID;
    RETURN MOVIES_PRODUCED;
END;
/
VAR movies VARCHAR2(1000);
EXECUTE :movies:=GET_MOVIES_PRODUCED(2);
print movies;


-- FUNCTION 5
DROP FUNCTION songs_in_movies_with_genre;
-- Return songs that are present in movies with genre 'gn'
CREATE FUNCTION songs_in_movies_with_genre(
    gn Genre.genre_name%type
)
RETURN VARCHAR2 IS
songs_list VARCHAR2(2000);
BEGIN
    SELECT
        LISTAGG('->'||s.song_name||' from movie '||m.title, chr(10)) INTO songs_list
    FROM
        MOVIE m,
        Genre g,
        Songs s,
        TaggedWith t
    WHERE
        g.genre_name = gn
        AND g.genre_id = t.genre_id
        AND t.movie_id = m.movie_id
        AND m.movie_id = s.movie_id;
    RETURN songs_list;
END;
/
VAR songs_list VARCHAR2(1000);
EXECUTE :songs_list:=songs_in_movies_with_genre('Romance');
print songs_list;


-- PROCEDURES

-- Procedure 1
Create or replace procedure get_artist_by_movie( 
    v_movie_id in Movie.movie_id%type
)
    Is 
    artist_names varchar2(2000);
    Begin
    Select listagg(a.artist_name,',') into artist_names
    From Artist a, ActsIn ai
    Where  ai.movie_id = v_movie_id and ai.artist_id = a.artist_id;
    dbms_output.put_line('Artists acted in movie with id '|| v_movie_id|| ': ' || artist_names);
END;
/

-- Procedure 2
Create or replace procedure get_building_by_location( 
    v_location_id in SiteLocation.location_id%type
)
is
    v_building_name varchar2(255);
    Begin
    Select listagg(building_name,',') into v_building_name
    from Building b, SiteLocation s1
    Where s1.location_id = v_location_id and b.location_id = s1.location_id;
    dbms_output.put_line('Building name' || v_building_name);
END;
/

-- Procedure 3
create or replace procedure get_genre_name(
    v_genre_id in Genre.genre_id%type
)
Is
    v_genre_name Genre.genre_name%type;
    Begin
    select genre_name into v_genre_name 
    from Genre 
    where v_genre_id = genre_id;
    dbms_output.put_line('Genre Name :' || v_genre_name);
END;
/

-- Procedure 4
create or replace procedure get_song_name(
    v_song_id in Songs.song_id%type
)
    Is
    v_song_name songs.song_name%type;
    BEGIN
    select song_name into v_song_name 
    from Songs 
    where song_id = v_song_id;
    dbms_output.put_line('Song Name :' || v_song_name);
END;
/

-- Procedure 5
Create or replace procedure get_payroll_by_employee( 
v_employee_id in Employees.employee_id%type
)
Is 
v_hours_worked number;
Begin
Select sum(hourly_worked) into v_hours_worked
From Payroll p
Where  p.employee_id = v_employee_id;
dbms_output.put_line(' Employees hours worked with' || v_employee_id|| ': ' || v_hours_worked || 'hrs.');
END;
/


-- TRIGGERS

-- Trigger 1
CREATE OR REPLACE TRIGGER update_movie_avg_rating
AFTER INSERT ON Rating
DECLARE average_rating NUMBER;
BEGIN
    SELECT avg(rating) INTO average_rating
    FROM Rating R
    WHERE R.movie_id = movie_id;
    UPDATE Movie m SET m.avg_rating = average_rating WHERE m.movie_id = movie_id;
END;
/

select * from movie where movie_id = 1;

INSERT INTO RATING VALUES (25, 1, 10);
INSERT INTO RATING VALUES (26, 1, 10);
INSERT INTO RATING VALUES (27, 1, 10);
select * from movie where movie_id = 1;



-- Trigger 2

SELECT EMPLOYEE_LEVEL, HOURLY_PAY FROM EMPLOYEES WHERE EMPLOYEE_ID = 101;


CREATE OR REPLACE TRIGGER update_employee_hourly_pay
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
IF :NEW.employee_level = 'Manager' THEN
:NEW.hourly_pay := 50;
ELSIF :NEW.employee_level = 'Assistant Manager' THEN
:NEW.hourly_pay := 30;
ELSIF :NEW.employee_level = 'Production Assistant' THEN
:NEW.hourly_pay := 15;
END IF;
END;
/

UPDATE Employees SET employee_level = 'Assistant Manager' WHERE employee_id = 101;

SELECT EMPLOYEE_LEVEL, HOURLY_PAY FROM EMPLOYEES WHERE EMPLOYEE_ID = 101;


-- Trigger 3
SELECT * FROM SponsoringCompany WHERE COMPANY_ID = 1;

CREATE OR REPLACE TRIGGER update_movie_count
BEFORE INSERT ON Produces
FOR EACH ROW
BEGIN
    UPDATE SponsoringCompany SET num_movies_produced = (SELECT COUNT(*)+1 FROM PRODUCES WHERE company_id = :NEW.company_id) WHERE company_id = :NEW.company_id;
END;
/

INSERT INTO Produces (company_id, movie_id) VALUES (1, 12);

SELECT * FROM SponsoringCompany WHERE COMPANY_ID = 1;

-- PACKAGE

CREATE OR REPLACE PACKAGE MOVIE_DB AS
    FUNCTION SONGS_IN_MOVIES_WITH_GENRE(
        GN GENRE.GENRE_NAME%TYPE
    ) RETURN VARCHAR2;
    PROCEDURE GET_BUILDING_BY_LOCATION(
        V_LOCATION_ID IN SITELOCATION.LOCATION_ID%TYPE
    );
END;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY MOVIE_DB AS
    FUNCTION SONGS_IN_MOVIES_WITH_GENRE(
        GN GENRE.GENRE_NAME%TYPE
    ) RETURN VARCHAR2 IS
        SONGS_LIST VARCHAR2(2000);
    BEGIN
        SELECT
            LISTAGG('->'||S.SONG_NAME||' from movie '||M.TITLE,
            CHR(10)) INTO SONGS_LIST
        FROM
            MOVIE      M,
            GENRE      G,
            SONGS      S,
            TAGGEDWITH T
        WHERE
            G.GENRE_NAME = GN
            AND G.GENRE_ID = T.GENRE_ID
            AND T.MOVIE_ID = M.MOVIE_ID
            AND M.MOVIE_ID = S.MOVIE_ID;
        RETURN SONGS_LIST;
    END;
    PROCEDURE GET_BUILDING_BY_LOCATION(
        V_LOCATION_ID IN SITELOCATION.LOCATION_ID%TYPE
    ) IS
        V_BUILDING_NAME VARCHAR2(255);
    BEGIN
        SELECT
            LISTAGG(BUILDING_NAME,
            ',') INTO V_BUILDING_NAME
        FROM
            BUILDING     B,
            SITELOCATION S1
        WHERE
            S1.LOCATION_ID = V_LOCATION_ID
            AND B.LOCATION_ID = S1.LOCATION_ID;
        DBMS_OUTPUT.PUT_LINE('Building name'
            || V_BUILDING_NAME);
    END;
END;
/
SHOW ERRORS;

