dramaling-vocab-learning/backend/DramaLing.Api/Repositories/FlashcardReviewRepository.cs

using DramaLing.Api.Contracts.Repositories;
using Microsoft.EntityFrameworkCore;
using DramaLing.Api.Data;
using DramaLing.Api.Models.Entities;
using DramaLing.Api.Models.DTOs;

namespace DramaLing.Api.Repositories;

public class FlashcardReviewRepository : BaseRepository<FlashcardReview>, IFlashcardReviewRepository
{
    public FlashcardReviewRepository(
        DramaLingDbContext context,
        ILogger<BaseRepository<FlashcardReview>> logger) : base(context, logger)
    {
    }

    public async Task<IEnumerable<(Flashcard Flashcard, FlashcardReview? Review)>> GetDueFlashcardsAsync(
        Guid userId,
        DueFlashcardsQuery query)
    {
        var now = DateTime.UtcNow;
        var cutoffDate = now;

        if (query.IncludeToday)
        {
            cutoffDate = cutoffDate.AddDays(1); // 包含今天到期的
        }

        // 簡化查詢：分別獲取詞卡和複習記錄，避免複雜的 GroupJoin

        // 首先獲取用戶的詞卡
        var flashcardsQuery = _context.Flashcards
            .Where(f => f.UserId == userId && !f.IsArchived);

        // 如果只要收藏的卡片
        if (query.FavoritesOnly)
        {
            flashcardsQuery = flashcardsQuery.Where(f => f.IsFavorite);
        }

        var allFlashcards = await flashcardsQuery.ToListAsync();

        // 獲取用戶的所有複習記錄
        var reviewsDict = await _context.FlashcardReviews
            .Where(fr => fr.UserId == userId)
            .ToDictionaryAsync(fr => fr.FlashcardId, fr => fr);

        // 在記憶體中進行篩選和排序
        var candidateItems = allFlashcards.Select(flashcard =>
        {
            reviewsDict.TryGetValue(flashcard.Id, out var review);
            return new { Flashcard = flashcard, Review = review };
        })
        .Where(x =>
            // 沒有複習記錄的新卡片
            x.Review == null ||
            // 或者到期需要複習的卡片
            (x.Review.NextReviewDate <= cutoffDate))
        .Where(x =>
            // 如果不包含過期，過濾掉過期的卡片
            query.IncludeOverdue || x.Review == null || x.Review.NextReviewDate >= now.Date)
        .OrderBy(x => x.Review?.NextReviewDate ?? DateTime.MinValue)
        .ThenBy(x => x.Flashcard.CreatedAt)
        .Take(query.Limit);

        var results = candidateItems.ToList();

        return results.Select(x => (x.Flashcard, x.Review));
    }

    public async Task<FlashcardReview> GetOrCreateReviewAsync(Guid userId, Guid flashcardId)
    {
        var existingReview = await _context.FlashcardReviews
            .FirstOrDefaultAsync(fr => fr.UserId == userId && fr.FlashcardId == flashcardId);

        if (existingReview != null)
        {
            return existingReview;
        }

        // 創建新的複習記錄
        var newReview = new FlashcardReview
        {
            Id = Guid.NewGuid(),
            FlashcardId = flashcardId,
            UserId = userId,
            SuccessCount = 0,
            NextReviewDate = DateTime.UtcNow.AddDays(1), // 新卡片明天複習
            TotalSkipCount = 0,
            TotalWrongCount = 0,
            TotalCorrectCount = 0,
            CreatedAt = DateTime.UtcNow,
            UpdatedAt = DateTime.UtcNow
        };

        await _context.FlashcardReviews.AddAsync(newReview);
        await _context.SaveChangesAsync(); // 立即保存新記錄
        return newReview;
    }

    public async Task<FlashcardReview?> GetByUserAndFlashcardAsync(Guid userId, Guid flashcardId)
    {
        return await _context.FlashcardReviews
            .FirstOrDefaultAsync(fr => fr.UserId == userId && fr.FlashcardId == flashcardId);
    }

    public async Task<(int TodayDue, int Overdue, int TotalReviews)> GetReviewStatsAsync(Guid userId)
    {
        var now = DateTime.UtcNow;
        var today = now.Date;
        var tomorrow = today.AddDays(1);

        var userReviews = _context.FlashcardReviews.Where(fr => fr.UserId == userId);

        var todayDue = await userReviews
            .CountAsync(fr => fr.NextReviewDate >= today && fr.NextReviewDate < tomorrow);

        var overdue = await userReviews
            .CountAsync(fr => fr.NextReviewDate < today);

        var totalReviews = await userReviews
            .SumAsync(fr => fr.TotalCorrectCount + fr.TotalWrongCount + fr.TotalSkipCount);

        return (todayDue, overdue, totalReviews);
    }

    public async Task<int> GetTodayDueCountAsync(Guid userId)
    {
        var today = DateTime.UtcNow.Date;
        var tomorrow = today.AddDays(1);

        return await _context.FlashcardReviews
            .Where(fr => fr.UserId == userId)
            .CountAsync(fr => fr.NextReviewDate >= today && fr.NextReviewDate < tomorrow);
    }

    public async Task<int> GetOverdueCountAsync(Guid userId)
    {
        var today = DateTime.UtcNow.Date;

        return await _context.FlashcardReviews
            .Where(fr => fr.UserId == userId)
            .CountAsync(fr => fr.NextReviewDate < today);
    }

    public async Task UpdateReviewAsync(FlashcardReview review)
    {
        review.UpdatedAt = DateTime.UtcNow;
        _context.FlashcardReviews.Update(review);
        await _context.SaveChangesAsync();
    }
}