tuitbot_core/context/
topics.rs

1//! Topic performance snapshot with double-down/reduce recommendations.
2//!
3//! Queries time-windowed performance data from original tweets and their
4//! measured engagement to produce ranked topic analysis with actionable
5//! recommendations grounded in stored data.
6
7use crate::error::StorageError;
8use crate::storage::DbPool;
9use serde::Serialize;
10
11/// Complete topic performance snapshot for a lookback window.
12#[derive(Debug, Clone, Serialize)]
13pub struct TopicSnapshot {
14    pub lookback_days: u32,
15    pub topics: Vec<TopicAnalysis>,
16    pub overall_avg_performance: f64,
17    pub total_posts_analyzed: i64,
18}
19
20/// Performance analysis for a single topic.
21#[derive(Debug, Clone, Serialize)]
22pub struct TopicAnalysis {
23    pub topic: String,
24    pub post_count: i64,
25    pub avg_performance: f64,
26    pub performance_vs_average: f64,
27    pub recommendation: String,
28    pub provenance: TopicProvenance,
29}
30
31/// Evidence trail showing the data behind the recommendation.
32#[derive(Debug, Clone, Serialize)]
33pub struct TopicProvenance {
34    pub best_content_preview: String,
35    pub best_performance_score: f64,
36    pub worst_performance_score: f64,
37}
38
39type AggRow = (String, i64, f64, f64, f64);
40
41/// Build a topic performance snapshot for the given lookback window.
42///
43/// Returns topics ranked by average performance, each annotated with
44/// a recommendation ("double_down", "reduce", "maintain", or "experiment")
45/// and provenance data showing which posts drove the score.
46pub async fn get_topic_snapshot(
47    pool: &DbPool,
48    lookback_days: u32,
49) -> Result<TopicSnapshot, StorageError> {
50    let since = chrono::Utc::now()
51        .checked_sub_signed(chrono::Duration::days(lookback_days as i64))
52        .unwrap_or_else(chrono::Utc::now)
53        .format("%Y-%m-%dT%H:%M:%SZ")
54        .to_string();
55
56    // Aggregate topic performance within the window
57    let agg_rows: Vec<AggRow> = sqlx::query_as(
58        "SELECT ot.topic, \
59                COUNT(*) as post_count, \
60                AVG(COALESCE(tp.performance_score, 0.0)) as avg_perf, \
61                MAX(COALESCE(tp.performance_score, 0.0)) as best, \
62                MIN(COALESCE(tp.performance_score, 0.0)) as worst \
63         FROM original_tweets ot \
64         LEFT JOIN tweet_performance tp ON tp.tweet_id = ot.tweet_id \
65         WHERE ot.created_at >= ? \
66           AND ot.topic IS NOT NULL AND ot.topic != '' \
67           AND ot.status = 'sent' \
68         GROUP BY ot.topic \
69         ORDER BY avg_perf DESC",
70    )
71    .bind(&since)
72    .fetch_all(pool)
73    .await
74    .map_err(|e| StorageError::Query { source: e })?;
75
76    if agg_rows.is_empty() {
77        return Ok(TopicSnapshot {
78            lookback_days,
79            topics: Vec::new(),
80            overall_avg_performance: 0.0,
81            total_posts_analyzed: 0,
82        });
83    }
84
85    // Compute weighted overall average
86    let total_posts: i64 = agg_rows.iter().map(|r| r.1).sum();
87    let weighted_sum: f64 = agg_rows.iter().map(|r| r.2 * r.1 as f64).sum();
88    let overall_avg = if total_posts > 0 {
89        weighted_sum / total_posts as f64
90    } else {
91        0.0
92    };
93
94    // Build topic analysis with provenance
95    let mut topics = Vec::with_capacity(agg_rows.len());
96    for (topic, post_count, avg_perf, best, worst) in &agg_rows {
97        let preview = query_best_content_preview(pool, topic, &since).await?;
98        let vs_avg = if overall_avg > 0.0 {
99            avg_perf / overall_avg
100        } else {
101            1.0
102        };
103        let recommendation = classify_topic(*post_count, vs_avg);
104
105        topics.push(TopicAnalysis {
106            topic: topic.clone(),
107            post_count: *post_count,
108            avg_performance: *avg_perf,
109            performance_vs_average: vs_avg,
110            recommendation,
111            provenance: TopicProvenance {
112                best_content_preview: preview,
113                best_performance_score: *best,
114                worst_performance_score: *worst,
115            },
116        });
117    }
118
119    Ok(TopicSnapshot {
120        lookback_days,
121        topics,
122        overall_avg_performance: overall_avg,
123        total_posts_analyzed: total_posts,
124    })
125}
126
127async fn query_best_content_preview(
128    pool: &DbPool,
129    topic: &str,
130    since: &str,
131) -> Result<String, StorageError> {
132    let row: Option<(String,)> = sqlx::query_as(
133        "SELECT SUBSTR(ot.content, 1, 120) \
134         FROM original_tweets ot \
135         LEFT JOIN tweet_performance tp ON tp.tweet_id = ot.tweet_id \
136         WHERE ot.topic = ? AND ot.created_at >= ? AND ot.status = 'sent' \
137         ORDER BY COALESCE(tp.performance_score, 0.0) DESC \
138         LIMIT 1",
139    )
140    .bind(topic)
141    .bind(since)
142    .fetch_optional(pool)
143    .await
144    .map_err(|e| StorageError::Query { source: e })?;
145
146    Ok(row.map(|(s,)| s).unwrap_or_default())
147}
148
149/// Classify a topic into an actionable recommendation.
150///
151/// - `double_down`: ≥3 posts and avg > 1.5× overall avg
152/// - `reduce`: ≥3 posts and avg < 0.5× overall avg
153/// - `experiment`: < 3 posts (insufficient data)
154/// - `maintain`: everything else
155fn classify_topic(post_count: i64, performance_vs_average: f64) -> String {
156    if post_count < 3 {
157        "experiment".to_string()
158    } else if performance_vs_average > 1.5 {
159        "double_down".to_string()
160    } else if performance_vs_average < 0.5 {
161        "reduce".to_string()
162    } else {
163        "maintain".to_string()
164    }
165}
tuitbot_core/context/topics.rs

tuitbot_core/context/
topics.rs
