systemprompt_analytics/repository/
content_analytics.rs

1use anyhow::Result;
2use chrono::{DateTime, Utc};
3use sqlx::PgPool;
4use std::sync::Arc;
5use systemprompt_database::DbPool;
6
7use crate::models::cli::{ContentStatsRow, ContentTrendRow, TopContentRow};
8
9#[derive(Debug)]
10pub struct ContentAnalyticsRepository {
11    pool: Arc<PgPool>,
12}
13
14impl ContentAnalyticsRepository {
15    pub fn new(db: &DbPool) -> Result<Self> {
16        let pool = db.pool_arc()?;
17        Ok(Self { pool })
18    }
19
20    pub async fn get_top_content(
21        &self,
22        start: DateTime<Utc>,
23        end: DateTime<Utc>,
24        limit: i64,
25    ) -> Result<Vec<TopContentRow>> {
26        sqlx::query_as!(
27            TopContentRow,
28            r#"
29            SELECT
30                content_id as "content_id!",
31                total_views as "total_views!",
32                unique_visitors as "unique_visitors!",
33                avg_time_on_page_seconds as "avg_time_on_page_seconds",
34                trend_direction as "trend_direction"
35            FROM content_performance_metrics
36            WHERE created_at >= $1 AND created_at < $2
37            ORDER BY total_views DESC
38            LIMIT $3
39            "#,
40            start,
41            end,
42            limit
43        )
44        .fetch_all(&*self.pool)
45        .await
46        .map_err(Into::into)
47    }
48
49    pub async fn get_stats(
50        &self,
51        start: DateTime<Utc>,
52        end: DateTime<Utc>,
53    ) -> Result<ContentStatsRow> {
54        sqlx::query_as!(
55            ContentStatsRow,
56            r#"
57            WITH page_view_stats AS (
58                SELECT
59                    COUNT(*) as total_views,
60                    COUNT(DISTINCT user_id) as unique_visitors
61                FROM analytics_events
62                WHERE event_type = 'page_view'
63                    AND timestamp >= $1 AND timestamp < $2
64            ),
65            engagement_stats AS (
66                SELECT
67                    COALESCE(AVG(time_on_page_ms) / 1000.0, 0) as avg_time_on_page_seconds,
68                    COALESCE(AVG(max_scroll_depth), 0) as avg_scroll_depth,
69                    COALESCE(SUM(click_count), 0) as total_clicks
70                FROM engagement_events
71                WHERE created_at >= $1 AND created_at < $2
72            )
73            SELECT
74                pv.total_views::bigint as "total_views!",
75                pv.unique_visitors::bigint as "unique_visitors!",
76                es.avg_time_on_page_seconds::float8 as "avg_time_on_page_seconds",
77                es.avg_scroll_depth::float8 as "avg_scroll_depth",
78                es.total_clicks::bigint as "total_clicks!"
79            FROM page_view_stats pv, engagement_stats es
80            "#,
81            start,
82            end
83        )
84        .fetch_one(&*self.pool)
85        .await
86        .map_err(Into::into)
87    }
88
89    pub async fn get_content_for_trends(
90        &self,
91        start: DateTime<Utc>,
92        end: DateTime<Utc>,
93    ) -> Result<Vec<ContentTrendRow>> {
94        sqlx::query_as!(
95            ContentTrendRow,
96            r#"
97            SELECT
98                created_at as "timestamp!",
99                total_views as "views!",
100                unique_visitors as "unique_visitors!"
101            FROM content_performance_metrics
102            WHERE created_at >= $1 AND created_at < $2
103            ORDER BY created_at
104            "#,
105            start,
106            end
107        )
108        .fetch_all(&*self.pool)
109        .await
110        .map_err(Into::into)
111    }
112}
systemprompt_analytics/repository/content_analytics.rs

systemprompt_analytics/repository/
content_analytics.rs
