<?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 'qfqTableDefault.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, $t3ConfigQfq = array()) {

        $new = $this->getExtensionVersion();
        $versionInfo = $this->getDatabaseVersion();
        $old = $versionInfo[QFQ_VERSION_KEY] ?? false;

        $this->checkT3QfqConfig($old, $new, $t3ConfigQfq);
        // Check if
        // a) update is not wanted
        // b) we're called by API (than some things are not defined)
        if ($dbUpdate === SYSTEM_DB_UPDATE_NEVER || defined('QFQ_API')) {
            return;
        }



        if ($dbUpdate === SYSTEM_DB_UPDATE_ALWAYS || $dbUpdate === SYSTEM_DB_UPDATE_ONCE || ($dbUpdate === SYSTEM_DB_UPDATE_AUTO && $new != $old)) {
            $this->ensureDirectDownloadSymlink();

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

            $versionInfo[QFQ_VERSION_KEY_TABLE_SPLIT] = $this->checkAndSetTableSplitFlag($versionInfo);

            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/' . QFQ_TABLE_DEFAULT_SQL);
                    FormAsFile::exportAllForms($this->db);
                } else {
                    // If not, then create Form table and export all system forms
                    $this->db->playSqlFile(__DIR__ . '/../../Sql/' . QFQ_TABLE_DEFAULT_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/' . QFQ_TABLE_DEFAULT_SQL);
            }

            // Perform dbUpdate only after qfqTableDefault.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 (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);
        }

        if ($dbUpdate === SYSTEM_DB_UPDATE_ONCE) {
            T3Handler::updateT3QfqConfig(SYSTEM_DB_UPDATE_AUTO, SYSTEM_DB_UPDATE);
        }
    }

    /**
     * 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, $t3ConfigQfq) {
        $dirty = false;
        if ($new == $old || $old === false) {
            return;
        }

        if (version_compare($old, '20.2.0') == -1) {
            $t3ConfigQfq[SYSTEM_RENDER_BOTH] = SYSTEM_RENDER;
            $dirty = true;
        }

        if (version_compare($old, '22.12.0') == -1) {
            $t3ConfigQfq[SYSTEM_DO_NOT_LOG_COLUMN] = SYSTEM_DO_NOT_LOG_COLUMN_DEFAULT;
            $dirty = true;
        }

        if ($dirty) {
            T3Handler::updateT3QfqConfig($t3ConfigQfq); //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%' OR `bodytext` LIKE '%file={{file:SU:::_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 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.`uid`, tt.`header`, tt.`bodytext`, tt.`hidden`, p.`hidden` AS pageHidden,"
            . " p.`title` AS pageTitle, p.`uid` AS pageId 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 ORDER BY p.uid, tt.sorting");
        $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 with uid <u>' . $r['uid'] . '</u> and header <u>' . $r['header']
                    . '</u> on page <u>' . $r['pageId'] . '/' . $r['pageTitle'] . '</u>'
                    . ($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'
        ];
        $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 (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 / id 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 QFQ 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 special 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 color.'
                . "<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>'

                . '<li>The state of the migration is saved in the comment field of table <pre>Form</pre> (together with QFQ version and hash of functions).'
                . ' To manually deactive the migration wizard add <pre>&pageSlugMigration=skip</pre> at the end of the comment field.</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_DEFAULT_SQL);
        $functionHash = hash('md5', $functionSql);

        if ($functionHash === $oldFunctionsHash) {
            return null;
        }

        $query = str_replace('%%FUNCTIONSHASH%%', $functionHash, $functionSql);
        if (stripos($query, 'delimiter')) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error in file " . QFQ_FUNCTION_DEFAULT_SQL . ": The keyword DELIMITER is present " .
                "in " . QFQ_FUNCTION_DEFAULT_SQL . ", this usually leads to errors when trying to execute it on the database.";
            throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
        }

        try {
            $this->db->playMultiQuery($query);
            $functionsHashTest = $this->db->sql('SELECT GETFUNCTIONSHASH() AS res;', ROW_EXPECT_1)['res'];
        } catch (\DbException $e) {
            $functionsHashTest = null;
        } catch (\CodeException $e) {
            $functionsHashTest = null;
        }

        $qfqFunctionSqlRelToApp = Path::appToExt('Classes/Sql/' . QFQ_FUNCTION_DEFAULT_SQL);

        if ($functionHash !== null && $functionsHashTest === $functionHash) {
            return $functionHash;
        } else {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] =
                "Failed to play " . QFQ_FUNCTION_DEFAULT_SQL . ", probably not enough <a href='https://mariadb.com/kb/en/library/stored-routine-privileges/'>permissions</a> for the qfq mysql user. " .
                "Possible solutions: <ul>" .
                '<li>Grant SUPER, CREATE ROUTINE, ALTER ROUTINE privileges to qfq mysql user temporarily.</li>' .
                '<li>Play the following file manually on the database: ' .
                '<a href="' . $qfqFunctionSqlRelToApp . '">' . $qfqFunctionSqlRelToApp . '</a><br>and grant the qfq mysql user execution privileges on the sql functions.</li>' .
                '<li><a href="?' . http_build_query(array_merge($_GET, array(ACTION_FUNCTION_UPDATE => ACTION_FUNCTION_UPDATE_NEXT_UPDATE))) . '">Click here</a> to skip the sql functions update until next qfq release update</li>' .
                '<li><a href="?' . http_build_query(array_merge($_GET, array(ACTION_FUNCTION_UPDATE => ACTION_FUNCTION_UPDATE_NEVER))) . '">Click here</a> to skip the sql functions update forever</li>' .
                '</ul>' .
                "To enable the sql functions update again you can delete the parameter 'functionsHash' in the table comments of the table 'Form'.";
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
            throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
        }
    }

    /**
     * @param $path
     *
     * @return array
     * @throws \CodeException
     */
    private function readUpdateData($path) {

        if (!@file_exists($path)) {
            throw new \CodeException("File '$path'' not found", ERROR_IO_OPEN);
        }

        $UPDATE_ARRAY = null;

        include $path;

        return $UPDATE_ARRAY;

    }

    /**
     * Check if Table Split exists and set the flag depending on if it exists or not.
     *
     * @return string "1"|"0"
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function checkAndSetTableSplitFlag($versionInfo = false): string {
        $splitTableExists = $this->db->sql("SHOW TABLES LIKE '" . TABLE_NAME_SPLIT . "'");
        $versionArray = is_array($versionInfo) ? $versionInfo : $this->getDatabaseVersion();

        if (count($splitTableExists) === 1) {
            // Table Found
            $versionArray[QFQ_VERSION_KEY_TABLE_SPLIT] = QFQ_VERSION_KEY_TABLE_SPLIT_ON;
        } else {
            // Table Not Found
            $versionArray[QFQ_VERSION_KEY_TABLE_SPLIT] = QFQ_VERSION_KEY_TABLE_SPLIT_OFF;
        }

        // Version info was not passed called by getTableSplitFlag()
        if ($versionInfo === false) {
            $this->db->sql("ALTER TABLE `Form` COMMENT = '" . http_build_query($versionArray) . "'");
        }

        return $versionArray[QFQ_VERSION_KEY_TABLE_SPLIT];
    }

    /**
     * Get and set the Table Slit flag from Form Comment to Store System (Y) for further use.
     *
     * If the Flag has not been set, set it.
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function getTableSplitFlag(): void {
        $versionInfo = $this->getDatabaseVersion();
        $flag = $versionInfo[QFQ_VERSION_KEY_TABLE_SPLIT] ?? false;
        if ($flag === false) {
            $flag = $this->checkAndSetTableSplitFlag();
        }
        // Set Flag to store System
        $this->store->setVar(QFQ_VERSION_KEY_TABLE_SPLIT, $flag,STORE_SYSTEM);
    }

    /**
     * Play all update statement with version number are '>' than $old and '<=' to $new.
     *
     * @param $old
     * @param $new
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function dbUpdateStatements($old, $new) {

        $dummy = array();

        if ($new == '' || $old === false || $old === null) {
            return;
        }
        $updateArray = $this->readUpdateData(__DIR__ . '/DatabaseUpdateData.php');

        $apply = false;
        foreach ($updateArray as $key => $sqlStatements) {

            // Search starting point to apply updates. Do not apply updates for $key>$new
            $rc1 = version_compare($key, $old);
            $rc2 = version_compare($key, $new);
            if ($rc1 == 1 && $rc2 != 1) {
                $apply = true;
            }

            if ($apply) {
                // Play Statements
                foreach ($sqlStatements as $sql) {
                    $this->db->sql($sql, ROW_REGULAR, array(),
                        "Apply updates to QFQ database. Installed version: $old. New QFQ version: $new",
                        $dummy, $dummy, [1060] /* duplicate column name */);
                }
                // Remember already applied updates - in case something breaks and the update has to be repeated.
                $this->setDatabaseVersion($new);
            }
        }
    }

    private function ensureDirectDownloadSymlink(): void {
        $symlinkPath = Path::absoluteApp(API_DIRECT_DOWNLOAD_PHP);
        $targetPath = Path::appToApi(API_DOWNLOAD_PHP);
        if (!file_exists($symlinkPath)) {
            symlink($targetPath, $symlinkPath);
        }
    }
}