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') 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 NOT NULL, " +
            "title VARCHAR(200) NOT NULL, " +
            "description VARCHAR(500), " +
            "points TINYINT DEFAULT 2, " +
            "deadline DATETIME NOT NULL, " +
            "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, " +
            "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)" +
            ") 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 NOT 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"
);

        // 执行创建表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) {
            // 修改失败，忽略错误
        }

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

        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) {
                // 插入预设奖励模板
                jdbcTemplate.execute("INSERT INTO rewards (family_id, child_id, title, points_required, category, is_template) VALUES " +
                    "(0, 0, '看动画片30分钟', 30, '精神类', 1), " +
                    "(0, 0, '玩平板游戏30分钟', 40, '特权类', 1), " +
                    "(0, 0, '去游乐场', 100, '物质类', 1), " +
                    "(0, 0, '买玩具', 150, '物质类', 1), " +
                    "(0, 0, '吃冰淇淋', 20, '物质类', 1), " +
                    "(0, 0, '选择晚餐', 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 {
  // 先检查并添加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());
}

        // 初始化任务模板示例数据
        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());
}
}
}