-- Migration 001: Initial OAuth2 STS Schema
-- Created: 2024-01-01
-- Description: Create core OAuth2 tables with proper foreign keys and indexes

-- OAuth Clients table
CREATE TABLE IF NOT EXISTS oauth_clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id TEXT NOT NULL UNIQUE,
    client_secret_hash TEXT NOT NULL,
    client_name TEXT NOT NULL,
    redirect_uris TEXT NOT NULL, -- JSON array
    scopes TEXT NOT NULL, -- space-separated
    grant_types TEXT NOT NULL, -- space-separated  
    response_types TEXT NOT NULL, -- space-separated
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT 1
);

-- Authorization Codes table
CREATE TABLE IF NOT EXISTS auth_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    client_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    redirect_uri TEXT NOT NULL,
    scope TEXT,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    is_used BOOLEAN NOT NULL DEFAULT 0,
    code_challenge TEXT,
    code_challenge_method TEXT,
    FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id)
);

-- Access Tokens table
CREATE TABLE IF NOT EXISTS access_tokens (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT NOT NULL UNIQUE,
    client_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    scope TEXT,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    is_revoked BOOLEAN NOT NULL DEFAULT 0,
    token_hash TEXT NOT NULL,
    FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id)
);

-- Refresh Tokens table
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT NOT NULL UNIQUE,
    access_token_id INTEGER NOT NULL,
    client_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    scope TEXT,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    is_revoked BOOLEAN NOT NULL DEFAULT 0,
    token_hash TEXT NOT NULL,
    FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id),
    FOREIGN KEY (access_token_id) REFERENCES access_tokens (id)
);

-- RSA Keys table
CREATE TABLE IF NOT EXISTS rsa_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kid TEXT NOT NULL UNIQUE,
    private_key_pem TEXT NOT NULL,
    public_key_pem TEXT NOT NULL,
    created_at TEXT NOT NULL,
    is_current BOOLEAN NOT NULL DEFAULT 0
);

-- Audit Log table
CREATE TABLE IF NOT EXISTS audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    client_id TEXT,
    user_id TEXT,
    ip_address TEXT,
    user_agent TEXT,
    details TEXT, -- JSON
    created_at TEXT NOT NULL,
    success BOOLEAN NOT NULL
);

-- Rate Limiting table
CREATE TABLE IF NOT EXISTS rate_limits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    identifier TEXT NOT NULL, -- client_id or IP
    endpoint TEXT NOT NULL,
    count INTEGER NOT NULL DEFAULT 1,
    window_start TEXT NOT NULL,
    created_at TEXT NOT NULL,
    UNIQUE (identifier, endpoint, window_start)
);

-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_auth_codes_client_id ON auth_codes (client_id);
CREATE INDEX IF NOT EXISTS idx_auth_codes_expires_at ON auth_codes (expires_at);
CREATE INDEX IF NOT EXISTS idx_access_tokens_client_id ON access_tokens (client_id);
CREATE INDEX IF NOT EXISTS idx_access_tokens_expires_at ON access_tokens (expires_at);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_client_id ON refresh_tokens (client_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs (created_at);
CREATE INDEX IF NOT EXISTS idx_rate_limits_identifier ON rate_limits (identifier, endpoint);