-- Add daily bandwidth monitoring to an existing installation.


CREATE TABLE IF NOT EXISTS bandwidth_daily_usage (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  usage_date DATE NOT NULL,
  interface_name VARCHAR(64) NOT NULL,
  rx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  tx_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  total_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  previous_rx_bytes BIGINT UNSIGNED NULL,
  previous_tx_bytes BIGINT UNSIGNED NULL,
  current_rx_bytes BIGINT UNSIGNED NULL,
  current_tx_bytes BIGINT UNSIGNED NULL,
  captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('baseline','ok','failed','counter_reset') NOT NULL DEFAULT 'ok',
  http_code INT NULL,
  last_error TEXT NULL,
  raw_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_bandwidth_device_date_interface (device_id, usage_date, interface_name),
  INDEX (usage_date),
  INDEX (device_id),
  INDEX (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
