kaccy_db/
platform_stats.rs

1//! Platform-wide statistics and aggregations
2//!
3//! This module provides high-level statistics that aggregate data across
4//! multiple repositories for platform monitoring and dashboard displays.
5
6use crate::error::Result;
7use rust_decimal::Decimal;
8use serde::{Deserialize, Serialize};
9use sqlx::{Executor, PgPool, Row};
10
11/// Platform-wide statistics summary
12#[derive(Debug, Clone, Serialize, Deserialize)]
13pub struct PlatformStats {
14    /// Total number of users
15    pub total_users: i64,
16    /// Total number of active users (not deleted)
17    pub active_users: i64,
18    /// Total number of tokens
19    pub total_tokens: i64,
20    /// Total number of active tokens
21    pub active_tokens: i64,
22    /// Total number of trades
23    pub total_trades: i64,
24    /// Total trading volume
25    pub total_volume: Decimal,
26    /// Total platform fees collected
27    pub total_fees: Decimal,
28    /// Total number of orders
29    pub total_orders: i64,
30    /// Number of pending orders
31    pub pending_orders: i64,
32    /// Total number of commitments
33    pub total_commitments: i64,
34    /// Number of verified commitments
35    pub verified_commitments: i64,
36}
37
38/// Get comprehensive platform statistics
39///
40/// Aggregates key metrics across all repositories for monitoring dashboards
41pub async fn get_platform_stats<'e, E>(executor: E) -> Result<PlatformStats>
42where
43    E: Executor<'e, Database = sqlx::Postgres>,
44{
45    let row = sqlx::query(
46        r#"
47        SELECT
48            (SELECT COUNT(*) FROM users) as total_users,
49            (SELECT COUNT(*) FROM users WHERE role != 'deleted') as active_users,
50            (SELECT COUNT(*) FROM tokens) as total_tokens,
51            (SELECT COUNT(*) FROM tokens WHERE status = 'active') as active_tokens,
52            (SELECT COUNT(*) FROM trades) as total_trades,
53            (SELECT COALESCE(SUM(btc_amount), 0) FROM trades) as total_volume,
54            (SELECT COALESCE(SUM(platform_fee), 0) FROM trades) as total_fees,
55            (SELECT COUNT(*) FROM orders) as total_orders,
56            (SELECT COUNT(*) FROM orders WHERE status = 'pending') as pending_orders,
57            (SELECT COUNT(*) FROM output_commitments) as total_commitments,
58            (SELECT COUNT(*) FROM output_commitments WHERE status = 'verified') as verified_commitments
59        "#,
60    )
61    .fetch_one(executor)
62    .await?;
63
64    Ok(PlatformStats {
65        total_users: row.get("total_users"),
66        active_users: row.get("active_users"),
67        total_tokens: row.get("total_tokens"),
68        active_tokens: row.get("active_tokens"),
69        total_trades: row.get("total_trades"),
70        total_volume: row.get("total_volume"),
71        total_fees: row.get("total_fees"),
72        total_orders: row.get("total_orders"),
73        pending_orders: row.get("pending_orders"),
74        total_commitments: row.get("total_commitments"),
75        verified_commitments: row.get("verified_commitments"),
76    })
77}
78
79/// Platform growth metrics comparing time periods
80#[derive(Debug, Clone, Serialize, Deserialize)]
81pub struct GrowthMetrics {
82    /// New users in last 24 hours
83    pub users_24h: i64,
84    /// New users in last 7 days
85    pub users_7d: i64,
86    /// New users in last 30 days
87    pub users_30d: i64,
88    /// New tokens in last 24 hours
89    pub tokens_24h: i64,
90    /// New tokens in last 7 days
91    pub tokens_7d: i64,
92    /// New tokens in last 30 days
93    pub tokens_30d: i64,
94    /// Trades in last 24 hours
95    pub trades_24h: i64,
96    /// Trades in last 7 days
97    pub trades_7d: i64,
98    /// Trades in last 30 days
99    pub trades_30d: i64,
100    /// Trading volume in last 24 hours
101    pub volume_24h: Decimal,
102    /// Trading volume in last 7 days
103    pub volume_7d: Decimal,
104    /// Trading volume in last 30 days
105    pub volume_30d: Decimal,
106}
107
108/// Get platform growth metrics
109///
110/// Calculates growth statistics over different time periods
111pub async fn get_growth_metrics<'e, E>(executor: E) -> Result<GrowthMetrics>
112where
113    E: Executor<'e, Database = sqlx::Postgres>,
114{
115    let row = sqlx::query(
116        r#"
117        SELECT
118            (SELECT COUNT(*) FROM users WHERE created_at >= NOW() - INTERVAL '24 hours') as users_24h,
119            (SELECT COUNT(*) FROM users WHERE created_at >= NOW() - INTERVAL '7 days') as users_7d,
120            (SELECT COUNT(*) FROM users WHERE created_at >= NOW() - INTERVAL '30 days') as users_30d,
121            (SELECT COUNT(*) FROM tokens WHERE created_at >= NOW() - INTERVAL '24 hours') as tokens_24h,
122            (SELECT COUNT(*) FROM tokens WHERE created_at >= NOW() - INTERVAL '7 days') as tokens_7d,
123            (SELECT COUNT(*) FROM tokens WHERE created_at >= NOW() - INTERVAL '30 days') as tokens_30d,
124            (SELECT COUNT(*) FROM trades WHERE created_at >= NOW() - INTERVAL '24 hours') as trades_24h,
125            (SELECT COUNT(*) FROM trades WHERE created_at >= NOW() - INTERVAL '7 days') as trades_7d,
126            (SELECT COUNT(*) FROM trades WHERE created_at >= NOW() - INTERVAL '30 days') as trades_30d,
127            (SELECT COALESCE(SUM(btc_amount), 0) FROM trades WHERE created_at >= NOW() - INTERVAL '24 hours') as volume_24h,
128            (SELECT COALESCE(SUM(btc_amount), 0) FROM trades WHERE created_at >= NOW() - INTERVAL '7 days') as volume_7d,
129            (SELECT COALESCE(SUM(btc_amount), 0) FROM trades WHERE created_at >= NOW() - INTERVAL '30 days') as volume_30d
130        "#,
131    )
132    .fetch_one(executor)
133    .await?;
134
135    Ok(GrowthMetrics {
136        users_24h: row.get("users_24h"),
137        users_7d: row.get("users_7d"),
138        users_30d: row.get("users_30d"),
139        tokens_24h: row.get("tokens_24h"),
140        tokens_7d: row.get("tokens_7d"),
141        tokens_30d: row.get("tokens_30d"),
142        trades_24h: row.get("trades_24h"),
143        trades_7d: row.get("trades_7d"),
144        trades_30d: row.get("trades_30d"),
145        volume_24h: row.get("volume_24h"),
146        volume_7d: row.get("volume_7d"),
147        volume_30d: row.get("volume_30d"),
148    })
149}
150
151/// Platform health indicators
152#[derive(Debug, Clone, Serialize, Deserialize)]
153pub struct HealthIndicators {
154    /// Average reputation score of all users
155    pub avg_reputation: Decimal,
156    /// Percentage of verified commitments
157    pub commitment_success_rate: Decimal,
158    /// Percentage of completed orders
159    pub order_completion_rate: Decimal,
160    /// Average trade size
161    pub avg_trade_size: Decimal,
162    /// Number of unique traders in last 30 days
163    pub active_traders_30d: i64,
164    /// Number of tokens with active trading
165    pub liquid_tokens: i64,
166}
167
168/// Get platform health indicators
169///
170/// Provides key metrics for assessing platform health and engagement
171pub async fn get_health_indicators<'e, E>(executor: E) -> Result<HealthIndicators>
172where
173    E: Executor<'e, Database = sqlx::Postgres>,
174{
175    let row = sqlx::query(
176        r#"
177        SELECT
178            COALESCE(AVG(reputation_score), 0) as avg_reputation,
179            CASE
180                WHEN (SELECT COUNT(*) FROM output_commitments) > 0
181                THEN (SELECT COUNT(*)::decimal FROM output_commitments WHERE status = 'verified') * 100.0 /
182                     (SELECT COUNT(*) FROM output_commitments)
183                ELSE 0
184            END as commitment_success_rate,
185            CASE
186                WHEN (SELECT COUNT(*) FROM orders) > 0
187                THEN (SELECT COUNT(*)::decimal FROM orders WHERE status = 'completed') * 100.0 /
188                     (SELECT COUNT(*) FROM orders)
189                ELSE 0
190            END as order_completion_rate,
191            CASE
192                WHEN (SELECT COUNT(*) FROM trades) > 0
193                THEN (SELECT AVG(btc_amount) FROM trades)
194                ELSE 0
195            END as avg_trade_size,
196            (
197                SELECT COUNT(DISTINCT user_id) FROM (
198                    SELECT buyer_user_id as user_id FROM trades WHERE created_at >= NOW() - INTERVAL '30 days'
199                    UNION
200                    SELECT seller_user_id as user_id FROM trades WHERE created_at >= NOW() - INTERVAL '30 days'
201                ) as active_users
202            ) as active_traders_30d,
203            (
204                SELECT COUNT(DISTINCT token_id) FROM trades WHERE created_at >= NOW() - INTERVAL '7 days'
205            ) as liquid_tokens
206        FROM users
207        WHERE role != 'deleted'
208        "#,
209    )
210    .fetch_one(executor)
211    .await?;
212
213    Ok(HealthIndicators {
214        avg_reputation: row.get("avg_reputation"),
215        commitment_success_rate: row.get("commitment_success_rate"),
216        order_completion_rate: row.get("order_completion_rate"),
217        avg_trade_size: row.get("avg_trade_size"),
218        active_traders_30d: row.get("active_traders_30d"),
219        liquid_tokens: row.get("liquid_tokens"),
220    })
221}
222
223/// Get efficient platform snapshot (optimized single query)
224///
225/// Returns the most essential metrics in a single database round-trip
226pub async fn get_platform_snapshot(pool: &PgPool) -> Result<PlatformStats> {
227    get_platform_stats(pool).await
228}
229
230#[cfg(test)]
231mod tests {
232    use super::*;
233
234    #[test]
235    fn test_platform_stats_structure() {
236        let stats = PlatformStats {
237            total_users: 100,
238            active_users: 95,
239            total_tokens: 50,
240            active_tokens: 45,
241            total_trades: 1000,
242            total_volume: Decimal::new(50000, 2),
243            total_fees: Decimal::new(500, 2),
244            total_orders: 800,
245            pending_orders: 50,
246            total_commitments: 200,
247            verified_commitments: 180,
248        };
249
250        assert_eq!(stats.total_users, 100);
251        assert_eq!(stats.active_users, 95);
252        assert_eq!(stats.total_tokens, 50);
253    }
254
255    #[test]
256    fn test_growth_metrics_structure() {
257        let metrics = GrowthMetrics {
258            users_24h: 10,
259            users_7d: 50,
260            users_30d: 200,
261            tokens_24h: 5,
262            tokens_7d: 25,
263            tokens_30d: 100,
264            trades_24h: 100,
265            trades_7d: 500,
266            trades_30d: 2000,
267            volume_24h: Decimal::new(1000, 2),
268            volume_7d: Decimal::new(5000, 2),
269            volume_30d: Decimal::new(20000, 2),
270        };
271
272        assert_eq!(metrics.users_24h, 10);
273        assert_eq!(metrics.trades_7d, 500);
274        assert_eq!(metrics.volume_30d, Decimal::new(20000, 2));
275    }
276
277    #[test]
278    fn test_health_indicators_structure() {
279        let indicators = HealthIndicators {
280            avg_reputation: Decimal::new(7500, 2),
281            commitment_success_rate: Decimal::new(9000, 2),
282            order_completion_rate: Decimal::new(8500, 2),
283            avg_trade_size: Decimal::new(50, 2),
284            active_traders_30d: 150,
285            liquid_tokens: 30,
286        };
287
288        assert_eq!(indicators.avg_reputation, Decimal::new(7500, 2));
289        assert_eq!(indicators.active_traders_30d, 150);
290    }
291
292    #[test]
293    fn test_platform_stats_serialization() {
294        let stats = PlatformStats {
295            total_users: 100,
296            active_users: 95,
297            total_tokens: 50,
298            active_tokens: 45,
299            total_trades: 1000,
300            total_volume: Decimal::new(50000, 2),
301            total_fees: Decimal::new(500, 2),
302            total_orders: 800,
303            pending_orders: 50,
304            total_commitments: 200,
305            verified_commitments: 180,
306        };
307
308        let json = serde_json::to_string(&stats).unwrap();
309        let deserialized: PlatformStats = serde_json::from_str(&json).unwrap();
310
311        assert_eq!(deserialized.total_users, 100);
312        assert_eq!(deserialized.total_volume, Decimal::new(50000, 2));
313    }
314
315    #[test]
316    fn test_growth_metrics_time_periods() {
317        let metrics = GrowthMetrics {
318            users_24h: 10,
319            users_7d: 50,
320            users_30d: 200,
321            tokens_24h: 5,
322            tokens_7d: 25,
323            tokens_30d: 100,
324            trades_24h: 100,
325            trades_7d: 500,
326            trades_30d: 2000,
327            volume_24h: Decimal::new(1000, 2),
328            volume_7d: Decimal::new(5000, 2),
329            volume_30d: Decimal::new(20000, 2),
330        };
331
332        // Growth should typically be increasing over time
333        assert!(metrics.users_7d >= metrics.users_24h);
334        assert!(metrics.users_30d >= metrics.users_7d);
335        assert!(metrics.trades_30d >= metrics.trades_7d);
336    }
337
338    #[test]
339    fn test_health_indicators_percentage_ranges() {
340        let indicators = HealthIndicators {
341            avg_reputation: Decimal::new(7500, 2),
342            commitment_success_rate: Decimal::new(9000, 2),
343            order_completion_rate: Decimal::new(8500, 2),
344            avg_trade_size: Decimal::new(50, 2),
345            active_traders_30d: 150,
346            liquid_tokens: 30,
347        };
348
349        // Success rates should be between 0 and 100
350        assert!(indicators.commitment_success_rate >= Decimal::ZERO);
351        assert!(indicators.commitment_success_rate <= Decimal::new(100, 0));
352        assert!(indicators.order_completion_rate >= Decimal::ZERO);
353        assert!(indicators.order_completion_rate <= Decimal::new(100, 0));
354    }
355}
kaccy_db/platform_stats.rs

kaccy_db/
platform_stats.rs
