<?php 
class baseusergroup implements crudable {

	const EVERYONE='Everyone';
	const ADMINISTRATORS='Administrators';

	protected static array $fields=array(
		'name'=>validator::REQUIRED,
		'description'=>validator::REQUIRED,
		'issystem'=>validator::BOOLEAN,
		'cancreateprojects'=>validator::BOOLEAN,
		'cancreateusergroups'=>validator::BOOLEAN,
		'groupvisibility'=>array(validator::REQUIRED,validator::GROUPVISIBILITY),
		'membershipvisibility'=>array(validator::REQUIRED,validator::GROUPMEMBERSHIPVISIBILITY),
		'joining'=>validator::GROUPJOINING,
	);

	private static array $helpTexts=array(
			'name'=>'The name of the group',
			'description'=>'A short description of why the group exists',
			'issystem'=>'A system group\'s membership is managed automatically',
			'cancreateprojects'=>'Whether members of this group can create projects',
			'cancreateusergroups'=>'Whether members of this group can create user groups',
			'groupvisibility'=>'Whether the group can be seen by users, in or outside the group',
			'membershipvisibility'=>'Whether the group\'s membership can be seen by users, in or outside the group',
			'joining'=>'Whether users can join the group',
	);
	
	public static string $defaultSortOrder='issystem DESC, UPPER(name) ASC';
	
	protected static string $adminSelect='
        SELECT SQL_CALC_FOUND_ROWS usergroup.*, (gm.isgroupadmin IS NOT NULL) AS isgroupmember, gm.isgroupadmin AS isgroupadmin 
		FROM usergroup
		LEFT JOIN groupmembership AS gm ON gm.usergroupid=usergroup.id AND gm.userid=:userid
		WHERE 1=1';
	
	protected static string $normalSelect="SELECT SQL_CALC_FOUND_ROWS DISTINCT gm.userid AS userid, usergroup.*, (gm.isgroupadmin IS NOT NULL) AS isgroupmember, gm.isgroupadmin AS isgroupadmin 
		FROM usergroup 
		LEFT JOIN groupmembership AS gm ON (gm.usergroupid=usergroup.id AND gm.userid=:userid) 
		WHERE (
			usergroup.groupvisibility='visible' 
			OR (usergroup.groupvisibility='membersonly' AND gm.isgroupadmin IS NOT NULL) 
			OR (usergroup.groupvisibility='hidden' AND gm.isgroupadmin=1)
		)";
	
	
	public static function getFieldValidations(): array {
		return self::$fields;
	}
	public static function getFieldHelpTexts(): array {
		return self::$helpTexts;
	}
	
	private static function getSelectClause(): string {
		if(session::isAdmin() ){
			return self::$adminSelect;
		}
		return self::$normalSelect;
	}

	/**
	 * @param int $id
	 * @return array|null
	 * @throws BadRequestException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function getById(int $id): ?array {
		$sqlStatement=self::getSelectClause().' AND usergroup.id=:id';
		$params=array(':id'=>$id);
		$params[':userid']=session::getUserId();
		$group=database::queryGetOne($sqlStatement, $params);
		if(empty($group['id'])){ throw new NotFoundException("Either the group does not exist or you do not have permission to see it"); }
		return $group;
	}

	/**
	 * @param string $name
	 * @return array|null
	 * @throws BadRequestException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function getByName(string $name): ?array {
		$sqlStatement=self::getSelectClause().' AND usergroup.name=:name';
		$params=array(':name'=>$name);
		$params[':userid']=session::getUserId();
		$group=database::queryGetOne($sqlStatement, $params);
		if(empty($group['id'])){ throw new NotFoundException("Either the group does not exist or you do not have permission to see it"); }
		return $group;
	}

	/**
	 * @param string $key
	 * @param string $value
	 * @param array $request
	 * @return array|null
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function getByProperty(string $key, string $value, array $request=array()): ?array {
		$keys=array_keys(self::getFieldValidations());
		if(!in_array($key, $keys)){
			throw new BadRequestException('Property '.$key.' not recognised on usergroup');
		}
		$sqlStatement=static::getSelectClause().' AND usergroup.'.$key.'=:val ';
		$params=[':val'=>$value];
		$params[':userid']=session::getUserId();
		$sqlStatement.=database::getOrderClause($request, 'usergroup');
		$sqlStatement.=database::getLimitClause($request);
		return database::queryGetAll($sqlStatement,$params);
	}

	/**
	 * @param array $keyValuePairs
	 * @param array $request
	 * @return array|null
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function getByProperties(array $keyValuePairs, array $request=array()): ?array {
		$sqlStatement=static::getSelectClause();
		$params=[':userid'=>session::getUserId()];
		$keys=array_keys(self::getFieldValidations());
		foreach ($keyValuePairs as $key=>$value){
			if(!in_array($key, $keys)){
				throw new BadRequestException('Property '.$key.' not recognised on usergroup');
			}
			$sqlStatement.=" AND usergroup.$key=:$key ";
			$params[":$key"]=$value;
		}

		$sqlStatement.=database::getOrderClause($request, 'usergroup');
		$sqlStatement.=database::getLimitClause($request);
		return database::queryGetAll($sqlStatement,$params);
	}

	/**
	 * @param array $request
	 * @return array|null
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function getAll(array $request=array()): ?array {
		$sqlStatement=self::getSelectClause();
		$params=array();
		$params[':userid']=session::getUserId();
		$sqlStatement.=database::getOrderClause($request, 'usergroup');
		$sqlStatement.=database::getLimitClause($request);
		return database::queryGetAll($sqlStatement,$params);
	}

    /**
     * @param array $request
     * @return array
     * @throws BadRequestException
     * @throws ForbiddenException
     * @throws NotFoundException
     * @throws ServerException
     */
	public static function create(array $request=array()): array {
		if(!static::canCreate()){
			throw new ForbiddenException('You do not have permission to create usergroups');
		}
		$fieldNames=array_keys(self::$fields);
		$keys=array();
		foreach($request as $k=>$v){
			if(in_array($k, $fieldNames)){
				validator::validate($k, $v, self::$fields[$k]);
				$keys[]=$k;
			}
		}
        /** @noinspection SqlInsertValues */
        $sqlStatement='INSERT INTO usergroup('.
			implode(',',$keys).
			') VALUES(:'.
			implode(',:',$keys) .')';
		$params=array();
		foreach($keys as $k){
			$params[':'.$k]=$request[$k];
		}
		database::query($sqlStatement,$params);
		$createdId=database::getLastInsertId();
		
		//Add group creator to group. Groupmembership::create should make them group admin
		groupmembership::create(array(
				'userid'=>session::getUserId(),
				'usergroupid'=>$createdId
		));
		
		return array('type'=>'usergroup', 'created'=>self::getById($createdId));
	}

