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