BEGIN;

CREATE OR REPLACE FUNCTION claim_background_jobs(p_worker_id text, p_limit integer DEFAULT 25)
RETURNS SETOF background_jobs
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH candidates AS (
    SELECT id
    FROM background_jobs
    WHERE status = 'pending' AND available_at <= now()
    ORDER BY priority ASC, available_at ASC, created_at ASC
    FOR UPDATE SKIP LOCKED
    LIMIT LEAST(GREATEST(p_limit, 1), 100)
  )
  UPDATE background_jobs job
  SET status = 'processing',
      locked_at = now(),
      locked_by = p_worker_id,
      attempts = attempts + 1
  FROM candidates
  WHERE job.id = candidates.id
  RETURNING job.*;
END;
$$;

COMMIT;