package taskboard

import (
	"context"
	"errors"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/message"
	"github.com/tidbcloud/AgentTeam/internal/store/taskcompat"
	"github.com/tidbcloud/AgentTeam/internal/store/tasktimeline"
)

func (txs TxStore) UpdateTask(ctx context.Context, request UpdateTaskRequest) (UpdateTaskResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return UpdateTaskResponse{}, err
	}

	switch request.Context.principalType() {
	case PrincipalTypeUser:
		userID, _, err := request.Context.requireUser()
		if err != nil {
			return UpdateTaskResponse{}, err
		}
		taskID, err := request.Task.requireTaskID()
		if err != nil {
			return UpdateTaskResponse{}, err
		}
		task, err := txs.updateTaskForUser(ctx, userID, workspaceID, taskID, request.Patch)
		if err != nil {
			return UpdateTaskResponse{}, err
		}
		return UpdateTaskResponse{Task: task}, nil
	case PrincipalTypeAgent:
		agent, err := request.Context.requireAgentActor()
		if err != nil {
			return UpdateTaskResponse{}, err
		}
		taskNumber, err := request.Task.requireTaskNumber()
		if err != nil {
			return UpdateTaskResponse{}, err
		}
		task, err := txs.updateTaskForAgent(ctx, agent, request.Board, taskNumber, request.Patch)
		if err != nil {
			return UpdateTaskResponse{}, err
		}
		return UpdateTaskResponse{Task: task}, nil
	default:
		return UpdateTaskResponse{}, badRequest("context subject principalType is required")
	}
}

func (txs TxStore) UpdateTaskStatus(ctx context.Context, request UpdateTaskStatusRequest) (UpdateTaskStatusResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return UpdateTaskStatusResponse{}, err
	}
	desiredStatus, err := ParseStatus(request.Status)
	if err != nil {
		return UpdateTaskStatusResponse{}, err
	}

	switch request.Context.principalType() {
	case PrincipalTypeUser:
		userID, _, err := request.Context.requireUser()
		if err != nil {
			return UpdateTaskStatusResponse{}, err
		}
		taskID, err := request.Task.requireTaskID()
		if err != nil {
			return UpdateTaskStatusResponse{}, err
		}
		task, changed, err := txs.updateTaskStatusForUser(ctx, userID, workspaceID, taskID, desiredStatus)
		if err != nil {
			return UpdateTaskStatusResponse{}, err
		}
		return UpdateTaskStatusResponse{Task: task, Changed: changed}, nil
	case PrincipalTypeAgent:
		agent, err := request.Context.requireAgentActor()
		if err != nil {
			return UpdateTaskStatusResponse{}, err
		}
		taskNumber, err := request.Task.requireTaskNumber()
		if err != nil {
			return UpdateTaskStatusResponse{}, err
		}
		task, changed, err := txs.updateTaskStatusForAgent(ctx, agent, request.Board, taskNumber, desiredStatus)
		if err != nil {
			return UpdateTaskStatusResponse{}, err
		}
		return UpdateTaskStatusResponse{Task: task, Changed: changed}, nil
	default:
		return UpdateTaskStatusResponse{}, badRequest("context subject principalType is required")
	}
}

func (txs TxStore) BlockTask(ctx context.Context, request BlockTaskRequest) (BlockTaskResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return BlockTaskResponse{}, err
	}
	if err := validateTaskBlockInput(request.Block); err != nil {
		return BlockTaskResponse{}, err
	}

	switch request.Context.principalType() {
	case PrincipalTypeUser:
		userID, _, err := request.Context.requireUser()
		if err != nil {
			return BlockTaskResponse{}, err
		}
		taskID, err := request.Task.requireTaskID()
		if err != nil {
			return BlockTaskResponse{}, err
		}
		task, changed, err := txs.BlockTaskForUser(ctx, userID, workspaceID, taskID, request.Block)
		if err != nil {
			return BlockTaskResponse{}, err
		}
		return BlockTaskResponse{Task: task, Changed: changed}, nil
	case PrincipalTypeAgent:
		agent, err := request.Context.requireAgentActor()
		if err != nil {
			return BlockTaskResponse{}, err
		}
		taskNumber, err := request.Task.requireTaskNumber()
		if err != nil {
			return BlockTaskResponse{}, err
		}
		task, changed, err := txs.BlockTaskForAgent(ctx, agent, request.Board, taskNumber, request.Block)
		if err != nil {
			return BlockTaskResponse{}, err
		}
		return BlockTaskResponse{Task: task, Changed: changed}, nil
	default:
		return BlockTaskResponse{}, badRequest("context subject principalType is required")
	}
}

