<?php
declare(strict_types=1);

namespace IMATHUZH\Qfq\Core\Form;

use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Exception\Thrower;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
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\SqlQuery;
use IMATHUZH\Qfq\Core\Store\Store;

/**
 * Class FormAsFile
 * @package IMATHUZH\Qfq\Core\Form
 */
class FormAsFile {
    /**
     * Remove the form from the DB and insert it using the form file. (only if the form file was changed)
     * If the form file can't be read, then the form is deleted from the DB and an exception is thrown.
     * If the form exists only in the DB and was never exported, then it is exported.
     *
     * - Recognize form file change: Compare the current file stats with the ones saved in the Form table.
     * - Container References: The form file uses names instead if ids to reference container formElements. These references are translated when the formElements are inserted.
     * - Ignored keys: The keys 'id', 'formId', 'feIdContainer' are ignored when reading the form file.
     *
     * @param string $formName
     * @param Database $database
     * @param bool $keepIfNeverExported true: If the form was never exported and file not exists, then export the form from DB
     * @return bool True if form has been updated.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importForm(string $formName, Database $database, bool $keepIfNeverExported = true): bool {
        // Get file stats from database form
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);

        // Get file stats from file system
        $absoluteFormFilePath = self::formPathFileName($formName, $database);
        $fileReadException = function () use ($absoluteFormFilePath, $database, $formName) {

            // Search for form name: convert all to lowercase and compare.
            $addHint = implode(', ', array_filter(self::formFileNames(self::formPath($database)),
                function ($f) use ($formName) {
                    return strtolower($f) === strtolower($formName);
                }));

            if ($addHint != '') {
                $addHint = 'Similar forms: ' . $addHint;
            }

            Thrower::userFormException(
                "Form file not found or missing permission: '" . baseName($absoluteFormFilePath) . "'. " . $addHint,
                "Form definition file not found or no permission to read file: '$absoluteFormFilePath'"
            );
        };
        if (!file_exists($absoluteFormFilePath)) {
            if ($keepIfNeverExported && isset($formFromDb[F_ID]) && (!isset($formFromDb[F_FILE_STATS]) ||
                    !self::isValidFileStats($formFromDb[F_FILE_STATS]))) {
                // if file not exists and form was never exported, then export
                self::exportForm($formName, $database);
            } else {
                self::deleteFormDB($formName, $database, "No corresponding form file found.");
                $fileReadException();
            }
        }
        $fileStatsNew = self::formFileStatsJson($absoluteFormFilePath);
        if ($fileStatsNew === false) {
            self::deleteFormDB($formName, $database, "Failed to read form file stats.");
            $fileReadException();
        }

        // if fileStats from DB and file are equal: do nothing
        if (array_key_exists(F_FILE_STATS, $formFromDb) && $fileStatsNew === $formFromDb[F_FILE_STATS]) {
            return false;
        }

        // Read form file
        $fileContents = file_get_contents($absoluteFormFilePath);
        if ($fileContents === false) {
            self::deleteFormDB($formName, $database, "Failed to read form file.");
            $fileReadException();
        }

        // import json to database
        self::jsonToDatabase($formName, $fileContents, $database, $absoluteFormFilePath, $fileStatsNew);
        return true;
    }

    /**
     * Import all forms found in Resources/Private/Form into the database.
     *
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importSystemForms(Database $database) {
        $absoluteSystemFormPath = Path::absoluteExt(Path::EXT_TO_FORM_SYSTEM);
        $formNames = self::formFileNames($absoluteSystemFormPath);

        # import each json file into db (overwrite)
        foreach ($formNames as $formName) {
            $fileContents = HelperFile::file_get_contents(Path::join($absoluteSystemFormPath, $formName . FORM_FILE_EXTENSION));
            self::jsonToDatabase($formName, $fileContents, $database);
        }
    }

    /**
     * Remove the form from the DB and insert it using the given form json.
     *
     * - Container References: The form json uses names instead if ids to reference container formElements. These references are translated when the formElements are inserted.
     * - Ignored keys: The keys 'id', 'formId', 'feIdContainer' are ignored when reading the form json.
     * @param string $formName
     * @param string $rawJson
     * @param Database $database
     * @param string|null $absoluteFormFilePath
     * @param string|null $fileStatsNew
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public static function jsonToDatabase(string $formName, string $rawJson, Database $database, string $absoluteFormFilePath = null, string $fileStatsNew = null) {
        $formFromJson = json_decode($rawJson, true);

        $store = Store::getInstance();
        // This sip var is set with an 'extra' FormElement using _formJson, this is only needed when _formElementJson is being used
        $formFromStore = $store->getVar(EXTRA_FE_MY_JSON, STORE_SIP);
        if ($formFromStore) {
            $formFromStore = json_decode($formFromStore, true);
            $currentFeId = $store->getVar(TOKEN_FORMELEMENT_ID, STORE_SIP);
            // Get all the FormElements from the current form
            $formIdRecord = $store->getVar(F_ID, STORE_RECORD);
            list($sql, $parameterArray) = SqlQuery::selectFormElementsByFormId($formIdRecord);
            $formElementRecord = $database->sql($sql, ROW_REGULAR, $parameterArray);
            for ($i = 0; $i < count($formFromStore[F_FILE_FORM_ELEMENT]); $i++) {
                // When id = currentFeId then use edited/new value from $formFromJson, else just take unedited from same form record
                if ($formElementRecord[$i][FE_ID] == $currentFeId) {
                    $formFromStore[F_FILE_FORM_ELEMENT][$i] = $formFromJson[0];
                    $formFromStore[F_FILE_FORM_ELEMENT][$i][FE_ID_CONTAINER] = $formElementRecord[$i][FE_ID_CONTAINER];
                } else {
                    $formFromStore[F_FILE_FORM_ELEMENT][$i] = $formElementRecord[$i];
                }
            }
            $formFromJson = $formFromStore;
        }

        if (json_last_error() !== JSON_ERROR_NONE) {
            Thrower::userFormException('Error during form import.', 'Json encode error: ' . json_last_error_msg());
        }

        // form elements exist?
        if (!isset($formFromJson[F_FILE_FORM_ELEMENT])) {
            Thrower::userFormException('Failed to import form json.', "Json key '" . F_FILE_FORM_ELEMENT . "' is missing."
                . ($absoluteFormFilePath !== null ? " Form file path: '$absoluteFormFilePath'" : ''));
        }

        // make sure container names are unique and non-empty
        $containerNames = [];
        foreach ($formFromJson[F_FILE_FORM_ELEMENT] as $formElementFromJson) {

            // Check if mandatory keys are set. (Currently unused since no mandatory keys defined)
            $keysNotSet = OnArray::keysNotSet([], $formElementFromJson);
            if (!empty($keysNotSet)) {
                Thrower::userFormException('Failed to import form json.',
                    "One or more required keys are missing in FormElement definition. Missing keys: " . implode(', ', $keysNotSet)
                    . ($absoluteFormFilePath !== null ? ". Form file path: '$absoluteFormFilePath'" : ''));
            }

            // collect names of container FormElements
            if (($formElementFromJson[FE_CLASS] ?? '') === FE_CLASS_CONTAINER) {
                if (!key_exists(FE_NAME, $formElementFromJson) || $formElementFromJson[FE_NAME] == '') {
                    Thrower::userFormException('Failed to import form json.',
                        "Found formElement of class container with empty or non-existing name."
                        . ($absoluteFormFilePath !== null ? " Form file path: '$absoluteFormFilePath'" : ''));
                }
                if (in_array($formElementFromJson[FE_NAME], $containerNames)) {
                    Thrower::userFormException('Failed to import form json.',
                        "Multiple formElements of class container with the same name '" . $formElementFromJson[FE_NAME]
                        . ($absoluteFormFilePath !== null ? ". Form file path: '$absoluteFormFilePath'" : ''));
                }
                $containerNames[] = $formElementFromJson[FE_NAME];
            }
        }

        // define temporary name for new form
        $newFormTempName = $formName . '_FAILED_IMPORT'; // will be renamed at the end

        // Insert new Form to DB (after filtering allowed columns and adding column 'name')
        $formSchema = $database->getTableDefinition(TABLE_NAME_FORM);
        $formColumns = array_column($formSchema, 'Field');
        $insertValues = array_filter($formFromJson, function ($columnName) use ($formColumns) {
            return $columnName !== F_ID && in_array($columnName, $formColumns);
        }, ARRAY_FILTER_USE_KEY); // array(column => value)
        $insertValues[F_NAME] = $newFormTempName;
        $insertValues[F_FILE_STATS] = $fileStatsNew ?? 'no file';
        list($sqlFormInsert, $parameterArrayFormInsert) = SqlQuery::insertRecord(TABLE_NAME_FORM, $insertValues);
        $formIdNew = $database->sql($sqlFormInsert, ROW_REGULAR, $parameterArrayFormInsert);

        // Delete stale formElements with the new form id (these should not exist, but better make sure)
        self::deleteFormElementsDBWithFormId($formIdNew, $database, "Inserted new form with id $formIdNew.");

        // Insert FormElements to DB and collect container ids
        $containerIds = []; // array(container_name => id)
        foreach ($formFromJson[F_FILE_FORM_ELEMENT] as &$formElementFromJson) {
            $feId = self::insertFormElement($formElementFromJson, $formIdNew, $database);
            $formElementFromJson[FE_ID] = $feId;
            if (($formElementFromJson[FE_CLASS] ?? '') === FE_CLASS_CONTAINER) {
                // the existence of the key FE_NAME is ensured above for FE container
                $containerIds[$formElementFromJson[FE_NAME]] = $feId;
            }
        }

        // Update container IDs for each form element which has a container name
        foreach ($formFromJson[F_FILE_FORM_ELEMENT] as &$formElementFromJson) {
            if (array_key_exists(FE_FILE_CONTAINER_NAME, $formElementFromJson)) {
                $containerName = $formElementFromJson[FE_FILE_CONTAINER_NAME];
                if (!isset($containerIds[$containerName])) {
                    Thrower::userFormException('Failed to import form json.',
                        "Key '" . FE_FILE_CONTAINER_NAME . "' points to non-existing container with name '$containerName' in definition of formElement with name '" . $formElementFromJson[FE_NAME]
                        . ($absoluteFormFilePath !== null ? ". Form file path: '$absoluteFormFilePath'" : ''));
                }
                $containerId = $containerIds[$containerName];
                $feId = $formElementFromJson[FE_ID];
                list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM_ELEMENT, [FE_ID_CONTAINER => $containerId], $feId);
                $database->sql($sql, ROW_REGULAR, $parameterArray);
            }
        }

        // Delete old form if everything went well
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);
        $formIdOld = $formFromDb[F_ID] ?? null;
        if ($formIdOld !== null) {
            self::deleteFormDBWithId($formIdOld, $database, "New version of form $formName was imported successfully. New form id: $formIdNew");

            // Replace temporary name of new form and set id to old id
            list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_NAME => $formName, F_ID => $formIdOld], $formIdNew);
            $database->sql($sql, ROW_REGULAR, $parameterArray);

            // update formId on all new FormElements
            $_FormElement = TABLE_NAME_FORM_ELEMENT;
            $_formId = FE_FORM_ID;
            $sql = "UPDATE `$_FormElement` SET `$_formId`=? WHERE `$_formId`=?";
            $database->sql($sql, ROW_REGULAR, [$formIdOld, $formIdNew]);
        } else {
            // Replace temporary name of new form
            list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_NAME => $formName], $formIdNew);
            $database->sql($sql, ROW_REGULAR, $parameterArray);
        }

        // This is only used with _formElementJson
        if (isset($formElementRecord)) {
            list($sql, $parameterArray) = SqlQuery::selectFormElementsByFormId($formIdOld);
            $newFeIds = $database->sql($sql, ROW_REGULAR, $parameterArray);
            // Update the id's from the newly created FormElements to the id's we started with, also update lost container id's
            for ($i = 0; $i < count($newFeIds); $i++) {
                $values = array(
                    FE_ID => $formElementRecord[$i][FE_ID],
                    FE_ID_CONTAINER => $formElementRecord[$i][FE_ID_CONTAINER]
                );
                list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM_ELEMENT, $values, $newFeIds[$i][FE_ID]);
                $database->sql($sql, ROW_REGULAR, $parameterArray);
            }
        }
    }

    /**
     * Reads the form from the database and saves it in the form folder as a form file.
     * If $formId is given, then $formName is ignored.
     * If the form file path does not exist, it is created and all forms are exported.
     *
     * ! Warning: Overwrites form file without any checks or warning.
     *
     * - FileStats: After the export the column "fileStats" is updated with new stats from new file.
     * - Container FormElements: The form file uses names instead if ids to reference containers. These references are translated before saving the file.
     * - Ignored columns: The columns 'id', 'name' (only Form table), 'fileStats', 'formId', 'feIdContainer' are not saved in the file.
     * - FormElement order: The formElements are ordered by the 'ord' column before writing them to the file.
     *
     * @param string $formName
     * @param Database $database
     * @param int|null $formId if given, then $formName is ignored
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function exportForm(string $formName, Database $database, ?int $formId = null) // : void
    {
        list($formName, $formId, $formJson, $adjustContainerName, $form) = self::formToJson($formName, $database, $formId);

        // backup and write file
        $pathFileName = self::formPathFileName($formName, $database);
        self::backupFormFile($pathFileName);
        HelperFile::file_put_contents($pathFileName, $formJson);
        Logger::logMessage(date('Y.m.d H:i:s ') . ": Overwrote form file '$pathFileName'. Reason: Export new version of form from database.", Path::absoluteQfqLogFile());

        // some column names where adjusted => import form
        if ($adjustContainerName) {
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Importing form file '$pathFileName'. Reason: Empty or non-unique names of container formElements where adjusted during form export.", Path::absoluteQfqLogFile());
            self::importForm($formName, $database);

            // otherwise => Update column fileStats
        } else {
            $fileStats = self::formFileStatsJson($pathFileName);
            list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_FILE_STATS => $fileStats, F_MODIFIED => $form[F_MODIFIED]], $formId);
            $database->sql($sql, ROW_REGULAR, $parameterArray);
        }
    }

    /**
     * Create copy of given form file in form/_backup. If given file does not exist, do nothing.
     * New file name: <formName>.YYYMMDDhhmmss.file.json
     *
     * @param string $absoluteFormFilePath
     * @throws \UserFormException
     */
    private static function backupFormFile(string $absoluteFormFilePath) {
        if (file_exists($absoluteFormFilePath)) {
            if (!is_readable($absoluteFormFilePath)) {
                Thrower::userFormException('Error while trying to backup form file.', "Form file is not readable: $absoluteFormFilePath");
            }

            // copy file
            $absoluteBackupFilePath = self::newBackupPathFileName(basename($absoluteFormFilePath, FORM_FILE_EXTENSION), 'file');
            $success = copy($absoluteFormFilePath, $absoluteBackupFilePath);
            if ($success === false) {
                Thrower::userFormException('Error while trying to backup form file.', "Can't copy file $absoluteFormFilePath to $absoluteBackupFilePath");
            }
        }
    }

