-- Autonomous Training System Migration
-- Phase 1: Add Specialization Path Support
-- Created: 2026-01-10
-- Safe to run on production (additive changes only)
-- Compatible with MySQL 5.7+ and 8.0+

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- ============================================
-- 1. Add path columns to users table
-- ============================================
-- Check if columns exist before adding
SET @dbname = DATABASE();

-- Add primary_path to users
SET @columnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'users' AND COLUMN_NAME = 'primary_path');
SET @sqlUsers1 = IF(@columnExists = 0, 'ALTER TABLE users ADD COLUMN primary_path VARCHAR(20) DEFAULT "SIEM"', 'SELECT "primary_path already exists"');
PREPARE stmt FROM @sqlUsers1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add secondary_path to users
SET @columnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'users' AND COLUMN_NAME = 'secondary_path');
SET @sqlUsers2 = IF(@columnExists = 0, 'ALTER TABLE users ADD COLUMN secondary_path VARCHAR(20) DEFAULT NULL', 'SELECT "secondary_path already exists"');
PREPARE stmt FROM @sqlUsers2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- 2. Add level/path columns to alerts table
-- ============================================
-- Add min_level to alerts
SET @columnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'alerts' AND COLUMN_NAME = 'min_level');
SET @sqlAlerts1 = IF(@columnExists = 0, 'ALTER TABLE alerts ADD COLUMN min_level INT DEFAULT 1', 'SELECT "alerts.min_level already exists"');
PREPARE stmt FROM @sqlAlerts1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add path_code to alerts
SET @columnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'alerts' AND COLUMN_NAME = 'path_code');
SET @sqlAlerts2 = IF(@columnExists = 0, 'ALTER TABLE alerts ADD COLUMN path_code VARCHAR(20) DEFAULT NULL', 'SELECT "alerts.path_code already exists"');
PREPARE stmt FROM @sqlAlerts2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- 3. Add level/path columns to operations table
-- ============================================
-- Add min_level to operations
SET @columnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'operations' AND COLUMN_NAME = 'min_level');
SET @sqlOps1 = IF(@columnExists = 0, 'ALTER TABLE operations ADD COLUMN min_level INT DEFAULT 1', 'SELECT "operations.min_level already exists"');
PREPARE stmt FROM @sqlOps1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add path_code to operations
SET @columnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'operations' AND COLUMN_NAME = 'path_code');
SET @sqlOps2 = IF(@columnExists = 0, 'ALTER TABLE operations ADD COLUMN path_code VARCHAR(20) DEFAULT NULL', 'SELECT "operations.path_code already exists"');
PREPARE stmt FROM @sqlOps2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- 4. Create user_alert_assignments table
-- Tracks daily alert assignments per user
-- ============================================
CREATE TABLE IF NOT EXISTS `user_alert_assignments` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `alert_id` INT NOT NULL,
    `assigned_date` DATE NOT NULL,
    `completed` TINYINT(1) DEFAULT 0,
    `completed_at` TIMESTAMP NULL,
    `is_replay` TINYINT(1) DEFAULT 0,
    `xp_earned` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_user_alert_date` (`user_id`, `alert_id`, `assigned_date`),
    CONSTRAINT `fk_assignments_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_assignments_alert` FOREIGN KEY (`alert_id`) REFERENCES `alerts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- 5. Create user_path_specialization table
-- Tracks progress per path (separate from XP)
-- ============================================
CREATE TABLE IF NOT EXISTS `user_path_specialization` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `path_code` VARCHAR(20) NOT NULL,
    `alerts_completed` INT DEFAULT 0,
    `missions_completed` INT DEFAULT 0,
    `average_score` DECIMAL(5,2) DEFAULT 0.00,
    `last_activity` TIMESTAMP 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`),
    CONSTRAINT `fk_specialization_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- 6. Add indexes for performance (if not exist)
-- ============================================
-- These will silently fail if they already exist which is fine

SELECT 'Migration completed successfully!' AS status;