func (txs TxStore) UnblockTask(ctx context.Context, request UnblockTaskRequest) (UnblockTaskResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return UnblockTaskResponse{}, err
	}

	switch request.Context.principalType() {
	case PrincipalTypeUser:
		userID, _, err := request.Context.requireUser()
		if err != nil {
			return UnblockTaskResponse{}, err
		}
		taskID, err := request.Task.requireTaskID()
		if err != nil {
			return UnblockTaskResponse{}, err
		}
		task, changed, err := txs.UnblockTaskForUser(ctx, userID, workspaceID, taskID)
		if err != nil {
			return UnblockTaskResponse{}, err
		}
		return UnblockTaskResponse{Task: task, Changed: changed}, nil
	case PrincipalTypeAgent:
		agent, err := request.Context.requireAgentActor()
		if err != nil {
			return UnblockTaskResponse{}, err
		}
		taskNumber, err := request.Task.requireTaskNumber()
		if err != nil {
			return UnblockTaskResponse{}, err
		}
		task, changed, err := txs.UnblockTaskForAgent(ctx, agent, request.Board, taskNumber)
		if err != nil {
			return UnblockTaskResponse{}, err
		}
		return UnblockTaskResponse{Task: task, Changed: changed}, nil
	default:
		return UnblockTaskResponse{}, badRequest("context subject principalType is required")
	}
}

func (txs TxStore) updateTaskForUser(ctx context.Context, userID string, workspaceID string, taskID string, patch TaskUpdatePatch) (Task, error) {
	channelID, _, _, err := txs.loadTaskMutationState(ctx, txs.tx, workspaceID, taskID)
	if err != nil {
		return Task{}, err
	}
	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, txs.tx, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return Task{}, err
	}
	actorMemberID := channelAccess.WorkspaceMember.Member.ID
	authorizeAssigneeClear := func(ctx context.Context, tx coredb.Tx, currentAssigneeMemberID string) error {
		if strings.TrimSpace(currentAssigneeMemberID) == strings.TrimSpace(actorMemberID) {
			return nil
		}
		anchor, err := txs.loadTaskCollaboratorTaskAnchor(ctx, tx, workspaceID, taskID)
		if err != nil {
			return err
		}
		return txs.authorizeTaskCollaboratorManager(ctx, tx, workspaceID, actorMemberID, anchor.ExecutionOwnerMemberID)
	}
	return txs.updateTaskInTx(ctx, txs.tx, workspaceID, channelID, taskID, actorMemberID, patch, false, authorizeAssigneeClear, humanTaskFencerFor(userID, workspaceID, taskWriteChannelAction))
}

func (txs TxStore) updateTaskForAgent(ctx context.Context, agent AgentActor, board BoardRef, taskNumber int64, patch TaskUpdatePatch) (Task, error) {
	channelID, taskID, err := txs.loadAccessibleAgentTaskID(ctx, txs.tx, agent, board, taskNumber)
	if err != nil {
		return Task{}, err
	}
	authorizeAssigneeClear := func(_ context.Context, _ coredb.Tx, currentAssigneeMemberID string) error {
		if strings.TrimSpace(currentAssigneeMemberID) != strings.TrimSpace(agent.MemberID) {
			return ErrForbidden
		}
		return nil
	}
	return txs.updateTaskInTx(ctx, txs.tx, agent.WorkspaceID, channelID, taskID, agent.MemberID, patch, true, authorizeAssigneeClear, agentTaskFencerFor(agent))
}

func taskUpdateClearsAssignee(patch TaskUpdatePatch) bool {
	return patch.AssigneeRef != nil && strings.TrimSpace(*patch.AssigneeRef) == ""
}

func (txs TxStore) updateTaskStatusForUser(ctx context.Context, userID string, workspaceID string, taskID string, desiredStatus Status) (Task, bool, error) {
	// Resolve the actor member id (a read); the authoritative lock is the human
	// fencer inside updateTaskStatusInTx.
	memberID, err := loadTaskReadMemberID(ctx, txs.tx, workspaceID, userID)
	if err != nil {
		return Task{}, false, err
	}
	return txs.updateTaskStatusInTx(ctx, txs.tx, workspaceID, taskID, memberID, desiredStatus, humanTaskFencerFor(userID, workspaceID, taskWriteChannelAction))
}

