-- IG Sort Yellow v45 reset schema
-- 주의: 기존 ig_media / ig_media_user_download / ig_media_collect_event 데이터를 모두 삭제하고 새 3테이블 구조로 시작합니다.
-- 적용 전 백업이 필요하면 반드시 먼저 백업하세요.

DROP TABLE IF EXISTS `ig_media_collect_event`;
DROP TABLE IF EXISTS `ig_media_user_download`;
DROP TABLE IF EXISTS `ig_media`;

CREATE TABLE `ig_media` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shortcode` varchar(64) NOT NULL COMMENT 'Instagram post/reel/tv shortcode',
  `media_url` varchar(1024) NOT NULL DEFAULT '',
  `account_name` varchar(255) NOT NULL DEFAULT '' COMMENT '표시용 인스타 계정명',
  `ig_account_id` varchar(120) NOT NULL DEFAULT '' COMMENT '수집 대상 인스타 ID',
  `upload_at` datetime DEFAULT NULL,
  `thumbnail_url` varchar(1024) NOT NULL DEFAULT '',
  `video_url` varchar(1024) NOT NULL DEFAULT '',
  `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,
  `first_collector_id` varchar(80) NOT NULL DEFAULT '' COMMENT '최초 수집 사용자/PC/작업자 ID',
  `first_collected_at` datetime DEFAULT NULL,
  `last_collected_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 `uq_shortcode` (`shortcode`),
  KEY `idx_ig_account_id_id` (`ig_account_id`, `id`),
  KEY `idx_account_name_id` (`account_name`, `id`),
  KEY `idx_upload_at` (`upload_at`),
  KEY `idx_last_collected_at` (`last_collected_at`),
  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;

CREATE TABLE `ig_media_user_download` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `collector_id` varchar(80) NOT NULL DEFAULT '' COMMENT '수집 사용자/PC/작업자 ID',
  `shortcode` varchar(64) NOT NULL,
  `ig_account_id` varchar(120) NOT NULL DEFAULT '' COMMENT '수집 대상 인스타 ID',
  `save_folder` varchar(1024) NOT NULL DEFAULT '' COMMENT '사용자 실제 저장 폴더',
  `folder_name` varchar(255) NOT NULL DEFAULT '' COMMENT '표시용 폴더명',
  `image_file_name` varchar(255) NOT NULL DEFAULT '',
  `image_file_path` varchar(1024) NOT NULL DEFAULT '',
  `image_md5` char(32) NOT NULL DEFAULT '',
  `image_sha256` char(64) NOT NULL DEFAULT '',
  `image_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `image_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, done, failed, skipped',
  `video_file_name` varchar(255) NOT NULL DEFAULT '',
  `video_file_path` varchar(1024) NOT NULL DEFAULT '',
  `video_md5` char(32) NOT NULL DEFAULT '',
  `video_sha256` char(64) NOT NULL DEFAULT '',
  `video_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `video_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, done, failed, skipped',
  `download_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, partial, complete, failed',
  `last_error` varchar(1024) NOT NULL DEFAULT '',
  `attempt_count` int(10) unsigned NOT NULL DEFAULT 0,
  `first_started_at` datetime DEFAULT NULL,
  `image_downloaded_at` datetime DEFAULT NULL,
  `video_downloaded_at` datetime DEFAULT NULL,
  `completed_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 `uq_user_shortcode` (`collector_id`, `shortcode`),
  KEY `idx_user_ig_status` (`collector_id`, `ig_account_id`, `download_status`, `id`),
  KEY `idx_shortcode` (`shortcode`),
  KEY `idx_ig_account_id_id` (`ig_account_id`, `id`),
  KEY `idx_download_status` (`download_status`),
  KEY `idx_image_hash` (`image_md5`, `image_sha256`),
  KEY `idx_video_hash` (`video_md5`, `video_sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `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;
