chapter-organizer/WebApp/Services/YearRolloverService.cs

using Core.YearTransition;
using Data;
using Microsoft.EntityFrameworkCore;

namespace WebApp.Services;

/// <summary>
/// Applies a year-transition plan: backup, wipe season data, promote/remove students, update year.
/// </summary>
public class YearRolloverService : IYearRolloverService
{
    private readonly AppDbContext _context;
    private readonly IDatabaseBackupService _backupService;
    private readonly IChapterSettingsWriter _chapterSettingsWriter;
    private readonly INotesService _notesService;
    private readonly ILogger<YearRolloverService> _logger;

    public YearRolloverService(
        AppDbContext context,
        IDatabaseBackupService backupService,
        IChapterSettingsWriter chapterSettingsWriter,
        INotesService notesService,
        ILogger<YearRolloverService> logger)
    {
        _context = context;
        _backupService = backupService;
        _chapterSettingsWriter = chapterSettingsWriter;
        _notesService = notesService;
        _logger = logger;
    }

    public async Task<YearRolloverResult> ApplyAsync(
        YearRolloverOptions options,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(options);
        ArgumentNullException.ThrowIfNull(options.Plan);

        var plan = options.Plan;
        var returningIds = plan.Promotions.Select(p => p.Student.Id).ToHashSet();
        var removalIds = plan.StudentsToRemove.Select(s => s.Id).ToHashSet();

        _logger.LogInformation(
            "Starting year rollover to {Year}: {Returning} returning, {Removing} removing, clearOccurrences={ClearOccurrences}",
            plan.TargetCompetitionYear,
            plan.ReturningCount,
            plan.RemovalCount,
            options.ClearEventOccurrences);

        // VACUUM INTO cannot run inside a transaction — backup first and abort if it fails.
        var backupPath = await _backupService.CreatePreRolloverBackupAsync(cancellationToken);

        int meetingHistoriesDeleted;
        int teamsDeleted;
        int rankingsDeleted;
        int eventOccurrencesDeleted;
        int studentsRemoved;
        int studentsPromoted;
        List<string> officerSummary;

        await using (var transaction = await _context.Database.BeginTransactionAsync(cancellationToken))
        {
            try
            {
                // Delete season data with ExecuteDelete / SQL so we never leave tracked Team
                // entities in the change tracker (Include+Remove then ExecuteDelete caused
                // optimistic concurrency failures when later deleting captain students).
                meetingHistoriesDeleted = await _context.TeamMeetingHistories.CountAsync(cancellationToken);
                await _context.Database.ExecuteSqlRawAsync(
                    """DELETE FROM "TeamMeetingHistoryTeams" """, cancellationToken);
                await _context.Database.ExecuteSqlRawAsync(
                    """DELETE FROM "TeamMeetingHistoryStudents" """, cancellationToken);
                await _context.TeamMeetingHistories.ExecuteDeleteAsync(cancellationToken);

                teamsDeleted = await _context.Teams.ExecuteDeleteAsync(cancellationToken);
                rankingsDeleted = await _context.StudentEventRanking.ExecuteDeleteAsync(cancellationToken);

                eventOccurrencesDeleted = 0;
                if (options.ClearEventOccurrences)
                {
                    eventOccurrencesDeleted = await _context.EventOccurrences.ExecuteDeleteAsync(cancellationToken);
                }

                // Drop any stale tracked entities from earlier queries in this request scope.
                _context.ChangeTracker.Clear();

                var students = await _context.Students.ToListAsync(cancellationToken);
                var toRemove = students.Where(s => removalIds.Contains(s.Id)).ToList();
                var toPromote = students.Where(s => returningIds.Contains(s.Id)).ToList();

                if (toRemove.Count != removalIds.Count)
                {
                    throw new InvalidOperationException(
                        "Some students marked for removal were not found in the database. Aborting rollover.");
                }

                if (toPromote.Count != returningIds.Count)
                {
                    throw new InvalidOperationException(
                        "Some returning students were not found in the database. Aborting rollover.");
                }

                // Extra students added while the wizard was open — refuse rather than leave them unprocessed.
                var plannedIds = returningIds.Union(removalIds).ToHashSet();
                var unexpected = students.Where(s => !plannedIds.Contains(s.Id)).ToList();
                if (unexpected.Count > 0)
                {
                    var names = string.Join(", ", unexpected.Select(s => s.LastNameFirstName));
                    throw new InvalidOperationException(
                        $"Roster changed since the wizard loaded ({unexpected.Count} unexpected student(s): {names}). Reload the page and try again.");
                }

                await _notesService.SoftDeleteStudentNotesAsync(removalIds, cancellationToken);

                _context.Students.RemoveRange(toRemove);

                var promotionById = plan.Promotions.ToDictionary(p => p.Student.Id);
                foreach (var student in toPromote)
                {
                    var promotion = promotionById[student.Id];
                    student.Grade = promotion.NewGrade;
                    student.TsaYear = promotion.NewTsaYear;
                    student.OfficerRole = promotion.NewOfficerRole;
                }

                await _context.SaveChangesAsync(cancellationToken);
                await transaction.CommitAsync(cancellationToken);

                studentsRemoved = toRemove.Count;
                studentsPromoted = toPromote.Count;
                officerSummary = plan.OfficerChanges
                    .Select(c => c.NewOfficer == null
                        ? $"{c.Role}: (vacant)"
                        : $"{c.Role}: {c.NewOfficer.LastNameFirstName}")
                    .ToList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Year rollover failed after backup at {BackupPath}; rolling back database changes", backupPath);
                await transaction.RollbackAsync(cancellationToken);
                throw;
            }
        }

        try
        {
            await _chapterSettingsWriter.UpdateCompetitionYearAsync(
                plan.TargetCompetitionYear,
                cancellationToken);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex,
                "Year rollover DB changes committed but CompetitionYear file update failed. Backup={BackupPath}",
                backupPath);
            throw new InvalidOperationException(
                $"Database rollover succeeded (backup at '{backupPath}'), but updating CompetitionYear failed: {ex.Message}. Set the year in Chapter Settings, then restart.",
                ex);
        }

        var result = new YearRolloverResult
        {
            BackupPath = backupPath,
            StudentsRemoved = studentsRemoved,
            StudentsPromoted = studentsPromoted,
            TeamsDeleted = teamsDeleted,
            RankingsDeleted = rankingsDeleted,
            MeetingHistoriesDeleted = meetingHistoriesDeleted,
            EventOccurrencesDeleted = eventOccurrencesDeleted,
            CompetitionYear = plan.TargetCompetitionYear,
            OfficerSummary = officerSummary
        };

        _logger.LogInformation(
            "Year rollover complete. Backup={BackupPath}, Removed={Removed}, Promoted={Promoted}, Teams={Teams}, Rankings={Rankings}, Histories={Histories}, Occurrences={Occurrences}, Officers={Officers}",
            result.BackupPath,
            result.StudentsRemoved,
            result.StudentsPromoted,
            result.TeamsDeleted,
            result.RankingsDeleted,
            result.MeetingHistoriesDeleted,
            result.EventOccurrencesDeleted,
            string.Join("; ", officerSummary));

        return result;
    }
}