package team

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"
	"unicode"
	"unicode/utf8"

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

const (
	MaxTeamMentionTokens    = 32
	MaxTeamExpansionMembers = 256
)

// ResolveTeamMentionTokens resolves active Teams from browser mention tokens.
func (store *Store) ResolveTeamMentionTokens(ctx context.Context, workspaceID string, tokens []string, hints []TeamMentionHint) ([]TeamMentionMatch, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return resolveTeamMentionTokens(ctx, queryer, workspaceID, tokens, hints)
}

// ResolveTeamMentionTokens resolves active Teams from browser mention tokens in
// the current transaction.
func (store *TxStore) ResolveTeamMentionTokens(ctx context.Context, workspaceID string, tokens []string, hints []TeamMentionHint) ([]TeamMentionMatch, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return nil, err
	}
	return resolveTeamMentionTokens(ctx, queryer, workspaceID, tokens, hints)
}

// ExpandTeamMentionsForScope expands Team mentions against one collaboration
// surface, returning only actors visible on that surface.
func (store *Store) ExpandTeamMentionsForScope(ctx context.Context, params ExpandTeamMentionsParams) ([]TeamExpansionEdge, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return expandTeamMentionsForScope(ctx, queryer, params)
}

// ExpandTeamMentionsForScope expands Team mentions against one collaboration
// surface in the current transaction.
func (store *TxStore) ExpandTeamMentionsForScope(ctx context.Context, params ExpandTeamMentionsParams) ([]TeamExpansionEdge, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return nil, err
	}
	return expandTeamMentionsForScope(ctx, queryer, params)
}

// ExpandTeamInvocationForScope expands one explicit Team context against a
// collaboration surface. It does not resolve mention tokens or apply human
// Team-visibility rules; callers must authorize the Team id before calling.
func (store *Store) ExpandTeamInvocationForScope(ctx context.Context, params ExpandTeamInvocationParams) ([]TeamExpansionEdge, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return expandTeamInvocationForScope(ctx, queryer, params)
}

// ExpandTeamInvocationForScope expands one explicit Team context against a
// collaboration surface in the current transaction.
func (store *TxStore) ExpandTeamInvocationForScope(ctx context.Context, params ExpandTeamInvocationParams) ([]TeamExpansionEdge, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return nil, err
	}
	return expandTeamInvocationForScope(ctx, queryer, params)
}

func resolveTeamMentionTokens(ctx context.Context, queryer coredb.Queryer, workspaceID string, tokens []string, hints []TeamMentionHint) ([]TeamMentionMatch, error) {
	return resolveTeamMentionTokensWith(ctx, queryer, workspaceID, tokens, hints, nil, loadActiveTeamsByIDs, resolveTeamMentionTokensByHandle)
}

func resolveMentionableTeamMentionTokens(ctx context.Context, queryer coredb.Queryer, workspaceID string, authorMemberID string, tokens []string, hints []TeamMentionHint) ([]TeamMentionMatch, error) {
	return resolveMentionableTeamMentionTokensWithFallback(ctx, queryer, workspaceID, authorMemberID, tokens, hints, nil)
}

func resolveMentionableTeamMentionTokensWithFallback(ctx context.Context, queryer coredb.Queryer, workspaceID string, authorMemberID string, tokens []string, hints []TeamMentionHint, fallbackTokens []string) ([]TeamMentionMatch, error) {
	loadIDs := func(ctx context.Context, queryer coredb.Queryer, workspaceID string, teamIDs []string) ([]model.WorkspaceTeam, error) {
		return loadMentionableActiveTeamsByIDs(ctx, queryer, workspaceID, authorMemberID, teamIDs)
	}
	resolveByHandle := func(ctx context.Context, queryer coredb.Queryer, workspaceID string, tokens []string) ([]TeamMentionMatch, error) {
		return resolveMentionableTeamMentionTokensByHandle(ctx, queryer, workspaceID, authorMemberID, tokens)
	}
	return resolveTeamMentionTokensWith(ctx, queryer, workspaceID, tokens, hints, fallbackTokens, loadIDs, resolveByHandle)
}

