-- =============================================
-- Stored Procedure: sp_GetUserWeeklyBalances
-- Description: دریافت تعادل‌های هفتگی با صفحه‌بندی و فیلترهای مختلف
-- Author: System
-- Created: 2026-05-04
-- =============================================

CREATE OR ALTER PROCEDURE [CMS].[sp_GetUserWeeklyBalances]
    @UserId           BIGINT = NULL,
    @WeekDefinitionId BIGINT = NULL,
    @PackageId        BIGINT = NULL,
    @OnlyActive       BIT    = 0,
    @SortDescending   BIT    = 1,   -- 1 = نزولی (جدیدترین اول)، 0 = صعودی
    @PageNumber       INT    = 1,
    @PageSize         INT    = 10
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Skip INT = @PageSize * (@PageNumber - 1);

    SELECT
        n.Id,
        n.UserId,
        LTRIM(RTRIM(ISNULL(u.FirstName, '') + ' ' + ISNULL(u.LastName, ''))) AS UserFullName,
        n.WeekDefinitionId,
        ISNULL(wd.DisplayName, '')  AS WeekDisplayName,
        n.LeftLegNewMembers,
        n.LeftLegCarryover,
        n.LeftLegTotal,
        n.RightLegNewMembers,
        n.RightLegCarryover,
        n.RightLegTotal,
        n.TotalBalances,
        n.WeeklyPoolContribution,
        n.CalculatedAt,
        n.IsExpired,
        CAST(n.Created AS datetimeoffset) AS Created,
        n.PackageId,
        ISNULL(p.Title, '')         AS PackageTitle,
        COUNT(*) OVER()             AS TotalCount
    FROM [CMS].[NetworkWeeklyBalances] n
    LEFT JOIN [CMS].[Users]           u  ON u.Id  = n.UserId
    LEFT JOIN [CMS].[WeekDefinitions] wd ON wd.Id = n.WeekDefinitionId
    LEFT JOIN [CMS].[Packages]        p  ON p.Id  = n.PackageId
    WHERE n.IsDeleted = 0
      AND (@UserId           IS NULL OR n.UserId           = @UserId)
      AND (@WeekDefinitionId IS NULL OR n.WeekDefinitionId = @WeekDefinitionId)
      AND (@PackageId        IS NULL OR n.PackageId        = @PackageId)
      AND (@OnlyActive       = 0     OR n.IsExpired        = 0)
    ORDER BY
        CASE WHEN @SortDescending = 1 THEN n.WeekDefinitionId END DESC,
        CASE WHEN @SortDescending = 0 THEN n.WeekDefinitionId END ASC
    OFFSET @Skip ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;
