schema.sql

-- Library Resource Booking & Reservation Queue — DDL
-- Demonstrates normalized schema for borrow, waitlist FIFO, and reservation lifecycle.

CREATE TABLE resources (
    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title        VARCHAR(255) NOT NULL,
    type         VARCHAR(32)  NOT NULL,
    total_copies INT          NOT NULL CHECK (total_copies >= 1)
);

CREATE TABLE employees (
    id    BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE borrow_records (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    resource_id BIGINT    NOT NULL REFERENCES resources(id),
    employee_id BIGINT    NOT NULL REFERENCES employees(id),
    borrowed_at TIMESTAMP NOT NULL,
    returned_at TIMESTAMP,
    CONSTRAINT uq_active_borrow UNIQUE (resource_id, employee_id, returned_at)
);

CREATE TABLE waitlist_entries (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    resource_id BIGINT      NOT NULL REFERENCES resources(id),
    employee_id BIGINT      NOT NULL REFERENCES employees(id),
    joined_at   TIMESTAMP   NOT NULL,
    status      VARCHAR(32) NOT NULL
);

CREATE INDEX idx_waitlist_resource_status_joined
    ON waitlist_entries (resource_id, status, joined_at);

CREATE TABLE reservations (
    id                BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    waitlist_entry_id BIGINT      NOT NULL REFERENCES waitlist_entries(id),
    resource_id       BIGINT      NOT NULL REFERENCES resources(id),
    employee_id       BIGINT      NOT NULL REFERENCES employees(id),
    created_at        TIMESTAMP   NOT NULL,
    expires_at        TIMESTAMP   NOT NULL,
    status            VARCHAR(32) NOT NULL
);

CREATE INDEX idx_reservation_status_expires
    ON reservations (status, expires_at);

-- Availability is derived:
-- available = total_copies - active_borrows - pending_reservations
-- Reservation expiry releases hold and advances FIFO waitlist via application service.