-- =========================================================
-- INFOSECLABS PRODUCTION FIX SCRIPT (FINAL CONSOLIDATED)
-- Run this script in phpMyAdmin to fix all missing columns
-- and tables that are causing crashes/bugs.
-- =========================================================

-- 1. Fix Users Table (Resolves 'Today's Queue' Empty List)
-- Adds missing path columns needed for the assignment algorithm.
ALTER TABLE users ADD COLUMN IF NOT EXISTS primary_path VARCHAR(50) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS secondary_path VARCHAR(50) DEFAULT NULL;

-- 2. Fix Operations Table (Resolves Mission Assignment Errors)
-- Adds min_level column required to start missions.
ALTER TABLE operations ADD COLUMN IF NOT EXISTS min_level INT DEFAULT 1;

-- 3. Fix Specialization Tables (Resolves Missing 'Your Specialization' Widget)
-- Creates the analytics table needed for the dashboard widget.
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)
);

-- 4. Fix User Progress Table (Safety Fallback)
-- Ensures assignment service doesn't crash on this table check.
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)
);

-- Note: Code changes (JS files) must also be deployed for Multi-User Isolation fixes.