1  -- Migration 027: Add GDPR Verification Support
 2  -- Created: 2026-02-06
 3  -- Purpose: Add company verification fields for GDPR compliance in EU/UK countries
 4  
 5  -- Columns already exist, just ensure indexes are created
 6  CREATE INDEX IF NOT EXISTS idx_sites_gdpr_verified ON sites(gdpr_verified) WHERE gdpr_verified IS NOT NULL;
 7  
 8  -- Create index for GDPR countries needing verification
 9  CREATE INDEX IF NOT EXISTS idx_sites_country_gdpr ON sites(country_code, gdpr_verified) WHERE country_code IN (
10    'DE', 'FR', 'IT', 'ES', 'NL', 'BE', 'AT', 'SE', 'DK', 'NO', 'IE', 'UK', 'PL'
11  );
12  
13  -- Update outreaches table to track GDPR verification status
14  -- Add delivery_status for tracking verification issues
15  -- Possible values: 'pending', 'sent', 'delivered', 'failed', 'bounced', 'unsubscribed', 'unverified_email'
16  -- 'unverified_email' = GDPR country email that failed company verification
17  
18  -- Note: delivery_status column already exists, just documenting the new value
19  
20  -- Create view for GDPR verification reporting
21  CREATE VIEW IF NOT EXISTS gdpr_verification_report AS
22  SELECT
23    country_code,
24    COUNT(*) as total_sites,
25    SUM(CASE WHEN gdpr_verified = 1 THEN 1 ELSE 0 END) as verified_count,
26    SUM(CASE WHEN gdpr_verified = 0 THEN 1 ELSE 0 END) as unverified_count,
27    SUM(CASE WHEN gdpr_verified IS NULL THEN 1 ELSE 0 END) as unchecked_count,
28    ROUND(100.0 * SUM(CASE WHEN gdpr_verified = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) as verification_rate,
29    MIN(gdpr_verified_at) as first_check,
30    MAX(gdpr_verified_at) as last_check
31  FROM sites
32  WHERE country_code IN ('DE', 'FR', 'IT', 'ES', 'NL', 'BE', 'AT', 'SE', 'DK', 'NO', 'IE', 'UK', 'PL')
33  GROUP BY country_code
34  ORDER BY total_sites DESC;
35  
36  -- Example queries:
37  --
38  -- 1. Get all unverified GDPR sites:
39  --    SELECT * FROM sites WHERE country_code IN ('DE', 'UK', ...) AND gdpr_verified = 0;
40  --
41  -- 2. Get verification statistics by country:
42  --    SELECT * FROM gdpr_verification_report;
43  --
44  -- 3. Find sites needing verification:
45  --    SELECT * FROM sites
46  --    WHERE country_code IN ('DE', 'UK', ...)
47  --    AND gdpr_verified IS NULL
48  --    AND status IN ('enriched', 'proposals_drafted');
49  --
50  -- 4. Get outreaches blocked by GDPR:
51  --    SELECT o.*, s.domain, s.country_code
52  --    FROM outreaches o
53  --    JOIN sites s ON o.site_id = s.id
54  --    WHERE o.delivery_status = 'unverified_email';