<?php
/**
 * Database migration script to fix incorrect image URLs in items table
 * This script will update all image URLs that contain incorrect paths
 */

require_once 'backend/config/database.php';

echo "Starting image URL migration...\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "Connected to database successfully.\n";
    
    // First, let's see how many items have incorrect URLs
    $check_query = "SELECT id, picture FROM items WHERE picture LIKE '%/var/www/html/%' OR picture LIKE '%/api/%'";
    $check_stmt = $db->prepare($check_query);
    $check_stmt->execute();
    
    $incorrect_urls = $check_stmt->fetchAll(PDO::FETCH_ASSOC);
    $total_items = count($incorrect_urls);
    
    echo "Found {$total_items} items with incorrect image URLs.\n";
    
    if ($total_items === 0) {
        echo "No items need fixing. Migration complete.\n";
        exit(0);
    }
    
    // Update each incorrect URL
    $update_query = "UPDATE items SET picture = ? WHERE id = ?";
    $update_stmt = $db->prepare($update_query);
    
    $fixed_count = 0;
    
    foreach ($incorrect_urls as $item) {
        $old_url = $item['picture'];
        $new_url = $old_url;
        
        // Remove /var/www/html/ from the path
        if (strpos($new_url, '/var/www/html/') !== false) {
            $new_url = str_replace('/var/www/html/', '', $new_url);
        }
        
        // Remove /api/ from the beginning if present
        if (strpos($new_url, '/api/') !== false) {
            $new_url = str_replace('/api/', '', $new_url);
        }
        
        // Only update if the URL actually changed
        if ($new_url !== $old_url) {
            echo "Fixing item ID {$item['id']}: {$old_url} -> {$new_url}\n";
            $update_stmt->execute([$new_url, $item['id']]);
            $fixed_count++;
        }
    }
    
    echo "Successfully fixed {$fixed_count} image URLs.\n";
    echo "Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "Error during migration: " . $e->getMessage() . "\n";
    exit(1);
}
?>