<?php
/**
 * Populate Recipes for All Menu Items
 */

$conn = new mysqli('localhost', 'root', '', 'granny_restaurant');
if ($conn->connect_error) {
    die(json_encode(['success' => false, 'message' => 'Connection failed: ' . $conn->connect_error]));
}

// Get all menu items that don't have recipes
$sql = "
    SELECT m.id, m.item_name
    FROM menu_items m
    LEFT JOIN recipes r ON m.id = r.menu_item_id
    WHERE r.id IS NULL
    ORDER BY m.item_name ASC
";

$result = $conn->query($sql);
if (!$result) {
    die(json_encode(['success' => false, 'message' => 'Query failed: ' . $conn->error]));
}

$items = $result->fetch_all(MYSQLI_ASSOC);

// Define recipe templates based on item type
$recipeTemplates = [
    'biryani' => [
        'name' => 'Biryani',
        'preparation_time' => 45,
        'difficulty_level' => 'hard',
        'yield_quantity' => 4,
        'chef_notes' => 'Layer rice and marinated protein. Cook on high heat for 3 min, then low heat for 25 min. Dum pukht style.',
        'ingredients' => [
            ['name' => 'Rice', 'qty' => 1, 'unit' => 'kg'],
            ['name' => 'Yogurt', 'qty' => 0.5, 'unit' => 'kg'],
            ['name' => 'Onion', 'qty' => 0.6, 'unit' => 'kg'],
            ['name' => 'Garlic', 'qty' => 0.08, 'unit' => 'kg'],
            ['name' => 'Ginger', 'qty' => 0.08, 'unit' => 'kg'],
            ['name' => 'Ghee (Clarified Butter)', 'qty' => 0.15, 'unit' => 'liter'],
            ['name' => 'Red Chili Powder', 'qty' => 0.02, 'unit' => 'kg'],
            ['name' => 'Turmeric Powder', 'qty' => 0.01, 'unit' => 'kg'],
            ['name' => 'Coriander Powder', 'qty' => 0.03, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.02, 'unit' => 'kg'],
            ['name' => 'Mint Leaves', 'qty' => 0.05, 'unit' => 'kg'],
            ['name' => 'Cilantro (Coriander Leaves)', 'qty' => 0.05, 'unit' => 'kg'],
        ]
    ],
    'tandoori' => [
        'name' => 'Tandoori',
        'preparation_time' => 40,
        'difficulty_level' => 'easy',
        'yield_quantity' => 4,
        'chef_notes' => 'Marinate in yogurt and spices for minimum 2 hours. Grill in tandoor or very high heat.',
        'ingredients' => [
            ['name' => 'Yogurt', 'qty' => 0.5, 'unit' => 'kg'],
            ['name' => 'Garlic', 'qty' => 0.08, 'unit' => 'kg'],
            ['name' => 'Ginger', 'qty' => 0.08, 'unit' => 'kg'],
            ['name' => 'Green Chili', 'qty' => 0.03, 'unit' => 'kg'],
            ['name' => 'Lemon', 'qty' => 0.2, 'unit' => 'kg'],
            ['name' => 'Red Chili Powder', 'qty' => 0.02, 'unit' => 'kg'],
            ['name' => 'Turmeric Powder', 'qty' => 0.015, 'unit' => 'kg'],
            ['name' => 'Coriander Powder', 'qty' => 0.03, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.015, 'unit' => 'kg'],
            ['name' => 'Olive Oil', 'qty' => 0.05, 'unit' => 'liter'],
        ]
    ],
    'chicken' => [
        'name' => 'Chicken Curry',
        'preparation_time' => 35,
        'difficulty_level' => 'medium',
        'yield_quantity' => 4,
        'chef_notes' => 'Cook chicken in tomato-cream sauce. Medium spice level. Garnish with cilantro.',
        'ingredients' => [
            ['name' => 'Chicken Breast', 'qty' => 600, 'unit' => 'g'],
            ['name' => 'Tomato', 'qty' => 0.6, 'unit' => 'kg'],
            ['name' => 'Onion', 'qty' => 0.4, 'unit' => 'kg'],
            ['name' => 'Cream', 'qty' => 0.3, 'unit' => 'liter'],
            ['name' => 'Butter', 'qty' => 0.1, 'unit' => 'kg'],
            ['name' => 'Garlic', 'qty' => 0.06, 'unit' => 'kg'],
            ['name' => 'Ginger', 'qty' => 0.06, 'unit' => 'kg'],
            ['name' => 'Red Chili Powder', 'qty' => 0.015, 'unit' => 'kg'],
            ['name' => 'Turmeric Powder', 'qty' => 0.01, 'unit' => 'kg'],
            ['name' => 'Coriander Powder', 'qty' => 0.03, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.015, 'unit' => 'kg'],
            ['name' => 'Cilantro (Coriander Leaves)', 'qty' => 0.03, 'unit' => 'kg'],
        ]
    ],
    'fish' => [
        'name' => 'Fish Curry',
        'preparation_time' => 25,
        'difficulty_level' => 'easy',
        'yield_quantity' => 4,
        'chef_notes' => 'Use fresh fish. Cook quickly to avoid overcooking. Serve hot.',
        'ingredients' => [
            ['name' => 'Fish', 'qty' => 600, 'unit' => 'g'],
            ['name' => 'Tomato', 'qty' => 0.5, 'unit' => 'kg'],
            ['name' => 'Onion', 'qty' => 0.3, 'unit' => 'kg'],
            ['name' => 'Coconut', 'qty' => 0.2, 'unit' => 'kg'],
            ['name' => 'Garlic', 'qty' => 0.04, 'unit' => 'kg'],
            ['name' => 'Ginger', 'qty' => 0.04, 'unit' => 'kg'],
            ['name' => 'Red Chili Powder', 'qty' => 0.01, 'unit' => 'kg'],
            ['name' => 'Turmeric Powder', 'qty' => 0.01, 'unit' => 'kg'],
            ['name' => 'Coriander Powder', 'qty' => 0.02, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.01, 'unit' => 'kg'],
        ]
    ],
    'mutton' => [
        'name' => 'Mutton Curry',
        'preparation_time' => 60,
        'difficulty_level' => 'hard',
        'yield_quantity' => 4,
        'chef_notes' => 'Slow cook for tender meat. Long cooking time required.',
        'ingredients' => [
            ['name' => 'Mutton', 'qty' => 700, 'unit' => 'g'],
            ['name' => 'Tomato', 'qty' => 0.7, 'unit' => 'kg'],
            ['name' => 'Onion', 'qty' => 0.5, 'unit' => 'kg'],
            ['name' => 'Yogurt', 'qty' => 0.3, 'unit' => 'kg'],
            ['name' => 'Garlic', 'qty' => 0.1, 'unit' => 'kg'],
            ['name' => 'Ginger', 'qty' => 0.1, 'unit' => 'kg'],
            ['name' => 'Red Chili Powder', 'qty' => 0.025, 'unit' => 'kg'],
            ['name' => 'Turmeric Powder', 'qty' => 0.015, 'unit' => 'kg'],
            ['name' => 'Coriander Powder', 'qty' => 0.04, 'unit' => 'kg'],
            ['name' => 'Ghee (Clarified Butter)', 'qty' => 0.1, 'unit' => 'liter'],
            ['name' => 'Salt', 'qty' => 0.02, 'unit' => 'kg'],
        ]
    ],
    'masala' => [
        'name' => 'Masala Curry',
        'preparation_time' => 30,
        'difficulty_level' => 'medium',
        'yield_quantity' => 4,
        'chef_notes' => 'Spiced curry with balanced flavors. Serve with rice or bread.',
        'ingredients' => [
            ['name' => 'Tomato', 'qty' => 0.5, 'unit' => 'kg'],
            ['name' => 'Onion', 'qty' => 0.4, 'unit' => 'kg'],
            ['name' => 'Garlic', 'qty' => 0.05, 'unit' => 'kg'],
            ['name' => 'Ginger', 'qty' => 0.05, 'unit' => 'kg'],
            ['name' => 'Cream', 'qty' => 0.2, 'unit' => 'liter'],
            ['name' => 'Red Chili Powder', 'qty' => 0.02, 'unit' => 'kg'],
            ['name' => 'Turmeric Powder', 'qty' => 0.01, 'unit' => 'kg'],
            ['name' => 'Coriander Powder', 'qty' => 0.03, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.015, 'unit' => 'kg'],
            ['name' => 'Cilantro (Coriander Leaves)', 'qty' => 0.02, 'unit' => 'kg'],
        ]
    ],
    'dosa' => [
        'name' => 'Dosa',
        'preparation_time' => 20,
        'difficulty_level' => 'easy',
        'yield_quantity' => 2,
        'chef_notes' => 'Crispy crepe made from fermented batter. Serve with chutney and sambar.',
        'ingredients' => [
            ['name' => 'Rice', 'qty' => 0.5, 'unit' => 'kg'],
            ['name' => 'Flour', 'qty' => 0.2, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.01, 'unit' => 'kg'],
            ['name' => 'Olive Oil', 'qty' => 0.05, 'unit' => 'liter'],
        ]
    ],
    'idly' => [
        'name' => 'Idly',
        'preparation_time' => 15,
        'difficulty_level' => 'easy',
        'yield_quantity' => 8,
        'chef_notes' => 'Steamed rice cake. Light and healthy. Serve with chutney.',
        'ingredients' => [
            ['name' => 'Rice', 'qty' => 0.3, 'unit' => 'kg'],
            ['name' => 'Flour', 'qty' => 0.1, 'unit' => 'kg'],
            ['name' => 'Salt', 'qty' => 0.005, 'unit' => 'kg'],
            ['name' => 'Ghee (Clarified Butter)', 'qty' => 0.03, 'unit' => 'liter'],
        ]
    ],
];

