<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/30/16
 * Time: 7:59 PM
 */

namespace IMATHUZH\Qfq\Core;

use HTMLPurifier;
use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Database\DatabaseManager;
use IMATHUZH\Qfq\Core\Exception\Thrower;
use IMATHUZH\Qfq\Core\Form\Chat;
use IMATHUZH\Qfq\Core\Form\FormAction;
use IMATHUZH\Qfq\Core\Form\FormElement\UploadFormElementPublication;
use IMATHUZH\Qfq\Core\Helper\EncryptDecrypt;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
use IMATHUZH\Qfq\Core\Helper\KeyValueStringParser;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\OnString;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Helper\SqlQuery;
use IMATHUZH\Qfq\Core\Helper\Support;
use IMATHUZH\Qfq\Core\Store\FillStoreForm;
use IMATHUZH\Qfq\Core\Store\Session;
use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;
use UserFormException;
use ZipArchive;

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

    private $formSpec = array();  // copy of the loaded form
    private $feSpecAction = array(); // copy of all formElement.class='action' of the loaded form
    private $feSpecNative = array(); // copy of all formElement.class='native' of the loaded form
    private $feSpecNativeRaw = array(); // copy of all formElement.class='native' of the loaded form
    public $changedElements = array();

    /**
     * @var FormAction
     */
    private $formAction = null;

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

    private $dbIndexData = false;
    private $dbIndexQfq = false;

    /**
     * @var Database[] - Array of Database instantiated class
     */
    protected $dbArray = array();

    private $evaluate = null;

    private $qfqLogFilename = '';

    /**
     * @param array $formSpec
     * @param array $feSpecAction
     * @param array $feSpecNative
     * @param array $feSpecNativeRaw
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct(array $formSpec, array $feSpecAction, array $feSpecNative, array $feSpecNativeRaw) {

        $this->formSpec = $formSpec;
        $this->feSpecAction = $feSpecAction;
        $this->feSpecNative = $feSpecNative;
        $this->feSpecNativeRaw = $feSpecNativeRaw;
        $this->store = Store::getInstance();

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

        $this->dbArray[$this->dbIndexData] = new Database($this->dbIndexData);

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

        $this->evaluate = new Evaluate($this->store, $this->dbArray[$this->dbIndexData]);
        $this->formAction = new FormAction($formSpec, $this->dbArray);

        $this->qfqLogFilename = Path::absoluteQfqLogFile();

        $this->changedElements = array();
    }

    /**
     * Starts save process. Returns recordId.
     *
     * @param $recordId
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function process($recordId) {
        $formAction = new FormAction($this->formSpec, $this->dbArray);

        // If developer is in formElement editor check for valid key and database field type
        $encryptionKey = trim($this->store->getVar(SYSTEM_ENCRYPTION_KEY, STORE_SYSTEM), " ");
        $formName = $this->store->getVar(SIP_FORM, STORE_SIP);
        if ($formName === FORM_NAME_FORM_ELEMENT || $formName === FORM_NAME_FORM) {
            // Validate form and formElement params, modeSql, multiSql
            if ($this->store->getVar(SYSTEM_VALIDATE_FORM_ELEMENT, STORE_SYSTEM)) {
                HelperFormElement::validateFormRules($this->store->getStore(STORE_FORM));
            }
        }

        if ($formName === FORM_NAME_FORM_ELEMENT) {

            $encryptionState = $this->store->getVar(FE_ENCRYPTION, STORE_FORM, SANITIZE_ALLOW_ALL);
            $databaseFieldType = 'No real column';
            if ($encryptionState === 'yes') {
                if ($encryptionKey == '') {
                    throw new \UserFormException("Missing encryption key.", ERROR_MISSING_ENCRYPTION_KEY);
                }
                $indexData = $this->store->getVar(SYSTEM_DB_INDEX_DATA, STORE_SYSTEM, SANITIZE_ALLOW_ALL);
                if (!EncryptDecrypt::validDatabaseFieldType($indexData, $databaseFieldType)) {
                    throw new \UserFormException("Invalid database field type for encryption: " . $databaseFieldType . ". Only varchar or text.", ERROR_INVALID_DATABASE_FIELD_TYPE);
                }
            }
        } else {
            // Get all elements which are to encrypt and their information
            $elementsToEncrypt = EncryptDecrypt::getElementsToEncrypt($this->feSpecNative);

            // Database field type and length from each form element is needed
            $dbObject = $this->dbArray[$this->dbIndexData];
            $dbFieldAttributes = EncryptDecrypt::getDbFieldAttributes($this->formSpec[F_TABLE_NAME], $dbObject);

            if (EncryptDecrypt::hasEnoughSpace($elementsToEncrypt, $dbFieldAttributes)) {
                // Overwrite values in form store with encrypted values
                foreach ($elementsToEncrypt as $element) {
                    $this->store->setVar($element['name'], $element[ENCRYPTION_VALUE], STORE_FORM);
                }
            }
        }

        if ($this->formSpec[F_MULTI_SQL] == '') {
            // If an old record exist: load it. Necessary to delete uploaded files which should be overwritten.
            $this->store->fillStoreWithRecord($this->formSpec[F_TABLE_NAME], $recordId,
                $this->dbArray[$this->dbIndexData], $this->formSpec[F_PRIMARY_KEY]);

            $rc = $this->processSingle($recordId, $formAction);
        } else {
            $rc = $this->saveMultiForm($formAction);
        }

        return $rc;
    }

    /**
     * Process save of a form. Fire all action elements first
     *
     * @param $recordId
     * @param $formAction
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function processSingle($recordId, FormAction $formAction) {

        // Action: Before
        $feTypeList = FE_TYPE_BEFORE_SAVE . ',' . ($recordId == 0 ? FE_TYPE_BEFORE_INSERT : FE_TYPE_BEFORE_UPDATE);
        $formAction->elements($recordId, $this->feSpecAction, $feTypeList);

        $this->checkRequiredHidden();

        $rc = $this->elements($recordId);

        // Uploads are handled after processing native form elements.
        $this->processUploads($rc);
        $this->processAllImageCutFE($recordId);

        // Action: After*, Sendmail
        $feTypeList = FE_TYPE_SENDMAIL . ',' . FE_TYPE_AFTER_SAVE . ',' . ($recordId == 0 ? FE_TYPE_AFTER_INSERT : FE_TYPE_AFTER_UPDATE);

        $status = $formAction->elements($rc, $this->feSpecAction, $feTypeList);
        if ($status != ACTION_ELEMENT_NO_CHANGE) {
            // Reload fresh saved record and fill STORE_RECORD with it.
            $this->store->fillStoreWithRecord($this->formSpec[F_TABLE_NAME], $rc, $this->dbArray[$this->dbIndexData], $this->formSpec[F_PRIMARY_KEY]);
        }

        // Action: Paste
        $this->pasteClipboard($this->formSpec[F_ID] ?? '', $formAction);

        return $rc;
    }

    /**
     * @return bool  true if there is at least one paste record, else false.
     */
    private function isPasteRecord() {

        foreach ($this->feSpecAction as $formElement) {
            if ($formElement[FE_TYPE] == FE_TYPE_PASTE) {
                return true;
            }
        }

        return false;
    }

    /**
     * Iterate over all Clipboard source records and fire for each all FE.type=paste records.
     *
     * @param int $formId
     * @param FormAction $formAction
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function pasteClipboard($formId, FormAction $formAction) {

        if (!$this->isPasteRecord()) {
            return;
        }

        $cookieQfq = $this->store->getVar(CLIENT_COOKIE_QFQ, STORE_CLIENT, SANITIZE_ALLOW_ALNUMX);
        if ($cookieQfq === false || $cookieQfq == '') {
            throw new \UserFormException('Qfq Session missing', ERROR_QFQ_SESSION_MISSING);
        }

        # select clipboard records
        $sql = "SELECT c.idSrc as id, c.xId FROM `Clipboard` AS c WHERE `c`.`cookie`='$cookieQfq' AND `c`.`formIdPaste`=$formId ORDER BY `c`.`id`";
        $arrClipboard = $this->dbArray[$this->dbIndexQfq]->sql($sql);

        // Process clipboard records.
        foreach ($arrClipboard as $srcIdRecord) {
            $formAction->doAllFormElementPaste($this->feSpecAction, $this->formSpec[F_TABLE_NAME], $this->formSpec[F_TABLE_NAME], "", $srcIdRecord);
        }

    } # doClipboard()

    /**
     * @param $formAction
     * @return int|string
     * @throws \CodeException
     * @throws \DbException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function saveMultiForm(FormAction $formAction) {

        $parentRecords = $this->evaluate->parse($this->formSpec[F_MULTI_SQL], ROW_REGULAR);
        $fakeRecord = false;
        $newDeleteBtns = array();

        $processCounter = null;
        // No rows: This must be an error, cause MultiForms must have at least one record.
        if (empty($parentRecords)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => $this->formSpec[F_MULTI_MSG_NO_RECORD],
                    ERROR_MESSAGE_TO_DEVELOPER => 'Query selects no records: ' . $this->formSpec[F_MULTI_SQL]]),
                ERROR_MISSING_EXPECT_RECORDS);
        }

        // Check for 'id' or '_id' as column name
        $idName = isset($parentRecords[0]['_' . F_MULTI_COL_ID]) ? '_' . F_MULTI_COL_ID : F_MULTI_COL_ID;

        // Check for a fake record (id=0) and no other records
        if (count($parentRecords) == 1 && $parentRecords[0][$idName] == 0) {
            $fakeRecord = true;
        }

        // Author: Zhoujie Li
        // get all Element with id = 0
        $newRow = $this->retriveNewElements($fakeRecord, $newDeleteBtns);
        // add the amount of new Element into $parentRecords as an array
        HelperFormElement::addGenericElements($parentRecords, count($newRow), $idName);

        // remove the fake record (id=0) from the array and keep all others
        if ($fakeRecord) {
            array_shift($parentRecords);
        }
        // End from author


        // Check that an column 'id' is given
        if (!isset($parentRecords[0][$idName])) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'Missing column "_' . F_MULTI_COL_ID . '"', ERROR_MESSAGE_TO_DEVELOPER => $this->formSpec[F_MULTI_SQL]]),
                ERROR_INVALID_OR_MISSING_PARAMETER);
        }

        $fillStoreForm = new FillStoreForm();
        $storeVarBase = $this->store->getStore(STORE_VAR);
        $flagCheckProcessRow = isset($this->formSpec[F_PROCESS_ROW]) && $this->formSpec[F_PROCESS_ROW] != '0';

        // Author: Zhoujie Li
        // Retrieve array keys from $newRow.
        $newRowIndexs = array_keys($newRow);
        // Initialize newRowCounter
        $newRowCounter = 0;
        // Loop through each record in the parent records array
        foreach ($parentRecords as $row) {
            // check if parameter processRow is set
            // Check if the current record ID is 0
            if ($row[$idName] == 0) {
                // Ensure $newRowCounter does not exceed the bounds of $newRowIndexs
                if (isset($newRowIndexs[$newRowCounter])) {
                    // Assign the current new row index to processCounter
                    $processCounter = $newRowIndexs[$newRowCounter];
                    // Increment newRowCounter
                    $newRowCounter++;
                } else {
                    // Handle the case where $newRowCounter exceeds the bounds
                    $processCounter = null;
                }
            } else {
                // Set processCounter to null if the current record ID is not 0
                $processCounter = null;
            }
            // End from author
            if ($flagCheckProcessRow) {

                $processRowName = HelperFormElement::buildFormElementName([FE_NAME => F_PROCESS_ROW_COLUMN], $row[$idName], $processCounter);

                if ('on' !== $this->store->getVar($processRowName, STORE_CLIENT . STORE_ZERO, SANITIZE_ALLOW_ALNUMX)) {
                    // Remove from $newDeleteBtns since these rows are not Processed
                    foreach ($newDeleteBtns as $key => $value) {
                        if (str_ends_with($key, (string)$processCounter)) {
                            unset($newDeleteBtns[$key]);
                        }
                    }
                    continue;
                }
            }

            // Always start with a clean STORE_VAR
            $this->store->setStore($storeVarBase, STORE_VAR, true);

            $this->store->setStore(OnArray::keyNameRemoveLeadingUnderscore($row), STORE_PARENT_RECORD, true);
            $this->store->setVar(F_MULTI_COL_ID, $row[$idName], STORE_PARENT_RECORD); // In case '_id' is used, both '_id' and 'id' should be accessible.

            $record = $this->dbArray[$this->dbIndexData]->sql('SELECT * FROM `' . $this->formSpec[F_TABLE_NAME] . '` WHERE `id`=' . $row[$idName], ROW_EXPECT_0_1);
            $this->store->setStore($record, STORE_RECORD, true);


            $id = $row[$idName];
            $newRecord = false;
            // if id = 0 then add new index 0-1, 0-2... to multisave records with id 0
            if ($row[$idName] === 0) {
                $id = $row[$idName] . HTML_DELIMITER_NAME . $processCounter;
                $newRecord = $id;
            }
            // Fake current recordId
            $this->store->setVar(SIP_RECORD_ID, $id, STORE_SIP);
            $fillStoreForm->process(FORM_SAVE);

            $rc = $this->processSingle($row[$idName], $formAction);
            if ($newRecord) {
                $newDeleteBtns[$newRecord][API_CHANGED_ELEMENTS_ID] = $rc;
            }
        }

        $this->changedElements[API_CHANGED_ELEMENTS_DELETE_BUTTONS] = $newDeleteBtns;

        // If no rows are selected, saving is still possible, thus requiring a null coalescing operator.
        return $rc ?? '';
    }

    /**
     * Get the newly added elements in the $_POST array using explode.
     * Grouped elements share the same last segment (index) after the last hyphen, e.g., -0-1.
     *
     * @return array
     */
    private function retriveNewElements($fakeRecord, &$newDeleteBtns): array {
        $newElementsGroups = [];
        $dummyIndex = -1;
        // Iterate over each item in the $_POST array
        foreach ($_POST as $key => $value) {
            // explode in to array with delimiter '-'
            $parts = explode('-', $key);
            // Check if the key fits the expected pattern and collect unique indexes
            if (count($parts) >= 3 && $parts[1] === '0') {
                // If the id of the dummy was already found skip elements with the same ending id index
                if ($parts[2] == $dummyIndex || $parts[2] == '0') {
                    continue;
                }
                // The first element that gets found is the dummy row - not in case of empty result set.
                // Special case: $fakeRecord is true, then the dummy index is set to 1
                if ($dummyIndex === -1 && !$fakeRecord || $dummyIndex === -1 && $fakeRecord && $parts[2] == '1') {
                    $dummyIndex = $parts[2];
                    continue;
                }
                // Get the last part, which is the index
                $index = end($parts);
                // Use the index as key to avoid duplicates
                $newElementsGroups[$index] = true;
                $newDeleteBtns[$parts[1] . '-' . $parts[2]][API_CHANGED_ELEMENTS_HTML_NAME] = $key;
                $newDeleteBtns[$parts[1] . '-' . $parts[2]][API_CHANGED_ELEMENTS_FORM_TYPE] = API_CHANGED_ELEMENTS_FORM_TYPE_MULTI;
            }
        }

        return $newElementsGroups;
    }


    /**
     * Create empty FormElements based on templateGroups, for those who not already exist.
     *
     * @param array $formValues
     *
     * @return array
     * @throws \UserFormException
     */
    private function createEmptyTemplateGroupElements(array $formValues) {

        foreach ($this->feSpecNative as $formElement) {

            switch ($formElement[FE_TYPE]) {
//                case FE_TYPE_EXTRA:
                case FE_TYPE_NOTE:
                case FE_TYPE_SUBRECORD:
                    continue 2;
                default:
                    break;
            }
            $feName = $formElement[FE_NAME];

            // #7705. Skip FE, which are not already expanded. Detect them by '%' (== '%d')
            if (!isset($formValues[$feName]) && false === stripos($feName, '%d') && $this->isMemberOfTemplateGroup($formElement)) {
                $formValues[$feName] = $formElement[FE_VALUE];
            }
        }

        return $formValues;
    }

    /**
     * Check if the current $formElement is member of a templateGroup.
     *
     * @param array $formElement
     * @param int $depth
     * @return bool
     * @throws \UserFormException
     */
    private function isMemberOfTemplateGroup(array $formElement, $depth = 0) {
        $depth++;

        if ($depth > 15) {
            throw new \UserFormException('FormElement nested too much (in each other - endless?): stop recursion',
                ERROR_FE_NESTED_TOO_MUCH);
        }

        if ($formElement[FE_TYPE] == FE_TYPE_TEMPLATE_GROUP) {
            return true;
        }

        if ($formElement[FE_ID_CONTAINER] == 0) {
            return false;
        }

        // Get the parent element
        $formElementArr = OnArray::filter($this->feSpecNativeRaw, FE_ID, $formElement[FE_ID_CONTAINER]);
        if (isset($formElementArr[0])) {
            return $this->isMemberOfTemplateGroup($formElementArr[0], $depth);
        }

        return false; // This should not be reached,
    }

    /**
     *
     * @param $feName
     *
     * @return bool
     */
    private function isSetEmptyMeansNull($feName) {

        $fe = OnArray::filter($this->feSpecNative, FE_NAME, $feName);

        $flag = isset($fe[0][FE_EMPTY_MEANS_NULL]) && $fe[0][FE_EMPTY_MEANS_NULL] != '0';

        return $flag;
    }

    /**
     * SAVES THE RECORD TO THE DB!!!
     * Build an array of all values which should be saved. Values must exist as a 'form value' as well as a regular
     * 'table column'.
     *
     * @param $recordId
     *
     * @return int    record id (in case of insert, it's different from $recordId)
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function elements($recordId) {
        $columnCreated = false;
        $columnModified = false;
        $realColumnFound = false;

        $newValues = array();

        $tableColumns = array_keys($this->store->getStore(STORE_TABLE_COLUMN_TYPES));
        $tableColumnTypes = $this->store->getStore(STORE_TABLE_COLUMN_TYPES);
        $formValues = $this->store->getStore(STORE_FORM);
        $formValues = $this->createEmptyTemplateGroupElements($formValues);

        $feColumnTypes = array();
        foreach ($this->feSpecNative as $fe) {
            $feColumnTypes[$fe[FE_NAME]] = $fe[FE_TYPE];
        }

        // Validate InputCollectionJson FormElements (only if any exist)
        if (in_array(FE_TYPE_INPUT_COLLECTION_JSON, $feColumnTypes)) {
            $this->validateInputCollectionJson($tableColumnTypes);
        }

        // Get htmlAllow parameters of all formValues and store in $feSpecsTags
        $feSpecsTags = $this->getHtmlAllowTags($this->feSpecNative, $formValues);

        // Prepare extra columnValues
        $extraValues = $this->prepareExtraValues($this->formSpec);

        // Iterate over all table.columns. Built an assoc array $newValues.
        foreach ($tableColumns as $column) {

            // Never save a predefined 'id': autoincrement values will be given by database..
            if ($column === COLUMN_ID) {
                continue;
            }

            // Skip Upload Elements: those will be processed later.
            if ($this->isColumnUpload($column)) {
                $realColumnFound = true;
                continue;
            }

            if ($column === COLUMN_CREATED) {
                $columnCreated = true;
            }

            if ($column === COLUMN_MODIFIED) {
                $columnModified = true;
            }

            // Is it a extraValue given via F Parameter
            if (isset($extraValues[$column])) {
                $formValues[$column] = $extraValues[$column];
                unset($extraValues[$column]);
            }

            // Is there a value? Do not forget SIP values. Those do not have necessarily a FormElement.
            if (!isset($formValues[$column])) {
                continue;
            }

            $this->store->setVar(SYSTEM_FORM_ELEMENT, "Column: $column", STORE_SYSTEM);
            if (!isset($feColumnTypes[$column])) {
                $feColumnTypes[$column] = '';
            }

            // Convert time to datetime if mysql column is datetime, keep date if given
            if ($tableColumnTypes[$column] === DB_COLUMN_TYPE_DATETIME && $feColumnTypes[$column] === FE_TYPE_TIME) {
                // Keep old date value if exists
                $actualDate = explode(' ', $this->store->getVar($column, STORE_RECORD), 2)[0];
                if (!isset($actualDate) || $actualDate === '0000-00-00' || $actualDate === '') {
                    $actualDate = '0000-00-00';
                }

                // Check if date already is given to prevent double date issues.
                $actualDateFragments = explode(' ', $formValues[$column], 2);
                if (count($actualDateFragments) > 1) {
                    $formValues[$column] = $actualDate . ' ' . $actualDateFragments[1];
                } else {
                    $formValues[$column] = $actualDate . ' ' . $formValues[$column];
                }
            }

            // Convert date to datetime if mysql column is datetime, keep time if given
            if ($tableColumnTypes[$column] === DB_COLUMN_TYPE_DATETIME && $feColumnTypes[$column] === FE_TYPE_DATE) {
                $actualTime = '00:00:00';
                // Keep old time value if exists
                $timeFragment = explode(' ', $this->store->getVar($column, STORE_RECORD), 2);
                if (isset($timeFragment[1])) {
                    $actualTime = $timeFragment[1];
                }

                // Check if time from datetime already is given and prevent issues with double time string
                $actualTimeFragments = explode(' ', $formValues[$column], 2);
                if (!isset($actualTimeFragments[1])) {
                    $formValues[$column] = $formValues[$column] . ' ' . $actualTime;
                }
            }

            // Check if an empty string has to be converted to null.
            if (isset($formValues[$column]) && $formValues[$column] == '' && $this->isSetEmptyMeansNull($column)) {
                $formValues[$column] = null;
            } else {
                Support::setIfNotSet($formValues, $column);
            }

            // Check for existing htmlAllow and strip tags, purify html result to prevent XSS
            if (isset($feSpecsTags[$column]) && $feSpecsTags[$column] !== '') {
                $formValues[$column] = $this->custom_strip_tags($formValues[$column], $feSpecsTags[$column]);
                $formValues[$column] = $this->purifierHtml($formValues[$column]);
            }

            if ($feColumnTypes[$column] === FE_TYPE_SELECT) {
                // Typecast: some maria-db have a problem if an integer is assigned to an enum string.
                $formValues[$column] = (string)$formValues[$column];
            }

            if ($feColumnTypes[$column] === FE_TYPE_EDITOR && str_contains(($fe[FE_EDITOR_FILE_UPLOAD_PATH] ?? ''), PROTECTED_UPLOAD_DIR)  ) {
                $formValues[$column] = $this->replaceImageSourceSip($formValues[$column]);
            }

            $newValues[$column] = $formValues[$column];
            $realColumnFound = true;
        }

        // Only save record if real columns exist.
        if ($realColumnFound) {
            if (count($extraValues) !== 0) {
                $keys = implode(', ', array_keys($extraValues));
                throw new \UserFormException("The following columns could not be assigned: $keys");
            }

            if ($columnModified && !isset($newValues[COLUMN_MODIFIED])) {
                $newValues[COLUMN_MODIFIED] = date('YmdHis');
            }

            if ($recordId == 0) {
                if ($columnCreated && !isset($newValues[COLUMN_CREATED])) {
                    $newValues[COLUMN_CREATED] = date('YmdHis');
                }
                $recordId = $this->insertRecord($this->formSpec[F_TABLE_NAME], $newValues);

            } else {
                $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
            }
        }

        // Reload fresh saved record and fill STORE_RECORD with it. Do this before nativeDoSlave().
        $this->store->fillStoreWithRecord($this->formSpec[F_TABLE_NAME], $recordId, $this->dbArray[$this->dbIndexData],
            $this->formSpec[F_PRIMARY_KEY]);

        $this->nativeDoSlave($recordId);

        return $recordId;
    }

    /**
     *  Replaces <img> tag `src` attributes in the given HTML string with QFQ placeholders.
     *
     *  This function identifies all `<img>` tags containing SIP-based secure download URLs,
     *  extracts the `s=` parameter, resolves it to a file path using the QFQ SIP,
     *  and replaces the original `src` with the actual Full Filepath.
     *
     * @param string $text
     * @return string
     * @throws \CodeException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function replaceImageSourceSip(string $text): string {
        // HTML decode the input text
        $decodedText = html_entity_decode($text);
        $sip = new Sip();
        // Check for <img> tags and find all src attributes
        if (preg_match_all(PATTERN_IMAGE_TAG_AND_SOURCE, $decodedText, $matches)) {
            foreach ($matches[1] as $originalSrc) {
                // Extract 's=' parameter value from the src
                $parsedUrl = parse_url($originalSrc);
                if (!isset($parsedUrl['query'])) {
                    continue;
                }
                parse_str($parsedUrl['query'], $queryParams);
                if (!isset($queryParams[TOKEN_SIP])) {
                    continue;
                }
                $sParam = $queryParams[TOKEN_SIP];

                // Retrieve file data from Session
                $file = $sip->getVarsFromSip($sParam);
                if (!is_array($file) || !isset($file[SIP_DOWNLOAD_PARAMETER])) {
                    continue;
                }
                // Extract file path from SIP_DOWNLOAD_PARAMETER
                $parts = explode(':', base64_decode($file[SIP_DOWNLOAD_PARAMETER]));
                if (count($parts) !== 2) {
                    continue;
                }

                // append file Path to Base App URL
                // $filePath = Path::urlApp($parts[1]);
                // Create QFQ replacement
                $filePath = "{{r:7|M:file|d|F:$parts[1] AS link}}";


                $decodedText = str_replace($originalSrc, $filePath, $decodedText);
            }
        }
        return $decodedText;
    }

    /**
     * Get for every formElement htmlAllow tags from parameter
     *
     * @param $feSpecNative
     * @param $formValues
     * @return array
     */
    private function getHtmlAllowTags($feSpecNative, $formValues): array {

        $feSpecsTags = array();

        foreach ($feSpecNative as $formElement) {
            foreach ($formValues as $keyName => $keyValue) {
                if ($formElement[FE_NAME] === $keyName) {
                    if (isset($formElement[FE_HTML_ALLOW]) && $formElement[FE_HTML_ALLOW] !== '') {
                        $feSpecsTags[$keyName] = $formElement[FE_HTML_ALLOW];
                    }
                }
            }
        }

        return $this->setTinyMceSpecificTags($feSpecsTags);
    }

    /**
     * For TinyMCE there are specific tags needed for lists and text decoration (underline, strikethrough).
     * These tags should be added here.
     *
     * @param $feSpecsTags
     * @return array
     */
    private function setTinyMceSpecificTags($feSpecsTags): array {
        $listFlag = false;
        $decorationFlag = false;
        $tableFlag = false;
        $strongFlag = false;
        foreach ($feSpecsTags as $key => $value) {
            $feSpecsTagArray[$key] = explode(',', $value);
            foreach ($feSpecsTagArray[$key] as $key2 => $tag) {
                switch ($tag) {
                    case 'ul':
                    case 'ol':
                        $listFlag = true;
                        break;
                    case 'textDecoration':
                    case 'u':
                    case 'ins':
                    case 'del':
                    case 's':
                        $decorationFlag = true;
                        break;
                    case 'table':
                        $tableFlag = true;
                        break;
                    case 'b':
                        $strongFlag = true;
                        break;
                    default:
                        $feSpecsTagArray[$key][$key2] = $tag;
                        break;
                }
            }

            if ($listFlag) {
                $feSpecsTagArray[$key][] = "li";
                $listFlag = false;
            }

            // In case of TinyMCE span is automatically used for underline and strikethrough
            if ($decorationFlag) {
                $feSpecsTagArray[$key][] = "span";
                $decorationFlag = false;
            }


            if ($strongFlag) {
                $feSpecsTagArray[$key][] = "strong";
                $strongFlag = false;
            }

            if ($tableFlag) {
                array_push($feSpecsTagArray[$key], "th", "td", "tr", "tbody", "thead");
                $tableFlag = false;
            }

            $feSpecsTags[$key] = implode(',', $feSpecsTagArray[$key]);
        }

        return $feSpecsTags;
    }

    /**
     * Process sqlBefore, sqlInsert|.... for all native FE.
     *
     * @param $recordId
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function nativeDoSlave($recordId) {

        foreach ($this->feSpecNative as $fe) {
            // Preparation for Log, Debug
            $this->store->setVar(SYSTEM_FORM_ELEMENT, Logger::formatFormElementName($fe), STORE_SYSTEM);
            $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, $fe[FE_ID], STORE_SYSTEM);


            $this->formAction->doSqlBeforeSlaveAfter($fe, $recordId, false);
            $this->typeAheadDoTagGlue($fe);
        }

        $this->store->setVar(SYSTEM_FORM_ELEMENT, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, 0, STORE_SYSTEM);

    }

    /**
     * typeAhead: if given, process Tag or Glue.
     *
     * @param array $fe
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function typeAheadDoTagGlue(array $fe) {

        // Update 'glue' records?
        if (($fe[FE_TYPEAHEAD_TAG] ?? '0') == '0' || (!isset($fe[FE_TYPEAHEAD_GLUE_INSERT]) && !isset($fe[FE_TYPEAHEAD_TAG_INSERT]))) {
            return;
        }

        if (empty($fe[FE_TYPEAHEAD_GLUE_INSERT]) || empty($fe[FE_TYPEAHEAD_GLUE_DELETE])) {
            throw new \UserFormException("Missing 'typeAheadGlueInsert' or 'typeAheadGlueDelete'", ERROR_MISSING_REQUIRED_PARAMETER);
        }

        // Extract assigned tags: last
        $tagLast = KeyValueStringParser::parse($this->evaluate->parse($fe[FE_VALUE], ROW_EXPECT_0_1));

        // Extract assigned tags: new
        $tagCurrent = KeyValueStringParser::parse($this->store->getVar($fe[FE_NAME], STORE_FORM,
            ($fe[FE_CHECK_TYPE] == SANITIZE_ALLOW_AUTO) ? SANITIZE_ALLOW_ALNUMX : $fe[FE_CHECK_TYPE]));

        $result = array_diff_assoc($tagCurrent, $tagLast);

        // Create glue records
        // Add all tags from tagNew which do not exist in tagLast. 
        foreach ($result as $id => $value) {

            $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
            $this->store->setVar(VAR_TAG_VALUE, $value, STORE_VAR);

            if ($id == 0 || preg_match('/^0-\d+$/', $id)) {
                if (empty($fe[FE_TYPEAHEAD_TAG_INSERT])) {
                    throw new \UserFormException("Missing 'typeAheadTagInsert'", ERROR_MISSING_REQUIRED_PARAMETER);
                }
                // Create tag
                $id = $this->evaluate->parse($fe[FE_TYPEAHEAD_TAG_INSERT]);
                $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
            }

            // Create glue
            $this->evaluate->parse($fe[FE_TYPEAHEAD_GLUE_INSERT]);
        }

        // Get all tags that have been removed
        $result = array_diff_assoc($tagLast, $tagCurrent);

        // Delete Glue records
        foreach ($result as $id => $value) {
            $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
            $this->store->setVar(VAR_TAG_VALUE, $value, STORE_VAR);
            // Delete glue
            $this->evaluate->parse($fe[FE_TYPEAHEAD_GLUE_DELETE]);
        }

        // Update Glue Records
        if (isset($fe[FE_TYPEAHEAD_GLUE_UPDATE])) {
            foreach ($tagCurrent AS $id => $value) {
                $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
                $this->store->setVar(VAR_TAG_VALUE, $value, STORE_VAR);
                // Update Glue
                $this->evaluate->parse($fe[FE_TYPEAHEAD_GLUE_UPDATE]);
            }
        }
    }

    /**
     * Checks if there is a formElement with name '$feName' of type 'upload'
     *
     * @param $feName
     * @return bool
     */
    private function isColumnUpload($feName) {

        foreach ($this->feSpecNative as $formElement) {
            if ($formElement[FE_NAME] === $feName && $formElement[FE_TYPE] == FE_TYPE_UPLOAD)
                return true;
        }

        return false;
    }

    /**
     * Insert new record in table $this->formSpec['tableName'].
     *
     * @param $tableName
     * @param array $values
     *
     * @return int  last insert id
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function insertRecord($tableName, array $values) {

        if (count($values) === 0)
            return 0; // nothing to write, last insert id=0

        list($sql, $parameterArray) = SqlQuery::insertRecord($tableName, $values);

        $rc = $this->dbArray[$this->dbIndexData]->sql($sql, ROW_REGULAR, $parameterArray);

        return $rc;
    }

    /**
     * @param string $tableName
     * @param array $values
     * @param int $recordId
     * @param string $primaryKey
     *
     * @return bool|int     false if $values is empty, else affected rows
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function updateRecord($tableName, array $values, $recordId, $primaryKey = F_PRIMARY_KEY_DEFAULT) {

        if (count($values) === 0)
            return 0; // nothing to write, 0 rows affected

        list($sql, $parameterArray) = SqlQuery::updateRecord($tableName, $values, $recordId, $primaryKey);

        $rc = $this->dbArray[$this->dbIndexData]->sql($sql, ROW_REGULAR, $parameterArray);

        return $rc;
    }

    /**
     * Process all Upload Formelements for the given $recordId. After processing &$formValues will be updated with the
     * final filenames.
     *
     * Constellation: # FILE OLD   FILE NEW     FILESIZE
     *                1 none       none
     *                2 none       new
     *                3 exist      no change
     *                4 delete     none
     *                5 delete     new
     *
     * @param $recordId
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \InfoException
     */
    private function processUploads($recordId) {

        $sip = new Sip(false);
        $newValues = array();

        $flagDoUnzip = false;

        $formValues = $this->store->getStore(STORE_FORM);
        $primaryRecord = $this->store->getStore(STORE_RECORD); // necessary to check if the current formElement exist as a column of the primary table.

        // Upload - Take care the necessary target directories exist.
        $cwd = getcwd();
        $absoluteApp = Path::absoluteApp();
        if ($cwd === false || $absoluteApp === false || !HelperFile::chdir($absoluteApp)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'getcwd() failed or SITE_PATH undefined or chdir() failed', ERROR_MESSAGE_TO_DEVELOPER => "getcwd() failed or SITE_PATH undefined or chdir('$absoluteApp') failed."]),
                ERROR_IO_CHDIR);
        }

        foreach ($this->feSpecNative as $formElement) {
            // skip non upload formElements
            if ($formElement[FE_TYPE] != FE_TYPE_UPLOAD) {
                continue;
            }

            if (($formElement[UPLOAD_TYPE] ?? '') === UPLOAD_MULTI_UPLOAD) {
                // Handel Form Element Upload with type multiUpload
                $column = $formElement[FE_NAME];
                $uploadSip = $formValues[$column];
                $this->doMultiUpload($uploadSip, $formElement);
                continue;
            }

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

            $formElement = HelperFormElement::initUploadFormElement($formElement);
            if (isset($formElement[FE_FILL_STORE_VAR])) {
                $formElement[FE_FILL_STORE_VAR] = $this->evaluate->parse($formElement[FE_FILL_STORE_VAR], ROW_EXPECT_0_1);
                $this->store->appendToStore($formElement[FE_FILL_STORE_VAR], STORE_VAR);
            }

            $column = $formElement[FE_NAME];

            $statusUpload = $this->store->getVar($formValues[$column] ?? '', STORE_EXTRA);
            // Get file stats
            $vars = array();
            $vars[VAR_FILE_SIZE] = $statusUpload[FILES_SIZE] ?? 0;
            $vars[VAR_FILE_MIME_TYPE] = $statusUpload[FILES_TYPE] ?? '';

            // fileNote
            $noteColumn = $formElement[FE_NAME] . FILE_NOTE_COLUMN_EXTENSION;
            $sanitizeClassNote = $formElement[FE_CHECK_TYPE] === SANITIZE_ALLOW_AUTO ? SANITIZE_ALLOW_ALNUMX : $formElement[FE_CHECK_TYPE];
            $fileNote = $this->store->getVar($noteColumn . '-' . $recordId, STORE_CLIENT, $sanitizeClassNote);
            $vars[FE_FILE_NOTE] = $fileNote === false ? '' : $fileNote;

            // Check for 'unzip'.
            if (isset($formElement[FE_FILE_UNZIP])
                && $formElement[FE_FILE_UNZIP] != '0'
                && $vars[VAR_FILE_MIME_TYPE] == 'application/zip') {
                $flagDoUnzip = true;
            }

            // Do upload
            $pathFileName = $this->doUpload($formElement, ($formValues[$column] ?? ''), $sip, $modeUpload);
            if ($flagDoUnzip && $pathFileName != '') {
                if ($formElement[FE_FILE_UNZIP] == '' || $formElement[FE_FILE_UNZIP] == '1') {
                    // Set default dir.
                    $formElement[FE_FILE_UNZIP] = HelperFile::joinPathFilename(dirname($pathFileName), FE_FILE_UNPACK_DIR);
                }

                // Backup STORE_VAR - will be changed in doUnzip()
                $tmpStoreVar = $this->store->getStore(STORE_VAR);
                $this->doUnzip($formElement, $pathFileName);
                // Restore STORE_VAR
                $this->store->setStore($tmpStoreVar, STORE_VAR, true);
            }

            if ($modeUpload == UPLOAD_MODE_DELETEOLD && $pathFileName == '') {
                $pathFileNameTmp = '';  // see '4'
            } else {
                if (empty($pathFileName)) {
                    $pathFileNameTmp = $primaryRecord[$column] ?? ''; // see '3'. Attention: in case of Advanced Upload, $primaryRecord[$column] does not exist.
                } else {
                    $pathFileNameTmp = $pathFileName; // see '1,2,5'
                }
            }

            // Get latest file information
            if ($pathFileNameTmp == '') {
                // No new upload and no existing: take care to remove previous upload file statistics.
                $this->store->unsetVar(VAR_FILE_MIME_TYPE, STORE_VAR);
                $this->store->unsetVar(VAR_FILE_SIZE, STORE_VAR);
            } else {
                $this->store->appendToStore($vars, STORE_VAR);
            }

            // If given: fire a sqlBefore query
            if (!$flagDoUnzip) {
                $this->evaluate->parse($formElement[FE_SQL_BEFORE]);
            }

            $fileKeyParam = $formElement[FE_FILE_KEY] ?? '0';

            // Upload Type: Simple or Advanced
            // If (isset($primaryRecord[$column])) { - see #5048 - isset does not deal correctly with NULL!
            if (array_key_exists($column, $primaryRecord)) {
                // 'Simple Upload': no special action needed, just process the current (maybe modified) value.
                if ($pathFileName !== false) {
                    $newValues[$column] = $pathFileName;

                    if (isset($primaryRecord[COLUMN_FILE_SIZE])) {
                        $newValues[COLUMN_FILE_SIZE] = $vars[VAR_FILE_SIZE];
                    }

                    if (isset($primaryRecord[COLUMN_MIME_TYPE])) {
                        $newValues[COLUMN_MIME_TYPE] = $vars[VAR_FILE_MIME_TYPE];
                    }

                    if ($fileKeyParam !== '0' && $pathFileName !== '') {
                        // fileKey: "1" → default column, "<columnName>" → custom column
                        $fileKeyCol = ($fileKeyParam === '1') ? UPLOAD_PATH_FILE_NAME_KEY : $fileKeyParam;
                        if (array_key_exists($fileKeyCol, $primaryRecord)) {
                            $newValues[$fileKeyCol] = $this->generateFileKey(basename($pathFileName), $this->formSpec[F_TABLE_NAME], $fileKeyCol);
                        }
                    }
                }

                if (isset($formElement[FE_FILE_NOTE]) && isset($primaryRecord[$noteColumn]) && $modeUpload !== UPLOAD_MODE_DELETEOLD) {
                    // fileNote
                    $newValues[$noteColumn] = $vars[FE_FILE_NOTE];
                }

            } elseif (isset($formElement[FE_IMPORT_TO_TABLE]) && !isset($formElement[FE_SLAVE_ID])) {
                // Excel import on nonexisting column -> no upload
            } elseif ($flagDoUnzip) {
                // If ZIP and advanced upload: process it not here but via doUnzip.
            } else {
                // 'Advanced Upload': generate fileKey per FE, set in STORE_VAR right before query
                // fileKey: "<tableName>" or "<tableName>,<columnName>"
                if ($fileKeyParam !== '0' && $pathFileName !== false && $pathFileName !== '') {
                    $fileKeyParts = explode(',', $fileKeyParam);
                    $fileKeyTable = ($fileKeyParts[0] === '1') ? $this->formSpec[F_TABLE_NAME] : $fileKeyParts[0];
                    $fileKeyCol = $fileKeyParts[1] ?? UPLOAD_PATH_FILE_NAME_KEY;
                    $this->store->setVar(VAR_FILE_KEY, $this->generateFileKey(basename($pathFileName), $fileKeyTable, $fileKeyCol), STORE_VAR);
                }
                $this->doUploadSlave($formElement, $modeUpload, $vars[FE_FILE_NOTE]);
            }

            // If given: fire a sqlAfter query
            if (!$flagDoUnzip) {
                $this->evaluate->parse($formElement[FE_SQL_AFTER]);
            }
        }

        $this->store->setVar(SYSTEM_FORM_ELEMENT, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, 0, STORE_SYSTEM);

        // Clean up
        HelperFile::chdir($cwd);

        // Only used in 'Simple Upload'
        if (count($newValues) > 0) {
            $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
            // In case there is an update: Reload STORE_RECORD
            $this->store->fillStoreWithRecord($this->formSpec[F_TABLE_NAME], $recordId, $this->dbArray[$this->dbIndexData], $this->formSpec[F_PRIMARY_KEY]);
        }

    }

    /**
     * Unzip $pathFileName to $formElement[FE_FILE_UNZIP]. Before final extract, fire FE_SQL_VALIDATE.
     * For each file in ZIP:
     * - Fill STORE_VAR with VAR_FILENAME, VAR_FILENAME_ONLY, VAR_FILENAME_BASE, VAR_FILENAME_EXT, VAR_FILE_MIME_TYPE, VAR_FILE_SIZE.
     * - Fire $formElement[FE_SQL_VALIDATE]
     * - Fire FE_SLAVE_ID, FE_SQL_BEFORE, FE_SQL_INSERT, FE_SQL_UPDATE, FE_SQL_DELETE, FE_SQL_AFTER
     *
     * @param array $formElement
     * @param string $pathFileName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function doUnzip(array $formElement, $pathFileName) {

        if (!is_readable($pathFileName)) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => "Open ZIP file failed",
                ERROR_MESSAGE_TO_DEVELOPER => "File: " . $pathFileName]),
                ERROR_IO_ZIP_OPEN);
        }

        $zip = new ZipArchive();
        $res = $zip->open($pathFileName);
        if ($res !== true) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => "Open ZIP file failed" . HelperFile::zipFileErrMsg($res),
                ERROR_MESSAGE_TO_DEVELOPER => "File: " . $pathFileName]), ERROR_IO_ZIP_OPEN);
        }

        // Extract
        if (false === $zip->extractTo($formElement[FE_FILE_UNZIP])) {
            throw new \UserFormException("Failed to extract ZIP.", ERROR_IO_ZIP_OPEN);
        }

        // Do sqlValidate() - to get mime type of zipped items, the archive has already been extracted.
        if (!empty($formElement[FE_SQL_VALIDATE])) {
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $stat = $zip->statIndex($i);

                $itemPathFileName = HelperFile::joinPathFilename($formElement[FE_FILE_UNZIP], $stat['name']);
                $this->store->appendToStore(HelperFile::getFileStat($itemPathFileName), STORE_VAR);
                $this->store->appendToStore(HelperFile::pathinfo($itemPathFileName), STORE_VAR);

                HelperFormElement::sqlValidate($this->evaluate, $formElement);
            }
        }

        // Process
        if (!isset($formElement[FE_SLAVE_ID])) {
            $formElement[FE_SLAVE_ID] = '';
        }

        if (!empty($formElement[FE_SLAVE_ID] . $formElement[FE_SQL_BEFORE] . $formElement[FE_SQL_INSERT] .
            $formElement[FE_SQL_UPDATE] . $formElement[FE_SQL_DELETE] . $formElement[FE_SQL_AFTER])) {
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $stat = $zip->statIndex($i);

                $itemPathFileName = HelperFile::joinPathFilename($formElement[FE_FILE_UNZIP], $stat['name']);
                $this->store->appendToStore(HelperFile::getFileStat($itemPathFileName), STORE_VAR);
                $this->store->appendToStore(HelperFile::pathinfo($itemPathFileName), STORE_VAR);

                $this->evaluate->parse($formElement[FE_SQL_BEFORE]);
                $this->doUploadSlave($formElement, UPLOAD_MODE_NEW);
                $this->evaluate->parse($formElement[FE_SQL_AFTER]);
            }
        }

        // Close Zip
        if (false === $zip->close()) {
            throw new \UserFormException("Failed to close ZIP.", ERROR_IO_ZIP_OPEN);
        }
    }

    /**
     * Process all imageCut FormElements.
     *
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function processAllImageCutFE($recordId): void {

        foreach ($this->feSpecNative as $formElement) {
            // skip non upload formElements
            if ($formElement[FE_TYPE] == FE_TYPE_IMAGE_CUT) {

                // Typically: $htmlElementNameIdZero = true
                // After Saving a record, staying on the form, the FormElements on the Client are still known as '<feName>:0'.
                $htmlFormElementName = HelperFormElement::buildFormElementName($formElement, $recordId);

                $this->extractImageDataReplaceFile($formElement, $htmlFormElementName, $recordId);
            }
        }
    }


    /**
     * Iterates over all FE and checks all 'required' (mode & modeSql) FE.
     * If a required FE is empty, throw an exception.
     * Take care to remove all FE with modeSql='hidden'.
     *
     * Typically, the browser does not allow a submit if a required field is empty.
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function checkRequiredHidden() {

        $formModeGlobal = Support::getFormModeGlobal($this->formSpec[F_MODE_GLOBAL] ?? '');
        $reportRequiredFailed = true;

        switch ($formModeGlobal) {
            case F_MODE_REQUIRED_OFF:
            case F_MODE_REQUIRED_OFF_BUT_MARK:
                $reportRequiredFailed = false;
                break;
        }

        $clientValues = $this->store::getStore(STORE_FORM);

        $flagAllRequiredGiven = 1;

        foreach ($this->feSpecNative as $key => $formElement) {

            // Do not check retype slave FE.
            if (isset($formElement[FE_RETYPE_SOURCE_NAME])) {
                continue;
            }
            // Do not check FE.note
            if ($formElement[FE_TYPE] == FE_TYPE_NOTE) {
                continue;
            }

            $this->store->setVar(SYSTEM_FORM_ELEMENT, "Column: " . $formElement[FE_NAME], STORE_SYSTEM);
            $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, $formElement[FE_ID], STORE_SYSTEM);

            // Normalize FE_MODE
            $mode = Support::handleEscapeSpaceComment($formElement[FE_MODE_SQL] ?? '');
            $mode = empty($mode) ? ($formElement[FE_MODE] ?? '') : $this->evaluate->parse($mode);
            $this->feSpecNative[$key][FE_MODE] = $formElement[FE_MODE] = $mode;
            $this->feSpecNative[$key][FE_MODE_SQL] = $formElement[FE_MODE_SQL] = '';

            if (isset($formElement[FE_ACCEPT_ZERO_AS_REQUIRED]) && $formElement[FE_ACCEPT_ZERO_AS_REQUIRED] != '0' &&
                isset($clientValues[$formElement[FE_NAME]]) && $clientValues[$formElement[FE_NAME]] == '0') {
                continue;
            }

            // Upload needs special action to check for empty.
            if (($mode == FE_MODE_REQUIRED || $mode == FE_MODE_HIDDEN) && $formElement[FE_TYPE] == FE_TYPE_UPLOAD) {
                // If there is a new upload, returns array '[FILES_NAME, FILES_TMP_NAME, FILES_TYPE, FILES_ERROR, FILES_SIZE, FILES_FLAG_DELETE]'
                $statusUpload = $this->store->getVar($clientValues[$formElement[FE_NAME]] ?? '', STORE_EXTRA);
                if ($mode == FE_MODE_HIDDEN && ($statusUpload[FILES_FLAG_DELETE] ?? 0) == '0') {
                    continue;
                }
                // Check if there is a new upload
                if (!empty($statusUpload[FILES_TMP_NAME])) {
                    continue;// Upload given: continue with next FE
                }

                // No new upload: check for existing. To check for a value if it is given in STORE_RECORD, is ok for simple upload, but
                // breaks for advanced upload (non-primary column). Therefore, we need access to the original via UPLOAD SIP.
                $str = $this->store::$sip->getQueryStringFromSip($clientValues[$formElement[FE_NAME]]);
                $arr = KeyValueStringParser::parse($str, '=', '&');
                if (($arr[EXISTING_PATH_FILE_NAME] ?? '') == '' || ($statusUpload[FILES_FLAG_DELETE] ?? 0) == '1') {
                    // Fake to trigger the next if( .. empty...)
                    $clientValues[$formElement[FE_NAME]] = '';
                }
            }

            // Check if parent container is enabled
            if (($formElement[FE_ID_CONTAINER] ?? 0) !== 0) {

                $enabled = $this->isContainerEnabled($formElement[FE_ID_CONTAINER]);

                // Skip if container is not enabled
                if (!$enabled) {
                    continue;
                }
            }

            // Required fieldset is skipped (only child elements are checked)
            if ($mode == FE_MODE_REQUIRED && empty($clientValues[$formElement[FE_NAME]]) && $formElement[FE_TYPE] != FE_TYPE_FIELDSET) {
                $flagAllRequiredGiven = 0;
                if ($reportRequiredFailed) {
                    $name = ($formElement[FE_LABEL] == '') ? $formElement[FE_NAME] : $formElement[FE_LABEL];

                    throw new \UserFormException("Missing required value: $name", ERROR_REQUIRED_VALUE_EMPTY);
                }

                // Check if mode = required was inherited
            } else if (empty($clientValues[$formElement[FE_NAME]]) && $formElement[FE_TYPE] != FE_TYPE_FIELDSET) {
                $name = ($formElement[FE_LABEL] == '') ? $formElement[FE_NAME] : $formElement[FE_LABEL];

                // Check if FE is nested
                $feParent = OnArray::filter($this->feSpecNativeRaw, FE_ID, $formElement[FE_ID_CONTAINER]);

                // Check if parent FE is required fieldset
                // Only reached if JS-required-check is bypassed/skipped
                if (!empty($feParent) && $feParent[0][FE_TYPE] == FE_TYPE_FIELDSET && $feParent[0][FE_MODE] == FE_MODE_REQUIRED) {
                    $parentName = $feParent[0][FE_NAME];

                    throw new \UserFormException("Missing required value: $name (mode inherited from $parentName)", ERROR_REQUIRED_VALUE_EMPTY);
                }
            }

            if ($mode == FE_MODE_HIDDEN) {
                if (!($formElement[FE_TYPE] == FE_TYPE_UPLOAD && isset($statusUpload[FILES_FLAG_DELETE]) && $statusUpload[FILES_FLAG_DELETE] === '1')) {
                    // Removing the value from the store, forces that the value won't be stored.
                    $this->store::unsetVar($formElement[FE_NAME], STORE_FORM);
                }
            }
        }

        $this->store->setVar(SYSTEM_FORM_ELEMENT, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, 0, STORE_SYSTEM);

        // Save 'allRequiredGiven in STORE_VAR
        $this->store::setVar(VAR_ALL_REQUIRED_GIVEN, $flagAllRequiredGiven, STORE_VAR, true);
    }

    /**
     * imageCut:
     *
     * @param array $formElement
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function extractImageDataReplaceFile(array $formElement, $htmlFormElementName, $recordId) {

        // Get imageSource
        if ('' == ($pathFileName = $this->evaluate->parse($formElement[FE_IMAGE_SOURCE]))) {
            return; // Nothing to do.
        }

        $cwd = getcwd();
        $sitePath = Path::absoluteApp();
        if ($cwd === false || $sitePath === false || !HelperFile::chdir($sitePath)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'getcwd() failed or SITE_PATH undefined or chdir() failed', ERROR_MESSAGE_TO_DEVELOPER => "getcwd() failed or SITE_PATH undefined or chdir('$sitePath') failed."]),
                ERROR_IO_CHDIR);
        }

        // 'data:image/png;base64,AAAFBfj42Pj4...';
        $data = $this->store->getVar(HTML_NAME_TARGET_IMAGE . $formElement[FE_NAME], STORE_FORM, SANITIZE_ALLOW_ALLBUT);
        if ($data == '' || $data === false) {
            return; // Nothing to do
        }

        if (($formElement[FE_IMAGE_KEEP_ORIGINAL] ?? 1) == 1) {
            HelperFile::createOrigFileIfNotExist($pathFileName);
        } else {
            // Original File will be replaced: therefore remove Fabric JSON string from record, cause the file is already updated.
            $sql = "UPDATE " . $this->formSpec[F_TABLE_NAME] . ' SET ' . $formElement[FE_NAME] . '= "" WHERE id=' . $recordId;
            $this->dbArray[$this->dbIndexData]->sql($sql);
        }

        // Split base64 encoded image: 'data:image/png;base64,AAAFBfj42Pj4...'
        list($type, $imageData) = explode(';', $data, 2); // $type= 'data:image/png;', $imageData='base64,AAAFBfj42Pj4...'
        list(, $extension) = explode('/', $type); // $type='png'
        list(, $imageData) = explode(',', $imageData); // $imageData='AAAFBfj42Pj4...'

        if (false === file_put_contents($pathFileName, base64_decode($imageData))) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'Write new image failed', ERROR_MESSAGE_TO_DEVELOPER => "Write new image failed: $pathFileName"]),
                ERROR_IO_WRITE);
        }

        HelperFile::chdir($cwd);
    }

    /**
     * Process upload for the given Formelement. If necessary, delete a previous uploaded file.
     * Calculate the final path/filename and move the file to the new location.
     *
     * Check also: Documentation-develop/CODING.md
     *
     * @param array $formElement FormElement 'upload'
     * @param string $sipUpload SIP
     * @param Sip $sip
     * @param string $modeUpload UPLOAD_MODE_UNCHANGED | UPLOAD_MODE_NEW | UPLOAD_MODE_DELETEOLD |
     *                            UPLOAD_MODE_DELETEOLD_NEW
     * @return false|string New pathFilename or false on error
     * @throws \CodeException
     * @throws \DbException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \UserFormException
     * @throws \UserReportException
     * @internal param $recordId
     */
    private function doUpload($formElement, $sipUpload, Sip $sip, &$modeUpload) {
        $flagDelete = false;
        $modeUpload = UPLOAD_MODE_UNCHANGED;
        $pathFileName = '';

        // Status information about upload file
        $statusUpload = $this->store->getVar($sipUpload, STORE_EXTRA);
        if ($statusUpload === false) {
            return false;
        }

        if (isset($formElement[FE_IMPORT_TO_TABLE]) && isset($statusUpload[FILES_TMP_NAME])) {
            // Import
            $tmpFile = Support::extendFilename($statusUpload[FILES_TMP_NAME], UPLOAD_CACHED);
            $this->doImport($formElement, $tmpFile);
        }

        // Delete existing old file.
        if (isset($statusUpload[FILES_FLAG_DELETE]) && $statusUpload[FILES_FLAG_DELETE] == '1') {
            $arr = $sip->getVarsFromSip($sipUpload);
            $oldFile = $arr[EXISTING_PATH_FILE_NAME];
            if (file_exists($oldFile)) {
                //TODO: it might be possible to delete a file, which is referenced by another record - a check would be nice.
                HelperFile::unlink($oldFile, $this->qfqLogFilename);
            }
            $flagDelete = ($oldFile != '');
        }

        // Set $modeUpload
        if (isset($statusUpload[FILES_TMP_NAME]) && $statusUpload[FILES_TMP_NAME] != '') {
            $modeUpload = $flagDelete ? UPLOAD_MODE_DELETEOLD_NEW : UPLOAD_MODE_NEW;
        } else {
            $modeUpload = $flagDelete ? UPLOAD_MODE_DELETEOLD : UPLOAD_MODE_UNCHANGED;
        }
        if (isset($formElement[FILE_TYPE_KEY]) && $modeUpload !== UPLOAD_MODE_DELETEOLD) {
            $tmpFile = Support::extendFilename($statusUpload[FILES_TMP_NAME], UPLOAD_CACHED);

            $this->handlePublicationUpload($tmpFile, $formElement);
        }
        Logger::logMessageWithPrefix(UPLOAD_LOG_PREFIX . ': modeUpload= ' . $modeUpload, $this->qfqLogFilename);

        // skip uploading the file, if this is an import without a specified file destination
        if (!isset($formElement[FE_IMPORT_TO_TABLE]) || isset($formElement[FE_FILE_DESTINATION])) {
            $pathFileName = $this->copyUploadFile($formElement, $statusUpload);

            // Save final pathFileNames after form has been saved. Makes uploaded files downloadable without page reload.
            $statusUpload[UPLOAD_SIP_DOWNLOAD_KEY] = $statusUpload[UPLOAD_SIP_DOWNLOAD_KEY] ?? '';
            $sipDownloadParams = $this->store::getVar($statusUpload[UPLOAD_SIP_DOWNLOAD_KEY], STORE_EXTRA);
            $sipDownloadParams[FE_FILE_DESTINATION] = $pathFileName;
            $this->store::setVar($statusUpload[UPLOAD_SIP_DOWNLOAD_KEY], $sipDownloadParams, STORE_EXTRA);

            $msg = UPLOAD_LOG_PREFIX . ': ';
            $msg .= ($pathFileName == '') ? 'Remove old upload / no new upload' : 'File "' . $statusUpload[FILES_TMP_NAME] . '" >> "' . $pathFileName . '"';
            Logger::logMessageWithPrefix($msg, $this->qfqLogFilename);
        }

        // Delete current used uniq SIP
        $this->store->setVar($sipUpload, array(), STORE_EXTRA);

        return $pathFileName;
    }

    /**
     * Handle the upload of a BibTeX publication file and insert its entries into the database.
     *
     * This function checks required form attributes, ensures the file type is BibTeX,
     * and then delegates to processUpload().
     *
     * @param string $tmpFile Absolute path to the temporarily uploaded file.
     * @param array $attributes Form attributes; must include:
     *                            - OWNER_ID_KEY: The ID of the user who owns the publications.
     *                            - FIELD_TARGETTABLE: The name of the database table.
     *                            - FILE_TYPE_KEY: Must equal FILE_TYPE_BIB to trigger processing.
     *
     * @throws UserFormException  If OWNER_ID_KEY or FIELD_TARGETTABLE is missing or empty.
     *
     * @example
     * handlePublicationUpload($_FILES['bibfile']['tmp_name'], [
     *     OWNER_ID_KEY       => $currentUserId,
     *     FIELD_TARGETTABLE  => 'publications',
     *     FILE_TYPE_KEY      => FILE_TYPE_BIB,
     * ]);
     *
     */
    private function handlePublicationUpload(string $tmpFile, array $attributes): void {
        // Pflichtprüfungen
        if (empty(trim($attributes[OWNER_ID_KEY] ?? ''))) {
            throw new UserFormException('Owner ID is missing.', ERROR_MISSING_REQUIRED_PARAMETER);
        }
        if (empty(trim($attributes[FIELD_TARGETTABLE] ?? ''))) {
            throw new UserFormException('Target table is missing.', ERROR_MISSING_REQUIRED_PARAMETER);
        }

        // Wenn BibTeX-Datei, dann verarbeiten
        if (($attributes[FILE_TYPE_KEY] ?? '') === FILE_TYPE_BIB) {
            $this->processUpload($tmpFile, $attributes);
        }
    }

    /**
     * Read the BibTeX file, parse its entries, and insert each into the database.
     *
     * This function:
     *   1. Verifies file existence and readability.
     *   2. Reads the entire file content.
     *   3. Parses all BibTeX entries.
     *   4. Inserts each entry via insertPublication().
     *
     * @param string $filePath Path to the BibTeX file.
     * @param array $attributes Form attributes (see handlePublicationUpload()).
     *
     * @return void
     *
     * @example
     * processUpload('/tmp/uploaded.bib', [
     *     OWNER_ID_KEY      => 42,
     *     FIELD_TARGETTABLE => 'publications',
     * ]);
     */
    public function processUpload(string $filePath, array $attributes): void {
        if (!file_exists($filePath) || !is_readable($filePath)) {
            return;
        }

        $attributes[OWNER_ID_KEY] = $this->evaluate->parse($attributes[OWNER_ID_KEY]);

        $content = file_get_contents($filePath);
        $entries = $this->parseBibtex($content);

        $db = DatabaseManager::getInstance()->getDataDb();
        foreach ($entries as $entry) {
            $this->insertPublication($db, $entry, $attributes);
        }
    }

    /**
     * Parses the BibTeX text into an associative array of entries.
     *
     * Uses the regex:
     *   '/@(\w+)\s*\{\s*([^,]+),\s*(.*?)\s*\}(?=\s*@|\s*$)/s'
     *
     *  Brief explanation:
     *    - **(\w+)**: Captures the entry type (e.g., "article").
     *    - **\{\s*([^,]+),**: Matches the opening brace and captures the citation key (e.g., "smith2020") up to the first comma.
     *    - **(.*?)\s*\}**: Non-greedily captures the fields content (all key-value pairs) until the closing brace.
     *    - **(?=\s*@|\s*$)**: Ensures the match ends right before the next entry or at the end of the text.
     *
     * Regex Breakdown:
     *   - Group 1: Captures the entry type (e.g., "article").
     *   - Group 2: Captures the citation key (e.g., "smith2020").
     *   - Group 3: Captures the fields content.
     *
     * Example BibTeX text:
     *
     * @article{smith2020,
     *     title = {A Brief Title},
     *     author = {Smith, John},
     *     year = {2020}
     *   }
     *
     * In this example:
     *   - Group 1 will capture "article"
     *   - Group 2 will capture "smith2020"
     *   - Group 3 will capture:
     *       "title = {A Brief Title},
     *        author = {Smith, John},
     *        year = {2020}"
     *
     * Example structure of $matches (after applying preg_match_all):
     *
     *   Array
     *   (
     *       [0] => Array
     *           (
     *               [0] => "@article{smith2020,
     *                        title = {A Brief Title},
     *                        author = {Smith, John},
     *                        year = {2020}"
     *               [1] => "article"
     *               [2] => "smith2020"
     *               [3] => "title = {A Brief Title},
     *                        author = {Smith, John},
     *                        year = {2020}"
     *           )
     *   )
     *
     * Returns an array of entries, each being an associative array of field values.
     *
     * @param string $bibtexText The complete BibTeX text.
     * @return array Parsed entries.
     */
    private function parseBibtex(string $bibtexText): array {
        $pattern = '/@(\w+)\s*\{\s*([^,]+),\s*(.*?)\s*\}(?=\s*@|\s*$)/s';
        preg_match_all($pattern, $bibtexText, $matches, PREG_SET_ORDER);

        $result = [];
        foreach ($matches as $m) {
            $fields = $this->parseFields($m[3]);
            $fields[FIELD_TYPE] = strtolower($m[1]);
            $fields[FIELD_CITEKEY] = $m[2];
            $result[] = $fields;
        }
        return $result;
    }

    /**
     * Parses the fields from the BibTeX fields text.
     *
     * This function extracts field names and their corresponding values from a string
     * containing BibTeX field definitions. It also normalizes the field names (e.g., "Titel" becomes "titel")
     * and correctly extracts values that are wrapped in curly braces, double quotes, or given as numeric values.
     *
     * When processing the fields, the function uses PHP's trim() function to remove any leading or trailing whitespace.
     * This ensures that any extra spaces accidentally present in the input do not affect the keys or values.
     *
     * For example:
     *
     *   // Before using trim()
     *   $dirtyKey   = "  Titel  ";  // Contains extra spaces at the beginning and end.
     *   $dirtyValue = "  A Great Book  "; // Also contains extra spaces.
     *
     *   // After using trim()
     *   $cleanKey   = trim($dirtyKey);    // Results in "Titel"
     *   $cleanValue = trim($dirtyValue);   // Results in "A Great Book"
     *
     * These clean values are then normalized (the key is converted to lowercase) and stored in the result array.
     *
     * Example of a complete BibTeX publication entry (as processed by parseBibtex()):
     *
     * @article{doe2021,
     *     Titel = {A Great Book},
     *     Autor = "John Doe",
     *     Jahr = 1963,
     *     Verlag = {Fictional Press}
     *   }
     *
     * The fields text passed to this function is:
     *
     *   Titel = {A Great Book},
     *   Autor = "John Doe",
     *   Jahr = 1963,
     *   Verlag = {Fictional Press}
     *
     * Example of the "dirty" input array (i.e., keys before normalization):
     *
     *   Array
     *   (
     *       [Titel]  => A Great Book
     *       [Autor]  => John Doe
     *       [Jahr]   => 1963
     *       [Verlag] => Fictional Press
     *   )
     *
     * Resulting array after normalization (keys converted to lowercase and values trimmed):
     *
     *   Array
     *   (
     *       [titel]  => A Great Book
     *       [autor]  => John Doe
     *       [jahr]   => 1963
     *       [verlag] => Fictional Press
     *   )
     *
     * Additional examples for individual fields:
     *
     * 1. Field with a value in curly braces:
     *    - Input:  title = {A Great Book},
     *    - Result: Key "title" with value "A Great Book"
     *
     * 2. Field with a value in double quotes:
     *    - Input:  author = "John Doe",
     *    - Result: Key "author" with value "John Doe"
     *
     * 3. Field with a numeric value:
     *    - Input:  year = 1963,
     *    - Result: Key "year" with value "1963"
     *
     * Regex used :
     *   '/(\w+)\s*=\s*(?:\{([^}]+)\}|\"([^\"]+)\"|(\d+))\s*(?:,|$)/s'
     *
     *  Brief explanation:
     *  - **(\w+)**: Captures the field name (e.g., "Titel").
     *  - **\s*=\s***: Matches the equal sign, allowing for optional whitespace on either side.
     *  - **(?:\{([^}]+)\}|\"([^\"]+)\"|(\d+))**: Matches the field value, which can be:
     *      - Enclosed in curly braces (captured in group 2),
     *      - Enclosed in double quotes (captured in group 3), or
     *      - A numeric value (captured in group 4).
     *  - **\s*(?:,|$)**: Matches any trailing whitespace, followed by a comma or the end of the string.
     *
     * @param string $fieldsText The text containing all the fields of a BibTeX entry.
     * @return array Associative array where keys (in lowercase) are field names and values are the extracted contents.
     */
    private function parseFields(string $fieldsText): array {
        $pattern = '/(\w+)\s*=\s*(?:\{([^}]+)\}|"([^"]+)"|(\d+))\s*(?:,|$)/s';
        preg_match_all($pattern, $fieldsText, $matches, PREG_SET_ORDER);

        $out = [];
        foreach ($matches as $f) {
            $key = strtolower(trim($f[1]));
            $value = trim($f[2] ?? $f[3] ?? $f[4] ?? '');
            $out[$key] = $value;
        }
        return $out;
    }

    /**
     * Insert a single parsed BibTeX entry into the specified database table.
     *
     * Builds a parameter array in the predefined order of columns, then executes
     * an INSERT statement via the provided DB adapter.
     *
     * @param object $db Database adapter with a ->sql($query, $mode, $params) method.
     * @param array $pubEntry Parsed BibTeX entry (see parseBibtex()).
     * @param array $attributes Form attributes; must include:
     *                             - OWNER_ID_KEY
     *                             - FIELD_TARGETTABLE
     *
     * @return void
     *
     * @example
     * insertPublication($db, [
     *     'citeKey' => 'doe2019',
     *     'author'  => 'Doe, Jane',
     *     'title'   => 'Another Study',
     *     // ... other fields ...
     * ], [
     *     OWNER_ID_KEY      => 42,
     *     FIELD_TARGETTABLE => 'publications',
     * ]);
     */
    private function insertPublication($db, array $pubEntry, array $attributes): void {
        $orderedKeys = [
            'citeKey', 'author', 'title', 'journal', 'booktitle', 'publisher', 'address',
            'chapter', 'edition', 'editor', 'isbn', 'month', 'year', 'volume', 'number',
            'pages', 'series', 'abstract', 'doi', 'eprint', 'keywords', 'url', 'note',
            'organization', 'school', 'institution'
        ];

        // Aufbau des Parameters-Arrays:
        // 1. pIdOwner
        // 2. status (hier Standardwert 'new')
        // 3. anschließend die Werte aus $orderedKeys in exakt dieser Reihenfolge.
        $params = [];
        $params[] = $attributes[OWNER_ID_KEY];
        $params[] = $pubEntry['citeKey'] ?? '';

        foreach ($orderedKeys as $key) {
            $params[] = $pubEntry[$key] ?? ($key === 'year' ? 0 : '');
        }

        $table = $attributes[FIELD_TARGETTABLE];
        $sql = "INSERT INTO {$table} (
                pIdOwner,status,citationKey,author,title,detail,bookTitle,
                publisher,address,chapter,edition,editor,isbn,month,pubYear,
                volume,number,pages,series,abstractchapter,dxDoi,eprint,
                keywords,url,note,organization,school,institution
            ) VALUES (
                " . rtrim(str_repeat('?,', count($params)), ',') . "
            )";

        $db->sql($sql, ROW_REGULAR, $params);
    }


    /**
     * Excel Import
     *
     * @param $formElement
     * @param $fileName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    private function doImport($formElement, $fileName) {
        $importNamedSheetsOnly = array();

        Support::setIfNotSet($formElement, FE_IMPORT_TYPE, FE_IMPORT_TYPE_AUTO);

        if (!empty($formElement[FE_IMPORT_NAMED_SHEETS_ONLY])) {
            $importNamedSheetsOnly = explode(',', $formElement[FE_IMPORT_NAMED_SHEETS_ONLY]);
        }

        // Check for keywords which needs an explicit given document type.
        if ($formElement[FE_IMPORT_TYPE] === FE_IMPORT_TYPE_AUTO) {

            $list = [FE_IMPORT_LIST_SHEET_NAMES, FE_IMPORT_READ_DATA_ONLY, FE_IMPORT_LIST_SHEET_NAMES];
            foreach ($list as $token) {
                if (isset($formElement[$token])) {
                    throw new \UserFormException('If ' . $token .
                        ' is given, an explicit document type (like ' . FE_IMPORT_TYPE . '=xlsx) should be set.', ERROR_IMPORT_MISSING_EXPLICIT_TYPE);
                }
            }
        }

        switch ($formElement[FE_IMPORT_TYPE]) {
            case FE_IMPORT_TYPE_AUTO:
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);
                break;

            case FE_IMPORT_TYPE_XLS:
            case FE_IMPORT_TYPE_XLSX:
            case FE_IMPORT_TYPE_CSV:
            case FE_IMPORT_TYPE_ODS:
                $inputFileType = ucfirst($formElement[FE_IMPORT_TYPE]);
                $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

                // setReadDataOnly
                if (($formElement[FE_IMPORT_READ_DATA_ONLY] ?? '0') != '0') {
                    $reader->setReadDataOnly(true);
                }

                // setLoadSheetsOnly
                if (!empty ($importNamedSheetsOnly)) {
                    $reader->setLoadSheetsOnly($importNamedSheetsOnly);
                }

                // Debug option importListSheetNames=1 will list all recognized sheetnames and stops import.
                if (($formElement[FE_IMPORT_LIST_SHEET_NAMES] ?? '0') != '0') {
                    $sheetNames = $reader->listWorksheetNames($fileName);
                    throw new \UserFormException("Worksheets: " . implode(', ', $sheetNames),
                        ERROR_IMPORT_LIST_SHEET_NAMES);
                }

                $spreadsheet = $reader->load($fileName);
                break;

            default:
                throw new \UserFormException("Unknown Excel import type: '" . $formElement[FE_IMPORT_TYPE] . "'.",
                    ERROR_UNKNOWN_EXCEL_IMPORT_TYPE);
        }

        $tableName = $formElement[FE_IMPORT_TO_TABLE];
        $importMode = $formElement[FE_IMPORT_MODE] ?? FE_IMPORT_MODE_APPEND;
        $tableDefinition = $this->dbArray[$this->dbIndexData]->getTableDefinition($tableName);

        // Process based on import type
        if (($formElement[FE_IMPORT_FROM_COLUMNS] ?? '') != '' || ($formElement[FE_IMPORT_REGION] ?? '') == '') {
            // If importToColumns and importFromColumns are empty then try to assign columns by table column names. All table columns should map. Otherwise exception.
            // If importFromColumns is given and importToColumns is empty then try to assign columns by header names. Unmapped columns will be inserted in the order of the importFromColumns.
            // If importFromColumns and importToColumns are given then map those together. importFromColumns: 'col1,col2,col3' (excel columns) importToColumns: 'col3,col1,col2' (table columns) -> col1 -> col3, col2 -> col1, col3 -> col2 (replaces importColumnMapping list).
            $this->importWithColumnHeaders($spreadsheet, $formElement, $tableName, $importMode, $tableDefinition);
        } else {
            $this->importWithRegions($spreadsheet, $formElement, $tableName, $importMode, $tableDefinition);
        }
    }

    /**
     * Import data from a spreadsheet based on header defined in the excel file.
     *
     * @param $spreadsheet
     * @param $formElement
     * @param $tableName
     * @param $importMode
     * @param $tableDefinition
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function importWithColumnHeaders($spreadsheet, $formElement, $tableName, $importMode, $tableDefinition): void {
        $mode = EXCEL_IMPORT_FROM_COLUMN_MODE_ONLY;

        // Parse column names from importFromColumns
        $importFromColumns = [];
        $formElement[FE_IMPORT_FROM_COLUMNS] = $this->evaluate->parse($formElement[FE_IMPORT_FROM_COLUMNS] ?? '');
        if ($formElement[FE_IMPORT_FROM_COLUMNS] != '') {
            $importFromColumns = OnArray::trimArray(explode(',', $formElement[FE_IMPORT_FROM_COLUMNS]));
        }

        // Parse table column names from importToColumns if provided
        $importToColumns = [];
        if (($formElement[FE_IMPORT_TO_COLUMNS] ?? '') != '') {
            $formElement[FE_IMPORT_TO_COLUMNS] = $this->evaluate->parse($formElement[FE_IMPORT_TO_COLUMNS]);
            $importToColumns = OnArray::trimArray(explode(',', $formElement[FE_IMPORT_TO_COLUMNS]));
        }

        $tableColumnNames = array();
        foreach ($tableDefinition as $column) {
            if ($column[COLUMN_FIELD] !== COLUMN_ID) {
                $tableColumnNames[] = $column[COLUMN_FIELD];
            }
        }

        // Find the worksheet and headerRow first
        $worksheet = null;
        $originalFromColumns = $importFromColumns;

        // First value from importFromColumns might be a tab name, let's check
        $firstValue = $importFromColumns[0] ?? '';
        $isTabName = false;

        // In case of importFromColumns and importToColumns are empty, assign columns by table column names.
        if (empty($importFromColumns) && empty($importToColumns)) {
            $importFromColumns = $tableColumnNames;
            $originalFromColumns = $tableColumnNames;
            $mode = EXCEL_IMPORT_FROM_COLUMN_MODE_SIMPLE;
        } elseif (!empty($importToColumns)) {
            $mode = EXCEL_IMPORT_FROM_COLUMN_MODE_BOTH;
        }

        // Check if firstValue is a valid sheet name or index
        if (is_numeric($firstValue)) {
            // Check if it's a valid sheet index
            if ($firstValue > 0 && $firstValue <= $spreadsheet->getSheetCount()) {
                $tab = $firstValue;
                // Remove the tab from columnNames
                array_shift($importFromColumns);
                $isTabName = true;

                try {
                    $worksheet = $spreadsheet->getSheet($tab - 1);
                } catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
                    throw new \UserFormException($e->getMessage());
                }
            }
        } else {
            // Check if it's a valid sheet name
            try {
                $sheet = $spreadsheet->getSheetByName($firstValue);
                if ($sheet !== null) {
                    $tab = $firstValue;
                    // Remove the tab from columnNames
                    array_shift($importFromColumns);
                    $isTabName = true;
                    $worksheet = $sheet;
                }
            } catch (\Exception $e) {
            }
        }

        // If first value was not a valid tab name, search for the row with most column header matching in all sheets
        if (!$isTabName) {
            // Determine which headers to look for in excel file
            if (empty($importFromColumns)) {
                throw new \UserFormException("No columns found in table '$tableName' to use as headers.");
            }

            // Variables to track best match
            $bestMatchSheet = null;
            $bestMatchCount = 0;

            // Iterate through all sheets in the workbook
            for ($sheetIndex = 0; $sheetIndex < $spreadsheet->getSheetCount(); $sheetIndex++) {
                $currentSheet = $spreadsheet->getSheet($sheetIndex);
                $highestColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($currentSheet->getHighestColumn());
                // Only check first 20 rows
                $highestRow = min($currentSheet->getHighestRow(), 20);

                // Check each row for header matches
                for ($row = 1; $row <= $highestRow; $row++) {
                    $matchCount = 0;

                    // Check all columns in this row
                    for ($col = 1; $col <= $highestColumn; $col++) {
                        $cellValue = $currentSheet->getCellByColumnAndRow($col, $row)->getValue();
                        if (in_array($cellValue, $importFromColumns)) {
                            $matchCount++;
                        }
                    }

                    // If this row has more matches than our current best, update the best match
                    if ($matchCount > $bestMatchCount) {
                        $bestMatchCount = $matchCount;
                        $bestMatchSheet = $currentSheet;
                    }
                }
            }

            // Check if we found any matches
            if ($bestMatchCount == 0) {
                throw new \UserFormException("Could not find any of the specified headers in any sheet of the workbook.");
            }

            // Use the sheet and row with the best match
            $worksheet = $bestMatchSheet;
        }

        // Find the header row of selected sheet with the most matches
        $highestColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($worksheet->getHighestColumn());
        $highestRow = $worksheet->getHighestRow();

        // First pass inside a worksheet: find the row with the most headers
        $rowHeaderCounts = array();
        // Only check first 20 rows for headers
        for ($row = 1; $row <= min($highestRow, 20); $row++) {
            for ($col = 1; $col <= $highestColumn; $col++) {
                $cellValue = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
                if (in_array($cellValue, $importFromColumns)) {
                    if (!isset($rowHeaderCounts[$row])) {
                        $rowHeaderCounts[$row] = 0;
                    }

                    $rowHeaderCounts[$row]++;
                }
                // If all headers are found, break early
                if (count($rowHeaderCounts) == count($importFromColumns)) {
                    break 2;
                }
            }
        }

        // Sort rows by header count (descending)
        arsort($rowHeaderCounts);
        if (reset($rowHeaderCounts) > 0) {
            // Row with the most headers
            $headerRow = key($rowHeaderCounts);
        } else {
            throw new \UserFormException("Could not find any of the specified headers in the first 20 rows.");
        }

        // First, find all header positions in the Excel file
        // [header => [column_positions]]
        $excelHeadersMap = array();
        for ($col = 1; $col <= $highestColumn; $col++) {
            $cellValue = $worksheet->getCellByColumnAndRow($col, $headerRow)->getValue();
            if (in_array($cellValue, $originalFromColumns)) {
                if (!isset($excelHeadersMap[$cellValue])) {
                    $excelHeadersMap[$cellValue] = [];
                }
                $excelHeadersMap[$cellValue][] = $col;
            }
        }

        // Count occurrences to handle duplicates
        // Track which occurrence we're on for each header
        $headerOccurrenceCounts = array();

        // Create a mapping that preserves the original order from FE_IMPORT_FROM_COLUMNS
        // Will hold [original_index => [excel_col, header_name, occurrence]]
        $orderedColumnMap = [];

        foreach ($originalFromColumns as $index => $headerName) {
            // Skip tab name if it was the first element
            if ($index === 0 && $isTabName) {
                continue;
            }

            // Initialize occurrence counter if not set
            if (!isset($headerOccurrenceCounts[$headerName])) {
                $headerOccurrenceCounts[$headerName] = 0;
            }

            // Check if this header exists in the Excel file
            if (isset($excelHeadersMap[$headerName])) {
                $occurrence = $headerOccurrenceCounts[$headerName]++;

                // Check if we have this occurrence in the file
                if (isset($excelHeadersMap[$headerName][$occurrence])) {
                    $excelCol = $excelHeadersMap[$headerName][$occurrence];
                    $orderedColumnMap[] = [
                        EXCEL_COL => $excelCol,
                        EXCEL_HEADER_NAME => $headerName,
                        EXCEL_OCCURRENCE => $occurrence
                    ];
                }
            } else {
                // Header not found in the file
                throw new \UserFormException("Header '$headerName' specified in importFromColumns could not be found in the worksheet.");
            }
        }

        // Now handle the mapping to table columns
        $tableColumns = [];
        foreach ($tableDefinition as $column) {
            if ($column[COLUMN_FIELD] !== COLUMN_ID) {
                $tableColumns[] = $column[COLUMN_FIELD];
            }
        }

        // Will store [original_index => table_column]
        $columnMapping = [];

        switch ($mode) {
            case EXCEL_IMPORT_FROM_COLUMN_MODE_BOTH:
                // Case 1: Both importFromColumns and importToColumns are provided
                // Map them directly to each other

                // Skip tab name if it was specified in importFromColumns
                $startIndex = $isTabName ? 1 : 0;
                $fromColumns = array_slice($originalFromColumns, $startIndex);

                // Verify we have the same number of columns in both arrays
                if (count($fromColumns) != count($importToColumns)) {
                    throw new \UserFormException("The number of columns in importFromColumns and importToColumns must match.");
                }

                // Create a lookup from header name+occurrence to table column
                // For example there is a column named 'Name' in the Excel file, but it appears twice in same row
                $headerToTableMap = [];
                $headerOccurrenceCounts = [];

                for ($i = 0; $i < count($fromColumns); $i++) {
                    $excelHeader = $fromColumns[$i];
                    $tableColumn = $importToColumns[$i];

                    if (!isset($headerOccurrenceCounts[$excelHeader])) {
                        $headerOccurrenceCounts[$excelHeader] = 0;
                    }

                    $occurrence = $headerOccurrenceCounts[$excelHeader]++;
                    $headerToTableMap[$excelHeader . '_' . $occurrence] = $tableColumn;
                }

                // Now map each ordered column to its table column
                foreach ($orderedColumnMap as $index => $columnInfo) {
                    $headerKey = $columnInfo[EXCEL_HEADER_NAME] . '_' . $columnInfo[EXCEL_OCCURRENCE];
                    if (isset($headerToTableMap[$headerKey])) {
                        $columnMapping[$index] = $headerToTableMap[$headerKey];
                    }
                }
                break;

            case EXCEL_IMPORT_FROM_COLUMN_MODE_ONLY:
                // Case 2: importFromColumns is provided but importToColumns is empty
                // First try direct name matching, then use positional mapping as a fallback

                $processedHeaders = [];
                $directMappedIndices = [];

                // First pass - direct name matching
                foreach ($orderedColumnMap as $index => $columnInfo) {
                    $headerName = $columnInfo[EXCEL_HEADER_NAME];

                    // Only apply direct matching for the first occurrence of each header
                    if (!isset($processedHeaders[$headerName]) && in_array($headerName, $tableColumns)) {
                        $processedHeaders[$headerName] = true;
                        $columnMapping[$index] = $headerName;
                        $directMappedIndices[] = $index;
                    }
                }

                // Second pass - positional mapping for remainder
                $availableTableColumns = array_diff($tableColumns, array_values($columnMapping));
                $availableTableColumns = array_values($availableTableColumns);

                $unmappedIndices = array_diff(array_keys($orderedColumnMap), $directMappedIndices);
                // Ensure we process in order
                sort($unmappedIndices);

                $tableColIndex = 0;
                foreach ($unmappedIndices as $index) {
                    if ($tableColIndex < count($availableTableColumns)) {
                        $columnMapping[$index] = $availableTableColumns[$tableColIndex++];
                    }
                }
                break;

            case EXCEL_IMPORT_FROM_COLUMN_MODE_SIMPLE:
                // Case 3: Both importFromColumns and importToColumns are empty
                // All table column names must match excel columns exactly
                // If any table column is not found in excel columns, throw exception

                $unmappedHeaders = [];

                foreach ($orderedColumnMap as $index => $columnInfo) {
                    $headerName = $columnInfo[EXCEL_HEADER_NAME];

                    if (in_array($headerName, $tableColumns)) {
                        $columnMapping[$index] = $headerName;
                    } else {
                        $unmappedHeaders[] = $headerName;
                    }
                }

                // If any headers couldn't be mapped, throw exception
                if (!empty($unmappedHeaders)) {
                    throw new \UserFormException("The following table columns do not match any excel header: " .
                        implode(", ", array_unique($unmappedHeaders)) .
                        ". Please provide explicit column mapping via importToColumns.");
                }
                break;
        }

        // Read data from Excel and prepare for import
        if ($importMode === FE_IMPORT_MODE_REPLACE) {
            $this->dbArray[$this->dbIndexData]->sql("TRUNCATE $tableName");
            $importMode = FE_IMPORT_MODE_APPEND;
        }

        // Determine data range - find min and max columns we need to read
        $excelColumns = array_column($orderedColumnMap, EXCEL_COL);
        $columnStart = min($excelColumns);
        $columnEnd = max($excelColumns);
        $rowStart = $headerRow + 1;

        // Read the worksheet data
        $rangeStr = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnStart) . $rowStart . ':' .
            \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnEnd) . $highestRow;
        $worksheetData = $worksheet->rangeToArray($rangeStr, '', true, false);

        // Prepare the insert columns and data mapping
        $insertColumns = [];
        // Maps from Excel column index to position in insert data
        $columnIndexMapping = [];

        foreach ($orderedColumnMap as $index => $columnInfo) {
            if (isset($columnMapping[$index])) {
                $tableColumn = $columnMapping[$index];
                $excelCol = $columnInfo[EXCEL_COL];
                $excelColIndex = $excelCol - $columnStart;
                $insertColumns[] = $tableColumn;
                $columnIndexMapping[$excelColIndex] = count($columnIndexMapping);
            }
        }

        // Insert the data
        $columnList = '`' . implode('`,`', $insertColumns) . '`';
        $paramPlaceholders = str_repeat('?,', count($insertColumns) - 1) . '?';

        foreach ($worksheetData as $rowData) {
            // Reorganize the data according to the mapping
            $insertData = array_fill(0, count($insertColumns), '');

            foreach ($columnIndexMapping as $excelIndex => $insertIndex) {
                if (isset($rowData[$excelIndex])) {
                    $insertData[$insertIndex] = $rowData[$excelIndex];
                }
            }

            $insertSql = "INSERT INTO `$tableName` ($columnList) VALUES ($paramPlaceholders)";
            $this->dbArray[$this->dbIndexData]->sql($insertSql, ROW_REGULAR, $insertData);
        }
    }

    /**
     * Import data from given Excel regions
     * Approach with given regions
     *
     * @param $spreadsheet
     * @param $formElement
     * @param $tableName
     * @param $importMode
     * @param $tableDefinition
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function importWithRegions($spreadsheet, $formElement, $tableName, $importMode, $tableDefinition): void {
        $regions = OnArray::trimArray(explode('|', $formElement[FE_IMPORT_REGION] ?? ''));
        $columnNames = OnArray::trimArray(explode(',', $formElement[FE_IMPORT_TO_COLUMNS] ?? ''));
        $excelStats = array();

        // Get existing primary key id for later update statement
        $existingId = false;
        foreach ($tableDefinition as $column) {
            if ($column[COLUMN_FIELD] === COLUMN_ID) {
                $existingId = true;
            }
        }

        // Check for exceeding columns
        if ($existingId) {
            $availableColumns = count($tableDefinition) - 1;
        } else {
            $availableColumns = count($tableDefinition);
        }

        // Initialize import statistics
        $excelStats = $this->setExcelStats($excelStats);

        foreach ($regions as $region) {
            // region: tab, startColumn, startRow, endColumn, endRow
            $region = OnArray::trimArray(explode(',', $region));
            $tab = 1;
            if (!empty($region[0])) {
                $tab = $region[0];
            }

            try {
                if (is_numeric($tab)) {
                    $worksheet = $spreadsheet->getSheet($tab - 1); // 0-based
                } else {
                    $worksheet = $spreadsheet->getSheetByName($tab);
                    if ($worksheet === null) {
                        throw new \PhpOffice\PhpSpreadsheet\Exception(
                            "No sheet with the name '$tab' could be found."
                        );
                    }
                }
            } catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
                throw new \UserFormException($e->getMessage());
            }

            // Set up requested region
            $columnStart = '1';
            $columnEnd = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($worksheet->getHighestColumn());
            $rowStart = 1;
            $rowEnd = $worksheet->getHighestRow();
            if (!empty($region[1])) { // startColumn
                if (!is_numeric($region[1])) $region[1] = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($region[1]);
                if ($region[1] >= $columnStart && $region[1] <= $columnEnd) {
                    $columnStart = $region[1];
                }
            }
            if (!empty($region[3])) { // endColumn
                if (!is_numeric($region[3])) $region[3] = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($region[3]);
                if ($region[3] >= $columnStart && $region[3] <= $columnEnd) {
                    $columnEnd = $region[3];
                }
            }
            if (!empty($region[2]) && $region[2] >= $rowStart && $region[2] <= $rowEnd) {
                $rowStart = $region[2];
            }
            if (!empty($region[4]) && $region[4] >= $rowStart && $region[4] <= $rowEnd) {
                $rowEnd = $region[4];
            }
            if (!empty($region[5]) && $excelStats[FE_IMPORT_FLAG_INSERTED]) {
                if ($region[5] === FE_IMPORT_REGION_APPEND) {
                    // Reset stats for append mode
                    $excelStats = $this->setExcelStats($excelStats);
                }
            }

            // Read the specified region
            $rangeStr = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnStart) . $rowStart . ':' .
                \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnEnd) . $rowEnd;
            $worksheetData = $worksheet->rangeToArray($rangeStr, '', true, false);

            $columnDefinitionArr = [];
            $columnListArr = [];
            // get right table columns
            for ($column = $columnStart; $column <= $columnEnd; ++$column) {
                if (!empty($columnNames[$column - $columnStart]) && !$excelStats[FE_IMPORT_REGION_JOIN]) {
                    $columnName = $columnNames[$column - $columnStart];
                } else if (!empty($columnNames[$excelStats[FE_IMPORT_COLUMN_COUNT]])) {
                    $columnName = $columnNames[$excelStats[FE_IMPORT_COLUMN_COUNT]];
                } else {
                    $columnName = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($column);
                }

                // Throw exception if actual column count exceeds maximal available column names
                if ($excelStats[FE_IMPORT_COLUMN_COUNT] + 1 > $availableColumns) {
                    throw new \UserFormException(
                        'Excel columns exceeds amount from table. More columns from excel given than available in table.',
                        ERROR_EXCEL_JOIN_EXCEEDS_TABLE
                    );
                }
                $excelStats[FE_IMPORT_COLUMN_COUNT]++;
                $columnDefinitionArr[] = "`$columnName`   TEXT       NOT NULL  DEFAULT ''";
                $columnListArr[] = "$columnName";
            }

            // SQL time!
            $createTableSql = "CREATE TABLE IF NOT EXISTS `$tableName` (" .
                "`id`        INT(11)    NOT NULL  AUTO_INCREMENT," .
                implode(', ', $columnDefinitionArr) . ',' .
                "`modified`  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," .
                "`created`   DATETIME   NOT NULL  DEFAULT CURRENT_TIMESTAMP," .
                "PRIMARY KEY (`id`) )" .
                "ENGINE = InnoDB  DEFAULT CHARSET = utf8 AUTO_INCREMENT = 0;";
            $this->dbArray[$this->dbIndexData]->sql($createTableSql);

            if ($importMode === FE_IMPORT_MODE_REPLACE) {
                $this->dbArray[$this->dbIndexData]->sql("TRUNCATE $tableName");
                $importMode = FE_IMPORT_MODE_APPEND;
            }

            // Import the data
            if (!$excelStats[FE_IMPORT_FLAG_INSERTED] || !$excelStats[FE_IMPORT_REGION_JOIN]) {
                foreach ($worksheetData as $rowIndex => $row) {
                    $columnList = '`' . implode('`,`', $columnListArr) . '`';
                    $paramPlaceholders = str_repeat('?,', count($worksheetData[0]) - 1) . '?';
                    $insertSql = "INSERT INTO `$tableName` ($columnList) VALUES ($paramPlaceholders)";
                    $this->dbArray[$this->dbIndexData]->sql($insertSql, ROW_REGULAR, $row);
                    $excelStats[FE_IMPORT_INSERTED_IDS][] = $this->dbArray[$this->dbIndexData]->getLastInsertId();
                    if ($excelStats[FE_IMPORT_FIRST_INSERTED_ID] === null) {
                        $excelStats[FE_IMPORT_FIRST_INSERTED_ID] = $this->dbArray[$this->dbIndexData]->getLastInsertId();
                        $excelStats[FE_IMPORT_START_ID_COUNT] = array_search($excelStats[FE_IMPORT_FIRST_INSERTED_ID], $excelStats[FE_IMPORT_INSERTED_IDS]);
                    }
                }
                $excelStats[FE_IMPORT_FLAG_INSERTED] = true;
            } else {
                if (!$existingId) {
                    throw new \UserFormException(
                        'No primary key "id" found in table. Is needed to join previous import region. No primary key id found in table',
                        ERROR_EXCEL_NO_PRIMARY_KEY_ID
                    );
                }
                foreach ($worksheetData as $row) {
                    $columnParams = implode('=?,', $columnListArr) . '=?';
                    $updateSql = "UPDATE $tableName SET $columnParams WHERE  id=?";
                    $row[] = $excelStats[FE_IMPORT_INSERTED_IDS][$excelStats[FE_IMPORT_START_ID_COUNT]];
                    $this->dbArray[$this->dbIndexData]->sql($updateSql, ROW_REGULAR, $row);
                    $excelStats[FE_IMPORT_START_ID_COUNT]++;
                }
                $excelStats[FE_IMPORT_START_ID_COUNT] = 0;
            }
            $excelStats[FE_IMPORT_REGION_JOIN] = true;
        }
    }

    /**
     * Set all needed stats for Excel import. If exists, reset 'sameRow', 'columnCount' and 'firstInsertedId'.
     *
     * @param string $excelStats
     * @return array
     */
    private function setExcelStats($excelStats = array()): array {
        if (empty($excelStats)) {
            $excelStats = array(
                FE_IMPORT_COLUMN_COUNT => 0,
                FE_IMPORT_START_ID_COUNT => 0,
                FE_IMPORT_INSERTED_IDS => array(),
                FE_IMPORT_FLAG_INSERTED => false,
                FE_IMPORT_REGION_JOIN => true,
                FE_IMPORT_FIRST_INSERTED_ID => null
            );
        } else {
            $excelStats[FE_IMPORT_REGION_JOIN] = false;
            $excelStats[FE_IMPORT_COLUMN_COUNT] = 0;
            $excelStats[FE_IMPORT_FIRST_INSERTED_ID] = null;
        }

        return $excelStats;
    }

    /**
     * Copy uploaded file from temporary location to final location.
     *
     * Check also: Documentation-develop/CODING.md
     *
     * @param array $formElement
     * @param array $statusUpload
     * @return array|mixed|null|string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function copyUploadFile(array $formElement, array $statusUpload) {
        $pathFileName = '';

        $fileNameToLower = $this->store->getVar(SYSTEM_FILE_NAME_TO_LOWER, STORE_SYSTEM) == '1';

        if (isset($this->formSpec[SYSTEM_FILE_NAME_TO_LOWER])) {
            $fileNameToLower = $this->formSpec[SYSTEM_FILE_NAME_TO_LOWER] != '0';
        }

        if (isset($formElement[SYSTEM_FILE_NAME_TO_LOWER])) {
            $fileNameToLower = $formElement[SYSTEM_FILE_NAME_TO_LOWER] != '0';
        }


        if (!isset($statusUpload[FILES_TMP_NAME]) || $statusUpload[FILES_TMP_NAME] === '') {
            // nothing to upload: e.g. user has deleted a previous uploaded file.
            return '';
        }
        // fileNameToLower
        $srcFile = Support::extendFilename($statusUpload[FILES_TMP_NAME], UPLOAD_CACHED);

        if (isset($formElement[FE_FILE_DESTINATION])) {

            // Provide variable 'filename'. Might be substituted in $formElement[FE_PATH_FILE_NAME].
            $origFilename = Sanitize::safeFilename($statusUpload[FILES_NAME], false, false, $fileNameToLower);
            $this->store->appendToStore(HelperFile::pathinfo($origFilename), STORE_VAR);
            $pathFileName = $this->evaluate->parse($formElement[FE_FILE_DESTINATION]);

            // Unique Path File name has to be evaluated after file Path has been evaluated since SQL queries can be used.
            $this->store->appendToStore(HelperFile::base64EncodeUniqueFileName($pathFileName, $origFilename), STORE_VAR);
            $pathFileName = $this->evaluate->parse($pathFileName);

            $pathFileName = Sanitize::safeFilename($pathFileName, false, true); // Dynamically calculated pathFileName might contain invalid characters.

            // Saved in store for later use during 'Advanced Upload'-post processing
            $this->store->setVar(VAR_FILE_DESTINATION, $pathFileName, STORE_VAR);
        }

        if ($pathFileName === '') {
            throw new \UserFormException("Upload failed, no target '" . FE_FILE_DESTINATION . "' specified.", ERROR_NO_TARGET_PATH_FILE_NAME);
        }

        // If given, get chmodDir. Needs to be prefixed with a 0 (=octal) - it should not be quoted! Symbolic mode is not allowed. E.g.: 0660, or 01777
        if (empty($formElement[FE_FILE_CHMOD_DIR])) {
            $chmodDir = false;
        } else {
            $chmodDir = octdec($formElement[FE_FILE_CHMOD_DIR]);
        }

        $overwrite = isset($formElement[FE_FILE_REPLACE_MODE]) && $formElement[FE_FILE_REPLACE_MODE] == FE_FILE_REPLACE_MODE_ALWAYS;
        Support::moveFile($srcFile, $pathFileName, $overwrite, $chmodDir);

        // get chmodFile
        if (empty($formElement[FE_FILE_CHMOD_FILE])) {
            $chmodFile = false;
        } else {
            $chmodFile = octdec($formElement[FE_FILE_CHMOD_FILE]);
        }

        if (1) {
            // HEIC conversion?
            if (strpos($statusUpload['type'] ?? '', 'image/heic') !== false) {
                $cmd = $this->store->getVar(SYSTEM_CMD_HEIF_CONVERT, STORE_SYSTEM);
                if (!empty($cmd)) {
                    exec("$cmd -q 100 $pathFileName $pathFileName.png", $output, $return_var);
                    if ($return_var == 0) {
                        HelperFile::unlink($pathFileName);
                        $pathFileName .= '.png';
                        $statusUpload['type'] = 'image/png';
                        $statusUpload['name'] .= '.png';
                    } else {
                        $msg = error_get_last();
                        throw new \UserFormException(
                            json_encode([ERROR_MESSAGE_TO_USER => 'Upload: HEIC conversion failed', ERROR_MESSAGE_TO_DEVELOPER => $msg . '\nFile: ' . $pathFileName]),
                            ERROR_UPLOAD_FILE_TYPE);
                    }
                }
                // Update STORE_VAR
                $this->store->appendToStore(HelperFile::pathinfo($pathFileName), STORE_VAR);
                $this->store->setVar(VAR_FILE_DESTINATION, $pathFileName, STORE_VAR);
            }
        }

        $this->autoOrient($formElement, $pathFileName);
        HelperFile::chmod($pathFileName, $chmodFile);

        $this->splitUpload($formElement, $pathFileName, $chmodFile, $statusUpload);

        return $pathFileName;
    }

    /**
     * If fe['autoOrient'] is given and the MimeType corresponds to fe['autoOrientMimeType']: the given {{pathFileName:V}} will be converted.
     * ImageMagick 'convert' seems to do a better job than GraficsMagick (Orientation is stable even if multiple times applied).
     *
     * @param array $formElement
     * @param $pathFileName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function autoOrient(array $formElement, $pathFileName) {

        // 'autoOrient' wished?
        if (!isset($formElement[FE_FILE_AUTO_ORIENT]) || $formElement[FE_FILE_AUTO_ORIENT] == '0') {
            return; // No
        }

        // Upload has matching MimeType?
        $mimeTypeList = empty($formElement[FE_FILE_AUTO_ORIENT_MIME_TYPE]) ? 'image/jpeg,image/png,image/tiff' : $formElement[FE_FILE_AUTO_ORIENT_MIME_TYPE];
        if (!HelperFile::checkFileType($pathFileName, $pathFileName, $mimeTypeList)) {
            return;
        }

        // Get 'autoOrient' command
        $cmd = empty($formElement[FE_FILE_AUTO_ORIENT_CMD]) ? FE_FILE_AUTO_ORIENT_CMD_DEFAULT : $formElement[FE_FILE_AUTO_ORIENT_CMD];
        $cmd = $this->evaluate->parse($cmd);

        // Do 'autoOrient' command
        $output = Support::qfqExec($cmd, $rc);
        if ($rc != 0) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => 'copy failed', ERROR_MESSAGE_TO_DEVELOPER => "[cmd=$cmd]$output"]), ERROR_IO_COPY);
        }
    }

    /**
     * Check's if the file $pathFileName should be split'ed in one file per PDF page. If no: do nothing and return.
     * The only possible split target file format is 'svg': fileSplit=svg.
     * The split'ed files will be saved under fileDestinationSplit=some/path/to/file.%02d.svg. A printf style token,
     * like '%02d', is needed to create distinguished filename's. See 'man pdf2svg' for further details.
     * For every created file, a record in table 'Split' is created (see splitSvg() ), storing the pathFileName of the
     * current page/file.
     *
     * @param array $formElement
     * @param string $pathFileName
     * @param int $chmod
     * @param array $statusUpload
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function splitUpload(array $formElement, $pathFileName, $chmod, array $statusUpload) {


        // Should the file be split?
        if (empty($formElement[FE_FILE_SPLIT])) {
            return;
        }

        // Is it technically possible to split the file?
        // BTW: the $statusUpload['type'] delivers 'octetstream' for PDF files who do not have the extension '.pdf' - therefore get mimetype again.
        $mime = HelperFile::getMimeType($pathFileName);
        if (false === strpos($mime, MIME_TYPE_SPLIT_CAPABLE) && $statusUpload[FILES_TYPE] != MIME_TYPE_SPLIT_CAPABLE) {
            return;
        }

        $fileDestinationSplit = $this->evaluate->parse($formElement[FE_FILE_DESTINATION_SPLIT] ?? '');
        $fileSplitType = $this->evaluate->parse($formElement[FE_FILE_SPLIT] ?? '');
        $fileSplitTypeOptions = $this->evaluate->parse($formElement[FE_FILE_SPLIT_OPTIONS] ?? '');
        $fileSplitTableName = $this->evaluate->parse($formElement[FE_FILE_SPLIT_TABLE_NAME] ?? '');

        if (empty($fileSplitTableName)) {
            $fileSplitTableName = $this->formSpec[F_TABLE_NAME];
        }

        if ($fileDestinationSplit == '') {
            $ext = ($fileSplitType == FE_FILE_SPLIT_SVG) ? '.%02d.svg' : '.jpg';
            $fileDestinationSplit = $pathFileName . '.split/split' . $ext;
        }

        HelperFile::mkDirParent($fileDestinationSplit);

        // Save CWD
        $cwd = getcwd();

        // Create temporary directory
        $tempDir = HelperFile::mktempdir();
        $newSrc = $tempDir . DIRECTORY_SEPARATOR . QFQ_TEMP_SOURCE;
        HelperFile::copy($pathFileName, $newSrc);

        // Split destination.
        $pathParts = pathinfo($fileDestinationSplit);
        if (empty($pathParts['filename']) || empty($pathParts['basename'])) {
            throw new \UserFormException('Missing filename in ' . FE_FILE_DESTINATION_SPLIT, ERROR_MISSING_FILE_NAME);
        }

        // Extract filename from destination directory.
        $fileNameDest = $pathParts['basename'];

        switch ($fileSplitType) {
            case FE_FILE_SPLIT_SVG:
                $cmd = $this->buildPdf2svg($newSrc, $fileNameDest);
                break;
            case FE_FILE_SPLIT_JPEG:
                $cmd = $this->buildConvertSplit($newSrc, $fileNameDest, $fileSplitTypeOptions);
                break;
            default:
                throw new \UserFormException("Unknown 'fileSplit' type: " . $formElement[FE_FILE_SPLIT], ERROR_UNKNOWN_TOKEN);
        }

        // Split PDF
        HelperFile::chdir($tempDir);
        $cnt = 0;
        do {
            $output = Support::qfqExec($cmd, $rc);
            $cnt++;

            if ($rc != 0) {
                // Split failed
                $cmdRepair = $this->buildPdftocairo($newSrc, $newSrc . '.1');
                $output .= PHP_EOL . $cmdRepair . PHP_EOL;
                $output .= Support::qfqExec($cmdRepair, $rc1);
                if ($rc1 != 0) {
                    // Repair failed too: stop here.
                    break;
                }
                HelperFile::rename($newSrc . '.1', $newSrc);
            }
        } while ($rc != 0 && $cnt < 2);

        HelperFile::chdir($cwd);

        if ($rc != 0) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'pdf split failed', ERROR_MESSAGE_TO_DEVELOPER => "[$cwd][cmd=$cmd]$output"]),
                ERROR_PDF_SPLIT);
        }

        $files = Helperfile::getSplitFileNames($tempDir);

        $xId = $this->store->getVar(COLUMN_ID, STORE_RECORD);

        // Clean optional existing old split records and files from further uploads.
        $this->dbArray[$this->dbIndexData]->deleteSplitFileAndRecord($xId, $fileSplitTableName);

        // IM 'convert' will produce files <file>-1.jpg <file>-10.jpg ... - bring them in natural sort order
        natsort($files);

        // Create DB records according to the extracted filenames.
        $tableName = TABLE_NAME_SPLIT;
        $sql = "INSERT INTO `$tableName` (`tableName`, `xId`, `pathFilename`) VALUES (?,?,?)";

        // 1) Move split files to final location. 2) Created records to reference each split file.
        foreach ($files as $file) {

            if ($file == '.' || $file == '..' || $file == QFQ_TEMP_SOURCE) {
                continue;
            }

            if (!empty($pathParts['dirname'])) {
                $fileDestination = $pathParts['dirname'] . '/' . $file;
            } else {
                $fileDestination = $file;
            }

            Support::moveFile($tempDir . DIRECTORY_SEPARATOR . $file, Support::joinPath($cwd, $fileDestination), true);
            HelperFile::chmod($fileDestination, $chmod);

            // Insert records.
            $this->dbArray[$this->dbIndexData]->sql($sql, ROW_REGULAR, [$fileSplitTableName, $xId, $fileDestination]);
        }

        // Remove duplicated source
        HelperFile::unlink($newSrc);

        // Remove empty directory
        HelperFile::rmdir($tempDir);
    }

    /**
     * @param $src
     * @param $dest
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function buildPdf2svg($src, $dest) {

        $cmd = $this->store->getVar(SYSTEM_CMD_PDF2SVG, STORE_SYSTEM);
        return "$cmd '$src' '$dest' all";
    }

    /**
     * @param $src
     * @param $dest
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function buildPdftocairo($src, $dest) {

        $cmd = $this->store->getVar(SYSTEM_CMD_PDFTOCAIRO, STORE_SYSTEM);
        return "$cmd -pdf '$src' '$dest'";
    }

    /**
     * @param $src
     * @param $dest
     * @param $options
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function buildConvertSplit($src, $dest, $options) {
        if ($options == '') {
            $options = FE_FILE_SPLIT_OPTIONS_JPEG;
        }
        $cmd = $this->store->getVar(SYSTEM_CMD_CONVERT, STORE_SYSTEM);
        return "$cmd $options '$src' '$dest'";
    }

    /**
     * Create/update or delete the slave record.
     *
     * @param array $fe
     * @param string $modeUpload UPLOAD_MODE_NEW|UPLOAD_MODE_DELETEOLD_NEW|UPLOAD_MODE_DELETEOLD
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function doUploadSlave(array $fe, $modeUpload, $fileNote) {
        $sql = '';
        $flagUpdateSlaveId = false;
        $flagSlaveDeleted = false;

        if (!isset($fe[FE_SLAVE_ID])) {
            throw new \UserFormException("Missing 'slaveId'-definition", ERROR_MISSING_SLAVE_ID_DEFINITION);
        }

        // Get the slaveId
        $slaveId = Support::falseEmptyToZero($this->evaluate->parse($fe[FE_SLAVE_ID]));
        // Store the slaveId: it's used and replaced in the update statement.
        $this->store->setVar(VAR_SLAVE_ID, $slaveId, STORE_VAR, true);

        $mode = ($slaveId == '0') ? 'I' : 'U'; // I=Insert, U=Update
        $mode .= ($modeUpload == UPLOAD_MODE_NEW || $modeUpload == UPLOAD_MODE_DELETEOLD_NEW) ? 'N' : ''; // N=New File, '' if no new file.
        $mode .= ($modeUpload == UPLOAD_MODE_DELETEOLD) ? 'D' : ''; // Delete slave record only if there is no new and not 'unchanged'.

        // fileNote
        $noteSql = '';
        $advancedNote = $fe[FE_FILE_NOTE_TARGET] ?? false;
        if ($advancedNote !== false) {
            $advancedNote = explode(':', $advancedNote);
            $noteSql = "UPDATE $advancedNote[0] SET $advancedNote[1] = ? WHERE id = ?";
        }

        switch ($mode) {
            case 'IN':
                $sql = $fe[FE_SQL_INSERT];
                $flagUpdateSlaveId = true;
                break;
            case 'UN':
                $sql = $fe[FE_SQL_UPDATE];
                break;
            case 'I':
            case 'U':
                $sql = ''; // no old file and no new file.
                break;
            case 'UD':
                $sql = $fe[FE_SQL_DELETE];
                $flagSlaveDeleted = true;
                break;
            default:
                throw new \CodeException('Unknown mode: ' . $mode, ERROR_UNKNOWN_MODE);
        }

        $rc = $this->evaluate->parse($sql);
        // Check if the slave record has been deleted: if yes, set slaveId=0
        if ($flagSlaveDeleted && $rc > 0) {
            $rc = 0;
            $flagUpdateSlaveId = true;
        }

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

        if ($slaveId > 0) {
            // Update if no Delete
            if ($noteSql != '') {
                $this->dbArray[$this->dbIndexData]->sql($noteSql, ROW_REGULAR, [$fileNote, $slaveId]);
            }
        }

        return $slaveId;
    }

    /**
     * Remove not allowed tags.
     *
     * @param array $html_str
     * @param string $allowedTags
     * @param string $allowedAttributes
     * @return string
     */
    function custom_strip_tags($html, string $allowedTags) {
        $allowed_tags = explode(',', $allowedTags);
        $allowed_tags = array_map('strtolower', $allowed_tags);
        $regex_tags = '/<\/?([^>\s]+)[^>]*>/i';
        $matches = array();
        preg_match_all($regex_tags, $html, $matches);
        $rhtml = preg_replace_callback($regex_tags, function ($matches) use (&$allowed_tags) {
            return in_array(strtolower($matches[1]), $allowed_tags) ? $matches[0] : '';
        }, $html);
        return $rhtml;
    }

    /**
     * Remove not allowed attributes and content which is not in whitelist
     * Used in combination with htmlAllow.
     * Author:  Edward Z. Yang
     * Website: http://htmlpurifier.org/
     *
     * @param $html
     * @return array|string|string[]|null
     */
    function purifierHtml($html) {
        $purifier = new HTMLPurifier();
        $rhtml = $purifier->purify($html);
        return $rhtml;
    }

    /**
     * Handle Upload and Delete for FormElement Upload of type multiUpload
     *
     * This function processes multi-upload operations for a form element.
     * It handles new uploads (copying files, inserting database records)
     * and deletions (removing files and database entries), as well as updating
     * the session upload information.
     *
     * @param $uploadSip | The sip used for the multiUpload Process
     * @param $formElement
     * @throws \DbException
     * @throws \UserReportException
     * @throws \CodeException
     * @throws \InfoException
     * @throws \UserFormException
     */
    private function doMultiUpload($uploadSip, $formElement): void {

        // Get Upload sip information and extract upload information
        $sip = Session::get($uploadSip);
        $uploadsArray = onString::extractUploadsFromSipString($_SESSION[SESSION_NAME][$uploadSip]);
        $projectPath = Path::absoluteApp();
        $sipArray = KeyValueStringParser::parse($sip, '=', '&');

        // Prepare Detail variables for new Upload
        $detailColumns = '';
        $detailValues = '';
        $detailPrep = '';

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

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

        $table = $formElement[MULTI_UPLOAD_TARGET_TABLE] ?? MULTI_UPLOAD_TARGET_TABLE_DEFAULT;

        if (isset($formElement[FE_FILL_STORE_VAR])) {
            $formElement[FE_FILL_STORE_VAR] = $this->evaluate->parse($formElement[FE_FILL_STORE_VAR], ROW_EXPECT_0_1);
            $this->store->appendToStore($formElement[FE_FILL_STORE_VAR], STORE_VAR);
        }

        $uploadId = $this->evaluate->parse($formElement[UPLOAD_ID] ?? '0');

        // Prepare Additional Upload Details if given
        $uploadDetails = $formElement[MULTI_UPLOAD_DETAIL] ?? false;
        if ($uploadDetails) {
            $uploadVariables = $this->evaluate->parse($formElement[MULTI_UPLOAD_DETAIL]);
            $uploadVariables = explode(',', $uploadVariables);


            foreach ($uploadVariables as $variable) {
                $keyValue = explode(':', $variable);
                $detailColumns .= ',' . $keyValue[0];
                $detailValues .= ',' . $keyValue[1];
                $detailPrep .= ',?';
            }
        }

        // Process Each Upload Record
        foreach ($uploadsArray as $index => $upload) {
            $newUploadFlag = $upload[MULTI_UPLOAD_NEW_UPLOAD_FLAG] ?? 0;
            $deleteFlag = $upload[MULTI_UPLOAD_DELETE_FLAG] ?? 0;

            // If a new upload was immediately deleted, skip.
            if ($newUploadFlag && $deleteFlag) {
                continue;
            }

            // If it's an old upload that isn't marked as new or deleted, there's nothing to do.
            if (!$deleteFlag && !$newUploadFlag) {
                continue;
            }

            // Handle Delete first so file name is free
            if ($deleteFlag) {
                // Prepare a query to fetch the file path for this upload.
                $sql = "SELECT pathFileName FROM $table WHERE uploadId = ? AND id = ?";
                $id = $upload[COLUMN_ID] ?? 0;

                $pathFileName = $this->dbArray[$this->dbIndexData]->sql($sql, ROW_EXPECT_1, [$uploadId, $id], 'Can\'t find previously uploaded file.');

                // If the file exists in the filesystem, proceed to delete it.
                if (file_exists($pathFileName[UPLOAD_PATH_FILE_NAME])) {
                    $deleteSql = "DELETE FROM $table WHERE uploadId = ? AND id = ?";
                    HelperFile::unlink($pathFileName[UPLOAD_PATH_FILE_NAME]);

                    $this->dbArray[$this->dbIndexData]->sql($deleteSql, ROW_REGULAR, [$uploadId, $id]);
                } else {
                    throw new \InfoException('Cant delete file since it no longer exists.');
                }

                // Reset Delete flag
                $upload[MULTI_UPLOAD_DELETE_FLAG] = 0;

            }

            if ($newUploadFlag) {

                // Move the temporary file to its final destination and get the new file path.
                $pathFileName = $this->copyUploadFile($formElement, $upload);
                $this->splitUpload($formElement, $pathFileName, false, $upload);

                // Prepare sql
                $tableColumns = UPLOAD_PATH_FILE_NAME . ',' . UPLOAD_ID . ',' . COLUMN_FILE_SIZE . ',' . COLUMN_MIME_TYPE . $detailColumns;
                $tableValuesPrep = '?,?,?,?' . $detailPrep;
                $tableValues = $projectPath . '/' . $pathFileName . ',' . $uploadId . ',' . $upload[FILES_SIZE] . ',' . $upload[FILES_TYPE] . $detailValues;

                if (($formElement[FE_FILE_KEY] ?? '0') !== '0') {
                    $fileKey = $this->generateFileKey(basename($pathFileName), $table);
                    $tableColumns .= ',' . UPLOAD_PATH_FILE_NAME_KEY;
                    $tableValuesPrep .= ',?';
                    $tableValues .= ',' . $fileKey;
                }

                $sql = "INSERT INTO $table ($tableColumns) VALUES ($tableValuesPrep)";

                // Execute sql
                $this->dbArray[$this->dbIndexData]->sql($sql, ROW_EXPECT_0_1, explode(',', $tableValues));
                $lastInsertId = $this->dbArray[$this->dbIndexData]->getLastInsertId();

                // If no uploadId was provided (i.e., it was '0'), update the record to set the uploadId.
                if ($uploadId == '0') {
                    $sqlUpdate = "UPDATE $table SET uploadId = ? WHERE id = ?";
                    $this->dbArray[$this->dbIndexData]->sql($sqlUpdate, ROW_REGULAR, [$lastInsertId, $lastInsertId]);
                    $uploadId = $lastInsertId;
                    $this->store->setVar(UPLOAD_ID, $uploadId, STORE_SIP);
                    $store = OnArray::toString($this->store->getStore(STORE_SIP));
                    // Update the form sip with the uploadId
                    Session::set($this->store->getVar(SIP_SIP), $store);
                }

                // Update the current upload record with:
                // - Resetting the new upload flag.
                // - Storing the full file path.
                // - Saving the database record ID.
                // - Clearing the temporary file name.
                $upload[MULTI_UPLOAD_NEW_UPLOAD_FLAG] = 0;
                $upload[MULTI_UPLOAD_FILE_FULL_PATH] = $projectPath . '/' . $pathFileName;
                $upload[COLUMN_ID] = $lastInsertId;
                $upload[FILES_TMP_NAME] = '';
            }

            // Save any modifications back into the uploads array.
            $uploadsArray[$index] = $upload;
        }

        // Encode uploads and update upload SIP
        $sipArray[MULTI_UPLOAD_UPLOADS_KEY] = json_encode($uploadsArray);
        $sipSting = OnArray::toString($sipArray);
        Session::set($uploadSip, $sipSting);

    }

    /**
     * Extracts all form parameters with keys matching the pattern column[...]
     * and returns a new array where the content inside the brackets becomes the key.
     *
     * Example:
     * Input:  ['column[foo]' => 'A', 'column[bar]' => 'B']
     * Output: ['foo' => 'A', 'bar' => 'B']
     * @param $formSpec
     * @return array An array of extracted values with simplified keys.
     */
    private function prepareExtraValues($formSpec): array {
        $output = [];

        foreach ($formSpec as $key => $value) {
            if (preg_match('/^column\[(.*?)]$/', $key, $matches)) {
                $output[$matches[1]] = $value;
            }
        }

        return $output;
    }

    /**
     * Checks recursively if parent container is enabled
     *
     * @param $containerId
     * @return bool
     */
    private function isContainerEnabled($containerId) {

        $enabled = false;

        // Loop through all enabled FEs
        foreach ($this->feSpecNativeRaw as $fe) {
            if ($fe[FE_ID] === $containerId) {

                // Is nested
                if (($fe[FE_ID_CONTAINER] ?? 0) !== 0) {
                    $enabled = $this->isContainerEnabled($fe[FE_ID_CONTAINER]);
                    break;
                } else {
                    $enabled = true;
                }
            }
        }

        return $enabled;
    }

    /**
     * Validates all InputCollectionJson FormElements:
     * - Column type must be TEXT, MEDIUMTEXT, LONGTEXT or JSON
     * - encode must be 'none' (JSON contains special characters)
     * - checkType must be 'all' (JSON may contain any characters)
     *
     * @param array $tableColumnTypes
     * @throws \UserFormException if any requirement is not met
     */
    private function validateInputCollectionJson(array $tableColumnTypes): void {
        $allowedColumnTypes = ['text', 'mediumtext', 'longtext', 'json'];

        foreach ($this->feSpecNative as $fe) {
            if ($fe[FE_TYPE] !== FE_TYPE_INPUT_COLLECTION_JSON) {
                continue;
            }

            $columnName = $fe[FE_NAME];

            // Validate column type
            if (isset($tableColumnTypes[$columnName])) {
                $columnType = $tableColumnTypes[$columnName];
                $baseType = strtolower(explode('(', $columnType)[0]);
                if (!in_array($baseType, $allowedColumnTypes)) {
                    throw new \UserFormException(
                        "FormElement type '" . FE_TYPE_INPUT_COLLECTION_JSON . "' requires column type TEXT, MEDIUMTEXT, LONGTEXT or JSON. " .
                        "Column '$columnName' has type '$columnType'.",
                        ERROR_FORM_INVALID_COLUMN_TYPE
                    );
                }
            }

            // Validate encode = 'none'
            $encode = $fe[FE_ENCODE] ?? '';
            if ($encode !== FE_ENCODE_NONE) {
                throw new \UserFormException(
                    "FormElement type '" . FE_TYPE_INPUT_COLLECTION_JSON . "' requires encode = 'none'. Current value: '$encode'.",
                    ERROR_FORM_INVALID_COLUMN_TYPE
                );
            }

            // Validate checkType = 'all'
            $checkType = $fe[FE_CHECK_TYPE] ?? '';
            if ($checkType !== SANITIZE_ALLOW_ALL) {
                throw new \UserFormException(
                    "FormElement type '" . FE_TYPE_INPUT_COLLECTION_JSON . "' requires checkType = 'all'. Current value: '$checkType'.",
                    ERROR_FORM_INVALID_COLUMN_TYPE
                );
            }
        }
    }

    /**
     * Generate a slugified file key for the given filename, ensuring uniqueness within the table.
     *
     * @param string $fileName Original file name (basename)
     * @param string $tableName Table to check uniqueness against
     * @param string $columnName Column to check uniqueness against
     * @return string Slugified unique key like "my-document.pdf"
     * @throws \DbException
     * @throws \UserFormException
     */
    private function generateFileKey(string $fileName, string $tableName, string $columnName = UPLOAD_PATH_FILE_NAME_KEY): string {
        $ext = pathinfo($fileName, PATHINFO_EXTENSION);
        $name = pathinfo($fileName, PATHINFO_FILENAME);

        // Single query: QSLUGIFY + fetch all existing keys matching "slug%.ext"
        $rows = $this->dbArray[$this->dbIndexData]->sql(
            "SELECT s.slug, t.$columnName FROM (SELECT QSLUGIFY(?) AS slug) s" .
            " LEFT JOIN $tableName t ON t.$columnName LIKE CONCAT(s.slug, '%.', ?)",
            ROW_REGULAR, [$name, $ext]
        );

        $slug = $rows[0]['slug'];
        $existing = array_column($rows, $columnName);

        $candidate = $slug . '.' . $ext;
        if (!in_array($candidate, $existing)) return $candidate;

        $counter = 2;
        while (in_array($slug . $counter . '.' . $ext, $existing)) {
            $counter++;
        }
        return $slug . $counter . '.' . $ext;
    }
}