-- 用户行为分析（小程序埋点 + 日聚合）
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS user_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL DEFAULT NULL,
  session_id VARCHAR(64) NOT NULL DEFAULT '',
  event_name VARCHAR(64) NOT NULL,
  properties JSON NULL,
  platform VARCHAR(16) NOT NULL DEFAULT 'mp' COMMENT 'mp=微信小程序',
  client_ts BIGINT UNSIGNED NOT NULL DEFAULT 0,
  server_ts INT NOT NULL,
  ip_hash CHAR(64) NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_event_server (event_name, server_ts),
  KEY idx_user_server (user_id, server_ts),
  KEY idx_platform_date (platform, server_ts),
  KEY idx_session (session_id, server_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_daily (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  metric_date DATE NOT NULL,
  metric_key VARCHAR(64) NOT NULL,
  dimension_key VARCHAR(32) NOT NULL DEFAULT '',
  dimension_value VARCHAR(128) NOT NULL DEFAULT '',
  count_value BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_daily_metric (metric_date, metric_key, dimension_key, dimension_value),
  KEY idx_metric_date (metric_key, metric_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
