# Officer User ID Normalization and Auto-Increment Update

## Summary

The officer user IDs were normalized into a continuous sequence beginning at 3, instead of leaving the database with scattered IDs such as 391, 392, 393, ..., 401, and 9496.

The final normalized values were:

- 391 -> 3
- 392 -> 4
- 393 -> 5
- 394 -> 6
- 395 -> 7
- 396 -> 8
- 399 -> 9
- 400 -> 10
- 401 -> 11
- 9496 -> 12

## Why this was needed

The original officer IDs were inconsistent and not in a clean range. This caused the `users` table to have a large gap and made the identifier sequence look chaotic. Normalizing them ensures the system starts from a clean base and keeps the next inserted user IDs predictable.

## Database update steps

### 1. Update related foreign keys

Before renumbering the primary user IDs, all tables that referenced `users.id` were updated so their foreign keys also pointed to the new IDs.

Affected references included:

- `users.assigned_officer_id`
- `loans.user_id`
- `transactions.user_id`
- `loan_requests.user_id`
- `loan_requests.approved_by`
- `login_activities.user_id`
- `guarantee_deposits.user_id`
- `audit_logs.admin_id`
- `suspense_transactions.user_id`

### 2. Renumber the records

The officer records were reassigned to a clean sequence starting at 3.

### 3. Set the next auto-increment value

After the renumbering, the next auto-increment value for the `users` table was updated to continue after the last used ID.

## MySQL statement to keep auto-increment aligned

```sql
SELECT @max_id := MAX(id) FROM users;
SET @sql = CONCAT('ALTER TABLE users AUTO_INCREMENT = ', @max_id + 1);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```

This ensures that the next inserted user gets an ID immediately after the highest current value.

## Result

The officer table now has the normalized IDs:

```text
3|JOSEPHINE|josephine@lendcore.com|officer
4|MOSES|moses@lendcore.com|officer
5|ALICE|alice@lendcore.com|officer
6|susan|susan@lendcore.com|officer
7|MONICAH|monicah@lendcore.com|officer
8|richard|richard@lendcore.com|officer
9|beth Wambui|bethwambui@lendcore.com|officer
10|john chege|johnchege@lendcore.com|officer
11|mercy|mercy@lendcore.com|officer
12|VERONICA|veronica@lendcore.com|officer
```

The next new user inserted will begin from ID 13 unless a separate explicit ID is supplied.
