PlotDirector/PlotLine/Data/SubscriptionRepository.cs

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

namespace PlotLine.Data;

public interface ISubscriptionRepository
{
    Task<UserSubscriptionDetail?> GetCurrentSubscriptionAsync(int userId);
    Task<SubscriptionLevel?> GetSubscriptionLevelAsync(int subscriptionLevelId);
    Task<IReadOnlyList<SubscriptionLevel>> GetActiveSubscriptionLevelsAsync();
    Task<int> GetOwnedBookCountAsync(int userId);
    Task<int> GetChapterCountForBookAsync(int bookId);
    Task<int> GetSceneCountForBookAsync(int bookId);
    Task<int> GetCharacterCountForProjectAsync(int projectId);
    Task<int> GetCollaboratorCountForOwnerAsync(int userId);
    Task<SubscriptionUsageSummary> GetUsageSummaryForOwnerAsync(int userId);
    Task<SubscriptionLevel?> GetSubscriptionLevelByStripePriceAsync(string stripePriceId);
    Task SetStripeCustomerAsync(int userId, string stripeCustomerId);
    Task<bool> TryBeginStripeWebhookEventAsync(string stripeEventId, string eventType);
    Task CompleteStripeWebhookEventAsync(string stripeEventId, string processingStatus, string? errorMessage = null);
    Task ApplyStripeStateAsync(StripeSubscriptionSyncState state);
    Task DowngradeToDraftDeskAsync(string? stripeCustomerId, string? stripeSubscriptionId, string? lastStripeEventId);
    Task RecordPaymentFailureAsync(string? stripeCustomerId, string? stripeSubscriptionId, string? lastStripeEventId);
    Task ClearPaymentFailureAsync(string? stripeCustomerId, string? stripeSubscriptionId, string? lastStripeEventId);
}

public sealed class SubscriptionRepository(ISqlConnectionFactory connectionFactory) : ISubscriptionRepository
{
    public async Task<UserSubscriptionDetail?> GetCurrentSubscriptionAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<UserSubscriptionDetail>(
            "dbo.UserSubscription_GetCurrent",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<SubscriptionLevel?> GetSubscriptionLevelAsync(int subscriptionLevelId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<SubscriptionLevel>(
            "dbo.SubscriptionLevel_Get",
            new { SubscriptionLevelID = subscriptionLevelId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<SubscriptionLevel>> GetActiveSubscriptionLevelsAsync()
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<SubscriptionLevel>(
            "dbo.SubscriptionLevel_ListActive",
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<int> GetOwnedBookCountAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<int>(
            "dbo.SubscriptionUsage_BookCountForOwner",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<int> GetChapterCountForBookAsync(int bookId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<int>(
            "dbo.SubscriptionUsage_ChapterCountForBook",
            new { BookID = bookId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<int> GetSceneCountForBookAsync(int bookId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<int>(
            "dbo.SubscriptionUsage_SceneCountForBook",
            new { BookID = bookId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<int> GetCharacterCountForProjectAsync(int projectId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<int>(
            "dbo.SubscriptionUsage_CharacterCountForProject",
            new { ProjectID = projectId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<int> GetCollaboratorCountForOwnerAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<int>(
            "dbo.SubscriptionUsage_CollaboratorCountForOwner",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<SubscriptionUsageSummary> GetUsageSummaryForOwnerAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<SubscriptionUsageSummary>(
            "dbo.SubscriptionUsage_GetSummaryForOwner",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<SubscriptionLevel?> GetSubscriptionLevelByStripePriceAsync(string stripePriceId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<SubscriptionLevel>(
            "dbo.SubscriptionLevel_GetByStripePrice",
            new { StripePriceId = stripePriceId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task SetStripeCustomerAsync(int userId, string stripeCustomerId)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.UserSubscription_SetStripeCustomer",
            new { UserID = userId, StripeCustomerId = stripeCustomerId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<bool> TryBeginStripeWebhookEventAsync(string stripeEventId, string eventType)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<bool>(
            "dbo.StripeWebhookEvent_TryBegin",
            new { StripeEventId = stripeEventId, EventType = eventType },
            commandType: CommandType.StoredProcedure);
    }

    public async Task CompleteStripeWebhookEventAsync(string stripeEventId, string processingStatus, string? errorMessage = null)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.StripeWebhookEvent_Complete",
            new { StripeEventId = stripeEventId, ProcessingStatus = processingStatus, ErrorMessage = errorMessage },
            commandType: CommandType.StoredProcedure);
    }

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

    public async Task DowngradeToDraftDeskAsync(string? stripeCustomerId, string? stripeSubscriptionId, string? lastStripeEventId)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.UserSubscription_DowngradeToDraftDesk",
            new { StripeCustomerId = stripeCustomerId, StripeSubscriptionId = stripeSubscriptionId, LastStripeEventId = lastStripeEventId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task RecordPaymentFailureAsync(string? stripeCustomerId, string? stripeSubscriptionId, string? lastStripeEventId)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.UserSubscription_RecordPaymentFailure",
            new { StripeCustomerId = stripeCustomerId, StripeSubscriptionId = stripeSubscriptionId, LastStripeEventId = lastStripeEventId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task ClearPaymentFailureAsync(string? stripeCustomerId, string? stripeSubscriptionId, string? lastStripeEventId)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.UserSubscription_ClearPaymentFailure",
            new { StripeCustomerId = stripeCustomerId, StripeSubscriptionId = stripeSubscriptionId, LastStripeEventId = lastStripeEventId },
            commandType: CommandType.StoredProcedure);
    }
}