<?php
/*
 * Copyright 2007-2017 Charles du Jeu - Abstrium SAS <team (at) pyd.io>
 * This file is part of Pydio.
 *
 * Pydio is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * Pydio 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with Pydio.  If not, see <http://www.gnu.org/licenses/>.
 *
 * The latest code can be found at <https://pydio.com>.
 */
namespace Pydio\Conf\Sql;

use DibiResult;
use Pydio\Access\Core\Filter\ContentFilter;
use Pydio\Access\Core\Model\Repository;
use Pydio\Core\Controller\HTMLWriter;
use Pydio\Core\Exception\DBConnectionException;
use Pydio\Core\Exception\PydioException;
use Pydio\Core\Model\ContextInterface;
use Pydio\Core\Model\RepositoryInterface;
use Pydio\Core\Model\UserInterface;
use Pydio\Conf\Core\AbstractUser;
use Pydio\Conf\Core\AbstractConfDriver;
use Pydio\Conf\Core\AJXP_Role;
use Pydio\Conf\Core\AjxpRole;

use Pydio\Core\Services\RepositoryService;
use Pydio\Core\Services\RolesService;
use Pydio\Core\Services\UsersService;
use Pydio\Core\Utils\DBHelper;
use Pydio\Core\Utils\Vars\InputFilter;
use Pydio\Core\Utils\Vars\OptionsHelper;
use Pydio\Core\Utils\Vars\StatHelper;
use Pydio\Core\Utils\Vars\StringHelper;
use Pydio\Core\PluginFramework\SqlTableProvider;

use \dibi;
use \DibiException;

defined('AJXP_EXEC') or die( 'Access not allowed');

/**
 * Configuration stored in an SQL Database
 * @package AjaXplorer_Plugins
 * @subpackage Conf
 */
class SqlConfDriver extends AbstractConfDriver implements SqlTableProvider
{
    public $sqlDriver = array();

    /**
     * Initialise the driver.
     *
     * Expects options containing a key 'SQL_DRIVER' with constructor values from dibi::connect()
     *
     * Example:
     *        "SQL_DRIVER" => Array(
     *        'driver' => 'sqlite',
     *            'file' => "./server/ajxp.db"
     *        )
     *
     * Example 2:
     *        "SQL_DRIVER" => Array(
     *        'driver' => 'mysql',
     *        'host' => 'localhost',
     *        'username' => 'root',
     *        'password' => '***',
     *        'database' => 'dbname'
     *        )
     *
     * @see AbstractConfDriver#init($options)
     * @param ContextInterface $ctx
     * @param array $options
     * @throws DBConnectionException
     * @throws PydioException
     */
    public function init(ContextInterface $ctx, $options = [])
    {
        parent::init($ctx, $options);
        $this->sqlDriver = OptionsHelper::cleanDibiDriverParameters($options["SQL_DRIVER"]);
        try {
            if(!dibi::isConnected()) {
                dibi::connect($this->sqlDriver);
            }
            if(AJXP_SERVER_DEBUG && AJXP_VERSION_DB != "##DB_VERSION##"){
                try{
                    $res = dibi::query("select MAX(db_build) from [ajxp_version]");
                    if(!empty($res)){
                        $dbVersion = intval($res->fetchSingle());
                        $current = intval(AJXP_VERSION_DB);
                        if($dbVersion > 0 && $dbVersion < $current){
                            // We need to upgrade now!
                            error_log("[Pydio] DB Upgrade Required! You may encounter strange issues. Make sure to manually apply the DB update.");
                            $this->logError("[DB]", "DB Upgrade Required! You may encounter strange issues. Make sure to manually apply the DB update.");
                        }
                    }
                }catch (DibiException $e1){
                    error_log("[Pydio] Error while checking DB version: ".$e1->getMessage());
                    $this->logError("[DB]", "Error while checking DB version: ".$e1->getMessage());
                }
            }
        } catch (DibiException $e) {
            throw new DBConnectionException();
        }
    }

    /**
     * @throws PydioException
     * @throws \Exception
     */
    public function performChecks()
    {
        if(!isSet($this->options)) return;
        $test = OptionsHelper::cleanDibiDriverParameters($this->options["SQL_DRIVER"]);
        if (!count($test)) {
            throw new \Exception("You probably did something wrong! To fix this issue you have to remove the file \"bootstrap.json\" and rename the backup file \"bootstrap.json.bak\" into \"bootstrap.json\" in data/plugins/boot.conf/");
        }
    }

