microclaw_engine/internal/storage/db/
chats.rs

1use super::*;
2
3#[derive(Debug, Clone)]
4pub struct StoredMessage {
5    pub id: String,
6    pub chat_id: i64,
7    pub sender_name: String,
8    pub content: String,
9    pub is_from_bot: bool,
10    pub timestamp: String,
11}
12
13#[derive(Debug, Clone)]
14pub struct ChatSummary {
15    pub chat_id: i64,
16    pub chat_title: Option<String>,
17    pub session_label: Option<String>,
18    pub chat_type: String,
19    pub last_message_time: String,
20    pub last_message_preview: Option<String>,
21}
22
23impl Database {
24    pub fn upsert_chat(
25        &self,
26        chat_id: i64,
27        chat_title: Option<&str>,
28        chat_type: &str,
29    ) -> Result<(), MicroClawError> {
30        let conn = self.lock_conn();
31        let now = chrono::Utc::now().to_rfc3339();
32        conn.execute(
33            "INSERT INTO chats (chat_id, chat_title, chat_type, last_message_time, channel, external_chat_id)
34             VALUES (?1, ?2, ?3, ?4, ?5, ?6)
35             ON CONFLICT(chat_id) DO UPDATE SET
36                chat_title = COALESCE(?2, chat_title),
37                chat_type = ?3,
38                last_message_time = ?4,
39                channel = COALESCE(channel, ?5),
40                external_chat_id = COALESCE(external_chat_id, ?6)",
41            params![
42                chat_id,
43                chat_title,
44                chat_type,
45                now,
46                infer_channel_from_chat_type(chat_type),
47                chat_id.to_string()
48            ],
49        )?;
50        Ok(())
51    }
52
53    pub fn resolve_or_create_chat_id(
54        &self,
55        channel: &str,
56        external_chat_id: &str,
57        chat_title: Option<&str>,
58        chat_type: &str,
59    ) -> Result<i64, MicroClawError> {
60        let conn = self.lock_conn();
61        let now = chrono::Utc::now().to_rfc3339();
62
63        if let Some(chat_id) = conn
64            .query_row(
65                "SELECT chat_id FROM chats WHERE channel = ?1 AND external_chat_id = ?2 LIMIT 1",
66                params![channel, external_chat_id],
67                |row| row.get::<_, i64>(0),
68            )
69            .optional()?
70        {
71            conn.execute(
72                "UPDATE chats
73                 SET chat_title = COALESCE(?2, chat_title),
74                     chat_type = ?3,
75                     last_message_time = ?4
76                 WHERE chat_id = ?1",
77                params![chat_id, chat_title, chat_type, now],
78            )?;
79            return Ok(chat_id);
80        }
81
82        let preferred_chat_id = external_chat_id.parse::<i64>().ok();
83        if let Some(cid) = preferred_chat_id {
84            let occupied = conn
85                .query_row(
86                    "SELECT 1 FROM chats WHERE chat_id = ?1 LIMIT 1",
87                    params![cid],
88                    |_| Ok(()),
89                )
90                .optional()?
91                .is_some();
92            if !occupied {
93                conn.execute(
94                    "INSERT INTO chats(chat_id, chat_title, chat_type, last_message_time, channel, external_chat_id)
95                     VALUES(?1, ?2, ?3, ?4, ?5, ?6)",
96                    params![cid, chat_title, chat_type, now, channel, external_chat_id],
97                )?;
98                return Ok(cid);
99            }
100        }
101
102        conn.execute(
103            "INSERT INTO chats(chat_title, chat_type, last_message_time, channel, external_chat_id)
104             VALUES(?1, ?2, ?3, ?4, ?5)",
105            params![chat_title, chat_type, now, channel, external_chat_id],
106        )?;
107        Ok(conn.last_insert_rowid())
108    }
109
110    pub fn find_chat_id(
111        &self,
112        channel: &str,
113        external_chat_id: &str,
114    ) -> Result<Option<i64>, MicroClawError> {
115        let conn = self.lock_conn();
116        conn.query_row(
117            "SELECT chat_id FROM chats WHERE channel = ?1 AND external_chat_id = ?2 LIMIT 1",
118            params![channel, external_chat_id],
119            |row| row.get::<_, i64>(0),
120        )
121        .optional()
122        .map_err(Into::into)
123    }
124
125    pub fn store_message(&self, msg: &StoredMessage) -> Result<(), MicroClawError> {
126        let conn = self.lock_conn();
127        conn.execute(
128            "INSERT OR REPLACE INTO messages (id, chat_id, sender_name, content, is_from_bot, timestamp)
129             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
130            params![
131                msg.id,
132                msg.chat_id,
133                msg.sender_name,
134                msg.content,
135                msg.is_from_bot as i32,
136                msg.timestamp,
137            ],
138        )?;
139        Ok(())
140    }
141
142    pub fn store_message_if_new(&self, msg: &StoredMessage) -> Result<bool, MicroClawError> {
143        let conn = self.lock_conn();
144        let affected = conn.execute(
145            "INSERT OR IGNORE INTO messages (id, chat_id, sender_name, content, is_from_bot, timestamp)
146             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
147            params![
148                msg.id,
149                msg.chat_id,
150                msg.sender_name,
151                msg.content,
152                msg.is_from_bot as i32,
153                msg.timestamp,
154            ],
155        )?;
156        Ok(affected > 0)
157    }
158
159    pub fn message_exists(&self, chat_id: i64, message_id: &str) -> Result<bool, MicroClawError> {
160        let conn = self.lock_conn();
161        let exists = conn
162            .query_row(
163                "SELECT 1 FROM messages WHERE chat_id = ?1 AND id = ?2 LIMIT 1",
164                params![chat_id, message_id],
165                |_| Ok(()),
166            )
167            .optional()?
168            .is_some();
169        Ok(exists)
170    }
171
172    pub fn get_recent_messages(
173        &self,
174        chat_id: i64,
175        limit: usize,
176    ) -> Result<Vec<StoredMessage>, MicroClawError> {
177        let conn = self.lock_conn();
178        let mut stmt = conn.prepare(
179            "SELECT id, chat_id, sender_name, content, is_from_bot, timestamp
180             FROM messages
181             WHERE chat_id = ?1
182             ORDER BY timestamp DESC
183             LIMIT ?2",
184        )?;
185
186        let messages = stmt
187            .query_map(params![chat_id, limit as i64], |row| {
188                Ok(StoredMessage {
189                    id: row.get(0)?,
190                    chat_id: row.get(1)?,
191                    sender_name: row.get(2)?,
192                    content: row.get(3)?,
193                    is_from_bot: row.get::<_, i32>(4)? != 0,
194                    timestamp: row.get(5)?,
195                })
196            })?
197            .collect::<Result<Vec<_>, _>>()?;
198
199        // Reverse so oldest first
200        let mut messages = messages;
201        messages.reverse();
202        Ok(messages)
203    }
204
205    pub fn get_all_messages(&self, chat_id: i64) -> Result<Vec<StoredMessage>, MicroClawError> {
206        let conn = self.lock_conn();
207        let mut stmt = conn.prepare(
208            "SELECT id, chat_id, sender_name, content, is_from_bot, timestamp
209             FROM messages
210             WHERE chat_id = ?1
211             ORDER BY timestamp ASC",
212        )?;
213        let messages = stmt
214            .query_map(params![chat_id], |row| {
215                Ok(StoredMessage {
216                    id: row.get(0)?,
217                    chat_id: row.get(1)?,
218                    sender_name: row.get(2)?,
219                    content: row.get(3)?,
220                    is_from_bot: row.get::<_, i32>(4)? != 0,
221                    timestamp: row.get(5)?,
222                })
223            })?
224            .collect::<Result<Vec<_>, _>>()?;
225        Ok(messages)
226    }
227
228    pub fn get_chats_by_type(
229        &self,
230        chat_type: &str,
231        limit: usize,
232    ) -> Result<Vec<ChatSummary>, MicroClawError> {
233        let conn = self.lock_conn();
234        let mut stmt = conn.prepare(
235            "SELECT
236                c.chat_id,
237                c.chat_title,
238                s.label,
239                c.chat_type,
240                c.last_message_time,
241                (
242                    SELECT m.content
243                    FROM messages m
244                    WHERE m.chat_id = c.chat_id
245                    ORDER BY m.timestamp DESC
246                    LIMIT 1
247                ) AS last_message_preview
248             FROM chats c
249             LEFT JOIN sessions s ON s.chat_id = c.chat_id
250             WHERE c.chat_type = ?1
251             ORDER BY c.last_message_time DESC
252             LIMIT ?2",
253        )?;
254        let chats = stmt
255            .query_map(params![chat_type, limit as i64], |row| {
256                Ok(ChatSummary {
257                    chat_id: row.get(0)?,
258                    chat_title: row.get(1)?,
259                    session_label: row.get(2)?,
260                    chat_type: row.get(3)?,
261                    last_message_time: row.get(4)?,
262                    last_message_preview: row.get(5)?,
263                })
264            })?
265            .collect::<Result<Vec<_>, _>>()?;
266        Ok(chats)
267    }
268
269    pub fn get_recent_chats(&self, limit: usize) -> Result<Vec<ChatSummary>, MicroClawError> {
270        let conn = self.lock_conn();
271        let mut stmt = conn.prepare(
272            "SELECT
273                c.chat_id,
274                c.chat_title,
275                s.label,
276                c.chat_type,
277                c.last_message_time,
278                (
279                    SELECT m.content
280                    FROM messages m
281                    WHERE m.chat_id = c.chat_id
282                    ORDER BY m.timestamp DESC
283                    LIMIT 1
284                ) AS last_message_preview
285             FROM chats c
286             LEFT JOIN sessions s ON s.chat_id = c.chat_id
287             ORDER BY c.last_message_time DESC
288             LIMIT ?1",
289        )?;
290        let chats = stmt
291            .query_map(params![limit as i64], |row| {
292                Ok(ChatSummary {
293                    chat_id: row.get(0)?,
294                    chat_title: row.get(1)?,
295                    session_label: row.get(2)?,
296                    chat_type: row.get(3)?,
297                    last_message_time: row.get(4)?,
298                    last_message_preview: row.get(5)?,
299                })
300            })?
301            .collect::<Result<Vec<_>, _>>()?;
302        Ok(chats)
303    }
304
305    pub fn get_chat_type(&self, chat_id: i64) -> Result<Option<String>, MicroClawError> {
306        let conn = self.lock_conn();
307        let result = conn.query_row(
308            "SELECT chat_type FROM chats WHERE chat_id = ?1",
309            params![chat_id],
310            |row| row.get::<_, String>(0),
311        );
312        match result {
313            Ok(v) => Ok(Some(v)),
314            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
315            Err(e) => Err(e.into()),
316        }
317    }
318
319    pub fn get_chat_id_by_channel_and_title(
320        &self,
321        channel: &str,
322        chat_title: &str,
323    ) -> Result<Option<i64>, MicroClawError> {
324        let conn = self.lock_conn();
325        let result = conn.query_row(
326            "SELECT chat_id
327             FROM chats
328             WHERE channel = ?1 AND chat_title = ?2
329             ORDER BY last_message_time DESC
330             LIMIT 1",
331            params![channel, chat_title],
332            |row| row.get::<_, i64>(0),
333        );
334        match result {
335            Ok(v) => Ok(Some(v)),
336            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
337            Err(e) => Err(e.into()),
338        }
339    }
340
341    pub fn get_chat_channel(&self, chat_id: i64) -> Result<Option<String>, MicroClawError> {
342        let conn = self.lock_conn();
343        let result = conn.query_row(
344            "SELECT channel FROM chats WHERE chat_id = ?1",
345            params![chat_id],
346            |row| row.get::<_, Option<String>>(0),
347        );
348        match result {
349            Ok(v) => Ok(v),
350            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
351            Err(e) => Err(e.into()),
352        }
353    }
354
355    pub fn get_chat_external_id(&self, chat_id: i64) -> Result<Option<String>, MicroClawError> {
356        let conn = self.lock_conn();
357        let result = conn.query_row(
358            "SELECT external_chat_id FROM chats WHERE chat_id = ?1",
359            params![chat_id],
360            |row| row.get::<_, Option<String>>(0),
361        );
362        match result {
363            Ok(v) => Ok(v),
364            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
365            Err(e) => Err(e.into()),
366        }
367    }
368
369    /// Get messages since the bot's last response in this chat.
370    /// Falls back to `fallback_limit` most recent messages if bot never responded.
371    pub fn get_messages_since_last_bot_response(
372        &self,
373        chat_id: i64,
374        max: usize,
375        fallback: usize,
376    ) -> Result<Vec<StoredMessage>, MicroClawError> {
377        let conn = self.lock_conn();
378
379        // Find timestamp of last bot message
380        let last_bot_ts: Option<String> = conn
381            .query_row(
382                "SELECT timestamp FROM messages
383                 WHERE chat_id = ?1 AND is_from_bot = 1
384                 ORDER BY timestamp DESC LIMIT 1",
385                params![chat_id],
386                |row| row.get(0),
387            )
388            .ok();
389
390        let mut messages = if let Some(ts) = last_bot_ts {
391            let mut stmt = conn.prepare(
392                "SELECT id, chat_id, sender_name, content, is_from_bot, timestamp
393                 FROM messages
394                 WHERE chat_id = ?1 AND timestamp >= ?2
395                 ORDER BY timestamp DESC
396                 LIMIT ?3",
397            )?;
398            let rows = stmt
399                .query_map(params![chat_id, ts, max as i64], |row| {
400                    Ok(StoredMessage {
401                        id: row.get(0)?,
402                        chat_id: row.get(1)?,
403                        sender_name: row.get(2)?,
404                        content: row.get(3)?,
405                        is_from_bot: row.get::<_, i32>(4)? != 0,
406                        timestamp: row.get(5)?,
407                    })
408                })?
409                .collect::<Result<Vec<_>, _>>()?;
410            rows
411        } else {
412            let mut stmt = conn.prepare(
413                "SELECT id, chat_id, sender_name, content, is_from_bot, timestamp
414                 FROM messages
415                 WHERE chat_id = ?1
416                 ORDER BY timestamp DESC
417                 LIMIT ?2",
418            )?;
419            let rows = stmt
420                .query_map(params![chat_id, fallback as i64], |row| {
421                    Ok(StoredMessage {
422                        id: row.get(0)?,
423                        chat_id: row.get(1)?,
424                        sender_name: row.get(2)?,
425                        content: row.get(3)?,
426                        is_from_bot: row.get::<_, i32>(4)? != 0,
427                        timestamp: row.get(5)?,
428                    })
429                })?
430                .collect::<Result<Vec<_>, _>>()?;
431            rows
432        };
433
434        messages.reverse();
435        Ok(messages)
436    }
437
438    // --- Scheduled tasks ---
439
440    /// Clear all resettable chat state without deleting chat metadata or memories.
441    /// This removes resumable session state, historical messages, and scheduled task state.
442    pub fn clear_chat_context(&self, chat_id: i64) -> Result<bool, MicroClawError> {
443        let conn = self.lock_conn();
444        let tx = conn.unchecked_transaction()?;
445        let mut affected = 0usize;
446        affected += tx.execute(
447            "DELETE FROM task_run_logs WHERE chat_id = ?1",
448            params![chat_id],
449        )?;
450        affected += tx.execute(
451            "DELETE FROM scheduled_task_dlq WHERE chat_id = ?1",
452            params![chat_id],
453        )?;
454        affected += tx.execute(
455            "DELETE FROM scheduled_tasks WHERE chat_id = ?1",
456            params![chat_id],
457        )?;
458        affected += tx.execute("DELETE FROM sessions WHERE chat_id = ?1", params![chat_id])?;
459        affected += tx.execute("DELETE FROM messages WHERE chat_id = ?1", params![chat_id])?;
460        tx.commit()?;
461        Ok(affected > 0)
462    }
463
464    /// Clear conversational context for a chat while preserving scheduled task state.
465    /// This removes resumable session state and historical messages only.
466    pub fn clear_chat_conversation(&self, chat_id: i64) -> Result<bool, MicroClawError> {
467        let conn = self.lock_conn();
468        let tx = conn.unchecked_transaction()?;
469        let mut affected = 0usize;
470        affected += tx.execute("DELETE FROM sessions WHERE chat_id = ?1", params![chat_id])?;
471        affected += tx.execute("DELETE FROM messages WHERE chat_id = ?1", params![chat_id])?;
472        tx.commit()?;
473        Ok(affected > 0)
474    }
475
476    pub fn delete_chat_data(&self, chat_id: i64) -> Result<bool, MicroClawError> {
477        let conn = self.lock_conn();
478        let tx = conn.unchecked_transaction()?;
479        let mut affected = 0usize;
480
481        affected += erase_chat_learning(&tx, chat_id)?;
482        affected += tx.execute(
483            "DELETE FROM llm_usage_logs WHERE chat_id = ?1",
484            params![chat_id],
485        )?;
486        affected += tx.execute("DELETE FROM sessions WHERE chat_id = ?1", params![chat_id])?;
487        affected += tx.execute("DELETE FROM messages WHERE chat_id = ?1", params![chat_id])?;
488        affected += tx.execute(
489            "DELETE FROM scheduled_tasks WHERE chat_id = ?1",
490            params![chat_id],
491        )?;
492        affected += tx.execute(
493            "DELETE FROM memory_reflector_state WHERE chat_id = ?1",
494            params![chat_id],
495        )?;
496        affected += tx.execute(
497            "DELETE FROM memory_reflector_runs WHERE chat_id = ?1",
498            params![chat_id],
499        )?;
500        affected += tx.execute(
501            "DELETE FROM memory_injection_logs WHERE chat_id = ?1",
502            params![chat_id],
503        )?;
504        affected += tx.execute(
505            "DELETE FROM memory_supersede_edges
506             WHERE from_memory_id IN (SELECT id FROM memories WHERE chat_id = ?1)
507                OR to_memory_id IN (SELECT id FROM memories WHERE chat_id = ?1)",
508            params![chat_id],
509        )?;
510        affected += tx.execute("DELETE FROM memories WHERE chat_id = ?1", params![chat_id])?;
511        affected += tx.execute("DELETE FROM chats WHERE chat_id = ?1", params![chat_id])?;
512
513        tx.commit()?;
514        Ok(affected > 0)
515    }
516
517    // --- Auth: password/session/api-key ---
518
519    pub fn get_new_user_messages_since(
520        &self,
521        chat_id: i64,
522        since: &str,
523    ) -> Result<Vec<StoredMessage>, MicroClawError> {
524        let conn = self.lock_conn();
525        let mut stmt = conn.prepare(
526            "SELECT id, chat_id, sender_name, content, is_from_bot, timestamp
527             FROM messages
528             WHERE chat_id = ?1 AND timestamp > ?2 AND is_from_bot = 0
529             ORDER BY timestamp ASC",
530        )?;
531        let messages = stmt
532            .query_map(params![chat_id, since], |row| {
533                Ok(StoredMessage {
534                    id: row.get(0)?,
535                    chat_id: row.get(1)?,
536                    sender_name: row.get(2)?,
537                    content: row.get(3)?,
538                    is_from_bot: row.get::<_, i32>(4)? != 0,
539                    timestamp: row.get(5)?,
540                })
541            })?
542            .collect::<Result<Vec<_>, _>>()?;
543        Ok(messages)
544    }
545
546    pub fn get_messages_since(
547        &self,
548        chat_id: i64,
549        since: &str,
550        limit: usize,
551    ) -> Result<Vec<StoredMessage>, MicroClawError> {
552        let conn = self.lock_conn();
553        let mut stmt = conn.prepare(
554            "SELECT id, chat_id, sender_name, content, is_from_bot, timestamp
555             FROM messages
556             WHERE chat_id = ?1 AND timestamp > ?2
557             ORDER BY timestamp ASC
558             LIMIT ?3",
559        )?;
560        let messages = stmt
561            .query_map(params![chat_id, since, limit as i64], |row| {
562                Ok(StoredMessage {
563                    id: row.get(0)?,
564                    chat_id: row.get(1)?,
565                    sender_name: row.get(2)?,
566                    content: row.get(3)?,
567                    is_from_bot: row.get::<_, i32>(4)? != 0,
568                    timestamp: row.get(5)?,
569                })
570            })?
571            .collect::<Result<Vec<_>, _>>()?;
572        Ok(messages)
573    }
574
575    /// Full-text search over stored messages using the SQLite FTS5 index.
576    ///
577    /// `query` must be a valid FTS5 match expression (simple words are OK,
578    /// e.g. `"rust async"`). Returns ranked matches newest-first for
579    /// equally-ranked rows. When `chat_id` is `Some`, the search is scoped to
580    /// that chat; otherwise it spans all chats. When `since` is `Some`, only
581    /// messages with timestamp >= that value are returned. Results are
582    /// truncated to `limit` rows.
583    pub fn search_messages_fts(
584        &self,
585        query: &str,
586        chat_id: Option<i64>,
587        since: Option<&str>,
588        limit: usize,
589    ) -> Result<Vec<StoredMessage>, MicroClawError> {
590        if query.trim().is_empty() {
591            return Ok(Vec::new());
592        }
593        let limit = limit.clamp(1, 200) as i64;
594        let conn = self.lock_conn();
595
596        let mut sql = String::from(
597            "SELECT m.id, m.chat_id, m.sender_name, m.content, m.is_from_bot, m.timestamp
598             FROM messages_fts f
599             JOIN messages m ON m.id = f.message_id AND m.chat_id = f.chat_id
600             WHERE f.content MATCH ?1",
601        );
602        let mut binds: Vec<Box<dyn rusqlite::ToSql>> = vec![Box::new(query.to_string())];
603        if let Some(cid) = chat_id {
604            sql.push_str(" AND m.chat_id = ?");
605            sql.push_str(&(binds.len() + 1).to_string());
606            binds.push(Box::new(cid));
607        }
608        if let Some(ts) = since {
609            sql.push_str(" AND m.timestamp >= ?");
610            sql.push_str(&(binds.len() + 1).to_string());
611            binds.push(Box::new(ts.to_string()));
612        }
613        sql.push_str(" ORDER BY bm25(messages_fts), m.timestamp DESC LIMIT ?");
614        sql.push_str(&(binds.len() + 1).to_string());
615        binds.push(Box::new(limit));
616
617        let mut stmt = conn.prepare(&sql)?;
618        let bind_refs: Vec<&dyn rusqlite::ToSql> = binds.iter().map(|b| b.as_ref()).collect();
619        let messages = stmt
620            .query_map(
621                rusqlite::params_from_iter(bind_refs.iter().copied()),
622                |row| {
623                    Ok(StoredMessage {
624                        id: row.get(0)?,
625                        chat_id: row.get(1)?,
626                        sender_name: row.get(2)?,
627                        content: row.get(3)?,
628                        is_from_bot: row.get::<_, i32>(4)? != 0,
629                        timestamp: row.get(5)?,
630                    })
631                },
632            )?
633            .collect::<Result<Vec<_>, _>>()?;
634        Ok(messages)
635    }
636
637    /// Total number of stored messages in a chat (both sides). Used as a cheap
638    /// proxy for how well the bot "knows" this person.
639    pub fn count_messages_for_chat(&self, chat_id: i64) -> Result<i64, MicroClawError> {
640        let conn = self.lock_conn();
641        conn.query_row(
642            "SELECT COUNT(*) FROM messages WHERE chat_id = ?1",
643            params![chat_id],
644            |row| row.get(0),
645        )
646        .map_err(Into::into)
647    }
648
649    pub fn get_active_chat_ids_since(&self, since: &str) -> Result<Vec<i64>, MicroClawError> {
650        let conn = self.lock_conn();
651        let mut stmt = conn.prepare(
652            "SELECT DISTINCT chat_id FROM messages WHERE timestamp > ?1 AND is_from_bot = 0",
653        )?;
654        let ids = stmt
655            .query_map(params![since], |row| row.get::<_, i64>(0))?
656            .collect::<Result<Vec<_>, _>>()?;
657        Ok(ids)
658    }
659
660    /// Chats whose most recent message is older than `cutoff` (i.e. idle since
661    /// then). Only chats that have ever had a message are returned.
662    pub fn list_idle_chats(&self, cutoff: &str, limit: usize) -> Result<Vec<i64>, MicroClawError> {
663        let conn = self.lock_conn();
664        let mut stmt = conn.prepare(
665            "SELECT chat_id FROM messages
666             GROUP BY chat_id
667             HAVING MAX(timestamp) < ?1
668             LIMIT ?2",
669        )?;
670        let ids = stmt
671            .query_map(params![cutoff, limit.max(1) as i64], |row| {
672                row.get::<_, i64>(0)
673            })?
674            .collect::<Result<Vec<_>, _>>()?;
675        Ok(ids)
676    }
677}
678
679#[cfg(test)]
680mod tests {
681    use super::*;
682    use crate::internal::storage::db::test_support::*;
683
684    #[test]
685    fn test_new_database_creates_tables() {
686        let (db, dir) = test_db();
687        // Verify we can do basic operations without errors
688        let msgs = db.get_recent_messages(1, 10).unwrap();
689        assert!(msgs.is_empty());
690        let tasks = db.get_due_tasks("2099-01-01T00:00:00Z").unwrap();
691        assert!(tasks.is_empty());
692        cleanup(&dir);
693    }
694
695    #[test]
696    fn test_upsert_chat_insert_and_update() {
697        let (db, dir) = test_db();
698        db.upsert_chat(100, Some("Test Chat"), "group").unwrap();
699        // Update title
700        db.upsert_chat(100, Some("New Title"), "group").unwrap();
701        // Insert without title
702        db.upsert_chat(200, None, "private").unwrap();
703        cleanup(&dir);
704    }
705
706    #[test]
707    fn test_store_and_retrieve_message() {
708        let (db, dir) = test_db();
709        let msg = StoredMessage {
710            id: "msg1".into(),
711            chat_id: 100,
712            sender_name: "alice".into(),
713            content: "hello".into(),
714            is_from_bot: false,
715            timestamp: "2024-01-01T00:00:00Z".into(),
716        };
717        db.store_message(&msg).unwrap();
718
719        let messages = db.get_recent_messages(100, 10).unwrap();
720        assert_eq!(messages.len(), 1);
721        assert_eq!(messages[0].id, "msg1");
722        assert_eq!(messages[0].sender_name, "alice");
723        assert_eq!(messages[0].content, "hello");
724        assert!(!messages[0].is_from_bot);
725        cleanup(&dir);
726    }
727
728    #[test]
729    fn test_search_messages_fts_basic() {
730        let (db, dir) = test_db();
731        let now = chrono::Utc::now();
732        let messages = [
733            ("chat-1", 101, "alice", "Rust async futures are awesome"),
734            ("chat-2", 101, "bot", "I agree, tokio makes them easy"),
735            (
736                "chat-3",
737                101,
738                "alice",
739                "Let's talk about JavaScript promises instead",
740            ),
741            ("chat-4", 202, "bob", "Discussing Rust borrow checker"),
742        ];
743        for (i, (id, chat, sender, content)) in messages.iter().enumerate() {
744            let msg = StoredMessage {
745                id: (*id).into(),
746                chat_id: *chat,
747                sender_name: (*sender).into(),
748                content: (*content).into(),
749                is_from_bot: *sender == "bot",
750                timestamp: (now + chrono::Duration::seconds(i as i64)).to_rfc3339(),
751            };
752            db.store_message(&msg).unwrap();
753        }
754
755        let rust_hits = db.search_messages_fts("rust", None, None, 10).unwrap();
756        assert!(rust_hits.len() >= 2, "expected at least 2 rust matches");
757        assert!(rust_hits
758            .iter()
759            .all(|m| m.content.to_lowercase().contains("rust")));
760
761        let scoped = db.search_messages_fts("rust", Some(101), None, 10).unwrap();
762        assert!(scoped.iter().all(|m| m.chat_id == 101));
763
764        let empty = db.search_messages_fts("", None, None, 10).unwrap();
765        assert!(empty.is_empty());
766
767        let no_match = db
768            .search_messages_fts("nothingmatchesthis", None, None, 10)
769            .unwrap();
770        assert!(no_match.is_empty());
771
772        // Delete and confirm the FTS row is also removed via trigger.
773        {
774            let conn = db.lock_conn();
775            conn.execute(
776                "DELETE FROM messages WHERE id = ?1 AND chat_id = ?2",
777                params!["chat-1", 101i64],
778            )
779            .unwrap();
780        }
781        let after_delete = db.search_messages_fts("awesome", None, None, 10).unwrap();
782        assert!(after_delete.is_empty());
783
784        cleanup(&dir);
785    }
786
787    #[test]
788    fn test_store_message_upsert() {
789        let (db, dir) = test_db();
790        let msg = StoredMessage {
791            id: "msg1".into(),
792            chat_id: 100,
793            sender_name: "alice".into(),
794            content: "original".into(),
795            is_from_bot: false,
796            timestamp: "2024-01-01T00:00:00Z".into(),
797        };
798        db.store_message(&msg).unwrap();
799
800        // Store same id again with different content (INSERT OR REPLACE)
801        let msg2 = StoredMessage {
802            id: "msg1".into(),
803            chat_id: 100,
804            sender_name: "alice".into(),
805            content: "updated".into(),
806            is_from_bot: false,
807            timestamp: "2024-01-01T00:00:01Z".into(),
808        };
809        db.store_message(&msg2).unwrap();
810
811        let messages = db.get_recent_messages(100, 10).unwrap();
812        assert_eq!(messages.len(), 1);
813        assert_eq!(messages[0].content, "updated");
814        cleanup(&dir);
815    }
816
817    #[test]
818    fn test_message_exists() {
819        let (db, dir) = test_db();
820        assert!(!db.message_exists(100, "msg1").unwrap());
821
822        db.store_message(&StoredMessage {
823            id: "msg1".into(),
824            chat_id: 100,
825            sender_name: "alice".into(),
826            content: "hello".into(),
827            is_from_bot: false,
828            timestamp: "2024-01-01T00:00:00Z".into(),
829        })
830        .unwrap();
831
832        assert!(db.message_exists(100, "msg1").unwrap());
833        assert!(!db.message_exists(100, "msg2").unwrap());
834        assert!(!db.message_exists(200, "msg1").unwrap());
835        cleanup(&dir);
836    }
837
838    #[test]
839    fn test_store_message_if_new() {
840        let (db, dir) = test_db();
841        let msg = StoredMessage {
842            id: "msg-new".into(),
843            chat_id: 100,
844            sender_name: "alice".into(),
845            content: "hello".into(),
846            is_from_bot: false,
847            timestamp: "2024-01-01T00:00:00Z".into(),
848        };
849        assert!(db.store_message_if_new(&msg).unwrap());
850        assert!(!db.store_message_if_new(&msg).unwrap());
851        cleanup(&dir);
852    }
853
854    #[test]
855    fn test_get_recent_messages_ordering_and_limit() {
856        let (db, dir) = test_db();
857        for i in 0..5 {
858            let msg = StoredMessage {
859                id: format!("msg{i}"),
860                chat_id: 100,
861                sender_name: "alice".into(),
862                content: format!("message {i}"),
863                is_from_bot: false,
864                timestamp: format!("2024-01-01T00:00:0{i}Z"),
865            };
866            db.store_message(&msg).unwrap();
867        }
868
869        // Limit to 3 - should get the 3 most recent, but reversed to oldest-first
870        let messages = db.get_recent_messages(100, 3).unwrap();
871        assert_eq!(messages.len(), 3);
872        assert_eq!(messages[0].content, "message 2"); // oldest of the 3 most recent
873        assert_eq!(messages[1].content, "message 3");
874        assert_eq!(messages[2].content, "message 4"); // most recent
875
876        // Different chat_id should be empty
877        let messages = db.get_recent_messages(200, 10).unwrap();
878        assert!(messages.is_empty());
879        cleanup(&dir);
880    }
881
882    #[test]
883    fn test_get_messages_since_last_bot_response_with_bot_msg() {
884        let (db, dir) = test_db();
885
886        // User message 1
887        db.store_message(&StoredMessage {
888            id: "m1".into(),
889            chat_id: 100,
890            sender_name: "alice".into(),
891            content: "hi".into(),
892            is_from_bot: false,
893            timestamp: "2024-01-01T00:00:01Z".into(),
894        })
895        .unwrap();
896
897        // Bot response
898        db.store_message(&StoredMessage {
899            id: "m2".into(),
900            chat_id: 100,
901            sender_name: "bot".into(),
902            content: "hello!".into(),
903            is_from_bot: true,
904            timestamp: "2024-01-01T00:00:02Z".into(),
905        })
906        .unwrap();
907
908        // User message 2 (after bot response)
909        db.store_message(&StoredMessage {
910            id: "m3".into(),
911            chat_id: 100,
912            sender_name: "alice".into(),
913            content: "how are you?".into(),
914            is_from_bot: false,
915            timestamp: "2024-01-01T00:00:03Z".into(),
916        })
917        .unwrap();
918
919        // User message 3
920        db.store_message(&StoredMessage {
921            id: "m4".into(),
922            chat_id: 100,
923            sender_name: "bob".into(),
924            content: "me too".into(),
925            is_from_bot: false,
926            timestamp: "2024-01-01T00:00:04Z".into(),
927        })
928        .unwrap();
929
930        let messages = db
931            .get_messages_since_last_bot_response(100, 50, 10)
932            .unwrap();
933        // Should include the bot message and everything after it
934        assert!(messages.len() >= 2);
935        // First should be the bot msg or after it
936        assert_eq!(messages[0].id, "m2"); // the bot message (timestamp >= bot's timestamp)
937        assert_eq!(messages[1].id, "m3");
938        assert_eq!(messages[2].id, "m4");
939        cleanup(&dir);
940    }
941
942    #[test]
943    fn test_get_messages_since_last_bot_response_no_bot_msg() {
944        let (db, dir) = test_db();
945
946        for i in 0..5 {
947            db.store_message(&StoredMessage {
948                id: format!("m{i}"),
949                chat_id: 100,
950                sender_name: "alice".into(),
951                content: format!("msg {i}"),
952                is_from_bot: false,
953                timestamp: format!("2024-01-01T00:00:0{i}Z"),
954            })
955            .unwrap();
956        }
957
958        // Fallback to last 3
959        let messages = db.get_messages_since_last_bot_response(100, 50, 3).unwrap();
960        assert_eq!(messages.len(), 3);
961        assert_eq!(messages[0].content, "msg 2");
962        assert_eq!(messages[2].content, "msg 4");
963        cleanup(&dir);
964    }
965
966    #[test]
967    fn test_get_all_messages() {
968        let (db, dir) = test_db();
969        for i in 0..5 {
970            db.store_message(&StoredMessage {
971                id: format!("msg{i}"),
972                chat_id: 100,
973                sender_name: "alice".into(),
974                content: format!("message {i}"),
975                is_from_bot: false,
976                timestamp: format!("2024-01-01T00:00:0{i}Z"),
977            })
978            .unwrap();
979        }
980
981        let messages = db.get_all_messages(100).unwrap();
982        assert_eq!(messages.len(), 5);
983        assert_eq!(messages[0].content, "message 0");
984        assert_eq!(messages[4].content, "message 4");
985
986        // Different chat should be empty
987        assert!(db.get_all_messages(200).unwrap().is_empty());
988        cleanup(&dir);
989    }
990
991    #[test]
992    fn test_clear_chat_memory_removes_memories_but_keeps_conversation() {
993        let (db, dir) = test_db();
994        db.upsert_chat(100, Some("chat-100"), "private").unwrap();
995        db.save_session(100, r#"[{"role":"user","content":"hi"}]"#)
996            .unwrap();
997        db.store_message(&StoredMessage {
998            id: "m1".into(),
999            chat_id: 100,
1000            sender_name: "alice".into(),
1001            content: "hello".into(),
1002            is_from_bot: false,
1003            timestamp: "2024-01-01T00:00:01Z".into(),
1004        })
1005        .unwrap();
1006        db.insert_memory(Some(100), "User likes Rust", "PROFILE")
1007            .unwrap();
1008
1009        assert!(db.clear_chat_memory(100).unwrap());
1010        assert!(db.search_memories(100, "Rust", 10).unwrap().is_empty());
1011        assert!(db.load_session(100).unwrap().is_some());
1012        assert!(!db.get_recent_messages(100, 10).unwrap().is_empty());
1013        assert!(db.get_chat_type(100).unwrap().is_some());
1014
1015        cleanup(&dir);
1016    }
1017
1018    #[test]
1019    fn test_get_new_user_messages_since() {
1020        let (db, dir) = test_db();
1021
1022        // Messages before the cutoff
1023        db.store_message(&StoredMessage {
1024            id: "m1".into(),
1025            chat_id: 100,
1026            sender_name: "alice".into(),
1027            content: "old msg".into(),
1028            is_from_bot: false,
1029            timestamp: "2024-01-01T00:00:01Z".into(),
1030        })
1031        .unwrap();
1032
1033        // Bot message at the cutoff
1034        db.store_message(&StoredMessage {
1035            id: "m2".into(),
1036            chat_id: 100,
1037            sender_name: "bot".into(),
1038            content: "response".into(),
1039            is_from_bot: true,
1040            timestamp: "2024-01-01T00:00:02Z".into(),
1041        })
1042        .unwrap();
1043
1044        // User messages after cutoff
1045        db.store_message(&StoredMessage {
1046            id: "m3".into(),
1047            chat_id: 100,
1048            sender_name: "alice".into(),
1049            content: "new msg 1".into(),
1050            is_from_bot: false,
1051            timestamp: "2024-01-01T00:00:03Z".into(),
1052        })
1053        .unwrap();
1054
1055        db.store_message(&StoredMessage {
1056            id: "m4".into(),
1057            chat_id: 100,
1058            sender_name: "bob".into(),
1059            content: "new msg 2".into(),
1060            is_from_bot: false,
1061            timestamp: "2024-01-01T00:00:04Z".into(),
1062        })
1063        .unwrap();
1064
1065        // Bot message after cutoff (should be excluded - only non-bot)
1066        db.store_message(&StoredMessage {
1067            id: "m5".into(),
1068            chat_id: 100,
1069            sender_name: "bot".into(),
1070            content: "bot again".into(),
1071            is_from_bot: true,
1072            timestamp: "2024-01-01T00:00:05Z".into(),
1073        })
1074        .unwrap();
1075
1076        let msgs = db
1077            .get_new_user_messages_since(100, "2024-01-01T00:00:02Z")
1078            .unwrap();
1079        assert_eq!(msgs.len(), 2);
1080        assert_eq!(msgs[0].content, "new msg 1");
1081        assert_eq!(msgs[1].content, "new msg 2");
1082
1083        cleanup(&dir);
1084    }
1085
1086    #[test]
1087    fn test_get_messages_since_includes_user_and_bot() {
1088        let (db, dir) = test_db();
1089        db.store_message(&StoredMessage {
1090            id: "m1".into(),
1091            chat_id: 100,
1092            sender_name: "alice".into(),
1093            content: "old".into(),
1094            is_from_bot: false,
1095            timestamp: "2024-01-01T00:00:01Z".into(),
1096        })
1097        .unwrap();
1098        db.store_message(&StoredMessage {
1099            id: "m2".into(),
1100            chat_id: 100,
1101            sender_name: "bot".into(),
1102            content: "bot".into(),
1103            is_from_bot: true,
1104            timestamp: "2024-01-01T00:00:02Z".into(),
1105        })
1106        .unwrap();
1107        db.store_message(&StoredMessage {
1108            id: "m3".into(),
1109            chat_id: 100,
1110            sender_name: "alice".into(),
1111            content: "new".into(),
1112            is_from_bot: false,
1113            timestamp: "2024-01-01T00:00:03Z".into(),
1114        })
1115        .unwrap();
1116
1117        let msgs = db
1118            .get_messages_since(100, "2024-01-01T00:00:01Z", 10)
1119            .unwrap();
1120        assert_eq!(msgs.len(), 2);
1121        assert_eq!(msgs[0].id, "m2");
1122        assert_eq!(msgs[1].id, "m3");
1123
1124        cleanup(&dir);
1125    }
1126
1127    #[test]
1128    fn test_resolve_or_create_chat_id_channel_scoped() {
1129        let (db, dir) = test_db();
1130
1131        let tg = db
1132            .resolve_or_create_chat_id(
1133                "telegram",
1134                "12345",
1135                Some("telegram-12345"),
1136                "telegram_private",
1137            )
1138            .unwrap();
1139        let tg_again = db
1140            .resolve_or_create_chat_id(
1141                "telegram",
1142                "12345",
1143                Some("telegram-12345"),
1144                "telegram_private",
1145            )
1146            .unwrap();
1147        assert_eq!(tg, tg_again);
1148
1149        let discord = db
1150            .resolve_or_create_chat_id("discord", "12345", Some("discord-12345"), "discord")
1151            .unwrap();
1152        assert_ne!(tg, discord);
1153        assert_eq!(db.find_chat_id("telegram", "12345").unwrap(), Some(tg));
1154        assert_eq!(db.find_chat_id("discord", "12345").unwrap(), Some(discord));
1155        assert_eq!(db.find_chat_id("work", "12345").unwrap(), None);
1156        assert_eq!(
1157            db.get_chat_external_id(discord).unwrap().as_deref(),
1158            Some("12345")
1159        );
1160
1161        cleanup(&dir);
1162    }
1163
1164    #[test]
1165    fn test_upsert_chat_preserves_existing_channel_identity() {
1166        let (db, dir) = test_db();
1167
1168        let scoped_chat_id = db
1169            .resolve_or_create_chat_id(
1170                "telegram.btcpos",
1171                "12345",
1172                Some("telegram-12345"),
1173                "telegram_private",
1174            )
1175            .unwrap();
1176
1177        db.upsert_chat(scoped_chat_id, Some("Updated title"), "telegram_private")
1178            .unwrap();
1179
1180        assert_eq!(
1181            db.get_chat_channel(scoped_chat_id).unwrap().as_deref(),
1182            Some("telegram.btcpos")
1183        );
1184        assert_eq!(
1185            db.get_chat_external_id(scoped_chat_id).unwrap().as_deref(),
1186            Some("12345")
1187        );
1188
1189        let scoped_again = db
1190            .resolve_or_create_chat_id(
1191                "telegram.btcpos",
1192                "12345",
1193                Some("telegram-12345"),
1194                "telegram_private",
1195            )
1196            .unwrap();
1197        assert_eq!(scoped_chat_id, scoped_again);
1198
1199        cleanup(&dir);
1200    }
1201
1202    #[test]
1203    fn test_resolve_or_create_chat_id_scopes_same_external_id_by_channel() {
1204        let (db, dir) = test_db();
1205
1206        let default_tg = db
1207            .resolve_or_create_chat_id(
1208                "telegram",
1209                "12345",
1210                Some("telegram-12345"),
1211                "telegram_private",
1212            )
1213            .unwrap();
1214        let scoped_tg = db
1215            .resolve_or_create_chat_id(
1216                "telegram.btcpos",
1217                "12345",
1218                Some("telegram-12345"),
1219                "telegram_private",
1220            )
1221            .unwrap();
1222
1223        assert_ne!(default_tg, scoped_tg);
1224
1225        let default_tg_again = db
1226            .resolve_or_create_chat_id(
1227                "telegram",
1228                "12345",
1229                Some("telegram-12345"),
1230                "telegram_private",
1231            )
1232            .unwrap();
1233        let scoped_tg_again = db
1234            .resolve_or_create_chat_id(
1235                "telegram.btcpos",
1236                "12345",
1237                Some("telegram-12345"),
1238                "telegram_private",
1239            )
1240            .unwrap();
1241
1242        assert_eq!(default_tg, default_tg_again);
1243        assert_eq!(scoped_tg, scoped_tg_again);
1244
1245        cleanup(&dir);
1246    }
1247
1248    #[test]
1249    fn test_get_chat_id_by_channel_and_title_finds_non_recent_chat() {
1250        let (db, dir) = test_db();
1251
1252        for i in 0..5000 {
1253            db.resolve_or_create_chat_id(
1254                "web",
1255                &format!("ext-{i}"),
1256                Some(&format!("title-{i}")),
1257                "web",
1258            )
1259            .unwrap();
1260        }
1261        let target = db
1262            .resolve_or_create_chat_id("web", "legacy-ext", Some("legacy-session"), "web")
1263            .unwrap();
1264        for i in 5000..9300 {
1265            db.resolve_or_create_chat_id(
1266                "web",
1267                &format!("ext-{i}"),
1268                Some(&format!("title-{i}")),
1269                "web",
1270            )
1271            .unwrap();
1272        }
1273
1274        let found = db
1275            .get_chat_id_by_channel_and_title("web", "legacy-session")
1276            .unwrap();
1277        assert_eq!(found, Some(target));
1278
1279        cleanup(&dir);
1280    }
1281
1282    #[test]
1283    fn test_migration_backfills_chat_identity_columns() {
1284        let dir = std::env::temp_dir().join(format!(
1285            "microclaw_migration_chat_identity_{}",
1286            uuid::Uuid::new_v4()
1287        ));
1288        std::fs::create_dir_all(&dir).unwrap();
1289        let db_path = dir.join("microclaw.db");
1290        let conn = Connection::open(&db_path).unwrap();
1291        conn.execute_batch(
1292            "CREATE TABLE chats (
1293                chat_id INTEGER PRIMARY KEY,
1294                chat_title TEXT,
1295                chat_type TEXT NOT NULL DEFAULT 'private',
1296                last_message_time TEXT NOT NULL
1297            );
1298            INSERT INTO chats(chat_id, chat_title, chat_type, last_message_time)
1299            VALUES (100, 'legacy tg', 'telegram_private', '2026-01-01T00:00:00Z');",
1300        )
1301        .unwrap();
1302        drop(conn);
1303
1304        let db = Database::new(dir.to_str().unwrap()).unwrap();
1305        let conn = db.lock_conn();
1306        let (channel, external): (Option<String>, Option<String>) = conn
1307            .query_row(
1308                "SELECT channel, external_chat_id FROM chats WHERE chat_id = 100",
1309                [],
1310                |row| Ok((row.get(0)?, row.get(1)?)),
1311            )
1312            .unwrap();
1313        assert_eq!(channel.as_deref(), Some("telegram"));
1314        assert_eq!(external.as_deref(), Some("100"));
1315        drop(conn);
1316
1317        cleanup(&dir);
1318    }
1319
1320    #[test]
1321    fn test_migration_backfills_memory_identity_columns() {
1322        let dir = std::env::temp_dir().join(format!(
1323            "microclaw_migration_memory_identity_{}",
1324            uuid::Uuid::new_v4()
1325        ));
1326        std::fs::create_dir_all(&dir).unwrap();
1327        let db_path = dir.join("microclaw.db");
1328        let conn = Connection::open(&db_path).unwrap();
1329        conn.execute_batch(
1330            "CREATE TABLE chats (
1331                chat_id INTEGER PRIMARY KEY,
1332                chat_title TEXT,
1333                chat_type TEXT NOT NULL DEFAULT 'private',
1334                last_message_time TEXT NOT NULL
1335            );
1336            INSERT INTO chats(chat_id, chat_title, chat_type, last_message_time)
1337            VALUES (200, 'legacy discord', 'discord', '2026-01-01T00:00:00Z');
1338
1339            CREATE TABLE memories (
1340                id INTEGER PRIMARY KEY AUTOINCREMENT,
1341                chat_id INTEGER,
1342                content TEXT NOT NULL,
1343                category TEXT NOT NULL,
1344                created_at TEXT NOT NULL,
1345                updated_at TEXT NOT NULL,
1346                embedding_model TEXT
1347            );
1348            INSERT INTO memories(chat_id, content, category, created_at, updated_at, embedding_model)
1349            VALUES (200, 'legacy memory', 'KNOWLEDGE', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z', NULL);",
1350        )
1351        .unwrap();
1352        drop(conn);
1353
1354        let db = Database::new(dir.to_str().unwrap()).unwrap();
1355        let conn = db.lock_conn();
1356        let (chat_channel, external): (Option<String>, Option<String>) = conn
1357            .query_row(
1358                "SELECT chat_channel, external_chat_id FROM memories WHERE chat_id = 200",
1359                [],
1360                |row| Ok((row.get(0)?, row.get(1)?)),
1361            )
1362            .unwrap();
1363        assert_eq!(chat_channel.as_deref(), Some("discord"));
1364        assert_eq!(external.as_deref(), Some("200"));
1365        drop(conn);
1366
1367        cleanup(&dir);
1368    }
1369
1370    #[test]
1371    fn test_get_active_chat_ids_since() {
1372        let (db, dir) = test_db();
1373        db.store_message(&StoredMessage {
1374            id: "m1".into(),
1375            chat_id: 100,
1376            sender_name: "alice".into(),
1377            content: "hello".into(),
1378            is_from_bot: false,
1379            timestamp: "2024-06-01T00:00:01Z".into(),
1380        })
1381        .unwrap();
1382        db.store_message(&StoredMessage {
1383            id: "m2".into(),
1384            chat_id: 200,
1385            sender_name: "bob".into(),
1386            content: "hi".into(),
1387            is_from_bot: false,
1388            timestamp: "2024-06-01T00:00:02Z".into(),
1389        })
1390        .unwrap();
1391        // Bot message should not count
1392        db.store_message(&StoredMessage {
1393            id: "m3".into(),
1394            chat_id: 300,
1395            sender_name: "bot".into(),
1396            content: "bot msg".into(),
1397            is_from_bot: true,
1398            timestamp: "2024-06-01T00:00:03Z".into(),
1399        })
1400        .unwrap();
1401
1402        let ids = db
1403            .get_active_chat_ids_since("2024-06-01T00:00:00Z")
1404            .unwrap();
1405        assert_eq!(ids.len(), 2);
1406        assert!(ids.contains(&100));
1407        assert!(ids.contains(&200));
1408        assert!(!ids.contains(&300));
1409
1410        // Before any messages
1411        let ids_empty = db
1412            .get_active_chat_ids_since("2025-01-01T00:00:00Z")
1413            .unwrap();
1414        assert!(ids_empty.is_empty());
1415
1416        cleanup(&dir);
1417    }
1418
1419    #[test]
1420    fn test_list_idle_chats() {
1421        let (db, dir) = test_db();
1422        // Chat 1: last message long ago (idle). Chat 2: recent (active).
1423        db.store_message(&StoredMessage {
1424            id: "old".into(),
1425            chat_id: 1,
1426            sender_name: "u".into(),
1427            content: "hi".into(),
1428            is_from_bot: false,
1429            timestamp: "2020-01-01T00:00:00Z".into(),
1430        })
1431        .unwrap();
1432        db.store_message(&StoredMessage {
1433            id: "new".into(),
1434            chat_id: 2,
1435            sender_name: "u".into(),
1436            content: "hi".into(),
1437            is_from_bot: false,
1438            timestamp: "2099-01-01T00:00:00Z".into(),
1439        })
1440        .unwrap();
1441        let idle = db.list_idle_chats("2030-01-01T00:00:00Z", 50).unwrap();
1442        assert!(idle.contains(&1));
1443        assert!(!idle.contains(&2));
1444
1445        // Familiarity proxy: message counts per chat.
1446        assert_eq!(db.count_messages_for_chat(1).unwrap(), 1);
1447        assert_eq!(db.count_messages_for_chat(2).unwrap(), 1);
1448        assert_eq!(db.count_messages_for_chat(999).unwrap(), 0);
1449        cleanup(&dir);
1450    }
1451}
microclaw_engine/internal/storage/db/chats.rs

microclaw_engine/internal/storage/db/
chats.rs
