QFQ

<?php

/**

 * Created by PhpStorm.

 * User: crose

 * Date: 5/9/17

 * Time: 8:56 AM

 */

namespace IMATHUZH\Qfq\Core\Database;

use IMATHUZH\Qfq\Core\Exception\Thrower;

use IMATHUZH\Qfq\Core\Form\FormAsFile;

use IMATHUZH\Qfq\Core\Helper\Logger;

use IMATHUZH\Qfq\Core\Helper\OnString;

use IMATHUZH\Qfq\Core\Helper\Path;

use IMATHUZH\Qfq\Core\QuickFormQuery;

use IMATHUZH\Qfq\Core\Store\Config;

use IMATHUZH\Qfq\Core\Store\Store;

use IMATHUZH\Qfq\Core\Store\T3Info;

use IMATHUZH\Qfq\Core\Typo3\T3Handler;

/*

 * Read the extension version number.

 * Read the QFQ database version number: stored in the comment field of table 'Form'. Format:  Version=x.y.z

 * If versions different:

 *   * Read the update array 'DatabaseUpdateData.php'.

 *   * Play all changes from the update array after 'old' upto 'new'.

 *   * Save new QFQ database version in the comment field of table 'Form'

 *

 * In a new QFQ installation, the comment field of table 'Form' is empty. On the first call of QFQ, the version string

 * will be set. Also the 'qfqDefaultTables.sql' will be played to create alls QFQ system tables initially.

 *

 */

/**

 * Class DatabaseUpdate

 * @package qfq

 */

class DatabaseUpdate {

    /**

     * @var Database

     */

    protected $db = null;

    /**

     * @var Store

     */

    protected $store = null;

    /**

     * @param Database $db

     * @param Store $store

     */

    public function __construct(Database $db, Store $store) {

        $this->db = $db;

        $this->store = $store;

    }

    /**

     * @return mixed

     * @throws \CodeException

     */

    private function getExtensionVersion() {

        $path = __DIR__ . '/../../../ext_emconf.php';

        $_EXTKEY = EXT_KEY;

        $EM_CONF = null;

        if (@file_exists($path)) {

            include $path;

            if (isset($EM_CONF[$_EXTKEY]['version'])) {

                return $EM_CONF[$_EXTKEY]['version'];

            }

        }

        throw new \CodeException('Failed to read extension version', ERROR_QFQ_VERSION);

    }

    /**

     * Try to read the QFQ version number from 'comment' in table 'Form'.

     * In a very special situation , there might be table 'form' and 'Form' in the same Database. This should be handled

     *  in the way that the latest version number is the active one.

     *

     * @return bool|string  false if there is no table 'Form' or if there is no comment set in table 'Form'.

     * @throws \CodeException

     * @throws \DbException

     * @throws \UserFormException

     */

    private function getDatabaseVersion() {

        $arr = $this->db->sql("SHOW TABLE STATUS WHERE Name='Form'", ROW_REGULAR);

        $found = '';

        //

        foreach ($arr as $row) {

            if (isset($row['Comment'])) {

                parse_str($row['Comment'], $arr);

                if (($arr[QFQ_VERSION_KEY] ?? '') !== '' && (version_compare($arr[QFQ_VERSION_KEY], $found) == 1)) {

                    $found = $arr;

                }

            } else {

                continue;

            }

        }

        return ($found === '') ? false : $found;

    }

    /**

     * @param $version

     *

     * @throws \CodeException

     * @throws \DbException

     * @throws \UserFormException

     */

    private function setDatabaseVersion($version) {

        if (is_array($version)) {

            $versionInfo = $version;

        } else {

            $versionInfo = $this->getDatabaseVersion();

            $versionInfo[QFQ_VERSION_KEY] = $version;

        }

        $this->db->sql("ALTER TABLE `Form` COMMENT = '" . http_build_query($versionInfo) . "'");

    }

    /**

     *

     * @param string $dbUpdate SYSTEM_DB_UPDATE_ON | SYSTEM_DB_UPDATE_OFF | SYSTEM_DB_UPDATE_AUTO

     *

     * @throws \CodeException

     * @throws \DbException

     * @throws \UserFormException

     * @throws \UserReportException

     */

    public function checkNupdate($dbUpdate) {

        $new = $this->getExtensionVersion();

        $versionInfo = $this->getDatabaseVersion();

        $old = $versionInfo[QFQ_VERSION_KEY] ?? false;

        $this->checkT3QfqConfig($old, $new);

        if ($dbUpdate === SYSTEM_DB_UPDATE_NEVER) {

            return;

        }

        if ($dbUpdate === SYSTEM_DB_UPDATE_ALWAYS || ($dbUpdate === SYSTEM_DB_UPDATE_AUTO && $new != $old)) {

            if (version_compare($old, '21.2.0') < 1 && !defined('PHPUNIT_QFQ')) {

                $this->enforceExistenceOfFormEditorReport();

            }

            $newFunctionHash = $this->updateSqlFunctions($versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] ?? '');

            if (null !== $newFunctionHash) {

                $versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] = $newFunctionHash;

                $versionInfo[QFQ_VERSION_KEY_FUNCTION_VERSION] = $new;

            }