func (txs TxStore) updateTaskStatusForAgent(ctx context.Context, agent AgentActor, board BoardRef, taskNumber int64, desiredStatus Status) (Task, bool, error) {
	channelID, taskID, err := txs.loadAccessibleAgentTaskID(ctx, txs.tx, agent, board, taskNumber)
	if err != nil {
		return Task{}, false, err
	}
	task, changed, err := txs.updateTaskStatusInTx(ctx, txs.tx, agent.WorkspaceID, taskID, agent.MemberID, desiredStatus, agentTaskFencerFor(agent))
	if err != nil {
		return Task{}, false, err
	}
	if task.ChannelID == "" {
		task.ChannelID = channelID
	}
	return task, changed, nil
}

func (txs TxStore) BlockTaskForUser(ctx context.Context, userID string, workspaceID string, taskID string, input TaskBlockInput) (Task, bool, error) {
	tx := txs.tx
	channelID, _, _, err := txs.lockTaskRow(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, tx, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return Task{}, false, err
	}
	// TOCTOU fence: the Task row is already locked (lockTaskRow FOR UPDATE);
	// lock the user's active membership on the canonical board Channel before
	// writing, so a concurrent board-membership revoke serializes deterministically.
	if err := txs.lockActiveHumanBoardChannelAuthority(ctx, tx, userID, workspaceID, channelID); err != nil {
		return Task{}, false, err
	}
	return txs.blockTaskInTx(ctx, tx, workspaceID, taskID, channelAccess.WorkspaceMember.Member.ID, input)
}

func (txs TxStore) BlockTaskForAgent(ctx context.Context, agent AgentActor, board BoardRef, taskNumber int64, input TaskBlockInput) (Task, bool, error) {
	tx := txs.tx
	channelID, taskID, err := txs.loadAccessibleAgentTaskID(ctx, tx, agent, board, taskNumber)
	if err != nil {
		return Task{}, false, err
	}
	// TOCTOU fence: lock the canonical Task row FOR UPDATE (deriving the board
	// Channel server-side) and the agent's active membership on it in a fixed
	// order, so a concurrent board-membership revoke / Task delete / board move
	// serializes deterministically.
	if _, err := txs.lockTaskBoardChannelAuthority(ctx, tx, agent.WorkspaceID, taskID, agent.MemberID); err != nil {
		return Task{}, false, err
	}
	task, changed, err := txs.blockTaskInTx(ctx, tx, agent.WorkspaceID, taskID, agent.MemberID, input)
	if err != nil {
		return Task{}, false, err
	}
	if task.ChannelID == "" {
		task.ChannelID = channelID
	}
	return task, changed, nil
}

func (txs TxStore) UnblockTaskForUser(ctx context.Context, userID string, workspaceID string, taskID string) (Task, bool, error) {
	tx := txs.tx
	channelID, _, _, err := txs.lockTaskRow(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, tx, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return Task{}, false, err
	}
	// TOCTOU fence: the Task row is already locked (lockTaskRow FOR UPDATE);
	// lock the user's active membership on the canonical board Channel before
	// writing, so a concurrent board-membership revoke serializes deterministically.
	if err := txs.lockActiveHumanBoardChannelAuthority(ctx, tx, userID, workspaceID, channelID); err != nil {
		return Task{}, false, err
	}
	return txs.unblockTaskInTx(ctx, tx, workspaceID, taskID, channelAccess.WorkspaceMember.Member.ID)
}

func (txs TxStore) UnblockTaskForAgent(ctx context.Context, agent AgentActor, board BoardRef, taskNumber int64) (Task, bool, error) {
	tx := txs.tx
	channelID, taskID, err := txs.loadAccessibleAgentTaskID(ctx, tx, agent, board, taskNumber)
	if err != nil {
		return Task{}, false, err
	}
	// TOCTOU fence: lock the canonical Task row FOR UPDATE (deriving the board
	// Channel server-side) and the agent's active membership on it in a fixed
	// order, so a concurrent board-membership revoke / Task delete / board move
	// serializes deterministically.
	if _, err := txs.lockTaskBoardChannelAuthority(ctx, tx, agent.WorkspaceID, taskID, agent.MemberID); err != nil {
		return Task{}, false, err
	}
	task, changed, err := txs.unblockTaskInTx(ctx, tx, agent.WorkspaceID, taskID, agent.MemberID)
	if err != nil {
		return Task{}, false, err
	}
	if task.ChannelID == "" {
		task.ChannelID = channelID
	}
	return task, changed, nil
}

