CREATE TABLE sika_app_activations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(190) NOT NULL,
    activation_token VARCHAR(128) NOT NULL UNIQUE,
    email_verified TINYINT(1) NOT NULL DEFAULT 0,
    activated_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NULL,
    app_version VARCHAR(50) NULL,
    device_model VARCHAR(190) NULL,
    android_version VARCHAR(80) NULL,

    INDEX idx_phone (phone),
    INDEX idx_email (email),
    INDEX idx_activation_token (activation_token),
    INDEX idx_email_verified (email_verified)
);


CREATE TABLE IF NOT EXISTS tracking_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event VARCHAR(20) NOT NULL,
    ip_address VARCHAR(45),
    location VARCHAR(255),
    device_os VARCHAR(100),
    browser VARCHAR(50),
    referrer TEXT,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_event (event),
    INDEX idx_created (created_at),
    INDEX idx_ip (ip_address)
);