## ORAC Risk Assessments API – Documentation

This backend exposes generic RESTful CRUD endpoints for all tables in the shared `hse` MySQL database (same schema as SAT).

Base URL (default):

- **Local**: `http://localhost:3000/api`

---

### 1. Common patterns

- **List records**  
  - `GET /api/<resource>`
  - Returns an array of rows.

- **Filter records (query params)**  
  - Exact equality on any column: `?column=value`  
  - Text search (LIKE) on any column: `?column_like=value`  
  - Examples:
    - `GET /api/users?department_id=3&status_id=1`
    - `GET /api/users?employee_name_like=John`
    - `GET /api/orac_questions?question_like=fire`

- **Get one record by primary key(s)**  
  - `GET /api/<resource>/:pk1[/:pk2/... ]`
  - PK order always matches the table’s PRIMARY KEY in MySQL.

- **Create record**  
  - `POST /api/<resource>`
  - Body: JSON object with any valid columns for that table.
  - The DB applies defaults / nullability.

- **Update record by primary key(s)**  
  - `PUT /api/<resource>/:pk1[/:pk2/... ]`
  - Body: JSON object with columns to update (partial or full).

- **Delete record by primary key(s)**  
  - `DELETE /api/<resource>/:pk1[/:pk2/... ]`

All endpoints return JSON. Errors are standardized by the central error handler.

---

### 2. Health check

- **GET `/api/health`**
  - Response: `{ "status": "ok" }`

---

### 3. Users and related reference data

#### 3.1 Users

Table: `users`  
Primary key: (`user_id`, `employee_id`, `status_id`, `job_title_id`, `department_id`, `location_id`)

- `GET /api/users`
  - Optional filters: any column, e.g. `?department_id=3&status_id=1`, `?employee_name_like=John`.

- `GET /api/users/:user_id/:employee_id/:status_id/:job_title_id/:department_id/:location_id`

- `POST /api/users`
  - Example body:
  ```json
  {
    "user_id": 1,
    "employee_id": "E1001",
    "status_id": 1,
    "job_title_id": 2,
    "department_id": 3,
    "location_id": 4,
    "employee_name": "John Doe",
    "email_id": "john@example.com",
    "phonenumber": "1234567890"
  }
  ```

- `PUT /api/users/:user_id/:employee_id/:status_id/:job_title_id/:department_id/:location_id`

- `DELETE /api/users/:user_id/:employee_id/:status_id/:job_title_id/:department_id/:location_id`

#### 3.2 Account status

Table: `account_status`  
PK: `status_id`

- `GET /api/account_status`
- `GET /api/account_status/:status_id`
- `POST /api/account_status`
- `PUT /api/account_status/:status_id`
- `DELETE /api/account_status/:status_id`

#### 3.3 Departments

Table: `departments`  
PK: `department_id`

- `GET /api/departments`
- `GET /api/departments/:department_id`
- `POST /api/departments`
- `PUT /api/departments/:department_id`
- `DELETE /api/departments/:department_id`

#### 3.4 Job titles

Table: `job_title`  
PK: `job_title_id`

- `GET /api/job_title`
- `GET /api/job_title/:job_title_id`
- `POST /api/job_title`
- `PUT /api/job_title/:job_title_id`
- `DELETE /api/job_title/:job_title_id`

#### 3.5 Countries and locations

Table: `country` (PK: `country_id`)  
Table: `locations` (PK: `location_id`)

- `GET /api/country`
- `GET /api/country/:country_id`
- `POST /api/country`
- `PUT /api/country/:country_id`
- `DELETE /api/country/:country_id`

- `GET /api/locations`
- `GET /api/locations/:location_id`
- `POST /api/locations`
- `PUT /api/locations/:location_id`
- `DELETE /api/locations/:location_id`

To fetch locations by country:

- `GET /api/locations?country_id=1`

#### 3.6 Location–department mapping

Table: `location_departments`  
PK: (`department_id`, `location_id`)

- `GET /api/location_departments`
- `GET /api/location_departments/:department_id/:location_id`
- `POST /api/location_departments`
- `PUT /api/location_departments/:department_id/:location_id`
- `DELETE /api/location_departments/:department_id/:location_id`

---

### 4. Roles and access

#### 4.1 Roles

Table: `roles` (PK: `role_id`)

- `GET /api/roles`
- `GET /api/roles/:role_id`
- `POST /api/roles`
- `PUT /api/roles/:role_id`
- `DELETE /api/roles/:role_id`

#### 4.2 User-role mapping

Table: `user_role`  
PK: (`user_role_id`, `role_id`, `user_id`)

