Performance: N+1 database query issue in InboxMessagePreview causing slow inbox/reply loading [USED AI]

[childking3]

Description
The Mail module suffers from a severe performance degradation (an N+1 query problem) when rendering the inbox message list. This causes significant latency when loading the inbox or triggering inbox updates (such as during the /mail/mail/reply or update-entries AJAX requests).

If a user has many messages in their inbox, the redundant database queries can add several seconds of dead waiting time to the request, which also causes secondary frontend issues like AJAX race conditions (duplicate or out-of-order messages).

Root Cause Analysis
When the inbox renders, views/inbox.php loops through UserMessage objects and passes them to the InboxMessagePreview widget. The UserMessage model already has the pinned status loaded in memory from the main optimized query (UserMessage::findByUser()).

However, in protected/modules/mail/widgets/InboxMessagePreview.php, the run() method does not pass the $userMessage object to the view context.

Consequently, in views/inboxMessagePreview.php, the view calls $message->getPinIcon(). This triggers the following chain:

1. Message::getPinIcon() calls $this->isPinned()
2. Message::isPinned() calls $this->getUserMessage($userId)
3. Message::getUserMessage() executes a brand new UserMessage::findOne(['user_id' => $userId, 'message_id' => $this->id]) database query.

If a user has 30 messages in their inbox, this results in 1 initial query + 30 redundant findOne() queries just to check if messages are pinned.

Steps to Reproduce

1. Have a user with multiple conversations in their inbox.
2. Enable the MariaDB slow query log (e.g., set long_query_time = 1).
3. Load the inbox or trigger an inbox update (e.g., sending a reply which updates the UI/inbox state).
4. Observe multiple SELECT * FROM user_message WHERE user_id=X AND message_id=Y queries executing sequentially in the slow log.

Proposed Fix
We need to pass the already-loaded $userMessage object into the view context to avoid the redundant database lookup.

File 1: protected/modules/mail/widgets/InboxMessagePreview.php**
Update the run() method to pass the userMessage object to the view:

public function run()
{
    if ($this->getLastEntry() === null) {
        return '';
    }

    return $this->render('inboxMessagePreview', [
        'message' => $this->userMessage->message,
        'userMessage' => $this->userMessage, // <-- Pass the loaded relation
        'messageTitle' => $this->getMessageTitle(),
        'messageText' => $this->getMessagePreview(),
        'messageTime' => $this->getMessageTime(),
        'lastParticipant' => $this->lastParticipant(),
        'options' => $this->getOptions(),
    ]);
}

File 2: protected/modules/mail/widgets/views/inboxMessagePreview.php**
Update the view to use the passed $userMessage->pinned state directly from memory instead of querying the DB via $message->getPinIcon():

<!-- Change this: -->
<!-- <?= Html::encode($messageTitle) . ' ' . $message->getPinIcon() ?> -->

<!-- To this: -->
<?= Html::encode($messageTitle) ?>
<?php if ($userMessage->pinned): ?>
    <?= \humhub\modules\ui\icon\widgets\Icon::get('map-pin')
        ->tooltip(Yii::t('MailModule.base', 'Pinned'))
        ->color('var(--bs-danger)') ?>
<?php endif; ?>

Impact
Fixing this N+1 query problem will drastically reduce the database load and response time for inbox rendering. By dropping dozens of unnecessary DB hits, the backend latency for requests like /mail/reply should drop significantly, naturally resolving the frontend AJAX race conditions caused by the 9+ second response times.

[yurabakhtin]

@luke- Done #516