func resolveTeamMentionTokensWith(
	ctx context.Context,
	queryer coredb.Queryer,
	workspaceID string,
	tokens []string,
	hints []TeamMentionHint,
	fallbackTokens []string,
	loadTeamsByIDs func(context.Context, coredb.Queryer, string, []string) ([]model.WorkspaceTeam, error),
	resolveByHandle func(context.Context, coredb.Queryer, string, []string) ([]TeamMentionMatch, error),
) ([]TeamMentionMatch, error) {
	workspaceID = trim(workspaceID)
	allowedTokens := normalizeMentionTokens(tokens)
	if workspaceID == "" || len(allowedTokens) == 0 {
		return nil, nil
	}
	if len(allowedTokens) > MaxTeamMentionTokens {
		allowedTokens = allowedTokens[:MaxTeamMentionTokens]
	}
	fallbackAllowedTokens := allowedTokens
	if fallbackTokens != nil {
		fallbackAllowedTokens = normalizeMentionTokens(fallbackTokens)
		if len(fallbackAllowedTokens) > MaxTeamMentionTokens {
			fallbackAllowedTokens = fallbackAllowedTokens[:MaxTeamMentionTokens]
		}
	}
	allowedTokenSet := make(map[string]struct{}, len(allowedTokens))
	for _, token := range allowedTokens {
		allowedTokenSet[token] = struct{}{}
	}

	matches := make([]TeamMentionMatch, 0)
	seenMatches := make(map[string]struct{})
	seenTokens := make(map[string]struct{})

	hintedIDs := make([]string, 0, len(hints))
	seenHintedIDs := make(map[string]struct{}, len(hints))
	for _, hint := range hints {
		if strings.ToLower(trim(hint.Type)) != "team" {
			continue
		}
		id := trim(hint.ID)
		token := strings.ToLower(trim(hint.Token))
		if id == "" || token == "" {
			continue
		}
		if _, ok := allowedTokenSet[token]; !ok && !mentionTokenMatchesAny(token, allowedTokens) {
			continue
		}
		if _, ok := seenHintedIDs[id]; !ok {
			seenHintedIDs[id] = struct{}{}
			hintedIDs = append(hintedIDs, id)
		}
		if len(hintedIDs) >= MaxTeamMentionTokens {
			break
		}
	}
	if len(hintedIDs) > 0 {
		teams, err := loadTeamsByIDs(ctx, queryer, workspaceID, hintedIDs)
		if err != nil {
			return nil, err
		}
		teamByID := make(map[string]model.WorkspaceTeam, len(teams))
		for _, team := range teams {
			teamByID[team.ID] = team
		}
		for _, hint := range hints {
			if strings.ToLower(trim(hint.Type)) != "team" {
				continue
			}
			team, ok := teamByID[trim(hint.ID)]
			if !ok {
				continue
			}
			token := strings.ToLower(trim(hint.Token))
			matchedToken, ok := matchedMentionTargetToken(token, []string{team.Handle, team.DisplayName, team.ID})
			if !ok || !mentionTokenMatchesAny(token, allowedTokens) {
				continue
			}
			if _, ok := seenTokens[token]; ok {
				continue
			}
			matchKey := team.ID + "\x00" + matchedToken
			if _, ok := seenMatches[matchKey]; ok {
				continue
			}
			seenMatches[matchKey] = struct{}{}
			seenTokens[token] = struct{}{}
			matches = append(matches, teamMentionMatch(team, matchedToken))
		}
	}

	fallbackCandidates := make([]string, 0, len(fallbackAllowedTokens))
	for _, token := range fallbackAllowedTokens {
		if _, ok := seenTokens[token]; ok {
			continue
		}
		fallbackCandidates = append(fallbackCandidates, token)
	}
	if len(fallbackCandidates) == 0 {
		return matches, nil
	}
	fallback, err := resolveByHandle(ctx, queryer, workspaceID, fallbackCandidates)
	if err != nil {
		return nil, err
	}
	for _, match := range fallback {
		matchKey := match.TeamID + "\x00" + match.MentionToken
		if _, ok := seenMatches[matchKey]; ok {
			continue
		}
		if _, ok := seenTokens[match.MentionToken]; ok {
			continue
		}
		seenMatches[matchKey] = struct{}{}
		seenTokens[match.MentionToken] = struct{}{}
		matches = append(matches, match)
	}
	return matches, nil
}

