queries

    public static $registerUser = "INSERT INTO `users` (`email`, `password`, `displayname`) VALUES (?, ?, ?);";
    public static $loginUser = "SELECT `id`, `displayname` FROM `users` WHERE email = ? AND password = ?;";
    public static $isEmailRegistered = "SELECT id FROM `users` WHERE `email` = ?;";
    public static $isUserInGroup = "SELECT id FROM `groupusers` WHERE `userid` = ? AND `groupid` = ?;";
    public static $getDisplayName = "SELECT `displayname` FROM `users` WHERE `id` = ?;";
    public static $getDisplayNames = "SELECT `userid` AS id, `displayname` FROM (SELECT `groupid` FROM `groupusers` WHERE `userid` = ?) AS mg JOIN `groupusers` ON mg.groupid = `groupusers`.groupid JOIN `users` ON `groupusers`.userid = `users`.id GROUP BY(`userid`)";
    public static $createGroup = "INSERT INTO `groups` (`code`, `name`, `admin_id`) VALUES (?, ?, ?);";
    public static $getGroupDetails = "SELECT `name`, `code` FROM `groups` WHERE id = ?;";
    public static $getGroupFromCode = "SELECT id, `name` FROM `groups` WHERE `code` = ?;";
    public static $joinGroup = "INSERT INTO `groupusers` (`userid`, `groupid`) VALUES (?, ?);";
    public static $getCodes = "SELECT g.code FROM `groups` AS g JOIN `groupusers` ON g.id = `groupusers`.groupid WHERE `groupusers`.userid = ?;";
    public static $getGroups = "SELECT g.id, g.name, g.code, g.admin_id, (SELECT COUNT(`id`) FROM `messages` AS m WHERE m.groupid = g.id AND m.when > (SELECT lastviewed FROM groupusers WHERE groupusers.userid = @userid AND groupusers.groupid = g.id)) AS unread, (SELECT GROUP_CONCAT(`displayname`) FROM (SELECT `groupid`, `code`, `name`, `lastactive`, `userid`, `displayname`, `lastviewed` FROM `groups` JOIN `groupusers` JOIN `users` ON `groups`.`id` = `groupusers`.`groupid` AND `groupusers`.`userid` = `users`.`id`) AS `groupusers_full` WHERE groupid = g.id AND userid != @userid) AS othermembers FROM groups AS g JOIN `groupusers` ON g.id = `groupusers`.groupid WHERE `groupusers`.userid = @userid ORDER BY unread DESC, lastactive DESC;";
    public static $updateLastActive = "UPDATE `groupusers` SET `lastviewed` = NOW() WHERE `userid` = ? AND `groupid` = ?;";
    public static $getMessages = "SELECT m.id, m.userid, m.groupid, m.userid = ? AS own, `users`.displayname, m.when, m.text FROM `messages` AS m JOIN `users` ON m.userid = `users`.id WHERE `groupid` = ? ORDER BY `when` ASC;";
    public static $getOwnMessage = "SELECT `text`, `when` FROM `messages` WHERE `userid` = ? ORDER BY `when` DESC LIMIT 1;";
    public static $postMessage = "INSERT INTO `messages` (`userid`, `groupid`, `text`, `when`) VALUES (?, ?, ?, CURRENT_TIMESTAMP);";
    public static $updateGroupActive = "UPDATE `groups` SET `lastactive` = NOW() WHERE `id` = ?;";