cfc/cfc-backend/src/main/java/com/etotem/cfc/config/DatabaseInitializer.java

package com.etotem.cfc.config;

import com.etotem.cfc.mapper.*;
import lombok.extern.slf4j.Slf4j;
import javax.annotation.Resource;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.StreamUtils;

import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
@Component
public class DatabaseInitializer implements CommandLineRunner {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private ResourceLoader resourceLoader;

    @Override
    public void run(String... args) throws Exception {
        log.info("开始初始化数据库...");

        // 1. 检查并创建数据库
        initializeDatabase();

        // 2. 检查并创建表
        initializeTables();

        // 3. 执行数据库迁移（添加新列）
        runMigrations();

        // 4. 初始化默认数据
        initializeDefaultData();

        // 5. 初始化食品相关表
        initFoodTables();

        log.info("数据库初始化完成！");
    }

    private void initializeDatabase() {
        try {
            // 尝试连接数据库，如果不存在则创建
            jdbcTemplate.execute("CREATE DATABASE IF NOT EXISTS xzyj DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            log.info("数据库检查完成");
        } catch (Exception e) {
            log.warn("创建数据库失败: {}", e.getMessage());
        }
    }

    private void initializeTables() {
        try {
            org.springframework.core.io.Resource resource = resourceLoader.getResource("classpath:schema.sql");
            String content;
            try (java.io.InputStream is = resource.getInputStream()) {
                content = new String(StreamUtils.copyToByteArray(is), StandardCharsets.UTF_8);
            }

            // Remove SQL single-line comments
            content = content.replaceAll("(?m)^\\s*--.*$", "");

            // Extract all CREATE TABLE IF NOT EXISTS statements
            Pattern pattern = Pattern.compile(
                "CREATE\\s+TABLE\\s+IF\\s+NOT\\s+EXISTS\\s+.*?;",
                Pattern.CASE_INSENSITIVE | Pattern.DOTALL
            );
            Matcher matcher = pattern.matcher(content);

            List<String> createTableSQLs = new ArrayList<>();
            while (matcher.find()) {
                String sql = matcher.group().trim();
                if (!sql.isEmpty()) {
                    createTableSQLs.add(sql);
                }
            }

            log.info("从 schema.sql 加载了 {} 个建表语句", createTableSQLs.size());

            for (String sql : createTableSQLs) {
                try {
                    jdbcTemplate.execute(sql);
                } catch (Exception e) {
                    log.warn("创建表失败: {}", e.getMessage());
                }
            }

            log.info("数据表检查完成");
        } catch (Exception e) {
            log.error("读取 schema.sql 失败", e);
        }
    }

    private void runMigrations() {
        // 迁移1: 任务表添加执行者字段
        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN executor_type ENUM('child', 'parent') DEFAULT 'child' COMMENT '执行者类型'");
            log.info("已添加executor_type列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN executor_id BIGINT COMMENT '执行者ID'");
            log.info("已添加executor_id列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD INDEX idx_executor (executor_type, executor_id)");
            log.info("已添加idx_executor索引到tasks表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移2: 用户表添加角色和积分字段
        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN roles VARCHAR(100) COMMENT '角色列表'");
            log.info("已添加roles列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN total_points INT DEFAULT 0 COMMENT '家长总积分'");
            log.info("已添加total_points列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD INDEX idx_roles (roles(20))");
            log.info("已添加idx_roles索引到users表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移3: users表添加新字段
        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN id_card VARCHAR(18) COMMENT '身份证号'");
            log.info("已添加id_card列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN real_name VARCHAR(50) COMMENT '真实姓名'");
            log.info("已添加real_name列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN phone VARCHAR(11) COMMENT '手机号'");
            log.info("已添加phone列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN birthday DATE COMMENT '生日'");
            log.info("已添加birthday列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN gender ENUM('male', 'female') COMMENT '性别'");
            log.info("已添加gender列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD INDEX idx_phone (phone)");
            log.info("已添加idx_phone索引到users表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移4: children表添加新字段
        try {
            jdbcTemplate.execute("ALTER TABLE children ADD COLUMN birthday DATE COMMENT '生日'");
            log.info("已添加birthday列到children表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE children ADD COLUMN gender ENUM('male', 'female') COMMENT '性别'");
            log.info("已添加gender列到children表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移5: 修改users.role为支持teacher
        try {
            jdbcTemplate.execute("ALTER TABLE users MODIFY COLUMN role ENUM('parent', 'child', 'teacher') NOT NULL");
            log.info("已更新users.role字段支持teacher角色");
        } catch (Exception e) {
            // 修改失败，忽略错误
        }

        // 迁移5.1: 修改users.role为支持admin
        try {
            jdbcTemplate.execute("ALTER TABLE users MODIFY COLUMN role ENUM('parent', 'child', 'teacher', 'admin') NOT NULL");
            log.info("已更新users.role字段支持admin角色");
        } catch (Exception e) {
            // 修改失败，忽略错误
        }

        // 迁移5.2: 确保管理员角色为admin（修复旧数据遗留问题）
        try {
            int updated = jdbcTemplate.update("UPDATE admins SET role = 'admin' WHERE username = 'admin'");
            if (updated > 0) {
                log.info("已修正管理员角色为admin");
            }
        } catch (Exception e) {
            // 更新失败，忽略错误
        }

        // 迁移6: 创建streets表（街道地址维度表）
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS streets (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "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, " +
                    "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                    "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");
            log.info("已创建streets表");
        } catch (Exception e) {
            // 表已存在，忽略错误
        }

        // 迁移7: families表添加地址字段
        try {
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN street_id BIGINT COMMENT '街道ID'");
            log.info("已添加street_id列到families表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN province VARCHAR(50) COMMENT '省份'");
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN city VARCHAR(50) COMMENT '城市'");
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN district VARCHAR(50) COMMENT '区县'");
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN street VARCHAR(100) COMMENT '街道'");
            log.info("已添加地址字段到families表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE families ADD INDEX idx_street_id (street_id)");
            log.info("已添加idx_street_id索引到families表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移7b: families表添加teacher_id和creator_id字段
        try {
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN teacher_id BIGINT COMMENT '绑定的成长规划师ID'");
            log.info("已添加teacher_id列到families表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }
        try {
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN creator_id BIGINT COMMENT '家庭创建者ID'");
            log.info("已添加creator_id列到families表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移8: teachers表添加地址字段
        try {
            jdbcTemplate.execute("ALTER TABLE teachers ADD COLUMN street_id BIGINT COMMENT '街道ID'");
            log.info("已添加street_id列到teachers表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE teachers ADD COLUMN province VARCHAR(50) COMMENT '省份'");
            jdbcTemplate.execute("ALTER TABLE teachers ADD COLUMN city VARCHAR(50) COMMENT '城市'");
            jdbcTemplate.execute("ALTER TABLE teachers ADD COLUMN district VARCHAR(50) COMMENT '区县'");
            jdbcTemplate.execute("ALTER TABLE teachers ADD COLUMN street VARCHAR(100) COMMENT '街道'");
            log.info("已添加地址字段到teachers表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE teachers ADD INDEX idx_street_id (street_id)");
            log.info("已添加idx_street_id索引到teachers表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移9: users表添加成长规划师相关字段
        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN teacher_no VARCHAR(50) COMMENT '成长规划师证书号'");
            log.info("已添加teacher_no列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN teacher_photo VARCHAR(500) COMMENT '成长规划师照片'");
            log.info("已添加teacher_photo列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN certificate_image VARCHAR(500) COMMENT '成长规划师证书照片'");
            log.info("已添加certificate_image列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN teacher_status VARCHAR(20) DEFAULT 'pending' COMMENT '成长规划师审核状态'");
            log.info("已添加teacher_status列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE users ADD INDEX idx_teacher_status (teacher_status)");
            log.info("已添加idx_teacher_status索引到users表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移10: users表添加teacherFamilyIds字段（成长规划师关联的家庭ID列表）
        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN teacher_family_ids VARCHAR(500) COMMENT '成长规划师关联的家庭ID列表(逗号分隔)'");
            log.info("已添加teacher_family_ids列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移11: families表添加teacherId字段（绑定的成长规划师ID）
        try {
            jdbcTemplate.execute("ALTER TABLE families ADD COLUMN teacher_id BIGINT COMMENT '绑定的成长规划师ID'");
            log.info("已添加teacher_id列到families表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE families ADD INDEX idx_teacher_id (teacher_id)");
            log.info("已添加idx_teacher_id索引到families表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // 迁移12: 创建 teacher_messages 表（家长咨询成长规划师）
        try {
            jdbcTemplate.execute("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");
            log.info("已创建teacher_messages表");
        } catch (Exception e) {
            // 表已存在，忽略错误
        }

// 迁移13: admin_task_templates表添加teacher_id字段
try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN teacher_id BIGINT COMMENT '成长规划师ID'");
log.info("已添加teacher_id列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

// 迁移13b: admin_task_templates表添加新字段
try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN complete_types VARCHAR(100) COMMENT '完成方式JSON'");
log.info("已添加complete_types列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN duration INT COMMENT '预计完成时长(分钟)'");
log.info("已添加duration列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN review_type VARCHAR(20) COMMENT '审核人类型: creator/parent/teacher/ai'");
log.info("已添加review_type列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN task_type VARCHAR(20) COMMENT '任务性质: onetime/recurring'");
log.info("已添加task_type列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN frequency VARCHAR(20) COMMENT '频率: daily/weekly/monthly'");
log.info("已添加frequency列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

try {
jdbcTemplate.execute("ALTER TABLE admin_task_templates ADD COLUMN max_frequency INT COMMENT '周期内最多完成次数'");
log.info("已添加max_frequency列到admin_task_templates表");
} catch (Exception e) {
// 列已存在，忽略错误
}

// 迁移14: rewards表child_id字段允许NULL（模板奖励不需要child_id）
        try {
            jdbcTemplate.execute("ALTER TABLE rewards MODIFY COLUMN child_id BIGINT NULL COMMENT '孩子ID，模板奖励为NULL'");
            log.info("已修改rewards.child_id字段允许NULL");
        } catch (Exception e) {
            // 修改失败，忽略错误
        }

        // 迁移15: admins表添加缺失列
        try {
            jdbcTemplate.execute("ALTER TABLE admins ADD COLUMN nickname VARCHAR(50) DEFAULT NULL AFTER password");
            log.info("已添加nickname列到admins表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE admins ADD COLUMN email VARCHAR(100) DEFAULT NULL AFTER phone");
            log.info("已添加email列到admins表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE admins MODIFY COLUMN status VARCHAR(20) DEFAULT 'active'");
            log.info("已修改admins.status字段类型");
        } catch (Exception e) {
            // 修改失败，忽略错误
        }

        // 迁移16: 创建用户操作日志表
try {
jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS user_operation_logs (" +
"id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
"user_id BIGINT COMMENT \'用户ID\', " +
"nickname VARCHAR(50) COMMENT \'用户昵称\', " +
"operation_type VARCHAR(20) NOT NULL COMMENT \'操作类型: login/logout/create/update/delete/query等\', " +
"description VARCHAR(500) COMMENT \'操作描述\', " +
"request_path VARCHAR(200) COMMENT \'请求路径\', " +
"request_method VARCHAR(10) COMMENT \'请求方法\', " +
"request_params TEXT COMMENT \'请求参数（脱敏）\', " +
"response_status INT COMMENT \'响应状态码\', " +
"client_ip VARCHAR(50) COMMENT \'客户端IP\', " +
"user_agent VARCHAR(500) COMMENT \'User-Agent\', " +
"created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
"INDEX idx_user_id (user_id), " +
"INDEX idx_operation_type (operation_type), " +
"INDEX idx_created_at (created_at)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
log.info("已创建user_operation_logs表");
} catch (Exception e) {
// 表已存在，忽略错误
}

// 迁移16: tasks表添加bonus_rule字段（POINT-002: 超额积分规则）
try {
jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN bonus_rule VARCHAR(500) COMMENT \'超额积分规则JSON\'");
log.info("已添加bonus_rule列到tasks表");
} catch (Exception e) {
// 列已存在，忽略错误
}

// 迁移17: tasks表添加points_expire_days字段（POINT-009: 积分有效期）
try {
jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN points_expire_days INT DEFAULT 0 COMMENT \'积分有效期天数，0表示永久有效\'");
log.info("已添加points_expire_days列到tasks表");
} catch (Exception e) {
// 列已存在，忽略错误
}

// 迁移18: points_log表添加expire_at字段（POINT-009: 积分有效期）
try {
jdbcTemplate.execute("ALTER TABLE points_log ADD COLUMN expire_at DATE COMMENT \'积分过期日期\'");
log.info("已添加expire_at列到points_log表");
} catch (Exception e) {
// 列已存在，忽略错误
}

// 迁移19: points_log表添加expired字段（POINT-009: 过期状态标记）
try {
jdbcTemplate.execute("ALTER TABLE points_log ADD COLUMN expired TINYINT DEFAULT 0 COMMENT \'是否已过期: 0否1是\'");
log.info("已添加expired列到points_log表");
} catch (Exception e) {
// 列已存在，忽略错误
}

// 迁移20: tasks表添加template_id字段（TASK-004: 重复任务模板追踪）
try {
jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN template_id BIGINT COMMENT \'模板任务ID\'");
log.info("已添加template_id列到tasks表");
} catch (Exception e) {
// 列已存在，忽略错误
}

        // 迁移21: tasks表添加is_template字段（TASK-004: 标记模板任务）
        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN is_template TINYINT DEFAULT 0 COMMENT '是否为模板任务'");
            log.info("已添加is_template列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移22: 修复tasks表child_id和deadline允许NULL（支持多孩子和家长任务）
        try {
            jdbcTemplate.execute("ALTER TABLE tasks MODIFY COLUMN child_id BIGINT NULL COMMENT '执行者ID'");
            log.info("已修改tasks.child_id允许NULL");
        } catch (Exception e) {
            // 修改失败，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks MODIFY COLUMN deadline DATETIME NULL COMMENT '截止时间'");
            log.info("已修改tasks.deadline允许NULL");
        } catch (Exception e) {
            // 修改失败，忽略错误
        }

        // 迁移23: tasks表添加新字段
        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN task_type VARCHAR(20) COMMENT '任务性质: onetime/recurring'");
            log.info("已添加task_type列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN frequency VARCHAR(20) COMMENT '频率: daily/weekly/monthly'");
            log.info("已添加frequency列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN max_frequency INT COMMENT '周期内最多完成次数'");
            log.info("已添加max_frequency列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN complete_types VARCHAR(100) COMMENT '完成方式JSON'");
            log.info("已添加complete_types列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN review_type VARCHAR(20) COMMENT '审核人类型: creator/parent/teacher/ai'");
            log.info("已添加review_type列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN earliest_start DATETIME COMMENT '最早开始时间'");
            log.info("已添加earliest_start列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN latest_end DATETIME COMMENT '最晚结束时间'");
            log.info("已添加latest_end列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移24: tasks表添加minigame_code字段（小游戏任务）
        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN minigame_code VARCHAR(50) DEFAULT NULL COMMENT '小游戏代码: schulte/1a2b/sudoku'");
            log.info("已添加minigame_code列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移25: tasks表添加duration字段（TASK-011: 任务预计时长）
        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN duration INT DEFAULT 0 COMMENT '预计时长（分钟）'");
            log.info("已添加duration列到tasks表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE tasks ADD INDEX idx_minigame (minigame_code) WHERE minigame_code IS NOT NULL");
            log.info("已添加idx_minigame索引到tasks表");
        } catch (Exception e) {
            // 索引已存在，忽略错误
        }

        // assessment_appointments 新字段
        try {
            jdbcTemplate.execute("ALTER TABLE assessment_appointments ADD COLUMN user_id BIGINT COMMENT '用户ID'");
        } catch (Exception e) {
        }
        try {
            jdbcTemplate.execute("ALTER TABLE assessment_appointments ADD COLUMN guide_id BIGINT COMMENT '成长规划师ID'");
        } catch (Exception e) {
        }
        try {
            jdbcTemplate.execute("ALTER TABLE assessment_appointments ADD COLUMN package_id BIGINT COMMENT '套餐ID'");
        } catch (Exception e) {
        }
        try {
            jdbcTemplate.execute("ALTER TABLE assessment_appointments ADD COLUMN remark VARCHAR(500) COMMENT '备注'");
        } catch (Exception e) {
        }

        // assessment_orders 表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS assessment_orders (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "order_no VARCHAR(64) NOT NULL UNIQUE, " +
                    "family_id BIGINT NOT NULL, " +
                    "user_id BIGINT NOT NULL, " +
                    "child_id BIGINT, " +
                    "guide_id BIGINT NOT NULL, " +
                    "package_id BIGINT NOT NULL, " +
                    "guide_name VARCHAR(50), " +
                    "package_name VARCHAR(100), " +
                    "total_price BIGINT DEFAULT 0, " +
                    "discount_amount BIGINT DEFAULT 0, " +
                    "actual_price BIGINT DEFAULT 0, " +
                    "status VARCHAR(20) DEFAULT 'pending', " +
                    "pay_time VARCHAR(50), " +
                    "pay_type VARCHAR(20), " +
                    "transaction_id VARCHAR(64), " +
                    "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                    "INDEX idx_family (family_id), " +
                    "INDEX idx_guide (guide_id), " +
                    "INDEX idx_status (status)) " +
                    "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评估订单表'");
        } catch (Exception e) {
        }

        // 迁移26: guide_applications表添加address和bank_card字段
        try {
            jdbcTemplate.execute("ALTER TABLE guide_applications ADD COLUMN address VARCHAR(500) COMMENT '地址'");
            log.info("已添加address列到guide_applications表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE guide_applications ADD COLUMN bank_card VARCHAR(50) COMMENT '银行卡号'");
            log.info("已添加bank_card列到guide_applications表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE guide_applications ADD COLUMN bank_name VARCHAR(100) COMMENT '开户行'");
            log.info("已添加bank_name列到guide_applications表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移27: users表添加address字段（成长规划师地址）
        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN address VARCHAR(500) COMMENT '地址'");
            log.info("已添加address列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移28: 创建guide_application_records表（申请过程记录）
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_application_records (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "application_id BIGINT NOT NULL COMMENT '关联申请ID', " +
                    "user_id BIGINT NOT NULL COMMENT '操作人用户ID', " +
                    "action VARCHAR(50) NOT NULL COMMENT '操作: submit/approve/reject/withdraw/resubmit', " +
                    "from_status VARCHAR(20) COMMENT '操作前状态', " +
                    "to_status VARCHAR(20) NOT NULL COMMENT '操作后状态', " +
                    "remark VARCHAR(500) COMMENT '备注/原因', " +
                    "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "INDEX idx_application_id (application_id), " +
                    "INDEX idx_user_id (user_id), " +
                    "INDEX idx_action (action)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师申请过程记录表'");
            log.info("已创建guide_application_records表");
        } catch (Exception e) {
            // 表已存在，忽略错误
        }

        // 迁移20: children表添加system_points字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'children' AND COLUMN_NAME = 'system_points'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE children ADD COLUMN system_points INT DEFAULT 0 COMMENT '系统积分'");
                log.info("已添加system_points列到children表");
            } else {
                log.info("system_points列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加system_points列失败: {}", e.getMessage());
        }

        // 迁移21: points_log表添加category字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'points_log' AND COLUMN_NAME = 'category'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE points_log ADD COLUMN category VARCHAR(20) DEFAULT 'task' COMMENT '积分分类: task任务积分, system系统积分'");
                log.info("已添加category列到points_log表");
            } else {
                log.info("category列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加category列失败: {}", e.getMessage());
        }

        // 迁移22: 创建invite_card表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS invite_card (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "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, " +
                    "used TINYINT DEFAULT 0, " +
                    "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                    "INDEX idx_code (code), " +
                    "INDEX idx_ref_type (ref_id, type)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邀请卡片'");
            log.info("已创建invite_card表");
        } catch (Exception e) {
            // 表已存在，忽略错误
        }

        // 迁移23: 创建wishes表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS wishes (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "family_id BIGINT COMMENT '家庭ID', " +
                    "child_id BIGINT COMMENT '目标孩子ID', " +
                    "created_by BIGINT COMMENT '创建者用户ID', " +
                    "creator_type VARCHAR(20) COMMENT '创建者类型: child/parent', " +
                    "title VARCHAR(200) NOT NULL COMMENT '心愿标题', " +
                    "description VARCHAR(500) COMMENT '心愿描述', " +
                    "category VARCHAR(32) COMMENT '心愿分类', " +
                    "points_required INT COMMENT '所需积分', " +
                    "points_set_by BIGINT COMMENT '定价者ID', " +
                    "points_set_at DATETIME COMMENT '定价时间', " +
                    "status VARCHAR(32) DEFAULT 'active' COMMENT '状态: draft/active/pending_exchange/approved/exchanging/fulfilled/cancelled/rejected', " +
                    "exchange_requested_at DATETIME COMMENT '申请兑换时间', " +
                    "exchange_approved_by BIGINT COMMENT '批准者ID', " +
                    "exchange_approved_at DATETIME COMMENT '批准时间', " +
                    "points_deducted_at DATETIME COMMENT '扣积分时间', " +
                    "fulfilled_by BIGINT COMMENT '兑现者ID', " +
                    "fulfilled_at DATETIME COMMENT '兑现时间', " +
                    "child_confirmed_at DATETIME COMMENT '孩子确认收货时间', " +
                    "rejected_by BIGINT COMMENT '拒绝者ID', " +
                    "rejected_at DATETIME COMMENT '拒绝时间', " +
                    "reject_reason VARCHAR(500) COMMENT '拒绝原因', " +
                    "reject_count INT DEFAULT 0 COMMENT '被拒绝次数', " +
                    "cancelled_by BIGINT COMMENT '取消者ID', " +
                    "cancelled_at DATETIME COMMENT '取消时间', " +
                    "cancel_reason VARCHAR(500) COMMENT '取消原因', " +
                    "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "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 COMMENT='心愿单表'");
            log.info("已创建wishes表");
        } catch (Exception e) {
            log.warn("创建wishes表可能已存在: {}", e.getMessage());
        }

        // 迁移24: users表添加个人信息补充字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'ethnicity'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE users " +
                    "ADD COLUMN ethnicity VARCHAR(32) DEFAULT NULL COMMENT '民族', " +
                    "ADD COLUMN blood_type VARCHAR(16) DEFAULT NULL COMMENT '血型', " +
                    "ADD COLUMN highest_education VARCHAR(32) DEFAULT NULL COMMENT '最高学历', " +
                    "ADD COLUMN marital_status VARCHAR(16) DEFAULT NULL COMMENT '婚姻状态', " +
                    "ADD COLUMN family_role VARCHAR(16) DEFAULT NULL COMMENT '家长身份: 爸爸/妈妈/爷爷/奶奶/姥爷/姥姥/其他'");
                log.info("已添加个人信息补充字段到users表");
            } else {
                log.info("个人信息补充字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加个人信息补充字段失败: {}", e.getMessage());
        }

        // Phase 0: 浠艾福俱乐部 — 服务商字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'vendor_type'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN vendor_type VARCHAR(32) DEFAULT NULL COMMENT '服务商类型: planner/activity_provider/product_supplier'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN vendor_status VARCHAR(16) DEFAULT NULL COMMENT '服务商审核状态: pending/approved/rejected'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN vendor_reject_reason VARCHAR(500) DEFAULT NULL COMMENT '审核拒绝原因'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN is_family_admin TINYINT DEFAULT 0 COMMENT '是否家庭管理员'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN vendor_info VARCHAR(2000) DEFAULT NULL COMMENT '服务商资质信息(JSON)'");
                log.info("已添加浠艾福俱乐部服务商字段到users表");
            } else {
                log.info("浠艾福俱乐部服务商字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加服务商字段失败: {}", e.getMessage());
        }

        // Phase 0: 迁移已有规划师到服务商体系
        try {
            int updated = jdbcTemplate.update(
                "UPDATE users SET vendor_type = 'planner', vendor_status = 'approved' WHERE teacher_status = 'approved' AND vendor_type IS NULL"
            );
            if (updated > 0) {
                log.info("已迁移 {} 名规划师到服务商体系", updated);
            }
        } catch (Exception e) {
            log.warn("迁移规划师到服务商体系失败: {}", e.getMessage());
        }

        // ==================== Phase 1: 浠艾福俱乐部商品系统 ====================

        // 商品表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS products (" +
                "  id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                "  name VARCHAR(200) NOT NULL," +
                "  description VARCHAR(1000)," +
                "  intro TEXT," +
                "  cover_image VARCHAR(500)," +
                "  images TEXT," +
                "  price INT NOT NULL DEFAULT 0 COMMENT '价格(分)'," +
                "  stock INT NOT NULL DEFAULT 0," +
                "  sales_count INT NOT NULL DEFAULT 0," +
                "  product_type VARCHAR(32) NOT NULL COMMENT 'activity/course/physical/digital/service'," +
                "  vendor_id BIGINT," +
                "  vendor_name VARCHAR(100)," +
                "  status VARCHAR(32) NOT NULL DEFAULT 'draft' COMMENT 'draft/pending/approved/rejected/on_shelf/off_shelf'," +
                "  reject_reason VARCHAR(500)," +
                "  member_price INT COMMENT '会员价(分)'," +
                "  profit_rate INT DEFAULT 0 COMMENT '平台利润率(千分比)'," +
                "  member_eligible VARCHAR(32) DEFAULT 'none' COMMENT 'all/budget/none'," +
                "  domain VARCHAR(32) COMMENT 'action/mind/body/wisdom/wealth'," +
                "  external_source VARCHAR(64)," +
                "  external_id VARCHAR(128)," +
                "  external_data TEXT," +
                "  created_at DATETIME DEFAULT CURRENT_TIMESTAMP," +
                "  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")"
            );
        } catch (Exception e) {
            log.warn("创建products表失败: {}", e.getMessage());
        }

        // 订单表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS product_orders (" +
                "  id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                "  order_no VARCHAR(64) NOT NULL UNIQUE," +
                "  product_id BIGINT NOT NULL," +
                "  product_name VARCHAR(200)," +
                "  product_type VARCHAR(32)," +
                "  buyer_id BIGINT NOT NULL," +
                "  family_id BIGINT," +
                "  quantity INT NOT NULL DEFAULT 1," +
                "  unit_price INT NOT NULL COMMENT '单价(分)'," +
                "  total_amount INT NOT NULL COMMENT '总金额(分)'," +
                "  status VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT 'pending/paid/completed/cancelled/refunded'," +
                "  payment_method VARCHAR(32) COMMENT 'wechat/points/membership'," +
                "  remark VARCHAR(500)," +
                "  paid_at DATETIME," +
                "  created_at DATETIME DEFAULT CURRENT_TIMESTAMP," +
                "  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")"
            );
        } catch (Exception e) {
            log.warn("创建product_orders表失败: {}", e.getMessage());
        }

        // 系统配置表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS sys_config (" +
                "  id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                "  config_key VARCHAR(64) NOT NULL UNIQUE," +
                "  config_value TEXT," +
                "  description VARCHAR(500)," +
                "  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")"
            );
        } catch (Exception e) {
            log.warn("创建sys_config表失败: {}", e.getMessage());
        }

        // SysConfig 种子数据
        try {
            insertSysConfigSeed("points_exchange_rate", "1", "积分兑换汇率（1积分=1分，即0.01元）");
            insertSysConfigSeed("points_deduct_cap", "0.5", "积分抵扣上限（50%）");
            insertSysConfigSeed("member_fee_standard", "1314", "标准会员年费（元）");
            insertSysConfigSeed("member_fee_premium", "3650", "高级会员年费（元）");
            insertSysConfigSeed("member_eligible_budget", "1314", "等值商品选择预算上限（元）");
            insertSysConfigSeed("renewal_discount_rate", "0.85", "续费折扣率");
            insertSysConfigSeed("member_discount_standard", "0.9", "标准会员折扣率");
            insertSysConfigSeed("member_discount_premium", "0.8", "高级会员折扣率");
            insertSysConfigSeed("member_fee_family", "36500", "家庭会员年费（分）");
            insertSysConfigSeed("member_upgrade_threshold", "131400", "自动升级家庭会员累计消费门槛（分）");
            insertSysConfigSeed("member_discount_family", "9000", "家庭会员商品折扣(bps)");
            insertSysConfigSeed("commission_member_l1_rate", "2000", "推荐家庭会员L1佣金率(bps)");
            insertSysConfigSeed("commission_member_l2_rate", "500", "推荐家庭会员L2佣金率(bps)");
            insertSysConfigSeed("commission_product_profit_share", "1000", "下线商品购买利润分成(bps)");
            insertSysConfigSeed("commission_service_rate", "1000", "下线服务购买佣金率(bps)");
            insertSysConfigSeed("commission_event_fixed", "2000", "推荐新用户报名活动固定佣金(分)");
            insertSysConfigSeed("withdraw_min_amount", "10000", "最低提现金额(分)");
            insertSysConfigSeed("withdraw_tax_rate", "2000", "佣金税率(bps)");
            insertSysConfigSeed("withdraw_fee_free_threshold", "80000", "免税额度(分)");
            insertSysConfigSeed("article_energy_rate_normal", "1", "普通文章能量汇率(每100字)");
            insertSysConfigSeed("article_energy_rate_premium", "2", "优质文章能量汇率(每100字)");
            insertSysConfigSeed("article_energy_rate_original", "3", "原创文章能量汇率(每100字)");
            insertSysConfigSeed("article_reading_min_seconds", "60", "文章阅读达标最少秒数");
            insertSysConfigSeed("activity_checkin_default_points", "5", "活动签到默认积分");
            insertSysConfigSeed("health_checkin_points", "5", "健康打卡积分");
            insertSysConfigSeed("finance_checkin_points", "5", "理财打卡积分");
            log.info("SysConfig 种子数据已加载");
        } catch (Exception e) {
            log.warn("SysConfig 种子数据初始化失败: {}", e.getMessage());
        }

        // Product 种子数据（5 个示例商品，覆盖全部 5 种 productType）
        try {
            insertProductSeed("亲子关系工作坊", "线下半日工作坊，重建家庭对话模式", "activity", 38800, "action", "all");
            insertProductSeed("情绪管理21天训练营", "21天视频课程，识别并管理情绪", "course", 19900, "mind", "all");
            insertProductSeed("肠道菌群检测套装", "家用检测 + 报告解读", "physical", 59900, "body", "budget");
            insertProductSeed("儿童多模态认知测评", "在线测评 + 个性化建议", "digital", 9900, "wisdom", "none");
            insertProductSeed("成长规划师1对1咨询", "60分钟专业规划", "service", 50000, "wealth", "budget");
            log.info("Product 种子数据已加载");
        } catch (Exception e) {
            log.warn("Product 种子数据初始化失败: {}", e.getMessage());
        }

        // ==================== Phase 2: 内容区块可见性配置 ====================

        // 内容区块配置表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS content_sections (" +
                "  id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                "  page_key VARCHAR(50) NOT NULL," +
                "  section_key VARCHAR(50) NOT NULL," +
                "  title VARCHAR(100)," +
                "  allowed_roles VARCHAR(255) DEFAULT '[\"anonymous\"]'," +
                "  sort_order INT DEFAULT 0," +
                "  status VARCHAR(20) DEFAULT 'active'," +
                "  created_at DATETIME DEFAULT CURRENT_TIMESTAMP," +
                "  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")"
            );
            log.info("content_sections 表已创建");
        } catch (Exception e) {
            log.warn("创建 content_sections 表失败: {}", e.getMessage());
        }

        // ContentSection 种子数据
        try {
            insertContentSectionSeed("mind", "func_entries", "功能入口", "[\"anonymous\"]", 1);
            insertContentSectionSeed("mind", "daily_tip", "每日心理", "[\"anonymous\"]", 2);
            insertContentSectionSeed("mind", "recommended_reading", "推荐阅读", "[\"anonymous\"]", 3);
            insertContentSectionSeed("mind", "premium_content", "会员专属内容", "[\"parent\",\"teacher\"]", 4);
            insertContentSectionSeed("mind", "mind_tasks", "心智任务", "[\"parent\",\"child\"]", 5);
            insertContentSectionSeed("mind", "related_activities", "相关活动", "[\"parent\",\"child\"]", 6);
            insertContentSectionSeed("mind", "purchased_courses", "已购课程", "[\"parent\",\"child\"]", 7);
            insertContentSectionSeed("body", "func_entries", "功能入口", "[\"anonymous\"]", 1);
            insertContentSectionSeed("body", "daily_stats", "今日数据", "[\"anonymous\"]", 2);
            insertContentSectionSeed("body", "health_tips", "健康小贴士", "[\"anonymous\"]", 3);
            insertContentSectionSeed("body", "teacher_tips", "规划师推荐", "[\"teacher\"]", 4);
            log.info("ContentSection 种子数据已加载");
        } catch (Exception e) {
            log.warn("ContentSection 种子数据初始化失败: {}", e.getMessage());
        }

        // ==================== 文章内容系统种子数据 ====================

        // 文章分类种子数据
        try {
            Integer catCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM article_categories WHERE id IN (1,2,3,4,5)", Integer.class);
            if (catCount == null || catCount == 0) {
                jdbcTemplate.execute("INSERT IGNORE INTO article_categories (id, name, icon, color, sort_order, status) VALUES " +
                    "(1, '心理健康', '🧠', '#5B9BD5', 1, 1), " +
                    "(2, '情绪管理', '💖', '#FF6B35', 2, 1), " +
                    "(3, '亲子教育', '👨‍👩‍👧‍👦', '#4CAF50', 3, 1), " +
                    "(4, '学习力', '📚', '#FFD700', 4, 1), " +
                    "(5, '专注力', '🎯', '#8D6E63', 5, 1)");
                log.info("文章分类种子数据已加载");
            }
        } catch (Exception e) {
            log.warn("文章分类种子数据初始化失败: {}", e.getMessage());
        }

        // ==================== 分佣裂变系统 ====================
        
        // 迁移: users表添加推荐人字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'referrer_id'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN referrer_id BIGINT DEFAULT NULL COMMENT '推荐人ID'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN referral_code VARCHAR(32) DEFAULT NULL COMMENT '个人邀请码'");
                jdbcTemplate.execute("ALTER TABLE users ADD INDEX idx_referral_code (referral_code)");
                log.info("已添加分佣字段到users表");
            } else {
                log.info("分佣金字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加分佣金字段失败: {}", e.getMessage());
        }

        // 迁移: products表添加利润率字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'profit_rate'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE products ADD COLUMN profit_rate INT DEFAULT 0 COMMENT '平台利润率(bps,100=1%)'");
                log.info("已添加profit_rate列到products表");
            } else {
                log.info("profit_rate列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加profit_rate列失败: {}", e.getMessage());
        }

        // 新表: commission_records (佣金记录)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS commission_records (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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', " +
                "order_amount INT NOT NULL COMMENT '订单金额(分)', " +
                "profit_rate INT DEFAULT 0 COMMENT '利润率(bps,100=1%)', " +
                "commission_amount INT NOT NULL COMMENT '佣金金额(分)', " +
                "status VARCHAR(16) NOT NULL DEFAULT 'settled' COMMENT '状态: pending/settled', " +
                "remark VARCHAR(255) DEFAULT NULL, " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_referrer (referrer_id), " +
                "INDEX idx_buyer (buyer_id), " +
                "INDEX idx_order (order_id, order_type)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            log.info("已创建commission_records表");
        } catch (Exception e) {
            log.warn("创建commission_records表失败: {}", e.getMessage());
        }

        // 新表: withdrawal_requests (提现申请)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS withdrawal_requests (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                "amount INT NOT NULL COMMENT '提现金额(分)', " +
                "account_info TEXT COMMENT '账户信息(JSON)', " +
                "status VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/approved/rejected', " +
                "audit_by BIGINT DEFAULT NULL COMMENT '审核人ID', " +
                "audit_at DATETIME DEFAULT NULL COMMENT '审核时间', " +
                "remark VARCHAR(255) DEFAULT NULL COMMENT '审核备注', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_user (user_id), " +
                "INDEX idx_status (status)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            log.info("已创建withdrawal_requests表");
        } catch (Exception e) {
            log.warn("创建withdrawal_requests表失败: {}", e.getMessage());
        }

