<?php
/**
 * Created by PhpStorm.
 * User: enured
 * Date: 01/05/24
 * Time: 4:00 PM
 */

namespace IMATHUZH\Qfq\Core\Form;


use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\MessengerPublisher;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Report\Thumbnail;
use IMATHUZH\Qfq\Core\Store\Store;


/**
 * Class Chat
 * @package qfq
 */
class Chat {

    /**
     * Builds the nchan channel name for a chat room. Derived deterministically
     * from xId and cIdTopic; nchan's channel id regex disallows dots in the
     * room location pattern (see nchan-messages nginx snippets), so the
     * components are joined with underscores.
     */
    public static function buildChannel(array $chatConfig): string {
        $xId = $chatConfig[CHAT_COLUMN_XID] ?? 0;
        $cIdTopic = $chatConfig[CHAT_COLUMN_CID_TOPIC] ?? 0;
        return 'chat_' . $xId . '_' . $cIdTopic;
    }

    /**
     * Publishes a chat notification to nchan via MessengerPublisher (which
     * targets the auth-less messenger-local.conf endpoint). Silent no-op
     * upstream if SYSTEM_MESSENGER_PUBLISH_URL is empty, so chat persistence
     * keeps working in dev setups without an nchan server configured.
     *
     * Errors from the upstream publisher are intentionally ignored here —
     * the save operation has already committed to the database and should
     * not fail just because the live-notification bus is unreachable.
     *
     * @param array $chatConfig  normalized chat config (post createChatConfig)
     * @param string $name       nchan message name, e.g. 'ping' / 'toolbar-ping'
     * @param array $payload     JSON-serialisable payload delivered to subscribers
     */
    public static function publish(array $chatConfig, string $name, array $payload): void {
        $group = Store::getVar(SYSTEM_MESSENGER_GROUP, STORE_SYSTEM) ?: MESSENGER_DEFAULT_GROUP;
        $channel = self::buildChannel($chatConfig);
        MessengerPublisher::publish($group, [$channel], $name, $payload);
    }

