package attachment

import (
	"context"
	"errors"
	"fmt"
	"net/url"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
)

func AvatarBlobColumnExpr(runtime any) string {
	if coredb.IsTiDB(runtime) {
		return "`blob`"
	}
	return `"blob"`
}

// AppAttachment is the attachment shape expected by the web app client.
type AppAttachment struct {
	ID          string
	Filename    string
	ContentType string
	SizeBytes   int64

	// ThumbnailURL is optional. The upstream client treats it as an <img src> URL when present.
	ThumbnailURL string
}

const (
	avatarOwnerKindAgent          = "agent"
	avatarOwnerKindApp            = "app"
	avatarOwnerKindServiceAccount = "service_account"
	avatarOwnerKindUser           = "user"
)

type CreateAttachmentParams struct {
	Filename    string
	ContentType string
	Blob        []byte
}

type AttachmentDownload struct {
	Filename    string
	ContentType string
	Blob        []byte
	PublicURL   string
	CreatedAt   time.Time
}

type attachmentDownloadRecord struct {
	ID            string
	Download      AttachmentDownload
	WorkspaceID   string
	ScopeID       string
	MessageID     string
	StorageDriver string
	ObjectKey     string
}

type attachmentInsertRow struct {
	id                 string
	workspaceID        string
	scopeID            string
	uploadedByMemberID string
	messageID          string
	filename           string
	contentType        string
	sizeBytes          int64
	storageDriver      string
	objectKey          string
	payload            []byte
	createdAt          time.Time
}

func (txs TxStore) UploadAttachmentsForUser(ctx context.Context, userID string, workspaceID string, scopeID string, attachments []CreateAttachmentParams) ([]AppAttachment, error) {
	if len(attachments) == 0 {
		return nil, badRequest("files are required")
	}

	memberID, resolvedScopeID, err := txs.resolveAttachmentUploadScopeForUser(ctx, userID, workspaceID, scopeID)
	if err != nil {
		return nil, err
	}
	return txs.uploadAttachmentsToScope(ctx, memberID, workspaceID, resolvedScopeID, attachments)
}

func (txs TxStore) UploadAttachmentsForMember(ctx context.Context, memberID string, workspaceID string, scopeID string, attachments []CreateAttachmentParams) ([]AppAttachment, error) {
	return txs.uploadAttachmentsForMember(ctx, memberID, workspaceID, scopeID, attachments)
}

func (txs TxStore) uploadAttachmentsForMember(ctx context.Context, memberID string, workspaceID string, scopeID string, attachments []CreateAttachmentParams) ([]AppAttachment, error) {
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedMemberID == "" {
		return nil, badRequest("memberId is required")
	}

	trimmedScopeID := strings.TrimSpace(scopeID)
	if trimmedScopeID == "" {
		return nil, badRequest("scopeId is required")
	}

	resolvedScopeID, err := txs.resolveUploadScopeForMember(ctx, txs.tx, trimmedMemberID, workspaceID, trimmedScopeID)
	if err != nil {
		return nil, err
	}

	return txs.uploadAttachmentsToScope(ctx, trimmedMemberID, workspaceID, resolvedScopeID, attachments)
}

func (txs TxStore) resolveAttachmentUploadScopeForUser(ctx context.Context, userID string, workspaceID string, scopeID string) (string, string, error) {
	trimmedScopeID := strings.TrimSpace(scopeID)
	if trimmedScopeID == "" {
		return "", "", badRequest("scopeId is required")
	}
	if txs.options.ResolveUploadScopeForUser != nil {
		return txs.resolveUploadScopeForUser(ctx, txs.tx, userID, workspaceID, trimmedScopeID)
	}
	membership, err := access.LoadHumanWorkspaceMember(ctx, txs.tx, userID, workspaceID)
	if err != nil {
		if errors.Is(err, access.ErrForbidden) {
			return "", "", ErrForbidden
		}
		if errors.Is(err, access.ErrNotFound) {
			return "", "", ErrNotFound
		}
		return "", "", fmt.Errorf("load attachment upload member: %w", err)
	}
	resolvedScopeID, err := txs.resolveUploadScopeForMember(ctx, txs.tx, membership.Member.ID, workspaceID, trimmedScopeID)
	if err != nil {
		return "", "", err
	}
	return membership.Member.ID, resolvedScopeID, nil
}