    /**
     * @param string $pluginId
     * @param array $options
     * @throws DibiException
     */
    public function _loadPluginConfig($pluginId, &$options)
    {
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::query("SET bytea_output=escape");
        }
        $res_opts = dibi::query('SELECT * FROM [ajxp_plugin_configs] WHERE [id] = %s', $pluginId);
        $config_row = $res_opts->fetchPairs();
        $confOpt = unserialize($config_row[$pluginId]);
        if (is_array($confOpt)) {
            foreach($confOpt as $key => $value) $options[$key] = $value;
        }
    }

    /**
     *
     * @param String $pluginId
     * @param String $options
     * @throws DibiException
     */
    public function _savePluginConfig($pluginId, $options)
    {
        $res_opts = dibi::query('SELECT COUNT(*) FROM [ajxp_plugin_configs] WHERE [id] = %s', $pluginId);
        if ($res_opts->fetchSingle()) {
            dibi::query('UPDATE [ajxp_plugin_configs] SET [configs] = %bin WHERE [id] = %s', serialize($options), $pluginId);
        } else {
            dibi::query('INSERT INTO [ajxp_plugin_configs] ([id],[configs]) VALUES (%s,%bin)', $pluginId, serialize($options));
        }
    }

    /**
     * Create a Repository object from a Database Result
     *
     * The method expects the following schema:
     * CREATE TABLE ajxp_repo ( uuid VARCHAR(33) PRIMARY KEY,
     *                             path VARCHAR(255),
     *                             display VARCHAR(255),
     *                             accessType VARCHAR(20),
     *                             recycle VARCHAR(255) ,
     *                             bcreate BOOLEAN, -- For some reason 'create' is a reserved keyword
     *                             writeable BOOLEAN,
     *                             enabled BOOLEAN );
     *
     * Additionally, the options are stored in a separate table:
     * CREATE TABLE ajxp_repo_options ( oid INTEGER PRIMARY KEY, uuid VARCHAR(33), name VARCHAR(50), val VARCHAR(255) );
     *
     * I recommend an index to increase performance of uuid lookups:
     * CREATE INDEX ajxp_repo_options_uuid_idx ON ajxp_repo_options ( uuid );
     *
     *
     * @param $result DibiResult of a dibi::query() as array
     * @param array|DibiResult $options_result Result of dibi::query() for options as array
     * @return RepositoryInterface object
     */
    public function repoFromDb($result, $options_result = Array())
    {
        $repo = new Repository($result['id'], $result['display'], $result['accessType']);
        $repo->uuid = $result['uuid'];
        $repo->setOwnerData($result['parent_uuid'], $result['owner_user_id'], $result['child_user_id']);
        $repo->path = $result['path'];
        $repo->create = (bool) $result['bcreate'];
        $repo->writeable = (bool) $result['writeable'];
        $repo->enabled = (bool) $result['enabled'];
        $repo->recycle = "";
        $repo->setSlug($result['slug']);
        if (isSet($result['groupPath']) && !empty($result['groupPath'])) {
            $repo->setGroupPath($result['groupPath']);
        }
        $repo->isTemplate = (bool) $result['isTemplate'];
        $repo->setInferOptionsFromParent((bool) $result['inferOptionsFromParent']);

        foreach ($options_result as $k => $v) {
            if (strpos($v, '$phpserial$') !== false && strpos($v, '$phpserial$') === 0) {
                $v = unserialize(substr($v, strlen('$phpserial$')));
            } else if ($k == "META_SOURCES") {
                $v = unserialize($v);
            }
            $repo->options[$k] = $v;
        }
        if(isSet($repo->options["content_filter"]) && $repo->options["content_filter"] instanceof ContentFilter){
            $repo->setContentFilter($repo->options["content_filter"]);
        }

        return $repo;
    }

    /**
     * Convert a repository object to an array, which will be stored in the database.
     *
     * @param $repository RepositoryInterface
     * @return array containing row values, and another array with the key "options" to be stored as repo options.
     */
    public function repoToArray($repository)
    {

        $repository_row = Array(
                'uuid'                      => $repository->getUniqueId(),
                'parent_uuid'               => $repository->getParentId(),
                'owner_user_id'             => $repository->getOwner(),
                'child_user_id'             => $repository->getUniqueUser(),
                'path'                      => $repository->options['PATH'],
                'display'                   => $repository->getDisplay(),
                'accessType'                => $repository->getAccessType(),
                'recycle'                   => $repository->recycle,
                'bcreate'                   => $repository->getCreate(),
                'writeable'                 => $repository->isWriteable(),
                'options'                   => $repository->options,
                'groupPath'                 => $repository->getGroupPath(),
                'slug'		                => $repository->getSlug(),
                'isTemplate'                => (bool) $repository->isTemplate(),
                'inferOptionsFromParent'    => $repository->getInferOptionsFromParent()
        );

        return $repository_row;
    }


    /**
     * @param array $array
     * @return Repository[]
     */
    protected function initRepoArrayFromDbFetch($array){
        $repositories = array();
        if(!count($array)){
            return $repositories;
        }
        // Load all at once
        $ids = array();
        $allOpts = array();
        foreach($array as $row) {
            $ids[] = $row['uuid'];
            $allOpts[$row['uuid']] = array("ROW" => $row, "OPTIONS" => array());
        }
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::query("SET bytea_output=escape");
        }
        $dbres = dibi::query("SELECT [uuid], [name], [val] FROM [ajxp_repo_options] WHERE [uuid] IN (%s)", $ids);
        foreach($dbres as $row){
            $allOpts[$row['uuid']]["OPTIONS"][$row['name']] = $row['val'];
        }
        foreach($allOpts as $repoId => $repoOptions){
            $repo = $this->repoFromDb($repoOptions["ROW"], $repoOptions["OPTIONS"]);
            $repositories[$repo->getUniqueId()] = $repo;
        }
        return $repositories;
    }

    /**
     * Get a list of repositories
     *
     * The list is an associative array of Array( 'uuid' => [Repository Object] );
     * @param AbstractUser $user
     * @return Repository[]
     * @see AbstractConfDriver#listRepositories()
     */
    public function listRepositories($user = null)
    {
        if ($user != null) {
            return $this->listRepositoriesForRole($user->mergedRole);
        } else {
            $res = dibi::query('SELECT * FROM [ajxp_repo] ORDER BY [display] ASC');
        }
        $all = $res->fetchAll();
        return $this->initRepoArrayFromDbFetch($all);
    }

    /**
     * @param AJXP_Role $role
     * @return RepositoryInterface[]
     * @throws DibiException
     */
    public function listRepositoriesForRole($role){
        $acls = $role->listAcls();
        if(!count($acls)) return array();
        $limitRepositories = array_keys($acls);
        $res = dibi::query('SELECT * FROM [ajxp_repo] WHERE [uuid] IN (%s) ORDER BY [display] ASC', $limitRepositories);
        $all = $res->fetchAll();
        return $this->initRepoArrayFromDbFetch($all);
    }

    /**
     * Returns a list of available repositories (dynamic ones only, not the ones defined in the config file).
     * @param array $criteria
     * @param int $count possible total count
     * @return RepositoryInterface[]
     * @throws DibiException
     */
    public function listRepositoriesWithCriteria($criteria, &$count = null){

        $wheres = array();
        $limit = $groupBy = "";
        $order = "ORDER BY display ASC";

        if(isSet($criteria["role"]) && $criteria["role"] instanceof AJXP_Role){
            return $this->listRepositoriesForRole($criteria["role"]);
        }

        $searchableKeys = array("uuid", "parent_uuid", "owner_user_id", "display", "accessType", "isTemplate", "slug", "groupPath");
        foreach($criteria as $cName => $cValue){
            if(in_array($cName, $searchableKeys) || in_array(substr($cName,1), $searchableKeys)){
                if(is_array($cValue)){
                    if($cName[0] == "!"){
                        $cName = substr($cName, 1);
                        $wheres[] = array("[$cName] NOT IN (%s)", $cValue);
                    }else{
                        $wheres[] = array("[$cName] IN (%s)", $cValue);
                    }
                }else if(strpos($cValue, "regexp:") === 0){
                    $regexp = str_replace("regexp:", "", $cValue);
                    $wheres[] = array("[$cName] ". StringHelper::regexpToLike($regexp), StringHelper::cleanRegexp($regexp));
                }else if ($cValue == AJXP_FILTER_NOT_EMPTY){
                    $wheres[] = array("[$cName] IS NOT NULL");
                }else if ($cValue == AJXP_FILTER_EMPTY){
                    $wheres[] = array("[$cName] IS NULL");
                }else{
                    $type = "%s";
                    if($cName == 'isTemplate') $type = "%b";
                    $wheres[] = array("[$cName] = $type", $cValue);
                }
            }else if($cName == "CURSOR"){
                $limit = $cValue;
            }else if($cName == "ORDERBY"){
                $order = "ORDER BY ".$cValue["KEY"]." ".$cValue["DIR"];
            }else if($cName == "GROUPBY"){
                $groupBy = "GROUP BY ".$cValue;
            }
        }

        if(isset($criteria["CURSOR"])){
            $res = dibi::query("SELECT COUNT(uuid) FROM [ajxp_repo] WHERE %and", $wheres);
            $count = $res->fetchSingle();
        }

        if(!empty($limit) && is_array($limit)){
            $res = dibi::query("SELECT * FROM [ajxp_repo] WHERE %and $groupBy $order %lmt %ofs", $wheres, $limit["LIMIT"], $limit["OFFSET"]);
        }else{
            $res = dibi::query("SELECT * FROM [ajxp_repo] WHERE %and $groupBy $order", $wheres);
        }
        $all = $res->fetchAll();
        return $this->initRepoArrayFromDbFetch($all);

    }


    /**
     * Get repository by Unique ID (a hash calculated from the serialised object).
     *
     * @param String $repositoryId hash uuid
     * @return RepositoryInterface object
     */
    public function getRepositoryById($repositoryId)
    {
        return $this->_loadRepository("uuid", $repositoryId);

    }

    /**
     * Retrieve a Repository given its alias.
     *
     * @param String $repositorySlug
     * @return RepositoryInterface
     */
    public function getRepositoryByAlias($repositorySlug)
    {
        return $this->_loadRepository("slug", $repositorySlug);
    }

    /**
     * @param $slugOrAlias
     * @param $value
     * @return RepositoryInterface|null
     * @throws DibiException
     */
    protected function _loadRepository($slugOrAlias, $value){
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::nativeQuery("SET bytea_output=escape");
        }
        $keyName = ($slugOrAlias=="slug"?"slug":"uuid");
        $res = dibi::query("SELECT * FROM [ajxp_repo],[ajxp_repo_options] WHERE [ajxp_repo].[uuid] = [ajxp_repo_options].[uuid] AND [ajxp_repo].[$keyName] = %s", $value);

        $options = array();
        foreach($res as $row){
            $options[$row->name] = $row->val;
        }
        if(isSet($row)){
            unset($row->name);
            unset($row->val);
            $repository = $this->repoFromDb($row, $options);
            return $repository;
        }
        return null;
    }

    /**
     * @param String $slug
     * @param String|null $repositoryId
     * @return String mixed
     */
    protected function uniquifySlug($slug, $repositoryId = null){

        if(!empty($repositoryId)){
            $res = dibi::query("SELECT [slug],[uuid] FROM [ajxp_repo] WHERE [uuid] != %s AND [slug] = %s", $repositoryId, $slug);
        }else{
            $res = dibi::query("SELECT [slug],[uuid] FROM [ajxp_repo] WHERE [slug] LIKE '".$slug."%'");
        }
        $existingSlugs = $res->fetchPairs();
        $configSlugs = RepositoryService::reservedSlugsFromConfig();
        if(in_array($slug, $configSlugs)){
            $existingSlugs[$slug] = $slug;
        }
        if(!count($existingSlugs)) return $slug;
        $index = 1;
        $base = $slug;
        $slug = $base."-".$index;
        while(isSet($existingSlugs[$slug])){
            $index++;
            $slug = $base."-".$index;
        }

        return $slug;
    }

    /**
     * Store a newly created repository
     *
     * @param Repository $repositoryObject
     * @param Boolean $update
     * @return int -1 if failed
     */
    public function saveRepository($repositoryObject, $update = false)
    {
        try {
            if($update){
                $repositoryObject->setSlug($this->uniquifySlug(
                    $repositoryObject->getSlug(),
                    $repositoryObject->getUniqueId()
                ));
            }else{
                $repositoryObject->setSlug($this->uniquifySlug($repositoryObject->getSlug()));
            }
            $repository_array = $this->repoToArray($repositoryObject);
            $options = $repository_array['options'];
            if($repositoryObject->hasContentFilter()){
                $options["content_filter"] = $repositoryObject->getContentFilter();
            }else if(isSet($options["content_filter"])){
                unset($options["content_filter"]);
            }
            unset($repository_array['options']);
            if (!$update) {
                dibi::query('INSERT INTO [ajxp_repo]', $repository_array);

                foreach ($options as $k => $v) {
                    if (!is_string($v)) {
                        $v = '$phpserial$'.serialize($v);
                    }
                    dibi::query('INSERT INTO [ajxp_repo_options] ([uuid],[name],[val]) VALUES (%s,%s,%bin)', $repositoryObject->getUniqueId(), $k,$v);
                }

            } else {
                dibi::query('DELETE FROM [ajxp_repo] WHERE [uuid] = %s',$repositoryObject->getUniqueId());
                dibi::query('DELETE FROM [ajxp_repo_options] WHERE [uuid] = %s',$repositoryObject->getUniqueId());
                dibi::query('INSERT INTO [ajxp_repo]', $repository_array);
                foreach ($options as $k => $v) {
                    if (!is_string($v)) {
                        $v = '$phpserial$'.serialize($v);
                    }
                    dibi::query('INSERT INTO [ajxp_repo_options] ([uuid],[name],[val]) VALUES (%s,%s,%bin)',$repositoryObject->getUniqueId(),$k,$v);
                }
            }

        } catch (DibiException $e) {
            throw $e;
        }
    }

    /**
     * Delete a repository, given its unique ID.
     *
     * @param String $repositoryId
     * @return int|string
     */
    public function deleteRepository($repositoryId)
    {
        try {
            dibi::query('DELETE FROM [ajxp_repo] WHERE [uuid] = %s', $repositoryId);
            dibi::query('DELETE FROM [ajxp_repo_options] WHERE [uuid] = %s', $repositoryId);
            dibi::query('DELETE FROM [ajxp_user_rights] WHERE [repo_uuid] = %s',$repositoryId);

            switch ($this->sqlDriver["driver"]) {
                case "postgre":
                    dibi::nativeQuery("SET bytea_output=escape");
                    $children_results = dibi::query('SELECT * FROM [ajxp_roles] WHERE [searchable_repositories] LIKE %~like~ GROUP BY [role_id]', '"'.$repositoryId.'";s:');
                    break;
                case "sqlite":
                case "sqlite3":
                    $children_results = dibi::query('SELECT * FROM [ajxp_roles] WHERE [searchable_repositories] LIKE %~like~ GROUP BY [role_id]', '"'.$repositoryId.'";s:');
                    break;
                case "mysqli":
                case "mysql":
                    $children_results = dibi::query('SELECT * FROM [ajxp_roles] WHERE [serial_role] LIKE %~like~ GROUP BY [role_id]', '"'.$repositoryId.'";s:');
                    break;
                default:
                    return "ERROR!, DB driver ". $this->sqlDriver["driver"] ." not supported yet in __FUNCTION__";
            }
            $all = $children_results->fetchAll();
            foreach ($all as $item) {
                $role = unserialize($item["serial_role"]);
                $role->setAcl($repositoryId, "");
                $this->updateRole($role);
            }

        } catch (DibiException $e) {
            $this->logError(__FUNCTION__, $e->getMessage());
            return -1;
        }
        return 1;
    }

    /**
     * @abstract
     * @param $userId
     * @return AbstractUser[]
     */
    public function getUserChildren($userId )
    {
        $ignoreHiddens = "NOT EXISTS (SELECT * FROM [ajxp_user_rights] WHERE [ajxp_user_rights.login]=[ajxp_users.login] AND [ajxp_user_rights.repo_uuid] = 'ajxp.hidden')";
        $children = array();
        $children_results = dibi::query('SELECT [ajxp_users].[login] FROM [ajxp_user_rights],[ajxp_users] WHERE [repo_uuid] = %s AND [rights] = %s AND [ajxp_user_rights].[login] = [ajxp_users].[login] AND '.$ignoreHiddens, "ajxp.parent_user", $userId);
        $all = $children_results->fetchAll();
        foreach ($all as $item) {
            $children[] = UsersService::getUserById($item["login"]);
        }
        return $children;

    }

    /**
     * @abstract
     * @param string $repositoryId
     * @param string $rolePrefix
     * @param bool $splitByType
     * @return array
     */
    public function getRolesForRepository($repositoryId, $rolePrefix = '', $splitByType = false){

        $allRoles = $q = $selectStr = $fromStr = [];

        // Initing select
        $q['select']['role_id'] = '[role_id]';

        if ($splitByType) {
            $q['select']['type'] = 'case when [role_id] LIKE \'AJXP_USR%\' then \'role_user\' when [role_id] LIKE \'AJXP_GRP%\' then \'role_grp\' else \'role_role\' end';
        }

        // Initing from
        $q['from']['a'] = '[ajxp_roles]';

        // Initing where and args
        if(is_numeric($repositoryId)){
            $likeRepositoryId = "i:$repositoryId;s:";
        }else{
            $likeRepositoryId = '"'.$repositoryId.'";s:';
        }
        switch ($this->sqlDriver["driver"]) {
            case "sqlite":
            case "sqlite3":
            case "postgre":
                $q['where'][] = ['[searchable_repositories] LIKE %~like~', $likeRepositoryId];
                break;
            case "mysql":
            case "mysqli":
                $q['where'][] = ['[serial_role] LIKE %~like~', $likeRepositoryId];
                break;
            default:
                return "ERROR!, DB driver ". $this->sqlDriver["driver"] ." not supported yet in __FUNCTION__";
        }

        if(!empty($rolePrefix)){
            $q['where'][] = ['[role_id] LIKE %like~', $rolePrefix];
        }

        // Initing group by
        $q['groupBy'][] = '[role_id]';

        // Building the request from $q

        // Building select
        $reqStr = 'SELECT';
        foreach ($q['select'] as $k => $v) {
            $selectStr[] = $v.' as '.$k;
        }
        $reqStr .= ' ';
        $reqStr .= join(', ', $selectStr);
        $reqStr .= ' ';

        // Building from
        $reqStr .= 'FROM';
        foreach ($q['from'] as $k => $v) {
            $fromStr[] = $v.' '.$k;
        }
        $reqStr .= ' ';
        $reqStr .= join(', ', $fromStr);
        $reqStr .= ' ';

        // Building where
        if (!empty($q['where'])) {
            $reqStr .= 'WHERE %and';
            $reqStr .= ' ';
        }

        // Building group by
        if (!empty($q['groupBy'])) {
            $reqStr .= 'GROUP BY';
            $reqStr .= ' ';
            $reqStr .= join(', ', $q['groupBy']);
            $reqStr .= ' ';
        }

        $res =  dibi::query($reqStr, $q['where']);
        $all = $res->fetchAll();
        foreach ($all as $item) {
            $rId = $item['role_id'];

            if ($splitByType) {
                $type = $item['type'];
                $allRoles[$type][] = $rId;
            } else {
                $allRoles[] = $rId;
            }
        }

        return $allRoles;
    }

    /**
     * @param $roleId
     * @param bool $countOnly
     * @return \DibiRow[]|mixed
     */
    public function getUsersForRole($roleId, $countOnly = false) {
        if($countOnly){
            $res =  dibi::query("SELECT count([login]) FROM [ajxp_user_rights] WHERE [repo_uuid] = %s AND [rights] LIKE %~like~", "ajxp.roles", '"'.$roleId.'";b:1');
            return $res->fetchSingle();
        }else{
            $res =  dibi::query("SELECT [login] FROM [ajxp_user_rights] WHERE [repo_uuid] = %s AND [rights] LIKE %~like~", "ajxp.roles", '"'.$roleId.'";b:1');
            $data = $res->fetchAll();
            return array_map(function($entry){
                return $entry->login;
            }, $data);
        }
    }

    /**
     * @param ContextInterface $ctx
     * @param string $repositoryId
     * @param boolean $details
     * @param bool $admin
     * @return array|int
     */
    public function countUsersForRepository(ContextInterface $ctx, $repositoryId, $details = false, $admin=false){
        $object = RepositoryService::getRepositoryById($repositoryId);
        if(!$admin){
            if($object->securityScope() == "USER"){
                if($details) {
                    return array('users' => 1);
                } else {
                    return 1;
                }
            }else if($object->securityScope() == "GROUP" && $ctx->hasUser()){
                $groupUsers = UsersService::authCountUsers($ctx->getUser()->getGroupPath());
                if($details) {
                    return array('users' => $groupUsers);
                } else {
                    return $groupUsers;
                }
            }
        }

        // Users from roles
        $roles = $this->getRolesForRepository($repositoryId, '', $details);

        if($details){
            $a = ['users' => 0, 'groups' => 0];
            if (isSet($roles['role_user'])) {
                $a['users'] = count($roles['role_user']);
            }
            if (isSet($roles['role_group'])) {
                $a['groups'] = count($roles['role_group']);
            }
            if (isSet($roles['role_role'])) {
                $a['groups'] += count($roles['role_role']);
            }
            return $a;
        }else{
            return count($roles);
        }
    }


    // SAVE / EDIT / CREATE / DELETE USER OBJECT (except password)
    /**
     * Instantiate the right class
     *
     * @param AbstractUser $userId
     * @return AbstractUser|SqlUser
     */
    public function instantiateAbstractUserImpl($userId)
    {
        return new SqlUser($userId, $this);
    }

    /**
     * Get the full path to the Ajxp user class.
     *
     * @see AbstractConfDriver#getUserClassFileName()
     */
    public function getUserClassFileName()
    {
        return AJXP_INSTALL_PATH."/plugins/conf.sql/SqlUser.php";
    }


    /**
     * @param array $roleIds
     * @param bool $excludeReserved
     * @param bool $includeOwnedRoles
     * @return array
     */
    public function listRoles($roleIds = array(), $excludeReserved = false, $includeOwnedRoles = false)
    {
        $wClauses = [];
        if(!$includeOwnedRoles){
            $wClauses[] = '[owner_user_id] IS NULL';
        }
        if (count($roleIds)) {
            // We use (%s) instead of %in to pass everyting as string ('1' instead of 1)
            $wClauses[] = array('[role_id] IN (%s)', $roleIds);
        }
        if ($excludeReserved) {
            $wClauses[] = array('[role_id] NOT LIKE %like~', 'AJXP_');
        }
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::nativeQuery("SET bytea_output=escape");
        }
        $res = dibi::query('SELECT * FROM [ajxp_roles] %if', count($wClauses), 'WHERE %and', $wClauses);
        $all = $res->fetchAll();

        $roles = Array();

        foreach ($all as $role_row) {
            $id = $role_row['role_id'];
            $serialized = $role_row['serial_role'];
            $object = unserialize($serialized);
            if ($object instanceof AjxpRole || $object instanceof AJXP_Role) {
                $roles[$id] = $object;
                if($object instanceof AJXP_Role){
                    $object->setLastUpdated($role_row["last_updated"]);
                }
            }
        }

        return $roles;

    }

    /**
     * Get Roles owned by a given user ( = teams )
     * @param $ownerId
     * @return AJXP_Role[]
     */
    public function listRolesOwnedBy($ownerId){

        $wClauses = [
            ['[owner_user_id] = %s', $ownerId]
        ];
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::nativeQuery("SET bytea_output=escape");
        }
        $res = dibi::query('SELECT * FROM [ajxp_roles] %if', count($wClauses), 'WHERE %and', $wClauses);
        $all = $res->fetchAll();

        $roles = [];

        foreach ($all as $role_row) {
            $id = $role_row['role_id'];
            $serialized = $role_row['serial_role'];
            $object = unserialize($serialized);
            if ($object instanceof AJXP_Role) {
                $roles[$id] = $object;
                $object->setLastUpdated($role_row["last_updated"]);
                $object->setOwnerId($ownerId);
            }
        }

        return $roles;
    }

    /**
     * @param AJXP_Role $role
     * @param null $userObject
     * @return string|void
     * @throws PydioException
     */
    public function updateRole($role, $userObject = null)
    {
        // if role is not existed => insert into
        switch ($this->sqlDriver["driver"]) {
            case "sqlite":
            case "sqlite3":
            case "postgre":
                $row = dibi::query("SELECT [role_id] FROM [ajxp_roles] WHERE [role_id]=%s", $role->getId());
                $res = $row->fetchSingle();
                if($res != null){
                    dibi::query("UPDATE [ajxp_roles] SET [serial_role]=%bin,[searchable_repositories]=%s,[owner_user_id]=%s,[last_updated]=%i WHERE [role_id]=%s",
                        serialize($role),
                        serialize($role->listAcls()),
                        ($role->hasOwner() ? $role->getOwner() : null),
                        time(),
                        $role->getId()
                    );
                }
                else{
                    dibi::query("INSERT INTO [ajxp_roles] ([role_id],[serial_role],[searchable_repositories],[owner_user_id],[last_updated]) VALUES (%s, %bin,%s,%s,%i)",
                        $role->getId(),
                        serialize($role),
                        serialize($role->listAcls()),
                        ($role->hasOwner() ? $role->getOwner() : null),
                        time());
                }
                break;
            case "mysqli":
            case "mysql":
                dibi::query("INSERT INTO [ajxp_roles] ([role_id],[serial_role],[owner_user_id],[last_updated]) VALUES (%s, %s, %s, %i) ON DUPLICATE KEY UPDATE [serial_role]=VALUES([serial_role]), [last_updated]=VALUES([last_updated])",
                    $role->getId(),
                    serialize($role),
                    ($role->hasOwner() ? $role->getOwner() : null),
                    time());
                break;
            default:
                throw new PydioException("ERROR!, DB driver ". $this->sqlDriver["driver"] ." not supported yet in __FUNCTION__");
        }
    }

    /**
     * @param AJXP_Role $role
     */
    public function deleteRole($role)
    {
        // Mixed input Object or ID
        if($role instanceof AJXP_Role) $roleId = $role->getId();
        else $roleId = $role;

        dibi::query("DELETE FROM [ajxp_roles] WHERE [role_id]=%s", $roleId);
    }

    /**
     * Compute the most recent date where one of these roles where updated.
     *
     * @param $rolesIdsList
     * @return int
     */
    public function rolesLastUpdated($rolesIdsList){
        $res = dibi::query("SELECT MAX([last_updated]) FROM [ajxp_roles] WHERE [role_id] IN (%s)", $rolesIdsList);
        return $res->fetchSingle();
    }

    /**
     * @return mixed
     */
    public function countAdminUsers()
    {
        $rows = dibi::query("SELECT COUNT(*) FROM [ajxp_user_rights] WHERE [repo_uuid] = %s AND [rights] = %s", "ajxp.admin", "1");
        return $rows->fetchSingle();
    }

    /**
     * This function is useless for the SQL driver : users are by default correctly listed by group.
     * @param AbstractUser[] $flatUsersList
     * @param string $baseGroup
     * @param bool $fullTree
     * @return void
     */
    public function filterUsersByGroup(&$flatUsersList, $baseGroup = "/", $fullTree = false)
    {
    }

    /**
     * Check if group already exists
     * @param string $groupPath
     * @return boolean
     */
    public function groupExists($groupPath)
    {
        $ret = dibi::query("SELECT [groupPath] FROM [ajxp_groups] WHERE [groupPath] = %s", $groupPath);
        $groups = $ret->fetchSingle();
        if(!empty($groups)) return true;

        return false;
    }

    /**
     * @param string $groupPath
     * @param string $groupLabel
     * @return mixed
     */
    public function createGroup($groupPath, $groupLabel)
    {
        $test = dibi::query("SELECT COUNT(*) FROM [ajxp_groups] WHERE [groupPath] = %s", $groupPath);
        if ($test->fetchSingle()) {
            dibi::query("UPDATE [ajxp_groups] SET [groupLabel]=%s WHERE [groupPath]=%s", $groupLabel, $groupPath);
        } else {
            dibi::query("INSERT INTO [ajxp_groups]",array("groupPath" => $groupPath, "groupLabel" => $groupLabel));
        }
    }

    /**
     * @param string $groupPath
     * @param string $groupLabel
     */
    public function relabelGroup($groupPath, $groupLabel)
    {
        dibi::query("UPDATE [ajxp_groups] SET [groupLabel]=%s WHERE [groupPath]=%s", $groupLabel, $groupPath);
    }


    /**
     * @param $groupPath
     * @throws \Exception
     */
    public function deleteGroup($groupPath)
    {
        // Delete users of this group
        $res = dibi::query("SELECT [login] FROM [ajxp_users] WHERE [groupPath] LIKE %like~ OR [groupPath] = %s ORDER BY [login] ASC", $groupPath."/", $groupPath);
        $rows = $res->fetchAll();
        foreach ($rows as $row) {
            UsersService::deleteUser($row["login"]);
        }

        // Delete associated roles
        $res = dibi::query("SELECT [groupPath] FROM [ajxp_groups] WHERE [groupPath] LIKE %like~ OR [groupPath] = %s", $groupPath."/", $groupPath);
        $rows = $res->fetchAll();
        foreach($rows as $row){
            RolesService::deleteRole('AJXP_GRP_'.$row['groupPath']);
        }

        // Now delete groups and subgroups
        dibi::query("DELETE FROM [ajxp_groups] WHERE [groupPath] LIKE %like~ OR [groupPath] = %s", $groupPath."/", $groupPath);

    }

    /**
     * @param string $baseGroup
     * @return string[]
     */
    public function getChildrenGroups($baseGroup = "/")
    {
        $searchGroup = $baseGroup;
        if($baseGroup[strlen($baseGroup)-1] != "/") $searchGroup.="/";
        $res = dibi::query("SELECT * FROM [ajxp_groups] WHERE [groupPath] LIKE %like~ AND [groupPath] NOT LIKE %s", $searchGroup, $searchGroup."%/%");
        $pairs = $res->fetchPairs("groupPath", "groupLabel");
        foreach ($pairs as $path => $label) {
            if(strlen($path) <= strlen($baseGroup)) unset($pairs[$path]);
            if ($baseGroup != "/") {
                unset($pairs[$path]);
                $pairs[substr($path, strlen($baseGroup))] = $label;
            }
        }
        return $pairs;
    }

    /**
     * Function for deleting a user
     *
     * @param String $userId
     * @param array $deletedSubUsers
     * @throws \Exception
     * @return void
     */
    public function deleteUser($userId, &$deletedSubUsers)
    {
        $children = array();
        try {
            // FIND ALL CHILDREN FIRST
            $children_results = dibi::query('SELECT [login] FROM [ajxp_user_rights] WHERE [repo_uuid] = %s AND [rights] = %s', "ajxp.parent_user", $userId);
            $all = $children_results->fetchAll();
            foreach ($all as $item) {
                $children[] = $item["login"];
            }
            dibi::begin();
            //This one is done by AUTH_DRIVER, not CONF_DRIVER
            //dibi::query('DELETE FROM [ajxp_users] WHERE [login] = %s', $userId);
            dibi::query('DELETE FROM [ajxp_user_rights] WHERE [login] = %s', $userId);
            dibi::query('DELETE FROM [ajxp_user_prefs] WHERE [login] = %s', $userId);
            dibi::query('DELETE FROM [ajxp_user_bookmarks] WHERE [login] = %s', $userId);
            dibi::query('DELETE FROM [ajxp_user_teams] WHERE [owner_id] = %s', $userId);
            dibi::query('DELETE FROM [ajxp_roles] WHERE [owner_user_id] = %s', $userId);
            dibi::commit();
            foreach ($children as $childId) {
                $this->deleteUser($childId, $deletedSubUsers);
                $deletedSubUsers[] = $childId;
            }
            RolesService::deleteRole("AJXP_USR_/".$userId);
        } catch (DibiException $e) {
            throw new \Exception('Failed to delete user, Reason: '.$e->getMessage());
        }
    }

    /**
     * @param $storeID
     * @param $dataID
     * @param $data
     * @param string $dataType
     * @param null $relatedObjectId
     * @throws \Exception
     */
    public function simpleStoreSet($storeID, $dataID, $data, $dataType = "serial", $relatedObjectId = null)
    {
        $values = array(
            "store_id" => $storeID,
            "object_id" => $dataID
        );
        if ($relatedObjectId !== null) {
            $values["related_object_id"] = $relatedObjectId;
        }
        if ($dataType == "serial") {
            $values["serialized_data"] = serialize($data);
        } else if ($dataType == "binary") {
            $values["binary_data"] = $data;
        } else {
            throw new \Exception("Unsupported format type ".$dataType);
        }
        dibi::query("DELETE FROM [ajxp_simple_store] WHERE [store_id]=%s AND [object_id]=%s", $storeID, $dataID);
        dibi::query("INSERT INTO [ajxp_simple_store] ([object_id],[store_id],[serialized_data],[binary_data],[related_object_id]) VALUES (%s,%s,%bin,%bin,%s)",
            $dataID, $storeID, $values["serialized_data"], $values["binary_data"], $values["related_object_id"]);
    }

    /**
     * @param $storeID
     * @param $dataID
     */
    public function simpleStoreClear($storeID, $dataID)
    {
        dibi::query("DELETE FROM [ajxp_simple_store] WHERE [store_id]=%s AND [object_id]=%s", $storeID, $dataID);
    }

    /**
     * @param $storeID
     * @param $dataID
     * @param $dataType
     * @param $data
     * @return bool
     */
    public function simpleStoreGet($storeID, $dataID, $dataType, &$data)
    {
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::nativeQuery("SET bytea_output=escape");
        }
        $children_results = dibi::query("SELECT * FROM [ajxp_simple_store] WHERE [store_id]=%s AND [object_id]=%s", $storeID, $dataID);
        $value = $children_results->fetchAll();
        if(!count($value)) return false;
        $value = $value[0];
        if ($dataType == "serial") {
            $data = unserialize($value["serialized_data"]);
        } else {
            $data = $value["binary_data"];
        }
        if (isSet($value["related_object_id"])) {
            return $value["related_object_id"];
        } else {
            return false;
        }
    }

    /**
     * @param $storeId
     * @param null $cursor
     * @param string|array $dataIdLike
     * @param string $dataType
     * @param string|array $serialDataLike
     * @param string $relatedObjectId
     * @param callable $callbackFunction
     * @return array
     */
    public function simpleStoreList($storeId, &$cursor=null, $dataIdLike="", $dataType="serial", $serialDataLike="", $relatedObjectId="", $callbackFunction = null){
        $wheres = array();
        $wheres[] = array('[store_id]=%s', $storeId);
        if(!empty($dataIdLike)){
            if(is_string($dataIdLike)) $dataIdLike = [$dataIdLike];
            foreach($dataIdLike as $like){
                $wheres[] = array('[object_id] LIKE %s', $like);
            }
        }
        if(!empty($serialDataLike)){
            if(is_string($serialDataLike)) $serialDataLike = [$serialDataLike];
            foreach($serialDataLike as $like){
                $wheres[] = array('[serialized_data] LIKE %s', $like);
            }
        }
        if($relatedObjectId !== ''){ // Do not use empty() here, or "0" can be seen as empty
            $wheres[] = array('[related_object_id] = %s', $relatedObjectId);
        }
        if($cursor != null){
            $total = dibi::query("SELECT count(*) FROM [ajxp_simple_store] WHERE %and", $wheres);
            $cursor["total"] = $total->fetchSingle();
            if(isSet($cursor["count"])) {
                return [];
            }
            $children_results = dibi::query("SELECT * FROM [ajxp_simple_store] WHERE %and %lmt %ofs", $wheres, $cursor[1], $cursor[0]);
        }else{
            $children_results = dibi::query("SELECT * FROM [ajxp_simple_store] WHERE %and", $wheres);

        }

        $result = array();
        foreach($children_results as $value){
            if ($dataType == "serial") {
                $data = unserialize($value["serialized_data"]);
            } else {
                $data = $value["binary_data"];
            }
            if($callbackFunction !== null){
                $stop = $callbackFunction($value['object_id'], $data);
                if($stop){
                    break;
                }
            }else{
                $result[$value['object_id']] = $data;
            }
        }
        return $result;
    }

    /**
     * @param $storeId
     * @param string $dataIdLike
     * @param string $dataType
     * @param string $serialDataLike
     * @param string $relatedObjectId
     * @param bool   $groupByRelated
     * @return array
     */
    public function simpleStoreCount($storeId, $dataIdLike="", $dataType="serial", $serialDataLike="", $relatedObjectId="", $groupByRelated = false){
        $wheres = array();
        $wheres[] = array('[store_id]=%s', $storeId);
        if(!empty($dataIdLike)){
            if(is_string($dataIdLike)) $dataIdLike = [$dataIdLike];
            foreach($dataIdLike as $like){
                $wheres[] = array('[object_id] LIKE %s', $like);
            }
        }
        if(!empty($serialDataLike)){
            if(is_string($serialDataLike)) $serialDataLike = [$serialDataLike];
            foreach($serialDataLike as $like){
                $wheres[] = array('[serialized_data] LIKE %s', $like);
            }
        }
        if($relatedObjectId != ""){
            $wheres[] = array('[related_object_id] = %s', $relatedObjectId);
        }
        if($groupByRelated){
            $gBy = " GROUP BY ([related_object_id]) ORDER BY COUNT([object_id]) DESC";
            $children_results = dibi::query("SELECT COUNT([object_id]) as related_count,[related_object_id] FROM [ajxp_simple_store] WHERE %and".$gBy, $wheres);
            $values = $children_results->fetchAll();
            $result = array();
            foreach($values as $value){
                $result[$value['related_object_id']] = $value['related_count'];
            }
        }else{
            $children_results = dibi::query("SELECT COUNT([object_id]) as related_count FROM [ajxp_simple_store] WHERE %and", $wheres);
            $result = $children_results->fetchSingle();
        }
        return $result;
    }

    /**
     * @param $context
     * @return string
     */
    protected function binaryContextToStoreID($context)
    {
        $storage = "binaries";
        if (isSet($context["USER"])) {
            $storage ="users_binaries.".$context["USER"];
        } else if (isSet($context["REPO"])) {
            $storage ="repos_binaries.".$context["REPO"];
        } else if (isSet($context["ROLE"])) {
            $storage ="roles_binaries.".$context["ROLE"];
        } else if (isSet($context["PLUGIN"])) {
            $storage ="plugins_binaries.".$context["PLUGIN"];
        }
        return $storage;
    }
    /**
     * @param array $context
     * @param String $fileName
     * @param String $ID
     * @return String $ID
     */
    public function saveBinary($context, $fileName, $ID = null)
    {
        if (empty($ID)) {
            $ID = substr(md5(microtime()*rand(0,100)), 0, 12);
            $ID .= ".".pathinfo($fileName, PATHINFO_EXTENSION);
        }
        $store = $this->binaryContextToStoreID($context);
        $this->simpleStoreSet($store, $ID, file_get_contents($fileName), "binary");
        return $ID;
    }

    /**
     * @abstract
     * @param array $context
     * @param String $ID
     * @return boolean
     */
    public function deleteBinary($context, $ID)
    {
        $store = $this->binaryContextToStoreID($context);
        $this->simpleStoreClear($store, $ID);
    }


    /**
     * @param array $context
     * @param String $ID
     * @param Resource $outputStream
     * @return boolean
     */
    public function loadBinary($context, $ID, $outputStream = null)
    {
        $store = $this->binaryContextToStoreID($context);
        $data = "";
        $this->simpleStoreGet($store, $ID, "binary", $data);
        if ($outputStream != null) {
            fwrite($outputStream, $data, strlen($data));
        } else {
            header("Content-Type: ". StatHelper::getImageMimeType($ID));
            echo $data;
        }
    }

    /**
     * @abstract
     * @param String $keyType
     * @param String $keyId
     * @param String $userId
     * @param array $data
     * @return boolean
     */
    public function saveTemporaryKey($keyType, $keyId, $userId, $data)
    {
        $this->simpleStoreSet("temporakey_".$keyType, $keyId, $data, "serial", $userId);
    }

    /**
     * @abstract
     * @param String $keyType
     * @param String $keyId
     * @return array
     */
    public function loadTemporaryKey($keyType, $keyId)
    {
        $data = array();
        $userId = $this->simpleStoreGet("temporakey_".$keyType, $keyId, "serial", $data);
        $data['user_id'] = $userId;
        return $data;
    }

    /**
     * @abstract
     * @param String $keyType
     * @param String $keyId
     * @return boolean
     */
    public function deleteTemporaryKey($keyType, $keyId)
    {
        $this->simpleStoreClear("temporakey_".$keyType, $keyId);
    }

    /**
     * @abstract
     * @param String $keyType
     * @param String $expiration
     * @return null
     */
    public function pruneTemporaryKeys($keyType, $expiration)
    {
        if($this->sqlDriver["driver"] == "postgre"){
            dibi::query("DELETE FROM [ajxp_simple_store] WHERE [store_id] = %s AND [insertion_date] < (CURRENT_TIMESTAMP - time '0:$expiration')", "temporakey_".$keyType);
        }else{
            dibi::query("DELETE FROM [ajxp_simple_store] WHERE [store_id] = %s AND [insertion_date] < (CURRENT_TIMESTAMP - %i)", "temporakey_".$keyType, $expiration*60);
        }
    }


    /**
     * @param array $param
     * @return string
     * @throws \Exception
     */
    public function installSQLTables($param)
    {
        $p = OptionsHelper::cleanDibiDriverParameters($param["SQL_DRIVER"]);
        $res = DBHelper::runCreateTablesQuery($p, $this->getBaseDir() . "/create.sql");
        // SET DB VERSION
        if(defined('AJXP_VERSION_DB') && AJXP_VERSION_DB != "##DB_VERSION##"){
            dibi::connect($p);
            dibi::query("UPDATE [ajxp_version] SET [db_build]=%i", intval(AJXP_VERSION_DB));
            dibi::disconnect();
        }
        return $res;
    }

    /**
     * @return bool
     */
    public function supportsUserTeams()
    {
        return true;
    }

    /**
     * @param UserInterface $parentUser
     * @return array
     */
    public function listUserTeams(UserInterface $parentUser)
    {
        $res = dibi::query("SELECT * FROM [ajxp_user_teams] WHERE [owner_id] = %s ORDER BY [team_id]", $parentUser->getId());
        $data = $res->fetchAll();
        $all = array();
        foreach ($data as $row) {
            $teamId = $row["team_id"];
            $userId = $row["user_id"];
            $teamLabel = $row["team_label"];
            if(!isSet($all[$teamId])) $all[$teamId] = array("LABEL" => $teamLabel, "USERS" => array());
            $all[$teamId]["USERS"][$userId] = $userId;
        }
        return $all;
    }

    /**
     * @param UserInterface $parentUser
     * @param $teamId
     * @return array
     */
    public function teamIdToUsers(UserInterface $parentUser, $teamId)
    {
        if(empty($parentUser)) {
            return [];
        }
        $res = [];
        $teams = $this->listUserTeams($parentUser);
        $teamData = $teams[$teamId];
        foreach ($teamData["USERS"] as $userId) {
            if (UsersService::userExists($userId)) {
                $res[] = $userId;
            } else {
                $this->removeUserFromTeam($teamId, $userId);
            }
        }
        return $res;
    }

    /**
     * @param UserInterface $parentUser
     * @param $teamId
     * @param $userId
     * @param null $teamLabel
     */
    private function addUserToTeam(UserInterface $parentUser, $teamId, $userId, $teamLabel = null)
    {
        if ($teamLabel == null) {
            $res = dibi::query("SELECT [team_label] FROM [ajxp_user_teams] WHERE [team_id] = %s AND  [owner_id] = %s",
                $teamId, $parentUser->getId());
            $teamLabel = $res->fetchSingle();
        }
        dibi::query("INSERT INTO [ajxp_user_teams] ([team_id],[user_id],[team_label],[owner_id]) VALUES (%s,%s,%s,%s)",
            $teamId, $userId, $teamLabel, $parentUser->getId()
        );
    }

    /**
     * @param UserInterface $parentUser
     * @param $teamId
     * @param $users
     * @param null $teamLabel
     */
    private function editTeamUsers(UserInterface $parentUser, $teamId, $users, $teamLabel = null)
    {
        if ($teamLabel == null) {
            $res = dibi::query("SELECT [team_label] FROM [ajxp_user_teams] WHERE [team_id] = %s AND  [owner_id] = %s",
                $teamId, $parentUser->getId());
            $teamLabel = $res->fetchSingle();
        }
        // Remove old users
        dibi::query("DELETE FROM [ajxp_user_teams] WHERE [team_id] = %s", $teamId);
        foreach($users as $userId){
            if(!UsersService::userExists($userId, "r")) continue;
            dibi::query("INSERT INTO [ajxp_user_teams] ([team_id],[user_id],[team_label],[owner_id]) VALUES (%s,%s,%s,%s)",
                $teamId, $userId, $teamLabel, $parentUser->getId()
            );
        }
    }

    /**
     * @param UserInterface $parentUser
     * @param $teamId
     * @param null $userId
     */
    private function removeUserFromTeam(UserInterface $parentUser, $teamId, $userId = null)
    {
        if ($userId == null) {
            dibi::query("DELETE FROM [ajxp_user_teams] WHERE [team_id] = %s AND  [owner_id] = %s",
                $teamId, $parentUser->getId()
            );
        } else {
            dibi::query("DELETE FROM [ajxp_user_teams] WHERE [team_id] = %s AND  [user_id] = %s AND [owner_id] = %s",
                $teamId, $userId, $parentUser->getId()
            );
        }
    }

    /**
     * @param $actionName
     * @param $httpVars
     * @param $fileVars
     * @param ContextInterface $ctx
     * @throws \Exception
     */
    public function userTeamsActions($actionName, $httpVars, $fileVars, ContextInterface $ctx)
    {
        switch ($actionName) {

            case "user_team_create":

                $userIds = $httpVars["user_ids"];
                $teamLabel = InputFilter::sanitize($httpVars["team_label"], InputFilter::SANITIZE_HTML_STRICT);
                if(empty($teamLabel)){
                    throw new \Exception("Empty Team Label!");
                }
                if(empty($userIds)){
                    throw new \Exception("Please select some users for this team.");
                }
                $teamId = StringHelper::slugify($teamLabel) ."-".intval(rand(0,1000));
                foreach ($userIds as $userId) {
                    $id = InputFilter::sanitize($userId, InputFilter::SANITIZE_EMAILCHARS);
                    $this->addUserToTeam($ctx->getUser(), $teamId, $id, $teamLabel);
                }
                echo 'Created Team $teamId';

                break;

            case "user_team_delete":

                $this->removeUserFromTeam($ctx->getUser(), $httpVars["team_id"], null);
                break;

            case "user_team_add_user":

                $this->addUserToTeam($ctx->getUser(), $httpVars["team_id"], $httpVars["user_id"], null);
                break;

            case "user_team_edit_users":

                $this->editTeamUsers($ctx->getUser(), $httpVars["team_id"], $httpVars["users"], $httpVars["team_label"]);
                break;

            case "user_team_delete_user":

                $this->removeUserFromTeam($ctx->getUser(), $httpVars["team_id"], $httpVars["user_id"]);
                break;

        }
    }


    /**
     * @param $actionName
     * @param $httpVars
     * @param $fileVars
     */
    public function ajxpTableExists($actionName, $httpVars, $fileVars){

        $p = $this->sqlDriver;
        if($p["driver"] == "postgre"){
            $tableQuery = "SELECT [tablename] FROM [pg_catalog].[pg_tables] WHERE [tablename] = %s";
        }else if($p["driver"] == "sqlite3" || $p["driver"] == "sqlite"){
            $tableQuery = "SELECT [name] FROM [sqlite_master] WHERE type = \"table\" AND [name] = %s";
        }else{
            $tableQuery = "SHOW TABLES LIKE %s";
        }
        $tableName = InputFilter::sanitize($httpVars["table_name"], InputFilter::SANITIZE_ALPHANUM);
        $tables = dibi::query($tableQuery, $tableName)->fetchPairs();
        $exists = (count($tables) && in_array($tableName, $tables));
        HTMLWriter::charsetHeader("application/json");
        echo json_encode(array("result" => $exists));

    }

}