<?php
/**
 * Migration Script: Enable Multiple Source Heats
 * 
 * This script modifies the event_heat_settings table to allow
 * storing multiple source heats as a JSON array instead of a single integer.
 */

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

echo "<h1>Flow Source Heat Migration</h1>";
echo "<p>Converting flow_source_heat column to support multiple heats...</p>";

try {
    // Start transaction
    $pdo->beginTransaction();
    
    // Step 1: Check current column type
    $check = $pdo->query("SHOW COLUMNS FROM event_heat_settings LIKE 'flow_source_heat'")->fetch(PDO::FETCH_ASSOC);
    echo "<p><strong>Current column type:</strong> " . htmlspecialchars($check['Type']) . "</p>";
    
    // Step 2: Alter column to VARCHAR
    echo "<p>Changing column type to VARCHAR(255)...</p>";
    $pdo->exec("
        ALTER TABLE `event_heat_settings` 
        MODIFY COLUMN `flow_source_heat` VARCHAR(255) DEFAULT NULL 
        COMMENT 'JSON array of source heat numbers or single heat number'
    ");
    echo "<p style='color: green;'>✓ Column type changed successfully</p>";
    
    // Step 3: Convert existing data to JSON format
    echo "<p>Converting existing single values to JSON arrays...</p>";
    $result = $pdo->exec("
        UPDATE `event_heat_settings` 
        SET `flow_source_heat` = CONCAT('[', `flow_source_heat`, ']')
        WHERE `flow_source_heat` IS NOT NULL 
        AND `flow_source_heat` != ''
        AND `flow_source_heat` NOT LIKE '[%'
        AND `flow_source_heat` REGEXP '^[0-9]+$'
    ");
    echo "<p style='color: green;'>✓ Converted $result existing records to JSON format</p>";
    
    // Step 4: Verify the changes
    $verify = $pdo->query("SELECT heat_number, flow_source_heat FROM event_heat_settings WHERE flow_source_heat IS NOT NULL")->fetchAll(PDO::FETCH_ASSOC);
    echo "<h3>Updated Records:</h3>";
    if ($verify) {
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>Heat Number</th><th>Flow Source Heat(s)</th></tr>";
        foreach ($verify as $row) {
            echo "<tr><td>{$row['heat_number']}</td><td>" . htmlspecialchars($row['flow_source_heat']) . "</td></tr>";
        }
        echo "</table>";
    } else {
        echo "<p>No records with flow_source_heat found.</p>";
    }
    
    // Commit transaction
    $pdo->commit();
    
    echo "<h2 style='color: green;'>✓ Migration completed successfully!</h2>";
    echo "<p><a href='admin_heat_config.php'>Go to Heat Configuration</a></p>";
    
} catch (Exception $e) {
    // Rollback on error
    $pdo->rollBack();
    echo "<h2 style='color: red;'>✗ Migration failed!</h2>";
    echo "<p style='color: red;'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "<p>The database has been rolled back to its previous state.</p>";
}
?>
