tetratto_core/database/

posts.rs

use std::collections::HashMap;
use crate::config::StringBan;
use crate::model::auth::{AchievementName, Notification};
use crate::model::communities::{CommunityMembership, CommunityReadAccess, Poll, Question};
use crate::model::communities_permissions::CommunityPermission;
use crate::model::moderation::AuditLogEntry;
use crate::model::stacks::{StackMode, StackSort, UserStack};
use crate::model::{
    Error, Result,
    auth::User,
    communities::{Community, CommunityWriteAccess, Post, PostContext},
    permissions::FinePermission,
};
use tetratto_shared::unix_epoch_timestamp;
use crate::{auto_method, DataManager};
use oiseau::{PostgresRow, execute, get, query_row, query_rows, params, cache::Cache};

pub type FullPost = (
    Post,
    User,
    Community,
    Option<(User, Post)>,
    Option<(Question, User, Option<(User, Post)>)>,
    Option<(Poll, bool, bool)>,
    Option<UserStack>,
);
pub type FullQuestion = (Question, User, Option<(User, Post)>);

macro_rules! private_post_replying {
    ($post:ident, $replying_posts:ident, $ua1:ident, $data:ident) => {
        // post owner is not following us
        // check if we're the owner of the post the post is replying to
        // all routes but 1 must lead to continue
        if let Some(replying) = $post.replying_to {
            if replying != 0 {
                if let Some(post) = $replying_posts.get(&replying) {
                    // we've seen this post before
                    if post.owner != $ua1.id {
                        // we aren't the owner of this post,
                        // so we can't see their comment
                        continue;
                    }
                } else {
                    // we haven't seen this post before
                    let post = $data.get_post_by_id(replying).await?;

                    if post.owner != $ua1.id {
                        continue;
                    }

                    $replying_posts.insert(post.id, post);
                }
            } else {
                continue;
            }
        } else {
            continue;
        }
    };

    ($post:ident, $replying_posts:ident, id=$user_id:ident, $data:ident) => {
        // post owner is not following us
        // check if we're the owner of the post the post is replying to
        // all routes but 1 must lead to continue
        if let Some(replying) = $post.replying_to {
            if replying != 0 {
                if let Some(post) = $replying_posts.get(&replying) {
                    // we've seen this post before
                    if post.owner != $user_id {
                        // we aren't the owner of this post,
                        // so we can't see their comment
                        continue;
                    }
                } else {
                    // we haven't seen this post before
                    let post = $data.get_post_by_id(replying).await?;

                    if post.owner != $user_id {
                        continue;
                    }

                    $replying_posts.insert(post.id, post);
                }
            } else {
                continue;
            }
        } else {
            continue;
        }
    };
}

impl DataManager {
    /// Get a [`Post`] from an SQL row.
    pub(crate) fn get_post_from_row(x: &PostgresRow) -> Post {
        Post {
            id: get!(x->0(i64)) as usize,
            created: get!(x->1(i64)) as usize,
            content: get!(x->2(String)),
            owner: get!(x->3(i64)) as usize,
            community: get!(x->4(i64)) as usize,
            context: serde_json::from_str(&get!(x->5(String))).unwrap(),
            replying_to: get!(x->6(Option<i64>)).map(|id| id as usize),
            // likes
            likes: get!(x->7(i32)) as isize,
            dislikes: get!(x->8(i32)) as isize,
            // other counts
            comment_count: get!(x->9(i32)) as usize,
            // ...
            uploads: serde_json::from_str(&get!(x->10(String))).unwrap(),
            is_deleted: get!(x->11(i32)) as i8 == 1,
            // SKIP tsvector (12)
            poll_id: get!(x->13(i64)) as usize,
            title: get!(x->14(String)),
            is_open: get!(x->15(i32)) as i8 == 1,
            stack: get!(x->16(i64)) as usize,
            topic: get!(x->17(i64)) as usize,
        }
    }

    auto_method!(get_post_by_id()@get_post_from_row -> "SELECT * FROM posts WHERE id = $1" --name="post" --returns=Post --cache-key-tmpl="atto.post:{}");

    /// Get all posts which are comments on the given post by ID.
    ///
    /// # Arguments
    /// * `id` - the ID of the post the requested posts are commenting on
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_replies_by_post(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        sort: &str,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE replying_to = $1 ORDER BY created {sort} LIMIT $2 OFFSET $3"
            ),
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get the post the given post is reposting (if some).
    pub async fn get_post_reposting(
        &self,
        post: &Post,
        ignore_users: &[usize],
        user: &Option<User>,
    ) -> (bool, Option<(User, Post)>) {
        if let Some(ref repost) = post.context.repost {
            if let Some(reposting) = repost.reposting {
                let mut x = match self.get_post_by_id(reposting).await {
                    Ok(p) => p,
                    Err(_) => return (true, None),
                };

                if x.is_deleted {
                    return (!post.content.is_empty(), None);
                }

                if ignore_users.contains(&x.owner) {
                    return (!post.content.is_empty(), None);
                }

                // check private profile settings
                let owner = match self.get_user_by_id(x.owner).await {
                    Ok(ua) => ua,
                    Err(_) => return (true, None),
                };

                // TODO: maybe check community membership to see if we can MANAGE_POSTS in community
                if owner.settings.private_profile {
                    if let Some(ua) = user {
                        if owner.id != ua.id && !ua.permissions.check(FinePermission::MANAGE_POSTS)
                        {
                            if self
                                .get_userfollow_by_initiator_receiver(owner.id, ua.id)
                                .await
                                .is_err()
                            {
                                // owner isn't following us, we aren't the owner, AND we don't have MANAGE_POSTS permission
                                return (!post.content.is_empty(), None);
                            }
                        }
                    } else {
                        // private profile, but we're an unauthenticated user
                        return (!post.content.is_empty(), None);
                    }
                }

                // ...
                x.mark_as_repost();
                (
                    true,
                    Some((
                        match self.get_user_by_id(x.owner).await {
                            Ok(ua) => ua,
                            Err(_) => return (true, None),
                        },
                        x,
                    )),
                )
            } else {
                (true, None)
            }
        } else {
            (true, None)
        }
    }

