SET NAMES utf8mb4;

-- 风险中心原来只能看。加白是"这个人我们看过了，别再报"，事件处理是"这条我
-- 跟进完了"，两件事都得留下是谁做的。
ALTER TABLE user_risk_profiles
  ADD COLUMN whitelisted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '人工加白后不再进风险列表',
  ADD COLUMN whitelist_note VARCHAR(255) NOT NULL DEFAULT '',
  ADD COLUMN reviewed_by BIGINT UNSIGNED NULL,
  ADD COLUMN reviewed_at DATETIME(3) NULL;

ALTER TABLE risk_events
  ADD COLUMN handled_at DATETIME(3) NULL,
  ADD COLUMN handled_by BIGINT UNSIGNED NULL,
  ADD COLUMN handled_note VARCHAR(255) NOT NULL DEFAULT '',
  ADD KEY idx_risk_events_handled (handled_at, id);

-- 统一词库：消息、动态、送养原来各有各的判断（送养那组还写在配置里）。
-- 命中之后是拦下来还是转人工，是运营的判断，所以写在数据里而不是代码里。
CREATE TABLE IF NOT EXISTS content_keywords (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  word VARCHAR(60) NOT NULL,
  scope VARCHAR(20) NOT NULL DEFAULT 'all' COMMENT 'all/message/post/adoption/nickname',
  action VARCHAR(10) NOT NULL DEFAULT 'review' COMMENT 'block 直接拦下 / review 转人工',
  category VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分类，便于成组开关',
  note VARCHAR(200) NOT NULL DEFAULT '',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0 停用 1 启用',
  hit_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_hit_at DATETIME(3) NULL,
  created_by BIGINT UNSIGNED 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_keyword_scope (word, scope),
  KEY idx_keywords_status (status, scope)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 把送养那组已经在用的词搬进词库，行为保持原样（转人工，不直接拦）。
INSERT IGNORE INTO content_keywords(word,scope,action,category,note)
SELECT TRIM(word),'adoption','review','送养收费','从 pet.adoption_review_words 迁移' FROM (
  SELECT '元' AS word UNION ALL SELECT '钱' UNION ALL SELECT '价' UNION ALL SELECT '押金'
  UNION ALL SELECT '费用' UNION ALL SELECT '红包' UNION ALL SELECT '转账' UNION ALL SELECT '收费'
  UNION ALL SELECT '出售' UNION ALL SELECT '买' UNION ALL SELECT '卖'
) AS legacy;

INSERT INTO system_configs(config_key,config_value,value_type,description) VALUES
  ('moderation.keywords_enabled','true','boolean','是否启用统一词库；关闭后消息与动态不做关键词判断')
ON DUPLICATE KEY UPDATE description=VALUES(description);