- `GET /api/user_role`
- `GET /api/user_role/:user_role_id/:role_id/:user_id`
- `POST /api/user_role`
- `PUT /api/user_role/:user_role_id/:role_id/:user_id`
- `DELETE /api/user_role/:user_role_id/:role_id/:user_id`

---

### 5. Tickets

#### 5.1 Ticket status and types

Tables:

- `tickets_status` (PK: `status_id`)
- `tickets_type` (PK: `type_id`)

Endpoints:

- `GET /api/tickets_status`, `GET /api/tickets_status/:status_id`, `POST`, `PUT`, `DELETE`
- `GET /api/tickets_type`, `GET /api/tickets_type/:type_id`, `POST`, `PUT`, `DELETE`

#### 5.2 Tickets

Table: `tickets`  
PK: (`id`, `user_id`, `ticket_type_id`, `status_id`)

- `GET /api/tickets`
  - Example filters:
    - `GET /api/tickets?user_id=1`
    - `GET /api/tickets?status_id=2`
- `GET /api/tickets/:id/:user_id/:ticket_type_id/:status_id`
- `POST /api/tickets`
- `PUT /api/tickets/:id/:user_id/:ticket_type_id/:status_id`
- `DELETE /api/tickets/:id/:user_id/:ticket_type_id/:status_id`

#### 5.3 Ticket logs

Table: `tickets_logs`  
PK: (`id`, `ticket_id`, `status_id`, `ticket_type_id`)

- `GET /api/tickets_logs`
- `GET /api/tickets_logs/:id/:ticket_id/:status_id/:ticket_type_id`
- `POST /api/tickets_logs`
- `PUT /api/tickets_logs/:id/:ticket_id/:status_id/:ticket_type_id`
- `DELETE /api/tickets_logs/:id/:ticket_id/:status_id/:ticket_type_id`

---

### 6. ORAC forms, sections, questions, answers

#### 6.1 Form details

Table: `orac_form_details` (PK: `form_id`)

- `GET /api/orac_form_details`
- `GET /api/orac_form_details/:form_id`
- `POST /api/orac_form_details`
- `PUT /api/orac_form_details/:form_id`
- `DELETE /api/orac_form_details/:form_id`

#### 6.2 Form archives and logs

Tables:

- `orac_archive_form` (PK: `form_id`)
- `orac_form_logs` (PK: `user_id`)

Endpoints:

- `GET /api/orac_archive_form`, `GET /api/orac_archive_form/:form_id`, `POST`, `PUT`, `DELETE`
- `GET /api/orac_form_logs`, `GET /api/orac_form_logs/:user_id`, `POST`, `PUT`, `DELETE`

#### 6.3 Section details

Table: `orac_section_details` (PK: `section_id`)

- `GET /api/orac_section_details`
- `GET /api/orac_section_details/:section_id`
- `POST /api/orac_section_details`
- `PUT /api/orac_section_details/:section_id`
- `DELETE /api/orac_section_details/:section_id`

#### 6.4 Section archives and logs

Tables:

- `orac_archive_section` (PK: `section_id`)
- `orac_section_logs` (PK: `user_id`)

Endpoints:

- `GET /api/orac_archive_section`, `GET /api/orac_archive_section/:section_id`, `POST`, `PUT`, `DELETE`
- `GET /api/orac_section_logs`, `GET /api/orac_section_logs/:user_id`, `POST`, `PUT`, `DELETE`

#### 6.5 Form–section mapping (dynamic form builder)

Table: `orac_form_section_map`  
PK: (`form_id`, `section_id`)  
Access: **both admin and user** (authenticated via `requireAuth`)

| Method | Endpoint | Description |
|--------|--------|-------------|
| `GET` | `/api/orac_form_section_map` | List all form–section mappings |
| `GET` | `/api/orac_form_section_map/form/:form_id` | Get a form with all sections and their questions, types & options |
| `GET` | `/api/orac_form_section_map/:form_id/:section_id` | Get a specific mapping |
| `POST` | `/api/orac_form_section_map` | Map a section to a form (validated) |
| `PUT` | `/api/orac_form_section_map/:form_id/:section_id` | Update a mapping |
| `DELETE` | `/api/orac_form_section_map/:form_id/:section_id` | Remove a mapping |

**GET `/api/orac_form_section_map/form/:form_id` response example:**

