








CREATE TABLE IF NOT EXISTS `albums` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` varchar(255) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `original_title` varchar(500) DEFAULT NULL,
  `slug` varchar(280) NOT NULL,
  `description` text DEFAULT NULL,
  `original_description` text DEFAULT NULL,
  `album_folder` char(16) DEFAULT NULL,
  `storage_id` int(10) unsigned DEFAULT NULL,
  `cover_file` varchar(255) DEFAULT NULL,
  `cover_width` smallint(5) unsigned DEFAULT NULL,
  `cover_height` smallint(5) unsigned DEFAULT NULL,
  `image_count` smallint(5) unsigned NOT NULL DEFAULT 0,
  `images` longtext DEFAULT NULL,
  `views` int(10) unsigned NOT NULL DEFAULT 0,
  `impressions` int(10) unsigned NOT NULL DEFAULT 0,
  `recent_views` double NOT NULL DEFAULT 0,
  `recent_impressions` double NOT NULL DEFAULT 0,
  `ctr_score` double NOT NULL DEFAULT 0,
  `likes` int(10) unsigned NOT NULL DEFAULT 0,
  `dislikes` int(10) unsigned NOT NULL DEFAULT 0,
  `favorites_count` int(10) unsigned NOT NULL DEFAULT 0,
  `status` enum('draft','published','private','processing','error') NOT NULL DEFAULT 'draft',
  `video_type` enum('hetero','gay','hentai') NOT NULL DEFAULT 'hetero',
  `performer_name` varchar(100) DEFAULT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `user_id` int(10) unsigned DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  UNIQUE KEY `idx_external_id` (`external_id`),
  KEY `user_id` (`user_id`),
  KEY `idx_status_published` (`status`,`published_at` DESC),
  KEY `idx_status_views` (`status`,`views` DESC),
  KEY `idx_status_likes` (`status`,`likes` DESC),
  KEY `idx_status_created` (`status`,`created_at` DESC),
  KEY `idx_status_featured` (`status`,`is_featured`),
  KEY `idx_status_favorites` (`status`,`favorites_count` DESC),
  KEY `idx_status_ctr` (`status`,`ctr_score` DESC),
  KEY `idx_status_pub_id` (`status`,`published_at` DESC,`id` DESC),
  KEY `idx_video_type` (`video_type`),
  KEY `idx_type_status_published` (`video_type`,`status`,`published_at` DESC),
  KEY `idx_type_status_views` (`video_type`,`status`,`views` DESC),
  KEY `idx_type_status_ctr` (`video_type`,`status`,`ctr_score` DESC),
  KEY `idx_type_status_likes` (`video_type`,`status`,`likes` DESC),
  KEY `idx_type_status_favorites` (`video_type`,`status`,`favorites_count` DESC),
  KEY `idx_type_status_pub_id` (`video_type`,`status`,`published_at` DESC,`id` DESC),
  KEY `idx_storage` (`storage_id`),
  KEY `idx_created` (`created_at`),
  FULLTEXT KEY `idx_fulltext` (`title`,`description`),
  CONSTRAINT `albums_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `album_categories` (
  `album_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`album_id`,`category_id`),
  KEY `idx_category_album` (`category_id`,`album_id`),
  CONSTRAINT `album_categories_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `album_tags` (
  `album_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`album_id`,`tag_id`),
  KEY `idx_tag_album` (`tag_id`,`album_id`),
  CONSTRAINT `album_tags_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `album_performers` (
  `album_id` int(10) unsigned NOT NULL,
  `performer_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`album_id`,`performer_id`),
  KEY `idx_performer_album` (`performer_id`,`album_id`),
  CONSTRAINT `album_performers_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `album_channels` (
  `album_id` int(10) unsigned NOT NULL,
  `channel_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`album_id`,`channel_id`),
  KEY `idx_channel_album` (`channel_id`,`album_id`),
  CONSTRAINT `album_channels_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `album_translations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `album_id` int(10) unsigned NOT NULL,
  `lang_code` varchar(5) NOT NULL,
  `title` varchar(500) DEFAULT NULL,
  `slug` varchar(500) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_album_lang` (`album_id`,`lang_code`),
  UNIQUE KEY `idx_lang_slug` (`lang_code`,`slug`),
  CONSTRAINT `album_translations_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `album_view_log` (
  `album_id` int(10) unsigned NOT NULL,
  `viewer_hash` binary(16) NOT NULL,
  `viewed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`album_id`,`viewer_hash`),
  KEY `idx_viewed_at` (`viewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `album_favorites` (
  `user_id` int(10) unsigned NOT NULL,
  `album_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`user_id`,`album_id`),
  KEY `album_id` (`album_id`),
  KEY `idx_user_created` (`user_id`,`created_at` DESC),
  CONSTRAINT `album_favorites_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `album_favorites_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





ALTER TABLE `categories` ADD COLUMN `album_count` int(10) unsigned NOT NULL DEFAULT 0;
ALTER TABLE `tags`       ADD COLUMN `album_count` int(10) unsigned NOT NULL DEFAULT 0;
ALTER TABLE `performers` ADD COLUMN `album_count` int(10) unsigned NOT NULL DEFAULT 0;
ALTER TABLE `channels`   ADD COLUMN `album_count` int(10) unsigned NOT NULL DEFAULT 0;
