SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS story_clusters (
  id CHAR(32) NOT NULL, canonical_news_id CHAR(32) NOT NULL, canonical_title VARCHAR(1000) NOT NULL,
  category VARCHAR(100) NOT NULL DEFAULT 'general', source_count INT NOT NULL DEFAULT 1,
  priority_score INT NOT NULL DEFAULT 0, is_breaking TINYINT(1) NOT NULL DEFAULT 0,
  first_pub_ts BIGINT NOT NULL DEFAULT 0, last_pub_ts BIGINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY(id), INDEX idx_story_score(priority_score,last_pub_ts), INDEX idx_story_category(category,last_pub_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS story_cluster_members (
  cluster_id CHAR(32) NOT NULL, news_id CHAR(32) NOT NULL, source_id VARCHAR(100) NOT NULL,
  similarity DECIMAL(5,4) NOT NULL DEFAULT 1, added_at DATETIME NOT NULL,
  PRIMARY KEY(cluster_id,news_id), UNIQUE KEY uniq_story_news(news_id), INDEX idx_story_member_cluster(cluster_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, action VARCHAR(80) NOT NULL, level VARCHAR(20) NOT NULL DEFAULT 'info',
  entity_type VARCHAR(40) NULL, entity_id VARCHAR(190) NULL, message VARCHAR(1000) NOT NULL, meta JSON NULL,
  created_at DATETIME NOT NULL, PRIMARY KEY(id), INDEX idx_auto_log_date(created_at), INDEX idx_auto_log_action(action,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_notifications (
  story_key VARCHAR(190) NOT NULL, sent_at DATETIME NOT NULL, title VARCHAR(500) NOT NULL,
  PRIMARY KEY(story_key), INDEX idx_auto_push_date(sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;