func resolveTeamMentionTokensByHandle(ctx context.Context, queryer coredb.Queryer, workspaceID string, tokens []string) ([]TeamMentionMatch, error) {
	if len(tokens) == 0 {
		return nil, nil
	}
	mentionedTokensCTE, tokenArgs, _, err := coredb.BuildStringValuesCTE("mentioned_tokens", "token", tokens, 3)
	if err != nil {
		return nil, err
	}
	type candidate struct {
		match       TeamMentionMatch
		matchLength int
	}
	candidates := make(map[string]candidate)
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT mentioned_tokens.token,
       t.id, t.workspace_id, t.handle, t.display_name, t.description, t.avatar_url,
       t.visibility, t.status, COALESCE(t.channel_id, ''), t.created_by_member_id, t.created_at, t.updated_at, t.archived_at
FROM mentioned_tokens
JOIN workspace_teams t
  ON t.workspace_id = $1
 AND t.status = $2
 AND (
      LOWER(t.handle) = mentioned_tokens.token
      OR LOWER(t.id) = mentioned_tokens.token
      OR LOWER(t.display_name) = mentioned_tokens.token
      OR mentioned_tokens.token LIKE CONCAT(LOWER(NULLIF(t.handle, '')), '%%')
      OR mentioned_tokens.token LIKE CONCAT(LOWER(NULLIF(t.display_name, '')), '%%')
 )
ORDER BY mentioned_tokens.token ASC, t.handle ASC, t.display_name ASC, t.id ASC
`, mentionedTokensCTE), append([]any{trim(workspaceID), StatusActive}, tokenArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("resolve team mention tokens: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var token string
		var team model.WorkspaceTeam
		if err := scanTeamWithLeadingToken(rows, &token, &team); err != nil {
			return nil, fmt.Errorf("scan team mention candidate: %w", err)
		}
		normalizedToken := strings.ToLower(trim(token))
		matchedToken, ok := matchedMentionTargetToken(normalizedToken, []string{team.Handle, team.DisplayName, team.ID})
		if !ok {
			continue
		}
		next := candidate{match: teamMentionMatch(team, matchedToken), matchLength: len(matchedToken)}
		if previous, ok := candidates[normalizedToken]; ok && previous.matchLength >= next.matchLength {
			continue
		}
		candidates[normalizedToken] = next
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate team mention candidates: %w", err)
	}
	ordered := make([]string, 0, len(candidates))
	for token := range candidates {
		ordered = append(ordered, token)
	}
	sort.Strings(ordered)
	matches := make([]TeamMentionMatch, 0, len(ordered))
	for _, token := range ordered {
		matches = append(matches, candidates[token].match)
	}
	return matches, nil
}

func resolveMentionableTeamMentionTokensByHandle(ctx context.Context, queryer coredb.Queryer, workspaceID string, authorMemberID string, tokens []string) ([]TeamMentionMatch, error) {
	workspaceID = trim(workspaceID)
	authorMemberID = trim(authorMemberID)
	if workspaceID == "" || authorMemberID == "" || len(tokens) == 0 {
		return nil, nil
	}
	type candidate struct {
		match       TeamMentionMatch
		matchLength int
	}
	candidates := make(map[string]candidate)
	if len(tokens) > 0 {
		mentionedTokensCTE, tokenArgs, _, err := coredb.BuildStringValuesCTE("mentioned_tokens", "token", tokens, 3)
		if err != nil {
			return nil, err
		}
		rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT mentioned_tokens.token,
       t.id, t.workspace_id, t.handle, t.display_name, t.description, t.avatar_url,
       t.visibility, t.status, COALESCE(t.channel_id, ''), t.created_by_member_id, t.created_at, t.updated_at, t.archived_at
FROM mentioned_tokens
JOIN workspace_teams t
  ON t.workspace_id = $1
 AND t.status = 'active'
 AND (
      LOWER(t.handle) = mentioned_tokens.token
      OR LOWER(t.id) = mentioned_tokens.token
      OR LOWER(t.display_name) = mentioned_tokens.token
      OR mentioned_tokens.token LIKE CONCAT(LOWER(NULLIF(t.handle, '')), '%%')
      OR mentioned_tokens.token LIKE CONCAT(LOWER(NULLIF(t.display_name, '')), '%%')
 )
JOIN members viewer_member
  ON viewer_member.workspace_id = t.workspace_id
 AND viewer_member.id = $2
 AND viewer_member.type = 'human'
 AND viewer_member.status = 'active'
WHERE t.created_by_member_id = $2
ORDER BY mentioned_tokens.token ASC, t.handle ASC, t.display_name ASC, t.id ASC
`, mentionedTokensCTE), append([]any{workspaceID, authorMemberID}, tokenArgs...)...)
		if err != nil {
			return nil, fmt.Errorf("resolve mentionable team mention tokens: %w", err)
		}
		defer rows.Close()
		for rows.Next() {
			var token string
			var team model.WorkspaceTeam
			if err := scanTeamWithLeadingToken(rows, &token, &team); err != nil {
				return nil, fmt.Errorf("scan mentionable team mention candidate: %w", err)
			}
			normalizedToken := strings.ToLower(trim(token))
			matchedToken, ok := matchedMentionTargetToken(normalizedToken, []string{team.Handle, team.DisplayName, team.ID})
			if !ok {
				continue
			}
			next := candidate{match: teamMentionMatch(team, matchedToken), matchLength: len(matchedToken)}
			if previous, ok := candidates[normalizedToken]; ok && previous.matchLength >= next.matchLength {
				continue
			}
			candidates[normalizedToken] = next
		}
		if err := rows.Err(); err != nil {
			return nil, fmt.Errorf("iterate mentionable team mention candidates: %w", err)
		}
	}
	ordered := make([]string, 0, len(candidates))
	for token := range candidates {
		ordered = append(ordered, token)
	}
	sort.Strings(ordered)
	matches := make([]TeamMentionMatch, 0, len(ordered))
	for _, token := range ordered {
		matches = append(matches, candidates[token].match)
	}
	return matches, nil
}

