1  -- Migration: Refactor processing_status to match pipeline stages
 2  -- Maps old status values to new descriptive values that match the 8-stage pipeline
 3  
 4  -- Update existing status values to new descriptive names
 5  -- 'pending' stays as 'pending' (will become 'loaded_from_serp' after SERP stage)
 6  -- 'processing' → 'assets_captured' (after assets stage completion)
 7  -- 'scored' stays as 'scored' (after scoring stage)
 8  -- 'failed' stays as 'failed' (error state)
 9  -- 'outreach_sent' stays as 'outreach_sent' (after outreach stage)
10  
11  UPDATE sites SET processing_status = 'assets_captured' WHERE processing_status = 'processing';
12  
13  -- New valid processing_status values for the 8-stage pipeline:
14  -- 1. 'pending' - Initial state after keyword selection, before SERP scraping
15  -- 2. 'loaded_from_serp' - After SERP scraping (serps stage)
16  -- 3. 'assets_captured' - After screenshot capture (assets stage)
17  -- 4. 'scored' - After initial scoring (scoring stage)
18  -- 5. 'rescored' - After rescoring with below-fold screenshots (rescoring stage)
19  -- 6. 'proposals_drafted' - After proposal generation (proposals stage)
20  -- 7. 'outreach_sent' - After outreach delivery (outreach stage)
21  -- 8. 'failed' - Error state (any stage can transition here)
22  
23  -- Note: SQLite doesn't support ALTER TABLE to modify CHECK constraints
24  -- The CHECK constraint would need to be recreated by:
25  -- 1. Creating a new table with the updated constraint
26  -- 2. Copying data
27  -- 3. Dropping old table
28  -- 4. Renaming new table
29  -- This is left for manual execution if strict validation is needed
30  
31  -- For reference, the desired CHECK constraint would be:
32  -- CHECK(processing_status IN (
33  --   'pending', 'loaded_from_serp', 'assets_captured', 'scored',
34  --   'rescored', 'proposals_drafted', 'outreach_sent', 'failed'
35  -- ))