tuitbot_core/storage/analytics/
ancestors.rs

1use super::super::DbPool;
2use crate::error::StorageError;
3
4/// Update the archetype_vibe classification for a tweet performance record.
5pub async fn update_tweet_archetype(
6    pool: &DbPool,
7    tweet_id: &str,
8    archetype_vibe: &str,
9) -> Result<(), StorageError> {
10    sqlx::query("UPDATE tweet_performance SET archetype_vibe = ? WHERE tweet_id = ?")
11        .bind(archetype_vibe)
12        .bind(tweet_id)
13        .execute(pool)
14        .await
15        .map_err(|e| StorageError::Query { source: e })?;
16    Ok(())
17}
18
19/// Update the archetype_vibe classification for a reply performance record.
20pub async fn update_reply_archetype(
21    pool: &DbPool,
22    reply_id: &str,
23    archetype_vibe: &str,
24) -> Result<(), StorageError> {
25    sqlx::query("UPDATE reply_performance SET archetype_vibe = ? WHERE reply_id = ?")
26        .bind(archetype_vibe)
27        .bind(reply_id)
28        .execute(pool)
29        .await
30        .map_err(|e| StorageError::Query { source: e })?;
31    Ok(())
32}
33
34/// Update the engagement_score for a tweet performance record.
35pub async fn update_tweet_engagement_score(
36    pool: &DbPool,
37    tweet_id: &str,
38    score: f64,
39) -> Result<(), StorageError> {
40    sqlx::query("UPDATE tweet_performance SET engagement_score = ? WHERE tweet_id = ?")
41        .bind(score)
42        .bind(tweet_id)
43        .execute(pool)
44        .await
45        .map_err(|e| StorageError::Query { source: e })?;
46    Ok(())
47}
48
49/// Update the engagement_score for a reply performance record.
50pub async fn update_reply_engagement_score(
51    pool: &DbPool,
52    reply_id: &str,
53    score: f64,
54) -> Result<(), StorageError> {
55    sqlx::query("UPDATE reply_performance SET engagement_score = ? WHERE reply_id = ?")
56        .bind(score)
57        .bind(reply_id)
58        .execute(pool)
59        .await
60        .map_err(|e| StorageError::Query { source: e })?;
61    Ok(())
62}
63
64/// Get the maximum performance_score across all tweets and replies.
65///
66/// Returns 0.0 if no performance data exists. Used to normalize engagement scores.
67pub async fn get_max_performance_score(pool: &DbPool) -> Result<f64, StorageError> {
68    let row: (f64,) = sqlx::query_as(
69        "SELECT COALESCE(MAX(max_score), 0.0) FROM (\
70             SELECT MAX(performance_score) as max_score FROM tweet_performance \
71             UNION ALL \
72             SELECT MAX(performance_score) as max_score FROM reply_performance\
73         )",
74    )
75    .fetch_one(pool)
76    .await
77    .map_err(|e| StorageError::Query { source: e })?;
78
79    Ok(row.0)
80}
81
82/// Row type returned by the ancestor retrieval query.
83#[derive(Debug, Clone)]
84pub struct AncestorRow {
85    /// "tweet" or "reply"
86    pub content_type: String,
87    /// The tweet or reply ID.
88    pub id: String,
89    /// Truncated content preview (up to 120 chars).
90    pub content_preview: String,
91    /// Archetype classification (may be None if not yet classified).
92    pub archetype_vibe: Option<String>,
93    /// Normalized engagement score (0.0-1.0).
94    pub engagement_score: Option<f64>,
95    /// Raw performance score.
96    pub performance_score: f64,
97    /// When the content was posted (ISO-8601).
98    pub posted_at: String,
99}
100
101/// Row type returned by the ancestor retrieval UNION query.
102type AncestorQueryRow = (
103    String,
104    String,
105    String,
106    Option<String>,
107    Option<f64>,
108    f64,
109    String,
110);
111
112/// Convert an ancestor query row tuple into an `AncestorRow` struct.
113fn ancestor_row_from_tuple(r: AncestorQueryRow) -> AncestorRow {
114    AncestorRow {
115        content_type: r.0,
116        id: r.1,
117        content_preview: r.2,
118        archetype_vibe: r.3,
119        engagement_score: r.4,
120        performance_score: r.5,
121        posted_at: r.6,
122    }
123}
124
125/// Query scored ancestors with engagement_score populated, scoped to an account.
126///
127/// Returns ancestors where `engagement_score >= min_score`, ordered by
128/// engagement_score DESC. For topic matching, uses the `topic` column on
129/// original_tweets and LIKE-based content matching on replies.
130pub async fn get_scored_ancestors(
131    pool: &DbPool,
132    account_id: &str,
133    topic_keywords: &[String],
134    min_score: f64,
135    limit: u32,
136) -> Result<Vec<AncestorRow>, StorageError> {
137    if topic_keywords.is_empty() {
138        // No keywords: return top ancestors regardless of topic
139        let rows: Vec<AncestorQueryRow> = sqlx::query_as(
140            "SELECT 'tweet' as content_type, tp.tweet_id, \
141                        SUBSTR(ot.content, 1, 120), \
142                        tp.archetype_vibe, tp.engagement_score, tp.performance_score, \
143                        ot.created_at \
144                 FROM tweet_performance tp \
145                 JOIN original_tweets ot ON ot.tweet_id = tp.tweet_id \
146                 WHERE ot.account_id = ? \
147                   AND tp.engagement_score IS NOT NULL \
148                   AND tp.engagement_score >= ? \
149                 UNION ALL \
150                 SELECT 'reply', rp.reply_id, SUBSTR(rs.reply_content, 1, 120), \
151                        rp.archetype_vibe, rp.engagement_score, rp.performance_score, \
152                        rs.created_at \
153                 FROM reply_performance rp \
154                 JOIN replies_sent rs ON rs.reply_tweet_id = rp.reply_id \
155                 WHERE rs.account_id = ? \
156                   AND rp.engagement_score IS NOT NULL \
157                   AND rp.engagement_score >= ? \
158                 ORDER BY engagement_score DESC \
159                 LIMIT ?",
160        )
161        .bind(account_id)
162        .bind(min_score)
163        .bind(account_id)
164        .bind(min_score)
165        .bind(limit)
166        .fetch_all(pool)
167        .await
168        .map_err(|e| StorageError::Query { source: e })?;
169
170        return Ok(rows.into_iter().map(ancestor_row_from_tuple).collect());
171    }
172
173    // Build parameterized IN clause for tweet topics and LIKE clauses for replies.
174    // SQLx uses sequential `?` placeholders for SQLite.
175    let topic_placeholders: String = (0..topic_keywords.len())
176        .map(|_| "?".to_string())
177        .collect::<Vec<_>>()
178        .join(", ");
179
180    let like_conditions: Vec<String> = (0..topic_keywords.len())
181        .map(|_| "rs.reply_content LIKE '%' || ? || '%'".to_string())
182        .collect();
183    let like_clause = like_conditions.join(" OR ");
184
185    let query_str = format!(
186        "SELECT 'tweet' as content_type, tp.tweet_id, \
187                SUBSTR(ot.content, 1, 120), \
188                tp.archetype_vibe, tp.engagement_score, tp.performance_score, \
189                ot.created_at \
190         FROM tweet_performance tp \
191         JOIN original_tweets ot ON ot.tweet_id = tp.tweet_id \
192         WHERE ot.account_id = ? \
193           AND tp.engagement_score IS NOT NULL \
194           AND tp.engagement_score >= ? \
195           AND (ot.topic IN ({topic_placeholders})) \
196         UNION ALL \
197         SELECT 'reply', rp.reply_id, SUBSTR(rs.reply_content, 1, 120), \
198                rp.archetype_vibe, rp.engagement_score, rp.performance_score, \
199                rs.created_at \
200         FROM reply_performance rp \
201         JOIN replies_sent rs ON rs.reply_tweet_id = rp.reply_id \
202         WHERE rs.account_id = ? \
203           AND rp.engagement_score IS NOT NULL \
204           AND rp.engagement_score >= ? \
205           AND ({like_clause}) \
206         ORDER BY engagement_score DESC \
207         LIMIT ?"
208    );
209
210    let mut query = sqlx::query_as::<_, AncestorQueryRow>(&query_str);
211
212    // Bind: account_id for tweets, min_score, then topic keywords for IN clause
213    query = query.bind(account_id);
214    query = query.bind(min_score);
215    for kw in topic_keywords {
216        query = query.bind(kw);
217    }
218    // Bind: account_id for replies, min_score, keywords for LIKE clauses, then limit
219    query = query.bind(account_id);
220    query = query.bind(min_score);
221    for kw in topic_keywords {
222        query = query.bind(kw);
223    }
224    query = query.bind(limit);
225
226    let rows = query
227        .fetch_all(pool)
228        .await
229        .map_err(|e| StorageError::Query { source: e })?;
230
231    Ok(rows.into_iter().map(ancestor_row_from_tuple).collect())
232}
tuitbot_core/storage/analytics/ancestors.rs

tuitbot_core/storage/analytics/
ancestors.rs
