PDO::ERRMODE_EXCEPTION] ); } catch (PDOException $e) { http_response_code(500); echo json_encode(['error' => 'DB connection failed']); exit(); } $sql = <<= CURDATE() THEN il.quantity * mi.price ELSE 0 END) AS daily_sales, SUM(CASE WHEN i.created_at >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND i.created_at < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY) THEN il.quantity * mi.price ELSE 0 END) AS weekly_sales, SUM(CASE WHEN i.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) THEN il.quantity * mi.price ELSE 0 END) AS monthly_sales, -- Most popular dish in each period (SELECT mi2.name FROM invoice_items AS il2 JOIN invoices AS i2 ON i2.id = il2.invoice_id JOIN menu_items AS mi2 ON mi2.id = il2.menu_item_id WHERE i2.created_at >= CURDATE() GROUP BY il2.menu_item_id ORDER BY SUM(il2.quantity) DESC LIMIT 1 ) AS daily_top, (SELECT mi3.name FROM invoice_items AS il3 JOIN invoices AS i3 ON i3.id = il3.invoice_id JOIN menu_items AS mi3 ON mi3.id = il3.menu_item_id WHERE i3.created_at >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND i3.created_at < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY) GROUP BY il3.menu_item_id ORDER BY SUM(il3.quantity) DESC LIMIT 1 ) AS weekly_top, (SELECT mi4.name FROM invoice_items AS il4 JOIN invoices AS i4 ON i4.id = il4.invoice_id JOIN menu_items AS mi4 ON mi4.id = il4.menu_item_id WHERE i4.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY il4.menu_item_id ORDER BY SUM(il4.quantity) DESC LIMIT 1 ) AS monthly_top FROM invoices AS i JOIN invoice_items AS il ON il.invoice_id = i.id JOIN menu_items AS mi ON mi.id = il.menu_item_id; SQL; try { $stmt = $pdo->query($sql); $result = $stmt->fetch(PDO::FETCH_ASSOC); echo json_encode([ 'daily_sales' => (float)$result['daily_sales'], 'weekly_sales' => (float)$result['weekly_sales'], 'monthly_sales' => (float)$result['monthly_sales'], 'daily_top' => $result['daily_top'] ?? null, 'weekly_top' => $result['weekly_top'] ?? null, 'monthly_top' => $result['monthly_top'] ?? null, ]); } catch (PDOException $e) { http_response_code(500); echo json_encode(['error' => 'Query failed: ' . $e->getMessage()]); }