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