-- Create table "Genres" to store movie genres

CREATE TABLE genres (
    genre_id INTEGER PRIMARY KEY AUTOINCREMENT,
    genre_name TEXT NOT NULL UNIQUE CHECK(length(genre_name) > 0), -- Genre name cannot be empty
    CONSTRAINT no_double_spaces_in_genre_name CHECK (genre_name NOT LIKE '%  %') -- Genre name cannot contain double spaces
);

-- Create table "Movies" to store movie details

CREATE TABLE movies (
    movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL UNIQUE CHECK(length(title) > 0), -- Movie title cannot be empty
    genre_id INTEGER,
    duration INTEGER CHECK(duration > 0), -- Movie duration must be greater than 0
    FOREIGN KEY (genre_id) REFERENCES genres (genre_id) ON DELETE CASCADE,
    CONSTRAINT no_double_spaces_in_title CHECK (title NOT LIKE '%  %') -- Movie title cannot contain double spaces
);

-- Create table "Halls" to store cinema hall details

CREATE TABLE halls (
    hall_id INTEGER PRIMARY KEY AUTOINCREMENT,
    hall_name TEXT NOT NULL UNIQUE CHECK(length(hall_name) > 0), -- Hall name cannot be empty
    capacity INTEGER NOT NULL CHECK(capacity > 0), -- Hall capacity must be positive
    CONSTRAINT no_double_spaces_in_hall_name CHECK (hall_name NOT LIKE '%  %') -- Hall name cannot contain double spaces
);

-- Create table "Sessions" to store session details

CREATE TABLE sessions (
    session_id INTEGER PRIMARY KEY AUTOINCREMENT,
    movie_id INTEGER,
    hall_id INTEGER,
    begin_at TIMESTAMP NOT NULL, -- Date and time of the session
    ticket_price REAL NOT NULL CHECK(ticket_price >= 0), -- Ticket price cannot be negative
    FOREIGN KEY (movie_id) REFERENCES movies (movie_id) ON DELETE CASCADE,
    FOREIGN KEY (hall_id) REFERENCES halls (hall_id) ON DELETE CASCADE,
    CONSTRAINT unique_hall_time UNIQUE (hall_id, begin_at) -- Prevent duplicate sessions in the same hall at the same time
);

-- Create table "Tickets" to store sold tickets

CREATE TABLE tickets (
    ticket_id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id INTEGER,
    seat_number INTEGER NOT NULL CHECK(seat_number > 0), -- Seat number must be positive
    sold_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Sale time defaults to current time
    FOREIGN KEY (session_id) REFERENCES sessions (session_id) ON DELETE CASCADE,
    CONSTRAINT unique_session_seat UNIQUE(session_id, seat_number) -- Unique combination of session and seat number
);

-- Create table "Refunds" to store refunded tickets

CREATE TABLE refunds (
    refund_id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket_id INTEGER NOT NULL UNIQUE,
    refund_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    refund_amount REAL NOT NULL CHECK (refund_amount >= 0), -- Refund amount cannot be negative
    FOREIGN KEY (ticket_id) REFERENCES tickets(ticket_id) ON DELETE CASCADE
);

-- Create table "Roles" to store user roles

CREATE TABLE roles (
    role_id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_name TEXT UNIQUE NOT NULL CHECK(length(role_name) > 0), -- Role name cannot be empty
    access_level INTEGER UNIQUE NOT NULL CHECK(access_level > 0), -- Access level must be positive
    CONSTRAINT no_spaces_in_role_name CHECK (role_name NOT LIKE '% %') -- Role name cannot contain spaces
);

-- Create table "Users" to store user details

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL CHECK(length(username) > 0), -- Username cannot be empty
    password_hash TEXT NOT NULL CHECK(length(password_hash) > 0), -- Password hash cannot be empty
    salt TEXT UNIQUE NOT NULL CHECK(length(salt) > 0), -- Salt cannot be empty
    role_id INTEGER NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    CONSTRAINT no_spaces_in_username CHECK (username NOT LIKE '% %') -- Username cannot contain spaces
);