tuitbot_core/storage/analytics/
best_times.rs

1//! Best-time-to-post analytics: ranked time slots by historical engagement.
2
3use super::super::accounts::DEFAULT_ACCOUNT_ID;
4use super::super::DbPool;
5use crate::error::StorageError;
6use serde::{Deserialize, Serialize};
7
8/// A recommended posting time slot with engagement metrics.
9#[derive(Debug, Clone, Serialize, Deserialize)]
10pub struct BestTimeSlot {
11    pub hour: i32,
12    pub day_of_week: i32,
13    pub day_name: String,
14    pub avg_engagement: f64,
15    pub confidence_score: f64, // 0-100, higher = more historical data
16    pub sample_size: i64,
17}
18
19/// Get ranked best-time-to-post slots for a specific account (sorted by avg_engagement DESC).
20pub async fn get_best_times_for(
21    pool: &DbPool,
22    account_id: &str,
23) -> Result<Vec<BestTimeSlot>, StorageError> {
24    let day_names = [
25        "Sunday",
26        "Monday",
27        "Tuesday",
28        "Wednesday",
29        "Thursday",
30        "Friday",
31        "Saturday",
32    ];
33
34    let rows = sqlx::query_as::<_, (i32, i32, f64, f64, i64)>(
35        "SELECT hour_of_day, day_of_week, avg_engagement, confidence_score, sample_size \
36         FROM best_times \
37         WHERE account_id = ? \
38         ORDER BY avg_engagement DESC",
39    )
40    .bind(account_id)
41    .fetch_all(pool)
42    .await
43    .map_err(|e| StorageError::Query { source: e })?;
44
45    Ok(rows
46        .into_iter()
47        .map(
48            |(hour, day, avg_engagement, confidence, sample_size)| BestTimeSlot {
49                hour,
50                day_of_week: day,
51                day_name: day_names[day as usize % 7].to_string(),
52                avg_engagement,
53                confidence_score: confidence,
54                sample_size,
55            },
56        )
57        .collect())
58}
59
60/// Get ranked best-time-to-post slots (default account).
61pub async fn get_best_times(pool: &DbPool) -> Result<Vec<BestTimeSlot>, StorageError> {
62    get_best_times_for(pool, DEFAULT_ACCOUNT_ID).await
63}
64
65/// Compute and update best-times aggregations for a specific account.
66/// Call daily via background job.
67pub async fn aggregate_best_times_for(pool: &DbPool, account_id: &str) -> Result<(), StorageError> {
68    // Query engagement metrics grouped by hour and day-of-week
69    let rows = sqlx::query_as::<_, (i32, i32, f64, i64)>(
70        "SELECT \
71           CAST(STRFTIME('%H', posted_at) AS INTEGER) as hour, \
72           (CAST(STRFTIME('%w', posted_at) AS INTEGER) + 6) % 7 as day_of_week, \
73           AVG(engagement_rate) as avg_engagement, \
74           COUNT(*) as sample_size \
75         FROM engagement_metrics \
76         WHERE account_id = ? AND posted_at IS NOT NULL \
77         GROUP BY hour, day_of_week",
78    )
79    .bind(account_id)
80    .fetch_all(pool)
81    .await
82    .map_err(|e| StorageError::Query { source: e })?;
83
84    // Compute confidence score based on sample size
85    // Assuming 5+ samples = high confidence (90+), 2-4 = medium (50-80), 0-1 = low (0-40)
86    let now = chrono::Utc::now().format("%Y-%m-%dT%H:%M:%SZ").to_string();
87
88    for (hour, day_of_week, avg_engagement, sample_size) in rows {
89        let confidence_score = if sample_size >= 5 {
90            90.0 + (sample_size as f64 - 5.0).min(10.0)
91        } else if sample_size >= 2 {
92            50.0 + (sample_size as f64 - 2.0) * 15.0
93        } else {
94            (sample_size as f64) * 20.0
95        };
96
97        sqlx::query(
98            "INSERT INTO best_times \
99             (account_id, hour_of_day, day_of_week, avg_engagement, confidence_score, sample_size, last_updated) \
100             VALUES (?, ?, ?, ?, ?, ?, ?) \
101             ON CONFLICT(account_id, hour_of_day, day_of_week) DO UPDATE SET \
102             avg_engagement = excluded.avg_engagement, \
103             confidence_score = excluded.confidence_score, \
104             sample_size = excluded.sample_size, \
105             last_updated = excluded.last_updated",
106        )
107        .bind(account_id)
108        .bind(hour)
109        .bind(day_of_week)
110        .bind(avg_engagement)
111        .bind(confidence_score)
112        .bind(sample_size)
113        .bind(&now)
114        .execute(pool)
115        .await
116        .map_err(|e| StorageError::Query { source: e })?;
117    }
118
119    Ok(())
120}
121
122/// Follower growth time-series: daily deltas and weekly deltas.
123#[derive(Debug, Clone, Serialize, Deserialize)]
124pub struct FollowerGrowthSnapshot {
125    pub date: String,
126    pub follower_count: i64,
127    pub daily_delta: i64,
128    pub weekly_delta: i64,
129}
130
131/// Get follower growth time-series for a specific account over the past N days.
132pub async fn get_follower_growth_for(
133    pool: &DbPool,
134    account_id: &str,
135    days: u32,
136) -> Result<Vec<FollowerGrowthSnapshot>, StorageError> {
137    // Query follower_snapshots, compute deltas
138    let rows = sqlx::query_as::<_, (String, i64)>(
139        "SELECT snapshot_date, follower_count \
140         FROM follower_snapshots \
141         WHERE account_id = ? \
142         AND snapshot_date >= date('now', '-' || ? || ' days') \
143         ORDER BY snapshot_date ASC",
144    )
145    .bind(account_id)
146    .bind(days as i64)
147    .fetch_all(pool)
148    .await
149    .map_err(|e| StorageError::Query { source: e })?;
150
151    let mut result = Vec::new();
152    for (i, (date, follower_count)) in rows.iter().enumerate() {
153        let daily_delta = if i > 0 {
154            follower_count - rows[i - 1].1
155        } else {
156            0
157        };
158
159        let weekly_delta = if i >= 7 {
160            follower_count - rows[i - 7].1
161        } else {
162            0
163        };
164
165        result.push(FollowerGrowthSnapshot {
166            date: date.clone(),
167            follower_count: *follower_count,
168            daily_delta,
169            weekly_delta,
170        });
171    }
172
173    Ok(result)
174}
175
176/// Get follower growth time-series (default account).
177pub async fn get_follower_growth(
178    pool: &DbPool,
179    days: u32,
180) -> Result<Vec<FollowerGrowthSnapshot>, StorageError> {
181    get_follower_growth_for(pool, DEFAULT_ACCOUNT_ID, days).await
182}
183
184#[cfg(test)]
185mod tests {
186
187    #[test]
188    fn confidence_score_high() {
189        let sample_size = 10i64;
190        let confidence = if sample_size >= 5 {
191            90.0 + (sample_size as f64 - 5.0).min(10.0)
192        } else {
193            0.0
194        };
195        assert!(confidence >= 95.0);
196    }
197
198    #[test]
199    fn confidence_score_medium() {
200        let sample_size = 3i64;
201        let confidence = if sample_size >= 2 {
202            50.0 + (sample_size as f64 - 2.0) * 15.0
203        } else {
204            0.0
205        };
206        assert_eq!(confidence, 65.0);
207    }
208
209    #[test]
210    fn confidence_score_low() {
211        let sample_size = 1i64;
212        let confidence = (sample_size as f64) * 20.0;
213        assert_eq!(confidence, 20.0);
214    }
215
216    #[test]
217    fn follower_delta_calculation() {
218        let prev_count = 1000i64;
219        let curr_count = 1050i64;
220        let daily_delta = curr_count - prev_count;
221        assert_eq!(daily_delta, 50);
222    }
223
224    #[test]
225    fn day_name_mapping() {
226        let day_names = [
227            "Sunday",
228            "Monday",
229            "Tuesday",
230            "Wednesday",
231            "Thursday",
232            "Friday",
233            "Saturday",
234        ];
235        assert_eq!(day_names[0], "Sunday");
236        assert_eq!(day_names[6], "Saturday");
237    }
238}
tuitbot_core/storage/analytics/best_times.rs

tuitbot_core/storage/analytics/
best_times.rs
