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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 套餐购买订单
CREATE TABLE IF NOT EXISTS package_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) UNIQUE NOT NULL,
    user_id BIGINT NOT NULL COMMENT '购买者用户ID',
    family_id BIGINT NOT NULL COMMENT '家庭ID',
    package_id BIGINT NOT NULL COMMENT '套餐ID',
    package_name VARCHAR(100) COMMENT '套餐名称',
    price INT NOT NULL COMMENT '支付金额(分)',
    platform_fee INT DEFAULT 0 COMMENT '平台服务费(分)',
    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 COMMENT '验证码类型',
    expires_in INT DEFAULT 300 COMMENT '过期时间(秒)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    expires_at DATETIME NOT NULL COMMENT '过期时间',
    used TINYINT DEFAULT 0 COMMENT '是否已使用:0否1是',
    INDEX idx_phone_type (phone, type),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='验证码表';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- =============================================
-- 佣金系统表
-- =============================================

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- ============================================================
-- 购物车表
-- ============================================================
CREATE TABLE IF NOT EXISTS cart (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    quantity INT NOT NULL DEFAULT 1 COMMENT '数量',
    sku_id BIGINT DEFAULT NULL COMMENT 'SKU ID',
    spec_option_ids VARCHAR(500) DEFAULT NULL COMMENT '规格选项ID列表JSON',
    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 '描述',
    models TEXT COMMENT 'AI模型列表JSON，如["agnes-image-2.0-flash","agnes-2.5-flash"]',
    status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active/disabled',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_admin_id (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商体系';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 家庭访问安全日志表
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 '五行提示',
    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='成员年度能量表';

-- 商品推荐日志表（迁移 80）
CREATE TABLE IF NOT EXISTS product_recommendation_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '用户 ID',
    product_id BIGINT COMMENT '商品 ID',
    scene VARCHAR(32) COMMENT '触发场景：report_upload/manual/chat',
    reason VARCHAR(255) COMMENT '推荐理由',
    match_score DOUBLE COMMENT '匹配分',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品推荐日志';

-- 商品维度关联表（迁移 81）
CREATE TABLE IF NOT EXISTS product_dimension_mapping (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT NOT NULL COMMENT '商品 ID',
    dimension_code VARCHAR(32) NOT NULL COMMENT '维度: body/wisdom/mind/action/wealth',
    match_score INT DEFAULT 100 COMMENT '匹配度 0-100',
    match_reason VARCHAR(200) COMMENT '匹配原因',
    tags VARCHAR(500) COMMENT '推荐标签 JSON',
    enabled TINYINT DEFAULT 1,
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_product (product_id),
    INDEX idx_dimension (dimension_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品维度关联表';

-- 复购提醒发送记录表（迁移 82）
CREATE TABLE IF NOT EXISTS repurchase_reminder_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    order_id BIGINT COMMENT '关联订单 ID',
    reminder_days INT DEFAULT 30,
    sent_at DATETIME,
    clicked TINYINT DEFAULT 0,
    purchased TINYINT DEFAULT 0,
    INDEX idx_user_pending (user_id, purchased)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='复购提醒发送记录';

-- 复购提醒配置表（迁移 83）
CREATE TABLE IF NOT EXISTS repurchase_reminder_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_category VARCHAR(100),
    product_id BIGINT COMMENT '特定商品 ID（优先于 category）',
    reminder_days INT DEFAULT 30,
    reminder_template VARCHAR(500) COMMENT '提醒话术模板',
    max_reminders INT DEFAULT 3,
    enabled TINYINT DEFAULT 1,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='复购提醒配置表';

-- products 表添加推荐相关字段（迁移 81-83）
ALTER TABLE products ADD COLUMN IF NOT EXISTS recommendation_tags VARCHAR(500) COMMENT '推荐标签 JSON' AFTER domain;
ALTER TABLE products ADD COLUMN IF NOT EXISTS purchase_count_threshold INT DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS repurchase_interval_days INT DEFAULT 30;

-- 社交圈/珍珠表（迁移 85）
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) NOT NULL COMMENT '匹配源: article/activity/game/assessment/product/health_report/teacher',
  source_id BIGINT COMMENT '匹配源ID',
  member_count INT DEFAULT 0 COMMENT '成员数',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_type (type),
  INDEX idx_match_source (match_source),
  INDEX idx_source_id (source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='社交圈/珍珠';

-- 社交圈成员表（迁移 85）
CREATE TABLE IF NOT EXISTS social_circle_member (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  circle_id BIGINT NOT NULL,
  member_id BIGINT NOT NULL COMMENT '用户ID',
  member_type VARCHAR(20) NOT NULL COMMENT 'parent/child',
  joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_circle_member (circle_id, member_id),
  INDEX idx_member (member_id, member_type),
  INDEX idx_circle_id (circle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='圈子成员';

-- AI对话摘要
CREATE TABLE IF NOT EXISTS ai_conversation_summary (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  conversation_id VARCHAR(100) NOT NULL COMMENT 'Dify会话ID',
  summary_text TEXT COMMENT '对话摘要文本',
  summary_tokens INT DEFAULT 0 COMMENT '摘要token数',
  message_count INT DEFAULT 0 COMMENT '本次对话消息数',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  INDEX idx_conversation (conversation_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI对话摘要';

-- AI用户关键事实
CREATE TABLE IF NOT EXISTS ai_user_facts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  fact_key VARCHAR(100) NOT NULL COMMENT '事实键',
  fact_value VARCHAR(500) COMMENT '事实值',
  source_conversation VARCHAR(100) COMMENT '来源会话ID',
  confidence DECIMAL(3,2) DEFAULT 1.00 COMMENT '置信度0-1',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_user_key (user_id, fact_key),
  INDEX idx_conversation (source_conversation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI用户关键事实';

CREATE TABLE IF NOT EXISTS supply_hierarchy (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    system_id BIGINT NOT NULL COMMENT '供应商体系ID',
    parent_id BIGINT NOT NULL COMMENT '上级供应商ID',
    child_id BIGINT NOT NULL COMMENT '下级供应商ID',
    level INT DEFAULT 1 COMMENT '层级深度',
    status VARCHAR(20) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_system_parent_child (system_id, parent_id, child_id),
    KEY idx_system_child (system_id, child_id),
    KEY idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商上下级关系表';

CREATE TABLE IF NOT EXISTS supply_hierarchy_change_request (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    system_id BIGINT NOT NULL,
    applicant_id BIGINT NOT NULL COMMENT '申请人ID',
    target_parent_id BIGINT NOT NULL COMMENT '目标上级ID',
    status VARCHAR(20) DEFAULT 'pending',
    reason VARCHAR(500),
    reject_reason VARCHAR(500),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reviewed_at DATETIME,
    reviewed_by BIGINT,
    KEY idx_system_applicant (system_id, applicant_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商更换上级申请表';

CREATE TABLE IF NOT EXISTS butler_profiles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '用户ID',
    tier VARCHAR(10) COMMENT '服务等级: L1/L2',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/approved/rejected',
    member_count INT DEFAULT 0 COMMENT '当前服务家庭数',
    max_members INT DEFAULT 50 COMMENT '最大服务家庭上限',
    annual_fee_l1 BIGINT COMMENT '年费L1档(P点)',
    annual_fee_l2 BIGINT COMMENT '年费L2档(P点)',
    description TEXT COMMENT '个人简介',
    certificates TEXT COMMENT '资质证书URL(JSON数组)',
    reject_reason VARCHAR(500) COMMENT '审核拒绝原因',
    commission_rate_l1 INT DEFAULT 1000 COMMENT '年度分佣比例L1(basis points,10000=100%)',
    commission_rate_l2 INT DEFAULT 5000 COMMENT '年度分佣比例L2(basis points,10000=100%)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    approved_at DATETIME COMMENT '审核通过时间',
    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 COMMENT '管家用户ID',
    member_user_id BIGINT NOT NULL COMMENT '会员用户ID',
    subscription_id BIGINT COMMENT '关联订阅记录ID(年费服务时)',
    service_type VARCHAR(20) NOT NULL COMMENT '服务类型: annual/per_session',
    amount_p BIGINT NOT NULL COMMENT '服务金额(P点)',
    platform_fee_p BIGINT DEFAULT 0 COMMENT '平台留成(P点)',
    butler_amount_p BIGINT DEFAULT 0 COMMENT '管家所得(P点)',
    member_tier VARCHAR(10) COMMENT '会员订阅级别: L1/L2',
    year_month VARCHAR(7) COMMENT '结算年月 YYYY-MM',
    remark VARCHAR(500) COMMENT '备注',
    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),
    INDEX idx_service_type (service_type)
) 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 COMMENT '管家用户ID',
    year_month VARCHAR(7) NOT NULL COMMENT '结算年月 YYYY-MM',
    member_count INT DEFAULT 0 COMMENT '本月服务家庭数',
    annual_base_amount_p BIGINT DEFAULT 0 COMMENT '年费基准总额(P点)',
    annual_butler_amount_p BIGINT DEFAULT 0 COMMENT '年费-管家所得(P点)',
    annual_platform_amount_p BIGINT DEFAULT 0 COMMENT '年费-平台留成(P点)',
    per_session_total_p BIGINT DEFAULT 0 COMMENT '按次服务总金额(P点)',
    per_session_platform_total_p BIGINT DEFAULT 0 COMMENT '按次-平台留成(P点)',
    per_session_butler_total_p BIGINT DEFAULT 0 COMMENT '按次-管家所得(P点)',
    total_butler_amount_p BIGINT DEFAULT 0 COMMENT '本月管家总所得(P点)',
    status VARCHAR(20) DEFAULT 'pending' COMMENT '结算状态: pending/paid',
    settled_at DATETIME COMMENT '确认发放时间',
    settled_by BIGINT COMMENT '结算确认人',
    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 family_earnings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL UNIQUE COMMENT '家庭ID',
    total_earned BIGINT NOT NULL DEFAULT 0 COMMENT '累计推荐收入（分）',
    available_amount BIGINT NOT NULL DEFAULT 0 COMMENT '当前可支配余额（分）',
    withdrawn_amount BIGINT NOT NULL DEFAULT 0 COMMENT '已提现金额（分）',
    distributed_amount BIGINT NOT NULL DEFAULT 0 COMMENT '已颁发给成员的金额（分）',
    updated_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭公共账户';

CREATE TABLE family_earnings_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    allocated_amount BIGINT NOT NULL DEFAULT 0 COMMENT '管理员累计分配（分）',
    consumed_amount BIGINT NOT NULL DEFAULT 0 COMMENT '已消费金额（分）',
    available_amount BIGINT NOT NULL DEFAULT 0 COMMENT '可消费余额=allocated-consumed（分）',
    can_use_family_balance TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否允许使用家庭公共池',
    updated_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_family_user (family_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭成员已分配余额';

CREATE TABLE family_earnings_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT NOT NULL,
    type VARCHAR(20) NOT NULL COMMENT '类型: earn收入/withdraw提现/distribute颁发/consume消费',
    amount BIGINT NOT NULL COMMENT '金额（分）',
    from_user_id BIGINT COMMENT '操作人',
    to_user_id BIGINT COMMENT '受益人（颁发/消费时填写）',
    note VARCHAR(255) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭收益收支流水';

ALTER TABLE commission_records ADD COLUMN family_id BIGINT COMMENT '归属家庭ID';


-- ========== Schema.sql 补充: 从 DatabaseInitializer 同步的表 ==========

-- after_sales_requests

CREATE TABLE IF NOT EXISTS after_sales_requests (id BIGINT AUTO_INCREMENT PRIMARY KEY,order_id BIGINT NOT NULL COMMENT '订单ID',order_no VARCHAR(32) NOT NULL COMMENT '订单号',user_id BIGINT NOT NULL COMMENT '用户ID',type VARCHAR(32) NOT NULL COMMENT '类型: REFUND/RETURN_REFUND',reason VARCHAR(255) COMMENT '原因',description TEXT COMMENT '描述',images TEXT COMMENT '凭证图片(JSON数组)',refund_amount INT DEFAULT 0 COMMENT '退款金额(分)',status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/approved/rejected/completed',reject_reason VARCHAR(255) COMMENT '驳回原因',handled_by BIGINT COMMENT '处理人ID',handled_at DATETIME COMMENT '处理时间',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_order (order_id),INDEX idx_user (user_id),INDEX idx_status (status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='售后申请';

-- article_reading_records

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

-- bazi_reading_template

CREATE TABLE IF NOT EXISTS bazi_reading_template (id BIGINT AUTO_INCREMENT PRIMARY KEY, element VARCHAR(20) NOT NULL COMMENT '五行: wood/fire/earth/metal/water', condition VARCHAR(20) NOT NULL COMMENT '状态: strong/weak/balanced', personal_trait TEXT COMMENT '性格特质描述', mind_growth_advice TEXT COMMENT '心理成长建议', daily_tip_template TEXT COMMENT '每日宜忌模板', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_element_condition (element, condition)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='五行命理解读模板';

-- cart_items

CREATE TABLE IF NOT EXISTS cart_items (id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL COMMENT '用户ID',product_id BIGINT NOT NULL COMMENT '商品ID',quantity INT DEFAULT 1 COMMENT '数量',selected TINYINT(1) DEFAULT 1 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),UNIQUE KEY uk_user_product (user_id, product_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车';

-- contacts

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 '备注', family_member_id BIGINT DEFAULT NULL COMMENT '关联的家庭成员ID', invited_status VARCHAR(16) DEFAULT 'none' COMMENT '邀请加入家庭状态: none/invited/accepted/declined', created_at DATETIME, updated_at DATETIME, INDEX idx_user_id (user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='联系人表';

-- family_member_logs

CREATE TABLE IF NOT EXISTS family_member_logs (id BIGINT AUTO_INCREMENT PRIMARY KEY, family_id BIGINT NOT NULL COMMENT '家庭ID', member_id BIGINT NOT NULL COMMENT '成员ID', action VARCHAR(32) NOT NULL COMMENT '操作: join/leave/update/kick', old_value VARCHAR(500) COMMENT '旧值', new_value VARCHAR(500) COMMENT '新值', operator_id BIGINT COMMENT '操作人用户ID', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_family_id (family_id), INDEX idx_member_id (member_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='家庭成员变更日志';

-- dan_knowledge_base

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='知识库表';

-- dan_knowledge_base_dimension

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='知识-维度关联表';

-- dan_knowledge_base_tag

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='知识-标签关联表';

-- dan_knowledge_tag

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='标签表';

-- distribution_relations

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(null=根)',depth INT DEFAULT 0 COMMENT '层级深度',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='销售关系';

-- distribution_systems

CREATE TABLE IF NOT EXISTS distribution_systems (id BIGINT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64) NOT NULL COMMENT '体系名称',description VARCHAR(255) COMMENT '描述',owner_id BIGINT COMMENT '创建者ID',profit_rate INT DEFAULT 0 COMMENT '平台利润率(千分比)',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='销售体系';

-- emotion_checkin

CREATE TABLE IF NOT EXISTS emotion_checkin (id BIGINT AUTO_INCREMENT PRIMARY KEY, child_id BIGINT NOT NULL COMMENT '孩子ID', user_id BIGINT NOT NULL COMMENT '打卡人用户ID', emotion_type INT NOT NULL COMMENT '情绪类型: 1快乐/2平和平静/3平静/4有点不舒服/5难受/6烦躁/7生气/8焦虑/9恐惧', emotion_name VARCHAR(20) COMMENT '情绪名称: happy/calm/peaceful/uncomfortable/sad/irritable/angry/anxious/fearful', energy_level INT NOT NULL DEFAULT 3 COMMENT '能量状态: 1很低/2低/3中等/4高/5很高', note TEXT COMMENT '打卡备注', photo_url VARCHAR(500) COMMENT '照片URL', favorite_thing VARCHAR(500) COMMENT '今日最喜欢的一件事', points_awarded INT DEFAULT 0 COMMENT '奖励积分', parent_visible TINYINT DEFAULT 1 COMMENT '家长可见: 1可见/0不可见', checkin_date DATE NOT NULL 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_user_id (user_id), INDEX idx_checkin_date (checkin_date), INDEX idx_child_date (child_id, checkin_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='情绪打卡表';

-- health_checkins

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='健康打卡表';

-- health_disease_risk

CREATE TABLE IF NOT EXISTS health_disease_risk (id BIGINT AUTO_INCREMENT PRIMARY KEY, report_id BIGINT NOT NULL COMMENT '关联报告ID', disease_name VARCHAR(100) NOT NULL COMMENT '疾病名称', risk_value VARCHAR(50) COMMENT '风险值(0-1)', risk_level VARCHAR(20) COMMENT '风险等级: 高风险/需注意/低风险', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_report (report_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='疾病风险评估数据';

-- health_gut_flora

CREATE TABLE IF NOT EXISTS health_gut_flora (id BIGINT AUTO_INCREMENT PRIMARY KEY, 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(20) COMMENT '状态: 正常/偏低/偏高', category VARCHAR(50) COMMENT '分类: 有益菌/有害菌/中性菌', sort_order INT DEFAULT 0 COMMENT '排序', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_report (report_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菌群检测专项数据';

-- health_report_survey

CREATE TABLE IF NOT EXISTS health_report_survey (id BIGINT AUTO_INCREMENT PRIMARY KEY, report_id BIGINT NOT NULL COMMENT '关联报告ID', user_id BIGINT NOT NULL COMMENT '用户ID', child_id BIGINT NOT NULL COMMENT '孩子ID', survey_data JSON COMMENT '调研问卷结果JSON', status VARCHAR(20) DEFAULT 'pending' COMMENT '状态: pending/completed', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_report (report_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告调研问卷';

-- numsoul_config

CREATE TABLE IF NOT EXISTS numsoul_config (number INT PRIMARY KEY COMMENT '灵数1-9', title VARCHAR(50) NOT NULL COMMENT '称号', keywords VARCHAR(200) COMMENT '性格关键词', mind_growth_advice TEXT COMMENT '心理成长建议', recommended_article_tags VARCHAR(200) COMMENT '关联文章标签', color_hex VARCHAR(7) COMMENT '代表色' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生命灵数配置';

-- nutrition_deficiency_record

CREATE TABLE IF NOT EXISTS nutrition_deficiency_record (id BIGINT AUTO_INCREMENT PRIMARY KEY,child_id BIGINT NOT NULL COMMENT '孩子ID',report_id BIGINT NOT NULL COMMENT '关联报告ID',nutrient VARCHAR(50) NOT NULL COMMENT '营养素名',deficiency_type VARCHAR(20) NOT NULL COMMENT 'deficiency/excess/borderline/unclassified',severity DECIMAL(5,2) DEFAULT 0 COMMENT '严重程度(0-1)',indicator_name VARCHAR(100) COMMENT '关联指标名',indicator_value VARCHAR(50) COMMENT '指标检测值',ref_range VARCHAR(100) COMMENT '参考范围',symptoms TEXT COMMENT '症状描述',food_suggestions TEXT COMMENT '推荐食物',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_child (child_id),INDEX idx_report (report_id),INDEX idx_nutrient (nutrient)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='营养缺乏分析记录';

-- nutrition_indicator_mapping

CREATE TABLE IF NOT EXISTS nutrition_indicator_mapping (id BIGINT AUTO_INCREMENT PRIMARY KEY,indicator_name_pattern VARCHAR(200) NOT NULL COMMENT '指标名匹配模式(SQL LIKE语法,%为通配符)',nutrient VARCHAR(50) NOT NULL COMMENT '关联营养素名',severity_weight DECIMAL(3,2) DEFAULT 1.00 COMMENT '权重(0-1)',deficiency_symptoms TEXT COMMENT '缺乏症状描述',excess_symptoms TEXT COMMENT '过量症状描述',food_suggestions TEXT COMMENT '推荐食物(逗号分隔)',sort_order INT DEFAULT 0 COMMENT '排序(大值优先匹配)',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_nutrient (nutrient)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='指标-营养素映射表';

-- product_dimension_config

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='维度配置表';

-- promotion_tier_config

CREATE TABLE IF NOT EXISTS promotion_tier_config (id BIGINT AUTO_INCREMENT PRIMARY KEY, tier_code VARCHAR(10) NOT NULL UNIQUE COMMENT '等级编码: R0/R1/R2/R3/R4', tier_name VARCHAR(50) NOT NULL COMMENT '等级名称', min_team_size_1st INT NOT NULL DEFAULT 0 COMMENT '一级团队人数下限', min_team_size_2nd INT NOT NULL DEFAULT 0 COMMENT '二级团队人数下限', min_total_referral_earnings INT NOT NULL DEFAULT 0 COMMENT '累计推荐佣金下限(分)', min_total_share_earnings INT NOT NULL DEFAULT 0 COMMENT '累计消费返利下限(分)', commission_rate_l1 INT NOT NULL DEFAULT 0 COMMENT 'L1佣金比例(%)', commission_rate_l2 INT NOT NULL DEFAULT 0 COMMENT 'L2佣金比例(%)', sort_order INT NOT NULL DEFAULT 0 COMMENT '排序', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广等级阈值配置';

-- shop_categories

CREATE TABLE IF NOT EXISTS shop_categories (id BIGINT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64) NOT NULL COMMENT '分类名称',parent_id BIGINT DEFAULT 0 COMMENT '父分类ID(0=根节点)',level INT DEFAULT 0 COMMENT '层级(0/1/2/3)',sort INT DEFAULT 0 COMMENT '排序',image VARCHAR(512) COMMENT '分类图标',enabled TINYINT(1) DEFAULT 1 COMMENT '启用',dimension_codes VARCHAR(128) COMMENT '维度编码(逗号分隔: body,mind,heart)',product_types VARCHAR(128) COMMENT '商品类型(逗号分隔: PHYSICAL,COURSE,ASSESSMENT)',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_parent (parent_id),INDEX idx_sort (sort)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品类目';

-- shop_discount_rules

CREATE TABLE IF NOT EXISTS shop_discount_rules (id BIGINT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(64) NOT NULL COMMENT '规则名称',type VARCHAR(32) NOT NULL COMMENT '类型: PERCENT/FIXED/THRESHOLD',value INT NOT NULL COMMENT '折扣值(PERCENT=百分比数字, FIXED/THRESHOLD=金额分)',threshold_amount INT COMMENT '满减门槛(分,仅THRESHOLD)',category_id BIGINT COMMENT '适用类目ID(null=全部)',distribution_system_id BIGINT COMMENT '适用体系ID(null=全部)',product_ids TEXT COMMENT '适用商品ID(逗号分隔,null=全部)',start_time DATETIME COMMENT '开始时间',end_time DATETIME COMMENT '结束时间',max_uses INT DEFAULT 0 COMMENT '最大使用次数(0=不限)',used_count INT DEFAULT 0 COMMENT '已使用次数',enabled TINYINT(1) DEFAULT 1 COMMENT '启用',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_category (category_id),INDEX idx_enabled (enabled)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='折扣规则';

-- trial_memberships

CREATE TABLE IF NOT EXISTS trial_memberships (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT '用户ID', family_id BIGINT NOT NULL COMMENT '家庭ID', start_date DATETIME NOT NULL COMMENT '试用开始时间', end_date DATETIME NOT NULL COMMENT '试用结束时间', status VARCHAR(20) DEFAULT 'PENDING' COMMENT '状态:PENDING/ACTIVE/EXPIRED/CONVERTED', trial_days INT DEFAULT 7 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), INDEX idx_end_date (end_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- user_profile_history

CREATE TABLE IF NOT EXISTS user_profile_history (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT '用户ID', field_name VARCHAR(64) NOT NULL COMMENT '变更字段名', old_value TEXT COMMENT '旧值', new_value TEXT COMMENT '新值', source VARCHAR(32) DEFAULT 'user' COMMENT '变更来源: user/admin/auto', changed_at DATETIME NOT NULL COMMENT '变更时间', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_ph_user (user_id), INDEX idx_ph_field (field_name), INDEX idx_ph_changed (changed_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息变更历史表';

-- follow_up

CREATE TABLE IF NOT EXISTS follow_up (id BIGINT AUTO_INCREMENT PRIMARY KEY, family_id BIGINT NOT NULL COMMENT '家庭ID', child_id BIGINT COMMENT '孩子ID', teacher_id BIGINT COMMENT '规划师ID', deficiency_id BIGINT COMMENT '缺乏项ID', message_id BIGINT COMMENT '消息ID', status VARCHAR(20) DEFAULT 'pending' COMMENT '状态', priority VARCHAR(10) DEFAULT 'medium' COMMENT '优先级', description VARCHAR(500) COMMENT '描述', parent_feedback TEXT COMMENT '家长反馈', teacher_review VARCHAR(500) COMMENT '规划师点评', due_date DATETIME COMMENT '截止日期', completed_at DATETIME COMMENT '完成时间', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_family (family_id), INDEX idx_teacher (teacher_id), INDEX idx_status (status), INDEX idx_due (due_date);

-- food_nutrients

CREATE TABLE IF NOT EXISTS food_nutrients (id BIGINT AUTO_INCREMENT PRIMARY KEY, food_id BIGINT NOT NULL COMMENT '食材ID', nutrient_code VARCHAR(20) NOT NULL COMMENT '营养素编码', amount_per_100g DECIMAL(10,4) NOT NULL COMMENT '每100g含量', unit VARCHAR(20) NOT NULL COMMENT '单位', source VARCHAR(50) DEFAULT 'importer' COMMENT '数据来源', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_food_nutrient (food_id, nutrient_code), INDEX idx_nutrient (nutrient_code);

-- foods

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)', 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 '食材图片', status VARCHAR(20) DEFAULT 'active', sort_order INT DEFAULT 0, 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);

-- meal_logs

CREATE TABLE IF NOT EXISTS meal_logs (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT '用户ID', child_id BIGINT COMMENT '孩子ID', meal_type VARCHAR(20) NOT NULL COMMENT '早餐/午餐/晚餐/加餐', meal_date DATE NOT NULL COMMENT '用餐日期', meal_time DATETIME COMMENT '具体时间', foods TEXT NOT NULL COMMENT '食物清单JSON', total_calories DECIMAL(8,2) COMMENT '总热量', total_protein DECIMAL(8,2) COMMENT '总蛋白质', total_fat DECIMAL(8,2) COMMENT '总脂肪', total_carbs DECIMAL(8,2) COMMENT '总碳水', total_fiber DECIMAL(8,2) COMMENT '总膳食纤维', note TEXT COMMENT '备注', images VARCHAR(1000) COMMENT '图片JSON', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_date (user_id, meal_date), INDEX idx_child_date (child_id, meal_date);

-- product_skus

CREATE TABLE IF NOT EXISTS product_skus (  id INT NOT NULL AUTO_INCREMENT,  product_id INT NOT NULL COMMENT '商品ID',  sku_code VARCHAR(64) NOT NULL COMMENT 'SKU编码',  specs JSON NOT NULL COMMENT '规格JSON',  price DECIMAL(10,2) NOT NULL COMMENT 'SKU价格',  stock INT NOT NULL DEFAULT 0 COMMENT '库存',  original_price DECIMAL(10,2) COMMENT '原价/划线价',  image VARCHAR(512) COMMENT 'SKU图片',  enabled TINYINT(1) NOT NULL DEFAULT 1,  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (id),  KEY idx_product_id (product_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品SKU';

-- rdi_standards

CREATE TABLE IF NOT EXISTS rdi_standards (id BIGINT AUTO_INCREMENT PRIMARY KEY, nutrient_code VARCHAR(20) NOT NULL COMMENT '营养素编码', age_group VARCHAR(10) NOT NULL COMMENT '年龄段', gender VARCHAR(6) DEFAULT 'all' COMMENT '性别', rdi_type VARCHAR(10) NOT NULL COMMENT '参考类型', amount DECIMAL(10,4) NOT NULL COMMENT '推荐量', unit VARCHAR(20) NOT NULL COMMENT '单位', source VARCHAR(100) COMMENT '数据来源', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_rdi (nutrient_code, age_group, gender, rdi_type), INDEX idx_age (age_group);

-- recipes

CREATE TABLE IF NOT EXISTS recipes (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL COMMENT '食谱名称', meal_type VARCHAR(20) COMMENT '早餐/午餐/晚餐/加餐', description TEXT COMMENT '简介', ingredients TEXT NOT NULL COMMENT '食材清单JSON', steps TEXT COMMENT '烹饪步骤JSON', cook_time INT COMMENT '烹饪时间(分钟)', difficulty VARCHAR(20) COMMENT '难度: 简单/中等/困难', total_calories DECIMAL(8,2) COMMENT '总热量', total_protein DECIMAL(8,2) COMMENT '总蛋白质', total_fat DECIMAL(8,2) COMMENT '总脂肪', total_carbs DECIMAL(8,2) COMMENT '总碳水', total_fiber DECIMAL(8,2) COMMENT '总膳食纤维', suitable_for VARCHAR(200) COMMENT '适宜场景', nutrition_tags VARCHAR(500) COMMENT '营养标签', external_source VARCHAR(100), external_id VARCHAR(100), cover_image VARCHAR(500), status VARCHAR(20) DEFAULT 'active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_meal_type (meal_type), INDEX idx_cook_time (cook_time);

-- report_food_suitability

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

-- seasonal_foods

CREATE TABLE IF NOT EXISTS seasonal_foods (id BIGINT AUTO_INCREMENT PRIMARY KEY, food_id BIGINT NOT NULL COMMENT '关联foods.id', month TINYINT NOT NULL COMMENT '月份 1-12', region VARCHAR(50) DEFAULT '全国' COMMENT '产地', peak_flag TINYINT DEFAULT 0 COMMENT '盛产标志', sort_order INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_food_month (food_id, month), INDEX idx_month (month);

-- shopping_list_items

CREATE TABLE IF NOT EXISTS shopping_list_items (id BIGINT AUTO_INCREMENT PRIMARY KEY, list_id BIGINT NOT NULL COMMENT '购物清单ID', food_id BIGINT COMMENT '食材ID', food_name VARCHAR(100) NOT NULL COMMENT '食材名称', category VARCHAR(50) COMMENT '分类', quantity DECIMAL(8,2) DEFAULT 1 COMMENT '数量', unit VARCHAR(20) DEFAULT '份' COMMENT '单位', priority VARCHAR(10) DEFAULT 'medium' COMMENT '优先级', reason VARCHAR(200) COMMENT '推荐原因', score INT COMMENT '评分', source VARCHAR(20) COMMENT '来源', purchased TINYINT DEFAULT 0 COMMENT '是否已购', purchased_at DATETIME COMMENT '购买时间', sort_order INT DEFAULT 0 COMMENT '排序', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_list (list_id), INDEX idx_purchased (purchased);

-- shopping_lists

CREATE TABLE IF NOT EXISTS shopping_lists (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT '用户ID', child_id BIGINT COMMENT '孩子ID', report_id BIGINT COMMENT '健康报告ID', title VARCHAR(200) COMMENT '标题', source VARCHAR(20) NOT NULL COMMENT '来源', status VARCHAR(20) DEFAULT 'active' COMMENT '状态', total_items INT DEFAULT 0 COMMENT '总项数', purchased_items INT DEFAULT 0 COMMENT '已购项数', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_user (user_id), INDEX idx_report (report_id), INDEX idx_status (status);

-- user_food_preference

CREATE TABLE IF NOT EXISTS user_food_preference (id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT '用户ID', food_id BIGINT COMMENT '食材ID', food_name VARCHAR(100) NOT NULL COMMENT '食材名称', score INT NOT NULL DEFAULT 0 COMMENT '评分', reason VARCHAR(500) COMMENT '原因', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_user (user_id);



-- ========== Schema.sql 补充: 从 entity 反向生成的表 ==========

-- article_quiz_records

CREATE TABLE IF NOT EXISTS article_quiz_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    article_id BIGINT,
    user_id BIGINT,
    child_id BIGINT,
    questions VARCHAR(255),
    answers VARCHAR(255),
    score INT,
    points_earned INT,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- assessment_results

CREATE TABLE IF NOT EXISTS assessment_results (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT,
    teacher_id BIGINT,
    appointment_id BIGINT,
    attention_score INT,
    focus_score INT,
    memory_score INT,
    logic_score INT,
    overall_score INT,
    assessment_date DATETIME,
    duration_minutes INT,
    analysis_report VARCHAR(255),
    growth_suggestions VARCHAR(255),
    next_review_date DATETIME,
    status VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME,
    child_name VARCHAR(255),
    child_birthday DATETIME,
    child_gender VARCHAR(255),
    child_age INT,
    school VARCHAR(255),
    grade VARCHAR(255),
    grade_class VARCHAR(255),
    height INT,
    weight INT,
    family_status VARCHAR(255),
    parent_degree VARCHAR(255),
    nation VARCHAR(255),
    province VARCHAR(255),
    city VARCHAR(255),
    district VARCHAR(255),
    address VARCHAR(255),
    single_child VARCHAR(255),
    school_score VARCHAR(255),
    urgent_name VARCHAR(255),
    urgent_phone VARCHAR(255),
    pics VARCHAR(255),
    videos VARCHAR(255),
    audio VARCHAR(255),
    audio_url VARCHAR(255),
    process_desc VARCHAR(255),
    result_desc VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- cognitive_norms

CREATE TABLE IF NOT EXISTS cognitive_norms (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dimension_key VARCHAR(255),
    gender VARCHAR(255),
    age_min INT,
    age_max INT,
    percentile5 INT,
    percentile25 INT,
    percentile50 INT,
    percentile75 INT,
    percentile95 INT,
    source VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- contact_match_requests

CREATE TABLE IF NOT EXISTS contact_match_requests (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    requester_id BIGINT,
    target_user_id BIGINT,
    contact_id BIGINT,
    status VARCHAR(255),
    message VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- daily_checkin

CREATE TABLE IF NOT EXISTS daily_checkin (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    checkin_date DATETIME,
    diet_score INT,
    exercise_score INT,
    sleep_score INT,
    water_intake INT,
    mood_score INT,
    weight_kg DECIMAL(10,2),
    height_cm DECIMAL(10,2),
    remark VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- diet_meal_record

CREATE TABLE IF NOT EXISTS diet_meal_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    checkin_id BIGINT,
    meal_type VARCHAR(255),
    food_name VARCHAR(255),
    food_amount VARCHAR(255),
    calories INT,
    protein DECIMAL(10,2),
    fat DECIMAL(10,2),
    carbs DECIMAL(10,2),
    fiber DECIMAL(10,2),
    eaten_at DATETIME,
    source VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- dimension_weights

CREATE TABLE IF NOT EXISTS dimension_weights (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    target_type VARCHAR(255),
    target_id BIGINT,
    dimension VARCHAR(255),
    weight INT,
    enabled TINYINT(1),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- emotion_alert

CREATE TABLE IF NOT EXISTS emotion_alert (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT,
    alert_type VARCHAR(255),
    severity VARCHAR(255),
    title VARCHAR(255),
    message VARCHAR(255),
    related_checkin_id BIGINT,
    resolved TINYINT(1),
    created_at DATETIME,
    resolved_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- energy_dimension_config

CREATE TABLE IF NOT EXISTS energy_dimension_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dimension VARCHAR(255),
    data_source VARCHAR(255),
    category VARCHAR(255),
    weight INT,
    is_active INT,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- exercise_record

CREATE TABLE IF NOT EXISTS exercise_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    checkin_id BIGINT,
    exercise_type VARCHAR(255),
    duration_minutes INT,
    intensity VARCHAR(255),
    calories_burned INT,
    heart_rate_avg INT,
    heart_rate_max INT,
    start_time DATETIME,
    end_time DATETIME,
    remark VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- family_memberships

CREATE TABLE IF NOT EXISTS family_memberships (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT,
    level_code VARCHAR(255),
    start_date DATETIME,
    end_date DATETIME,
    payment_method VARCHAR(255),
    payment_status VARCHAR(255),
    order_no VARCHAR(255),
    transaction_id VARCHAR(255),
    amount INT,
    auto_renew INT,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- food_recommend_idx

CREATE TABLE IF NOT EXISTS food_recommend_idx (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    food_id BIGINT,
    food_name VARCHAR(255),
    index_score INT,
    health_report_id BIGINT,
    calculated_at DATETIME,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- food_recommend_idx_log

CREATE TABLE IF NOT EXISTS food_recommend_idx_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    food_id BIGINT,
    food_name VARCHAR(255),
    previous_index INT,
    new_index INT,
    health_report_id BIGINT,
    change_reason VARCHAR(255),
    changed_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- guide_activities

CREATE TABLE IF NOT EXISTS guide_activities (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    guide_id BIGINT,
    title VARCHAR(255),
    description VARCHAR(255),
    cover_image VARCHAR(255),
    category VARCHAR(255),
    location VARCHAR(255),
    activity_date DATETIME,
    max_participants INT,
    current_participants INT,
    price BIGINT,
    status VARCHAR(255),
    source VARCHAR(255),
    published_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- guide_family_relations

CREATE TABLE IF NOT EXISTS guide_family_relations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    guide_id BIGINT,
    family_id BIGINT,
    relation_type VARCHAR(255),
    status VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- guide_hierarchy

CREATE TABLE IF NOT EXISTS guide_hierarchy (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT,
    child_id BIGINT,
    level INT,
    status VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- health_data_sources

CREATE TABLE IF NOT EXISTS health_data_sources (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    source_type VARCHAR(255),
    dimension_codes VARCHAR(255),
    parsed_result VARCHAR(255),
    file_url VARCHAR(255),
    confidence DECIMAL(10,2),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- health_dimension_scores

CREATE TABLE IF NOT EXISTS health_dimension_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    dimension VARCHAR(255),
    score INT,
    percentile INT,
    data_source VARCHAR(255),
    tier INT,
    raw_data VARCHAR(255),
    assess_date DATETIME,
    expire_date DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- health_growth_record

CREATE TABLE IF NOT EXISTS health_growth_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    record_date DATETIME,
    height_cm DECIMAL(10,2),
    weight_kg DECIMAL(10,2),
    bmi DECIMAL(10,2),
    head_circumference DECIMAL(10,2),
    bone_age INT,
    tooth_age INT,
    growth_rate DECIMAL(10,2),
    percentile_height INT,
    percentile_weight INT,
    percentile_bmi INT,
    standard_type VARCHAR(255),
    source VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- health_norm_reference

CREATE TABLE IF NOT EXISTS health_norm_reference (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dimension VARCHAR(255),
    gender VARCHAR(255),
    age_min INT,
    age_max INT,
    percentile5 INT,
    percentile15 INT,
    percentile25 INT,
    percentile50 INT,
    percentile75 INT,
    percentile85 INT,
    percentile95 INT,
    source VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- interaction_logs

CREATE TABLE IF NOT EXISTS interaction_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id BIGINT,
    from_member_id BIGINT,
    to_member_id BIGINT,
    interaction_type VARCHAR(255),
    description VARCHAR(255),
    happened_at DATETIME,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- membership_levels

CREATE TABLE IF NOT EXISTS membership_levels (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level_code VARCHAR(255),
    level_name VARCHAR(255),
    level_desc VARCHAR(255),
    price_monthly INT,
    price_quarterly INT,
    price_yearly INT,
    features VARCHAR(255),
    max_children INT,
    max_tasks_per_day INT,
    ai_review_enabled INT,
    priority_support INT,
    teacher_consultation INT,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- mental_health_screen

CREATE TABLE IF NOT EXISTS mental_health_screen (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    child_id BIGINT,
    screen_type VARCHAR(255),
    answers VARCHAR(255),
    total_score INT,
    severity_level VARCHAR(255),
    risk_flags VARCHAR(255),
    suggestions VARCHAR(255),
    completed_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- order_items

CREATE TABLE IF NOT EXISTS order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT,
    product_id BIGINT,
    sku_id BIGINT,
    product_name VARCHAR(255),
    product_image VARCHAR(255),
    sku_specs VARCHAR(255),
    price DECIMAL(10,2),
    quantity INT,
    subtotal DECIMAL(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- payment_orders

CREATE TABLE IF NOT EXISTS payment_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(255),
    family_id BIGINT,
    level_code VARCHAR(255),
    payment_type VARCHAR(255),
    period VARCHAR(255),
    amount INT,
    user_coupon_id BIGINT,
    status VARCHAR(255),
    pay_method VARCHAR(255),
    transaction_id VARCHAR(255),
    paid_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- points_exchange_products

CREATE TABLE IF NOT EXISTS points_exchange_products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    description VARCHAR(255),
    cover_image VARCHAR(255),
    points_price INT,
    stock INT,
    product_type VARCHAR(255),
    category VARCHAR(255),
    expiry_days INT,
    sort_order INT,
    status VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- points_exchange_records

CREATE TABLE IF NOT EXISTS points_exchange_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    child_id BIGINT,
    family_member_id BIGINT,
    product_id BIGINT,
    product_name VARCHAR(255),
    points_cost INT,
    quantity INT,
    status VARCHAR(255),
    redeem_code VARCHAR(255),
    expired_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- promotion_materials

CREATE TABLE IF NOT EXISTS promotion_materials (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    description VARCHAR(255),
    material_type VARCHAR(255),
    cover_url VARCHAR(255),
    file_url VARCHAR(255),
    tags VARCHAR(255),
    usage_scenario VARCHAR(255),
    sort_order INT,
    status VARCHAR(255),
    created_by BIGINT,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- relationship_questionnaire_responses

CREATE TABLE IF NOT EXISTS relationship_questionnaire_responses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    snapshot_id BIGINT,
    respondent_id BIGINT,
    answers_json VARCHAR(255),
    trust_score DECIMAL(10,2),
    intimacy_score DECIMAL(10,2),
    communication_score DECIMAL(10,2),
    interaction_bonus DECIMAL(10,2),
    total_score DECIMAL(10,2),
    calculated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- relationship_questionnaire_snapshots

CREATE TABLE IF NOT EXISTS relationship_questionnaire_snapshots (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_member_id BIGINT,
    respondent_id BIGINT,
    member_name VARCHAR(255),
    relationship_type VARCHAR(255),
    ai_generated_json VARCHAR(255),
    version VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- share_events

CREATE TABLE IF NOT EXISTS share_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    target_type VARCHAR(255),
    target_id BIGINT,
    share_channel VARCHAR(255),
    share_title VARCHAR(255),
    share_url VARCHAR(255),
    click_count INT,
    read_count INT,
    reward_points INT,
    status VARCHAR(255),
    expires_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- share_reads

CREATE TABLE IF NOT EXISTS share_reads (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    share_event_id BIGINT,
    reader_id BIGINT,
    reader_ip VARCHAR(255),
    read_duration INT,
    read_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sleep_record

CREATE TABLE IF NOT EXISTS sleep_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    checkin_id BIGINT,
    sleep_time DATETIME,
    wake_time DATETIME,
    duration_minutes INT,
    deep_sleep_minutes INT,
    light_sleep_minutes INT,
    rem_minutes INT,
    quality_score INT,
    dream_count INT,
    remark VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- teachers

CREATE TABLE IF NOT EXISTS teachers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    teacher_no VARCHAR(255),
    title VARCHAR(255),
    specialty VARCHAR(255),
    bio VARCHAR(255),
    experience_years INT,
    rating INT,
    total_appointments INT,
    status VARCHAR(255),
    verification_status VARCHAR(255),
    street_id BIGINT,
    province VARCHAR(255),
    city VARCHAR(255),
    district VARCHAR(255),
    street VARCHAR(255),
    verified_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- training_plan_items

CREATE TABLE IF NOT EXISTS training_plan_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    plan_id BIGINT,
    sequence INT,
    dimension_key VARCHAR(255),
    suggestion VARCHAR(255),
    task_title VARCHAR(255),
    task_desc VARCHAR(255),
    minigame_code VARCHAR(255),
    article_id BIGINT,
    task_points INT,
    deadline_days INT,
    status VARCHAR(255),
    completed_at DATETIME,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- training_plans

CREATE TABLE IF NOT EXISTS training_plans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    guide_id BIGINT,
    child_id BIGINT,
    title VARCHAR(255),
    description VARCHAR(255),
    status VARCHAR(255),
    source VARCHAR(255),
    deployed_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- training_plan_tasks

CREATE TABLE IF NOT EXISTS training_plan_tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    plan_id BIGINT,
    plan_item_id BIGINT,
    task_id BIGINT,
    child_id BIGINT,
    status VARCHAR(255),
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- upgrade_services

CREATE TABLE IF NOT EXISTS upgrade_services (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    guide_id BIGINT,
    from_level VARCHAR(255),
    to_level VARCHAR(255),
    service_fee INT,
    status VARCHAR(255),
    reviewed_by BIGINT,
    reviewed_at DATETIME,
    reject_reason VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- user_nutrition_profile

CREATE TABLE IF NOT EXISTS user_nutrition_profile (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    fruit_allergy VARCHAR(255),
    fruit_preferences VARCHAR(255),
    questionnaire_status VARCHAR(255),
    budget_monthly INT,
    family_taste_preferences VARCHAR(255),
    cuisine_style VARCHAR(255),
    meal_count INT,
    cooking_ability VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- user_rfm_tags

CREATE TABLE IF NOT EXISTS user_rfm_tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    r_score INT,
    f_score INT,
    m_score INT,
    rfm_tier VARCHAR(255),
    total_orders INT,
    total_amount DECIMAL(10,2),
    last_order_at DATETIME,
    updated_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- user_trigger_rules

CREATE TABLE IF NOT EXISTS user_trigger_rules (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    action_config VARCHAR(255),
    last_triggered_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- water_record

CREATE TABLE IF NOT EXISTS water_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT,
    checkin_id BIGINT,
    amount_ml INT,
    water_type VARCHAR(255),
    drunk_at DATETIME,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- 家庭成员变更日志表
-- =============================================

CREATE TABLE IF NOT EXISTS family_member_logs (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    family_id    BIGINT       NOT NULL COMMENT '家庭ID',
    member_id    BIGINT       NOT NULL COMMENT '成员ID',
    action       VARCHAR(32)  NOT NULL COMMENT '操作: join/leave/update/kick',
    old_value    VARCHAR(500) COMMENT '旧值',
    new_value    VARCHAR(500) COMMENT '新值',
    operator_id  BIGINT       COMMENT '操作人用户ID',
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_family_id (family_id),
    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(32) COMMENT '运动类型: run/swim/yoga/walk/cycle/other',
    duration_minutes INT COMMENT '时长(分钟)',
    intensity VARCHAR(16) 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 sleep_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL COMMENT '家庭成员ID',
    checkin_id BIGINT COMMENT '关联打卡ID',
    sleep_time DATETIME COMMENT '入睡时间',
    wake_time DATETIME 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='冥想打卡记录';