-- Part 5: production controls
-- Compose after: content/series/durable-work-postgres/source/schema.sql.
-- Run from worker housekeeping, not as a schema migration.

BEGIN;
SELECT pg_try_advisory_xact_lock(847291) AS acquired;

UPDATE workers
SET status = 'dead'
WHERE status IN ('alive', 'draining')
  AND last_seen_at < now() - interval '30 seconds';

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',
    last_error = COALESCE(last_error, 'max attempts during lease cleanup')
WHERE status = 'processing'
  AND lease_expires_at < now()
  AND attempts >= max_attempts;

COMMIT;
