<?php
declare(strict_types = 1);

/**
 * Code SQL commun.
 *
 * @license https://www.gnu.org/licenses/gpl-3.0.html
 * @link https://www.igalerie.org/
 */
class SQL
{
	/**
	 * Retourne la clause ORDER BY d'une requête SQL sur la table {categories}.
	 *
	 * @param string $sql
	 *   Contenu de la clause ORDER BY.
	 * @param array $i
	 *   Informations de la catégorie.
	 *
	 * @return string
	 */
	public static function catOrderBy(string $sql, array &$i = []): string
	{
		if (!self::_checkOrderBy($sql, 'category'))
		{
			$sql = 'cat_name ASC';
		}

		// Le tri numérique ne fonctionne qu'avec MySQL.
		if (CONF_DB_TYPE != 'mysql')
		{
			$sql = str_replace('_num', '', $sql);
		}

		$cat_path = (strpos($sql, 'cat_path_num') !== FALSE
			&& isset($i['cat_path']) && preg_match('`^[-_a-z0-9]+$`i', $i['cat_path']))
			? $i['cat_path']
			: '';

		return strtr($sql,
		[
			'cat_a_items' => '(cat_a_images + cat_a_videos)',
			'cat_lastadddt' => 'cat_lastpubdt',
			'cat_name' => 'LOWER(cat_name)',
			'cat_name_num' => 'LOWER(cat_name)+0',
			'cat_path' => 'LOWER(cat_path)',
			'cat_path_num' => $cat_path
				? "REPLACE(cat_path, '$cat_path/', '')+0"
				: 'LOWER(cat_path)'
		]);
	}

	/**
	 * Retourne la partie de la clause WHERE ou SELECT d'une requête
	 * SQL permettant de déterminer si l'accès à une catégorie
	 * protégée par mot de passe est autorisée avec le jeton de
	 * session que possède l'utilisateur.
	 *
	 * Valeurs possibles pour password_auth :
	 *	   1 : Aucun mot de passe sur la catégorie.
	 *     2 : Mot de passe présent sur la catégorie et utilisateur
	 *         possédant un identifiant de session valide pour cette
	 *         catégorie.
	 *  NULL : Mot de passe présent sur la catégorie et utilisateur
	 *         ne possédant pas d'identifiant de session valide pour
	 *         cette catégorie.
	 *
	 * @param string $clause
	 *   'where' ou 'select'.
	 *
	 * @return string
	 */
	public static function catPassword(string $clause = 'where'): string
	{
		if ($session_token = Auth::getCookieSessionToken())
		{
			$sql = "SELECT 2
					  FROM {sessions} AS s
				 LEFT JOIN {sessions_categories} AS sc USING (session_id)
				 LEFT JOIN {passwords} AS p USING (cat_id)
					 WHERE cat.password_id = p.password_id
					   AND session_token = :session_token
					   AND session_expire > NOW()";
			DB::params(['session_token' => $session_token]);
		}
		else
		{
			$sql = 'NULL';
		}

		if ($clause == 'select')
		{
			return "CASE WHEN cat.password_id IS NULL THEN 1 ELSE ($sql) END AS password_auth";
		}
		else
		{
			return "(cat.password_id IS NULL OR ($sql) = 2)";
		}
	}

