# Users & Access

| | |
|---|---|
| **Nav ID** | `users-access` |
| **Component** | `AdminUsersAccessPage.jsx` |
| **Services** | `usersApi.js`, `masterApi.js` |

---

## Endpoints

### GET `/users`

List HSE users for the admin table.

```http
GET /api/ptw/users
Authorization: Bearer <admin_token>
```

Optional query filters: `user_id`, `email_id`, `employee_name_like`, etc.

---

### GET `/users/:user_id`

Fetch a single user record.

---

### POST `/users`

Create user with optional profile photo.

**Content-Type:** `multipart/form-data`

| Field | Type | Required |
|-------|------|----------|
| `employee_name` | string | Yes |
| `employee_id` | string | |
| `email_id` | string | |
| `phonenumber` | string | |
| `photo` | file | |

Also accepts JSON fields for non-file creates when no photo is uploaded.

---

### PUT `/users/:user_id`

Update user fields and/or replace photo. Same multipart shape as create.

**Status toggle (JSON):**

```http
PUT /api/ptw/users/10
Content-Type: application/json

{ "status_id": 2 }
```

---

### DELETE `/users/:user_id`

Remove user; writes audit entry to `users_logs`.

---

### GET `/users/:user_id/photo`

Serve profile photo blob. Used as `<img src>` via `userPhotoUrl(userId)`.

---

### GET `/roles`

Master lookup — list assignable roles.

```http
GET /api/ptw/roles
```

---

### GET `/user_role`

List user–role assignments. Filter: `?user_id=10`.

---

### POST `/user_role`

Assign a role.

```json
{ "user_id": 10, "role_id": 1 }
```

---

### DELETE `/user_role/:user_role_id`

Remove a role assignment.

---

## Page flow

1. Load users + roles on mount.
2. Open user modal → load `user_role` rows for that user.
3. Create/update user via multipart; toggle status via JSON PUT.
4. Assign/remove roles via `user_role` CRUD.

**Controller:** `usersController.js` (dedicated); `user_role` via generic CRUD.

---

## SQL queries

### List users (photo excluded from JSON; `has_photo` flag instead)

```sql
SELECT user_id, employee_id, status_id, employee_name, job_title_id,
       email_id, phonenumber, department_id, location_id, password,
       created_at,
       (CASE WHEN photo IS NOT NULL AND LENGTH(photo) > 0 THEN 1 ELSE 0 END) AS has_photo
FROM users
WHERE `email_id` = ?           -- optional filters from query string
ORDER BY user_id ASC;
```

### Get one user + role IDs

```sql
SELECT user_id, employee_id, status_id, employee_name, ...
FROM users WHERE user_id = ? LIMIT 1;

SELECT role_id FROM user_role WHERE user_id = ?;
```

### Serve photo

```sql
SELECT photo FROM users WHERE user_id = ? LIMIT 1;
```

### Create user (transaction)

```sql
INSERT INTO users (`employee_name`, `email_id`, `photo`, ...) VALUES (?, ?, ?, ...);

INSERT INTO user_role (role_id, user_id) VALUES (?, ?);   -- repeated per role_id
```

### Update user (transaction)

Before update — snapshot for audit:

```sql
INSERT INTO users_logs
  (user_id, employee_id, role, status, employee_name, phonenumber,
   department, location, photo, password, action, created_at)
SELECT u.user_id, u.employee_id,
       (SELECT GROUP_CONCAT(r.role_name SEPARATOR ', ')
          FROM user_role ur JOIN roles r ON r.role_id = ur.role_id
         WHERE ur.user_id = u.user_id) AS role,
       ast.status_name, u.employee_name, u.phonenumber,
       dep.department_name, loc.location_name,
       u.photo, u.password, 'UPDATE', NOW()
FROM users u
LEFT JOIN account_status ast ON ast.status_id = u.status_id
LEFT JOIN departments dep ON dep.department_id = u.department_id
LEFT JOIN locations loc ON loc.location_id = u.location_id
WHERE u.user_id = ? LIMIT 1;
```

Then:

```sql
UPDATE users SET `status_id` = ?, `employee_name` = ? WHERE user_id = ?;

-- optional role replace:
DELETE FROM user_role WHERE user_id = ?;
INSERT INTO user_role (role_id, user_id) VALUES (?, ?);
```

### Delete user (transaction)

```sql
-- snapshot with action = 'DELETE' (same SELECT as above, then INSERT with 'DELETE')
DELETE FROM user_role WHERE user_id = ?;
DELETE FROM admin_level_user_multi_city_map WHERE user_id = ?;
DELETE FROM users WHERE user_id = ?;
```

### List roles (master table)

```sql
SELECT * FROM `roles`;
```

### User role assignments

```sql
SELECT * FROM `user_role` WHERE `user_id` = ?;

INSERT INTO `user_role` (`user_id`, `role_id`) VALUES (?, ?);

DELETE FROM `user_role` WHERE `user_role_id` = ?;
```
