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::accounts::DEFAULT_ACCOUNT_ID;
7use super::DbPool;
8use crate::error::StorageError;
9
10/// A reply generated and posted by the agent.
11#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
12pub struct ReplySent {
13    /// Internal auto-generated ID.
14    pub id: i64,
15    /// Tweet ID that was replied to.
16    pub target_tweet_id: String,
17    /// Our reply's X tweet ID (None if post failed).
18    pub reply_tweet_id: Option<String>,
19    /// Generated reply text.
20    pub reply_content: String,
21    /// Which LLM generated this reply.
22    pub llm_provider: Option<String>,
23    /// Which model was used.
24    pub llm_model: Option<String>,
25    /// ISO-8601 UTC timestamp when reply was sent.
26    pub created_at: String,
27    /// Status: sent, failed, or deleted.
28    pub status: String,
29    /// Error details if failed.
30    pub error_message: Option<String>,
31}
32
33/// Insert a new reply record for a specific account. Returns the auto-generated ID.
34pub async fn insert_reply_for(
35    pool: &DbPool,
36    account_id: &str,
37    reply: &ReplySent,
38) -> Result<i64, StorageError> {
39    let result = sqlx::query(
40        "INSERT INTO replies_sent \
41         (account_id, target_tweet_id, reply_tweet_id, reply_content, llm_provider, llm_model, \
42          created_at, status, error_message) \
43         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
44    )
45    .bind(account_id)
46    .bind(&reply.target_tweet_id)
47    .bind(&reply.reply_tweet_id)
48    .bind(&reply.reply_content)
49    .bind(&reply.llm_provider)
50    .bind(&reply.llm_model)
51    .bind(&reply.created_at)
52    .bind(&reply.status)
53    .bind(&reply.error_message)
54    .execute(pool)
55    .await
56    .map_err(|e| StorageError::Query { source: e })?;
57
58    Ok(result.last_insert_rowid())
59}
60
61/// Insert a new reply record. Returns the auto-generated ID.
62pub async fn insert_reply(pool: &DbPool, reply: &ReplySent) -> Result<i64, StorageError> {
63    insert_reply_for(pool, DEFAULT_ACCOUNT_ID, reply).await
64}
65
66/// Fetch all replies with `created_at >= since` for a specific account.
67pub async fn get_replies_since_for(
68    pool: &DbPool,
69    account_id: &str,
70    since: &str,
71) -> Result<Vec<ReplySent>, StorageError> {
72    sqlx::query_as::<_, ReplySent>(
73        "SELECT * FROM replies_sent WHERE account_id = ? AND created_at >= ? ORDER BY created_at ASC",
74    )
75    .bind(account_id)
76    .bind(since)
77    .fetch_all(pool)
78    .await
79    .map_err(|e| StorageError::Query { source: e })
80}
81
82/// Fetch all replies with `created_at >= since`.
83pub async fn get_replies_since(pool: &DbPool, since: &str) -> Result<Vec<ReplySent>, StorageError> {
84    get_replies_since_for(pool, DEFAULT_ACCOUNT_ID, since).await
85}
86
87/// Check if a reply has already been sent to a given tweet for a specific account (deduplication).
88pub async fn has_replied_to_for(
89    pool: &DbPool,
90    account_id: &str,
91    tweet_id: &str,
92) -> Result<bool, StorageError> {
93    let row: (i64,) = sqlx::query_as(
94        "SELECT EXISTS(SELECT 1 FROM replies_sent WHERE account_id = ? AND target_tweet_id = ?)",
95    )
96    .bind(account_id)
97    .bind(tweet_id)
98    .fetch_one(pool)
99    .await
100    .map_err(|e| StorageError::Query { source: e })?;
101
102    Ok(row.0 == 1)
103}
104
105/// Check if a reply has already been sent to a given tweet (deduplication).
106pub async fn has_replied_to(pool: &DbPool, tweet_id: &str) -> Result<bool, StorageError> {
107    has_replied_to_for(pool, DEFAULT_ACCOUNT_ID, tweet_id).await
108}
109
110/// Get recent reply contents for a specific account for phrasing deduplication.
111pub async fn get_recent_reply_contents_for(
112    pool: &DbPool,
113    account_id: &str,
114    limit: i64,
115) -> Result<Vec<String>, StorageError> {
116    let rows: Vec<(String,)> = sqlx::query_as(
117        "SELECT reply_content FROM replies_sent WHERE account_id = ? ORDER BY created_at DESC LIMIT ?",
118    )
119    .bind(account_id)
120    .bind(limit)
121    .fetch_all(pool)
122    .await
123    .map_err(|e| StorageError::Query { source: e })?;
124
125    Ok(rows.into_iter().map(|r| r.0).collect())
126}
127
128/// Get recent reply contents for phrasing deduplication.
129pub async fn get_recent_reply_contents(
130    pool: &DbPool,
131    limit: i64,
132) -> Result<Vec<String>, StorageError> {
133    get_recent_reply_contents_for(pool, DEFAULT_ACCOUNT_ID, limit).await
134}
135
136/// Count replies sent today (UTC) for a specific account.
137pub async fn count_replies_today_for(pool: &DbPool, account_id: &str) -> Result<i64, StorageError> {
138    let row: (i64,) = sqlx::query_as(
139        "SELECT COUNT(*) FROM replies_sent WHERE account_id = ? AND date(created_at) = date('now')",
140    )
141    .bind(account_id)
142    .fetch_one(pool)
143    .await
144    .map_err(|e| StorageError::Query { source: e })?;
145
146    Ok(row.0)
147}
148
149/// Count replies sent today (UTC).
150pub async fn count_replies_today(pool: &DbPool) -> Result<i64, StorageError> {
151    count_replies_today_for(pool, DEFAULT_ACCOUNT_ID).await
152}
153
154/// Get replies within a date range for a specific account, ordered by creation time.
155pub async fn get_replies_in_range_for(
156    pool: &DbPool,
157    account_id: &str,
158    from: &str,
159    to: &str,
160) -> Result<Vec<ReplySent>, StorageError> {
161    sqlx::query_as::<_, ReplySent>(
162        "SELECT * FROM replies_sent \
163         WHERE account_id = ? AND created_at BETWEEN ? AND ? \
164         ORDER BY created_at ASC",
165    )
166    .bind(account_id)
167    .bind(from)
168    .bind(to)
169    .fetch_all(pool)
170    .await
171    .map_err(|e| StorageError::Query { source: e })
172}
173
174/// Get replies within a date range, ordered by creation time.
175pub async fn get_replies_in_range(
176    pool: &DbPool,
177    from: &str,
178    to: &str,
179) -> Result<Vec<ReplySent>, StorageError> {
180    get_replies_in_range_for(pool, DEFAULT_ACCOUNT_ID, from, to).await
181}
182
183/// Get the most recent replies for a specific account, newest first, with pagination.
184pub async fn get_recent_replies_for(
185    pool: &DbPool,
186    account_id: &str,
187    limit: u32,
188    offset: u32,
189) -> Result<Vec<ReplySent>, StorageError> {
190    sqlx::query_as::<_, ReplySent>(
191        "SELECT * FROM replies_sent WHERE account_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?",
192    )
193    .bind(account_id)
194    .bind(limit)
195    .bind(offset)
196    .fetch_all(pool)
197    .await
198    .map_err(|e| StorageError::Query { source: e })
199}
200
201/// Get the most recent replies, newest first, with pagination.
202pub async fn get_recent_replies(
203    pool: &DbPool,
204    limit: u32,
205    offset: u32,
206) -> Result<Vec<ReplySent>, StorageError> {
207    get_recent_replies_for(pool, DEFAULT_ACCOUNT_ID, limit, offset).await
208}
209
210#[cfg(test)]
211mod tests {
212    use super::*;
213    use crate::storage::init_test_db;
214
215    fn sample_reply(target_id: &str) -> ReplySent {
216        ReplySent {
217            id: 0, // ignored on insert
218            target_tweet_id: target_id.to_string(),
219            reply_tweet_id: Some("reply_123".to_string()),
220            reply_content: "Great point! Here's my take...".to_string(),
221            llm_provider: Some("openai".to_string()),
222            llm_model: Some("gpt-4o-mini".to_string()),
223            created_at: chrono::Utc::now().format("%Y-%m-%dT%H:%M:%SZ").to_string(),
224            status: "sent".to_string(),
225            error_message: None,
226        }
227    }
228
229    #[tokio::test]
230    async fn insert_and_check_reply() {
231        let pool = init_test_db().await.expect("init db");
232        let reply = sample_reply("tweet_abc");
233
234        let id = insert_reply(&pool, &reply).await.expect("insert");
235        assert!(id > 0);
236
237        assert!(has_replied_to(&pool, "tweet_abc").await.expect("check"));
238        assert!(!has_replied_to(&pool, "tweet_xyz").await.expect("check"));
239    }
240
241    #[tokio::test]
242    async fn count_replies_today_works() {
243        let pool = init_test_db().await.expect("init db");
244        let reply = sample_reply("tweet_count");
245
246        insert_reply(&pool, &reply).await.expect("insert");
247        let count = count_replies_today(&pool).await.expect("count");
248        assert_eq!(count, 1);
249    }
250
251    #[tokio::test]
252    async fn get_recent_contents() {
253        let pool = init_test_db().await.expect("init db");
254
255        let mut r1 = sample_reply("t1");
256        r1.reply_content = "Reply one".to_string();
257        let mut r2 = sample_reply("t2");
258        r2.reply_content = "Reply two".to_string();
259
260        insert_reply(&pool, &r1).await.expect("ins1");
261        insert_reply(&pool, &r2).await.expect("ins2");
262
263        let contents = get_recent_reply_contents(&pool, 5).await.expect("get");
264        assert_eq!(contents.len(), 2);
265    }
266
267    #[tokio::test]
268    async fn get_replies_in_range_filters() {
269        let pool = init_test_db().await.expect("init db");
270
271        let mut r1 = sample_reply("t_range1");
272        r1.created_at = "2026-02-20T10:00:00Z".to_string();
273        insert_reply(&pool, &r1).await.expect("insert");
274
275        let mut r2 = sample_reply("t_range2");
276        r2.created_at = "2026-02-25T10:00:00Z".to_string();
277        insert_reply(&pool, &r2).await.expect("insert");
278
279        let in_range = get_replies_in_range(&pool, "2026-02-19T00:00:00Z", "2026-02-21T00:00:00Z")
280            .await
281            .expect("range");
282        assert_eq!(in_range.len(), 1);
283        assert_eq!(in_range[0].target_tweet_id, "t_range1");
284
285        let all = get_replies_in_range(&pool, "2026-02-01T00:00:00Z", "2026-02-28T00:00:00Z")
286            .await
287            .expect("range");
288        assert_eq!(all.len(), 2);
289    }
290
291    #[tokio::test]
292    async fn get_replies_since_filters() {
293        let pool = init_test_db().await.expect("init db");
294        let reply = sample_reply("tweet_since");
295
296        insert_reply(&pool, &reply).await.expect("insert");
297
298        let all = get_replies_since(&pool, "2000-01-01T00:00:00Z")
299            .await
300            .expect("get");
301        assert_eq!(all.len(), 1);
302
303        let none = get_replies_since(&pool, "2099-01-01T00:00:00Z")
304            .await
305            .expect("get");
306        assert!(none.is_empty());
307    }
308}
tuitbot_core/storage/replies.rs

tuitbot_core/storage/
replies.rs
