-- =============================================
-- Stored Procedure: sp_CalculateWeeklyCommissionPool
-- Description: محاسبه استخر کمیسیون هفتگی و توزیع بین کاربران
-- Author: System
-- Created: 2025-12-19
-- =============================================

CREATE OR ALTER PROCEDURE [CMS].[sp_CalculateWeeklyCommissionPool]
    @WeekDefinitionId BIGINT,
    @ForceRecalculate BIT = 0,
    @PoolId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @TotalPoolAmount BIGINT;
    DECLARE @TotalBalances INT;
    DECLARE @ValuePerBalance BIGINT;
    DECLARE @CalculatedAt DATETIME = GETDATE();

    BEGIN TRY
        BEGIN TRANSACTION;

        -- =============================================
        -- 1. پیدا کردن Pool هفته
        -- =============================================
        SELECT @PoolId = Id, @TotalPoolAmount = TotalPoolAmount
        FROM CMS.WeeklyCommissionPools
        WHERE WeekDefinitionId = @WeekDefinitionId;

        IF @PoolId IS NULL
        BEGIN
            SET @ErrorMessage = N'استخر کمیسیون برای هفته ' + CAST(@WeekDefinitionId AS NVARCHAR(20)) + N' تعریف نشده است';
            RAISERROR(@ErrorMessage, 16, 1);
            RETURN;
        END

        -- =============================================
        -- 2. بررسی محاسبات قبلی
        -- =============================================
        IF EXISTS (SELECT 1 FROM CMS.UserCommissionPayouts WHERE WeekDefinitionId = @WeekDefinitionId)
        BEGIN
            IF @ForceRecalculate = 0
            BEGIN
                SET @ErrorMessage = N'کمیسیون هفته ' + CAST(@WeekDefinitionId AS NVARCHAR(20)) + N' قبلاً محاسبه شده است. برای محاسبه مجدد از ForceRecalculate استفاده کنید';
                RAISERROR(@ErrorMessage, 16, 1);
                RETURN;
            END

            -- =============================================
            -- 3. Revert کیف پول‌ها (در صورت ForceRecalculate)
            -- =============================================
            
            -- کم کردن مقادیر قبلی از NetworkBalance
            UPDATE uw
            SET uw.NetworkBalance = uw.NetworkBalance - ISNULL(
                (SELECT SUM(wl.ChangeNerworkValue) 
                 FROM CMS.UserWalletChangeLogs wl 
                 WHERE wl.WalletId = uw.Id 
                 AND wl.RefrenceId IN (SELECT Id FROM CMS.UserCommissionPayouts WHERE WeekDefinitionId = @WeekDefinitionId)
                ), 0)
            FROM CMS.UserWallets uw
            WHERE uw.Id IN (
                SELECT DISTINCT wl.WalletId 
                FROM CMS.UserWalletChangeLogs wl 
                WHERE wl.RefrenceId IN (SELECT Id FROM CMS.UserCommissionPayouts WHERE WeekDefinitionId = @WeekDefinitionId)
            );

            -- حذف لاگ‌های تغییرات قبلی
            DELETE FROM CMS.UserWalletChangeLogs 
            WHERE RefrenceId IN (SELECT Id FROM CMS.UserCommissionPayouts WHERE WeekDefinitionId = @WeekDefinitionId);

            -- حذف تاریخچه پرداخت
            DELETE FROM CMS.CommissionPayoutHistories 
            WHERE UserCommissionPayoutId IN (SELECT Id FROM CMS.UserCommissionPayouts WHERE WeekDefinitionId = @WeekDefinitionId);

            -- حذف پرداخت‌های قبلی
            DELETE FROM CMS.UserCommissionPayouts WHERE WeekDefinitionId = @WeekDefinitionId;

            -- Reset وضعیت Pool
            UPDATE CMS.WeeklyCommissionPools 
            SET IsCalculated = 0 
            WHERE Id = @PoolId;
        END

        -- =============================================
        -- 4. بررسی وجود تعادل‌های هفتگی
        -- =============================================
        IF NOT EXISTS (SELECT 1 FROM CMS.NetworkWeeklyBalances WHERE WeekDefinitionId = @WeekDefinitionId)
        BEGIN
            SET @ErrorMessage = N'ابتدا باید تعادل‌های هفتگی محاسبه شوند. sp_CalculateWeeklyBalances را اجرا کنید';
            RAISERROR(@ErrorMessage, 16, 1);
            RETURN;
        END

        -- =============================================
        -- 5. محاسبه مجموع تعادل‌ها
        -- =============================================
        SELECT @TotalBalances = SUM(TotalBalances)
        FROM CMS.NetworkWeeklyBalances
        WHERE WeekDefinitionId = @WeekDefinitionId AND TotalBalances > 0;

        IF @TotalBalances IS NULL OR @TotalBalances = 0
        BEGIN
            -- هیچ تعادلی برای توزیع وجود ندارد
            UPDATE CMS.WeeklyCommissionPools 
            SET IsCalculated = 1, CalculatedAt = @CalculatedAt
            WHERE Id = @PoolId;
            
            COMMIT TRANSACTION;
            RETURN;
        END

        -- =============================================
        -- 6. محاسبه ارزش هر تعادل
        -- =============================================
        SET @ValuePerBalance = @TotalPoolAmount / @TotalBalances;

        -- =============================================
        -- 7. ایجاد پرداخت برای هر کاربر
        -- =============================================
        INSERT INTO CMS.UserCommissionPayouts (
            UserId,
            WeekDefinitionId,
            WeeklyPoolId,
            BalancesEarned,
            ValuePerBalance,
            TotalAmount,
            Status,
            PaidAt,
            IsDeleted,
            Created,
            CreatedBy,
            LastModified,
            LastModifiedBy
        )
        SELECT 
            nb.UserId,
            @WeekDefinitionId,
            @PoolId,
            nb.TotalBalances,
            @ValuePerBalance,
            nb.TotalBalances * @ValuePerBalance,
            0, -- Status: Pending
            NULL, -- PaidAt
            0, -- IsDeleted
            @CalculatedAt,
            'SP',
            @CalculatedAt,
            'SP'
        FROM CMS.NetworkWeeklyBalances nb
        WHERE nb.WeekDefinitionId = @WeekDefinitionId AND nb.TotalBalances > 0;

        -- =============================================
        -- 8. ثبت تاریخچه پرداخت
        -- =============================================
        INSERT INTO CMS.CommissionPayoutHistories (
            UserCommissionPayoutId,
            UserId,
            WeekDefinitionId,
            AmountBefore,
            AmountAfter,
            OldStatus,
            NewStatus,
            Action,
            PerformedBy,
            Reason,
            IsDeleted,
            Created,
            CreatedBy,
            LastModified,
            LastModifiedBy
        )
        SELECT 
            cp.Id,           -- UserCommissionPayoutId
            cp.UserId,       -- UserId
            @WeekDefinitionId,
            0,               -- AmountBefore
            cp.TotalAmount,  -- AmountAfter
            0,               -- OldStatus: Pending
            0,               -- NewStatus: Pending
            0,               -- Action: Created (assuming enum value)
            'SP',            -- PerformedBy
            N'محاسبه اولیه توسط SP', -- Reason
            0,               -- IsDeleted
            @CalculatedAt,
            'SP',
            @CalculatedAt,
            'SP'
        FROM CMS.UserCommissionPayouts cp
        WHERE cp.WeekDefinitionId = @WeekDefinitionId;

        -- =============================================
        -- 9. شارژ کیف پول کاربران
        -- =============================================
        
        -- بروزرسانی NetworkBalance
        UPDATE uw
        SET uw.NetworkBalance = uw.NetworkBalance + cp.TotalAmount
        FROM CMS.UserWallets uw
        INNER JOIN CMS.UserCommissionPayouts cp ON cp.UserId = uw.UserId
        WHERE cp.WeekDefinitionId = @WeekDefinitionId;

        -- ثبت لاگ تغییرات کیف پول
        INSERT INTO CMS.UserWalletChangeLogs (
            WalletId,
            CurrentBalance,
            ChangeValue,
            CurrentNetworkBalance,
            ChangeNerworkValue,
            CurrentDiscountBalance,
            ChangeDiscountValue,
            IsIncrease,
            RefrenceId,
            IsDeleted,
            Created,
            CreatedBy,
            LastModified,
            LastModifiedBy
        )
        SELECT 
            uw.Id,
            uw.Balance,                          -- CurrentBalance
            0,                                   -- ChangeValue (no change to main balance)
            uw.NetworkBalance,                   -- CurrentNetworkBalance (after update)
            cp.TotalAmount,                      -- ChangeNerworkValue
            uw.DiscountBalance,                  -- CurrentDiscountBalance
            0,                                   -- ChangeDiscountValue
            1,                                   -- IsIncrease: true
            cp.Id,                               -- RefrenceId: PayoutId
            0,                                   -- IsDeleted
            @CalculatedAt,
            'SP',
            @CalculatedAt,
            'SP'
        FROM CMS.UserCommissionPayouts cp
        INNER JOIN CMS.UserWallets uw ON uw.UserId = cp.UserId
        WHERE cp.WeekDefinitionId = @WeekDefinitionId;

        -- =============================================
        -- 10. بروزرسانی Pool
        -- =============================================
        UPDATE CMS.WeeklyCommissionPools 
        SET 
            IsCalculated = 1, 
            CalculatedAt = @CalculatedAt,
            TotalBalances = @TotalBalances,
            ValuePerBalance = @ValuePerBalance,
            LastModified = @CalculatedAt,
            LastModifiedBy = 'SP'
        WHERE Id = @PoolId;

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @ErrorMessage = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END
GO
