1  -- Migration 044: Add 'gov_blocked' status to outreaches table
 2  -- Blocks outreach to government email addresses (.gov, .gov.au, .gc.ca, etc.)
 3  
 4  -- SQLite doesn't support modifying CHECK constraints directly
 5  -- We need to recreate the table with the updated constraint
 6  
 7  -- CREATE TABLE is a no-op since the status already includes 'gov_blocked'
 8  -- Just ensure indexes exist
 9  SELECT 'gov_blocked status already exists in CHECK constraint' as status;
10  
11  -- Ensure indexes exist
12  CREATE INDEX IF NOT EXISTS idx_outreaches_site_id ON outreaches(site_id);
13  CREATE INDEX IF NOT EXISTS idx_outreaches_method ON outreaches(contact_method);
14  CREATE INDEX IF NOT EXISTS idx_outreaches_sale ON outreaches(resulted_in_sale);
15  CREATE INDEX IF NOT EXISTS idx_outreaches_status ON outreaches(status);
16  CREATE INDEX IF NOT EXISTS idx_outreaches_sent ON outreaches(sent_at);
17  
18  -- Summary:
19  -- Added 'gov_blocked' to status CHECK constraint
20  -- This status is used to block outreach to government email addresses
21  -- Examples: joe@example.gov, alice@example.gov.au, bob@example.gc.ca