<?php
/**
 * Database Migration: Update hero_slider table schema
 * Migrates from single slide_image to dual slide_bg_image + slide_hero_image
 */

require_once 'config.php';

$migrations = [];
$results = [];

// Check if table exists
$tableCheckResult = $conn->query("SHOW TABLES LIKE 'hero_slider'");
$tableExists = $tableCheckResult && $tableCheckResult->num_rows > 0;

if (!$tableExists) {
    // Create table from scratch
    $sql = file_get_contents('create_hero_slider_table.sql');
    if ($conn->multi_query($sql)) {
        do {
            // Store results while consuming them
            if ($result = $conn->store_result()) {
                $result->free();
            }
        } while ($conn->next_result());
        $results[] = ['status' => 'success', 'message' => 'Hero slider table created successfully'];
    } else {
        $results[] = ['status' => 'error', 'message' => 'Failed to create table: ' . $conn->error];
    }
} else {
    // Table exists, perform migration
    
    // Check if slide_bg_image column exists
    $bgImageExists = $conn->query("SHOW COLUMNS FROM hero_slider LIKE 'slide_bg_image'");
    $bgImageFound = $bgImageExists && $bgImageExists->num_rows > 0;
    
    if (!$bgImageFound) {
        // Add slide_bg_image column
        if ($conn->query("ALTER TABLE hero_slider ADD COLUMN slide_bg_image VARCHAR(255) AFTER stat_3_label")) {
            $results[] = ['status' => 'success', 'message' => 'Added slide_bg_image column'];
        } else {
            $results[] = ['status' => 'error', 'message' => 'Failed to add slide_bg_image: ' . $conn->error];
        }
    } else {
        $results[] = ['status' => 'info', 'message' => 'slide_bg_image column already exists'];
    }
    
    // Check if slide_hero_image column exists
    $heroImageExists = $conn->query("SHOW COLUMNS FROM hero_slider LIKE 'slide_hero_image'");
    $heroImageFound = $heroImageExists && $heroImageExists->num_rows > 0;
    
    if (!$heroImageFound) {
        // Add slide_hero_image column
        if ($conn->query("ALTER TABLE hero_slider ADD COLUMN slide_hero_image VARCHAR(255) AFTER slide_bg_image")) {
            $results[] = ['status' => 'success', 'message' => 'Added slide_hero_image column'];
        } else {
            $results[] = ['status' => 'error', 'message' => 'Failed to add slide_hero_image: ' . $conn->error];
        }
    } else {
        $results[] = ['status' => 'info', 'message' => 'slide_hero_image column already exists'];
    }
    
    // Check if old slide_image column exists and migrate data
    $oldImageExists = $conn->query("SHOW COLUMNS FROM hero_slider LIKE 'slide_image'");
    $oldImageFound = $oldImageExists && $oldImageExists->num_rows > 0;
    
    if ($oldImageFound) {
        // Copy slide_image data to slide_bg_image and remove old column
        if ($conn->query("UPDATE hero_slider SET slide_bg_image = slide_image WHERE slide_bg_image IS NULL")) {
            $results[] = ['status' => 'success', 'message' => 'Migrated data from slide_image to slide_bg_image'];
            
            // Drop old column
            if ($conn->query("ALTER TABLE hero_slider DROP COLUMN slide_image")) {
                $results[] = ['status' => 'success', 'message' => 'Removed old slide_image column'];
            } else {
                $results[] = ['status' => 'warning', 'message' => 'Could not remove old slide_image column: ' . $conn->error];
            }
        } else {
            $results[] = ['status' => 'error', 'message' => 'Failed to migrate data: ' . $conn->error];
        }
    }
}

$conn->close();

// Display results
?>
<!DOCTYPE html>
<html>
<head>
    <title>Database Migration - Hero Slider</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            border-bottom: 3px solid #109c3d;
            padding-bottom: 10px;
        }
        .result-item {
            padding: 12px;
            margin: 10px 0;
            border-left: 4px solid #ddd;
            border-radius: 4px;
        }
        .result-item.success {
            background: #d4edda;
            border-left-color: #28a745;
            color: #155724;
        }
        .result-item.error {
            background: #f8d7da;
            border-left-color: #dc3545;
            color: #721c24;
        }
        .result-item.warning {
            background: #fff3cd;
            border-left-color: #ffc107;
            color: #856404;
        }
        .result-item.info {
            background: #d1ecf1;
            border-left-color: #17a2b8;
            color: #0c5460;
        }
        .icon {
            margin-right: 8px;
            font-weight: bold;
        }
        .summary {
            margin-top: 20px;
            padding: 15px;
            background: #f9f9f9;
            border-radius: 4px;
        }
        .button {
            display: inline-block;
            margin-top: 20px;
            padding: 12px 24px;
            background: #109c3d;
            color: white;
            text-decoration: none;
            border-radius: 4px;
            font-weight: bold;
        }
        .button:hover {
            background: #0d7a2e;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>✅ Database Migration Complete</h1>
        
        <div class="results">
            <?php foreach ($results as $result): ?>
                <div class="result-item <?php echo $result['status']; ?>">
                    <span class="icon">
                        <?php 
                        switch($result['status']) {
                            case 'success': echo '✓'; break;
                            case 'error': echo '✗'; break;
                            case 'warning': echo '⚠'; break;
                            case 'info': echo 'ℹ'; break;
                        }
                        ?>
                    </span>
                    <?php echo $result['message']; ?>
                </div>
            <?php endforeach; ?>
        </div>
        
        <div class="summary">
            <strong>Migration Summary:</strong><br>
            ✓ The hero_slider table now supports dual images:<br>
            - slide_bg_image: Background image<br>
            - slide_hero_image: Hero character image<br><br>
            You can now use the updated hero slider system with both images.
        </div>
        
        <a href="admin-dashboard.php#hero-slider" class="button">Go to Admin Dashboard</a>
    </div>
</body>
</html>
