5zu-books/数据库迁移-添加审核字段.sql

-- 数据库迁移脚本：为content表添加审核相关字段
-- 执行前请备份数据库

USE book;

-- 添加第三方发布者ID字段
ALTER TABLE `content` 
ADD COLUMN `publisher_id` INT(11) NULL COMMENT '第三方发布者ID（userRole=3的用户ID），超级管理员发布为NULL' 
AFTER `is_free`;

-- 添加审核状态字段
ALTER TABLE `content` 
ADD COLUMN `audit_status` INT(11) NULL DEFAULT 0 COMMENT '审核状态：0待审核，1已通过，2已驳回' 
AFTER `publisher_id`;

-- 添加驳回理由字段
ALTER TABLE `content` 
ADD COLUMN `reject_reason` VARCHAR(500) NULL COMMENT '驳回理由' 
AFTER `audit_status`;

-- 添加是否可见字段
ALTER TABLE `content` 
ADD COLUMN `is_visible` INT(11) NULL DEFAULT 1 COMMENT '是否可见/上架：1可见，0隐藏（下架）' 
AFTER `reject_reason`;

-- 添加删除状态字段
ALTER TABLE `content` 
ADD COLUMN `delete_status` INT(11) NULL DEFAULT 0 COMMENT '删除状态：0正常，1待删除审核，2已删除' 
AFTER `is_visible`;

-- 为现有数据设置默认值
-- 超级管理员发布的内容：自动通过审核、可见
UPDATE `content` 
SET `audit_status` = 1, 
    `is_visible` = 1, 
    `delete_status` = 0,
    `publisher_id` = NULL
WHERE `publisher_id` IS NULL OR `audit_status` IS NULL;

-- 创建索引以提高查询性能
CREATE INDEX `idx_publisher_id` ON `content` (`publisher_id`);
CREATE INDEX `idx_audit_status` ON `content` (`audit_status`);
CREATE INDEX `idx_is_visible` ON `content` (`is_visible`);
CREATE INDEX `idx_delete_status` ON `content` (`delete_status`);

-- 验证数据
SELECT 
    COUNT(*) as total,
    SUM(CASE WHEN audit_status = 0 THEN 1 ELSE 0 END) as pending,
    SUM(CASE WHEN audit_status = 1 THEN 1 ELSE 0 END) as approved,
    SUM(CASE WHEN audit_status = 2 THEN 1 ELSE 0 END) as rejected,
    SUM(CASE WHEN delete_status = 1 THEN 1 ELSE 0 END) as delete_pending
FROM `content`;