            if (FEATURE_FORM_FILE_SYNC) {

                if ($this->db->existTable('Form')) {

                    // If Form table exists, import all form files so everything is up to date.

                    FormAsFile::importAllForms($this->db, true); // Note: Creates path and exports all forms first if the form directory does not exist.

                    // create Form table and export all system forms

                    $this->db->playSqlFile(__DIR__ . '/../../Sql/qfqDefaultTables.sql');

                    FormAsFile::exportAllForms($this->db);

                } else {

                    // If not, then create Form table and export all system forms

                    $this->db->playSqlFile(__DIR__ . '/../../Sql/qfqDefaultTables.sql');

                    FormAsFile::exportAllForms($this->db);

                    // import form files which existed before the new installation

                    FormAsFile::importAllForms($this->db, true);

                }

            } else {

                $this->db->playSqlFile(__DIR__ . '/../../Sql/qfqDefaultTables.sql');

            }

            // Perform dbUpdate only after qfqDefaultTables.sql has been played:

            // in case a new system table has been added between old and new and there is an dbUpdate on the new system table now.

            $this->dbUpdateStatements($old, $new);

            FormAsFile::importSystemForms($this->db);

            Logger::logMessage(date('Y.m.d H:i:s ') . ": Updated from QFQ version '$old' to '$new'", Path::absoluteQfqLogFile());

            // Finally write the latest version number.

            $versionInfo[QFQ_VERSION_KEY] = $new;

