package message

import (
	"context"
	"fmt"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

// AttachResolvedMentionsForAppMessages annotates raw @tokens in app messages
// with viewer-safe display identities. The stored content stays unchanged; the
// annotation is a read-time presentation aid for clients.
func AttachResolvedMentionsForAppMessages(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberID string, messages []AppMessage) error {
	if len(messages) == 0 {
		return nil
	}

	scopeIDs := make([]string, 0)
	tokens := make([]string, 0)
	seenPairs := make(map[string]struct{})
	entityScopeIDs := make([]string, 0)
	entityAgentIDs := make([]string, 0)
	entityTeamScopeIDs := make([]string, 0)
	entityTeamIDs := make([]string, 0)
	seenEntities := make(map[string]struct{})
	for _, appMessage := range messages {
		scopeID := strings.TrimSpace(appMessage.ChannelID)
		if scopeID == "" || strings.TrimSpace(appMessage.Content) == "" {
			continue
		}
		_, tokenSet := normalizedDirectMentionTokens(appMessage.Content)
		for normalizedToken := range tokenSet {
			key := resolvedMentionPairKey(scopeID, normalizedToken)
			if _, ok := seenPairs[key]; ok {
				continue
			}
			seenPairs[key] = struct{}{}
			scopeIDs = append(scopeIDs, scopeID)
			tokens = append(tokens, normalizedToken)
		}
		for _, mention := range persistedMentionsForTokens(appMessage.Metadata, "agent", tokenSet) {
			key := resolvedMentionTypedEntityPairKey("agent", scopeID, mention.ID)
			if _, ok := seenEntities[key]; ok {
				continue
			}
			seenEntities[key] = struct{}{}
			entityScopeIDs = append(entityScopeIDs, scopeID)
			entityAgentIDs = append(entityAgentIDs, mention.ID)
		}
		for _, mention := range persistedMentionsForTokens(appMessage.Metadata, "team", tokenSet) {
			key := resolvedMentionTypedEntityPairKey("team", scopeID, mention.ID)
			if _, ok := seenEntities[key]; ok {
				continue
			}
			seenEntities[key] = struct{}{}
			entityTeamScopeIDs = append(entityTeamScopeIDs, scopeID)
			entityTeamIDs = append(entityTeamIDs, mention.ID)
		}
	}
	if len(tokens) == 0 && len(entityAgentIDs) == 0 && len(entityTeamIDs) == 0 {
		return nil
	}

	mentions := make(map[string]ResolvedMention)
	if len(tokens) > 0 {
		loaded, err := loadResolvedAgentMentionsForViewers(ctx, queryer, workspaceID, []string{viewerMemberID}, scopeIDs, tokens)
		if err != nil {
			return err
		}
		mentions = loaded
		loaded, err = loadResolvedTeamMentionsForViewers(ctx, queryer, workspaceID, []string{viewerMemberID}, scopeIDs, tokens)
		if err != nil {
			return err
		}
		mentions = mergeMissingResolvedMentions(mentions, loaded)
		loaded, err = loadResolvedMemberMentionsForViewers(ctx, queryer, workspaceID, []string{viewerMemberID}, scopeIDs, tokens)
		if err != nil {
			return err
		}
		mentions = mergeMissingResolvedMentions(mentions, loaded)
	}
	entityMentions := make(map[string]ResolvedMention)
	if len(entityAgentIDs) > 0 {
		loaded, err := loadResolvedAgentMentionsByIDForViewers(ctx, queryer, workspaceID, []string{viewerMemberID}, entityScopeIDs, entityAgentIDs)
		if err != nil {
			return err
		}
		entityMentions = loaded
	}
	if len(entityTeamIDs) > 0 {
		loaded, err := loadResolvedTeamMentionsByIDForViewers(ctx, queryer, workspaceID, []string{viewerMemberID}, entityTeamScopeIDs, entityTeamIDs)
		if err != nil {
			return err
		}
		entityMentions = mergeMissingResolvedMentions(entityMentions, loaded)
	}

	for i := range messages {
		scopeID := strings.TrimSpace(messages[i].ChannelID)
		if scopeID == "" {
			continue
		}
		resolved := make([]ResolvedMention, 0)
		directTokens, tokenSet := normalizedDirectMentionTokens(messages[i].Content)
		persistedByToken := persistedMentionsByDirectToken(append(
			persistedMentionsForTokens(messages[i].Metadata, "agent", tokenSet),
			persistedMentionsForTokens(messages[i].Metadata, "team", tokenSet)...,
		), directTokens)
		for _, normalizedToken := range directTokens {
			if persistedMentions := persistedByToken[normalizedToken]; len(persistedMentions) > 0 {
				for _, persisted := range persistedMentions {
					if mention, ok := entityMentions[resolvedMentionViewerTypedEntityPairKey(viewerMemberID, persisted.Type, scopeID, persisted.ID)]; ok {
						mention.Token = persisted.Token
						resolved = append(resolved, mention)
					}
				}
				continue
			}
			mention, ok := mentions[resolvedMentionViewerPairKey(viewerMemberID, scopeID, normalizedToken)]
			if !ok {
				continue
			}
			resolved = append(resolved, mention)
		}
		if len(resolved) > 0 {
			messages[i].ResolvedMentions = resolved
		}
	}
	return nil
}

// AttachSharedResolvedMentionsForAppMessages annotates raw @tokens for shared
// realtime/fanout payloads. Shared payloads are delivered to every subscriber in
// a scope, so they must not contain viewer-specific private-agent identities.
//
// Passing an empty viewer member ID through the existing resolution path keeps
// only public scope memberships: private memberships require owner_member_id to
// match the viewer. History/read paths should continue to call
// AttachResolvedMentionsForAppMessages with the real viewer member ID.
func AttachSharedResolvedMentionsForAppMessages(ctx context.Context, queryer coredb.Queryer, workspaceID string, messages []AppMessage) error {
	for i := range messages {
		messages[i].ResolvedMentions = nil
	}
	return AttachResolvedMentionsForAppMessages(ctx, queryer, workspaceID, "", messages)
}

// ResolveAppMessageMentionsForViewers returns viewer-specific mention
// annotations for one message. It is used by realtime delivery where one room
// event may need distinct private-agent visibility per connected member.
func ResolveAppMessageMentionsForViewers(ctx context.Context, queryer coredb.Queryer, workspaceID string, appMessage AppMessage, viewerMemberIDs []string) (map[string][]ResolvedMention, error) {
	scopeID := strings.TrimSpace(appMessage.ChannelID)
	if scopeID == "" || strings.TrimSpace(appMessage.Content) == "" || len(viewerMemberIDs) == 0 {
		return nil, nil
	}

	viewers := make([]string, 0, len(viewerMemberIDs))
	seenViewers := make(map[string]struct{}, len(viewerMemberIDs))
	for _, viewerMemberID := range viewerMemberIDs {
		trimmed := strings.TrimSpace(viewerMemberID)
		if trimmed == "" {
			continue
		}
		if _, ok := seenViewers[trimmed]; ok {
			continue
		}
		seenViewers[trimmed] = struct{}{}
		viewers = append(viewers, trimmed)
	}
	if len(viewers) == 0 {
		return nil, nil
	}

	tokens, tokenSet := normalizedDirectMentionTokens(appMessage.Content)
	if len(tokens) == 0 {
		return nil, nil
	}

	scopeIDs := make([]string, len(tokens))
	for i := range tokens {
		scopeIDs[i] = scopeID
	}
	mentions := make(map[string]ResolvedMention)
	loaded, err := loadResolvedAgentMentionsForViewers(ctx, queryer, workspaceID, viewers, scopeIDs, tokens)
	if err != nil {
		return nil, err
	}
	mentions = loaded
	loaded, err = loadResolvedMemberMentionsForViewers(ctx, queryer, workspaceID, viewers, scopeIDs, tokens)
	if err != nil {
		return nil, err
	}
	teamMentions, err := loadResolvedTeamMentionsForViewers(ctx, queryer, workspaceID, viewers, scopeIDs, tokens)
	if err != nil {
		return nil, err
	}
	mentions = mergeMissingResolvedMentions(mentions, teamMentions)
	mentions = mergeMissingResolvedMentions(mentions, loaded)

	persistedMentions := append(
		persistedMentionsForTokens(appMessage.Metadata, "agent", tokenSet),
		persistedMentionsForTokens(appMessage.Metadata, "team", tokenSet)...,
	)
	entityMentions := make(map[string]ResolvedMention)
	if len(persistedMentions) > 0 {
		entityScopeIDs := make([]string, 0, len(persistedMentions))
		entityAgentIDs := make([]string, 0, len(persistedMentions))
		entityTeamScopeIDs := make([]string, 0, len(persistedMentions))
		entityTeamIDs := make([]string, 0, len(persistedMentions))
		seenEntities := make(map[string]struct{}, len(persistedMentions))
		for _, mention := range persistedMentions {
			key := resolvedMentionTypedEntityPairKey(mention.Type, scopeID, mention.ID)
			if _, ok := seenEntities[key]; ok {
				continue
			}
			seenEntities[key] = struct{}{}
			switch mention.Type {
			case "agent":
				entityScopeIDs = append(entityScopeIDs, scopeID)
				entityAgentIDs = append(entityAgentIDs, mention.ID)
			case "team":
				entityTeamScopeIDs = append(entityTeamScopeIDs, scopeID)
				entityTeamIDs = append(entityTeamIDs, mention.ID)
			}
		}
		if len(entityAgentIDs) > 0 {
			loaded, err := loadResolvedAgentMentionsByIDForViewers(ctx, queryer, workspaceID, viewers, entityScopeIDs, entityAgentIDs)
			if err != nil {
				return nil, err
			}
			entityMentions = loaded
		}
		if len(entityTeamIDs) > 0 {
			loaded, err := loadResolvedTeamMentionsByIDForViewers(ctx, queryer, workspaceID, viewers, entityTeamScopeIDs, entityTeamIDs)
			if err != nil {
				return nil, err
			}
			entityMentions = mergeMissingResolvedMentions(entityMentions, loaded)
		}
	}

	resolvedByViewer := make(map[string][]ResolvedMention, len(viewers))
	persistedByToken := persistedMentionsByDirectToken(persistedMentions, tokens)
	for _, viewerMemberID := range viewers {
		resolved := make([]ResolvedMention, 0)
		for _, normalizedToken := range tokens {
			if persistedMentions := persistedByToken[normalizedToken]; len(persistedMentions) > 0 {
				for _, persisted := range persistedMentions {
					if mention, ok := entityMentions[resolvedMentionViewerTypedEntityPairKey(viewerMemberID, persisted.Type, scopeID, persisted.ID)]; ok {
						mention.Token = persisted.Token
						resolved = append(resolved, mention)
					}
				}
				continue
			}
			mention, ok := mentions[resolvedMentionViewerPairKey(viewerMemberID, scopeID, normalizedToken)]
			if !ok {
				continue
			}
			resolved = append(resolved, mention)
		}
		if len(resolved) > 0 {
			resolvedByViewer[viewerMemberID] = resolved
		}
	}
	return resolvedByViewer, nil
}

func normalizedDirectMentionTokens(content string) ([]string, map[string]struct{}) {
	tokens := make([]string, 0)
	seenTokens := make(map[string]struct{})
	for _, token := range ParseDirectMentionTokens(content) {
		normalizedToken := strings.ToLower(strings.TrimSpace(token))
		if normalizedToken == "" {
			continue
		}
		if _, ok := seenTokens[normalizedToken]; ok {
			continue
		}
		seenTokens[normalizedToken] = struct{}{}
		tokens = append(tokens, normalizedToken)
	}
	return tokens, seenTokens
}

func persistedAgentMentionsForTokens(metadata map[string]any, tokenSet map[string]struct{}) []PersistedMention {
	return persistedMentionsForTokens(metadata, "agent", tokenSet)
}

func persistedMentionsForTokens(metadata map[string]any, mentionType string, tokenSet map[string]struct{}) []PersistedMention {
	if len(tokenSet) == 0 {
		return nil
	}
	mentions := ExtractPersistedMentionMetadata(metadata)
	if len(mentions) == 0 {
		return nil
	}
	tokens := mapKeys(tokenSet)
	filtered := make([]PersistedMention, 0, len(mentions))
	normalizedType := strings.ToLower(strings.TrimSpace(mentionType))
	for _, mention := range mentions {
		if mention.Type != normalizedType {
			continue
		}
		if !mentionTokenMatchesAny(mention.Token, tokens) {
			continue
		}
		filtered = append(filtered, mention)
	}
	return filtered
}

func persistedAgentMentionByDirectToken(metadata map[string]any, directTokens []string, tokenSet map[string]struct{}) map[string]PersistedMention {
	return persistedMentionByDirectToken(persistedAgentMentionsForTokens(metadata, tokenSet), directTokens)
}

func persistedMentionByDirectToken(mentions []PersistedMention, directTokens []string) map[string]PersistedMention {
	persistedByToken := persistedMentionsByDirectToken(mentions, directTokens)
	if len(persistedByToken) == 0 {
		return nil
	}
	byToken := make(map[string]PersistedMention, len(persistedByToken))
	for token, mentions := range persistedByToken {
		if len(mentions) > 0 {
			byToken[token] = mentions[0]
		}
	}
	return byToken
}

func persistedMentionsByDirectToken(mentions []PersistedMention, directTokens []string) map[string][]PersistedMention {
	if len(mentions) == 0 {
		return nil
	}
	byToken := make(map[string][]PersistedMention, len(mentions))
	for _, mention := range mentions {
		for _, directToken := range directTokens {
			normalizedToken := strings.ToLower(strings.TrimSpace(directToken))
			if normalizedToken == "" {
				continue
			}
			if !MentionTokenMatchesTarget(normalizedToken, mention.Token) {
				continue
			}
			mentionKey := mention.Type + "\x00" + mention.ID
			replaced := false
			for index, previous := range byToken[normalizedToken] {
				previousKey := previous.Type + "\x00" + previous.ID
				if previousKey != mentionKey {
					continue
				}
				if len(previous.Token) >= len(mention.Token) {
					replaced = true
					break
				}
				byToken[normalizedToken][index] = mention
				replaced = true
				break
			}
			if replaced {
				continue
			}
			byToken[normalizedToken] = append(byToken[normalizedToken], mention)
		}
	}
	return byToken
}

func mergeMissingResolvedMentions(base map[string]ResolvedMention, extra map[string]ResolvedMention) map[string]ResolvedMention {
	if len(extra) == 0 {
		return base
	}
	if base == nil {
		base = make(map[string]ResolvedMention, len(extra))
	}
	for key, mention := range extra {
		if _, ok := base[key]; ok {
			continue
		}
		base[key] = mention
	}
	return base
}

func mapKeys(values map[string]struct{}) []string {
	keys := make([]string, 0, len(values))
	for key := range values {
		keys = append(keys, key)
	}
	return keys
}

func loadResolvedAgentMentionsForViewers(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberIDs []string, scopeIDs []string, tokens []string) (map[string]ResolvedMention, error) {
	if len(viewerMemberIDs) == 0 {
		return nil, nil
	}
	requestedMentionsCTE, requestedArgs, _, err := coredb.BuildStringPairsCTE("requested_mentions", "scope_id", "token", scopeIDs, tokens, 2)
	if err != nil {
		return nil, err
	}
	requestedViewersCTE, requestedViewerArgs, _, err := coredb.BuildStringValuesCTE("requested_viewers", "viewer_member_id", viewerMemberIDs, 2+len(requestedArgs))
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
%s
  SELECT
    requested_viewers.viewer_member_id,
    requested_mentions.scope_id,
    requested_mentions.token,
    a.id,
    a.name,
    a.member_id,
    COALESCE(NULLIF(agent_member.display_name, ''), '') AS display_name,
    sm.visibility
  FROM requested_mentions
  CROSS JOIN requested_viewers
  JOIN scopes mention_scope
    ON mention_scope.id = requested_mentions.scope_id
   AND mention_scope.workspace_id = $1
   AND mention_scope.status = 'active'
   AND mention_scope.archived_at IS NULL
  JOIN scope_memberships sm
    ON sm.scope_id = CASE
        WHEN mention_scope.type = 'thread' AND COALESCE(mention_scope.parent_scope_id, '') <> ''
          THEN mention_scope.parent_scope_id
        ELSE requested_mentions.scope_id
      END
   AND sm.removed_at IS NULL
   AND sm.archived_at IS NULL
   AND sm.effective_to_seq IS NULL
  JOIN agents a
    ON a.workspace_id = $1
   AND a.member_id = sm.member_id
   AND a.status <> 'archived'
  JOIN machines mac
    ON mac.workspace_id = a.workspace_id
   AND mac.id = a.machine_id
   AND mac.status = 'active'
  JOIN members agent_member
    ON agent_member.id = a.member_id
   AND agent_member.workspace_id = a.workspace_id
   AND agent_member.type = 'agent'
  WHERE (
      LOWER(a.name) = requested_mentions.token
      OR LOWER(a.member_id) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(agent_member.display_name, ''), '')) = requested_mentions.token
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(a.name, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(a.member_id, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(agent_member.display_name, '')), '%%')
    )
    AND (
      sm.visibility = 'public'
      OR (sm.visibility = 'private' AND sm.owner_member_id = requested_viewers.viewer_member_id)
    )