	/**
	 * Retourne la partie de la clause WHERE d'une requête SQL qui gère
	 * l'accès aux catégories en fonction des permissions d'accès aux
	 * catégories de l'utilisateur connecté ou faisant partie du groupe
	 * $id (si spécifié) avec liste de blocage $list (si spécifiée).
	 *
	 * @param int $id
	 *   Identifiant du groupe.
	 *   Si non fourni, utilise les informations
	 *   du groupe de l'utilisateur connecté.
	 * @param string $list
	 *   Liste de blocage : 'black' ou 'white'.
	 *   Si non fournie, utilise soit la liste de blocage définie
	 *   dans le groupe $id, soit celle de l'utilisateur connecté.
	 * @param int $is_admin
	 *   Est-ce que l'utilisateur est un administrateur ?
	 *
	 * @return string
	 */
	public static function catPerms(int $id = 0, string $list = '', int $is_admin = 0): string
	{
		static $sql_perms = [];

		// Identifiant de groupe de l'utilisateur connecté si non fourni.
		$group_id = $id > 0 ? $id : (int) (Auth::$infos['group_id'] ?? 2);

		// Si les permissions ont déjà été récupérée,
		// on retourne le code SQL déjà mémorisé.
		if (isset($sql_perms[$group_id]))
		{
			return $sql_perms[$group_id];
		}
		$sql_perms[$group_id] = '1=1';

		$fail = function()
		{
			trigger_error('Missing permissions.', E_USER_ERROR);
			die('Error: Missing permissions.');
		};

		// On n'ajoute pas les permissions pour la galerie
		// si la gestion de membres n'est pas activée.
		if (!Config::$params['users'])
		{
			return $sql_perms[$group_id];
		}

		// Si l'utilisateur est un administrateur,
		// inutile d'aller plus loin.
		if ((!$id && Auth::$isAdmin) || ($id && $is_admin))
		{
			return $sql_perms[$group_id];
		}

		// Récupération des permissions d'accès aux catégories.
		$sql = 'SELECT ga.*,
					   cat.cat_parents
				  FROM {groups_permissions} AS ga
			 LEFT JOIN {categories} AS cat
				    ON ga.cat_id = cat.cat_id
				 WHERE group_id = ?';
		if (!DB::execute($sql, $group_id))
		{
			$fail();
		}
		$perms = DB::fetchAll();

		// Classement des catégories par type de permission.
		$perms_list = ['black' => [], 'white' => []];
		$cat_parents = [];
		foreach ($perms as &$i)
		{
			$perms_list[$i['perm_list']][] = $i['cat_id'];
			$cat_parents[$i['cat_id']] = $i['cat_parents'];
		}

		// Type de liste d'accès aux catégories.
		if (!$list)
		{
			if ($id > 0)
			{
				$sql = 'SELECT group_perms FROM {groups} WHERE group_id = ?';
				if (!DB::execute($sql, $group_id) || !$group_perms = DB::fetchVal())
				{
					$fail();
				}
				$group_perms = Utility::jsonDecode($group_perms);
				if (!is_array($group_perms))
				{
					$fail();
				}
				$list = $group_perms['perm_list'];
			}
			else
			{
				$list = Auth::$groupPerms['perm_list'];
			}
		}

		$sep = Parents::SEPARATOR;

		// Liste noire.
		if ($list == 'black')
		{
			$list = $perms_list['black'];
			if (!$list)
			{
				return $sql_perms[$group_id];
			}
			$sql_perms[$group_id] = '(cat.cat_id NOT IN (' . DB::inInt($list) . ')';
			foreach ($list as &$cat_id)
			{
				$sql_perms[$group_id]
					.= " AND cat_parents NOT LIKE '%$sep" . (int) $cat_id . "$sep%'";
			}
		}

		// Liste blanche.
		else
		{
			$list = $perms_list['white'];
			if ($list)
			{
				$sql_perms[$group_id] = '(cat.cat_id IN (1, ' . DB::inInt($list) . ')';
				$parents_id = [];
				foreach ($list as &$cat_id)
				{
					$sql_perms[$group_id]
						.= " OR cat_parents LIKE '%$sep" . (int) $cat_id . "$sep%'";

					// Catégories parentes qui doivent être accessibles même si elles
					// ne sont pas en liste blanche.
					if ($p = substr($cat_parents[$cat_id], 2, -1))
					{
						$parents_id = array_merge($parents_id, explode($sep, $p));
					}
				}
				if ($parents_id)
				{
					$sql_perms[$group_id] = str_replace(
						'(cat.cat_id IN (',
						'(cat.cat_id IN (' . DB::inInt(array_unique($parents_id)) . ', ',
						$sql_perms[$group_id]
					);
				}
			}
			else
			{
				$sql_perms[$group_id] = '(cat.cat_id IN (1)';
			}
		}

		$sql_perms[$group_id] .= ')';

		return $sql_perms[$group_id];
	}

