CREATE TABLE IF NOT EXISTS gateway.identity_providers (
    id uuid PRIMARY KEY,
    code text NOT NULL UNIQUE,
    kind varchar(16) NOT NULL CHECK (kind IN ('oidc')),
    display_name varchar(128) NOT NULL,
    issuer_url text NOT NULL,
    client_id text NOT NULL,
    encrypted_credentials bytea NOT NULL,
    credential_kek_version integer NOT NULL,
    redirect_uri text NOT NULL,
    portal_return_url text NOT NULL,
    scopes text[] NOT NULL DEFAULT ARRAY['openid', 'profile', 'email'],
    auto_provision boolean NOT NULL DEFAULT false,
    default_department_id uuid REFERENCES gateway.departments(id),
    enabled boolean NOT NULL DEFAULT false,
    revision bigint NOT NULL DEFAULT 1,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

ALTER TABLE gateway.portal_users
    ADD COLUMN IF NOT EXISTS identity_provider_id uuid
        REFERENCES gateway.identity_providers(id);

DROP INDEX IF EXISTS gateway.portal_users_external_subject_idx;
CREATE UNIQUE INDEX IF NOT EXISTS portal_users_provider_subject_idx
    ON gateway.portal_users (identity_provider_id, external_subject)
    WHERE identity_provider_id IS NOT NULL AND external_subject IS NOT NULL;

COMMENT ON TABLE gateway.identity_providers IS
    'OIDC provider registry. Client secrets are encrypted with a dedicated AEAD purpose.';