1-- name: GetFile :one
 2SELECT *
 3FROM files
 4WHERE id = ? LIMIT 1;
 5
 6-- name: GetFileByPathAndSession :one
 7SELECT *
 8FROM files
 9WHERE path = ? AND session_id = ?
10ORDER BY created_at DESC
11LIMIT 1;
12
13-- name: ListFilesBySession :many
14SELECT *
15FROM files
16WHERE session_id = ?
17ORDER BY created_at ASC;
18
19-- name: ListFilesByPath :many
20SELECT *
21FROM files
22WHERE path = ?
23ORDER BY created_at DESC;
24
25-- name: CreateFile :one
26INSERT INTO files (
27    id,
28    session_id,
29    path,
30    content,
31    version,
32    created_at,
33    updated_at
34) VALUES (
35    ?, ?, ?, ?, ?, strftime('%s', 'now'), strftime('%s', 'now')
36)
37RETURNING *;
38
39-- name: UpdateFile :one
40UPDATE files
41SET
42    content = ?,
43    version = ?,
44    updated_at = strftime('%s', 'now')
45WHERE id = ?
46RETURNING *;
47
48-- name: DeleteFile :exec
49DELETE FROM files
50WHERE id = ?;
51
52-- name: DeleteSessionFiles :exec
53DELETE FROM files
54WHERE session_id = ?;
55
56-- name: ListLatestSessionFiles :many
57SELECT f.*
58FROM files f
59INNER JOIN (
60    SELECT path, MAX(created_at) as max_created_at
61    FROM files
62    GROUP BY path
63) latest ON f.path = latest.path AND f.created_at = latest.max_created_at
64WHERE f.session_id = ?
65ORDER BY f.path;
66
67-- name: ListNewFiles :many
68SELECT *
69FROM files
70WHERE is_new = 1
71ORDER BY created_at DESC;