1  import { createInMemoryDb, getTestDbPath } from './src/utils/test-db.js';
 2  
 3  const dbName = 'outreach-unit-test-debug';
 4  process.env.DATABASE_PATH = getTestDbPath(dbName);
 5  
 6  const setupDb = createInMemoryDb(dbName);
 7  
 8  const site = setupDb
 9    .prepare(
10      `INSERT INTO sites (domain, landing_page_url, keyword, status, score, country_code) VALUES (?, ?, ?, ?, ?, 'US')`
11    )
12    .run('test.com', 'https://test.com', 'test keyword', 'proposals_drafted', 75);
13  
14  setupDb
15    .prepare(
16      `INSERT INTO messages (site_id, contact_method, contact_uri, message_body, approval_status) VALUES (?, ?, ?, ?, ?)`
17    )
18    .run(site.lastInsertRowid, 'sms', '+1234567890', 'Test SMS Reply STOP to opt out', 'approved');
19  
20  const allMsgs = setupDb.prepare('SELECT * FROM messages').all();
21  console.log('Messages:', JSON.stringify(allMsgs));
22  
23  const rows = setupDb
24    .prepare(
25      `
26    SELECT o.id, o.site_id, o.contact_method, o.direction, o.approval_status, o.delivery_status, o.message_type, s.country_code, s.last_outreach_at
27    FROM messages o
28    JOIN sites s ON o.site_id = s.id
29    WHERE o.direction = 'outbound'
30      AND o.approval_status = 'approved'
31      AND o.delivery_status IS NULL
32      AND o.contact_method IS NOT NULL
33      AND o.contact_uri IS NOT NULL
34      AND s.country_code IN ('AU','US','NZ','GB','UK')
35      AND (s.last_outreach_at IS NULL OR s.last_outreach_at < datetime('now', '-3 days'))
36  `
37    )
38    .all();
39  
40  console.log('Query result:', JSON.stringify(rows));