using Dapper;
using FluentAssertions;
using SIGCM2.Infrastructure.Persistence;
using SIGCM2.TestSupport;

namespace SIGCM2.Application.Tests.Products.Repository;

/// <summary>
/// PRD-002 — Integration tests for ProductQueryRepository against SIGCM2_Test_App.
/// These tests verify the real Dapper implementation replaces NullProductQueryRepository.
/// Issue #41: CountActiveByRubroAsync tests added here (same class, same DB, same fixture).
/// </summary>
[Collection("Database")]
public class ProductQueryRepositoryTests : IAsyncLifetime
{
    private readonly SqlTestFixture _db;
    private ProductQueryRepository _repository = null!;

    public ProductQueryRepositoryTests(SqlTestFixture db)
    {
        _db = db;
    }

    public async Task InitializeAsync()
    {
        await _db.ResetAndSeedAsync();
        var factory = new SqlConnectionFactory(TestConnectionStrings.AppTestDb);
        _repository = new ProductQueryRepository(factory);
    }

    public Task DisposeAsync() => Task.CompletedTask;

    // ── ExistsActiveByProductTypeAsync ───────────────────────────────────────

    [Fact]
    public async Task ExistsActiveByProductTypeAsync_NoProducts_ReturnsFalse()
    {
        var result = await _repository.ExistsActiveByProductTypeAsync(productTypeId: 999);

        result.Should().BeFalse();
    }

    [Fact]
    public async Task ExistsActiveByProductTypeAsync_WithActiveProduct_ReturnsTrue()
    {
        // Arrange: insert a ProductType and an active Product referencing it
        var (medioId, productTypeId) = await InsertMedioAndProductTypeAsync();
        await InsertActiveProductAsync(medioId, productTypeId, rubroId: null);

        var result = await _repository.ExistsActiveByProductTypeAsync(productTypeId);

        result.Should().BeTrue();
    }

    [Fact]
    public async Task ExistsActiveByProductTypeAsync_WithOnlyInactiveProduct_ReturnsFalse()
    {
        // Arrange: insert an inactive product
        var (medioId, productTypeId) = await InsertMedioAndProductTypeAsync();
        await InsertInactiveProductAsync(medioId, productTypeId, rubroId: null);

        var result = await _repository.ExistsActiveByProductTypeAsync(productTypeId);

        result.Should().BeFalse();
    }

    [Fact]
    public async Task ExistsActiveByProductTypeAsync_DifferentProductType_ReturnsFalse()
    {
        // Arrange: insert active product for productTypeId=A, query for productTypeId=B
        var (medioId, productTypeId) = await InsertMedioAndProductTypeAsync();
        await InsertActiveProductAsync(medioId, productTypeId, rubroId: null);
        var otherProductTypeId = productTypeId + 100;

        var result = await _repository.ExistsActiveByProductTypeAsync(otherProductTypeId);

        result.Should().BeFalse();
    }

    // ── CountActiveByRubroAsync (issue #41) ──────────────────────────────────

    [Fact]
    public async Task CountActiveByRubroAsync_NoProducts_ReturnsZero()
    {
        var result = await _repository.CountActiveByRubroAsync(rubroId: 99999);

        result.Should().Be(0);
    }

    [Fact]
    public async Task CountActiveByRubroAsync_Returns_CorrectCount()
    {
        // Arrange: insert a Rubro and products in various states
        var rubroId = await InsertRubroAsync("Rubro Clasificados");
        var (medioId, productTypeId) = await InsertMedioAndProductTypeAsync();

        // 2 active products referencing the rubro
        await InsertActiveProductAsync(medioId, productTypeId, rubroId: rubroId);
        await InsertActiveProductAsync(medioId, productTypeId, rubroId: rubroId);

        // 1 inactive product referencing the same rubro (should NOT count)
        await InsertInactiveProductAsync(medioId, productTypeId, rubroId: rubroId);

        // 1 active product with a DIFFERENT rubroId (should NOT count)
        var otherRubroId = await InsertRubroAsync("Otro Rubro");
        await InsertActiveProductAsync(medioId, productTypeId, rubroId: otherRubroId);

        var result = await _repository.CountActiveByRubroAsync(rubroId);

        result.Should().Be(2);
    }

    [Fact]
    public async Task CountActiveByRubroAsync_WithOnlyInactiveProducts_ReturnsZero()
    {
        var rubroId = await InsertRubroAsync("Rubro Solo Inactivos");
        var (medioId, productTypeId) = await InsertMedioAndProductTypeAsync();

        await InsertInactiveProductAsync(medioId, productTypeId, rubroId: rubroId);

        var result = await _repository.CountActiveByRubroAsync(rubroId);

        result.Should().Be(0);
    }

    // ── Helpers ───────────────────────────────────────────────────────────────

    private async Task<int> InsertRubroAsync(string nombre)
    {
        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();
        return await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.Rubro (Nombre, ParentId, Orden, Activo, TarifarioBaseId, FechaCreacion)
            OUTPUT INSERTED.Id
            VALUES (@Nombre, NULL, 0, 1, NULL, SYSUTCDATETIME())
            """, new { Nombre = nombre });
    }

    private async Task<(int MedioId, int ProductTypeId)> InsertMedioAndProductTypeAsync()
    {
        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var medioId = await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.Medio (Codigo, Nombre, Tipo, Activo)
            OUTPUT INSERTED.Id
            VALUES ('TM', 'Test Medio', 1, 1)
            """);

        var productTypeId = await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.ProductType (Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle, AllowImages)
            OUTPUT INSERTED.Id
            VALUES ('Test Type', 0, 0, 0, 0, 0)
            """);

        return (medioId, productTypeId);
    }

    private async Task InsertActiveProductAsync(int medioId, int productTypeId, int? rubroId)
    {
        var nombre = $"ProdActivo-{Guid.NewGuid():N}";
        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();
        await conn.ExecuteAsync("""
            INSERT INTO dbo.Product (Nombre, MedioId, ProductTypeId, RubroId, BasePrice, IsActive, FechaCreacion)
            VALUES (@Nombre, @MedioId, @ProductTypeId, @RubroId, 100, 1, SYSUTCDATETIME())
            """, new { Nombre = nombre, MedioId = medioId, ProductTypeId = productTypeId, RubroId = rubroId });
    }

    private async Task InsertInactiveProductAsync(int medioId, int productTypeId, int? rubroId)
    {
        var nombre = $"ProdInactivo-{Guid.NewGuid():N}";
        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();
        await conn.ExecuteAsync("""
            INSERT INTO dbo.Product (Nombre, MedioId, ProductTypeId, RubroId, BasePrice, IsActive, FechaCreacion)
            VALUES (@Nombre, @MedioId, @ProductTypeId, @RubroId, 100, 0, SYSUTCDATETIME())
            """, new { Nombre = nombre, MedioId = medioId, ProductTypeId = productTypeId, RubroId = rubroId });
    }
}
