1  -- Update valid contact methods to only allow: sms, email, form, x, linkedin
 2  -- Remove facebook and instagram (no cold outreach allowed)
 3  -- Add x (X.com/Twitter)
 4  
 5  -- 1. Backup outreaches table
 6  CREATE TABLE outreaches_backup AS SELECT * FROM outreaches;
 7  
 8  -- 2. Drop the old outreaches table
 9  DROP TABLE outreaches;
10  
11  -- 3. Recreate outreaches with updated contact_method constraint
12  CREATE TABLE outreaches (
13      id INTEGER PRIMARY KEY AUTOINCREMENT,
14      site_id INTEGER NOT NULL REFERENCES sites(id),
15      proposal_text TEXT NOT NULL,
16      subject_line TEXT,
17      variant_number INTEGER CHECK(variant_number IN (1, 2, 3)),
18      contact_method TEXT NOT NULL CHECK(contact_method IN (
19          'sms', 'email', 'form', 'x', 'linkedin'
20      )),
21      contact_uri TEXT NOT NULL,
22      status TEXT DEFAULT 'pending' CHECK(status IN (
23          'pending', 'sent', 'delivered', 'failed', 'bounced'
24      )),
25      sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
26      delivered_at DATETIME,
27      resulted_in_sale BOOLEAN DEFAULT 0,
28      sale_amount DECIMAL(10, 2),
29      tracking_url TEXT,
30      tracking_clicked_at DATETIME,
31      unsubscribed BOOLEAN DEFAULT 0,
32      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
33  );
34  
35  -- 4. Restore data (excluding facebook/instagram if any exist)
36  INSERT INTO outreaches
37  SELECT * FROM outreaches_backup
38  WHERE contact_method IN ('sms', 'email', 'form', 'linkedin')
39     OR contact_method = 'x';
40  
41  -- 5. Drop backup table
42  DROP TABLE outreaches_backup;
43  
44  -- 6. Recreate indexes
45  CREATE INDEX idx_outreaches_site ON outreaches(site_id);
46  CREATE INDEX idx_outreaches_method ON outreaches(contact_method);
47  CREATE INDEX idx_outreaches_sale ON outreaches(resulted_in_sale);
48  CREATE INDEX idx_outreaches_status ON outreaches(status);
49  CREATE INDEX idx_outreaches_sent ON outreaches(sent_at);
50  
51  -- 7. Backup conversations table
52  CREATE TABLE conversations_backup AS SELECT * FROM conversations;
53  
54  -- 8. Drop the old conversations table
55  DROP TABLE conversations;
56  
57  -- 9. Recreate conversations with updated channel constraint
58  CREATE TABLE conversations (
59      id INTEGER PRIMARY KEY AUTOINCREMENT,
60      outreach_id INTEGER NOT NULL REFERENCES outreaches(id),
61      direction TEXT NOT NULL CHECK(direction IN ('inbound', 'outbound')),
62      channel TEXT NOT NULL CHECK(channel IN (
63          'sms', 'email', 'form', 'x', 'linkedin'
64      )),
65      sender_identifier TEXT,
66      message_body TEXT NOT NULL,
67      subject_line TEXT,
68      raw_payload TEXT,
69      sentiment TEXT CHECK(sentiment IN ('positive', 'neutral', 'negative', 'objection')),
70      received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
71      read_at DATETIME,
72      replied_at DATETIME
73  );
74  
75  -- 10. Restore data (excluding facebook/instagram if any exist)
76  INSERT INTO conversations
77  SELECT * FROM conversations_backup
78  WHERE channel IN ('sms', 'email', 'form', 'linkedin')
79     OR channel = 'x';
80  
81  -- 11. Drop backup table
82  DROP TABLE conversations_backup;
83  
84  -- 12. Recreate indexes for conversations
85  CREATE INDEX idx_conversations_outreach ON conversations(outreach_id);
86  CREATE INDEX idx_conversations_received ON conversations(received_at);
87  CREATE INDEX idx_conversations_direction ON conversations(direction);