package taskboard

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

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

var (
	taskReadChannelAction = access.HumanAction{
		Resource: access.ResourceChannel,
		Action:   access.ActionRead,
	}
	taskWriteChannelAction = access.HumanAction{
		Resource: access.ResourceChannel,
		Action:   access.ActionPost,
	}
)

var taskSelectColumns = `
SELECT
    t.id,
    t.task_scope_id,
    tb.channel_id AS channel_id,
    t.task_number,
    t.title,
    t.status,
    COALESCE(t.description, ''),
    COALESCE(t.kind_option_id, ''),
    COALESCE(kind_option.option_key, ''),
    COALESCE(kind_option.display_label, ''),
    COALESCE(NULLIF(t.legacy_kind_value, ''), t.kind, ''),
    COALESCE(t.priority_option_id, ''),
    COALESCE(priority_option.option_key, ''),
    COALESCE(priority_option.display_label, ''),
    COALESCE(NULLIF(t.legacy_priority_value, ''), t.priority, ''),
    COALESCE(t.blocked_reason, ''),
    COALESCE(t.last_activity_at, t.updated_at, t.created_at),
    COALESCE((
        SELECT COUNT(*)
        FROM task_artifacts ta
        WHERE ta.task_id = t.id
    ), 0),
    COALESCE((
        SELECT COUNT(*)
        FROM task_checklist_items tci
        WHERE tci.task_id = t.id
          AND tci.list_kind = 'checkpoint'
    ), 0),
    COALESCE((
        SELECT SUM(CASE WHEN tci.is_checked THEN 1 ELSE 0 END)
        FROM task_checklist_items tci
        WHERE tci.task_id = t.id
          AND tci.list_kind = 'checkpoint'
    ), 0),
    COALESCE((
        SELECT COUNT(*)
        FROM tasks subtask
        WHERE subtask.parent_task_ref = t.id
    ), 0),
    COALESCE((
        SELECT SUM(CASE WHEN subtask.status = 'done' THEN 1 ELSE 0 END)
        FROM tasks subtask
        WHERE subtask.parent_task_ref = t.id
    ), 0),
    COALESCE(t.discussion_count, 0),
    COALESCE(t.parent_task_ref, '') AS parent_task_ref,
    t.created_at,
    ` + taskcompat.EffectiveAssignedAtSQL("t") + `,
    t.completed_at,
    COALESCE(t.source_thread_channel_id, ''),
    COALESCE(t.source_thread_parent_channel_id, ''),
    COALESCE(t.source_thread_parent_message_id, ''),
    COALESCE(t.source_thread_ref, ''),
    COALESCE(created_member.type, ''),
    COALESCE(created_member.id, ''),
    COALESCE(created_user.id, ''),
    COALESCE(created_user.display_name, ''),
    COALESCE(created_user.avatar_url, ''),
    COALESCE(created_member.display_name, ''),
    COALESCE(created_user.name, ''),
    COALESCE(created_agent.id, ''),
    COALESCE(created_agent.name, ''),
    COALESCE(created_agent.avatar_url, ''),
    COALESCE(created_service_member.service_installation_id, ''),
    COALESCE(created_service_member.service_kind, ''),
    COALESCE(created_service_member.handle, ''),
    COALESCE(created_service_member.display_name, ''),
    COALESCE(created_service_member.avatar_url, ''),
    COALESCE(created_service_definition.id, ''),
    COALESCE(created_service_definition.slug, ''),
    COALESCE(created_service_definition.name, ''),
    COALESCE(created_service_definition.avatar_url, ''),
    COALESCE(assignee_member.type, ''),
    COALESCE(assignee_member.id, ''),
    COALESCE(assignee_user.id, ''),
    COALESCE(assignee_user.display_name, ''),
    COALESCE(assignee_user.avatar_url, ''),
    COALESCE(assignee_member.display_name, ''),
    COALESCE(assignee_user.name, ''),
    COALESCE(assignee_agent.id, ''),
    COALESCE(assignee_agent.name, ''),
    COALESCE(assignee_agent.avatar_url, ''),
    '' AS assignee_service_installation_id,
    '' AS assignee_service_kind,
    '' AS assignee_service_handle,
    '' AS assignee_service_display_name,
    '' AS assignee_service_avatar_url,
    '' AS assignee_service_definition_id,
    '' AS assignee_service_definition_slug,
    '' AS assignee_service_definition_name,
    '' AS assignee_service_definition_avatar_url`

