-- name: CreateFile :one
INSERT INTO files(file_name, file_path, original_name, folder_name, mime_type, file_size, file_url)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id,file_url,file_name,file_size;

-- name: ListFiles :many
SELECT f.*,
       CASE
           WHEN m.file_id IS NULL THEN NULL
           ELSE jsonb_build_object(
                   'width', m.width,
                   'height', m.height,
                   'format', m.format
                )
           END AS metadata
FROM files f
         LEFT JOIN file_image_metadata m
                   ON f.id = m.file_id
ORDER BY f.id DESC
LIMIT $1 OFFSET $2;

-- name: CountFiles :one
SELECT COUNT(*)
FROM files;

-- name: ListImageFiles :many
SELECT id, file_path
FROM files
WHERE mime_type LIKE 'image/%';