1  -- Migration: Drop tracking_url column from outreaches table
 2  -- Date: 2026-01-27
 3  -- Reason: SMS no longer uses custom tracking URLs (Twilio handles delivery tracking)
 4  --         Email uses Resend's built-in tracking (tracking_clicked_at is sufficient)
 5  
 6  -- SQLite doesn't support DROP COLUMN directly, so we need to:
 7  -- 1. Create a new table without the column
 8  -- 2. Copy data from old table
 9  -- 3. Drop old table
10  -- 4. Rename new table
11  
12  -- Create new outreaches table without tracking_url
13  CREATE TABLE outreaches_new (
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', 'x', 'linkedin'
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_clicked_at DATETIME,
31      opened_at DATETIME,
32      email_id TEXT,
33      unsubscribed BOOLEAN DEFAULT 0,
34      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
35  );
36  
37  -- Copy data from old table (excluding tracking_url)
38  INSERT INTO outreaches_new (
39      id, site_id, proposal_text, subject_line, variant_number,
40      contact_method, contact_uri, status, sent_at, delivered_at,
41      resulted_in_sale, sale_amount, tracking_clicked_at,
42      unsubscribed, created_at
43  )
44  SELECT
45      id, site_id, proposal_text, subject_line, variant_number,
46      contact_method, contact_uri, status, sent_at, delivered_at,
47      resulted_in_sale, sale_amount, tracking_clicked_at,
48      unsubscribed, created_at
49  FROM outreaches;
50  
51  -- Drop old table
52  DROP TABLE outreaches;
53  
54  -- Rename new table
55  ALTER TABLE outreaches_new RENAME TO outreaches;