tuitbot-core 0.1.47

//! CRUD operations for replies sent by the agent.
//!
//! Provides functions to insert replies, check for duplicates,
//! count daily usage, and retrieve recent reply content.

use super::accounts::DEFAULT_ACCOUNT_ID;
use super::DbPool;
use crate::error::StorageError;

/// A reply generated and posted by the agent.
#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
pub struct ReplySent {
    /// Internal auto-generated ID.
    pub id: i64,
    /// Tweet ID that was replied to.
    pub target_tweet_id: String,
    /// Our reply's X tweet ID (None if post failed).
    pub reply_tweet_id: Option<String>,
    /// Generated reply text.
    pub reply_content: String,
    /// Which LLM generated this reply.
    pub llm_provider: Option<String>,
    /// Which model was used.
    pub llm_model: Option<String>,
    /// ISO-8601 UTC timestamp when reply was sent.
    pub created_at: String,
    /// Status: sent, failed, or deleted.
    pub status: String,
    /// Error details if failed.
    pub error_message: Option<String>,
}

/// Insert a new reply record for a specific account. Returns the auto-generated ID.
pub async fn insert_reply_for(
    pool: &DbPool,
    account_id: &str,
    reply: &ReplySent,
) -> Result<i64, StorageError> {
    let result = sqlx::query(
        "INSERT INTO replies_sent \
         (account_id, target_tweet_id, reply_tweet_id, reply_content, llm_provider, llm_model, \
          created_at, status, error_message) \
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
    )
    .bind(account_id)
    .bind(&reply.target_tweet_id)
    .bind(&reply.reply_tweet_id)
    .bind(&reply.reply_content)
    .bind(&reply.llm_provider)
    .bind(&reply.llm_model)
    .bind(&reply.created_at)
    .bind(&reply.status)
    .bind(&reply.error_message)
    .execute(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })?;

    Ok(result.last_insert_rowid())
}

/// Insert a new reply record. Returns the auto-generated ID.
pub async fn insert_reply(pool: &DbPool, reply: &ReplySent) -> Result<i64, StorageError> {
    insert_reply_for(pool, DEFAULT_ACCOUNT_ID, reply).await
}

/// Fetch all replies with `created_at >= since` for a specific account.
pub async fn get_replies_since_for(
    pool: &DbPool,
    account_id: &str,
    since: &str,
) -> Result<Vec<ReplySent>, StorageError> {
    sqlx::query_as::<_, ReplySent>(
        "SELECT * FROM replies_sent WHERE account_id = ? AND created_at >= ? ORDER BY created_at ASC",
    )
    .bind(account_id)
    .bind(since)
    .fetch_all(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })
}

/// Fetch all replies with `created_at >= since`.
pub async fn get_replies_since(pool: &DbPool, since: &str) -> Result<Vec<ReplySent>, StorageError> {
    get_replies_since_for(pool, DEFAULT_ACCOUNT_ID, since).await
}

/// Check if a reply has already been sent to a given tweet for a specific account (deduplication).
pub async fn has_replied_to_for(
    pool: &DbPool,
    account_id: &str,
    tweet_id: &str,
) -> Result<bool, StorageError> {
    let row: (i64,) = sqlx::query_as(
        "SELECT EXISTS(SELECT 1 FROM replies_sent WHERE account_id = ? AND target_tweet_id = ?)",
    )
    .bind(account_id)
    .bind(tweet_id)
    .fetch_one(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })?;

    Ok(row.0 == 1)
}

/// Check if a reply has already been sent to a given tweet (deduplication).
pub async fn has_replied_to(pool: &DbPool, tweet_id: &str) -> Result<bool, StorageError> {
    has_replied_to_for(pool, DEFAULT_ACCOUNT_ID, tweet_id).await
}

/// Get recent reply contents for a specific account for phrasing deduplication.
pub async fn get_recent_reply_contents_for(
    pool: &DbPool,
    account_id: &str,
    limit: i64,
) -> Result<Vec<String>, StorageError> {
    let rows: Vec<(String,)> = sqlx::query_as(
        "SELECT reply_content FROM replies_sent WHERE account_id = ? ORDER BY created_at DESC LIMIT ?",
    )
    .bind(account_id)
    .bind(limit)
    .fetch_all(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })?;

    Ok(rows.into_iter().map(|r| r.0).collect())
}

/// Get recent reply contents for phrasing deduplication.
pub async fn get_recent_reply_contents(
    pool: &DbPool,
    limit: i64,
) -> Result<Vec<String>, StorageError> {
    get_recent_reply_contents_for(pool, DEFAULT_ACCOUNT_ID, limit).await
}

/// Count replies sent today (UTC) for a specific account.
pub async fn count_replies_today_for(pool: &DbPool, account_id: &str) -> Result<i64, StorageError> {
    let row: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM replies_sent WHERE account_id = ? AND date(created_at) = date('now')",
    )
    .bind(account_id)
    .fetch_one(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })?;

    Ok(row.0)
}

/// Count replies sent today (UTC).
pub async fn count_replies_today(pool: &DbPool) -> Result<i64, StorageError> {
    count_replies_today_for(pool, DEFAULT_ACCOUNT_ID).await
}

