Stored Procedure for UpdateUserDeletionSettings and UserDeletionService (WORKING VERSION)

//SQL QUERY
CREATE PROCEDURE UpdateUserDeletionSettings
    @DeletionTimeValue INT,
    @DeletionTimeUnit NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Define the deletion threshold
    DECLARE @DeletionThreshold DATETIME;
    SET @DeletionThreshold = CASE
        WHEN @DeletionTimeUnit = 'Minutes' THEN DATEADD(MINUTE, -@DeletionTimeValue, GETUTCDATE())
        WHEN @DeletionTimeUnit = 'Hours' THEN DATEADD(HOUR, -@DeletionTimeValue, GETUTCDATE())
        WHEN @DeletionTimeUnit = 'Days' THEN DATEADD(DAY, -@DeletionTimeValue, GETUTCDATE())
        WHEN @DeletionTimeUnit = 'Weeks' THEN DATEADD(WEEK, -@DeletionTimeValue, GETUTCDATE())
        WHEN @DeletionTimeUnit = 'Months' THEN DATEADD(MONTH, -@DeletionTimeValue, GETUTCDATE())
        WHEN @DeletionTimeUnit = 'Years' THEN DATEADD(YEAR, -@DeletionTimeValue, GETUTCDATE())
        ELSE GETUTCDATE()
    END;

    -- Delete users based on the threshold
    DELETE FROM dbo.AspNetUsers
    WHERE User_Status_ID = 2 AND DeactivatedAt < @DeletionThreshold;
END

//UserDeletionService
using av_motion_api.Data; 
using av_motion_api.Models;
using Microsoft.AspNetCore.Identity;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace av_motion_api.Services
{
    public class UserDeletionService : IHostedService, IDisposable
    {
        private readonly IServiceProvider _serviceProvider;
        private readonly IOptionsMonitor<DeletionSettings> _settings;
        private Timer _timer;
        private long _remainingIntervals;
        private const long MaxInterval = Int32.MaxValue - 2;

        public UserDeletionService(IServiceProvider serviceProvider, IOptionsMonitor<DeletionSettings> settings)
        {
            _serviceProvider = serviceProvider;
            _settings = settings;
        }

        public Task StartAsync(CancellationToken cancellationToken)
        {
            ScheduleDeletionTask();
            _settings.OnChange(settings => ScheduleDeletionTask());
            return Task.CompletedTask;
        }

        private void ScheduleDeletionTask()
        {
            var interval = GetTimeSpan(_settings.CurrentValue.DeletionTimeValue, _settings.CurrentValue.DeletionTimeUnit);
            _remainingIntervals = (long)Math.Ceiling(interval.TotalMilliseconds / MaxInterval);

            _timer?.Dispose();
            _timer = new Timer(OnTimerElapsed, null, TimeSpan.Zero, TimeSpan.FromMilliseconds(MaxInterval));
        }



        private void OnTimerElapsed(object state)
        {
            if (--_remainingIntervals <= 0)
            {
                DeleteDeactivatedUsers(state);
                ScheduleDeletionTask(); // Reschedule for the next interval
            }
        }

        private void DeleteDeactivatedUsers(object state)
        {
            using (var scope = _serviceProvider.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                var deletionThreshold = DateTime.UtcNow.Subtract(GetTimeSpan(_settings.CurrentValue.DeletionTimeValue, _settings.CurrentValue.DeletionTimeUnit));

                var connection = context.Database.GetDbConnection();
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "UpdateUserDeletionSettings";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@DeletionTimeValue", _settings.CurrentValue.DeletionTimeValue));
                    command.Parameters.Add(new SqlParameter("@DeletionTimeUnit", _settings.CurrentValue.DeletionTimeUnit));

                    command.ExecuteNonQuery();
                }
            }
        }


        private TimeSpan GetTimeSpan(int value, string unit)
        {
            return unit.ToLower() switch
            {
                "minutes" => TimeSpan.FromMinutes(value),
                "hours" => TimeSpan.FromHours(value),
                "days" => TimeSpan.FromDays(value),
                "weeks" => TimeSpan.FromDays(value * 7),
                "months" => TimeSpan.FromDays(value * 30), // Approximation
                "years" => TimeSpan.FromDays(value * 365), // Approximation
                _ => TimeSpan.FromMinutes(value),
            };
        }

        public Task StopAsync(CancellationToken cancellationToken)
        {
            _timer?.Change(Timeout.Infinite, 0);
            return Task.CompletedTask;
        }

        public void Dispose()
        {
            _timer?.Dispose();
        }
    }
}

//Program.cs --unchanged
using av_motion_api.Data;
using av_motion_api.Factory;
using av_motion_api.Models;
using av_motion_api.Interfaces;
using av_motion_api.Services;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Microsoft.IdentityModel.Tokens;
using Microsoft.AspNetCore.Http.Features;
using Microsoft.Extensions.Logging;
using System.Text;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using System.Text.Json.Serialization;
using Microsoft.Extensions.Configuration; // Ensure this is here
using Microsoft.Extensions.Hosting;

var builder = WebApplication.CreateBuilder(args);

// Configure the app environment
var configuration = builder.Configuration;

