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         AND snapshot_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' \
69         ORDER BY snapshot_date DESC \
70         LIMIT ?",
71    )
72    .bind(account_id)
73    .bind(limit)
74    .fetch_all(pool)
75    .await
76    .map_err(|e| StorageError::Query { source: e })?;
77
78    Ok(rows
79        .into_iter()
80        .map(|r| FollowerSnapshot {
81            snapshot_date: r.0,
82            follower_count: r.1,
83            following_count: r.2,
84            tweet_count: r.3,
85        })
86        .collect())
87}
88
89/// Get recent follower snapshots, ordered by date descending.
90pub async fn get_follower_snapshots(
91    pool: &DbPool,
92    limit: u32,
93) -> Result<Vec<FollowerSnapshot>, StorageError> {
94    get_follower_snapshots_for(pool, DEFAULT_ACCOUNT_ID, limit).await
95}
tuitbot_core/storage/analytics/snapshots.rs

tuitbot_core/storage/analytics/
snapshots.rs
