# 🛒 ShopMind AI — E-Commerce Operations Agent

**Ask your e-commerce database a question in plain English. Get a real, data-backed answer.**

ShopMind AI is a conversational analytics agent for an e-commerce business.
Instead of writing SQL or building dashboards, you type a question like
*"What are the top 5 best-selling products?"* or *"How many users signed up
last month?"* — the agent figures out the right query, runs it safely
against the real database, and answers in plain business language.

---

## Why this project exists

Most small e-commerce teams have the data (orders, products, users, reviews,
events) but not the SQL skills to dig into it on demand. This project is an
exploration of **agentic tool-use with the Model Context Protocol (MCP)**:
rather than letting an LLM touch the database directly, the LLM only ever
sees a single, narrow, read-only *tool* — it never gets raw database
credentials or write access. That separation is the actual point of the
project, not just the chat UI on top of it.

---

## How it works

```
┌─────────────┐      question       ┌──────────────────┐
│  Streamlit  │ ──────────────────▶ │   Gemini model    │
│   Chat UI   │                     │ (gemini-3.6-flash)│
│  (app.py)   │ ◀────────────────── │                    │
└─────────────┘   final answer      └─────────┬──────────┘
                                                │ decides it needs data,
                                                │ requests a tool call
                                                ▼
                                     ┌──────────────────────┐
                                     │   MCP Server          │
                                     │  (mcp_server.py)      │
                                     │  tool: execute_       │
                                     │  readonly_sql(query)  │
                                     └──────────┬─────────────┘
                                                │ validated SELECT only
                                                ▼
                                     ┌──────────────────────┐
                                     │   MySQL database       │
                                     │   (database.py)        │
                                     │   users, products,     │
                                     │   orders, order_items, │
                                     │   reviews, events       │
                                     └──────────────────────┘
```

1. You type a question into the Streamlit chat box.
2. `app.py` sends the question to Gemini, along with the database schema
   and a set of ground rules (only `SELECT`, only the given tables, never
   invent data, exclude cancelled/returned orders from revenue, etc.).
3. Gemini decides it needs data and calls the one tool it's been given:
   `execute_readonly_sql`, generating the SQL itself.
4. That tool call goes over **MCP** (not a direct function call) to a
   separate MCP server process (`mcp_server.py`), which is the *only* thing
   with a database connection.
5. The MCP server validates the query (must start with `SELECT`, only one
   statement allowed, no destructive keywords), runs it, and returns the
   rows as JSON.
6. That result is fed back to Gemini, which turns raw rows into a
   plain-English, business-friendly answer.
7. The agent can loop this up to 5 times if a query needs correcting —
   e.g. if the first attempt references a column that doesn't exist.

The AI **never sees your database password and can never run
`INSERT`/`UPDATE`/`DELETE`/`DROP`** — it only ever gets to ask the MCP
server to run one `SELECT` at a time, and the server enforces that.

---

## Tech stack

