<?php
/**
 * Heat Format Settings Migration
 * Adds comprehensive format-related settings to event_heat_settings table
 */

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

// Set content type for web output
header('Content-Type: text/plain');

try {
    echo "Starting Heat Format Settings Migration...\n";
    
    // Check if migration is needed
    $checkStmt = $pdo->query("SHOW COLUMNS FROM event_heat_settings LIKE 'format_name'");
    if ($checkStmt->rowCount() > 0) {
        echo "Migration already applied - format_name column exists.\n";
        exit(0);
    }
    
    // Start transaction
    $pdo->beginTransaction();
    
    // Execute migration statements individually
    echo "Adding format_name column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN format_name VARCHAR(255) DEFAULT NULL COMMENT 'Heat-specific format name override'");
    
    echo "Adding discipline column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN discipline VARCHAR(100) DEFAULT NULL COMMENT 'Sport discipline for this heat'");
    
    echo "Adding difficulty_level column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN difficulty_level VARCHAR(50) DEFAULT NULL COMMENT 'Difficulty level'");
    
    echo "Adding course_length column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN course_length INT DEFAULT 0 COMMENT 'Course length in meters'");
    
    echo "Adding time_limit column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN time_limit INT DEFAULT 0 COMMENT 'Time limit per run in seconds'");
    
    echo "Adding weather_dependent column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN weather_dependent BOOLEAN DEFAULT 0 COMMENT 'Whether heat is weather dependent'");
    
    echo "Adding format_description column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN format_description TEXT DEFAULT NULL COMMENT 'Detailed format description'");
    
    echo "Adding scoring_method column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN scoring_method VARCHAR(100) DEFAULT 'standard' COMMENT 'Scoring method'");
    
    echo "Adding required_figures column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN required_figures JSON DEFAULT NULL COMMENT 'JSON array of required figures'");
    
    echo "Adding judges_required column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN judges_required INT DEFAULT 5 COMMENT 'Number of judges required'");
    
    echo "Adding scale_min column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN scale_min DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Minimum score value'");
    
    echo "Adding scale_max column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN scale_max DECIMAL(5,2) DEFAULT 100.00 COMMENT 'Maximum score value'");
    
    echo "Adding precision_decimal column...\n";
    $pdo->exec("ALTER TABLE event_heat_settings ADD COLUMN precision_decimal INT DEFAULT 0 COMMENT 'Number of decimal places for scores'");
    
    // Add indexes
    echo "Creating indexes...\n";
    try {
        $pdo->exec("CREATE INDEX idx_event_heat_discipline ON event_heat_settings(discipline)");
        $pdo->exec("CREATE INDEX idx_event_heat_difficulty ON event_heat_settings(difficulty_level)");
        $pdo->exec("CREATE INDEX idx_event_heat_scoring ON event_heat_settings(scoring_method)");
        $pdo->exec("CREATE INDEX idx_event_heat_weather ON event_heat_settings(weather_dependent)");
    } catch (PDOException $e) {
        echo "Index creation warning (may already exist): " . $e->getMessage() . "\n";
    }
    
    // Populate default values from event settings
    echo "Populating default values from event settings...\n";
    
    $updateStmt = $pdo->prepare("
        UPDATE event_heat_settings ehs
        INNER JOIN events e ON ehs.event_id = e.id
        LEFT JOIN scoring_formats sf ON e.scoring_format = sf.format_id
        LEFT JOIN scoring_format_judges sfj ON sf.format_id = sfj.format_id
        SET 
            ehs.format_name = COALESCE(ehs.format_name, sf.name),
            ehs.discipline = COALESCE(ehs.discipline, sf.sport),
            ehs.scale_min = COALESCE(ehs.scale_min, sfj.scale_min, 0),
            ehs.scale_max = COALESCE(ehs.scale_max, sfj.scale_max, 100),
            ehs.precision_decimal = COALESCE(ehs.precision_decimal, sfj.precision_decimal, 0),
            ehs.judges_required = COALESCE(ehs.judges_required, 5)
        WHERE ehs.format_name IS NULL OR ehs.discipline IS NULL
    ");
    
    $updateStmt->execute();
    $updatedRows = $updateStmt->rowCount();
    
    // Commit transaction
    $pdo->commit();
    
    echo "Migration completed successfully!\n";
    echo "Updated {$updatedRows} heat settings with default values.\n";
    echo "\nNew columns added:\n";
    echo "- format_name: Heat-specific format name override\n";
    echo "- discipline: Sport discipline for heat\n";
    echo "- difficulty_level: Competition difficulty level\n";
    echo "- course_length: Course length in meters\n";
    echo "- time_limit: Time limit per run in seconds\n";
    echo "- weather_dependent: Weather dependency flag\n";
    echo "- format_description: Detailed format description\n";
    echo "- scoring_method: Scoring methodology\n";
    echo "- required_figures: JSON array of required figures\n";
    echo "- judges_required: Number of judges needed\n";
    echo "- scale_min/scale_max: Score range\n";
    echo "- precision_decimal: Score decimal precision\n";
    
} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    
    echo "Migration failed: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
    exit(1);
}
?>
