<?php
declare(strict_types = 1);

/**
 * Moteur de recherche.
 *
 * @license https://www.gnu.org/licenses/gpl-3.0.html
 * @link https://www.igalerie.org/
 */
class Search
{
	/**
	 * Options : masques pour filtrage des données.
	 *
	 * @var array
	 */
	const OPTIONS_PATTERN =
	[
		'all_words' => 'bin',
		'columns' => '[a-z0-9_]{1,30}',
		'type' => '[a-z0-9_]{1,30}',

		// Action.
		'action' => '[a-z0-9_]{1,30}',

		// Date.
		'date' => 'bin',
		'date_end_day' => '\d{1,2}',
		'date_end_month' => '\d{1,2}',
		'date_end_year' => '\d{4}',
		'date_column' => '[a-z0-9_]{1,30}',
		'date_start_day' => '\d{1,2}',
		'date_start_month' => '\d{1,2}',
		'date_start_year' => '\d{4}',

		// Favoris.
		'favorites' => '\d{1,6}',

		// Options spécifiques aux fichiers.
		'item_comments' => 'bin',
		'item_exclude' => 'bin',
		'item_exclude_column' => '[a-z0-9_]{1,30}',
		'item_filesize' => 'bin',
		'item_filesize_end' => '\d{1,6}',
		'item_filesize_start' => '\d{1,6}',
		'item_filesize_unit' => '(?:b|kb|mb|gb)',
		'item_format' => '[a-z0-9_]{1,30}',
		'item_size' => 'bin',
		'item_size_height_end' => '\d{1,6}',
		'item_size_height_start' => '\d{1,6}',
		'item_size_width_end' => '\d{1,6}',
		'item_size_width_start' => '\d{1,6}',
		'item_tags' => 'bin',
		'item_type' => '[a-z0-9_]{1,30}',

		// Résultat.
		'result' => '(?:accepted|rejected)',

		// État.
		'status' => '\-?\d',
		'status_column' => '[a-z0-9_]{1,30}',

		// Utilisateur.
		'user' => '\d{1,12}',
		'user_table' => '[a-z0-9_]{1,30}'
	];



	/**
	 * Options de recherche.
	 *
	 * @var array
	 */
	public $options = [];

	/**
	 * Requête.
	 *
	 * @var string
	 */
	public $query = '';



	/**
	 * Code et paramètres SQL.
	 *
	 * @var array
	 */
	private $_sql = [];



	/**
	 * Enregistre en base de données les paramètres d'une recherche.
	 *
	 * @param string $query
	 *   Requête.
	 * @param array $options_values
	 *   Valeurs des options.
	 *
	 * @return mixed
	 *   Retourne l'identifiant de recherche créé,
	 *   ou FALSE si une erreur est survenue.
	 */
	public function insert(string $query, array $options_values)
	{
		// Quelques vérifications.
		if (Utility::isEmpty($query) || mb_strlen($query) > 68)
		{
			return FALSE;
		}

		// Vérifications des options.
		if (count($options_values) > 50)
		{
			return FALSE;
		}
		$this->_checkOptions($options_values);
		$options = Utility::jsonEncode($this->options);
		if (!Utility::isJson($options))
		{
			return FALSE;
		}

		// Identifiant de recherche.
		$search_id = Security::key(12);

		// Enregistrement de la requête en base de données.
		$sql = 'INSERT INTO {search}
			(search_id, search_query, search_options, search_date) VALUES (?, ?, ?, NOW())';
		if (!DB::execute($sql, [$search_id, $query, $options]))
		{
			return FALSE;
		}

		return $search_id;
	}