func (txs TxStore) uploadAttachmentsToScope(ctx context.Context, memberID string, workspaceID string, scopeID string, attachments []CreateAttachmentParams) ([]AppAttachment, error) {
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedMemberID == "" {
		return nil, badRequest("memberId is required")
	}

	created := make([]AppAttachment, 0, len(attachments))
	cleanup := func(err error) ([]AppAttachment, error) {
		txs.cleanupUploadedMediaObjects(ctx)
		return nil, err
	}
	now := time.Now().UTC()
	insertRows := make([]attachmentInsertRow, 0, len(attachments))

	for _, input := range attachments {
		filename := strings.TrimSpace(input.Filename)
		if filename == "" {
			return cleanup(badRequest("filename is required"))
		}
		contentType := strings.TrimSpace(input.ContentType)
		if contentType == "" {
			contentType = "application/octet-stream"
		}
		if len(input.Blob) == 0 {
			return cleanup(badRequest("file is empty"))
		}

		attachmentID, err := identifiers.NewEntityID("att")
		if err != nil {
			return cleanup(fmt.Errorf("generate attachment id: %w", err))
		}

		storageDriver := ""
		objectKey := ""
		payload := input.Blob
		if txs.mediaStorageEnabled() {
			storageDriver, objectKey, err = txs.putMediaObject(ctx, mediaObjectTypeAttachment, workspaceID, attachmentID, contentType, input.Blob)
			if err != nil {
				return cleanup(err)
			}
			payload = nil
			txs.trackUploadedMediaObject(storageDriver, objectKey)
		}

		insertRows = append(insertRows, attachmentInsertRow{
			id:                 attachmentID,
			workspaceID:        workspaceID,
			scopeID:            scopeID,
			uploadedByMemberID: trimmedMemberID,
			filename:           filename,
			contentType:        contentType,
			sizeBytes:          int64(len(input.Blob)),
			storageDriver:      storageDriver,
			objectKey:          objectKey,
			payload:            payload,
			createdAt:          now,
		})
		created = append(created, AppAttachment{
			ID:          attachmentID,
			Filename:    filename,
			ContentType: contentType,
			SizeBytes:   int64(len(input.Blob)),
		})
	}
	if err := txs.insertAttachmentRows(ctx, insertRows); err != nil {
		return cleanup(fmt.Errorf("insert attachments: %w", err))
	}
	return created, nil
}

func (txs TxStore) LoadAttachmentForUser(ctx context.Context, userID string, workspaceID string, attachmentID string) (AttachmentDownload, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)

	trimmedAttachmentID := strings.TrimSpace(attachmentID)
	if trimmedAttachmentID == "" {
		return AttachmentDownload{}, badRequest("attachmentId is required")
	}

	record, err := txs.loadAttachmentDownloadRecord(ctx, trimmedAttachmentID)
	if err != nil {
		return AttachmentDownload{}, err
	}
	if trimmedWorkspaceID != "" && record.WorkspaceID != trimmedWorkspaceID {
		return AttachmentDownload{}, ErrNotFound
	}

	if record.MessageID != "" {
		if err := txs.authorizeAttachmentMessageReadForUser(ctx, txs.tx, userID, record.WorkspaceID, record.MessageID); err != nil {
			if errors.Is(err, ErrForbidden) {
				return AttachmentDownload{}, ErrNotFound
			}
			return AttachmentDownload{}, err
		}
		return txs.loadAttachmentDownloadBlob(ctx, record)
	}
	return AttachmentDownload{}, ErrNotFound
}

func (txs TxStore) LoadAttachmentForMember(ctx context.Context, memberID string, workspaceID string, attachmentID string) (AttachmentDownload, error) {
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedMemberID == "" {
		return AttachmentDownload{}, badRequest("memberId is required")
	}

	trimmedWorkspaceID := strings.TrimSpace(workspaceID)

	trimmedAttachmentID := strings.TrimSpace(attachmentID)
	if trimmedAttachmentID == "" {
		return AttachmentDownload{}, badRequest("attachmentId is required")
	}

	record, err := txs.loadAttachmentDownloadRecord(ctx, trimmedAttachmentID)
	if err != nil {
		return AttachmentDownload{}, err
	}
	if trimmedWorkspaceID != "" && record.WorkspaceID != trimmedWorkspaceID {
		return AttachmentDownload{}, ErrNotFound
	}
	if record.MessageID != "" {
		if err := txs.authorizeAttachmentMessageReadForMember(ctx, txs.tx, trimmedMemberID, record.WorkspaceID, record.MessageID); err != nil {
			return AttachmentDownload{}, err
		}
		return txs.loadAttachmentDownloadBlob(ctx, record)
	}
	return AttachmentDownload{}, ErrNotFound
}

