1-- name: CreateMessage :one
 2INSERT INTO messages (message_id, conversation_id, sequence_id, type, llm_data, user_data, usage_data, display_data, excluded_from_context)
 3VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
 4RETURNING *;
 5
 6-- name: GetNextSequenceID :one
 7SELECT COALESCE(MAX(sequence_id), 0) + 1 
 8FROM messages 
 9WHERE conversation_id = ?;
10
11-- name: GetMessage :one
12SELECT * FROM messages
13WHERE message_id = ?;
14
15-- name: ListMessages :many
16SELECT * FROM messages
17WHERE conversation_id = ?
18ORDER BY sequence_id ASC;
19
20-- name: ListMessagesForContext :many
21SELECT * FROM messages
22WHERE conversation_id = ? AND excluded_from_context = FALSE
23ORDER BY sequence_id ASC;
24
25-- name: ListMessagesPaginated :many
26SELECT * FROM messages
27WHERE conversation_id = ?
28ORDER BY sequence_id ASC
29LIMIT ? OFFSET ?;
30
31-- name: ListMessagesByType :many
32SELECT * FROM messages
33WHERE conversation_id = ? AND type = ?
34ORDER BY sequence_id ASC;
35
36-- name: GetLatestMessage :one
37SELECT * FROM messages
38WHERE conversation_id = ?
39ORDER BY sequence_id DESC
40LIMIT 1;
41
42-- name: DeleteMessage :exec
43DELETE FROM messages
44WHERE message_id = ?;
45
46-- name: DeleteConversationMessages :exec
47DELETE FROM messages
48WHERE conversation_id = ?;
49
50-- name: CountMessagesInConversation :one
51SELECT COUNT(*) FROM messages
52WHERE conversation_id = ?;
53
54-- name: CountMessagesByType :one
55SELECT COUNT(*) FROM messages
56WHERE conversation_id = ? AND type = ?;
57
58-- name: ListMessagesSince :many
59SELECT * FROM messages
60WHERE conversation_id = ? AND sequence_id > ?
61ORDER BY sequence_id ASC;