ai_english/serve/internal/services/vocabulary_service.go

package services

import (
	"fmt"
	"log"
	"strconv"
	"time"

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

type VocabularyService struct {
	db *gorm.DB
}

func NewVocabularyService(db *gorm.DB) *VocabularyService {
	return &VocabularyService{db: db}
}

// 显式实现 VocabularyServiceInterface 接口
var _ interfaces.VocabularyServiceInterface = (*VocabularyService)(nil)

// GetCategories 获取词汇分类列表
func (s *VocabularyService) GetCategories(page, pageSize int, level string) (*common.PaginationData, error) {
	var categories []*models.VocabularyCategory
	var total int64

	query := s.db.Model(&models.VocabularyCategory{})
	if level != "" {
		query = query.Where("level = ?", level)
	}

	if err := query.Count(&total).Error; err != nil {
		return nil, err
	}

	offset := utils.CalculateOffset(page, pageSize)
	if err := query.Offset(offset).Limit(pageSize).Order("created_at DESC").Find(&categories).Error; err != nil {
		return nil, err
	}

	totalPages := utils.CalculateTotalPages(int(total), pageSize)

	return &common.PaginationData{
		Items: categories,
		Pagination: &common.Pagination{
			Page:       page,
			PageSize:   pageSize,
			Total:      int(total),
			TotalPages: totalPages,
		},
	}, nil
}

// CreateCategory 创建词汇分类
func (s *VocabularyService) CreateCategory(name, description, level string) (*models.VocabularyCategory, error) {
	// 检查分类名称是否已存在
	var existingCategory models.VocabularyCategory
	if err := s.db.Where("name = ?", name).First(&existingCategory).Error; err == nil {
		return nil, common.NewBusinessError(common.ErrCodeUserExists, "分类名称已存在")
	} else if err != gorm.ErrRecordNotFound {
		return nil, err
	}

	category := &models.VocabularyCategory{
		ID:          utils.GenerateUUID(),
		Name:        name,
		Description: &description,
		Level:       level,
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	}

	if err := s.db.Create(category).Error; err != nil {
		return nil, err
	}

	return category, nil
}

// UpdateCategory 更新词汇分类
func (s *VocabularyService) UpdateCategory(categoryID string, updates map[string]interface{}) (*models.VocabularyCategory, error) {
	var category models.VocabularyCategory
	if err := s.db.Where("id = ?", categoryID).First(&category).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, common.NewBusinessError(common.ErrCodeCategoryNotFound, "分类不存在")
		}
		return nil, err
	}

	// 如果更新名称，检查是否重复
	if name, ok := updates["name"]; ok {
		var existingCategory models.VocabularyCategory
		if err := s.db.Where("name = ? AND id != ?", name, categoryID).First(&existingCategory).Error; err == nil {
			return nil, common.NewBusinessError(common.ErrCodeUserExists, "分类名称已存在")
		} else if err != gorm.ErrRecordNotFound {
			return nil, err
		}
	}

	updates["updated_at"] = time.Now()
	if err := s.db.Model(&category).Updates(updates).Error; err != nil {
		return nil, err
	}

	return &category, nil
}

// DeleteCategory 删除词汇分类
func (s *VocabularyService) DeleteCategory(categoryID string) error {
	// 检查分类是否存在
	var category models.VocabularyCategory
	if err := s.db.Where("id = ?", categoryID).First(&category).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return common.NewBusinessError(common.ErrCodeCategoryNotFound, "分类不存在")
		}
		return err
	}

	// 检查是否有词汇使用该分类
	var count int64
	if err := s.db.Model(&models.Vocabulary{}).Where("category_id = ?", categoryID).Count(&count).Error; err != nil {
		return err
	}
	if count > 0 {
		return common.NewBusinessError(common.ErrCodeBadRequest, "该分类下还有词汇，无法删除")
	}

	if err := s.db.Delete(&category).Error; err != nil {
		return err
	}

	return nil
}

// GetVocabulariesByCategory 根据分类获取词汇列表
func (s *VocabularyService) GetVocabulariesByCategory(categoryID string, page, pageSize int, level string) (*common.PaginationData, error) {
	offset := utils.CalculateOffset(page, pageSize)

	query := s.db.Where("category_id = ?", categoryID)
	if level != "" {
		query = query.Where("level = ?", level)
	}

	// 获取总数
	var total int64
	if err := query.Model(&models.Vocabulary{}).Count(&total).Error; err != nil {
		return nil, err
	}

	// 获取词汇列表
	var vocabularies []models.Vocabulary
	if err := query.Offset(offset).Limit(pageSize).Order("created_at DESC").Find(&vocabularies).Error; err != nil {
		return nil, err
	}

	totalPages := utils.CalculateTotalPages(int(total), pageSize)

	return &common.PaginationData{
		Items: vocabularies,
		Pagination: &common.Pagination{
			Page:       page,
			PageSize:   pageSize,
			Total:      int(total),
			TotalPages: totalPages,
		},
	}, nil
}

// GetVocabularyByID 根据ID获取词汇详情
func (s *VocabularyService) GetVocabularyByID(vocabularyID string) (*models.Vocabulary, error) {
	var vocabulary models.Vocabulary
	if err := s.db.Where("id = ?", vocabularyID).First(&vocabulary).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, common.NewBusinessError(common.ErrCodeVocabularyNotFound, "词汇不存在")
		}
		return nil, err
	}
	return &vocabulary, nil
}

