CREATE schema if not exists oauth;

CREATE TABLE if not exists oauth.clients (
  client_id              text PRIMARY KEY DEFAULT next_id(),   -- ID клієнта (публічний ідентифікатор)
  client_secret_hash     text,                                 -- Хеш секрету (NULL для public-клієнтів)
  name                   text NOT NULL,                        -- Назва застосунку
  type                   text NOT NULL CHECK (type IN ('public','confidential')),
  token_endpoint_auth_method text NOT NULL CHECK (token_endpoint_auth_method IN ('client_secret_basic','client_secret_post','private_key_jwt','none')),
  owner_user_id          text,                                 -- Власник/адміністратор клієнта (посилання на users.id or other id)

  redirect_uris          text[],         					   -- Дозволені redirect_uri  
  grant_types            text[]  CHECK (case when grant_types is not null then grant_types <@ ARRAY['authorization_code','refresh_token','client_credentials','device_code']::text[] else true end),
  require_pkce           boolean NOT NULL DEFAULT true,
  scopes         text[],  
  allowed_cors_origins   text[],
  jwks                   jsonb,                                -- Вбудований JWK Set (опційно)  

  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE if not exists oauth.tokens (
  id                     text PRIMARY KEY DEFAULT next_id(),
  token_type             text NOT NULL CHECK (token_type IN ('access','refresh')),
  token_hash             text NOT NULL UNIQUE,                  -- Argon2/bcrypt/SCrypt (хеш у застосунку)
  token_hint             text,                                  -- останні 6-8 символів для діагностики (необов’язково)
  jti                    text UNIQUE,                           -- JWT ID, якщо токен — JWT
  client_id              text NOT NULL REFERENCES oauth.clients(client_id) ON DELETE CASCADE,
  user_id                text,                                  -- NULL для client_credentials    
  issuer                 text,                                  -- iss
  scopes                 text[],
  claims                 jsonb,                                 -- додаткові клейми
  issued_at              timestamptz NOT NULL DEFAULT now(),
  expires_at             timestamptz NOT NULL,
  revoked_at             timestamptz,
  revocation_reason      text,
  ip                     inet                                  -- IP видачі/використання (опційно)  
);

COMMENT ON SCHEMA oauth IS 'Schema for OAuth2 / OpenID Connect clients and tokens';

-- Comments for oauth.clients
COMMENT ON TABLE oauth.clients IS 'OAuth 2.0 clients (applications) that can request tokens';

COMMENT ON COLUMN oauth.clients.client_id IS 'Client identifier (public ID, generated by next_id())';
COMMENT ON COLUMN oauth.clients.client_secret_hash IS 'Hashed client secret (NULL for public clients)';
COMMENT ON COLUMN oauth.clients.name IS 'Name of the application/client';
COMMENT ON COLUMN oauth.clients.type IS 'Client type: public or confidential';
COMMENT ON COLUMN oauth.clients.token_endpoint_auth_method IS 'Authentication method at token endpoint (client_secret_basic, client_secret_post, private_key_jwt, none)';
COMMENT ON COLUMN oauth.clients.owner_user_id IS 'Owner/administrator of the client (reference to users.id or external id)';
COMMENT ON COLUMN oauth.clients.redirect_uris IS 'Allowed redirect URIs';
COMMENT ON COLUMN oauth.clients.grant_types IS 'Allowed grant types (authorization_code, refresh_token, client_credentials, device_code)';
COMMENT ON COLUMN oauth.clients.require_pkce IS 'Whether PKCE is required (default true)';
COMMENT ON COLUMN oauth.clients.scopes IS 'Allowed OAuth2 scopes';
COMMENT ON COLUMN oauth.clients.allowed_cors_origins IS 'Allowed CORS origins for browser-based apps';
COMMENT ON COLUMN oauth.clients.jwks IS 'Embedded JSON Web Key Set (optional)';
COMMENT ON COLUMN oauth.clients.created_at IS 'Creation timestamp';
COMMENT ON COLUMN oauth.clients.updated_at IS 'Last update timestamp';

-- Comments for oauth.tokens
COMMENT ON TABLE oauth.tokens IS 'Issued OAuth 2.0 tokens (access or refresh)';

COMMENT ON COLUMN oauth.tokens.id IS 'Internal token ID (generated by next_id())';
COMMENT ON COLUMN oauth.tokens.token_type IS 'Type of token: access or refresh';
COMMENT ON COLUMN oauth.tokens.token_hash IS 'Secure hash of the token (Argon2/bcrypt/SCrypt)';
COMMENT ON COLUMN oauth.tokens.token_hint IS 'Optional hint (last 6–8 characters of token) for diagnostics';
COMMENT ON COLUMN oauth.tokens.jti IS 'JWT ID if token is a JWT (unique)';
COMMENT ON COLUMN oauth.tokens.client_id IS 'Reference to oauth.clients (issuing client)';
COMMENT ON COLUMN oauth.tokens.user_id IS 'User ID if bound to user (NULL for client_credentials flow)';
COMMENT ON COLUMN oauth.tokens.issuer IS 'Token issuer (iss claim)';
COMMENT ON COLUMN oauth.tokens.scopes IS 'Granted OAuth2 scopes for this token';
COMMENT ON COLUMN oauth.tokens.claims IS 'Additional claims (JSONB)';
COMMENT ON COLUMN oauth.tokens.issued_at IS 'Timestamp when issued';
COMMENT ON COLUMN oauth.tokens.expires_at IS 'Timestamp when token expires';
COMMENT ON COLUMN oauth.tokens.revoked_at IS 'Timestamp when revoked';
COMMENT ON COLUMN oauth.tokens.revocation_reason IS 'Reason for revocation (if any)';
COMMENT ON COLUMN oauth.tokens.ip IS 'IP address of issuance/usage (optional)';