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 systemprompt_identifiers::{ContentId, SourceId};
8
9use crate::models::cli::{ContentStatsRow, ContentTrendRow, TopContentRow};
10
11#[derive(Debug)]
12pub struct ContentAnalyticsRepository {
13    pool: Arc<PgPool>,
14}
15
16impl ContentAnalyticsRepository {
17    pub fn new(db: &DbPool) -> Result<Self> {
18        let pool = db.pool_arc()?;
19        Ok(Self { pool })
20    }
21
22    pub async fn get_top_content(
23        &self,
24        start: DateTime<Utc>,
25        end: DateTime<Utc>,
26        limit: i64,
27    ) -> Result<Vec<TopContentRow>> {
28        sqlx::query_as!(
29            TopContentRow,
30            r#"
31            WITH content_stats AS (
32                SELECT
33                    ee.content_id,
34                    COUNT(*)::bigint as total_views,
35                    COUNT(DISTINCT ee.session_id)::bigint as unique_visitors,
36                    (AVG(LEAST(ee.time_on_page_ms, 1800000)) / 1000.0)::float8 as avg_time_on_page_seconds
37                FROM engagement_events ee
38                INNER JOIN user_sessions us ON ee.session_id = us.session_id
39                WHERE ee.created_at >= $1 AND ee.created_at < $2
40                    AND ee.content_id IS NOT NULL
41                    AND us.is_bot = false AND us.is_behavioral_bot = false
42                GROUP BY ee.content_id
43            )
44            SELECT
45                cs.content_id as "content_id!: ContentId",
46                mc.slug as "slug?",
47                mc.title as "title?",
48                mc.source_id as "source_id?: SourceId",
49                cs.total_views as "total_views!",
50                cs.unique_visitors as "unique_visitors!",
51                cs.avg_time_on_page_seconds::float8 as "avg_time_on_page_seconds",
52                NULL::text as "trend_direction"
53            FROM content_stats cs
54            LEFT JOIN markdown_content mc ON cs.content_id = mc.id
55            ORDER BY cs.total_views DESC
56            LIMIT $3
57            "#,
58            start,
59            end,
60            limit
61        )
62        .fetch_all(&*self.pool)
63        .await
64        .map_err(Into::into)
65    }
66
67    pub async fn get_stats(
68        &self,
69        start: DateTime<Utc>,
70        end: DateTime<Utc>,
71    ) -> Result<ContentStatsRow> {
72        sqlx::query_as!(
73            ContentStatsRow,
74            r#"
75            SELECT
76                COUNT(*)::bigint as "total_views!",
77                COUNT(DISTINCT ee.session_id)::bigint as "unique_visitors!",
78                COALESCE(AVG(LEAST(ee.time_on_page_ms, 1800000)) / 1000.0, 0)::float8 as "avg_time_on_page_seconds",
79                COALESCE(AVG(ee.max_scroll_depth), 0)::float8 as "avg_scroll_depth",
80                COALESCE(SUM(ee.click_count), 0)::bigint as "total_clicks!"
81            FROM engagement_events ee
82            INNER JOIN user_sessions us ON ee.session_id = us.session_id
83            WHERE ee.created_at >= $1 AND ee.created_at < $2
84                AND us.is_bot = false AND us.is_behavioral_bot = false
85            "#,
86            start,
87            end
88        )
89        .fetch_one(&*self.pool)
90        .await
91        .map_err(Into::into)
92    }
93
94    pub async fn get_content_for_trends(
95        &self,
96        start: DateTime<Utc>,
97        end: DateTime<Utc>,
98    ) -> Result<Vec<ContentTrendRow>> {
99        sqlx::query_as!(
100            ContentTrendRow,
101            r#"
102            WITH date_series AS (
103                SELECT generate_series(
104                    date_trunc('day', $1::timestamptz),
105                    date_trunc('day', $2::timestamptz) - interval '1 day',
106                    '1 day'::interval
107                ) as day
108            ),
109            daily_stats AS (
110                SELECT
111                    date_trunc('day', ee.created_at) as day,
112                    COUNT(*)::bigint as views,
113                    COUNT(DISTINCT ee.session_id)::bigint as unique_visitors
114                FROM engagement_events ee
115                INNER JOIN user_sessions us ON ee.session_id = us.session_id
116                WHERE ee.created_at >= $1 AND ee.created_at < $2
117                    AND us.is_bot = false AND us.is_behavioral_bot = false
118                GROUP BY date_trunc('day', ee.created_at)
119            )
120            SELECT
121                ds.day as "timestamp!",
122                COALESCE(s.views, 0)::bigint as "views!",
123                COALESCE(s.unique_visitors, 0)::bigint as "unique_visitors!"
124            FROM date_series ds
125            LEFT JOIN daily_stats s ON ds.day = s.day
126            ORDER BY ds.day
127            "#,
128            start,
129            end
130        )
131        .fetch_all(&*self.pool)
132        .await
133        .map_err(Into::into)
134    }
135}
systemprompt_analytics/repository/content_analytics.rs

systemprompt_analytics/repository/
content_analytics.rs
