# College Management AI Assistant (v5)

An intelligent college administrative assistant powered by Google Gemini and the Model Context Protocol (MCP). The assistant interacts with a relational SQLite database through structured FastMCP tools to answer inquiries regarding students, faculty, courses, enrollments, and academic performance.

---

## Architecture Overview

```
+-------------------------------------------------------------+
|                      User / Client                          |
+-------------------------------------------------------------+
                              |
                              v
+-------------------------------------------------------------+
|                   Gemini LLM Assistant                      |
|                         (bot.py)                            |
+-------------------------------------------------------------+
                              |
                     MCP Protocol (stdio)
                              |
                              v
+-------------------------------------------------------------+
|                     FastMCP Server                          |
|                      (tooling.py)                           |
+-------------------------------------------------------------+
                              |
                        SQLite Queries
                              |
                              v
+-------------------------------------------------------------+
|                   College SQLite Database                   |
|                        (college.db)                         |
|   - persons (students, faculty, staff)                      |
|   - courses                                                 |
|   - student_enrollments                                     |
+-------------------------------------------------------------+
```

---

## Features

- **Synthetic Data Generation**: Creates realistic college records using `Faker` including students, faculty, courses, and grade histories.
- **FastMCP Tooling**: Exposes robust, schema-compliant MCP tools for querying academic records, courses, departments, and enrollment statistics.
- **Interactive Multi-turn Bot**: Chat interface leveraging the Google GenAI SDK (`google-genai`) with automatic tool call orchestration via MCP.
- **Secure Configuration**: Environment-based API key management with `.env` support.

---

## Project Structure

```
.
├── bot.py                   # Interactive Gemini chatbot integrating MCP tools via stdio
├── tooling.py               # FastMCP Server exposing database querying tools
├── create_data.py           # SQLite database schema initializer & synthetic data generator
├── college.db               # SQLite database file (generated by create_data.py)
├── system_prompt.txt        # System instruction prompt for the AI assistant
├── prompt.txt               # Context and initial project design prompts
├── mcp_config.example.json  # Example configuration for MCP client registration
├── requirements.txt         # Python package dependencies
├── .env                     # Environment variables (GEMINI_API_KEY)
└── .gitignore               # Git ignore file for Python, SQLite, and secrets
```

---

## Database Schema

The SQLite database (`college.db`) includes the following relational tables:

1. **`persons`**: Stores students, faculty, and staff profiles (ID, name, email, phone, DOB, gender, address, role).
2. **`courses`**: Stores course listings (course ID, code, course name, department, credits, assigned instructor).
3. **`student_enrollments`**: Stores enrollment details (enrollment ID, student ID, course ID, date, grade, semester).

---

## Available MCP Tools (`tooling.py`)

- `get_student_info(student_id_or_name)`: Retrieve student profiles by ID, name, or email.
- `get_course_details(course_code_or_name)`: Get course details and instructor info.
- `get_student_enrollments(student_id_or_name)`: List all courses and grades for a student.
- `get_students_by_course(course_code_or_name)`: List students enrolled in a given course.
- `list_courses_by_department(department)`: List all courses in an academic department.
- `get_courses_taught_by_faculty(faculty_id_or_name)`: List courses taught by a faculty member.
- `search_persons(query, role)`: Search persons across roles (student, faculty, staff).
- `get_course_statistics(course_code_or_name)`: Summary stats (total enrolled, grade distribution).
- `list_all_courses()`: Complete directory of all college courses.
- `execute_custom_sql_query(query)`: Read-only query execution for complex aggregations.

---

## Setup & Installation

### 1. Prerequisites
- Python 3.10+ installed
- Google Gemini API Key ([Google AI Studio](https://aistudio.google.com/))

### 2. Install Dependencies
```bash
pip install -r requirements.txt
```

### 3. Configure Environment Variables
Create or edit your `.env` file in the project root:
```env
GEMINI_API_KEY=your_actual_gemini_api_key_here
```

### 4. Generate Synthetic Database
Initialize the database with synthetic records:
```bash
python create_data.py
```

---

## Usage

### Run the Interactive AI Bot
```bash
python bot.py
```
Type your academic or administrative questions (e.g., *"Show me all courses in Computer Science"* or *"What grades did John Doe receive?"*). Type `exit` or `quit` to end the session.

### Connect as an MCP Server (e.g. IDE / Claude Desktop / Antigravity)
Reference `mcp_config.example.json` to register `tooling.py` in your MCP client configuration:
```json
{
  "mcpServers": {
    "college-tools-mcp": {
      "command": "python",
      "args": [
        "-Wignore",
        "<FULL_PATH_TO>/tooling.py"
      ]
    }
  }
}
```