csn1

--  Rental Data
-- C. Provide SQL code in a text format that creates the detailed and summary tables to hold your report table sections.

CREATE TABLE all_rentals AS
SELECT
    r.rental_id,
    i.film_id,
    f.title,
    f.rental_rate
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON f.film_id = i.film_id
GROUP BY r.rental_id, f.title, f.rental_rate, i.film_id
ORDER by r.rental_id ASC;
SELECT * FROM all_rentals;

DROP TABLE all_rentals;

-- B.  Provide original code for function(s) in text format that perform the transformation(s) you identified in part A4.

--DETAILED TABLE

CREATE TABLE detailed_table AS
SELECT
    film_id,
    title,
    rental_rate::money,
    COUNT(film_id) AS times_rented,
    (COUNT(film_id) * rental_rate)::money AS revenue
FROM all_rentals ar
GROUP BY film_id, title, rental_rate
ORDER BY revenue DESC, title
LIMIT 25;
SELECT * FROM detailed_table;

DROP TABLE detailed_table;

--SUMMARY TABLE
CREATE TABLE summary_table AS
SELECT
    title,
    revenue
FROM detailed_table
ORDER BY REVENUE DESC, title
LIMIT 25;
SELECT * FROM summary_table;

DROP TABLE summary_table;

--D. Write code for functions that perform the transformations identified in A4.
--functions that update tables (to be triggered periodically)
CREATE OR REPLACE FUNCTION update_all_rentals()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM all_rentals;
    INSERT INTO all_rentals
    SELECT
        r.rental_id,
        i.film_id,
        f.title,
        f.rental_rate
    FROM rental r
    INNER JOIN inventory i ON r.inventory_id = i.inventory_id
    INNER JOIN film f ON f.film_id = i.film_id
    GROUP BY r.rental_id, f.title, f.rental_rate, i.film_id
    ORDER by r.rental_id ASC;
RETURN NEW;
END;
$$

CREATE OR REPLACE FUNCTION update_detailed()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM detailed_table;
    INSERT INTO detailed_table
    SELECT
        film_id,
        title,
        rental_rate::money,
        --transformed data fields vv
        COUNT(film_id) AS times_rented,
        (COUNT(film_id) * rental_rate)::money AS revenue
    FROM all_rentals ar
    GROUP BY film_id, title, rental_rate
    ORDER BY revenue DESC, title
    LIMIT 25;
    RETURN NEW;
END;
$$

DROP FUNCTION update_detailed();

--update_summary function
CREATE OR REPLACE FUNCTION update_summary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    --2. Update summary table
    DELETE FROM summary_table;
    INSERT INTO summary_table
    SELECT
        title,
        revenue
    FROM detailed_table
    ORDER BY REVENUE DESC, title
    LIMIT 25;
    RETURN NEW;
END;
$$

--E. Write a SQL code that creates a trigger on the detailed report that will continually update the summary table as data is added to the detailed table.
--trigger statements
CREATE TRIGGER update_all_rentals
    AFTER INSERT
    ON rental
    FOR EACH STATEMENT
    EXECUTE PROCEDURE update_all_rentals();

DROP TRIGGER update_all_rentals ON rental;

--update_detailed table trigger
CREATE TRIGGER update_detailed
    AFTER INSERT ON all_rentals
    FOR EACH STATEMENT
    EXECUTE PROCEDURE update_detailed();

DROP TRIGGER update_detailed ON all_rentals;

--update_summary table trigger
CREATE TRIGGER update_summary
    AFTER INSERT ON detailed_table
    FOR EACH STATEMENT
    EXECUTE PROCEDURE update_summary();

DROP TRIGGER update_summary ON detailed_table;

/*F.Create a stored procedure that can be used to refresh the data in both your detailed and summary tables. The procedure should clear
the contents of the detailed and summary tables
and explain how the stored procedure can be run on a schedule to ensure data freshness*/
--stored procedure that updates tables
CREATE OR REPLACE PROCEDURE update_all_rentals()
LANGUAGE plpgsql
as $$
BEGIN
    DELETE FROM all_rentals;
    INSERT INTO all_rentals
    SELECT
    r.rental_id,
    i.film_id,
    f.title,
    f.rental_rate
    FROM rental r
    INNER JOIN inventory i ON r.inventory_id = i.inventory_id
    INNER JOIN film f ON f.film_id = i.film_id
    GROUP BY r.rental_id, f.title, f.rental_rate, i.film_id
    ORDER by r.rental_id ASC;
    SELECT * FROM all_rentals;
    SELECT * FROM all_rentals ORDER BY rental_id DESC;
END;$$;

CREATE OR REPLACE PROCEDURE update_detailed()
LANGUAGE plpgsql
as $$
BEGIN
        DELETE FROM detailed_table;
        INSERT INTO detailed_table
        SELECT
            film_id,
            title,
            rental_rate::money,
            COUNT(film_id) AS times_rented,
            (COUNT(film_id) * rental_rate)::money AS revenue
        FROM all_rentals ar
        GROUP BY film_id, title, rental_rate
        ORDER BY revenue DESC, title
        LIMIT 25;
END;$$;

CALL update_detailed();

CREATE OR REPLACE PROCEDURE update_summary()
LANGUAGE plpgsql
as $$
BEGIN
        DELETE FROM summary_table;
        INSERT INTO summary_table
        SELECT
            title,
            revenue
FROM detailed_table
ORDER BY REVENUE DESC, title
LIMIT 25;
END;
$$

CALL update_summary();

--test queries
SELECT COUNT(*) FROM rental; --16044
SELECT COUNT(*) FROM all_rentals;
SELECT * FROM rental ORDER BY rental_id DESC;
SELECT * FROM all_rentals ORDER BY rental_id DESC;

INSERT INTO rental VALUES (99997, '2023-12-16 12:33:15', 900, 100, '2023-11-16 14:28:16', 1, '2022-11-16 14:28:16')
DELETE FROM rental WHERE rental_id >16049;

INSERT INTO all_rentals VALUES (99998, 999, 'testeroni', 99.99), (99999, 999, 'testeroni', 99.99);
DELETE FROM all_rentals WHERE rental_id > 16049;

SELECT * FROM detailed_table;
SELECT * FROM summary_table;

CALL update_detailed();
CALL update_summary();