SQL sqript

                Never    
SQL
       
create schema movies

create table movies.people (
  first_name varchar(255)   not null,
  last_name varchar(255)   not null,
  birth_date date,
  country varchar(255),
  city varchar(255),


  constraint pk_people primary key(first_name, last_name),
  constraint correct_birth_date check (birth_date > '1900-01-01' and birth_date < now()::date)
 );

create table movies.users (
  username varchar(255)   not null,
  email varchar(255)	not null,
  reg_date date   not null default now()::date,
  country varchar(255),
  first_name varchar(255),
  last_name varchar(255),

  constraint pk_users primary key (username),
  constraint correct_email check (email like '__%@_%._%')
);

create table movies.movie (
  id serial  not null ,
  name varchar(255)   not null,
  p_year integer,
  country varchar(255),
  genre varchar(255),
  budget integer,

  constraint pk_movie primary key(id),
  constraint correct_year check (p_year > 1800),
  constraint positive_budget check ( budget > 0 )
);

create table movies.showtimes (
  id serial  not null,
  movie_id integer   not null,
  date date,
  time time,
  country varchar(255),
  city varchar(255),
  theater varchar(255),
  income integer,

  constraint pk_showtimes primary key(id),
  constraint fk_movie foreign key(movie_id) references movies.movie(id)
);

create index showtimes_fk_movies on movies.showtimes (movie_id);

create table movies.users_has_showtimes (
  users_username varchar(255) not null,
  showtimes_id integer not null,

  constraint pk_uhs primary key(users_username, showtimes_id),
  constraint fk_users foreign key(users_username) references movies.users(username),
  constraint fk_showtimes foreign key(showtimes_id) references movies.showtimes(id)
);


create index users_has_showtimes_fk_users on movies.users_has_showtimes (users_username);
create index users_has_showtimes_fk_showtimes on movies.users_has_showtimes (showtimes_id);

create table movies.grades (
  id serial  not null,
  movie_id integer   not null,
  users_username varchar(255)   not null,
  grade integer,
  recommend bool,
  review text,

  constraint pk_grades primary key(id),
  constraint fk_movie foreign key(movie_id) references movies.movie(id),
  constraint fk_users foreign key(users_username) references movies.users(username),
  constraint correct_grade check (grade > 0 and grade <= 10)
);

create index grades_fk_index_movie on movies.grades (movie_id);
create index grades_fk_index_users on movies.grades (users_username);

create table movies.movie_has_people (
  people_last_name varchar(255)   not null,
  people_first_name varchar(255)   not null,
  movie_id integer   not null,
  job varchar(255)   not null,
  salary integer   not null,
  constraint positive_salary check (salary > 0),

  constraint pk_movie_has_people primary key(people_last_name, people_first_name, job, movie_id),
  constraint fk_movie foreign key(movie_id) references movies.movie(id),
  constraint fk_people foreign key(people_first_name, people_last_name)
    references movies.people(first_name, last_name)
);


create index movie_has_people_fk_movies on movies.movie_has_people (movie_id);
create index movie_has_people_fk_people on movies.movie_has_people (people_first_name, people_last_name);








insert into movies.people values (
      'Андрей', 'Тарковский', '04-04-1932', 'СССР', 'Завражье'
);
insert into movies.people values (
      'Квентин', 'Тарантино', '27-03-1963', 'США', 'Ноксвилл'
);
insert into movies.people values (
      'Леонардо', 'ДиКаприо', '11-11-1974', 'США', 'Лос-Анджелес'
);
insert into movies.people values (
      'Том', 'Хэнкс', '09-07-1956', 'США', 'Конкорд'
);
insert into movies.people values (
      'Юрий', 'Быков', '15-08-1981', 'СССР', 'Новомичуринск'
);
insert into movies.people values (
      'Майкл', 'Бей', '17-03-1965', 'США', 'Лос-Анджелес'
);
insert into movies.people values (
      'Райан', 'Гостлинг', '12-11-1980', 'Канада', 'Лондон'
);
insert into movies.people values (
      'Никита', 'Михалков', '21-10-1945', 'СССР', 'Москва'
);
insert into movies.people values (
      'Гай', 'Ричи', '10-09-1968', 'Великобритания', 'Хатфилд'
);
insert into movies.people values (
      'Кристофер', 'Нолан', '30-07-1970', 'Великобритания', 'Лондон'
);








insert into movies.users (username, email) values (
      'YYY_NaGiBaToR_YYY', '[email protected]'
);
insert into movies.users (username, email) values (
      'Qunantiah', '[email protected]'
);
insert into movies.users (username, email) values (
      'Gumanari', '[email protected]'
);
insert into movies.users (username, email) values (
      'Geveve', '[email protected]'
);
insert into movies.users (username, email) values (
      'AnImE_PsIh', '[email protected]'
);
insert into movies.users (username, email, reg_date, first_name) values (
      'MishMish', '[email protected]', '21-04-2008', 'Миша'
);
insert into movies.users (username, email, reg_date, first_name, last_name, country) values (
      'EVGENII_NIKOVLAEVICH_PONASENKOV', '[email protected]', '11-01-2005', 'Евгений', 'Понасенков', 'Россия'
);



