-- Bookmarks are always private — they're the user's "save my place" markers.
-- We don't store the full passage text, only the source URL + a short excerpt
-- so the bookmarks index can render meaningful previews without a separate
-- content fetch.
--
-- UNIQUE (user_id, source_url) means re-bookmarking the same passage is a
-- no-op rather than producing a duplicate; the service maps the unique
-- violation to a 200 idempotent response.
CREATE TABLE IF NOT EXISTS bookmarks (
    id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    source_url    TEXT         NOT NULL,
    source_title  VARCHAR(200),
    excerpt       TEXT,
    note          TEXT,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (user_id, source_url)
);

CREATE INDEX IF NOT EXISTS bookmarks_user_created_idx
    ON bookmarks (user_id, created_at DESC);