1  -- Migration 102: Fix messages table FK pointing to non-existent sites_old
 2  -- The messages table was created with REFERENCES "sites_old"(id) during a
 3  -- migration that used sites_old as a temp table (097/098). After those
 4  -- migrations dropped sites_old, the FK reference remained stale.
 5  -- With foreign_keys=ON, every INSERT into messages failed:
 6  --   SqliteError: no such table: main.sites_old
 7  -- This migration recreates messages with REFERENCES "sites"(id).
 8  --
 9  -- NOTE: This migration was already applied directly to the live DB on 2026-03-15.
10  -- It is included here for documentation and future fresh installs.
11  
12  PRAGMA foreign_keys = OFF;
13  
14  ALTER TABLE messages RENAME TO messages_fk_fix_old;
15  
16  CREATE TABLE "messages" (
17      id INTEGER PRIMARY KEY AUTOINCREMENT,
18      site_id INTEGER NOT NULL REFERENCES "sites"(id) ON DELETE CASCADE,
19      direction TEXT NOT NULL CHECK(direction IN ('inbound', 'outbound')),
20      contact_method TEXT NOT NULL CHECK(contact_method IN ('email', 'sms', 'form', 'x', 'linkedin')),
21      contact_uri TEXT NOT NULL,
22      message_body TEXT,
23      subject_line TEXT,
24      approval_status TEXT CHECK(approval_status IN (
25          'pending', 'approved', 'rework', 'rejected', 'gdpr_blocked', 'parked'
26      )),
27      rework_instructions TEXT,
28      exported_at TEXT,
29      delivery_status TEXT CHECK(delivery_status IN (
30          'queued', 'sent', 'delivered', 'failed', 'bounced', 'retry_later'
31      )),
32      error_message TEXT,
33      retry_at TEXT,
34      sent_at TEXT,
35      delivered_at TEXT,
36      email_id TEXT,
37      opened_at TEXT,
38      tracking_clicked_at TEXT,
39      sentiment TEXT CHECK(sentiment IN ('positive', 'neutral', 'negative', 'objection')),
40      intent TEXT CHECK(intent IN (
41          'inquiry', 'opt-out', 'interested', 'not-interested',
42          'pricing', 'schedule', 'unknown', 'autoresponder'
43      )),
44      raw_payload TEXT,
45      is_read INTEGER DEFAULT 0,
46      read_at TEXT,
47      processed_at TEXT,
48      payment_link TEXT,
49      payment_id TEXT,
50      payment_amount REAL,
51      payment_currency TEXT,
52      payment_amount_local INTEGER,
53      payment_amount_usd INTEGER,
54      exchange_rate REAL,
55      pricing_variant TEXT,
56      report_url TEXT,
57      our_account TEXT,
58      template_id TEXT,
59      zb_status TEXT,
60      created_at TEXT NOT NULL DEFAULT (datetime('now')),
61      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
62      retry_count INTEGER DEFAULT 0,
63      message_type TEXT DEFAULT 'outreach',
64      reworded_at TEXT DEFAULT NULL
65  );
66  
67  INSERT INTO messages SELECT * FROM messages_fk_fix_old;
68  DROP TABLE messages_fk_fix_old;
69  
70  CREATE INDEX idx_messages_site_id ON messages(site_id);
71  CREATE INDEX idx_messages_approval_status ON messages(approval_status) WHERE approval_status IS NOT NULL;
72  CREATE INDEX idx_messages_sent_at ON messages(sent_at) WHERE sent_at IS NOT NULL;
73  CREATE INDEX idx_messages_direction ON messages(direction);
74  CREATE INDEX idx_messages_contact_uri ON messages(contact_uri);
75  CREATE INDEX idx_messages_created_at ON messages(created_at);
76  CREATE INDEX idx_messages_outreach_queue ON messages(approval_status, delivery_status, direction, contact_method)
77  WHERE approval_status='approved' AND delivery_status IS NULL AND direction='outbound';
78  
79  PRAGMA foreign_keys = ON;