package taskboard

import (
	"context"
	"errors"
	"fmt"
	"strings"

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

const (
	defaultRelatedTaskLimit = 20
	maxRelatedTaskLimit     = 50
)

func (txs TxStore) ListRelatedTasksForThread(ctx context.Context, request ListRelatedTasksForThreadRequest) (RelatedTasksResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	page, err := normalizeRelatedTaskPage(RelatedTaskPage{Limit: request.Limit, Cursor: request.Cursor, Status: request.Status})
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	threadChannelID := strings.TrimSpace(request.ThreadChannelID)
	if threadChannelID == "" {
		return RelatedTasksResponse{}, badRequest("threadChannelId is required")
	}
	_, err = access.LoadAuthorizedHumanThreadAccess(ctx, txs.tx, userID, workspaceID, threadChannelID, taskReadThreadAction)
	if errors.Is(err, access.ErrForbidden) {
		return RelatedTasksResponse{}, ErrForbidden
	}
	if errors.Is(err, access.ErrNotFound) {
		return RelatedTasksResponse{}, ErrNotFound
	}
	if err != nil {
		return RelatedTasksResponse{}, fmt.Errorf("load authorized related task thread: %w", err)
	}

	taskIDs, err := txs.loadRelatedThreadTaskIDs(ctx, txs.tx, workspaceID, threadChannelID, page)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	hasMore := len(taskIDs) > page.Limit
	if hasMore {
		taskIDs = taskIDs[:page.Limit]
	}
	tasks, err := txs.listTasksByIDsForQueryer(ctx, txs.tx, workspaceID, taskIDs)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	tasks = orderRelatedTasksByIDs(tasks, taskIDs)
	rows, err := txs.relatedTaskRowsFromTasks(ctx, txs.tx, workspaceID, tasks)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	return RelatedTasksResponse{Rows: rows, HasMore: hasMore, NextCursor: relatedTaskNextCursor(hasMore, tasks)}, nil
}

func (txs TxStore) RelatedTasksSummaryForThread(ctx context.Context, request RelatedTasksSummaryForThreadRequest) (RelatedTaskUnreadSummary, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	threadChannelID := strings.TrimSpace(request.ThreadChannelID)
	if threadChannelID == "" {
		return RelatedTaskUnreadSummary{}, badRequest("threadChannelId is required")
	}
	_, err = access.LoadAuthorizedHumanThreadAccess(ctx, txs.tx, userID, workspaceID, threadChannelID, taskReadThreadAction)
	if errors.Is(err, access.ErrForbidden) {
		return RelatedTaskUnreadSummary{}, ErrForbidden
	}
	if errors.Is(err, access.ErrNotFound) {
		return RelatedTaskUnreadSummary{}, ErrNotFound
	}
	if err != nil {
		return RelatedTaskUnreadSummary{}, fmt.Errorf("load authorized related task thread: %w", err)
	}
	return txs.loadRelatedThreadTaskSummary(ctx, txs.tx, workspaceID, threadChannelID, RelatedTaskPage{})
}

func (txs TxStore) ListRelatedExecutionTasksForAgent(ctx context.Context, request ListRelatedExecutionTasksForAgentRequest) (RelatedTasksResponse, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	page, err := normalizeRelatedTaskPage(RelatedTaskPage{Limit: request.Limit, Cursor: request.Cursor, Status: request.Status})
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	agentID := strings.TrimSpace(request.AgentID)
	if agentID == "" {
		return RelatedTasksResponse{}, badRequest("agentId is required")
	}
	_, err = access.LoadHumanWorkspaceMember(ctx, txs.tx, userID, workspaceID)
	if errors.Is(err, access.ErrForbidden) {
		return RelatedTasksResponse{}, ErrForbidden
	}
	if err != nil {
		return RelatedTasksResponse{}, fmt.Errorf("load related task actor: %w", err)
	}
	agentMemberID, err := loadOwnedRelatedAgentMemberID(ctx, txs.tx, workspaceID, userID, agentID)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	taskIDs, err := txs.loadRelatedAgentExecutionTaskIDs(ctx, txs.tx, workspaceID, agentMemberID, page)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	hasMore := len(taskIDs) > page.Limit
	if hasMore {
		taskIDs = taskIDs[:page.Limit]
	}
	tasks, err := txs.listTasksByIDsForQueryer(ctx, txs.tx, workspaceID, taskIDs)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	tasks = orderRelatedTasksByIDs(tasks, taskIDs)
	rows, err := txs.relatedTaskRowsFromTasks(ctx, txs.tx, workspaceID, tasks)
	if err != nil {
		return RelatedTasksResponse{}, err
	}
	return RelatedTasksResponse{Rows: rows, HasMore: hasMore, NextCursor: relatedTaskNextCursor(hasMore, tasks)}, nil
}

func (txs TxStore) RelatedExecutionTasksSummaryForAgent(ctx context.Context, request RelatedExecutionTasksSummaryForAgentRequest) (RelatedTaskUnreadSummary, error) {
	workspaceID, err := request.Context.requireWorkspaceID()
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	userID, _, err := request.Context.requireUser()
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	page, err := normalizeRelatedTaskPage(RelatedTaskPage{Limit: request.Limit, Cursor: request.Cursor, Status: request.Status})
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	agentID := strings.TrimSpace(request.AgentID)
	if agentID == "" {
		return RelatedTaskUnreadSummary{}, badRequest("agentId is required")
	}
	_, err = access.LoadHumanWorkspaceMember(ctx, txs.tx, userID, workspaceID)
	if errors.Is(err, access.ErrForbidden) {
		return RelatedTaskUnreadSummary{}, ErrForbidden
	}
	if err != nil {
		return RelatedTaskUnreadSummary{}, fmt.Errorf("load related task actor: %w", err)
	}
	agentMemberID, err := loadOwnedRelatedAgentMemberID(ctx, txs.tx, workspaceID, userID, agentID)
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	return txs.loadRelatedAgentExecutionTaskSummary(ctx, txs.tx, workspaceID, agentMemberID, page)
}

func normalizeRelatedTaskPage(page RelatedTaskPage) (RelatedTaskPage, error) {
	status, err := normalizeRelatedTaskStatus(page.Status)
	if err != nil {
		return RelatedTaskPage{}, err
	}
	limit := page.Limit
	if limit <= 0 {
		limit = defaultRelatedTaskLimit
	}
	if limit > maxRelatedTaskLimit {
		limit = maxRelatedTaskLimit
	}
	cursor := RelatedTaskCursor{}
	if !page.Cursor.IsZero() {
		cursor = RelatedTaskCursor{TaskNumber: page.Cursor.TaskNumber, TaskID: strings.TrimSpace(page.Cursor.TaskID)}
		if cursor.TaskNumber <= 0 || cursor.TaskID == "" {
			return RelatedTaskPage{}, badRequest("invalid related task cursor")
		}
	}
	return RelatedTaskPage{Limit: limit, Cursor: cursor, Status: status}, nil
}

func normalizeRelatedTaskStatus(status RelatedTaskStatus) (RelatedTaskStatus, error) {
	switch RelatedTaskStatus(strings.TrimSpace(string(status))) {
	case "", RelatedTaskStatusActive:
		return RelatedTaskStatusActive, nil
	case RelatedTaskStatusDone:
		return RelatedTaskStatusDone, nil
	default:
		return "", badRequest("related task status is invalid")
	}
}

func relatedTaskStatusCondition(status RelatedTaskStatus, alias string) string {
	switch status {
	case RelatedTaskStatusDone:
		return alias + ".status = 'done'"
	default:
		return alias + ".status <> 'done'"
	}
}

func relatedTaskNextCursor(hasMore bool, tasks []Task) RelatedTaskCursor {
	if !hasMore || len(tasks) == 0 {
		return RelatedTaskCursor{}
	}
	last := tasks[len(tasks)-1]
	return RelatedTaskCursor{TaskNumber: last.TaskNumber, TaskID: strings.TrimSpace(last.ID)}
}

func orderRelatedTasksByIDs(tasks []Task, taskIDs []string) []Task {
	if len(tasks) == 0 || len(taskIDs) == 0 {
		return []Task{}
	}

	tasksByID := make(map[string]Task, len(tasks))
	for _, task := range tasks {
		taskID := strings.TrimSpace(task.ID)
		if taskID == "" {
			continue
		}
		if _, exists := tasksByID[taskID]; !exists {
			tasksByID[taskID] = task
		}
	}

	ordered := make([]Task, 0, len(tasks))
	seen := make(map[string]struct{}, len(taskIDs))
	for _, taskID := range taskIDs {
		taskID = strings.TrimSpace(taskID)
		if taskID == "" {
			continue
		}
		if _, duplicate := seen[taskID]; duplicate {
			continue
		}
		seen[taskID] = struct{}{}
		task, ok := tasksByID[taskID]
		if !ok {
			continue
		}
		ordered = append(ordered, task)
	}
	return ordered
}

func relatedTaskCursorClause(page RelatedTaskPage, alias string, args []any) (string, []any) {
	if page.Cursor.IsZero() {
		return "", args
	}
	taskNumberArg := len(args) + 1
	taskIDArg := taskNumberArg + 1
	args = append(args, page.Cursor.TaskNumber, strings.TrimSpace(page.Cursor.TaskID))
	return relatedTaskCursorClauseForAlias(alias, taskNumberArg, taskIDArg), args
}

func relatedTaskCursorClauseForAlias(alias string, taskNumberArg int, taskIDArg int) string {
	return fmt.Sprintf(`
  AND (%[1]s.task_number > $%[2]d OR (%[1]s.task_number = $%[2]d AND %[1]s.id > $%[3]d))`, alias, taskNumberArg, taskIDArg)
}

func relatedTaskCursorPlaceholders(page RelatedTaskPage, args []any) (int, int, []any) {
	if page.Cursor.IsZero() {
		return 0, 0, args
	}
	taskNumberArg := len(args) + 1
	taskIDArg := taskNumberArg + 1
	args = append(args, page.Cursor.TaskNumber, strings.TrimSpace(page.Cursor.TaskID))
	return taskNumberArg, taskIDArg, args
}

// relatedTaskRecursiveTreeSQL is the cycle-safe, canonical Task hierarchy
// relation used by related list and summary surfaces. Matching nodes are
// filtered by their own status; the recursive context walk adds every active
// ancestor so an arbitrary-depth correction remains grouped under its full
// root -> parent chain even when ancestor statuses differ. All recursive edges
// stay on one workspace/board/channel and stop at a bounded depth, so moved,
// malformed, or cyclic descendants fail closed instead of being silently
// projected through an unrelated board.
const relatedTaskRecursiveTreeSQL = `WITH RECURSIVE related_tree (
    id,
    task_number,
    parent_task_ref,
    board_id,
    channel_id,
    status,
    depth,
    path
) AS (
    SELECT
        root.id,
        root.task_number,
        COALESCE(root.parent_task_ref, ''),
        root.board_id,
        root_board.channel_id,
        root.status,
        0,
        CAST(root.id AS CHAR(8192))
    FROM tasks root
    JOIN task_boards root_board
      ON root_board.id = root.board_id
     AND root_board.workspace_id = $1
    JOIN scopes root_channel
      ON root_channel.id = root_board.channel_id
     AND root_channel.workspace_id = root_board.workspace_id
     AND root_channel.type = 'channel'
     AND root_channel.status = 'active'
     AND root_channel.archived_at IS NULL
     AND root.channel_id = root_board.channel_id
    JOIN scopes root_scope
      ON root_scope.id = root.task_scope_id
     AND root_scope.workspace_id = root_board.workspace_id
     AND root_scope.type = 'task'
     AND root_scope.status = 'active'
     AND root_scope.archived_at IS NULL
    JOIN scopes source_thread
      ON source_thread.id = root.source_thread_channel_id
     AND source_thread.workspace_id = root_board.workspace_id
     AND source_thread.type = 'thread'
     AND source_thread.parent_scope_id = root_channel.id
     AND source_thread.status = 'active'
     AND source_thread.archived_at IS NULL
    WHERE root.source_thread_channel_id = $2
      AND (
        COALESCE(root.source_thread_parent_channel_id, '') = ''
        OR root.source_thread_parent_channel_id = root_channel.id
      )
    UNION ALL
    SELECT
        child.id,
        child.task_number,
        COALESCE(child.parent_task_ref, ''),
        child.board_id,
        child_board.channel_id,
        child.status,
        related_tree.depth + 1,
        CAST(CONCAT(related_tree.path, ',', child.id) AS CHAR(8192))
    FROM related_tree
    JOIN tasks child
      ON child.parent_task_ref = related_tree.id
    JOIN task_boards child_board
      ON child_board.id = child.board_id
     AND child_board.workspace_id = $1
     AND child_board.id = related_tree.board_id
    JOIN scopes child_channel
      ON child_channel.id = child_board.channel_id
     AND child_channel.workspace_id = child_board.workspace_id
     AND child_channel.type = 'channel'
     AND child_channel.status = 'active'
     AND child_channel.archived_at IS NULL
     AND child.channel_id = child_board.channel_id
     AND child_channel.id = related_tree.channel_id
    JOIN scopes child_scope
      ON child_scope.id = child.task_scope_id
     AND child_scope.workspace_id = child_board.workspace_id
     AND child_scope.type = 'task'
     AND child_scope.status = 'active'
     AND child_scope.archived_at IS NULL
    WHERE related_tree.depth < 64
      AND INSTR(CONCAT(',', related_tree.path, ','), CONCAT(',', child.id, ',')) = 0
),
matching_tasks AS (
    SELECT node.id, node.task_number, node.parent_task_ref
    FROM related_tree node
    WHERE %s
),
related_context (
    id,
    task_number,
    parent_task_ref,
    path,
    depth
) AS (
    SELECT
        matching.id,
        matching.task_number,
        matching.parent_task_ref,
        CAST(matching.id AS CHAR(8192)),
        0
    FROM matching_tasks matching
    UNION ALL
    SELECT
        parent.id,
        parent.task_number,
        parent.parent_task_ref,
        CAST(CONCAT(related_context.path, ',', parent.id) AS CHAR(8192)),
        related_context.depth + 1
    FROM related_context
    JOIN related_tree parent
      ON parent.id = related_context.parent_task_ref
    WHERE related_context.depth < 64
      AND INSTR(CONCAT(',', related_context.path, ','), CONCAT(',', parent.id, ',')) = 0
),
related_tasks AS (
    SELECT DISTINCT id, task_number
    FROM related_context
)
SELECT id
FROM related_tasks related
WHERE %s
ORDER BY task_number ASC, id ASC
LIMIT $%d
`

func buildRelatedThreadTaskIDsQuery(workspaceID string, threadChannelID string, page RelatedTaskPage) (string, []any, error) {
	page, err := normalizeRelatedTaskPage(page)
	if err != nil {
		return "", nil, err
	}
	args := []any{strings.TrimSpace(workspaceID), strings.TrimSpace(threadChannelID)}
	cursorTaskNumberArg, cursorTaskIDArg, args := relatedTaskCursorPlaceholders(page, args)
	cursorClause := "1 = 1"
	if cursorTaskNumberArg > 0 {
		cursorClause = strings.TrimSpace(relatedTaskCursorClauseForAlias("related", cursorTaskNumberArg, cursorTaskIDArg))
		cursorClause = strings.TrimPrefix(cursorClause, "AND ")
	}
	limitArg := len(args) + 1
	args = append(args, page.Limit+1)
	query := fmt.Sprintf(
		relatedTaskRecursiveTreeSQL,
		relatedTaskStatusCondition(page.Status, "node"),
		cursorClause,
		limitArg,
	)
	return query, args, nil
}

// buildRelatedThreadTaskStatusCountsQuery returns the full status distribution
// over every cycle-safe descendant in the canonical Task tree, not only direct
// children. A moved/reparented child is excluded because the recursive edge
// requires the same canonical board and Channel as its root.
func buildRelatedThreadTaskStatusCountsQuery(workspaceID string, threadChannelID string) (string, []any, error) {
	args := []any{strings.TrimSpace(workspaceID), strings.TrimSpace(threadChannelID)}
	query := relatedTaskRecursiveTreeSQL
	query = strings.Replace(query, "WHERE %s\n),\nrelated_context", "WHERE 1 = 1\n),\nrelated_context", 1)
	// The ID-page query's matching/context CTEs are not needed for a full
	// summary. Keep the same recursive root/descendant relation and count each
	// canonical tree node exactly once.
	query = query[:strings.Index(query, ",\nmatching_tasks AS (")] + `,
related_tasks AS (
    SELECT DISTINCT id
    FROM related_tree
)
SELECT t.status, COUNT(*)
FROM related_tasks related
JOIN tasks t ON t.id = related.id
GROUP BY t.status
`
	return query, args, nil
}

func (txs TxStore) loadRelatedThreadTaskSummary(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, threadChannelID string, page RelatedTaskPage) (RelatedTaskUnreadSummary, error) {
	query, args, err := buildRelatedThreadTaskStatusCountsQuery(workspaceID, threadChannelID)
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	defer rows.Close()
	statusCounts := make(map[string]int64)
	var taskCount int64
	for rows.Next() {
		var status string
		var count int64
		if err := rows.Scan(&status, &count); err != nil {
			return RelatedTaskUnreadSummary{}, err
		}
		statusCounts[status] = count
		taskCount += count
	}
	if err := rows.Err(); err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	return RelatedTaskUnreadSummary{TaskCount: taskCount, StatusCounts: statusCounts}, nil
}

func (txs TxStore) loadRelatedThreadTaskIDs(ctx context.Context, queryer coredb.Queryer, workspaceID string, threadChannelID string, page RelatedTaskPage) ([]string, error) {
	query, args, err := buildRelatedThreadTaskIDsQuery(workspaceID, threadChannelID, page)
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list related thread task ids: %w", err)
	}
	defer rows.Close()
	return scanRelatedTaskIDs(rows)
}

