tetratto-core 18.0.2

use super::common::NAME_REGEX;
use oiseau::cache::Cache;
use crate::model::{
    Error, Result,
    auth::{Token, User, UserSettings},
    permissions::{FinePermission, SecondaryPermission},
    oauth::AuthGrant,
    moderation::AuditLogEntry,
    auth::{
        Achievement, AchievementName, AchievementRarity, Notification, UserConnections,
        ACHIEVEMENTS,
    },
};
use tetratto_shared::{
    hash::{hash_salted, salt},
    unix_epoch_timestamp,
};
use crate::{auto_method, DataManager};
use oiseau::{PostgresRow, execute, get, query_row, params};

macro_rules! update_role_fn {
    ($name:ident, $role_ty:ty, $col:literal) => {
        pub async fn $name(
            &self,
            id: usize,
            role: $role_ty,
            user: &User,
            force: bool,
        ) -> Result<()> {
            let other_user = self.get_user_by_id(id).await?;

            if !force {
                // check permission
                if !user.permissions.check(FinePermission::MANAGE_USERS) {
                    return Err(Error::NotAllowed);
                }

                if other_user.permissions.check_manager() && !user.permissions.check_admin() {
                    return Err(Error::MiscError(
                        "Cannot manage the role of other managers".to_string(),
                    ));
                }

                if other_user.permissions == user.permissions {
                    return Err(Error::MiscError(
                        "Cannot manage users of equal level to you".to_string(),
                    ));
                }
            }

            // ...
            let conn = match self.0.connect().await {
                Ok(c) => c,
                Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
            };

            let res = execute!(
                &conn,
                &format!("UPDATE users SET {} = $1 WHERE id = $2", $col),
                params![&(role.bits() as i32), &(id as i64)]
            );

            if let Err(e) = res {
                return Err(Error::DatabaseError(e.to_string()));
            }

            self.cache_clear_user(&other_user).await;

            // create audit log entry
            self.create_audit_log_entry(AuditLogEntry::new(
                user.id,
                format!(
                    "invoked `{}` with x value `{}` and y value `{}`",
                    $col,
                    other_user.id,
                    role.bits()
                ),
            ))
            .await?;

            // ...
            Ok(())
        }
    };
}

impl DataManager {
    /// Get a [`User`] from an SQL row.
    pub(crate) fn get_user_from_row(x: &PostgresRow) -> User {
        User {
            id: get!(x->0(i64)) as usize,
            created: get!(x->1(i64)) as usize,
            username: get!(x->2(String)),
            password: get!(x->3(String)),
            salt: get!(x->4(String)),
            settings: serde_json::from_str(&get!(x->5(String)).to_string()).unwrap(),
            tokens: serde_json::from_str(&get!(x->6(String)).to_string()).unwrap(),
            permissions: FinePermission::from_bits(get!(x->7(i32)) as u32).unwrap(),
            is_verified: get!(x->8(i32)) as i8 == 1,
            notification_count: {
                let x = get!(x->9(i32)) as usize;
                // we're a little too close to the maximum count, clearly something's gone wrong
                if x > usize::MAX - 1000 { 0 } else { x }
            },
            follower_count: get!(x->10(i32)) as usize,
            following_count: get!(x->11(i32)) as usize,
            last_seen: get!(x->12(i64)) as usize,
            totp: get!(x->13(String)),
            recovery_codes: serde_json::from_str(&get!(x->14(String)).to_string()).unwrap(),
            post_count: get!(x->15(i32)) as usize,
            request_count: {
                let x = get!(x->16(i32)) as usize;
                if x > usize::MAX - 1000 { 0 } else { x }
            },
            connections: serde_json::from_str(&get!(x->17(String)).to_string()).unwrap(),
            stripe_id: get!(x->18(String)),
            grants: serde_json::from_str(&get!(x->19(String)).to_string()).unwrap(),
            associated: serde_json::from_str(&get!(x->20(String)).to_string()).unwrap(),
            invite_code: get!(x->21(i64)) as usize,
            secondary_permissions: SecondaryPermission::from_bits(get!(x->22(i32)) as u32).unwrap(),
            achievements: serde_json::from_str(&get!(x->23(String)).to_string()).unwrap(),
            awaiting_purchase: get!(x->24(i32)) as i8 == 1,
            was_purchased: get!(x->25(i32)) as i8 == 1,
            ban_reason: get!(x->26(String)),
            is_deactivated: get!(x->27(i32)) as i8 == 1,
            ban_expire: get!(x->28(i64)) as usize,
            checkouts: serde_json::from_str(&get!(x->29(String)).to_string()).unwrap(),
            last_policy_consent: get!(x->30(i64)) as usize,
            close_friends_stack: get!(x->31(i64)) as usize,
            missed_messages_count: get!(x->32(i32)) as usize,
            views: get!(x->33(i32)) as usize,
            shrimpcamp_link: get!(x->34(i64)) as usize,
        }
    }

