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

-- 家庭表
CREATE TABLE IF NOT EXISTS families (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    invite_code VARCHAR(8) UNIQUE,
    butler_id BIGINT COMMENT '绑定的管家userId',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_invite_code (invite_code),
    INDEX idx_butler_id (butler_id)
) 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,
    role VARCHAR(20) NOT NULL,
    roles VARCHAR(100) COMMENT '角色列表JSON/逗号分隔(多角色)',
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    password VARCHAR(128),
    session_key VARCHAR(64) COMMENT '微信登录会话密钥(虚拟支付签名用)',
    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(福宝)',
    portrait_prompt TEXT COMMENT '用户自定义画像 prompt（为空时自动从 profile_snapshot 渲染）',
    family_inviter_id BIGINT DEFAULT NULL COMMENT '家庭邀请人ID（首次注册时绑定，用于追踪邀请来源）',
    province VARCHAR(50) DEFAULT NULL COMMENT '省份',
    city VARCHAR(50) DEFAULT NULL COMMENT '城市',
    district VARCHAR(50) DEFAULT NULL COMMENT '区县',
    street VARCHAR(100) DEFAULT NULL 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;

-- ============================================================
-- 【已废除】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 'CF值(测评建档/平台发放)',
--     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/member',
    executor_id BIGINT COMMENT '执行者ID',
    title VARCHAR(200) NOT NULL,
    description TEXT,
    points TINYINT DEFAULT 2 COMMENT '任务积分1-10',
    deadline DATETIME NOT NULL,
    accept_deadline DATETIME DEFAULT NULL COMMENT '接受截止时间（分配给其他家庭成员的任务）',
    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', 'assigned', 'in_progress', 'completed', 'rejected', 'overdue', 'cancelled', 'locked', 'pending_review') DEFAULT 'pending',
    completed_at DATETIME,
    bonus_rule JSON COMMENT '积分奖励规则',
    points_expire_days INT DEFAULT 0 COMMENT '积分有效期天数',
    dimension_code VARCHAR(20) COMMENT '五维维度: body/mind/wisdom/action/wealth',
    dimension_weights JSON COMMENT '五维权重: {"body":0,"mind":0,"wisdom":0,"action":0,"wealth":0}',
    parent_task_id BIGINT NULL COMMENT '父任务ID（购买任务→每日使用子任务链；子任务在父任务完成前不显示）',
    -- TASK-SYS-248: 行为调度中心扩展字段
    prerequisite_task_id BIGINT NULL COMMENT '前置任务ID：完成后才解锁本任务',
    action_type VARCHAR(32) NULL COMMENT '联动动作: minigame/focus/survey/article/activity/cart/order',
    action_config JSON NULL COMMENT '联动参数: {"gameCode":"schulte","productId":123,"minSeconds":30,"completeMilestone":"paid"/"received","surveyId":99}',
    require_input TINYINT DEFAULT 0 COMMENT '1=需填写内容才能完成',
    start_required TINYINT DEFAULT 0 COMMENT '1=需先开始再完成（两阶段）',
    start_count INT DEFAULT 0 COMMENT '已开始次数（累计）',
    min_duration_seconds 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),
    INDEX idx_dimension_code (dimension_code),
    INDEX idx_executor_assigned (executor_type, executor_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务执行进度表（两阶段/里程碑/填写内容记录）
CREATE TABLE IF NOT EXISTS task_executions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT NOT NULL COMMENT '任务实例ID',
    member_id BIGINT NOT NULL COMMENT '执行成员ID(family_members.id)',
    status VARCHAR(20) NOT NULL DEFAULT 'started' COMMENT 'started/finished/failed',
    started_at DATETIME NOT NULL COMMENT '开始时间',
    finished_at DATETIME COMMENT '完成时间',
    milestone VARCHAR(32) COMMENT '购物任务里程碑: cart_added/paid/received（当前进度）',
    content TEXT COMMENT '填写内容（require_input 任务）',
    content_type VARCHAR(20) COMMENT 'content 类型: text/image/audio/video',
    extra JSON COMMENT '扩展数据: {gameScore, focusMinutes, orderId, articleId, surveyId}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_task_member (task_id, member_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务执行进度表';

-- 积分流水表
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-任务积分, cf-CF值',
    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,
    task_id BIGINT NULL COMMENT '关联的任务ID（专注任务挂钩）',
    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',
    assessor_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',
    dimension VARCHAR(10) DEFAULT 'mind' COMMENT '报告维度: mind(心-A2)/wisdom(智-B4)',
    report_file_id VARCHAR(500) COMMENT '上传文件媒资ID或相对路径',
    draft_status VARCHAR(10) DEFAULT 'pending' COMMENT '草稿状态: pending(待确认)/confirmed(已确认)',
    dan_level VARCHAR(10) DEFAULT NULL COMMENT 'DAN等级A/B/C/D',
    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',
    family_member_id BIGINT COMMENT '家庭成员ID',
    family_member_age INT COMMENT '家庭成员年龄(月)',
    parent_id BIGINT NOT NULL COMMENT '家长用户ID',
    family_member_id BIGINT COMMENT '关联家庭成员ID',
    family_member_age INT COMMENT '成员年龄快照',
    assessment_id BIGINT COMMENT '关联的测评结果ID',
    record_title VARCHAR(200) COMMENT '档案标题',
    child_age INT COMMENT '孩子当时年龄(月)',
    height VARCHAR(20) COMMENT '身高',
    weight VARCHAR(20) COMMENT '体重',
    school VARCHAR(100) COMMENT '学校名称',
    grade INT COMMENT '年级(数字)',
    academicPerformance VARCHAR(200) COMMENT '成绩/学业表现',
    schoolProvince VARCHAR(50) COMMENT '学校所在省',
    schoolCity VARCHAR(50) COMMENT '学校所在市',
    schoolDistrict VARCHAR(50) COMMENT '学校所在区',
    schoolStreet VARCHAR(100) 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 '外部系统来源',
    source_type VARCHAR(20) DEFAULT 'manual' COMMENT '来源类型: manual/platform_purchase/self_upload/supplement',
    source_file_url VARCHAR(500) COMMENT '原始报告文件URL',
    order_id BIGINT COMMENT '关联订单ID',
    report_date DATETIME COMMENT '测评/报告日期',
    parent_record_id BIGINT COMMENT '关联的原始档案ID（补充档案专用）',
    is_latest TINYINT(1) DEFAULT 1 COMMENT '是否同组最新记录',
    dedup_hash VARCHAR(64) COMMENT '去重哈希值(SHA256)',
    parsed_data TEXT COMMENT '上传报告的解析结果(JSON)',
    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),
    UNIQUE INDEX uk_growth_dedup_hash (dedup_hash)
) 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 '平台服务费(分)',
    user_coupon_id BIGINT COMMENT '使用的用户优惠券ID',
    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 DEFAULT 'login' COMMENT '验证码类型: login/register/reset',
    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 '价格(分)',
    purchase_price DECIMAL(10,2) DEFAULT 0.00 COMMENT '进价(分)',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存',
    min_stock_alert INT DEFAULT 0 COMMENT '库存预警阈值(低于此值预警)',
    sales_count INT NOT NULL DEFAULT 0 COMMENT '销量',
    product_type VARCHAR(32) NOT NULL COMMENT '商品类型: activity/course/physical/digital/service',
    growth_category VARCHAR(50) DEFAULT NULL COMMENT '成长分类',
    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',
    dimension_weights JSON DEFAULT NULL COMMENT '五维权重百分比: {"body":0,"mind":0,"wisdom":0,"action":0,"wealth":0}',
    profit_rate INT DEFAULT 0 COMMENT '平台利润率(千分比)',
    sort_order INT DEFAULT 0 COMMENT '排序(值越小越靠前)',
    points_multiplier DECIMAL(10,2) NOT NULL DEFAULT 1.00 COMMENT '平台积分倍数，1元=1积分×倍数',
    delivery_method TINYINT DEFAULT 1 COMMENT '配送方式: 1=快递 2=自提 3=两者皆可 4=线上-可选人 5=线上-单人',
    purchase_count_threshold INT DEFAULT 0 COMMENT '最小购买数量限制',
    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',
    family_scope TINYINT DEFAULT 1 COMMENT '是否面向家庭: 1=家庭成员消费过即排除, 0=仅按个人消费排除',
    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 inventory_transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT COMMENT '商品ID',
    sku_id BIGINT COMMENT 'SKU ID',
    type VARCHAR(32) COMMENT '交易类型: INBOUND/OUTBOUND/ADJUSTMENT/TRANSFER/COUNT',
    direction TINYINT COMMENT '方向: 1=入库, -1=出库',
    quantity INT NOT NULL COMMENT '数量',
    before_stock INT COMMENT '操作前库存',
    after_stock INT COMMENT '操作后库存',
    reference_type VARCHAR(32) COMMENT '关联业务类型',
    reference_id BIGINT COMMENT '关联业务ID',
    operator_id BIGINT COMMENT '操作人ID',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存交易流水表';

-- 库存盘点记录表
CREATE TABLE IF NOT EXISTS inventory_counting_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT COMMENT '商品ID',
    sku_id BIGINT COMMENT 'SKU ID',
    expected_stock INT COMMENT '账面库存',
    actual_stock INT COMMENT '实际库存',
    difference INT COMMENT '差异',
    status VARCHAR(16) DEFAULT 'pending' COMMENT '状态',
    operator_id BIGINT COMMENT '盘点人ID',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存盘点记录表';

-- 库存入库单表
CREATE TABLE IF NOT EXISTS inventory_inbound_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL COMMENT '入库单号',
    supplier_id BIGINT COMMENT '供应商ID',
    supplier_name VARCHAR(100) COMMENT '供应商名称',
    status VARCHAR(16) DEFAULT 'pending' COMMENT '状态',
    total_items INT DEFAULT 0 COMMENT '商品种类数',
    total_amount INT DEFAULT 0 COMMENT '入库总金额(分)',
    inbound_date DATE COMMENT '入库日期',
    operator_id BIGINT COMMENT '操作人ID',
    remark TEXT 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 inventory_inbound_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    inbound_order_id BIGINT NOT NULL COMMENT '入库单ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    sku_id BIGINT COMMENT 'SKU ID',
    quantity INT NOT NULL COMMENT '入库数量',
    purchase_price DECIMAL(10,2) DEFAULT 0.00 COMMENT '入库单价(分)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存入库明细表';

