<?php
/**
 * Migration: Add heat status column to event_heat_settings table
 * 
 * This migration adds a 'status' column to replace the binary is_active field
 * with a more flexible status system:
 * - 'pending': Heat is configured but not started
 * - 'active': Heat is currently running
 * - 'finished': Heat has been completed
 * - 'cancelled': Heat was cancelled
 * - 'rescheduled': Heat was rescheduled to another time
 * 
 * Run this file once to migrate the database.
 */

require_once '../../includes/db.php';

echo "=== Heat Status Column Migration ===\n\n";

try {
    // Check if status column already exists
    $checkColumn = $pdo->query("SHOW COLUMNS FROM event_heat_settings LIKE 'status'");
    
    if ($checkColumn->rowCount() > 0) {
        echo "✓ Status column already exists. Skipping migration.\n";
        exit(0);
    }
    
    echo "Step 1: Adding 'status' column to event_heat_settings table...\n";
    
    // Add status column with default value
    $pdo->exec("
        ALTER TABLE event_heat_settings 
        ADD COLUMN status ENUM('pending', 'active', 'finished', 'cancelled', 'rescheduled') 
        NOT NULL DEFAULT 'pending'
        AFTER is_active
    ");
    
    echo "✓ Status column added successfully.\n\n";
    
    echo "Step 2: Migrating existing is_active data to status...\n";
    
    // Migrate existing data: is_active = 1 -> status = 'active'
    $updateActive = $pdo->exec("
        UPDATE event_heat_settings 
        SET status = 'active' 
        WHERE is_active = 1
    ");
    
    echo "✓ Migrated {$updateActive} active heats.\n";
    
    // Set all inactive heats to 'pending' (default already set)
    $updatePending = $pdo->exec("
        UPDATE event_heat_settings 
        SET status = 'pending' 
        WHERE is_active = 0 AND status = 'pending'
    ");
    
    echo "✓ Set {$updatePending} inactive heats to 'pending' status.\n\n";
    
    echo "Step 3: Adding index on status column for performance...\n";
    
    $pdo->exec("
        ALTER TABLE event_heat_settings 
        ADD INDEX idx_status (status)
    ");
    
    echo "✓ Index added successfully.\n\n";
    
    echo "Step 4: Creating composite index for event_id + status queries...\n";
    
    $pdo->exec("
        ALTER TABLE event_heat_settings 
        ADD INDEX idx_event_status (event_id, status)
    ");
    
    echo "✓ Composite index added successfully.\n\n";
    
    echo "=== Migration completed successfully! ===\n\n";
    echo "Note: The is_active column is kept for backward compatibility.\n";
    echo "You can remove it later after verifying everything works correctly.\n";
    echo "\nTo remove is_active column (OPTIONAL - do this after testing):\n";
    echo "ALTER TABLE event_heat_settings DROP COLUMN is_active;\n";
    
} catch (PDOException $e) {
    echo "✗ Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}