// CreateVocabulary 创建词汇
func (s *VocabularyService) CreateVocabulary(word, phonetic, level string, frequency int, categoryID string, definitions, examples, images []string) (*models.Vocabulary, error) {
	// 检查词汇是否已存在
	var existingVocabulary models.Vocabulary
	if err := s.db.Where("word = ?", word).First(&existingVocabulary).Error; err == nil {
		return nil, common.NewBusinessError(common.ErrCodeWordExists, "词汇已存在")
	} else if err != gorm.ErrRecordNotFound {
		return nil, err
	}

	// 检查分类是否存在
	var category models.VocabularyCategory
	if err := s.db.Where("id = ?", categoryID).First(&category).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, common.NewBusinessError(common.ErrCodeCategoryNotFound, "分类不存在")
		}
		return nil, err
	}

	// 创建词汇
	vocabulary := &models.Vocabulary{
		Word:      word,
		Level:     level,
		Frequency: frequency,
		IsActive:  true,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	// 设置音标（可选）
	if phonetic != "" {
		vocabulary.Phonetic = &phonetic
	}

	// 开始事务
	tx := s.db.Begin()
	defer func() {
		if r := recover(); r != nil {
			tx.Rollback()
		}
	}()

	// 创建词汇记录
	if err := tx.Create(vocabulary).Error; err != nil {
		tx.Rollback()
		return nil, err
	}

	// 关联分类
	if err := tx.Model(vocabulary).Association("Categories").Append(&category); err != nil {
		tx.Rollback()
		return nil, err
	}

	// 创建定义
	for i, def := range definitions {
		definition := &models.VocabularyDefinition{
			VocabularyID: vocabulary.ID,
			PartOfSpeech: "noun", // 默认词性
			Definition:   def,
			Translation:  def, // 暂时用定义作为翻译
			SortOrder:    i,
			CreatedAt:    time.Now(),
		}
		if err := tx.Create(definition).Error; err != nil {
			tx.Rollback()
			return nil, err
		}
	}

	// 创建例句
	for i, ex := range examples {
		example := &models.VocabularyExample{
			VocabularyID: vocabulary.ID,
			Example:      ex,
			Translation:  ex, // 暂时用例句作为翻译
			SortOrder:    i,
			CreatedAt:    time.Now(),
		}
		if err := tx.Create(example).Error; err != nil {
			tx.Rollback()
			return nil, err
		}
	}

	// 创建图片（暂时跳过，因为VocabularyImage结构需要更新）
	_ = images // 避免未使用警告
	/*
		for i, img := range images {
			image := &models.VocabularyImage{
				VocabularyID: vocabulary.ID,
				ImageURL:     img,
				SortOrder:    i,
				CreatedAt:    time.Now(),
				UpdatedAt:    time.Now(),
			}
			if err := tx.Create(image).Error; err != nil {
				tx.Rollback()
				return nil, err
			}
		}
	*/

	// 提交事务
	if err := tx.Commit().Error; err != nil {
		return nil, err
	}

	return vocabulary, nil
}

// UpdateVocabulary 更新词汇
func (s *VocabularyService) UpdateVocabulary(id string, vocabulary *models.Vocabulary) error {
	return s.db.Model(&models.Vocabulary{}).Where("id = ?", id).Updates(vocabulary).Error
}

// DeleteVocabulary 删除词汇
func (s *VocabularyService) DeleteVocabulary(id string) error {
	return s.db.Delete(&models.Vocabulary{}, id).Error
}

// GetUserVocabularyProgress 获取用户词汇学习进度
func (s *VocabularyService) GetUserVocabularyProgress(userID int64, vocabularyID string) (*models.UserVocabularyProgress, error) {
	var progress models.UserVocabularyProgress
	if err := s.db.Where("user_id = ? AND vocabulary_id = ?", userID, vocabularyID).First(&progress).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, common.NewBusinessError(common.ErrCodeProgressNotFound, "学习进度不存在")
		}
		return nil, err
	}
	return &progress, nil
}

// UpdateUserVocabularyProgress 更新用户词汇学习进度
func (s *VocabularyService) UpdateUserVocabularyProgress(userID int64, vocabularyID string, updates map[string]interface{}) (*models.UserVocabularyProgress, error) {
	// 查找或创建进度记录
	var progress models.UserVocabularyProgress
	err := s.db.Where("user_id = ? AND vocabulary_id = ?", userID, vocabularyID).First(&progress).Error

	if err == gorm.ErrRecordNotFound {
		// 创建新的进度记录
		now := time.Now()
		progress = models.UserVocabularyProgress{
			ID:            0, // 让数据库自动生成
			UserID:        userID,
			VocabularyID:  vocabularyID,
			StudyCount:    1,
			LastStudiedAt: &now,
			CreatedAt:     now,
			UpdatedAt:     now,
		}

		// 应用更新
		if masteryLevel, ok := updates["mastery_level"].(int); ok {
			progress.MasteryLevel = masteryLevel
		}

		if err := s.db.Create(&progress).Error; err != nil {
			return nil, err
		}
		return &progress, nil
	} else if err != nil {
		return nil, err
	}

	// 更新现有进度记录
	now := time.Now()
	updateData := map[string]interface{}{
		"study_count":     progress.StudyCount + 1,
		"last_studied_at": &now,
		"updated_at":      now,
	}

	// 合并传入的更新数据
	for key, value := range updates {
		updateData[key] = value
	}

	if err := s.db.Model(&progress).Updates(updateData).Error; err != nil {
		return nil, err
	}

	// 重新查询更新后的记录
	if err := s.db.Where("user_id = ? AND vocabulary_id = ?", userID, vocabularyID).First(&progress).Error; err != nil {
		return nil, err
	}

	return &progress, nil
}

