1-- name: CreateSession :one
  2INSERT INTO sessions (
  3    id,
  4    parent_session_id,
  5    title,
  6    message_count,
  7    prompt_tokens,
  8    completion_tokens,
  9    cost,
 10    summary_message_id,
 11    updated_at,
 12    created_at
 13) VALUES (
 14    ?,
 15    ?,
 16    ?,
 17    ?,
 18    ?,
 19    ?,
 20    ?,
 21    null,
 22    strftime('%s', 'now'),
 23    strftime('%s', 'now')
 24) RETURNING *;
 25
 26-- name: GetSessionByID :one
 27SELECT *
 28FROM sessions
 29WHERE id = ? LIMIT 1;
 30
 31-- name: ListSessions :many
 32SELECT *
 33FROM sessions
 34WHERE parent_session_id is NULL
 35ORDER BY created_at DESC;
 36
 37-- name: UpdateSession :one
 38UPDATE sessions
 39SET
 40    title = ?,
 41    prompt_tokens = ?,
 42    completion_tokens = ?,
 43    summary_message_id = ?,
 44    cost = ?
 45WHERE id = ?
 46RETURNING *;
 47
 48
 49-- name: DeleteSession :exec
 50DELETE FROM sessions
 51WHERE id = ?;
 52
 53-- name: ListChildSessions :many
 54SELECT *
 55FROM sessions
 56WHERE parent_session_id = ?
 57ORDER BY created_at ASC;
 58
 59-- name: ListAllSessions :many
 60SELECT *
 61FROM sessions
 62ORDER BY created_at DESC;
 63
 64-- name: SearchSessionsByTitle :many
 65SELECT *
 66FROM sessions
 67WHERE title LIKE ?
 68ORDER BY created_at DESC;
 69
 70-- name: SearchSessionsByTitleAndText :many
 71SELECT DISTINCT s.*
 72FROM sessions s
 73JOIN messages m ON s.id = m.session_id
 74WHERE s.title LIKE ? AND m.parts LIKE ?
 75ORDER BY s.created_at DESC;
 76
 77-- name: SearchSessionsByText :many
 78SELECT DISTINCT s.*
 79FROM sessions s
 80JOIN messages m ON s.id = m.session_id
 81WHERE m.parts LIKE ?
 82ORDER BY s.created_at DESC;
 83
 84-- name: GetSessionStats :one
 85SELECT 
 86    COUNT(*) as total_sessions,
 87    SUM(message_count) as total_messages,
 88    SUM(prompt_tokens) as total_prompt_tokens,
 89    SUM(completion_tokens) as total_completion_tokens,
 90    SUM(cost) as total_cost,
 91    AVG(cost) as avg_cost_per_session
 92FROM sessions;
 93
 94-- name: GetSessionStatsByDay :many
 95SELECT 
 96    date(created_at, 'unixepoch') as day,
 97    COUNT(*) as session_count,
 98    SUM(message_count) as message_count,
 99    SUM(prompt_tokens) as prompt_tokens,
100    SUM(completion_tokens) as completion_tokens,
101    SUM(cost) as total_cost,
102    AVG(cost) as avg_cost
103FROM sessions
104GROUP BY date(created_at, 'unixepoch')
105ORDER BY day DESC;
106
107-- name: GetSessionStatsByWeek :many
108SELECT 
109    date(created_at, 'unixepoch', 'weekday 0', '-6 days') as week_start,
110    COUNT(*) as session_count,
111    SUM(message_count) as message_count,
112    SUM(prompt_tokens) as prompt_tokens,
113    SUM(completion_tokens) as completion_tokens,
114    SUM(cost) as total_cost,
115    AVG(cost) as avg_cost
116FROM sessions
117GROUP BY date(created_at, 'unixepoch', 'weekday 0', '-6 days')
118ORDER BY week_start DESC;
119
120-- name: GetSessionStatsByMonth :many
121SELECT 
122    strftime('%Y-%m', datetime(created_at, 'unixepoch')) as month,
123    COUNT(*) as session_count,
124    SUM(message_count) as message_count,
125    SUM(prompt_tokens) as prompt_tokens,
126    SUM(completion_tokens) as completion_tokens,
127    SUM(cost) as total_cost,
128    AVG(cost) as avg_cost
129FROM sessions
130GROUP BY strftime('%Y-%m', datetime(created_at, 'unixepoch'))
131ORDER BY month DESC;