package com.lesingle.edu.util;

import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import lombok.extern.slf4j.Slf4j;

/**
 * 数据库迁移启动器
 */
@Slf4j
@Component
public class DatabaseMigrationRunner implements CommandLineRunner {

    @Override
    public void run(String... args) throws Exception {
        // 检查是否需要执行数据库迁移
        if (args.length > 0 && "migrate".equals(args[0])) {
            log.info("开始执行数据库迁移...");

            String sqlScript = getMigrationScript();
            String[] statements = DatabaseMigrationUtil.parseSqlScript(sqlScript);
            DatabaseMigrationUtil.executeMigration(statements);

            log.info("数据库迁移完成！程序退出。");
            System.exit(0);
        }
    }

    private String getMigrationScript() {
        return """
        -- ============================================
        -- Java 后端新实体数据库迁移脚本
        -- 创建时间: 2026-03-12
        -- ============================================

        -- 1. 课程套餐表
        CREATE TABLE IF NOT EXISTS course_package (
            id BIGINT NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL COMMENT '套餐名称',
            description TEXT COMMENT '套餐描述',
            price BIGINT NOT NULL COMMENT '价格（分）',
            discount_price BIGINT COMMENT '折后价格（分）',
            discount_type VARCHAR(50) COMMENT '折扣类型',
            grade_levels VARCHAR(500) COMMENT '适用年级',
            course_count INT NOT NULL DEFAULT 0 COMMENT '课程数量',
            status VARCHAR(50) NOT NULL DEFAULT 'DRAFT' COMMENT '状态',
            submitted_at DATETIME COMMENT '提交时间',
            submitted_by BIGINT COMMENT '提交人ID',
            reviewed_at DATETIME COMMENT '审核时间',
            reviewed_by BIGINT COMMENT '审核人ID',
            review_comment TEXT COMMENT '审核意见',
            published_at DATETIME COMMENT '发布时间',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程套餐表';

        -- 2. 套餐课程关联表
        CREATE TABLE IF NOT EXISTS course_package_course (
            id BIGINT NOT NULL AUTO_INCREMENT,
            package_id BIGINT NOT NULL COMMENT '套餐ID',
            course_id BIGINT NOT NULL COMMENT '课程ID',
            grade_level VARCHAR(50) COMMENT '适用年级',
            sort_order INT NOT NULL DEFAULT 0 COMMENT '排序号',
            PRIMARY KEY (id),
            UNIQUE KEY uk_package_course (package_id, course_id),
            KEY idx_package_id (package_id),
            KEY idx_course_id (course_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='套餐课程关联表';

        -- 3. 租户套餐关联表
        CREATE TABLE IF NOT EXISTS tenant_package (
            id BIGINT NOT NULL AUTO_INCREMENT,
            tenant_id BIGINT NOT NULL COMMENT '租户ID',
            package_id BIGINT NOT NULL COMMENT '套餐ID',
            start_date DATE NOT NULL COMMENT '开始日期',
            end_date DATE NOT NULL COMMENT '结束日期',
            price_paid BIGINT NOT NULL DEFAULT 0 COMMENT '实付价格',
            status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_tenant_id (tenant_id),
            KEY idx_package_id (package_id),
            KEY idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户套餐关联表';

        -- 4. 课程环节表
        CREATE TABLE IF NOT EXISTS course_lesson (
            id BIGINT NOT NULL AUTO_INCREMENT,
            course_id BIGINT NOT NULL COMMENT '课程ID',
            lesson_type VARCHAR(50) NOT NULL COMMENT '课程类型',
            name VARCHAR(255) NOT NULL COMMENT '课程名称',
            description TEXT COMMENT '课程描述',
            duration INT COMMENT '时长（分钟）',
            video_path VARCHAR(500) COMMENT '视频路径',
            video_name VARCHAR(255) COMMENT '视频名称',
            ppt_path VARCHAR(500) COMMENT 'PPT路径',
            ppt_name VARCHAR(255) COMMENT 'PPT名称',
            pdf_path VARCHAR(500) COMMENT 'PDF路径',
            pdf_name VARCHAR(255) COMMENT 'PDF名称',
            objectives TEXT COMMENT '教学目标',
            preparation TEXT COMMENT '教学准备',
            extension TEXT COMMENT '教学延伸',
            reflection TEXT COMMENT '教学反思',
            assessment_data TEXT COMMENT '评测数据',
            use_template TINYINT(1) DEFAULT 0 COMMENT '是否使用模板',
            sort_order INT NOT NULL DEFAULT 0 COMMENT '排序号',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_course_id (course_id),
            KEY idx_lesson_type (lesson_type),
            UNIQUE KEY uk_course_lesson_type (course_id, lesson_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程环节表';

        -- 5. 教学环节表
        CREATE TABLE IF NOT EXISTS lesson_step (
            id BIGINT NOT NULL AUTO_INCREMENT,
            lesson_id BIGINT NOT NULL COMMENT '课程环节ID',
            name VARCHAR(255) NOT NULL COMMENT '环节名称',
            content TEXT COMMENT '环节内容',
            duration INT NOT NULL DEFAULT 5 COMMENT '时长（分钟）',
            objective TEXT COMMENT '教学目标',
            resource_ids TEXT COMMENT '资源ID列表',
            sort_order INT NOT NULL DEFAULT 0 COMMENT '排序号',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_lesson_id (lesson_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教学环节表';

        -- 6. 环节资源关联表
        CREATE TABLE IF NOT EXISTS lesson_step_resource (
            id BIGINT NOT NULL AUTO_INCREMENT,
            step_id BIGINT NOT NULL COMMENT '环节ID',
            resource_id BIGINT NOT NULL COMMENT '资源ID',
            sort_order INT NOT NULL DEFAULT 0 COMMENT '排序号',
            PRIMARY KEY (id),
            KEY idx_step_id (step_id),
            KEY idx_resource_id (resource_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='环节资源关联表';

        -- 7. 主题字典表
        CREATE TABLE IF NOT EXISTS theme (
            id BIGINT NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL COMMENT '主题名称',
            description TEXT COMMENT '主题描述',
            sort_order INT NOT NULL DEFAULT 0 COMMENT '排序号',
            status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_status (status),
            KEY idx_sort_order (sort_order)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主题字典表';

        -- 验证表创建
        SELECT '数据库表创建完成！' AS message;
        """;
    }
}
