1  #!/usr/bin/env node
 2  
 3  /**
 4   * Clear Search Volumes from Keywords
 5   *
 6   * Sets search_volume and priority to NULL for all keywords in the database.
 7   * This prepares the keywords table for population via DataForSEO API.
 8   */
 9  
10  import { createDatabaseConnection } from '../src/utils/db.js';
11  import Logger from '../src/utils/logger.js';
12  
13  const logger = new Logger('ClearSearchVolumes');
14  
15  function main() {
16    const dbPath = process.env.DATABASE_PATH || './db/sites.db';
17    const db = createDatabaseConnection(dbPath);
18    db.pragma('journal_mode = WAL');
19  
20    try {
21      // Get count before
22      const beforeCount = db
23        .prepare('SELECT COUNT(*) as count FROM keywords WHERE search_volume IS NOT NULL')
24        .get();
25  
26      logger.info(`Found ${beforeCount.count} keywords with search volumes set`);
27  
28      // Clear search volumes and priority
29      const result = db
30        .prepare(
31          `UPDATE keywords
32           SET search_volume = NULL,
33               priority = NULL
34           WHERE search_volume IS NOT NULL OR priority IS NOT NULL`
35        )
36        .run();
37  
38      logger.success(`✓ Cleared search volumes and priority for ${result.changes} keywords`);
39  
40      // Verify
41      const afterCount = db
42        .prepare('SELECT COUNT(*) as count FROM keywords WHERE search_volume IS NOT NULL')
43        .get();
44  
45      if (afterCount.count === 0) {
46        logger.success('✓ All search volumes cleared successfully');
47      } else {
48        logger.warn(`⚠ ${afterCount.count} keywords still have search volumes`);
49      }
50    } catch (error) {
51      logger.error(`Fatal error: ${error.message}`);
52      console.error(error);
53      process.exit(1);
54    } finally {
55      db.close();
56    }
57  }
58  
59  main();