using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using SIGCM2.Application.Abstractions.Persistence;
using SIGCM2.Domain.Entities;
using SIGCM2.Domain.Exceptions;

namespace SIGCM2.Infrastructure.Persistence;

/// <summary>
/// PRD-003 — Dapper implementation of IProductPriceRepository against dbo.ProductPrices.
/// AddAsync invokes dbo.usp_AddProductPrice and maps SqlException numbers to domain exceptions.
/// GetByProductIdAsync and GetActiveAsync run direct SQL and map DateTime columns to DateOnly.
/// </summary>
public sealed class ProductPriceRepository : IProductPriceRepository
{
    private readonly SqlConnectionFactory _factory;

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

    /// <inheritdoc/>
    public async Task<(long NewId, long? ClosedId)> AddAsync(
        int productId,
        decimal price,
        DateOnly priceValidFrom,
        CancellationToken ct = default)
    {
        var p = new DynamicParameters();
        p.Add("@ProductId",      productId,                                        DbType.Int32);
        p.Add("@Price",          price,                                            DbType.Decimal, precision: 12, scale: 2);
        p.Add("@PriceValidFrom", priceValidFrom.ToDateTime(TimeOnly.MinValue),     DbType.Date);
        p.Add("@NewId",          dbType: DbType.Int64, direction: ParameterDirection.Output);
        p.Add("@ClosedId",       dbType: DbType.Int64, direction: ParameterDirection.Output);

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

        try
        {
            await connection.ExecuteAsync(
                new CommandDefinition(
                    "dbo.usp_AddProductPrice",
                    p,
                    commandType: CommandType.StoredProcedure,
                    cancellationToken: ct));
        }
        catch (SqlException ex) when (ex.Number == 50404)
        {
            throw new ProductNotFoundException(productId);
        }
        catch (SqlException ex) when (ex.Number == 50409)
        {
            // Forward-only violation detected by SP (new PVF <= active PVF).
            // activePriceValidFrom is not returned by the SP; use MinValue as safe placeholder.
            throw new ProductPriceForwardOnlyException(productId, priceValidFrom, DateOnly.MinValue);
        }
        catch (SqlException ex) when (ex.Number == 2601 || ex.Number == 2627)
        {
            // Race condition: two concurrent inserts with PriceValidTo IS NULL slipped through
            // the SERIALIZABLE guard. Defense-in-depth: surface as forward-only.
            throw new ProductPriceForwardOnlyException(productId, priceValidFrom, DateOnly.MinValue);
        }

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

    /// <inheritdoc/>
    public async Task<IReadOnlyList<ProductPrice>> GetByProductIdAsync(
        int productId,
        CancellationToken ct = default)
    {
        // Uses IX_ProductPrices_Lookup (ProductId, PriceValidFrom DESC).
        const string sql = """
            SELECT Id, ProductId, Price, PriceValidFrom, PriceValidTo, FechaCreacion
            FROM dbo.ProductPrices
            WHERE ProductId = @ProductId
            ORDER BY PriceValidFrom DESC, Id DESC
            """;

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

        var rows = await connection.QueryAsync<ProductPriceRow>(
            new CommandDefinition(sql, new { ProductId = productId }, cancellationToken: ct));

        return rows.Select(MapRow).ToList();
    }

    /// <inheritdoc/>
    public async Task<ProductPrice?> GetActiveAsync(
        int productId,
        DateOnly date,
        CancellationToken ct = default)
    {
        // Uses IX_ProductPrices_Lookup (ProductId, PriceValidFrom DESC) INCLUDE(Price, PriceValidTo).
        // TOP 1 ordered DESC by PriceValidFrom returns the most-recent row whose window covers date.
        const string sql = """
            SELECT TOP 1 Id, ProductId, Price, PriceValidFrom, PriceValidTo, FechaCreacion
            FROM dbo.ProductPrices
            WHERE ProductId = @ProductId
              AND PriceValidFrom <= @Date
              AND (PriceValidTo IS NULL OR PriceValidTo >= @Date)
            ORDER BY PriceValidFrom DESC, Id DESC
            """;

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

        var row = await connection.QuerySingleOrDefaultAsync<ProductPriceRow>(
            new CommandDefinition(
                sql,
                new { ProductId = productId, Date = date.ToDateTime(TimeOnly.MinValue) },
                cancellationToken: ct));

        return row is null ? null : MapRow(row);
    }

    // ── Mapping ───────────────────────────────────────────────────────────────
    // Dapper maps SQL DATE columns to DateTime; we convert to DateOnly here.

    private static ProductPrice MapRow(ProductPriceRow r)
        => new(
            Id:             r.Id,
            ProductId:      r.ProductId,
            Price:          r.Price,
            PriceValidFrom: DateOnly.FromDateTime(r.PriceValidFrom),
            PriceValidTo:   r.PriceValidTo.HasValue
                                ? DateOnly.FromDateTime(r.PriceValidTo.Value)
                                : (DateOnly?)null,
            FechaCreacion:  r.FechaCreacion);

    private sealed record ProductPriceRow(
        long      Id,
        int       ProductId,
        decimal   Price,
        DateTime  PriceValidFrom,
        DateTime? PriceValidTo,
        DateTime  FechaCreacion);
}
