# 🛫 DataPilot AI

**DataPilot AI** is a production-quality, AI-powered database assistant that allows users to upload CSV files, automatically converts them into an isolated SQLite database, and enables natural language data analysis using an **Ollama LLM Agent (`qwen3:8b`)** communicating strictly through **Model Context Protocol (MCP)** tools.

---

## 🏛️ Architecture & Clean Isolation

```
 +-----------------------------------------------------------------------+
 |                            Streamlit UI                               |
 |     - CSV Upload & Table Data Preview                                 |
 |     - Natural Language Query Interface                                |
 |     - Render SQL Queries, Results & Dynamic Plotly Charts             |
 +-----------------------------------+-----------------------------------+
                                     |
                                     v
 +-----------------------------------+-----------------------------------+
 |                             AI Agent                                  |
 |     - Ollama (`qwen3:8b`) Agent Loop                                  |
 |     - Translates user questions into MCP tool calls                   |
 |     - STRICTLY NO direct database access                              |
 +-----------------------------------+-----------------------------------+
                                     |
                                     | (MCP JSON-RPC Protocol)
                                     v
 +-----------------------------------+-----------------------------------+
 |                            MCP Server                                 |
 |     - Built with FastMCP / MCP Python SDK                             |
 |     - Exposes isolated tools:                                         |
 |         * list_tables()                                               |
 |         * describe_table(table_name)                                  |
 |         * run_sql(query)                                              |
 +-----------------------------------+-----------------------------------+
                                     |
                                     v
 +-----------------------------------+-----------------------------------+
 |                      Database & Storage Layer                         |
 |     - SQLite Database (`database/datapilot.db`)                       |
 |     - SQLAlchemy ORM & Engine abstraction                             |
 |     - Ingestion Layer (`database/csv_loader.py`)                      |
 +-----------------------------------------------------------------------+
```

### Key Architectural Principles
- **Strict MCP Tool Isolation:** The AI Model *never* opens SQLite files or executes SQL directly. It interacts with data solely through registered MCP server tools.
- **Security Guard:** `run_sql` blocks write operations (`DROP`, `DELETE`, `INSERT`, `UPDATE`, `ALTER`).
- **Type Hints & Clean Code:** Type annotations (`typing`), modern Python practices (`pathlib`), modular functions under 30 lines, proper logging, and exception handling.

---

## 🛠️ Tech Stack

- **Frontend:** Streamlit
- **Backend:** Python 3.10+
- **Database:** SQLite, SQLAlchemy
- **AI / LLM:** Ollama (`qwen3:8b`)
- **Protocol:** Official MCP Python SDK / FastMCP
- **Data Visualization:** Plotly
- **Data Processing:** Pandas
- **Configuration:** `python-dotenv`, Pydantic

---

## 📂 Project Structure

```
DataPilot-AI/
├── app/
│   ├── __init__.py
│   └── main.py              # Streamlit Web UI application
├── database/
│   ├── __init__.py
│   ├── database.py          # SQLAlchemy database engine management
│   └── csv_loader.py        # CSV parsing & SQL table ingestion
├── agent/
│   ├── __init__.py
│   └── agent.py             # Ollama AI agent & MCP tool dispatcher
├── mcp_server/
│   ├── __init__.py
│   ├── server.py            # FastMCP server transport
│   └── tools.py             # MCP database tool implementations
├── charts/
│   ├── __init__.py
│   └── chart_generator.py   # Automated Plotly chart generator
├── uploads/                 # Storage for raw CSV uploads
├── database/                # SQLite database directory (`datapilot.db`)
├── scratch/                 # Verification test scripts
├── .env.example             # Environment variables template
├── requirements.txt         # Pinned project dependencies
└── README.md                # Project documentation
```

---

## ⚙️ Quickstart Guide

### 1. Clone & Setup Virtual Environment
```bash
git clone https://github.com/taneeshk12/hcai_project.git DataPilotAI
cd DataPilotAI

# Create virtual environment
python3 -m venv .venv
source .venv/bin/activate

# Install dependencies
pip install --upgrade pip
pip install -r requirements.txt
```

### 2. Configure Environment Variables
Copy `.env.example` to `.env`:
```bash
cp .env.example .env
```

### 3. Setup & Start Ollama
Make sure Ollama is installed and running locally with the target model:
```bash
# Start Ollama server
ollama serve

# Pull target model in a separate terminal
ollama pull qwen3:8b
```

### 4. Run the Application
Launch the Streamlit interface:
```bash
streamlit run app/main.py
```
Open **`http://localhost:8501`** in your browser.

---

## 🔌 MCP Tools Specification

The MCP server exposes three database tools:

| Tool Name | Parameters | Description |
|---|---|---|
| `list_tables()` | None | Returns a JSON list of all active tables in the SQLite database. |
| `describe_table(table_name)` | `table_name: str` | Returns schema, column types, total row count, and 3 sample records. |
| `run_sql(query)` | `query: str` | Executes a read-only SQL query and returns matching records. |

---

## 💡 Usage Example

1. **Upload CSV:** Drag and drop `amazon.csv` (or any CSV dataset) into the uploader.
2. **Automatic SQL Ingestion:** DataPilot AI sanitizes the filename into a table name (e.g. `amazon`) and creates a SQLite table with full row insertion.
3. **Ask Natural Language Questions:**
   - *"How many rows are in the database?"*
   - *"Show all columns for table amazon"*
   - *"What are the top 5 highest rated items?"*
   - *"Show average price by category"*
4. **Inspect Output:**
   - **Generated SQL Query** displayed first in a code block.
   - **AI Answer** in clean natural language text.
   - **MCP Execution Trace** showing tool calls.
   - **Automated Plotly Chart** generated automatically for numerical data.

---

## 📜 License
MIT License. Created for AI Product Engineering portfolio.