-- Device usage tracking: total seconds and per-tab usage for dashboard stats.
-- Creates device_usage (no prefix) and ftt_device_usage (prefixed) for compatibility.

CREATE TABLE IF NOT EXISTS device_usage (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(64) NOT NULL,
  usage_date DATE NOT NULL,
  total_seconds INT NOT NULL DEFAULT 0,
  tab_usage JSON NULL COMMENT 'Map of tab_id to seconds',
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_device_date (device_id, usage_date),
  INDEX idx_device_usage_device (device_id),
  INDEX idx_device_usage_date (usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_device_usage (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(64) NOT NULL,
  usage_date DATE NOT NULL,
  total_seconds INT NOT NULL DEFAULT 0,
  tab_usage JSON NULL COMMENT 'Map of tab_id to seconds',
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_device_date (device_id, usage_date),
  INDEX idx_device_usage_device (device_id),
  INDEX idx_device_usage_date (usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;