<?php

namespace Admidio\Infrastructure\Entity;

use Admidio\Infrastructure\Database;
use Admidio\Infrastructure\Exception;
use Admidio\Infrastructure\Utils\SecurityUtils;
use Admidio\Infrastructure\Utils\StringUtils;
use Admidio\Users\Entity\User;
use Admidio\Changelog\Entity\LogChanges;
use DateTime;
use Ramsey\Uuid\Uuid;
use Throwable;

/**
 * @brief Controls read and write access to database tables
 *
 * This class should help you to read and write records of database tables.
 * You create an object for a special table, and then you are able to read
 * a special record, manipulate him and write him back. Also, new records can
 * be created with this class. The advantage of this class is that you are
 * independent of SQL. You can use @c getValue, @c setValue, @c readData
 * and @c save to handle the record.
 *
 * **Code example**
 * ```
 * // create an object for table adm_roles of role 4711
 * $roleId = 4177;
 * $role = new Entity($gDb, TBL_ROLES, 'rol', $roleId);
 *
 * // read max. Members and add 1 to the count
 * $maxMembers = $role->getValue('rol_max_members');
 * $maxMembers = $maxMembers + 1;
 * $role->setValue('rol_max_members', $maxMembers);
 * $role->save();
 * ```
 * @copyright The Admidio Team
 * @see https://www.admidio.org/
 * @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License v2.0 only
 */
class Entity
{
    /**
     * @var array<string,string> Array with subarray that contains additional tables and their connected fields that should be selected when data is read
     */
    protected array $additionalTables = array();
    /**
     * @var string Name of the database table from this object. This must be the table name with the installation-specific prefix e.g. **demo_users**
     */
    protected string $tableName;
    /**
     * @var string The prefix of each column that this table has. E.g., the table adm_users has the column prefix **usr**
     */
    protected string $columnPrefix;
    /**
     * @var string Name of the unique autoincrement index column from the database table
     */
    protected string $keyColumnName = '';
    /**
     * @var Database An object of the class Database for communication with the database
     */
    protected Database $db;

    /**
     * @var bool Flag whether a new data set or existing data set is being edited
     */
    protected bool $newRecord;
    /**
     * @var bool Flag if the data of this record must be inserted or updated
     */
    protected bool $insertRecord;
    /**
     * @var bool The Flag will be set to true if data in array dbColumns was changed
     */
    protected bool $columnsValueChanged;
    /**
     * @var array<string,mixed> Array over all fields of the corresponding table for the selected record
     */
    protected array $dbColumns = array();
    /**
     * @var array<string,array<string,mixed>> Array which stores further information (changed yes/no, field type, previous value)
     */
    protected array $columnsInfos = array();
    /**
     * @var bool If this flag is set, then some right checks will be disabled, so that the object could be saved also
     * if the current user doesn't have the right to do this.
     */
    protected bool $saveChangesWithoutRights;

    /**
     * Flag to enable/disable logging changes to the database.
     * @var bool If this flag is set (default), all changes will be logged to the database if the corresponding preferences item is set.
     * Setting this to false will disable logging in all cases, even with the preference set.
     */
    protected static bool $loggingEnabled = true;

    /**
     * Constructor that will create an object of a recordset from the specified table.
     * If the id is set, then this recordset will be loaded.
     * @param Database $database Object of the class Database. This should be the default global object **$gDb**.
     * @param string $tableName The name of the database table. Because of specific prefixes, this should be the defined value e.g. **TBL_USERS**
     * @param string $columnPrefix The prefix of each column from that table. E.g., for table **adm_roles** this is **rol**
     * @param int|string $id The id of the recordset that should be loaded. If id isn't set, then an empty object of the table is created.
     * @throws Exception
     */
    public function __construct(Database $database, string $tableName, string $columnPrefix, int|string $id = '')
    {
        $this->db =& $database;
        $this->tableName = $tableName;
        $this->columnPrefix = $columnPrefix;

        // if an ID is committed, then read data out of a database
        if ($id > 0) {
            $this->readDataById($id);
        } else {
            $this->clear();
        }
    }

    /**
     * A wakeup add the current database object to this class
     */
    public function __wakeup()
    {
        global $gDb;

        if ($gDb instanceof Database) {
            $this->db = $gDb;
        }
    }

    /**
     * Initializes all class parameters and deletes all read data.
     * Also, the database structure of the associated table will be
     * read and stored in the arrays **dbColumns** and **columnsInfos**
     * @throws Exception
     */
    public function clear(): void
    {
        $this->newRecord = true;
        $this->insertRecord = true;
        $this->columnsValueChanged = false;
        $this->saveChangesWithoutRights = false;

        if (count($this->columnsInfos) > 0) {
            // the column infos have already been read and will now only be reinitialized
            foreach ($this->dbColumns as $fieldName => &$fieldValue) {
                $fieldValue = ''; // $this->dbColumns[$fieldName] = '';
                $this->columnsInfos[$fieldName]['changed'] = false;
                $this->columnsInfos[$fieldName]['previousValue'] = null;
            }
            unset($fieldValue);
        } else {
            // read all column information of the tables
            $this->setColumnsInfos();
        }
    }

