-- OAuth2 客户端与令牌表（Authorization Code + PKCE，opaque access/refresh token）
-- 依赖 001_iam.sql

BEGIN;

CREATE TABLE IF NOT EXISTS oauth_client (
    id                   varchar(36) PRIMARY KEY,
    client_id            varchar(64)  NOT NULL UNIQUE,
    client_secret_hash   varchar(255) NULL,
    redirect_uris        text         NOT NULL,
    is_public            boolean      NOT NULL DEFAULT true,
    created_at           timestamptz  NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS oauth_authorization_code (
    id                      varchar(36) PRIMARY KEY,
    code_hash               varchar(64)  NOT NULL,
    client_id               varchar(64)  NOT NULL,
    redirect_uri            text         NOT NULL,
    user_id                 varchar(36)  NOT NULL,
    tenant_id               varchar(36)  NOT NULL,
    scope                   text         NOT NULL DEFAULT '',
    code_challenge          varchar(128) NOT NULL,
    code_challenge_method   varchar(16)  NOT NULL,
    expires_at              timestamptz  NOT NULL,
    used                    boolean      NOT NULL DEFAULT false,
    created_at              timestamptz  NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_oauth_authorization_code_hash ON oauth_authorization_code (code_hash);

CREATE TABLE IF NOT EXISTS oauth_access_token (
    id           varchar(36) PRIMARY KEY,
    token_hash   varchar(64)  NOT NULL UNIQUE,
    client_id    varchar(64)  NOT NULL,
    user_id      varchar(36)  NOT NULL,
    tenant_id    varchar(36)  NOT NULL,
    scope        text         NOT NULL DEFAULT '',
    expires_at   timestamptz  NOT NULL,
    revoked_at   timestamptz  NULL,
    created_at   timestamptz  NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS oauth_refresh_token (
    id                varchar(36) PRIMARY KEY,
    token_hash        varchar(64)  NOT NULL UNIQUE,
    access_token_id   varchar(36)  NOT NULL,
    client_id         varchar(64)  NOT NULL,
    user_id           varchar(36)  NOT NULL,
    tenant_id         varchar(36)  NOT NULL,
    scope             text         NOT NULL DEFAULT '',
    expires_at        timestamptz  NOT NULL,
    revoked_at        timestamptz  NULL,
    created_at        timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_oauth_refresh_access ON oauth_refresh_token (access_token_id);

-- 开发用公开客户端（PKCE，无 secret）；redirect 按实际前端修改
INSERT INTO oauth_client (id, client_id, client_secret_hash, redirect_uris, is_public) VALUES (
    '30000000-0000-4000-8000-000000000001',
    'spa',
    NULL,
    '["http://localhost:5173/callback","http://127.0.0.1:5173/callback","http://localhost:3000/callback"]',
    true
) ON CONFLICT (client_id) DO NOTHING;

COMMIT;