1  -- Migration: Add keywords table for tracking keyword performance
 2  -- This table tracks metrics for search keywords to optimize future campaigns
 3  
 4  CREATE TABLE IF NOT EXISTS keywords (
 5      id INTEGER PRIMARY KEY AUTOINCREMENT,
 6      keyword TEXT NOT NULL UNIQUE,
 7      zenrows_count INTEGER DEFAULT 0,  -- Total sites returned by ZenRows for this keyword
 8      processed_count INTEGER DEFAULT 0, -- Sites that have been successfully processed (captured + scored)
 9      low_scoring_count INTEGER DEFAULT 0, -- Sites with B- or below after initial scoring
10      rework_low_scoring_count INTEGER DEFAULT 0, -- Sites still low-scoring after resubmit
11      last_scraped_at DATETIME,
12      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
13      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
14  );
15  
16  -- Unique index to prevent duplicate keywords
17  CREATE UNIQUE INDEX IF NOT EXISTS idx_keywords_keyword ON keywords(keyword);
18  
19  -- Index for frequently queried columns
20  CREATE INDEX IF NOT EXISTS idx_keywords_zenrows_count ON keywords(zenrows_count);
21  CREATE INDEX IF NOT EXISTS idx_keywords_last_scraped ON keywords(last_scraped_at);
22  
23  -- Trigger to update updated_at timestamp
24  CREATE TRIGGER IF NOT EXISTS update_keywords_timestamp 
25  AFTER UPDATE ON keywords
26  BEGIN
27      UPDATE keywords SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
28  END;