// GetUserVocabularyStats 获取用户词汇学习统计
func (s *VocabularyService) GetUserVocabularyStats(userID int64) (map[string]interface{}, error) {
	stats := make(map[string]interface{})

	// 总学习词汇数
	var totalStudied int64
	if err := s.db.Table("ai_user_word_progress").Where("user_id = ?", userID).Count(&totalStudied).Error; err != nil {
		return nil, err
	}
	stats["total_studied"] = totalStudied

	// 掌握程度统计（基于proficiency字段：0-100）
	var masteryStats []struct {
		Level string `json:"level"`
		Count int64  `json:"count"`
	}
	if err := s.db.Table("ai_user_word_progress").
		Select("CASE WHEN proficiency >= 80 THEN 'mastered' WHEN proficiency >= 60 THEN 'familiar' WHEN proficiency >= 40 THEN 'learning' ELSE 'new' END as level, COUNT(*) as count").
		Where("user_id = ?", userID).
		Group("level").
		Scan(&masteryStats).Error; err != nil {
		return nil, err
	}
	stats["mastery_stats"] = masteryStats

	// 学习准确率
	var accuracyResult struct {
		TotalCorrect int64 `json:"total_correct"`
		TotalWrong   int64 `json:"total_wrong"`
	}
	if err := s.db.Table("ai_user_word_progress").
		Select("COALESCE(SUM(correct_count), 0) as total_correct, COALESCE(SUM(wrong_count), 0) as total_wrong").
		Where("user_id = ?", userID).
		Scan(&accuracyResult).Error; err != nil {
		return nil, err
	}

	totalAttempts := accuracyResult.TotalCorrect + accuracyResult.TotalWrong
	if totalAttempts > 0 {
		stats["accuracy_rate"] = float64(accuracyResult.TotalCorrect) / float64(totalAttempts) * 100
	} else {
		stats["accuracy_rate"] = 0.0
	}

	// 最近学习的词汇
	var recentVocabularies []models.Vocabulary
	if err := s.db.Table("ai_vocabulary v").
		Joins("JOIN ai_user_word_progress uwp ON v.id = uwp.vocabulary_id").
		Where("uwp.user_id = ?", userID).
		Order("uwp.last_studied_at DESC").
		Limit(5).
		Find(&recentVocabularies).Error; err != nil {
		return nil, err
	}
	stats["recent_vocabularies"] = recentVocabularies

	return stats, nil
}