var taskSelectFromJoins = `
FROM tasks t
JOIN task_boards tb
  ON tb.id = t.board_id
JOIN scopes task_board_channel
  ON task_board_channel.id = tb.channel_id
 AND task_board_channel.workspace_id = tb.workspace_id
 AND task_board_channel.type = 'channel'
 AND task_board_channel.status = 'active'
 AND task_board_channel.archived_at IS NULL
 AND t.channel_id = tb.channel_id
JOIN scopes task_scope
  ON task_scope.id = t.task_scope_id
 AND task_scope.workspace_id = tb.workspace_id
 AND task_scope.type = 'task'
 AND task_scope.status = 'active'
 AND task_scope.archived_at IS NULL
LEFT JOIN task_field_options kind_option ON kind_option.id = NULLIF(t.kind_option_id, '')
LEFT JOIN task_field_options priority_option ON priority_option.id = NULLIF(t.priority_option_id, '')
JOIN members created_member ON created_member.id = t.created_by_ref
LEFT JOIN users created_user ON created_member.type = 'human' AND created_user.id = created_member.user_id
LEFT JOIN agents created_agent ON created_member.type = 'agent' AND created_agent.member_id = created_member.id
LEFT JOIN service_members created_service_member ON created_member.type = 'service' AND created_service_member.workspace_id = created_member.workspace_id AND created_service_member.member_id = created_member.id
LEFT JOIN service_definitions created_service_definition ON created_service_definition.id = created_service_member.service_definition_id AND created_service_definition.service_kind = created_service_member.service_kind
LEFT JOIN members assignee_member ON assignee_member.id = ` + taskcompat.EffectiveAssigneeRefSQL("t") + `
LEFT JOIN users assignee_user ON assignee_member.type = 'human' AND assignee_user.id = assignee_member.user_id
LEFT JOIN agents assignee_agent ON assignee_member.type = 'agent' AND assignee_agent.member_id = assignee_member.id
`

var taskSelectBase = taskSelectColumns + taskSelectFromJoins

func (txs TxStore) ListForUser(ctx context.Context, userID string, workspaceID string, channelID string, includeSubtasks bool) ([]Task, error) {
	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, txs.db, userID, workspaceID, channelID, taskReadChannelAction)
	if err != nil {
		return nil, err
	}
	return txs.listTasksByChannel(ctx, workspaceID, channelAccess.Channel.ChannelID, includeSubtasks)
}

func (txs TxStore) ListWorkspaceTasksForUser(ctx context.Context, userID string, workspaceID string, includeSubtasks bool) ([]Task, error) {
	workspaceMember, err := access.LoadHumanWorkspaceMember(ctx, txs.db, userID, workspaceID)
	if errors.Is(err, access.ErrForbidden) {
		return nil, ErrForbidden
	}
	if err != nil {
		return nil, fmt.Errorf("load task workspace member: %w", err)
	}
	return txs.listTasksByMemberChannels(ctx, workspaceID, workspaceMember.Member.ID, includeSubtasks)
}

func (txs TxStore) CreateForUser(ctx context.Context, userID string, userName string, workspaceID string, channelID string, inputs []TaskCreateInput, metadata CreateTaskMetadata) ([]Task, []CreatedTask, error) {
	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, txs.db, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return nil, nil, err
	}

	taskIDs, createdTasks, err := txs.createTasksInTx(ctx, txs.tx, workspaceID, channelAccess.Channel.ChannelID, channelAccess.WorkspaceMember.Member.ID, inputs, metadata, false, humanTaskFencerFor(userID, workspaceID, taskWriteChannelAction))
	if err != nil {
		return nil, nil, err
	}

	tasks, err := txs.listTasksByIDs(ctx, workspaceID, taskIDs)
	if err != nil {
		return nil, nil, err
	}
	for index := range tasks {
		if tasks[index].CreatedByProfile == nil {
			tasks[index].CreatedByProfile = taskHumanPrincipalProfile(userID, userName)
			tasks[index].CreatedByType, tasks[index].CreatedByID, tasks[index].CreatedByName = taskLegacyCreatedByFromPrincipalProfile(tasks[index].CreatedByProfile, tasks[index].CreatedByType, tasks[index].CreatedByID, tasks[index].CreatedByName)
		}
	}
	return tasks, createdTasks, nil
}

func (txs TxStore) claimForUserInTx(ctx context.Context, userID string, workspaceID string, taskID string) error {
	channelID, status, assigneeMemberID, err := txs.lockTaskRow(ctx, txs.tx, workspaceID, taskID)
	if err != nil {
		return err
	}
	if status == string(StatusDone) {
		return ErrConflict
	}

	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, txs.tx, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return 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, txs.tx, userID, workspaceID, channelID); err != nil {
		return err
	}

	if assigneeMemberID == channelAccess.WorkspaceMember.Member.ID {
		return nil
	}
	if assigneeMemberID != "" {
		return ErrConflict
	}

	now := time.Now().UTC()
	if _, err := txs.tx.Exec(ctx, `
		UPDATE tasks
		SET assignee_member_id = $2,
		    assigned_at = $3,
		    claimed_by_member_id = $2,
		    claimed_by_ref = $2,
		    claimed_at = $3,
		    current_agent_ref = NULL,
		    suggested_assignee_ref = '',
		    reviewer_ref = '',
		    last_activity_at = $3,
		    updated_at = $3
		WHERE id = $1
		`, taskID, channelAccess.WorkspaceMember.Member.ID, now); err != nil {
		return fmt.Errorf("assign task: %w", err)
	}
	if err := applyTaskTypedEventPlan(ctx, txs.tx, taskTypedEventIntent(taskID, channelAccess.WorkspaceMember.Member.ID, taskEventAssigned, channelAccess.WorkspaceMember.Member.ID, now)); err != nil {
		return err
	}
	taskScopeID, err := loadTaskScopeIDForParticipantNotification(ctx, txs.tx, taskID)
	if err != nil {
		return err
	}
	if err := ensureTaskScopeParticipantsDefaultUnwatched(ctx, txs.tx, taskScopeID, []string{channelAccess.WorkspaceMember.Member.ID}, now); err != nil {
		return err
	}
	return nil
}

