-- Part 4: bucket-filtered claims
-- Compose after: content/series/durable-work-postgres/source/schema.sql.
-- This claim query assumes partition_bucket is populated at enqueue time.

CREATE INDEX idx_inbox_bucket_claim_ready
  ON inbox (partition_bucket, available_at, created_at, id)
  WHERE status = 'pending';

WITH candidates AS (
  SELECT id
  FROM inbox
  WHERE status = 'pending'
    AND available_at <= now()
    AND partition_bucket = ANY($1::int[])
  ORDER BY created_at, id
  LIMIT 25
  FOR UPDATE SKIP LOCKED
)
UPDATE inbox i
SET status = 'processing',
    claimed_by = $2,
    lease_expires_at = now() + interval '90 seconds',
    lease_generation = i.lease_generation + 1,
    attempts = i.attempts + 1
FROM candidates c
WHERE i.id = c.id
RETURNING i.*;
