Bakery Data MCP Server

Instructions

Implementation Reference

• src/bakery_data_mcp/server.py:353-459 (handler)

  Handler for the 'sales_summary' tool. Constructs dynamic SQL queries based on the 'group_by' parameter (product, department, payment_method, date, month, or default overall summary), applies date and department filters, executes the query on the transactions table (with joins as needed), and returns aggregated sales statistics as JSON.

  elif name == "sales_summary":
      group_by = arguments.get("group_by")
  
      # Base query
      if group_by == "product":
          query = """
              SELECT product_code, product_name,
                     COUNT(*) as transaction_count,
                     SUM(quantity) as total_quantity,
                     SUM(amount) as total_revenue
              FROM transactions
              WHERE 1=1
          """
          group_clause = " GROUP BY product_code, product_name ORDER BY total_revenue DESC"
  
      elif group_by == "department":
          query = """
              SELECT d.department_id, d.department_name,
                     COUNT(*) as transaction_count,
                     SUM(t.quantity) as total_quantity,
                     SUM(t.amount) as total_revenue
              FROM transactions t
              JOIN products p ON t.product_code = p.plu_code
              JOIN departments d ON p.department_id = d.department_id
              WHERE 1=1
          """
          group_clause = " GROUP BY d.department_id, d.department_name ORDER BY total_revenue DESC"
  
      elif group_by == "payment_method":
          query = """
              SELECT payment_method,
                     COUNT(*) as transaction_count,
                     SUM(quantity) as total_quantity,
                     SUM(amount) as total_revenue
              FROM transactions
              WHERE 1=1
          """
          group_clause = " GROUP BY payment_method ORDER BY total_revenue DESC"
  
      elif group_by == "date":
          query = """
              SELECT DATE(datetime) as date,
                     COUNT(*) as transaction_count,
                     SUM(quantity) as total_quantity,
                     SUM(amount) as total_revenue
              FROM transactions
              WHERE 1=1
          """
          group_clause = " GROUP BY DATE(datetime) ORDER BY date"
  
      elif group_by == "month":
          query = """
              SELECT strftime('%Y-%m', datetime) as month,
                     COUNT(*) as transaction_count,
                     SUM(quantity) as total_quantity,
                     SUM(amount) as total_revenue
              FROM transactions
              WHERE 1=1
          """
          group_clause = " GROUP BY strftime('%Y-%m', datetime) ORDER BY month"
  
      else:
          # Overall summary
          query = """
              SELECT COUNT(*) as transaction_count,
                     COUNT(DISTINCT transaction_number) as unique_transactions,
                     SUM(quantity) as total_quantity,
                     SUM(amount) as total_revenue,
                     AVG(amount) as avg_transaction_amount,
                     MIN(datetime) as first_transaction,
                     MAX(datetime) as last_transaction
              FROM transactions
              WHERE 1=1
          """
          group_clause = ""
  
      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 and group_by != "department":
          if "JOIN products" not in query:
              query = query.replace("FROM transactions",
                                   "FROM transactions t JOIN products p ON t.product_code = p.plu_code")
              query = query.replace("WHERE 1=1", "WHERE 1=1")
          query += " AND p.department_id = ?"
          params.append(arguments["department_id"])
  
      query += group_clause
  
      if group_by:
          query += " LIMIT ?"
          params.append(arguments.get("limit", 100))
  
      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:147-172 (schema)

  Input schema definition for the 'sales_summary' tool, defining parameters for date range, grouping options, department filter, 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."
          },
          "group_by": {
              "type": "string",
              "enum": ["product", "department", "payment_method", "date", "month"],
              "description": "How to group the results. Optional."
          },
          "department_id": {
              "type": "number",
              "description": "Filter by department ID. Optional."
          },
          "limit": {
              "type": "number",
              "description": "Maximum number of results. Default: 100."
          }
      }
  }

• src/bakery_data_mcp/server.py:144-173 (registration)

  Registration of the 'sales_summary' tool in the list_tools() function, including name, description, and input schema.

  Tool(
      name="sales_summary",
      description="Get sales summary statistics. Aggregate sales data by date range, product, department, or payment method.",
      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."
              },
              "group_by": {
                  "type": "string",
                  "enum": ["product", "department", "payment_method", "date", "month"],
                  "description": "How to group the results. Optional."
              },
              "department_id": {
                  "type": "number",
                  "description": "Filter by department ID. Optional."
              },
              "limit": {
                  "type": "number",
                  "description": "Maximum number of results. Default: 100."
              }
          }
      }
  ),