insert into movies.movie (name, p_year, country, genre, budget) values (
       'Трансформеры', 2009, 'США', 'Экшн', 250000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Белое солнце пустыни 2', 2007, 'Россия', 'Экшн', 5000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Криминальное чтиво', 1990, 'США', 'Экшн', 50000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Волк с Уолл-Стрит', 2005, 'США', 'Драма', 100000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Карты, деньги, два ствола', 1999, 'Великобритания', 'Экшн', 3000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Драйв', 2007, 'США', 'Экшн', 90000000
);
insert into movies.movie (name, p_year, country, genre) values (
       'Солярис', 1956, 'СССР', 'Фантастика'
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Завод', 2018, 'Россия', 'Драмма', 4000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Зеленая миля', 1997, 'США', 'Драма', 200000000
);
insert into movies.movie (name, p_year, country, genre, budget) values (
       'Твое имя', 2016, 'Япония', 'Аниме', 150000000
);







insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      10, '12-12-2018', '18:00', 'Россия', 'Москва', 'Октябрьский'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      10, '13-12-2018', '15:00', 'Россия', 'Москва', 'Октябрьский'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      10, '14-12-2018', '12:00', 'Россия', 'Москва', 'Октябрьский'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      10, '15-12-2018', '18:00', 'Россия', 'Москва', 'Октябрьский'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      1, '12-12-2018', '15:00', 'Россия', 'Москва', '17D'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      2, '12-12-2018', '18:00', 'Россия', 'Москва', 'Патриот'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      3, '12-12-2018', '18:00', 'Россия', 'Москва', 'АртДом'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      6, '15-12-2018', '18:00', 'Россия', 'Москва', 'Октябрьский'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      7, '12-12-2018', '15:00', 'Россия', 'Москва', '17D'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      8, '12-12-2018', '18:00', 'Россия', 'Москва', 'Патриот'
);

insert into movies.showtimes (movie_id, date, time, country, city, theater) values (
      3, '12-12-2018', '18:00', 'Россия', 'Москва', 'АртДом'
);



insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       1, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       2, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       3, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       4, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       5, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       6, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       7, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       8, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       9, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       10, 'EVGENII_NIKOVLAEVICH_PONASENKOV', 1, false, 'Дешевка'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       10, 'AnImE_PsIh', 10, true, 'Гениильно 10 из 10'
);
insert into movies.grades (movie_id, users_username, grade, recommend, review) values (
       1, 'YYY_NaGiBaToR_YYY', 10, true, 'Ваще крута!!11 Смешные шутки))0 и классный экнш!!))'
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       3, 'Gumanari', 5, false
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       7, 'Gumanari', 3, false
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       4, 'Geveve', 10, true
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       6, 'Geveve', 5, true
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       3, 'Geveve', 8, true
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       1, 'Qunantiah', 1, false
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       3, 'Qunantiah', 7, true
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       5, 'Qunantiah', 2, false
);
insert into movies.grades (movie_id, users_username, grade, recommend) values (
       2, 'Qunantiah', 2, false
);


insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Тарковский', 'Андрей', 7, 'Режиссер', 1000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Бей', 'Майкл', 1, 'Режиссер', 500000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Тарантино', 'Квентин', 3, 'Режиссер', 390000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
       'Тарантино', 'Квентин', 3, 'Актер', 45000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
       'Тарантино', 'Квентин', 3, 'Сценарист', 100000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Гостлинг', 'Райан', 6, 'Актер', 200000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Быков', 'Юрий', 8, 'Режиссер', 10000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Быков', 'Юрий', 8, 'Актер', 10000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Быков', 'Юрий', 8, 'Сценарист', 10000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Ричи', 'Гай', 5, 'Сценарист', 50000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'Хэнкс', 'Том', 9, 'Актер', 250000
);
insert into movies.movie_has_people (people_last_name, people_first_name, movie_id, job, salary) values (
        'ДиКаприо', 'Леонардо', 4, 'Актер', 300000
);


insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'AnImE_PsIh', 1
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'AnImE_PsIh', 2
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'AnImE_PsIh', 3
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'AnImE_PsIh', 4
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'Qunantiah', 5
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'Geveve', 6
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'EVGENII_NIKOVLAEVICH_PONASENKOV', 7
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'Gumanari', 8
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'YYY_NaGiBaToR_YYY', 9
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'MishMish', 10
);
insert into movies.users_has_showtimes (users_username, showtimes_id) values (
        'AnImE_PsIh', 11
);


