ddl.sql

create schema cinema;

create table cinema.genres(
	id serial primary key,
	genre varchar(255) not null unique
);

create table cinema.country(
	id serial primary key,
	name varchar(255) not null unique
);

create sequence if not exists next_movie_id;  -- sequence used to identify either movie or tv show
	
create table cinema.movies(
	id int default nextval('next_movie_id') primary key,
	title varchar(255) not null unique,
	year_of_release int not null,
	length_in_minutes int not null,
	country_id int references cinema.country(id) not null,
	imdb_rating numeric (2, 1)  default  0.0,
	budget int not null,
	actors json
);

create table cinema.tv_shows(
	id int default nextval('next_movie_id') primary key,
	title varchar(255) not null unique,
	year_of_release int not null,
	length_in_minutes int not null,
	country_id int references cinema.country(id) not null,
	imdb_rating numeric (2, 1)  default  0.0,
	budget int not null
);
	
create table cinema.genre_movie_show(
	id serial primary key,
	genre_id int references cinema.genres(id) not null,
	movie_show_id int not null
);

create or replace function cinema.return_movie_show_id(name varchar(255))   -- this function receives movie's(or tv_show's id) name and returns its current id
returns int 
as $$
declare
	inserted_id int;
begin
	inserted_id = (select id
				   from cinema.movies
				   where title = name
				   union
				   select id
				   from cinema.tv_shows
				   where title = name 
				   );
	return inserted_id;
end; 
$$
language plpgsql;

create or replace function cinema.insert_movie(movie varchar(255), year_ int, length_ int, country_id int,  --this function inserts movie and returns its id
									  rating numeric(2,1), budget int, actors json) 
returns int 
as $$
declare
	a_id int;
begin
	insert into cinema.movies(title, year_of_release, length_in_minutes, country_id, imdb_rating, budget, actors)
	values(movie, year_, length_, country_id, rating, budget, actors)
	returning id into a_id;
	return a_id;
end; 
$$
language plpgsql;

create table cinema.log_table(    -- in this table trigger will save changes made with table cinema.movies
	id serial primary key,
	log_date timestamp default current_timestamp(0),
	message varchar(255) not null
);

create or replace function cinema.update_log_table()  -- this function checks which changes were made with table cinema.movies and inserts info about them in cinema.log_table
returns trigger as
$$
declare 
	message varchar(255);
begin
	if TG_OP = 'UPDATE' then
		message = 'Movie "' || new.title || '" was updated!';
	elsif TG_OP = 'INSERT' then
		message = 'New movie "' || new.title || '" was added!';
	elsif TG_OP = 'DELETE' then
		message = 'Movie "' || old.title || '" was deleted!';
	else
		message = 'Something weird has happened!';
	end if;

	insert into cinema.log_table(message)
	values(message);
	
	return new;
end; 
$$
language plpgsql;

create trigger new_movie_update    -- trigger reacts on changes in table cinema.movies
after update or insert or delete
on cinema.movies
for each row
	execute procedure cinema.update_log_table();


create extension if not exists "uuid-ossp";  -- extension is needed to use autogenerated function


create table cinema.user_activity(
	id uuid primary key,
	favourite_movie_id int not null,
	created_date timestamp default current_timestamp(0)
);

create or replace function cinema.return_random_movie_id() -- this function returns random movie id in range (min and max of existing ids in table cinema.movies)
returns int 
as $$
declare
	a int;
	b int;
begin
	a = (select min(m.id) from cinema.movies m);
	b = (select max(m.id) from cinema.movies m);
	return (select floor(random() * b + a)::int);
end;
$$ 
language plpgsql strict;

create or replace function cinema.insert_random_record() -- this function inserts random uuid and id in table cinema.user_activity
returns void 
as $$
declare
	id uuid;
	m_id int;
begin
	id = (select uuid_generate_v4());
	m_id = (select cinema.return_random_movie_id());
	insert into cinema.user_activity(id, favourite_movie_id)
	values(id, m_id);
end; 
$$
language plpgsql;

create table cinema.archive_user_activity(
	user_id uuid not null,
	favourite_movie_id int not null,
	created_date timestamp
);

create or replace function cinema.loop_insert_random_record(n int) -- this functions loops cinema.insert_random_record() for n times
	returns setof record 
	as $$
declare
	i record;
begin
	for i in 1..n loop
    perform cinema.insert_random_record();
	end loop;
	return;
end; 
$$
language plpgsql;

create view cinema.movie_and_genre as     -- this view selects columns from joined tables
select m.id, 
	title, 
	year_of_release, 
	c.name as country, 
	g.genre 
from cinema.movies m
inner join cinema.country c on m.country_id = c.id
inner join cinema.genre_movie_show gms on gms.movie_show_id= m.id
inner join cinema.genres g on gms.genre_id = g.id;


create materialized view cinema.country_movie
as
select c.name as country, count(m.id) as movie_amount
from cinema.country c
inner join cinema.movies m on m.country_id = c.id
group by country
order by movie_amount desc
with data;

create unique index country_movie_index on cinema.country_movie (country); -- to be able to refresh materialized view concurrently you have to creat index on it