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