    /// Get the question of a given post.
    pub async fn get_post_question(
        &self,
        post: &Post,
        ignore_users: &[usize],
        seen_questions: &mut HashMap<usize, FullQuestion>,
    ) -> Result<Option<FullQuestion>> {
        if post.context.answering != 0 {
            if let Some(q) = seen_questions.get(&post.context.answering) {
                return Ok(Some(q.to_owned()));
            }

            // ...
            let question = self.get_question_by_id(post.context.answering).await?;

            if ignore_users.contains(&question.owner) {
                return Ok(None);
            }

            let user = if question.owner == 0 {
                User::anonymous()
            } else {
                self.get_user_by_id_with_void(question.owner).await?
            };

            let asking_about = self.get_question_asking_about(&question).await?;
            let full_question = (question, user, asking_about);

            seen_questions.insert(post.context.answering, full_question.to_owned());
            Ok(Some(full_question))
        } else {
            Ok(None)
        }
    }

    /// Get the poll of the given post (if some).
    ///
    /// # Returns
    /// `Result<Option<(poll, voted, expired)>>`
    pub async fn get_post_poll(
        &self,
        post: &Post,
        user: &Option<User>,
    ) -> Result<Option<(Poll, bool, bool)>> {
        let user = if let Some(ua) = user {
            ua
        } else {
            return Ok(None);
        };

        if post.poll_id != 0 {
            Ok(Some(match self.get_poll_by_id(post.poll_id).await {
                Ok(p) => {
                    let expired = unix_epoch_timestamp() - p.created > p.expires;
                    (
                        p,
                        self.get_pollvote_by_owner_poll(user.id, post.poll_id)
                            .await
                            .is_ok(),
                        expired,
                    )
                }
                Err(_) => return Err(Error::MiscError("Invalid poll ID attached".to_string())),
            }))
        } else {
            Ok(None)
        }
    }

    /// Get the stack of the given post (if some).
    ///
    /// # Returns
    /// `(can view post, stack)`
    pub async fn get_post_stack(
        &self,
        seen_stacks: &mut HashMap<usize, UserStack>,
        post: &Post,
        as_user_id: usize,
    ) -> (bool, Option<UserStack>) {
        if post.stack != 0 {
            if let Some(s) = seen_stacks.get(&post.stack) {
                (
                    (s.owner == as_user_id) | s.users.contains(&as_user_id),
                    Some(s.to_owned()),
                )
            } else {
                let s = match self.get_stack_by_id(post.stack).await {
                    Ok(s) => s,
                    Err(_) => return (true, None),
                };

                seen_stacks.insert(s.id, s.to_owned());
                (
                    (s.owner == as_user_id) | s.users.contains(&as_user_id),
                    Some(s.to_owned()),
                )
            }
        } else {
            (true, None)
        }
    }

    /// Complete a vector of just posts with their owner as well.
    pub async fn fill_posts(
        &self,
        posts: Vec<Post>,
        ignore_users: &[usize],
        user: &Option<User>,
    ) -> Result<
        Vec<(
            Post,
            User,
            Option<(User, Post)>,
            Option<(Question, User, Option<(User, Post)>)>,
            Option<(Poll, bool, bool)>,
            Option<UserStack>,
        )>,
    > {
        let mut out = Vec::new();

        let mut users: HashMap<usize, User> = HashMap::new();
        let mut seen_user_follow_statuses: HashMap<(usize, usize), bool> = HashMap::new();
        let mut seen_stacks: HashMap<usize, UserStack> = HashMap::new();
        let mut seen_questions: HashMap<usize, FullQuestion> = HashMap::new();
        let mut replying_posts: HashMap<usize, Post> = HashMap::new();

        for post in posts {
            if post.is_deleted {
                continue;
            }

            let owner = post.owner;

            if let Some(ua) = users.get(&owner) {
                // check if owner requires an account to view their posts (and if we have one)
                if ua.settings.require_account && user.is_none() {
                    continue;
                }

                // stack
                let (can_view, stack) = self
                    .get_post_stack(
                        &mut seen_stacks,
                        &post,
                        if let Some(ua) = user { ua.id } else { 0 },
                    )
                    .await;

                if !can_view {
                    continue;
                }

                // reposting
                let (can_view, reposting) =
                    self.get_post_reposting(&post, ignore_users, user).await;

                if !can_view {
                    continue;
                }

                // ...
                out.push((
                    post.clone(),
                    ua.clone(),
                    reposting,
                    self.get_post_question(&post, ignore_users, &mut seen_questions)
                        .await?,
                    self.get_post_poll(&post, user).await?,
                    stack,
                ));
            } else {
                let ua = self.get_user_by_id(owner).await?;

                if ua.settings.require_account && user.is_none() {
                    continue;
                }

                if (ua.permissions.check_banned()
                    | ignore_users.contains(&owner)
                    | ua.is_deactivated)
                    && !ua.permissions.check(FinePermission::MANAGE_POSTS)
                {
                    continue;
                }

                // check relationship
                if ua.settings.private_profile {
                    // if someone were to look for places to optimize memory usage,
                    // look no further than here
                    if let Some(ua1) = user {
                        if ua1.id == 0 {
                            continue;
                        }

                        if ua1.id != ua.id && !ua1.permissions.check(FinePermission::MANAGE_POSTS) {
                            if let Some(is_following) =
                                seen_user_follow_statuses.get(&(ua.id, ua1.id))
                            {
                                if !is_following && ua.id != ua1.id {
                                    private_post_replying!(post, replying_posts, ua1, self);
                                }
                            } else {
                                if self
                                    .get_userfollow_by_initiator_receiver(ua.id, ua1.id)
                                    .await
                                    .is_err()
                                    && ua.id != ua1.id
                                {
                                    // post owner is not following us
                                    seen_user_follow_statuses.insert((ua.id, ua1.id), false);
                                    private_post_replying!(post, replying_posts, ua1, self);
                                }

                                seen_user_follow_statuses.insert((ua.id, ua1.id), true);
                            }
                        }
                    } else {
                        // private post, but not authenticated
                        continue;
                    }
                }

                // stack
                let (can_view, stack) = self
                    .get_post_stack(
                        &mut seen_stacks,
                        &post,
                        if let Some(ua) = user { ua.id } else { 0 },
                    )
                    .await;

                if !can_view {
                    continue;
                }

                // reposting
                let (can_view, reposting) =
                    self.get_post_reposting(&post, ignore_users, user).await;

                if !can_view {
                    continue;
                }

                // ...
                users.insert(owner, ua.clone());
                out.push((
                    post.clone(),
                    ua,
                    reposting,
                    self.get_post_question(&post, ignore_users, &mut seen_questions)
                        .await?,
                    self.get_post_poll(&post, user).await?,
                    stack,
                ));
            }
        }

        Ok(out)
    }

