caremate/train-backend/src/main/resources/schema.sql

-- 爱伴·AI之旅 训练营 数据库初始化脚本
-- 数据库: cfc 同库 (zxyj) —— 训练营运营表 train_* 与 cfc 会员/活动/账本同库共存
-- 训练营品牌：爱伴·AI之旅
-- 课程等级：L0/L1/L2/L3/L4；其中 L0 = 「家立方-AI管家成长营」（首期课程）

-- 课程表（L0~L4 课程主数据）
CREATE TABLE IF NOT EXISTS train_course (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level VARCHAR(20) NOT NULL COMMENT '课程等级：L0/L1/L2/L3/L4',
    track VARCHAR(20) COMMENT '学习路径/轨道：wealth/health/growth（模块化方向，空=主线通用课程如 L0）',
    name VARCHAR(100) NOT NULL COMMENT '课程名称（如 L0 = 家立方-AI管家成长营）',
    description VARCHAR(500) COMMENT '课程简介',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '手动覆盖状态：draft(下架)/active(强制上架)/finished(强制结束)',
    start_time DATETIME COMMENT '课程开始时间（状态自动切换依据）',
    end_time DATETIME COMMENT '课程结束时间（状态自动切换依据）',
    price INT DEFAULT 0 COMMENT '课程报名价（分，报名=报课程，价格挂在课程上）',
    member_price INT DEFAULT 0 COMMENT '课程会员价（分）',
    track VARCHAR(20) COMMENT '学习路径/轨道：wealth/health/growth（模块化方向，空=主线通用课程如L0）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_level (level),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='训练营课程（L0/L1/L2/L3/L4）';

-- 课程版块表（管理端维护：版块增删改/排序/启用）
CREATE TABLE IF NOT EXISTS train_course_section (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT NOT NULL COMMENT '所属课程ID（train_course.id）',
    title VARCHAR(100) NOT NULL COMMENT '版块标题（如：课前准备/课中学习/课后跟进）',
    description VARCHAR(500) COMMENT '版块说明',
    sort INT DEFAULT 0 COMMENT '排序（升序）',
    enabled TINYINT DEFAULT 1 COMMENT '1=启用，0=禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_course_id (course_id),
    INDEX idx_sort (sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程版块（管理端维护）';

-- 课程模块表（模块化教学：课程按模块组织内容，支持学习路径 track 归类与排序）
CREATE TABLE IF NOT EXISTS train_module (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT NOT NULL COMMENT '所属课程ID（train_course.id）',
    track VARCHAR(20) COMMENT '学习路径/轨道：wealth/health/growth（null=通用/无方向）',
    title VARCHAR(100) NOT NULL COMMENT '模块标题',
    description VARCHAR(500) COMMENT '模块简介',
    sort INT DEFAULT 0 COMMENT '排序（升序）',
    enabled TINYINT DEFAULT 1 COMMENT '1=启用，0=禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_course_id (course_id),
    INDEX idx_track (track),
    INDEX idx_sort (sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程模块（模块化教学）';

-- 班次表
CREATE TABLE IF NOT EXISTS train_class (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    activity_id BIGINT COMMENT '关联 cfc 活动 ID（activities.id，cfc 同库）',
    course_id BIGINT COMMENT '关联课程 ID（train_course.id，L0/L1/L2…）',
    name VARCHAR(100) NOT NULL COMMENT '班次名称',
    code VARCHAR(20) UNIQUE COMMENT '班级码（学员进班用）',
    time VARCHAR(100) COMMENT '培训时间描述',
    place VARCHAR(100) COMMENT '培训地点',
    capacity INT DEFAULT 20 COMMENT '容量',
    status VARCHAR(20) DEFAULT 'draft' COMMENT 'draft/active/finished',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_activity_id (activity_id),
    INDEX idx_course_id (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班次';

-- 学员用户表
CREATE TABLE IF NOT EXISTS train_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    cfc_user_id BIGINT COMMENT 'cfc 统一会员 ID（users.id，cfc 同库）',
    openid VARCHAR(64) COMMENT '微信openid',
    phone VARCHAR(11) COMMENT '手机号',
    name VARCHAR(50) COMMENT '姓名',
    alumni_verify VARCHAR(50) COMMENT '校友身份校验: accepted/pending/rejected',
    invite_code VARCHAR(50) COMMENT '专属邀请码',
    grade VARCHAR(50) COMMENT '校友届别',
    department VARCHAR(50) COMMENT '院系',
    city VARCHAR(50) COMMENT '城市',
    family_struct VARCHAR(100) COMMENT '家庭结构',
    concern_book VARCHAR(20) COMMENT '最关心的账本: wealth/health/growth',
    role VARCHAR(20) DEFAULT 'student' COMMENT 'student/teacher/assistant/admin',
    class_id BIGINT COMMENT '所属班次ID',
    status VARCHAR(20) DEFAULT 'active' COMMENT 'active/blocked',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_openid (openid),
    INDEX idx_phone (phone),
    INDEX idx_class_id (class_id),
    INDEX idx_cfc_user_id (cfc_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学员用户';

-- 兼容存量库：为新版本新增的 cfc 关联列补列（列已存在时静默跳过）
-- 下方语句由 DatabaseInitializer 逐条执行并容错，重复执行不报致命错误
ALTER TABLE train_class ADD COLUMN activity_id BIGINT COMMENT '关联 cfc 活动 ID（activities.id，cfc 同库）';
ALTER TABLE train_class ADD COLUMN course_id BIGINT COMMENT '关联课程 ID（train_course.id，L0/L1/L2…）';
ALTER TABLE train_user ADD COLUMN cfc_user_id BIGINT COMMENT 'cfc 统一会员 ID（users.id，cfc 同库）';
ALTER TABLE train_class ADD COLUMN start_at DATETIME COMMENT '课程开始时间（投屏计时基准，NULL=未开始）';
ALTER TABLE train_course ADD COLUMN start_time DATETIME COMMENT '课程开始时间（状态自动切换依据）';
ALTER TABLE train_course ADD COLUMN end_time DATETIME COMMENT '课程结束时间（状态自动切换依据）';
ALTER TABLE train_course ADD COLUMN price INT DEFAULT 0 COMMENT '课程报名价（分，报名=报课程，价格挂在课程上）';
ALTER TABLE train_course ADD COLUMN member_price INT DEFAULT 0 COMMENT '课程会员价（分）';
ALTER TABLE train_course ADD COLUMN track VARCHAR(20) COMMENT '学习路径/轨道：wealth/health/growth（模块化方向，空=主线通用课程如 L0）';
ALTER TABLE train_course ADD COLUMN track VARCHAR(20) COMMENT '学习路径/轨道：wealth/health/growth（模块化方向，空=主线通用课程如L0）';
ALTER TABLE train_enrollment ADD COLUMN course_id BIGINT COMMENT '报名所属课程 ID（train_course.id，报名=报课程；班次仅确定上课期次）';

-- 小组表
CREATE TABLE IF NOT EXISTS train_group (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    class_id BIGINT NOT NULL COMMENT '班次ID',
    name VARCHAR(50) NOT NULL COMMENT '组名（含数字+钱/身体/孩子之一）',
    slogan VARCHAR(100) COMMENT '8字口号',
    qr_code VARCHAR(255) COMMENT '二维码',
    spokesperson_uid BIGINT COMMENT '路演发言人（话最少的人，不可更换）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_class_id (class_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小组';

-- 组内成员表
CREATE TABLE IF NOT EXISTS train_group_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT NOT NULL COMMENT '组ID',
    uid BIGINT NOT NULL COMMENT '学员ID',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_group_id (group_id),
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='组内成员';

-- 分工表（三本账）
CREATE TABLE IF NOT EXISTS train_assignment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT NOT NULL COMMENT '组ID',
    uid BIGINT NOT NULL COMMENT '学员ID',
    book_type VARCHAR(20) NOT NULL COMMENT 'wealth/health/growth',
    role VARCHAR(20) DEFAULT 'expert' COMMENT 'expert/support',
    use_sample TINYINT DEFAULT 0 COMMENT '1=使用etotem脱敏样本',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/confirmed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_group_book (group_id, book_type),
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='组内分工（三本账）';

-- 成果上传表
CREATE TABLE IF NOT EXISTS train_submission (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL COMMENT '学员ID',
    group_id BIGINT COMMENT '组ID',
    book_type VARCHAR(20) COMMENT 'wealth/health/growth',
    type VARCHAR(20) DEFAULT 'deep' COMMENT 'deep(深度产出)/taught(互教跑通)',
    files TEXT COMMENT '文件列表JSON: [{url,name}]',
    image TEXT COMMENT '核心产出截图（脱敏后）',
    sample_flag TINYINT DEFAULT 0 COMMENT '1=样本数据非本人',
    desensitized TINYINT DEFAULT 0 COMMENT '1=已确认脱敏',
    verified_by BIGINT COMMENT '助教/讲师ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/approved/rejected',
    reject_reason VARCHAR(255) COMMENT '退回原因',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_uid (uid),
    INDEX idx_group_id (group_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成果上传';

-- 教学卡表（互教5步教学卡）
CREATE TABLE IF NOT EXISTS train_teaching_card (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    submission_id BIGINT COMMENT '关联成果ID',
    uid BIGINT NOT NULL,
    group_id BIGINT,
    book_type VARCHAR(20) COMMENT 'wealth/health/growth',
    steps TEXT COMMENT '5步教学卡内容JSON',
    image VARCHAR(255) COMMENT '教学卡照片',
    quality_score INT DEFAULT NULL COMMENT '教学卡质量分0-20',
    quality_by BIGINT COMMENT '评分人',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/approved/rejected',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教学卡';

-- 积分流水表
CREATE TABLE IF NOT EXISTS train_score (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT COMMENT '组ID（可为空=个人分）',
    uid BIGINT COMMENT '学员ID',
    item VARCHAR(50) NOT NULL COMMENT '计分项：install/prep/find/upload/teach/roadmap_quality/roadmap/vote/help',
    points INT NOT NULL COMMENT '分值',
    by_uid BIGINT COMMENT '打分人（人工评分项）',
    remark VARCHAR(255) COMMENT '备注',
    ts DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_group_id (group_id),
    INDEX idx_uid (uid),
    INDEX idx_item (item)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分流水';

-- 投票表（两轮）
CREATE TABLE IF NOT EXISTS train_vote (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    class_id BIGINT NOT NULL,
    round INT NOT NULL COMMENT '1=我学会了（2票，不得投本组）；2=最想抄走（1票，可投本组）',
    voter_uid BIGINT NOT NULL COMMENT '投票人',
    target_group_id BIGINT NOT NULL COMMENT '被投组ID',
    ts DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_class_round (class_id, round),
    INDEX idx_voter (voter_uid),
    INDEX idx_target (target_group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='投票';

-- 路演材料表
CREATE TABLE IF NOT EXISTS train_roadmap (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT NOT NULL,
    proposition VARCHAR(20) COMMENT '命题A-E',
    files TEXT COMMENT '路演材料截图/文件',
    speaker_uid BIGINT COMMENT '发言人ID',
    score INT DEFAULT NULL COMMENT '讲师路演评分0-40',
    score_by BIGINT COMMENT '评分人',
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='路演材料';

-- 装机打卡表
CREATE TABLE IF NOT EXISTS train_checkin (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL,
    image VARCHAR(255) COMMENT 'WorkBuddy已安装登录截图',
    status VARCHAR(20) DEFAULT 'done' COMMENT 'done=已装机',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='装机打卡';

-- 数据准备自检表
CREATE TABLE IF NOT EXISTS train_prep_check (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL,
    wealth_data TINYINT DEFAULT 0 COMMENT '券商持仓',
    health_data TINYINT DEFAULT 0 COMMENT '体检或血压',
    growth_data TINYINT DEFAULT 0 COMMENT '孩子成绩或日程',
    has_none TINYINT DEFAULT 0 COMMENT '1=选了无',
    use_sample TINYINT DEFAULT 0 COMMENT '1=分配到etotem脱敏样本',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据准备自检';

-- 7天行动计划表
CREATE TABLE IF NOT EXISTS train_plan (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL,
    items TEXT COMMENT '4件事JSON: [补齐账本,固化3用法,第2条自动化,约etotem测评]',
    submitted_at DATETIME,
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/done',
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='7天行动计划';

-- 卡券表
CREATE TABLE IF NOT EXISTS train_coupon (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL,
    type VARCHAR(20) NOT NULL COMMENT 'L1/private/etotem',
    code VARCHAR(64) COMMENT '凭证码',
    status VARCHAR(20) DEFAULT 'unused' COMMENT 'unused/used/expired',
    `trigger` VARCHAR(50) COMMENT '触发来源: vote/finish/research/champion/teaching',
    granted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    used_at DATETIME,
    INDEX idx_uid (uid),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='卡券';

-- 测评兴趣调研表
CREATE TABLE IF NOT EXISTS train_survey (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL,
    class_id BIGINT,
    interest VARCHAR(100) COMMENT 'interest_health/interest_growth',
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评兴趣调研';

-- 管理后台账号表
CREATE TABLE IF NOT EXISTS train_admin (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(128) NOT NULL COMMENT 'BCrypt加密',
    role VARCHAR(20) NOT NULL COMMENT 'admin(运营)/lecturer(讲师)/assistant(助教)',
    name VARCHAR(50) COMMENT '姓名',
    manage_group_ids VARCHAR(255) COMMENT '助教负责的组ID（逗号分隔）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理后台账号';

-- 审核日志表
CREATE TABLE IF NOT EXISTS train_audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100) NOT NULL COMMENT '操作',
    actor_uid BIGINT COMMENT '操作者',
    target_type VARCHAR(50) COMMENT '目标类型',
    target_id BIGINT COMMENT '目标ID',
    detail TEXT COMMENT '详情',
    ts DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_actor (actor_uid),
    INDEX idx_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审计日志';

-- 报名单表（报名挂课程；班次确定上课期次）
CREATE TABLE IF NOT EXISTS train_enrollment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT COMMENT '学员ID（登录后）',
    course_id BIGINT COMMENT '报名所属课程ID（train_course.id，报名=报课程）',
    class_id BIGINT COMMENT '所选班次ID（该课程的某一期）',
    salon_id BIGINT COMMENT '关联沙龙期次(train_salon.id)',
    name VARCHAR(50) COMMENT '姓名',
    phone VARCHAR(11) COMMENT '手机号',
    bring_laptop TINYINT DEFAULT 0 COMMENT '1=带电脑',
    data_ready VARCHAR(50) COMMENT '已备数据类别（逗号分隔）',
    top_problems TEXT COMMENT '最想解决的3个问题JSON',
    source VARCHAR(20) DEFAULT 'scene' COMMENT '来源：scene/invite',
    invite_code VARCHAR(50) COMMENT '邀请码（基于train_user.invite_code）',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending(待支付)/paid(已支付)/reviewing(进班待审核)/confirmed(已进班)/rejected(进班被拒)/cancelled(已取消)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_uid (uid),
    INDEX idx_course_id (course_id),
    INDEX idx_class_id (class_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报名单';

-- 支付订单表
CREATE TABLE IF NOT EXISTS train_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    enrollment_id BIGINT COMMENT '关联报名单',
    uid BIGINT COMMENT '学员ID',
    class_id BIGINT COMMENT '班次ID',
    amount INT NOT NULL COMMENT '实付金额（分）',
    original_amount INT COMMENT '原价（分）',
    discount_amount INT DEFAULT 0 COMMENT '优惠（分）',
    pay_channel VARCHAR(20) DEFAULT 'wechat' COMMENT '支付渠道',
    status VARCHAR(20) DEFAULT 'unpaid' COMMENT 'unpaid/paid/refunded/failed/closed',
    pay_time DATETIME,
    wechat_txn_id VARCHAR(64) COMMENT '微信交易号',
    prepay_id VARCHAR(64) COMMENT '预支付ID',
    refund_amount INT DEFAULT 0 COMMENT '已退金额（分）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_uid (uid),
    INDEX idx_class_id (class_id),
    INDEX idx_status (status),
    INDEX idx_enrollment_id (enrollment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付订单';

-- 转介绍关系表
CREATE TABLE IF NOT EXISTS train_invite (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    inviter_id BIGINT NOT NULL COMMENT '邀请人（train_user.id）',
    invitee_id BIGINT COMMENT '被邀请人（train_user.id）',
    invite_code VARCHAR(50) COMMENT '邀请码',
    order_id BIGINT COMMENT '被邀请人的订单ID',
    successful TINYINT DEFAULT 0 COMMENT '1=被邀请人已完成付费',
    reward_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/granted',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_inviter (inviter_id),
    INDEX idx_invitee (invitee_id),
    UNIQUE KEY uk_code_invitee (invite_code, invitee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='转介绍关系';

-- 课前资料包表（二期）
CREATE TABLE IF NOT EXISTS train_course_material (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT COMMENT '所属课程ID（train_course.id，为空=按班次关联的存量资料）',
    section_id BIGINT COMMENT '所属课程版块ID（train_course_section.id，空=通用）',
    class_id BIGINT COMMENT '班次ID（为空=通用资料）',
    title VARCHAR(100) NOT NULL COMMENT '资料标题',
    category VARCHAR(20) COMMENT '分类：pre(课前)/course(课中)/after(课后)',
    file_url VARCHAR(255) COMMENT 'PDF/附件URL',
    sort INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_course_id (course_id),
    INDEX idx_section_id (section_id),
    INDEX idx_class_id (class_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课前资料包';

-- 课前资料包表补列（存量库幂等迁移：重复执行报错被吞，属预期）
ALTER TABLE train_course_material ADD COLUMN course_id BIGINT COMMENT '所属课程ID（train_course.id）';
ALTER TABLE train_course_material ADD COLUMN section_id BIGINT COMMENT '所属课程版块ID（train_course_section.id）';
ALTER TABLE train_course_material ADD COLUMN category VARCHAR(20) COMMENT '分类：pre(课前)/course(课中)/after(课后)';

-- 班级投屏配置表（每班一条，按下述模块开关控制大屏显示）
CREATE TABLE IF NOT EXISTS train_screen_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    class_id BIGINT NOT NULL COMMENT '班次ID',
    show_scoreboard TINYINT DEFAULT 1 COMMENT '1=显示小组积分榜',
    show_checkin TINYINT DEFAULT 1 COMMENT '1=显示签到情况',
    show_progress TINYINT DEFAULT 1 COMMENT '1=显示完课进度',
    show_group_progress TINYINT DEFAULT 1 COMMENT '1=显示小组进展矩阵（分工/任务/路演）',
    show_vote TINYINT DEFAULT 1 COMMENT '1=显示投票结果',
    show_material TINYINT DEFAULT 1 COMMENT '1=显示课程资料',
    show_banner TINYINT DEFAULT 1 COMMENT '1=显示公告横幅',
    show_timer TINYINT DEFAULT 1 COMMENT '1=显示课程进行时间',
    agenda VARCHAR(2000) COMMENT '课程环节JSON: [{"title":"开场","minutes":10},...]',
    current_step INT DEFAULT 0 COMMENT '当前环节序号（从0开始，助教切换）',
    banner VARCHAR(200) COMMENT '投屏公告',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_class_id (class_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级投屏配置';

-- 团报订单表（FR-PAY-03：多学员一单 + 企业开票信息采集）
CREATE TABLE IF NOT EXISTS train_group_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_no VARCHAR(32) NOT NULL UNIQUE COMMENT '团报单号',
    class_id BIGINT NOT NULL COMMENT '班次ID',
    leader_uid BIGINT NOT NULL COMMENT '发起人（train_user.id）',
    member_count INT DEFAULT 1 COMMENT '报名人数',
    unit_amount INT COMMENT '单价（分）',
    total_amount INT NOT NULL COMMENT '总金额（分）',
    order_id BIGINT COMMENT '关联支付订单（train_order.id）',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/paid/closed',
    invoice_title VARCHAR(200) COMMENT '发票抬头（企业名称）',
    invoice_tax_no VARCHAR(100) COMMENT '税号',
    invoice_email VARCHAR(100) COMMENT '电子发票接收邮箱',
    invoice_phone VARCHAR(50) COMMENT '收票人电话',
    invoice_type VARCHAR(20) COMMENT '普票/专票',
    pay_time DATETIME COMMENT '支付时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_class_id (class_id),
    INDEX idx_leader_uid (leader_uid),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='团报订单（多学员一单+开票）';

-- 团报成员表（FR-PAY-03：一个团报单下的学员名单，支付成功后逐人生成报名单）
CREATE TABLE IF NOT EXISTS train_group_order_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_order_id BIGINT NOT NULL COMMENT '团报单ID',
    name VARCHAR(50) NOT NULL COMMENT '学员姓名',
    phone VARCHAR(11) COMMENT '手机号',
    uid BIGINT COMMENT '对应学员ID（已注册则回填）',
    enrollment_id BIGINT COMMENT '支付成功后生成的报名单ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/paid',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_group_order_id (group_order_id),
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='团报成员';

-- 学员案例表（FR-FISS-04：采集→脱敏→授权→回流）
CREATE TABLE IF NOT EXISTS train_case (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL COMMENT '案例学员ID',
    class_id BIGINT COMMENT '班次ID',
    title VARCHAR(100) NOT NULL COMMENT '案例标题',
    summary TEXT COMMENT '脱敏后的案例内容（不含真实数据）',
    source_submission_id BIGINT COMMENT '来源成果（train_submission.id）',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending待授权/authorized已授权/rejected已拒绝/published已发布',
    consent_at DATETIME COMMENT '学员授权时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_uid (uid),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学员案例（采集/授权/回流）';

-- 沙龙表（沙龙产品化：一期沙龙 = train_salon 行 + 同步一条 cfc 活动）
CREATE TABLE IF NOT EXISTS train_salon (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    activity_id BIGINT COMMENT '关联 cfc 活动 ID（activities.id，cfc 同库）',
    theme VARCHAR(50) COMMENT '主题: ai_basic/wealth/health/growth',
    title VARCHAR(100) NOT NULL COMMENT '沙龙标题',
    time VARCHAR(100) COMMENT '时间描述',
    place VARCHAR(100) COMMENT '地点',
    capacity INT DEFAULT 20 COMMENT '容量',
    price INT DEFAULT 6800 COMMENT '价格（分）',
    status VARCHAR(20) DEFAULT 'active' COMMENT 'draft/active/finished',
    start_at DATETIME COMMENT '开始时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_activity_id (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='沙龙期次';

-- 沙龙报名补列（存量库幂等迁移：重复执行报错被吞，属预期）
ALTER TABLE train_enrollment ADD COLUMN salon_id BIGINT COMMENT '关联沙龙期次(train_salon.id)';

-- 沙龙现场签到表（一人一期一条，唯一键防重复签到）
CREATE TABLE IF NOT EXISTS train_salon_checkin (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL COMMENT '学员(train_user.id)',
    salon_id BIGINT NOT NULL COMMENT '沙龙期次(train_salon.id)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_uid_salon (uid, salon_id),
    INDEX idx_salon_id (salon_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='沙龙签到';

-- 沙龙会后反馈表（一人一期一条，唯一键防重复提交）
CREATE TABLE IF NOT EXISTS train_salon_feedback (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL COMMENT '学员(train_user.id)',
    salon_id BIGINT NOT NULL COMMENT '沙龙期次(train_salon.id)',
    rating INT DEFAULT 5 COMMENT '评分 1-5',
    comment VARCHAR(500) COMMENT '留言内容',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_uid_salon (uid, salon_id),
    INDEX idx_salon_id (salon_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='沙龙反馈';

-- 完课证书表（编号体系：CM-{课程码}-{年份}-{序号}，幂等发号）
CREATE TABLE IF NOT EXISTS train_certificate (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    cert_no VARCHAR(50) NOT NULL COMMENT '证书编号 CM-{课程码}-{年份}-{序号}',
    uid BIGINT NOT NULL COMMENT '持有人（train_user.id）',
    class_id BIGINT COMMENT '班次ID（train_class.id）',
    level VARCHAR(10) COMMENT '课程码 L0/L1/L2/L3',
    year INT COMMENT '颁发年份',
    seq INT COMMENT '当年序号',
    holder_name VARCHAR(50) COMMENT '持有人姓名快照',
    issued_at DATETIME COMMENT '颁发时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_cert_no (cert_no),
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='完课证书';

-- ========== 徽章与成长体系 ==========

-- 徽章目录表（32 枚静态定义，种子数据随表插入）
CREATE TABLE IF NOT EXISTS train_badge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    badge_key VARCHAR(50) NOT NULL UNIQUE COMMENT '徽章键，如 mod_s1/course_start/skill_pitch/ms_light/lec_t1',
    category VARCHAR(20) NOT NULL COMMENT 'module/course/skill/milestone/lecturer',
    name VARCHAR(50) NOT NULL COMMENT '徽章名',
    color VARCHAR(20) COMMENT '标识色（hex，如 #7C3AED）',
    carrier VARCHAR(20) DEFAULT '电子' COMMENT '载体：电子/实体',
    condition_desc VARCHAR(200) COMMENT '获得条件描述（前端展示用）',
    sort INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='徽章目录（32枚）';

-- 用户徽章表
CREATE TABLE IF NOT EXISTS train_user_badge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uid BIGINT NOT NULL COMMENT '学员ID（train_user.id）',
    badge_key VARCHAR(50) NOT NULL COMMENT '徽章键',
    proof VARCHAR(200) COMMENT '获得依据（判定结果/手动备注）',
    granted_by BIGINT COMMENT '颁发人（train_admin.id，0=系统自动）',
    earned_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '获得时间',
    UNIQUE KEY uk_uid_badge (uid, badge_key),
    INDEX idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户徽章';

-- 徽章目录种子数据（32 条，ON DUPLICATE KEY UPDATE 幂等）
INSERT INTO train_badge(badge_key,category,name,color,carrier,condition_desc,sort) VALUES
('mod_s1','module','启航·上','#7C3AED','电子','完成 S1 家庭目录树检查',1),
('mod_s2','module','启航·下','#7C3AED','电子','完成 S2 路演两轮投票',2),
('mod_a1','module','财富诊断','#F59E0B','电子','完成 L1-A1 提交真实财富报告',3),
('mod_a2','module','财富系统','#F59E0B','电子','完成 L1-A2 自动化生效',4),
('mod_h1','module','健康趋势','#10B981','电子','完成 L1-H1 提交风险清单',5),
('mod_h2','module','健康系统','#10B981','电子','完成 L1-H2 解读菌群报告',6),
('mod_g1','module','成长拆解','#8B5CF6','电子','完成 L1-G1 习惯实验',7),
('mod_g2','module','成长系统','#8B5CF6','电子','完成 L1-G2 学期计划',8),
('mod_b1','module','系统架构','#1D4ED8','电子','完成 L2-B1 ≥5条联动规则',9),
('mod_b2','module','自动化工厂','#1D4ED8','电子','完成 L2-B2 ≥8条自动化',10),
('mod_b3','module','仪表盘','#1D4ED8','电子','完成 L2-B3 看板上线',11),
('mod_b4','module','季度闭环','#1D4ED8','电子','完成 L2-B4 系统体检',12),
('course_start','course','启航徽章','#7C3AED','实体+证书','L0 完课',13),
('course_wealth','course','财富专精徽章','#F59E0B','电子','L1-A 完课',14),
('course_health','course','健康专精徽章','#10B981','电子','L1-B 完课',15),
('course_growth','course','成长规划专精徽章','#8B5CF6','电子','L1-C 完课',16),
('course_system','course','系统师徽章','#14B8A6','电子','L2 完课+L3 结业',17),
('skill_mask','skill','脱敏达人','#64748B','电子','找茬赛满分/脱敏零问题',18),
('skill_prompt','skill','提示词匠人','#64748B','电子','改编提示词≥10条被采用',19),
('skill_automation','skill','自动化搭建者','#64748B','电子','自建自动化≥5条稳定1月',20),
('skill_pitch','skill','路演讲者','#64748B','电子','L0路演获最想抄走票',21),
('skill_board','skill','看板设计师','#64748B','电子','仪表盘获最佳',22),
('skill_retest','skill','复测坚持者','#64748B','电子','连续2季度复测',23),
('ms_21day','milestone','21天全勤','#F59E0B','实体','L3打卡≥21天',24),
('ms_light','milestone','传灯徽章','#F97316','实体','成功转介≥3人报名',25),
('ms_repeat','milestone','回炉徽章','#F97316','电子','复训≥3模块',26),
('ms_share','milestone','分享徽章','#F97316','实体','沙龙案例分享≥1次',27),
('ms_review','milestone','年度复盘','#F59E0B','电子','完成三本账年度复盘',28),
('lec_t1','lecturer','讲师·铜','#F97316','实体+讲师证','T1认证',29),
('lec_t2','lecturer','讲师·银','#D4D4D8','电子','T2认证',30),
('lec_t3','lecturer','讲师·金','#F59E0B','电子','T3认证',31),
('lec_t4','lecturer','讲师·珊瑚金','#F97316','电子','T4认证',32)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 课程模块表（模块化教学：L1/L2 按三本账方向拆模块 + 插班制）
CREATE TABLE IF NOT EXISTS train_module (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT NOT NULL COMMENT '所属课程ID（train_course.id）',
    track VARCHAR(20) COMMENT '学习路径/轨道：wealth/health/growth（空=通用模块）',
    name VARCHAR(100) NOT NULL COMMENT '模块名称（如：L1-A1 财富诊断）',
    description VARCHAR(500) COMMENT '模块说明',
    price INT DEFAULT 0 COMMENT '模块报名价（分，插班按模块收费；展示除以100）',
    sort INT DEFAULT 0 COMMENT '排序（升序）',
    enabled TINYINT DEFAULT 1 COMMENT '1=启用，0=禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_course_id (course_id),
    INDEX idx_sort (sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程模块（模块化教学/插班）';