-- IG Sort Yellow v45 migration from v44
-- 기존 ig_media / ig_media_user_download 데이터를 보존하고 통계 이벤트 테이블만 추가합니다.

CREATE TABLE IF NOT EXISTS `ig_media_collect_event` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_key` char(40) NOT NULL COMMENT 'idempotent key for one pass/snapshot media event',
  `collector_id` varchar(80) NOT NULL DEFAULT '',
  `shortcode` varchar(64) NOT NULL,
  `ig_account_id` varchar(120) NOT NULL DEFAULT '',
  `account_name` varchar(255) NOT NULL DEFAULT '',
  `folder_name` varchar(255) NOT NULL DEFAULT '',
  `source_type` varchar(40) NOT NULL DEFAULT '' COMMENT 'explore/profile/reel/search/etc',
  `target_url` varchar(1024) NOT NULL DEFAULT '',
  `pass_id` varchar(120) NOT NULL DEFAULT '' COMMENT 'snapshot/pass/cycle id',
  `top_n` int(10) unsigned NOT NULL DEFAULT 0,
  `threshold_value` bigint(20) unsigned NOT NULL DEFAULT 0,
  `threshold_label` varchar(80) NOT NULL DEFAULT '',
  `rank_no` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'pass/snapshot rank only, not stable id',
  `rank_label` varchar(40) NOT NULL DEFAULT '',
  `rank_meaning` varchar(80) NOT NULL DEFAULT 'sort_rank_at_collection_time',
  `sort_field` varchar(40) NOT NULL DEFAULT '' COMMENT 'play_count/likes/comments/reposts/upload_at/etc',
  `sort_value` bigint(20) unsigned NOT NULL DEFAULT 0,
  `views` bigint(20) unsigned NOT NULL DEFAULT 0,
  `likes` bigint(20) unsigned NOT NULL DEFAULT 0,
  `comments` bigint(20) unsigned NOT NULL DEFAULT 0,
  `reposts` bigint(20) unsigned NOT NULL DEFAULT 0,
  `upload_at` datetime DEFAULT NULL,
  `collected_at` datetime DEFAULT NULL,
  `downloaded_at` datetime DEFAULT NULL,
  `filename_stem` varchar(255) NOT NULL DEFAULT '',
  `media_index` int(10) unsigned NOT NULL DEFAULT 0,
  `has_image` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `has_video` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `image_file_name` varchar(255) NOT NULL DEFAULT '',
  `video_file_name` varchar(255) NOT NULL DEFAULT '',
  `image_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `video_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `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 `uq_event_key` (`event_key`),
  KEY `idx_collector_date` (`collector_id`, `downloaded_at`, `id`),
  KEY `idx_collector_collect` (`collector_id`, `collected_at`, `id`),
  KEY `idx_sort_date` (`sort_field`, `downloaded_at`, `rank_no`),
  KEY `idx_shortcode` (`shortcode`),
  KEY `idx_upload_at` (`upload_at`),
  KEY `idx_rank` (`rank_no`),
  KEY `idx_views` (`views`),
  KEY `idx_likes` (`likes`),
  KEY `idx_comments` (`comments`),
  KEY `idx_reposts` (`reposts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
