squad-rain-ops-mini/Services/DatabaseCleanupService.cs

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using RainOpsMini.Helpers;
using RainOpsMini.Helpers.SquadGameLog;
using RainOpsMini.Models;
using SqlSugar;
using System;
using System.Threading;
using System.Threading.Tasks;

namespace RainOpsMini.Services
{
    public class DatabaseCleanupService : IHostedService
    {
        private readonly ILogger<DatabaseCleanupService> _logger;

        public DatabaseCleanupService(ILogger<DatabaseCleanupService> logger)
        {
            _logger = logger;
        }

        public Task StartAsync(CancellationToken cancellationToken)
        {
            _logger.LogInformation("[Cleanup] Database Cleanup Service starting...");

            // Run cleanup in a background thread to not block startup
            Task.Run(() =>
            {
                try
                {
                    CleanupDatabase();
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "[Cleanup] Fatal error during cleanup task.");
                }
            });

            return Task.CompletedTask;
        }

        public Task StopAsync(CancellationToken cancellationToken)
        {
            return Task.CompletedTask;
        }

        private void CleanupDatabase()
        {
            try
            {
                using (var db = DbHelper.GetInstance())
                {
                    _logger.LogInformation("[Cleanup] Checking tables for cleanup...");

                    // 1. Clean ChatLogs (Keep latest 1000 - Existing logic)
                    CleanTableCount<ChatLog>(db, 1000);

                    // 2. Clean Game Logs (Keep 7 days)
                    int retentionDays = 7;
                    CleanTableByDate<Log_Disconnect>(db, retentionDays);
                    CleanTableByDate<Log_MatchLost>(db, retentionDays);
                    CleanTableByDate<Log_CreateSquad>(db, retentionDays);
                    CleanTableByDate<Log_NewPlayerConnect>(db, retentionDays);
                    CleanTableByDate<Log_Wound>(db, retentionDays);
                    CleanTableByDate<Log_Die>(db, retentionDays);
                    CleanTableByDate<Log_Revived>(db, retentionDays);
                    CleanTableByDate<Log_Attack>(db, retentionDays);
                    CleanTableByDate<Log_Tick>(db, retentionDays);
                    CleanTableByDate<Log_BattleEnd>(db, retentionDays);
                    CleanTableByDate<Log_RemovedPlayer>(db, retentionDays);
                    CleanTableByDate<Log_SetNextLayer>(db, retentionDays);
                    CleanTableByDate<Log_ChangeLayer>(db, retentionDays);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[Cleanup] Error accessing database.");
            }
        }

        private void CleanTableCount<T>(SqlSugarScope db, int keepCount) where T : class, new()
        {
            try 
            {
                var total = db.Queryable<T>().Count();
                if (total > keepCount)
                {
                    var tableName = db.EntityMaintenance.GetTableName<T>();
                    _logger.LogInformation($"[Cleanup] Cleaning table {tableName}. Total rows: {total}, Limit: {keepCount}");
                    
                    string sql = $"DELETE FROM {tableName} WHERE Id NOT IN (SELECT Id FROM {tableName} ORDER BY Id DESC LIMIT {keepCount})";
                    int affected = db.Ado.ExecuteCommand(sql);
                    
                    _logger.LogInformation($"[Cleanup] Removed {affected} old records from {tableName}.");
                }
            }
            catch (Exception ex)
            {
                 _logger.LogError(ex, $"[Cleanup] Failed to clean table {typeof(T).Name} by count");
            }
        }

        private void CleanTableByDate<T>(SqlSugarScope db, int days) where T : class, new()
        {
            try
            {
                var date = DateTime.Now.AddDays(-days);
                var tableName = db.EntityMaintenance.GetTableName<T>();
                
                // Assumes LogTime column exists and is mapped
                int affected = db.Deleteable<T>().Where("LogTime < @date", new { date }).ExecuteCommand();

                if (affected > 0)
                {
                    _logger.LogInformation($"[Cleanup] Removed {affected} old records from {tableName} (older than {days} days).");
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"[Cleanup] Failed to clean table {typeof(T).Name} by date");
            }
        }
    }
}