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

namespace IMATHUZH\Qfq\Core\Database;

use IMATHUZH\Qfq\Core\Exception\Thrower;
use IMATHUZH\Qfq\Core\Helper\BindParam;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
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;
use mysqli_sql_exception;

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

    /**
     * @var \array
     */
    private static $mysqliCache = [];

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

    /**
     * @var \mysqli
     */
    private $mysqli = null;

    /**
     * @var \mysqli_stmt
     */
    private $mysqli_stmt = null;

    /**
     * @var \mysqli_result
     */
    private $mysqli_result = null;

    /**
     * @var string
     */
    private $sqlLogAbsolute = '';

    /**
     * @var array
     */
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];

    private $dbName = null;
    private $dbIndex = null;

    /**
     * Returns data base handle with requested index from cache.
     * If not exists: open database and store the new handle in database cache.
     *
     * @param string $dbIndex Typically '1' for Data, optional 2 for external Form/FormElement
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct($dbIndex = DB_INDEX_DEFAULT, $config = array()) {

        if (empty($dbIndex)) {
            $dbIndex = DB_INDEX_DEFAULT;
        }

        $this->dbIndex = $dbIndex;

        $this->store = Store::getInstance();
        $storeSystem = $this->store->getStore(STORE_SYSTEM);

        $this->sqlLogAbsolute = Path::absoluteSqlLogFile();
        $dbInit = $storeSystem[SYSTEM_DB_INIT];

        // In case if typo3 database is asked, use given config for credentials instead of system store data.
        if (count($config) > 1) {
            $storeSystem = $config;
        }

        $config = $this->getConnectionDetails($dbIndex, $storeSystem);
        $this->dbName = $config[SYSTEM_DB_NAME];

        $this->mysqli = self::$mysqliCache[$dbIndex] ?? null;
        if ($this->mysqli === null) {
            $this->mysqli = $this->dbConnect($config);
            self::$mysqliCache[$dbIndex] = $this->mysqli;
        }

        // DB Init
        if ($dbInit !== false && $dbInit != '') {
            $arr = explode(';', $dbInit);
            foreach ($arr as $sql) {
                $sql = trim($sql);
                if ('' != $sql) {
                    $this->sql($sql);
                }
            }
        }
    }

    /**
     * @return mixed|string
     */
    public function getDbIndex() {
        return $this->dbIndex;
    }

    /**
     * @return mixed|string
     */
    public function getDbName() {
        return $this->dbName;
    }

    /**
     * Transaction control via the native mysqli API (BEGIN/COMMIT/ROLLBACK are not preparable, so
     * they cannot go through sql()). Used to batch many INSERTs into one commit - e.g. the email
     * sync backfill, where a commit (fsync) per row would otherwise saturate the disk. The mysqli
     * handle is shared/cached per dbIndex, so callers on the same connection must not nest these.
     */
    public function beginTransaction(): void {
        $this->mysqli->begin_transaction();
    }

    public function commit(): void {
        $this->mysqli->commit();
    }

    public function rollback(): void {
        $this->mysqli->rollback();
    }

    /**
     * Depending on $dbIndex, read DB_?_SERVER ... crendentials.
     * If $dbIndex==1 but no DB_1_xxx specified, take the default DB_xxxx - old config.qfq.ini style
     *
     * @param $dbIndex 1,2,...
     *
     * @param array $config
     * @return array
     * @throws \UserFormException
     */
    private function getConnectionDetails($dbIndex, array $config) {

        if (isset($config["DB_" . $dbIndex . "_SERVER"])) {
            $config[SYSTEM_DB_SERVER] = $config["DB_" . $dbIndex . "_SERVER"];
            $config[SYSTEM_DB_USER] = $config["DB_" . $dbIndex . "_USER"];
            $config[SYSTEM_DB_PASSWORD] = $config["DB_" . $dbIndex . "_PASSWORD"];
            $config[SYSTEM_DB_NAME] = $config["DB_" . $dbIndex . "_NAME"];
        } elseif ($dbIndex != 1) {
            // Backward compatibility: old configs use SYSTEM_DB_SERVER (without index) and index=1 might mean 'legacy config'.
            throw new \UserFormException("DB Handle not found in config: $dbIndex", ERROR_INVALID_VALUE);
        }

        return $config;
    }

    /**
     * Open mysqli database connection if not already done.
     *
     * @param $config
     * @return \mysqli
     * @throws \UserFormException
     */
    private function dbConnect($config) {
        $mysqli = null;

        $mysqli = new \mysqli($config[SYSTEM_DB_SERVER], $config[SYSTEM_DB_USER], $config[SYSTEM_DB_PASSWORD], $config[SYSTEM_DB_NAME]);

        if ($mysqli->connect_error) {
            throw new \UserFormException (
                json_encode([ERROR_MESSAGE_TO_USER => 'Error open Database',
                    ERROR_MESSAGE_TO_DEVELOPER => "Error open Database 'mysql:host=" . $config[SYSTEM_DB_SERVER] .
                        ";dbname=" . $config[SYSTEM_DB_NAME] .
                        ";dbuser=" . $config[SYSTEM_DB_USER] .
                        "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
                ERROR_DB_OPEN);

        }

        // Necessary that mysqli::real_escape_string() functions properly.
        if (!$mysqli->set_charset('utf8')) {
            throw new \UserFormException (
                json_encode([ERROR_MESSAGE_TO_USER => "Error set_charset('utf8')",
                    ERROR_MESSAGE_TO_DEVELOPER => "Error set_charset('utf8') Database: " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
                ERROR_DB_SET_CHARSET);
        }

        return $mysqli;
    }

    /**
     * Fires query $sql and fetches result as assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_KEYS).
     * Throws exception.
     *
     * $mode
     *  ROW_REGULAR: Return 2-dimensional assoc array. Every query row is one array row.
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
     *  ROW_EXPECT_0: Return empty string if there is no record row, Else an exception.
     *  ROW_EXPECT_1: Return 1-dimensional assoc array if there are exact one row. Else an exception.
     *  ROW_EXPECT_0_1: Return empty array if there is no row. Return 1-dimensional assoc array if there is one row.
     *  Else an exception. ROW_EXPECT_GE_1: Like 'ROW_REGULAR'. Throws an exception if there is an empty resultset.
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. $keys are the column names.
     *
     * @param string $sql
     * @param string $mode
     * @param array $parameterArray
     * @param string $specificMessage
     * @param array $keys
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
     * @param array $skipErrno Array of ERRNO numbers, which should be skipped and not throw an error.
     *
     * @return array|int
     *      SELECT | SHOW | DESCRIBE | EXPLAIN: see $mode
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '',
                        array &$keys = array(), array &$stat = array(), array $skipErrno = array()) {
        $queryType = '';
        $result = array();
        $this->closeMysqliStmt();

        // CR (& EV) often forgets to specify the $mode and use prepared statement with parameter instead.
        if (is_array($mode)) {
            throw new \CodeException("Probably a parameter forgotten: $mode ?");
        }

        // for error reporting in exception
        if ($specificMessage) {
            $specificMessage .= " ";
        }

        $sql = trim($sql);
        $count = $this->prepareExecute($sql, $parameterArray, $queryType, $stat, $specificMessage, $skipErrno);


        if ($count === false) {
            throw new \DbException($specificMessage . "No idea why this error happens - please take some time and check the problem.", ERROR_DB_GENERIC_CHECK);
        }

        if ($queryType === QUERY_TYPE_SELECT) {
            switch ($mode) {
                case ROW_IMPLODE_ALL:
                    $result = $this->fetchAll($mode);
                    break;
                case ROW_KEYS:
                case ROW_REGULAR:
                    $result = $this->fetchAll($mode, $keys);
                    break;
                case ROW_EXPECT_0:
                    if ($count === 0) {
                        $result = array();
                    } else {
                        throw new \DbException($specificMessage . "Expected none row, got $count rows", ERROR_DB_TOO_MANY_ROWS);
                    }
                    break;
                case ROW_EXPECT_1:
                    if ($count === 1) {
                        $result = $this->fetchAll($mode)[0];
                    } else {
                        throw new \DbException($specificMessage . "Expected one row, got $count rows", ERROR_DB_COUNT_DO_NOT_MATCH);
                    }
                    break;
                case ROW_EXPECT_0_1:
                    if ($count === 1) {
                        $result = $this->fetchAll($mode)[0];
                    } elseif ($count === 0) {
                        $result = array();
                    } else
                        throw new \DbException($specificMessage . "Expected zero or one rows, got $count rows", ERROR_DB_TOO_MANY_ROWS);
                    break;
                case ROW_EXPECT_GE_1:
                    if ($count > 0) {
                        $result = $this->fetchAll($mode);
                    } else {
                        throw new \DbException($specificMessage . "Expected at least one row, got none", ERROR_DB_TOO_FEW_ROWS);
                    }
                    break;

                default:
                    throw new \DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }

            $this->freeResult();

        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
        } else {
            $result = $count;
        }

        $this->closeMysqliStmt();

        $this->store->setVar(SYSTEM_SQL_RAW, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_FINAL, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, '', STORE_SYSTEM);

        return $result;
    }

    /**
     * Close an optional open MySQLi Statement.
     *
     * @throws \DbException
     */
    private function closeMysqliStmt() {

        if ($this->mysqli_result !== null && $this->mysqli_result !== false) {
            $this->mysqli_result->free_result();
        }

        if ($this->mysqli_stmt !== null && $this->mysqli_stmt !== false) {
            $this->mysqli_stmt->free_result();
            if (!$this->mysqli_stmt->close()) {
                throw new \DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
            }
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
    }

    /**
     * Checks the problematic $sql if there is a common mistake.
     * If something is found, give a hint.
     *
     * @param string $sql
     * @param string $errorMsg
     * @return string
     */
    private function getSqlHint($sql, $errorMsg) {
        $msg = '';

        // Check if there is a comma before FROM: 'SELECT ... , FROM ...'
        $pos = stripos($sql, ' FROM ');
        if ($pos !== false && $pos > 0 && $sql[$pos - 1] == ',') {
            $msg .= "HINT: Remove extra ',' before FROM\n";
        }

        // Look for QFQ variables which haven't been replaced
        $matches = array();
        preg_match_all("/{{[^}}]*}}/", $sql, $matches);
        // '.line.count' might be replaced later and should not shown.
        foreach ($matches[0] as $key => $value) {
            if (false !== stripos($value, '.line.count')) {
                unset($matches[0][$key]);
            }
        }
        if (count($matches[0]) > 0) {
            $msg .= "HINT: The following variables couldn't be replaced: " . implode(', ', $matches[0]) . "\n";
        }

        // Look for missing '()' after FROM in case LEFT JOIN is used.
        $pos = stripos($sql, ' LEFT JOIN ');
        if (stripos($errorMsg, 'Unknown column') !== false && $pos !== false && ($sql[$pos - 1] ?? '') != ')') {
            $msg .= "HINT: Maybe the tables after 'FROM' should be enclosed by '()' \n";
        }

        // Check for double comma
        if (stripos($errorMsg, 'the right syntax to use near') && preg_match('/,[ ]*,/', $sql)) {
            $msg .= "HINT: There seems to be a double comma in your query.\n";
        }

        return $msg;
    }

    /**
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
     *
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains
     * appropriate num_rows, insert_id or rows_affected.
     *
     * In case of an error, throw an exception.
     * mysqli error code listed in $skipErrno[] do not throw an error.
     *
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
     * @param string $queryType returns QUERY_TYPE_SELECT | QUERY_TYPE_UPDATE | QUERY_TYPE_INSERT, depending on
     *                               the query.
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
     * @param string $specificMessage
     * @param array $skipErrno Array of ERRNO numbers, which should be skipped and not throw an error.
     *
     * @return int|mixed
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function prepareExecute($sql, array $parameterArray, &$queryType, array &$stat, $specificMessage = '', array $skipErrno = array()) {

        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
        $errno = 0;
        $stat = array();
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;

        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
        }

        // Logfile
        $this->dbLog($sqlLogMode, $sql, $parameterArray);

        // Get method from query and remove it from the sql variable to get a valid mysqli_stmt. Store given method in system store
        // Krzysztof Putyra: commented out, because it breaks SQL statements.
        // If this feature is needed, a smart parser must be used
//        $needle = 'AS _encrypt=';
//        if (mb_strpos($sql, $needle)) {
//            $preparedSql = explode('AS _encrypt=', $sql);
//            $sql = $preparedSql[0] . ' AS _encrypt';
//            $encryptionMethod = $preparedSql[1];
//            $this->store::setVar(ENCRYPTION_CIPHER_METHOD_COLUMN_NAME, $encryptionMethod, STORE_SYSTEM);
//        } else if ($this->store::getVar(ENCRYPTION_CIPHER_METHOD_COLUMN_NAME, STORE_SYSTEM, SANITIZE_ALLOW_ALL) !== null) {
//            $this->store::unsetVar(ENCRYPTION_CIPHER_METHOD_COLUMN_NAME, STORE_SYSTEM);
//        }

        try {
            $errorMsg[ERROR_MESSAGE_DB_INDEX] = $this->dbIndex;

            $this->mysqli_stmt = $this->mysqli->prepare($sql);
        } catch (mysqli_sql_exception $e) {
            $errno = $this->mysqli->errno;
            if (false === array_search($errno, $skipErrno)) {  // removed nonsensical condition $skipErrno === array() &&
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli->error;

                throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
            }
        }

        if (count($parameterArray) > 0) {
            if (false === $this->prepareBindParam($parameterArray)) {
                $errno = $this->mysqli_stmt->errno;
                if ($skipErrno === array() && false === array_search($errno, $skipErrno)) {
                    $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                    $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
                    $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli_stmt->error;

                    throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
                }
            }
        }

        try {
            $this->mysqli_stmt->execute();
        } catch (mysqli_sql_exception $e) {
            $errno = $this->mysqli->errno;
            if ($skipErrno === array() || false === array_search($errno, $skipErrno)) {
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;

                throw new \DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
            }
        }

        if ($errno === 0) {
            $command = strtoupper(explode(' ', $sql, 2)[0]);
        } else {
            $command = 'FAILED';
        }

        $command = OnString::removeLeadingBrace($command);
        switch ($command) {
            case 'SELECT':
            case 'SHOW':
            case 'DESCRIBE':
            case 'EXPLAIN':
            case 'WITH':
            case 'VALUES':
                if (false === ($result = $this->mysqli_stmt->get_result())) {
                    throw new \DbException(
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
                        ERROR_DB_EXECUTE);

                }
                $queryType = QUERY_TYPE_SELECT;
                $this->mysqli_result = $result;
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
                break;
            case 'CALL':
                $result = $this->mysqli_stmt->get_result();
                $queryType = QUERY_TYPE_SELECT;
                if ($result === false) {
                    $stat[DB_NUM_ROWS] = 0;
                    $msg = 'Read rows: ' . $stat[DB_NUM_ROWS] . '(No SELECT statement)';
                } else {
                    $this->mysqli_result = $result;
                    $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                    $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
                }
                $count = $stat[DB_NUM_ROWS];
                break;
            case 'REPLACE':
            case 'INSERT':
                $queryType = QUERY_TYPE_INSERT;
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = 'ID: ' . $this->mysqli->insert_id . ' - affected rows: ' . $count;
                break;
            case 'UPDATE':
            case 'DELETE':
            case 'TRUNCATE':
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = 'Affected rows: ' . $count;
                break;

            case 'SET':
            case 'ALTER':
            case 'DROP':
            case 'CREATE':
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;
            case 'FAILED':
                $queryType = QUERY_TYPE_FAILED;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = -1;
                $msg = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
                break;

            default:
                // Unknown command: treat it as a control command
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;
        }

        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }

        $this->dbLog($sqlLogMode, $msg);

        return $count;
    }

    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
     *
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {

        $command = explode(' ', $sql, 2);

        switch (strtoupper($command[0])) {
            case 'UPDATE':
            case 'DELETE':
                if (stripos($sql, ' WHERE ') === false) {
                    Thrower::userFormException("Safety stop: SQL query 'UPDATE/DELETE' without 'WHERE' limit.", 'If the statement is really correct, add "... WHERE 1" to suppress this check.');
                }
                return true;
                break;
            case 'INSERT':
            case 'REPLACE':
            case 'TRUNCATE':
            case 'DROP':
            case 'CREATE':
            case 'ALTER':
                return true;
        }

        return false;
    }

    /**
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
     *
     * @param string $currentQueryMode
     * @param string $sql
     * @param array $parameterArray
     *
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {

        if ($sql == '') {
            return;
        }

        $status = '';

        // If no sqlLogMode is defined/available, choose SQL_LOG_MODE_ERROR
        $sqlLogMode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);
        if ($sqlLogMode === false) {
            $sqlLogMode = SQL_LOG_MODE_ERROR;
        }

        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
                throw new \UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
            }
        }

        // Log?
        if ($this->sqlLogModePrio[$sqlLogMode] < ($this->sqlLogModePrio[$currentQueryMode])) {
            return;
        }

        // Client IP Address
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
            ['FESU', TYPO3_FE_USER, STORE_USER],
            ['Page', TYPO3_PAGE_ID, STORE_TYPO3],
            ['tt', TYPO3_TT_CONTENT_UID, STORE_TYPO3],
            ['level', SYSTEM_REPORT_FULL_LEVEL, STORE_SYSTEM],
            ['form', SIP_FORM, STORE_SIP],
            ['fslId', EXTRA_FORM_SUBMIT_LOG_ID, STORE_EXTRA],
            ['feId', SYSTEM_FORM_ELEMENT_ID, STORE_SYSTEM]
        ];

        $t3msg = '';
        foreach ($logArr as $logItem) {
            $value = $this->store->getVar($logItem[1], $logItem[2]);
            if ($logItem[0] == 'feId' && empty($value)) {
                $value = 'all';
            }
            if (!empty($value)) {
                $t3msg .= $logItem[0] . ":" . $value . ",";
            }
        }
        $t3msg = substr($t3msg, 0, strlen($t3msg) - 1);

        $msg = '[' . date('Y.m.d H:i:s O') . '][' . $remoteAddress . '][' . $t3msg . ']';

        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
        }

        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
        }
        $msg .= '[' . $status . $sql . ']';

        Logger::logMessage($msg, $this->sqlLogAbsolute);
    }

    /**
     * @param $sql
     * @param $parameterArray
     *
     * @return string
     */
    private function preparedStatementInsertParameter($sql, $parameterArray) {
        $msg = '';

        $sqlArray = explode('?', $sql);
        $ii = 0;
        foreach ($parameterArray as $value) {
            if (isset($sqlArray[$ii])) {
                if (is_array($value)) {
                    $value = OnArray::toString($value);
                }

                $msg .= $sqlArray[$ii++] . "'" . $value . "'";
            } else {
                $msg = '?';
            }
        }
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
    }

    /**
     * @param $arr
     */
    private function prepareBindParam($arr) {

        // PDO don't like if the array elemnts have names (assoc array) - clean them.
        $arr = array_values($arr);

        $bindParam = new BindParam();
        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
        }
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
    }

    /**
     * Fetch all rows of the result.
     *
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
     *  ROW_KEYS: Return num array with column names in $keys
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
     * @param array $keys
     *
     * @return array|bool|mixed|string false in case of an error.
     *              Empty string is returned if the query didn't yield any rows.
     *              All rows as Multi Assoc array if $mode!=IMPLODE_ALL.
     *              All rows and all columns imploded to one string if $mode=IMPLODE_ALL
     *
     */
    private function fetchAll($mode = '', &$keys = array()) {

        $result = null;

        if ($this->mysqli_result == null || $this->mysqli_result == false) {
            return false;
        }

        if ($this->mysqli_result->num_rows === 0) {
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $result = "";
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
                    $result .= implode($row);
                }
                break;

            case ROW_KEYS:
                $keys = array();

                for ($ii = 0; $ii < $this->mysqli_result->field_count; $ii++) {
                    $keys[$ii] = $this->mysqli_result->fetch_field_direct($ii)->name;
                }

                $result = $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                $result = $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
        }

        return $result;
    }

    /**
     * Return the number of rows returned by the last call to execute().
     *
     * If execute() has never been called, returns FALSE.
     *
     * @return mixed Number of rows returned by last call to execute(). If Database::execute()
     *     has never been called prior a call to this method, false is returned.
     */
    public function getRowCount() {
        if ($this->mysqli_result == null) {
            return false;
        }

        return $this->mysqli_result->num_rows;
    }

    /**
     * Get the values for a given ENUM or SET column
     *
     * @param string $table name of the table
     * @param string $columnName name of the column
     *
     * @return array
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function getEnumSetValueList($table, $columnName) {

        $columnDefinition = $this->getFieldDefinitionFromTable($table, $columnName);
        $setEnumDefinition = $columnDefinition["Type"];

        // $setEnumDefinition holds now a string like
        // String:  enum('','red','blue','green')
        $len = mb_strlen($setEnumDefinition);

        # "enum('" = 6, "set('" = 5
        $tokenLength = strpos($setEnumDefinition, "'") + 1;

        // count("enum('") == 6, count("')") == 2
        $enumSetString = mb_substr($setEnumDefinition, $tokenLength, $len - (2 + $tokenLength));

        // String: ','red','blue','green

        if (($setEnumValueList = explode("','", $enumSetString)) === false) {
            return array();
        }

        return $setEnumValueList;
    }

    /**
     * Get database column definition.
     *
     * If the column is not found in the table, an exception is thrown.
     *
     * @param string $table name of the table
     *
     * @param string $columnName name of the column
     *
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function getFieldDefinitionFromTable($table, $columnName) {

        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition as $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }

        throw new \DbException(
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
    }

    /**
     * Get all column definitions for a table. Return Assoc Array:
     *
     * Field      Type                      Null    Key    Default    Extra
     * --------------------------------------------------------------------------
     * id         bigint(20)                 NO     PRI    NULL    auto_increment
     * name       varchar(128)               YES           NULL
     * firstname  varchar(128)               YES           NULL
     * gender     enum('','male','female')   NO            male
     * groups     set('','a','b','c')        NO            a
     *
     * @param string $table table to retrieve column definition from
     *
     * @return array column definition of table as returned by SHOW FIELDS FROM as associative array.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`", ROW_EXPECT_GE_1, array(), "No columns found for table '$table'");
    }

    /**
     * Wrapper for sql(), to simplyfy access.
     *
     * @param              $sql
     * @param array $keys
     * @param array $stat
     *
     * @return array|bool
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {

        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
    }

    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
        return $this->mysqli->insert_id;
    }

    /**
     * Searches for the table '$name'.
     *
     * @param $name
     *
     * @return bool  true if found, else false
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function existTable($name) {
        $found = false;

        $tables = $this->sql("SHOW tables");

        foreach ($tables as $t) {
            foreach ($t as $key => $value) {
                if ($value === $name) {
                    $found = true;
                    break 2;
                }
            }
        }

        return $found;
    }

    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function createTable($table, $columnDefinition, $mode) {

        $cols = array();

        if (!$this->existTable($table)) {
            $sql = "CREATE TABLE $table (";
            foreach ($columnDefinition as $key => $value) {
                $cols[] = "`" . $key . "` " . $value . " NOT NULL,";
            }
            $sql .= implode(',', $cols);
            $sql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8_general_ci";

            $this->sql($sql);
        }

        if ($mode == IMPORT_MODE_REPLACE) {
            $this->sql("TRUNCATE $table");
        }
    }

    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
     * @param string $sql SQL_FORM_ELEMENT_SPECIFIC_CONTAINER | SQL_FORM_ELEMENT_ALL_CONTAINER
     * @param array $param Parameter which matches the prepared statement in $sql
     * @param array $formSpec Main FormSpec to copy generic parameter to FormElements
     *
     * @return array|int
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

        $feSpecNative = $this->sql($sql, ROW_REGULAR, $param);

        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);

        // Explode and Do $FormElement.parameter
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);

        // Check for retype FormElements which have to be duplicated.
        $feSpecNative = HelperFormElement::duplicateRetypeElements($feSpecNative);

        // Copy Attributes to FormElements
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);

        return $feSpecNative;
    }

    /**
     * Checks if there is the SQL keyword 'limit' at the end of the SQL statement.
     * returns true for '... LIMIT', '.... LIMIT 1, ... LIMIT 1,2, ... LIMIT 1 , 2
     *
     * @param $sql
     *
     * @return bool
     */
    public function hasLimit($sql) {

        $sql = trim(strtolower($sql));
        $arr = explode(' ', $sql);

        $ii = 3;
        array_pop($arr); // the last token can't be 'limit'

        while ($ii > 0) {
            $item = array_pop($arr);
            if ($item === null) {
                return false;
            }
            if ($item != '') {
                if ($item == 'limit') {
                    return true;
                } else {
                    $ii--;
                }
            }
        }

        return false;
    }

    /**
     * $arr = [ 0 => [ $srcColumn1 => $value0_1, $srcColumn2 => $value0_2 ], 1 => [ $srcColumn1 => $value1_1, $srcColumn2 => $value1_2 ], ...]
     *
     * $arr will be converted to a two column array with keys $destColumn1 and $destColumn2.
     * If $destColumn1 or $destColumn2 is empty, take $srcColumn1, $srcColumn2 as names.
     * $arr might contain one or more columns. Only the first two columns are used.
     * If there is only one column, that column will be doubled.
     *
     * @param array $arr
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
     *
     * @return array
     */
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

        if ($arr == array() || $arr === null) {
            return array();
        }

        // Set defaults
        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

        if ($destColumn2 == '') {
            $destColumn2 = $srcColumn2;
        }

        // Set final column names
        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
        } else {
            $column1 = $keys[0];
            $column2 = $keys[1];
        }

        $new = array();
        $row = array_shift($arr);
        while (null !== $row) {
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
//            $new[] = [$destColumn1 => htmlentities($row[$column1], ENT_QUOTES), $destColumn2 => htmlentities($row[$column2], ENT_QUOTES)];
            $row = array_shift($arr);
        }

        return $new;
    }

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
     *
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }

    /**
     * Function is called only for initial DB Update / QFQ installation logic.
     * Special case: If a $sqlStatements starts with 'DELIMITER $$', than it is STORED PROCEDURE SQL file, which
     *               needs to fixed, cause PHP won't play it natively. Problem: SQL command DELIMETER won't be understand
     *               by mysqli->multi_query(). Therefore remove it and split each procedure in a single statement.
     *
     * @param $sqlStatements
     *
     * @return bool|false if at least one statement failed
     * @throws \CodeException
     */
    public function playMultiQuery($sqlStatements) {

        if (str_starts_with($sqlStatements, 'DELIMITER $$') !== false) {
            // Remove DELIMITER directives
            $sqlStatements = preg_replace('/^DELIMITER\s+.*$/m', '', $sqlStatements);
            $statements = explode('$$', $sqlStatements);
        } else {
            $statements[] = $sqlStatements;
        }

        $flagFinal = true;
        $tmpMsg=array();
        foreach ($statements as $statement) {
            $statement = trim($statement);

            if ($statement === '') {
                continue;
            }

            $this->freeResult();

            try {
                if (false === ($executed = $this->mysqli->multi_query($statement))) {
                    $flagFinal = false;
                }
            } catch (\DbException $e) {
                $flagFinal = false;
            } catch (\CodeException $e) {
                $flagFinal = false;
            } catch (\Throwable $e) {
                $flagFinal = false;
            }

            if (false === $executed) {
                $tmpMsg[]=$this->mysqli->error;
            }
        }

        if (false === $flagFinal) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'SQL Error.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error playing multi query: " . implode($tmpMsg) . "\n\nSQL Query:\n\n" . $sqlStatements;
            throw new \CodeException(json_encode($errorMsg), ERROR_PLAY_SQL_MULTIQUERY);
        }

        // discard all results: this is important - if missed, following calls on $mysqli will fail.
        $this->freeResult();
        return $flagFinal;
    }

    /**
     * Free all results. If there are multiple, free them all. Required at least for multi_query() (mysqli) and call() (mysql)
     */
    private function freeResult() {
        do {
            if ($res = $this->mysqli->store_result()) {
                $res->free();
            }
        } while ($this->mysqli->more_results() && $this->mysqli->next_result());
    }

    /**
     * @param $filename
     *
     * @throws \CodeException
     */
    public function playSqlFile($filename) {

        $query = file_get_contents($filename);

        try {
            $this->playMultiQuery($query);
        } catch (\CodeException $e) {
            throw new \CodeException("Error playing $filename", ERROR_PLAY_SQL_FILE);
        }

    }

    /**
     * Selects in table 'Split' all records with tableName='$tableName' and xId='$xId'. Deletes all referenced files and records.
     *
     * If Table Split does not exist don't do anything.
     *
     * @param $xId
     * @param $tableName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function deleteSplitFileAndRecord($xId, $tableName) {
        // Check if table Split exists
        $splitExists = $this->store->getVar(QFQ_VERSION_KEY_TABLE_SPLIT, STORE_SYSTEM) == QFQ_VERSION_KEY_TABLE_SPLIT_ON;
        if (!$splitExists) {
            return;
        }


        $sql = 'SELECT `pathFileName` FROM `' . TABLE_NAME_SPLIT . '` WHERE `tableName`=? AND `xId`=?';
        $data = $this->sql($sql, ROW_REGULAR, [$tableName, $xId]);

        foreach ($data as $row) {
            if (!empty($row['pathFileName']) && is_writable($row['pathFileName'])) {
                HelperFile::unlink($row['pathFileName']);
            }
        }

        $this->sql('DELETE FROM `' . TABLE_NAME_SPLIT . '` WHERE `tableName`=? AND `xId`=?', ROW_REGULAR, [$tableName, $xId]);
    }

    /**
     * Returns either empty array or an array containing the selected columns.
     * $formName is matched case sensitively.
     * Throws exception if more than one form was found with the exact same name.
     *
     * @param string $formName
     * @param array|null $columnsToSelect
     * @return array|bool|int|string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function selectFormByName(string $formName, array $columnsToSelect = null) {
        // make sure to select column 'name'
        if (is_array($columnsToSelect) && !in_array(F_NAME, $columnsToSelect)) {
            $columnsToSelect[] = F_NAME;
        }

        // select
        list($sql, $parameterArray) = SqlQuery::selectFormByName($formName, $columnsToSelect);
        $forms = $this->sql($sql, ROW_REGULAR, $parameterArray);

        $forms = array_filter($forms, function ($f) use ($formName) {
            return $formName === $f[F_NAME];
        });

        // Check for case sensitive matches
        $count = count($forms);
        if ($count > 1) {
            Thrower::userFormException("Multiple forms with the exact same name: '$formName' (case sensitive). Expected one or zero rows, got $count rows");
        } else if ($count === 1) {
            return reset($forms); // returns first element
        } else {
            return [];
        }

    }

    /**
     * Load tt-content record with subheader=$uid or uid=$uid (depending if $uid is numeric).
     * It's important that the current DB class has access to the Typo3 DB.
     *
     * @param $uid
     * @return array // The full T3 tt-content record.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function getBodyText($uid, $ignoreHidden = true) {

        $column = (is_numeric($uid)) ? 'uid' : 'subheader';
        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
        if ($ignoreHidden) {
            $sql = "SELECT * FROM `$dbT3`.`tt_content` WHERE `$column` = ? AND `deleted` = 0 AND `hidden` = 0 ";
        } else {
            $sql = "SELECT * FROM `$dbT3`.`tt_content` WHERE `$column` = ? AND `deleted` = 0";
        }


        $arr = $this->sql($sql, ROW_EXPECT_1, [$uid], 'Function "' . $column . '=' . $uid . '" not found or more than one found.');
        return ($arr);
    }

    /**
     * Load page record with page id.
     * It's important that the current DB class has access to the Typo3 DB.
     *
     * @param $pageId
     * @return array // The full T3 tt-content record.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function getPageParam($pageId, $ignoreHidden = true) {

        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
        if ($ignoreHidden) {
            $sql = "SELECT * FROM `$dbT3`.`pages` WHERE `uid` = ? AND `deleted` = 0 AND `hidden` = 0 ";
        } else {
            $sql = "SELECT * FROM `$dbT3`.`pages` WHERE `uid` = ? AND `deleted` = 0";
        }

        $arr = $this->sql($sql, ROW_EXPECT_1, [$pageId], 'Function "uid=' . $pageId . '" not found or more than one found.');
        return ($arr);
    }

    /**
     * Get table name by form id.
     * Needed for encryption feature
     *
     * @param $formId
     * @return string
     */
    public function getTableNameById($formId) {
        return $this->sql("SELECT tableName FROM Form WHERE id=" . $formId, ROW_EXPECT_GE_1, array(), "No form found with this id: '$formId'");

    }

    /**
     * Create history record for inline editing reports.
     *
     * @param $dataHistory
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function setHistoryRecord($dataHistory): void {
        $version = $this->store->getVar(TYPO3_VERSION, STORE_TYPO3 . STORE_SIP);
        // History is only implemented for Version >=9
        if (version_compare($version, '9') >= 0) {
            $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
            $sql = "INSERT INTO `$dbT3`.`sys_history` (tstamp, actiontype, usertype, userid, originaluserid, recuid, tablename, history_data, workspace)
                VALUES (?,?,?,?,?,?,?,?,?)";
            $this->sql($sql, ROW_REGULAR, $dataHistory, "Creating history record failed.");
        }
    }

    /** Get all form history records for a given recordId and tableName.
     * Handle different situations with feUser list and limit.
     *
     * @throws \UserFormException
     * @throws \CodeException
     * @throws \DbException
     */
    public function getAllFormHistoryRecords($tableName, $recordId, $showHistoryFeUserSet = '', $limit = false, ?Database $dbQfq = null): array|string {
        // Get all form ids with the given tableName. Form table is always in the QFQ DB.
        $dbForFormLookup = $dbQfq ?? $this;
        $sql = 'SELECT id FROM ' . TABLE_NAME_FORM . ' WHERE tableName = ?';
        $formIds = $dbForFormLookup->sql($sql, ROW_REGULAR, [$tableName]);
        $formIds = array_column((array)$formIds, 'id');
        $feUserSetQuery = '';

        // Build comma seperated list of formIds from the db result
        $formIdList = implode(',', $formIds);

        if (!empty($showHistoryFeUserSet)) {
            $feUserSetQuery = ' AND FIND_IN_SET(feUser, ?)';
        }

        // Get all history records. Not only current open form. In case of given limit, only created, feUser and formName are selected.
        if ($limit) {
            $sql = 'SELECT created, feUser, formName FROM ' . TABLE_NAME_FORM_SUBMIT_LOG . ' WHERE recordId = ? AND FIND_IN_SET(formId, ?) ' . $feUserSetQuery . ' ORDER BY created DESC LIMIT ' . $limit;
        } else {
            $sql = 'SELECT * FROM ' . TABLE_NAME_FORM_SUBMIT_LOG . ' WHERE recordId = ? AND FIND_IN_SET(formId, ?) ' . $feUserSetQuery . ' ORDER BY created DESC';
        }

        if (!empty($showHistoryFeUserSet)) {
            return $this->sql($sql, ROW_REGULAR, [$recordId, $formIdList, $showHistoryFeUserSet]);
        } else {
            return $this->sql($sql, ROW_REGULAR, [$recordId, $formIdList]);
        }
    }

    /** Check for history records for a given recordId and formId
     *  Returns true if at least one records exist, false if not
     *
     * @throws \UserFormException
     * @throws \CodeException
     * @throws \DbException
     */
    public function checkFormHistoryRecords($formId, $recordId, $showHistoryFeUserSet = ''): array|string {

        $feUserSetQuery = !empty($showHistoryFeUserSet) ? ' AND FIND_IN_SET(feUser, ?)' : '';
        $queryArr = !empty($showHistoryFeUserSet) ? [$recordId, $formId, $showHistoryFeUserSet] : [$recordId, $formId];

        $sql = 'SELECT * FROM ' . TABLE_NAME_FORM_SUBMIT_LOG . ' WHERE recordId = ? AND formId = ? ' . $feUserSetQuery . ' LIMIT 1';

        return count($this->sql($sql, ROW_REGULAR, $queryArr)) > 0;
    }
}