ylrz_saas_pharmacy/java/sql/admin_ai_model_config.sql

-- =====================================================
-- Admin DB: AI模型统一配置表
-- 将除了FastGPT、TTS、图像/语音以外的所有AI模型配置
-- 从租户DB迁移到Admin DB统一管理
-- =====================================================

-- 1. 模型配置表
DROP TABLE IF EXISTS admin_ai_model;
CREATE TABLE admin_ai_model (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    model_name VARCHAR(100) NOT NULL COMMENT '模型显示名称',
    provider_code VARCHAR(50) NOT NULL COMMENT '供应商编码: doubao/qwen/yuanbao/deepseek',
    model_identifier VARCHAR(200) NOT NULL COMMENT '模型标识符, 如 doubao-1-5-pro-32k',
    api_endpoint VARCHAR(500) NOT NULL COMMENT 'API地址',
    api_key VARCHAR(500) NOT NULL COMMENT 'API密钥',
    embedding_endpoint VARCHAR(500) COMMENT '嵌入端点(可选)',
    max_tokens INT DEFAULT 4096 COMMENT '最大Token数',
    temperature DOUBLE DEFAULT 0.7 COMMENT '温度参数',
    sort_order INT DEFAULT 0 COMMENT '全局排序，越小越优先',
    status TINYINT DEFAULT 1 COMMENT '状态: 1启用 0禁用',
    model_type INT DEFAULT 0 COMMENT '模型类型，0:默认 1:租户销售端使用生成视频的模型',
    create_by VARCHAR(50) DEFAULT 'system' COMMENT '创建人',
    create_time DATETIME DEFAULT NOW() COMMENT '创建时间',
    update_by VARCHAR(50) DEFAULT 'system' COMMENT '更新人',
    update_time DATETIME DEFAULT NOW() ON UPDATE NOW() COMMENT '更新时间'
) COMMENT='AI模型配置表(Admin DB统一管理, 不含FastGPT/TTS/图像/语音)';

-- 2. 场景定义表
DROP TABLE IF EXISTS admin_ai_scene;
CREATE TABLE admin_ai_scene (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    scene_code VARCHAR(50) NOT NULL UNIQUE COMMENT '场景编码',
    scene_name VARCHAR(100) NOT NULL COMMENT '场景名称',
    scene_type VARCHAR(20) NOT NULL DEFAULT 'single' COMMENT '场景类型: single单模型 / multi_pipeline多模型流水线',
    pipeline_type VARCHAR(30) COMMENT '流水线类型: sequential顺序调用 / scoring质量评分链',
    quality_threshold INT DEFAULT 120 COMMENT '质量评分通过阈值(满分160, 默认120)',
    status TINYINT DEFAULT 1 COMMENT '状态: 1启用 0禁用',
    create_by VARCHAR(50) DEFAULT 'system' COMMENT '创建人',
    create_time DATETIME DEFAULT NOW() COMMENT '创建时间',
    update_by VARCHAR(50) DEFAULT 'system' COMMENT '更新人',
    update_time DATETIME DEFAULT NOW() ON UPDATE NOW() COMMENT '更新时间'
) COMMENT='AI模型使用场景定义';

-- 3. 场景-模型关联表（支持排序）
DROP TABLE IF EXISTS admin_ai_scene_model;
CREATE TABLE admin_ai_scene_model (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    scene_code VARCHAR(50) NOT NULL COMMENT '关联场景编码',
    model_id BIGINT NOT NULL COMMENT '关联模型ID',
    pipeline_order INT DEFAULT 0 COMMENT '流水线中的顺序(min→max)',
    role VARCHAR(30) DEFAULT 'generator' COMMENT '角色: generator生成者 / scorer评分者',
    sort_weight INT DEFAULT 0 COMMENT '当多模型同级时的优先级权重',
    create_time DATETIME DEFAULT NOW() COMMENT '创建时间',
    INDEX idx_scene_code (scene_code),
    INDEX idx_model_id (model_id)
) COMMENT='场景-模型关联(支持排序)';

-- =====================================================
-- 初始化数据
-- =====================================================

-- 从 company_ai_provider 迁移现有模型（如存在）
INSERT INTO admin_ai_model (model_name, provider_code, model_identifier, api_endpoint, api_key, embedding_endpoint, max_tokens, temperature, sort_order, status)
SELECT
    provider_name,
    provider_code,
    model_name,
    api_endpoint,
    COALESCE(api_key, ''),
    CASE WHEN provider_code = 'doubao' THEN REPLACE(api_endpoint, '/chat/completions', '/embeddings')
         WHEN provider_code = 'qwen' THEN REPLACE(api_endpoint, '/chat/completions', '/embeddings')
         ELSE NULL END,
    COALESCE(max_tokens, 4096),
    COALESCE(temperature, 0.7),
    CASE WHEN is_default = 1 THEN 0 ELSE 10 END,
    enabled
FROM company_ai_provider
WHERE del_flag = 0 AND enabled = 1;

