-- ClientFlow v4928.1.4.4
-- Ensure legacy integration_outbox tables have all columns used by the worker/UI.
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS business_event_id UUID;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS retry_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS idempotency_key TEXT;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS last_error TEXT;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS sent_at TIMESTAMPTZ;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS locked_at TIMESTAMPTZ;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS lock_owner TEXT;
ALTER TABLE integration_outbox ADD COLUMN IF NOT EXISTS ignored_at TIMESTAMPTZ;

CREATE UNIQUE INDEX IF NOT EXISTS ux_integration_outbox_idempotency_key
ON integration_outbox(idempotency_key)
WHERE idempotency_key IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_integration_outbox_pending_dispatch
ON integration_outbox(target_system, created_at)
WHERE status = 'pending';