func expandTeamMentionsForScope(ctx context.Context, queryer coredb.Queryer, params ExpandTeamMentionsParams) ([]TeamExpansionEdge, error) {
	workspaceID := trim(params.WorkspaceID)
	scopeID := trim(params.ScopeID)
	authorMemberID := trim(params.AuthorMemberID)
	if workspaceID == "" || scopeID == "" || authorMemberID == "" {
		return nil, badRequest("workspaceId, scopeId, and authorMemberId are required for team mention expansion")
	}
	effectiveScopeID, err := effectiveMentionScopeID(ctx, queryer, workspaceID, scopeID)
	if err != nil {
		return nil, err
	}

	roots, err := resolveExpansionRootTeams(ctx, queryer, params)
	if err != nil {
		return nil, err
	}
	if len(roots) == 0 {
		return nil, nil
	}
	edges := make([]TeamExpansionEdge, 0)
	for _, root := range roots {
		rootTeam := root.team
		path := []string{rootTeam.ID}
		if err := expandOneTeam(ctx, queryer, workspaceID, effectiveScopeID, authorMemberID, rootTeam, root.mentionToken, rootTeam.ID, true, path, &edges); err != nil {
			return nil, err
		}
		if len(edges) > MaxTeamExpansionMembers {
			return nil, badRequest("team mention expansion exceeds member limit")
		}
	}
	if len(edges) > MaxTeamExpansionMembers {
		return nil, badRequest("team mention expansion exceeds member limit")
	}
	return edges, nil
}