func (txs TxStore) ClaimForUser(ctx context.Context, userID string, workspaceID string, taskID string) (Task, error) {
	if err := txs.claimForUserInTx(ctx, userID, workspaceID, taskID); err != nil {
		return Task{}, err
	}
	return txs.taskByID(ctx, txs.tx, workspaceID, taskID)
}

func (txs TxStore) UnclaimForUser(ctx context.Context, userID string, workspaceID string, taskID string) (Task, error) {
	channelID, status, assigneeMemberID, err := txs.lockTaskRow(ctx, txs.tx, workspaceID, taskID)
	if err != nil {
		return Task{}, err
	}
	if status == string(StatusDone) {
		return Task{}, ErrConflict
	}
	if assigneeMemberID == "" {
		return Task{}, ErrConflict
	}
	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, txs.tx, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return Task{}, err
	}
	if assigneeMemberID != channelAccess.WorkspaceMember.Member.ID {
		return Task{}, ErrConflict
	}
	// 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, txs.tx, userID, workspaceID, channelID); err != nil {
		return Task{}, err
	}

	now := time.Now().UTC()
	if _, err := txs.tx.Exec(ctx, `
		UPDATE tasks
		SET assignee_member_id = NULL,
		    assigned_at = NULL,
		    claimed_by_member_id = NULL,
		    claimed_by_ref = NULL,
		    claimed_at = NULL,
		    current_agent_ref = NULL,
		    suggested_assignee_ref = '',
		    reviewer_ref = '',
		    last_activity_at = $2,
		    updated_at = $2
		WHERE id = $1
		`, taskID, now); err != nil {
		return Task{}, fmt.Errorf("unassign task: %w", err)
	}
	if err := applyTaskTypedEventPlan(ctx, txs.tx, taskTypedEventIntent(taskID, channelAccess.WorkspaceMember.Member.ID, taskEventUnassigned, channelAccess.WorkspaceMember.Member.ID, now)); err != nil {
		return Task{}, err
	}

	return txs.taskByID(ctx, txs.tx, workspaceID, taskID)
}

type DeleteForUserRequest struct {
	UserID      string
	WorkspaceID string
	TaskID      string
	SubtaskMode DeleteTaskSubtaskMode
}

func (txs TxStore) DeleteForUser(ctx context.Context, request DeleteForUserRequest) (DeleteTaskResponse, error) {
	userID := strings.TrimSpace(request.UserID)
	workspaceID := strings.TrimSpace(request.WorkspaceID)
	taskID := strings.TrimSpace(request.TaskID)
	// TOCTOU fence: lock the canonical Task->board Channel row and revalidate the
	// full human Channel authority in a fixed order so a concurrent
	// board-membership revoke / archive / effective-close / Channel delete /
	// member deactivate / Task delete / board move serializes deterministically.
	channelID, err := txs.lockHumanTaskBoardChannelAuthority(ctx, txs.tx, userID, workspaceID, taskID)
	if err != nil {
		return DeleteTaskResponse{}, err
	}

	channelAccess, err := txs.loadAuthorizedUserTaskChannelAccess(ctx, txs.tx, userID, workspaceID, channelID, taskWriteChannelAction)
	if err != nil {
		return DeleteTaskResponse{}, err
	}

	return txs.deleteLockedTask(ctx, workspaceID, channelID, taskID, request.SubtaskMode, func(taskIDs []string) (bool, error) {
		return txs.userCanDeleteTasks(ctx, txs.tx, channelAccess, taskIDs)
	})
}

type deleteTaskAuthorizer func(taskIDs []string) (bool, error)

