use std::collections::HashMap;
use crate::config::StringBan;
use crate::model::groups::GroupRole;
use crate::model::{
    Error, Result,
    auth::User,
    posts::{Post, PostContext},
    permissions::FinePermission,
    moderation::AuditLogEntry,
    auth::{AchievementName, Notification},
    id::Id,
};
use tritools::time::unix_epoch_timestamp;
use crate::{auto_method, DataManager};
use oiseau::{PostgresRow, execute, get, query_row, query_rows, params, cache::Cache};

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

    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: &Id,
        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 replying_to = $1 ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&id.printable(), &(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 (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: &Id, 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 IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&id.printable(), &(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 group (from most recent).
    ///
    /// # Arguments
    /// * `id` - the ID of the group the requested posts belong to
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_by_group(
        &self,
        id: &Id,
        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 group_id = $1 AND replying_to IS NULL AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&id.printable(), &(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 (sorted by likes - dislikes).
    ///
    /// # 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_popular_posts_by_user(
        &self,
        id: &Id,
        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 IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 ORDER BY (likes - dislikes) DESC, created DESC LIMIT $2 OFFSET $3",
            &[&id.printable(), &(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: &Id,
        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 IS NULL AND NOT (context::json->>'is_profile_pinned')::boolean AND is_deleted = 0 AND NOT context LIKE '%\"answering\":null%' ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&id.printable(), &(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: &Id,
        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 IS NULL 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.printable(), &(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: &Id,
        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.printable(), &(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: &Id,
        text_query: &str,
        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 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.printable(),
                &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
    /// * `text_query` - the search query
    /// * `batch` - the limit of posts in each page
    /// * `page` - the page number
    pub async fn get_posts_searched(
        &self,
        text_query: &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 tsvector_content @@ to_tsquery($1) AND is_deleted = 0 ORDER BY created DESC LIMIT $2 OFFSET $3",
            params![
                &text_query.replace(" ", " & "),
                &(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: &Id,
        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.printable(),
                &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: &Id,
        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.printable(),
                &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 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: &Id) -> 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.printable()],
            |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: &Id,
        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: &Id, question: &Id) -> 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.printable(),
            ],
            |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: &Id,
        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: &Id,
        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
    /// * `before` - the timestamp to pull posts before
    /// * `cutoff` - the maximum number of milliseconds ago the post could have been created
    pub async fn get_popular_posts(
        &self,
        batch: usize,
        before: 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,
            &format!(
                "SELECT * FROM posts WHERE replying_to IS NULL AND limited_reach = 0 AND NOT context LIKE '%\"is_nsfw\":true%' AND ($1 - created) < $2{} ORDER BY (likes - dislikes) DESC, created ASC LIMIT $3",
                if before > 0 {
                    format!(" AND created < {before}")
                } else {
                    String::new()
                }
            ),
            &[
                &(unix_epoch_timestamp() as i64),
                &(cutoff as i64),
                &(batch as i64),
            ],
            |x| { Self::get_post_from_row(x) }
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.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,
        as_user: &Option<&User>,
        batch: usize,
        page: 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;
        let mut can_view_deleted: bool = false;

        if let Some(ua) = as_user {
            hide_nsfw = !ua.settings.show_nsfw;
            can_view_deleted = ua.permissions.check(FinePermission::ManagePosts);
        }

        // ...
        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 IS NULL AND limited_reach = 0 AND group_id IS NULL{}{}{} ORDER BY created DESC LIMIT $1 OFFSET $2",
                if hide_nsfw {
                    " AND NOT context LIKE '%\"is_nsfw\":true%'"
                } else {
                    ""
                },
                if hide_answers {
                    " AND context LIKE '%\"answering\":null%'"
                } else {
                    ""
                },
                if !can_view_deleted {
                    " AND is_deleted = 0"
                } else {
                    ""
                }
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.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: &Id,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let following = self.get_user_follows_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 = '{id}' OR owner = '{}' {query_string}) AND replying_to IS NULL 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 let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get posts from all groups the 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_groups(
        &self,
        id: &Id,
        batch: usize,
        page: usize,
    ) -> Result<Vec<Post>> {
        let groups = self.get_group_memberships_group_by_owner_all(id).await?;
        let mut groups = groups.iter();
        let first = match groups.next() {
            Some(f) => f,
            None => return Ok(Vec::new()),
        };

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

        for id in groups {
            query_string.push_str(&format!(" OR group_id = '{}'", id));
        }

        // ...
        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 group_id = '{first}' {query_string} AND replying_to IS NULL AND is_deleted = 0 ORDER BY created DESC LIMIT $1 OFFSET $2",
            ),
            &[&(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_post_from_row(x) }
        );

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

        Ok(res.unwrap())
    }

    /// 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<Id> {
        // check characters
        for ban in &self.0.0.banned_data {
            match ban {
                StringBan::String(x) => {
                    if data.content.contains(x) {
                        return Ok(Id::Legacy(0));
                    }
                }
                StringBan::Unicode(x) => {
                    if data.content.contains(&match char::from_u32(x.to_owned()) {
                        Some(c) => c.to_string(),
                        None => continue,
                    }) {
                        return Ok(Id::Legacy(0));
                    }
                }
            }
        }

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

        // check group
        if let Some(ref group) = data.group {
            // must at least be a member (not pending)
            let membership = self
                .get_group_membership_by_owner_group(&owner.id, group)
                .await?;

            if membership.role == GroupRole::Pending || membership.role == GroupRole::Banned {
                return Err(Error::NotAllowed);
            }
        }

        // 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.is_empty() {
                return Err(Error::DataTooShort("content".to_string()));
            } else if data.content.len() > 4096 {
                return Err(Error::DataTooLong("content".to_string()));
            }
        }

        // remove request if we were answering a question
        if let Some(ref answering) = data.context.answering {
            let question = self.get_question_by_id(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(answering).await?;
            }

            // create notification for question owner
            // (if the current user isn't the owner)
            if (question.owner != data.owner)
                && (question.owner != Id::Legacy(0))
                && (!owner.settings.private_profile
                    | self
                        .get_user_follow_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/v2/users/find/{}) has answered your [question](/questions/{}).",
                        owner.username, owner.id, question.id
                    ),
                    question.owner,
                ))
                .await?;
            }

            // inherit nsfw status
            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(ref id) = repost.reposting {
                Some(self.get_post_by_id(id).await?)
            } else {
                None
            }
        } else {
            None
        };

        if let Some(ref rt) = reposting {
            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;
            }

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

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

            // send notification
            if owner.id != rt.owner && !owner.settings.private_profile {
                self.create_notification(Notification::new(
                    format!(
                        "[@{}](/api/v2/users/find/{}) has [quoted](/posts/{}) your [post](/posts/{})",
                        owner.username, owner.id, data.id, rt.id
                    ),
                    if data.content.is_empty() {
                        String::new()
                    } else {
                        format!("\"{}\"", data.content)
                    },
                    rt.owner.to_owned(),
                ))
                .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(ref 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_user_block_by_initiator_receiver(&rt.owner, &data.owner)
                .await
                .is_ok()
            {
                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_user_block_by_initiator_receiver(&user.id, &data.owner)
                        .await
                        .is_ok()
                    {
                        return Err(Error::NotAllowed);
                    }

                    // check private status
                    if user.settings.private_profile
                        && self
                            .get_user_follow_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/v2/users/find/{}) has mentioned you in their [post](/posts/{}).",
                            owner.username, owner.id, data.id
                        ),
                        user.id.to_owned(),
                    ))
                    .await?;

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

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

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

        // ...
        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 posts VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, DEFAULT, $12, $13, $14, $15)",
            params![
                &data.id.printable(),
                &(data.created as i64),
                &data.content,
                &data.owner.printable(),
                &serde_json::to_string(&data.context).unwrap(),
                &data.replying_to.map(|x| x.printable()),
                &0_i32,
                &0_i32,
                &0_i32,
                &serde_json::to_string(&data.uploads).unwrap(),
                &{ if data.is_deleted { 1 } else { 0 } },
                &data.poll_id.map(|x| x.printable()),
                &0_i32,
                &data.group.map(|x| x.printable()),
                &{ if data.limited_reach { 1 } else { 0 } },
            ]
        );

        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_user_follow_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/v2/users/find/{}) has commented on your [post](/posts/{}).",
                            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?;

        // return
        Ok(data.id)
    }

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

        if user.id != y.owner {
            if !user.permissions.check(FinePermission::ManagePosts) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id.to_owned(),
                    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.printable()]);

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

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

        if !y.is_deleted {
            // decr parent comment count
            if let Some(replying_to) = y.replying_to {
                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 question answer count
            if let Some(answering) = y.context.answering {
                let question = self.get_question_by_id(&answering).await?;

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

        // delete uploads
        for upload in y.uploads {
            if let Err(e) = self.1.delete_upload(upload).await {
                return Err(Error::MiscError(e.to_string()));
            }
        }

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

        // return
        Ok(())
    }

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

        macro_rules! del {
            ($user:ident, $self:ident) => {
                if !$user.permissions.check(FinePermission::ManagePosts) {
                    return Err(Error::NotAllowed);
                } else {
                    $self
                        .create_audit_log_entry(AuditLogEntry::new(
                            user.id.to_owned(),
                            format!("invoked `fake_delete_post` with x value `{id}`"),
                        ))
                        .await?
                }
            };
        }

        if user.id != y.owner {
            if let Some(ref id) = y.group {
                if let Ok(x) = self.get_group_membership_by_owner_group(&user.id, id).await {
                    if x.role != GroupRole::Owner {
                        del!(user, self);
                    }
                } else {
                    del!(user, self);
                }
            } else {
                del!(user, self);
            }
        }

        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.printable()]
        );

        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 {
                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 question answer count
            if let Some(answering) = y.context.answering {
                let question = self.get_question_by_id(&answering).await?;

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

            // delete uploads
            for upload in y.uploads {
                if let Err(e) = self.1.delete_upload(upload).await {
                    return Err(Error::MiscError(e.to_string()));
                }
            }
        } 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 question answer count
            if let Some(answering) = y.context.answering {
                let question = self.get_question_by_id(&answering).await?;

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

            // unfortunately, uploads will not be restored
        }

        // return
        Ok(())
    }

    pub async fn update_post_context(&self, id: &Id, 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

        if user.id != y.owner {
            if !user.permissions.check(FinePermission::ManagePosts) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id.clone(),
                    format!("invoked `update_post_context` 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::ManagePosts) {
                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_nsfw {
            x.is_nsfw = 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.printable()]
        );

        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: &Id, 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::ManagePosts) {
                return Err(Error::NotAllowed);
            } else {
                self.create_audit_log_entry(AuditLogEntry::new(
                    user.id.clone(),
                    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.printable()]
        );

        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);

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