func buildRelatedAgentExecutionTaskIDsQuery(workspaceID string, agentMemberID string, page RelatedTaskPage) (string, []any, error) {
	page, err := normalizeRelatedTaskPage(page)
	if err != nil {
		return "", nil, err
	}
	args := []any{strings.TrimSpace(workspaceID), strings.TrimSpace(agentMemberID)}
	cursorTaskNumberArg, cursorTaskIDArg, args := relatedTaskCursorPlaceholders(page, args)
	cursorClause := ""
	if cursorTaskNumberArg > 0 {
		cursorClause = relatedTaskCursorClauseForAlias("t", cursorTaskNumberArg, cursorTaskIDArg)
	}
	limitArg := len(args) + 1
	args = append(args, page.Limit+1)
	query := fmt.Sprintf(`
SELECT t.id
FROM tasks t
JOIN task_boards tb
  ON tb.id = t.board_id
JOIN scopes task_channel
  ON task_channel.id = tb.channel_id
 AND task_channel.workspace_id = tb.workspace_id
 AND task_channel.type = 'channel'
 AND task_channel.status = 'active'
 AND task_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
WHERE tb.workspace_id = $1
  AND %s
  AND `+taskcompat.EffectiveAssigneeRefSQL("t")+` = $2%s
ORDER BY t.task_number ASC, t.id ASC
LIMIT $%d
`, relatedTaskStatusCondition(page.Status, "t"), cursorClause, limitArg)
	return query, args, nil
}

