tuitbot_core/storage/
replies.rs

1//! CRUD operations for replies sent by the agent.
2//!
3//! Provides functions to insert replies, check for duplicates,
4//! count daily usage, and retrieve recent reply content.
5
6use super::DbPool;
7use crate::error::StorageError;
8
9/// A reply generated and posted by the agent.
10#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
11pub struct ReplySent {
12    /// Internal auto-generated ID.
13    pub id: i64,
14    /// Tweet ID that was replied to.
15    pub target_tweet_id: String,
16    /// Our reply's X tweet ID (None if post failed).
17    pub reply_tweet_id: Option<String>,
18    /// Generated reply text.
19    pub reply_content: String,
20    /// Which LLM generated this reply.
21    pub llm_provider: Option<String>,
22    /// Which model was used.
23    pub llm_model: Option<String>,
24    /// ISO-8601 UTC timestamp when reply was sent.
25    pub created_at: String,
26    /// Status: sent, failed, or deleted.
27    pub status: String,
28    /// Error details if failed.
29    pub error_message: Option<String>,
30}
31
32/// Insert a new reply record. Returns the auto-generated ID.
33pub async fn insert_reply(pool: &DbPool, reply: &ReplySent) -> Result<i64, StorageError> {
34    let result = sqlx::query(
35        "INSERT INTO replies_sent \
36         (target_tweet_id, reply_tweet_id, reply_content, llm_provider, llm_model, \
37          created_at, status, error_message) \
38         VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
39    )
40    .bind(&reply.target_tweet_id)
41    .bind(&reply.reply_tweet_id)
42    .bind(&reply.reply_content)
43    .bind(&reply.llm_provider)
44    .bind(&reply.llm_model)
45    .bind(&reply.created_at)
46    .bind(&reply.status)
47    .bind(&reply.error_message)
48    .execute(pool)
49    .await
50    .map_err(|e| StorageError::Query { source: e })?;
51
52    Ok(result.last_insert_rowid())
53}
54
55/// Fetch all replies with `created_at >= since`.
56pub async fn get_replies_since(pool: &DbPool, since: &str) -> Result<Vec<ReplySent>, StorageError> {
57    sqlx::query_as::<_, ReplySent>(
58        "SELECT * FROM replies_sent WHERE created_at >= ? ORDER BY created_at ASC",
59    )
60    .bind(since)
61    .fetch_all(pool)
62    .await
63    .map_err(|e| StorageError::Query { source: e })
64}
65
66/// Check if a reply has already been sent to a given tweet (deduplication).
67pub async fn has_replied_to(pool: &DbPool, tweet_id: &str) -> Result<bool, StorageError> {
68    let row: (i64,) =
69        sqlx::query_as("SELECT EXISTS(SELECT 1 FROM replies_sent WHERE target_tweet_id = ?)")
70            .bind(tweet_id)
71            .fetch_one(pool)
72            .await
73            .map_err(|e| StorageError::Query { source: e })?;
74
75    Ok(row.0 == 1)
76}
77
78/// Get recent reply contents for phrasing deduplication.
79pub async fn get_recent_reply_contents(
80    pool: &DbPool,
81    limit: i64,
82) -> Result<Vec<String>, StorageError> {
83    let rows: Vec<(String,)> =
84        sqlx::query_as("SELECT reply_content FROM replies_sent ORDER BY created_at DESC LIMIT ?")
85            .bind(limit)
86            .fetch_all(pool)
87            .await
88            .map_err(|e| StorageError::Query { source: e })?;
89
90    Ok(rows.into_iter().map(|r| r.0).collect())
91}
92
93/// Count replies sent today (UTC).
94pub async fn count_replies_today(pool: &DbPool) -> Result<i64, StorageError> {
95    let row: (i64,) =
96        sqlx::query_as("SELECT COUNT(*) FROM replies_sent WHERE date(created_at) = date('now')")
97            .fetch_one(pool)
98            .await
99            .map_err(|e| StorageError::Query { source: e })?;
100
101    Ok(row.0)
102}
103
104/// Get the most recent replies, newest first, with pagination.
105pub async fn get_recent_replies(
106    pool: &DbPool,
107    limit: u32,
108    offset: u32,
109) -> Result<Vec<ReplySent>, StorageError> {
110    sqlx::query_as::<_, ReplySent>(
111        "SELECT * FROM replies_sent ORDER BY created_at DESC LIMIT ? OFFSET ?",
112    )
113    .bind(limit)
114    .bind(offset)
115    .fetch_all(pool)
116    .await
117    .map_err(|e| StorageError::Query { source: e })
118}
119
120#[cfg(test)]
121mod tests {
122    use super::*;
123    use crate::storage::init_test_db;
124
125    fn sample_reply(target_id: &str) -> ReplySent {
126        ReplySent {
127            id: 0, // ignored on insert
128            target_tweet_id: target_id.to_string(),
129            reply_tweet_id: Some("reply_123".to_string()),
130            reply_content: "Great point! Here's my take...".to_string(),
131            llm_provider: Some("openai".to_string()),
132            llm_model: Some("gpt-4o-mini".to_string()),
133            created_at: chrono::Utc::now().format("%Y-%m-%dT%H:%M:%SZ").to_string(),
134            status: "sent".to_string(),
135            error_message: None,
136        }
137    }
138
139    #[tokio::test]
140    async fn insert_and_check_reply() {
141        let pool = init_test_db().await.expect("init db");
142        let reply = sample_reply("tweet_abc");
143
144        let id = insert_reply(&pool, &reply).await.expect("insert");
145        assert!(id > 0);
146
147        assert!(has_replied_to(&pool, "tweet_abc").await.expect("check"));
148        assert!(!has_replied_to(&pool, "tweet_xyz").await.expect("check"));
149    }
150
151    #[tokio::test]
152    async fn count_replies_today_works() {
153        let pool = init_test_db().await.expect("init db");
154        let reply = sample_reply("tweet_count");
155
156        insert_reply(&pool, &reply).await.expect("insert");
157        let count = count_replies_today(&pool).await.expect("count");
158        assert_eq!(count, 1);
159    }
160
161    #[tokio::test]
162    async fn get_recent_contents() {
163        let pool = init_test_db().await.expect("init db");
164
165        let mut r1 = sample_reply("t1");
166        r1.reply_content = "Reply one".to_string();
167        let mut r2 = sample_reply("t2");
168        r2.reply_content = "Reply two".to_string();
169
170        insert_reply(&pool, &r1).await.expect("ins1");
171        insert_reply(&pool, &r2).await.expect("ins2");
172
173        let contents = get_recent_reply_contents(&pool, 5).await.expect("get");
174        assert_eq!(contents.len(), 2);
175    }
176
177    #[tokio::test]
178    async fn get_replies_since_filters() {
179        let pool = init_test_db().await.expect("init db");
180        let reply = sample_reply("tweet_since");
181
182        insert_reply(&pool, &reply).await.expect("insert");
183
184        let all = get_replies_since(&pool, "2000-01-01T00:00:00Z")
185            .await
186            .expect("get");
187        assert_eq!(all.len(), 1);
188
189        let none = get_replies_since(&pool, "2099-01-01T00:00:00Z")
190            .await
191            .expect("get");
192        assert!(none.is_empty());
193    }
194}
tuitbot_core/storage/replies.rs

tuitbot_core/storage/
replies.rs
