multica/server/pkg/db/queries/attachment.sql

-- name: CreateAttachment :one
WITH inserted AS (
  INSERT INTO attachment (
    id, workspace_id, issue_id, comment_id, chat_session_id, task_id, source_context_id,
    uploader_type, uploader_id, filename, url, content_type, size_bytes
  )
  VALUES (
    $1, $2, sqlc.narg(issue_id), sqlc.narg(comment_id), sqlc.narg(chat_session_id), sqlc.narg(task_id), sqlc.narg(source_context_id),
    $3, $4, $5, $6, $7, $8
  )
  RETURNING *
), bumped_issue AS (
  UPDATE issue
  SET revision = revision + 1
  WHERE id IN (SELECT issue_id FROM inserted WHERE issue_id IS NOT NULL)
  RETURNING revision
), bumped_comment AS (
  UPDATE comment
  SET revision = revision + 1
  WHERE id IN (SELECT comment_id FROM inserted WHERE comment_id IS NOT NULL)
  RETURNING revision
)
SELECT inserted.*,
       COALESCE((SELECT revision FROM bumped_issue), 0)::bigint AS issue_revision,
       COALESCE((SELECT revision FROM bumped_comment), 0)::bigint AS comment_revision
FROM inserted;

-- name: ListAttachmentsByIssue :many
SELECT * FROM attachment
WHERE issue_id = $1 AND workspace_id = $2
ORDER BY created_at ASC;

-- name: ListAttachmentsByComment :many
SELECT * FROM attachment
WHERE comment_id = $1 AND workspace_id = $2
ORDER BY created_at ASC;

-- name: GetAttachment :one
SELECT * FROM attachment
WHERE id = $1 AND workspace_id = $2;

-- name: GetAttachmentByIDOnly :one
-- Used by the download endpoint, which derives workspace context from the
-- attachment row itself rather than from request headers/query params. The
-- caller still has to verify the requester is a member of the returned
-- workspace_id before serving the bytes — this query is access-neutral on
-- purpose so a self-contained URL like /api/attachments/{id}/download can
-- work as a native <img>/<video> resource load (no header attachment).
SELECT * FROM attachment
WHERE id = $1;

-- name: ListAttachmentsByCommentIDs :many
SELECT * FROM attachment
WHERE comment_id = ANY($1::uuid[]) AND workspace_id = $2
ORDER BY created_at ASC;

-- name: ListSourceContextIssueAttachments :many
-- Source snapshots keep issue-owned attachments separate from comment-owned
-- attachments. In this schema a comment attachment retains issue_id, so the
-- generic ListAttachmentsByIssue query is intentionally too broad here.
SELECT * FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND issue_id = sqlc.arg(issue_id)
  AND comment_id IS NULL
  AND source_context_id IS NULL
ORDER BY created_at ASC, id ASC;

-- name: ListSourceContextCommentAttachments :many
-- The issue predicate is a defense-in-depth owner guard for damaged rows; the
-- comment ids were already derived from the guarded thread history.
SELECT * FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND issue_id = sqlc.arg(issue_id)
  AND comment_id = ANY(sqlc.arg(comment_ids)::uuid[])
  AND source_context_id IS NULL
ORDER BY created_at ASC, id ASC;

-- name: ListAttachmentURLsByIssueOrComments :many
SELECT a.url FROM attachment a
WHERE a.issue_id = $1
   OR a.comment_id IN (SELECT c.id FROM comment c WHERE c.issue_id = $1);

-- name: ListAttachmentURLsByCommentID :many
SELECT url FROM attachment
WHERE comment_id = $1;

-- name: DeleteCommentAttachments :many
-- Part of the comment delete transaction: removes the deleted comment's
-- attachments and returns their storage URLs for cleanup after commit.
DELETE FROM attachment
WHERE comment_id = @comment_id AND workspace_id = @workspace_id
RETURNING url;

-- name: LinkAttachmentsToComment :exec
UPDATE attachment
SET comment_id = $1
WHERE issue_id = $2
  AND comment_id IS NULL
  AND source_context_id IS NULL
  AND id = ANY($3::uuid[]);

-- name: ReplaceCommentAttachments :execrows
UPDATE attachment
SET comment_id = CASE
  WHEN id = ANY(sqlc.arg(attachment_ids)::uuid[]) THEN $1
  ELSE NULL
END
WHERE issue_id = $2
  AND source_context_id IS NULL
  AND (
    (comment_id = $1 AND NOT id = ANY(sqlc.arg(attachment_ids)::uuid[]))
    OR (comment_id IS NULL AND id = ANY(sqlc.arg(attachment_ids)::uuid[]))
  );

-- name: LinkAttachmentsToChatMessage :many
UPDATE attachment
SET chat_message_id = sqlc.arg(chat_message_id),
    chat_session_id = sqlc.arg(chat_session_id)
