-- Migration: 044-add-oidc-context-to-email-verify-tokens.sql
-- Description: Add oidc_uid, oidc_client_id, oidc_redirect_uri columns to email_verify_tokens
--             for Flow C — auto-login after email verification
-- Date: 2026-06-25
-- Usage: Run via `docker exec sso-postgres psql -U postgres -d sso -f /tmp/migration.sql`
-- Note: Container DB name is `sso` (not `vietprodev_sso`).
--       If your DB is `vietprodev_sso`, replace `-d sso` with `-d vietprodev_sso`.

ALTER TABLE email_verify_tokens
ADD COLUMN IF NOT EXISTS oidc_uid         VARCHAR(255),
ADD COLUMN IF NOT EXISTS oidc_client_id   VARCHAR(255),
ADD COLUMN IF NOT EXISTS oidc_redirect_uri TEXT;

CREATE INDEX IF NOT EXISTS idx_email_verify_tokens_oidc_uid
ON email_verify_tokens(oidc_uid)
WHERE oidc_uid IS NOT NULL;