	/**
	 * Retourne un intervalle de date pour les filtres par date.
	 *
	 * @param string $column
	 *   Nom de la colonne.
	 * @param string $date
	 *   Date au format YYYY-MM-DD ou YYYY-MM ou YYYY.
	 *
	 * @return array
	 */
	public static function dateInterval(string $column, string $date): array
	{
		$sql = "$column >= :filter_1 AND $column ";
		$year = (int) substr($date, 0, 4);
		switch (strlen($date))
		{
			// Jour.
			case 10 :
				$start = "$date 00:00:00";
				$end = "$date 23:59:59";
				$sql .= '<= :filter_2';
				break;

			// Mois.
			case 7 :
				if (($month = ((int) substr($date, 5)) + 1) > 12)
				{
					$month = 1;
					$year++;
				}
				$start = "$date-01";
				$end = "$year-" . sprintf('%02s', $month) . '-01';
				$sql .= '< :filter_2';
				break;

			// Année.
			case 4 :
				$start = "$date-01-01";
				$end = ++$year . '-01-01';
				$sql .= '< :filter_2';
				break;
		}
		return ['sql' => $sql, 'params' => ['filter_1' => $start, 'filter_2' => $end]];
	}

	/**
	 * Retourne le schéma de la base de données.
	 *
	 * @param string $table_name
	 *   Retourne uniquement la requête pour la table $table_name.
	 *
	 * @return mixed
	 *   FALSE si une erreur est survenue.
	 *   Sinon, un tableau des requêtes SQL.
	 */
	public static function getSchema(string $table_name = '')
	{
		// On récupère le contenu du fichier SQL.
		if (($sql_content = file_get_contents(GALLERY_ROOT . '/install/schema.sql')) === FALSE)
		{
			return FALSE;
		}

		// On convertit les sauts de ligne.
		$sql_content = trim(Utility::LF($sql_content));

		// On crée un tableau des requêtes SQL.
		$sql_content = preg_split('`[\n]{2}`', $sql_content, -1, PREG_SPLIT_NO_EMPTY);

		foreach ($sql_content as $i => &$sql)
		{
			// On supprime les commentaires.
			if (substr(trim($sql), 0, 2) == '--')
			{
				unset($sql_content[$i]);
				continue;
			}
			$sql = preg_replace('`[\n\t\s]*--[^\n]+`', '', $sql);

			// On vérifie qu'il ne reste que des requêtes CREATE.
			if (substr($sql, 0, 6) != 'CREATE')
			{
				return FALSE;
			}

			// Si spécifiée, retourne la requête pour la table $table_name.
			if ($table_name && strstr($sql, "CREATE TABLE IF NOT EXISTS {{$table_name}}"))
			{
				return $sql;
			}
		}

		$sql_content = array_values($sql_content);

		if (count($sql_content) === 0)
		{
			return FALSE;
		}

		return $sql_content;
	}

	/**
	 * Retourne la clause FROM correspondant
	 * aux critères de recherche des fichiers à récupérer.
	 *
	 * @param string $filter
	 *
	 * @return string
	 */
	public static function itemsFrom(string $filter): string
	{
		switch ($filter)
		{
			case 'camera-brand' :
			case 'camera-model' :
				return ' LEFT JOIN {cameras_items} AS cam_i
								ON i.item_id = cam_i.item_id
						 LEFT JOIN {cameras_models} AS cam_m
								ON cam_i.camera_model_id = cam_m.camera_model_id
						 LEFT JOIN {cameras_brands} AS cam_b
								ON cam_m.camera_brand_id = cam_b.camera_brand_id';

			case 'selection' :
				return Auth::$connected
					? ' LEFT JOIN {selection} AS sel
							   ON i.item_id = sel.item_id'
					: '';

			case 'tag' :
				return ' LEFT JOIN {tags_items} AS ti
								ON i.item_id = ti.item_id';

			case 'user-favorites' :
				return ' LEFT JOIN {favorites} AS fav
								ON i.item_id = fav.item_id';

			default :
				return '';
		}
	}

