create table if not exists "Film"
(
    "FilmName" varchar(50) not null,
    "ReleaseDate" varchar(17),
    "LengthOfFilm" varchar(7),
    "MainRoles" varchar(1000),
    "Logo" varchar(200),
    "Genre" varchar(100),
    "FilmIMDB" integer not null
        constraint film_pk
            primary key,
    "ReleaseYear" varchar(4),
    "Rated" varchar(10),
    "Director" varchar(100),
    "Plot" varchar(500)
);

alter table "Film" owner to postgres;

create unique index if not exists film_filmname_uindex
    on "Film" ("FilmName");

create unique index if not exists film_filmimdb_uindex
    on "Film" ("FilmIMDB");

create table if not exists "Cinema"
(
    "CinemaID" serial not null
        constraint cinema_pk
            primary key,
    "Address" varchar(300),
    "PublicName" varchar(100)
);

alter table "Cinema" owner to postgres;

create table if not exists "Hall"
(
    "HallID" serial not null
        constraint hall_pk
            primary key,
    "RowsNumber" smallint not null,
    "CinemaID" integer
        constraint hall_cinema__fk
            references "Cinema"
                on update cascade on delete cascade,
    "HallName" varchar(20)
);

alter table "Hall" owner to postgres;

create table if not exists "Row"
(
    "RowID" serial not null
        constraint row_pk
            primary key,
    "SeatNumber" smallint not null,
    "HallID" integer
        constraint row_hall__fk
            references "Hall"
                on update cascade on delete cascade,
    "NumberInHall" smallint
);

alter table "Row" owner to postgres;

create table if not exists "Seat"
(
    "SeatID" serial not null
        constraint seat_pk
            primary key,
    "RowID" integer
        constraint seat_row__fk
            references "Row"
                on update cascade on delete cascade,
    "SeatName" varchar(3)
);

alter table "Seat" owner to postgres;

create unique index if not exists seat_seatid_uindex
    on "Seat" ("SeatID");

create table if not exists "Customer"
(
    "Email" varchar(50) not null
        constraint customer_pk
            primary key,
    "FirstName" varchar(50),
    "LastName" varchar(50),
    "LoginName" varchar(25) not null,
    "PasswordHash" varchar(60) not null
);

alter table "Customer" owner to postgres;

create unique index if not exists customer_loginname_uindex
    on "Customer" ("LoginName");

create unique index if not exists customer_email_uindex
    on "Customer" ("Email");

create table if not exists "SessionFilm"
(
    "SessionID" serial not null
        constraint sessionfilm_pk
            primary key,
    "HallID" integer
        constraint sessionfilm_hall__fk
            references "Hall"
                on update cascade on delete cascade,
    "FilmIMDB" integer
        constraint sessionfilm_film__fk
            references "Film"
                on update cascade on delete cascade,
    "Price" smallint,
    "DateAndTime" timestamp
);

alter table "SessionFilm" owner to postgres;

create unique index if not exists sessionfilm_sessionid_uindex
    on "SessionFilm" ("SessionID");

create table if not exists "Ticket"
(
    "TicketID" serial not null
        constraint table_name_pk
            primary key,
    "SeatID" integer
        constraint table_name_seat__fk
            references "Seat"
                on update cascade on delete cascade,
    "SessionID" integer
        constraint table_name_sessionfilm__fk
            references "SessionFilm"
                on update cascade on delete cascade,
    "CustomerID" varchar(25)
        constraint table_name_customer__fk
            references "Customer"
                on update cascade on delete cascade
);

alter table "Ticket" owner to postgres;

create unique index if not exists table_name_ticketid_uindex
    on "Ticket" ("TicketID");