-- 006-usage-pings.sql
-- Per-user, per-module activity pings for the admin "Usage" modal.
-- Each row = ~30 seconds of focused attention (client heartbeats every 30s
-- while tab is visible and user is not idle). Aggregation: COUNT(*) * 30.
-- Retention: 90 days, batched cleanup hourly via core/lib/usage-cleanup.js.

CREATE TABLE IF NOT EXISTS usage_pings (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT NOT NULL,
  module_id   VARCHAR(50) NOT NULL,
  ping_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  url_path    VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_user_module_at (user_id, module_id, ping_at),
  KEY idx_user_at (user_id, ping_at),
  KEY idx_at (ping_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;