ORDER BY requested_viewers.viewer_member_id ASC,
         requested_mentions.scope_id ASC,
         requested_mentions.token ASC,
         CASE WHEN sm.visibility = 'private' THEN 0 ELSE 1 END,
         a.name ASC,
         a.member_id ASC,
         a.id ASC
`, requestedMentionsCTE, requestedViewersCTE), append(append([]any{strings.TrimSpace(workspaceID)}, requestedArgs...), requestedViewerArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load resolved agent mentions: %w", err)
	}
	defer rows.Close()

	type resolvedMentionCandidate struct {
		mention     ResolvedMention
		matchLength int
		privateRank int
	}
	candidates := make(map[string]resolvedMentionCandidate)
	for rows.Next() {
		var viewerMemberID string
		var scopeID string
		var token string
		var agentID string
		var agentName string
		var agentMemberID string
		var displayName string
		var visibility string
		if err := rows.Scan(&viewerMemberID, &scopeID, &token, &agentID, &agentName, &agentMemberID, &displayName, &visibility); err != nil {
			return nil, fmt.Errorf("scan resolved agent mention: %w", err)
		}
		normalizedToken := strings.ToLower(strings.TrimSpace(token))
		mentionToken, ok := matchedMentionTargetToken(normalizedToken, []string{
			agentName,
			agentMemberID,
			displayName,
		})
		if !ok {
			continue
		}
		resolvedToken := strings.TrimSpace(agentName)
		label := strings.TrimSpace(displayName)
		if label == "" {
			label = resolvedToken
		}
		if label == "" {
			label = mentionToken
		}
		key := resolvedMentionViewerPairKey(viewerMemberID, scopeID, normalizedToken)
		privateRank := 1
		if strings.TrimSpace(visibility) == "private" {
			privateRank = 0
		}
		candidate := resolvedMentionCandidate{
			mention: ResolvedMention{
				Token:         mentionToken,
				Type:          "agent",
				ID:            strings.TrimSpace(agentID),
				DisplayName:   label,
				ResolvedToken: resolvedToken,
			},
			matchLength: len(mentionToken),
			privateRank: privateRank,
		}
		if previous, ok := candidates[key]; ok {
			if candidate.matchLength < previous.matchLength {
				continue
			}
			if candidate.matchLength == previous.matchLength && candidate.privateRank >= previous.privateRank {
				continue
			}
		}
		candidates[key] = candidate
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate resolved agent mentions: %w", err)
	}
	resolved := make(map[string]ResolvedMention, len(candidates))
	for key, candidate := range candidates {
		resolved[key] = candidate.mention
	}
	return resolved, nil
}

func loadResolvedMemberMentionsForViewers(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberIDs []string, scopeIDs []string, tokens []string) (map[string]ResolvedMention, error) {
	if len(viewerMemberIDs) == 0 {
		return nil, nil
	}
	requestedMentionsCTE, requestedArgs, _, err := coredb.BuildStringPairsCTE("requested_mentions", "scope_id", "token", scopeIDs, tokens, 2)
	if err != nil {
		return nil, err
	}
	requestedViewersCTE, requestedViewerArgs, _, err := coredb.BuildStringValuesCTE("requested_viewers", "viewer_member_id", viewerMemberIDs, 2+len(requestedArgs))
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
%s,
mention_scope_members AS (
  SELECT
    requested_viewers.viewer_member_id,
    requested_mentions.scope_id,
    requested_mentions.token,
    sm.visibility,
    mentioned_member.id AS member_id,
    mentioned_member.type AS member_type,
    COALESCE(mentioned_member.display_name, '') AS member_display_name,
    COALESCE(human_user.id, '') AS user_id,
    COALESCE(human_user.name, '') AS user_name,
    COALESCE(human_user.display_name, '') AS user_display_name,
    COALESCE(app_installation.handle, '') AS service_handle,
    COALESCE(app_definition.slug, '') AS service_slug,
    COALESCE(app_definition.name, '') AS service_name
  FROM requested_mentions
  CROSS JOIN requested_viewers
  JOIN scopes mention_scope
    ON mention_scope.id = requested_mentions.scope_id
   AND mention_scope.workspace_id = $1
   AND mention_scope.status = 'active'
   AND mention_scope.archived_at IS NULL
  JOIN scope_memberships sm
    ON sm.scope_id = CASE
        WHEN mention_scope.type = 'thread' AND COALESCE(mention_scope.parent_scope_id, '') <> ''
          THEN mention_scope.parent_scope_id
        ELSE requested_mentions.scope_id
      END
   AND sm.removed_at IS NULL
   AND sm.archived_at IS NULL
   AND sm.effective_to_seq IS NULL
  JOIN members mentioned_member
    ON mentioned_member.id = sm.member_id
   AND mentioned_member.workspace_id = $1
   AND mentioned_member.status = 'active'
   AND mentioned_member.type IN ('human', 'service')
  LEFT JOIN users human_user
    ON mentioned_member.type = 'human'
   AND human_user.id = mentioned_member.user_id
  LEFT JOIN service_members app_installation
    ON mentioned_member.type = 'service'
   AND app_installation.workspace_id = mentioned_member.workspace_id
   AND app_installation.member_id = mentioned_member.id
  LEFT JOIN service_installations app_installation_inst
    ON app_installation_inst.id = app_installation.service_installation_id
  LEFT JOIN service_definitions app_definition
    ON mentioned_member.type = 'service'
   AND app_definition.id = app_installation_inst.service_definition_id
  WHERE (
      sm.visibility = 'public'
      OR (sm.visibility = 'private' AND sm.owner_member_id = requested_viewers.viewer_member_id)
    )
    AND (
      LOWER(mentioned_member.id) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(mentioned_member.display_name, ''), '')) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(human_user.name, ''), '')) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(human_user.display_name, ''), '')) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(app_installation.handle, ''), '')) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(app_definition.slug, ''), '')) = requested_mentions.token
      OR LOWER(COALESCE(NULLIF(app_definition.name, ''), '')) = requested_mentions.token
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(mentioned_member.id, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(mentioned_member.display_name, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(human_user.name, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(human_user.display_name, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(app_installation.handle, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(app_definition.slug, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(app_definition.name, '')), '%%')
    )
)
SELECT
    viewer_member_id,
    scope_id,
    token,
    visibility,
    member_id,
    member_type,
    member_display_name,
    user_id,
    user_name,
    user_display_name,
    service_handle,
    service_slug,
    service_name
FROM mention_scope_members
ORDER BY viewer_member_id ASC, scope_id ASC, token ASC, member_type ASC, member_id ASC
`, requestedMentionsCTE, requestedViewersCTE), append(append([]any{strings.TrimSpace(workspaceID)}, requestedArgs...), requestedViewerArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load resolved member mentions: %w", err)
	}
	defer rows.Close()

	type resolvedMentionCandidate struct {
		mention     ResolvedMention
		matchLength int
		privateRank int
	}
	candidates := make(map[string]resolvedMentionCandidate)
	for rows.Next() {
		var viewerMemberID string
		var scopeID string
		var token string
		var visibility string
		var memberID string
		var memberType string
		var memberDisplayName string
		var userID string
		var userName string
		var userDisplayName string
		var serviceHandle string
		var serviceSlug string
		var serviceName string
		if err := rows.Scan(&viewerMemberID, &scopeID, &token, &visibility, &memberID, &memberType, &memberDisplayName, &userID, &userName, &userDisplayName, &serviceHandle, &serviceSlug, &serviceName); err != nil {
			return nil, fmt.Errorf("scan resolved member mention: %w", err)
		}

		normalizedToken := strings.ToLower(strings.TrimSpace(token))
		var mention ResolvedMention
		var matchedToken string
		var ok bool
		switch strings.TrimSpace(memberType) {
		case "human":
			matchedToken, ok = matchedMentionTargetToken(normalizedToken, []string{
				memberID,
				userName,
				userDisplayName,
				memberDisplayName,
			})
			if !ok {
				continue
			}
			resolvedToken := strings.TrimSpace(userName)
			if resolvedToken == "" {
				resolvedToken = strings.TrimSpace(memberID)
			}
			label := strings.TrimSpace(userDisplayName)
			if label == "" {
				label = strings.TrimSpace(memberDisplayName)
			}
			if label == "" {
				label = resolvedToken
			}
			if label == "" {
				label = matchedToken
			}
			mention = ResolvedMention{
				Token:         matchedToken,
				Type:          "human",
				ID:            strings.TrimSpace(userID),
				DisplayName:   label,
				ResolvedToken: resolvedToken,
			}
		case "service":
			matchedToken, ok = matchedMentionTargetToken(normalizedToken, []string{
				memberID,
				serviceHandle,
				serviceSlug,
				serviceName,
				memberDisplayName,
			})
			if !ok {
				continue
			}
			resolvedToken := strings.TrimSpace(serviceHandle)
			if resolvedToken == "" {
				resolvedToken = strings.TrimSpace(serviceSlug)
			}
			if resolvedToken == "" {
				resolvedToken = strings.TrimSpace(memberID)
			}
			label := strings.TrimSpace(memberDisplayName)
			if label == "" {
				label = strings.TrimSpace(serviceName)
			}
			if label == "" {
				label = resolvedToken
			}
			if label == "" {
				label = matchedToken
			}
			mention = ResolvedMention{
				Token:         matchedToken,
				Type:          "service",
				ID:            strings.TrimSpace(memberID),
				DisplayName:   label,
				ResolvedToken: resolvedToken,
			}
		default:
			continue
		}
		if mention.ID == "" {
			continue
		}
		privateRank := 1
		if strings.TrimSpace(visibility) == "private" {
			privateRank = 0
		}
		key := resolvedMentionViewerPairKey(viewerMemberID, scopeID, normalizedToken)
		candidate := resolvedMentionCandidate{
			mention:     mention,
			matchLength: len(matchedToken),
			privateRank: privateRank,
		}
		if previous, ok := candidates[key]; ok {
			if candidate.matchLength < previous.matchLength {
				continue
			}
			if candidate.matchLength == previous.matchLength && candidate.privateRank >= previous.privateRank {
				continue
			}
		}
		candidates[key] = candidate
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate resolved member mentions: %w", err)
	}
	resolved := make(map[string]ResolvedMention, len(candidates))
	for key, candidate := range candidates {
		resolved[key] = candidate.mention
	}
	return resolved, nil
}

