CREATE TABLE MOVIE
(
    movie_id     SERIAL       NOT NULL,
    title        varchar(255) NOT NULL,
    release_date date         NOT NULL,
    description  varchar(255) NOT NULL,
    duration     int4         NOT NULL,
    PRIMARY KEY (movie_id)
);
CREATE TABLE ROLE
(
    role_id   SERIAL       NOT NULL,
    role_name varchar(255) NOT NULL,
    PRIMARY KEY (role_id)
);
CREATE TABLE DIRECTOR
(
    director_id varchar(13) NOT NULL,
    name        varchar(50) NOT NULL,
    surname     varchar(50) NOT NULL,
    PRIMARY KEY (director_id)
);
CREATE TABLE GENRE
(
    genre_id SERIAL      NOT NULL,
    name     varchar(50) NOT NULL,
    PRIMARY KEY (genre_id)
);
CREATE TABLE RESERVATION
(
    reservation_id SERIAL       NOT NULL,
    status         varchar(255) NOT NULL,
    "date"         date         NOT NULL,
    PRIMARY KEY (reservation_id)
);
CREATE TABLE PAYMENT
(
    payment_id     SERIAL       NOT NULL,
    amount         float4       NOT NULL,
    status         varchar(255) NOT NULL,
    reservation_id int4         NOT NULL,
    PRIMARY KEY (payment_id)
);
CREATE TABLE TICKET
(
    ticket_id      SERIAL      NOT NULL,
    price          float4      NOT NULL,
    user_id        varchar(13) NOT NULL,
    reservation_id int4        NOT NULL,
    seat_id        int4        NOT NULL,
    screening_id   int4        NOT NULL,
    PRIMARY KEY (ticket_id)
);
CREATE TABLE PAYMENT_METHOD
(
    payment_method_id SERIAL      NOT NULL,
    name              varchar(20) NOT NULL,
    payment_id        int4        NOT NULL,
    PRIMARY KEY (payment_method_id)
);
CREATE TABLE REVIEW
(
    review_id SERIAL       NOT NULL,
    comment   varchar(255) NOT NULL,
    "date"    date         NOT NULL,
    user_id   varchar(13)  NOT NULL,
    movie_id  int4         NOT NULL,
    rating    int4         NOT NULL,
    PRIMARY KEY (review_id)
);
CREATE TABLE SEAT
(
    seat_id       SERIAL NOT NULL,
    row_number    int4   NOT NULL,
    seat_number   int4   NOT NULL,
    type_id       int4   NOT NULL,
    cinemahall_id int4   NOT NULL,
    PRIMARY KEY (seat_id)
);
CREATE TABLE SEAT_TYPE
(
    seattype_id SERIAL      NOT NULL,
    name        varchar(20) NOT NULL,
    price       float4      NOT NULL,
    PRIMARY KEY (seattype_id)
);
CREATE TABLE SCREENING
(
    screening_id  SERIAL  NOT NULL,
    "date"        date    NOT NULL,
    time          time(7) NOT NULL,
    cinemahall_id int4    NOT NULL,
    movie_id      int4    NOT NULL,
    PRIMARY KEY (screening_id)
);
CREATE TABLE CINEMA
(
    cinema_id BIGSERIAL    NOT NULL,
    name      varchar(255) NOT NULL,
    address   varchar(255) NOT NULL,
    contact   varchar(255) NOT NULL,
    city_id   int4         NOT NULL,
    PRIMARY KEY (cinema_id)
);
CREATE TABLE CINEMA_HALL
(
    cinemahall_id SERIAL       NOT NULL,
    capacity      int4         NOT NULL,
    cinema_id     int8         NOT NULL,
    number_rows   int4         NOT NULL,
    number_seats  int4         NOT NULL,
    description   varchar(255) NOT NULL,
    status        varchar(255) NOT NULL,
    PRIMARY KEY (cinemahall_id)
);
CREATE TABLE CITY
(
    name    varchar(50) NOT NULL,
    city_id SERIAL      NOT NULL,
    PRIMARY KEY (city_id)
);
CREATE TABLE ACTOR
(
    actor_id varchar(13) NOT NULL,
    name     varchar(50) NOT NULL,
    surname  varchar(50) NOT NULL,
    PRIMARY KEY (actor_id)
);
CREATE TABLE PROMOTION
(
    promotion_id SERIAL      NOT NULL,
    name         varchar(50) NOT NULL,
    discount     float4      NOT NULL,
    "start"      date        NOT NULL,
    "end"        date        NOT NULL,
    screening_id int4        NOT NULL,
    PRIMARY KEY (promotion_id)
);
CREATE TABLE PURCHASED_PRODUCT
(
    purchased_product_id SERIAL NOT NULL,
    numbers              int4   NOT NULL,
    ticket_id            int4   NOT NULL,
    product_id           int4   NOT NULL,
    PRIMARY KEY (purchased_product_id)
);
CREATE TABLE PRODUCT
(
    product_id SERIAL      NOT NULL,
    name       varchar(50) NOT NULL,
    unit       varchar(20) NOT NULL,
    quantity   int4        NOT NULL,
    price      float4      NOT NULL,
    PRIMARY KEY (product_id)
);
CREATE TABLE NOTIFICATION
(
    notification_id SERIAL       NOT NULL,
    message         varchar(255) NOT NULL,
    "date"          date         NOT NULL,
    user_id         varchar(13)  NOT NULL,
    PRIMARY KEY (notification_id)
);
CREATE TABLE "USER"
(
    user_id   varchar(13)  NOT NULL,
    password  varchar(255) NOT NULL,
    name      varchar(50)  NOT NULL,
    surname   varchar(255) NOT NULL,
    role_id   int4         NOT NULL,
    cinema_id int8,
    PRIMARY KEY (user_id)
);
CREATE TABLE USER_RESERVATION
(
    user_id varchar(13) NOT NULL,
    reservation_id int4 NOT NULL,
    PRIMARY KEY (user_id, reservation_id)
);