	/**
	 * Retourne la clause ORDER BY d'une requête SQL sur la table {items}.
	 *
	 * @param string $filter
	 *   Nom du filtre.
	 * @param string $sql
	 *   Contenu de la clause ORDER BY.
	 * @param array $i
	 *   Informations de la catégorie.
	 *
	 * @return string
	 */
	public static function itemsOrderBy(string $filter, string $sql, array &$i = []): string
	{
		if (!self::_checkOrderBy($sql, 'album'))
		{
			$sql = 'item_pubdt DESC, item_id DESC';
		}

		// Le tri numérique ne fonctionne qu'avec MySQL.
		if (CONF_DB_TYPE != 'mysql')
		{
			$sql = str_replace('_num', '', $sql);
		}

		$sql = strtr($sql,
		[
			'item_id' => 'i.item_id',
			'item_name' => 'LOWER(item_name)',
			'item_name_num' => 'LOWER(item_name)+0',
			'item_path' => 'LOWER(item_path)',
			'item_path_num' => preg_match('`^[-_a-z0-9]+$`i', $i['cat_path'] ?? '')
				? "REPLACE(item_path, '{$i['cat_path']}/', '')+0"
				: 'LOWER(item_path)',
			'item_size' => '(item_width*item_height)'
		]);

		switch ($filter)
		{
			case 'comments' :
				$sql = "item_comments DESC, $sql";
				break;

			case 'date-created' :
				$sql = "item_crtdt DESC, $sql";
				break;

			case 'date-published' :
			case 'recent-images' :
			case 'recent-items' :
			case 'recent-videos' :
				$sql = 'item_pubdt DESC, i.item_id DESC';
				break;

			case 'favorites' :
				$sql = "item_favorites DESC, $sql";
				break;

			case 'selection' :
				if (Auth::$connected)
				{
					$sql = "selection_date DESC, $sql";
				}
				break;

			case 'user-favorites' :
				$sql = "fav.fav_date DESC, $sql";
				break;

			case 'views' :
				$sql = "item_hits DESC, $sql";
				break;

			case 'votes' :
				$sql = "item_rating DESC, item_votes DESC, $sql";
				break;
		}

		return strstr($sql, 'i.item_id') ? $sql : "$sql, i.item_id DESC";
	}