builder.Configuration.SetBasePath(Directory.GetCurrentDirectory())
    //.AddJsonFile($"appsettings.{builder.Environment.EnvironmentName}.json", optional: false);
    .AddJsonFile("appsettings.Development.json", optional: true, reloadOnChange: true); // Add reloadOnChange

builder.Host.ConfigureAppConfiguration((hostingContext, config) =>
{
    //config.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
    config.AddJsonFile("appsettings.Development.json", optional: true, reloadOnChange: true); // Add reloadOnChange
    config.AddJsonFile($"appsettings.{builder.Environment.EnvironmentName}.json", optional: true);
});

// Configure logging
builder.Logging.ClearProviders();
builder.Logging.AddConsole();
builder.Logging.AddDebug();

// CORS
if (builder.Environment.IsDevelopment())
{
    builder.Services.AddCors(options =>
    {
        options.AddPolicy("AllowAll", policy =>
        {
            policy.AllowAnyOrigin()
                  .AllowAnyHeader()
                  .AllowAnyMethod();
        });
    });
}

// Add services to the container
builder.Services.AddControllers()
                .AddJsonOptions(options =>
                {
                    options.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
                    options.JsonSerializerOptions.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;
                });

// SQL
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IRepository, Repository>();

builder.Services.AddIdentity<User, Role>(options =>
                {
                    options.Password.RequireUppercase = false;
                    options.Password.RequireLowercase = false;
                    options.Password.RequireNonAlphanumeric = false;
                    options.Password.RequireDigit = true;
                    options.User.RequireUniqueEmail = true;
                })
                .AddRoles<Role>()
                .AddEntityFrameworkStores<AppDbContext>()
                .AddDefaultTokenProviders();

builder.Services.AddAuthentication(options =>
                {
                    options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
                    options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
                    options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
                })
                .AddCookie()
                .AddJwtBearer(options =>
                {
                    options.TokenValidationParameters = new TokenValidationParameters()
                    {
                        ValidateIssuer = true,
                        ValidateAudience = true,
                        ValidateLifetime = true,
                        ValidateIssuerSigningKey = true,
                        ValidIssuer = builder.Configuration["Tokens:Issuer"],
                        ValidAudience = builder.Configuration["Tokens:Audience"],
                        IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(builder.Configuration["Tokens:Key"]))
                    };
                });

// Configure FormOptions for file uploads
builder.Services.Configure<FormOptions>(o =>
{
    o.ValueLengthLimit = int.MaxValue;
    o.MultipartBodyLengthLimit = int.MaxValue;
    o.MemoryBufferThreshold = int.MaxValue;
});

builder.Services.AddScoped<IUserClaimsPrincipalFactory<User>, AppUserClaimsPrincipalFactory>();

builder.Services.Configure<DataProtectionTokenProviderOptions>(options => options.TokenLifespan = TimeSpan.FromHours(3));


// Register the OrderStatusUpdater hosted service
builder.Services.AddHostedService<OrderStatusUpdater>();

// Register the DeletionSettings configuration section
builder.Services.Configure<DeletionSettings>(configuration.GetSection("DeletionSettings"));

// Register the UserDeletionService hosted service
builder.Services.AddHostedService<UserDeletionService>();

// Register ContractLinkingService
//builder.Services.AddHostedService<ContractLinkingService>();


// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

// Use CORS
app.UseCors("AllowAll");

app.UseHttpsRedirection();
app.UseAuthentication();
app.UseAuthorization();

app.MapControllers();

app.Use(async (context, next) =>
{
    var logger = app.Services.GetRequiredService<ILogger<Program>>();
    logger.LogInformation("Handling request: " + context.Request.Path);
    await next.Invoke();
    logger.LogInformation("Finished handling request.");
});

app.Run();

//appsettings.Development.json --unchanged
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-9QI4G7U\\SQLEXPRESS;Database=AV_Motion;Trusted_Connection=True;TrustServerCertificate=True"
  },
  "Tokens": {
    "Key": "hLKjZkF5q2wX3rQ$@1hy+VRv[&)0XhxJ<sk=yUpW{yE5CH@xh",
    "Issuer": "https://localhost:7185",
    "Audience": "http://localhost:4200"
  },
  "SendGrid": {
    "ApiKey": "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",
    "SenderEmail": "datalungeteam43@gmail.com",
    "SenderName": "AVSFitnessAdmin@AVMotion.com",
    "ContractsDirectory": "C:\\Contracts\\AVS_Fitness"
  },
  "DeletionSettings": {
    "DeletionTimeValue": 1,
    "DeletionTimeUnit": "Minutes"
  }
}
  
//AppDbContext --Add & run migration
  public DbSet<DeletionSettings> DeletionSettings { get; set; }

  // Configure the DeletionSettings entity
  builder.Entity<DeletionSettings>()
    .HasKey(ds => new { ds.DeletionTimeValue, ds.DeletionTimeUnit });

  builder.Entity<DeletionSettings>()
    .Property(ds => ds.DeletionTimeValue)
    .IsRequired();

  builder.Entity<DeletionSettings>()
    .Property(ds => ds.DeletionTimeUnit)
    .IsRequired()
    .HasMaxLength(50);