content-find-agent-new/sql/content_agent_schema.sql

-- Content Agent V1 / V1.1 schema.
-- Target database: `content-deconstruction-supply`.
-- This file is safe to run repeatedly because every table uses IF NOT EXISTS.

SET NAMES utf8mb4;
USE `content-deconstruction-supply`;

CREATE TABLE IF NOT EXISTS content_agent_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  demand_content_id BIGINT UNSIGNED NULL,
  run_label VARCHAR(160) NULL,
  platform VARCHAR(32) NOT NULL DEFAULT 'douyin',
  platform_mode VARCHAR(32) NOT NULL,
  strategy_version VARCHAR(160) NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'created',
  current_step VARCHAR(80) NULL,
  validation_status VARCHAR(32) NULL,
  source_ref JSON NULL,
  error_code VARCHAR(120) NULL,
  error_message TEXT NULL,
  error_detail JSON NULL,
  started_at DATETIME(3) NULL,
  completed_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_content_agent_runs_run (run_id),
  KEY idx_content_agent_runs_schema_created_at (schema_version, created_at),
  KEY idx_content_agent_runs_demand_content_id (demand_content_id),
  KEY idx_content_agent_runs_status_created_at (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_source_contexts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  demand_content_id BIGINT UNSIGNED NULL,
  pattern_source_system VARCHAR(80) NULL,
  source_kind VARCHAR(80) NULL,
  source_post_id VARCHAR(120) NULL,
  pattern_execution_id BIGINT NULL,
  mining_config_id BIGINT NULL,
  evidence_pack JSON NOT NULL,
  source_context JSON NOT NULL,
  raw_demand_content JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_content_agent_source_contexts_run (run_id),
  KEY idx_content_agent_source_contexts_demand_content_id (demand_content_id),
  KEY idx_content_agent_source_contexts_pattern (pattern_source_system, pattern_execution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_pattern_seed_packs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  source_post_id VARCHAR(120) NULL,
  pattern_execution_id BIGINT NULL,
  itemset_ids JSON NULL,
  seed_terms JSON NULL,
  pattern_seed_pack JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_pattern_seed_run_policy (run_id, policy_run_id),
  KEY idx_content_agent_pattern_seed_packs_pattern (pattern_execution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_queries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  search_query_id VARCHAR(80) NOT NULL,
  search_query VARCHAR(512) NOT NULL,
  search_query_generation_method VARCHAR(80) NULL,
  discovery_start_source VARCHAR(80) NULL,
  previous_discovery_step VARCHAR(80) NULL,
  search_query_effect_status VARCHAR(32) NULL,
  pattern_seed_ref JSON NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_queries_run_policy_query (run_id, policy_run_id, search_query_id),
  KEY idx_content_agent_queries_effect_status (search_query_effect_status),
  KEY idx_content_agent_queries_text (search_query(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_discovered_content_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  content_discovery_id VARCHAR(80) NOT NULL,
  search_query_id VARCHAR(80) NOT NULL,
  platform VARCHAR(32) NOT NULL,
  platform_content_id VARCHAR(120) NOT NULL,
  platform_content_format VARCHAR(40) NULL,
  platform_content_url TEXT NULL,
  description TEXT NULL,
  platform_author_id VARCHAR(256) NULL,
  author_display_name VARCHAR(256) NULL,
  discovery_start_source VARCHAR(80) NULL,
  previous_discovery_step VARCHAR(80) NULL,
  statistics JSON NULL,
  tags JSON NULL,
  text_extra JSON NULL,
  source_evidence JSON NULL,
  pattern_match_result JSON NULL,
  platform_raw_payload JSON NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_items_run_policy_discovery (run_id, policy_run_id, content_discovery_id),
  UNIQUE KEY uk_ca_items_run_policy_content (run_id, policy_run_id, platform, platform_content_id),
  KEY idx_ca_items_run_policy_query (run_id, policy_run_id, search_query_id),
  KEY idx_content_agent_content_items_author (platform_author_id(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_content_media_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  platform VARCHAR(32) NOT NULL,
  platform_content_id VARCHAR(120) NOT NULL,
  content_media_status VARCHAR(64) NOT NULL,
  content_metadata_source VARCHAR(128) NULL,
  play_url TEXT NULL,
  local_path TEXT NULL,
  oss_url TEXT NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_media_run_policy_content (run_id, policy_run_id, platform, platform_content_id),
  KEY idx_content_agent_media_records_status (content_media_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_rule_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  decision_id VARCHAR(80) NOT NULL,
  policy_bundle_id VARCHAR(160) NULL,
  rule_pack_id VARCHAR(160) NULL,
  rule_pack_version VARCHAR(160) NULL,
  strategy_version VARCHAR(160) NULL,
  decision_target_type VARCHAR(64) NOT NULL,
  decision_target_id VARCHAR(160) NOT NULL,
  decision_action VARCHAR(64) NOT NULL,
  decision_reason_code VARCHAR(160) NULL,
  search_query_effect_status VARCHAR(32) NULL,
  score DECIMAL(12,4) NULL,
  triggered_blocking_rules JSON NULL,
  scorecard JSON NULL,
  source_evidence JSON NULL,
  decision_replay_data JSON NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_rule_run_policy_decision (run_id, policy_run_id, decision_id),
  UNIQUE KEY uk_ca_rule_run_policy_target (run_id, policy_run_id, decision_target_type, decision_target_id),
  KEY idx_content_agent_rule_decisions_action (decision_action),
  KEY idx_content_agent_rule_decisions_reason (decision_reason_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_source_path_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  source_path_record_id VARCHAR(80) NOT NULL,
  source_path_type VARCHAR(80) NOT NULL,
  from_node_type VARCHAR(80) NOT NULL,
  from_node_id VARCHAR(160) NOT NULL,
  to_node_type VARCHAR(80) NOT NULL,
  to_node_id VARCHAR(160) NOT NULL,
  decision_id VARCHAR(80) NULL,
  rule_pack_id VARCHAR(160) NULL,
  discovery_start_source VARCHAR(80) NULL,
  previous_discovery_step VARCHAR(80) NULL,
  origin_path_id VARCHAR(240) NULL,
  source_evidence_ref VARCHAR(240) NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_paths_run_policy_path (run_id, policy_run_id, source_path_record_id),
  KEY idx_ca_paths_from (run_id, policy_run_id, from_node_type, from_node_id),
  KEY idx_ca_paths_to (run_id, policy_run_id, to_node_type, to_node_id),
  KEY idx_ca_paths_decision (run_id, policy_run_id, decision_id),
  KEY idx_content_agent_source_paths_type (source_path_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_search_clues (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  clue_id VARCHAR(80) NOT NULL,
  search_query_id VARCHAR(80) NOT NULL,
  search_query VARCHAR(512) NOT NULL,
  discovery_start_source VARCHAR(80) NULL,
  previous_discovery_step VARCHAR(80) NULL,
  result_count INT UNSIGNED NOT NULL DEFAULT 0,
  pooled_content_count INT UNSIGNED NOT NULL DEFAULT 0,
  review_content_count INT UNSIGNED NOT NULL DEFAULT 0,
  pending_content_count INT UNSIGNED NOT NULL DEFAULT 0,
  rejected_content_count INT UNSIGNED NOT NULL DEFAULT 0,
  search_query_effect_status VARCHAR(32) NULL,
  walk_next_step VARCHAR(80) NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_clues_run_policy_clue (run_id, policy_run_id, clue_id),
  UNIQUE KEY uk_ca_clues_run_policy_query (run_id, policy_run_id, search_query_id),
  KEY idx_content_agent_search_clues_effect (search_query_effect_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_walk_actions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  walk_action_id VARCHAR(120) NOT NULL,
  edge_id VARCHAR(120) NOT NULL,
  edge_type VARCHAR(80) NULL,
  from_node_type VARCHAR(80) NULL,
  from_node_id VARCHAR(191) NULL,
  to_node_type VARCHAR(80) NULL,
  to_node_id VARCHAR(191) NULL,
  walk_action VARCHAR(120) NOT NULL,
  walk_status VARCHAR(32) NOT NULL,
  budget_tier VARCHAR(32) NULL,
  depth INT NULL,
  page_cursor VARCHAR(191) NULL,
  next_cursor VARCHAR(191) NULL,
  decision_id VARCHAR(120) NULL,
  rule_pack_id VARCHAR(160) NULL,
  rule_pack_version VARCHAR(80) NULL,
  reason_code VARCHAR(160) NULL,
  source_path_record_id VARCHAR(120) NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_walk_actions_run_policy_action (run_id, policy_run_id, walk_action_id),
  KEY idx_ca_walk_actions_edge_status (edge_id, walk_status),
  KEY idx_ca_walk_actions_status_created_at (walk_status, created_at),
  KEY idx_ca_walk_actions_decision (decision_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_run_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  event_id VARCHAR(80) NOT NULL,
  event_type VARCHAR(120) NOT NULL,
  status VARCHAR(32) NOT NULL,
  input_ref VARCHAR(240) NULL,
  output_ref VARCHAR(240) NULL,
  error_code VARCHAR(120) NULL,
  message TEXT NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_events_run_policy_event (run_id, policy_run_id, event_id),
  KEY idx_content_agent_run_events_type_status (event_type, status),
  KEY idx_content_agent_run_events_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_final_outputs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  output_version VARCHAR(80) NOT NULL DEFAULT 'v1',
  summary JSON NULL,
  final_output JSON NOT NULL,
  validation_status VARCHAR(32) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_outputs_run_policy_version (run_id, policy_run_id, output_version),
  KEY idx_content_agent_final_outputs_validation (validation_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_publish_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  publish_job_id VARCHAR(80) NOT NULL,
  platform_content_id VARCHAR(120) NULL,
  job_status VARCHAR(32) NOT NULL DEFAULT 'created',
  trigger_mode VARCHAR(64) NULL,
  crawler_plan_id VARCHAR(120) NULL,
  produce_plan_id VARCHAR(120) NULL,
  publish_plan_id VARCHAR(120) NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  error_code VARCHAR(120) NULL,
  error_message TEXT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_publish_run_policy_job (run_id, policy_run_id, publish_job_id),
  KEY idx_content_agent_publish_jobs_status (job_status),
  KEY idx_content_agent_publish_jobs_content (platform_content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_author_assets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  author_asset_id VARCHAR(80) NOT NULL,
  platform VARCHAR(32) NOT NULL DEFAULT 'douyin',
  platform_author_id VARCHAR(256) NOT NULL,
  author_display_name VARCHAR(256) NULL,
  author_profile_url TEXT NULL,
  asset_status VARCHAR(32) NOT NULL DEFAULT 'candidate',
  source_type VARCHAR(64) NOT NULL,
  validation_status VARCHAR(64) NOT NULL DEFAULT 'unverified',
  eligible_as_source TINYINT(1) NOT NULL DEFAULT 0,
  elderly_ratio DECIMAL(8,4) NULL,
  elderly_tgi DECIMAL(12,4) NULL,
  content_tags JSON NULL,
  source_run_id VARCHAR(80) NULL,
  source_policy_run_id VARCHAR(80) NULL,
  last_profile_fetch_at DATETIME(3) NULL,
  last_works_fetch_at DATETIME(3) NULL,
  last_validated_at DATETIME(3) NULL,
  profile_snapshot JSON NULL,
  evidence_refs JSON NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_author_assets_asset (author_asset_id),
  UNIQUE KEY uk_ca_author_assets_platform_author (platform, platform_author_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_author_asset_roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  author_asset_id VARCHAR(80) NOT NULL,
  role VARCHAR(64) NOT NULL,
  role_status VARCHAR(32) NOT NULL DEFAULT 'active',
  role_reason_code VARCHAR(160) NULL,
  assigned_by VARCHAR(32) NOT NULL DEFAULT 'system',
  source_run_id VARCHAR(80) NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_author_asset_roles_asset_role (author_asset_id, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_pattern_recall_evidence (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  recall_evidence_id VARCHAR(80) NOT NULL,
  content_discovery_id VARCHAR(80) NULL,
  platform_content_id VARCHAR(120) NOT NULL,
  recall_status VARCHAR(32) NULL,
  evidence_summary JSON NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_recall_run_policy_evidence (run_id, policy_run_id, recall_evidence_id),
  KEY idx_ca_recall_content (run_id, policy_run_id, platform_content_id),
  KEY idx_content_agent_pattern_recall_status (recall_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_strategy_reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  review_id VARCHAR(80) NOT NULL,
  review_status VARCHAR(32) NOT NULL DEFAULT 'generated',
  summary JSON NULL,
  effective_search_queries JSON NULL,
  weak_search_queries JSON NULL,
  top_reject_reasons JSON NULL,
  productive_paths JSON NULL,
  suggestions JSON NULL,
  raw_payload JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_reviews_run_policy_review (run_id, policy_run_id, review_id),
  KEY idx_content_agent_strategy_reviews_status (review_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_performance_feedback (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  feedback_id VARCHAR(80) NOT NULL,
  platform VARCHAR(40) NOT NULL DEFAULT 'douyin',
  platform_content_id VARCHAR(120) NOT NULL,
  content_asset_id VARCHAR(120) NULL,
  feedback_source VARCHAR(80) NOT NULL,
  feedback_status VARCHAR(32) NOT NULL DEFAULT 'available',
  metric_window_start DATETIME(3) NULL,
  metric_window_end DATETIME(3) NULL,
  completion_rate DECIMAL(10,6) NULL,
  share_rate DECIMAL(10,6) NULL,
  average_watch_seconds DECIMAL(12,3) NULL,
  total_watch_seconds DECIMAL(18,3) NULL,
  impression_count BIGINT NULL,
  play_count BIGINT NULL,
  like_count BIGINT NULL,
  comment_count BIGINT NULL,
  share_count BIGINT NULL,
  collect_count BIGINT NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_feedback_run_policy_feedback (run_id, policy_run_id, feedback_id),
  KEY idx_ca_feedback_content (run_id, policy_run_id, platform_content_id),
  KEY idx_ca_feedback_source_status (feedback_source, feedback_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_search_clue_assets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  search_clue_asset_id VARCHAR(120) NOT NULL,
  platform VARCHAR(40) NOT NULL DEFAULT 'douyin',
  clue_type VARCHAR(80) NOT NULL,
  normalized_clue_text VARCHAR(500) NOT NULL,
  display_clue_text VARCHAR(500) NOT NULL,
  promotion_status VARCHAR(32) NOT NULL DEFAULT 'promoted',
  reusable_priority INT NOT NULL DEFAULT 0,
  can_seed_next_run TINYINT(1) NOT NULL DEFAULT 1,
  first_seen_run_id VARCHAR(80) NOT NULL,
  first_seen_policy_run_id VARCHAR(80) NOT NULL,
  last_validated_at DATETIME(3) NULL,
  summary_metrics JSON NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_search_clue_assets_asset (search_clue_asset_id),
  UNIQUE KEY uk_ca_search_clue_assets_identity (platform, clue_type, normalized_clue_text),
  KEY idx_ca_search_clue_assets_status (promotion_status, can_seed_next_run),
  KEY idx_ca_search_clue_assets_last_validated (last_validated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_search_clue_asset_evidence (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  evidence_id VARCHAR(120) NOT NULL,
  search_clue_asset_id VARCHAR(120) NOT NULL,
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  clue_id VARCHAR(80) NOT NULL,
  search_query_id VARCHAR(80) NULL,
  pooled_content_count INT NOT NULL DEFAULT 0,
  review_content_count INT NOT NULL DEFAULT 0,
  failed_content_count INT NOT NULL DEFAULT 0,
  source_path_record_ids JSON NULL,
  decision_ids JSON NULL,
  performance_feedback_refs JSON NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_ca_search_clue_evidence_id (evidence_id),
  UNIQUE KEY uk_ca_search_clue_evidence_run_clue (run_id, policy_run_id, clue_id),
  KEY idx_ca_search_clue_evidence_asset (search_clue_asset_id),
  KEY idx_ca_search_clue_evidence_run_policy (run_id, policy_run_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_agent_policy_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  schema_version VARCHAR(80) NOT NULL DEFAULT 'content_agent.v1',
  run_id VARCHAR(80) NOT NULL,
  policy_run_id VARCHAR(80) NOT NULL,
  experiment_name VARCHAR(160) NULL,
  run_role VARCHAR(80) NULL,
  policy_bundle_id VARCHAR(160) NULL,
  rule_pack_id VARCHAR(160) NULL,
  strategy_id VARCHAR(160) NULL,
  strategy_version VARCHAR(160) NULL,
  rule_pack_version VARCHAR(160) NULL,
  walk_strategy_version VARCHAR(160) NULL,
  policy_bundle_hash VARCHAR(128) NULL,
  strategy_source_ref JSON NULL,
  rule_pack_source_ref JSON NULL,
  evidence_bundle_schema_version VARCHAR(80) NULL,
  runtime_record_schema_version VARCHAR(80) NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'recorded',
  metrics JSON NULL,
  decision_summary JSON NULL,
  raw_payload JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_content_agent_policy_runs_run_policy_run (run_id, policy_run_id),
  KEY idx_content_agent_policy_runs_experiment (experiment_name, run_role, status, created_at),
  KEY idx_content_agent_policy_runs_policy (strategy_version, rule_pack_id, rule_pack_version),
  KEY idx_content_agent_policy_runs_schema_created_at (schema_version, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;