// deleteLockedTask owns the canonical in-transaction deletion state machine.
// Callers must lock the parent task and authorize its channel before entering;
// the supplied authorizer keeps Human admin/creator and Agent creator policies
// separate without duplicating tombstone, promotion, or deletion writes.
func (txs TxStore) deleteLockedTask(
	ctx context.Context,
	workspaceID string,
	channelID string,
	taskID string,
	requestedMode DeleteTaskSubtaskMode,
	authorize deleteTaskAuthorizer,
) (DeleteTaskResponse, error) {
	descendantTasks, err := txs.listTasksByParentTaskForUpdate(ctx, txs.tx, workspaceID, taskID)
	if err != nil {
		return DeleteTaskResponse{}, err
	}
	directChildTasks := make([]Task, 0, len(descendantTasks))
	for _, descendant := range descendantTasks {
		if strings.TrimSpace(descendant.ParentTaskRef) == taskID {
			directChildTasks = append(directChildTasks, descendant)
		}
	}
	allowed, err := authorize([]string{taskID})
	if err != nil {
		return DeleteTaskResponse{}, err
	}
	if !allowed {
		return DeleteTaskResponse{}, ErrForbidden
	}

	subtaskMode, err := normalizeDeleteTaskSubtaskMode(requestedMode, len(descendantTasks))
	if err != nil {
		return DeleteTaskResponse{}, err
	}

	taskNumber, err := txs.taskNumberByID(ctx, txs.tx, workspaceID, taskID)
	if err != nil {
		return DeleteTaskResponse{}, err
	}
	taskScopeID, err := txs.loadTaskScopeID(ctx, txs.tx, taskID)
	if err != nil {
		return DeleteTaskResponse{}, err
	}
	now := time.Now().UTC()
	deletedTaskIDs := make([]string, 0, len(descendantTasks)+1)
	deletedTaskNumbers := make([]int64, 0, len(descendantTasks)+1)
	promotedTasks := []Task(nil)
	if subtaskMode == DeleteTaskSubtaskModeDelete {
		childTaskIDs := taskIDsFromTasks(descendantTasks)
		allowed, err := authorize(childTaskIDs)
		if err != nil {
			return DeleteTaskResponse{}, err
		}
		if !allowed {
			return DeleteTaskResponse{}, ErrForbidden
		}
		for _, childTask := range descendantTasks {
			if err := txs.tombstoneTaskScope(ctx, txs.tx, childTask.TaskScopeID, now); err != nil {
				return DeleteTaskResponse{}, err
			}
			deletedTaskIDs = append(deletedTaskIDs, childTask.ID)
			deletedTaskNumbers = append(deletedTaskNumbers, childTask.TaskNumber)
		}
		if len(descendantTasks) > 0 {
			childTaskIDs := taskIDsFromTasks(descendantTasks)
			placeholders, deleteArgs := appendStringPlaceholders([]any{}, childTaskIDs)
			if _, err := txs.tx.Exec(ctx, `
DELETE FROM tasks
WHERE id IN (`+placeholders+`)
`, deleteArgs...); err != nil {
				return DeleteTaskResponse{}, fmt.Errorf("delete subtasks: %w", err)
			}
		}
	} else if subtaskMode == DeleteTaskSubtaskModePromote && len(directChildTasks) > 0 {
		if _, err := txs.tx.Exec(ctx, `
UPDATE tasks
SET parent_task_ref = '',
    updated_at = $2
WHERE parent_task_ref = $1
`, taskID, now); err != nil {
			return DeleteTaskResponse{}, fmt.Errorf("promote subtasks: %w", err)
		}
		for index := range directChildTasks {
			directChildTasks[index].ParentTaskRef = ""
		}
		promotedTasks = directChildTasks
	}

	if err := txs.tombstoneTaskScope(ctx, txs.tx, taskScopeID, now); err != nil {
		return DeleteTaskResponse{}, err
	}
	commandTag, err := txs.tx.Exec(ctx, `DELETE FROM tasks WHERE id = $1`, taskID)
	if err != nil {
		return DeleteTaskResponse{}, fmt.Errorf("delete task: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return DeleteTaskResponse{}, ErrNotFound
	}
	deletedTaskIDs = append(deletedTaskIDs, taskID)
	deletedTaskNumbers = append(deletedTaskNumbers, taskNumber)
	return DeleteTaskResponse{
		ChannelID:           channelID,
		DeletedTaskIDs:      deletedTaskIDs,
		DeletedTaskNumbers:  deletedTaskNumbers,
		PromotedTasks:       promotedTasks,
		PromotedTaskNumbers: taskNumbersFromTasks(promotedTasks),
	}, nil
}

func normalizeDeleteTaskSubtaskMode(mode DeleteTaskSubtaskMode, childTaskCount int) (DeleteTaskSubtaskMode, error) {
	switch mode {
	case DeleteTaskSubtaskModePromote, DeleteTaskSubtaskModeDelete:
		return mode, nil
	case DeleteTaskSubtaskModeUnspecified:
		if childTaskCount > 0 {
			return "", badRequest("subtaskMode is required when deleting a task with subtasks")
		}
		return DeleteTaskSubtaskModePromote, nil
	default:
		return "", badRequest("subtaskMode must be promote or delete")
	}
}

func taskIDsFromTasks(tasks []Task) []string {
	taskIDs := make([]string, 0, len(tasks))
	for _, task := range tasks {
		taskIDs = append(taskIDs, task.ID)
	}
	return taskIDs
}

func taskNumbersFromTasks(tasks []Task) []int64 {
	taskNumbers := make([]int64, 0, len(tasks))
	for _, task := range tasks {
		taskNumbers = append(taskNumbers, task.TaskNumber)
	}
	return taskNumbers
}

func (txs TxStore) taskNumberByID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, taskID string) (int64, error) {
	row := queryer.QueryRow(ctx, `
SELECT t.task_number
FROM tasks t
JOIN task_boards tb ON tb.id = t.board_id
WHERE tb.workspace_id = $1
  AND t.id = $2
`, workspaceID, taskID)
	var taskNumber int64
	if err := row.Scan(&taskNumber); err != nil {
		if coredb.IsNotFound(err) {
			return 0, ErrNotFound
		}
		return 0, fmt.Errorf("load task number: %w", err)
	}
	return taskNumber, nil
}

