-- OAuth Tokens for external API integrations
-- Supports: Google (GBP Business Profile, Search Console), Meta (Facebook, Instagram)

CREATE TABLE IF NOT EXISTS oauth_tokens (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id),
    provider VARCHAR(50) NOT NULL,  -- 'google', 'meta'
    service VARCHAR(50) NOT NULL,   -- 'gbp', 'search_console', 'facebook', 'instagram'
    access_token TEXT NOT NULL,
    refresh_token TEXT,
    token_type VARCHAR(50) DEFAULT 'Bearer',
    expires_at TIMESTAMP,
    scopes TEXT,  -- space-separated scopes
    account_id VARCHAR(255),  -- external account/page ID
    account_name VARCHAR(255),  -- external account/page name
    metadata JSONB,  -- additional provider-specific data
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(company_id, provider, service)
);

CREATE INDEX idx_oauth_tokens_company ON oauth_tokens(company_id);
CREATE INDEX idx_oauth_tokens_provider ON oauth_tokens(provider, service);

-- Grant permissions
GRANT ALL ON TABLE oauth_tokens TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE oauth_tokens_id_seq TO nordabiz_app;