-- MySQL dump 10.13  Distrib 8.4.11, for Linux (aarch64)
--
-- Host: localhost    Database: ai_chat
-- ------------------------------------------------------
-- Server version	8.4.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ai_models`
--

DROP TABLE IF EXISTS `ai_models`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_models` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `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 DEFAULT NULL COMMENT '扩展配置（ComfyUI 工作流 JSON、节点映射等）',
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `ai_models`
--

LOCK TABLES `ai_models` WRITE;
/*!40000 ALTER TABLE `ai_models` DISABLE KEYS */;
INSERT INTO `ai_models` VALUES (1,'GPT-4o Mini','openai','gpt-4o-mini','https://api.openai.com/v1','your-api-key-here',4096,0.70,1,1,1,1,0.00,0.00,NULL,0,'2026-08-03 02:38:43','2026-08-03 02:38:43');
/*!40000 ALTER TABLE `ai_models` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `conversations`
--

DROP TABLE IF EXISTS `conversations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `conversations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `title` varchar(200) DEFAULT '新对话',
  `model_id` int unsigned DEFAULT NULL,
  `is_pinned` tinyint(1) DEFAULT '0',
  `message_count` int unsigned DEFAULT '0',
  `external_conversation_id` varchar(64) DEFAULT NULL COMMENT '第三方平台（如 Dify）自己维护的会话ID，用于保持上下文连续',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `model_id` (`model_id`),
  KEY `idx_user_updated` (`user_id`,`updated_at` DESC),
  CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`model_id`) REFERENCES `ai_models` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `conversations`
--

LOCK TABLES `conversations` WRITE;
/*!40000 ALTER TABLE `conversations` DISABLE KEYS */;
INSERT INTO `conversations` VALUES (1,1,'新对话',1,0,0,NULL,'2026-08-03 02:58:18','2026-08-03 02:58:18',NULL);
/*!40000 ALTER TABLE `conversations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `departments`
--

DROP TABLE IF EXISTS `departments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `departments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `parent_id` int unsigned DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `departments`
--