// authorizeAttachmentRead loads the attachment record (NOT the blob) + checks
// message-read permission. Lightweight — avoids the OSS blob fetch on cache
// hits. Used by the thumbnail endpoint before checking the cache.
func (txs TxStore) authorizeAttachmentRead(ctx context.Context, workspaceID string, attachmentID string, userID string, isAgentToken bool, agentMemberID string) error {
	record, err := txs.loadAttachmentDownloadRecord(ctx, attachmentID)
	if err != nil {
		return err
	}
	if workspaceID != "" && record.WorkspaceID != workspaceID {
		return ErrNotFound
	}
	if record.MessageID == "" {
		return ErrNotFound
	}
	if isAgentToken {
		return txs.authorizeAttachmentMessageReadForMember(ctx, txs.tx, agentMemberID, record.WorkspaceID, record.MessageID)
	}
	if err := txs.authorizeAttachmentMessageReadForUser(ctx, txs.tx, userID, record.WorkspaceID, record.MessageID); err != nil {
		if errors.Is(err, ErrForbidden) {
			return ErrNotFound
		}
		return err
	}
	return nil
}

func (txs TxStore) loadAttachmentDownloadRecord(ctx context.Context, attachmentID string) (attachmentDownloadRecord, error) {
	row := txs.tx.QueryRow(ctx, `
SELECT a.workspace_id, a.scope_id, a.message_id, a.filename, a.content_type, COALESCE(a.storage_driver, ''), COALESCE(a.object_key, ''), a.created_at
FROM attachments a
JOIN scope_messages sm
  ON sm.id = a.message_id
 AND sm.scope_id = a.scope_id
WHERE a.id = $1
	`, attachmentID)

	var record attachmentDownloadRecord
	if err := row.Scan(
		&record.WorkspaceID,
		&record.ScopeID,
		&record.MessageID,
		&record.Download.Filename,
		&record.Download.ContentType,
		&record.StorageDriver,
		&record.ObjectKey,
		&record.Download.CreatedAt,
	); err != nil {
		if coredb.IsNotFound(err) {
			return attachmentDownloadRecord{}, ErrNotFound
		}
		return attachmentDownloadRecord{}, fmt.Errorf("load attachment: %w", err)
	}
	record.ID = strings.TrimSpace(attachmentID)
	return record, nil
}

func (txs TxStore) loadAttachmentDownloadBlob(ctx context.Context, record attachmentDownloadRecord) (AttachmentDownload, error) {
	download := record.Download
	if publicURL := txs.mediaObjectPublicURL(record.StorageDriver, record.ObjectKey); publicURL != "" {
		download.PublicURL = publicURL
	}

	row := txs.tx.QueryRow(ctx, `
SELECT payload
FROM attachments
WHERE id = $1
`, strings.TrimSpace(record.ID))

	var payload []byte
	if err := row.Scan(&payload); err != nil {
		if coredb.IsNotFound(err) {
			return AttachmentDownload{}, ErrNotFound
		}
		return AttachmentDownload{}, fmt.Errorf("load attachment payload: %w", err)
	}

	blob, err := txs.loadMediaObject(ctx, record.StorageDriver, record.ObjectKey, payload)
	if err != nil {
		if download.PublicURL != "" {
			return download, nil
		}
		return AttachmentDownload{}, err
	}
	download.Blob = blob
	return download, nil
}

// UploadUserAvatar stores an avatar image blob for a user profile and returns its ID.
func (txs TxStore) UploadUserAvatar(ctx context.Context, userID string, workspaceID string, params CreateAttachmentParams) (string, error) {
	trimmedUserID := strings.TrimSpace(userID)
	if trimmedUserID == "" {
		return "", badRequest("userId is required")
	}
	avatarID, err := txs.uploadAvatar(ctx, workspaceID, avatarOwnerKindUser, trimmedUserID, params)
	if err != nil {
		return "", err
	}
	result, err := txs.tx.Exec(ctx, `
UPDATE users
SET avatar_url = $2, updated_at = NOW()
WHERE id = $1
`, trimmedUserID, VersionedUserAvatarURL(trimmedUserID, avatarID))
	if err != nil {
		return "", fmt.Errorf("update user avatar url: %w", err)
	}
	if result.RowsAffected() == 0 {
		return "", ErrNotFound
	}
	return avatarID, nil
}