	/**
	 * Retourne la partie d'une clause WHERE
	 * correspondant aux critères de recherche.
	 *
	 * @return mixed
	 *   Une chaîne contenant le code SQL,
	 *   ou FALSE en cas d'erreur.
	 */
	public function sql()
	{
		if ($this->_sql)
		{
			return $this->_sql;
		}

		// Récupération et filtrage des arguments.
		$args = func_get_args();
		if (count($args) < 1 || count($args) > 2 || !is_string($args[0]))
		{
			return FALSE;
		}

		// sql(string $query, array $options)
		if (count($args) > 1)
		{
			if (!is_array($args[1]))
			{
				return FALSE;
			}
			$this->query = $args[0];
			$this->options = $args[1];
		}

		// sql(string $search_id)
		else
		{
			if (!$this->_getQuery($args[0]))
			{
				return FALSE;
			}
		}

		// Vérifications de la requête.
		if (Utility::isEmpty($this->query) || mb_strlen($this->query) > 68)
		{
			return FALSE;
		}

		// Vérifications des options.
		$this->_checkOptions($this->options);

		// Recherche dans les colonnes.
		$this->_sql = $this->_columns(
			$this->query,
			$this->options['columns'] ?? [],
			!empty($this->options['all_words'])
		);
		if (!$this->_sql)
		{
			$this->_sql = ['sql' => '1=2', 'params' => []];
		}

		// Ajout des options de recherche.
		$this->_sql['sql'] = sprintf($this->_options(), $this->_sql['sql']);

		// Identifiants de fichiers à ajouter en effectuant
		// une recherche dans d'autres tables.
		$this->_sql['sql'] = sprintf($this->_itemTables(), $this->_sql['sql']);

		// Recherche dans les favoris d'un utilisateur uniquement.
		if (isset($this->options['favorites']))
		{
			$sql_favorites = 'fav.user_id = :fav_user_id';
			$this->_sql['sql'] = sprintf("(%s AND $sql_favorites)", $this->_sql['sql']);
			$this->_sql['params']['fav_user_id'] = $this->options['favorites'];
		}

		return $this->_sql;
	}

