api/menu.php

<?php
header("Content-Type: application/json");
include "db.php";

try {
    // Get menu items with stock and popularity (order count)
    $sql = "SELECT f.*,
            COALESCE(s.quantity, 0) as stock,
            COALESCE(pop.total_qty, 0) as popularity
            FROM foods f
            LEFT JOIN stock s ON f.id = s.food_id
            LEFT JOIN (
                SELECT food_id, SUM(quantity) as total_qty
                FROM order_items
                GROUP BY food_id
            ) pop ON f.id = pop.food_id
            ORDER BY popularity DESC";
    $stmt = $conn->query($sql);

    $menu = [];
    while ($row = $stmt->fetch()) {
        $row['id'] = (int)$row['id'];
        $row['price'] = (float)$row['price'];
        $row['stock'] = (int)$row['stock'];
        $row['popularity'] = (int)$row['popularity'];
        $cat_emojis = [
            'Beverages' => '🥤',
            'Wafers' => '🍪',
            'Snacks' => '🍟',
            'Coffee & Drinks' => '☕',
            'Hot Dogs' => '🌭',
            'Biryani' => '🍛'
        ];
        $row['emoji'] = $cat_emojis[$row['category']] ?? '🍽';
        $row['cat'] = $row['category'];
        $row['img'] = $row['image_url'];
        $row['sub'] = $row['description'];
        $menu[] = $row;
    }

    echo json_encode(['status' => 'success', 'count' => count($menu), 'data' => $menu]);
} catch (PDOException $e) {
    echo json_encode(['status' => 'error', 'message' => 'Query failed', 'error' => $e->getMessage()]);
}
?>