	/**
	 * Retourne la clause WHERE correspondant
	 * aux critères de recherche des fichiers à récupérer.
	 *
	 * @param string $filter
	 * @param string $value
	 *
	 * @return mixed
	 */
	public static function itemsWhere(string $filter, string $value)
	{
		$sql = '';
		$params = ['filter' => (int) $value];

		switch ($filter)
		{
			case 'camera-brand' :
				$sql = ' cam_b.camera_brand_id = :filter';
				break;

			case 'camera-model' :
				$sql = ' cam_i.camera_model_id = :filter';
				break;

			case 'comments' :
				$sql = ' item_comments > 0';
				$params = [];
				break;

			case 'date-created' :
				return self::dateInterval('item_crtdt', $value);

			case 'date-published' :
				return self::dateInterval('item_pubdt', $value);

			case 'datetime' :
				$sql = " item_adddt = :filter";
				$params = ['filter' => date('Y-m-d H:i:s', (int) $value)];
				break;

			case 'favorites' :
				$sql = ' item_favorites > 0';
				$params = [];
				break;

			case 'images' :
				$types = DB::inInt(Item::IMAGE_TYPES);
				$sql = " i.item_type IN ($types)";
				$params = [];
				break;

			case 'items' :
				$sql = ' 1=1';
				$params = [];
				break;

			case 'recent-images' :
			case 'recent-items' :
			case 'recent-videos' :
				$types = '';
				switch ($filter)
				{
					case 'recent-images' :
						$types = DB::inInt(Item::IMAGE_TYPES);
						$types = " AND i.item_type IN ($types)";
						break;

					case 'recent-videos' :
						$types = DB::inInt(Item::VIDEO_TYPES);
						$types = " AND i.item_type IN ($types)";
						break;
				}
				$sql = " item_pubdt > :filter$types";
				$params = ['filter' => date('Y-m-d H:i:s', Item::getRecentTimestamp())];
				break;

			case 'selection' :
				if (Auth::$connected)
				{
					$sql = ' sel.user_id = :filter';
					$params = ['filter' => Auth::$id];
				}
				else
				{
					$sql = ($items_id = Selection::getCookieItems())
						? ' i.item_id IN (' . DB::inInt($items_id) . ')'
						: ' 1=2';
					$params = [];
				}
				break;

			case 'tag' :
				$sql = ' ti.tag_id = :filter';
				break;

			case 'tags' :
				$tags = explode(',', $value);
				$tags_count = count($tags);
				$tags = DB::inInt($tags);
				$sql = "SELECT item_id
						  FROM {tags_items}
						 WHERE tag_id IN ($tags)
					  GROUP BY item_id
						HAVING COUNT(DISTINCT tag_id) = $tags_count";
				$sql = " i.item_id IN ($sql)";
				$params = [];
				break;

			case 'user-favorites' :
				$sql = ' fav.user_id != 2 AND fav.user_id = :filter';
				break;

			case 'user-images' :
				$types = DB::inInt(Item::IMAGE_TYPES);
				$sql = " i.user_id != 2 AND i.user_id = :filter AND i.item_type IN ($types)";
				break;

			case 'user-items' :
				$sql = ' i.user_id != 2 AND i.user_id = :filter';
				break;

			case 'user-videos' :
				$types = DB::inInt(Item::VIDEO_TYPES);
				$sql = " i.user_id != 2 AND i.user_id = :filter AND i.item_type IN ($types)";
				break;

			case 'videos' :
				$types = DB::inInt(Item::VIDEO_TYPES);
				$sql = " i.item_type IN ($types)";
				$params = [];
				break;

			case 'views' :
				$sql = ' item_hits > 0';
				$params = [];
				break;

			case 'votes' :
				$sql = ' item_votes > 0';
				$params = [];
				break;
		}

		if (!$sql)
		{
			return FALSE;
		}

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

	/**
	 * Dans la clause ORDER BY, convertit la colonne 'user_login'
	 * en 'nickname' si le champ 'Pseudonyme' est activé.
	 *
	 * @param string $sql_select
	 * @param string $sql_order_by
	 *
	 * @return void
	 */
	public static function nicknameOrderBy(&$sql_select, string &$sql_order_by): void
	{
		$sql_select = '
		   CASE WHEN user_nickname IS NULL
				THEN user_login
				ELSE user_nickname
				 END AS nickname';
		if (Config::$params['users_profile_params']['nickname']['activated'])
		{
			// On utilise nickname_order_by au lieu de LOWER(nickname)
			// pour compatibilité avec PostgreSQL qui n'accepte pas
			// l'utilisation de la fonction LOWER() sur un alias...
			if (strstr($sql_order_by, 'LOWER(user_login)') && CONF_DB_TYPE == 'pgsql')
			{
				$sql_select .= ',
				   CASE WHEN user_nickname IS NULL
						THEN LOWER(user_login)
						ELSE LOWER(user_nickname)
						 END AS nickname_order_by';
				$sql_order_by = str_replace('LOWER(user_login)',
					'nickname_order_by', $sql_order_by);
			}
			else
			{
				$sql_order_by = str_replace('user_login', 'nickname', $sql_order_by);
			}
		}
	}



	/**
	 * Vérifie la validité d'une clause ORDER BY appliquée à une catégorie.
	 *
	 * @param string $orderby
	 * @param string $type
	 *   Type de catégorie : 'album' ou 'category'.
	 *
	 * @return bool
	 */
	private static function _checkOrderBy(string $orderby, string $type): bool
	{
		if (!preg_match('`^(?:[\sa-z_]{3,30}(?:,\s)?){1,3}$`i', $orderby))
		{
			return FALSE;
		}

		$orderby = explode(',', $orderby);
		$params = Category::getOrderByParams()[$type];
		foreach ($orderby as &$e)
		{
			$e = explode(' ', trim($e));
			if (!isset($params[$e[0]]) || !in_array($e[1], ['ASC', 'DESC']))
			{
				return FALSE;
			}
		}

		return TRUE;
	}
}
?>