# OBN Carrier Group Update Tool

**Created:** 31 Jul 2026  
**Path:** `/obncarriergrouptool`  
**Menu:** Hamburger drawer → **OBN Carrier Group Tool**

---

## 1. Problem

When OBNs are added via file upload through `UserNumbers::insertOdn()`:

- Rows are written to `user_numbers` (or `numbershield_user_number`)
- Entity links are written to `obn_number_entities` (`entity` = `user_test_age.id`)
- **Carrier groups are skipped** when a file is uploaded (`empty($request->file('file'))` guard)

Without rows in `obn_number_entity_carrier_groups`, mapper API/batch cannot assign those OBNs for `entity_type = 3` (Mapper Assignment).

**Constraint:** Do **not** modify `insertOdn()` or `UpdateUserOBNDetails()`. Build a separate admin tool.

---

## 2. Data model

```
user_test_age (entity)
  entity_type: 1=Group By, 2=Test Cycle, 3=Mapper Assignment
       │
       ├── mapper_entity_settings   (carrier group definitions)
       │
       └── obn_number_entities      (number ↔ entity)
                │
                └── obn_number_entity_carrier_groups
                      (user_id, number, entity_id, carrier_group_id)
```

| Table | Role |
|-------|------|
| `user_numbers` / `numbershield_user_number` | OBN details (PBN, status, description) |
| `user_test_age` | Entities |
| `obn_number_entities` | Number ↔ entity link |
| `mapper_entity_settings` | Carrier group definitions per mapper entity |
| `obn_number_entity_carrier_groups` | Per-number carrier group picks |

---

## 3. Solution overview

Standalone tool (new controller + library + Blade). Reuses existing helpers where safe; no changes to insert/update OBN core paths.

```
Select User → Select Entity (load all numbers)
           OR paste numbers → load matching OBNs
           → edit table rows / Apply All
           → Save (batched DB writes)
```

---

## 4. Files

| File | Purpose |
|------|---------|
| `app/Http/Controllers/ObnCarrierGroupToolController.php` | Thin controller |
| `app/Libraries/ObnCarrierGroupTool/ObnCarrierGroupToolLib.php` | Queries + batched save |
| `resources/views/obncarriergrouptool/index.blade.php` | UI |
| `routes/web.php` | Routes under `auth` + `verified` (same pattern as `naapitool`) |
| `resources/views/layouts/app.blade.php` | Menu item + `$fselectRoutes` entry |

### Routes

| Method | URI | Name |
|--------|-----|------|
| GET | `obncarriergrouptool` | `obncarriergrouptool` |
| GET | `obncarriergrouptool/entities` | `obncarriergrouptool.entities` |
| GET | `obncarriergrouptool/numbers` | `obncarriergrouptool.numbers` |
| POST | `obncarriergrouptool/numbers-by-paste` | `obncarriergrouptool.numbersByPaste` |
| POST | `obncarriergrouptool/save` | `obncarriergrouptool.save` |

---

## 5. UI behavior (final)

### Toolbar

1. **User** fSelect — all `UserInfo` users  
2. **Entity** fSelect — all entity types for user (label includes type)  
3. **Paste numbers** textarea + Load Paste  

### Apply to all (6-column grid)

Checkboxes control which fields apply:

1. Entity  
2. Preferred Business Name  
3. Status  
4. Description  
5. Carrier Groups  
6. **Apply** button (same row, aligned with inputs)

### Numbers table

| Column | Control |
|--------|---------|
| Number | Readonly |
| Entity (max 6) | fSelect multiselect |
| Preferred Business Name | Text |
| Status | fSelect (0–5 + NumberShield statuses supported on save) |
| Description | Text |
| Carrier Groups | **One** fSelect: `optgroup` = entity name, options = group names; value = `{groupId}_{entityId}` |
| Remove | Compact × |

### UX details settled in conversation

- Carrier groups: **single** fSelect with entity optgroups (not one select per entity)  
- `numDisplayed`: Entity default 2; Carrier Groups **3**  
- Alerts auto-hide after ~4 seconds  
- Apply All fSelect dropdowns: `overflow: visible` + z-index so menus are not clipped  
- Equal-height inputs/selects (34px) in Apply All bar  

---

## 6. Save logic (batched for Cloudflare ~100s)

### Problem with first save

Per-number SELECT/UPDATE/DELETE/INSERT caused thousands of queries and risked timeout.

### Optimized approach

1. Prefetch existing rows from `user_numbers` **and** `numbershield_user_number` (chunked `WHERE IN`, string keys)  
2. Also accept ownership via `obn_number_entities` (so entity/CG-only updates work)  
3. Group identical PBN/status/description updates → few `UPDATE`s (Apply All often = 1)  
4. Bulk insert status history (only when status changed on `user_numbers`)  
5. Replace entities: chunked delete + bulk insert **only for numbers in the request**  
6. Replace carrier groups: chunked delete + bulk insert **only for numbers in the request**  
7. **Skip** `user_numbers` / numbershield UPDATE when detail fields are unchanged  

### Batch isolation bug (fixed)

When saving a second batch of ~12.5k numbers after a first batch, MySQL could coerce VARCHAR phone numbers to FLOAT inside `WHERE number IN (...)`, matching/deleting **unrelated** numbers from the first batch.

**Fix:** entity and carrier-group deletes use digit-only quoted string literals (`deleteRowsForNumbers`), scoped strictly to the numbers in the current request. Other numbers’ relations are left untouched.

### Ownership / “number not found” fix

- Frontend must send number via `$row.attr('data-number')` (not `.data('number')` — jQuery can coerce/mangle)  
- Backend treats a number as valid if it exists in any of:  
  - `user_numbers`  
  - `numbershield_user_number`  
  - `obn_number_entities` for that user  

Entity + carrier group updates still run even when detail tables are not updated.

### Save payload shape

```json
{
  "user_id": 123,
  "numbers": [{
    "number": "5551234567",
    "entities": [10, 20],
    "preferred_business_name": "...",
    "status": 1,
    "number_description": "...",
    "carrier_groups": { "10": [101, 102] }
  }]
}
```

---

## 7. Decisions locked during build

| Topic | Decision |
|-------|----------|
| Entity list | All types; carrier UI only for `entity_type = 3` |
| Max entities per number | 6 |
| Load by entity | All numbers linked to that entity |
| Paste | Parse newline/comma/tab; skip unknowns |
| Access | Auth + verified (no new permission); menu always visible when logged in |
| Core OBN insert/update | Unchanged |
| Zoho / plan / branding side effects | Out of scope |

---

## 8. How to use

1. Open **OBN Carrier Group Tool** from the hamburger menu  
2. Select user → entities load  
3. Select entity **or** paste numbers and Load Paste  
4. Optionally set Apply All fields (check boxes) → Apply  
5. Adjust individual rows if needed  
6. **Save Changes**  

---

## 9. Future (optional)

If saves grow to tens of thousands of OBNs in one request, add a queue job (same pattern as `BulkUpdateDid`) and return immediately with a progress/status message. Batched sync is expected to stay under Cloudflare’s ~100s for typical bulk sizes.

---

## 10. Out of scope

- Changing `UserNumbers::insertOdn()` file-upload carrier insert behavior  
- Changing `UpdateUserOBNDetails`  
- Zoho / branding / plan-change side effects on this tool’s save path