1  /**
  2   * Tests for src/inbound/processor.js — pure DB functions
  3   *
  4   * Tests: getUnreadConversations, getConversationThread,
  5   *        markConversationRead, markThreadRead, getInboundStats
  6   *
  7   * pollAllChannels/processAllReplies require live Twilio/Resend — not tested here.
  8   */
  9  
 10  import { test, describe, before, mock } from 'node:test';
 11  import assert from 'node:assert/strict';
 12  import Database from 'better-sqlite3';
 13  import { createPgMock } from '../helpers/pg-mock.js';
 14  
 15  // Shared in-memory database — created at module level so mock.module() can reference it
 16  const db = new Database(':memory:');
 17  db.pragma('journal_mode = WAL');
 18  db.exec(`
 19    CREATE TABLE IF NOT EXISTS sites (
 20      id INTEGER PRIMARY KEY AUTOINCREMENT,
 21      domain TEXT NOT NULL DEFAULT 'test.com',
 22      keyword TEXT DEFAULT 'web design',
 23      status TEXT DEFAULT 'found',
 24      score REAL,
 25      conversion_score REAL,
 26      landing_page_url TEXT,
 27      updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
 28      rescored_at DATETIME
 29    );
 30  
 31    CREATE TABLE IF NOT EXISTS messages (
 32      id INTEGER PRIMARY KEY AUTOINCREMENT,
 33      site_id INTEGER NOT NULL,
 34      direction TEXT NOT NULL DEFAULT 'outbound',
 35      contact_method TEXT NOT NULL DEFAULT 'sms',
 36      contact_uri TEXT NOT NULL DEFAULT '',
 37      message_body TEXT,
 38      subject_line TEXT,
 39      sentiment TEXT,
 40      intent TEXT,
 41      approval_status TEXT,
 42      delivery_status TEXT,
 43      read_at TEXT,
 44      sent_at TEXT,
 45      delivered_at TEXT,
 46      opened_at TEXT,
 47      tracking_clicked_at TEXT,
 48      created_at TEXT NOT NULL DEFAULT (datetime('now')),
 49      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
 50      message_type TEXT DEFAULT 'outreach',
 51      raw_payload TEXT
 52    );
 53  
 54    CREATE TABLE IF NOT EXISTS countries (
 55      country_code TEXT PRIMARY KEY,
 56      country_name TEXT,
 57      google_domain TEXT,
 58      language_code TEXT,
 59      currency_code TEXT,
 60      is_active INTEGER DEFAULT 1,
 61      sms_enabled INTEGER DEFAULT 1,
 62      requires_gdpr_check INTEGER DEFAULT 0,
 63      twilio_phone_number TEXT
 64    );
 65  `);
 66  
 67  mock.module('../../src/utils/db.js', { namedExports: createPgMock(db) });
 68  
 69  const {
 70    getUnreadConversations,
 71    getConversationThread,
 72    markConversationRead,
 73    markThreadRead,
 74    getInboundStats,
 75  } = await import('../../src/inbound/processor.js');
 76  
 77  // ─── Schema + Seed BEFORE tests ───────────────────────────────────────────────
 78  
 79  let siteId, outboundId, inboundId1, inboundId2;
 80  
 81  before(() => {
 82    // Insert a site
 83    db.prepare("INSERT INTO sites (domain, keyword) VALUES ('example.com', 'web design')").run();
 84    siteId = db.prepare('SELECT id FROM sites ORDER BY id DESC LIMIT 1').get().id;
 85  
 86    // Insert an outbound message
 87    db.prepare(
 88      `INSERT INTO messages (site_id, direction, contact_method, contact_uri, message_body, approval_status, delivery_status)
 89       VALUES (?, 'outbound', 'sms', '+61412345678', 'Hi, we noticed your website...', 'approved', 'sent')`
 90    ).run(siteId);
 91    outboundId = db.prepare('SELECT id FROM messages ORDER BY id DESC LIMIT 1').get().id;
 92  
 93    // Insert two inbound messages (unread)
 94    db.prepare(
 95      `INSERT INTO messages (site_id, direction, contact_method, contact_uri, message_body, sentiment)
 96       VALUES (?, 'inbound', 'sms', '+61412345678', 'What is this?', 'neutral')`
 97    ).run(siteId);
 98    inboundId1 = db.prepare('SELECT id FROM messages ORDER BY id DESC LIMIT 1').get().id;
 99  
100    db.prepare(
101      `INSERT INTO messages (site_id, direction, contact_method, contact_uri, message_body, sentiment)
102       VALUES (?, 'inbound', 'sms', '+61412345678', 'Tell me more.', 'positive')`
103    ).run(siteId);
104    inboundId2 = db.prepare('SELECT id FROM messages ORDER BY id DESC LIMIT 1').get().id;
105  });
106  
107  // ─── getUnreadConversations ───────────────────────────────────────────────────
108  
109  describe('getUnreadConversations', () => {
110    test('returns array of unread inbound messages', async () => {
111      const convs = await getUnreadConversations();
112      assert.ok(Array.isArray(convs));
113      assert.ok(convs.length >= 1);
114    });
115  
116    test('returns messages with expected fields', async () => {
117      const convs = await getUnreadConversations();
118      const conv = convs.find(c => c.id === inboundId1);
119      assert.ok(conv, 'inbound message should be in results');
120      assert.equal(typeof conv.domain, 'string');
121      assert.equal(typeof conv.message_body, 'string');
122      assert.equal(typeof conv.channel, 'string');
123    });
124  
125    test('respects limit parameter', async () => {
126      const convs = await getUnreadConversations(1);
127      assert.equal(convs.length, 1);
128    });
129  
130    test('returns empty array when no unread messages', async () => {
131      // Mark all as read temporarily
132      db.exec("UPDATE messages SET read_at = CURRENT_TIMESTAMP WHERE direction = 'inbound'");
133  
134      const convs = await getUnreadConversations();
135      assert.equal(convs.length, 0);
136  
137      // Restore
138      db.exec("UPDATE messages SET read_at = NULL WHERE direction = 'inbound'");
139    });
140  });
141  
142  // ─── getConversationThread ────────────────────────────────────────────────────
143  
144  describe('getConversationThread', () => {
145    test('returns outreach + messages for valid site_id', async () => {
146      const thread = await getConversationThread(siteId);
147      assert.ok(typeof thread === 'object');
148      assert.ok('outreach' in thread);
149      assert.ok('messages' in thread);
150      assert.ok(Array.isArray(thread.messages));
151    });
152  
153    test('messages include both inbound and outbound', async () => {
154      const thread = await getConversationThread(siteId);
155      const directions = thread.messages.map(m => m.direction);
156      assert.ok(directions.includes('inbound'), 'should include inbound');
157      assert.ok(directions.includes('outbound'), 'should include outbound');
158    });
159  
160    test('returns null outreach for non-existent site', async () => {
161      const thread = await getConversationThread(999999);
162      assert.ok(thread.outreach == null, `expected null or undefined, got ${thread.outreach}`);
163      assert.deepEqual(thread.messages, []);
164    });
165  });
166  
167  // ─── markConversationRead ─────────────────────────────────────────────────────
168  
169  describe('markConversationRead', () => {
170    test('returns true on success', async () => {
171      const result = await markConversationRead(inboundId1);
172      assert.equal(result, true);
173    });
174  
175    test('message read_at is set after marking read', async () => {
176      await markConversationRead(inboundId1);
177      const row = db.prepare('SELECT read_at FROM messages WHERE id = ?').get(inboundId1);
178      assert.ok(row.read_at !== null);
179    });
180  
181    test('does not throw for non-existent message id', async () => {
182      await assert.doesNotReject(() => markConversationRead(999999));
183    });
184  });
185  
186  // ─── markThreadRead ───────────────────────────────────────────────────────────
187  
188  describe('markThreadRead', () => {
189    test('returns number of messages marked as read', async () => {
190      // Reset both inbound messages to unread
191      db.exec("UPDATE messages SET read_at = NULL WHERE direction = 'inbound'");
192  
193      const changes = await markThreadRead(siteId);
194      assert.ok(typeof changes === 'number');
195      assert.ok(changes >= 1, 'should have marked at least 1 message');
196    });
197  
198    test('returns 0 when all already read', async () => {
199      // All inbound should already be read from previous test
200      const changes = await markThreadRead(siteId);
201      assert.equal(changes, 0);
202    });
203  
204    test('returns 0 for non-existent site_id', async () => {
205      const changes = await markThreadRead(999999);
206      assert.equal(changes, 0);
207    });
208  });
209  
210  // ─── getInboundStats ──────────────────────────────────────────────────────────
211  
212  describe('getInboundStats', () => {
213    test('returns object with unreadCount, byChannel, bySentiment', async () => {
214      const stats = await getInboundStats();
215      assert.ok(typeof stats === 'object');
216      assert.ok('unreadCount' in stats);
217      assert.ok('byChannel' in stats);
218      assert.ok('bySentiment' in stats);
219      assert.ok(Array.isArray(stats.byChannel));
220      assert.ok(Array.isArray(stats.bySentiment));
221    });
222  
223    test('unreadCount is a number', async () => {
224      assert.ok(typeof (await getInboundStats()).unreadCount === 'number');
225    });
226  
227    test('byChannel has sms entries for our test data', async () => {
228      // Reset inbound to unread so there's data
229      db.exec("UPDATE messages SET read_at = NULL WHERE direction = 'inbound'");
230  
231      const stats = await getInboundStats();
232      const smsChannel = stats.byChannel.find(c => c.channel === 'sms');
233      assert.ok(smsChannel, 'should have sms channel stats');
234      assert.ok(smsChannel.total >= 1);
235      assert.ok(smsChannel.unread >= 0);
236    });
237  
238    test('bySentiment groups by sentiment value', async () => {
239      const stats = await getInboundStats();
240      // We have 'neutral' and 'positive' inbound messages
241      const neutralEntry = stats.bySentiment.find(s => s.sentiment === 'neutral');
242      assert.ok(
243        neutralEntry || stats.bySentiment.length === 0,
244        'should have sentiment entries or empty'
245      );
246    });
247  });