# Bakery Data MCP Server

An MCP (Model Context Protocol) server that provides access to bakery POS (Point of Sale) data stored in SQLite. This server enables Claude and other MCP clients to query transaction data, product information, and generate sales analytics.

## Overview

This project imports bakery sales data from CSV files into a SQLite database and exposes it through an MCP server with powerful querying capabilities.

### Data Sources

- **POS Transaction Journal** (`pos_journal_2023_2024.csv`): Sales transactions from 2023-2024
- **Product Master** (`商品マスタ.csv`): Product catalog with pricing and cost data
- **Product Master Extended** (`商品マスタ_タグ拡張版.csv`): Product catalog with category tags
- **Department Master** (`部門マスタ.csv`): Department/category definitions

## Setup

### 1. Install Dependencies

```bash
pip install mcp
```

Or install in development mode:

```bash
pip install -e .
```

### 2. Import Data into SQLite

Run the import script to create the database and load CSV data:

```bash
python import_data.py
```

This will:
- Create `bakery_data.db` SQLite database
- Import all CSV files from the `Data` directory
- Create indexes for better query performance
- Display database statistics

### 3. Configure MCP Server

Add the server to your Claude Desktop configuration file:

**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`

**Windows**: `%APPDATA%\Claude\claude_desktop_config.json`

```json
{
  "mcpServers": {
    "bakery-data": {
      "command": "python",
      "args": [
        "-m",
        "bakery_data_mcp.server"
      ],
      "cwd": "/absolute/path/to/bakery_data_mcp"
    }
  }
}
```

Replace `/absolute/path/to/bakery_data_mcp` with the actual path to this project directory.

### 4. Restart Claude Desktop

Restart Claude Desktop to load the new MCP server configuration.

## Available Tools

The MCP server provides the following tools:

### 1. `query_transactions`
Query POS transaction data with various filters.

**Parameters:**
- `start_date` (optional): Start date (YYYY-MM-DD)
- `end_date` (optional): End date (YYYY-MM-DD)
- `product_code` (optional): Filter by product code
- `product_name` (optional): Search product name (partial match)
- `payment_method` (optional): Filter by payment method
- `min_amount` / `max_amount` (optional): Amount range filter
- `limit` (optional): Max results (default: 100)

### 2. `query_products`
Query product master data.

**Parameters:**
- `plu_code` (optional): Product PLU code
- `product_name` (optional): Search product name (partial match)
- `department_id` (optional): Filter by department
- `min_price` / `max_price` (optional): Price range filter
- `tag` (optional): Filter by product tag
- `include_tags` (optional): Include tag data in results
- `limit` (optional): Max results (default: 100)

### 3. `query_departments`
Query department master data.

**Parameters:**
- `department_id` (optional): Department ID
- `department_name` (optional): Search department name (partial match)

### 4. `sales_summary`
Get aggregated sales statistics.

**Parameters:**
- `start_date` / `end_date` (optional): Date range
- `group_by` (optional): Group by `product`, `department`, `payment_method`, `date`, or `month`
- `department_id` (optional): Filter by department
- `limit` (optional): Max results (default: 100)

### 5. `top_products`
Get top selling products.

**Parameters:**
- `start_date` / `end_date` (optional): Date range
- `department_id` (optional): Filter by department
- `metric` (optional): Rank by `quantity` or `revenue` (default: revenue)
- `limit` (optional): Number of top products (default: 10)

### 6. `execute_sql`
Execute custom SQL queries on the database.

**Parameters:**
- `query`: SQL query to execute
- `params` (optional): Query parameters for parameterized queries

**⚠️ Use with caution**: This allows arbitrary SQL execution. Use read-only queries when possible.

### 7. `get_schema`
Get database schema information including table structures and row counts.

## Example Usage

Once configured, you can ask Claude questions like:

- "What were the top 10 selling products in January 2024?"
- "Show me all transactions paid with credit card over ¥1000"
- "What's the total revenue by department for 2023?"
- "Find all products tagged with '朝食向け' (breakfast)"
- "What are the sales trends by month?"

## Database Schema

### Tables

- **departments**: Department master data
  - `department_id` (PRIMARY KEY)
  - `department_name`

- **products**: Product master data
  - `plu_code` (PRIMARY KEY)
  - `department_id` (FOREIGN KEY)
  - `product_name`
  - `price`
  - `cost`
  - `cost_rate`

- **products_extended**: Product master with tags
  - Same as `products` plus:
  - `tags` (JSON array as text)

- **transactions**: POS transaction journal
  - `id` (PRIMARY KEY, auto-increment)
  - `transaction_number`
  - `datetime`
  - `product_code`
  - `product_name`
  - `unit_price`
  - `quantity`
  - `amount`
  - `payment_method`

## Development

### Project Structure

```
bakery_data_mcp/
├── Data/                          # CSV data files
├── src/
│   └── bakery_data_mcp/
│       ├── __init__.py
│       └── server.py              # MCP server implementation
├── schema.sql                     # Database schema
├── import_data.py                 # Data import script
├── pyproject.toml                 # Project configuration
├── bakery_data.db                 # SQLite database (generated)
└── README.md
```

### Running the Server

For testing, you can run the server directly:

```bash
python -m bakery_data_mcp.server
```

The server communicates via stdio and expects MCP protocol messages.

## License

MIT License