CREATE TABLE IF NOT EXISTS marketing_links (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) NOT NULL UNIQUE,
    campaign_name TEXT DEFAULT NULL,
    flow_id INT DEFAULT NULL,
    card_id INT DEFAULT NULL,
    daily_budget DECIMAL(10,2) DEFAULT 0.00,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    hits INT DEFAULT 0,
    unique_visitors INT DEFAULT 0,
    conversions INT DEFAULT 0,
    trust_scores TEXT DEFAULT NULL,
    nginx_active TINYINT(1) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'draft',
    image_path TEXT DEFAULT NULL,
    target_url TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS marketing_visitors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    link_id INT UNSIGNED NOT NULL,
    session_id VARCHAR(64) NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    referrer TEXT DEFAULT NULL,
    trust_score INT DEFAULT 100,
    flags TEXT DEFAULT NULL,
    device_fingerprint TEXT DEFAULT NULL,
    is_blackholed TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_link (link_id),
    INDEX idx_session (session_id)
);
