-- Migration: 035-add-oidc-schema.sql
-- Description: Add OIDC grants and clients tables for oidc-provider
-- Date: 2026-06-13

-- OIDC Grants table (stores AuthorizationCode, RefreshToken, AccessToken, Interaction references)
CREATE TABLE IF NOT EXISTS oidc_grants (
    id VARCHAR(128) NOT NULL,
    model VARCHAR(64) NOT NULL,
    payload JSONB NOT NULL,
    expires_at TIMESTAMPTZ,
    consumed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (model, id)
);

CREATE INDEX IF NOT EXISTS idx_oidc_grants_expires_at ON oidc_grants (expires_at);
CREATE INDEX IF NOT EXISTS idx_oidc_grants_payload_uid ON oidc_grants ((payload->>'uid'));
CREATE INDEX IF NOT EXISTS idx_oidc_grants_payload_user_code ON oidc_grants ((payload->>'userCode'));
CREATE INDEX IF NOT EXISTS idx_oidc_grants_payload_grant_id ON oidc_grants ((payload->>'grantId'));

COMMENT ON TABLE oidc_grants IS 'Stores OIDC grants: AuthorizationCode, RefreshToken, AccessToken, Interaction';
