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: 24/03/2026 13:54:04
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for asset_purchase_logs
-- ----------------------------
DROP TABLE IF EXISTS `asset_purchase_logs`;
CREATE TABLE `asset_purchase_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
  `quantity` int(10) unsigned NOT NULL COMMENT '购买数量',
  `unit_price_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '单价快照-能量石(保留2位小数)',
  `unit_price_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '单价快照-水晶石(保留2位小数)',
  `cost_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '总花费-能量石(保留2位小数)',
  `cost_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '总花费-水晶石(保留2位小数)',
  `item_name_snapshot` varchar(100) NOT NULL COMMENT '商品名快照',
  `image_name_snapshot` varchar(255) NOT NULL COMMENT '图片名称快照',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户购买流水表';

-- ----------------------------
-- 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 chatbot_layout_items
-- ----------------------------
DROP TABLE IF EXISTS `chatbot_layout_items`;
CREATE TABLE `chatbot_layout_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `layout_id` bigint(20) unsigned NOT NULL COMMENT '/chatbot布局ID(业务字段)',
  `user_asset_id` bigint(20) unsigned NOT NULL COMMENT '用户资产ID(业务字段)',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '商品ID(冗余便于查询)',
  `placement_type` enum('wall','floor') NOT NULL COMMENT '区域类型：墙/地',
  `start_row` tinyint(3) unsigned NOT NULL COMMENT '起始行(从1开始)',
  `start_col` tinyint(3) unsigned NOT NULL COMMENT '起始列(从1开始)',
  `size_rows` tinyint(3) unsigned NOT NULL COMMENT '占用行数',
  `size_cols` tinyint(3) unsigned NOT NULL COMMENT '占用列数',
  `z_index` int(11) NOT NULL DEFAULT '0' COMMENT '渲染层级(用于排序)',
  `image_name_snapshot` varchar(255) NOT NULL COMMENT '图片名称快照',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COMMENT='/chatbot布局明细表';

-- ----------------------------
-- Table structure for chatbot_layouts
-- ----------------------------
DROP TABLE IF EXISTS `chatbot_layouts`;
CREATE TABLE `chatbot_layouts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `grid_rows` tinyint(3) unsigned NOT NULL DEFAULT '5' COMMENT '固定：5行',
  `grid_cols` tinyint(3) unsigned NOT NULL DEFAULT '4' COMMENT '固定：4列',
  `cell_width` smallint(5) unsigned NOT NULL DEFAULT '96' COMMENT '固定：96',
  `cell_height` smallint(5) unsigned NOT NULL DEFAULT '96' COMMENT '固定：96',
  `design_width` smallint(5) unsigned NOT NULL DEFAULT '393' COMMENT '设计宽(393)',
  `design_height` smallint(5) unsigned NOT NULL DEFAULT '852' COMMENT '设计高(852)',
  `decor_area_width` smallint(5) unsigned NOT NULL DEFAULT '393' COMMENT '装饰区宽(393)',
  `decor_area_height` smallint(5) unsigned NOT NULL DEFAULT '482' COMMENT '装饰区高(482)',
  `version` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '布局版本(可选)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COMMENT='/chatbot布局主表(仅最新)';

-- ----------------------------
-- 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=346 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=99 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='对话总结表';

-- ----------------------------
-- Table structure for curr_daily
-- ----------------------------
DROP TABLE IF EXISTS `curr_daily`;
CREATE TABLE `curr_daily` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `activity_date` date NOT NULL COMMENT '统计日期(当日)',
  `prev_day_active_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '昨日活跃用户数',
  `retained_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '昨日活跃中今日仍活跃人数',
  `curr_rate_pct` decimal(5,2) DEFAULT NULL COMMENT 'CURR百分比(retained/prev*100)，昨日无活跃为NULL',
  `total_active_today` int(10) unsigned DEFAULT NULL COMMENT '当日DAU(可选)',
  `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_activity_date` (`activity_date`),
  KEY `idx_activity_date` (`activity_date`)
) ENGINE=InnoDB AUTO_INCREMENT=450 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='CURR按日统计表';

-- ----------------------------
-- 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=1214 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=2436 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=70 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 organizations
-- ----------------------------
DROP TABLE IF EXISTS `organizations`;
CREATE TABLE `organizations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `org_id` varchar(64) NOT NULL COMMENT '组织唯一标识',
  `org_name` varchar(200) NOT NULL COMMENT '组织名称',
  `org_type` varchar(32) DEFAULT NULL COMMENT '组织类型：school/institution/internal等',
  `region` varchar(32) 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 `uk_org_id` (`org_id`),
  KEY `idx_org_id` (`org_id`),
  KEY `idx_org_type` (`org_type`),
  KEY `idx_org_region` (`region`)
) ENGINE=InnoDB 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=2454 DEFAULT CHARSET=utf8mb4 COMMENT='页面访问日志表';

