<?php
$conn = new mysqli('localhost', 'root', '', 'granny_restaurant');
if ($conn->connect_error) die('Connection failed: ' . $conn->connect_error);

echo "=== RECIPES IN DATABASE ===\n";
$result = $conn->query('SELECT r.id, r.menu_item_id, r.name, r.preparation_time, r.difficulty_level, COUNT(ri.id) as ingredient_count FROM recipes r LEFT JOIN recipe_ingredients ri ON r.id = ri.recipe_id GROUP BY r.id');
while ($row = $result->fetch_assoc()) {
    echo "ID: {$row['id']}, Menu Item ID: {$row['menu_item_id']}, Name: {$row['name']}, Prep: {$row['preparation_time']}min, Difficulty: {$row['difficulty_level']}, Ingredients: {$row['ingredient_count']}\n";
}

echo "\n=== MENU ITEMS WITH RECIPES ===\n";
$result = $conn->query('SELECT m.id, m.item_name, r.name as recipe_name FROM menu_items m LEFT JOIN recipes r ON m.id = r.menu_item_id WHERE r.id IS NOT NULL');
while ($row = $result->fetch_assoc()) {
    echo "Menu Item: {$row['item_name']}, Recipe: {$row['recipe_name']}\n";
}

echo "\n=== SAMPLE RECIPE WITH INGREDIENTS ===\n";
$result = $conn->query('SELECT r.id, r.name, ri.ingredient_id, i.name as ingredient_name, ri.quantity_required, ri.unit FROM recipes r LEFT JOIN recipe_ingredients ri ON r.id = ri.recipe_id LEFT JOIN ingredients i ON ri.ingredient_id = i.id WHERE r.id = 1');
while ($row = $result->fetch_assoc()) {
    if ($row['ingredient_name']) {
        echo "Recipe: {$row['name']}, Ingredient: {$row['ingredient_name']}, Qty: {$row['quantity_required']} {$row['unit']}\n";
    }
}
