1  -- Migration 023: Add multi-currency pricing and split testing support
 2  -- Created: 2024-02-06
 3  --
 4  -- This migration adds:
 5  -- 1. Weekly repricing fields to countries table ($300 base → PPP → FX → Round to 7)
 6  -- 2. Split testing infrastructure for A/B testing pricing variants
 7  -- 3. Payment currency tracking in conversations table
 8  
 9  -- Part A: Update Countries Table for Weekly Repricing
10  -- =====================================================
11  
12  ALTER TABLE countries ADD COLUMN price_usd_base INTEGER DEFAULT 30000; -- Base $300 USD for all (cents)
13  ALTER TABLE countries ADD COLUMN price_usd_ppp INTEGER; -- PPP-adjusted USD (cents)
14  ALTER TABLE countries ADD COLUMN price_local INTEGER; -- Local currency (cents/smallest unit)
15  ALTER TABLE countries ADD COLUMN price_local_formatted TEXT; -- Display format (e.g., "367")
16  ALTER TABLE countries ADD COLUMN exchange_rate REAL; -- USD to local currency rate
17  ALTER TABLE countries ADD COLUMN price_last_updated TEXT; -- Timestamp of last repricing
18  
19  -- Split testing support
20  ALTER TABLE countries ADD COLUMN pricing_variant TEXT DEFAULT 'control'; -- control, test_a, test_b
21  ALTER TABLE countries ADD COLUMN variant_multiplier REAL DEFAULT 1.0; -- 0.9 = -10%, 1.1 = +10%
22  
23  -- Indexes for query performance
24  CREATE INDEX IF NOT EXISTS idx_countries_pricing_variant ON countries(pricing_variant);
25  CREATE INDEX IF NOT EXISTS idx_countries_price_updated ON countries(price_last_updated);
26  
27  -- Part B: Payment Currency Tracking
28  -- ==================================
29  -- Columns already exist, just ensure indexes are created
30  
31  -- Indexes for analytics queries
32  CREATE INDEX IF NOT EXISTS idx_conversations_payment_currency ON conversations(payment_currency);
33  CREATE INDEX IF NOT EXISTS idx_conversations_pricing_variant ON conversations(pricing_variant);
34  
35  -- Migration complete
36  SELECT 'Migration 023 completed: Added multi-currency and split testing support' as result;