PlotDirector/PlotLine/Data/EmailQueueRepository.cs

using System.Data;
using Dapper;
using PlotLine.Models;

namespace PlotLine.Data;

public interface IEmailQueueRepository
{
    Task<long> CreateAsync(EmailQueueItem email);
    Task<IReadOnlyList<EmailQueueItem>> DequeueBatchAsync(int batchSize);
    Task MarkSentAsync(long emailQueueId);
    Task MarkFailedAsync(long emailQueueId, string lastError);
    Task ResetStuckSendingAsync();
}

public sealed class EmailQueueRepository(ISqlConnectionFactory connectionFactory) : IEmailQueueRepository
{
    public async Task<long> CreateAsync(EmailQueueItem email)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<long>(
            "dbo.EmailQueue_Create",
            new
            {
                email.ToEmail,
                email.ToName,
                email.FromEmail,
                email.FromName,
                email.Subject,
                email.PlainTextBody,
                email.HtmlBody,
                email.MaxAttempts
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<EmailQueueItem>> DequeueBatchAsync(int batchSize)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<EmailQueueItem>(
            "dbo.EmailQueue_DequeueBatch",
            new { BatchSize = batchSize },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task MarkSentAsync(long emailQueueId)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.EmailQueue_MarkSent",
            new { EmailQueueID = emailQueueId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task MarkFailedAsync(long emailQueueId, string lastError)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.EmailQueue_MarkFailed",
            new { EmailQueueID = emailQueueId, LastError = lastError },
            commandType: CommandType.StoredProcedure);
    }

    public async Task ResetStuckSendingAsync()
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.EmailQueue_ResetStuckSending",
            commandType: CommandType.StoredProcedure);
    }
}