<?php
/**
 * Create separate menu_categories table and migrate data
 */

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

try {
    echo "Creating separate menu categories system...\n\n";
    
    // 1. Create menu_categories table
    echo "1. Creating menu_categories table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS menu_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        sort_order INT DEFAULT 0,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_sort_order (sort_order),
        INDEX idx_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✓ menu_categories table created\n\n";
    
    // 2. Insert menu categories
    echo "2. Inserting menu categories...\n";
    $categories = [
        'Kawy',
        'Herbaty',
        'Matcha',
        'Kawy mrożone',
        'Soki',
        'Soki ze świeżych owoców',
        'Smoothies',
        'Gorące czekolady',
        'Shakes',
        'Napoje zimne',
        'Dodatki',
        'Desery',
        'Lody',
        'Dodatki lody',
        'Kanapki',
        'Śniadania',
        'Tosty',
        'Naleśniki',
        'Gofry',
        'Alkohole',
        'Alkohole na ciepło',
        'Piwa',
        'Drinki',
        'Nalewki',
        'Zimowe Specjały',
        'Napoje i Lemoniady',
        'Menu dla dzieci',
        'Ciastka bezglutenowe'
    ];
    
    $stmt = $pdo->prepare("INSERT INTO menu_categories (name, sort_order) VALUES (?, ?)");
    foreach ($categories as $index => $category) {
        $stmt->execute([$category, $index]);
        echo "✓ {$category}\n";
    }
    
    // 3. Update menu_items table to reference menu_categories
    echo "\n3. Updating menu_items table...\n";
    
    // Rename old column
    $pdo->exec("ALTER TABLE menu_items CHANGE COLUMN category_id old_category_id INT");
    
    // Add new column
    $pdo->exec("ALTER TABLE menu_items ADD COLUMN menu_category_id INT AFTER code");
    
    // Update foreign key
    $pdo->exec("ALTER TABLE menu_items 
        ADD CONSTRAINT fk_menu_category 
        FOREIGN KEY (menu_category_id) REFERENCES menu_categories(id) ON DELETE SET NULL");
    
    echo "✓ menu_items table updated\n\n";
    
    echo "✅ Successfully created separate menu categories system!\n";
    echo "\nNote: old_category_id column preserved for reference. You can remove it later.\n";
    
} catch (PDOException $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}