| Layer               | Technology                                   |
|---------------------|-----------------------------------------------|
| UI                  | [Streamlit](https://streamlit.io) chat interface |
| LLM / reasoning      | Google Gemini (`gemini-3.6-flash`) via the `google-genai` SDK, using its tool-calling / Interactions API |
| Agent ↔ data bridge | [Model Context Protocol (MCP)](https://modelcontextprotocol.io) — the emerging standard for giving LLMs safe, structured tool access |
| Data layer          | MySQL, accessed only through `mysql-connector-python` inside the MCP server |
| Data                | ~170k rows of synthetic e-commerce data: users, products, orders, order items, reviews, and behavioral events |

---

## Features

- 💬 **Natural-language querying** — no SQL knowledge needed to explore the data.
- 🔒 **Read-only by design** — the MCP tool rejects anything that isn't a single `SELECT` statement.
- 🔁 **Self-correcting agent loop** — if a generated query is wrong, the agent sees the error and tries again (up to 5 rounds).
- 🧠 **Schema-aware prompting** — the model is given the exact table/column names up front, so it doesn't guess.
- 📊 **Business-sensible answers** — rules baked into the prompt handle real analytics nuance, like excluding cancelled/returned orders from revenue figures.
- 🧩 **Decoupled architecture** — the LLM, the tool server, and the database are three separate processes talking over well-defined protocols, not one tangled script.

---

## Project structure

```
MCP/
├── app.py              # Streamlit chat app — the main entry point
├── mcp_server.py        # MCP server exposing the execute_readonly_sql tool
├── mcp_client.py         # CLI version of the full agent loop (for debugging without the UI)
├── agent.py              # Minimal CLI version — no MCP, calls the DB function directly
├── database.py            # MySQL connection handling
├── test_database.py        # Sanity check: run a query directly against the DB
├── test_mcp.py               # Sanity check: spin up the MCP server and call the tool
├── requirements.txt            # Python dependencies
├── .env                          # API key + database credentials (not committed in real projects)
└── data/
    ├── schema.sql                # Table definitions matching the CSVs below
    ├── users.csv, products.csv, orders.csv,
    │   order_items.csv, reviews.csv, events.csv   # Source data
    └── MCP.ipynb                                   # Exploratory notebook used while building this
```

---

## Database schema

| Table         | Key columns                                                              |
|---------------|---------------------------------------------------------------------------|
| `users`       | `user_id`, `name`, `email`, `gender`, `city`, `signup_date`                |
| `products`    | `product_id`, `product_name`, `category`, `brand`, `price`, `rating`       |
| `orders`      | `order_id`, `user_id`, `order_date`, `order_status`, `total_amount`         |
| `order_items` | `order_item_id`, `order_id`, `product_id`, `user_id`, `quantity`, `item_price`, `item_total` |
| `reviews`     | `review_id`, `order_id`, `product_id`, `user_id`, `rating`, `review_text`, `review_date` |
| `events`      | `event_id`, `user_id`, `product_id`, `event_type`, `event_timestamp`         |

`order_status` values: `processing`, `completed`, `cancelled`, `returned`, `shipped`.

---

## Setup

### 1. Install dependencies

```bash
pip install -r requirements.txt
```

### 2. Set up MySQL

```bash
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mcp CHARACTER SET utf8mb4;"
mysql -u root -p mcp < data/schema.sql
```

Then load the CSVs (adjust paths to absolute; enable `local_infile` if disabled):

```sql
SET GLOBAL local_infile=1;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/users.csv'
  INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/products.csv'
  INTO TABLE products FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/orders.csv'
  INTO TABLE orders FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS
  (order_id, user_id, @order_date, order_status, total_amount)
  SET order_date = STR_TO_DATE(@order_date, '%Y-%m-%dT%H:%i:%s.%f');

LOAD DATA LOCAL INFILE '/absolute/path/to/data/order_items.csv'
  INTO TABLE order_items FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/reviews.csv'
  INTO TABLE reviews FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS
  (review_id, order_id, product_id, user_id, rating, review_text, @review_date)
  SET review_date = STR_TO_DATE(@review_date, '%Y-%m-%dT%H:%i:%s.%f');

LOAD DATA LOCAL INFILE '/absolute/path/to/data/events.csv'
  INTO TABLE events FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS
  (event_id, user_id, product_id, event_type, @event_timestamp)
  SET event_timestamp = STR_TO_DATE(@event_timestamp, '%Y-%m-%dT%H:%i:%s.%f');
```

### 3. Configure environment variables

Create/edit `.env`:

```
GEMINI_API_KEY=your_key_here

DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=your_mysql_password
DB_NAME=mcp
```

### 4. Sanity-check the pieces

```bash
python test_database.py   # confirms the DB connection + a real query work
python test_mcp.py        # confirms the MCP server starts and the tool responds
```

### 5. Run the app

```bash
streamlit run app.py
```

Open the local URL Streamlit prints, and start asking questions.

---

## Example questions to try

- "What are the top 5 best-selling products?"
- "How many orders were cancelled last quarter?"
- "Which city has the most signed-up users?"
- "What's the average rating for the Electronics category?"
- "What's our total revenue excluding cancelled and returned orders?"

---

## Design decisions & guardrails

- **Least privilege**: the database user the app connects as should only
  have `SELECT` on the `mcp` schema — never grant it write access.
- **Defense in depth on SQL**: the tool itself rejects anything that isn't
  a single `SELECT` statement, independent of whatever the model was told
  to do in the prompt — a compromised or confused model still can't do
  damage.
- **Schema-first prompting**: the model is always given the real column
  names, so answers are grounded instead of guessed.
- **Graceful degradation**: if the model's answer doesn't land in the
  expected place, or a query fails, the agent retries or returns a clear
  message — never a silent blank response.

## Known limitations

- There's no dedicated "profit" column in the data (no cost-of-goods
  figures), so profit-style questions are answered using revenue instead,
  with that substitution stated explicitly in the answer.
- The agent caps itself at 5 tool-call rounds per question to avoid
  runaway loops on an unanswerable question.
- This is a single-user local tool, not a multi-tenant production service —
  there's no authentication layer on the Streamlit app itself.

## Possible next steps

- Add a chat history so follow-up questions ("...and for last month
  specifically?") retain context.
- Cache repeated queries to cut down on Gemini calls.
- Add a lightweight cost/COGS table so real profit questions can be answered.
- Deploy the MCP server as a long-lived process instead of spawning a new
  one per question.