CREATE TABLE IF NOT EXISTS threads (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    category_id  UUID        NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    author_id    UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title        TEXT        NOT NULL,
    is_pinned    BOOLEAN     NOT NULL DEFAULT FALSE,
    is_locked    BOOLEAN     NOT NULL DEFAULT FALSE,
    reply_count  INT         NOT NULL DEFAULT 0,
    view_count   INT         NOT NULL DEFAULT 0,
    last_post_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_threads_category_last_post
    ON threads (category_id, last_post_at DESC);

CREATE INDEX IF NOT EXISTS idx_threads_author
    ON threads (author_id);