$createdCount = 0;
$results = [];

// Process each menu item
foreach ($items as $item) {
    $itemName = strtolower($item['item_name']);
    $recipeType = null;

    // Determine recipe type
    if (stripos($itemName, 'biryani') !== false || stripos($itemName, 'briyani') !== false) {
        $recipeType = 'biryani';
    } elseif (stripos($itemName, 'tandoori') !== false) {
        $recipeType = 'tandoori';
    } elseif (stripos($itemName, 'fish') !== false) {
        $recipeType = 'fish';
    } elseif (stripos($itemName, 'mutton') !== false) {
        $recipeType = 'mutton';
    } elseif (stripos($itemName, 'masala') !== false) {
        $recipeType = 'masala';
    } elseif (stripos($itemName, 'dosa') !== false || stripos($itemName, 'dosai') !== false) {
        $recipeType = 'dosa';
    } elseif (stripos($itemName, 'idly') !== false) {
        $recipeType = 'idly';
    } elseif (stripos($itemName, 'chicken') !== false) {
        $recipeType = 'chicken';
    }

    if (!$recipeType || !isset($recipeTemplates[$recipeType])) {
        $results[] = "⊘ Skipped: " . $item['item_name'] . " (no matching recipe type)";
        continue;
    }

    $recipe = $recipeTemplates[$recipeType];

    // Insert recipe
    $sql = "INSERT INTO recipes (menu_item_id, name, preparation_time, difficulty_level, yield_quantity, chef_notes) VALUES (?, ?, ?, ?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('isisis', $item['id'], $recipe['name'], $recipe['preparation_time'], $recipe['difficulty_level'], $recipe['yield_quantity'], $recipe['chef_notes']);

    if ($stmt->execute()) {
        $recipeId = $stmt->insert_id;
        $stmt->close();

        // Insert ingredients
        foreach ($recipe['ingredients'] as $ing) {
            // Get ingredient ID
            $ingStmt = $conn->prepare("SELECT id FROM ingredients WHERE name = ?");
            $ingStmt->bind_param('s', $ing['name']);
            $ingStmt->execute();
            $ingResult = $ingStmt->get_result();

            if ($ingResult->num_rows > 0) {
                $ingRow = $ingResult->fetch_assoc();
                $ingId = $ingRow['id'];

                $ingInsertStmt = $conn->prepare("INSERT INTO recipe_ingredients (recipe_id, ingredient_id, quantity_required, unit) VALUES (?, ?, ?, ?)");
                $ingInsertStmt->bind_param('iids', $recipeId, $ingId, $ing['qty'], $ing['unit']);
                $ingInsertStmt->execute();
                $ingInsertStmt->close();
            }
            $ingStmt->close();
        }

        $createdCount++;
        $results[] = "✓ Created: " . $item['item_name'] . " - " . $recipe['name'];
    } else {
        $results[] = "✗ Failed: " . $item['item_name'] . " - " . $stmt->error;
        $stmt->close();
    }
}

echo json_encode([
    'success' => true,
    'message' => "Recipes created successfully",
    'created_count' => $createdCount,
    'results' => $results
], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
