""" Optimized database queries for better performance """ from sqlalchemy.orm import Session, joinedload, selectinload from sqlalchemy import and_, or_, func, text from typing import List, Optional, Dict, Any from datetime import datetime, timedelta import logging from ..database import Order, OrderItem, Dish, Person, Table logger = logging.getLogger(__name__) class OptimizedQueryService: """Service for optimized database queries with caching and performance improvements""" def __init__(self): self.query_cache = {} self.cache_ttl = { 'menu': 300, # 5 minutes 'categories': 900, # 15 minutes 'specials': 300, # 5 minutes 'offers': 300, # 5 minutes } def get_menu_optimized(self, db: Session, category: Optional[str] = None) -> List[Dict]: """Optimized menu query with eager loading and caching""" try: # Build optimized query query = db.query(Dish).filter( Dish.is_visible == True ) if category and category != 'All': query = query.filter(Dish.category == category) # Order by category and name for consistent results query = query.order_by(Dish.category, Dish.name) # Execute query dishes = query.all() # Convert to dict for JSON serialization result = [] for dish in dishes: dish_dict = { 'id': dish.id, 'name': dish.name, 'description': dish.description, 'price': float(dish.price), 'category': dish.category, 'image_path': dish.image_path, 'is_offer': dish.is_offer, 'discount': float(dish.discount) if dish.discount else 0, 'is_visible': dish.is_visible, 'created_at': dish.created_at.isoformat() if dish.created_at else None } result.append(dish_dict) return result except Exception as e: logger.error(f"Error in get_menu_optimized: {str(e)}") raise def get_orders_optimized(self, db: Session, person_id: Optional[int] = None, table_number: Optional[int] = None, status: Optional[str] = None) -> List[Dict]: """Optimized order query with eager loading of related data""" try: # Build base query with eager loading query = db.query(Order).options( selectinload(Order.items).selectinload(OrderItem.dish), joinedload(Order.person) ) # Apply filters filters = [] if person_id: filters.append(Order.person_id == person_id) if table_number: filters.append(Order.table_number == table_number) if status: filters.append(Order.status == status) if filters: query = query.filter(and_(*filters)) # Order by creation time (newest first) query = query.order_by(Order.created_at.desc()) # Execute query orders = query.all() # Convert to dict with optimized serialization result = [] for order in orders: order_dict = { 'id': order.id, 'table_number': order.table_number, 'unique_id': order.unique_id, 'person_id': order.person_id, 'status': order.status, 'created_at': order.created_at.isoformat() if order.created_at else None, 'updated_at': order.updated_at.isoformat() if order.updated_at else None, 'items': [] } # Add order items for item in order.items: item_dict = { 'id': item.id, 'dish_id': item.dish_id, 'dish_name': item.dish.name if item.dish else 'Unknown', 'quantity': item.quantity, 'price': float(item.price), 'remarks': item.remarks, 'position': item.position } order_dict['items'].append(item_dict) result.append(order_dict) return result except Exception as e: logger.error(f"Error in get_orders_optimized: {str(e)}") raise def get_chef_orders_optimized(self, db: Session, status: str) -> List[Dict]: """Optimized chef order query with minimal data transfer""" try: # Use raw SQL for better performance on chef queries sql = text(""" SELECT o.id, o.table_number, o.status, o.created_at, o.updated_at, COUNT(oi.id) as item_count, GROUP_CONCAT( CONCAT(d.name, ' (', oi.quantity, ')') SEPARATOR ', ' ) as items_summary FROM orders o LEFT JOIN order_items oi ON o.id = oi.order_id LEFT JOIN dishes d ON oi.dish_id = d.id WHERE o.status = :status GROUP BY o.id, o.table_number, o.status, o.created_at, o.updated_at ORDER BY o.created_at ASC """) result = db.execute(sql, {'status': status}).fetchall() # Convert to dict orders = [] for row in result: order_dict = { 'id': row.id, 'table_number': row.table_number, 'status': row.status, 'created_at': row.created_at.isoformat() if row.created_at else None, 'updated_at': row.updated_at.isoformat() if row.updated_at else None, 'item_count': row.item_count, 'items_summary': row.items_summary or '' } orders.append(order_dict) return orders except Exception as e: logger.error(f"Error in get_chef_orders_optimized: {str(e)}") # Fallback to regular query return self._get_chef_orders_fallback(db, status) def _get_chef_orders_fallback(self, db: Session, status: str) -> List[Dict]: """Fallback method for chef orders if raw SQL fails""" try: orders = db.query(Order).options( selectinload(Order.items).selectinload(OrderItem.dish) ).filter(Order.status == status).order_by(Order.created_at.asc()).all() result = [] for order in orders: items_summary = ', '.join([ f"{item.dish.name if item.dish else 'Unknown'} ({item.quantity})" for item in order.items ]) order_dict = { 'id': order.id, 'table_number': order.table_number, 'status': order.status, 'created_at': order.created_at.isoformat() if order.created_at else None, 'updated_at': order.updated_at.isoformat() if order.updated_at else None, 'item_count': len(order.items), 'items_summary': items_summary } result.append(order_dict) return result except Exception as e: logger.error(f"Error in chef orders fallback: {str(e)}") raise def get_table_status_optimized(self, db: Session) -> List[Dict]: """Optimized table status query""" try: # Use raw SQL for better performance sql = text(""" SELECT t.table_number, t.is_occupied, t.current_order_id, t.updated_at, o.status as order_status, COUNT(oi.id) as item_count FROM tables t LEFT JOIN orders o ON t.current_order_id = o.id LEFT JOIN order_items oi ON o.id = oi.order_id GROUP BY t.table_number, t.is_occupied, t.current_order_id, t.updated_at, o.status ORDER BY t.table_number """) result = db.execute(sql).fetchall() tables = [] for row in result: table_dict = { 'table_number': row.table_number, 'is_occupied': bool(row.is_occupied), 'current_order_id': row.current_order_id, 'updated_at': row.updated_at.isoformat() if row.updated_at else None, 'order_status': row.order_status, 'item_count': row.item_count or 0 } tables.append(table_dict) return tables except Exception as e: logger.error(f"Error in get_table_status_optimized: {str(e)}") raise def get_analytics_data_optimized(self, db: Session, start_date: datetime, end_date: datetime) -> Dict[str, Any]: """Optimized analytics query with aggregations""" try: # Use raw SQL for complex aggregations sql = text(""" SELECT DATE(o.created_at) as order_date, COUNT(DISTINCT o.id) as total_orders, COUNT(DISTINCT o.table_number) as unique_tables, SUM(oi.quantity * oi.price) as total_revenue, AVG(oi.quantity * oi.price) as avg_order_value, d.category, COUNT(oi.id) as items_sold FROM orders o JOIN order_items oi ON o.id = oi.order_id JOIN dishes d ON oi.dish_id = d.id WHERE o.created_at BETWEEN :start_date AND :end_date AND o.status = 'paid' GROUP BY DATE(o.created_at), d.category ORDER BY order_date DESC, d.category """) result = db.execute(sql, { 'start_date': start_date, 'end_date': end_date }).fetchall() # Process results analytics = { 'daily_stats': {}, 'category_stats': {}, 'summary': { 'total_orders': 0, 'total_revenue': 0, 'avg_order_value': 0 } } for row in result: date_str = row.order_date.isoformat() if date_str not in analytics['daily_stats']: analytics['daily_stats'][date_str] = { 'orders': row.total_orders, 'revenue': float(row.total_revenue), 'avg_value': float(row.avg_order_value), 'unique_tables': row.unique_tables } category = row.category if category not in analytics['category_stats']: analytics['category_stats'][category] = { 'items_sold': 0, 'revenue': 0 } analytics['category_stats'][category]['items_sold'] += row.items_sold return analytics except Exception as e: logger.error(f"Error in get_analytics_data_optimized: {str(e)}") raise # Create singleton instance optimized_queries = OptimizedQueryService()