<?php
/*
** Copyright (C) 2001-2025 Zabbix SIA
**
** This program is free software: you can redistribute it and/or modify it under the terms of
** the GNU Affero General Public License as published by the Free Software Foundation, version 3.
**
** This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
** without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
** See the GNU Affero General Public License for more details.
**
** You should have received a copy of the GNU Affero General Public License along with this program.
** If not, see <https://www.gnu.org/licenses/>.
**/


class DB {

	const SCHEMA_FILE = 'schema.inc.php';

	const DBEXECUTE_ERROR = 1;
	const RESERVEIDS_ERROR = 2;
	const SCHEMA_ERROR = 3;
	const INIT_ERROR = 4;

	const FIELD_TYPE_INT = 0x01;
	const FIELD_TYPE_CHAR = 0x02;
	const FIELD_TYPE_ID = 0x04;
	const FIELD_TYPE_FLOAT = 0x08;
	const FIELD_TYPE_UINT = 0x10;
	const FIELD_TYPE_BLOB = 0x20;
	const FIELD_TYPE_TEXT = 0x40;
	const FIELD_TYPE_CUID = 0x80;

	const SUPPORTED_FILTER_TYPES = self::FIELD_TYPE_INT | self::FIELD_TYPE_CHAR | self::FIELD_TYPE_ID |
		self::FIELD_TYPE_FLOAT | self::FIELD_TYPE_UINT | self::FIELD_TYPE_CUID;
	const SUPPORTED_SEARCH_TYPES = self::FIELD_TYPE_CHAR | self::FIELD_TYPE_TEXT | self::FIELD_TYPE_CUID;

	/**
	 * Maximum number of IDs per SQL request.
	 *
	 * @var int
	 */
	private const CHUNK_SIZE = 1000;

	private static $schema = null;

	/**
	 * @var DbBackend
	 */
	private static $dbBackend;

	/**
	 * Get necessary DB class.
	 *
	 * @return DbBackend
	 */
	public static function getDbBackend() {
		global $DB;

		if (!self::$dbBackend) {
			switch ($DB['TYPE']) {
				case ZBX_DB_MYSQL:
					self::$dbBackend = new MysqlDbBackend();
					break;

				case ZBX_DB_POSTGRESQL:
					self::$dbBackend = new PostgresqlDbBackend();
					break;
			}
		}

		return self::$dbBackend;
	}

	private static function exception($code, $error) {
		throw new DBException($error, $code);
	}