func expandTeamInvocationForScope(ctx context.Context, queryer coredb.Queryer, params ExpandTeamInvocationParams) ([]TeamExpansionEdge, error) {
	workspaceID := trim(params.WorkspaceID)
	scopeID := trim(params.ScopeID)
	authorMemberID := trim(params.AuthorMemberID)
	teamID := trim(params.TeamID)
	if workspaceID == "" || scopeID == "" || authorMemberID == "" || teamID == "" {
		return nil, badRequest("workspaceId, scopeId, authorMemberId, and teamId are required for team invocation expansion")
	}
	effectiveScopeID, err := effectiveMentionScopeID(ctx, queryer, workspaceID, scopeID)
	if err != nil {
		return nil, err
	}
	rootTeam, err := loadActiveTeam(ctx, queryer, workspaceID, teamID)
	if err != nil {
		return nil, err
	}
	edges := make([]TeamExpansionEdge, 0)
	if err := expandOneTeam(ctx, queryer, workspaceID, effectiveScopeID, authorMemberID, rootTeam, rootTeam.Handle, rootTeam.ID, true, []string{rootTeam.ID}, &edges); err != nil {
		return nil, err
	}
	if len(edges) > MaxTeamExpansionMembers {
		return nil, badRequest("team invocation expansion exceeds member limit")
	}
	return edges, nil
}

type expansionRootTeam struct {
	team         model.WorkspaceTeam
	mentionToken string
}

func resolveExpansionRootTeams(ctx context.Context, queryer coredb.Queryer, params ExpandTeamMentionsParams) ([]expansionRootTeam, error) {
	workspaceID := trim(params.WorkspaceID)
	authorMemberID := trim(params.AuthorMemberID)
	seen := make(map[string]struct{})
	roots := make([]expansionRootTeam, 0)
	if len(params.TeamIDs) > 0 {
		ids := normalizeNonEmptyStrings(params.TeamIDs)
		if len(ids) > MaxTeamMentionTokens {
			ids = ids[:MaxTeamMentionTokens]
		}
		teams, err := loadMentionableActiveTeamsByIDs(ctx, queryer, workspaceID, authorMemberID, ids)
		if err != nil {
			return nil, err
		}
		for _, team := range teams {
			if _, ok := seen[team.ID]; ok {
				continue
			}
			seen[team.ID] = struct{}{}
			roots = append(roots, expansionRootTeam{team: team, mentionToken: team.Handle})
		}
	}
	matches, err := resolveMentionableTeamMentionTokensWithFallback(ctx, queryer, workspaceID, authorMemberID, params.Tokens, params.Hints, params.FallbackTokens)
	if err != nil {
		return nil, err
	}
	if len(matches) == 0 {
		return roots, nil
	}
	matchIDs := make([]string, 0, len(matches))
	for _, match := range matches {
		matchIDs = append(matchIDs, match.TeamID)
	}
	teams, err := loadMentionableActiveTeamsByIDs(ctx, queryer, workspaceID, authorMemberID, matchIDs)
	if err != nil {
		return nil, err
	}
	teamByID := make(map[string]model.WorkspaceTeam, len(teams))
	for _, team := range teams {
		teamByID[team.ID] = team
	}
	for _, match := range matches {
		team, ok := teamByID[match.TeamID]
		if !ok {
			continue
		}
		seenKey := team.ID + "\x00" + match.MentionToken
		if _, ok := seen[seenKey]; ok {
			continue
		}
		seen[seenKey] = struct{}{}
		roots = append(roots, expansionRootTeam{team: team, mentionToken: match.MentionToken})
	}
	return roots, nil
}