func loadResolvedTeamMentionsForViewers(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberIDs []string, scopeIDs []string, tokens []string) (map[string]ResolvedMention, error) {
	if len(viewerMemberIDs) == 0 {
		return nil, nil
	}
	requestedMentionsCTE, requestedArgs, _, err := coredb.BuildStringPairsCTE("requested_mentions", "scope_id", "token", scopeIDs, tokens, 2)
	if err != nil {
		return nil, err
	}
	requestedViewersCTE, requestedViewerArgs, _, err := coredb.BuildStringValuesCTE("requested_viewers", "viewer_member_id", viewerMemberIDs, 2+len(requestedArgs))
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
%s
SELECT
    requested_viewers.viewer_member_id,
    requested_mentions.scope_id,
    requested_mentions.token,
    t.id,
    t.handle,
    t.display_name,
    t.visibility
FROM requested_mentions
CROSS JOIN requested_viewers
JOIN workspace_teams t
  ON t.workspace_id = $1
 AND t.status = 'active'
 AND (
      LOWER(t.handle) = requested_mentions.token
      OR LOWER(t.id) = requested_mentions.token
      OR LOWER(t.display_name) = requested_mentions.token
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(t.handle, '')), '%%')
      OR requested_mentions.token LIKE CONCAT(LOWER(NULLIF(t.display_name, '')), '%%')
 )