-- ----------------------------
-- Table structure for purchase_order_items
-- ----------------------------
DROP TABLE IF EXISTS `purchase_order_items`;
CREATE TABLE `purchase_order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` bigint(20) unsigned NOT NULL COMMENT '订单ID(业务字段)',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
  `quantity` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '购买数量',
  `unit_price_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '单价快照-能量石(保留2位小数)',
  `unit_price_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '单价快照-水晶石(保留2位小数)',
  `cost_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '花费快照-能量石(保留2位小数)',
  `cost_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '花费快照-水晶石(保留2位小数)',
  `item_name_snapshot` varchar(100) NOT NULL COMMENT '商品名快照',
  `image_name_snapshot` varchar(255) NOT NULL COMMENT '图片名称快照',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购买订单明细表';

-- ----------------------------
-- Table structure for purchase_orders
-- ----------------------------
DROP TABLE IF EXISTS `purchase_orders`;
CREATE TABLE `purchase_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `cart_id` bigint(20) unsigned NOT NULL COMMENT '结算来源购物车ID(业务字段)',
  `status` enum('created','completed','failed','cancelled') NOT NULL DEFAULT 'created' COMMENT '订单状态',
  `total_quantity` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单总数量(便于展示)',
  `total_cost_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '能量石总花费(保留2位小数)',
  `total_cost_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '水晶石总花费(保留2位小数)',
  `completed_at` datetime DEFAULT NULL COMMENT '完成时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 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 shop_items
-- ----------------------------
DROP TABLE IF EXISTS `shop_items`;
CREATE TABLE `shop_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `item_code` varchar(64) NOT NULL COMMENT '商品编码(业务唯一)',
  `name` varchar(100) NOT NULL COMMENT '商品名称',
  `item_type` enum('furniture','clothing','backdrop') NOT NULL COMMENT '商品类型',
  `placement_type` enum('wall','floor','backdrop','clothing') NOT NULL COMMENT '摆放/使用类型',
  `image_name` varchar(255) NOT NULL COMMENT '图片名称',
  `size_rows` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '占用行数(家具用)',
  `size_cols` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '占用列数(家具用)',
  `price_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '能量石价格(保留2位小数)',
  `price_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '水晶石价格(保留2位小数)',
  `default_z_index` int(11) NOT NULL DEFAULT '0' COMMENT '默认渲染层级',
  `scene_anchor_type` enum('none','seat','window') NOT NULL DEFAULT 'none' COMMENT '场景锚点类型(none=非锚点,seat=凳子/沙发,window=窗户)',
  `stock_mode` enum('unlimited') NOT NULL DEFAULT 'unlimited' COMMENT '库存模式(当前仅无限)',
  `repeat_purchase_mode` enum('single','repeatable') NOT NULL DEFAULT 'single' COMMENT '是否可重复购买',
  `max_per_user` int(10) unsigned DEFAULT NULL COMMENT '每用户最多可购数量(NULL=不限制)',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否上架',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '前端展示排序',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_shop_items_scene_anchor_type` (`scene_anchor_type`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COMMENT='系统商品表';

-- ----------------------------
-- Table structure for shop_items_bak_20260324
-- ----------------------------
DROP TABLE IF EXISTS `shop_items_bak_20260324`;
CREATE TABLE `shop_items_bak_20260324` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '主键',
  `item_code` varchar(64) CHARACTER SET utf8mb4 NOT NULL COMMENT '商品编码(业务唯一)',
  `name` varchar(100) CHARACTER SET utf8mb4 NOT NULL COMMENT '商品名称',
  `item_type` enum('furniture','clothing','backdrop') CHARACTER SET utf8mb4 NOT NULL COMMENT '商品类型',
  `placement_type` enum('wall','floor','backdrop','clothing') CHARACTER SET utf8mb4 NOT NULL COMMENT '摆放/使用类型',
  `image_name` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '图片名称',
  `size_rows` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '占用行数(家具用)',
  `size_cols` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '占用列数(家具用)',
  `price_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '能量石价格(保留2位小数)',
  `price_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '水晶石价格(保留2位小数)',
  `default_z_index` int(11) NOT NULL DEFAULT '0' COMMENT '默认渲染层级',
  `stock_mode` enum('unlimited') CHARACTER SET utf8mb4 NOT NULL DEFAULT 'unlimited' COMMENT '库存模式(当前仅无限)',
  `repeat_purchase_mode` enum('single','repeatable') CHARACTER SET utf8mb4 NOT NULL DEFAULT 'single' COMMENT '是否可重复购买',
  `max_per_user` int(10) unsigned DEFAULT NULL COMMENT '每用户最多可购数量(NULL=不限制)',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否上架',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '前端展示排序',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for shopping_cart_items
