1  -- Migration 095: Create free_scans table for inbound funnel (Free Website Scanner)
 2  --
 3  -- Tracks every free scan from auditandfix.com/scan:
 4  --   - URL input, computed score, email capture, UTM attribution
 5  --   - Conversion tracking (free → $47 → $297)
 6  --   - 7-day expiry on cached results
 7  
 8  CREATE TABLE IF NOT EXISTS free_scans (
 9    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
10    scan_id            TEXT    UNIQUE NOT NULL,          -- UUID, public-facing ID
11    url                TEXT    NOT NULL,
12    domain             TEXT    NOT NULL,
13    email              TEXT,                              -- null until email captured
14    ip_address         TEXT,
15    score              REAL,
16    grade              TEXT,
17    score_json         TEXT,                             -- full factor scores (server-side only)
18    industry           TEXT,
19    country_code       TEXT,
20    is_js_heavy        INTEGER DEFAULT 0,                -- 1 = JS-heavy site (neutral scores)
21    utm_source         TEXT,
22    utm_medium         TEXT,
23    utm_campaign       TEXT,
24    ref                TEXT,                             -- 'google', 'facebook', 'linkedin', 'organic'
25    converted_to       TEXT,                             -- 'quick_fixes' | 'full_audit' | NULL
26    converted_at       TEXT,
27    created_at         TEXT    NOT NULL DEFAULT (datetime('now')),
28    email_captured_at  TEXT,
29    expires_at         TEXT    NOT NULL DEFAULT (datetime('now', '+7 days'))
30  );
31  
32  CREATE INDEX IF NOT EXISTS idx_free_scans_email      ON free_scans(email);
33  CREATE INDEX IF NOT EXISTS idx_free_scans_domain     ON free_scans(domain);
34  CREATE INDEX IF NOT EXISTS idx_free_scans_ip         ON free_scans(ip_address, created_at);
35  CREATE INDEX IF NOT EXISTS idx_free_scans_expires    ON free_scans(expires_at);
36  CREATE INDEX IF NOT EXISTS idx_free_scans_converted  ON free_scans(converted_to);