-- 0pt durable AUD0 evidence outbox (§70)
-- AUD0 remains canonical evidence; this is only a durable producer queue.

CREATE TABLE IF NOT EXISTS opt_aud0_evidence_outbox (
  outbox_id          text PRIMARY KEY,
  tenant_id          text NOT NULL,
  app_id             text NOT NULL DEFAULT '0pt',
  evidence_kind      text NOT NULL CHECK (evidence_kind IN ('event', 'openlineage')),
  idempotency_key    text NOT NULL,
  payload            jsonb NOT NULL,
  status             text NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending', 'delivered', 'dead')),
  attempts           integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  next_attempt_at    timestamptz NOT NULL DEFAULT now(),
  leased_by          text,
  leased_until       timestamptz,
  last_error         text,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  delivered_at       timestamptz,
  dead_at            timestamptz,
  CONSTRAINT opt_aud0_evidence_outbox_idempotency
    UNIQUE (tenant_id, evidence_kind, idempotency_key),
  CONSTRAINT opt_aud0_evidence_outbox_delivery_consistency CHECK (
    (status = 'delivered' AND delivered_at IS NOT NULL AND dead_at IS NULL)
    OR (status = 'dead' AND dead_at IS NOT NULL AND delivered_at IS NULL)
    OR (status = 'pending' AND delivered_at IS NULL AND dead_at IS NULL)
  )
);

CREATE INDEX IF NOT EXISTS opt_aud0_evidence_outbox_pending_idx
  ON opt_aud0_evidence_outbox (next_attempt_at, created_at)
  WHERE status = 'pending';

CREATE INDEX IF NOT EXISTS opt_aud0_evidence_outbox_tenant_idx
  ON opt_aud0_evidence_outbox (tenant_id, created_at DESC);

COMMENT ON TABLE opt_aud0_evidence_outbox IS
  'Durable local delivery queue for 0pt -> AUD0 evidence. Not canonical evidence.';