<?php
/**
 * Add run_number column to judge_assignments table
 */

require_once 'includes/db.php';

echo "=== Add run_number Column Migration ===\n\n";

try {
    // Check if column already exists
    $check = $pdo->query("SHOW COLUMNS FROM judge_assignments LIKE 'run_number'");
    if ($check->rowCount() > 0) {
        echo "✓ Column 'run_number' already exists in judge_assignments table\n";
    } else {
        echo "Adding 'run_number' column to judge_assignments table...\n";
        $pdo->exec("
            ALTER TABLE `judge_assignments` 
            ADD COLUMN `run_number` INT(11) NULL 
            COMMENT 'Run number assignment for judges' 
            AFTER `heat_number`
        ");
        echo "✓ Column 'run_number' added successfully\n";
    }
    
    // Add index for faster queries
    $indexes = $pdo->query("SHOW INDEX FROM judge_assignments WHERE Key_name = 'idx_heat_run'");
    if ($indexes->rowCount() > 0) {
        echo "✓ Index 'idx_heat_run' already exists\n";
    } else {
        echo "Adding composite index 'idx_heat_run'...\n";
        $pdo->exec("ALTER TABLE `judge_assignments` ADD INDEX `idx_heat_run` (`heat_number`, `run_number`)");
        echo "✓ Index 'idx_heat_run' added successfully\n";
    }
    
    // Show updated 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 %-15s\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);
}