func expandOneTeam(ctx context.Context, queryer coredb.Queryer, workspaceID string, effectiveScopeID string, authorMemberID string, rootTeam model.WorkspaceTeam, mentionToken string, teamID string, pathWakeOnMention bool, path []string, edges *[]TeamExpansionEdge) error {
	members, err := listActiveTeamMembers(ctx, queryer, workspaceID, teamID)
	if err != nil {
		return err
	}
	for _, member := range members {
		if len(*edges) >= MaxTeamExpansionMembers {
			return badRequest("team mention expansion exceeds member limit")
		}
		memberWakeOnMention := pathWakeOnMention && member.WakeOnTeamMention
		if member.ActorKind != ActorKindAgent {
			continue
		}
		actorID := trim(member.ActorID)
		if actorID == "" || actorID == authorMemberID {
			continue
		}
		visible, err := actorVisibleInMentionScope(ctx, queryer, effectiveScopeID, authorMemberID, actorID)
		if err != nil {
			return err
		}
		if !visible {
			continue
		}
		profile, ok, err := loadExpansionActorProfile(ctx, queryer, workspaceID, effectiveScopeID, member.ActorKind, actorID)
		if err != nil {
			return err
		}
		if !ok {
			continue
		}
		*edges = append(*edges, TeamExpansionEdge{
			TeamID:              rootTeam.ID,
			TeamHandle:          rootTeam.Handle,
			TeamDisplayName:     rootTeam.DisplayName,
			TeamDescription:     rootTeam.Description,
			MentionToken:        mentionToken,
			ActorKind:           member.ActorKind,
			ActorID:             actorID,
			ActorDisplayName:    profile.displayName,
			ActorHandle:         profile.handle,
			TeamMemberID:        member.ID,
			AgentID:             profile.agentID,
			MachineID:           profile.machineID,
			AutoWakeEnabled:     profile.autoWakeEnabled,
			Role:                member.Role,
			RoleLabel:           member.RoleLabel,
			RoleOverlaySnapshot: member.RoleOverlayPrompt,
			RoleOverlayVersion:  member.RoleOverlayVersion,
			WakeOnTeamMention:   memberWakeOnMention,
			ExpansionPath:       append([]string(nil), path...),
		})
	}
	return nil
}

type expansionActorProfile struct {
	displayName     string
	handle          string
	agentID         string
	machineID       string
	autoWakeEnabled bool
}

func loadExpansionActorProfile(ctx context.Context, queryer coredb.Queryer, workspaceID string, scopeID string, actorKind string, actorID string) (expansionActorProfile, bool, error) {
	switch actorKind {
	case ActorKindAgent:
		var profile expansionActorProfile
		err := queryer.QueryRow(ctx, `
SELECT a.id,
       a.machine_id,
       COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), a.member_id) AS display_name,
       COALESCE(NULLIF(a.name, ''), a.member_id) AS handle,
       CASE WHEN sns.state = 'auto-wake' THEN 1 ELSE 0 END AS auto_wake_enabled
FROM agents a
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
 AND mac.status = 'active'
JOIN members m
  ON m.workspace_id = a.workspace_id
 AND m.id = a.member_id
 AND m.type = $3
 AND m.status = 'active'
LEFT JOIN scope_notification_settings sns
  ON sns.scope_id = $4
 AND sns.member_id = a.member_id
WHERE a.workspace_id = $1
  AND a.member_id = $2
  AND a.status <> 'archived'
LIMIT 1
`, trim(workspaceID), trim(actorID), ActorKindAgent, trim(scopeID)).Scan(&profile.agentID, &profile.machineID, &profile.displayName, &profile.handle, &profile.autoWakeEnabled)
		if err != nil {
			if coredb.IsNotFound(err) {
				return expansionActorProfile{}, false, nil
			}
			return expansionActorProfile{}, false, fmt.Errorf("load team expansion agent profile: %w", err)
		}
		return profile, true, nil
	default:
		return expansionActorProfile{}, false, nil
	}
}

func actorVisibleInMentionScope(ctx context.Context, queryer coredb.Queryer, scopeID string, authorMemberID string, actorID string) (bool, error) {
	var exists int
	err := queryer.QueryRow(ctx, `
SELECT 1
FROM scope_memberships sm
LEFT JOIN scope_memberships author_private_context
  ON author_private_context.scope_id = sm.scope_id
 AND author_private_context.member_id = $3
 AND author_private_context.owner_member_id = sm.owner_member_id
 AND author_private_context.visibility = 'private'
 AND author_private_context.removed_at IS NULL
 AND author_private_context.archived_at IS NULL
 AND author_private_context.effective_to_seq IS NULL
WHERE sm.scope_id = $1
  AND sm.member_id = $2
  AND sm.removed_at IS NULL
  AND sm.archived_at IS NULL
  AND sm.effective_to_seq IS NULL
  AND (
      sm.visibility = 'public'
      OR (sm.visibility = 'private' AND sm.owner_member_id = $3)
      OR (sm.visibility = 'private' AND sm.owner_member_id IS NOT NULL AND author_private_context.member_id IS NOT NULL)
  )
LIMIT 1
`, trim(scopeID), trim(actorID), trim(authorMemberID)).Scan(&exists)
	if err != nil {
		if coredb.IsNotFound(err) {
			return false, nil
		}
		return false, fmt.Errorf("load team expansion scope visibility: %w", err)
	}
	return exists == 1, nil
}