func (txs TxStore) blockTaskInTx(ctx context.Context, tx coredb.Tx, workspaceID string, taskID string, actorMemberID string, input TaskBlockInput) (Task, bool, error) {
	if err := validateTaskBlockInput(input); err != nil {
		return Task{}, false, err
	}
	reason := strings.TrimSpace(input.Reason)

	task, err := txs.taskByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	if strings.TrimSpace(task.BlockedReason) == reason {
		return task, false, nil
	}

	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
UPDATE tasks
SET blocked_reason = $2,
    last_activity_at = $3,
    updated_at = $3
WHERE id = $1
`, taskID, reason, now); err != nil {
		return Task{}, false, fmt.Errorf("block task: %w", err)
	}
	if err := applyTaskTypedEventPlan(ctx, tx, taskTypedEventIntent(taskID, actorMemberID, taskEventBlocked, reason, now)); err != nil {
		return Task{}, false, err
	}
	task, err = txs.taskByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	return task, true, nil
}

func (txs TxStore) unblockTaskInTx(ctx context.Context, tx coredb.Tx, workspaceID string, taskID string, actorMemberID string) (Task, bool, error) {
	task, err := txs.taskByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	previousReason := strings.TrimSpace(task.BlockedReason)
	if previousReason == "" {
		return task, false, nil
	}

	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
UPDATE tasks
SET blocked_reason = '',
    last_activity_at = $2,
    updated_at = $2
WHERE id = $1
`, taskID, now); err != nil {
		return Task{}, false, fmt.Errorf("unblock task: %w", err)
	}
	if err := applyTaskTypedEventPlan(ctx, tx, taskTypedEventIntent(taskID, actorMemberID, taskEventUnblocked, previousReason, now)); err != nil {
		return Task{}, false, err
	}
	task, err = txs.taskByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	return task, true, nil
}

func (txs TxStore) updateTaskStatusInTx(ctx context.Context, tx coredb.Tx, workspaceID string, taskID string, actorMemberID string, desiredStatus Status, fencer taskAuthorityFencer) (Task, bool, error) {
	// TOCTOU fence: lock the canonical Task row (deriving the board Channel
	// server-side) then revalidate the actor's active authority chain under the
	// shared lock order, so a concurrent revoke / archive / effective-close /
	// Channel delete / member deactivate / Task move / Task delete serializes
	// deterministically and authority-loss-first fails closed.
	lockedChannel, err := fencer.lockExistingTaskChannel(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	task, err := txs.taskByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	currentStatus := Status(strings.TrimSpace(task.Status))
	if currentStatus == desiredStatus {
		return task, false, nil
	}
	if task.ChannelID == "" {
		task.ChannelID = lockedChannel
	}

	now := time.Now().UTC()
	if err := txs.applyTaskStatusUpdate(ctx, tx, workspaceID, taskID, actorMemberID, currentStatus, desiredStatus, now); err != nil {
		return Task{}, false, err
	}
	task, err = txs.taskByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return Task{}, false, err
	}
	return task, true, nil
}

type taskAssigneeClearAuthorizer func(context.Context, coredb.Tx, string) error

