1  -- Migration 075: Per-country Twilio phone numbers
 2  -- Date: 2026-03-04
 3  -- Purpose: Store a local Twilio number per country for SMS outreach.
 4  --   sms_enabled=1 → we have (or plan to have) a local number for this country.
 5  --   sms_enabled=0 → SMS disabled for this country until a number is purchased.
 6  --   twilio_phone_number → E.164 number once purchased (NULL = not yet bought).
 7  
 8  ALTER TABLE countries ADD COLUMN twilio_phone_number TEXT;
 9  ALTER TABLE countries ADD COLUMN sms_enabled BOOLEAN NOT NULL DEFAULT 0;
10  
11  -- Enable SMS for the 5 markets where we're buying numbers (AU, NZ, CA, GB, US)
12  -- Note: UK is stored as 'UK' (not 'GB') in the countries table
13  UPDATE countries SET sms_enabled = 1 WHERE country_code IN ('AU', 'NZ', 'CA', 'UK', 'US');
14  
15  -- All other countries remain sms_enabled=0 until numbers are purchased.
16  
17  -- Seed purchased numbers (added 2026-03-04)
18  -- AU and NZ share the same +61 number
19  UPDATE countries SET twilio_phone_number='+61468089949' WHERE country_code IN ('AU', 'NZ');
20  UPDATE countries SET twilio_phone_number='+18254794242' WHERE country_code='CA';
21  UPDATE countries SET twilio_phone_number='+447446944610' WHERE country_code='UK';
22  -- US: pending A2P 10DLC registration (~$10/month for marketing use case)