CREATE TABLE MOVIE_GENRE
(
    genre_id int4 NOT NULL,
    movie_id int4 NOT NULL,
    PRIMARY KEY (genre_id, movie_id)
);

CREATE TABLE MOVIE_ACTOR
(
    movie_id int4 NOT NULL,
    actor_id varchar(13) NOT NULL,
    PRIMARY KEY (movie_id, actor_id)
);

CREATE TABLE MOVIE_DIRECTOR
(
    director_id varchar(13) NOT NULL,
    movie_id int4 NOT NULL,
    PRIMARY KEY (director_id, movie_id)
);
CREATE TABLE PERMISSION
(
    permission_id SERIAL NOT NULL,
    status bool NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (permission_id)
);
CREATE TABLE ROLE_PERMISSION
(
    role_id int4 NOT NULL,
    permission_id int4 NOT NULL,
    PRIMARY KEY (role_id, permission_id)
);
ALTER TABLE REVIEW
    ADD CONSTRAINT FKREVIEW298251 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
ALTER TABLE REVIEW
    ADD CONSTRAINT FKREVIEW927899 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
ALTER TABLE TICKET
    ADD CONSTRAINT FKTICKET285003 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
ALTER TABLE USER_RESERVATION
    ADD CONSTRAINT FKUSER_RESER151935 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
ALTER TABLE USER_RESERVATION
    ADD CONSTRAINT FKUSER_RESER867618 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
ALTER TABLE PAYMENT
    ADD CONSTRAINT FKPAYMENT601419 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
ALTER TABLE PAYMENT_METHOD
    ADD CONSTRAINT FKPAYMENT_ME706637 FOREIGN KEY (payment_id) REFERENCES PAYMENT (payment_id);
ALTER TABLE TICKET
    ADD CONSTRAINT FKTICKET332966 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
ALTER TABLE TICKET
    ADD CONSTRAINT FKTICKET680705 FOREIGN KEY (seat_id) REFERENCES SEAT (seat_id);
ALTER TABLE SEAT
    ADD CONSTRAINT FKSEAT164380 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id);
ALTER TABLE SEAT
    ADD CONSTRAINT FKSEAT804590 FOREIGN KEY (type_id) REFERENCES SEAT_TYPE (seattype_id);
ALTER TABLE SCREENING
    ADD CONSTRAINT FKSCREENING975220 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id);
ALTER TABLE CINEMA_HALL
    ADD CONSTRAINT FKCINEMA_HAL154643 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id);
ALTER TABLE TICKET
    ADD CONSTRAINT FKTICKET411280 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id);
ALTER TABLE PURCHASED_PRODUCT
    ADD CONSTRAINT FKPURCHASED_989966 FOREIGN KEY (ticket_id) REFERENCES TICKET (ticket_id);
ALTER TABLE SCREENING
    ADD CONSTRAINT FKSCREENING176236 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
ALTER TABLE PROMOTION
    ADD CONSTRAINT FKPROMOTION365376 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id);
ALTER TABLE MOVIE_GENRE
    ADD CONSTRAINT FKMOVIE_GENR273664 FOREIGN KEY (genre_id) REFERENCES GENRE (genre_id);
ALTER TABLE MOVIE_GENRE
    ADD CONSTRAINT FKMOVIE_GENR655579 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
ALTER TABLE MOVIE_ACTOR
    ADD CONSTRAINT FKMOVIE_ACTO60552 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
ALTER TABLE MOVIE_ACTOR
    ADD CONSTRAINT FKMOVIE_ACTO38053 FOREIGN KEY (actor_id) REFERENCES ACTOR (actor_id);
ALTER TABLE MOVIE_DIRECTOR
    ADD CONSTRAINT FKMOVIE_DIRE300372 FOREIGN KEY (director_id) REFERENCES DIRECTOR (director_id);
ALTER TABLE MOVIE_DIRECTOR
    ADD CONSTRAINT FKMOVIE_DIRE810181 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
ALTER TABLE "USER"
    ADD CONSTRAINT FKUSER65207 FOREIGN KEY (role_id) REFERENCES ROLE (role_id);
ALTER TABLE PURCHASED_PRODUCT
    ADD CONSTRAINT FKPURCHASED_466460 FOREIGN KEY (product_id) REFERENCES PRODUCT (product_id);
ALTER TABLE ROLE_PERMISSION
    ADD CONSTRAINT FKROLE_PERMI829363 FOREIGN KEY (role_id) REFERENCES ROLE (role_id);
ALTER TABLE ROLE_PERMISSION
    ADD CONSTRAINT FKROLE_PERMI727346 FOREIGN KEY (permission_id) REFERENCES PERMISSION (permission_id);
ALTER TABLE NOTIFICATION
    ADD CONSTRAINT FKNOTIFICATI106294 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
ALTER TABLE CINEMA
    ADD CONSTRAINT FKCINEMA56868 FOREIGN KEY (city_id) REFERENCES CITY (city_id);
ALTER TABLE "USER"
    ADD CONSTRAINT FKUSER561546 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id);