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.
/// </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);

        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);

        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);
        var otherProductTypeId = productTypeId + 100;

        var result = await _repository.ExistsActiveByProductTypeAsync(otherProductTypeId);

        result.Should().BeFalse();
    }

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

    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)
    {
        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();
        await conn.ExecuteAsync("""
            INSERT INTO dbo.Product (Nombre, MedioId, ProductTypeId, BasePrice, IsActive, FechaCreacion)
            VALUES ('Producto Activo', @MedioId, @ProductTypeId, 100, 1, SYSUTCDATETIME())
            """, new { MedioId = medioId, ProductTypeId = productTypeId });
    }

    private async Task InsertInactiveProductAsync(int medioId, int productTypeId)
    {
        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();
        await conn.ExecuteAsync("""
            INSERT INTO dbo.Product (Nombre, MedioId, ProductTypeId, BasePrice, IsActive, FechaCreacion)
            VALUES ('Producto Inactivo', @MedioId, @ProductTypeId, 100, 0, SYSUTCDATETIME())
            """, new { MedioId = medioId, ProductTypeId = productTypeId });
    }
}
