public async Task<(List<GetUserNotificationListingModel> data, int totalCount)> GetUserNotification(int statusId, int page, int pageSize)
 {
     var userId = (await GetUser()).Id;
     var result = RunSqlQuery((connection) =>
     {
         var offset = Offset(page, pageSize);
         const string sqlCount = @"
                                     SELECT COUNT(*) 
                                     FROM dbo.""UserNotifications""
                                     WHERE 
                                         ""UserId"" = @UserId AND ""StatusId"" = @StatusId;
                                   ";

         const string sql = @" SELECT 
                                     ""Id"",
                                     ""Title"",
                                     ""Content"",
                                     ""Url"", 
                                     ""DateCreated"" AS ""CreatedAt"",
                                     ns.""Name"" AS ""Status""
                                 FROM dbo.""UserNotifications"" un
                                 LEFT JOIN dbo.""NotificationStatuses"" ns ON un.""StatusId"" = ns.""Value""
                                 WHERE 
                                      ""UserId"" = @UserId AND ""StatusId"" = @StatusId
                                 ORDER BY ""DateCreated"" DESC
                                 LIMIT @PageSize OFFSET @Offset;
         ";
         var multiQueryResult =  connection.QueryMultiple($"{sqlCount} {sql}", new { UserId = userId, PageSize = pageSize, Offset = offset, StatusId = statusId });
         var totalRowCounts = multiQueryResult.Read<int>().Single();
         var result = multiQueryResult.Read<GetUserNotificationListingModel>().ToList();
         return (result?.ToList() ?? [], totalRowCounts);
     });
     return result;
 }
    
	
	
	
	
	
	  public async Task<(List<GetUserNotificationListingModel> data, int totalCount)> GetUserNotification(int? statusId, int page, int pageSize,
      string sortBy = "DateCreated", string sortOrder = "DESC")
  {
      var userId = (await GetUser()).Id;

      
      var allowedSortBy = new[] { "DateCreated", "StatusId" };
      var allowedSortOrder = new[] { "ASC", "DESC" };

      sortBy = allowedSortBy.Contains(sortBy) ? sortBy : "DateCreated";
      sortOrder = allowedSortOrder.Contains(sortOrder.ToUpper()) ? sortOrder.ToUpper() : "DESC";

      var result = RunSqlQuery((connection) =>
      {
          var offset = Offset(page, pageSize);
          var filterClause = statusId.HasValue ? @"AND un.""StatusId"" = @StatusId" : "";
          var sqlCount = $@"
                                      SELECT COUNT(*) 
                                      FROM dbo.""UserNotifications"" un 
                                      WHERE 
                                          un.""UserId"" = @UserId {filterClause}
                                    ";

          var sql = $@" SELECT 
                                      un.""Id"",
                                      un.""Title"",
                                     un.""Content"",
                                     un.""Url"", 
                                      un.""DateCreated"" AS ""CreatedAt"",
                                      ns.""Name"" AS ""Status""
                                  FROM dbo.""UserNotifications"" un
                                  LEFT JOIN dbo.""NotificationStatuses"" ns ON un.""StatusId"" = ns.""Value""
                                  WHERE 
                                       un.""UserId"" = @UserId {filterClause}
                                  ORDER BY un.""{sortBy}"" {sortOrder}
                                  LIMIT @PageSize OFFSET @Offset;
          ";

          var parameters = new
          {
              UserId = userId,
              PageSize = pageSize,
              Offset = offset,
              StatusId = statusId
            
          };
          var multiQueryResult =  connection.QueryMultiple($"{sqlCount} {sql}", parameters);
          var totalRowCounts = multiQueryResult.Read<int>().Single();
          var result = multiQueryResult.Read<GetUserNotificationListingModel>().ToList();
          return (result?.ToList() ?? [], totalRowCounts);
      });
      return result;
  }
  
  
  
  
  Notification service
  
  
  using Google.Protobuf.WellKnownTypes;
