Database 4031

CREATE OR REPLACE FUNCTION UpdateLastRentalDate()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE customer
    SET last_rental_date = NEW.rental_date
    WHERE customer_id = NEW.customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER UpdateCustomerLastRentalDate
AFTER INSERT ON rental
FOR EACH ROW
EXECUTE FUNCTION UpdateLastRentalDate();

CREATE OR REPLACE FUNCTION CheckInventoryAvailability()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM rental
        WHERE inventory_id = NEW.inventory_id
        AND return_date IS NULL
    ) THEN
        RAISE EXCEPTION 'This inventory item is already rented out and has not been returned.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER PreventDoubleRental
BEFORE INSERT ON rental
FOR EACH ROW
EXECUTE FUNCTION CheckInventoryAvailability();

-- First, create the trigger function
CREATE OR REPLACE FUNCTION CheckFilmReleaseYear()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the release year is less than 1900
    IF NEW.release_year < 1900 THEN
        -- Raise an exception to prevent insertion
        RAISE EXCEPTION 'Release year cannot be less than 1900.';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Now, create the trigger to call the function before inserting a new film
CREATE TRIGGER ValidateReleaseYear
BEFORE INSERT ON film
FOR EACH ROW
EXECUTE FUNCTION CheckFilmReleaseYear();

