PlotDirector/PlotLine/Data/StoryIntelligenceRepository.cs

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

namespace PlotLine.Data;

public interface IStoryIntelligenceRepository
{
    Task<StoryIntelligenceJob?> CreateWithConsentAsync(int userId, int onboardingStateId, int projectId, int bookId, string consentText);
    Task<StoryIntelligenceJob?> GetAsync(int userId, Guid jobId);
    Task<StoryIntelligenceJob?> GetLatestForUserAsync(int userId);
    Task<StoryIntelligenceJob?> ClaimNextPendingAsync();
    Task<StoryIntelligenceJob?> UpdateProgressAsync(Guid jobId, string status, int progressPercent, string currentStage, string currentMessage, string? estimatedRemaining);
    Task<StoryIntelligenceJob?> CompleteAsync(Guid jobId);
    Task<StoryIntelligenceJob?> CancelAsync(int userId, Guid jobId);
    Task<StoryIntelligenceJob?> FailAsync(Guid jobId, string errorMessage);
}

public sealed class StoryIntelligenceRepository(ISqlConnectionFactory connectionFactory) : IStoryIntelligenceRepository
{
    public async Task<StoryIntelligenceJob?> CreateWithConsentAsync(int userId, int onboardingStateId, int projectId, int bookId, string consentText)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_CreateWithConsent",
            new
            {
                UserID = userId,
                UserOnboardingStateID = onboardingStateId,
                ProjectID = projectId,
                BookID = bookId,
                ConsentText = consentText
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> GetAsync(int userId, Guid jobId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_Get",
            new { UserID = userId, JobID = jobId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> GetLatestForUserAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_GetLatestForUser",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> ClaimNextPendingAsync()
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_ClaimNextPending",
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> UpdateProgressAsync(Guid jobId, string status, int progressPercent, string currentStage, string currentMessage, string? estimatedRemaining)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_UpdateProgress",
            new
            {
                JobID = jobId,
                Status = status,
                ProgressPercent = progressPercent,
                CurrentStage = currentStage,
                CurrentMessage = currentMessage,
                EstimatedRemaining = estimatedRemaining
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> CompleteAsync(Guid jobId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_Complete",
            new { JobID = jobId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> CancelAsync(int userId, Guid jobId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_Cancel",
            new { UserID = userId, JobID = jobId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<StoryIntelligenceJob?> FailAsync(Guid jobId, string errorMessage)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceJob>(
            "dbo.StoryIntelligenceJob_Fail",
            new { JobID = jobId, ErrorMessage = errorMessage },
            commandType: CommandType.StoredProcedure);
    }
}