-- V11: 广场作品审核功能
-- 为 plaza_work 表添加审核相关字段

USE `1818ai`;

-- 1. 添加审核相关字段
ALTER TABLE `plaza_work`
    ADD COLUMN `audit_status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '审核状态：pending-待审核, approved-已通过, rejected-已拒绝' AFTER `status`,
    ADD COLUMN `audit_admin_id` BIGINT NULL COMMENT '审核管理员ID' AFTER `audit_status`,
    ADD COLUMN `audit_time` DATETIME NULL COMMENT '审核时间' AFTER `audit_admin_id`,
    ADD COLUMN `audit_remark` TEXT NULL COMMENT '审核备注（拒绝原因等）' AFTER `audit_time`,
    ADD INDEX `idx_audit_status` (`audit_status`),
    ADD INDEX `idx_audit_time` (`audit_time`);

-- 2. 更新现有数据（如果有的话）
-- 将已发布的作品标记为已审核通过
UPDATE `plaza_work` 
SET `audit_status` = 'approved', 
    `audit_time` = `create_time`
WHERE `status` = 'published' AND `is_deleted` = 0;

-- 3. 创建审核记录表（用于记录审核历史）
CREATE TABLE IF NOT EXISTS `plaza_work_audit_log` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `work_no` VARCHAR(64) NOT NULL COMMENT '作品编号',
    `work_id` BIGINT NOT NULL COMMENT '作品ID',
    `audit_status` VARCHAR(20) NOT NULL COMMENT '审核结果：approved-通过, rejected-拒绝',
    `audit_admin_id` BIGINT NOT NULL COMMENT '审核管理员ID',
    `audit_admin_name` VARCHAR(100) NULL COMMENT '审核管理员名称',
    `audit_remark` TEXT NULL COMMENT '审核备注',
    `audit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
    INDEX `idx_work_no` (`work_no`),
    INDEX `idx_work_id` (`work_id`),
    INDEX `idx_audit_admin_id` (`audit_admin_id`),
    INDEX `idx_audit_time` (`audit_time`)
) COMMENT '广场作品审核记录表';

-- 4. 更新视图：只显示已审核通过的作品
DROP VIEW IF EXISTS `v_plaza_hot_works`;
CREATE VIEW `v_plaza_hot_works` AS
SELECT pw.*,
       (SELECT COUNT(*) FROM `plaza_work_like` pwr WHERE pwr.work_no = pw.work_no) AS actual_like_count
FROM `plaza_work` pw
WHERE pw.status = 'published' 
  AND pw.audit_status = 'approved' 
  AND pw.is_deleted = 0
ORDER BY actual_like_count DESC, pw.create_time DESC;

DROP VIEW IF EXISTS `v_plaza_latest_works`;
CREATE VIEW `v_plaza_latest_works` AS
SELECT pw.*
FROM `plaza_work` pw
WHERE pw.status = 'published' 
  AND pw.audit_status = 'approved' 
  AND pw.is_deleted = 0
ORDER BY pw.create_time DESC;

-- 5. 创建待审核作品视图（管理端使用）
CREATE VIEW `v_plaza_pending_works` AS
SELECT pw.*,
       u.username AS author_name,
       u.phone AS author_phone
FROM `plaza_work` pw
LEFT JOIN `user` u ON pw.user_id = u.id
WHERE pw.audit_status = 'pending' 
  AND pw.is_deleted = 0
ORDER BY pw.create_time ASC;

COMMIT;

