DDL: ddl_final2.sql

CREATE TABLE MOVIE
(
    movie_id     SERIAL  NOT NULL,
    title        text    NOT NULL,
    release_date date    NOT NULL,
    description  text    NOT NULL,
    duration     int     NOT NULL,
    PRIMARY KEY (movie_id),
    CONSTRAINT check_duration CHECK (duration > 0)
);

CREATE TABLE ROLE
(
    role_id   SERIAL NOT NULL,
    role_name text   NOT NULL,
    PRIMARY KEY (role_id),
    CONSTRAINT unique_role_name UNIQUE (role_name)
);

CREATE TABLE DIRECTOR
(
    director_id text NOT NULL,
    name        text NOT NULL,
    surname     text NOT NULL,
    PRIMARY KEY (director_id)
);

CREATE TABLE GENRE
(
    genre_id SERIAL NOT NULL,
    name     text   NOT NULL,
    PRIMARY KEY (genre_id),
    CONSTRAINT unique_genre_name UNIQUE (name)
);

CREATE TABLE CITY
(
    name    text   NOT NULL,
    city_id SERIAL NOT NULL,
    PRIMARY KEY (city_id),
    CONSTRAINT unique_city_name UNIQUE (name)
);

CREATE TABLE CINEMA
(
    cinema_id BIGSERIAL NOT NULL,
    name      text      NOT NULL,
    address   text      NOT NULL,
    contact   text      NOT NULL,
    city_id   int       NOT NULL,
    PRIMARY KEY (cinema_id),
    CONSTRAINT FKCINEMA56868 FOREIGN KEY (city_id) REFERENCES CITY (city_id) ON DELETE CASCADE,
    CONSTRAINT unique_cinema_contact UNIQUE (contact)
);

CREATE TABLE ROLE_PERMISSION
(
    role_id       int NOT NULL,
    permission_id int NOT NULL,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE "USER"
(
    user_id   text   NOT NULL,
    password  text   NOT NULL,
    name      text   NOT NULL,
    surname   text   NOT NULL,
    role_id   int    NOT NULL,
    cinema_id bigint,
    PRIMARY KEY (user_id),
    CONSTRAINT FKUSER65207  FOREIGN KEY (role_id)   REFERENCES ROLE (role_id),
    CONSTRAINT FKUSER561546 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id) ON DELETE SET NULL
);

CREATE TABLE CINEMA_HALL
(
    cinemahall_id SERIAL NOT NULL,
    capacity      int    NOT NULL,
    cinema_id     bigint NOT NULL,
    number_rows   int    NOT NULL,
    number_seats  int    NOT NULL,
    description   text   NOT NULL,
    status        text   NOT NULL,
    PRIMARY KEY (cinemahall_id),
    CONSTRAINT FKCINEMA_HAL154643 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id) ON DELETE CASCADE,
    CONSTRAINT check_capacity     CHECK (capacity > 0),
    CONSTRAINT check_number_rows  CHECK (number_rows > 0),
    CONSTRAINT check_number_seats CHECK (number_seats > 0)
);

CREATE TABLE SEAT_TYPE
(
    seattype_id SERIAL NOT NULL,
    name        text   NOT NULL,
    price       float4 NOT NULL,
    PRIMARY KEY (seattype_id),
    CONSTRAINT check_seattype_price  CHECK (price > 0),
    CONSTRAINT unique_seattype_name  UNIQUE (name)
);

CREATE TABLE SEAT
(
    seat_id       SERIAL NOT NULL,
    row_number    int    NOT NULL,
    seat_number   int    NOT NULL,
    type_id       int    NOT NULL,
    cinemahall_id int    NOT NULL,
    PRIMARY KEY (seat_id),
    CONSTRAINT FKSEAT164380 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id) ON DELETE CASCADE,
    CONSTRAINT FKSEAT804590 FOREIGN KEY (type_id)       REFERENCES SEAT_TYPE (seattype_id)
);

