<?php
/**
 * Setup menu system database tables
 */

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

try {
    echo "Creating menu system tables...\n\n";
    
    // 1. Locations table
    echo "1. Creating locations table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        slug VARCHAR(100) NOT NULL UNIQUE,
        address TEXT,
        phone VARCHAR(20),
        is_active TINYINT(1) DEFAULT 1,
        display_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_slug (slug),
        INDEX idx_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✓ Locations table created\n\n";
    
    // 2. Menu items table
    echo "2. Creating menu_items table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS menu_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(50),
        category_id INT,
        name_pl VARCHAR(255) NOT NULL,
        name_en VARCHAR(255),
        after_name_pl VARCHAR(255),
        after_name_en VARCHAR(255),
        description_pl TEXT,
        description_en TEXT,
        image_url VARCHAR(500),
        price_1_prefix_pl VARCHAR(50),
        price_1 DECIMAL(10,2),
        price_1_prefix_en VARCHAR(50),
        price_2_prefix_pl VARCHAR(50),
        price_2 DECIMAL(10,2),
        price_2_prefix_en VARCHAR(50),
        price_3_prefix_pl VARCHAR(50),
        price_3 DECIMAL(10,2),
        price_3_prefix_en VARCHAR(50),
        display_order DECIMAL(10,2) DEFAULT 0,
        font_size VARCHAR(20),
        custom_css TEXT,
        border_style VARCHAR(100),
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
        INDEX idx_category (category_id),
        INDEX idx_code (code),
        INDEX idx_order (display_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✓ Menu items table created\n\n";
    
    // 3. Menu item locations junction table
    echo "3. Creating menu_item_locations table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS menu_item_locations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        menu_item_id INT NOT NULL,
        location_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
        FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
        UNIQUE KEY unique_item_location (menu_item_id, location_id),
        INDEX idx_menu_item (menu_item_id),
        INDEX idx_location (location_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✓ Menu item locations junction table created\n\n";
    
    // 4. Insert default locations
    echo "4. Inserting default locations...\n";
    $defaultLocations = [
        ['name' => 'Słoneczna 1', 'slug' => 'sloneczna-1', 'order' => 1],
        ['name' => 'Witkiewicza 2', 'slug' => 'witkiewicza-2', 'order' => 2],
        ['name' => 'Krupówki 4a', 'slug' => 'krupowki-4a', 'order' => 3],
        ['name' => 'Kościelisko', 'slug' => 'koscielisko', 'order' => 4],
        ['name' => 'Kasprusie 34', 'slug' => 'kasprusie-34', 'order' => 5],
        ['name' => 'Chramcówki 4', 'slug' => 'chramcowki-4', 'order' => 6],
        ['name' => 'Pardałówka', 'slug' => 'pardalowka', 'order' => 7]
    ];
    
    $stmt = $pdo->prepare("INSERT IGNORE INTO locations (name, slug, display_order) VALUES (?, ?, ?)");
    foreach ($defaultLocations as $location) {
        $stmt->execute([$location['name'], $location['slug'], $location['order']]);
        echo "  + {$location['name']}\n";
    }
    echo "✓ Default locations inserted\n\n";
    
    echo "✅ Menu system setup complete!\n";
    echo "\nNext steps:\n";
    echo "1. Access menu management at /menu/items\n";
    echo "2. Import existing menu data from CSV\n";
    echo "3. Manage locations at /menu/locations\n";
    
} catch (PDOException $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}