    /// Complete a vector of just posts with their owner and community as well.
    pub async fn fill_posts_with_community(
        &self,
        posts: Vec<Post>,
        user_id: usize,
        ignore_users: &[usize],
        user: &Option<User>,
    ) -> Result<Vec<FullPost>> {
        let mut out = Vec::new();

        let mut seen_before: HashMap<(usize, usize), (User, Community)> = HashMap::new();
        let mut seen_user_follow_statuses: HashMap<(usize, usize), bool> = HashMap::new();
        let mut seen_stacks: HashMap<usize, UserStack> = HashMap::new();
        let mut seen_questions: HashMap<usize, FullQuestion> = HashMap::new();
        let mut replying_posts: HashMap<usize, Post> = HashMap::new();
        let mut memberships: HashMap<usize, CommunityMembership> = HashMap::new();

        for post in posts {
            if post.is_deleted {
                continue;
            }

            let owner = post.owner;
            let community = post.community;

            if let Some((ua, community)) = seen_before.get(&(owner, community)) {
                if ua.settings.require_account && user.is_none() {
                    continue;
                }

                // check membership
                if community.read_access == CommunityReadAccess::Joined {
                    if let Some(user) = user {
                        if let Some(membership) = memberships.get(&community.id) {
                            if !membership.role.check(CommunityPermission::MEMBER) {
                                continue;
                            }
                        } else {
                            if let Ok(membership) = self
                                .get_membership_by_owner_community_no_void(user.id, community.id)
                                .await
                            {
                                if !membership.role.check(CommunityPermission::MEMBER) {
                                    continue;
                                }
                            } else {
                                continue;
                            }
                        }
                    } else {
                        continue;
                    }
                }

                // stack
                let (can_view, stack) = self
                    .get_post_stack(
                        &mut seen_stacks,
                        &post,
                        if let Some(ua) = user { ua.id } else { 0 },
                    )
                    .await;

                if !can_view {
                    continue;
                }

                // reposting
                let (can_view, reposting) =
                    self.get_post_reposting(&post, ignore_users, user).await;

                if !can_view {
                    continue;
                }

                // ...
                out.push((
                    post.clone(),
                    ua.clone(),
                    community.to_owned(),
                    reposting,
                    self.get_post_question(&post, ignore_users, &mut seen_questions)
                        .await?,
                    self.get_post_poll(&post, user).await?,
                    stack,
                ));
            } else {
                let ua = self.get_user_by_id(owner).await?;

                if ua.settings.require_account && user.is_none() {
                    continue;
                }

                if ua.permissions.check_banned() | ignore_users.contains(&owner)
                    && !ua.permissions.check(FinePermission::MANAGE_POSTS)
                {
                    continue;
                }

                // check membership
                let community = self.get_community_by_id(community).await?;
                if community.read_access == CommunityReadAccess::Joined {
                    if let Some(user) = user {
                        if let Some(membership) = memberships.get(&community.id) {
                            if !membership.role.check(CommunityPermission::MEMBER) {
                                continue;
                            }
                        } else {
                            if let Ok(membership) = self
                                .get_membership_by_owner_community_no_void(user.id, community.id)
                                .await
                            {
                                memberships.insert(owner, membership.clone());
                                if !membership.role.check(CommunityPermission::MEMBER) {
                                    continue;
                                }
                            } else {
                                continue;
                            }
                        }
                    } else {
                        continue;
                    }
                }

                // check relationship
                if ua.settings.private_profile && ua.id != user_id {
                    if user_id == 0 {
                        continue;
                    }

                    if user_id != ua.id {
                        if let Some(is_following) = seen_user_follow_statuses.get(&(ua.id, user_id))
                        {
                            if !is_following {
                                private_post_replying!(post, replying_posts, id = user_id, self);
                            }
                        } else {
                            if self
                                .get_userfollow_by_initiator_receiver(ua.id, user_id)
                                .await
                                .is_err()
                            {
                                // post owner is not following us
                                seen_user_follow_statuses.insert((ua.id, user_id), false);
                                private_post_replying!(post, replying_posts, id = user_id, self);
                            }

                            seen_user_follow_statuses.insert((ua.id, user_id), true);
                        }
                    }
                }

                // stack
                let (can_view, stack) = self
                    .get_post_stack(
                        &mut seen_stacks,
                        &post,
                        if let Some(ua) = user { ua.id } else { 0 },
                    )
                    .await;

                if !can_view {
                    continue;
                }

                // reposting
                let (can_view, reposting) =
                    self.get_post_reposting(&post, ignore_users, user).await;

                if !can_view {
                    continue;
                }

                // ...
                seen_before.insert((owner, community.id), (ua.clone(), community.clone()));
                out.push((
                    post.clone(),
                    ua,
                    community,
                    reposting,
                    self.get_post_question(&post, ignore_users, &mut seen_questions)
                        .await?,
                    self.get_post_poll(&post, user).await?,
                    stack,
                ));
            }
        }

        Ok(out)
    }

    /// Update posts which contain a muted phrase.
    pub fn posts_muted_phrase_filter(
        &self,
        posts: &Vec<FullPost>,
        muted: Option<&Vec<String>>,
    ) -> Vec<FullPost> {
        // this shit is actually ass bro it has to clone
        // very useless
        let muted = match muted {
            Some(m) => m,
            None => return posts.to_owned(),
        };

        let mut out: Vec<FullPost> = Vec::new();

        for mut post in posts.clone() {
            for phrase in muted {
                if phrase.is_empty() {
                    continue;
                }

                if post
                    .0
                    .content
                    .to_lowercase()
                    .contains(&phrase.to_lowercase())
                {
                    post.0.context.content_warning = "Contains muted phrase".to_string();
                    break;
                }

                if let Some(ref mut reposting) = post.3 {
                    if reposting
                        .1
                        .content
                        .to_lowercase()
                        .contains(&phrase.to_lowercase())
                    {
                        reposting.1.context.content_warning = "Contains muted phrase".to_string();
                        break;
                    }
                }
            }

            out.push(post);
        }

        out
    }

    /// Filter to update posts to clean their owner for public APIs.
    pub fn posts_owner_filter(&self, posts: &Vec<FullPost>) -> Vec<FullPost> {
        let mut out: Vec<FullPost> = Vec::new();

        for mut post in posts.clone() {
            post.1.clean();

            // reposting
            if let Some((ref mut x, _)) = post.3 {
                x.clean();
            }

            // question
            if let Some((_, ref mut x, ref mut y)) = post.4 {
                x.clean();

                if y.is_some() {
                    y.as_mut().unwrap().0.clean();
                }
            }

            // ...
            out.push(post);
        }

        out
    }