func (txs TxStore) updateTaskInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	channelID string,
	taskID string,
	actorMemberID string,
	patch TaskUpdatePatch,
	strictFieldOptions bool,
	authorizeAssigneeClear taskAssigneeClearAuthorizer,
	fencer taskAuthorityFencer,
) (Task, error) {
	if err := validateTaskUpdatePatch(patch); err != nil {
		return Task{}, err
	}

	normalizedAcceptance, err := normalizeChecklistItems(patch.AcceptanceCriteria)
	if err != nil {
		return Task{}, err
	}
	normalizedReviewChecklist, err := normalizeChecklistItems(patch.ReviewChecklist)
	if err != nil {
		return Task{}, err
	}

	trimmedTitle := normalizeOptionalString(patch.Title)
	trimmedDescription := normalizeOptionalString(patch.Description)
	normalizedPinnedMessageRefs := normalizeOptionalStringList(patch.PinnedMessageRefs)
	normalizedRelevantURLs := normalizeOptionalStringList(patch.RelevantURLs)
	normalizedWorkspaceRefs := normalizeOptionalStringList(patch.WorkspaceRefs)
	trimmedAssigneeRef, err := resolveOptionalTaskAssigneeRefPointer(ctx, tx, workspaceID, channelID, actorMemberID, patch.AssigneeRef)
	if err != nil {
		return Task{}, err
	}
	trimmedBlockedReason := normalizeOptionalString(patch.BlockedReason)
	trimmedParentTaskRef := normalizeOptionalString(patch.ParentTaskRef)
	normalizedAttachmentIDs := normalizeUniqueStringList(patch.AttachmentIDs)

	previousAssigneeMemberID := ""
	var lockedChannel string
	if trimmedParentTaskRef != nil {
		previousAssigneeMemberID, lockedChannel, err = txs.lockTaskParentMembershipUpdate(ctx, tx, workspaceID, channelID, taskID, *trimmedParentTaskRef)
		if err != nil {
			return Task{}, err
		}
		// TOCTOU fence: the parent/child task rows are locked above; revalidate the
		// actor's active authority chain on the server-derived board Channel.
		if err := fencer.lockChannelAuthority(ctx, tx, lockedChannel); err != nil {
			return Task{}, err
		}
	} else if trimmedAssigneeRef != nil {
		lockedChannel, _, previousAssigneeMemberID, err = txs.lockTaskRow(ctx, tx, workspaceID, taskID)
		if err != nil {
			return Task{}, err
		}
		// TOCTOU fence: the Task row is locked; revalidate the actor's active
		// authority chain on the server-derived board Channel.
		if err := fencer.lockChannelAuthority(ctx, tx, lockedChannel); err != nil {
			return Task{}, err
		}
	} else {
		// No parent or assignee change: the TOCTOU fence locks the canonical
		// Task->board Channel row (deriving the Channel server-side from the
		// current board) and revalidates the actor's active authority chain, so a
		// concurrent revoke / archive / close / Channel delete / member deactivate
		// / Task delete / board move serializes deterministically.
		lockedChannel, err = fencer.lockExistingTaskChannel(ctx, tx, workspaceID, taskID)
		if err != nil {
			return Task{}, err
		}
	}
	if taskUpdateClearsAssignee(patch) {
		if authorizeAssigneeClear == nil {
			return Task{}, ErrForbidden
		}
		if err := authorizeAssigneeClear(ctx, tx, previousAssigneeMemberID); err != nil {
			return Task{}, err
		}
	}
	if trimmedTitle != nil && *trimmedTitle == "" {
		return Task{}, badRequest("title is required")
	}
	kindUpdate, err := txs.resolveTaskFieldUpdate(ctx, tx, workspaceID, taskFieldKindKind, patch.KindOptionID, patch.Kind, strictFieldOptions)
	if err != nil {
		return Task{}, err
	}
	priorityUpdate, err := txs.resolveTaskFieldUpdate(ctx, tx, workspaceID, taskFieldKindPriority, patch.PriorityOptionID, patch.Priority, strictFieldOptions)
	if err != nil {
		return Task{}, err
	}

	hasScalarChanges := trimmedTitle != nil ||
		trimmedDescription != nil ||
		kindUpdate.Present ||
		priorityUpdate.Present ||
		trimmedAssigneeRef != nil ||
		trimmedBlockedReason != nil ||
		trimmedParentTaskRef != nil
	hasContextListChanges := patch.PinnedMessageRefs != nil ||
		patch.RelevantURLs != nil ||
		patch.WorkspaceRefs != nil
	hasChecklistChanges := patch.AcceptanceCriteria != nil || patch.ReviewChecklist != nil
	hasAttachmentClaims := len(normalizedAttachmentIDs) > 0
	if !hasScalarChanges && !hasContextListChanges && !hasChecklistChanges && !hasAttachmentClaims {
		return txs.taskByID(ctx, tx, workspaceID, taskID)
	}

	now := time.Now().UTC()
	if hasAttachmentClaims {
		if err := txs.anchorTaskDescriptionAttachments(ctx, tx, workspaceID, channelID, taskID, actorMemberID, strictFieldOptions, normalizedAttachmentIDs, now); err != nil {
			return Task{}, err
		}
	}
	if hasScalarChanges {
		if _, err := tx.Exec(ctx, `
UPDATE tasks
SET title = CASE WHEN $2 THEN $3 ELSE title END,
    description = CASE WHEN $4 THEN $5 ELSE description END,
    kind = CASE WHEN $6 THEN $7 ELSE kind END,
    kind_option_id = CASE WHEN $6 THEN $8 ELSE kind_option_id END,
    legacy_kind_value = CASE WHEN $6 THEN $9 ELSE legacy_kind_value END,
    priority = CASE WHEN $10 THEN $11 ELSE priority END,
    priority_option_id = CASE WHEN $10 THEN $12 ELSE priority_option_id END,
    legacy_priority_value = CASE WHEN $10 THEN $13 ELSE legacy_priority_value END,
    assignee_member_id = CASE WHEN $14 THEN NULLIF($15, '') ELSE assignee_member_id END,
    assigned_at = CASE
        WHEN NOT $14 THEN assigned_at
        WHEN NULLIF($15, '') IS NULL THEN NULL
        WHEN COALESCE(`+taskcompat.EffectiveAssigneeRefSQL("tasks")+`, '') <> $15 THEN $20
        ELSE assigned_at
    END,
    claimed_by_member_id = CASE WHEN $14 THEN NULLIF($15, '') ELSE claimed_by_member_id END,
    claimed_by_ref = CASE WHEN $14 THEN NULLIF($15, '') ELSE claimed_by_ref END,
    claimed_at = CASE
        WHEN NOT $14 THEN claimed_at
        WHEN NULLIF($15, '') IS NULL THEN NULL
        WHEN COALESCE(`+taskcompat.EffectiveAssigneeRefSQL("tasks")+`, '') <> $15 THEN $20
        ELSE claimed_at
    END,
    current_agent_ref = CASE WHEN $14 THEN NULL ELSE current_agent_ref END,
    suggested_assignee_ref = CASE WHEN $14 THEN '' ELSE suggested_assignee_ref END,
    reviewer_ref = CASE WHEN $14 THEN '' ELSE reviewer_ref END,
    blocked_reason = CASE WHEN $16 THEN $17 ELSE blocked_reason END,
    parent_task_ref = CASE WHEN $18 THEN $19 ELSE parent_task_ref END,
    last_activity_at = $20,
    updated_at = $20
WHERE id = $1
`, taskID,
			trimmedTitle != nil, valueOrEmpty(trimmedTitle),
			trimmedDescription != nil, valueOrEmpty(trimmedDescription),
			kindUpdate.Present, kindUpdate.EchoValue, kindUpdate.OptionID, kindUpdate.LegacyValue,
			priorityUpdate.Present, priorityUpdate.EchoValue, priorityUpdate.OptionID, priorityUpdate.LegacyValue,
			trimmedAssigneeRef != nil, valueOrEmpty(trimmedAssigneeRef),
			trimmedBlockedReason != nil, valueOrEmpty(trimmedBlockedReason),
			trimmedParentTaskRef != nil, valueOrEmpty(trimmedParentTaskRef),
			now,
		); err != nil {
			return Task{}, fmt.Errorf("update task detail: %w", err)
		}
	}
	if hasContextListChanges {
		pinnedMessageRefsJSON, err := marshalStringListJSON(normalizedPinnedMessageRefs)
		if err != nil {
			return Task{}, err
		}
		relevantURLsJSON, err := marshalStringListJSON(normalizedRelevantURLs)
		if err != nil {
			return Task{}, err
		}
		workspaceRefsJSON, err := marshalStringListJSON(normalizedWorkspaceRefs)
		if err != nil {
			return Task{}, err
		}
		if _, err := tx.Exec(ctx, fmt.Sprintf(`
UPDATE tasks
SET pinned_message_refs_json = CASE WHEN $2 THEN %s ELSE pinned_message_refs_json END,
    relevant_urls_json = CASE WHEN $4 THEN %s ELSE relevant_urls_json END,
    workspace_refs_json = CASE WHEN $6 THEN %s ELSE workspace_refs_json END,
    last_activity_at = $8,
    updated_at = $8
WHERE id = $1
`, taskboardJSONParamExpression(tx, 3), taskboardJSONParamExpression(tx, 5), taskboardJSONParamExpression(tx, 7)), taskID,
			patch.PinnedMessageRefs != nil, pinnedMessageRefsJSON,
			patch.RelevantURLs != nil, relevantURLsJSON,
			patch.WorkspaceRefs != nil, workspaceRefsJSON,
			now,
		); err != nil {
			return Task{}, fmt.Errorf("update task context refs: %w", err)
		}
	}
	if patch.AcceptanceCriteria != nil {
		if err := replaceTaskChecklist(ctx, tx, taskID, taskChecklistKindAcceptanceCriteria, normalizedAcceptance, now); err != nil {
			return Task{}, err
		}
	}
	if patch.ReviewChecklist != nil {
		if err := replaceTaskChecklist(ctx, tx, taskID, taskChecklistKindReviewChecklist, normalizedReviewChecklist, now); err != nil {
			return Task{}, err
		}
	}
	if hasChecklistChanges {
		if _, err := tx.Exec(ctx, `
UPDATE tasks
SET last_activity_at = $2,
    updated_at = $2
WHERE id = $1
`, taskID, now); err != nil {
			return Task{}, fmt.Errorf("touch task after checklist update: %w", err)
		}
	}
	if hasScalarChanges {
		taskScopeID, err := loadTaskScopeIDForParticipantNotification(ctx, tx, taskID)
		if err != nil {
			return Task{}, err
		}
		if err := ensureTaskScopeParticipantsDefaultUnwatched(ctx, tx, taskScopeID, []string{valueOrEmpty(trimmedAssigneeRef)}, now); err != nil {
			return Task{}, err
		}
	}
	activityIntents := make([]tasktimeline.TypedActivityIntent, 0, 3)
	if hasScalarChanges || hasContextListChanges {
		activityIntents = append(activityIntents, taskTypedEventIntent(taskID, actorMemberID, taskEventUpdated, "", now))
	}
	if trimmedAssigneeRef != nil && previousAssigneeMemberID != valueOrEmpty(trimmedAssigneeRef) {
		eventType := taskEventAssigned
		payload := valueOrEmpty(trimmedAssigneeRef)
		if payload == "" {
			eventType = taskEventUnassigned
			payload = previousAssigneeMemberID
		}
		activityIntents = append(activityIntents, taskTypedEventIntent(taskID, actorMemberID, eventType, payload, now))
	}
	if hasChecklistChanges {
		activityIntents = append(activityIntents, taskTypedEventIntent(taskID, actorMemberID, taskEventChecklistUpdated, "", now))
	}
	if len(activityIntents) > 0 {
		if err := applyTaskTypedEventPlan(ctx, tx, activityIntents...); err != nil {
			return Task{}, err
		}
	}
	return txs.taskByID(ctx, tx, workspaceID, taskID)
}