/// Get replies within a date range for a specific account, ordered by creation time.
pub async fn get_replies_in_range_for(
    pool: &DbPool,
    account_id: &str,
    from: &str,
    to: &str,
) -> Result<Vec<ReplySent>, StorageError> {
    sqlx::query_as::<_, ReplySent>(
        "SELECT * FROM replies_sent \
         WHERE account_id = ? AND created_at BETWEEN ? AND ? \
         ORDER BY created_at ASC",
    )
    .bind(account_id)
    .bind(from)
    .bind(to)
    .fetch_all(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })
}

/// Get replies within a date range, ordered by creation time.
pub async fn get_replies_in_range(
    pool: &DbPool,
    from: &str,
    to: &str,
) -> Result<Vec<ReplySent>, StorageError> {
    get_replies_in_range_for(pool, DEFAULT_ACCOUNT_ID, from, to).await
}

/// Get the most recent replies for a specific account, newest first, with pagination.
pub async fn get_recent_replies_for(
    pool: &DbPool,
    account_id: &str,
    limit: u32,
    offset: u32,
) -> Result<Vec<ReplySent>, StorageError> {
    sqlx::query_as::<_, ReplySent>(
        "SELECT * FROM replies_sent WHERE account_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?",
    )
    .bind(account_id)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await
    .map_err(|e| StorageError::Query { source: e })
}

/// Get the most recent replies, newest first, with pagination.
pub async fn get_recent_replies(
    pool: &DbPool,
    limit: u32,
    offset: u32,
) -> Result<Vec<ReplySent>, StorageError> {
    get_recent_replies_for(pool, DEFAULT_ACCOUNT_ID, limit, offset).await
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::storage::init_test_db;

    fn sample_reply(target_id: &str) -> ReplySent {
        ReplySent {
            id: 0, // ignored on insert
            target_tweet_id: target_id.to_string(),
            reply_tweet_id: Some("reply_123".to_string()),
            reply_content: "Great point! Here's my take...".to_string(),
            llm_provider: Some("openai".to_string()),
            llm_model: Some("gpt-4o-mini".to_string()),
            created_at: chrono::Utc::now().format("%Y-%m-%dT%H:%M:%SZ").to_string(),
            status: "sent".to_string(),
            error_message: None,
        }
    }

    #[tokio::test]
    async fn insert_and_check_reply() {
        let pool = init_test_db().await.expect("init db");
        let reply = sample_reply("tweet_abc");

        let id = insert_reply(&pool, &reply).await.expect("insert");
        assert!(id > 0);

        assert!(has_replied_to(&pool, "tweet_abc").await.expect("check"));
        assert!(!has_replied_to(&pool, "tweet_xyz").await.expect("check"));
    }

    #[tokio::test]
    async fn count_replies_today_works() {
        let pool = init_test_db().await.expect("init db");
        let reply = sample_reply("tweet_count");

        insert_reply(&pool, &reply).await.expect("insert");
        let count = count_replies_today(&pool).await.expect("count");
        assert_eq!(count, 1);
    }

    #[tokio::test]
    async fn get_recent_contents() {
        let pool = init_test_db().await.expect("init db");

        let mut r1 = sample_reply("t1");
        r1.reply_content = "Reply one".to_string();
        let mut r2 = sample_reply("t2");
        r2.reply_content = "Reply two".to_string();

        insert_reply(&pool, &r1).await.expect("ins1");
        insert_reply(&pool, &r2).await.expect("ins2");

        let contents = get_recent_reply_contents(&pool, 5).await.expect("get");
        assert_eq!(contents.len(), 2);
    }

    #[tokio::test]
    async fn get_replies_in_range_filters() {
        let pool = init_test_db().await.expect("init db");

        let mut r1 = sample_reply("t_range1");
        r1.created_at = "2026-02-20T10:00:00Z".to_string();
        insert_reply(&pool, &r1).await.expect("insert");

        let mut r2 = sample_reply("t_range2");
        r2.created_at = "2026-02-25T10:00:00Z".to_string();
        insert_reply(&pool, &r2).await.expect("insert");

        let in_range = get_replies_in_range(&pool, "2026-02-19T00:00:00Z", "2026-02-21T00:00:00Z")
            .await
            .expect("range");
        assert_eq!(in_range.len(), 1);
        assert_eq!(in_range[0].target_tweet_id, "t_range1");

        let all = get_replies_in_range(&pool, "2026-02-01T00:00:00Z", "2026-02-28T00:00:00Z")
            .await
            .expect("range");
        assert_eq!(all.len(), 2);
    }

    #[tokio::test]
    async fn get_replies_since_filters() {
        let pool = init_test_db().await.expect("init db");
        let reply = sample_reply("tweet_since");

        insert_reply(&pool, &reply).await.expect("insert");

        let all = get_replies_since(&pool, "2000-01-01T00:00:00Z")
            .await
            .expect("get");
        assert_eq!(all.len(), 1);

        let none = get_replies_since(&pool, "2099-01-01T00:00:00Z")
            .await
            .expect("get");
        assert!(none.is_empty());
    }
}