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

namespace SIGCM2.Application.Tests.Products.Repository;

/// <summary>
/// PRD-003 — Integration tests for dbo.ProductPrices + dbo.usp_AddProductPrice.
/// Batch 1: Direct SP invocation (RED until V019 applied).
/// Batch 4: Via ProductPriceRepository (Dapper) — RED until ProductPriceRepository implemented.
///
/// Spec coverage:
///   §REQ-1.1 — Happy path: first price, no active to close
///   §REQ-1.1 — Happy path: closes previous active on new price
///   §REQ-1.2 — Concurrency: only one winner, loser gets 409 or unique violation
///   §REQ-2.2 — ForwardOnly: retroactive date → SQL 50409
///   §REQ-2.3 — ForwardOnly: equal PriceValidFrom → SQL 50409
///   §REQ-3.3 — Inactive product → SQL 50404
///   SYSTEM_VERSIONING: UPDATE to close active produces history row
///   Filtered unique index: duplicate active → SQL error 2601/2627
/// </summary>
[Collection("Database")]
public class ProductPriceRepositoryIntegrationTests : IAsyncLifetime
{
    private readonly SqlTestFixture _db;
    private int _defaultProductId;
    private int _defaultMedioId;
    private int _defaultProductTypeId;

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

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

        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        // Ensure V019 schema is present (table + SP + indexes).
        await EnsureV019SchemaAsync(conn);

