-- name: GetAttachment :one
SELECT * FROM attachments
WHERE id = ? LIMIT 1;

-- name: ListAttachments :many
SELECT * FROM attachments
WHERE archived = 0
ORDER BY created DESC
LIMIT ? OFFSET ?;

-- name: ListArchivedAttachments :many
SELECT * FROM attachments
WHERE archived = 1
ORDER BY created DESC
LIMIT ? OFFSET ?;

-- name: CreateAttachment :execresult
INSERT INTO attachments (
    principle_id, created, modified, name, filename, 
    file, type, size, description, archived
) VALUES (
    ?, NOW(), NOW(), ?, ?, ?, ?, ?, ?, 0
);

-- name: UpdateAttachment :exec
UPDATE attachments
SET modified = NOW(),
    name = ?,
    description = ?
WHERE id = ?;

-- name: DeleteAttachment :exec
UPDATE attachments
SET archived = 1,
    modified = NOW()
WHERE id = ?;

-- name: ListAttachmentsByPrinciple :many
SELECT * FROM attachments
WHERE principle_id = ? AND archived = 0
ORDER BY created DESC;