;
-- Atomically claim and return hung tasks to prevent duplicate replays
-- Uses OUTPUT clause to return claimed tasks in the same transaction
DECLARE @ClaimedTasks TABLE (Id UNIQUEIDENTIFIER);

WITH last_transition AS
         (SELECT st.TaskExecutionId, MAX(st.[Timestamp]) AS LastTs
          FROM dbo.StatusTransitions st
          GROUP BY st.TaskExecutionId),
     candidates AS
         (SELECT TOP(@Top) te.Id,
                           te.Status,
                           te.ReplayedFromTaskId,
                           COALESCE(lt.LastTs, te.QueuedAt) AS LastActivityAt
          FROM dbo.TaskExecutions te
                   LEFT JOIN last_transition lt
                             ON lt.TaskExecutionId = te.Id
          WHERE te.ReplayedFromTaskId IS NULL
            AND COALESCE(lt.LastTs, te.QueuedAt) <= @CutoffUtc
            AND te.Status IN @Statuses
            AND NOT EXISTS
              (SELECT 1
               FROM dbo.TaskExecutionMetadata m
               WHERE m.TaskExecutionId = te.Id
                 AND m.[Key] = @MetadataKey)
            AND NOT EXISTS
              (SELECT 1
               FROM dbo.TaskExecutionMetadata m
               WHERE m.TaskExecutionId = te.Id
                 AND m.[Key] = @DismissedKey)
          ORDER BY COALESCE(lt.LastTs, te.QueuedAt))

-- Atomically insert metadata for claimed tasks
INSERT INTO dbo.TaskExecutionMetadata (Id, TaskExecutionId, [Key], [Value])
OUTPUT inserted.TaskExecutionId
    INTO @ClaimedTasks
SELECT NEWID(), c.Id, @MetadataKey, @ClaimTimestamp
FROM candidates c
WHERE NOT EXISTS (
    -- Double-check metadata doesn't exist (handles race conditions)
    SELECT 1 FROM dbo.TaskExecutionMetadata m2
    WHERE m2.TaskExecutionId = c.Id AND m2.[Key] = @MetadataKey
);

SELECT
    t.Id, te.Status, te.ReplayedFromTaskId
FROM
    @ClaimedTasks t JOIN dbo.TaskExecutions te
        ON te.Id = t.Id;
