<?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\av_processing\AudioConverter;
use seekquarry\yioop\library\av_processing\VideoExtractor;
use seekquarry\yioop\library\MediaConstants;
use seekquarry\yioop\library\version_control\VersionManager;
use seekquarry\yioop\library\wiki\WikiParser;
use seekquarry\yioop\library\processors\TextProcessor;
use seekquarry\yioop\library\processors\ImageProcessor;
use seekquarry\yioop\library\processors\EpubProcessor;
use seekquarry\yioop\library\processors\PdfProcessor;
use seekquarry\yioop\library\processors\VideoProcessor;
use seekquarry\yioop\models\ImpressionModel;
use seekquarry\yioop\library\wiki as LW;

/**
 * FeedModel stores the feed of a group in the database. A feed is a
 * list of threads the members write. Each thread holds posts.
 *
 * It starts a thread, adds a post to one, edits a post, and deletes
 * one. It counts the threads in a group and the posts in a thread. It
 * stores who follows a thread and who subscribes to a group, so they
 * can be told when a post arrives. It stores that a member flagged a
 * post as unwanted, and it stores a moderator's decision about it.
 *
 * This model extends GroupModel, which stores the group a thread
 * belongs to and the key that encrypts a private group's posts.
 *
 * SocialComponent uses this model to draw the group feed screen and to
 * save what someone writes there. GroupfeedElement draws what it
 * reads.
 *
 * @author Chris Pollett
 */
class FeedModel extends GroupModel
{
    /**
     * countGroupThreads 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'];
    }
    /**
     * countThreadPosts 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'];
    }
    /**
     * isThreadSubscribed 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;
    }
    /**
     * setThreadSubscribe 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]);
        }
    }
    /**
     * getGroupThreadId 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;
    }
    /**
     * getGroupItem 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;
    }
    /**
     * getThreadFollowers 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;
    }
    /**
     * getThreadSubscribers 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;
    }
    /**
     * getFollowedThreads 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;
    }
    /**
     * addGroupItem 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;
    }
    /**
     * addGroupItemModeration 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;
    }
    /**
     * updateGroupItem 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]);
    }
    /**
     * deleteConversationThreadRow lets go of the row a conversation hangs from
     * in one person's own message group, which is what takes the conversation
     * off their list of contacts. The other person's row is left alone, so they
     * still see the conversation on theirs.
     * @param int $group_id the person's own message group
     * @param string $thread_title the name the pair's conversation is kept
     *     under
     * @return int how many rows were let go
     */
    public function deleteConversationThreadRow($group_id, $thread_title)
    {
        $db = $this->db;
        /* The row a conversation hangs from carries no words, which is
           what tells it apart from the messages under it. */
        $sql = "DELETE FROM GROUP_ITEM WHERE GROUP_ID=? AND TITLE=?
            AND DESCRIPTION = ''";
        $db->execute($sql, [$group_id, $thread_title]);
        return $db->affectedRows();
    }
    /**
     * deleteGroupItem 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;
    }
    /**
     * flagGroupItem 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;
    }
    /**
     * getParentPostById 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'];
    }
    /**
     * getParentIdOfParentPost 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'];

    }
    /**
     * approveFlaggedPost 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;
    }
    /**
     * deleteFlaggedPost 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;
    }
    /**
     * getGroupItems 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;
    }
    /**
     * getGroupItemCount 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;
    }
    /**
     * getMostRecentGroupPost 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;
    }
    /**
     * getGroupThreadCount 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;
    }
    /**
     * getUserPostCount 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;
    }
    /**
     * getGroupPostCount 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;
    }
    /**
     * excludeGitIssueThreadsClause 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)";
    }
    /**
     * getFeedClipboardId 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");
    }
    /**
     * getMessagesThreadTitle 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);
    }
    /**
     * getThreadPostCount 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;
    }
    /**
     * cullExpiredGroupItems 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);
    }
    /**
     * getPageInfoByThread 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;
    }
    /**
     * copyThreadResources 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);
    }
    /**
     * getGitIssueThread 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];
    }
}
