<?php
$conn = new mysqli('localhost', 'root', '', 'granny_restaurant');

echo "=== ALL RECIPES WITH DETAILS ===\n\n";
$result = $conn->query('SELECT r.id, r.menu_item_id, r.name, m.item_name, r.preparation_time, r.difficulty_level, r.yield_quantity, COUNT(ri.id) as ingredient_count FROM recipes r LEFT JOIN menu_items m ON r.menu_item_id = m.id LEFT JOIN recipe_ingredients ri ON r.id = ri.recipe_id GROUP BY r.id ORDER BY r.difficulty_level DESC, r.name ASC');

while ($row = $result->fetch_assoc()) {
    echo "✓ Recipe: " . $row['name'] . "\n";
    echo "  Menu Item: " . $row['item_name'] . "\n";
    echo "  Difficulty: " . strtoupper($row['difficulty_level']) . " | Prep Time: " . $row['preparation_time'] . " min | Yield: " . $row['yield_quantity'] . " portions\n";
    echo "  Ingredients: " . $row['ingredient_count'] . "\n\n";
}

echo "\n=== SUMMARY ===\n";
$result = $conn->query('SELECT COUNT(DISTINCT id) as total FROM recipes');
$row = $result->fetch_assoc();
echo "Total Recipes: " . $row['total'] . "\n";

$result = $conn->query('SELECT COUNT(DISTINCT name) as unique_recipes FROM recipes');
$row = $result->fetch_assoc();
echo "Unique Recipe Names: " . $row['unique_recipes'] . "\n";

$result = $conn->query('SELECT COUNT(DISTINCT id) as total FROM ingredients');
$row = $result->fetch_assoc();
echo "Total Ingredients: " . $row['total'] . "\n";