LOCK TABLES `departments` WRITE;
/*!40000 ALTER TABLE `departments` DISABLE KEYS */;
INSERT INTO `departments` VALUES (1,'总公司',NULL,0,'2026-08-03 02:38:43','2026-08-03 02:53:39');
/*!40000 ALTER TABLE `departments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `membership_levels`
--

DROP TABLE IF EXISTS `membership_levels`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `membership_levels` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `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 DEFAULT NULL COMMENT '允许使用的模型ID列表，null表示全部',
  `permissions` json DEFAULT NULL COMMENT '额外权限配置',
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `membership_levels`
--

LOCK TABLES `membership_levels` WRITE;
/*!40000 ALTER TABLE `membership_levels` DISABLE KEYS */;
INSERT INTO `membership_levels` VALUES (1,'免费用户','free',20,50,5,NULL,'{\"can_use_voice\": false, \"can_upload_file\": false, \"can_upload_image\": true, \"can_upload_video\": false}',0,'2026-08-03 02:38:43','2026-08-03 02:53:39'),(2,'高级会员','premium',200,500,50,NULL,'{\"can_use_voice\": true, \"can_upload_file\": true, \"can_upload_image\": true, \"can_upload_video\": true}',0,'2026-08-03 02:38:43','2026-08-03 02:38:43'),(3,'管理员','admin',9999,9999,100,NULL,'{\"can_use_voice\": true, \"can_upload_file\": true, \"can_upload_image\": true, \"can_upload_video\": true}',0,'2026-08-03 02:38:43','2026-08-03 02:53:39');
/*!40000 ALTER TABLE `membership_levels` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `messages`
--

DROP TABLE IF EXISTS `messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `messages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `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 DEFAULT NULL COMMENT '[{type,url,name,size,mime}]',
  `tokens_used` int unsigned DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_conversation` (`conversation_id`,`created_at`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `messages`
--

LOCK TABLES `messages` WRITE;
/*!40000 ALTER TABLE `messages` DISABLE KEYS */;
/*!40000 ALTER TABLE `messages` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `roles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `permissions` json DEFAULT NULL COMMENT '后台权限配置',
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `roles`
--

LOCK TABLES `roles` WRITE;
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
INSERT INTO `roles` VALUES (1,'超级管理员','super_admin','{\"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_access_admin\": true, \"can_manage_roles\": true, \"can_manage_users\": true, \"can_manage_models\": true, \"can_manage_settings\": true, \"can_manage_departments\": true, \"can_manage_memberships\": true, \"can_view_all_conversations\": true, \"can_view_subordinate_conversations\": true}',1,'2026-08-03 02:38:43','2026-08-03 02:53:39'),(2,'部门管理员','dept_manager','{\"dirs\": [\"dir:overview\", \"dir:business\"], \"menus\": [\"menu:dashboard\", \"menu:conversations\"], \"buttons\": [\"btn:conv:view_subordinate\"], \"can_access_admin\": true, \"can_manage_roles\": false, \"can_manage_users\": false, \"can_manage_models\": false, \"can_manage_settings\": false, \"can_manage_departments\": false, \"can_manage_memberships\": false, \"can_view_all_conversations\": false, \"can_view_subordinate_conversations\": true}',2,'2026-08-03 02:38:43','2026-08-03 02:53:39'),(3,'普通用户','user','{\"dirs\": [], \"menus\": [], \"buttons\": [], \"can_access_admin\": false, \"can_manage_roles\": false, \"can_manage_users\": false, \"can_manage_models\": false, \"can_manage_settings\": false, \"can_manage_departments\": false, \"can_manage_memberships\": false, \"can_view_all_conversations\": false, \"can_view_subordinate_conversations\": false}',3,'2026-08-03 02:38:43','2026-08-03 02:53:39');
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `system_settings`
--

DROP TABLE IF EXISTS `system_settings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `system_settings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `system_settings`
--

LOCK TABLES `system_settings` WRITE;
/*!40000 ALTER TABLE `system_settings` DISABLE KEYS */;
INSERT INTO `system_settings` VALUES (1,'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}','功能开关','2026-08-03 02:53:39'),(2,'site_name','AI Chat','站点名称','2026-08-03 02:53:39'),(3,'allow_register','true','是否允许注册','2026-08-03 02:53:39');
/*!40000 ALTER TABLE `system_settings` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `uploads`
--

DROP TABLE IF EXISTS `uploads`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `uploads` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `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 NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `uploads_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `uploads`
--

LOCK TABLES `uploads` WRITE;
/*!40000 ALTER TABLE `uploads` DISABLE KEYS */;
/*!40000 ALTER TABLE `uploads` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_daily_stats`
--

DROP TABLE IF EXISTS `user_daily_stats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_daily_stats` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `stat_date` date NOT NULL,
  `message_count` int unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_date` (`user_id`,`stat_date`),
  CONSTRAINT `user_daily_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_daily_stats`
--

LOCK TABLES `user_daily_stats` WRITE;
/*!40000 ALTER TABLE `user_daily_stats` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_daily_stats` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `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 DEFAULT NULL,
  `department_id` int unsigned DEFAULT NULL,
  `membership_level_id` int unsigned DEFAULT '1',
  `status` enum('active','disabled') DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `membership_level_id` (`membership_level_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`membership_level_id`) REFERENCES `membership_levels` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'admin','admin@example.com','$2y$10$VJCJHjJoAdUAENQZzLgKO.DrJaVsquBXGWSg5ok3u2FHAKRkVMWX.','管理员',NULL,'admin',1,1,3,'active','2026-08-03 02:55:11','2026-08-03 02:38:43','2026-08-03 02:55:11'),(2,'guest_c4772e915f8bde2132119237b2049ee3','guest_c4772e915f8bde2132119237b2049ee3@guest.local','$2y$10$zOSDCypCQL..1FENZ8p/..tyNFr1gGA8dnh8XFnqmGte5qVJu9VAe','访客',NULL,'user',3,NULL,1,'active',NULL,'2026-08-03 02:48:18','2026-08-03 02:48:18');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'ai_chat'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-08-04  9:36:16