CREATE TABLE SCREENING
(
    screening_id  SERIAL  NOT NULL,
    "date"        date    NOT NULL,
    "time"        time(7) NOT NULL,
    cinemahall_id int     NOT NULL,
    movie_id      int     NOT NULL,
    PRIMARY KEY (screening_id),
    CONSTRAINT FKSCREENING975220 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id) ON DELETE CASCADE,
    CONSTRAINT FKSCREENING176236 FOREIGN KEY (movie_id)      REFERENCES MOVIE (movie_id) ON DELETE CASCADE
);

CREATE TABLE RESERVATION
(
    reservation_id SERIAL NOT NULL,
    status         text   NOT NULL,
    "date"         date   NOT NULL,
    PRIMARY KEY (reservation_id)
);

CREATE TABLE PAYMENT
(
    payment_id     SERIAL  NOT NULL,
    amount         float4  NOT NULL,
    status         text    NOT NULL,
    reservation_id int     NOT NULL,
    PRIMARY KEY (payment_id),
    CONSTRAINT FKPAYMENT601419 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id) ON DELETE CASCADE,
    CONSTRAINT check_amount CHECK (amount > 0)
);

CREATE TABLE TICKET
(
    ticket_id      SERIAL NOT NULL,
    price          float4 NOT NULL,
    user_id        text,
    reservation_id int,
    seat_id        int    NOT NULL,
    screening_id   int    NOT NULL,
    PRIMARY KEY (ticket_id),
    CONSTRAINT FKTICKET285003 FOREIGN KEY (user_id)        REFERENCES "USER" (user_id),
    CONSTRAINT FKTICKET332966 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id) ON DELETE CASCADE,
    CONSTRAINT FKTICKET680705 FOREIGN KEY (seat_id)        REFERENCES SEAT (seat_id) ON DELETE CASCADE,
    CONSTRAINT FKTICKET411280 FOREIGN KEY (screening_id)   REFERENCES SCREENING (screening_id) ON DELETE CASCADE,
    CONSTRAINT check_price           CHECK (price > 0),
    CONSTRAINT unique_seat_screening UNIQUE (seat_id, screening_id)
);

CREATE TABLE PAYMENT_METHOD
(
    payment_method_id SERIAL NOT NULL,
    name              text   NOT NULL,
    payment_id        int    NOT NULL,
    PRIMARY KEY (payment_method_id),
    CONSTRAINT FKPAYMENT_ME706637 FOREIGN KEY (payment_id) REFERENCES PAYMENT (payment_id) ON DELETE CASCADE,
    CONSTRAINT unique_payment_method_name UNIQUE (name)
);

CREATE TABLE REVIEW
(
    review_id SERIAL NOT NULL,
    comment   text   NOT NULL,
    "date"    date   NOT NULL,
    user_id   text   NOT NULL,
    movie_id  int    NOT NULL,
    rating    int    NOT NULL,
    PRIMARY KEY (review_id),
    CONSTRAINT FKREVIEW298251   FOREIGN KEY (user_id)  REFERENCES "USER" (user_id) ON DELETE CASCADE,
    CONSTRAINT FKREVIEW927899   FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id) ON DELETE CASCADE,
    CONSTRAINT check_rating     CHECK (rating >= 1 AND rating <= 10),
    CONSTRAINT unique_user_movie UNIQUE (user_id, movie_id)
);

CREATE TABLE ACTOR
(
    actor_id text NOT NULL,
    name     text NOT NULL,
    surname  text NOT NULL,
    PRIMARY KEY (actor_id)
);

CREATE TABLE PROMOTION
(
    promotion_id SERIAL NOT NULL,
    name         text   NOT NULL,
    discount     float4 NOT NULL,
    "start"      date   NOT NULL,
    "end"        date   NOT NULL,
    screening_id int    NOT NULL,
    PRIMARY KEY (promotion_id),
    CONSTRAINT FKPROMOTION365376 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id) ON DELETE CASCADE,
    CONSTRAINT check_discount    CHECK (discount > 0 AND discount <= 100),
    CONSTRAINT unique_promotion_name UNIQUE (name)
);

