<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett chris@pollett.org
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models;

use seekquarry\yioop\configs as C;
use seekquarry\yioop\CronModel;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\processors\ImageProcessor;

/** For getLocaleTag*/
require_once __DIR__.'/../library/LocaleFunctions.php';
/**
 * This class is used to handle
 * database statements related to User Administration
 *
 * @author Chris Pollett
 */
class UserModel extends Model
{
    /**
     * Associations of the form
     *     name of field for web forms => database column names/abbreviations
     * In this case, things will in general map to the USERS table in the
     * Yioop data base
     * @var array
     */
    public $search_table_column_map = ["first"=>"FIRST_NAME",
        "last" => "LAST_NAME", "user" => "USER_NAME", "email"=>"EMAIL",
        "status"=>"STATUS"];
    /**
     * These fields if present in $search_array (used by @see getRows() ),
     * but with value "-1", will be skipped as part of the where clause
     * but will be used for order by clause
     * @var array
     */
    public $any_fields = ["status"];
    /**
     * {@inheritDoc}
     *
     * @param mixed $args any additional arguments which should be used to
     *     determine these tables (in this case none)
     */
    public function selectCallback($args = null)
    {
        return "USER_ID, USER_NAME, FIRST_NAME, LAST_NAME, EMAIL, STATUS";
    }
    /**
     * {@inheritDoc}
     *
     * @param mixed $args any additional arguments which should be used to
     *     determine these tables (in this case none)
     */
    public function fromCallback($args = null)
    {
        return "USERS";
    }
    /**
     * {@inheritDoc}
     *
     * @param mixed $args any additional arguments which should be used to
     *     determine these tables (in this case none)
     */
    public function whereCallback($args = null)
    {
        return "USER_ID != '" . C\PUBLIC_USER_ID . "'";
    }
    /**
     * SQL AND-clause that keeps only USER_ROLE rows (aliased UR) whose
     * grant is still active: those that never lapse or whose expiry is
     * still in the future. Used so a lapsed, unrenewed role stops
     * conferring its activities.
     *
     * @return string SQL clause beginning with a leading " AND "
     */
    private function activeUserRoleClause()
    {
        return " AND (UR.EXPIRES = " . C\FOREVER . " OR UR.EXPIRES > " .
            time() . ") ";
    }
    /**
     * Get a list of admin activities that a user is allowed to perform.
     * This includes their name and their associated method.
     *
     * @param string $user_id  id of user to get activities fors
     * @return array list of admin-activity rows the user is permitted to
     *      run (each row contains the activity's METHOD_NAME and
     *      localized name); empty array when the user is suspended or
     *      inactive
     */
    public function getUserActivities($user_id)
    {
        $db = $this->db;
        $activities = [];
        $status = $this->getUserStatus($user_id);
        if (!$status || in_array($status,
            [C\SUSPENDED_STATUS, C\INACTIVE_STATUS])) {
            return [];
        }
        $locale_tag = L\getLocaleTag();
        $limit_offset = $db->limitOffset(1);
        $sql = "SELECT LOCALE_ID FROM LOCALE ".
            "WHERE LOCALE_TAG = ? $limit_offset";
        $result = $db->execute($sql, [$locale_tag]);
        $row = $db->fetchArray($result);
        $locale_id = $row['LOCALE_ID'];
        $sql = "SELECT DISTINCT A.ACTIVITY_ID AS ACTIVITY_ID, ".
            "T.TRANSLATION_ID AS TRANSLATION_ID, A.METHOD_NAME AS METHOD_NAME,".
            " RA.ALLOWED_ARGUMENTS AS ALLOWED_ARGUMENTS," .
            " T.IDENTIFIER_STRING AS IDENTIFIER_STRING FROM ACTIVITY A, ".
            " USER_ROLE UR, ROLE_ACTIVITY RA, TRANSLATION T ".
            " WHERE UR.USER_ID = ? " . $this->activeUserRoleClause() .
            " AND UR.ROLE_ID=RA.ROLE_ID AND T.TRANSLATION_ID=A.TRANSLATION_ID ".
            " AND RA.ACTIVITY_ID = A.ACTIVITY_ID ORDER BY A.ACTIVITY_ID ASC";
        $result = $db->execute($sql, [$user_id]);
        $sub_sql = "SELECT TRANSLATION AS ACTIVITY_NAME ".
            "FROM TRANSLATION_LOCALE ".
            "WHERE TRANSLATION_ID=? AND LOCALE_ID=? $limit_offset";
            // maybe do left join at some point
        while ($row = $this->db->fetchArray($result)) {
            $id = $row['TRANSLATION_ID'];
            if (empty($activities[$id])) {
                $activities[$id] = $row;
            } else {
                if (empty($activities[$id]["ALLOWED_ARGUMENTS"]) ||
                    $activities[$id]["ALLOWED_ARGUMENTS"] == "all") {
                    // all arguments allowed
                    $activities[$id]["ALLOWED_ARGUMENTS"] = "all";
                    continue;
                } else if (empty($row["ALLOWED_ARGUMENTS"]) ||
                    trim($row["ALLOWED_ARGUMENTS"]) == "all") {
                    $activities[$id]["ALLOWED_ARGUMENTS"] = "all";
                }
                $activities[$id]["ALLOWED_ARGUMENTS"] .= ", " .
                    $row["ALLOWED_ARGUMENTS"];
            }
            $result_sub =  $db->execute($sub_sql, [$id, $locale_id]);
            $translate = $db->fetchArray($result_sub);
            if ($translate) {
                $activities[$id]['ACTIVITY_NAME'] = $translate['ACTIVITY_NAME'];
            }
            if (!isset($activities[$id]['ACTIVITY_NAME']) ||
                $activities[$id]['ACTIVITY_NAME'] == "") {
                $activities[$id]['ACTIVITY_NAME'] = $this->translateDb(
                    $activities[$id]['IDENTIFIER_STRING'],
                        C\p('DEFAULT_LOCALE'));
            }
        }
        $activities = array_values($activities);
        return $activities;
    }
    /**
     * Checks if a user is allowed to perform the activity given by
     * method name
     *
     * @param string $user_id  id of user to check
     * @param string $method_name to see if user allowed to do
     * @param bool $get_roles when true, return the list of roles that
     *  grant the activity instead of a boolean (used by the role-audit UI)
     * @return mixed bool whether the user is allowed; or, when
     *  $get_roles is true, the array of granting role ids
     */
    public function isAllowedUserActivity($user_id, $method_name,
        $get_roles = false)
    {
        $db = $this->db;
        $select = "COUNT(*)";
        $sql = "SELECT $select AS ALLOWED FROM ACTIVITY A, ".
            "USER_ROLE UR, ROLE_ACTIVITY RA WHERE UR.USER_ID = ? " .
            $this->activeUserRoleClause() .
            "AND UR.ROLE_ID=RA.ROLE_ID AND A.METHOD_NAME = ? ".
            "AND RA.ACTIVITY_ID = A.ACTIVITY_ID";
        $result = $db->execute($sql, [$user_id, $method_name]);
        $role_ids = [];
        if ($result) {
            while($row = $db->fetchArray($result)) {
                if (intval($row['ALLOWED']) > 0) {
                    return true;
                }
            }
        }
        return false;
    }
    /**
     * Records which signed-in user a session id belongs to, so the
     * session can be restored later if the server no longer has it in
     * memory (for example after it was dropped under load or the server
     * restarted). Any earlier mapping for the same session id is
     * replaced. Nothing is stored for the public (not signed-in) user.
     *
     * @param string $session_id the session id from the visitor's
     *      cookie
     * @param int $user_id id of the signed-in user
     * @param int $expires unix time after which this mapping should no
     *      longer be honored
     */
    public function setSessionUser($session_id, $user_id, $expires)
    {
        if ($user_id == C\PUBLIC_USER_ID) {
            return;
        }
        $sql = "DELETE FROM AUTH_SESSION WHERE SESSION_ID = ?";
        $this->db->execute($sql, [$session_id]);
        $sql = "INSERT INTO AUTH_SESSION VALUES (?, ?, ?)";
        $this->db->execute($sql, [$session_id, $user_id, $expires]);
    }
    /**
     * Looks up which signed-in user a session id belongs to, ignoring
     * mappings that have passed their expiry time. Used to restore a
     * session that is no longer in the server's memory.
     *
     * @param string $session_id the session id from the visitor's
     *      cookie
     * @return int the user id, or 0 when there is no usable mapping
     */
    public function getSessionUser($session_id)
    {
        $db = $this->db;
        $sql = "SELECT USER_ID FROM AUTH_SESSION " .
            "WHERE SESSION_ID = ? AND EXPIRES > ? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$session_id, time()]);
        if (!empty($result)) {
            $row = $db->fetchArray($result);
            if (!empty($row["USER_ID"])) {
                return $row["USER_ID"];
            }
        }
        return 0;
    }
    /**
     * Removes the mapping from a session id to its user, for example
     * when the person signs out so the session can no longer be
     * restored.
     *
     * @param string $session_id the session id from the visitor's
     *      cookie
     */
    public function deleteSessionUser($session_id)
    {
        $sql = "DELETE FROM AUTH_SESSION WHERE SESSION_ID = ?";
        $this->db->execute($sql, [$session_id]);
    }
    /**
     * Returns $_SESSION variable of given user from the last time
     * logged in.
     *
     * @param int $user_id id of user to get session for
     * @return array user's session data
     */
    public function getUserSession($user_id)
    {
        $db = $this->db;
        $sql = "SELECT SESSION FROM USER_SESSION ".
            "WHERE USER_ID = ? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$user_id]);
        if (!empty($result)) {
            $row = $db->fetchArray($result);
            if (!empty($row["SESSION"])) {
                $session = unserialize($row["SESSION"]);
                if (!empty($session)) {
                    return $session;
                }
            }
        }
        return [];
    }
    /**
     * Stores into DB the $session associative array of given user
     *
     * @param int $user_id id of user to store session for
     * @param array $session session data for the given user
     * @return bool true on a successful insert, false when the session
     *      exceeds MAX_USER_SESSION_SIZE, the user is PUBLIC_USER_ID,
     *      or the insert itself failed
     */
    public function setUserSession($user_id, $session)
    {
        $session_string = serialize($session);
        if (strlen($session_string) >= C\MAX_USER_SESSION_SIZE ||
            $user_id == C\PUBLIC_USER_ID) {
            return false;
        }
        $sql = "DELETE FROM USER_SESSION WHERE USER_ID = ?";
        $this->db->execute($sql, [$user_id]);
        $sql = "INSERT INTO USER_SESSION VALUES (?, ?)";
        if(!$this->db->execute($sql, [$user_id, $session_string])) {
            return false;
        }
        return true;
    }
    /**
     * Get a username by user_id
     *
     * @param string $user_id id of the user
     * @return string the name of the user corresponding to that id
     */
    public function getUsername($user_id)
    {
        $db = $this->db;
        if (intval($user_id) != $user_id) {
            return false; //keep postgres error log cleaner by doing check
        }
        $sql = "SELECT USER_NAME FROM USERS WHERE USER_ID = ?";
        $result = $db->execute($sql, [$user_id]);
        $row = $db->fetchArray($result);
        return ($row['USER_NAME'] ?? false);
    }
    /**
     * Get the status of user by user_id
     *
     * @param string $user_id id of the user
     * @return int the status flag of the user: ACTIVE, INACTIVE, INVITED,
     *      SUSPENDED
     */
    public function getUserStatus($user_id)
    {
        $db = $this->db;
        if (intval($user_id) != $user_id) {
            return false; //keep postgres error log cleaner by doing check
        }
        $sql = "SELECT STATUS FROM USERS WHERE USER_ID = ?";
        $result = $db->execute($sql, [$user_id]);
        $row = $db->fetchArray($result);
        return $row['STATUS'] ?? false;
    }
    /**
     * Returns a row from the USERS table based on a username (case-insensitive)
     *
     * @param string $username user login to be used for look up
     * @return array corresponds to the row of that user in the USERS table
     */
    public function getUser($username)
    {
        $db = $this->db;
        $sql = "SELECT * FROM USERS WHERE LOWER(USER_NAME) = LOWER(?) " .
            $db->limitOffset(1);
        $result = $db->execute($sql, [$username]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (isset($row['USER_ID'])) {
            $row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
            $row['IS_BOT_USER'] = $this->isBotUser($row['USER_ID']);
        }
        if (isset($row['IS_BOT_USER'])) {
            $sql = "SELECT BOT_TOKEN, CALLBACK_URL
                FROM CHAT_BOT WHERE USER_ID = ?";
            $result = $db->execute($sql,[$row['USER_ID']]);
            $bot_row = $db->fetchArray($result);
            if (!empty($bot_row)) {
                $row['BOT_TOKEN'] = $bot_row['BOT_TOKEN'];
                $row['CALLBACK_URL'] = $bot_row['CALLBACK_URL'];
            } else {
                $row['BOT_TOKEN'] = "";
                $row['CALLBACK_URL'] = "";
            }
        }
        return $row;
    }
    /**
     * Looks up a USERS row based on their $email (potentially not unique)
     * and the time at which their account was create in microseconds
     * @param string $email of user to lookup
     * @param string $creation_time when the user's account was created in
     *     the current epoch
     * @return array row from USERS table
     */
    public function getUserByEmailTime($email, $creation_time)
    {
        $db = $this->db;
        $sql = "SELECT * FROM USERS WHERE LOWER(EMAIL) = LOWER(?)
            AND CREATION_TIME = ? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$email, $creation_time]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (isset($row['USER_ID'])) {
            $row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
        }
        return $row;
    }
    /**
     * Looks up the account, if any, that uses a given email address.
     * Used to stop the site's own bot address from being pointed at an
     * address some member already signs up with. The match ignores
     * letter case.
     *
     * @param string $email the email address to look up
     * @return array|bool the matching user row, or false when no account
     *      uses that address
     */
    public function getUserByEmail($email)
    {
        $db = $this->db;
        $sql = "SELECT * FROM USERS WHERE LOWER(EMAIL) = LOWER(?) " .
            $db->limitOffset(1);
        $result = $db->execute($sql, [$email]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (isset($row['USER_ID'])) {
            $row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
        }
        return $row;
    }
    /**
     * Saves the personal recovery question a member typed and, when an
     * answer is given, the hash of that answer, used when the site recovers
     * accounts by question rather than by email. The question is kept as
     * plain text so it can be shown back to the member, while only a one-way
     * hash of the answer is stored, the same way a password is. Passing null
     * for the answer leaves the stored answer untouched, which lets a member
     * reword their question without retyping the answer.
     *
     * @param int $user_id numeric id of the member
     * @param string $question the recovery question the member typed
     * @param string $answer the member's answer, hashed before storage, or
     *     null to keep the answer already on file
     */
    public function setRecoveryQuestion($user_id, $question, $answer = null)
    {
        if ($answer === null) {
            $sql = "UPDATE USERS SET RECOVERY_QUESTION = ? WHERE USER_ID = ?";
            $this->db->execute($sql, [$question, $user_id]);
            return;
        }
        $sql = "UPDATE USERS SET RECOVERY_QUESTION = ?, " .
            "RECOVERY_ANSWER = ? WHERE USER_ID = ?";
        $this->db->execute($sql,
            [$question, L\crawlCrypt($answer), $user_id]);
    }
    /**
     * Checks whether a typed answer matches the stored hash of a member's
     * recovery answer, using a constant-time comparison so a wrong answer
     * and a right one take the same time to reject.
     *
     * @param array $user a USERS row holding the stored RECOVERY_ANSWER hash
     * @param string $answer the answer the person typed during recovery
     * @return bool true only when the answer matches the stored hash
     */
    public function checkRecoveryAnswer($user, $answer)
    {
        if (empty($user['RECOVERY_ANSWER'])) {
            return false;
        }
        return hash_equals($user['RECOVERY_ANSWER'],
            L\crawlCrypt($answer, $user['RECOVERY_ANSWER']));
    }
    /**
     * Looks up a user by their numeric user id and returns the whole
     * USERS row, or false when there is no such user. Used by the
     * account-activation link, which names the new member by an opaque
     * signed token holding only this id, so the email address never
     * travels in the link.
     *
     * @param int $user_id the id of the user to look up
     * @return array|bool the user's database row as an associative
     *      array, or false when no user has that id
     */
    public function getUserById($user_id)
    {
        $db = $this->db;
        $sql = "SELECT * FROM USERS WHERE USER_ID = ? " .
            $db->limitOffset(1);
        $result = $db->execute($sql, [$user_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (isset($row['USER_ID'])) {
            $row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
        }
        return $row;
    }
    /**
     * Works out the on-disk folder name and its short prefix for a user's
     * private files. The name is a crawlHash of the user id keyed by the
     * site AUTH_KEY, so it cannot be guessed from the id alone, and the
     * prefix is its first few characters, used to spread users across
     * subdirectories rather than piling them into one folder. The hash is
     * left as crawlHash on purpose: it names an existing folder on disk, so
     * changing it would orphan every user's current files.
     *
     * @param int $user_id id of the user whose folder is wanted
     * @return array the folder name followed by its prefix
     */
    public function userFolderAndPrefix($user_id)
    {
        $user_folder = L\crawlHash("user" . $user_id . C\p('AUTH_KEY'));
        $user_prefix = substr($user_folder, 0, C\FOLDER_PREFIX_LEN);
        return [$user_folder, $user_prefix];
    }
    /**
     * Returns the relative url needed to request the given users avatar icon
     *
     * @param int $user_id user to look up path for
     * @return string path to icon
     */
    public function getUserIconUrl($user_id)
    {
        $user_icon = C\SHORT_BASE_URL . "resources/anonymous.png";
        list($user_folder, $user_prefix) =
            $this->userFolderAndPrefix($user_id);
        $icon_path = C\APP_DIR .
            "/resources/$user_prefix/$user_folder/user_icon.webp";
        if (file_exists($icon_path)) {
            if (C\REDIRECTS_ON) {
                $user_icon =  C\SHORT_BASE_URL .
                    "wd/users/$user_folder/user_icon.webp";
            } else {
                $user_icon = C\SHORT_BASE_URL .
                    "?c=resource&amp;a=get&amp;f=resources&amp;" .
                    "s=$user_folder&amp;n=user_icon.webp";
            }
        }
        return $user_icon;
    }
    /**
     * Returns the path to a user's resource folder (where uploaded files
     * will be stored). It creates the folder if it does not exist
     *
     * @param int $user_id user id of user to get path for
     * @return string|bool absolute filesystem path to the user's resource
     *      folder on success; false if any of the parent directories
     *      could not be created
     */
    public function getUserIconFolder($user_id)
    {
        list($user_folder, $user_prefix) =
            $this->userFolderAndPrefix($user_id);
        $resource_path = C\APP_DIR . "/resources";
        $prefix_path = $resource_path."/$user_prefix";
        $user_path = "$prefix_path/$user_folder";
        if (file_exists($user_path)) {
            return $user_path;
        }
        if (!file_exists(C\APP_DIR) && !mkdir(C\APP_DIR)) {
            return false;
        }
        if (!file_exists($resource_path) && !mkdir($resource_path)) {
            return false;
        }
        if (!file_exists($prefix_path) && !mkdir($prefix_path)) {
            return false;
        }
        if (mkdir($user_path)) {
            return $user_path;
        }
        return false;
    }
    /**
     * Set status of user by user_id
     *
     * @param string $user_id id of the user
     * @param int $status one of ACTIVE_STATUS, INACTIVE_STATUS, or
     *      SUSPENDED_STATUS
     */
    public function updateUserStatus($user_id, $status)
    {
        $db = $this->db;
        if (!in_array($status, [C\ACTIVE_STATUS, C\INACTIVE_STATUS,
            C\SUSPENDED_STATUS])) {
            return;
        }
        $sql = "UPDATE USERS SET STATUS=? WHERE USER_ID=?";
        $db->execute($sql, [$status, $user_id]);
    }
    /**
     * Add a user with a given username and password to the list of users
     * that can login to the admin panel
     *
     * @param string $username  the username of the user to be added
     * @param string $password  the password in plaintext
     *      of the user to be added
     * @param string $firstname the firstname of the user to be added
     * @param string $lastname the lastname of the user to be added
     * @param string $email the email of the user to be added
     * @param int $status one of ACTIVE_STATUS, INACTIVE_STATUS, or
     *      SUSPENDED_STATUS
     * @return mixed false if operation not successful, user_id otherwise
     */
    public function addUser($username, $password, $firstname = '', $lastname='',
        $email = '', $status = C\ACTIVE_STATUS)
    {
        $creation_time = L\microTimestamp();
        $db = $this->db;
        $sql = "INSERT INTO USERS(FIRST_NAME, LAST_NAME,
            USER_NAME, EMAIL, PASSWORD, STATUS, HASH,
            CREATION_TIME) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?)";
        $username = mb_strtolower($username);
        $result = $db->execute($sql, [$firstname, $lastname,
            $username, $email, L\crawlCrypt($password), $status,
            L\crawlCrypt($username . C\p('AUTH_KEY') . $creation_time),
            $creation_time]);
        if (!$user_id = $this->getUserId($username)) {
            return false;
        }
        $now = time();
        $user_id = $db->escapeString($user_id);
        $sql = "INSERT INTO USER_GROUP (USER_ID, GROUP_ID, STATUS,
            JOIN_DATE) VALUES(?, ?, ?, ?)";
        $result = $db->execute($sql, [$user_id, C\PUBLIC_GROUP_ID,
            C\ACTIVE_STATUS, $now]);
        /* Naming the columns rather than relying on their order lets the
           two columns a role grant has gained since, when it lapses and
           whether it renews, take the defaults they were added with; a
           row given only two values in order is refused outright once a
           table has more columns than that. */
        $sql = "INSERT INTO USER_ROLE (USER_ID, ROLE_ID) VALUES (?, ?)";
        $result_id = $db->execute($sql, [$user_id, C\USER_ROLE]);
        return $user_id;
    }
    /**
     * Deletes a user by username from the list of users that can login to
     * the admin panel
     *
     * @param string $username  the login name of the user to delete
     */
    public function deleteUser($username)
    {
        $db = $this->db;
        $user_id = $this->getUserId($username);
        if ($user_id) {
            $sql = "DELETE FROM USER_ROLE WHERE USER_ID=?";
            $result = $db->execute($sql, [$user_id]);
            $sql = "DELETE FROM USER_GROUP WHERE USER_ID=?";
            $result = $db->execute($sql, [$user_id]);
            $sql = "DELETE FROM USER_SESSION WHERE USER_ID=?";
            $result = $db->execute($sql, [$user_id]);
        }
        $sql = "DELETE FROM USERS WHERE USER_ID=?";
        $result = $db->execute($sql, [$user_id]);
    }
    /**
     * Used to update the fields stored in a USERS row according to
     * an array holding new values
     *
     * @param array $user updated values for a USERS row
     */
    public function updateUser($user)
    {
        $user_id = $user['USER_ID'];
        if (isset($user['IMAGE_STRING'])) {
            $folder = $this->getUserIconFolder($user_id);
            $image = @imagecreatefromstring($user['IMAGE_STRING']);
            $thumb_string = ImageProcessor::createThumb($image);
            if (!empty($thumb_string)) {
                file_put_contents($folder . "/user_icon.webp",
                    $thumb_string);
                clearstatcache($folder . "/user_icon.webp");
            }
        }
        unset($user['USER_ID'], $user['IMAGE_STRING'], $user['USER_ICON'],
            $user['IS_BOT_USER'], $user['BOT_TOKEN'], $user['CALLBACK_URL']);
        $user['USER_NAME'] = mb_strtolower($this->getUserName(
            $user_id));
        if (empty($user['USER_NAME'])) {
            unset($user['USER_NAME']);
        }
        $sql = "UPDATE USERS SET ";
        $comma ="";
        $params = [];
        if ($user == []) {
            return;
        }
        foreach ($user as $field => $value) {
            $sql .= "$comma $field=? ";
            $comma = ",";
            if ($field == "PASSWORD") {
                $params[] = L\crawlCrypt($value);
            } else {
                $params[] = $value;
            }
        }
        $sql .= " WHERE USER_ID=?";
        $params[] = $user_id;
        $this->db->execute($sql, $params);
    }
    /**
     * Checks if a user is bot
     *
     * @param string $user_id  id of user to check
     * @return bool whether or not the user is bot
     */
    public function isBotUser($user_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM ".
            "USER_ROLE UR WHERE UR.USER_ID = ? ".
            "AND UR.ROLE_ID = ?";
        $is_bot = false;
        $bot_id = RoleModel::getRoleIdWithDb("Bot User", $db);
        if ($bot_id) {
            $result = $db->execute($sql, [$user_id, $bot_id]);
            if ($result) {
                while ($row = $db->fetchArray($result)) {
                    if ($row["NUM"] > 0) {
                        $is_bot = true;
                    } else {
                        $is_bot = false;
                    }
                }
            }
        }
        return $is_bot;
    }
    /**
     * Used to update the fields stored in a CHAT_BOT row according to
     * an array holding new values
     *
     * @param array $user updated values for a CHAT_BOT row
     */
    public function updateBot($user)
    {
        if ($user == []) {
            return;
        }
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM ".
            "CHAT_BOT CB WHERE CB.USER_ID = ? ";
        $result = $db->execute($sql, [$user['USER_ID']]);
        $is_new_bot = false;
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                if ($row["NUM"] > 0) {
                    $is_new_bot = false;
                } else {
                    $is_new_bot = true;
                }
            }
        }
        if (!$this->isBotUser($user["USER_ID"])) {
            if (!$is_new_bot) {
                $sql = "DELETE FROM CHAT_BOT WHERE USER_ID = ?";
                $db->execute($sql, [$user['USER_ID']]);
            }
            return;
        }
        if ($is_new_bot) {
            $user['BOT_TOKEN'] = (empty($user['BOT_TOKEN'])) ? "" :
                $user['BOT_TOKEN'];
            $user['CALLBACK_URL'] = (empty($user['CALLBACK_URL'])) ? "" :
                $user['CALLBACK_URL'];
            $sql = "INSERT INTO CHAT_BOT(USER_ID, BOT_TOKEN,
                CALLBACK_URL) VALUES (?, ?, ?)";
            $result = $db->execute($sql, [$user['USER_ID'], $user['BOT_TOKEN'],
                $user['CALLBACK_URL']]);
        } else {
            $sql = "UPDATE CHAT_BOT SET ";
            $comma = "";
            $params = [];
            foreach ($user as $field => $value) {
                if ($field == "USER_ID") {
                    $sql .= "$comma $field = ? ";
                    $comma = ",";
                    $params[] = $value;
                } else if ($field == "BOT_TOKEN") {
                    $sql .= "$comma $field = ? ";
                    $comma = ",";
                    $params[] = $value;
                } else if ($field == "CALLBACK_URL") {
                    $sql .= "$comma $field = ? ";
                    $comma = ",";
                    $params[] = $value;
                }
            }
            $sql .= " WHERE USER_ID = ?";
            $params[] = $user['USER_ID'];
            $this->db->execute($sql, $params);
        }
    }
    /**
     * Gets $num many user recommendations of the given type.
     *
     * @param int $timestamp of the recommendation media job calculation
     *      of recommendations (available using cron model
     *      item_group_recommendations)
     * @param int $user_id to user to get recommendations for
     * @param int $type = one of C\TRENDING_RECOMMENDATION,
     *      C\THREAD_RECOMMENDATION, or C\GROUP_RECOMMENDATION
     * @param int $num the number of recommendations to return
     * @return array list of recommendation rows (each having ID and NAME
     *      columns from GROUP_ITEM or SOCIAL_GROUPS depending on $type),
     *      ordered by SCORE DESC and capped at $num entries
     */
    public function getRecommendations($timestamp, $user_id, $type, $num = 3)
    {
        $db = $this->db;
        $name_table = "GROUP_ITEM";
        $name_column = "TITLE";
        $name_id = "ID";
        if ($type == C\GROUP_RECOMMENDATION) {
            $name_table = "SOCIAL_GROUPS";
            $name_column = "GROUP_NAME";
            $name_id = "GROUP_ID";
        }
        $sql = "SELECT IR.ITEM_ID AS ID, NT.$name_column AS NAME ".
            "FROM GROUP_ITEM_RECOMMENDATION IR, $name_table NT ".
            "WHERE IR.ITEM_ID = NT.$name_id AND IR.USER_ID = ? AND " .
            "ITEM_TYPE = ? AND TIMESTAMP = $timestamp " .
            "ORDER BY SCORE DESC " . $db->limitOffset($num);
        $result = $db->execute($sql, [$user_id, $type]);
        $recommendations = [];
        while($row = $db->fetchArray($result)) {
            $recommendations[$row['ID']] = $row['NAME'];
        }
        return $recommendations;
    }
    /**
     * Returns the top wiki-resource recommendations for the given user,
     * resolved from GROUP_RESOURCE_RECOMMENDATION back into a list of
     * [group_id, page_id, page_title, resource_name, sub_path] tuples
     * the UI can link to.
     *
     * @param int $user_id user whose recommendations to load
     * @param int $num max number of recommendations to return (default 3)
     * @return array list of resource-recommendation tuples ordered by
     *      SCORE DESC, capped at $num entries
     */
    public function getResourceRecommendations($user_id, $num = 3)
    {
        $db = $this->db;
        $sql = "SELECT * FROM GROUP_RESOURCE_RECOMMENDATION WHERE" .
            " USER_ID = ? ORDER BY SCORE DESC " . $db->limitOffset($num);
        $results = $db->execute($sql, [$user_id]);
        $recommendations = [];
        while($row = $db->fetchArray($results)) {
            $group_id = $row['GROUP_ID'];
            $page_id = $row['PAGE_ID'];
            $page_sql = "SELECT TITLE FROM GROUP_PAGE WHERE ID = ?";
            $result = $db->execute($page_sql, [$page_id]);
            while ($sub_row = $db->fetchArray($result)) {
                $page_title = $sub_row['TITLE'];
            }
            $index = strrpos($row['RESOURCE_PATH'], "/");
            $name = substr($row['RESOURCE_PATH'], $index + 1);
            $resource_index = strrpos($row['RESOURCE_PATH'], "/resources/");
            $sub_path = substr($row['RESOURCE_PATH'], $resource_index + 28,
                $index - $resource_index - 28);
            $recommendations[] = [$group_id, $page_id, $page_title ?? "",
                $name ?? "", $sub_path ?? ""];
        }
        return $recommendations;
    }
}
