jobtrackingapp/JobTrackerApi/Services/DatabaseBackupRunner.cs

using Microsoft.Data.Sqlite;

namespace JobTrackerApi.Services
{
    public interface IDatabaseBackupRunner
    {
        string BackupsRoot { get; }
        bool IsSupported { get; }

        /// <summary>Creates one backup file and prunes old ones. Returns the backup path, or null when unsupported.</summary>
        Task<string?> RunOnceAsync(CancellationToken ct);

        DateTime? GetLatestBackupUtc();
    }

    public sealed class SqliteDatabaseBackupRunner : IDatabaseBackupRunner
    {
        public const string BackupFilePrefix = "jobtracker_backup_";

        private readonly ILogger<SqliteDatabaseBackupRunner> _logger;
        private readonly string _connectionString;
        private readonly int _retainCount;

        public string BackupsRoot { get; }
        public bool IsSupported { get; }

        public SqliteDatabaseBackupRunner(IConfiguration cfg, AppPaths paths, ILogger<SqliteDatabaseBackupRunner> logger)
        {
            _logger = logger;

            var provider = (cfg["Database:Provider"] ?? "sqlite").Trim().ToLowerInvariant();
            var cs = cfg.GetConnectionString("JobTracker");
            if (string.IsNullOrWhiteSpace(cs))
            {
                cs = $"Data Source={paths.GetDbPath()}";
                provider = "sqlite";
            }

            _connectionString = cs;
            IsSupported = provider == "sqlite";
            BackupsRoot = Path.Combine(paths.DataRoot, "backups");
            _retainCount = Math.Clamp(cfg.GetValue("Backups:RetainCount", 14), 1, 365);
        }

        // Test-friendly constructor.
        public SqliteDatabaseBackupRunner(string connectionString, string backupsRoot, int retainCount, ILogger<SqliteDatabaseBackupRunner> logger)
        {
            _logger = logger;
            _connectionString = connectionString;
            IsSupported = true;
            BackupsRoot = backupsRoot;
            _retainCount = Math.Clamp(retainCount, 1, 365);
        }

        public async Task<string?> RunOnceAsync(CancellationToken ct)
        {
            if (!IsSupported)
            {
                _logger.LogWarning("Automated backups only support the SQLite provider. Configure external backups for MySQL/MariaDB.");
                return null;
            }

            Directory.CreateDirectory(BackupsRoot);

            var target = Path.Combine(BackupsRoot, $"{BackupFilePrefix}{DateTime.UtcNow:yyyyMMdd_HHmmss}.db");
            if (File.Exists(target)) File.Delete(target);

            await using (var connection = new SqliteConnection(_connectionString))
            {
                await connection.OpenAsync(ct);
                await using var command = connection.CreateCommand();
                // VACUUM INTO produces a consistent, compacted snapshot without blocking writers (WAL).
                command.CommandText = $"VACUUM INTO '{target.Replace("'", "''")}'";
                await command.ExecuteNonQueryAsync(ct);
            }

            _logger.LogInformation("Database backup written: {File}.", target);
            PruneOldBackups();
            return target;
        }

        public DateTime? GetLatestBackupUtc()
        {
            if (!Directory.Exists(BackupsRoot)) return null;
            var latest = ListBackups().FirstOrDefault();
            return latest?.LastWriteTimeUtc;
        }

        private void PruneOldBackups()
        {
            foreach (var stale in ListBackups().Skip(_retainCount))
            {
                try
                {
                    stale.Delete();
                    _logger.LogInformation("Pruned old database backup: {File}.", stale.Name);
                }
                catch (IOException ex)
                {
                    _logger.LogWarning(ex, "Could not prune old database backup {File}.", stale.Name);
                }
            }
        }

        private IOrderedEnumerable<FileInfo> ListBackups()
            => new DirectoryInfo(BackupsRoot)
                .EnumerateFiles($"{BackupFilePrefix}*.db")
                .OrderByDescending(f => f.LastWriteTimeUtc);
    }
}