CREATE TABLE `novel_reading_progress` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `novel_source_id` BIGINT NOT NULL,
  `chapter_id` BIGINT NOT NULL,
  `page_index` INT NOT NULL DEFAULT 0,
  `page_count` INT NOT NULL DEFAULT 1,
  `progress_percent` DECIMAL(6, 3) NULL,
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `novel_reading_progress_user_id_novel_source_id_key` (`user_id`, `novel_source_id`),
  KEY `novel_reading_progress_user_id_updated_at_idx` (`user_id`, `updated_at`),
  KEY `novel_reading_progress_novel_source_id_idx` (`novel_source_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `novel_bookmarks` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `novel_source_id` BIGINT NOT NULL,
  `chapter_id` BIGINT NOT NULL,
  `page_index` INT NOT NULL DEFAULT 0,
  `title` VARCHAR(255) NULL,
  `note_text` TEXT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `novel_bookmarks_user_id_novel_source_id_created_at_idx` (`user_id`, `novel_source_id`, `created_at`),
  KEY `novel_bookmarks_chapter_id_idx` (`chapter_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `novel_annotations` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `novel_source_id` BIGINT NOT NULL,
  `chapter_id` BIGINT NOT NULL,
  `page_index` INT NOT NULL DEFAULT 0,
  `selected_text` TEXT NOT NULL,
  `note_text` TEXT NULL,
  `color` VARCHAR(30) NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `novel_annotations_user_id_novel_source_id_created_at_idx` (`user_id`, `novel_source_id`, `created_at`),
  KEY `novel_annotations_chapter_id_idx` (`chapter_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;