DECLARE
@SortColumn NVARCHAR(20) = CASE
                                   WHEN @SortField IN ('QueuedAt', 'Status', 'AssignedAgent') THEN @SortField
                                   ELSE 'QueuedAt'
END;
DECLARE
@SortDirection NVARCHAR(4) = CASE
                                     WHEN LOWER(@SortDir) = 'asc' THEN 'ASC'
                                     ELSE 'DESC'
END;

WITH Base AS
         (SELECT te.[Id],
                 te.[ExecutionMode],
                 te.[WorkKind],
                 te.[ExecutionType],
                 te.[AssignedAgent],
                 te.[QueuedAt],
                 te.[StartedAt],
                 te.[FinishedAt],
                 te.[Status],
                 te.[InputDataJson],
                 te.[OutputDataJson],
                 te.[OutputDataRef],
                 te.[ErrorMessage],
                 te.[CorrelationId],
                 te.[ReplayedFromTaskId],
                 te.[WorkspaceId],
                 te.[AssignmentType],
                 te.[ConfidenceScore],
                 te.[RetryCount],
                 te.[LastRetryAt],
                 te.[MaxRetryAttempts],
                 te.[RetryReason],
                 te.[Priority],
                 te.[RetryOfTaskId],
                 te.[SuggestionLogId],
                 te.[CreatedAt],
                 te.[SuggestedPatchJson],
                 te.[AiReviewEvidenceManifestJson],
                 te.[AiReviewStatus],
                 te.[CreatedVia],
                 te.[Language], te.[Domain], te.[RepositoryUrl], te.[Branch], te.[FilePath], te.[Prompt], te.[OutputFormat], te.[TriggerSource], te.[ExternalId], te.[ExternalSystem], te.[RowVersion], ts.[Name] AS [StatusName]
FROM [dbo].[TaskExecutions] te
    INNER JOIN [dbo].[TaskExecutionStatuses] ts
ON te.[Status] = ts.[Id]
WHERE (@WorkspaceId IS NULL
   OR te.[WorkspaceId] = @WorkspaceId)
  AND (@Search IS NULL
   OR CAST (te.[Id] AS NVARCHAR(36)) LIKE '%' + @Search + '%'
   OR te.[AssignedAgent] LIKE '%' + @Search + '%'
   OR te.[ExternalId] LIKE '%' + @Search + '%'
   OR te.[Prompt] LIKE '%' + @Search + '%'
   OR EXISTS (SELECT 1
    FROM [dbo].[TaskExecutionMetadata] ctx
    WHERE ctx.[TaskExecutionId] = te.[Id]
  AND ctx.[Key] = @ContextKey
  AND ctx.[Value] LIKE '%' + @Search + '%'))
  AND (@AgentName IS NULL
   OR te.[AssignedAgent] = @AgentName)
  AND (@Status IS NULL
   OR te.[Status] = @Status)
  AND (@Since IS NULL
   OR te.[CreatedAt] >= @Since)
  AND (@Until IS NULL
   OR te.[CreatedAt] <= @Until)
  AND (@TriggerSources IS NULL
   OR te.[TriggerSource] IN @TriggerSources)
  AND (@IncludeRetries = 1
   OR te.[RetryOfTaskId] IS NULL)
  AND (@IncludeReplays = 1
   OR te.[ReplayedFromTaskId] IS NULL)
  AND (
    @IncludeDismissed = 1
   OR NOT EXISTS (SELECT 1
    FROM [dbo].[TaskExecutionMetadata] tem
    WHERE tem.[TaskExecutionId] = te.[Id]
  AND tem.[Key] = @DismissedKey)
    ))
    , Ordered AS
    (
SELECT b.*, ROW_NUMBER() OVER (
    ORDER BY
    CASE WHEN @SortColumn = 'QueuedAt' AND @SortDirection = 'ASC' THEN b.[QueuedAt] END, CASE
    WHEN @SortColumn = 'QueuedAt' AND @SortDirection = 'DESC'
    THEN b.[QueuedAt] END DESC, CASE
    WHEN @SortColumn = 'Status' AND @SortDirection = 'ASC'
    THEN b.[StatusName] END, CASE
    WHEN @SortColumn = 'Status' AND @SortDirection = 'DESC'
    THEN b.[StatusName] END DESC, CASE
    WHEN @SortColumn = 'AssignedAgent' AND @SortDirection = 'ASC'
    THEN b.[AssignedAgent] END, CASE
    WHEN @SortColumn = 'AssignedAgent' AND @SortDirection = 'DESC'
    THEN b.[AssignedAgent] END DESC, b.[QueuedAt] DESC, b.[Id]
    ) AS RowNum
FROM Base b)
SELECT o.[Id],
       o.[ExecutionMode],
       o.[WorkKind],
       o.[ExecutionType],
       o.[AssignedAgent],
       o.[QueuedAt],
       o.[StartedAt],
       o.[FinishedAt],
       o.[Status],
       o.[InputDataJson],
       o.[OutputDataJson],
       o.[OutputDataRef],
       o.[ErrorMessage],
       o.[CorrelationId],
       o.[ReplayedFromTaskId],
       o.[WorkspaceId],
       o.[AssignmentType],
       o.[ConfidenceScore],
       o.[RetryCount],
       o.[LastRetryAt],
       o.[MaxRetryAttempts],
       o.[RetryReason],
       o.[Priority],
       o.[RetryOfTaskId],
       o.[SuggestionLogId],
       o.[CreatedAt],
       o.[SuggestedPatchJson],
       o.[AiReviewEvidenceManifestJson],
       o.[AiReviewStatus],
       o.[CreatedVia],
       o.[Language], o.[Domain], o.[RepositoryUrl], o.[Branch], o.[FilePath], o.[Prompt], o.[OutputFormat], o.[TriggerSource], o.[ExternalId], o.[ExternalSystem], o.[RowVersion]
