-- Analytics events: button clicks, page views, duration, IP, device/browser, etc.
-- Creates analytics_events (no prefix) and ftt_analytics_events (prefixed) for compatibility.

CREATE TABLE IF NOT EXISTS analytics_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  correlation_id VARCHAR(64) NULL,
  received_at DATETIME NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  source VARCHAR(32) NOT NULL COMMENT 'android|web',
  page_id VARCHAR(128) NULL,
  tab_id VARCHAR(128) NULL,
  session_id VARCHAR(64) NULL,
  device_id VARCHAR(64) NULL,
  duration_seconds INT NULL,
  action_id VARCHAR(128) NULL,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  referrer VARCHAR(512) NULL,
  extra JSON NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_analytics_received (received_at),
  INDEX idx_analytics_event_type (event_type),
  INDEX idx_analytics_source (source),
  INDEX idx_analytics_session (session_id),
  INDEX idx_analytics_device (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_analytics_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  correlation_id VARCHAR(64) NULL,
  received_at DATETIME NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  source VARCHAR(32) NOT NULL COMMENT 'android|web',
  page_id VARCHAR(128) NULL,
  tab_id VARCHAR(128) NULL,
  session_id VARCHAR(64) NULL,
  device_id VARCHAR(64) NULL,
  duration_seconds INT NULL,
  action_id VARCHAR(128) NULL,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  referrer VARCHAR(512) NULL,
  extra JSON NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_analytics_received (received_at),
  INDEX idx_analytics_event_type (event_type),
  INDEX idx_analytics_source (source),
  INDEX idx_analytics_session (session_id),
  INDEX idx_analytics_device (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;