QualifyingMembers Stored Procedure

//Stored Procedure
CREATE PROCEDURE GetQualifyingMembers
    @RewardCriteria NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    IF @RewardCriteria = 'Placed a Booking'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        INNER JOIN Bookings b ON b.Member_ID = m.Member_ID;
    END
    ELSE IF @RewardCriteria = 'Completed 10 Bookings in a Month'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        INNER JOIN Bookings b ON b.Member_ID = m.Member_ID
        INNER JOIN Booking_Time_Slots bts ON b.Booking_ID = bts.Booking_ID
        INNER JOIN Time_Slots ts ON bts.Time_Slot_ID = ts.Time_Slot_ID
        WHERE ts.Slot_Date >= DATEADD(MONTH, -1, GETDATE())
        GROUP BY m.Member_ID
        HAVING COUNT(b.Booking_ID) >= 10;
    END
    ELSE IF @RewardCriteria = 'Made 20 Bookings in Last 3 Months'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        INNER JOIN Bookings b ON b.Member_ID = m.Member_ID
        INNER JOIN Booking_Time_Slots bts ON b.Booking_ID = bts.Booking_ID
        INNER JOIN Time_Slots ts ON bts.Time_Slot_ID = ts.Time_Slot_ID
        WHERE ts.Slot_Date >= DATEADD(MONTH, -3, GETDATE())
        GROUP BY m.Member_ID
        HAVING COUNT(b.Booking_ID) >= 20;
    END
    -- Add more conditions based on other reward criteria as needed
    ELSE IF @RewardCriteria = 'Placed First Order'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        WHERE EXISTS (
            SELECT 1 FROM Orders o WHERE o.Member_ID = m.Member_ID AND o.Order_Date = (
                SELECT MIN(Order_Date) FROM Orders o2 WHERE o2.Member_ID = m.Member_ID
            )
        );
    END
    ELSE IF @RewardCriteria = 'Made a Payment'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        INNER JOIN Payments p ON p.Member_ID = m.Member_ID;
    END
    ELSE IF @RewardCriteria = 'High-Value Order'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        INNER JOIN Orders o ON o.Member_ID = m.Member_ID
        WHERE o.Total_Amount >= 1000; -- Example threshold, adjust as needed
    END
    ELSE IF @RewardCriteria = 'Large Quantity Order'
    BEGIN
        SELECT DISTINCT m.Member_ID
        FROM Members m
        INNER JOIN Order_Items oi ON oi.Order_ID = (
            SELECT TOP 1 o.Order_ID
            FROM Orders o
            WHERE o.Member_ID = m.Member_ID
            ORDER BY o.Order_Date DESC
        )
        WHERE oi.Quantity >= 10; -- Example threshold, adjust as needed
    END
    ELSE
    BEGIN
        -- Return an empty set if criteria do not match
        SELECT * FROM Members WHERE 1 = 0;
    END
END

//Service
public class QualifyingMembersService : IHostedService, IDisposable
    {
        private readonly IServiceProvider _serviceProvider;
        private Timer _timer;

        public QualifyingMembersService(IServiceProvider serviceProvider)
        {
            _serviceProvider = serviceProvider;
        }

        public Task StartAsync(CancellationToken cancellationToken)
        {
            _timer = new Timer(UpdateQualifyingMembers, null, TimeSpan.Zero, TimeSpan.FromMinutes(1)); // Check every 1 minute
            return Task.CompletedTask;
        }

        private async void UpdateQualifyingMembers(object state)
        {
            using (var scope = _serviceProvider.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
                var postedRewards = await context.Rewards
                    .Where(r => r.IsPosted)
                    .ToListAsync();

                foreach (var reward in postedRewards)
                {
                    var rewardType = await context.Reward_Types
                        .FindAsync(reward.Reward_Type_ID);
                    
                    if (rewardType != null)
                    {
                        var qualifyingMembers = await GetQualifyingMembersAsync(rewardType.Reward_Criteria);

                        foreach (var member in qualifyingMembers)
                        {
                            var existingRewardMember = await context.Reward_Members
                                .FirstOrDefaultAsync(rm => rm.Member_ID == member.Member_ID && rm.Reward_ID == reward.Reward_ID);

                            if (existingRewardMember == null)
                            {
                                var rewardMember = new Reward_Member
                                {
                                    Member_ID = member.Member_ID,
                                    Reward_ID = reward.Reward_ID,
                                    IsRedeemed = false
                                };
                                context.Reward_Members.Add(rewardMember);
                            }
                        }
                    }
                }

                await context.SaveChangesAsync();
            }
        }

        public async Task<List<Member>> GetQualifyingMembersAsync(string criteria)
        {
            var criteriaParam = new SqlParameter("@Criteria", criteria);
            return await _serviceProvider.GetRequiredService<AppDbContext>()
                .Members
                .FromSqlRaw("EXEC GetQualifyingMembers @Criteria", criteriaParam)
                .ToListAsync();
        }

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

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

//UpdateQualifyingMembersForReward
private async Task UpdateQualifyingMembersForReward(int rewardId)
{
    // Fetch reward and reward type
    var reward = await _appDbContext.Rewards.FindAsync(rewardId);
    if (reward != null)
    {
        var rewardType = await _appDbContext.Reward_Types.FindAsync(reward.Reward_Type_ID);
        if (rewardType != null)
        {
            // Use QualifyingMembersService to get qualifying members
            var qualifyingMembers = await _qualifyingMembersService.GetQualifyingMembersAsync(rewardType.Reward_Criteria);

            foreach (var member in qualifyingMembers)
            {
                var existingRewardMember = await _appDbContext.Reward_Members
                    .FirstOrDefaultAsync(rm => rm.Member_ID == member.Member_ID && rm.Reward_ID == reward.Reward_ID);

                // Add qualifying members to the Reward_Member table
                if (existingRewardMember == null)
                {
                    var rewardMember = new Reward_Member
                    {
                        Member_ID = member.Member_ID,
                        Reward_ID = reward.Reward_ID,
                        IsRedeemed = false
                    };
                    _appDbContext.Reward_Members.Add(rewardMember);
                }
            }

            await _appDbContext.SaveChangesAsync();
        }
    }
}