// UploadAgentAvatar stores an avatar image blob for an agent profile and returns its ID.
func (txs TxStore) UploadAgentAvatar(ctx context.Context, workspaceID string, agentID string, params CreateAttachmentParams) (string, error) {
	trimmedAgentID := strings.TrimSpace(agentID)
	if trimmedAgentID == "" {
		return "", badRequest("agentId is required")
	}
	return txs.uploadAvatar(ctx, workspaceID, avatarOwnerKindAgent, trimmedAgentID, params)
}

// UploadResourceAvatar stores an immutable avatar asset for a managed
// workspace resource. The owning domain remains responsible for authorizing
// and applying the returned asset id to its profile.
func (txs TxStore) UploadResourceAvatar(ctx context.Context, workspaceID string, ownerKind string, ownerID string, params CreateAttachmentParams) (string, error) {
	switch ownerKind = strings.TrimSpace(ownerKind); ownerKind {
	case avatarOwnerKindApp, avatarOwnerKindServiceAccount:
	default:
		return "", badRequest("unsupported avatar owner kind")
	}
	return txs.uploadAvatar(ctx, workspaceID, ownerKind, ownerID, params)
}

func (txs TxStore) uploadAvatar(ctx context.Context, workspaceID string, ownerKind string, ownerID string, params CreateAttachmentParams) (string, error) {
	workspaceID = strings.TrimSpace(workspaceID)
	if workspaceID == "" {
		return "", badRequest("workspaceId is required")
	}
	ownerKind = strings.TrimSpace(ownerKind)
	if ownerKind == "" {
		return "", badRequest("ownerKind is required")
	}
	ownerID = strings.TrimSpace(ownerID)
	if ownerID == "" {
		return "", badRequest("ownerId is required")
	}
	filename := strings.TrimSpace(params.Filename)
	if filename == "" {
		return "", badRequest("filename is required")
	}
	contentType := strings.TrimSpace(params.ContentType)
	if contentType == "" {
		contentType = "application/octet-stream"
	}
	if len(params.Blob) == 0 {
		return "", badRequest("file is empty")
	}

	// Compress before storing: oversized originals are downscaled to the
	// avatar render cap and re-encoded, so stored avatars (and the original
	// route) stay small. Undecodable formats pass through unchanged.
	contentType, params.Blob = NormalizeAvatarUpload(contentType, params.Blob)

	avatarID, err := identifiers.NewEntityID("avt")
	if err != nil {
		return "", fmt.Errorf("generate avatar id: %w", err)
	}

	now := time.Now().UTC()
	storageDriver := ""
	objectKey := ""
	payload := params.Blob
	if txs.mediaStorageEnabled() {
		storageDriver, objectKey, err = txs.putMediaObject(ctx, mediaObjectTypeAvatar, workspaceID, avatarID, contentType, params.Blob)
		if err != nil {
			return "", err
		}
		payload = nil
		txs.trackUploadedMediaObject(storageDriver, objectKey)
	}

	avatarBlobColumn := AvatarBlobColumnExpr(txs.tx)
	if _, err := txs.tx.Exec(ctx, fmt.Sprintf(`
INSERT INTO avatars (id, workspace_id, owner_kind, owner_id, filename, content_type, size_bytes, storage_driver, object_key, %s, created_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
`, avatarBlobColumn), avatarID, workspaceID, ownerKind, ownerID, filename, contentType, int64(len(params.Blob)), storageDriver, nullableString(objectKey), payload, now); err != nil {
		txs.cleanupUploadedMediaObjects(ctx)
		return "", fmt.Errorf("insert avatar: %w", err)
	}

	return avatarID, nil
}

// LoadAvatar loads an avatar image blob by ID from the avatars table.
func (txs TxStore) LoadAvatar(ctx context.Context, avatarID string) (AttachmentDownload, error) {
	trimmedID := strings.TrimSpace(avatarID)
	if trimmedID == "" {
		return AttachmentDownload{}, badRequest("avatarId is required")
	}

	avatarBlobColumn := AvatarBlobColumnExpr(txs.tx)
	row := txs.tx.QueryRow(ctx, fmt.Sprintf(`
SELECT filename, content_type, COALESCE(storage_driver, ''), COALESCE(object_key, ''), %s
FROM avatars
WHERE id = $1
`, avatarBlobColumn), trimmedID)

	var download AttachmentDownload
	var storageDriver string
	var objectKey string
	if err := row.Scan(&download.Filename, &download.ContentType, &storageDriver, &objectKey, &download.Blob); err != nil {
		if coredb.IsNotFound(err) {
			return AttachmentDownload{}, ErrNotFound
		}
		return AttachmentDownload{}, fmt.Errorf("load avatar: %w", err)
	}
	// Avatar routes are used directly as <img src> URLs. Even when the
	// backing media object also has a public object-storage URL, keep the
	// browser-facing API route same-origin by loading and returning the image
	// bytes from the backend instead of exposing an intermediate redirect.
	blob, err := txs.loadMediaObject(ctx, storageDriver, objectKey, download.Blob)
	if err != nil {
		return AttachmentDownload{}, err
	}
	download.Blob = blob
	return download, nil
}