func effectiveMentionScopeID(ctx context.Context, queryer coredb.Queryer, workspaceID string, scopeID string) (string, error) {
	var scopeType string
	var parentScopeID string
	err := queryer.QueryRow(ctx, `
SELECT type, COALESCE(parent_scope_id, '')
FROM scopes
WHERE workspace_id = $1
  AND id = $2
  AND status = 'active'
  AND archived_at IS NULL
`, trim(workspaceID), trim(scopeID)).Scan(&scopeType, &parentScopeID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return "", notFound("mention scope not found")
		}
		return "", fmt.Errorf("load mention scope: %w", err)
	}
	if trim(scopeType) == "thread" && trim(parentScopeID) != "" {
		return trim(parentScopeID), nil
	}
	return trim(scopeID), nil
}

func loadActiveTeamsByIDs(ctx context.Context, queryer coredb.Queryer, workspaceID string, teamIDs []string) ([]model.WorkspaceTeam, error) {
	ids := normalizeNonEmptyStrings(teamIDs)
	if len(ids) == 0 {
		return nil, nil
	}
	requestedTeamsCTE, teamArgs, _, err := coredb.BuildStringValuesCTE("requested_teams", "team_id", ids, 3)
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT t.id, t.workspace_id, t.handle, t.display_name, t.description, t.avatar_url,
       t.visibility, t.status, COALESCE(t.channel_id, ''), t.created_by_member_id, t.created_at, t.updated_at, t.archived_at
FROM requested_teams
JOIN workspace_teams t
  ON t.workspace_id = $1
 AND t.id = requested_teams.team_id
 AND t.status = $2
ORDER BY requested_teams.team_id ASC
`, requestedTeamsCTE), append([]any{trim(workspaceID), StatusActive}, teamArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load active teams by id: %w", err)
	}
	defer rows.Close()
	teams := make([]model.WorkspaceTeam, 0, len(ids))
	for rows.Next() {
		team, err := scanTeam(rows)
		if err != nil {
			return nil, fmt.Errorf("scan active team: %w", err)
		}
		teams = append(teams, team)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active teams: %w", err)
	}
	return teams, nil
}

func loadMentionableActiveTeamsByIDs(ctx context.Context, queryer coredb.Queryer, workspaceID string, authorMemberID string, teamIDs []string) ([]model.WorkspaceTeam, error) {
	workspaceID = trim(workspaceID)
	authorMemberID = trim(authorMemberID)
	ids := normalizeNonEmptyStrings(teamIDs)
	if workspaceID == "" || authorMemberID == "" || len(ids) == 0 {
		return nil, nil
	}
	requestedTeamsCTE, teamArgs, _, err := coredb.BuildStringValuesCTE("requested_teams", "team_id", ids, 3)
	if err != nil {
		return nil, err
	}
	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT t.id, t.workspace_id, t.handle, t.display_name, t.description, t.avatar_url,
       t.visibility, t.status, COALESCE(t.channel_id, ''), t.created_by_member_id, t.created_at, t.updated_at, t.archived_at
FROM requested_teams
JOIN workspace_teams t
  ON t.workspace_id = $1
 AND t.id = requested_teams.team_id
 AND t.status = 'active'
JOIN members viewer_member
  ON viewer_member.workspace_id = t.workspace_id
 AND viewer_member.id = $2
 AND viewer_member.type = 'human'
 AND viewer_member.status = 'active'
WHERE t.created_by_member_id = $2
ORDER BY requested_teams.team_id ASC
`, requestedTeamsCTE), append([]any{workspaceID, authorMemberID}, teamArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load mentionable active teams by id: %w", err)
	}
	defer rows.Close()
	teams := make([]model.WorkspaceTeam, 0, len(ids))
	for rows.Next() {
		team, err := scanTeam(rows)
		if err != nil {
			return nil, fmt.Errorf("scan mentionable active team: %w", err)
		}
		teams = append(teams, team)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate mentionable active teams: %w", err)
	}
	return teams, nil
}

