1  -- Migration 018: Remove default values from keywords country columns
 2  -- Date: 2026-02-04
 3  -- Description: Make country_code and google_domain required without defaults
 4  -- This aligns with the DRY principle - these should be explicitly provided
 5  
 6  -- SQLite doesn't support ALTER COLUMN to remove DEFAULT directly
 7  -- We need to recreate the table without the defaults
 8  
 9  -- Step 0: Fix any priority values that are 0 (violate CHECK constraint)
10  UPDATE keywords SET priority = 1 WHERE priority = 0;
11  
12  -- Step 1: Create new table with correct schema (no defaults on country fields)
13  CREATE TABLE keywords_new (
14      id INTEGER PRIMARY KEY AUTOINCREMENT,
15      keyword TEXT NOT NULL,
16      priority INTEGER DEFAULT 5 CHECK(priority >= 1 AND priority <= 10),
17      status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive')),
18      search_count INTEGER DEFAULT 0,
19      zenrows_count INTEGER DEFAULT 0,
20      assets_scraped_count INTEGER DEFAULT 0,
21      low_scoring_count INTEGER DEFAULT 0,
22      rescored_count INTEGER DEFAULT 0,
23      search_volume INTEGER DEFAULT 0,
24      country_code TEXT NOT NULL,
25      google_domain TEXT NOT NULL,
26      last_scraped_at DATETIME,
27      last_searched_at DATETIME,
28      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
29      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
30      UNIQUE(keyword, country_code)
31  );
32  
33  -- Step 2: Copy data from old table to new table
34  INSERT INTO keywords_new (
35      id, keyword, priority, status, search_count, zenrows_count,
36      assets_scraped_count, low_scoring_count, rescored_count, search_volume,
37      country_code, google_domain, last_scraped_at, last_searched_at,
38      created_at, updated_at
39  )
40  SELECT
41      id, keyword, priority, status, search_count, zenrows_count,
42      assets_scraped_count, low_scoring_count, rescored_count, search_volume,
43      country_code, google_domain, last_scraped_at, last_searched_at,
44      created_at, updated_at
45  FROM keywords;
46  
47  -- Step 3: Drop old table
48  DROP TABLE keywords;
49  
50  -- Step 4: Rename new table to original name
51  ALTER TABLE keywords_new RENAME TO keywords;
52  
53  -- Step 5: Recreate indexes
54  CREATE INDEX IF NOT EXISTS idx_keywords_country ON keywords(country_code);
55  CREATE INDEX IF NOT EXISTS idx_keywords_google_domain ON keywords(google_domain);
56  CREATE INDEX IF NOT EXISTS idx_keywords_status ON keywords(status);
57  CREATE INDEX IF NOT EXISTS idx_keywords_priority ON keywords(priority DESC);
58  CREATE INDEX IF NOT EXISTS idx_keywords_search_count ON keywords(search_count);
59  CREATE INDEX IF NOT EXISTS idx_keywords_zenrows_count ON keywords(zenrows_count);
60  CREATE INDEX IF NOT EXISTS idx_keywords_search_volume ON keywords(search_volume DESC);
61  CREATE INDEX IF NOT EXISTS idx_keywords_priority_search_volume ON keywords(priority DESC, search_volume DESC);
62  CREATE INDEX IF NOT EXISTS idx_keywords_last_scraped ON keywords(last_scraped_at);
63  CREATE INDEX IF NOT EXISTS idx_keywords_last_searched ON keywords(last_searched_at);
64  
65  -- Step 6: Recreate trigger
66  CREATE TRIGGER IF NOT EXISTS update_keywords_timestamp
67  AFTER UPDATE ON keywords
68  BEGIN
69      UPDATE keywords SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
70  END;