<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett chris@pollett.org
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models;

use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;

/** For getLocaleTag*/
require_once __DIR__.'/../library/LocaleFunctions.php';
/**
 * This is class is used to handle
 * db results related to Role Administration
 *
 * @author Chris Pollett
 */
class RoleModel extends Model
{
    /**
     * Used to map between search role form variables and database columns
     * @var array
     */
    public $search_table_column_map = ["name"=>"NAME"];
    /** {@inheritDoc}
     *
     * @param mixed $args any additional arguments which should be used to
     *     determine the columns (ignored in this case)
     * @return string a comma separated list of columns suitable for a SQL
     *     query ("NAME" in this case)
     */
    public function selectCallback($args = null)
    {
        return "NAME";
    }
    /**
     * Get the activities  (name, method, id) that a given role can perform
     *
     * @param string $role_id  the rolid_id to get activities for
     * @return array list of activity rows (each having ROLE_ID,
     *      ACTIVITY_ID, METHOD_NAME, ALLOWED_ARGUMENTS, plus the
     *      localized activity name) for the given role
     */
    public function getRoleActivities($role_id)
    {
        $db = $this->db;
        $activities = [];
        $locale_tag = L\getLocaleTag();
        $sql = "SELECT LOCALE_ID FROM LOCALE WHERE LOCALE_TAG = ? " .
            $db->limitOffset(1);
        $result = $db->execute($sql, [$locale_tag]);
        $row = $db->fetchArray($result);
        $locale_id = $row['LOCALE_ID'];
        $sql = "SELECT DISTINCT R.ROLE_ID AS ROLE_ID, ".
            "RA.ACTIVITY_ID AS ACTIVITY_ID, ".
            "A.METHOD_NAME AS METHOD_NAME, ".
            "RA.ALLOWED_ARGUMENTS AS ALLOWED_ARGUMENTS, ".
            "T.IDENTIFIER_STRING AS IDENTIFIER_STRING, ".
            "T.TRANSLATION_ID AS TRANSLATION_ID FROM ".
            "ROLE R, ROLE_ACTIVITY RA, ACTIVITY A, TRANSLATION T ".
            "WHERE  R.ROLE_ID = ? AND ".
            "R.ROLE_ID = RA.ROLE_ID AND T.TRANSLATION_ID = A.TRANSLATION_ID ".
            "AND RA.ACTIVITY_ID = A.ACTIVITY_ID";
        $result = $db->execute($sql, [$role_id]);
        $i = 0;
        $sub_sql = "SELECT TRANSLATION AS ACTIVITY_NAME ".
            "FROM TRANSLATION_LOCALE ".
            "WHERE TRANSLATION_ID=? AND LOCALE_ID=? " . $db->limitOffset(1);
            // maybe do left join at some point
        while ($activities[$i] = $db->fetchArray($result)) {
            $id = $activities[$i]['TRANSLATION_ID'];
            $result_sub =  $db->execute($sub_sql, [$id, $locale_id]);
            $translate = $db->fetchArray($result_sub);
            if ($translate) {
                $activities[$i]['ACTIVITY_NAME'] = $translate['ACTIVITY_NAME'];
            } else {
                $activities[$i]['ACTIVITY_NAME'] =
                    $activities['IDENTIFIER_STRING'];
            }
            $i++;
        }
        unset($activities[$i]); //last one will be null
        return $activities;
    }
    /**
     * Gets all the roles associated with a user id
     *
     * @param string $user_id  the user_id to get roles of
     * @param string $filter to SQL LIKE filter roles
     * @param array $sorts directions on how to sort the columns of the
     *      results format is column_name => direction
     * @param int $limit starting index of first role to return
     * @param int $num number of roles to return
     * @return array of role_ids and their names
     */
    public function getUserRoles($user_id, $filter, $sorts = [], $limit = 0,
        $num = C\NUM_RESULTS_PER_PAGE)
    {
        $db = $this->db;
        $user_id = $db->escapeString($user_id);
        $roles = [];
        $locale_tag = L\getLocaleTag();
        $limit = $db->limitOffset($limit, $num);
        $like = "";
        $param_array = [$user_id];
        if ($filter != "") {
            $like = "AND LOWER(R.NAME) LIKE LOWER(?)";
            $param_array[] = "%".$filter."%";
        }
        $order_by = "";
        if (!empty($sorts)) {
            $sort_fields = ["ROLE_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";
                }
            }
        }
        $sql = "SELECT LOCALE_ID FROM LOCALE ".
            "WHERE LOCALE_TAG = ? ". $db->limitOffset(1);
        $result = $db->execute($sql, [$locale_tag]);
        $row = $db->fetchArray($result);
        $locale_id = $row['LOCALE_ID'];
        $sql = "SELECT UR.ROLE_ID AS ROLE_ID, R.NAME AS ROLE_NAME ".
            " FROM  USER_ROLE UR, ROLE R WHERE UR.USER_ID = ? ".
            " AND R.ROLE_ID = UR.ROLE_ID $like $order_by $limit";
        $result = $db->execute($sql, $param_array);
        while ($row = $db->fetchArray($result)) {
            $roles[] = $row;
        }
        return $roles;
    }
    /**
     * Get a count of the number of groups that a user_id has.
     *
     * @param int $user_id to get roles for
     * @param string $filter to SQL LIKE filter roles
     * @return int number of roles of the filtered type for the user
     */
    public function countUserRoles($user_id, $filter="")
    {
        $db = $this->db;
        $users = [];
        $like = "";
        $param_array = [$user_id];
        if ($filter != "") {
            $like = "AND LOWER(R.NAME) LIKE LOWER(?)";
            $param_array[] = "%".$filter."%";
        }
        $sql = "SELECT COUNT(*) AS NUM ".
            " FROM USER_ROLE UR, ROLE R".
            " WHERE UR.USER_ID = ? AND UR.ROLE_ID = R.ROLE_ID $like";
        $result = $db->execute($sql, $param_array);
        if ($result) {
            $row = $db->fetchArray($result);
        }
        return $row['NUM'];
    }
    /**
     * Check is a user given by $user_id has the role $role_id
     *
     * @param int $user_id user to look up
     * @param int $role_id role to check if member of
     * @return bool whether or not has role
     */
    public function checkUserRole($user_id, $role_id)
    {
        $db = $this->db;
        $params = [$user_id, $role_id];
        $sql = "SELECT COUNT(*) AS NUM FROM USER_ROLE UR WHERE
            UR.USER_ID=? AND UR.ROLE_ID=?";
        $result = $db->execute($sql, $params);
        if (!$row = $db->fetchArray($result)) {
            return false;
        }
        if ($row['NUM'] <= 0) {
            return false;
        }
        return true;
    }
    /**
     * Adds a role to a given user, optionally with an expiry time after
     * which the grant lapses and stops conferring the role's activities.
     * A grant that never lapses uses the FOREVER sentinel, the default,
     * so admin-granted roles stay permanent.
     *
     * @param string $user_id the id of the user to add the role to
     * @param string $role_id the id of the role to add
     * @param int $expires timestamp after which the role lapses, or
     *      FOREVER for a grant that never lapses
     * @param int $will_renew 1 if the account page should spend credits
     *      to renew this grant when it lapses, 0 if the user cancelled it
     *      and it should be removed once it lapses
     */
    public function addUserRole($user_id, $role_id, $expires = C\FOREVER,
        $will_renew = 1)
    {
        $sql = "INSERT INTO USER_ROLE " .
            "(USER_ID, ROLE_ID, EXPIRES, WILL_RENEW) VALUES (?, ?, ?, ?)";
        $this->db->execute($sql,
            [$user_id, $role_id, $expires, $will_renew]);
    }
    /**
     * Sets whether a role a user already holds should keep renewing. Used
     * to cancel a subscription (0, so the grant is removed once it lapses)
     * or to resume one before it lapses (1, at no new charge until the
     * current paid period ends).
     *
     * @param string $user_id the id of the user whose grant to update
     * @param string $role_id the id of the role whose grant to update
     * @param int $will_renew 1 to keep renewing, 0 to stop and let it
     *      lapse
     */
    public function setUserRoleRenew($user_id, $role_id, $will_renew)
    {
        $sql = "UPDATE USER_ROLE SET WILL_RENEW = ? " .
            "WHERE USER_ID = ? AND ROLE_ID = ?";
        $this->db->execute($sql, [$will_renew, $user_id, $role_id]);
    }
    /**
     * Deletes a role from a given user
     *
     * @param string $user_id  the id of the user to delete the role from
     * @param string $role_id  the id of the role to delete
     */
    public function deleteUserRole($user_id, $role_id)
    {
        $sql = "DELETE FROM USER_ROLE WHERE USER_ID=? AND  ROLE_ID=?";
        $this->db->execute($sql, [$user_id, $role_id]);
    }
    /**
     * Moves the expiry of a role a user already holds to a new time.
     * Used when credits are spent to renew a lapsed subscription, pushing
     * its expiry forward one billing period at a time.
     *
     * @param string $user_id the id of the user whose grant to update
     * @param string $role_id the id of the role whose grant to update
     * @param int $expires new timestamp after which the grant lapses
     */
    public function updateUserRoleExpires($user_id, $role_id, $expires)
    {
        $sql = "UPDATE USER_ROLE SET EXPIRES = ? " .
            "WHERE USER_ID = ? AND ROLE_ID = ?";
        $this->db->execute($sql, [$expires, $user_id, $role_id]);
    }
    /**
     * Returns the sellable roles a given user currently holds, each with
     * its cost, charge frequency, and the time the grant expires. Powers
     * the account page's list of a user's subscriptions with their
     * unsubscribe links, and the renewal pass that spends credits to
     * extend lapsed grants.
     *
     * @param int $user_id id of the user whose sellable roles to fetch
     * @return array rows with ROLE_ID, NAME, ROLE_COST, CHARGE_FREQUENCY,
     *      EXPIRES, WILL_RENEW for each sellable role the user holds,
     *      ordered by name
     */
    public function getUserSellableRoles($user_id)
    {
        $db = $this->db;
        $sql = "SELECT R.ROLE_ID AS ROLE_ID, R.NAME AS NAME, " .
            "RL.ROLE_COST AS ROLE_COST, " .
            "RL.CHARGE_FREQUENCY AS CHARGE_FREQUENCY, " .
            "UR.EXPIRES AS EXPIRES, UR.WILL_RENEW AS WILL_RENEW " .
            "FROM USER_ROLE UR, ROLE R, ROLE_LIMITS RL " .
            "WHERE UR.USER_ID = ? AND UR.ROLE_ID = R.ROLE_ID " .
            "AND R.ROLE_ID = RL.ROLE_ID AND RL.ROLE_COST > 0 " .
            "AND RL.CHARGE_FREQUENCY <> 'never' ORDER BY R.NAME";
        $result = $db->execute($sql, [$user_id]);
        $roles = [];
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                $roles[] = $row;
            }
        }
        return $roles;
    }
    /**
     * Returns the roles a user may buy: those whose ROLE_LIMITS row has a
     * cost above zero and a billing frequency other than "never". Each
     * row carries the role's id, name, cost in credits, and charge
     * frequency so the purchase form can show its terms; a frequency of
     * "once" is a one-time permanent buy, the others are subscriptions.
     *
     * @return array rows with ROLE_ID, NAME, ROLE_COST, CHARGE_FREQUENCY
     *      for every sellable role, ordered by name
     */
    public function getSellableRoles()
    {
        $db = $this->db;
        $sql = "SELECT R.ROLE_ID AS ROLE_ID, R.NAME AS NAME, " .
            "RL.ROLE_COST AS ROLE_COST, " .
            "RL.CHARGE_FREQUENCY AS CHARGE_FREQUENCY " .
            "FROM ROLE R, ROLE_LIMITS RL " .
            "WHERE R.ROLE_ID = RL.ROLE_ID AND RL.ROLE_COST > 0 " .
            "AND RL.CHARGE_FREQUENCY <> 'never' ORDER BY R.NAME";
        $result = $db->execute($sql);
        $roles = [];
        if ($result) {
            while ($row = $db->fetchArray($result)) {
                $roles[] = $row;
            }
        }
        return $roles;
    }
    /**
     * Return the role_id and role_name row corresponding to a given rolename
     * @param string $rolename ROLE.NAME to look up (case-insensitive)
     * @return array row in role table corresponding to $rolenames
     */
    public function getRole($rolename)
    {
        $db = $this->db;
        $sql = "SELECT * FROM ROLE WHERE LOWER(NAME) = LOWER(?) " .
            $db->limitOffset(1);
        $result = $db->execute($sql, [$rolename]);
        if (!$result) {
            return false;
        }
        $row = $db->fetchArray($result);
        return $row;
    }
    /**
     * Add a rolename to the database using provided string
     *
     * @param string $role_name  the rolename to be added
     */
    public function addRole($role_name)
    {
        $sql = "INSERT INTO ROLE (NAME) VALUES (?)";
        $this->db->execute($sql, [$role_name]);
    }
    /**
     * Add an allowed activity to an existing role
     *
     * @param string $role_id  the role id of the role to add the activity to
     * @param string $activity_id the id of the activity to add
     * @param string $allowed_arguments $_REQUEST['arg'] values this
     *      role is allowed to use. 'all' means any allowed argument.
     *      Otherwise would be comma separated list of allowed arguments:
     *      For example:
     *      edit, search, ...
     *      Can also use
     *      all, -arg_name1, -arg_name2 ...
     *      to say all arguments except arg_name1, arg_name2...
     */
    public function addActivityRole($role_id, $activity_id,
        $allowed_arguments = "all")
    {
        $sql = "INSERT INTO ROLE_ACTIVITY VALUES (?, ?, ?)";
        $this->db->execute($sql, [$role_id, $activity_id, $allowed_arguments]);
    }
    /**
     * Updates the allowed arguments of an Activity within a Role
     *
     * @param string $role_id  the role id of the role to add the activity to
     * @param string $activity_id the id of the activity to add
     * @param string $allowed_arguments $_REQUEST['arg'] values this
     *      role is allowed to use. 'all' means any allowed argument.
     *      Otherwise would be comma separated list of allowed arguments:
     *      For example:
     *      edit, search, ...
     *      Can also use
     *      all, -arg_name1, -arg_name2 ...
     *      to say all arguments except arg_name1, arg_name2...
     */
    public function updateActivityRoleArguments($role_id, $activity_id,
        $allowed_arguments)
    {
        $sql = "UPDATE ROLE_ACTIVITY SET ALLOWED_ARGUMENTS=? WHERE ROLE_ID = ?".
            " AND ACTIVITY_ID = ?";
        $this->db->execute($sql, [$allowed_arguments, $role_id, $activity_id]);
    }
    /**
     * Delete a role by its roleid
     *
     * @param string $role_id - the roleid of the role to delete
     */
    public function deleteRole($role_id)
    {
        $sql = "DELETE FROM ROLE_ACTIVITY WHERE ROLE_ID=?";
        $this->db->execute($sql, [$role_id]);
        $sql = "DELETE FROM ROLE WHERE ROLE_ID=?";
        $this->db->execute($sql, [$role_id]);
    }
    /**
     * Remove an allowed activity from a role
     *
     * @param string $role_id  the roleid of the role to be modified
     * @param string $activity_id  the activityid of the activity to remove
     */
    public function deleteActivityRole($role_id, $activity_id)
    {
        $sql = "DELETE FROM ROLE_ACTIVITY WHERE ROLE_ID=? AND ACTIVITY_ID=?";
        $this->db->execute($sql, [$role_id, $activity_id]);
    }
    /**
     * Get role id associated with role_name (so role_names better be unique)
     *
     * @param string $role_name to use to look up a role_id
     * @return string  role_id corresponding to the rolename.
     */
    public function getRoleId($role_name)
    {
        return self::getRoleIdWithDb($role_name, $this->db);
    }
    /**
     * Get role id associated with role_name (so role_names better be unique)
     * using a datasource
     *
     * @param string $role_name to use to look up a role_id
     * @param object $db a $database source used to run the role table queries
     *  on
     *
     * @return string  role_id corresponding to the rolename.
     */
    public static function getRoleIdWithDb($role_name, $db)
    {
        $sql = "SELECT R.ROLE_ID AS ROLE_ID FROM ROLE R WHERE R.NAME = ? ";
        $result = $db->execute($sql, [$role_name]);
        if (!$row = $db->fetchArray($result)) {
            return -1;
        }
        return $row['ROLE_ID'];
    }
    /**
     * Returns the per-role group limits for every role so the settings screen
     * can show and edit each role's caps, cost, and charge frequency. A role
     * with no stored limits row comes back with null values, which the screen
     * reads as the defaults (unlimited caps, zero cost, never charged).
     *
     * @return array rows keyed by role id, each holding the role name, the
     *      five cap columns, ROLE_COST, and CHARGE_FREQUENCY
     */
    public function getRoleLimits()
    {
        $db = $this->db;
        $sql = "SELECT R.ROLE_ID AS ROLE_ID, R.NAME AS NAME, " .
            "RL.MAX_GROUPS_OWNED AS MAX_GROUPS_OWNED, " .
            "RL.MAX_GROUP_MEMBERS AS MAX_GROUP_MEMBERS, " .
            "RL.MAX_GROUP_WIKI_PAGES AS MAX_GROUP_WIKI_PAGES, " .
            "RL.MAX_PAGE_RESOURCE_MEMORY AS MAX_PAGE_RESOURCE_MEMORY, " .
            "RL.MAX_GROUP_THREADS AS MAX_GROUP_THREADS, " .
            "RL.MAX_THREAD_RESOURCES AS MAX_THREAD_RESOURCES, " .
            "RL.MAX_THREAD_POSTS AS MAX_THREAD_POSTS, " .
            "RL.ROLE_COST AS ROLE_COST, " .
            "RL.CHARGE_FREQUENCY AS CHARGE_FREQUENCY " .
            "FROM ROLE R LEFT JOIN ROLE_LIMITS RL " .
            "ON R.ROLE_ID = RL.ROLE_ID ORDER BY R.ROLE_ID";
        $result = $db->execute($sql);
        $limits = [];
        while ($row = $db->fetchArray($result)) {
            $limits[$row['ROLE_ID']] = $row;
        }
        return $limits;
    }
    /**
     * Stores a role's group limits, updating the role's row in place, or
     * inserting one if the role has none yet. Each cap is a whole number,
     * with -1 meaning unlimited; ROLE_COST is a whole number of credits and
     * CHARGE_FREQUENCY is how often that cost is charged.
     *
     * @param int $role_id role whose caps are being set
     * @param array $caps cap column name => value for the limit columns,
     *      including ROLE_COST and CHARGE_FREQUENCY
     */
    public function setRoleLimits($role_id, $caps)
    {
        $db = $this->db;
        $result = $db->execute(
            "SELECT ROLE_ID FROM ROLE_LIMITS WHERE ROLE_ID = ?", [$role_id]);
        if ($db->fetchArray($result)) {
            $db->execute("UPDATE ROLE_LIMITS SET MAX_GROUPS_OWNED = ?, " .
                "MAX_GROUP_MEMBERS = ?, MAX_GROUP_WIKI_PAGES = ?, " .
                "MAX_PAGE_RESOURCE_MEMORY = ?, MAX_GROUP_THREADS = ?, " .
                "MAX_THREAD_RESOURCES = ?, MAX_THREAD_POSTS = ?, " .
                "ROLE_COST = ?, CHARGE_FREQUENCY = ? WHERE ROLE_ID = ?",
                [$caps['MAX_GROUPS_OWNED'], $caps['MAX_GROUP_MEMBERS'],
                $caps['MAX_GROUP_WIKI_PAGES'],
                $caps['MAX_PAGE_RESOURCE_MEMORY'],
                $caps['MAX_GROUP_THREADS'], $caps['MAX_THREAD_RESOURCES'],
                $caps['MAX_THREAD_POSTS'], $caps['ROLE_COST'],
                $caps['CHARGE_FREQUENCY'], $role_id]);
        } else {
            $db->execute("INSERT INTO ROLE_LIMITS (ROLE_ID, " .
                "MAX_GROUPS_OWNED, MAX_GROUP_MEMBERS, " .
                "MAX_GROUP_WIKI_PAGES, MAX_PAGE_RESOURCE_MEMORY, " .
                "MAX_GROUP_THREADS, MAX_THREAD_RESOURCES, " .
                "MAX_THREAD_POSTS, ROLE_COST, CHARGE_FREQUENCY) " .
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                [$role_id, $caps['MAX_GROUPS_OWNED'],
                $caps['MAX_GROUP_MEMBERS'], $caps['MAX_GROUP_WIKI_PAGES'],
                $caps['MAX_PAGE_RESOURCE_MEMORY'],
                $caps['MAX_GROUP_THREADS'], $caps['MAX_THREAD_RESOURCES'],
                $caps['MAX_THREAD_POSTS'], $caps['ROLE_COST'],
                $caps['CHARGE_FREQUENCY']]);
        }
    }
    /**
     * Resolves the effective group limits for a user account by taking, for
     * each cap, the most generous value across the roles the account holds.
     * Unlimited (-1) is the most generous, so one unlimited role lifts that
     * cap. A role with no stored limits row counts as unlimited, and an
     * account holding no roles is unlimited on every cap.
     *
     * @param int $user_id account whose effective caps are wanted
     * @return array cap column name => effective value (-1 means unlimited)
     */
    public function getUserGroupLimits($user_id)
    {
        $db = $this->db;
        $columns = ['MAX_GROUPS_OWNED', 'MAX_GROUP_MEMBERS',
            'MAX_GROUP_WIKI_PAGES', 'MAX_PAGE_RESOURCE_MEMORY',
            'MAX_GROUP_THREADS', 'MAX_THREAD_RESOURCES',
            'MAX_THREAD_POSTS'];
        $sql = "SELECT RL.MAX_GROUPS_OWNED AS MAX_GROUPS_OWNED, " .
            "RL.MAX_GROUP_MEMBERS AS MAX_GROUP_MEMBERS, " .
            "RL.MAX_GROUP_WIKI_PAGES AS MAX_GROUP_WIKI_PAGES, " .
            "RL.MAX_PAGE_RESOURCE_MEMORY AS MAX_PAGE_RESOURCE_MEMORY, " .
            "RL.MAX_GROUP_THREADS AS MAX_GROUP_THREADS, " .
            "RL.MAX_THREAD_RESOURCES AS MAX_THREAD_RESOURCES, " .
            "RL.MAX_THREAD_POSTS AS MAX_THREAD_POSTS " .
            "FROM USER_ROLE UR LEFT JOIN ROLE_LIMITS RL " .
            "ON RL.ROLE_ID = UR.ROLE_ID WHERE UR.USER_ID = ?";
        $result = $db->execute($sql, [$user_id]);
        $gathered = [];
        foreach ($columns as $column) {
            $gathered[$column] = [];
        }
        while ($row = $db->fetchArray($result)) {
            foreach ($columns as $column) {
                $value = $row[$column];
                if ($value === null) {
                    $value = -1;
                }
                $gathered[$column][] = (int)$value;
            }
        }
        $effective = [];
        foreach ($columns as $column) {
            $values = $gathered[$column];
            if ($values === [] || in_array(-1, $values, true)) {
                $effective[$column] = -1;
            } else {
                $effective[$column] = max($values);
            }
        }
        return $effective;
    }
}