	/**
	 * Retourne une liste de suggestions de recherche.
	 *
	 * @param string $search
	 * @param int $max
	 *
	 * @return array
	 */
	public static function suggestion(string $search, int $max = 10): array
	{
		$search = mb_strtolower($search);
		$search = substr($search, 0, 100);
		$left_space = $search[0] == ' ';
		$search = str_replace(['*', '?'], '', $search);
		$search = ltrim(preg_replace('`[^\w\s\-\']`u', ' ', $search));
		if (!preg_match('`\w`u', $search) || preg_match('`^\d{1,2}$`', $search))
		{
			return [];
		}

		// Paramètre de recherche.
		$p = $search;
		$p = '([[:<:]]|\_)' . $p;
		$p = str_replace('"', '', $p);

		// Gestion des accents.
		$utf8 = TRUE;
		if (CONF_DB_TYPE == 'mysql')
		{
			if (preg_match('`^[\x20-\x7e\xc2a0-\xc3bf]+$`', $p))
			{
				$p = self::_regexpAccents($p);
				$utf8 = FALSE;
			}
		}
		else
		{
			$p = self::_regexpAccents($p);
		}
		if (CONF_DB_TYPE == 'mysql' && version_compare(DB::$version, '8.0.4', '>='))
		{
			$p = Utility::convertPOSIXtoPCRE($p);
		}

		$p = preg_replace('`[\s-]+`', '[\s-]+', $p);

		// Titre et description des fichiers.
		$sql_item_desc = $utf8 ? 'item_desc' : 'CONVERT(item_desc USING LATIN1)';
		$sql_item_name = $utf8 ? 'item_name' : 'CONVERT(item_name USING LATIN1)';
		$sql = "SELECT item_id,
					   item_name,
					   item_desc
				  FROM {items} AS i
			 LEFT JOIN {categories} AS cat USING (cat_id)
				 WHERE item_status = '1'
				   AND " . SQL::catPerms() . "
				   AND " . SQL::catPassword() . "
				   AND ($sql_item_name REGEXP :search OR $sql_item_desc REGEXP :search)";
		DB::params(['search' => $p]);
		if (!DB::execute($sql))
		{
			return [];
		}
		$data = [];
		foreach (DB::fetchAll() as &$i)
		{
			$data[] = ['id' => $i['item_id'], 'text' => $i['item_name']];
			if ($i['item_desc'])
			{
				$data[] = ['id' => $i['item_id'], 'text' => strip_tags($i['item_desc'])];
			}
		}

		// Tags.
		if (Config::$params['tags'])
		{
			$sql_tag_name = $utf8 ? 'tag_name' : 'CONVERT(tag_name USING LATIN1)';
			$sql = "SELECT i.item_id,
						   tag_name
					  FROM {tags} AS t
				 LEFT JOIN {tags_items} AS ti
						ON t.tag_id = ti.tag_id
				 LEFT JOIN {items} AS i
						ON ti.item_id = i.item_id
				 LEFT JOIN {categories} AS cat
						ON i.cat_id = cat.cat_id
					 WHERE item_status = '1'
					   AND " . SQL::catPerms() . "
					   AND " . SQL::catPassword() . "
					   AND $sql_tag_name REGEXP :search";
			DB::params(['search' => $p]);
			if (DB::execute($sql))
			{
				foreach (DB::fetchAll() as &$i)
				{
					$data[] = ['id' => $i['item_id'], 'text' => $i['tag_name'], 'tag' => 1];
				}
			}
		}

		// Extraction des termes de la recherche.
		$terms = [];
		$search = self::_regexpAccents($search);
		$search = preg_replace('`[\s-]+`', '[\s-]+', $search);
		$regexp = $left_space
		?
		[
			'[\'\w-]{3,}\s+' . $search . '[\'\w-]*',
			'[\'\w-]{3,}\s+' . $search . '\s+[\'\w-]{3,}',
			'[\'\w-]{3,}\s+[\'\w-]*\s+' . $search . '[\'\w-]*'
		]
		:
		[
			$search . '[\'\w-]*',
			$search . '\s+[\'\w-]{3,}',
			$search . '[\'\w-]*\s+[\'\w-]{3,}',
			$search . '[\'\w-]*\s+[\'\w-]+\s+[\'\w-]{3,}'
		];
		foreach ($data as &$cols)
		{
			foreach ($regexp as &$reg)
			{
				if (preg_match('`(?:^|[^\'\w])(' . $reg . ')`ui', $cols['text'], $m)
				&& substr($m[1], -1) !== '-' && substr($m[1], 0, 1) !== '-')
				{
					$t = strtolower((string) $m[1]);
					if (isset($terms[$t]))
					{
						$terms[$t]['count']++;
						if (!in_array($cols['id'], $terms[$t]['id']))
						{
							$terms[$t]['id'][] = $cols['id'];
						}
					}
					else
					{
						$terms[$t] =
						[
							'count' => 1,
							'id' => [$cols['id']],
							'term' => !empty($cols['tag']) ? $cols['text'] : $m[1]
						];
					}
				}
			}
		}

		// Tri et construction du tableau des suggestions.
		array_multisort(array_map('strlen', array_keys($terms)), SORT_DESC, $terms);
		$suggestion = [];
		$id = [];
		foreach ($terms as &$t)
		{
			if (!in_array($t['id'], $id) && strlen((string) $t['term']) > 1)
			{
				$id[] = $t['id'];
				$suggestion[preg_replace('`[\n\r\s\t]+`', ' ', $t['term'])] = $t['count'];
			}
		}

		// Suppression des doublons.
		foreach ($suggestion as $term => &$count)
		{
			if (preg_match('`^(.+?)\s+([\'\w-]+)$`ui', (string) $term, $m))
			{
				$t = [$m[1]];
				if (preg_match('`^(.+?)\s+([\'\w-]+)$`ui', (string) $m[1], $sm))
				{
					$t[] = $sm[1];
				}
				foreach ($t as &$v)
				{
					if (isset($suggestion[$v]) && $suggestion[$v] == $count)
					{
						if (strlen((string) $m[2]) > 3)
						{
							unset($suggestion[$v]);
						}
						else
						{
							unset($suggestion[$term]);
						}
					}
				}
			}
			if (preg_match('`^([\'\w-]+)\s+(.+?)$`ui', (string) $term, $m))
			{
				$t = [$m[2]];
				if (preg_match('`^([\'\w-]+)\s+(.+?)$`ui', (string) $m[1], $sm))
				{
					$t[] = $sm[2];
				}
				foreach ($t as &$v)
				{
					if (isset($suggestion[$v]) && $suggestion[$v] == $count)
					{
						if (strlen($m[1]) > 3)
						{
							unset($suggestion[$v]);
						}
						else
						{
							unset($suggestion[$term]);
						}
					}
				}
			}
		}

		// Tri et réduction du nombre de suggestions.
		arsort($suggestion);
		$suggestion = array_slice($suggestion, 0, $max, TRUE);
		uksort($suggestion, 'strcasecmp');

		return $suggestion;
	}