    /**
     * Import Form from file if loaded record is Form/FormElement.
     * If form file was changed, import it and throw exception.
     * If form file is not writeable, throw exception.
     *
     * @param int $recordId
     * @param string $tableName
     * @param Database $database
     * @return string|null formName|null
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importFormRecordId(int $recordId, string $tableName, Database $database) // : ?string
    {
        $recordFormName = self::formNameFromFormRelatedRecord($recordId, $tableName, $database);
        if ($recordFormName !== null) {
            if (self::importForm($recordFormName, $database)) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => 'Form file was changed.',
                    ERROR_MESSAGE_TO_DEVELOPER => "Form definition file has been changed. Please close tab and reload the form list and Form-Editor from scratch."]),
                    ERROR_FORM_NOT_FOUND);
            }
            self::enforceFormFileWritable($recordFormName, $database);
        }
        return $recordFormName;
    }

    /**
     * Deletes the form file for the given form.
     *
     * @param $formName
     * @param Database $database
     * @param string $logMessageReason
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function deleteFormFile($formName, Database $database, string $logMessageReason = '') // : void
    {
        self::enforceFormFileWritable($formName, $database);
        $pathFileName = self::formPathFileName($formName, $database);
        if (file_exists($pathFileName)) {
            self::backupFormFile($pathFileName);
            $success = unlink($pathFileName);
            if ($success === false) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Deleting form file failed: " . baseName($pathFileName),
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't delete form file '$pathFileName'"]),
                    ERROR_IO_WRITE_FILE);
            }
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Removed form file '$pathFileName'. Reason: $logMessageReason", Path::absoluteQfqLogFile());
        }
    }

    /**
     * Multiple errors might occur after a form file import. This hint can be added to such exceptions to help users.
     * Only returns non-empty string if the table is either Form of FormElement.
     *
     * @param string $tableName
     * @return string
     */
    public static function errorHintFormImport(string $tableName = TABLE_NAME_FORM): string {
        $message = '';
        if (in_array($tableName, [TABLE_NAME_FORM, TABLE_NAME_FORM_ELEMENT])) {
            $message .= "Hint: Form definition file might have changed. Please reopen the form list and Form-Editor from scratch.";
        }
        return $message;
    }