// buildRelatedAgentExecutionTaskStatusCountsQuery returns the FULL-COUNT status
// distribution (todo / in_progress / in_review / done) of all tasks assigned
// to the agent, independent of pagination.
func buildRelatedAgentExecutionTaskStatusCountsQuery(workspaceID string, agentMemberID string) (string, []any, error) {
	args := []any{strings.TrimSpace(workspaceID), strings.TrimSpace(agentMemberID)}
	query := `
SELECT t.status, COUNT(*)
FROM tasks t
JOIN task_boards tb
  ON tb.id = t.board_id
JOIN scopes task_channel
  ON task_channel.id = tb.channel_id
 AND task_channel.workspace_id = tb.workspace_id
 AND task_channel.type = 'channel'
 AND task_channel.status = 'active'
 AND task_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
WHERE tb.workspace_id = $1
  AND ` + taskcompat.EffectiveAssigneeRefSQL("t") + ` = $2
GROUP BY t.status
`
	return query, args, nil
}

func (txs TxStore) loadRelatedAgentExecutionTaskIDs(ctx context.Context, queryer coredb.Queryer, workspaceID string, agentMemberID string, page RelatedTaskPage) ([]string, error) {
	query, args, err := buildRelatedAgentExecutionTaskIDsQuery(workspaceID, agentMemberID, page)
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list related agent execution task ids: %w", err)
	}
	defer rows.Close()
	return scanRelatedTaskIDs(rows)
}

