Tolan/backend/migrations/add_user_segment_org_and_user_events.sql

/*
  用户分群与事件表改造 SQL（MySQL 5.7）
  - 为 users 表增加分群与组织等字段
  - 新增 organizations 组织表
  - 新增 user_events 用户事件表
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. users 表新增字段（用户分群 / 组织 / 测试账号 / 地区）
ALTER TABLE `users`
  ADD COLUMN `user_segment` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'individual' COMMENT '用户/场景分群：individual/school/internal等' AFTER `user_type`,
  ADD COLUMN `org_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '组织ID(关联 organizations.org_id)' AFTER `user_segment`,
  ADD COLUMN `is_test_user` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否测试账号' AFTER `org_id`,
  ADD COLUMN `region` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '国家/地区（如 CN、US）' AFTER `is_test_user`,
  ADD KEY `idx_user_segment` (`user_segment`),
  ADD KEY `idx_org_id` (`org_id`),
  ADD KEY `idx_is_test_user` (`is_test_user`),
  ADD KEY `idx_region` (`region`);

-- 2. 新增组织表 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='组织表';

-- 3. 新增用户事件表 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) NOT NULL COMMENT '事件ID（客户端或服务端生成，唯一）',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID(关联 users.user_id)',
  `event_name` varchar(64) NOT NULL COMMENT '事件代码，如 page.chatbot__enter（遵循埋点命名规范）',
  `event_label` varchar(128) DEFAULT NULL COMMENT '事件中文名称/描述（如“进入聊天页”、“欢迎页开始按钮点击”）',
  `event_time` timestamp NOT NULL COMMENT '事件发生时间（UTC）',
  `session_id` varchar(64) DEFAULT NULL COMMENT '会话ID，同一次前台停留共享',
  `platform` varchar(16) NOT NULL COMMENT '平台：ios/android/web',
  `app_version` varchar(32) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户事件表';

SET FOREIGN_KEY_CHECKS = 1;