SQL sqript

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', 'vanya2006@mail.ru'
);
INSERT INTO movies.users (username, email) VALUES (
      'Qunantiah', 'qunant@gmail.com'
);
INSERT INTO movies.users (username, email) VALUES (
      'Gumanari', 'slavavv@mail.ru'
);
INSERT INTO movies.users (username, email) VALUES (
      'Geveve', 'izmair@yahoo.com'
);
INSERT INTO movies.users (username, email) VALUES (
      'AnImE_PsIh', 'kolya2008@mail.ru'
);
INSERT INTO movies.users (username, email, reg_date, first_name) VALUES (
      'MishMish', 'mhavarov@mail.ru', '21-04-2008', 'Миша'
);
INSERT INTO movies.users (username, email, reg_date, first_name, last_name, country) VALUES (
      'EVGENII_NIKOVLAEVICH_PONASENKOV', 'greatest_genious@mail.ru', '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;