```json
{
  "form_id": 2,
  "form_parent_id": null,
  "title": "Safety Assessment Form",
  "description": "Complete risk assessment",
  "created_by": 1,
  "created_date": "2026-03-15T00:00:00.000Z",
  "sections": [
    {
      "section_id": 1,
      "section_parent_id": null,
      "title": "Hazard Identification",
      "created_by": 1,
      "created_date": "2026-03-15T00:00:00.000Z",
      "questions": [
        {
          "question_id": 5,
          "question": "What hazards are present?",
          "question_type_id": 2,
          "question_type": "multiple_choice",
          "options": [
            { "question_options_id": 1, "option": "Electrical" },
            { "question_options_id": 2, "option": "Chemical" }
          ]
        }
      ]
    }
  ]
}
```

**POST request body:**

```json
{
  "form_id": 2,
  "section_id": 1
}
```

**POST validations:**

- `form_id` and `section_id` must be positive integers
- `form_id` must exist in `orac_form_details`
- `section_id` must exist in `orac_section_details`
- Duplicate `(form_id, section_id)` pairs return `409 Conflict`

**POST success response (201):**

```json
{
  "message": "Section mapped to form successfully",
  "data": {
    "form_id": 2,
    "section_id": 1
  }
}
```

**Error responses:**

| Status | Reason |
|--------|--------|
| `400` | Missing or invalid `form_id` / `section_id` |
| `401` | Missing or expired token |
| `404` | Form or section not found |
| `409` | Mapping already exists |

#### 6.6 Section–question mapping (dynamic form builder)

Table: `orac_section_question_map`  
PK: (`section_id`, `question_id`)  
Access: **both admin and user** (authenticated via `requireAuth`)  
Legacy alias: `/api/orac_section_question_map` (typo kept for backward compatibility)

| Method | Endpoint | Description |
|--------|--------|-------------|
| `GET` | `/api/orac_section_question_map` | List all section–question mappings |
| `GET` | `/api/orac_section_question_map/section/:section_id` | Get a section with all its mapped questions, types, and options |
| `GET` | `/api/orac_section_question_map/:section_id/:question_id` | Get a specific mapping |
| `POST` | `/api/orac_section_question_map` | Map a question to a section (validated) |
| `PUT` | `/api/orac_section_question_map/:section_id/:question_id` | Update a mapping |
| `DELETE` | `/api/orac_section_question_map/:section_id/:question_id` | Remove a mapping |

**GET `/api/orac_section_question_map/section/:section_id` response example:**

```json
{
  "section_id": 1,
  "section_parent_id": null,
  "title": "Hazard Identification",
  "created_by": 1,
  "created_date": "2026-03-15T00:00:00.000Z",
  "questions": [
    {
      "question_id": 5,
      "question": "What hazards are present?",
      "question_type_id": 2,
      "question_type": "multiple_choice",
      "options": [
        { "question_options_id": 1, "option": "Electrical" },
        { "question_options_id": 2, "option": "Chemical" }
      ]
    }
  ]
}
```

**POST request body:**

```json
{
  "section_id": 1,
  "question_id": 5
}
```

**POST validations:**

- `section_id` and `question_id` must be positive integers
- `section_id` must exist in `orac_section_details`
- `question_id` must exist in `orac_questions`
- Duplicate `(section_id, question_id)` pairs return `409 Conflict`

**POST success response (201):**

```json
{
  "message": "Question mapped to section successfully",
  "data": {
    "section_id": 1,
    "question_id": 5
  }
}
```

**Error responses:**

| Status | Reason |
|--------|--------|
| `400` | Missing or invalid `section_id` / `question_id` |
| `401` | Missing or expired token |
| `404` | Section or question not found |
| `409` | Mapping already exists |

#### 6.7 Question types and questions

Tables:

- `orac_question_type` (PK: `question_type_id`)
- `orac_questions` (PK: (`question_id`, `question_type_id`))

Endpoints:

- `GET /api/orac_question_type`, `GET /api/orac_question_type/:question_type_id`, `POST`, `PUT`, `DELETE`
- `GET /api/orac_questions`
- `GET /api/orac_questions/:question_id/:question_type_id`
- `POST /api/orac_questions`
- `PUT /api/orac_questions/:question_id/:question_type_id`
- `DELETE /api/orac_questions/:question_id/:question_type_id`

#### 6.8 Question options

Table: `orac_question_options`  
PK: (`question_options_id`, `question_id`)

- `GET /api/orac_question_options`
- `GET /api/orac_question_options/:question_options_id/:question_id`
- `POST /api/orac_question_options`
- `PUT /api/orac_question_options/:question_options_id/:question_id`
- `DELETE /api/orac_question_options/:question_options_id/:question_id`

#### 6.9 Combined questions

Table: `orac_combined_question`  
PK: (`main_question_id`, `child_question_id`)

