-- Per-page collections and tags. Collections become heterogeneous: the
-- existing `collection_mangas` join holds whole mangas, this new
-- `collection_pages` join holds individual pages. A page tagged or
-- collected references the stable `pages.id` UUID, so re-uploading a
-- chapter (which deletes and recreates the page rows) silently drops
-- any saves attached to it — cascade is intentional, matching the
-- semantics of the cover-image references in existing collections.

CREATE TABLE collection_pages (
    collection_id uuid NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    page_id       uuid NOT NULL REFERENCES pages(id)       ON DELETE CASCADE,
    added_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (collection_id, page_id)
);

-- Reverse lookup: "which of my collections contain this page?" — the
-- reader's context menu pre-checks the matching collection rows on
-- open.
CREATE INDEX collection_pages_page_idx ON collection_pages (page_id);

-- Per-user, per-page free-form tags. Distinct from the manga-level
-- shared `tags` taxonomy — these are personal annotations. Length cap
-- 64 leaves room for `namespace:value` conventions (e.g.
-- `character:askeladd`) without making the schema enforce them.
CREATE TABLE page_tags (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    page_id    uuid NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
    tag        text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT page_tags_tag_nonempty CHECK (length(tag) > 0 AND length(tag) <= 64)
);

-- One row per (user, page, tag). The repo upserts via ON CONFLICT DO
-- NOTHING so re-tagging is a no-op (handler returns 200 instead of 201).
CREATE UNIQUE INDEX page_tags_user_page_tag_uniq
    ON page_tags (user_id, page_id, tag);

-- "Show me everything I've tagged" — newest-first. Drives the library
-- Page-tags tab.
CREATE INDEX page_tags_user_idx ON page_tags (user_id, created_at DESC);

-- "Show me what I've tagged X" — same tab, when a chip filter is
-- active.
CREATE INDEX page_tags_user_tag_idx ON page_tags (user_id, tag, created_at DESC);

-- Per-page lookup for the context menu's contextual line.
CREATE INDEX page_tags_page_idx ON page_tags (page_id);