    /** Create toolbar configuration for chat or thread
     *
     * @param array $chatConfig
     * @param int $messageId
     * @param Database $db
     * @param Store $store
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function createToolbarConfig(array $chatConfig, int $messageId, Database $db, Store $store, $chatRoom = false): array {
        $chatConfigJson = json_encode($chatConfig, JSON_UNESCAPED_SLASHES);
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES] ?? array();

        // Difference between chat room id and thread id
        $cIdThreadQuery = ' AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' != "0"';
        $cIdChatRoomSipParam = 'false';
        if ($chatRoom) {
            $cIdThreadQuery = ' AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' = "0"';
            $cIdChatRoomSipParam = 'true';
        }

        // Create sips for delete and add api urls
        $sip = $store->getSipInstance();
        $doneApiUrl = $sip->queryStringToSip(Path::urlApi(API_SAVE_PHP) . '?' . 'mode=chat_tag_done&cId=' . $messageId . '&chat_config=' . $chatConfigJson . '&chat_room=' . $cIdChatRoomSipParam, RETURN_URL);
        $tagDelApiUrl = $sip->queryStringToSip(Path::urlApi(API_SAVE_PHP) . '?' . 'mode=chat_tag_delete&cId=' . $messageId . '&chat_config=' . $chatConfigJson . '&chat_room=' . $cIdChatRoomSipParam, RETURN_URL);
        $tagAddApiUrl = $sip->queryStringToSip(Path::urlApi(API_SAVE_PHP) . '?' . 'mode=chat_tag_add&cId=' . $messageId . '&chat_config=' . $chatConfigJson . '&chat_room=' . $cIdChatRoomSipParam, RETURN_URL);

        // Prepare tag list
        $sqlTags = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                return $dbColumnNames[$column];
            }, [CHAT_COLUMN_ID, CHAT_COLUMN_MESSAGE, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_USERNAME]))
            . ' FROM ' . $chatConfig[CHAT_TABLE_NAME] . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ? AND '
            . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND !FIND_IN_SET(' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ', "' . CHAT_TYPE_TAG_DONE . '")' . $cIdThreadQuery;
        $sqlTagParams = [$messageId, CHAT_COLUMN_TYPE_TAG];
        $tagResult = $db->sql($sqlTags, ROW_REGULAR, $sqlTagParams);
        $tags = [];
        foreach ($tagResult as $row) {
            $tagEntry = [
                'id' => $row[$dbColumnNames[CHAT_COLUMN_ID]],
                'value' => $row[$dbColumnNames[CHAT_COLUMN_MESSAGE]],
                'pIdCreator' => $row[$dbColumnNames[CHAT_COLUMN_PID_CREATOR]],
                'username' => $row[$dbColumnNames[CHAT_COLUMN_USERNAME]]
            ];

            $tags[] = $tagEntry;
        }

        // Prepare done list
        $sqlDone = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                return $dbColumnNames[$column];
            }, [CHAT_COLUMN_ID, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_USERNAME]))
            . ' FROM ' . $chatConfig[CHAT_TABLE_NAME] . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ? AND '
            . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ?' . $cIdThreadQuery;
        $sqlDoneParams = [$messageId, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE];
        $doneResult = $db->sql($sqlDone, ROW_REGULAR, $sqlDoneParams);
        $done = [];
        $currentUserDone = false;
        foreach ($doneResult as $row) {
            if ($row[$dbColumnNames[CHAT_COLUMN_PID_CREATOR]] == $chatConfig[CHAT_COLUMN_PID_CREATOR]) {
                $currentUserDone = $row[$dbColumnNames[CHAT_COLUMN_ID]];
                continue;
            }
            $doneEntry = [
                'id' => $row[$dbColumnNames[CHAT_COLUMN_ID]],
                'pIdCreator' => $row[$dbColumnNames[CHAT_COLUMN_PID_CREATOR]],
                'username' => $row[$dbColumnNames[CHAT_COLUMN_USERNAME]]
            ];
            $done[] = $doneEntry;
        }

        $toolbarConfig = array(
            "messageId" => $messageId,
            "optionTagDone" => $chatConfig[CHAT_PARAMETER_OPTION_TAG_DONE],
            "optionTag" => $chatConfig[CHAT_PARAMETER_OPTION_TAG],
            "activeDone" => $currentUserDone,
            "doneApiUrl" => $doneApiUrl,
            "tagDelApiUrl" => $tagDelApiUrl,
            "tagAddApiUrl" => $tagAddApiUrl,
            "pIdCreator" => $chatConfig[CHAT_COLUMN_PID_CREATOR],
            "username" => $chatConfig[CHAT_COLUMN_USERNAME],
            "cleanedTags" => [],
            "tags" => $tags,
            "usersDone" => $done,
            "topBtn" => true
        );

        return $toolbarConfig;
    }

    /** Create chat configuration
     *
     * @param array $formElement
     * @param Store $store
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function createChatConfig(array $formElement): array {
        $chatConfig = array();

        // [apId:rId,pId:pIdCreator,grIdStatus:xGrIdStatus,grIdGroup:grIdGroupList,...]
        $chatConfig[CHAT_DB_COLUMN_NAMES] = array(
            'id' => 'id',
            'xId' => 'xId',
            'pIdCreator' => 'pIdCreator',
            'pIdTeam' => 'pIdTeam',
            'cIdTopic' => 'cIdTopic',
            'cIdThread' => 'cIdThread',
            'cIdTag' => 'cIdTag',
            'cIdLastRead' => 'cIdLastRead',
            'cIdMessage' => 'cIdMessage',
            'xGrIdStatus' => 'xGrIdStatus',
            'type' => 'type',
            'emoticon' => 'emoticon',
            'reference' => 'reference',
            'message' => 'message',
            'fileName' => 'fileName',
            'pathFileName' => 'pathFileName',
            'mimeType' => 'mimeType',
            'fileSize' => 'fileSize',
            'downloadUrl' => 'downloadUrl',
            'thumbnailUrl' => 'thumbnailUrl',
            'username' => 'username',
            'created' => 'created'
        );

        $chatConfig[CHAT_COLUMN_MAP] = $formElement[CHAT_COLUMN_MAP] ?? '[]';

        OnArray::mapColumns($chatConfig[CHAT_DB_COLUMN_NAMES], $chatConfig[CHAT_COLUMN_MAP]);
        $chatConfig[CHAT_TABLE_NAME] = $formElement[CHAT_TABLE_NAME] ?? 'Chat';

        $chatConfig[CHAT_COLUMN_XID] = $formElement[CHAT_PARAMETER_XID] ?? 0;
        $chatConfig[CHAT_COLUMN_PID_CREATOR] = empty($formElement[CHAT_PARAMETER_PID_CREATOR]) ? 0 : $formElement[CHAT_PARAMETER_PID_CREATOR];
        $chatConfig[CHAT_COLUMN_PID_TEAM] = $formElement[CHAT_PARAMETER_PID_TEAM] ?? 0;
        $chatConfig[CHAT_COLUMN_USERNAME] = empty($formElement[CHAT_PARAMETER_USERNAME]) ? 'Anonym' : $formElement[CHAT_PARAMETER_USERNAME];
        $chatConfig[CHAT_COLUMN_X_GRID_STATUS] = $formElement[CHAT_PARAMETER_X_GRID_STATUS] ?? 0;
        $chatConfig[CHAT_COLUMN_CID_TOPIC] = $formElement[CHAT_PARAMETER_CID_TOPIC] ?? 0;
        $chatConfig[CHAT_PARAMETER_THREAD] = $formElement[CHAT_PARAMETER_THREAD] ?? 0;
        $chatConfig[CHAT_PARAMETER_OPTION_TAG_DONE_RESET] = $formElement[CHAT_PARAMETER_OPTION_TAG_DONE_RESET] ?? 0;

        $chatConfig[CHAT_PARAMETER_OPTION_TAG_DONE] = $formElement[CHAT_PARAMETER_OPTION_TAG_DONE] ?? "off"; // off-my-all
        $chatConfig[CHAT_PARAMETER_OPTION_TAG] = $formElement[CHAT_PARAMETER_OPTION_TAG] ?? "off"; //off-my-all

        // Not implemented so far.
        $chatConfig[CHAT_PARAMETER_OPTION_REMINDER] = $formElement[CHAT_PARAMETER_OPTION_REMINDER] ?? 0;

        return $chatConfig;
    }

    /** Fetch from client requested messages and return them as a json.
     *
     * @param $chatConfig
     * @param Database $db
     * @param string $messageIdList
     * @param string $loadMode
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function createChat($chatConfig, Database $db, string $messageIdList = '', string $loadMode = CHAT_LOAD_MODE_REFRESH, Store $store = null): array {
        $json = array();
        $sqlLimit = 11;
        $toolbarJson = null;
        $firstMsgId = 0;

        // Define LIMIT 10 for lazy loading (refresh) or LIMIT 1 for client ping as new sender message
        if ($loadMode === CHAT_LOAD_MODE_PING) {
            $sqlLimit = 1;
        }

        $flagFirstMsg = $store::getVar(CHAT_CLIENT_FLAG_FIRST_MSG, STORE_CLIENT, SANITIZE_ALLOW_ALLBUT);
        $flagFirstMsg = $flagFirstMsg === "true";

        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES] ?? array();

        // Get last 10 messages from database which have cIdThread = 0.
        $sqlAllNoThread = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                return $dbColumnNames[$column];
            }, [CHAT_COLUMN_ID, CHAT_COLUMN_XID, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_CID_TOPIC, CHAT_COLUMN_TYPE, CHAT_COLUMN_MESSAGE, CHAT_COLUMN_FILE_NAME,
                CHAT_COLUMN_PATH_FILE_NAME, CHAT_COLUMN_MIME_TYPE, CHAT_COLUMN_FILE_SIZE, CHAT_COLUMN_USERNAME, CHAT_COLUMN_CID_THREAD, CHAT_COLUMN_CREATED]))
            . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
            . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_XID] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ' = ? AND !FIND_IN_SET(' . $dbColumnNames[CHAT_COLUMN_ID]
            . ', ?) AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' IN (?, ?, ?) AND IF("' . $loadMode . '" = "' . CHAT_LOAD_MODE_PING . '", 1 = 1, ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' = 0) '
            . 'ORDER BY ' . $dbColumnNames[CHAT_COLUMN_CREATED] . ' DESC LIMIT ' . $sqlLimit;

        $sqlAllNoThreadParams = [$chatConfig[CHAT_COLUMN_XID], $chatConfig[CHAT_COLUMN_CID_TOPIC], $messageIdList, CHAT_COLUMN_TYPE_MESSAGE, CHAT_COLUMN_TYPE_FILE, CHAT_COLUMN_TYPE_VIDEO_CALL];

        $resultAllNoThread = $db->sql($sqlAllNoThread, ROW_REGULAR, $sqlAllNoThreadParams);

        // Get moreResults flag for client and remove the last one
        $flagMoreRecords = count($resultAllNoThread) === 11;
        if ($flagMoreRecords) {
            array_pop($resultAllNoThread);
        }

        $chatJson = array();
        $resultThreadMessages = array();
        $resultRestMessages = array();

        // Messages
        if (!empty($resultAllNoThread) && $loadMode !== CHAT_LOAD_MODE_PING) {
            $columnId = array_column($resultAllNoThread, $dbColumnNames[CHAT_COLUMN_ID]);
            $threadIdList = implode(',', $columnId);

            // Get all messages with the given cIdThread from $resultAllNoThread
            $sqlThreadMessages = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                    return $dbColumnNames[$column];
                }, [CHAT_COLUMN_ID, CHAT_COLUMN_XID, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_CID_TOPIC, CHAT_COLUMN_TYPE, CHAT_COLUMN_MESSAGE, CHAT_COLUMN_FILE_NAME,
                    CHAT_COLUMN_PATH_FILE_NAME, CHAT_COLUMN_MIME_TYPE, CHAT_COLUMN_FILE_SIZE, CHAT_COLUMN_USERNAME, CHAT_COLUMN_CID_THREAD, CHAT_COLUMN_CREATED]))
                . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE FIND_IN_SET(' . $dbColumnNames[CHAT_COLUMN_CID_THREAD]
                . ', ?) AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' IN (?, ?, ?) '
                . 'ORDER BY ' . $dbColumnNames[CHAT_COLUMN_CREATED] . ' DESC ';

            $sqlThreadMessagesParams = [$threadIdList, CHAT_COLUMN_TYPE_MESSAGE, CHAT_COLUMN_TYPE_FILE, CHAT_COLUMN_TYPE_VIDEO_CALL];

            $resultThreadMessages = $db->sql($sqlThreadMessages, ROW_REGULAR, $sqlThreadMessagesParams);

            // Get the ids which weren't caught because the noThread message already exist on client side but not every message inside the thread.
            $allIds = $messageIdList . ',' . $threadIdList;

            $sqlRestMessages = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                    return $dbColumnNames[$column];
                }, [CHAT_COLUMN_ID, CHAT_COLUMN_XID, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_CID_TOPIC, CHAT_COLUMN_TYPE, CHAT_COLUMN_MESSAGE, CHAT_COLUMN_FILE_NAME,
                    CHAT_COLUMN_PATH_FILE_NAME, CHAT_COLUMN_MIME_TYPE, CHAT_COLUMN_FILE_SIZE, CHAT_COLUMN_USERNAME, CHAT_COLUMN_CID_THREAD, CHAT_COLUMN_CREATED]))
                . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE FIND_IN_SET(' . $dbColumnNames[CHAT_COLUMN_CID_THREAD]
                . ', ?) AND !FIND_IN_SET(' . $dbColumnNames[CHAT_COLUMN_ID] . ', ?) AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' IN (?, ?, ?) '
                . 'ORDER BY ' . $dbColumnNames[CHAT_COLUMN_CREATED] . ' DESC ';

            $sqlRestMessagesParams = [$messageIdList, $allIds, CHAT_COLUMN_TYPE_MESSAGE, CHAT_COLUMN_TYPE_FILE, CHAT_COLUMN_TYPE_VIDEO_CALL];
            $resultRestMessages = $db->sql($sqlRestMessages, ROW_REGULAR, $sqlRestMessagesParams);

        }

        // Get first messageId from chat room after first load
        if ($loadMode === CHAT_LOAD_MODE_FIRST || $loadMode === CHAT_LOAD_MODE_PING && $flagFirstMsg) {
            $sqlFirstMessage = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                    return $dbColumnNames[$column];
                }, [CHAT_COLUMN_ID]))
                . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_XID] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_TYPE] . ' IN (?, ?, ?) AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' = 0 '
                . 'ORDER BY ' . $dbColumnNames[CHAT_COLUMN_ID] . ' LIMIT 1';
            $sqlFirstMessageParam = [$chatConfig[CHAT_COLUMN_XID], $chatConfig[CHAT_COLUMN_CID_TOPIC], CHAT_COLUMN_TYPE_MESSAGE, CHAT_COLUMN_TYPE_FILE, CHAT_COLUMN_TYPE_VIDEO_CALL];
            $resultFirstMessage = $db->sql($sqlFirstMessage, ROW_REGULAR, $sqlFirstMessageParam);

            $firstMsgId = $resultFirstMessage[0][$dbColumnNames[CHAT_COLUMN_ID]] ?? null;

            if (!empty($resultFirstMessage)) {
                $toolbarConfig = self::createToolbarConfig($chatConfig, $resultFirstMessage[0][$dbColumnNames[CHAT_COLUMN_ID]], $db, $store, true);
                $toolbarJson = json_encode($toolbarConfig, JSON_UNESCAPED_SLASHES);
            }
        }

        $resultAll = array_merge($resultAllNoThread, $resultThreadMessages, $resultRestMessages);

        // Pre-compute SIP context for attachment download URLs (used inside the foreach below)
        $chatConfigJson = json_encode($chatConfig, JSON_UNESCAPED_SLASHES);
        $sip = $store->getSipInstance();

        if (!empty($resultAll)) {
            $recordId = array_column($resultAll, $dbColumnNames[CHAT_COLUMN_XID]);

            $recordId = max($recordId);
            $builtThreadIds = [];
            foreach ($resultAll as $message) {
                $threadId = $message[$dbColumnNames[CHAT_COLUMN_CID_THREAD]];
                $messageId = $message[$dbColumnNames[CHAT_COLUMN_ID]];
                $chatJson[$messageId] = $message;

                // Build SIP-protected download URL for attachment rows
                if ($message[$dbColumnNames[CHAT_COLUMN_TYPE]] === CHAT_COLUMN_TYPE_FILE) {
                    $downloadUrl = $sip->queryStringToSip(
                        Path::urlApi(API_DOWNLOAD_PHP) . '?'
                        . DOWNLOAD_MODE . '=' . DOWNLOAD_MODE_CHAT_ATTACHMENT
                        . '&' . CHAT_MESSAGE_ID . '=' . $messageId
                        . '&' . CHAT_CONFIG_SIP . '=' . $chatConfigJson,
                        RETURN_URL
                    );
                    $chatJson[$messageId][CHAT_DOWNLOAD_URL] = $downloadUrl;

                    $mimeType = $message[$dbColumnNames[CHAT_COLUMN_MIME_TYPE]] ?? '';
                    if ( str_starts_with($mimeType, 'image/')) {
                        try {
                            $pathFile = $message[$dbColumnNames[CHAT_COLUMN_PATH_FILE_NAME]];
                            $tokenStr = TOKEN_THUMBNAIL . ':' . $pathFile . '|' . TOKEN_THUMBNAIL_DIMENSION . ':300x'
                            . '|' . TOKEN_SIP . ':1' . '|' . TOKEN_RENDER . ':' . RENDER_MODE_7_ONLY_URL;
                            $thumbnail = new Thumbnail();
                            $chatJson[$messageId][CHAT_THUMBNAIL_URL] = $thumbnail->process($tokenStr);
                        } catch (\Exception $e) {
                                Logger::logMessage('Chat thumbnail creation failed: ' . $e->getMessage(), 'chat.log');
                        }
                    }
                }

                if ($threadId !== 0 && !in_array($threadId, $builtThreadIds)) {
                    // Get toolbar config from thread
                    $toolbarThreadConfig = self::createToolbarConfig($chatConfig, $threadId, $db, $store);
                    $chatJson[$messageId][CHAT_TOOLBAR_CONFIG] = json_encode($toolbarThreadConfig,
                        JSON_UNESCAPED_SLASHES);
                    $builtThreadIds[] = $threadId;
                }
            }
        }

        //Chat Reaction: Voting function
        $allVisibleIds = array_keys($chatJson);
        if (!empty($messageIdList)) {
            $allVisibleIds = array_merge($allVisibleIds, explode(',', $messageIdList));
        }
        $allVisibleIds = array_values(array_unique(array_filter($allVisibleIds)));

        if (!empty($allVisibleIds)) {
            $visibleIdsCsv = implode(',', $allVisibleIds);

            $sqlReactions = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                    return $dbColumnNames[$column];
                }, [CHAT_COLUMN_CID_MESSAGE, CHAT_COLUMN_MESSAGE, CHAT_COLUMN_USERNAME, CHAT_COLUMN_PID_CREATOR]))
                . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND FIND_IN_SET('
                . $dbColumnNames[CHAT_COLUMN_CID_MESSAGE] . ', ?)';
            $sqlReactionsParams = [CHAT_COLUMN_TYPE_REACTION, $visibleIdsCsv];
            $resultReactions = $db->sql($sqlReactions, ROW_REGULAR, $sqlReactionsParams);

            $reactionsMap = [];
            foreach ($resultReactions as $reaction) {
                $parentId = $reaction[$dbColumnNames[CHAT_COLUMN_CID_MESSAGE]];
                $reactionsMap[$parentId][] = $reaction;
            }
            $json[CHAT_JSON_REACTIONS] = $reactionsMap;
        }

        $json[CHAT_COLUMN_XID] = $recordId ?? 0;
        $json[FE_TYPE_CHAT] = $chatJson;
        $json[CHAT_COLUMN_PID_CREATOR] = $chatConfig[CHAT_COLUMN_PID_CREATOR];
        $json[CHAT_DB_COLUMN_NAMES] = $dbColumnNames;
        $json[CHAT_FLAG_MORE_RECORDS] = $loadMode === CHAT_LOAD_MODE_PING ? null : $flagMoreRecords;
        $json[CHAT_PARAMETER_THREAD] = $chatConfig[CHAT_PARAMETER_THREAD] == 1;
        $json[CHAT_TOOLBAR_CONFIG] = $toolbarJson;
        $json[CHAT_PARAMETER_OPTION_TAG_DONE_RESET] = $chatConfig[CHAT_PARAMETER_OPTION_TAG_DONE_RESET] == 1;
        $json[CHAT_FIRST_MSG_ID] = $firstMsgId;

        // Currently not finalized.
        $json[CHAT_ACTIVE_THREAD] = false;

        return $json;
    }

    /** Save new message sent from client with given chat configuration
     *
     * @param $chatConfig
     * @param $feMode
     * @param Store $store
     * @param Database $db
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function saveChatMessage($chatConfig, $feMode, Store $store, Database $db): array {
        $json = array();
        $chatJson = array();
        $toolbarJson = null;
        $chatRoom = false;

        if ($feMode[API_CHAT_UPDATE_DISABLED]) {
            return $json;
        }

        // Get current value from client store
        $value = $store::getVar(CHAT_CLIENT_MESSAGE, STORE_CLIENT, SANITIZE_ALLOW_ALL);
        $value = self::normalizeHtmlEncode($value);
        $flagFirstMsg = $store::getVar(CHAT_CLIENT_FLAG_FIRST_MSG, STORE_CLIENT, SANITIZE_ALLOW_ALLBUT);
        $resetDone = $store::getVar(CHAT_CLIENT_RESET_DONE, STORE_SIP, SANITIZE_ALLOW_ALLBUT);
        $flagFirstMsg = $flagFirstMsg === "true";
        $resetDone = $resetDone === "true";

        // Get threadId if given from client
        $threadId = $store::getVar(CHAT_CLIENT_THREAD_ID, STORE_CLIENT) ?? 0;
        $chatRoomMsgId = $store::getVar('chatRoomMsgId', STORE_CLIENT, SANITIZE_ALLOW_ALNUMX) ?? 0;

        $chatConfig = Chat::createChatConfig($chatConfig);
        $chatParams = [
            CHAT_COLUMN_XID => $chatConfig[CHAT_COLUMN_XID] ?? 0,
            CHAT_COLUMN_PID_CREATOR => $chatConfig[CHAT_COLUMN_PID_CREATOR] ?? 0,
            CHAT_COLUMN_CID_TOPIC => $chatConfig[CHAT_COLUMN_CID_TOPIC] ?? 0,
            CHAT_COLUMN_USERNAME => $chatConfig[CHAT_COLUMN_USERNAME] ?? 0,
            CHAT_COLUMN_X_GRID_STATUS => $chatConfig[CHAT_COLUMN_X_GRID_STATUS] ?? 0
        ];

        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];

        // Insert new record for chat
        $sqlInsert = 'INSERT INTO ' . $chatConfig[CHAT_TABLE_NAME] . '(' . $dbColumnNames[CHAT_COLUMN_XID] . ',' . $dbColumnNames[CHAT_COLUMN_PID_CREATOR]
            . ',' . $dbColumnNames[CHAT_COLUMN_X_GRID_STATUS] . ',' . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ','
            . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ',' . $dbColumnNames[CHAT_COLUMN_USERNAME] . ',' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ')'
            . ' VALUES (?,?,?,?,?,?,?)';

        $insertParams = [$chatParams[CHAT_COLUMN_XID], $chatParams[CHAT_COLUMN_PID_CREATOR], $chatParams[CHAT_COLUMN_X_GRID_STATUS], $chatParams[CHAT_COLUMN_CID_TOPIC], $value, $chatParams[CHAT_COLUMN_USERNAME], $threadId];
        $id = $db->sql($sqlInsert, ROW_REGULAR, $insertParams);

        // Get inserted record data for chat json
        $sql = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                return $dbColumnNames[$column];
            }, [CHAT_COLUMN_ID, CHAT_COLUMN_XID, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_CID_TOPIC, CHAT_COLUMN_MESSAGE, CHAT_COLUMN_USERNAME, CHAT_COLUMN_CREATED, CHAT_COLUMN_CID_THREAD]))
            . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
            . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_ID] . ' = ?';
        $sqlParams = [$id];

        $resultAll = $db->sql($sql, ROW_REGULAR, $sqlParams);

        if ($flagFirstMsg) {
            $messageId = $id;
            $chatRoom = true;
            if ($threadId !== 0 && $threadId) {
                $messageId = $threadId;
                $chatRoom = false;
            }
            $toolbarConfig = self::createToolbarConfig($chatConfig, $messageId, $db, $store, $chatRoom);
            $toolbarJson = json_encode($toolbarConfig, JSON_UNESCAPED_SLASHES);
        }

        if (!empty($resultAll)) {
            foreach ($resultAll as $message) {
                $chatJson[$message[$dbColumnNames[CHAT_COLUMN_ID]]] = $message;
                $chatJson[$message[$dbColumnNames[CHAT_COLUMN_ID]]][CHAT_TOOLBAR_CONFIG] = $toolbarJson;
            }
        }

        // Check if resetDone is set
        if ($resetDone) {
            $cId = $threadId;
            if (!$cId) {
                $cId = $chatRoomMsgId;
                $chatRoom = true;
            }
            if ($cId !== 0) {
                self::setDone($chatConfig, $db, $store, CHAT_DONE_RESET, $cId, $chatRoom);
            }
        }

        $json[CHAT_COLUMN_XID] = $recordId ?? 0;
        $json[FE_TYPE_CHAT] = $chatJson;
        $json[CHAT_COLUMN_PID_CREATOR] = $chatConfig[CHAT_COLUMN_PID_CREATOR];
        $json[CHAT_DB_COLUMN_NAMES] = $chatConfig[CHAT_DB_COLUMN_NAMES];
        $json[CHAT_PARAMETER_THREAD] = $chatConfig[CHAT_PARAMETER_THREAD] == 1;
        $json[CHAT_PARAMETER_OPTION_TAG_DONE_RESET] = $chatConfig[CHAT_PARAMETER_THREAD] == 1;
        $json[CHAT_FLAG_MORE_RECORDS] = null;
        $json[CHAT_MESSAGE_ID] = $id;
        $json[CHAT_ACTIVE_THREAD] = true;

        // Notify live subscribers via nchan. Published from the server so a
        // listener-only browser token is sufficient and the sequence stays
        // deterministic: persist → publish → respond to the originating XHR.
        self::publish($chatConfig, 'ping', [
            'cId' => $id,
            'chatRoom' => $chatRoom,
        ]);

        return $json;
    }

    /**
     *  Normalizes a chat message via decode-then-encode (input-encoding): the
     *  stored value is always HTML-encoded exactly once, independent of the
     *  client. Idempotent, so it cannot double-encode.
     * @param string|null $value
     * @return string
     */
    public static function normalizeHtmlEncode(?string $value): string {
        if ($value === null || $value === '') {
            return '';
        }

        // Canonicalize: undo any client-side encoding, then encode exactly once.
        // Guarantees the stored value is always encoded, regardless of the client.
        $plain = htmlspecialchars_decode($value, ENT_QUOTES);
        return htmlspecialchars($plain, ENT_QUOTES, 'UTF-8');
    }