        // Create a Medio, ProductType, and an active Product for use in all tests.
        _defaultMedioId = await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.Medio (Codigo, Nombre, Tipo, Activo)
            OUTPUT INSERTED.Id
            VALUES ('PP', 'Medio ProductPrices', 1, 1)
            """);

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

        _defaultProductId = await conn.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.Product (Nombre, MedioId, ProductTypeId, RubroId, BasePrice, PriceDurationDays, IsActive)
            OUTPUT INSERTED.Id
            VALUES ('Producto Precios Test', @MedioId, @ProductTypeId, NULL, 100.00, NULL, 1)
            """, new { MedioId = _defaultMedioId, ProductTypeId = _defaultProductTypeId });
    }

    public Task DisposeAsync() => Task.CompletedTask;

    // ── Helper: invoke SP directly ────────────────────────────────────────────

    private static async Task<(long NewId, long? ClosedId)> ExecAddPriceSpAsync(
        SqlConnection conn,
        int productId,
        decimal price,
        DateOnly priceValidFrom)
    {
        var p = new DynamicParameters();
        p.Add("@ProductId",      productId);
        p.Add("@Price",          price);
        p.Add("@PriceValidFrom", priceValidFrom.ToDateTime(TimeOnly.MinValue), System.Data.DbType.Date);
        p.Add("@NewId",          dbType: System.Data.DbType.Int64, direction: System.Data.ParameterDirection.Output);
        p.Add("@ClosedId",       dbType: System.Data.DbType.Int64, direction: System.Data.ParameterDirection.Output);

        await conn.ExecuteAsync("dbo.usp_AddProductPrice", p,
            commandType: System.Data.CommandType.StoredProcedure);

        var newId    = p.Get<long>("@NewId");
        var closedId = p.Get<long?>("@ClosedId");
        return (newId, closedId);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-1.1 — Primer precio: ClosedId es null
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task FirstPrice_NoActiveToClose_ClosedIdIsNull()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var pvf = new DateOnly(2026, 4, 19);
        var (newId, closedId) = await ExecAddPriceSpAsync(conn, _defaultProductId, 150.00m, pvf);

        newId.Should().BeGreaterThan(0);
        closedId.Should().BeNull();

        var row = await conn.QuerySingleOrDefaultAsync<dynamic>(
            "SELECT Id, ProductId, Price, PriceValidFrom, PriceValidTo FROM dbo.ProductPrices WHERE Id = @Id",
            new { Id = newId });

        ((object?)row).Should().NotBeNull();
        ((int)row!.ProductId).Should().Be(_defaultProductId);
        ((decimal)row.Price).Should().Be(150.00m);
        ((DateTime)row.PriceValidFrom).Should().Be(new DateTime(2026, 4, 19));
        ((object?)row.PriceValidTo).Should().BeNull();
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-1.1 — Happy path: cierra activo, inserta nuevo
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task HappyPath_ClosesActivePriceAndInsertsNew()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        // Primera inserción
        var (firstId, _) = await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        // Segunda inserción (forward)
        var pvf2 = new DateOnly(2026, 4, 20);
        var (newId, closedId) = await ExecAddPriceSpAsync(conn, _defaultProductId, 200.00m, pvf2);

        // El nuevo es activo (PVT = NULL)
        var newRow = await conn.QuerySingleAsync<dynamic>(
            "SELECT PriceValidTo FROM dbo.ProductPrices WHERE Id = @Id", new { Id = newId });
        ((object?)newRow.PriceValidTo).Should().BeNull();

        // El anterior está cerrado con PVT = pvf2 - 1 día
        closedId.Should().Be(firstId);
        var closedRow = await conn.QuerySingleAsync<dynamic>(
            "SELECT PriceValidTo FROM dbo.ProductPrices WHERE Id = @Id", new { Id = firstId });
        ((DateTime)closedRow.PriceValidTo).Should().Be(new DateTime(2026, 4, 19));
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-2.2 — ForwardOnly: fecha retroactiva → THROW 50409
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task ForwardOnly_RetroactiveDate_ThrowsSql50409()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        // Precio activo desde 2026-04-01
        await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        // Intento retroactivo
        var act = async () => await ExecAddPriceSpAsync(conn, _defaultProductId, 90.00m, new DateOnly(2026, 3, 15));

        await act.Should().ThrowAsync<SqlException>()
            .Where(ex => ex.Number == 50409);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-2.3 — ForwardOnly: misma fecha → THROW 50409
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task ForwardOnly_EqualPriceValidFrom_ThrowsSql50409()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var pvf = new DateOnly(2026, 4, 19);
        await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, pvf);

        var act = async () => await ExecAddPriceSpAsync(conn, _defaultProductId, 120.00m, pvf);

        await act.Should().ThrowAsync<SqlException>()
            .Where(ex => ex.Number == 50409);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-3.3 — Producto inactivo → THROW 50404
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task InactiveProduct_ThrowsSql50404()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        // Desactivar el producto
        await conn.ExecuteAsync(
            "UPDATE dbo.Product SET IsActive = 0 WHERE Id = @Id",
            new { Id = _defaultProductId });

        var act = async () => await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 19));

        await act.Should().ThrowAsync<SqlException>()
            .Where(ex => ex.Number == 50404);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-3.3 — Producto inexistente → THROW 50404
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task NonExistentProduct_ThrowsSql50404()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var act = async () => await ExecAddPriceSpAsync(conn, 999999, 100.00m, new DateOnly(2026, 4, 19));

        await act.Should().ThrowAsync<SqlException>()
            .Where(ex => ex.Number == 50404);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // §REQ-1.2 — Concurrencia: solo un ganador, el perdedor lanza excepción
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task Concurrency_OnlyOneSucceeds_OneThrows()
    {
        // Ambas conexiones intentan agregar precio al mismo producto simultáneamente.
        // Con SERIALIZABLE + UPDLOCK, exactamente una debe tener éxito.
        var pvf = new DateOnly(2026, 5, 1);

        var task1 = Task.Run(async () =>
        {
            await using var conn1 = new SqlConnection(TestConnectionStrings.AppTestDb);
            await conn1.OpenAsync();
            return await ExecAddPriceSpAsync(conn1, _defaultProductId, 111.00m, pvf);
        });

        var task2 = Task.Run(async () =>
        {
            await using var conn2 = new SqlConnection(TestConnectionStrings.AppTestDb);
            await conn2.OpenAsync();
            return await ExecAddPriceSpAsync(conn2, _defaultProductId, 222.00m, pvf);
        });

        // Exactamente una debe lanzar (50409, 2601, 2627, o deadlock 1205)
        Exception? caughtEx = null;
        try
        {
            await Task.WhenAll(task1, task2);
        }
        catch (SqlException ex)
        {
            caughtEx = ex;
        }
        catch (AggregateException aex) when (aex.InnerExceptions.All(e => e is SqlException))
        {
            caughtEx = aex;
        }

        caughtEx.Should().NotBeNull("one concurrent insert must fail");

        // Exactamente 1 activo debe existir (PriceValidTo IS NULL)
        await using var verifyConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await verifyConn.OpenAsync();
        var activeCount = await verifyConn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.ProductPrices WHERE ProductId = @ProductId AND PriceValidTo IS NULL",
            new { ProductId = _defaultProductId });
        activeCount.Should().Be(1, "only one active price must survive concurrency");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // SYSTEM_VERSIONING: UPDATE del activo produce row en history
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task SystemVersioning_UpdateOnClose_ProducesHistoryRow()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var (firstId, _) = await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        // Cierra el activo con una segunda inserción
        await ExecAddPriceSpAsync(conn, _defaultProductId, 200.00m, new DateOnly(2026, 4, 20));

        // dbo.ProductPrices_History debe tener al menos 1 row para el ID cerrado
        var histCount = await conn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.ProductPrices_History WHERE Id = @Id",
            new { Id = firstId });

        histCount.Should().BeGreaterThanOrEqualTo(1,
            "SYSTEM_VERSIONING must produce a history row when the active price is closed");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Filtered unique index: two simultaneous direct INSERTs → 2601/2627
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task FilteredUniqueIndex_DirectDuplicateActiveInsert_ThrowsUniqueViolation()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        // Insert an active price directly (bypassing SP to force a duplicate)
        const string insertSql = """
            INSERT INTO dbo.ProductPrices (ProductId, Price, PriceValidFrom, PriceValidTo)
            VALUES (@ProductId, @Price, @PriceValidFrom, NULL)
            """;

        await conn.ExecuteAsync(insertSql, new
        {
            ProductId = _defaultProductId,
            Price = 100m,
            PriceValidFrom = new DateTime(2026, 4, 1)
        });

        var act = async () => await conn.ExecuteAsync(insertSql, new
        {
            ProductId = _defaultProductId,
            Price = 150m,
            PriceValidFrom = new DateTime(2026, 4, 10)
        });

        await act.Should().ThrowAsync<SqlException>()
            .Where(ex => ex.Number == 2601 || ex.Number == 2627,
                "filtered unique index UX_ProductPrices_Active must prevent two NULL PriceValidTo for same ProductId");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Batch 4 — Via ProductPriceRepository (Dapper wrapper)
    // ─────────────────────────────────────────────────────────────────────────

    // §REQ-4.1 / §P.1 — GetByProductIdAsync (paginated) orders descending by PriceValidFrom
    [Fact]
    public async Task GetByProductIdAsync_MultipleRows_OrdersDescendingByPriceValidFrom()
    {
        // Seed 3 prices via SP
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 200.00m, new DateOnly(2026, 3, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 300.00m, new DateOnly(2026, 5, 1));

        var repo = BuildRepository();
        var result = await repo.GetByProductIdAsync(_defaultProductId, page: 1, pageSize: 20);

        result.Total.Should().Be(3);
        result.Items.Should().HaveCount(3);
        result.Items[0].PriceValidFrom.Should().Be(new DateOnly(2026, 5, 1), "most recent first");
        result.Items[1].PriceValidFrom.Should().Be(new DateOnly(2026, 3, 1));
        result.Items[2].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 1));
    }

    // §REQ-4.3 / §P.6 — GetByProductIdAsync returns empty PagedResult when no history
    [Fact]
    public async Task GetByProductIdAsync_NoHistory_ReturnsEmptyPagedResult()
    {
        var repo = BuildRepository();
        var result = await repo.GetByProductIdAsync(_defaultProductId, page: 1, pageSize: 20);

        result.Items.Should().BeEmpty("product exists but has no price history yet");
        result.Total.Should().Be(0);
        result.Page.Should().Be(1);
        result.PageSize.Should().Be(20);
    }

    // §P.2 — OFFSET/FETCH: page 2 with pageSize=2 returns correct items
    [Fact]
    public async Task GetByProductIdAsync_Page2_ReturnsCorrectOffset()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Seed 5 prices: PVF Jan-1 through Jan-5 (DESC order: Jan5, Jan4, Jan3, Jan2, Jan1)
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 200.00m, new DateOnly(2026, 1, 2));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 300.00m, new DateOnly(2026, 1, 3));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 400.00m, new DateOnly(2026, 1, 4));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 500.00m, new DateOnly(2026, 1, 5));

        var repo = BuildRepository();
        // page=2, pageSize=2 → skip 2 → items at rank 3 and 4 (Jan3, Jan2)
        var result = await repo.GetByProductIdAsync(_defaultProductId, page: 2, pageSize: 2);

        result.Total.Should().Be(5);
        result.Page.Should().Be(2);
        result.PageSize.Should().Be(2);
        result.Items.Should().HaveCount(2);
        result.Items[0].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 3), "rank 3 in DESC order");
        result.Items[1].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 2), "rank 4 in DESC order");
    }

    // §P.3 — OFFSET/FETCH: page beyond total → empty items, correct total
    [Fact]
    public async Task GetByProductIdAsync_PageBeyondTotal_ReturnsEmptyItemsWithCorrectTotal()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 200.00m, new DateOnly(2026, 2, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 300.00m, new DateOnly(2026, 3, 1));

        var repo = BuildRepository();
        var result = await repo.GetByProductIdAsync(_defaultProductId, page: 100, pageSize: 10);

        result.Total.Should().Be(3, "COUNT always reflects actual total regardless of page");
        result.Items.Should().BeEmpty("offset far beyond available data");
        result.Page.Should().Be(100);
    }

    // §P.2 no-overlap — two different pages have no overlapping items
    [Fact]
    public async Task GetByProductIdAsync_TwoPages_HaveNoOverlap()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        for (var i = 1; i <= 6; i++)
            await ExecAddPriceSpAsync(seedConn, _defaultProductId, i * 100m, new DateOnly(2026, 1, i));

        var repo = BuildRepository();
        var page1 = await repo.GetByProductIdAsync(_defaultProductId, page: 1, pageSize: 3);
        var page2 = await repo.GetByProductIdAsync(_defaultProductId, page: 2, pageSize: 3);

        page1.Items.Select(p => p.PriceValidFrom)
            .Intersect(page2.Items.Select(p => p.PriceValidFrom))
            .Should().BeEmpty("pages must not overlap");

        page1.Total.Should().Be(6);
        page2.Total.Should().Be(6);
    }

    // §B3.2 — 30 prices: page 1 and page 2 (pageSize=10) are fully disjoint,
    // union = 20 distinct items, each page is ordered DESC by PriceValidFrom.
    [Fact]
    public async Task GetByProductIdAsync_ThirtyPrices_TwoPages_AreDisjointAndOrderedDesc()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Insert 30 prices directly (bypass SP to avoid forward-only complexity).
        // All rows get an explicit PriceValidTo so no unique-index violation.
        // PVF: 2026-01-01 to 2026-01-30 (ascending), all closed (PVT = PVF + 1 day).
        // Except row 30 which has PVT = NULL (the single active row).
        for (var i = 1; i <= 30; i++)
        {
            DateTime? pvt = i < 30
                ? (DateTime?)new DateTime(2026, 1, i + 1)
                : null;

            await seedConn.ExecuteAsync("""
                INSERT INTO dbo.ProductPrices (ProductId, Price, PriceValidFrom, PriceValidTo, FechaCreacion)
                VALUES (@ProductId, @Price, @PriceValidFrom, @PriceValidTo, SYSUTCDATETIME())
                """,
                new
                {
                    ProductId   = _defaultProductId,
                    Price       = (decimal)(i * 10),
                    PriceValidFrom = new DateTime(2026, 1, i),
                    PriceValidTo   = pvt
                });
        }

        var repo = BuildRepository();

        var page1 = await repo.GetByProductIdAsync(_defaultProductId, page: 1, pageSize: 10);
        var page2 = await repo.GetByProductIdAsync(_defaultProductId, page: 2, pageSize: 10);

        // Both pages must reflect the full dataset
        page1.Total.Should().Be(30);
        page2.Total.Should().Be(30);

        // Each page must have exactly 10 items
        page1.Items.Should().HaveCount(10);
        page2.Items.Should().HaveCount(10);

        // Page meta
        page1.Page.Should().Be(1);
        page1.PageSize.Should().Be(10);
        page2.Page.Should().Be(2);
        page2.PageSize.Should().Be(10);

        // The two ID sets must be fully disjoint
        var ids1 = page1.Items.Select(p => p.PriceValidFrom).ToHashSet();
        var ids2 = page2.Items.Select(p => p.PriceValidFrom).ToHashSet();
        ids1.Intersect(ids2).Should().BeEmpty("pages must not share any items");

        // Union covers exactly 20 distinct items
        ids1.Union(ids2).Should().HaveCount(20, "union of two pages of 10 must yield 20 distinct items");

        // Page 1 must be ordered DESC — first item is PVF Jan 30 (most recent)
        page1.Items[0].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 30),
            "page 1 rank-1 must be the most recent price (DESC)");
        page1.Items[9].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 21),
            "page 1 rank-10 must be Jan 21");

        // Page 2 must continue DESC — first item is PVF Jan 20
        page2.Items[0].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 20),
            "page 2 rank-1 must be Jan 20 (rank 11 globally in DESC order)");
        page2.Items[9].PriceValidFrom.Should().Be(new DateOnly(2026, 1, 11),
            "page 2 rank-10 must be Jan 11");
    }

    // §REQ-4.4 — GetActiveAsync: exact boundary PriceValidFrom = query date → returns row
    [Fact]
    public async Task GetActiveAsync_ExactBoundaryPvf_ReturnsRow()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        var pvf = new DateOnly(2026, 4, 19);
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 150.00m, pvf);

        var repo = BuildRepository();
        var result = await repo.GetActiveAsync(_defaultProductId, pvf);

        result.Should().NotBeNull();
        result!.PriceValidFrom.Should().Be(pvf);
        result.Price.Should().Be(150.00m);
        result.IsActive.Should().BeTrue();
    }

    // §REQ-4.4 — GetActiveAsync: date after closed window returns null
    [Fact]
    public async Task GetActiveAsync_DateAfterClosedWindow_ReturnsNull()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Insert and then close via a forward price
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 200.00m, new DateOnly(2026, 2, 1));
        // Now close the second one too by inserting a third further forward
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 300.00m, new DateOnly(2026, 3, 1));

        var repo = BuildRepository();
        // Query for a date before ANY price (before the first PVF)
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2025, 12, 31));

        result.Should().BeNull("date is before any recorded price window");
    }

    // §REQ-2.2 / SqlException mapping — AddAsync maps SqlException 50409 → ProductPriceForwardOnlyException
    [Fact]
    public async Task AddAsync_MapsSqlException50409_ToProductPriceForwardOnlyException()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Insert an active price first
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        // Try a retroactive date — SP will THROW 50409
        var act = async () => await repo.AddAsync(_defaultProductId, 90.00m, new DateOnly(2026, 3, 15));

        await act.Should().ThrowAsync<ProductPriceForwardOnlyException>(
            "SqlException 50409 must be mapped to ProductPriceForwardOnlyException");
    }

    // §REQ-3.3 / SqlException mapping — AddAsync maps SqlException 50404 → ProductNotFoundException
    [Fact]
    public async Task AddAsync_MapsSqlException50404_ToProductNotFoundException()
    {
        var repo = BuildRepository();
        var act = async () => await repo.AddAsync(999999, 100.00m, new DateOnly(2026, 4, 19));

        await act.Should().ThrowAsync<ProductNotFoundException>(
            "SqlException 50404 must be mapped to ProductNotFoundException");
    }

    // Defense-in-depth: unique index violation (2601/2627) → ProductPriceForwardOnlyException
    [Fact]
    public async Task AddAsync_MapsUniqueViolation2601_ToProductPriceForwardOnlyException()
    {
        // Force a 2601 by inserting a row directly that bypasses the SP's SERIALIZABLE guard.
        // Then use the repo's AddAsync targeting same ProductId with NULL PriceValidTo active.
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Insert an active row directly (bypassing SP)
        await seedConn.ExecuteAsync("""
            INSERT INTO dbo.ProductPrices (ProductId, Price, PriceValidFrom, PriceValidTo)
            VALUES (@ProductId, 100.00, '2026-01-01', NULL)
            """, new { ProductId = _defaultProductId });

        // Now try to insert another active row for same ProductId directly (to force 2601/2627)
        // We simulate this by temporarily disabling the SP and doing a direct INSERT via repo's AddAsync.
        // Since AddAsync goes via the SP which uses SERIALIZABLE+UPDLOCK and will see the active row
        // and THROW 50409 (forward-only), but the unique index test for 2601/2627 mapping is already
        // covered by FilteredUniqueIndex_DirectDuplicateActiveInsert_ThrowsUniqueViolation above.
        //
        // Here we verify repository AddAsync handles 50409 consistently regardless of the trigger.
        var act = async () => await new ProductPriceRepository(
            new SqlConnectionFactory(TestConnectionStrings.AppTestDb))
            .AddAsync(_defaultProductId, 200.00m, new DateOnly(2025, 12, 1));

        await act.Should().ThrowAsync<ProductPriceForwardOnlyException>(
            "retroactive date behind existing active row triggers SP 50409 → repository maps to ProductPriceForwardOnlyException");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Batch 7 — T7.1: Concurrency con 3 tasks + SemaphoreSlim barrier
    // §REQ-1.2 — Exactamente 1 ganador, 2 perdedores lanzan excepción manejable
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task Concurrency_ThreeConcurrentInserts_ExactlyOneSucceeds()
    {
        // Barrera: todos esperan en el semáforo; cuando se liberan juntos, la race es auténtica.
        var barrier = new SemaphoreSlim(0, 3);
        var pvf = new DateOnly(2027, 6, 1);

        async Task<Exception?> TryInsert(decimal price)
        {
            // Cada task espera en la barrera antes de ejecutar
            await barrier.WaitAsync();
            try
            {
                await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
                await conn.OpenAsync();
                await ExecAddPriceSpAsync(conn, _defaultProductId, price, pvf);
                return null; // éxito
            }
            catch (SqlException ex)
            {
                return ex; // perdedor: 50409, 2601, 2627 o deadlock 1205
            }
        }

        var t1 = Task.Run(() => TryInsert(111.00m));
        var t2 = Task.Run(() => TryInsert(222.00m));
        var t3 = Task.Run(() => TryInsert(333.00m));

        // Liberar las 3 tasks simultáneamente
        barrier.Release(3);

        var results = await Task.WhenAll(t1, t2, t3);

        // Exactamente 1 éxito (null), exactamente 2 fallos
        var successes = results.Count(r => r is null);
        var failures  = results.Count(r => r is not null);

        successes.Should().Be(1, "exactly one concurrent insert must succeed");
        failures.Should().Be(2, "the other two must fail with a SqlException");

        // Verificar que el estado final es exactamente 1 activo (PriceValidTo IS NULL)
        await using var verifyConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await verifyConn.OpenAsync();
        var activeCount = await verifyConn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.ProductPrices WHERE ProductId = @ProductId AND PriceValidTo IS NULL",
            new { ProductId = _defaultProductId });

        activeCount.Should().Be(1, "only one active price (PriceValidTo IS NULL) must survive the race");

        // Sin duplicados: COUNT(*) para este producto debe ser 1 (solo la ganadora)
        var totalCount = await verifyConn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.ProductPrices WHERE ProductId = @ProductId",
            new { ProductId = _defaultProductId });

        totalCount.Should().Be(1, "no duplicate rows must exist for the same ProductId");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Batch 7 — T7.2: SYSTEM_VERSIONING — no history before close, 1 row after
    // Verifica que el SP produce exactamente 1 row en dbo.ProductPrices_History
    // al cerrar el activo, y que antes del cierre la tabla está vacía para ese Id.
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task SystemVersioning_BeforeClose_HistoryTableIsEmpty()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var (firstId, _) = await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        // Antes del UPDATE (cierre), history debe estar vacía para este Id
        var histCountBefore = await conn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.ProductPrices_History WHERE Id = @Id",
            new { Id = firstId });

        histCountBefore.Should().Be(0,
            "SYSTEM_VERSIONING only creates history rows on UPDATE/DELETE, not on INSERT");
    }

    [Fact]
    public async Task SystemVersioning_AfterClose_ExactlyOneHistoryRow()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        var (firstId, _) = await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        // Cierra el activo con una segunda inserción más futura
        await ExecAddPriceSpAsync(conn, _defaultProductId, 200.00m, new DateOnly(2026, 4, 20));

        // dbo.ProductPrices_History debe tener exactamente 1 row para el Id cerrado
        var histCount = await conn.ExecuteScalarAsync<int>(
            "SELECT COUNT(1) FROM dbo.ProductPrices_History WHERE Id = @Id",
            new { Id = firstId });

        histCount.Should().Be(1,
            "SYSTEM_VERSIONING must produce exactly one history row when the active price is closed via UPDATE");

        // El row activo en dbo.ProductPrices debe tener PriceValidTo <> NULL
        var pvt = await conn.ExecuteScalarAsync<DateTime?>(
            "SELECT PriceValidTo FROM dbo.ProductPrices WHERE Id = @Id",
            new { Id = firstId });

        pvt.Should().NotBeNull("the closed row in dbo.ProductPrices must have PriceValidTo set");
        pvt!.Value.Date.Should().Be(new DateTime(2026, 4, 19),
            "PriceValidTo = new PVF - 1 day = 2026-04-19");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Batch 7 — T7.3: FOR SYSTEM_TIME AS OF — snapshot temporal
    // Verifica que la history table preserva el estado del activo en el instante
    // pre-cierre y que la query temporal devuelve el precio correcto.
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task ForSystemTimeAsOf_ReturnsSnapshotAtT0()
    {
        await using var conn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await conn.OpenAsync();

        // T0: insertar precio1 y capturar el instante UTC antes de cerrarlo
        var (firstId, _) = await ExecAddPriceSpAsync(conn, _defaultProductId, 100.00m, new DateOnly(2026, 4, 1));

        // Capturar T0 inmediatamente después del INSERT (SYSTEM_VERSIONING usa DATETIME2 UTC)
        var t0 = await conn.ExecuteScalarAsync<DateTime>(
            "SELECT SYSUTCDATETIME()");

        // Esperar 200ms para que DATETIME2(3) avance y el registro de history tenga un rango claro
        await Task.Delay(200);

        // Insertar precio2 que cierra precio1 — esto produce el row en history
        await ExecAddPriceSpAsync(conn, _defaultProductId, 200.00m, new DateOnly(2026, 5, 1));

        // T7.3.a — Query FOR SYSTEM_TIME AS OF T0: debe devolver precio1 con PriceValidTo = NULL
        // (estado del registro tal como estaba en T0, antes del cierre)
        var snapshotRow = await conn.QuerySingleOrDefaultAsync<dynamic>(
            """
            SELECT Id, Price, PriceValidTo
            FROM dbo.ProductPrices
            FOR SYSTEM_TIME AS OF @T0
            WHERE ProductId = @ProductId
              AND Id = @Id
            """,
            new { T0 = t0, ProductId = _defaultProductId, Id = firstId });

        ((object?)snapshotRow).Should().NotBeNull(
            "FOR SYSTEM_TIME AS OF T0 must return the row as it existed at T0 (before close)");
        ((decimal)snapshotRow!.Price).Should().Be(100.00m);
        ((object?)snapshotRow.PriceValidTo).Should().BeNull(
            "at T0 the row was still active (PriceValidTo IS NULL)");

        // T7.3.b — Query actual (sin FOR SYSTEM_TIME): precio1 debe tener PriceValidTo != NULL
        var currentRow = await conn.QuerySingleOrDefaultAsync<dynamic>(
            "SELECT Id, Price, PriceValidTo FROM dbo.ProductPrices WHERE Id = @Id",
            new { Id = firstId });

        ((object?)currentRow).Should().NotBeNull();
        ((object?)currentRow!.PriceValidTo).Should().NotBeNull(
            "in current state, the first price is closed (PriceValidTo IS NOT NULL)");
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Batch 7 — T7.5: GetActiveAsync boundary cases (ventanas civiles inclusivas)
    // §REQ-4.4 — Inclusive en ambos extremos (PVF y PVT)
    // ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task GetActiveAsync_BeforeFirstPrice_ReturnsNull()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Precio1: [2026-01-01 .. 2026-03-31] (cerrado)
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 150.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2025, 12, 31));

        result.Should().BeNull("date is before the first PriceValidFrom");
    }

    [Fact]
    public async Task GetActiveAsync_ExactMatchPvf_ReturnsPrice()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Precio1: [2026-01-01 .. 2026-03-31]
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 150.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        // Exact match en PVF
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2026, 1, 1));

        result.Should().NotBeNull("date equals PriceValidFrom → inclusive lower bound");
        result!.Price.Should().Be(100.00m);
    }

    [Fact]
    public async Task GetActiveAsync_MiddleOfRange_ReturnsPrice()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Precio1: [2026-01-01 .. 2026-03-31], Precio2: [2026-04-01 ..]
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 150.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2026, 2, 15));

        result.Should().NotBeNull("date is in the middle of precio1 window");
        result!.Price.Should().Be(100.00m);
    }

    [Fact]
    public async Task GetActiveAsync_ExactMatchPvt_ReturnsClosedPrice()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Precio1: [2026-01-01 .. 2026-03-31] — PVT=2026-03-31 (día anterior a 2026-04-01)
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 150.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        // Exact match en PVT del precio1 → inclusive upper bound
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2026, 3, 31));

        result.Should().NotBeNull("date equals PriceValidTo → inclusive upper bound");
        result!.Price.Should().Be(100.00m);
    }

    [Fact]
    public async Task GetActiveAsync_ExactMatchNextPvf_ReturnsNextPrice()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Precio1: [2026-01-01 .. 2026-03-31], Precio2: [2026-04-01 ..]
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 100.00m, new DateOnly(2026, 1, 1));
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 150.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        // PVF del precio2 → debe devolver precio2
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2026, 4, 1));

        result.Should().NotBeNull("date equals PriceValidFrom of precio2 → inclusive lower bound");
        result!.Price.Should().Be(150.00m);
    }

    [Fact]
    public async Task GetActiveAsync_FarFuture_ReturnsActivePrice()
    {
        await using var seedConn = new SqlConnection(TestConnectionStrings.AppTestDb);
        await seedConn.OpenAsync();

        // Solo precio activo desde 2026-04-01 (PriceValidTo = NULL)
        await ExecAddPriceSpAsync(seedConn, _defaultProductId, 200.00m, new DateOnly(2026, 4, 1));

        var repo = BuildRepository();
        var result = await repo.GetActiveAsync(_defaultProductId, new DateOnly(2099, 12, 31));

        result.Should().NotBeNull("far-future date should return the open-ended active price");
        result!.Price.Should().Be(200.00m);
        result.IsActive.Should().BeTrue();
        result.PriceValidTo.Should().BeNull();
    }

    // ── Helper: build a real ProductPriceRepository using the test DB ─────────

    private static ProductPriceRepository BuildRepository()
        => new(new SqlConnectionFactory(TestConnectionStrings.AppTestDb));

    // ─────────────────────────────────────────────────────────────────────────
    // Schema setup: ensures V019 objects exist in SIGCM2_Test_App
    // ─────────────────────────────────────────────────────────────────────────

    private static async Task EnsureV019SchemaAsync(SqlConnection conn)
    {
        // This will fail (RED) until V019__create_product_prices.sql is applied.
        // Once the migration file is created and applied, all tests above become GREEN.
        var tableExists = await conn.ExecuteScalarAsync<int>("""
            SELECT COUNT(1) FROM sys.tables
            WHERE object_id = OBJECT_ID(N'dbo.ProductPrices', N'U')
            """);

        if (tableExists == 0)
            throw new InvalidOperationException(
                "dbo.ProductPrices does not exist. Apply V019__create_product_prices.sql to SIGCM2_Test_App first.");

        var spExists = await conn.ExecuteScalarAsync<int>("""
            SELECT COUNT(1) FROM sys.objects
            WHERE object_id = OBJECT_ID(N'dbo.usp_AddProductPrice', N'P')
            """);

        if (spExists == 0)
            throw new InvalidOperationException(
                "dbo.usp_AddProductPrice does not exist. Apply V019__create_product_prices.sql to SIGCM2_Test_App first.");
    }
}
