-- sort server MySQL additive schema v45
-- 기준: 기존 yellow v44의 ig_media / ig_media_user_download는 삭제·변경하지 않는다.
-- 실제 MP4/JPG 파일과 사용자 로컬 절대경로는 서버에 저장하지 않는다.
-- 서버에는 수집자, 권한, 최신 통계, URL, 대본, 연구자료, 장치별 보유 인덱스만 저장한다.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS `collector_members` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `collector_code` varchar(8) NOT NULL COMMENT '현재 발급은 영문대문자+숫자 4자리, 향후 확장 가능',
  `display_name` varchar(120) NOT NULL DEFAULT '',
  `status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  `point_balance_cache` bigint NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_seen_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_collector_code` (`collector_code`),
  KEY `idx_collector_status_seen` (`status`,`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_devices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `member_id` bigint unsigned NOT NULL,
  `computer_id_hash` char(64) NOT NULL,
  `device_name` varchar(120) NOT NULL DEFAULT '',
  `device_public_key` text,
  `token_hash` char(64) NOT NULL DEFAULT '',
  `token_version` int unsigned NOT NULL DEFAULT 0,
  `token_expires_at` datetime DEFAULT NULL,
  `p2p_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `max_uploads` int unsigned NOT NULL DEFAULT 1,
  `status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  `last_seen_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_member_computer` (`member_id`,`computer_id_hash`),
  KEY `idx_device_online` (`p2p_enabled`,`status`,`last_seen_at`),
  CONSTRAINT `fk_device_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_groups` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `group_code` varchar(12) NOT NULL,
  `group_name` varchar(160) NOT NULL,
  `introduction` text,
  `promotion_text` text,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `join_policy` varchar(20) NOT NULL DEFAULT 'APPROVAL',
  `status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  `owner_member_id` bigint unsigned NOT 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_group_code` (`group_code`),
  KEY `idx_group_visibility_status` (`visibility`,`status`,`id`),
  CONSTRAINT `fk_group_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `collector_members` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_group_members` (
  `group_id` bigint unsigned NOT NULL,
  `member_id` bigint unsigned NOT NULL,
  `role` varchar(20) NOT NULL DEFAULT 'MEMBER',
  `member_status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  `joined_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`group_id`,`member_id`),
  KEY `idx_group_role_status` (`group_id`,`role`,`member_status`),
  KEY `idx_member_groups` (`member_id`,`member_status`,`group_id`),
  CONSTRAINT `fk_group_member_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_group_member_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_group_join_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `group_id` bigint unsigned NOT NULL,
  `member_id` bigint unsigned NOT NULL,
  `request_message` varchar(1000) NOT NULL DEFAULT '',
  `request_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `decided_by_member_id` bigint unsigned DEFAULT NULL,
  `decided_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_group_join_open` (`group_id`,`member_id`,`request_status`),
  KEY `idx_join_status` (`group_id`,`request_status`,`id`),
  CONSTRAINT `fk_join_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_join_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_join_decider` FOREIGN KEY (`decided_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_social_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `platform` varchar(24) NOT NULL DEFAULT 'instagram',
  `platform_account_id` varchar(160) NOT NULL DEFAULT '',
  `username` varchar(160) NOT NULL,
  `display_name` varchar(255) NOT NULL DEFAULT '',
  `profile_url` varchar(1024) 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_platform_username` (`platform`,`username`),
  KEY `idx_platform_account_id` (`platform`,`platform_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_account_lists` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `member_id` bigint unsigned NOT NULL,
  `account_id` bigint unsigned NOT NULL,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `memo` text,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `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_member_account` (`member_id`,`account_id`),
  KEY `idx_account_list_visibility` (`member_id`,`visibility`,`active`,`id`),
  CONSTRAINT `fk_account_list_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_account_list_account` FOREIGN KEY (`account_id`) REFERENCES `collector_social_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_account_group_visibility` (
  `account_list_id` bigint unsigned NOT NULL,
  `group_id` bigint unsigned NOT NULL,
  `granted_by_member_id` bigint unsigned NOT NULL,
  `granted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_list_id`,`group_id`),
  KEY `idx_group_visible_accounts` (`group_id`,`account_list_id`),
  CONSTRAINT `fk_account_visibility_list` FOREIGN KEY (`account_list_id`) REFERENCES `collector_account_lists` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_account_visibility_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_account_visibility_granter` FOREIGN KEY (`granted_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_collection_profiles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `member_id` bigint unsigned NOT NULL,
  `profile_name` varchar(160) NOT NULL,
  `lookback_days` int unsigned NOT NULL DEFAULT 7,
  `sort_by` varchar(40) NOT NULL DEFAULT 'views',
  `observation_limit` int unsigned NOT NULL DEFAULT 100,
  `download_policy` varchar(30) NOT NULL DEFAULT 'NONE',
  `download_limit` int unsigned NOT NULL DEFAULT 0,
  `transcript_policy` varchar(30) NOT NULL DEFAULT 'ON_DEMAND',
  `filter_json` longtext,
  `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_member_profile_name` (`member_id`,`profile_name`),
  CONSTRAINT `fk_collection_profile_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_account_rules` (
  `account_list_id` bigint unsigned NOT NULL,
  `collection_profile_id` bigint unsigned DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `last_collected_at` datetime DEFAULT NULL,
  `next_collect_at` datetime DEFAULT NULL,
  `last_result` varchar(20) NOT NULL DEFAULT 'UNKNOWN',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_list_id`),
  KEY `idx_account_rules_due` (`enabled`,`next_collect_at`,`account_list_id`),
  CONSTRAINT `fk_account_rule_list` FOREIGN KEY (`account_list_id`) REFERENCES `collector_account_lists` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_account_rule_profile` FOREIGN KEY (`collection_profile_id`) REFERENCES `collector_collection_profiles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `account_id` bigint unsigned DEFAULT NULL,
  `platform` varchar(24) NOT NULL DEFAULT 'instagram',
  `shortcode` varchar(80) NOT NULL,
  `post_url` varchar(1024) NOT NULL,
  `caption` longtext,
  `published_at` datetime DEFAULT NULL,
  `thumbnail_url` varchar(2048) NOT NULL DEFAULT '',
  `video_url` varchar(2048) NOT NULL DEFAULT '',
  `source_url_observed_at` datetime DEFAULT NULL,
  `first_collector_member_id` bigint unsigned DEFAULT NULL,
  `first_observed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_observed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `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_platform_shortcode` (`platform`,`shortcode`),
  KEY `idx_posts_account_published` (`account_id`,`published_at`,`id`),
  KEY `idx_posts_last_observed` (`last_observed_at`,`id`),
  CONSTRAINT `fk_post_account` FOREIGN KEY (`account_id`) REFERENCES `collector_social_accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_post_first_collector` FOREIGN KEY (`first_collector_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_post_stat_latest` (
  `post_id` bigint unsigned NOT NULL,
  `views` bigint unsigned NOT NULL DEFAULT 0,
  `likes` bigint unsigned NOT NULL DEFAULT 0,
  `comments` bigint unsigned NOT NULL DEFAULT 0,
  `reposts` bigint unsigned NOT NULL DEFAULT 0,
  `collected_by_member_id` bigint unsigned DEFAULT NULL,
  `collected_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`post_id`),
  KEY `idx_latest_views` (`views`,`post_id`),
  KEY `idx_latest_likes` (`likes`,`post_id`),
  KEY `idx_latest_comments` (`comments`,`post_id`),
  CONSTRAINT `fk_latest_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_latest_collector` FOREIGN KEY (`collected_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_post_stat_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `views` bigint unsigned NOT NULL DEFAULT 0,
  `likes` bigint unsigned NOT NULL DEFAULT 0,
  `comments` bigint unsigned NOT NULL DEFAULT 0,
  `reposts` bigint unsigned NOT NULL DEFAULT 0,
  `collected_by_member_id` bigint unsigned DEFAULT NULL,
  `collected_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_post_stat_time` (`post_id`,`collected_at`),
  KEY `idx_post_stat_history_time` (`post_id`,`collected_at`,`id`),
  CONSTRAINT `fk_stat_history_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_stat_history_member` FOREIGN KEY (`collected_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_asset_sources` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `asset_kind` varchar(20) NOT NULL,
  `media_index` int unsigned NOT NULL DEFAULT 0,
  `source_url` varchar(2048) NOT NULL DEFAULT '',
  `source_url_observed_at` datetime DEFAULT NULL,
  `file_size` bigint unsigned NOT NULL DEFAULT 0,
  `sha256` char(64) NOT NULL DEFAULT '',
  `has_audio` tinyint(1) 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_post_asset` (`post_id`,`asset_kind`,`media_index`),
  KEY `idx_asset_hash` (`sha256`,`file_size`),
  CONSTRAINT `fk_asset_source_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_asset_inventory` (
  `asset_id` bigint unsigned NOT NULL,
  `device_id` bigint unsigned NOT NULL,
  `member_id` bigint unsigned NOT NULL,
  `available` tinyint(1) NOT NULL DEFAULT 0,
  `share_scope` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `verified_at` datetime DEFAULT NULL,
  `last_seen_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 (`asset_id`,`device_id`),
  KEY `idx_inventory_available` (`asset_id`,`available`,`last_seen_at`),
  KEY `idx_inventory_member` (`member_id`,`available`,`asset_id`),
  CONSTRAINT `fk_inventory_asset` FOREIGN KEY (`asset_id`) REFERENCES `collector_asset_sources` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inventory_device` FOREIGN KEY (`device_id`) REFERENCES `collector_devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inventory_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_transcripts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `asset_id` bigint unsigned DEFAULT NULL,
  `language_code` varchar(20) NOT NULL DEFAULT '',
  `model_name` varchar(100) NOT NULL DEFAULT '',
  `model_version` varchar(100) NOT NULL DEFAULT '',
  `transcript_text` longtext,
  `segments_json` longtext,
  `status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned DEFAULT NULL,
  `created_by_member_id` bigint unsigned 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_transcript_variant` (`post_id`,`language_code`,`model_name`,`model_version`),
  KEY `idx_transcript_visibility` (`visibility`,`group_id`,`post_id`),
  CONSTRAINT `fk_transcript_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_transcript_asset` FOREIGN KEY (`asset_id`) REFERENCES `collector_asset_sources` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transcript_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transcript_member` FOREIGN KEY (`created_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_hook_segments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `transcript_id` bigint unsigned DEFAULT NULL,
  `start_ms` int unsigned NOT NULL DEFAULT 0,
  `end_ms` int unsigned NOT NULL DEFAULT 3000,
  `hook_text` text,
  `source_type` varchar(30) NOT NULL DEFAULT 'AUTO_SUGGESTED',
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned DEFAULT NULL,
  `confirmed_by_member_id` bigint unsigned 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`),
  KEY `idx_hook_post_time` (`post_id`,`start_ms`,`end_ms`),
  CONSTRAINT `fk_hook_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_hook_transcript` FOREIGN KEY (`transcript_id`) REFERENCES `collector_transcripts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_hook_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_hook_member` FOREIGN KEY (`confirmed_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_member_id` bigint unsigned NOT NULL,
  `source_category_id` bigint unsigned DEFAULT NULL,
  `parent_category_id` bigint unsigned DEFAULT NULL,
  `category_name` varchar(160) NOT NULL,
  `description` text,
  `category_type` varchar(30) NOT NULL DEFAULT 'GENERAL',
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned 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_owner_category` (`owner_member_id`,`category_name`,`category_type`),
  KEY `idx_category_public_search` (`visibility`,`category_type`,`category_name`),
  CONSTRAINT `fk_category_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_category_source` FOREIGN KEY (`source_category_id`) REFERENCES `collector_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_category_parent` FOREIGN KEY (`parent_category_id`) REFERENCES `collector_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_category_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_post_categories` (
  `post_id` bigint unsigned NOT NULL,
  `category_id` bigint unsigned NOT NULL,
  `assigned_by_member_id` bigint unsigned NOT NULL,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`post_id`,`category_id`,`assigned_by_member_id`),
  KEY `idx_post_category_search` (`category_id`,`visibility`,`group_id`,`post_id`),
  CONSTRAINT `fk_post_category_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_post_category_category` FOREIGN KEY (`category_id`) REFERENCES `collector_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_post_category_member` FOREIGN KEY (`assigned_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_post_category_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_tags` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_member_id` bigint unsigned NOT NULL,
  `tag_name` varchar(120) NOT NULL,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_owner_tag` (`owner_member_id`,`tag_name`),
  KEY `idx_tag_search` (`tag_name`,`visibility`,`group_id`),
  CONSTRAINT `fk_tag_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tag_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_post_tags` (
  `post_id` bigint unsigned NOT NULL,
  `tag_id` bigint unsigned NOT NULL,
  `assigned_by_member_id` bigint unsigned NOT NULL,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`post_id`,`tag_id`,`assigned_by_member_id`),
  KEY `idx_post_tag_search` (`tag_id`,`visibility`,`group_id`,`post_id`),
  CONSTRAINT `fk_post_tag_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_post_tag_tag` FOREIGN KEY (`tag_id`) REFERENCES `collector_tags` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_post_tag_member` FOREIGN KEY (`assigned_by_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_post_tag_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_notes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `author_member_id` bigint unsigned NOT NULL,
  `note_type` varchar(30) NOT NULL DEFAULT 'MEMO',
  `note_text` longtext,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned 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`),
  KEY `idx_notes_post_visibility` (`post_id`,`visibility`,`group_id`,`id`),
  CONSTRAINT `fk_note_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_note_author` FOREIGN KEY (`author_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_note_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_review_templates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_member_id` bigint unsigned NOT NULL,
  `template_name` varchar(160) NOT NULL,
  `description` text,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned 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_review_template_owner_name` (`owner_member_id`,`template_name`),
  CONSTRAINT `fk_review_template_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_template_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_review_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `template_id` bigint unsigned NOT NULL,
  `item_key` varchar(80) NOT NULL,
  `item_label` varchar(160) NOT NULL,
  `min_score` decimal(8,2) NOT NULL DEFAULT 1,
  `max_score` decimal(8,2) NOT NULL DEFAULT 5,
  `display_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_review_item_key` (`template_id`,`item_key`),
  CONSTRAINT `fk_review_item_template` FOREIGN KEY (`template_id`) REFERENCES `collector_review_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `template_id` bigint unsigned DEFAULT NULL,
  `author_member_id` bigint unsigned NOT NULL,
  `review_text` longtext,
  `overall_score` decimal(8,2) DEFAULT NULL,
  `prediction_score` decimal(8,2) DEFAULT NULL,
  `prediction_text` text,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned 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`),
  KEY `idx_reviews_post_visibility` (`post_id`,`visibility`,`group_id`,`id`),
  KEY `idx_reviews_author` (`author_member_id`,`id`),
  CONSTRAINT `fk_review_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_template` FOREIGN KEY (`template_id`) REFERENCES `collector_review_templates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_review_author` FOREIGN KEY (`author_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_review_scores` (
  `review_id` bigint unsigned NOT NULL,
  `review_item_id` bigint unsigned NOT NULL,
  `score` decimal(8,2) NOT NULL,
  PRIMARY KEY (`review_id`,`review_item_id`),
  CONSTRAINT `fk_review_score_review` FOREIGN KEY (`review_id`) REFERENCES `collector_reviews` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_score_item` FOREIGN KEY (`review_item_id`) REFERENCES `collector_review_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_comments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL,
  `author_member_id` bigint unsigned NOT NULL,
  `parent_comment_id` bigint unsigned DEFAULT NULL,
  `comment_type` varchar(30) NOT NULL DEFAULT 'OBSERVATION',
  `comment_text` longtext,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned 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`),
  KEY `idx_comments_post_visibility` (`post_id`,`visibility`,`group_id`,`id`),
  CONSTRAINT `fk_comment_post` FOREIGN KEY (`post_id`) REFERENCES `collector_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comment_author` FOREIGN KEY (`author_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comment_parent` FOREIGN KEY (`parent_comment_id`) REFERENCES `collector_comments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comment_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_aggregates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `owner_member_id` bigint unsigned NOT NULL,
  `aggregate_name` varchar(200) NOT NULL,
  `output_file_name` varchar(255) NOT NULL,
  `visibility` varchar(20) NOT NULL DEFAULT 'PRIVATE',
  `group_id` bigint unsigned DEFAULT NULL,
  `sort_by` varchar(40) NOT NULL DEFAULT 'views',
  `filter_days` int unsigned NOT NULL DEFAULT 7,
  `page_size` int unsigned NOT NULL DEFAULT 50,
  `limit_count` int unsigned NOT NULL DEFAULT 0,
  `filter_json` longtext,
  `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_aggregate_file` (`owner_member_id`,`output_file_name`),
  KEY `idx_aggregate_visibility` (`visibility`,`group_id`,`id`),
  CONSTRAINT `fk_aggregate_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_aggregate_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_aggregate_accounts` (
  `aggregate_id` bigint unsigned NOT NULL,
  `account_id` bigint unsigned NOT NULL,
  `display_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`aggregate_id`,`account_id`),
  CONSTRAINT `fk_aggregate_account_aggregate` FOREIGN KEY (`aggregate_id`) REFERENCES `collector_aggregates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_aggregate_account_account` FOREIGN KEY (`account_id`) REFERENCES `collector_social_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_tasks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `group_id` bigint unsigned DEFAULT NULL,
  `task_type` varchar(40) NOT NULL,
  `entity_type` varchar(40) NOT NULL,
  `entity_id` bigint unsigned NOT NULL,
  `task_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `lease_device_id` bigint unsigned DEFAULT NULL,
  `lease_member_id` bigint unsigned DEFAULT NULL,
  `lease_expires_at` datetime DEFAULT NULL,
  `heartbeat_at` datetime DEFAULT NULL,
  `attempt_count` int unsigned NOT NULL DEFAULT 0,
  `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`),
  KEY `idx_tasks_claim` (`task_status`,`task_type`,`lease_expires_at`,`id`),
  KEY `idx_tasks_group` (`group_id`,`task_status`,`id`),
  CONSTRAINT `fk_task_group` FOREIGN KEY (`group_id`) REFERENCES `collector_groups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_task_device` FOREIGN KEY (`lease_device_id`) REFERENCES `collector_devices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_task_member` FOREIGN KEY (`lease_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_sync_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,
  `entity_type` varchar(40) NOT NULL,
  `entity_id` bigint unsigned DEFAULT NULL,
  `operation` varchar(20) NOT NULL DEFAULT 'UPSERT',
  `event_status` varchar(20) NOT NULL DEFAULT 'ACCEPTED',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sync_client_event` (`device_id`,`client_event_id`),
  KEY `idx_sync_member_time` (`member_id`,`created_at`,`id`),
  CONSTRAINT `fk_sync_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sync_device` FOREIGN KEY (`device_id`) REFERENCES `collector_devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `collector_point_ledger` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `member_id` bigint unsigned NOT NULL,
  `amount` bigint NOT NULL,
  `reason_type` varchar(40) NOT NULL,
  `reference_type` varchar(40) NOT NULL DEFAULT '',
  `reference_id` bigint unsigned DEFAULT NULL,
  `transfer_peer_member_id` bigint unsigned DEFAULT NULL,
  `transaction_status` varchar(20) NOT NULL DEFAULT 'SETTLED',
  `balance_after` bigint DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_point_member_time` (`member_id`,`created_at`,`id`),
  KEY `idx_point_reference` (`reference_type`,`reference_id`,`id`),
  CONSTRAINT `fk_point_member` FOREIGN KEY (`member_id`) REFERENCES `collector_members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_point_peer` FOREIGN KEY (`transfer_peer_member_id`) REFERENCES `collector_members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