    /** Save or delete chat done flags.
     *
     * @param $chatConfig
     * @param Database $db
     * @param Store $store
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function setDone($chatConfig, Database $db, Store $store, $mode = CHAT_DONE_REGULAR, $cId = null, $chatRoom = false): array {
        $json = [];
        $tagDoneInserted = false;
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];
        $chatRoomSipBool = false;

        if ($mode === CHAT_DONE_REGULAR) {
            $tagId = $store::getVar(CHAT_TAG_VALUE, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
            $cId = $store::getVar(CHAT_CID, STORE_SIP . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
            $chatRoomSipBool = $store::getVar(CHAT_CLIENT_ROOM_FLAG, STORE_SIP . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
            $chatRoomSipBool = $chatRoomSipBool === 'true';
        }

        if (empty($cId)) {
            $cId = $store::getVar(CHAT_CID, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_ALL);
        }

        if (empty($cId)) {
            $json[CHAT_JSON_RESULT] = 'noCiD';
            return $json;
        }

        $cIdThreadInsert = $cId;
        $cIdThreadQuery = ' AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' != "0"';
        if ($chatRoom || $chatRoomSipBool) {
            $cIdThreadQuery = ' AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' = "0"';
            $cIdThreadInsert = 0;
            $chatRoom = true;
        }

        if ($mode === CHAT_DONE_RESET) {
            $sqlDelete = 'DELETE FROM ' . $chatConfig[CHAT_TABLE_NAME] . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ?' . $cIdThreadQuery;
            $sqlDeleteParam = [$cId, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE];
            $db->sql($sqlDelete, ROW_REGULAR, $sqlDeleteParam);
        } else {
            // Get current done flag from current user
            $sqlDoneSelect = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                    return $dbColumnNames[$column];
                }, [CHAT_COLUMN_ID, CHAT_COLUMN_PID_CREATOR, CHAT_COLUMN_USERNAME]))

                . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ' = ? '
                . ' AND ' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ?' . $cIdThreadQuery;
            $sqlDoneParam = [$cId, $chatConfig[CHAT_COLUMN_PID_CREATOR], CHAT_TYPE_TAG_DONE, CHAT_COLUMN_TYPE_TAG];

            $sqlDoneResult = $db->sql($sqlDoneSelect, ROW_REGULAR, $sqlDoneParam);

            if ($tagId === 'false' && empty($sqlDoneResult)) {
                // Set done flag
                $sqlDoneInsert = 'INSERT INTO ' . $chatConfig[CHAT_TABLE_NAME] . ' (' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ','
                    . $dbColumnNames[CHAT_COLUMN_TYPE] . ',' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ',' . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ','
                    . $dbColumnNames[CHAT_COLUMN_USERNAME] . ',' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ') '
                    . 'VALUES (?,?,?,?,?,?)';

                $sqlDoneInsertParam = [$cId, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE, $chatConfig[CHAT_COLUMN_PID_CREATOR], $chatConfig[CHAT_COLUMN_USERNAME], $cIdThreadInsert];

                $doneResult = $db->sql($sqlDoneInsert, ROW_REGULAR, $sqlDoneInsertParam);

                $tagDoneInserted = true;
            } else {
                $sqlDelete = 'DELETE FROM ' . $chatConfig[CHAT_TABLE_NAME] . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ? AND '
                    . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ? AND '
                    . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ' = ?' . $cIdThreadQuery;
                $sqlDeleteParam = [$cId, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE, $chatConfig[CHAT_COLUMN_PID_CREATOR]];
                $db->sql($sqlDelete, ROW_REGULAR, $sqlDeleteParam);
            }
        }


        if ($tagDoneInserted) {
            $json[CHAT_JSON_RESULT] = $doneResult;
        } else {
            $json[CHAT_JSON_RESULT] = 'deleted';
        }
        $json[CHAT_ROOM_FLAG] = $chatRoom;

        self::publish($chatConfig, 'toolbar-ping', [
            'cId' => $cId,
            'chatRoom' => $chatRoom,
        ]);

        return $json;
    }

    /** Save or delete custom tags.
     *
     * @param $chatConfig
     * @param Database $db
     * @param Store $store
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function setTag($chatConfig, Database $db, Store $store, $mode = CHAT_MODE_TAG_ADD): array {
        $json = [];
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];
        $chatRoom = false;

        $tagValue = $store::getVar(CHAT_TAG_VALUE, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
        $cId = $store::getVar(CHAT_CID, STORE_SIP . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
        $chatRoomSipBool = $store::getVar(CHAT_CLIENT_ROOM_FLAG, STORE_SIP . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
        $chatRoomSipBool = $chatRoomSipBool === 'true';

        // In case of the very first chat room message
        if (empty($cId)) {
            $cId = $store::getVar(CHAT_CID, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX);
            $chatRoom = true;
        }

        if (empty($cId) || empty($tagValue)) {
            $json[CHAT_JSON_RESULT] = 'noCiD or tag value';
            return $json;
        }

        // Handle difference chat room and thread
        $cIdThreadInsert = $cId;
        $cIdThreadQuery = ' AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' != "0"';
        if ($chatRoomSipBool || $chatRoom) {
            $cIdThreadQuery = ' AND ' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ' = "0"';
            $cIdThreadInsert = 0;
            $chatRoom = true;
        }


        if ($mode === CHAT_MODE_TAG_DELETE) {
            $sqlDelete = 'DELETE FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_ID] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' != ? AND '
                . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ?' . $cIdThreadQuery;
            $sqlDeleteParam = [$tagValue, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE, $chatConfig[CHAT_COLUMN_PID_CREATOR], $cId];

            $db->sql($sqlDelete, ROW_REGULAR, $sqlDeleteParam);

            $json[CHAT_TOOLBAR_CONFIG] = self::createToolbarConfig($chatConfig, $cId, $db, $store, $chatRoom);
        } else {
            if (is_numeric($tagValue)) {
                // Get current tag value over tag id
                $sqlTagSelect = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                        return $dbColumnNames[$column];
                    }, [CHAT_COLUMN_MESSAGE])) . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                    . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_ID] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND '
                    . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' != ? ' . ' LIMIT 1';
                $sqlTagSelectParam = [$tagValue, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE];

                $sqlTagResult = $db->sql($sqlTagSelect, ROW_REGULAR, $sqlTagSelectParam);

                $tagValue = $sqlTagResult[0][$dbColumnNames[CHAT_COLUMN_MESSAGE]];
            }

            // Check if tag already exists for configured user
            $sqlCheck = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                    return $dbColumnNames[$column];
                }, [CHAT_COLUMN_ID])) . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ? AND ' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ' = ? ' . $cIdThreadQuery . ' LIMIT 1';
            $sqlCheckParam = [$chatConfig[CHAT_COLUMN_PID_CREATOR], CHAT_COLUMN_TYPE_TAG, $tagValue, $cId];
            $sqlCheckResult = $db->sql($sqlCheck, ROW_REGULAR, $sqlCheckParam);

            // Set tag if not already set
            if (empty($sqlCheckResult)) {
                $sqlTagInsert = 'INSERT INTO ' . $chatConfig[CHAT_TABLE_NAME] . ' (' . $dbColumnNames[CHAT_COLUMN_CID_TAG] . ','
                    . $dbColumnNames[CHAT_COLUMN_TYPE] . ',' . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ',' . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ','
                    . $dbColumnNames[CHAT_COLUMN_USERNAME] . ',' . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ') '
                    . 'VALUES (?,?,?,?,?,?)';

                $sqlTagInsertParam = [$cId, CHAT_COLUMN_TYPE_TAG, $tagValue, $chatConfig[CHAT_COLUMN_PID_CREATOR], $chatConfig[CHAT_COLUMN_USERNAME], $cIdThreadInsert];

                $db->sql($sqlTagInsert, ROW_REGULAR, $sqlTagInsertParam);

                $json[CHAT_TOOLBAR_CONFIG] = self::createToolbarConfig($chatConfig, $cId, $db, $store, $chatRoom);
            }
        }

        if (isset($json[CHAT_TOOLBAR_CONFIG])) {
            $json[CHAT_ROOM_FLAG] = $chatRoom;
        }

        self::publish($chatConfig, 'toolbar-ping', [
            'cId' => $cId,
            'chatRoom' => $chatRoom,
        ]);

        return $json;
    }

    /**
     * Guard against cross-topic IDOR on the shared Chat table:
     * verify a client-supplied message id belongs to the caller's topic
     * (xId + cIdTopic come from the signed SIP config, not the client).
     * @throws \UserFormException
     */
    public static function assertMessageInTopic($messageId, array $chatConfig, Database $db): void {
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];
        $sql = 'SELECT ' . $dbColumnNames[CHAT_COLUMN_ID] . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
            . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_ID] . ' = ? AND '
            . $dbColumnNames[CHAT_COLUMN_XID] . ' = ? AND '
            . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ' = ? LIMIT 1';
        $params = [$messageId, $chatConfig[CHAT_COLUMN_XID], $chatConfig[CHAT_COLUMN_CID_TOPIC]];
        if (empty($db->sql($sql, ROW_REGULAR, $params))) {
            if (!defined('PHPUNIT_QFQ')) {
                Logger::logMessage(Logger::linePre() . "Security: cross-topic message access blocked [user: {$chatConfig[CHAT_COLUMN_USERNAME]}, messageId: {$messageId}, xId: {$chatConfig[CHAT_COLUMN_XID]}, cIdTopic: {$chatConfig[CHAT_COLUMN_CID_TOPIC]}]", Path::absoluteQfqLogFile());
            }
            throw new \UserFormException('Invalid chat message reference', ERROR_BROKEN_PARAMETER);
        }
    }

    /**
     * Add / Delete Reactions
     *
     * @param $chatConfig
     * @param Database $db
     * @param Store $store
     * @param $mode
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function setReaction($chatConfig, Database $db, Store $store, $mode = CHAT_MODE_REACTION_ADD): array {
        $json = [];
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];

        $reactionCode = $store::getVar(CHAT_REACTION_CODE, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_ALL);
        $cIdMessage = $store::getVar(CHAT_CID, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_DIGIT);

        //Input Validation - reaction has a separate REGEX pattern for validation
        if (empty($cIdMessage) || empty($reactionCode)) {
            throw new \UserFormException('Missing reaction parameters', ERROR_MISSING_REQUIRED_PARAMETER);
        }
        if (!preg_match(CHAT_REACTION_CODE_REGEX, $reactionCode)) {
            if (!defined('PHPUNIT_QFQ')) {
                Logger::logMessage(Logger::linePre() . "Security: invalid reactionCode format [user: {$chatConfig[CHAT_COLUMN_USERNAME]}, cIdMessage: {$cIdMessage}, payload: " . substr($reactionCode, 0, 64) . "]",
                    Path::absoluteQfqLogFile()
                );
            }
            throw new \UserFormException('Invalid reaction code', ERROR_BROKEN_PARAMETER);
        }

        //Cross-topic IDOR guard: the parent message must belong to the caller's topic
        self::assertMessageInTopic($cIdMessage, $chatConfig, $db);

        //ADD path Reaction
        if ($mode === CHAT_MODE_REACTION_ADD) {
            $sqlCheck = 'SELECT ' . implode(', ', array_map(function ($column) use ($dbColumnNames) {
                return $dbColumnNames[$column];
                }, [CHAT_COLUMN_ID])) . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_CID_MESSAGE] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ? LIMIT 1';
            $sqlCheckParam = [CHAT_COLUMN_TYPE_REACTION, $cIdMessage, $chatConfig[CHAT_COLUMN_PID_CREATOR], $reactionCode];
            $sqlCheckResult = $db->sql($sqlCheck, ROW_REGULAR, $sqlCheckParam);

            if (empty($sqlCheckResult)) {
                $sqlInsert = 'INSERT INTO ' . $chatConfig[CHAT_TABLE_NAME] . '('
                    . $dbColumnNames[CHAT_COLUMN_CID_MESSAGE] . ','
                    . $dbColumnNames[CHAT_COLUMN_TYPE] . ','
                    . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ','
                    . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ','
                    . $dbColumnNames[CHAT_COLUMN_USERNAME] . ') '
                    . 'VALUES (?,?,?,?,?)';
                $sqlInsertParam = [$cIdMessage, CHAT_COLUMN_TYPE_REACTION, $reactionCode, $chatConfig[CHAT_COLUMN_PID_CREATOR], $chatConfig[CHAT_COLUMN_USERNAME]];

                $db->sql($sqlInsert, ROW_REGULAR, $sqlInsertParam);
            }
        }

        //Remove path Reaction
        if ($mode === CHAT_MODE_REACTION_DELETE) {
            $sqlDelete = 'DELETE FROM ' . $chatConfig[CHAT_TABLE_NAME]
                . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_CID_MESSAGE] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ' = ? AND '
                . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ' = ?';
            $sqlDeleteParam = [CHAT_COLUMN_TYPE_REACTION, $cIdMessage, $chatConfig[CHAT_COLUMN_PID_CREATOR], $reactionCode];
            $db->sql($sqlDelete, ROW_REGULAR, $sqlDeleteParam);
        }

        self::publish($chatConfig, 'reaction-ping', ['cIdMessage' => $cIdMessage]);

        return $json;

    }

    /**
     * Generate Jitsi-Base-URL for the video-call.
     * Base-URL is saved in the Chat-Table.
     * To join the Jitsi room directly with username, add the Parameter on client side in the URL
     *
     * @param array $chatConfig
     * @param Database $db
     * @param Store $store
     * @return string[]
     * @throws \CodeException
     * @throws \DbException
     * @throws \Random\RandomException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function startVideoCall(array $chatConfig, Database $db, Store $store): array {
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];

        //Validate config, Jitsi base-url must be set
        $jitsiBaseUrl = $store::getVar(SYSTEM_JITSI_BASE_URL, STORE_SYSTEM);
        if (empty($jitsiBaseUrl)) {
            throw new \CodeException(json_encode([ERROR_MESSAGE_TO_USER => 'Video-Call is not configured. Please call the administrator.',
                ERROR_MESSAGE_TO_DEVELOPER => 'SYSTEM_JITSI_BASE_URL is empty. Set it in qfq.json or in the TYPO3 extension configuration.',
                ERROR_MESSAGE_HTTP_STATUS  => '500',ERROR_MESSAGE_LOG_ERROR    => true,]));
        }
        //Validate topic context (SIP validation)
        $cIdTopic = $chatConfig[CHAT_COLUMN_CID_TOPIC] ?? 0;
        if ((int)$cIdTopic <= 0) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => 'Video-Call cant be started, because there are no valid Chat access.',
                ERROR_MESSAGE_TO_DEVELOPER => 'Missing or invalid cIdTopic in chatConfig',
                ERROR_MESSAGE_HTTP_STATUS  => '400', ERROR_MESSAGE_LOG_ERROR    => true,]));
        }
        //Generate cryptographically unique room name
        $roomName = 'qfq-chat-' . bin2hex(random_bytes(16));
        //Persist invitation row (type='videoCall', message=roomName — mirrors reaction pattern)
        $sqlInsert = 'INSERT INTO ' . $chatConfig[CHAT_TABLE_NAME] . '('
            . $dbColumnNames[CHAT_COLUMN_XID] . ','
            . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ','
            . $dbColumnNames[CHAT_COLUMN_X_GRID_STATUS] . ','
            . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ','
            . $dbColumnNames[CHAT_COLUMN_TYPE] . ','
            . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ','
            . $dbColumnNames[CHAT_COLUMN_USERNAME] . ') '
            . 'VALUES (?,?,?,?,?,?,?)';

        $sqlInsertParam = [
            $chatConfig[CHAT_COLUMN_XID] ?? 0,
            $chatConfig[CHAT_COLUMN_PID_CREATOR] ?? 0,
            $chatConfig[CHAT_COLUMN_X_GRID_STATUS] ?? 0,
            $cIdTopic,
            CHAT_COLUMN_TYPE_VIDEO_CALL,
            $roomName,
            $chatConfig[CHAT_COLUMN_USERNAME] ?? '',
        ];
        $db->sql($sqlInsert, ROW_REGULAR, $sqlInsertParam);

        //Broadcast to topic participants via nchan
        try {
            self::publish($chatConfig, 'ping', []);
        } catch (\Throwable $e) {
            Logger::logMessage(Logger::linePre() . 'Video-Call nchan publish failed: ' . $e->getMessage(), Path::absoluteQfqLogFile());
        }
        return ['roomName' => $roomName];

    }

    /**
     * @param $chatConfig
     * @param $db
     * @param $store
     * @return array
     * @throws \CodeException
     * @throws \UserFormException
     */
    public static function saveAttachment($chatConfig, $db, $store) {
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES];
        $xId = $chatConfig[CHAT_COLUMN_XID] ?? 0;
        $cIdTopic = $chatConfig[CHAT_COLUMN_CID_TOPIC] ?? 0;
        $pIdCreator = $chatConfig[CHAT_COLUMN_PID_CREATOR] ?? 0;
        $username = $chatConfig[CHAT_COLUMN_USERNAME] ?? 'Anonym';
        $cIdThread = isset($_POST['cIdThread']) ? (int)$_POST['cIdThread'] : 0;
        $caption = isset($_POST['caption']) ? self::normalizeHtmlEncode($_POST['caption']) : '';

        if (empty($_FILES) || !isset($_FILES['file'])) {
            throw new \UserFormException('No file uploaded', ERROR_UPLOAD_FILES_BROKEN);
        }
        $upload = $_FILES['file'];

        // Map PHP-native upload error codes to user-friendly messages
        $uploadErrMsg = [
            UPLOAD_ERR_INI_SIZE   => 'File exceeds server upload limit',
            UPLOAD_ERR_FORM_SIZE  => 'File exceeds form upload limit',
            UPLOAD_ERR_PARTIAL    => 'File was only partially uploaded',
            UPLOAD_ERR_NO_FILE    => 'No file was uploaded',
            UPLOAD_ERR_NO_TMP_DIR => 'Missing temporary folder on server',
            UPLOAD_ERR_CANT_WRITE => 'Failed to write file to disk',
            UPLOAD_ERR_EXTENSION  => 'Upload stopped by PHP extension',
        ];

        //Validate Chat Attachment
        if ($upload['error'] !== UPLOAD_ERR_OK) {
            $msg = $uploadErrMsg[$upload['error']] ?? 'Unknown upload error';
            throw new \UserFormException($msg, ERROR_UPLOAD);
        }
        if ($upload['size'] > CHAT_ATTACHMENT_MAX_SIZE) {
            $maxMb = CHAT_ATTACHMENT_MAX_SIZE / 1024 / 1024;
            throw new \UserFormException('File too big (' . round($upload['size'] / 1024 / 1024, 1) . ' MB). Max allowed size is: ' . $maxMb . ' MB', ERROR_UPLOAD_TOO_BIG);
        }
        if (!HelperFile::checkFileType($upload['tmp_name'], $upload['name'], CHAT_ATTACHMENT_MIME_WHITELIST)) {
            throw new \UserFormException('Filetype not allowed. Allowed types: ' . CHAT_ATTACHMENT_MIME_WHITELIST, ERROR_UPLOAD_FILE_TYPE);
        }

        //Defense in depth: also enforce a server-side extension allow-list — the MIME check alone lets a polyglot keep a .php name
        $extension = strtolower(pathinfo($upload['name'], PATHINFO_EXTENSION));
        if (!in_array($extension, explode(',', CHAT_ATTACHMENT_EXTENSION_WHITELIST), true)) {
            throw new \UserFormException('Filetype not allowed. Allowed types: ' . CHAT_ATTACHMENT_EXTENSION_WHITELIST, ERROR_UPLOAD_FILE_TYPE);
        }

        //Capture the server-detected MIME type (strip the '; charset=…' suffix) — the client-sent type is spoofable
        $mimeType = explode(';', HelperFile::getMimeType($upload['tmp_name']), 2)[0];

        //Sanitize Filename and defense in depth
        $originalName = Sanitize::safeFilename($upload['name']);

        //Build target directory
        $relativeDir = 'fileadmin/protected/chat/' . date('Y') . '/' . $xId . '-' . $cIdTopic;
        $absoluteDir = Path::absoluteApp($relativeDir);
        $uniqueFileName = HelperFile::getUniqueFileName($absoluteDir, $originalName);
        $absoluteTarget = $absoluteDir . '/' . $uniqueFileName;
        $relativeTarget = $relativeDir . '/' . $uniqueFileName;
        HelperFile::mkDirParent($absoluteTarget);

        if (!move_uploaded_file($upload['tmp_name'], $absoluteTarget)) {
            throw new \CodeException('Failed to move uploaded file to destination', ERROR_UPLOAD);
        }

        //DB Statement
        $sqlInsert = 'INSERT INTO ' . $chatConfig[CHAT_TABLE_NAME] . ' ('
            . $dbColumnNames[CHAT_COLUMN_XID] . ','
            . $dbColumnNames[CHAT_COLUMN_PID_CREATOR] . ','
            . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ','
            . $dbColumnNames[CHAT_COLUMN_CID_THREAD] . ','
            . $dbColumnNames[CHAT_COLUMN_USERNAME] . ','
            . $dbColumnNames[CHAT_COLUMN_TYPE] . ','
            . $dbColumnNames[CHAT_COLUMN_MESSAGE] . ','
            . $dbColumnNames[CHAT_COLUMN_FILE_NAME] . ','
            . $dbColumnNames[CHAT_COLUMN_PATH_FILE_NAME] . ','
            . $dbColumnNames[CHAT_COLUMN_MIME_TYPE] . ','
            . $dbColumnNames[CHAT_COLUMN_FILE_SIZE]
            . ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

        $insertParams = [$xId, $pIdCreator, $cIdTopic, $cIdThread, $username, CHAT_COLUMN_TYPE_FILE, $caption, $originalName, $relativeTarget, $mimeType, $upload['size']];

        try {
            $insertId = $db->sql($sqlInsert, ROW_REGULAR, $insertParams, 'Failed to insert chat attachment row');
        } catch (\Throwable $e) {
            // Atomicity: the file was already moved to disk — remove it so a failed insert doesn't leave an orphan
            if (file_exists($absoluteTarget)) {
                unlink($absoluteTarget);
            }
            throw $e;
        }

        //nchan-Publish: notify live subscribers
        Chat::publish($chatConfig, 'ping', ['cId' => $insertId, 'chatRoom' => ($cIdThread === 0)]);

        return [CHAT_COLUMN_ID => $insertId];
    }

    /**
     * @param array $vars
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     */
    public static function downloadAttachment(array $vars): void {

        //Extract context from SIP
        $attachmentId = $vars[CHAT_MESSAGE_ID] ?? false;
        $chatConfigJson = $vars[CHAT_CONFIG_SIP] ?? false;

        if ($attachmentId === false || $chatConfigJson === false) {
            throw new \DownloadException('Invalid attachment download: missing parameters');
        }

        $chatConfig = json_decode($chatConfigJson, true);
        if (!is_array($chatConfig) || empty($chatConfig)) {
            throw new \DownloadException('Invalid chat configuration in session');
        }

        //Database lookup
        $dbColumnNames = $chatConfig[CHAT_DB_COLUMN_NAMES] ?? [];
        $db = new Database();

        $sql = 'SELECT ' . $dbColumnNames[CHAT_COLUMN_XID] . ',' . $dbColumnNames[CHAT_COLUMN_CID_TOPIC] . ',' . $dbColumnNames[CHAT_COLUMN_FILE_NAME] . ','
            . $dbColumnNames[CHAT_COLUMN_PATH_FILE_NAME] . ',' . $dbColumnNames[CHAT_COLUMN_MIME_TYPE]
            . ' FROM ' . $chatConfig[CHAT_TABLE_NAME]
            . ' WHERE ' . $dbColumnNames[CHAT_COLUMN_ID] . ' = ?'
            . ' AND ' . $dbColumnNames[CHAT_COLUMN_TYPE] . ' = ?';

        $row = $db->sql($sql, ROW_EXPECT_1, [$attachmentId, CHAT_COLUMN_TYPE_FILE], 'Attachment not found');

        //Authorization
        if ($row[$dbColumnNames[CHAT_COLUMN_XID]] != $chatConfig[CHAT_COLUMN_XID]
            || $row[$dbColumnNames[CHAT_COLUMN_CID_TOPIC]] != $chatConfig[CHAT_COLUMN_CID_TOPIC]) {
            throw new \DownloadException('Attachment access denied');
        }

        //Resolve absolute path and verify file integrity
        $relativePath = $row[$dbColumnNames[CHAT_COLUMN_PATH_FILE_NAME]];
        $absolutePath = Path::absoluteApp($relativePath);

        if (!file_exists($absolutePath) || !is_readable($absolutePath)) {
            throw new \DownloadException('Attachment file missing or unreadable');
        }

        //Stream the file with download headers
        $fileName = $row[$dbColumnNames[CHAT_COLUMN_FILE_NAME]];
        $mimeType = $row[$dbColumnNames[CHAT_COLUMN_MIME_TYPE]];

        header('Content-Type: ' . $mimeType);
        header('X-Content-Type-Options: nosniff');
        header('Content-Disposition: attachment; filename="' . $fileName . '"');
        header('Content-Length: ' . filesize($absolutePath));

        readfile($absolutePath);
    }

}