-- =============================================
-- FIX MISSING SPECIALIZATION TABLES
-- The production dump was missing these analytics tables
-- causing the Specialization Widget to crash/hide.
-- =============================================

-- 1. Create user_path_specialization if missing
CREATE TABLE IF NOT EXISTS user_path_specialization (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  path_code VARCHAR(20) NOT NULL,
  level INT DEFAULT 1,
  xp INT DEFAULT 0,
  alerts_completed INT DEFAULT 0,
  missions_completed INT DEFAULT 0,
  average_score DECIMAL(5,2) DEFAULT 0.00,
  last_activity_date DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_user_path (user_id, path_code)
);

-- 2. Populate stats for Jacob (User 34) so widget shows something
-- Insert 0 stats for his active paths (TI and SIEM)
INSERT IGNORE INTO user_path_specialization (user_id, path_code, alerts_completed) VALUES
(34, 'TI', 0),
(34, 'SIEM', 0);

-- 3. Just in case: user_path_progress table (if used elsewhere)
CREATE TABLE IF NOT EXISTS user_path_progress (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  path_code VARCHAR(50) DEFAULT NULL,
  current_level INT DEFAULT 1,
  xp_earned INT DEFAULT 0,
  completed_alerts INT DEFAULT 0,
  last_activity DATETIME DEFAULT NULL,
  UNIQUE KEY unique_user_path_prog (user_id, path_code)
);