    /**
     * Adds a table with the connected fields to a member array. This table will be added to the
     * select statement if data is read and the connected record is available in this class.
     * The connected table must have a foreign key in the class table.
     * @param string $table Database table name that should be connected.
     * @param string $columnNameAdditionalTable Name of the column in the connected table that has the foreign key to the class table
     * @param string $columnNameClassTable Name of the column in the class table that has the foreign key to the connected table
     *
     * **Code example**
     * ```
     * // Constructor of adm_events object where the category (calendar) is connected
     * public function __construct($database, $datId = 0)
     * {
     *     $this->connectAdditionalTable(TBL_CATEGORIES, 'cat_id', 'dat_cat_id');
     *     parent::__construct($db, TBL_EVENTS, 'dat', $datId);
     * }
     * ```
     */
    public function connectAdditionalTable(string $table, string $columnNameAdditionalTable, string $columnNameClassTable): void
    {
        $this->additionalTables[] = array(
            'table' => $table,
            'columnNameAdditionalTable' => $columnNameAdditionalTable,
            'columnNameClassTable' => $columnNameClassTable
        );
    }

    /**
     * Get the name of the underlying database table. This can be used to construct SQL queries without hardcoding the table.
     * @return string The name of the underlying database table
     */
    public function getTableName(): string
    {
        return $this->tableName;
    }

    /**
     * Get the column prefix of the underlying table. This can be used to construct column names without hardcoding the prefix.
     * @return string The column prefix used for the underlying database table
     */
    public function getColumnPrefix(): string
    {
        return $this->columnPrefix;
    }

    /**
     * Get the key column of the underlying database table. This can be used to construct SQL queries names without hardcoding the column name.
     * @return string The key column name used for the underlying database table
     */
    public function getKeyColumnName(): string
    {
        return $this->keyColumnName;
    }

    /**
     * Reads the number of all records from this table
     * @return int Number of records of this table
     * @throws Exception
     */
    public function countAllRecords(): int
    {
        $sql = 'SELECT COUNT(*) AS count FROM ' . $this->tableName;
        $countStatement = $this->db->queryPrepared($sql);

        return (int)$countStatement->fetchColumn();
    }

    /**
     * Return a human-readable representation of this record.
     * If a column [prefix]_name exists, it is returned, otherwise the id.
     * This method can be overridden in child classes for custom behavior.
     *
     * @return string The readable representation of the record (can also be a translatable identifier)
     */
    public function readableName(): string
    {
        if (array_key_exists($this->columnPrefix . '_name', $this->dbColumns)) {
            return $this->dbColumns[$this->columnPrefix . '_name'] ?? '';
        } elseif (array_key_exists($this->columnPrefix . '_title', $this->dbColumns)) {
            return $this->dbColumns[$this->columnPrefix . '_title'] ?? '';
        } elseif (array_key_exists($this->columnPrefix . '_headline', $this->dbColumns)) {
            return $this->dbColumns[$this->columnPrefix . '_headline'] ?? '';
        } elseif (array_key_exists($this->columnPrefix . '_text', $this->dbColumns)) {
            return $this->dbColumns[$this->columnPrefix . '_text'] ?? '';
        } else {
            return $this->dbColumns[$this->keyColumnName] ?? '';
        }
    }

    /**
     * Return a human-readable representation of the given database field/column.
     * By default, the column name is returned unmodified. Subclasses can override this method.
     * @param string $field The database column
     * @return string The readable representation of the DB column (can also be a translatable identifier)
     */
    public function getFieldTitle(string $field): string
    {
        return $field;
    }

    /**
     * Enable/disable logging globally, irrespective of the preference setting. Used, e.g., during setup / updates
     * @param mixed $enabled Whether logging should be enabled or not.
     * @return void
     */
    public static function setLoggingEnabled(mixed $enabled): void
    {
        self::$loggingEnabled = $enabled;
    }

    /**
     * Retrieve the list of database fields that are ignored for the changelog.
     * Some tables contain columns _usr_id_create, timestamp_create, etc. We do not want
     * to log changes to these columns. Subclasses can also add further fields
     * (e.g., the user's table stores and auto-increments the login count, which
     * we do not want to log)
     *
     * @return array Returns the list of database columns to be ignored for logging.
     */
    public function getIgnoredLogColumns(): array
    {
        return [
            $this->columnPrefix . '_uuid',
            $this->columnPrefix . '_usr_id_create',
            $this->columnPrefix . '_timestamp_create',
            $this->columnPrefix . '_usr_id_change',
            $this->columnPrefix . '_timestamp_change',
            $this->columnPrefix . '_usr_id',
            $this->columnPrefix . '_timestamp'
        ];
    }

    /**
     * Adjust the changelog entry for this db record. By default, record_id, record_name are taken
     * from this record, linked and related are left empty, and the field is the column name of each change.
     *
     * @param LogChanges $logEntry The log entry to adjust
     *
     * @return void
     */
    protected function adjustLogEntry(LogChanges $logEntry)
    {
    }


    /**
     * Logs creation of the DB record
     * @return true Returns **true** if no error occurred
     * @throws Exception
     */
    public function logCreation(): bool
    {
        if (!self::$loggingEnabled) return false;
        $table = $this->tableName;
        $table = str_replace(TABLE_PREFIX . '_', '', $table);
        $record_name = $this->readableName();
        if (array_key_exists($this->columnPrefix . '_uuid', $this->dbColumns)) {
            $uuid = (string)$this->getValue($this->columnPrefix . '_uuid');
        } else {
            $uuid = null;
        }

        $logEntry = new LogChanges($this->db);
        $logEntry->setLogCreation($table, $this->dbColumns[$this->keyColumnName] ?? 0, $uuid, $record_name);
        $this->adjustLogEntry($logEntry);
        return $logEntry->save();
    }

