PlotDirector/PlotLine/Data/StoryIntelligenceSourceRepository.cs

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

namespace PlotLine.Data;

public interface IStoryIntelligenceSourceRepository
{
    Task<IReadOnlyList<StoryIntelligenceSourceOption>> ListProjectsAsync();
    Task<IReadOnlyList<StoryIntelligenceSourceOption>> ListBooksAsync(int projectId);
    Task<IReadOnlyList<StoryIntelligenceSourceOption>> ListChaptersAsync(int bookId);
    Task<StoryIntelligenceExistingChapterSource?> GetChapterSourceAsync(int chapterId);
}

public sealed class StoryIntelligenceSourceRepository(ISqlConnectionFactory connectionFactory) : IStoryIntelligenceSourceRepository
{
    public async Task<IReadOnlyList<StoryIntelligenceSourceOption>> ListProjectsAsync()
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<StoryIntelligenceSourceOption>(
            "dbo.StoryIntelligenceSource_ProjectListAdmin",
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<IReadOnlyList<StoryIntelligenceSourceOption>> ListBooksAsync(int projectId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<StoryIntelligenceSourceOption>(
            "dbo.StoryIntelligenceSource_BookListAdmin",
            new { ProjectID = projectId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<IReadOnlyList<StoryIntelligenceSourceOption>> ListChaptersAsync(int bookId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<StoryIntelligenceSourceOption>(
            "dbo.StoryIntelligenceSource_ChapterListAdmin",
            new { BookID = bookId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<StoryIntelligenceExistingChapterSource?> GetChapterSourceAsync(int chapterId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<StoryIntelligenceExistingChapterSource>(
            "dbo.StoryIntelligenceSource_ChapterText_GetAdmin",
            new { ChapterID = chapterId },
            commandType: CommandType.StoredProcedure);
    }
}