CREATE TABLE `hit_analysis_cases` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `source_platform` VARCHAR(100) NULL,
  `source_url` VARCHAR(500) NULL,
  `content_type` VARCHAR(80) NOT NULL DEFAULT 'short_drama',
  `genre` VARCHAR(100) NULL,
  `language` VARCHAR(30) NOT NULL DEFAULT 'zh-CN',
  `target_audience` VARCHAR(255) NULL,
  `duration_seconds` INT NULL,
  `episode_count` INT NULL,
  `tags_json` JSON NULL,
  `metrics_json` JSON NULL,
  `transcript_text` LONGTEXT NULL,
  `summary_text` TEXT NULL,
  `analysis_json` JSON NULL,
  `diagnosis_score` DECIMAL(5, 2) NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'draft',
  `created_by_user_id` BIGINT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `hit_analysis_cases_source_platform_idx`(`source_platform`),
  INDEX `hit_analysis_cases_genre_status_idx`(`genre`, `status`),
  INDEX `hit_analysis_cases_status_diagnosis_score_idx`(`status`, `diagnosis_score`),
  INDEX `hit_analysis_cases_created_at_idx`(`created_at`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `hit_analysis_segments` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `case_id` BIGINT NOT NULL,
  `segment_no` INT NOT NULL,
  `start_second` INT NULL,
  `end_second` INT NULL,
  `scene_type` VARCHAR(80) NULL,
  `hook_type` VARCHAR(100) NULL,
  `emotion` VARCHAR(80) NULL,
  `conflict_type` VARCHAR(100) NULL,
  `plot_function` VARCHAR(120) NULL,
  `visual_strategy` VARCHAR(120) NULL,
  `dialogue_pattern` VARCHAR(120) NULL,
  `camera_notes` TEXT NULL,
  `importance_score` INT NULL,
  `emotion_score` INT NULL,
  `action_score` INT NULL,
  `tags_json` JSON NULL,
  `summary_text` TEXT NULL,
  `prompt_seed` TEXT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `hit_analysis_segments_case_id_segment_no_key`(`case_id`, `segment_no`),
  INDEX `hit_analysis_segments_case_id_idx`(`case_id`),
  INDEX `hit_analysis_segments_scene_type_idx`(`scene_type`),
  INDEX `hit_analysis_segments_hook_type_idx`(`hook_type`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `creative_patterns` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `source_case_id` BIGINT NULL,
  `pattern_type` VARCHAR(80) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `genre` VARCHAR(100) NULL,
  `language` VARCHAR(30) NOT NULL DEFAULT 'zh-CN',
  `description` TEXT NULL,
  `structure_json` JSON NULL,
  `prompt_template` TEXT NULL,
  `negative_prompt` TEXT NULL,
  `tags_json` JSON NULL,
  `usage_count` INT NOT NULL DEFAULT 0,
  `effectiveness_score` DECIMAL(5, 2) NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'active',
  `created_by_user_id` BIGINT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `creative_patterns_pattern_type_status_idx`(`pattern_type`, `status`),
  INDEX `creative_patterns_genre_status_idx`(`genre`, `status`),
  INDEX `creative_patterns_source_case_id_idx`(`source_case_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;