1  -- Migration 124: Create scan_email_sequence table for post-scan nurture emails
 2  --
 3  -- Tracks enrolment state for the 7-email non-converter sequence.
 4  -- One row per scan_id that opted in. The cron job (send-scan-email-sequence)
 5  -- checks next_send_at every 5-10 min and fires emails in order.
 6  
 7  CREATE TABLE IF NOT EXISTS scan_email_sequence (
 8    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
 9    scan_id               TEXT    NOT NULL REFERENCES free_scans(scan_id) ON DELETE CASCADE,
10    email                 TEXT    NOT NULL,          -- denormalised for query convenience
11    segment               TEXT    NOT NULL           -- 'A' (0-59) | 'B' (60-76) | 'C' (77-81)
12                            CHECK(segment IN ('A', 'B', 'C')),
13    country_code          TEXT,                      -- for localised pricing + timezone
14    score                 REAL,                      -- stored at enrolment, not re-computed
15    grade                 TEXT,
16    domain                TEXT    NOT NULL,
17    score_json            TEXT,                      -- factor scores for personalisation tokens
18    next_email_num        INTEGER NOT NULL DEFAULT 1 -- 1-7; 8 = sequence completed
19                            CHECK(next_email_num BETWEEN 1 AND 8),
20    next_send_at          TEXT,                      -- UTC ISO-8601; NULL = not yet scheduled
21    last_sent_at          TEXT,
22    enrolled_at           TEXT    NOT NULL DEFAULT (datetime('now')),
23    status                TEXT    NOT NULL DEFAULT 'active'
24                            CHECK(status IN ('active','completed','unsubscribed','bounced','purchased')),
25    purchase_detected_at  TEXT,                      -- set when purchase found, triggers exit
26    unsubscribe_token     TEXT    UNIQUE,             -- HMAC token for one-click unsub
27    created_at            TEXT    NOT NULL DEFAULT (datetime('now')),
28    updated_at            TEXT    NOT NULL DEFAULT (datetime('now'))
29  );
30  
31  CREATE UNIQUE INDEX IF NOT EXISTS idx_seq_scan_id ON scan_email_sequence(scan_id);
32  CREATE INDEX IF NOT EXISTS idx_seq_email        ON scan_email_sequence(email);
33  CREATE INDEX IF NOT EXISTS idx_seq_status       ON scan_email_sequence(status);
34  CREATE INDEX IF NOT EXISTS idx_seq_next_send    ON scan_email_sequence(next_send_at) WHERE status = 'active';
35  
36  CREATE TRIGGER IF NOT EXISTS update_scan_email_sequence_timestamp
37  AFTER UPDATE ON scan_email_sequence
38  BEGIN
39    UPDATE scan_email_sequence SET updated_at = datetime('now') WHERE id = NEW.id;
40  END;