using Dapper;
using SIGCM2.Application.Abstractions.Persistence;

namespace SIGCM2.Infrastructure.Persistence;

/// <summary>
/// PRD-002 — Real Dapper implementation of IProductQueryRepository against dbo.Product.
/// Replaces NullProductQueryRepository which was bound during PRD-001.
/// </summary>
public sealed class ProductQueryRepository : IProductQueryRepository
{
    private readonly SqlConnectionFactory _factory;

    public ProductQueryRepository(SqlConnectionFactory factory)
    {
        _factory = factory;
    }

    public async Task<bool> ExistsActiveByProductTypeAsync(int productTypeId, CancellationToken ct = default)
    {
        const string sql = """
            SELECT CASE
                WHEN EXISTS (
                    SELECT 1 FROM dbo.Product
                    WHERE ProductTypeId = @ProductTypeId
                      AND IsActive = 1
                ) THEN 1 ELSE 0
            END
            """;

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        var result = await connection.ExecuteScalarAsync<int>(sql, new { ProductTypeId = productTypeId });
        return result == 1;
    }

    public async Task<int> CountActiveByRubroAsync(int rubroId, CancellationToken ct = default)
    {
        const string sql = """
            SELECT COUNT(1) FROM dbo.Product WHERE RubroId = @RubroId AND IsActive = 1
            """;

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        return await connection.ExecuteScalarAsync<int>(sql, new { RubroId = rubroId });
    }
}
