-- Create tables for shared playlists feature
-- Run this SQL directly on the database

-- shared_playlists table
CREATE TABLE IF NOT EXISTS `shared_playlists` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `playlist_id` INT(11) UNSIGNED NOT NULL COMMENT 'Reference to playlists.id',
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT 'Owner user ID',
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `share_code` VARCHAR(32) NOT NULL COMMENT 'Unique code for sharing',
  `item_count` INT(11) NOT NULL DEFAULT 0,
  `view_count` INT(11) NOT NULL DEFAULT 0,
  `copy_count` INT(11) NOT NULL DEFAULT 0 COMMENT 'Times copied by others',
  `average_rating` DECIMAL(3,2) NOT NULL DEFAULT 0.00,
  `rating_count` INT(11) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Owner can unshare',
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `share_code` (`share_code`),
  KEY `playlist_id` (`playlist_id`),
  KEY `user_id` (`user_id`),
  KEY `rating_sort` (`average_rating`, `rating_count`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- playlist_ratings table
CREATE TABLE IF NOT EXISTS `playlist_ratings` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shared_playlist_id` INT(11) UNSIGNED NOT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `rating` TINYINT(1) UNSIGNED NOT NULL COMMENT '1-5 star rating',
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_playlist` (`shared_playlist_id`, `user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- shared_playlist_items table - snapshot of items
CREATE TABLE IF NOT EXISTS `shared_playlist_items` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shared_playlist_id` INT(11) UNSIGNED NOT NULL,
  `item_type` ENUM('lesson', 'youtube') NOT NULL DEFAULT 'lesson',
  `lesson_id` INT(11) NULL,
  `youtube_video_id` VARCHAR(20) NULL,
  `youtube_title` VARCHAR(255) NULL,
  `youtube_thumbnail` VARCHAR(500) NULL,
  `youtube_channel_name` VARCHAR(100) NULL,
  `youtube_channel_slug` VARCHAR(100) NULL,
  `youtube_video_slug` VARCHAR(255) NULL,
  `position` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `shared_playlist_id` (`shared_playlist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
