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

echo "=== Setting up Location Mapping ===\n\n";

// Check if pickup_locations table exists
$tables = $pdo->query("SHOW TABLES LIKE 'pickup_locations'")->fetch();

if (!$tables) {
    echo "1. Creating pickup_locations table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS pickup_locations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        address TEXT,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✓ pickup_locations table created\n\n";
}

echo "2. Syncing menu locations to pickup_locations...\n";

// Get menu locations
$menuLocations = $pdo->query("SELECT id, name, address, display_order FROM locations ORDER BY display_order")->fetchAll(PDO::FETCH_ASSOC);

$stmt = $pdo->prepare("INSERT INTO pickup_locations (id, name, address, sort_order, active) VALUES (?, ?, ?, ?, 1) ON DUPLICATE KEY UPDATE name = VALUES(name), address = VALUES(address), sort_order = VALUES(sort_order)");

foreach ($menuLocations as $loc) {
    $stmt->execute([$loc['id'], $loc['name'], $loc['address'], $loc['display_order']]);
    echo "✓ {$loc['name']}\n";
}

echo "\n3. Verifying sync...\n";
$count = $pdo->query("SELECT COUNT(*) FROM pickup_locations")->fetchColumn();
echo "Total pickup locations: $count\n";

echo "\n4. Location mapping:\n";
$locations = $pdo->query("SELECT l.id, l.name, pl.name as pickup_name 
    FROM locations l 
    LEFT JOIN pickup_locations pl ON l.id = pl.id 
    ORDER BY l.display_order")->fetchAll(PDO::FETCH_ASSOC);

foreach ($locations as $loc) {
    echo "  [{$loc['id']}] {$loc['name']} → {$loc['pickup_name']}\n";
}

echo "\n✅ Location mapping complete!\n";
echo "Orders and products can now use the same location IDs as menu system.\n";