WHERE workspace_id = sqlc.arg(workspace_id)
  AND issue_id IS NULL
  AND comment_id IS NULL
  AND chat_message_id IS NULL
  AND source_context_id IS NULL
  AND (
    chat_session_id IS NULL
    OR chat_session_id = sqlc.arg(chat_session_id)
  )
  AND uploader_type = sqlc.arg(uploader_type)
  AND uploader_id = sqlc.arg(uploader_id)
  AND id = ANY(sqlc.arg(attachment_ids)::uuid[])
RETURNING id;

-- name: DetachAttachmentsFromUserChatMessageByTask :many
-- When an empty chat task is cancelled, its user message is deleted. The
-- attachment FK is ON DELETE CASCADE, so without this the bound rows would be
-- destroyed and a restored draft could never re-bind them. Detach first
-- (chat_message_id -> NULL, keep chat_session_id) so the rows survive as
-- workspace/session-scoped unattached attachments and re-send can re-link them.
UPDATE attachment
SET chat_message_id = NULL
WHERE chat_message_id IN (
  SELECT id FROM chat_message WHERE chat_message.task_id = $1 AND role = 'user'
)
RETURNING *;

-- name: CountUnboundChatAttachmentsForTask :one
-- How many attachments the agent produced for this chat task that are still
-- unbound to any owner. Lets CompleteTask create an assistant message (and
-- bind them) even when the agent's text output was empty but it uploaded files.
SELECT COUNT(*) FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND task_id = sqlc.arg(task_id)
  AND issue_id IS NULL
  AND comment_id IS NULL
  AND chat_message_id IS NULL
  AND source_context_id IS NULL;

-- name: BindChatAttachmentsToMessage :many
-- Bind a chat agent's task-scoped attachments to the assistant reply it just
-- produced. Only rows still unclaimed by any owner (issue/comment/chat_message)
-- are eligible, so an attachment already linked elsewhere is never stolen.
-- Returns the bound ids for logging.
UPDATE attachment
SET chat_message_id = sqlc.arg(chat_message_id)
WHERE workspace_id = sqlc.arg(workspace_id)
  AND task_id = sqlc.arg(task_id)
  AND issue_id IS NULL
  AND comment_id IS NULL
  AND chat_message_id IS NULL
  AND source_context_id IS NULL
RETURNING id;

-- name: ListAttachmentsByChatMessage :many
SELECT * FROM attachment
WHERE chat_message_id = $1 AND workspace_id = $2
ORDER BY created_at ASC;

-- name: ListAttachmentsByChatMessageIDs :many
SELECT * FROM attachment
WHERE chat_message_id = ANY($1::uuid[]) AND workspace_id = $2
ORDER BY created_at ASC;

-- name: LockAttachmentsForIssueLink :many
-- Issue updates bind attachments and then touch the owner row. Only rows that
-- belong to no issue yet are eligible, and nothing reaches those through an
-- issue — not teardown's cascade, not DeleteAttachment, which takes the owning
-- issue first — so locking them before the owner cannot deadlock.
-- Attachments that DO belong to the issue are locked after it; see
-- LockAttachmentsForCommentLink.
SELECT id FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND issue_id IS NULL
  AND source_context_id IS NULL
  AND id = ANY(sqlc.arg(attachment_ids)::uuid[])
ORDER BY id
FOR UPDATE;

-- name: LockAttachmentsForCommentLink :many
-- CreateComment binds attachments in the transaction that created the comment,
-- after the CreateComment statement has taken the issue row: issue -> comment
-- -> child, the order every owner-first mutation here uses. This pins the
-- requested set under that lock and returns the ids still eligible, so the
-- caller can refuse before the comment is committed when a requested
-- attachment was deleted while the issue lock was contended.
SELECT id FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND issue_id = sqlc.arg(issue_id)
  AND comment_id IS NULL
  AND source_context_id IS NULL
  AND id = ANY(sqlc.arg(attachment_ids)::uuid[])
ORDER BY id
FOR UPDATE;

-- name: LockAttachmentRow :one
-- Locks an attachment that has no owner to lock instead — a chat, avatar or
-- still-unbound upload. Reading it under its own lock is what keeps it from
-- gaining an owner between the read and the write, which would put the write
-- back in the attachment -> issue order issue teardown deadlocks with.
SELECT * FROM attachment
WHERE id = $1 AND workspace_id = $2
FOR UPDATE;

-- name: LinkAttachmentsToIssue :one
WITH linked AS (
  UPDATE attachment
  SET issue_id = sqlc.arg(issue_id)
  WHERE attachment.workspace_id = sqlc.arg(workspace_id)
    AND attachment.issue_id IS NULL
    AND attachment.source_context_id IS NULL
    AND attachment.id = ANY(sqlc.arg(attachment_ids)::uuid[])
  RETURNING attachment.issue_id
), bumped_issue AS (
  UPDATE issue
  SET revision = revision + 1,
      updated_at = now()
  WHERE id = sqlc.arg(issue_id)
    AND sqlc.arg(bump_revision)::boolean
    AND EXISTS (SELECT 1 FROM linked)
  RETURNING revision
)
SELECT COUNT(*)::bigint AS linked_count,
       COALESCE((SELECT revision FROM bumped_issue), 0)::bigint AS issue_revision
