-- Indexing for Tenant Isolation and Performance (Core DB)

-- 1. Websites
CREATE INDEX IF NOT EXISTS idx_websites_account_id ON websites(account_id);
-- Standard index for URL lookup (Duplicates exist in current data)
CREATE INDEX IF NOT EXISTS idx_websites_url ON websites(url);

-- 2. AI Audience Fit Scores (ICP Results)
CREATE INDEX IF NOT EXISTS idx_audience_fit_account_id ON ai_audience_fit_scores(account_id);
CREATE INDEX IF NOT EXISTS idx_audience_fit_website_id ON ai_audience_fit_scores(website_id);
-- Composite index for multi-tenant queries
CREATE INDEX IF NOT EXISTS idx_audience_fit_composite ON ai_audience_fit_scores(account_id, website_id);

-- 3. AI Visibility Scores (AI Visibility Runs)
CREATE INDEX IF NOT EXISTS idx_visibility_scores_account_id ON ai_visibility_scores(account_id);
CREATE INDEX IF NOT EXISTS idx_visibility_scores_website_id ON ai_visibility_scores(website_id);
CREATE INDEX IF NOT EXISTS idx_visibility_scores_status ON ai_visibility_scores(status);
CREATE INDEX IF NOT EXISTS idx_visibility_scores_calc_at ON ai_visibility_scores(calculated_at);
-- Composite index for multi-tenant queries
CREATE INDEX IF NOT EXISTS idx_visibility_scores_composite ON ai_visibility_scores(account_id, website_id);

-- 4. General Tenant Isolation Indexes for other tables
CREATE INDEX IF NOT EXISTS idx_pages_account_id ON pages(account_id);
CREATE INDEX IF NOT EXISTS idx_prompts_account_id ON prompts(account_id);
CREATE INDEX IF NOT EXISTS idx_citations_account_id ON citations(account_id);
CREATE INDEX IF NOT EXISTS idx_visibility_gaps_account_id ON visibility_gaps(account_id);
CREATE INDEX IF NOT EXISTS idx_traffic_events_account_id ON traffic_events(account_id);
CREATE INDEX IF NOT EXISTS idx_prompt_ai_responses_account_id ON prompt_ai_responses(account_id);
CREATE INDEX IF NOT EXISTS idx_prompt_responses_account_id ON prompt_responses(account_id);
