1  -- Normalize contact_method values to lowercase single words
  2  -- Changes: SMS → sms, Email → email, Contact Form → form, LinkedIn → linkedin, etc.
  3  
  4  -- Since SQLite doesn't support ALTER COLUMN, we need to recreate the tables
  5  
  6  -- 1. Backup outreaches table
  7  CREATE TABLE outreaches_backup AS SELECT * FROM outreaches;
  8  
  9  -- 2. Drop the old outreaches table
 10  DROP TABLE outreaches;
 11  
 12  -- 3. Recreate outreaches with normalized contact_method constraint
 13  CREATE TABLE outreaches (
 14      id INTEGER PRIMARY KEY AUTOINCREMENT,
 15      site_id INTEGER NOT NULL REFERENCES sites(id),
 16      proposal_text TEXT NOT NULL,
 17      subject_line TEXT,
 18      variant_number INTEGER CHECK(variant_number IN (1, 2, 3)),
 19      contact_method TEXT NOT NULL CHECK(contact_method IN (
 20          'sms', 'email', 'form', 'linkedin', 'facebook', 'instagram'
 21      )),
 22      contact_uri TEXT NOT NULL,
 23      status TEXT DEFAULT 'pending' CHECK(status IN (
 24          'pending', 'sent', 'delivered', 'failed', 'bounced'
 25      )),
 26      sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 27      delivered_at DATETIME,
 28      resulted_in_sale BOOLEAN DEFAULT 0,
 29      sale_amount DECIMAL(10, 2),
 30      tracking_url TEXT,
 31      tracking_clicked_at DATETIME,
 32      unsubscribed BOOLEAN DEFAULT 0,
 33      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
 34  );
 35  
 36  -- 4. Restore data with normalized contact_method values (if any)
 37  INSERT INTO outreaches
 38  SELECT 
 39      id,
 40      site_id,
 41      proposal_text,
 42      subject_line,
 43      variant_number,
 44      CASE contact_method
 45          WHEN 'SMS' THEN 'sms'
 46          WHEN 'Email' THEN 'email'
 47          WHEN 'Contact Form' THEN 'form'
 48          WHEN 'LinkedIn' THEN 'linkedin'
 49          WHEN 'Facebook' THEN 'facebook'
 50          WHEN 'Instagram' THEN 'instagram'
 51          ELSE LOWER(contact_method)
 52      END,
 53      contact_uri,
 54      status,
 55      sent_at,
 56      delivered_at,
 57      resulted_in_sale,
 58      sale_amount,
 59      tracking_url,
 60      tracking_clicked_at,
 61      unsubscribed,
 62      created_at
 63  FROM outreaches_backup;
 64  
 65  -- 5. Drop backup table
 66  DROP TABLE outreaches_backup;
 67  
 68  -- 6. Recreate indexes
 69  CREATE INDEX idx_outreaches_site ON outreaches(site_id);
 70  CREATE INDEX idx_outreaches_method ON outreaches(contact_method);
 71  CREATE INDEX idx_outreaches_sale ON outreaches(resulted_in_sale);
 72  CREATE INDEX idx_outreaches_status ON outreaches(status);
 73  CREATE INDEX idx_outreaches_sent ON outreaches(sent_at);
 74  
 75  -- 7. Backup conversations table
 76  CREATE TABLE conversations_backup AS SELECT * FROM conversations;
 77  
 78  -- 8. Drop the old conversations table
 79  DROP TABLE conversations;
 80  
 81  -- 9. Recreate conversations with normalized channel constraint
 82  CREATE TABLE conversations (
 83      id INTEGER PRIMARY KEY AUTOINCREMENT,
 84      outreach_id INTEGER NOT NULL REFERENCES outreaches(id),
 85      direction TEXT NOT NULL CHECK(direction IN ('inbound', 'outbound')),
 86      channel TEXT NOT NULL CHECK(channel IN (
 87          'sms', 'email', 'form', 'linkedin', 'facebook', 'instagram'
 88      )),
 89      sender_identifier TEXT,
 90      message_body TEXT NOT NULL,
 91      subject_line TEXT,
 92      raw_payload TEXT,
 93      sentiment TEXT CHECK(sentiment IN ('positive', 'neutral', 'negative', 'objection')),
 94      received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 95      read_at DATETIME,
 96      replied_at DATETIME
 97  );
 98  
 99  -- 10. Restore data with normalized channel values (if any)
100  INSERT INTO conversations
101  SELECT 
102      id,
103      outreach_id,
104      direction,
105      CASE channel
106          WHEN 'SMS' THEN 'sms'
107          WHEN 'Email' THEN 'email'
108          WHEN 'Contact Form' THEN 'form'
109          WHEN 'LinkedIn' THEN 'linkedin'
110          WHEN 'Facebook' THEN 'facebook'
111          WHEN 'Instagram' THEN 'instagram'
112          ELSE LOWER(channel)
113      END,
114      sender_identifier,
115      message_body,
116      subject_line,
117      raw_payload,
118      sentiment,
119      received_at,
120      read_at,
121      replied_at
122  FROM conversations_backup;
123  
124  -- 11. Drop backup table
125  DROP TABLE conversations_backup;
126  
127  -- 12. Recreate indexes for conversations
128  CREATE INDEX idx_conversations_outreach ON conversations(outreach_id);
129  CREATE INDEX idx_conversations_received ON conversations(received_at);
130  CREATE INDEX idx_conversations_direction ON conversations(direction);