//! # Database
//! Database handler for all database types
use crate::utility;
use serde::{Deserialize, Serialize};

use dorsal::query as sqlquery;
use std::collections::HashMap;

#[derive(Clone)]
pub struct AppData {
    pub db: Database,
    pub http_client: awc::Client,
}

pub use dorsal::db::special::auth_db::{
    FullUser, RoleLevel, RoleLevelLog, UserMetadata, UserState,
};

pub use dorsal::db::special::log_db::{Log, LogIdentifier};
pub use dorsal::DefaultReturn;

#[derive(Debug, Serialize, Deserialize, Clone)]
pub struct DatabaseReturn {
    pub data: HashMap<String, String>,
}

#[derive(Default, PartialEq, Clone, Serialize, Deserialize)]
pub struct Board<M> {
    // selectors
    pub name: String,
    // dates
    pub timestamp: u128,
    // ...
    pub metadata: M,
}

#[derive(Default, Clone, Serialize, Deserialize, PartialEq)]
pub struct BoardMetadata {
    pub owner: String,                      // username of owner
    pub is_private: String, // if the homepage of the board is shown to other users (not owner)
    pub allow_anonymous: Option<String>, // if anonymous users can post
    pub allow_open_posting: Option<String>, // if all users can post on the board (not just owner)
    pub topic_required: Option<String>, // if posts are required to include a topic value
    pub about: Option<String>, // welcome message
    pub tags: Option<String>, // SPACE separated list of tags that identify the board for searches
                            // TODO: we could likely export a list of "valid" tags at some point in the future
}

#[derive(Default, Clone, Serialize, Deserialize, PartialEq)]
pub struct BoardPostLog {
    pub author: String, // username of owner
    pub content: String,
    pub content_html: String,
    pub topic: Option<String>, // post topic, content is hidden unless expanded if provided
    pub board: String,         // name of board the post is located in
    pub is_hidden: bool,       // if the post is hidden in the feed (does nothing right now)
    pub reply: Option<String>, // the ID of the post we're replying to
    pub pinned: Option<bool>,  // pin the post to the top of the board feed
    pub replies: Option<usize>, // not really managed in the log, just used to show the number of replies this post has
    pub tags: Option<String>,   // same as board tags, just for posts specifically
}

#[derive(Debug, Default, Clone, Serialize, Deserialize, PartialEq)]
pub struct BoardIdentifier {
    pub name: String,
    pub tags: String,
}

#[allow(dead_code)]
// front-end function
pub fn deserialize_post(input: Log) -> BoardPostLog {
    serde_json::from_str::<BoardPostLog>(&input.content).unwrap()
}

// ...
#[derive(Clone)]
pub struct Database {
    pub base: dorsal::StarterDatabase,
    pub auth: dorsal::AuthDatabase,
    pub logs: dorsal::LogDatabase,
}

impl Database {
    pub async fn new(opts: dorsal::DatabaseOpts) -> Database {
        let db = dorsal::StarterDatabase::new(opts).await;

        Database {
            base: db.clone(),
            auth: dorsal::AuthDatabase { base: db.clone() },
            logs: dorsal::LogDatabase { base: db },
        }
    }

    pub async fn init(&self) {
        // create tables
        let c = &self.base.db.client;

        let _ = sqlquery(
            "CREATE TABLE IF NOT EXISTS \"Boards\" (
                name VARCHAR(1000000),
                timestamp VARCHAR(1000000),
                metadata VARCHAR(1000000)
            )",
        )
        .execute(c)
        .await;

