-- User-owned manga collections. Each user can curate any number of
-- named lists (e.g., "Favorites", "Reading list"); mangas can belong
-- to many collections of many users without restriction.

CREATE TABLE collections (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        text NOT NULL,
    description text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- Per-user case-insensitive name uniqueness so "Favorites" and
-- "favorites" don't both end up in someone's sidebar.
CREATE UNIQUE INDEX collections_user_name_lower_uniq
    ON collections (user_id, lower(name));

CREATE INDEX collections_user_idx ON collections (user_id, created_at DESC);

CREATE TABLE collection_mangas (
    collection_id uuid NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    manga_id      uuid NOT NULL REFERENCES mangas(id) ON DELETE CASCADE,
    added_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (collection_id, manga_id)
);

-- Reverse lookup: which collections contain this manga? Used by the
-- "Add to collection" modal to pre-check the boxes for the user's
-- collections this manga is already in.
CREATE INDEX collection_mangas_manga_idx ON collection_mangas (manga_id);