-- Migration: user collections (personal sticker folders with pin support)

-- --------------------------------------------------------
-- 1. collection
-- --------------------------------------------------------
CREATE TABLE `collection` (
  `id`        INT NOT NULL AUTO_INCREMENT,
  `user_id`   INT NOT NULL,
  `name`      VARCHAR(100) NOT NULL,
  `isPinned`  TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `collection_user_idx` (`user_id`),
  CONSTRAINT `collection_user_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 2. collection_sticker  (many-to-many: collection ↔ sticker)
-- --------------------------------------------------------
CREATE TABLE `collection_sticker` (
  `id`            INT NOT NULL AUTO_INCREMENT,
  `collection_id` INT NOT NULL,
  `sticker_id`    INT NOT NULL,
  `createdAt`     TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `collection_sticker_UQ` (`collection_id`, `sticker_id`),
  KEY `collection_sticker_sticker_idx` (`sticker_id`),
  CONSTRAINT `collection_sticker_collection_fk`
    FOREIGN KEY (`collection_id`) REFERENCES `collection` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `collection_sticker_sticker_fk`
    FOREIGN KEY (`sticker_id`) REFERENCES `sticker` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;