135 lines
5.9 KiB
SQL
135 lines
5.9 KiB
SQL
-- AI Chat Database Schema
|
||
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 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',
|
||
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;
|
||
|
||
-- 系统功能开关
|
||
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,
|
||
model_id VARCHAR(100) NOT NULL COMMENT 'API model 参数',
|
||
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,
|
||
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,
|
||
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 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 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}', '功能开关'),
|
||
('site_name', 'AI Chat', '站点名称'),
|
||
('allow_register', 'true', '是否允许注册');
|
||
|
||
-- 默认管理员 (密码: admin123)
|
||
INSERT INTO users (username, email, password_hash, nickname, role, membership_level_id) VALUES
|
||
('admin', 'admin@example.com', '$2y$10$VJCJHjJoAdUAENQZzLgKO.DrJaVsquBXGWSg5ok3u2FHAKRkVMWX.', '管理员', 'admin', 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);
|