// GetTodayStudyWords 获取今日学习单词（包含完整的释义和例句）
func (s *VocabularyService) GetTodayStudyWords(userID int64, limit int) ([]map[string]interface{}, error) {
	var words []map[string]interface{}

	// 查询最近学习的单词（按最后学习时间排序，获取最近学习的词）
	query := `
		SELECT 
			v.id,
			v.word,
			COALESCE(v.phonetic, '') as phonetic,
			COALESCE(v.phonetic_us, '') as phonetic_us,
			COALESCE(v.phonetic_uk, '') as phonetic_uk,
			COALESCE(v.audio_url, '') as audio_url,
			COALESCE(v.audio_us_url, '') as audio_us_url,
			COALESCE(v.audio_uk_url, '') as audio_uk_url,
			COALESCE(v.level, '') as level,
			v.difficulty_level,
			v.frequency,
			uwp.proficiency as mastery_level,
			uwp.study_count as review_count,
			uwp.next_review_at
		FROM ai_vocabulary v
		INNER JOIN ai_user_word_progress uwp ON v.id = uwp.vocabulary_id
		WHERE uwp.user_id = ? 
			AND v.is_active = 1
		ORDER BY uwp.last_studied_at DESC
		LIMIT ?
	`

	rows, err := s.db.Raw(query, userID, limit).Rows()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var vocabularyIDs []int64
	tempWords := make(map[int64]map[string]interface{})

	for rows.Next() {
		var (
			id, reviewCount, difficultyLevel, frequency                                     int64
			word, phonetic, phoneticUs, phoneticUk, audioUrl, audioUsUrl, audioUkUrl, level string
			masteryLevel                                                                    int
			nextReviewAt                                                                    *time.Time
		)

		if err := rows.Scan(&id, &word, &phonetic, &phoneticUs, &phoneticUk, &audioUrl, &audioUsUrl, &audioUkUrl, &level, &difficultyLevel, &frequency, &masteryLevel, &reviewCount, &nextReviewAt); err != nil {
			continue
		}

		vocabularyIDs = append(vocabularyIDs, id)
		tempWords[id] = map[string]interface{}{
			"id":             fmt.Sprintf("%d", id),
			"word":           word,
			"phonetic":       phonetic,
			"phonetic_us":    phoneticUs,
			"phonetic_uk":    phoneticUk,
			"audio_url":      audioUrl,
			"audio_us_url":   audioUsUrl,
			"audio_uk_url":   audioUkUrl,
			"difficulty":     s.mapDifficultyLevel(int(difficultyLevel)),
			"frequency":      int(frequency),
			"mastery_level":  masteryLevel,
			"review_count":   reviewCount,
			"next_review_at": nextReviewAt,
			"definitions":    []map[string]interface{}{},
			"examples":       []map[string]interface{}{},
			"created_at":     time.Now(),
			"updated_at":     time.Now(),
		}
	}

	// 批量获取释义
	if len(vocabularyIDs) > 0 {
		s.loadDefinitionsForWords(vocabularyIDs, tempWords)
		s.loadExamplesForWords(vocabularyIDs, tempWords)
	}

	// 按原始顺序返回
	for _, id := range vocabularyIDs {
		words = append(words, tempWords[id])
	}

	// 如果没有需要复习的，返回一些新单词
	if len(words) == 0 {
		newWordsQuery := `
			SELECT 
				v.id,
				v.word,
				COALESCE(v.phonetic, '') as phonetic,
				COALESCE(v.phonetic_us, '') as phonetic_us,
				COALESCE(v.phonetic_uk, '') as phonetic_uk,
				COALESCE(v.audio_url, '') as audio_url,
				COALESCE(v.audio_us_url, '') as audio_us_url,
				COALESCE(v.audio_uk_url, '') as audio_uk_url,
				COALESCE(v.level, '') as level,
				v.difficulty_level,
				v.frequency
			FROM ai_vocabulary v
			WHERE v.is_active = 1
				AND NOT EXISTS (
					SELECT 1 FROM ai_user_word_progress uvp 
					WHERE uvp.vocabulary_id = v.id AND uvp.user_id = ?
				)
			ORDER BY v.frequency DESC, RAND()
			LIMIT ?
		`

		rows, err := s.db.Raw(newWordsQuery, userID, limit).Rows()
		if err != nil {
			return nil, err
		}
		defer rows.Close()

		var newVocabularyIDs []int64
		newTempWords := make(map[int64]map[string]interface{})

		for rows.Next() {
			var (
				id, difficultyLevel, frequency                                                  int64
				word, phonetic, phoneticUs, phoneticUk, audioUrl, audioUsUrl, audioUkUrl, level string
			)

			if err := rows.Scan(&id, &word, &phonetic, &phoneticUs, &phoneticUk, &audioUrl, &audioUsUrl, &audioUkUrl, &level, &difficultyLevel, &frequency); err != nil {
				fmt.Printf("err:%+v", err)
				continue
			}

			newVocabularyIDs = append(newVocabularyIDs, id)
			newTempWords[id] = map[string]interface{}{
				"id":            fmt.Sprintf("%d", id),
				"word":          word,
				"phonetic":      phonetic,
				"phonetic_us":   phoneticUs,
				"phonetic_uk":   phoneticUk,
				"audio_url":     audioUrl,
				"audio_us_url":  audioUsUrl,
				"audio_uk_url":  audioUkUrl,
				"difficulty":    s.mapDifficultyLevel(int(difficultyLevel)),
				"frequency":     int(frequency),
				"mastery_level": 0,
				"review_count":  0,
				"definitions":   []map[string]interface{}{},
				"examples":      []map[string]interface{}{},
				"created_at":    time.Now(),
				"updated_at":    time.Now(),
			}
		}

		// 批量获取释义和例句
		if len(newVocabularyIDs) > 0 {
			s.loadDefinitionsForWords(newVocabularyIDs, newTempWords)
			s.loadExamplesForWords(newVocabularyIDs, newTempWords)
		}

		// 按原始顺序返回
		for _, id := range newVocabularyIDs {
			words = append(words, newTempWords[id])
		}
	}

	return words, nil
}

// GetStudyStatistics 获取学习统计
func (s *VocabularyService) GetStudyStatistics(userID int64, date string) (map[string]interface{}, error) {
	stats := make(map[string]interface{})

	// 如果指定日期没有数据，使用最近一次学习的日期
	var actualDate string
	dateCheckQuery := `
		SELECT DATE_FORMAT(DATE(last_studied_at), '%Y-%m-%d') as study_date
		FROM ai_user_word_progress
		WHERE user_id = ? AND DATE(last_studied_at) <= ?
		ORDER BY last_studied_at DESC
		LIMIT 1
	`
	if err := s.db.Raw(dateCheckQuery, userID, date).Scan(&actualDate).Error; err != nil {
		// 如果没有任何学习记录，使用传入的日期
		actualDate = date
	}

	// 今日学习单词数（去重）
	var wordsStudied int64
	todayQuery := `
		SELECT COUNT(DISTINCT vocabulary_id) 
		FROM ai_user_word_progress 
		WHERE user_id = ? AND DATE(last_studied_at) = ?
	`
	if err := s.db.Raw(todayQuery, userID, actualDate).Scan(&wordsStudied).Error; err != nil {
		return nil, err
	}

	// 今日新学单词数（第一次学习）
	var newWordsLearned int64
	newWordsQuery := `
		SELECT COUNT(*) 
		FROM ai_user_word_progress 
		WHERE user_id = ? 
			AND DATE(first_studied_at) = ?
	`
	if err := s.db.Raw(newWordsQuery, userID, actualDate).Scan(&newWordsLearned).Error; err != nil {
		return nil, err
	}

	// 今日复习单词数
	var wordsReviewed int64
	reviewQuery := `
		SELECT COUNT(*) 
		FROM ai_user_word_progress 
		WHERE user_id = ? 
			AND DATE(last_studied_at) = ?
			AND study_count > 1
	`
	if err := s.db.Raw(reviewQuery, userID, actualDate).Scan(&wordsReviewed).Error; err != nil {
		return nil, err
	}

	// 今日掌握单词数
	var wordsMastered int64
	masteredQuery := `
		SELECT COUNT(*) 
		FROM ai_user_word_progress 
		WHERE user_id = ? 
			AND DATE(mastered_at) = ?
			AND status = 'mastered'
	`
	if err := s.db.Raw(masteredQuery, userID, actualDate).Scan(&wordsMastered).Error; err != nil {
		return nil, err
	}

	// 今日正确和错误次数
	var correctAnswers, wrongAnswers int64
	answersQuery := `
		SELECT 
			COALESCE(SUM(correct_count), 0) as correct,
			COALESCE(SUM(wrong_count), 0) as wrong
		FROM ai_user_word_progress 
		WHERE user_id = ? 
			AND DATE(last_studied_at) = ?
	`
	row := s.db.Raw(answersQuery, userID, actualDate).Row()
	if err := row.Scan(&correctAnswers, &wrongAnswers); err != nil {
		return nil, err
	}

	// 计算准确率
	totalAnswers := correctAnswers + wrongAnswers
	averageAccuracy := 0.0
	if totalAnswers > 0 {
		averageAccuracy = float64(correctAnswers) / float64(totalAnswers)
	}

	// 构建返回数据（匹配前端StudyStatistics模型）
	stats["id"] = fmt.Sprintf("stats_%d_%s", userID, actualDate)
	stats["user_id"] = fmt.Sprintf("%d", userID)
	stats["date"] = actualDate
	stats["session_count"] = 1
	stats["words_studied"] = wordsStudied
	stats["new_words_learned"] = newWordsLearned
	stats["words_reviewed"] = wordsReviewed
	stats["words_mastered"] = wordsMastered
	stats["total_study_time_seconds"] = int(wordsStudied) * 30 // 估算学习时间
	stats["correct_answers"] = correctAnswers
	stats["wrong_answers"] = wrongAnswers
	stats["average_accuracy"] = averageAccuracy
	stats["experience_gained"] = int(wordsStudied) * 5
	stats["points_gained"] = int(wordsStudied) * 2
	stats["streak_days"] = 1

	return stats, nil
}