	/**
	 * Vérifie le format des options.
	 *
	 * @param array $options_values
	 *
	 * @return void
	 */
	private function _checkOptions(array $options_values): void
	{
		$this->options = [];

		foreach ($options_values as $option => &$value)
		{
			if (array_key_exists($option, self::OPTIONS_PATTERN))
			{
				$pattern = self::OPTIONS_PATTERN[$option];

				// Champs de recherche (colonnes en BDD).
				if ($option == 'columns')
				{
					if (is_array($value))
					{
						foreach ($value as &$field)
						{
							if (preg_match('`^' . $pattern . '$`', (string) $field))
							{
								$this->options['columns'][] = $field;
							}
						}
					}
				}

				// Options binaires (cases à cocher).
				else if ($pattern == 'bin')
				{
					if (!empty($value))
					{
						$this->options[$option] = 1;
					}
				}

				// Autres options.
				else if (preg_match('`^' . $pattern . '$`', (string) $value))
				{
					// Dates.
					if (substr($option, -3) == 'day' || substr($option, -5) == 'month')
					{
						$value = sprintf("%'.02d", (string) $value);
					}

					$this->options[$option] = $value;
				}
			}
		}
	}

	/**
	 * Retourne la partie de la clause WHERE d'une requête SQL permettant
	 * de récupérer des objets en fonction de la requête $query dans les
	 * colonnes $cols.
	 *
	 * @param string $query
	 *   Requête.
	 * @param array $cols
	 *   Colonnes dans lesquelles effectuer la recherche.
	 * @param bool $all_words
	 *
	 * @return mixed
	 *   Une chaîne contenant le code SQL,
	 *   ou FALSE si aucun code n'a été généré.
	 */
	private function _columns(string $query, array $cols, bool $all_words = FALSE)
	{
		if (Utility::isEmpty($query) || !$cols)
		{
			return FALSE;
		}

		// Nettoyage de la requête.
		$query = Utility::trimAll($query);
		$query = Utility::deleteInvisibleChars($query);
		$query = preg_replace('`\-+`', '-', $query);
		$query = str_replace(['- ', ' *', '[:', ':]', '\\'], '', $query);
		$query = preg_replace('`\s+`', ' ', $query);

		// On ne tient pas compte de la casse.
		$query = mb_strtolower($query);

		// Décomposition de la recherche, sauf pour
		// les parties qui se trouvent entre des guillemets double.
		$query = preg_replace_callback('`\"[^\"]+\"`', function($m)
		{
			return preg_replace('`\s+`', '[[:space:]]+', $m[0]);
		}, $query);
		$query = str_replace('"', '', $query);
		$query = preg_split('`\s+`i', $query, -1, PREG_SPLIT_NO_EMPTY);

		// Méthodes "AND" ou "OR".
		$method = $all_words ? 'AND' : 'OR';

		$sql_cols = [];
		$params = [];
		$p = 1;

		// Pour chaque partie de la requête.
		foreach ($query as &$q)
		{
			// Remplacement des marqueurs d'espace.
			$q = str_replace('[[:space:]]', ' ', $q);

			// Suppression des guillemets double.
			$q = str_replace('"', '', $q);

			// On ne tient pas compte des accents (langues européennes).
			// Ne fonctionne pas correctement avec MySQL à cause des limitations
			// du moteur d'expressions régulières.
			// http://dev.mysql.com/doc/refman/5.0/en/regexp.html
			// D'où recourt à preg_match() et à CONVERT() (voir plus loin).
			$utf8 = TRUE;
			if (CONF_DB_TYPE == 'mysql')
			{
				if (preg_match('`^[\x20-\x7e\xc2a0-\xc3bf]+$`', $q))
				{
					$q = self::_regexpAccents($q);
					$utf8 = FALSE;
				}
			}
			else
			{
				$q = self::_regexpAccents($q);
			}

			// Si la requête est vide, inutile d'aller plus loin.
			if (Utility::trimAll($q) === '')
			{
				continue;
			}

			// Doit-on inclure ou exclure cette partie de la requête ?
			$sql_not = '';
			$sql_method = $method;
			if ($q[0] == '-')
			{
				$q = substr($q, 1);
				$sql_not = 'NOT ';
				$sql_method = 'AND';
			}

			// Jokers et espaces.
			$q = preg_replace('`\*+`', '*', $q);
			$q = str_replace([' ', '*', '?'], ['[^[:alnum:]]', '[^[:space:]]*', '.'], $q);

			// On ne recherche que des mots entiers.
			$q = '([[:<:]]|\_)' . $q . '([[:>:]]|\_)';

			// Champs de recherche.
			foreach ($cols as &$name)
			{
				if (!preg_match('`^[_a-z0-9]{0,50}$`i', $name))
				{
					continue;
				}
				if (!isset($sql_cols[$name]))
				{
					$sql_cols[$name] = '';
				}
				$sql_name = $utf8 ? "$name" : "CONVERT($name USING LATIN1)";
				$sql_cols[$name] .= $sql_method . (substr($name, -5) == '_path'
					? (CONF_DB_TYPE == 'mysql'
						? " SUBSTRING_INDEX($sql_name, '/', -1)"
						: " REGEXP_REPLACE($sql_name, '^.+/', '')")
					: " $sql_name") . " $sql_not REGEXP :q_$p ";
				$params['q_' . $p] = $q;
			}

			// Modifications pour MySQL 8.0.
			// https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility
			if (CONF_DB_TYPE == 'mysql' && version_compare(DB::$version, '8.0.4', '>='))
			{
				$params['q_' . $p] = Utility::convertPOSIXtoPCRE($params['q_' . $p]);
			}

			$p++;
		}

		// Préparation de la clause WHERE de la requête.
		$sql = '';
		foreach ($sql_cols as &$f)
		{
			if ($f)
			{
				$sql .= 'OR (' . preg_replace('`^(?:AND|OR) `', '', $f) . ') ';
			}
		}

		if (!$sql)
		{
			return FALSE;
		}

		$sql = '(' . preg_replace('`^OR `', '', $sql) . ')';
		$sql = str_replace([' )', '  '], [')', ' '], $sql);

		return ['sql' => $sql, 'params' => $params];
	}

