init.sql

-- NUST SeatFlow — database schema
-- Run this script directly in PostgreSQL to create the database structure.
-- The database itself must be created beforehand (e.g. CREATE DATABASE "SeatFlow";)

CREATE TABLE IF NOT EXISTS roles (
    id   SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS status (
    id     SERIAL PRIMARY KEY,
    status VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS users (
    id              SERIAL PRIMARY KEY,
    role_id         INTEGER NOT NULL REFERENCES roles(id),
    first_name      VARCHAR NOT NULL,
    last_name       VARCHAR NOT NULL,
    email           VARCHAR NOT NULL UNIQUE,
    hashed_password VARCHAR NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_users_email   ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_role_id ON users(role_id);

CREATE TABLE IF NOT EXISTS rooms (
    id          SERIAL PRIMARY KEY,
    building    VARCHAR NOT NULL,
    room_number VARCHAR NOT NULL,
    floor       INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_rooms_floor    ON rooms(floor);
CREATE INDEX IF NOT EXISTS idx_rooms_building ON rooms(building);

CREATE TABLE IF NOT EXISTS desks (
    id                SERIAL PRIMARY KEY,
    room_id           INTEGER NOT NULL REFERENCES rooms(id),
    desk_number       INTEGER NOT NULL,
    seats_quantity    INTEGER NOT NULL,
    has_power_sockets BOOLEAN,
    has_computers     BOOLEAN
);

CREATE INDEX IF NOT EXISTS idx_desks_room_id ON desks(room_id);

CREATE TABLE IF NOT EXISTS seats (
    id          SERIAL PRIMARY KEY,
    desk_id     INTEGER NOT NULL REFERENCES desks(id),
    seat_number INTEGER NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE INDEX IF NOT EXISTS idx_seats_desk_id ON seats(desk_id);

CREATE TABLE IF NOT EXISTS reservations (
    id               SERIAL PRIMARY KEY,
    user_id          INTEGER NOT NULL REFERENCES users(id),
    seat_id          INTEGER NOT NULL REFERENCES seats(id),
    start_time       TIMESTAMPTZ NOT NULL,
    end_time         TIMESTAMPTZ NOT NULL,
    status_id        INTEGER NOT NULL REFERENCES status(id),
    status_change_at TIMESTAMPTZ NOT NULL,
    created_at       TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_reservations_user_id    ON reservations(user_id);
CREATE INDEX IF NOT EXISTS idx_reservations_seat_id    ON reservations(seat_id);
CREATE INDEX IF NOT EXISTS idx_reservations_status_id  ON reservations(status_id);
CREATE INDEX IF NOT EXISTS idx_reservations_start_time ON reservations(start_time);

-- Seed data
INSERT INTO roles (id, title) VALUES
    (1, 'user'),
    (2, 'admin')
ON CONFLICT (id) DO NOTHING;

INSERT INTO status (id, status) VALUES
    (1, 'booked'),
    (2, 'checked_in'),
    (3, 'cancelled'),
    (4, 'no_show'),
    (5, 'finished')
ON CONFLICT (id) DO NOTHING;