# DB Analytics + Query Platform (FastAPI)

## Project Structure

- `app/main.py`: FastAPI app initialization and router registration
- `app/core`: config and security helpers
- `app/db`: DB engine/session and init
- `app/models`: SQLAlchemy ORM models
- `app/schemas`: Pydantic request/response schemas
- `app/api/routes`: endpoint routes
- `app/services`: business logic services
- `app/mcp`: MCP integration stubs
- `alembic`: migration configuration

## Requirements

- Python 3.10+
- SQLite

Install dependencies:

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

## Local Run

```bash
uvicorn app.main:app --reload --host 0.0.0.0 --port 8000
```

Open docs at `http://localhost:8000/docs`.

## Endpoints

- `POST /api/auth/register`: create user
- `POST /api/auth/token`: login and retrieve JWT
- `GET /api/auth/me`: get current user
- `POST /api/connections/test`: validate DB connection
- `POST /api/connections`: save connection (admin only)
- `POST /api/queries/execute`: execute SQL query
- `GET /api/reports/users/total`: return user total
- `GET /api/reports/access-logs`
- `GET /api/reports/objects/summary`
- `GET /api/metrics/performance`
- `GET /api/metrics/top-objects`
- `GET /api/metrics/frequency`
- `POST /api/mcp/run-query`
- `GET /api/mcp/schema`

## Sample Flow

1. Register admin

```bash
curl -X POST "http://localhost:8000/api/auth/register" -H "Content-Type: application/json" -d '{"username":"admin","email":"admin@example.com","password":"password","role":"admin"}'
```

2. Get token

```bash
curl -X POST "http://localhost:8000/api/auth/token" -H "Content-Type: application/x-www-form-urlencoded" -d "username=admin&password=password"
```

3. Use token for protected endpoints

```bash
curl -H "Authorization: Bearer <TOKEN>" "http://localhost:8000/api/reports/users/total"
```

### Expected Register Response

Successful registration returns:

```json
HTTP/1.1 200 OK
{
  "id": 1,
  "username": "admin",
  "email": "admin@example.com",
  "role": "admin",
  "is_active": true,
  "created_at": "2026-03-25T12:34:56.789000"
}
```

If the user exists:

```json
HTTP/1.1 400 Bad Request
{
  "detail": "User already exists"
}
```

## Alembic migrations

```bash
alembic revision --autogenerate -m "init"
alembic upgrade head
```