	/**
	 * Récupère les paramètres d'une recherche qui ont été
	 * enregistrés en base de données, et les placent dans les
	 * attributs correspondant de la classe.
	 *
	 * @param string $search_id
	 *   Identifiant de recherche.
	 *
	 * @return bool
	 */
	private function _getQuery(string $search_id): bool
	{
		if (!preg_match('`[a-z0-9]{12}`i', $search_id))
		{
			return FALSE;
		}

		// Récupération de la requête par son identifiant.
		$sql = 'SELECT search_query, search_options FROM {search} WHERE search_id = ?';
		if (!DB::execute($sql, $search_id) || !$search = DB::fetchRow())
		{
			return FALSE;
		}

		// Mise à jour de la date.
		$sql = 'UPDATE {search} SET search_date = NOW() WHERE search_id = ?';
		DB::execute($sql, $search_id);

		// Requête.
		$this->query = $search['search_query'];

		// Options.
		$this->options = Utility::jsonDecode($search['search_options']);

		return TRUE;
	}

	/**
	 * Ajout d'identifiants de fichiers dans les critères de recherche
	 * à partir d'une recherche dans des tables spécifiques.
	 *
	 * @return string
	 */
	private function _itemTables(): string
	{
		$items_id = [];

		$sql_from = '';
		$sql_where = '';
		$params = [];
		if (isset($this->options['favorites']))
		{
			$sql_from = SQL::itemsFrom('user-favorites');
			$sql_where = ' AND fav.user_id = :fav_user_id';
			$params['fav_user_id'] = $this->options['favorites'];
		}

		// Fichiers : recherche dans les commentaires.
		if (isset($this->options['item_comments']))
		{
			// Clause WHERE pour la recherche dans les commentaires.
			$search_comments = $this->_columns(
				$this->query,
				['com_message'],
				!empty($this->options['all_words'])
			);

			// Récupération des identifiants des fichiers liés aux commentaires trouvés.
			if (is_array($search_comments))
			{
				$search_comments['sql'] = sprintf($this->_options(), $search_comments['sql']);
				$sql = "SELECT i.item_id
						  FROM {comments} AS com
					 LEFT JOIN {items} AS i
							ON i.item_id = com.item_id
							   $sql_from
						 WHERE {$search_comments['sql']}
							   $sql_where";
				DB::params($params);
				DB::params($search_comments['params']);
				if (DB::execute($sql))
				{
					$items_id = array_merge($items_id, DB::fetchAll('item_id', 'item_id'));
				}
			}
		}

		// Fichiers : recherche dans les tags.
		if (isset($this->options['item_tags']))
		{
			// Clause WHERE pour la recherche dans les tags.
			$search_tags = $this->_columns(
				$this->query,
				['tag_name'],
				!empty($this->options['all_words'])
			);

			// Récupération des identifiants des fichiers liés aux tags trouvés.
			if (is_array($search_tags))
			{
				if (empty($this->options['all_words']))
				{
					$search_tags['sql'] = sprintf($this->_options(), $search_tags['sql']);
					$sql = "SELECT i.item_id
							  FROM {tags} AS t
						 LEFT JOIN {tags_items} AS ti
								ON t.tag_id = ti.tag_id
						 LEFT JOIN {items} AS i
								ON i.item_id = ti.item_id
								   $sql_from
							 WHERE {$search_tags['sql']}
								   $sql_where";
					DB::params($params);
					DB::params($search_tags['params']);
					if (DB::execute($sql))
					{
						$items_id = array_merge($items_id, DB::fetchAll('item_id', 'item_id'));
					}
				}
				else
				{
					$sql_search_tags = explode(' AND ', substr($search_tags['sql'], 2, -2));
					$sql_search_params = array_values($search_tags['params']);
					if (count($sql_search_tags) < 10)
					{
						$tags_items_id = [];
						foreach ($sql_search_tags as $k => $sql_tag)
						{
							$sql_tag = preg_replace('`:q_\d`', ':tag_name', $sql_tag);
							$sql_tag = sprintf($this->_options(), $sql_tag);
							$sql = "SELECT i.item_id
									  FROM {tags} AS t
								 LEFT JOIN {tags_items} AS ti
										ON t.tag_id = ti.tag_id
								 LEFT JOIN {items} AS i
										ON i.item_id = ti.item_id
										   $sql_from
									 WHERE ($sql_tag)
										   $sql_where";
							DB::params($params);
							DB::params(['tag_name' => $sql_search_params[$k]]);
							if (DB::execute($sql))
							{
								$tags_items_id[] = DB::fetchAll('item_id', 'item_id');
							}
						}
						if ($tags_items_id)
						{
							foreach ($tags_items_id[0] as &$id)
							{
								for ($i = 0; $i < count($tags_items_id); $i++)
								{
									if (!in_array($id, $tags_items_id[$i]))
									{
										continue 2;
									}
								}
								$items_id[] = $id;
							}
						}
					}
				}
			}
		}

		$sql = '(%s' . ($items_id ? ' OR i.item_id IN (' . DB::inInt($items_id) . ')' : '') . ')';
		return str_replace('(%s)', '%s', $sql);
	}

