CREATE TYPE statusReservation AS ENUM ('PENDING','CONFIRMED','CANCELLED');
CREATE TYPE statusOrder AS ENUM ('PREPARING','COMPLETED');

CREATE TABLE Genre (
    genre_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255)
);

CREATE TABLE Movie (
    movie_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    release_year INT NOT NULL,
    duration INT NOT NULL,
    language VARCHAR(255) NOT NULL,
    description VARCHAR(255)
);

CREATE TABLE Cinema (
    cinema_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL default  '123456789',
    email VARCHAR(255) default 'unknown@example.com',

    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CHECK (phone ~ '^\+?[0-9]{7,15}$')
);

CREATE TABLE Hall (
    hall_id SERIAL PRIMARY KEY,
    cinema_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    capacity INT NOT NULL,
    FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) on delete restrict on update cascade
);

CREATE TABLE Seat_Type (
    seat_type_id SERIAL PRIMARY KEY,
    type         VARCHAR(255) NOT NULL DEFAULT 'Standard',
    price        INT NOT NULL
);

CREATE TABLE Seat (
    seat_id      SERIAL PRIMARY KEY,
    hall_id      INT NOT NULL,
    seat_type_id INT NOT NULL DEFAULT 1,
    seat_row     INT NOT NULL,
    seat_number  INT NOT NULL,
    FOREIGN KEY (hall_id) REFERENCES Hall(hall_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (seat_type_id) REFERENCES Seat_Type(seat_type_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE CinemaUser (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) default 'Unknown',
    last_name VARCHAR(255) default 'Unknown',
    email VARCHAR(255) default 'unknown@example.com',
    phone VARCHAR(255) NOT NULL default '123456789',
    registration_date DATE DEFAULT CURRENT_DATE,

    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CHECK (phone ~ '^\+?[0-9]{7,15}$')
);

CREATE TABLE Employee (
    employee_id SERIAL PRIMARY KEY,
    cinema_id INT NOT NULL,
    first_name VARCHAR(255) default 'Unknown',
    last_name VARCHAR(255) default 'Unknown',
    phone VARCHAR(255) default '123456789',
    email VARCHAR(255) default 'unknown@example.com',
    hire_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) on delete restrict on update cascade ,

    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CHECK (phone ~ '^\+?[0-9]{7,15}$')
);

CREATE TABLE Showtime (
    showtime_id SERIAL PRIMARY KEY,
    movie_id INT NOT NULL,
    hall_id INT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    base_price INT NOT NULL,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) on delete restrict on update cascade ,
    FOREIGN KEY (hall_id) REFERENCES Hall(hall_id) on delete restrict on update cascade
);

CREATE TABLE Reservation (
    reservation_id   SERIAL PRIMARY KEY,
    user_id          INT NOT NULL DEFAULT 111111,
    showtime_id      INT NOT NULL,
    employee_id      INT NOT NULL DEFAULT 1,
    reservation_date DATE DEFAULT CURRENT_DATE,
    status  statusReservation DEFAULT 'PENDING',
    FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY (showtime_id) REFERENCES Showtime(showtime_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Ticket (
    ticket_id SERIAL PRIMARY KEY,
    showtime_id INT NOT NULL,
    seat_id INT NOT NULL,
    reservation_id INT NOT NULL,
    price INT NOT NULL,
    purchase_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (showtime_id) REFERENCES Showtime(showtime_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (seat_id) REFERENCES Seat(seat_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE ReservationPayment (
    payment_id     SERIAL PRIMARY KEY,
    user_id        INT NOT NULL DEFAULT 111111,
    reservation_id INT NOT NULL,
    employee_id    INT NOT NULL DEFAULT 1,
    amount         INT NOT NULL,
    payment_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    payment_method VARCHAR(255) NOT NULL,
    FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Review (
    review_id   SERIAL PRIMARY KEY,
    user_id     INT NOT NULL DEFAULT 111111,
    movie_id    INT NOT NULL,
    rating      INT NOT NULL,
    comment     VARCHAR(255),
    review_date DATE NOT NULL DEFAULT CURRENT_DATE,

    FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Role (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    base_salary INT NOT NULL
);

CREATE TABLE Employee_Role (
    employee_id INT NOT NULL,
    role_id     INT NOT NULL,
    PRIMARY KEY (employee_id, role_id),
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (role_id) REFERENCES Role(role_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Salary (
    salary_id    SERIAL PRIMARY KEY,
    employee_id  INT NOT NULL DEFAULT 1,
    amount       INT NOT NULL,
    payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Expense (
    expense_id   SERIAL PRIMARY KEY,
    cinema_id    INT NOT NULL,
    description  VARCHAR(255),
    amount       INT NOT NULL,
    expense_date DATE NOT NULL DEFAULT CURRENT_DATE,
    FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE CinemaOrder (
    order_id    SERIAL PRIMARY KEY,
    user_id     INT NOT NULL DEFAULT 111111,
    employee_id INT NOT NULL DEFAULT 1,
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    total_price INT NOT NULL,
    status      statusOrder NOT NULL DEFAULT 'PREPARING',

    FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Product (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    stock_quantity INT NOT NULL
);

CREATE TABLE Order_Product (
    order_product_id SERIAL PRIMARY KEY,
    order_id         INT NOT NULL,
    product_id       INT NOT NULL,
    quantity         INT NOT NULL,
    price_at_order   INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES CinemaOrder(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Supplier (
    supplier_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE Supply (
    supply_id      SERIAL PRIMARY KEY,
    product_id     INT NOT NULL,
    supplier_id    INT NOT NULL,
    expense_id     INT NOT NULL,
    quantity       INT NOT NULL,
    supply_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    price_per_unit INT NOT NULL,

    FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (expense_id) REFERENCES Expense(expense_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE OrderPayment (
    orderPayment_id SERIAL PRIMARY KEY,
    employee_id     INT NOT NULL DEFAULT 1,
    order_id        INT NOT NULL,
    user_id         INT NOT NULL DEFAULT 111111,
    amount          INT NOT NULL,
    payment_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    payment_method  VARCHAR(255) NOT NULL,

    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY (order_id) REFERENCES CinemaOrder(order_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE PresentationRights (
    presentationRights_id SERIAL PRIMARY KEY,
    movie_id   INT NOT NULL,
    cinema_id  INT NOT NULL,
    start_date DATE NOT NULL,
    end_date   DATE NOT NULL,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Genre_Movie (
    genre_id INT NOT NULL,
    movie_id INT NOT NULL,
    PRIMARY KEY (genre_id, movie_id),
    FOREIGN KEY (genre_id) REFERENCES Genre(genre_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE ON UPDATE CASCADE
);