-- 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);