1use std::sync::Arc;
2
3use anyhow::Result;
4use sqlx::PgPool;
5use systemprompt_database::DbPool;
6use systemprompt_identifiers::{ContentId, EngagementEventId, 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<EngagementEventId> {
29 let id = EngagementEventId::generate();
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, tab_switches, 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, $23
45 )
46 "#,
47 id.as_str(),
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.unwrap_or(0),
63 input.optional_metrics.blur_count.unwrap_or(0),
64 input.optional_metrics.tab_switches.unwrap_or(0),
65 input.optional_metrics.visible_time_ms.unwrap_or(0),
66 input.optional_metrics.hidden_time_ms.unwrap_or(0),
67 input.optional_metrics.is_rage_click,
68 input.optional_metrics.is_dead_click,
69 input.optional_metrics.reading_pattern
70 )
71 .execute(&*self.pool)
72 .await?;
73
74 Ok(id)
75 }
76
77 pub async fn find_by_id(&self, id: &EngagementEventId) -> Result<Option<EngagementEvent>> {
78 let event = sqlx::query_as!(
79 EngagementEvent,
80 r#"
81 SELECT
82 id as "id: EngagementEventId", session_id, user_id, page_url,
83 content_id as "content_id: ContentId",
84 time_on_page_ms, time_to_first_interaction_ms, time_to_first_scroll_ms,
85 max_scroll_depth, scroll_velocity_avg, scroll_direction_changes,
86 click_count, mouse_move_distance_px, keyboard_events, copy_events,
87 focus_time_ms as "focus_time_ms!",
88 blur_count as "blur_count!",
89 tab_switches as "tab_switches!",
90 visible_time_ms as "visible_time_ms!",
91 hidden_time_ms as "hidden_time_ms!",
92 is_rage_click, is_dead_click, reading_pattern,
93 created_at, updated_at
94 FROM engagement_events
95 WHERE id = $1
96 "#,
97 id.as_str()
98 )
99 .fetch_optional(&*self.pool)
100 .await?;
101
102 Ok(event)
103 }
104
105 pub async fn list_by_session(&self, session_id: &str) -> Result<Vec<EngagementEvent>> {
106 let events = sqlx::query_as!(
107 EngagementEvent,
108 r#"
109 SELECT
110 id as "id: EngagementEventId", session_id, user_id, page_url,
111 content_id as "content_id: ContentId",
112 time_on_page_ms as "time_on_page_ms!", time_to_first_interaction_ms, time_to_first_scroll_ms,
113 max_scroll_depth as "max_scroll_depth!", scroll_velocity_avg, scroll_direction_changes,
114 click_count as "click_count!", mouse_move_distance_px, keyboard_events, copy_events,
115 focus_time_ms as "focus_time_ms!",
116 blur_count as "blur_count!",
117 tab_switches as "tab_switches!",
118 visible_time_ms as "visible_time_ms!",
119 hidden_time_ms as "hidden_time_ms!",
120 is_rage_click, is_dead_click, reading_pattern,
121 created_at, updated_at
122 FROM engagement_events
123 WHERE session_id = $1
124 ORDER BY created_at ASC
125 "#,
126 session_id
127 )
128 .fetch_all(&*self.pool)
129 .await?;
130
131 Ok(events)
132 }
133
134 pub async fn list_by_user(&self, user_id: &str, limit: i64) -> Result<Vec<EngagementEvent>> {
135 let events = sqlx::query_as!(
136 EngagementEvent,
137 r#"
138 SELECT
139 id as "id: EngagementEventId", session_id, user_id, page_url,
140 content_id as "content_id: ContentId",
141 time_on_page_ms as "time_on_page_ms!", time_to_first_interaction_ms, time_to_first_scroll_ms,
142 max_scroll_depth as "max_scroll_depth!", scroll_velocity_avg, scroll_direction_changes,
143 click_count as "click_count!", mouse_move_distance_px, keyboard_events, copy_events,
144 focus_time_ms as "focus_time_ms!",
145 blur_count as "blur_count!",
146 tab_switches as "tab_switches!",
147 visible_time_ms as "visible_time_ms!",
148 hidden_time_ms as "hidden_time_ms!",
149 is_rage_click, is_dead_click, reading_pattern,
150 created_at, updated_at
151 FROM engagement_events
152 WHERE user_id = $1
153 ORDER BY created_at DESC
154 LIMIT $2
155 "#,
156 user_id,
157 limit
158 )
159 .fetch_all(&*self.pool)
160 .await?;
161
162 Ok(events)
163 }
164
165 pub async fn get_session_engagement_summary(
166 &self,
167 session_id: &str,
168 ) -> Result<Option<SessionEngagementSummary>> {
169 let summary = sqlx::query_as!(
170 SessionEngagementSummary,
171 r#"
172 SELECT
173 session_id,
174 COUNT(*)::BIGINT as page_count,
175 SUM(time_on_page_ms)::BIGINT as total_time_on_page_ms,
176 AVG(max_scroll_depth)::REAL as avg_scroll_depth,
177 MAX(max_scroll_depth) as max_scroll_depth,
178 SUM(click_count)::BIGINT as total_clicks,
179 COUNT(*) FILTER (WHERE is_rage_click = true)::BIGINT as rage_click_pages,
180 MIN(created_at) as first_engagement,
181 MAX(created_at) as last_engagement
182 FROM engagement_events
183 WHERE session_id = $1
184 GROUP BY session_id
185 "#,
186 session_id
187 )
188 .fetch_optional(&*self.pool)
189 .await?;
190
191 Ok(summary)
192 }
193}
194
195#[derive(Debug, Clone, sqlx::FromRow)]
196pub struct SessionEngagementSummary {
197 pub session_id: SessionId,
198 pub page_count: Option<i64>,
199 pub total_time_on_page_ms: Option<i64>,
200 pub avg_scroll_depth: Option<f32>,
201 pub max_scroll_depth: Option<i32>,
202 pub total_clicks: Option<i64>,
203 pub rage_click_pages: Option<i64>,
204 pub first_engagement: Option<chrono::DateTime<chrono::Utc>>,
205 pub last_engagement: Option<chrono::DateTime<chrono::Utc>>,
206}