SELECT [Id],
       [WorkspaceId],
       [Email],
       [DisplayName],
       [IsActive],
       [TimeZone],
       [AvailabilityStart],
       [AvailabilityEnd],
       [VacationStart],
       [VacationEnd],
       [IsOutOfOffice]
FROM
(
    SELECT [Id],
           [WorkspaceId],
           [Email],
           [DisplayName],
           [IsActive],
           [TimeZone],
           [AvailabilityStart],
           [AvailabilityEnd],
           [VacationStart],
           [VacationEnd],
           [IsOutOfOffice]
    FROM [dbo].[WorkspaceReviewers]
    WHERE [WorkspaceId] = @WorkspaceId

    UNION ALL

    SELECT [UserId] AS [Id],
           [WorkspaceId],
           [Email],
           COALESCE(NULLIF(LTRIM(RTRIM(CONCAT(COALESCE([FirstName], ''), ' ', COALESCE([LastName], '')))), ''), [Email]) AS [DisplayName],
           CAST(1 AS bit) AS [IsActive],
           'UTC' AS [TimeZone],
           NULL AS [AvailabilityStart],
           NULL AS [AvailabilityEnd],
           NULL AS [VacationStart],
           NULL AS [VacationEnd],
           CAST(0 AS bit) AS [IsOutOfOffice]
    FROM [dbo].[WorkspaceMembers] AS wm
    WHERE [WorkspaceId] = @WorkspaceId
      AND [Status] = 1
      AND [Role] IN (0, 1)
      AND NOT EXISTS
      (
          SELECT 1
          FROM [dbo].[WorkspaceReviewers] AS wr
          WHERE wr.[WorkspaceId] = wm.[WorkspaceId]
            AND wr.[Email] = wm.[Email]
      )
) AS [Combined]
ORDER BY [Email];
