tuitbot_core/storage/analytics/
engagement.rs

1//! Engagement rate and reach analytics queries.
2
3use super::super::accounts::DEFAULT_ACCOUNT_ID;
4use super::super::DbPool;
5use crate::error::StorageError;
6use chrono::Utc;
7use serde::{Deserialize, Serialize};
8
9/// Engagement metrics for a single post.
10#[derive(Debug, Clone, Serialize, Deserialize)]
11pub struct EngagementMetric {
12    pub post_id: String,
13    pub text: Option<String>,
14    pub engagement_rate: f64,
15    pub impressions: i64,
16    pub likes: i64,
17    pub retweets: i64,
18    pub replies: i64,
19    pub bookmarks: i64,
20    pub posted_at: Option<String>,
21}
22
23/// Get top engagement posts for a specific account (sorted by engagement_rate DESC).
24pub async fn get_engagement_rate_for(
25    pool: &DbPool,
26    account_id: &str,
27    limit: u32,
28) -> Result<Vec<EngagementMetric>, StorageError> {
29    let rows = sqlx::query_as::<_, (String, Option<String>, f64, i64, i64, i64, i64, i64, Option<String>)>(
30        "SELECT post_id, NULL as text, engagement_rate, impressions, likes, retweets, replies, bookmarks, posted_at \
31         FROM engagement_metrics \
32         WHERE account_id = ? \
33         ORDER BY engagement_rate DESC \
34         LIMIT ?",
35    )
36    .bind(account_id)
37    .bind(limit as i64)
38    .fetch_all(pool)
39    .await
40    .map_err(|e| StorageError::Query { source: e })?;
41
42    Ok(rows
43        .into_iter()
44        .map(
45            |(
46                post_id,
47                text,
48                engagement_rate,
49                impressions,
50                likes,
51                retweets,
52                replies,
53                bookmarks,
54                posted_at,
55            )| {
56                EngagementMetric {
57                    post_id,
58                    text,
59                    engagement_rate,
60                    impressions,
61                    likes,
62                    retweets,
63                    replies,
64                    bookmarks,
65                    posted_at,
66                }
67            },
68        )
69        .collect())
70}
71
72/// Get top engagement posts (default account).
73pub async fn get_engagement_rate(
74    pool: &DbPool,
75    limit: u32,
76) -> Result<Vec<EngagementMetric>, StorageError> {
77    get_engagement_rate_for(pool, DEFAULT_ACCOUNT_ID, limit).await
78}
79
80/// Reach snapshot for a single day.
81#[derive(Debug, Clone, Serialize, Deserialize)]
82pub struct ReachSnapshot {
83    pub date: String,
84    pub total_reach: i64,
85    pub avg_reach_per_post: f64,
86    pub post_count: i64,
87}
88
89/// Get reach time-series for a specific account over the past N days.
90pub async fn get_reach_for(
91    pool: &DbPool,
92    account_id: &str,
93    days: u32,
94) -> Result<Vec<ReachSnapshot>, StorageError> {
95    let rows = sqlx::query_as::<_, (String, i64, f64, i64)>(
96        "SELECT snapshot_date, total_reach, avg_reach_per_post, post_count \
97         FROM reach_snapshots \
98         WHERE account_id = ? \
99         AND snapshot_date >= date('now', '-' || ? || ' days') \
100         ORDER BY snapshot_date ASC",
101    )
102    .bind(account_id)
103    .bind(days as i64)
104    .fetch_all(pool)
105    .await
106    .map_err(|e| StorageError::Query { source: e })?;
107
108    Ok(rows
109        .into_iter()
110        .map(
111            |(date, total_reach, avg_reach_per_post, post_count)| ReachSnapshot {
112                date,
113                total_reach,
114                avg_reach_per_post,
115                post_count,
116            },
117        )
118        .collect())
119}
120
121/// Get reach time-series (default account).
122pub async fn get_reach(pool: &DbPool, days: u32) -> Result<Vec<ReachSnapshot>, StorageError> {
123    get_reach_for(pool, DEFAULT_ACCOUNT_ID, days).await
124}
125
126/// Insert or update engagement metrics for a post.
127/// Input for upserting engagement metrics for a single post.
128pub struct UpsertEngagementInput<'a> {
129    pub post_id: &'a str,
130    pub impressions: i64,
131    pub likes: i64,
132    pub retweets: i64,
133    pub replies: i64,
134    pub bookmarks: i64,
135    pub posted_at: Option<&'a str>,
136}
137
138pub async fn upsert_engagement_metric_for(
139    pool: &DbPool,
140    account_id: &str,
141    input: UpsertEngagementInput<'_>,
142) -> Result<(), StorageError> {
143    let engagement_rate = if input.impressions > 0 {
144        (input.likes + input.retweets + input.replies + input.bookmarks) as f64
145            / input.impressions as f64
146    } else {
147        0.0
148    };
149
150    let now = Utc::now().format("%Y-%m-%dT%H:%M:%SZ").to_string();
151
152    sqlx::query(
153        "INSERT INTO engagement_metrics \
154         (account_id, post_id, impressions, likes, retweets, replies, bookmarks, engagement_rate, posted_at, fetched_at, created_at) \
155         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \
156         ON CONFLICT(account_id, post_id) DO UPDATE SET \
157         impressions = excluded.impressions, \
158         likes = excluded.likes, \
159         retweets = excluded.retweets, \
160         replies = excluded.replies, \
161         bookmarks = excluded.bookmarks, \
162         engagement_rate = excluded.engagement_rate, \
163         fetched_at = excluded.fetched_at",
164    )
165    .bind(account_id)
166    .bind(input.post_id)
167    .bind(input.impressions)
168    .bind(input.likes)
169    .bind(input.retweets)
170    .bind(input.replies)
171    .bind(input.bookmarks)
172    .bind(engagement_rate)
173    .bind(input.posted_at)
174    .bind(&now)
175    .bind(&now)
176    .execute(pool)
177    .await
178    .map_err(|e| StorageError::Query { source: e })?;
179
180    Ok(())
181}
182
183/// Compute and store reach aggregations for today (call daily via background job).
184pub async fn aggregate_reach_for(pool: &DbPool, account_id: &str) -> Result<(), StorageError> {
185    let today = Utc::now().format("%Y-%m-%d").to_string();
186
187    // Query engagement metrics for today, compute aggregates
188    let row = sqlx::query_as::<_, (i64, i64)>(
189        "SELECT COALESCE(SUM(impressions), 0) as total, COUNT(*) as count \
190         FROM engagement_metrics \
191         WHERE account_id = ? AND DATE(posted_at) = ?",
192    )
193    .bind(account_id)
194    .bind(&today)
195    .fetch_optional(pool)
196    .await
197    .map_err(|e| StorageError::Query { source: e })?;
198
199    let (total_reach, post_count) = match row {
200        Some((t, c)) => (t, c),
201        None => (0, 0),
202    };
203
204    let avg_reach_per_post = if post_count > 0 {
205        total_reach as f64 / post_count as f64
206    } else {
207        0.0
208    };
209
210    let now = Utc::now().format("%Y-%m-%dT%H:%M:%SZ").to_string();
211
212    sqlx::query(
213        "INSERT INTO reach_snapshots \
214         (account_id, snapshot_date, total_reach, avg_reach_per_post, post_count, created_at) \
215         VALUES (?, ?, ?, ?, ?, ?) \
216         ON CONFLICT(account_id, snapshot_date) DO UPDATE SET \
217         total_reach = excluded.total_reach, \
218         avg_reach_per_post = excluded.avg_reach_per_post, \
219         post_count = excluded.post_count",
220    )
221    .bind(account_id)
222    .bind(&today)
223    .bind(total_reach)
224    .bind(avg_reach_per_post)
225    .bind(post_count)
226    .bind(&now)
227    .execute(pool)
228    .await
229    .map_err(|e| StorageError::Query { source: e })?;
230
231    Ok(())
232}
233
234#[cfg(test)]
235mod tests {
236    #[test]
237    fn engagement_rate_calculation() {
238        let likes = 10;
239        let retweets = 5;
240        let replies = 3;
241        let bookmarks = 2;
242        let impressions = 100;
243
244        let engagement_rate = (likes + retweets + replies + bookmarks) as f64 / impressions as f64;
245        assert!((engagement_rate - 0.2).abs() < 0.001);
246    }
247
248    #[test]
249    fn engagement_rate_zero_impressions() {
250        let engagement_rate = if 0 > 0 { 1.0 } else { 0.0 };
251        assert_eq!(engagement_rate, 0.0);
252    }
253
254    #[test]
255    fn reach_per_post_calculation() {
256        let total_reach = 1000;
257        let post_count = 5;
258        let avg = total_reach as f64 / post_count as f64;
259        assert_eq!(avg, 200.0);
260    }
261
262    #[test]
263    fn reach_per_post_zero_posts() {
264        let avg = if 0 > 0 { 1.0 } else { 0.0 };
265        assert_eq!(avg, 0.0);
266    }
267}
tuitbot_core/storage/analytics/engagement.rs

tuitbot_core/storage/analytics/
engagement.rs
