1-- name: CreateConversation :one
 2INSERT INTO conversations (conversation_id, slug, user_initiated, cwd)
 3VALUES (?, ?, ?, ?)
 4RETURNING *;
 5
 6-- name: GetConversation :one
 7SELECT * FROM conversations
 8WHERE conversation_id = ?;
 9
10-- name: GetConversationBySlug :one
11SELECT * FROM conversations
12WHERE slug = ?;
13
14-- name: ListConversations :many
15SELECT * FROM conversations
16WHERE archived = FALSE
17ORDER BY updated_at DESC
18LIMIT ? OFFSET ?;
19
20-- name: ListArchivedConversations :many
21SELECT * FROM conversations
22WHERE archived = TRUE
23ORDER BY updated_at DESC
24LIMIT ? OFFSET ?;
25
26-- name: SearchConversations :many
27SELECT * FROM conversations
28WHERE slug LIKE '%' || ? || '%' AND archived = FALSE
29ORDER BY updated_at DESC
30LIMIT ? OFFSET ?;
31
32-- name: SearchConversationsWithMessages :many
33-- Search conversations by slug OR message content (user messages and agent responses, not system prompts)
34SELECT DISTINCT c.* FROM conversations c
35LEFT JOIN messages m ON c.conversation_id = m.conversation_id AND m.type IN ('user', 'agent')
36WHERE c.archived = FALSE
37  AND (
38    c.slug LIKE '%' || ? || '%'
39    OR json_extract(m.user_data, '$.text') LIKE '%' || ? || '%'
40    OR m.llm_data LIKE '%' || ? || '%'
41  )
42ORDER BY c.updated_at DESC
43LIMIT ? OFFSET ?;
44
45-- name: SearchArchivedConversations :many
46SELECT * FROM conversations
47WHERE slug LIKE '%' || ? || '%' AND archived = TRUE
48ORDER BY updated_at DESC
49LIMIT ? OFFSET ?;
50
51-- name: UpdateConversationSlug :one
52UPDATE conversations
53SET slug = ?, updated_at = CURRENT_TIMESTAMP
54WHERE conversation_id = ?
55RETURNING *;
56
57-- name: UpdateConversationTimestamp :exec
58UPDATE conversations
59SET updated_at = CURRENT_TIMESTAMP
60WHERE conversation_id = ?;
61
62-- name: DeleteConversation :exec
63DELETE FROM conversations
64WHERE conversation_id = ?;
65
66-- name: CountConversations :one
67SELECT COUNT(*) FROM conversations WHERE archived = FALSE;
68
69-- name: CountArchivedConversations :one
70SELECT COUNT(*) FROM conversations WHERE archived = TRUE;
71
72-- name: ArchiveConversation :one
73UPDATE conversations
74SET archived = TRUE, updated_at = CURRENT_TIMESTAMP
75WHERE conversation_id = ?
76RETURNING *;
77
78-- name: UnarchiveConversation :one
79UPDATE conversations
80SET archived = FALSE, updated_at = CURRENT_TIMESTAMP
81WHERE conversation_id = ?
82RETURNING *;
83
84-- name: UpdateConversationCwd :one
85UPDATE conversations
86SET cwd = ?, updated_at = CURRENT_TIMESTAMP
87WHERE conversation_id = ?
88RETURNING *;