1use std::sync::Arc;
2
3use anyhow::Result;
4use sqlx::PgPool;
5use systemprompt_database::DbPool;
6use systemprompt_identifiers::{ContentId, SessionId};
7
8use crate::models::{CreateEngagementEventInput, EngagementEvent};
9
10#[derive(Clone, Debug)]
11pub struct EngagementRepository {
12 pool: Arc<PgPool>,
13}
14
15impl EngagementRepository {
16 pub fn new(db: &DbPool) -> Result<Self> {
17 let pool = db.pool_arc()?;
18 Ok(Self { pool })
19 }
20
21 #[allow(clippy::cognitive_complexity)]
22 pub async fn create_engagement(
23 &self,
24 session_id: &str,
25 user_id: &str,
26 content_id: Option<&ContentId>,
27 input: &CreateEngagementEventInput,
28 ) -> Result<String> {
29 let id = format!("eng_{}", uuid::Uuid::new_v4());
30
31 sqlx::query!(
32 r#"
33 INSERT INTO engagement_events (
34 id, session_id, user_id, page_url, content_id,
35 time_on_page_ms, max_scroll_depth, click_count,
36 time_to_first_interaction_ms, time_to_first_scroll_ms,
37 scroll_velocity_avg, scroll_direction_changes,
38 mouse_move_distance_px, keyboard_events, copy_events,
39 focus_time_ms, blur_count, visible_time_ms, hidden_time_ms,
40 is_rage_click, is_dead_click, reading_pattern
41 )
42 VALUES (
43 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12,
44 $13, $14, $15, $16, $17, $18, $19, $20, $21, $22
45 )
46 "#,
47 id,
48 session_id,
49 user_id,
50 input.page_url,
51 content_id.map(ContentId::as_str),
52 input.time_on_page_ms,
53 input.max_scroll_depth,
54 input.click_count,
55 input.optional_metrics.time_to_first_interaction_ms,
56 input.optional_metrics.time_to_first_scroll_ms,
57 input.optional_metrics.scroll_velocity_avg,
58 input.optional_metrics.scroll_direction_changes,
59 input.optional_metrics.mouse_move_distance_px,
60 input.optional_metrics.keyboard_events,
61 input.optional_metrics.copy_events,
62 input.optional_metrics.focus_time_ms,
63 input.optional_metrics.blur_count,
64 input.optional_metrics.visible_time_ms,
65 input.optional_metrics.hidden_time_ms,
66 input.optional_metrics.is_rage_click,
67 input.optional_metrics.is_dead_click,
68 input.optional_metrics.reading_pattern
69 )
70 .execute(&*self.pool)
71 .await?;
72
73 Ok(id)
74 }
75
76 pub async fn find_by_id(&self, id: &str) -> Result<Option<EngagementEvent>> {
77 let event = sqlx::query_as!(
78 EngagementEvent,
79 r#"
80 SELECT
81 id, session_id, user_id, page_url,
82 content_id as "content_id: ContentId",
83 time_on_page_ms, time_to_first_interaction_ms, time_to_first_scroll_ms,
84 max_scroll_depth, scroll_velocity_avg, scroll_direction_changes,
85 click_count, mouse_move_distance_px, keyboard_events, copy_events,
86 focus_time_ms as "focus_time_ms!",
87 blur_count as "blur_count!",
88 tab_switches as "tab_switches!",
89 visible_time_ms as "visible_time_ms!",
90 hidden_time_ms as "hidden_time_ms!",
91 is_rage_click, is_dead_click, reading_pattern,
92 created_at, updated_at
93 FROM engagement_events
94 WHERE id = $1
95 "#,
96 id
97 )
98 .fetch_optional(&*self.pool)
99 .await?;
100
101 Ok(event)
102 }
103
104 pub async fn list_by_session(&self, session_id: &str) -> Result<Vec<EngagementEvent>> {
105 let events = sqlx::query_as!(
106 EngagementEvent,
107 r#"
108 SELECT
109 id, session_id, user_id, page_url,
110 content_id as "content_id: ContentId",
111 time_on_page_ms as "time_on_page_ms!", time_to_first_interaction_ms, time_to_first_scroll_ms,
112 max_scroll_depth as "max_scroll_depth!", scroll_velocity_avg, scroll_direction_changes,
113 click_count as "click_count!", mouse_move_distance_px, keyboard_events, copy_events,
114 focus_time_ms as "focus_time_ms!",
115 blur_count as "blur_count!",
116 tab_switches as "tab_switches!",
117 visible_time_ms as "visible_time_ms!",
118 hidden_time_ms as "hidden_time_ms!",
119 is_rage_click, is_dead_click, reading_pattern,
120 created_at, updated_at
121 FROM engagement_events
122 WHERE session_id = $1
123 ORDER BY created_at ASC
124 "#,
125 session_id
126 )
127 .fetch_all(&*self.pool)
128 .await?;
129
130 Ok(events)
131 }
132
133 pub async fn list_by_user(&self, user_id: &str, limit: i64) -> Result<Vec<EngagementEvent>> {
134 let events = sqlx::query_as!(
135 EngagementEvent,
136 r#"
137 SELECT
138 id, session_id, user_id, page_url,
139 content_id as "content_id: ContentId",
140 time_on_page_ms as "time_on_page_ms!", time_to_first_interaction_ms, time_to_first_scroll_ms,
141 max_scroll_depth as "max_scroll_depth!", scroll_velocity_avg, scroll_direction_changes,
142 click_count as "click_count!", mouse_move_distance_px, keyboard_events, copy_events,
143 focus_time_ms as "focus_time_ms!",
144 blur_count as "blur_count!",
145 tab_switches as "tab_switches!",
146 visible_time_ms as "visible_time_ms!",
147 hidden_time_ms as "hidden_time_ms!",
148 is_rage_click, is_dead_click, reading_pattern,
149 created_at, updated_at
150 FROM engagement_events
151 WHERE user_id = $1
152 ORDER BY created_at DESC
153 LIMIT $2
154 "#,
155 user_id,
156 limit
157 )
158 .fetch_all(&*self.pool)
159 .await?;
160
161 Ok(events)
162 }
163
164 pub async fn get_session_engagement_summary(
165 &self,
166 session_id: &str,
167 ) -> Result<Option<SessionEngagementSummary>> {
168 let summary = sqlx::query_as!(
169 SessionEngagementSummary,
170 r#"
171 SELECT
172 session_id,
173 COUNT(*)::BIGINT as page_count,
174 SUM(time_on_page_ms)::BIGINT as total_time_on_page_ms,
175 AVG(max_scroll_depth)::REAL as avg_scroll_depth,
176 MAX(max_scroll_depth) as max_scroll_depth,
177 SUM(click_count)::BIGINT as total_clicks,
178 COUNT(*) FILTER (WHERE is_rage_click = true)::BIGINT as rage_click_pages,
179 MIN(created_at) as first_engagement,
180 MAX(created_at) as last_engagement
181 FROM engagement_events
182 WHERE session_id = $1
183 GROUP BY session_id
184 "#,
185 session_id
186 )
187 .fetch_optional(&*self.pool)
188 .await?;
189
190 Ok(summary)
191 }
192}
193
194#[derive(Debug, Clone, sqlx::FromRow)]
195pub struct SessionEngagementSummary {
196 pub session_id: SessionId,
197 pub page_count: Option<i64>,
198 pub total_time_on_page_ms: Option<i64>,
199 pub avg_scroll_depth: Option<f32>,
200 pub max_scroll_depth: Option<i32>,
201 pub total_clicks: Option<i64>,
202 pub rage_click_pages: Option<i64>,
203 pub first_engagement: Option<chrono::DateTime<chrono::Utc>>,
204 pub last_engagement: Option<chrono::DateTime<chrono::Utc>>,
205}