# Pagila Database AI Assistant

This project is a Streamlit-based chatbot that allows users to query a PostgreSQL database (Pagila schema) using natural language. It leverages Google's Gemini API for SQL generation and the Model Context Protocol (MCP) for secure database execution.

## Features

*   **Natural Language to SQL**: Converts English questions into valid SQL queries.
*   **Agentic Workflow**: The AI autonomously explores the database schema (listing tables, checking columns) before writing queries.
*   **Vector Caching (RAG)**: Uses ChromaDB to cache successful SQL queries locally. If a similar question is asked again, the cached SQL is executed immediately, saving API costs and time.
*   **Model Context Protocol (MCP)**: Uses a dedicated local server (`mcp_pagila_server.py`) to handle database operations, separating the UI from the backend logic.
*   **Cost Tracking**: Monitors token usage and estimates costs for both the current session and global history.
*   **Schema Visualization**: Displays the database schema diagram and metadata within the UI.

## Architecture

1.  **Frontend**: Streamlit (`app.py`) handles user input, chat history, and visualization.
2.  **AI Brain**: Google Gemini (via `google-generativeai`) acts as the reasoning engine.
3.  **Backend**: An MCP Server (`mcp_pagila_server.py`) runs as a subprocess, exposing tools like `list_tables`, `get_table_schema`, and `execute_sql`.
4.  **Database**: PostgreSQL hosting the Pagila sample database.
5.  **Cache**: ChromaDB stores embeddings of questions and their corresponding SQL.

## Prerequisites

*   Python 3.10+
*   PostgreSQL database with the **Pagila** schema installed.
*   Google Gemini API Key.

## Installation

1.  **Clone the repository**:
    ```bash
    git clone <repository-url>
    cd mcp-pagila-server
    ```

2.  **Install dependencies**:
    ```bash
    pip install -r requirements.txt
    ```

3.  **Configure Environment**:
    Create a `config.env` file in the root directory:
    ```env
    GEMINI_API_KEY=your_google_api_key_here
    PGHOST=localhost
    PGUSER=your_postgres_user
    PGPASSWORD=your_postgres_password
    PGDATABASE=pagila
    LOG_DIR=logs
    ```
    *Note: It is recommended to use a read-only database user for security.*

## Usage

Run the Streamlit application:

```bash
streamlit run app.py
```
### Testing & Inspection + +streamlit_app.py is provided as a lightweight, chat-like interface to test the MCP server directly without the full Gemini Agent loop. It is useful for debugging the MCP server connection and running raw SQL (using a run: prefix) or heuristic-based queries
## Project Structure

*   `app.py`: Main Streamlit application.
*   `mcp_pagila_server.py`: MCP server implementation.
*   `pagila-metadata.txt`: Text-based schema summary for the AI.
*   `requirements.txt`: Python dependencies.
*   `vector_store/`: Directory where ChromaDB persists data (ignored in git).
*   `usage_stats.json`: Local file tracking usage costs (ignored in git).