func (txs TxStore) ensureTaskChannel(ctx context.Context, queryer coredb.QueryRower, workspaceID string, channelID string) error {
	channel, err := access.LookupChannel(ctx, queryer, workspaceID, channelID)
	if err == nil {
		switch channel.Type {
		case "channel":
			return nil
		case "thread":
			return badRequest("threads do not support tasks")
		default:
			return fmt.Errorf("invalid channel type %q", channel.Type)
		}
	}
	if !errors.Is(err, access.ErrNotFound) {
		return err
	}

	dmRow := queryer.QueryRow(ctx, `
SELECT 1
FROM scopes
WHERE workspace_id = $1 AND id = $2 AND type = 'dm' AND status = 'active'
`, workspaceID, channelID)
	var marker int
	if err := dmRow.Scan(&marker); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load dm thread: %w", err)
	}
	return badRequest("dm does not support tasks")
}

func (txs TxStore) loadAuthorizedUserTaskChannelAccess(ctx context.Context, queryer coredb.QueryRower, userID string, workspaceID string, channelID string, action access.HumanAction) (access.HumanChannelAccess, error) {
	if err := txs.ensureTaskChannel(ctx, queryer, workspaceID, channelID); err != nil {
		return access.HumanChannelAccess{}, err
	}

	channelAccess, err := access.LoadAuthorizedHumanChannelAccess(ctx, queryer, userID, workspaceID, channelID, action)
	if errors.Is(err, access.ErrForbidden) {
		return access.HumanChannelAccess{}, ErrForbidden
	}
	if errors.Is(err, access.ErrNotFound) {
		return access.HumanChannelAccess{}, ErrNotFound
	}
	if err != nil {
		return access.HumanChannelAccess{}, fmt.Errorf("load authorized task channel access: %w", err)
	}
	return channelAccess, nil
}

func (txs TxStore) listTasksByChannel(ctx context.Context, workspaceID string, channelID string, includeSubtasks bool) ([]Task, error) {
	query, args := buildListTasksByChannelQuery(workspaceID, channelID, includeSubtasks)
	rows, err := txs.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list tasks: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0)
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate tasks: %w", err)
	}
	return txs.normalizeTasksWithTaxonomy(ctx, txs.db, workspaceID, tasks)
}

func (txs TxStore) listTasksByMemberChannels(ctx context.Context, workspaceID string, memberID string, includeSubtasks bool) ([]Task, error) {
	query, args := buildListWorkspaceTasksQuery(workspaceID, memberID, includeSubtasks)
	rows, err := txs.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list workspace tasks: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0)
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace tasks: %w", err)
	}
	return txs.normalizeTasksWithTaxonomy(ctx, txs.db, workspaceID, tasks)
}

func (txs TxStore) listTasksByIDs(ctx context.Context, workspaceID string, taskIDs []string) ([]Task, error) {
	return txs.listTasksByIDsForQueryer(ctx, txs.db, workspaceID, taskIDs)
}

func (txs TxStore) listTasksByIDsForQueryer(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, taskIDs []string) ([]Task, error) {
	if len(taskIDs) == 0 {
		return []Task{}, nil
	}
	query, args, err := buildListTasksByIDsQuery(workspaceID, taskIDs)
	if err != nil {
		return nil, fmt.Errorf("build task id query: %w", err)
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load tasks: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0)
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate tasks: %w", err)
	}
	return txs.normalizeTasksWithTaxonomy(ctx, queryer, workspaceID, tasks)
}

func (txs TxStore) listTasksByParentTask(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, parentTaskID string) ([]Task, error) {
	return txs.listTasksByParentTaskLimit(ctx, queryer, workspaceID, parentTaskID, 0)
}