	/**
	 * Options de recherche à ajouter dans la clause WHERE d'une requête SQL.
	 *
	 * @return string
	 */
	private function _options(): string
	{
		// Fichiers : dimensions.
		$sql_size = '';
		if (isset($this->options['item_size']))
		{
			$sql_size = ' AND item_height >= '
			  . (int) ($this->options['item_size_height_start'] ?? 1)
			  . ' AND item_height <= '
			  . (int) ($this->options['item_size_height_end'] ?? 100000)
			  . ' AND item_width >= '
			  . (int) ($this->options['item_size_width_start'] ?? 1)
			  . ' AND item_width <= '
			  . (int) ($this->options['item_size_width_end'] ?? 100000);
		}

		// Fichiers : poids.
		$sql_filesize = '';
		if (isset($this->options['item_filesize'])
		 && isset($this->options['item_filesize_unit']))
		{
			$m = 1024;
			switch ($this->options['item_filesize_unit'])
			{
				case 'b' : $m = 1; break;
				case 'mb' : $m = 1048576; break;
				case 'gb' : $m = 1073741824; break;
			}
			$sql_filesize = ' AND item_filesize >= '
			  . (int) ($this->options['item_filesize_start'] ?? 1) * $m
			  . ' AND item_filesize <= '
			  . (int) ($this->options['item_filesize_end'] ?? 1) * $m;
		}

		// Fichiers : exclusions.
		$sql_exclude = '';
		if (isset($this->options['item_exclude'])
		 && isset($this->options['item_exclude_column']))
		{
			switch ($column = $this->options['item_exclude_column'])
			{
				// Date de création.
				// Description.
				case 'crtdt' :
				case 'desc' :
					$sql_exclude = " AND item_$column IS NULL";
					break;

				// Commentaires.
				// Visites.
				// Votes.
				case 'comments' :
				case 'hits' :
				case 'votes' :
					$sql_exclude = " AND item_$column = 0";
					break;

				// Géolocalisation.
				case 'geoloc' :
					$sql_exclude = ' AND item_lat IS NULL AND item_long IS NULL';
					break;

				// Tags.
				case 'tags' :
					$sql = 'SELECT COUNT(*)
							  FROM {tags_items} AS ti
							 WHERE ti.item_id = i.item_id';
					$sql_exclude = " AND ($sql) = 0";
					break;
			}
		}

		// Type de fichier.
		$sql_type = '';
		if (isset($this->options['item_type']))
		{
			switch ($this->options['item_type'])
			{
				case 'image' :
					$sql_type = ' AND item_type IN (' . DB::inInt(Item::IMAGE_TYPES) . ')';
					break;

				case 'image_avif' :
					$sql_type = ' AND item_type = ' . Item::TYPE_AVIF;
					break;

				case 'image_gif' :
					$sql_type = ' AND item_type = ' . Item::TYPE_GIF;
					break;

				case 'image_gif_animated' :
					$sql_type = ' AND item_type = ' . Item::TYPE_GIF_ANIMATED;
					break;

				case 'image_jpeg' :
					$sql_type = ' AND item_type = ' . Item::TYPE_JPEG;
					break;

				case 'image_png' :
					$sql_type = ' AND item_type = ' . Item::TYPE_PNG;
					break;

				case 'image_png_animated' :
					$sql_type = ' AND item_type = ' . Item::TYPE_PNG_ANIMATED;
					break;

				case 'image_webp' :
					$sql_type = ' AND item_type = ' . Item::TYPE_WEBP;
					break;

				case 'image_webp_animated' :
					$sql_type = ' AND item_type = ' . Item::TYPE_WEBP_ANIMATED;
					break;

				case 'video' :
					$sql_type = ' AND item_type IN (' . DB::inInt(Item::VIDEO_TYPES) . ')';
					break;

				case 'video_mp4' :
					$sql_type = ' AND item_type = ' . Item::TYPE_MP4;
					break;

				case 'video_webm' :
					$sql_type = ' AND item_type = ' . Item::TYPE_WEBM;
					break;
			}
		}

		// Format d'image.
		$sql_format = '';
		if (isset($this->options['item_format']))
		{
			switch ($this->options['item_format'])
			{
				case 'landscape' :
					$sql_format = ' AND item_width > item_height';
					break;

				case 'panorama' :
					$sql_format = ' AND item_width > (item_height * 2)';
					break;

				case 'portrait' :
					$sql_format = ' AND item_height > item_width';
					break;

				case 'square' :
					$sql_format = ' AND item_height = item_width';
					break;
			}
		}

		// Activité des utilisateurs.
		$sql_users_logs = '';
		if (isset($this->options['action']))
		{
			require_once(__DIR__ . '/../admin/classes/AdminLogs.class.php');
			$list = AdminLogs::getActionsList();
			if (isset($list[$this->options['action']]))
			{
				$action = $this->options['action'] . '%%';
				$sql_users_logs .= " AND log_action LIKE '$action'";
			}
		}
		if (isset($this->options['result']))
		{
			$not = $this->options['result'] == 'accepted' ? 'NOT ' : '';
			$sql_users_logs .= " AND log_action {$not}LIKE '%%_rejected%%'";
		}

		// État.
		$sql_status = '';
		if (isset($this->options['status'])
		 && isset($this->options['status_column']))
		{
			$column = $this->options['status_column'];
			if ($column == 'item_status')
			{
				$status = $this->options['status'] == '1' ? "'1'" : "'-1', '0'";
			}
			else
			{
				$status = "'" . $this->options['status'] . "'";
			}
			$sql_status = " AND $column IN ($status)";
		}

		// Utilisateur.
		$sql_user = '';
		if (isset($this->options['user'])
		 && isset($this->options['user_table']))
		{
			$t = $this->options['user_table'];
			$sql_user = " AND $t.user_id = " . (int) $this->options['user'];
		}

		// Date.
		$sql_date = '';
		if (isset($this->options['date']))
		{
			$start_year  = $this->options['date_start_year']  ?? date('Y');
			$start_month = $this->options['date_start_month'] ?? date('m');
			$start_day   = $this->options['date_start_day']   ?? date('d');
			$end_year    = $this->options['date_end_year']    ?? date('Y');
			$end_month   = $this->options['date_end_month']   ?? date('m');
			$end_day     = $this->options['date_end_day']     ?? date('d');
			$start_date  = "$start_year-$start_month-$start_day";
			$end_date    = "$end_year-$end_month-$end_day";
			if (isset($this->options['date_column'])
			&& preg_match('`^\d{4}-\d{2}-\d{2}$`', $start_date)
			&& preg_match('`^\d{4}-\d{2}-\d{2}$`', $end_date))
			{
				$column = $this->options['date_column'];
				$sql_date = " AND $column >= '$start_date 00:00:00'
							  AND $column <= '$end_date 23:59:59'";
			}
		}

		// On ajoute les conditions de recherche
		// à la recherche dans les champs.
		$sql = '(%s' . $sql_users_logs . $sql_date . $sql_size . $sql_filesize
			. $sql_exclude . $sql_type . $sql_format . $sql_status . $sql_user . ')';

		return str_replace('(%s)', '%s', $sql);
	}

	/**
	 * Permet de construire une regexp insensible aux accents les plus courants.
	 *
	 * @param string $str
	 *
	 * @return string
	 */
	private static function _regexpAccents(string $str): string
	{
		$chars = CONF_DB_TYPE == 'mysql'
		?
		[
			'aäâàáåã', 'cç', 'eéèêë', 'iïîìí', 'nñ', 'oöôóòõ', 'uüûùú', 'yÿý'
		]
		:
		[
			'aàáâãäåāăąǎȁ', 'cçćĉċč', 'dðďđ', 'eèéêëēėęěȅ', 'gĝğġ', 'hĥħ',
			'iìíîïīİįıǐȉ', 'jĵ', 'lĺľł', 'nñńňŋ', 'oòóôõöøōőǒȍ', 'rŕřȑȓ',
			'sśŝşšș', 'tťŧț', 'uùúûüūůűųǔȕ', 'wŵẁẃẅ', 'yýÿŷȳỳ', 'zźżž'
		];
		foreach ($chars as &$c)
		{
			$str = preg_replace('`[' . $c . ']`u', '[' . $c . ']', $str);
		}
		return $str;
	}
}
?>