CREATE TABLE IF NOT EXISTS ai_usage_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  request_type VARCHAR(80) NOT NULL DEFAULT 'chat',
  provider VARCHAR(40) NOT NULL,
  model VARCHAR(120) NOT NULL,
  input_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  output_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  total_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  estimated_cost_cents INT UNSIGNED NOT NULL DEFAULT 0,
  budget_mode ENUM('normal', 'conservative', 'economy', 'system') NOT NULL DEFAULT 'normal',
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ai_usage_user_created (user_id, created_at),
  KEY idx_ai_usage_created (created_at),
  KEY idx_ai_usage_provider (provider, model),
  CONSTRAINT fk_ai_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_budget_periods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_key VARCHAR(40) NOT NULL DEFAULT 'free',
  period_start DATETIME NOT NULL,
  period_end DATETIME NOT NULL,
  budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  spent_cents INT UNSIGNED NOT NULL DEFAULT 0,
  mode ENUM('normal', 'conservative', 'economy') NOT NULL DEFAULT 'normal',
  last_checked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ai_budget_user_period (user_id, period_start, period_end),
  KEY idx_ai_budget_mode (mode),
  CONSTRAINT fk_ai_budget_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;