func (txs TxStore) listTasksByParentTaskForUpdate(ctx context.Context, tx coredb.Tx, workspaceID string, parentTaskID string) ([]Task, error) {
	// createSubtasksInTx takes the parent lock before inserting. Walk one
	// canonical direct-child edge at a time with SELECT ... FOR UPDATE. Unlike a
	// recursive CTE snapshot, each current read observes a child that commits
	// while the delete waits on the parent lock. Post-order output lets delete
	// mode tombstone/delete grandchildren before their ancestors; promote mode
	// still selects only the direct children at the root.
	visited := map[string]struct{}{strings.TrimSpace(parentTaskID): {}}
	var descendants []Task
	var walk func(string, int) error
	walk = func(currentParentID string, depth int) error {
		if depth >= 64 {
			return badRequest("Task hierarchy exceeds the supported depth")
		}
		rows, err := tx.Query(ctx, taskSelectBase+`
JOIN tasks task_parent_lock
  ON task_parent_lock.id = $2
 AND task_parent_lock.board_id = t.board_id
WHERE tb.workspace_id = $1
  AND t.parent_task_ref = $2
ORDER BY t.task_number ASC, t.created_at ASC, t.id ASC
FOR UPDATE
`, workspaceID, currentParentID)
		if err != nil {
			return fmt.Errorf("lock subtasks: %w", err)
		}
		children := make([]Task, 0)
		for rows.Next() {
			task, scanErr := scanTask(rows)
			if scanErr != nil {
				rows.Close()
				return scanErr
			}
			children = append(children, task)
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return fmt.Errorf("iterate locked subtasks: %w", err)
		}
		rows.Close()
		for _, child := range children {
			childID := strings.TrimSpace(child.ID)
			if childID == "" {
				return badRequest("Task hierarchy contains a malformed child")
			}
			if _, exists := visited[childID]; exists {
				return badRequest("Task hierarchy contains a cycle")
			}
			visited[childID] = struct{}{}
			if err := walk(childID, depth+1); err != nil {
				return err
			}
			descendants = append(descendants, child)
		}
		return nil
	}
	if err := walk(strings.TrimSpace(parentTaskID), 0); err != nil {
		return nil, err
	}
	return txs.normalizeTasksWithTaxonomy(ctx, tx, workspaceID, descendants)
}

func (txs TxStore) listTasksByParentTaskLimit(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, parentTaskID string, limit int) ([]Task, error) {
	limitClause := ""
	args := []any{workspaceID, parentTaskID}
	if limit > 0 {
		limitClause = "LIMIT $3"
		args = append(args, limit+1)
	}
	rows, err := queryer.Query(ctx, taskSelectBase+`
WHERE tb.workspace_id = $1
  AND t.parent_task_ref = $2
ORDER BY t.task_number ASC, t.created_at ASC, t.id ASC
`+limitClause, args...)
	if err != nil {
		return nil, fmt.Errorf("load subtasks: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0)
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate subtasks: %w", err)
	}
	if limit > 0 && len(tasks) > limit {
		tasks = tasks[:limit]
	}
	return txs.normalizeTasksWithTaxonomy(ctx, queryer, workspaceID, tasks)
}

// TaskSubtasksDefaultPageSize preserves the current 10-row initial detail
// behavior for the direct-children section.
const TaskSubtasksDefaultPageSize = 10

// TaskSubtasksMaxPageSize caps a single direct-children page.
const TaskSubtasksMaxPageSize = 100

// listTasksByParentTaskPage returns one bounded keyset page of direct children
// in newest-first order (created_at DESC, id DESC). The unique id tie-breaker
// is part of both ORDER BY and the strict continuation predicate, so equal
// timestamps, concurrent inserts ahead of the cursor, and repeated reads can
// neither duplicate nor omit existing rows. Fetching limit+1 lets the caller
// derive hasMore and a nextCursor from the last returned row without an extra
// count query.
func (txs TxStore) listTasksByParentTaskPage(
	ctx context.Context,
	queryer coredb.QueryExecer,
	workspaceID string,
	parentTaskID string,
	limit int,
	cursor SubtaskPageCursor,
) (SubtasksPage, error) {
	if limit <= 0 {
		limit = TaskSubtasksDefaultPageSize
	}
	if limit > TaskSubtasksMaxPageSize {
		limit = TaskSubtasksMaxPageSize
	}
	args := []any{workspaceID, parentTaskID}
	predicate := ""
	if !cursor.IsZero() {
		// Strict keyset continuation: rows strictly newer than the cursor in
		// (created_at DESC, id DESC) ordering. id is the unique tie-breaker, so a
		// concurrent insert sharing the cursor timestamp is still ordered
		// deterministically and cannot be skipped or repeated.
		predicate = `
  AND (t.created_at < $3 OR (t.created_at = $3 AND t.id < $4))`
		args = append(args, cursor.CreatedAt.UTC(), cursor.ID)
	}
	rows, err := queryer.Query(ctx, taskSelectBase+`
WHERE tb.workspace_id = $1
  AND t.parent_task_ref = $2`+predicate+`
ORDER BY t.created_at DESC, t.id DESC
LIMIT $`+strconv.Itoa(len(args)+1), append(args, limit+1)...)
	if err != nil {
		return SubtasksPage{}, fmt.Errorf("load subtask page: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0)
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return SubtasksPage{}, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return SubtasksPage{}, fmt.Errorf("iterate subtask page: %w", err)
	}
	hasMore := len(tasks) > limit
	if hasMore {
		tasks = tasks[:limit]
	}
	normalized, err := txs.normalizeTasksWithTaxonomy(ctx, queryer, workspaceID, tasks)
	if err != nil {
		return SubtasksPage{}, err
	}
	page := SubtasksPage{Tasks: normalized, HasMore: hasMore}
	if hasMore && len(normalized) > 0 {
		last := normalized[len(normalized)-1]
		page.NextCursor = SubtaskPageCursor{CreatedAt: last.CreatedAt.UTC(), ID: last.ID}
	}
	return page, nil
}

func (txs TxStore) listTasksByFieldOptionImpact(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, fieldKind string, optionID string, lookupKeys []string) ([]Task, error) {
	query, args, err := buildListTasksByFieldOptionImpactQuery(workspaceID, fieldKind, optionID, lookupKeys)
	if err != nil {
		return nil, err
	}
	if query == "" {
		return []Task{}, nil
	}

	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load tasks by field option impact: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0)
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate tasks by field option impact: %w", err)
	}
	return txs.normalizeTasksWithTaxonomy(ctx, queryer, workspaceID, tasks)
}