    /**
     * Logs deletion of the DB record
     *
     * @return true Returns **true** if no error occurred
     * @throws Exception
     */
    public function logDeletion(): bool
    {
        if (!self::$loggingEnabled) return false;
        $table = $this->tableName;
        $table = str_replace(TABLE_PREFIX . '_', '', $table);
        $record_name = $this->readableName();
        if (array_key_exists($this->columnPrefix . '_uuid', $this->dbColumns)) {
            $uuid = (string)$this->getValue($this->columnPrefix . '_uuid');
        } else {
            $uuid = null;
        }


        $logEntry = new LogChanges($this->db);
        $logEntry->setLogDeletion($table, $this->dbColumns[$this->keyColumnName] ?? 0, $uuid, $record_name);
        $this->adjustLogEntry($logEntry);
        return $logEntry->save();
    }


    /**
     * Logs all modifications of the DB record
     * @param array $logChanges Array of all changes, generated by the save method
     * @return true Returns **true** if no error occurred
     * @throws Exception
     */
    public function logModifications(array $logChanges): bool
    {
        if (!self::$loggingEnabled) return false;
        if (count($logChanges) === 0) return false;
        $retVal = true;
        $table = $this->tableName;
        $table = str_replace(TABLE_PREFIX . '_', '', $table);
        $id = $this->dbColumns[$this->keyColumnName];
        $record_name = $this->readableName();
        if (array_key_exists($this->columnPrefix . '_uuid', $this->dbColumns)) {
            $uuid = (string)$this->getValue($this->columnPrefix . '_uuid');
        } else {
            $uuid = null;
        }


        $logEntry = new LogChanges($this->db, $table);

        // Log each individual record modification
        foreach ($logChanges as $field => $changes) {
            $fieldName = $this->getFieldTitle($field);
            $logEntry->setLogModification($table, $id, $uuid, $record_name, $field, $fieldName, $changes['oldValue'], $changes['newValue']);
            $this->adjustLogEntry($logEntry);
            $retVal = $retVal && $logEntry->save();
            $logEntry->clear();
            $logEntry->saveChangesWithoutRights();
        }
        return $retVal;
    }


    /**
     * Deletes the selected record of the table and initializes the class
     * @return true Returns **true** if no error occurred
     * @throws Exception
     */
    public function delete(): bool
    {
        if (array_key_exists($this->keyColumnName, $this->dbColumns) && isset($this->dbColumns[$this->keyColumnName]) && $this->dbColumns[$this->keyColumnName] !== '') {
            // Log record deletion, then delete
            $this->logDeletion();
            $sql = 'DELETE FROM ' . $this->tableName . '
                     WHERE ' . $this->keyColumnName . ' = ? -- $this->dbColumns[$this->keyColumnName]';
            $this->db->queryPrepared($sql, array($this->dbColumns[$this->keyColumnName]));
        }

        $this->clear();
        return true;
    }

