Gecko [ v3.cesa.co.za ]

Name	Size	Permission	Date
echasl23-admin-login-design.md	1.89 KB	-rw-r--r--	2026-05-18 12:51
echasl23-admin-login-requirements.md	2.43 KB	-rw-r--r--	2026-05-18 12:51
mail-queue-monitor-design.md	2.64 KB	-rw-r--r--	2026-05-18 12:51
mail-queue-monitor-requirements.md	2.04 KB	-rw-r--r--	2026-05-18 12:51
member-portal-login-design.md	2.41 KB	-rw-r--r--	2026-05-20 09:05
member-portal-login-requirements.md	1.48 KB	-rw-r--r--	2026-05-20 09:05
member-transformation-declaration-design.md	2.84 KB	-rw-r--r--	2026-05-20 09:05
member-transformation-declaration-requirements.md	2.02 KB	-rw-r--r--	2026-05-20 09:05

Rename

# Mail queue monitor — design

**System:** cesa-v3  
**Implements:** [mail-queue-monitor-requirements.md](mail-queue-monitor-requirements.md)

## Architecture

```
Admin browser
    → MailQueueController (ROLE_ADMIN | ROLE_MAIL_QUEUE_ADMIN)
    → MailQueueService (business logic, aggregations)
    → Doctrine entity manager `mail` → MySQL mail DB (Messages, ToSend)
```

## Database connection

- New DBAL connection `mail` and ORM entity manager `mail`, configured via env:
  - `MAIL_DATABASE_HOST`, `MAIL_DATABASE_PORT`, `MAIL_DATABASE_USER`, `MAIL_DATABASE_PWD`, `MAIL_DATABASE_NAME`, `MAIL_DATABASE_SERVER_VERSION`
- Defaults mirror main DB host/port/version; database name follows cesa-main (`cesa_mail` local, `c37_cesa_mail` dev, `saacemail` production).

## Bundle and classes

| Component | Location |
|-----------|----------|
| Bundle | `App\MailQueueBundle` |
| Entities | `BulkMailMessage` → `Messages`, `BulkMailToSend` → `ToSend` |
| Service | `MailQueueBundle\Service\MailQueueService` |
| Controller | `MailQueueBundle\Controller\MailQueueController` |
| Views | `@MailQueueBundle/queue/list.html.twig`, `detail.html.twig` |

## Routes

| Route name | Path | Action |
|------------|------|--------|
| `mail_queue_list` | `/admin-tools/mail-queue.html` | List + summary |
| `mail_queue_detail` | `/admin-tools/mail-queue/{messageId}.html` | Campaign detail |

Templates extend `sonata_layout.html.twig` for consistent admin chrome.

## Queries

**List:** Messages ordered by `MessageID` DESC, with aggregated counts:

```sql
SELECT m, 
  SUM(CASE WHEN t.sent = 0 THEN 1 ELSE 0 END),
  SUM(CASE WHEN t.sent = 1 THEN 1 ELSE 0 END),
  SUM(CASE WHEN t.sent = 2 THEN 1 ELSE 0 END)
FROM BulkMailMessage m
LEFT JOIN BulkMailToSend t ON t.message = m
GROUP BY m.messageId
```

**Detail:** `BulkMailMessage` by ID; `BulkMailToSend` paginated (KnpPaginator, 50 per page).

**Summary:** Global `COUNT` on `ToSend` by `Sent`; count of `Messages` where `SendComplete = 0`.

## Status display

| `ToSend.Sent` | Label | Bootstrap label class |
|---------------|-------|------------------------|
| 0 | Pending | warning |
| 1 | Sent | success |
| 2 | Failed | danger |

## Security

```php
#[IsGranted(new Expression('is_granted("ROLE_ADMIN") or is_granted("ROLE_MAIL_QUEUE_ADMIN")'))]
```

Assign `MAIL_QUEUE_ADMIN` on a User Level for delegated access without full super-admin.

## Testing

- Unit tests for `MailQueueService::aggregateSendCounts()` and status label mapping (no DB).
- Manual: verify against local `cesa_mail` after queueing test mail.

## Dependencies

- Data model: cesa-mail `docs/specs/messages-tosend-data-model.md`
- Send worker: cesa-main `crons/ToSendProcess.php`