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') 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 '出生时辰: 子丑寅卯辰巳午未申酉戌亥',
    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;

-- 孩子详情表
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 VARCHAR(500),
    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,
    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 VARCHAR(255),
    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,
    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_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 '综合得分',
    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,
    INDEX idx_child_id (child_id),
    INDEX idx_teacher_id (teacher_id),
    INDEX idx_assessment_date (assessment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 成长规划表
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 VARCHAR(500) 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 VARCHAR(500) 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 VARCHAR(500) 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 VARCHAR(255) 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 VARCHAR(500) 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 VARCHAR(500) 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 VARCHAR(500) COMMENT '拒绝原因',
    reject_count INT DEFAULT 0 COMMENT '被拒绝次数',
    cancelled_by BIGINT COMMENT '取消者ID',
    cancelled_at DATETIME COMMENT '取消时间',
    cancel_reason VARCHAR(500) 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 VARCHAR(1000) 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 VARCHAR(500) 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 '平台利润率(千分比)',
    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 VARCHAR(500) COMMENT '备注',
    paid_at 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 sys_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    config_key VARCHAR(64) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    description VARCHAR(500) 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 VARCHAR(500) 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 '预计阅读分钟数',
    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='文章表';

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

-- 能量维度定义表
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 VARCHAR(500) 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 VARCHAR(255) 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 VARCHAR(255) 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 VARCHAR(500) 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 VARCHAR(500) 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 VARCHAR(500) 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 VARCHAR(500) 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 VARCHAR(200) 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 VARCHAR(500) 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 VARCHAR(500) 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 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 VARCHAR(200) 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      VARCHAR(200) 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 activities (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(100) NOT NULL COMMENT '活动标题',
    description      VARCHAR(500) 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.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',
    capability_role  VARCHAR(20)  NOT NULL COMMENT '能力角色: parent/child（决定界面权限）',
    show_to_family   INT DEFAULT 1 COMMENT '对家庭成员可见: 1=可见, 0=不可见',
    total_points     INT 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_phone (phone)
) 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 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='关系类型配置表';

-- 默认关系类型数据
INSERT IGNORE INTO relationship_types (type_key, type_name, capability_role, sort_order) VALUES
('spouse', '配偶', 'parent', 1),
('parent', '父母', 'parent', 2),
('child', '子女', 'child', 3),
('sibling', '兄弟姐妹', 'child', 4);

-- 用户收货地址表
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 VARCHAR(255),
    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='推广排行榜周记录';

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

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);