<?php
/**
 * Replace menu items with new CSV data
 */

require_once __DIR__ . '/bootstrap/db.php';

echo "<h2>Replacing Menu Items</h2>\n";

try {
    // Read CSV file
    $csvFile = __DIR__ . '/menu-lokale-2025-12-11-new.csv';
    if (!file_exists($csvFile)) {
        die("CSV file not found: $csvFile\n");
    }
    
    $handle = fopen($csvFile, 'r');
    if (!$handle) {
        die("Cannot open CSV file\n");
    }
    
    // Read header row
    $header = fgetcsv($handle);
    echo "CSV Columns: " . implode(', ', $header) . "<br>\n";
    
    // Map column names to indices
    $cols = array_flip($header);
    
    // Start transaction
    $pdo->beginTransaction();
    
    // Delete all existing menu items and their location associations
    echo "<br><strong>Deleting existing menu items...</strong><br>\n";
    $pdo->exec("DELETE FROM menu_item_locations");
    $pdo->exec("DELETE FROM menu_items");
    echo "✓ Cleared menu_items and menu_item_locations tables<br>\n";
    
    // Get all categories
    $categoriesStmt = $pdo->query("SELECT id, name FROM menu_categories");
    $categories = [];
    while ($row = $categoriesStmt->fetch(PDO::FETCH_ASSOC)) {
        $categories[trim($row['name'])] = $row['id'];
    }
    echo "<br><strong>Available categories:</strong> " . implode(', ', array_keys($categories)) . "<br>\n";
    
    // Get all locations
    $locationsStmt = $pdo->query("SELECT id, name FROM locations WHERE is_active = 1");
    $locations = [];
    while ($row = $locationsStmt->fetch(PDO::FETCH_ASSOC)) {
        $locations[trim($row['name'])] = $row['id'];
    }
    echo "<strong>Available locations:</strong> " . implode(', ', array_keys($locations)) . "<br>\n";
    
    // Prepare insert statements
    $itemStmt = $pdo->prepare("
        INSERT INTO menu_items (
            code, menu_category_id, name_pl, name_en,
            description_pl, description_en,
            price_1_prefix_pl, price_1, price_1_prefix_en,
            price_2_prefix_pl, price_2, price_2_prefix_en,
            price_3_prefix_pl, price_3, price_3_prefix_en,
            image_url, display_order, is_active
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
    ");
    
    $locationStmt = $pdo->prepare("
        INSERT INTO menu_item_locations (menu_item_id, location_id) VALUES (?, ?)
    ");
    
    $imported = 0;
    $skipped = 0;
    $displayOrder = 1;
    
    echo "<br><strong>Importing items...</strong><br>\n";
    
    while (($row = fgetcsv($handle)) !== false) {
        // Skip empty rows
        if (empty($row[0]) && empty($row[1]) && empty($row[2])) {
            $skipped++;
            continue;
        }
        
        $categoryName = trim($row[$cols['Kategoria']] ?? '');
        $name = trim($row[$cols['Nazwa']] ?? '');
        
        if (empty($name)) {
            echo "⚠ Skipping row - no name<br>\n";
            $skipped++;
            continue;
        }
        
        // Find category ID
        $categoryId = null;
        if (!empty($categoryName) && isset($categories[$categoryName])) {
            $categoryId = $categories[$categoryName];
        }
        
        if (!$categoryId) {
            echo "⚠ Skipping '$name' - category '$categoryName' not found<br>\n";
            $skipped++;
            continue;
        }
        
        // Extract data
        $code = trim($row[$cols['Code']] ?? '');
        $nameEn = trim($row[$cols['Nazwa en']] ?? '');
        $descriptionPl = trim($row[$cols['Opis']] ?? '');
        $descriptionEn = trim($row[$cols['Opis en']] ?? '');
        $imageUrl = trim($row[$cols['Plik']] ?? '');
        
        // Prices
        $price1Prefix = trim($row[$cols['Cena 1 prefix']] ?? '');
        $price1 = trim($row[$cols['Cena 1']] ?? '');
        $price1PrefixEn = trim($row[$cols['Cena 1 prefix en']] ?? '');
        
        $price2Prefix = trim($row[$cols['Cena 2 prefix']] ?? '');
        $price2 = trim($row[$cols['Cena 2']] ?? '');
        $price2PrefixEn = trim($row[$cols['Cena 2 prefix en']] ?? '');
        
        $price3Prefix = trim($row[$cols['Cena 3 prefix']] ?? '');
        $price3 = trim($row[$cols['Cena 3']] ?? '');
        $price3PrefixEn = trim($row[$cols['Cena 3 prefix en']] ?? '');
        
        // Clean prices - remove "zł" and convert to decimal
        $price1 = $price1 ? (float)str_replace(['zł', ' '], '', $price1) : null;
        $price2 = $price2 ? (float)str_replace(['zł', ' '], '', $price2) : null;
        $price3 = $price3 ? (float)str_replace(['zł', ' '], '', $price3) : null;
        
        // Insert menu item
        $itemStmt->execute([
            $code ?: null,
            $categoryId,
            $name,
            $nameEn ?: null,
            $descriptionPl ?: null,
            $descriptionEn ?: null,
            $price1Prefix ?: null,
            $price1,
            $price1PrefixEn ?: null,
            $price2Prefix ?: null,
            $price2,
            $price2PrefixEn ?: null,
            $price3Prefix ?: null,
            $price3,
            $price3PrefixEn ?: null,
            $imageUrl ?: null,
            $displayOrder++
        ]);
        
        $itemId = $pdo->lastInsertId();
        
        // Map locations
        $itemLocations = [];
        foreach ($locations as $locationName => $locationId) {
            // Check if location column exists and has value "tak"
            if (isset($cols[$locationName])) {
                $value = trim($row[$cols[$locationName]] ?? '');
                if (strtolower($value) === 'tak') {
                    $locationStmt->execute([$itemId, $locationId]);
                    $itemLocations[] = $locationName;
                }
            }
        }
        
        $locationsList = !empty($itemLocations) ? implode(', ', $itemLocations) : 'none';
        echo "✓ Imported: <strong>$name</strong> (category: $categoryName, locations: $locationsList)<br>\n";
        $imported++;
    }
    
    fclose($handle);
    
    // Commit transaction
    $pdo->commit();
    
    echo "<br><hr><br>";
    echo "<h3>Import Complete!</h3>\n";
    echo "<strong>Items imported:</strong> $imported<br>\n";
    echo "<strong>Items skipped:</strong> $skipped<br>\n";
    echo "<br><a href='/samanta_crm/menu/items'>View Menu Items</a>\n";
    
} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "<br><strong>ERROR:</strong> " . $e->getMessage() . "<br>\n";
    echo "<pre>" . $e->getTraceAsString() . "</pre>\n";
}