-- 商品规格SKU表（product_skus）
CREATE TABLE IF NOT EXISTS product_skus (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    product_id BIGINT NOT NULL COMMENT '所属商品ID',
    sku_code VARCHAR(64) COMMENT 'SKU编码',
    specs JSON COMMENT '规格选项JSON: [{"name":"颜色","value":"红色"},{"name":"尺码","value":"M"}]',
    price INT DEFAULT 0 COMMENT 'SKU价格(分)',
    purchase_price DECIMAL(10,2) DEFAULT 0.00 COMMENT '进价(分)',
    stock INT DEFAULT 0 COMMENT '库存数量',
    min_stock_alert INT DEFAULT 0 COMMENT '库存预警阈值(低于此值预警)',
    min_quantity INT DEFAULT 1 COMMENT '最小购买数量限制',
    original_price INT DEFAULT 0 COMMENT '原价(分)',
    image VARCHAR(500) COMMENT 'SKU图片',
    enabled TINYINT DEFAULT 1 COMMENT '是否启用: 0=禁用 1=启用',
    linked_product_id BIGINT COMMENT '关联商品ID，null=Legacy SKU',
    label VARCHAR(100) COMMENT '自定义显示标签，null=使用linked_product.name',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_product_id (product_id),
    INDEX idx_sku_code (sku_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格SKU表';

-- 商品订单表
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',
    pickup_location_id BIGINT COMMENT '自取地点ID',
    pickup_location_snapshot TEXT COMMENT '自取地点快照(JSON)',
    time_slot_id BIGINT COMMENT '所选时间段ID',
    time_slot_snapshot TEXT COMMENT '时间段快照(JSON)',
    service_person_id BIGINT COMMENT '所选服务者ID(deliveryMethod=4)',
    service_person_snapshot TEXT COMMENT '服务者快照(JSON)',
    fulfill_status VARCHAR(32) DEFAULT 'pending' COMMENT '履约状态: pending/confirmed_by_vendor/received_by_user/completed',
    fulfill_confirmed_at DATETIME COMMENT '供应商确认时间',
    received_at DATETIME COMMENT '用户确认收货时间',
purchase_info TEXT COMMENT '购买信息JSON: {id_card, hand_signature, ...}',
provision_status VARCHAR(32) COMMENT '活动provision状态: unprovisioned(待审核)/provisioned(已发货)/signed_in(已签到)/completed(已完成)/cancelled(审核拒绝)',
auditor_id BIGINT COMMENT '审核人ID',
audited_at DATETIME COMMENT '审核时间',
audit_reason TEXT COMMENT '审核拒绝原因',
    shipping_cost INT DEFAULT 0 COMMENT '快递费(分)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_buyer_id (buyer_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 product_pickup_locations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    product_id BIGINT NOT NULL COMMENT '所属商品ID',
    name VARCHAR(100) NOT NULL COMMENT '地点名称',
    address VARCHAR(500) NOT NULL COMMENT '详细地址',
    longitude DECIMAL(10,7) COMMENT '经度',
    latitude DECIMAL(10,7) COMMENT '纬度',
    contact_name VARCHAR(50) COMMENT '联系人',
    contact_phone VARCHAR(20) COMMENT '联系电话',
    sort_order INT DEFAULT 0 COMMENT '排序',
    enabled TINYINT DEFAULT 1 COMMENT '启用: 0/1',
    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 product_pickup_time_slots (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    location_id BIGINT NOT NULL COMMENT '所属地点ID',
    start_time TIME NOT NULL COMMENT '开始时间 HH:MM',
    end_time TIME NOT NULL COMMENT '结束时间 HH:MM',
    weekday TINYINT COMMENT '周几(1-7), null=按具体日期',
    specific_date DATE COMMENT '具体日期, null=按周循环',
    max_capacity INT DEFAULT 1 COMMENT '该时段最大接待人数',
    booked_count INT DEFAULT 0 COMMENT '已预订数',
    enabled TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_location_id (location_id),
    INDEX idx_specific_date (specific_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自取时间段模板';

-- 商品线上服务者表
CREATE TABLE IF NOT EXISTS product_service_persons (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    product_id BIGINT NOT NULL COMMENT '所属商品ID',
    user_id BIGINT NOT NULL COMMENT '服务者用户ID(关联users表)',
    name VARCHAR(50) NOT NULL COMMENT '展示名称',
    title VARCHAR(100) COMMENT '头衔(如高级营养师)',
    intro TEXT COMMENT '个人介绍',
    avatar VARCHAR(500) COMMENT '头像URL',
    price INT NOT NULL COMMENT '该人专属价格(分)',
    original_price INT COMMENT '原价(分)',
    sort_order INT DEFAULT 0,
    enabled TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_id (product_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品线上服务者';

-- 服务者可预约时间段表
CREATE TABLE IF NOT EXISTS product_service_person_slots (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    person_id BIGINT NOT NULL COMMENT '所属服务者ID',
    start_time DATETIME NOT NULL COMMENT '开始时间',
    end_time DATETIME NOT NULL COMMENT '结束时间',
    max_capacity INT DEFAULT 1 COMMENT '容量',
    booked_count INT DEFAULT 0 COMMENT '已预订数',
    enabled TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_person_id (person_id),
    INDEX idx_start_time (start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务者可预约时间段';

-- 单人线上服务时间段表
CREATE TABLE IF NOT EXISTS product_online_slots (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    product_id BIGINT NOT NULL COMMENT '所属商品ID',
    start_time DATETIME NOT NULL COMMENT '开始时间',
    end_time DATETIME NOT NULL COMMENT '结束时间',
    max_capacity INT DEFAULT 1 COMMENT '容量',
    booked_count INT DEFAULT 0 COMMENT '已预订数',
    enabled TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_id (product_id),
    INDEX idx_start_time (start_time)
) 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 UNIQUE COMMENT '标题（唯一，防止重复发布）',
    summary TEXT 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 数组',
    dimension_weights JSON DEFAULT NULL COMMENT '五维权重百分比: {"body":0,"mind":0,"wisdom":0,"action":0,"wealth":0}',
    visibility VARCHAR(20) DEFAULT 'public' COMMENT '浏览权限：public/login/private',
    visible_to TEXT COMMENT '私密指定人群 JSON',
    status VARCHAR(20) DEFAULT 'draft' COMMENT '状态：draft/published/archived',
    growth_category VARCHAR(50) DEFAULT NULL COMMENT '成长分类',
    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)',
    type VARCHAR(20) DEFAULT 'platform_points' COMMENT '提现来源: platform_points(CF值) / commission(历史佣金)',
    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 '家庭会员到期时间',
    approve_remark VARCHAR(500) DEFAULT NULL COMMENT '审批备注',
    approve_status VARCHAR(20) DEFAULT 'approved' COMMENT '审批状态: approved/rejected',
    approve_user_id BIGINT DEFAULT NULL COMMENT '审批人ID',
    approve_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 '更新时间',
    UNIQUE KEY uk_order_no (order_no)
) 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 NULL COMMENT '孩子ID，家长本人打卡时为NULL',
checkin_type VARCHAR(20) NOT NULL COMMENT '打卡类型: daily_plan/saving/expense/review',
    checkin_date DATE NOT NULL COMMENT '记账日期',
    type VARCHAR(10) DEFAULT 'expense' COMMENT '类型: income(收入)/expense(支出)',
    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 '备注',
    proxy_uploader_id BIGINT COMMENT '代上传人ID（规划师/营养师）',
    proxy_upload_note TEXT COMMENT '代上传备注',
    ai_analysis TEXT COMMENT 'AI解读结果JSON',
    shannon_index VARCHAR(32) DEFAULT NULL COMMENT '香农指数',
    payload_json LONGTEXT 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 '显示排序',
    collection_date DATETIME 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',
    remark VARCHAR(500) DEFAULT NULL 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_status_expire (status, expire_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康报告上传草稿表(两阶段入库中间态)';

-- 健康报告修改留痕表
CREATE TABLE IF NOT EXISTS health_report_edit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT NOT NULL,
    user_id BIGINT,
    report_type VARCHAR(32),
    action VARCHAR(20) COMMENT 'create/edit',
    before_payload LONGTEXT,
    after_payload LONGTEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_report_id (report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康报告修改留痕';

-- 菌群检测专项数据表（针对 gut_flora 类型报告）
CREATE TABLE IF NOT EXISTS health_gut_flora (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键 ID',
    report_id BIGINT NOT NULL COMMENT '关联报告 ID',
    bacteria_name VARCHAR(100) NOT NULL COMMENT '菌种名称',
    bacteria_value VARCHAR(50) COMMENT '菌种丰度值',
    normal_range VARCHAR(100) COMMENT '正常范围',
    status VARCHAR(50) COMMENT '状态：正常/偏低/偏高',
    category VARCHAR(50) COMMENT '分类：core/probiotic/harmful/other/pathogen_genus/pathogen_detection/taxonomy',
    sort_order INT DEFAULT 0 COMMENT '排序',
    population_level VARCHAR(100) DEFAULT NULL COMMENT '处于人群%水平',
    detection_rate VARCHAR(100) DEFAULT NULL COMMENT '%正常人有检出',
    description TEXT COMMENT '菌种说明',
    `level` VARCHAR(20) DEFAULT 'GENUS' COMMENT '级别：CLASS/ORDER/FAMILY/GENUS/SPECIES',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_report (report_id)
) 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 COMMENT '孩子ID(手动输入报名人为空)',
    user_id BIGINT NOT NULL COMMENT '报名人用户ID(家长)',
    registrant_name VARCHAR(100) COMMENT '报名人姓名',
    registrant_phone VARCHAR(20) COMMENT '报名人联系方式',
    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',
    dimension_weights JSON DEFAULT NULL COMMENT '五维权重百分比: {"body":0,"mind":0,"wisdom":0,"action":0,"wealth":0}',
    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 '活动价格(分)',
    member_price INT COMMENT '会员价(分)',
    vendor_id        BIGINT COMMENT '服务商ID',
    vendor_name      VARCHAR(100) COMMENT '服务商名称',
    feedback_dimensions TEXT COMMENT '评价维度列表JSON',
    family_scope TINYINT DEFAULT 1 COMMENT '是否面向家庭: 1=家庭成员参与过即排除, 0=仅按个人参与排除',
    platform_points INT DEFAULT 0 COMMENT '活动奖励CF值(0=不发)',
    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.x: 活动报名升级（报名截止时间+孩子/家长双收费+会员区分+创建人）
ALTER TABLE activities
  ADD COLUMN registration_deadline DATETIME DEFAULT NULL
    COMMENT '报名截止时间(NULL=不限制, 默认=活动开始时间)',
  ADD COLUMN child_fee INT DEFAULT 0
    COMMENT '孩子·非会员价(分)',
  ADD COLUMN child_member_fee INT DEFAULT 0
    COMMENT '孩子·会员价(分), 0=免费',
  ADD COLUMN parent_fee INT DEFAULT 0
    COMMENT '家长·非会员价(分)',
  ADD COLUMN parent_member_fee INT DEFAULT 0
    COMMENT '家长·会员价(分), 0=免费',
  ADD COLUMN creator_id BIGINT DEFAULT NULL
    COMMENT '创建人userId(普通用户发布者, admin创建可为空)';

-- ===== 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 '出生日期',
    generation INT COMMENT '辈分值（0=家庭创建者，+n=向上，-n=向下）',
    is_spouse TINYINT(1) DEFAULT 0 COMMENT '是否配偶（同辈中）',
    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 'CF值(干预/平台奖励)',
    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 '专注次数重置日期',
    -- 饮食模块扩展字段
    is_living_together TINYINT(1) DEFAULT 1 COMMENT '是否同住（0=不同住，不出现在食谱推荐中）',
    meal_with_breakfast_weekday TINYINT(1) DEFAULT 0 COMMENT '工作日早餐同餐',
    meal_with_lunch_weekday TINYINT(1) DEFAULT 0 COMMENT '工作日午餐同餐',
    meal_with_dinner_weekday TINYINT(1) DEFAULT 0 COMMENT '工作日晚餐同餐',
    meal_with_breakfast_weekend TINYINT(1) DEFAULT 0 COMMENT '周末早餐同餐',
    meal_with_lunch_weekend TINYINT(1) DEFAULT 0 COMMENT '周末午餐同餐',
    meal_with_dinner_weekend TINYINT(1) 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='家庭成员表（手动添加）';

-- 家庭成员双向关系表（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）',
    trust_score        DECIMAL(5,2) DEFAULT 0 COMMENT '信任度(答卷人视角)',
    intimacy_score     DECIMAL(5,2) DEFAULT 0 COMMENT '亲密度(答卷人视角)',
    communication_score DECIMAL(5,2) DEFAULT 0 COMMENT '沟通质量(答卷人视角)',
    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='家庭成员双向关系表';

-- 【已废弃】relationship_types 表已删除（generation 辈分模型替代）

-- 用户收货地址表
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/AI_GENERATED',
    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,
    dimension VARCHAR(20) NULL COMMENT '五维维度(body/mind/wisdom/action/wealth)',
    source_conversation_id VARCHAR(100) NULL COMMENT '来源AI会话ID',
    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,
    discount_rate INT COMMENT '折扣券折扣率(千分比,9000=9折)',
    product_id BIGINT COMMENT '绑定商品ID,NULL=全场通用',
    points_price INT DEFAULT 0 COMMENT '积分兑换价,0=不可积分兑换',
    grant_type VARCHAR(16) COMMENT '发放规则:JOIN/PERIODIC/POPULATION,NULL=不自动发放',
    grant_level_code VARCHAR(32) COMMENT '发放规则对应会员等级',
    grant_period VARCHAR(16) COMMENT 'PERIODIC周期:MONTHLY/QUARTERLY',
    grant_quantity INT DEFAULT 1 COMMENT '每次发放数量',
    status VARCHAR(16) DEFAULT 'ACTIVE' COMMENT '状态:ACTIVE=启用/DISABLED=停用',
    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 '收货地址',
    province VARCHAR(50) DEFAULT NULL COMMENT '省份',
    city VARCHAR(50) DEFAULT NULL COMMENT '城市',
    district VARCHAR(50) DEFAULT NULL COMMENT '区县',
    street VARCHAR(100) DEFAULT NULL 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 butler_profiles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    tier VARCHAR(10),
    status VARCHAR(20) DEFAULT 'pending',
    member_count INT DEFAULT 0,
    max_members INT DEFAULT 50,
    annual_fee_l1 BIGINT,
    annual_fee_l2 BIGINT,
    description TEXT,
    certificates TEXT,
    reject_reason VARCHAR(500),
    commission_rate_l1 INT DEFAULT 1000,
    commission_rate_l2 INT DEFAULT 5000,
    accepting TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否接受新家庭绑定: 0=停接/1=接单',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    approved_at DATETIME,
    UNIQUE KEY uk_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_tier (tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管家服务提供商档案';

-- 管家服务记录
CREATE TABLE IF NOT EXISTS butler_service_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    butler_id BIGINT NOT NULL,
    member_user_id BIGINT NOT NULL,
    subscription_id BIGINT,
    service_type VARCHAR(20) NOT NULL,
    amount_p BIGINT NOT NULL,
    platform_fee_p BIGINT DEFAULT 0,
    butler_amount_p BIGINT DEFAULT 0,
    member_tier VARCHAR(10),
    `year_month` VARCHAR(7),
    remark VARCHAR(500),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_butler_id (butler_id),
    INDEX idx_member_user_id (member_user_id),
    INDEX idx_year_month (`year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管家服务记录';

-- 管家月度佣金结算单
CREATE TABLE IF NOT EXISTS butler_commission_settlements (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    butler_id BIGINT NOT NULL,
    `year_month` VARCHAR(7) NOT NULL,
    member_count INT DEFAULT 0,
    annual_base_amount_p BIGINT DEFAULT 0,
    annual_butler_amount_p BIGINT DEFAULT 0,
    annual_platform_amount_p BIGINT DEFAULT 0,
    per_session_total_p BIGINT DEFAULT 0,
    per_session_platform_total_p BIGINT DEFAULT 0,
    per_session_butler_total_p BIGINT DEFAULT 0,
    total_butler_amount_p BIGINT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    settled_at DATETIME,
    settled_by BIGINT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_butler_month (butler_id, `year_month`),
    INDEX idx_status (status),
    INDEX idx_year_month (`year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管家月度佣金结算单';

-- 会员-管家分配记录
CREATE TABLE IF NOT EXISTS butler_assignments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭 ID',
    butler_user_id BIGINT NOT NULL COMMENT '管家用户 ID',
    subscription_id BIGINT COMMENT '关联订阅 ID',
    level VARCHAR(10) NOT NULL COMMENT '订阅级别 L1/L2',
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_family_assignment (family_id),
    INDEX idx_butler_user_id (butler_user_id),
    INDEX idx_subscription_id (subscription_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员 - 管家分配记录';

-- 家庭订阅记录
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 growth_archive_products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '关联products表',
    status TINYINT(1) DEFAULT 1 COMMENT '1=启用 0=停用',
    sort_order INT DEFAULT 0 COMMENT '展示排序(升序)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_product_id (product_id),
    KEY idx_status (status)
) 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='方案生成规则';

-- 家庭访问安全日志表
CREATE TABLE IF NOT EXISTS family_access_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '访问方用户ID',
    family_id BIGINT NOT NULL COMMENT '被访问的家庭ID',
    request_uri VARCHAR(500) NOT NULL COMMENT '请求路径',
    request_method VARCHAR(10) NOT NULL,
    access_type VARCHAR(20) NOT NULL COMMENT 'check_type: query/path/body',
    result VARCHAR(10) NOT NULL COMMENT 'allowed/denied/log_only',
    client_ip VARCHAR(50),
    user_agent VARCHAR(500),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, created_at),
    INDEX idx_family_time (family_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭访问安全日志';

-- 五维能量分数历史记录表
CREATE TABLE IF NOT EXISTS five_dimension_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '成员ID(user或family_member)',
    member_type VARCHAR(20) NOT NULL COMMENT '成员类型: user/member',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    dimension_code VARCHAR(20) NOT NULL COMMENT '维度编码: body/mind/wisdom/action/wealth',
    score INT NOT NULL COMMENT '分数(0-100)',
    assessed_at DATETIME NOT NULL COMMENT '评估时间',
    source_type VARCHAR(50) COMMENT '来源类型: periodic/task/assessment/manual',
    source_id BIGINT COMMENT '来源记录ID',
    detail_json TEXT COMMENT '详细数据JSON',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_member_dim (member_id, member_type, dimension_code),
    INDEX idx_family_time (family_id, assessed_at),
    INDEX idx_source (source_type, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='五维能量分数历史记录';

-- 家庭邀请令牌表
CREATE TABLE IF NOT EXISTS family_invitations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    token VARCHAR(64) NOT NULL UNIQUE COMMENT '邀请令牌(UUID)',
    created_by BIGINT NOT NULL COMMENT '创建人用户ID',
    status VARCHAR(16) DEFAULT 'active' COMMENT '状态: active/used/expired/revoked',
    expires_at DATETIME COMMENT '过期时间',
    max_use_count INT DEFAULT 1 COMMENT '最大使用次数',
    used_count 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_token (token),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭邀请令牌';

-- 规划师绑定家庭申请表
CREATE TABLE IF NOT EXISTS teacher_family_binding_requests (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    teacher_id BIGINT NOT NULL COMMENT '规划师用户ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    invitation_token VARCHAR(128) COMMENT '关联邀请码/令牌',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/approved/rejected/cancelled',
    requested_at DATETIME COMMENT '申请时间',
    decided_by BIGINT COMMENT '审批人用户ID',
    decided_at DATETIME COMMENT '审批时间',
    reject_reason VARCHAR(200) COMMENT '拒绝原因',
    message VARCHAR(500) COMMENT '申请留言',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_teacher_family (teacher_id, family_id),
    INDEX idx_family_status (family_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规划师绑定家庭申请表';

-- Dify会话本地镜像表
CREATE TABLE IF NOT EXISTS chat_conversations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    dify_conversation_id VARCHAR(64),
    assistant_type VARCHAR(32) COMMENT 'family/nutrition/tongue',
    title VARCHAR(200),
    last_message_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_dify_conv (dify_conversation_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Dify会话本地镜像';

CREATE TABLE IF NOT EXISTS chat_messages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT NOT NULL,
    dify_message_id VARCHAR(64),
    role VARCHAR(20) NOT NULL COMMENT 'user/assistant',
    content TEXT NOT NULL,
    inputs_json TEXT,
    metadata TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_conv (conversation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Dify消息本地镜像';

-- 迁移版本追踪表
CREATE TABLE IF NOT EXISTS schema_versions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    version_code VARCHAR(64) NOT NULL UNIQUE,
    description VARCHAR(500),
    checksum VARCHAR(64),
    applied_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    applied_by VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='迁移版本追踪表';


-- 待退款记录表
CREATE TABLE IF NOT EXISTS pending_refund (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    withdrawal_request_id BIGINT COMMENT '提现申请ID',
    amount INT NOT NULL COMMENT '退款金额(分)',
    attempt_count INT DEFAULT 0 COMMENT '当前尝试次数',
    max_attempts INT DEFAULT 10 COMMENT '最大重试次数',
    next_retry_time DATETIME COMMENT '下次重试时间',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/processing/success/failed',
    failure_reason VARCHAR(500) COMMENT '失败原因',
    refunded_amount INT COMMENT '实际退款金额(分)',
    refunded_at DATETIME COMMENT '退款时间',
    remark VARCHAR(500) 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_status (status),
    INDEX idx_next_retry (next_retry_time),
    INDEX idx_withdrawal_request (withdrawal_request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='待退款记录表';


-- 家庭每日运势表（迁移 79）
CREATE TABLE IF NOT EXISTS tianpan_daily_fortune (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    date VARCHAR(20) NOT NULL COMMENT '日期 YYYY-MM-DD',
    lunar_date VARCHAR(50) COMMENT '农历日期',
    day_ganzhi VARCHAR(10) COMMENT '日柱',
    five_elements_main VARCHAR(20) COMMENT '当日五行主气',
    five_elements_detail VARCHAR(100) COMMENT '五行详情',
    auspicious TEXT COMMENT '宜做的事(JSON数组)',
    inauspicious TEXT COMMENT '忌做的事(JSON数组)',
    family_advice TEXT COMMENT '家庭建议',
    focus_member_name VARCHAR(50) COMMENT '关注成员',
    focus_member_tip TEXT COMMENT '成员提示',
    wuxing_tip TEXT COMMENT '五行提示',
    lucky_direction VARCHAR(20) COMMENT '幸运方向',
    fortune_level VARCHAR(20) COMMENT '运势等级',
    fortune_level_text VARCHAR(100) COMMENT '运势等级描述',
    fortune_description TEXT COMMENT '运势描述',
    mood_score INT DEFAULT 50 COMMENT '心情分数',
    mood_emoji VARCHAR(10) COMMENT '心情emoji',
    mood_text VARCHAR(50) COMMENT '心情文本',
    weekly_tip TEXT COMMENT '每周提示',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_family_date (family_id, date),
    INDEX idx_family_id (family_id),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭每日运势表';


-- 成员年度能量表（迁移 80）
CREATE TABLE IF NOT EXISTS tianpan_member_annual_energy (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '成员ID',
    year INT NOT NULL COMMENT '年份',
    dimensions_body INT DEFAULT 0 COMMENT '身能量',
    dimensions_mind INT DEFAULT 0 COMMENT '心能量',
    dimensions_wisdom INT DEFAULT 0 COMMENT '智能量',
    dimensions_action INT DEFAULT 0 COMMENT '行能量',
    dimensions_wealth INT DEFAULT 0 COMMENT '富能量',
    jupiter_phase VARCHAR(50) COMMENT '木星相位',
    saturn_phase VARCHAR(50) COMMENT '土星相位',
    highlights TEXT COMMENT '年度亮点(JSON数组)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_member_year (member_id, year),
    INDEX idx_member_id (member_id),
    INDEX idx_year (year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成员年度能量表';

-- 维度权重表（管理端保存五维权重使用）
CREATE TABLE IF NOT EXISTS dimension_weights (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    target_type VARCHAR(20) NOT NULL COMMENT '目标类型: activity/product/article',
    target_id BIGINT NOT NULL COMMENT '目标ID',
    dimension VARCHAR(20) NOT NULL COMMENT '维度: body/mind/wisdom/action/wealth',
    weight INT DEFAULT 0 COMMENT '权重百分比',
    enabled TINYINT(1) DEFAULT 0 COMMENT '是否启用',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='维度权重表';

-- 健康打卡表
CREATE TABLE IF NOT EXISTS health_checkins (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户ID（家长）',
    child_id BIGINT COMMENT '孩子ID',
    checkin_date DATE COMMENT '打卡日期',
    behaviors TEXT COMMENT '打卡行为JSON数组',
    note VARCHAR(500) COMMENT '备注',
    mood VARCHAR(20) COMMENT '心情: happy/normal/sad/angry/sick',
    earned_energy INT DEFAULT 5 COMMENT '获得能量值',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_id (child_id),
    INDEX idx_checkin_date (checkin_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康打卡表';

-- 每日健康打卡表（综合打卡：饮食/运动/睡眠/饮水/心情评分）
CREATE TABLE IF NOT EXISTS daily_checkin (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_date DATE COMMENT '打卡日期',
    diet_score INT COMMENT '饮食评分',
    exercise_score INT COMMENT '运动评分',
    sleep_score INT COMMENT '睡眠评分',
    water_intake INT COMMENT '饮水量(ml)',
    mood_score INT COMMENT '心情评分',
    weight_kg DECIMAL(5,2) COMMENT '体重(kg)',
    height_cm DECIMAL(5,2) COMMENT '身高(cm)',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每日健康打卡表';

-- 健康打卡 - 睡眠记录
CREATE TABLE IF NOT EXISTS sleep_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_id BIGINT COMMENT '关联打卡ID',
    sleep_time TIME COMMENT '入睡时间',
    wake_time TIME COMMENT '醒来时间',
    duration_minutes INT COMMENT '总睡眠时长(分钟)',
    deep_sleep_minutes INT COMMENT '深睡时长(分钟)',
    light_sleep_minutes INT COMMENT '浅睡时长(分钟)',
    rem_minutes INT COMMENT 'REM时长(分钟)',
    quality_score INT COMMENT '睡眠质量评分1-10',
    dream_count INT COMMENT '做梦次数',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='睡眠打卡记录';

-- 健康打卡 - 饮水记录
CREATE TABLE IF NOT EXISTS water_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_id BIGINT COMMENT '关联打卡ID',
    amount_ml INT COMMENT '饮水量(毫升)',
    water_type VARCHAR(32) COMMENT '类型: plain/sparkling/tea/other',
    drunk_at DATETIME COMMENT '饮水时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮水打卡记录';

-- 健康打卡 - 饮食记录
CREATE TABLE IF NOT EXISTS health_meal_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_id BIGINT COMMENT '关联打卡ID',
    meal_type VARCHAR(32) COMMENT '餐次: breakfast/lunch/dinner/snack',
    food_items TEXT COMMENT '食物清单JSON',
    total_calories INT COMMENT '总卡路里',
    protein_g INT COMMENT '蛋白质克数',
    fat_g INT COMMENT '脂肪克数',
    carbs_g INT COMMENT '碳水克数',
    fiber_g INT COMMENT '膳食纤维克数',
    meal_time DATETIME COMMENT '用餐时间',
    remark VARCHAR(500) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮食打卡记录';

-- 健康打卡 - 冥想记录
CREATE TABLE IF NOT EXISTS health_meditation_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_id BIGINT COMMENT '关联打卡ID',
    duration_minutes INT COMMENT '时长(分钟)',
    type VARCHAR(32) COMMENT '类型: mindfulness/guided/breathing/body_scan',
    mood VARCHAR(32) COMMENT '冥想后心情',
    remark VARCHAR(500) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='冥想打卡记录';

-- 健康打卡 - 运动记录
CREATE TABLE IF NOT EXISTS exercise_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_id BIGINT COMMENT '关联打卡ID',
    exercise_type VARCHAR(50) COMMENT '运动类型',
    duration_minutes INT COMMENT '时长(分钟)',
    intensity VARCHAR(20) COMMENT '强度: low/medium/high',
    calories_burned INT COMMENT '消耗卡路里',
    heart_rate_avg INT COMMENT '平均心率',
    heart_rate_max INT COMMENT '最大心率',
    start_time DATETIME COMMENT '开始时间',
    end_time DATETIME COMMENT '结束时间',
    remark TEXT COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运动打卡记录';

-- 电商供应商
CREATE TABLE IF NOT EXISTS ecom_supplier (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '供应商名称',
    contact_name VARCHAR(50) COMMENT '联系人',
    contact_phone VARCHAR(20) COMMENT '联系电话',
    status TINYINT DEFAULT 1 COMMENT '启用状态 1启用 0禁用',
    remark VARCHAR(500) 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 rebate_commission_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '配置名称',
    rate INT COMMENT '佣金比例(万分比)',
    rate_type TINYINT DEFAULT 1 COMMENT '比例类型 1百分比 2固定金额',
    min_amount INT COMMENT '最低金额(分)',
    max_amount INT COMMENT '最高金额(分)',
    remark VARCHAR(500) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='佣金返利配置';

-- DAN测评服务闭环表

CREATE TABLE IF NOT EXISTS `dan_assessment_executions` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `assessment_execution_id` BIGINT NOT NULL COMMENT '执行记录ID',
    `assessment_order_id` BIGINT NOT NULL COMMENT '订单ID',
    `child_id` BIGINT NOT NULL COMMENT '孩子ID',
    `family_id` BIGINT NOT NULL COMMENT '家庭ID',
    `quota_id` BIGINT COMMENT '额度ID',
    `appointment_id` BIGINT COMMENT '关联assessment_appointments.id',
    `assessor_id` BIGINT NOT NULL COMMENT '测评师userId',
    `assessor_status` ENUM('pending','completed','rejected') DEFAULT 'pending' COMMENT '测评师状态',
    `assessor_completed_at` DATETIME COMMENT '测评师完成时间',
    `assessor_notes` VARCHAR(200) COMMENT '测评摘要/服务备注',
    `planner_id` BIGINT COMMENT '规划师userId',
    `planner_status` ENUM('pending','completed','rejected') DEFAULT 'pending' COMMENT '规划师状态',
    `planner_completed_at` DATETIME COMMENT '规划师完成时间',
    `planner_notes` VARCHAR(200) COMMENT '规划服务备注',
    `user_confirmed` TINYINT(1) DEFAULT 0 COMMENT '用户确认：0未确认,1已确认',
    `user_confirmed_at` DATETIME COMMENT '用户确认时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_assessment_execution_id` (`assessment_execution_id`),
    KEY `idx_assessment_order_id` (`assessment_order_id`),
    KEY `idx_assessor_id` (`assessor_id`),
    KEY `idx_planner_id` (`planner_id`),
    KEY `idx_child_id` (`child_id`),
    KEY `idx_family_id` (`family_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DAN测评服务执行记录';

CREATE TABLE IF NOT EXISTS `service_ratings` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `execution_id` BIGINT NOT NULL COMMENT '执行记录ID',
    `assessment_order_id` BIGINT NOT NULL COMMENT '订单ID',
    `user_id` BIGINT NOT NULL COMMENT '评分用户ID',
    `assessor_id` BIGINT NOT NULL COMMENT '测评师userId',
    `assessor_rating` TINYINT DEFAULT 0 COMMENT '测评师评分 1-5',
    `assessor_comment` VARCHAR(500) COMMENT '评价内容',
    `planner_id` BIGINT COMMENT '规划师userId',
    `planner_rating` TINYINT COMMENT '规划师评分 1-5',
    `planner_comment` VARCHAR(500) COMMENT '评价内容',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_execution_user` (`execution_id`, `user_id`),
    KEY `idx_assessor_id` (`assessor_id`),
    KEY `idx_planner_id` (`planner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务评分记录';

CREATE TABLE IF NOT EXISTS `service_settlements` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `execution_id` BIGINT NOT NULL COMMENT '执行记录ID',
    `assessment_order_id` BIGINT NOT NULL COMMENT '订单ID',
    `provider_id` BIGINT NOT NULL COMMENT '服务提供方userId',
    `provider_type` ENUM('assessor','planner') NOT NULL COMMENT '服务方类型',
    `service_fee` BIGINT DEFAULT 0 COMMENT '服务费用(分)',
    `commission_rate` INT COMMENT '佣金比例(bps)',
    `commission_amount` BIGINT DEFAULT 0 COMMENT '结算金额(分)',
    `status` ENUM('pending','settled','failed') DEFAULT 'pending' COMMENT '结算状态',
    `settled_at` DATETIME COMMENT '结算时间',
    `transaction_id` VARCHAR(100) COMMENT '转账交易ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_execution_id` (`execution_id`),
    KEY `idx_provider_id` (`provider_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务结算记录';

-- 健康知识库 — 菌属/营养素/指标说明
CREATE TABLE IF NOT EXISTS `health_knowledge_base` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `item_type` VARCHAR(50) NOT NULL COMMENT '类型: bacteria/nutrient/indicator/vitamin/amino_acid/disease',
    `item_name` VARCHAR(200) NOT NULL COMMENT '项目名称（精确匹配）',
    `category` VARCHAR(100) COMMENT '分类（如 有益菌/有害菌）',
    `normal_range` VARCHAR(200) COMMENT '正常范围参考',
    `description` TEXT COMMENT '详细说明',
    `suggestion` TEXT COMMENT '相关建议',
    `source` VARCHAR(100) COMMENT '数据来源',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_item_type_name` (`item_type`, `item_name`),
    KEY `idx_item_type` (`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康知识库 - 菌属/营养素/指标说明';

-- 统一知识库 — 按模块存储JSON（12个顶层模块，DB为查询源，classpath种子兜底）
CREATE TABLE IF NOT EXISTS `knowledge_base_section` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `module_key` VARCHAR(100) NOT NULL COMMENT '模块键（如 食物营养库/菌属功能库/报告指标分级标准）',
    `module_name` VARCHAR(200) COMMENT '模块中文名',
    `content` LONGTEXT COMMENT '模块内容（JSON序列化）',
    `version` VARCHAR(50) COMMENT '数据版本（来自元数据.版本）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_module_key` (`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='统一知识库 - 按模块存储JSON';
-- =============================================
-- DAN报告上传独立化（拆分 PDF 报告生命周期与数值结果）
-- =============================================

-- DAN PDF 报告上传/解析/确认记录表
CREATE TABLE IF NOT EXISTS dan_report_uploads (
    id                   BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id            BIGINT       NOT NULL COMMENT '家庭ID',
    family_member_id     BIGINT       NOT NULL COMMENT '家庭成员ID',
    uploader_user_id     BIGINT       NOT NULL COMMENT '上传者用户ID',
    dimension            VARCHAR(16)  NOT NULL COMMENT '维度: mind(心-A2)/wisdom(智-B4)',
    report_type          VARCHAR(16)  COMMENT 'A2/B4 报告类型（解析后确认）',
    file_url             VARCHAR(500) NOT NULL COMMENT 'PDF 文件URL',
    file_id              VARCHAR(500) COMMENT '媒资文件ID或相对路径',
    file_name            VARCHAR(200) COMMENT '原始文件名',
    file_size            BIGINT       COMMENT '文件大小(字节)',
    uploaded_at          DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
    parsed_items         JSON         COMMENT '解析的指标项 [{code, name, value, category}]',
    parsed_extra         JSON         COMMENT '解析的额外信息(姓名/性别/年龄/报告日期等)',
    parsed_summary       TEXT         COMMENT '解析的总结文本',
    parsed_suggestions   TEXT         COMMENT '解析的建议文本',
    parser_version       VARCHAR(32)  COMMENT '解析器版本',
    parse_status         VARCHAR(16)  DEFAULT 'pending' COMMENT 'pending/success/failed',
    parse_error          VARCHAR(500) COMMENT '解析失败原因',
    parsed_at            DATETIME     COMMENT '解析完成时间',
    draft_status         VARCHAR(16)  DEFAULT 'pending' COMMENT 'pending/confirmed/discarded',
    confirmed_at         DATETIME     COMMENT '确认时间',
    confirmed_by         BIGINT       COMMENT '确认操作人用户ID',
    edited_items         JSON         COMMENT '编辑后的指标项（覆盖 parsed_items）',
    edited_extra         JSON         COMMENT '编辑后的额外信息',
    edited_summary       TEXT         COMMENT '编辑后的总结',
    edited_suggestions   TEXT         COMMENT '编辑后的建议',
    edit_count           INT          DEFAULT 0 COMMENT '编辑次数',
    linked_result_id     BIGINT       COMMENT '关联 dan_assessment_results.id',
    created_at           DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at           DATETIME     COMMENT '软删除时间',
    INDEX idx_family_id (family_id),
    INDEX idx_member_id (family_member_id),
    INDEX idx_draft_status (draft_status),
    INDEX idx_dimension (dimension),
    INDEX idx_parse_status (parse_status),
    INDEX idx_linked_result (linked_result_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DAN测评PDF报告上传/解析/确认记录';

CREATE TABLE IF NOT EXISTS review_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    reviewer_id BIGINT NOT NULL COMMENT '评价人ID',
    rating INT COMMENT '总体评分',
    review_text TEXT COMMENT '评价内容',
    review_at DATETIME COMMENT '评价时间',
    organization_rating INT COMMENT '活动组织评分(0-5)',
    content_rating INT COMMENT '活动内容评分(0-5)',
    experience_rating INT COMMENT '活动体验评分(0-5)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_order_id (order_id),
    INDEX idx_reviewer_id (reviewer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动订单评价表';

-- 微行动定义表
CREATE TABLE IF NOT EXISTS micro_actions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(32) NOT NULL COMMENT '行动编码: DRINK_WATER, DEEP_BREATH, ...',
    name VARCHAR(50) NOT NULL COMMENT '行动名称',
    icon VARCHAR(10) COMMENT '图标emoji',
    description VARCHAR(200) COMMENT '行动描述',
    sort_order INT DEFAULT 0 COMMENT '排序号',
    status TINYINT DEFAULT 1 COMMENT '状态: 1启用 0禁用',
    dimension_code VARCHAR(32) COMMENT '维度code: body/wisdom/wealth/action/mind',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_code (code),
    INDEX idx_status (status),
    INDEX idx_dimension (dimension_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微行动定义（12个1秒健康行动）';

-- 微行动完成记录表
CREATE TABLE IF NOT EXISTS micro_action_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    action_id BIGINT NOT NULL COMMENT '行动ID',
    completed_date DATETIME NOT NULL COMMENT '完成日期',
    earned_energy INT DEFAULT 1 COMMENT '获得能量值',
    note VARCHAR(200) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_date (child_id, completed_date),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微行动完成记录';

-- 每日反馈表（AI/预设健康反馈）
CREATE TABLE IF NOT EXISTS daily_feedback (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    feedback_date DATETIME NOT NULL COMMENT '反馈日期',
    feedback_type VARCHAR(20) DEFAULT 'daily' COMMENT '反馈类型: daily/weekly/milestone',
    content VARCHAR(500) NOT NULL COMMENT 'AI生成的反馈文本',
    milestone_event VARCHAR(100) COMMENT '关联的里程碑事件',
    is_read TINYINT DEFAULT 0 COMMENT '是否已读: 0未读 1已读',
    source VARCHAR(20) DEFAULT 'preset' COMMENT '来源: ai_generated / preset',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_date (child_id, feedback_date),
    INDEX idx_feedback_type (feedback_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每日健康反馈';

-- 健康预警记录表（P1-2 健康行为智能预警）
CREATE TABLE IF NOT EXISTS health_alerts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户ID',
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    rule_code VARCHAR(10) NOT NULL COMMENT '规则编码: R001-R006',
    alert_text VARCHAR(200) NOT NULL COMMENT '预警文案',
    action_suggestion VARCHAR(200) COMMENT '行动建议',
    severity VARCHAR(10) DEFAULT 'medium' COMMENT '严重程度: high/medium/low',
    dismissed TINYINT DEFAULT 0 COMMENT '是否已关闭: 0未关闭 1已关闭',
    dismissed_at DATETIME COMMENT '关闭时间',
    alert_date DATETIME NOT NULL COMMENT '预警日期',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_date (child_id, alert_date),
    INDEX idx_rule (rule_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康预警记录';

-- 健康等级表（P2-1 身份重塑）
CREATE TABLE IF NOT EXISTS health_level (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level_num INT NOT NULL COMMENT '等级编号 1-5',
    title VARCHAR(50) NOT NULL COMMENT '称号',
    badge_icon VARCHAR(20) COMMENT '徽章图标',
    min_score INT DEFAULT 0 COMMENT '所需最低健康积分',
    max_score INT DEFAULT 99999 COMMENT '最高积分上限'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康等级定义';

-- 健康时间线事件表（P2-1 我的健康故事）
CREATE TABLE IF NOT EXISTS health_timeline_event (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户ID',
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    event_type VARCHAR(30) NOT NULL COMMENT '事件类型: streak_7/level_2/first_report',
    event_text VARCHAR(200) NOT NULL COMMENT '事件描述',
    ref_id VARCHAR(50) COMMENT '关联ID',
    event_date DATETIME NOT NULL COMMENT '事件日期',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_child_date (child_id, event_date),
    INDEX idx_event_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康时间线事件';

-- 家庭挑战表（P2-2 家庭健康互动）
CREATE TABLE IF NOT EXISTS family_challenge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    challenge_type VARCHAR(50) NOT NULL COMMENT 'full_checkin/sports/health_week',
    title VARCHAR(200) NOT NULL COMMENT '挑战标题',
    description TEXT COMMENT '挑战描述',
    duration_days INT DEFAULT 7 COMMENT '持续天数',
    reward_points INT DEFAULT 0 COMMENT '奖励积分',
    reward_badge VARCHAR(100) COMMENT '奖励徽章',
    target_mode VARCHAR(20) DEFAULT 'aggregate' COMMENT '目标模式: aggregate=集体总合/all_members=全员个人目标',
    target_value INT DEFAULT 0 COMMENT '目标值(aggregate=家庭合计值, all_members=每人目标值)',
    start_date DATE COMMENT '开始日期',
    end_date DATE COMMENT '结束日期',
    status VARCHAR(20) DEFAULT 'active' COMMENT 'active/completed/cancelled',
    creator_id BIGINT COMMENT '创建者ID（NULL=系统创建）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭挑战';

-- 家庭挑战进度表（P2-2 挑战进度）
CREATE TABLE IF NOT EXISTS family_challenge_progress (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    challenge_id BIGINT NOT NULL COMMENT '挑战ID',
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    progress_value INT DEFAULT 0 COMMENT '进度值',
    completed 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_challenge_id (challenge_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭挑战进度';

-- 家庭挑战参与成员表（参与机制重构：任意成员发起/可选参与/同意后开始）
CREATE TABLE IF NOT EXISTS family_challenge_participant (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    challenge_id BIGINT NOT NULL COMMENT '挑战ID',
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    status VARCHAR(20) NOT NULL COMMENT 'pending/agreed/rejected',
    agreed_at DATETIME NULL COMMENT '同意/拒绝时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_challenge_member (challenge_id, member_id),
    INDEX idx_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭挑战参与成员';

-- 方向性关系分数表（A对B ≠ B对A）
CREATE TABLE IF NOT EXISTS family_relationship_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    from_member_id BIGINT NOT NULL COMMENT '分数持有方(A对B的行)',
    to_member_id BIGINT NOT NULL COMMENT '分数对象方',
    trust_score DECIMAL(5,2) DEFAULT 0 COMMENT 'A对B的信任',
    intimacy_score DECIMAL(5,2) DEFAULT 0 COMMENT 'A对B的亲近',
    communication_score DECIMAL(5,2) DEFAULT 0 COMMENT 'A对B的沟通',
    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 (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='方向性关系分数';

-- 家庭挑战评分配置表（后台可配置）
CREATE TABLE IF NOT EXISTS family_challenge_score_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(20) NOT NULL COMMENT 'initiate/agree/reject',
    score_type VARCHAR(20) NOT NULL COMMENT 'energy/trust/intimacy/communication',
    value INT NOT NULL DEFAULT 0 COMMENT '增减值(可正可负,默认±1)',
    enabled TINYINT DEFAULT 1,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_action_type (action, score_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭挑战评分配置';

-- 健康互动记录表（P2-2 点赞）
CREATE TABLE IF NOT EXISTS health_interaction (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    from_user_id BIGINT NOT NULL COMMENT '点赞用户ID',
    to_user_id BIGINT NOT NULL COMMENT '被赞用户ID',
    action_type VARCHAR(50) NOT NULL COMMENT '行为类型',
    action_id BIGINT COMMENT '关联行为记录ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_family_action (family_id, action_type, action_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康互动记录(点赞)';

-- 社交圈子表（social circle）
CREATE TABLE IF NOT EXISTS social_circle (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '圈子名称',
    type VARCHAR(50) NOT NULL COMMENT '类型: topic/activity/hobby/ability/product/health/provider',
    match_source VARCHAR(50) COMMENT '匹配源',
    source_id BIGINT COMMENT '匹配源ID',
    member_count INT DEFAULT 0 COMMENT '成员数',
    description VARCHAR(500) COMMENT '圈子描述',
    cover_image VARCHAR(500) COMMENT '封面图URL',
    join_condition TINYINT(1) DEFAULT 0 COMMENT '加入条件: 0=公开/1=审核/2=邀请',
    review_required TINYINT(1) DEFAULT 0 COMMENT '是否需要审核: 0=否/1=是',
    is_public TINYINT(1) DEFAULT 1 COMMENT '是否公开可见: 0=否/1=是',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_is_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='社交圈子';

-- 社交圈子成员表（social circle member）
CREATE TABLE IF NOT EXISTS social_circle_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT NOT NULL COMMENT '圈子ID',
    member_id BIGINT NOT NULL COMMENT '用户成员ID',
    member_type VARCHAR(20) NOT NULL COMMENT 'parent/child',
    status VARCHAR(20) DEFAULT 'approved' COMMENT '申请状态: pending/approved/rejected',
    role VARCHAR(20) DEFAULT 'member' COMMENT '角色: member/admin',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_circle_member (circle_id, member_id),
    INDEX idx_circle (circle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='社交圈子成员';

-- 社交圈子动态帖子表
CREATE TABLE IF NOT EXISTS social_circle_post (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT NOT NULL COMMENT '圈子ID',
    member_id BIGINT NOT NULL COMMENT '发布者成员ID',
    member_type VARCHAR(20) NOT NULL COMMENT 'parent/child',
    type VARCHAR(50) NOT NULL COMMENT '帖子类型: checkin/diet/exercise/sleep/water/mood/text',
    content TEXT COMMENT '文字内容',
    checkin_id BIGINT COMMENT '关联打卡记录ID',
    image_urls TEXT COMMENT '图片URL列表JSON',
    like_count INT DEFAULT 0 COMMENT '点赞数',
    comment_count INT DEFAULT 0 COMMENT '评论数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT(1) DEFAULT 0,
    INDEX idx_circle_time (circle_id, created_at),
    INDEX idx_member_time (member_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='圈子动态帖子';

-- 社交圈子点赞表
CREATE TABLE IF NOT EXISTS social_circle_like (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL COMMENT '被点赞的帖子ID',
    member_id BIGINT NOT NULL COMMENT '点赞成员ID',
    member_type VARCHAR(20) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_like (circle_id, post_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='圈子点赞记录';

-- 社交圈子评论表
CREATE TABLE IF NOT EXISTS social_circle_comment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL COMMENT '所属帖子ID',
    parent_id BIGINT DEFAULT NULL COMMENT '回复的评论ID（NULL=一级评论）',
    member_id BIGINT NOT NULL COMMENT '评论者成员ID',
    member_type VARCHAR(20) NOT NULL,
    content TEXT NOT NULL COMMENT '评论内容',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_post_time (post_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='圈子评论记录';

-- 健康圈表（P3 健康社区扩散）
CREATE TABLE IF NOT EXISTS health_circle (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '圈名称',
    description VARCHAR(500) COMMENT '圈描述',
    creator_family_id BIGINT COMMENT '创建家庭ID',
    invite_code VARCHAR(32) COMMENT '邀请码',
    member_limit INT DEFAULT 50 COMMENT '成员上限',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态 active/closed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_invite_code (invite_code),
    INDEX idx_creator (creator_family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康圈';

-- 健康圈成员表（P3）
CREATE TABLE IF NOT EXISTS health_circle_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT NOT NULL COMMENT '圈ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    role VARCHAR(20) DEFAULT 'member' COMMENT '角色 member/admin',
    joined_at DATETIME COMMENT '加入时间',
    left_at DATETIME COMMENT '退出时间',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态 active/inactive',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_circle_id (circle_id),
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康圈成员';

-- 圈挑战表（P3）
CREATE TABLE IF NOT EXISTS circle_challenge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT NOT NULL COMMENT '圈ID',
    challenge_type VARCHAR(50) NOT NULL COMMENT '挑战类型 checkin/step/meditation',
    title VARCHAR(200) NOT NULL COMMENT '挑战标题',
    description VARCHAR(500) COMMENT '挑战描述',
    duration_days INT DEFAULT 7 COMMENT '持续天数',
    period VARCHAR(20) DEFAULT 'weekly' COMMENT '周期 weekly/monthly',
    start_date DATETIME COMMENT '开始日期',
    end_date DATETIME COMMENT '结束日期',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态 active/completed/cancelled',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_circle_id (circle_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='圈挑战';

-- 推广佣金记录表（P2-PROMO）
CREATE TABLE IF NOT EXISTS family_commission (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    promoter_family_id BIGINT NOT NULL COMMENT '推广方家庭ID',
    buyer_family_id BIGINT NOT NULL COMMENT '购买方家庭ID',
    order_id BIGINT COMMENT '关联订单ID',
    amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '订单金额',
    commission_rate DECIMAL(5,2) DEFAULT 10.00 COMMENT '佣金比例%',
    commission_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '佣金金额',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/paid/cancelled',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    paid_at DATETIME COMMENT '支付时间',
    INDEX idx_promoter (promoter_family_id),
    INDEX idx_buyer (buyer_family_id),
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭推广佣金记录';

-- 推广绑定关系表（P2-PROMO）
CREATE TABLE IF NOT EXISTS promotion_binding (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    promoter_family_id BIGINT NOT NULL COMMENT '推广方家庭ID',
    invitee_family_id BIGINT NOT NULL COMMENT '被邀请方家庭ID',
    invite_code VARCHAR(32) COMMENT '使用的邀请码',
    binding_type VARCHAR(20) DEFAULT 'direct' COMMENT 'direct/indirect',
    status VARCHAR(20) DEFAULT 'active' COMMENT 'active/inactive',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY uk_promoter_invitee (promoter_family_id, invitee_family_id),
    INDEX idx_promoter (promoter_family_id),
    INDEX idx_invitee (invitee_family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广绑定关系';

-- 健康维度评分表（7维健康模型）
CREATE TABLE IF NOT EXISTS health_dimension_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    dimension VARCHAR(20) NOT NULL COMMENT '维度编码: growth/sleep/vision/immunity/nutrition/gut/exercise',
    score INT NOT NULL COMMENT '评分(0-100)',
    percentile INT COMMENT '百分位',
    data_source VARCHAR(20) COMMENT '数据来源: MANUAL/REPORT/PHOTO/VOICE/AVERAGE',
    tier INT DEFAULT 3 COMMENT '数据可信度层级: 1=最高(报告) 2=中(AI分析) 3=基础(手动)',
    raw_data TEXT COMMENT '原始数据JSON',
    assess_date DATE COMMENT '评估日期',
    expire_date DATE COMMENT '过期日期',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_member_dim (member_id, dimension),
    INDEX idx_assess_date (assess_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康维度评分';

-- 健康数据源记录表（VOICE/PHOTO等多媒体数据源）
CREATE TABLE IF NOT EXISTS health_data_sources (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    source_type VARCHAR(20) NOT NULL COMMENT '数据源类型: VOICE/PHOTO/REPORT/MANUAL',
    dimension_codes VARCHAR(100) COMMENT '关联维度编码(逗号分隔)',
    parsed_result TEXT COMMENT '解析结果JSON',
    file_url VARCHAR(500) COMMENT '原始文件URL',
    confidence DECIMAL(5,4) COMMENT 'AI解析置信度(0-1)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_source (member_id, source_type),
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康数据源记录';

-- 活动评价表
CREATE TABLE IF NOT EXISTS activity_feedback (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    activity_id BIGINT NOT NULL COMMENT '活动ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    child_id BIGINT COMMENT '孩子ID',
    ratings TEXT COMMENT '评分JSON: {"组织形式":5,"会场气氛":4}',
    comment TEXT COMMENT '文字评价',
    status TINYINT DEFAULT 0 COMMENT '状态: 0=待审核 1=已发布',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_activity_id (activity_id),
    INDEX idx_user_id (user_id),
    INDEX idx_child_id (child_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动评价表';

-- ============================================================
-- 情绪打卡记录表（迁移117）
-- ============================================================
CREATE TABLE IF NOT EXISTS emotion_checkin (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    mood_weather VARCHAR(50) COMMENT '心情晴雨表: sunny/cloudy/rainy/stormy',
    emotion_tags VARCHAR(200) COMMENT '情绪标签(逗号分隔)',
    photo_url VARCHAR(500) COMMENT '配图URL',
    note TEXT COMMENT '文字备注',
    energy_awarded INT DEFAULT 0 COMMENT '奖励能量值',
    checkin_date DATE COMMENT '打卡日期',
    mood_score INT COMMENT '心情分数(1-10)',
    emotion_type VARCHAR(50) COMMENT '情绪类型',
    stress_level INT COMMENT '压力等级(1-10)',
    arousal_level INT COMMENT '激活水平(1-10)',
    energy_level INT COMMENT '精力水平(1-10)',
    sleep_hours DECIMAL(4,1) COMMENT '睡眠时长',
    sleep_quality INT COMMENT '睡眠质量(1-10)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_child_date (child_id, checkin_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='情绪打卡记录';

-- ============================================================
-- 情绪告警表（迁移118）
-- ============================================================
CREATE TABLE IF NOT EXISTS emotion_alert (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT NOT NULL COMMENT '家庭成员ID',
    alert_type VARCHAR(50) NOT NULL COMMENT '告警类型: consecutive_low_mood/crisis_keyword/sharp_decline',
    severity VARCHAR(20) NOT NULL DEFAULT 'low' COMMENT '严重级别: low/medium/high',
    title VARCHAR(200) COMMENT '告警标题',
    message TEXT COMMENT '告警具体描述',
    related_checkin_id BIGINT COMMENT '关联情绪打卡ID',
    resolved TINYINT(1) DEFAULT 0 COMMENT '是否已处理',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME COMMENT '处理时间',
    INDEX idx_child (child_id),
    INDEX idx_alert_type (alert_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='情绪告警';

-- ============================================================
-- 会员等级配置表（迁移119）
-- ============================================================
CREATE TABLE IF NOT EXISTS membership_levels (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level_code VARCHAR(50) NOT NULL COMMENT '等级编码: FREE/BASIC/PROFESSIONAL/ENTERPRISE',
    level_name VARCHAR(100) NOT NULL COMMENT '等级名称',
    level_desc TEXT COMMENT '等级描述',
    price_monthly INT COMMENT '月费(分)',
    price_quarterly INT COMMENT '季费(分)',
    price_yearly INT COMMENT '年费(分)',
    original_price_monthly INT COMMENT '原价-月费(分)',
    original_price_quarterly INT COMMENT '原价-季费(分)',
    original_price_yearly INT COMMENT '原价-年费(分)',
    features TEXT COMMENT '功能权限列表(JSON)',
    max_children INT DEFAULT 0 COMMENT '可添加孩子数量',
    max_tasks_per_day INT DEFAULT 0 COMMENT '每日任务上限',
    ai_review_enabled TINYINT(1) DEFAULT 0 COMMENT 'AI审核功能',
    priority_support TINYINT(1) DEFAULT 0 COMMENT '优先客服',
    teacher_consultation TINYINT(1) DEFAULT 0 COMMENT '老师咨询',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_level_code (level_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员等级配置';

-- ============================================================
-- 会员权益配置表（会员等级独立权益管理）
-- ============================================================
CREATE TABLE IF NOT EXISTS membership_benefits (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level_code VARCHAR(50) NOT NULL COMMENT '会员等级: FREE/BASIC/PROFESSIONAL/ENTERPRISE',
    benefit_code VARCHAR(50) NOT NULL COMMENT '权益编码: AI_CHAT,FAMILY_MEMBERS等',
    benefit_name VARCHAR(100) NOT NULL COMMENT '权益名称',
    benefit_desc VARCHAR(500) COMMENT '权益描述',
    icon VARCHAR(50) COMMENT '图标emoji',
    bg_color VARCHAR(20) COMMENT '背景色',
    sort_order INT DEFAULT 0 COMMENT '排序顺序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_level_benefit (level_code, benefit_code),
    INDEX idx_level (level_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员权益配置';

-- 会员权益种子数据
INSERT IGNORE INTO membership_benefits (level_code, benefit_code, benefit_name, benefit_desc, icon, bg_color, sort_order) VALUES
('FREE',  'NO_BENEFIT', '暂无会员权益', '开通会员解锁更多权益', '🔒', '#F3F4F6', 0),
('FREE',  'UPGRADE_HINT', '升级会员', '开通会员享专属权益', '👑', '#FEF3C7', 99),
('FAMILY', 'AI_CHAT', 'AI 健康顾问', '7×24 小时不限次数问答', '🤖', '#DBEAFE', 1),
('FAMILY', 'FAMILY_MEMBERS', '家庭档案管理', '管理最多 10 位家庭成员', '👨‍👩‍👧‍👦', '#D1FAE5', 2),
('FAMILY', 'HEALTH_PLAN', '月度健康计划', '个性化健康改善方案', '📋', '#FEF3C7', 3),
('FAMILY', 'REPORT_READING', '报告智能解读', '体检/菌群/测评报告 AI 解读', '📊', '#FCE7F3', 4),
('FAMILY', 'MANAGER_SERVICE', '专属健康教练服务', '认证健康教练月度随访', '👨‍🏫', '#FFEDD5', 5),
('FAMILY', 'PRODUCT_DISCOUNT', '商品会员折扣', '全场商品 8-9 折优惠', '🛒', '#E0E7FF', 6),
('FAMILY', 'COUPON_ACCESS', '优惠券使用权限', '可使用平台优惠券享更多优惠', '🎫', '#FEE2E2', 7),
('PREMIUM', 'AI_CHAT', 'AI 健康顾问', '7×24 小时不限次数问答', '🤖', '#DBEAFE', 1),
('PREMIUM', 'FAMILY_MEMBERS', '家庭档案管理', '管理最多 20 位家庭成员', '👨‍👩‍👧‍👦', '#D1FAE5', 2),
('PREMIUM', 'HEALTH_PLAN', '月度健康计划', '个性化健康改善方案 + 定制营养建议', '📋', '#FEF3C7', 3),
('PREMIUM', 'REPORT_READING', '报告智能解读', '体检/菌群/测评报告 AI 深度解读', '📊', '#FCE7F3', 4),
('PREMIUM', 'MANAGER_SERVICE', '专属健康教练服务', '每周 1 次电话随访 + 在线答疑', '👨‍🏫', '#FFEDD5', 5),
('PREMIUM', 'PRODUCT_DISCOUNT', '商品会员折扣', '全场商品 6-8 折优惠', '🛒', '#E0E7FF', 6),
('PREMIUM', 'COUPON_ACCESS', '优惠券使用权限', '可使用平台优惠券 + 会员专属券', '🎫', '#FEE2E2', 7),
('PREMIUM', 'PRIORITY_SUPPORT', '优先客服', '专属客服通道，快速响应', '🎧', '#EDE9FE', 8),
('PREMIUM', 'TEACHER_CONSULT', '规划师一对一咨询', '每月 1 次成长规划师专业咨询', '👩‍🏫', '#FBCFE8', 9),
('PREMIUM', 'GROWTH_COACH', '7X24小时成长教练', 'AI智能教练随时解答家庭教育问题 + 优先响应', '🎯', '#FEF3C7', 10),
('FAMILY', 'GROWTH_COACH', '7X24小时成长教练', 'AI智能教练随时解答家庭教育问题', '🎯', '#FEF3C7', 10);

-- ============================================================
-- 家庭会员订阅表（迁移120）
-- ============================================================
CREATE TABLE IF NOT EXISTS family_memberships (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    level_code VARCHAR(50) NOT NULL DEFAULT 'FREE' COMMENT '会员等级编码',
    start_date DATETIME COMMENT '开始时间',
    end_date DATETIME COMMENT '结束时间',
    payment_method VARCHAR(50) COMMENT '支付方式: wechat/alipay/card',
    payment_status VARCHAR(20) DEFAULT 'pending' COMMENT '支付状态: pending/paid/expired/refunded',
    order_no VARCHAR(100) COMMENT '订单号',
    transaction_id VARCHAR(200) COMMENT '交易流水号',
    amount INT COMMENT '支付金额(分)',
    auto_renew TINYINT(1) 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_end_date (end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭会员订阅';

-- ============================================================
-- 分享事件表（迁移121）
-- ============================================================
CREATE TABLE IF NOT EXISTS share_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '分享用户ID',
    target_type VARCHAR(50) NOT NULL COMMENT '分享目标类型: article/growth/product',
    target_id BIGINT NOT NULL COMMENT '分享目标ID',
    share_channel VARCHAR(50) COMMENT '分享渠道: wechat/timeline/qq/url',
    share_title VARCHAR(200) COMMENT '分享标题',
    share_url VARCHAR(500) COMMENT '分享URL',
    click_count INT DEFAULT 0 COMMENT '点击次数',
    read_count INT DEFAULT 0 COMMENT '阅读次数',
    reward_points INT DEFAULT 0 COMMENT '奖励积分',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/expired',
    expires_at DATETIME COMMENT '过期时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分享事件';

-- ============================================================
-- 分享阅读记录表（迁移122）
-- ============================================================
CREATE TABLE IF NOT EXISTS share_reads (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    share_event_id BIGINT NOT NULL COMMENT '分享事件ID',
    reader_id BIGINT COMMENT '阅读者用户ID',
    reader_ip VARCHAR(50) COMMENT '阅读者IP',
    read_duration INT COMMENT '阅读时长(秒)',
    read_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '阅读时间',
    INDEX idx_share_event (share_event_id),
    INDEX idx_reader (reader_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分享阅读记录';

-- ============================================================
-- 饮食推荐指数表（迁移123）
-- ============================================================
CREATE TABLE IF NOT EXISTS food_recommend_idx (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    food_id BIGINT NOT NULL COMMENT '食材ID',
    food_name VARCHAR(100) COMMENT '食材名称',
    index_score INT COMMENT '推荐指数(0-100)',
    health_report_id BIGINT COMMENT '关联健康报告ID',
    calculated_at DATETIME COMMENT '计算时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_food (food_id),
    INDEX idx_report (health_report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮食推荐指数';

-- 报告食材适宜度
CREATE TABLE IF NOT EXISTS report_food_suitability (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT NOT NULL COMMENT '健康报告ID',
    food_id BIGINT COMMENT '食材ID',
    food_name VARCHAR(100) NOT NULL COMMENT '食材名称',
    category VARCHAR(50) COMMENT '分类',
    score INT NOT NULL COMMENT '适宜度评分',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_report (report_id),
    INDEX idx_food (food_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告食材适宜度';


-- ============================================================
-- 积分兑换商品表（迁移124）
-- ============================================================
CREATE TABLE IF NOT EXISTS points_exchange_products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    description TEXT COMMENT '商品描述',
    cover_image VARCHAR(500) COMMENT '封面图片',
    points_price INT NOT NULL COMMENT '积分价格',
    stock INT DEFAULT 0 COMMENT '库存',
    product_type VARCHAR(50) COMMENT '商品类型',
    category VARCHAR(50) COMMENT '分类',
    expiry_days INT COMMENT '有效期(天)',
    sort_order INT DEFAULT 0 COMMENT '排序',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/disabled',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换商品';

-- ============================================================
-- 积分兑换记录表（迁移125）
-- ============================================================
CREATE TABLE IF NOT EXISTS points_exchange_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户ID',
    child_id BIGINT COMMENT '孩子ID',
    family_member_id BIGINT COMMENT '家庭成员ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    product_name VARCHAR(200) COMMENT '商品名称快照',
    points_cost INT NOT NULL COMMENT '消耗积分',
    quantity INT DEFAULT 1 COMMENT '数量',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/completed/cancelled',
    redeem_code VARCHAR(100) COMMENT '兑换码',
    expired_at DATETIME COMMENT '过期时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_product (product_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换记录';

-- 文章阅读记录表
CREATE TABLE IF NOT EXISTS article_reading_records (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  article_id BIGINT DEFAULT NULL COMMENT '文章ID',
  user_id BIGINT COMMENT '用户ID',
  child_id BIGINT NOT NULL COMMENT '孩子ID',
  content VARCHAR(500) COMMENT '阅读内容摘要',
  duration_seconds INT DEFAULT 0 COMMENT '阅读时长(秒)',
  task_id BIGINT DEFAULT NULL COMMENT '关联的任务ID',
  read_at DATETIME COMMENT '阅读时间',
  updated_at DATETIME DEFAULT NULL COMMENT '更新时间',
  created_at DATETIME,
  INDEX idx_child_id (child_id),
  INDEX idx_read_at (read_at),
  INDEX idx_article_child_date (article_id, child_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章阅读记录表';

-- ============================================================
-- 统一报告汇总表（report_summary）
-- ============================================================
CREATE TABLE IF NOT EXISTS report_summary (
  id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  report_type VARCHAR(32) NOT NULL COMMENT '报告类型',
  source_table VARCHAR(32) NOT NULL COMMENT '来源表',
  source_id BIGINT NOT NULL COMMENT '来源表记录ID',
  user_id BIGINT NOT NULL COMMENT '创建者用户ID',
  family_id BIGINT COMMENT '家庭ID',
  subject_id BIGINT COMMENT '报告主体ID',
  subject_name VARCHAR(50) COMMENT '报告主体名称',
  title VARCHAR(200) COMMENT '报告标题',
  summary TEXT COMMENT '摘要',
  file_url VARCHAR(500) COMMENT '文件URL',
  file_size BIGINT COMMENT '文件大小',
  file_name VARCHAR(200) COMMENT '文件名',
  has_file TINYINT DEFAULT 0 COMMENT '是否有文件',
  overall_score INT COMMENT '综合评分',
  report_date DATE COMMENT '报告日期',
  status VARCHAR(20) COMMENT '状态',
  detail_route VARCHAR(100) 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_subject_id (subject_id),
  INDEX idx_report_type (report_type),
  INDEX idx_report_date (report_date),
  INDEX idx_status (status),
  UNIQUE KEY uk_source (source_table, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='统一报告汇总表';
-- report_summary 表已由 DatabaseInitializer 迁移创建

-- category_ppoint: 品类P点比例配置（P点体系）
CREATE TABLE IF NOT EXISTS category_ppoint (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL COMMENT '商品类目ID',
    ratio INT DEFAULT 0 COMMENT 'P点比例(千分比)',
    start_date DATETIME COMMENT '生效日期',
    end_date DATETIME COMMENT '失效日期(null=永不过期)',
    created_by BIGINT COMMENT '创建人',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='品类P点比例配置';

-- commission_dist_log: P点分润记录（P点体系）
CREATE TABLE IF NOT EXISTS commission_dist_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    buyer_id BIGINT NOT NULL COMMENT '买家用户ID',
    order_id BIGINT COMMENT '订单ID',
    product_id BIGINT COMMENT '商品ID',
    source_ppoint INT DEFAULT 0 COMMENT '本次产生的原始P点数量',
    level1_user_id BIGINT COMMENT '一级推荐人用户ID',
    level1_ppoint INT DEFAULT 0 COMMENT '一级推荐人获得P点',
    level1_wealth_percent INT DEFAULT 0 COMMENT '一级推荐人当时财富等级(profitSharePercent)',
    level2_user_id BIGINT COMMENT '二级推荐人用户ID',
    level2_ppoint INT DEFAULT 0 COMMENT '二级推荐人获得P点',
    level2_wealth_percent INT DEFAULT 0 COMMENT '二级推荐人当时财富等级(profitSharePercent)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/completed/failed',
    remark VARCHAR(500) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_buyer (buyer_id),
    INDEX idx_order (order_id),
    INDEX idx_level1 (level1_user_id),
    INDEX idx_level2 (level2_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='P点分润记录';

-- 会员订单表
CREATE TABLE IF NOT EXISTS payment_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    level_code VARCHAR(50) DEFAULT 'FREE' COMMENT '会员等级编码',
    payment_type VARCHAR(20) DEFAULT 'yearly' COMMENT '支付类型: yearly/monthly/trial',
    period VARCHAR(20) DEFAULT 'yearly' COMMENT '订阅周期: monthly/quarterly/yearly',
    amount INT COMMENT '支付金额(分)',
    user_coupon_id BIGINT COMMENT '使用的用户优惠券ID',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/paid/expired',
    group_no VARCHAR(64) COMMENT '拆单组号(同一拆单组共享)',
    period_no INT DEFAULT 1 COMMENT '期数序号(1开始)',
    pay_method VARCHAR(20) COMMENT '支付方式',
    transaction_id VARCHAR(200) COMMENT '交易流水号',
    paid_at DATETIME COMMENT '支付时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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 energy_behavior_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    behavior_code VARCHAR(32) NOT NULL COMMENT '行为编码: task_complete/game_finish/...',
    behavior_name VARCHAR(50) NOT NULL COMMENT '行为名称',
    description VARCHAR(200) COMMENT '行为描述',
    default_amount INT DEFAULT 5 COMMENT '默认能量值',
    amount_source VARCHAR(32) DEFAULT 'fixed' COMMENT '能量值来源: fixed(固定)/points(跟随积分)/order_amount(跟随金额)/config(从配置表读取)',
    dimension_assign JSON COMMENT '维度分配: [{"dim":"action","ratio":100}]',
    daily_limit INT DEFAULT 0 COMMENT '每日上限(0=不限制)',
    cooldown_seconds INT DEFAULT 0 COMMENT '冷却时间(秒,0=无冷却)',
    expire_days INT DEFAULT 0 COMMENT '过期天数(0=永久)',
    enabled TINYINT DEFAULT 1 COMMENT '是否启用: 0=禁用 1=启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_behavior_code (behavior_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能量行为配置表';

-- 文章评论表
CREATE TABLE IF NOT EXISTS article_comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    article_id BIGINT NOT NULL COMMENT '文章ID',
    parent_id BIGINT COMMENT '父评论ID',
    reply_to_id BIGINT COMMENT '引用的评论ID',
    user_id BIGINT NOT NULL COMMENT '评论用户ID',
    content TEXT NOT NULL COMMENT '评论内容',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/approved/rejected',
    audit_by BIGINT COMMENT '审核人ID',
    audit_at DATETIME COMMENT '审核时间',
    earned_energy INT DEFAULT 0 COMMENT '获得的能量',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_article (article_id),
    INDEX idx_article_status (article_id, status),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章评论';

-- ============================================================
-- 服务角色申请表（统一角色申请入口）
-- ============================================================
CREATE TABLE IF NOT EXISTS `service_role_applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '申请人ID',
  `roles` varchar(255) NOT NULL COMMENT '申请角色,JSON数组 e.g. ["planner","nutritionist"]',
  `id_card` varchar(18) DEFAULT '' COMMENT '身份证号',
  `id_card_images` text COMMENT '身份证照片,JSON数组[正面URL,反面URL]',
  `certificate_images` text COMMENT '资质证书照片,JSON数组',
  `province` varchar(50) DEFAULT '' COMMENT '省',
  `city` varchar(50) DEFAULT '' COMMENT '市',
  `district` varchar(50) DEFAULT '' COMMENT '区',
  `street` varchar(50) DEFAULT '' COMMENT '街道',
  `address_detail` varchar(200) DEFAULT '' COMMENT '详细地址',
  `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0-pending, 1-approved, 2-rejected',
  `reject_reason` varchar(500) DEFAULT '' COMMENT '驳回原因',
  `reviewed_by` int(11) DEFAULT NULL COMMENT '审核人ID',
  `reviewed_at` datetime DEFAULT NULL COMMENT '审核时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务角色申请表';

-- 用户表新增字段（服务角色申请状态）
-- ALTER TABLE users ADD COLUMN article_admin_status VARCHAR(20) DEFAULT NULL COMMENT '文章管理员状态: pending/approved/rejected';
-- ALTER TABLE users ADD COLUMN activity_provider_status VARCHAR(20) DEFAULT NULL COMMENT '活动提供商状态: pending/approved/rejected';

-- ============================================================
-- 周期性服务续费配置
-- ============================================================
CREATE TABLE IF NOT EXISTS periodic_services (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '关联商品ID',
    service_name VARCHAR(100) COMMENT '服务名称',
    cycle_days INT NOT NULL COMMENT '周期天数（如365=年度服务）',
    remind_before_days INT DEFAULT 30 COMMENT '提前提醒天数',
    auto_renew TINYINT DEFAULT 0 COMMENT '是否支持自动续费: 0=否 1=是',
    enabled TINYINT DEFAULT 1 COMMENT '是否启用: 0=禁用 1=启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='周期性服务续费配置';

-- ============================================================
-- 推荐行为记录
-- ============================================================
CREATE TABLE IF NOT EXISTS recommendation_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户ID',
    family_id BIGINT COMMENT '家庭ID',
    child_id BIGINT COMMENT '孩子ID',
    content_type VARCHAR(20) NOT NULL COMMENT '内容类型: article/task/product/activity',
    content_id BIGINT NOT NULL COMMENT '内容ID',
    recommend_score INT COMMENT '推荐分值',
    action VARCHAR(20) COMMENT '行为: show/click/consume',
    recommend_type VARCHAR(20) DEFAULT 'tag' COMMENT '推荐方式: profile/dimension/tag',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_content (user_id, content_type, content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推荐行为记录';

-- products表添加family_scope列（推荐系统-家庭消费去重）
-- ALTER TABLE products ADD COLUMN family_scope TINYINT DEFAULT 1 COMMENT '是否面向家庭: 1=家庭成员消费过即排除, 0=仅按个人消费排除';

-- activities表添加family_scope列（推荐系统-家庭参与去重）
-- ALTER TABLE activities ADD COLUMN family_scope TINYINT DEFAULT 1 COMMENT '是否面向家庭: 1=家庭成员参与过即排除, 0=仅按个人参与排除';

-- ============================================================
-- 活动报名订单表（付费报名：先支付后报名，迁移143）
-- ============================================================
CREATE TABLE IF NOT EXISTS activity_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL UNIQUE COMMENT '订单号(ACT+时间戳+随机)',
    activity_id BIGINT NOT NULL COMMENT '活动ID',
    user_id BIGINT NOT NULL COMMENT '下单人userId',
    total_amount INT NOT NULL COMMENT '实付金额(分)',
    registrants_json TEXT COMMENT '报名人快照JSON: [{name, phone, childId, type: child/parent, fee}]',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/paid/cancelled',
    transaction_id VARCHAR(64) COMMENT '微信支付单号',
    paid_at DATETIME COMMENT '支付时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_activity (activity_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动报名订单表';

-- ============================================================
-- 活动总结资料表（总结中阶段发布人上传资料，迁移144）
-- ============================================================
CREATE TABLE IF NOT EXISTS activity_materials (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    activity_id BIGINT NOT NULL COMMENT '活动ID',
    title VARCHAR(200) COMMENT '标题',
    file_url VARCHAR(500) NOT NULL COMMENT '文件地址(图片/PDF/外链URL)',
    file_type VARCHAR(20) DEFAULT 'image' COMMENT '类型: image/pdf/link',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_activity (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动总结资料表';

-- ============================================================
-- 小程序虚拟支付道具映射
-- ============================================================
CREATE TABLE IF NOT EXISTS virtual_goods_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    goods_type VARCHAR(32) NOT NULL COMMENT 'ASSESSMENT_PACKAGE / MEMBERSHIP / SUBSCRIPTION',
    biz_key VARCHAR(64) NOT NULL COMMENT '业务SKU标识: 测评套餐ID / 会员等级code / 订阅档位code',
    product_id VARCHAR(64) NOT NULL COMMENT '微信商户后台道具ID',
    goods_name VARCHAR(128) NOT NULL COMMENT '道具名称',
    goods_price INT COMMENT '道具价格(分), 用于拆单判断',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1=启用 0=停用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_goods_type_biz (goods_type, biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小程序虚拟支付道具映射';

-- ============================================================
-- 家庭天盘周报导出记录表（迁移144）
-- ============================================================
CREATE TABLE IF NOT EXISTS family_fortune_report (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id INT NOT NULL COMMENT '家庭ID',
    element VARCHAR(32) COMMENT '主导五行元素',
    lucky_direction VARCHAR(64) COMMENT '本周吉位',
    pdf_path VARCHAR(255) COMMENT 'PDF周报文件路径',
    file_size BIGINT COMMENT '文件大小(字节)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭天盘周报导出记录';

-- ============================================================
-- 五维家庭自检记录表（迁移146，P0-1 五维自检问卷，书稿终章15题）
-- ============================================================
CREATE TABLE IF NOT EXISTS five_dimension_self_checks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '答卷人用户ID',
    family_member_id BIGINT COMMENT '关联家庭成员ID(可选)',
    answers_json TEXT COMMENT '用户答案JSON',
    scores_json VARCHAR(512) COMMENT '各维度得分JSON',
    total_score INT COMMENT '五维总分(满分45)',
    advice_json TEXT COMMENT '五行相生寻源建议JSON',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    question_ids_json VARCHAR(255) DEFAULT NULL COMMENT '本次自检使用的题号JSON: [1,5,9,...]',
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='五维家庭自检记录';

-- 商品赠送会员规则
CREATE TABLE IF NOT EXISTS product_gift_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '关联products表',
    level_code VARCHAR(50) NOT NULL COMMENT '赠送会员等级编码(FAMILY/PREMIUM等)',
    status TINYINT(1) DEFAULT 1 COMMENT '1=启用 0=停用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_product_level (product_id, level_code),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品赠送会员规则';

CREATE TABLE IF NOT EXISTS interaction_logs (
    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',
    interaction_type VARCHAR(20) NOT NULL COMMENT '互动类型: dining/outgoing/call/video/chat/play/gift',
    description VARCHAR(500) COMMENT '互动描述',
    happened_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '互动发生时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ilog_family (family_id),
    INDEX idx_ilog_to_member (to_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭成员互动记录';

CREATE TABLE IF NOT EXISTS relationship_questionnaire_snapshots (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    respondent_id BIGINT NOT NULL COMMENT '答卷人用户ID',
    member_name VARCHAR(50) COMMENT '成员名称',
    relationship_type VARCHAR(20) COMMENT '关系类型: parent/child',
    ai_generated_json TEXT COMMENT 'AI生成的题目JSON',
    version VARCHAR(20) COMMENT '版本号',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_q_snap_member (family_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='关系问卷快照';

CREATE TABLE IF NOT EXISTS relationship_questionnaire_responses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    snapshot_id BIGINT NOT NULL COMMENT '问卷快照ID',
    respondent_id BIGINT NOT NULL COMMENT '答卷人用户ID',
    answers_json TEXT COMMENT '用户回答JSON',
    trust_score DECIMAL(5,2) DEFAULT 0 COMMENT '信任度得分',
    intimacy_score DECIMAL(5,2) DEFAULT 0 COMMENT '亲密度得分',
    communication_score DECIMAL(5,2) DEFAULT 0 COMMENT '沟通质量得分',
    interaction_bonus DECIMAL(5,2) DEFAULT 0 COMMENT '互动记录加分',
    total_score DECIMAL(5,2) DEFAULT 0 COMMENT '综合得分',
    calculated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_q_resp_snap (snapshot_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='关系问卷回答记录';

-- =============================================
-- 优惠券发放流水表（会员体系重构: 统一价+优惠券发放/积分兑换）
-- =============================================

CREATE TABLE IF NOT EXISTS coupon_grant_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '收券用户ID',
    coupon_id BIGINT NOT NULL COMMENT '券模板ID',
    grant_type VARCHAR(16) NOT NULL COMMENT 'JOIN/PERIODIC/POPULATION/EXCHANGE',
    period VARCHAR(16) COMMENT '周期标识(YYYY-MM或YYYY-Qn),PERIODIC防重用',
    quantity INT DEFAULT 1 COMMENT '发放数量',
    source VARCHAR(64) COMMENT '触发来源(订单号/成员ID等)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_grant (user_id, coupon_id, grant_type, period),
    INDEX idx_coupon (coupon_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券发放流水表';
-- 家庭加入请求（邀请人审批，旧数据由家庭管理员兜底）
CREATE TABLE IF NOT EXISTS family_join_requests (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    requester_user_id BIGINT NOT NULL COMMENT '申请加入的用户ID',
    target_family_id BIGINT NOT NULL COMMENT '目标家庭ID',
    join_method VARCHAR(20) NOT NULL COMMENT '加入方式: by_code/by_token',
    invite_ref VARCHAR(50) COMMENT '邀请码或令牌',
    inviter_user_id BIGINT COMMENT '邀请人用户ID（分享邀请令牌的人）',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/approved/rejected/cancelled',
    approver_user_id BIGINT COMMENT '审批人ID',
    comment TEXT COMMENT '审批备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_requester (requester_user_id),
    INDEX idx_target_family (target_family_id),
    INDEX idx_status (status),
    INDEX idx_inviter (inviter_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭加入请求（待审批）';

-- 用户同意记录表
CREATE TABLE IF NOT EXISTS user_consent_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    consent_type VARCHAR(50) NOT NULL COMMENT '同意类型: privacy/agreement/children_data/ai_processing',
    version VARCHAR(20) NOT NULL COMMENT '协议版本号',
    status VARCHAR(20) NOT NULL DEFAULT 'agreed' COMMENT 'agreed/withdrawn/archived',
    ip_address VARCHAR(45) COMMENT '用户IP地址',
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    INDEX idx_user_consent (user_id, consent_type),
    INDEX idx_consent_type (consent_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户同意记录';

-- 用户数据授权表
CREATE TABLE IF NOT EXISTS user_data_permissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    allow_ai_analysis TINYINT(1) DEFAULT 0 COMMENT '允许AI分析对话数据',
    allow_personalized_recommend TINYINT(1) DEFAULT 1 COMMENT '允许个性化推荐',
    allow_share_teacher TINYINT(1) DEFAULT 1 COMMENT '允许向成长规划师共享数据',
    allow_third_party_share TINYINT(1) DEFAULT 0 COMMENT '允许与第三方共享数据',
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    INDEX idx_user_perm (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户数据使用授权';

-- ===== 饮食模块 =====

-- 食材库表
CREATE TABLE IF NOT EXISTS foods (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '食材名称',
    category VARCHAR(50) COMMENT '分类: 蔬菜/水果/肉禽/水产/蛋奶/豆类/谷物/调味/其他',
    unit VARCHAR(20) DEFAULT 'g' COMMENT '计量单位',
    calories DECIMAL(8,2) COMMENT '热量(kcal/100g)',
    protein DECIMAL(8,2) COMMENT '蛋白质(g/100g)',
    fat DECIMAL(8,2) COMMENT '脂肪(g/100g)',
    carbs DECIMAL(8,2) COMMENT '碳水(g/100g)',
    fiber DECIMAL(8,2) COMMENT '膳食纤维(g/100g)',
    energy_kj DECIMAL(8,2) COMMENT '能量(kJ/100g)',
    starch DECIMAL(8,2) COMMENT '淀粉(g/100g)',
    cholesterol DECIMAL(8,2) COMMENT '胆固醇(mg/100g)',
    price_level TINYINT COMMENT '价格等级 1-5',
    nutrition_tags VARCHAR(500) COMMENT '营养标签JSON',
    suitable_for VARCHAR(200) COMMENT '适宜人群JSON',
    allergens VARCHAR(200) COMMENT '过敏原JSON',
    external_source VARCHAR(100) COMMENT '数据来源',
    external_id VARCHAR(100) COMMENT '外部ID',
    cover_image VARCHAR(500) COMMENT '食材图片',
    report_id BIGINT COMMENT '关联报告ID（从PDF解析的食物推荐）',
    score INT COMMENT '食物评分',
    status VARCHAR(20) DEFAULT 'active',
    sort_order INT DEFAULT 0,
    diet_type VARCHAR(50) COMMENT '饮食类型',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_price (price_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='食材库表';

-- 饮食偏好调研表（每成员一份）
CREATE TABLE IF NOT EXISTS diet_preferences (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_member_id BIGINT NOT NULL COMMENT '绑定家庭成员',
    allergies JSON COMMENT '过敏原列表',
    absolute_avoid JSON COMMENT '绝对忌口列表',
    must_eat JSON COMMENT '必吃食物列表',
    religious_diet VARCHAR(50) DEFAULT 'none' COMMENT '宗教饮食：none/清真/素食/纯素/印度素',
    spice_level TINYINT DEFAULT NULL COMMENT '辣度 0-5',
    flavor_pref JSON COMMENT '口味偏好（酸/甜/咸/鲜/苦）',
    cuisine_pref JSON COMMENT '菜系偏好（川/粤/淮扬/日料/西餐）',
    cooking_methods JSON COMMENT '偏好烹饪方式（炒/蒸/煮/烤/凉拌）',
    health_goals JSON COMMENT '健康目标（减脂/增肌/控糖/肠道调理/护肝）',
    goal_source VARCHAR(20) DEFAULT NULL COMMENT 'manual/system_recommend',
    goal_confirmed TINYINT(1) DEFAULT 0 COMMENT '用户已确认',
    filled_at TIMESTAMP NULL COMMENT '首次填写时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_member (family_member_id),
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮食偏好调研表';

-- 共餐配置表（按家庭+日期类型+餐次）
CREATE TABLE IF NOT EXISTS meal_configs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    config_date_type VARCHAR(20) NOT NULL COMMENT '工作日/周末/节假日',
    meal_type VARCHAR(20) NOT NULL COMMENT 'breakfast/lunch/dinner',
    participant_member_ids JSON NOT NULL COMMENT '参与成员ID列表',
    notes VARCHAR(255) DEFAULT NULL COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_config (family_id, config_date_type, meal_type),
    INDEX idx_family (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='共餐配置表';

-- 食谱推荐方案表
CREATE TABLE IF NOT EXISTS diet_recommendations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    recommendation_date DATE NOT NULL COMMENT '推荐日期',
    meal_type VARCHAR(20) NOT NULL COMMENT 'breakfast/lunch/dinner',
    participant_member_ids JSON COMMENT '参与成员ID列表',
    menu_json MEDIUMTEXT NOT NULL COMMENT '菜品列表JSON',
    nutrition_summary JSON COMMENT '营养汇总',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/accepted/adjusted/completed/skipped',
    version INT DEFAULT 1 COMMENT '版本号（重新生成+1）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family_date (family_id, recommendation_date),
    INDEX idx_meal (meal_type, recommendation_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='食谱推荐方案表';

-- 饮食记录表
CREATE TABLE IF NOT EXISTS diet_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    member_id BIGINT NOT NULL COMMENT '吃的成员ID',
    meal_type VARCHAR(20) NOT NULL COMMENT 'breakfast/lunch/dinner/snack',
    record_date DATE NOT NULL COMMENT '记录日期',
    record_method VARCHAR(20) DEFAULT NULL COMMENT 'photo/manual/from_recommendation',
    image_url VARCHAR(500) DEFAULT NULL COMMENT '原图OSS路径',
    ai_recognized_foods JSON DEFAULT NULL COMMENT 'AI识别结果',
    user_confirmed_foods JSON NOT NULL COMMENT '用户最终确认食材',
    notes VARCHAR(500) DEFAULT NULL COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_date (member_id, record_date),
    INDEX idx_family_date (family_id, record_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮食记录表';

-- 饮食记录食材明细表
CREATE TABLE IF NOT EXISTS diet_record_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    record_id BIGINT NOT NULL COMMENT 'diet_records.id',
    food_name VARCHAR(100) NOT NULL COMMENT '食材名称',
    food_id BIGINT DEFAULT NULL COMMENT '关联foods表ID',
    confidence DECIMAL(3,2) DEFAULT NULL COMMENT 'AI置信度0-1',
    source VARCHAR(20) DEFAULT NULL COMMENT 'ai_recognized/manual/from_recommendation',
    INDEX idx_record (record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='饮食记录食材明细表';

-- 报告指纹检测与解析器调度系统

-- 报告类型注册表
CREATE TABLE IF NOT EXISTS report_type_registry (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    type_id VARCHAR(64) NOT NULL COMMENT '类型标识，如 dan_a1/gut_beijing',
    family VARCHAR(32) DEFAULT NULL COMMENT '报告家族: dan/gut_flora/其他',
    display_name VARCHAR(100) NOT NULL COMMENT '显示名称',
    description VARCHAR(500) DEFAULT NULL COMMENT '描述',
    parser_path VARCHAR(255) DEFAULT NULL COMMENT 'Python解析器脚本路径',
    min_confidence INT DEFAULT 10 COMMENT '识别阈值',
    is_active TINYINT(1) DEFAULT 1 COMMENT '启用状态',
    source VARCHAR(20) DEFAULT 'prebuilt' COMMENT '来源: prebuilt/imported/auto_generated',
    review_status VARCHAR(20) DEFAULT 'approved' COMMENT '审核状态: approved/pending/rejected',
    version VARCHAR(20) DEFAULT '1.0' COMMENT '版本',
    created_by BIGINT DEFAULT NULL COMMENT '创建人ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_type_id (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告类型注册表';

-- 报告指纹规则表
CREATE TABLE IF NOT EXISTS report_fingerprint (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    type_id VARCHAR(64) NOT NULL COMMENT '关联report_type_registry.type_id',
    pattern VARCHAR(200) NOT NULL COMMENT '文本特征',
    weight INT DEFAULT 10 COMMENT '权重(正=加分,负=排除扣分)',
    is_exclusion TINYINT(1) DEFAULT 0 COMMENT '是否为排除规则',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fp_type (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告指纹规则表';

-- 未知报告聚类表
CREATE TABLE IF NOT EXISTS report_unknown_cluster (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    cluster_key VARCHAR(64) NOT NULL COMMENT '聚类哈希键（同类报告分组）',
    common_signatures TEXT COMMENT '公共文本特征JSON（用于生成指纹）',
    llm_template TEXT COMMENT 'LLM解析结果模板JSON',
    report_count INT DEFAULT 0 COMMENT '累计报告数',
    status VARCHAR(20) DEFAULT 'collecting' COMMENT '状态: collecting/ready/generated/imported',
    generated_type_id VARCHAR(64) DEFAULT NULL COMMENT '生成的类型ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_cluster_key (cluster_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='未知报告聚类表';

-- 未知报告上传明细表
CREATE TABLE IF NOT EXISTS report_unknown_upload (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    cluster_id BIGINT DEFAULT NULL COMMENT '关联report_unknown_cluster.id',
    file_url VARCHAR(500) DEFAULT NULL COMMENT '文件路径',
    file_hash VARCHAR(64) DEFAULT NULL COMMENT '文件哈希',
    llm_result TEXT COMMENT 'LLM解析结果JSON',
    annotation TEXT COMMENT '管理员标注: 手动指定类型+确认提取结果JSON',
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_unknown_cluster (cluster_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='未知报告上传明细表';

-- ========== 报告展示模板表 ==========
CREATE TABLE IF NOT EXISTS report_template (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '模板名称',
    report_type VARCHAR(50) NOT NULL COMMENT '报告类型: gut_flora/dan/physical_exam/tongue',
    config JSON NOT NULL COMMENT '模板配置JSON: fixedBlocks+freeDisplay',
    is_active TINYINT DEFAULT 1 COMMENT '0=停用 1=启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_report_type (report_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告展示模板';

-- 报告解析器导入记录表
CREATE TABLE IF NOT EXISTS report_parser_import (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(100) NOT NULL COMMENT '导入包名称',
    package_file_url VARCHAR(500) DEFAULT NULL COMMENT '导入包文件路径',
    fingerprint_json TEXT COMMENT '指纹规则JSON',
    parser_script TEXT COMMENT '解析器脚本内容',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/approved/rejected',
    imported_by BIGINT DEFAULT NULL COMMENT '导入人ID',
    reviewed_by BIGINT DEFAULT NULL COMMENT '审核人ID',
    review_note VARCHAR(500) DEFAULT NULL COMMENT '审核意见',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告解析器导入记录表';

-- ===== CF值（平台积分）体系 =====

CREATE TABLE IF NOT EXISTS user_platform_balance (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT       NOT NULL COMMENT '用户ID',
    family_id     BIGINT       COMMENT '归属家庭ID',
    total_earned  INT DEFAULT 0 COMMENT '累计获得',
    available     INT DEFAULT 0 COMMENT '可用余额',
    frozen        INT DEFAULT 0 COMMENT '冻结(提现审核中)',
    withdrawn     INT DEFAULT 0 COMMENT '已提现',
    exchanged     INT DEFAULT 0 COMMENT '已兑换',
    updated_at    DATETIME,
    created_at    DATETIME,
    UNIQUE KEY uk_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户CF值余额';

CREATE TABLE IF NOT EXISTS platform_balance_log (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT       NOT NULL COMMENT '用户ID',
    family_id     BIGINT       COMMENT '归属家庭ID',
    type          VARCHAR(20)  NOT NULL COMMENT 'earn/spend/freeze/unfreeze/withdraw',
    amount        INT          NOT NULL COMMENT '变动数量',
    balance_after INT          NOT NULL COMMENT '变动后可用余额',
    ref_type      VARCHAR(30)  COMMENT 'product_order/activity/withdrawal/migration',
    ref_id        BIGINT       COMMENT '关联业务ID',
    remark        VARCHAR(255) COMMENT '备注',
    created_at    DATETIME,
    INDEX idx_user_time (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CF值流水';



-- ===== 家庭平台积分池（CF值家庭维度）=====

CREATE TABLE IF NOT EXISTS family_platform_balance (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id     BIGINT       NOT NULL COMMENT '家庭ID',
    total_earned  INT DEFAULT 0 COMMENT '累计获得',
    available     INT DEFAULT 0 COMMENT '可用余额',
    frozen        INT DEFAULT 0 COMMENT '冻结(提现审核中)',
    withdrawn     INT DEFAULT 0 COMMENT '已提现',
    exchanged     INT DEFAULT 0 COMMENT '已兑换(抵扣/积分消耗)',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_family (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭平台积分池余额';

CREATE TABLE IF NOT EXISTS family_platform_balance_log (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id     BIGINT       NOT NULL COMMENT '家庭ID',
    type          VARCHAR(20)  NOT NULL COMMENT 'earn/spend/freeze/unfreeze/withdraw/adjust/migration',
    amount        INT          NOT NULL COMMENT '变动数量',
    balance_after INT          NOT NULL COMMENT '变动后可用余额',
    ref_type      VARCHAR(50)  COMMENT 'product_order/referral_dist/activity/withdrawal/adjust/migration/refund',
    ref_id        BIGINT       COMMENT '关联业务ID',
    remark        VARCHAR(255) COMMENT '备注',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family (family_id),
    INDEX idx_ref (ref_type, ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭平台积分流水';

-- ===== 家庭平台积分兑换记录 =====

CREATE TABLE IF NOT EXISTS family_platform_exchange_record (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT       NOT NULL COMMENT '操作用户ID',
    family_id     BIGINT       NOT NULL COMMENT '家庭ID',
    coupon_id     BIGINT       NOT NULL COMMENT '关联优惠券ID',
    coupon_name   VARCHAR(200) COMMENT '优惠券名称',
    cf_cost       INT          NOT NULL COMMENT '消耗CF值数量',
    status        VARCHAR(20)  NOT NULL DEFAULT 'completed' COMMENT 'completed/failed',
    redeem_code   VARCHAR(32)  COMMENT '兑换码',
    remark        VARCHAR(255) COMMENT '备注',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_family_time (family_id, created_at),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭CF值兑换记录';

-- ===== 健康方案汇总 =====

CREATE TABLE IF NOT EXISTS health_plans (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id     BIGINT       NOT NULL COMMENT '家庭ID',
    member_ids    VARCHAR(200) NOT NULL COMMENT '目标成员ID列表(逗号分隔)',
    dimensions    VARCHAR(100) NOT NULL COMMENT '维度列表(逗号分隔)',
    goal          TEXT         NOT NULL COMMENT '用户输入的目标',
    plan_content  TEXT         COMMENT 'LangGraph生成的方案内容',
    member_name   VARCHAR(50)  COMMENT '成员名称摘要',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    plan_json     TEXT         COMMENT '结构化方案内容(JSON)',
    status        VARCHAR(20)  DEFAULT 'draft' COMMENT '方案状态: draft(草稿)/confirmed(已确认)',
    reviewed_by   BIGINT DEFAULT NULL COMMENT '审核人ID（规划师/营养师）',
    reviewed_at   DATETIME DEFAULT NULL COMMENT '审核时间',
    review_comment TEXT DEFAULT NULL COMMENT '审核意见',
    teacher_id    BIGINT DEFAULT NULL COMMENT '指定规划师ID（用户选择）',
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_family (family_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康方案汇总记录';

-- 定期调研模板
CREATE TABLE IF NOT EXISTS survey_templates (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(100) NOT NULL COMMENT '调研标题',
    dimension     VARCHAR(20) COMMENT '适用维度(身/智/富/行/心)',
    questions_json TEXT COMMENT '题目JSON(含选项)',
    cycle_type    VARCHAR(20) DEFAULT 'weekly' COMMENT '周期: weekly/biweekly/monthly',
    ai_generated  TINYINT(1) DEFAULT 0 COMMENT '是否AI生成',
    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 survey_records (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id     BIGINT NOT NULL COMMENT '家庭ID',
    member_id     BIGINT NOT NULL COMMENT '成员ID',
    template_id   BIGINT NOT NULL COMMENT '调研模板ID',
    answers_json  TEXT COMMENT '答案JSON',
    submitted_at  DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间',
    period_start  DATE COMMENT '周期开始',
    period_end    DATE COMMENT '周期结束',
    INDEX idx_member (member_id),
    INDEX idx_template (template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定期调研记录';

-- 报告通用展示块（迁移211）
CREATE TABLE IF NOT EXISTS report_blocks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_type VARCHAR(32) NOT NULL COMMENT 'gut_flora/dan/physical_exam/tongue',
    report_id   BIGINT NOT NULL COMMENT '各类型报告主键ID',
    blocks      JSON NOT NULL COMMENT '块数组 [{type,title,items,extra}]',
    version     INT DEFAULT 1,
    created_at  DATETIME,
    updated_at  DATETIME,
    UNIQUE KEY uk_report_type_id (report_type, report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告通用展示块';

-- 营养产品分类（迁移214）
CREATE TABLE IF NOT EXISTS nutrition_category (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '分类名',
    parent_id BIGINT DEFAULT NULL COMMENT '父分类ID',
    sort_order INT DEFAULT 0 COMMENT '排序',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养产品分类';

-- 营养产品品牌（迁移215）
CREATE TABLE IF NOT EXISTS nutrition_brand (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '品牌名',
    logo_url VARCHAR(500) DEFAULT NULL COMMENT '品牌Logo',
    description TEXT COMMENT '品牌简介',
    country VARCHAR(50) DEFAULT NULL COMMENT '产地国',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养产品品牌';

-- 营养产品主表（迁移216）
CREATE TABLE IF NOT EXISTS nutrition_product (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL COMMENT '产品名称',
    brand_id BIGINT DEFAULT NULL COMMENT '品牌ID',
    category_id BIGINT DEFAULT NULL COMMENT '分类ID',
    sub_category VARCHAR(50) DEFAULT NULL COMMENT '子分类',
    form VARCHAR(30) DEFAULT NULL COMMENT '剂型',
    net_content VARCHAR(50) DEFAULT NULL COMMENT '净含量',
    shelf_life VARCHAR(50) DEFAULT NULL COMMENT '保质期',
    suitable_for TEXT DEFAULT NULL COMMENT '适用人群',
    ingredients TEXT DEFAULT NULL COMMENT '成份/配料表（从详情页图片提取）',
    image_url VARCHAR(500) DEFAULT NULL COMMENT '产品图',
    description TEXT COMMENT '产品描述',
    status TINYINT DEFAULT 1 COMMENT '状态 0下架/1上架',
    local_product_id BIGINT DEFAULT NULL COMMENT '本平台商品ID，关联products.id',
    price DECIMAL(10,2) DEFAULT 0 COMMENT '当前售价',
    original_price INT DEFAULT 0 COMMENT '原价（整数）',
    sold_num INT DEFAULT 0 COMMENT '已售数量',
    source_platform VARCHAR(50) DEFAULT '' COMMENT '来源平台',
    source_id VARCHAR(100) DEFAULT '' COMMENT '来源平台商品ID',
    source_url VARCHAR(500) DEFAULT '' COMMENT '来源平台商品链接',
    product_images TEXT COMMENT '商品图片URL（JSON数组）',
    spec VARCHAR(200) DEFAULT '' COMMENT '规格',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_brand (brand_id),
    INDEX idx_category (category_id),
    INDEX idx_source (source_platform, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养产品';

-- 营养产品成分明细（迁移217）
CREATE TABLE IF NOT EXISTS nutrition_ingredient (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '产品ID',
    ingredient_type VARCHAR(20) COMMENT '成分类型',
    name VARCHAR(100) NOT NULL COMMENT '成分名',
    strain VARCHAR(100) DEFAULT NULL COMMENT '菌株号',
    amount_per_serving DECIMAL(10,2) DEFAULT NULL COMMENT '每份含量',
    unit VARCHAR(20) DEFAULT NULL COMMENT '单位',
    daily_value VARCHAR(50) DEFAULT NULL COMMENT '每日参考值%',
    purpose VARCHAR(200) DEFAULT NULL COMMENT '功效说明',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养产品成分明细';

-- 营养产品平台在售信息（迁移218）
CREATE TABLE IF NOT EXISTS nutrition_platform_listing (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '产品ID',
    platform VARCHAR(30) COMMENT '平台',
    platform_url VARCHAR(500) DEFAULT NULL COMMENT '商品链接',
    current_price DECIMAL(10,2) DEFAULT NULL COMMENT '当前价格',
    original_price DECIMAL(10,2) DEFAULT NULL COMMENT '原价/划线价',
    price_unit VARCHAR(30) DEFAULT NULL COMMENT '价格单位',
    price_per_unit DECIMAL(10,2) DEFAULT NULL COMMENT '每单位价格',
    sales_count INT DEFAULT NULL COMMENT '销量',
    stock_status VARCHAR(20) DEFAULT NULL COMMENT '库存状态',
    last_checked DATETIME DEFAULT NULL COMMENT '最后爬取时间',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product (product_id),
    INDEX idx_platform (platform)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养产品平台在售信息';

-- 营养产品评价（迁移219）
CREATE TABLE IF NOT EXISTS nutrition_product_review (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    platform_listing_id BIGINT NOT NULL COMMENT '平台在售ID',
    source VARCHAR(30) DEFAULT NULL COMMENT '来源平台',
    rating DECIMAL(2,1) DEFAULT NULL COMMENT '评分',
    review_count INT DEFAULT NULL COMMENT '评价数',
    good_rate VARCHAR(10) DEFAULT NULL COMMENT '好评率',
    last_updated DATETIME DEFAULT NULL COMMENT '最后更新时间',
    INDEX idx_listing (platform_listing_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养产品评价';

-- 文件记录表（迁移220：去重 + 维护）
CREATE TABLE IF NOT EXISTS file_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    hash VARCHAR(64) NOT NULL COMMENT 'SHA-256 文件哈希',
    url VARCHAR(500) NOT NULL COMMENT '文件访问URL',
    file_size BIGINT NOT NULL COMMENT '文件大小(字节)',
    content_type VARCHAR(100) DEFAULT NULL COMMENT 'MIME类型',
    original_filename VARCHAR(255) DEFAULT NULL COMMENT '原始文件名',
    category VARCHAR(50) DEFAULT 'general' COMMENT '分类: articles/dan-reports/assessment-reports/health-reports/general',
    file_type VARCHAR(50) DEFAULT NULL COMMENT '文件类型: image/pdf/video/audio/other',
    description VARCHAR(500) DEFAULT NULL COMMENT '描述',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_hash (hash),
    INDEX idx_category (category),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件记录表（去重&维护）';

-- 文件标签字典（迁移222）
CREATE TABLE IF NOT EXISTS file_tag (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '标签名称',
    color VARCHAR(7) DEFAULT '#409EFF' COMMENT '标签颜色(十六进制)',
    sort_order INT DEFAULT 0 COMMENT '排序号',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件标签字典';

-- 文件-标签关联（迁移223）
CREATE TABLE IF NOT EXISTS file_tag_rel (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    file_id BIGINT NOT NULL COMMENT 'file_record.id',
    tag_id BIGINT NOT NULL COMMENT 'file_tag.id',
    UNIQUE KEY uk_file_tag (file_id, tag_id),
    INDEX idx_tag (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件-标签关联';

-- 通关解锁关卡定义
CREATE TABLE IF NOT EXISTS unlock_gates (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    gate_type VARCHAR(32) NOT NULL COMMENT 'SELF_CHECK/INVITE_FAMILY/CREATE_FAMILY_MEMBER/PURCHASE/MEMBERSHIP/REPORT_UPLOAD/MICRO_ACTION/STREAK_DAYS',
    name VARCHAR(50) NOT NULL COMMENT '关卡名称',
    description VARCHAR(200) COMMENT '关卡说明',
    icon VARCHAR(10) COMMENT '图标emoji',
    sort_order INT DEFAULT 0 COMMENT '排序号',
    enabled TINYINT DEFAULT 1 COMMENT '启用:1 禁用:0',
    is_required TINYINT DEFAULT 1 COMMENT '主线:1 增值:0',
    is_soft TINYINT DEFAULT 0 COMMENT '硬门禁:0 软引导:1',
    unlock_target VARCHAR(32) COMMENT 'PERSONAL_SANDBOX/FAMILY_SANDBOX/CHILD_MICRO_ACTION/...',
    condition_params VARCHAR(500) COMMENT '条件参数JSON',
    reward_points INT DEFAULT 0 COMMENT '通关奖励积分',
    reward_energy INT DEFAULT 0 COMMENT '通关奖励能量',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sort (enabled, is_required, is_soft, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通关解锁关卡定义';

-- 家庭关卡通关进度
CREATE TABLE IF NOT EXISTS family_gate_progress (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    gate_id BIGINT NOT NULL COMMENT '关卡ID',
    status VARCHAR(16) DEFAULT 'LOCKED' COMMENT 'LOCKED/UNLOCKED',
    unlocked_by BIGINT COMMENT '解锁人user_id',
    unlocked_at DATETIME COMMENT '解锁时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_family_gate (family_id, gate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭关卡通关进度';

-- 菌群知识库文章管理
CREATE TABLE IF NOT EXISTS microbiome_article (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL COMMENT '文章标题',
    content MEDIUMTEXT COMMENT '文章原文Markdown',
    category VARCHAR(50) DEFAULT '科普' COMMENT '分类',
    tags VARCHAR(500) COMMENT '逗号分隔标签',
    source_url VARCHAR(500) COMMENT '来源URL',
    pub_date VARCHAR(20) COMMENT '发布日期',
    status TINYINT DEFAULT 1 COMMENT '1=启用 0=禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_status (status),
    FULLTEXT INDEX ft_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菌群知识库文章';

-- 后台侧边栏菜单表
CREATE TABLE IF NOT EXISTS sys_menu (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT NOT NULL DEFAULT 0 COMMENT '父菜单ID，0=顶级',
    title VARCHAR(50) COMMENT '目录标题（有子菜单时用，对应 el-submenu）',
    label VARCHAR(50) COMMENT '叶子菜单显示名（对应 el-menu-item）',
    path VARCHAR(200) COMMENT '路由路径（叶子菜单必填，如 /families）',
    icon VARCHAR(100) COMMENT 'Element UI 图标类名，如 el-icon-s-custom',
    perm VARCHAR(100) COMMENT '权限标识，如 family:list / task:* / system:config',
    sort INT DEFAULT 0 COMMENT '排序号，小的在前',
    visible TINYINT DEFAULT 1 COMMENT '是否显示 1=显示 0=隐藏',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台侧边栏菜单表';

-- 角色-菜单授权表
CREATE TABLE IF NOT EXISTS sys_role_menu (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    role VARCHAR(50) NOT NULL COMMENT '角色标识: admin/teacher/nutritionist/article_manager/activity_manager/supplier_admin',
    menu_id BIGINT NOT NULL COMMENT 'sys_menu.id',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_role_menu (role, menu_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色-菜单授权表';





-- =============================================
-- 改版v1: 需求驱动·逐步解锁（问题域/用户意图/站内通知）
-- =============================================

CREATE TABLE IF NOT EXISTS problem_domains (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL COMMENT '唯一编码: weight_loss/sleep/constipation',
  name VARCHAR(50) NOT NULL COMMENT '名称: 减肥/睡眠/便秘',
  icon VARCHAR(255) DEFAULT '' COMMENT '图标',
  description VARCHAR(500) DEFAULT '' COMMENT '一句话描述',
  flow_intro_json TEXT COMMENT '服务流程介绍(7步) JSON',
  survey_template_id BIGINT DEFAULT NULL COMMENT '专项调研问卷模板ID(SurveyTemplate.id)',
  content_tags VARCHAR(500) DEFAULT '' COMMENT '内容标签,逗号分隔(过滤文章/知识库)',
  recommended_product_ids VARCHAR(500) DEFAULT '' COMMENT '推荐产品ID,逗号分隔(Product.id)',
  task_template_package_ids VARCHAR(500) DEFAULT '' COMMENT '套餐绑定的任务模板包ID(TaskTemplatePackage.id)',
  reward_cf_value INT DEFAULT 0 COMMENT '全部完成奖励CF值',
  status TINYINT DEFAULT 1 COMMENT '1启用 0停用',
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问题域定义(改版v1)';

CREATE TABLE IF NOT EXISTS user_intents (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  intent_type VARCHAR(20) DEFAULT '' COMMENT 'A/B/C 用户类型',
  problem_domain_code VARCHAR(50) DEFAULT '' COMMENT '问题域编码(仅C类)',
  journey_stage INT DEFAULT 0 COMMENT '旅程阶段 0-7',
  stage_updated_at DATETIME DEFAULT NULL COMMENT '最近阶段变化时间',
  survey_template_id BIGINT DEFAULT NULL COMMENT '最近作答问卷模板ID',
  survey_submitted_at DATETIME DEFAULT NULL COMMENT '最近问卷提交时间',
  survey_result_json TEXT COMMENT '问卷答案→推荐结果快照',
  skip_count INT DEFAULT 0 COMMENT '跳过意图问卷次数',
  last_skip_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 (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户意图与服务旅程(改版v1)';

CREATE TABLE IF NOT EXISTS user_notifications (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL COMMENT '接收用户ID',
  type VARCHAR(30) DEFAULT 'report_ready' COMMENT 'report_ready/reward_granted/logistics/system',
  title VARCHAR(200) NOT NULL,
  content VARCHAR(1000) DEFAULT '',
  ref_type VARCHAR(30) DEFAULT '' COMMENT '关联对象类型: report/package/task',
  ref_id BIGINT DEFAULT NULL COMMENT '关联对象ID',
  is_read TINYINT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_read (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='站内通知(改版v1)';

-- 通知模板（迁移245 W3）
CREATE TABLE IF NOT EXISTS notification_templates (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(30) NOT NULL COMMENT '通知类型: report_ready/reward_granted/system',
  title VARCHAR(200) NOT NULL COMMENT '默认标题(支持占位符)',
  content VARCHAR(1000) NOT NULL COMMENT '默认内容模板(支持占位符)',
  channels VARCHAR(100) DEFAULT 'in_app' COMMENT '推送通道: in_app,sms,wechat,gzh,phone 逗号分隔',
  status TINYINT DEFAULT 1 COMMENT '1启用 0停用',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知模板(改版v1 W3)';

-- 当前状态调研-健康现状档案（迁移239）
CREATE TABLE IF NOT EXISTS health_status (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '所属账号',
    member_id BIGINT DEFAULT NULL COMMENT '家庭成员ID（NULL=账号级，非空=成员级归档）',
    height_cm DECIMAL(5,1) DEFAULT NULL COMMENT '身高(cm)',
    weight_kg DECIMAL(5,1) DEFAULT NULL COMMENT '体重(kg)',
    blood_pressure VARCHAR(20) DEFAULT NULL COMMENT '血压，如 120/80',
    blood_glucose DECIMAL(4,1) DEFAULT NULL COMMENT '空腹血糖(mmol/L)',
    blood_lipids JSON DEFAULT NULL COMMENT '血脂 [{name,value,unit,note}]',
    disease_history JSON DEFAULT NULL COMMENT '疾病史 [{name,note}]',
    medications JSON DEFAULT NULL COMMENT '在服药物/治疗 [{name,note}]',
    notes VARCHAR(500) DEFAULT NULL COMMENT '其他补充说明',
    filled_at DATETIME DEFAULT NULL COMMENT '首次填写时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_member (user_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='当前状态调研-健康现状档案';

-- 系统提示词管理表（cfc-web 后台可维护，langgraph 动态加载）
CREATE TABLE IF NOT EXISTS system_prompts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    prompt_key VARCHAR(64) NOT NULL COMMENT '提示词键（如 health_coach/analysis/chat）',
    title VARCHAR(100) NOT NULL COMMENT '提示词标题',
    content TEXT NOT NULL COMMENT '提示词内容',
    default_content TEXT COMMENT '出厂默认内容（重置时恢复）',
    version INT NOT NULL DEFAULT 1 COMMENT '版本号',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
    remark VARCHAR(255) COMMENT '备注',
    updated_by VARCHAR(64) COMMENT '更新人',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_prompt_key (prompt_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统提示词管理';

-- 套餐商品组成明细表（套餐 = 商品 productType=bundle，子商品在 product_bundle_items 中配置）
CREATE TABLE IF NOT EXISTS product_bundle_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    bundle_product_id BIGINT NOT NULL COMMENT '套餐商品ID',
    child_product_id BIGINT NOT NULL COMMENT '子商品ID',
    child_sku_id BIGINT DEFAULT NULL COMMENT '子SKU ID（为空时直接扣 product.stock）',
    quantity INT NOT NULL DEFAULT 1 COMMENT '子商品数量',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_bundle (bundle_product_id),
    INDEX idx_child (child_product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='套餐商品组成明细';

-- 联系人表（cfclub 联系人管理）
CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    phone VARCHAR(20) COMMENT '电话',
    avatar VARCHAR(500) COMMENT '头像',
    relationship_type VARCHAR(20) COMMENT '关系类型: family/friend/partner/colleague/other',
    known_since DATETIME COMMENT '认识时间',
    bio VARCHAR(500) COMMENT '简介',
    birthday DATE COMMENT '生日',
    intimacy_level INT DEFAULT 0 COMMENT '亲密度(0-100)',
    contact_source VARCHAR(20) DEFAULT 'manual' COMMENT '来源: phone/manual',
    tags VARCHAR(500) COMMENT '标签(逗号分隔)',
    last_contact_at DATETIME COMMENT '最近联系时间',
    contact_count INT DEFAULT 0 COMMENT '联系次数',
    notes TEXT COMMENT '备注',
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='联系人表';

-- 维度配置表（cfclub 五维能量体系）
CREATE TABLE IF NOT EXISTS product_dimension_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    dimension_code VARCHAR(50) NOT NULL UNIQUE COMMENT '维度代码: body/mind/wisdom/action/wealth',
    dimension_name VARCHAR(100) NOT NULL COMMENT '维度名称',
    dimension_type VARCHAR(50) NOT NULL DEFAULT 'five' COMMENT '类型: five(五维)/action(行动)/other',
    parent_id BIGINT COMMENT '父级ID，无父级为NULL',
    sort INT DEFAULT 0 COMMENT '排序',
    enabled TINYINT(1) DEFAULT 1 COMMENT '启用: 0禁用 1启用',
    dify_dataset_id VARCHAR(100) COMMENT 'Dify Dataset ID，不填则不同步',
    remark VARCHAR(500) 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 dan_knowledge_base (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL COMMENT '知识标题',
    content TEXT NOT NULL COMMENT '知识内容',
    sort INT DEFAULT 0 COMMENT '排序',
    status TINYINT(1) DEFAULT 1 COMMENT '状态: 0禁用 1启用',
    remark VARCHAR(500) 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 dan_knowledge_tag (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(100) NOT NULL UNIQUE COMMENT '标签名',
    tag_type VARCHAR(50) DEFAULT 'custom' COMMENT '标签类型: custom/auto',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';

-- 知识-维度关联表
CREATE TABLE IF NOT EXISTS dan_knowledge_base_dimension (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    knowledge_id BIGINT NOT NULL COMMENT '知识库ID',
    dimension_id BIGINT NOT NULL COMMENT '维度配置ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_knowledge_dimension (knowledge_id, dimension_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='知识-维度关联表';

-- 知识-标签关联表
CREATE TABLE IF NOT EXISTS dan_knowledge_base_tag (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    knowledge_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_knowledge_tag (knowledge_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='知识-标签关联表';

-- 用户画像最新快照表
CREATE TABLE IF NOT EXISTS profile_snapshot (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL UNIQUE COMMENT '家庭成员ID',
    dimension_scores JSON DEFAULT NULL COMMENT '五维能力评分',
    body_metrics JSON DEFAULT NULL COMMENT '近30天身体指标',
    mind_metrics JSON DEFAULT NULL COMMENT '近30天心理指标',
    wisdom_metrics JSON DEFAULT NULL COMMENT '最近测评成绩',
    action_metrics JSON DEFAULT NULL COMMENT '近7天行为指标',
    wealth_metrics JSON DEFAULT NULL COMMENT '近30天财商指标',
    problem_domains VARCHAR(500) DEFAULT NULL COMMENT '关注的问题域标签',
    computed_at DATETIME DEFAULT NULL COMMENT '最后一次计算时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户画像最新快照';

-- 画像历史表（保留365天）
CREATE TABLE IF NOT EXISTS profile_history (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    snapshot_date DATE NOT NULL COMMENT '日期',
    all_metrics JSON NOT NULL COMMENT '当日所有指标完整快照',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_member_date (member_id, snapshot_date),
    INDEX idx_date (snapshot_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户画像历史快照';

-- 健康启航计划购买订单表
CREATE TABLE IF NOT EXISTS purchase_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    family_id BIGINT NOT NULL,
    amount INT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    payment_method VARCHAR(20),
    transaction_id VARCHAR(64),
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    is_early_renewal TINYINT DEFAULT 0,
    cf_reward_given INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME,
    INDEX idx_user_id (user_id),
    INDEX idx_family_id (family_id),
    INDEX idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 闯关任务进度表
CREATE TABLE IF NOT EXISTS challenge_progress (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    purchase_id BIGINT NOT NULL,
    member_id BIGINT NOT NULL,
    week_num INT NOT NULL,
    task_type VARCHAR(50) NOT NULL,
    task_key VARCHAR(100) NOT NULL,
    task_title VARCHAR(255) NOT NULL,
    target_value INT DEFAULT 0,
    current_value INT DEFAULT 0,
    reward_cf INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    completed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_purchase_id (purchase_id),
    INDEX idx_member_id (member_id),
    UNIQUE KEY uk_member_task (member_id, task_key, week_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 先天画像体系（迁移270-272）
-- =============================================

-- 先天画像来源权重配置表
CREATE TABLE IF NOT EXISTS innate_portrait_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    source_type VARCHAR(20) NOT NULL COMMENT '来源: zodiac/bazi/blood/numsoul',
    source_name VARCHAR(50) NOT NULL COMMENT '来源名称',
    weight INT DEFAULT 25 COMMENT '权重(基点,2500=25%)',
    enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    ai_enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用AI解读',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_source (source_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='先天画像来源权重配置';

-- 先天画像报告缓存表
CREATE TABLE IF NOT EXISTS innate_portrait_report (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '成员ID',
    member_type VARCHAR(10) NOT NULL COMMENT '成员类型: child/parent',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    portrait_json TEXT COMMENT '先天画像数据JSON',
    ai_reading TEXT COMMENT 'AI解读文案',
    generated_at DATETIME COMMENT '生成时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_member (member_id, member_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='先天画像报告缓存';

-- 数字能量详细配置表
CREATE TABLE IF NOT EXISTS numsoul_detail_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    number_type VARCHAR(20) NOT NULL COMMENT '类型: life_path/talent/birthday/destiny',
    number_value INT NOT NULL COMMENT '数字值 1-9 或主数 11/22/33',
    title VARCHAR(50) COMMENT '称号',
    keywords VARCHAR(200) COMMENT '性格关键词',
    mind_base INT DEFAULT 0 COMMENT '心先天基础分(百分位)',
    mind_advice TEXT COMMENT '心维度成长建议',
    color_hex VARCHAR(10) COMMENT '代表色',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_type_num (number_type, number_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数字能量详细配置';

-- ============================================================
-- 五维自检忽略提醒记录表（迁移270）
-- ============================================================
CREATE TABLE IF NOT EXISTS self_check_ignores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    check_id BIGINT COMMENT '忽略的自检记录ID(可空)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '忽略时间',
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='五维自检忽略提醒记录';