cfc/docs/migrations/20261020_articles_to_dan_knowledge.sql

-- ============================================================================
-- 迁移: 809 篇菌群文章 合并入 dan_knowledge_base
-- 日期: 2026-10-20
-- 目的: 知识库统一。将 health_knowledge_base 中 809 篇菌群文章（item_type='article'）
--       迁入 dan_knowledge_base，以 source_type='article' 与既有 39 条 file 知识区分，
--       最终 dan_knowledge_base 共 848 条（39 file + 809 article）。
-- 数据来源: health_knowledge_base（仅含 item_type='article' 的 809 行，
--           已确认无空标题、无其他 item_type）
-- 映射关系:
--   dan_knowledge_base.title       <- health_knowledge_base.item_name
--   dan_knowledge_base.content     <- health_knowledge_base.description
--   dan_knowledge_base.sort        <- 0
--   dan_knowledge_base.status      <- 1 (启用)
--   dan_knowledge_base.source_url  <- health_knowledge_base.source
--   dan_knowledge_base.source_type <- 'article'
--   dan_knowledge_base.created_at  <- health_knowledge_base.created_at (保留原始时间戳)
--   dan_knowledge_base.updated_at  <- health_knowledge_base.updated_at (保留原始时间戳)
--
-- 说明: dan_knowledge_base 无 title 唯一约束，本 INSERT 非幂等，重复执行会重复插入 809 行；
--       如需重跑，先执行文末回滚语句。
-- 回滚: DELETE FROM dan_knowledge_base WHERE source_type='article';
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 0. 备份（部署前必做）
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS dan_knowledge_base_bak_20261020;
CREATE TABLE dan_knowledge_base_bak_20261020 LIKE dan_knowledge_base;
INSERT INTO dan_knowledge_base_bak_20261020 SELECT * FROM dan_knowledge_base;

DROP TABLE IF EXISTS health_knowledge_base_bak_20261020;
CREATE TABLE health_knowledge_base_bak_20261020 LIKE health_knowledge_base;
INSERT INTO health_knowledge_base_bak_20261020 SELECT * FROM health_knowledge_base;

-- 校验备份: 期望 dan_knowledge_base_bak_20261020 = 39, health_knowledge_base_bak_20261020 = 809
SELECT (SELECT COUNT(*) FROM dan_knowledge_base_bak_20261020) AS dan_bak_rows,
       (SELECT COUNT(*) FROM health_knowledge_base_bak_20261020) AS health_bak_rows;

-- ----------------------------------------------------------------------------
-- 1. INSERT 809 篇（保留原始时间戳）
-- ----------------------------------------------------------------------------
INSERT INTO dan_knowledge_base (title, content, sort, status, source_url, source_type, created_at, updated_at)
SELECT item_name, description, 0, 1, source, 'article', created_at, updated_at
FROM health_knowledge_base
WHERE item_type = 'article';

-- 期望: 影响 809 行

-- ----------------------------------------------------------------------------
-- 2. 校验
-- ----------------------------------------------------------------------------
-- 2.1 按 source_type 分组计数: 期望 file=39, article=809
SELECT source_type, COUNT(*) FROM dan_knowledge_base GROUP BY source_type;

-- 2.2 总数: 期望 848
SELECT COUNT(*) AS total FROM dan_knowledge_base;

-- 2.3 时间戳保留: 期望与 health_knowledge_base 原始时间一致（非迁移当天 2026-10-20）
SELECT MIN(created_at), MAX(created_at) FROM dan_knowledge_base WHERE source_type='article';
SELECT MIN(created_at), MAX(created_at) FROM health_knowledge_base WHERE item_type='article';

-- 2.4 空标题检查: 期望 0
SELECT COUNT(*) FROM dan_knowledge_base
WHERE source_type='article' AND (title IS NULL OR TRIM(title)='');

-- 2.5 空内容检查: 期望 0（注意 AND/OR 优先级，需加括号）
SELECT COUNT(*) FROM dan_knowledge_base
WHERE source_type='article' AND (content IS NULL OR TRIM(content)='');

-- ----------------------------------------------------------------------------
-- 3. 回滚
-- ----------------------------------------------------------------------------
-- DELETE FROM dan_knowledge_base WHERE source_type='article';