using CMSMicroservice.Application.Common.Interfaces;
using Microsoft.EntityFrameworkCore;

namespace CMSMicroservice.Infrastructure.Services.Commission;

/// <summary>
/// پیاده‌سازی محاسبه کمیسیون با استفاده از Stored Procedure
/// این روش برای داده‌های زیاد بهینه‌تر است و از CTE استفاده می‌کند
/// </summary>
public class StoredProcedureCommissionCalculationStrategy : ICommissionCalculationStrategy
{
    private readonly IApplicationDbContext _context;

    public StoredProcedureCommissionCalculationStrategy(IApplicationDbContext context)
    {
        _context = context;
    }

    /// <inheritdoc />
    public async Task<int> CalculateWeeklyBalancesAsync(
        long weekDefinitionId,
        bool forceRecalculate,
        CancellationToken cancellationToken = default)
    {
        // دسترسی به DbContext واقعی برای اجرای SP
        var dbContext = _context as DbContext;
        if (dbContext == null)
        {
            throw new InvalidOperationException("DbContext برای اجرای Stored Procedure در دسترس نیست");
        }

        var connection = dbContext.Database.GetDbConnection();
        await connection.OpenAsync(cancellationToken);

        try
        {
            using var command = connection.CreateCommand();
            command.CommandText = "CMS.sp_CalculateWeeklyBalances";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandTimeout = 300; // 5 دقیقه timeout

            // پارامترها
            var paramWeekDefinitionId = command.CreateParameter();
            paramWeekDefinitionId.ParameterName = "@WeekDefinitionId";
            paramWeekDefinitionId.Value = weekDefinitionId;
            command.Parameters.Add(paramWeekDefinitionId);

            var paramForceRecalculate = command.CreateParameter();
            paramForceRecalculate.ParameterName = "@ForceRecalculate";
            paramForceRecalculate.Value = forceRecalculate;
            command.Parameters.Add(paramForceRecalculate);

            // پارامتر خروجی
            var paramRowCount = command.CreateParameter();
            paramRowCount.ParameterName = "@RowCount";
            paramRowCount.Direction = System.Data.ParameterDirection.Output;
            paramRowCount.DbType = System.Data.DbType.Int32;
            command.Parameters.Add(paramRowCount);

            await command.ExecuteNonQueryAsync(cancellationToken);

            var rowCount = (int)(paramRowCount.Value ?? 0);
            return rowCount;
        }
        finally
        {
            await connection.CloseAsync();
        }
    }

    /// <inheritdoc />
    public async Task<long> CalculateWeeklyCommissionPoolAsync(
        long weekDefinitionId,
        bool forceRecalculate,
        CancellationToken cancellationToken = default)
    {
        var dbContext = _context as DbContext;
        if (dbContext == null)
        {
            throw new InvalidOperationException("DbContext برای اجرای Stored Procedure در دسترس نیست");
        }

        var connection = dbContext.Database.GetDbConnection();
        await connection.OpenAsync(cancellationToken);

        try
        {
            using var command = connection.CreateCommand();
            command.CommandText = "CMS.sp_CalculateWeeklyCommissionPool";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandTimeout = 300; // 5 دقیقه timeout

            // پارامترها
            var paramWeekDefinitionId = command.CreateParameter();
            paramWeekDefinitionId.ParameterName = "@WeekDefinitionId";
            paramWeekDefinitionId.Value = weekDefinitionId;
            command.Parameters.Add(paramWeekDefinitionId);

            var paramForceRecalculate = command.CreateParameter();
            paramForceRecalculate.ParameterName = "@ForceRecalculate";
            paramForceRecalculate.Value = forceRecalculate;
            command.Parameters.Add(paramForceRecalculate);

            // پارامتر خروجی - شناسه Pool
            var paramPoolId = command.CreateParameter();
            paramPoolId.ParameterName = "@PoolId";
            paramPoolId.Direction = System.Data.ParameterDirection.Output;
            paramPoolId.DbType = System.Data.DbType.Int64;
            command.Parameters.Add(paramPoolId);

            await command.ExecuteNonQueryAsync(cancellationToken);

            var poolId = (long)(paramPoolId.Value ?? 0L);
            return poolId;
        }
        finally
        {
            await connection.CloseAsync();
        }
    }
}