// LoadUserAvatar resolves a user's avatar URL and serves the backing avatar
// object when it is AgentTeam-managed. External avatar URLs are returned as
// redirects.
func (txs TxStore) LoadUserAvatar(ctx context.Context, userID string) (AttachmentDownload, error) {
	trimmedUserID := strings.TrimSpace(userID)
	if trimmedUserID == "" {
		return AttachmentDownload{}, badRequest("userId is required")
	}

	var avatarURL string
	row := txs.tx.QueryRow(ctx, `
SELECT COALESCE(avatar_url, '')
FROM users
WHERE id = $1
`, trimmedUserID)
	if err := row.Scan(&avatarURL); err != nil {
		if coredb.IsNotFound(err) {
			return AttachmentDownload{}, ErrNotFound
		}
		return AttachmentDownload{}, fmt.Errorf("load user avatar: %w", err)
	}

	trimmedAvatarURL := strings.TrimSpace(avatarURL)
	if trimmedAvatarURL == "" {
		return AttachmentDownload{}, ErrNotFound
	}
	if avatarID := avatarIDFromAgentTeamAvatarURL(trimmedAvatarURL); avatarID != "" {
		return txs.LoadAvatar(ctx, avatarID)
	}
	if isAgentTeamUserAvatarURL(trimmedAvatarURL, trimmedUserID) {
		return txs.loadLatestAvatarForOwner(ctx, "", avatarOwnerKindUser, trimmedUserID)
	}
	return AttachmentDownload{Filename: "avatar", PublicURL: trimmedAvatarURL}, nil
}

func avatarIDFromAgentTeamAvatarURL(rawURL string) string {
	for _, marker := range []string{"/api/v1/avatars/", "/api/avatars/"} {
		index := strings.Index(rawURL, marker)
		if index < 0 {
			continue
		}
		rest := rawURL[index+len(marker):]
		if slash := strings.IndexByte(rest, '/'); slash >= 0 {
			rest = rest[:slash]
		}
		if question := strings.IndexByte(rest, '?'); question >= 0 {
			rest = rest[:question]
		}
		if hash := strings.IndexByte(rest, '#'); hash >= 0 {
			rest = rest[:hash]
		}
		return strings.TrimSpace(rest)
	}
	return ""
}

func isAgentTeamUserAvatarURL(rawURL string, userID string) bool {
	trimmedURL := strings.TrimSpace(rawURL)
	if index := strings.IndexAny(trimmedURL, "?#"); index >= 0 {
		trimmedURL = trimmedURL[:index]
	}
	return trimmedURL == "/api/v1/me/avatar" || trimmedURL == CanonicalUserAvatarURL(userID)
}

// LoadAgentAvatar serves the latest AgentTeam-managed avatar for an agent.
func (txs TxStore) LoadAgentAvatar(ctx context.Context, workspaceID string, agentID string) (AttachmentDownload, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return AttachmentDownload{}, badRequest("workspaceId is required")
	}
	trimmedAgentID := strings.TrimSpace(agentID)
	if trimmedAgentID == "" {
		return AttachmentDownload{}, badRequest("agentId is required")
	}
	return txs.loadLatestAvatarForOwner(ctx, trimmedWorkspaceID, avatarOwnerKindAgent, trimmedAgentID)
}

// CanonicalUserAvatarURL returns the stable browser-facing avatar URL for a user.
func CanonicalUserAvatarURL(userID string) string {
	return "/api/v1/users/" + url.PathEscape(strings.TrimSpace(userID)) + "/avatar"
}

// CanonicalAgentAvatarURL returns the stable browser-facing avatar URL for an agent.
func CanonicalAgentAvatarURL(workspaceID string, agentID string) string {
	return "/api/v1/workspaces/" + url.PathEscape(strings.TrimSpace(workspaceID)) + "/agents/" + url.PathEscape(strings.TrimSpace(agentID)) + "/avatar"
}

// VersionedUserAvatarURL returns a browser-facing user avatar URL that changes
// after every successful upload while retaining the canonical resource route.
func VersionedUserAvatarURL(userID string, avatarID string) string {
	return versionedAvatarURL(CanonicalUserAvatarURL(userID), avatarID)
}