func scanTeamWithLeadingToken(row coredb.Row, token *string, team *model.WorkspaceTeam) error {
	var archivedAt sql.NullTime
	if err := row.Scan(
		token,
		&team.ID,
		&team.WorkspaceID,
		&team.Handle,
		&team.DisplayName,
		&team.Description,
		&team.AvatarURL,
		&team.Visibility,
		&team.Status,
		&team.ChannelID,
		&team.CreatedByMemberID,
		&team.CreatedAt,
		&team.UpdatedAt,
		&archivedAt,
	); err != nil {
		return err
	}
	team.ID = trim(team.ID)
	team.WorkspaceID = trim(team.WorkspaceID)
	team.Handle = trim(team.Handle)
	team.Visibility = trim(team.Visibility)
	team.Status = trim(team.Status)
	team.ChannelID = trim(team.ChannelID)
	team.CreatedByMemberID = trim(team.CreatedByMemberID)
	team.ArchivedAt = timePtrFromNull(archivedAt)
	return nil
}

func teamMentionMatch(team model.WorkspaceTeam, mentionToken string) TeamMentionMatch {
	return TeamMentionMatch{
		TeamID:       team.ID,
		Handle:       team.Handle,
		DisplayName:  team.DisplayName,
		Description:  team.Description,
		MentionToken: mentionToken,
	}
}

func normalizeMentionTokens(tokens []string) []string {
	normalized := make([]string, 0, len(tokens))
	seen := make(map[string]struct{}, len(tokens))
	for _, token := range tokens {
		value := strings.ToLower(trim(token))
		if value == "" {
			continue
		}
		if _, ok := seen[value]; ok {
			continue
		}
		seen[value] = struct{}{}
		normalized = append(normalized, value)
	}
	return normalized
}

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

func mentionTokenMatchesAny(target string, tokens []string) bool {
	for _, token := range tokens {
		if mentionTokenMatchesTarget(token, target) || mentionTokenMatchesTarget(target, token) {
			return true
		}
	}
	return false
}

func mentionTokenMatchesTarget(token string, target string) bool {
	_, ok := matchedMentionTargetToken(token, []string{target})
	return ok
}

func matchedMentionTargetToken(token string, targets []string) (string, bool) {
	normalizedToken := strings.ToLower(trim(token))
	if normalizedToken == "" {
		return "", false
	}
	best := ""
	for _, target := range targets {
		normalizedTarget := strings.ToLower(trim(target))
		if normalizedTarget == "" {
			continue
		}
		if normalizedToken == normalizedTarget {
			if len(normalizedTarget) > len(best) {
				best = normalizedTarget
			}
			continue
		}
		if !mentionTokenAllowsCJKPrefixMatch(normalizedToken, normalizedTarget) {
			continue
		}
		if len(normalizedTarget) > len(best) {
			best = normalizedTarget
		}
	}
	return best, best != ""
}

func mentionTokenAllowsCJKPrefixMatch(token string, target string) bool {
	if !strings.HasPrefix(token, target) || len(token) == len(target) {
		return false
	}
	if !containsHanRune(target) {
		return false
	}
	suffix := token[len(target):]
	first, _ := utf8.DecodeRuneInString(suffix)
	return first != utf8.RuneError && unicode.Is(unicode.Han, first)
}

func containsHanRune(value string) bool {
	for _, r := range value {
		if unicode.Is(unicode.Han, r) {
			return true
		}
	}
	return false
}