ai_english/serve/internal/database/migrate.go

package database

import (
    "log"
    "os"
    "path/filepath"
    "strings"

	"github.com/Nanqipro/YunQue-Tech-Projects/ai_english_learning/serve/internal/models"
	"gorm.io/gorm"
)

// AutoMigrate 自动迁移数据库表结构
func AutoMigrate(db *gorm.DB) error {
	log.Println("开始数据库迁移...")
	
	// 用户相关表
	err := db.AutoMigrate(
		&models.User{},
		&models.UserSocialLink{},
		&models.UserPreference{},
	)
	if err != nil {
		return err
	}
	
    // 词汇相关表迁移由 SQL 脚本维护，避免与既有外键/类型冲突
    // （跳过 Vocabulary* / UserVocabularyProgress / VocabularyTest 的 AutoMigrate）
	
	// 词汇书相关表（新增）
	err = db.AutoMigrate(
		&models.VocabularyBook{},
		&models.VocabularyBookWord{},
	)
	if err != nil {
		return err
	}
	
	// 学习相关表
	err = db.AutoMigrate(
		&models.Notification{},
		&models.StudyPlan{},
		&models.StudyPlanRecord{},
		&models.ListeningMaterial{},
		&models.ListeningRecord{},
		&models.ReadingMaterial{},
		&models.ReadingRecord{},
		&models.WritingPrompt{},
		&models.WritingSubmission{},
		&models.SpeakingScenario{},
		&models.SpeakingRecord{},
	)
	if err != nil {
		return err
	}
	
	log.Println("数据库迁移完成")
	return nil
}

// CreateIndexes 创建额外的索引
func CreateIndexes(db *gorm.DB) error {
	log.Println("开始创建索引...")
	
	// 创建索引的辅助函数
	createIndexIfNotExists := func(indexName, tableName, columns string) {
		// 检查索引是否存在
		var count int64
		db.Raw("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?", tableName, indexName).Scan(&count)
		if count == 0 {
			// 索引不存在，创建索引
			sql := "CREATE INDEX " + indexName + " ON " + tableName + "(" + columns + ")"
			result := db.Exec(sql)
			if result.Error != nil {
				log.Printf("创建索引 %s 失败: %v", indexName, result.Error)
			}
		}
	}
	
	// 用户表索引
    createIndexIfNotExists("idx_users_email_verified", "ai_users", "email_verified")
    createIndexIfNotExists("idx_users_status", "ai_users", "status")
    createIndexIfNotExists("idx_users_created_at", "ai_users", "created_at")
	
	// 词汇表索引
    createIndexIfNotExists("idx_vocabulary_level", "ai_vocabulary", "level")
    createIndexIfNotExists("idx_vocabulary_frequency", "ai_vocabulary", "frequency")
    createIndexIfNotExists("idx_vocabulary_is_active", "ai_vocabulary", "is_active")
	
	// 用户词汇进度索引
    createIndexIfNotExists("idx_user_vocabulary_progress_user_vocab", "ai_user_vocabulary_progress", "user_id, vocabulary_id")
    createIndexIfNotExists("idx_user_vocabulary_progress_mastery", "ai_user_vocabulary_progress", "mastery_level")
    createIndexIfNotExists("idx_user_vocabulary_progress_next_review", "ai_user_vocabulary_progress", "next_review_at")
	
	// 学习记录索引
    createIndexIfNotExists("idx_listening_records_user_material", "ai_listening_records", "user_id, material_id")
    createIndexIfNotExists("idx_reading_records_user_material", "ai_reading_records", "user_id, material_id")
    createIndexIfNotExists("idx_writing_submissions_user_prompt", "ai_writing_submissions", "user_id, prompt_id")
    createIndexIfNotExists("idx_speaking_records_user_scenario", "ai_speaking_records", "user_id, scenario_id")
	
	// 材料表索引
    createIndexIfNotExists("idx_listening_materials_level", "ai_listening_materials", "level")
    createIndexIfNotExists("idx_reading_materials_level", "ai_reading_materials", "level")
    createIndexIfNotExists("idx_writing_prompts_level", "ai_writing_prompts", "level")
    createIndexIfNotExists("idx_speaking_scenarios_level", "ai_speaking_scenarios", "level")
	
	log.Println("索引创建完成")
	return nil
}

// ApplyMergedSchemaIfNeeded 读取并执行合并后的SQL脚本，用于创建视图、触发器及扩展表
func ApplyMergedSchemaIfNeeded(db *gorm.DB) error {
    // 检查一个扩展表是否存在，作为是否需要执行脚本的依据
    var count int64
    db.Raw("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'ai_vocabulary_books'").Scan(&count)
    if count > 0 {
        // 已存在扩展结构，跳过
        return nil
    }

    // 读取脚本文件（从 serve 目录运行，脚本位于 ../docs/）
    candidates := []string{
        "../docs/database_schema_merged.sql",
        "../docs/database_schema.sql",
    }
    var content []byte
    var readErr error
    for _, p := range candidates {
        abs, _ := filepath.Abs(p)
        content, readErr = os.ReadFile(abs)
        if readErr == nil {
            break
        }
    }
    if readErr != nil {
        log.Printf("读取数据库脚本失败: %v", readErr)
        return nil
    }

    sql := string(content)
    // 移除 DELIMITER 指令并将触发器结束符 // 转为 ;
    lines := strings.Split(sql, "\n")
    var cleaned []string
    for _, line := range lines {
        trimmed := strings.TrimSpace(line)
        if strings.HasPrefix(trimmed, "DELIMITER") {
            continue
        }
        // 将以 // 结尾的行替换为 ;
        if strings.HasSuffix(trimmed, "//") {
            cleaned = append(cleaned, strings.TrimSuffix(line, "//")+";")
            continue
        }
        cleaned = append(cleaned, line)
    }
    cleanedSQL := strings.Join(cleaned, "\n")

    // 关闭外键检查以避免初始化时的顺序问题
    if err := db.Exec("SET FOREIGN_KEY_CHECKS=0;").Error; err != nil {
        log.Printf("关闭外键检查失败: %v", err)
    }
    // 执行脚本（依赖 multiStatements=true）
    if err := db.Exec(cleanedSQL).Error; err != nil {
        log.Printf("执行合并SQL失败: %v", err)
    }
    // 恢复外键检查
    if err := db.Exec("SET FOREIGN_KEY_CHECKS=1;").Error; err != nil {
        log.Printf("开启外键检查失败: %v", err)
    }

    return nil
}