        // ==================== 会员体系 & 分佣扩展 ====================

        // 迁移: users表添加会员字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'member_level'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN member_level VARCHAR(20) DEFAULT 'FREE' COMMENT '会员等级: FREE/FAMILY/PROVIDER'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN member_expire_time DATETIME DEFAULT NULL COMMENT '家庭会员到期时间'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN total_consumption BIGINT DEFAULT 0 COMMENT '累计消费金额（分）'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN upgrade_from_consumption TINYINT DEFAULT 0 COMMENT '是否通过累计消费自动升级'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN direct_count INT DEFAULT 0 COMMENT '直接推荐人数'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN indirect_count INT DEFAULT 0 COMMENT '间接推荐人数'");
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN total_commission_earned BIGINT DEFAULT 0 COMMENT '累计获得佣金（分）'");
                log.info("已添加会员字段到users表");
            } else {
                log.info("会员字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加会员字段失败: {}", e.getMessage());
        }

        // 迁移: commission_records表添加扩展字段
        try {
            Integer exists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_records' AND COLUMN_NAME = 'level'",
                Integer.class);
            if (exists == null || exists == 0) {
                jdbcTemplate.execute("ALTER TABLE commission_records ADD COLUMN level INT DEFAULT 1 COMMENT '佣金层级: 1=直接推荐, 2=间接推荐'");
                jdbcTemplate.execute("ALTER TABLE commission_records ADD COLUMN settled_at DATETIME DEFAULT NULL COMMENT '结算时间'");
                jdbcTemplate.execute("ALTER TABLE commission_records ADD COLUMN withdrawn_at DATETIME DEFAULT NULL COMMENT '提现时间'");
                log.info("已添加扩展字段到commission_records表");
            } else {
                log.info("commission_records扩展字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加commission_records扩展字段失败: {}", e.getMessage());
        }

        // 新表: member_upgrade_records (会员升级记录)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS member_upgrade_records (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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, " +
                "INDEX idx_user (user_id), " +
                "INDEX idx_order (order_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员升级记录'");
            log.info("已创建member_upgrade_records表");
        } catch (Exception e) {
            log.warn("创建member_upgrade_records表失败: {}", e.getMessage());
        }

        // ==================== 文章内容系统 ====================

        // 文章分类表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS article_categories (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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='文章分类'");
            log.info("已创建article_categories表");
        } catch (Exception e) {
            log.warn("创建article_categories表失败: {}", e.getMessage());
        }

        // 文章表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS articles (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "category_id BIGINT DEFAULT 0 COMMENT '所属分类ID', " +
                "title VARCHAR(200) NOT NULL COMMENT '标题', " +
                "summary VARCHAR(500) DEFAULT '' COMMENT '摘要', " +
                "cover_image VARCHAR(500) DEFAULT '' COMMENT '封面图URL', " +
                "content LONGTEXT COMMENT '富文本内容', " +
                "tags VARCHAR(200) DEFAULT '' COMMENT '标签JSON数组', " +
                "author VARCHAR(100) DEFAULT '浠艾福' COMMENT '作者', " +
                "read_time INT DEFAULT 0 COMMENT '预计阅读分钟数', " +
                "related_dimensions VARCHAR(100) DEFAULT '' COMMENT '关联五维JSON数组', " +
                "visibility VARCHAR(20) DEFAULT 'public' COMMENT '浏览权限:public/login/private', " +
                "visible_to TEXT COMMENT '私密指定人群JSON', " +
                "status VARCHAR(20) DEFAULT 'draft' COMMENT 'draft/published/archived', " +
                "is_featured TINYINT DEFAULT 0 COMMENT '1精选/0普通', " +
                "published_at DATETIME DEFAULT NULL COMMENT '发布时间', " +
                "view_count INT DEFAULT 0 COMMENT '浏览次数', " +
                "created_by BIGINT DEFAULT 0 COMMENT '发布人adminID', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章'");
            log.info("已创建articles表");
        } catch (Exception e) {
            log.warn("创建articles表失败: {}", e.getMessage());
        }

        // 迁移: articles表补充新字段（兼容旧表）
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'articles' AND COLUMN_NAME = 'visibility'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN visibility VARCHAR(20) DEFAULT 'public' COMMENT '浏览权限:public/login/private' AFTER related_dimensions");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN visible_to TEXT COMMENT '私密指定人群JSON' AFTER visibility");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN tags VARCHAR(200) DEFAULT '' COMMENT '标签JSON数组' AFTER content");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN related_dimensions VARCHAR(100) DEFAULT '' COMMENT '关联五维JSON数组' AFTER read_time");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN is_featured TINYINT DEFAULT 0 COMMENT '1精选/0普通' AFTER status");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN published_at DATETIME DEFAULT NULL COMMENT '发布时间' AFTER is_featured");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN view_count INT DEFAULT 0 COMMENT '浏览次数' AFTER published_at");
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN created_by BIGINT DEFAULT 0 COMMENT '发布人adminID' AFTER view_count");
                log.info("已迁移articles表补充字段");
            } else {
                log.info("articles表字段已完整，跳过迁移");
            }
        } catch (Exception e) {
            log.warn("articles表字段迁移失败: {}", e.getMessage());
        }

        // ==================== 五维能量系统（账本模式） ====================

        // 维度定义表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS energy_dimension (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能量维度定义表'"
            );
            log.info("已创建energy_dimension表");
        } catch (Exception e) {
            log.warn("创建energy_dimension表失败: {}", e.getMessage());
        }

        // 服务-维度比例配置表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS energy_source_config (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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 '占比(bps,10000=100%)', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_source (source_type, source_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务-维度比例配置表'"
            );
            log.info("已创建energy_source_config表");
        } catch (Exception e) {
            log.warn("创建energy_source_config表失败: {}", e.getMessage());
        }

        // 能量流水表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS energy_log (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "child_id BIGINT NOT NULL COMMENT '孩子ID', " +
                "dimension_id BIGINT NOT NULL COMMENT '维度ID', " +
                "amount INT NOT NULL COMMENT '变动量(正=获得,负=消耗)', " +
                "balance_after INT NOT NULL COMMENT '变动后余额', " +
                "source_type VARCHAR(32) COMMENT '来源类型: task/product/medical_report/daily_record', " +
                "source_id BIGINT COMMENT '来源ID', " +
                "ref_id BIGINT COMMENT '关联旧流水ID(覆盖时标记)', " +
                "expires_at DATETIME COMMENT '过期时间(空=永不过期)', " +
                "description VARCHAR(500) COMMENT '描述', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "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='能量流水表'"
            );
            log.info("已创建energy_log表");
        } catch (Exception e) {
            log.warn("创建energy_log表失败: {}", e.getMessage());
        }

        // 维度余额表
        try {
            jdbcTemplate.execute(
                "CREATE TABLE IF NOT EXISTS energy_balance (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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, " +
                "UNIQUE KEY uk_child_dim (child_id, dimension_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='维度余额表'"
            );
            log.info("已创建energy_balance表");
        } catch (Exception e) {
            log.warn("创建energy_balance表失败: {}", e.getMessage());
        }

        // 维度种子数据（按相生链: 智→富→行→心→身）
        try {
            Integer dimCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM energy_dimension", Integer.class);
            if (dimCount == null || dimCount == 0) {
                jdbcTemplate.execute("INSERT INTO energy_dimension (code, name, icon, element, sort_order, status) VALUES " +
                    "('mind', '心', '🔥', '火', 4, 1), " +
                    "('body', '身', '🌏', '土', 5, 1), " +
                    "('wisdom', '智', '⚔️', '金', 1, 1), " +
                    "('action', '行', '🌿', '木', 3, 1), " +
                    "('wealth', '富', '💧', '水', 2, 1)");
                log.info("能量维度种子数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化能量维度种子数据失败: {}", e.getMessage());
        }

        // 为已有商品插入默认比例配置（与Product.domain一致）
        try {
            Integer productConfigCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM energy_source_config WHERE source_type = 'product'", Integer.class);
            if (productConfigCount == null || productConfigCount == 0) {
                String[][] domainMapping = {
                    {"action", "action"}, {"mind", "mind"}, {"body", "body"},
                    {"wisdom", "wisdom"}, {"wealth", "wealth"}
                };
                for (String[] mapping : domainMapping) {
                    jdbcTemplate.execute(
                        "INSERT INTO energy_source_config (source_type, source_id, source_name, dimension_id, ratio) " +
                        "SELECT 'product', p.id, p.name, d.id, 10000 " +
                        "FROM products p " +
                        "JOIN energy_dimension d ON d.code = '" + mapping[1] + "' " +
                        "WHERE p.domain = '" + mapping[0] + "' " +
                        "AND NOT EXISTS (" +
                        "  SELECT 1 FROM energy_source_config esc " +
                        "  WHERE esc.source_type = 'product' AND esc.source_id = p.id" +
                        ")"
                    );
                }
                log.info("已有商品能量比例配置已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化商品能量比例配置失败: {}", e.getMessage());
        }

        // ==================== 精准营养 P0: health_report_survey ====================
        try {
            jdbcTemplate.execute("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='报告调研问卷'");
            log.info("已创建 health_report_survey 表");
        } catch (Exception e) {
            log.warn("创建 health_report_survey 表失败: {}", e.getMessage());
        }

        // ==================== 精准营养 P0: health_gut_flora + health_disease_risk ====================
        try {
            jdbcTemplate.execute("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='菌群检测专项数据'");
            log.info("已创建 health_gut_flora 表");
        } catch (Exception e) {
            log.warn("创建 health_gut_flora 表失败: {}", e.getMessage());
        }

        try {
            jdbcTemplate.execute("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='疾病风险评估数据'");
            log.info("已创建 health_disease_risk 表");
        } catch (Exception e) {
            log.warn("创建 health_disease_risk 表失败: {}", e.getMessage());
        }

        // ==================== 迁移: health_reports 表添加新字段 ====================
        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN balance_score INT DEFAULT NULL COMMENT '菌群平衡分'");
            log.info("已添加balance_score列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN diversity_score INT DEFAULT NULL COMMENT '菌群多样性分'");
            log.info("已添加diversity_score列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN beneficial_score INT DEFAULT NULL COMMENT '有益菌评分'");
            log.info("已添加beneficial_score列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN harmful_score INT DEFAULT NULL COMMENT '有害菌评分'");
            log.info("已添加harmful_score列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN core_genus_score INT DEFAULT NULL COMMENT '核心菌属评分'");
            log.info("已添加core_genus_score列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移: health_reports 表添加报告编号和姓名字段
        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN report_number VARCHAR(50) DEFAULT NULL COMMENT '报告编号'");
            log.info("已添加report_number列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_reports ADD COLUMN person_name VARCHAR(50) DEFAULT NULL COMMENT '被检测人姓名'");
            log.info("已添加person_name列到health_reports表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // ==================== 迁移: health_gut_flora 表添加新字段 ====================
        try {
            jdbcTemplate.execute("ALTER TABLE health_gut_flora ADD COLUMN population_level VARCHAR(50) DEFAULT NULL COMMENT '处于人群%水平'");
            log.info("已添加population_level列到health_gut_flora表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_gut_flora ADD COLUMN detection_rate VARCHAR(50) DEFAULT NULL COMMENT '%正常人有检出'");
            log.info("已添加detection_rate列到health_gut_flora表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_gut_flora ADD COLUMN description TEXT DEFAULT NULL COMMENT '菌种说明'");
            log.info("已添加description列到health_gut_flora表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE health_gut_flora ADD COLUMN `level` VARCHAR(20) DEFAULT 'GENUS' COMMENT '级别: GENUS/SPECIES'");
            log.info("已添加level列到health_gut_flora表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // ==================== 迁移: guides 表统一 ====================

        // 迁移: guides 添加 type 字段（区分服务商类型）
        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN type VARCHAR(32) DEFAULT 'teacher' COMMENT '服务商类型: teacher/planner/activity_provider/product_supplier'");
            log.info("已添加type列到guides表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移: guides 添加 teachers 合并字段
        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN teacher_no VARCHAR(20) COMMENT '服务商编号'");
            log.info("已添加teacher_no列到guides表");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN title VARCHAR(50) COMMENT '职称/称号'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN specialty VARCHAR(200) COMMENT '专长领域'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN bio TEXT COMMENT '简介'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN experience_years TINYINT DEFAULT 0 COMMENT '从业年限'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN rating INT DEFAULT 500 COMMENT '评分(百分位,500=5.00)'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN total_appointments INT DEFAULT 0 COMMENT '总服务次数'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN street_id BIGINT COMMENT '街道ID'");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN province VARCHAR(50) COMMENT '省份'");
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN city VARCHAR(50) COMMENT '城市'");
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN district VARCHAR(50) COMMENT '区县'");
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN street VARCHAR(100) COMMENT '街道'");
            log.info("已添加地址字段到guides表");
        } catch (Exception e) {}

        try {
            jdbcTemplate.execute("ALTER TABLE guides ADD COLUMN verified_at DATETIME COMMENT '审核通过时间'");
        } catch (Exception e) {}

        // 迁移: 将 teachers 表数据合并到 guides（type='teacher'）
        try {
            int migrated = jdbcTemplate.update(
                "INSERT IGNORE INTO guides (user_id, type, teacher_no, title, specialty, bio, experience_years, rating, total_appointments, " +
                "street_id, province, city, district, street, verified_at, status, level, created_at, updated_at) " +
                "SELECT t.user_id, 'teacher', t.teacher_no, t.title, t.specialty, t.bio, t.experience_years, t.rating, t.total_appointments, " +
                "t.street_id, t.province, t.city, t.district, t.street, t.verified_at, " +
                "CASE WHEN t.status = 'active' THEN 'active' ELSE 'inactive' END, " +
                "CASE WHEN t.verification_status = 'approved' THEN 'senior' WHEN t.verification_status = 'pending' THEN 'junior' ELSE 'junior' END, " +
                "t.created_at, t.updated_at " +
                "FROM teachers t " +
                "LEFT JOIN guides g ON g.user_id = t.user_id " +
                "WHERE g.id IS NULL"
            );
            if (migrated > 0) {
                log.info("已从 teachers 表迁移 {} 条数据到 guides（type=teacher）", migrated);
            }
        } catch (Exception e) {
            log.warn("迁移 teachers → guides 数据失败: {}", e.getMessage());
        }

        // 迁移: dan_assessment_results 表添加认知维度字段（6维认知体系扩展）
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dan_assessment_results' AND COLUMN_NAME = 'perception_score'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE dan_assessment_results " +
                    "ADD COLUMN perception_score INT COMMENT '感知得分', " +
                    "ADD COLUMN spatial_score INT COMMENT '空间思维得分', " +
                    "ADD COLUMN processing_speed_score INT COMMENT '加工速度得分'");
                log.info("已为 dan_assessment_results 表添加认知维度字段");
            } else {
                log.info("dan_assessment_results 认知维度字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加 dan_assessment_results 认知维度字段失败: {}", e.getMessage());
        }

        // 迁移: dan_assessment_results 表添加 EMI 维度字段（v1.2 心智模型重构）
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dan_assessment_results' AND COLUMN_NAME = 'emotion_management_score'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE dan_assessment_results " +
                    "ADD COLUMN emotion_management_score INT COMMENT '情绪管理得分(EMI)', " +
                    "ADD COLUMN empathy_score INT COMMENT '同理心得分(EMI)', " +
                    "ADD COLUMN social_adaptability_score INT COMMENT '社会适应得分(EMI)', " +
                    "ADD COLUMN self_motivation_score INT COMMENT '自我激励得分(EMI)'");
                log.info("已为 dan_assessment_results 表添加 EMI 维度字段");
            } else {
                log.info("dan_assessment_results EMI 维度字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加 dan_assessment_results EMI 维度字段失败: {}", e.getMessage());
        }

        // 迁移: dan_assessment_results 表添加 EMI 心理子维度字段
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dan_assessment_results' AND COLUMN_NAME = 'emotion_score'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE dan_assessment_results " +
                    "ADD COLUMN emotion_score INT COMMENT '情绪商数(0-100)', " +
                    "ADD COLUMN resilience_score INT COMMENT '心理韧性(0-100)', " +
                    "ADD COLUMN stress_coping_score INT COMMENT '压力应对(0-100)', " +
                    "ADD COLUMN self_awareness_score INT COMMENT '自我认知(0-100)'");
                log.info("已为 dan_assessment_results 表添加 EMI 心理子维度字段");
            } else {
                log.info("dan_assessment_results EMI 心理子维度字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加 dan_assessment_results EMI 心理子维度字段失败: {}", e.getMessage());
        }

        // 迁移: dan_assessment_results 表添加大五人格维度字段
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dan_assessment_results' AND COLUMN_NAME = 'openness_score'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE dan_assessment_results " +
                    "ADD COLUMN openness_score INT COMMENT '开放性(0-100)', " +
                    "ADD COLUMN conscientiousness_score INT COMMENT '尽责性(0-100)', " +
                    "ADD COLUMN extraversion_score INT COMMENT '外向性(0-100)', " +
                    "ADD COLUMN agreeableness_score INT COMMENT '宜人性(0-100)', " +
                    "ADD COLUMN neuroticism_score INT COMMENT '神经质(0-100)', " +
                    "ADD COLUMN big_five_overall_score INT COMMENT '大五人格综合分'");
                log.info("已为 dan_assessment_results 表添加大五人格维度字段");
            } else {
                log.info("dan_assessment_results 大五人格维度字段已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加 dan_assessment_results 大五人格维度字段失败: {}", e.getMessage());
        }

        // 迁移: users 表添加出生时辰字段
        try {
            jdbcTemplate.execute("ALTER TABLE users ADD COLUMN birth_hour VARCHAR(10) COMMENT '出生时辰: 子丑寅卯辰巳午未申酉戌亥'");
            log.info("已添加birth_hour列到users表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 迁移: 重建 family_member_attributes 表结构（v1.2 心智模型）
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'family_member_attributes' AND COLUMN_NAME = 'member_id'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                // 旧表存在但结构不同，先删除旧表重建
                jdbcTemplate.execute("DROP TABLE IF EXISTS family_member_attributes_old");
                jdbcTemplate.execute("RENAME TABLE family_member_attributes TO family_member_attributes_old");
                jdbcTemplate.execute("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 '心维度基础分(百分位,500=5.00)', " +
                    "wisdom_base_score INT DEFAULT 0 COMMENT '智维度基础分(百分位,500=5.00)', " +
                    "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='家庭成员先天属性表'");
                log.info("已重建 family_member_attributes 表（v1.2 心智模型）");
            } else {
                log.info("family_member_attributes 表已为 v1.2 结构，跳过");
            }
        } catch (Exception e) {
            log.warn("重建 family_member_attributes 表失败: {}", e.getMessage());
        }

        // 迁移: children 表添加 show_to_family 列（v1.3 家庭成员卡）
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'children' AND COLUMN_NAME = 'show_to_family'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE children ADD COLUMN show_to_family TINYINT DEFAULT 1 COMMENT '是否对家庭成员可见: 1=是, 0=否'");
                log.info("已为 children 表添加 show_to_family 列");
            } else {
                log.info("children 表 show_to_family 列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加 children 表 show_to_family 列失败: {}", e.getMessage());
        }

        // 迁移: users 表添加 show_to_family 列（v1.3 家庭成员卡）
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'show_to_family'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE users ADD COLUMN show_to_family TINYINT DEFAULT 1 COMMENT '是否对家庭成员可见: 1=是, 0=否'");
                log.info("已为 users 表添加 show_to_family 列");
            } else {
                log.info("users 表 show_to_family 列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加 users 表 show_to_family 列失败: {}", e.getMessage());
        }

        // ==================== DanShop 电商模块建表 ====================
        // 1. danshop_categories (商品分类表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_categories (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "parent_id BIGINT DEFAULT NULL COMMENT '父分类ID（null=一级）', " +
                "name VARCHAR(64) NOT NULL COMMENT '分类名称', " +
                "image VARCHAR(512) DEFAULT NULL COMMENT '分类图标URL', " +
                "sort_order INT DEFAULT 0 COMMENT '排序（越小越靠前）', " +
                "energy_dimension_id BIGINT DEFAULT NULL COMMENT '关联五维维度ID', " +
                "status INT DEFAULT 1 COMMENT '1启用/0禁用', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_parent (parent_id), " +
                "INDEX idx_status (status)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表'");
            log.info("已创建 danshop_categories 表");
        } catch (Exception e) {
            log.warn("创建 danshop_categories 表失败: {}", e.getMessage());
        }

        // 2. danshop_products (商品表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_products (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "category_id BIGINT NOT NULL COMMENT '所属分类ID', " +
                "name VARCHAR(128) NOT NULL COMMENT '商品名称', " +
                "description TEXT COMMENT '商品详细描述', " +
                "cover_image VARCHAR(512) COMMENT '封面图URL', " +
                "images TEXT COMMENT '多图(JSON数组)', " +
                "price INT NOT NULL COMMENT '售价(分)', " +
                "member_price INT DEFAULT NULL COMMENT '会员价(分)', " +
                "cost_price INT DEFAULT NULL COMMENT '成本价(分)', " +
                "stock INT DEFAULT 0 COMMENT '库存数量', " +
                "sales_count INT DEFAULT 0 COMMENT '销量', " +
                "product_type VARCHAR(32) NOT NULL COMMENT '类型: physical/virtual/coupon/course', " +
                "vendor_id BIGINT DEFAULT NULL COMMENT '供应商ID', " +
                "vendor_name VARCHAR(64) COMMENT '供应商名称', " +
                "status VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending/approved/rejected/on_shelf/off_shelf', " +
                "reject_reason VARCHAR(255) DEFAULT NULL COMMENT '审核拒绝原因', " +
                "profit_rate INT DEFAULT 0 COMMENT '平台利润率(bps,100=1%)', " +
                "external_source VARCHAR(32) COMMENT '外部商品来源', " +
                "external_id VARCHAR(64) COMMENT '外部商品ID', " +
                "external_data TEXT COMMENT '外部商品完整数据(JSON)', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_category (category_id), " +
                "INDEX idx_vendor (vendor_id), " +
                "INDEX idx_status (status)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表'");
            log.info("已创建 danshop_products 表");
        } catch (Exception e) {
            log.warn("创建 danshop_products 表失败: {}", e.getMessage());
        }

        // 3. danshop_orders (主订单表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_orders (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号(DS+时间戳+6位随机)', " +
                "buyer_id BIGINT NOT NULL COMMENT '买家ID', " +
                "family_id BIGINT DEFAULT NULL COMMENT '家庭ID', " +
                "total_amount INT NOT NULL COMMENT '订单总金额(分)', " +
                "discount_amount INT DEFAULT 0 COMMENT '优惠金额(分)', " +
                "actual_amount INT NOT NULL COMMENT '实付金额(分)', " +
                "status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/paid/shipped/completed/refunding/refunded/cancelled', " +
                "payment_method VARCHAR(16) DEFAULT 'wechat' COMMENT 'wechat/alipay', " +
                "transaction_id VARCHAR(64) DEFAULT NULL COMMENT '微信/支付宝交易号', " +
                "paid_at DATETIME DEFAULT NULL COMMENT '支付时间', " +
                "shipped_at DATETIME DEFAULT NULL COMMENT '发货时间', " +
                "completed_at DATETIME DEFAULT NULL COMMENT '完成时间', " +
                "remark VARCHAR(255) DEFAULT NULL COMMENT '买家备注', " +
                "cancel_reason VARCHAR(255) DEFAULT NULL COMMENT '取消原因', " +
                "receiver_name VARCHAR(32) DEFAULT NULL COMMENT '收件人姓名', " +
                "receiver_phone VARCHAR(16) DEFAULT NULL COMMENT '收件人电话', " +
                "receiver_address VARCHAR(256) DEFAULT NULL COMMENT '收件人地址', " +
                "freight INT DEFAULT 0 COMMENT '运费(分)', " +
                "profit_amount INT DEFAULT 0 COMMENT '分润金额(分)', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_buyer (buyer_id), " +
                "INDEX idx_family (family_id), " +
                "INDEX idx_status (status), " +
                "INDEX idx_order_no (order_no)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主订单表'");
            log.info("已创建 danshop_orders 表");
        } catch (Exception e) {
            log.warn("创建 danshop_orders 表失败: {}", e.getMessage());
        }

        // 4. danshop_order_items (订单明细表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_order_items (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "order_id BIGINT NOT NULL COMMENT '所属订单ID', " +
                "product_id BIGINT NOT NULL COMMENT '商品ID', " +
                "product_name VARCHAR(128) NOT NULL COMMENT '商品名称(快照)', " +
                "cover_image VARCHAR(512) COMMENT '封面图(快照)', " +
                "unit_price INT NOT NULL COMMENT '单价(分,快照)', " +
                "price INT NOT NULL COMMENT '单价(分,别名兼容)', " +
                "quantity INT NOT NULL DEFAULT 1 COMMENT '购买数量', " +
                "subtotal INT NOT NULL COMMENT '小计(分)', " +
                "product_type VARCHAR(32) COMMENT '商品类型(快照)', " +
                "vendor_id BIGINT COMMENT '供应商ID(快照)', " +
                "vendor_name VARCHAR(64) COMMENT '供应商名称(快照)', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_order (order_id), " +
                "INDEX idx_product (product_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表'");
            log.info("已创建 danshop_order_items 表");
        } catch (Exception e) {
            log.warn("创建 danshop_order_items 表失败: {}", e.getMessage());
        }

        // 5. danshop_order_logs (订单操作日志表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_order_logs (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "order_id BIGINT NOT NULL COMMENT '订单ID', " +
                "action VARCHAR(32) NOT NULL COMMENT '操作: create/pay/ship/confirm/refund/cancel', " +
                "operator_id BIGINT DEFAULT NULL COMMENT '操作人ID', " +
                "operator_type VARCHAR(16) DEFAULT NULL COMMENT 'buyer/vendor/admin/system', " +
                "detail TEXT DEFAULT NULL COMMENT '操作详情(JSON)', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_order (order_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单操作日志表'");
            log.info("已创建 danshop_order_logs 表");
        } catch (Exception e) {
            log.warn("创建 danshop_order_logs 表失败: {}", e.getMessage());
        }

        // 6. danshop_logistics (物流信息表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_logistics (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "order_id BIGINT NOT NULL COMMENT '订单ID', " +
                "order_item_id BIGINT DEFAULT NULL COMMENT '订单明细ID', " +
                "logistics_no VARCHAR(64) NOT NULL COMMENT '物流单号', " +
                "logistics_company VARCHAR(32) NOT NULL COMMENT '物流公司编码: sf/yto/zt/sto/yd/ttkd', " +
                "logistics_name VARCHAR(32) DEFAULT NULL COMMENT '物流公司名称', " +
                "sender_name VARCHAR(32) DEFAULT NULL COMMENT '发货人姓名', " +
                "sender_phone VARCHAR(16) DEFAULT NULL COMMENT '发货人电话', " +
                "sender_address VARCHAR(256) DEFAULT NULL COMMENT '发货人地址', " +
                "receiver_name VARCHAR(32) NOT NULL COMMENT '收件人姓名', " +
                "receiver_phone VARCHAR(16) NOT NULL COMMENT '收件人电话', " +
                "receiver_address VARCHAR(256) NOT NULL COMMENT '收件人地址', " +
                "status VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending/shipped/signing/completed', " +
                "tracking_data TEXT DEFAULT NULL COMMENT '物流轨迹(JSON数组)', " +
                "shipped_at DATETIME DEFAULT NULL COMMENT '发货时间', " +
                "signed_at DATETIME DEFAULT NULL COMMENT '签收时间', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_order (order_id), " +
                "INDEX idx_logistics_no (logistics_no), " +
                "INDEX idx_status (status)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物流信息表'");
            log.info("已创建 danshop_logistics 表");
        } catch (Exception e) {
            log.warn("创建 danshop_logistics 表失败: {}", e.getMessage());
        }

        // 7. danshop_profit_rules (分润规则配置表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_profit_rules (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "rule_name VARCHAR(64) NOT NULL COMMENT '规则名称', " +
                "rule_type VARCHAR(32) NOT NULL COMMENT '规则类型: product_category/vendor_level/global', " +
                "target_id BIGINT DEFAULT NULL COMMENT '关联目标ID', " +
                "platform_profit_rate INT NOT NULL COMMENT '平台利润率(bps,100=1%)', " +
                "referrer_l1_rate INT DEFAULT 0 COMMENT '直接推荐人分润比例(bps,占利润%)', " +
                "referrer_l2_rate INT DEFAULT 0 COMMENT '间接推荐人分润比例(bps,占利润%)', " +
                "platform_service_rate INT DEFAULT 0 COMMENT '平台服务费率(bps,占订单金额%)', " +
                "is_active INT DEFAULT 1 COMMENT '1启用/0禁用', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_type (rule_type, target_id), " +
                "INDEX idx_active (is_active)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分润规则配置表'");
            log.info("已创建 danshop_profit_rules 表");
        } catch (Exception e) {
            log.warn("创建 danshop_profit_rules 表失败: {}", e.getMessage());
        }

        // 8. danshop_profit_logs (分润流水表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_profit_logs (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "order_id BIGINT NOT NULL COMMENT '订单ID', " +
                "order_no VARCHAR(32) NOT NULL COMMENT '订单号', " +
                "order_amount INT NOT NULL COMMENT '订单实付金额(分)', " +
                "cost_amount INT DEFAULT 0 COMMENT '成本金额(分)', " +
                "profit_amount INT NOT NULL COMMENT '利润金额(分)', " +
                "platform_retained INT NOT NULL COMMENT '平台留存金额(分)', " +
                "vendor_income INT NOT NULL COMMENT '供应商实收金额(分)', " +
                "commission_l1 INT DEFAULT 0 COMMENT 'L1推荐人佣金(分)', " +
                "commission_l2 INT DEFAULT 0 COMMENT 'L2推荐人佣金(分)', " +
                "rule_id BIGINT DEFAULT NULL COMMENT '使用的分润规则ID', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_order (order_id), " +
                "INDEX idx_order_no (order_no)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分润流水表'");
            log.info("已创建 danshop_profit_logs 表");
        } catch (Exception e) {
            log.warn("创建 danshop_profit_logs 表失败: {}", e.getMessage());
        }

        // 9. danshop_favorites (商品收藏表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_favorites (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                "product_id BIGINT NOT NULL COMMENT '商品ID', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "UNIQUE INDEX idx_user_product (user_id, product_id), " +
                "INDEX idx_user (user_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品收藏表'");
            log.info("已创建 danshop_favorites 表");
        } catch (Exception e) {
            log.warn("创建 danshop_favorites 表失败: {}", e.getMessage());
        }

        // 10. danshop_addresses (收货地址表)
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS danshop_addresses (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                "receiver_name VARCHAR(32) NOT NULL COMMENT '收货人姓名', " +
                "phone VARCHAR(16) NOT NULL COMMENT '收货人电话', " +
                "province VARCHAR(32) NOT NULL COMMENT '省份', " +
                "city VARCHAR(32) NOT NULL COMMENT '城市', " +
                "district VARCHAR(32) NOT NULL COMMENT '区县', " +
                "street VARCHAR(256) NOT NULL COMMENT '详细地址', " +
                "is_default INT DEFAULT 0 COMMENT '是否默认地址 1=是 0=否', " +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_user (user_id), " +
                "INDEX idx_user_default (user_id, is_default)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表'");
            log.info("已创建 danshop_addresses 表");
        } catch (Exception e) {
            log.warn("创建 danshop_addresses 表失败: {}", e.getMessage());
        }

        // ==================== 迁移: activities表添加visible_scope列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'activities' AND COLUMN_NAME = 'visible_scope'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE activities ADD COLUMN visible_scope VARCHAR(20) DEFAULT NULL COMMENT '可见范围: family(家庭)/role(角色)/user(用户)' AFTER visibility");
                log.info("已添加visible_scope列到activities表");
            } else {
                log.info("activities表visible_scope列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加activities表visible_scope列失败: {}", e.getMessage());
        }

        // ==================== 迁移: articles表添加article_type列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'articles' AND COLUMN_NAME = 'article_type'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN article_type VARCHAR(20) DEFAULT 'normal' COMMENT '文章类型: normal(普通)/premium(优质)/original(原创)' AFTER read_time");
                log.info("已添加article_type列到articles表");
            } else {
                log.info("articles表article_type列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加articles表article_type列失败: {}", e.getMessage());
        }

        // ==================== 迁移: articles表添加word_count列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'articles' AND COLUMN_NAME = 'word_count'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE articles ADD COLUMN word_count INT DEFAULT 0 COMMENT '文章字数' AFTER article_type");
                log.info("已添加word_count列到articles表");
            } else {
                log.info("articles表word_count列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加articles表word_count列失败: {}", e.getMessage());
        }

        // ==================== 迁移: article_reading_records表添加task_id列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'article_reading_records' AND COLUMN_NAME = 'task_id'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE article_reading_records ADD COLUMN task_id BIGINT DEFAULT NULL COMMENT '关联的任务ID' AFTER duration_seconds");
                log.info("已添加task_id列到article_reading_records表");
            } else {
                log.info("article_reading_records表task_id列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加article_reading_records表task_id列失败: {}", e.getMessage());
        }

        // ==================== 迁移: tasks表添加source_type列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tasks' AND COLUMN_NAME = 'source_type'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN source_type VARCHAR(20) DEFAULT NULL COMMENT '来源类型: article/activity/manual' AFTER minigame_code");
                log.info("已添加source_type列到tasks表");
            } else {
                log.info("tasks表source_type列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加tasks表source_type列失败: {}", e.getMessage());
        }

        // ==================== 迁移: tasks表添加source_id列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tasks' AND COLUMN_NAME = 'source_id'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE tasks ADD COLUMN source_id BIGINT DEFAULT NULL COMMENT '来源ID' AFTER source_type");
                log.info("已添加source_id列到tasks表");
            } else {
                log.info("tasks表source_id列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加tasks表source_id列失败: {}", e.getMessage());
        }

        // ==================== 迁移: activities表添加checkin_points列 ====================
        try {
            Integer colExists = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'activities' AND COLUMN_NAME = 'checkin_points'",
                Integer.class);
            if (colExists == null || colExists == 0) {
                jdbcTemplate.execute("ALTER TABLE activities ADD COLUMN checkin_points INT DEFAULT 0 COMMENT '签到积分(0=使用系统默认)' AFTER price");
                log.info("已添加checkin_points列到activities表");
            } else {
                log.info("activities表checkin_points列已存在，跳过");
            }
        } catch (Exception e) {
            log.warn("检查/添加activities表checkin_points列失败: {}", e.getMessage());
        }

        // 迁移: 创建family_invitations表（家庭邀请令牌）
        try {
            jdbcTemplate.execute("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 '过期时间', " +
                    "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='家庭邀请令牌'");
            log.info("已创建family_invitations表");
        } catch (Exception e) {
            log.warn("创建family_invitations表失败: {}", e.getMessage());
        }

        log.info("数据库迁移完成");
    }

    private void initializeDefaultData() {
        // 初始化预设奖励模板
        try {
            // 检查是否已有预设奖励
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM rewards WHERE is_template = 1", Integer.class);
            
if (count == null || count == 0) {
// 插入预设奖励模板（child_id为NULL表示模板）
jdbcTemplate.execute("INSERT 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)");
log.info("预设奖励模板已初始化");
}
        } catch (Exception e) {
            log.warn("初始化预设奖励失败: {}", e.getMessage());
        }

        // 初始化奖励分类
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM reward_categories WHERE family_id = 0", Integer.class);
            
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO reward_categories (family_id, name, icon, sort_order) VALUES " +
                    "(0, '物质类', 'gift', 1), " +
                    "(0, '精神类', 'star', 2), " +
                    "(0, '特权类', 'crown', 3)");
                log.info("奖励分类已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化奖励分类失败: {}", e.getMessage());
        }

        // 初始化打卡里程碑
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM streak_milestones", Integer.class);
            
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO streak_milestones (days, reward_points) VALUES " +
                    "(3, 5), (7, 10), (30, 30), (100, 100)");
                log.info("打卡里程碑已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化打卡里程碑失败: {}", e.getMessage());
        }

        // 初始化勋章定义
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM badge_definitions", Integer.class);
            
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO badge_definitions (badge_type, name, description, icon, condition_type, condition_value, reward_points) VALUES " +
                    "('first_task', '初次完成任务', '首次完成任意任务', 'star', 'task_count', 1, 0), " +
                    "('streak_3', '连续3天', '连续3天按时完成任务', 'fire', 'streak_days', 3, 5), " +
                    "('streak_7', '连续7天', '连续7天按时完成任务', 'fire', 'streak_days', 7, 10), " +
                    "('points_50', '积分达人', '一周获得50积分', 'trophy', 'weekly_points', 50, 0), " +
                    "('task_100', '任务达人', '完成100个任务', 'medal', 'task_count', 100, 20)");
                log.info("勋章定义已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化勋章定义失败: {}", e.getMessage());
        }

        // 初始化小游戏数据
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM mini_games", Integer.class);
            
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO mini_games (game_code, game_name, description, icon, default_duration, default_points, game_url, sort_order, status) VALUES " +
                    "('schulte', '舒尔特方格', '训练专注力和眼动能力，找到1-25按顺序点击', '🎯', 5, 5, '/pages/games/schulte', 1, 1), " +
                    "('1a2b', '猜数字', '猜对数字和位置获得积分，锻炼逻辑思维', '🔢', 10, 5, '/pages/games/1a2b', 2, 1), " +
                    "('sudoku', '数独', '填充数字使每行每列每个宫格都包含1-9', '🔳', 15, 8, '/pages/games/sudoku', 3, 1)");
                log.info("小游戏数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化小游戏数据失败: {}", e.getMessage());
        }