            $this->setDatabaseVersion($versionInfo);

        }

        if ($old === false) {

            // A complete new installation get's some extra tables

            $this->db->playSqlFile(__DIR__ . '/../../Sql/customTable.sql');

        }

        if (version_compare($old, '19.9.0') === -1) {

            $this->updateSpecialColumns();

            if (FEATURE_FORM_FILE_SYNC) {

                FormAsFile::exportAllForms($this->db);

            }

        }

        // run slug migration: show exception if old alias (e.g. "p:id=<Alias>") notation is used anywhere in QFQ code

        $state = $this->t3v10SlugMigration($versionInfo[QFQ_VERSION_KEY_SLUG_MIGRATION] ?? '');

        if (!is_null($state)) {

            $versionInfo[QFQ_VERSION_KEY_SLUG_MIGRATION] = $state;

            $this->setDatabaseVersion($versionInfo);

        }

    }

    /**

     * Check Typo3 config if values needs to be updated.

     * This is typically necessary if default config values change, to guarantee existing installations behave in legacy mode.

     *

     * @param $old

     * @param $new

     */

    private function checkT3QfqConfig($old, $new) {

        if ($new == $old || $old === false) {

            return;

        }

        if (version_compare($old, '20.2.0') == -1) {

            T3Handler::updateT3QfqConfig(SYSTEM_RENDER_BOTH, SYSTEM_RENDER); //Legacy behaviour.

        }

    }

    /**

     * Throws exception if no tt-content record exists which contains "file=_formEditor"

     *

     * @throws \CodeException

     * @throws \DbException

     * @throws \UserFormException

     * @throws \UserReportException

     */

    private function enforceExistenceOfFormEditorReport() {

        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);

        $sql = "SELECT `uid` FROM " . $dbT3 . ".`tt_content` WHERE `CType`='qfq_qfq' AND `deleted`=0 AND `bodytext` LIKE '%file=_formEditor%'";

        $res = $this->db->sql($sql);

        if (empty($res)) {

            $message = '<h2>FormEditor Report not found</h2>'

                . 'Please create a Typo3 QFQ content element with the following content:'

                . '<pre>file=_formEditor</pre>'

                . 'More information: See Section "FormEditor" of the QFQ Documentation.';

            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';

            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;

            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;

            throw new \DbException(json_encode($errorMsg), E_ERROR);

        }

    }

    /**

     * @throws \CodeException

     * @throws \DbException

     * @throws \UserFormException

     * @throws \UserReportException

     */

    private function t3v10SlugMigration($dBMigrationFlag) {

        ##################################

        ##### PHPUNIT: SKIP FUNCTION #####

        # php unit: skip

        if (defined('PHPUNIT_QFQ')) {

            return null;

        }

        #####################################################################################

        ##### ONLY RUN THIS FUNCTION IF TYPO3 VERSION 9 OR HIGHER AND NOT API ENDPOINT #####

        if ( ! (T3Handler::isTypo3Loaded() && T3Handler::useSlugsInsteadOfPageAlias()) ) {

            return null;

        }

        ##################################

        ##### COMPUTE MIGRATION MODE #####

        // read variable "RUN_PAGE_SLUG_MIGRATION_CHECK" from qfq.json

        $x = Config::get(FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK);

        $forceRunMigrationCheck = ($x === true || $x === "true");

        // if db flag "done" or "skip" then skip check

        if (!$forceRunMigrationCheck && ($dBMigrationFlag === QFQ_VERSION_KEY_SLUG_MIGRATION_DONE || $dBMigrationFlag === QFQ_VERSION_KEY_SLUG_MIGRATION_SKIP)) {

            return null;

        }

        // get value from GET parameter ACTION_SLUG_MIGRATION_UPDATE

        $actionSlugMigration = ($_GET[ACTION_SLUG_MIGRATION_UPDATE] ?? '');

        // if GET parameter "skipForever" then write "skip" to db

        if (!$forceRunMigrationCheck && $actionSlugMigration === ACTION_SLUG_MIGRATION_DO_SKIP_FOREVER) {

            return QFQ_VERSION_KEY_SLUG_MIGRATION_SKIP;

        }

        // if GET is "resume" and session is "pause" then resume.

        if (($_SESSION[ACTION_SLUG_MIGRATION_UPDATE] ?? '') === ACTION_SLUG_MIGRATION_DO_PAUSE && $actionSlugMigration === ACTION_SLUG_MIGRATION_DO_RESUME) {

            $_SESSION[ACTION_SLUG_MIGRATION_UPDATE] = '';

        }

        // if GET parameter or session is "pause" then skip check and show message

        if ($actionSlugMigration === ACTION_SLUG_MIGRATION_DO_PAUSE || ($_SESSION[ACTION_SLUG_MIGRATION_UPDATE] ?? '') === ACTION_SLUG_MIGRATION_DO_PAUSE) {

            $_SESSION[ACTION_SLUG_MIGRATION_UPDATE] = ACTION_SLUG_MIGRATION_DO_PAUSE;

            QuickFormQuery::$systemMessages[] = 'Page slug migration paused. '

                . '<a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_RESUME . '">Resume migration</a>';

            return null;

        }

        // is replacement mode?

        $doReplace = $actionSlugMigration === ACTION_SLUG_MIGRATION_DO_REPLACE;

        ############################################################

        ##### THROW EXCEPTION IF BACKEND USER IS NOT LOGGED IN #####

        if (!T3Info::beUserLoggedIn()) {

            Thrower::dbException('Page Slug Migration: Please log in to Typo3 backend and reload this page.');

        }

        ################################################

        ##### ONLY RUN THIS FUNCTION ONCE PER PAGE #####

        # Caching: If multiple QFQ tt_content elements are present then only process this function once

        static $hasAlreadyRunOnPage = false;

        if ($hasAlreadyRunOnPage) {

            Thrower::dbException('Page Slug Migration.', 'Please go to a page which contains only one QFQ content element.');

        }

        $hasAlreadyRunOnPage = true;

        ############################################

        ##### DEFINE SYMBOLS USED IN EXCEPTION #####

        $noSuggestionSymbol = '❎'; // signifies that there no suggestion has been found for this occurrence

        $replacedSymbol = '🆎'; // signifies that here something was replaced automatically. (only in replacement mode)

        $preventMatchSymbol = '🍼'; // not seen by user. Used to prevent already matched strings to match again.

        #######################################

        ##### DEFINE SLUG LOOKUP FUNCTION #####

        // check whether column pages.zzz_deleted_alias exists

        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);

        $aliasColumn = 'zzz_deleted_alias';

        $tableDefinition = $this->db->sql("SHOW FIELDS FROM `$dbT3`.`pages`", ROW_EXPECT_GE_1);

        $aliasColumnExists = 0 < count(array_filter($tableDefinition, function ($c) use ($aliasColumn) {return $c['Field'] === $aliasColumn;}));

        // Get id, slug and alias of all Typo3 pages

        $pages = $this->db->sql("SELECT `uid`, `slug`" . ($aliasColumnExists ? ", `$aliasColumn`" : '') . " FROM `" . $dbT3 . "`.`pages` WHERE `deleted`=0;");

        // define slug lookup function

        $getSlug = function (string $aliasOrId) use ($aliasColumn, $aliasColumnExists, $pages, $noSuggestionSymbol) {

            // remove ?id=

            if (OnString::strStartsWith($aliasOrId, '?id=')) {

                $aliasOrId = substr($aliasOrId, 4);

            } else if (OnString::strStartsWith($aliasOrId, 'index.php?id=')) {

                $aliasOrId = substr($aliasOrId, 13);

            }

            if (is_numeric($aliasOrId)) {

                // if its an id (number), get the page with that id

                $uidMatches = array_filter($pages, function ($p) use ($aliasOrId) {return $p['uid'] === intval($aliasOrId);});

                $page = reset($uidMatches);

                return $page !== false ? $page['slug'] : $noSuggestionSymbol;

            } else {

                // search alias in page slugs. ('_' was automatically replaced by '-' Typo3 v9 Migration)

                $slugMatches = array_filter($pages, function ($p) use ($aliasOrId) {

                    return ($p['slug'] === '/' . str_replace('_', '-', $aliasOrId)) || ($p['slug'] === '/' . $aliasOrId);});

                $slugs = array_map(function ($p) {return $p['slug'];}, $slugMatches);

                if (1 === count(array_unique($slugs))) {

                    return reset($slugs);

                }

                // search alias in old alias column ('zzz_deleted_alias') if it exists

                if ($aliasColumnExists) {

                    $aliasMatches = array_filter($pages, function ($p) use ($aliasColumn, $aliasOrId) {return $p[$aliasColumn] === $aliasOrId;});

                    $slugs = array_map(function ($p) {return $p['slug'];}, $aliasMatches);

                    if (1 === count(array_unique($slugs))) {

                        return reset($slugs);

                    }

                }

            }

            return $noSuggestionSymbol;

        };

        ##################################

        ##### DEFINE REGEX PATTERNS ######

        // Patterns for which we might be able to give a suggestion

        $patternsWithSuggestions = [

            '/([\'"\|]p:)((?:id=)?[a-zA-Z0-9_\-]*)([&\|"\'])/s',

            '/([\'"\|]p:)((?:id=)?{{(?:pageAlias|pageId))(:T[0E]*}}[&\|"\'])/s',

            '/(href=[\'"])((?:index\.php)?\?id=[a-zA-Z0-9_\-]*)([&"\'])/s',

            '/(href=[\'"])((?:index\.php)?\?id={{(?:pageAlias|pageId))(:T[0E]*}}[&"\'])/s'

            ];

        // Patterns for which we can't make a suggestion (applied after the the patterns with suggestions were replaced)

        $patternsNeedManualFix = [

            '/({{)((?:pageAlias|pageId))(:T[BCDEFLMPRSUVY0]*}})/s',

            '/([\'"\|]u:\?)(\S+?)([&\|"\'])/s',

//            '/([\'"\|]U:)(id=\S+?)([&\|"\'])/s',   // already caught by a pattern bellow

//            '/([\'"\|]u:\S+?[\?&])(id=\S+?)([&\|"\'])/s',  // already caught by a pattern bellow

            '/([&\|"\'\?:]id=)([^' . $noSuggestionSymbol . '\s]+?)([&\|"\'])/s'

        ];

        ##################################################

        ##### DEFINE SUGGESTION GENERATING FUNCTION ######

        $suggestionForMatch = function (string $prefix, string $match, string $postfix) use ($getSlug, $noSuggestionSymbol) {

            if (OnString::strEndsWith($prefix, 'p:') && !OnString::strContains($match, '{')) {

                // cases  'p:id=alias&  or  'p:id=5' etc. >>> remove id=

                $match = OnString::strStartsWith($match, 'id=') ? substr($match, 3) : $match;

                // replace & with ? behind slug

                $postfix = ($postfix === '&' ? '?' : $postfix);

                // cases  'p:alias&  or  'p:5' etc.  >>>  <slug>

                return [$prefix, $getSlug($match), $postfix];

            } elseif (OnString::strEndsWith($prefix, 'p:') && OnString::strContains($match, '{{')) {

                // case 'p:id={{pageAlias:T}}&  >>>  remove id=

                $match = OnString::strStartsWith($match, 'id=') ? substr($match, 3) : $match;

                // replace & with ? behind variable

                $trailing = substr($postfix, -1);

                $postfix = substr($postfix, 0, -1) . ($trailing === '&' ? '?' : $trailing);

                # case 'p:{{pageAlias:T}}&  >>>  pageSlug

                return [$prefix, '{{' . TYPO3_PAGE_SLUG, $postfix];

            } elseif (OnString::strStartsWith($prefix, 'href=') && !OnString::strStartsWith($postfix, ':T')) {

                // replace & with ? behind slug

                $postfix = ($postfix === '&' ? '?' : $postfix);

                #  case  href="?id=alias&  or href="?id=5&  >>>  {{baseUrlLang:Y}}/<slug>

                $slug = $getSlug($match);

                return [$prefix, '{{' . SYSTEM_BASE_URL_LANG . ':Y}}' . (OnString::strStartsWith($slug, '/') ? '' : '/') . $slug, $postfix];

            } elseif (OnString::strStartsWith($prefix, 'href=') && OnString::strStartsWith($postfix, ':T')) {

                // replace & with ? behind variable

                $trailing = substr($postfix, -1);

                $postfix = substr($postfix, 0, -1) . ($trailing === '&' ? '?' : $trailing);

                // case  href="index.php?id={{pageAlias:T}}  or  href="?id={{pageAlias:T}}  >>>  {{baseUrlLang:Y}}/{{pageSlug

                return [$prefix, '{{' . SYSTEM_BASE_URL_LANG . ':Y}}/{{' . TYPO3_PAGE_SLUG, $postfix];

            } else {

                return [$prefix, $noSuggestionSymbol . 'ERROR', $postfix]; // This should never happen

            }

        };

        ##################################################

        ##### GET QFQ CODE FROM DB (REPORT AND FORM) #####

        # fill the array $qfqCodeBlobs with qfq code

        // Array keys for the qfq code collection array $qfqCodeBlobs

        $KEY_TITLE = 'title'; // title which will be shown in exception for that code blob

        $KEY_CONTENT = 'content'; // the content of the blob

        $KEY_SQL_UPDATE = 'sql_update'; // sql update statement for that blob

        // get reports from tt_content.bodytext

        $reports = $this->db->sql("SELECT tt.`pid`, tt.`uid`, tt.`header`, tt.`bodytext`, tt.`hidden`, p.`hidden` AS pageHidden FROM `" . $dbT3 . "`.`tt_content` AS tt, `" . $dbT3 . "`.`pages` AS p WHERE tt.`CType`='qfq_qfq' AND tt.`deleted`=0 AND p.`deleted`=0 AND p.uid=tt.pid");

        $qfqCodeBlobs = array_map(function($r) use ($dbT3, $KEY_SQL_UPDATE, $KEY_CONTENT, $KEY_TITLE) {

            $maybeHidden = (intval($r['hidden']) !== 0) || (intval($r['pageHidden']) !== 0);

            return [

                $KEY_TITLE => 'QFQ Report on pid=' . $r['pid'] . ' with uid=' . $r['uid'] . ' and header: ' . $r['header']

                    . ($maybeHidden ? '<br><small>Note: Content element is probably hidden / not in use.</small>' : ''),

                $KEY_CONTENT => $r['bodytext'],

                $KEY_SQL_UPDATE => "UPDATE `$dbT3`.`tt_content` SET `bodytext` = ? WHERE uid=" . $r['uid'] . ";"

        ];}, $reports);

        // get Forms

        $formColumnsToCheck = [

            'title',

            'multiMode',

            'multiSql',

            'multiDetailForm',

            'multiDetailFormParameter',

            'parameter',

            'parameterLanguageA',

            'parameterLanguageB',

            'parameterLanguageC',

            'parameterLanguageD',

            'forwardPage'

        ];

        $forms = $this->db->sql("SELECT `f`.`id`, `f`.`name`, `f`.`" . join("`, `f`.`", $formColumnsToCheck) . "` FROM `Form` AS f");

        foreach ($forms as $i => $form) {

            foreach ($formColumnsToCheck as $j => $column) {

                $qfqCodeBlobs[] = [

                    $KEY_TITLE => "Column '$column' of table Form with id=" . $form['id'] . " and name=" . $form['name'],

                    $KEY_CONTENT => $form[$column],

                    $KEY_SQL_UPDATE => "UPDATE Form SET `$column`=? WHERE `id`=" . $form['id'] . ";"

                ];

            }

        }

        // get FormElements

        $formElementColumnsToCheck = [

            'label',

            'modeSql',

            'checkPattern',

            'onChange',

            'maxLength',

            'note',

            'tooltip',

            'placeholder',

            'value',

            'sql1',

            'parameter',

            'parameterLanguageA',

            'parameterLanguageB',

            'parameterLanguageC',

            'parameterLanguageD',

            'clientJs',

            'feGroup'

        ];

        $formElements = $this->db->sql("SELECT `fe`.`id`, `fe`.`" . join("`, `fe`.`", $formElementColumnsToCheck) . "` FROM `FormElement` AS fe");

        foreach ($formElements as $i => $formElement) {

            foreach ($formElementColumnsToCheck as $j => $column) {

                $qfqCodeBlobs[] = [

                    $KEY_TITLE => "Column '$column' of table FormElement with id=" . $formElement['id'],

                    $KEY_CONTENT => $formElement[$column],

                    $KEY_SQL_UPDATE => "UPDATE FormElement SET `$column`=? WHERE `id`=" . $formElement['id'] . ";"

                ];

            }

        }

        #####################

        ##### MAIN LOOP #####

        # find occurrences of the patterns

        # replace occurrences with suggestions and update database if replacement mode is active

        // placeholders will be replaced with html after using htmlentities() on qfq code

        $placeholderBegin = '%%%BEGIN-ALIAS-SUGGESTION%%%';

        $placeholderArrow = '%%%ARROW-ALIAS-SUGGESTION%%%';

        $placeholderEnd = '%%%END-ALIAS-SUGGESTION%%%';

        $message = ''; // content of exception

        $allMatches = []; // collect all matching lines for overview at beginning of exception

        foreach ($qfqCodeBlobs as $i => $qfqCode) {

            // make sure the control characters are not used in the QFQ code

            if ($qfqCode[$KEY_CONTENT] && ((OnString::strContains($qfqCode[$KEY_CONTENT], $noSuggestionSymbol) || OnString::strContains($qfqCode[$KEY_CONTENT], $replacedSymbol)))) {

                Thrower::dbException('Page Slug Migration.', "Unicode character $noSuggestionSymbol or $replacedSymbol found in" . $qfqCode[$KEY_TITLE] .  ". The page slug migration script can't continue since it uses those characters as control characters. Please temporarily replace those characters.");

            }

            // add suggestion marks to all occurrences of regex patterns in $patternsWithSuggestions

            $replaced_with_placeholder = preg_replace_callback($patternsWithSuggestions, function (array $matches) use ($preventMatchSymbol, $doReplace, $KEY_SQL_UPDATE, $qfqCode, $replacedSymbol, $suggestionForMatch, $placeholderArrow, $getSlug, $placeholderBegin, $placeholderEnd, $noSuggestionSymbol, &$allMatches) {

                $fullMatch = $matches[0];

                $prefix = $matches[1];

                $match = $matches[2];

                $postfix = $matches[3];

                // get suggestion

                list($prefix_suggestion, $match_suggestion, $postfix_suggestion) = $suggestionForMatch($prefix, $match, $postfix);

                if ($doReplace) {

                    // if replacement mode is active replace occurrence with suggestion (add trailing replacement symbol)

                    if (!OnString::strContains($match_suggestion, $noSuggestionSymbol)) {

                        // only replace if there actually is a suggestion

                        $replacement = $prefix_suggestion . $match_suggestion . $postfix_suggestion . $replacedSymbol;

                        $allMatches[] = $fullMatch . ' ➡ ' . $prefix_suggestion . $match_suggestion . $postfix_suggestion . $replacedSymbol;

                    } else {

                        // if there is no suggestion, keep as is

                        $replacement = $fullMatch;

                    }

                } else {

                    # if not replacement mode then replace occurrence with "OCCURRENCE -> SUGGESTION" (using placeholders)

                    // protect matched string from being matched again by interlacing it with $preventMatchSymbol ( "string" -> "XsXtXrXiXnXg" )

                    // symbol will be removed before output

                    $fullMatchProtected = join(array_map(function ($c) use ($preventMatchSymbol) {return $preventMatchSymbol . $c;}, str_split($fullMatch)));

                    $replacement = $placeholderBegin . $fullMatchProtected . $placeholderArrow . $prefix_suggestion . $match_suggestion . $postfix_suggestion . $placeholderEnd;

                    $allMatches[] = $fullMatch . ' ➡ ' . $prefix_suggestion . $match_suggestion . $postfix_suggestion;

                }

                return $replacement;

            }, $qfqCode[$KEY_CONTENT]);

            // find those patterns where we can't give a suggestion

            if (!$doReplace) {

                $replaced_with_placeholder = preg_replace($patternsNeedManualFix, $placeholderBegin . '${1}' . '${2}'. '${3}' . $placeholderArrow . $noSuggestionSymbol . $placeholderEnd, $replaced_with_placeholder);

            }

            // do the suggestion replacement if in replacement mode and construct error message

            if (strpos($replaced_with_placeholder, $placeholderBegin) !== false) {

                // We are in "display" mode, we show suggestions, no replacements are made

                $message .= '<hr><span style="font-weight: bold; color: blue;">' . $qfqCode[$KEY_TITLE] . '</span><br><br>';

                $message .= $qfqCode[$KEY_SQL_UPDATE];

                $message .= '<pre>' . str_replace([$preventMatchSymbol, $placeholderBegin, $placeholderArrow,  $placeholderEnd, "\r\n", "\n"], [

                    '',

                    '<span style="font-weight: bold; color: red;">',

                    '</span> ➡ <span style="font-weight: bold; color: green;">',

                    '</span>',

                    "<br>", "<br>"

                ], htmlentities($replaced_with_placeholder)) . '</pre>';

            } elseif (strpos($replaced_with_placeholder, $replacedSymbol) !== false) {

                // We are in "replacement" mode, show what is been replaced and do the replacement

                $message .= '<hr><span style="font-weight: bold; color: blue;">' . $qfqCode[$KEY_TITLE] . '</span><br><br>';

                $message .= '<pre>' . str_replace(["\r\n", "\n"], ["<br>", "<br>"], htmlentities($replaced_with_placeholder)) . '</pre>';

                // Check again if we are actually in replacement mode, just to be sure

                if ($doReplace) {

                    // DATABASE CHANGE! here the database is updated with the replaced suggestions

                    $this->db->sql($qfqCode[$KEY_SQL_UPDATE], ROW_REGULAR, [str_replace($replacedSymbol, '', $replaced_with_placeholder)]);

                }

            }

        }

        ###################################

        ##### IF NO OCCURRENCES FOUND #####

        // if no occurrences were found then set db flag to "done"

        if ($message === '' && !$doReplace) {

            if ($forceRunMigrationCheck) {

                Thrower::dbException('Page Slug Migration.', 'No occurrences of page alias where found. Please disable the setting ' . FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK . ' in ' . Path::absoluteConf(CONFIG_QFQ_JSON));

            }

            return QFQ_VERSION_KEY_SLUG_MIGRATION_DONE;

        }

        ###############################################

        ##### FINALIZE THE EXCEPTION AND THROW IT #####

        $reportFilePostfix = '_page_slug_migration.html';

        if ($doReplace) {

            // save report to file

            $reportPath = Path::absoluteLog() . '/' . date("YmdHi") . $reportFilePostfix;

            Logger::logMessage('<meta charset="UTF-8">' . "<h2>Report: Replacement Marked with $replacedSymbol in Code</h2>" . $message, $reportPath);

            $message = ''

                . '<h2>Automatic Replacement Completed</h2>'

                . 'The following report has been saved to <br>' .  $reportPath

                . '<br><br>Click <a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=null">check-again</a> to search for still existing usages of page alias.'

                . '<h2>Overview Replacement Suggestions</h2>'

                . join('<br>', array_unique($allMatches))

                . ' <br><br>(list might not be complete)'

                . "<h2>Report: Replacement Marked with $replacedSymbol in Code</h2>"

                . $message;

        } else {

            $message = ''

                . '<h2>Page Slug Migration</h2>'

                . 'Usages of page alias found. The Typo3 feature "page alias" has been replaced by the "page slug" feature and can no longer be used in links with "?id=[alias]".'

                . '<h2>SOLUTION</h2>'

                . "<ul>"

                . '<li>The special colums such as "_link" now treat the "p:" parameter value as a page slug.</li>'

                . '<li>Use {{pageSlug:T}} instead of {{pageAlias:T}} or {{pageId:T}} in the _link columns.  E.g. "p:{{pageSlug}}?foo=bar" AS link .</li>'

                . '<li>Use {{baseUrlLang:Y}}/{{pageSlug:T}} instead of {{pageAlias:T}} or {{pageId:T}} in hardcoded ' . htmlentities("<a>") . ' tags. E.g. href="{{baseUrlLang:Y}}/{{pageSlug:T}}?foo=bar".</li>'

                . '<li>Replace hardcoded aliases in QFQ code with the slugs of the pages. E.g. "p:/some/page?foo=bar" AS _link.</li>'

                . '<li>Replace "U: ... &id=[alias]& ... " with "p:[slug]? ... " for all sepecial columns except _paged. I.e. "U: ... &id=[alias]& ... " becomes "p:[slug]? ... ". </li>'

                . '<li>Hint: Typo3 replaces "_" with "-" when converting an alias to a slug during the Typo3 version 9 upgrade.</li>'

                . '<li>Hint: A page slug always starts with a slash "/" and QFQ expects the slash to be there.</li>'

                . '<li>Note: After the page slug comes a "?" not a "&". E.g. "p:/some/page?foo=bar" AS _link. </li>'

                . "</ul>"

                . '<h2>AUTO SUGGESTIONS</h2>'

                . 'In the report below the suggested changes are prominently marked with colour.'

                . "<br> If there is a $noSuggestionSymbol then there is no suggestion and you will have to fix it manually."

                . "<br> Tip: use ctrl+f and copy $noSuggestionSymbol into the search bar to quickly jump between matches."

                . '<h2>ACTIONS</h2>'

                . "<ul>"

                . '<li><a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_REPLACE . '">Auto replace</a>'

                . ' occurrences of page aliases and page ids with the suggested page slug.'

                . '<br>A report file with name "[timestamp]'. $reportFilePostfix .'" will be saved to "' . Path::absoluteLog() . '" after the automatic replacement. </li>'

                . ' <li>To use the Form Editor you can '

                . '<a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_PAUSE . '">pause the migration temporarliy</a>.</li>'

                . '<li>You may also '

                . '<a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_SKIP_FOREVER . '">skip this check forever</a> but your app will probably be broken.'

                . '<br>To reenable the check you can set the config variable ' . FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK . ' to "true" in ' . Path::absoluteConf(CONFIG_QFQ_JSON) . '</li>'

                . "</ul>"

                . ($forceRunMigrationCheck ? '<br><br>Note: setting ' . FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK . ' is active in qfq.json.' : '')

                . '<h2>Overview Replacement Suggestions</h2>'

                . join('<br>', array_unique($allMatches))

                . '<h2>Report: Occurrences Marked with Red Color in Code</h2>'

                . $message;

        }

        Thrower::dbException('Page Slug Migration.', $message, false, ERROR_PLAY_SQL_FILE);

    }

    /**

     * Check if there are special columns without prepended underscore in the QFQ application. If yes, then throw an error.

     * A link is provided to automatically prepend all found special columns. And another link to skip the auto-replacement.

     *

     * @throws \CodeException

     * @throws \DbException

     * @throws \UserFormException

     */

    private function updateSpecialColumns() {

        // Prepare regex patterns to find "AS <special column name>"

        $special_columns = ['link', 'exec', 'Page', 'Pagec', 'Paged', 'Pagee', 'Pageh', 'Pagei', 'Pagen', 'Pages'

            , 'page', 'pagec', 'paged', 'pagee', 'pageh', 'pagei', 'pagen', 'pages', 'yank', 'Pdf', 'File', 'Zip'

            , 'pdf', 'file', 'zip', 'excel', 'savePdf', 'thumbnail', 'monitor', 'mimeType', 'fileSize', 'nl2br'

            , 'htmlentities', 'striptags', 'XLS', 'XLSs', 'XLSb', 'XLSn', 'bullet', 'check', 'img', 'mailto'

            , 'sendmail', 'vertical'];

        $make_pattern = function ($column) {

            return '/([aA][sS]\s+)(' . $column . ')/s';

        };

        $patterns = array_map($make_pattern, $special_columns);

        // Prepare search and replace

        $placeholder = '%%%UNDERLINE%%%';  // used temporarily to mark where '_' should go

        $actionSpecialColumn = $_GET[ACTION_SPECIAL_COLUMN_UPDATE] ?? ''; // get parameter to decide whether to execute the replacement

        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);

        $message = ''; // error message in case an old special column is found

        // TT_CONTENT tt_content.bodytext

        $message_fe = '';

        if (defined('PHPUNIT_QFQ')) {

            $res = array();

        } else {

            $res = $this->db->sql("SELECT `uid`, `header`, `bodytext` FROM `" . $dbT3 . "`.`tt_content` WHERE `CType`='qfq_qfq' AND `deleted`=0;");

        }

        foreach ($res as $i => $tt_content) {

            $replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['bodytext']);

            if (strpos($replaced_placeholder, $placeholder) !== false) {

                if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {

                    $replace = str_replace($placeholder, '_', $replaced_placeholder);

                    $query = "UPDATE `" . $dbT3 . "`.`tt_content` SET `bodytext`='" . addslashes($replace) . "' WHERE `uid`='" . $tt_content['uid'] . "'";

                    $this->db->sql($query);

                }

                $message_fe .= '<hr><b>' . $tt_content['header'] . ' [uid:' . $tt_content['uid'] . ']</b><br><br>';

                $message_fe .= str_replace($placeholder,

                    '<span style="font-weight: bold; color: red;">>>>_</span>',

                    htmlentities($replaced_placeholder));

            }

        }

        if ($message_fe != '') {

            $message .= '<hr><h3>Typo3 Table: tt_content (column: bodytext)</h3>' . $message_fe;

        }

        // FORM ELEMENTS FormElement.value, FormElement.note

        $message_ttc = '';

        if (defined('PHPUNIT_QFQ')) {

            $res = array();

        } else {

            $res = $this->db->sql("SELECT `fe`.`id`, `fe`.`name`, `fe`.`value`, `fe`.`note` FROM `FormElement` AS fe WHERE `fe`.`type`='note' AND `fe`.`value` LIKE '#!report%' OR `fe`.`note` LIKE '%#!report%';");

        }

        foreach ($res as $i => $tt_content) {

            foreach (['value', 'note'] as $j => $columnName) {

                $replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content[$columnName]);

                if (strpos($replaced_placeholder, $placeholder) !== false) {

                    if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {

                        $replace = str_replace($placeholder, '_', $replaced_placeholder);

                        $query = "UPDATE `FormElement` SET `" . $columnName . "`='" . addslashes($replace) . "' WHERE `id`='" . $tt_content['id'] . "'";

                        $this->db->sql($query);

                    }

                    $message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.' . $columnName . ')</b><br><br>';

                    $message_ttc .= str_replace($placeholder,

                        '<span style="font-weight: bold; color: red;">>>>_</span>',

                        htmlentities($replaced_placeholder));

                }

            }

        }

        if ($message_ttc != '') {

            $message .= '<hr><h3>QFQ Table: FormElement (columns: value and note)</h3>' . $message_ttc;

        }

        // show error message or save log

        if ($message != '') {

            if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {

                // save log file

                $message = '<h1>Special column names replaced</h1>The following special column names were replaced.<hr>' . $message;

                Logger::logMessage($message, Path::absoluteLog() . '/' . date("YmdHi") . '_special_columns_auto_update.html');

            } elseif ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE) {

                // do nothing

            } else {

                // show error

                $message = $actionSpecialColumn

                    . '<h2>Special Column names without prepended underscore found.</h2>'

                    . ' Those are not supported any longer.'

                    . '<h2>SOLUTION</h2>'

                    . 'Click <a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_REPLACE))) . '">Auto-Replace</a>'

                    . ' to automatically prepend the found column names with an underscore.'

                    . ' In the report below the missing underscores are marked by "<span style="font-weight: bold; color: red;">>>>_</span>".'

                    . ' This report will be saved in ' . Path::absoluteLog() . ' after the automatic replacement.'

                    . ' <br><br>To update qfq without changing the special columns (your app will probably be broken): '

                    . '<a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE))) . '">Skip Auto-Replace</a>'

                    . '<h2>Report</h2>'

                    . $message;

                $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq. ';

                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;

                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;

                throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);

            }

        }

    }

    /**

     * @param $oldFunctionsHash

     *

     * @return string

     *

     * @throws \DbException

     * @throws \UserFormException

     */

    private function updateSqlFunctions($oldFunctionsHash) {

        if (ACTION_FUNCTION_UPDATE_NEVER === $oldFunctionsHash) {

            return null;

        }

        $actionFunction = $_GET[ACTION_FUNCTION_UPDATE] ?? '';

        if ($actionFunction === ACTION_FUNCTION_UPDATE_NEXT_UPDATE) {

            return ACTION_FUNCTION_UPDATE_NOT_PERFORMED;

        } elseif ($actionFunction === ACTION_FUNCTION_UPDATE_NEVER) {

            return ACTION_FUNCTION_UPDATE_NEVER;

        }

        $functionSql = file_get_contents(__DIR__ . '/../../Sql/' . QFQ_FUNCTION_SQL);