taxbaik/TaxBaik.Infrastructure/Repositories/AnnouncementRepository.cs

namespace TaxBaik.Infrastructure.Repositories;

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

public class AnnouncementRepository(IDbConnectionFactory connectionFactory)
    : BaseRepository(connectionFactory), IAnnouncementRepository
{
    private const string SelectColumns =
        "id, title, content, display_type, is_active, starts_at, ends_at, sort_order, created_at, updated_at";

    public async Task<IEnumerable<Announcement>> GetActiveAsync(CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryAsync<Announcement>(
            $@"SELECT {SelectColumns}
               FROM announcements
               WHERE is_active = TRUE
                 AND (starts_at IS NULL OR starts_at <= NOW())
                 AND (ends_at   IS NULL OR ends_at   >= NOW())
               ORDER BY sort_order DESC, created_at DESC");
    }

    public async Task<IEnumerable<Announcement>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryAsync<Announcement>(
            $"SELECT {SelectColumns} FROM announcements ORDER BY sort_order DESC, created_at DESC");
    }

    public async Task<Announcement?> GetByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryFirstOrDefaultAsync<Announcement>(
            $"SELECT {SelectColumns} FROM announcements WHERE id = @Id",
            new { Id = id });
    }

    public async Task<int> CreateAsync(Announcement announcement, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        return await conn.QueryFirstAsync<int>(
            @"INSERT INTO announcements
                (title, content, display_type, is_active, starts_at, ends_at, sort_order, created_at, updated_at)
              VALUES
                (@Title, @Content, @DisplayType, @IsActive, @StartsAt, @EndsAt, @SortOrder, NOW(), NOW())
              RETURNING id",
            announcement);
    }

    public async Task UpdateAsync(Announcement announcement, CancellationToken cancellationToken = default)
    {
        using var conn = Conn();
        await conn.ExecuteAsync(
            @"UPDATE announcements
              SET title        = @Title,
                  content      = @Content,
                  display_type = @DisplayType,
                  is_active    = @IsActive,
                  starts_at    = @StartsAt,
                  ends_at      = @EndsAt,
                  sort_order   = @SortOrder,
                  updated_at   = NOW()
              WHERE id = @Id",
            announcement);
    }

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