FROM Ordered o
WHERE o.RowNum BETWEEN ((@Page - 1) * @PageSize) + 1 AND @Page * @PageSize
ORDER BY o.RowNum;
;
WITH Base AS
         (SELECT te.[Id],
                 te.[ExecutionMode],
                 te.[WorkKind],
                 te.[ExecutionType],
                 te.[AssignedAgent],
                 te.[QueuedAt],
                 te.[StartedAt],
                 te.[FinishedAt],
                 te.[Status],
                 te.[InputDataJson],
                 te.[OutputDataJson],
                 te.[OutputDataRef],
                 te.[ErrorMessage],
                 te.[CorrelationId],
                 te.[ReplayedFromTaskId],
                 te.[WorkspaceId],
                 te.[AssignmentType],
                 te.[ConfidenceScore],
                 te.[RetryCount],
                 te.[LastRetryAt],
                 te.[MaxRetryAttempts],
                 te.[RetryReason],
                 te.[Priority],
                 te.[RetryOfTaskId],
                 te.[SuggestionLogId],
                 te.[CreatedAt],
                 te.[SuggestedPatchJson],
                 te.[AiReviewEvidenceManifestJson],
                 te.[AiReviewStatus],
                 te.[CreatedVia],
                 te.[Language], te.[Domain], te.[RepositoryUrl], te.[Branch], te.[FilePath], te.[Prompt], te.[OutputFormat], te.[TriggerSource], te.[ExternalId], te.[ExternalSystem], te.[RowVersion]
FROM [dbo].[TaskExecutions] te
WHERE (@WorkspaceId IS NULL
   OR te.[WorkspaceId] = @WorkspaceId)
  AND (@Search IS NULL
   OR CAST (te.[Id] AS NVARCHAR(36)) LIKE '%' + @Search + '%'
   OR te.[AssignedAgent] LIKE '%' + @Search + '%'
   OR te.[ExternalId] LIKE '%' + @Search + '%'
   OR te.[Prompt] LIKE '%' + @Search + '%'
   OR EXISTS (SELECT 1
    FROM [dbo].[TaskExecutionMetadata] ctx
    WHERE ctx.[TaskExecutionId] = te.[Id]
  AND ctx.[Key] = @ContextKey
  AND ctx.[Value] LIKE '%' + @Search + '%'))
  AND (@AgentName IS NULL
   OR te.[AssignedAgent] = @AgentName)
  AND (@Status IS NULL
   OR te.[Status] = @Status)
  AND (@Since IS NULL
   OR te.[CreatedAt] >= @Since)
  AND (@Until IS NULL
   OR te.[CreatedAt] <= @Until)
  AND (@TriggerSources IS NULL
   OR te.[TriggerSource] IN @TriggerSources)
  AND (@IncludeRetries = 1
   OR te.[RetryOfTaskId] IS NULL)
  AND (@IncludeReplays = 1
   OR te.[ReplayedFromTaskId] IS NULL)
  AND (
    @IncludeDismissed = 1
   OR NOT EXISTS (SELECT 1
    FROM [dbo].[TaskExecutionMetadata] tem
    WHERE tem.[TaskExecutionId] = te.[Id]
  AND tem.[Key] = @DismissedKey)
    ))
SELECT COUNT(1)
FROM Base;
