1  -- Migration 127: Ad signal detection columns
 2  -- Tracks whether a site is running paid ads (Google Ads, Meta, Bing, etc.)
 3  -- Used to prioritise ad-running businesses in outreach (they're already investing in traffic)
 4  
 5  ALTER TABLE sites ADD COLUMN IF NOT EXISTS is_running_ads BOOLEAN DEFAULT NULL;
 6  ALTER TABLE sites ADD COLUMN IF NOT EXISTS ad_signals JSONB DEFAULT NULL;
 7  ALTER TABLE sites ADD COLUMN IF NOT EXISTS ad_signals_updated_at TIMESTAMPTZ DEFAULT NULL;
 8  
 9  -- Composite index for outreach prioritisation: ads-running sites first, then by score
10  CREATE INDEX IF NOT EXISTS idx_sites_running_ads
11    ON sites(is_running_ads DESC NULLS LAST, score ASC)
12    WHERE status IN ('proposals_drafted', 'outreach_partial', 'outreach_sent');
13  
14  -- For backfill/update queries
15  CREATE INDEX IF NOT EXISTS idx_sites_ad_signals_null
16    ON sites(id)
17    WHERE is_running_ads IS NULL AND status != 'ignored';
18  
19  COMMENT ON COLUMN sites.is_running_ads IS 'Whether site has detectable paid ad activity (Google Ads, Meta, Bing, call tracking)';
20  COMMENT ON COLUMN sites.ad_signals IS 'Detailed JSON: {google_ads: bool, meta_pixel: bool, bing_ads: bool, call_tracking: bool, ...}';