Backend-Barracuda/SDG-Backend-Barracuda/Models/CardListModel.cs

using Dapper;
using Npgsql;

namespace SDG_Backend_Barracuda.Models;

public record CardList
{
    public int Id { get; init; }
    public IEnumerable<Card>? Cards { get; init; }
    public IEnumerable<CardList>? SubLists { get; init; }

    public CardList() { }

    public CardList(int id, IEnumerable<Card>? cards = null, IEnumerable<CardList>? subLists = null)
    {
        Id = id;
        Cards = cards;
        SubLists = subLists;
    }
}

public record CardListInformation(IEnumerable<int>? CardIds = null, IEnumerable<int>? SubListIds = null);

public interface ICardListModel : IDatabaseModel<CardList, CardListInformation>
{}

public class CardListModel(NpgsqlDataSource dataSource) : ICardListModel, IEndpointRouteHandler
{
    public static void MapEndpoints(IEndpointRouteBuilder router)
    {
        var listEndpoint = router.MapGroup("/cardlist");

        // Create
        listEndpoint.MapPost("/", async (CardListInformation input, ICardListModel model) =>
        {
            var newList = await model.Create(input);
            return Results.Created($"/cardlist/{newList.Id}", newList);
        });

        // Get all
        listEndpoint.MapGet("/", async (ICardListModel model) =>
            Results.Ok(await model.GetAll()));

        // Get one
        listEndpoint.MapGet("/{id}", async (int id, ICardListModel model) =>
            await model.GetById(id) is { } l ? Results.Ok(l) : Results.NotFound());

        // Update
        listEndpoint.MapPut("/{id}", async (int id, CardListInformation input, ICardListModel model) =>
            await model.Update(id, input) is { } l ? Results.Ok(l) : Results.NotFound());

        // Delete
        listEndpoint.MapDelete("/{id}", async (int id, ICardListModel model) =>
            await model.Delete(id) ? Results.NoContent() : Results.NotFound());
    }

    public async Task<IEnumerable<CardList>> GetAll()
    {
        await using var connection = await dataSource.OpenConnectionAsync();
        var sql = """SELECT * FROM "CardList";""";
        var cardListIds = await connection.QueryAsync<CardList>(sql);
        
        var result = new List<CardList>();
        foreach (var cardListId in cardListIds)
        {
            result.Add(await GetById(cardListId.Id) ?? cardListId);
        }
        return result;
    }

    public async Task<CardList?> GetById(int id)
    {
        // Check if it exists
        await using var connection = await dataSource.OpenConnectionAsync();
        var listSql = """SELECT * FROM "CardList" WHERE "Id" = @Id;""";
        var cardListId = await connection.QueryFirstOrDefaultAsync<CardList>(listSql, new { Id = id });
        if (cardListId == null) return null;

        // Get all associated cards
        var cardsSql = """
            SELECT c."Id", c."Value" 
            FROM "Card" c
            JOIN "CardListCard" clc ON c."Id" = clc."CardId"
            WHERE clc."CardListId" = @Id;
            """;
        var cards = await connection.QueryAsync<Card>(cardsSql, new { Id = id });

        // Get all child CardLists
        var subListsSql = """
            SELECT sl."Id"
            FROM "CardList" sl
            JOIN "CardListSubList" clsl ON sl."Id" = clsl."SubCardListId"
            WHERE clsl."CardListId" = @Id;
            """;
        var subLists = await connection.QueryAsync<CardList>(subListsSql, new { Id = id });
        
        return cardListId with { Cards = cards, SubLists = subLists };
    }

    public async Task<CardList> CreateEmpty()
    {
        await using var connection = await dataSource.OpenConnectionAsync();
        var sql = """INSERT INTO "CardList" DEFAULT VALUES RETURNING "Id";""";
        return await connection.QuerySingleAsync<CardList>(sql);
    }

    public async Task<CardList> Create(CardListInformation input)
    {
        await using var connection = await dataSource.OpenConnectionAsync();
        var newList = await CreateEmpty();

        if (input.CardIds != null)
        {
            foreach (var cardId in input.CardIds)
            {
                await connection.ExecuteAsync(
                    """INSERT INTO "CardListCard" ("CardListId", "CardId") VALUES (@ListId, @CardId)""",
                    new { ListId = newList.Id, CardId = cardId });
            }
        }

        if (input.SubListIds != null)
        {
            foreach (var subListId in input.SubListIds)
            {
                await connection.ExecuteAsync(
                    """INSERT INTO "CardListSubList" ("CardListId", "SubCardListId") VALUES (@ListId, @SubListId)""",
                    new { ListId = newList.Id, SubListId = subListId });
            }
        }

        return (await GetById(newList.Id))!;
    }

    public async Task<CardList?> Update(int id, CardListInformation input)
    {
        await using var connection = await dataSource.OpenConnectionAsync();
        
        // Verify list exists
        var exists = await connection.ExecuteScalarAsync<bool>("""SELECT EXISTS(SELECT 1 FROM "CardList" WHERE "Id" = @Id)""", new { Id = id });
        if (!exists) return null;

        // Sync Cards - Maybe think through a better way to do this that isn't "wipe it, then start over"
        await connection.ExecuteAsync("""DELETE FROM "CardListCard" WHERE "CardListId" = @Id""", new { Id = id });
        if (input.CardIds != null)
        {
            foreach (var cardId in input.CardIds)
            {
                await connection.ExecuteAsync(
                    """INSERT INTO "CardListCard" ("CardListId", "CardId") VALUES (@Id, @CardId)""",
                    new { Id = id, CardId = cardId });
            }
        }

        // Sync SubLists - Maybe think through a better way to do this that isn't "wipe it, then start over"
        await connection.ExecuteAsync("""DELETE FROM "CardListSubList" WHERE "CardListId" = @Id""", new { Id = id });
        if (input.SubListIds != null)
        {
            foreach (var subListId in input.SubListIds)
            {
                await connection.ExecuteAsync(
                    """INSERT INTO "CardListSubList" ("CardListId", "SubCardListId") VALUES (@Id, @SubListId)""",
                    new { Id = id, SubListId = subListId });
            }
        }

        return await GetById(id);
    }

    public async Task<bool> Delete(int id)
    {
        await using var connection = await dataSource.OpenConnectionAsync();
        var sql = """DELETE FROM "CardList" WHERE "Id" = @Id;""";
        var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
        return rowsAffected > 0;
    }
}