    auto_method!(get_user_by_id(usize as i64)@get_user_from_row -> "SELECT * FROM users WHERE id = $1" --name="user" --returns=User --cache-key-tmpl="atto.user:{}");
    auto_method!(get_user_by_username(&str)@get_user_from_row -> "SELECT * FROM users WHERE username = $1" --name="user" --returns=User --cache-key-tmpl="atto.user:{}");
    auto_method!(get_user_by_username_no_cache(&str)@get_user_from_row -> "SELECT * FROM users WHERE username = $1" --name="user" --returns=User);
    auto_method!(get_user_by_browser_session(&str)@get_user_from_row -> "SELECT * FROM users WHERE browser_session = $1" --name="user" --returns=User);

    /// Get a user given just their ID. Returns the void user if the user doesn't exist.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    pub async fn get_user_by_id_with_void(&self, id: usize) -> Result<User> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_row!(
            &conn,
            "SELECT * FROM users WHERE id = $1",
            &[&(id as i64)],
            |x| Ok(Self::get_user_from_row(x))
        );

        if res.is_err() {
            return Ok(User::deleted());
            // return Err(Error::UserNotFound);
        }

        Ok(res.unwrap())
    }

    /// Get a user given just their auth token.
    ///
    /// # Arguments
    /// * `token` - the token of the user
    pub async fn get_user_by_token(&self, token: &str) -> Result<User> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_row!(
            &conn,
            "SELECT * FROM users WHERE tokens LIKE $1",
            &[&format!("%\"{token}\"%")],
            |x| Ok(Self::get_user_from_row(x))
        );

        if res.is_err() {
            return Err(Error::UserNotFound);
        }

        Ok(res.unwrap())
    }

    /// Get a user given just their grant token.
    ///
    /// Also returns the auth grant this token is associated with from the user.
    ///
    /// # Arguments
    /// * `token` - the token of the user
    pub async fn get_user_by_grant_token(
        &self,
        token: &str,
        check_expiration: bool,
    ) -> Result<(AuthGrant, User)> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_row!(
            &conn,
            "SELECT * FROM users WHERE grants LIKE $1",
            &[&format!("%\"token\":\"{token}\"%")],
            |x| Ok(Self::get_user_from_row(x))
        );

        if res.is_err() {
            return Err(Error::UserNotFound);
        }

        let user = res.unwrap();
        let grant = user
            .grants
            .iter()
            .find(|x| x.token == token)
            .unwrap()
            .clone();

        // check token expiry
        if check_expiration {
            let now = unix_epoch_timestamp();
            let delta = now - grant.last_updated;

            if delta > 604_800_000 {
                return Err(Error::MiscError("Token expired".to_string()));
            }
        }

        // ...
        Ok((grant, user))
    }

    /// Create a new user in the database.
    ///
    /// # Arguments
    /// * `data` - a mock [`User`] object to insert
    pub async fn create_user(&self, mut data: User) -> Result<()> {
        if !self.0.0.security.registration_enabled {
            return Err(Error::RegistrationDisabled);
        }

        data.username = data.username.to_lowercase();

        // check values
        if data.username.len() < 2 {
            return Err(Error::DataTooShort("username".to_string()));
        } else if data.username.len() > 32 {
            return Err(Error::DataTooLong("username".to_string()));
        }

        if data.password.len() < 6 {
            return Err(Error::DataTooShort("password".to_string()));
        }

        if self.0.0.banned_usernames.contains(&data.username) {
            return Err(Error::MiscError("This username cannot be used".to_string()));
        }

        let regex = regex::RegexBuilder::new(NAME_REGEX)
            .multi_line(true)
            .build()
            .unwrap();

        if regex.captures(&data.username).is_some() {
            return Err(Error::MiscError(
                "This username contains invalid characters".to_string(),
            ));
        }

        // make sure username isn't taken
        if self.get_user_by_username(&data.username).await.is_ok() {
            return Err(Error::UsernameInUse);
        }

        // ...
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "INSERT INTO users VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35)",
            params![
                &(data.id as i64),
                &(data.created as i64),
                &data.username.to_lowercase(),
                &data.password,
                &data.salt,
                &serde_json::to_string(&data.settings).unwrap(),
                &serde_json::to_string(&data.tokens).unwrap(),
                &(FinePermission::DEFAULT.bits() as i32),
                &if data.is_verified { 1_i32 } else { 0_i32 },
                &0_i32,
                &0_i32,
                &0_i32,
                &(data.last_seen as i64),
                &String::new(),
                "[]",
                &0_i32,
                &0_i32,
                &serde_json::to_string(&data.connections).unwrap(),
                &"",
                &serde_json::to_string(&data.grants).unwrap(),
                &serde_json::to_string(&data.associated).unwrap(),
                &(data.invite_code as i64),
                &(SecondaryPermission::DEFAULT.bits() as i32),
                &serde_json::to_string(&data.achievements).unwrap(),
                &if data.awaiting_purchase { 1_i32 } else { 0_i32 },
                &if data.was_purchased { 1_i32 } else { 0_i32 },
                &data.ban_reason,
                &if data.is_deactivated { 1_i32 } else { 0_i32 },
                &(data.ban_expire as i64),
                &serde_json::to_string(&data.checkouts).unwrap(),
                &(data.last_policy_consent as i64),
                &(data.close_friends_stack as i64),
                &(data.missed_messages_count as i32),
                &(data.views as i32),
                &(data.shrimpcamp_link as i64),
            ]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        Ok(())
    }

    /// Delete an existing user in the database.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    /// * `password` - the current password of the user
    /// * `force` - if we should delete even if the given password is incorrect
    pub async fn delete_user(&self, id: usize, password: &str, force: bool) -> Result<User> {
        let user = self.get_user_by_id(id).await?;

        if (hash_salted(password.to_string(), user.salt.clone()) != user.password) && !force {
            return Err(Error::IncorrectPassword);
        }

        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(&conn, "DELETE FROM users WHERE id = $1", &[&(id as i64)]);

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&user).await;

        // delete communities
        for community in self.get_communities_by_owner(user.id).await? {
            self.delete_community(community.id, &user).await?;
        }

        // delete memberships
        // member counts will remain the same... but that should probably be changed
        let res = execute!(
            &conn,
            "DELETE FROM memberships WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete notifications
        let res = execute!(
            &conn,
            "DELETE FROM notifications WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete requests
        let res = execute!(
            &conn,
            "DELETE FROM requests WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete warnings
        let res = execute!(
            &conn,
            "DELETE FROM user_warnings WHERE receiver = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete blocks
        let res = execute!(
            &conn,
            "DELETE FROM userblocks WHERE initiator = $1 OR receiver = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        let res = execute!(
            &conn,
            "DELETE FROM ipblocks WHERE initiator = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete reactions
        // reactions counts will remain the same :)
        let res = execute!(
            &conn,
            "DELETE FROM reactions WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete stacks
        let res = execute!(
            &conn,
            "DELETE FROM stacks WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete drafts
        let res = execute!(
            &conn,
            "DELETE FROM drafts WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete posts
        let res = execute!(&conn, "DELETE FROM posts WHERE owner = $1", &[&(id as i64)]);

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete polls
        let res = execute!(&conn, "DELETE FROM polls WHERE owner = $1", &[&(id as i64)]);

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete poll votes
        let res = execute!(
            &conn,
            "DELETE FROM pollvotes WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete stackblocks
        let res = execute!(
            &conn,
            "DELETE FROM stackblocks WHERE initiator = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete invite codes
        let res = execute!(
            &conn,
            "DELETE FROM invite_codes WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete guest_logs
        let res = execute!(
            &conn,
            "DELETE FROM guest_logs WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete transfers
        let res = execute!(
            &conn,
            "DELETE FROM transfers WHERE sender = $1 OR receiver = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete products
        let res = execute!(
            &conn,
            "DELETE FROM products WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete letters
        let res = execute!(
            &conn,
            "DELETE FROM letters WHERE owner = $1",
            &[&(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete ads
        let res = execute!(&conn, "DELETE FROM ads WHERE owner = $1", &[&(id as i64)]);

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // delete user follows... individually since it requires updating user counts
        for follow in self.get_userfollows_by_receiver_all(id).await? {
            self.delete_userfollow(follow.id, &user, true).await?;
        }

        for follow in self.get_userfollows_by_initiator_all(id).await? {
            self.delete_userfollow(follow.id, &user, true).await?;
        }

        // delete apps
        for app in self.get_apps_by_owner(id).await? {
            self.delete_app(app.id, &user).await?;
        }

        // delete uploads
        for upload in match self.2.get_uploads_by_owner_all(user.id).await {
            Ok(x) => x,
            Err(e) => return Err(Error::MiscError(e.to_string())),
        } {
            if let Err(e) = self.2.delete_upload(upload.id).await {
                return Err(Error::MiscError(e.to_string()));
            }
        }

        // delete polls
        for poll in self.get_polls_by_owner_all(user.id).await? {
            self.delete_poll(poll.id, &user).await?;
        }

        // free up invite code
        if self.0.0.security.enable_invite_codes
            && user.invite_code != 0
            && self.get_invite_code_by_id(user.invite_code).await.is_ok()
        {
            // we're checking if the code is ok because the owner might've deleted their account,
            // deleting all of their invite codes as well
            self.update_invite_code_is_used(user.invite_code, false)
                .await?;
        }

        // ...
        Ok(user)
    }

    pub async fn update_user_verified_status(&self, id: usize, x: bool, user: User) -> Result<()> {
        if !user.permissions.check(FinePermission::MANAGE_VERIFIED) {
            return Err(Error::NotAllowed);
        }

        let other_user = self.get_user_by_id(id).await?;

        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE users SET verified = $1 WHERE id = $2",
            params![&{ if x { 1 } else { 0 } }, &(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&other_user).await;

        // create audit log entry
        self.create_audit_log_entry(AuditLogEntry::new(
            user.id,
            format!(
                "invoked `update_user_verified_status` with x value `{}` and y value `{}`",
                other_user.id, x
            ),
        ))
        .await?;

        // ...
        Ok(())
    }

    pub async fn update_user_is_deactivated(&self, id: usize, x: bool, user: User) -> Result<()> {
        if id != user.id && !user.permissions.check(FinePermission::MANAGE_USERS) {
            return Err(Error::NotAllowed);
        }

        let other_user = self.get_user_by_id(id).await?;

        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE users SET is_deactivated = $1 WHERE id = $2",
            params![&{ if x { 1 } else { 0 } }, &(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&other_user).await;

        // create audit log entry (if we aren't the user that is being updated)
        if user.id != other_user.id {
            self.create_audit_log_entry(AuditLogEntry::new(
                user.id,
                format!(
                    "invoked `update_user_is_deactivated` with x value `{}` and y value `{}`",
                    other_user.id, x
                ),
            ))
            .await?;
        }

        // ...
        Ok(())
    }

    pub async fn update_user_password(
        &self,
        id: usize,
        from: String,
        to: String,
        user: User,
        force: bool,
    ) -> Result<()> {
        // verify password
        if !user.check_password(from.clone()) && !force {
            return Err(Error::MiscError("Password does not match".to_string()));
        }

        // ...
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let new_salt = salt();
        let new_password = hash_salted(to, new_salt.clone());
        let res = execute!(
            &conn,
            "UPDATE users SET password = $1, salt = $2 WHERE id = $3",
            params![&new_password.as_str(), &new_salt.as_str(), &(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&user).await;
        Ok(())
    }

    pub async fn update_user_username(&self, id: usize, to: String, user: User) -> Result<()> {
        // check value
        if to.len() < 2 {
            return Err(Error::DataTooShort("username".to_string()));
        } else if to.len() > 32 {
            return Err(Error::DataTooLong("username".to_string()));
        }

        if self.0.0.banned_usernames.contains(&to) {
            return Err(Error::MiscError("This username cannot be used".to_string()));
        }

        let regex = regex::RegexBuilder::new(r"[^\w_\-\.!]+")
            .multi_line(true)
            .build()
            .unwrap();

        if regex.captures(&to).is_some() {
            return Err(Error::MiscError(
                "This username contains invalid characters".to_string(),
            ));
        }

        // ...
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE users SET username = $1 WHERE id = $2",
            params![&to.to_lowercase(), &(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&user).await;
        Ok(())
    }

    pub async fn update_user_awaiting_purchased_status(
        &self,
        id: usize,
        x: bool,
        user: User,
        require_permission: bool,
    ) -> Result<()> {
        if (user.id != id) | require_permission
            && !user.permissions.check(FinePermission::MANAGE_USERS)
        {
            return Err(Error::NotAllowed);
        }

        let other_user = self.get_user_by_id(id).await?;

        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE users SET awaiting_purchase = $1 WHERE id = $2",
            params![&{ if x { 1 } else { 0 } }, &(id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&other_user).await;

        // create audit log entry
        if user.id != other_user.id {
            self.create_audit_log_entry(AuditLogEntry::new(
                user.id,
                format!(
                    "invoked `update_user_purchased_status` with x value `{}` and y value `{}`",
                    other_user.id, x
                ),
            ))
            .await?;
        }

        // ...
        Ok(())
    }

    pub async fn seen_user(&self, user: &User) -> Result<()> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE users SET last_seen = $1 WHERE id = $2",
            params![&(unix_epoch_timestamp() as i64), &(user.id as i64)]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(user).await;

        Ok(())
    }

    /// Add an achievement to a user.
    ///
    /// Still returns `Ok` if the user already has the achievement.
    #[async_recursion::async_recursion]
    pub async fn add_achievement(
        &self,
        user: &mut User,
        achievement: Achievement,
        check_for_final: bool,
    ) -> Result<()> {
        if user.settings.disable_achievements {
            return Ok(());
        }

        if user.achievements.iter().any(|x| x.name == achievement.name) {
            return Ok(());
        }

        // send notif
        self.create_notification(Notification::new(
            "You've earned a new achievement!".to_string(),
            format!(
                "You've earned the \"{}\" [achievement](/achievements)!",
                achievement.name.title()
            ),
            user.id,
        ))
        .await?;

        // add achievement
        user.achievements.push(achievement);
        self.update_user_achievements(user.id, user.achievements.to_owned())
            .await?;

        // check for final
        if check_for_final && user.achievements.len() + 1 == ACHIEVEMENTS {
            self.add_achievement(user, AchievementName::GetAllOtherAchievements.into(), false)
                .await?;
        }

        // ...
        Ok(())
    }

    /// Fill achievements with their title and description.
    ///
    /// # Returns
    /// `(name, description, rarity, achievement)`
    pub fn fill_achievements(
        &self,
        mut list: Vec<Achievement>,
    ) -> Vec<(String, String, AchievementRarity, Achievement)> {
        let mut out = Vec::new();

        // sort by unlocked desc
        list.sort_by(|a, b| a.unlocked.cmp(&b.unlocked));
        list.reverse();

        // ...
        for x in list {
            out.push((
                x.name.title().to_string(),
                x.name.description().to_string(),
                x.name.rarity(),
                x,
            ))
        }

        out
    }

    /// Validate a given TOTP code for the given profile.
    pub fn check_totp(&self, ua: &User, code: &str) -> bool {
        let totp = ua.totp(Some(
            self.0
                .0
                .host
                .replace("http://", "")
                .replace("https://", "")
                .replace(":", "_"),
        ));

        if let Some(totp) = totp {
            return !code.is_empty()
                && (totp.check_current(code).unwrap()
                    | ua.recovery_codes.contains(&code.to_string()));
        }

        true
    }

    /// Generate 8 random recovery codes for TOTP.
    pub fn generate_totp_recovery_codes() -> Vec<String> {
        let mut out: Vec<String> = Vec::new();

        for _ in 0..9 {
            out.push(salt())
        }

        out
    }

    /// Update the profile's TOTP secret.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    /// * `secret` - the TOTP secret
    /// * `recovery` - the TOTP recovery codes
    pub async fn update_user_totp(
        &self,
        id: usize,
        secret: &str,
        recovery: &Vec<String>,
    ) -> Result<()> {
        let user = self.get_user_by_id(id).await?;

        // update
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE users SET totp = $1, recovery_codes = $2 WHERE id = $3",
            params![
                &secret,
                &serde_json::to_string(recovery).unwrap(),
                &(id as i64)
            ]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.cache_clear_user(&user).await;
        Ok(())
    }

    /// Enable TOTP for a profile.
    ///
    /// # Arguments
    /// * `id` - the ID of the user to enable TOTP for
    /// * `user` - the user doing this
    ///
    /// # Returns
    /// `Result<(secret, qr base64)>`
    pub async fn enable_totp(
        &self,
        id: usize,
        user: User,
    ) -> Result<(String, String, Vec<String>)> {
        let other_user = self.get_user_by_id(id).await?;

        if other_user.id != user.id {
            if other_user.permissions.check(FinePermission::MANAGE_USERS) {
                // create audit log entry
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `enable_totp` with x value `{}`", other_user.id,),
                ))
                .await?;
            } else {
                return Err(Error::NotAllowed);
            }
        }

        let secret = totp_rs::Secret::default().to_string();
        let recovery = Self::generate_totp_recovery_codes();
        self.update_user_totp(id, &secret, &recovery).await?;

        // fetch profile again (with totp information)
        let other_user = self.get_user_by_id(id).await?;

        // get totp
        let totp = other_user.totp(Some(
            self.0
                .0
                .host
                .replace("http://", "")
                .replace("https://", "")
                .replace(":", "_"),
        ));

        if totp.is_none() {
            return Err(Error::MiscError("Failed to get TOTP code".to_string()));
        }

        let totp = totp.unwrap();

        // generate qr
        let qr = match totp.get_qr_base64() {
            Ok(q) => q,
            Err(e) => return Err(Error::MiscError(e.to_string())),
        };

        // return
        Ok((totp.get_secret_base32(), qr, recovery))
    }

    pub async fn cache_clear_user(&self, user: &User) {
        self.0.1.remove(format!("atto.user:{}", user.id)).await;
        self.0
            .1
            .remove(format!("atto.user:{}", user.username))
            .await;
    }

    update_role_fn!(update_user_role, FinePermission, "permissions");
    update_role_fn!(
        update_user_secondary_role,
        SecondaryPermission,
        "secondary_permissions"
    );

    auto_method!(update_user_tokens(Vec<Token>)@get_user_by_id -> "UPDATE users SET tokens = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_grants(Vec<AuthGrant>)@get_user_by_id -> "UPDATE users SET grants = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_settings(UserSettings)@get_user_by_id -> "UPDATE users SET settings = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_connections(UserConnections)@get_user_by_id -> "UPDATE users SET connections = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_associated(Vec<usize>)@get_user_by_id -> "UPDATE users SET associated = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_achievements(Vec<Achievement>)@get_user_by_id -> "UPDATE users SET achievements = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_invite_code(i64)@get_user_by_id -> "UPDATE users SET invite_code = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_browser_session(&str)@get_user_by_id -> "UPDATE users SET browser_session = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_ban_reason(&str)@get_user_by_id -> "UPDATE users SET ban_reason = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_channel_mutes(Vec<usize>)@get_user_by_id -> "UPDATE users SET channel_mutes = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_ban_expire(i64)@get_user_by_id -> "UPDATE users SET ban_expire = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_checkouts(Vec<String>)@get_user_by_id -> "UPDATE users SET checkouts = $1 WHERE id = $2" --serde --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_last_policy_consent(i64)@get_user_by_id -> "UPDATE users SET last_policy_consent = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_close_friends_stack(i64)@get_user_by_id -> "UPDATE users SET close_friends_stack = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(update_user_shrimpcamp_link(i64)@get_user_by_id -> "UPDATE users SET shrimpcamp_link = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);

    auto_method!(get_user_by_stripe_id(&str)@get_user_from_row -> "SELECT * FROM users WHERE stripe_id = $1" --name="user" --returns=User);
    auto_method!(update_user_stripe_id(&str)@get_user_by_id -> "UPDATE users SET stripe_id = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);

    auto_method!(update_user_notification_count(i32)@get_user_by_id -> "UPDATE users SET notification_count = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(incr_user_notifications()@get_user_by_id -> "UPDATE users SET notification_count = notification_count + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_user_notifications()@get_user_by_id -> "UPDATE users SET notification_count = notification_count - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=notification_count);

    auto_method!(incr_user_follower_count()@get_user_by_id -> "UPDATE users SET follower_count = follower_count + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_user_follower_count()@get_user_by_id -> "UPDATE users SET follower_count = follower_count - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=follower_count);

    auto_method!(incr_user_following_count()@get_user_by_id -> "UPDATE users SET following_count = following_count + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_user_following_count()@get_user_by_id -> "UPDATE users SET following_count = following_count - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=following_count);

    auto_method!(incr_user_post_count()@get_user_by_id -> "UPDATE users SET post_count = post_count + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_user_post_count()@get_user_by_id -> "UPDATE users SET post_count = post_count - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=post_count);

    auto_method!(update_user_request_count(i32)@get_user_by_id -> "UPDATE users SET request_count = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(incr_user_request_count()@get_user_by_id -> "UPDATE users SET request_count = request_count + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_user_request_count()@get_user_by_id -> "UPDATE users SET request_count = request_count - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=request_count);

    auto_method!(get_user_by_invite_code(i64)@get_user_from_row -> "SELECT * FROM users WHERE invite_code = $1" --name="user" --returns=User);

    auto_method!(update_user_missed_messages_count(i32)@get_user_by_id -> "UPDATE users SET missed_messages_count = $1 WHERE id = $2" --cache-key-tmpl=cache_clear_user);
    auto_method!(incr_user_missed_messages()@get_user_by_id -> "UPDATE users SET missed_messages_count = missed_messages_count + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_user_missed_messages()@get_user_by_id -> "UPDATE users SET missed_messages_count = missed_messages_count - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=notification_count);

    auto_method!(incr_profile_views()@get_user_by_id -> "UPDATE users SET views = views + 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --incr);
    auto_method!(decr_profile_views()@get_user_by_id -> "UPDATE users SET views = views - 1 WHERE id = $1" --cache-key-tmpl=cache_clear_user --decr=views);
}