-- ============================================
-- 任务状态级联更新触发器
-- 当 task_status 表状态更新时，自动同步到其他关联表
-- ============================================

DELIMITER //

-- 删除已存在的触发器（如果存在）
DROP TRIGGER IF EXISTS trg_task_status_update//

-- 创建触发器：当 task_status 更新时，级联更新其他表
CREATE TRIGGER trg_task_status_update
AFTER UPDATE ON task_status
FOR EACH ROW
BEGIN
    -- 只在状态发生变化时触发
    IF NEW.status <> OLD.status THEN
        
        -- 更新 task_queue 表
        UPDATE task_queue 
        SET status = NEW.status,
            updated_at = NOW(),
            error_message = CASE WHEN NEW.status = 'FAILED' THEN NEW.error_message ELSE error_message END
        WHERE task_id = NEW.task_id;
        
        -- 更新 user_works 表
        UPDATE user_works 
        SET status = CASE 
            WHEN NEW.status = 'COMPLETED' THEN 'COMPLETED'
            WHEN NEW.status = 'FAILED' THEN 'FAILED'
            WHEN NEW.status = 'PROCESSING' THEN 'PROCESSING'
            WHEN NEW.status = 'PENDING' THEN 'PENDING'
            WHEN NEW.status = 'CANCELLED' THEN 'CANCELLED'
            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;
        
        -- 更新 text_to_video_tasks 表（根据 task_id 前缀判断）
        IF NEW.task_id LIKE 'txt2vid_%' THEN
            UPDATE text_to_video_tasks 
            SET status = NEW.status,
                updated_at = NOW(),
                error_message = CASE WHEN NEW.status = 'FAILED' THEN 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;
        
        -- 更新 image_to_video_tasks 表
        IF NEW.task_id LIKE 'img2vid_%' THEN
            UPDATE image_to_video_tasks 
            SET status = NEW.status,
                updated_at = NOW(),
                error_message = CASE WHEN NEW.status = 'FAILED' THEN 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_video_tasks 表（分镜视频，排除分镜图）
        IF NEW.task_id LIKE 'storyboard_%' AND NEW.task_id NOT LIKE '%_image' THEN
            UPDATE storyboard_video_tasks 
            SET status = NEW.status,
                updated_at = NOW(),
                error_message = CASE WHEN NEW.status = 'FAILED' THEN 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;
        
        -- 分镜图任务（taskId 以 _image 结尾）
        IF NEW.task_id LIKE '%_image' THEN
            -- 分镜图关联到主任务，更新主任务的时间戳和错误信息
            UPDATE storyboard_video_tasks 
            SET updated_at = NOW(),
                error_message = CASE WHEN NEW.status = 'FAILED' THEN NEW.error_message ELSE error_message END
            WHERE task_id = REPLACE(NEW.task_id, '_image', '');
        END IF;
        
    END IF;
END//

DELIMITER ;

-- ============================================
-- 可选：反向触发器（业务表更新时同步到 task_status）
-- 根据需要启用
-- ============================================

/*
DELIMITER //

-- text_to_video_tasks 状态更新时同步
DROP TRIGGER IF EXISTS trg_text_to_video_status_update//
CREATE TRIGGER trg_text_to_video_status_update
AFTER UPDATE ON text_to_video_tasks
FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        UPDATE task_status SET status = NEW.status, updated_at = NOW() WHERE task_id = NEW.task_id;
    END IF;
END//

-- image_to_video_tasks 状态更新时同步
DROP TRIGGER IF EXISTS trg_image_to_video_status_update//
CREATE TRIGGER trg_image_to_video_status_update
AFTER UPDATE ON image_to_video_tasks
FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        UPDATE task_status SET status = NEW.status, updated_at = NOW() WHERE task_id = NEW.task_id;
    END IF;
END//

-- storyboard_video_tasks 状态更新时同步
DROP TRIGGER IF EXISTS trg_storyboard_status_update//
CREATE TRIGGER trg_storyboard_status_update
AFTER UPDATE ON storyboard_video_tasks
FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        UPDATE task_status SET status = NEW.status, updated_at = NOW() WHERE task_id = NEW.task_id;
    END IF;
END//

DELIMITER ;
*/