func scanRelatedTaskIDs(rows coredb.Rows) ([]string, error) {
	taskIDs := make([]string, 0)
	for rows.Next() {
		var taskID string
		if err := rows.Scan(&taskID); err != nil {
			return nil, fmt.Errorf("scan related task id: %w", err)
		}
		taskID = strings.TrimSpace(taskID)
		if taskID != "" {
			taskIDs = append(taskIDs, taskID)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate related task ids: %w", err)
	}
	return taskIDs, nil
}

func loadOwnedRelatedAgentMemberID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, userID string, agentID string) (string, error) {
	var agentMemberID string
	if err := queryer.QueryRow(ctx, `
SELECT a.member_id
FROM agents a
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
WHERE a.workspace_id = $1
  AND a.id = $2
  AND a.status <> 'archived'
  AND mac.status = 'active'
  AND mac.created_by_user_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(agentID), strings.TrimSpace(userID)).Scan(&agentMemberID); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("load owned related agent: %w", err)
	}
	return strings.TrimSpace(agentMemberID), nil
}

func (txs TxStore) relatedTaskRowsFromTasks(ctx context.Context, queryer coredb.Queryer, workspaceID string, tasks []Task) ([]RelatedTaskRow, error) {
	taskScopeIDs := relatedTaskScopeIDs(tasks)
	lastSignals, err := loadRelatedTaskLastSignals(ctx, queryer, workspaceID, taskScopeIDs)
	if err != nil {
		return nil, err
	}
	rows := make([]RelatedTaskRow, 0, len(tasks))
	for _, task := range tasks {
		taskScopeID := strings.TrimSpace(task.TaskScopeID)
		rows = append(rows, relatedTaskRowFromTask(task, lastSignals[taskScopeID], RelatedTaskUnread{}))
	}
	return rows, nil
}

func relatedTaskScopeIDs(tasks []Task) []string {
	seen := make(map[string]struct{}, len(tasks))
	taskScopeIDs := make([]string, 0, len(tasks))
	for _, task := range tasks {
		taskScopeID := strings.TrimSpace(task.TaskScopeID)
		if taskScopeID == "" {
			taskScopeID = taskReadScopeID(task)
		}
		if taskScopeID == "" {
			continue
		}
		if _, ok := seen[taskScopeID]; ok {
			continue
		}
		seen[taskScopeID] = struct{}{}
		taskScopeIDs = append(taskScopeIDs, taskScopeID)
	}
	return taskScopeIDs
}

func relatedTaskRowFromTask(task Task, lastSignal *RelatedTaskSignal, unread RelatedTaskUnread) RelatedTaskRow {
	row := RelatedTaskRow{
		TaskID:          strings.TrimSpace(task.ID),
		TaskScopeID:     strings.TrimSpace(task.TaskScopeID),
		ChannelID:       strings.TrimSpace(task.ChannelID),
		TaskNumber:      task.TaskNumber,
		Title:           strings.TrimSpace(task.Title),
		Status:          strings.TrimSpace(task.Status),
		ParentTaskID:    strings.TrimSpace(task.ParentTaskRef),
		ParentTaskRef:   strings.TrimSpace(task.ParentTaskRef),
		Source:          task.Source,
		SourceThread:    task.SourceThread,
		AssigneeProfile: task.AssigneeProfile,
		LastSignal:      lastSignal,
		Unread:          unread,
	}
	if row.ChannelID != "" && row.TaskNumber > 0 {
		row.Route = fmt.Sprintf("/channels/%s/tasks/%d", row.ChannelID, row.TaskNumber)
	}
	return row
}

func buildRelatedTaskLastSignalsQuery(workspaceID string, taskScopeIDs []string) (string, []any, error) {
	cte, cteArgs, _, err := coredb.BuildStringValuesCTE("requested_scopes", "scope_id", taskScopeIDs, 2)
	if err != nil {
		return "", nil, err
	}
	query := fmt.Sprintf(`
WITH %s,
ranked_signals AS (
    SELECT
        ae.scope_id,
        CASE
            WHEN JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(ae.payload) THEN ae.payload ELSE '{}' END, '$.taskEvent.type')) IS NOT NULL
                THEN JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(ae.payload) THEN ae.payload ELSE '{}' END, '$.taskEvent.type'))
            WHEN ae.activity_type LIKE 'task.%%' THEN SUBSTRING(ae.activity_type, 6)
            ELSE ae.activity_type
        END AS event_type,
        CASE
            WHEN JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(ae.payload) THEN ae.payload ELSE '{}' END, '$.taskEvent.payload')) IS NULL THEN ''
            ELSE JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(ae.payload) THEN ae.payload ELSE '{}' END, '$.taskEvent.payload'))
        END AS event_payload,
        ae.created_at,
        ROW_NUMBER() OVER (PARTITION BY ae.scope_id ORDER BY ae.created_at DESC, ae.id DESC) AS rn
    FROM requested_scopes requested
    JOIN scope_activity_events ae
      ON ae.scope_id = requested.scope_id
     AND ae.workspace_id = $1
     AND ae.activity_ref_type = 'task_event'
     AND ae.suppressed_at IS NULL
     AND %s
)
SELECT scope_id, event_type, event_payload, created_at
FROM ranked_signals
WHERE rn = 1
`, cte, highSignalActivityPredicateSQL("ae"))
	return query, append([]any{strings.TrimSpace(workspaceID)}, cteArgs...), nil
}

func loadRelatedTaskLastSignals(ctx context.Context, queryer coredb.Queryer, workspaceID string, taskScopeIDs []string) (map[string]*RelatedTaskSignal, error) {
	signals := make(map[string]*RelatedTaskSignal, len(taskScopeIDs))
	if len(taskScopeIDs) == 0 {
		return signals, nil
	}
	query, args, err := buildRelatedTaskLastSignalsQuery(workspaceID, taskScopeIDs)
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load related task last signals: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var taskScopeID string
		var signal RelatedTaskSignal
		if err := rows.Scan(&taskScopeID, &signal.Type, &signal.Payload, &signal.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan related task last signal: %w", err)
		}
		signal.Type = strings.TrimSpace(signal.Type)
		signal.Payload = strings.TrimSpace(signal.Payload)
		signal.CreatedAt = signal.CreatedAt.UTC()
		signals[strings.TrimSpace(taskScopeID)] = &signal
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate related task last signals: %w", err)
	}
	return signals, nil
}

func (txs TxStore) loadRelatedAgentExecutionTaskSummary(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, agentMemberID string, page RelatedTaskPage) (RelatedTaskUnreadSummary, error) {
	query, args, err := buildRelatedAgentExecutionTaskStatusCountsQuery(workspaceID, agentMemberID)
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	defer rows.Close()
	statusCounts := make(map[string]int64)
	var taskCount int64
	for rows.Next() {
		var status string
		var count int64
		if err := rows.Scan(&status, &count); err != nil {
			return RelatedTaskUnreadSummary{}, err
		}
		statusCounts[status] = count
		taskCount += count
	}
	if err := rows.Err(); err != nil {
		return RelatedTaskUnreadSummary{}, err
	}
	return RelatedTaskUnreadSummary{TaskCount: taskCount, StatusCounts: statusCounts}, nil
}