-- =================================================================
-- V7: 为 points_config 表添加 task_type 字段，支持更细致的任务类型分类
-- 时间: 2025-10-22
-- 描述: 添加任务类型字段，区分文生图、图生图、图生视频、文生视频等
-- =================================================================

-- 指定数据库（请根据实际情况修改数据库名）
USE `1818_user_server`;

-- 1. 添加 task_type 字段（如果不存在）
-- 检查字段是否存在，如果不存在则添加
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'points_config' 
      AND COLUMN_NAME = 'task_type'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `points_config` ADD COLUMN `task_type` VARCHAR(50) NULL COMMENT ''任务类型：text_to_image(文生图)/image_to_image(图生图)/text_to_video(文生视频)/image_to_video(图生视频)/llm(大语言模型)/text_to_audio(文生音频)/image_to_text(图生文)/other(其他)'' AFTER `provider_config`',
    'SELECT ''Column task_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 根据现有模型名称更新 task_type
-- OpenAI 模型（文生图）
UPDATE `points_config` 
SET `task_type` = 'text_to_image' 
WHERE `model_name` IN ('sora_image', 'gpt-4o-image') 
  AND `task_type` IS NULL;

-- RunningHub Sora2 文生视频模型（竖屏/横屏，10秒/15秒）
UPDATE `points_config` 
SET `task_type` = 'text_to_video' 
WHERE `model_name` IN (
    'sora_video2',              -- 竖屏10秒
    'sora_video2-landscape',    -- 横屏10秒
    'sora_video2-15s',          -- 竖屏15秒
    'sora_video2-landscape-15s', -- 横屏15秒
    'rh_sora2_text_portrait',   -- RunningHub 文生视频-竖屏
    'rh_sora2_text_landscape',  -- RunningHub 文生视频-横屏
    'rh_sora2_text_portrait_hd', -- RunningHub 文生视频-高清竖屏
    'rh_sora2_text_landscape_hd', -- RunningHub 文生视频-高清横屏
    'rh_sora2_text_portrait_15s', -- RunningHub 文生视频-竖屏15秒
    'rh_sora2_text_landscape_15s' -- RunningHub 文生视频-横屏15秒
) AND `task_type` IS NULL;

-- RunningHub Sora2 图生视频模型
UPDATE `points_config` 
SET `task_type` = 'image_to_video' 
WHERE `model_name` IN (
    'rh_sora2_img_portrait',     -- RunningHub 图生视频-竖屏
    'rh_sora2_img_landscape',    -- RunningHub 图生视频-横屏
    'rh_sora2_img_portrait_hd',  -- RunningHub 图生视频-高清竖屏
    'rh_sora2_img_landscape_hd', -- RunningHub 图生视频-高清横屏
    'rh_sora2_img_portrait_15s', -- RunningHub 图生视频-竖屏15秒
    'rh_sora2_img_landscape_15s' -- RunningHub 图生视频-横屏15秒
) AND `task_type` IS NULL;

-- RunningHub Sora Pro 高清视频
UPDATE `points_config` 
SET `task_type` = 'text_to_video' 
WHERE `model_name` = 'sora-2-pro-all' 
  AND `task_type` IS NULL;

-- 3. 添加新的模型配置示例（可选）
-- 如果需要添加更多模型类型，可以在这里插入

-- 文生图模型示例
INSERT INTO `points_config` (`model_name`, `points_cost`, `description`, `is_enabled`, `provider_type`, `task_type`) VALUES
    ('dall-e-3', 15, 'DALL-E 3 高质量图片生成', 0, 'openai', 'text_to_image'),
    ('midjourney-v6', 20, 'Midjourney V6 艺术图片生成', 0, 'midjourney', 'text_to_image'),
    ('rh_dalle-3', 12, 'RunningHub DALL-E 3 图片生成', 1, 'runninghub', 'text_to_image'),
    ('rh_midjourney', 18, 'RunningHub Midjourney 图片生成', 1, 'runninghub', 'text_to_image')
ON DUPLICATE KEY UPDATE 
    `task_type` = VALUES(`task_type`),
    `description` = VALUES(`description`);

-- 图生图模型示例
INSERT INTO `points_config` (`model_name`, `points_cost`, `description`, `is_enabled`, `provider_type`, `task_type`) VALUES
    ('stable-diffusion-img2img', 12, 'Stable Diffusion 图片转换', 0, 'stability', 'image_to_image')
ON DUPLICATE KEY UPDATE 
    `task_type` = VALUES(`task_type`),
    `description` = VALUES(`description`);

-- LLM大语言模型示例
INSERT INTO `points_config` (`model_name`, `points_cost`, `description`, `is_enabled`, `provider_type`, `task_type`) VALUES
    ('gpt-4-turbo', 5, 'GPT-4 Turbo 对话模型', 0, 'openai', 'llm'),
    ('claude-3-opus', 6, 'Claude 3 Opus 对话模型', 0, 'anthropic', 'llm'),
    ('gemini-pro', 4, 'Google Gemini Pro 对话模型', 0, 'google', 'llm')
ON DUPLICATE KEY UPDATE 
    `task_type` = VALUES(`task_type`),
    `description` = VALUES(`description`);

-- 文生音频模型示例
INSERT INTO `points_config` (`model_name`, `points_cost`, `description`, `is_enabled`, `provider_type`, `task_type`) VALUES
    ('tts-1', 3, 'OpenAI TTS 语音合成', 0, 'openai', 'text_to_audio'),
    ('elevenlabs-tts', 4, 'ElevenLabs 高质量语音合成', 0, 'elevenlabs', 'text_to_audio')
ON DUPLICATE KEY UPDATE 
    `task_type` = VALUES(`task_type`),
    `description` = VALUES(`description`);

-- 图生文模型示例
INSERT INTO `points_config` (`model_name`, `points_cost`, `description`, `is_enabled`, `provider_type`, `task_type`) VALUES
    ('gpt-4-vision', 8, 'GPT-4 Vision 图片理解', 0, 'openai', 'image_to_text'),
    ('claude-3-vision', 7, 'Claude 3 图片分析', 0, 'anthropic', 'image_to_text')
ON DUPLICATE KEY UPDATE 
    `task_type` = VALUES(`task_type`),
    `description` = VALUES(`description`);

-- 4. 为未分类的模型设置默认类型
UPDATE `points_config` 
SET `task_type` = 'other' 
WHERE `task_type` IS NULL;

-- 5. 添加索引以提升查询性能（如果不存在）
-- 检查并添加 task_type 索引
SET @index_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'points_config' 
      AND INDEX_NAME = 'idx_points_config_task_type'
);

SET @sql = IF(@index_exists = 0,
    'CREATE INDEX `idx_points_config_task_type` ON `points_config`(`task_type`)',
    'SELECT ''Index idx_points_config_task_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加复合索引
SET @index_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'points_config' 
      AND INDEX_NAME = 'idx_points_config_provider_task'
);

SET @sql = IF(@index_exists = 0,
    'CREATE INDEX `idx_points_config_provider_task` ON `points_config`(`provider_type`, `task_type`)',
    'SELECT ''Index idx_points_config_provider_task already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =================================================================
-- V7脚本结束
-- =================================================================