    /// Get all posts from the given user (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_user(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE owner = $1 AND replying_to = 0 AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts (that are answering a question) from the given user (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_responses_by_user(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE owner = $1 AND replying_to = 0 AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 AND NOT context::jsonb->>'answering' = '0' ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all replies from the given user (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_replies_by_user(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        user: &Option<User>,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

        // ...
        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE owner = $1 AND NOT replying_to = 0 AND NOT (context::json->>'is_profile_pinned')::boolean {} ORDER BY created DESC LIMIT $2 OFFSET $3",
                if hide_nsfw {
                    "AND NOT (context::json->>'is_nsfw')::boolean"
                } else {
                    ""
                }
            ),
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts containing media from the given user (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_media_posts_by_user(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        user: &Option<User>,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

        // ...
        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE owner = $1 AND NOT uploads = '[]' AND NOT (context::json->>'is_profile_pinned')::boolean {} ORDER BY created DESC LIMIT $2 OFFSET $3",
                if hide_nsfw {
                    "AND NOT (context::json->>'is_nsfw')::boolean"
                } else {
                    ""
                }
            ),
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts from the given user (searched).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    /// * `text_query` - the search query
    /// * `user` - the user who is viewing the posts
    pub async fn get_posts_by_user_searched(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        text_query: &str,
        user: &Option<&User>,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

        // ...
        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE owner = $1 AND tsvector_content @@ to_tsquery($2) {} AND is_deleted = 0 ORDER BY created DESC LIMIT $3 OFFSET $4",
                if hide_nsfw {
                    "AND NOT (context::json->>'is_nsfw')::boolean"
                } else {
                    ""
                }
            ),
            params![
                &(id as i64),
                &text_query,
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all post (searched).
    ///
    /// # Arguments
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    /// * `text_query` - the search query
    pub async fn get_posts_searched(
        &self,
        batch: usize,
        page: usize,
        text_query: &str,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        // ...
        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE tsvector_content @@ to_tsquery($1) AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            params![&text_query, &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts from the given user with the given tag (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `tag` - the tag to filter by
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_user_tag(
        &self,
        id: usize,
        tag: &str,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE owner = $1 AND context::json->>'tags' LIKE $2 AND is_deleted = 0 ORDER BY created DESC LIMIT $3 OFFSET $4",
            params![
                &(id as i64),
                &format!("%\"{tag}\"%"),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts (that are answering a question) from the given user
    /// with the given tag (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    /// * `tag` - the tag to filter by
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_responses_by_user_tag(
        &self,
        id: usize,
        tag: &str,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE owner = $1 AND context::json->>'tags' LIKE $2 AND is_deleted = 0 AND NOT context::jsonb->>'answering' = '0' ORDER BY created DESC LIMIT $3 OFFSET $4",
            params![
                &(id as i64),
                &format!("%\"{tag}\"%"),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts from the given community (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the community the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_community(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        user: &Option<User>,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

        // ...
        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE community = $1 AND replying_to = 0 AND NOT context LIKE '%\"is_pinned\":true%' AND is_deleted = 0 {} ORDER BY created DESC LIMIT $2 OFFSET $3",
                if hide_nsfw {
                    "AND NOT (context::json->>'is_nsfw')::boolean"
                } else {
                    ""
                }
            ),
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts from the given community and topic (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the community the requested posts belong to
    /// * `topic` - the ID of the topic the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_community_topic(
        &self,
        id: usize,
        topic: usize,
        batch: usize,
        page: usize,
        user: &Option<User>,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

        // ...
        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE community = $1 AND topic = $2 AND replying_to = 0 AND NOT context LIKE '%\"is_pinned\":true%' AND is_deleted = 0 {} ORDER BY created DESC LIMIT $3 OFFSET $4",
                if hide_nsfw {
                    "AND NOT (context::json->>'is_nsfw')::boolean"
                } else {
                    ""
                }
            ),
            &[
                &(id as i64),
                &(topic as i64),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts from the given stack (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the stack the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_stack(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE stack = $1 AND replying_to = 0 AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&(id as i64), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all pinned posts from the given community (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the community the requested posts belong to
    pub async fn get_pinned_posts_by_community(&self, id: usize) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE community = $1 AND context LIKE '%\"is_pinned\":true%' ORDER BY created DESC",
            &[&(id as i64),],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all pinned posts from the given community (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the community the requested posts belong to
    /// * `topic` - the ID of the topic the requested posts belong to
    pub async fn get_pinned_posts_by_community_topic(
        &self,
        id: usize,
        topic: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE community = $1 AND topic = $2 AND context LIKE '%\"is_pinned\":true%' ORDER BY created DESC",
            &[&(id as i64), &(topic as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all pinned posts from the given user (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the user the requested posts belong to
    pub async fn get_pinned_posts_by_user(&self, id: usize) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE owner = $1 AND context LIKE '%\"is_profile_pinned\":true%' ORDER BY created DESC",
            &[&(id as i64),],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all posts answering the given question (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the question the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_question(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE context LIKE $1 AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            params![
                &format!("%\"answering\":{id}%"),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get a post given its owner and question ID.
    ///
    /// # Arguments
    /// * `owner` - the ID of the post owner
    /// * `question` - the ID of the post question
    pub async fn get_post_by_owner_question(&self, owner: usize, question: usize) -> Result<Post> {
        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 posts WHERE context LIKE $1 AND owner = $2 AND is_deleted = 0 LIMIT 1",
            params![&format!("%\"answering\":{question}%"), &(owner as i64),],
            |x| { Ok(Self::get_post_from_row(x)) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all quoting posts by the post their quoting.
    ///
    /// Requires that the post has content. See [`Self::get_reposts_by_quoting`]
    /// for the no-content version.
    ///
    /// # Arguments
    /// * `id` - the ID of the post that is being quoted
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_quoting_posts_by_quoting(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE NOT content = '' AND context LIKE $1 ORDER BY created DESC LIMIT $2 OFFSET $3",
            params![
                &format!("%\"reposting\":{id}%"),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all quoting posts by the post their quoting.
    ///
    /// Requires that the post has no content. See [`Self::get_quoting_posts_by_quoting`]
    /// for the content-required version.
    ///
    /// # Arguments
    /// * `id` - the ID of the post that is being quoted
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_reposts_by_quoting(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE content = '' AND context LIKE $1 ORDER BY created DESC LIMIT $2 OFFSET $3",
            params![
                &format!("%\"reposting\":{id}%"),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get posts from all communities, sorted by likes.
    ///
    /// # Arguments
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    /// * `cutoff` - the maximum number of milliseconds ago the post could have been created
    pub async fn get_popular_posts(
        &self,
        batch: usize,
        page: usize,
        cutoff: usize,
    ) -> Result<Vec<Post>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM posts WHERE replying_to = 0 AND NOT context LIKE '%\"is_nsfw\":true%' AND ($1 - created) < $2 ORDER BY likes - dislikes DESC, created ASC LIMIT $3 OFFSET $4",
            &[
                &(unix_epoch_timestamp() as i64),
                &(cutoff as i64),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get posts from all communities, sorted by creation.
    ///
    /// # Arguments
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_latest_posts(
        &self,
        batch: usize,
        page: usize,
        as_user: &Option<User>,
        before_time: usize,
    ) -> Result<Vec<Post>> {
        let hide_answers: bool = if let Some(user) = as_user {
            user.settings.all_timeline_hide_answers
        } else {
            false
        };

        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = as_user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

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

        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE replying_to = 0{}{}{} AND NOT context LIKE '%\"full_unlist\":true%' AND topic = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
                if before_time > 0 {
                    format!(" AND created < {before_time}")
                } else {
                    String::new()
                },
                if hide_nsfw {
                    " AND NOT context LIKE '%\"is_nsfw\":true%'"
                } else {
                    ""
                },
                if hide_answers {
                    " AND context::jsonb->>'answering' = '0'"
                } else {
                    ""
                }
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get forum posts from all communities, sorted by creation.
    ///
    /// # Arguments
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_latest_forum_posts(
        &self,
        batch: usize,
        page: usize,
        as_user: &Option<User>,
        before_time: usize,
    ) -> Result<Vec<Post>> {
        // check if we should hide nsfw posts
        let mut hide_nsfw: bool = true;

        if let Some(ua) = as_user {
            hide_nsfw = !ua.settings.show_nsfw;
        }

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

        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE replying_to = 0{}{} AND NOT context LIKE '%\"full_unlist\":true%' AND NOT topic = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
                if before_time > 0 {
                    format!(" AND created < {before_time}")
                } else {
                    String::new()
                },
                if hide_nsfw {
                    " AND NOT context LIKE '%\"is_nsfw\":true%'"
                } else {
                    ""
                }
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get posts from all communities the given user is in.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_from_user_communities(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        user: &User,
    ) -> Result<Vec<Post>> {
        let memberships = self.get_memberships_by_owner(id).await?;
        let mut memberships = memberships.iter();
        let first = match memberships.next() {
            Some(f) => f,
            None => return Ok(Vec::new()),
        };

        let mut query_string: String = String::new();

        for membership in memberships {
            query_string.push_str(&format!(" OR community = {}", membership.community));
        }

        // check if we should hide nsfw posts
        let hide_nsfw: bool = !user.settings.show_nsfw;

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

        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE (community = {} {query_string}){} AND NOT context LIKE '%\"full_unlist\":true%' AND replying_to = 0 AND is_deleted = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
                first.community,
                if hide_nsfw {
                    " AND NOT context LIKE '%\"is_nsfw\":true%'"
                } else {
                    ""
                },
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get posts from all users the given user is following.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_from_user_following(
        &self,
        id: usize,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let following = self.get_userfollows_by_initiator_all(id).await?;
        let mut following = following.iter();
        let first = match following.next() {
            Some(f) => f,
            None => return Ok(Vec::new()),
        };

        let mut query_string: String = String::new();

        for user in following {
            query_string.push_str(&format!(" OR owner = {}", user.receiver));
        }

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

        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE (owner = {} {query_string}) AND replying_to = 0 AND is_deleted = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
                first.receiver
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get posts from all users in the given stack.
    ///
    /// # Arguments
    /// * `id` - the ID of the stack
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_from_stack(
        &self,
        id: usize,
        batch: usize,
        page: usize,
        sort: StackSort,
    ) -> Result<Vec<Post>> {
        let users = self.get_stack_by_id(id).await?.users;
        let mut users = users.iter();

        let first = match users.next() {
            Some(f) => f,
            None => return Ok(Vec::new()),
        };

        let mut query_string: String = String::new();

        for user in users {
            query_string.push_str(&format!(" OR owner = {}", user));
        }

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

        let res = query_rows!(
            &conn,
            &format!(
                "SELECT * FROM posts WHERE (owner = {} {query_string}) AND replying_to = 0 AND is_deleted = 0 ORDER BY {} DESC LIMIT $1 OFFSET $2",
                first,
                if sort == StackSort::Created {
                    "created"
                } else {
                    "likes"
                }
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("post".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Check if the given `uid` can post in the given `community`.
    pub async fn check_can_post(&self, community: &Community, uid: usize) -> bool {
        match community.write_access {
            CommunityWriteAccess::Owner => uid == community.owner,
            CommunityWriteAccess::Joined => {
                match self
                    .get_membership_by_owner_community(uid, community.id)
                    .await
                {
                    Ok(m) => m.role.check_member(),
                    Err(_) => false,
                }
            }
            _ => true,
        }
    }

    /// Check if the given `uid` can post in the given `community` with the given `access`.
    pub async fn check_can_post_with_access(
        &self,
        community: &Community,
        access: &CommunityWriteAccess,
        uid: usize,
    ) -> bool {
        match *access {
            CommunityWriteAccess::Owner => uid == community.owner,
            CommunityWriteAccess::Joined => {
                match self
                    .get_membership_by_owner_community(uid, community.id)
                    .await
                {
                    Ok(m) => m.role.check_member(),
                    Err(_) => false,
                }
            }
            _ => true,
        }
    }

    /// Create a new post in the database.
    ///
    /// # Arguments
    /// * `data` - a mock [`Post`] object to insert
    pub async fn create_post(&self, mut data: Post) -> Result<usize> {
        // check characters
        for ban in &self.0.0.banned_data {
            match ban {
                StringBan::String(x) => {
                    if data.content.contains(x) {
                        return Ok(0);
                    }
                }
                StringBan::Unicode(x) => {
                    if data.content.contains(&match char::from_u32(x.to_owned()) {
                        Some(c) => c.to_string(),
                        None => continue,
                    }) {
                        return Ok(0);
                    }
                }
            }
        }

        // check stack
        if data.stack != 0 {
            let stack = self.get_stack_by_id(data.stack).await?;

            if stack.mode != StackMode::Circle {
                return Err(Error::MiscError(
                    "You must use a \"Circle\" stack for this".to_string(),
                ));
            }

            if stack.owner != data.owner && !stack.users.contains(&data.owner) {
                return Err(Error::NotAllowed);
            }
        }

        // ...
        let community = if data.stack != 0 {
            // if we're posting to a stack, the community should always be the town square
            data.community = self.0.0.town_square;
            self.get_community_by_id(self.0.0.town_square).await?
        } else {
            // otherwise, load whatever community the post is requesting
            self.get_community_by_id(data.community).await?
        };

        // check is_forum
        if community.is_forum {
            if data.topic == 0 {
                return Err(Error::MiscError(
                    "Topic is required for this community".to_string(),
                ));
            }

            if let Some(topic) = community.topics.get(&data.topic) {
                // check permission
                if !self
                    .check_can_post_with_access(&community, &topic.write_access, data.owner)
                    .await
                {
                    return Err(Error::NotAllowed);
                }
            } else {
                return Err(Error::GeneralNotFound("topic".to_string()));
            }
        } else if data.topic != 0 {
            return Err(Error::DoesNotSupportField("Community".to_string()));
        }

        // ...
        let mut owner = self.get_user_by_id(data.owner).await?;

        // check values (if this isn't reposting something else)
        let is_reposting = if let Some(ref repost) = data.context.repost {
            repost.reposting.is_some()
        } else {
            false
        };

        if !is_reposting {
            if data.content.len() < 2 && data.uploads.len() == 0 {
                return Err(Error::DataTooShort("content".to_string()));
            } else if data.content.len() > 4096 {
                return Err(Error::DataTooLong("content".to_string()));
            }

            // check title
            if !community.context.enable_titles {
                if !data.title.is_empty() {
                    return Err(Error::MiscError(
                        "Community does not allow titles".to_string(),
                    ));
                }
            } else if data.replying_to.is_none() {
                if data.title.trim().len() < 2 && community.context.require_titles {
                    return Err(Error::DataTooShort("title".to_string()));
                } else if data.title.len() > 128 {
                    return Err(Error::DataTooLong("title".to_string()));
                }

                // award achievement
                self.add_achievement(
                    &mut owner,
                    AchievementName::CreatePostWithTitle.into(),
                    true,
                )
                .await?;
            }
        }

        // check permission in community
        if !self.check_can_post(&community, data.owner).await {
            return Err(Error::NotAllowed);
        }

        // mirror nsfw state
        data.context.is_nsfw = community.context.is_nsfw;

        // remove request if we were answering a question
        if data.context.answering != 0 {
            let question = self.get_question_by_id(data.context.answering).await?;

            // check if we've already answered this
            if self
                .get_post_by_owner_question(owner.id, question.id)
                .await
                .is_ok()
            {
                return Err(Error::MiscError(
                    "You've already answered this question".to_string(),
                ));
            }

            if !question.is_global {
                self.delete_request(question.id, question.id, &owner, false)
                    .await?;
            } else {
                self.incr_question_answer_count(data.context.answering)
                    .await?;
            }

            // create notification for question owner
            // (if the current user isn't the owner)
            if (question.owner != data.owner)
                && (question.owner != 0)
                && (!owner.settings.private_profile
                    | self
                        .get_userfollow_by_initiator_receiver(data.owner, question.owner)
                        .await
                        .is_ok())
            {
                self.create_notification(Notification::new(
                    "Your question has received a new answer!".to_string(),
                    format!(
                        "[@{}](/api/v1/auth/user/find/{}) has answered your [question](/question/{}).",
                        owner.username, owner.id, question.id
                    ),
                    question.owner,
                ))
                .await?;
            }

            // inherit nsfw status if we didn't get it from the community
            if question.context.is_nsfw {
                data.context.is_nsfw = question.context.is_nsfw;
            }
        }

        // check if we're reposting a post
        let reposting = if let Some(ref repost) = data.context.repost {
            if let Some(id) = repost.reposting {
                Some(self.get_post_by_id(id).await?)
            } else {
                None
            }
        } else {
            None
        };

        if let Some(ref rt) = reposting {
            if rt.stack != data.stack && rt.stack != 0 {
                return Err(Error::MiscError("Cannot repost out of stack".to_string()));
            }

            if data.content.is_empty() {
                // reposting but NOT quoting... we shouldn't be able to repost a direct repost
                data.context.reposts_enabled = false;
                data.context.reactions_enabled = false;
                data.context.comments_enabled = false;
            }

            // mirror nsfw status
            if rt.context.is_nsfw {
                data.context.is_nsfw = true;
            }

            // // make sure we aren't trying to repost a repost
            // if if let Some(ref repost) = rt.context.repost {
            //     repost.reposting.is_some()
            // } else {
            //     false
            // } {
            //     return Err(Error::MiscError("Cannot repost a repost".to_string()));
            // }

            // ...
            if !rt.context.reposts_enabled {
                return Err(Error::MiscError("Post has reposts disabled".to_string()));
            }

            // check blocked status
            if self
                .get_userblock_by_initiator_receiver(rt.owner, data.owner)
                .await
                .is_ok()
                | self
                    .get_user_stack_blocked_users(rt.owner)
                    .await
                    .contains(&data.owner)
            {
                return Err(Error::NotAllowed);
            }

            // send notification
            // this would look better if rustfmt didn't give up on this line
            if owner.id != rt.owner && !owner.settings.private_profile && data.stack == 0 {
                self.create_notification(
                    Notification::new(
                        format!(
                            "[@{}](/api/v1/auth/user/find/{}) has [quoted](/post/{}) your [post](/post/{})",
                            owner.username,
                            owner.id,
                            data.id,
                            rt.id
                        ),
                        if data.content.is_empty() {
                            String::new()
                        } else {
                            format!("\"{}\"", data.content)
                        },
                        rt.owner
                    )
                )
                .await?;
            }

            // award achievement
            self.add_achievement(&mut owner, AchievementName::CreateRepost.into(), true)
                .await?;
        }

        // check if the post we're replying to allows commments
        let replying_to = if let Some(id) = data.replying_to {
            Some(self.get_post_by_id(id).await?)
        } else {
            None
        };

        if let Some(ref rt) = replying_to {
            if !rt.context.comments_enabled {
                return Err(Error::MiscError("Post has comments disabled".to_string()));
            }

            // check blocked status
            if self
                .get_userblock_by_initiator_receiver(rt.owner, data.owner)
                .await
                .is_ok()
                | self
                    .get_user_stack_blocked_users(rt.owner)
                    .await
                    .contains(&data.owner)
            {
                return Err(Error::NotAllowed);
            }
        }

        // send mention notifications
        let mut already_notified: HashMap<String, User> = HashMap::new();
        for username in User::parse_mentions(&data.content) {
            let user = {
                if let Some(ua) = already_notified.get(&username) {
                    ua.to_owned()
                } else {
                    let user = self.get_user_by_username(&username).await?;

                    // check blocked status
                    if self
                        .get_userblock_by_initiator_receiver(user.id, data.owner)
                        .await
                        .is_ok()
                        | self
                            .get_user_stack_blocked_users(user.id)
                            .await
                            .contains(&data.owner)
                    {
                        return Err(Error::NotAllowed);
                    }

                    // check private status
                    if user.settings.private_profile {
                        if self
                            .get_userfollow_by_initiator_receiver(user.id, data.owner)
                            .await
                            .is_err()
                        {
                            return Err(Error::NotAllowed);
                        }
                    }

                    // send notif
                    self.create_notification(Notification::new(
                        "You've been mentioned in a post!".to_string(),
                        format!(
                            "[@{}](/api/v1/auth/user/find/{}) has mentioned you in their [post](/post/{}).",
                            owner.username, owner.id, data.id
                        ),
                        user.id,
                    ))
                    .await?;

                    // ...
                    already_notified.insert(username.to_owned(), user.clone());
                    user
                }
            };

            data.content = data.content.replace(
                &format!("@{username}"),
                &format!("[@{username}](/api/v1/auth/user/find/{})", user.id),
            );
        }

        // auto unlist
        if owner.settings.auto_unlist {
            data.context.is_nsfw = true;
        }

        if owner.settings.auto_full_unlist {
            data.context.full_unlist = true;
        }

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

        let replying_to_id = data.replying_to.unwrap_or(0).to_string();

        let res = execute!(
            &conn,
            "INSERT INTO posts VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, DEFAULT, $13, $14, $15, $16, $17)",
            params![
                &(data.id as i64),
                &(data.created as i64),
                &data.content,
                &(data.owner as i64),
                &(data.community as i64),
                &serde_json::to_string(&data.context).unwrap(),
                &if replying_to_id != "0" {
                    replying_to_id.parse::<i64>().unwrap()
                } else {
                    0_i64
                },
                &0_i32,
                &0_i32,
                &0_i32,
                &serde_json::to_string(&data.uploads).unwrap(),
                &{ if data.is_deleted { 1 } else { 0 } },
                &(data.poll_id as i64),
                &data.title,
                &{ if data.is_open { 1 } else { 0 } },
                &(data.stack as i64),
                &(data.topic as i64),
            ]
        );

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

        // incr comment count and send notification
        if let Some(rt) = replying_to {
            self.incr_post_comments(rt.id).await.unwrap();

            // send notification
            if data.owner != rt.owner {
                let owner = self.get_user_by_id(data.owner).await?;

                // make sure we're actually following the person we're commenting to
                // we shouldn't send the notif if we aren't, because they can't see it
                // (only if our profile is private)
                if !owner.settings.private_profile
                    | self
                        .get_userfollow_by_initiator_receiver(data.owner, rt.owner)
                        .await
                        .is_ok()
                {
                    self.create_notification(Notification::new(
                        "Your post has received a new comment!".to_string(),
                        format!(
                            "[@{}](/api/v1/auth/user/find/{}) has commented on your [post](/post/{}).",
                            owner.username, owner.id, rt.id
                        ),
                        rt.owner,
                    ))
                    .await?;
                }

                if !rt.context.comments_enabled {
                    return Err(Error::NotAllowed);
                }
            }
        }

        // increase user post count
        self.incr_user_post_count(data.owner).await?;

        // increase community post count
        self.incr_community_post_count(data.community).await?;

        // return
        Ok(data.id)
    }

    pub async fn delete_post(&self, id: usize, user: User) -> Result<()> {
        let y = self.get_post_by_id(id).await?;

        let user_membership = self
            .get_membership_by_owner_community(user.id, y.community)
            .await?;

        if (user.id != y.owner)
            && !user_membership
                .role
                .check(CommunityPermission::MANAGE_POSTS)
        {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `delete_post` with x value `{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, "DELETE FROM posts WHERE id = $1", &[&(id as i64)]);

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

        self.0.1.remove(format!("atto.post:{}", id)).await;

        // decr parent comment count
        if let Some(replying_to) = y.replying_to {
            if replying_to != 0 {
                self.decr_post_comments(replying_to).await.unwrap();
            }
        }

        // decr user post count
        let owner = self.get_user_by_id(y.owner).await?;

        if owner.post_count > 0 {
            self.decr_user_post_count(y.owner).await?;
        }

        // decr community post count
        let community = self.get_community_by_id_no_void(y.community).await?;

        if community.post_count > 0 {
            self.decr_community_post_count(y.community).await?;
        }

        // decr question answer count
        if y.context.answering != 0 {
            let question = self.get_question_by_id(y.context.answering).await?;

            if question.is_global {
                self.decr_question_answer_count(y.context.answering).await?;
            }
        }

        // delete uploads
        for upload in y.uploads {
            self.delete_upload(upload).await?;
        }

        // remove poll
        if y.poll_id != 0 {
            self.delete_poll(y.poll_id, &user).await?;
        }

        // delete question (if not global question)
        if y.context.answering != 0 {
            let question = self.get_question_by_id(y.context.answering).await?;

            if !question.is_global {
                self.delete_question(question.id, &user).await?;
            }
        }

        // return
        Ok(())
    }

    pub async fn fake_delete_post(&self, id: usize, user: User, is_deleted: bool) -> Result<()> {
        let y = self.get_post_by_id(id).await?;

        let user_membership = self
            .get_membership_by_owner_community(user.id, y.community)
            .await?;

        if (user.id != y.owner)
            && !user_membership
                .role
                .check(CommunityPermission::MANAGE_POSTS)
        {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `fake_delete_post` with x value `{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 posts SET is_deleted = $1 WHERE id = $2",
            params![&if is_deleted { 1 } else { 0 }, &(id as i64)]
        );

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

        self.0.1.remove(format!("atto.post:{}", id)).await;

        if is_deleted {
            // decr parent comment count
            if let Some(replying_to) = y.replying_to {
                if replying_to != 0 {
                    self.decr_post_comments(replying_to).await.unwrap();
                }
            }

            // decr user post count
            let owner = self.get_user_by_id(y.owner).await?;

            if owner.post_count > 0 {
                self.decr_user_post_count(y.owner).await?;
            }

            // decr community post count
            let community = self.get_community_by_id_no_void(y.community).await?;

            if community.post_count > 0 {
                self.decr_community_post_count(y.community).await?;
            }

            // decr question answer count
            if y.context.answering != 0 {
                let question = self.get_question_by_id(y.context.answering).await?;

                if question.is_global {
                    self.decr_question_answer_count(y.context.answering).await?;
                }
            }

            // delete uploads
            for upload in y.uploads {
                self.delete_upload(upload).await?;
            }

            // delete question (if not global question)
            if y.context.answering != 0 {
                let question = self.get_question_by_id(y.context.answering).await?;

                if !question.is_global {
                    self.delete_question(question.id, &user).await?;
                }
            }
        } else {
            // incr parent comment count
            if let Some(replying_to) = y.replying_to {
                self.incr_post_comments(replying_to).await.unwrap();
            }

            // incr user post count
            self.incr_user_post_count(y.owner).await?;

            // incr community post count
            self.incr_community_post_count(y.community).await?;

            // incr question answer count
            if y.context.answering != 0 {
                let question = self.get_question_by_id(y.context.answering).await?;

                if question.is_global {
                    self.incr_question_answer_count(y.context.answering).await?;
                }
            }

            // unfortunately, uploads will not be restored
        }

        // return
        Ok(())
    }

    pub async fn update_post_is_open(&self, id: usize, user: User, is_open: bool) -> Result<()> {
        let y = self.get_post_by_id(id).await?;

        // make sure this is a forge community
        let community = self.get_community_by_id(y.community).await?;

        if !community.is_forge {
            return Err(Error::MiscError(
                "This community does not support this".to_string(),
            ));
        }

        // check permissions
        let user_membership = self
            .get_membership_by_owner_community(user.id, y.community)
            .await?;

        if (user.id != y.owner)
            && !user_membership
                .role
                .check(CommunityPermission::MANAGE_POSTS)
        {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `update_post_is_open` with x value `{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 posts SET is_open = $1 WHERE id = $2",
            params![&if is_open { 1 } else { 0 }, &(id as i64)]
        );

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

        self.0.1.remove(format!("atto.post:{}", id)).await;
        Ok(())
    }

    pub async fn update_post_context(
        &self,
        id: usize,
        user: User,
        mut x: PostContext,
    ) -> Result<()> {
        let y = self.get_post_by_id(id).await?;
        x.repost = y.context.repost; // cannot change repost settings at all
        x.answering = y.context.answering; // cannot change answering settings at all

        let user_membership = self
            .get_membership_by_owner_community(user.id, y.community)
            .await?;

        if (user.id != y.owner)
            && !user_membership
                .role
                .check(CommunityPermission::MANAGE_POSTS)
        {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `update_post_context` with x value `{id}`"),
                ))
                .await?
            }
        }

        // check if we can manage pins
        if x.is_pinned != y.context.is_pinned
            && !user_membership.role.check(CommunityPermission::MANAGE_PINS)
        {
            // lacking this permission is overtaken by having the MANAGE_POSTS
            // global permission
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `update_post_context(pinned)` with x value `{id}`"),
                ))
                .await?
            }
        }

        // check if we can manage profile pins
        if (x.is_profile_pinned != y.context.is_profile_pinned) && (user.id != y.owner) {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `update_post_context(profile_pinned)` with x value `{id}`"),
                ))
                .await?
            }
        }

        // auto unlist
        if user.settings.auto_unlist {
            x.is_nsfw = true;
        }

        if user.settings.auto_full_unlist {
            x.full_unlist = true;
        }

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

        let res = execute!(
            &conn,
            "UPDATE posts SET context = $1 WHERE id = $2",
            params![&serde_json::to_string(&x).unwrap(), &(id as i64)]
        );

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

        self.0.1.remove(format!("atto.post:{}", id)).await;

        // return
        Ok(())
    }

    pub async fn update_post_content(&self, id: usize, user: User, x: String) -> Result<()> {
        let mut y = self.get_post_by_id(id).await?;

        if user.id != y.owner {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `update_post_content` with x value `{id}`"),
                ))
                .await?
            }
        }

        // check length
        if x.len() < 2 {
            return Err(Error::DataTooShort("content".to_string()));
        } else if x.len() > 4096 {
            return Err(Error::DataTooLong("content".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 posts SET content = $1 WHERE id = $2",
            params![&x, &(id as i64)]
        );

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

        // update context
        y.context.edited = unix_epoch_timestamp();
        self.update_post_context(id, user, y.context).await?;

        // return
        Ok(())
    }

    pub async fn update_post_title(&self, id: usize, user: User, x: String) -> Result<()> {
        let mut y = self.get_post_by_id(id).await?;

        if user.id != y.owner {
            if !user.permissions.check(FinePermission::MANAGE_POSTS) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id,
                    format!("invoked `update_post_title` with x value `{id}`"),
                ))
                .await?
            }
        }

        let community = self.get_community_by_id(y.community).await?;

        if !community.context.enable_titles {
            return Err(Error::MiscError(
                "Community does not allow titles".to_string(),
            ));
        }

        if x.len() < 2 && community.context.require_titles {
            return Err(Error::DataTooShort("title".to_string()));
        } else if x.len() > 128 {
            return Err(Error::DataTooLong("title".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 posts SET title = $1 WHERE id = $2",
            params![&x, &(id as i64)]
        );

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

        // update context
        y.context.edited = unix_epoch_timestamp();
        self.update_post_context(id, user, y.context).await?;

        // return
        Ok(())
    }

    auto_method!(incr_post_likes() -> "UPDATE posts SET likes = likes + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
    auto_method!(incr_post_dislikes() -> "UPDATE posts SET dislikes = dislikes + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
    auto_method!(decr_post_likes() -> "UPDATE posts SET likes = likes - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr);
    auto_method!(decr_post_dislikes() -> "UPDATE posts SET dislikes = dislikes - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr);

    auto_method!(incr_post_comments() -> "UPDATE posts SET comment_count = comment_count + 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --incr);
    auto_method!(decr_post_comments()@get_post_by_id -> "UPDATE posts SET comment_count = comment_count - 1 WHERE id = $1" --cache-key-tmpl="atto.post:{}" --decr=comment_count);
}