systemprompt_users/repository/user/
stats.rs

1use chrono::{DateTime, Utc};
2
3use crate::error::Result;
4use crate::models::{UserStats, UserStatus};
5use crate::repository::UserRepository;
6
7#[derive(sqlx::FromRow)]
8struct StatusCount {
9    status: Option<String>,
10    cnt: Option<i64>,
11}
12
13#[derive(sqlx::FromRow)]
14struct RoleCount {
15    role: Option<String>,
16    cnt: Option<i64>,
17}
18
19#[derive(sqlx::FromRow)]
20struct StatsRow {
21    total: Option<i64>,
22    created_24h: Option<i64>,
23    created_7d: Option<i64>,
24    created_30d: Option<i64>,
25    active: Option<i64>,
26    suspended: Option<i64>,
27    admins: Option<i64>,
28    anonymous: Option<i64>,
29    bots: Option<i64>,
30    oldest_user: Option<DateTime<Utc>>,
31    newest_user: Option<DateTime<Utc>>,
32}
33
34impl UserRepository {
35    pub async fn count_by_status(&self) -> Result<Vec<(String, i64)>> {
36        let deleted_status = UserStatus::Deleted.as_str();
37        let rows = sqlx::query_as!(
38            StatusCount,
39            r#"
40            SELECT status, COUNT(*)::bigint as cnt
41            FROM users
42            WHERE status != $1
43            GROUP BY status
44            ORDER BY cnt DESC
45            "#,
46            deleted_status
47        )
48        .fetch_all(&*self.pool)
49        .await?;
50
51        Ok(rows
52            .into_iter()
53            .filter_map(|r| Some((r.status?, r.cnt?)))
54            .collect())
55    }
56
57    pub async fn count_by_role(&self) -> Result<Vec<(String, i64)>> {
58        let deleted_status = UserStatus::Deleted.as_str();
59        let rows = sqlx::query_as!(
60            RoleCount,
61            r#"
62            SELECT role, COUNT(*)::bigint as cnt
63            FROM users, UNNEST(roles) as role
64            WHERE status != $1
65            GROUP BY role
66            ORDER BY cnt DESC
67            "#,
68            deleted_status
69        )
70        .fetch_all(&*self.pool)
71        .await?;
72
73        Ok(rows
74            .into_iter()
75            .filter_map(|r| Some((r.role?, r.cnt?)))
76            .collect())
77    }
78
79    pub async fn get_stats(&self) -> Result<UserStats> {
80        let deleted_status = UserStatus::Deleted.as_str();
81        let row = sqlx::query_as!(
82            StatsRow,
83            r#"
84            SELECT
85                COUNT(*)::bigint as total,
86                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '24 hours')::bigint as created_24h,
87                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days')::bigint as created_7d,
88                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days')::bigint as created_30d,
89                COUNT(*) FILTER (WHERE status = 'active')::bigint as active,
90                COUNT(*) FILTER (WHERE status = 'suspended')::bigint as suspended,
91                COUNT(*) FILTER (WHERE 'admin' = ANY(roles))::bigint as admins,
92                COUNT(*) FILTER (WHERE 'anonymous' = ANY(roles))::bigint as anonymous,
93                COUNT(*) FILTER (WHERE is_bot = true)::bigint as bots,
94                MIN(created_at) as oldest_user,
95                MAX(created_at) as newest_user
96            FROM users
97            WHERE status != $1
98            "#,
99            deleted_status
100        )
101        .fetch_one(&*self.pool)
102        .await?;
103
104        Ok(UserStats {
105            total: row.total.unwrap_or(0),
106            created_24h: row.created_24h.unwrap_or(0),
107            created_7d: row.created_7d.unwrap_or(0),
108            created_30d: row.created_30d.unwrap_or(0),
109            active: row.active.unwrap_or(0),
110            suspended: row.suspended.unwrap_or(0),
111            admins: row.admins.unwrap_or(0),
112            anonymous: row.anonymous.unwrap_or(0),
113            bots: row.bots.unwrap_or(0),
114            oldest_user: row.oldest_user,
115            newest_user: row.newest_user,
116        })
117    }
118}
systemprompt_users/repository/user/stats.rs

systemprompt_users/repository/user/
stats.rs
