tuitbot_core/storage/analytics/
snapshots.rs

1use super::super::accounts::DEFAULT_ACCOUNT_ID;
2use super::super::DbPool;
3use crate::error::StorageError;
4
5/// A daily follower snapshot.
6#[derive(Debug, Clone, serde::Serialize)]
7pub struct FollowerSnapshot {
8    pub snapshot_date: String,
9    pub follower_count: i64,
10    pub following_count: i64,
11    pub tweet_count: i64,
12}
13
14/// Upsert a follower snapshot for today for a specific account.
15pub async fn upsert_follower_snapshot_for(
16    pool: &DbPool,
17    account_id: &str,
18    follower_count: i64,
19    following_count: i64,
20    tweet_count: i64,
21) -> Result<(), StorageError> {
22    sqlx::query(
23        "INSERT INTO follower_snapshots (account_id, snapshot_date, follower_count, following_count, tweet_count) \
24         VALUES (?, date('now'), ?, ?, ?) \
25         ON CONFLICT(snapshot_date) DO UPDATE SET \
26         account_id = excluded.account_id, \
27         follower_count = excluded.follower_count, \
28         following_count = excluded.following_count, \
29         tweet_count = excluded.tweet_count",
30    )
31    .bind(account_id)
32    .bind(follower_count)
33    .bind(following_count)
34    .bind(tweet_count)
35    .execute(pool)
36    .await
37    .map_err(|e| StorageError::Query { source: e })?;
38    Ok(())
39}
40
41/// Upsert a follower snapshot for today.
42pub async fn upsert_follower_snapshot(
43    pool: &DbPool,
44    follower_count: i64,
45    following_count: i64,
46    tweet_count: i64,
47) -> Result<(), StorageError> {
48    upsert_follower_snapshot_for(
49        pool,
50        DEFAULT_ACCOUNT_ID,
51        follower_count,
52        following_count,
53        tweet_count,
54    )
55    .await
56}
57
58/// Get recent follower snapshots for a specific account, ordered by date descending.
59pub async fn get_follower_snapshots_for(
60    pool: &DbPool,
61    account_id: &str,
62    limit: u32,
63) -> Result<Vec<FollowerSnapshot>, StorageError> {
64    let rows: Vec<(String, i64, i64, i64)> = sqlx::query_as(
65        "SELECT snapshot_date, follower_count, following_count, tweet_count \
66         FROM follower_snapshots \
67         WHERE account_id = ? \
68         ORDER BY snapshot_date DESC \
69         LIMIT ?",
70    )
71    .bind(account_id)
72    .bind(limit)
73    .fetch_all(pool)
74    .await
75    .map_err(|e| StorageError::Query { source: e })?;
76
77    Ok(rows
78        .into_iter()
79        .map(|r| FollowerSnapshot {
80            snapshot_date: r.0,
81            follower_count: r.1,
82            following_count: r.2,
83            tweet_count: r.3,
84        })
85        .collect())
86}
87
88/// Get recent follower snapshots, ordered by date descending.
89pub async fn get_follower_snapshots(
90    pool: &DbPool,
91    limit: u32,
92) -> Result<Vec<FollowerSnapshot>, StorageError> {
93    get_follower_snapshots_for(pool, DEFAULT_ACCOUNT_ID, limit).await
94}
tuitbot_core/storage/analytics/snapshots.rs

tuitbot_core/storage/analytics/
snapshots.rs
