1  /**
  2   * Tests for Keywords Stage
  3   *
  4   * Migrated to use db.js mock (pg-mock) — keywords.js now uses db.js (PostgreSQL).
  5   */
  6  
  7  import { test, describe, mock, beforeEach } from 'node:test';
  8  import assert from 'node:assert';
  9  import Database from 'better-sqlite3';
 10  import { createPgMock } from '../helpers/pg-mock.js';
 11  
 12  // Minimal schema for keywords and sites tables
 13  const SCHEMA_SQL = `
 14    CREATE TABLE IF NOT EXISTS keywords (
 15      id INTEGER PRIMARY KEY AUTOINCREMENT,
 16      keyword TEXT NOT NULL,
 17      priority INTEGER NOT NULL DEFAULT 5 CHECK(priority >= 1 AND priority <= 10),
 18      status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'paused')),
 19      country_code TEXT NOT NULL DEFAULT 'AU',
 20      google_domain TEXT NOT NULL DEFAULT 'google.com.au',
 21      search_count INTEGER NOT NULL DEFAULT 0,
 22      zenrows_count INTEGER NOT NULL DEFAULT 0,
 23      low_scoring_count INTEGER NOT NULL DEFAULT 0,
 24      last_scraped_at DATETIME,
 25      last_searched_at DATETIME,
 26      created_at DATETIME DEFAULT (datetime('now')),
 27      updated_at DATETIME DEFAULT (datetime('now')),
 28      UNIQUE (keyword, country_code)
 29    );
 30  
 31    CREATE TABLE IF NOT EXISTS sites (
 32      id INTEGER PRIMARY KEY AUTOINCREMENT,
 33      domain TEXT NOT NULL,
 34      landing_page_url TEXT NOT NULL DEFAULT '',
 35      keyword TEXT,
 36      score REAL,
 37      status TEXT DEFAULT 'found',
 38      created_at DATETIME DEFAULT (datetime('now'))
 39    );
 40  `;
 41  
 42  // Shared in-memory SQLite DB — mock.module can only be called once
 43  const db = new Database(':memory:');
 44  db.exec(SCHEMA_SQL);
 45  mock.module('../../src/utils/db.js', { namedExports: createPgMock(db) });
 46  
 47  // Dynamic import AFTER mock.module
 48  const { runKeywordsStage, addKeyword, listKeywords, updateKeywordPriority } =
 49    await import('../../src/stages/keywords.js');
 50  
 51  describe('Keywords Stage', () => {
 52    beforeEach(() => {
 53      db.exec('DELETE FROM keywords; DELETE FROM sites;');
 54    });
 55  
 56    describe('addKeyword', () => {
 57      test('adds keyword with default priority', async () => {
 58        const id = await addKeyword('plumber sydney');
 59        assert.ok(id > 0, 'Should return valid ID');
 60  
 61        const keyword = db.prepare('SELECT * FROM keywords WHERE id = ?').get(id);
 62  
 63        assert.strictEqual(keyword.keyword, 'plumber sydney');
 64        assert.strictEqual(keyword.priority, 5);
 65        assert.strictEqual(keyword.status, 'active');
 66        assert.strictEqual(keyword.search_count, 0);
 67      });
 68  
 69      test('adds keyword with custom priority', async () => {
 70        const id = await addKeyword('electrician melbourne', 8);
 71        assert.ok(id > 0, 'Should return valid ID');
 72  
 73        const keyword = db.prepare('SELECT * FROM keywords WHERE id = ?').get(id);
 74  
 75        assert.strictEqual(keyword.keyword, 'electrician melbourne');
 76        assert.strictEqual(keyword.priority, 8);
 77        assert.strictEqual(keyword.status, 'active');
 78      });
 79  
 80      test('enforces unique constraint on keyword', async () => {
 81        await addKeyword('plumber sydney', 5);
 82  
 83        await assert.rejects(
 84          async () => { await addKeyword('plumber sydney', 7); },
 85          /UNIQUE constraint failed/
 86        );
 87      });
 88  
 89      test('enforces priority bounds (1-10)', () => {
 90        // Priority < 1 should fail
 91        assert.throws(() => {
 92          db
 93            .prepare(
 94              'INSERT INTO keywords (keyword, priority, status, country_code, google_domain) VALUES (?, ?, ?, ?, ?)'
 95            )
 96            .run('test keyword', 0, 'active', 'AU', 'google.com.au');
 97        }, /CHECK constraint failed/);
 98  
 99        // Priority > 10 should fail
100        assert.throws(() => {
101          db
102            .prepare(
103              'INSERT INTO keywords (keyword, priority, status, country_code, google_domain) VALUES (?, ?, ?, ?, ?)'
104            )
105            .run('test keyword 2', 11, 'active', 'AU', 'google.com.au');
106        }, /CHECK constraint failed/);
107      });
108    });
109  
110    describe('updateKeywordPriority', () => {
111      test('updates keyword priority', async () => {
112        const id = await addKeyword('plumber sydney', 5);
113        await updateKeywordPriority(id, 9);
114  
115        const keyword = db.prepare('SELECT priority FROM keywords WHERE id = ?').get(id);
116  
117        assert.strictEqual(keyword.priority, 9);
118      });
119  
120      test('enforces priority bounds via CHECK constraint', async () => {
121        const id = await addKeyword('plumber sydney', 5);
122  
123        // Attempting to set priority < 1 should fail
124        assert.throws(() => {
125          db.prepare('UPDATE keywords SET priority = ? WHERE id = ?').run(0, id);
126        }, /CHECK constraint failed/);
127  
128        // Attempting to set priority > 10 should fail
129        assert.throws(() => {
130          db.prepare('UPDATE keywords SET priority = ? WHERE id = ?').run(11, id);
131        }, /CHECK constraint failed/);
132      });
133    });
134  
135    describe('listKeywords', () => {
136      test('returns empty array when no keywords exist', async () => {
137        const keywords = await listKeywords();
138        assert.strictEqual(keywords.length, 0);
139      });
140  
141      test('lists keywords with stats', async () => {
142        const id1 = await addKeyword('plumber sydney', 8);
143        const id2 = await addKeyword('electrician melbourne', 5);
144  
145        const keywords = await listKeywords();
146  
147        assert.strictEqual(keywords.length, 2);
148        assert.strictEqual(keywords[0].id, id1); // Higher priority first
149        assert.strictEqual(keywords[0].keyword, 'plumber sydney');
150        assert.strictEqual(keywords[0].priority, 8);
151        assert.strictEqual(keywords[0].total_sites, 0);
152        assert.strictEqual(keywords[0].low_score_sites, 0);
153  
154        assert.strictEqual(keywords[1].id, id2);
155        assert.strictEqual(keywords[1].keyword, 'electrician melbourne');
156        assert.strictEqual(keywords[1].priority, 5);
157      });
158  
159      test('orders by priority DESC, then search_count ASC', async () => {
160        // Insert keywords with different priorities and search counts
161        db
162          .prepare(
163            'INSERT INTO keywords (keyword, priority, status, search_count, country_code, google_domain) VALUES (?, ?, ?, ?, ?, ?)'
164          )
165          .run('keyword-high-priority', 9, 'active', 5, 'AU', 'google.com.au');
166        db
167          .prepare(
168            'INSERT INTO keywords (keyword, priority, status, search_count, country_code, google_domain) VALUES (?, ?, ?, ?, ?, ?)'
169          )
170          .run('keyword-low-priority-low-count', 3, 'active', 1, 'AU', 'google.com.au');
171        db
172          .prepare(
173            'INSERT INTO keywords (keyword, priority, status, search_count, country_code, google_domain) VALUES (?, ?, ?, ?, ?, ?)'
174          )
175          .run('keyword-low-priority-high-count', 3, 'active', 10, 'AU', 'google.com.au');
176  
177        const keywords = await listKeywords();
178  
179        // Should be ordered: high priority (9), then low priority sorted by search_count
180        assert.strictEqual(keywords[0].keyword, 'keyword-high-priority');
181        assert.strictEqual(keywords[1].keyword, 'keyword-low-priority-low-count');
182        assert.strictEqual(keywords[2].keyword, 'keyword-low-priority-high-count');
183      });
184  
185      test('joins with sites table correctly', async () => {
186        // Add keyword
187        await addKeyword('plumber sydney', 5);
188  
189        // Add sites with matching keyword
190        db
191          .prepare('INSERT INTO sites (keyword, domain, landing_page_url, score) VALUES (?, ?, ?, ?)')
192          .run('plumber sydney', 'example1.com', 'https://example1.com', 95);
193        db
194          .prepare('INSERT INTO sites (keyword, domain, landing_page_url, score) VALUES (?, ?, ?, ?)')
195          .run('plumber sydney', 'example2.com', 'https://example2.com', 72);
196        db
197          .prepare('INSERT INTO sites (keyword, domain, landing_page_url, score) VALUES (?, ?, ?, ?)')
198          .run('plumber sydney', 'example3.com', 'https://example3.com', 80);
199  
200        const keywords = await listKeywords();
201  
202        assert.strictEqual(keywords[0].total_sites, 3);
203        assert.strictEqual(keywords[0].low_score_sites, 2); // Scores < 83 (72 and 80)
204      });
205    });
206  
207    describe('runKeywordsStage', () => {
208      test('returns empty stats when no active keywords exist', async () => {
209        const stats = await runKeywordsStage();
210  
211        assert.strictEqual(stats.processed, 0);
212        assert.strictEqual(stats.succeeded, 0);
213        assert.strictEqual(stats.failed, 0);
214        assert.ok(stats.duration >= 0);
215      });
216  
217      test('processes active keywords', async () => {
218        await addKeyword('plumber sydney', 8);
219        await addKeyword('electrician melbourne', 5);
220  
221        const stats = await runKeywordsStage();
222  
223        assert.strictEqual(stats.processed, 2);
224        assert.strictEqual(stats.succeeded, 2);
225        assert.strictEqual(stats.failed, 0);
226      });
227  
228      test('respects limit parameter', async () => {
229        await addKeyword('plumber sydney', 8);
230        await addKeyword('electrician melbourne', 7);
231        await addKeyword('carpenter brisbane', 6);
232  
233        const stats = await runKeywordsStage({ limit: 2 });
234  
235        assert.strictEqual(stats.processed, 2);
236        assert.strictEqual(stats.succeeded, 2);
237      });
238  
239      test('only selects active keywords', async () => {
240        db
241          .prepare(
242            'INSERT INTO keywords (keyword, priority, status, country_code, google_domain) VALUES (?, ?, ?, ?, ?)'
243          )
244          .run('active-keyword', 8, 'active', 'AU', 'google.com.au');
245        db
246          .prepare(
247            'INSERT INTO keywords (keyword, priority, status, country_code, google_domain) VALUES (?, ?, ?, ?, ?)'
248          )
249          .run('inactive-keyword', 9, 'inactive', 'AU', 'google.com.au');
250  
251        const stats = await runKeywordsStage();
252  
253        assert.strictEqual(stats.processed, 1);
254        assert.strictEqual(stats.succeeded, 1);
255      });
256    });
257  
258    describe('Schema Validation', () => {
259      test('keywords table has all required columns', () => {
260        const tableInfo = db.prepare("PRAGMA table_info('keywords')").all();
261  
262        const columnNames = tableInfo.map(col => col.name);
263  
264        assert.ok(columnNames.includes('id'));
265        assert.ok(columnNames.includes('keyword'));
266        assert.ok(columnNames.includes('priority'));
267        assert.ok(columnNames.includes('status'));
268        assert.ok(columnNames.includes('search_count'));
269        assert.ok(columnNames.includes('zenrows_count'));
270        assert.ok(columnNames.includes('low_scoring_count'));
271        assert.ok(columnNames.includes('last_scraped_at'));
272        assert.ok(columnNames.includes('last_searched_at'));
273        assert.ok(columnNames.includes('created_at'));
274        assert.ok(columnNames.includes('updated_at'));
275      });
276  
277      test('status column has correct CHECK constraint', () => {
278        // Valid status should work
279        db
280          .prepare(
281            'INSERT INTO keywords (keyword, status, country_code, google_domain) VALUES (?, ?, ?, ?)'
282          )
283          .run('test-active', 'active', 'AU', 'google.com.au');
284        db
285          .prepare(
286            'INSERT INTO keywords (keyword, status, country_code, google_domain) VALUES (?, ?, ?, ?)'
287          )
288          .run('test-inactive', 'inactive', 'AU', 'google.com.au');
289  
290        // Invalid status should fail
291        assert.throws(() => {
292          db
293            .prepare(
294              'INSERT INTO keywords (keyword, status, country_code, google_domain) VALUES (?, ?, ?, ?)'
295            )
296            .run('test-invalid', 'invalid', 'AU', 'google.com.au');
297        }, /CHECK constraint failed/);
298      });
299    });
300  });