	/**
	 * @param int $id
	 * @param array $request
	 * @return array
	 * @throws BadRequestException
	 * @throws ForbiddenException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function update(int $id, array $request=array()): array {
		if(!session::isAdmin() && !self::userisgroupadmin($id)){
			throw new ForbiddenException('Only site and group administrators can update usergroups');
		}
		foreach($request as $k=>$v){
			if(in_array($k, array_keys(self::$fields))){
				validator::validate($k, $v, self::$fields[$k]);
				database::query('UPDATE usergroup SET '.$k.'=:val WHERE id=:id', array(':id'=>$id, ':val'=>$v));
			}
		}
		return array('updated'=>self::getById($id));
	}

	/**
	 * @param int $id
	 * @return array
	 * @throws BadRequestException
	 * @throws ForbiddenException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function delete(int $id): array {
		if(!session::isAdmin()){
			throw new ForbiddenException('Only administrators can delete usergroups');
		}
		$group=self::getById($id);
		if($group['issystem']){
			throw new ForbiddenException('System groups cannot be deleted');
		}
		$sqlStatement='DELETE FROM usergroup WHERE id=:id';
		database::query($sqlStatement, array(':id'=>$id));
		return array('deleted'=>$id);
	}

	/**
	 * @param int|string|array $groupid
	 * @param int|null $userid
	 * @return bool
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function userisingroup(int|string|array $groupid, int $userid=null): bool {
		if(!$userid){ $userid=session::getUserId(); }
		if(empty($userid)){ return false; }
		if(0==(int)$groupid){ $groupid=static::nameToId($groupid); }
		if(is_array($groupid)){ $groupid=$groupid['id']; }
		$sqlStatement='SELECT isgroupadmin FROM groupmembership WHERE usergroupid=:group AND userid=:user';
		$parameters=array(':group'=>$groupid, ':user'=>$userid);
		$result=database::queryGetOne($sqlStatement, $parameters);
		return !empty($result);
	}

	/**
	 * @param int|string|array $groupid
	 * @param int|null $userid
	 * @return bool
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function userisgroupadmin(int|string|array $groupid, int $userid=null): bool {
		if(!$userid){ $userid=session::getUserId(); }
		if(empty($userid)){ return false; }
		if(0==(int)$groupid){ $groupid=static::nameToId($groupid); }
		$sqlStatement='SELECT isgroupadmin FROM groupmembership WHERE usergroupid=:group AND userid=:user AND isgroupadmin=TRUE';
		$parameters=array(':group'=>$groupid, ':user'=>$userid);
		$result=database::queryGetOne($sqlStatement, $parameters);
		return !empty($result);
	}

	/**
	 * @param string $name
	 * @return int|null
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	private static function nameToId(string $name): ?int {
			if(empty($name)){ return null; }
			$sqlStatement='SELECT id FROM usergroup WHERE name=:name';
			$group=database::queryGetOne($sqlStatement, array(':name'=>$name));
			if(empty($group)){
				throw new BadRequestException('No group called '.$name);
			}
			return (int)($group['id']);
	}

	/**
	 * @return array
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	private static function getAdminUserIds(): array {
		return static::getGroupMemberUserIds(self::ADMINISTRATORS);
	}

	/**
	 * @param int|string $id
	 * @return array
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function getGroupMemberUserIds(int|string $id): array {
		if(0==(int)$id){
			$id=static::nameToId($id);
		}
		$sqlStatement='SELECT userid FROM groupmembership WHERE usergroupid=:groupid';
		$result=database::queryGetAll($sqlStatement, array(':groupid'=>$id));
		if(empty($result)){ return array(); }
		return array_column($result['rows'], 'userid');
	}

    /**
     * @return bool
     */
	public static function canCreate(): bool {
		return session::isAdmin() || session::canCreateUsergroups();
	}

