-- analytics table: tracks all user actions
CREATE TABLE IF NOT EXISTS analytics (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event        VARCHAR(50)     NOT NULL COMMENT 'paste_created, paste_viewed, paste_unlocked, paste_failed_password, paste_expired, paste_not_found, decrypt_error',
    paste_id     CHAR(32)        DEFAULT NULL COMMENT 'related paste ID if applicable',
    ip           VARCHAR(45)     NOT NULL DEFAULT '',
    user_agent   VARCHAR(512)    DEFAULT NULL,
    referer      VARCHAR(512)    DEFAULT NULL,
    extra        JSON            DEFAULT NULL COMMENT 'expire_time, char_count, has_password, source, etc.',
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_event      (event),
    INDEX idx_paste_id   (paste_id),
    INDEX idx_ip         (ip),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;