272 lines
14 KiB
SQL
272 lines
14 KiB
SQL
-- AI Chat Database Schema
|
||
SET NAMES utf8mb4;
|
||
CREATE DATABASE IF NOT EXISTS ai_chat DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
USE ai_chat;
|
||
|
||
-- 会员等级
|
||
CREATE TABLE IF NOT EXISTS membership_levels (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(50) NOT NULL,
|
||
slug VARCHAR(50) NOT NULL UNIQUE,
|
||
max_conversations INT UNSIGNED DEFAULT 50,
|
||
max_messages_per_day INT UNSIGNED DEFAULT 100,
|
||
max_upload_size_mb INT UNSIGNED DEFAULT 10,
|
||
allowed_models JSON NULL COMMENT '允许使用的模型ID列表，null表示全部',
|
||
permissions JSON NULL COMMENT '额外权限配置',
|
||
sort_order INT DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 角色
|
||
CREATE TABLE IF NOT EXISTS roles (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(50) NOT NULL,
|
||
slug VARCHAR(50) NOT NULL UNIQUE,
|
||
permissions JSON NULL COMMENT '后台权限配置',
|
||
sort_order INT DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 部门（支持上下级）
|
||
CREATE TABLE IF NOT EXISTS departments (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
parent_id INT UNSIGNED NULL,
|
||
sort_order INT DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_parent (parent_id)
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 用户
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
username VARCHAR(50) NOT NULL UNIQUE,
|
||
email VARCHAR(100) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
nickname VARCHAR(50) DEFAULT NULL,
|
||
avatar VARCHAR(255) DEFAULT NULL,
|
||
role ENUM('user', 'admin') DEFAULT 'user',
|
||
role_id INT UNSIGNED NULL,
|
||
department_id INT UNSIGNED NULL,
|
||
membership_level_id INT UNSIGNED DEFAULT 1,
|
||
status ENUM('active', 'disabled') DEFAULT 'active',
|
||
last_login_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (membership_level_id) REFERENCES membership_levels(id)
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 一次性注册邀请码；指定 department_id 后，新用户会自动归属到该部门
|
||
CREATE TABLE IF NOT EXISTS invitation_codes (
|
||
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(32) NOT NULL UNIQUE,
|
||
department_id INT UNSIGNED NULL,
|
||
created_by INT UNSIGNED NULL,
|
||
used_by INT UNSIGNED NULL,
|
||
status ENUM('active', 'used', 'revoked') NOT NULL DEFAULT 'active',
|
||
expires_at TIMESTAMP NULL,
|
||
used_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_invitation_status (status),
|
||
INDEX idx_invitation_department (department_id),
|
||
INDEX idx_invitation_created (created_at)
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 系统功能开关
|
||
CREATE TABLE IF NOT EXISTS system_settings (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
setting_key VARCHAR(100) NOT NULL UNIQUE,
|
||
setting_value TEXT NOT NULL,
|
||
description VARCHAR(255) DEFAULT NULL,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB;
|
||
|
||
-- AI 模型配置（OpenAI 格式）
|
||
CREATE TABLE IF NOT EXISTS ai_models (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
provider VARCHAR(20) NOT NULL DEFAULT 'openai' COMMENT '接口类型：openai=OpenAI兼容协议，dify=Dify应用API',
|
||
model_id VARCHAR(100) NOT NULL COMMENT 'API model 参数（Dify 模式下可留空，应用由 API Key 决定）',
|
||
api_base_url VARCHAR(255) NOT NULL DEFAULT 'https://api.openai.com/v1',
|
||
api_key VARCHAR(255) NOT NULL,
|
||
max_tokens INT UNSIGNED DEFAULT 4096,
|
||
temperature DECIMAL(3,2) DEFAULT 0.70,
|
||
is_default TINYINT(1) DEFAULT 0,
|
||
enabled TINYINT(1) DEFAULT 1,
|
||
support_context TINYINT(1) DEFAULT 1 COMMENT '是否支持上下文（多轮对话），关闭则每次只发送当前消息',
|
||
support_image TINYINT(1) DEFAULT 1 COMMENT '是否支持图片/多模态输入，关闭则图片不会发送给模型',
|
||
frequency_penalty DECIMAL(3,2) DEFAULT 0.00 COMMENT '频率惩罚，值越大越能抑制模型重复输出相同内容（部分自部署模型容易陷入重复循环）',
|
||
presence_penalty DECIMAL(3,2) DEFAULT 0.00 COMMENT '存在惩罚，抑制模型重复讨论相同话题/短语',
|
||
extra_config JSON NULL COMMENT '扩展配置（ComfyUI 工作流 JSON、节点映射等）',
|
||
sort_order INT DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 会话
|
||
CREATE TABLE IF NOT EXISTS conversations (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT UNSIGNED NOT NULL,
|
||
title VARCHAR(200) DEFAULT '新对话',
|
||
model_id INT UNSIGNED NULL,
|
||
is_pinned TINYINT(1) DEFAULT 0,
|
||
message_count INT UNSIGNED DEFAULT 0,
|
||
external_conversation_id VARCHAR(64) NULL COMMENT '第三方平台（如 Dify）自己维护的会话ID，用于保持上下文连续',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP NULL,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (model_id) REFERENCES ai_models(id) ON DELETE SET NULL,
|
||
INDEX idx_user_updated (user_id, updated_at DESC)
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 消息
|
||
CREATE TABLE IF NOT EXISTS messages (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
conversation_id INT UNSIGNED NOT NULL,
|
||
role ENUM('user', 'assistant', 'system') NOT NULL,
|
||
content TEXT NOT NULL,
|
||
content_type ENUM('text', 'markdown', 'mixed') DEFAULT 'text',
|
||
attachments JSON NULL COMMENT '[{type,url,name,size,mime}]',
|
||
tokens_used INT UNSIGNED DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
|
||
INDEX idx_conversation (conversation_id, created_at)
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 上传文件
|
||
CREATE TABLE IF NOT EXISTS uploads (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT UNSIGNED NOT NULL,
|
||
original_name VARCHAR(255) NOT NULL,
|
||
stored_name VARCHAR(255) NOT NULL,
|
||
file_path VARCHAR(500) NOT NULL,
|
||
mime_type VARCHAR(100) NOT NULL,
|
||
file_size INT UNSIGNED NOT NULL,
|
||
file_type ENUM('image', 'video', 'audio', 'document', 'other') NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 短剧项目（一个项目可包含多集，角色资产在项目内复用）
|
||
CREATE TABLE IF NOT EXISTS video_projects (
|
||
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT UNSIGNED NOT NULL,
|
||
title VARCHAR(160) NOT NULL,
|
||
idea MEDIUMTEXT NOT NULL,
|
||
style VARCHAR(100) DEFAULT '电影写实',
|
||
aspect_ratio VARCHAR(10) NOT NULL DEFAULT '9:16',
|
||
episode_duration INT UNSIGNED NOT NULL DEFAULT 30,
|
||
duration_mode VARCHAR(10) NOT NULL DEFAULT 'fixed',
|
||
quality VARCHAR(20) NOT NULL DEFAULT 'fast',
|
||
voice_language VARCHAR(20) NOT NULL DEFAULT 'zh-CN',
|
||
show_subtitles TINYINT(1) NOT NULL DEFAULT 1,
|
||
character_origin VARCHAR(20) NOT NULL DEFAULT 'east_asian',
|
||
screen_text_language VARCHAR(20) NOT NULL DEFAULT 'zh-CN',
|
||
shot_duration_mode VARCHAR(10) NOT NULL DEFAULT 'auto',
|
||
status VARCHAR(30) NOT NULL DEFAULT 'draft',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
INDEX idx_video_project_user (user_id, updated_at)
|
||
) ENGINE=InnoDB;
|
||
|
||
CREATE TABLE IF NOT EXISTS video_characters (
|
||
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
project_id BIGINT UNSIGNED NOT NULL,
|
||
user_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(80) NOT NULL,
|
||
description TEXT NULL,
|
||
reference_upload_id INT UNSIGNED NULL,
|
||
voice_key VARCHAR(100) NULL,
|
||
is_locked TINYINT(1) NOT NULL DEFAULT 1,
|
||
asset_version INT UNSIGNED NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (project_id) REFERENCES video_projects(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
INDEX idx_video_character_project (project_id, id)
|
||
) ENGINE=InnoDB;
|
||
|
||
CREATE TABLE IF NOT EXISTS video_episodes (
|
||
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
project_id BIGINT UNSIGNED NOT NULL,
|
||
episode_no INT UNSIGNED NOT NULL DEFAULT 1,
|
||
title VARCHAR(160) NOT NULL,
|
||
script MEDIUMTEXT NULL,
|
||
status VARCHAR(30) NOT NULL DEFAULT 'storyboard',
|
||
progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
||
progress_message VARCHAR(255) NULL,
|
||
final_upload_id INT UNSIGNED NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (project_id) REFERENCES video_projects(id) ON DELETE CASCADE,
|
||
UNIQUE KEY uk_video_episode_number (project_id, episode_no),
|
||
INDEX idx_video_episode_status (status, updated_at)
|
||
) ENGINE=InnoDB;
|
||
|
||
CREATE TABLE IF NOT EXISTS video_shots (
|
||
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
episode_id BIGINT UNSIGNED NOT NULL,
|
||
shot_no INT UNSIGNED NOT NULL,
|
||
title VARCHAR(160) NOT NULL,
|
||
prompt TEXT NOT NULL,
|
||
dialogue TEXT NULL,
|
||
duration_seconds SMALLINT UNSIGNED NOT NULL DEFAULT 5,
|
||
workflow_type VARCHAR(20) NOT NULL DEFAULT 'fl2va',
|
||
status VARCHAR(30) NOT NULL DEFAULT 'draft',
|
||
prompt_id VARCHAR(80) NULL,
|
||
seed BIGINT UNSIGNED NULL,
|
||
output_upload_id INT UNSIGNED NULL,
|
||
error_message TEXT NULL,
|
||
meta JSON NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (episode_id) REFERENCES video_episodes(id) ON DELETE CASCADE,
|
||
UNIQUE KEY uk_video_shot_number (episode_id, shot_no),
|
||
INDEX idx_video_shot_prompt (prompt_id),
|
||
INDEX idx_video_shot_status (status, updated_at)
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 每日消息统计
|
||
CREATE TABLE IF NOT EXISTS user_daily_stats (
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT UNSIGNED NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
message_count INT UNSIGNED DEFAULT 0,
|
||
UNIQUE KEY uk_user_date (user_id, stat_date),
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB;
|
||
|
||
-- 初始会员等级
|
||
INSERT INTO membership_levels (name, slug, max_conversations, max_messages_per_day, max_upload_size_mb, permissions) VALUES
|
||
('免费用户', 'free', 20, 50, 5, '{"can_upload_image":true,"can_upload_video":false,"can_upload_file":false,"can_use_voice":false}'),
|
||
('高级会员', 'premium', 200, 500, 50, '{"can_upload_image":true,"can_upload_video":true,"can_upload_file":true,"can_use_voice":true}'),
|
||
('管理员', 'admin', 9999, 9999, 100, '{"can_upload_image":true,"can_upload_video":true,"can_upload_file":true,"can_use_voice":true}');
|
||
|
||
INSERT INTO roles (name, slug, permissions, sort_order) VALUES
|
||
('超级管理员', 'super_admin', '{"can_access_admin":true,"dirs":["dir:overview","dir:org","dir:business","dir:system"],"menus":["menu:dashboard","menu:users","menu:departments","menu:roles","menu:conversations","menu:memberships","menu:models","menu:settings"],"buttons":["btn:user:edit","btn:user:reset_password","btn:dept:create","btn:dept:edit","btn:dept:delete","btn:role:create","btn:role:edit","btn:role:delete","btn:conv:view_all","btn:conv:view_subordinate","btn:membership:edit","btn:model:create","btn:model:edit","btn:model:delete","btn:model:test","btn:settings:save"],"can_manage_users":true,"can_manage_roles":true,"can_manage_departments":true,"can_view_all_conversations":true,"can_view_subordinate_conversations":true,"can_manage_models":true,"can_manage_settings":true,"can_manage_memberships":true}', 1),
|
||
('部门管理员', 'dept_manager', '{"can_access_admin":true,"dirs":["dir:overview","dir:business"],"menus":["menu:dashboard","menu:conversations"],"buttons":["btn:conv:view_subordinate"],"can_manage_users":false,"can_manage_roles":false,"can_manage_departments":false,"can_view_all_conversations":false,"can_view_subordinate_conversations":true,"can_manage_models":false,"can_manage_settings":false,"can_manage_memberships":false}', 2),
|
||
('普通用户', 'user', '{"can_access_admin":false,"dirs":[],"menus":[],"buttons":[],"can_manage_users":false,"can_manage_roles":false,"can_manage_departments":false,"can_view_all_conversations":false,"can_view_subordinate_conversations":false,"can_manage_models":false,"can_manage_settings":false,"can_manage_memberships":false}', 3);
|
||
|
||
INSERT INTO departments (name, parent_id, sort_order) VALUES
|
||
('总公司', NULL, 0);
|
||
|
||
-- 默认系统设置
|
||
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
|
||
('features', '{"markdown":true,"image":true,"video":true,"voice":true,"document":true,"emoji":true,"upload_image":true,"upload_video":true,"upload_file":true,"paste_image":true,"short_drama":true}', '功能开关'),
|
||
('site_name', 'AI Chat', '站点名称'),
|
||
('allow_register', 'true', '是否允许注册');
|
||
|
||
-- 默认管理员 (密码: admin123)
|
||
INSERT INTO users (username, email, password_hash, nickname, role, role_id, department_id, membership_level_id) VALUES
|
||
('admin', 'admin@example.com', '$2y$10$VJCJHjJoAdUAENQZzLgKO.DrJaVsquBXGWSg5ok3u2FHAKRkVMWX.', '管理员', 'admin', 1, 1, 3);
|
||
|
||
-- 示例 AI 模型（需替换 api_key）
|
||
INSERT INTO ai_models (name, model_id, api_base_url, api_key, is_default, enabled) VALUES
|
||
('GPT-4o Mini', 'gpt-4o-mini', 'https://api.openai.com/v1', 'your-api-key-here', 1, 1);
|