Tolan/Lyrria.sql

/*
 Navicat Premium Dump SQL

 Source Server         : 101.201.60.170 Lyrria@Root@123456
 Source Server Type    : MySQL
 Source Server Version : 50739 (5.7.39)
 Source Host           : 101.201.60.170:13306
 Source Schema         : Lyrria

 Target Server Type    : MySQL
 Target Server Version : 50739 (5.7.39)
 File Encoding         : 65001

 Date: 02/03/2026 16:00:47
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for avatar_assets
-- ----------------------------
DROP TABLE IF EXISTS `avatar_assets`;
CREATE TABLE `avatar_assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `avatar_id` varchar(64) NOT NULL COMMENT '形象ID',
  `state` enum('default','positive','negative','talking','idle','sleep') NOT NULL DEFAULT 'default' COMMENT '动作/情绪状态',
  `image_url` varchar(500) NOT NULL COMMENT 'GIF 资源 URL',
  `thumbnail_url` varchar(500) DEFAULT NULL COMMENT '缩略图URL',
  `metadata` json DEFAULT NULL COMMENT '扩展配置',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_avatar_asset_state` (`avatar_id`,`state`),
  KEY `idx_avatar_asset_avatar_id` (`avatar_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='形象动作资源表';

-- ----------------------------
-- Table structure for avatars
-- ----------------------------
DROP TABLE IF EXISTS `avatars`;
CREATE TABLE `avatars` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `avatar_id` varchar(64) NOT NULL COMMENT '形象唯一标识',
  `avatar_name` varchar(100) NOT NULL COMMENT '形象名称',
  `description` varchar(255) DEFAULT NULL COMMENT '形象描述',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `metadata` json DEFAULT NULL COMMENT '扩展配置',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `avatar_id` (`avatar_id`),
  KEY `idx_avatar_is_active` (`is_active`),
  KEY `idx_avatar_id` (`avatar_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='形象配置表';

-- ----------------------------
-- Table structure for background_images
-- ----------------------------
DROP TABLE IF EXISTS `background_images`;
CREATE TABLE `background_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `image_id` varchar(64) NOT NULL COMMENT '背景图片唯一标识',
  `image_name` varchar(100) NOT NULL COMMENT '背景图片名称',
  `image_url` varchar(500) NOT NULL COMMENT '背景图片URL',
  `thumbnail_url` varchar(500) DEFAULT NULL COMMENT '缩略图URL',
  `unlock_type` enum('default','level','purchase','mission') NOT NULL DEFAULT 'default' COMMENT '解锁类型',
  `unlock_level` int(11) DEFAULT NULL COMMENT '解锁所需等级（unlock_type=level时）',
  `unlock_mission_type` varchar(64) DEFAULT NULL COMMENT '解锁所需任务类型（unlock_type=mission时）',
  `display_order` int(11) NOT NULL DEFAULT '0' COMMENT '显示顺序',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `description` varchar(255) DEFAULT NULL COMMENT '背景图片描述',
  `metadata` json DEFAULT NULL COMMENT '扩展配置',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `image_id` (`image_id`),
  KEY `idx_image_id` (`image_id`),
  KEY `idx_unlock_type` (`unlock_type`),
  KEY `idx_display_order` (`display_order`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='背景图片配置表';

-- ----------------------------
-- Table structure for conversation_energy_stones
-- ----------------------------
DROP TABLE IF EXISTS `conversation_energy_stones`;
CREATE TABLE `conversation_energy_stones` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stone_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '能量石ID',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `request_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '触发的请求ID',
  `threshold_index` int(11) NOT NULL COMMENT '达成阈值的序号(1=30秒,2=60秒...)',
  `total_duration_ms` bigint(20) NOT NULL COMMENT '累计说话时长（毫秒）',
  `duration_increment_ms` int(11) NOT NULL COMMENT '本次新增时长（毫秒）',
  `appear_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '出现时间',
  `clicked_at` timestamp NULL DEFAULT NULL COMMENT '点击时间',
  `auto_removed` tinyint(1) DEFAULT '0' COMMENT '是否自动消失',
  `metadata` json DEFAULT NULL COMMENT '扩展信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stone_id` (`stone_id`),
  UNIQUE KEY `uk_conversation_stone_threshold` (`user_id`,`threshold_index`),
  KEY `idx_stone_id` (`stone_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_conversation_stone_user_threshold` (`user_id`,`threshold_index`),
  KEY `idx_appear_at` (`appear_at`)
) ENGINE=InnoDB AUTO_INCREMENT=275 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='对话能量石表（按时长发放）';

-- ----------------------------
-- Table structure for conversation_summaries
-- ----------------------------
DROP TABLE IF EXISTS `conversation_summaries`;
CREATE TABLE `conversation_summaries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `summary_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '总结ID',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `start_round` int(11) NOT NULL COMMENT '起始轮次',
  `end_round` int(11) NOT NULL COMMENT '结束轮次',
  `summary_text` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '总结内容',
  `summary_type` enum('auto','manual') COLLATE utf8mb4_unicode_ci DEFAULT 'auto' COMMENT '总结类型',
  `message_count` int(11) DEFAULT NULL COMMENT '总结的消息数量',
  `token_count` int(11) DEFAULT NULL COMMENT '总结文本Token数',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `summary_id` (`summary_id`) USING BTREE,
  UNIQUE KEY `uk_user_round_range` (`user_id`,`start_round`,`end_round`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_user_end_round` (`user_id`,`end_round`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='对话总结表';

-- ----------------------------
-- Table structure for feedbacks
-- ----------------------------
DROP TABLE IF EXISTS `feedbacks`;
CREATE TABLE `feedbacks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `feedback_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '反馈ID',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '反馈内容',
  `status` enum('pending','reviewed','resolved','closed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending' COMMENT '反馈状态',
  `metadata` json DEFAULT NULL COMMENT '扩展信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `feedback_id` (`feedback_id`),
  KEY `idx_feedback_id` (`feedback_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_feedback_user_created` (`user_id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户反馈表';

-- ----------------------------
-- Table structure for llm_prompts
-- ----------------------------
DROP TABLE IF EXISTS `llm_prompts`;
CREATE TABLE `llm_prompts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `prompt_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '提示词记录ID',
  `message_id` bigint(20) NOT NULL COMMENT '关联的消息ID',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `round_number` int(11) NOT NULL COMMENT '对话轮次',
  `system_prompt` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '系统提示词（完整内容）',
  `runtime_context` text COLLATE utf8mb4_unicode_ci COMMENT '运行时上下文',
  `messages_json` json NOT NULL COMMENT '完整的messages数组（JSON格式）',
  `model_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '模型名称',
  `temperature` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '温度参数',
  `max_tokens` int(11) DEFAULT NULL COMMENT '最大Token数',
  `stream` tinyint(1) DEFAULT '1' COMMENT '是否流式输出',
  `prompt_token_count` int(11) DEFAULT NULL COMMENT '提示词Token数',
  `total_token_count` int(11) DEFAULT NULL COMMENT '总Token数（包含响应）',
  `metadata_json` json DEFAULT NULL COMMENT '扩展信息（调用耗时、错误信息等）',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `prompt_id` (`prompt_id`),
  KEY `idx_prompt_id` (`prompt_id`),
  KEY `idx_llm_prompt_message` (`message_id`),
  KEY `idx_llm_prompt_user_round` (`user_id`,`round_number`),
  KEY `idx_llm_prompt_created` (`created_at`),
  CONSTRAINT `llm_prompts_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=991 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='LLM提示词记录表';

-- ----------------------------
-- Table structure for messages
-- ----------------------------
DROP TABLE IF EXISTS `messages`;
CREATE TABLE `messages` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `message_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息ID',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `role` enum('user','assistant','system') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色',
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息内容',
  `round_number` int(11) NOT NULL COMMENT '对话轮次(从1开始)',
  `emotion` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '情感标签',
  `message_metadata` json DEFAULT NULL COMMENT '扩展信息(音频路径、识别耗时等)',
  `token_count` int(11) DEFAULT NULL COMMENT 'Token数量',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `message_id` (`message_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_user_round` (`user_id`,`round_number`) USING BTREE,
  KEY `idx_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1988 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息表';

-- ----------------------------
-- Table structure for mission_energy_stones
-- ----------------------------
DROP TABLE IF EXISTS `mission_energy_stones`;
CREATE TABLE `mission_energy_stones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `stone_id` varchar(64) NOT NULL COMMENT '能量石ID',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID',
  `request_id` varchar(64) DEFAULT NULL COMMENT '触发请求ID',
  `appear_reason` varchar(64) NOT NULL DEFAULT 'first_dialog' COMMENT '触发原因',
  `mission_type` varchar(64) NOT NULL DEFAULT 'first_dialog' COMMENT '任务类型',
  `appear_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '出现时间',
  `clicked_at` timestamp NULL DEFAULT NULL COMMENT '点击时间',
  `auto_removed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否系统自动消失',
  `metadata` json DEFAULT NULL COMMENT '扩展信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_mission_stone_id` (`stone_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COMMENT='任务能量石记录';

-- ----------------------------
-- Table structure for mission_types
-- ----------------------------
DROP TABLE IF EXISTS `mission_types`;
CREATE TABLE `mission_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `type_key` varchar(64) NOT NULL COMMENT '任务类型唯一标识',
  `display_name` varchar(100) NOT NULL COMMENT '显示名称',
  `description` varchar(255) DEFAULT NULL COMMENT '类型描述',
  `is_repeatable` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否允许重复触发',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `metadata` json DEFAULT NULL COMMENT '扩展配置',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_mission_type_key` (`type_key`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='任务类型配置';

-- ----------------------------
-- Table structure for page_visit_logs
-- ----------------------------
DROP TABLE IF EXISTS `page_visit_logs`;
CREATE TABLE `page_visit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(64) DEFAULT NULL COMMENT '用户ID(未登录则为空)',
  `path` varchar(512) NOT NULL COMMENT '访问路径',
  `visited_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间',
  PRIMARY KEY (`id`),
  KEY `idx_page_visit_user_id` (`user_id`),
  KEY `idx_page_visit_path` (`path`(255)),
  KEY `idx_page_visit_visited_at` (`visited_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2238 DEFAULT CHARSET=utf8mb4 COMMENT='页面访问日志表';

-- ----------------------------
-- Table structure for share_images
-- ----------------------------
DROP TABLE IF EXISTS `share_images`;
CREATE TABLE `share_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(64) NOT NULL,
  `url` text NOT NULL,
  `title` varchar(128) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_share_images_sort` (`sort_order`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for user_avatars
-- ----------------------------
DROP TABLE IF EXISTS `user_avatars`;
CREATE TABLE `user_avatars` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID',
  `avatar_id` varchar(64) NOT NULL COMMENT '形象ID',
  `obtained_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '获得时间',
  `obtained_type` enum('default','register','level_up','mission','purchase','gift','event') NOT NULL DEFAULT 'default' COMMENT '获得方式',
  `obtained_source` varchar(64) DEFAULT NULL COMMENT '获得来源（等级/任务/订单等）',
  `metadata` json DEFAULT NULL COMMENT '扩展信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_avatar` (`user_id`,`avatar_id`),
  KEY `idx_user_avatar_user_obtained` (`user_id`,`obtained_at`),
  KEY `idx_user_avatar_avatar_id` (`avatar_id`)
) ENGINE=InnoDB AUTO_INCREMENT=258 DEFAULT CHARSET=utf8mb4 COMMENT='用户已拥有的形象';

-- ----------------------------
-- Table structure for user_background_images
-- ----------------------------
DROP TABLE IF EXISTS `user_background_images`;
CREATE TABLE `user_background_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID',
  `image_id` varchar(64) NOT NULL COMMENT '背景图片ID',
  `obtained_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '获得时间',
  `obtained_type` enum('default','level_up','purchase','mission','gift') NOT NULL DEFAULT 'default' COMMENT '获得方式',
  `obtained_source` varchar(64) DEFAULT NULL COMMENT '获得来源（任务类型、等级等）',
  `metadata` json DEFAULT NULL COMMENT '扩展信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_background_image` (`user_id`,`image_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_image_id` (`image_id`),
  KEY `idx_user_obtained` (`user_id`,`obtained_at`)
) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8mb4 COMMENT='用户背景图片关联表';

-- ----------------------------
-- Table structure for user_bonus_energy_stones
-- ----------------------------
DROP TABLE IF EXISTS `user_bonus_energy_stones`;
CREATE TABLE `user_bonus_energy_stones` (
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID(关联 users.user_id)',
  `bonus_conversation_count` int(11) NOT NULL DEFAULT '0' COMMENT '赠送的对话能量石个数',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户赠送对话能量石数量表';

-- ----------------------------
-- Table structure for user_community_choice
-- ----------------------------
DROP TABLE IF EXISTS `user_community_choice`;
CREATE TABLE `user_community_choice` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `opted_out` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否选择退出早期共创社区',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户是否退出早期共创社区';

-- ----------------------------
-- Table structure for user_early_community
-- ----------------------------
DROP TABLE IF EXISTS `user_early_community`;
CREATE TABLE `user_early_community` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `email` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '加入早期共创时留的邮箱',
  `popup_shown` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示过弹窗',
  `joined_at` timestamp NULL DEFAULT NULL COMMENT '加入时间（留邮箱时）',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户早期共创社区（手机号新用户留邮箱）';

-- ----------------------------
-- Table structure for user_intimacy
-- ----------------------------
DROP TABLE IF EXISTS `user_intimacy`;
CREATE TABLE `user_intimacy` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID',
  `heart_count` int(11) NOT NULL DEFAULT '3' COMMENT '亲密度心数 0-3',
  `last_visit_date_at_deduction` varchar(10) DEFAULT NULL COMMENT '上次扣心所依据的最后访问日期 YYYY-MM-DD',
  `last_light_up_date` varchar(10) DEFAULT NULL COMMENT '上次手动点亮日期 YYYY-MM-DD，控制每天仅点亮一次',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_intimacy_user_id` (`user_id`),
  KEY `idx_user_intimacy_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=utf8mb4 COMMENT='用户亲密度表';

-- ----------------------------
-- Table structure for user_special_scenario_topic_logs
-- ----------------------------
DROP TABLE IF EXISTS `user_special_scenario_topic_logs`;
CREATE TABLE `user_special_scenario_topic_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID(关联 users.user_id)',
  `scenario_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '场景: window|reading|eating',
  `topic_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '话题键(如 weather|season|travel 等)',
  `conversed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '该场景该话题下首次对话时间',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_scenario_topic` (`user_id`,`scenario_key`,`topic_key`),
  KEY `idx_user_scenario` (`user_id`,`scenario_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户情景话题对话记录（用于概率排除与不再触发）';

-- ----------------------------
-- Table structure for user_ui_states
-- ----------------------------
DROP TABLE IF EXISTS `user_ui_states`;
CREATE TABLE `user_ui_states` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID(关联 users.user_id)',
  `current_page` varchar(64) DEFAULT NULL COMMENT '当前业务流程页面标识',
  `visited_routes` json DEFAULT NULL COMMENT '已访问路由列表',
  `has_shown_tap_to_chat` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示 TapToChat 提示',
  `has_shown_cancel_tooltip` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示取消按钮提示',
  `has_shown_send_tooltip` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示发送按钮提示',
  `has_shown_progress_bar_tooltip` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示进度条提示',
  `avatar_c_unlocked` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已解锁 Avatar-C',
  `has_shared_avatar` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已分享头像奖励',
  `current_avatar_id` varchar(64) DEFAULT NULL COMMENT '当前使用的形象ID(关联 avatars.avatar_id)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `has_shown_mission_stone_modal` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示过任务能量石弹窗',
  `has_shown_conversation_stone_modal` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已展示过对话能量石弹窗',
  `has_clicked_mission_stone` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已点击过任务能量石（控制光晕显示）',
  `has_clicked_conversation_stone` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已点击过对话能量石（控制光晕显示）',
  `subtitle_on` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否开启字幕显示',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx_user_ui_state_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=191 DEFAULT CHARSET=utf8mb4 COMMENT='用户前端可视化状态表';

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户唯一标识(UUID)',
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名/昵称',
  `user_type` enum('guest','registered') COLLATE utf8mb4_unicode_ci DEFAULT 'guest' COMMENT '用户类型',
  `area_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机区号（如 +86）',
  `bot_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户给机器人取的名字',
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号',
  `email` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `wechat_openid` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '微信OpenID',
  `qq_openid` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'QQ OpenID',
  `qq_unionid` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'QQ UnionID',
  `google_email` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Google邮箱',
  `is_verified` tinyint(1) DEFAULT '0' COMMENT '是否已认证',
  `registered_at` timestamp NULL DEFAULT NULL COMMENT '注册时间',
  `total_messages` int(11) DEFAULT '0' COMMENT '总消息数',
  `total_rounds` int(11) DEFAULT '0' COMMENT '总对话轮次',
  `total_speech_duration_ms` bigint(20) DEFAULT '0' COMMENT '累积说话时长（毫秒）',
  `user_level` int(11) NOT NULL DEFAULT '0' COMMENT '用户等级(0-5)',
  `preferred_language` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '首选语言（zh/en）',
  `last_active_at` timestamp NULL DEFAULT NULL COMMENT '最后活跃时间',
  `current_background_image_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前使用的背景图片ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `user_id` (`user_id`) USING BTREE,
  UNIQUE KEY `phone` (`phone`) USING BTREE,
  UNIQUE KEY `wechat_openid` (`wechat_openid`) USING BTREE,
  UNIQUE KEY `google_email` (`google_email`) USING BTREE,
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `qq_openid` (`qq_openid`),
  UNIQUE KEY `qq_unionid` (`qq_unionid`),
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_phone` (`phone`) USING BTREE,
  KEY `idx_wechat_openid` (`wechat_openid`) USING BTREE,
  KEY `idx_google_email` (`google_email`) USING BTREE,
  KEY `idx_user_type` (`user_type`) USING BTREE,
  KEY `idx_email` (`email`),
  KEY `idx_qq_openid` (`qq_openid`),
  KEY `idx_qq_unionid` (`qq_unionid`)
) ENGINE=InnoDB AUTO_INCREMENT=191 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ----------------------------
-- Table structure for verification_codes
-- ----------------------------
DROP TABLE IF EXISTS `verification_codes`;
CREATE TABLE `verification_codes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `code_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '验证码ID',
  `area_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机区号（如 +86）',
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号',
  `email` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '验证码',
  `send_type` enum('domestic_sms','international_sms','email') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '发送类型：国内短信、国外短信、邮箱',
  `is_sent` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已发送',
  `is_valid` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `sent_at` timestamp NULL DEFAULT NULL COMMENT '发送时间',
  `metadata` json DEFAULT NULL COMMENT '扩展信息（发送结果、错误信息等）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_id` (`code_id`),
  KEY `idx_code_id` (`code_id`),
  KEY `idx_verification_code_phone` (`phone`,`is_valid`,`created_at`),
  KEY `idx_verification_code_email` (`email`,`is_valid`,`created_at`),
  KEY `idx_verification_code_send_type` (`send_type`,`is_sent`,`is_valid`)
) ENGINE=InnoDB AUTO_INCREMENT=229 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='验证码表';

SET FOREIGN_KEY_CHECKS = 1;