    /**
     * Get the first name and last name of the person who has created this record. In dependence of the preference
     * system_show_create_edit the login name will be shown. If the current user has a valid login and the
     * parameter **$linkToProfile** is set, then an HTML link to the profile is set around the name.
     * @param bool $linkToProfile If set to **true** a link to the profile is set around the name.
     * @return string Returns the first name and last name of the person optional with a link to the profile.
     * @throws Exception
     */
    public function getNameOfCreatingUser(bool $linkToProfile = true): string
    {
        global $gDb, $gProfileFields, $gL10n, $gSettingsManager, $gValidLogin;

        $nameOfCreatingUser = '';

        if ($this->getValue($this->columnPrefix . '_timestamp_create') !== '') {
            if ((int)$this->getValue($this->columnPrefix . '_usr_id_create') > 0) {
                $userCreated = new User($gDb, $gProfileFields, $this->getValue($this->columnPrefix . '_usr_id_create'));

                if ((int)$gSettingsManager->get('system_show_create_edit') === 1) {
                    $nameOfCreatingUser = $userCreated->getValue('FIRST_NAME') . ' ' . $userCreated->getValue('LAST_NAME');
                } else {
                    $nameOfCreatingUser = $userCreated->getValue('usr_login_name');
                }

                // if valid login and a user id is given than create a link to the profile of this user
                if ($linkToProfile && $gValidLogin && $nameOfCreatingUser !== $gL10n->get('SYS_SYSTEM')) {
                    $nameOfCreatingUser = '<a href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/profile/profile.php', array('user_uuid' => $userCreated->getValue('usr_uuid'))) .
                        '">' . $nameOfCreatingUser . '</a>';
                }
            } else {
                $nameOfCreatingUser = $gL10n->get('SYS_DELETED_USER');
            }
        }
        return $nameOfCreatingUser;
    }

    /**
     * Get the first name and last name of the person who was the last editor of this record. In dependence of the preference
     * system_show_create_edit the login name will be shown. If the current user has a valid login and the
     * parameter **$linkToProfile** is set, then an HTML link to the profile is set around the name.
     * @param bool $linkToProfile If set to **true** a link to the profile is set around the name.
     * @return string Returns the first name and last name of the person optional with a link to the profile.
     * @throws Exception
     */
    public function getNameOfLastEditingUser(bool $linkToProfile = true): string
    {
        global $gDb, $gProfileFields, $gL10n, $gSettingsManager, $gValidLogin;

        $nameOfLastEditingUser = '';

        if ($this->getValue($this->columnPrefix . '_timestamp_change') !== '') {
            if ((int)$this->getValue($this->columnPrefix . '_usr_id_change') > 0) {
                $userLastEdited = new User($gDb, $gProfileFields, $this->getValue($this->columnPrefix . '_usr_id_change'));

                if ((int)$gSettingsManager->get('system_show_create_edit') === 1) {
                    $nameOfLastEditingUser = $userLastEdited->getValue('FIRST_NAME') . ' ' . $userLastEdited->getValue('LAST_NAME');
                } else {
                    $nameOfLastEditingUser = $userLastEdited->getValue('usr_login_name');
                }

                // if valid login and a user id is given than create a link to the profile of this user
                if ($linkToProfile && $gValidLogin && $nameOfLastEditingUser !== $gL10n->get('SYS_SYSTEM')) {
                    $nameOfLastEditingUser = '<a href="' . SecurityUtils::encodeUrl(ADMIDIO_URL . FOLDER_MODULES . '/profile/profile.php', array('user_uuid' => $userLastEdited->getValue('usr_uuid'))) .
                        '">' . $nameOfLastEditingUser . '</a>';
                }
            } else {
                $nameOfLastEditingUser = $gL10n->get('SYS_DELETED_USER');
            }
        }
        return $nameOfLastEditingUser;
    }

    /**
     * Get the value of a column of the database table.
     * If the value was manipulated before with **setValue** then the manipulated value is returned.
     * @param string $columnName The name of the database column whose value should be read
     * @param string $format For date or timestamp columns, the format should be the date/time format e.g. **d.m.Y = '02.04.2011'**.
     *                           For text columns, the format can be **database** that would return the original database value without any transformations
     * @return mixed Returns the value of the database column.
     *               If the value was manipulated before with **setValue** then the manipulated value is returned.
     * @throws Exception
     * @see Entity#setValue
     */
    public function getValue(string $columnName, string $format = ''): mixed
    {
        global $gSettingsManager;

        $columnValue = '';

        if (array_key_exists($columnName, $this->dbColumns)) {
            // if the key field is empty, return 0
            if ($this->keyColumnName === $columnName && empty($this->dbColumns[$columnName])) {
                $columnValue = 0;
            } else {
                $columnValue = $this->dbColumns[$columnName];
            }
        }

        if (array_key_exists($columnName, $this->columnsInfos) && array_key_exists('type', $this->columnsInfos[$columnName])) {
            switch ($this->columnsInfos[$columnName]['type']) {
                // String
                case 'char': // fallthrough
                case 'varchar': // fallthrough
                case 'text':
                case 'mediumtext':
                    if ($format !== 'database') {
                        // if text field and format not 'database' then convert all quotes to HTML syntax
                        $columnValue = SecurityUtils::encodeHTML((string)$columnValue);
                    } else {
                        // Postgres returns null for empty text fields, so we convert it to empty string here
                        $columnValue = $columnValue ?? '';
                    }
                    break;

                case 'blob':
                    // For blobs, we return the raw data as is
                    break;

                case 'bytea':
                    // For Postgres, we must encode the stored resource hex value back to binary
                    if (is_resource($columnValue)) {
                        ob_start();
                        fpassthru($columnValue);
                        $columnValue = hex2bin(ob_get_contents());
                        ob_end_clean();
                        $this->dbColumns[$columnName] = $columnValue;
                    }
                    break;

                case 'timestamp': // fallthrough
                case 'date': // fallthrough
                case 'time':
                    if (isset($columnValue) && $columnValue !== '') {
                        if ($format === '' && isset($gSettingsManager)) {
                            if (str_contains($this->columnsInfos[$columnName]['type'], 'timestamp')) {
                                $format = $gSettingsManager->getString('system_date') . ' ' . $gSettingsManager->getString('system_time');
                            } elseif (str_contains($this->columnsInfos[$columnName]['type'], 'date')) {
                                $format = $gSettingsManager->getString('system_date');
                            } else {
                                $format = $gSettingsManager->getString('system_time');
                            }
                        }

                        // try to format the date, else output the available data
                        try {
                            $datetime = new DateTime($columnValue);
                            $columnValue = $datetime->format($format);
                        } catch (Throwable) {
                            $columnValue = $this->dbColumns[$columnName];
                        }
                    } else {
                        $columnValue = '';
                    }
                    break;
            }
        }

        return $columnValue;
    }

    /**
     * If a column of the row in this object has changed throw setValue, then this method
     * will return **true** otherwise @false
     * @return bool Returns **true** if at least one value of one column has changed
     *              after the recordset was loaded otherwise **false**
     */
    public function hasColumnsValueChanged(): bool
    {
        return $this->columnsValueChanged;
    }

    /**
     * If the recordset is new and wasn't read from a database or was not stored in a database,
     * then this method will return true otherwise false
     * @return bool Returns **true** if record is not stored in a database
     */
    public function isNewRecord(): bool
    {
        return $this->newRecord;
    }

    /**
     * Reads a record out of the table in the database selected by the conditions of the param **$sqlWhereCondition** out of the table.
     * If the SQL finds more than one record, the method returns **false**.
     * Per default, all columns of the default table will be read and stored in the object.
     * @param string $sqlWhereCondition Conditions for the table to select one record
     * @param array<int,mixed> $queryParams The query params for the prepared statement
     * @return bool Returns **true** if one record is found
     * @throws Exception
     * @see Entity#readDataByColumns
     * @see Entity#readDataById
     * @see Entity#readDataByUuid
     */
    protected function readData(string $sqlWhereCondition, array $queryParams = array()): bool
    {
        $sqlAdditionalTables = '';

        // create SQL to connect additional tables to the select statement
        if (count($this->additionalTables) > 0) {
            foreach ($this->additionalTables as $arrAdditionalTable) {
                $sqlAdditionalTables .= ', ' . $arrAdditionalTable['table'];
                $sqlWhereCondition .= ' AND ' . $arrAdditionalTable['columnNameAdditionalTable'] . ' = ' . $arrAdditionalTable['columnNameClassTable'] . ' ';
            }
        }

        // if the condition starts with AND then remove this
        if (StringUtils::strStartsWith(ltrim($sqlWhereCondition), 'AND', false)) {
            $sqlWhereCondition = substr($sqlWhereCondition, 4);
        }

        if ($sqlWhereCondition !== '') {
            $sql = 'SELECT *
                      FROM ' . $this->tableName . '
                           ' . $sqlAdditionalTables . '
                     WHERE ' . $sqlWhereCondition;
            $readDataStatement = $this->db->queryPrepared($sql, $queryParams); // TODO add more params

            if ($readDataStatement->rowCount() === 1) {
                $row = $readDataStatement->fetch();
                $this->newRecord = false;
                $this->insertRecord = false;

                // move data to class column value array
                foreach ($row as $key => $value) {
                    if ($this->columnsInfos[$key]['type'] === 'boolean'
                        || $this->columnsInfos[$key]['type'] === 'tinyint') {
                        $this->dbColumns[$key] = (bool)$value;
                    } elseif (($this->columnsInfos[$key]['type'] === 'integer'
                            || $this->columnsInfos[$key]['type'] === 'smallint')
                        && $value != '') {
                        // only convert to int if it's not a null value
                        $this->dbColumns[$key] = (int)$value;
                    } else {
                        $this->dbColumns[$key] = $value;
                    }
                }

                return true;
            }

            $this->clear();
        }

        return false;
    }

    /**
     * Reads a record out of the table in the database selected by the unique id column in the table.
     * Per default, all columns of the default table will be read and stored in the object.
     * @param int $id Unique id of id column of the table.
     * @return bool Returns **true** if one record is found
     * @throws Exception
     * @see Entity#readDataByColumns
     * @see Entity#readData
     * @see Entity#readDataByUuid
     */
    public function readDataById(int $id): bool
    {
        // initialize the object, so that all fields are empty
        $this->clear();

        // add id to SQL condition
        if ($id > 0) {
            // call method to read data out of a database
            return $this->readData(' AND ' . $this->keyColumnName . ' = ? ', array($id));
        }

        return false;
    }

    /**
     * Reads a record out of the table in the database selected by the unique uuid column in the table.
     * The name of the column must have the syntax table_prefix, underscore and uuid. E.g., usr_uuid.
     * Per default, all columns of the default table will be read and stored in the object.
     * Not every Admidio table has a UUID. Please check the database structure before you use this method.
     * @param string $uuid Unique uuid that should be searched.
     * @return bool Returns **true** if one record is found
     * @throws Exception
     * @see Entity#readDataByColumns
     * @see Entity#readData
     * @see Entity#readDataById
     */
    public function readDataByUuid(string $uuid): bool
    {
        // initialize the object, so that all fields are empty
        $this->clear();

        // add id to SQL condition
        if ($uuid !== '') {
            // call method to read data out of a database
            return $this->readData(' AND ' . $this->columnPrefix . '_uuid = ? ', array($uuid));
        }

        return false;
    }

    /**
     * Reads a record out of the table in database selected by different columns in the table.
     * The columns are committed with an array where every element index is the column name and the value is the column value.
     * If you want a column to be null, then set the value to **NULL**
     * The columns and values must be selected so that they identify only one record.
     * If the SQL finds more than one record, the method returns **false**.
     * Per default, all columns of the default table will be read and stored in the object.
     * @param array<string,mixed> $columnArray An array where every element index is the column name and the value is the column value
     * @return bool Returns **true** if one record is found
     *
     * **Code example**
     * ```
     * // reads data not be mem_id but with combination of role and user id
     * $member = new Entity($gDb, TBL_MEMBERS, 'rol');
     * $member->readDataByColumn(array('mem_rol_id' => $roleId, 'mem_usr_id' => $userId));
     * ```
     * @throws Exception
     * @see Entity#readDataByUuid
     * @see Entity#readData
     * @see Entity#readDataById
     */
    public function readDataByColumns(array $columnArray): bool
    {
        // initialize the object, so that all fields are empty
        $this->clear();

        if (count($columnArray) === 0) {
            return false;
        }

        $sqlWhereCondition = '';
        $sqlParams = array();

        // add every array element as an SQL condition to the condition string
        foreach ($columnArray as $columnName => $columnValue) {
            if ($columnValue === 'NULL') {
                $sqlWhereCondition .= ' AND ' . $columnName . ' IS NULL ';
            } else {
                $sqlWhereCondition .= ' AND ' . $columnName . ' = ? ';
                $sqlParams[] = $columnValue;
            }
        }

        // call method to read data out of a database
        $returnCode = $this->readData($sqlWhereCondition, array_values($sqlParams));

        // if no record was found, then save the array fields in the object
        if (!$returnCode) {
            foreach ($columnArray as $columnName => $columnValue) {
                if (str_starts_with($columnName, $this->columnPrefix . '_')) {
                    $this->setValue($columnName, $columnValue);
                }
            }
        }

        return $returnCode;
    }

    /**
     * Save all changed columns of the recordset in table of a database. Therefore, the class remembers if it's
     * a new record or if only an update is necessary. The update statement will only update the changed columns.
     * If the table has columns for creator or editor, then these columns with their timestamp will be updated.
     * For a new record if there is a UUID column, a new uuid will be created and stored.
     * @param bool $updateFingerPrint Default **true**. Will update the creator or editor of the recordset
     *                                if a table has columns like **usr_id_create** or **usr_id_change**
     * @return bool If an update or insert into the database was done, then return true, otherwise false.
     * @throws Exception
     */
    public function save(bool $updateFingerPrint = true): bool
    {
        if (!$this->columnsValueChanged && isset($this->dbColumns[$this->keyColumnName]) && $this->dbColumns[$this->keyColumnName] !== '') {
            return false;
        }

        // if a new role then set create the uuid
        if ($this->isNewRecord()
            && array_key_exists($this->columnPrefix . '_uuid', $this->dbColumns)
            && (string)$this->getValue($this->columnPrefix . '_uuid') === '') {
            $this->setValue($this->columnPrefix . '_uuid', (string)Uuid::uuid4());
        }

        // TODO check if "$gCurrentUser instanceof User"
        // see "start_installation.php"
        if ($updateFingerPrint && isset($GLOBALS['gCurrentUserId']) && $GLOBALS['gCurrentUserId'] > 0) {
            // if the table has fields to store the creator and the last change,
            // then fill them here automatically
            if ($this->newRecord && $this->insertRecord && array_key_exists($this->columnPrefix . '_usr_id_create', $this->dbColumns)) {
                $this->setValue($this->columnPrefix . '_timestamp_create', DATETIME_NOW);
                $this->setValue($this->columnPrefix . '_usr_id_create', $GLOBALS['gCurrentUserId']);
            } elseif (array_key_exists($this->columnPrefix . '_usr_id_change', $this->dbColumns)) {
                // Do not update data if the same user has done so within 15 minutes
                if ($GLOBALS['gCurrentUserId'] !== $this->getValue($this->columnPrefix . '_usr_id_create')
                    || time() > (strtotime($this->getValue($this->columnPrefix . '_timestamp_create')) + 900)) {
                    $this->setValue($this->columnPrefix . '_timestamp_change', DATETIME_NOW);
                    $this->setValue($this->columnPrefix . '_usr_id_change', $GLOBALS['gCurrentUserId']);
                }
            }
        }

        $sqlFieldArray = array();
        $sqlSetArray = array();
        $queryParams = array();
        $returnCode = false;

        $logChanges = array();

        // Loop over all DB fields and add them to the update
        foreach ($this->dbColumns as $key => $value) {
            // fields of other tables must not appear in insert/update
            if (str_starts_with($key, $this->columnPrefix . '_')) {
                if ($this->columnsInfos[$key]['type'] === 'boolean' && DB_ENGINE === Database::PDO_ENGINE_PGSQL) {
                    if ($value || $value === '1') {
                        $value = 'true';
                    } else {
                        $value = 'false';
                    }
                }

                // Auto-increment fields must not appear in insert/update
                if (!$this->columnsInfos[$key]['serial'] && $this->columnsInfos[$key]['changed']) {
                    if ($this->insertRecord) {
                        // Prepare data for an insert
                        $sqlFieldArray[] = $key;
                    } else {
                        // Prepare data for an update
                        $sqlSetArray[] = $key . ' = ?';

                    }
                    $queryParams[] = $value;
                    // Ignore the usr_id_create and timestamp_create (and *_change) columns in the change log...
                    if (!in_array($key, $this->getIgnoredLogColumns())) {
                        $logChanges[$key] = array('oldValue' => $this->columnsInfos[$key]['previousValue'], 'newValue' => $value);
                    }

                    $this->columnsInfos[$key]['changed'] = false;
                }
            }
        }

        if ($this->insertRecord) {
            // insert record and remember the new id
            $sql = 'INSERT INTO ' . $this->tableName . '
                           (' . implode(',', $sqlFieldArray) . ')
                    VALUES (' . Database::getQmForValues($sqlFieldArray) . ')';
            if ($this->db->queryPrepared($sql, $queryParams) !== false) {
                $returnCode = true;
                if ($this->keyColumnName !== '') {
                    $this->dbColumns[$this->keyColumnName] = $this->db->lastInsertId();
                }
                $this->logCreation();
                $this->logModifications($logChanges);
                $this->insertRecord = false;
            }
        } else {
            $sql = 'UPDATE ' . $this->tableName . '
                       SET ' . implode(', ', $sqlSetArray) . '
                     WHERE ' . $this->keyColumnName . ' = ? -- $this->dbColumns[$this->keyColumnName]';
            $queryParams[] = $this->dbColumns[$this->keyColumnName];
            if ($this->db->queryPrepared($sql, $queryParams) !== false) {
                $returnCode = true;
                // Log record modification
                $this->logModifications($logChanges);
            }
        }

        $this->columnsValueChanged = false;

        return $returnCode;
    }

    /**
     * If this method is set, then the current user can save changes to this object if he hasn't the necessary rights.
     * The flag must be used within the class implementation.
     * @return void
     */
    public function saveChangesWithoutRights(): void
    {
        $this->saveChangesWithoutRights = true;
    }

    /**
     * The method requires an array with all fields of one recordset from the table object.
     * These fields will be added to the object as if you read one record with **readDataById**
     * but without a separate SQL. This method is useful if you have several recordset of the
     * table and want to use a table object for each recordset. So you don't have to do a
     * separate SQL read for each record. This is a performant way to fill the object with
     * the necessary data.
     * @param array $fieldArray An array with all fields and their values of the table. If the object has
     *                          more connected tables than you should add the fields of these tables, too.
     *
     * **Code example**
     * ```
     * // read all announcements with their categories
     * $sql = 'SELECT * FROM ' . TBL_ANNOUNCEMENTS . ' INNER JOIN ' . TBL_CATEGORIES . ' ON ann_cat_id = cat_id';
     * $announcementsStatement = $gDb->queryPrepared($sql);
     * $announcement = new Announcements($gDb);
     *
     * While ($row = $announcementsStatement->fetch())
     * {
     *     // add each recordset to an object without a separate sql within the object
     *     $announcement->clear();
     *     $announcement->setArray($row);
     *     ...
     * }
     * ```
     * @throws Exception
     */
    public function setArray(array $fieldArray): void
    {
        foreach ($fieldArray as $field => $value) {
            if (!empty($this->dbColumns[$field])) {
                $this->columnsInfos[$field]['previousValue'] = $this->dbColumns[$field];
            }
            $this->dbColumns[$field] = $value;
            $this->columnsInfos[$field]['changed'] = false;
        }

        if (empty($this->dbColumns[$this->keyColumnName])) {
            $this->setNewRecord();
        } else {
            $this->newRecord = false;
            $this->insertRecord = false;
        }
    }

    /**
     * Read all columns with their information like **type** (integer, varchar, boolean),
     * **null** (or not), **key** and **serial**. Also, the changed flag will be set to false.
     * @throws Exception
     */
    protected function setColumnsInfos(): void
    {
        // create an array with base table and all connected tables
        $tables = array($this->tableName);

        foreach ($this->additionalTables as $values) {
            $tables[] = $values['table'];
        }

        foreach ($tables as $table) {
            $tableColumnsProperties = $this->db->getTableColumnsProperties($table);

            foreach ($tableColumnsProperties as $columnName => $property) {
                // some actions should only be done for columns of the main table from this class
                if (str_starts_with($columnName, $this->columnPrefix . '_')) {
                    $this->dbColumns[$columnName] = null;

                    if ($property['serial']) {
                        $this->keyColumnName = $columnName;
                    }
                }
                $this->columnsInfos[$columnName]['changed'] = false;
                $this->columnsInfos[$columnName]['previousValue'] = null;
                if (strpos($property['type'], '(') > 0) {
                    $this->columnsInfos[$columnName]['type'] = substr($property['type'], 0, strpos($property['type'], '('));
                } else {
                    $this->columnsInfos[$columnName]['type'] = $property['type'];
                }
                $this->columnsInfos[$columnName]['null'] = $property['null'];
                $this->columnsInfos[$columnName]['key'] = $property['key'];
                $this->columnsInfos[$columnName]['serial'] = $property['serial'];
                if (isset($property['default'])) {
                    $this->columnsInfos[$columnName]['default'] = $property['default'];
                } elseif ($property['null']) {
                    $this->columnsInfos[$columnName]['default'] = null;
                }
            }
        }
    }

    /**
     * Use this method if you have read a record from the database and want to use this data to create a new record.
     * Method set the flag that it's a new record and initialize the ID and set a new UUID if that column exists.
     * @return void
     * @throws Exception
     */
    public function setNewRecord(): void
    {
        $this->newRecord = true;
        $this->insertRecord = true;

        if (array_key_exists($this->columnPrefix . '_id', $this->dbColumns)) {
            $this->setValue($this->columnPrefix . '_id', 0);
        }
        if (array_key_exists($this->columnPrefix . '_uuid', $this->dbColumns)) {
            $this->setValue($this->columnPrefix . '_uuid', (string)Uuid::uuid4());
        }
    }

    /**
     * Set a new value for a column of the database table. The value is only saved in the object.
     * You must call the method **save** to store the new value to the database. If the unique key
     * column is set to 0, then this record will be a new record and all other columns are marked as changed.
     * @param string $columnName The name of the database column whose value should get a new value
     * @param mixed $newValue The new value that should be stored in the database field
     * @param bool $checkValue The value will be checked if it's valid. If set to **false** then the value will not be checked.
     * @return bool Returns **true** if the value is stored in the current object and **false** if a check failed
     * @throws Exception If **columnName** doesn't exist. Exception->text contains a string with the reason why the login failed.
     * @see Entity#getValue
     */
    public function setValue(string $columnName, mixed $newValue, bool $checkValue = true): bool
    {
        if (!array_key_exists($columnName, $this->dbColumns)) {
            throw new Exception('Column ' . $columnName . ' does not exists in table ' . $this->tableName . '!');
        }

        $type     = strtolower($this->columnsInfos[$columnName]['type']);
        $nullable = $this->columnsInfos[$columnName]['null'] ?? false;
        $isKey    = $this->columnsInfos[$columnName]['key'] ?? false;

        // normalize string values
        $newValue = is_string($newValue) ? trim($newValue) : $newValue;

        if ($checkValue) {
            if (!isset($newValue) || $newValue === '') {
                if ($nullable) {
                    $newValue = null;
                } else {
                    // fallback to safe defaults when NULL not allowed
                    if (preg_match('/int|tinyint|smallint|mediumint|bigint/', $type)) {
                        if (isset($this->columnsInfos[$columnName]['default'])) {
                            $newValue = $this->columnsInfos[$columnName]['default'];
                        } else {
                            $newValue = 0;
                        }
                        // Key fields should not contain 0
                        if ($isKey && (int)$newValue === 0) {
                            $newValue = '';
                        }
                    } elseif (preg_match('/decimal|numeric|float|double|real/', $type)) {
                        if (isset($this->columnsInfos[$columnName]['default'])) {
                            $newValue = $this->columnsInfos[$columnName]['default'];
                        } else {
                            $newValue = 0.0;
                        }
                    } elseif (preg_match('/date|time|year/', $type)) {
                        if (isset($this->columnsInfos[$columnName]['default'])) {
                            $newValue = $this->columnsInfos[$columnName]['default'];
                        } else {
                            $newValue = '1970-01-01';
                        }
                    } elseif (preg_match('/datetime|timestamp/', $type)) {
                        if (isset($this->columnsInfos[$columnName]['default'])) {
                            $newValue = $this->columnsInfos[$columnName]['default'];
                        } else {
                            $newValue = '1970-01-01 00:00:00';
                        }
                    } elseif (preg_match('/bool|tinyint\(1\)/', $type)) {
                        if (isset($this->columnsInfos[$columnName]['default'])) {
                            $newValue = $this->columnsInfos[$columnName]['default'];
                        } else {
                            $newValue = false;
                        }
                    } else {
                        if (isset($this->columnsInfos[$columnName]['default'])) {
                            $newValue = $this->columnsInfos[$columnName]['default'];
                        } else {
                            $newValue = '';
                        }
                    }
                }
            } else {
                // Convert and sanitize based on type
                switch ($type) {
                    // Numeric
                    case 'integer':
                    case 'smallint':
                    case 'bigint':
                    case 'tinyint':
                        if (!is_numeric($newValue)) {
                            $newValue = $nullable ? null : 0;
                        } else {
                            $newValue = (int)$newValue;
                        }

                        // Key fields should not contain 0
                        if ($isKey && (int)$newValue === 0) {
                            $newValue = $nullable ? null : '';
                        }
                        break;

                    // Decimal / Float
                    case 'decimal':
                    case 'numeric':
                    case 'float':
                    case 'double':
                    case 'real':
                        $newValue = (float)str_replace(',', '.', $newValue);
                        break;

                    // Boolean
                    case 'boolean':
                        $boolVal = filter_var($newValue, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);
                        $newValue = $boolVal ?? ($nullable ? null : false);
                        break;

                    // Date
                    case 'date':
                        $date = date_create($newValue);
                        $newValue = $date ? $date->format('Y-m-d') : ($nullable ? null : '1970-01-01');
                        break;

                    // Datetime
                    case 'datetime':
                    case 'timestamp':
                        $dateTime = date_create($newValue);
                        $newValue = $dateTime ? $dateTime->format('Y-m-d H:i:s') : ($nullable ? null : '1970-01-01 00:00:00');
                        break;

                    // Strings
                    case 'char':
                    case 'varchar':
                    case 'text':
                        // no HTML tags and no HTML entities should be stored in the database
                        $newValue = StringUtils::strStripTags(html_entity_decode($newValue, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'));
                        break;

                    // Byte/Blob
                    case 'blob':
                        // For blobs, we accept the raw data as is
                        break;

                    case 'bytea':
                        // Postgres can only store hex values in bytea, so we must decode binary in hex
                        $newValue = bin2hex($newValue);
                        break;

                    default:
                        // fallback sanitize
                        $newValue = is_scalar($newValue) ? htmlspecialchars((string)$newValue, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') : $newValue;
                }
            }
        }

        // if the key field was set to 0, then a new record is to be created
        if ($this->keyColumnName === $columnName && (int)$newValue === 0) {
            $this->newRecord = true;
            $this->insertRecord = true;

            // now mark all other columns with values of this object as changed
            foreach ($this->dbColumns as $column => $value) {
                if ((is_array($value) && count($value) > 0) || isset($value)) {
                    $this->columnsInfos[$column]['changed'] = true;
                }
            }
        }

        if ($this->valueChanged($columnName, $newValue)) {
            $this->columnsInfos[$columnName]['previousValue'] = $this->dbColumns[$columnName];
            $this->dbColumns[$columnName] = $newValue;
            $this->columnsValueChanged = true;
            $this->columnsInfos[$columnName]['changed'] = true;
        }

        return true;
    }

    /**
     * Check if the given column has changed, considering the DB column type.
     * Since loading from the database converts to the actual data types, but setting
     * uses strings, some datatypes need special-casing. Boolean false are read as null,
     * but set as 0 -> null and 0 must be considered as false. Similarly, loading a
     * (date)time will include seconds, but the setting will not include seconds in the string
     * value.
     *
     * @param string $columnName the database column name to check
     * @param string|null $newValue the new value to set
     * @return bool Whether the $newValue can be considered different from the current value
     */
    protected function valueChanged(string $columnName, ?string $newValue): bool
    {
        $oldValue = isset($this->dbColumns[$columnName]) && !empty($this->dbColumns[$columnName]) ? $this->dbColumns[$columnName] : null;

        // certain data types need special handling to detect changes
        //   * bool: unset/null and 0 mean false
        //   * date/time: Make sure seconds are handled consistently, no need to convert to string
        //   * all other types can be compared by converting to string and comparing strings
        switch ($this->columnsInfos[$columnName]['type']) {
            case 'boolean': // fallthrough
            case 'tinyint':
                if (empty($newValue)) $newValue = 0;
                return $oldValue != $newValue;
            case 'timestamp': // fallthrough
            case 'date': // fallthrough
            case 'time':
                // if both are empty, no need to go through DateTime
                if (empty($oldValue) && empty($newValue)) {
                    return false;
                } elseif (empty($oldValue) || empty($newValue)) {
                    return true;
                }
                try {
                    // Convert old and new to a DateTime and compare that directly
                    $oldDate = new DateTime($oldValue);
                    $newDate = new DateTime($newValue);
                    return $oldDate != $newDate;
                } catch (\Exception) {
                    // if DateTime-conversion did not work, compare the strings
                    return $oldValue != $newValue;
                }
            default:
                // only mark as "changed" if the value is different (DON'T use binary safe function!)
                if (!isset($oldValue) && !isset($newValue)) {
                    return false;
                } elseif (!isset($oldValue) && isset($newValue)) {
                    return true;
                } else {
                    return strcmp((string)$oldValue, (string)$newValue) !== 0;
                }
        }
    }

}
