<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 5/29/16
 * Time: 5:24 PM
 */

namespace IMATHUZH\Qfq\Core\Form;

use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Evaluate;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
use IMATHUZH\Qfq\Core\Helper\Ldap;
use IMATHUZH\Qfq\Core\Helper\MessengerPublisher;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Helper\Support;
use IMATHUZH\Qfq\Core\Report\Link;
use IMATHUZH\Qfq\Core\Report\SendMail;
use IMATHUZH\Qfq\Core\Store\Store;


/**
 * Class formAction
 * @package qfq
 */
class FormAction {

//    private $feSpecNative = array(); // copy of all formElement.class='native' of the loaded form
    /**
     * @var Evaluate instantiated class
     */
    protected $evaluate = null;  // copy of the loaded form

    private $formSpec = array();
    private $primaryTableName = '';

    /**
     * @var Database
     */
    private $dbArray = null;

    /**
     * @var int|string|null
     */
    private $dbIndexData;
    private $dbIndexQfq;

    /**
     * @var Store
     */
    private $store = null;

    private $link = null;


    /**
     * @param array $formSpec
     * @param array $db
     * @param bool|false $phpUnit
     * @throws \CodeException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct(array $formSpec, array $dbArray, $phpUnit = false) {

        #TODO: rewrite $phpUnit to: "if (!defined('PHPUNIT_QFQ')) {...}"
        $this->formSpec = $formSpec;
        $this->primaryTableName = Support::setIfNotSet($formSpec, F_TABLE_NAME);

        $this->store = Store::getInstance('', $phpUnit);

        $this->dbArray = $dbArray;
        $this->dbIndexData = $formSpec[F_DB_INDEX] ?? $this->store::getVar(SYSTEM_DB_INDEX_DATA, STORE_SYSTEM);
        $this->dbIndexQfq = $this->store::getVar(SYSTEM_DB_INDEX_QFQ, STORE_SYSTEM);

        if (!isset($this->dbArray[$this->dbIndexData])) {
            $this->dbArray[$this->dbIndexData] = new Database($this->dbIndexData);
        }

        $this->evaluate = new Evaluate($this->store, $this->dbArray[$this->dbIndexData]);
        $this->link = new Link($this->store::getSipInstance(), $this->dbIndexData);
    }

    /**
     * Parse $fillStoreVar and if something is given, add it to STORE_VAR.
     *
     * @param string $fillStoreVar
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function feFillStoreVar($fillStoreVar) {

        if ($fillStoreVar != '') {

            $this->store->setVar(SYSTEM_FORM_ELEMENT_COLUMN, FE_FILL_STORE_VAR, STORE_SYSTEM); // debug
            $rows = $this->evaluate->parse($fillStoreVar, ROW_EXPECT_0_1);

            if (is_array($rows)) {
                $this->store->appendToStore($rows, STORE_VAR);
            } else {
                if (!empty($rows)) {
                    throw new \UserFormException(json_encode(
                        [ERROR_MESSAGE_TO_USER => "Invalid statement for 'fillStoreVar'.",
                            ERROR_MESSAGE_TO_DEVELOPER => $fillStoreVar]), ERROR_INVALID_OR_MISSING_PARAMETER);
                }
            }
        }

    }

    /**
     * @param integer $recordId
     * @param array $feSpecAction
     * @param string $feTypeList
     *         On FormLoad: FE_TYPE_BEFORE_LOAD, FE_TYPE_AFTER_LOAD
     *         Before Save: FE_TYPE_BEFORE_SAVE, FE_TYPE_BEFORE_INSERT, FE_TYPE_BEFORE_UPDATE, FE_TYPE_BEFORE_DELETE
     *         After Save: FE_TYPE_AFTER_SAVE, FE_TYPE_AFTER_INSERT, FE_TYPE_AFTER_UPDATE, FE_TYPE_AFTER_DELETE
     *
     * @return int: ACTION_ELEMENT_MODIFIED if there are potential changes on the DB like fired SQL statements,
     *              ACTION_ELEMENT_NO_CHANGE if nothing happened
     *              ACTION_ELEMENT_DELETED:  if a record has been deleted (only in recursive calls, not the initial one)
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function elements($recordId, array $feSpecAction, $feTypeList) {

        $rc = ACTION_ELEMENT_NO_CHANGE;

        // Iterate over all Action FormElements
        foreach ($feSpecAction as $fe) {
            // Check if sql*** = Exists and is Enclosed by {{ }}
            Support::validateFeActions($fe, ACTION_REQUIRING_BRACES);

            // Preparation for Log, Debug
            $this->store->setVar(SYSTEM_FORM_ELEMENT, Logger::formatFormElementName($fe), STORE_SYSTEM); // debug
            $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, $fe[FE_ID] ?? '', STORE_SYSTEM); // debug

            $fe = HelperFormElement::initActionFormElement($fe);

            // Only process FE elements of types listed in $feTypeList. Skip all other
            if (false === Support::findInSet($fe[FE_TYPE], $feTypeList)) {
                continue;
            }

            $this->feFillStoreVar($fe[FE_FILL_STORE_VAR] ?? '');
            $fe[FE_FILL_STORE_VAR] = ''; // do not process the same later on.

            // Process templateGroup action elements
            if (isset($fe[FE_ID_CONTAINER]) && $fe[FE_ID_CONTAINER] > 0) {
                // Get native 'templateGroup'-FE - to retrieve MAX_LENGTH
                $templateGroup = $this->dbArray[$this->dbIndexQfq]->sql(SQL_FORM_ELEMENT_TEMPLATE_GROUP_FE_ID, ROW_EXPECT_1, [$fe[FE_ID_CONTAINER]],
                    "Action FormElements should not be assigned to a container (exception: templateGroup). FormElement.id=" . $fe[FE_ID] . ", feIdContainer=" . $fe[FE_ID_CONTAINER] . ' is not a templateGroup');

                if (isset($templateGroup[FE_TYPE]) && $templateGroup[FE_TYPE] == FE_TYPE_TEMPLATE_GROUP) {
                    $maxCopies = HelperFormElement::tgGetMaxLength($templateGroup[FE_MAX_LENGTH]);
                    $fe[FE_ID_CONTAINER] = 0; // Flag to make the nested TG unnested and therefore the SQLs are fired.
//                    for ($ii = $maxCopies; $ii > 0; $ii--) { // Iterate backwards: deleting records starts at the end and doesn't affect remaining counting
                    $correctDeleteIndex = 0;
                    for ($ii = 1; $ii <= $maxCopies; $ii++) {
                        $feNew = OnArray::arrayValueReplace($fe, FE_TEMPLATE_GROUP_NAME_PATTERN, $ii - $correctDeleteIndex);
                        $feNew = OnArray::arrayValueReplace($feNew, FE_TEMPLATE_GROUP_NAME_PATTERN_0, $ii - 1 - $correctDeleteIndex);
                        switch ($this->elements($recordId, [$feNew], $feTypeList)) {
                            case ACTION_ELEMENT_MODIFIED:
                                $rc = ACTION_ELEMENT_MODIFIED;
                                break;
                            case ACTION_ELEMENT_DELETED:
                                $rc = ACTION_ELEMENT_MODIFIED;
                                $correctDeleteIndex++;
                                break;
                            case ACTION_ELEMENT_NO_CHANGE:
                            default:
                                break;
                        }
                    }
                    continue; // skip to next FormElement
                }
            }

            switch ($fe[FE_TYPE]) {
                case FE_TYPE_BEFORE_LOAD:
                case FE_TYPE_AFTER_LOAD:
                case FE_TYPE_AFTER_DELETE:  # Main record is already deleted. Do not try to load it again.
                    break;
                default:
                    // Always work on recent data: previous actions might have modified the data.
                    $this->store->fillStoreWithRecord($this->primaryTableName, $recordId, $this->dbArray[$this->dbIndexData], $this->formSpec[F_PRIMARY_KEY] ?? '');
            }

            if (!$this->checkRequiredList($fe)) {
                continue;
            }

            if (isset($fe[FE_FILL_STORE_LDAP])) {
                $keyNames = [F_LDAP_SERVER, F_LDAP_BASE_DN, F_LDAP_ATTRIBUTES, F_LDAP_SEARCH, F_LDAP_TIME_LIMIT];
                $fe = OnArray::copyArrayItemsIfNotAlreadyExist($this->formSpec, $fe, $keyNames);

                // Extract necessary elements
                $config = OnArray::getArrayItems($fe, [FE_LDAP_SERVER, FE_LDAP_BASE_DN, FE_LDAP_SEARCH, FE_LDAP_ATTRIBUTES, FE_LDAP_USE_BIND_CREDENTIALS]);
                $config = $this->evaluate->parseArray($config);

                if ($fe[FE_LDAP_USE_BIND_CREDENTIALS] == 1) {
                    $config[SYSTEM_LDAP_1_RDN] = $this->store->getVar(SYSTEM_LDAP_1_RDN, STORE_SYSTEM);
                    $config[SYSTEM_LDAP_1_PASSWORD] = $this->store->getVar(SYSTEM_LDAP_1_PASSWORD, STORE_SYSTEM);
                }

                $ldap = new Ldap();
                $arr = $ldap->process($config, '', MODE_LDAP_SINGLE);
                $this->store->setStore($arr, STORE_LDAP, true);
            }

            // sqlValidate should not be checked if submit_reason = "save,force"
            if (API_SUBMIT_REASON_SAVE_FORCE !== $this->store->getVar(API_SUBMIT_REASON, STORE_CLIENT . STORE_EMPTY, SANITIZE_ALLOW_ALNUMX)) {
                HelperFormElement::sqlValidate($this->evaluate, $fe);
            }

            if ($fe[FE_TYPE] === FE_TYPE_SENDMAIL) {
                $this->doSendMail($fe);
            }

            $rcTmp = $this->doSqlBeforeSlaveAfter($fe, $recordId, true);
            switch ($rcTmp) {
                case ACTION_ELEMENT_MODIFIED:
                case ACTION_ELEMENT_DELETED:
                    $rc = $rcTmp;
                    break;
                default:
                    break;
            }
        }
        $this->store->setVar(SYSTEM_FORM_ELEMENT, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, 0, STORE_SYSTEM);

        return $rc;
    }

    /**
     * Process all FormElements given in the `requiredList` identified by their name.
     * If none is empty in STORE_FORM return true, else false.
     * If none FormElement is specified, return true.
     *
     * @param array $fe
     *
     * @return bool  true if none FE is specified or all specified are non empty.
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function checkRequiredList(array $fe) {

        if (!isset($fe[FE_REQUIRED_LIST]) || $fe[FE_REQUIRED_LIST] === '') {
            return true;
        }

        $arr = explode(',', $fe[FE_REQUIRED_LIST]);
        foreach ($arr as $key) {

            $key = trim($key);
            $val = $this->store->getVar($key, STORE_FORM, SANITIZE_ALLOW_ALL);

            if ($val === false || $val === '' || $val === '0') {
                return false;
            }
        }

        return true;
    }

    /**
     * @param array $feSpecAction
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    private function doSendMail(array $feSpecAction) {

        $args = array();

        $args[] = SENDMAIL_TOKEN_RECEIVER . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_TO]);
        $args[] = SENDMAIL_TOKEN_SENDER . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_FROM]);
        $args[] = SENDMAIL_TOKEN_SUBJECT . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_SUBJECT]);
        $args[] = SENDMAIL_TOKEN_BODY . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_VALUE]);
        $args[] = SENDMAIL_TOKEN_REPLY_TO . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_REPLY_TO]);
        $autoSubmit = ($this->evaluate->parse($feSpecAction[FE_SENDMAIL_FLAG_AUTO_SUBMIT]) === 'off') ? 'off' : 'on';
        $args[] = SENDMAIL_TOKEN_FLAG_AUTO_SUBMIT . PARAM_TOKEN_DELIMITER . $autoSubmit;
        $args[] = SENDMAIL_TOKEN_GR_ID . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_GR_ID]);
        $args[] = SENDMAIL_TOKEN_X_ID . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_X_ID]);
        $args[] = SENDMAIL_TOKEN_RECEIVER_CC . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_CC]);
        $args[] = SENDMAIL_TOKEN_RECEIVER_BCC . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_BCC]);
        $args[] = SENDMAIL_TOKEN_SRC . PARAM_TOKEN_DELIMITER . "FormId: " . $feSpecAction[FE_FORM_ID] . ", FormElementId: " . $feSpecAction['id'];
        $args[] = SENDMAIL_TOKEN_X_ID2 . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_X_ID2]);
        $args[] = SENDMAIL_TOKEN_X_ID3 . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_X_ID3]);
        $args[] = SENDMAIL_TOKEN_BODY_MODE . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_BODY_MODE]);
        $args[] = SENDMAIL_TOKEN_BODY_HTML_ENTITY . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_BODY_HTML_ENTITY]);
        $args[] = SENDMAIL_TOKEN_SUBJECT_HTML_ENTITY . PARAM_TOKEN_DELIMITER . $this->evaluate->parse($feSpecAction[FE_SENDMAIL_SUBJECT_HTML_ENTITY]);
        $args[] = $this->evaluate->parse($feSpecAction[FE_SENDMAIL_ATTACHMENT] ?? '');

        // Mail: send
        $sendMail = new SendMail();
        $mailConfig = $sendMail->parseStringToArray(implode(PARAM_DELIMITER, $args));
        $sendMail->process($mailConfig);
    }

    /**
     * Process slaveId, sqlBefore, sqlInsert|sqlUpdate|sqlDelete, sqlAfter.
     * flagFeAction=false: for Native Elements
     * flagFeAction=true: for Action Elements
     *
     * Create the slave record. First try to evaluate a slaveId. Depending if the slaveId > 0 choose `sqlUpdate` or
     * `sqlInsert`
     *
     * @param array $fe
     * @param int $recordId
     * @param bool $flagFeAction indicates of the FE are of type 'native' or 'action'.
     * @return int  ACTION_ELEMENT_MODIFIED if there are potential(!) changes on the DB like INSERT / UPDATE,
     *              ACTION_ELEMENT_NO_CHANGE if nothing happened
     *              ACTION_ELEMENT_DELETED:  if a record has been deleted
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function doSqlBeforeSlaveAfter(array $fe, $recordId, $flagFeAction) {

        // Uploads will be processed later.
        if ($fe[FE_TYPE] == FE_TYPE_UPLOAD) {
            return ACTION_ELEMENT_NO_CHANGE;
        }

        $rcStatus = ACTION_ELEMENT_NO_CHANGE;

        $this->feFillStoreVar($fe[FE_FILL_STORE_VAR] ?? '');

        // slaveId might be used in sqlBefore: get it first.
        if (isset($fe[FE_SLAVE_ID])) {
            // Get the slaveId
            $slaveId = $this->evaluate->parse($fe[FE_SLAVE_ID]);

            if ($flagFeAction && $slaveId === '' && $fe[FE_NAME] !== '') {
                // If the current action element has the same name as a real master record column: take that value as an id.
                $slaveId = $this->store->getVar($fe[FE_NAME], STORE_RECORD);
            }

            if ($slaveId === '' || $slaveId === false) {
                $slaveId = 0;
            }

            // Store the slaveId: it's used and replaced in the update statement.
            $this->store->setVar(VAR_SLAVE_ID, $slaveId, STORE_VAR, true);
        } else {

            if (false !== ($id = $this->store->getVar(VAR_SLAVE_ID, STORE_VAR))) {
                $slaveId = $id;
                $fe[FE_SLAVE_ID] = $id;
            }
        }

        // If given: fire a sqlBefore query
        $this->evaluate->parse($fe[FE_SQL_BEFORE]);

        if (isset($fe[FE_SLAVE_ID])) {
            $doInsert = ($slaveId == 0);
            $doUpdate = ($slaveId != 0);
            $doDelete = ($slaveId != 0) && !empty($fe[FE_SQL_DELETE]);

            if (!empty($fe[FE_SQL_HONOR_FORM_ELEMENTS])) {
                $filled = $this->checkFormElements($fe[FE_SQL_HONOR_FORM_ELEMENTS]);
                $doInsert = $filled && $doInsert;
                $doUpdate = $filled && $doUpdate;
                $doDelete = !$filled && $doDelete;
            }

            // Fire slave query
            if ($doInsert) {
                $slaveId = $this->evaluate->parse($fe[FE_SQL_INSERT]);
                // Store the slaveId: might be used later
                $this->store->setVar(VAR_SLAVE_ID, $slaveId, STORE_VAR, true);
                $rcStatus = ACTION_ELEMENT_MODIFIED;
            }

            if ($doUpdate) {
                $this->evaluate->parse($fe[FE_SQL_UPDATE]);
                $rcStatus = ACTION_ELEMENT_MODIFIED;
            }

            // Fire a delete query
            if ($doDelete) {
                $this->evaluate->parse($fe[FE_SQL_DELETE]);
                $slaveId = 0;
                $rcStatus = ACTION_ELEMENT_DELETED;
            }

            // Check if there is a column with the same name as the 'action'-FormElement.
            if ($flagFeAction && false !== $this->store->getVar($fe[FE_NAME], STORE_RECORD)) {
                // After an insert or update, propagate the (new) slave id to the master record.
                $this->dbArray[$this->dbIndexData]->sql("UPDATE `" . $this->primaryTableName . "` SET `" . $fe[FE_NAME] . "` = $slaveId WHERE `id` = ? LIMIT 1", ROW_REGULAR, [$recordId]);
            }
        }

        // If given: fire a $sqlAfter query
        $this->evaluate->parse($fe[FE_SQL_AFTER]);

        // If given: save json form
        if (!empty($fe[FE_SAVE_FORM_JSON]) && !empty($fe[FE_SAVE_FORM_JSON_NAME])) {
            $jsonContent = $this->evaluate->parse($fe[FE_SAVE_FORM_JSON]);
            if ($jsonContent !== '') {
                $dbQfq = new Database($this->store->getVar(SYSTEM_DB_INDEX_QFQ, STORE_SYSTEM));
                FormAsFile::jsonToDatabase($this->evaluate->parse($fe[FE_SAVE_FORM_JSON_NAME]), $jsonContent, $dbQfq);
            }
        }

        return $rcStatus;
    }

    /**
     * Iterates over list of FormElement-names and check STORE_FORM if there is a corresponding value. If at least one
     * of the give elements is non empty, return true. If all elements are empty, return false.
     *
     * @param string $listOfFormElementNames E.g.: 'city, street, number'
     *
     * @return bool true if at lease one of the named elements is non empty on STORE_FORM (use SANITIZE_ALLOW_ALL to
     *              perform the check)
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function checkFormElements($listOfFormElementNames) {
        $arr = explode(',', $listOfFormElementNames);

        foreach ($arr as $key) {
            $value = $this->store->getVar(trim($key), STORE_FORM . STORE_EMPTY, SANITIZE_ALLOW_ALL);
            if ($value != '') {
                return true;
            }
        }

        return false;
    }

    /**
     * Will be called for each master record (clipboard).
     * Process all FE.type='paste' for the given master record in clipboard.
     * Will store the clipboard in STORE_PARENT.
     *
     * @param array $feSpecAction - all FE.class='action' - just process 'paste'
     * @param string $recordSourceTable - table name from where to copy the source records
     * @param string $recordDestinationTable - table name where the records will be duplicated to.
     * @param string $sub - on the highest level an empty string. It's a filter, value comes from
     *                                       FE.name, to specify sub-sub copy rules.
     * @param array $clipboard
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function doAllFormElementPaste(array $feSpecAction, $recordSourceTable, $recordDestinationTable, $sub, array $clipboard) {

        # process all paste records
        foreach ($feSpecAction as $formElement) {

            // Set the clipboard as the parent record. Update always the latest created Ids
            $this->store->setStore($clipboard, STORE_PARENT_RECORD, true);

            // Only process FE elements of types listed in $feTypeList. Skip all other.
            if (false === Support::findInSet($formElement[FE_TYPE], FE_TYPE_PASTE) || $formElement[FE_LABEL] != $sub) {
                continue;
            }

            // Preparation for Log, Debug
            $this->store->setVar(SYSTEM_FORM_ELEMENT, Logger::formatFormElementName($formElement), STORE_SYSTEM);

            $formElement = HelperFormElement::initActionFormElement($formElement);

            if (!empty($formElement[FE_RECORD_DESTINATION_TABLE])) {
                $recordDestinationTable = $formElement[FE_RECORD_DESTINATION_TABLE];
                $recordSourceTable = (empty($formElement[FE_RECORD_SOURCE_TABLE])) ? $recordDestinationTable : $formElement[FE_RECORD_SOURCE_TABLE];
            }

            $newValues = $this->evaluate->parse($formElement[FE_SQL1]);

            # Dupliziere den Record. RC ist die ID des neu erzeugten Records.
            $lastInsertId = $this->prepareDuplicate($feSpecAction, $formElement, $newValues, $recordSourceTable, $recordDestinationTable, $sub, $clipboard);

            # Lege die Record ID im Array ab, damit spaetere 'paste' Records diese entsprechend einsetzen koennen.
            # Nur falls ein Name angegeben ist und dieser !='id' ist.
            if ($formElement[FE_NAME] !== '' && $formElement[FE_NAME] != COLUMN_ID) {
                $clipboard[$formElement[FE_NAME]] = $lastInsertId;
            }
        }
    } # doAllFormElementPaste()


    /**
     *
     *
     * @param array $feSpecActionAll - all FE.class='action' - just process 'paste'
     * @param array $feSpecAction
     * @param array $updateRecords - array of records: 'id' is the source.id, all other fields will replace
     *                                       source columns.
     * @param        $recordSourceTable - table name from where to copy the source records
     * @param        $recordDestinationTable - table name where the records will be duplicated to.
     * @param string $sub - on the highest level an empty string. It's a filter, value comes from
     *                                       FE.name, to specify sub-sub copy rules.
     * @param array $clipboard -
     * @return int - lastInsertId
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function prepareDuplicate(array $feSpecActionAll, array $feSpecAction, array $updateRecords, $recordSourceTable, $recordDestinationTable, $sub, array $clipboard) {
        $translateMap = array();
        $field = $feSpecAction[FE_NAME];

        // Sometimes there is no query at all.
        if (count($updateRecords) == 0) {
            return (0);
        }

        // Iterate (for the given Paste FE) all updateRecords: duplicate each.
        $lastInsertId = 0;
        foreach ($updateRecords as $newColumns) {

            // will be used in sub paste's
//            $clipboard["_src_id"] = $newColumns[COLUMN_ID];

            $rowSrc = $this->dbArray[$this->dbIndexData]->sql("SELECT * FROM `$recordSourceTable` WHERE `id`=?", ROW_EXPECT_1, [$newColumns[COLUMN_ID]]);

            $this->checkNCopyFiles($rowSrc, $newColumns);

            foreach ($newColumns as $key => $val) {
                $rowSrc[$key] = $val;
            }

            $lastInsertId = $this->copyRecord($rowSrc, $recordDestinationTable);
            $clipboard[$field] = $lastInsertId;
            $translateMap[$newColumns[COLUMN_ID]] = $lastInsertId;

            // Set the clipboard as the primary record as long as secondaries are created.
            $this->store->setStore($clipboard, STORE_PARENT_RECORD, true);

            # Do subqueries
            if ($sub == "") {
                $this->doAllFormElementPaste($feSpecActionAll, $recordSourceTable, $recordDestinationTable, $field, $clipboard);
            }
        }

        // If necessary: correct table self referencing id columns
        if (!empty($feSpecAction[FE_TRANSLATE_ID_COLUMN])) {
            $this->translateId($translateMap, $feSpecAction[FE_TRANSLATE_ID_COLUMN], $recordDestinationTable);
        }

        return $lastInsertId;

    } // prepareDuplicate()

    /**
     * Translate table self referencing columns to the new values.
     * Rerun on all new records. Search and translate old id's (copied) to the new generated id's.
     *
     * Example with FormElement:  id, feIdContainer, type
     *
     *     Original:  [1,2,'input'], [2,3,'templateGroup'], [3,0, 'pill']
     *   Duplicated:  [4,2,'input'], [5,3,'templateGroup'], [6,0, 'pill']
     *  TranslateId:  [4,5,'input'], [5,6,'templateGroup'], [6,0, 'pill']
     *
     * @param array $translateMap array with old id's as keys, and new id's as their value
     * @param string $translateIdColumn column name to update. E.g. FormElement.feIdContainer, Ggroup.grId, ...
     * @param string $tableName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function translateId(array $translateMap, $translateIdColumn, $tableName) {

        foreach ($translateMap as $oldId => $newId) {

            $row = $this->dbArray[$this->dbIndexData]->sql("SELECT `$translateIdColumn` FROM `$tableName` WHERE `id`=$newId", ROW_EXPECT_1);

            if (!empty($row[$translateIdColumn])) {
                $newNewId = $translateMap[$row[$translateIdColumn]];
                $this->dbArray[$this->dbIndexData]->sql("UPDATE `$tableName` SET `$translateIdColumn`=$newNewId WHERE `id`=$newId LIMIT 1");
            }

        }
    }

    /**
     * @param array $rowSrc
     * @param array $rowDest
     *
     * @throws \UserFormException
     */
    private function checkNCopyFiles(array $rowSrc, array $rowDest) {

        foreach ($rowSrc as $key => $val) {

            // Skip non 'special file column'.
            if (false === strpos($key, COLUMN_PATH_FILE_NAME)) {
                continue;
            }

            // If a/b) the target is empty, c) src & dest is equal, d) src is not a file: there is nothing to copy.
            if (empty($rowDest[$key]) || ($val === $rowDest[$key]) || !is_file($val)) {
                continue;
            }

            HelperFile::mkDirParent($rowDest[$key]);
            HelperFile::copy($val, $rowDest[$key]);
        }
    }