using NscEcms.Aggregator.Common.OutputModels;
using NscEcms.Notification.Grpc;
using NscEcms.Startup;
using NscEcms.Startup.ApiBaseResponses;
using NscEcms.Util;
using NscEcms.Util.ApiConnectors;
using NscEcms.Util.Enums;
using NscEcms.Util.Exceptions;

namespace Aggregator.Web.Services.Implementations;

internal sealed class NotificationService(
    NotificationGrpcApi.NotificationGrpcApiClient client,
    ITokenService tokenService) : AggregatorServiceBase(tokenService), INotificationService
{
    private readonly int _deadLine = 45;

    public async Task<ApiResponse<PaginatedResult<GetUserNotificationListingModel>>> GetReadNotifications(BasePaginationRequestModel request)
    {
        var command = new GetNotificationPaginationGrpcCommand
        {
            Page = request.Page,
            PageSize = request.PageSize
        };
        var response = await client.GetReadNotificationsAsync(command, await GetAuthMetaData(), deadline: DateTime.UtcNow.AddSeconds(_deadLine));
        var contentRes = response.Content.Unpack<GetPaginatedNotificationMessage>();
        var paginatedResult = new PaginatedResult<GetUserNotificationListingModel>
        {
            Page = request.Page,
            PageSize = request.PageSize,
            Items = contentRes.Notifications.Select(x => new GetUserNotificationListingModel
            {
                Id = x.Id.ToGuid(),
                Title = x.Title,
                Content = x.Content,
                Url = x.Url,
                CreatedAt = x.CreatedAt.ToDateTime(),
                Status = x.Status,
            }).ToList(),
            TotalRowCount = contentRes.TotalRowCount
        }; 
        return new ApiResponse<PaginatedResult<GetUserNotificationListingModel>>(paginatedResult, response.Message);
    }




    public async Task<ApiResponse<PaginatedResult<GetUserNotificationListingModel>>> GetUnReadNotifications(BasePaginationRequestModel request)
    {
        var command = new GetNotificationPaginationGrpcCommand
        {
            Page = request.Page,
            PageSize = request.PageSize
        };
        var response = await client.GetUnReadNotificationsAsync(command, await GetAuthMetaData(), deadline: DateTime.UtcNow.AddSeconds(_deadLine));
        var contentRes = response.Content.Unpack<GetPaginatedNotificationMessage>();
        var paginatedResult = new PaginatedResult<GetUserNotificationListingModel>
        {
            Page = request.Page,
            PageSize = request.PageSize,
            Items = contentRes.Notifications.Select(x => new GetUserNotificationListingModel
            {
                Id = x.Id.ToGuid(),
                Title = x.Title,
                Content = x.Content,
                Url = x.Url,
                CreatedAt = x.CreatedAt.ToDateTime(),
                Status = x.Status,
            }).ToList(),
            TotalRowCount = contentRes.TotalRowCount
        }; 
        return new ApiResponse<PaginatedResult<GetUserNotificationListingModel>>(paginatedResult, response.Message);
    }

    public async Task<ApiResponse<bool>> MarkNotificationRead(Guid id)
    {
        var command = new UpdateNotificationStatusGrpcCommand
        {
            NotificationId = id.ToString(),
            StatusId = NotificationStatus.Read
        };
        var response = await client.UpdateInAppNotificationStatusAsync(command, await GetAuthMetaData(),
            deadline: DateTime.UtcNow.AddSeconds(_deadLine));
        var contentRes = response.Content.Unpack<BoolValue>();
        if (!contentRes.Value)
            throw new UserMessageDomainException(response.Message);

        return new ApiResponse<bool>(contentRes.Value, response.Message);
    }

    public async Task<ApiResponse<bool>> MarkNotificationUnRead(Guid id)
    {
        var command = new UpdateNotificationStatusGrpcCommand
        {
            NotificationId = id.ToString(),
            StatusId = NotificationStatus.UnRead
        };
        var response = await client.UpdateInAppNotificationStatusAsync(command, await GetAuthMetaData(),
            deadline: DateTime.UtcNow.AddSeconds(_deadLine));
        var contentRes = response.Content.Unpack<BoolValue>();
        if (!contentRes.Value)
            throw new UserMessageDomainException(response.Message);
        
        return new ApiResponse<bool>(contentRes.Value, response.Message);
    }
}