func (txs TxStore) lockTaskParentMembershipUpdate(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	channelID string,
	taskID string,
	newParentTaskID string,
) (string, string, error) {
	oldParentTaskID, err := txs.taskParentRefByID(ctx, tx, workspaceID, taskID)
	if err != nil {
		return "", "", err
	}
	for _, parentTaskID := range uniqueSortedTaskIDs(oldParentTaskID, newParentTaskID) {
		if _, _, _, err := txs.lockTaskRow(ctx, tx, workspaceID, parentTaskID); err != nil {
			if strings.TrimSpace(parentTaskID) == strings.TrimSpace(newParentTaskID) && errors.Is(err, ErrNotFound) {
				return "", "", badRequest("parentTaskRef must reference a task on the same board")
			}
			return "", "", err
		}
	}
	lockedChannel, _, previousAssigneeMemberID, err := txs.lockTaskRow(ctx, tx, workspaceID, taskID)
	if err != nil {
		return "", "", err
	}
	lockedOldParentTaskID, err := txs.taskParentRefByIDForUpdate(ctx, tx, workspaceID, taskID)
	if err != nil {
		return "", "", err
	}
	if strings.TrimSpace(lockedOldParentTaskID) != strings.TrimSpace(oldParentTaskID) {
		return "", "", conflict("task parent changed concurrently")
	}
	if strings.TrimSpace(newParentTaskID) != "" {
		if err := ensureParentTaskRef(ctx, tx, workspaceID, channelID, taskID, newParentTaskID); err != nil {
			return "", "", err
		}
	}
	return previousAssigneeMemberID, lockedChannel, nil
}