    /**
     * Copy $row to $destable.
     * Copy only values which have a column in $destTable.
     * If there is nothing to copy - Do nothing.
     * Columns with name 'id', 'modified' or 'created' are skipped.
     *
     * @param array $row
     * @param string $destTable
     *
     * @return int - lastInsertId
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    function copyRecord(array $row, $destTable) {
        $keys = array();
        $values = array();
        $placeholder = array();

        $columns = $this->dbArray[$this->dbIndexData]->sql("SHOW FIELDS FROM " . $destTable);

        // Process all columns of destTable
        foreach ($columns as $col) {

            $key = $col[COLUMN_FIELD];
            // Only copy columns which exist on source AND destination.
            if (!isset($row[$key])) {
                continue;
            }
            $val = $row[$key];

            switch ($key) {
                case COLUMN_ID:
                    continue 2;
                case COLUMN_MODIFIED:
                case COLUMN_CREATED:
                    $keys[] = $key;
                    $placeholder[] = 'NOW()';
                    continue 2;
            }

            if (isset($row[$key])) {
                $keys[] = $key;
                $values[] = $val;
                $placeholder[] = '?';
            }
        }

        // If there is nothing to write: return
        if (count($values) == 0) {
            return (0);
        }

        $keyString = '`' . implode('`,`', $keys) . '`';
        $valueString = implode(',', $placeholder);

        $sql = "INSERT INTO `$destTable` ($keyString) VALUES ($valueString)";

        return $this->dbArray[$this->dbIndexData]->sql($sql, ROW_REGULAR, $values);

    } # copyRecord()

    /**
     * Execute touch statement or publish to nchan messenger.
     * Example parameter string: P_page1|M_wsGroup:channel:action:payload
     *   P_<slug>                    -> touch /var/www/html/trigger/<slug>
     *   M_<group>:<chan>:<act>:<p>  -> publish {name: <act>, payload: <p>} to nchan channel <chan>
     *
     * The nchan publish targets SYSTEM_MESSENGER_PUBLISH_URL via MessengerPublisher,
     * which posts to the auth-less messenger-local.conf location. If that config
     * is empty, the publish is a silent no-op.
     *
     * @param string $parameter
     * @throws \UserFormException  if the messenger service rejects the publish
     */
    public function executeMessage(string $parameter): void {

        $basePathTriggerScript = Path::absoluteTriggerDir();
        // Parse the parameter: P_<pageSlug>|M_<ws>:<channel>:<action>:<payload>|P_<pageSlug2>|M_<ws2>:<channel2>:<action2>:<payload2>
        $result = $this->parseMessageParameter($parameter);

        foreach ($result as $entry) {
            if ($entry[F_MESSAGE_TYPE] === F_MESSAGE_TYPE_PAGE) {
                $filename = $entry[F_MESSAGE_SLUG];
                $output = shell_exec('touch ' . $basePathTriggerScript . '/' . $filename);
            } elseif ($entry[F_MESSAGE_TYPE] === F_MESSAGE_TYPE_MESSAGE) {
                switch ($entry[F_MESSAGE_ACTION]) {
                    case F_MESSAGE_ACTION_REPORT:
                        // Resolve payload → tt_content uid if a human-readable subheader was given.
                        if (!is_numeric($entry[F_MESSAGE_PAYLOAD])) {
                            $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
                            $sql = "SELECT * FROM `$dbT3`.`tt_content` WHERE `subheader` = ? AND `deleted` = 0 AND `hidden` = 0 ";
                            $arr = $this->dbArray[$this->dbIndexQfq]->sql($sql, ROW_EXPECT_1, [$entry[F_MESSAGE_PAYLOAD]], 'Function "subheader=' . $entry[F_MESSAGE_PAYLOAD] . '" not found or more than one found.');
                            if (!empty($arr[NAME_UID])) {
                                $entry[F_MESSAGE_PAYLOAD] = $arr[NAME_UID];
                            }
                        }

                        $errors = MessengerPublisher::publish(
                            $entry[F_MESSAGE_WS_GROUP],
                            [$entry[F_MESSAGE_CHANNEL]],
                            $entry[F_MESSAGE_ACTION],
                            $entry[F_MESSAGE_PAYLOAD]
                        );
                        if (!empty($errors)) {
                            throw new \UserFormException(json_encode(
                                [ERROR_MESSAGE_TO_USER => "Was not able to call the messenger service.",
                                    ERROR_MESSAGE_TO_DEVELOPER => "Messenger publish failed for group=" . $entry[F_MESSAGE_WS_GROUP] . " channel=" . $entry[F_MESSAGE_CHANNEL] . ". Check SYSTEM_MESSENGER_PUBLISH_URL and the messenger-local.conf nginx snippet."]),
                                ERROR_INVALID_OR_MISSING_PARAMETER
                            );
                        }

                        break;
                    default:
                        // Placeholder for other actions
                        break;
                }
            }
        }
    }