func buildListTasksByFieldOptionImpactQuery(workspaceID string, fieldKind string, optionID string, lookupKeys []string) (string, []any, error) {
	validatedFieldKind, err := validateTaskFieldKind(fieldKind)
	if err != nil {
		return "", nil, err
	}

	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedOptionID := strings.TrimSpace(optionID)
	normalizedLookups := make([]string, 0, len(lookupKeys))
	seenLookups := make(map[string]struct{}, len(lookupKeys))
	for _, lookupKey := range lookupKeys {
		normalized := normalizeTaskFieldLookupKey(lookupKey)
		if normalized == "" {
			continue
		}
		if _, ok := seenLookups[normalized]; ok {
			continue
		}
		seenLookups[normalized] = struct{}{}
		normalizedLookups = append(normalizedLookups, normalized)
	}
	if trimmedWorkspaceID == "" || (trimmedOptionID == "" && len(normalizedLookups) == 0) {
		return "", nil, nil
	}

	args := []any{trimmedWorkspaceID}
	clauses := make([]string, 0, 2)

	var optionColumn string
	var legacyColumn string
	switch validatedFieldKind {
	case taskFieldKindKind:
		optionColumn = "COALESCE(t.kind_option_id, '')"
		legacyColumn = "LOWER(TRIM(COALESCE(NULLIF(t.legacy_kind_value, ''), t.kind, '')))"
	case taskFieldKindPriority:
		optionColumn = "COALESCE(t.priority_option_id, '')"
		legacyColumn = "LOWER(TRIM(COALESCE(NULLIF(t.legacy_priority_value, ''), t.priority, '')))"
	default:
		return "", nil, badRequest("fieldKind is invalid")
	}

	if trimmedOptionID != "" {
		args = append(args, trimmedOptionID)
		clauses = append(clauses, fmt.Sprintf("%s = $%d", optionColumn, len(args)))
	}
	if len(normalizedLookups) > 0 {
		placeholders := make([]string, 0, len(normalizedLookups))
		for _, lookupKey := range normalizedLookups {
			args = append(args, lookupKey)
			placeholders = append(placeholders, fmt.Sprintf("$%d", len(args)))
		}
		clauses = append(clauses, fmt.Sprintf("%s IN (%s)", legacyColumn, strings.Join(placeholders, ", ")))
	}
	if len(clauses) == 0 {
		return "", nil, nil
	}

	query := taskSelectBase + `
WHERE tb.workspace_id = $1
  AND (` + strings.Join(clauses, `
   OR `) + `)
ORDER BY tb.channel_id ASC, t.task_number ASC, t.created_at ASC, t.id ASC
`
	return query, args, nil
}

func (txs TxStore) ListByTaskNumbers(ctx context.Context, workspaceID string, channelID string, taskNumbers []int64) ([]Task, error) {
	if len(taskNumbers) == 0 {
		return []Task{}, nil
	}
	query, args, err := buildListTasksByNumbersQuery(workspaceID, channelID, taskNumbers)
	if err != nil {
		return nil, fmt.Errorf("build task number query: %w", err)
	}
	rows, err := txs.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load tasks by number: %w", err)
	}
	defer rows.Close()

	tasks := make([]Task, 0, len(taskNumbers))
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate tasks by number: %w", err)
	}
	return txs.normalizeTasksWithTaxonomy(ctx, txs.db, workspaceID, tasks)
}

func (txs TxStore) taskByID(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, taskID string) (Task, error) {
	query, args := buildTaskByIDQuery(workspaceID, taskID)
	row := queryer.QueryRow(ctx, query, args...)
	task, err := scanTask(row)
	if err != nil {
		return Task{}, err
	}
	tasks, err := txs.normalizeTasksWithTaxonomy(ctx, queryer, workspaceID, []Task{task})
	if err != nil {
		return Task{}, err
	}
	return tasks[0], nil
}

