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

systemprompt_analytics/repository/
content_analytics.rs