    /**
     * Parse message parameter
     * Example parameter string: P_page1|M_ws1:channel1:action1:payload1
     * Return: [ ['type' => 'page', 'slug' => 'page1'], ['type' => 'message' , 'wsGroup' => 'ws1', 'channel' => 'channel1', 'action' => 'action1', 'payload' => 'payload1'] ]
     *
     * Parses parameter string to extract segments marked with P_ or M_ prefixes.
     * Segments continue until next |P_ or |M_ marker
     *
     * @param string $parameter
     */
    private function parseMessageParameter(string $parameter): array {
        $result = [];
        // Pattern to match P_ and M_ entries
        $pattern = '/(?P<type>P|M)_(?P<content>(?:(?!\|P_|\|M_).)+)/';
        preg_match_all($pattern, $parameter, $matches, PREG_SET_ORDER);
        foreach ($matches as $match) {
            $type = $match[F_MESSAGE_TYPE];
            $content = $match['content'];
            if ($type === 'P') {
                // This is a page entry
                $result[] = [
                    F_MESSAGE_TYPE => F_MESSAGE_TYPE_PAGE,
                    F_MESSAGE_SLUG => $content
                ];
            } elseif ($type === 'M') {
                // For message entries, split by colons
                $parts = explode(':', $content, 4);

                // Create result based on available parts
                $messageEntry = [
                    F_MESSAGE_TYPE => F_MESSAGE_TYPE_MESSAGE,
                    F_MESSAGE_WS_GROUP => $parts[0] ?? null,
                    F_MESSAGE_CHANNEL => $parts[1] ?? null
                ];

                // Add action if available
                $messageEntry[F_MESSAGE_ACTION] = $parts[2] ?? null;

                // Add payload if available
                $messageEntry[F_MESSAGE_PAYLOAD] = $parts[3] ?? null;

                $result[] = $messageEntry;
            }
        }
        return $result;
    }
}
