<?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 Mallika Perepa, Chris Pollett
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models;

use seekquarry\yioop as B;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\MediaConstants;
use seekquarry\yioop\library\VersionManager;
use seekquarry\yioop\library\WikiParser;
use seekquarry\yioop\library\processors\TextProcessor;
use seekquarry\yioop\library\processors\ImageProcessor;
use seekquarry\yioop\library\processors\PdfProcessor;
use seekquarry\yioop\library\processors\VideoProcessor;
use seekquarry\yioop\models\ImpressionModel;

/**
 * This is class is used to handle db results related to Group Administration.
 * Groups are collections of
 * users who might access a common blog/news feed and set of pages. This
 * method also controls adding and deleting entries to a group feed and
 * does limited access control checks of these operations.
 *
 * @author Mallika Perepa (creator), Chris Pollett (rewrite)
 */
class GroupModel extends Model implements MediaConstants
{
    /**
     * Directory to tell WikiThumbDetailJob that a wiki resource needs a thumb
     */
    const NEEDS_THUMBS_DIR = C\APP_DIR . "/resources/needs_thumbs";
    /**
     * How many resources to process between cooperative yields while
     * assembling a media list, so a large list does not freeze the
     * single-process server. Small enough that each uninterrupted stretch
     * stays brief, large enough that the yields add little overhead.
     */
    const RESOURCE_LIST_YIELD = 64;
    /**
     * File to tell DescriptionUpdateJob that a wiki resource folder might
     * have files that need descriptions
     */
    const NEEDS_DESCRIPTION_FILE =
        C\APP_DIR . "/resources/needs_descriptions.txt";
    /**
     * Associations of the form
     *     name of field for web forms => database column names/abbreviations
     * In this case, things will in general map to the SOCIAL_GROUPS, o
     * USER_GROUP or GROUP_ITEM tables in the Yioop database
     * @var array
     */
    public $search_table_column_map = ["access" => "G.MEMBER_ACCESS",
        "created_time" => "G.CREATED_TIME",
        "group_id" => "G.GROUP_ID", "post_id" => "GI.ID",
        "join_date" => "UG.JOIN_DATE",
        "name" => "G.GROUP_NAME", "owner" => "O.USER_NAME",
        "pub_date" => "GI.PUBDATE", "parent_id"=>"GI.PARENT_ID",
        "register" => "G.REGISTER_TYPE", "status"=>"UG.STATUS",
        "user_id"=>"O.USER_ID", "voting" => "G.VOTE_ACCESS",
        "lifetime" => "G.POST_LIFETIME",
        "key" => "G.GROUP_ID"];
    /**
     * 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 = ["access", "register", "voting", "lifetime"];
    /**
     * Used to determine the select clause for SOCIAL_GROUPS table when do query
     * to marshal group objects for the controller mainly in mangeGroups
     * @param mixed $args We use $args[1] to say whether in browse mode or not.
     *     browse mode is for groups a user could join rather than ones already
     *     joined
     * @return string SQL select-clause text used by the SOCIAL_GROUPS
     *     query — either the literal "*" when no args supplied, or a
     *     comma-separated column list adapted for browse vs membership mode
     */
    public function selectCallback($args = null)
    {
        if (!is_array($args) || count($args) < 2) {
            return "*";
        }
        list($user_id, $browse, ) = $args;
        if ($browse) {
            $join_date = "";
            $status = "";
        } else {
            $join_date = ", UG.JOIN_DATE AS JOIN_DATE";
            $status = " UG.STATUS AS STATUS,";
        }
        $select = "DISTINCT G.GROUP_ID AS GROUP_ID,
            G.GROUP_NAME AS GROUP_NAME, G.OWNER_ID AS OWNER_ID,
            O.USER_NAME AS OWNER, REGISTER_TYPE, $status
            G.MEMBER_ACCESS, VOTE_ACCESS, POST_LIFETIME $join_date,
            G.CREATED_TIME GROUP_CREATION_DATE";
        return $select;
    }
    /**
     * {@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 "SOCIAL_GROUPS G, USER_GROUP UG, USERS O";
    }
    /**
     * Used to restrict getRows in which rows it returns. Rows in this
     * case corresponding to Yioop groups. The restrictions added are to
     * restrict to those group available to a given user_id and whether or
     * not the user wants groups subscribed to, or groups that could be
     * subscribed to
     *
     * @param array $args first two elements are the $user_id of the user
     *     and the $browse flag which says whether or not user is browsing
     *     through all groups to which he could subscribe and read or
     *     just those groups to which he is already subscribed.
     * @return string a SQL WHERE clause suitable to perform the above
     *     restrictions
     */
    public function whereCallback($args = null)
    {
        $db = $this->db;
        if (!is_array($args) || count($args) < 2) {
            return "";
        }
        list($user_id, $browse, ) = $args;
        if ($browse) {
            $where =
                " UG.GROUP_ID=G.GROUP_ID AND G.OWNER_ID=O.USER_ID AND NOT ".
                " EXISTS (SELECT * FROM USER_GROUP UG2 WHERE UG2.USER_ID = ".
                $db->escapeString($user_id)." AND UG2.GROUP_ID = G.GROUP_ID ".
                " AND (UG2.STATUS = " . C\ACTIVE_STATUS . " OR UG2.STATUS = ".
                C\SUSPENDED_STATUS . " ))";
        } else {
            $where = " UG.USER_ID='".$db->escapeString($user_id).
                "' AND  UG.GROUP_ID=G.GROUP_ID AND G.OWNER_ID=O.USER_ID";
        }
        $where .= " AND G.GROUP_NAME NOT LIKE '" . C\PERSONAL_GROUP_PREFIX .
            "%' ";
        return $where;
    }
    /**
     * Get an array of users that belong to a group
     *
     * @param string $group_id  the group_id to get users for
     * @param string $filter to LIKE filter users
     * @param array $sorts directions on how to sort the columns of the
     *      results format is column_name => direction
     * @param int $limit first user to get
     * @param int $num number of users to return
     * @param bool $subscribed_only when true, only members who still have
     *      this group's mail switched on (RECEIVE_MAIL) are returned; used
     *      when choosing recipients for the group's bulk mail
     * @return array of USERS rows
     */
    public function getGroupUsers($group_id, $filter = "", $sorts = [],
        $limit = "", $num = C\NUM_RESULTS_PER_PAGE,
        $subscribed_only = false)
    {
        $db = $this->db;
        if ($limit !== "") {
            $limit = $db->limitOffset($limit, $num);
        }
        $like = "";
        $param_array = [$group_id];
        if ($filter != "") {
            $like = "AND U.USER_NAME LIKE ?";
            $param_array[] = "%" . $filter . "%";
        }
        $order_by = "";
        if (!empty($sorts)) {
            $sort_fields = ["JOIN_DATE", "STATUS", "USER_NAME"];
            $directions = ["ASC", "DESC"];
            foreach ($sorts as $column_name => $direction) {
                if (in_array($column_name, $sort_fields) &&
                    in_array($direction, $directions)) {
                    if (empty($order_by)) {
                        $order_by = " ORDER BY ";
                    } else {
                        $order_by .= ",";
                    }
                    $order_by .= " $column_name $direction";
                }
            }
        }
        $users = [];
        $mail_filter = $subscribed_only ? " AND UG.RECEIVE_MAIL <> 0 " : "";
        $sql = "SELECT UG.USER_ID, U.USER_NAME AS USER_NAME,".
            " UG.GROUP_ID, G.OWNER_ID, U.EMAIL, UG.STATUS AS STATUS,".
            " UG.JOIN_DATE AS JOIN_DATE".
            " FROM USER_GROUP UG, USERS U, SOCIAL_GROUPS G".
            " WHERE UG.GROUP_ID = ? AND UG.USER_ID = U.USER_ID AND" .
            " G.GROUP_ID = UG.GROUP_ID $mail_filter $like $order_by $limit";
        $result = $db->execute($sql, $param_array);
        $i = 0;
        while ($users[$i] = $db->fetchArray($result)) {
            $i++;
        }
        unset($users[$i]); //last one will be null
        return $users;
    }
    /**
     * Get the number of users which belong to a group and whose user_name
     * matches a filter
     *
     * @param int $group_id id of the group to get a count of
     * @param string $filter to filter usernames by
     * @return int count of matching users
     */
    public function countGroupUsers($group_id, $filter="")
    {
        $db = $this->db;
        $users = [];
        $like = "";
        $users = "";
        $param_array = [$group_id];
        if ($filter != "") {
            $like = "AND UG.USER_ID = U.USER_ID AND U.USER_NAME LIKE ?";
            $users = ", USERS U";
            $param_array[] = "%" . $filter . "%";
        }
        $sql = "SELECT COUNT(DISTINCT UG.USER_ID) AS NUM ".
            " FROM USER_GROUP UG $users".
            " WHERE UG.GROUP_ID = ? $like";
        $result = $db->execute($sql, $param_array);
        if ($result) {
            $row = $db->fetchArray($result);
        }
        return $row['NUM'];
    }
    /**
     * Returns how many groups a given user owns.
     *
     * @param int $user_id user to count owned groups for
     * @return int number of groups whose owner is $user_id
     */
    public function countGroupsOwnedByUser($user_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM SOCIAL_GROUPS " .
            "WHERE OWNER_ID = ?";
        $result = $db->execute($sql, [$user_id]);
        $row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
        return $row['NUM'];
    }
    /**
     * Returns how many wiki pages a group has.
     *
     * @param int $group_id group to count wiki pages for
     * @return int number of wiki pages belonging to $group_id
     */
    public function countGroupWikiPages($group_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM GROUP_PAGE " .
            "WHERE GROUP_ID = ?";
        $result = $db->execute($sql, [$group_id]);
        $row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
        return $row['NUM'];
    }
    /**
     * Returns how many threads (top-level feed items) a group has.
     *
     * @param int $group_id group to count threads for
     * @return int number of threads belonging to $group_id
     */
    public function countGroupThreads($group_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM " .
            "WHERE GROUP_ID = ? AND PARENT_ID = ID";
        $result = $db->execute($sql, [$group_id]);
        $row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
        return $row['NUM'];
    }
    /**
     * Returns how many posts a thread has, counting the thread's own
     * starting item together with its replies.
     *
     * @param int $thread_id parent thread to count posts within
     * @return int number of posts whose parent is $thread_id
     */
    public function countThreadPosts($thread_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM " .
            "WHERE PARENT_ID = ?";
        $result = $db->execute($sql, [$thread_id]);
        $row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
        return $row['NUM'];
    }
    /**
     * Add a groupname to the database using provided string
     *
     * @param string $group_name  the groupname to be added
     * @param int $user_id user identifier of who owns the group
     * @param int $register flag that says what kinds of registration are
     *      allowed for this group INVITE_ONLY_JOIN, REQUEST_JOIN, PUBLIC_JOIN,
     *      or some group fee amount in credits 100, 200, 500, 1000, 2000
     * @param int $member flag that says how members other than the owner can
     *      access this group GROUP_READ, GROUP_READ_COMMENT (can comment
     *      on threads but not start. i.e., a blog), GROUP_READ_WRITE,
     *      (can read, comment, start threads), GROUP_READ_WIKI, (can read,
     *      comment, start threads, and edit the wiki)
     * @param int $voting flag that says how members can vote on each others
     *      posts: NON_VOTING_GROUP, UP_VOTING_GROUP, UP_DOWN_VOTING_GROUP
     * @param int $post_lifetime specifies the time in seconds that posts should
     *      live before they expire and are deleted
     * @param int $encryption 0 means don't encrypt group, 1 means encrypt
     *      group
     * @param int $render_engine what parser to use when drawing wiki pages or
     *      posts (mediawiki == C\MEDIAWIKI_ENGINE ,
     *      markdown == C\MARKDOWN_ENGINE)
     * @param bool $skip_impression whether to skip impression logging or not
     * @param int $input_timestamp optional UNIX timestamp to use for impression
     *     tracking; if set to -1 or omitted, the current time will be used
     * @return int id of group added
     */
    public function addGroup($group_name, $user_id,
        $register = C\REQUEST_JOIN, $member = C\GROUP_READ,
        $voting = C\NON_VOTING_GROUP, $post_lifetime = C\FOREVER,
        $encryption = 0, $render_engine = C\MEDIAWIKI_ENGINE,
        $skip_impression = false, $input_timestamp = -1)
    {
        $db = $this->db;
        $private_db = $this->private_db;
        $timestamp = L\microTimestamp();
        $options = ($encryption ? C\GROUP_OPTION_ENCRYPTED : 0) |
            C\GROUP_OPTION_PAGE_SOURCE_ALLOWED |
            C\GROUP_OPTION_PAGE_LIST_ALLOWED;
        $sql = "INSERT INTO SOCIAL_GROUPS (GROUP_NAME, CREATED_TIME, OWNER_ID,
            REGISTER_TYPE, MEMBER_ACCESS, VOTE_ACCESS, POST_LIFETIME,
            OPTIONS, RENDER_ENGINE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $db->execute($sql, [$group_name, $timestamp, $user_id,
            $register, $member, $voting, $post_lifetime, $options,
            $render_engine]);
        $sql = "SELECT G.GROUP_ID AS GROUP_ID FROM ".
            " SOCIAL_GROUPS G WHERE G.GROUP_NAME = ?";
        $result = $db->execute($sql, [$group_name]);
        if (!$row = $db->fetchArray($result)) {
            $last_id = -1;
        }
        $last_id = $row['GROUP_ID'];
        $now = time();
        $sql= "INSERT INTO USER_GROUP (USER_ID, GROUP_ID, STATUS,
            JOIN_DATE) VALUES
            ($user_id, $last_id, " . C\ACTIVE_STATUS . ", $now)";
        $db->execute($sql);
        if ($skip_impression === false) {
            ImpressionModel::initWithDb($user_id, $last_id, C\GROUP_IMPRESSION,
                $db, $input_timestamp);
            ImpressionModel::initWithDb(C\PUBLIC_GROUP_ID, $last_id,
                C\GROUP_IMPRESSION, $db, $input_timestamp);
        }
        if ($encryption != 0) {
            $sql = "INSERT INTO TYPE_KEYS (TYPE_ID, KEY_NAME) VALUES (?, ?)";
            //AES 256 is 32 bytes long (8*32 bits)
            $encrypt_key = base64_encode(openssl_random_pseudo_bytes(32));
            $private_db->execute($sql, [$last_id, $encrypt_key]);
        }
        return $last_id;
    }
    /**
     * Takes the passed associated array $group representing changes
     * fields of a SOCIAL_GROUPS row, and executes an UPDATE statement to
     * persist those changes fields to the database.
     *
     * @param array $group associative array with a GROUP_ID as well as the
     *     fields to update
     */
    public function updateGroup($group)
    {
        $db = $this->db;
        $group_id = $group['GROUP_ID'];
        unset($group['GROUP_ID']);
        unset($group['GROUP_NAME']);
        unset($group['OWNER']); //column not in table
        unset($group['STATUS']); // column not in table
        unset($group['JOIN_DATE']); // column not in table
        $group['OPTIONS'] =
            (!empty($group['ENCRYPTION']) ?
            C\GROUP_OPTION_ENCRYPTED : 0) |
            (!empty($group['PAGE_SOURCE_ALLOWED']) ?
            C\GROUP_OPTION_PAGE_SOURCE_ALLOWED : 0) |
            (!empty($group['PAGE_LIST_ALLOWED']) ?
            C\GROUP_OPTION_PAGE_LIST_ALLOWED : 0);
        unset($group['ENCRYPTION']);
        unset($group['PAGE_SOURCE_ALLOWED']);
        unset($group['PAGE_LIST_ALLOWED']);
        $sql = "UPDATE SOCIAL_GROUPS SET ";
        $comma ="";
        $params = [];
        foreach ($group as $field => $value) {
            $sql .= "$comma $field=? ";
            $comma = ",";
            $params[] = $value;
        }
        $sql .= " WHERE GROUP_ID=?";
        $params[] = $group_id;
        $db->execute($sql, $params);
    }
    /**
     * Check is a user given by $user_id belongs to a group given
     * by $group_id. If the field $status is sent then check if belongs
     * to the group with $status access (active, editor, invited, request,
     * banned)
     *
     * @param int $user_id user to look up
     * @param int $group_id group to check if member of
     * @param int $status membership type
     * @return int whether or not is a member
     */
    public function checkUserGroup($user_id, $group_id, $status = -1)
    {
        $db = $this->db;
        if (intval($user_id) != $user_id) {
            return C\NOT_MEMBER_STATUS;
        }
        $params = [$user_id, $group_id];
        $sql = "SELECT STATUS FROM USER_GROUP WHERE
            USER_ID=? AND GROUP_ID=?";
        if ($status >=0) {
            $sql .= " AND STATUS=?";
            $params[] = $status;
        }
        $result = $db->execute($sql, $params);
        if (!$row = $db->fetchArray($result)) {
            return false;
        }
        return $row['STATUS'];
    }
    /**
     * Looks up whether one user currently wants to receive a given
     * group's mail. Returns null when the user is not a member of the
     * group, so a caller can tell a member who opted out apart from
     * someone who is not in the group at all.
     *
     * @param int $user_id the user to look up
     * @param int $group_id the group to look up
     * @return mixed 1 if the user receives the group's mail, 0 if they
     *      have turned it off, or null if the user is not a member
     */
    public function getMailSubscription($user_id, $group_id)
    {
        $db = $this->db;
        $sql = "SELECT RECEIVE_MAIL FROM USER_GROUP " .
            "WHERE USER_ID = ? AND GROUP_ID = ?";
        $result = $db->execute($sql, [$user_id, $group_id]);
        if (!$result || !($row = $db->fetchArray($result))) {
            return null;
        }
        return intval($row['RECEIVE_MAIL']);
    }
    /**
     * Turns one user's mail from a given group on or off by setting the
     * RECEIVE_MAIL flag on their membership row. Does nothing when the
     * user is not a member of the group, since there is no membership
     * row to change.
     *
     * @param int $user_id the user whose preference is changing
     * @param int $group_id the group the preference is for
     * @param int $receive 1 to receive the group's mail, 0 to stop it
     */
    public function setMailSubscription($user_id, $group_id, $receive)
    {
        $db = $this->db;
        $receive = $receive ? 1 : 0;
        $sql = "UPDATE USER_GROUP SET RECEIVE_MAIL = ? " .
            "WHERE USER_ID = ? AND GROUP_ID = ?";
        $db->execute($sql, [$receive, $user_id, $group_id]);
    }
    /**
     * Says whether a member is following one discussion thread, meaning
     * they have asked to be mailed about it. Not following is the
     * default, so this returns true only when the member has posted to
     * the thread or pressed the follow control for it.
     *
     * @param int $user_id the member to check
     * @param int $thread_id the thread (its first post's id) to check
     * @return bool true if the member is following this thread's mail
     */
    public function isThreadSubscribed($user_id, $thread_id)
    {
        $db = $this->db;
        $sql = "SELECT USER_ID FROM THREAD_FOLLOW " .
            "WHERE USER_ID = ? AND THREAD_ID = ?";
        $result = $db->execute($sql, [$user_id, $thread_id]);
        return ($result && $db->fetchArray($result)) ? true : false;
    }
    /**
     * Starts or stops one member following a single discussion thread.
     * Following records a row saying "mail me about this thread";
     * unfollowing removes any such row. Any existing row is cleared first
     * so a thread never has two follow rows for the same member, which
     * also makes following a thread you already follow harmless.
     *
     * @param int $user_id the member whose choice is changing
     * @param int $thread_id the thread (its first post's id) the choice
     *      is for
     * @param bool $subscribe true to follow this thread (be mailed about
     *      it), false to stop following it
     */
    public function setThreadSubscribe($user_id, $thread_id,
        $subscribe)
    {
        $db = $this->db;
        $db->execute("DELETE FROM THREAD_FOLLOW " .
            "WHERE USER_ID = ? AND THREAD_ID = ?",
            [$user_id, $thread_id]);
        if ($subscribe) {
            $db->execute("INSERT INTO THREAD_FOLLOW " .
                "(USER_ID, THREAD_ID) VALUES (?, ?)",
                [$user_id, $thread_id]);
        }
    }
    /**
     * Change the status of a user in a group
     *
     * @param int $user_id of user to change
     * @param int $group_id of group to change status for
     * @param int $status what the new status should be
     */
    public function updateStatusUserGroup($user_id, $group_id, $status)
    {
        $db = $this->db;
        $sql = "UPDATE USER_GROUP SET STATUS=? WHERE
            GROUP_ID=? AND USER_ID=?";
        $db->execute($sql, [$status, $group_id, $user_id]);
    }
    /**
     * Get group id associated with groupname (so groupnames better be unique)
     *
     * @param string $group_name to use to look up a group_id
     * @return int  group_id corresponding to the groupname.
     */
    public function getGroupId($group_name)
    {
        $db = $this->db;
        $sql = "SELECT G.GROUP_ID AS GROUP_ID FROM ".
            "SOCIAL_GROUPS G WHERE G.GROUP_NAME = ? ";
        $result = $db->execute($sql, [$group_name]);
        if (!$row = $db->fetchArray($result)) {
            return -1;
        }
        return $row['GROUP_ID'];
    }
    /**
     * Returns the per-domain landing routes: which social group's Main
     * wiki page each custom-routed secure domain serves as its landing
     * page. Domains without a row use the site default landing
     * behavior and do not appear in the returned array.
     *
     * @return array associative array of lowercased domain => name of
     *      the group that domain serves
     */
    public function getDomainRoutes()
    {
        $db = $this->db;
        $routes = [];
        $sql = "SELECT DR.DOMAIN AS DOMAIN, G.GROUP_NAME AS GROUP_NAME " .
            "FROM DOMAIN_ROUTE DR, SOCIAL_GROUPS G " .
            "WHERE DR.GROUP_ID = G.GROUP_ID";
        $result = $db->execute($sql);
        if (!$result) {
            return $routes;
        }
        while ($row = $db->fetchArray($result)) {
            $routes[$row['DOMAIN']] = $row['GROUP_NAME'];
        }
        return $routes;
    }
    /**
     * Sets the landing route for a secure domain: requests for that
     * domain with no controller selected serve the Main wiki page of
     * the given group. Replaces any previous route for the domain.
     *
     * @param string $domain lowercased domain name to route
     * @param int $group_id id of the group whose Main page to serve
     */
    public function setDomainRoute($domain, $group_id)
    {
        $db = $this->db;
        $db->execute("DELETE FROM DOMAIN_ROUTE WHERE DOMAIN = ?",
            [$domain]);
        $db->execute("INSERT INTO DOMAIN_ROUTE (DOMAIN, GROUP_ID) " .
            "VALUES (?, ?)", [$domain, $group_id]);
    }
    /**
     * Removes any custom landing route for a secure domain, returning
     * it to the site default landing behavior.
     *
     * @param string $domain lowercased domain name to unroute
     */
    public function deleteDomainRoute($domain)
    {
        $this->db->execute("DELETE FROM DOMAIN_ROUTE WHERE DOMAIN = ?",
            [$domain]);
    }
    /**
     * Looks up the group whose Main wiki page the given domain serves
     * as its landing page, if a custom route has been set for it.
     *
     * @param string $domain lowercased domain name to look up
     * @return int group id the domain routes to, or 0 when the domain
     *      has no custom route (or the route table does not exist yet)
     */
    public function getDomainRouteGroupId($domain)
    {
        $db = $this->db;
        $sql = "SELECT GROUP_ID FROM DOMAIN_ROUTE WHERE DOMAIN = ?";
        $result = $db->execute($sql, [$domain]);
        if (!$result || !($row = $db->fetchArray($result))) {
            return 0;
        }
        return intval($row['GROUP_ID']);
    }
    /**
     * Returns the per-domain appearance overrides for one secure
     * domain. The stored value is a serialized associative array of
     * appearance fields (colors, logos, favicon, background image,
     * auxiliary CSS name and contents, landing page, searchbar path).
     *
     * @param string $domain lowercased domain name to look up
     * @return array appearance field => value overrides for the
     *      domain, or an empty array when the domain has no saved
     *      appearance (or the table does not exist yet)
     */
    public function getDomainAppearance($domain)
    {
        $db = $this->db;
        $sql = "SELECT APPEARANCE FROM DOMAIN_APPEARANCE " .
            "WHERE DOMAIN = ?";
        $result = $db->execute($sql, [$domain]);
        if (!$result || !($row = $db->fetchArray($result))) {
            return [];
        }
        $appearance = unserialize($row['APPEARANCE']);
        return is_array($appearance) ? $appearance : [];
    }
    /**
     * Saves the per-domain appearance overrides for one secure
     * domain, replacing any previous overrides for that domain. The
     * appearance array is serialized into a single row.
     *
     * @param string $domain lowercased domain name to store
     *      appearance for
     * @param array $appearance appearance field => value overrides to
     *      store for the domain
     */
    public function setDomainAppearance($domain, $appearance)
    {
        $db = $this->db;
        $db->execute("DELETE FROM DOMAIN_APPEARANCE WHERE DOMAIN = ?",
            [$domain]);
        $db->execute("INSERT INTO DOMAIN_APPEARANCE (DOMAIN, " .
            "APPEARANCE) VALUES (?, ?)",
            [$domain, serialize($appearance)]);
    }
    /**
     * Removes any per-domain appearance overrides for a secure
     * domain, returning it to the global Appearance settings.
     *
     * @param string $domain lowercased domain name to clear
     *      appearance for
     */
    public function deleteDomainAppearance($domain)
    {
        $this->db->execute(
            "DELETE FROM DOMAIN_APPEARANCE WHERE DOMAIN = ?",
            [$domain]);
    }
    /**
     * Get group id of personal group of a User.
     *
     * @param string $user_id to use to look up a group_id
     * @return int  group_id of personal group of that user
     */
    public function getPersonalGroupId($user_id)
    {
        return $this->getGroupId(C\PERSONAL_GROUP_PREFIX . $user_id);
    }
    /**
     * Get group id associated with groupname (so groupnames better be unique)
     *
     * @param int $group_id to use to look up a group name
     * @return string group_name corresponding to the id.
     */
    public function getGroupName($group_id)
    {
        $db = $this->db;
        $sql = "SELECT G.GROUP_NAME AS GROUP_NAME FROM ".
            "SOCIAL_GROUPS G WHERE G.GROUP_ID = ? ";
        $result = $db->execute($sql, [$group_id]);
        if (!$row = $db->fetchArray($result)) {
            return false;
        }
        return $row['GROUP_NAME'];
    }
    /**
     * Check whether group's encryption is enabled or not
     *
     * @param int $group_id to check for encryption value
     * @return boolean whether the group is encrypted or not
     */
    public function isGroupEncrypted($group_id)
    {
        $db = $this->db;
        $sql = "SELECT OPTIONS FROM SOCIAL_GROUPS WHERE GROUP_ID = ? ";
        $result = $db->execute($sql, [$group_id]);
        if (!$row = $db->fetchArray($result)) {
            return false;
        }
        $encryption =
            (intval($row['OPTIONS']) & C\GROUP_OPTION_ENCRYPTED);
        return ($encryption != 0);
    }
    /**
     * Return the engine code to be used by the group's wiki parser
     * 0 == mediawiki, 1 == markdown
     *
     * @param int $group_id to check for render_engine code of
     * @return int code for parser want
     */
    public function getRenderEngine($group_id)
    {
        $db = $this->db;
        $sql = "SELECT RENDER_ENGINE FROM SOCIAL_GROUPS WHERE GROUP_ID = ? ";
        $result = $db->execute($sql, [$group_id]);
        if (!$row = $db->fetchArray($result)) {
            return 0;
        }
        $render_code = intval($row['RENDER_ENGINE']);
        return $render_code;
    }
    /**
     * Delete a group from the database and any associated data in
     * GROUP_ITEM and USER_GROUP tables.
     *
     * @param string $group_id id of the group to delete
     */
    public function deleteGroup($group_id)
    {
        $db = $this->db;
        $private_db = $this->private_db;
        $params = [$group_id];
        $sql = "DELETE FROM SOCIAL_GROUPS WHERE GROUP_ID=?";
        $db->execute($sql, $params);
        $sql = "DELETE FROM GROUP_ITEM WHERE GROUP_ID=?";
        $db->execute($sql, $params);
        $sql = "SELECT ID FROM GROUP_PAGE WHERE GROUP_ID=?";
        $result = $db->execute($sql, $params);
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                list($folder, $thumb_folder, ) =
                    $this->getGroupPageResourcesFolders($group_id,
                    $row['ID'], "", false, false);
                if (file_exists($folder)) {
                    $db->unlinkRecursive($folder);
                }
                if (file_exists($thumb_folder)) {
                    $db->unlinkRecursive($thumb_folder);
                }
            }
        }
        $sql = "DELETE FROM GROUP_PAGE WHERE GROUP_ID=?";
        $db->execute($sql, $params);
        $sql = "DELETE FROM GROUP_PAGE_HISTORY WHERE GROUP_ID=?";
        $db->execute($sql, $params);
        $sql = "DELETE FROM TYPE_KEYS WHERE TYPE_ID=?";
        $private_db->execute($sql, $params);
    }
    /**
     * Deletes a wiki page from a group from the database
     *
     * @param string $group_id id of the group to delete
     * @param string $page_name name of wiki page to delete
     * @param string $locale_tag locale of page to delete
     * @return bool whether page was deleted
     */
    public function deleteGroupPage($group_id, $page_name, $locale_tag)
    {
        $db = $this->db;
        $page_id = $this->getPageId($group_id, $page_name, $locale_tag);
        if (!$page_id) {
            return false;
        }
        list($folder, $thumb_folder, ) =
            $this->getGroupPageResourcesFolders($group_id, $page_id, "", false,
            false);
        if ($folder && file_exists($folder)) {
            $db->unlinkRecursive($folder);
        }
        if ($thumb_folder && file_exists($thumb_folder)) {
            $db->unlinkRecursive($thumb_folder);
        }
        $params = [$page_id];
        $sql = "DELETE FROM GROUP_PAGE WHERE ID=?";
        $db->execute($sql, $params);
        $sql = "DELETE FROM GROUP_PAGE_HISTORY WHERE PAGE_ID=?";
        $db->execute($sql, $params);
        return true;
    }
    /**
     * Return the type of the registration for a group given by $group_id
     * This says who is allowed to register for the group (i.e., is it
     *  by invitation only, by request, or anyone can join)
     *
     * @param int $group_id which group to find the type of
     * @return int the numeric code for the registration type
     */
    public function getRegisterType($group_id)
    {
        $db = $this->db;
        $groups = [];
        $sql = "SELECT REGISTER_TYPE FROM SOCIAL_GROUPS G WHERE GROUP_ID=?";
        $result = $db->execute($sql, [$group_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        return $row['REGISTER_TYPE'];
    }
    /**
     * Returns information about the group with id $group_id provided
     * that the requesting user $user_id has access to it
     *
     * @param int $group_id id of group to look up
     * @param int $user_id user asking for group info
     * @param bool $require_root_or_member require the $user_id to be in the
     *      group or root
     * @return array row from group table or false (if no access or doesn't
     *     exists)
     */
    public function getGroupById($group_id, $user_id,
        $require_root_or_member = false)
    {
        $db = $this->db;
        if (empty($group_id)) {
            return false;
        }
        if (!is_numeric($user_id)) { //Postgres strict about types so being safe
            $user_id = C\PUBLIC_USER_ID;
        }
        $where = " WHERE ";
        $params = [":group_id" => $group_id];
        if ($user_id != C\ROOT_ID) {
            if ($require_root_or_member) {
                $where .= " (UG.USER_ID = :user_id) AND ";
            } else {
                $where .= " (UG.USER_ID = :user_id OR G.REGISTER_TYPE IN (".
                    C\PUBLIC_BROWSE_REQUEST_JOIN . ",". C\PUBLIC_JOIN .
                    ")) AND ";
            }
            $params[":user_id"] = $user_id;
        }
        $where .= " UG.GROUP_ID= :group_id".
            " AND  UG.GROUP_ID=G.GROUP_ID AND OWNER_ID = O.USER_ID";
        $sql = "SELECT G.GROUP_ID AS GROUP_ID,
            G.GROUP_NAME AS GROUP_NAME,
            G.OWNER_ID AS OWNER_ID, O.USER_NAME AS OWNER, REGISTER_TYPE,
            UG.STATUS AS STATUS, G.MEMBER_ACCESS AS MEMBER_ACCESS,
            G.VOTE_ACCESS AS VOTE_ACCESS, G.POST_LIFETIME AS POST_LIFETIME,
            UG.JOIN_DATE AS JOIN_DATE, G.OPTIONS AS OPTIONS,
            G.RENDER_ENGINE AS RENDER_ENGINE
            FROM SOCIAL_GROUPS G, USERS O, USER_GROUP UG $where " .
            $db->limitOffset(1);
        $result = $db->execute($sql, $params);
        $group = false;
        if ($result) {
            $group = $db->fetchArray($result);
            if (!$group) {
                return false;
            }
            $group['ENCRYPTION'] =
                (intval($group['OPTIONS']) & C\GROUP_OPTION_ENCRYPTED)
                ? 1 : 0;
            $group['PAGE_SOURCE_ALLOWED'] = (intval($group['OPTIONS']) &
                C\GROUP_OPTION_PAGE_SOURCE_ALLOWED) ? 1 : 0;
            $group['PAGE_LIST_ALLOWED'] = (intval($group['OPTIONS']) &
                C\GROUP_OPTION_PAGE_LIST_ALLOWED) ? 1 : 0;
            if (!$require_root_or_member) {
                $sql = "SELECT STATUS FROM USER_GROUP WHERE
                    USER_ID=? AND GROUP_ID=? ".$db->limitOffset(1);
                $params = [$user_id, $group_id];
                $result = $db->execute($sql, $params);
                if ($result) {
                    $row = $db->fetchArray($result);
                    if ($row) {
                        $group['STATUS'] = $row['STATUS'];
                    } else {
                        $group['STATUS'] = C\NOT_MEMBER_STATUS;
                    }
                } else {
                    $group['STATUS'] = C\NOT_MEMBER_STATUS;
                }
            }
        }
        if (!$group) {
            return false;
        }
        return $group;
    }
    /**
     * Get a list of all groups which user_id belongs to. Group names
     * are not localized since these are
     * created by end user admins of the search engine
     *
     * @param int $user_id to get groups for
     * @param string $filter to LIKE filter groups
     * @param array $sorts directions on how to sort the columns of the
     *      results format is column_name => direction
     * @param int $limit first user to get
     * @param int $num number of users to return
     * @param bool $include_personal whether to include $user_id's personal
     *  group
     * @param array $allowed_statuses a list of group membership statuses
     *  that to restrict the returned list to
     * @return array an array of group_id, group_name pairs
     */
    public function getUserGroups($user_id, $filter, $sorts, $limit,
        $num = C\NUM_RESULTS_PER_PAGE, $include_personal = false,
        $allowed_statuses = [C\ACTIVE_STATUS, C\EDITOR_STATUS])
    {
        $db = $this->db;
        $groups = [];
        $like = "";
        $param_array = [$user_id];
        $exclude_personal_group = ($include_personal) ? " " :
            " AND G.GROUP_NAME NOT LIKE '%" . C\PERSONAL_GROUP_PREFIX . "%'";
        $status_where = "";
        $status_delim = " AND (";
        foreach ($allowed_statuses as $allowed_status) {
            $status_where .= $status_delim . "  UG.STATUS = $allowed_status";
            $status_delim = " OR ";
        }
        if (!empty($status_where)) {
            $status_where .= ") ";
        }
        if ($filter != "") {
            $like = "AND G.GROUP_NAME LIKE ?";
            $param_array[] = "%".$filter."%";
        }
        $order_by = "";
        if (!empty($sorts)) {
            $sort_fields = ["GROUP_NAME", "STATUS"];
            $directions = ["ASC", "DESC"];
            foreach ($sorts as $column_name => $direction) {
                if (in_array($column_name, $sort_fields) &&
                    in_array($direction, $directions)) {
                    if (empty($order_by)) {
                        $order_by = " ORDER BY ";
                    } else {
                        $order_by .= ",";
                    }
                    $order_by .= " $column_name $direction";
                }
            }
        }
        $limit = $db->limitOffset($limit, $num);
        $sql = "SELECT UG.GROUP_ID AS GROUP_ID, UG.USER_ID AS USER_ID," .
            " G.GROUP_NAME AS GROUP_NAME, UG.STATUS AS STATUS, ".
            " G.OWNER_ID " .
            " FROM USER_GROUP UG, SOCIAL_GROUPS G" .
            " WHERE USER_ID = ? AND UG.GROUP_ID = G.GROUP_ID $status_where" .
            " $exclude_personal_group $like $order_by $limit";
        $result = $db->execute($sql, $param_array);
        $groups = [];
        while ($group = $db->fetchArray($result)) {
            $groups[] = $group;
        }
        return $groups;
    }
    /**
     * Get user id's of all members of a group determined by a group_id
     *
     * @param int $group_id id of group to get members for
     * @return array of the group's user ids
     */
    public function getGroupUserIds($group_id)
    {
        $db = $this->db;
        $sql = "SELECT USER_ID FROM USER_GROUP WHERE GROUP_ID = ?";
        $result = $db->execute($sql, [$group_id]);
        if (empty($result)) {
            return false;
        }
        $user_ids = [];
        while ($row = $db->fetchArray($result)) {
            if (!empty($row['USER_ID'])) {
                $user_ids[] = $row['USER_ID'];
            }
        }
        return $user_ids;
    }
    /**
     * Checks is a given $user_id is in the list of users
     * of the personcal group of a $contact_user_id
     *
     * @param int $user_id user id to check if in other personal group
     * @param int $contact_user_id user id to check personal group of
     * @return bool whether $user_id was in list of contacts or not
     */
    public function isUserIdInContacts($user_id, $contact_user_id)
    {
        $db = $this->db;
        $sql = "SELECT UG.STATUS FROM USER_GROUP UG, ".
            "SOCIAL_GROUPS G WHERE UG.USER_ID = ? AND " .
            "G.GROUP_NAME = ? AND  UG.GROUP_ID = G.GROUP_ID";
        $contact_group = C\PERSONAL_GROUP_PREFIX . $contact_user_id;
        $result = $db->execute($sql, [$user_id, $contact_group]);
        if (empty($result) || empty($db->fetchArray($result))) {
            return false;
        }
        return true;
    }
    /**
     * Get the PARENT_ID of a thread based on the group-id opf group thread is
     * in, the user_id of the user posting the thread and the exact thread title
     *
     * @param int $group_id id of group thread is in
     * @param int $user_id id of user posting to thread
     * @param string $title exact title of thread
     * @return int the id of the first thread matching the above criteria
     */
    public function getGroupThreadId($group_id, $user_id, $title)
    {
        $db = $this->db;
        if ($user_id !== null) {
            $sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND " .
                "USER_ID=? AND TITLE=? " . $db->limitOffset(1);
            $result = $db->execute($sql, [$group_id, $user_id, $title]);
        } else {
            $sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND " .
                "TITLE=? " . $db->limitOffset(1);
            $result = $db->execute($sql, [$group_id, $title]);
        }
        $row = $db->fetchArray($result);
        return $row["PARENT_ID"] ?? false;
    }
    /**
     * Get a count of the number of groups to which user_id belongs.
     *
     * @param int $user_id to get groups for
     * @param string $filter to LIKE filter groups
     * @param bool $include_personal whether to include $user_id's personal
     *  group
     * @param array $allowed_statuses a list of group membership statuses
     *  that to restrict the returned list to
     * @return int number of groups of the filtered type for the user
     */
    public function countUserGroups($user_id, $filter = "",
        $include_personal = false, $allowed_statuses = [C\ACTIVE_STATUS,
        C\EDITOR_STATUS])
    {
        $db = $this->db;
        $users = [];
        $like = "";
        $param_array = [$user_id];
        if ($filter != "") {
            $like = "AND G.GROUP_NAME LIKE ?";
            $param_array[] = "%".$filter."%";
        }
        $status_where = "";
        $status_delim = " AND (";
        foreach ($allowed_statuses as $allowed_status) {
            $status_where .= $status_delim . "  UG.STATUS = $allowed_status";
            $status_delim = " OR ";
        }
        if (!empty($status_where)) {
            $status_where .= ") ";
        }
        $sql = "SELECT COUNT(DISTINCT G.GROUP_ID) AS NUM ".
            " FROM USER_GROUP UG, SOCIAL_GROUPS G".
            " WHERE UG.USER_ID = ? AND UG.GROUP_ID = G.GROUP_ID $status_where".
            " $like";
        $result = $db->execute($sql, $param_array);
        if ($result) {
            $row = $db->fetchArray($result);
        }
        $subtract_personal = ($include_personal) ? 0 : -1;
        return ($row['NUM'] ?? 0)  + $subtract_personal;
    }
    /**
     * Get the key of a private/encrypted group.
     *
     * @param int $group_id to get key for
     * @return string key of the group
     */
    public function getGroupKey($group_id)
    {
        $private_db = $this->private_db;
        $sql = "SELECT KEY_NAME FROM TYPE_KEYS WHERE TYPE_ID=?";
        $result = $private_db->execute($sql, [$group_id]);
        if ($result) {
            $row = $private_db->fetchArray($result);
        }
        return empty($row['KEY_NAME']) ? false :
            base64_decode($row['KEY_NAME']);
    }
    /**
     * To update the OWNER_ID of a group
     *
     * @param string $user_id the id of the user who becomes the admin of group
     * @param string $group_id  the group id  to transfer admin privileges
     */
    public function changeOwnerGroup($user_id, $group_id)
    {
        $db = $this->db;
        $sql = "UPDATE SOCIAL_GROUPS SET OWNER_ID=? WHERE GROUP_ID=?";
        $db->execute($sql, [$user_id, $group_id]);
    }
    /**
     * Add an allowed user to an existing group
     *
     * @param string $user_id the id of the user to add
     * @param string $group_id  the group id of the group to add the user to
     * @param int $status what should be the membership status of the added
     *      user. Should be one of ACTIVE_STATUS, INACTIVE_STATUS,
     *      SUSPENDED_STATUS, INVITED_STATUS
     */
    public function addUserGroup($user_id, $group_id, $status = C\ACTIVE_STATUS)
    {
        $join_date = time();
        $db = $this->db;
        $sql = "INSERT INTO USER_GROUP " .
            "(USER_ID, GROUP_ID, STATUS, JOIN_DATE) VALUES (?, ?, ?, ?)";
        $db->execute($sql, [$user_id, $group_id, $status, $join_date]);
    }
    /**
     * Checks if a user belongs to a group but is not the owner of that group
     * Such a user could be deleted from the group
     *
     * @param int $user_id which user to look up
     * @param int $group_id which group to look up for
     * @return bool where user is deletable
     */
    public function deletableUser($user_id, $group_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM USER_GROUP UG, SOCIAL_GROUPS G WHERE
            UG.USER_ID != G.OWNER_ID AND UG.USER_ID=? AND UG.GROUP_ID=?";
        $result = $db->execute($sql, [$user_id, $group_id]);
        if (!$row = $db->fetchArray($result)) {
            return false;
        }
        if ($row['NUM'] <= 0) {
            return false;
        }
        return true;
    }
    /**
     * Delete a user from a group by userid an groupid
     *
     * @param string $user_id  the userid of the user to delete
     * @param string $group_id  the group id of the group to delete
     */
    public function deleteUserGroup($user_id, $group_id)
    {
        $db = $this->db;
        $sql = "DELETE FROM USER_GROUP WHERE USER_ID=? AND GROUP_ID=?";
        $db->execute($sql, [$user_id, $group_id]);
    }
    /**
     * Returns the GROUP_FEED item with the given id
     *
     * @param int $item_id the item to get info about
     * @param bool $is_group_id whether $item_id is a group_id (true) or
     *     a row id from GROUP_ITEM (false)
     * @return array row from GROUP_FEED table
     */
    public function getGroupItem($item_id, $is_group_id = false)
    {
        $db = $this->db;
        if ($is_group_id) {
            $sql = "SELECT * FROM GROUP_ITEM WHERE GROUP_ID=? " .
            $db->limitOffset(1);
        } else {
            $sql = "SELECT * FROM GROUP_ITEM WHERE ID=? " . $db->limitOffset(1);
        }
        $result = $db->execute($sql, [$item_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!empty($row['GROUP_ID']) &&
            $this->isGroupEncrypted($row['GROUP_ID'])) {
            // Decrypt group's title and description
            $key = $this->getGroupKey($row['GROUP_ID']);
            $row['TITLE'] = $this->decrypt($row['TITLE'], $key);
            $row['DESCRIPTION'] = $this->decrypt($row['DESCRIPTION'], $key);
        }
        return $row;
    }
    /**
     * Returns the id of the relationship between wiki pages with the given name
     *
     * @param string $relationship_type the relationship type to get id about
     * @return array row from PAGE_RELATIONSHIP table or false (if no access
     *      or doesn't exist)
     */
    public function getRelationshipId($relationship_type)
    {
        $db = $this->db;
        $sql = "SELECT ID AS RELATIONSHIP_ID FROM " .
            "PAGE_RELATIONSHIP WHERE NAME=? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$relationship_type]);
        if (!$result) {
            return false;
        }
        if (!($row = $db->fetchArray($result))) {
            return false;
        }
        return $row['RELATIONSHIP_ID'];
    }
    /**
     * Returns an array of user information about users who have contributed
     * to a thread or own the group a thread belongs to
     *
     * @param int $thread_id the id of the thread that want users for
     * @param int $owner_id usually owner of group thread belongs to
     *    however could be id of any additional user want info for. If
     *    left a default value (-1) than only user's who have contributed to
     *    thread will be returned
     * @param int $exclude_id an id of a user to exclude from the array
     *      returned
     * @return array user information of users following the thread
     */
    public function getThreadFollowers($thread_id, $owner_id = -1,
        $exclude_id = -1)
    {
        $db = $this->db;
        $params = [$thread_id, $owner_id];
        $sql = "SELECT DISTINCT U.USER_ID AS USER_ID, " .
            "U.USER_NAME AS USER_NAME, U.EMAIL AS EMAIL ".
            "FROM GROUP_ITEM GI, USERS U ".
            "WHERE GI.PARENT_ID=? AND (GI.USER_ID=U.USER_ID OR U.USER_ID=?)";
        if ($exclude_id != -1) {
            $sql .= " AND U.USER_ID != ?";
            $params[] = $exclude_id;
        }
        $result = $db->execute($sql, $params);
        if (!$result) {
            return false;
        }
        $i = 0;
        $rows = [];
        while ($row = $db->fetchArray($result)) {
            $rows[] = $row;
        }
        return $rows;
    }
    /**
     * Returns the members who are following one discussion thread, that
     * is the people who asked to be mailed about it (by posting to it or
     * pressing its follow control). Used to build the recipient list when
     * a new reply is posted, so each row carries the address fields a mail
     * needs.
     *
     * @param int $thread_id the thread (its first post's id) to list
     *      followers for
     * @param int $exclude_id a user to leave out of the list, normally the
     *      person who just posted; pass -1 to exclude no one
     * @return array one row per follower with USER_ID, USER_NAME and EMAIL
     */
    public function getThreadSubscribers($thread_id, $exclude_id = -1)
    {
        $db = $this->db;
        $params = [$thread_id];
        $sql = "SELECT DISTINCT U.USER_ID AS USER_ID, " .
            "U.USER_NAME AS USER_NAME, U.EMAIL AS EMAIL " .
            "FROM THREAD_FOLLOW TF, USERS U " .
            "WHERE TF.THREAD_ID = ? AND TF.USER_ID = U.USER_ID";
        if ($exclude_id != -1) {
            $sql .= " AND U.USER_ID != ?";
            $params[] = $exclude_id;
        }
        $result = $db->execute($sql, $params);
        if (!$result) {
            return false;
        }
        $rows = [];
        while ($row = $db->fetchArray($result)) {
            $rows[] = $row;
        }
        return $rows;
    }
    /**
     * Given a list of thread ids, says which of them a member is
     * following. The feed uses this so each thread row can show whether
     * its follow control is on or off, looked up in one query rather than
     * one lookup per row.
     *
     * @param int $user_id the member to check
     * @param array $thread_ids the thread ids shown on the feed page
     * @return array the subset of $thread_ids the member follows, given
     *      as a map from thread id to true for an easy isset() test
     */
    public function getFollowedThreads($user_id, $thread_ids)
    {
        $followed = [];
        if (empty($thread_ids)) {
            return $followed;
        }
        $db = $this->db;
        $marks = implode(", ", array_fill(0, count($thread_ids), "?"));
        $sql = "SELECT THREAD_ID FROM THREAD_FOLLOW " .
            "WHERE USER_ID = ? AND THREAD_ID IN ($marks)";
        $params = array_merge([$user_id], $thread_ids);
        $result = $db->execute($sql, $params);
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                $followed[$row['THREAD_ID']] = true;
            }
        }
        return $followed;
    }
    /**
     * Creates a new group item
     *
     * @param int $parent_id thread id to use for the item
     * @param int $group_id what group the item should be added to
     * @param int $user_id of user making the post
     * @param string $title title of the group feed item
     * @param string $description actual content of the post
     * @param int $type flag saying what kind of group item this is. One of
     *      STANDARD_GROUP_ITEM, WIKI_GROUP_ITEM (used for threads discussing
     *      a wiki page)
     * @param int $post_time timstamp for when this group item was created
     *      default to the current time
     * @param string $url a url associated with this group item (mainly for
     *      search group, otherwise use empty string)
     * @param int $edit_time timestamp for when this group item was last
     *      edited
     * @param int $ups number of times the item has been upvoted
     * @param int $downs number of times the item has been down voted
     * @param int $flag number of times the item has been flagged
     * @param int $input_timestamp optional UNIX timestamp to use for impression
     *    tracking; if set to -1 or omitted, the current time will be used
     * @return int $id of item added
     */
    public function addGroupItem($parent_id, $group_id, $user_id, $title,
        $description, $type= C\STANDARD_GROUP_ITEM, $post_time = 0, $url = "",
        $edit_time = 0, $ups = 0, $downs = 0, $flag = 0, $input_timestamp = -1)
    {
        $db = $this->db;
        if ($post_time == 0) {
            $post_time = time();
        }
        if ($edit_time <= $post_time) {
            $edit_time = $post_time;
        }
        if ($this->isGroupEncrypted($group_id)) {
            $key = $this->getGroupKey($group_id);
            $title = $this->encrypt($title, $key);
            $description = $this->encrypt($description, $key);
        }
        $sql = "INSERT INTO GROUP_ITEM (PARENT_ID, GROUP_ID, USER_ID, URL,
            TITLE, DESCRIPTION, PUBDATE, EDIT_DATE, TYPE, UPS, DOWNS,
            FLAG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $db->execute($sql, [$parent_id, $group_id, $user_id, $url, $title,
            $description, $post_time, $edit_time, $type, $ups, $downs, $flag]);
        $id = $db->insertID("GROUP_ITEM");
        if ($parent_id == 0) {
            $sql = "UPDATE GROUP_ITEM SET PARENT_ID=? WHERE ID=?";
            $db->execute($sql, [$id, $id]);
            ImpressionModel::initWithDb($user_id, $id, C\THREAD_IMPRESSION,
                $db, $input_timestamp);
        }
        return $id;
    }
    /**
     * Adds a flagged group item to the moderation group
     *
     * @param int $parent_id thread id to use for the item
     * @param int $group_id what group the item should be added to
     * @param int $user_id of user making the post
     * @param string $title title of the group feed item
     * @param string $description actual content of the post
     * @param int $flag flag value to record on the inserted GROUP_ITEM
     *      row (e.g. NEEDS_MODERATION_FLAG)
     * @param int $parent_item_id row id of the original item that was
     *      flagged; the new moderation post points back to it
     * @param int $parent_group_id group id the flagged item lives in;
     *      used to fetch the encryption key when re-decrypting before
     *      reinsertion
     * @param int $type flag saying what kind of group item this is. One of
     *      STANDARD_GROUP_ITEM, WIKI_GROUP_ITEM (used for threads discussing
     *      a wiki page)
     * @param int $post_time timstamp for when this group item was created
     *      default to the current time
     * @param string $url a url associated with this group item (mainly for
     *      search group)
     * @return int $id of item added
     */
    public function addGroupItemModeration($parent_id, $group_id, $user_id,
        $title, $description, $flag, $parent_item_id, $parent_group_id,
        $type= C\STANDARD_GROUP_ITEM, $post_time = 0, $url = "")
    {
        $db = $this->db;
        if ($post_time == 0) {
            $post_time = time();
        }
        if($this->isGroupEncrypted($parent_group_id)) {
            $key = $this->getGroupKey($parent_group_id);
            $title = $this->decrypt($title, $key);
            $description = $this->decrypt($description, $key);
        }
        $sql = "INSERT INTO GROUP_ITEM (PARENT_ID, GROUP_ID, USER_ID, URL,
             TITLE, DESCRIPTION, PUBDATE, EDIT_DATE, FLAG, PARENT_ITEM_ID, TYPE)
             VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $db->execute($sql, [$parent_id, $group_id, $user_id, $url, $title,
             $description, $post_time, $post_time, $flag, $parent_item_id,
             $type]);
        $id = $db->insertID("GROUP_ITEM");
        $update_sql = "UPDATE GROUP_ITEM SET PARENT_ID = ? WHERE ID = ?";
        $db->execute($update_sql, [$id, $id]);
        return $id;
    }
    /**
     * Encrypts  data based on provided key.
     *
     * @param string $data what data to encrypt
     * @param string $key what key to use to encrypt
     * @param string $cipher_method a cipher encrypt/decrypt method
     *      supported by OpenSSL
     * @return string $out_data the encrypted string
     */
    public function encrypt($data, $key, $cipher_method = 'aes-256-cbc')
    {
        $iv = openssl_random_pseudo_bytes(
            openssl_cipher_iv_length($cipher_method ));
        $encrypted = openssl_encrypt($data, $cipher_method, $key, 0, $iv);
        /* append value of $IV to the encrypted data with a separator
          so this can be used during decryption. Use base 64 to make
          friendly to a wide variety of DBMSs such as postgres.
         */
        return base64_encode($iv .  str_repeat("0", 10)  . $encrypted);
    }
    /**
     * Decrypts data based on provided key.
     *
     * @param string $data what data to decrypt
     * @param string $key what key to use to decrypt
     * @param string $cipher_method a cipher encrypt/decrypt method
     *      supported by OpenSSL
     * @return string $out_data the decrypted string
     */
    public function decrypt($data, $key, $cipher_method = 'aes-256-cbc')
    {
        $out_data = "";
        $data = base64_decode($data);
        $pos = strpos($data, str_repeat("0", 10));
        if ($pos !== false) {
            $data_parts = explode(str_repeat("0", 10), $data);
            $out_data = openssl_decrypt($data_parts[1], $cipher_method,
                $key, 0, $data_parts[0]);
        }
        return $out_data;
    }
    /**
     * Encrypts flag value based on provided key.
     *
     * @param string $data what data to encrypt
     * @param string $key what key to use to encrypt
     * @param string $cipher_method a cipher encrypt/decrypt method
     *      supported by OpenSSL
     * @return string $out_data the encrypted string
     */
    public function encryptFlag($data, $key, $cipher_method = 'AES-256-ECB')
    {
        $block_length = 16;
        $padding = $block_length - (strlen($data) % $block_length);
        $data = $data . str_repeat(chr($padding), $padding);
        $key = pack('H*', $key);
        $cipher_method = strtolower($cipher_method);
        $encrypted = openssl_encrypt($data, $cipher_method, $key,
            OPENSSL_RAW_DATA|OPENSSL_NO_PADDING);
        return base64_encode($encrypted);
    }
    /**
     * Decrypts flag value based on provided key.
     *
     * @param string $encrypted_data base64-encoded ciphertext to decrypt
     * @param string $key what key to use to decrypt
     * @param string $cipher_method a cipher encrypt/decrypt method
     *      supported by OpenSSL
     * @return string $out_data the decrypted string
     */
    public function decryptFlag($encrypted_data, $key,
    $cipher_method = 'AES-256-ECB')
    {
        $encrypted_data = base64_decode($encrypted_data);
        $key = pack('H*', $key);
        $cipher_method = strtolower($cipher_method);
        $out_data = openssl_decrypt($encrypted_data, $cipher_method, $key,
            OPENSSL_RAW_DATA|OPENSSL_NO_PADDING);
        $padding = ord(substr($out_data, -1));
        $out_data = substr($out_data, 0, -$padding);
        return $out_data;
    }
    /**
     * Updates a group feed item's title and description. This assumes
     * the given item already exists.
     *
     * @param int $id which item to change
     * @param string $title the new title
     * @param string $description the new description
     */
    public function updateGroupItem($id, $title, $description)
    {
        $db = $this->db;
        $edit_date = time();
        $group_item = $this->getGroupItem($id);
        if ($this->isGroupEncrypted($group_item['GROUP_ID'])) {
            $key = $this->getGroupKey($group_item['GROUP_ID']);
            $title = $this->encrypt($title, $key);
            $description = $this->encrypt($description, $key);
        }
        $sql = "UPDATE GROUP_ITEM SET TITLE=?, DESCRIPTION=?,
            EDIT_DATE=? WHERE ID=?";
        $db->execute($sql, [$title, $description, $edit_date, $id]);
    }
    /**
     * Removes a group feed item from the GROUP_ITEM table.
     *
     * @param int $post_id of item to remove
     * @param int $user_id the id of the person trying to perform the
     *     removal. If not root, or the original creator of the item,
     *     the item won't be removed
     * @return int number of GROUP_ITEM rows actually deleted (0 when
     *     the user lacked permission and the row was untouched)
     */
    public function deleteGroupItem($post_id, $user_id)
    {
        $db = $this->db;
        $params = [$post_id];
        if ($user_id == C\ROOT_ID) {
            $and_where = "";
        } else {
            $and_where = " AND USER_ID=?";
            $params[] = $user_id;
        }
        $sql = "DELETE FROM GROUP_ITEM WHERE ID=? $and_where";
        if ($result = $db->execute($sql, $params)) {
            $affected_rows = $db->affectedRows();
            ImpressionModel::deleteWithDb($user_id, $post_id,
                C\THREAD_IMPRESSION, $db);
            ImpressionModel::deleteWithDb(C\PUBLIC_USER_ID, $post_id,
                C\THREAD_IMPRESSION, $db);
        } else {
            $affected_rows = $db->affectedRows();
        }
        return $affected_rows;
    }
    /**
     * Flags a group feed item in the GROUP_ITEM table.
     *
     * @param int $post_id of item to be flagged
     * @param int $user_id the id of the person trying to flag the item
     * If user does not belong to the group, the item won't be flagged
     * @return int|bool number of rows updated by the flag insert, or
     *     false when the threshold has already been reached so the
     *     additional flag is rejected
     */
    public function flagGroupItem($post_id, $user_id)
    {
        $db = $this->db;
        $sql = "SELECT * FROM GROUP_ITEM WHERE ID = ?";
        $stmt = $db->execute($sql, [$post_id]);
        $result = $stmt->fetch();
        $group_id = $result['GROUP_ID'];
        $flag_count = $this->getFlagCount($post_id);
        $threshold = $this->getThresholdValue($group_id);
        if ($this->isGroupEncrypted($group_id)) {
            $key = $this->getGroupKey($group_id);
            if (is_null($flag_count)) {
                $flag_count = $this->encryptFlag(strval($flag_count), $key);
            }
            if ($flag_count === $threshold) {
                $flag_count_decrypt = $this->decryptFlag(
                    base64_decode($flag_count), $key);
                if (C\p('USE_MODERATION')) {
                    $this->addGroupItemModeration(0,
                        C\MODERATION_GROUP_ID, $result['USER_ID'],
                        $result['TITLE'], $result['DESCRIPTION'],
                        C\MODERATION_FLAGGED, $post_id, $group_id,
                        C\STANDARD_GROUP_ITEM, 0, "");
                }
            } else if ($flag_count === $this->encryptFlag($threshold, $key)) {
                return false;
            }
            $flag_count = $this->encryptFlag(strval($flag_count), $key);
        } else {
            $flag_count++;
            if($flag_count > C\p('MODERATION_FLAG_THRESHOLD')) {
                return false;
            }
            if($flag_count === $threshold && C\p('USE_MODERATION')) {
                $this->addGroupItemModeration(0, C\MODERATION_GROUP_ID,
                    $result['USER_ID'], $result['TITLE'],$result['DESCRIPTION'],
                    C\MODERATION_FLAGGED, $post_id, $group_id,
                    C\STANDARD_GROUP_ITEM, 0, "");
            }
        }
        $sql = "INSERT INTO GROUP_ITEM_FLAG VALUES (?, ?)";
        $this->db->execute($sql, [$user_id, $post_id]);
        $update_sql = "UPDATE GROUP_ITEM SET FLAG = ? WHERE ID = ?
            AND GROUP_ID IN (SELECT GROUP_ID FROM USER_GROUP
            WHERE USER_ID = ?)";
        $db->execute($update_sql, [$flag_count, $post_id, $user_id]);
        $affected_rows = $db->affectedRows();
        return  $affected_rows;
    }
    /**
     * Retrieves flag column value from the database
     *
     * @param int $post_id of thread whose flag value needs to be fetched
     * @return mixed the FLAG column value (int for plaintext groups,
     *     base64 ciphertext for encrypted groups), false on query
     *     failure or no row, or null when the row exists with NULL flag
     */
    public function getFlagCount($post_id)
    {
        $db = $this->db;
        $sql = "SELECT FLAG FROM GROUP_ITEM WHERE ID = ?";
        $stmt = $db->execute($sql, [$post_id]);
        if (!$stmt) {
            return false;
        }
        if (!($row = $db->fetchArray($stmt))) {
            return false;
        }
        return $row['FLAG'];
    }
    /**
     * Checks if post is already flagged
     *
     * @param int $user_id user whose flag is being checked
     * @param int $post_id to check if the thread is already flagged
     * @return bool true if a row in GROUP_ITEM_FLAG exists for the
     *     (user, post) pair; false on query failure or no match
     */
    public function alreadyFlagged($user_id, $post_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM_FLAG WHERE USER_ID = ?
                AND ITEM_ID = ?";
        $result = $db->execute($sql, [$user_id, $post_id]);
        if (!$result) {
             return false;
        }
        $row = $db->fetchArray($result);
        if (!$row || !isset($row['NUM'])) {
            return false;
        }
        return ($row['NUM'] > 0);
    }
    /**
     * Get the group id of the parent thread
     *
     * @param int $post_id denotes the thread
     * @return mixed GROUP_ID of the parent thread, or false on query
     *     failure / no parent row
     */
    public function getParentGroupId($post_id)
    {
       $db = $this->db;
       $sql = "SELECT GROUP_ID FROM GROUP_ITEM WHERE ID =
               (SELECT PARENT_ITEM_ID FROM GROUP_ITEM WHERE ID = ?)";
       $stmt = $db->execute($sql, [$post_id]);
        if (!$stmt) {
            return false;
        }
        if (!($row = $db->fetchArray($stmt))) {
            return false;
        }
        return $row['GROUP_ID'];
    }
    /**
     * Get the minimum number of times a post has to be flagged to be reviewed
     * by the moderator
     *
     * @param int $group_id to get threshold value for the given group
     * @return mixed integer MODERATION_FLAG_THRESHOLD for plaintext
     *     groups, or its encrypted-flag equivalent for encrypted groups
     */
    public function getThresholdValue($group_id)
    {
       if (!$this->isGroupEncrypted($group_id)) {
           return C\p('MODERATION_FLAG_THRESHOLD');
       }
       $key = $this->getGroupKey($group_id);
       $threshold_value = 0;
       for ($i = C\p('MODERATION_FLAG_THRESHOLD'); $i > 0; $i--) {
         $threshold_value = $this->encryptFlag(strval($threshold_value), $key);
       }
       return $threshold_value;
    }
    /**
     * Get the id of the parent thread of a post
     *
     * @param int $post_id denotes the thread
     * @return mixed ID of the grand-parent GROUP_ITEM row, or false on
     *     query failure / missing parent
     */
    public function getParentPostById($post_id)
    {
        $db = $this->db;
        $sql = "SELECT ID FROM GROUP_ITEM WHERE ID =
                (SELECT PARENT_ITEM_ID FROM GROUP_ITEM WHERE ID = ?)";
        $stmt = $db->execute($sql, [$post_id]);
        if (!$stmt) {
            return false;
        }
        if (!($row = $db->fetchArray($stmt))) {
            return false;
        }
        return $row['ID'];
    }
    /**
     * Get parent_id of the parent thread of a given post
     *
     * @param int $post_id denotes the post
     * @return mixed PARENT_ID of the parent GROUP_ITEM row, or false on
     *     query failure / missing parent
     */
    public function getParentIdOfParentPost($post_id)
    {
        $db = $this->db;
        $sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE ID =
                (SELECT PARENT_ITEM_ID FROM GROUP_ITEM WHERE ID = ?)";
        $stmt = $db->execute($sql, [$post_id]);
        if (!$stmt) {
            return false;
        }
        if (!($row = $db->fetchArray($stmt))) {
            return false;
        }
        return $row['PARENT_ID'];

    }
    /**
     * Moderator approves a flagged post
     *
     * @param int $post_id to fetch the flagged post
     * @return int number of GROUP_ITEM rows updated by the
     *     MODERATION_CLEARED reset
     */
    public function approveFlaggedPost($post_id)
    {
         //get parent_item_id and go to the original post
         $item_id= $this->getParentPostById($post_id);
         //reset flag column value to zero
         $db = $this->db;
         $update_sql = "UPDATE GROUP_ITEM SET FLAG = 0 WHERE ID = ?";
         $db->execute($update_sql, [$item_id]);
         $reset_sql = "UPDATE GROUP_ITEM SET FLAG = " . C\MODERATION_CLEARED .
            " WHERE ID = ?";
         $db->execute($reset_sql, [$post_id]);
         $affected_rows = $db->affectedRows();
         return  $affected_rows;
    }
    /**
     * Deletes a flagged post from group items
     *
     * @param int $post_id to fetch the flagged post
     * @param string $message string to show for the removed post
     * @return int number of GROUP_ITEM rows affected by the
     *     MODERATION_REMOVED state change
     */
    public function deleteFlaggedPost($post_id, $message)
    {
         $parent_group_id = $this->getParentGroupId($post_id);
         if($this->isGroupEncrypted($parent_group_id)) {
            $key = $this->getGroupKey($parent_group_id);
            $description = $this->encrypt($description, $key);
         }
         $item_id = $this->getParentPostById($post_id);
         $db = $this->db;
         $update_sql = "UPDATE GROUP_ITEM SET DESCRIPTION = ? WHERE ID = ?";
         $db->execute($update_sql, [$message, $item_id]);
         $reset_sql = "UPDATE GROUP_ITEM SET FLAG = ".  C\MODERATION_REMOVED .
            " WHERE ID = ?";
         $db->execute($reset_sql, [$post_id]);
         $affected_rows = $db->affectedRows();
         return  $affected_rows;
    }
    /**
     * Gets the group feed items visible to a user with $user_id
     * and which match the supplied search criteria found in $search_array,
     * starting from the $limit'th matching item to the $limit+$num item.
     *
     * @param int $limit starting offset group item to display
     * @param int $num number of items from offset to display
     * @param array $search_array each element of this is a quadruple
     *     name of a field, what comparison to perform, a value to check,
     *     and an order (ascending/descending) to sort by
     * @param int $user_id who is making this request to determine which
     * @param int $for_group if this value is set it is a assumed
     *     that group_items are being returned for only one group
     *     and that they should be grouped by thread
     * @return array elements of which represent one group feed item
     */
    public function getGroupItems($limit = 0, $num = 100, $search_array = [],
        $user_id = C\ROOT_ID, $for_group = -1)
    {
        $db = $this->db;
        $limit = $db->limitOffset($limit, $num);
        $any_fields = ["access", "register"];
        $is_thread = false;
        foreach ($search_array as $search_item) {
            if ($search_item[0] == 'parent_id') {
                if ($search_item[2] > 0) {
                    $is_thread = true;
                }
                break;
            }
        }
        list($where, $order_by) =
            $this->searchArrayToWhereOrderClauses($search_array, $any_fields);
        $where = str_replace("O.USER_ID", "P.USER_ID", $where); //hacky
        $add_where = " WHERE ";
        if (!empty($where)) {
            $add_where = " AND ";
            if (str_ends_with(rtrim($where), "AND")) {
                $add_where = " ";
            }
        }
        $user_id = $db->escapeString($user_id);
        if ($for_group > 0 || $for_group == -2) { //-2 is just_thread case)
            $non_public_where = " (UG.USER_ID='$user_id' OR ".
                " G.REGISTER_TYPE IN ('" . C\PUBLIC_JOIN . "','".
                C\PUBLIC_BROWSE_REQUEST_JOIN . "') ) AND ";
            if (!$is_thread) {
                $non_public_where .=
                 "TYPE = " . C\STANDARD_GROUP_ITEM. " AND ";
            }
        } else {
            $non_public_where = " UG.USER_ID='$user_id' AND ";
        }
        $non_public_status = ($user_id != C\PUBLIC_GROUP_ID) ?
            " UG.STATUS='" . C\ACTIVE_STATUS . "' AND " : "";
        $where .= $add_where . $non_public_where .
            "GI.GROUP_ID=G.GROUP_ID AND GI.GROUP_ID=UG.GROUP_ID AND ((
            $non_public_status
            G.MEMBER_ACCESS IN ('" . C\GROUP_READ  ."','".C\GROUP_READ_COMMENT.
            "','".C\GROUP_READ_WRITE."', '". C\GROUP_READ_WIKI ."')) OR
            (G.OWNER_ID = UG.USER_ID OR UG.USER_ID = '" . C\ROOT_ID . "'))";
        $where .= $this->excludeGitIssueThreadsClause("GI");
        if ($for_group >= 0) {
            $outer_where = "";
            if (preg_match("/P\.USER_ID=[\'\"]\d+[\'\"]/", $where, $matches)) {
                $outer_where = " AND " . $matches[0];
                $where = preg_replace("/P\.USER_ID=[\'\"]\d+[\'\"]/", "",
                    $where);
                //above could result in two ANDs
                $where = preg_replace('/AND\s+AND/ui', "AND", $where);
            }
            $group_by = " GROUP BY GI.PARENT_ID";
            $order_by = " ORDER BY E.PUBDATE DESC ";
            $select = "SELECT E.*, I.TITLE AS TITLE,
                I.DESCRIPTION AS DESCRIPTION, I.FLAG AS FLAG,
                I.USER_ID AS USER_ID, II.USER_ID AS LAST_POSTER_ID,
                U.USER_NAME AS USER_NAME, P.USER_NAME AS LAST_POSTER,
                COALESCE(IIS.NUM_VIEWS, 0) AS NUM_VIEWS,
                COALESCE(IIS.FUZZY_NUM_VIEWS, 0) AS FUZZY_NUM_VIEWS,
                COALESCE(IIS.TMP_NUM_VIEWS, 0) AS TMP_NUM_VIEWS";
            $sub_select = "SELECT DISTINCT MIN(GI.ID) AS ID,
                MAX(GI.ID) AS LAST_ID,
                COUNT(DISTINCT GI.ID) AS NUM_POSTS, GI.PARENT_ID AS PARENT_ID,
                MIN(GI.GROUP_ID) AS GROUP_ID, MAX(GI.PUBDATE) AS PUBDATE,
                MIN(G.OWNER_ID) AS OWNER_ID,
                MIN(G.MEMBER_ACCESS) AS MEMBER_ACCESS,
                MIN(G.GROUP_NAME) AS GROUP_NAME,
                MIN(GI.PUBDATE) AS RECENT_DATE,
                MIN(GI.TYPE) AS TYPE";
            $sub_sql = "$sub_select
                FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USER_GROUP UG
                $where $group_by";
            $sql = "$select FROM ($sub_sql) E JOIN GROUP_ITEM I
                    ON E.ID = I.ID
                JOIN GROUP_ITEM II ON E.LAST_ID = II.ID
                JOIN USERS U ON I.USER_ID = U.USER_ID
                JOIN USERS P ON II.USER_ID = P.USER_ID
                LEFT OUTER JOIN ITEM_IMPRESSION_SUMMARY IIS ON
                    IIS.ITEM_ID = E.PARENT_ID AND IIS.ITEM_TYPE=" .
                C\THREAD_IMPRESSION . " AND IIS.USER_ID=" .
                C\PUBLIC_USER_ID . " AND
                IIS.ITEM_ID = E.PARENT_ID AND
                IIS.UPDATE_PERIOD=" . C\FOREVER .
                " $outer_where $order_by $limit";
        } else {
            $where .= " AND P.USER_ID = GI.USER_ID ";
            $select = "SELECT DISTINCT GI.ID AS ID,
                GI.PARENT_ID AS PARENT_ID, GI.GROUP_ID AS GROUP_ID,
                GI.TITLE AS TITLE, GI.DESCRIPTION AS DESCRIPTION,
                GI.FLAG AS FLAG, GI.PUBDATE AS PUBDATE,
                GI.EDIT_DATE AS EDIT_DATE, G.OWNER_ID AS OWNER_ID,
                G.MEMBER_ACCESS AS MEMBER_ACCESS,
                G.GROUP_NAME AS GROUP_NAME, P.USER_NAME AS USER_NAME,
                P.USER_ID AS USER_ID, GI.TYPE AS TYPE, GI.UPS AS UPS,
                GI.DOWNS AS DOWNS, G.VOTE_ACCESS AS VOTE_ACCESS ";
            $sql = "$select
                FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USER_GROUP UG, USERS P
                $where $order_by $limit";
        }
        $result = $db->execute($sql);
        $i = 0;
        $read_only = ($user_id == C\PUBLIC_GROUP_ID);
        if ($read_only) {
            while ($groups[$i] = $db->fetchArray($result)) {
                $groups[$i]["MEMBER_ACCESS"] = C\GROUP_READ;
                $i++;
            }
        } else {
            while ($groups[$i] = $db->fetchArray($result)) {
                $i++;
            }
        }
        unset($groups[$i]); //last one will be null
        $i = 0;
        foreach ($groups as $group_key => $group_value) {
            if ($this->isGroupEncrypted($group_value['GROUP_ID'])) {
                $keys = $this->getGroupKey($group_value['GROUP_ID']);
                $groups[$i]['TITLE'] = $this->decrypt(
                    $group_value['TITLE'], $keys);
                $groups[$i]['DESCRIPTION'] = $this->decrypt(
                    $group_value['DESCRIPTION'], $keys);
            }
            $i++;
        }
        return $groups;
    }
    /**
     * Gets the number of group feed items visible to a user with $user_id
     * and which match the supplied search criteria found in $search_array
     *
     * @param array $search_array each element of this is a quadruple
     *     name of a field, what comparison to perform, a value to check,
     *     and an order (ascending/descending) to sort by
     * @param int $user_id who is making this request to determine which
     * @param int $for_group if this value is set it is a assumed
     *     that group_items are being returned for only one group
     *     and that the count desired is over the number of threads in that
     *     group
     * @return int number of items matching the search criteria for the
     *     given user_id
     */
    public function getGroupItemCount($search_array = [], $user_id = C\ROOT_ID,
        $for_group = -1)
    {
        $db = $this->db;
        $any_fields = ["access", "register"];
        $is_thread = false;
        foreach ($search_array as $search_item) {
            if ($search_item[0] == 'parent_id') {
                if ($search_item[2] > 0) {
                    $is_thread = true;
                }
                break;
            }
        }
        list($where, $order_by) =
            $this->searchArrayToWhereOrderClauses($search_array, $any_fields);
        $add_where = " WHERE ";
        if ($where != "") {
            $add_where = " AND ";
        }
        $user_id = $db->escapeString($user_id);
        if ($for_group > 0 || $for_group == -2) { //-2 is just_thread case
            $non_public_where = " (UG.USER_ID='$user_id' OR ".
                " G.REGISTER_TYPE IN ('".C\PUBLIC_JOIN."','".
                C\PUBLIC_BROWSE_REQUEST_JOIN."') ) AND ";
            if (!$is_thread) {
                $non_public_where .=
                 "TYPE = " . C\STANDARD_GROUP_ITEM. " AND ";
            }
        } else {
            $non_public_where = " UG.USER_ID='$user_id' AND ";
        }
        $non_public_status = ($user_id != C\PUBLIC_GROUP_ID) ?
            " UG.STATUS='" . C\ACTIVE_STATUS."' AND " : "";
        $where .= $add_where. $non_public_where .
            "GI.USER_ID=O.USER_ID AND
            GI.GROUP_ID=G.GROUP_ID AND GI.GROUP_ID=UG.GROUP_ID AND ((
            $non_public_status
            G.MEMBER_ACCESS IN ('".C\GROUP_READ."','".C\GROUP_READ_COMMENT.
            "','".C\GROUP_READ_WRITE."', '" . C\GROUP_READ_WIKI . "')) OR
            (G.OWNER_ID = UG.USER_ID OR UG.USER_ID = '".C\ROOT_ID."'))";
        if ($for_group >= 0) {
            $count_col = " COUNT(DISTINCT GI.PARENT_ID) ";
        } else {
            $count_col = " COUNT(DISTINCT GI.ID) ";
        }
        $sql = "SELECT $count_col AS NUM FROM GROUP_ITEM GI, SOCIAL_GROUPS G,
            USER_GROUP UG, USERS O $where";
        $result = $db->execute($sql);
        $row = $db->fetchArray($result);
        return $row['NUM'] ?? false;
    }
    /**
     * Returns the most recent post posted to a group
     * @param int $group_id id of the group to get the most recent post for
     * @return array associate array of post details
     */
    public function getMostRecentGroupPost($group_id)
    {
        $db = $this->db;
        $sql = "SELECT MAX(GI.PUBDATE) AS PUBDATE
            FROM GROUP_ITEM GI
            WHERE GI.GROUP_ID = ?" .
            $this->excludeGitIssueThreadsClause("GI");
        $result = $db->execute($sql, [$group_id]);
        if (empty($result)) {
            return "";
        }
        $row = $db->fetchArray($result);
        if (empty($row) || empty($row["PUBDATE"])) {
            return "";
        }
        $sql = "SELECT DISTINCT GI.ID AS ID, GI.PARENT_ID AS PARENT_ID,
            GI.GROUP_ID AS GROUP_ID, GI.TITLE AS TITLE,
            GI.DESCRIPTION AS DESCRIPTION, GI.PUBDATE AS PUBDATE,
            GI.EDIT_DATE AS EDIT_DATE, G.OWNER_ID AS OWNER_ID,
            G.MEMBER_ACCESS AS MEMBER_ACCESS, G.GROUP_NAME AS GROUP_NAME,
            P.USER_NAME AS USER_NAME, P.USER_ID AS USER_ID, GI.TYPE AS TYPE,
            GI.UPS AS UPS, GI.DOWNS AS DOWNS, G.VOTE_ACCESS AS VOTE_ACCESS
            FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USERS P
            WHERE GI.GROUP_ID = ? AND GI.GROUP_ID=G.GROUP_ID AND
            GI.USER_ID = P.USER_ID AND GI.PUBDATE = ?" .
            $this->excludeGitIssueThreadsClause("GI") . " " .
            $db->limitOffset(0, 1);
        $result = $db->execute($sql, [$group_id, $row['PUBDATE']]);
        if (empty($result)) {
            return "";
        }
        $row = $db->fetchArray($result);
        if (!empty($group_id) &&
            $this->isGroupEncrypted($group_id)) {
            // Decrypt group's title and description
            $key = $this->getGroupKey($group_id);
            $row['TITLE'] = $this->decrypt($row['TITLE'], $key);
            $row['DESCRIPTION'] = $this->decrypt($row['DESCRIPTION'], $key);
        }
        return $row;
    }
    /**
     * Returns the number of distinct threads in a group's feed
     * @param int $group_id id of the group to get thread count for
     * @return int number of threads
     */
    public function getGroupThreadCount($group_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(GI.PARENT_ID) AS NUM
            FROM GROUP_ITEM GI
            WHERE GI.GROUP_ID = ? AND GI.ID = GI.PARENT_ID" .
            $this->excludeGitIssueThreadsClause("GI");
        $result = $db->execute($sql, [$group_id]);
        if (!$result) {
            return 0;
        }
        $row = $db->fetchArray($result);
        return $row['NUM'] ?? 0;
    }
    /**
     * Returns the number of posts to groups that a user belongs to since
     * a timestamp
     *
     * @param int $user_id id of the user to get posts for
     * @param int $timestamp only post with value pubdate greater than
     *   this will be counted
     * @return int number of posts
     */
    public function getUserPostCount($user_id, $timestamp = 0)
    {
        $db = $this->db;
        $subselect = "(SELECT GROUP_ID " .
            "FROM USER_GROUP WHERE USER_ID = ? AND STATUS = ".
            C\ACTIVE_STATUS . ")";
        $sql = "SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI ".
            "WHERE GI.GROUP_ID IN $subselect AND GI.TITLE NOT LIKE ".
            "? AND GI.PUBDATE > ?";
        if (empty($user_id)) {
            return 0;
        }
        $result = $db->execute($sql, [$user_id, "%$user_id%", $timestamp]);
        if (!$result) {
            return 0;
        }
        $row = $db->fetchArray($result);
        return $row['NUM'] ?? 0;
    }
    /**
     * Returns the number of posts to a group
     * @param int $group_id id of the group to get post count for
     * @param int $timestamp only post with value pubdate greater or
     *  EDIT_DATE than this will be counted
     * @return int number of posts
     */
    public function getGroupPostCount($group_id, $timestamp = 0)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI
            WHERE GI.GROUP_ID = ? AND GI.PUBDATE > ?" .
            $this->excludeGitIssueThreadsClause("GI");
        $result = $db->execute($sql, [$group_id, $timestamp]);
        if (!$result) {
            return 0;
        }
        $row = $db->fetchArray($result);
        return $row['NUM'] ?? 0;
    }
    /**
     * Gives a SQL fragment that leaves out the discussion threads belonging
     * to git issue companion pages, whose titles carry the reserved issue
     * separator, so those issues are not counted or shown as ordinary group
     * posts in feeds, in a group's post and thread counts, or as a group's
     * most recent post. The check that the thread is set keeps the NOT IN
     * test from wrongly matching when a page has no thread.
     *
     * @param string $alias the table alias used for GROUP_ITEM in the query
     * @return string the fragment to add to a WHERE clause
     */
    private function excludeGitIssueThreadsClause($alias)
    {
        return " AND " . $alias . ".PARENT_ID NOT IN (SELECT DISCUSS_THREAD" .
            " FROM GROUP_PAGE WHERE TITLE LIKE '%" . C\GIT_ISSUE_SEPARATOR .
            "%' AND DISCUSS_THREAD IS NOT NULL)";
    }
    /**
     * Get the id of the clipboard thread used for moving around group items for
     * a given user.
     *
     * @param int $user_id id of user to get the clipboard thread for
     * @return int id of thread
     */
    public function getFeedClipboardId($user_id)
    {
        if (($clip_group_id = $this->getPersonalGroupId($user_id)) < 0) {
            return false;
        }
        $clip_title = C\PERSONAL_GROUP_PREFIX . "_clipboard_" . $user_id;
        $db = $this->db;
        $sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND " .
            "TITLE=? " . $db->limitOffset(1);
        $result = $db->execute($sql, [$clip_group_id, $clip_title]);
        if (!empty($result)) {
            $row = $db->fetchArray($result);
            if (!empty($row)) {
                return $row["PARENT_ID"];
            }
        }
        return $this->addGroupItem(0, $clip_group_id, $user_id, $clip_title,
            "start_feed_clip");
    }
    /**
     * Messages use the same storage mechanism as group posts, so need a title,
     * The title used is always computed as the `-` concatenation of the sorted
     * id's of the participants in the message session. This method computes
     * such a title string from an array of message participants.
     * @param array $thread_follower_ids message chat participants
     * @return string appropriate title of chat thread.
     */
    public function getMessagesThreadTitle($thread_follower_ids)
    {
        sort($thread_follower_ids);
        return implode("-", $thread_follower_ids);
    }
    /**
     * Returns the number of posts to a thread
     * @param int $thread_id id of the thread to get post count for
     * @param int $timestamp only post with value pubdate greater or
     *  EDIT_DATE than this will be counted
     * @return int number of posts
     */
    public function getThreadPostCount($thread_id, $timestamp = 0)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI
            WHERE GI.PARENT_ID = ? AND GI.PUBDATE > ?";
        $result = $db->execute($sql, [$thread_id, $timestamp]);
        if (!$result) {
            return 0;
        }
        $row = $db->fetchArray($result);
        return $row['NUM'] ?? 0;
    }
    /**
     * Deletes Group Items which are older than the expiry date for posts
     * for that group
     */
    public function cullExpiredGroupItems()
    {
        $time = time();
        // nesting SELECTs to keep Mysql from giving a Error 1093
        $sql = "DELETE FROM GROUP_ITEM WHERE ID IN (
            SELECT ID FROM (
            SELECT GI.ID AS ID FROM GROUP_ITEM GI, SOCIAL_GROUPS G
            WHERE GI.GROUP_ID=G.GROUP_ID AND G.POST_LIFETIME > 0
            AND ($time - GI.PUBDATE) > G.POST_LIFETIME) AS C)";
        $this->db->execute($sql);
    }
    /**
     * Returns true or false depending on whether a given user has voted on
     * a given post or not
     *
     * @param int $user_id id of user to check if voted
     * @param int $post_id id of GROUP_ITEM to see if voted on
     * @return bool whether or not the user has voted on that item
     */
    public function alreadyVoted($user_id, $post_id)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM_VOTE WHERE USER_ID = ?
            AND ITEM_ID = ?";
        $result = $db->execute($sql, [$user_id, $post_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row || !isset($row['NUM'])) {
            return false;
        }
        return ($row['NUM'] > 0);
    }
    /**
     * Casts one up vote by a user to a post
     *
     * @param int $user_id  id of user to cast vote for
     * @param int $post_id  id of post on which to cast vote
     */
    public function voteUp($user_id, $post_id)
    {
        $sql = "INSERT INTO GROUP_ITEM_VOTE VALUES (?, ?)";
        $this->db->execute($sql, [$user_id, $post_id]);
        $sql = "UPDATE GROUP_ITEM SET UPS = UPS + 1 WHERE ID=?";
        $this->db->execute($sql, [$post_id]);
    }
    /**
     * Casts one up vote by a user to a post
     *
     * @param int $user_id  id of user to cast vote for
     * @param int $post_id  id of post on which to cast vote
     */
    public function voteDown($user_id, $post_id)
    {
        $sql = "INSERT INTO GROUP_ITEM_VOTE VALUES (?, ?)";
        $this->db->execute($sql, [$user_id, $post_id]);
        $sql = "UPDATE GROUP_ITEM SET DOWNS = DOWNS + 1 WHERE ID=?";
        $this->db->execute($sql, [$post_id]);
    }
    /**
     * Writes one revision row into the page-history table for a wiki
     * page, first removing any row that already shares this page and
     * timestamp. The history is keyed by page and whole-second
     * timestamp, so two saves of the same page within the same second
     * would otherwise collide on the primary key. Removing the earlier
     * row lets the later save win, which is what happens when a resource
     * upload re-saves the page a moment after the first save in the same
     * second.
     *
     * @param object $db open database connection to write through
     * @param int $page_id id of the wiki page this revision belongs to
     * @param int $user_id id of the user credited as the editor
     * @param int $group_id id of the group that owns the page
     * @param string $page_name title of the page
     * @param string $page the page source stored for this revision
     * @param string $locale_tag locale the revision was written in
     * @param int $pubdate whole-second timestamp of the revision
     * @param string $edit_comment short reason shown in the history list
     * @return void
     */
    private function insertPageHistory($db, $page_id, $user_id, $group_id,
        $page_name, $page, $locale_tag, $pubdate, $edit_comment)
    {
        $sql = "DELETE FROM GROUP_PAGE_HISTORY WHERE PAGE_ID = ? AND
            PUBDATE = ?";
        $db->execute($sql, [$page_id, $pubdate]);
        $sql = "INSERT INTO GROUP_PAGE_HISTORY (PAGE_ID, EDITOR_ID,
            GROUP_ID, TITLE, PAGE, LOCALE_TAG, PUBDATE, EDIT_COMMENT)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $db->execute($sql, [$page_id, $user_id, $group_id, $page_name,
            $page, $locale_tag, $pubdate, $edit_comment]);
    }
    /**
     * Used to add a wiki page revision by a given user to a wiki page
     * of a given name in a given group viewing the group under a given
     * language. If the page does not exist yet it, and its corresponding
     * discussion thread is created. Two pages are used for storage
     * GROUP_PAGE which contains a parsed to html version of the most recent
     * revision of a wiki page and GROUP_PAGE_HISTORY which contains non-parsed
     * versions of all revisions
     *
     * @param int $user_id identifier of who is adding this revision
     * @param int $group_id which group the wiki page revision if being done in
     * @param string $page_name title of page being revised
     * @param string $page wiki page with potential wiki mark up containing the
     *     revision
     * @param string $locale_tag locale we are adding the revision to
     * @param string $edit_comment user's reason for making the revision
     * @param string $thread_title if this is the first revision, then this
     *     should contain the title for the discussion thread about the
     *     revision
     * @param string $thread_description if this is the first revision, then
     *     this should be the body of the first post in discussion thread
     * @param string $base_address default url to be used in links
     *     on wiki page that use short syntax
     * @param bool $add_relationship_data whether to add link relationship
     *      data for this wiki page or not (default add, typically won't
     *      add for knowledge wiki pages for search results generated by
     *      configs/TokenTool.php)
     * @param int $pubdate if not >=0 then the tiemstate of when the post was
     *      published; otherwise current time will be used
     * @param int $input_timestamp optional UNIX timestamp to use for impression
     *    tracking; if set to -1 or omitted, the current time will be used
     * @return int $page_id id of added or updated page
     */
    public function setPageName($user_id, $group_id, $page_name, $page,
        $locale_tag, $edit_comment, $thread_title, $thread_description,
        $base_address = "", $add_relationship_data = true, $pubdate = -1,
        $input_timestamp = -1)
    {
        $db = $this->db;
        $pubdate = ($pubdate == -1) ? time() : $pubdate;
        $parser = new WikiParser($base_address);
        $end_head = WikiParser::END_HEAD_VARS;
        if ($add_relationship_data) {
            $links_relationships = $parser->fetchLinks($page);
        }
        $is_form = false;
        if ($this->getPageType($page) == 'share') {
            $parsed_page = $page;
        } else {
            if (strstr($page, '{{submit|') !== false) {
                $is_form = true;
            }
            if (!str_contains($page, $end_head)) {
                $page = WikiParser::makeWikiPageHead() . $end_head . $page;
            }
            $render_engine = $this->getRenderEngine($group_id);
            $parsed_page = $parser->parse($page, render_engine: $render_engine);
            if ($is_form && strstr($page, '<form ') !== false) {
                return null;
            }
        }
        if ($page_id = $this->getPageId($group_id, $page_name, $locale_tag)) {
            //can only add and use resources for a page that exists
            $parsed_page = $this->insertResourcesParsePage($group_id, $page_id,
                $locale_tag, $parsed_page);
            if ($is_form) {
                $has_answer_field =
                    str_contains($parsed_page, '[{keyword-captcha');
                $form_proof = WikiParser::proofOfWorkFormFields(
                    !$has_answer_field);
                $parsed_page = str_replace($end_head, $end_head .
                    "\n<form method='post' >\n<input type='hidden' name='" .
                    C\p('CSRF_TOKEN') . "' value='[{just-token}]' >" .
                    "<input type='hidden' name='CSV_FORM_HASH' ".
                    "value='[{form-hash}]' >" . $form_proof, $parsed_page);
                $parsed_page .= "\n</form>\n";
                $page_body = explode($end_head, $parsed_page, 2)[1];
                $parsed_page = preg_replace("/\[{form\-hash}\]/",
                    "[{form-hash". hash("sha256", $page_body) . "}]",
                    $parsed_page);
            }
            $sql = "UPDATE GROUP_PAGE SET PAGE=?, LAST_MODIFIED=? WHERE ID = ?";
            $result = $db->execute($sql, [$parsed_page, $pubdate, $page_id]);
        } else {
            $discuss_thread = $this->addGroupItem(0, $group_id, $user_id,
                $thread_title, $thread_description . " " . date("r", $pubdate),
                C\WIKI_GROUP_ITEM, $pubdate, input_timestamp: $input_timestamp);
            $sql = "INSERT INTO GROUP_PAGE (DISCUSS_THREAD, GROUP_ID, TITLE,
                PAGE, LOCALE_TAG, LAST_MODIFIED) VALUES (?, ?, ?, ?, ?, ?)";
            $result = $db->execute($sql, [$discuss_thread, $group_id,
                $page_name, $parsed_page, $locale_tag, $pubdate]);
            $page_id = $db->insertID("GROUP_PAGE");
            ImpressionModel::initWithDb($user_id, $page_id, C\WIKI_IMPRESSION,
                $db, $input_timestamp);
            ImpressionModel::initWithDb(C\PUBLIC_USER_ID, $page_id,
                C\WIKI_IMPRESSION, $db, $input_timestamp);
        }
        $this->insertPageHistory($db, $page_id, $user_id, $group_id,
            $page_name, $page, $locale_tag, $pubdate, $edit_comment);
        if (!$add_relationship_data) {
            return $page_id;
        }
        $sql = "DELETE FROM GROUP_PAGE_LINK WHERE FROM_ID = ?";
        $db->execute($sql, [$page_id]);
        $template_prefix = "template:";
        if (substr($page_name, 0, strlen($template_prefix)) ==
            $template_prefix) {
            $sql = "INSERT INTO GROUP_PAGE_LINK (LINK_TYPE_ID, FROM_ID, TO_ID)".
                " VALUES (?, ?, ?)";
            $db->execute($sql, [C\WIKI_TEMPLATE_LINK, $page_id, $group_id]);
        }
        $sql = "INSERT INTO GROUP_PAGE_LINK (LINK_TYPE_ID, FROM_ID, TO_ID) ".
            "SELECT LINK_TYPE_ID, FROM_ID, ?  FROM GROUP_PAGE_PRE_LINK ".
            "WHERE TO_GROUP_ID = ? AND TO_PAGE_NAME = ?";
        $db->execute($sql, [$page_id, $group_id, $page_name]);
        $sql = "DELETE FROM GROUP_PAGE_PRE_LINK WHERE TO_GROUP_ID = ? AND " .
            "TO_PAGE_NAME = ?";
        $db->execute($sql, [$group_id, $page_name]);
        $sql = "DELETE FROM GROUP_PAGE_PRE_LINK WHERE FROM_ID = ?";
        $db->execute($sql, [$page_id]);
        $link_sql = "INSERT INTO GROUP_PAGE_LINK ".
            "(LINK_TYPE_ID, FROM_ID, TO_ID) VALUES (?, ?, ?)";
        $pre_link_sql = "INSERT INTO GROUP_PAGE_PRE_LINK ".
            "(LINK_TYPE_ID, FROM_ID, TO_GROUP_ID, TO_PAGE_NAME) ".
            " VALUES (?, ?, ?, ?)";
        foreach ($links_relationships as $links_relationship) {
            //extract link and relation type from $links_relationship
            list($link_page_name, $relationship_type) = explode('|',
                $links_relationship);
            if (!$relationship_type) {
                $relationship_id = C\WIKI_STANDARD_LINK;
            } else if (!($relationship_id = $this->getRelationshipId(
                $relationship_type))) {
                $sql = "INSERT INTO PAGE_RELATIONSHIP (NAME) VALUES (?)";
                $db->execute($sql, [$relationship_type]);
                $relationship_id = $this->getRelationshipId(
                    $relationship_type);
            }
            //get page id
            $linked_page_id = $this->getPageId($group_id, $link_page_name,
                $locale_tag);
            //insert into GROUP_PAGE_LINK values of parent and child links
            if (!$linked_page_id) {
                $db->execute($pre_link_sql, [$relationship_id, $page_id,
                    $group_id, $link_page_name]);
            } else {
                $db->execute($link_sql, [$relationship_id, $page_id,
                    $linked_page_id]);
            }
        }
        return $page_id;
    }
    /**
     *  Returns the page type of an unparsed wiki page
     *
     * @param string $unparsed_page an unparsed wiki page (i.e., so has
     *  the configuration parameters in its head)
     * @return string|bool returns the pages type as a string or false if
     *  this page head variable is not set
     */
    public function getPageType($unparsed_page)
    {
        if (preg_match('/^.*page_type\s*\=\s*(\w+).*END\_HEAD\_VARS/s',
            $unparsed_page, $matches)) {
            return $matches[1];
        }
        return false;
    }
    /**
     * Returns an array page_id => page_name of all templates for a given
     * Group for a given language
     *
     * @param int $group_id id of group to produce template map for
     * @param string $locale_tag of language to produce template map for
     * @return array page_id => page_name for each template for the given
     *  group in the provided language
     */
    public function getTemplateMap($group_id, $locale_tag)
    {
        $db = $this->db;
        $sql = "SELECT GP.ID AS ID, GP.TITLE AS PAGE_NAME
            FROM GROUP_PAGE GP, GROUP_PAGE_LINK GPL
            WHERE GPL.LINK_TYPE_ID = ? AND GPL.FROM_ID = GP.ID AND
            GP.LOCALE_TAG= ? AND GPL.TO_ID = ?";
        $result = $db->execute($sql,
            [C\WIKI_TEMPLATE_LINK, $locale_tag, $group_id]);
        $templates = [];
        if ($result) {
            while ($template = $db->fetchArray($result)) {
                $templates['t'.$template['ID']] = $template['PAGE_NAME'];
            }
        }
        return $templates;
    }
    /**
     * Looks up the page_id of a wiki page based on the group it belongs to,
     * its title, and the language it is in (these three things together
     * should uniquely fix a page).
     *
     * @param int $group_id group identifier of group wiki page belongs to
     * @param string $page_name title of wiki page to look up
     * @param string $locale_tag IANA language tag of page to lookup
     * @return mixed $page_id of page if exists, false otherwise
     */
    public function getPageId($group_id, $page_name, $locale_tag)
    {
        $db = $this->db;
        $sql = "SELECT ID FROM GROUP_PAGE WHERE GROUP_ID = ?
            AND TITLE=? AND LOCALE_TAG= ?";
        $result = $db->execute($sql, [$group_id, $page_name, $locale_tag]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if ($row) {
            return $row["ID"];
        }
        return false;
    }
    /**
     * Gets all the pages that are linked to a particular wiki page
     * by providing a particular relationship type.
     *
     * @param int $page_id identifier for the current page
     * @param int $group_id group that wiki page belongs
     * @param string $page_name name of the current page (in case page didn't
     *   exist before)
     * @param string $relationship the type of relationship
     *   linking the wiki pages
     * @param string $limit first row we want from the result set
     * @param string $num number of rows we want starting from the
     *     first row in the result set
     * @return two arrays of elements which represent all pages linked
     *     to and from the given wiki page with a particular relationship
     */
    public function pagesLinkedWithRelationship($page_id, $group_id,
        $page_name, $relationship, $limit, $num)
    {
        $db = $this->db;
        //get data from group pre link and insert to group link
        $sql = "INSERT INTO GROUP_PAGE_LINK (LINK_TYPE_ID, FROM_ID, TO_ID) ".
            "SELECT LINK_TYPE_ID, FROM_ID, ?  FROM GROUP_PAGE_PRE_LINK ".
            "WHERE TO_GROUP_ID = ? AND TO_PAGE_NAME = ?";
        $db->execute($sql, [$page_id, $group_id, $page_name]);
        $sql = "DELETE FROM GROUP_PAGE_PRE_LINK WHERE TO_GROUP_ID = ? AND " .
            "TO_PAGE_NAME = ?";
        $db->execute($sql, [$group_id, $page_name]);
        $sql = "DELETE FROM GROUP_PAGE_PRE_LINK WHERE FROM_ID = ?";
        $db->execute($sql, [$page_id]);
        //get the count of pages that link to the given page
        $sql = "SELECT COUNT(*) AS NUM
            FROM GROUP_PAGE_LINK L, PAGE_RELATIONSHIP P
            WHERE L.TO_ID = ? AND P.NAME = ? AND (L.LINK_TYPE_ID = P.ID)";
        $result = $db->execute($sql, [$page_id, $relationship]);
        if ($result) {
            $row = $db->fetchArray($result);
            $total_to_pages = $row['NUM'];
        }
        //get the count of pages that link from the given page
        $sql = "SELECT COUNT(*) AS NUM
            FROM GROUP_PAGE_LINK L, PAGE_RELATIONSHIP P
            WHERE L.FROM_ID = ? AND P.NAME = ? AND (L.LINK_TYPE_ID = P.ID)";
        $result = $db->execute($sql, [$page_id, $relationship]);
        if ($result) {
            $row = $db->fetchArray($result);
            $total_from_pages = $row['NUM'];
        }
        //get the array of all pages linking to this page
        $pages_that_link_to = [];
        $sql = "SELECT G.TITLE AS PAGES_LINKING_TO, G.ID AS PAGE_ID
            FROM GROUP_PAGE_LINK L, GROUP_PAGE G, PAGE_RELATIONSHIP P
            WHERE L.TO_ID = ? AND P.NAME = ? AND (L.FROM_ID = G.ID)
            AND (L.LINK_TYPE_ID = P.ID)"
            .$db->limitOffset($limit, $num);
        $result = $db->execute($sql, [$page_id, $relationship]);
        if ($result) {
            while ($tmp = $db->fetchArray($result)) {
                $pages_that_link_to[] = $tmp;
            }
        }
        // get the array of all pages that link from
        $pages_that_link_from = [];
        $sql = "SELECT G.TITLE AS PAGES_LINKING_FROM, G.ID AS PAGE_ID
            FROM GROUP_PAGE_LINK L, GROUP_PAGE G, PAGE_RELATIONSHIP P
            WHERE L.FROM_ID = ? AND P.NAME = ? AND (L.TO_ID = G.ID)
            AND (L.LINK_TYPE_ID = P.ID)"
            .$db->limitOffset($limit, $num);
        $result = $db->execute($sql, [$page_id, $relationship]);
        if ($result) {
            while ( $tmp = $db->fetchArray($result)) {
                $pages_that_link_from[] = $tmp;
            }
        }
        return [$total_to_pages, $pages_that_link_to, $total_from_pages,
            $pages_that_link_from];
    }
    /**
     * Gets all the relationship types between this particular wiki page
     * and all other pages that it is linked to.
     *
     * @param int $page_id identifier for the current page
     * @param string $limit first row we want from the result set
     * @param string $num number of rows we want starting from the
     *     first row in the result set
     * @return array of relationship types which represent all relationships
     *     between the given wiki page and all other linked wiki pages
     */
    public function getRelationshipsToFromPage($page_id, $limit, $num)
    {
        $total = $this->countPageRelationships($page_id);
        $db = $this->db;
        $i = 0;
        $relationships = [];
        $sql = "SELECT DISTINCT R.NAME AS RELATIONSHIP_TYPE
            FROM PAGE_RELATIONSHIP R, GROUP_PAGE_LINK G
            where (G.FROM_ID = ? OR G.TO_ID = ?) AND
            (R.ID = G.LINK_TYPE_ID)".$db->limitOffset($limit, $num);
        $result = $db->execute($sql, [$page_id, $page_id]);
        if ($result) {
            while ($relationships[$i] = $db->fetchArray($result)) {
                $i++;
            }
            unset($relationships[$i]);
        }
        return [$total, $relationships];
    }
    /**
     * Gets if there is any page related to this particular wiki page.
     *
     * @param int $page_id identifier for the current page
     * @return int distinct relationship-type count for the given page
     *     (0 when $page_id is falsy or the page has no relationships)
     */
    public function countPageRelationships($page_id)
    {
        if (!$page_id) {
            return 0;
        }
        $db = $this->db;
        $sql = "SELECT COUNT(DISTINCT LINK_TYPE_ID) AS NUM FROM GROUP_PAGE_LINK
            WHERE TO_ID = ? OR FROM_ID = ?";
        $result = $db->execute($sql, [$page_id, $page_id]);
        if ($result) {
            $row = $db->fetchArray($result);
            $total = $row['NUM'];
        }
        return $total;
    }
    /**
     * Return the page id, page string, and discussion thread id of the
     * most recent revision of a wiki page
     *
     * @param int $group_id group identifier of group wiki page belongs to
     * @param string $name title of wiki page to look up
     * @param string $locale_tag IANA language tag of page to lookup
     * @param string $mode if "edit" we assume we are looking up the page
     *     so that it can be edited and so we return the most recent non-parsed
     *     revision of the page. Otherwise, we assume the page is meant to be
     *     read and so we return the variant of the page where wiki markup
     *     has already been replaced with HTML
     * @return array (page_id, page, discussion_id) of desired wiki page
     */
    public function getPageInfoByName($group_id, $name, $locale_tag, $mode)
    {
        $db = $this->db;
        if (in_array($mode, ['api', 'edit', 'source'])) {
            $sql = "SELECT HP.PAGE_ID AS ID, HP.PAGE AS PAGE,
                HP.EDIT_COMMENT AS EDIT_COMMENT, HP.PUBDATE AS PUBDATE,
                GP.DISCUSS_THREAD AS DISCUSS_THREAD FROM GROUP_PAGE GP,
                GROUP_PAGE_HISTORY HP WHERE GP.GROUP_ID = ?
                AND GP.TITLE = ? AND GP.LOCALE_TAG = ? AND HP.PAGE_ID = GP.ID
                ORDER BY HP.PUBDATE DESC " . $db->limitOffset(0, 1);
        } else {
            $sql = "SELECT ID, PAGE, DISCUSS_THREAD, LAST_MODIFIED FROM
                GROUP_PAGE WHERE GROUP_ID = ? AND TITLE = ? AND LOCALE_TAG = ?";
        }
        $result = $db->execute($sql, [$group_id, $name, $locale_tag]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        return $row;
    }
    /**
     * Returns the group_id, language, page name, last modified date of a wiki
     * pagecorresponding to a page discussion thread with id $page_thread_id
     * @param int $page_thread_id the id of a wiki page discussion thread
     *     to look up page info for
     * @return array (group_id, language, and page name) of that wiki page
     */
    public function getPageInfoByThread($page_thread_id)
    {
        $db = $this->db;
        $sql = "SELECT GROUP_ID, LOCALE_TAG, TITLE AS PAGE_NAME,
            LAST_MODIFIED FROM GROUP_PAGE WHERE DISCUSS_THREAD = ?";
        $result = $db->execute($sql,  [$page_thread_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        return $row;
    }
    /**
     * Returns the group_id, language, and page name of a wiki page
     *     corresponding to $page_id
     * @param int $page_id to look up page info for
     * @return array (group_id, language, and page name) of that wiki page
     */
    public function getPageInfoByPageId($page_id)
    {
        $db = $this->db;
        $sql = "SELECT GROUP_ID, LOCALE_TAG, TITLE AS PAGE_NAME,
            DISCUSS_THREAD, LAST_MODIFIED FROM GROUP_PAGE WHERE ID = ?";
        $result = $db->execute($sql, [$page_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        return $row;
    }
    /**
     * Looks up the kind of a wiki page, such as standard or git_repository,
     * from its page id by reading the page's head.
     *
     * @param int $page_id id of the wiki page to look up
     * @return mixed the page type string, or false if the page was not found
     */
    public function getPageTypeByPageId($page_id)
    {
        $db = $this->db;
        $sql = "SELECT PAGE FROM GROUP_PAGE WHERE ID = ?";
        $result = $db->execute($sql, [$page_id]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!$row) {
            return false;
        }
        return $this->getPageType($row["PAGE"]);
    }
    /**
     * Makes a VersionManager for a wiki page's resource folder, turning its
     * own versioning off when the page is a git repository page. Git keeps
     * that page's history itself, so a second copy under an archive folder
     * would only duplicate it.
     *
     * @param string $folder resource folder the manager will look after
     * @param int $page_id id of the page whose resources are managed
     * @return VersionManager the manager for the folder
     */
    private function versionManagerForPage($folder, $page_id)
    {
        $versioning =
            ($this->getPageTypeByPageId($page_id) != "git_repository");
        return new VersionManager($folder, versioning: $versioning);
    }
    /**
     * Returns an historical revision of a wiki page
     *
     * @param int $page_id identifier of wiki page want revision for
     * @param int $pubdate timestamp of revision desired
     * @return array (id, non-parsed wiki page, page_name,
     *     group id, locale_tag, discussion thread id) of page revision
     */
    public function getHistoryPage($page_id, $pubdate)
    {
        $db = $this->db;
        $sql = "SELECT HP.PAGE_ID AS ID, HP.PAGE AS PAGE, HP.TITLE AS PAGE_NAME,
            HP.GROUP_ID AS GROUP_ID, HP.LOCALE_TAG AS LOCALE_TAG,
            GP.DISCUSS_THREAD AS DISCUSS_THREAD FROM GROUP_PAGE GP,
            GROUP_PAGE_HISTORY HP WHERE HP.PAGE_ID = ?
            AND HP.PUBDATE=? AND HP.PAGE_ID=GP.ID";
        $result = $db->execute($sql, [$page_id, $pubdate]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        if (!isset($row["PAGE"])) {
            return false;
        }
        return $row;
    }
    /**
     * Returns a list of revision history info for a wiki page.
     *
     * @param int $page_id identifier for page want revision history of
     * @param int $limit first row we want from the result set
     * @param int $num number of rows we want starting from the first row
     *     in the result set
     * @return array elements of which are array with the revision date
     *     (PUBDATE), user name, page length, edit reason for the wiki pages
     *     revision
     */
    public function getPageHistoryList($page_id, $limit, $num)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS TOTAL, MAX(TITLE) AS PAGE_NAME
            FROM GROUP_PAGE_HISTORY
            WHERE PAGE_ID = ?";
        $page_name = "";
        $result = $db->execute($sql, [$page_id]);
        if ($result) {
            $row = $db->fetchArray($result);
            $total = (!empty($row)) ? $row["TOTAL"] : 0;
            $page_name = (!empty($row)) ? $row["PAGE_NAME"] : "";
        }
        $pages = [];
        if ($total > 0) {
            $sql = "SELECT H.PUBDATE AS PUBDATE, U.USER_NAME AS USER_NAME,
                LENGTH(H.PAGE) AS PAGE_LEN,
                H.EDIT_COMMENT AS EDIT_REASON FROM GROUP_PAGE_HISTORY H, USERS U
                WHERE H.PAGE_ID = ? AND
                U.USER_ID= H.EDITOR_ID ORDER BY PUBDATE DESC ".
                $db->limitOffset($limit, $num);
            $result = $db->execute($sql, [$page_id]);
            if ($result) {
                while ($row = $db->fetchArray($result)) {
                    $pages[] = $row;
                }
            }
        }
        return [$total, $page_name, $pages];
    }
    /**
     * Given the Wiki name in the format GroupName@PageName/sub_path/some_file
     * returns array [group_id, page_id, sub_path, some_file] for the given
     * resource. If one of the components is missing in the above, does its
     * best guess for the value
     *
     * @param string $complete_group_page_name formatted as described in summary
     * @param string $locale_tag language of wiki page
     * @return array [group_id, page_id, sub_path, some_file]
     */
    public function getGroupIdPageIdSubPathFromName(
        $complete_group_page_name, $locale_tag = C\DEFAULT_LOCALE)
    {
        $name_parts = explode("@", $complete_group_page_name, 2);
        if (count($name_parts) == 1) {
            $group_id = C\PUBLIC_GROUP_ID;
            $name_path = $complete_group_page_name;
        } else {
            $group_id = $this->getGroupId($name_parts[0]);
            $name_path = $name_parts[1];
        }
        $name_path_parts = explode("/", $name_path, 2);
        if (count($name_path_parts) == 1) {
            list($page_name, $sub_path) = [$name_path, ""];
        } else {
            list($page_name, $sub_path) = $name_path_parts;
        }
        $trim_flag = false;
        if (str_contains($sub_path, "%") &&
            !str_contains($sub_path, ".")) {
            $sub_path = "$sub_path.t";
            $trim_flag = 2;
        }
        $path_parts = pathinfo($sub_path);
        if (empty($path_parts['extension'])) {
            $file_name = "";
        } else {
            $file_name = $path_parts['basename'];
            if ($trim_flag) {
                $file_name = substr($file_name, 0, -2);
            }
            $sub_path = $path_parts['dirname'];
        }
        $page_name = str_replace(" ", "_", trim($page_name));
        $page_id = $this->getPageId($group_id, $page_name, $locale_tag);
        return [$group_id, $page_id, $sub_path, $file_name];
    }
    /**
     *  Returns the folder and thumb folder associated with the resources of
     *  a wiki page. Also returns base folders of these which may be different
     *  if there is a sub_path.
     *
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want folder paths for
     * @param string $sub_path file system path within the resource folder
     *      to get the folder name for
     * @param bool $create if folder doesn't exist whether to create it or not
     * @param bool $check_redirect whether to check the default group page
     *      folder for a redirect to a different folder
     * @param bool $check_exists if true and $create false, then check if the
     *      file path exists and return false if it doesn't. If false,
     *      and prefix path doesn't exist, this flag will cause it to be
     *      created
     * @return array (page_folder, thumb_folder, base_page_folder,
     *      base_thumb_folder)
     */
    public function getGroupPageResourcesFolders($group_id, $page_id,
        $sub_path = "", $create = false, $check_redirect = true,
        $check_exists = true)
    {
        $redirect_filename = "redirect.txt";
        $sub_path = str_replace("..", "", $sub_path);
        $sub_path = str_replace("/./", "/", $sub_path);
        $sub_path = ($sub_path == "/" || $sub_path == "") ? "" : "/$sub_path";
        $group_page_folder = L\crawlHash(
            "group" . $group_id . $page_id . C\p('AUTH_KEY'));
        $old_thumb_page_folder = L\crawlHash(
            "thumb" . $group_id . $page_id . C\p('AUTH_KEY'));
        $group_prefix = substr($group_page_folder, 0, 3);
        $old_thumb_prefix = substr($old_thumb_page_folder, 0, 3);
        $resource_path = C\APP_DIR . "/resources";
        $group_prefix_path = $resource_path . "/$group_prefix";
        $old_thumb_prefix_path = $resource_path . "/$old_thumb_prefix";
        $group_path = "$group_prefix_path/$group_page_folder";
        $redirect_path = $group_path . "/$redirect_filename";
        $thumb_path = "$group_prefix_path/t$group_page_folder";
        $old_thumb_path = "$old_thumb_prefix_path/$old_thumb_page_folder";
        $redirected = false;
        if (!$check_exists || file_exists($group_path)) {
            if (!$check_exists && !file_exists($group_prefix_path)) {
                L\makePath($group_prefix_path);
            }
            if ($check_redirect && file_exists($redirect_path)) {
                $tmp_path = trim(file_get_contents($redirect_path));
                if (!is_dir($tmp_path) && $create &&
                    is_dir(dirname($tmp_path))) {
                    @mkdir($tmp_path);
                }
                if (!is_dir($tmp_path)) {
                    /* a resource path was set for this page but its folder
                       does not exist; when asked to create it could not be
                       made under an existing parent. Either way, report the
                       failure rather than silently falling back to the
                       page's default folder, which would hide the
                       misconfiguration and store resources where the
                       operator did not ask */
                    return false;
                }
                $group_path = $tmp_path;
                $group_hash =  L\crawlHash($group_path);
                $group_prefix = substr($group_hash, 0, 3);
                $group_prefix_path = $resource_path . "/$group_prefix";
                $no_redirect_thumb_path = $thumb_path;
                $thumb_path = "$group_prefix_path/t$group_hash";
                if (!file_exists($group_prefix_path) &&
                    file_exists($group_path . $sub_path) && $create) {
                    L\makePath($group_prefix_path);
                }
                $redirected = true;
            }
            if (!$check_exists || (file_exists($group_path . $sub_path) &&
                file_exists($thumb_path . $sub_path))) {
                return [$group_path . $sub_path, $thumb_path . $sub_path,
                    $group_path, $thumb_path];
            } else if (!$create) {
                if (file_exists($group_path . $sub_path)) {
                    /* if path exists always let thumb path be created
                       as used for count files
                     */
                    if (!file_exists($thumb_path)) {
                        L\makePath($thumb_path);
                    }
                    $thumb_path = file_exists($thumb_path) ? $thumb_path :
                        false;
                    return [$group_path . $sub_path, false,
                        $group_path, $thumb_path];
                }
                return false;
            }
        } elseif (!$create) {
            return false;
        }
        /*
            The naming convention for the thumb folder directory has evolved.
            The current version allows one to compute the thumb folder
            based on the group-page resource folder. It also satisfies
            that if a two pages have redirects to the same resource folder
            their thumb folders will be the same. The code below also
            attempts to move pre-existing thumbs folders of old yioop
            version to the new locations
         */
        if (file_exists($group_path) || L\makePath($group_path)) {
            if ($check_redirect && !$redirected &&
                file_exists($group_path . "/$redirect_filename")) {
                $tmp_path = trim(file_get_contents($group_path .
                    "/$redirect_filename"));
                if (!is_dir($tmp_path) && $create &&
                    is_dir(dirname($tmp_path))) {
                    @mkdir($tmp_path);
                }
                if (!is_dir($tmp_path)) {
                    /* the resource path was set but cannot be reached and,
                       when asked, could not be created, so do not quietly
                       keep using the page's default folder */
                    return false;
                }
                $group_path = $tmp_path;
                $group_hash =  L\crawlHash($group_path);
                $group_prefix = substr($group_hash, 0, 3);
                $group_prefix_path = $resource_path."/$group_prefix";
                $no_redirect_thumb_path = $thumb_path;
                $thumb_path = "$group_prefix_path/t$group_hash";
                if (!file_exists($group_prefix_path)) {
                    L\makePath($group_prefix_path);
                }
            }
            if (!file_exists($thumb_path)) {
                if (!empty($no_redirect_thumb_path) &&
                    file_exists($no_redirect_thumb_path)) {
                    rename($no_redirect_thumb_path, $thumb_path);
                } else if (file_exists($old_thumb_path)) {
                    rename($old_thumb_path, $thumb_path);
                }
            }
            $full_group_path = $group_path . $sub_path;
            $full_thumb_path = $thumb_path . $sub_path;
            if ((file_exists($full_group_path) || L\makePath($full_group_path))
                && (file_exists($full_thumb_path) ||
                L\makePath($full_thumb_path))) {
                return [$full_group_path, $full_thumb_path, $group_path,
                    $thumb_path];
            }
        }
        return false;
    }
    /**
     * Tells whether a page has a resource path (a redirect to a folder the
     * operator chose for its resources) that is set but cannot be used
     * because that folder does not exist and could not be created. Such a
     * page has nowhere valid to keep its resources, and the read view warns
     * about it rather than the resources silently going to, or coming from,
     * the page's default folder.
     *
     * @param int $group_id group the page belongs to
     * @param int $page_id page to check
     * @return bool whether the page's resource path is set but unusable
     */
    public function resourcePathBroken($group_id, $page_id)
    {
        $default = $this->getGroupPageResourcesFolders($group_id, $page_id,
            "", false, false, false);
        if (!is_array($default) ||
            !file_exists($default[0] . "/redirect.txt")) {
            return false;
        }
        return $this->getGroupPageResourcesFolders($group_id, $page_id, "",
            false) === false;
    }
    /**
     * Given a wiki page that has been parsed to html except for wiki syntax
     * related to resources, this method adds the html to include these
     * resources
     *
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to parse resources for
     * @param string $locale_tag the locale of the parsed page.
     * @param string $parsed_page the parsed wiki page before resources added
     * @param string $csrf_token to prevent cross-site request forgery
     * @param string $controller name of controller (admin or group) that
     *  inserted urls should be for
     * @param bool $include_charts_and_spreadsheets when true, render
     *  chart/spreadsheet resources inline as well; when false (default)
     *  they are linked rather than embedded
     * @return string resulting html page
     */
    public function insertResourcesParsePage($group_id, $page_id, $locale_tag,
        $parsed_page, $csrf_token = "", $controller = 'admin',
        $include_charts_and_spreadsheets = false)
    {
        $default_folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id);
        $autoplay = "autoplay='autoplay'";
        if (str_starts_with($page_id, 'post')) {
            $autoplay = "";
        }
        if ($default_folders) {
            list($folder, $thumb_folder,) = $default_folders;
        } else {
            $folder = "";
            $thumb_folder = "";
        }
        if (!preg_match_all('/\(\(resource(\-?[a-z]+)?\:(.+?)\|(.+?)\)\)/ui',
            $parsed_page, $matches)) {
            return $parsed_page;
        }
        $num_matches = count($matches[0]);
        for ($i = 0; $i < $num_matches; $i++) {
            $match_string = $matches[0][$i];
            $resource_namespace_name = $matches[2][$i];
            if (empty($matches[1][$i])) {
                $resource_namespace_name = urldecode($resource_namespace_name);
            }
            /* a !verbose suffix asks for the download link and, for a
               spreadsheet, the histogram toggle; without it they are left
               off so a page can just show its data. It uses a symbol other
               than a colon so it does not clash with the namespace colon,
               and it is pulled off here before the namespace split and the
               rectangle parse below */
            $verbose = false;
            if (str_ends_with($resource_namespace_name, "!verbose")) {
                $verbose = true;
                $resource_namespace_name = substr($resource_namespace_name,
                    0, -strlen("!verbose"));
            }
            $namespace_parts = explode(":", $resource_namespace_name, 2);
            if (count($namespace_parts) > 1 && $matches[1][$i] != "-qr") {
                list($current_namespace, $resource_namespace_name) =
                    $namespace_parts;
                if (empty($current_namespace)) {
                    $current_page_id = $page_id;
                } else {
                    $current_page_id = $this->getPageId($group_id,
                        $current_namespace, $locale_tag);
                }
                if ($current_page_id === false || $current_page_id === null) {
                    continue;
                }
                $current_folders = $this->getGroupPageResourcesFolders(
                    $group_id, $current_page_id);
                if ($current_folders) {
                    list($current_folder, $current_thumb_folder,) =
                        $current_folders;
                    if (!$current_thumb_folder) {
                        continue;
                    }
                } else {
                    continue;
                }
            } else {
                $current_page_id = $page_id;
                $current_folder = $folder;
                $current_thumb_folder = $thumb_folder;
            }
            $sub_path = "";
            $chart_resource = (in_array($matches[1][$i], ["-bargraph",
                "-linegraph", "-pointgraph"])) ? true : false;
            $data_resource = ($matches[1][$i] == "-data") ? true : false;
            $nolink_resource = ($matches[1][$i] == "-nolink") ? true : false;
            if (C\nsdefined("QRENCODE")) {
                $qr_resource = ($matches[1][$i] == "-qr") ? true : false;
            }
            $thumb_resource = ($matches[1][$i] == "-thumb") ? true : false;
            $resource_description = $matches[3][$i];
            $resource_description_parts = explode("|", $matches[3][$i]);
            if (!empty($resource_description_parts[1])) {
                $resource_description = $resource_description_parts[1];
                $sub_path = $resource_description_parts[0];
            }
            $spreadsheet_rectangle = false;
            $rect_parts = explode("#", $resource_namespace_name);
            $data_chart = false;
            if (count($rect_parts) > 1) {
                if ($chart_resource) {
                    $num_rect_parts = count($rect_parts);
                    $data_chart = empty($rect_parts[0]) && $num_rect_parts > 2;
                    if ($data_chart || $num_rect_parts == 6) {
                        $resource_name = "";
                        if ($data_chart) {
                            $points = array_slice($rect_parts, 2);
                            $x_values = [];
                            $y_values = [];
                            foreach ($points as $point) {
                                preg_match("/^\s*\((.+)\,(.+)\)\s*$/",
                                    $point, $point_matches);
                                if (empty($point_matches[2])) {
                                    break;
                                }
                                $x_values[] = $point_matches[1];
                                $y_values[] = $point_matches[2];
                            }
                        } else {
                            list($resource_name, $chart_config, $x_start,
                                $x_end, $y_start, $y_end) = $rect_parts;
                        }
                        $chart_type = ($matches[1][$i] == '-bargraph') ?
                            "BarGraph" : (($matches[1][$i] == '-linegraph')
                            ? "LineGraph" : "PointGraph");
                        if (!empty($chart_config)) {
                            $chart_config = json_decode($chart_config, true);
                        } else {
                            $chart_config = [];
                        }
                        $chart_config['type'] = $chart_type;
                        $chart_config = json_encode($chart_config);
                    } else {
                        $resource_name = implode("#", $rect_parts);
                        $chart_resource = false;
                    }
                } else {
                    $spreadsheet_rectangle = $this->convertSpreadsheetRectangle(
                        array_slice($rect_parts, 2));
                    if ($spreadsheet_rectangle === false) {
                        $resource_name = implode("#", $rect_parts);
                    } else {
                        $resource_name = $rect_parts[0];
                        $sheet_config = ($rect_parts[1] == 'noheadings') ?
                            "{'headings': false}" : "{}";
                    }
                }
            }
            $resource_name = (isset($rect_parts[0])) ? $rect_parts[0] :
                $resource_namespace_name;
            $is_dir = false;
            if ($data_chart) {
                $mime_type = "text/csv";
                $resource_url = "";
            } else if ($data_resource) {
                $resource_url = "data://$resource_name";
                $url_parts = explode(";", $resource_name);
                $mime_type = $url_parts[0];
                $file_name = $resource_url; /* PHP can do file_get_contents on
                    data uri's*/
            } else if (!empty($qr_resource)) {
                $raw_qr_code = shell_exec(C\QRENCODE .
                    " -o - \"$resource_namespace_name\"");
                $qr_code = "data:image/png;base64,".
                    base64_encode($raw_qr_code ?? "");
                $resource_url = $qr_code;
                $nolink_resource = true;
                $mime_type = "image/png";
                $file_name = $resource_url; /* PHP can do file_get_contents on
                    data uri's*/
            } else {
                $current_folder  = realpath($current_folder);
                $file_name = (empty($sub_path)) ?
                    "$current_folder/$resource_name"
                    : "$current_folder/$sub_path/$resource_name";
                $resource_pos = strpos($file_name, "resources");
                if ($resource_pos === false) {
                    $resource_path = "resources/" . substr($current_folder,
                        strrpos($current_folder, "/") + 1).
                        "/$sub_path/$resource_name";
                } else {
                    $resource_path = substr($file_name, $resource_pos);
                }
                $mime_type = L\mimeType($file_name);
                $mime_type_parts = explode(";", $mime_type);
                $mime_type = $mime_type_parts[0];
                $resource_url = $this->getGroupPageResourceUrl($csrf_token,
                    $group_id, $current_page_id, $resource_name, $sub_path);
                if (is_dir($file_name)) {
                    $is_dir = true;
                    $is_static = ($controller == 'static') ? true : false;
                    $page_info = $this->getPageInfoByPageId($current_page_id);
                    if (empty($page_info['PAGE_NAME'])) {
                        continue;
                    }
                    $resource_url =
                        htmlentities(B\wikiUrl($page_info['PAGE_NAME'] ,
                        true, $controller, $group_id));
                    if ($csrf_token != "") {
                        $resource_url .= "&amp;". C\p('CSRF_TOKEN') . "=" .
                            $csrf_token;
                    } else {
                        $resource_url .= "&amp;[{rtoken}]";
                    }
                }
            }
            if ($is_dir) {
                $new_sub_path = ($sub_path) ?
                    "$sub_path/$resource_name" : "$resource_name";
                $resource_url .= "&amp;sf=" . urlencode($new_sub_path);
                $replace_string = "<a href='$resource_url' >".
                    "$resource_description</a>";
                $parsed_page = preg_replace('/'.preg_quote($match_string, '/')
                    .'/u',  $replace_string, $parsed_page);
            } else if (($matches[1][$i] == "-link")) {
                $replace_string = "<a href='$resource_url' >".
                    "$resource_description</a>";
                $parsed_page = preg_replace('/' . preg_quote($match_string, '/')
                    .'/u',  $replace_string, $parsed_page);
            } else if (in_array(substr($mime_type, 0, 5), ['image', 'video']) ||
                $mime_type == 'application/ogg') {
                $parsed_page = $this->insertVideoImageResourceParsePage(
                    $mime_type, $parsed_page, $thumb_resource, $nolink_resource,
                    $resource_name, $resource_url, $resource_description,
                    $autoplay, $current_folder, $data_resource, $match_string,
                    $locale_tag, $csrf_token, $group_id, $current_page_id,
                    $sub_path);
            } else if (in_array($mime_type, ['audio/aiff', 'audio/basic',
                'audio/L24', 'audio/mpeg', 'audio/mpeg3', 'audio/mp4',
                'audio/ogg', 'audio/opus',
                'audio/vorbis', 'audio/vnd.rn-realaudio', 'audio/vnd.wave',
                'audio/webm'])) {
                $audio_id = L\crawlHash($resource_name);
                $replace_string = "<div class='audio-message-container'>\n".
                    "<div class='audio-player-wrapper'>\n".
                    "<audio controls='controls' $autoplay class='audio' " .
                        "id='$audio_id'>\n".
                    "<source src='$resource_url' type='$mime_type'>\n".
                    $resource_description."\n".
                    "</audio>\n";
                if (C\nsdefined('WHISPER')) {
                    $replace_string .= "<button onclick='transcribeAudio(\"" .
                        htmlspecialchars($resource_name, ENT_QUOTES) .
                        "\", \"" .
                        htmlspecialchars($current_folder, ENT_QUOTES) .
                        "\", \"" .
                        htmlspecialchars($csrf_token, ENT_QUOTES) . "\")' " .
                        "class='transcribe-btn' title='Show transcript'>" .
                            "📝</button>\n";
                }
                $replace_string .= "</div>\n".
                    "<div class='transcript-container' " .
                        "id='transcript-$audio_id'></div>\n".
                    "</div>";

                $parsed_page = preg_replace('/'.preg_quote($match_string, '/')
                    .'/u', $replace_string, $parsed_page);
            } else if ($mime_type == 'application/epub+zip' &&
                file_exists(C\APP_DIR .
                "/scripts/epubjs-reader/reader/index.html")) {
                $epub_reader_url = C\SHORT_BASE_URL .
                    "wd/scripts/epubjs-reader/reader/index.html";
                $replace_string = "<div class='wiki-resource-download'>".
                    "<a href='$resource_url' >⤓</a></div>";
                $resource_url = urlencode($resource_url);
                $resource_url = "$epub_reader_url?bookPath=$resource_url";
                $replace_string .= "<iframe class='wiki-resource-object' ".
                    "src='$resource_url' >$resource_description</iframe>";
                $parsed_page = preg_replace('/'.preg_quote($match_string, '/')
                    .'/u',  $replace_string, $parsed_page);
            } else if (in_array($mime_type, ['text/html', 'application/pdf'])) {
                $replace_string = "<div class='wiki-resource-download'>".
                    "<a href='$resource_url' >⤓</a></div>";
                $replace_string = "<iframe class='wiki-resource-object' ".
                    "src='$resource_url' >$resource_description</iframe>";
                $parsed_page = preg_replace('/'.preg_quote($match_string, '/')
                    .'/u',  $replace_string, $parsed_page);
            }  else if ($mime_type == 'text/csv') {
                if (!$include_charts_and_spreadsheets) {
                    continue;
                }
                if (!$data_chart && !isset($resource[$file_name])) {
                    $data_url_csv = 'data://text/csv;base64,';
                    if (substr($file_name, 0,
                        strlen($data_url_csv)) == $data_url_csv) {
                        $resource = base64_decode(substr($file_name,
                            strlen($data_url_csv)));
                    } else {
                        $resource = file_get_contents($file_name);
                    }
                    $resources[$file_name] = L\parseCsv($resource);
                }
                if ($chart_resource) {
                    if (!$data_chart) {
                        $pre_x_vals = $this->evalRangeExpression(
                            "$x_start:$x_end", 0, $resources[$file_name]);
                        $x_values = $pre_x_vals[1];
                        $pre_y_vals = $this->evalRangeExpression(
                            "$y_start:$y_end", 0, $resources[$file_name]);
                        $y_values = $pre_y_vals[1];
                    }
                    $resource_data = json_encode(array_combine($x_values,
                        $y_values));
                    $replace_string = "<script>\n" .
                        "if (typeof chart_data === 'undefined') {\n" .
                        "    chart_data = [];\n".
                        "    chart_config = [];\n}\n".
                        "chart_data[$i] = $resource_data;" .
                        "chart_config[$i] = $chart_config;" .
                        "\n</script><div id='chart_$i'> </div>";
                    $parsed_page = preg_replace('/' .
                        preg_quote($match_string, '/')
                        .'/u',  $replace_string, $parsed_page, 1);
                } else {
                    $resource_data = json_encode(
                        $this->spreadsheetRectangleData(
                        $spreadsheet_rectangle, $resources[$file_name]));
                    if (isset($sheet_config)) {
                        $spread_config =
                            "spreadsheet_config[$i] = $sheet_config;";
                    } else {
                        $spread_config = "spreadsheet_config[$i] = {};";
                    }
                    if (!empty($spreadsheet_rectangle[0]) &&
                        $spreadsheet_rectangle[0] != [0, 0]) {
                        $spread_config .= "spreadsheet_config[$i]['offset'] =".
                            json_encode($spreadsheet_rectangle[0]) .";";
                    }
                    if (!empty($_SESSION['USER_NAME'])) {
                        $spread_config .=
                            "spreadsheet_config[$i]['user_name'] =".
                            json_encode($_SESSION['USER_NAME']) .";";
                    }
                    $spreadsheet_controls = "";
                    if ($verbose) {
                        $spreadsheet_controls =
                            "<div class='wiki-resource-download'>".
                            "<a href='$resource_url' >".
                            "⤓</a></div><div><a href='javascript:".
                            "spreadsheet[$i].toggleHistograms({".
                            "has_column_headers:true, ignore_columns:[".
                            "\"user_captcha_text\"]})'>" .
                            L\tl('wiki_element_toggle_histograms') .
                            "</a></div>";
                    }
                    $replace_string = "<script>\n" .
                        "if (typeof spreadsheet_data === 'undefined') {\n" .
                        "    spreadsheet_data = [];\n".
                        "    spreadsheet_config = [];\n}\n".
                        "spreadsheet_data[$i] = $resource_data;" .
                        $spread_config .
                        "\n</script>".
                        $spreadsheet_controls .
                        "<div id='spreadsheet_$i'></div>";
                    $parsed_page = preg_replace('/' .
                        preg_quote($match_string, '/')
                        .'/u',  $replace_string, $parsed_page, 1);
                }
            } else if (str_starts_with($mime_type, 'text')) {
                $resource = file_get_contents($file_name);
                $replace_string = "<pre>\n".htmlentities($resource)."\n</pre>";
                $parsed_page = preg_replace('/'.preg_quote($match_string, '/')
                    .'/u',  $replace_string, $parsed_page);
            } else {
                $replace_string = "<a href='$resource_url' >".
                    "$resource_description</a>";
                $parsed_page = preg_replace('/'.preg_quote($match_string, '/')
                    .'/u',  $replace_string, $parsed_page);
            }
        }
        return $parsed_page;
    }
    /**
     * Auxiliary method for @see insertResourcesParsePage used to insert
     * video and image resources into an otherwise parsed to HTML wiki page.
     *
     * @param string $mime_type of resource to insert
     * @param string $parsed_page partiall parsed wiki page to insert resources
     *  into
     * @param bool $thumb_resource whether this is a thumbnail image resource.
     * @param bool $nolink_resource whether this is a nolink image resource
     *  (one not enclosed is a link to the resource).
     * @param string $resource_name name of resource that is being inserted
     * @param string $resource_url url of resource that's being inserted
     * @param string $resource_description human description of resource to be
     *  inserted
     * @param string $autoplay html code for attribute saying whether or not
     *  this is an autoplay resource
     * @param string $current_folder folder in which resource lives, so
     *  can check if there is an associated vtt transscript of audio
     * @param string $data_resource if the Video or Image resource is a
     *  data url, then the string of that data url. If this case we use
     *  this only to know if should pother with auxiliary source or track tags
     * @param string $match_string code string that was used to make the
     *  portion of the partially parsed wiki page to be replaced with htm
     *  for the resource to be inserted
     * @param string $locale_tag tag name language of wiki page
     * @param string $csrf_token cross site request forgery token to be
     *  used in links
     * @param int $group_id group of page into which resources are being
     *  inserted
     * @param int $current_page_id of page within group
     * @param string $sub_path of folder structure of wiki page from which
     *  resource comes
     * @return string the page with resources inserted
     */
    public function insertVideoImageResourceParsePage($mime_type, $parsed_page,
        $thumb_resource, $nolink_resource, $resource_name, $resource_url,
        $resource_description, $autoplay, $current_folder, $data_resource,
        $match_string, $locale_tag, $csrf_token, $group_id, $current_page_id,
        $sub_path)
    {
        $resource_path = parse_url($resource_url, \PHP_URL_PATH) ?? "";
        $path_info = pathinfo($resource_path);
        $dir_name = $path_info['dirname'] ?? "";
        $is_360 = (preg_match("/\b360\b/", $dir_name)) ? true : false;
        if (str_starts_with($mime_type, 'image')) {
            if ($thumb_resource) {
                $replace_string = "<a class='image-list' ".
                    "href='$resource_url' ><img src='$resource_url' ".
                    " loading='lazy' alt='$resource_description' ></a>";
            } else {
                if ($is_360) {
                    $resource_id = L\crawlHash($resource_url);
                    $replace_string = <<<EOD
<div class='photo-container'>
    <canvas id='p$resource_id' class='canvas-360' >
    <script>
    if (typeof yioop_post_scripts === 'undefined') {
        yioop_post_scripts = [];
    }
    yioop_post_scripts.push(function () {
        draw360('p$resource_id', '$resource_url');
    });
    </script>
</div>
EOD;
                } else if ($nolink_resource) {
                    $replace_string = "<img src='$resource_url' loading='lazy'".
                        " alt='$resource_description' class='photo' >";
                } else {
                    $replace_string = "<a href='$resource_url' ><img" .
                        " loading='lazy' src='$resource_url' ".
                        " alt='$resource_description' class='photo' ></a>";
                }
            }
            $parsed_page = preg_replace('/'. preg_quote($match_string,'/').'/u',
                $replace_string, $parsed_page);
        } else {
            $video_type_extensions = ['video/mp4' => "mp4",
                'video/ogg' => "ogv", 'video/avi' => 'avi',
                'video/quicktime' => 'mov',
                'video/x-flv' => 'flv',
                'video/x-ms-wmv' => 'wmv', 'video/webm' => 'webm',
                'application/ogg' => 'ogv'];
            $replace_string = "<video class='video' " .
                "controls='controls' $autoplay id='" .
                L\crawlHash($current_page_id . $resource_name . $sub_path) .
                    "' >\n".
                "<source src='$resource_url' type='$mime_type'/>\n";
            $multi_source_types = ["mp4", "webm", "ogg"];
            $current_extension = $video_type_extensions[$mime_type];
            $add_sources = [];
            if (empty($data_resource) &&
                !in_array($current_extension, $multi_source_types)) {
                $add_sources = array_diff($multi_source_types,
                    [$current_extension]);
            }
            $pre_name = substr($resource_name, 0,
                -strlen($current_extension) -1);
            // add subtitles file if exists
            $subtitle_file = "$pre_name-subtitles-$locale_tag.vtt";
            $all_subtitle_files = glob(
                "$current_folder/$pre_name-subtitles-*.vtt");
            if (empty($data_resource) && !empty($all_subtitle_files)) {
                foreach ($all_subtitle_files as $sub_file) {
                    preg_match("@$pre_name-subtitles-(.+).vtt@", $sub_file,
                        $matches);
                    if (!empty($matches[1])) {
                        $resource_url = $this->getGroupPageResourceUrl(
                            $csrf_token, $group_id, $current_page_id,
                            $matches[0], $sub_path);
                        $default = ($sub_file ==
                            "$current_folder/$subtitle_file") ?
                            "default" : "";
                        $tag = $matches[1];
                        $replace_string .= "<track src='$resource_url' " .
                            "label='$tag' kind='subtitles' " .
                            "srclang='$tag' $default >\n";
                    }
                }
            }
            $captions_file = "$pre_name-captions-$locale_tag.vtt";
            $all_captions_files = glob(
                "$current_folder/$pre_name-captions-*.vtt");
            if (empty($data_resource) && !empty($all_captions_files)) {
                foreach ($all_captions_files as $cap_file) {
                    preg_match("@$pre_name-captions-(.+).vtt@", $cap_file,
                        $matches);
                    if (!empty($matches[1])) {
                        $resource_url = $this->getGroupPageResourceUrl(
                            $csrf_token, $group_id, $current_page_id,
                            $matches[0], $sub_path);
                        $default = ($cap_file ==
                            "$current_folder/$captions_file") ?
                            "default" : "";
                        $tag = $matches[1];
                        $replace_string .= "<track src='$resource_url' " .
                            "label='$tag' kind='captions' " .
                            "srclang='$tag' $default >\n";
                    }
                }
            }
            foreach ($add_sources as $extension) {
                if (file_exists("$current_folder/$pre_name.$extension")) {
                    $resource_url = $this->getGroupPageResourceUrl(
                        $csrf_token, $group_id, $current_page_id,
                        "$pre_name.$extension", $sub_path);
                    $replace_string .= "<source src='$resource_url' ".
                        "type='video/$extension'/>\n";
                }
            }
            $replace_string .= $resource_description . "\n</video>";
            $parsed_page = preg_replace('/'.preg_quote($match_string, '/').'/u',
                $replace_string, $parsed_page);
        }
        return $parsed_page;
    }
    /**
     * Used to convert a pair of spreadsheet coordinates into a pair
     * of integer rectangular coordinates. For example [A3, B4] into
     * [[0,2], [1, 3]]
     *
     * @param array $spreadsheet_coords a pair of spreadsheet cell coordinates
     * @return array rectangular integer pair corresponding to these coordinates
     */
    public function convertSpreadsheetRectangle($spreadsheet_coords)
    {
        if ($spreadsheet_coords &&
            is_array($spreadsheet_coords) && count($spreadsheet_coords) <= 2) {
            if (count($spreadsheet_coords) == 1) {
                $spreadsheet_coords[] = $spreadsheet_coords[0];
            }
        } else {
            return false;
        }
        $rect = [];
        for ($i = 0; $i < 2; $i++) {
            $num_matches = preg_match("/^([A-Z]+)(\d+)$/",
                $spreadsheet_coords[$i], $cell_parts);
            if ($num_matches == 0 || count($cell_parts) != 3) {
                return false;
            }
            if (!isset($cell_parts[1])) {
                return false;
            }
            $column_string = $cell_parts[1];
            $len = strlen($column_string);
            $column = 0;
            $shift = 1;
            for ($j = 0; $j < $len; $j++) {
                $column += (ord($column_string[$j]) - 65) * $shift;
                $shift = 26;
            }
            $rect[$i] =  [(int)$cell_parts[2] - 1, $column];
        }
        return $rect;
    }
    /**
     * Given a pair of coordinates [top-left, bottom-right] in a spreadsheet
     * returns the rectangular portion of the data in the spreadsheet
     * corresponding to these coordinates.
     *
     * @param array $rectangle coordinates to use when getting data out of
     *      spreadsheet
     * @param array $data 2D array of spreadsheet data
     * @return array 2D array corresponding to the portion of the spreadsheet
     *      defined by the rectangle given.
     */
    public function spreadsheetRectangleData($rectangle, $data)
    {
        if (!$rectangle) {
            return $data;
        }
        $out_data = [];
        $eval_data = $data;
        $k = 0;
        for ($i = 0; $i <= $rectangle[1][0]; $i++) {
            $m = 0;
            for ($j = 0; $j <= $rectangle[1][1]; $j++) {
                if (!empty($data[$i][$j]) && $data[$i][$j][0] == '=') {
                    list(, $eval_data[$k][$m]) = $this->evaluateCell(
                        $data[$i][$j], 1, $eval_data);
                } else {
                    $eval_data[$k][$m] = $data[$i][$j] ?? 0;
                }
                if ($i >= $rectangle[0][0] && $j >= $rectangle[0][1]) {
                    $out_data[$k][$m] = $eval_data[$k][$m];
                    $m++;
                }
            }
            if ($i >= $rectangle[0][0]) {
                $k++;
            }
        }
        return $out_data;
    }
    /**
     * Used to evaluate a cell of a CSV spreadsheet. This code
     * runs on the server. In scripts folder there is almost identical
     * Javascript code in spreadsheet.js that runs on the client.
     *
     * @param string $cell_expression a string representing a formula to
     * calculate from a spreadsheet file
     * @param int $location character position in cell_expression to start
     *      evaluating from
     * @param array $data 2D array of spreadsheet rows the cell lives in;
     *      referenced cells (e.g. A1) are resolved against this
     * @param int $operator_pos a position in the array of binary operators
     *     ['*', '/', '+', '-', '%'] (used to compute operators with preference)
     * @return array [new_loc, the value of the cell or the String 'NaN' if
     *      the expression was not evaluatable]
     */
    public function evaluateCell($cell_expression, $location, $data,
        $operator_pos = 0)
    {
        $out = [$location, false];
        $operators = ['%', '-','+', '/', '*'];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        if ($operator_pos >= count($operators)) {
            $out = $this->evaluateFactor($cell_expression, $location, $data);
            return $out;
        }
        $operator = $operators[$operator_pos];
        $left_out = $this->evaluateCell($cell_expression, $location, $data,
            $operator_pos + 1);
        if ($left_out[0] >= strlen($cell_expression)) {
            return $left_out;
        }
        $left_out[0] = $this->skipWhitespace($cell_expression, $left_out[0]);
        if ($cell_expression[$left_out[0]] != $operator) {
            return $left_out;
        }
        $right_out = $this->evaluateCell($cell_expression, $left_out[0] + 1,
            $data, $operator_pos);
        $out[0] = $this->skipWhitespace($cell_expression, $right_out[0]);
        $out[1] = eval("return ". $left_out[1] . $operator . $right_out[1] .
            ";");
        return $out;
    }
    /**
     * Used to evaluate the left hand factor of a binary operator
     * appearing in a CSV spreadsheet cell
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of factor protion that this method needs to evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evaluateFactor($cell_expression, $location, $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        $out = $this->evalFunctionInvocation($cell_expression, $location,
            $data);
        if ($out[1] !== false) {
            return $out;
        }
        $out = $this->evalParenthesizedExpression($cell_expression, $location,
            $data);
        if ($out[1] !== false) {
            return $out;
        }
        $out = $this->evalRangeExpression($cell_expression, $location, $data);
        if ($out[1] !== false) {
            return $out;
        }
        $out = $this->evalNegatedExpression($cell_expression, $location, $data);
        if ($out[1] !== false) {
            return $out;
        }
        $out = $this->evalNumericExpression($cell_expression, $location, $data);
        if ($out[1] !== false) {
            return $out;
        }
        $out = $this->evalCellNameExpression($cell_expression, $location,
            $data);
        if ($out[1] !== false) {
            return $out;
        }
        $out = $this->evalStringExpression($cell_expression, $location, $data);
        if ($out[1] !== false) {
            return $out;
        }
        return $out;
    }
    /**
     * Used to evaluate a function call
     * appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of function call that this method needs to evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evalFunctionInvocation($cell_expression, $location,
        $data)
    {
        $out = [$location, false];
        $rest = substr($cell_expression, $location, C\NAME_LEN);
        $pattern = "/^(avg|ceil|cell|col|exp|floor|log|min|max|pow|" .
            "row|sqrt|sum|username)\\(/i";
        $num_matches = preg_match($pattern, $rest, $matches);
        if (!$num_matches) {
            return $out;
        }
        $location += strlen($matches[0]);
        $arg_values = $this->evaluateArgListExpression($cell_expression,
            $location, $data);
        if ($arg_values[1] == "NaN") {
            return $arg_values;
        }
        if ($arg_values[1] == false) {
            $arg_values[1] = [];
        }
        $num_args = count($arg_values[1]);
        if ((in_array($matches[1], ["ceil", "floor", "exp", "log", "sqrt"])
            &&  $num_args != 1) ||
            (in_array($matches[1], ["cell", "pow"]) && $num_args != 2) ||
            (in_array($matches[1], ["min", "max"]) && $num_args == 0) ||
            (in_array($matches[1], ["col", "row"]) && $num_args != 4) ||
            $cell_expression[$arg_values[0]] != ')') {
            $out[0] = $arg_values[0];
            $out[1] = "NaN";
            return $out;
        }
        $out[0] = $arg_values[0] + 1;
        switch ($matches[1]) {
            case "ceil":
                $out[1] = ceil($arg_values[1][0]);
                break;
            case "cell":
                $args = $arg_values[1];
                $row_col = $this->cellNameAsRowColumn("{$args[1]}{$args[0]}");
                $out[1] = $data[$row_col[0] - 1][$row_col[1]];
                break;
            case "col":
                $args = $arg_values[1];
                $tmp = $this->cellNameAsRowColumn("{$args[2]}0");
                $start = max($tmp[1] - 1, 0);
                $tmp = $this->cellNameAsRowColumn("{$args[3]}0");
                $end = min($tmp[1] - 1, count($data[0]) - 1);
                $out[1] = -1;
                for ($j = $start; $j <= $end; $j++) {
                    if ($data[max($args[1] - 1, 0)][$j] == $args[0]) {
                        $out[1] = $this->letterRepresentation($j);
                        break;
                    }
                }
                break;
            case "exp":
                $out[1] = exp($arg_values[1][0]);
                break;
            case "floor":
                $out[1] = floor($arg_values[1][0]);
            case "log":
                $out[1] = log($arg);
                break;
            case "min":
                $minnands = $arg_values[1];
                $min = $minnands[0];
                for ($i = 0; $i < count($minnands); $i++) {
                    if ($minnands[i] < $min) {
                        $min = $minnands[$i];
                    }
                }
                $out[1] = $min;
                break;
            case "max":
                $maxands = $arg_values[1];
                $max = $maxands[0];
                for ($i = 0; $i < count($maxands); $i++) {
                    if ($maxands[$i] > $max) {
                        $max = $maxands[$i];
                    }
                }
                $out[1] = $max;
                break;
            case "pow":
                $out[1] = pow($arg_values[1][0], $arg_values[1][1]);
                break;
            case "row":
                $args = $arg_values[1];
                $tmp = $this->cellNameAsRowColumn("{$args[1]}0");
                $col = $tmp[1];
                $start = max($args[2] - 1, 0);
                $end = min($args[3] - 1, count($data) - 1);
                $out[1] = -1;
                for ($i = $start; $i <= $end; $i++) {
                    if ($data[i][$col] == $args[0]) {
                        $out[1] = $i + 1;
                        break;
                    }
                }
                break;
            case "sqrt":
                $out[1] = sqrt($arg_values[1][0]);
                break;
            case "sum":
            case "avg":
                $sum = 0;
                $summands = $arg_values[1];
                for ($i = 0; $i < count($summands); $i++) {
                    $summand = $summands[$i];
                    $sum += $summand;
                }
                $out[1] = ($matches[1] == 'sum') ? $sum :
                     $sum/count($summands);
                break;
            case "username":
                $out[1] = (empty($_SESSION['USER_NAME'])) ?
                    'anonymous': $_SESSION['USER_NAME'];
                break;
        }
        return $out;
    }
    /**
     * Used to evaluate a spreadsheet expression surrounded by
     * parentheses appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of parentheses expression that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evalParenthesizedExpression($cell_expression, $location,
        $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        if ($cell_expression[$location] == "(") {
            $out = $this->evaluateCell($cell_expression, $location + 1, $data);
            if ($cell_expression[$out[0]] != ')') {
                $out[1] = "NaN";
                return $out;
            }
            $out[0] = $this->skipWhitespace($cell_expression, $out[0] + 1);
            return $out;
        }
        return $out;
    }
    /**
     * Used to evaluate the expressions in a list of arguments to
     * a function call appearing in a CSV spreadsheet
     * cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of argument list that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, array of arg-list]
     */
    public function evaluateArgListExpression($cell_expression, $location,
        $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        $more_args = true;
        $out = [$location, []];
        while ($more_args) {
            $more_args = false;
            $sub_out = $this->evaluateCell($cell_expression, $location);
            if ($sub_out[1] == 'NaN' || $sub_out[1] === false) {
                return $sub_out;
            }
            if (is_array($sub_out[1])) {
                for ($i = s0; $i < count($sub_out[1]); $i++) {
                    array_push($out[1], $sub_out[1][$i]);
                }
            } else {
                array_push($out[1], $sub_out[1]);
            }
            $location = self.skipWhitespace($cell_expression, $sub_out[0]);
            $out[0] = $location;
            if ($location < strlen($cell_expression) &&
                $cell_expression[$location] == ',') {
                $more_args = true;
                $location++;
            }
        }
        return $out;
    }
    /**
     * Used to convert range expressions, cell_name1:cell_name2 into a
     * sequence of cells, cell_name1, ..., cell_name2 so that it may be
     * used as part of an argument list to a function call
     * appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of range expression that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, array of range cells]
     */
    public function evalRangeExpression($cell_expression, $location, $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        $rest = substr($cell_expression, $location, C\NAME_LEN);
        $num_matches = preg_match("/^([A-Z]+)(\d+)\s*\:\s*([A-Z]+)(\d+)/",
            $rest, $matches);
        $col_flag = (!empty($matches[3]) && $matches[1] == $matches[3]);
        if ($num_matches && ($col_flag || $matches[2] == $matches[4])) {
            $out[0] = $this->skipWhitespace($cell_expression, $location +
                strlen($matches[0]));
            $out[1] = [];
            if ($col_flag) {
                for ($i = intval($matches[2]); $i <= intval($matches[4]);
                    $i++) {
                    $row_col = $this->cellNameAsRowColumn("{$matches[1]}$i");
                    if (!isset($data[$row_col[0] - 1]) ||
                        !isset($data[$row_col[0] - 1][$row_col[1]]) ||
                        $data[$row_col[0] - 1][$row_col[1]] == 'NaN') {
                        $out[1] = "NaN";
                        return $out;
                    }
                    $cell_value = $data[$row_col[0] - 1][$row_col[1]];
                    if (!empty($cell_value[0]) && $cell_value[0] == '=') {
                        $cell_value = $this->evaluateCell($cell_value, 1,
                            $data);
                        $cell_value = $cell_value[1];
                    }
                    array_push($out[1], $cell_value);
                }
            } else {
                $row_col1 = $this->cellNameAsRowColumn("" . $matches[1]
                    . $matches[2]);
                $row_col2 = $this->cellNameAsRowColumn("" . $matches[3]
                    . $matches[4]);
                $i = $row_col1[0];
                for ($j = $row_col1[1]; $j <= $row_col2[1]; $j++) {
                    if (!isset($data[$i - 1]) || !isset($data[$i - 1][$j])
                        || $data[$i - 1][$j] == 'NaN') {
                        $out[1] = "NaN";
                        return $out;
                    }
                    $cell_value = $data[$i - 1][$j];
                    if (!empty($cell_value[0]) && $cell_value[0] == '=') {
                        $cell_value = $this->evaluateCell($cell_value, 1,
                            $data);
                        $cell_value = $cell_value[1];
                    }
                    array_push($out[1], $cell_value);
                }
            }
        }
        return $out;
    }
    /**
     * Used to parse expression of the form: -expr
     * appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of negated expression that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evalNegatedExpression($cell_expression, $location, $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        if ($cell_expression[$location] == "-") {
            $sub_out = $this->evaluateCell($cell_expression, $location + 1);
            if ($sub_out[1] == 'NaN' || $sub_out[1] == false) {
                return $sub_out;
            }
            $out[0] = $this->skipWhitespace($cell_expression, $sub_out[0]);
            $out[1] = - $sub_out[1];
            return $out;
        }
        return $out;
    }
    /**
     * Used to parse a integer or float expression
     * appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of integer or float expression that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evalNumericExpression($cell_expression, $location, $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        $rest = substr($cell_expression, $location, C\NAME_LEN);
        $num_matches = preg_match("/^\-?\d+(\.\d*)?|^\-?\.\d+/", $rest,
            $matches);
        if ($num_matches) {
            $out[0] = $this->skipWhitespace($cell_expression, $location +
                strlen($matches[0]));
            $out[1] = (preg_match('/\./', $matches[0]))? floatval($matches[0]):
                intval($matches[0]);
            return $out;
        }
        return $out;
    }
    /**
     * Used to parse an expression of the form letter sequence followed by
     * number sequence corresponding to the name of a spreadsheet cell
     * appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of cell name expression that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evalCellNameExpression($cell_expression, $location, $data)
    {
        $out = [$location, false];
        if ($location >= strlen($cell_expression)) {
            return $out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        $rest = substr($cell_expression, $location, C\NAME_LEN);
        $value = preg_match("/^[A-Z]+\d+/", $rest, $matches);
        if ($value) {
            $out[0] = $this->skipWhitespace($cell_expression, $location +
                strlen($matches[0]) );
            $row_col = $this->cellNameAsRowColumn(trim($matches[0]));
            $out[1] = $data[$row_col[0] - 1][$row_col[1]];
        }
        return $out;
    }
    /**
     * Used to parse a string expression, "some string" or 'some string',
     * appearing in a CSV spreadsheet cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param int $location start offset in cell expression
     *      of string expression that this method needs to
     *      evaluate
     * @param array $data array of spreadsheet data to be used for
     *      evaluation
     * @return array [new_loc, the value of sub-expression]
     */
    public function evalStringExpression($cell_expression, $location, $data)
    {
        $out = [$location, $false];
        if ($location >= strlen($cell_expression)) {
            return out;
        }
        $location = $this->skipWhitespace($cell_expression, $location);
        $rest = substr($cell_expression, $location, C\NAME_LEN);
        $value = preg_match('/^\"([^\"]*)\"/', $rest, $matches);
        if (!$value) {
            $value = preg_match("/^\'([^\']*)\'/", $rest, $matches);
        }
        if ($value) {
            $out[0] = $this->skipWhitespace($cell_expression, $location +
                strlen($matches[0]));
            $out[1] = $matches[1];
            return $out;
        }
        return $out;
    }
    /**
     * Finds the next non-whitespace location in the provided spreadsheet
     * $cell_expression after position $location
     *
     * @param string $cell_expression cell formula to search in
     * @param int $location start offset to begin search at
     * @return int location of non-whitespace character or the length of
     *      the cell expression
     */
    public function skipWhiteSpace($cell_expression, $location)
    {
        while (isset($cell_expression[$location]) &&
            trim($cell_expression[$location]) == '') {
            $location++;
        }
        return $location;
    }
    /**
     * Converts a string of the form letter sequence followed by
     * number sequence to an array of int's of the form [row, column]
     *
     * @param string $cell_name name of spreadsheet cell to convert
     * @return array [row, column] name corresponds to
     */
    public function cellNameAsRowColumn($cell_name)
    {
        if (!preg_match('/^([A-Z]+)(\d+)$/', $cell_name, $matches)) {
            return null;
        }
        $column_string = $matches[1];
        $len = strlen($column_string);
        $column = 0;
        $old_column = 0;
        $shift = 26;
        for ($i = $len - 1; $i >= 0; $i--) {
            $column = $old_column + (ord($column_string[$i]) - 65);
            $old_column = $shift * $column;
        }
        return [intval($matches[2]), $column];
    }
    /**
     * Converts a decimal number to a base 26 number string using A-Z for 0-25.
     * Used where drawing column headers for spreadsheet
     * @param int $number the value to convert to base 26
     * @return string result of conversion
     */
    public function letterRepresentation($number)
    {
        $pre_letter;
        $out = "";
        do {
            $pre_letter = $number % 26;
            $number = floor($number/26);
            $out .= chr(65 + $pre_letter);
        } while ($number > 25);
        return $out;
    }
    /**
     * Creates a new version of a wiki page in the GROUP_PAGE_HISTORY
     * without changing the page contents, but with an edit reason. This
     * function might be called when a resource has been added to the page
     * so that one can restore to a variant of the page with earlier resource
     * lists.
     *
     * @param int $user_id of user responsible for version being created
     * @param int $page_id of page that new version is being made for
     * @param string $version_reason reason new version is being created;
     *     stored in the GROUP_PAGE_HISTORY EDIT_COMMENT column
     */
    public function versionGroupPage($user_id, $page_id, $version_reason)
    {
        list(, $page_name, $pages) = $this->getPageHistoryList($page_id, 0, 1);
        $pubdate = $pages[0]['PUBDATE'];
        $latest_page_info = $this->getHistoryPage($page_id, $pubdate);
        $page = $latest_page_info["PAGE"];
        $locale_tag =  $latest_page_info["LOCALE_TAG"];
        $group_id = $latest_page_info["GROUP_ID"];
        $this->insertPageHistory($this->db, $page_id, $user_id, $group_id,
            $page_name, $page, $locale_tag, time(), $version_reason);
    }
    /**
     * Called to revert a wiki pages resources to those that existed for
     * the wiki page at a give time
     *
     * @param int $page_id of page that new version is being made for
     * @param int $group_id of group wiki page belongs to
     * @param int $timestamp of when to revert resources back to
     */
    public function revertResources($page_id, $group_id, $timestamp)
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id);
        if (!$folders) {
            return;
        }
        list($folder, $thumb_folder, $base_folder,) = $folders;
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        $vcs->restoreVersion(intval($timestamp) + 1);
    }
    /**
     * Deletes a resource (image, video, etc) associated with a wiki page or
     * group feed post belong to a group
     *
     * @param string $resource_name name of resource to delete
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return bool whether the deletion was successful
     */
    public function deleteResource($resource_name, $group_id, $page_id,
        $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder) = $folders;
        $file_name = "$folder/$resource_name";
        $thumb_name = "$thumb_folder/$resource_name.webp";
        if (file_exists($file_name)) {
            $this->db->unlinkRecursive($file_name);
            $vcs = $this->versionManagerForPage($base_folder, $page_id);
            $vcs->createVersion($file_name);
        }
        if (file_exists($thumb_name)) {
            unlink($thumb_name);
        }
        return true;
    }
    /**
     * Uncompresses a compressed resource associated with a wiki page or
     * group feed post belong to a group
     *
     * @param string $resource_name name of resource to delete
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return bool whether the deletion was successful
     */
    public function extractResource($resource_name, $group_id, $page_id,
        $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder,,$base_folder,) = $folders;
        $file_name = "$folder/$resource_name";
        $zip_extractor = new \ZipArchive();
        if (!$zip_extractor) {
            return false;
        }
        $zip_extractor->open($file_name);
        $zip_extractor->extractTo($folder);
        $zip_extractor->close();
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        $vcs->createVersion($folder);
        return true;
    }
    /**
     * Deletes all resources (image, video, etc) associated with a wiki page
     * belonging to a group.
     *
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return bool whether the deletion was successful
     */
    public function deleteResources($group_id, $page_id, $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder,) = $folders;
        if ($folder && file_exists($folder)) {
            $this->db->unlinkRecursive($folder, false);
            $vcs = $this->versionManagerForPage($base_folder, $page_id);
            $vcs->createVersion($folder);
        }
        if ($thumb_folder && file_exists($thumb_folder)) {
            $this->db->unlinkRecursive($thumb_folder, false);
        }
        return true;
    }
    /**
     * Create a new resource in the given group and page's
     * resource folder/sub_path of the type requests.
     *
     * @param string $resource_type either new-file or new-folder
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return bool whether the deletion was successful
     */
    public function newResource($resource_type, $group_id, $page_id,
        $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder,) = $folders;
        if (!file_exists($folder)) {
            return false;
        }
        $i = 0;
        $base_name = ($resource_type == 'new-text-file') ? "untitled%d.txt" :
            (($resource_type == 'new-csv-file') ? "untitled%d.csv" :
            "untitled_folder%d");
        do {
            $file_name = sprintf($folder . "/" . $base_name, $i);
            $i++;
        } while (file_exists($file_name));
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        if ($resource_type == 'new-text-file') {
            return ($vcs->headPutContents($file_name, "") ==
                VersionManager::SUCCESS);
        }
        if ($resource_type == 'new-csv-file') {
            $csv = ",,,,\n,,,,\n,,,,\n,,,,\n,,,,\n";
            return ($vcs->headPutContents($file_name, $csv) ==
                VersionManager::SUCCESS);
        }
        return ($vcs->headMakeDirectory($file_name) ==
            VersionManager::SUCCESS);
    }
    /**
     * Removes a leftover lock file from a wiki page's resource version
     * history. If an earlier resource operation stopped partway it can
     * leave this lock in place, after which later operations quietly
     * do nothing. Clearing it lets resource operations work again.
     *
     * @param int $group_id group the wiki page belongs to
     * @param int $page_id identifier of the wiki page
     * @param string $sub_path subfolder of the page's resource folder
     *      to act on, if any
     * @return bool true if there is no lock afterward
     */
    public function clearGroupPageResourceLock($group_id, $page_id,
        $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!is_array($folders) || !isset($folders[2])) {
            return false;
        }
        $base_folder = $folders[2];
        $lock_file = $base_folder . "/.archive/LOCK";
        if (file_exists($lock_file)) {
            unlink($lock_file);
        }
        return !file_exists($lock_file);
    }
    /**
     * Saves a fresh version snapshot of a wiki page's resource folder.
     * Useful for getting the version history back in step with what is
     * actually in the folder after manual fixes.
     *
     * @param int $group_id group the wiki page belongs to
     * @param int $page_id identifier of the wiki page
     * @param string $sub_path subfolder of the page's resource folder
     *      to act on, if any
     * @return bool true if a snapshot was saved
     */
    public function versionGroupPageResource($group_id, $page_id,
        $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!is_array($folders) || !isset($folders[2])) {
            return false;
        }
        $base_folder = $folders[2];
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        return $vcs->createVersion() !== VersionManager::LOCK_FAIL;
    }
    /**
     * Sets the text description file of a resource
     *
     * @param string $resource_name name of resource to set description for
     * @param string $resource_description description of the resource
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return bool whether the deletion was successful
     */
    public function setResourceDescription($resource_name,
        $resource_description, $group_id, $page_id, $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder,) = $folders;
        if (!file_exists($thumb_folder)) {
            return false;
        }
        $description_file = $thumb_folder ."/$resource_name.txt";
        return file_put_contents($description_file, $resource_description);
    }
    /**
     * Gets the text description file of a resource
     *
     * @param string $resource_name name of resource to set description for

     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return string the text description of a resource
     */
    public function getResourceDescription($resource_name, $group_id, $page_id,
        $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder,) = $folders;
        if (!file_exists($thumb_folder)) {
            return false;
        }
        $description_file = $thumb_folder ."/$resource_name.txt";
        if (file_exists($description_file)) {
            return file_get_contents($description_file);
        }
        return false;
    }
    /**
     * Renames a resource (image, video, etc) associated with a wiki page
     * belonging to a group.
     *
     * @param string $old_resource_name name of resource before renaming
     * @param string $new_resource_name name of resource after renaming
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to delete resource from
     * @param string $sub_path path to a subfolder of default resource folder
     *      if desired
     * @return bool whether the deletion was successful
     */
    public function renameResource($old_resource_name, $new_resource_name,
        $group_id, $page_id, $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder, ) = $folders;
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        $old_file_name = "$folder/$old_resource_name";
        if (file_exists($old_file_name)) {
            $vcs->headRename($old_file_name, "$folder/$new_resource_name");
        } else {
            return false;
        }
        $old_thumb_paths = glob("$thumb_folder/$old_resource_name.*");
        $thumb_folder_len = strlen("$thumb_folder/");
        foreach ($old_thumb_paths as $old_thumb_path) {
            $old_thumb_name = substr($old_thumb_path, $thumb_folder_len);
            $new_thumb_name = str_replace($old_resource_name,
                $new_resource_name, $old_thumb_name);
            rename($old_thumb_path, "$thumb_folder/$new_thumb_name");
        }
        return true;
    }
    /**
     * Moves a file that has been uploaded via a wiki pages resource form
     * to its correct position in the resources folder so it shows up for
     * that page. For images and video (if FFMPEG configured) thumbs are
     * generated. For video if FFMPEG is configured then a schedule is
     * added to the media_convert folder so that the media_updater can produce
     * mp4 and webm files corresponding to the video file.
     *
     * @param string $tmp_name tmp location that uploaded file initially stored
     *  at
     * @param string $file_name file name of file that has been uploaded
     * @param string $mime_type mime type of uploaded file
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want copy a page resource for
     * @param string $sub_path used to specify sub-folder of default resource
     *      folder to copy to
     * @param string $data string data for file to use instead of filename
     *      (only used in case run non-empty)
     * @param int $timestamp seconds-since-epoch to set on the destination
     *      file's mtime; 0 leaves the system default in place
     * @return bool false if the resource folder couldn't be resolved;
     *      void on the success path
     */
    public function copyFileToGroupPageResource($tmp_name, $file_name,
        $mime_type, $group_id, $page_id, $sub_path = "", $data = "",
        $timestamp = 0)
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id,
            $sub_path, true);
        if (!is_array($folders) || !isset($folders[2])) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder,) = $folders;
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        if (empty($data)) {
            if (!move_uploaded_file($tmp_name, "$folder/$file_name")) {
                return false;
            }
            $file_size = filesize("$folder/$file_name");
            $vcs->createVersion("$folder/$file_name", "", $timestamp);
        } else {
            $file_size = strlen($data);
            $vcs->headPutContents("$folder/$file_name", $data, true,
                $timestamp);
        }
        $this->makeThumbStripExif($file_name, $folder, $thumb_folder,
            $mime_type);
        if (C\nsdefined('FFMPEG') && in_array($mime_type, [
            'video/mp4', 'video/webm', 'video/ogg', 'video/avi',
            'video/quicktime'])) {
            if ($file_size < C\MAX_VIDEO_CONVERT_SIZE) {
                $convert_folder = C\SCHEDULES_DIR . self::CONVERT_FOLDER;
                if (!file_exists($convert_folder) && !mkdir($convert_folder)) {
                    return;
                }
                $num_convert_files = count(glob($convert_folder. "/*.txt"));
                $video_directory =
                    $convert_folder . "/". L\crawlHash($file_name) . time();
                mkdir($video_directory);
                if (!file_exists($video_directory) &&
                    !mkdir($video_directory)) {
                    return;
                }
                if (file_exists($video_directory)) {
                    $split_file = $video_directory . self::SPLIT_FILE;
                    file_put_contents($split_file, "split this!");
                    $file_info = $video_directory . self::FILE_INFO;
                    file_put_contents($file_info,
                        "$page_id\n$folder\n$thumb_folder\n$file_name");
                }
            }
        }
        if (C\nsdefined('WHISPER') && in_array($mime_type, [
            'audio/mpeg', 'audio/mp3', 'audio/wav', 'audio/ogg',
            'audio/mp4', 'audio/webm', 'audio/m4a', 'audio/aiff',
            'audio/basic', 'audio/L24'])) {
            $task_dir = C\SCHEDULES_DIR . "/audio_transcribe";
            if (!file_exists($task_dir)) {
                if (!mkdir($task_dir, 0777, true)) {
                    L\crawlLog("Failed to create audio transcription " .
                        "task directory");
                    return;
                }
            }
            $task_id = L\crawlHash($file_name . $group_id . $page_id . time());
            $task_file = $task_dir . "/" . $task_id . ".txt";
            $status_file = $task_dir . "/" . $task_id . ".status";
            $task_data = [
                'audio_path' => "$folder/$file_name",
                'filename' => $file_name,
                'task_id' => $task_id,
                'group_id' => $group_id,
                'page_id' => $page_id,
                'sub_path' => $sub_path,
                'created_at' => time(),
                'status' => 'pending',
                'retry_count' => 0
            ];
            if (file_put_contents($task_file, serialize($task_data)) !==
                false && file_put_contents($status_file, 'pending') !== false) {
                L\crawlLog("AudioTranscription: Created task for $file_name " .
                          "(ID: $task_id)");
                $transcript_path = "$folder/" .
                    pathinfo($file_name, PATHINFO_FILENAME) . ".txt";
                file_put_contents($transcript_path,
                    "Transcription pending...");
                $vcs->createVersion($transcript_path, "", $timestamp);
            } else {
                L\crawlLog("AudioTranscription: Failed to create task files " .
                    "for $file_name");
            }
        }
    }
    /**
     * Creates a symlink between two resource folders (and their associate
     * thumb folders)
     *
     * @param string $link_group_id group with page that is going to get the
     *  symlink
     * @param string $link_store_id page_id of page that is going to get the
     *  symlink
     * @param string $target_group_id group whose page resources will be
     *   linked to
     * @param string $target_store_id page whose page resources will be
     *   linked to
     */
    public function linkResourceFolders($link_group_id, $link_store_id,
        $target_group_id, $target_store_id)
    {
        $link_folder_names = $this->getGroupPageResourcesFolders(
            $link_group_id, $link_store_id, "", false, false, false);
        $target_folder_names = $this->getGroupPageResourcesFolders(
            $target_group_id, $target_store_id, "", false, false, false);
        for ($i = 0; $i < 2; $i++) {
            if (!file_exists($link_folder_names[$i])) {
                symlink($target_folder_names[$i], $link_folder_names[$i]);
            }
        }
    }
    /**
     * Reads in and returns as a string the contents of a resource that has been
     * associated to a page.
     *
     * @param string $file_name file name of page resource desired
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want copy a page resource for
     * @param string $sub_path subpath with the resource folder that should be
     *  used to look up filename in
     * @param bool $raw if csv file don't content to array of rows
     * @return string desired page resource
     */
    public function getPageResource($file_name, $group_id, $page_id,
        $sub_path = "", $raw = false)
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id,
            $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder) = $folders;
        $contents = file_get_contents("$folder/$file_name");
        $name_parts = pathinfo($file_name);
        if (!$raw && !empty($name_parts['extension']) &&
            $name_parts['extension'] == 'csv') {
            $contents = json_encode(L\parseCsv($contents));
        }
        return $contents;
    }
    /**
     * Saves the string for an page resource that has been updated
     * to the appropriate folder for that wiki page.
     *
     * @param string $file_name file name of page resource desired
     * @param string $resource_data the data to be saved
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want copy a page resource for
     * @param string $sub_path subpath with the resource folder that should be
     *  add to resource path and filename
     * @return bool false if the resource folder couldn't be resolved or
     *  a CSV payload couldn't be decoded; void on the success path
     */
    public function setPageResource($file_name, $resource_data, $group_id,
        $page_id, $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id,
            $sub_path, true);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder) = $folders;
        $vcs = $this->versionManagerForPage($folder, $page_id);
        $name_parts = pathinfo($file_name);
        if (!empty($name_parts['extension']) &&
            $name_parts['extension'] == 'csv') {
            $lines = json_decode($resource_data);
            if (!is_array($lines)) {
                return false;
            }
            $resource_data = "";
            foreach ($lines as $line) {
                $resource_data .= L\arraytoCsv($line) ."\n";
            }
        }
        return ($vcs->headPutContents("$folder/$file_name", $resource_data)
            == VersionManager::SUCCESS);
    }
    /**
     * Makes a thumbnail for files of a type that thumbs can be generated for
     * and strips exif data on jpegs images (only if PHP has exif functions
     * enabled).
     *
     * @param string $file_name name of file to create thumb for
     * @param string $folder the folder in which the file lives
     * @param string $thumb_folder the folder in which to save thumbs
     * @param string $mime_type the mime type of the file
     * @return bool whether a thumb was made or not
     */
    function makeThumbStripExif($file_name, $folder, $thumb_folder,
        $mime_type = "")
    {
        if ($mime_type == "") {
            $mime_type = L\mimeType($file_name, true);
        }
        $image_types = ['image/png', 'image/gif', 'image/jpeg', 'image/webp',
            'image/bmp'];
        $video_types = [
            'video/mp4', 'video/webm', 'video/ogg', 'video/avi',
            'video/quicktime'];
        if (!in_array($mime_type, $image_types) && !in_array($mime_type,
            $video_types) &&
            ($mime_type != 'application/pdf' || !C\nsdefined('IMAGE_MAGICK')) &&
            (($mime_type != 'application/epub+zip' &&
            !in_array($mime_type, ['text/html', 'text/plain'])) ||
            !C\nsdefined('IMAGE_MAGICK') || !C\nsdefined('CALIBRE'))) {
            return false;
        }
        if (in_array($mime_type, $image_types)) {
            $file_string = file_get_contents("$folder/$file_name");
            /* A file can carry an image's name without holding an image:
               one saved under the wrong name, or one that arrived
               damaged. Reading it then says so by handing back false,
               and everything below here needs an image to work on, so
               there is nothing to make a thumbnail of and we stop. The
               site's own error handler is set aside over the read, as it
               is over the exif read below, because it reports what is
               being asked about rather than letting the read answer. */
            set_error_handler(null);
            $image = @imagecreatefromstring($file_string);
            restore_error_handler();
            if ($image === false) {
                return false;
            }
            if (function_exists("exif_read_data")) {
                set_error_handler(null);
                ob_start();
                $exif = @exif_read_data("$folder/$file_name");
                ob_end_clean();
                restore_error_handler();
                if (!empty($exif['Orientation'])) {
                    $image = match ($exif['Orientation']) {
                        8 => imagerotate($image, 90, 0),
                        3 => imagerotate($image, 180, 0),
                        6 => imagerotate($image, -90, 0),
                        default => $image,
                    };
                }
                /* writing the jpeg with strip exif data */
                if (!empty($exif)) {
                    imagejpeg($image, "$folder/$file_name", 100);
                }
            }
            $thumb_string = ImageProcessor::createThumb($image);
            if ($thumb_string) {
                file_put_contents("$thumb_folder/$file_name.webp",
                    $thumb_string);
                clearstatcache("$thumb_folder/$file_name.webp");
            }
            return true;
        }
        if (C\nsdefined('IMAGE_MAGICK') && $mime_type == 'application/pdf') {
            PdfProcessor::createThumb($folder, $thumb_folder, $file_name);
            return true;
        }
        if (C\nsdefined('IMAGE_MAGICK') && C\nsdefined('CALIBRE') &&
            (($mime_type == 'application/epub+zip' ||
            in_array($mime_type, ['text/html', 'text/plain'])))) {
            TextProcessor::createThumb($folder, $thumb_folder, $file_name);
            return true;
        }
        if (C\nsdefined('FFMPEG') && in_array($mime_type, $video_types)) {
            VideoProcessor::createThumbs($folder, $thumb_folder, $file_name);
            return true;
        }
        return false;
    }
    /**
     * Get the names of the resources in the clipboard of $user_id
     *
     * @param int $user_id of user we want to get clipboard resource names for
     * @return array names of resources in clipboard
     */
    public function getClipboardResourceNames($user_id)
    {
        if (($clip_group_id = $this->getPersonalGroupId($user_id)) < 0) {
            return false;
        }
        if (!($clip_page_id = $this->getPageId($clip_group_id,
            C\CLIPBOARD_PAGE_NAME, C\p('DEFAULT_LOCALE')) ) ) {
            $clip_page_id = $this->setPageName($user_id, $clip_group_id,
                C\CLIPBOARD_PAGE_NAME, "page_type=media_list\n\n" .
                WikiParser::END_HEAD_VARS .
                C\CLIPBOARD_PAGE_NAME, C\p('DEFAULT_LOCALE'), "create", "", "");
            if (!$clip_page_id) {
                return false;
            }
        }
        if (!($folders = $this->getGroupPageResourcesFolders($clip_group_id,
            $clip_page_id, "", true))) {
            return false;
        }
        list($clip_folder, ) = $folders;
        if (!is_dir($clip_folder)) {
            return false;
        }
        $clip_folder_len = strlen("$clip_folder/");
        $resource_paths = glob("$clip_folder/*");
        $resource_names = [];
        foreach ($resource_paths as $resource_path) {
            $resource_names[] = substr($resource_path, $clip_folder_len);
        }
        return $resource_names;
    }
    /**
     * Deletes the resources in the clipboard of user with id $user_id
     *
     * @param int $user_id that we want to delete the contents of the clipboard
     *  for
     * @return bool true on successful clear, false when the personal
     *  group, clipboard page, or clip folder couldn't be resolved
     */
    public function emptyClipFolder($user_id)
    {
        if (($clip_group_id = $this->getPersonalGroupId($user_id)) < 0) {
            return false;
        }
        if (!($clip_page_id = $this->getPageId($clip_group_id,
            C\CLIPBOARD_PAGE_NAME, C\p('DEFAULT_LOCALE')) ) ) {
            return false;
        }
        $folders = $this->getGroupPageResourcesFolders($clip_group_id,
            $clip_page_id);
        list($clip_folder, $clip_thumb_folder, $base_clip_folder, ) = $folders;
        if (empty($clip_folder) || !is_dir($clip_folder)) {
            return false;
        }
        $this->db->unlinkRecursive($clip_folder, false);
        if (!empty($clip_thumb_folder) && is_dir($clip_thumb_folder)) {
            $this->db->unlinkRecursive($clip_thumb_folder, false);
        }
        return true;
    }
    /**
     * Used to paste all resources from the user's clip folder
     * to the provided folder
     *
     * @param int $user_id of user whose clip_folder copying to
     * @param int $group_id id of group the file resource belongs to
     * @param int $page_id id of page the file resource belongs to
     * @param string $sub_path path within the page resource folder to
     *  the folder that contains the resource to copy
     * @return bool true if every resource in the clipboard was pasted
     *  successfully, false on any folder-resolution or copy failure
     */
    public function pasteAllClipFolder($user_id, $group_id, $page_id,
        $sub_path = "")
    {
        if (($clip_group_id = $this->getPersonalGroupId($user_id)) < 0) {
            return false;
        }
        if (!($clip_page_id = $this->getPageId($clip_group_id,
            C\CLIPBOARD_PAGE_NAME, C\p('DEFAULT_LOCALE')) ) ) {
            return false;
        }
        if (!($folders = $this->getGroupPageResourcesFolders($clip_group_id,
            $clip_page_id))) {
            return false;
        }
        list($clip_folder, ) = $folders;
        if (!is_dir($clip_folder)) {
            return false;
        }
        $clip_folder_len = strlen("$clip_folder/");
        $resource_paths = glob("$clip_folder/*");
        foreach ($resource_paths as $resource_path) {
            $resource_name = substr($resource_path, $clip_folder_len);
            if (!$this->pasteFromClipFolder($user_id,
                $resource_name, $group_id, $page_id, $sub_path)) {
                return false;
            }
        }
        return true;
    }
    /**
     * Used to paste a resource from the user's clip folder
     * to the provided folder
     *
     * @param int $user_id of user whose clip_folder copying to
     * @param string $resource_name what to copy
     * @param int $group_id id of group the file resource belongs to
     * @param int $page_id id of page the file resource belongs to
     * @param string $sub_path path within the page resource folder to
     *  the folder that contains the resource to copy
     * @return bool true on successful paste, false when the clip
     *  transfer paths could not be resolved
     */
    public function pasteFromClipFolder($user_id,
        $resource_name, $group_id, $page_id, $sub_path = "")
    {
        $transfer_paths = $this->getClipTransferPaths($user_id, $resource_name,
            $group_id, $page_id, $sub_path);
        if (empty($transfer_paths)) {
            return false;
        }
        list($file_path, $thumb_folder, $clip_path,
            $clip_thumb_folder, $base_folder) = $transfer_paths;
        $this->db->copyRecursive($clip_path, $file_path);
        if (is_dir($clip_path)) {
            $thumb_path = "$thumb_folder/$resource_name";
            $clip_thumb_path = "$clip_thumb_folder/$resource_name";
            $this->db->copyRecursive($clip_thumb_path, $thumb_path);
        } else {
            $clip_thumb_resources = glob("$clip_thumb_folder/$resource_name.*");
            $clip_thumb_folder_len = strlen("$clip_thumb_folder/");
            foreach ($clip_thumb_resources as $clip_thumb_resource_path) {
                $clip_thumb_resource_name =  substr($clip_thumb_resource_path,
                    $clip_thumb_folder_len);
                $this->db->copyRecursive($clip_thumb_resource_path,
                    "$thumb_folder/$clip_thumb_resource_name");
            }
        }
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        $vcs->createVersion($file_path);
        return true;
    }
    /**
     * Used to copy a resource in the provided folder to
     * the user's clip folder
     *
     * @param int $user_id of user whose clip_folder copying to
     * @param string $resource_name what to copy
     * @param int $group_id id of group the file resource belongs to
     * @param int $page_id id of page the file resource belongs to
     * @param string $sub_path path within the page resource folder to
     *  the folder that contains the resource to copy
     * @return bool true on successful copy, false when the clip
     *  transfer paths could not be resolved
     */
    public function copyResourceToClipFolder($user_id,
        $resource_name, $group_id, $page_id, $sub_path = "")
    {
        $transfer_paths = $this->getClipTransferPaths($user_id, $resource_name,
            $group_id, $page_id, $sub_path);
        if (empty($transfer_paths)) {
            return false;
        }
        list($file_path, $thumb_folder, $clip_path,
            $clip_thumb_folder,) = $transfer_paths;
        $this->db->copyRecursive($file_path, $clip_path);
        if (is_dir($file_path)) {
            $thumb_path = "$thumb_folder/$resource_name";
            $clip_thumb_path = "$clip_thumb_folder/$resource_name";
            $this->db->copyRecursive($thumb_path, $clip_thumb_path);
        } else {
            $thumb_resources = glob("$thumb_folder/$resource_name.*");
            $thumb_folder_len = strlen("$thumb_folder/");
            foreach ($thumb_resources as $thumb_resource_path) {
                $thumb_resource_name =  substr($thumb_resource_path,
                    $thumb_folder_len);
                $this->db->copyRecursive($thumb_resource_path,
                    "$clip_thumb_folder/$thumb_resource_name");
            }
        }
        return true;
    }
    /**
     * Used to move a resource in the provided folder to
     * the user's clip folder
     *
     * @param int $user_id of user whose clip_folder moving to
     * @param string $resource_name what to move
     * @param int $group_id id of group the file resource belongs to
     * @param int $page_id id of page the file resource belongs to
     * @param string $sub_path path within the page resource folder to
     *  the folder that contains the resource to move
     * @return bool true on successful move, false when the clip
     *  transfer paths could not be resolved
     */
    public function moveResourceToClipFolder($user_id,
        $resource_name, $group_id, $page_id, $sub_path = "")
    {
        $transfer_paths = $this->getClipTransferPaths($user_id, $resource_name,
            $group_id, $page_id, $sub_path);
        if (empty($transfer_paths)) {
            return false;
        }
        list($file_path, $thumb_folder, $clip_path,
            $clip_thumb_folder, $base_folder) = $transfer_paths;
        rename($file_path, $clip_path);
        if (is_dir($file_path)) {
            $thumb_path = "$thumb_folder/$resource_name";
            $clip_thumb_path = "$clip_thumb_folder/$resource_name";
            rename($thumb_path, $clip_thumb_path);
        } else {
            $thumb_resources = glob("$thumb_folder/$resource_name.*");
            $thumb_folder_len = strlen("$thumb_folder/");
            foreach ($thumb_resources as $thumb_resource_path) {
                $thumb_resource_name =  substr($thumb_resource_path,
                    $thumb_folder_len);
                rename($thumb_resource_path,
                    "$clip_thumb_folder/$thumb_resource_name");
            }
        }
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        $vcs->createVersion($file_path);
        return true;
    }
    /**
     * Moves a resource, with any thumbnails it has, from the folder
     * currently being viewed into one of that folder's subfolders, and
     * records the change in the page's version history. This is what
     * happens when someone drags a resource onto a folder: the resource
     * is relocated the same way a clipboard cut-and-paste would move
     * it, without going through the clipboard.
     *
     * @param string $resource_name name of the file or folder to move
     * @param string $target_folder name of the subfolder (inside the
     *      folder being viewed) to move the resource into
     * @param int $group_id group the wiki page belongs to
     * @param int $page_id identifier of the wiki page
     * @param string $sub_path folder currently being viewed, relative
     *      to the page's resource folder
     * @return bool true if the resource was moved
     */
    public function moveResourceToFolder($resource_name, $target_folder,
        $group_id, $page_id, $sub_path = "")
    {
        if ($target_folder === "" || $resource_name === $target_folder) {
            return false;
        }
        $target_sub_path = ($sub_path === "") ? $target_folder :
            trim($sub_path, "/") . "/" . $target_folder;
        return $this->moveResourceBetweenSubPaths($resource_name,
            $sub_path, $target_sub_path, $group_id, $page_id);
    }
    /**
     * Moves a resource, with any thumbnails it has, from the folder
     * currently being viewed to another folder named by its full path
     * under the page's resource folder. This is what happens when
     * someone drags a resource onto a folder in the breadcrumb path,
     * for example to move it up into a parent folder.
     *
     * @param string $resource_name name of the file or folder to move
     * @param string $target_sub_path destination folder relative to
     *      the page's resource folder, empty for the top folder
     * @param int $group_id group the wiki page belongs to
     * @param int $page_id identifier of the wiki page
     * @param string $sub_path folder currently being viewed, relative
     *      to the page's resource folder
     * @return bool true if the resource was moved
     */
    public function moveResourceToSubPath($resource_name,
        $target_sub_path, $group_id, $page_id, $sub_path = "")
    {
        $target_sub_path = trim(str_replace("\\", "/", $target_sub_path),
            "/");
        if (trim($sub_path, "/") === $target_sub_path) {
            return false;
        }
        return $this->moveResourceBetweenSubPaths($resource_name,
            $sub_path, $target_sub_path, $group_id, $page_id);
    }
    /**
     * Moves a resource, with any thumbnails it has, from one folder to
     * another within a wiki page's resource folder, and records the
     * change in the page's version history. Both folders are given by
     * their path under the page's resource folder. Shared by the
     * drag-onto-folder and drag-onto-breadcrumb move actions.
     *
     * @param string $resource_name name of the file or folder to move
     * @param string $source_sub_path folder the resource is currently
     *      in, relative to the page's resource folder
     * @param string $target_sub_path folder to move the resource into,
     *      relative to the page's resource folder
     * @param int $group_id group the wiki page belongs to
     * @param int $page_id identifier of the wiki page
     * @return bool true if the resource was moved
     */
    private function moveResourceBetweenSubPaths($resource_name,
        $source_sub_path, $target_sub_path, $group_id, $page_id)
    {
        if ($resource_name === "") {
            return false;
        }
        $source_folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $source_sub_path);
        if (!is_array($source_folders) || !isset($source_folders[2])) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder, ) = $source_folders;
        $file_path = "$folder/$resource_name";
        if (!file_exists($file_path)) {
            return false;
        }
        $target_folders = $this->getGroupPageResourcesFolders($group_id,
            $page_id, $target_sub_path, true);
        if (!is_array($target_folders) || !isset($target_folders[1])) {
            return false;
        }
        list($dest_folder, $dest_thumb_folder, , ) = $target_folders;
        $dest_path = "$dest_folder/$resource_name";
        if (file_exists($dest_path)) {
            return false;
        }
        rename($file_path, $dest_path);
        if (is_dir($dest_path)) {
            $thumb_path = "$thumb_folder/$resource_name";
            if (file_exists($thumb_path)) {
                rename($thumb_path, "$dest_thumb_folder/$resource_name");
            }
        } else {
            $thumb_resources = glob("$thumb_folder/$resource_name.*");
            $thumb_folder_len = strlen("$thumb_folder/");
            foreach ($thumb_resources as $thumb_resource_path) {
                $thumb_resource_name = substr($thumb_resource_path,
                    $thumb_folder_len);
                rename($thumb_resource_path,
                    "$dest_thumb_folder/$thumb_resource_name");
            }
        }
        $vcs = $this->versionManagerForPage($base_folder, $page_id);
        $vcs->createVersion($folder);
        return true;
    }
    /**
     * Helper method to get file_paths for copying/moving resources to
     * a user's personal clipboard
     *
     * @param int $user_id of user whose clip_folder need paths for
     * @param string $resource_name what to copy/move
     * @param int $group_id id of group the file resource belongs to
     * @param int $page_id id of page the file resource belongs to
     * @param string $sub_path path within the page resource folder to
     *  the folder that contains the resource to copy/move
     * @return mixed array [file_path, thumb_folder, clip_path,
     *  clip_thumb_folder, base_folder] for the source-and-destination
     *  pair, or false on any folder-resolution failure (no personal
     *  group, no clipboard page, missing clip folder)
     */
    private function getClipTransferPaths($user_id,
        $resource_name, $group_id, $page_id, $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id,
            $sub_path);
        if (!$folders) {
            return false;
        }
        list($folder, $thumb_folder, $base_folder, ) = $folders;
        $file_path =  "$folder/$resource_name";
        if (($clip_group_id = $this->getPersonalGroupId($user_id)) < 0) {
            return false;
        }
        if (!($clip_page_id = $this->getPageId($clip_group_id,
            C\CLIPBOARD_PAGE_NAME, C\p('DEFAULT_LOCALE')) ) ) {
            return false;
        }
        $folders = $this->getGroupPageResourcesFolders($clip_group_id,
            $clip_page_id);
        list($clip_folder, $clip_thumb_folder, $base_clip_folder, ) = $folders;
        if (!is_dir($clip_folder)) {
            return false;
        }
        $clip_path = "$clip_folder/$resource_name";
        return [$file_path, $thumb_folder, $clip_path, $clip_thumb_folder,
            $base_folder];
    }
    /**
     * Copies resources associated with a thread post in one group to
     * the resource folders associated with a different group and thread id
     *
     * @param int $from_group_id group resources are coming from
     * @param int $from_thread_id thread post to get resources from
     * @param int $to_group_id group that we are copying to
     * @param int $to_thread_id resource folder of this thread post will be
     *  copied to
     */
    public function copyThreadResources($from_group_id, $from_thread_id,
        $to_group_id, $to_thread_id)
    {
        $from_folders = $this->getGroupPageResourcesFolders($from_group_id,
            "post" . $from_thread_id);
        list($from_folder, $from_thumb_folder, $from_base_folder, ) =
            $from_folders;
        $to_folders = $this->getGroupPageResourcesFolders($to_group_id,
            "post" . $to_thread_id, "", true);
        list($to_folder, $to_thumb_folder, $to_base_folder, ) = $to_folders;
        $this->db->copyRecursive($from_folder, $to_folder);
        $this->db->copyRecursive($from_thumb_folder, $to_thumb_folder);
    }
    /**
     * Gets all the urls of resources belonging to a particular groups wiki
     * page.
     *
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to get page resources for
     * @param string $sub_path additional path beneath the default folder used
     *      for the resource folder
     * @param bool $create if folder doesn't exist whether to create it or not
     * @param string $needs_descriptions_format optional filter controlling
     *      which entries get a needs_description.txt path attached; one of
     *      'files-only', 'folders-only', 'files-and-folders', or null to
     *      skip description-file resolution entirely
     * @return array (url_prefix - prefix to apply to all urls, thum_prefix
     *      prefix to apply to a resource name to get its thumb, list of
     *      resources). Each resource is an pair (name - string
     *      file name of the resource, has_thumb a boolean as to whether the
     *      resource has a thumb)
     */
    public function getGroupPageResourceUrls($group_id, $page_id, $sub_path ="",
        $create = false, $needs_descriptions_format = null)
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id,
            $sub_path, $create);
        if (!$folders) {
            return false;
        }
        $find_files_descriptions = (in_array($needs_descriptions_format,
            ['files-only', 'files-and-folders'])) ? true : false;
        $find_folders_descriptions = (in_array($needs_descriptions_format,
            ['folders-only', 'files-and-folders'])) ? true : false;
        list($folder, $thumb_folder) = $folders;
        $folder_len = strlen($folder) + 1;
        $pre_resources = glob(preg_quote($folder) . "/*");
        $subfolder_counts_file = "";
        if (!empty($thumb_folder)) {
            $subfolder_counts_file = $thumb_folder . "/subfolder_counts.txt";
            $needs_description_file = $thumb_folder . "/needs_description.txt";
        }
        $parent_counts_file = "";
        $pre_thumbs = [];
        $resource_info['folder'] = $folder;
        $subfolder_counts_change = true;
        $subfolder_counts = [];
        $needs_description_string = "";
        if (!empty($thumb_folder)) {
            $resource_info['thumb_folder'] = $thumb_folder;
            $thumb_len = strlen($thumb_folder) + 1;
            $pre_thumbs = glob(preg_quote($thumb_folder) . "/*");
            if (!empty($subfolder_counts_file) &&
                file_exists($subfolder_counts_file)) {
                $subfolder_counts = unserialize(
                    file_get_contents($subfolder_counts_file));
                $subfolder_counts_change = false;
            }
            if (!empty($sub_path) && !empty($thumb_folder)) {
                $thumb_path_parts = explode("/", $thumb_folder);
                array_pop($thumb_path_parts);
                $parent_path = implode("/", $thumb_path_parts);
                $parent_counts_file = $parent_path . "/subfolder_counts.txt";
                $parent_counts = [];
                if (file_exists($subfolder_counts_file)) {
                    $parent_counts = unserialize(
                        file_get_contents($parent_counts_file));
                }
            }
        }
        $thumbs = [];
        foreach ($pre_thumbs as $pre_thumb) {
            $thumb_name = substr($pre_thumb, $thumb_len);
            /* keyed by name so the per-resource has-thumb checks below are
               O(1) lookups; with one to two thousand resources and as many
               thumbnails a linear in_array scan made the whole assembly
               grow with the square of the count and hang the server */
            $thumbs[$thumb_name] = true;
        }
        $resource_info['default_folder_writable'] = false;
        if (is_writable($folder)) {
            $resource_info['default_folder_writable'] = true;
        }
        if (C\REDIRECTS_ON) {
            $url_common = "-/$group_id/$page_id";
            $resource_info['url_prefix'] = C\SHORT_BASE_URL .
                "wd/resources/$url_common";
            $resource_info['thumb_prefix'] = C\SHORT_BASE_URL .
                "wd/thumbs/$url_common";
            $resource_info['athumb_prefix'] = C\SHORT_BASE_URL .
                "wd/athumbs/$url_common";
        } else {
            $resource_info['url_prefix'] = C\SHORT_BASE_URL .
                "?c=resource&amp;a=get&amp;f=resources".
                "&amp;g=$group_id&amp;p=$page_id";
            $resource_info['thumb_prefix'] = $resource_info['url_prefix'] .
                "&amp;t=thumb";
            $resource_info['athumb_prefix'] = $resource_info['url_prefix'] .
                "&amp;t=athumb";
        }
        $sub_path = str_replace("..", "", $sub_path);
        $sub_path = str_replace("/./", "/", $sub_path);
        $sub_path = htmlentities($sub_path);
        if ($sub_path != "" && $sub_path != "/") {
            if (C\REDIRECTS_ON) {
                $resource_info['url_prefix'] .= "/" . urlencode(
                    urlencode($sub_path));
                $resource_info['thumb_prefix'] .= "/" . urlencode(
                    urlencode($sub_path));
                $resource_info['athumb_prefix'] .= "/" . urlencode(
                    urlencode($sub_path));
            } else {
                $resource_info['url_prefix'] .= "&amp;sf=" .
                    urlencode($sub_path);
                $resource_info['thumb_prefix'] .= "&amp;sf=" .
                    urlencode($sub_path);
                $resource_info['athumb_prefix'] .= "&amp;sf=" .
                    urlencode($sub_path);
            }
        }
        $resource_info = array_merge($resource_info,
            $this->getDefaultThumbUrls());
        $resources = [];
        $time = time();
        $missing_thumb = false;
        foreach ($pre_resources as $pre_resource) {
            if (!file_exists($pre_resource)) {
                continue;
            }
            $resource = [];
            $name = substr($pre_resource, $folder_len);
            $resource['name'] = $name;
            $resource['size'] = filesize($pre_resource);
            $resource['modified'] = filemtime($pre_resource);
            $resource['has_thumb'] = false;
            $resource['has_animated_thumb'] = false;
            $resource['is_dir'] = false;
            $resource['is_compressed'] = false;
            $resource['is_writable'] = false;
            $resource['media_type'] = false;
            $might_have_thumb = false;
            $might_have_animated_thumb = false;
            if (is_dir($pre_resource)) {
                $resource['is_dir'] = true;
                if (!empty($subfolder_counts[$pre_resource])) {
                    $resource['num_files'] = $subfolder_counts[$pre_resource];
                } else {
                    $resource['num_files'] = L\countFiles($pre_resource);
                    $subfolder_counts[$pre_resource] = $resource['num_files'];
                    $subfolder_counts_change = true;
                }
                if ($find_folders_descriptions && !empty($thumb_folder) &&
                    !file_exists($thumb_folder . "/" . $name . ".txt")) {
                    $needs_description_string .= "$name\n";
                }
            } else {
                $mime_type = L\mimeType($pre_resource);
                $mime_parts = explode('/', $mime_type);
                if (in_array($mime_parts[0], ['video', 'audio'])) {
                    $resource['media_type'] = $mime_parts[0];
                }
                if (in_array($mime_type, ["application/zip"])) {
                    $resource['is_compressed'] = true;
                }
                if (in_array($mime_parts[0], ['video', 'image']) ||
                    (C\nsdefined("IMAGE_MAGICK") &&
                    in_array($mime_type, ["application/pdf",
                    'application/epub+zip', "text/html", "text/plain"]))) {
                    $might_have_thumb = true;
                    if ($mime_parts[0] == 'video') {
                        $might_have_animated_thumb = true;
                    }
                }
                if ($find_files_descriptions && !empty($thumb_folder) &&
                    !file_exists($thumb_folder . "/" . $name . ".txt")) {
                    $needs_description_string .= "$name\n";
                }
            }
            if (is_writable($pre_resource)) {
                $resource['is_writable'] = true;
            }
            if (isset($thumbs[$name . ".jpg"]) ||
                isset($thumbs[$name . ".webp"])) {
                $resource['has_thumb'] = true;
                if (isset($thumbs[$name . ".gif"])) {
                    $resource['has_animated_thumb'] = true;
                }
            } else if ($thumb_folder && !$resource['is_dir'] &&
                time() < $time + C\PAGE_TIMEOUT/2) {
                $resource['has_thumb'] =
                    $this->makeThumbStripExif($name, $folder, $thumb_folder);
            }
            if ($resource['is_writable'] && ($might_have_animated_thumb &&
                !$resource['has_animated_thumb'])
                || ($might_have_thumb && !$resource['has_thumb'])) {
                $missing_thumb = true;
            }
            $resources[] = $resource;
            if (count($resources) % self::RESOURCE_LIST_YIELD == 0) {
                /* Assembling a large media list reads and stats every file
                   and can take long enough to freeze a single-process
                   server. Give the cooperative loop a turn every so often
                   so other connections keep being served while this runs;
                   outside the cooperative loop this does nothing. */
                L\cooperativeYield();
            }
        }
        if (!empty($parent_counts_file)) {
            $num_resources = count($resources);
            $parent_counts ??= [];
            $parent_counts[$folder] ??= -1;
            if ($parent_counts[$folder] != $num_resources) {
                $parent_counts[$folder] = $num_resources;
                file_put_contents($parent_counts_file,
                    serialize($parent_counts));
            }
        }
        $missing_name = L\crawlHash($folder) . ".txt";
        if ($missing_thumb && !file_exists(self::NEEDS_THUMBS_DIR .
            "/$missing_name")) {
            if (file_exists(self::NEEDS_THUMBS_DIR)) {
                file_put_contents(self::NEEDS_THUMBS_DIR .
                    "/$missing_name", serialize($folders));
            } else {
                L\makePath(self::NEEDS_THUMBS_DIR);
                clearstatcache();
                if (file_exists(self::NEEDS_THUMBS_DIR)) {
                    file_put_contents(self::NEEDS_THUMBS_DIR .
                        "/$missing_name", serialize($folders));
                }
            }
        }
        $resource_info['resources'] = $resources;
        if ($subfolder_counts_change && !empty($thumb_folder)) {
            file_put_contents($subfolder_counts_file,
                serialize($subfolder_counts));
        }
        if (!empty($needs_descriptions_format) &&
            $needs_descriptions_format != 'no-lookup' &&
            !empty($needs_description_file)) {
            // record folder missing descriptions in global needs file
            $needs_description_data = file_exists(self::NEEDS_DESCRIPTION_FILE)?
                file_get_contents(self::NEEDS_DESCRIPTION_FILE) : "";
            if (!str_contains($needs_description_data,
                "$page_id:$thumb_folder")) {
                $needs_description_data .= "$page_id:$thumb_folder\n";
                file_put_contents(self::NEEDS_DESCRIPTION_FILE,
                    $needs_description_data);
            }
            file_put_contents($needs_description_file,
                $needs_description_string);
        }
        return $resource_info;
    }
    /**
     * Returns the filesystem path where the per-page icon for a wiki
     * page lives on disk.
     *
     * @param int $group_id group the page belongs to
     * @param int $page_id wiki page id
     * @return string absolute path to "page_icon.webp.webp" under
     *      the page's thumb folder (the double extension is the
     *      historical on-disk naming)
     */
    public function getGroupPageIconPath($group_id, $page_id)
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id);
        if (!is_array($folders)) {
            return "";
        }
        $thumb_folder = $folders[1] ?? "";
        return $thumb_folder . "/page_icon.webp.webp";
    }
    /**
     * Returns the URL used by clients to fetch the per-page icon for
     * a wiki page, falling back to a default thumb when the icon
     * does not exist on disk.
     *
     * @param string $csrf_token CSRF token to embed in the URL; empty
     *      string substitutes the "[{rtoken}]" placeholder for later
     *      substitution
     * @param int $group_id group the page belongs to
     * @param int $page_id wiki page id
     * @return string fully-qualified URL the browser can request the
     *      icon at (or the default thumb URL when missing)
     */
    public function getGroupPageIconUrl($csrf_token, $group_id, $page_id)
    {
        $page_icon_path = $this->getGroupPageIconPath($group_id, $page_id);
        if (!file_exists($page_icon_path)) {
            $urls = $this->getDefaultThumbUrls();
            return C\SHORT_BASE_URL . $urls['default_thumb'];
        } else {
            $token_string = ($csrf_token) ? C\p('CSRF_TOKEN') . "=" .
                $csrf_token :
                "[{rtoken}]";
            if (C\REDIRECTS_ON) {
                return  C\SHORT_BASE_URL .
                    "wd/thumbs/$token_string/$group_id/$page_id/page_icon.webp";
            } else {
                return C\SHORT_BASE_URL .
                    "?c=resource&amp;a=get&amp;f=resources".
                    "&amp;g=$group_id&amp;p=$page_id" .
                    "&amp;t=thumb&amp;$token_string &amp;n=page_icon.webp";
            }
        }
    }
    /**
     * Returns the relative paths used for fallback page-icon /
     * resource-icon thumbs when a real per-page icon isn't present.
     *
     * @return array map "default_thumb" / "default_editable_thumb" /
     *      "default_folder_thumb" => relative URL under SHORT_BASE_URL
     */
    public function getDefaultThumbUrls()
    {
        return  ['default_thumb' =>  "resources/file-icon.png",
            'default_editable_thumb' => "resources/editable-resource.png",
            'default_folder_thumb' => "resources/folder.png"];
    }
    /**
     * Return the url needed to get a resource of a given resource name that
     * belongs to the provided group and page.
     *
     * @param string $csrf_token a token used to prevent CSRF attacks
     * @param int $group_id group identifier of group wiki page belongs to
     * @param int $page_id identifier for page want to get page resources for
     * @param string $resource_name file name of resource
     * @param string $sub_path additional path beneath the default folder used
     *      for the resource folder
     * @return string relative url to get resource
     */
    public function getGroupPageResourceUrl($csrf_token,
        $group_id, $page_id, $resource_name, $sub_path = "")
    {
        $folders = $this->getGroupPageResourcesFolders($group_id, $page_id);
        if (!$folders) {
            return false;
        }
        list($folder, ) = $folders;
        $resource_name = urlencode($resource_name);
        $sub_path = urlencode($sub_path);
        $token_string = ($csrf_token) ? C\p('CSRF_TOKEN') . "=" . $csrf_token :
            "[{rtoken}]";
        if (C\REDIRECTS_ON) {
            $url = C\SHORT_BASE_URL . "wd/resources/$token_string/$group_id/".
                $page_id;
            if ($sub_path) {
                $url .= "/" . urlencode($sub_path) . "/$resource_name";
            } else {
                $url .= "/$resource_name";
            }
        } else {
            $url = C\SHORT_BASE_URL ."?c=resource&amp;a=get&amp;f=resources".
                "&amp;g=$group_id&amp;p=$page_id&amp;n=". $resource_name;
            if (!empty($sub_path)) {
                $url .= "&amp;sf=". $sub_path;
            }
            $url .= "&amp;$token_string";
        }
        return $url;
    }
    /**
     * Returns the number of non-empty wiki pages a group has (across all
     *      locales)
     * @param int $group_id id of group to return the number of wiki pages for
     * @return int number of wiki pages for that group
     */
    public function getGroupPageCount($group_id)
    {
        $sql = "SELECT COUNT(*) AS TOTAL
            FROM GROUP_PAGE WHERE GROUP_ID = ? AND PAGE <> ''";
        $result = $this->db->execute($sql, [$group_id]);
        $row = $this->db->fetchArray($result);
        $total = ($row) ? $row["TOTAL"] : 0;
        return $total;
    }
    /**
     * Works out the number to give the next issue reported against a git
     * repository wiki page, one more than the largest issue number already
     * used for that page. Issue companion pages are named with the page's
     * name, a dollar sign, and the issue number, so the numbers already in
     * use are read from the names of those companion pages.
     *
     * @param int $group_id id of the group the page belongs to
     * @param string $page_name name of the git repository wiki page
     * @param string $locale_tag language the pages are written for
     * @return int the number to give the next issue, starting at one
     */
    public function nextGitIssueNumber($group_id, $page_name, $locale_tag)
    {
        $db = $this->db;
        $prefix = $page_name . C\GIT_ISSUE_SEPARATOR;
        $sql = "SELECT TITLE FROM GROUP_PAGE WHERE GROUP_ID = ? AND
            LOCALE_TAG = ? AND TITLE LIKE ?";
        $result = $db->execute($sql,
            [$group_id, $locale_tag, $prefix . "%"]);
        $highest = 0;
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                $number = $this->gitIssueNumberFromTitle($row["TITLE"],
                    $prefix);
                if ($number > $highest) {
                    $highest = $number;
                }
            }
        }
        return $highest + 1;
    }
    /**
     * Reads the issue number out of a companion page's name, or reports that
     * the name is not one of this page's issue companions. This double-checks
     * the name because the database search that finds companion pages treats
     * an underscore as matching any character, so it can turn up a name that
     * only looks similar.
     *
     * @param string $title the companion page name to read
     * @param string $prefix the git page's name followed by a dollar sign,
     *      which every one of its companion pages starts with
     * @return int the issue number, or zero when the name is not one of this
     *      page's issue companions
     */
    private function gitIssueNumberFromTitle($title, $prefix)
    {
        if (strncmp($title, $prefix, strlen($prefix)) !== 0) {
            return 0;
        }
        $rest = substr($title, strlen($prefix));
        if ($rest === "" || !ctype_digit($rest)) {
            return 0;
        }
        return (int)$rest;
    }
    /**
     * Reports a new issue against a git repository wiki page by making its
     * hidden companion page. The companion page holds the issue's record as
     * plain data and carries its own discussion thread, which is where the
     * issue is talked over. The chosen issue number is handed back.
     *
     * @param int $user_id id of the person reporting the issue
     * @param int $group_id id of the group the page belongs to
     * @param string $page_name name of the git repository wiki page
     * @param array $record the issue record to store
     * @param string $locale_tag language the page is written for
     * @param string $thread_title title for the issue's discussion thread
     * @param string $thread_description opening text for that thread
     * @param int $post_time when the issue was reported, as a Unix
     *      timestamp, so an imported issue keeps its original date; 0 uses
     *      the current time
     * @param int $issue_number a specific number to give the issue, used
     *      when importing into an empty tracker so numbers line up with the
     *      source; 0 takes the next free number
     * @return int the number given to the new issue
     */
    public function createGitIssue($user_id, $group_id, $page_name, $record,
        $locale_tag, $thread_title, $thread_description, $post_time = 0,
        $issue_number = 0)
    {
        $number = ($issue_number > 0) ? $issue_number :
            $this->nextGitIssueNumber($group_id, $page_name, $locale_tag);
        $issue_page_name = $page_name . C\GIT_ISSUE_SEPARATOR . $number;
        $page = WikiParser::END_HEAD_VARS . "\n" . json_encode($record);
        $pubdate = ($post_time > 0) ? $post_time : -1;
        $this->setPageName($user_id, $group_id, $issue_page_name,
            $page, $locale_tag, "", $thread_title,
            $thread_description, "", false, $pubdate);
        return $number;
    }
    /**
     * Saves a changed issue record back onto its hidden companion page. The
     * page already exists, so this replaces its stored record and leaves the
     * issue's discussion thread untouched.
     *
     * @param int $user_id id of the person making the change
     * @param int $group_id id of the group the page belongs to
     * @param string $page_name name of the git repository wiki page
     * @param int $issue_number which issue to save
     * @param array $record the changed issue record
     * @param string $locale_tag language the page is written for
     */
    public function updateGitIssue($user_id, $group_id, $page_name,
        $issue_number, $record, $locale_tag)
    {
        $issue_page_name = $page_name . C\GIT_ISSUE_SEPARATOR .
            $issue_number;
        $page = WikiParser::END_HEAD_VARS . "\n" . json_encode($record);
        $this->setPageName($user_id, $group_id, $issue_page_name,
            $page, $locale_tag, "", "", "", "", false);
    }
    /**
     * Reads back the record of one issue reported against a git repository
     * wiki page.
     *
     * @param int $group_id id of the group the page belongs to
     * @param string $page_name name of the git repository wiki page
     * @param int $issue_number which issue to read
     * @param string $locale_tag language the page is written for
     * @return array the issue record, or false when there is no such issue
     */
    public function getGitIssue($group_id, $page_name, $issue_number,
        $locale_tag)
    {
        $issue_page_name = $page_name . C\GIT_ISSUE_SEPARATOR .
            $issue_number;
        $info = $this->getPageInfoByName($group_id, $issue_page_name,
            $locale_tag, "edit");
        if (empty($info["PAGE"])) {
            return false;
        }
        $marker = WikiParser::END_HEAD_VARS;
        $position = strpos($info["PAGE"], $marker);
        if ($position === false) {
            return false;
        }
        $body = substr($info["PAGE"], $position + strlen($marker));
        $record = json_decode(trim($body), true);
        return is_array($record) ? $record : false;
    }
    /**
     * Reads the discussion thread that holds an issue's comments: the id of
     * the thread and every reply posted to it, in the order they were made.
     * The thread's opening post is left out because the issue's description
     * stands in for it on the detail page.
     *
     * @param int $group_id id of the group the issue belongs to
     * @param string $page_name name of the git repository wiki page
     * @param int $issue_number which issue on that page
     * @param string $locale_tag language the page is written for
     * @return array a pair of the thread id and a list of its reply posts,
     *      each with an id, a poster id and name, a time, and a body
     */
    public function getGitIssueThread($group_id, $page_name, $issue_number,
        $locale_tag)
    {
        $issue_page_name = $page_name . C\GIT_ISSUE_SEPARATOR .
            $issue_number;
        $info = $this->getPageInfoByName($group_id, $issue_page_name,
            $locale_tag, "edit");
        $thread_id = (int)($info["DISCUSS_THREAD"] ?? 0);
        if ($thread_id <= 0) {
            return [0, []];
        }
        $db = $this->db;
        $sql = "SELECT GI.ID AS ID, GI.USER_ID AS USER_ID,
            GI.PUBDATE AS PUBDATE, GI.DESCRIPTION AS DESCRIPTION,
            U.USER_NAME AS USER_NAME
            FROM GROUP_ITEM GI, USERS U
            WHERE GI.PARENT_ID = ? AND GI.ID <> ? AND GI.USER_ID = U.USER_ID
            ORDER BY GI.PUBDATE ASC";
        $result = $db->execute($sql, [$thread_id, $thread_id]);
        $posts = [];
        while ($result && $row = $db->fetchArray($result)) {
            $posts[] = $row;
        }
        return [$thread_id, $posts];
    }
    /**
     * Removes an issue reported against a git repository page, along with
     * everything hanging off it. An issue is kept as a page of its own
     * beside the repository's page, named for it and numbered, so
     * removing that page is what removes the issue: its record, its
     * history, its discussion, and anything uploaded to it all go with
     * it. There is no undoing this, so whoever asks should be someone
     * allowed to edit the repository's page.
     *
     * @param int $group_id which group the repository page belongs to
     * @param string $page_name name of the repository's own page
     * @param int $issue_number which issue against that page
     * @param string $locale_tag language the page is written for
     * @return bool whether an issue was there to remove
     */
    public function deleteGitIssue($group_id, $page_name, $issue_number,
        $locale_tag)
    {
        $issue_page_name = $page_name . C\GIT_ISSUE_SEPARATOR .
            $issue_number;
        return $this->deleteGroupPage($group_id, $issue_page_name,
            $locale_tag);
    }
    /**
     * Reads back the records of every issue reported against a git
     * repository wiki page, keyed by issue number.
     *
     * @param int $group_id id of the group the page belongs to
     * @param string $page_name name of the git repository wiki page
     * @param string $locale_tag language the page is written for
     * @return array issue number to issue record for every issue on the page
     */
    public function gitIssueList($group_id, $page_name, $locale_tag)
    {
        $db = $this->db;
        $prefix = $page_name . C\GIT_ISSUE_SEPARATOR;
        $sql = "SELECT TITLE, LAST_MODIFIED FROM GROUP_PAGE WHERE
            GROUP_ID = ? AND LOCALE_TAG = ? AND TITLE LIKE ? ORDER BY TITLE";
        $result = $db->execute($sql,
            [$group_id, $locale_tag, $prefix . "%"]);
        $issues = [];
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                $number = $this->gitIssueNumberFromTitle($row["TITLE"],
                    $prefix);
                if ($number <= 0) {
                    continue;
                }
                $record = $this->getGitIssue($group_id, $page_name, $number,
                    $locale_tag);
                if ($record !== false) {
                    $record["last_modified"] = $row["LAST_MODIFIED"];
                    $issues[$number] = $record;
                }
            }
        }
        return $issues;
    }
    /**
     * Returns a list of applicable wiki pages of a group
     *
     * @param int $group_id of group want list of wiki pages for
     * @param string $locale_tag language want wiki page list for
     * @param string $filter string we want to filter wiki page title by
     * @param string $sort one of name_asc, name_desc, date_asc, date_desc
     *      specifying how the page list should be sorted
     * @param string $limit first row we want from the result set
     * @param string $num number of rows we want starting from the first row
     *     in the result set
     * @param string $category optional category name; when non-empty,
     *     restricts results to wiki pages tagged with this category
     *     via the {{category|name}} tag
     * @return array a pair ($total, $pages) where $total is the total number
     *     of rows that could be returned if $limit and $num not present
     *     $pages is an array each of whose elements is an array corresponding
     *     to one TITLE and the first 100 chars out of a wiki page.
     */
    public function getPageList($group_id, $locale_tag, $filter, $sort, $limit,
        $num, $category = "")
    {
        $db = $this->db;
        $filter_parts = preg_split("/\s+/", $filter);
        $sort_map = [ "name_asc" => "LOWER(GP.TITLE) ASC",
            "name_desc" => "LOWER(GP.TITLE) DESC",
            "modified_asc" => "GP.LAST_MODIFIED ASC",
            "" => "GP.LAST_MODIFIED DESC",
        ];
        $sort_dir = $sort_map[$sort] ?? "LOWER(GP.TITLE) ASC";
        $like = "";
        $params = [$group_id, $locale_tag];
        foreach ($filter_parts as $part) {
            if ($part != "") {
                $like .= " AND LOWER(GP.TITLE) LIKE LOWER(?) ";
                $params[] = "%$part%";
            }
        }
        $additional_tables = "";
        $additional_wheres = "";
        if (!empty($category)) {
            $category_id = $this->getCategoryId($category);
            if ($category_id !== false) {
                $additional_tables = ", GROUP_PAGE_LINK GPL";
                $additional_wheres = "AND GP.ID = GPL.FROM_ID
                    AND GPL.LINK_TYPE_ID = '$category_id' ";
            }
        }
        $hide_issue_pages = " AND GP.TITLE NOT LIKE '%" .
            C\GIT_ISSUE_SEPARATOR . "%' ";
        $sql = "SELECT COUNT(*) AS TOTAL
            FROM GROUP_PAGE GP $additional_tables WHERE GP.GROUP_ID = ? AND
            GP.LOCALE_TAG= ? AND LENGTH(GP.PAGE) > 0 $like $additional_wheres
            $hide_issue_pages";
        $result = $db->execute($sql, $params);
        if ($result) {
            $row = $db->fetchArray($result);
        }
        $total = (isset($row) && $row) ? $row["TOTAL"] : 0;
        $pages = [];
        if ($total > 0) {
            $sql = "SELECT GP.ID AS ID, GP.TITLE AS PAGE_NAME,
                GP.PAGE AS DESCRIPTION,
                GP.LAST_MODIFIED AS LAST_MODIFIED
                FROM GROUP_PAGE GP $additional_tables WHERE GP.GROUP_ID = ? AND
                GP.LOCALE_TAG= ? AND LENGTH(GP.PAGE) > 0
                $like $additional_wheres $hide_issue_pages
                ORDER BY $sort_dir ".
                $db->limitOffset($limit, $num);
            $result = $db->execute($sql, $params);
            $i = 0;
            if ($result) {
                $separator_len = strlen(WikiParser::END_HEAD_VARS);
                $stretch = ($_SERVER["MOBILE"]) ? 5 : 9;
                $max_title_len = $stretch * C\NAME_TRUNCATE_LEN;
                while ($pages[$i] = $db->fetchArray($result)) {
                    $head_pos = strpos($pages[$i]['DESCRIPTION'],
                        WikiParser::END_HEAD_VARS);
                    if ($head_pos) {
                        $head = substr($pages[$i]['DESCRIPTION'], 0, $head_pos);
                        $pages[$i]['HEADER'] = [];
                        if (preg_match('/page_type\=(.*)/', $head, $matches)) {
                            $pages[$i]['TYPE'] = $matches[1];
                            if (preg_match('/page_alias\=(.+)/', $head,
                                $matches)) {
                                $pages[$i]['ALIAS'] = $matches[1];
                            } elseif ($pages[$i]['TYPE'] == 'page_alias') {
                                $pages[$i]['TYPE'] = "standard";
                            }
                        } else {
                            $pages[$i]['TYPE'] = "standard";
                        }
                        if ($pages[$i]['TYPE'] == 'page_alias') {
                            $pages[$i]['DESCRIPTION'] =
                                $pages[$i]['ALIAS'];
                        } else {
                            list ($pages[$i]['HEADER'],
                                $pages[$i]['DESCRIPTION']) =
                                WikiParser::parsePageHeadVars(
                                    $pages[$i]['DESCRIPTION'], true);
                        }
                    } else {
                        $pages[$i]['TYPE'] = "standard";
                    }
                    $show_description =
                        empty($pages[$i]['HEADER']['description']) ?
                        $pages[$i]["DESCRIPTION"] :
                        $pages[$i]['HEADER']['description'];
                    $ellipsis = (mb_strlen($show_description) >
                        self::MIN_SNIPPET_LENGTH) ? "..." : "";
                    $pages[$i]['SHOW_DESCRIPTION'] = mb_substr(
                        $show_description, 0,
                        self::MIN_SNIPPET_LENGTH) . $ellipsis;
                    $ellipsis = (mb_strlen($pages[$i]["PAGE_NAME"]) >
                        $max_title_len) ? "..." : "";
                    $pages[$i]["SHOW_PAGE_NAME"] = mb_substr(
                        $pages[$i]["PAGE_NAME"], 0,
                        $max_title_len) . $ellipsis;
                    $i++;
                }
                unset($pages[$i]); //last one will be null
            }
        }
        return [$total, $pages];
    }
    /**
     * Returns the PAGE_RELATIONSHIP id for the given category name.
     *
     * @param string $category category name (e.g. "Help", "Sports")
     * @return mixed numeric ID on hit, false on miss or DB error
     */
    public function getCategoryId($category)
    {
        $db = $this->db;
        $sql = "SELECT ID FROM PAGE_RELATIONSHIP WHERE NAME = ? ".
            $db->limitOffset(1);
        $result = $db->execute($sql, [$category]);
        if ($result) {
            $row = $db->fetchArray($result);
            if ($row) {
                return $row['ID'];
            }
        }
        return false;
    }
    /**
     * Get an array of bots that belong to a group
     *
     * @param string $group_id  the group_id to get bots for
     * @return array of bot rows
     */
    public function getGroupBots($group_id)
    {
        $db = $this->db;
        $sql = "SELECT CB.USER_ID, U.USER_NAME, CB.BOT_TOKEN, CB.CALLBACK_URL".
            " FROM USER_GROUP UG, USERS U, CHAT_BOT CB".
            " WHERE UG.GROUP_ID = ? AND UG.USER_ID = U.USER_ID AND" .
            " CB.USER_ID = UG.USER_ID " .
            $db->limitOffset(C\GROUP_BOT_FOLLOWERS);
        $result = $db->execute($sql, [$group_id]);
        $bots = [];
        while ($bot = $db->fetchArray($result)) {
            $bots[] = $bot;
        }
        return $bots;
    }
    /**
     * Computes the stable group-call identifier for the given set of
     * member ids by sorting the ids and hashing the joined list.
     *
     * @param array $members list of user ids participating in the call
     * @return string the 8-byte crawlHash of the sorted member-id list,
     *      used as a stable identifier for the call across reconnects
     */
    public function getCallGroupID($members)
    {
        asort($members);
        return L\crawlHash(implode("-", $members));
    }
    /**
     * Inserts a new GROUP_CALL row recording that a group call has
     * begun, and resets the per-call event sequence to zero.
     *
     * @param string $call_group_id stable call identifier from
     *      getCallGroupID
     * @param int $initiator_id user id of the caller who started the
     *      call
     * @param int $time Unix timestamp the call started; -1 substitutes
     *      time()
     * @return float|int the call's recorded start time (the resolved
     *      value of $time, useful as a baseline for subsequent
     *      nextCallEvent calls)
     */
    public function createGroupCall($call_group_id, $initiator_id,
        $time = -1)
    {
        $db = $this->db;
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        $time = ($time == -1) ? L\microTimestamp() : $time;
        $sql = "INSERT INTO GROUP_CALL (CALL_HASH_ID, INITIATOR_ID, CALL_TIME)
            VALUES (?, ?, ?)";
        $sql = $db->insertIgnore($sql, $dbinfo);
        $result = $db->execute($sql, [$call_group_id, $initiator_id, $time]);
        return $time;
    }
    /**
     * Removes the GROUP_CALL row identified by $call_hash_id.
     *
     * @param string $call_hash_id hash id of the call session to delete
     */
    public function deleteGroupCall($call_hash_id)
    {
        $db = $this->db;
        $sql = "DELETE FROM GROUP_CALL WHERE CALL_HASH_ID = ?";
        $db->execute($sql, [$call_hash_id]);
    }
    /**
     * Returns the CALL_TIME of the group call identified by
     * $call_hash_id.
     *
     * @param string $call_hash_id hash id of the call session to look up
     * @return string the call's start time as recorded by createGroupCall,
     *  or the literal "-1" when no row matches the given hash
     */
    public function getGroupCallTime($call_hash_id)
    {
        $db = $this->db;
        $sql = "SELECT CALL_TIME FROM GROUP_CALL WHERE CALL_HASH_ID = ?";
        $result = $db->execute($sql, [$call_hash_id]);
        $call_time = "-1";
        if ($result) {
            $row = $db->fetchArray($result);
            if (!empty($row)) {
                $call_time = $row["CALL_TIME"];
            }
        }
        return $call_time;
    }
    /**
     * Returns whether the given group call is currently active. A
     * call is considered active when its CALL_TIME is recent (within
     * MAX_CALL_EVENT_AGE) or, if older, when at least one event has
     * landed since CALL_TIME.
     *
     * @param string $call_hash_id stable call identifier
     * @return mixed true when within the freshness window; otherwise
     *      the int event count since CALL_TIME (truthy when there's
     *      continuing activity)
     */
    public function isActiveCall($call_hash_id)
    {
        $call_time = $this->getGroupCallTime($call_hash_id);
        $now = L\microTimestamp();
        if ((floatval($now) - floatval($call_time)) >
            C\MAX_CALL_EVENT_AGE) {
            return $this->numCallEvents($call_hash_id, $call_time);
        }
        return true;
    }
    /**
     * Counts GROUP_CALL_EVENTS rows newer than $timestamp for the
     * given call. Used by isActiveCall to decide whether a call
     * whose own age exceeded MAX_CALL_EVENT_AGE has continuing
     * activity worth keeping alive.
     *
     * @param string $call_hash_id stable call identifier
     * @param string|float $timestamp microtime cutoff; only events at
     *      or after this timestamp are counted
     * @return int number of recent events (0 when none or on DB error)
     */
    public function numCallEvents($call_hash_id, $timestamp)
    {
        $db = $this->db;
        $sql = "SELECT COUNT(*) AS NUM_EVENTS FROM GROUP_CALL_EVENTS
            WHERE CALL_HASH_ID = ? AND  EVENT_TIME >= ?";
        $result = $db->execute($sql, [$call_hash_id, $timestamp]);
        if ($row = $db->fetchArray($result)) {
            return $row['NUM_EVENTS'];
        }
        return 0;
    }
    /**
     * Returns the next call event (after $timestamp) for the given
     * user in the given call, excluding events sent by that user
     * themselves.
     *
     * @param string $call_group_id stable call identifier
     * @param int $user_id viewer; events with SENDER_ID == $user_id
     *      are skipped
     * @param string|float $timestamp microtime cutoff; only events
     *      strictly newer are returned
     * @return mixed associative array with EVENT_TIME and unserialized
     *      EVENT keys for the next matching row; false when nothing
     *      newer is available or the query fails
     */
    public function nextCallEvent($call_group_id, $user_id, $timestamp)
    {
        $db = $this->db;
        $sql = "SELECT EVENT_TIME, EVENT FROM GROUP_CALL_EVENTS
            WHERE CALL_HASH_ID = ? AND  EVENT_TIME > ? AND SENDER_ID <> ?
            ORDER BY EVENT_TIME ASC ". $db->limitOffset(1);
        $out = false;
        if ($result = $db->execute($sql, [$call_group_id, $timestamp,
            $user_id])) {
            $out = $db->fetchArray($result);
            if ($out) {
                $out['EVENT'] = unserialize($out['EVENT']);
            }
        }
        return $out;
    }
    /**
     * Records a call event (e.g. ICE candidate, SDP offer/answer,
     * mute/unmute) for the given call, by inserting a row into
     * GROUP_CALL_EVENTS.
     *
     * @param string $call_group_id stable call identifier
     * @param int $sender_id user who produced the event
     * @param string $event_type event tag understood by the call UI
     *      (e.g. "offer", "answer", "candidate")
     * @param mixed $data event payload; serialized before being
     *      stored in the EVENT column
     */
    public function addCallEvent($call_group_id, $sender_id, $event_type,
        $data)
    {
        $db = $this->db;
        $dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
            "DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
            "DB_NAME" => C\p('DB_NAME')];
        $time = L\microTimestamp();
        $event = serialize([
            'type' => $event_type,
            'data' => $data,
        ]);
        $sql = "INSERT INTO GROUP_CALL_EVENTS (CALL_HASH_ID, SENDER_ID,
            EVENT_TIME, EVENT) VALUES (?, ?, ?, ?)";
        $sql = $db->insertIgnore($sql, $dbinfo);
        $result = $db->execute($sql, [$call_group_id, $sender_id, $time,
            $event]);
    }
    /**
     * Deletes GROUP_CALL_EVENTS rows older than MAX_CALL_EVENT_AGE
     * for the given call. Called periodically to keep the events
     * table from growing without bound.
     *
     * @param string $call_group_id stable call identifier
     * @param int $time microtime cutoff (-1 substitutes
     *      microTimestamp()); events older than $time minus
     *      MAX_CALL_EVENT_AGE are removed
     */
    public function cullCallEvents($call_group_id, $time = -1)
    {
        $db = $this->db;
        $time = ($time == -1) ? L\microTimestamp() : $time;
        $timestamp = strval(
            floatval($time) - C\MAX_CALL_EVENT_AGE);
        $sql = "DELETE FROM GROUP_CALL_EVENTS WHERE CALL_HASH_ID = ? AND
            EVENT_TIME < ?";
        $result = $db->execute($sql, [$call_group_id, $timestamp]);
    }
    /**
     * Retrieves the translation language setting for a specific contact
     *
     * @param int $user_id id of the user who owns the setting
     * @param int $contact_id id of the contact whose messages may be
     *  translated
     * @return string|false translation language code or false if not set
     */
    public function getTranslationLanguage($user_id, $contact_id)
    {
        $db = $this->db;
        $user_group_id = $this->getPersonalGroupId($user_id);
        $settings_thread_title = "translate-settings-" . $user_id .
            "-" . $contact_id;
        $thread_id = $this->getGroupThreadId($user_group_id, $user_id,
            $settings_thread_title);
        if (!$thread_id) {
            return false;
        }
        $sql = "SELECT DESCRIPTION FROM GROUP_ITEM WHERE PARENT_ID = ?";
        $result = $db->execute($sql, [$thread_id]);
        $row = $db->fetchArray($result);
        if (isset($row['DESCRIPTION'])) {
            $settings = json_decode($row['DESCRIPTION'], true);
            return $settings['TRANSLATE_LANGUAGE'] ?? false;
        }
        return false;
    }
    /**
     * Stores the translation language setting for a specific contact
     *
     * @param int $user_id id of the user who owns the setting
     * @param int $contact_id id of the contact whose messages will be
     *  translated
     * @param string $language language code (e.g., 'es', 'fr') or 'off'
     *  to disable
     */
    public function setTranslationLanguage($user_id, $contact_id, $language)
    {
        $db = $this->db;
        $user_group_id = $this->getPersonalGroupId($user_id);
        $settings_thread_title = "translate-settings-" . $user_id .
            "-" . $contact_id;
        $thread_id = $this->getGroupThreadId($user_group_id, $user_id,
            $settings_thread_title);
        $settings = json_encode(["TRANSLATE_LANGUAGE" => $language]);
        if (!$thread_id) {
            $this->addGroupItem(
                0,
                $user_group_id,
                $user_id,
                $settings_thread_title,
                $settings
            );
        } else {
            $sql = "UPDATE GROUP_ITEM SET DESCRIPTION = ? WHERE PARENT_ID = ?";
            $db->execute($sql, [$settings, $thread_id]);
        }
    }
}