func (txs TxStore) taskByNumber(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, channelID string, taskNumber int64) (Task, error) {
	query, args := buildTaskByNumberQuery(workspaceID, channelID, taskNumber)
	row := queryer.QueryRow(ctx, query, args...)
	task, err := scanTask(row)
	if err != nil {
		return Task{}, err
	}
	tasks, err := txs.normalizeTasksWithTaxonomy(ctx, queryer, workspaceID, []Task{task})
	if err != nil {
		return Task{}, err
	}
	return tasks[0], nil
}

func (txs TxStore) lockTaskRow(ctx context.Context, queryer coredb.QueryRower, workspaceID string, taskID string) (string, string, string, error) {
	row := queryer.QueryRow(ctx, `
SELECT tb.channel_id, ct.status, COALESCE(`+taskcompat.EffectiveAssigneeRefSQL("ct")+`, '')
FROM tasks ct
JOIN task_boards tb
  ON tb.id = ct.board_id
 AND tb.workspace_id = $1
JOIN scopes task_scope
  ON task_scope.id = ct.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 ct.channel_id = tb.channel_id
  AND ct.id = $2
FOR UPDATE
`, workspaceID, taskID)
	var channelID string
	var status string
	var assigneeMemberID string
	if err := row.Scan(&channelID, &status, &assigneeMemberID); err != nil {
		if coredb.IsNotFound(err) {
			return "", "", "", ErrNotFound
		}
		return "", "", "", fmt.Errorf("lock task: %w", err)
	}
	return channelID, status, assigneeMemberID, nil
}

func (txs TxStore) loadTaskMutationState(ctx context.Context, queryer coredb.QueryRower, workspaceID string, taskID string) (string, string, string, error) {
	row := queryer.QueryRow(ctx, `
SELECT tb.channel_id, ct.status, COALESCE(`+taskcompat.EffectiveAssigneeRefSQL("ct")+`, '')
FROM tasks ct
JOIN task_boards tb
  ON tb.id = ct.board_id
 AND tb.workspace_id = $1
JOIN scopes task_scope
  ON task_scope.id = ct.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 ct.channel_id = tb.channel_id
  AND ct.id = $2
`, workspaceID, taskID)
	var channelID string
	var status string
	var assigneeMemberID string
	if err := row.Scan(&channelID, &status, &assigneeMemberID); err != nil {
		if coredb.IsNotFound(err) {
			return "", "", "", ErrNotFound
		}
		return "", "", "", fmt.Errorf("load task mutation state: %w", err)
	}
	return channelID, status, assigneeMemberID, nil
}

func (txs TxStore) userCanDeleteTasks(ctx context.Context, tx coredb.Tx, channelAccess access.HumanChannelAccess, taskIDs []string) (bool, error) {
	if len(taskIDs) == 0 || access.HasWorkspaceAdminRole(domain.WorkspaceRole(channelAccess.WorkspaceMember.WorkspaceRole)) {
		return true, nil
	}
	return txs.tasksCreatedByMember(ctx, tx, channelAccess.WorkspaceMember.Member.ID, taskIDs)
}

func (txs TxStore) tasksCreatedByMember(ctx context.Context, tx coredb.Tx, memberID string, taskIDs []string) (bool, error) {
	if len(taskIDs) == 0 {
		return true, nil
	}
	placeholders := make([]string, 0, len(taskIDs))
	args := make([]any, 0, len(taskIDs)+1)
	seen := make(map[string]struct{}, len(taskIDs))
	for _, taskID := range taskIDs {
		trimmed := strings.TrimSpace(taskID)
		if trimmed == "" {
			return false, nil
		}
		if _, exists := seen[trimmed]; exists {
			continue
		}
		seen[trimmed] = struct{}{}
		args = append(args, trimmed)
		placeholders = append(placeholders, fmt.Sprintf("$%d", len(args)))
	}
	if len(placeholders) == 0 {
		return true, nil
	}
	// Use a locking read so a delete that waited behind create-subtask observes
	// the just-committed child even under TiDB repeatable-read semantics.
	rows, err := tx.Query(ctx, fmt.Sprintf(`
SELECT id, COALESCE(created_by_ref, '')
FROM tasks
WHERE id IN (%s)
FOR UPDATE
`, strings.Join(placeholders, ", ")), args...)
	if err != nil {
		return false, fmt.Errorf("check task delete permissions: %w", err)
	}
	defer rows.Close()

	creatorMemberID := strings.TrimSpace(memberID)
	found := make(map[string]struct{}, len(seen))
	for rows.Next() {
		var taskID string
		var createdByRef string
		if err := rows.Scan(&taskID, &createdByRef); err != nil {
			return false, fmt.Errorf("scan task delete permissions: %w", err)
		}
		found[strings.TrimSpace(taskID)] = struct{}{}
		if strings.TrimSpace(createdByRef) != creatorMemberID {
			return false, nil
		}
	}
	if err := rows.Err(); err != nil {
		return false, fmt.Errorf("iterate task delete permissions: %w", err)
	}
	return len(found) == len(seen), nil
}