-- Phase 2b — durable job queue for async handlers (shell, script, ollama).
-- Voir ~/.claude/plans/pour-les-notifications-on-inherited-seal.md § Phase 2.
CREATE TABLE IF NOT EXISTS gateway_jobs (
  id            BIGSERIAL    PRIMARY KEY,
  bot_slug      TEXT         NOT NULL,
  handler_type  TEXT         NOT NULL,
  update_id     BIGINT       NOT NULL,
  payload       JSONB        NOT NULL,
  attempts      INT          NOT NULL DEFAULT 0,
  next_retry_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  status        TEXT         NOT NULL DEFAULT 'pending',
  last_error    TEXT,
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  -- idempotence : si Telegram redélivre (ack > 60s), on ignore le doublon.
  CONSTRAINT gateway_jobs_uniq_update UNIQUE (bot_slug, update_id)
);

CREATE INDEX IF NOT EXISTS gateway_jobs_pickable
  ON gateway_jobs (next_retry_at)
  WHERE status = 'pending';

CREATE INDEX IF NOT EXISTS gateway_jobs_status
  ON gateway_jobs (status);