// VersionedAgentAvatarURL returns a browser-facing agent avatar URL that
// changes after every successful upload while retaining the canonical resource route.
func VersionedAgentAvatarURL(workspaceID string, agentID string, avatarID string) string {
	return versionedAvatarURL(CanonicalAgentAvatarURL(workspaceID, agentID), avatarID)
}

func versionedAvatarURL(canonicalURL string, avatarID string) string {
	trimmedAvatarID := strings.TrimSpace(avatarID)
	if trimmedAvatarID == "" {
		return canonicalURL
	}
	return canonicalURL + "?v=" + url.QueryEscape(trimmedAvatarID)
}

// AvatarAssetURL returns the immutable browser-facing URL for one
// managed avatar asset.
func AvatarAssetURL(avatarID string) string {
	trimmedID := strings.TrimSpace(avatarID)
	if trimmedID == "" {
		return ""
	}
	return "/api/v1/avatar-assets/" + url.PathEscape(trimmedID)
}

func (txs TxStore) loadLatestAvatarForOwner(ctx context.Context, workspaceID string, ownerKind string, ownerID string) (AttachmentDownload, error) {
	avatarID, err := txs.latestAvatarIDForOwner(ctx, workspaceID, ownerKind, ownerID)
	if err != nil {
		return AttachmentDownload{}, err
	}
	return txs.LoadAvatar(ctx, avatarID)
}

func (txs TxStore) latestAvatarIDForOwner(ctx context.Context, workspaceID string, ownerKind string, ownerID string) (string, error) {
	trimmedOwnerID := strings.TrimSpace(ownerID)
	if trimmedOwnerID == "" {
		return "", badRequest("ownerId is required")
	}
	query := `
SELECT id
FROM avatars
WHERE owner_kind = $1 AND owner_id = $2
`
	args := []any{strings.TrimSpace(ownerKind), trimmedOwnerID}
	if trimmedWorkspaceID := strings.TrimSpace(workspaceID); trimmedWorkspaceID != "" {
		query += " AND workspace_id = $3"
		args = append(args, trimmedWorkspaceID)
	}
	query += `
ORDER BY created_at DESC, id DESC
LIMIT 1
`
	var avatarID string
	if err := txs.tx.QueryRow(ctx, query, args...).Scan(&avatarID); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("load avatar owner: %w", err)
	}
	return avatarID, nil
}

func (txs TxStore) ClaimAttachmentsForMessage(ctx context.Context, workspaceID string, scopeID string, uploadedByMemberID string, messageID string, attachmentIDs []string) ([]AppAttachment, error) {
	if len(attachmentIDs) == 0 {
		return nil, nil
	}

	selectQuery, selectArgs, err := BuildSelectClaimableAttachmentsQuery(workspaceID, scopeID, uploadedByMemberID, attachmentIDs)
	if err != nil {
		return nil, fmt.Errorf("claim attachments: %w", err)
	}
	rows, err := txs.tx.Query(ctx, selectQuery, selectArgs...)
	if err != nil {
		return nil, fmt.Errorf("load claimable attachments: %w", err)
	}
	defer rows.Close()

	claimed := make([]AppAttachment, 0, len(attachmentIDs))
	for rows.Next() {
		var attachment AppAttachment
		if err := rows.Scan(&attachment.ID, &attachment.Filename, &attachment.ContentType, &attachment.SizeBytes); err != nil {
			return nil, fmt.Errorf("scan claimed attachment: %w", err)
		}
		claimed = append(claimed, attachment)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate claimed attachments: %w", err)
	}
	if len(claimed) != len(attachmentIDs) {
		return nil, ErrNotFound
	}

	updateQuery, updateArgs, err := BuildClaimAttachmentsForMessageUpdateQuery(workspaceID, scopeID, uploadedByMemberID, messageID, attachmentIDs)
	if err != nil {
		return nil, fmt.Errorf("claim attachments: %w", err)
	}
	commandTag, err := txs.tx.Exec(ctx, updateQuery, updateArgs...)
	if err != nil {
		return nil, fmt.Errorf("claim attachments: %w", err)
	}
	if commandTag.RowsAffected() != int64(len(claimed)) {
		return nil, ErrNotFound
	}

	return claimed, nil
}

