-- ============================================
-- TEMPLATE-SAAS: Auth Tokens
-- Schema: auth
-- Version: 1.0.0
-- ============================================

-- Generic tokens (password reset, email verification, etc.)
CREATE TABLE auth.tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES tenants.tenants(id) ON DELETE CASCADE,  -- NULL for pre-signup tokens
    user_id UUID REFERENCES users.users(id) ON DELETE CASCADE,

    -- Token data
    token_hash VARCHAR(255) UNIQUE NOT NULL,
    type auth.token_type NOT NULL,

    -- For API keys
    name VARCHAR(200),  -- User-defined name
    prefix VARCHAR(10),  -- First chars for identification (e.g., "sk_live_")
    last_used_at TIMESTAMPTZ,

    -- Scope/permissions (for API keys)
    scopes JSONB DEFAULT '[]'::jsonb,

    -- Expiration
    expires_at TIMESTAMPTZ,
    is_revoked BOOLEAN DEFAULT FALSE,
    revoked_at TIMESTAMPTZ,

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID
);

-- Refresh tokens (separate for better management)
CREATE TABLE auth.refresh_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    session_id UUID REFERENCES auth.sessions(id) ON DELETE CASCADE,

    -- Token
    token_hash VARCHAR(255) UNIQUE NOT NULL,

    -- Rotation tracking
    family_id UUID NOT NULL,  -- For refresh token rotation
    generation INT DEFAULT 1,

    -- Status
    is_revoked BOOLEAN DEFAULT FALSE,
    revoked_at TIMESTAMPTZ,

    -- Expiration
    expires_at TIMESTAMPTZ NOT NULL,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    used_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX idx_tokens_user ON auth.tokens(user_id) WHERE NOT is_revoked;
CREATE INDEX idx_tokens_type ON auth.tokens(type) WHERE NOT is_revoked;
CREATE INDEX idx_tokens_tenant ON auth.tokens(tenant_id) WHERE NOT is_revoked;
CREATE INDEX idx_tokens_api_key ON auth.tokens(prefix) WHERE type = 'api_key' AND NOT is_revoked;

CREATE INDEX idx_refresh_tokens_user ON auth.refresh_tokens(user_id) WHERE NOT is_revoked;
CREATE INDEX idx_refresh_tokens_family ON auth.refresh_tokens(family_id);
CREATE INDEX idx_refresh_tokens_session ON auth.refresh_tokens(session_id);

-- RLS
ALTER TABLE auth.tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE auth.refresh_tokens ENABLE ROW LEVEL SECURITY;

CREATE POLICY tokens_tenant_isolation ON auth.tokens
    USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY refresh_tokens_tenant_isolation ON auth.refresh_tokens
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Comments
COMMENT ON TABLE auth.tokens IS 'Generic tokens for various auth purposes';
COMMENT ON TABLE auth.refresh_tokens IS 'JWT refresh tokens with rotation support';
COMMENT ON COLUMN auth.tokens.prefix IS 'Visible prefix for API key identification';
COMMENT ON COLUMN auth.refresh_tokens.family_id IS 'Group ID for refresh token rotation (detect reuse)';