// GetStudyStatisticsHistory 获取学习统计历史
func (s *VocabularyService) GetStudyStatisticsHistory(userID int64, startDate, endDate string) ([]map[string]interface{}, error) {
	var history []map[string]interface{}

	log.Printf("[DEBUG] GetStudyStatisticsHistory: userID=%d, startDate=%s, endDate=%s", userID, startDate, endDate)

	// 按日期分组统计学习数据
	query := `
		SELECT 
			DATE(last_studied_at) as date,
			COUNT(DISTINCT vocabulary_id) as words_studied,
			SUM(CASE WHEN DATE(first_studied_at) = DATE(last_studied_at) THEN 1 ELSE 0 END) as new_words_learned,
			SUM(CASE WHEN study_count > 1 THEN 1 ELSE 0 END) as words_reviewed,
			SUM(CASE WHEN status = 'mastered' AND DATE(mastered_at) = DATE(last_studied_at) THEN 1 ELSE 0 END) as words_mastered,
			SUM(correct_count) as correct_answers,
			SUM(wrong_count) as wrong_answers
		FROM ai_user_word_progress
		WHERE user_id = ? 
			AND DATE(last_studied_at) BETWEEN ? AND ?
		GROUP BY DATE(last_studied_at)
		ORDER BY DATE(last_studied_at) ASC
	`

	rows, err := s.db.Raw(query, userID, startDate, endDate).Rows()
	if err != nil {
		log.Printf("[ERROR] GetStudyStatisticsHistory query failed: %v", err)
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			date                                                        time.Time
			wordsStudied, newWordsLearned, wordsReviewed, wordsMastered int64
			correctAnswers, wrongAnswers                                int64
		)

		if err := rows.Scan(&date, &wordsStudied, &newWordsLearned, &wordsReviewed, &wordsMastered, &correctAnswers, &wrongAnswers); err != nil {
			log.Printf("[ERROR] GetStudyStatisticsHistory scan failed: %v", err)
			continue
		}

		// 格式化日期为 YYYY-MM-DD 字符串
		dateStr := date.Format("2006-01-02")

		// 计算准确率
		totalAnswers := correctAnswers + wrongAnswers
		averageAccuracy := 0.0
		if totalAnswers > 0 {
			averageAccuracy = float64(correctAnswers) / float64(totalAnswers)
		}

		// 构建返回数据（匹配前端StudyStatistics模型）
		history = append(history, map[string]interface{}{
			"id":                       fmt.Sprintf("stats_%d_%s", userID, dateStr),
			"user_id":                  fmt.Sprintf("%d", userID),
			"date":                     dateStr,
			"session_count":            1,
			"words_studied":            wordsStudied,
			"new_words_learned":        newWordsLearned,
			"words_reviewed":           wordsReviewed,
			"words_mastered":           wordsMastered,
			"total_study_time_seconds": int(wordsStudied) * 30,
			"correct_answers":          correctAnswers,
			"wrong_answers":            wrongAnswers,
			"average_accuracy":         averageAccuracy,
			"experience_gained":        int(wordsStudied) * 5,
			"points_gained":            int(wordsStudied) * 2,
			"streak_days":              1,
		})
	}

	return history, nil
}

// GetVocabularyTest 获取词汇测试
func (s *VocabularyService) GetVocabularyTest(testID string) (*models.VocabularyTest, error) {
	var test models.VocabularyTest
	if err := s.db.Where("id = ?", testID).First(&test).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, common.NewBusinessError(common.ErrCodeTestNotFound, "测试不存在")
		}
		return nil, err
	}
	return &test, nil
}

