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

-- 系统功能开关
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 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}', '功能开关'),
('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);