-- ============================================
-- TEMPLATE-SAAS: OAuth Connections
-- Schema: auth
-- Version: 1.0.0
-- ============================================

CREATE TABLE auth.oauth_connections (
    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,

    -- Provider info
    provider auth.oauth_provider NOT NULL,
    provider_user_id VARCHAR(255) NOT NULL,

    -- Provider data
    provider_email VARCHAR(255),
    provider_name VARCHAR(255),
    provider_avatar_url VARCHAR(500),

    -- Tokens (encrypted at rest)
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMPTZ,

    -- Scopes granted
    scopes JSONB DEFAULT '[]'::jsonb,

    -- Metadata from provider
    raw_data JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    last_used_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT unique_provider_user UNIQUE (provider, provider_user_id),
    CONSTRAINT unique_user_provider UNIQUE (user_id, provider)
);

-- Indexes
CREATE INDEX idx_oauth_user ON auth.oauth_connections(user_id);
CREATE INDEX idx_oauth_provider ON auth.oauth_connections(provider, provider_user_id);
CREATE INDEX idx_oauth_tenant ON auth.oauth_connections(tenant_id);

-- RLS
ALTER TABLE auth.oauth_connections ENABLE ROW LEVEL SECURITY;

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

-- Trigger
CREATE OR REPLACE FUNCTION auth.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_oauth_updated_at
    BEFORE UPDATE ON auth.oauth_connections
    FOR EACH ROW
    EXECUTE FUNCTION auth.update_updated_at();

-- Comments
COMMENT ON TABLE auth.oauth_connections IS 'OAuth provider connections per user';
COMMENT ON COLUMN auth.oauth_connections.access_token IS 'Encrypted OAuth access token';
COMMENT ON COLUMN auth.oauth_connections.raw_data IS 'Raw profile data from provider';