-- sort server MySQL v46 additive migration
-- prerequisite: migration_v45_from_yellow_v44.sql
-- 목적: 전체/필터/Top/개별/대본요청 다운로드의 공통 MEDIA_SAVED 완료 기록
-- 주의: 서버에는 로컬 절대경로 및 MP4/JPG 원본을 저장하지 않는다.

CREATE TABLE IF NOT EXISTS `collector_download_batches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `member_id` bigint unsigned NOT NULL,
  `device_id` bigint unsigned NOT NULL,
  `batch_key` varchar(80) NOT NULL,
  `source_mode` varchar(24) NOT NULL,
  `account_id` bigint unsigned DEFAULT NULL,
  `sort_by` varchar(40) NOT NULL DEFAULT '',
  `filter_days` int unsigned DEFAULT NULL,
  `requested_count` int unsigned NOT NULL DEFAULT 0,
  `batch_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `metadata_json` longtext,
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `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_download_batch_device_key` (`device_id`,`batch_key`),
  KEY `idx_download_batch_member_mode` (`member_id`,`source_mode`,`batch_status`,`id`),
  CONSTRAINT `fk_download_batch_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_download_batch_device` FOREIGN KEY (`device_id`) REFERENCES `collector_devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_download_batch_account` FOREIGN KEY (`account_id`) REFERENCES `collector_social_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_download_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned DEFAULT NULL,
  `post_id` bigint unsigned NOT NULL,
  `source_mode` varchar(24) NOT NULL,
  `asset_kind` varchar(20) NOT NULL,
  `media_index` int unsigned NOT NULL DEFAULT 0,
  `rank_no` int unsigned DEFAULT NULL,
  `source_url` varchar(2048) NOT NULL DEFAULT '',
  `expected_file_name` varchar(255) NOT NULL DEFAULT '',
  `job_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `requested_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  `last_error` varchar(1000) NOT NULL DEFAULT '',
  `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_download_job_batch_asset` (`batch_id`,`post_id`,`asset_kind`,`media_index`),
  KEY `idx_download_job_status` (`job_status`,`source_mode`,`id`),
  KEY `idx_download_job_post` (`post_id`,`asset_kind`,`media_index`,`id`),
  CONSTRAINT `fk_download_job_batch` FOREIGN KEY (`batch_id`) REFERENCES `collector_download_batches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_download_job_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_media_saved_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `member_id` bigint unsigned NOT NULL,
  `device_id` bigint unsigned NOT NULL,
  `client_event_id` varchar(80) NOT NULL,
  `download_job_id` bigint unsigned DEFAULT NULL,
  `post_id` bigint unsigned NOT NULL,
  `asset_id` bigint unsigned DEFAULT NULL,
  `source_mode` varchar(24) NOT NULL,
  `asset_kind` varchar(20) NOT NULL,
  `media_index` int unsigned NOT NULL DEFAULT 0,
  `rank_no` int unsigned DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` bigint unsigned NOT NULL DEFAULT 0,
  `md5` char(32) NOT NULL DEFAULT '',
  `sha256` char(64) NOT NULL DEFAULT '',
  `event_status` varchar(20) NOT NULL DEFAULT 'ACCEPTED',
  `completed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_media_saved_device_event` (`device_id`,`client_event_id`),
  KEY `idx_media_saved_post_time` (`post_id`,`completed_at`,`id`),
  KEY `idx_media_saved_member_mode` (`member_id`,`source_mode`,`completed_at`,`id`),
  KEY `idx_media_saved_hash` (`sha256`,`file_size`,`id`),
  CONSTRAINT `fk_media_saved_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_media_saved_device` FOREIGN KEY (`device_id`) REFERENCES `collector_devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_media_saved_job` FOREIGN KEY (`download_job_id`) REFERENCES `collector_download_jobs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_media_saved_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_media_saved_asset` FOREIGN KEY (`asset_id`) REFERENCES `collector_asset_sources` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