	/**
	 * Reserve IDs for primary key of passed table.
	 * If record for table does not exist or value is out of range, ids record is created using maximum ID from table
	 * or minimum allowed value.
	 *
	 * @param string $table table name
	 * @param int $count number of IDs to reserve
	 *
	 * @throws APIException
	 *
	 * @return string
	 */
	public static function reserveIds(string $table, int $count): string {
		$table_schema = self::getSchema($table);
		$pk_field_name = $table_schema['key'];

		$sql = 'SELECT nextid'.
			' FROM ids'.
			' WHERE table_name='.zbx_dbstr($table).
				' AND field_name='.zbx_dbstr($pk_field_name).
			' FOR UPDATE';

		$resource = DBselect($sql);

		if ($resource === false) {
			self::exception(self::DBEXECUTE_ERROR, _('Database error occurred.'));
		}

		$res = DBfetch($resource);

		if (!$res) {
			return self::refreshIds($table, $count);
		}

		$max_nextid = bcadd($res['nextid'], $count, 0);

		if (bccomp($max_nextid, ZBX_DB_MAX_ID) == 1) {
			return self::refreshIds($table, $count);
		}

		$sql = 'UPDATE ids'.
			' SET nextid='.$max_nextid.
			' WHERE table_name='.zbx_dbstr($table).
				' AND field_name='.zbx_dbstr($pk_field_name);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _('Database error occurred.'));
		}

		return bcadd($res['nextid'], 1, 0);
	}

	/**
	 * Refresh ID record for given table.
	 * Record is deleted and then created again with value of maximum ID from table or minimum allowed.
	 *
	 * @param string $table table name
	 * @param int    $count number of IDs to reserve
	 *
	 * @throws APIException
	 *
	 * @return string
	 */
	public static function refreshIds(string $table, int $count): string {
		$tableSchema = self::getSchema($table);
		$id_name = $tableSchema['key'];

		// when we reach the maximum ID, we try to refresh them to check if any IDs have been freed
		$sql = 'DELETE FROM ids WHERE table_name='.zbx_dbstr($table).' AND field_name='.zbx_dbstr($id_name);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _('Database error occurred.'));
		}

		$row = DBfetch(DBselect('SELECT MAX('.$id_name.') AS id FROM '.$table));

		$nextid = ($row && $row['id']) ? $row['id'] : 0;

		$maxNextId = bcadd($nextid, $count, 0);

		if (bccomp($maxNextId, ZBX_DB_MAX_ID) == 1) {
			self::exception(
				self::RESERVEIDS_ERROR, __METHOD__.' ID greater than maximum allowed for table "'.$table.'"'
			);
		}

		$sql = 'INSERT INTO ids (table_name,field_name,nextid)'.
				' VALUES ('.zbx_dbstr($table).','.zbx_dbstr($id_name).','.$maxNextId.')';

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _('Database error occurred.'));
		}

		$nextid = bcadd($nextid, 1, 0);

		return $nextid;
	}

	/**
	 * Returns an array describing the database schema.
	 *
	 * If the $table parameter is passed, the method will return the schema for the given table,
	 * otherwise - for the whole database.
	 *
	 * @param string|null $table
	 *
	 * @throws DBException if the given table does not exist.
	 *
	 * @return array
	 */
	public static function getSchema(?string $table = null): array {
		if (self::$schema === null) {
			self::$schema = include __DIR__.'/../../'.self::SCHEMA_FILE;
		}

		if ($table === null) {
			return self::$schema;
		}

		if (!array_key_exists($table, self::$schema)) {
			self::exception(self::SCHEMA_ERROR, _s('Table "%1$s" does not exist.', $table));
		}

		return self::$schema[$table];
	}

	/**
	 * Returns the names of the fields that are used as the primary key of the table.
	 *
	 * @param string $table_name
	 *
	 * @return string
	 */
	public static function getPk(string $table_name): string {
		$schema = self::getSchema($table_name);

		return $schema['key'];
	}

	/**
	 * Returns true if the table $tableName has the $fieldName field.
	 *
	 * @param string $tableName
	 * @param string $fieldName
	 *
	 * @return bool
	 */
	public static function hasField($tableName, $fieldName) {
		$schema = self::getSchema($tableName);

		return isset($schema['fields'][$fieldName]);
	}

	/**
	 * Returns length of the field by schema.
	 *
	 * @param array $field_schema
	 *
	 * @return int
	 */
	public static function getFieldLengthBySchema(array $field_schema): int {
		return $field_schema['length'];
	}

	/**
	 * Returns length of the field.
	 *
	 * @param string $table_name
	 * @param string $field_name
	 *
	 * @return int
	 */
	public static function getFieldLength(string $table_name, string $field_name): int {
		return self::getFieldLengthBySchema(self::getSchema($table_name)['fields'][$field_name]);
	}

	public static function getDefaults($table) {
		$table = self::getSchema($table);

		$defaults = [];
		foreach ($table['fields'] as $name => $field) {
			if (isset($field['default'])) {
				$defaults[$name] = $field['default'];
			}
		}
		return $defaults;
	}

	/**
	 * Returns the default value of the given field.
	 *
	 * @param string $table		name of the table
	 * @param string $field		name of the field
	 *
	 * @return string|null
	 */
	public static function getDefault($table, $field) {
		$table = self::getSchema($table);
		$field = $table['fields'][$field];

		return isset($field['default']) ? $field['default'] : null;
	}

	/**
	 * Get the updated values of a record by correctly comparing the new and old ones, taking field types into account.
	 *
	 * @param string $table_name
	 * @param array  $new_values
	 * @param array  $old_values
	 *
	 * @return array
	 */
	public static function getUpdatedValues(string $table_name, array $new_values, array $old_values): array {
		$updated_values = [];

		// Discard field names not existing in the target table.
		$fields = array_intersect_key(self::getSchema($table_name)['fields'], $new_values);

		foreach ($fields as $name => $spec) {
			if (!array_key_exists($name, $old_values)) {
				$updated_values[$name] = $new_values[$name];
				continue;
			}

			if ($spec['type'] & self::FIELD_TYPE_ID) {
				if (bccomp($new_values[$name], $old_values[$name]) != 0) {
					$updated_values[$name] = $new_values[$name];
				}
			}
			elseif ($spec['type'] & (self::FIELD_TYPE_INT | self::FIELD_TYPE_UINT | self::FIELD_TYPE_FLOAT)) {
				if ($new_values[$name] != $old_values[$name]) {
					$updated_values[$name] = $new_values[$name];
				}
			}
			elseif ($new_values[$name] !== $old_values[$name]) {
				$updated_values[$name] = $new_values[$name];
			}
		}

		return $updated_values;
	}

	private static function checkValueTypes($tableSchema, &$values) {
		global $DB;

		foreach ($values as $field => $value) {
			if (!isset($tableSchema['fields'][$field])) {
				unset($values[$field]);
				continue;
			}

			if (isset($tableSchema['fields'][$field]['ref_table'])) {
				if ($tableSchema['fields'][$field]['null']) {
					$values[$field] = ($values[$field] == '0') ? NULL : $values[$field];
				}
			}

			if (is_null($values[$field])) {
				if ($tableSchema['fields'][$field]['null']) {
					$values[$field] = 'NULL';
				}
				elseif (isset($tableSchema['fields'][$field]['default'])) {
					$values[$field] = zbx_dbstr($tableSchema['fields'][$field]['default']);
				}
				else {
					self::exception(self::DBEXECUTE_ERROR,
						_s('Field "%1$s" cannot be set to NULL.', $field)
					);
				}
			}
			else {
				if ($tableSchema['fields'][$field]['type'] & (self::FIELD_TYPE_CUID | self::FIELD_TYPE_CHAR)) {
					$length = mb_strlen($values[$field]);

					if ($length > $tableSchema['fields'][$field]['length']) {
						self::exception(self::SCHEMA_ERROR, _s('Value "%1$s" is too long for field "%2$s" - %3$d characters. Allowed length is %4$d characters.',
							$values[$field], $field, $length, $tableSchema['fields'][$field]['length']));
					}
					$values[$field] = zbx_dbstr($values[$field]);
				}
				elseif ($tableSchema['fields'][$field]['type'] & (self::FIELD_TYPE_ID | self::FIELD_TYPE_UINT)) {
					if (!zbx_ctype_digit($values[$field])) {
						self::exception(self::DBEXECUTE_ERROR, _s('Incorrect value "%1$s" for unsigned int field "%2$s".', $values[$field], $field));
					}
					$values[$field] = zbx_dbstr($values[$field]);
				}
				elseif ($tableSchema['fields'][$field]['type'] & self::FIELD_TYPE_INT) {
					if (!zbx_is_int($values[$field])) {
						self::exception(self::DBEXECUTE_ERROR, _s('Incorrect value "%1$s" for int field "%2$s".', $values[$field], $field));
					}
					$values[$field] = zbx_dbstr($values[$field]);
				}
				elseif ($tableSchema['fields'][$field]['type'] & self::FIELD_TYPE_FLOAT) {
					if (!is_numeric($values[$field])) {
						self::exception(self::DBEXECUTE_ERROR, _s('Incorrect value "%1$s" for float field "%2$s".', $values[$field], $field));
					}
					$values[$field] = zbx_dbstr($values[$field]);
				}
				elseif ($tableSchema['fields'][$field]['type'] & self::FIELD_TYPE_TEXT) {
					$values[$field] = zbx_dbstr($values[$field]);
				}
				elseif ($tableSchema['fields'][$field]['type'] & self::FIELD_TYPE_BLOB) {
					switch ($DB['TYPE']) {
						case ZBX_DB_MYSQL:
							$values[$field] = zbx_dbstr($values[$field]);
							break;

						case ZBX_DB_POSTGRESQL:
							$values[$field] = "'".pg_escape_bytea($DB['DB'], $values[$field])."'";
							break;
					}
				}
			}
		}
	}

	/**
	 * Returns the records that match the given criteria.
	 *
	 * @param string $tableName
	 * @param array $criteria   An associative array of field-value pairs, where value can be either a single value
	 *                          or an array (IN)
	 *
	 * @return array
	 */
	public static function find($tableName, array $criteria = []) {
		// build the WHERE part
		$sqlWhere = [];
		foreach ($criteria as $field => $value) {
			// check if the table has this field
			if (!self::hasField($tableName, $field)) {
				self::exception(self::DBEXECUTE_ERROR, _s('Table "%1$s" doesn\'t have a field named "%2$s".', $tableName, $field));
			}

			$sqlWhere[] = dbConditionString($field, zbx_toArray($value));
		}

		// build query
		$sql = 'SELECT * FROM '.$tableName;
		if ($sqlWhere) {
			$sql .= ' WHERE '.implode(' AND ', $sqlWhere);
		}

		return DBfetchArray(DBSelect($sql));
	}

	/**
	 * Insert data into DB.
	 *
	 * @param string $table
	 * @param array  $values pair of fieldname => fieldvalue
	 * @param bool   $getids
	 *
	 * @return array    an array of ids with the keys preserved
	 */
	public static function insert($table, $values, $getids = true) {
		$table_schema = self::getSchema($table);
		$fields = array_reduce($values, 'array_merge', []);
		$fields = array_intersect_key($fields, $table_schema['fields']);

		foreach ($fields as $field => &$value) {
			$value = array_key_exists('default', $table_schema['fields'][$field])
				? $table_schema['fields'][$field]['default']
				: null;
		}
		unset($value);

		foreach ($values as $key => &$row) {
			$row = array_merge($fields, $row);
		}
		unset($row);

		return self::insertBatch($table, $values, $getids);
	}

	/**
	 * Returns the list of mandatory fields with default values for INSERT statements.
	 *
	 * @param array $table_schema
	 *
	 * @return array
	 */
	private static function getMandatoryFields(array $table_schema): array {
		global $DB;

		$mandatory_fields = [];

		if ($DB['TYPE'] === ZBX_DB_MYSQL) {
			foreach ($table_schema['fields'] as $name => $field) {
				if ($field['type'] & self::FIELD_TYPE_TEXT) {
					$mandatory_fields += [$name => $field['default']];
				}
			}
		}

		return $mandatory_fields;
	}

	/**
	 * Add IDs to inserted rows.
	 *
	 * @param string $table
	 * @param array  $values
	 *
	 * @return array An array of IDs with the keys preserved.
	 */
	private static function addIds(string $table, array &$values): array {
		$table_schema = self::getSchema($table);
		$resultids = [];

		if ($table_schema['fields'][$table_schema['key']]['type'] & self::FIELD_TYPE_ID) {
			$id = self::reserveIds($table, count($values));
		}

		foreach ($values as $key => &$row) {
			if ($table_schema['fields'][$table_schema['key']]['type'] & self::FIELD_TYPE_ID) {
				$resultids[$key] = $id;
				$row = [$table_schema['key'] => $id] + $row;
				$id = bcadd($id, 1, 0);
			}
			elseif ($table_schema['fields'][$table_schema['key']]['type'] & self::FIELD_TYPE_CUID) {
				$id = CCuid::generate();
				$resultids[$key] = $id;
				$row = [$table_schema['key'] => $id] + $row;
			}
		}
		unset($row);

		return $resultids;
	}

	/**
	 * Insert batch data into DB.
	 *
	 * @param string $table
	 * @param array  $values pair of fieldname => fieldvalue
	 * @param bool   $getids
	 *
	 * @return array An array of IDs with the keys preserved.
	 */
	public static function insertBatch($table, $values, $getids = true) {
		if (empty($values)) {
			return true;
		}

		$resultids = [];
		$table_schema = self::getSchema($table);
		$mandatory_fields = self::getMandatoryFields($table_schema);

		if ($getids) {
			$resultids = self::addIds($table, $values);
		}

		foreach ($values as &$row) {
			$row += $mandatory_fields;

			self::checkValueTypes($table_schema, $row);
			self::uppercaseValues($table, $row);
		}
		unset($row);

		$sql = self::getDbBackend()->createInsertQuery($table, array_keys(reset($values)), $values);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s".', $sql));
		}

		return $resultids;
	}

	/**
	 * Update data in DB.
	 *
	 * @param string $table_name
	 * @param array  $upd_requests
	 * @param array  $upd_requests[]['values']  Pair of fieldname => fieldvalue for SET clause.
	 * @param array  $upd_requests[]['where']   Pair of fieldname => fieldvalue for WHERE clause.
	 */
	public static function update(string $table_name, array $upd_requests): void {
		if (!$upd_requests) {
			return;
		}

		$table_schema = self::getSchema($table_name);

		$upd_requests = zbx_toArray($upd_requests);

		foreach ($upd_requests as $upd_request) {
			self::validateUpdValues($table_name, $table_schema, $upd_request);
			self::validateUpdConditions($table_name, $table_schema, $upd_request);

			if (self::hasCompositePk($table_schema)) {
				self::updateByConditions($table_name, $upd_request);
			}
			elseif (count($upd_request['where']) == 1 && array_key_exists($table_schema['key'], $upd_request['where'])) {
				self::updateByPkIds($table_name, $upd_request);
			}
			else {
				self::updateExistingRowsByPkIds($table_name, $upd_request, $table_schema['key']);
			}
		}
	}

	private static function validateUpdValues(string $table_name, array $table_schema, array &$upd_request): void {
		if (!array_key_exists('values', $upd_request) || !$upd_request['values']) {
			self::exception(self::DBEXECUTE_ERROR,
				_s('Cannot perform update statement on table "%1$s" without values.', $table_name)
			);
		}

		self::checkValueTypes($table_schema, $upd_request['values']);
		self::uppercaseValues($table_name, $upd_request['values']);
	}

	private static function validateUpdConditions(string $table_name, array $table_schema, array &$upd_request): void {
		if (!array_key_exists('where', $upd_request) || !$upd_request['where']) {
			self::exception(self::DBEXECUTE_ERROR,
				_s('Cannot perform update statement on table "%1$s" without where condition.', $table_name)
			);
		}

		foreach ($upd_request['where'] as $field => &$values) {
			if (!array_key_exists($field, $table_schema['fields']) || $values === null) {
				self::exception(self::DBEXECUTE_ERROR,
					_s('Incorrect field "%1$s" name or value in where statement for table "%2$s".', $field, $table_name)
				);
			}

			$values = zbx_toArray($values);
			sort($values); // Sorting IDs to prevent deadlocks when two transactions depend on each other.
		}
		unset($values);
	}

	private static function updateByConditions(string $table_name, array $upd_request): void {
		$sql_where = [];

		foreach ($upd_request['where'] as $field => $values) {
			$sql_where[] = dbConditionString($field, $values);
		}

		$sql = 'UPDATE '.$table_name.
			' SET '.self::getUpdSqlValues($upd_request).
			' WHERE '.implode(' AND ', $sql_where);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s".', $sql));
		}
	}

	private static function updateByPkIds(string $table_name, array $upd_request): void {
		$sql_values = self::getUpdSqlValues($upd_request);
		$pk_field_name = key($upd_request['where']);

		$chunkids = [];

		foreach ($upd_request['where'][$pk_field_name] as $id) {
			$chunkids[] = $id;

			if (count($chunkids) == self::CHUNK_SIZE) {
				self::updateByIdField($table_name, $sql_values, $pk_field_name, $chunkids);

				$chunkids = [];
			}
		}

		if ($chunkids) {
			self::updateByIdField($table_name, $sql_values, $pk_field_name, $chunkids);
		}
	}

	private static function updateExistingRowsByPkIds(string $table_name, array $upd_request,
			string $pk_field_name): void {
		$options = [
			'output' => [$pk_field_name],
			'filter' => $upd_request['where'],
			'sortfield' => [$pk_field_name]
		];
		$resource = DBselect(self::makeSql($table_name, $options));

		$sql_values = self::getUpdSqlValues($upd_request);
		$chunkids = [];

		while ($pk_row = DBfetch($resource)) {
			$chunkids[] = $pk_row[$pk_field_name];

			if (count($chunkids) == self::CHUNK_SIZE) {
				self::updateByIdField($table_name, $sql_values, $pk_field_name, $chunkids);

				$chunkids = [];
			}
		}

		if ($chunkids) {
			self::updateByIdField($table_name, $sql_values, $pk_field_name, $chunkids);
		}
	}

	private static function getUpdSqlValues(array $upd_request): string {
		$sql_values = '';

		foreach ($upd_request['values'] as $field_name => $value) {
			if ($sql_values !== '') {
				$sql_values .= ',';
			}

			$sql_values .= $field_name.'='.$value;
		}

		return $sql_values;
	}

	private static function updateByIdField(string $table_name, string $sql_values, string $id_field_name,
			array $ids): void {
		$sql = 'UPDATE '.$table_name.
			' SET '.$sql_values.
			' WHERE '.dbConditionId($id_field_name, $ids);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s".', $sql));
		}
	}

	/**
	 * Updates the values by the given PK.
	 *
	 * @param string $tableName
	 * @param string $pk
	 * @param array  $values
	 *
	 * @return bool
	 */
	public static function updateByPk($tableName, $pk, array $values) {
		return self::update($tableName, [
			'where' => [self::getPk($tableName) => $pk],
			'values' => $values
		]);
	}

	/**
	 * Saves the given records to the database. If the record has the primary key set, it is updated, otherwise - a new
	 * record is inserted. For new records the newly generated PK is added to the result.
	 *
	 * @param $tableName
	 * @param $data
	 *
	 * @return array    the same records, that have been passed with the primary keys set for new records
	 */
	public static function save($tableName, array $data) {
		$pk = self::getPk($tableName);

		$newRecords = [];
		foreach ($data as $key => $record) {
			// if the pk is set - update the record
			if (isset($record[$pk])) {
				self::updateByPk($tableName, $record[$pk], $record);
			}
			// if no pk is set, create the record later
			else {
				$newRecords[$key] = $data[$key];
			}
		}

		// insert the new records
		if ($newRecords) {
			$newIds = self::insert($tableName, $newRecords);
			foreach ($newIds as $key => $id) {
				$data[$key][$pk] = $id;
			}
		}

		return $data;
	}

	/**
	 * Replaces the records given in $oldRecords with the ones in $newRecords.
	 *
	 * If a record with the same primary key as a new one already exists in the old records, the record is updated
	 * only if they are different. For new records the newly generated PK is added to the result. Old records that are
	 * not present in the new records are deleted.
	 *
	 * All of the records must have the primary key defined.
	 *
	 * @param string $tableName
	 * @param array  $oldRecords
	 * @param array  $newRecords
	 *
	 * @return array    the new records, that have been passed with the primary keys set for newly inserted records
	 */
	public static function replace($tableName, array $oldRecords, array $newRecords) {
		$pk = self::getPk($tableName);
		$oldRecords = zbx_toHash($oldRecords, $pk);

		$modifiedRecords = [];
		foreach ($newRecords as $key => $record) {
			// if it's a new or modified record - save it later
			if (!isset($record[$pk]) || self::recordModified($tableName, $oldRecords[$record[$pk]], $record)) {
				$modifiedRecords[$key] = $record;
			}

			// remove the existing records from the collection, the remaining ones will be deleted
			if(isset($record[$pk])) {
				unset($oldRecords[$record[$pk]]);
			}
		}

		// save modified records
		if ($modifiedRecords) {
			$modifiedRecords = self::save($tableName, $modifiedRecords);

			// add the new IDs to the new records
			foreach ($modifiedRecords as $key => $record) {
				$newRecords[$key][$pk] = $record[$pk];
			}
		}

		// delete remaining records
		if ($oldRecords) {
			self::delete($tableName, [
				$pk => array_keys($oldRecords)
			]);
		}

		return $newRecords;
	}

	/**
	 * Compares the fields, that are present in both records, and returns true if any of the values differ.
	 *
	 * @param string $tableName
	 * @param array  $oldRecord
	 * @param array  $newRecord
	 *
	 * @return bool
	 */
	public static function recordModified($tableName, array $oldRecord, array $newRecord) {
		foreach ($oldRecord as $field => $value) {
			if (self::hasField($tableName, $field)
					&& isset($newRecord[$field])
					&& (string) $value !== (string) $newRecord[$field]) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Delete data from DB.
	 *
	 * Example:
	 * DB::delete('items', ['itemid' => [1, 8, 6]]);
	 * DELETE FROM items WHERE itemid IN (1,8,6)
	 *
	 * DB::delete('items', ['itemid' => [1], 'templateid' => [10, 21]]);
	 * DELETE FROM items WHERE itemid=1 AND templateid IN (10,21)
	 *
	 * @param string $table_name
	 * @param array  $del_conditions
	 */
	public static function delete(string $table_name, array $del_conditions): void {
		$table_schema = self::getSchema($table_name);

		self::validateDelConditions($table_name, $table_schema, $del_conditions);

		if (self::hasCompositePk($table_schema)) {
			self::deleteByConditions($table_name, $del_conditions);
		}
		elseif (array_key_exists($table_schema['key'], $del_conditions) && count($del_conditions) == 1) {
			self::deleteByPkIds($table_name, $del_conditions);
		}
		else {
			self::deleteExistingRowsByPkIds($table_name, $del_conditions, $table_schema['key']);
		}
	}

	private static function validateDelConditions(string $table_name, array $table_schema,
			array &$del_conditions): void {
		if (!$del_conditions) {
			self::exception(self::DBEXECUTE_ERROR,
				_s('Cannot perform delete statement on table "%1$s" without where condition.', $table_name)
			);
		}

		foreach ($del_conditions as $field => &$values) {
			if (!array_key_exists($field, $table_schema['fields']) || $values === null) {
				self::exception(self::DBEXECUTE_ERROR,
					_s('Incorrect field "%1$s" name or value in where statement for table "%2$s".', $field, $table_name)
				);
			}

			$values = zbx_toArray($values);
			sort($values); // Sorting IDs to prevent deadlocks when two transactions depend on each other.
		}
		unset($values);
	}

	private static function hasCompositePk(array $table_schema): bool {
		return strpos($table_schema['key'], ',') !== false;
	}

	private static function deleteByConditions(string $table_name, array $del_conditions): void {
		$sql_where = [];

		foreach ($del_conditions as $field => $values) {
			$sql_where[] = dbConditionString($field, $values);
		}

		$sql = 'DELETE FROM '.$table_name.
			' WHERE '.implode(' AND ', $sql_where);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s"', $sql));
		}
	}

	private static function deleteByPkIds(string $table_name, array $del_conditions): void {
		$pk_field_name = key($del_conditions);

		$chunkids = [];

		foreach ($del_conditions[$pk_field_name] as $id) {
			$chunkids[] = $id;

			if (count($chunkids) == self::CHUNK_SIZE) {
				self::deleteByIdField($table_name, $pk_field_name, $chunkids);

				$chunkids = [];
			}
		}

		if ($chunkids) {
			self::deleteByIdField($table_name, $pk_field_name, $chunkids);
		}
	}

	private static function deleteExistingRowsByPkIds(string $table_name, array $del_conditions,
			string $pk_field_name): void {
		$options = [
			'output' => [$pk_field_name],
			'filter' => $del_conditions,
			'sortfield' => [$pk_field_name]
		];
		$resource = DBselect(self::makeSql($table_name, $options));

		$chunkids = [];

		while ($row = DBfetch($resource)) {
			$chunkids[] = $row[$pk_field_name];

			if (count($chunkids) == self::CHUNK_SIZE) {
				self::deleteByIdField($table_name, $pk_field_name, $chunkids);

				$chunkids = [];
			}
		}

		if ($chunkids) {
			self::deleteByIdField($table_name, $pk_field_name, $chunkids);
		}
	}

	private static function deleteByIdField(string $table_name, string $id_field_name, array $ids): void {
		$sql = 'DELETE FROM '.$table_name.
			' WHERE '.dbConditionId($id_field_name, $ids);

		if (!DBexecute($sql)) {
			self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s"', $sql));
		}
	}

	/**
	 * @param string $table_name
	 * @param array  $options
	 * @param string $table_alias
	 *
	 * @return string
	 */
	public static function makeSql($table_name, array &$options, $table_alias = null) {
		$defaults = [
			'output' => [],
			'countOutput' => false,
			'filter' => [],
			'search' => [],
			'startSearch' => false,
			'searchByAny' => false,
			'sortfield' => [],
			'sortorder' => [],
			'limit' => null,
			'preservekeys' => false
		];

		if ($array_diff = array_diff_key($options, $defaults)) {
			unset($array_diff[self::getPk($table_name).'s']);
			if ($array_diff) {
				self::exception(self::SCHEMA_ERROR,
					vsprintf('%s: unsupported option "%s".', [__FUNCTION__, key($array_diff)])
				);
			}
		}

		$options = zbx_array_merge($defaults, $options);

		$sql_parts = self::createSelectQueryParts($table_name, $options, $table_alias);

		return 'SELECT '.implode(',', $sql_parts['select']).
				' FROM '.implode(',', $sql_parts['from']).
				($sql_parts['where'] ? ' WHERE '.implode(' AND ', $sql_parts['where']) : '').
				($sql_parts['order'] ? ' ORDER BY '.implode(',', $sql_parts['order']) : '');
	}

	/**
	 * @param string $table_name
	 * @param array  $options
	 * @param string $table_alias
	 *
	 * @return array
	 */
	public static function select($table_name, array $options, $table_alias = null) {
		$db_result = DBSelect(self::makeSql($table_name, $options, $table_alias), $options['limit']);

		if ($options['countOutput']) {
			return DBfetch($db_result)['rowscount'];
		}

		$result = [];
		$field_names = array_flip($options['output']);

		if ($options['preservekeys']) {
			$pk = self::getPk($table_name);

			while ($db_row = DBfetch($db_result)) {
				$result[$db_row[$pk]] = array_intersect_key($db_row, $field_names);
			}
		}
		else {
			while ($db_row = DBfetch($db_result)) {
				$result[] = array_intersect_key($db_row, $field_names);
			}
		}

		return $result;
	}

	/**
	 * Returns the table name with the table alias.
	 *
	 * @param string $table_name
	 * @param string $table_alias
	 *
	 * @return string
	 */
	private static function tableId($table_name, $table_alias = null) {
		return($table_alias !== null) ? $table_name.' '.$table_alias : $table_name;
	}

	/**
	 * Prepends the table alias to the given field name.
	 *
	 * @param string $field_name
	 * @param string $table_alias
	 *
	 * @return string
	 */
	private static function fieldId($field_name, $table_alias = null) {
		return ($table_alias !== null) ? $table_alias.'.'.$field_name : $field_name;
	}

	/**
	 * Convert field to uppercase or substitute it with its pre-upcased variant.
	 *
	 * @param string      $field_name
	 * @param string      $table_name
	 * @param string|null $table_alias
	 *
	 * @return string
	 */
	public static function uppercaseField(string $field_name, string $table_name, ?string $table_alias = null): string {
		if ($table_alias === null) {
			$table_alias = $table_name;
		}

		if ($field_name === 'name' && self::hasField($table_name, 'name_upper')) {
			return $table_alias.'.name_upper';
		}

		if ($field_name === 'name_resolved' && self::hasField($table_name, 'name_resolved_upper')) {
			return $table_alias.'.name_resolved_upper';
		}

		return 'UPPER('.$table_alias.'.'.$field_name.')';
	}

	/**
	 * Convert field values to uppercase.
	 *
	 * @param string $table_name
	 * @param array  $values
	 */
	public static function uppercaseValues(string $table_name, array &$values): void {
		if (array_key_exists('name_resolved', $values) && self::hasField($table_name, 'name_resolved_upper')) {
			$values['name_resolved_upper'] = 'UPPER('.$values['name_resolved'].')';
		}
	}

	/**
	 * Builds an SQL parts array from the given options.
	 *
	 * @param string $table_name
	 * @param array  $options
	 * @param string $table_alias
	 *
	 * @return array		The resulting SQL parts array
	 */
	private static function createSelectQueryParts($table_name, array $options, $table_alias = null) {
		$sql_parts = [
			'select' => [],
			'from' => [self::tableId($table_name, $table_alias)],
			'where' => [],
			'order' => []
		];

		// add output options
		$sql_parts = self::applyQueryOutputOptions($table_name, $options, $table_alias, $sql_parts);

		// add filter options
		$sql_parts = self::applyQueryFilterOptions($table_name, $options, $table_alias, $sql_parts);

		// add search options
		$sql_parts = self::applyQuerySearchOptions($table_name, $options, $table_alias, $sql_parts);

		// add sort options
		$sql_parts = self::applyQuerySortOptions($table_name, $options, $table_alias, $sql_parts);

		return $sql_parts;
	}

	/**
	 * Modifies the SQL parts to implement all of the output related options.
	 *
	 * @param string      $table_name
	 * @param array       $options
	 * @param string|null $table_alias
	 * @param array       $sql_parts
	 *
	 * @throws APIException
	 * @throws DBException
	 * @return array
	 */
	private static function applyQueryOutputOptions($table_name, array $options, $table_alias, array $sql_parts) {
		if ($options['countOutput']) {
			$sql_parts['select'][] = 'COUNT('.self::fieldId('*', $table_alias).') AS rowscount';
		}
		else {
			$table_schema = self::getSchema($table_name);
			$select = [];
			$select[self::fieldId(self::getPk($table_name), $table_alias)] = true;

			foreach ($options['output'] as $field_name) {
				if (!array_key_exists($field_name, $table_schema['fields'])) {
					self::exception(self::SCHEMA_ERROR,
						vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name])
					);
				}

				$select[self::fieldId($field_name, $table_alias)] = true;
			}

			$sql_parts['select'] = array_keys($select);
		}

		return $sql_parts;
	}

	/**
	 * Modifies the SQL parts to implement all the filter related options.
	 *
	 * @param string      $table_name
	 * @param array       $options
	 * @param string|null $table_alias
	 * @param array       $sql_parts
	 *
	 * @throws APIException
	 * @return array
	 */
	private static function applyQueryFilterOptions($table_name, array $options, $table_alias, array $sql_parts) {
		$table_schema = self::getSchema($table_name);
		$pk = self::getPk($table_name);
		$pk_option = $pk.'s';

		// pks
		if (array_key_exists($pk_option, $options)) {
			$options[$pk_option] = (array) $options[$pk_option];

			$field_schema = $table_schema['fields'][$pk];
			$field_name = self::fieldId($pk, $table_alias);

			if ($field_schema['type'] & self::FIELD_TYPE_ID) {
				$sql_parts['where'][] = dbConditionId($field_name, $options[$pk_option]);
			}
			elseif ($field_schema['type'] & (self::FIELD_TYPE_INT | self::FIELD_TYPE_UINT)) {
				$sql_parts['where'][] = dbConditionInt($field_name, $options[$pk_option]);
			}
			else {
				$sql_parts['where'][] = dbConditionString($field_name, $options[$pk_option]);
			}
		}

		// filters
		if (is_array($options['filter'])) {
			$sql_parts = self::dbFilter($table_name, $options, $table_alias, $sql_parts);
		}

		return $sql_parts;
	}

	/**
	 * Modifies the SQL parts to implement all of the search related options.
	 *
	 * @param string      $table_name
	 * @param array       $options
	 * @param array       $options['search']
	 * @param bool        $options['startSearch']
	 * @param bool        $options['searchByAny']
	 * @param string|null $table_alias
	 * @param array       $sql_parts
	 *
	 * @throws APIException
	 * @throws DBException
	 * @return array
	 */
	private static function applyQuerySearchOptions($table_name, array $options, $table_alias, array $sql_parts) {
		global $DB;

		$table_schema = self::getSchema($table_name);

		$start = $options['startSearch'] ? '' : '%';
		$glue = $options['searchByAny'] ? ' OR ' : ' AND ';

		$search = [];

		foreach ($options['search'] as $field_name => $patterns) {
			if (!array_key_exists($field_name, $table_schema['fields'])) {
				self::exception(self::SCHEMA_ERROR,
					vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name])
				);
			}

			$field_schema = $table_schema['fields'][$field_name];

			if (($field_schema['type'] & self::SUPPORTED_SEARCH_TYPES) == 0) {
				self::exception(self::SCHEMA_ERROR,
					vsprintf('%s: field "%s.%s" has an unsupported type.', [__FUNCTION__, $table_name, $field_name])
				);
			}

			if ($patterns === null) {
				continue;
			}

			foreach ((array) $patterns as $pattern) {
				// escaping parameter that is about to be used in LIKE statement
				$pattern = mb_strtoupper(strtr($pattern, ['!' => '!!', '%' => '!%', '_' => '!_']));
				$pattern = zbx_dbstr($start.$pattern.'%');

				$search[] = self::uppercaseField($field_name, $table_name, $table_alias).' LIKE '.$pattern." ESCAPE '!'";
			}
		}

		if ($search) {
			$sql_parts['where'][] = ($options['searchByAny'] && count($search) > 1)
				? '('.implode($glue, $search).')'
				: implode($glue, $search);
		}

		return $sql_parts;
	}

	/**
	 * Apply filter conditions to sql built query.
	 *
	 * @param string      $table_name
	 * @param array       $options
	 * @param string|null $table_alias
	 * @param array       $sql_parts
	 *
	 * @throws APIException
	 * @throws DBException
	 * @return array
	 */
	private static function dbFilter($table_name, $options, $table_alias, $sql_parts) {
		$table_schema = self::getSchema($table_name);
		$filter = [];

		foreach ($options['filter'] as $field_name => $value) {
			if (!array_key_exists($field_name, $table_schema['fields'])) {
				self::exception(self::SCHEMA_ERROR,
					vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name])
				);
			}

			$field_schema = $table_schema['fields'][$field_name];

			if (($field_schema['type'] & self::SUPPORTED_FILTER_TYPES) == 0) {
				self::exception(self::SCHEMA_ERROR,
					vsprintf('%s: field "%s.%s" has an unsupported type.', [__FUNCTION__, $table_name, $field_name])
				);
			}

			if ($value === null) {
				continue;
			}

			if (!is_array($value)) {
				$value = [$value];
			}

			if ($field_schema['type'] & self::FIELD_TYPE_ID) {
				$filter[] = dbConditionId(self::fieldId($field_name, $table_alias), $value);
			}
			elseif ($field_schema['type'] & (self::FIELD_TYPE_INT | self::FIELD_TYPE_UINT)) {
				$filter[] = dbConditionInt(self::fieldId($field_name, $table_alias), $value);
			}
			else {
				$filter[] = dbConditionString(self::fieldId($field_name, $table_alias), $value);
			}
		}

		if ($filter) {
			$sql_parts['where'][] = implode(' AND ', $filter);
		}

		return $sql_parts;
	}

	/**
	 * Get array of the field names by which filtering is supported from the given table.
	 * If $output_fields parameter is given, get filterable fields among them in scope of the given table name.
	 *
	 * @param string     $table_name
	 * @param array|null $output_fields
	 *
	 * @return array
	 */
	public static function getFilterFields(string $table_name, ?array $output_fields = null): array {
		$table_schema = self::getSchema($table_name);

		if ($output_fields !== null) {
			$table_schema['fields'] = array_intersect_key($table_schema['fields'], array_flip($output_fields));
		}

		$filter_fields = [];

		foreach ($table_schema['fields'] as $field_name => $field_schema) {
			if ($field_schema['type'] & self::SUPPORTED_FILTER_TYPES) {
				$filter_fields[] = $field_name;
			}
		}

		return $filter_fields;
	}

	/**
	 * Get array of the field names by which searching is supported from the given table.
	 * If $output_fields parameter is given, get searchable fields among them in scope of the given table name.
	 *
	 * @param string     $table_name
	 * @param array|null $output_fields
	 *
	 * @return array
	 */
	public static function getSearchFields(string $table_name, ?array $output_fields = null): array {
		$table_schema = self::getSchema($table_name);

		if ($output_fields !== null) {
			$table_schema['fields'] = array_intersect_key($table_schema['fields'], array_flip($output_fields));
		}

		$search_fields = [];

		foreach ($table_schema['fields'] as $field_name => $field_schema) {
			if ($field_schema['type'] & self::SUPPORTED_SEARCH_TYPES) {
				$search_fields[] = $field_name;
			}
		}

		return $search_fields;
	}

	/**
	 * Modifies the SQL parts to implement all of the sorting related options.
	 *
	 * @param string      $table_name
	 * @param array       $options
	 * @param string|null $table_alias
	 * @param array       $sql_parts
	 *
	 * @throws APIException
	 * @throws DBException
	 * @return array
	 */
	private static function applyQuerySortOptions($table_name, array $options, $table_alias, array $sql_parts) {
		$table_schema = self::getSchema($table_name);

		foreach ($options['sortfield'] as $index => $field_name) {
			if (!array_key_exists($field_name, $table_schema['fields'])) {
				self::exception(self::SCHEMA_ERROR,
					vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name])
				);
			}

			$sortorder = '';
			if (array_key_exists($index, $options['sortorder']) && $options['sortorder'][$index] == ZBX_SORT_DOWN) {
				$sortorder = ' '.ZBX_SORT_DOWN;
			}

			$sql_parts['order'][] = self::fieldId($field_name, $table_alias).$sortorder;
		}

		return $sql_parts;
	}
}