/**
    Author: Filip Pitak
    2AIN 2021/2022
 */

drop table if exists movies cascade;
create table movies
(
    movie_id serial primary key,
    name varchar not null,
    length integer not null,
    description text
);

drop table if exists genres cascade;
create table genres
(
    genre_id serial primary key,
    name varchar not null
);

drop table if exists movie_genres cascade;
create table movie_genres
(
    movie_id int not null,
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id) ON DELETE CASCADE,
    genre_id int not null,
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id) ON DELETE CASCADE
);

drop table if exists cinema_halls cascade;
create table cinema_halls
(
    cinemahall_id serial primary key,
    hallLabel varchar not null,
    rowCount int not null,
    seatsPerRow int not null,
    screenType varchar not null
);

drop table if exists seats cascade;
create table seats
(
    seat_id serial primary key,
    hall_id int not null,
    FOREIGN KEY (hall_id) REFERENCES cinema_halls(cinemahall_id) ON DELETE CASCADE,
    row int not null,
    seatNumber int not null
);

drop table if exists screenings cascade;
create table screenings
(
    screening_id serial primary key,
    movie_id int,
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id) ON DELETE SET NULL,
    hall_id int,
    FOREIGN KEY (hall_id) REFERENCES cinema_halls(cinemahall_id) ON DELETE SET NULL,
    screeningDay TIMESTAMP not null
);

drop table if exists prices cascade;
create table prices
(
    price_id serial primary key,
    value double precision not null,
    customerType varchar not null,
    screeningType varchar not null
);

drop table if exists customers cascade;
create table customers
(
    customer_id serial primary key,
    firstName varchar not null,
    lastName varchar not null,
    dateOfBirth DATE not null,
    email varchar not null
);

drop table if exists vouchers cascade;
create table vouchers
(
    voucher_id serial primary key,
    customer_id integer,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL,
    code varchar not null,
    validUntil DATE,
    used boolean not null,
    discountValue double precision not null
);

drop table if exists reservations cascade;
create table reservations
(
    reservation_id serial primary key,
    customer_id int,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL,
    screening_id integer,
    FOREIGN KEY (screening_id) REFERENCES screenings(screening_id) ON DELETE SET NULL,
    begin TIMESTAMP not null,
    pending boolean not null,
    voucher_id integer,
    FOREIGN KEY (voucher_id) REFERENCES vouchers(voucher_id) ON DELETE SET NULL,
    finalPrice double precision DEFAULT 0
);

drop table if exists tickets cascade;
create table tickets
(
    ticket_id serial primary key,
    screening_id integer,
    FOREIGN KEY (screening_id) REFERENCES screenings(screening_id) ON DELETE SET NULL,
    seat_id integer,
    FOREIGN KEY (seat_id) REFERENCES seats(seat_id) ON DELETE SET NULL,
    state varchar not null DEFAULT 'AVAILABLE',
    reservation_id integer DEFAULT NULL,
    FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) ON DELETE SET NULL,
    price_id integer DEFAULT NULL,
    FOREIGN KEY (price_id) REFERENCES prices(price_id) ON DELETE SET NULL,
    finalPrice double precision DEFAULT 0
);