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 version DESC, created_at DESC
11LIMIT 1;
12
13-- name: ListFilesBySession :many
14SELECT *
15FROM files
16WHERE session_id = ?
17ORDER BY version ASC, created_at ASC;
18
19-- name: ListFilesByPath :many
20SELECT *
21FROM files
22WHERE path = ?
23ORDER BY version DESC, 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: DeleteFile :exec
40DELETE FROM files
41WHERE id = ?;
42
43-- name: DeleteSessionFiles :exec
44DELETE FROM files
45WHERE session_id = ?;
46
47-- name: ListLatestSessionFiles :many
48SELECT f.*
49FROM files f
50INNER JOIN (
51    SELECT path, MAX(version) as max_version, MAX(created_at) as max_created_at
52    FROM files
53    GROUP BY path
54) latest ON f.path = latest.path AND f.version = latest.max_version AND f.created_at = latest.max_created_at
55WHERE f.session_id = ?
56ORDER BY f.path;
57
58-- name: ListNewFiles :many
59SELECT *
60FROM files
61WHERE is_new = 1
62ORDER BY version DESC, created_at DESC;