-- Bookmark collections (private "folders" for organising user_bookmark).
--
-- Model: one media → at most one collection (Pinterest "boards" style).
-- `collection_id IS NULL` on `user_bookmark` means "unclassified favourite"
-- — there's no row in `bookmark_collection` for that pseudo-bucket; the
-- application surfaces it as a virtual collection when listing.
--
-- Why this shape:
--   - `position` orders the user's collections in their UI (drag & drop).
--   - `media_count` is a denormalised counter maintained applicatively by
--     `BookmarkService` in the same tx as the user_bookmark INSERT / UPDATE
--     / DELETE — same pattern as `media_stats.comments_count`. Triggers
--     would have been brittle around moves between collections.
--   - `cover_media_id` lets the user pin a specific cover; when NULL the
--     serializer falls back to the most recently added media of the
--     collection (cheap lookup via the new `idx_ub_collection_created`).
--   - `(user_id, name)` UNIQUE so a user can't have two "Voyage 2026"
--     collections (rename is the right answer if they want a similar name).

CREATE TABLE `bookmark_collection` (
    `id`             BINARY(16)   NOT NULL,
    `user_id`        BINARY(16)   NOT NULL,
    `name`           VARCHAR(80)  NOT NULL,
    `position`       INT UNSIGNED NOT NULL DEFAULT 0,
    `media_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `cover_media_id` BINARY(16)   NULL,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NULL,
    PRIMARY KEY (`id`),
    -- "List my collections in display order" + tiebreaker by id.
    KEY `idx_bc_user_position` (`user_id`, `position`, `id`),
    -- Anti-duplicate on name within the same user (rename instead).
    UNIQUE KEY `uq_bc_user_name` (`user_id`, `name`),
    CONSTRAINT `fk_bc_user`  FOREIGN KEY (`user_id`)        REFERENCES `user`(`id`)  ON DELETE CASCADE,
    -- ON DELETE SET NULL: if the pinned cover media is deleted, the
    -- collection survives and falls back to "most recent" rendering.
    CONSTRAINT `fk_bc_cover` FOREIGN KEY (`cover_media_id`) REFERENCES `media`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend user_bookmark with a (nullable) collection pointer + supporting
-- indexes for the two hot listings:
--   1. "all my bookmarks newest first"
--   2. "contents of collection X newest first"
--
-- ON DELETE SET NULL on the collection FK so dropping a collection moves
-- its bookmarks to "unclassified" rather than losing them.
ALTER TABLE `user_bookmark`
    ADD COLUMN `collection_id` BINARY(16) NULL AFTER `media_id`,
    ADD KEY `idx_ub_user_created`            (`user_id`, `created_at`),
    ADD KEY `idx_ub_user_collection_created` (`user_id`, `collection_id`, `created_at`),
    ADD CONSTRAINT `fk_ub_collection` FOREIGN KEY (`collection_id`)
        REFERENCES `bookmark_collection`(`id`) ON DELETE SET NULL;
