-- Part 2: lease cleanup for crashed workers
-- Compose after: 01-claim-loop/source.sql or content/series/durable-work-postgres/source/schema.sql.
-- This file adds recovery/complete statements for the shared inbox table.

UPDATE inbox
SET status = 'pending',
    claimed_by = NULL,
    lease_expires_at = NULL,
    available_at = now() + (LEAST(POWER(2, attempts)::int, 3600) * interval '1 second')
WHERE status = 'processing'
  AND lease_expires_at < now()
  AND attempts < max_attempts;

UPDATE inbox
SET status = 'dead_letter'
WHERE status = 'processing'
  AND lease_expires_at < now()
  AND attempts >= max_attempts;

UPDATE inbox
SET status = 'completed'
WHERE id = $1
  AND claimed_by = $2
  AND lease_generation = $3
  AND status = 'processing'
  AND lease_expires_at > now();