func (txs TxStore) CopyMessageAttachments(ctx context.Context, workspaceID string, targetScopeID string, targetUploadedByMemberID string, targetMessageID string, sourceMessageIDs []string) ([]AppAttachment, error) {
	messageIDs := normalizeNonEmptyStrings(sourceMessageIDs)
	if len(messageIDs) == 0 {
		return nil, nil
	}

	rowsQuery, rowsArgs, err := BuildLoadAttachmentRowsForMessagesQuery(workspaceID, messageIDs)
	if err != nil {
		return nil, fmt.Errorf("copy message attachments: %w", err)
	}
	rows, err := txs.tx.Query(ctx, rowsQuery, rowsArgs...)
	if err != nil {
		return nil, fmt.Errorf("load copied message attachments: %w", err)
	}
	defer rows.Close()

	type sourceAttachment struct {
		Filename      string
		ContentType   string
		SizeBytes     int64
		StorageDriver string
		ObjectKey     string
		Payload       []byte
	}
	sources := make([]sourceAttachment, 0)
	for rows.Next() {
		var source sourceAttachment
		var messageOrdinality int
		var sourceMessageID string
		var sourceAttachmentID string
		var uploadedByMemberID string
		var sourceCreatedAt time.Time
		if err := rows.Scan(&messageOrdinality, &sourceMessageID, &sourceAttachmentID, &uploadedByMemberID, &source.Filename, &source.ContentType, &source.SizeBytes, &source.StorageDriver, &source.ObjectKey, &source.Payload, &sourceCreatedAt); err != nil {
			return nil, fmt.Errorf("scan copied message attachment: %w", err)
		}
		sources = append(sources, source)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate copied message attachments: %w", err)
	}
	if len(sources) == 0 {
		return nil, nil
	}

	created := make([]AppAttachment, 0, len(sources))
	now := time.Now().UTC()
	insertRows := make([]attachmentInsertRow, 0, len(sources))
	for _, source := range sources {
		attachmentID, err := identifiers.NewEntityID("att")
		if err != nil {
			return nil, fmt.Errorf("generate copied attachment id for message %s: %w", targetMessageID, err)
		}
		insertRows = append(insertRows, attachmentInsertRow{
			id:                 attachmentID,
			workspaceID:        workspaceID,
			scopeID:            targetScopeID,
			uploadedByMemberID: targetUploadedByMemberID,
			messageID:          targetMessageID,
			filename:           source.Filename,
			contentType:        source.ContentType,
			sizeBytes:          source.SizeBytes,
			storageDriver:      source.StorageDriver,
			objectKey:          source.ObjectKey,
			payload:            source.Payload,
			createdAt:          now,
		})
		created = append(created, AppAttachment{
			ID:          attachmentID,
			Filename:    source.Filename,
			ContentType: source.ContentType,
			SizeBytes:   source.SizeBytes,
		})
	}
	if err := txs.insertAttachmentRows(ctx, insertRows); err != nil {
		return nil, fmt.Errorf("insert copied attachments for message %s: %w", targetMessageID, err)
	}
	return created, nil
}

func (txs TxStore) LoadAttachmentsForMessages(ctx context.Context, workspaceID string, messageIDs []string) (map[string][]AppAttachment, error) {
	if len(messageIDs) == 0 {
		return map[string][]AppAttachment{}, nil
	}

	query, args, err := BuildLoadAttachmentsForMessagesQuery(workspaceID, messageIDs)
	if err != nil {
		return nil, fmt.Errorf("load message attachments: %w", err)
	}

	rows, err := txs.tx.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load message attachments: %w", err)
	}
	defer rows.Close()

	attachments := make(map[string][]AppAttachment)
	for rows.Next() {
		var messageID string
		var attachment AppAttachment
		if err := rows.Scan(&messageID, &attachment.ID, &attachment.Filename, &attachment.ContentType, &attachment.SizeBytes); err != nil {
			return nil, fmt.Errorf("scan message attachment: %w", err)
		}
		if messageID == "" {
			continue
		}
		attachments[messageID] = append(attachments[messageID], attachment)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate message attachments: %w", err)
	}
	return attachments, nil
}

