-- =============================================
-- Stored Procedure: sp_CalculateWeeklyBalances
-- Description: محاسبه تعادل‌های هفتگی شبکه باینری با استفاده از CTE
-- Author: System
-- Created: 2025-12-19
-- =============================================

CREATE OR ALTER PROCEDURE [CMS].[sp_CalculateWeeklyBalances]
    @WeekDefinitionId BIGINT,
    @ForceRecalculate BIT = 0,
    @PackageId BIGINT = NULL,              -- پکیج خاص (اگر NULL از پکیج پایه استفاده می‌کند)
    @InputMaxBalancesPerLeg INT = NULL,    -- سقف تعادل هر پا (از Package entity)
    @InputMaxNetworkLevel INT = NULL,      -- حداکثر عمق شبکه (از Package entity)
    @RowCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @StartDate DATETIME;
    DECLARE @EndDate DATETIME;
    DECLARE @PreviousWeekDefinitionId BIGINT;
    DECLARE @MaxBalancesPerLeg INT;
    DECLARE @MaxNetworkLevel INT;
    DECLARE @CalculatedAt DATETIME = GETDATE();
    DECLARE @HasPackageActivityUntilWeek BIT = 0;

    -- تعیین PackageId پیش‌فرض (پکیج پایه)
    IF @PackageId IS NULL
    BEGIN
        SELECT TOP 1 @PackageId = Id
        FROM CMS.Packages
        WHERE IsBasePackage = 1 AND IsDeleted = 0;
    END

    -- خواندن تنظیمات از Package entity اگر پارامتر داده نشده
    IF @InputMaxBalancesPerLeg IS NOT NULL
        SET @MaxBalancesPerLeg = @InputMaxBalancesPerLeg;
    ELSE
        SELECT @MaxBalancesPerLeg = ISNULL(MaxBalancesPerLeg, 300) FROM CMS.Packages WHERE Id = @PackageId;

    IF @InputMaxNetworkLevel IS NOT NULL
        SET @MaxNetworkLevel = @InputMaxNetworkLevel;
    ELSE
        SELECT @MaxNetworkLevel = ISNULL(MaxNetworkLevel, 15) FROM CMS.Packages WHERE Id = @PackageId;

    -- fallbackهای امن
    SET @MaxBalancesPerLeg = ISNULL(@MaxBalancesPerLeg, 300);
    SET @MaxNetworkLevel = ISNULL(@MaxNetworkLevel, 15);

    BEGIN TRY
        BEGIN TRANSACTION;

        -- =============================================
        -- 1. اعتبارسنجی WeekDefinitionId
        -- =============================================
        IF NOT EXISTS (SELECT 1 FROM CMS.WeekDefinitions WHERE Id = @WeekDefinitionId)
        BEGIN
            SET @ErrorMessage = N'هفته ' + CAST(@WeekDefinitionId AS NVARCHAR(20)) + N' در سیستم تعریف نشده است';
            RAISERROR(@ErrorMessage, 16, 1);
            RETURN;
        END

        -- دریافت بازه تاریخی هفته
        SELECT @StartDate = StartDate, @EndDate = EndDate
        FROM CMS.WeekDefinitions
        WHERE Id = @WeekDefinitionId;

        -- اگر تا پایان این هفته هنوز هیچ Cycle برای این پکیج وجود نداشته،
        -- این پکیج نباید برای هفته‌های قبل از شروعش هیچ رکوردی بسازد.
        IF EXISTS (
            SELECT 1
            FROM CMS.ClubMembershipCycles cc
            WHERE cc.PackageId = @PackageId
              AND cc.IsDeleted = 0
              AND cc.PackagePurchasedAt <= @EndDate
        )
        BEGIN
            SET @HasPackageActivityUntilWeek = 1;
        END

        IF @HasPackageActivityUntilWeek = 0
        BEGIN
            SET @RowCount = 0;
            COMMIT TRANSACTION;
            RETURN;
        END

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

        -- =============================================
        -- 3. پیدا کردن هفته قبل
        -- =============================================
        SELECT TOP 1 @PreviousWeekDefinitionId = Id
        FROM CMS.WeekDefinitions
        WHERE WeekOrder = (SELECT WeekOrder - 1 FROM CMS.WeekDefinitions WHERE Id = @WeekDefinitionId)
        AND IsActive = 1;

        -- =============================================
        -- 4. تنظیمات پکیج (از پارامترها خوانده می‌شود)
        -- =============================================
        -- @MaxBalancesPerLeg و @MaxNetworkLevel قبلاً مقداردهی شده‌اند

        -- =============================================
        -- 5. ایجاد جداول موقت برای نتایج
        -- =============================================
        CREATE TABLE #Balances (
            UserId BIGINT PRIMARY KEY,
            LeftLegNewMembers INT DEFAULT 0,
            RightLegNewMembers INT DEFAULT 0,
            LeftLegCarryover INT DEFAULT 0,
            RightLegCarryover INT DEFAULT 0,
            LeftLegTotal INT DEFAULT 0,
            RightLegTotal INT DEFAULT 0,
            TotalBalances INT DEFAULT 0,
            LeftLegRemainder INT DEFAULT 0,
            RightLegRemainder INT DEFAULT 0,
            FlushedPerSide INT DEFAULT 0,
            TotalFlushed INT DEFAULT 0,
            SubordinateBalances INT DEFAULT 0
        );

        CREATE TABLE #LeftNewMembers (
            UserId BIGINT PRIMARY KEY,
            NewMembersCount INT NOT NULL
        );

        CREATE TABLE #RightNewMembers (
            UserId BIGINT PRIMARY KEY,
            NewMembersCount INT NOT NULL
        );

        -- =============================================
        -- 6. محاسبه اعضای جدید هر پا با CTE (بهینه!)
        -- =============================================
        
        -- CTE برای شمارش اعضای جدید شاخه چپ
        ;WITH LeftLegCTE AS (
            -- فرزند مستقیم چپ (لول 1)
            SELECT 
                u.NetworkParentId AS ParentId,
                u.Id AS ChildId,
                1 AS Level
            FROM CMS.Users u
            WHERE u.LegPosition = 0 -- Left
            AND u.NetworkParentId IS NOT NULL

            UNION ALL

            -- زیرمجموعه‌های بازگشتی
            SELECT 
                cte.ParentId,
                u.Id AS ChildId,
                cte.Level + 1 AS Level
            FROM CMS.Users u
            INNER JOIN LeftLegCTE cte ON u.NetworkParentId = cte.ChildId
            WHERE cte.Level < @MaxNetworkLevel
        ),
        LeftNewMembers AS (
            SELECT 
                cte.ParentId AS UserId,
                COUNT(DISTINCT cte.ChildId) AS NewMembersCount
            FROM LeftLegCTE cte
            INNER JOIN CMS.ClubMembershipCycles cc ON cc.UserId = cte.ChildId
            WHERE cc.PackagePurchasedAt >= @StartDate
              AND cc.PackagePurchasedAt <= @EndDate
              AND cc.PackageId = @PackageId
              AND cc.IsDeleted = 0
            GROUP BY cte.ParentId
        )
        INSERT INTO #LeftNewMembers (UserId, NewMembersCount)
        SELECT UserId, NewMembersCount
        FROM LeftNewMembers
        OPTION (MAXRECURSION 100);

        -- CTE برای شمارش اعضای جدید شاخه راست
        ;WITH RightLegCTE AS (
            -- فرزند مستقیم راست (لول 1)
            SELECT 
                u.NetworkParentId AS ParentId,
                u.Id AS ChildId,
                1 AS Level
            FROM CMS.Users u
            WHERE u.LegPosition = 1 -- Right
            AND u.NetworkParentId IS NOT NULL

            UNION ALL

            -- زیرمجموعه‌های بازگشتی
            SELECT 
                cte.ParentId,
                u.Id AS ChildId,
                cte.Level + 1 AS Level
            FROM CMS.Users u
            INNER JOIN RightLegCTE cte ON u.NetworkParentId = cte.ChildId
            WHERE cte.Level < @MaxNetworkLevel
        ),
        RightNewMembers AS (
            SELECT 
                cte.ParentId AS UserId,
                COUNT(DISTINCT cte.ChildId) AS NewMembersCount
            FROM RightLegCTE cte
            INNER JOIN CMS.ClubMembershipCycles cc ON cc.UserId = cte.ChildId
            WHERE cc.PackagePurchasedAt >= @StartDate
              AND cc.PackagePurchasedAt <= @EndDate
              AND cc.PackageId = @PackageId
              AND cc.IsDeleted = 0
            GROUP BY cte.ParentId
        )
        INSERT INTO #RightNewMembers (UserId, NewMembersCount)
        SELECT UserId, NewMembersCount
        FROM RightNewMembers
        OPTION (MAXRECURSION 100);

        -- =============================================
        -- 7. انتخاب parentهایی که باید برای این پکیج/هفته row بگیرند
        -- شرط ورود:
        --   - عضو فعال باشگاه باشند
        --   - یا از هفته قبل برای همین پکیج carryover داشته باشند
        --   - یا این هفته recruit جدید همین پکیج در چپ/راست subtree داشته باشند
        -- =============================================
        INSERT INTO #Balances (UserId)
        SELECT DISTINCT cm.UserId
        FROM CMS.ClubMemberships cm
        INNER JOIN CMS.Users u ON u.Id = cm.UserId
        LEFT JOIN #LeftNewMembers lnm ON lnm.UserId = cm.UserId
        LEFT JOIN #RightNewMembers rnm ON rnm.UserId = cm.UserId
        LEFT JOIN CMS.NetworkWeeklyBalances prev ON prev.UserId = cm.UserId
            AND prev.WeekDefinitionId = @PreviousWeekDefinitionId
            AND prev.PackageId = @PackageId
        WHERE cm.IsActive = 1
          AND u.IsDeleted = 0
          AND (
                ISNULL(lnm.NewMembersCount, 0) > 0
             OR ISNULL(rnm.NewMembersCount, 0) > 0
             OR ISNULL(prev.LeftLegRemainder, 0) > 0
             OR ISNULL(prev.RightLegRemainder, 0) > 0
          );

        -- =============================================
        -- 8. دریافت باقیمانده هفته قبل + اعمال counts محاسبه‌شده
        -- =============================================
        IF @PreviousWeekDefinitionId IS NOT NULL
        BEGIN
            UPDATE b
            SET b.LeftLegCarryover = ISNULL(nb.LeftLegRemainder, 0),
                b.RightLegCarryover = ISNULL(nb.RightLegRemainder, 0)
            FROM #Balances b
            LEFT JOIN CMS.NetworkWeeklyBalances nb ON nb.UserId = b.UserId 
                AND nb.WeekDefinitionId = @PreviousWeekDefinitionId
                AND nb.PackageId = @PackageId;
        END

        UPDATE b
        SET b.LeftLegNewMembers = ISNULL(lnm.NewMembersCount, 0)
        FROM #Balances b
        LEFT JOIN #LeftNewMembers lnm ON lnm.UserId = b.UserId;

        UPDATE b
        SET b.RightLegNewMembers = ISNULL(rnm.NewMembersCount, 0)
        FROM #Balances b
        LEFT JOIN #RightNewMembers rnm ON rnm.UserId = b.UserId;

        -- =============================================
        -- 9. محاسبه تعادل‌ها
        -- =============================================
        UPDATE #Balances
        SET 
            -- مجموع هر پا
            LeftLegTotal = LeftLegNewMembers + LeftLegCarryover,
            RightLegTotal = RightLegNewMembers + RightLegCarryover;

        UPDATE #Balances
        SET
            -- تعادل = MIN(چپ، راست)
            TotalBalances = CASE 
                WHEN LeftLegTotal <= RightLegTotal THEN LeftLegTotal 
                ELSE RightLegTotal 
            END;

        UPDATE #Balances
        SET
            -- باقیمانده برای هفته بعد
            LeftLegRemainder = LeftLegTotal - TotalBalances,
            RightLegRemainder = RightLegTotal - TotalBalances,
            -- فلش (اگر تعادل > سقف)
            FlushedPerSide = CASE 
                WHEN TotalBalances > @MaxBalancesPerLeg THEN TotalBalances - @MaxBalancesPerLeg 
                ELSE 0 
            END;

        UPDATE #Balances
        SET
            -- اعمال سقف
            TotalBalances = CASE 
                WHEN TotalBalances > @MaxBalancesPerLeg THEN @MaxBalancesPerLeg 
                ELSE TotalBalances 
            END,
            -- مجموع فلش از دو طرف
            TotalFlushed = FlushedPerSide * 2;

        -- =============================================
        -- 10. محاسبه تعادل زیرمجموعه با CTE
        -- =============================================
        ;WITH SubordinateCTE AS (
            -- فرزندان مستقیم (لول 1)
            SELECT 
                u.NetworkParentId AS ParentId,
                u.Id AS ChildId,
                1 AS Level
            FROM CMS.Users u
            WHERE u.NetworkParentId IS NOT NULL

            UNION ALL

            -- زیرمجموعه‌های بازگشتی
            SELECT 
                cte.ParentId,
                u.Id AS ChildId,
                cte.Level + 1 AS Level
            FROM CMS.Users u
            INNER JOIN SubordinateCTE cte ON u.NetworkParentId = cte.ChildId
            WHERE cte.Level < @MaxNetworkLevel
        ),
        SubordinateBalances AS (
            SELECT 
                cte.ParentId AS UserId,
                SUM(b.TotalBalances) AS TotalSubordinateBalances
            FROM SubordinateCTE cte
            INNER JOIN #Balances b ON b.UserId = cte.ChildId
            GROUP BY cte.ParentId
        )
        UPDATE b
        SET b.SubordinateBalances = ISNULL(sb.TotalSubordinateBalances, 0)
        FROM #Balances b
        LEFT JOIN SubordinateBalances sb ON sb.UserId = b.UserId
        OPTION (MAXRECURSION 100);

        -- =============================================
        -- 11. Insert نتایج در جدول اصلی
        -- =============================================
        INSERT INTO CMS.NetworkWeeklyBalances (
            UserId,
            WeekDefinitionId,
            PackageId,
            LeftLegNewMembers,
            RightLegNewMembers,
            LeftLegCarryover,
            RightLegCarryover,
            LeftLegTotal,
            RightLegTotal,
            TotalBalances,
            LeftLegRemainder,
            RightLegRemainder,
            FlushedPerSide,
            TotalFlushed,
            SubordinateBalances,
            LeftLegBalances,
            RightLegBalances,
            WeeklyPoolContribution,
            CalculatedAt,
            IsExpired,
            IsDeleted,
            Created,
            CreatedBy,
            LastModified,
            LastModifiedBy
        )
        SELECT 
            UserId,
            @WeekDefinitionId,
            @PackageId,
            LeftLegNewMembers,
            RightLegNewMembers,
            LeftLegCarryover,
            RightLegCarryover,
            LeftLegTotal,
            RightLegTotal,
            TotalBalances,
            LeftLegRemainder,
            RightLegRemainder,
            FlushedPerSide,
            TotalFlushed,
            SubordinateBalances,
            LeftLegTotal,  -- LeftLegBalances (deprecated)
            RightLegTotal, -- RightLegBalances (deprecated)
            0,             -- WeeklyPoolContribution
            @CalculatedAt,
            0,             -- IsExpired
            0,             -- IsDeleted
            @CalculatedAt, -- Created
            'SP',          -- CreatedBy
            @CalculatedAt, -- LastModified
            'SP'           -- LastModifiedBy
        FROM #Balances
        WHERE LeftLegNewMembers <> 0
           OR RightLegNewMembers <> 0
           OR LeftLegCarryover <> 0
           OR RightLegCarryover <> 0
           OR LeftLegTotal <> 0
           OR RightLegTotal <> 0
           OR TotalBalances <> 0
           OR LeftLegRemainder <> 0
           OR RightLegRemainder <> 0
           OR FlushedPerSide <> 0
           OR TotalFlushed <> 0
           OR SubordinateBalances <> 0;

        SET @RowCount = @@ROWCOUNT;

        -- پاکسازی
        DROP TABLE #RightNewMembers;
        DROP TABLE #LeftNewMembers;
        DROP TABLE #Balances;

        COMMIT TRANSACTION;

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

        -- پاکسازی در صورت خطا
        IF OBJECT_ID('tempdb..#RightNewMembers') IS NOT NULL
            DROP TABLE #RightNewMembers;

        IF OBJECT_ID('tempdb..#LeftNewMembers') IS NOT NULL
            DROP TABLE #LeftNewMembers;

        IF OBJECT_ID('tempdb..#Balances') IS NOT NULL
            DROP TABLE #Balances;

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