CREATE OR REPLACE PROCEDURE InsertNewFilm(
    IN film_title VARCHAR,
    IN release_year INT,
    IN language_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    lang_id INT;
BEGIN
    -- Find the language ID based on the language name
    SELECT language_id INTO lang_id
    FROM language
    WHERE name = language_name;

    -- If the language does not exist, raise an exception
    IF lang_id IS NULL THEN
        RAISE EXCEPTION 'Language % not found in the language table.', language_name;
    END IF;

    -- Insert the new film with the given title, release year, and language ID
    INSERT INTO film (title, release_year, language_id)
    VALUES (film_title, release_year, lang_id);

    RAISE NOTICE 'Film % has been successfully added.', film_title;
END;
$$;

CALL InsertNewFilm('Inception', 2010, 'English');

CREATE OR REPLACE PROCEDURE UpdateFilmRentalRate(
    IN film_ids INT,
    IN new_rental_rate DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE film
    SET rental_rate = $2
    WHERE film_id = $1;

    -- Optional: You could add checks here to confirm if the film exists and raise exceptions if not
END;
$$;
CALL UpdateFilmRentalRate(10, 4);
select * from film where film_id=10

create or replace procedure films_of_each_customer(in customerID INT)
language plpgsql
as $$
declare
    filmRecord RECORD;
begin
    for filmRecord in
        select
            film.film_id,
            title,
            rental_duration,
            rental_rate,
            inventory_id,
            film.last_update
        from film
        join inventory using(film_id)
      join store using(store_id)
    join customer using(store_id)
        where
            customer_id = customerID
    loop
        raise notice 'FilmID: %, Title: %, Rental duration: %, Rental rate: %, InventoryID: %, Last update: %',
            filmRecord.film_id,
            filmRecord.title,
            filmRecord.rental_duration,
            filmRecord.rental_rate,
            filmRecord.inventory_id,
            filmRecord.last_update;
    end loop;
end;
$$;
call films_of_each_customer(2)

ALTER TABLE rental ADD COLUMN late_fee DECIMAL DEFAULT 0;
CREATE OR REPLACE PROCEDURE ApplyFlatLateFee(
    IN flat_fee DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    rows_updated INT;
BEGIN
    -- Update late fees for overdue rentals with a single flat fee
    UPDATE rental AS r
    SET late_fee = flat_fee
    FROM film AS f, inventory AS i
    WHERE r.inventory_id = i.inventory_id
    AND i.film_id = f.film_id
    AND r.return_date IS NULL
    AND CURRENT_DATE > (r.rental_date + INTERVAL '1 day' * f.rental_duration);

    -- Get the number of rows affected by the update
    GET DIAGNOSTICS rows_updated = ROW_COUNT;

    -- Display a message indicating how many rentals had a flat late fee applied
    RAISE NOTICE '% rentals were updated with a flat late fee.', rows_updated;
END;
$$;
CALL ApplyFlatLateFee(5.00);

create or replace function get_actor_by_filmID(input_filmID integer)
returns table  (
  first_name varchar(45),
  last_name varchar(45)
)
as $$
begin
    return query
    select
  actor.first_name,
  actor.last_name
    from
        actor join film_actor using(actor_id)
    where
        film_actor.film_id = input_filmID ;
end;
$$ language plpgsql;

select get_actor_by_filmID(133)

create or replace function get_films_of_country(input_contry_of_film  varchar)
returns table  (
  film_id int,
  title varchar
)
as $$
begin
    return query
    select 
  distinct film.film_id,film.title
    from
        film join inventory using(film_id)
    join store using(store_id)
    join address using(address_id)
    join city using(city_id)
    join country using(country_id)
    where
        country = input_contry_of_film
  order by film_id asc;  
end;
$$ language plpgsql;

select *from  get_films_of_country('Canada')

CREATE OR REPLACE FUNCTION FilmAvailability(film_id INT, store_id INT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
        -- Check if there are available (unrented) copies in the store
        (SELECT COUNT(*) 
         FROM inventory i
         LEFT JOIN rental r ON i.inventory_id = r.inventory_id AND r.return_date IS NULL
         WHERE i.film_id = $1
         AND i.store_id = $2
         AND r.rental_id IS NULL) > 0
    );
END;
$$ LANGUAGE plpgsql;

SELECT FilmAvailability(10, 1);

CREATE OR REPLACE FUNCTION CustomerBalance(customer_id INT)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (
        -- Total rental fees minus total payments
        (SELECT COALESCE(SUM(f.rental_rate), 0)
         FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film f ON i.film_id = f.film_id
         WHERE r.customer_id = $1)
        -
        (SELECT COALESCE(SUM(amount), 0)
         FROM payment
         WHERE payment.customer_id = $1)
    );
END;
$$ LANGUAGE plpgsql;

SELECT CustomerBalance(12);

CREATE MATERIALIZED VIEW StoreInventorySummary AS
SELECT
    s.store_id,
   
    COUNT(i.inventory_id) AS total_inventory,
    COUNT(r.rental_id) AS total_rented
FROM
    store AS s
LEFT JOIN inventory AS i ON s.store_id = i.store_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    s.store_id;
  
select * from StoreInventorySummary

CREATE MATERIALIZED VIEW MostExpensiveMoviePerCustomer AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    f.title AS most_expensive_movie,
    f.rental_rate AS movie_price
FROM
    customer AS c
JOIN rental AS r ON c.customer_id = r.customer_id
JOIN inventory AS i ON r.inventory_id = i.inventory_id
JOIN film AS f ON i.film_id = f.film_id
WHERE f.rental_rate = (
    SELECT MAX(f2.rental_rate)
    FROM rental AS r2
    JOIN inventory AS i2 ON r2.inventory_id = i2.inventory_id
    JOIN film AS f2 ON i2.film_id = f2.film_id
    WHERE r2.customer_id = c.customer_id
);

select * from MostExpensiveMoviePerCustomer

CREATE VIEW FilmPopularitySummary AS
SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(r.rental_id) AS total_rentals
FROM
    film AS f
LEFT JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    f.film_id, f.title, f.rating;

select * from FilmPopularitySummary order by total_rentals desc limit 20

CREATE VIEW CustomerRentalSummary AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(distinct r.rental_id) AS total_rentals,
    COALESCE(SUM(p.amount), 0) AS total_payments
FROM
    customer AS c
LEFT JOIN rental AS r ON c.customer_id = r.customer_id
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name;

select * from CustomerRentalSummary order by total_payments desc limit 20

CREATE VIEW FilmAvailabilityPopularity AS
SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(DISTINCT i.inventory_id) AS total_copies,
    COUNT(DISTINCT r.rental_id) AS total_rentals,
    COUNT(DISTINCT CASE WHEN r.return_date IS NULL THEN i.inventory_id END) AS copies_rented_out,
    COUNT(DISTINCT CASE WHEN r.return_date IS NOT NULL THEN i.inventory_id END) AS copies_available
FROM
    film AS f
JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    f.film_id, f.title, f.rating;

select * from FilmAvailabilityPopularity