PlotDirector/PlotLine/Data/FeatureRequestRepository.cs

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

namespace PlotLine.Data;

public interface IFeatureRequestRepository
{
    Task<FeatureRequest> CreateAsync(int userId, string title, string description, string? appArea, string? importance);
    Task<FeatureRequest?> GetForUserAsync(int featureRequestId, int userId);
    Task<FeatureRequest?> GetForAdminAsync(int featureRequestId);
    Task<IReadOnlyList<FeatureRequest>> ListForUserAsync(int userId);
    Task<IReadOnlyList<FeatureRequest>> ListForAdminAsync(string? status, string? appArea, string? importance, string? search, string sort);
    Task<IReadOnlyList<FeatureRequest>> ListRoadmapAsync();
    Task<FeatureRequest?> UpdateStatusAsync(int featureRequestId, string status, bool isPublicCandidate);
    Task<FeatureRequest?> UpdateRoadmapVisibilityAsync(int featureRequestId, bool isPublicCandidate);
    Task<FeatureRequest?> UpdateAdminNotesAsync(int featureRequestId, string? adminNotes);
    Task<FeatureRequestMessage?> AddMessageAsync(int featureRequestId, int? userId, bool isAdmin, string messageBody);
    Task<IReadOnlyList<FeatureRequestMessage>> ListMessagesAsync(int featureRequestId);
    Task<bool> MarkViewedForUserAsync(int featureRequestId, int userId);
    Task<int> CountAttentionForUserAsync(int userId);
    Task<int> CountWaitingForAdminAsync();
}

public sealed class FeatureRequestRepository(ISqlConnectionFactory connectionFactory) : IFeatureRequestRepository
{
    public async Task<FeatureRequest> CreateAsync(int userId, string title, string description, string? appArea, string? importance)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<FeatureRequest>(
            "dbo.FeatureRequest_Create",
            new { UserID = userId, Title = title, Description = description, AppArea = appArea, Importance = importance },
            commandType: CommandType.StoredProcedure);
    }

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

    public async Task<FeatureRequest?> GetForAdminAsync(int featureRequestId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<FeatureRequest>(
            "dbo.FeatureRequest_GetForAdmin",
            new { FeatureRequestID = featureRequestId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<FeatureRequest>> ListForUserAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<FeatureRequest>(
            "dbo.FeatureRequest_ListForUser",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<IReadOnlyList<FeatureRequest>> ListForAdminAsync(string? status, string? appArea, string? importance, string? search, string sort)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<FeatureRequest>(
            "dbo.FeatureRequest_ListForAdmin",
            new { Status = status, AppArea = appArea, Importance = importance, Search = search, Sort = sort },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

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

    public async Task<FeatureRequest?> UpdateStatusAsync(int featureRequestId, string status, bool isPublicCandidate)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<FeatureRequest>(
            "dbo.FeatureRequest_UpdateStatus",
            new { FeatureRequestID = featureRequestId, Status = status, IsPublicCandidate = isPublicCandidate },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<FeatureRequest?> UpdateRoadmapVisibilityAsync(int featureRequestId, bool isPublicCandidate)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<FeatureRequest>(
            "dbo.FeatureRequest_UpdateRoadmapVisibility",
            new { FeatureRequestID = featureRequestId, IsPublicCandidate = isPublicCandidate },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<FeatureRequest?> UpdateAdminNotesAsync(int featureRequestId, string? adminNotes)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<FeatureRequest>(
            "dbo.FeatureRequest_UpdateAdminNotes",
            new { FeatureRequestID = featureRequestId, AdminNotes = adminNotes },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<FeatureRequestMessage?> AddMessageAsync(int featureRequestId, int? userId, bool isAdmin, string messageBody)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<FeatureRequestMessage>(
            "dbo.FeatureRequestMessage_Add",
            new { FeatureRequestID = featureRequestId, UserID = userId, IsAdmin = isAdmin, MessageBody = messageBody },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<FeatureRequestMessage>> ListMessagesAsync(int featureRequestId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<FeatureRequestMessage>(
            "dbo.FeatureRequestMessage_List",
            new { FeatureRequestID = featureRequestId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

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

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

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