func normalizeNonEmptyStrings(values []string) []string {
	if len(values) == 0 {
		return nil
	}
	normalized := make([]string, 0, len(values))
	seen := make(map[string]struct{}, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	return normalized
}

func nullableString(value string) any {
	trimmedValue := strings.TrimSpace(value)
	if trimmedValue == "" {
		return nil
	}
	return trimmedValue
}

func (txs TxStore) insertAttachmentRows(ctx context.Context, rows []attachmentInsertRow) error {
	if len(rows) == 0 {
		return nil
	}

	values := make([]string, 0, len(rows))
	args := make([]any, 0, len(rows)*12)
	for _, row := range rows {
		base := len(args) + 1
		values = append(values, fmt.Sprintf(
			"($%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d)",
			base, base+1, base+2, base+3, base+4, base+5, base+6, base+7, base+8, base+9, base+10, base+11,
		))
		args = append(args,
			row.id,
			row.workspaceID,
			row.scopeID,
			row.uploadedByMemberID,
			nullableString(row.messageID),
			row.filename,
			row.contentType,
			row.sizeBytes,
			row.storageDriver,
			nullableString(row.objectKey),
			row.payload,
			row.createdAt,
		)
	}

	_, err := txs.tx.Exec(ctx, fmt.Sprintf(`
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, message_id, filename, content_type, size_bytes, storage_driver, object_key, payload, created_at)
VALUES %s
`, strings.Join(values, ",\n")), args...)
	return err
}

func BuildSelectClaimableAttachmentsQuery(workspaceID string, scopeID string, uploadedByMemberID string, attachmentIDs []string) (string, []any, error) {
	cte, attachmentArgs, _, err := coredb.BuildStringValuesCTE("attachment_ids", "attachment_id", attachmentIDs, 4)
	if err != nil {
		return "", nil, err
	}

	query := fmt.Sprintf(`
WITH %s
SELECT a.id, a.filename, a.content_type, a.size_bytes
FROM attachments AS a
WHERE a.workspace_id = $1
  AND a.scope_id = $2
  AND a.uploaded_by_member_id = $3
  AND a.message_id IS NULL
  AND EXISTS (
      SELECT 1
      FROM attachment_ids
      WHERE attachment_ids.attachment_id = a.id
  )
ORDER BY a.created_at ASC, a.id ASC
FOR UPDATE
`, cte)
	args := append([]any{workspaceID, scopeID, uploadedByMemberID}, attachmentArgs...)
	return query, args, nil
}

func BuildClaimAttachmentsForMessageUpdateQuery(workspaceID string, scopeID string, uploadedByMemberID string, messageID string, attachmentIDs []string) (string, []any, error) {
	cte, attachmentArgs, _, err := coredb.BuildStringValuesCTE("attachment_ids", "attachment_id", attachmentIDs, 5)
	if err != nil {
		return "", nil, err
	}

	query := fmt.Sprintf(`
WITH %s
UPDATE attachments AS a
SET message_id = $4
WHERE a.workspace_id = $1
  AND a.scope_id = $2
  AND a.uploaded_by_member_id = $3
  AND a.message_id IS NULL
  AND EXISTS (
      SELECT 1
      FROM attachment_ids
      WHERE attachment_ids.attachment_id = a.id
  )
`, cte)
	args := append([]any{workspaceID, scopeID, uploadedByMemberID, messageID}, attachmentArgs...)
	return query, args, nil
}

func BuildLoadAttachmentsForMessagesQuery(workspaceID string, messageIDs []string) (string, []any, error) {
	cte, messageArgs, _, err := coredb.BuildIndexedStringValuesCTE("message_ids", "message_id", "message_ordinality", messageIDs, 2)
	if err != nil {
		return "", nil, err
	}

	query := fmt.Sprintf(`
WITH %s
SELECT a.message_id, a.id, a.filename, a.content_type, a.size_bytes
FROM attachments AS a
JOIN message_ids ON message_ids.message_id = a.message_id
WHERE a.workspace_id = $1
ORDER BY message_ids.message_ordinality ASC, a.created_at ASC, a.id ASC
`, cte)
	args := append([]any{workspaceID}, messageArgs...)
	return query, args, nil
}

func BuildLoadAttachmentRowsForMessagesQuery(workspaceID string, messageIDs []string) (string, []any, error) {
	cte, messageArgs, _, err := coredb.BuildIndexedStringValuesCTE("message_ids", "message_id", "message_ordinality", messageIDs, 2)
	if err != nil {
		return "", nil, err
	}

	query := fmt.Sprintf(`
WITH %s
SELECT message_ids.message_ordinality, a.message_id, a.id, a.uploaded_by_member_id, a.filename, a.content_type, a.size_bytes, COALESCE(a.storage_driver, ''), COALESCE(a.object_key, ''), a.payload, a.created_at
FROM attachments AS a
JOIN message_ids ON message_ids.message_id = a.message_id
WHERE a.workspace_id = $1
ORDER BY message_ids.message_ordinality ASC, a.created_at ASC, a.id ASC
`, cte)

	args := append([]any{workspaceID}, messageArgs...)
	return query, args, nil
}