-- name: InsertAttachment :one
INSERT INTO message_attachments (id, user_id, message_id, filename, mime_type, size_bytes, sha256, storage_path)
VALUES ($1, $2, NULL, $3, $4, $5, $6, $7)
RETURNING *;

-- name: GetAttachment :one
SELECT * FROM message_attachments WHERE id = $1;

-- name: ListAttachmentsForMessage :many
SELECT * FROM message_attachments WHERE message_id = $1 ORDER BY created_at ASC;

-- name: ListOrphanAttachmentsForUser :many
SELECT * FROM message_attachments
WHERE user_id = $1 AND message_id IS NULL AND id = ANY($2::uuid[]);

-- name: AttachToMessage :exec
UPDATE message_attachments SET message_id = $2 WHERE id = ANY($1::uuid[]);

-- name: DeleteAttachment :exec
DELETE FROM message_attachments WHERE id = $1;

-- name: ListExpiredOrphans :many
SELECT id, storage_path FROM message_attachments
WHERE message_id IS NULL AND created_at < NOW() - ($1::interval);

-- name: ListAttachmentsForSoftDeletedConversations :many
SELECT a.id, a.storage_path
FROM message_attachments a
JOIN messages m ON m.id = a.message_id
JOIN conversations c ON c.id = m.conversation_id
WHERE c.deleted_at IS NOT NULL
  AND c.deleted_at < NOW() - ($1::interval);

-- name: DeleteAttachmentsByIDs :exec
DELETE FROM message_attachments WHERE id = ANY($1::uuid[]);