    /**
     * Throw exception if form file or form directory is not writeable.
     *
     * @param string $formName
     * @param Database $database
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function enforceFormFileWritable(string $formName, Database $database) // : void
    {
        $pathFileName = self::formPathFileName($formName, $database);
        HelperFile::enforce_writable_or_creatable($pathFileName);

    }

    /**
     * Return form name if given record is an existing Form or FormElement.
     * Return null otherwise.
     *
     * @param int $recordId
     * @param string $recordTable
     * @param Database $database
     * @return string formName|null
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function formNameFromFormRelatedRecord(int $recordId, string $recordTable, Database $database) // : ?string
    {
        if ($recordId === 0) {
            return null;
        }
        switch ($recordTable) {
            case TABLE_NAME_FORM:
                list($sql, $parameterArray) = SqlQuery::selectFormById($recordId);
                $formFromDb = $database->sql($sql, ROW_EXPECT_1,
                    $parameterArray, "Form with id '$recordId' not found. " . self::errorHintFormImport());
                return $formFromDb[F_NAME];
            case TABLE_NAME_FORM_ELEMENT:
                $F_NAME = F_NAME;
                $TABLE_NAME_FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT;
                $TABLE_NAME_FORM = TABLE_NAME_FORM;
                $F_ID = F_ID;
                $FE_FORM_ID = FE_FORM_ID;
                $FE_ID = FE_ID;

                // Select form name by formElement id
                $formFromDb = $database->sql("SELECT `f`.`$F_NAME` FROM `$TABLE_NAME_FORM` AS f INNER JOIN `$TABLE_NAME_FORM_ELEMENT` AS fe ON f.`$F_ID`=fe.`$FE_FORM_ID` WHERE `fe`.`$FE_ID`=?", ROW_EXPECT_1,
                    [$recordId], "Form element with id '$recordId' not found. " . self::errorHintFormImport());
                return $formFromDb[F_NAME];
            default:
                return null;
        }
    }

    /**
     * Returns true if the sql query selects Form or FormElement table
     *
     * @param string $sql
     * @return bool
     */
    public static function isFormQuery(string $sql): bool {
        // find substrings which start with FROM and are followed by Form or FormElement
        preg_match_all('/(?i)FROM(?-i)(.*?)\b(' . TABLE_NAME_FORM . '|' . TABLE_NAME_FORM_ELEMENT . ')\b/s', $sql, $matches);

        // Check if no other SQL keywords are in between FROM and the table name
        $keywordsAfterFrom = ['WHERE', 'GROUP BY', 'HAVING', 'WINDOW', 'ORDER BY', 'LIMIT', 'FOR', 'INTO'];
        foreach ($matches[0] as $match) {
            if (!OnString::containsOneOfWords($keywordsAfterFrom, $match)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Import all form files into the database.
     * If the file folder does not exist, it is created and all forms from the DB are exported.
     *
     * @param Database $database
     * @param bool $enforceWritable Throw exception if one of the form files is not writable by current user.
     * @param bool $deleteFromDB Delete forms from DB if there are no corresponding form files. Except if the form was never exported.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importAllForms(Database $database, bool $enforceWritable = false, bool $deleteFromDB = false) // : void
    {
        // Import all form files
        $formFileNames = self::formFileNames(self::formPath($database));
        foreach ($formFileNames as $formFileName) {
            self::importForm($formFileName, $database);
            if ($enforceWritable) {
                self::enforceFormFileWritable($formFileName, $database);
            }
        }

        // Delete all forms which are in DB but not in files. Except if they were never exported.
        if ($deleteFromDB) {
            $formNamesDB = self::queryAllFormNames($database);
            $formsToDelete = array_diff($formNamesDB, $formFileNames);
            foreach ($formsToDelete as $formToDelete) {
                self::deleteFormDB($formToDelete, $database, "No corresponding form file found.", true);
            }
        }
    }

    /**
     * Export all forms in database to form files.
     * Warning: This overwrites form files without any checks or warning. Use cautiously.
     *
     * @param Database $database
     * @param bool $deleteFiles Delete form files without a corresponding form record in the DB.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function exportAllForms(Database $database, bool $deleteFiles = false) // : void
    {
        $formNamesDB = self::queryAllFormNames($database);
        foreach ($formNamesDB as $formNameDB) {
            self::exportForm($formNameDB, $database);
        }
        if ($deleteFiles) {
            $formFileNames = self::formFileNames(self::formPath($database));
            $filesToDelete = array_diff($formFileNames, $formNamesDB);
            foreach ($filesToDelete as $fileToDelete) {
                self::deleteFormFile($fileToDelete, $database, "Export all forms from database. No form with name '$fileToDelete' in database.");
            }
        }
    }

    /**
     * Return the form/form-element as a json string.
     *
     * @param $columnValue string form-element or form id as string
     * @param $database
     * @param $mode string either formJson, formElementJson (taken from columnName)
     * @return string form/form-element as json.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function renderColumnFormJson(string $columnValue, $database, $mode): string {
        // Parse arguments
        $param = KeyValueStringParser::parse($columnValue, PARAM_TOKEN_DELIMITER, PARAM_DELIMITER);

        foreach ($param as $key => $value) {
            if (!in_array($key, [TOKEN_FORM_ID, TOKEN_ENCODING_BASE_64, TOKEN_REDUCE_KEYS, TOKEN_FORMELEMENT_ID,])) {
                Thrower::userFormException('Rendering Json failed.', "Token '$key' not recognized for column _" . $mode);
            }
        }
        if (!key_exists(TOKEN_FORM_ID, $param) || !ctype_digit($param[TOKEN_FORM_ID])) {
            Thrower::userFormException('Rendering Json Form failed.', "The special column '_" . $mode . "' expects token " . TOKEN_FORM_ID . ' with an integer value.');
        }
        if ($mode == COLUMN_FORM_ELEMENT_JSON) {
            if (!key_exists(TOKEN_FORMELEMENT_ID, $param) || !ctype_digit($param[TOKEN_FORMELEMENT_ID])) {
                Thrower::userFormException('Rendering Json Form failed.', "The special column '_" . $mode . "' expects token " . TOKEN_FORMELEMENT_ID . ' with an integer value.');
            }
        }

        $base64 = key_exists(TOKEN_ENCODING_BASE_64, $param);
        $json = '';

        switch ($mode) {
            case COLUMN_FORM_JSON:
                $formId = intval($param[TOKEN_FORM_ID]);
                $reduceKeys = key_exists(TOKEN_REDUCE_KEYS, $param);
                if ($formId !== 0) {
                    list($_, $_, $json) = self::formToJson('', $database, $formId, $reduceKeys);
                    $json = $base64 ? base64_encode($json) : $json;
                }
                break;
            case COLUMN_FORM_ELEMENT_JSON:
                $formElementId = intval($param[TOKEN_FORMELEMENT_ID]);
                $json = self::formElementToJson($database, $formElementId);
                $json = $base64 ? base64_encode($json) : $json;
                break;
        }
        return $json;
    }

    /**
     * Insert formElement to the given form.
     * Keys removed before insert: id, formId, feIdContainer
     *
     * @param array $values
     * @param int $formId
     * @param Database $database
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function insertFormElement(array $values, int $formId, Database $database): int {
        // filter allowed formElement columns (remove id, formId, feIdContainer)
        $formElementSchema = $database->getTableDefinition(TABLE_NAME_FORM_ELEMENT);
        $formElementColumns = array_column($formElementSchema, 'Field');
        $insertValues = array_filter($values, function ($columnName) use ($formElementColumns) {
            return !in_array($columnName, [FE_ID, FE_FORM_ID, FE_ID_CONTAINER]) && in_array($columnName, $formElementColumns);
        }, ARRAY_FILTER_USE_KEY); // array(column => value)

        // execute insert
        $insertValues[FE_FORM_ID] = $formId;
        list($sql, $parameterArray) = SqlQuery::insertRecord(TABLE_NAME_FORM_ELEMENT, $insertValues);
        $id = $database->sql($sql, ROW_REGULAR, $parameterArray);

        return $id;
    }

    /**
     * Delete form with given name and its form elements from DB.
     * If $keepIfNeverExported is set to true then the form is kept (i.e. exported) instead of deleted if the form was never exported yet.
     *
     * @param string $formName
     * @param Database $database
     * @param string $logMessageReason
     * @param bool $keepIfNeverExported
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function deleteFormDB(string $formName, Database $database, string $logMessageReason = '', bool $keepIfNeverExported = false) // : void
    {
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);

        if ($keepIfNeverExported && (isset($formFromDb[F_ID]) && !isset($formFromDb[F_FILE_STATS])) || (isset($formFromDb[F_FILE_STATS]) && !self::isValidFileStats($formFromDb[F_FILE_STATS]))) {
            // export form instead of deleting since it was never exported before
            self::exportForm($formName, $database);
        } else if (array_key_exists(F_ID, $formFromDb)) {
            self::deleteFormDBWithId($formFromDb[F_ID], $database, $logMessageReason . " Form name '$formName'.");
        }
    }

    /**
     * Return true if the given string is a valid JSON encoded file stats string.
     * Currently only checks if first char is '{'
     *
     * @param string $fileStats
     * @return bool
     */
    private static function isValidFileStats(string $fileStats) {
        return substr($fileStats, 0, 1) === '{';
    }

    /**
     * Delete form with given id and its form elements from DB
     *
     * @param int $formId
     * @param Database $database
     * @param string $logMessageReason
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function deleteFormDBWithId(int $formId, Database $database, string $logMessageReason = '') // : void
    {
        self::backupFormDb($formId, $database);
        $F_ID = F_ID; // can't use constants in strings directly
        $TABLE_NAME_FORM = TABLE_NAME_FORM; // can't use constants in strings directly
        $rowsAffected = $database->sql("DELETE FROM `$TABLE_NAME_FORM` WHERE `$F_ID`=? LIMIT 1", ROW_REGULAR, [$formId]);
        if ($rowsAffected > 0) {
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Remove form with id $formId from database. Reason: $logMessageReason", Path::absoluteQfqLogFile());
        }
        self::deleteFormElementsDBWithFormId($formId, $database, 'Remove form. ' . $logMessageReason);
    }

    /**
     * Export the given form from database into a backup file.
     * Backup file name: <formName>.YYYMMDDhhmmss.db.json
     *
     * @param int $formId
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function backupFormDb(int $formId, Database $database) {
        list($formName, $formId, $formJson) = self::formToJson('', $database, $formId);
        $absoluteBackupFilePath = self::newBackupPathFileName($formName, 'db', $formId);
        HelperFile::file_put_contents($absoluteBackupFilePath, $formJson);
    }


    /**
     * Delete form elements with given formId from DB
     *
     * @param int $formId
     * @param Database $database
     * @param string $logMessageReason
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function deleteFormElementsDBWithFormId(int $formId, Database $database, string $logMessageReason = '') // : void
    {
        $TABLE_NAME_FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT; // can't use constants in strings directly
        $FE_FORM_ID = FE_FORM_ID; // can't use constants in strings directly
        $affectedRows = $database->sql("DELETE FROM `$TABLE_NAME_FORM_ELEMENT` WHERE `$FE_FORM_ID`=?", ROW_REGULAR, [$formId]);
        if ($affectedRows > 0) {
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Removed $affectedRows formElements with formId $formId from database. Reason: $logMessageReason", Path::absoluteQfqLogFile());
        }
    }

    /**
     * Return a selection of file stats as a JSON string which serves as a fingerprint of the file.
     * If any one of the stats changed the file content was probably changed as well.
     *
     * @param string $pathFileName
     * @return false|string
     */
    private static function formFileStatsJson(string $pathFileName) {
        clearstatcache(true, $pathFileName);
        $stats = stat($pathFileName);
        if ($stats === false) {
            return false;
        }
        return json_encode([
            'modified' => $stats['mtime'],
            'size' => $stats['size'],
            'inode' => $stats['ino']
        ]);
    }

    /**
     * Return correct pathFileName of form file relative to current working directory.
     * Create path if it doesn't exist and export all forms from DB.
     *
     * @param string $formName
     * @param Database $database
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formPathFileName(string $formName, Database $database): string {
        // validate form name
        if (!HelperFile::isValidFileName($formName)) {
            throw new \UserFormException(json_encode([
                ERROR_MESSAGE_TO_USER => 'Reading/Writing Form file failed.',
                ERROR_MESSAGE_TO_DEVELOPER => "Form name '$formName' not valid. Name may only consist of alphanumeric characters and _ . -"]),
                ERROR_FORM_INVALID_NAME);
        }
        return self::formPath($database) . '/' . $formName . FORM_FILE_EXTENSION;
    }

    /**
     * Return the path of the form directory relative to CWD.
     * Create path if it doesn't exist and export all forms from DB.
     *
     * @param Database $database
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formPath(Database $database): string {
        $absoluteFormPath = Path::absoluteProject(Path::projectToForm());
        if (!is_dir($absoluteFormPath)) {

            // create path
            $success = mkdir($absoluteFormPath, 0777, true);
            if ($success === false) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Can't create form file path.",
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't create path: " . $absoluteFormPath]),
                    ERROR_IO_WRITE_FILE);
            }

            if (FEATURE_FORM_FILE_SYNC) {
                // export all forms
                self::exportAllForms($database);
            }
        }
        return $absoluteFormPath;
    }

    /**
     * Return array of all form names in the Form table.
     *
     * @param Database $database
     * @return array [formName]
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function queryAllFormNames(Database $database): array {
        $NAME = F_NAME;
        $FORM = TABLE_NAME_FORM;
        return array_column($database->sql("SELECT `$NAME` FROM `$FORM`", ROW_REGULAR), $NAME);
    }

    /**
     * Return array of form file names contained in the given directory (without suffix).
     *
     * @param string $absoluteDirPath
     * @return array|false [formName]
     * @throws \UserFormException
     */
    private static function formFileNames(string $absoluteDirPath): array {
        $files = scandir($absoluteDirPath);
        if ($files === false) {
            Thrower::userFormException("Reading directory failed.", "Can't read directory: " . $absoluteDirPath);
        }
        return $jsonFileNames = array_reduce($files, function ($result, $file) {
            $fileInfo = pathinfo($file);
            if (isset($fileInfo['extension']) && isset($fileInfo['filename']) && $fileInfo['extension'] === 'json') {
                $result[] = $fileInfo['filename'];
            }
            return $result;
        }, []);
    }

    /**
     * Return json string of the given form-element.
     * @param Database $database
     * @param int $formElementId
     * @return string $formElementJson
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formElementToJson(Database $database, int $formElementId): string {

        list($sql, $parameterArray) = SqlQuery::selectFormElementById($formElementId);
        $formElement = $database->sql($sql, ROW_REGULAR, $parameterArray, "FormElement with id $formElementId not found.");

        // should not be adjustable in form, will be set automatically
        unset($formElement[0][FE_FORM_ID]);
        unset($formElement[0][FE_ID]);
        unset($formElement[0][FE_ID_CONTAINER]);

        $formElementJson = json_encode($formElement, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
        if (json_last_error() !== JSON_ERROR_NONE) {
            Thrower::userFormException('Error during form export.', 'Json encode error: ' . json_last_error_msg());
        }
        return $formElementJson;
    }

    /**
     * Return json string of the given form with form-elements. If $formId is given, $formName is ignored.
     *
     * - Container FormElements: The form file uses names instead if ids to reference containers. These references are translated before saving the file.
     * - Adjust container names: If a container name is empty or not unique it is adjusted. In that case the fourth return parameter is set to true.
     * - Ignored columns: The columns 'id', 'name' (only Form table), 'fileStats', 'formId', 'feIdContainer' are not added to the json output.
     * - FormElement order: The formElements are ordered by the 'ord' column before writing them to the file.
     *
     * @param string $formName
     * @param Database $database
     * @param int|null $formId If given, $formName is ignored
     * @param bool $removeUnnecessaryKeys
     * @return array [string $formName, int $formId, string $formJson, bool ($adjustedContainerNames > 0), array $form]
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formToJson(string $formName, Database $database, ?int $formId = null, bool $removeUnnecessaryKeys = false): array {
        // Get form from DB (either by id or by name)
        if ($formId !== null) {
            list($sql, $parameterArray) = SqlQuery::selectFormById($formId);
            $form = $database->sql($sql, ROW_EXPECT_1,
                $parameterArray, "Form with id $formId not found."); // array(column name => value)
        } else {
            $form = $database->selectFormByName($formName); // array(column name => value)
            if (!isset($form[F_ID])) {
                Thrower::userFormException("Error during form export.", "Form $formName not found in database. Note: Form names are case sensitive.");
            }
        }

        // Remove columns: id, name, fileStats
        $formId = $form[F_ID];
        $formName = $form[F_NAME];
        unset($form[F_ID]);
        unset($form[F_NAME]);
        unset($form[F_FILE_STATS]);
        if ($removeUnnecessaryKeys) {
            unset($form[COLUMN_CREATED]);
            unset($form[COLUMN_MODIFIED]);
            $form = self::filterNonDefaultColumns($form, TABLE_NAME_FORM, $database);
        }

        // Get formElements from DB
        list($sql, $parameterArray) = SqlQuery::selectFormElementsByFormId($formId);
        $formElements = $database->sql($sql, ROW_REGULAR, $parameterArray); // array(array(column name => value))

        // Create id => name dictionary for column names
        $adjustedContainerNames = 0;
        $containerNames = array_reduce($formElements, function ($result, $formElement) use ($formName, $formId, &$adjustedContainerNames) {
            if ($formElement[FE_CLASS] === FE_CLASS_CONTAINER) {
                $containerName = $formElement[FE_NAME];

                // container name not unique => adjust make unique
                if (in_array($containerName, $result)) {
                    $containerName = $containerName . '_auto_adjust_not_unique_' . count($result);
                    $adjustedContainerNames++;
                }

                // container name empty => adjust make non-empty unique
                if ($containerName === '') {
                    $containerName = 'auto_adjust_empty_' . count($result);
                    $adjustedContainerNames++;
                }
                $result[$formElement[FE_ID]] = $containerName;
            }
            return $result;
        }, []); // array(id => name)

        // ajdust formElements for export
        $formElements = array_map(function ($formElement) use ($containerNames, $removeUnnecessaryKeys, $database) {

            // in case container name was auto adjusted above we set new name
            if (isset($containerNames[$formElement[FE_ID]])) {
                $formElement[FE_NAME] = $containerNames[$formElement[FE_ID]];
            }

            // Replace container id references with name references
            $containerId = $formElement[FE_ID_CONTAINER];
            if ($containerId !== 0 && isset($containerNames[$containerId])) {
                $formElement[FE_FILE_CONTAINER_NAME] = $containerNames[$containerId];
            }

            // remove columns id, formId, feIdContainer
            unset($formElement[FE_ID_CONTAINER]);
            unset($formElement[FE_ID]);
            unset($formElement[FE_FORM_ID]);

            if ($removeUnnecessaryKeys) {
                unset($formElement[COLUMN_CREATED]);
                unset($formElement[COLUMN_MODIFIED]);
                $formElement = self::filterNonDefaultColumns($formElement, TABLE_NAME_FORM_ELEMENT, $database);
            }

            return $formElement;
        }, $formElements);

        // add form elements and create json
        $form[F_FILE_FORM_ELEMENT] = $formElements;
        $formJson = json_encode($form, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
        if (json_last_error() !== JSON_ERROR_NONE) {
            Thrower::userFormException('Error during form export.', 'Json encode error: ' . json_last_error_msg());
        }
        return array($formName, $formId, $formJson, $adjustedContainerNames > 0, $form);
    }

    /**
     * Return the path to a (non-existing) form backup file with name:
     * <formName>.YYYMMDDhhmmss.<tag>.json
     * If the form id is given then the backup file is placed into a folder named by the id.
     *
     * @param string $formName
     * @param string $tag
     * @param int|null $formId
     * @return string
     * @throws \UserFormException
     */
    private static function newBackupPathFileName(string $formName, string $tag, int $formId = null): string {
        // create backup path if not exists
        $absoluteBackupPath = Path::absoluteProject(Path::projectToForm(), Path::FORM_TO_FORM_BACKUP, $formId ?? '');
        if (!is_dir($absoluteBackupPath)) {
            $success = mkdir($absoluteBackupPath, 0777, true);
            if ($success === false) {
                Thrower::userFormException('Error while trying to backup form file.', "Can't create backup path: $absoluteBackupPath");
            }
        }

        $absoluteBackupFilePath = Path::join($absoluteBackupPath, $formName . FORM_FILE_EXTENSION . '.' . date('Y-m-d_H-i-s') . ".$tag");

        // add index to filename if backup file with current timestamp already exists
        $index = 1;
        while (file_exists($absoluteBackupFilePath)) {
            $absoluteBackupFilePath = Path::join($absoluteBackupPath, $formName . FORM_FILE_EXTENSION . '.' . date('Y-m-d_H-i-s') . ".$index.$tag");
            $index++;
            if ($index > 20) {
                Thrower::userFormException('Error while trying to backup form file.', 'Infinite loop.');
            }
        }
        return $absoluteBackupFilePath;
    }

    /**
     *
     * Returns an associative array with the default value for each column in table $tableName
     * return: array(column name => default value)
     *
     * @param string $tableName
     * @param Database $database
     * @return array array(column name => default value)
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function getColumnDefaults(string $tableName, Database $database): array {
        $tableDefinition = $database->getTableDefinition($tableName);
        return array_reduce($tableDefinition, function ($result, $column) {
            $result[$column['Field']] = ($column['Default'] ?? '');
            return $result;
        }, []);
    }

    /**
     *
     * Receives a row $row from table $tableName and returns a new array with only the columns of $row which do not
     * contain the default value set for that column.
     * Keeps the default values of $tableName in a cache for multiple use of function.
     *
     * @param array $row
     * @param string $tableName
     * @param Database $database
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function filterNonDefaultColumns(array $row, string $tableName, Database $database): array {
        static $cache = [];
        if (!key_exists($tableName, $cache)) {
            $cache[$tableName] = self::getColumnDefaults($tableName, $database);
        }
        $columnDefaults = $cache[$tableName];

        // return $formNoDefault: array(column name => value)
        return array_filter($row, function ($value, $columnName) use ($columnDefaults) {
            return $value !== ($columnDefaults[$columnName] ?? null);
        }, ARRAY_FILTER_USE_BOTH);
    }
}