notification gprs service



using Google.Protobuf.WellKnownTypes;
using Grpc.Core;
using MediatR;
using Notification.Api.Application.Commands;
using Notification.Api.Application.Queries;
using Notification.Api.Domain.AggregateModel.UserNotificationAggregate;
using NscEcms.Notification.Grpc;
using NscEcms.Protos;
using NscEcms.Util;
using NscEcms.Util.Enums;

namespace Notification.Api.Application.Grpc.Services;

public class NotificationGrpcService(IMediator mediator, IUserNotificationQueries notificationQueries) : NotificationGrpcApi.NotificationGrpcApiBase
{ 

    public override async Task<ServiceResponseMessage> GetUnReadNotifications(GetNotificationPaginationGrpcCommand request, ServerCallContext context)
    {
        var unreadStatusId = NotificationStatus.UnRead.Value;
        var (data, totalCount) = await notificationQueries.GetUserNotification(unreadStatusId, request.Page, request.PageSize);
        var userNotificationResult = new GetPaginatedNotificationMessage
        {
            Notifications = { },
            TotalRowCount = totalCount
        };
        data.ForEach(x =>
        {
            userNotificationResult.Notifications.Add(new GetNotificationListingMessage
            {
                Content = x.Content.ToEmptyStringIfEmpty(),
                Title = x.Title.ToEmptyStringIfEmpty(), 
                Id = x.Id.ToString(),
                CreatedAt = Timestamp.FromDateTime(x.CreatedAt.ToUniversalTime()),
                Url = x.Url.ToEmptyStringIfEmpty(),
                Status = x.Status.ToString(),
                
            });
        });
        return new ServiceResponseMessage
        {
            Message = !userNotificationResult.IsDefault() ? "User notification records retrieved successfully." : $"Unable to retrieve user notification records.",
            Content = Any.Pack(userNotificationResult)
        };
    }
    
    public override async Task<ServiceResponseMessage> GetReadNotifications(GetNotificationPaginationGrpcCommand request, ServerCallContext context)
    {
        var readStatusId = NotificationStatus.Read.Value;
        var (data, totalCount) = await notificationQueries.GetUserNotification(readStatusId, request.Page, request.PageSize);
        var userNotificationResult = new GetPaginatedNotificationMessage
        {
            Notifications = {  },
            TotalRowCount = totalCount
        };
        data.ForEach(x =>
        {
            userNotificationResult.Notifications.Add(new GetNotificationListingMessage
            {
                Content = x.Content.ToEmptyStringIfEmpty(),
                Title = x.Title.ToEmptyStringIfEmpty(), 
                Id = x.Id.ToString(),
                CreatedAt = Timestamp.FromDateTime(x.CreatedAt.ToUniversalTime()),
                Url = x.Url.ToEmptyStringIfEmpty(),
                Status = x.Status.ToString(),
                
            });
        });
        return new ServiceResponseMessage
        {
            Message = !userNotificationResult.IsDefault() ? "User notification records retrieved successfully." : $"Unable to retrieve user notification records.",
            Content = Any.Pack(userNotificationResult)
        };
    }

    public override async Task<ServiceResponseMessage> UpdateInAppNotificationStatus(UpdateNotificationStatusGrpcCommand request, ServerCallContext context)
    {
        var command = new UpdateUserNotificationStatusCommand(request.NotificationId.ToGuid(), request.StatusId);
        var result = await mediator.Send(command);

        return new ServiceResponseMessage
        {
            Message = result ? "Notification status updated successfully." : "Failed to update notification status.",
            Content = Any.Pack(result.MapBooleanMessage())
        };
    }
}


SELECT COUNT(*) 
FROM dbo.""UserNotifications"" 
WHERE 
    ""UserId"" = @UserId AND ""StatusId"" = @StatusId
             ";