<?php //$Id$
//Copyright (c) 2015-2016 Pierre Pronchery <khorben@defora.org>
//This file is part of DeforaOS Web DaPortal
//
//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, version 3 of the License.
//
//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 <http://www.gnu.org/licenses/>.


//SQLUserBackend
class SQLUserBackend extends UserBackend
{
	//public
	//methods
	//essential
	//SQLUserBackend::SQLUserBackend
	public function __construct(Engine $engine, $uid, $username = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_get_by_id;
		$args = array('user_id' => $uid);

		if($username !== FALSE)
		{
			if($engine instanceof HTTPFriendlyEngine)
			{
				//XXX workaround for friendly titles
				$query .= ' AND username '
					.$db->like().' :username';
				$username = str_replace('-', '_', $username);
			}
			else
				$query = static::$query_get_by_id_username;
			$args['username'] = $username;
		}
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| count($res) != 1)
			return;
		$res = $res->current();
		$this->user_id = $res['id'];
		$this->username = $res['username'];
		parent::__construct($engine, $res['id'], $res['username']);
		$this->enabled = $db->isTrue($res['enabled']);
		$this->locked = ($res['locked'] == '!');
		$this->group_id = $res['group_id'];
		$this->admin = $db->isTrue($res['admin']);
		$this->email = $res['email'];
		$this->fullname = $res['fullname'];
	}


	//public
	//methods
	//accessors
	//SQLUserBackend::isMember
	public function isMember(Engine $engine, $group)
	{
		$database = $engine->getDatabase();
		$query = static::$query_member;
		$args = array('user_id' => $this->user_id,
			'groupname' => $group);

		if(($res = $database->query($engine, $query, $args)) === FALSE
				|| count($res) != 1)
			return FALSE;
		return TRUE;
	}


	//SQLUserBackend::setGroup
	public function setGroup(Engine $engine, $group_id, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_set_group;

		$args = array('user_id' => $this->user_id,
			'group_id' => $group_id);
		if($db->query($engine, $query, $args) === FALSE)
		{
			$error = $this->username.': Could not set group';
			return FALSE;
		}
		//FIXME also invalidate the cache in User::lookup()
		$this->group_id = $group_id;
		return TRUE;
	}


	//SQLUserBackend::setPassword
	public function setPassword(Engine $engine, $password, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_set_password;
		$salt = '$5$'.static::passwordGenerate(16);

		$hash = crypt($password, $salt);
		$args = array('user_id' => $this->user_id,
			'password' => $hash);
		return ($db->query($engine, $query, $args) !== FALSE);
	}


	//useful
	//SQLUserBackend::authenticate
	public function authenticate(Engine $engine, $password)
	{
		$db = $engine->getDatabase();
		$query = static::$query_authenticate;
		$args = array('username' => $this->username);

		//obtain the password hash
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| count($res) != 1)
		{
			$error = $this->username
				.': Could not obtain the password hash';
			return $engine->log(LOG_ERR, $error);
		}
		$res = $res->current();
		if(strlen($res['password']) > 0 && $res['password'][0] == '$')
		{
			//the password is salted
			$a = explode('$', $res['password']);
			$cipher = $a[1];
			switch($cipher)
			{
				case '1':
				case '2a':
				case '5':
				case '6':
					$hash = crypt($password,
							$res['password']);
					break;
				default:
					$error = $this->username
						.': Unsupported cipher';
					return $engine->log(LOG_ERR, $error);
			}
		}
		else if(strlen($res['password']) == 32)
		{
			//the password is not salted (plain MD5)
			$hash = md5($password);
			//if it matches, hash it and save it again
			if($res['password'] == $hash)
				$this->setPassword($engine, $password);
		}
		else if(strlen($res['password']) > 0
				&& $res['password'][0] == '!')
			return $engine->log(LOG_ERR, $this->username
					.': User is locked');
		else
			return $engine->log(LOG_ERR, $this->username
					.': Invalid password hash');
		if(hash_equals($res['password'], $hash) !== TRUE)
			return $engine->log(LOG_ERR, $this->username
					.': Could not authenticate user');
		//the password is correct
		return new AuthCredentials($res['user_id'], $res['username'],
				$res['group_id'], $res['groupname'],
				$db->isTrue($res['admin']));
	}


	//SQLUserBackend::addGroup
	public function addGroup(Engine $engine, $group_id, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_insert_group;
		$args = array('user_id' => $this->user_id,
			'group_id' => $group_id);

		if($db->query($engine, $query, $args) === FALSE)
		{
			$error = $this->username.': '.$group_id
				.': Could not add group';
			return FALSE;
		}
		return TRUE;
	}


	//SQLUserBackend::delete
	public function delete(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_delete;
		$args = array('user_id' => $this->user_id);

		if($this->user_id === FALSE)
			return TRUE;
		if($db->transactionBegin($engine) === FALSE)
		{
			$error = $this->username.': Could not delete user';
			return FALSE;
		}
		//remove foreign constraints
		if($this->removeGroups($engine, $error) === FALSE
				|| $this->removeRegister($engine, $error)
				=== FALSE
				|| $this->removeReset($engine, $error)
				=== FALSE)
		{
			$db->transactionRollback($engine);
			return FALSE;
		}
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| $res->getAffectedCount() != 1)
		{
			$error = $this->username.': Could not delete user';
			$db->transactionRollback($engine);
			return FALSE;
		}
		if($db->transactionCommit($engine) === FALSE)
		{
			$error = $this->username.': Could not delete user';
			return FALSE;
		}
		$this->user_id = FALSE;
		$this->username = FALSE;
		$this->group_id = FALSE;
		$this->groupname = FALSE;
		$this->enabled = FALSE;
		$this->locked = FALSE;
		$this->admin = FALSE;
		$this->email = FALSE;
		$this->fullname = FALSE;
		return TRUE;
	}


	//SQLUserBackend::disable
	public function disable(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_disable;
		$args = array('user_id' => $this->user_id);

		if($this->enabled === FALSE)
			return TRUE;
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| $res->getAffectedCount() != 1)
		{
			$error = $this->username.': Could not disable user';
			return FALSE;
		}
		$this->enabled = FALSE;
		return TRUE;
	}


	//SQLUserBackend::enable
	public function enable(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_enable;
		$args = array('user_id' => $this->user_id);

		if($this->enabled === TRUE)
			return TRUE;
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| $res->getAffectedCount() != 1)
		{
			$error = $this->username.': Could not enable user';
			return FALSE;
		}
		$this->enabled = TRUE;
		return TRUE;
	}


	//SQLUserBackend::listAll
	static public function listAll(Engine $engine, $enabled = -1)
	{
		$db = $engine->getDatabase();
		$query = is_bool($enabled)
			? static::$query_list_enabled : static::$query_list;

		if(($res = $db->query($engine, $query)) === FALSE)
			return FALSE;
		$ret = array();
		foreach($res as $r)
			//XXX inefficient (issues more queries again)
			$ret[] = new User($engine, $r['user_id']);
		return $ret;
	}


	//SQLUserBackend::lock
	public function lock(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_lock;
		$args = array('user_id' => $this->user_id);

		if($this->locked !== FALSE)
			return TRUE;
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| $res->getAffectedCount() != 1)
		{
			$error = $this->username.': Could not lock user';
			return FALSE;
		}
		$this->locked = TRUE;
		return TRUE;
	}


	//SQLUserBackend::removeGroup
	public function removeGroup(Engine $engine, $group_id, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_delete_group;
		$args = array('user_id' => $this->user_id,
			'group_id' => $group_id);

		if($db->query($engine, $query, $args) === FALSE)
		{
			$error = $this->username.': '.$group_id
				.': Could not remove group';
			return FALSE;
		}
		return TRUE;
	}


	//SQLUserBackend::removeGroups
	public function removeGroups(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_delete_groups;
		$args = array('user_id' => $this->user_id);

		if($db->query($engine, $query, $args) === FALSE)
		{
			$error = $this->username.': Could not remove groups';
			return FALSE;
		}
		return TRUE;
	}


	//SQLUserBackend::removeRegister
	public function removeRegister(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_register_delete_user;
		$args = array('user_id' => $this->user_id);

		if($db->query($engine, $query, $args) === FALSE)
		{
			$error = $this->username
				.': Could not remove registration';
			return FALSE;
		}
		return TRUE;
	}


	//SQLUserBackend::removeReset
	public function removeReset(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_reset_delete_user;
		$args = array('user_id' => $this->user_id);

		if($db->query($engine, $query, $args) === FALSE)
		{
			$error = $this->username.': Could not remove reset';
			return FALSE;
		}
		return TRUE;
	}


	//SQLUserBackend::unlock
	public function unlock(Engine $engine = NULL, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$query = static::$query_unlock;
		$args = array('user_id' => $this->user_id);

		if($this->locked === FALSE)
			return TRUE;
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| $res->getAffectedCount() != 1)
		{
			$error = $this->username.': Could not unlock user';
			return FALSE;
		}
		$this->locked = FALSE;
		return TRUE;
	}


	//static
	//SQLUserBackend::insert
	static public function insert(Engine $engine, $username, $group_id,
			$fullname, $password, $email, $enabled = FALSE,
			$locked = FALSE, $admin = FALSE, &$error = FALSE)
	{
		//FIXME code duplication with User::register()
		$db = $engine->getDatabase();
		$query = static::$query_insert;
		$error = '';
		$salt = '$5$'.static::passwordGenerate(16);

		//FIXME really validate username
		if(!is_string($username) || strlen($username) == 0)
			$error .= _("The username is not valid\n");
		if($fullname === FALSE)
			$fullname = '';
		//FIXME really validate e-mail
		if(strpos($email, '@') === FALSE)
			$error .= _("The e-mail address is not valid\n");
		//FIXME verify that the username and e-mail are both unique
		if(strlen($error) > 0)
			return FALSE;
		if($password === FALSE || strlen($password) == 0)
			$password = $locked ? '!' : '';
		else
			$password = ($locked ? '!' : '')
				.crypt($password, $salt);
		$args = array('username' => $username, 'group_id' => $group_id,
			'fullname' => $fullname, 'password' => $password,
			'email' => $email, 'enabled' => $enabled ? 1 : 0,
			'admin' => $admin ? 1 : 0);
		$res = $db->query($engine, $query, $args);
		if($res === FALSE || ($uid = $db->getLastID($engine,
						'daportal_user', 'user_id'))
				=== FALSE)
		{
			$error = _('Could not insert the user');
			return FALSE;
		}
		$user = new User($engine, $uid);
		if($user->getUserID() === FALSE)
		{
			$error = _('Could not insert the user');
			return FALSE;
		}
		$error = '';
		return $user;
	}


	//SQLUserBackend::lookup
	static public function lookup(Engine $engine, $username,
			$user_id = FALSE, $enabled = TRUE)
	{
		static $cache = array();
		$db = $engine->getDatabase();
		$query = static::$query_get_by_username;
		$args = array('username' => $username, 'enabled' => $enabled);

		if(isset($cache[$username]))
		{
			if($user_id !== FALSE && $cache[$username]->getUserID()
					!= $user_id)
				return FALSE;
			return $cache[$username];
		}
		if(($res = $db->query($engine, $query, $args)) === FALSE
				|| count($res) != 1)
			return FALSE;
		$res = $res->current();
		$cache[$username] = new User($engine, $res['id'], $username);
		if($user_id !== FALSE && $cache[$username]->getUserID()
				!= $user_id)
			return FALSE;
		return $cache[$username];
	}


	//SQLUserBackend::passwordGenerate
	static public function passwordGenerate($length = 8,
			$string = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
	{
		$password = '';

		for($i = 0; $i < $length; $i++)
			$password .= $string[rand(0, strlen($string) - 1)];
		return $password;
	}


	//SQLUserBackend::register
	static public function register(Engine $engine, $module, $username,
			$password, $email, $enabled = FALSE, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$error = '';

		//FIXME really validate username
		if(!is_string($username) || strlen($username) == 0)
			$error .= _("The username is not valid\n");
		//FIXME really validate e-mail
		if(strpos($email, '@') === FALSE)
			$error .= _("The e-mail address is not valid\n");
		//FIXME verify that the username and e-mail are both unique
		if(strlen($error) > 0)
			return FALSE;
		if($db->transactionBegin($engine) === FALSE)
		{
			$error = _('Could not register the user');
			return FALSE;
		}
		$query = static::$query_register;
		$args = array('username' => $username, 'email' => $email,
			'enabled' => $enabled ? 1 : 0);
		$res = $db->query($engine, $query, $args);
		if($res === FALSE || ($uid = $db->getLastID($engine,
						'daportal_user', 'user_id'))
				=== FALSE)
		{
			$db->transactionRollback($engine);
			$error = _('Could not register the user');
			return FALSE;
		}
		$user = static::lookup($engine, $username, $uid, FALSE);
		if($user === FALSE || $user->getUserID() == 0)
		{
			$db->transactionRollback($engine);
			$error = _('Could not register the user');
			return FALSE;
		}
		if($enabled === FALSE)
		{
			$query = static::$query_register_token;
			//let the user confirm registration
			if($password === FALSE)
				//generate a random password
				$password = static::passwordGenerate();
			//generate a token
			$token = sha1(uniqid($password, TRUE));
			$args = array('user_id' => $uid, 'token' => $token);
			if($user->setPassword($engine, $password) === FALSE
				|| $db->query($engine, $query, $args) === FALSE)
			{
				$db->transactionRollback($engine);
				$error = _('Could not register the user');
				return FALSE;
			}
			//send an e-mail for confirmation
			$r = $user->getRequest('validate', array(
					'token' => $token));
			$subject = _('User registration'); //XXX add site title
			$text = _("Thank you for registering on this site.\n");
			//FIXME do not send the password if already known
			$text .= _("\nYour password is: ").$password."\n";
			$text .= _("\nPlease click on the following link to enable your account:\n");
			$content = new PageElement('label',
				array('text' => $text));
			$content->append('link', array('request' => $r));
			$text = _("Please note that this link will expire in 7 days.\n");
			$content->append('label', array('text' => $text));
			//FIXME send only if the transaction succeeds
			if(DaPortal\Mail::send($engine, FALSE, $email, $subject,
					$content) === FALSE)
			{
				$db->transactionRollback($engine);
				$error = _('Could not register the user');
				return $engine->log(LOG_ERR, $username
						.': '.$email
						.': Could not send the activation message');
			}
		}
		if($db->transactionCommit($engine) === FALSE)
		{
			$error = _('Could not register the user');
			return FALSE;
		}
		$error = '';
		return $user;
	}


	//SQLUserBackend::reset
	static public function reset(Engine $engine, $module, $username, $email,
			&$error = FALSE)
	{
		$db = $engine->getDatabase();

		//we can ignore errors
		static::resetCleanup($engine);
		//verify the username and e-mail address
		$query = static::$query_reset_validate;
		$args = array('username' => $username, 'email' => $email);
		$res = $db->query($engine, $query, $args);
		if($res === FALSE || count($res) != 1)
		{
			//XXX consider silently failing (to avoid bruteforcing)
			$error = _('Could not reset the password');
			return FALSE;
		}
		$res = $res->current();
		$uid = $res['user_id'];
		//check for existing tokens
		$query = static::$query_reset_token_check;
		$args = array('user_id' => $uid);
		if(($res = $db->query($engine, $query, $args)) === FALSE)
		{
			$error = _('Could not reset the password');
			return FALSE;
		}
		if(count($res) != 0)
		{
			$error = _('A password request was issued less than 24 hours ago already.');
			return FALSE;
		}
		//generate a token
		$token = sha1(uniqid($uid.$username.$email, TRUE));
		//insert the token in the database
		$query = static::$query_reset_token;
		$args = array('user_id' => $uid, 'token' => $token);
		if(($res = $db->query($engine, $query, $args)) === FALSE)
		{
			$error = _('Could not reset the password');
			return FALSE;
		}
		//send an e-mail with the token
		$r = new Request($module, 'reset', $uid, FALSE,
			array('token' => $token));
		$subject = _('Password reset'); //XXX add site title
		$text = _("Someone (hopefully you) has requested a password reset on your account.\n");
		$text .= _("Please click on the following link to reset your password:\n");
		$content = new PageElement('label', array('text' => $text));
		$content->append('link', array('request' => $r));
		$content->append('label', array('text' => "\n"));
		$content->append('label', array(
			'text' => _("Please note that this link will expire in 24 hours.")));
		if(DaPortal\Mail::send($engine, FALSE, $email, $subject,
				$content) === FALSE)
		{
			$error = _('Could not send the confirmation e-mail');
			return FALSE;
		}
		return TRUE;
	}


	//SQLUserBackend::resetPassword
	static public function resetPassword(Engine $engine, $uid, $password,
			$token, &$error = FALSE)
	{
		$db = $engine->getDatabase();
		$error = _('Could not reset the password');

		if($db->transactionBegin($engine) === FALSE)
			return FALSE;
		//delete password reset requests older than one day
		if(static::resetCleanup($engine) === FALSE)
		{
			$db->transactionRollback($engine);
			return FALSE;
		}
		//lookup the token
		$query = static::$query_reset_validate_token;
		$args = array('user_id' => $uid, 'token' => $token);
		$res = $db->query($engine, $query, $args);
		if($res === FALSE || count($res) != 1)
		{
			$db->transactionRollback($engine);
			return FALSE;
		}
		$user = new User($engine, $uid);
		if($user->setPassword($engine, $password) === FALSE)
		{
			$db->transactionRollback($engine);
			return FALSE;
		}
		$query = static::$query_reset_delete;
		$args = array('user_id' => $uid, 'token' => $token);
		if($db->query($engine, $query, $args) === FALSE)
		{
			$db->transactionRollback($engine);
			return FALSE;
		}
		if($db->transactionCommit($engine) === FALSE)
			return FALSE;
		$error = '';
		return FALSE;
	}


	//SQLUserBackend::validate
	static public function validate(Engine $engine, $uid, $token,
			&$error = FALSE)
	{
		$db = $engine->getDatabase();
		$error = '';

		if($uid === FALSE || !is_numeric($uid))
			$error .= _("Unknown user ID\n");
		if($token === FALSE)
			$error .= _("The token must be specified\n");
		if(strlen($error) > 0)
			return FALSE;
		if(static::registerCleanup($engine) === FALSE)
		{
			$error = _("Could not validate the user");
			return FALSE;
		}
		$query = static::$query_register_validate;
		$args = array('user_id' => $uid, 'token' => $token);
		$res = $db->query($engine, $query, $args);
		if($res === FALSE || count($res) != 1)
		{
			$error = _('Could not validate the user');
			return FALSE;
		}
		$res = $res->current();
		if($db->transactionBegin($engine) === FALSE)
		{
			$error = _('Could not validate the user');
			return FALSE;
		}
		$query = static::$query_register_delete;
		$args = array('user_register_id' => $res['user_register_id']);
		if($db->query($engine, $query, $args) === FALSE)
		{
			$db->transactionRollback($engine);
			$error = _('Could not validate the user');
			return FALSE;
		}
		$query = static::$query_register_delete;
		$args = array('user_register_id' => $res['user_register_id']);
		if($db->query($engine, $query, $args) === FALSE)
		{
			$db->transactionRollback($engine);
			$error = _('Could not validate the user');
			return FALSE;
		}
		$user = new User($engine, $res['user_id']);
		if($user->enable($engine) === FALSE
				|| $db->transactionCommit($engine) === FALSE)
		{
			$db->transactionRollback($engine);
			$error = _('Could not enable the user');
			return FALSE;
		}
		return $user;
	}


	//protected
	//properties
	static protected $timestamp_format = '%Y-%m-%d %H:%M:%S';

	//queries
	//IN:	username
	static protected $query_authenticate = "SELECT user_id, username,
		daportal_user.group_id AS group_id, groupname, admin, password
		FROM daportal_user
		LEFT JOIN daportal_group
		ON daportal_user.group_id=daportal_group.group_id
		WHERE username=:username
		AND daportal_user.enabled='1'
		AND daportal_group.enabled='1'";
	//IN:	user_id
	static protected $query_get_by_id = "SELECT user_id AS id, username,
		daportal_user.enabled AS enabled,
		substr(password, 1, 1) AS locked,
		daportal_user.group_id AS group_id, groupname, admin, email,
		fullname
		FROM daportal_user
		LEFT JOIN daportal_group
		ON daportal_user.group_id=daportal_group.group_id
		WHERE daportal_group.enabled='1'
		AND user_id=:user_id";
	//IN:	user_id
	//	username
	static protected $query_get_by_id_username = "SELECT user_id AS id,
		username, daportal_user.enabled AS enabled,
		substr(password, 1, 1) AS locked,
		daportal_user.group_id AS group_id, groupname, admin, email,
		fullname
		FROM daportal_user
		LEFT JOIN daportal_group
		ON daportal_user.group_id=daportal_group.group_id
		WHERE daportal_group.enabled='1'
		AND user_id=:user_id
		AND username=:username";
	//IN:	user_id
	//	groupname
	static protected $query_member = "SELECT user_id,
		daportal_group.group_id AS group_id
		FROM daportal_user_group, daportal_group
		WHERE daportal_user_group.group_id=daportal_group.group_id
		AND user_id=:user_id
		AND groupname=:groupname
		AND enabled='1'";
	//IN:	user_id
	//	group_id
	static protected $query_set_group = 'UPDATE daportal_user
		SET group_id=:group_id
		WHERE user_id=:user_id';
	//IN:	user_id
	//	password
	static protected $query_set_password = 'UPDATE daportal_user
		SET password=:password
		WHERE user_id=:user_id';
	//IN:	user_id
	static protected $query_delete = 'DELETE FROM daportal_user
		WHERE user_id=:user_id';
	//IN:	user_id
	//	group_id
	static protected $query_delete_group = 'DELETE
		FROM daportal_user_group
		WHERE user_id=:user_id
		AND group_id=:group_id';
	//IN:	user_id
	static protected $query_delete_groups = 'DELETE
		FROM daportal_user_group
		WHERE user_id=:user_id';
	//IN:	user_id
	static protected $query_disable = "UPDATE daportal_user
		SET enabled='0'
		WHERE user_id=:user_id";
	//IN:	user_id
	static protected $query_enable = "UPDATE daportal_user
		SET enabled='1'
		WHERE user_id=:user_id";
	//IN:	username
	//	enabled
	static protected $query_get_by_username = 'SELECT user_id AS id
		FROM daportal_user
		WHERE username=:username AND enabled=:enabled';
	//IN:	username
	//	group_id
	//	fullname
	//	password
	//	email
	//	enabled
	//	admin
	static protected $query_insert = 'INSERT INTO daportal_user
		(username, group_id, fullname, password, email, enabled, admin)
		VALUES (:username, :group_id, :fullname, :password, :email,
		:enabled, :admin)';
	//IN:	user_id
	//	group_id
	static protected $query_insert_group = 'INSERT INTO
		daportal_user_group (user_id, group_id)
		VALUES (:user_id, :group_id)';
	static protected $query_list = 'SELECT user_id
		FROM daportal_user
		ORDER BY username ASC';
	static protected $query_list_enabled = 'SELECT user_id
		FROM daportal_user_enabled
		ORDER BY username ASC';
	//IN:	user_id
	static protected $query_lock = "UPDATE daportal_user
		SET password=concat('!', password)
		WHERE user_id=:user_id AND substr(password, 1, 1) != '!'";
	static protected $query_register = 'INSERT INTO daportal_user
		(username, email, enabled)
		VALUES (:username, :email, :enabled)';
	static protected $query_register_token = 'INSERT INTO daportal_user_register
		(user_id, token)
		VALUES (:user_id, :token)';
	static protected $query_register_cleanup = 'DELETE FROM daportal_user_register
		WHERE timestamp <= :timestamp';
	//IN:	user_register_id
	static protected $query_register_delete = 'DELETE
		FROM daportal_user_register
		WHERE user_register_id=:user_register_id';
	//IN:	user_id
	static protected $query_register_delete_user = 'DELETE
		FROM daportal_user_register
		WHERE user_id=:user_id';
	static protected $query_register_validate = 'SELECT user_register_id,
		daportal_user.user_id AS user_id, username
		FROM daportal_user, daportal_user_register
		WHERE daportal_user.user_id=daportal_user_register.user_id
		AND daportal_user.user_id=:user_id AND token=:token';
	//IN:	timestamp
	static protected $query_reset_cleanup = 'DELETE
		FROM daportal_user_reset
		WHERE timestamp <= :timestamp';
	//IN:	user_id
	//	token
	static protected $query_reset_delete = 'DELETE
		FROM daportal_user_reset
		WHERE user_id=:user_id AND token=:token';
	//IN:	user_id
	static protected $query_reset_delete_user = 'DELETE
		FROM daportal_user_reset
		WHERE user_id=:user_id';
	//IN:	user_id
	//	token
	static protected $query_reset_token = 'INSERT
		INTO daportal_user_reset
		(user_id, token)
		VALUES (:user_id, :token)';
	static protected $query_reset_token_check = 'SELECT user_id
		FROM daportal_user_reset
		WHERE user_id=:user_id';
	static protected $query_reset_validate = "SELECT user_id
		FROM daportal_user
		WHERE enabled='1' AND username=:username AND email=:email";
	static protected $query_reset_validate_token = "SELECT
		daportal_user.user_id AS user_id, username
		FROM daportal_user, daportal_user_reset
		WHERE daportal_user.user_id=daportal_user_reset.user_id
		AND enabled='1' AND daportal_user.user_id=:user_id
		AND token=:token";
	//IN:	user_id
	static protected $query_unlock = "UPDATE daportal_user
		SET password=substr(password, 2)
		WHERE user_id=:user_id AND substr(password, 1, 1) = '!'";


	//methods
	//useful
	//SQLUserBackend::registerCleanup
	//delete registrations older than one week
	static protected function registerCleanup(Engine $engine)
	{
		$db = $engine->getDatabase();
		$query = static::$query_register_cleanup;
		$timestamp = strftime(static::$timestamp_format, time()
			- 604800);
		$args = array('timestamp' => $timestamp);
		$error = 'Could not clean the registration database up';

		if($db->query($engine, $query, $args, TRUE) === FALSE)
			return $engine->log(LOG_ERR, $error);
		return TRUE;
	}


	//SQLUserBackend::resetCleanup
	//delete password reset requests older than one day
	static protected function resetCleanup(Engine $engine)
	{
		$db = $engine->getDatabase();
		$query = static::$query_reset_cleanup;
		$timestamp = strftime(static::$timestamp_format, time()
			- 86400);
		$args = array('timestamp' => $timestamp);
		$error = 'Could not clean the password reset database up';

		if($db->query($engine, $query, $args, TRUE) === FALSE)
			return $engine->log(LOG_ERR, $error);
		return TRUE;
	}
}

?>