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            SELECT
58                COUNT(*)::bigint as "total_views!",
59                COUNT(DISTINCT ae.session_id)::bigint as "unique_visitors!",
60                COALESCE(AVG(ee.time_on_page_ms) / 1000.0, 0)::float8 as "avg_time_on_page_seconds",
61                COALESCE(AVG(ee.max_scroll_depth), 0)::float8 as "avg_scroll_depth",
62                COALESCE(SUM(ee.click_count), 0)::bigint as "total_clicks!"
63            FROM analytics_events ae
64            LEFT JOIN engagement_events ee ON ae.session_id = ee.session_id
65            WHERE ae.event_type = 'page_view'
66                AND ae.timestamp >= $1 AND ae.timestamp < $2
67            "#,
68            start,
69            end
70        )
71        .fetch_one(&*self.pool)
72        .await
73        .map_err(Into::into)
74    }
75
76    pub async fn get_content_for_trends(
77        &self,
78        start: DateTime<Utc>,
79        end: DateTime<Utc>,
80    ) -> Result<Vec<ContentTrendRow>> {
81        sqlx::query_as!(
82            ContentTrendRow,
83            r#"
84            SELECT
85                created_at as "timestamp!",
86                total_views as "views!",
87                unique_visitors as "unique_visitors!"
88            FROM content_performance_metrics
89            WHERE created_at >= $1 AND created_at < $2
90            ORDER BY created_at
91            "#,
92            start,
93            end
94        )
95        .fetch_all(&*self.pool)
96        .await
97        .map_err(Into::into)
98    }
99}
systemprompt_analytics/repository/content_analytics.rs

systemprompt_analytics/repository/
content_analytics.rs
