Bakery Data MCP Server

Instructions

Implementation Reference

• src/bakery_data_mcp/server.py:461-515 (handler)

  Handler for the 'top_products' tool. Constructs and executes a SQL query to retrieve top-selling products based on specified metric (quantity or revenue), date range, department filter, and limit. Joins with products table if department filter is applied. Returns JSON-formatted results.

  elif name == "top_products":
      metric = arguments.get("metric", "revenue")
  
      if metric == "quantity":
          order_by = "total_quantity DESC"
      else:
          order_by = "total_revenue DESC"
  
      query = f"""
          SELECT product_code, product_name,
                 COUNT(*) as transaction_count,
                 SUM(quantity) as total_quantity,
                 SUM(amount) as total_revenue,
                 AVG(unit_price) as avg_price
          FROM transactions
          WHERE 1=1
      """
      params = []
  
      if "start_date" in arguments:
          query += " AND datetime >= ?"
          params.append(arguments["start_date"])
  
      if "end_date" in arguments:
          query += " AND datetime <= ?"
          params.append(arguments["end_date"] + " 23:59:59")
  
      if "department_id" in arguments:
          query = """
              SELECT t.product_code, t.product_name,
                     COUNT(*) as transaction_count,
                     SUM(t.quantity) as total_quantity,
                     SUM(t.amount) as total_revenue,
                     AVG(t.unit_price) as avg_price
              FROM transactions t
              JOIN products p ON t.product_code = p.plu_code
              WHERE p.department_id = ?
          """
          params.insert(0, arguments["department_id"])
  
          if "start_date" in arguments:
              query += " AND t.datetime >= ?"
          if "end_date" in arguments:
              query += " AND t.datetime <= ?"
  
      query += f" GROUP BY product_code, product_name ORDER BY {order_by} LIMIT ?"
      params.append(arguments.get("limit", 10))
  
      cursor.execute(query, params)
      results = cursor.fetchall()
  
      return [TextContent(
          type="text",
          text=json.dumps(results, ensure_ascii=False, indent=2)
      )]

• src/bakery_data_mcp/server.py:174-203 (registration)

  Registration of the 'top_products' tool in the list_tools() function, including name, description, and input schema definition for MCP tool discovery.

  Tool(
      name="top_products",
      description="Get top selling products by quantity or revenue. Supports filtering by date range and department.",
      inputSchema={
          "type": "object",
          "properties": {
              "start_date": {
                  "type": "string",
                  "description": "Start date (YYYY-MM-DD format). Optional."
              },
              "end_date": {
                  "type": "string",
                  "description": "End date (YYYY-MM-DD format). Optional."
              },
              "department_id": {
                  "type": "number",
                  "description": "Filter by department ID. Optional."
              },
              "metric": {
                  "type": "string",
                  "enum": ["quantity", "revenue"],
                  "description": "Rank by quantity sold or total revenue. Default: revenue."
              },
              "limit": {
                  "type": "number",
                  "description": "Number of top products to return. Default: 10."
              }
          }
      }
  ),

• src/bakery_data_mcp/server.py:177-202 (schema)

  Input schema for the 'top_products' tool, defining parameters for date range, department, ranking metric, and result limit.

  inputSchema={
      "type": "object",
      "properties": {
          "start_date": {
              "type": "string",
              "description": "Start date (YYYY-MM-DD format). Optional."
          },
          "end_date": {
              "type": "string",
              "description": "End date (YYYY-MM-DD format). Optional."
          },
          "department_id": {
              "type": "number",
              "description": "Filter by department ID. Optional."
          },
          "metric": {
              "type": "string",
              "enum": ["quantity", "revenue"],
              "description": "Rank by quantity sold or total revenue. Default: revenue."
          },
          "limit": {
              "type": "number",
              "description": "Number of top products to return. Default: 10."
          }
      }
  }