// CreateVocabularyTest 创建词汇测试
func (s *VocabularyService) CreateVocabularyTest(userID int64, testType, level string, totalWords int) (*models.VocabularyTest, error) {
	test := &models.VocabularyTest{
		ID:         0, // 让数据库自动生成
		UserID:     userID,
		TestType:   testType,
		Level:      level,
		TotalWords: totalWords,
		StartedAt:  time.Now(),
		CreatedAt:  time.Now(),
		UpdatedAt:  time.Now(),
	}

	if err := s.db.Create(test).Error; err != nil {
		return nil, err
	}

	return test, nil
}

// UpdateVocabularyTestResult 更新词汇测试结果
func (s *VocabularyService) UpdateVocabularyTestResult(testID string, correctWords int, score float64, duration int) error {
	now := time.Now()
	updates := map[string]interface{}{
		"correct_words": correctWords,
		"score":         score,
		"duration":      duration,
		"completed_at":  &now,
		"updated_at":    now,
	}

	result := s.db.Model(&models.VocabularyTest{}).Where("id = ?", testID).Updates(updates)
	if result.Error != nil {
		return result.Error
	}

	if result.RowsAffected == 0 {
		return common.NewBusinessError(common.ErrCodeTestNotFound, "测试不存在")
	}

	return nil
}

// SearchVocabularies 搜索词汇
func (s *VocabularyService) SearchVocabularies(keyword string, level string, page, pageSize int) (*common.PaginationData, error) {
	offset := utils.CalculateOffset(page, pageSize)

	query := s.db.Model(&models.Vocabulary{})

	// 关键词搜索
	if keyword != "" {
		query = query.Where("word LIKE ? OR phonetic LIKE ?", "%"+keyword+"%", "%"+keyword+"%")
	}

	// 级别过滤
	if level != "" {
		query = query.Where("level = ?", level)
	}

	// 只查询启用的词汇
	query = query.Where("is_active = ?", true)

	// 获取总数
	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, err
	}

	// 获取词汇列表
	var vocabularies []models.Vocabulary
	if err := query.Preload("Definitions").Preload("Examples").Preload("Images").Preload("Categories").
		Offset(offset).Limit(pageSize).Order("created_at DESC").Find(&vocabularies).Error; err != nil {
		return nil, err
	}

	totalPages := utils.CalculateTotalPages(int(total), pageSize)

	return &common.PaginationData{
		Items: vocabularies,
		Pagination: &common.Pagination{
			Page:       page,
			PageSize:   pageSize,
			Total:      int(total),
			TotalPages: totalPages,
		},
	}, nil
}

// GetDailyStats 获取每日学习统计
func (s *VocabularyService) GetDailyStats(userID string) (map[string]interface{}, error) {
	var stats struct {
		WordsLearned     int `json:"wordsLearned"`
		StudyTimeMinutes int `json:"studyTimeMinutes"`
	}

	// 查询今日学习单词数量（今天有复习记录的单词）
	if err := s.db.Raw(`
        SELECT COUNT(DISTINCT vocabulary_id) AS wordsLearned
        FROM ai_user_vocabulary_progress
        WHERE user_id = ? AND DATE(last_reviewed_at) = CURDATE()
    `, userID).Scan(&stats.WordsLearned).Error; err != nil {
		return nil, err
	}

	// 查询今日学习时间（根据复习次数估算，每次复习约2分钟）
	var reviewCount int
	if err := s.db.Raw(`
        SELECT COALESCE(SUM(review_count), 0) AS review_count
        FROM ai_user_vocabulary_progress
        WHERE user_id = ? AND DATE(last_reviewed_at) = CURDATE()
    `, userID).Scan(&reviewCount).Error; err != nil {
		return nil, err
	}
	stats.StudyTimeMinutes = reviewCount * 2 // 估算学习时间

	return map[string]interface{}{
		"wordsLearned":     stats.WordsLearned,
		"studyTimeMinutes": stats.StudyTimeMinutes,
	}, nil
}

// GetUserLearningProgress 获取用户学习进度
func (s *VocabularyService) GetUserLearningProgress(userID string, page, limit int) ([]map[string]interface{}, int64, error) {
	var progressList []map[string]interface{}
	var total int64

	// 查询用户在各个分类的学习进度
	query := `
		SELECT 
			vc.id,
			vc.name as title,
			vc.category,
			vc.level,
			COUNT(DISTINCT v.id) as total_words,
			COUNT(DISTINCT CASE WHEN uvp.mastery_level >= 3 THEN uvp.vocabulary_id END) as learned_words,
			MAX(uvp.last_review_date) as last_study_date
		FROM vocabulary_categories vc
		LEFT JOIN vocabulary v ON v.category_id = vc.id
		LEFT JOIN user_vocabulary_progress uvp ON uvp.vocabulary_id = v.id AND uvp.user_id = ?
		GROUP BY vc.id, vc.name, vc.category, vc.level
		HAVING total_words > 0
		ORDER BY last_study_date DESC NULLS LAST
	`

	// 获取总数
	countQuery := `
		SELECT COUNT(*) FROM (
			SELECT vc.id
			FROM vocabulary_categories vc
			LEFT JOIN vocabulary v ON v.category_id = vc.id
			GROUP BY vc.id
			HAVING COUNT(DISTINCT v.id) > 0
		) as subquery
	`
	if err := s.db.Raw(countQuery).Scan(&total).Error; err != nil {
		return nil, 0, err
	}

	// 分页查询
	offset := (page - 1) * limit
	rows, err := s.db.Raw(query+" LIMIT ? OFFSET ?", userID, limit, offset).Rows()
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			id            string
			title         string
			category      string
			level         string
			totalWords    int
			learnedWords  int
			lastStudyDate *time.Time
		)

		if err := rows.Scan(&id, &title, &category, &level, &totalWords, &learnedWords, &lastStudyDate); err != nil {
			continue
		}

		progress := float64(0)
		if totalWords > 0 {
			progress = float64(learnedWords) / float64(totalWords) * 100
		}

		item := map[string]interface{}{
			"id":              id,
			"title":           title,
			"category":        category,
			"level":           level,
			"total_words":     totalWords,
			"learned_words":   learnedWords,
			"progress":        progress,
			"last_study_date": lastStudyDate,
		}

		progressList = append(progressList, item)
	}

	return progressList, total, nil
}

