package com.zxyj.config;

import com.zxyj.mapper.*;
import lombok.extern.slf4j.Slf4j;
import javax.annotation.Resource;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.List;

@Slf4j
@Component
public class DatabaseInitializer implements CommandLineRunner {

    @Resource
    private JdbcTemplate jdbcTemplate;

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

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

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

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

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

        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() {
        // 表创建SQL - 使用IF NOT EXISTS确保幂等性
        List<String> createTableSQLs = Arrays.asList(
            // 家庭表
            "CREATE TABLE IF NOT EXISTS families (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "name VARCHAR(100), " +
            "invite_code VARCHAR(8) UNIQUE, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
            "INDEX idx_invite_code (invite_code)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 用户表
            "CREATE TABLE IF NOT EXISTS users (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "openid VARCHAR(64) NOT NULL, " +
            "unionid VARCHAR(64), " +
            "family_id BIGINT NOT NULL, " +
            "role ENUM('parent','child','admin') NOT NULL, " +
            "nickname VARCHAR(50), " +
            "avatar VARCHAR(255), " +
            "password VARCHAR(128), " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
            "INDEX idx_openid (openid), " +
            "INDEX idx_family_id (family_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
// 孩子详情表
"CREATE TABLE IF NOT EXISTS children (" +
"id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
"user_id BIGINT NOT NULL, " +
"family_id BIGINT NOT NULL, " +
"nickname VARCHAR(50) NOT NULL, " +
"age TINYINT NOT NULL, " +
"dan_level VARCHAR(2), " +
"penalty_enabled TINYINT DEFAULT 1, " +
"theme VARCHAR(32) DEFAULT \'default\', " +
"total_points INT DEFAULT 0, " +
"streak_days INT DEFAULT 0, " +
"last_task_date DATE, " +
"focus_max_daily TINYINT DEFAULT 3, " +
"focus_remaining TINYINT DEFAULT 3, " +
"focus_reset_date DATE, " +
"created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
"updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
"INDEX idx_user_id (user_id), " +
"INDEX idx_family_id (family_id)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 任务表
            "CREATE TABLE IF NOT EXISTS tasks (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "family_id BIGINT NOT NULL, " +
            "creator_id BIGINT NOT NULL, " +
            "child_id BIGINT COMMENT '执行者ID(child任务时必填，parent任务时可空)', " +
            "executor_type VARCHAR(20) COMMENT '执行者类型: child/parent', " +
            "executor_id BIGINT COMMENT '执行者ID', " +
            "title VARCHAR(200) NOT NULL, " +
            "description VARCHAR(500), " +
            "points TINYINT DEFAULT 2, " +
            "deadline DATETIME, " +
            "repeat_type ENUM('none','daily','weekly') DEFAULT 'none', " +
            "category VARCHAR(32), " +
            "need_review TINYINT DEFAULT 0, " +
            "review_by_category TINYINT DEFAULT 0, " +
            "status ENUM('pending','completed','overdue','cancelled') DEFAULT 'pending', " +
            "completed_at DATETIME, " +
            "bonus_rule VARCHAR(500), " +
            "points_expire_days INT DEFAULT 0, " +
            "template_id BIGINT, " +
            "is_template TINYINT DEFAULT 0, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
            "INDEX idx_child_id_deadline (child_id, deadline), " +
            "INDEX idx_status (status), " +
            "INDEX idx_category (category), " +
            "INDEX idx_executor (executor_type, executor_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 积分流水表
            "CREATE TABLE IF NOT EXISTS points_log (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "child_id BIGINT NOT NULL, " +
            "task_id BIGINT, " +
            "amount INT NOT NULL, " +
            "type ENUM('earn','spend','bonus','penalty','adjust','reset','refund') NOT NULL, " +
            "description VARCHAR(255), " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_child_id_created (child_id, created_at), " +
            "INDEX idx_type (type)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 积分清零记录表
            "CREATE TABLE IF NOT EXISTS points_reset_log (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "child_id BIGINT NOT NULL, " +
            "balance_before INT NOT NULL, " +
            "balance_after INT DEFAULT 0, " +
            "reset_type ENUM('monthly','manual') NOT NULL, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_child_id (child_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
// 奖励表
"CREATE TABLE IF NOT EXISTS rewards (" +
"id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
"family_id BIGINT NOT NULL, " +
"child_id BIGINT NULL COMMENT '孩子ID，模板奖励为NULL', " +
"title VARCHAR(100) NOT NULL, " +
"points_required INT NOT NULL, " +
"category VARCHAR(32), " +
"status ENUM(\'available\',\'pending\',\'exchanged\',\'rejected\') DEFAULT \'available\', " +
"exchange_approved TINYINT, " +
"exchanged_at DATETIME, " +
"is_template TINYINT DEFAULT 0, " +
"created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
"updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
"INDEX idx_child_id_status (child_id, status), " +
"INDEX idx_is_template (is_template)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 奖励分类表
            "CREATE TABLE IF NOT EXISTS reward_categories (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "family_id BIGINT NOT NULL, " +
            "name VARCHAR(32) NOT NULL, " +
            "icon VARCHAR(64), " +
            "sort_order INT DEFAULT 0, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_family_id (family_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 敏感词表
            "CREATE TABLE IF NOT EXISTS sensitive_words (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "word VARCHAR(50) NOT NULL, " +
            "category VARCHAR(32) NOT NULL, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_word (word)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 任务审核表
            "CREATE TABLE IF NOT EXISTS task_reviews (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "task_id BIGINT NOT NULL, " +
            "child_id BIGINT NOT NULL, " +
            "photo_url VARCHAR(255), " +
            "voice_note_url VARCHAR(255), " +
            "ai_result ENUM('pass','review','pending'), " +
            "ai_confidence DECIMAL(3,2), " +
            "ai_suggestion VARCHAR(255), " +
            "ai_threshold ENUM('strict','normal','loose') DEFAULT 'normal', " +
            "parent_result ENUM('pass','reject'), " +
            "parent_override TINYINT DEFAULT 0, " +
            "review_type ENUM('ai','manual') NOT NULL, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "reviewed_at DATETIME, " +
            "INDEX idx_task_id (task_id), " +
            "INDEX idx_ai_result (ai_result)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 勋章定义表
            "CREATE TABLE IF NOT EXISTS badge_definitions (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "badge_type VARCHAR(32) NOT NULL, " +
            "name VARCHAR(50) NOT NULL, " +
            "description VARCHAR(200), " +
            "icon VARCHAR(64), " +
            "condition_type VARCHAR(32) NOT NULL, " +
            "condition_value INT NOT NULL, " +
            "reward_points INT DEFAULT 0, " +
            "INDEX idx_badge_type (badge_type)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 勋章表
            "CREATE TABLE IF NOT EXISTS badges (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "child_id BIGINT NOT NULL, " +
            "badge_type VARCHAR(32) NOT NULL, " +
            "unlocked_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_child_id_type (child_id, badge_type)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 打卡里程碑表
            "CREATE TABLE IF NOT EXISTS streak_milestones (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "days INT NOT NULL, " +
            "reward_points INT DEFAULT 0, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 专注训练记录表
            "CREATE TABLE IF NOT EXISTS focus_sessions (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "child_id BIGINT NOT NULL, " +
            "mode ENUM('simple','pomodoro') NOT NULL, " +
            "duration TINYINT NOT NULL, " +
            "stars TINYINT, " +
            "points_earned INT, " +
            "voice_note_url VARCHAR(255), " +
            "started_at DATETIME NOT NULL, " +
            "completed_at DATETIME, " +
            "INDEX idx_child_id_completed (child_id, completed_at)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 专注训练配置表
            "CREATE TABLE IF NOT EXISTS focus_config (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "family_id BIGINT, " +
            "max_daily_count TINYINT DEFAULT 3, " +
            "allow_exit TINYINT DEFAULT 1, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // DAN测评表
            "CREATE TABLE IF NOT EXISTS dan_assessments (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "child_id BIGINT NOT NULL, " +
            "dan_level VARCHAR(2) NOT NULL, " +
            "assessment_date DATE NOT NULL, " +
            "details JSON, " +
            "source ENUM('api','manual') DEFAULT 'manual', " +
            "photo_url VARCHAR(255), " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_child_id_level (child_id, dan_level)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 任务模板表
            "CREATE TABLE IF NOT EXISTS task_templates (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "dan_level VARCHAR(2) NOT NULL, " +
            "title VARCHAR(200) NOT NULL, " +
            "points TINYINT DEFAULT 2, " +
            "duration_minutes TINYINT, " +
            "category VARCHAR(32), " +
            "is_default TINYINT DEFAULT 1, " +
            "family_id BIGINT, " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "INDEX idx_dan_level (dan_level), " +
            "INDEX idx_is_default (is_default)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
// 管理员表
"CREATE TABLE IF NOT EXISTS admins (" +
"id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
"username VARCHAR(50) NOT NULL, " +
"password VARCHAR(128) NOT NULL, " +
"real_name VARCHAR(50), " +
"phone VARCHAR(11), " +
"email VARCHAR(100), " +
"role VARCHAR(20) DEFAULT 'admin', " +
"status TINYINT DEFAULT 1, " +
"created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
"updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
"INDEX idx_phone (phone), " +
"INDEX idx_username (username)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
// 验证码表
"CREATE TABLE IF NOT EXISTS verification_codes (" +
"id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
"phone VARCHAR(11) NOT NULL, " +
"code VARCHAR(6) NOT NULL, " +
"type VARCHAR(20) NOT NULL, " +
"expires_in INT DEFAULT 300, " +
"created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
"expires_at DATETIME NOT NULL, " +
"used TINYINT DEFAULT 0, " +
"INDEX idx_phone_type (phone, type), " +
"INDEX idx_expires_at (expires_at)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

// 管理后台任务模板表
"CREATE TABLE IF NOT EXISTS admin_task_templates (" +
"id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
"title VARCHAR(200) NOT NULL, " +
"description TEXT, " +
"points INT DEFAULT 10, " +
"category VARCHAR(32), " +
"need_review TINYINT DEFAULT 0, " +
"review_by_category TINYINT DEFAULT 0, " +
"recommended_age INT, " +
"difficulty ENUM('easy', 'medium', 'hard') DEFAULT 'medium', " +
"is_active TINYINT DEFAULT 1, " +
"created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
"updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
"INDEX idx_category (category), " +
            "INDEX idx_difficulty (difficulty), " +
            "INDEX idx_is_active (is_active)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            // 测评资料表
            "CREATE TABLE IF NOT EXISTS assessment_materials (" +
            "id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', " +
            "title VARCHAR(100) NOT NULL COMMENT '资料标题', " +
            "content TEXT COMMENT '测评介绍内容', " +
            "cover_image VARCHAR(255) COMMENT '封面图片', " +
            "status VARCHAR(20) DEFAULT 'active' COMMENT '状态:active/inactive', " +
            "created_at DATETIME COMMENT '创建时间', " +
            "updated_at DATETIME COMMENT '更新时间', " +
            "INDEX idx_status (status)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测评资料表'",

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

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

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

            // 成长规划师表（已认证的规划师）
            "CREATE TABLE IF NOT EXISTS guides (" +
            "id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID', " +
            "user_id BIGINT NOT NULL UNIQUE COMMENT '用户ID（唯一）', " +
            "level VARCHAR(20) NOT NULL DEFAULT 'junior' COMMENT '等级: junior-初级, intermediate-中级, senior-高级', " +
            "status VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active-活跃, inactive-停用', " +
            "service_count INT DEFAULT 0 COMMENT '当前服务家庭数', " +
            "package_id BIGINT COMMENT '购买套餐ID', " +
            "nickname VARCHAR(100) COMMENT '规划师昵称', " +
            "phone VARCHAR(20) COMMENT '联系电话', " +
            "intro TEXT COMMENT '个人介绍', " +
            "avatar VARCHAR(500) COMMENT '头像URL', " +
            "commission_rate DECIMAL(5,4) DEFAULT 0.1000 COMMENT '佣金比例', " +
            "approved_at DATETIME COMMENT '审核通过时间', " +
            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', " +
            "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', " +
            "INDEX idx_user_id (user_id), " +
            "INDEX idx_level (level), " +
            "INDEX idx_status (status)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成长规划师表'",

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

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

        // 执行创建表SQL
        for (String sql : createTableSQLs) {
            try {
                jdbcTemplate.execute(sql);
            } catch (Exception e) {
                log.warn("创建表失败: {}", e.getMessage());
            }
        }
        
        log.info("数据表检查完成");
    }

    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) {
            // 索引已存在，忽略错误
        }

        // 迁移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 DECIMAL(10,2) NOT NULL DEFAULT 0," +
                "  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 DECIMAL(10,2)," +
                "  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 DECIMAL(10,2) NOT NULL," +
                "  total_amount DECIMAL(10,2) NOT NULL," +
                "  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", "0.01", "积分兑换汇率（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", "高级会员折扣率");
            log.info("SysConfig 种子数据已加载");
        } catch (Exception e) {
            log.warn("SysConfig 种子数据初始化失败: {}", e.getMessage());
        }

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

        // 初始化成长规划师模块相关表
        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 DECIMAL(10,2) 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 DECIMAL(10,2) COMMENT '参考价格', " +
                "min_price DECIMAL(10,2) 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 DECIMAL(10,2) 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 DECIMAL(5,2) COMMENT '佣金比例' 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 DECIMAL(10,2) 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 DECIMAL(10,2) NOT NULL COMMENT '佣金金额', " +
                "rate DECIMAL(5,2) COMMENT '佣金比例', " +
                "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) {
            // 列已存在，忽略错误
        }

        log.info("成长规划师模块数据库表初始化完成");
    }

    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 insertProductSeed(String name, String desc, String type,
                                java.math.BigDecimal 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);
        }
    }
}