func (txs TxStore) taskParentRefByID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, taskID string) (string, error) {
	var parentTaskID string
	if err := queryer.QueryRow(ctx, `
SELECT COALESCE(t.parent_task_ref, '')
FROM tasks t
JOIN task_boards tb
  ON tb.id = t.board_id
 AND tb.workspace_id = $1
JOIN scopes task_scope
  ON task_scope.id = t.task_scope_id
 AND task_scope.workspace_id = $1
 AND task_scope.type = 'task'
 AND task_scope.status = 'active'
 AND task_scope.archived_at IS NULL
JOIN scopes task_channel
  ON task_channel.id = tb.channel_id
 AND task_channel.workspace_id = $1
 AND task_channel.type = 'channel'
 AND task_channel.status = 'active'
 AND task_channel.archived_at IS NULL
WHERE t.channel_id = tb.channel_id
  AND t.id = $2
`, workspaceID, taskID).Scan(&parentTaskID); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("load task parent reference: %w", err)
	}
	return strings.TrimSpace(parentTaskID), nil
}

func (txs TxStore) taskParentRefByIDForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, taskID string) (string, error) {
	var parentTaskID string
	if err := queryer.QueryRow(ctx, `
SELECT COALESCE(t.parent_task_ref, '')
FROM tasks t
JOIN task_boards tb
  ON tb.id = t.board_id
 AND tb.workspace_id = $1
JOIN scopes task_scope
  ON task_scope.id = t.task_scope_id
 AND task_scope.workspace_id = $1
 AND task_scope.type = 'task'
 AND task_scope.status = 'active'
 AND task_scope.archived_at IS NULL
JOIN scopes task_channel
  ON task_channel.id = tb.channel_id
 AND task_channel.workspace_id = $1
 AND task_channel.type = 'channel'
 AND task_channel.status = 'active'
 AND task_channel.archived_at IS NULL
WHERE t.channel_id = tb.channel_id
  AND t.id = $2
FOR UPDATE
`, workspaceID, taskID).Scan(&parentTaskID); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("lock task parent reference: %w", err)
	}
	return strings.TrimSpace(parentTaskID), nil
}