LEFT JOIN members viewer_member
  ON viewer_member.workspace_id = t.workspace_id
 AND viewer_member.id = requested_viewers.viewer_member_id
 AND viewer_member.type = 'human'
 AND viewer_member.status = 'active'
LEFT JOIN workspace_team_members viewer_team_member
  ON viewer_team_member.workspace_id = t.workspace_id
 AND viewer_team_member.team_id = t.id
 AND viewer_team_member.actor_kind = 'human'
 AND viewer_team_member.actor_id = requested_viewers.viewer_member_id
 AND viewer_team_member.removed_at IS NULL
WHERE t.visibility = 'workspace'
   OR COALESCE(viewer_member.workspace_role, '') IN ('owner', 'admin')
   OR t.created_by_member_id = requested_viewers.viewer_member_id
   OR viewer_team_member.id IS NOT NULL
ORDER BY requested_viewers.viewer_member_id ASC,
         requested_mentions.scope_id ASC,
         requested_mentions.token ASC,
         CASE WHEN t.visibility = 'private' THEN 0 ELSE 1 END,
         t.handle ASC,
         t.display_name ASC,
         t.id ASC
`, requestedMentionsCTE, requestedViewersCTE), append(append([]any{strings.TrimSpace(workspaceID)}, requestedArgs...), requestedViewerArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load resolved team mentions: %w", err)
	}
	defer rows.Close()

	type resolvedMentionCandidate struct {
		mention     ResolvedMention
		matchLength int
		privateRank int
	}
	candidates := make(map[string]resolvedMentionCandidate)
	for rows.Next() {
		var viewerMemberID string
		var scopeID string
		var token string
		var teamID string
		var handle string
		var displayName string
		var visibility string
		if err := rows.Scan(&viewerMemberID, &scopeID, &token, &teamID, &handle, &displayName, &visibility); err != nil {
			return nil, fmt.Errorf("scan resolved team mention: %w", err)
		}
		normalizedToken := strings.ToLower(strings.TrimSpace(token))
		mentionToken, ok := matchedMentionTargetToken(normalizedToken, []string{
			handle,
			displayName,
			teamID,
		})
		if !ok {
			continue
		}
		resolvedToken := strings.TrimSpace(handle)
		label := strings.TrimSpace(displayName)
		if label == "" {
			label = resolvedToken
		}
		if label == "" {
			label = strings.TrimSpace(teamID)
		}
		privateRank := 1
		if strings.TrimSpace(visibility) == "private" {
			privateRank = 0
		}
		key := resolvedMentionViewerPairKey(viewerMemberID, scopeID, normalizedToken)
		candidate := resolvedMentionCandidate{
			mention: ResolvedMention{
				Token:         mentionToken,
				Type:          "team",
				ID:            strings.TrimSpace(teamID),
				DisplayName:   label,
				ResolvedToken: resolvedToken,
			},
			matchLength: len(mentionToken),
			privateRank: privateRank,
		}
		if previous, ok := candidates[key]; ok {
			if candidate.matchLength < previous.matchLength {
				continue
			}
			if candidate.matchLength == previous.matchLength && candidate.privateRank >= previous.privateRank {
				continue
			}
		}
		candidates[key] = candidate
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate resolved team mentions: %w", err)
	}
	resolved := make(map[string]ResolvedMention, len(candidates))
	for key, candidate := range candidates {
		resolved[key] = candidate.mention
	}
	return resolved, nil
}

func loadResolvedTeamMentionsByIDForViewers(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberIDs []string, scopeIDs []string, teamIDs []string) (map[string]ResolvedMention, error) {
	if len(viewerMemberIDs) == 0 || len(teamIDs) == 0 {
		return nil, nil
	}
	requestedMentionsCTE, requestedArgs, _, err := coredb.BuildStringPairsCTE("requested_mentions", "scope_id", "team_id", scopeIDs, teamIDs, 2)
	if err != nil {
		return nil, err
	}
	requestedViewersCTE, requestedViewerArgs, _, err := coredb.BuildStringValuesCTE("requested_viewers", "viewer_member_id", viewerMemberIDs, 2+len(requestedArgs))
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
%s
SELECT
    requested_viewers.viewer_member_id,
    requested_mentions.scope_id,
    t.id,
    t.handle,
    t.display_name
FROM requested_mentions
CROSS JOIN requested_viewers
JOIN workspace_teams t
  ON t.workspace_id = $1
 AND t.id = requested_mentions.team_id
 AND t.status = 'active'
LEFT JOIN members viewer_member
  ON viewer_member.workspace_id = t.workspace_id
 AND viewer_member.id = requested_viewers.viewer_member_id
 AND viewer_member.type = 'human'
 AND viewer_member.status = 'active'
LEFT JOIN workspace_team_members viewer_team_member
  ON viewer_team_member.workspace_id = t.workspace_id
 AND viewer_team_member.team_id = t.id
 AND viewer_team_member.actor_kind = 'human'
 AND viewer_team_member.actor_id = requested_viewers.viewer_member_id
 AND viewer_team_member.removed_at IS NULL
WHERE t.visibility = 'workspace'
   OR COALESCE(viewer_member.workspace_role, '') IN ('owner', 'admin')
   OR t.created_by_member_id = requested_viewers.viewer_member_id
   OR viewer_team_member.id IS NOT NULL
ORDER BY requested_viewers.viewer_member_id ASC,
         requested_mentions.scope_id ASC,
         t.handle ASC,
         t.id ASC
`, requestedMentionsCTE, requestedViewersCTE), append(append([]any{strings.TrimSpace(workspaceID)}, requestedArgs...), requestedViewerArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load resolved team mentions by id: %w", err)
	}
	defer rows.Close()

	resolved := make(map[string]ResolvedMention)
	for rows.Next() {
		var viewerMemberID string
		var scopeID string
		var teamID string
		var handle string
		var displayName string
		if err := rows.Scan(&viewerMemberID, &scopeID, &teamID, &handle, &displayName); err != nil {
			return nil, fmt.Errorf("scan resolved team mention by id: %w", err)
		}
		resolvedToken := strings.TrimSpace(handle)
		label := strings.TrimSpace(displayName)
		if label == "" {
			label = resolvedToken
		}
		if label == "" {
			label = strings.TrimSpace(teamID)
		}
		resolved[resolvedMentionViewerTeamPairKey(viewerMemberID, scopeID, teamID)] = ResolvedMention{
			Type:          "team",
			ID:            strings.TrimSpace(teamID),
			DisplayName:   label,
			ResolvedToken: resolvedToken,
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate resolved team mentions by id: %w", err)
	}
	return resolved, nil
}

