1  -- Migration 040: Create site_status table for historical status tracking
 2  -- This table tracks every status change for each site with timestamps
 3  
 4  -- Create site_status table
 5  CREATE TABLE IF NOT EXISTS site_status (
 6      id INTEGER PRIMARY KEY AUTOINCREMENT,
 7      site_id INTEGER NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
 8      status TEXT NOT NULL CHECK(status IN (
 9          'found', 'assets_captured', 'scored', 'rescored', 'enriched',
10          'proposals_drafted', 'outreach_sent', 'ignore', 'failing', 'high_score'
11      )),
12      error_message TEXT,
13      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
14  );
15  
16  -- Index for efficient queries by site
17  CREATE INDEX IF NOT EXISTS idx_site_status_site_id ON site_status(site_id);
18  
19  -- Index for time-based queries (dashboard charts)
20  CREATE INDEX IF NOT EXISTS idx_site_status_created_at ON site_status(created_at);
21  
22  -- Composite index for site + time queries
23  CREATE INDEX IF NOT EXISTS idx_site_status_site_created ON site_status(site_id, created_at);
24  
25  -- Index for status filtering
26  CREATE INDEX IF NOT EXISTS idx_site_status_status ON site_status(status);
27  
28  -- Create trigger to automatically log status changes
29  -- Fires whenever sites.status is updated
30  CREATE TRIGGER IF NOT EXISTS log_site_status_change
31  AFTER UPDATE OF status ON sites
32  FOR EACH ROW
33  WHEN NEW.status != OLD.status
34  BEGIN
35      INSERT INTO site_status (site_id, status, error_message, created_at)
36      VALUES (NEW.id, NEW.status, NEW.error_message, CURRENT_TIMESTAMP);
37  END;
38  
39  -- Migrate existing status data for all sites
40  -- This creates an initial status record for each site using its current status
41  INSERT INTO site_status (site_id, status, error_message, created_at)
42  SELECT
43      id,
44      status,
45      error_message,
46      -- Use updated_at if available, otherwise created_at
47      COALESCE(updated_at, created_at)
48  FROM sites
49  WHERE id NOT IN (SELECT site_id FROM site_status);