func uniqueSortedTaskIDs(taskIDs ...string) []string {
	unique := make(map[string]struct{}, len(taskIDs))
	for _, taskID := range taskIDs {
		if trimmed := strings.TrimSpace(taskID); trimmed != "" {
			unique[trimmed] = struct{}{}
		}
	}
	sorted := make([]string, 0, len(unique))
	for taskID := range unique {
		sorted = append(sorted, taskID)
	}
	sort.Strings(sorted)
	return sorted
}

func (txs TxStore) anchorTaskDescriptionAttachments(ctx context.Context, tx coredb.Tx, workspaceID string, channelID string, taskID string, actorMemberID string, allowAgentAuthor bool, attachmentIDs []string, now time.Time) error {
	normalizedAttachmentIDs := normalizeUniqueStringList(attachmentIDs)
	if len(normalizedAttachmentIDs) == 0 {
		return nil
	}
	// Description markdown is read with task channel authority, not through the
	// visible task discussion timeline. Anchor the uploaded sidecars to a hidden
	// channel-scope row so attachment download uses the same readable scope.
	messageID, err := identifiers.NewMessageID()
	if err != nil {
		return fmt.Errorf("generate task description attachment anchor id: %w", err)
	}
	seq, err := message.AllocateNextScopeMessageSeq(ctx, tx, channelID)
	if err != nil {
		return err
	}
	plan := message.MessagePlan{
		SideEffectProfile: message.MessagePlanSideEffectProfileNoOutbox,
		MessageWrite: message.ScopeMessageRecord{
			ID:                 messageID,
			WorkspaceID:        workspaceID,
			ScopeID:            channelID,
			Seq:                seq,
			WorkspaceGlobalSeq: seq,
			AuthorMemberID:     actorMemberID,
			AuthorType:         taskScopeDiscussionAuthorType(allowAgentAuthor),
			MessageType:        message.MessageTypeTaskDescriptionAttachment,
			CreatedAt:          now,
			UpdatedAt:          now,
		},
		SidecarWrites: message.MessageSidecarWrites{
			AttachmentClaims: []message.AttachmentClaimWrite{{
				WorkspaceID:        workspaceID,
				ScopeID:            channelID,
				UploadedByMemberID: actorMemberID,
				MessageID:          messageID,
				AttachmentIDs:      normalizedAttachmentIDs,
			}},
		},
	}
	if _, err := message.ApplyMessagePlanTxWithSidecarWriter(ctx, tx, plan, taskScopeDiscussionSidecarWriter{
		channelID: channelID,
		taskID:    taskID,
	}); err != nil {
		return fmt.Errorf("anchor task description attachments: %w", err)
	}
	return nil
}