# Database Schema

This document describes the database schema used by the Database Service.

## Tables

### User

Stores user account information.

| Column     | Type                | Description                       |
|------------|---------------------|-----------------------------------|
| id         | INTEGER             | Primary key                       |
| name       | VARCHAR             | Username                          |
| email      | VARCHAR             | Email address                     |
| role       | VARCHAR             | User role (e.g., admin, user)     |
| createdAt  | TIMESTAMP           | Account creation timestamp        |
| updatedAt  | TIMESTAMP           | Last update timestamp             |

### Character

Stores character information.

| Column     | Type                | Description                       |
|------------|---------------------|-----------------------------------|
| id         | INTEGER             | Primary key                       |
| userId     | INTEGER             | Foreign key to User.id            |
| name       | VARCHAR             | Character name                    |
| money      | BIGINT              | Character's currency              |
| inventory  | JSONB               | Serialized inventory data         |
| stats      | JSONB               | Character statistics              |
| skills     | JSONB               | Character skills                  |
| looks      | JSONB               | Character appearance              |
| position   | JSONB               | Character position in world       |
| createdAt  | TIMESTAMP           | Character creation timestamp      |
| updatedAt  | TIMESTAMP           | Last update timestamp             |
| deletedAt  | TIMESTAMP           | Deletion timestamp (if deleted)   |
| isActive   | BOOLEAN             | Whether character is active       |

### Session

Stores session information.

| Column     | Type                | Description                       |
|------------|---------------------|-----------------------------------|
| id         | VARCHAR             | Session ID (primary key)          |
| userId     | INTEGER             | Foreign key to User.id            |
| createdAt  | TIMESTAMP           | Session creation timestamp        |
| expiresAt  | TIMESTAMP           | Session expiration timestamp      |

## JSON Structures

### Inventory

```json
{
  "items": [
    {
      "id": 1,
      "itemId": 1001,
      "count": 5,
      "slot": 0
    }
  ],
  "capacity": 100
}
```

### Stats

```json
{
  "strength": 10,
  "dexterity": 10,
  "intelligence": 10,
  "vitality": 10,
  "hp": 100,
  "mp": 100,
  "level": 1,
  "experience": 0
}
```

### Skills

```json
{
  "skills": [
    {
      "id": 1,
      "level": 1,
      "cooldown": 0
    }
  ]
}
```

### Looks

```json
{
  "race": 1,
  "gender": 0,
  "hair": 1,
  "face": 1,
  "height": 180,
  "skinColor": "#F5DEB3"
}
```

### Position

```json
{
  "mapId": 1,
  "x": 100.0,
  "y": 100.0,
  "z": 0.0,
  "direction": 0
}
```

## Indexes

- `user_name_idx`: Index on User.name for fast username lookups
- `user_email_idx`: Index on User.email for fast email lookups
- `character_user_id_idx`: Index on Character.userId for fast character list retrieval
- `character_name_idx`: Index on Character.name for name uniqueness checks
- `session_user_id_idx`: Index on Session.userId for user session lookups