FROM linked;

-- name: DeleteAttachment :one
WITH deleted AS (
  DELETE FROM attachment
  WHERE attachment.id = $1 AND attachment.workspace_id = $2
    AND attachment.source_context_id IS NULL
  RETURNING issue_id, comment_id
), bumped_issue AS (
  UPDATE issue
  SET revision = revision + 1
  WHERE id IN (SELECT issue_id FROM deleted WHERE issue_id IS NOT NULL)
  RETURNING revision
), bumped_comment AS (
  UPDATE comment
  SET revision = revision + 1
  WHERE id IN (SELECT comment_id FROM deleted WHERE comment_id IS NOT NULL)
  RETURNING revision
)
SELECT EXISTS(SELECT 1 FROM deleted) AS changed,
       COALESCE((SELECT revision FROM bumped_issue), 0)::bigint AS issue_revision,
       COALESCE((SELECT revision FROM bumped_comment), 0)::bigint AS comment_revision;

-- name: ListAttachmentsByIDs :many
SELECT * FROM attachment
WHERE id = ANY(sqlc.arg(attachment_ids)::uuid[]) AND workspace_id = sqlc.arg(workspace_id)
ORDER BY created_at ASC;

-- name: CreateSourceContextAttachment :one
INSERT INTO attachment (
  id, workspace_id, source_context_id, uploader_type, uploader_id,
  filename, url, content_type, size_bytes
) VALUES (
  sqlc.arg(id), sqlc.arg(workspace_id), sqlc.arg(source_context_id),
  sqlc.arg(uploader_type), sqlc.arg(uploader_id), sqlc.arg(filename),
  sqlc.arg(url), sqlc.arg(content_type), sqlc.arg(size_bytes)
)
RETURNING *;

-- name: ListAttachmentsBySourceContext :many
SELECT * FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND source_context_id = sqlc.arg(source_context_id)
ORDER BY created_at ASC, id ASC;

-- name: DeleteAttachmentsBySourceContext :many
DELETE FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND source_context_id = sqlc.arg(source_context_id)
RETURNING *;

-- name: ListSourceContextAttachmentURLsByWorkspace :many
SELECT url FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND source_context_id IS NOT NULL
ORDER BY id;

-- name: DeleteSourceContextAttachmentsByWorkspace :exec
DELETE FROM attachment
WHERE workspace_id = sqlc.arg(workspace_id)
  AND source_context_id IS NOT NULL;

-- name: ListProjectAttachments :many
-- The project library: every attachment of every issue in the project. A
-- comment attachment keeps its issue_id (it is bound with issue_id = the
-- comment's issue), so joining on attachment.issue_id alone reaches issue-
-- and comment-owned rows exactly once. Source-context snapshot copies are
-- excluded (they duplicate the original rows), as are attachments whose
-- comment became a tombstone.
SELECT sqlc.embed(a), i.number AS issue_number, i.title AS issue_title
FROM attachment a
JOIN issue i ON i.id = a.issue_id AND i.workspace_id = a.workspace_id
LEFT JOIN comment c ON c.id = a.comment_id
WHERE i.project_id = sqlc.arg(project_id)
  AND i.workspace_id = sqlc.arg(workspace_id)
  AND a.source_context_id IS NULL
  AND c.deleted_at IS NULL
  AND (sqlc.narg(uploader_type)::text IS NULL OR a.uploader_type = sqlc.narg(uploader_type)::text)
ORDER BY a.created_at DESC, a.id DESC
LIMIT sqlc.arg(page_limit) OFFSET sqlc.arg(page_offset);

-- name: CountProjectAttachments :one
-- Same row set as ListProjectAttachments, counted per uploader type so the
-- library can label both groups from one round trip.
SELECT
  (count(*) FILTER (WHERE a.uploader_type = 'agent'))::bigint AS agent_count,
  (count(*) FILTER (WHERE a.uploader_type = 'member'))::bigint AS member_count
FROM attachment a
JOIN issue i ON i.id = a.issue_id AND i.workspace_id = a.workspace_id
LEFT JOIN comment c ON c.id = a.comment_id
WHERE i.project_id = sqlc.arg(project_id)
  AND i.workspace_id = sqlc.arg(workspace_id)
  AND a.source_context_id IS NULL
  AND c.deleted_at IS NULL;

-- name: GetProjectLibraryIssue :one
-- The top-level issue that holds files uploaded straight to a project. Found
-- by its fixed title in any status, so closing it does not spawn a second one.
SELECT * FROM issue
WHERE project_id = sqlc.arg(project_id)
  AND workspace_id = sqlc.arg(workspace_id)
  AND parent_issue_id IS NULL
  AND title = sqlc.arg(title)
ORDER BY created_at ASC
LIMIT 1;