authbeam/
database.rs

1use std::collections::BTreeMap;
2
3use crate::layout::LayoutComponent;
4use crate::model::{
5    DatabaseError, FinePermission, IpBan, IpBanCreate, IpBlock, IpBlockCreate, Item, ItemCreate,
6    ItemEdit, ItemEditContent, ItemStatus, ItemType, Profile, ProfileCreate, ProfileMetadata,
7    RelationshipStatus, TokenContext, Transaction, TransactionCreate, UserLabel, Warning,
8    WarningCreate,
9};
10use crate::model::{Group, Notification, NotificationCreate, UserFollow};
11use hcaptcha_no_wasm::Hcaptcha;
12use rainbeam_shared::snow::AlmostSnowflake;
13use reqwest::Client as HttpClient;
14use serde::{Deserialize, Serialize};
15
16use databeam::{query as sqlquery, utility, prelude::*};
17use pathbufd::{PathBufD, pathd};
18
19pub use rainbeam_shared::config::HCaptchaConfig;
20
21pub type Result<T> = std::result::Result<T, DatabaseError>;
22use std::sync::LazyLock;
23
24use crate::{cache_sync, from_row, update_profile_count, simplify};
25
26/// Custom keys allowed to be used as metadata options.
27pub static ALLOWED_CUSTOM_KEYS: LazyLock<Vec<&'static str>> = LazyLock::new(|| {
28    vec![
29        "sparkler:display_name",
30        "sparkler:status_note",
31        "sparkler:status_emoji",
32        "sparkler:limited_friend_requests",
33        "sparkler:private_profile",
34        "sparkler:allow_drawings",
35        "sparkler:biography",
36        "sparkler:sidebar",
37        "sparkler:avatar_url",
38        "sparkler:banner_url",
39        "sparkler:banner_fit",
40        "sparkler:website_theme",
41        "sparkler:allow_profile_themes",
42        "sparkler:motivational_header",
43        "sparkler:warning",
44        "sparkler:anonymous_username",
45        "sparkler:anonymous_avatar",
46        "sparkler:pinned",
47        "sparkler:profile_theme",
48        "sparkler:desktop_tl_logo",
49        "sparkler:custom_css",
50        "sparkler:color_surface",
51        "sparkler:color_lowered",
52        "sparkler:color_super_lowered",
53        "sparkler:color_raised",
54        "sparkler:color_super_raised",
55        "sparkler:color_text",
56        "sparkler:color_text_raised",
57        "sparkler:color_text_lowered",
58        "sparkler:color_link",
59        "sparkler:color_primary",
60        "sparkler:color_primary_lowered",
61        "sparkler:color_primary_raised",
62        "sparkler:color_text_primary",
63        "sparkler:color_shadow",
64        "sparkler:lock_profile",
65        "sparkler:disallow_anonymous",
66        "sparkler:disallow_anonymous_comments",
67        "sparkler:require_account",
68        "sparkler:private_social",
69        "sparkler:filter",
70        "rainbeam:verify_url",
71        "rainbeam:verify_code",
72        "rainbeam:market_theme_template",
73        "rainbeam:nsfw_profile",
74        "rainbeam:share_hashtag",
75        "rainbeam:authenticated_only",
76        "rainbeam:force_default_layout",
77        "rainbeam:disallow_response_comments",
78        "rainbeam:view_password",
79        "rainbeam:do_not_send_global_questions_to_inbox",
80        "rainbeam:do_not_clear_inbox_count_on_view",
81        "rainbeam:do_not_send_global_questions_to_friends",
82    ]
83});
84
85#[derive(Serialize, Deserialize, Clone, Debug)]
86pub struct ServerOptions {
87    /// If new registrations are enabled
88    #[serde(default)]
89    pub registration_enabled: bool,
90    /// HCaptcha configuration
91    #[serde(default)]
92    pub captcha: HCaptchaConfig,
93    /// The header to read user IP from
94    #[serde(default)]
95    pub real_ip_header: Option<String>,
96    /// The directory to serve static assets from
97    #[serde(default)]
98    pub static_dir: PathBufD,
99    /// The location of media uploads on the file system
100    #[serde(default)]
101    pub media_dir: PathBufD,
102    /// The origin of the public server (ex: "https://rainbeam.net")
103    ///
104    /// Used in embeds and links.
105    #[serde(default)]
106    pub host: String,
107    /// The server ID for ID generation
108    pub snowflake_server_id: usize,
109    /// A list of image hosts that are blocked
110    #[serde(default)]
111    pub blocked_hosts: Vec<String>,
112}
113
114impl Default for ServerOptions {
115    fn default() -> Self {
116        Self {
117            registration_enabled: true,
118            captcha: HCaptchaConfig::default(),
119            real_ip_header: Option::None,
120            static_dir: PathBufD::default(),
121            media_dir: PathBufD::default(),
122            host: String::new(),
123            snowflake_server_id: 1234567890,
124            blocked_hosts: Vec::new(),
125        }
126    }
127}
128
129/// Database connector
130#[derive(Clone)]
131pub struct Database {
132    pub base: StarterDatabase,
133    pub config: ServerOptions,
134    pub http: HttpClient,
135}
136
137impl Database {
138    /// Create a new [`Database`]
139    pub async fn new(
140        database_options: databeam::DatabaseOpts,
141        server_options: ServerOptions,
142    ) -> Self {
143        let base = StarterDatabase::new(database_options).await;
144
145        Self {
146            base: base.clone(),
147            http: HttpClient::new(),
148            config: server_options,
149        }
150    }
151
152    /// Pull [`databeam::DatabaseOpts`] from env
153    pub fn env_options() -> databeam::DatabaseOpts {
154        use std::env::var;
155        databeam::DatabaseOpts {
156            r#type: match var("DB_TYPE") {
157                Ok(v) => Option::Some(v),
158                Err(_) => Option::None,
159            },
160            host: match var("DB_HOST") {
161                Ok(v) => Option::Some(v),
162                Err(_) => Option::None,
163            },
164            user: var("DB_USER").unwrap_or(String::new()),
165            pass: var("DB_PASS").unwrap_or(String::new()),
166            name: var("DB_NAME").unwrap_or(String::new()),
167        }
168    }
169
170    /// Init database
171    pub async fn init(&self) {
172        // create tables
173        let c = &self.base.db.client;
174
175        let _ = sqlquery(
176            "CREATE TABLE IF NOT EXISTS \"xprofiles\" (
177                id                 TEXT,
178                username           TEXT,
179                password           TEXT,
180                tokens             TEXT,
181                metadata           TEXT,
182                joined             TEXT,
183                gid                TEXT,
184                salt               TEXT,
185                ips                TEXT,
186                badges             TEXT,
187                tier               TEXT,
188                token_context      TEXT,
189                coins              TEXT DEFAULT '0',
190                links              TEXT DEFAULT '{}',
191                layout             TEXT DEFAULT '{\"json\":\"default.json\"}',
192                question_count     TEXT DEFAULT '0',
193                response_count     TEXT DEFAULT '0',
194                totp               TEXT DEFAULT '',
195                recovery_codes     TEXT DEFAULT '[]'
196                notification_count TEXT DEFAULT '0',
197                inbox_count        TEXT DEFAULT '0'
198                labels             TEXT DEFAULT '[]',
199            )",
200        )
201        .execute(c)
202        .await;
203
204        let _ = sqlquery(
205            "CREATE TABLE IF NOT EXISTS \"xgroups\" (
206                name        TEXT,
207                id          TEXT,
208                permissions INTEGER
209            )",
210        )
211        .execute(c)
212        .await;
213
214        let _ = sqlquery(
215            "CREATE TABLE IF NOT EXISTS \"xfollows\" (
216                user      TEXT,
217                following TEXT
218            )",
219        )
220        .execute(c)
221        .await;
222
223        let _ = sqlquery(
224            "CREATE TABLE IF NOT EXISTS \"xnotifications\" (
225                title     TEXT,
226                content   TEXT,
227                address   TEXT,
228                timestamp TEXT,
229                id        TEXT,
230                recipient TEXT
231            )",
232        )
233        .execute(c)
234        .await;
235
236        let _ = sqlquery(
237            "CREATE TABLE IF NOT EXISTS \"xwarnings\" (
238                id        TEXT,
239                content   TEXT,
240                timestamp TEXT,
241                recipient TEXT,
242                moderator TEXT
243            )",
244        )
245        .execute(c)
246        .await;
247
248        let _ = sqlquery(
249            "CREATE TABLE IF NOT EXISTS \"xbans\" (
250                id        TEXT,
251                ip        TEXT,
252                reason    TEXT,
253                moderator TEXT,
254                timestamp TEXT
255            )",
256        )
257        .execute(c)
258        .await;
259
260        let _ = sqlquery(
261            "CREATE TABLE IF NOT EXISTS \"xrelationships\" (
262                one       TEXT,
263                two       TEXT,
264                status    TEXT,
265                timestamp TEXT
266            )",
267        )
268        .execute(c)
269        .await;
270
271        let _ = sqlquery(
272            "CREATE TABLE IF NOT EXISTS \"xipblocks\" (
273                id        TEXT,
274                ip        TEXT,
275                user      TEXT,
276                context   TEXT,
277                timestamp TEXT
278            )",
279        )
280        .execute(c)
281        .await;
282
283        let _ = sqlquery(
284            "CREATE TABLE IF NOT EXISTS \"xlabels\" (
285                id        TEXT,
286                name      TEXT,
287                timestamp TEXT,
288                creator   TEXT
289            )",
290        )
291        .execute(c)
292        .await;
293
294        let _ = sqlquery(
295            // "xugc_transactions" to not interfere with real money transactions
296            "CREATE TABLE IF NOT EXISTS \"xugc_transactions\" (
297                id        TEXT,
298                amount    TEXT,
299                item      TEXT,
300                timestamp TEXT,
301                customer  TEXT,
302                merchant  TEXT
303            )",
304        )
305        .execute(c)
306        .await;
307
308        let _ = sqlquery(
309            "CREATE TABLE IF NOT EXISTS \"xugc_items\" (
310                id          TEXT,
311                name        TEXT,
312                description TEXT,
313                cost        TEXT,
314                content     TEXT,
315                type        TEXT,
316                status      TEXT,
317                timestamp   TEXT,
318                creator     TEXT
319            )",
320        )
321        .execute(c)
322        .await;
323    }
324
325    // util
326
327    /// Create a moderator audit log entry.
328    pub async fn audit(&self, actor_id: &str, content: &str) -> Result<()> {
329        match self
330            .create_notification(
331                NotificationCreate {
332                    title: format!("[{actor_id}](/+u/{actor_id})"),
333                    content: content.to_string(),
334                    address: format!("/+u/{actor_id}"),
335                    recipient: "*(audit)".to_string(), // all staff, audit registry
336                },
337                None,
338            )
339            .await
340        {
341            Ok(_) => Ok(()),
342            Err(_) => Err(DatabaseError::Other),
343        }
344    }
345
346    // profiles
347
348    /// Get profile given the `row` data.
349    pub async fn gimme_profile(&self, row: BTreeMap<String, String>) -> Result<Box<Profile>> {
350        let id = from_row!(row->id());
351
352        let metadata: ProfileMetadata = from_row!(row->metadata(json); DatabaseError::ValueError);
353        let do_not_clear_inbox_count_on_view =
354            metadata.is_true("rainbeam:do_not_clear_inbox_count_on_view");
355
356        Ok(Box::new(Profile {
357            id: id.clone(),
358            username: from_row!(row->username()),
359            password: from_row!(row->password()),
360            salt: from_row!(row->salt(); &String::new()),
361            tokens: from_row!(row->tokens(json); DatabaseError::ValueError),
362            ips: from_row!(row->ips(json); DatabaseError::ValueError),
363            token_context: from_row!(row->token_context(json); DatabaseError::ValueError),
364            metadata,
365            badges: from_row!(row->badges(json); DatabaseError::ValueError),
366            group: from_row!(row->gid(i32); 0),
367            joined: from_row!(row->joined(u128); 0),
368            tier: from_row!(row->tier(i32); 0),
369            labels: from_row!(row->labels(json); DatabaseError::ValueError),
370            coins: from_row!(row->coins(i32); 0),
371            links: from_row!(row->links(json); DatabaseError::ValueError),
372            layout: from_row!(row->layout(json); DatabaseError::ValueError),
373            question_count: from_row!(row->question_count(usize); 0),
374            response_count: cache_sync!(
375                |row, id| response_count->(update_profile_response_count in self) {1}
376            ),
377            totp: from_row!(row->totp()),
378            recovery_codes: from_row!(row->recovery_codes(json); DatabaseError::ValueError),
379            notification_count: from_row!(row->notification_count(usize); 0),
380            inbox_count: if do_not_clear_inbox_count_on_view {
381                // sync
382                cache_sync!(|row, id| inbox_count->(update_profile_inbox_count in self) {1})
383            } else {
384                from_row!(row->inbox_count(usize); 0)
385            },
386        }))
387    }
388
389    // GET
390    fn is_digit(&self, input: &str) -> bool {
391        for char in input.chars() {
392            if !char.is_numeric() {
393                return false;
394            }
395        }
396
397        true
398    }
399
400    /// Get a profile's username given its `id`.
401    pub async fn get_profile_username(&self, id: &str) -> String {
402        // fetch from database
403        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
404            "SELECT \"username\" FROM \"xprofiles\" WHERE \"id\" = ?"
405        } else {
406            "SELECT \"username\" FROM \"xprofiles\" WHERE \"id\" = $1"
407        };
408
409        let c = &self.base.db.client;
410        let row = match sqlquery(query).bind::<&str>(id).fetch_one(c).await {
411            Ok(u) => self.base.textify_row(u).0,
412            Err(_) => return String::new(),
413        };
414
415        // return
416        from_row!(row->username())
417    }
418
419    /// Fetch a profile correctly.
420    pub async fn get_profile(&self, id: &str) -> Result<Box<Profile>> {
421        let mut id = id.to_string();
422        if id.starts_with("ANSWERED:") {
423            // we use the "ANSWERED" prefix whenever we answer a question so it doesn't show up in inboxes
424            id = id.replace("ANSWERED:", "");
425        }
426
427        if id == "@" {
428            return Ok(Box::new(Profile::global()));
429        } else if id.starts_with("anonymous#") | (id == "anonymous") | (id == "#") {
430            let tag = Profile::anonymous_tag(&id);
431            return Ok(Box::new(Profile::anonymous(tag.3)));
432        } else if (id == "0") | (id == "system") {
433            return Ok(Box::new(Profile::system()));
434        }
435
436        // remove circle tag
437        if id.contains("%") {
438            id = id
439                .split("%")
440                .collect::<Vec<&str>>()
441                .get(0)
442                .unwrap()
443                .to_string();
444        }
445
446        // handle legacy IDs (usernames)
447        if (id.len() <= 32) && (!self.is_digit(&id) | (id.len() < 18)) {
448            return match self.get_profile_by_username(&id).await {
449                Ok(ua) => Ok(ua),
450                Err(e) => return Err(e),
451            };
452        }
453
454        match self.get_profile_by_id(&id).await {
455            Ok(ua) => Ok(ua),
456            Err(e) => return Err(e),
457        }
458    }
459
460    /// Get a [`Profile`] by their hashed ID.
461    ///
462    /// # Arguments:
463    /// * `hashed` - `String` of the profile's hashed ID
464    pub async fn get_profile_by_hashed(&self, hashed: &str) -> Result<Box<Profile>> {
465        // fetch from database
466        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
467            "SELECT * FROM \"xprofiles\" WHERE \"tokens\" LIKE ?"
468        } else {
469            "SELECT * FROM \"xprofiles\" WHERE \"tokens\" LIKE $1"
470        };
471
472        let c = &self.base.db.client;
473        let row = match sqlquery(query)
474            .bind::<&str>(&format!("%\"{hashed}\"%"))
475            .fetch_one(c)
476            .await
477        {
478            Ok(u) => self.base.textify_row(u).0,
479            Err(_) => return Err(DatabaseError::Other),
480        };
481
482        // return
483        Ok(match self.gimme_profile(row).await {
484            Ok(ua) => ua,
485            Err(e) => return Err(e),
486        })
487    }
488
489    /// Get a user by their unhashed ID (hashes ID and then calls [`Database::get_profile_by_hashed()`]).
490    ///
491    /// # Arguments:
492    /// * `unhashed` - `String` of the user's unhashed ID
493    pub async fn get_profile_by_unhashed(&self, unhashed: &str) -> Result<Box<Profile>> {
494        self.get_profile_by_hashed(&utility::hash(unhashed.to_string()))
495            .await
496    }
497
498    /// Get a [`Profile`] by their IP.
499    ///
500    /// # Arguments:
501    /// * `hashed` - `String` of the profile's IP
502    pub async fn get_profile_by_ip(&self, ip: &str) -> Result<Box<Profile>> {
503        // fetch from database
504        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
505            "SELECT * FROM \"xprofiles\" WHERE \"ips\" LIKE ?"
506        } else {
507            "SELECT * FROM \"xprofiles\" WHERE \"ips\" LIKE $1"
508        };
509
510        let c = &self.base.db.client;
511        let row = match sqlquery(query)
512            .bind::<&str>(&format!("%\"{ip}\"%"))
513            .fetch_one(c)
514            .await
515        {
516            Ok(u) => self.base.textify_row(u).0,
517            Err(_) => return Err(DatabaseError::Other),
518        };
519
520        // return
521        Ok(match self.gimme_profile(row).await {
522            Ok(ua) => ua,
523            Err(e) => return Err(e),
524        })
525    }
526
527    /// Get a user by their username.
528    ///
529    /// # Arguments:
530    /// * `username` - `String` of the user's username
531    pub async fn get_profile_by_username(&self, username: &str) -> Result<Box<Profile>> {
532        let username = username.to_lowercase();
533
534        // check in cache
535        let cached = self
536            .base
537            .cache
538            .get(format!("rbeam.auth.profile:{}", username))
539            .await;
540
541        if cached.is_some() {
542            match serde_json::from_str::<Profile>(cached.unwrap().as_str()) {
543                Ok(p) => return Ok(Box::new(p)),
544                Err(_) => {
545                    self.base
546                        .cache
547                        .remove(format!("rbeam.auth.profile:{}", username))
548                        .await;
549                }
550            };
551        }
552
553        // ...
554        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
555            "SELECT * FROM \"xprofiles\" WHERE \"username\" = ?"
556        } else {
557            "SELECT * FROM \"xprofiles\" WHERE \"username\" = $1"
558        };
559
560        let c = &self.base.db.client;
561        let row = match sqlquery(query).bind::<&str>(&username).fetch_one(c).await {
562            Ok(r) => self.base.textify_row(r).0,
563            Err(_) => return Err(DatabaseError::NotFound),
564        };
565
566        // store in cache
567        let user = match self.gimme_profile(row).await {
568            Ok(ua) => ua,
569            Err(e) => return Err(e),
570        };
571
572        self.base
573            .cache
574            .set(
575                format!("rbeam.auth.profile:{}", username),
576                serde_json::to_string::<Profile>(&user).unwrap(),
577            )
578            .await;
579
580        // return
581        Ok(user)
582    }
583
584    /// Get a user by their id.
585    ///
586    /// # Arguments:
587    /// * `id` - `String` of the user's username
588    pub async fn get_profile_by_id(&self, id: &str) -> Result<Box<Profile>> {
589        let id = id.to_lowercase();
590
591        // check in cache
592        let cached = self
593            .base
594            .cache
595            .get(format!("rbeam.auth.profile:{}", id))
596            .await;
597
598        if cached.is_some() {
599            match serde_json::from_str::<Profile>(cached.unwrap().as_str()) {
600                Ok(p) => return Ok(Box::new(p)),
601                Err(_) => {
602                    self.base
603                        .cache
604                        .remove(format!("rbeam.auth.profile:{}", id))
605                        .await;
606                }
607            };
608        }
609
610        // ...
611        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
612            "SELECT * FROM \"xprofiles\" WHERE \"id\" = ?"
613        } else {
614            "SELECT * FROM \"xprofiles\" WHERE \"id\" = $1"
615        };
616
617        let c = &self.base.db.client;
618        let row = match sqlquery(query).bind::<&String>(&id).fetch_one(c).await {
619            Ok(r) => self.base.textify_row(r).0,
620            Err(_) => return Err(DatabaseError::NotFound),
621        };
622
623        // store in cache
624        let user = match self.gimme_profile(row).await {
625            Ok(ua) => ua,
626            Err(e) => return Err(e),
627        };
628
629        self.base
630            .cache
631            .set(
632                format!("rbeam.auth.profile:{}", id),
633                serde_json::to_string::<Profile>(&user).unwrap(),
634            )
635            .await;
636
637        // return
638        Ok(user)
639    }
640
641    /// Validate a username.
642    pub fn validate_username(username: &str) -> Result<()> {
643        let banned_usernames = &[
644            "admin",
645            "account",
646            "anonymous",
647            "login",
648            "sign_up",
649            "settings",
650            "api",
651            "intents",
652            "circles",
653            "sites",
654            "responses",
655            "questions",
656            "comments",
657            "response",
658            "question",
659            "comment",
660            "pages",
661            "inbox",
662            "system",
663            "market",
664            ".well-known",
665            "static",
666        ];
667
668        let regex = regex::RegexBuilder::new(r"[^\w_\-\.!]+")
669            .multi_line(true)
670            .build()
671            .unwrap();
672
673        if regex.captures(&username).is_some() {
674            return Err(DatabaseError::ValueError);
675        }
676
677        if (username.len() < 2) | (username.len() > 500) {
678            return Err(DatabaseError::ValueError);
679        }
680
681        if banned_usernames.contains(&username) {
682            return Err(DatabaseError::ValueError);
683        }
684
685        Ok(())
686    }
687
688    // SET
689    /// Create a new user given their username. Returns their unhashed token.
690    ///
691    /// # Arguments:
692    /// * `username` - `String` of the user's `username`
693    /// * `user_ip` - the ip address of the user registering
694    pub async fn create_profile(&self, props: ProfileCreate, user_ip: &str) -> Result<String> {
695        if self.config.registration_enabled == false {
696            return Err(DatabaseError::NotAllowed);
697        }
698
699        // ...
700        let username = props.username.trim();
701        let password = props.password.trim();
702
703        // check captcha
704        if let Err(_) = props
705            .valid_response(&self.config.captcha.secret, None)
706            .await
707        {
708            return Err(DatabaseError::NotAllowed);
709        }
710
711        // make sure user doesn't already exists
712        if let Ok(_) = &self.get_profile_by_username(username).await {
713            return Err(DatabaseError::UsernameTaken);
714        };
715
716        // check username
717        if let Err(e) = Database::validate_username(username) {
718            return Err(e);
719        }
720
721        // ...
722        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
723            "INSERT INTO \"xprofiles\" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
724        } else {
725            "INSERT INTO \"xprofiles\" VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)"
726        };
727
728        let user_token_unhashed: &str = &databeam::utility::uuid();
729        let user_token_hashed: &str = &databeam::utility::hash(user_token_unhashed.to_string());
730        let salt: &str = &rainbeam_shared::hash::salt();
731
732        let timestamp = utility::unix_epoch_timestamp();
733
734        let c = &self.base.db.client;
735        match sqlquery(query)
736            // .bind::<&str>(&databeam::utility::uuid())
737            .bind::<&str>(&AlmostSnowflake::new(self.config.snowflake_server_id).to_string())
738            .bind::<&str>(&username.to_lowercase())
739            .bind::<&str>(&rainbeam_shared::hash::hash_salted(
740                password.to_string(),
741                salt.to_string(),
742            ))
743            .bind::<&str>(&serde_json::to_string::<Vec<&str>>(&vec![user_token_hashed]).unwrap())
744            .bind::<&str>(
745                &serde_json::to_string::<ProfileMetadata>(&ProfileMetadata::default()).unwrap(),
746            )
747            .bind::<&String>(&timestamp.to_string())
748            .bind::<i8>(0)
749            .bind::<&str>(&salt)
750            .bind::<&str>(&serde_json::to_string::<Vec<&str>>(&vec![user_ip]).unwrap())
751            .bind::<&str>("[]")
752            .bind::<i8>(0)
753            .bind::<&str>("[]")
754            .bind::<i8>(0)
755            .bind::<&str>("{}")
756            .bind::<&str>("{\"json\":\"default.json\"}")
757            .bind::<i8>(0)
758            .bind::<i8>(0)
759            .bind::<&str>("")
760            .bind::<&str>("[]")
761            .bind::<i8>(0)
762            .bind::<i8>(0)
763            .bind::<&str>("[]")
764            .execute(c)
765            .await
766        {
767            Ok(_) => Ok(user_token_unhashed.to_string()),
768            Err(_) => Err(DatabaseError::Other),
769        }
770    }
771
772    update_profile_count!(update_profile_question_count, question_count);
773    update_profile_count!(update_profile_response_count, response_count);
774    update_profile_count!(update_profile_inbox_count, inbox_count);
775    update_profile_count!(update_profile_notification_count, notification_count);
776
777    /// Update a [`Profile`]'s metadata by its `id`.
778    pub async fn update_profile_metadata(
779        &self,
780        id: &str,
781        mut metadata: ProfileMetadata,
782    ) -> Result<()> {
783        // make sure user exists
784        let profile = match self.get_profile(&id).await {
785            Ok(ua) => ua,
786            Err(e) => return Err(e),
787        };
788
789        // check metadata kv
790        for kv in metadata.kv.clone() {
791            if !ALLOWED_CUSTOM_KEYS.contains(&kv.0.as_str()) {
792                metadata.kv.remove(&kv.0);
793            }
794        }
795
796        if !metadata.check() {
797            return Err(DatabaseError::TooLong);
798        }
799
800        // update user
801        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
802            "UPDATE \"xprofiles\" SET \"metadata\" = ? WHERE \"id\" = ?"
803        } else {
804            "UPDATE \"xprofiles\" SET (\"metadata\") = ($1) WHERE \"id\" = $2"
805        };
806
807        let c = &self.base.db.client;
808        let meta = &serde_json::to_string(&metadata).unwrap();
809        match sqlquery(query)
810            .bind::<&str>(meta)
811            .bind::<&str>(&id)
812            .execute(c)
813            .await
814        {
815            Ok(_) => {
816                self.base
817                    .cache
818                    .remove(format!("rbeam.auth.profile:{}", profile.username))
819                    .await;
820
821                self.base
822                    .cache
823                    .remove(format!("rbeam.auth.profile:{}", profile.id))
824                    .await;
825
826                Ok(())
827            }
828            Err(_) => Err(DatabaseError::Other),
829        }
830    }
831
832    /// Update a [`Profile`]'s tokens (and IPs/token_contexts) by its `id`
833    pub async fn update_profile_tokens(
834        &self,
835        id: &str,
836        tokens: Vec<String>,
837        ips: Vec<String>,
838        token_context: Vec<TokenContext>,
839    ) -> Result<()> {
840        // make sure user exists
841        let ua = match self.get_profile(&id).await {
842            Ok(ua) => ua,
843            Err(e) => return Err(e),
844        };
845
846        // update user
847        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
848            "UPDATE \"xprofiles\" SET \"tokens\" = ?, \"ips\" = ?, \"token_context\" = ? WHERE \"id\" = ?"
849        } else {
850            "UPDATE \"xprofiles\" SET (\"tokens\", \"ips\") = ($1, $2, $3) WHERE \"id\" = $4"
851        };
852
853        let c = &self.base.db.client;
854
855        let tokens = &serde_json::to_string(&tokens).unwrap();
856        let ips = &serde_json::to_string(&ips).unwrap();
857        let token_context = &serde_json::to_string(&token_context).unwrap();
858
859        match sqlquery(query)
860            .bind::<&str>(tokens)
861            .bind::<&str>(ips)
862            .bind::<&str>(token_context)
863            .bind::<&str>(&ua.id)
864            .execute(c)
865            .await
866        {
867            Ok(_) => {
868                self.base
869                    .cache
870                    .remove(format!("rbeam.auth.profile:{}", ua.username))
871                    .await;
872
873                self.base
874                    .cache
875                    .remove(format!("rbeam.auth.profile:{}", ua.id))
876                    .await;
877
878                Ok(())
879            }
880            Err(_) => Err(DatabaseError::Other),
881        }
882    }
883
884    /// Update a [`Profile`]'s badges by its `id`
885    pub async fn update_profile_badges(
886        &self,
887        id: &str,
888        badges: Vec<(String, String, String)>,
889    ) -> Result<()> {
890        // make sure user exists
891        let ua = match self.get_profile(&id).await {
892            Ok(ua) => ua,
893            Err(e) => return Err(e),
894        };
895
896        // update user
897        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
898            "UPDATE \"xprofiles\" SET \"badges\" = ? WHERE \"id\" = ?"
899        } else {
900            "UPDATE \"xprofiles\" SET (\"badges\") = ($1) WHERE \"id\" = $2"
901        };
902
903        let c = &self.base.db.client;
904        let badges = &serde_json::to_string(&badges).unwrap();
905
906        match sqlquery(query)
907            .bind::<&str>(badges)
908            .bind::<&str>(&id)
909            .execute(c)
910            .await
911        {
912            Ok(_) => {
913                self.base
914                    .cache
915                    .remove(format!("rbeam.auth.profile:{}", ua.username))
916                    .await;
917
918                self.base
919                    .cache
920                    .remove(format!("rbeam.auth.profile:{}", ua.id))
921                    .await;
922
923                Ok(())
924            }
925            Err(_) => Err(DatabaseError::Other),
926        }
927    }
928
929    /// Update a [`Profile`]'s labels by its `id`
930    pub async fn update_profile_labels(&self, id: &str, labels: Vec<i64>) -> Result<()> {
931        // make sure user exists
932        let ua = match self.get_profile(&id).await {
933            Ok(ua) => ua,
934            Err(e) => return Err(e),
935        };
936
937        // update user
938        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
939            "UPDATE \"xprofiles\" SET \"labels\" = ? WHERE \"id\" = ?"
940        } else {
941            "UPDATE \"xprofiles\" SET (\"labels\") = ($1) WHERE \"id\" = $2"
942        };
943
944        let c = &self.base.db.client;
945        let labels = &serde_json::to_string(&labels).unwrap();
946
947        match sqlquery(query)
948            .bind::<&str>(labels)
949            .bind::<&str>(&id)
950            .execute(c)
951            .await
952        {
953            Ok(_) => {
954                self.base
955                    .cache
956                    .remove(format!("rbeam.auth.profile:{}", ua.username))
957                    .await;
958
959                self.base
960                    .cache
961                    .remove(format!("rbeam.auth.profile:{}", ua.id))
962                    .await;
963
964                Ok(())
965            }
966            Err(_) => Err(DatabaseError::Other),
967        }
968    }
969
970    /// Update a [`Profile`]'s links by its `id`
971    pub async fn update_profile_links(
972        &self,
973        id: &str,
974        links: BTreeMap<String, String>,
975    ) -> Result<()> {
976        // make sure user exists
977        let ua = match self.get_profile(&id).await {
978            Ok(ua) => ua,
979            Err(e) => return Err(e),
980        };
981
982        // update user
983        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
984            "UPDATE \"xprofiles\" SET \"links\" = ? WHERE \"id\" = ?"
985        } else {
986            "UPDATE \"xprofiles\" SET (\"links\") = ($1) WHERE \"id\" = $2"
987        };
988
989        let c = &self.base.db.client;
990        let links = &serde_json::to_string(&links).unwrap();
991
992        match sqlquery(query)
993            .bind::<&str>(links)
994            .bind::<&str>(&id)
995            .execute(c)
996            .await
997        {
998            Ok(_) => {
999                self.base
1000                    .cache
1001                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1002                    .await;
1003
1004                self.base
1005                    .cache
1006                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1007                    .await;
1008
1009                Ok(())
1010            }
1011            Err(_) => Err(DatabaseError::Other),
1012        }
1013    }
1014
1015    /// Update a [`Profile`]'s links by its `id`
1016    pub async fn update_profile_layout(&self, id: &str, layout: LayoutComponent) -> Result<()> {
1017        // make sure user exists
1018        let ua = match self.get_profile(&id).await {
1019            Ok(ua) => ua,
1020            Err(e) => return Err(e),
1021        };
1022
1023        // update user
1024        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1025            "UPDATE \"xprofiles\" SET \"layout\" = ? WHERE \"id\" = ?"
1026        } else {
1027            "UPDATE \"xprofiles\" SET (\"layout\") = ($1) WHERE \"id\" = $2"
1028        };
1029
1030        let c = &self.base.db.client;
1031        let layout = &serde_json::to_string(&layout).unwrap();
1032
1033        match sqlquery(query)
1034            .bind::<&str>(layout)
1035            .bind::<&str>(&id)
1036            .execute(c)
1037            .await
1038        {
1039            Ok(_) => {
1040                self.base
1041                    .cache
1042                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1043                    .await;
1044
1045                self.base
1046                    .cache
1047                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1048                    .await;
1049
1050                Ok(())
1051            }
1052            Err(_) => Err(DatabaseError::Other),
1053        }
1054    }
1055
1056    /// Update a [`Profile`]'s tier by its ID
1057    pub async fn update_profile_tier(&self, id: &str, tier: i32) -> Result<()> {
1058        // make sure user exists
1059        let ua = match self.get_profile(&id).await {
1060            Ok(ua) => ua,
1061            Err(e) => return Err(e),
1062        };
1063
1064        // update user
1065        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1066            "UPDATE \"xprofiles\" SET \"tier\" = ? WHERE \"id\" = ?"
1067        } else {
1068            "UPDATE \"xprofiles\" SET (\"tier\") = ($1) WHERE \"id\" = $2"
1069        };
1070
1071        let c = &self.base.db.client;
1072        match sqlquery(query)
1073            .bind::<i32>(tier)
1074            .bind::<&str>(&id)
1075            .execute(c)
1076            .await
1077        {
1078            Ok(_) => {
1079                self.base
1080                    .cache
1081                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1082                    .await;
1083
1084                self.base
1085                    .cache
1086                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1087                    .await;
1088
1089                Ok(())
1090            }
1091            Err(_) => Err(DatabaseError::Other),
1092        }
1093    }
1094
1095    /// Update a [`Profile`]'s `gid` by its `id`
1096    pub async fn update_profile_group(&self, id: &str, group: i32) -> Result<()> {
1097        // make sure user exists
1098        let ua = match self.get_profile(&id).await {
1099            Ok(ua) => ua,
1100            Err(e) => return Err(e),
1101        };
1102
1103        // update user
1104        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1105            "UPDATE \"xprofiles\" SET \"gid\" = ? WHERE \"id\" = ?"
1106        } else {
1107            "UPDATE \"xprofiles\" SET (\"gid\") = ($1) WHERE \"id\" = $2"
1108        };
1109
1110        let c = &self.base.db.client;
1111        match sqlquery(query)
1112            .bind::<&i32>(&group)
1113            .bind::<&str>(&id)
1114            .execute(c)
1115            .await
1116        {
1117            Ok(_) => {
1118                self.base
1119                    .cache
1120                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1121                    .await;
1122
1123                self.base
1124                    .cache
1125                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1126                    .await;
1127
1128                Ok(())
1129            }
1130            Err(_) => Err(DatabaseError::Other),
1131        }
1132    }
1133
1134    /// Update a [`Profile`]'s coins by its ID
1135    ///
1136    /// # Arguments
1137    /// * `coins` - the amount to ADD to the existing coins value
1138    pub async fn update_profile_coins(&self, id: &str, coins: i32) -> Result<()> {
1139        // make sure user exists
1140        let ua = match self.get_profile(&id).await {
1141            Ok(ua) => ua,
1142            Err(e) => return Err(e),
1143        };
1144
1145        // update user
1146        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1147            "UPDATE \"xprofiles\" SET \"coins\" = ? WHERE \"id\" = ?"
1148        } else {
1149            "UPDATE \"xprofiles\" SET (\"coins\") = ($1) WHERE \"id\" = $2"
1150        };
1151
1152        let c = &self.base.db.client;
1153        match sqlquery(query)
1154            .bind::<i32>(ua.coins + coins)
1155            .bind::<&str>(&id)
1156            .execute(c)
1157            .await
1158        {
1159            Ok(_) => {
1160                self.base
1161                    .cache
1162                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1163                    .await;
1164
1165                self.base
1166                    .cache
1167                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1168                    .await;
1169
1170                Ok(())
1171            }
1172            Err(_) => Err(DatabaseError::Other),
1173        }
1174    }
1175
1176    /// Update a [`Profile`]'s `password` by its name and password
1177    pub async fn update_profile_password(
1178        &self,
1179        id: &str,
1180        password: &str,
1181        new_password: &str,
1182        do_password_check: bool,
1183    ) -> Result<()> {
1184        // make sure user exists
1185        let ua = match self.get_profile(&id).await {
1186            Ok(ua) => ua,
1187            Err(e) => return Err(e),
1188        };
1189
1190        // check password
1191        if do_password_check {
1192            let password_hashed = rainbeam_shared::hash::hash_salted(password.to_string(), ua.salt);
1193
1194            if password_hashed != ua.password {
1195                return Err(DatabaseError::NotAllowed);
1196            }
1197        }
1198
1199        // update user
1200        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1201            "UPDATE \"xprofiles\" SET \"password\" = ?, \"salt\" = ? WHERE \"id\" = ?"
1202        } else {
1203            "UPDATE \"xprofiles\" SET (\"password\", \"salt\") = ($1, $2) WHERE \"id\" = $3"
1204        };
1205
1206        let new_salt = rainbeam_shared::hash::salt();
1207
1208        let c = &self.base.db.client;
1209        match sqlquery(query)
1210            .bind::<&str>(&rainbeam_shared::hash::hash_salted(
1211                new_password.to_string(),
1212                new_salt.clone(),
1213            ))
1214            .bind::<&str>(&new_salt)
1215            .bind::<&str>(&id)
1216            .execute(c)
1217            .await
1218        {
1219            Ok(_) => {
1220                self.base
1221                    .cache
1222                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1223                    .await;
1224
1225                self.base
1226                    .cache
1227                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1228                    .await;
1229
1230                Ok(())
1231            }
1232            Err(_) => Err(DatabaseError::Other),
1233        }
1234    }
1235
1236    /// Update a [`Profile`]'s `username` by its id and password
1237    pub async fn update_profile_username(
1238        &self,
1239        id: &str,
1240        password: &str,
1241        new_name: &str,
1242    ) -> Result<()> {
1243        let new_name = new_name.to_lowercase();
1244
1245        // make sure user exists
1246        let ua = match self.get_profile(&id).await {
1247            Ok(ua) => ua,
1248            Err(e) => return Err(e),
1249        };
1250
1251        // make sure username isn't in use
1252        if let Ok(_) = self.get_profile_by_username(&new_name).await {
1253            return Err(DatabaseError::MustBeUnique);
1254        }
1255
1256        // check username
1257        if let Err(e) = Database::validate_username(&new_name) {
1258            return Err(e);
1259        }
1260
1261        // check password
1262        let password_hashed = rainbeam_shared::hash::hash_salted(password.to_string(), ua.salt);
1263
1264        if password_hashed != ua.password {
1265            return Err(DatabaseError::NotAllowed);
1266        }
1267
1268        // update user
1269        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1270            "UPDATE \"xprofiles\" SET \"username\" = ? WHERE \"id\" = ?"
1271        } else {
1272            "UPDATE \"xprofiles\" SET (\"username\") = ($1) WHERE \"id\" = $2"
1273        };
1274
1275        let c = &self.base.db.client;
1276        match sqlquery(query)
1277            .bind::<&str>(&new_name)
1278            .bind::<&str>(&id)
1279            .execute(c)
1280            .await
1281        {
1282            Ok(_) => {
1283                self.base
1284                    .cache
1285                    .remove(format!("rbeam.auth.profile:{}", ua.username))
1286                    .await;
1287
1288                self.base
1289                    .cache
1290                    .remove(format!("rbeam.auth.profile:{}", ua.id))
1291                    .await;
1292
1293                Ok(())
1294            }
1295            Err(_) => Err(DatabaseError::Other),
1296        }
1297    }
1298
1299    /// Delete a profile
1300    ///
1301    /// **VALIDATION SHOULD BE DONE *BEFORE* THIS!!**
1302    async fn delete_profile(&self, id: &str) -> Result<()> {
1303        let user = self.get_profile_by_id(&id).await.unwrap();
1304
1305        // delete user
1306        let query: &str = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1307            "DELETE FROM \"xprofiles\" WHERE \"id\" = ?"
1308        } else {
1309            "DELETE FROM \"xprofiles\" WHERE \"id\" = $1"
1310        };
1311
1312        let c = &self.base.db.client;
1313        match sqlquery(query).bind::<&str>(&id).execute(c).await {
1314            Ok(_) => {
1315                let query: &str =
1316                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1317                        "DELETE FROM \"xnotifications\" WHERE \"recipient\" = ?"
1318                    } else {
1319                        "DELETE FROM \"xnotifications\" WHERE \"recipient\" = $1"
1320                    };
1321
1322                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1323                    return Err(DatabaseError::Other);
1324                };
1325
1326                let query: &str =
1327                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1328                        "DELETE FROM \"xwarnings\" WHERE \"recipient\" = ?"
1329                    } else {
1330                        "DELETE FROM \"xwarnings\" WHERE \"recipient\" = $1"
1331                    };
1332
1333                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1334                    return Err(DatabaseError::Other);
1335                };
1336
1337                let query: &str =
1338                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1339                        "DELETE FROM \"xfollows\" WHERE \"user\" = ? OR \"following\" = ?"
1340                    } else {
1341                        "DELETE FROM \"xfollows\" WHERE \"user\" = $1 OR \"following\" = $2"
1342                    };
1343
1344                if let Err(_) = sqlquery(query)
1345                    .bind::<&str>(&id)
1346                    .bind::<&str>(&id)
1347                    .execute(c)
1348                    .await
1349                {
1350                    return Err(DatabaseError::Other);
1351                };
1352
1353                // rainbeam crate stuff
1354                // questions to user
1355                let query: &str =
1356                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1357                        "DELETE FROM \"xquestions\" WHERE \"recipient\" = ?"
1358                    } else {
1359                        "DELETE FROM \"xquestions\" WHERE \"recipient\" = $1"
1360                    };
1361
1362                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1363                    return Err(DatabaseError::Other);
1364                };
1365
1366                // questions by user
1367                let query: &str =
1368                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1369                        "DELETE FROM \"xquestions\" WHERE \"author\" = ?"
1370                    } else {
1371                        "DELETE FROM \"xquestions\" WHERE \"author\" = $1"
1372                    };
1373
1374                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1375                    return Err(DatabaseError::Other);
1376                };
1377
1378                // responses by user
1379                let query: &str =
1380                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1381                        "DELETE FROM \"xresponses\" WHERE \"author\" = ?"
1382                    } else {
1383                        "DELETE FROM \"xresponses\" WHERE \"author\" = $1"
1384                    };
1385
1386                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1387                    return Err(DatabaseError::Other);
1388                };
1389
1390                // responses to questions by user
1391                let query: &str =
1392                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1393                        "DELETE FROM \"xresponses\" WHERE \"question\" LIKE ?"
1394                    } else {
1395                        "DELETE FROM \"xresponses\" WHERE \"question\" LIKE $1"
1396                    };
1397
1398                if let Err(_) = sqlquery(query)
1399                    .bind::<&str>(&format!("%\"author\":\"{id}\"%"))
1400                    .execute(c)
1401                    .await
1402                {
1403                    return Err(DatabaseError::Other);
1404                };
1405
1406                self.base
1407                    .cache
1408                    .remove(format!("rbeam.app.response_count:{}", id))
1409                    .await;
1410
1411                self.base
1412                    .cache
1413                    .remove(format!("rbeam.app.global_question_count:{}", id))
1414                    .await;
1415
1416                // relationships involving user
1417                let query: &str =
1418                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1419                        "DELETE FROM \"xrelationships\" WHERE \"one\" = ? OR \"two\" = ?"
1420                    } else {
1421                        "DELETE FROM \"xrelationships\" WHERE \"one\" = $1 OR \"two\" = $2"
1422                    };
1423
1424                if let Err(_) = sqlquery(query)
1425                    .bind::<&str>(&id)
1426                    .bind::<&str>(&id)
1427                    .execute(c)
1428                    .await
1429                {
1430                    return Err(DatabaseError::Other);
1431                };
1432
1433                self.base
1434                    .cache
1435                    .remove(format!("rbeam.app.friends_count:{}", id))
1436                    .await;
1437
1438                // ipblocks by user
1439                let query: &str =
1440                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1441                        "DELETE FROM \"xipblocks\" WHERE \"user\" = ?"
1442                    } else {
1443                        "DELETE FROM \"xipblocks\" WHERE \"user\" = $1"
1444                    };
1445
1446                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1447                    return Err(DatabaseError::Other);
1448                };
1449
1450                // transactions with user
1451                let query: &str = if (self.base.db.r#type == "sqlite")
1452                    | (self.base.db.r#type == "mysql")
1453                {
1454                    "DELETE FROM \"xugc_transactions\" WHERE \"customer\" = ? OR \"merchant\" = ?"
1455                } else {
1456                    "DELETE FROM \"xugc_transactions\" WHERE \"customer\" = $1 OR \"merchant\" = $2"
1457                };
1458
1459                if let Err(_) = sqlquery(query)
1460                    .bind::<&str>(&id)
1461                    .bind::<&str>(&id)
1462                    .execute(c)
1463                    .await
1464                {
1465                    return Err(DatabaseError::Other);
1466                };
1467
1468                // items by user
1469                let query: &str =
1470                    if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1471                        "DELETE FROM \"xugc_items\" WHERE \"creator\" = ?"
1472                    } else {
1473                        "DELETE FROM \"xugc_items\" WHERE \"creator\" = $1"
1474                    };
1475
1476                if let Err(_) = sqlquery(query).bind::<&str>(&id).execute(c).await {
1477                    return Err(DatabaseError::Other);
1478                };
1479
1480                // ...
1481                self.base
1482                    .cache
1483                    .remove(format!("rbeam.auth.profile:{}", id))
1484                    .await;
1485
1486                self.base
1487                    .cache
1488                    .remove(format!("rbeam.auth.profile:{}", user.username))
1489                    .await;
1490
1491                self.base
1492                    .cache
1493                    .remove(format!("rbeam.auth.followers_count:{}", id))
1494                    .await;
1495
1496                self.base
1497                    .cache
1498                    .remove(format!("rbeam.auth.following_count:{}", id))
1499                    .await;
1500
1501                self.base
1502                    .cache
1503                    .remove(format!("rbeam.auth.notification_count:{}", id))
1504                    .await;
1505
1506                // delete images
1507                if !self.config.media_dir.to_string().is_empty() {
1508                    let avatar = pathd!("{}/avatars/{}.avif", self.config.media_dir, id);
1509                    if let Ok(_) = rainbeam_shared::fs::fstat(&avatar) {
1510                        if let Err(_) = rainbeam_shared::fs::remove_file(avatar) {
1511                            return Err(DatabaseError::Other);
1512                        }
1513                    }
1514
1515                    let banner = pathd!("{}/banners/{}.avif", self.config.media_dir, id);
1516                    if let Ok(_) = rainbeam_shared::fs::fstat(&banner) {
1517                        if let Err(_) = rainbeam_shared::fs::remove_file(banner) {
1518                            return Err(DatabaseError::Other);
1519                        }
1520                    }
1521                }
1522
1523                // ...
1524                Ok(())
1525            }
1526            Err(_) => Err(DatabaseError::Other),
1527        }
1528    }
1529
1530    /// Delete an existing [`Profile`] by its `id`
1531    pub async fn delete_profile_by_id(&self, id: &str) -> Result<()> {
1532        let user = match self.get_profile_by_id(&id).await {
1533            Ok(ua) => ua,
1534            Err(e) => return Err(e),
1535        };
1536
1537        // make sure they aren't a manager
1538        let group = match self.get_group_by_id(user.group).await {
1539            Ok(g) => g,
1540            Err(_) => return Err(DatabaseError::Other),
1541        };
1542
1543        if group.permissions.check(FinePermission::DELETE_USER) {
1544            return Err(DatabaseError::NotAllowed);
1545        }
1546
1547        // delete
1548        self.delete_profile(id).await
1549    }
1550
1551    // groups
1552
1553    // GET
1554    /// Get a group by its id
1555    ///
1556    /// # Arguments:
1557    /// * `username` - `String` of the user's username
1558    pub async fn get_group_by_id(&self, id: i32) -> Result<Group> {
1559        // check in cache
1560        let cached = self.base.cache.get(format!("rbeam.auth.gid:{}", id)).await;
1561
1562        if cached.is_some() {
1563            return Ok(serde_json::from_str::<Group>(cached.unwrap().as_str()).unwrap());
1564        }
1565
1566        // ...
1567        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1568            "SELECT * FROM \"xgroups\" WHERE \"id\" = ?"
1569        } else {
1570            "SELECT * FROM \"xgroups\" WHERE \"id\" = $1"
1571        };
1572
1573        let c = &self.base.db.client;
1574        let row = match sqlquery(query).bind::<&i32>(&id).fetch_one(c).await {
1575            Ok(r) => self.base.textify_row(r).0,
1576            Err(_) => return Ok(Group::default()),
1577        };
1578
1579        // store in cache
1580        let group = Group {
1581            name: from_row!(row->name()),
1582            id: row.get("id").unwrap().parse::<i32>().unwrap(),
1583            permissions: match serde_json::from_str(row.get("permissions").unwrap()) {
1584                Ok(m) => m,
1585                Err(_) => return Err(DatabaseError::ValueError),
1586            },
1587        };
1588
1589        self.base
1590            .cache
1591            .set(
1592                format!("rbeam.auth.gid:{}", id),
1593                serde_json::to_string::<Group>(&group).unwrap(),
1594            )
1595            .await;
1596
1597        // return
1598        Ok(group)
1599    }
1600
1601    // profiles
1602
1603    // GET
1604    /// Get an existing [`UserFollow`]
1605    ///
1606    /// # Arguments:
1607    /// * `user`
1608    /// * `following`
1609    pub async fn get_follow(&self, user: &str, following: &str) -> Result<UserFollow> {
1610        // fetch from database
1611        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1612            "SELECT * FROM \"xfollows\" WHERE \"user\" = ? AND \"following\" = ?"
1613        } else {
1614            "SELECT * FROM \"xfollows\" WHERE \"user\" = $1 AND \"following\" = $2"
1615        };
1616
1617        let c = &self.base.db.client;
1618        let row = match sqlquery(query)
1619            .bind::<&str>(&user)
1620            .bind::<&str>(&following)
1621            .fetch_one(c)
1622            .await
1623        {
1624            Ok(u) => self.base.textify_row(u).0,
1625            Err(_) => return Err(DatabaseError::Other),
1626        };
1627
1628        // return
1629        Ok(UserFollow {
1630            user: from_row!(row->user()),
1631            following: from_row!(row->following()),
1632        })
1633    }
1634
1635    /// Get all existing [`UserFollow`]s where `following` is the value of `user`
1636    ///
1637    /// # Arguments:
1638    /// * `user`
1639    pub async fn get_followers(
1640        &self,
1641        user: &str,
1642    ) -> Result<Vec<(UserFollow, Box<Profile>, Box<Profile>)>> {
1643        // fetch from database
1644        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1645            "SELECT * FROM \"xfollows\" WHERE \"following\" = ?"
1646        } else {
1647            "SELECT * FROM \"xfollows\" WHERE \"following\" = $1"
1648        };
1649
1650        let c = &self.base.db.client;
1651        let res = match sqlquery(query).bind::<&str>(&user).fetch_all(c).await {
1652            Ok(u) => {
1653                let mut out = Vec::new();
1654
1655                for row in u {
1656                    let row = self.base.textify_row(row).0;
1657
1658                    let user = from_row!(row->user());
1659                    let following = from_row!(row->following());
1660
1661                    out.push((
1662                        UserFollow {
1663                            user: user.clone(),
1664                            following: following.clone(),
1665                        },
1666                        match self.get_profile_by_id(&user).await {
1667                            Ok(ua) => ua,
1668                            Err(e) => return Err(e),
1669                        },
1670                        match self.get_profile_by_id(&following).await {
1671                            Ok(ua) => ua,
1672                            Err(e) => return Err(e),
1673                        },
1674                    ))
1675                }
1676
1677                out
1678            }
1679            Err(_) => return Err(DatabaseError::Other),
1680        };
1681
1682        // return
1683        Ok(res)
1684    }
1685
1686    /// Get all existing [`UserFollow`]s where `following` is the value of `user`, 12 at a time
1687    ///
1688    /// # Arguments:
1689    /// * `user`
1690    /// * `page`
1691    pub async fn get_followers_paginated(
1692        &self,
1693        user: &str,
1694        page: i32,
1695    ) -> Result<Vec<(UserFollow, Box<Profile>, Box<Profile>)>> {
1696        // fetch from database
1697        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1698            format!(
1699                "SELECT * FROM \"xfollows\" WHERE \"following\" = ? LIMIT 12 OFFSET {}",
1700                page * 12
1701            )
1702        } else {
1703            format!(
1704                "SELECT * FROM \"xfollows\" WHERE \"following\" = $1 LIMIT 12 OFFSET {}",
1705                page * 12
1706            )
1707        };
1708
1709        let c = &self.base.db.client;
1710        let res = match sqlquery(&query).bind::<&str>(&user).fetch_all(c).await {
1711            Ok(u) => {
1712                let mut out = Vec::new();
1713
1714                for row in u {
1715                    let row = self.base.textify_row(row).0;
1716
1717                    let user = from_row!(row->user());
1718                    let following = from_row!(row->following());
1719
1720                    out.push((
1721                        UserFollow {
1722                            user: user.clone(),
1723                            following: following.clone(),
1724                        },
1725                        match self.get_profile_by_id(&user).await {
1726                            Ok(ua) => ua,
1727                            Err(_) => continue,
1728                        },
1729                        match self.get_profile_by_id(&following).await {
1730                            Ok(ua) => ua,
1731                            Err(_) => continue,
1732                        },
1733                    ))
1734                }
1735
1736                out
1737            }
1738            Err(_) => return Err(DatabaseError::Other),
1739        };
1740
1741        // return
1742        Ok(res)
1743    }
1744
1745    /// Get the number of followers `user` has
1746    ///
1747    /// # Arguments:
1748    /// * `user`
1749    pub async fn get_followers_count(&self, user: &str) -> usize {
1750        // attempt to fetch from cache
1751        if let Some(count) = self
1752            .base
1753            .cache
1754            .get(format!("rbeam.auth.followers_count:{}", user))
1755            .await
1756        {
1757            return count.parse::<usize>().unwrap_or(0);
1758        };
1759
1760        // fetch from database
1761        let count = self.get_followers(user).await.unwrap_or(Vec::new()).len();
1762
1763        self.base
1764            .cache
1765            .set(
1766                format!("rbeam.auth.followers_count:{}", user),
1767                count.to_string(),
1768            )
1769            .await;
1770
1771        count
1772    }
1773
1774    /// Get all existing [`UserFollow`]s where `user` is the value of `user`
1775    ///
1776    /// # Arguments:
1777    /// * `user`
1778    pub async fn get_following(
1779        &self,
1780        user: &str,
1781    ) -> Result<Vec<(UserFollow, Box<Profile>, Box<Profile>)>> {
1782        // fetch from database
1783        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1784            "SELECT * FROM \"xfollows\" WHERE \"user\" = ?"
1785        } else {
1786            "SELECT * FROM \"xfollows\" WHERE \"user\" = $1"
1787        };
1788
1789        let c = &self.base.db.client;
1790        let res = match sqlquery(query).bind::<&str>(&user).fetch_all(c).await {
1791            Ok(u) => {
1792                let mut out = Vec::new();
1793
1794                for row in u {
1795                    let row = self.base.textify_row(row).0;
1796
1797                    let user = from_row!(row->user());
1798                    let following = from_row!(row->following());
1799
1800                    out.push((
1801                        UserFollow {
1802                            user: user.clone(),
1803                            following: following.clone(),
1804                        },
1805                        match self.get_profile_by_id(&user).await {
1806                            Ok(ua) => ua,
1807                            Err(_) => continue,
1808                        },
1809                        match self.get_profile_by_id(&following).await {
1810                            Ok(ua) => ua,
1811                            Err(_) => continue,
1812                        },
1813                    ))
1814                }
1815
1816                out
1817            }
1818            Err(_) => return Err(DatabaseError::Other),
1819        };
1820
1821        // return
1822        Ok(res)
1823    }
1824
1825    /// Get all existing [`UserFollow`]s where `user` is the value of `user`, 12 at a time
1826    ///
1827    /// # Arguments:
1828    /// * `user`
1829    /// * `page`
1830    pub async fn get_following_paginated(
1831        &self,
1832        user: &str,
1833        page: i32,
1834    ) -> Result<Vec<(UserFollow, Box<Profile>, Box<Profile>)>> {
1835        // fetch from database
1836        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1837            format!(
1838                "SELECT * FROM \"xfollows\" WHERE \"user\" = ? LIMIT 12 OFFSET {}",
1839                page * 12
1840            )
1841        } else {
1842            format!(
1843                "SELECT * FROM \"xfollows\" WHERE \"user\" = $1 LIMIT 12 OFFSET {}",
1844                page * 12
1845            )
1846        };
1847
1848        let c = &self.base.db.client;
1849        let res = match sqlquery(&query).bind::<&str>(&user).fetch_all(c).await {
1850            Ok(u) => {
1851                let mut out = Vec::new();
1852
1853                for row in u {
1854                    let row = self.base.textify_row(row).0;
1855
1856                    let user = from_row!(row->user());
1857                    let following = from_row!(row->following());
1858
1859                    out.push((
1860                        UserFollow {
1861                            user: user.clone(),
1862                            following: following.clone(),
1863                        },
1864                        match self.get_profile_by_id(&user).await {
1865                            Ok(ua) => ua,
1866                            Err(_) => continue,
1867                        },
1868                        match self.get_profile_by_id(&following).await {
1869                            Ok(ua) => ua,
1870                            Err(_) => continue,
1871                        },
1872                    ))
1873                }
1874
1875                out
1876            }
1877            Err(_) => return Err(DatabaseError::Other),
1878        };
1879
1880        // return
1881        Ok(res)
1882    }
1883
1884    /// Get the number of users `user` is following
1885    ///
1886    /// # Arguments:
1887    /// * `user`
1888    pub async fn get_following_count(&self, user: &str) -> usize {
1889        // attempt to fetch from cache
1890        if let Some(count) = self
1891            .base
1892            .cache
1893            .get(format!("rbeam.auth.following_count:{}", user))
1894            .await
1895        {
1896            return count.parse::<usize>().unwrap_or(0);
1897        };
1898
1899        // fetch from database
1900        let count = self.get_following(user).await.unwrap_or(Vec::new()).len();
1901
1902        self.base
1903            .cache
1904            .set(
1905                format!("rbeam.auth.following_count:{}", user),
1906                count.to_string(),
1907            )
1908            .await;
1909
1910        count
1911    }
1912
1913    // SET
1914    /// Toggle the following status of `user` on `following` ([`UserFollow`])
1915    ///
1916    /// # Arguments:
1917    /// * `props` - [`UserFollow`]
1918    pub async fn toggle_user_follow(&self, props: &mut UserFollow) -> Result<()> {
1919        // users cannot be the same
1920        if props.user == props.following {
1921            return Err(DatabaseError::Other);
1922        }
1923
1924        // make sure both users exist
1925        let user_1 = match self.get_profile(&props.user).await {
1926            Ok(ua) => ua,
1927            Err(e) => return Err(e),
1928        };
1929
1930        // make sure both users exist
1931        if let Err(e) = self.get_profile(&props.following).await {
1932            return Err(e);
1933        };
1934
1935        // check if follow exists
1936        if let Ok(_) = self.get_follow(&props.user, &props.following).await {
1937            // delete
1938            let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1939                "DELETE FROM \"xfollows\" WHERE \"user\" = ? AND \"following\" = ?"
1940            } else {
1941                "DELETE FROM \"xfollows\" WHERE \"user\" = $1 AND \"following\" = $2"
1942            };
1943
1944            let c = &self.base.db.client;
1945            match sqlquery(&query)
1946                .bind::<&str>(&props.user)
1947                .bind::<&str>(&props.following)
1948                .execute(c)
1949                .await
1950            {
1951                Ok(_) => {
1952                    self.base
1953                        .cache
1954                        .decr(format!("rbeam.auth.following_count:{}", props.user))
1955                        .await;
1956
1957                    self.base
1958                        .cache
1959                        .decr(format!("rbeam.auth.followers_count:{}", props.following))
1960                        .await;
1961
1962                    return Ok(());
1963                }
1964                Err(_) => return Err(DatabaseError::Other),
1965            };
1966        }
1967
1968        // return
1969        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
1970            "INSERT INTO \"xfollows\" VALUES (?, ?)"
1971        } else {
1972            "INSERT INTO \"xfollows\" VALUES ($1, $2)"
1973        };
1974
1975        let c = &self.base.db.client;
1976        match sqlquery(&query)
1977            .bind::<&str>(&props.user)
1978            .bind::<&str>(&props.following)
1979            .execute(c)
1980            .await
1981        {
1982            Ok(_) => {
1983                // bump counts
1984                self.base
1985                    .cache
1986                    .incr(format!("rbeam.auth.following_count:{}", props.user))
1987                    .await;
1988
1989                self.base
1990                    .cache
1991                    .incr(format!("rbeam.auth.followers_count:{}", props.following))
1992                    .await;
1993
1994                // create notification
1995                if let Err(e) = self
1996                    .create_notification(
1997                        NotificationCreate {
1998                            title: format!(
1999                                "[@{}](/+u/{}) followed you!",
2000                                user_1.username, user_1.id
2001                            ),
2002                            content: String::new(),
2003                            address: format!("/+u/{}", user_1.id),
2004                            recipient: props.following.clone(),
2005                        },
2006                        None,
2007                    )
2008                    .await
2009                {
2010                    return Err(e);
2011                };
2012
2013                // return
2014                Ok(())
2015            }
2016            Err(_) => Err(DatabaseError::Other),
2017        }
2018    }
2019
2020    /// Force remove the following status of `user` on `following` ([`UserFollow`])
2021    ///
2022    /// # Arguments:
2023    /// * `props` - [`UserFollow`]
2024    pub async fn force_remove_user_follow(&self, props: &mut UserFollow) -> Result<()> {
2025        // users cannot be the same
2026        if props.user == props.following {
2027            return Err(DatabaseError::Other);
2028        }
2029
2030        // check if follow exists
2031        if let Ok(_) = self.get_follow(&props.user, &props.following).await {
2032            // delete
2033            let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2034                "DELETE FROM \"xfollows\" WHERE \"user\" = ? AND \"following\" = ?"
2035            } else {
2036                "DELETE FROM \"xfollows\" WHERE \"user\" = $1 AND \"following\" = $2"
2037            };
2038
2039            let c = &self.base.db.client;
2040            match sqlquery(&query)
2041                .bind::<&str>(&props.user)
2042                .bind::<&str>(&props.following)
2043                .execute(c)
2044                .await
2045            {
2046                Ok(_) => {
2047                    self.base
2048                        .cache
2049                        .decr(format!("rbeam.auth.following_count:{}", props.user))
2050                        .await;
2051
2052                    self.base
2053                        .cache
2054                        .decr(format!("rbeam.auth.followers_count:{}", props.following))
2055                        .await;
2056
2057                    return Ok(());
2058                }
2059                Err(_) => return Err(DatabaseError::Other),
2060            };
2061        }
2062
2063        // return
2064        // we can only remove following here, not add it
2065        Ok(())
2066    }
2067
2068    // notifications
2069
2070    // GET
2071    /// Get an existing notification
2072    ///
2073    /// # Arguments
2074    /// * `id`
2075    pub async fn get_notification(&self, id: &str) -> Result<Notification> {
2076        // check in cache
2077        match self
2078            .base
2079            .cache
2080            .get(format!("rbeam.auth.notification:{}", id))
2081            .await
2082        {
2083            Some(c) => return Ok(serde_json::from_str::<Notification>(c.as_str()).unwrap()),
2084            None => (),
2085        };
2086
2087        // pull from database
2088        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2089            "SELECT * FROM \"xnotifications\" WHERE \"id\" = ?"
2090        } else {
2091            "SELECT * FROM \"xnotifications\" WHERE \"id\" = $1"
2092        };
2093
2094        let c = &self.base.db.client;
2095        let res = match sqlquery(&query).bind::<&str>(&id).fetch_one(c).await {
2096            Ok(p) => self.base.textify_row(p).0,
2097            Err(_) => return Err(DatabaseError::NotFound),
2098        };
2099
2100        // return
2101        let notification = Notification {
2102            title: from_row!(res->title()),
2103            content: from_row!(res->content()),
2104            address: from_row!(res->address()),
2105            timestamp: from_row!(res->timestamp(u128); 0),
2106            id: from_row!(res->id()),
2107            recipient: from_row!(res->recipient()),
2108        };
2109
2110        // store in cache
2111        self.base
2112            .cache
2113            .set(
2114                format!("rbeam.auth.notification:{}", id),
2115                serde_json::to_string::<Notification>(&notification).unwrap(),
2116            )
2117            .await;
2118
2119        // return
2120        Ok(notification)
2121    }
2122
2123    /// Get all notifications by their recipient
2124    ///
2125    /// # Arguments
2126    /// * `recipient`
2127    pub async fn get_notifications_by_recipient(
2128        &self,
2129        recipient: &str,
2130    ) -> Result<Vec<Notification>> {
2131        // pull from database
2132        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2133            "SELECT * FROM \"xnotifications\" WHERE \"recipient\" = ? ORDER BY \"timestamp\" DESC"
2134        } else {
2135            "SELECT * FROM \"xnotifications\" WHERE \"recipient\" = $1 ORDER BY \"timestamp\" DESC"
2136        };
2137
2138        let c = &self.base.db.client;
2139        let res = match sqlquery(&query)
2140            .bind::<&str>(&recipient.to_lowercase())
2141            .fetch_all(c)
2142            .await
2143        {
2144            Ok(p) => {
2145                let mut out: Vec<Notification> = Vec::new();
2146
2147                for row in p {
2148                    let res = self.base.textify_row(row).0;
2149                    out.push(Notification {
2150                        title: from_row!(res->title()),
2151                        content: from_row!(res->content()),
2152                        address: from_row!(res->address()),
2153                        timestamp: from_row!(res->timestamp(u128); 0),
2154                        id: from_row!(res->id()),
2155                        recipient: from_row!(res->recipient()),
2156                    });
2157                }
2158
2159                out
2160            }
2161            Err(_) => return Err(DatabaseError::NotFound),
2162        };
2163
2164        // return
2165        Ok(res)
2166    }
2167
2168    /// Get the number of notifications by their recipient
2169    ///
2170    /// # Arguments
2171    /// * `recipient`
2172    pub async fn get_notification_count_by_recipient_cache(&self, recipient: &str) -> usize {
2173        // attempt to fetch from cache
2174        if let Some(count) = self
2175            .base
2176            .cache
2177            .get(format!("rbeam.auth.notification_count:{}", recipient))
2178            .await
2179        {
2180            return count.parse::<usize>().unwrap_or(0);
2181        };
2182
2183        // fetch from database
2184        let count = self
2185            .get_notifications_by_recipient(recipient)
2186            .await
2187            .unwrap_or(Vec::new())
2188            .len();
2189
2190        self.base
2191            .cache
2192            .set(
2193                format!("rbeam.auth.notification_count:{}", recipient),
2194                count.to_string(),
2195            )
2196            .await;
2197
2198        count
2199    }
2200
2201    /// Get the number of notifications by their recipient
2202    ///
2203    /// # Arguments
2204    /// * `recipient`
2205    pub async fn get_notification_count_by_recipient(&self, recipient: &str) -> usize {
2206        match self.get_profile(recipient).await {
2207            Ok(x) => x.notification_count,
2208            Err(_) => 0,
2209        }
2210    }
2211
2212    /// Get all notifications by their recipient, 12 at a time
2213    ///
2214    /// # Arguments
2215    /// * `recipient`
2216    /// * `page`
2217    pub async fn get_notifications_by_recipient_paginated(
2218        &self,
2219        recipient: &str,
2220        page: i32,
2221    ) -> Result<Vec<Notification>> {
2222        // pull from database
2223        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2224            format!("SELECT * FROM \"xnotifications\" WHERE \"recipient\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
2225        } else {
2226            format!("SELECT * FROM \"xnotifications\" WHERE \"recipient\" = $1 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
2227        };
2228
2229        let c = &self.base.db.client;
2230        let res = match sqlquery(&query)
2231            .bind::<&str>(&recipient.to_lowercase())
2232            .fetch_all(c)
2233            .await
2234        {
2235            Ok(p) => {
2236                let mut out: Vec<Notification> = Vec::new();
2237
2238                for row in p {
2239                    let res = self.base.textify_row(row).0;
2240                    out.push(Notification {
2241                        title: from_row!(res->title()),
2242                        content: from_row!(res->content()),
2243                        address: from_row!(res->address()),
2244                        timestamp: from_row!(res->timestamp(u128); 0),
2245                        id: from_row!(res->id()),
2246                        recipient: from_row!(res->recipient()),
2247                    });
2248                }
2249
2250                out
2251            }
2252            Err(_) => return Err(DatabaseError::NotFound),
2253        };
2254
2255        // return
2256        Ok(res)
2257    }
2258
2259    // SET
2260    /// Create a new notification
2261    ///
2262    /// # Arguments
2263    /// * `props` - [`NotificationCreate`]
2264    pub async fn create_notification(
2265        &self,
2266        props: NotificationCreate,
2267        id: Option<String>,
2268    ) -> Result<()> {
2269        let notification = Notification {
2270            title: props.title,
2271            content: props.content,
2272            address: props.address,
2273            timestamp: utility::unix_epoch_timestamp(),
2274            id: if let Some(id) = id {
2275                id
2276            } else {
2277                AlmostSnowflake::new(self.config.snowflake_server_id).to_string()
2278            },
2279            recipient: props.recipient,
2280        };
2281
2282        // create notification
2283        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2284            "INSERT INTO \"xnotifications\" VALUES (?, ?, ?, ?, ?, ?)"
2285        } else {
2286            "INSERT INTO \"xnotifications\" VALUES ($1, $2, $3, $4, $5, $6)"
2287        };
2288
2289        let c = &self.base.db.client;
2290        match sqlquery(&query)
2291            .bind::<&str>(&notification.title)
2292            .bind::<&str>(&notification.content)
2293            .bind::<&str>(&notification.address)
2294            .bind::<&str>(&notification.timestamp.to_string())
2295            .bind::<&str>(&notification.id)
2296            .bind::<&str>(&notification.recipient)
2297            .execute(c)
2298            .await
2299        {
2300            Ok(_) => {
2301                // incr notifications count
2302                self.base
2303                    .cache
2304                    .incr(format!(
2305                        "rbeam.auth.notification_count:{}",
2306                        notification.recipient
2307                    ))
2308                    .await;
2309
2310                // check recipient
2311                if !notification.recipient.starts_with("*") {
2312                    let recipient =
2313                        simplify!(self.get_profile(&notification.recipient).await; Result);
2314
2315                    simplify!(
2316                        self.update_profile_notification_count(
2317                            &notification.recipient,
2318                            recipient.notification_count + 1,
2319                        )
2320                        .await; Err
2321                    );
2322                }
2323
2324                // ...
2325                return Ok(());
2326            }
2327            Err(_) => return Err(DatabaseError::Other),
2328        };
2329    }
2330
2331    /// Delete an existing notification
2332    ///
2333    /// Notifications can only be deleted by their recipient.
2334    ///
2335    /// # Arguments
2336    /// * `id` - the ID of the notification
2337    /// * `user` - the user doing this
2338    pub async fn delete_notification(&self, id: &str, user: Box<Profile>) -> Result<()> {
2339        // make sure notification exists
2340        let notification = match self.get_notification(id).await {
2341            Ok(n) => n,
2342            Err(e) => return Err(e),
2343        };
2344
2345        // check username
2346        if user.id != notification.recipient {
2347            // check permission
2348            let group = match self.get_group_by_id(user.group).await {
2349                Ok(g) => g,
2350                Err(_) => return Err(DatabaseError::Other),
2351            };
2352
2353            if !group
2354                .permissions
2355                .check(FinePermission::MANAGE_NOTIFICATIONS)
2356            {
2357                return Err(DatabaseError::NotAllowed);
2358            }
2359        }
2360
2361        // delete notification
2362        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2363            "DELETE FROM \"xnotifications\" WHERE \"id\" = ?"
2364        } else {
2365            "DELETE FROM \"xnotifications\" WHERE \"id\" = $1"
2366        };
2367
2368        let c = &self.base.db.client;
2369        match sqlquery(&query).bind::<&str>(&id).execute(c).await {
2370            Ok(_) => {
2371                // decr notifications count
2372                self.base
2373                    .cache
2374                    .decr(format!(
2375                        "rbeam.auth.notification_count:{}",
2376                        notification.recipient
2377                    ))
2378                    .await;
2379
2380                // remove from cache
2381                self.base
2382                    .cache
2383                    .remove(format!("rbeam.auth.notification:{}", id))
2384                    .await;
2385
2386                // return
2387                return Ok(());
2388            }
2389            Err(_) => return Err(DatabaseError::Other),
2390        };
2391    }
2392
2393    /// Delete all existing notifications by their recipient
2394    ///
2395    /// # Arguments
2396    /// * `id` - the ID of the notification
2397    /// * `user` - the user doing this
2398    pub async fn delete_notifications_by_recipient(
2399        &self,
2400        recipient: &str,
2401        user: Box<Profile>,
2402    ) -> Result<()> {
2403        // make sure notifications exists
2404        let notifications = match self.get_notifications_by_recipient(recipient).await {
2405            Ok(n) => n,
2406            Err(e) => return Err(e),
2407        };
2408
2409        // check username
2410        if user.id != recipient {
2411            // check permission
2412            let group = match self.get_group_by_id(user.group).await {
2413                Ok(g) => g,
2414                Err(_) => return Err(DatabaseError::Other),
2415            };
2416
2417            if !group
2418                .permissions
2419                .check(FinePermission::MANAGE_NOTIFICATIONS)
2420            {
2421                return Err(DatabaseError::NotAllowed);
2422            }
2423        }
2424
2425        // delete notifications
2426        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2427            "DELETE FROM \"xnotifications\" WHERE \"recipient\" = ?"
2428        } else {
2429            "DELETE FROM \"xnotifications\" WHERE \"recipient\" = $1"
2430        };
2431
2432        let c = &self.base.db.client;
2433        match sqlquery(&query).bind::<&str>(&recipient).execute(c).await {
2434            Ok(_) => {
2435                // clear notifications count
2436                self.base
2437                    .cache
2438                    .remove(format!("rbeam.auth.notification_count:{}", recipient))
2439                    .await;
2440
2441                // clear cache for all deleted notifications
2442                for notification in notifications {
2443                    // remove from cache
2444                    self.base
2445                        .cache
2446                        .remove(format!("rbeam.auth.notification:{}", notification.id))
2447                        .await;
2448                }
2449
2450                // return
2451                return Ok(());
2452            }
2453            Err(_) => return Err(DatabaseError::Other),
2454        };
2455    }
2456
2457    // warnings
2458
2459    // GET
2460    /// Get an existing warning
2461    ///
2462    /// # Arguments
2463    /// * `id`
2464    pub async fn get_warning(&self, id: &str) -> Result<Warning> {
2465        // check in cache
2466        match self
2467            .base
2468            .cache
2469            .get(format!("rbeam.auth.warning:{}", id))
2470            .await
2471        {
2472            Some(c) => return Ok(serde_json::from_str::<Warning>(c.as_str()).unwrap()),
2473            None => (),
2474        };
2475
2476        // pull from database
2477        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2478            "SELECT * FROM \"xwarnings\" WHERE \"id\" = ?"
2479        } else {
2480            "SELECT * FROM \"xwarnings\" WHERE \"id\" = $1"
2481        };
2482
2483        let c = &self.base.db.client;
2484        let res = match sqlquery(&query).bind::<&str>(&id).fetch_one(c).await {
2485            Ok(p) => self.base.textify_row(p).0,
2486            Err(_) => return Err(DatabaseError::NotFound),
2487        };
2488
2489        // return
2490        let warning = Warning {
2491            id: from_row!(res->id()),
2492            content: from_row!(res->content()),
2493            timestamp: from_row!(res->timestamp(u128); 0),
2494            recipient: from_row!(res->recipient()),
2495            moderator: match self.get_profile_by_id(res.get("moderator").unwrap()).await {
2496                Ok(ua) => ua,
2497                Err(e) => return Err(e),
2498            },
2499        };
2500
2501        // store in cache
2502        self.base
2503            .cache
2504            .set(
2505                format!("rbeam.auth.warning:{}", id),
2506                serde_json::to_string::<Warning>(&warning).unwrap(),
2507            )
2508            .await;
2509
2510        // return
2511        Ok(warning)
2512    }
2513
2514    /// Get all warnings by their recipient
2515    ///
2516    /// # Arguments
2517    /// * `recipient`
2518    /// * `user` - the user doing this
2519    pub async fn get_warnings_by_recipient(
2520        &self,
2521        recipient: &str,
2522        user: Box<Profile>,
2523    ) -> Result<Vec<Warning>> {
2524        // make sure user is a manager
2525        let group = match self.get_group_by_id(user.group).await {
2526            Ok(g) => g,
2527            Err(_) => return Err(DatabaseError::Other),
2528        };
2529
2530        if !group.permissions.check(FinePermission::MANAGE_WARNINGS) {
2531            return Err(DatabaseError::NotAllowed);
2532        }
2533
2534        // pull from database
2535        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2536            "SELECT * FROM \"xwarnings\" WHERE \"recipient\" = ? ORDER BY \"timestamp\" DESC"
2537        } else {
2538            "SELECT * FROM \"xwarnings\" WHERE \"recipient\" = $1 ORDER BY \"timestamp\" DESC"
2539        };
2540
2541        let c = &self.base.db.client;
2542        let res = match sqlquery(&query)
2543            .bind::<&str>(&recipient.to_lowercase())
2544            .fetch_all(c)
2545            .await
2546        {
2547            Ok(p) => {
2548                let mut out: Vec<Warning> = Vec::new();
2549
2550                for row in p {
2551                    let res = self.base.textify_row(row).0;
2552                    out.push(Warning {
2553                        id: from_row!(res->id()),
2554                        content: from_row!(res->content()),
2555                        timestamp: from_row!(res->timestamp(u128); 0),
2556                        recipient: from_row!(res->recipient()),
2557                        moderator: match self.get_profile_by_id(res.get("moderator").unwrap()).await
2558                        {
2559                            Ok(ua) => ua,
2560                            Err(_) => continue,
2561                        },
2562                    });
2563                }
2564
2565                out
2566            }
2567            Err(_) => return Err(DatabaseError::NotFound),
2568        };
2569
2570        // return
2571        Ok(res)
2572    }
2573
2574    // SET
2575    /// Create a new warning
2576    ///
2577    /// # Arguments
2578    /// * `props` - [`WarningCreate`]
2579    /// * `user` - the user creating this warning
2580    pub async fn create_warning(&self, props: WarningCreate, user: Box<Profile>) -> Result<()> {
2581        // make sure user is a manager
2582        let group = match self.get_group_by_id(user.group).await {
2583            Ok(g) => g,
2584            Err(_) => return Err(DatabaseError::Other),
2585        };
2586
2587        if !group.permissions.check(FinePermission::MANAGE_WARNINGS) {
2588            return Err(DatabaseError::NotAllowed);
2589        }
2590
2591        // ...
2592        let warning = Warning {
2593            id: utility::random_id(),
2594            content: props.content,
2595            timestamp: utility::unix_epoch_timestamp(),
2596            recipient: props.recipient,
2597            moderator: user,
2598        };
2599
2600        // create notification
2601        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2602            "INSERT INTO \"xwarnings\" VALUES (?, ?, ?, ?, ?)"
2603        } else {
2604            "INSERT INTO \"xwarnings\" VALUES ($1, $2, $3, $4, $5)"
2605        };
2606
2607        let c = &self.base.db.client;
2608        match sqlquery(&query)
2609            .bind::<&str>(&warning.id)
2610            .bind::<&str>(&warning.content)
2611            .bind::<&str>(&warning.timestamp.to_string())
2612            .bind::<&str>(&warning.recipient)
2613            .bind::<&str>(&warning.moderator.id)
2614            .execute(c)
2615            .await
2616        {
2617            Ok(_) => {
2618                // create notification for recipient
2619                if let Err(e) = self
2620                    .create_notification(
2621                        NotificationCreate {
2622                            title: "You have received an account warning!".to_string(),
2623                            content: warning.content,
2624                            address: String::new(),
2625                            recipient: warning.recipient,
2626                        },
2627                        None,
2628                    )
2629                    .await
2630                {
2631                    return Err(e);
2632                };
2633
2634                // ...
2635                return Ok(());
2636            }
2637            Err(_) => return Err(DatabaseError::Other),
2638        };
2639    }
2640
2641    /// Delete an existing warning
2642    ///
2643    /// Warnings can only be deleted by their moderator or admins.
2644    ///
2645    /// # Arguments
2646    /// * `id` - the ID of the warning
2647    /// * `user` - the user doing this
2648    pub async fn delete_warning(&self, id: &str, user: Box<Profile>) -> Result<()> {
2649        // make sure warning exists
2650        let warning = match self.get_warning(id).await {
2651            Ok(n) => n,
2652            Err(e) => return Err(e),
2653        };
2654
2655        // check id
2656        if user.id != warning.moderator.id {
2657            // check permission
2658            let group = match self.get_group_by_id(user.group).await {
2659                Ok(g) => g,
2660                Err(_) => return Err(DatabaseError::Other),
2661            };
2662
2663            if !group.permissions.check(FinePermission::MANAGE_WARNINGS) {
2664                return Err(DatabaseError::NotAllowed);
2665            }
2666        }
2667
2668        // delete warning
2669        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2670            "DELETE FROM \"xwarnings\" WHERE \"id\" = ?"
2671        } else {
2672            "DELETE FROM \"xwarnings\" WHERE \"id\" = $1"
2673        };
2674
2675        let c = &self.base.db.client;
2676        match sqlquery(&query).bind::<&str>(&id).execute(c).await {
2677            Ok(_) => {
2678                // remove from cache
2679                self.base
2680                    .cache
2681                    .remove(format!("rbeam.auth.warning:{}", id))
2682                    .await;
2683
2684                // return
2685                return Ok(());
2686            }
2687            Err(_) => return Err(DatabaseError::Other),
2688        };
2689    }
2690
2691    // ip bans
2692
2693    // GET
2694    /// Get an existing [`IpBan`]
2695    ///
2696    /// # Arguments
2697    /// * `id`
2698    pub async fn get_ipban(&self, id: &str) -> Result<IpBan> {
2699        // check in cache
2700        match self
2701            .base
2702            .cache
2703            .get(format!("rbeam.auth.ipban:{}", id))
2704            .await
2705        {
2706            Some(c) => return Ok(serde_json::from_str::<IpBan>(c.as_str()).unwrap()),
2707            None => (),
2708        };
2709
2710        // pull from database
2711        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2712            "SELECT * FROM \"xbans\" WHERE \"id\" = ?"
2713        } else {
2714            "SELECT * FROM \"xbans\" WHERE \"id\" = $1"
2715        };
2716
2717        let c = &self.base.db.client;
2718        let res = match sqlquery(&query).bind::<&str>(&id).fetch_one(c).await {
2719            Ok(p) => self.base.textify_row(p).0,
2720            Err(_) => return Err(DatabaseError::NotFound),
2721        };
2722
2723        // return
2724        let ban = IpBan {
2725            id: from_row!(res->id()),
2726            ip: from_row!(res->ip()),
2727            reason: from_row!(res->reason()),
2728            moderator: match self.get_profile_by_id(res.get("moderator").unwrap()).await {
2729                Ok(ua) => ua,
2730                Err(e) => return Err(e),
2731            },
2732            timestamp: from_row!(res->timestamp(u128); 0),
2733        };
2734
2735        // store in cache
2736        self.base
2737            .cache
2738            .set(
2739                format!("rbeam.auth.ipban:{}", id),
2740                serde_json::to_string::<IpBan>(&ban).unwrap(),
2741            )
2742            .await;
2743
2744        // return
2745        Ok(ban)
2746    }
2747
2748    /// Get an existing [`IpBan`] by its IP
2749    ///
2750    /// # Arguments
2751    /// * `ip`
2752    pub async fn get_ipban_by_ip(&self, ip: &str) -> Result<IpBan> {
2753        // pull from database
2754        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2755            "SELECT * FROM \"xbans\" WHERE \"ip\" = ?"
2756        } else {
2757            "SELECT * FROM \"xbans\" WHERE \"ip\" = $1"
2758        };
2759
2760        let c = &self.base.db.client;
2761        let res = match sqlquery(&query).bind::<&str>(&ip).fetch_one(c).await {
2762            Ok(p) => self.base.textify_row(p).0,
2763            Err(_) => return Err(DatabaseError::NotFound),
2764        };
2765
2766        // return
2767        let ban = IpBan {
2768            id: from_row!(res->id()),
2769            ip: from_row!(res->ip()),
2770            reason: from_row!(res->reason()),
2771            moderator: match self.get_profile_by_id(res.get("moderator").unwrap()).await {
2772                Ok(ua) => ua,
2773                Err(e) => return Err(e),
2774            },
2775            timestamp: from_row!(res->timestamp(u128); 0),
2776        };
2777
2778        // return
2779        Ok(ban)
2780    }
2781
2782    /// Get all [`IpBan`]s
2783    ///
2784    /// # Arguments
2785    /// * `user` - the user doing this
2786    pub async fn get_ipbans(&self, user: Box<Profile>) -> Result<Vec<IpBan>> {
2787        // make sure user is a manager
2788        let group = match self.get_group_by_id(user.group).await {
2789            Ok(g) => g,
2790            Err(_) => return Err(DatabaseError::Other),
2791        };
2792
2793        if !group.permissions.check(FinePermission::BAN_IP) {
2794            return Err(DatabaseError::NotAllowed);
2795        }
2796
2797        // pull from database
2798        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2799            "SELECT * FROM \"xbans\" ORDER BY \"timestamp\" DESC"
2800        } else {
2801            "SELECT * FROM \"xbans\" ORDER BY \"timestamp\" DESC"
2802        };
2803
2804        let c = &self.base.db.client;
2805        let res = match sqlquery(&query).fetch_all(c).await {
2806            Ok(p) => {
2807                let mut out: Vec<IpBan> = Vec::new();
2808
2809                for row in p {
2810                    let res = self.base.textify_row(row).0;
2811                    out.push(IpBan {
2812                        id: from_row!(res->id()),
2813                        ip: from_row!(res->ip()),
2814                        reason: from_row!(res->reason()),
2815                        moderator: match self.get_profile_by_id(res.get("moderator").unwrap()).await
2816                        {
2817                            Ok(ua) => ua,
2818                            Err(_) => continue,
2819                        },
2820                        timestamp: from_row!(res->timestamp(u128); 0),
2821                    });
2822                }
2823
2824                out
2825            }
2826            Err(_) => return Err(DatabaseError::NotFound),
2827        };
2828
2829        // return
2830        Ok(res)
2831    }
2832
2833    // SET
2834    /// Create a new [`IpBan`]
2835    ///
2836    /// # Arguments
2837    /// * `props` - [`IpBanCreate`]
2838    /// * `user` - the user creating this ban
2839    pub async fn create_ipban(&self, props: IpBanCreate, user: Box<Profile>) -> Result<()> {
2840        // make sure user is a helper
2841        let group = match self.get_group_by_id(user.group).await {
2842            Ok(g) => g,
2843            Err(_) => return Err(DatabaseError::Other),
2844        };
2845
2846        if !group.permissions.check(FinePermission::BAN_IP) {
2847            return Err(DatabaseError::NotAllowed);
2848        } else {
2849            let actor_id = user.id.clone();
2850            if let Err(e) = self
2851                .create_notification(
2852                    NotificationCreate {
2853                        title: format!("[{actor_id}](/+u/{actor_id})"),
2854                        content: format!("Banned an IP: {}", props.ip),
2855                        address: format!("/+u/{actor_id}"),
2856                        recipient: "*(audit)".to_string(), // all staff, audit
2857                    },
2858                    None,
2859                )
2860                .await
2861            {
2862                return Err(e);
2863            }
2864        }
2865
2866        // make sure this ip isn't already banned
2867        if self.get_ipban_by_ip(&props.ip).await.is_ok() {
2868            return Err(DatabaseError::MustBeUnique);
2869        }
2870
2871        // ...
2872        let ban = IpBan {
2873            id: utility::random_id(),
2874            ip: props.ip,
2875            reason: props.reason,
2876            moderator: user,
2877            timestamp: utility::unix_epoch_timestamp(),
2878        };
2879
2880        // create notification
2881        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2882            "INSERT INTO \"xbans\" VALUES (?, ?, ?, ?, ?)"
2883        } else {
2884            "INSERT INTO \"xbans\" VALUES ($1, $2, $3, $4, $5)"
2885        };
2886
2887        let c = &self.base.db.client;
2888        match sqlquery(&query)
2889            .bind::<&str>(&ban.id)
2890            .bind::<&str>(&ban.ip)
2891            .bind::<&str>(&ban.reason)
2892            .bind::<&str>(&ban.moderator.id)
2893            .bind::<&str>(&ban.timestamp.to_string())
2894            .execute(c)
2895            .await
2896        {
2897            Ok(_) => return Ok(()),
2898            Err(_) => return Err(DatabaseError::Other),
2899        };
2900    }
2901
2902    /// Delete an existing IpBan
2903    ///
2904    /// # Arguments
2905    /// * `id` - the ID of the ban
2906    /// * `user` - the user doing this
2907    pub async fn delete_ipban(&self, id: &str, user: Box<Profile>) -> Result<()> {
2908        // make sure ban exists
2909        let ipban = match self.get_ipban(id).await {
2910            Ok(n) => n,
2911            Err(e) => return Err(e),
2912        };
2913
2914        // check id
2915        if user.id != ipban.moderator.id {
2916            // check permission
2917            let group = match self.get_group_by_id(user.group).await {
2918                Ok(g) => g,
2919                Err(_) => return Err(DatabaseError::Other),
2920            };
2921
2922            if !group.permissions.check(FinePermission::UNBAN_IP) {
2923                return Err(DatabaseError::NotAllowed);
2924            } else {
2925                let actor_id = user.id.clone();
2926                if let Err(e) = self
2927                    .create_notification(
2928                        NotificationCreate {
2929                            title: format!("[{actor_id}](/+u/{actor_id})"),
2930                            content: format!("Unbanned an IP: {}", ipban.ip),
2931                            address: format!("/+u/{actor_id}"),
2932                            recipient: "*(audit)".to_string(), // all staff, audit
2933                        },
2934                        None,
2935                    )
2936                    .await
2937                {
2938                    return Err(e);
2939                }
2940            }
2941        }
2942
2943        // delete ban
2944        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2945            "DELETE FROM \"xbans\" WHERE \"id\" = ?"
2946        } else {
2947            "DELETE FROM \"xbans\" WHERE \"id\" = $1"
2948        };
2949
2950        let c = &self.base.db.client;
2951        match sqlquery(&query).bind::<&str>(&id).execute(c).await {
2952            Ok(_) => {
2953                // remove from cache
2954                self.base
2955                    .cache
2956                    .remove(format!("rbeam.auth.ipban:{}", id))
2957                    .await;
2958
2959                // return
2960                return Ok(());
2961            }
2962            Err(_) => return Err(DatabaseError::Other),
2963        };
2964    }
2965
2966    // relationships
2967
2968    /// Get the membership status of the given user and the other user
2969    ///
2970    /// # Arguments
2971    /// * `user` - the ID of the first user
2972    /// * `other` - the ID of the second user
2973    pub async fn get_user_relationship(
2974        &self,
2975        user: &str,
2976        other: &str,
2977    ) -> (RelationshipStatus, String, String) {
2978        // pull from database
2979        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
2980            "SELECT * FROM \"xrelationships\" WHERE (\"one\" = ? AND \"two\" = ?) OR (\"one\" = ? AND \"two\" = ?)"
2981        } else {
2982            "SELECT * FROM \"xrelationships\" WHERE (\"one\" = $1 AND \"two\" = $2) OR (\"one\" = $3 AND \"two\" = $4)"
2983        };
2984
2985        let c = &self.base.db.client;
2986        let res = match sqlquery(&query)
2987            .bind::<&str>(&user)
2988            .bind::<&str>(&other)
2989            .bind::<&str>(&other)
2990            .bind::<&str>(&user)
2991            .fetch_one(c)
2992            .await
2993        {
2994            Ok(p) => self.base.textify_row(p).0,
2995            Err(_) => {
2996                return (
2997                    RelationshipStatus::default(),
2998                    user.to_string(),
2999                    other.to_string(),
3000                )
3001            }
3002        };
3003
3004        // return
3005        (
3006            serde_json::from_str(&res.get("status").unwrap()).unwrap(),
3007            res.get("one").unwrap().to_string(),
3008            res.get("two").unwrap().to_string(),
3009        )
3010    }
3011
3012    /// Set the relationship of user `one` and `two`
3013    ///
3014    /// # Arguments
3015    /// * `one` - the ID of the first user
3016    /// * `two` - the ID of the second user
3017    /// * `status` - the new relationship status, setting to "Unknown" will remove the relationship
3018    /// * `disable_notifications`
3019    pub async fn set_user_relationship_status(
3020        &self,
3021        one: &str,
3022        two: &str,
3023        status: RelationshipStatus,
3024        disable_notifications: bool,
3025    ) -> Result<()> {
3026        // get current membership status
3027        let mut relationship = self.get_user_relationship(one, two).await;
3028
3029        if relationship.0 == status {
3030            return Ok(());
3031        }
3032
3033        let mut uone = match self.get_profile(&relationship.1).await {
3034            Ok(ua) => ua,
3035            Err(e) => return Err(e),
3036        };
3037
3038        let mut utwo = match self.get_profile(&relationship.2).await {
3039            Ok(ua) => ua,
3040            Err(e) => return Err(e),
3041        };
3042
3043        // ...
3044        match status {
3045            RelationshipStatus::Blocked => {
3046                // if the relationship exists but we aren't user one, delete it
3047                if relationship.0 != RelationshipStatus::Unknown && uone.id != one {
3048                    // delete
3049                    let query =
3050                        if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3051                            "DELETE FROM \"xrelationships\" WHERE \"one\" = ? AND \"two\" = ?"
3052                        } else {
3053                            "DELETE FROM \"xrelationships\" WHERE \"one\" = ? AND \"two\" = ?"
3054                        };
3055
3056                    let c = &self.base.db.client;
3057                    if let Err(_) = sqlquery(&query)
3058                        .bind::<&str>(&uone.id)
3059                        .bind::<&str>(&utwo.id)
3060                        .execute(c)
3061                        .await
3062                    {
3063                        return Err(DatabaseError::Other);
3064                    };
3065
3066                    relationship.0 = RelationshipStatus::Unknown; // act like it never happened
3067                    uone.id = one.to_string();
3068                    utwo.id = two.to_string();
3069                }
3070
3071                // ...
3072                if relationship.0 != RelationshipStatus::Unknown {
3073                    if relationship.0 == RelationshipStatus::Friends {
3074                        // decr friendship counts since we were previously friends but are not now
3075                        self.base
3076                            .cache
3077                            .decr(format!("rbeam.app.friends_count:{}", uone.id))
3078                            .await;
3079
3080                        self.base
3081                            .cache
3082                            .decr(format!("rbeam.app.friends_count:{}", utwo.id))
3083                            .await;
3084                    }
3085
3086                    // update
3087                    let query = if (self.base.db.r#type == "sqlite")
3088                        | (self.base.db.r#type == "mysql")
3089                    {
3090                        "UPDATE \"xrelationships\" SET \"status\" = ? WHERE \"one\" = ? AND \"two\" = ?"
3091                    } else {
3092                        "UPDATE \"xrelationships\" SET (\"status\") = (?) WHERE \"one\" = ? AND \"two\" = ?"
3093                    };
3094
3095                    let c = &self.base.db.client;
3096                    if let Err(_) = sqlquery(&query)
3097                        .bind::<&str>(&serde_json::to_string(&status).unwrap())
3098                        .bind::<&str>(&uone.id)
3099                        .bind::<&str>(&utwo.id)
3100                        .execute(c)
3101                        .await
3102                    {
3103                        return Err(DatabaseError::Other);
3104                    };
3105                } else {
3106                    // add
3107                    let query =
3108                        if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3109                            "INSERT INTO \"xrelationships\" VALUES (?, ?, ?, ?)"
3110                        } else {
3111                            "INSERT INTO \"xrelationships\" VALUES ($1, $2, $3, $4)"
3112                        };
3113
3114                    let c = &self.base.db.client;
3115                    if let Err(_) = sqlquery(&query)
3116                        .bind::<&str>(&uone.id)
3117                        .bind::<&str>(&utwo.id)
3118                        .bind::<&str>(&serde_json::to_string(&status).unwrap())
3119                        .bind::<&str>(&rainbeam_shared::unix_epoch_timestamp().to_string())
3120                        .execute(c)
3121                        .await
3122                    {
3123                        return Err(DatabaseError::Other);
3124                    };
3125                }
3126            }
3127            RelationshipStatus::Pending => {
3128                // check utwo permissions
3129                if utwo.metadata.is_true("sparkler:limited_friend_requests") {
3130                    // make sure utwo is following uone
3131                    if let Err(_) = self.get_follow(&utwo.id, &uone.id).await {
3132                        return Err(DatabaseError::NotAllowed);
3133                    }
3134                }
3135
3136                // add
3137                let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql")
3138                {
3139                    "INSERT INTO \"xrelationships\" VALUES (?, ?, ?, ?)"
3140                } else {
3141                    "INSERT INTO \"xrelationships\" VALUES ($1, $2, $3, $4)"
3142                };
3143
3144                let c = &self.base.db.client;
3145                if let Err(_) = sqlquery(&query)
3146                    .bind::<&str>(&uone.id)
3147                    .bind::<&str>(&utwo.id)
3148                    .bind::<&str>(&serde_json::to_string(&status).unwrap())
3149                    .bind::<&str>(&rainbeam_shared::unix_epoch_timestamp().to_string())
3150                    .execute(c)
3151                    .await
3152                {
3153                    return Err(DatabaseError::Other);
3154                };
3155
3156                // create notification
3157                if !disable_notifications {
3158                    if let Err(_) = self
3159                        .create_notification(
3160                            NotificationCreate {
3161                                title: format!(
3162                                    "[@{}](/+u/{}) has sent you a friend request!",
3163                                    uone.username, uone.id
3164                                ),
3165                                content: format!("{} wants to be your friend.", uone.username),
3166                                address: format!("/@{}/relationship/friend_accept", uone.id),
3167                                recipient: utwo.id,
3168                            },
3169                            None,
3170                        )
3171                        .await
3172                    {
3173                        return Err(DatabaseError::Other);
3174                    };
3175                };
3176            }
3177            RelationshipStatus::Friends => {
3178                // update
3179                let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql")
3180                {
3181                    "UPDATE \"xrelationships\" SET \"status\" = ? WHERE \"one\" = ? AND \"two\" = ?"
3182                } else {
3183                    "UPDATE \"xrelationships\" SET (\"status\") = (?) WHERE \"one\" = ? AND \"two\" = ?"
3184                };
3185
3186                let c = &self.base.db.client;
3187                if let Err(_) = sqlquery(&query)
3188                    .bind::<&str>(&serde_json::to_string(&status).unwrap())
3189                    .bind::<&str>(&uone.id)
3190                    .bind::<&str>(&utwo.id)
3191                    .execute(c)
3192                    .await
3193                {
3194                    return Err(DatabaseError::Other);
3195                };
3196
3197                self.base
3198                    .cache
3199                    .incr(format!("rbeam.app.friends_count:{}", uone.id))
3200                    .await;
3201
3202                self.base
3203                    .cache
3204                    .incr(format!("rbeam.app.friends_count:{}", utwo.id))
3205                    .await;
3206
3207                // create notification
3208                if !disable_notifications {
3209                    if let Err(_) = self
3210                        .create_notification(
3211                            NotificationCreate {
3212                                title: "Your friend request has been accepted!".to_string(),
3213                                content: format!(
3214                                    "[@{}](/@{}) has accepted your friend request.",
3215                                    utwo.username, utwo.username
3216                                ),
3217                                address: String::new(),
3218                                recipient: uone.id,
3219                            },
3220                            None,
3221                        )
3222                        .await
3223                    {
3224                        return Err(DatabaseError::Other);
3225                    };
3226                };
3227            }
3228            RelationshipStatus::Unknown => {
3229                // delete
3230                let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql")
3231                {
3232                    "DELETE FROM \"xrelationships\" WHERE \"one\" = ? AND \"two\" = ?"
3233                } else {
3234                    "DELETE FROM \"xrelationships\" WHERE \"one\" = ? AND \"two\" = ?"
3235                };
3236
3237                let c = &self.base.db.client;
3238                if let Err(_) = sqlquery(&query)
3239                    .bind::<&str>(&uone.id)
3240                    .bind::<&str>(&utwo.id)
3241                    .execute(c)
3242                    .await
3243                {
3244                    return Err(DatabaseError::Other);
3245                };
3246
3247                if relationship.0 == RelationshipStatus::Friends {
3248                    // decr friendship counts since we were previously friends but are not now
3249                    self.base
3250                        .cache
3251                        .decr(format!("rbeam.app.friends_count:{}", uone.id))
3252                        .await;
3253
3254                    self.base
3255                        .cache
3256                        .decr(format!("rbeam.app.friends_count:{}", utwo.id))
3257                        .await;
3258                }
3259            }
3260        }
3261
3262        // return
3263        Ok(())
3264    }
3265
3266    /// Get all relationships owned by `user` (ownership is the relationship's `one` field)
3267    ///
3268    /// # Arguments
3269    /// * `user`
3270    pub async fn get_user_relationships(
3271        &self,
3272        user: &str,
3273    ) -> Result<Vec<(Box<Profile>, RelationshipStatus)>> {
3274        // pull from database
3275        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3276            "SELECT * FROM \"xrelationships\" WHERE \"one\" = ?"
3277        } else {
3278            "SELECT * FROM \"xrelationships\" WHERE \"one\" = $1"
3279        };
3280
3281        let c = &self.base.db.client;
3282        match sqlquery(&query).bind::<&str>(&user).fetch_all(c).await {
3283            Ok(p) => {
3284                let mut out = Vec::new();
3285
3286                for row in p {
3287                    let res = self.base.textify_row(row).0;
3288
3289                    // get profile
3290                    let profile = match self.get_profile(res.get("two").unwrap()).await {
3291                        Ok(c) => c,
3292                        Err(e) => return Err(e),
3293                    };
3294
3295                    // add to out
3296                    out.push((
3297                        profile,
3298                        serde_json::from_str(&res.get("status").unwrap()).unwrap(),
3299                    ));
3300                }
3301
3302                Ok(out)
3303            }
3304            Err(_) => return Err(DatabaseError::Other),
3305        }
3306    }
3307
3308    /// Get all relationships owned by `user` (ownership is the relationship's `one` field)
3309    ///
3310    /// # Arguments
3311    /// * `user`
3312    /// * `status`
3313    pub async fn get_user_relationships_of_status(
3314        &self,
3315        user: &str,
3316        status: RelationshipStatus,
3317    ) -> Result<Vec<(Box<Profile>, RelationshipStatus)>> {
3318        // pull from database
3319        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3320            "SELECT * FROM \"xrelationships\" WHERE \"one\" = ? AND \"status\" = ?"
3321        } else {
3322            "SELECT * FROM \"xrelationships\" WHERE \"one\" = $1 AND \"status\" = $2"
3323        };
3324
3325        let c = &self.base.db.client;
3326        match sqlquery(&query)
3327            .bind::<&str>(&user)
3328            .bind::<&str>(&serde_json::to_string(&status).unwrap())
3329            .fetch_all(c)
3330            .await
3331        {
3332            Ok(p) => {
3333                let mut out = Vec::new();
3334
3335                for row in p {
3336                    let res = self.base.textify_row(row).0;
3337
3338                    // get profile
3339                    let profile = match self.get_profile(res.get("two").unwrap()).await {
3340                        Ok(c) => c,
3341                        Err(_) => continue,
3342                    };
3343
3344                    // add to out
3345                    out.push((
3346                        profile,
3347                        serde_json::from_str(&res.get("status").unwrap()).unwrap(),
3348                    ));
3349                }
3350
3351                Ok(out)
3352            }
3353            Err(_) => return Err(DatabaseError::Other),
3354        }
3355    }
3356
3357    /// Get all relationships where `user` is either `one` or `two` and the status is `status`
3358    ///
3359    /// # Arguments
3360    /// * `user`
3361    /// * `status`
3362    pub async fn get_user_participating_relationships_of_status(
3363        &self,
3364        user: &str,
3365        status: RelationshipStatus,
3366    ) -> Result<Vec<(Box<Profile>, Box<Profile>)>> {
3367        // pull from database
3368        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3369            "SELECT * FROM \"xrelationships\" WHERE (\"one\" = ? OR \"two\" = ?) AND \"status\" = ?"
3370        } else {
3371            "SELECT * FROM \"xrelationships\" WHERE (\"one\" = $1 OR \"two\" = $2) AND \"status\" = $3"
3372        };
3373
3374        let c = &self.base.db.client;
3375        match sqlquery(&query)
3376            .bind::<&str>(&user)
3377            .bind::<&str>(&user)
3378            .bind::<&str>(&serde_json::to_string(&status).unwrap())
3379            .fetch_all(c)
3380            .await
3381        {
3382            Ok(p) => {
3383                let mut out = Vec::new();
3384
3385                for row in p {
3386                    let res = self.base.textify_row(row).0;
3387
3388                    // get profiles
3389                    let profile = match self.get_profile(res.get("one").unwrap()).await {
3390                        Ok(c) => c,
3391                        Err(_) => continue,
3392                    };
3393
3394                    let profile_2 = match self.get_profile(res.get("two").unwrap()).await {
3395                        Ok(c) => c,
3396                        Err(_) => continue,
3397                    };
3398
3399                    // add to out
3400                    out.push((profile, profile_2));
3401                }
3402
3403                Ok(out)
3404            }
3405            Err(_) => return Err(DatabaseError::Other),
3406        }
3407    }
3408
3409    /// Get all relationships where `user` is either `one` or `two` and the status is `status`, 12 at a time
3410    ///
3411    /// # Arguments
3412    /// * `user`
3413    /// * `status`
3414    /// * `page`
3415    pub async fn get_user_participating_relationships_of_status_paginated(
3416        &self,
3417        user: &str,
3418        status: RelationshipStatus,
3419        page: i32,
3420    ) -> Result<Vec<(Box<Profile>, Box<Profile>)>> {
3421        // pull from database
3422        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3423            format!("SELECT * FROM \"xrelationships\" WHERE (\"one\" = ? OR \"two\" = ?) AND \"status\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
3424        } else {
3425            format!("SELECT * FROM \"xrelationships\" WHERE (\"one\" = $1 OR \"two\" = $2) AND \"status\" = $3 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
3426        };
3427
3428        let c = &self.base.db.client;
3429        match sqlquery(&query)
3430            .bind::<&str>(&user)
3431            .bind::<&str>(&user)
3432            .bind::<&str>(&serde_json::to_string(&status).unwrap())
3433            .fetch_all(c)
3434            .await
3435        {
3436            Ok(p) => {
3437                let mut out = Vec::new();
3438
3439                for row in p {
3440                    let res = self.base.textify_row(row).0;
3441
3442                    // get profiles
3443                    let profile = match self.get_profile(res.get("one").unwrap()).await {
3444                        Ok(c) => c,
3445                        Err(e) => return Err(e),
3446                    };
3447
3448                    let profile_2 = match self.get_profile(res.get("two").unwrap()).await {
3449                        Ok(c) => c,
3450                        Err(e) => return Err(e),
3451                    };
3452
3453                    // add to out
3454                    out.push((profile, profile_2));
3455                }
3456
3457                Ok(out)
3458            }
3459            Err(_) => return Err(DatabaseError::Other),
3460        }
3461    }
3462
3463    /// Get the number of friends a user has
3464    ///
3465    /// # Arguments
3466    /// * `id`
3467    pub async fn get_friendship_count_by_user(&self, id: &str) -> usize {
3468        // attempt to fetch from cache
3469        if let Some(count) = self
3470            .base
3471            .cache
3472            .get(format!("rbeam.app.friends_count:{}", id))
3473            .await
3474        {
3475            return count.parse::<usize>().unwrap_or(0);
3476        };
3477
3478        // fetch from database
3479        let count = self
3480            .get_user_participating_relationships_of_status(id, RelationshipStatus::Friends)
3481            .await
3482            .unwrap_or(Vec::new())
3483            .len();
3484
3485        self.base
3486            .cache
3487            .set(format!("rbeam.app.friends_count:{}", id), count.to_string())
3488            .await;
3489
3490        count
3491    }
3492
3493    // ip blocks
3494
3495    // GET
3496    /// Get an existing [`IpBlock`]
3497    ///
3498    /// # Arguments
3499    /// * `id`
3500    pub async fn get_ipblock(&self, id: &str) -> Result<IpBlock> {
3501        // check in cache
3502        match self
3503            .base
3504            .cache
3505            .get(format!("rbeam.auth.ipblock:{}", id))
3506            .await
3507        {
3508            Some(c) => return Ok(serde_json::from_str::<IpBlock>(c.as_str()).unwrap()),
3509            None => (),
3510        };
3511
3512        // pull from database
3513        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3514            "SELECT * FROM \"xipblocks\" WHERE \"id\" = ?"
3515        } else {
3516            "SELECT * FROM \"xipblocks\" WHERE \"id\" = $1"
3517        };
3518
3519        let c = &self.base.db.client;
3520        let res = match sqlquery(&query).bind::<&str>(&id).fetch_one(c).await {
3521            Ok(p) => self.base.textify_row(p).0,
3522            Err(_) => return Err(DatabaseError::NotFound),
3523        };
3524
3525        // return
3526        let block = IpBlock {
3527            id: from_row!(res->id()),
3528            ip: from_row!(res->ip()),
3529            user: from_row!(res->user()),
3530            context: from_row!(res->context()),
3531            timestamp: from_row!(res->timestamp(u128); 0),
3532        };
3533
3534        // store in cache
3535        self.base
3536            .cache
3537            .set(
3538                format!("rbeam.auth.ipblock:{}", id),
3539                serde_json::to_string::<IpBlock>(&block).unwrap(),
3540            )
3541            .await;
3542
3543        // return
3544        Ok(block)
3545    }
3546
3547    /// Get an existing [`IpBlock`] by its IP and its `user`
3548    ///
3549    /// # Arguments
3550    /// * `ip`
3551    /// * `user`
3552    pub async fn get_ipblock_by_ip(&self, ip: &str, user: &str) -> Result<IpBlock> {
3553        // pull from database
3554        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3555            "SELECT * FROM \"xipblocks\" WHERE \"ip\" = ? AND \"user\" = ?"
3556        } else {
3557            "SELECT * FROM \"xipblocks\" WHERE \"ip\" = $1 AND \"user\" = $2"
3558        };
3559
3560        let c = &self.base.db.client;
3561        let res = match sqlquery(&query)
3562            .bind::<&str>(&ip)
3563            .bind::<&str>(&user)
3564            .fetch_one(c)
3565            .await
3566        {
3567            Ok(p) => self.base.textify_row(p).0,
3568            Err(_) => return Err(DatabaseError::NotFound),
3569        };
3570
3571        // return
3572        let block = IpBlock {
3573            id: from_row!(res->id()),
3574            ip: from_row!(res->ip()),
3575            user: from_row!(res->user()),
3576            context: from_row!(res->context()),
3577            timestamp: from_row!(res->timestamp(u128); 0),
3578        };
3579
3580        // return
3581        Ok(block)
3582    }
3583
3584    /// Get all [`IpBlocks`]s for the given `query_user`
3585    ///
3586    /// # Arguments
3587    /// * `query_user` - the ID of the user the blocks belong to
3588    pub async fn get_ipblocks(&self, query_user: &str) -> Result<Vec<IpBlock>> {
3589        // pull from database
3590        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3591            "SELECT * FROM \"xipblocks\" WHERE \"user\" = ? ORDER BY \"timestamp\" DESC"
3592        } else {
3593            "SELECT * FROM \"xipblocks\" WHERE \"user\" = $1 ORDER BY \"timestamp\" DESC"
3594        };
3595
3596        let c = &self.base.db.client;
3597        let res = match sqlquery(&query)
3598            .bind::<&str>(&query_user)
3599            .fetch_all(c)
3600            .await
3601        {
3602            Ok(p) => {
3603                let mut out: Vec<IpBlock> = Vec::new();
3604
3605                for row in p {
3606                    let res = self.base.textify_row(row).0;
3607                    out.push(IpBlock {
3608                        id: from_row!(res->id()),
3609                        ip: from_row!(res->ip()),
3610                        user: from_row!(res->user()),
3611                        context: from_row!(res->context()),
3612                        timestamp: from_row!(res->timestamp(u128); 0),
3613                    });
3614                }
3615
3616                out
3617            }
3618            Err(_) => return Err(DatabaseError::NotFound),
3619        };
3620
3621        // return
3622        Ok(res)
3623    }
3624
3625    // SET
3626    /// Create a new [`IpBlock`]
3627    ///
3628    /// # Arguments
3629    /// * `props` - [`IpBlockCreate`]
3630    /// * `user` - the user creating this block
3631    pub async fn create_ipblock(&self, props: IpBlockCreate, user: Box<Profile>) -> Result<()> {
3632        // make sure this ip isn't already banned
3633        if self.get_ipblock_by_ip(&props.ip, &user.id).await.is_ok() {
3634            return Err(DatabaseError::MustBeUnique);
3635        }
3636
3637        // ...
3638        let block = IpBlock {
3639            id: utility::random_id(),
3640            ip: props.ip,
3641            user: user.id,
3642            context: props.context,
3643            timestamp: utility::unix_epoch_timestamp(),
3644        };
3645
3646        // create notification
3647        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3648            "INSERT INTO \"xipblocks\" VALUES (?, ?, ?, ?, ?)"
3649        } else {
3650            "INSERT INTO \"xipblocks\" VALUES ($1, $2, $3, $4, $5)"
3651        };
3652
3653        let c = &self.base.db.client;
3654        match sqlquery(&query)
3655            .bind::<&str>(&block.id)
3656            .bind::<&str>(&block.ip)
3657            .bind::<&str>(&block.user)
3658            .bind::<&str>(&block.context)
3659            .bind::<&str>(&block.timestamp.to_string())
3660            .execute(c)
3661            .await
3662        {
3663            Ok(_) => return Ok(()),
3664            Err(_) => return Err(DatabaseError::Other),
3665        };
3666    }
3667
3668    /// Delete an existing IpBlock
3669    ///
3670    /// # Arguments
3671    /// * `id` - the ID of the block
3672    /// * `user` - the user doing this
3673    pub async fn delete_ipblock(&self, id: &str, user: Box<Profile>) -> Result<()> {
3674        // make sure block exists
3675        let block = match self.get_ipblock(id).await {
3676            Ok(n) => n,
3677            Err(e) => return Err(e),
3678        };
3679
3680        // check id
3681        if user.id != block.user {
3682            // check permission
3683            let group = match self.get_group_by_id(user.group).await {
3684                Ok(g) => g,
3685                Err(_) => return Err(DatabaseError::Other),
3686            };
3687
3688            if !group.permissions.check(FinePermission::UNBAN_IP) {
3689                return Err(DatabaseError::NotAllowed);
3690            } else {
3691                let actor_id = user.id.clone();
3692                if let Err(e) = self
3693                    .create_notification(
3694                        NotificationCreate {
3695                            title: format!("[{actor_id}](/+u/{actor_id})"),
3696                            content: format!("Unblocked an IP: {}", block.ip),
3697                            address: format!("/+u/{actor_id}"),
3698                            recipient: "*(audit)".to_string(), // all staff, audit
3699                        },
3700                        None,
3701                    )
3702                    .await
3703                {
3704                    return Err(e);
3705                }
3706            }
3707        }
3708
3709        // delete block
3710        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3711            "DELETE FROM \"xipblocks\" WHERE \"id\" = ?"
3712        } else {
3713            "DELETE FROM \"xipblocks\" WHERE \"id\" = $1"
3714        };
3715
3716        let c = &self.base.db.client;
3717        match sqlquery(&query).bind::<&str>(&id).execute(c).await {
3718            Ok(_) => {
3719                // remove from cache
3720                self.base
3721                    .cache
3722                    .remove(format!("rbeam.auth.ipblock:{}", id))
3723                    .await;
3724
3725                // return
3726                return Ok(());
3727            }
3728            Err(_) => return Err(DatabaseError::Other),
3729        };
3730    }
3731
3732    // labels
3733
3734    /// Get a [`UserLabel`] from a database result
3735    pub async fn gimme_label(&self, res: BTreeMap<String, String>) -> Result<UserLabel> {
3736        Ok(UserLabel {
3737            id: from_row!(res->id(i64); 0),
3738            name: from_row!(res->name()),
3739            timestamp: from_row!(res->timestamp(u128); 0),
3740            creator: from_row!(res->creator()),
3741        })
3742    }
3743
3744    /// Get an existing label
3745    ///
3746    /// # Arguments
3747    /// * `id`
3748    pub async fn get_label(&self, id: i64) -> Result<UserLabel> {
3749        // check in cache
3750        match self
3751            .base
3752            .cache
3753            .get(format!("rbeam.auth.label:{}", id))
3754            .await
3755        {
3756            Some(c) => match serde_json::from_str::<UserLabel>(c.as_str()) {
3757                Ok(c) => return Ok(c),
3758                Err(_) => {
3759                    self.base
3760                        .cache
3761                        .remove(format!("rbeam.auth.label:{}", id))
3762                        .await;
3763                }
3764            },
3765            None => (),
3766        };
3767
3768        // pull from database
3769        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3770            "SELECT * FROM \"xlabels\" WHERE \"id\" = ?"
3771        } else {
3772            "SELECT * FROM \"xlabels\" WHERE \"id\" = $1"
3773        };
3774
3775        let c = &self.base.db.client;
3776        let res = match sqlquery(&query).bind::<i64>(id).fetch_one(c).await {
3777            Ok(p) => self.base.textify_row(p).0,
3778            Err(_) => return Err(DatabaseError::NotFound),
3779        };
3780
3781        // return
3782        let label = match self.gimme_label(res).await {
3783            Ok(l) => l,
3784            Err(e) => return Err(e),
3785        };
3786
3787        // store in cache
3788        self.base
3789            .cache
3790            .set(
3791                format!("rbeam.auth.label:{}", id),
3792                serde_json::to_string::<UserLabel>(&label).unwrap(),
3793            )
3794            .await;
3795
3796        // return
3797        Ok(label)
3798    }
3799
3800    /// Create a new user label
3801    ///
3802    /// # Arguments
3803    /// * `name` - the name of the label
3804    /// * `id` - the ID of the label
3805    /// * `author` - the ID of the user creating the label
3806    pub async fn create_label(&self, name: &str, id: i64, author: &str) -> Result<UserLabel> {
3807        // check author permissions
3808        let author = match self.get_profile(author).await {
3809            Ok(ua) => ua,
3810            Err(e) => return Err(e),
3811        };
3812
3813        let group = match self.get_group_by_id(author.group).await {
3814            Ok(g) => g,
3815            Err(e) => return Err(e),
3816        };
3817
3818        if !group.permissions.check(FinePermission::CREATE_LABEL) {
3819            return Err(DatabaseError::NotAllowed);
3820        }
3821
3822        // check name length
3823        if name.len() < 2 {
3824            return Err(DatabaseError::Other);
3825        }
3826
3827        if name.len() > 32 {
3828            return Err(DatabaseError::Other);
3829        }
3830
3831        // ...
3832        let label = UserLabel {
3833            // id: utility::random_id(),
3834            id,
3835            name: name.to_string(),
3836            timestamp: utility::unix_epoch_timestamp(),
3837            creator: author.id,
3838        };
3839
3840        // create label
3841        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3842            "INSERT INTO \"xlabels\" VALUES (?, ?, ?, ?)"
3843        } else {
3844            "INSERT INTO \"xlabels\" VALUES ($1, $2, $3, $4)"
3845        };
3846
3847        let c = &self.base.db.client;
3848        match sqlquery(&query)
3849            .bind::<i64>(label.id)
3850            .bind::<&str>(&label.name)
3851            .bind::<&str>(&label.timestamp.to_string())
3852            .bind::<&str>(&label.creator)
3853            .execute(c)
3854            .await
3855        {
3856            Ok(_) => Ok(label),
3857            Err(_) => Err(DatabaseError::Other),
3858        }
3859    }
3860
3861    /// Create a new user label
3862    ///
3863    /// # Arguments
3864    /// * `id` - the ID of the label
3865    /// * `author` - the ID of the user creating the label
3866    pub async fn delete_label(&self, id: i64, author: Box<Profile>) -> Result<()> {
3867        // check author permissions
3868        let group = match self.get_group_by_id(author.group).await {
3869            Ok(g) => g,
3870            Err(e) => return Err(e),
3871        };
3872
3873        if !group.permissions.check(FinePermission::MANAGE_LABELS) {
3874            return Err(DatabaseError::NotAllowed);
3875        }
3876
3877        // delete label
3878        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3879            "DELETE FROM \"xlabels\" WHERE \"id\" = ?"
3880        } else {
3881            "DELETE FROM \"xlabels\" WHERE \"id\" = $1"
3882        };
3883
3884        let c = &self.base.db.client;
3885        let res = match sqlquery(&query).bind::<i64>(id).execute(c).await {
3886            Ok(_) => Ok(()),
3887            Err(_) => Err(DatabaseError::Other),
3888        };
3889
3890        self.base
3891            .cache
3892            .remove(format!("rbeam.auth.label:{}", id))
3893            .await;
3894
3895        res
3896    }
3897
3898    // ugc transactions
3899
3900    // Get item given the `row` data
3901    pub async fn gimme_transaction(
3902        &self,
3903        row: BTreeMap<String, String>,
3904    ) -> Result<(Transaction, Option<Item>)> {
3905        let item = row.get("item").unwrap();
3906
3907        Ok((
3908            Transaction {
3909                id: from_row!(row->id()),
3910                amount: row.get("amount").unwrap().parse::<i32>().unwrap_or(0),
3911                item: item.clone(),
3912                timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
3913                customer: from_row!(row->customer()),
3914                merchant: from_row!(row->merchant()),
3915            },
3916            match self.get_item(item).await {
3917                Ok(i) => Some(i),
3918                Err(_) => None,
3919            },
3920        ))
3921    }
3922
3923    // GET
3924    /// Get an existing transaction
3925    ///
3926    /// # Arguments
3927    /// * `id`
3928    pub async fn get_transaction(&self, id: &str) -> Result<(Transaction, Option<Item>)> {
3929        // check in cache
3930        match self
3931            .base
3932            .cache
3933            .get(format!("rbeam.auth.econ.transaction:{}", id))
3934            .await
3935        {
3936            Some(c) => match serde_json::from_str::<BTreeMap<String, String>>(c.as_str()) {
3937                Ok(c) => {
3938                    return Ok(match self.gimme_transaction(c).await {
3939                        Ok(t) => t,
3940                        Err(e) => return Err(e),
3941                    })
3942                }
3943                Err(_) => {
3944                    self.base
3945                        .cache
3946                        .remove(format!("rbeam.auth.econ.transaction:{}", id))
3947                        .await;
3948                }
3949            },
3950            None => (),
3951        };
3952
3953        // pull from database
3954        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3955            "SELECT * FROM \"xugc_transactions\" WHERE \"id\" = ?"
3956        } else {
3957            "SELECT * FROM \"xugc_transactions\" WHERE \"id\" = $1"
3958        };
3959
3960        let c = &self.base.db.client;
3961        let res = match sqlquery(&query).bind::<&str>(&id).fetch_one(c).await {
3962            Ok(p) => self.base.textify_row(p).0,
3963            Err(_) => return Err(DatabaseError::NotFound),
3964        };
3965
3966        // return
3967        let transaction = match self.gimme_transaction(res).await {
3968            Ok(t) => t,
3969            Err(e) => return Err(e),
3970        };
3971
3972        // store in cache
3973        self.base
3974            .cache
3975            .set(
3976                format!("rbeam.auth.econ.transaction:{}", id),
3977                serde_json::to_string::<Transaction>(&transaction.0).unwrap(),
3978            )
3979            .await;
3980
3981        // return
3982        Ok(transaction)
3983    }
3984
3985    /// Get an existing transaction given the `customer` and the `item`
3986    ///
3987    /// # Arguments
3988    /// * `user`
3989    /// * `item`
3990    pub async fn get_transaction_by_customer_item(
3991        &self,
3992        customer: &str,
3993        item: &str,
3994    ) -> Result<(Transaction, Option<Item>)> {
3995        // pull from database
3996        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
3997            "SELECT * FROM \"xugc_transactions\" WHERE \"customer\" = ? AND \"item\" = ?"
3998        } else {
3999            "SELECT * FROM \"xugc_transactions\" WHERE \"customer\" = $1 AND \"item\" = $2"
4000        };
4001
4002        let c = &self.base.db.client;
4003        let res = match sqlquery(&query)
4004            .bind::<&str>(&customer)
4005            .bind::<&str>(&item)
4006            .fetch_one(c)
4007            .await
4008        {
4009            Ok(p) => self.base.textify_row(p).0,
4010            Err(_) => return Err(DatabaseError::NotFound),
4011        };
4012
4013        // return
4014        let transaction = match self.gimme_transaction(res).await {
4015            Ok(t) => t,
4016            Err(e) => return Err(e),
4017        };
4018
4019        // return
4020        Ok(transaction)
4021    }
4022
4023    /// Get all transactions where the customer is the given user ID, 12 at a time
4024    ///
4025    /// # Arguments
4026    /// * `user`
4027    /// * `page`
4028    ///
4029    /// # Returns
4030    /// `Vec<(Transaction, Customer, Merchant)>`
4031    pub async fn get_transactions_by_customer_paginated(
4032        &self,
4033        user: &str,
4034        page: i32,
4035    ) -> Result<Vec<((Transaction, Option<Item>), Box<Profile>, Box<Profile>)>> {
4036        // pull from database
4037        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4038            format!("SELECT * FROM \"xugc_transactions\" WHERE \"customer\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4039        } else {
4040            format!("SELECT * FROM \"xugc_transactions\" WHERE \"customer\" = $1 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4041        };
4042
4043        let c = &self.base.db.client;
4044        let res = match sqlquery(&query)
4045            .bind::<&str>(&user)
4046            .bind::<&str>(&user)
4047            .fetch_all(c)
4048            .await
4049        {
4050            Ok(p) => {
4051                let mut out = Vec::new();
4052
4053                for row in p {
4054                    let res = self.base.textify_row(row).0;
4055                    let transaction = match self.gimme_transaction(res).await {
4056                        Ok(t) => t,
4057                        Err(e) => return Err(e),
4058                    };
4059
4060                    let customer = transaction.0.customer.clone();
4061                    let merchant = transaction.0.merchant.clone();
4062
4063                    out.push((
4064                        transaction.clone(),
4065                        match self.get_profile(&customer).await {
4066                            Ok(ua) => ua,
4067                            Err(_) => continue,
4068                        },
4069                        match self.get_profile(&merchant).await {
4070                            Ok(ua) => ua,
4071                            Err(_) => continue,
4072                        },
4073                    ));
4074                }
4075
4076                out
4077            }
4078            Err(_) => return Err(DatabaseError::Other),
4079        };
4080
4081        // return
4082        Ok(res)
4083    }
4084
4085    /// Get all transactions by the given user ID, 12 at a time
4086    ///
4087    /// # Arguments
4088    /// * `user`
4089    /// * `page`
4090    ///
4091    /// # Returns
4092    /// `Vec<(Transaction, Customer, Merchant)>`
4093    pub async fn get_participating_transactions_paginated(
4094        &self,
4095        user: &str,
4096        page: i32,
4097    ) -> Result<Vec<((Transaction, Option<Item>), Box<Profile>, Box<Profile>)>> {
4098        // pull from database
4099        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4100            format!("SELECT * FROM \"xugc_transactions\" WHERE \"customer\" = ? OR \"merchant\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4101        } else {
4102            format!("SELECT * FROM \"xugc_transactions\" WHERE \"customer\" = $1 OR \"merchant\" = $2 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4103        };
4104
4105        let c = &self.base.db.client;
4106        let res = match sqlquery(&query)
4107            .bind::<&str>(&user)
4108            .bind::<&str>(&user)
4109            .fetch_all(c)
4110            .await
4111        {
4112            Ok(p) => {
4113                let mut out = Vec::new();
4114
4115                for row in p {
4116                    let res = self.base.textify_row(row).0;
4117                    let transaction = match self.gimme_transaction(res).await {
4118                        Ok(t) => t,
4119                        Err(e) => return Err(e),
4120                    };
4121
4122                    let customer = transaction.0.customer.clone();
4123                    let merchant = transaction.0.merchant.clone();
4124
4125                    out.push((
4126                        transaction.clone(),
4127                        match self.get_profile(&customer).await {
4128                            Ok(ua) => ua,
4129                            Err(_) => continue,
4130                        },
4131                        match self.get_profile(&merchant).await {
4132                            Ok(ua) => ua,
4133                            Err(_) => continue,
4134                        },
4135                    ));
4136                }
4137
4138                out
4139            }
4140            Err(_) => return Err(DatabaseError::Other),
4141        };
4142
4143        // return
4144        Ok(res)
4145    }
4146
4147    // SET
4148    /// Create a new transaction
4149    ///
4150    /// # Arguments
4151    /// * `props` - [`TransactionCreate`]
4152    /// * `customer` - the user in the `customer` field of the transaction
4153    pub async fn create_transaction(
4154        &self,
4155        props: TransactionCreate,
4156        customer: &str,
4157    ) -> Result<Transaction> {
4158        // make sure customer and merchant exist
4159        let customer = match self.get_profile(customer).await {
4160            Ok(ua) => ua,
4161            Err(e) => return Err(e),
4162        };
4163
4164        let merchant = match self.get_profile(&props.merchant).await {
4165            Ok(ua) => ua,
4166            Err(e) => return Err(e),
4167        };
4168
4169        // make sure customer can afford this
4170        if props.amount.is_negative() {
4171            if (customer.coins + props.amount) < 0 {
4172                return Err(DatabaseError::TooExpensive);
4173            }
4174        }
4175
4176        // ...
4177        let transaction = Transaction {
4178            // id: utility::random_id(),
4179            id: AlmostSnowflake::new(self.config.snowflake_server_id)
4180                .to_string()
4181                .to_string(),
4182            amount: props.amount,
4183            item: props.item,
4184            timestamp: utility::unix_epoch_timestamp(),
4185            customer: customer.id.clone(),
4186            merchant: merchant.id.clone(),
4187        };
4188
4189        // create transaction
4190        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4191            "INSERT INTO \"xugc_transactions\" VALUES (?, ?, ?, ?, ?, ?)"
4192        } else {
4193            "INSERT INTO \"xugc_transactions\" VALUES ($1, $2, $3, $4, $5, $6)"
4194        };
4195
4196        let c = &self.base.db.client;
4197        match sqlquery(&query)
4198            .bind::<&str>(&transaction.id)
4199            .bind::<i32>(transaction.amount)
4200            .bind::<&str>(&transaction.item)
4201            .bind::<&str>(&transaction.timestamp.to_string())
4202            .bind::<&str>(&transaction.customer)
4203            .bind::<&str>(&transaction.merchant)
4204            .execute(c)
4205            .await
4206        {
4207            Ok(_) => {
4208                // update balances
4209                if let Err(e) = self
4210                    .update_profile_coins(&customer.id, transaction.amount)
4211                    .await
4212                {
4213                    return Err(e);
4214                };
4215
4216                if let Err(e) = self
4217                    .update_profile_coins(&merchant.id, transaction.amount.abs())
4218                    .await
4219                {
4220                    return Err(e);
4221                };
4222
4223                // send notification
4224                if (customer.id != merchant.id) && (merchant.id != "0") {
4225                    if let Err(e) = self
4226                        .create_notification(
4227                            NotificationCreate {
4228                                title: "Purchased data now available!".to_string(),
4229                                content: "Data from an item you purchased is now available."
4230                                    .to_string(),
4231                                address: format!(
4232                                    "/market/item/{}#/preview",
4233                                    transaction.item.clone()
4234                                ),
4235                                recipient: customer.id,
4236                            },
4237                            None,
4238                        )
4239                        .await
4240                    {
4241                        return Err(e);
4242                    }
4243                }
4244
4245                // ...
4246                return Ok(transaction);
4247            }
4248            Err(_) => return Err(DatabaseError::Other),
4249        };
4250    }
4251
4252    // ugc items
4253
4254    // Get transaction given the `row` data
4255    pub fn gimme_item(&self, row: BTreeMap<String, String>) -> Result<Item> {
4256        Ok(Item {
4257            id: from_row!(row->id()),
4258            name: from_row!(row->name()),
4259            description: from_row!(row->description()),
4260            cost: row.get("cost").unwrap().parse::<i32>().unwrap_or(0),
4261            content: from_row!(row->content()),
4262            r#type: match serde_json::from_str(row.get("type").unwrap()) {
4263                Ok(v) => v,
4264                Err(_) => return Err(DatabaseError::ValueError),
4265            },
4266            status: match serde_json::from_str(row.get("status").unwrap()) {
4267                Ok(v) => v,
4268                Err(_) => return Err(DatabaseError::ValueError),
4269            },
4270            timestamp: row.get("timestamp").unwrap().parse::<u128>().unwrap(),
4271            creator: from_row!(row->creator()),
4272        })
4273    }
4274
4275    // GET
4276    /// Get an existing item
4277    ///
4278    /// # Arguments
4279    /// * `id`
4280    pub async fn get_item(&self, id: &str) -> Result<Item> {
4281        if id == "0" {
4282            // this item can be charged for things that don't relate to an item
4283            return Ok(Item {
4284                id: "0".to_string(),
4285                name: "System cost".to_string(),
4286                description: String::new(),
4287                cost: -1,
4288                content: String::new(),
4289                r#type: ItemType::Text,
4290                status: ItemStatus::Approved,
4291                timestamp: 0,
4292                creator: "0".to_string(),
4293            });
4294        }
4295
4296        // check in cache
4297        match self
4298            .base
4299            .cache
4300            .get(format!("rbeam.auth.econ.item:{}", id))
4301            .await
4302        {
4303            Some(c) => match serde_json::from_str::<Item>(c.as_str()) {
4304                Ok(c) => return Ok(c),
4305                Err(_) => {
4306                    self.base
4307                        .cache
4308                        .remove(format!("rbeam.auth.econ.item:{}", id))
4309                        .await;
4310                }
4311            },
4312            None => (),
4313        };
4314
4315        // pull from database
4316        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4317            "SELECT * FROM \"xugc_items\" WHERE \"id\" = ?"
4318        } else {
4319            "SELECT * FROM \"xugc_items\" WHERE \"id\" = $1"
4320        };
4321
4322        let c = &self.base.db.client;
4323        let res = match sqlquery(&query).bind::<&str>(&id).fetch_one(c).await {
4324            Ok(p) => self.base.textify_row(p).0,
4325            Err(_) => return Err(DatabaseError::NotFound),
4326        };
4327
4328        // return
4329        let item = match self.gimme_item(res) {
4330            Ok(t) => t,
4331            Err(e) => return Err(e),
4332        };
4333
4334        // store in cache
4335        self.base
4336            .cache
4337            .set(
4338                format!("rbeam.auth.econ.item:{}", id),
4339                serde_json::to_string::<Item>(&item).unwrap(),
4340            )
4341            .await;
4342
4343        // return
4344        Ok(item)
4345    }
4346
4347    /// Get all items by their creator, 12 at a time
4348    ///
4349    /// # Arguments
4350    /// * `user`
4351    /// * `page`
4352    ///
4353    /// # Returns
4354    /// `Vec<(Item, Box<Profile>)>`
4355    pub async fn get_items_by_creator_paginated(
4356        &self,
4357        user: &str,
4358        page: i32,
4359    ) -> Result<Vec<(Item, Box<Profile>)>> {
4360        // pull from database
4361        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4362            format!("SELECT * FROM \"xugc_items\" WHERE \"creator\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4363        } else {
4364            format!("SELECT * FROM \"xugc_items\" WHERE \"creator\" = $1 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4365        };
4366
4367        let c = &self.base.db.client;
4368        let res = match sqlquery(&query).bind::<&str>(&user).fetch_all(c).await {
4369            Ok(p) => {
4370                let mut out = Vec::new();
4371
4372                for row in p {
4373                    let res = self.base.textify_row(row).0;
4374                    let item = match self.gimme_item(res) {
4375                        Ok(t) => t,
4376                        Err(e) => return Err(e),
4377                    };
4378
4379                    let creator = item.creator.clone();
4380
4381                    out.push((
4382                        item,
4383                        match self.get_profile(&creator).await {
4384                            Ok(ua) => ua,
4385                            Err(_) => continue,
4386                        },
4387                    ));
4388                }
4389
4390                out
4391            }
4392            Err(_) => return Err(DatabaseError::Other),
4393        };
4394
4395        // return
4396        Ok(res)
4397    }
4398
4399    /// Get all items by their creator and type, 12 at a time
4400    ///
4401    /// # Arguments
4402    /// * `user`
4403    /// * `type`
4404    /// * `page`
4405    ///
4406    /// # Returns
4407    /// `Vec<(Item, Box<Profile>)>`
4408    pub async fn get_items_by_creator_type_paginated(
4409        &self,
4410        user: &str,
4411        r#type: ItemType,
4412        page: i32,
4413    ) -> Result<Vec<(Item, Box<Profile>)>> {
4414        // pull from database
4415        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4416            format!("SELECT * FROM \"xugc_items\" WHERE \"creator\" = ? AND \"type\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4417        } else {
4418            format!("SELECT * FROM \"xugc_items\" WHERE \"creator\" = $1 AND \"type\" = $2 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4419        };
4420
4421        let c = &self.base.db.client;
4422        let res = match sqlquery(&query)
4423            .bind::<&str>(&user)
4424            .bind::<&str>(&serde_json::to_string(&r#type).unwrap())
4425            .fetch_all(c)
4426            .await
4427        {
4428            Ok(p) => {
4429                let mut out = Vec::new();
4430
4431                for row in p {
4432                    let res = self.base.textify_row(row).0;
4433                    let item = match self.gimme_item(res) {
4434                        Ok(t) => t,
4435                        Err(e) => return Err(e),
4436                    };
4437
4438                    let creator = item.creator.clone();
4439
4440                    out.push((
4441                        item,
4442                        match self.get_profile(&creator).await {
4443                            Ok(ua) => ua,
4444                            Err(_) => continue,
4445                        },
4446                    ));
4447                }
4448
4449                out
4450            }
4451            Err(_) => return Err(DatabaseError::Other),
4452        };
4453
4454        // return
4455        Ok(res)
4456    }
4457
4458    /// Get all items by their status, 12 at a time
4459    ///
4460    /// # Arguments
4461    /// * `user`
4462    /// * `page`
4463    ///
4464    /// # Returns
4465    /// `Vec<(Item, Box<Profile>)>`
4466    pub async fn get_items_by_status_searched_paginated(
4467        &self,
4468        status: ItemStatus,
4469        page: i32,
4470        search: &str,
4471    ) -> Result<Vec<(Item, Box<Profile>)>> {
4472        // pull from database
4473        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4474            format!("SELECT * FROM \"xugc_items\" WHERE \"status\" = ? AND \"name\" LIKE ? AND \"cost\" != '-1' ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4475        } else {
4476            format!("SELECT * FROM \"xugc_items\" WHERE \"status\" = $1 AND \"name\" LIKE $2 AND \"cost\" != '-1' ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4477        };
4478
4479        let c = &self.base.db.client;
4480        let res = match sqlquery(&query)
4481            .bind::<&str>(&serde_json::to_string(&status).unwrap())
4482            .bind::<&str>(&format!("%{search}%"))
4483            .fetch_all(c)
4484            .await
4485        {
4486            Ok(p) => {
4487                let mut out = Vec::new();
4488
4489                for row in p {
4490                    let res = self.base.textify_row(row).0;
4491                    let item = match self.gimme_item(res) {
4492                        Ok(t) => t,
4493                        Err(e) => return Err(e),
4494                    };
4495
4496                    let creator = item.creator.clone();
4497
4498                    out.push((
4499                        item,
4500                        match self.get_profile(&creator).await {
4501                            Ok(ua) => ua,
4502                            Err(_) => continue,
4503                        },
4504                    ));
4505                }
4506
4507                out
4508            }
4509            Err(_) => return Err(DatabaseError::Other),
4510        };
4511
4512        // return
4513        Ok(res)
4514    }
4515
4516    /// Get all items by their type, 12 at a time
4517    ///
4518    /// # Arguments
4519    /// * `type`
4520    /// * `page`
4521    ///
4522    /// # Returns
4523    /// `Vec<(Item, Box<Profile>)>`
4524    pub async fn get_items_by_type_paginated(
4525        &self,
4526        r#type: ItemType,
4527        page: i32,
4528    ) -> Result<Vec<(Item, Box<Profile>)>> {
4529        // pull from database
4530        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4531            format!("SELECT * FROM \"xugc_items\" WHERE \"type\" = ? ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4532        } else {
4533            format!("SELECT * FROM \"xugc_items\" WHERE \"type\" = $2 ORDER BY \"timestamp\" DESC LIMIT 12 OFFSET {}", page * 12)
4534        };
4535
4536        let c = &self.base.db.client;
4537        let res = match sqlquery(&query)
4538            .bind::<&str>(&serde_json::to_string(&r#type).unwrap())
4539            .fetch_all(c)
4540            .await
4541        {
4542            Ok(p) => {
4543                let mut out = Vec::new();
4544
4545                for row in p {
4546                    let res = self.base.textify_row(row).0;
4547                    let item = match self.gimme_item(res) {
4548                        Ok(t) => t,
4549                        Err(e) => return Err(e),
4550                    };
4551
4552                    let creator = item.creator.clone();
4553
4554                    out.push((
4555                        item,
4556                        match self.get_profile(&creator).await {
4557                            Ok(ua) => ua,
4558                            Err(_) => continue,
4559                        },
4560                    ));
4561                }
4562
4563                out
4564            }
4565            Err(_) => return Err(DatabaseError::Other),
4566        };
4567
4568        // return
4569        Ok(res)
4570    }
4571
4572    // SET
4573    /// Create a new item
4574    ///
4575    /// # Arguments
4576    /// * `props` - [`ItemCreate`]
4577    /// * `creator` - the user in the `creator` field of the item
4578    pub async fn create_item(&self, props: ItemCreate, creator: &str) -> Result<Item> {
4579        // check values
4580        if props.content.len() > (64 * 128 * 2) {
4581            return Err(DatabaseError::TooLong);
4582        }
4583
4584        if props.content.len() < 2 {
4585            return Err(DatabaseError::ValueError);
4586        }
4587
4588        if props.name.len() > (64 * 2) {
4589            return Err(DatabaseError::TooLong);
4590        }
4591
4592        if props.name.len() < 2 {
4593            return Err(DatabaseError::ValueError);
4594        }
4595
4596        if props.description.len() > (64 * 128) {
4597            return Err(DatabaseError::TooLong);
4598        }
4599
4600        // if we're creating a module, the cost HAS to be -1 (offsale)
4601        if props.r#type == ItemType::Module && props.cost != -1 {
4602            return Err(DatabaseError::NotAllowed);
4603        }
4604
4605        // ...
4606        if props.cost.is_negative() && props.cost != -1 {
4607            return Err(DatabaseError::NotAllowed);
4608        }
4609
4610        let item = Item {
4611            // id: utility::random_id(),
4612            id: AlmostSnowflake::new(self.config.snowflake_server_id).to_string(),
4613            name: props.name,
4614            description: props.description,
4615            cost: props.cost,
4616            content: props.content,
4617            r#type: props.r#type,
4618            status: ItemStatus::Pending,
4619            timestamp: utility::unix_epoch_timestamp(),
4620            creator: creator.to_string(),
4621        };
4622
4623        // subtract creation cost from creator
4624        /* if let Err(e) = self
4625            .create_transaction(
4626                TransactionCreate {
4627                    merchant: "0",
4628                    item: "0",
4629                    amount: -25,
4630                },
4631                creator.clone(),
4632            )
4633            .await
4634        {
4635            return Err(e);
4636        }; */
4637
4638        // create item
4639        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4640            "INSERT INTO \"xugc_items\" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
4641        } else {
4642            "INSERT INTO \"xugc_items\" VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)"
4643        };
4644
4645        let c = &self.base.db.client;
4646        match sqlquery(&query)
4647            .bind::<&str>(&item.id)
4648            .bind::<&str>(&item.name)
4649            .bind::<&str>(&item.description)
4650            .bind::<i32>(item.cost)
4651            .bind::<&str>(&item.content)
4652            .bind::<&str>(&serde_json::to_string(&item.r#type).unwrap())
4653            .bind::<&str>(&serde_json::to_string(&item.status).unwrap())
4654            .bind::<&str>(&item.timestamp.to_string())
4655            .bind::<&str>(&item.creator)
4656            .execute(c)
4657            .await
4658        {
4659            Ok(_) => {
4660                // buy item (for free)
4661                if let Err(e) = self
4662                    .create_transaction(
4663                        TransactionCreate {
4664                            merchant: creator.to_string(),
4665                            item: item.id.clone(),
4666                            amount: 0,
4667                        },
4668                        creator,
4669                    )
4670                    .await
4671                {
4672                    return Err(e);
4673                };
4674
4675                // ...
4676                return Ok(item);
4677            }
4678            Err(_) => return Err(DatabaseError::Other),
4679        };
4680    }
4681
4682    /// Update item status
4683    ///
4684    /// # Arguments
4685    /// * `id`
4686    /// * `status` - [`ItemStatus`]
4687    /// * `user`
4688    pub async fn update_item_status(
4689        &self,
4690        id: &str,
4691        status: ItemStatus,
4692        user: Box<Profile>,
4693    ) -> Result<()> {
4694        // make sure item exists and check permission
4695        let item = match self.get_item(id).await {
4696            Ok(i) => i,
4697            Err(e) => return Err(e),
4698        };
4699
4700        // check permission
4701        let group = match self.get_group_by_id(user.group).await {
4702            Ok(g) => g,
4703            Err(_) => return Err(DatabaseError::Other),
4704        };
4705
4706        if !group.permissions.check(FinePermission::ECON_MASTER) {
4707            return Err(DatabaseError::NotAllowed);
4708        }
4709
4710        // update item
4711        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4712            "UPDATE \"xugc_items\" SET \"status\" = ? WHERE \"id\" = ?"
4713        } else {
4714            "UPDATE \"xugc_items\" SET (\"status\") = ($1) WHERE \"id\" = $2"
4715        };
4716
4717        let c = &self.base.db.client;
4718        match sqlquery(&query)
4719            .bind::<&str>(&serde_json::to_string(&status).unwrap())
4720            .bind::<&str>(&id)
4721            .execute(c)
4722            .await
4723        {
4724            Ok(_) => {
4725                if let Err(e) = self
4726                    .create_notification(
4727                        NotificationCreate {
4728                            title: "Item status updated!".to_string(),
4729                            content: format!(
4730                                "An item you created has been updated to the status of \"{}\"",
4731                                status.to_string()
4732                            ),
4733                            address: format!("/market/item/{}", item.id.clone()),
4734                            recipient: item.creator,
4735                        },
4736                        None,
4737                    )
4738                    .await
4739                {
4740                    return Err(e);
4741                }
4742
4743                // remove from cache
4744                self.base
4745                    .cache
4746                    .remove(format!("rbeam.auth.econ.item:{}", id))
4747                    .await;
4748
4749                // ...
4750                return Ok(());
4751            }
4752            Err(_) => return Err(DatabaseError::Other),
4753        };
4754    }
4755
4756    /// Update item fields
4757    ///
4758    /// # Arguments
4759    /// * `id`
4760    /// * `props` - [`ItemEdit`]
4761    /// * `user`
4762    pub async fn update_item(&self, id: &str, props: ItemEdit, user: Box<Profile>) -> Result<()> {
4763        // make sure item exists and check permission
4764        let item = match self.get_item(id).await {
4765            Ok(i) => i,
4766            Err(e) => return Err(e),
4767        };
4768
4769        // check values
4770        if props.name.len() > (64 * 2) {
4771            return Err(DatabaseError::TooLong);
4772        }
4773
4774        if props.name.len() < 2 {
4775            return Err(DatabaseError::ValueError);
4776        }
4777
4778        if props.description.len() > (64 * 128) {
4779            return Err(DatabaseError::TooLong);
4780        }
4781
4782        // if we're creating a module, the cost HAS to be -1 (offsale)
4783        if item.r#type == ItemType::Module && props.cost != -1 {
4784            return Err(DatabaseError::NotAllowed);
4785        }
4786
4787        // ...
4788        if props.cost.is_negative() && props.cost != -1 {
4789            return Err(DatabaseError::NotAllowed);
4790        }
4791
4792        // check permission
4793        let group = match self.get_group_by_id(user.group).await {
4794            Ok(g) => g,
4795            Err(_) => return Err(DatabaseError::Other),
4796        };
4797
4798        if user.id != item.creator {
4799            if !group.permissions.check(FinePermission::ECON_MASTER) {
4800                return Err(DatabaseError::NotAllowed);
4801            }
4802        }
4803
4804        // update item
4805        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4806            "UPDATE \"xugc_items\" SET \"name\" = ?, \"description\" = ?, \"cost\" = ? WHERE \"id\" = ?"
4807        } else {
4808            "UPDATE \"xugc_items\" SET (\"name\", \"description\", \"cost\") = ($1, $2, $3) WHERE \"id\" = $4"
4809        };
4810
4811        let c = &self.base.db.client;
4812        match sqlquery(&query)
4813            .bind::<&str>(&props.name)
4814            .bind::<&str>(&props.description)
4815            .bind::<i32>(props.cost)
4816            .bind::<&str>(&id)
4817            .execute(c)
4818            .await
4819        {
4820            Ok(_) => {
4821                // remove from cache
4822                self.base
4823                    .cache
4824                    .remove(format!("rbeam.auth.econ.item:{}", id))
4825                    .await;
4826
4827                // ...
4828                return Ok(());
4829            }
4830            Err(_) => return Err(DatabaseError::Other),
4831        };
4832    }
4833
4834    /// Update item content
4835    ///
4836    /// # Arguments
4837    /// * `id`
4838    /// * `props` - [`ItemEditContent`]
4839    /// * `user`
4840    pub async fn update_item_content(
4841        &self,
4842        id: &str,
4843        props: ItemEditContent,
4844        user: Box<Profile>,
4845    ) -> Result<()> {
4846        // make sure item exists and check permission
4847        let item = match self.get_item(id).await {
4848            Ok(i) => i,
4849            Err(e) => return Err(e),
4850        };
4851
4852        // we cannot change the content of a module
4853        // this would allow people to lie about the checksum of their wasm package
4854        //
4855        // doing this also ensures that people create a new asset for each version of their package,
4856        // meaning old versions will still verify properly
4857        if item.r#type == ItemType::Module {
4858            return Err(DatabaseError::NotAllowed);
4859        }
4860
4861        // check values
4862        if props.content.len() > (64 * 128 * 2) {
4863            return Err(DatabaseError::TooLong);
4864        }
4865
4866        if props.content.len() < 2 {
4867            return Err(DatabaseError::ValueError);
4868        }
4869
4870        // check permission
4871        let group = match self.get_group_by_id(user.group).await {
4872            Ok(g) => g,
4873            Err(_) => return Err(DatabaseError::Other),
4874        };
4875
4876        if user.id != item.creator {
4877            if !group.permissions.check(FinePermission::ECON_MASTER) {
4878                return Err(DatabaseError::NotAllowed);
4879            }
4880        }
4881
4882        // update item
4883        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4884            "UPDATE \"xugc_items\" SET \"content\" = ? WHERE \"id\" = ?"
4885        } else {
4886            "UPDATE \"xugc_items\" SET (\"content\") = ($1) WHERE \"id\" = $2"
4887        };
4888
4889        let c = &self.base.db.client;
4890        match sqlquery(&query)
4891            .bind::<&str>(&props.content)
4892            .bind::<&str>(&id)
4893            .execute(c)
4894            .await
4895        {
4896            Ok(_) => {
4897                // remove from cache
4898                self.base
4899                    .cache
4900                    .remove(format!("rbeam.auth.econ.item:{}", id))
4901                    .await;
4902
4903                // ...
4904                return Ok(());
4905            }
4906            Err(_) => return Err(DatabaseError::Other),
4907        };
4908    }
4909
4910    /// Delete an existing item
4911    ///
4912    /// Items can only be deleted by their creator.
4913    ///
4914    /// # Arguments
4915    /// * `id` - the ID of the item
4916    /// * `user` - the user doing this
4917    pub async fn delete_item(&self, id: &str, user: Box<Profile>) -> Result<()> {
4918        // make sure item exists
4919        let item = match self.get_item(id).await {
4920            Ok(n) => n,
4921            Err(e) => return Err(e),
4922        };
4923
4924        // check username
4925        if item.creator != user.id {
4926            // check permission
4927            let group = match self.get_group_by_id(user.group).await {
4928                Ok(g) => g,
4929                Err(_) => return Err(DatabaseError::Other),
4930            };
4931
4932            if !group.permissions.check(FinePermission::ECON_MASTER) {
4933                return Err(DatabaseError::NotAllowed);
4934            }
4935        }
4936
4937        // delete item
4938        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4939            "DELETE FROM \"xugc_items\" WHERE \"id\" = ?"
4940        } else {
4941            "DELETE FROM \"xugc_items\" WHERE \"id\" = $1"
4942        };
4943
4944        let c = &self.base.db.client;
4945        match sqlquery(&query).bind::<&str>(&id).execute(c).await {
4946            Ok(_) => {
4947                // remove from cache
4948                self.base
4949                    .cache
4950                    .remove(format!("rbeam.auth.econ.item:{}", id))
4951                    .await;
4952
4953                // return
4954                return Ok(());
4955            }
4956            Err(_) => return Err(DatabaseError::Other),
4957        };
4958    }
4959
4960    // totp
4961
4962    /// Update the profile's TOTP secret.
4963    pub async fn update_profile_totp_secret(
4964        &self,
4965        id: &str,
4966        secret: &str,
4967        recovery: &Vec<String>,
4968    ) -> Result<()> {
4969        let ua = match self.get_profile_by_id(&id).await {
4970            Ok(ua) => ua,
4971            Err(e) => return Err(e),
4972        };
4973
4974        // update profile
4975        let query = if (self.base.db.r#type == "sqlite") | (self.base.db.r#type == "mysql") {
4976            "UPDATE \"xprofiles\" SET \"totp\" = ?, \"recovery_codes\" = ? WHERE \"id\" = ?"
4977        } else {
4978            "UPDATE \"xprofiles\" SET (\"totp\", \"recovery_codes\") = ($1, $2) WHERE \"id\" = $3"
4979        };
4980
4981        let c = &self.base.db.client;
4982        match sqlquery(&query)
4983            .bind::<&str>(&secret)
4984            .bind::<&str>(&serde_json::to_string(&recovery).unwrap())
4985            .bind::<&str>(&id)
4986            .execute(c)
4987            .await
4988        {
4989            Ok(_) => {
4990                self.base
4991                    .cache
4992                    .remove(format!("rbeam.auth.profile:{id}"))
4993                    .await;
4994
4995                self.base
4996                    .cache
4997                    .remove(format!("rbeam.auth.profile:{}", ua.username))
4998                    .await;
4999
5000                Ok(())
5001            }
5002            Err(_) => Err(DatabaseError::Other),
5003        }
5004    }
5005
5006    /// Enable TOTP for a profile.
5007    ///
5008    /// # Returns
5009    /// `Result<(secret, qr base64)>`
5010    pub async fn enable_totp(
5011        &self,
5012        as_user: Box<Profile>,
5013        id: &str,
5014    ) -> Result<(String, String, Vec<String>)> {
5015        let profile = match self.get_profile(&id).await {
5016            Ok(p) => p,
5017            Err(e) => return Err(e),
5018        };
5019
5020        if profile.id != as_user.id {
5021            return Err(DatabaseError::NotAllowed);
5022        }
5023
5024        let secret = totp_rs::Secret::default().to_string();
5025        let recovery = Database::generate_totp_recovery_codes();
5026
5027        // update profile
5028        if let Err(e) = self
5029            .update_profile_totp_secret(id, &secret, &recovery)
5030            .await
5031        {
5032            return Err(e);
5033        }
5034
5035        // fetch profile again (with totp information)
5036        let profile = match self.get_profile(&id).await {
5037            Ok(p) => p,
5038            Err(e) => return Err(e),
5039        };
5040
5041        // get totp
5042        let totp = profile.totp(Some(
5043            self.config
5044                .host
5045                .replace("http://", "")
5046                .replace("https://", "")
5047                .replace(":", "_"),
5048        ));
5049
5050        if totp.is_none() {
5051            return Err(DatabaseError::Other);
5052        }
5053
5054        let totp = totp.unwrap();
5055
5056        // generate qr
5057        let qr = match totp.get_qr_base64() {
5058            Ok(q) => q,
5059            Err(_) => return Err(DatabaseError::Other),
5060        };
5061
5062        // return
5063        Ok((totp.get_secret_base32(), qr, recovery))
5064    }
5065
5066    /// Validate a given TOTP code for the given profile.
5067    pub fn check_totp(&self, ua: &Box<Profile>, code: &str) -> bool {
5068        let totp = ua.totp(Some(
5069            self.config
5070                .host
5071                .replace("http://", "")
5072                .replace("https://", "")
5073                .replace(":", "_"),
5074        ));
5075
5076        if let Some(totp) = totp {
5077            return !code.is_empty()
5078                && (totp.check_current(code).unwrap()
5079                    | ua.recovery_codes.contains(&code.to_string()));
5080        }
5081
5082        true
5083    }
5084
5085    /// Generate 8 random recovery codes for TOTP.
5086    pub fn generate_totp_recovery_codes() -> Vec<String> {
5087        let mut out: Vec<String> = Vec::new();
5088
5089        for _ in 0..9 {
5090            out.push(rainbeam_shared::hash::salt())
5091        }
5092
5093        out
5094    }
5095}