cfc/cfc-backend/src/main/resources/schema.sql

-- 浠艾福数据库初始化脚本
-- 自动创建表

-- 家庭表
CREATE TABLE IF NOT EXISTS families (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    invite_code VARCHAR(8) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_invite_code (invite_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    openid VARCHAR(64) NOT NULL,
    unionid VARCHAR(64),
    family_id BIGINT NOT NULL,
    role ENUM('parent','child','teacher','supplier_admin') NOT NULL,
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    password VARCHAR(128),
    id_card VARCHAR(18),
    real_name VARCHAR(50),
    phone VARCHAR(11),
    birthday DATE,
    gender ENUM('male','female'),
    birth_hour VARCHAR(10) COMMENT '出生时辰: 子丑寅卯辰巳午未申酉戌亥',
    mascot VARCHAR(20) DEFAULT NULL COMMENT 'AI助手形象: xibao(浠宝)/fubao(福宝)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_openid (openid),
    INDEX idx_family_id (family_id),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 【已废除】children 表已合并到 family_members
-- 保留此处仅作参考，勿执行
-- ============================================================
-- CREATE TABLE IF NOT EXISTS children (
--     id BIGINT AUTO_INCREMENT PRIMARY KEY,
--     user_id BIGINT NOT NULL,
--     family_id BIGINT NOT NULL,
--     nickname VARCHAR(50) NOT NULL,
--     age TINYINT NOT NULL,
--     birthday DATE,
--     gender ENUM('male','female'),
--     penalty_enabled TINYINT DEFAULT 1 COMMENT '是否开启扣分(0否1是)',
--     theme VARCHAR(32) DEFAULT 'default',
--     total_points INT DEFAULT 0,
--     system_points INT DEFAULT 0 COMMENT '系统积分(测评建档/平台发放)',
--     streak_days INT DEFAULT 0,
--     last_task_date DATE,
--     focus_max_daily TINYINT DEFAULT 3,
--     focus_remaining TINYINT DEFAULT 3,
--     focus_reset_date DATE,
--     created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
--     updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
--     INDEX idx_user_id (user_id),
--     INDEX idx_family_id (family_id)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务表
CREATE TABLE IF NOT EXISTS tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    creator_id BIGINT NOT NULL,
    child_id BIGINT COMMENT '执行者ID(child任务时必填，parent任务时可空)',
    executor_type VARCHAR(20) COMMENT '执行者类型: child/parent',
    executor_id BIGINT COMMENT '执行者ID',
    title VARCHAR(200) NOT NULL,
    description TEXT,
    points TINYINT DEFAULT 2 COMMENT '任务积分1-10',
    deadline DATETIME NOT NULL,
    repeat_type ENUM('none', 'daily', 'weekly') DEFAULT 'none',
    category VARCHAR(32),
    need_review TINYINT DEFAULT 0 COMMENT '是否需要审核',
    review_by_category TINYINT DEFAULT 0 COMMENT '是否按分类审核',
    status ENUM('pending', 'completed', 'overdue', 'cancelled') DEFAULT 'pending',
    completed_at DATETIME,
    bonus_rule JSON COMMENT '积分奖励规则',
    points_expire_days INT DEFAULT 0 COMMENT '积分有效期天数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id_deadline (child_id, deadline),
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_executor (executor_type, executor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 积分流水表
CREATE TABLE IF NOT EXISTS points_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    family_member_id BIGINT COMMENT '家庭成员ID',
    task_id BIGINT,
    amount INT NOT NULL COMMENT '积分数量正负',
    type ENUM('earn', 'spend', 'bonus', 'penalty', 'adjust', 'reset', 'refund') NOT NULL,
    category VARCHAR(20) DEFAULT 'task' COMMENT '积分分类: task-任务积分, system-系统积分',
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id_created (child_id, created_at),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 积分清零记录表
CREATE TABLE IF NOT EXISTS points_reset_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    balance_before INT NOT NULL,
    balance_after INT DEFAULT 0,
    reset_type ENUM('monthly', 'manual') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 奖励表
CREATE TABLE IF NOT EXISTS rewards (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
family_id BIGINT NOT NULL COMMENT '家庭 ID，所有奖励必须关联家庭',
child_id BIGINT NULL COMMENT '孩子 ID，模板奖励为NULL',
title VARCHAR(100) NOT NULL,
points_required INT NOT NULL,
category VARCHAR(32),
status ENUM('available', 'pending', 'exchanged', 'rejected') DEFAULT 'available',
exchange_approved TINYINT COMMENT '是否审批通过',
exchanged_at DATETIME,
is_template TINYINT DEFAULT 0 COMMENT '是否为预设模板',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_family_id (family_id),
INDEX idx_child_id_status (child_id, status),
INDEX idx_is_template (is_template)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖励表，所有奖励必须关联到具体家庭';

-- 奖励分类表
CREATE TABLE IF NOT EXISTS reward_categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    name VARCHAR(32) NOT NULL,
    icon VARCHAR(64),
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 预设奖励模板数据（child_id为NULL表示模板）
INSERT IGNORE INTO rewards (family_id, child_id, title, points_required, category, is_template) VALUES
(0, NULL, '看动画片30分钟', 30, '精神奖励', 1),
(0, NULL, '玩平板游戏30分钟', 40, '特权奖励', 1),
(0, NULL, '去游乐场', 100, '物质奖励', 1),
(0, NULL, '买玩具', 150, '物质奖励', 1),
(0, NULL, '吃冰淇淋', 20, '物质奖励', 1),
(0, NULL, '选择晚餐', 25, '特权奖励', 1);

-- 默认奖励分类
INSERT IGNORE INTO reward_categories (family_id, name, icon, sort_order) VALUES
(0, '物质奖励', 'gift', 1),
(0, '精神奖励', 'star', 2),
(0, '特权奖励', 'crown', 3);

-- 敏感词表
CREATE TABLE IF NOT EXISTS sensitive_words (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(50) NOT NULL,
    category VARCHAR(32) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_word (word)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务审核表
CREATE TABLE IF NOT EXISTS task_reviews (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT NOT NULL,
    child_id BIGINT NOT NULL,
    photo_url VARCHAR(255),
    voice_note_url VARCHAR(255),
    ai_result ENUM('pass', 'review', 'pending'),
    ai_confidence INT COMMENT 'AI置信度(0-100)',
    ai_suggestion VARCHAR(255),
    ai_threshold ENUM('strict', 'normal', 'loose') DEFAULT 'normal',
    parent_result ENUM('pass', 'reject'),
    parent_override TINYINT DEFAULT 0,
    review_type ENUM('ai', 'manual') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reviewed_at DATETIME,
    INDEX idx_task_id (task_id),
    INDEX idx_ai_result (ai_result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 打卡里程碑表
CREATE TABLE IF NOT EXISTS streak_milestones (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    days INT NOT NULL,
    reward_points INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入打卡里程碑数据
INSERT IGNORE INTO streak_milestones (days, reward_points) VALUES
(3, 5),
(7, 10),
(30, 30),
(100, 100);

-- 专注训练记录表
CREATE TABLE IF NOT EXISTS focus_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    mode ENUM('simple', 'pomodoro') NOT NULL,
    duration TINYINT NOT NULL,
    stars TINYINT COMMENT '评分1-5',
    points_earned INT,
    voice_note_url VARCHAR(255),
    started_at DATETIME NOT NULL,
    completed_at DATETIME,
    INDEX idx_child_id_completed (child_id, completed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 专注训练配置表
CREATE TABLE IF NOT EXISTS focus_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT,
    max_daily_count TINYINT DEFAULT 3,
    allow_exit TINYINT DEFAULT 1 COMMENT '是否允许退出(0否1是)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DAN测评表
CREATE TABLE IF NOT EXISTS dan_assessments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    assessment_date DATE NOT NULL,
    details JSON,
    source ENUM('api', 'manual') DEFAULT 'manual',
    photo_url VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务模板表
CREATE TABLE IF NOT EXISTS task_templates (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
points TINYINT DEFAULT 2,
duration_minutes TINYINT,
category VARCHAR(32),
is_default TINYINT DEFAULT 1,
family_id BIGINT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_is_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 媒体记录表（支持图片、视频、音频、文字）
CREATE TABLE IF NOT EXISTS media_records (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
type ENUM('image', 'video', 'audio', 'text') NOT NULL,
url VARCHAR(500),
description TEXT,
task_id BIGINT NOT NULL,
creator_id BIGINT NOT NULL,
original_filename VARCHAR(255),
file_size BIGINT,
content_type VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_task_id (task_id),
INDEX idx_creator_id (creator_id),
INDEX idx_type (type),
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 服务商（成长规划师）相关表
-- =============================================

-- 服务商表（统一管理 teacher/planner/activity_provider/product_supplier）
CREATE TABLE IF NOT EXISTS guides (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL UNIQUE COMMENT '用户ID（唯一）',
    type VARCHAR(32) DEFAULT 'teacher' COMMENT '服务商类型: teacher/planner/activity_provider/product_supplier',
    teacher_no VARCHAR(20) COMMENT '服务商编号',
    title VARCHAR(50) COMMENT '职称/称号',
    specialty VARCHAR(200) COMMENT '专长领域',
    bio TEXT COMMENT '简介',
    university VARCHAR(200) COMMENT '毕业院校',
    education_system VARCHAR(32) COMMENT '教育体系(参考education_systems表)',
    experience_years TINYINT DEFAULT 0 COMMENT '从业年限',
    rating INT DEFAULT 500 COMMENT '评分(百分位,500=5.00)',
    total_appointments INT DEFAULT 0 COMMENT '总服务次数',
    level VARCHAR(20) NOT NULL DEFAULT 'junior' COMMENT '等级: junior-初级, intermediate-中级, senior-高级',
    status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active-活跃, inactive-停用',
    service_count INT DEFAULT 0 COMMENT '当前服务家庭数',
    package_id BIGINT COMMENT '购买套餐ID',
    nickname VARCHAR(100) COMMENT '申请昵称',
    phone VARCHAR(20) COMMENT '联系电话',
    intro TEXT COMMENT '个人介绍',
    avatar VARCHAR(500) COMMENT '头像URL',
    commission_rate INT DEFAULT 1000 COMMENT '佣金比例(基点,1000=10%)',
    street_id BIGINT COMMENT '街道ID',
    province VARCHAR(50) COMMENT '省份',
    city VARCHAR(50) COMMENT '城市',
    district VARCHAR(50) COMMENT '区县',
    street VARCHAR(100) COMMENT '街道',
    approved_at DATETIME COMMENT '审核通过时间',
    verified_at DATETIME COMMENT '认证通过时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_level (level),
    INDEX idx_status (status),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务商表（规划师/活动方/供应商）';

-- 测评预约表
CREATE TABLE IF NOT EXISTS assessment_appointments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户ID',
    family_id BIGINT NOT NULL,
    child_id BIGINT COMMENT '孩子ID',
    teacher_id BIGINT COMMENT '教师ID',
    guide_id BIGINT COMMENT '规划师ID',
    package_id BIGINT COMMENT '套餐ID',
    appointment_date VARCHAR(20) COMMENT '预约日期 yyyy-MM-dd',
    appointment_time VARCHAR(20) COMMENT '预约时间 HH:mm',
    duration_minutes INT COMMENT '测评时长',
    status TINYINT DEFAULT 0 COMMENT '0=待确认,1=已确认,2=已完成,3=已取消,4=未到',
    appointment_type VARCHAR(20) DEFAULT 'initial' COMMENT '初测/复测/回访',
    notes TEXT COMMENT '备注',
    remark TEXT COMMENT '补充说明',
    contact_phone VARCHAR(20),
    meet_link VARCHAR(255) COMMENT '会议链接',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_guide_id (guide_id),
    INDEX idx_family_id (family_id),
    INDEX idx_child_id (child_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DAN测评结果表
CREATE TABLE IF NOT EXISTS dan_assessment_results (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    family_member_id BIGINT COMMENT '家庭成员ID',
    family_member_name VARCHAR(50) COMMENT '成员姓名快照',
    family_member_birthday DATE COMMENT '成员出生日期快照',
    family_member_gender VARCHAR(10) COMMENT '成员性别快照',
    family_member_age INT COMMENT '成员年龄快照',
    teacher_id BIGINT NOT NULL,
    appointment_id BIGINT,
    attention_score INT COMMENT '注意力得分',
    focus_score INT COMMENT '专注力得分',
    memory_score INT COMMENT '记忆力得分',
    logic_score INT COMMENT '逻辑思维得分',
    perception_score INT COMMENT '感知得分',
    spatial_score INT COMMENT '空间思维得分',
    processing_speed_score INT COMMENT '加工速度得分',
    emotion_score INT COMMENT '情绪商数(EMI)',
    resilience_score INT COMMENT '心理韧性(EMI)',
    stress_coping_score INT COMMENT '压力应对(EMI)',
    self_awareness_score INT COMMENT '自我认知(EMI)',
    emotion_management_score INT COMMENT '情绪管理得分(EMI)',
    empathy_score INT COMMENT '同理心得分(EMI)',
    social_adaptability_score INT COMMENT '社会适应得分(EMI)',
    self_motivation_score INT COMMENT '自我激励得分(EMI)',
    overall_score INT COMMENT '综合得分',
    openness_score INT COMMENT '开放性(大五人格)',
    conscientiousness_score INT COMMENT '尽责性(大五人格)',
    extraversion_score INT COMMENT '外向性(大五人格)',
    agreeableness_score INT COMMENT '宜人性(大五人格)',
    neuroticism_score INT COMMENT '神经质(大五人格)',
    big_five_overall_score INT COMMENT '大五人格综合分',
    assessment_date DATE NOT NULL,
    duration_minutes INT COMMENT '测评时长',
    game_records JSON COMMENT '游戏记录详情',
    analysis_report TEXT COMMENT '分析报告',
    growth_suggestions TEXT COMMENT '成长建议',
    next_review_date DATE COMMENT '下次复测日期',
    status ENUM('draft', 'completed', 'archived') DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- 孩子快照字段（测评时保存的完整信息）
    child_name VARCHAR(50) COMMENT '孩子姓名快照',
    child_birthday DATE COMMENT '出生日期快照',
    child_gender VARCHAR(10) COMMENT '性别快照',
    child_age INT COMMENT '年龄快照',
    school VARCHAR(100) COMMENT '学校快照',
    grade VARCHAR(20) COMMENT '年级快照',
    grade_class VARCHAR(20) COMMENT '班级快照',
    height INT COMMENT '身高(CM)快照',
    weight INT COMMENT '体重(KG)快照',
    family_status VARCHAR(50) COMMENT '家庭状况快照',
    parent_degree VARCHAR(50) COMMENT '父母最高学历快照',
    nation VARCHAR(20) COMMENT '民族快照',
    province VARCHAR(50) COMMENT '省快照',
    city VARCHAR(50) COMMENT '市快照',
    district VARCHAR(50) COMMENT '区快照',
    address VARCHAR(200) COMMENT '详细地址快照',
    single_child VARCHAR(10) COMMENT '是否独生子女快照',
    school_score VARCHAR(20) COMMENT '在校成绩快照',
    urgent_name VARCHAR(50) COMMENT '紧急联系人姓名',
    urgent_phone VARCHAR(20) COMMENT '紧急联系人电话',
    -- 媒体字段
    pics TEXT COMMENT '图片IDs',
    videos TEXT COMMENT '视频IDs',
    audio VARCHAR(100) COMMENT '音频ID',
    audio_url VARCHAR(255) COMMENT '音频URL',
    -- 扩展描述字段
    process_desc TEXT COMMENT '过程描述',
    result_desc TEXT COMMENT '结果简评',
    source VARCHAR(20) DEFAULT 'planner_entry' COMMENT '报告来源: planner_entry/parent_upload/auto_fetch',
    INDEX idx_child_id (child_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_assessment_date (assessment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DAN测评结果表';

-- 成长规划表
CREATE TABLE IF NOT EXISTS growth_plans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    teacher_id BIGINT NOT NULL,
    assessment_id BIGINT,
    plan_title VARCHAR(200) NOT NULL,
    plan_content TEXT NOT NULL COMMENT '规划内容',
    duration_months TINYINT COMMENT '规划周期（月）',
    status ENUM('draft', 'active', 'completed', 'expired') DEFAULT 'draft',
    start_date DATE,
    end_date DATE,
    review_count INT DEFAULT 0 COMMENT '已复盘次数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 成长档案表
-- =============================================
CREATE TABLE IF NOT EXISTS growth_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    parent_id BIGINT NOT NULL COMMENT '家长用户ID',
    assessment_id BIGINT COMMENT '关联的测评结果ID',
    record_title VARCHAR(200) COMMENT '档案标题',
    child_age INT COMMENT '孩子当时年龄(月)',
    height VARCHAR(20) COMMENT '身高',
    weight VARCHAR(20) COMMENT '体重',
    assessment_summary TEXT COMMENT '测评摘要',
    overall_score INT COMMENT '综合得分',
    growth_suggestions TEXT COMMENT '成长建议',
    task_recommendations TEXT COMMENT '任务推荐(JSON)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/completed',
    external_sync TINYINT DEFAULT 0 COMMENT '是否来自外部系统同步',
    external_source VARCHAR(100) COMMENT '外部系统来源',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 数据库迁移（新增字段和表）- 通过Java代码处理
-- =============================================

-- 注意：ALTER TABLE语句已移到 DatabaseInitializer.java 中执行
-- 避免重复执行导致的错误

-- 3. 家长心愿单表（新增）
CREATE TABLE IF NOT EXISTS reward_wishlists (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '创建者用户ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    title VARCHAR(100) NOT NULL COMMENT '心愿名称',
    description TEXT COMMENT '心愿描述',
    points_required INT NOT NULL COMMENT '所需积分',
    category VARCHAR(32) COMMENT '分类',
    image_url VARCHAR(255) COMMENT '图片链接',
    status ENUM('available', 'pending', 'exchanged', 'rejected') DEFAULT 'available' COMMENT '状态',
    exchange_approved TINYINT COMMENT '是否审批通过',
    exchanged_at DATETIME COMMENT '兑换时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_family_id (family_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 更新任务状态枚举（如果需要支持更多状态）
-- ALTER TABLE tasks MODIFY COLUMN status ENUM('pending', 'completed', 'overdue', 'cancelled', 'parent_pending', 'parent_completed') DEFAULT 'pending';

-- ===================== 任务模板套餐功能 =====================

-- 成长规划师-家庭关系表
CREATE TABLE IF NOT EXISTS guide_families (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    guide_id BIGINT NOT NULL COMMENT '成长规划师用户ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    service_type VARCHAR(20) DEFAULT 'consulting' COMMENT '服务类型: monthly/yearly/consulting',
    service_price INT DEFAULT 0 COMMENT '服务价格(分)',
    status VARCHAR(20) DEFAULT 'binding' COMMENT '状态: binding/paused/cancelled',
    bound_at DATETIME COMMENT '绑定时间',
    expire_at DATETIME COMMENT '服务到期时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_guide_family (guide_id, family_id),
    INDEX idx_guide_id (guide_id),
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务模板套餐
CREATE TABLE IF NOT EXISTS task_template_packages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT NOT NULL COMMENT '创建者成长规划师ID',
    name VARCHAR(100) NOT NULL COMMENT '套餐名称',
    description TEXT COMMENT '详细介绍',
    cycle_days INT NOT NULL COMMENT '周期天数',
    category VARCHAR(50) COMMENT '分类',
    tags JSON COMMENT '标签',
    price INT DEFAULT 0 COMMENT '售价(分)',
    platform_fee INT DEFAULT 0 COMMENT '平台服务费(基点)',
    is_public TINYINT DEFAULT 0 COMMENT '是否公开',
    share_code VARCHAR(50) UNIQUE COMMENT '分享码',
    cover_image VARCHAR(500) COMMENT '封面图',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态',
    reviewer_id BIGINT COMMENT '审核人',
    reviewed_at DATETIME COMMENT '审核时间',
    reject_reason TEXT COMMENT '拒绝原因',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_creator_id (creator_id),
    INDEX idx_status (status),
    INDEX idx_is_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 套餐内任务项
CREATE TABLE IF NOT EXISTS task_template_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    package_id BIGINT NOT NULL COMMENT '所属套餐ID',
    task_name VARCHAR(100) NOT NULL,
    description TEXT,
    points INT DEFAULT 0,
    frequency VARCHAR(20) DEFAULT 'daily',
    duration INT COMMENT '预计时长(分钟)',
    is_required TINYINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_package_id (package_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 套餐应用实例
CREATE TABLE IF NOT EXISTS task_plan_instances (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    package_id BIGINT NOT NULL,
    family_id BIGINT NOT NULL,
    child_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    total_days INT,
    completed_days INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id),
    INDEX idx_child_id (child_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 实例任务项
CREATE TABLE IF NOT EXISTS task_plan_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    instance_id BIGINT NOT NULL,
    template_item_id BIGINT,
    task_name VARCHAR(100) NOT NULL,
    description TEXT,
    points INT DEFAULT 0,
    frequency VARCHAR(20) DEFAULT 'daily',
    duration INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_instance_id (instance_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 套餐购买订单
CREATE TABLE IF NOT EXISTS package_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) UNIQUE NOT NULL,
    user_id BIGINT NOT NULL COMMENT '购买者用户ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    package_id BIGINT NOT NULL COMMENT '套餐ID',
    package_name VARCHAR(100) COMMENT '套餐名称',
    price INT NOT NULL COMMENT '支付金额(分)',
    platform_fee INT DEFAULT 0 COMMENT '平台服务费(分)',
    guide_id BIGINT COMMENT '成长规划师ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/paid/cancelled/refunded',
    pay_method VARCHAR(20) COMMENT '支付方式',
    transaction_id VARCHAR(64) COMMENT '微信支付订单号',
    paid_at DATETIME COMMENT '支付时间',
    notify_data TEXT COMMENT '支付回调数据',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_family_id (family_id),
    INDEX idx_package_id (package_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户表增加成长规划师相关字段 - 已移到 DatabaseInitializer.java 执行
-- ALTER TABLE users ADD COLUMN teacher_no VARCHAR(50) COMMENT '成长规划师证书号';
-- ALTER TABLE users ADD COLUMN teacher_photo VARCHAR(500) COMMENT '成长规划师照片';
-- ALTER TABLE users ADD COLUMN certificate_image VARCHAR(500) COMMENT '成长规划师证书照片';
-- ALTER TABLE users ADD COLUMN teacher_status VARCHAR(20) DEFAULT 'pending' COMMENT '成长规划师审核状态: pending/approved/rejected';
-- ALTER TABLE users ADD INDEX idx_teacher_status (teacher_status);

-- 成长规划师咨询消息表
CREATE TABLE IF NOT EXISTS teacher_messages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    parent_id BIGINT NOT NULL COMMENT '家长用户ID',
    teacher_id BIGINT NOT NULL COMMENT '成长规划师用户ID',
    content TEXT NOT NULL COMMENT '消息内容',
    reply_content TEXT COMMENT '回复内容',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态:pending/replied',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    replied_at DATETIME COMMENT '回复时间',
    INDEX idx_family_id (family_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 游戏记录表（专注训练模块）
CREATE TABLE IF NOT EXISTS game_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    game_code VARCHAR(20) NOT NULL COMMENT '游戏代码',
    score INT DEFAULT 0 COMMENT '得分(0-100)',
    completion_time INT COMMENT '完成用时(秒)',
    difficulty VARCHAR(20) DEFAULT 'medium' COMMENT '难度(easy/medium/hard)',
    points_earned INT DEFAULT 0 COMMENT '获得积分',
    played_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '游玩时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id),
    INDEX idx_game_code (game_code),
    INDEX idx_played_at (played_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 勋章定义表
CREATE TABLE IF NOT EXISTS badge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    badge_id VARCHAR(50) UNIQUE NOT NULL COMMENT '勋章唯一标识',
    name VARCHAR(100) NOT NULL COMMENT '勋章名称',
    description TEXT COMMENT '勋章描述',
    icon VARCHAR(255) COMMENT '勋章图标',
    category VARCHAR(20) COMMENT '分类(学习/运动/家务/阅读/打卡/综合)',
    level VARCHAR(20) COMMENT '等级(铜牌/银牌/金牌/钻石)',
    rarity VARCHAR(20) COMMENT '稀有度(普通/稀有/史诗/传说)',
    trigger_type VARCHAR(20) COMMENT '触发类型(task_count/streak_days/points_total/reward_count/custom)',
    threshold INT DEFAULT 0 COMMENT '阈值',
    need_approval TINYINT DEFAULT 0 COMMENT '是否需要审批',
    expire_days INT DEFAULT -1 COMMENT '有效期天数(-1永久)',
    sort_order INT DEFAULT 0 COMMENT '排序',
    is_active TINYINT DEFAULT 1 COMMENT '是否启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 儿童勋章关联表
CREATE TABLE IF NOT EXISTS child_badge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    badge_id BIGINT NOT NULL COMMENT '勋章ID',
    earned_at DATETIME COMMENT '获得时间',
    expire_at DATETIME COMMENT '过期时间',
    status TINYINT DEFAULT 1 COMMENT '状态:1有效 0过期 -1撤销',
    is_favorite TINYINT DEFAULT 0 COMMENT '是否收藏',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_child_badge (child_id, badge_id),
    INDEX idx_child_id (child_id),
    INDEX idx_badge_id (badge_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 以下表从 DatabaseInitializer.java 提取
-- 管理后台表
-- =============================================

-- 管理员表
CREATE TABLE IF NOT EXISTS admins (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(128) NOT NULL COMMENT '密码(MD5加密)',
    nickname VARCHAR(50) COMMENT '昵称',
    real_name VARCHAR(50) COMMENT '真实姓名',
    phone VARCHAR(11) COMMENT '手机号',
    email VARCHAR(100) COMMENT '邮箱',
    role VARCHAR(20) DEFAULT 'admin' COMMENT '角色: admin/teacher',
    status TINYINT DEFAULT 1 COMMENT '状态:1启用0禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_phone (phone),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

-- 验证码表
CREATE TABLE IF NOT EXISTS verification_codes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    phone VARCHAR(11) NOT NULL COMMENT '手机号',
    code VARCHAR(6) NOT NULL COMMENT '验证码',
    type VARCHAR(20) NOT NULL COMMENT '验证码类型',
    expires_in INT DEFAULT 300 COMMENT '过期时间(秒)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    expires_at DATETIME NOT NULL COMMENT '过期时间',
    used TINYINT DEFAULT 0 COMMENT '是否已使用:0否1是',
    INDEX idx_phone_type (phone, type),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='验证码表';

-- 管理后台任务模板表
CREATE TABLE IF NOT EXISTS admin_task_templates (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    title VARCHAR(200) NOT NULL COMMENT '模板标题',
    description TEXT COMMENT '模板描述',
    points INT DEFAULT 10 COMMENT '默认积分',
    category VARCHAR(32) COMMENT '任务分类',
    need_review TINYINT DEFAULT 0 COMMENT '是否需要审核:0否1是',
    review_by_category TINYINT DEFAULT 0 COMMENT '是否按分类审核:0否1是',
    recommended_age INT COMMENT '推荐年龄',
    difficulty ENUM('easy', 'medium', 'hard') DEFAULT 'medium' COMMENT '难度: easy/medium/hard',
    teacher_id BIGINT COMMENT '成长规划师ID',
    complete_types VARCHAR(100) COMMENT '完成方式JSON',
    duration INT COMMENT '预计完成时长(分钟)',
    review_type VARCHAR(20) COMMENT '审核人类型: creator/parent/teacher/ai',
    task_type VARCHAR(20) COMMENT '任务性质: onetime/recurring',
    frequency VARCHAR(20) COMMENT '频率: daily/weekly/monthly',
    max_frequency INT COMMENT '周期内最多完成次数',
    is_active TINYINT DEFAULT 1 COMMENT '是否启用:0禁用1启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_category (category),
    INDEX idx_difficulty (difficulty),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理后台任务模板表';

-- =============================================
-- 测评模块表
-- =============================================

-- 测评资料表
-- 数学闯关 - 孩子当前等级
CREATE TABLE IF NOT EXISTS math_child_level (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL UNIQUE,
    current_level TINYINT NOT NULL DEFAULT 1 COMMENT '1=一位数加减,2=两位数加减,3=一位数四则,4=两位数四则',
    total_passed INT DEFAULT 0 COMMENT '累计通关次数',
    best_time_ms BIGINT DEFAULT NULL COMMENT '最佳用时(毫秒)',
    best_accuracy INT DEFAULT NULL COMMENT '最佳正确率(%)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 数学闯关 - 通关日志
CREATE TABLE IF NOT EXISTS math_quiz_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL,
    level TINYINT NOT NULL COMMENT '闯关时等级',
    target_n INT NOT NULL COMMENT '目标连续答对数',
    correct_count INT NOT NULL COMMENT '实际连续答对数',
    total_attempts INT NOT NULL COMMENT '总答题数',
    total_time_ms BIGINT NOT NULL COMMENT '总用时(毫秒)',
    accuracy INT NOT NULL COMMENT '正确率(%)',
    passed TINYINT(1) DEFAULT 0 COMMENT '是否通关',
    exprs TEXT COMMENT '本轮表达式列表(JSON)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id),
    INDEX idx_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assessment_materials (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    title VARCHAR(100) NOT NULL COMMENT '资料标题',
    content TEXT COMMENT '测评介绍内容',
    cover_image VARCHAR(255) COMMENT '封面图片',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态:active/inactive',
    created_at DATETIME COMMENT '创建时间',
    updated_at DATETIME COMMENT '更新时间',
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评资料表';

-- 积分配置表
CREATE TABLE IF NOT EXISTS assessment_points_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    points_value INT DEFAULT 100 COMMENT '完成测评获得积分',
    points_value_yuan INT DEFAULT 1 COMMENT '1积分等于多少分(原元→现分)',
    is_active TINYINT DEFAULT 1 COMMENT '是否启用',
    created_at DATETIME COMMENT '创建时间',
    updated_at DATETIME COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分配置表';

-- 家庭测评配置表
CREATE TABLE IF NOT EXISTS family_assessment_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    material_id BIGINT COMMENT '测评资料ID',
    remind_months INT DEFAULT 12 COMMENT '提醒周期(月)',
    last_remind_at DATETIME COMMENT '上次提醒时间',
    next_remind_at DATETIME COMMENT '下次提醒时间',
    is_enabled TINYINT DEFAULT 1 COMMENT '是否启用:1启用0关闭',
    created_by BIGINT COMMENT '创建人(规划师ID)',
    created_at DATETIME COMMENT '创建时间',
    updated_at DATETIME COMMENT '更新时间',
    UNIQUE KEY uk_family (family_id),
    INDEX idx_next_remind (next_remind_at),
    INDEX idx_enabled (is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭测评配置表';

-- 测评记录表
CREATE TABLE IF NOT EXISTS assessment_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    family_id BIGINT COMMENT '家庭ID',
    child_id BIGINT COMMENT '孩子ID',
    material_id BIGINT COMMENT '测评资料ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态:pending待完成/completed已完成',
    completed_at DATETIME COMMENT '完成时间',
    created_at DATETIME COMMENT '创建时间',
    INDEX idx_family (family_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评记录表';

-- 评估订单表（测评订单）
CREATE TABLE IF NOT EXISTS assessment_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    order_no VARCHAR(64) NOT NULL UNIQUE COMMENT '订单编号',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT COMMENT '孩子ID',
    guide_id BIGINT NOT NULL COMMENT '成长规划师ID',
    package_id BIGINT NOT NULL COMMENT '套餐ID',
    appointment_id BIGINT COMMENT '关联预约ID',
    guide_name VARCHAR(50) COMMENT '规划师名称',
    package_name VARCHAR(100) COMMENT '套餐名称',
    total_price BIGINT DEFAULT 0 COMMENT '总价(分)',
    discount_amount BIGINT DEFAULT 0 COMMENT '优惠金额(分)',
    actual_price BIGINT DEFAULT 0 COMMENT '实付金额(分)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/paid/completed/cancelled/refunded',
    pay_time VARCHAR(50) COMMENT '支付时间',
    pay_type VARCHAR(20) COMMENT '支付方式',
    transaction_id VARCHAR(64) COMMENT '交易流水号',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_family (family_id),
    INDEX idx_guide (guide_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评估订单表';

-- =============================================
-- 成长规划师相关表
-- =============================================

-- 成长规划师申请表
CREATE TABLE IF NOT EXISTS guide_applications (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '申请人用户ID',
    type VARCHAR(20) NOT NULL DEFAULT 'new_apply' COMMENT '申请类型: new_apply-新注册, upgrade-升级',
    target_level VARCHAR(20) NOT NULL COMMENT '目标等级: junior-初级, intermediate-中级, senior-高级',
    current_level VARCHAR(20) DEFAULT NULL COMMENT '当前等级（升级时填写）',
    package_id BIGINT COMMENT '申请的套餐ID',
    payment_proof VARCHAR(500) COMMENT '付款凭证URL',
    payment_amount INT COMMENT '付款金额(分)',
    address VARCHAR(500) COMMENT '地址',
    bank_card VARCHAR(50) COMMENT '银行卡号',
    bank_name VARCHAR(100) COMMENT '开户行',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending-待审核, approved-已通过, rejected-已拒绝',
    reject_reason TEXT COMMENT '拒绝原因',
    reviewed_at DATETIME COMMENT '审核时间',
    reviewed_by BIGINT COMMENT '审核人ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师申请表';

-- 成长规划师申请过程记录表
CREATE TABLE IF NOT EXISTS guide_application_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    application_id BIGINT NOT NULL COMMENT '关联申请ID',
    user_id BIGINT NOT NULL COMMENT '操作人用户ID',
    action VARCHAR(50) NOT NULL COMMENT '操作: submit/approve/reject/withdraw/resubmit',
    from_status VARCHAR(20) COMMENT '操作前状态',
    to_status VARCHAR(20) NOT NULL COMMENT '操作后状态',
    remark TEXT COMMENT '备注/原因',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_application_id (application_id),
    INDEX idx_user_id (user_id),
    INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师申请过程记录表';

-- =============================================
-- 小游戏表
-- =============================================

-- 小游戏表
CREATE TABLE IF NOT EXISTS mini_games (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    game_code VARCHAR(20) NOT NULL UNIQUE COMMENT '游戏代码',
    game_name VARCHAR(50) NOT NULL COMMENT '游戏名称',
    description TEXT COMMENT '游戏描述',
    icon VARCHAR(50) COMMENT '图标emoji',
    default_duration INT DEFAULT 5 COMMENT '默认时长(分钟)',
    default_points INT DEFAULT 5 COMMENT '默认奖励积分',
    game_url VARCHAR(255) COMMENT '游戏H5页面URL',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status TINYINT DEFAULT 1 COMMENT '状态:0禁用1启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_game_code (game_code),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小游戏表';

-- =============================================
-- 地址维度表
-- =============================================

-- 街道地址维度表
CREATE TABLE IF NOT EXISTS streets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    province VARCHAR(50) COMMENT '省份',
    province_code VARCHAR(20) COMMENT '省份编码',
    city VARCHAR(50) COMMENT '城市',
    city_code VARCHAR(20) COMMENT '城市编码',
    district VARCHAR(50) COMMENT '区县',
    district_code VARCHAR(20) COMMENT '区县编码',
    street VARCHAR(100) COMMENT '街道',
    street_code VARCHAR(20) COMMENT '街道编码',
    full_name VARCHAR(200) COMMENT '完整地址',
    level INT COMMENT '层级:1省2市3区4街道',
    parent_id BIGINT COMMENT '父级ID',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_level (level),
    INDEX idx_parent_id (parent_id),
    INDEX idx_street_code (street_code),
    INDEX idx_full_name (full_name(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='街道地址维度表';

-- =============================================
-- 系统日志表
-- =============================================

-- 用户操作日志表
CREATE TABLE IF NOT EXISTS user_operation_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT COMMENT '用户ID',
    nickname VARCHAR(50) COMMENT '用户昵称',
    operation_type VARCHAR(20) NOT NULL COMMENT '操作类型: login/logout/create/update/delete/query等',
    description TEXT COMMENT '操作描述',
    request_path VARCHAR(200) COMMENT '请求路径',
    request_method VARCHAR(10) COMMENT '请求方法',
    request_params TEXT COMMENT '请求参数（脱敏）',
    response_status INT COMMENT '响应状态码',
    client_ip VARCHAR(50) COMMENT '客户端IP',
    user_agent VARCHAR(500) COMMENT 'User-Agent',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user_id (user_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户操作日志表';

-- =============================================
-- 邀请与心愿表
-- =============================================

-- 邀请卡片表
CREATE TABLE IF NOT EXISTS invite_card (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    type VARCHAR(20) NOT NULL COMMENT '类型: child/family/guide',
    code VARCHAR(64) NOT NULL UNIQUE COMMENT '邀请码',
    ref_id BIGINT NOT NULL COMMENT '关联ID(childId/familyId)',
    creator_id BIGINT NOT NULL COMMENT '创建人',
    expires_at DATETIME NOT NULL COMMENT '过期时间',
    used TINYINT DEFAULT 0 COMMENT '是否已使用:0否1是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_code (code),
    INDEX idx_ref_type (ref_id, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邀请卡片表';

-- 心愿单表
CREATE TABLE IF NOT EXISTS wishes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    family_id BIGINT COMMENT '家庭ID',
    child_id BIGINT COMMENT '目标孩子ID',
    created_by BIGINT COMMENT '创建者用户ID',
    creator_type VARCHAR(20) COMMENT '创建者类型: child/parent',
    title VARCHAR(200) NOT NULL COMMENT '心愿标题',
    description TEXT COMMENT '心愿描述',
    category VARCHAR(32) COMMENT '心愿分类',
    points_required INT COMMENT '所需积分',
    points_set_by BIGINT COMMENT '定价者ID',
    points_set_at DATETIME COMMENT '定价时间',
    status VARCHAR(32) DEFAULT 'active' COMMENT '状态: draft/active/pending_exchange/approved/exchanging/fulfilled/cancelled/rejected',
    exchange_requested_at DATETIME COMMENT '申请兑换时间',
    exchange_approved_by BIGINT COMMENT '批准者ID',
    exchange_approved_at DATETIME COMMENT '批准时间',
    points_deducted_at DATETIME COMMENT '扣积分时间',
    fulfilled_by BIGINT COMMENT '兑现者ID',
    fulfilled_at DATETIME COMMENT '兑现时间',
    child_confirmed_at DATETIME COMMENT '孩子确认收货时间',
    rejected_by BIGINT COMMENT '拒绝者ID',
    rejected_at DATETIME COMMENT '拒绝时间',
    reject_reason TEXT COMMENT '拒绝原因',
    reject_count INT DEFAULT 0 COMMENT '被拒绝次数',
    cancelled_by BIGINT COMMENT '取消者ID',
    cancelled_at DATETIME COMMENT '取消时间',
    cancel_reason TEXT COMMENT '取消原因',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_family_id (family_id),
    INDEX idx_child_id (child_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='心愿单表';

-- =============================================
-- 商品与订单表
-- =============================================

-- 商品表
CREATE TABLE IF NOT EXISTS products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    description TEXT COMMENT '商品描述',
    intro TEXT COMMENT '详细介绍',
    cover_image VARCHAR(500) COMMENT '封面图片',
    images TEXT COMMENT '多张图片JSON',
    price INT NOT NULL DEFAULT 0 COMMENT '价格(分)',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存',
    sales_count INT NOT NULL DEFAULT 0 COMMENT '销量',
    product_type VARCHAR(32) NOT NULL COMMENT '商品类型: activity/course/physical/digital/service',
    vendor_id BIGINT COMMENT '供应商ID',
    vendor_name VARCHAR(100) COMMENT '供应商名称',
    status VARCHAR(32) NOT NULL DEFAULT 'draft' COMMENT '状态: draft/pending/approved/rejected/on_shelf/off_shelf',
    reject_reason TEXT COMMENT '审核拒绝原因',
    member_price INT COMMENT '会员价(分)',
    member_eligible VARCHAR(32) DEFAULT 'none' COMMENT '会员资格: all/budget/none',
    domain VARCHAR(32) COMMENT '五维领域: action/mind/body/wisdom/wealth',
    profit_rate INT DEFAULT 0 COMMENT '平台利润率(千分比)',
    delivery_method TINYINT DEFAULT 1 COMMENT '配送方式: 1=快递 2=自提 3=两者皆可',
    category_id BIGINT COMMENT '商品类目ID',
    distribution_system_id BIGINT COMMENT '分销体系ID',
    external_source VARCHAR(64) COMMENT '外部来源系统',
    external_id VARCHAR(128) COMMENT '外部来源ID',
    external_data TEXT COMMENT '外部来源数据JSON',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 商品订单表
CREATE TABLE IF NOT EXISTS product_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    order_no VARCHAR(64) NOT NULL UNIQUE COMMENT '订单编号',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    product_name VARCHAR(200) COMMENT '商品名称',
    product_type VARCHAR(32) COMMENT '商品类型',
    buyer_id BIGINT NOT NULL COMMENT '购买者ID',
    family_id BIGINT COMMENT '家庭ID',
    quantity INT NOT NULL DEFAULT 1 COMMENT '数量',
    unit_price INT NOT NULL COMMENT '单价(分)',
    total_amount INT NOT NULL COMMENT '总金额(分)',
    status VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/paid/completed/cancelled/refunded',
    payment_method VARCHAR(32) COMMENT '支付方式: wechat/points/membership',
    remark TEXT COMMENT '备注',
    paid_at DATETIME COMMENT '支付时间',
    supply_system_id BIGINT COMMENT '所属供应商体系ID',
    supplier_id BIGINT COMMENT '收款供应商用户ID',
    supply_commission INT COMMENT '分销佣金(分)',
    points_used INT DEFAULT 0 COMMENT '使用的积分数量',
    points_cost INT DEFAULT 0 COMMENT '积分抵扣金额(分)',
    money_amount INT DEFAULT 0 COMMENT '现金支付金额(分)',
    consignee_id BIGINT COMMENT '收货人ID',
    purchase_info TEXT COMMENT '购买信息JSON: {id_card, hand_signature, ...}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_family_id (family_id),
    INDEX idx_product_id (product_id),
    INDEX idx_status (status),
    INDEX idx_supply_system_id (supply_system_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品订单表';

-- =============================================
-- 系统配置与内容表
-- =============================================

-- 系统配置表
CREATE TABLE IF NOT EXISTS sys_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    config_key VARCHAR(64) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    description TEXT COMMENT '配置说明',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- 内容区块配置表
CREATE TABLE IF NOT EXISTS content_sections (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    page_key VARCHAR(50) NOT NULL COMMENT '页面标识',
    section_key VARCHAR(50) NOT NULL COMMENT '区块标识',
    title VARCHAR(100) COMMENT '区块标题',
    allowed_roles VARCHAR(255) DEFAULT '["anonymous"]' COMMENT '允许查看的角色JSON数组',
    sort_order INT DEFAULT 0 COMMENT '显示顺序',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/inactive',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='内容区块配置表';

-- 文章分类表
CREATE TABLE IF NOT EXISTS article_categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(50) NOT NULL COMMENT '分类名',
    icon VARCHAR(20) DEFAULT '' COMMENT '图标emoji',
    color VARCHAR(20) DEFAULT '#5B9BD5' COMMENT '标识色',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status TINYINT DEFAULT 1 COMMENT '状态:1启用/0禁用'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章分类表';

-- 文章表
CREATE TABLE IF NOT EXISTS articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键 ID',
    category_id BIGINT DEFAULT 0 COMMENT '所属分类 ID',
    title VARCHAR(200) NOT NULL COMMENT '标题',
    summary TEXT DEFAULT '' COMMENT '摘要',
    cover_image VARCHAR(500) DEFAULT '' COMMENT '封面图 URL',
    content LONGTEXT COMMENT '富文本内容',
    tags VARCHAR(200) DEFAULT '' COMMENT '标签 JSON 数组',
    author VARCHAR(100) DEFAULT '浠艾福' COMMENT '作者',
    read_time INT DEFAULT 0 COMMENT '预计阅读分钟数',
    article_type VARCHAR(20) DEFAULT 'normal' COMMENT '文章类型：normal(普通)/premium(优质)/original(原创)',
    word_count INT DEFAULT 0 COMMENT '文章字数',
    related_dimensions VARCHAR(100) DEFAULT '' COMMENT '关联五维 JSON 数组',
    visibility VARCHAR(20) DEFAULT 'public' COMMENT '浏览权限：public/login/private',
    visible_to TEXT COMMENT '私密指定人群 JSON',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态：draft/published/archived',
    is_featured TINYINT DEFAULT 0 COMMENT '是否精选:1 精选/0 普通',
    published_at DATETIME DEFAULT NULL COMMENT '发布时间',
    view_count INT DEFAULT 0 COMMENT '浏览次数',
    created_by BIGINT DEFAULT 0 COMMENT '发布人 adminID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

-- Article audit fields (for user-generated content)
ALTER TABLE articles
    ADD COLUMN author_type VARCHAR(20) DEFAULT 'admin' COMMENT '作者类型：admin/parent/child' AFTER created_by,
    ADD COLUMN audit_status VARCHAR(20) DEFAULT 'approved' COMMENT '审核状态：approved/pending/rejected' AFTER status,
    ADD COLUMN audit_reason TEXT COMMENT '驳回原因' AFTER audit_status,
    ADD COLUMN auditor_id BIGINT DEFAULT 0 COMMENT '审核人 ID' AFTER audit_reason,
    ADD COLUMN audited_at DATETIME COMMENT '审核时间' AFTER auditor_id;

-- =============================================
-- 五维能量系统表
-- =============================================

-- 能量维度定义表
CREATE TABLE IF NOT EXISTS energy_dimension (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    code VARCHAR(16) NOT NULL UNIQUE COMMENT '维度代码: body/mind/wisdom/action/wealth',
    name VARCHAR(20) NOT NULL COMMENT '维度名称: 身/心/智/行/富',
    icon VARCHAR(16) COMMENT '图标emoji',
    element VARCHAR(10) COMMENT '五行元素: 土/火/金/木/水',
    sort_order INT DEFAULT 0 COMMENT '显示顺序',
    status TINYINT DEFAULT 1 COMMENT '状态:1启用/0禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能量维度定义表';

-- 服务-维度比例配置表
CREATE TABLE IF NOT EXISTS energy_source_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    source_type VARCHAR(32) NOT NULL COMMENT '来源类型: task/activity/course/product/consultation',
    source_id BIGINT NOT NULL COMMENT '来源业务表ID',
    source_name VARCHAR(200) COMMENT '冗余名称',
    dimension_id BIGINT NOT NULL COMMENT '维度ID',
    ratio INT NOT NULL COMMENT '占比(基点,6000=60%)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_source (source_type, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务-维度比例配置表';

-- 能量流水表
CREATE TABLE IF NOT EXISTS energy_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    dimension_id BIGINT NOT NULL COMMENT '维度ID',
    amount INT NOT NULL COMMENT '变动量(正=获得,负=消耗)',
    balance_after INT NOT NULL COMMENT '变动后余额',
    source_type VARCHAR(32) COMMENT '来源类型: task/product/medical_report/daily_record',
    source_id BIGINT COMMENT '来源ID',
    ref_id BIGINT COMMENT '关联旧流水ID(覆盖时标记)',
    expires_at DATETIME COMMENT '过期时间(空=永不过期)',
    description TEXT COMMENT '描述',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_child_dim (child_id, dimension_id),
    INDEX idx_created (created_at),
    INDEX idx_source (source_type, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能量流水表';

-- 维度余额表
CREATE TABLE IF NOT EXISTS energy_balance (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    dimension_id BIGINT NOT NULL COMMENT '维度ID',
    balance INT DEFAULT 0 COMMENT '当前能量值(可为负)',
    total_earned INT DEFAULT 0 COMMENT '累计获得',
    total_spent INT DEFAULT 0 COMMENT '累计消耗',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_child_dim (child_id, dimension_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='维度余额表';

-- =============================================
-- 分佣裂变系统表
-- =============================================

-- 佣金记录表
CREATE TABLE IF NOT EXISTS commission_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    order_type VARCHAR(32) NOT NULL COMMENT '订单类型: membership/product/assessment/package',
    referrer_id BIGINT NOT NULL COMMENT '推荐人ID',
    buyer_id BIGINT NOT NULL COMMENT '购买人ID',
    commission_type VARCHAR(16) NOT NULL COMMENT '佣金类型: member/profit',
    level INT DEFAULT 1 COMMENT '佣金层级: 1=直接推荐, 2=间接推荐',
    order_amount INT NOT NULL COMMENT '订单金额(分)',
    profit_rate INT DEFAULT 0 COMMENT '利润率(千分比,如235=23.5%)',
    commission_amount INT NOT NULL COMMENT '佣金金额(分)',
    status VARCHAR(16) NOT NULL DEFAULT 'settled' COMMENT '状态: pending/settled',
    remark TEXT DEFAULT NULL COMMENT '备注',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    settled_at DATETIME DEFAULT NULL COMMENT '结算时间',
    withdrawn_at DATETIME DEFAULT NULL COMMENT '提现时间',
    INDEX idx_referrer (referrer_id),
    INDEX idx_buyer (buyer_id),
    INDEX idx_order (order_id, order_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='佣金记录表';

-- 提现申请表
CREATE TABLE IF NOT EXISTS withdrawal_requests (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    amount INT NOT NULL COMMENT '提现金额(分)',
    account_info TEXT COMMENT '账户信息(JSON)',
    status VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/approved/rejected',
    audit_by BIGINT DEFAULT NULL COMMENT '审核人ID',
    audit_at DATETIME DEFAULT NULL COMMENT '审核时间',
    remark TEXT DEFAULT NULL COMMENT '审核备注',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现申请表';

-- 会员升级记录表
CREATE TABLE IF NOT EXISTS member_upgrade_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    from_level VARCHAR(20) NOT NULL COMMENT '升级前等级',
    to_level VARCHAR(20) NOT NULL COMMENT '升级后等级',
    upgrade_type VARCHAR(20) NOT NULL COMMENT '升级类型: pay/consumption/admin',
    order_id BIGINT DEFAULT NULL COMMENT '关联支付订单ID',
    consumption_amount BIGINT DEFAULT NULL COMMENT '触发升级的累计消费金额',
    expire_time DATETIME DEFAULT NULL COMMENT '家庭会员到期时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user (user_id),
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员升级记录表';

-- =============================================
-- 规划师服务模块表
-- =============================================

-- 服务类型表
CREATE TABLE IF NOT EXISTS service_types (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(100) NOT NULL COMMENT '类型名称',
    icon VARCHAR(50) COMMENT '图标',
    description TEXT COMMENT '描述',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态: ACTIVE/DISABLED',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务类型表';

-- 服务内容表
CREATE TABLE IF NOT EXISTS service_contents (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    type_id BIGINT NOT NULL COMMENT '关联服务类型',
    title VARCHAR(200) NOT NULL COMMENT '内容标题',
    content TEXT COMMENT '富文本内容',
    duration INT COMMENT '建议时长(分钟)',
    age_range VARCHAR(50) COMMENT '适用年龄',
    price INT COMMENT '参考价格(分)',
    status VARCHAR(20) DEFAULT 'DRAFT' COMMENT '状态: DRAFT/ACTIVE',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务内容表';

-- 套餐模板表
CREATE TABLE IF NOT EXISTS guide_package_templates (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(100) NOT NULL COMMENT '模板名称',
    validity_months INT COMMENT '有效期(月)',
    reference_price INT COMMENT '参考价格(分)',
    min_price INT COMMENT '最低售价(分)',
    description TEXT COMMENT '说明',
    status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态: ACTIVE/DISABLED',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='套餐模板表';

-- 套餐模板服务项表
CREATE TABLE IF NOT EXISTS package_template_services (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    template_id BIGINT NOT NULL COMMENT '关联模板',
    content_id BIGINT NOT NULL COMMENT '关联服务内容',
    quantity INT DEFAULT 1 COMMENT '数量',
    unit VARCHAR(20) COMMENT '单位'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='套餐模板服务项表';

-- 成长规划师套餐表
CREATE TABLE IF NOT EXISTS guide_packages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    guide_id BIGINT COMMENT '成长规划师ID',
    package_id BIGINT COMMENT '关联模板',
    commission_rate INT COMMENT '佣金比例(基点,1000=10%)',
    teacher_id BIGINT COMMENT '成长规划师ID(旧字段)',
    template_id BIGINT COMMENT '关联模板(旧字段)',
    name VARCHAR(100) NOT NULL COMMENT '套餐名称',
    level VARCHAR(20) COMMENT '套餐等级',
    price INT NOT NULL COMMENT '销售价格(分)',
    validity_months INT COMMENT '有效期(月)',
    validity_days INT DEFAULT 0 COMMENT '有效期天数',
    description TEXT COMMENT '套餐说明',
    status VARCHAR(20) DEFAULT 'DRAFT' COMMENT '状态: DRAFT/PUBLISHED/PAUSED',
    sort_order INT DEFAULT 0 COMMENT '排序',
    sales_count INT DEFAULT 0 COMMENT '销量',
    share_code VARCHAR(50) COMMENT '分享码',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师套餐表';

-- 成长规划师套餐服务项表
CREATE TABLE IF NOT EXISTS guide_package_services (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    package_id BIGINT NOT NULL COMMENT '关联套餐',
    content_id BIGINT NOT NULL COMMENT '关联服务内容',
    quantity INT DEFAULT 1 COMMENT '数量',
    unit VARCHAR(20) COMMENT '单位'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师套餐服务项表';

-- 成长规划师订单表
CREATE TABLE IF NOT EXISTS guide_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    order_no VARCHAR(50) NOT NULL COMMENT '订单号',
    teacher_id BIGINT NOT NULL COMMENT '成长规划师ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    package_id BIGINT NOT NULL COMMENT '套餐ID',
    amount INT NOT NULL COMMENT '金额(分)',
    status VARCHAR(20) DEFAULT 'PENDING' COMMENT '状态: PENDING/PAID/COMPLETED/REFUNDED',
    pay_time DATETIME COMMENT '支付时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师订单表';

-- 佣金明细表
CREATE TABLE IF NOT EXISTS guide_commissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    teacher_id BIGINT NOT NULL COMMENT '成长规划师ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    amount INT NOT NULL COMMENT '佣金金额(分)',
    rate INT COMMENT '佣金比例(基点)',
    status VARCHAR(20) DEFAULT 'PENDING' COMMENT '状态: PENDING/COMPLETED',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='佣金明细表';

-- 服务记录表
CREATE TABLE IF NOT EXISTS guide_service_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    teacher_id BIGINT NOT NULL COMMENT '成长规划师ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    order_id BIGINT COMMENT '订单ID',
    service_content_id BIGINT NOT NULL COMMENT '服务内容ID',
    notes TEXT COMMENT '服务记录',
    duration INT COMMENT '服务时长(分钟)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务记录表';

-- 成长规划师邀请码表
CREATE TABLE IF NOT EXISTS guide_invite_codes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    guide_id BIGINT NOT NULL COMMENT '成长规划师用户ID',
    code VARCHAR(50) NOT NULL UNIQUE COMMENT '邀请码',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态',
    used_by_user_id BIGINT COMMENT '使用该邀请码的用户ID',
    used_at DATETIME COMMENT '使用时间',
    expire_at DATETIME COMMENT '过期时间',
    use_count INT DEFAULT 0 COMMENT '使用次数',
    max_use_count INT COMMENT '最大使用次数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师邀请码表';

-- =============================================
-- 财富维度表
-- =============================================

-- 财商打卡记录表
CREATE TABLE IF NOT EXISTS finance_checkins (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    checkin_type VARCHAR(20) NOT NULL COMMENT '打卡类型: daily_plan/saving/expense/review',
    checkin_date DATE NOT NULL COMMENT '打卡日期',
    amount INT COMMENT '金额(分,收支记录)',
    category VARCHAR(50) COMMENT '收支分类',
    note TEXT COMMENT '备注',
    mood VARCHAR(20) COMMENT '心情',
    earned_energy INT DEFAULT 0 COMMENT '获得的能量值',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user_date (user_id, checkin_date),
    INDEX idx_child (child_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财商打卡记录表';

-- 保单管理表
CREATE TABLE IF NOT EXISTS insurance_policies (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT COMMENT '关联孩子ID',
    policy_name VARCHAR(100) NOT NULL COMMENT '保单名称',
    insurance_company VARCHAR(100) COMMENT '保险公司',
    policy_type VARCHAR(50) COMMENT '保单类型: medical/life/accident/education/endowment',
    insured_person VARCHAR(50) NOT NULL COMMENT '被保人姓名',
    premium INT COMMENT '保费(分)',
    sum_insured INT COMMENT '保额(分)',
    start_date DATE COMMENT '生效日期',
    end_date DATE COMMENT '到期日期',
    renewal_type VARCHAR(20) DEFAULT 'annual' COMMENT '续费类型: annual/monthly/single',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/expired/cancelled',
    policy_no VARCHAR(100) COMMENT '保单号',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user (user_id),
    INDEX idx_child (child_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='保单管理表';

-- =============================================
-- 健康检测报告模块表
-- =============================================

-- 健康检测报告表
CREATE TABLE IF NOT EXISTS health_reports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '上传者用户ID(关联users表)',
    subject_id BIGINT COMMENT '报告主体用户ID(关联users表,为null表示未绑定)',
    family_id BIGINT COMMENT '家庭ID',
    report_type VARCHAR(32) NOT NULL COMMENT '报告类型: physical_exam(体检)/gut_flora(菌群检测)',
    report_date DATE COMMENT '报告日期',
    overall_score INT COMMENT '健康总分(0-100)',
    gut_health_score INT COMMENT '菌群健康分',
    chronic_disease_score INT COMMENT '慢病控制分',
    nutrition_score INT COMMENT '营养均衡分',
    gut_age VARCHAR(32) COMMENT '肠道预测年龄',
    gut_type VARCHAR(50) COMMENT '肠型',
    file_url VARCHAR(500) COMMENT '报告文件URL',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/archived',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_subject_id (subject_id),
    INDEX idx_family_id (family_id),
    INDEX idx_report_type (report_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康检测报告表';

-- 健康报告查看权限表
CREATE TABLE IF NOT EXISTS health_report_permissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    report_id BIGINT NOT NULL COMMENT '报告ID',
    user_id BIGINT NOT NULL COMMENT '有查看权限的用户ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY uk_report_user (report_id, user_id),
    INDEX idx_report_id (report_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康报告查看权限表';

-- 健康指标明细表
CREATE TABLE IF NOT EXISTS health_indicators (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    report_id BIGINT NOT NULL COMMENT '关联报告ID',
    category VARCHAR(50) COMMENT '指标分类: 营养状况/疾病风险/肠道屏障/神经递质/...',
    indicator_name VARCHAR(100) NOT NULL COMMENT '指标名称',
    indicator_value VARCHAR(100) COMMENT '检测值',
    unit VARCHAR(50) COMMENT '单位',
    ref_range VARCHAR(200) COMMENT '参考范围',
    status VARCHAR(50) COMMENT '评估状态: 正常/偏低/偏高/过多/注意/低风险/未检出',
    sort_order INT DEFAULT 0 COMMENT '显示排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_report_id (report_id),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康指标明细表';

-- 健康报告上传草稿表（两阶段入库的中间态）
CREATE TABLE IF NOT EXISTS health_report_drafts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '上传人用户ID',
    family_id BIGINT COMMENT '家庭ID',
    report_type VARCHAR(32) NOT NULL COMMENT '报告类型: physical_exam/gut_flora',
    file_url VARCHAR(500) COMMENT '上传的文件URL',
    original_filename VARCHAR(255) COMMENT '原始文件名',
    payload_json MEDIUMTEXT NOT NULL COMMENT '草稿载荷JSON(含解析/手动录入的所有指标)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/confirmed/discarded',
    expire_at DATETIME COMMENT '草稿过期时间',
    confirmed_report_id BIGINT COMMENT 'confirm阶段入库后的健康报告ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_status_expire (status, expire_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康报告上传草稿表(两阶段入库中间态)';

-- 教育体系配置表
CREATE TABLE IF NOT EXISTS education_systems (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(32) NOT NULL UNIQUE COMMENT '编码(key)',
    label VARCHAR(50) NOT NULL COMMENT '显示名称',
    description TEXT COMMENT '描述说明',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态: ACTIVE/DISABLED',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教育体系配置表';

INSERT INTO education_systems (name, label, description, sort_order) VALUES
('high_school', '中专/高中', '中等教育学历', 1),
('associate', '大专', '高等专科学历', 2),
('bachelor', '本科', '大学本科学历', 3),
('master', '硕士', '硕士研究生学历', 4),
('doctor', '博士', '博士研究生学历', 5);

-- ===== v1.2 先天属性体系 =====

-- 家庭成员先天属性表
CREATE TABLE IF NOT EXISTS family_member_attributes (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id        BIGINT       NOT NULL COMMENT '家庭成员ID（children表或users表）',
    member_type      VARCHAR(10)  NOT NULL COMMENT '成员类型: child/parent',
    birth_datetime   DATETIME     COMMENT '出生日期（精确到分钟，用于八字排盘）',
    eight_characters JSON         COMMENT '八字四柱JSON',
    wuxing_elements  JSON         COMMENT '五行星盘元素JSON',
    zodiac           VARCHAR(16)  COMMENT '生肖: rat/ox/tiger/rabbit/dragon/snake/horse/sheep/monkey/rooster/dog/pig',
    blood_type       VARCHAR(5)   COMMENT '血型: A/B/AB/O',
    mind_base_score  INT DEFAULT 0 COMMENT '心维度基础分(百分位)',
    wisdom_base_score INT DEFAULT 0 COMMENT '智维度基础分(百分位)',
    behavior_modifier JSON        COMMENT '行为修正因子JSON',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_member (member_id, member_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭成员先天属性表';

-- 星座配置表
CREATE TABLE IF NOT EXISTS zodiac_config (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    zodiac_code      VARCHAR(20)  NOT NULL UNIQUE COMMENT '星座代码',
    zodiac_name      VARCHAR(10)  NOT NULL COMMENT '星座名称',
    element          VARCHAR(10)  NOT NULL COMMENT '五行: 金木水火土',
    start_month      INT          NOT NULL COMMENT '起始月',
    start_day        INT          NOT NULL COMMENT '起始日',
    end_month        INT          NOT NULL COMMENT '结束月',
    end_day          INT          NOT NULL COMMENT '结束日',
    mind_base        INT DEFAULT 0 COMMENT '心先天基础分(百分位)',
    wisdom_base      INT DEFAULT 0 COMMENT '智先天基础分(百分位)',
    body_modifier    INT DEFAULT 0 COMMENT '身修正系数(千分比)',
    action_modifier  INT DEFAULT 0 COMMENT '行修正系数(千分比)',
    wealth_modifier  INT DEFAULT 0 COMMENT '富修正系数(千分比)',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='星座配置表';

-- 八字配置表
CREATE TABLE IF NOT EXISTS bazi_config (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    stem_or_branch   VARCHAR(10)  NOT NULL COMMENT '天干或地支',
    type             VARCHAR(10)  NOT NULL COMMENT '类型: stem/branch',
    element          VARCHAR(10)  NOT NULL COMMENT '五行: 金木水火土',
    yin_yang         VARCHAR(5)   COMMENT '阴阳: yin/yang',
    mind_base        INT DEFAULT 0 COMMENT '心先天基础分(百分位)',
    wisdom_base      INT DEFAULT 0 COMMENT '智先天基础分(百分位)',
    body_modifier    INT DEFAULT 0 COMMENT '身修正系数(千分比)',
    action_modifier  INT DEFAULT 0 COMMENT '行修正系数(千分比)',
    wealth_modifier  INT DEFAULT 0 COMMENT '富修正系数(千分比)',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='八字配置表（天干地支五行）';

-- 血型配置表
CREATE TABLE IF NOT EXISTS blood_type_config (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    blood_type       VARCHAR(5)   NOT NULL UNIQUE COMMENT '血型: A/B/AB/O',
    description      TEXT COMMENT '性格描述',
    mind_base        INT DEFAULT 0 COMMENT '心先天基础分(百分位)',
    wisdom_base      INT DEFAULT 0 COMMENT '智先天基础分(百分位)',
    body_modifier    INT DEFAULT 0 COMMENT '身修正系数(千分比)',
    action_modifier  INT DEFAULT 0 COMMENT '行修正系数(千分比)',
    wealth_modifier  INT DEFAULT 0 COMMENT '富修正系数(千分比)',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='血型配置表';

-- =============================================
-- 活动报名 与 能量规则引擎表(v1.4)
-- =============================================

-- 活动报名表
CREATE TABLE IF NOT EXISTS activity_registrations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    activity_id BIGINT NOT NULL COMMENT '活动ID',
    child_id BIGINT NOT NULL COMMENT '孩子ID',
    user_id BIGINT NOT NULL COMMENT '报名人用户ID(家长)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/approved/rejected/cancelled',
    registered_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '报名时间',
    approved_at DATETIME COMMENT '审核通过时间',
    approved_by BIGINT COMMENT '审核人用户ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_activity_child (activity_id, child_id),
    INDEX idx_activity_id (activity_id),
    INDEX idx_child_id (child_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动报名表';

-- 能量规则表(规则引擎)
CREATE TABLE IF NOT EXISTS energy_rule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    rule_code VARCHAR(64) NOT NULL UNIQUE COMMENT '规则编码(唯一)',
    rule_name VARCHAR(200) NOT NULL COMMENT '规则名称',
    event_type VARCHAR(32) NOT NULL COMMENT '事件类型: checkin/register/complete/cancel/overdue',
    action VARCHAR(16) NOT NULL COMMENT '动作: increase/decrease',
    dimension_code VARCHAR(20) COMMENT '维度代码: body/mind/wisdom/action/wealth(NULL=五维均衡)',
    amount INT COMMENT '固定变动量(基点)',
    amount_formula VARCHAR(255) COMMENT '动态公式(如: balance*0.1)',
    priority INT DEFAULT 0 COMMENT '优先级(数字越大越优先匹配)',
    conditions JSON COMMENT '触发条件(JSON表达式)',
    status TINYINT DEFAULT 1 COMMENT '状态: 1=启用 0=禁用',
    description TEXT COMMENT '规则描述',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_event_type (event_type),
    INDEX idx_status (status),
    INDEX idx_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能量规则表(规则引擎)';

CREATE TABLE IF NOT EXISTS activities (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(100) NOT NULL COMMENT '活动标题',
    description      TEXT COMMENT '活动描述',
    cover_image      VARCHAR(500) COMMENT '封面图片URL',
    dimension_code   VARCHAR(20) COMMENT '维度代码: body/mind/wisdom/action/wealth',
    activity_type    VARCHAR(20) DEFAULT 'offline' COMMENT '活动类型: offline/online/campaign',
    status           VARCHAR(20) DEFAULT 'draft' COMMENT '状态: draft/published/ended',
    start_time       DATETIME COMMENT '开始时间',
    end_time         DATETIME COMMENT '结束时间',
    location         VARCHAR(200) COMMENT '活动地点',
    max_participants INT DEFAULT 0 COMMENT '最大参与人数',
    current_participants INT DEFAULT 0 COMMENT '当前参与人数',
    price INT DEFAULT 0 COMMENT '活动价格(分)',
    vendor_id        BIGINT COMMENT '服务商ID',
    vendor_name      VARCHAR(100) COMMENT '服务商名称',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_dimension (dimension_code),
    INDEX idx_status (status),
    INDEX idx_start_time (start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动表';

-- v1.x: 活动工作流状态扩展
ALTER TABLE activities
  MODIFY COLUMN status VARCHAR(20) DEFAULT 'draft'
    COMMENT '状态：draft(草稿)/pending(待审核)/rejected(已驳回)/published(发布中)/withdrawn(已撤回)',
  ADD COLUMN audit_status VARCHAR(20) DEFAULT NULL
    COMMENT '审核状态：approved/pending/rejected',
  ADD COLUMN audit_reason TEXT DEFAULT NULL
    COMMENT '驳回原因',
  ADD COLUMN auditor_id BIGINT DEFAULT NULL
    COMMENT '审核人 ID',
  ADD COLUMN audited_at DATETIME DEFAULT NULL
    COMMENT '审核时间';

-- ===== v1.3 家庭成员体系 =====

-- 手动添加的家庭成员表（替代children表用于新成员）
CREATE TABLE IF NOT EXISTS family_members (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id        BIGINT       NOT NULL COMMENT '所属家庭ID',
    user_id          BIGINT       COMMENT '关联的用户ID（手机号匹配到已有用户时）',
    nickname         VARCHAR(100) NOT NULL COMMENT '昵称',
    phone            VARCHAR(20)  COMMENT '手机号',
    avatar           VARCHAR(500) COMMENT '头像',
    gender           VARCHAR(10)  COMMENT '性别: male/female',
    birthday         DATE         COMMENT '出生日期',
    relationship_type VARCHAR(50) NOT NULL COMMENT '关系类型key',
    role_override VARCHAR(20) DEFAULT 'auto' COMMENT '角色覆盖: auto/parent/child/elderly，空=auto',
    show_to_family INT DEFAULT 1 COMMENT '对家庭成员可见: 1=可见, 0=不可见',
    total_points INT DEFAULT 0 COMMENT '总积分',
    -- 孩子专属字段（原children表合并）
    id_card VARCHAR(18) DEFAULT NULL COMMENT '身份证号(孩子)',
    penalty_enabled TINYINT DEFAULT 1 COMMENT '是否扣减积分(0否1是)',
    theme VARCHAR(32) DEFAULT 'default' COMMENT '主题',
    system_points INT DEFAULT 0 COMMENT '系统积分(干预/平台奖励)',
    streak_days INT DEFAULT 0 COMMENT '连续打卡天数',
    last_task_date DATE COMMENT '上次任务日期',
    focus_max_daily TINYINT DEFAULT 3 COMMENT '每日专注上限',
    focus_remaining TINYINT DEFAULT 3 COMMENT '今日剩余专注次数',
    focus_reset_date DATE COMMENT '专注次数重置日期',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭成员表（手动添加）';

-- 家庭成员双向关系表（v2: 替代relationship_type/role_override）
CREATE TABLE IF NOT EXISTS family_relationships (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id        BIGINT       NOT NULL COMMENT '所属家庭ID',
    from_member_id   BIGINT       NOT NULL COMMENT '关系起点成员ID',
    to_member_id     BIGINT       NOT NULL COMMENT '关系终点成员ID',
    generation_value INT          NOT NULL COMMENT '辈分值: -1=子女, 0=同辈, 1=父母, 2=祖父母, 3=曾祖父母',
    relationship_key VARCHAR(50)  COMMENT '关系标签key（冗余用于显示, 如father/mother/son/daughter/spouse/sibling）',
    created_at       DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_from_to (from_member_id, to_member_id),
    INDEX idx_family_id (family_id),
    INDEX idx_from (from_member_id),
    INDEX idx_to (to_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭成员双向关系表';

-- 关系类型配置表（管理端可配置）
CREATE TABLE IF NOT EXISTS relationship_types (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    type_key         VARCHAR(50)  NOT NULL UNIQUE COMMENT '类型标识: spouse/parent/child/sibling',
    type_name        VARCHAR(100) NOT NULL COMMENT '类型显示名: 配偶/父母/子女/兄弟姐妹',
    capability_role  VARCHAR(20)  NOT NULL DEFAULT 'parent' COMMENT '能力角色: parent/child',
    default_role     VARCHAR(20)  DEFAULT NULL COMMENT '默认角色模板: parent/child（已废弃, 兼容保留）',
    sort_order       INT DEFAULT 0 COMMENT '排序序号',
    enabled          INT DEFAULT 1 COMMENT '是否启用: 1=启用, 0=禁用',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='关系类型配置表';

-- 默认关系类型数据（default_role: parent/child 为该关系类型的默认角色模板）
INSERT IGNORE INTO relationship_types (type_key, type_name, default_role, sort_order, enabled) VALUES
('spouse', '配偶', 'parent', 1, 1),
('parent', '父母', 'parent', 2, 1),
('child', '子女', 'child', 3, 1),
('sibling', '兄弟姐妹', 'child', 4, 1);

-- 用户收货地址表
CREATE TABLE IF NOT EXISTS user_address (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    receiver_name VARCHAR(50) NOT NULL COMMENT '收货人姓名',
    phone VARCHAR(20) NOT NULL COMMENT '收货人电话',
    province VARCHAR(50) COMMENT '省',
    city VARCHAR(50) COMMENT '市',
    district VARCHAR(50) COMMENT '区',
    street VARCHAR(200) COMMENT '详细地址',
    is_default TINYINT DEFAULT 0 COMMENT '是否默认地址 1=是 0=否',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_is_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户收货地址表';

-- =============================================
-- 新手引导奖励表
-- =============================================

CREATE TABLE IF NOT EXISTS onboarding_task (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    task_type VARCHAR(32) NOT NULL,
    reward_points INT DEFAULT 0,
    reward_energy INT DEFAULT 0,
    status VARCHAR(16) DEFAULT 'PENDING',
    completed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 成长任务配置表
-- =============================================

CREATE TABLE IF NOT EXISTS growth_task (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(16) NOT NULL COMMENT 'DAILY/NEWBIE',
    title VARCHAR(100) NOT NULL,
    description TEXT,
    reward_points INT DEFAULT 0,
    reward_energy INT DEFAULT 0,
    target_value INT DEFAULT 1,
    task_key VARCHAR(32) UNIQUE NOT NULL,
    enabled TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长任务配置表';

CREATE TABLE IF NOT EXISTS growth_task_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    task_id BIGINT NOT NULL,
    progress INT DEFAULT 0,
    completed TINYINT DEFAULT 0,
    claimed TINYINT DEFAULT 0,
    date VARCHAR(10) COMMENT 'YYYY-MM-DD for daily, empty for newbie',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_date (user_id, date),
    INDEX idx_task_id (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长任务用户进度表';

-- =============================================
-- 优惠券配置表
-- =============================================

CREATE TABLE IF NOT EXISTS coupon (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    type VARCHAR(16) DEFAULT 'FIXED',
    value INT DEFAULT 0 COMMENT '金额 单位:分',
    min_spend INT DEFAULT 0 COMMENT '最低消费 单位:分',
    applicable_to VARCHAR(32) DEFAULT 'ALL',
    valid_from DATETIME,
    valid_until DATETIME,
    total_count INT DEFAULT 0,
    used_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_valid_period (valid_from, valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券配置表';

CREATE TABLE IF NOT EXISTS user_coupon (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    coupon_id BIGINT NOT NULL,
    status VARCHAR(16) DEFAULT 'AVAILABLE',
    received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    used_at DATETIME,
    order_id BIGINT,
    INDEX idx_user_coupon (user_id, coupon_id),
    INDEX idx_status (status),
    INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户优惠券表';

-- =============================================
-- 邀请里程碑表
-- =============================================

CREATE TABLE IF NOT EXISTS invite_milestone (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    milestone VARCHAR(32) NOT NULL,
    reward_points INT DEFAULT 0,
    reward_energy INT DEFAULT 0,
    status VARCHAR(16) DEFAULT 'PENDING',
    achieved_at DATETIME,
    claimed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_milestone (user_id, milestone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邀请里程碑表';

-- =============================================
-- 推广排行榜周记录表
-- =============================================

CREATE TABLE IF NOT EXISTS referral_leaderboard (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    week_start VARCHAR(10) NOT NULL COMMENT '周一日期 YYYY-MM-DD',
    referral_count INT DEFAULT 0,
    `rank` INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_user_week (user_id, week_start),
    INDEX idx_week_rank (week_start, referral_count DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广排行榜周记录';

-- =============================================
-- 虚拟服务商团队（分销体系）
-- =============================================

CREATE TABLE IF NOT EXISTS `distribution_systems` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT '团队名称',
    `description` TEXT COMMENT '团队描述',
    `owner_id` BIGINT COMMENT '创建人(服务商用户ID)',
    `profit_rate` INT DEFAULT 0 COMMENT '利润分成比例(bps, 如2000=20%)',
    `enabled` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='虚拟服务商团队（分销体系）';

CREATE TABLE IF NOT EXISTS `distribution_relations` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `system_id` BIGINT NOT NULL COMMENT '所属团队ID',
    `user_id` BIGINT NOT NULL COMMENT '服务商用户ID',
    `parent_id` BIGINT COMMENT '上级服务商用户ID',
    `depth` INT DEFAULT 0 COMMENT '层级深度(0=root)',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_system (system_id),
    INDEX idx_user (user_id),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='团队-服务商关系（同一人在不同团队可处于不同位置）';

-- =============================================
-- 成长任务种子数据
-- =============================================

INSERT IGNORE INTO growth_task (type, title, description, reward_points, reward_energy, target_value, task_key, sort_order) VALUES
('DAILY', '每日签到', '完成每日签到', 20, 3, 1, 'DAILY_CHECKIN', 1),
('DAILY', '完成任务', '完成一个任务', 30, 5, 1, 'DAILY_TASK', 2),
('DAILY', '分享文章', '分享一篇好文给好友', 10, 2, 1, 'DAILY_SHARE', 3),
('DAILY', 'AI对话', '与AI助手进行一次对话', 15, 3, 1, 'DAILY_AI', 4);

-- ========== 全域指标定义表 ==========
-- ===== v1.5 家庭文化镜：关系兼容性配置 =====

-- 生肖配对规则表
CREATE TABLE IF NOT EXISTS zodiac_pairing (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    zodiac_code_1    VARCHAR(20) NOT NULL COMMENT '生肖代码1',
    zodiac_code_2    VARCHAR(20) NOT NULL COMMENT '生肖代码2',
    pairing_type     VARCHAR(20) NOT NULL COMMENT '配对类型: liuhe/sanhe/banhe/liuchong/xianghai/xiangxing/ping',
    compatibility_score INT DEFAULT 50 COMMENT '兼容分 0-100',
    description      TEXT COMMENT '配对解读',
    advice           TEXT COMMENT '相处建议',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_zodiac_pair (zodiac_code_1, zodiac_code_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生肖配对规则表';

-- 十二生肖六合/三合/六冲种子数据
INSERT IGNORE INTO zodiac_pairing (zodiac_code_1, zodiac_code_2, pairing_type, compatibility_score, description, advice) VALUES
('rat', 'ox', 'liuhe', 100, '子丑六合，天生一对', '你们是最佳拍档，互相补充，默契十足'),
('rat', 'monkey', 'sanhe', 85, '申子半合水局，默契度高', '智慧结合，创意无限，适合一起做规划'),
('rat', 'dragon', 'sanhe', 85, '子辰半合水局，相辅相成', '一个机灵一个大气，共同成就大事'),
('rat', 'horse', 'liuchong', 20, '子午相冲，水火不容', '价值观差异大，需要多包容理解'),
('rat', 'tiger', 'xiangxing', 30, '寅子相刑，容易误会', '沟通方式不同，建议用行动代替言语'),
('rat', 'rabbit', 'xiangxing', 30, '卯子相刑，互不相让', '各执己见时先冷静再沟通'),
('rat', 'sheep', 'xianghai', 35, '子未相害，暗中较劲', '表面和谐但内心可能有竞争意识'),
('ox', 'tiger', 'ping', 55, '牛虎平平，互敬互重', '踏实遇上冒险，可以互相学习'),
('ox', 'rabbit', 'xianghai', 35, '丑卯相害，各怀心思', '建议开诚布公，减少猜测'),
('ox', 'dragon', 'ping', 55, '牛龙平平，求同存异', '务实和理想主义的碰撞'),
('ox', 'snake', 'sanhe', 85, '巳酉丑三合金局，配合默契', '务实加智慧，黄金搭档'),
('ox', 'horse', 'xianghai', 35, '丑午相害，相处需技巧', '步调不同，需要彼此迁就'),
('ox', 'sheep', 'liuchong', 20, '丑未相冲，针锋相对', '一个固执一个犟，需要第三方调和'),
('tiger', 'rabbit', 'ping', 55, '虎兔平平，各有所长', '虎的魄力配兔的细腻，互补型'),
('tiger', 'dragon', 'ping', 55, '虎龙平平，互相欣赏', '都有王者风范，但要注意谁说了算'),
('tiger', 'snake', 'xianghai', 35, '寅巳相害，互相防备', '一个直率一个深沉，需要更多信任'),
('tiger', 'horse', 'sanhe', 85, '寅午半合火局，一拍即合', '热情似火，志同道合的行动派'),
('tiger', 'sheep', 'ping', 55, '虎羊平平，温柔与力量', '虎保护羊，羊温暖虎'),
('tiger', 'monkey', 'liuchong', 20, '寅申相冲，竞争关系', '针尖对麦芒，需要在竞争中找合作'),
('tiger', 'pig', 'liuhe', 100, '寅亥六合，情投意合', '虎猪相合，真诚相待的最佳组合'),
('rabbit', 'dragon', 'xianghai', 35, '卯辰相害，表面和谐', '温和遇到强势，需要找到平衡'),
('rabbit', 'snake', 'ping', 55, '兔蛇平平，温和相处', '都是内敛型，适合安静的陪伴'),
('rabbit', 'horse', 'ping', 55, '兔马平平，一动一静', '一个爱静一个爱动，互相调剂'),
('rabbit', 'sheep', 'sanhe', 85, '卯未半合木局，志趣相投', '文艺组合，品味相投'),
('rabbit', 'monkey', 'xiangxing', 30, '卯申相刑，互不买账', '思维方式差异大，需要耐心'),
('rabbit', 'rooster', 'liuchong', 20, '卯酉相冲，水火难容', '生活节奏完全不同，需要大包容'),
('rabbit', 'dog', 'liuhe', 100, '卯戌六合，忠诚相伴', '兔狗相合，彼此忠诚的最佳关系'),
('dragon', 'snake', 'ping', 55, '龙蛇平平，同源相惜', '都有神秘气质，惺惺相惜'),
('dragon', 'horse', 'ping', 55, '龙马平平，各有天地', '一个在天一个在地，互不干涉'),
('dragon', 'sheep', 'xiangxing', 30, '辰未相刑，暗中别扭', '看似无事却有暗流，多交流可化解'),
('dragon', 'monkey', 'sanhe', 85, '申子辰三合水局，天生队友', '最佳事业组合，互相成就'),
('dragon', 'rooster', 'liuhe', 100, '辰酉六合，珠联璧合', '龙凤配，传统的最佳搭档'),
('dragon', 'dog', 'liuchong', 20, '辰戌相冲，正面冲突', '硬碰硬的关系，需要智慧化解'),
('snake', 'horse', 'xiangxing', 30, '巳午相刑，热度不同', '蛇冷静马热情，温度要调和'),
('snake', 'sheep', 'sanhe', 85, '巳酉丑三合金局，智慧搭档', '沉稳加温柔，互相信任的深层关系'),
('snake', 'monkey', 'liuhe', 100, '巳申六合，智谋相投', '聪明人之间的默契'),
('snake', 'rooster', 'sanhe', 85, '巳酉丑三合金局，配合无间', '细节控和完美主义者的理想组合'),
('snake', 'pig', 'liuchong', 20, '巳亥相冲，南辕北辙', '一个深沉一个随性，需要大幅磨合'),
('horse', 'sheep', 'liuhe', 100, '午未六合，温情相依', '马羊相合，温暖体贴的最佳关系'),
('horse', 'monkey', 'xiangxing', 30, '午申相刑，互不理解', '行动派和思考派的碰撞'),
('horse', 'rooster', 'xiangxing', 30, '午酉相刑，意气用事', '都容易冲动，需要一个冷静者调和'),
('horse', 'dog', 'sanhe', 85, '寅午戌三合火局，行动搭档', '热情忠诚组合，一起行动效率高'),
('horse', 'pig', 'liuhe', 100, '亥午暗合，心有灵犀', '一静一动却暗合，出人意料的默契'),
('sheep', 'monkey', 'xiangxing', 30, '未申相刑，互挑毛病', '容易在小事上相互挑剔'),
('sheep', 'rooster', 'ping', 55, '羊鸡平平，互相欣赏', '羊的细腻欣赏鸡的精致'),
('sheep', 'dog', 'xiangxing', 30, '未戌相刑，各怀心事', '需要建立更深层的信任'),
('sheep', 'pig', 'sanhe', 85, '亥卯未三合木局，温柔组合', '温和的三合组，舒适无压力的关系'),
('monkey', 'rooster', 'ping', 55, '猴鸡平平，互相启发', '聪明人的相遇，火花四射'),
('monkey', 'dog', 'xiangxing', 30, '申戌相刑，互不信任', '需要时间证明真心'),
('monkey', 'pig', 'liuhe', 100, '申亥六合，智勇双全', '猴子机智猪包容，最佳朋友组合'),
('rooster', 'dog', 'xiangxing', 30, '酉戌相刑，互相挑剔', '完美主义遇见忠诚卫士，互相看不惯'),
('rooster', 'pig', 'ping', 55, '鸡猪平平，各取所需', '鸡的精明和猪的豁达互相补充'),
('dog', 'pig', 'sanhe', 85, '寅午戌三合火局，忠诚相伴', '忠诚的守护组合，互相支持');

-- 血型配对表
CREATE TABLE IF NOT EXISTS blood_type_pairing (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    blood_type_1     VARCHAR(5) NOT NULL COMMENT '血型1',
    blood_type_2     VARCHAR(5) NOT NULL COMMENT '血型2',
    compatibility_score INT DEFAULT 50 COMMENT '兼容分 0-100',
    description      TEXT COMMENT '配对描述',
    advice           TEXT COMMENT '相处建议',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_blood_pair (blood_type_1, blood_type_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='血型配对表';

INSERT IGNORE INTO blood_type_pairing (blood_type_1, blood_type_2, compatibility_score, description, advice) VALUES
('A', 'A', 80, '同型相吸，稳重默契', '都能理解对方的细腻敏感，但注意不要一起钻牛角尖'),
('A', 'B', 50, '互补型，有吸引力也有摩擦', 'B型的自由奔放吸引A型，但A型的严谨可能让B型感到压力'),
('A', 'O', 75, 'A主内O主外，搭配和谐', 'O型的大气包容A型的细致，是最常见的和谐组合'),
('A', 'AB', 70, 'AB型善解人意，A型感到被理解', 'AB型的理性刚好平衡A型的感性'),
('B', 'B', 75, '同型相投，自由自在', '都给彼此空间，但可能缺乏规划'),
('B', 'O', 70, 'O型包容B型，相处轻松', 'O型的稳定给了B型安全感'),
('B', 'AB', 65, '互相欣赏，各有天地', 'B型的创意和AB型的分析能力互补'),
('O', 'O', 85, '强强联合，行动力爆棚', '都是行动派，但要注意不要都太强势'),
('O', 'AB', 75, 'O型领导，AB型军师', 'O型的大方向加AB型的精细思维，无敌组合'),
('AB', 'AB', 60, '互相理解但可能过度理性', '两个理性派，别忘了情感交流');

-- 生命灵数关系解读表
CREATE TABLE IF NOT EXISTS life_number_relationship (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    relationship_number INT NOT NULL COMMENT '关系数字 1-9',
    title            VARCHAR(100) COMMENT '关系主题',
    description      TEXT COMMENT '关系解读',
    advice           TEXT COMMENT '相处建议',
    color_hex        VARCHAR(10) COMMENT '代表色',
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_relationship_num (relationship_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生命灵数关系解读表';

INSERT IGNORE INTO life_number_relationship (relationship_number, title, description, advice, color_hex) VALUES
(1, '开创者关系', '你们在一起充满创造力和领导力，彼此激励去实现目标', '注意不要互相竞争谁说了算，明确分工才能发挥最大能量', '#FF4444'),
(2, '合作伙伴关系', '你们是天生的搭档，善于倾听和配合，营造和谐氛围', '过于迁就对方可能会失去自我，保持适度独立才能长久', '#FF8C42'),
(3, '创意火花关系', '在一起充满欢乐和创造力，社交圈会因为你们而扩大', '注意不要太表面化，偶尔需要深度的对话来维系情感', '#FFD700'),
(4, '建设者关系', '你们是务实稳健的组合，能一起把想法变成现实', '生活不能只有工作和责任，记得给彼此创造惊喜和浪漫', '#10B981'),
(5, '探险者关系', '在一起充满变化和新鲜感，喜欢一起探索未知', '变化太多可能会让关系缺乏安全感，寻找一些共同的固定仪式', '#3B82F6'),
(6, '守护者关系', '你们的关系以关爱和责任为基础，彼此是最坚实的后盾', '付出太多可能会感到疲惫，学会接受对方的爱和照顾', '#6366F1'),
(7, '智者关系', '在一起有深度的精神交流和思想碰撞，追求真理', '不要沉浸在各自的世界里，记得用简单的方式表达爱', '#8B5CF6'),
(8, '成就者关系', '你们是强强联合，追求物质和事业上的共同成就', '在追求成功的同时不要忽视感情的温度，平衡事业和生活', '#F59E0B'),
(9, '圆满者关系', '在一起有强烈的宿命感，彼此理解和支持，是灵魂伴侣', '太理想化可能会失望，接受对方的不完美才能走得更远', '#FF6B9D');

CREATE TABLE IF NOT EXISTS indicator_definitions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(50) NOT NULL COMMENT '领域: physical/mental/nutrition/behavior/social',
    category VARCHAR(50) NOT NULL COMMENT '分类: 舌色/苔色/情绪/...',
    code VARCHAR(50) NOT NULL UNIQUE COMMENT '编码: tongue_color/coating_color/...',
    name VARCHAR(100) NOT NULL COMMENT '显示名: 舌体颜色/苔色/...',
    data_type VARCHAR(20) NOT NULL DEFAULT 'enum' COMMENT '数据类型: enum/number/string',
    options JSON COMMENT '枚举值列表: ["淡红","红","绛","青紫"] 或 null',
    unit VARCHAR(50) COMMENT '单位',
    ref_range VARCHAR(200) COMMENT '参考范围',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status TINYINT DEFAULT 1 COMMENT '0=禁用 1=启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='全域指标定义';

-- ========== 全域指标值表 ==========
CREATE TABLE IF NOT EXISTS indicator_values (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    source_type VARCHAR(50) NOT NULL COMMENT '来源: tongue_record/health_report/assessment',
    source_id BIGINT NOT NULL COMMENT '来源记录ID',
    definition_id BIGINT NOT NULL COMMENT 'FK→indicator_definitions.id',
    value VARCHAR(200) COMMENT '值',
    numeric_value DECIMAL(10,2) COMMENT '数值(number类型时用)',
    remark TEXT COMMENT '备注',
    recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_source (source_type, source_id),
    INDEX idx_definition (definition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='全域指标值';

-- ========== 舌诊主记录表 ==========
CREATE TABLE IF NOT EXISTS tongue_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    child_id BIGINT COMMENT '孩子ID',
    image_url VARCHAR(500) COMMENT '舌象图片URL',
    overall_assessment TEXT COMMENT '总体评估',
    status VARCHAR(20) DEFAULT 'draft' COMMENT 'draft/confirmed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_member (member_id, child_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='舌诊主记录';

-- ========== 种子数据：舌诊7项指标定义 ==========
INSERT IGNORE INTO indicator_definitions (domain, category, code, name, data_type, options, sort_order, status) VALUES
('physical', '舌色', 'tongue_color', '舌体颜色', 'enum', '["淡红","红","绛","青紫"]', 1, 1),
('physical', '苔色', 'coating_color', '苔色', 'enum', '["薄白","白","黄","灰黑"]', 2, 1),
('physical', '苔质', 'coating_texture', '苔质', 'enum', '["润","燥","滑","涩","腻"]', 3, 1),
('physical', '裂纹', 'fissure', '裂纹程度', 'enum', '["无","轻","中","重"]', 4, 1),
('physical', '齿痕', 'teeth_mark', '齿痕程度', 'enum', '["无","轻","中","重"]', 5, 1),
('physical', '舌下络脉', 'sublingual_vein', '舌下络脉', 'enum', '["正常","轻","中","重"]', 6, 1),
('physical', '体质', 'constitution', '体质辨识', 'enum', '["平和质","气虚质","阳虚质","阴虚质","湿热质","痰湿质","血瘀质","气郁质","特禀质"]', 7, 1);

-- =============================================
-- 活动报名 & 能量规则引擎 v1.4 - 字段扩展
-- 通过 DatabaseInitializer.java 在 ALTER 阶段执行
-- =============================================

-- 活动表扩展字段
ALTER TABLE activities ADD COLUMN require_registration TINYINT DEFAULT 1 COMMENT '是否需要报名: 1=需要, 0=直接参与';
ALTER TABLE activities ADD COLUMN require_checkin_review TINYINT DEFAULT 1 COMMENT '打卡是否需要审核: 1=需要, 0=直接通过';
ALTER TABLE activities ADD COLUMN energy_penalty_unregistered INT DEFAULT 5 COMMENT '未报名打卡的行能量扣减值';

-- ============================================================
-- 新增：系统配置表（角色年龄阈值等）
-- ============================================================
CREATE TABLE IF NOT EXISTS system_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    description TEXT COMMENT '说明',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- ============================================================
-- 数据迁移: children → family_members + roleOverride 变更
-- 执行时机: Phase 9（所有代码变更完成后、上线前）
-- ============================================================

-- 1. family_members.capability_role → role_override (已在 DDL 中 ALTER)
-- 2. relationship_types.capability_role → default_role (已在 DDL 中 ALTER)

-- ============================================================
-- 数据迁移: children → family_members + roleOverride 变更
-- 执行时机: Phase 9（所有代码变更完成后、上线前）
-- ============================================================

-- 1. family_members 已包含所有必要字段（role_override, 孩子专属字段已在 DDL 中）
-- relationship_types.capability_role → default_role 已在 DDL 中 ALTER

-- 2. 迁移 children 数据到 family_members（匹配更新：family_members 已存在该成员记录时更新）
UPDATE family_members fm
INNER JOIN children c ON fm.family_id = c.family_id AND fm.nickname = c.nickname AND fm.user_id = c.user_id
SET
    fm.id_card = c.id_card,
    fm.penalty_enabled = c.penalty_enabled,
    fm.theme = c.theme,
    fm.system_points = c.system_points,
    fm.streak_days = c.streak_days,
    fm.last_task_date = c.last_task_date,
    fm.focus_max_daily = c.focus_max_daily,
    fm.focus_remaining = c.focus_remaining,
    fm.focus_reset_date = c.focus_reset_date,
    fm.total_points = c.total_points,
    fm.show_to_family = c.show_to_family,
    fm.birthday = c.birthday,
    fm.gender = c.gender,
    fm.phone = c.phone,
    fm.role_override = 'child';

-- 3. 迁移 children 数据到 family_members（不匹配则插入新记录）
INSERT INTO family_members (family_id, user_id, nickname, birthday, gender, phone,
  id_card, penalty_enabled, theme, system_points, streak_days, last_task_date,
  focus_max_daily, focus_remaining, focus_reset_date, show_to_family, total_points,
  role_override, created_at, updated_at)
SELECT family_id, user_id, nickname, birthday, gender, phone,
  id_card, penalty_enabled, theme, system_points, streak_days, last_task_date,
  focus_max_daily, focus_remaining, focus_reset_date, show_to_family, total_points,
  'child', created_at, updated_at
FROM children
WHERE NOT EXISTS (
    SELECT 1 FROM family_members fm2
    WHERE fm2.family_id = children.family_id
      AND fm2.nickname = children.nickname
      AND fm2.user_id = children.user_id
);

-- 4. 插入 system_config 初始数据
INSERT INTO system_config (config_key, config_value, description) VALUES
  ('child_age_threshold', '18', '未成年年龄分界线（岁）'),
  ('elderly_age_threshold', '65', '老年年龄分界线（岁）')
ON DUPLICATE KEY UPDATE config_value = VALUES(config_value);

-- 5. 删除 children 表（最后执行，确认迁移完成后再执行此步）
-- DROP TABLE IF EXISTS children;

-- ============================================================
-- 购物车表
-- ============================================================
CREATE TABLE IF NOT EXISTS cart (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    quantity INT NOT NULL DEFAULT 1 COMMENT '数量',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_product (user_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车';

-- ============================================================
-- 供应商分销体系
-- ============================================================

CREATE TABLE IF NOT EXISTS supply_system (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '体系名称',
    admin_id BIGINT NOT NULL COMMENT '管理员用户ID',
    settlement_period_days INT NOT NULL DEFAULT 30 COMMENT '账期（天）',
    platform_profit_rate DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '平台留利比例(%)',
    description TEXT COMMENT '描述',
    status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active/disabled',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_admin_id (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商体系';

CREATE TABLE IF NOT EXISTS supply_system_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    system_id BIGINT NOT NULL COMMENT '体系ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    role VARCHAR(20) NOT NULL DEFAULT 'member' COMMENT '角色: admin/member',
    status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active/inactive',
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_system_user (system_id, user_id),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='体系成员';

CREATE TABLE IF NOT EXISTS supply_settlement (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    system_id BIGINT NOT NULL COMMENT '体系ID',
    period_start DATE NOT NULL COMMENT '结算周期开始',
    period_end DATE NOT NULL COMMENT '结算周期结束',
    total_sales INT NOT NULL DEFAULT 0 COMMENT '销售总额(分)',
    platform_profit INT NOT NULL DEFAULT 0 COMMENT '平台留利金额(分)',
    supplier_payout INT NOT NULL DEFAULT 0 COMMENT '供应商实付(分)',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/settled',
    settled_at DATETIME COMMENT '结算时间',
    remark TEXT COMMENT '备注',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_system_id (system_id),
    KEY idx_period (period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='结算记录';

CREATE TABLE IF NOT EXISTS supply_settlement_detail (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    settlement_id BIGINT NOT NULL COMMENT '结算ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称',
    amount INT NOT NULL COMMENT '订单金额(分)',
    platform_fee INT NOT NULL COMMENT '平台留利(分)',
    supplier_id BIGINT COMMENT '收款供应商用户ID',
    supplier_name VARCHAR(100) COMMENT '收款供应商名称',
    payout INT NOT NULL COMMENT '供应商实付(分)',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_settlement_id (settlement_id),
    KEY idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='结算明细';

-- =============================================
-- 收货人与购买信息采集
-- =============================================

CREATE TABLE IF NOT EXISTS consignees (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '所属用户ID',
    name VARCHAR(100) NOT NULL COMMENT '收货人姓名',
    phone VARCHAR(20) COMMENT '手机号',
    id_card VARCHAR(20) COMMENT '身份证号',
    hand_signature TEXT COMMENT '手签名(base64图片或URL)',
    ethnicity VARCHAR(20) COMMENT '民族',
    blood_type VARCHAR(20) COMMENT '血型',
    address VARCHAR(500) COMMENT '收货地址',
    is_default TINYINT(1) DEFAULT 0 COMMENT '是否默认收货人',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货人信息（含购买所需特殊信息）';

CREATE TABLE IF NOT EXISTS product_purchase_fields (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    field_key VARCHAR(50) NOT NULL COMMENT '字段标识: name/phone/id_card/hand_signature/ethnicity/blood_type',
    field_name VARCHAR(100) NOT NULL COMMENT '显示名: 收货人姓名/手机号/身份证号/手签名/民族/血型',
    is_required TINYINT(1) DEFAULT 1 COMMENT '是否必填 1=是 0=否',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_product_id (product_id),
    UNIQUE KEY uk_product_field (product_id, field_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品购买信息字段配置';

-- AI管家会话表
CREATE TABLE IF NOT EXISTS butler_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    title VARCHAR(200) DEFAULT '新会话' COMMENT '会话标题',
    conversation_id VARCHAR(100) COMMENT 'Dify对话ID',
    context TEXT COMMENT '上下文信息(JSON)',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active(进行中)/archived(已归档)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI管家会话表';

-- 家庭订阅记录
CREATE TABLE IF NOT EXISTS member_subscription (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    `level` VARCHAR(10) NOT NULL DEFAULT 'L1' COMMENT 'L1=一生一世, L2=久久一生',
    status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT 'active/expired/cancelled',
    start_time DATETIME DEFAULT NULL COMMENT '开始时间',
    expire_time DATETIME DEFAULT NULL COMMENT '到期时间',
    payment_id BIGINT DEFAULT NULL COMMENT '关联订单ID',
    auto_renew TINYINT(1) DEFAULT 0 COMMENT '自动续费(L2)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id),
    INDEX idx_status (status),
    INDEX idx_expire_time (expire_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭订阅记录';

-- 订阅订单
CREATE TABLE IF NOT EXISTS member_subscription_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    `level` VARCHAR(10) NOT NULL COMMENT 'L1/L2',
    amount INT NOT NULL COMMENT '金额(分)',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/paid/cancelled/refunded',
    payment_type VARCHAR(20) DEFAULT NULL COMMENT '支付方式 wechat/alipay',
    transaction_id VARCHAR(64) DEFAULT NULL COMMENT '微信支付订单号',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME DEFAULT NULL,
    UNIQUE KEY uk_order_no (order_no),
    INDEX idx_family_id (family_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订阅订单';

-- 权益使用日志
CREATE TABLE IF NOT EXISTS subscription_benefit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    subscription_id BIGINT NOT NULL COMMENT '订阅ID',
    benefit_code VARCHAR(50) NOT NULL COMMENT '权益代码',
    used_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    detail TEXT COMMENT '使用详情(JSON)',
    INDEX idx_subscription_id (subscription_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权益使用日志';

-- 推广等级 R0-R4
CREATE TABLE IF NOT EXISTS promotion_tier (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    tier VARCHAR(20) NOT NULL DEFAULT 'R0' COMMENT 'R0利他者/R1传福人/R2聚能师/R3启慧者/R4传承者',
    team_size_1st INT DEFAULT 0 COMMENT '一层团队人数',
    team_size_2nd INT DEFAULT 0 COMMENT '二层团队人数',
    team_size_3rd INT DEFAULT 0 COMMENT '三层团队人数',
    total_team_size INT DEFAULT 0 COMMENT '三层累计总人数',
    total_referral_earnings INT DEFAULT 0 COMMENT '累计推荐佣金(分)',
    total_share_earnings INT DEFAULT 0 COMMENT '累计消费分润(分)',
    promoted_at DATETIME DEFAULT NULL COMMENT '最近晋级时间',
    last_change_at DATETIME DEFAULT NULL COMMENT '最后变更时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_id (user_id),
    INDEX idx_tier (tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广等级';

-- 推广等级变更记录
CREATE TABLE IF NOT EXISTS promotion_tier_change_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    old_tier VARCHAR(20) DEFAULT NULL COMMENT '变更前等级',
    new_tier VARCHAR(20) NOT NULL COMMENT '变更后等级',
    reason VARCHAR(50) DEFAULT NULL COMMENT 'qualify升级/demote降级/admin管理员调整',
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广等级变更记录';

-- 推荐关系树（三层）
CREATE TABLE IF NOT EXISTS referral_tree (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT NOT NULL COMMENT '推荐人ID',
    child_id BIGINT NOT NULL COMMENT '被推荐人ID',
    level INT NOT NULL COMMENT '层级深度(1/2/3)',
    path VARCHAR(255) DEFAULT NULL COMMENT 'materialized path如/1/2/3/',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_child_id (child_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推荐关系树';

-- 产品P点配置
CREATE TABLE IF NOT EXISTS product_ppoint (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '产品ID',
    ppoint INT NOT NULL DEFAULT 0 COMMENT 'P点值(平台利润分)',
    start_date DATE DEFAULT NULL COMMENT '生效日期',
    end_date DATE DEFAULT NULL COMMENT '失效日期',
    created_by BIGINT DEFAULT NULL COMMENT '创建人',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_id (product_id),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品P点配置';

-- 测评商品扩展信息
CREATE TABLE IF NOT EXISTS assessment_products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '关联products表',
    assessment_type VARCHAR(20) NOT NULL COMMENT 'single=单包, bundle=套餐',
    total_sessions INT NOT NULL DEFAULT 1 COMMENT '包含测评次数',
    validity_days INT NOT NULL DEFAULT 365 COMMENT '有效期(天)',
    guide_scope VARCHAR(50) DEFAULT 'all' COMMENT '规划师范围: all/assigned/auto',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评商品扩展信息';

-- 测评额度表
CREATE TABLE IF NOT EXISTS assessment_quotas (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    quota_no VARCHAR(32) NOT NULL COMMENT '额度编号',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    child_id BIGINT COMMENT '指定孩子ID(NULL=不限)',
    product_order_id BIGINT NOT NULL COMMENT '来源订单ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    product_name VARCHAR(100) NOT NULL COMMENT '商品名快照',
    total_sessions INT NOT NULL DEFAULT 1 COMMENT '总次数',
    remaining_sessions INT NOT NULL DEFAULT 1 COMMENT '剩余次数',
    validity_start DATETIME NOT NULL COMMENT '有效期开始',
    validity_end DATETIME NOT NULL COMMENT '有效期截止',
    status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT 'active/expired/used_all/cancelled',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family (family_id),
    INDEX idx_order (product_order_id),
    UNIQUE KEY uk_quota_no (quota_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评额度表';

-- 测评执行记录表
CREATE TABLE IF NOT EXISTS assessment_executions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    quota_id BIGINT NOT NULL COMMENT '关联额度ID',
    child_id BIGINT NOT NULL COMMENT '被测评孩子',
    guide_id BIGINT COMMENT '执行规划师ID',
    assessment_type VARCHAR(30) COMMENT '本次具体测评类型',
    appointment_id BIGINT COMMENT '关联预约ID',
    result_id BIGINT COMMENT '关联测评结果ID',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/in_progress/completed/cancelled',
    scheduled_date DATETIME COMMENT '预约执行时间',
    completed_at DATETIME COMMENT '实际完成时间',
    notes VARCHAR(500) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_quota (quota_id),
    INDEX idx_child (child_id),
    INDEX idx_appointment (appointment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评执行记录表';

-- 测评指导意见表（结构化存储）
CREATE TABLE IF NOT EXISTS growth_guidances (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    result_id BIGINT NOT NULL COMMENT '关联测评结果ID',
    dimension VARCHAR(30) NOT NULL COMMENT '维度: attention/focus/memory/logic/emotion/general',
    score INT COMMENT '该维度得分(0-100)',
    suggestion TEXT NOT NULL COMMENT '建议内容',
    priority INT DEFAULT 0 COMMENT '优先级(0=普通, 1=重要, 2=紧急)',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_result (result_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评指导意见(结构化)';

-- 方案生成规则表
CREATE TABLE IF NOT EXISTS assessment_plan_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dimension VARCHAR(30) NOT NULL COMMENT '测评维度: attention/focus/memory/logic/emotion',
    score_min INT NOT NULL COMMENT '分数下限(含)',
    score_max INT NOT NULL COMMENT '分数上限(含)',
    template_id BIGINT NOT NULL COMMENT '关联TaskTemplatePackage ID',
    priority INT DEFAULT 0 COMMENT '优先级',
    is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_dimension (dimension),
    INDEX idx_score (score_min, score_max)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='方案生成规则';