# Database Setup Documentation

## Overview
The `on-internal-api` service uses a multi-database architecture primarily based on **PostgreSQL**, implemented using the **Sequelize ORM**. It connects to four distinct PostgreSQL databases and one MongoDB instance.

## 1. Environment Configuration
To run the application, you must configure the following environment variables in your `.env` file to establish connections to the required databases.

### Sunshine Database (Main Employee/HR DB)
| Variable | Description |
|----------|-------------|
| `PG_SUNSHINE_HOST` | Database host address |
| `PG_SUNSHINE_USER` | Database username |
| `PG_SUNSHINE_PASSWORD` | Database password |
| `PG_SUNSHINE_DB` | Database name |
| `PG_SUNSHINE_DIALECT` | `postgres` |
| `PG_SUNSHINE_PORT` | Port (default: 5432) |

### Fleet Database
| Variable | Description |
|----------|-------------|
| `PG_FLEET_HOST` | Database host |
| `PG_FLEET_USER` | Database username |
| `PG_FLEET_PASSWORD` | Database password |
| `PG_FLEET_DB` | Database name |
| `PG_FLEET_dialect` | `postgres` |
| `PG_FLEET_PORT` | Port |

### CMS Database
| Variable | Description |
|----------|-------------|
| `PG_CMS_HOST` | Database host |
| `PG_CMS_USER` | Database username |
| `PG_CMS_PASSWORD` | Database password |
| `PG_CMS_DB` | Database name |
| `PG_CMS_dialect` | `postgres` |
| `PG_CMS_PORT` | Port |

### Mitra Database (Partners)
| Variable | Description |
|----------|-------------|
| `PG_MITRA_HOST` | Database host |
| `PG_MITRA_USER` | Database username |
| `PG_MITRA_PASSWORD` | Database password |
| `PG_MITRA_DB` | Database name |
| `PG_MITRA_DIALECT` | `postgres` |
| `PG_MITRA_PORT` | Port |

*(Note: There is also a `MONGO_MITRA` variable for a MongoDB connection).*

---

## 2. Key Database Schemas & Tables
The application uses specific PostgreSQL schemas beyond the default `public`. You may need to create these schemas manually if Sequelize does not create them automatically.

*   **Public**: Default schema for most tables.
*   **Approval**: Used for `area` and `template_json` tables.
*   **CMS**: Used for `ad` and other content management tables.

### Sunshine DB Models
This database appears to handle employee management, authentication, and core organizational structure.

#### Table: `users`
*   **Primary Key**: `username` (String)
*   **Foreign Key**: `role_code` references `roles(code)`
*   **Columns**:
    *   `email` (String, Unique)
    *   `password` (String)
    *   `login_token` (JSONB)
    *   `imei` (String)
    *   `created_at`, `updated_at` (Timestamp)

#### Table: `roles`
*   **Primary Key**: `code` (String)
*   **Columns**:
    *   `name` (String, Not Null)
    *   `access_menu` (Array of Integers, Not Null)
    *   `status` (Boolean)
    *   `division` (String)

#### Table: `positions`
*   **Primary Key**: `id` (Integer, Auto-increment)
*   **Columns**:
    *   `name` (String, Not Null)
    *   `parent_id` (Integer) - Likely for hierarchy

#### Table: `division`
*   **Primary Key**: `id` (Integer, Auto-increment)
*   **Columns**:
    *   `name` (String, Not Null)
    *   `status` (Boolean, Not Null)

### Ticketing Models
Likely located in the Sunshine or Fleet database (referencing `vendors` and `agents`).

#### Table: `tickets`
*   **Primary Key**: `tag` (String) - *Note: `id` is auto-increment but `tag` is marked Primary Key in definition.*
*   **Foreign Keys**:
    *   `vendor_code` -> `vendors(code)`
    *   `agent_code` -> `agents(code)`
    *   `problem_type_code` -> `problem_types(code)`
    *   `created_by` -> `users(username)`
*   **Columns**:
    *   `waybill` (String)
    *   `status` (Enum: 'Solved', 'Open', 'In Progress', etc.)
    *   `description` (Text)
    *   `attachments` (Array of Strings) - *Note: Contains logic to handle file paths.*

### Approval Schema Models

#### Table: `approval.area`
*   **Primary Key**: `area_code` (String)
*   **Columns**:
    *   `id` (Integer, Unique, Auto-increment)
    *   `area_name` (String)
    *   `large_area_group` (String)
    *   `status` (String)

#### Table: `approval.template_json`
*   **Primary Key**: `type` (String)
*   **Columns**:
    *   `template` (JSONB)
*   **Timestamps**: False

### Mitra DB Models

#### Table: `driver_login`
*   **Primary Key**: `id` (BigInt)
*   **Columns**:
    *   `email` (String, Not Null)
    *   `password` (String, Not Null)
    *   `login_token` (String)
    *   `online` (Boolean)
    *   `disabled` (Boolean)

---

## 3. Initialization Steps
1.  **Create Databases**: Ensure the 4 databases defined in the environment variables exist (`PG_SUNSHINE_DB`, etc.).
2.  **Create Schemas**: Run the following SQL in the appropriate databases:
    ```sql
    CREATE SCHEMA IF NOT EXISTS "approval";
    CREATE SCHEMA IF NOT EXISTS "cms";
    ```
3.  **Run Migration/Sync**:
    The application uses `sequelize.sync()` (implied by model definitions). When the application starts, it will attempt to create these tables if they do not exist. Ensure the database user has `CREATE TABLE` permissions.

## 4. Notes on Data Types
*   **Arrays**: The application makes heavy use of PostgreSQL `ARRAY` types (e.g., `access_menu` in `roles`, `attachments` in `tickets`).
*   **JSONB**: Used for dynamic data like `login_token` and `template`.
*   **Virtual Fields**: several models (`roles`, `area`) define virtual fields (`code_name`, `formattedName`). These exist in the API layer only and are not columns in the database.