<?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\MessengerPublisher;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\Path;
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',
            'xGrIdStatus' => 'xGrIdStatus',
            'type' => 'type',
            'emoticon' => 'emoticon',
            'reference' => 'reference',
            'message' => 'message',
            '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_MESSAGE, 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] . ' = ? 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];

        $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_MESSAGE, 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] . ' = ? '
                . 'ORDER BY ' . $dbColumnNames[CHAT_COLUMN_CREATED] . ' DESC ';

            $sqlThreadMessagesParams = [$threadIdList, CHAT_COLUMN_TYPE_MESSAGE];

            $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_MESSAGE, 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] . ' = ? '
                . 'ORDER BY ' . $dbColumnNames[CHAT_COLUMN_CREATED] . ' DESC ';

            $sqlRestMessagesParams = [$messageIdList, $allIds, CHAT_COLUMN_TYPE_MESSAGE];
            $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] . ' = ? 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];
            $resultFirstMessage = $db->sql($sqlFirstMessage, ROW_REGULAR, $sqlFirstMessageParam);

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

            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);

        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]];
                $chatJson[$message[$dbColumnNames[CHAT_COLUMN_ID]]] = $message;
                if ($threadId !== 0 && !in_array($threadId, $builtThreadIds)) {
                    // Get toolbar config from thread
                    $toolbarThreadConfig = self::createToolbarConfig($chatConfig, $threadId, $db, $store);
                    $chatJson[$message[$dbColumnNames[CHAT_COLUMN_ID]]][CHAT_TOOLBAR_CONFIG] = json_encode($toolbarThreadConfig, JSON_UNESCAPED_SLASHES);
                    $builtThreadIds[] = $threadId;
                }
            }
        }

        $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_ALLBUT);
        $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;
    }

    /** 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] . ' != ?' . $cIdThreadQuery;
            $sqlDeleteParam = [$tagValue, CHAT_COLUMN_TYPE_TAG, CHAT_TYPE_TAG_DONE];

            $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;
    }

}