-- Part 1: minimal durable claim loop
-- UUIDv7 can be generated by Postgres when uuidv7() is available, or by the app.

CREATE TABLE inbox (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  partition_key text NOT NULL,
  partition_bucket int NOT NULL CHECK (partition_bucket >= 0 AND partition_bucket < 1024),
  payload jsonb NOT NULL,
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'processing', 'completed', 'dead_letter')),
  idempotency_key text,
  claimed_by text,
  lease_expires_at timestamptz,
  lease_generation bigint NOT NULL DEFAULT 0,
  attempts int NOT NULL DEFAULT 0,
  max_attempts int NOT NULL DEFAULT 5,
  available_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now()
);

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

CREATE UNIQUE INDEX idx_inbox_idempotency
  ON inbox (idempotency_key)
  WHERE idempotency_key IS NOT NULL;

INSERT INTO inbox (partition_key, partition_bucket, payload, idempotency_key)
VALUES (
  'order:9182',
  417,
  '{"type":"send_receipt","order_id":9182}'::jsonb,
  'receipt-9182-v1'
)
ON CONFLICT (idempotency_key) WHERE idempotency_key IS NOT NULL DO NOTHING;

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