// loadDefinitionsForWords 批量加载单词的释义
func (s *VocabularyService) loadDefinitionsForWords(vocabularyIDs []int64, words map[int64]map[string]interface{}) {
	var definitions []struct {
		VocabularyID int64  `gorm:"column:vocabulary_id"`
		PartOfSpeech string `gorm:"column:part_of_speech"`
		DefinitionEn string `gorm:"column:definition_en"`
		DefinitionCn string `gorm:"column:definition_cn"`
	}

	if err := s.db.Table("ai_vocabulary_definitions").
		Where("vocabulary_id IN ?", vocabularyIDs).
		Order("vocabulary_id, sort_order").
		Find(&definitions).Error; err != nil {
		return
	}

	for _, def := range definitions {
		if word, ok := words[def.VocabularyID]; ok {
			defs := word["definitions"].([]map[string]interface{})
			defs = append(defs, map[string]interface{}{
				"type":        def.PartOfSpeech,
				"definition":  def.DefinitionEn,
				"translation": def.DefinitionCn,
			})
			word["definitions"] = defs
		}
	}
}

// loadExamplesForWords 批量加载单词的例句
func (s *VocabularyService) loadExamplesForWords(vocabularyIDs []int64, words map[int64]map[string]interface{}) {
	var examples []struct {
		VocabularyID int64  `gorm:"column:vocabulary_id"`
		SentenceEn   string `gorm:"column:sentence_en"`
		SentenceCn   string `gorm:"column:sentence_cn"`
	}

	if err := s.db.Table("ai_vocabulary_examples").
		Where("vocabulary_id IN ?", vocabularyIDs).
		Order("vocabulary_id, sort_order").
		Limit(len(vocabularyIDs) * 3). // 每个单词最多3个例句
		Find(&examples).Error; err != nil {
		return
	}

	for _, ex := range examples {
		if word, ok := words[ex.VocabularyID]; ok {
			exs := word["examples"].([]map[string]interface{})
			exs = append(exs, map[string]interface{}{
				"sentence":    ex.SentenceEn,
				"translation": ex.SentenceCn,
			})
			word["examples"] = exs
		}
	}
}

// mapDifficultyLevel 映射难度等级
func (s *VocabularyService) mapDifficultyLevel(level int) string {
	switch level {
	case 1:
		return "beginner"
	case 2:
		return "elementary"
	case 3:
		return "intermediate"
	case 4:
		return "advanced"
	case 5:
		return "expert"
	default:
		return "intermediate"
	}
}

// GetSystemVocabularyBooks 获取系统词汇书列表
func (s *VocabularyService) GetSystemVocabularyBooks(page, limit int, category string) ([]models.VocabularyBook, int64, error) {
	var books []models.VocabularyBook
	var total int64

	query := s.db.Model(&models.VocabularyBook{}).Where("is_system = ? AND is_active = ?", true, true)

	// 如果指定了分类，添加分类过滤
	if category != "" {
		query = query.Where("category = ?", category)
	}

	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	offset := (page - 1) * limit
	if err := query.Offset(offset).Limit(limit).Order("sort_order ASC, created_at DESC").Find(&books).Error; err != nil {
		return nil, 0, err
	}

	return books, total, nil
}

// GetVocabularyBookCategories 获取词汇书分类列表
func (s *VocabularyService) GetVocabularyBookCategories() ([]map[string]interface{}, error) {
	var results []struct {
		Category string
		Count    int64
	}

	// 查询所有分类及其词汇书数量
	if err := s.db.Model(&models.VocabularyBook{}).
		Select("category, COUNT(*) as count").
		Where("is_system = ? AND is_active = ?", true, true).
		Group("category").
		Order("MIN(sort_order)").
		Find(&results).Error; err != nil {
		return nil, err
	}

	// 转换为返回格式
	categories := make([]map[string]interface{}, 0, len(results))
	for _, result := range results {
		categories = append(categories, map[string]interface{}{
			"name":  result.Category,
			"count": result.Count,
		})
	}

	return categories, nil
}

// GetVocabularyBookProgress 获取词汇书学习进度
func (s *VocabularyService) GetVocabularyBookProgress(userID int64, bookID string) (*models.UserVocabularyBookProgress, error) {
	var progress models.UserVocabularyBookProgress

	// 查询进度表
	err := s.db.Where("user_id = ? AND book_id = ?", userID, bookID).
		First(&progress).Error

	if err != nil {
		return nil, err
	}

	return &progress, nil
}