-- 如果上述迁移没有数据，插入默认4种模型预设
INSERT INTO admin_ai_model (model_name, provider_code, model_identifier, api_endpoint, api_key, embedding_endpoint, max_tokens, temperature, sort_order, status)
SELECT * FROM (
    SELECT '豆包(Doubao)' AS model_name, 'doubao' AS provider_code, 'doubao-pro-32k' AS model_identifier,
           'https://ark.cn-beijing.volces.com/api/v3/chat/completions' AS api_endpoint, '' AS api_key,
           'https://ark.cn-beijing.volces.com/api/v3/embeddings' AS embedding_endpoint,
           4096 AS max_tokens, 0.7 AS temperature, 0 AS sort_order, 1 AS status
    UNION ALL
    SELECT '通义千问(Qwen)', 'qwen', 'qwen-plus',
           'https://dashscope.aliyuncs.com/compatible-mode/v1/chat/completions', '',
           'https://dashscope.aliyuncs.com/compatible-mode/v1/embeddings',
           8192, 0.7, 1, 1
    UNION ALL
    SELECT '元宝/混元(Yuanbao)', 'yuanbao', 'hunyuan-lite',
           'https://api.hunyuan.cloud.tencent.com/v1/chat/completions', '',
           NULL, 4096, 0.7, 2, 1
    UNION ALL
    SELECT 'DeepSeek', 'deepseek', 'deepseek-v4-pro',
           'https://api.deepseek.com/chat/completions', '',
           NULL, 8192, 0.7, 3, 1
) t
WHERE NOT EXISTS (SELECT 1 FROM admin_ai_model LIMIT 1);

-- 初始化19个场景
INSERT INTO admin_ai_scene (scene_code, scene_name, scene_type, pipeline_type, quality_threshold, status) VALUES
-- 多模型流水线场景
('workflow_generation', '工作流生成', 'multi_pipeline', 'sequential', NULL, 1),
('quality_scoring',     '质量评分',   'multi_pipeline', 'scoring',    120, 1),
-- 单模型场景
('workflow_llm',        '提示词管理',       'single', NULL, NULL, 1),
('workflow_fallback',   '工作流兜底回答',   'single', NULL, NULL, 1),
('dynamic_node',        '动态节点执行',     'single', NULL, NULL, 1),
('semantic_analysis',   '语义意图分析',     'single', NULL, NULL, 1),
('summary_generation',  '摘要生成',         'single', NULL, NULL, 1),
('content_moderation',  '内容审核',         'single', NULL, NULL, 1),
('multi_turn_dialogue', '多轮对话管理',     'single', NULL, NULL, 1),
('takeover_detection',  '转人工检测',       'single', NULL, NULL, 1),
('dynamic_adjustment',  '动态节点调整',     'single', NULL, NULL, 1),
('workflow_evolution',  '工作流进化',       'single', NULL, NULL, 1),
('template_evolution',  '模板进化',         'single', NULL, NULL, 1),
('feedback_evolution',  '反馈驱动进化',     'single', NULL, NULL, 1),
('event_decision',      '事件决策',         'single', NULL, NULL, 1),
('user_optimization',   '用户级优化',       'single', NULL, NULL, 1),
('corpus_analysis',     '语料分析',         'single', NULL, NULL, 1),
('tenant_learning',     '租户学习',         'single', NULL, NULL, 1),
('vector_embedding',    '向量嵌入',         'single', NULL, NULL, 1),
('image_recognition',   '图片识别(多模态)', 'single', NULL, NULL, 1);

-- 为单模型场景绑定默认模型（取排序第一的doubao模型）
INSERT INTO admin_ai_scene_model (scene_code, model_id, pipeline_order, role, sort_weight)
SELECT s.scene_code, m.id, 0, 'generator', 0
FROM admin_ai_scene s
CROSS JOIN (SELECT id FROM admin_ai_model WHERE provider_code = 'doubao' AND status = 1 ORDER BY sort_order LIMIT 1) m
WHERE s.scene_type = 'single'
AND NOT EXISTS (SELECT 1 FROM admin_ai_scene_model sm WHERE sm.scene_code = s.scene_code);

-- 为工作流生成场景绑定默认3模型流水线（如存在≥3个模型）
INSERT INTO admin_ai_scene_model (scene_code, model_id, pipeline_order, role, sort_weight)
SELECT 'workflow_generation', m.id, m.rn - 1,
       CASE WHEN m.rn = 1 THEN 'generator' WHEN m.rn = 2 THEN 'generator' ELSE 'scorer' END,
       0
FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY sort_order) AS rn FROM admin_ai_model WHERE status = 1 LIMIT 3) m
WHERE NOT EXISTS (SELECT 1 FROM admin_ai_scene_model WHERE scene_code = 'workflow_generation');

-- 为质量评分场景绑定默认2模型流水线（如存在≥2个模型）
INSERT INTO admin_ai_scene_model (scene_code, model_id, pipeline_order, role, sort_weight)
SELECT 'quality_scoring', m.id, m.rn - 1,
       CASE WHEN m.rn = 1 THEN 'generator' ELSE 'scorer' END,
       0
FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY sort_order) AS rn FROM admin_ai_model WHERE status = 1 LIMIT 2) m
WHERE NOT EXISTS (SELECT 1 FROM admin_ai_scene_model WHERE scene_code = 'quality_scoring');