# MCP Database Server

A Model Context Protocol (MCP) server that provides PostgreSQL database access with query execution and schema inspection capabilities.

## Overview

This MCP server enables AI assistants (like Claude) to interact with a PostgreSQL database containing data from multiple systems:
- **cs** (CustomerScoreView): Customer and credit assessment data
- **dms** (Data Management System): Document management data
- **los** (Loan Management System): Loan and payment data
- **mls** (Mahatheun Leasing System): Contract and asset leasing data

## Features

- ✅ **Query Execution**: Execute SQL SELECT, INSERT, UPDATE, DELETE queries
- ✅ **Schema Inspection**: List schemas, tables, and column details
- ✅ **Multi-Schema Support**: Organize data by system (cs, dms, los, mls)
- ✅ **Docker Setup**: PostgreSQL in Docker with automatic initialization
- ✅ **CSV Import**: Sample data included and ready to import

## Prerequisites

- Python 3.10 or higher
- Docker and Docker Compose
- pip (Python package installer)

## Installation

1. **Clone or navigate to the project directory:**
   ```bash
   cd c:\Users\chaya\project\mcp-database
   ```

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

3. **Configure environment variables:**
   ```bash
   copy .env.example .env
   ```
   
   Edit `.env` if you need to change database credentials (optional).

## Setup

### 1. Start PostgreSQL Database

Start the PostgreSQL container with Docker Compose:

```bash
docker-compose up -d
```

Verify the database is running:

```bash
docker-compose ps
```

### 2. Verify Database Initialization

The database will automatically initialize with schemas and sample data. Check the schemas:

```bash
docker-compose exec postgres psql -U postgres -d mcp_database -c "\dn"
```

List tables in a schema:

```bash
docker-compose exec postgres psql -U postgres -d mcp_database -c "\dt cs.*"
```

### 3. (Optional) Import CSV Data

Sample CSV files are provided in `raw_data/`. To import them into the database:

```bash
docker-compose exec postgres bash -c "cd /raw_data && find . -name '*.csv' -type f"
```

Import a specific CSV file:

```bash
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY cs.customers FROM '/raw_data/cs/customers.csv' WITH CSV HEADER;"
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY dms.documents FROM '/raw_data/dms/documents.csv' WITH CSV HEADER;"
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY los.loans FROM '/raw_data/los/loans.csv' WITH CSV HEADER;"
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY mls.contracts FROM '/raw_data/mls/contracts.csv' WITH CSV HEADER;"
```

## Running the MCP Server

### Test Locally

Run the server directly to test:

```bash
python src/server.py
```

The server will start and listen for MCP messages via stdio.

### Configure with Claude Desktop

To use this MCP server with Claude Desktop, add it to your Claude configuration file:

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

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

Add this configuration:

```json
{
  "mcpServers": {
    "mcp-database": {
      "command": "python",
      "args": ["c:\\Users\\chaya\\project\\mcp-database\\src\\server.py"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "mcp_database",
        "DB_USER": "postgres",
        "DB_PASSWORD": "postgres"
      }
    }
  }
}
```

Restart Claude Desktop to load the server.

## Available MCP Tools

### 1. `list_schemas`
Lists all available database schemas.

**Example:**
```
Can you list all database schemas?
```

### 2. `list_tables`
Lists all tables, optionally filtered by schema.

**Parameters:**
- `schema` (optional): Schema name to filter (e.g., "cs", "dms", "los", "mls")

**Example:**
```
Show me all tables in the cs schema
```

### 3. `describe_table`
Get detailed column information for a specific table.

**Parameters:**
- `table_name` (required): Name of the table
- `schema` (optional): Schema name (default: "public")

**Example:**
```
Describe the structure of the customers table in the cs schema
```

### 4. `execute_query`
Execute a SQL query on the database.

**Parameters:**
- `query` (required): SQL query string
- `params` (optional): Array of parameters for parameterized queries

**Examples:**
```
Query all customers with credit score above 700

SELECT * FROM cs.customers WHERE credit_score > 700

Get total loan amount by customer

SELECT customer_code, SUM(loan_amount) as total_loans 
FROM los.loans 
GROUP BY customer_code
```

## Database Schema

### cs (CustomerScoreView)
- `customers`: Customer information and credit scores
- `credit_assessments`: Credit assessment history

### dms (Data Management System)
- `documents`: Document metadata and storage info
- `document_versions`: Document version history

### los (Loan Management System)
- `loans`: Loan accounts and terms
- `payments`: Payment history

### mls (Mahatheun Leasing System)
- `contracts`: Lease contract information
- `assets`: Asset details for leased items
- `lease_payments`: Lease payment records

## Project Structure

```
mcp-database/
├── src/
│   ├── server.py          # Main MCP server
│   ├── database.py        # Database connection manager
│   └── tools.py           # MCP tool definitions
├── db/
│   └── init.sql           # Database initialization script
├── raw_data/
│   ├── cs/                # CustomerScoreView CSV files
│   ├── dms/               # Data Management System CSV files
│   ├── los/               # Loan Management System CSV files
│   └── mls/               # Mahatheun Leasing System CSV files
├── docker-compose.yml     # Docker configuration
├── requirements.txt       # Python dependencies
├── pyproject.toml        # Python project metadata
└── .env.example          # Environment variable template
```

## Troubleshooting

### Docker container won't start
```bash
# Check logs
docker-compose logs postgres

# Restart container
docker-compose restart postgres
```

### Connection refused error
- Ensure PostgreSQL is running: `docker-compose ps`
- Check port 5432 is not in use by another process
- Verify `.env` file has correct credentials

### Import errors in Python
```bash
# Reinstall dependencies
pip install --upgrade -r requirements.txt
```

## Development

To extend the server:

1. Add new tools in `src/tools.py`
2. Register handlers in `src/server.py`
3. Update database schema in `db/init.sql`
4. Add sample data in `raw_data/`

## License

This project is provided as-is for database integration purposes.