// 初始化默认管理员
try {
  // 先检查并添加role列（如果不存在）
  try {
    jdbcTemplate.execute("ALTER TABLE admins ADD COLUMN role VARCHAR(20) DEFAULT \'admin\' AFTER email");
    log.info("已添加role列到admins表");
  } catch (Exception e) {
    // 列已存在，忽略错误
  }

  Integer count = jdbcTemplate.queryForObject(
  "SELECT COUNT(*) FROM admins", Integer.class);

  if (count == null || count == 0) {
    // 默认管理员: 手机号 13800138000, 密码 admin123
    String encryptedPassword = org.springframework.util.DigestUtils.md5DigestAsHex("xzyj_admin_salt_admin123".getBytes());
    jdbcTemplate.execute("INSERT INTO admins (username, password, real_name, phone, email, role, status) VALUES " +
    "(\'admin\', \'" + encryptedPassword + "\', \'系统管理员\', \'13800138000\', \'admin@xzyj.com\', \'admin\', 1)");
    log.info("默认管理员已初始化: 手机号 13800138000, 密码 admin123");
  }
} catch (Exception e) {
  log.warn("初始化默认管理员失败: {}", e.getMessage());
}

    // 初始化成长规划师WEB管理账号
    try {
      Integer teacherCount = jdbcTemplate.queryForObject(
        "SELECT COUNT(*) FROM admins WHERE username = 'teacher'", Integer.class);
      if (teacherCount == null || teacherCount == 0) {
        String teacherPassword = org.springframework.util.DigestUtils.md5DigestAsHex("xzyj_admin_salt_teacher123".getBytes());
        jdbcTemplate.execute("INSERT INTO admins (username, password, real_name, phone, role, status) VALUES " +
          "('teacher', '" + teacherPassword + "', '成长规划师', '13800138003', 'teacher', 1)");
        log.info("成长规划师管理账号已初始化: 用户名 teacher, 密码 teacher123");
      }
    } catch (Exception e) {
      log.warn("初始化成长规划师管理账号失败: {}", e.getMessage());
    }

        // 初始化任务模板示例数据
        try {
            Integer count = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM admin_task_templates", Integer.class);

            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO admin_task_templates (title, description, points, category, need_review, recommended_age, difficulty, is_active) VALUES " +
                        "('完成作业', '按时完成学校布置的家庭作业', 10, '学习', 1, 6, 'medium', 1), " +
                        "('阅读30分钟', '自主阅读课外书籍30分钟', 8, '阅读', 0, 5, 'easy', 1), " +
                        "('整理房间', '收拾整理自己的房间和书桌', 6, '家务', 0, 6, 'easy', 1), " +
                        "('洗碗', '饭后帮忙清洗餐具', 5, '家务', 1, 8, 'medium', 1), " +
                        "('户外运动1小时', '进行跑步、球类等户外运动', 12, '运动', 0, 7, 'hard', 1), " +
                        "('练习书法', '练习毛笔或硬笔书法20分钟', 7, '学习', 0, 7, 'medium', 1), " +
                        "('早睡早起', '晚上9点前睡觉，早上7点前起床', 5, '生活习惯', 0, 5, 'easy', 1), " +
                        "('背诵古诗', '背诵一首古诗词', 8, '学习', 1, 6, 'medium', 1)");
                log.info("任务模板示例数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化任务模板失败: {}", e.getMessage());
        }

        // 初始化街道数据示例（广东省广州市）
        try {
            Integer count = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM streets", Integer.class);

            if (count == null || count == 0) {
                // 省
                jdbcTemplate.execute("INSERT INTO streets (province, province_code, full_name, level, parent_id, sort_order) VALUES " +
                        "('广东省', '44', '广东省', 1, NULL, 1)");
                
                // 市
                jdbcTemplate.execute("INSERT INTO streets (province, province_code, city, city_code, full_name, level, parent_id, sort_order) VALUES " +
                        "('广东省', '44', '广州市', '4401', '广东省广州市', 2, 1, 1), " +
                        "('广东省', '44', '深圳市', '4403', '广东省深圳市', 2, 1, 2), " +
                        "('广东省', '44', '珠海市', '4404', '广东省珠海市', 2, 1, 3)");
                
                // 区
                jdbcTemplate.execute("INSERT INTO streets (province, province_code, city, city_code, district, district_code, full_name, level, parent_id, sort_order) VALUES " +
                        "('广东省', '44', '广州市', '4401', '天河区', '440106', '广东省广州市天河区', 3, 2, 1), " +
                        "('广东省', '44', '广州市', '4401', '越秀区', '440104', '广东省广州市越秀区', 3, 2, 2), " +
                        "('广东省', '44', '广州市', '4401', '番禺区', '440113', '广东省广州市番禺区', 3, 2, 3), " +
                        "('广东省', '44', '深圳市', '4403', '南山区', '440305', '广东省深圳市南山区', 3, 3, 1), " +
                        "('广东省', '44', '深圳市', '4403', '福田区', '440304', '广东省深圳市福田区', 3, 3, 2)");
                
                // 街道
                jdbcTemplate.execute("INSERT INTO streets (province, province_code, city, city_code, district, district_code, street, street_code, full_name, level, parent_id, sort_order) VALUES " +
                        "('广东省', '44', '广州市', '4401', '天河区', '440106', '天河南街道', '440106001', '广东省广州市天河区天河南街道', 4, 4, 1), " +
                        "('广东省', '44', '广州市', '4401', '天河区', '440106', '沙河街道', '440106002', '广东省广州市天河区沙河街道', 4, 4, 2), " +
                        "('广东省', '44', '广州市', '4401', '越秀区', '440104', '东山街道', '440104001', '广东省广州市越秀区东山街道', 4, 5, 1), " +
                        "('广东省', '44', '广州市', '4401', '番禺区', '440113', '市桥街道', '440113001', '广东省广州市番禺区市桥街道', 4, 6, 1), " +
                        "('广东省', '44', '深圳市', '4403', '南山区', '440305', '南山街道', '440305001', '广东省深圳市南山区南山街道', 4, 7, 1), " +
                        "('广东省', '44', '深圳市', '4403', '福田区', '440304', '福田街道', '440304001', '广东省深圳市福田区福田街道', 4, 8, 1)");
                
                log.info("街道示例数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化街道数据失败: {}", e.getMessage());
        }

try {
  Integer count = jdbcTemplate.queryForObject(
  "SELECT COUNT(*) FROM admins", Integer.class);

  if (count == null || count == 0) {
    // 默认管理员: 手机号 13800138000, 密码 admin123
    String encryptedPassword = org.springframework.util.DigestUtils.md5DigestAsHex("xzyj_admin_salt_admin123".getBytes());
    jdbcTemplate.execute("INSERT INTO admins (username, password, real_name, phone, email, role, status) VALUES " +
    "('admin', '" + encryptedPassword + "', '系统管理员', '13800138000', 'admin@xzyj.com', 'admin', 1)");
    log.info("默认管理员已初始化: 手机号 13800138000, 密码 admin123");
  }
} catch (Exception e) {
  log.warn("初始化任务模板失败: {}", e.getMessage());
}

// 初始化 users 表测试用户
try {
  // 创建默认家庭（如果不存在）
  Integer familyCount = jdbcTemplate.queryForObject(
    "SELECT COUNT(*) FROM families WHERE id = 1", Integer.class);
  if (familyCount == null || familyCount == 0) {
    jdbcTemplate.execute("INSERT INTO families (id, name, invite_code) VALUES (1, '默认家庭', '888888')");
    log.info("默认家庭已创建");
  }

  // 密码加密: MD5("xzyj_salt_" + password)
  String adminPassword = org.springframework.util.DigestUtils.md5DigestAsHex("xzyj_salt_admin123".getBytes());
  String teacherPassword = org.springframework.util.DigestUtils.md5DigestAsHex("xzyj_salt_teacher123".getBytes());

  // 创建管理员用户（管理员角色，密码 admin123）
  Integer adminUserCount = jdbcTemplate.queryForObject(
    "SELECT COUNT(*) FROM users WHERE phone = '13800138000'", Integer.class);
  if (adminUserCount == null || adminUserCount == 0) {
    jdbcTemplate.execute("INSERT INTO users (openid, family_id, role, phone, nickname, real_name, password) VALUES " +
      "('seed_admin_13800138000', 1, 'admin', '13800138000', '系统管理员', '系统管理员', '" + adminPassword + "')");
    log.info("测试管理员用户已创建: 13800138000 (admin角色, 密码 admin123)");
  }

  // 创建测试教师用户
  Integer teacherUserCount = jdbcTemplate.queryForObject(
    "SELECT COUNT(*) FROM users WHERE phone = '13800138001'", Integer.class);
  if (teacherUserCount == null || teacherUserCount == 0) {
    jdbcTemplate.execute("INSERT INTO users (openid, family_id, role, phone, nickname, real_name, password) VALUES " +
      "('seed_teacher_13800138001', 1, 'teacher', '13800138001', '测试教师', '测试教师', '" + teacherPassword + "')");
    log.info("测试教师用户已创建: 13800138001 (教师角色, 密码 teacher123)");
  }
} catch (Exception e) {
  log.warn("初始化测试用户失败: {}", e.getMessage());
}

        // 初始化星座配置种子数据
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM zodiac_config", Integer.class);
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO zodiac_config (zodiac_code, zodiac_name, element, start_month, start_day, end_month, end_day, mind_base, wisdom_base, body_modifier, action_modifier, wealth_modifier) VALUES " +
                    "('aries',       '白羊座', '火', 3, 21, 4, 19, 65, 70, 1.10, 1.05, 0.95), " +
                    "('taurus',      '金牛座', '土', 4, 20, 5, 20, 60, 65, 1.05, 1.10, 1.15), " +
                    "('gemini',      '双子座', '金', 5, 21, 6, 21, 70, 80, 0.95, 1.15, 0.90), " +
                    "('cancer',      '巨蟹座', '水', 6, 22, 7, 22, 75, 60, 1.00, 0.90, 1.05), " +
                    "('leo',         '狮子座', '火', 7, 23, 8, 22, 65, 75, 1.15, 1.00, 1.10), " +
                    "('virgo',       '处女座', '土', 8, 23, 9, 22, 55, 85, 0.95, 1.05, 1.00), " +
                    "('libra',       '天秤座', '金', 9, 23, 10, 23, 70, 75, 0.90, 0.95, 1.10), " +
                    "('scorpio',     '天蝎座', '水', 10, 24, 11, 22, 80, 70, 1.10, 1.00, 1.15), " +
                    "('sagittarius', '射手座', '火', 11, 23, 12, 21, 60, 65, 1.15, 1.15, 0.95), " +
                    "('capricorn',   '摩羯座', '土', 12, 22, 1, 19, 55, 75, 1.05, 1.10, 1.20), " +
                    "('aquarius',    '水瓶座', '金', 1, 20, 2, 18, 75, 85, 0.95, 0.90, 1.00), " +
                    "('pisces',      '双鱼座', '水', 2, 19, 3, 20, 80, 60, 0.90, 0.95, 0.95)");
                log.info("星座配置种子数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化星座配置失败: {}", e.getMessage());
        }

        // 初始化八字配置种子数据（十天干 + 十二地支）
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM bazi_config", Integer.class);
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO bazi_config (stem_or_branch, type, element, yin_yang, mind_base, wisdom_base, body_modifier, action_modifier, wealth_modifier) VALUES " +
                    "('甲', 'stem', '木', 'yang', 60, 75, 1.05, 1.10, 1.00), " +
                    "('乙', 'stem', '木', 'yin',  65, 70, 0.95, 1.05, 1.00), " +
                    "('丙', 'stem', '火', 'yang', 75, 65, 1.10, 1.00, 0.95), " +
                    "('丁', 'stem', '火', 'yin',  70, 60, 1.05, 0.95, 1.00), " +
                    "('戊', 'stem', '土', 'yang', 60, 65, 1.15, 1.05, 1.10), " +
                    "('己', 'stem', '土', 'yin',  55, 60, 1.10, 1.00, 1.15), " +
                    "('庚', 'stem', '金', 'yang', 65, 80, 1.05, 1.10, 1.05), " +
                    "('辛', 'stem', '金', 'yin',  60, 75, 0.95, 1.00, 1.10), " +
                    "('壬', 'stem', '水', 'yang', 70, 70, 1.00, 0.95, 1.15), " +
                    "('癸', 'stem', '水', 'yin',  75, 65, 0.95, 0.90, 1.10), " +
                    "('子', 'branch', '水', 'yang', 70, 65, 1.00, 0.95, 1.05), " +
                    "('丑', 'branch', '土', 'yin',  55, 60, 1.10, 1.05, 1.10), " +
                    "('寅', 'branch', '木', 'yang', 65, 75, 1.15, 1.10, 1.00), " +
                    "('卯', 'branch', '木', 'yin',  60, 70, 1.05, 1.05, 0.95), " +
                    "('辰', 'branch', '土', 'yang', 60, 65, 1.10, 1.00, 1.10), " +
                    "('巳', 'branch', '火', 'yin',  75, 70, 1.05, 0.95, 1.00), " +
                    "('午', 'branch', '火', 'yang', 80, 65, 1.15, 1.00, 0.95), " +
                    "('未', 'branch', '土', 'yin',  55, 60, 1.05, 1.00, 1.05), " +
                    "('申', 'branch', '金', 'yang', 65, 80, 1.00, 1.10, 1.05), " +
                    "('酉', 'branch', '金', 'yin',  60, 75, 0.95, 1.05, 1.10), " +
                    "('戌', 'branch', '土', 'yang', 60, 65, 1.10, 1.00, 1.15), " +
                    "('亥', 'branch', '水', 'yin',  70, 60, 1.00, 0.95, 1.05)");
                log.info("八字配置种子数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化八字配置失败: {}", e.getMessage());
        }

        // 初始化血型配置种子数据
        try {
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM blood_type_config", Integer.class);
            if (count == null || count == 0) {
                jdbcTemplate.execute("INSERT INTO blood_type_config (blood_type, description, mind_base, wisdom_base, body_modifier, action_modifier, wealth_modifier) VALUES " +
                    "('A',  '认真负责、严谨细致，但易焦虑', 70, 75, 0.95, 1.00, 1.05), " +
                    "('B',  '乐观开朗、好奇心强，但缺乏耐心', 65, 70, 1.05, 1.10, 0.95), " +
                    "('AB', '理性冷静、适应力强，但优柔寡断', 75, 80, 1.00, 0.95, 1.10), " +
                    "('O',  '自信果断、行动力强，但固执己见', 60, 65, 1.15, 1.15, 1.15)");
                log.info("血型配置种子数据已初始化");
            }
        } catch (Exception e) {
            log.warn("初始化血型配置失败: {}", e.getMessage());
        }

        // 初始化成长规划师模块相关表
        initGuideModuleTables(jdbcTemplate);
    }

    private void initGuideModuleTables(JdbcTemplate jdbcTemplate) {
        // 服务类型表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS service_types (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "name VARCHAR(100) NOT NULL COMMENT '类型名称', " +
                "icon VARCHAR(50) COMMENT '图标', " +
                "description VARCHAR(500) COMMENT '描述', " +
                "sort_order INT DEFAULT 0 COMMENT '排序', " +
                "status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态:ACTIVE/DISABLED', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建service_types表失败: {}", e.getMessage());
        }

        // 服务内容表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS service_contents (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建service_contents表失败: {}", e.getMessage());
        }

        // 套餐模板表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_package_templates (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "name VARCHAR(100) NOT NULL COMMENT '模板名称', " +
                "validity_months INT COMMENT '有效期(月)', " +
                "reference_price INT COMMENT '参考价格(分)', " +
                "min_price INT COMMENT '最低售价(分)', " +
                "description VARCHAR(500) COMMENT '说明', " +
                "status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态:ACTIVE/DISABLED', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_package_templates表失败: {}", e.getMessage());
        }

        // 套餐模板服务项表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS package_template_services (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "template_id BIGINT NOT NULL COMMENT '关联模板', " +
                "content_id BIGINT NOT NULL COMMENT '关联服务内容', " +
                "quantity INT DEFAULT 1 COMMENT '数量', " +
                "unit VARCHAR(20) COMMENT '单位' " +
                ")");
        } catch (Exception e) {
            log.warn("创建package_template_services表失败: {}", e.getMessage());
        }

        // 成长规划师套餐表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_packages (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "teacher_id BIGINT NOT NULL COMMENT '成长规划师ID', " +
                "template_id BIGINT COMMENT '关联模板', " +
                "name VARCHAR(100) NOT NULL COMMENT '套餐名称', " +
                "price INT NOT NULL COMMENT '销售价格(分)', " +
                "validity_months INT COMMENT '有效期(月)', " +
                "description VARCHAR(500) COMMENT '套餐说明', " +
                "status VARCHAR(20) DEFAULT 'DRAFT' COMMENT '状态:DRAFT/PUBLISHED/PAUSED', " +
                "sales_count INT DEFAULT 0 COMMENT '销量', " +
                "share_code VARCHAR(50) COMMENT '分享码', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_packages表失败: {}", e.getMessage());
        }

        // 迁移：guide_packages 表添加缺失列
        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages ADD COLUMN guide_id BIGINT COMMENT '成长规划师ID' AFTER id");
        } catch (Exception e) {
        }

        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages ADD COLUMN package_id BIGINT COMMENT '关联模板' AFTER guide_id");
        } catch (Exception e) {
        }

        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages ADD COLUMN commission_rate INT COMMENT '佣金比例(bps,100=1%)' AFTER package_id");
        } catch (Exception e) {
        }

        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages MODIFY COLUMN teacher_id BIGINT NULL");
        } catch (Exception e) {
        }

        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages MODIFY COLUMN template_id BIGINT NULL");
        } catch (Exception e) {
        }

        // 迁移：guide_packages 表添加 level 列（GuidePackage 实体需要）
        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages ADD COLUMN level VARCHAR(20) COMMENT '套餐等级' AFTER name");
        } catch (Exception e) {
        }

        // 迁移：guide_packages 表添加 validity_days 列
        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages ADD COLUMN validity_days INT DEFAULT 0 COMMENT '有效期天数' AFTER validity_months");
        } catch (Exception e) {
        }

        // 迁移：guide_packages 表添加 sort_order 列
        try {
            jdbcTemplate.execute("ALTER TABLE guide_packages ADD COLUMN sort_order INT DEFAULT 0 COMMENT '排序' AFTER status");
        } catch (Exception e) {
        }

        // 成长规划师套餐服务项表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_package_services (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "package_id BIGINT NOT NULL COMMENT '关联套餐', " +
                "content_id BIGINT NOT NULL COMMENT '关联服务内容', " +
                "quantity INT DEFAULT 1 COMMENT '数量', " +
                "unit VARCHAR(20) COMMENT '单位' " +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_package_services表失败: {}", e.getMessage());
        }

        // 订单表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_orders (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_orders表失败: {}", e.getMessage());
        }

        // 佣金明细表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_commissions (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "teacher_id BIGINT NOT NULL COMMENT '成长规划师ID', " +
                "order_id BIGINT NOT NULL COMMENT '订单ID', " +
                "amount INT NOT NULL COMMENT '佣金金额(分)', " +
                "rate INT COMMENT '佣金比例(bps,100=1%)', " +
                "status VARCHAR(20) DEFAULT 'PENDING' COMMENT '状态:PENDING/COMPLETED', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_commissions表失败: {}", e.getMessage());
        }

        // 服务记录表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_service_logs (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "teacher_id BIGINT NOT NULL COMMENT '成长规划师ID', " +
                "family_id BIGINT NOT NULL COMMENT '家庭ID', " +
                "order_id BIGINT COMMENT '订单ID', " +
                "service_content_id BIGINT NOT NULL COMMENT '服务内容ID', " +
                "notes VARCHAR(500) COMMENT '服务记录', " +
                "duration INT COMMENT '服务时长(分钟)', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_service_logs表失败: {}", e.getMessage());
        }

        // 成长规划师邀请码表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS guide_invite_codes (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "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, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ")");
        } catch (Exception e) {
            log.warn("创建guide_invite_codes表失败: {}", e.getMessage());
        }

        // 迁移29: assessment_orders表添加appointment_id字段
        try {
            jdbcTemplate.execute("ALTER TABLE assessment_orders ADD COLUMN appointment_id BIGINT COMMENT '关联的预约ID'");
            log.info("已添加appointment_id列到assessment_orders表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 财富维度: 财商打卡记录表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS finance_checkins (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                "child_id BIGINT NOT NULL COMMENT '孩子ID', " +
                "checkin_type VARCHAR(20) NOT NULL COMMENT '打卡类型: daily_plan/saving/expense/review', " +
                "checkin_date DATE NOT NULL COMMENT '打卡日期', " +
                "amount INT COMMENT '金额(分,收支记录)', " +
                "category VARCHAR(50) COMMENT '收支分类', " +
                "note VARCHAR(200) COMMENT '备注', " +
                "mood VARCHAR(20) COMMENT '心情', " +
                "earned_energy INT DEFAULT 0 COMMENT '获得的能量值', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "INDEX idx_user_date (user_id, checkin_date), " +
                "INDEX idx_child (child_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财商打卡记录表'");
            log.info("已创建finance_checkins表");
        } catch (Exception e) {
            log.warn("创建finance_checkins表失败: {}", e.getMessage());
        }

        // 财富维度: 保单管理表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS insurance_policies (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                "child_id BIGINT COMMENT '关联孩子ID', " +
                "policy_name VARCHAR(100) NOT NULL COMMENT '保单名称', " +
                "insurance_company VARCHAR(100) COMMENT '保险公司', " +
                "policy_type VARCHAR(50) COMMENT '保单类型: medical/life/accident/education/endowment', " +
                "insured_person VARCHAR(50) NOT NULL COMMENT '被保人姓名', " +
                "premium INT COMMENT '保费(分)', " +
                "sum_insured INT COMMENT '保额(分)', " +
                "start_date DATE COMMENT '生效日期', " +
                "end_date DATE COMMENT '到期日期', " +
                "renewal_type VARCHAR(20) DEFAULT 'annual' COMMENT '续费类型: annual/monthly/single', " +
                "status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/expired/cancelled', " +
                "policy_no VARCHAR(100) COMMENT '保单号', " +
                "remark VARCHAR(500) COMMENT '备注', " +
                "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "INDEX idx_user (user_id), " +
                "INDEX idx_child (child_id), " +
                "INDEX idx_status (status)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='保单管理表'");
            log.info("已创建insurance_policies表");
        } catch (Exception e) {
            log.warn("创建insurance_policies表失败: {}", e.getMessage());
        }

        // ==================== 健康检测报告模块 ====================
        // 健康报告表（体检报告/菌群检测报告）
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS health_reports (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                    "family_id BIGINT COMMENT '家庭ID', " +
                    "report_type VARCHAR(32) NOT NULL COMMENT '报告类型: physical_exam(体检)/gut_flora(菌群检测)', " +
                    "report_date DATE COMMENT '报告日期', " +
                    "overall_score INT COMMENT '健康总分(0-100)', " +
                    "gut_health_score INT COMMENT '菌群健康分', " +
                    "chronic_disease_score INT COMMENT '慢病控制分', " +
                    "nutrition_score INT COMMENT '营养均衡分', " +
                    "gut_age VARCHAR(32) COMMENT '肠道预测年龄', " +
                    "gut_type VARCHAR(50) COMMENT '肠型', " +
                    "file_url VARCHAR(500) COMMENT '报告文件URL', " +
                    "status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/archived', " +
                    "remark VARCHAR(500) COMMENT '备注', " +
                    "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                    "INDEX idx_user_id (user_id), " +
                    "INDEX idx_family_id (family_id), " +
                    "INDEX idx_report_type (report_type)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康检测报告表'");
            log.info("已创建health_reports表");
        } catch (Exception e) {
            log.warn("创建health_reports表可能已存在: {}", e.getMessage());
        }

        // 健康指标明细表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS health_indicators (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "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, " +
                    "INDEX idx_report_id (report_id), " +
                    "INDEX idx_category (category)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康指标明细表'");
            log.info("已创建health_indicators表");
        } catch (Exception e) {
            log.warn("创建health_indicators表可能已存在: {}", e.getMessage());
        }

        // ---------- 健康报告表 新增菌群摘要字段 ----------
        try {
            jdbcTemplate.execute("ALTER TABLE health_reports " +
                    "ADD COLUMN gut_balance_score INT COMMENT '菌群平衡分(0-100)', " +
                    "ADD COLUMN gut_diversity_score INT COMMENT '菌群多样性分(0-100)', " +
                    "ADD COLUMN beneficial_bacteria_score INT COMMENT '有益菌分(0-100)', " +
                    "ADD COLUMN harmful_bacteria_score INT COMMENT '有害菌分(0-100)', " +
                    "ADD COLUMN core_species_score INT COMMENT '核心菌属分(0-100)', " +
                    "ADD COLUMN health_rating VARCHAR(100) COMMENT '总分评级，如亚健康/优秀等'");
            log.info("已为health_reports表添加6个菌群摘要字段");
        } catch (Exception e) {
            log.warn("为health_reports表添加字段可能已存在: {}", e.getMessage());
        }

        // ---------- 健康指标表 新增symptoms字段 ----------
        try {
            jdbcTemplate.execute("ALTER TABLE health_indicators ADD COLUMN symptoms TEXT COMMENT '过量/缺乏相关症状描述'");
            log.info("已为health_indicators表添加symptoms字段");
        } catch (Exception e) {
            log.warn("为health_indicators表添加symptoms字段可能已存在: {}", e.getMessage());
        }

        log.info("成长规划师模块、财富维度及健康检测报告数据库表初始化完成");

        // 健康打卡表
        try {
            jdbcTemplate.execute("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='健康打卡表'");
            log.info("已创建health_checkins表");
        } catch (Exception e) {
            log.warn("创建health_checkins表可能已存在: {}", e.getMessage());
        }

        // 迁移31: 创建联系人表
        try {
            jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS contacts (" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                "user_id BIGINT NOT NULL COMMENT '用户ID', " +
                "name VARCHAR(100) NOT NULL COMMENT '姓名', " +
                "phone VARCHAR(20) COMMENT '电话', " +
                "avatar VARCHAR(500) COMMENT '头像', " +
                "relationship_type VARCHAR(20) COMMENT '关系类型: family/friend/partner/colleague/other', " +
                "known_since DATETIME COMMENT '认识时间', " +
                "bio VARCHAR(500) COMMENT '简介', " +
                "birthday DATE COMMENT '生日', " +
                "intimacy_level INT DEFAULT 0 COMMENT '亲密度(0-100)', " +
                "contact_source VARCHAR(20) DEFAULT 'manual' COMMENT '来源: phone/manual', " +
                "tags VARCHAR(500) COMMENT '标签(逗号分隔)', " +
                "last_contact_at DATETIME COMMENT '最近联系时间', " +
                "contact_count INT DEFAULT 0 COMMENT '联系次数', " +
                "notes TEXT COMMENT '备注', " +
                "created_at DATETIME, " +
                "updated_at DATETIME, " +
                "INDEX idx_user_id (user_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='联系人表'");
            log.info("已创建contacts联系表");
        } catch (Exception e) {
            log.warn("创建contacts表可能已存在: {}", e.getMessage());
        }

        // 创建文章阅读记录表
        try {
            jdbcTemplate.execute("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='文章阅读记录表'");
            log.info("已创建article_reading_records表");
        } catch (Exception e) {
            log.warn("创建article_reading_records表可能已存在: {}", e.getMessage());
        }

        // 迁移30: activities表添加visibility和visible_to字段
        try {
            jdbcTemplate.execute("ALTER TABLE activities ADD COLUMN visibility VARCHAR(20) DEFAULT 'all' COMMENT '可见性: all/member'");
            log.info("已添加visibility列到activities表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        try {
            jdbcTemplate.execute("ALTER TABLE activities ADD COLUMN visible_to VARCHAR(255) COMMENT '可见角色列表(逗号分隔)'");
            log.info("已添加visible_to列到activities表");
        } catch (Exception e) {
            // 列已存在，忽略错误
        }

        // 创建维度配置表
        try {
            jdbcTemplate.execute("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='维度配置表'");
            log.info("已创建product_dimension_config表");
        } catch (Exception e) {
            log.warn("创建product_dimension_config表可能已存在: {}", e.getMessage());
        }

        // 创建知识库表
        try {
            jdbcTemplate.execute("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='知识库表'");
            log.info("已创建dan_knowledge_base表");
        } catch (Exception e) {
            log.warn("创建dan_knowledge_base表可能已存在: {}", e.getMessage());
        }

        // 创建标签表
        try {
            jdbcTemplate.execute("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='标签表'");
            log.info("已创建dan_knowledge_tag表");
        } catch (Exception e) {
            log.warn("创建dan_knowledge_tag表可能已存在: {}", e.getMessage());
        }

        // 创建知识-维度关联表
        try {
            jdbcTemplate.execute("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='知识-维度关联表'");
            log.info("已创建dan_knowledge_base_dimension表");

            // 添加外键约束（单独执行避免CREATE TABLE时依赖表不存在）
            jdbcTemplate.execute("ALTER TABLE dan_knowledge_base_dimension ADD CONSTRAINT fk_kd_knowledge FOREIGN KEY (knowledge_id) REFERENCES dan_knowledge_base(id) ON DELETE CASCADE");
            jdbcTemplate.execute("ALTER TABLE dan_knowledge_base_dimension ADD CONSTRAINT fk_kd_dimension FOREIGN KEY (dimension_id) REFERENCES product_dimension_config(id) ON DELETE CASCADE");
            log.info("已添加dan_knowledge_base_dimension外键约束");
        } catch (Exception e) {
            log.warn("创建dan_knowledge_base_dimension表可能已存在: {}", e.getMessage());
        }

        // 创建知识-标签关联表
        try {
            jdbcTemplate.execute("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='知识-标签关联表'");
            log.info("已创建dan_knowledge_base_tag表");

            // 添加外键约束
            jdbcTemplate.execute("ALTER TABLE dan_knowledge_base_tag ADD CONSTRAINT fk_kt_knowledge FOREIGN KEY (knowledge_id) REFERENCES dan_knowledge_base(id) ON DELETE CASCADE");
            jdbcTemplate.execute("ALTER TABLE dan_knowledge_base_tag ADD CONSTRAINT fk_kt_tag FOREIGN KEY (tag_id) REFERENCES dan_knowledge_tag(id) ON DELETE CASCADE");
            log.info("已添加dan_knowledge_base_tag外键约束");
        } catch (Exception e) {
            log.warn("创建dan_knowledge_base_tag表可能已存在: {}", e.getMessage());
        }
    }

    private void insertSysConfigSeed(String key, String value, String desc) {
        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM sys_config WHERE config_key = ?", Integer.class, key);
        if (count == null || count == 0) {
            jdbcTemplate.update(
                "INSERT INTO sys_config (config_key, config_value, description, updated_at) VALUES (?, ?, ?, NOW())",
                key, value, desc);
        }
    }

    private void initFoodTables() {
        log.info("开始初始化食品相关表...");

        // 4张新表 (CREATE TABLE IF NOT EXISTS)
        String[] ddl = {
            "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)"
            + ");",

            "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)"
            + ");",

            "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)"
            + ");",

            "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)"
            + ");"
        };

        for (String sql : ddl) {
            try {
                jdbcTemplate.execute(sql);
            } catch (Exception e) {
                log.warn("食品表DDL执行失败(可能已存在): {}", e.getMessage());
            }
        }

        // 扩展 user_nutrition_profile（如果表不存在则跳过）
        ensureColumn("user_nutrition_profile", "budget_monthly", "INT COMMENT '月食品预算(元)'");
        ensureColumn("user_nutrition_profile", "family_taste_preferences", "TEXT COMMENT '家庭成员口味偏好JSON'");
        ensureColumn("user_nutrition_profile", "cuisine_style", "VARCHAR(100) COMMENT '偏好菜系'");
        ensureColumn("user_nutrition_profile", "meal_count", "INT DEFAULT 3 COMMENT '每日餐数'");
        ensureColumn("user_nutrition_profile", "cooking_ability", "VARCHAR(20) DEFAULT 'simple' COMMENT '烹饪能力: simple/medium/advanced'");

        log.info("食品相关表初始化完成");
    }

    private void ensureColumn(String table, String column, String definition) {
        try {
            jdbcTemplate.execute("ALTER TABLE " + table + " ADD COLUMN " + column + " " + definition);
            log.info("已添加列 {}.{}", table, column);
        } catch (Exception e) {
            if (e.getMessage() != null && e.getMessage().contains("Duplicate column")) {
                log.info("列 {}.{} 已存在，跳过", table, column);
            } else {
                log.warn("添加列 {}.{} 失败: {}", table, column, e.getMessage());
            }
        }
    }

    private void insertProductSeed(String name, String desc, String type,
                                Integer price,
                                String domain, String memberEligible) {
        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM products WHERE name = ? AND product_type = ?", Integer.class, name, type);
        if (count == null || count == 0) {
            jdbcTemplate.update(
                "INSERT INTO products (name, description, price, product_type, vendor_id, vendor_name, status, domain, member_eligible, created_at, updated_at) " +
                "VALUES (?, ?, ?, ?, NULL, '平台官方', 'on_shelf', ?, ?, NOW(), NOW())",
                name, desc, price, type, domain, memberEligible);
        }
    }

    private void insertContentSectionSeed(String pageKey, String sectionKey, String title,
                                        String allowedRoles, int sortOrder) {
        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM content_sections WHERE page_key = ? AND section_key = ?",
            Integer.class, pageKey, sectionKey);
        if (count == null || count == 0) {
            jdbcTemplate.update(
                "INSERT INTO content_sections (page_key, section_key, title, allowed_roles, sort_order, status, created_at, updated_at) " +
                "VALUES (?, ?, ?, ?, ?, 'active', NOW(), NOW())",
                pageKey, sectionKey, title, allowedRoles, sortOrder);
        }
    }
}