using Dapper;
using SIGCM2.Application.Abstractions.Persistence;
using SIGCM2.Application.Common;
using SIGCM2.Domain.Entities;

namespace SIGCM2.Infrastructure.Persistence;

/// <summary>
/// PRD-002 — Dapper implementation of IProductRepository against dbo.Product.
/// Full implementation in Batch 6.
/// </summary>
public sealed class ProductRepository : IProductRepository
{
    private readonly SqlConnectionFactory _factory;

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

    public async Task<int> AddAsync(Product product, CancellationToken ct = default)
    {
        const string sql = """
            INSERT INTO dbo.Product (
                Nombre, MedioId, ProductTypeId, RubroId, BasePrice, PriceDurationDays,
                IsActive, FechaCreacion
            )
            OUTPUT INSERTED.Id
            VALUES (
                @Nombre, @MedioId, @ProductTypeId, @RubroId, @BasePrice, @PriceDurationDays,
                1, @FechaCreacion
            )
            """;

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

        return await connection.ExecuteScalarAsync<int>(sql, new
        {
            product.Nombre,
            product.MedioId,
            product.ProductTypeId,
            product.RubroId,
            product.BasePrice,
            product.PriceDurationDays,
            product.FechaCreacion,
        });
    }

    public async Task<Product?> GetByIdAsync(int id, CancellationToken ct = default)
    {
        const string sql = """
            SELECT Id, Nombre, MedioId, ProductTypeId, RubroId,
                   BasePrice, PriceDurationDays, IsActive, FechaCreacion, FechaModificacion
            FROM dbo.Product
            WHERE Id = @Id
            """;

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

        var row = await connection.QuerySingleOrDefaultAsync<ProductRow>(sql, new { Id = id });
        return row is null ? null : MapRow(row);
    }

    public async Task<PagedResult<Product>> GetPagedAsync(ProductsQuery query, CancellationToken ct = default)
    {
        var conditions = new List<string>();
        if (query.Activo.HasValue)
            conditions.Add("IsActive = @Activo");
        if (!string.IsNullOrWhiteSpace(query.Search))
            conditions.Add("Nombre LIKE '%' + @Search + '%'");
        if (query.MedioId.HasValue)
            conditions.Add("MedioId = @MedioId");
        if (query.ProductTypeId.HasValue)
            conditions.Add("ProductTypeId = @ProductTypeId");
        if (query.RubroId.HasValue)
            conditions.Add("RubroId = @RubroId");

        var where = conditions.Count > 0
            ? "WHERE " + string.Join(" AND ", conditions)
            : string.Empty;

        var countSql = $"SELECT COUNT(1) FROM dbo.Product {where}";
        var dataSql = $"""
            SELECT Id, Nombre, MedioId, ProductTypeId, RubroId,
                   BasePrice, PriceDurationDays, IsActive, FechaCreacion, FechaModificacion
            FROM dbo.Product
            {where}
            ORDER BY Nombre
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
            """;

        var offset = (query.Page - 1) * query.PageSize;
        var parameters = new
        {
            Activo = query.Activo.HasValue ? (object)(query.Activo.Value ? 1 : 0) : null,
            Search = string.IsNullOrWhiteSpace(query.Search) ? null : query.Search,
            query.MedioId,
            query.ProductTypeId,
            query.RubroId,
            Offset = offset,
            PageSize = query.PageSize,
        };

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

        var total = await connection.ExecuteScalarAsync<int>(countSql, parameters);
        var rows = await connection.QueryAsync<ProductRow>(dataSql, parameters);
        var items = rows.Select(MapRow).ToList();

        return new PagedResult<Product>(items, query.Page, query.PageSize, total);
    }

    public async Task UpdateAsync(Product product, CancellationToken ct = default)
    {
        const string sql = """
            UPDATE dbo.Product
            SET Nombre              = @Nombre,
                RubroId             = @RubroId,
                BasePrice           = @BasePrice,
                PriceDurationDays   = @PriceDurationDays,
                IsActive            = @IsActive,
                FechaModificacion   = @FechaModificacion
            WHERE Id = @Id
            """;

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

        await connection.ExecuteAsync(sql, new
        {
            product.Nombre,
            product.RubroId,
            product.BasePrice,
            product.PriceDurationDays,
            IsActive = product.IsActive ? 1 : 0,
            product.FechaModificacion,
            product.Id,
        });
    }

    public async Task<bool> ExistsByNombreAsync(
        string nombre,
        int medioId,
        int productTypeId,
        int? excludeId = null,
        CancellationToken ct = default)
    {
        const string sql = """
            SELECT COUNT(1)
            FROM dbo.Product
            WHERE Nombre = @Nombre
              AND MedioId = @MedioId
              AND ProductTypeId = @ProductTypeId
              AND IsActive = 1
              AND (@ExcludeId IS NULL OR Id <> @ExcludeId)
            """;

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

        var count = await connection.ExecuteScalarAsync<int>(sql, new
        {
            Nombre = nombre,
            MedioId = medioId,
            ProductTypeId = productTypeId,
            ExcludeId = excludeId,
        });

        return count > 0;
    }

    // ── Mapping ───────────────────────────────────────────────────────────────

    private static Product MapRow(ProductRow r)
        => new(
            id: r.Id,
            nombre: r.Nombre,
            medioId: r.MedioId,
            productTypeId: r.ProductTypeId,
            rubroId: r.RubroId,
            basePrice: r.BasePrice,
            priceDurationDays: r.PriceDurationDays,
            isActive: r.IsActive,
            fechaCreacion: r.FechaCreacion,
            fechaModificacion: r.FechaModificacion);

    private sealed record ProductRow(
        int Id,
        string Nombre,
        int MedioId,
        int ProductTypeId,
        int? RubroId,
        decimal BasePrice,
        int? PriceDurationDays,
        bool IsActive,
        DateTime FechaCreacion,
        DateTime? FechaModificacion);
}
