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

namespace SIGCM2.Infrastructure.Persistence;

public sealed class ProductTypeRepository : IProductTypeRepository
{
    private readonly SqlConnectionFactory _factory;

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

    public async Task<int> AddAsync(ProductType productType, CancellationToken ct = default)
    {
        // DF handles: IsActive (1), FechaCreacion (SYSUTCDATETIME()).
        const string sql = """
            INSERT INTO dbo.ProductType (
                Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle,
                AllowImages, MaxImages, MaxImageSizeMB, MaxImageWidth, MaxImageHeight
            )
            OUTPUT INSERTED.Id
            VALUES (
                @Nombre, @HasDuration, @RequiresText, @RequiresCategory, @IsBundle,
                @AllowImages, @MaxImages, @MaxImageSizeMB, @MaxImageWidth, @MaxImageHeight
            )
            """;

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

        return await connection.ExecuteScalarAsync<int>(sql, new
        {
            productType.Nombre,
            HasDuration = productType.HasDuration ? 1 : 0,
            RequiresText = productType.RequiresText ? 1 : 0,
            RequiresCategory = productType.RequiresCategory ? 1 : 0,
            IsBundle = productType.IsBundle ? 1 : 0,
            AllowImages = productType.AllowImages ? 1 : 0,
            productType.MaxImages,
            productType.MaxImageSizeMB,
            productType.MaxImageWidth,
            productType.MaxImageHeight,
        });
    }

    public async Task<ProductType?> GetByIdAsync(int id, CancellationToken ct = default)
    {
        const string sql = """
            SELECT Id, Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle,
                   AllowImages, MaxImages, MaxImageSizeMB, MaxImageWidth, MaxImageHeight,
                   IsActive, FechaCreacion, FechaModificacion
            FROM dbo.ProductType
            WHERE Id = @Id
            """;

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

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

    public async Task<PagedResult<ProductType>> GetPagedAsync(
        ProductTypesQuery query,
        CancellationToken ct = default)
    {
        // Build the WHERE clause dynamically.
        var conditions = new List<string>();
        if (query.Activo.HasValue)
            conditions.Add("IsActive = @Activo");
        if (!string.IsNullOrWhiteSpace(query.Search))
            conditions.Add("Nombre LIKE '%' + @Search + '%'");

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

        var countSql = $"SELECT COUNT(1) FROM dbo.ProductType {where}";
        var dataSql = $"""
            SELECT Id, Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle,
                   AllowImages, MaxImages, MaxImageSizeMB, MaxImageWidth, MaxImageHeight,
                   IsActive, FechaCreacion, FechaModificacion
            FROM dbo.ProductType
            {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,
            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<ProductTypeRow>(dataSql, parameters);
        var items = rows.Select(MapRow).ToList();

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

    public async Task UpdateAsync(ProductType productType, CancellationToken ct = default)
    {
        const string sql = """
            UPDATE dbo.ProductType
            SET Nombre              = @Nombre,
                HasDuration         = @HasDuration,
                RequiresText        = @RequiresText,
                RequiresCategory    = @RequiresCategory,
                IsBundle            = @IsBundle,
                AllowImages         = @AllowImages,
                MaxImages           = @MaxImages,
                MaxImageSizeMB      = @MaxImageSizeMB,
                MaxImageWidth       = @MaxImageWidth,
                MaxImageHeight      = @MaxImageHeight,
                IsActive            = @IsActive,
                FechaModificacion   = @FechaModificacion
            WHERE Id = @Id
            """;

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

        await connection.ExecuteAsync(sql, new
        {
            productType.Nombre,
            HasDuration = productType.HasDuration ? 1 : 0,
            RequiresText = productType.RequiresText ? 1 : 0,
            RequiresCategory = productType.RequiresCategory ? 1 : 0,
            IsBundle = productType.IsBundle ? 1 : 0,
            AllowImages = productType.AllowImages ? 1 : 0,
            productType.MaxImages,
            productType.MaxImageSizeMB,
            productType.MaxImageWidth,
            productType.MaxImageHeight,
            IsActive = productType.IsActive ? 1 : 0,
            productType.FechaModificacion,
            productType.Id,
        });
    }

    public async Task<bool> ExistsByNombreAsync(
        string nombre,
        int? excludeId = null,
        CancellationToken ct = default)
    {
        // DB collation is SQL_Latin1_General_CP1_CI_AI on Nombre (CI) — comparison is
        // already case-insensitive; no need for UPPER(). The filtered unique index
        // (UQ_ProductType_Nombre_Activo WHERE IsActive=1) aligns with this query.
        const string sql = """
            SELECT COUNT(1)
            FROM dbo.ProductType
            WHERE Nombre = @Nombre
              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,
            ExcludeId = excludeId,
        });

        return count > 0;
    }

    // ── mapping ───────────────────────────────────────────────────────────────

    private static ProductType MapRow(ProductTypeRow r)
        => new(
            id: r.Id,
            nombre: r.Nombre,
            hasDuration: r.HasDuration,
            requiresText: r.RequiresText,
            requiresCategory: r.RequiresCategory,
            isBundle: r.IsBundle,
            allowImages: r.AllowImages,
            maxImages: r.MaxImages,
            maxImageSizeMB: r.MaxImageSizeMB,
            maxImageWidth: r.MaxImageWidth,
            maxImageHeight: r.MaxImageHeight,
            isActive: r.IsActive,
            fechaCreacion: r.FechaCreacion,
            fechaModificacion: r.FechaModificacion);

    private sealed record ProductTypeRow(
        int Id,
        string Nombre,
        bool HasDuration,
        bool RequiresText,
        bool RequiresCategory,
        bool IsBundle,
        bool AllowImages,
        int? MaxImages,
        decimal? MaxImageSizeMB,
        int? MaxImageWidth,
        int? MaxImageHeight,
        bool IsActive,
        DateTime FechaCreacion,
        DateTime? FechaModificacion);
}
