taxbaik/TaxBaik.Infrastructure/Repositories/BlogPostRepository.cs

namespace TaxBaik.Infrastructure.Repositories;

using Dapper;
using TaxBaik.Domain.Entities;
using TaxBaik.Domain.Interfaces;

public class BlogPostRepository(IDbConnectionFactory connectionFactory) : BaseRepository(connectionFactory), IBlogPostRepository
{
    public async Task<BlogPost?> GetByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryFirstOrDefaultAsync<BlogPost>(
            @"SELECT bp.id, bp.title, bp.content, bp.slug, bp.category_id, bp.tags, bp.author_id,
                     bp.published_at, bp.view_count, bp.seo_title, bp.seo_description, bp.thumbnail_url,
                     bp.is_published, bp.created_at, bp.updated_at, c.name AS category_name
              FROM blog_posts bp
              LEFT JOIN categories c ON bp.category_id = c.id
              WHERE bp.id = @Id",
            new { Id = id });
    }

    public async Task<BlogPost?> GetBySlugAsync(string slug, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryFirstOrDefaultAsync<BlogPost>(
            @"SELECT bp.id, bp.title, bp.content, bp.slug, bp.category_id, bp.tags, bp.author_id,
                     bp.published_at, bp.view_count, bp.seo_title, bp.seo_description, bp.thumbnail_url,
                     bp.is_published, bp.created_at, bp.updated_at, c.name AS category_name
              FROM blog_posts bp
              LEFT JOIN categories c ON bp.category_id = c.id
              WHERE bp.slug = @Slug AND bp.is_published = TRUE",
            new { Slug = slug });
    }

    public async Task<(IEnumerable<BlogPost> Items, int Total)> GetPublishedPagedAsync(
        int page, int pageSize, int? categoryId = null, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        var offset = (page - 1) * pageSize;

        using var reader = await conn.QueryMultipleAsync(
            @"SELECT bp.id, bp.title, bp.content, bp.slug, bp.category_id, bp.tags, bp.author_id,
                     bp.published_at, bp.view_count, bp.seo_title, bp.seo_description, bp.thumbnail_url,
                     bp.is_published, bp.created_at, bp.updated_at, c.name AS category_name
              FROM blog_posts bp
              LEFT JOIN categories c ON bp.category_id = c.id
              WHERE bp.is_published = TRUE AND (@CategoryId::int IS NULL OR bp.category_id = @CategoryId)
              ORDER BY bp.published_at DESC
              LIMIT @PageSize OFFSET @Offset;

              SELECT COUNT(*) FROM blog_posts
              WHERE is_published = TRUE AND (@CategoryId::int IS NULL OR category_id = @CategoryId);",
            new { CategoryId = categoryId, PageSize = pageSize, Offset = offset });

        var items = (await reader.ReadAsync<BlogPost>()).ToList();
        var total = await reader.ReadFirstAsync<int>();

        return (items, total);
    }

    public async Task<IEnumerable<BlogPost>> GetByCategorySlugAsync(string categorySlug, int limit, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryAsync<BlogPost>(
            @"SELECT bp.id, bp.title, bp.slug, bp.category_id, bp.tags,
                     bp.published_at, bp.view_count, bp.seo_description, bp.thumbnail_url,
                     bp.is_published, bp.created_at, bp.updated_at, c.name AS category_name
              FROM blog_posts bp
              LEFT JOIN categories c ON bp.category_id = c.id
              WHERE bp.is_published = TRUE AND c.slug = @CategorySlug
              ORDER BY bp.published_at DESC
              LIMIT @Limit",
            new { CategorySlug = categorySlug, Limit = limit });
    }

    public async Task<IEnumerable<BlogPost>> GetAllForAdminAsync(CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryAsync<BlogPost>(
            @"SELECT bp.id, bp.title, bp.content, bp.slug, bp.category_id, bp.tags, bp.author_id,
                     bp.published_at, bp.view_count, bp.seo_title, bp.seo_description, bp.thumbnail_url,
                     bp.is_published, bp.created_at, bp.updated_at, c.name AS category_name
              FROM blog_posts bp
              LEFT JOIN categories c ON bp.category_id = c.id
              ORDER BY bp.created_at DESC");
    }

    public async Task<(IEnumerable<BlogPost> Items, int Total)> GetAdminPagedAsync(
        int page, int pageSize, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        var offset = (page - 1) * pageSize;

        using var reader = await conn.QueryMultipleAsync(
            @"SELECT bp.id, bp.title, bp.content, bp.slug, bp.category_id, bp.tags, bp.author_id,
                     bp.published_at, bp.view_count, bp.seo_title, bp.seo_description, bp.thumbnail_url,
                     bp.is_published, bp.created_at, bp.updated_at, c.name AS category_name
              FROM blog_posts bp
              LEFT JOIN categories c ON bp.category_id = c.id
              ORDER BY bp.created_at DESC
              LIMIT @PageSize OFFSET @Offset;

              SELECT COUNT(*) FROM blog_posts;",
            new { PageSize = pageSize, Offset = offset });

        var items = (await reader.ReadAsync<BlogPost>()).ToList();
        var total = await reader.ReadFirstAsync<int>();

        return (items, total);
    }

    public async Task<int> CreateAsync(BlogPost post, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryFirstAsync<int>(
            @"INSERT INTO blog_posts (title, content, slug, category_id, tags, author_id, published_at,
                                      seo_title, seo_description, thumbnail_url, is_published, created_at, updated_at)
              VALUES (@Title, @Content, @Slug, @CategoryId, @Tags, @AuthorId, @PublishedAt,
                      @SeoTitle, @SeoDescription, @ThumbnailUrl, @IsPublished, NOW(), NOW())
              RETURNING id",
            post);
    }

    public async Task UpdateAsync(BlogPost post, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        await conn.ExecuteAsync(
            @"UPDATE blog_posts
              SET title = @Title, content = @Content, slug = @Slug, category_id = @CategoryId,
                  tags = @Tags, author_id = @AuthorId, published_at = @PublishedAt,
                  seo_title = @SeoTitle, seo_description = @SeoDescription,
                  thumbnail_url = @ThumbnailUrl, is_published = @IsPublished, updated_at = NOW()
              WHERE id = @Id",
            post);
    }

    public async Task DeleteAsync(int id, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        await conn.ExecuteAsync("DELETE FROM blog_posts WHERE id = @Id", new { Id = id });
    }

    public async Task IncrementViewCountAsync(int id, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        await conn.ExecuteAsync("UPDATE blog_posts SET view_count = view_count + 1 WHERE id = @Id", new { Id = id });
    }
}