<?php
/**
 * COMPLETE SOLUTION IN ONE GO
 * 1. Creates missing tables
 * 2. Fixes image paths in database
 * 3. Ready to use
 */

require_once __DIR__ . '/db_config.php';

echo "<!DOCTYPE html>
<html>
<head>
<meta charset='UTF-8'>
<title>Complete Setup</title>
<style>
body { font-family: Arial; margin: 20px; background: #f5f5f5; }
h1 { color: #333; }
h2 { color: #0066cc; margin-top: 30px; }
.success { color: green; }
.error { color: red; }
.info { color: blue; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; }
table td, table th { border: 1px solid #ddd; padding: 10px; text-align: left; }
table th { background: #0066cc; color: white; }
.step { background: white; padding: 15px; margin: 10px 0; border-left: 4px solid #0066cc; }
</style>
</head>
<body>
<h1>🚀 Complete Database Setup & Image Fix</h1>
<p><strong>Database:</strong> $DB_NAME | <strong>Environment:</strong> " . ($is_local ? "LOCALHOST" : "CPANEL") . "</p>
<hr>";

// ===== STEP 1: CREATE MISSING TABLES =====
echo "<div class='step'>";
echo "<h2>Step 1: Creating Missing Tables</h2>";

$created = [];
$already_exist = [];

// Create products table
$check = $conn->query("SHOW TABLES LIKE 'products'");
if (!$check || $check->num_rows == 0) {
    $sql = "CREATE TABLE IF NOT EXISTS `products` (
        `id` INT PRIMARY KEY AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
        `description` LONGTEXT,
        `price` DECIMAL(10, 2),
        `quantity` INT DEFAULT 0,
        `category_id` INT,
        `image` VARCHAR(255),
        `gallery_images` LONGTEXT,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    if ($conn->query($sql)) {
        $created[] = 'products';
        echo "<p class='success'>✅ Created 'products' table</p>";
    } else {
        echo "<p class='error'>❌ Error creating products: " . $conn->error . "</p>";
    }
} else {
    $already_exist[] = 'products';
    echo "<p class='info'>ℹ️ 'products' table already exists</p>";
}

// Create categories table
$check = $conn->query("SHOW TABLES LIKE 'categories'");
if (!$check || $check->num_rows == 0) {
    $sql = "CREATE TABLE IF NOT EXISTS `categories` (
        `id` INT PRIMARY KEY AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL UNIQUE,
        `description` LONGTEXT,
        `image` VARCHAR(255),
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    if ($conn->query($sql)) {
        $created[] = 'categories';
        echo "<p class='success'>✅ Created 'categories' table</p>";
    } else {
        echo "<p class='error'>❌ Error creating categories: " . $conn->error . "</p>";
    }
} else {
    $already_exist[] = 'categories';
    echo "<p class='info'>ℹ️ 'categories' table already exists</p>";
}

echo "</div>";

// ===== STEP 2: FIX IMAGE PATHS =====
echo "<div class='step'>";
echo "<h2>Step 2: Normalizing Image Paths</h2>";

$stats = [
    'products_fixed' => 0,
    'blogs_fixed' => 0,
    'carousel_fixed' => false
];

// Fix product images if products table exists
$check = $conn->query("SHOW TABLES LIKE 'products'");
if ($check && $check->num_rows > 0) {
    $result = $conn->query("SELECT id, image FROM products LIMIT 1000");
    if ($result && $result->num_rows > 0) {
        echo "<p>Scanning " . $result->num_rows . " products...</p>";
        
        while ($product = $result->fetch_assoc()) {
            $image = $product['image'] ?? '';
            if (!empty($image) && strpos($image, 'http') === 0) {
                $new_image = basename($image);
                $stmt = $conn->prepare("UPDATE products SET image = ? WHERE id = ?");
                $stmt->bind_param("si", $new_image, $product['id']);
                if ($stmt->execute()) {
                    $stats['products_fixed']++;
                }
                $stmt->close();
            }
        }
        
        if ($stats['products_fixed'] > 0) {
            echo "<p class='success'>✅ Fixed $stats[products_fixed] product images</p>";
        } else {
            echo "<p class='info'>ℹ️ No product images needed fixing</p>";
        }
    }
} else {
    echo "<p class='info'>ℹ️ Skipping products (table not found)</p>";
}

// Fix blog images if blogs table exists
$check = $conn->query("SHOW TABLES LIKE 'blogs'");
if ($check && $check->num_rows > 0) {
    $result = $conn->query("SELECT id, featured_image FROM blogs LIMIT 1000");
    if ($result && $result->num_rows > 0) {
        echo "<p>Scanning " . $result->num_rows . " blogs...</p>";
        
        while ($blog = $result->fetch_assoc()) {
            $image = $blog['featured_image'] ?? '';
            if (!empty($image) && strpos($image, 'http') === 0) {
                $new_image = basename($image);
                $stmt = $conn->prepare("UPDATE blogs SET featured_image = ? WHERE id = ?");
                $stmt->bind_param("si", $new_image, $blog['id']);
                if ($stmt->execute()) {
                    $stats['blogs_fixed']++;
                }
                $stmt->close();
            }
        }
        
        if ($stats['blogs_fixed'] > 0) {
            echo "<p class='success'>✅ Fixed $stats[blogs_fixed] blog images</p>";
        } else {
            echo "<p class='info'>ℹ️ No blog images needed fixing</p>";
        }
    }
} else {
    echo "<p class='info'>ℹ️ Skipping blogs (table not found)</p>";
}

echo "</div>";

// ===== STEP 3: SUMMARY & NEXT STEPS =====
echo "<div class='step'>";
echo "<h2>Step 3: Summary</h2>";

echo "<table>";
echo "<tr><th>Component</th><th>Status</th></tr>";
echo "<tr><td>Tables Created</td><td class='success'>✅ " . count($created) . " (" . implode(', ', $created) . ")</td></tr>";
echo "<tr><td>Tables Existing</td><td class='info'>ℹ️ " . count($already_exist) . " (" . implode(', ', $already_exist) . ")</td></tr>";
echo "<tr><td>Product Images Fixed</td><td class='success'>✅ $stats[products_fixed]</td></tr>";
echo "<tr><td>Blog Images Fixed</td><td class='success'>✅ $stats[blogs_fixed]</td></tr>";
echo "</table>";

echo "<h2>✅ You're All Set!</h2>";
echo "<p><strong>Next steps:</strong></p>";
echo "<ol>";
echo "<li><a href='index.php' style='color: #0066cc; text-decoration: none;'><strong>View Homepage</strong></a> - Check if everything loads</li>";
echo "<li><a href='shop.php' style='color: #0066cc; text-decoration: none;'><strong>Check Shop Page</strong></a> - View products</li>";
echo "<li><a href='blogs.php' style='color: #0066cc; text-decoration: none;'><strong>Check Blog Page</strong></a> - View blogs</li>";
echo "</ol>";

echo "</div>";

echo "</body></html>";

$conn->close();
?>