func loadResolvedAgentMentionsByIDForViewers(ctx context.Context, queryer coredb.Queryer, workspaceID string, viewerMemberIDs []string, scopeIDs []string, agentIDs []string) (map[string]ResolvedMention, error) {
	if len(viewerMemberIDs) == 0 || len(agentIDs) == 0 {
		return nil, nil
	}
	requestedMentionsCTE, requestedArgs, _, err := coredb.BuildStringPairsCTE("requested_mentions", "scope_id", "agent_id", scopeIDs, agentIDs, 2)
	if err != nil {
		return nil, err
	}
	requestedViewersCTE, requestedViewerArgs, _, err := coredb.BuildStringValuesCTE("requested_viewers", "viewer_member_id", viewerMemberIDs, 2+len(requestedArgs))
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
%s,
ranked_mentions AS (
  SELECT
    requested_viewers.viewer_member_id,
    requested_mentions.scope_id,
    requested_mentions.agent_id,
    a.name,
    COALESCE(NULLIF(agent_member.display_name, ''), NULLIF(a.name, ''), a.member_id) AS display_name,
    ROW_NUMBER() OVER (
      PARTITION BY requested_viewers.viewer_member_id, requested_mentions.scope_id, requested_mentions.agent_id
      ORDER BY CASE WHEN sm.visibility = 'private' THEN 0 ELSE 1 END,
               a.name ASC,
               a.member_id ASC,
               a.id ASC
    ) AS mention_rank
  FROM requested_mentions
  CROSS JOIN requested_viewers
  JOIN scopes mention_scope
    ON mention_scope.id = requested_mentions.scope_id
   AND mention_scope.workspace_id = $1
   AND mention_scope.status = 'active'
   AND mention_scope.archived_at IS NULL
  JOIN agents a
    ON a.workspace_id = $1
   AND a.id = requested_mentions.agent_id
   AND a.status <> 'archived'
  JOIN machines mac
    ON mac.workspace_id = a.workspace_id
   AND mac.id = a.machine_id
   AND mac.status = 'active'
  JOIN scope_memberships sm
    ON sm.scope_id = CASE
        WHEN mention_scope.type = 'thread' AND COALESCE(mention_scope.parent_scope_id, '') <> ''
          THEN mention_scope.parent_scope_id
        ELSE requested_mentions.scope_id
      END
   AND sm.member_id = a.member_id
   AND sm.removed_at IS NULL
   AND sm.archived_at IS NULL
   AND sm.effective_to_seq IS NULL
  JOIN members agent_member
    ON agent_member.id = a.member_id
   AND agent_member.workspace_id = a.workspace_id
   AND agent_member.type = 'agent'
  WHERE (
      sm.visibility = 'public'
      OR (sm.visibility = 'private' AND sm.owner_member_id = requested_viewers.viewer_member_id)
    )
)
SELECT viewer_member_id, scope_id, agent_id, name, display_name
FROM ranked_mentions
WHERE mention_rank = 1
`, requestedMentionsCTE, requestedViewersCTE), append(append([]any{strings.TrimSpace(workspaceID)}, requestedArgs...), requestedViewerArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load resolved agent mentions by id: %w", err)
	}
	defer rows.Close()

	resolved := make(map[string]ResolvedMention)
	for rows.Next() {
		var viewerMemberID string
		var scopeID string
		var agentID string
		var agentName string
		var displayName string
		if err := rows.Scan(&viewerMemberID, &scopeID, &agentID, &agentName, &displayName); err != nil {
			return nil, fmt.Errorf("scan resolved agent mention by id: %w", err)
		}
		resolvedToken := strings.TrimSpace(agentName)
		label := strings.TrimSpace(displayName)
		if label == "" {
			label = resolvedToken
		}
		if label == "" {
			label = strings.TrimSpace(agentID)
		}
		resolved[resolvedMentionViewerAgentPairKey(viewerMemberID, scopeID, agentID)] = ResolvedMention{
			Type:          "agent",
			ID:            strings.TrimSpace(agentID),
			DisplayName:   label,
			ResolvedToken: resolvedToken,
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate resolved agent mentions by id: %w", err)
	}
	return resolved, nil
}

func resolvedMentionPairKey(scopeID string, token string) string {
	return strings.TrimSpace(scopeID) + "\x00" + strings.ToLower(strings.TrimSpace(token))
}

func resolvedMentionViewerPairKey(viewerMemberID string, scopeID string, token string) string {
	return strings.TrimSpace(viewerMemberID) + "\x00" + resolvedMentionPairKey(scopeID, token)
}

func resolvedMentionViewerAgentPairKey(viewerMemberID string, scopeID string, agentID string) string {
	return strings.TrimSpace(viewerMemberID) + "\x00" + resolvedMentionTypedEntityPairKey("agent", scopeID, agentID)
}

func resolvedMentionTeamPairKey(scopeID string, teamID string) string {
	return resolvedMentionTypedEntityPairKey("team", scopeID, teamID)
}

func resolvedMentionViewerTeamPairKey(viewerMemberID string, scopeID string, teamID string) string {
	return strings.TrimSpace(viewerMemberID) + "\x00" + resolvedMentionTeamPairKey(scopeID, teamID)
}

func resolvedMentionTypedEntityPairKey(mentionType string, scopeID string, entityID string) string {
	return strings.ToLower(strings.TrimSpace(mentionType)) + "\x00" + strings.TrimSpace(scopeID) + "\x00" + strings.TrimSpace(entityID)
}

func resolvedMentionViewerTypedEntityPairKey(viewerMemberID string, mentionType string, scopeID string, entityID string) string {
	return strings.TrimSpace(viewerMemberID) + "\x00" + resolvedMentionTypedEntityPairKey(mentionType, scopeID, entityID)
}