    /**
     * @param $id
     * @return bool
     * @throws BadRequestException
     * @throws NotFoundException
	 * @throws ServerException
     */
	public static function canUpdate(int $id): bool {
		self::getById($id); //Throws exception if user can't read
		if(session::isAdmin()){ return true; }
		return usergroup::userisgroupadmin($id);
	}

	/**
	 * @param int $id
	 * @param array $request
	 * @return array
	 * @throws BadRequestException
	 * @throws ForbiddenException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function getmembers(int $id, array $request=[]): array {
		$grp=self::getById($id); //Throws exception if user can't read
		if(!session::isAdmin() /*&& !session::canCreateUsergroups()*/){
			$isInGroup=self::userisingroup($id);
			$isGroupAdmin=self::userisgroupadmin($id);
			if(('hidden'==$grp['membershipvisibility'] && !$isGroupAdmin) || (!$isInGroup && 'membersonly'==$grp['membershipvisibility'])){
				throw new ForbiddenException('You do not have permission to see the membership of this group.');
			}
		}
		$sqlStatement='SELECT user.*, groupmembership.isgroupadmin, groupmembership.id as groupmembershipid FROM user, groupmembership
				WHERE groupmembership.userid=user.id AND groupmembership.usergroupid=:id
				ORDER BY groupmembership.isgroupadmin DESC, LOWER(fullname)';
		$sqlStatement.=database::getLimitClause($request);
		$params=array(':id'=>$id);
		$members=database::queryGetAll($sqlStatement, $params);
		if(self::ADMINISTRATORS==$grp['name']){
			foreach($members['rows'] as &$m){
				$m['isadmin']="1";
			}
		} else if(!empty($members['rows'])){
			$adminIds=static::getAdminUserIds();
			foreach($members['rows'] as &$m){
				if(in_array($m['id'], $adminIds)){
					$m['isadmin']="1";
				} else {
					$m['isadmin']="0";
				}
			}
		}
		return $members;
	}

	/**
	 * @param int $id
	 * @param array $request
	 * @return array
	 * @throws BadRequestException
	 * @throws ForbiddenException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function getnonmembers(int $id, array $request=[]): array {
		if(!session::isAdmin()){
			$grp=static::getById($id); //Throws exception if user can't read
			$isInGroup=static::userisingroup($id);
			$isGroupAdmin=static::userisgroupadmin($id);
			if(('hidden'==$grp['membershipvisibility'] && !$isGroupAdmin) || (!$isInGroup && 'membersonly'==$grp['membershipvisibility'])){
				throw new ForbiddenException('You do not have permission to see the membership of this group.');
			}
		}
		$sqlStatement='SELECT u.*, 0 AS isgroupmember, 0 AS isgroupadmin 
				FROM user AS u LEFT JOIN groupmembership AS gm 
					ON gm.userid=u.id AND gm.usergroupid=:groupid 
				WHERE gm.userid IS NULL
				ORDER BY LOWER(fullname)';
		$sqlStatement.=database::getLimitClause($request);
		return database::queryGetAll($sqlStatement, array(':groupid'=>$id));
	}

	/**
	 * @param int $id
	 * @param array $request
	 * @return array
	 * @throws BadRequestException
	 * @throws NotFoundException
	 * @throws ServerException
	 */
	public static function getpermissions(int $id, array $request=[]): array {
		$sqlStatement='SELECT id,name,isarchived, owner, "project" AS objecttype FROM project WHERE 1=1 ';
//		if(!session::isAdmin()){
			self::getById($id); //Throws exception if the current user can't read
			$readables=session::getReadProjects();
			if(empty($readables)){ $readables=array('0'); }
			$sqlStatement.=' AND id IN('.implode((','), $readables).')';
//		}
		$sqlStatement.=' ORDER BY issystem DESC, name ASC';
		$result=database::queryGetAll($sqlStatement);
		$projects=array();
		foreach($result['rows'] as $r){
			$r['permissions']=array('read'=>false, 'update'=>false, 'create'=>false, 'delete'=>false);
			$projects['proj'.$r['id']]=$r;
		}
		$projectIds=array_column($projects,'id');
		$sqlStatement='SELECT permission.*,project.isarchived FROM permission,project WHERE permission.projectid=project.id 
                            AND usergroupid=:id AND projectid IN('.implode(',',$projectIds).')';
		$perms=database::queryGetAll($sqlStatement, array(':id'=>$id));
		if(!empty($perms)){
			foreach($perms['rows'] as $p){
				$projects['proj'.$p['projectid']]['permissions'][$p['type']]=$p['id'];
			}
		}
		return array(
			'total'=>count($projects),
			'rows'=>array_values($projects)
		);
	}
	
}