// GetVocabularyBookWords 获取词汇书单词列表
func (s *VocabularyService) GetVocabularyBookWords(bookID string, page, limit int) ([]models.VocabularyBookWord, int64, error) {
	var bookWords []models.VocabularyBookWord
	var total int64

	query := s.db.Model(&models.VocabularyBookWord{}).Where("book_id = ?", bookID)

	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	offset := (page - 1) * limit
	if err := query.Offset(offset).
		Limit(limit).
		Order("sort_order ASC, id ASC").
		Find(&bookWords).Error; err != nil {
		return nil, 0, err
	}

	// 手动加载词汇数据
	for i := range bookWords {
		var vocab models.Vocabulary

		// 将vocabulary_id从string转换为int64（因为数据库表类型不一致）
		// ai_vocabulary_book_words.vocabulary_id 是 varchar
		// ai_vocabulary.id 是 bigint
		vocabularyIDInt64, err := strconv.ParseInt(bookWords[i].VocabularyID, 10, 64)
		if err != nil {
			// 如果转换失败，跳过这个单词
			continue
		}

		if err := s.db.Where("id = ?", vocabularyIDInt64).First(&vocab).Error; err != nil {
			continue
		}

		// 加载释义
		s.db.Where("vocabulary_id = ?", vocab.ID).Find(&vocab.Definitions)

		// 加载例句
		s.db.Where("vocabulary_id = ?", vocab.ID).Find(&vocab.Examples)

		bookWords[i].Vocabulary = &vocab
	}

	return bookWords, total, nil
}

// GetUserWordProgress 获取用户单词学习进度
func (s *VocabularyService) GetUserWordProgress(userID int64, wordID int64) (map[string]interface{}, error) {
	// 查询用户单词进度记录
	var progress models.UserWordProgress
	err := s.db.Where("user_id = ? AND vocabulary_id = ?", userID, wordID).First(&progress).Error

	if err == gorm.ErrRecordNotFound {
		// 如果没有记录，返回默认进度
		now := time.Now()
		return map[string]interface{}{
			"id":             "0",
			"userId":         fmt.Sprint(userID),
			"wordId":         fmt.Sprint(wordID),
			"status":         "not_started",
			"studyCount":     0,
			"correctCount":   0,
			"wrongCount":     0,
			"proficiency":    0,
			"nextReviewAt":   nil,
			"reviewInterval": 1,
			"firstStudiedAt": now,
			"lastStudiedAt":  now,
			"masteredAt":     nil,
		}, nil
	}

	if err != nil {
		return nil, err
	}

	// 返回进度数据
	return map[string]interface{}{
		"id":             fmt.Sprint(progress.ID),
		"userId":         fmt.Sprint(progress.UserID),
		"wordId":         fmt.Sprint(progress.VocabularyID),
		"status":         progress.Status,
		"studyCount":     progress.StudyCount,
		"correctCount":   progress.CorrectCount,
		"wrongCount":     progress.WrongCount,
		"proficiency":    progress.Proficiency,
		"nextReviewAt":   progress.NextReviewAt,
		"reviewInterval": progress.ReviewInterval,
		"firstStudiedAt": progress.FirstStudiedAt,
		"lastStudiedAt":  progress.LastStudiedAt,
		"masteredAt":     progress.MasteredAt,
	}, nil
}

// UpdateUserWordProgress 更新用户单词学习进度
func (s *VocabularyService) UpdateUserWordProgress(userID int64, wordID int64, status string, isCorrect *bool) (map[string]interface{}, error) {
	var progress models.UserWordProgress
	err := s.db.Where("user_id = ? AND vocabulary_id = ?", userID, wordID).First(&progress).Error

	now := time.Now()

	if err == gorm.ErrRecordNotFound {
		// 创建新记录
		progress = models.UserWordProgress{
			UserID:         userID,
			VocabularyID:   wordID,
			Status:         status,
			StudyCount:     1,
			CorrectCount:   0,
			WrongCount:     0,
			Proficiency:    0,
			ReviewInterval: 1,
			FirstStudiedAt: now,
			LastStudiedAt:  now,
		}

		if isCorrect != nil && *isCorrect {
			progress.CorrectCount = 1
			progress.Proficiency = 20
		} else if isCorrect != nil {
			progress.WrongCount = 1
		}

		if err := s.db.Create(&progress).Error; err != nil {
			return nil, err
		}
	} else if err != nil {
		return nil, err
	} else {
		// 更新现有记录
		progress.Status = status
		progress.StudyCount++
		progress.LastStudiedAt = now

		if isCorrect != nil && *isCorrect {
			progress.CorrectCount++
			// 根据正确率更新熟练度
			accuracy := float64(progress.CorrectCount) / float64(progress.StudyCount)
			progress.Proficiency = int(accuracy * 100)

			// 如果熟练度达到80%，标记为已掌握
			if progress.Proficiency >= 80 && progress.Status != "mastered" {
				progress.Status = "mastered"
				progress.MasteredAt = &now
			}
		} else if isCorrect != nil {
			progress.WrongCount++
			// 降低熟练度
			if progress.Proficiency > 10 {
				progress.Proficiency -= 10
			}
		}

		if err := s.db.Save(&progress).Error; err != nil {
			return nil, err
		}
	}

	// 返回更新后的进度
	return map[string]interface{}{
		"id":             fmt.Sprint(progress.ID),
		"userId":         fmt.Sprint(progress.UserID),
		"wordId":         fmt.Sprint(progress.VocabularyID),
		"status":         progress.Status,
		"studyCount":     progress.StudyCount,
		"correctCount":   progress.CorrectCount,
		"wrongCount":     progress.WrongCount,
		"proficiency":    progress.Proficiency,
		"nextReviewAt":   progress.NextReviewAt,
		"reviewInterval": progress.ReviewInterval,
		"firstStudiedAt": progress.FirstStudiedAt,
		"lastStudiedAt":  progress.LastStudiedAt,
		"masteredAt":     progress.MasteredAt,
	}, nil
}