-- Migration to add account_id to child tables for better RLS performance

-- 1. Add account_id to ai_audience_fit_scores
ALTER TABLE ai_audience_fit_scores ADD COLUMN IF NOT EXISTS account_id UUID;
UPDATE ai_audience_fit_scores s SET account_id = w.account_id FROM websites w WHERE s.website_id = w.website_id AND s.account_id IS NULL;

-- 2. Add account_id to pages
ALTER TABLE pages ADD COLUMN IF NOT EXISTS account_id UUID;
UPDATE pages p SET account_id = w.account_id FROM websites w WHERE p.website_id = w.website_id AND p.account_id IS NULL;

-- 3. Add account_id to prompts
ALTER TABLE prompts ADD COLUMN IF NOT EXISTS account_id UUID;
UPDATE prompts p SET account_id = w.account_id FROM websites w WHERE p.website_id = w.website_id AND p.account_id IS NULL;

-- 4. Add account_id to ai_visibility_scores
ALTER TABLE ai_visibility_scores ADD COLUMN IF NOT EXISTS account_id UUID;
UPDATE ai_visibility_scores s SET account_id = w.account_id FROM websites w WHERE s.website_id = w.website_id AND s.account_id IS NULL;

-- 5. Add account_id to citations
ALTER TABLE citations ADD COLUMN IF NOT EXISTS account_id UUID;
UPDATE citations c SET account_id = w.account_id FROM websites w WHERE c.website_id = w.website_id AND c.account_id IS NULL;

-- 6. Add account_id to visibility_gaps
ALTER TABLE visibility_gaps ADD COLUMN IF NOT EXISTS account_id UUID;
UPDATE visibility_gaps g SET account_id = w.account_id FROM websites w WHERE g.website_id = w.website_id AND g.account_id IS NULL;