        // users and logs tables
        let _ = sqlquery(
            "CREATE TABLE IF NOT EXISTS \"Users\" (
                username VARCHAR(1000000),
                id_hashed VARCHAR(1000000),
                role VARCHAR(1000000),
                timestamp VARCHAR(1000000),
                metadata VARCHAR(1000000)
            )",
        )
        .execute(c)
        .await;

        let _ = sqlquery(
            "CREATE TABLE IF NOT EXISTS \"Logs\" (
                id VARCHAR(1000000),
                logtype VARCHAR(1000000),
                timestamp  VARCHAR(1000000),
                content VARCHAR(1000000)
            )",
        )
        .execute(c)
        .await;
    }

    // users

    // GET
    /// Get a user by their hashed ID
    ///
    /// # Arguments:
    /// * `hashed` - `String` of the user's hashed ID
    pub async fn get_user_by_hashed(
        &self,
        hashed: String,
    ) -> DefaultReturn<Option<FullUser<String>>> {
        self.auth.get_user_by_hashed(hashed).await
    }

    /// Get a user by their unhashed ID (hashes ID and then calls [`Database::get_user_by_hashed()`])
    ///
    /// Calls [`Database::get_user_by_unhashed_st()`] if user is invalid.
    ///
    /// # Arguments:
    /// * `unhashed` - `String` of the user's unhashed ID
    pub async fn get_user_by_unhashed(
        &self,
        unhashed: String,
    ) -> DefaultReturn<Option<FullUser<String>>> {
        self.auth.get_user_by_unhashed(unhashed).await
    }

    /// Get a user by their unhashed secondary token
    ///
    /// # Arguments:
    /// * `unhashed` - `String` of the user's unhashed secondary token
    pub async fn get_user_by_unhashed_st(
        &self,
        unhashed: String,
    ) -> DefaultReturn<Option<FullUser<String>>> {
        self.auth.get_user_by_unhashed_st(unhashed).await
    }

    /// Get a user by their username
    ///
    /// # Arguments:
    /// * `username` - `String` of the user's username
    pub async fn get_user_by_username(
        &self,
        username: String,
    ) -> DefaultReturn<Option<FullUser<String>>> {
        self.auth.get_user_by_username(username).await
    }

    /// Get a [`RoleLevel`] by its `name`
    ///
    /// # Arguments:
    /// * `name` - `String` of the level's role name
    pub async fn get_level_by_role(&self, name: String) -> DefaultReturn<RoleLevelLog> {
        self.auth.get_level_by_role(name).await
    }

    // boards

    // GET
    /// Get a [`Board`] by its name
    ///
    /// # Arguments:
    /// * `url` - board name
    pub async fn get_board_by_name(&self, url: String) -> DefaultReturn<Option<Board<String>>> {
        // check in cache
        let cached = self
            .base
            .cachedb
            .get(format!("board:{}", url.to_lowercase()))
            .await;

        if cached.is_some() {
            // ...
            let board = serde_json::from_str::<Board<String>>(cached.unwrap().as_str()).unwrap();

            // return
            return DefaultReturn {
                success: true,
                message: String::from("Board exists (cache)"),
                payload: Option::Some(board),
            };
        }

        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Boards\" WHERE \"name\" = ?"
        } else {
            "SELECT * FROM \"Boards\" WHERE \"name\" = $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&url.to_lowercase())
            .fetch_one(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist"),
                payload: Option::None,
            };
        }

        // ...
        let row = res.unwrap();
        let row = self.base.textify_row(row).data;

        // store in cache
        let board = Board {
            name: row.get("name").unwrap().to_string(),
            timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
            metadata: row.get("metadata").unwrap().to_string(),
        };

        self.base
            .cachedb
            .set(
                format!("board:{}", url.to_lowercase()),
                serde_json::to_string::<Board<String>>(&board).unwrap(),
            )
            .await;

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Board exists (new)"),
            payload: Option::Some(board),
        };
    }

    /// Get public [`Board`]s
    ///
    /// # Arguments:
    /// * `offset` - optional value representing the SQL fetch offset
    pub async fn get_boards(
        &self,
        offset: Option<i32>,
    ) -> DefaultReturn<Option<Vec<BoardIdentifier>>> {
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Boards\" WHERE \"metadata\" NOT LIKE '%\"is_private\":\"yes\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET ?"
        } else {
            "SELECT * FROM \"Boards\" WHERE \"metadata\" NOT LIKE '%\"is_private\":\"yes\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind(if offset.is_some() { offset.unwrap() } else { 0 })
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch boards"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<BoardIdentifier> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(BoardIdentifier {
                name: row.get("name").unwrap().to_string(),
                tags: String::new(),
            });
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Board exists"),
            payload: Option::Some(output),
        };
    }

    /// Get public [`Board`]s by their tags
    ///
    /// # Arguments:
    /// * `tags` - space separated list of tags, tags should use "_" instead of a space in their names, all tags should start with "+"
    /// * `offset` - optional value representing the SQL fetch offset
    pub async fn get_boards_by_tags(
        &self,
        mut tags: String,
        offset: Option<i32>,
    ) -> DefaultReturn<Option<Vec<BoardIdentifier>>> {
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Boards\" WHERE \"metadata\" LIKE ? AND \"metadata\" NOT LIKE '%\"is_private\":\"yes\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET ?"
        } else {
            "SELECT * FROM \"Boards\" WHERE \"metadata\" LIKE $1 AND \"metadata\" NOT LIKE '%\"is_private\":\"yes\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET $2"
        };

        // check tags (reason below)
        for tag in tags.clone().split(" ") {
            if tag.starts_with("+") | (tag.len() == 0) {
                continue;
            }

            tags = tags.replacen(tag, &format!("+{}", tag), 1);
        }

        // ...
        let c = &self.base.db.client;
        let res = sqlquery(query)
            // this allows us to search within ANY field of the metadata ...
            // ideally we would use MySQL JSON_VALUE here, but SQLite and PostgreSQL don't support this
            // instead we'll just require that tags start with "+"
            .bind::<&String>(&format!("%{tags}%"))
            .bind(if offset.is_some() { offset.unwrap() } else { 0 })
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch boards"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<BoardIdentifier> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            let metadata =
                serde_json::from_str::<BoardMetadata>(&row.get("metadata").unwrap().to_string())
                    .unwrap();

            output.push(BoardIdentifier {
                name: row.get("name").unwrap().to_string(),
                tags: if metadata.tags.is_some() {
                    metadata.tags.unwrap()
                } else {
                    String::new()
                },
            });
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Board exists"),
            payload: Option::Some(output),
        };
    }

    /// Get all posts in a [`Board`] by its name
    ///
    /// # Arguments:
    /// * `url` - board name
    /// * `offset` - optional value representing the SQL fetch offset
    pub async fn get_board_posts(
        &self,
        url: String,
        offset: Option<i32>,
    ) -> DefaultReturn<Option<Vec<Log>>> {
        let offset = if offset.is_some() { offset.unwrap() } else { 0 };

        // make sure board exists
        let existing: DefaultReturn<Option<Board<String>>> =
            self.get_board_by_name(url.to_owned().to_lowercase()).await;

        if existing.success == false {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist"),
                payload: Option::None,
            };
        }

        // check in cache
        let cached = self
            .base
            .cachedb
            .get(format!(
                "board-posts:{}:offset{}",
                url.to_lowercase(),
                offset
            ))
            .await;

        if cached.is_some() {
            // ...
            let posts = serde_json::from_str::<Vec<Log>>(cached.unwrap().as_str()).unwrap();

            // ...
            return DefaultReturn {
                success: true,
                message: String::from("Successfully fetched posts"),
                payload: Option::Some(posts),
            };
        }

        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE ? AND \"content\" NOT LIKE '%\"reply\":\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET ?"
        } else {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE $1 AND \"content\" NOT LIKE '%\"reply\":\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET $2"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"board\":\"{}\"%", url))
            .bind(offset)
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch posts"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<Log> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(Log {
                id: row.get("id").unwrap().to_string(),
                logtype: row.get("logtype").unwrap().to_string(),
                timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
                content: row.get("content").unwrap().to_string(),
            });
        }

        let mut true_output: Vec<Log> = Vec::new();
        for mut post in output {
            let mut parsed = serde_json::from_str::<BoardPostLog>(&post.content).unwrap();

            // get replies
            let replies = &self.get_post_replies(post.clone().id, false).await;

            if replies.payload.is_some() {
                parsed.replies = Option::Some(replies.payload.as_ref().unwrap().len());

                // update
                post.content = serde_json::to_string::<BoardPostLog>(&parsed).unwrap();
                true_output.push(post);

                continue;
            }

            continue;
        }

        // store in cache
        self.base
            .cachedb
            .set(
                format!("board-posts:{}:offset{}", url.to_lowercase(), offset),
                serde_json::to_string::<Vec<Log>>(&true_output).unwrap(),
            )
            .await;

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Successfully fetched posts"),
            payload: Option::Some(true_output),
        };
    }

    /// Get all posts in a [`Board`] by its name and their tags
    ///
    /// # Arguments:
    /// * `url` - board name
    /// * `tags` - space separated list of tags, tags should use "_" instead of a space in their names, all tags should start with "+"
    /// * `offset` - optional value representing the SQL fetch offset
    pub async fn get_board_posts_by_tag(
        &self,
        url: String,
        mut tags: String,
        offset: Option<i32>,
    ) -> DefaultReturn<Option<Vec<Log>>> {
        // make sure board exists
        let existing: DefaultReturn<Option<Board<String>>> =
            self.get_board_by_name(url.to_owned().to_lowercase()).await;

        if existing.success == false {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist"),
                payload: Option::None,
            };
        }

        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE ? AND \"content\" LIKE ? AND \"content\" NOT LIKE '%\"reply\":\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET ?"
        } else {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE $1 AND \"content\" LIKE $2 AND \"content\" NOT LIKE '%\"reply\":\"%' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET $3"
        };

        // check tags
        for tag in tags.clone().split(" ") {
            if tag.starts_with("+") | (tag.len() == 0) {
                continue;
            }

            tags = tags.replacen(tag, &format!("+{}", tag), 1);
        }

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"board\":\"{}\"%", url))
            .bind::<&String>(&format!("%{tags}%"))
            .bind(if offset.is_some() { offset.unwrap() } else { 0 })
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch posts"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<Log> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(Log {
                id: row.get("id").unwrap().to_string(),
                logtype: row.get("logtype").unwrap().to_string(),
                timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
                content: row.get("content").unwrap().to_string(),
            });
        }

        let mut true_output: Vec<Log> = Vec::new();
        for mut post in output {
            let mut parsed = serde_json::from_str::<BoardPostLog>(&post.content).unwrap();

            // get replies
            let replies = &self.get_post_replies(post.clone().id, false).await;

            if replies.payload.is_some() {
                parsed.replies = Option::Some(replies.payload.as_ref().unwrap().len());

                // update
                post.content = serde_json::to_string::<BoardPostLog>(&parsed).unwrap();
                true_output.push(post);

                continue;
            }

            continue;
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Successfully fetched posts"),
            payload: Option::Some(true_output),
        };
    }

    /// Get all pinned posts in a [`Board`] by its name
    ///
    /// # Arguments:
    /// * `url` - board name
    pub async fn get_pinned_board_posts(&self, url: String) -> DefaultReturn<Option<Vec<Log>>> {
        // make sure board exists
        let existing: DefaultReturn<Option<Board<String>>> =
            self.get_board_by_name(url.to_owned().to_lowercase()).await;

        if existing.success == false {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist"),
                payload: Option::None,
            };
        }

        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE ? AND \"content\" NOT LIKE '%\"reply\":\"%' AND \"content\" LIKE '%\"pinned\":true%' ORDER BY \"timestamp\" DESC LIMIT 50"
        } else {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE $1 AND \"content\" NOT LIKE '%\"reply\":\"%' AND \"content\" LIKE '%\"pinned\":true%' ORDER BY \"timestamp\" DESC LIMIT 50"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"board\":\"{}\"%", url))
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch posts"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<Log> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(Log {
                id: row.get("id").unwrap().to_string(),
                logtype: row.get("logtype").unwrap().to_string(),
                timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
                content: row.get("content").unwrap().to_string(),
            });
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Successfully fetched posts (pinned)"),
            payload: Option::Some(output),
        };
    }

    /// Get all posts in a [`Board`] by its name that are replying to another [`BoardPostLog`] (limited form)
    ///
    /// # Arguments:
    /// * `id` - post id
    /// * `run_existing_check` - if we should check that the log exists first
    pub async fn get_post_replies_limited(
        &self,
        id: String,
        run_existing_check: bool,
        offset: Option<i32>,
    ) -> DefaultReturn<Option<Vec<Log>>> {
        let offset = if offset.is_some() { offset.unwrap() } else { 0 };

        // make sure message exists
        if run_existing_check != false {
            let existing: DefaultReturn<Option<Log>> = self.logs.get_log_by_id(id.to_owned()).await;

            if existing.success == false {
                return DefaultReturn {
                    success: false,
                    message: String::from("Post does not exist"),
                    payload: Option::None,
                };
            }
        }

        // check in cache
        let cached = self
            .base
            .cachedb
            .get(format!("post-replies:{}:offset{}", id, offset))
            .await;

        if cached.is_some() {
            // ...
            let posts = serde_json::from_str::<Vec<Log>>(cached.unwrap().as_str()).unwrap();

            // ...
            return DefaultReturn {
                success: true,
                message: String::from("Successfully fetched replies (limited)"),
                payload: Option::Some(posts),
            };
        }

        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE ? ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET ?"
        } else {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE $1 ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET $2"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"reply\":\"{}\"%", id))
            .bind(offset)
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch posts"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<Log> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(Log {
                id: row.get("id").unwrap().to_string(),
                logtype: row.get("logtype").unwrap().to_string(),
                timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
                content: row.get("content").unwrap().to_string(),
            });
        }

        let mut true_output: Vec<Log> = Vec::new();
        for mut post in output {
            let mut parsed = serde_json::from_str::<BoardPostLog>(&post.content).unwrap();

            // get replies
            let replies = &self.get_post_replies(post.clone().id, false).await;

            if replies.payload.is_some() {
                parsed.replies = Option::Some(replies.payload.as_ref().unwrap().len());

                // update
                post.content = serde_json::to_string::<BoardPostLog>(&parsed).unwrap();
                true_output.push(post);

                continue;
            }

            continue;
        }

        // store in cache
        self.base
            .cachedb
            .set(
                format!("post-replies:{}:offset{}", id, offset),
                serde_json::to_string::<Vec<Log>>(&true_output).unwrap(),
            )
            .await;

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Successfully fetched replies (limited)"),
            payload: Option::Some(true_output),
        };
    }

    /// Get all posts in a [`Board`] by its name that are replying to another [`BoardPostLog`]
    ///
    /// - only includes post id
    ///
    /// # Arguments:
    /// * `id` - post id
    /// * `run_existing_check` - if we should check that the log exists first
    pub async fn get_post_replies(
        &self,
        id: String,
        run_existing_check: bool,
    ) -> DefaultReturn<Option<Vec<LogIdentifier>>> {
        // make sure message exists
        if run_existing_check != false {
            let existing: DefaultReturn<Option<Log>> = self.logs.get_log_by_id(id.to_owned()).await;

            if existing.success == false {
                return DefaultReturn {
                    success: false,
                    message: String::from("Post does not exist"),
                    payload: Option::None,
                };
            }
        }

        // check in cache
        let cached = self.base.cachedb.get(format!("post-replies:{}", id)).await;

        if cached.is_some() {
            // ...
            let posts =
                serde_json::from_str::<Vec<LogIdentifier>>(cached.unwrap().as_str()).unwrap();

            // ...
            return DefaultReturn {
                success: true,
                message: String::from("Successfully fetched posts (limited)"),
                payload: Option::Some(posts),
            };
        }

        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT \"ID\" FROM \"Logs\" WHERE \"content\" LIKE ?"
        } else {
            "SELECT \"ID\" FROM \"Logs\" WHERE \"content\" LIKE $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"reply\":\"{}\"%", id))
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch replies"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<LogIdentifier> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(LogIdentifier {
                id: row.get("id").unwrap_or(&String::new()).to_string(),
            });
        }

        // store in cache
        self.base
            .cachedb
            .set(
                format!("post-replies:{}", id),
                serde_json::to_string::<Vec<LogIdentifier>>(&output).unwrap(),
            )
            .await;

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Successfully fetched replies"),
            payload: Option::Some(output),
        };
    }

    /// Get all [boards](Board) owned by a specific user
    ///
    /// # Arguments:
    /// * `owner` - `String` of the owner's `username`
    pub async fn get_boards_by_owner(
        &self,
        owner: String,
    ) -> DefaultReturn<Option<Vec<BoardIdentifier>>> {
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Boards\" WHERE \"metadata\" LIKE ?"
        } else {
            "SELECT * FROM \"Boards\" WHERE \"metadata\" LIKE $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"owner\":\"{}\"%", &owner))
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from(res.err().unwrap().to_string()),
                payload: Option::None,
            };
        }

        // build res
        let mut full_res: Vec<BoardIdentifier> = Vec::new();

        for row in res.unwrap() {
            let row = self.base.textify_row(row).data;
            full_res.push(BoardIdentifier {
                name: row.get("name").unwrap().to_string(),
                tags: String::new(),
            });
        }

        // return
        return DefaultReturn {
            success: true,
            message: owner,
            payload: Option::Some(full_res),
        };
    }

    /// Get most recent posts from all [`Boards`](Board)
    ///
    /// # Arguments:
    /// * `offset` - optional value representing the SQL fetch offset
    pub async fn fetch_most_recent_posts(
        &self,
        offset: Option<i32>,
    ) -> DefaultReturn<Option<Vec<Log>>> {
        // ...
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Logs\" WHERE \"logtype\" = 'board_post' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET ?"
        } else {
            "SELECT * FROM \"Logs\" WHERE \"logtype\" = 'board_post' ORDER BY \"timestamp\" DESC LIMIT 50 OFFSET $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind(if offset.is_some() { offset.unwrap() } else { 0 })
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch posts"),
                payload: Option::None,
            };
        }

        // ...
        let rows = res.unwrap();
        let mut output: Vec<Log> = Vec::new();

        for row in rows {
            let row = self.base.textify_row(row).data;
            output.push(Log {
                id: row.get("id").unwrap().to_string(),
                logtype: row.get("logtype").unwrap().to_string(),
                timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
                content: row.get("content").unwrap().to_string(),
            });
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Successfully fetched posts"),
            payload: Option::Some(output),
        };
    }

    /// Get the amount of posts a user has created in all boards
    ///
    /// # Arguments:
    /// * `user` - username of user to check
    pub async fn get_user_posts_count(&self, user: String) -> DefaultReturn<usize> {
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE ? AND \"logtype\" = 'board_post'"
        } else {
            "SELECT * FROM \"Logs\" WHERE \"content\" LIKE $1 AND \"logtype\" = 'board_post'"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"author\":\"{user}\"%"))
            .fetch_all(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from("Failed to fetch posts"),
                payload: 0,
            };
        }

        // ...
        let rows = res.unwrap();

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Posts exist"),
            payload: rows.len(),
        };
    }

    // SET
    /// Create a new [`Board`] given various properties
    ///
    /// # Arguments:
    /// * `props` - [`Board<String>`](Board)
    /// * `as_user` - The ID of the user creating the board
    pub async fn create_board(
        &self,
        props: &mut Board<String>,
        as_user: Option<String>, // id of board owner
    ) -> DefaultReturn<Option<Board<String>>> {
        let p: &mut Board<String> = props; // borrowed props

        // create default metadata
        let metadata: BoardMetadata = BoardMetadata {
            owner: as_user.clone().unwrap(),
            is_private: String::from("no"), // values other than no/yes will show the view password prompt
            allow_anonymous: Option::Some(String::from("yes")),
            allow_open_posting: Option::Some(String::from("yes")),
            topic_required: Option::Some(String::from("no")),
            about: Option::None,
            tags: Option::None,
        };

        // check values

        // (check length)
        if (p.name.len() < 2) | (p.name.len() > 250) {
            return DefaultReturn {
                success: false,
                message: String::from("Name is invalid"),
                payload: Option::None,
            };
        }

        // (characters used)
        let regex = regex::RegexBuilder::new("^[\\w\\_\\-\\.\\!]+$")
            .multi_line(true)
            .build()
            .unwrap();

        if regex.captures(&p.name).iter().len() < 1 {
            return DefaultReturn {
                success: false,
                message: String::from("Name is invalid"),
                payload: Option::None,
            };
        }

        // make sure board does not exist
        let existing: DefaultReturn<Option<Board<String>>> = self
            .get_board_by_name(p.name.to_owned().to_lowercase())
            .await;

        if existing.success {
            return DefaultReturn {
                success: false,
                message: String::from("Board already exists!"),
                payload: Option::None,
            };
        }

        // create board
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "INSERT INTO \"Boards\" VALUES (?, ?, ?)"
        } else {
            "INSERT INTO \"Boards\" VALUES ($1, $2, $3)"
        };

        let c = &self.base.db.client;
        let p: &mut Board<String> = &mut props.clone();
        p.timestamp = utility::unix_epoch_timestamp();

        let res = sqlquery(query)
            .bind::<&String>(&p.name)
            .bind::<&String>(&p.timestamp.to_string())
            .bind::<&String>(&serde_json::to_string(&metadata).unwrap())
            .execute(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: res.err().unwrap().to_string(),
                payload: Option::None,
            };
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Created board"),
            payload: Option::Some(p.to_owned()),
        };
    }

    /// Create a new post in a given [`Board`]
    ///
    /// # Arguments:
    /// * `props` - [`BoardPostLog`]
    /// * `as_user` - The ID of the user creating the post
    pub async fn create_board_post(
        &self,
        props: &mut BoardPostLog,
        as_user: Option<String>,    // username of user posting
        as_role: Option<RoleLevel>, // role of user posting
    ) -> DefaultReturn<Option<String>> {
        let p: &mut BoardPostLog = props; // borrowed props

        // check values

        // (check length)
        if (p.content.len() < 2) | (p.content.len() > 1_000) {
            return DefaultReturn {
                success: false,
                message: String::from("Content is invalid"),
                payload: Option::None,
            };
        }

        // make sure this board exists
        let existing: DefaultReturn<Option<Board<String>>> = self
            .get_board_by_name(p.board.to_owned().to_lowercase())
            .await;

        if !existing.success {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist!"),
                payload: Option::None,
            };
        }

        let board =
            serde_json::from_str::<BoardMetadata>(&existing.payload.unwrap().metadata).unwrap();

        // check board "topic_required" setting
        // if it is set to "yes", make sure we provided a topic AND this is not a reply (replies to not count)
        if board.topic_required.is_some()
            && board.topic_required.unwrap() == "yes"
            && p.reply.is_none()
            && p.topic.is_none()
        {
            return DefaultReturn {
                success: false,
                message: String::from("This board requires a topic to be set before posting"),
                payload: Option::None,
            };
        }

        // check board "allow_anonymous" setting
        if board.allow_anonymous.is_some()
            && board.allow_anonymous.unwrap() == "no"
            && as_user.is_none()
        {
            return DefaultReturn {
                success: false,
                message: String::from("An account is required to do this"),
                payload: Option::None,
            };
        }

        // check board "allow_open_posting" setting
        if board.allow_open_posting.is_some()
            && board.allow_open_posting.unwrap() == String::from("no")
        {
            let can_post = as_user.is_some()
                && ((as_user.as_ref().unwrap() == &board.owner)
                    | (as_role
                        .unwrap()
                        .permissions
                        .contains(&String::from("ManageBoards"))));

            if can_post == false {
                return DefaultReturn {
                    success: false,
                    message: String::from("You do not have permission to do this"),
                    payload: Option::None,
                };
            }
        }

        // create post
        let post = BoardPostLog {
            author: if as_user.is_some() {
                as_user.unwrap()
            } else {
                String::from("Anonymous")
            },
            content: p.content.clone(),
            content_html: crate::markup::render(&p.content),
            topic: p.topic.clone(),
            board: p.board.clone().to_lowercase(),
            is_hidden: false,
            reply: p.reply.clone(),
            pinned: Option::Some(false),
            replies: Option::None,
            tags: Option::None,
        };

        // update cache
        if p.reply.is_none() {
            // a new post would always be on the first page, so only clear that one
            self.base
                .cachedb
                .remove(format!("board-posts:{}:offset0", p.board.to_lowercase()))
                .await;

            // ...but also clear the board (TODO: remove this step)
            self.base
                .cachedb
                .remove(format!("board-posts:{}", p.board.to_lowercase()))
                .await;
        } else {
            // a new post would always be on the first page, so only clear that one
            self.base
                .cachedb
                .remove(format!("post-replies:{}", p.reply.as_ref().unwrap()))
                .await;

            self.base
                .cachedb
                .remove(format!(
                    "post-replies:{}:offset0",
                    p.reply.as_ref().unwrap()
                ))
                .await;

            // get replying_to
            let replying_to = self
                .logs
                .get_log_by_id(p.reply.as_ref().unwrap().to_string())
                .await;

            let replying_to =
                serde_json::from_str::<BoardPostLog>(&replying_to.payload.unwrap().content)
                    .unwrap();

            // delete replying_to's reply list at offset 0
            if replying_to.reply.is_some() {
                self.base
                    .cachedb
                    .remove(format!(
                        "post-replies:{}:offset0",
                        replying_to.reply.unwrap()
                    ))
                    .await;
            } else {
                self.base
                    .cachedb
                    .remove(format!("board-posts:{}:offset0", p.board.to_lowercase()))
                    .await;
            }
        }

        // return
        self.logs
            .create_log(
                String::from("board_post"),
                serde_json::to_string::<BoardPostLog>(&post).unwrap(),
            )
            .await
    }

    /// Update a [`Board`]'s metadata by its `custom_url`
    pub async fn edit_board_metadata_by_name(
        &self,
        name: String,
        metadata: BoardMetadata,
        edit_as: Option<String>, // username of account that is editing this board
    ) -> DefaultReturn<Option<String>> {
        // make sure board exists
        let existing = &self.get_board_by_name(name.clone()).await;
        if !existing.success {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist!"),
                payload: Option::None,
            };
        }

        // get edit_as user account
        let ua = if edit_as.is_some() {
            Option::Some(
                self.get_user_by_username(edit_as.clone().unwrap())
                    .await
                    .payload,
            )
        } else {
            Option::None
        };

        if ua.is_none() {
            return DefaultReturn {
                success: false,
                message: String::from("An account is required to do this"),
                payload: Option::None,
            };
        }

        // make sure we can do this
        let user = ua.unwrap().unwrap();
        let can_edit: bool =
            // using "metadata" here likely prohibits us from changing board owner
            (user.user.username == metadata.owner) | (user.level.permissions.contains(&String::from("ManageBoards")));

        if can_edit == false {
            return DefaultReturn {
                success: false,
                message: String::from(
                    "You do not have permission to manage this board's contents.",
                ),
                payload: Option::None,
            };
        }

        // make sure we're not trying to convert this to a mail stream
        if metadata.about.is_some()
            && metadata
                .about
                .as_ref()
                .unwrap()
                .contains("\"_is_user_mail_stream\":true")
        {
            return DefaultReturn {
                success: false,
                message: String::from("Cannot convert this board into a user mail stream."),
                payload: Option::None,
            };
        }

        // update board
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "UPDATE \"Boards\" SET \"metadata\" = ? WHERE \"name\" = ?"
        } else {
            "UPDATE \"Boards\" SET (\"metadata\") = ($1) WHERE \"name\" = $2"
        };

        let c = &self.base.db.client;
        let metadata = serde_json::to_string(&metadata).unwrap();
        let res = sqlquery(query)
            .bind::<&String>(&metadata)
            .bind::<&String>(&name)
            .execute(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from(res.err().unwrap().to_string()),
                payload: Option::None,
            };
        }

        // update cache
        let existing_in_cache = self.base.cachedb.get(format!("board:{}", name)).await;

        if existing_in_cache.is_some() {
            let mut board =
                serde_json::from_str::<Board<String>>(&existing_in_cache.unwrap()).unwrap();
            board.metadata = metadata; // update metadata

            // update cache
            self.base
                .cachedb
                .update(
                    format!("board:{}", name),
                    serde_json::to_string::<Board<String>>(&board).unwrap(),
                )
                .await;
        }

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Board updated!"),
            payload: Option::Some(name),
        };
    }

    /// Delete a board given its name
    ///
    /// # Arguments:
    /// * `name` - `String` of the board's `name`
    pub async fn delete_board(&self, name: String) -> DefaultReturn<Option<String>> {
        // make sure log exists
        let existing = &self.get_board_by_name(name.clone()).await;
        if !existing.success {
            return DefaultReturn {
                success: false,
                message: String::from("Board does not exist!"),
                payload: Option::None,
            };
        }

        // delete board
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "DELETE FROM \"Boards\" WHERE \"name\" = ?"
        } else {
            "DELETE FROM \"Boards\" WHERE \"name\" = $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query).bind::<&String>(&name).execute(c).await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from(res.err().unwrap().to_string()),
                payload: Option::None,
            };
        }

        // delete board messages
        let query: &str = if (self.base.db._type == "sqlite") | (self.base.db._type == "mysql") {
            "DELETE FROM \"Logs\" WHERE \"content\" LIKE ?"
        } else {
            "DELETE FROM \"Logs\" WHERE \"content\" LIKE $1"
        };

        let c = &self.base.db.client;
        let res = sqlquery(query)
            .bind::<&String>(&format!("%\"board\":\"{}\"%", name))
            .execute(c)
            .await;

        if res.is_err() {
            return DefaultReturn {
                success: false,
                message: String::from(res.err().unwrap().to_string()),
                payload: Option::None,
            };
        }

        // update cache
        self.base.cachedb.remove(format!("board:{}", name)).await;

        // return
        return DefaultReturn {
            success: true,
            message: String::from("Board deleted!"),
            payload: Option::Some(name),
        };
    }
}