AIGC/demo/docs/task_status_cascade_trigger.sql

-- ============================================
-- 任务状态级联更新触发器
-- 
-- 功能：当 task_status 表状态更新时，自动同步到：
--   - task_queue
--   - user_works  
--   - text_to_video_tasks
--   - image_to_video_tasks
--   - storyboard_video_tasks
--
-- 执行方式：在 MySQL 客户端中执行此脚本
-- ============================================

-- 选择数据库
USE aigc_platform;

-- 先删除已存在的触发器
DROP TRIGGER IF EXISTS trg_task_status_update;

-- 修改分隔符
DELIMITER //

-- 创建触发器
-- 状态枚举说明：
--   task_status/task_queue: PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED, TIMEOUT
--   user_works: PROCESSING, COMPLETED, FAILED, DELETED (缺少 PENDING, CANCELLED, TIMEOUT)
--   业务表: PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED (缺少 TIMEOUT)
-- 
-- 注意：分镜图(_image后缀)在task_status表中没有独立记录，无需处理
CREATE TRIGGER trg_task_status_update
AFTER UPDATE ON task_status
FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        
        -- 1. 更新 task_queue 表（状态枚举一致，直接同步）
        UPDATE task_queue 
        SET status = NEW.status,
            updated_at = NOW(),
            error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN NEW.error_message ELSE error_message END
        WHERE task_id = NEW.task_id;
        
        -- 2. 更新 user_works 表（需要状态映射）
        -- PENDING -> PROCESSING, CANCELLED/TIMEOUT -> FAILED
        UPDATE user_works 
        SET status = CASE 
            WHEN NEW.status IN ('PENDING', 'PROCESSING') THEN 'PROCESSING'
            WHEN NEW.status = 'COMPLETED' THEN 'COMPLETED'
            WHEN NEW.status IN ('FAILED', 'CANCELLED', 'TIMEOUT') THEN 'FAILED'
            ELSE status
        END,
        updated_at = NOW(),
        result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
        WHERE task_id = NEW.task_id;
        
        -- 3. 更新业务任务表（TIMEOUT 映射为 FAILED）
        
        -- 文生视频 (txt2vid_*)
        IF NEW.task_id LIKE 'txt2vid_%' THEN
            UPDATE text_to_video_tasks 
            SET status = CASE WHEN NEW.status = 'TIMEOUT' THEN 'FAILED' ELSE NEW.status END,
                updated_at = NOW(),
                progress = CASE WHEN NEW.status = 'COMPLETED' THEN 100 ELSE progress END,
                completed_at = CASE WHEN NEW.status IN ('COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') THEN NOW() ELSE completed_at END,
                error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN COALESCE(NEW.error_message, '任务超时') ELSE error_message END,
                result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
            WHERE task_id = NEW.task_id;
        END IF;
        
        -- 图生视频 (img2vid_*)
        IF NEW.task_id LIKE 'img2vid_%' THEN
            UPDATE image_to_video_tasks 
            SET status = CASE WHEN NEW.status = 'TIMEOUT' THEN 'FAILED' ELSE NEW.status END,
                updated_at = NOW(),
                progress = CASE WHEN NEW.status = 'COMPLETED' THEN 100 ELSE progress END,
                completed_at = CASE WHEN NEW.status IN ('COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') THEN NOW() ELSE completed_at END,
                error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN COALESCE(NEW.error_message, '任务超时') ELSE error_message END,
                result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
            WHERE task_id = NEW.task_id;
        END IF;
        
        -- 分镜视频 (storyboard_*)
        IF NEW.task_id LIKE 'storyboard_%' THEN
            UPDATE storyboard_video_tasks 
            SET status = CASE WHEN NEW.status = 'TIMEOUT' THEN 'FAILED' ELSE NEW.status END,
                updated_at = NOW(),
                progress = CASE WHEN NEW.status = 'COMPLETED' THEN 100 ELSE progress END,
                completed_at = CASE WHEN NEW.status IN ('COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') THEN NOW() ELSE completed_at END,
                error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN COALESCE(NEW.error_message, '任务超时') ELSE error_message END,
                result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
            WHERE task_id = NEW.task_id;
        END IF;
        
    END IF;
END//

-- 恢复分隔符
DELIMITER ;

-- 验证触发器已创建
SHOW TRIGGERS LIKE 'task_status';

-- ============================================
-- 使用说明：
-- 
-- 1. 连接到 MySQL 数据库
-- 2. 选择对应的数据库: USE your_database_name;
-- 3. 执行此脚本
-- 
-- 注意事项：
-- - 触发器会在 task_status 表的 status 字段更新时自动执行
-- - 确保所有表都有 task_id 字段作为关联键
-- - 状态值使用字符串存储（如 'COMPLETED', 'FAILED' 等）
-- ============================================