-- ----------------------------
DROP TABLE IF EXISTS `shopping_cart_items`;
CREATE TABLE `shopping_cart_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cart_id` bigint(20) unsigned NOT NULL COMMENT '购物车ID',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
  `quantity` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '购物车数量(支持部分结算后递减)',
  `unit_price_energy` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '加购时能量石单价快照(保留2位小数)',
  `unit_price_crystal` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '加购时水晶石单价快照(保留2位小数)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车明细表';

-- ----------------------------
-- Table structure for shopping_carts
-- ----------------------------
DROP TABLE IF EXISTS `shopping_carts`;
CREATE TABLE `shopping_carts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车主表';

-- ----------------------------
-- Table structure for user_assets
-- ----------------------------
DROP TABLE IF EXISTS `user_assets`;
CREATE TABLE `user_assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
  `owned_qty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '拥有数量',
  `used_qty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '已使用数量(摆放/穿戴等)',
  `last_acquired_at` datetime DEFAULT NULL COMMENT '最近获取时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=784 DEFAULT CHARSET=utf8mb4 COMMENT='用户资产表';

-- ----------------------------
-- 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=302 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=234 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_chatbot_backdrops
-- ----------------------------
DROP TABLE IF EXISTS `user_chatbot_backdrops`;
CREATE TABLE `user_chatbot_backdrops` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `backdrop_item_id` bigint(20) unsigned NOT NULL COMMENT '幕布商品ID(来源shop_items)',
  `z_index` int(11) NOT NULL DEFAULT '0' COMMENT '渲染层级',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COMMENT='用户当前/chatbot背景幕布表';

-- ----------------------------
-- 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=81 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户是否退出早期共创社区';

-- ----------------------------
-- Table structure for user_daily_activity
-- ----------------------------
DROP TABLE IF EXISTS `user_daily_activity`;
CREATE TABLE `user_daily_activity` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `activity_date` date NOT NULL COMMENT '统计日期(UTC日)',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID',
  `active_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '当天是否有≥1次活跃判定事件：1=是',
  `session_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天去重session数(基于活跃事件)',
  `message_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天用户消息条数(role=user)',
  `first_seen_date` date DEFAULT NULL COMMENT '用户首次出现日期(来自users.created_at，首次写入后不覆盖)',
  `user_segment_snapshot` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'users.user_segment快照',
  `org_id_snapshot` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'users.org_id快照',
  `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_activity_date_user` (`activity_date`,`user_id`),
  KEY `idx_activity_date` (`activity_date`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_first_seen_date` (`first_seen_date`)
) ENGINE=InnoDB AUTO_INCREMENT=417 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=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户早期共创社区（手机号新用户留邮箱）';

-- ----------------------------
-- Table structure for user_events
-- ----------------------------
DROP TABLE IF EXISTS `user_events`;
CREATE TABLE `user_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `event_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件ID（客户端或服务端生成，唯一）',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户ID(关联 users.user_id)',
  `event_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件代码，如 page.chatbot__enter（遵循埋点命名规范）',
  `event_label` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '事件中文名称/描述（如“进入聊天页”、“欢迎页开始按钮点击”）',
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '事件发生时间（UTC）',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会话ID，同一次前台停留共享',
  `platform` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '平台：ios/android/web',
  `app_version` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '应用版本号，如 v0.1.x',
  `timezone_offset_minutes` int(11) DEFAULT NULL COMMENT '用户本地相对UTC的分钟偏移量（分钟）',
  `properties` json DEFAULT NULL COMMENT '扩展属性（page、entry、button_id、duration_seconds 等）',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_event_id` (`event_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_event_time` (`event_time`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_user_event_time` (`user_id`,`event_time`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_event_time_name` (`event_time`,`event_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2261 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=190 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 AUTO_INCREMENT=23 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=214 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 '用户类型',
  `user_segment` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'individual' COMMENT '用户/场景分群：individual/school/internal等',
  `org_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '组织ID(关联 organizations.org_id)',
  `is_test_user` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否测试账号',
  `region` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '国家/地区（如 CN、US）',
  `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`),
  KEY `idx_user_segment` (`user_segment`),
  KEY `idx_org_id` (`org_id`),
  KEY `idx_is_test_user` (`is_test_user`),
  KEY `idx_region` (`region`)
) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ----------------------------
-- Table structure for users_copy1
-- ----------------------------
DROP TABLE IF EXISTS `users_copy1`;
CREATE TABLE `users_copy1` (
  `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 '用户类型',
  `user_segment` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'individual' COMMENT '用户/场景分群：individual/school/internal等',
  `org_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '组织ID(关联 organizations.org_id)',
  `is_test_user` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否测试账号',
  `region` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '国家/地区（如 CN、US）',
  `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`),
  KEY `idx_user_segment` (`user_segment`),
  KEY `idx_org_id` (`org_id`),
  KEY `idx_is_test_user` (`is_test_user`),
  KEY `idx_region` (`region`)
) ENGINE=InnoDB AUTO_INCREMENT=208 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=310 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='验证码表';

SET FOREIGN_KEY_CHECKS = 1;