CREATE TABLE PRODUCT
(
    product_id SERIAL NOT NULL,
    name       text   NOT NULL,
    unit       text   NOT NULL,
    quantity   int    NOT NULL,
    price      float4 NOT NULL,
    PRIMARY KEY (product_id),
    CONSTRAINT check_product_price    CHECK (price > 0),
    CONSTRAINT check_product_quantity CHECK (quantity >= 0),
    CONSTRAINT unique_product_name    UNIQUE (name)
);

CREATE TABLE PURCHASED_PRODUCT
(
    purchased_product_id SERIAL NOT NULL,
    numbers              int    NOT NULL,
    ticket_id            int    NOT NULL,
    product_id           int    NOT NULL,
    PRIMARY KEY (purchased_product_id),
    CONSTRAINT FKPURCHASED_989966 FOREIGN KEY (ticket_id)  REFERENCES TICKET (ticket_id) ON DELETE CASCADE,
    CONSTRAINT FKPURCHASED_466460 FOREIGN KEY (product_id) REFERENCES PRODUCT (product_id),
    CONSTRAINT check_numbers CHECK (numbers > 0)
);

CREATE TABLE NOTIFICATION
(
    notification_id SERIAL NOT NULL,
    message         text   NOT NULL,
    "date"          date   NOT NULL,
    user_id         text   NOT NULL,
    PRIMARY KEY (notification_id),
    CONSTRAINT FKNOTIFICATI106294 FOREIGN KEY (user_id) REFERENCES "USER" (user_id) ON DELETE CASCADE
);

CREATE TABLE USER_RESERVATION
(
    user_id        text NOT NULL,
    reservation_id int  NOT NULL,
    PRIMARY KEY (user_id, reservation_id),
    CONSTRAINT FKUSER_RESER151935 FOREIGN KEY (user_id)        REFERENCES "USER" (user_id) ON DELETE CASCADE,
    CONSTRAINT FKUSER_RESER867618 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id) ON DELETE CASCADE
);

CREATE TABLE MOVIE_GENRE
(
    genre_id int NOT NULL,
    movie_id int NOT NULL,
    PRIMARY KEY (genre_id, movie_id),
    CONSTRAINT FKMOVIE_GENR273664 FOREIGN KEY (genre_id) REFERENCES GENRE (genre_id) ON DELETE CASCADE,
    CONSTRAINT FKMOVIE_GENR655579 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id) ON DELETE CASCADE
);

CREATE TABLE MOVIE_ACTOR
(
    movie_id int  NOT NULL,
    actor_id text NOT NULL,
    PRIMARY KEY (movie_id, actor_id),
    CONSTRAINT FKMOVIE_ACTO60552 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id) ON DELETE CASCADE,
    CONSTRAINT FKMOVIE_ACTO38053 FOREIGN KEY (actor_id) REFERENCES ACTOR (actor_id) ON DELETE CASCADE
);

CREATE TABLE MOVIE_DIRECTOR
(
    director_id text NOT NULL,
    movie_id    int  NOT NULL,
    PRIMARY KEY (director_id, movie_id),
    CONSTRAINT FKMOVIE_DIRE300372 FOREIGN KEY (director_id) REFERENCES DIRECTOR (director_id) ON DELETE CASCADE,
    CONSTRAINT FKMOVIE_DIRE810181 FOREIGN KEY (movie_id)    REFERENCES MOVIE (movie_id) ON DELETE CASCADE
);

CREATE TABLE PERMISSION
(
    permission_id SERIAL  NOT NULL,
    status        boolean NOT NULL,
    name          text    NOT NULL,
    PRIMARY KEY (permission_id),
    CONSTRAINT unique_permission_name UNIQUE (name)
);

CREATE TABLE ROLE_PERMISSION
(
    role_id       int NOT NULL,
    permission_id int NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT FKROLE_PERMI829363 FOREIGN KEY (role_id)       REFERENCES ROLE (role_id) ON DELETE CASCADE,
    CONSTRAINT FKROLE_PERMI727346 FOREIGN KEY (permission_id) REFERENCES PERMISSION (permission_id) ON DELETE CASCADE
);