- `GET /api/orac_combined_question`
- `GET /api/orac_combined_question/:main_question_id/:child_question_id`
- `POST /api/orac_combined_question`
- `PUT /api/orac_combined_question/:main_question_id/:child_question_id`
- `DELETE /api/orac_combined_question/:main_question_id/:child_question_id`

#### 6.10 Answers, snaps, submissions, verification

Tables & primary keys:

- `orac_answers` – (`answers_id`, `submission_id`, `question_id`)
- `orac_snaps` – (`snap_id`, `assessment_id`)
- `orac_submissions` – (`submission_id`, `assessment_id`, `submitter_id`, `snap_id`)
- `orac_verification_flow` – (`assessment_id`, `occurrence`)

Example endpoints (pattern is the same as above):

- `GET /api/orac_answers`, `GET /api/orac_answers/:answers_id/:submission_id/:question_id`, `POST`, `PUT`, `DELETE`
- `GET /api/orac_snaps`, `GET /api/orac_snaps/:snap_id/:assessment_id`, `POST`, `PUT`, `DELETE`
- `GET /api/orac_submissions`, `GET /api/orac_submissions/:submission_id/:assessment_id/:submitter_id/:snap_id`, `POST`, `PUT`, `DELETE`
- `GET /api/orac_verification_flow`, `GET /api/orac_verification_flow/:assessment_id/:occurrence`, `POST`, `PUT`, `DELETE`

---

### 7. ORAC assessments and participants

#### 7.1 Assessments

Table: `orac_assessments`  
PK: (`assessment_id`, `uploader_user_id`, `assessment_status_id`)

- `GET /api/orac_assessments`
  - Examples:
    - `GET /api/orac_assessments?country_id=1&location_id=4`
    - `GET /api/orac_assessments?assessment_status_id=2`
- `GET /api/orac_assessments/:assessment_id/:uploader_user_id/:assessment_status_id`
- `POST /api/orac_assessments`
- `PUT /api/orac_assessments/:assessment_id/:uploader_user_id/:assessment_status_id`
- `DELETE /api/orac_assessments/:assessment_id/:uploader_user_id/:assessment_status_id`
- `GET /api/orac_assessments/user/:user_id/grouped_by_status`
  - Returns all assessments for one user, grouped by `assessment_status_id`.
  - Response shape:
  ```json
  {
    "user_id": 10,
    "total_assessments": 3,
    "grouped_assessments": [
      {
        "assessment_status_id": 1,
        "assessment_status_name": "Pending",
        "count": 2,
        "assessments": []
      },
      {
        "assessment_status_id": 3,
        "assessment_status_name": "InProgress",
        "count": 1,
        "assessments": []
      }
    ]
  }
  ```

#### 7.2 Assessment status

Table: `orac_assessments_status` (PK: `assessment_status_id`)

- `GET /api/orac_assessments_status`
- `GET /api/orac_assessments_status/:assessment_status_id`
- `POST /api/orac_assessments_status`
- `PUT /api/orac_assessments_status/:assessment_status_id`
- `DELETE /api/orac_assessments_status/:assessment_status_id`

#### 7.3 Participants

Table: `orac_participants`  
PK: (`assessment_id`, `participant_user_id`)

- `GET /api/orac_participants`
- `GET /api/orac_participants/:assessment_id/:participant_user_id`
- `POST /api/orac_participants`
- `PUT /api/orac_participants/:assessment_id/:participant_user_id`
- `DELETE /api/orac_participants/:assessment_id/:participant_user_id`

---

### 8. User and section/form logs

#### 8.1 User logs

Table: `users_logs` (PK: `user_id`)

- `GET /api/users_logs`
- `GET /api/users_logs/:user_id`
- `POST /api/users_logs`
- `PUT /api/users_logs/:user_id`
- `DELETE /api/users_logs/:user_id`

#### 8.2 Form and section logs

Covered above under ORAC forms and sections.

---

### 9. Error responses

Errors use a consistent JSON shape:

- **Validation / bad request** (e.g. missing PK parameter, invalid body):
  - Status: `400`
  - Body: `{ "message": "...", "details": optional }`

- **Not found**:
  - Status: `404`
  - Body: `{ "message": "<table> record not found" }`

- **Server / DB error**:
  - Status: `500`
  - Body: `{ "message": "Internal Server Error" }` (plus `stack` in non-production)

---

### 10. Running the API

1. Create `.env` from `.env.example` and set DB credentials.
2. Install dependencies:

```bash
npm install
```

3. Start the server:

```bash
npm run dev
```

4. Test:
   - `GET http://localhost:3000/api/health`
   - Then try table-specific endpoints as documented above.