---- Выбирает самого высокооплачиваемого актера -----
select people_first_name as "Имя", people_last_name as "Фамилия"
  from movies.movie_has_people
    where salary = (select max(salary)
      from movies.movie_has_people
        where job = 'Актер');

---- Данные о пользователе который ставит самые низкие оценки ----

select username as "Никнейм", email as "E-Mail", reg_date as "Дата регистрации",
       country as "Страна", first_name AS "Имя", last_name as  "Фамилия" from movies.users
  where username in (with usr_avg as (select users_username, avg(grade) as "avg_grd"
    from movies.grades
      group by users_username)
        select users_username from usr_avg
          where usr_avg.avg_grd = (select min(avg_grd) from usr_avg));

---- Фильм который пользователи чаще всего смотрели в кинотеатрах и количество просмотров ----

select distinct on (users_username) users_username as "Никнейм", name as "Название" , count(*) as "Количество"
  from movies.users_has_showtimes inner join movies.showtimes on
      movies.showtimes.id = movies.users_has_showtimes.showtimes_id inner join
        movies.movie on movies.movie.id = movie_id
          group by users_username, name
            order by users_username, "Количество" desc;





---- Имя фильма и его средний рейтинг -----
create view movies.AverageRating as
  select name, avg(grade)
    from movies.movie inner join movies.grades on
      movies.movie.id = movie_id
        group by movies.movie.id;

select * from movies.AverageRating;


---- Информация о пользователях доступная всех ----

create view movies.UserInfo as
  select username,
    case
      when country is NULL then 'undefined'
      else country end country,
    case
      when last_name is NULL then 'undefined'
      else last_name end last_name,
    case
      when first_name is NULL then 'undefined'
      else first_name end first_name
    from movies.users;

select * from movies.UserInfo;


CREATE OR REPLACE FUNCTION movies.get_kinopoisk_rating(m_id INT) RETURNS float AS $$
DECLARE
    res float;
    current_magic_constant float := 5;
    average_rating float;
    movie_average_rating float;
    number_of_votes int;
BEGIN
    average_rating = (select avg(grade) from movies.grades);
    movie_average_rating = (select avg(grade) from movies.grades where movie_id = m_id);
    number_of_votes = (select count(grade) from movies.grades where movie_id = m_id);
    res = (number_of_votes::float / (number_of_votes::float + current_magic_constant)) * movie_average_rating +
          (current_magic_constant::float4 / (number_of_votes::float + current_magic_constant)) * average_rating;
    return res;
END
$$ LANGUAGE 'plpgsql';


select movies.get_kinopoisk_rating(1);

CREATE OR REPLACE FUNCTION movies.closest_showtime(m_id INT) RETURNS date AS $$
DECLARE
    res date;
BEGIN
    return (select min(movies.showtimes.date)::date from movies.showtimes
        where movies.showtimes.date > now()::date and movie_id = m_id);
END
$$ LANGUAGE 'plpgsql';




create or replace function movies.add_person() returns trigger as
  $$
  declare
    fn varchar(255);
    ln varchar(255);
    begin
    fn := new.people_first_name;
    ln := new.people_last_name;
      if fn is NULL
        then raise exception 'not null';
         end if;

        if ln is NULL
        then raise exception 'not null';
         end if;

         begin
            insert into movies.people (first_name, last_name) values (fn, ln);
         exception
            WHEN others THEN
           end;

         return new;
    end;
  $$ LANGUAGE 'plpgsql';


create trigger movie_has_ppl_insert
  before insert on movies.movie_has_people
  for each row execute procedure movies.add_person();


create or replace function movies.delete_user() returns trigger as
  $$
  declare
    un varchar(255);
    begin
    un := old.username;

    delete from movies.grades where users_username = un;
    delete from movies.users_has_showtimes where users_username = un;

    return old;
    end;
  $$ LANGUAGE 'plpgsql';

create trigger delete_user_from_users
  before delete on movies.users
  for each row execute procedure movies.delete_user();
  
  
  
  create role common_user;
-- обычный пользователь, может просматривить оценки и публичную информаию других пользователей

grant select on movies.averagerating to common_user;
grant select on movies.userinfo to common_user;
grant insert on movies.grades to common_user;



create role moderator with password 'password';
-- модератор, может просматривать любую информацию о пользователях, а также удалять отзывы

grant select, delete on movies.grades to moderator;
grant select on movies.users to moderator;


create role rkn;
--РКН, может запрещать фильмы, удалять неугодные комментарии и банить оставивших их людей,
  -- также имеет права на отмену любого сеанса, чтобы дать место отечественному кинематографу
grant select, delete, insert on movies.showtimes to rkn;
grant delete on movies.users to rkn;
grant delete on movies.grades to rkn;
grant delete on movies.movie to rkn;