<?php
/**
 * Add Judge Status Columns Migration
 * This script adds status and status_updated_at columns to judge_assignments table
 */

require_once 'includes/db.php';

echo "=== Judge Status Columns Migration ===\n\n";

try {
    // Check if columns already exist
    $check = $pdo->query("SHOW COLUMNS FROM judge_assignments LIKE 'status'");
    if ($check->rowCount() > 0) {
        echo "✓ Column 'status' already exists in judge_assignments table\n";
    } else {
        echo "Adding 'status' column to judge_assignments table...\n";
        $pdo->exec("
            ALTER TABLE `judge_assignments` 
            ADD COLUMN `status` VARCHAR(20) DEFAULT 'ready' 
            COMMENT 'Judge status: ready, pending, danger, warning, info, off' 
            AFTER `judge_id`
        ");
        echo "✓ Column 'status' added successfully\n";
    }
    
    $check = $pdo->query("SHOW COLUMNS FROM judge_assignments LIKE 'status_updated_at'");
    if ($check->rowCount() > 0) {
        echo "✓ Column 'status_updated_at' already exists in judge_assignments table\n";
    } else {
        echo "Adding 'status_updated_at' column to judge_assignments table...\n";
        $pdo->exec("
            ALTER TABLE `judge_assignments` 
            ADD COLUMN `status_updated_at` DATETIME NULL 
            COMMENT 'Timestamp when status was last updated' 
            AFTER `status`
        ");
        echo "✓ Column 'status_updated_at' added successfully\n";
    }
    
    // Check if indexes exist
    $indexes = $pdo->query("SHOW INDEX FROM judge_assignments WHERE Key_name = 'idx_status'");
    if ($indexes->rowCount() > 0) {
        echo "✓ Index 'idx_status' already exists\n";
    } else {
        echo "Adding index 'idx_status'...\n";
        $pdo->exec("ALTER TABLE `judge_assignments` ADD INDEX `idx_status` (`status`)");
        echo "✓ Index 'idx_status' added successfully\n";
    }
    
    $indexes = $pdo->query("SHOW INDEX FROM judge_assignments WHERE Key_name = 'idx_status_updated'");
    if ($indexes->rowCount() > 0) {
        echo "✓ Index 'idx_status_updated' already exists\n";
    } else {
        echo "Adding index 'idx_status_updated'...\n";
        $pdo->exec("ALTER TABLE `judge_assignments` ADD INDEX `idx_status_updated` (`status_updated_at`)");
        echo "✓ Index 'idx_status_updated' added successfully\n";
    }
    
    // Update existing records
    echo "\nUpdating existing records with default status...\n";
    $stmt = $pdo->exec("
        UPDATE `judge_assignments` 
        SET `status` = 'ready', 
            `status_updated_at` = NOW() 
        WHERE `status` IS NULL OR `status` = ''
    ");
    echo "✓ Updated $stmt records with default 'ready' status\n";
    
    // Show table structure
    echo "\n=== Updated Table Structure ===\n";
    $columns = $pdo->query("DESCRIBE judge_assignments")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($columns as $col) {
        echo sprintf("%-25s %-20s %-10s %-10s\n", 
            $col['Field'], 
            $col['Type'], 
            $col['Null'], 
            $col['Default'] ?? 'NULL'
        );
    }
    
    echo "\n✓ Migration completed successfully!\n";
    
} catch (PDOException $e) {
    echo "\n✗ Error: " . $e->getMessage() . "\n";
    exit(1);
}
