using Dapper;
using FluentAssertions;
using Microsoft.Data.SqlClient;
using SIGCM2.Application.Common;
using SIGCM2.Domain.Entities;
using SIGCM2.Infrastructure.Persistence;
using SIGCM2.TestSupport;

namespace SIGCM2.Application.Tests.Products.Repository;

/// <summary>
/// PRD-002 — Integration tests for ProductRepository against SIGCM2_Test_App.
/// Uses shared SqlTestFixture via [Collection("Database")] — fixture manages Respawn + seeds.
/// Verifies full CRUD, paged listing, UQ constraint, and temporal history.
/// </summary>
[Collection("Database")]
public class ProductRepositoryTests : IAsyncLifetime
{
    private readonly SqlTestFixture _db;
    private ProductRepository _repository = null!;
    private int _defaultMedioId;
    private int _defaultProductTypeId;

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

    public async Task InitializeAsync()
    {
        await _db.ResetAndSeedAsync();

        var factory = new SqlConnectionFactory(TestConnectionStrings.AppTestDb);
        _repository = new ProductRepository(factory);

        // Insert Medio and ProductType for use across all tests
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        _defaultMedioId = await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.Medio (Codigo, Nombre, Tipo, Activo)
            OUTPUT INSERTED.Id
            VALUES ('PR', 'Prueba Medio', 1, 1)
            """);

        _defaultProductTypeId = await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.ProductType (Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle, AllowImages)
            OUTPUT INSERTED.Id
            VALUES ('Tipo Prueba', 0, 0, 0, 0, 0)
            """);
    }

    public Task DisposeAsync() => Task.CompletedTask;

    private Product AProduct(string nombre = "Clasificado Test") =>
        Product.ForCreation(
            nombre: nombre,
            medioId: _defaultMedioId,
            productTypeId: _defaultProductTypeId,
            rubroId: null,
            basePrice: 100.50m,
            priceDurationDays: null,
            timeProvider: TimeProvider.System);

    // ── AddAsync + GetByIdAsync roundtrip ─────────────────────────────────────

    [Fact]
    public async Task AddAsync_AndGetById_ReturnsAllFields()
    {
        var product = AProduct("Mi Producto");
        var id = await _repository.AddAsync(product);
        var result = await _repository.GetByIdAsync(id);

        result.Should().NotBeNull();
        result!.Id.Should().Be(id);
        result.Nombre.Should().Be("Mi Producto");
        result.MedioId.Should().Be(_defaultMedioId);
        result.ProductTypeId.Should().Be(_defaultProductTypeId);
        result.RubroId.Should().BeNull();
        result.BasePrice.Should().Be(100.50m);
        result.PriceDurationDays.Should().BeNull();
        result.IsActive.Should().BeTrue();
        result.FechaCreacion.Should().BeAfter(DateTime.MinValue);
        result.FechaModificacion.Should().BeNull();
    }

    // ── GetByIdAsync null for unknown ─────────────────────────────────────────

    [Fact]
    public async Task GetByIdAsync_UnknownId_ReturnsNull()
    {
        var result = await _repository.GetByIdAsync(999999);

        result.Should().BeNull();
    }

    // ── UpdateAsync ────────────────────────────────────────────────────────────

    [Fact]
    public async Task UpdateAsync_ChangesNombreAndBasePrice()
    {
        var id = await _repository.AddAsync(AProduct("Original"));
        var product = await _repository.GetByIdAsync(id);
        var updated = product!.WithUpdated("Renombrado", null, 200m, null, TimeProvider.System);

        await _repository.UpdateAsync(updated);
        var result = await _repository.GetByIdAsync(id);

        result!.Nombre.Should().Be("Renombrado");
        result.BasePrice.Should().Be(200m);
        result.FechaModificacion.Should().NotBeNull();
    }

    // ── WithDeactivated creates history row ────────────────────────────────────

    [Fact]
    public async Task UpdateAsync_Deactivate_CreatesHistoryRow()
    {
        var id = await _repository.AddAsync(AProduct("Para Desactivar"));
        var product = await _repository.GetByIdAsync(id);
        var deactivated = product!.WithDeactivated(TimeProvider.System);

        await _repository.UpdateAsync(deactivated);

        // Verify temporal history: ProductType_History should have at least 1 row
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();
        var historyCount = await conn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.Product_History WHERE Id = @Id", new { Id = id });
        historyCount.Should().BeGreaterThanOrEqualTo(1);
    }

    // ── GetPagedAsync ─────────────────────────────────────────────────────────

    [Fact]
    public async Task GetPagedAsync_DefaultQuery_ReturnsActiveProducts()
    {
        await _repository.AddAsync(AProduct("Producto A"));
        await _repository.AddAsync(AProduct("Producto B"));

        var result = await _repository.GetPagedAsync(new ProductsQuery(Page: 1, PageSize: 20, Activo: true));

        result.Items.Should().HaveCountGreaterThanOrEqualTo(2);
        result.Items.Should().AllSatisfy(p => p.IsActive.Should().BeTrue());
    }

    [Fact]
    public async Task GetPagedAsync_FilterByMedioId_ReturnsOnlyMatching()
    {
        await _repository.AddAsync(AProduct("Producto Filtrado"));

        var result = await _repository.GetPagedAsync(
            new ProductsQuery(Page: 1, PageSize: 20, Activo: null, MedioId: _defaultMedioId));

        result.Items.Should().AllSatisfy(p => p.MedioId.Should().Be(_defaultMedioId));
    }

    // ── ExistsByNombreAsync ────────────────────────────────────────────────────

    [Fact]
    public async Task ExistsByNombreAsync_ExistingActiveProduct_ReturnsTrue()
    {
        var nombre = "Nombre Unico Test";
        await _repository.AddAsync(AProduct(nombre));

        var result = await _repository.ExistsByNombreAsync(nombre, _defaultMedioId, _defaultProductTypeId);

        result.Should().BeTrue();
    }

    [Fact]
    public async Task ExistsByNombreAsync_ExcludeSelf_ReturnsFalse()
    {
        var nombre = "Nombre Self Excluido";
        var id = await _repository.AddAsync(AProduct(nombre));

        var result = await _repository.ExistsByNombreAsync(nombre, _defaultMedioId, _defaultProductTypeId, excludeId: id);

        result.Should().BeFalse();
    }

    [Fact]
    public async Task ExistsByNombreAsync_NonExisting_ReturnsFalse()
    {
        var result = await _repository.ExistsByNombreAsync("Nombre Que No Existe XYZ", _defaultMedioId, _defaultProductTypeId);

        result.Should().BeFalse();
    }

    // ── UQ index: deactivated allows reuse of name ────────────────────────────

    [Fact]
    public async Task ExistsByNombreAsync_DeactivatedProduct_ReturnsFalse_AllowsReuse()
    {
        var nombre = "Nombre Reutilizable";
        var id = await _repository.AddAsync(AProduct(nombre));
        var product = await _repository.GetByIdAsync(id);
        await _repository.UpdateAsync(product!.WithDeactivated(TimeProvider.System));

        // After deactivation, name should be available again
        var result = await _repository.ExistsByNombreAsync(nombre, _defaultMedioId, _defaultProductTypeId);

        result.Should().BeFalse();
    }
}
