package message

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

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

var (
	scopeSearchVisibilityCandidatesQuery = scopeQueryDescriptor{
		Name:              "scope_search_visibility_candidates",
		TargetIndex:       "scope_message_search_docs_content_fulltext_idx(content) + scope_message_search_docs_workspace_global_seq_idx(workspace_id, global_seq) / scope_message_search_docs_scope_global_seq_idx(scope_id, global_seq) + scope_messages PRIMARY(id) + scope_memberships PRIMARY(scope_id, member_id) + members PRIMARY(id) + machines_workspace_created_by_status_created_idx(workspace_id, created_by_user_id, status, created_at) + agents PRIMARY(id) / agents_machine_id_idx(machine_id) / agents UNIQUE(member_id) + scope_private_grants_scope_owner_idx + task_scope_executions_scope_key + task_scope_participant_metadata_execution_member_idx",
		AmplificationRisk: "bounded by read-ready scope search projection, one workspace, optional scope, one SQL-readable query window, and caller limit; optional author eligibility uses workspace-scoped indexed member/agent ownership lookups before rank, readability joins, and ORDER/LIMIT; cursor predicates match scoped seq/id or workspace global_seq/scope/seq/id order",
		SQL: `
WITH matched_docs AS (
  SELECT docs.message_id, docs.workspace_id, docs.scope_id, docs.global_seq, docs.content
  FROM scope_message_search_docs docs
  WHERE __MATCH_CONDITION__
),
workspace_docs AS (
  SELECT docs.message_id, docs.workspace_id, docs.scope_id, docs.global_seq, docs.content
  FROM matched_docs docs
  WHERE docs.workspace_id = $1
    AND ($3 = '' OR docs.scope_id = $3)
    AND __AUTHOR_DOC_FILTER__
),
ranked_docs AS (
  SELECT docs.message_id, docs.workspace_id, docs.scope_id, docs.global_seq, __SEARCH_RANK__ AS search_rank
  FROM workspace_docs docs
)
SELECT m.id, m.workspace_id, m.scope_id, m.seq, m.workspace_global_seq, m.author_member_id,
       m.author_type,
       COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id),
       COALESCE(NULLIF(m.idempotency_subject_member_id, ''), m.author_member_id),
       COALESCE(m.client_message_id, ''), COALESCE(m.idempotency_payload_digest, ''),
       m.message_type, m.content, m.metadata, m.created_at, m.updated_at,
       m.edited_at, COALESCE(m.edited_by_member_id, ''), m.edit_revision,
       sm.scope_id, sm.member_id, sm.visibility, COALESCE(sm.owner_member_id, ''), sm.last_read_seq,
       sm.last_read_global_seq, sm.last_activity_global_seq, sm.owner_context_dismissed_through_global_seq, sm.effective_from_seq,
       sm.effective_to_seq, sm.created_at, sm.updated_at, sm.removed_at, sm.archived_at,
       ` + scopePrivateAuthorityGrantIDSQL + `, ` + scopeReadableMessageReasonSQL + `, docs.search_rank, ''
FROM ranked_docs docs
JOIN scope_messages m
  ON m.id = docs.message_id
JOIN scopes message_scope
  ON message_scope.id = m.scope_id
 AND message_scope.workspace_id = m.workspace_id
` + scopeReadableMembershipJoinSQL("$2") + `
` + scopePrivateAuthorityGrantJoinsSQL + `
WHERE docs.workspace_id = $1
  AND ($3 = '' OR docs.scope_id = $3)
  AND ` + scopeReadableMessagePredicateSQL + `
  AND (
       ($3 <> '' AND (
         $6 = 0
         OR ($7 = '' AND m.seq < $6)
         OR ($7 <> '' AND (
           docs.search_rank > $8
           OR (docs.search_rank = $8 AND (
             m.seq < $6
             OR (m.seq = $6 AND m.id > $7)
           ))
         ))
       ))
       OR ($3 = '' AND (
         $4 = 0
         OR ($7 = '' AND docs.global_seq < $4)
         OR ($7 <> '' AND (
           docs.search_rank > $8
           OR (docs.search_rank = $8 AND (
             docs.global_seq < $4
             OR (docs.global_seq = $4 AND m.scope_id > $5)
             OR (docs.global_seq = $4 AND m.scope_id = $5 AND m.seq < $6)
             OR (docs.global_seq = $4 AND m.scope_id = $5 AND m.seq = $6 AND m.id > $7)
           ))
         ))
       ))
      )
ORDER BY
  docs.search_rank ASC,
  CASE WHEN $3 = '' THEN docs.global_seq END DESC,
  CASE WHEN $3 = '' THEN m.scope_id END ASC,
  CASE WHEN $3 = '' THEN m.seq END DESC,
  CASE WHEN $3 = '' THEN m.id END ASC,
  CASE WHEN $3 <> '' THEN m.seq END DESC,
  CASE WHEN $3 <> '' THEN m.id END ASC
LIMIT __LIMIT_PLACEHOLDER__
`,
	}
	scopeSearchReadinessQuery = scopeQueryDescriptor{
		Name:              "scope_search_readiness",
		TargetIndex:       "scope_message_search_state PRIMARY(workspace_id, scope_id, projection_name)",
		AmplificationRisk: "bounded by exact scope search state plus workspace-wide fallback; enforces full migration/backfill/finalize/read-ready contract before API search reads projection rows",
		SQL: `
SELECT migration_status, backfill_status, finalize_status, read_ready
FROM scope_message_search_state
WHERE workspace_id = $1
  AND projection_name = $3
  AND scope_id IN ($2, '')
ORDER BY CASE WHEN scope_id = $2 THEN 0 ELSE 1 END
LIMIT 1
`,
	}
	scopeMessageVisibilityByIDQuery = scopeQueryDescriptor{
		Name:              "scope_message_visibility_by_id",
		TargetIndex:       "scope_messages PRIMARY(id) + scope_memberships PRIMARY(scope_id, member_id) + members PRIMARY(id) + scope_private_grants_scope_owner_idx + task_scope_executions_scope_key + task_scope_participant_metadata_execution_member_idx + agents + scope_memberships PRIMARY(scope_id, member_id)",
		AmplificationRisk: "bounded by one explicit message id, one viewer, and the SQL-readable predicate used by root/source/attachment/diagnostics preview read checks",
		SQL: `
SELECT m.id, m.workspace_id, m.scope_id, m.seq, m.workspace_global_seq, m.author_member_id,
       m.author_type,
       COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id),
       COALESCE(NULLIF(m.idempotency_subject_member_id, ''), m.author_member_id),
       COALESCE(m.client_message_id, ''), COALESCE(m.idempotency_payload_digest, ''),
       m.message_type, m.content, m.metadata, m.created_at, m.updated_at,
       m.edited_at, COALESCE(m.edited_by_member_id, ''), m.edit_revision,
       sm.scope_id, sm.member_id, sm.visibility, COALESCE(sm.owner_member_id, ''), sm.last_read_seq,
       sm.last_read_global_seq, sm.last_activity_global_seq, sm.owner_context_dismissed_through_global_seq, sm.effective_from_seq,
       sm.effective_to_seq, sm.created_at, sm.updated_at, sm.removed_at, sm.archived_at,
       ` + scopePrivateAuthorityGrantIDSQL + `, ` + scopeReadableMessageReasonSQL + `, ''
FROM scope_messages m
JOIN scopes message_scope
  ON message_scope.id = m.scope_id
 AND message_scope.workspace_id = m.workspace_id
` + scopeReadableMembershipJoinSQL("$2") + `
` + scopePrivateAuthorityGrantJoinsSQL + `
WHERE m.workspace_id = $1
  AND m.id = $3
  AND ` + scopeReadableMessagePredicateSQL + `
ORDER BY m.seq DESC
LIMIT $4
`,
	}
	scopeMemberDisplayRowsQuery = scopeQueryDescriptor{
		Name:              "scope_member_display_rows",
		TargetIndex:       "scopes PRIMARY(id) + scope_memberships PRIMARY(scope_id, member_id) + scope_memberships_scope_visibility_idx(scope_id, visibility, removed_at) + members PRIMARY(id)",
		AmplificationRisk: "bounded by one authenticated workspace/scope and one viewer membership; private rows are returned only for the viewer or viewer-owned private membership",
		SQL: `
SELECT sm.member_id, m.type, COALESCE(a.id, ''),
       CASE
         WHEN m.type = 'human' THEN COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), m.id)
         WHEN m.type = 'agent' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), m.id)
         ELSE COALESCE(NULLIF(m.display_name, ''), m.id)
       END,
       sm.visibility, COALESCE(sm.owner_member_id, '')
FROM scopes s
JOIN scope_memberships viewer
  ON viewer.scope_id = s.id
 AND viewer.member_id = $3
 AND viewer.removed_at IS NULL
 AND viewer.archived_at IS NULL
 AND viewer.effective_to_seq IS NULL
 AND viewer.visibility IN ('public', 'private')
JOIN scope_memberships sm
  ON sm.scope_id = s.id
 AND sm.removed_at IS NULL
 AND sm.archived_at IS NULL
 AND sm.effective_to_seq IS NULL
JOIN members m ON m.id = sm.member_id
LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
LEFT JOIN agents a ON m.type = 'agent' AND a.member_id = m.id
WHERE s.workspace_id = $1
  AND s.id = $2
  AND s.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
  AND (sm.visibility = 'public' OR sm.member_id = $3 OR sm.owner_member_id = $3)
ORDER BY sm.visibility ASC, sm.member_id ASC, sm.effective_from_seq ASC
`,
	}
	scopePresentationsByIDQuery = scopeQueryDescriptor{
		Name:              "scope_presentations_by_id",
		TargetIndex:       "scopes PRIMARY(id) + scope_channel_profiles PRIMARY(scope_id) + scope_memberships PRIMARY(scope_id, member_id, effective_from_seq)",
		AmplificationRisk: "bounded by explicit scope ids selected by API adapter conversion and their direct parent scopes for reply-thread labels",
		SQL: `
WITH requested_scopes AS (
    SELECT id, parent_scope_id
    FROM scopes
    WHERE workspace_id = $1
      AND id IN (__SCOPE_IDS__)
),
candidate_label_scopes AS (
    SELECT id AS scope_id
    FROM requested_scopes
    UNION
    SELECT parent_scope_id AS scope_id
    FROM requested_scopes
    WHERE parent_scope_id IS NOT NULL
),
dm_scope_human_members AS (
    SELECT sm.scope_id, MIN(sm.member_id) AS member_id
    FROM scope_memberships sm
    JOIN candidate_label_scopes candidate
      ON candidate.scope_id = sm.scope_id
    JOIN scopes dm_scope
      ON dm_scope.id = sm.scope_id
     AND dm_scope.type = 'dm'
    JOIN members human
      ON human.id = sm.member_id
     AND human.type = 'human'
     AND human.status = 'active'
    WHERE sm.removed_at IS NULL
      AND sm.archived_at IS NULL
      AND sm.effective_to_seq IS NULL
    GROUP BY sm.scope_id
)
SELECT s.id, s.type,
       CASE
         WHEN s.type = 'dm' THEN COALESCE(NULLIF(dm_human.id, ''), NULLIF(cp.slug, ''), NULLIF(s.identity_key, ''), s.id)
         ELSE COALESCE(NULLIF(cp.slug, ''), NULLIF(s.identity_key, ''), s.id)
       END,
       CASE
         WHEN s.type = 'dm' THEN COALESCE(NULLIF(dm_human.id, ''), NULLIF(cp.display_name, ''), NULLIF(cp.slug, ''), NULLIF(s.identity_key, ''), s.id)
         ELSE COALESCE(NULLIF(cp.display_name, ''), NULLIF(cp.slug, ''), NULLIF(s.identity_key, ''), s.id)
       END,
       COALESCE(parent.id, ''), COALESCE(parent.type, ''),
       CASE
         WHEN parent.type = 'dm' THEN COALESCE(NULLIF(parent_dm_human.id, ''), NULLIF(parent_cp.slug, ''), NULLIF(parent.identity_key, ''), parent.id, '')
         ELSE COALESCE(NULLIF(parent_cp.slug, ''), NULLIF(parent.identity_key, ''), parent.id, '')
       END,
       CASE
         WHEN parent.type = 'dm' THEN COALESCE(NULLIF(parent_dm_human.id, ''), NULLIF(parent_cp.display_name, ''), NULLIF(parent_cp.slug, ''), NULLIF(parent.identity_key, ''), parent.id, '')
         ELSE COALESCE(NULLIF(parent_cp.display_name, ''), NULLIF(parent_cp.slug, ''), NULLIF(parent.identity_key, ''), parent.id, '')
       END
FROM requested_scopes requested
JOIN scopes s ON s.id = requested.id
LEFT JOIN scope_channel_profiles cp ON cp.scope_id = s.id
LEFT JOIN dm_scope_human_members dm_member ON dm_member.scope_id = s.id
LEFT JOIN members dm_human ON dm_human.id = dm_member.member_id
LEFT JOIN scopes parent ON parent.id = s.parent_scope_id
LEFT JOIN scope_channel_profiles parent_cp ON parent_cp.scope_id = parent.id
LEFT JOIN dm_scope_human_members parent_dm_member ON parent_dm_member.scope_id = parent.id
LEFT JOIN members parent_dm_human ON parent_dm_human.id = parent_dm_member.member_id
WHERE s.workspace_id = $1
`,
	}
	scopeMemberPresentationsByIDQuery = scopeQueryDescriptor{
		Name:              "scope_member_presentations_by_id",
		TargetIndex:       "members PRIMARY(id) + users PRIMARY(id) + agents(member_id) + service_members(member_id) + service_installations(id) + service_definitions(id)",
		AmplificationRisk: "bounded by explicit message author member ids selected by API adapter conversion",
		SQL: `
SELECT m.id, m.type,
       CASE WHEN m.type = 'human' THEN COALESCE(u.id, '') WHEN m.type = 'agent' THEN COALESCE(a.id, '') ELSE m.id END,
       CASE WHEN m.type = 'service' THEN COALESCE(ai.id, m.id) ELSE CASE WHEN m.type = 'human' THEN COALESCE(u.id, '') WHEN m.type = 'agent' THEN COALESCE(a.id, '') ELSE m.id END END,
       CASE
         WHEN m.type = 'human' THEN m.id
         WHEN m.type = 'agent' THEN COALESCE(NULLIF(a.name, ''), NULLIF(m.display_name, ''), m.id)
         WHEN m.type = 'service' THEN COALESCE(NULLIF(ai.handle, ''), NULLIF(m.display_name, ''), NULLIF(app_def.slug, ''), m.id)
         ELSE m.id
       END,
       CASE
         WHEN m.type = 'human' THEN COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), m.id)
         WHEN m.type = 'agent' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), m.id)
         WHEN m.type = 'service' AND app_def.slug IN ('github', 'gitea') THEN
           CASE
             WHEN NULLIF(ai.handle, '') IS NOT NULL THEN NULLIF(ai.handle, '')
             WHEN NULLIF(m.display_name, '') IS NOT NULL THEN NULLIF(m.display_name, '')
             WHEN NULLIF(app_def.name, '') IS NOT NULL THEN NULLIF(app_def.name, '')
             ELSE m.id
           END
         WHEN m.type = 'service' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(app_def.name, ''), NULLIF(ai.handle, ''), m.id)
         ELSE COALESCE(NULLIF(m.display_name, ''), m.id)
       END,
       CASE WHEN m.type = 'human' THEN COALESCE(u.avatar_url, '') WHEN m.type = 'agent' THEN COALESCE(a.avatar_url, '') WHEN m.type = 'service' THEN COALESCE(app_def.avatar_url, '') ELSE '' END,
       CASE WHEN m.type = 'service' AND ai.id IS NOT NULL THEN 'app' ELSE '' END,
       CASE WHEN m.type = 'service' THEN COALESCE(app_def.slug, '') ELSE '' END
FROM members m
LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
LEFT JOIN agents a ON m.type = 'agent' AND a.member_id = m.id
LEFT JOIN service_members ai ON m.type = 'service' AND ai.workspace_id = m.workspace_id AND ai.member_id = m.id
LEFT JOIN service_installations app_inst ON app_inst.id = ai.service_installation_id
LEFT JOIN service_definitions app_def ON app_def.id = app_inst.service_definition_id
WHERE m.workspace_id = $1
  AND m.id IN (__MEMBER_IDS__)
`,
	}
	scopeVisibleAuthorMemberIDsQuery = scopeQueryDescriptor{
		Name:              "scope_visible_author_member_ids",
		TargetIndex:       "scope_messages_scope_seq_key(scope_id, seq) + shared scope readability joins",
		AmplificationRisk: "one explicit thread scope and one authorized viewer; DISTINCT output is bounded by workspace members while the shared readable-message predicate prevents private-author disclosure",
		SQL: `
SELECT DISTINCT m.author_member_id
FROM scope_messages m
JOIN scopes message_scope
  ON message_scope.id = m.scope_id
 AND message_scope.workspace_id = m.workspace_id
` + scopeReadableMembershipJoinSQL("$2") + `
` + scopePrivateAuthorityGrantJoinsSQL + `
WHERE m.scope_id = $1
  AND m.workspace_id = $3
  AND m.author_member_id <> ''
  AND ` + scopeReadableMessagePredicateSQL + `
ORDER BY m.author_member_id ASC
`,
	}
)

type ScopeAPIService struct {
	store      *Store
	db         coredb.QueryExecer
	repos      ScopeRepositories
	visibility VisibilityFilter
}

// NewScopeAPIService creates the scope-backed API adapter used by the cutover
// routes. The adapter owns API-shaped reads and receive conversion while all SQL
// remains in the message repository/query descriptors.
func NewScopeAPIService(store *Store) ScopeAPIService {
	if store == nil {
		return ScopeAPIService{
			visibility: NewVisibilityFilter(),
		}
	}
	return ScopeAPIService{
		store:      store,
		db:         store.db,
		repos:      NewScopeRepositories(store.db),
		visibility: NewVisibilityFilter(),
	}
}

// NewScopeAPIServiceForQueryer exposes the message-owned read authority on a
// caller transaction/query surface without pulling sidecar packages into the
// message store facade. It is intended for resource sidecars that need
// message-id read-checks inside their own transaction.
func NewScopeAPIServiceForQueryer(queryer coredb.Queryer) ScopeAPIService {
	if queryer == nil {
		return ScopeAPIService{
			visibility: NewVisibilityFilter(),
		}
	}
	execer := scopeAPIReadOnlyQueryExecer{queryer: queryer}
	if typed, ok := queryer.(coredb.QueryExecer); ok {
		execer.execer = typed
	}
	return ScopeAPIService{
		db:         execer,
		repos:      NewScopeRepositories(execer),
		visibility: NewVisibilityFilter(),
	}
}

type scopeAPIReadOnlyQueryExecer struct {
	queryer coredb.Queryer
	execer  coredb.QueryExecer
}

func (adapter scopeAPIReadOnlyQueryExecer) Database() string {
	type databaseReporter interface {
		Database() string
	}
	if reporter, ok := adapter.execer.(databaseReporter); ok {
		return reporter.Database()
	}
	if reporter, ok := adapter.queryer.(databaseReporter); ok {
		return reporter.Database()
	}
	return ""
}

func (adapter scopeAPIReadOnlyQueryExecer) QueryRow(ctx context.Context, query string, args ...any) coredb.Row {
	if adapter.execer != nil {
		return adapter.execer.QueryRow(ctx, query, args...)
	}
	return adapter.queryer.QueryRow(ctx, query, args...)
}

func (adapter scopeAPIReadOnlyQueryExecer) Query(ctx context.Context, query string, args ...any) (coredb.Rows, error) {
	if adapter.execer != nil {
		return adapter.execer.Query(ctx, query, args...)
	}
	return adapter.queryer.Query(ctx, query, args...)
}

func (adapter scopeAPIReadOnlyQueryExecer) Exec(ctx context.Context, query string, args ...any) (coredb.Result, error) {
	if adapter.execer != nil {
		return adapter.execer.Exec(ctx, query, args...)
	}
	return nil, fmt.Errorf("scope api read-only query surface cannot execute writes")
}

type ScopeAPIHistoryRequest struct {
	WorkspaceID            string
	ScopeID                string
	ViewerMemberID         string
	ViewerType             string
	RequestKind            string
	BeforeSeq              int64
	Limit                  int
	CandidateBatchLimit    int
	MaxCandidateBatches    int
	IncludeReplyThreadRoot bool
	AdvanceReadState       bool
}

type ScopeAPIHistoryPage struct {
	TargetKey                string
	CoversThroughSeq         int64
	Messages                 []HistoryMessage
	NextCursor               int64
	HasMore                  bool
	CandidateBudgetExhausted bool
	LastReadSeq              int64
}

type ScopeAPIMessagePage struct {
	Messages                 []AppMessage
	NextCursor               int64
	HasMore                  bool
	CandidateBudgetExhausted bool
}

type ScopeAPISearchRequest struct {
	WorkspaceID         string
	ScopeID             string
	ViewerMemberID      string
	ViewerType          string
	Query               string
	AuthorFilter        MessageAuthorFilter
	BeforeSeq           int64
	BeforeCursor        MessageSearchCursor
	Limit               int
	CandidateBatchLimit int
	MaxCandidateBatches int
}

type ScopeAPISearchPage struct {
	Messages                 []HistoryMessage
	NextCursor               int64
	NextSearchCursor         MessageSearchCursor
	HasMore                  bool
	CandidateBudgetExhausted bool
}

type ScopeAPIReadCheckRequest struct {
	WorkspaceID    string
	MessageID      string
	ViewerMemberID string
	ViewerType     string
	RequestKind    string
}

type ScopeAPIPinnedMessageRequest struct {
	WorkspaceID    string
	MessageID      string
	ViewerMemberID string
	ViewerType     string
	RequestKind    string
}

type ScopeAPIReadCheckResult struct {
	Allowed bool
	Reason  string
	Message *HistoryMessage
}

type ScopeMemberDisplayRow struct {
	MemberID      string
	MemberType    string
	PublicID      string
	DisplayName   string
	Visibility    string
	OwnerMemberID string
}

type ScopeMemberListRequest struct {
	WorkspaceID    string
	ScopeID        string
	ViewerMemberID string
	Limit          int
	Cursor         string
	Query          string
	MemberType     string
	Visibility     string
}

type ScopeMemberListPage struct {
	Members    []ScopeMemberDisplayRow
	Limit      int
	HasMore    bool
	NextCursor string
}

type scopePresentation struct {
	ID                string
	Type              string
	Name              string
	DisplayName       string
	ParentID          string
	ParentType        string
	ParentName        string
	ParentDisplayName string
}

type scopeMemberPresentation struct {
	MemberID    string
	MemberType  string
	PublicID    string
	PrincipalID string
	TargetID    string
	DisplayName string
	AvatarURL   string
	ServiceKind string
	ServiceSlug string
}

func (presentation scopeMemberPresentation) AuthorProfile() AuthorProfile {
	actorType := strings.TrimSpace(presentation.MemberType)
	if actorType == "" {
		actorType = "service"
	}
	return AuthorProfile{
		ActorType:   actorType,
		PrincipalID: firstNonEmpty(presentation.PrincipalID, presentation.PublicID, presentation.MemberID),
		MemberID:    strings.TrimSpace(presentation.MemberID),
		TargetID:    firstNonEmpty(presentation.TargetID, presentation.PublicID, presentation.MemberID),
		DisplayName: firstNonEmpty(presentation.DisplayName, presentation.TargetID, presentation.PublicID, presentation.MemberID),
		AvatarURL:   strings.TrimSpace(presentation.AvatarURL),
		ServiceKind: strings.TrimSpace(presentation.ServiceKind),
		ServiceSlug: strings.TrimSpace(presentation.ServiceSlug),
	}
}

func (service ScopeAPIService) ListHistory(ctx context.Context, request ScopeAPIHistoryRequest) (ScopeAPIHistoryPage, error) {
	lastReadSeq, err := service.loadHistoryLastReadSeq(ctx, request)
	if err != nil {
		return ScopeAPIHistoryPage{}, err
	}
	result, err := service.listVisible(ctx, request)
	if err != nil {
		return ScopeAPIHistoryPage{}, err
	}
	readStateRefs := append([]VisibleMessageRef(nil), result.Messages...)
	targetKey, coversThroughSeq := historyPageCoverage(request, readStateRefs)
	if request.IncludeReplyThreadRoot && request.BeforeSeq == 0 {
		root, ok, err := service.loadReplyThreadRootContext(ctx, request, result.Messages)
		if err != nil {
			return ScopeAPIHistoryPage{}, err
		}
		if ok {
			result.Messages = prependVisibleMessageRef(result.Messages, root)
		}
	}
	messages, err := service.historyMessagesFromVisibleRefs(ctx, strings.TrimSpace(request.WorkspaceID), result.Messages)
	if err != nil {
		return ScopeAPIHistoryPage{}, err
	}
	if request.AdvanceReadState && request.BeforeSeq == 0 {
		if err := service.advanceHistoryReadState(ctx, request, readStateRefs, lastReadSeq); err != nil {
			return ScopeAPIHistoryPage{}, err
		}
	}
	return ScopeAPIHistoryPage{
		TargetKey:                targetKey,
		CoversThroughSeq:         coversThroughSeq,
		Messages:                 messages,
		NextCursor:               result.NextCursor,
		HasMore:                  result.HasMore,
		CandidateBudgetExhausted: result.CandidateBudgetExhausted,
		LastReadSeq:              lastReadSeq,
	}, nil
}

func historyPageCoverage(request ScopeAPIHistoryRequest, refs []VisibleMessageRef) (string, int64) {
	scopeID := strings.TrimSpace(request.ScopeID)
	if scopeID == "" || request.BeforeSeq != 0 || len(refs) == 0 {
		return scopeID, 0
	}
	var maxSeq int64
	for _, ref := range refs {
		message := ref.Message
		if strings.TrimSpace(message.ScopeID) != scopeID {
			continue
		}
		if message.Seq > maxSeq {
			maxSeq = message.Seq
		}
	}
	return scopeID, maxSeq
}

func (service ScopeAPIService) ListAppMessages(ctx context.Context, request ScopeAPIHistoryRequest) (ScopeAPIMessagePage, error) {
	result, err := service.listVisible(ctx, request)
	if err != nil {
		return ScopeAPIMessagePage{}, err
	}
	if request.IncludeReplyThreadRoot && request.BeforeSeq == 0 {
		root, ok, err := service.loadReplyThreadRootContext(ctx, request, result.Messages)
		if err != nil {
			return ScopeAPIMessagePage{}, err
		}
		if ok {
			result.Messages = prependVisibleMessageRef(result.Messages, root)
		}
	}
	messages, err := service.appMessagesFromVisibleRefs(ctx, strings.TrimSpace(request.WorkspaceID), result.Messages)
	if err != nil {
		return ScopeAPIMessagePage{}, err
	}
	if err := AttachResolvedMentionsForAppMessages(ctx, service.db, request.WorkspaceID, request.ViewerMemberID, messages); err != nil {
		return ScopeAPIMessagePage{}, err
	}
	if err := AttachMessagePinStateForAppMessages(ctx, service.db, request.WorkspaceID, request.ViewerMemberID, messages); err != nil {
		return ScopeAPIMessagePage{}, err
	}
	return ScopeAPIMessagePage{Messages: messages, NextCursor: result.NextCursor, HasMore: result.HasMore, CandidateBudgetExhausted: result.CandidateBudgetExhausted}, nil
}

func (service ScopeAPIService) LoadPinnedAppMessage(ctx context.Context, request ScopeAPIPinnedMessageRequest) (AppMessage, bool, error) {
	if err := service.requireDB(); err != nil {
		return AppMessage{}, false, err
	}
	workspaceID := strings.TrimSpace(request.WorkspaceID)
	messageID := strings.TrimSpace(request.MessageID)
	viewerMemberID := strings.TrimSpace(request.ViewerMemberID)
	if workspaceID == "" || messageID == "" || viewerMemberID == "" {
		return AppMessage{}, false, invalidArgument("pinned app message workspace, message, and viewer are required")
	}
	candidates, err := service.repos.Messages.LoadVisibilityCandidatesByMessageID(ctx, workspaceID, viewerMemberID, messageID)
	if err != nil {
		return AppMessage{}, false, err
	}
	visible := service.visibility.FilterCandidates(candidates, VisibilityFilterRequest{
		ViewerMemberID: viewerMemberID,
		ViewerType:     firstNonEmpty(request.ViewerType, ScopeActorHuman),
		RequestKind:    firstNonEmpty(request.RequestKind, ScopeVisibilityRequestSourceRef),
		Limit:          1,
	})
	if len(visible.Messages) == 0 {
		return AppMessage{}, false, nil
	}
	messages, err := service.appMessagesFromVisibleRefs(ctx, workspaceID, visible.Messages[:1])
	if err != nil {
		return AppMessage{}, false, err
	}
	if err := AttachResolvedMentionsForAppMessages(ctx, service.db, workspaceID, viewerMemberID, messages); err != nil {
		return AppMessage{}, false, err
	}
	if err := AttachMessagePinStateForAppMessages(ctx, service.db, workspaceID, viewerMemberID, messages); err != nil {
		return AppMessage{}, false, err
	}
	return messages[0], true, nil
}

func (service ScopeAPIService) loadHistoryLastReadSeq(ctx context.Context, request ScopeAPIHistoryRequest) (int64, error) {
	if !request.AdvanceReadState {
		return 0, nil
	}
	membership, err := service.repos.Memberships.LoadActive(ctx, strings.TrimSpace(request.ScopeID), strings.TrimSpace(request.ViewerMemberID))
	if err != nil {
		return 0, err
	}
	return membership.LastReadSeq, nil
}

func (service ScopeAPIService) loadReplyThreadRootContext(ctx context.Context, request ScopeAPIHistoryRequest, existing []VisibleMessageRef) (VisibleMessageRef, bool, error) {
	return service.loadReplyThreadRootVisibleRef(
		ctx,
		strings.TrimSpace(request.WorkspaceID),
		strings.TrimSpace(request.ScopeID),
		strings.TrimSpace(request.ViewerMemberID),
		firstNonEmpty(request.ViewerType, ScopeActorHuman),
		existing,
	)
}

func (service ScopeAPIService) loadReplyThreadRootVisibleRef(ctx context.Context, workspaceID string, scopeID string, viewerMemberID string, viewerType string, existing []VisibleMessageRef) (VisibleMessageRef, bool, error) {
	workspaceID = strings.TrimSpace(workspaceID)
	scopeID = strings.TrimSpace(scopeID)
	viewerMemberID = strings.TrimSpace(viewerMemberID)
	if workspaceID == "" || scopeID == "" || viewerMemberID == "" {
		return VisibleMessageRef{}, false, invalidArgument("reply thread root context workspace, scope, and viewer are required")
	}
	scope, err := service.repos.Scopes.LoadScope(ctx, scopeID)
	if err != nil {
		return VisibleMessageRef{}, false, err
	}
	if strings.TrimSpace(scope.WorkspaceID) != workspaceID || strings.TrimSpace(scope.Type) != "thread" || strings.TrimSpace(scope.RootMessageID) == "" {
		return VisibleMessageRef{}, false, nil
	}
	for _, ref := range existing {
		if strings.TrimSpace(ref.Message.ID) == strings.TrimSpace(scope.RootMessageID) {
			return VisibleMessageRef{}, false, nil
		}
	}
	candidates, err := service.repos.Messages.LoadVisibilityCandidatesByMessageID(ctx, workspaceID, viewerMemberID, scope.RootMessageID)
	if err != nil {
		return VisibleMessageRef{}, false, err
	}
	visible := service.visibility.FilterCandidates(candidates, VisibilityFilterRequest{
		ViewerMemberID: viewerMemberID,
		ViewerType:     firstNonEmpty(viewerType, ScopeActorHuman),
		ScopeID:        strings.TrimSpace(scope.ParentScopeID),
		RequestKind:    ScopeVisibilityRequestRootContext,
		Limit:          1,
	})
	if len(visible.Messages) == 0 {
		return VisibleMessageRef{}, false, nil
	}
	return visible.Messages[0], true, nil
}

func (service ScopeAPIService) advanceHistoryReadState(ctx context.Context, request ScopeAPIHistoryRequest, refs []VisibleMessageRef, lastReadSeq int64) error {
	scopeID := strings.TrimSpace(request.ScopeID)
	viewerMemberID := strings.TrimSpace(request.ViewerMemberID)
	if scopeID == "" || viewerMemberID == "" {
		return invalidArgument("history read-state scope and viewer are required")
	}
	var maxSeq, maxGlobalSeq int64
	for _, ref := range refs {
		message := ref.Message
		if strings.TrimSpace(message.ScopeID) != scopeID {
			continue
		}
		if message.Seq > maxSeq || (message.Seq == maxSeq && message.WorkspaceGlobalSeq > maxGlobalSeq) {
			maxSeq = message.Seq
			maxGlobalSeq = message.WorkspaceGlobalSeq
		}
	}
	if maxSeq == 0 || maxSeq <= lastReadSeq {
		return nil
	}
	return service.repos.Memberships.AdvanceReadState(ctx, scopeID, viewerMemberID, maxSeq, maxGlobalSeq, time.Now().UTC())
}

func prependVisibleMessageRef(refs []VisibleMessageRef, root VisibleMessageRef) []VisibleMessageRef {
	rootID := strings.TrimSpace(root.Message.ID)
	if rootID == "" {
		return refs
	}
	for _, ref := range refs {
		if strings.TrimSpace(ref.Message.ID) == rootID {
			return refs
		}
	}
	prepended := make([]VisibleMessageRef, 0, len(refs)+1)
	prepended = append(prepended, root)
	prepended = append(prepended, refs...)
	return prepended
}

func mergeReceiveMessageMetadataWithReplyThreadRoot(metadata map[string]any, root map[string]any) map[string]any {
	if len(root) == 0 {
		return metadata
	}
	merged := CopyMessageMetadata(metadata)
	if len(merged) == 0 {
		merged = make(map[string]any, 1)
	}
	merged[ReceiveMetadataReplyThreadRootKey] = root
	return merged
}

func (service ScopeAPIService) SearchMessages(ctx context.Context, request ScopeAPISearchRequest) (ScopeAPISearchPage, error) {
	if err := service.requireDB(); err != nil {
		return ScopeAPISearchPage{}, err
	}
	query := strings.TrimSpace(request.Query)
	if query == "" {
		return ScopeAPISearchPage{Messages: []HistoryMessage{}}, nil
	}
	if !coredb.IsTiDB(service.db) {
		return ScopeAPISearchPage{}, requestError{kind: ErrMessageSearchUnavailable, message: "scope message search is unavailable without a TiDB core database"}
	}
	if err := service.repos.Search.RequireReadReady(ctx, strings.TrimSpace(request.WorkspaceID), strings.TrimSpace(request.ScopeID)); err != nil {
		return ScopeAPISearchPage{}, err
	}
	limit := normalizeScopeAPILimit(request.Limit, 20)
	result, err := service.searchVisible(ctx, request, limit)
	if err != nil {
		return ScopeAPISearchPage{}, err
	}
	messages, err := service.historyMessagesFromVisibleRefs(ctx, strings.TrimSpace(request.WorkspaceID), result.Messages)
	if err != nil {
		return ScopeAPISearchPage{}, err
	}
	return ScopeAPISearchPage{
		Messages:                 messages,
		NextCursor:               result.NextCursor,
		NextSearchCursor:         result.NextSearchCursor,
		HasMore:                  result.HasMore,
		CandidateBudgetExhausted: result.CandidateBudgetExhausted,
	}, nil
}

func (service ScopeAPIService) CanReadMessage(ctx context.Context, request ScopeAPIReadCheckRequest) (ScopeAPIReadCheckResult, error) {
	if err := service.requireDB(); err != nil {
		return ScopeAPIReadCheckResult{}, err
	}
	requestKind := firstNonEmpty(request.RequestKind, ScopeVisibilityRequestHistory)
	candidates, err := service.repos.Messages.LoadVisibilityCandidatesByMessageID(ctx, strings.TrimSpace(request.WorkspaceID), strings.TrimSpace(request.ViewerMemberID), strings.TrimSpace(request.MessageID))
	if err != nil {
		return ScopeAPIReadCheckResult{}, err
	}
	visible := service.visibility.FilterCandidates(candidates, VisibilityFilterRequest{
		ViewerMemberID: strings.TrimSpace(request.ViewerMemberID),
		ViewerType:     firstNonEmpty(request.ViewerType, ScopeActorHuman),
		RequestKind:    requestKind,
		Limit:          1,
	})
	if len(visible.Messages) == 0 {
		return ScopeAPIReadCheckResult{Allowed: false}, nil
	}
	messages, err := service.historyMessagesFromVisibleRefs(ctx, strings.TrimSpace(request.WorkspaceID), visible.Messages[:1])
	if err != nil {
		return ScopeAPIReadCheckResult{}, err
	}
	return ScopeAPIReadCheckResult{Allowed: true, Reason: visible.Messages[0].Reason, Message: &messages[0]}, nil
}

func (service ScopeAPIService) ListMemberDisplayRows(ctx context.Context, workspaceID string, scopeID string, viewerMemberID string) ([]ScopeMemberDisplayRow, error) {
	page, err := service.ListMemberDisplayRowsPage(ctx, ScopeMemberListRequest{
		WorkspaceID:    workspaceID,
		ScopeID:        scopeID,
		ViewerMemberID: viewerMemberID,
		Limit:          100,
	})
	if err != nil {
		return nil, err
	}
	return page.Members, nil
}

func (service ScopeAPIService) ListMemberDisplayRowsPage(ctx context.Context, request ScopeMemberListRequest) (ScopeMemberListPage, error) {
	if err := service.requireDB(); err != nil {
		return ScopeMemberListPage{}, err
	}
	if strings.TrimSpace(request.WorkspaceID) == "" {
		return ScopeMemberListPage{}, invalidArgument("scope member display workspace is required")
	}
	page, err := service.repos.Scopes.ListMemberDisplayRowsPage(ctx, request)
	if err != nil {
		return ScopeMemberListPage{}, err
	}
	return page, nil
}

// PreloadReplyThreadRootMetadata attaches a reply-thread root preview using
// the same read-check/readable predicate semantics as ordinary root context
// reads; it must not reintroduce a separate receive visibility mode.
func (service ScopeAPIService) PreloadReplyThreadRootMetadata(ctx context.Context, workspaceID string, viewerMemberID string, viewerType string, msg ReceiveMessage) (ReceiveMessage, error) {
	if strings.TrimSpace(msg.MessageID) == "" || strings.TrimSpace(msg.ChannelType) != "thread" {
		return msg, nil
	}
	if len(msg.Metadata) > 0 {
		if _, ok := msg.Metadata[ReceiveMetadataReplyThreadRootKey]; ok {
			return msg, nil
		}
	}

	workspaceID = strings.TrimSpace(workspaceID)
	viewerMemberID = strings.TrimSpace(viewerMemberID)
	if workspaceID == "" || viewerMemberID == "" {
		return msg, nil
	}
	isRoot, err := service.deliveryMessageStartsReplyThread(ctx, workspaceID, strings.TrimSpace(msg.MessageID))
	if err != nil || isRoot {
		return msg, err
	}

	candidates, err := service.repos.Messages.LoadVisibilityCandidatesByMessageID(ctx, workspaceID, viewerMemberID, strings.TrimSpace(msg.MessageID))
	if err != nil {
		return msg, err
	}
	visible := service.visibility.FilterCandidates(candidates, VisibilityFilterRequest{
		ViewerMemberID: viewerMemberID,
		ViewerType:     firstNonEmpty(viewerType, ScopeActorHuman),
		RequestKind:    ScopeVisibilityRequestRootContext,
		Limit:          1,
	})
	if len(visible.Messages) == 0 {
		return msg, nil
	}
	scopeID := strings.TrimSpace(visible.Messages[0].Message.ScopeID)
	if scopeID == "" {
		return msg, nil
	}
	needsRoot, err := service.threadNeedsReplyThreadRoot(ctx, scopeID, viewerMemberID)
	if err != nil || !needsRoot {
		return msg, err
	}
	root, ok, err := service.loadReplyThreadRootMetadata(ctx, workspaceID, scopeID, viewerMemberID, viewerType)
	if err != nil || !ok {
		return msg, err
	}
	msg.Metadata = mergeReceiveMessageMetadataWithReplyThreadRoot(msg.Metadata, root)
	return msg, nil
}

func (service ScopeAPIService) threadNeedsReplyThreadRoot(ctx context.Context, scopeID string, viewerMemberID string) (bool, error) {
	membership, err := service.repos.Memberships.LoadActive(ctx, strings.TrimSpace(scopeID), strings.TrimSpace(viewerMemberID))
	if err != nil {
		return false, err
	}
	return membership.LastReadSeq <= 0, nil
}

func (service ScopeAPIService) loadReplyThreadRootMetadata(ctx context.Context, workspaceID string, scopeID string, viewerMemberID string, viewerType string) (map[string]any, bool, error) {
	rootRef, ok, err := service.loadReplyThreadRootVisibleRef(ctx, workspaceID, scopeID, viewerMemberID, viewerType, nil)
	if err != nil || !ok {
		return nil, ok, err
	}
	metadata, err := service.replyThreadRootMetadataFromVisibleRef(ctx, workspaceID, rootRef)
	if err != nil {
		return nil, false, err
	}
	return metadata, true, nil
}

func (service ScopeAPIService) replyThreadRootMetadataFromVisibleRef(ctx context.Context, workspaceID string, ref VisibleMessageRef) (map[string]any, error) {
	// Build the reply-thread root preview from the authoritative AppMessage
	// projection so the typed workflow_run/schedule_run source is resolved and
	// the automation service-author overlay is applied exactly as the app and
	// history surfaces do. This keeps Workflow/Schedule reply-thread roots from
	// serializing the frozen Human as the visible author, and it carries the
	// typed source as provenance into the metadata for Desktop resolution.
	appMessages, err := service.appMessagesFromVisibleRefs(ctx, workspaceID, []VisibleMessageRef{ref})
	if err != nil {
		return nil, err
	}
	var appMessage AppMessage
	var authorType string
	var authorName string
	if len(appMessages) == 1 {
		appMessage = appMessages[0]
		authorType = appMessage.AuthorActorType()
		if IsAutomationServiceAuthor(appMessage.AuthorProfile) {
			// Workflow/Schedule: the recognizable automation name is the visible
			// author, never the frozen Human authorization/audit principal.
			authorName = appMessage.AuthorDisplayName()
		} else if strings.TrimSpace(appMessage.AuthorProfile.ActorType) == AuthorActorTypeHuman {
			// Preserve ordinary Human reply-thread-root wire: the durable member
			// id, exactly as the original projection produced.
			authorName = appMessage.AuthorMemberID()
		} else {
			// Agent/App/service: the profile display name (agent/app name).
			authorName = appMessage.AuthorDisplayName()
		}
	}
	if authorType == "" {
		authorType = ref.Message.AuthorType
	}
	if authorName == "" {
		authorName = firstNonEmpty(ref.Message.AuthorMemberID, ref.Message.AuthorType)
	}

	metadata := map[string]any{
		"messageId":  ref.Message.ID,
		"authorType": authorType,
		"authorName": authorName,
		// Old daemon reply-thread-root metadata still reads senderName. New
		// desktop/runtime code resolves the authoritative authorProfile/authorName.
		"senderName":  authorName,
		"messageType": ref.Message.MessageType,
		"content":     ref.Message.Content,
		"timestamp":   ref.Message.CreatedAt.UTC().Format(time.RFC3339Nano),
	}
	if len(appMessages) == 1 {
		if profileMetadata := authorProfileMetadata(appMessage.EffectiveAuthorProfile()); len(profileMetadata) > 0 {
			metadata["authorProfile"] = profileMetadata
		}
		if sourceMetadata := contributionSourceMetadata(appMessage.Source); len(sourceMetadata) > 0 {
			metadata["source"] = sourceMetadata
		}
	}
	if attachments := appMessage.Attachments; len(attachments) > 0 {
		items := make([]map[string]any, 0, len(attachments))
		for _, attachment := range attachments {
			item := map[string]any{}
			if trimmed := strings.TrimSpace(attachment.ID); trimmed != "" {
				item["id"] = trimmed
			}
			if trimmed := strings.TrimSpace(attachment.Filename); trimmed != "" {
				item["filename"] = trimmed
			}
			if len(item) > 0 {
				items = append(items, item)
			}
		}
		if len(items) > 0 {
			metadata["attachments"] = items
		}
	}
	return metadata, nil
}

func (service ScopeAPIService) listVisible(ctx context.Context, request ScopeAPIHistoryRequest) (VisibilityFilterResult, error) {
	if err := service.requireDB(); err != nil {
		return VisibilityFilterResult{}, err
	}
	return service.visibility.ListVisibleMessages(ctx, service.repos.Messages, VisibilityFilterRequest{
		ViewerMemberID:      strings.TrimSpace(request.ViewerMemberID),
		ViewerType:          firstNonEmpty(request.ViewerType, ScopeActorHuman),
		ScopeID:             strings.TrimSpace(request.ScopeID),
		RequestKind:         firstNonEmpty(request.RequestKind, ScopeVisibilityRequestHistory),
		BeforeSeq:           request.BeforeSeq,
		Limit:               normalizeScopeAPILimit(request.Limit, 50),
		CandidateBatchLimit: request.CandidateBatchLimit,
		MaxCandidateBatches: request.MaxCandidateBatches,
	})
}

func (service ScopeAPIService) loadVisibleAuthorMemberIDs(ctx context.Context, workspaceID string, scopeID string, viewerMemberID string) ([]string, error) {
	if err := service.requireDB(); err != nil {
		return nil, err
	}
	rows, err := service.db.Query(
		ctx,
		scopeVisibleAuthorMemberIDsQuery.SQL,
		strings.TrimSpace(scopeID),
		strings.TrimSpace(viewerMemberID),
		strings.TrimSpace(workspaceID),
	)
	if err != nil {
		return nil, fmt.Errorf("load visible scope author member ids: %w", err)
	}
	defer rows.Close()

	memberIDs := make([]string, 0)
	for rows.Next() {
		var memberID string
		if err := rows.Scan(&memberID); err != nil {
			return nil, fmt.Errorf("scan visible scope author member id: %w", err)
		}
		if memberID = strings.TrimSpace(memberID); memberID != "" {
			memberIDs = append(memberIDs, memberID)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate visible scope author member ids: %w", err)
	}
	return memberIDs, nil
}

func (service ScopeAPIService) searchVisible(ctx context.Context, request ScopeAPISearchRequest, limit int) (VisibilityFilterResult, error) {
	authorFilter, err := NormalizeMessageAuthorFilter(request.AuthorFilter)
	if err != nil {
		return VisibilityFilterResult{}, err
	}
	request.AuthorFilter = authorFilter
	return searchVisibleWithLoader(ctx, service.visibility, request, limit, func(ctx context.Context, cursor MessageSearchCursor, batchLimit int) ([]ScopeMessageVisibilityCandidate, error) {
		return service.repos.Messages.ListSearchVisibilityCandidates(ctx, strings.TrimSpace(request.WorkspaceID), strings.TrimSpace(request.ViewerMemberID), strings.TrimSpace(request.ScopeID), strings.TrimSpace(request.Query), request.AuthorFilter, cursor, batchLimit)
	})
}

type scopeSearchCandidateLoader func(context.Context, MessageSearchCursor, int) ([]ScopeMessageVisibilityCandidate, error)

func searchVisibleWithLoader(ctx context.Context, visibility VisibilityFilter, request ScopeAPISearchRequest, limit int, load scopeSearchCandidateLoader) (VisibilityFilterResult, error) {
	viewerMemberID := strings.TrimSpace(request.ViewerMemberID)
	workspaceID := strings.TrimSpace(request.WorkspaceID)
	scopeID := strings.TrimSpace(request.ScopeID)
	query := strings.TrimSpace(request.Query)
	if workspaceID == "" || viewerMemberID == "" || query == "" {
		return VisibilityFilterResult{}, invalidArgument("scope search workspace, viewer, and query are required")
	}
	cursor, err := normalizeScopeAPISearchCursor(request, scopeID)
	if err != nil {
		return VisibilityFilterResult{}, err
	}
	result := VisibilityFilterResult{Messages: make([]VisibleMessageRef, 0, limit)}
	batchLimit := visibilityCandidateBatchLimit(limit, request.CandidateBatchLimit)
	maxBatches := visibilityMaxCandidateBatches(request.MaxCandidateBatches)
	candidateLimit := batchLimit * maxBatches
	candidates, err := load(ctx, cursor, candidateLimit)
	if err != nil {
		return VisibilityFilterResult{}, err
	}
	if len(candidates) == 0 {
		return result, nil
	}
	sortSearchVisibilityCandidates(candidates, scopeID != "")
	seenMessageIDs := make(map[string]struct{}, limit)
	lastScannedCursor := MessageSearchCursor{}
	viewerType := firstNonEmpty(request.ViewerType, ScopeActorHuman)
	for i, candidate := range candidates {
		candidateCursor := searchCandidateCursor(candidate.Message, scopeID, candidate.SearchRank)
		lastScannedCursor = candidateCursor
		messageID := strings.TrimSpace(candidate.Message.ID)
		if _, ok := seenMessageIDs[messageID]; ok && messageID != "" {
			continue
		}
		if viewerType == ScopeActorHuman && !IsHumanVisibleScopeMessage(candidate.Message) {
			continue
		}
		visible, ref := visibility.visibleCandidate(candidate, VisibilityFilterRequest{
			ViewerMemberID: viewerMemberID,
			ViewerType:     viewerType,
			ScopeID:        scopeID,
			RequestKind:    ScopeVisibilityRequestSearch,
			Limit:          limit,
		})
		if !visible {
			continue
		}
		if len(result.Messages) >= limit {
			result.HasMore = true
			if len(candidates) == candidateLimit && i == len(candidates)-1 {
				result.CandidateBudgetExhausted = true
			}
			break
		}
		if messageID != "" {
			seenMessageIDs[messageID] = struct{}{}
		}
		result.Messages = append(result.Messages, ref)
		result.NextCursor = candidateCursor.LegacySeq()
		result.NextSearchCursor = candidateCursor
		if len(result.Messages) >= limit {
			result.HasMore = true
		}
	}
	if len(result.Messages) < limit && len(candidates) == candidateLimit {
		result.CandidateBudgetExhausted = true
		result.HasMore = true
		if !lastScannedCursor.IsZero() {
			result.NextCursor = lastScannedCursor.LegacySeq()
			result.NextSearchCursor = lastScannedCursor
		}
	}
	return result, nil
}

func authorProfileMetadata(profile AuthorProfile) map[string]any {
	profile = profile.Normalize()
	if !profile.Valid() {
		return nil
	}
	return map[string]any{
		"actorType":   profile.ActorType,
		"principalId": profile.PrincipalID,
		"memberId":    profile.MemberID,
		"targetId":    profile.TargetID,
		"displayName": profile.DisplayName,
		"avatarUrl":   profile.AvatarURL,
		"serviceKind": profile.ServiceKind,
		"serviceSlug": profile.ServiceSlug,
		"badge":       profile.Badge,
	}
}

// contributionSourceMetadata serializes a typed contribution source into the
// reply-thread-root metadata wire shape. It mirrors the browser/daemon
// ContributionSource DTO field names so Desktop parses the same typed source
// facts. Source is provenance, never authorization authority.
func contributionSourceMetadata(source ContributionSource) map[string]any {
	source = source.Normalize()
	if source.IsZero() {
		return nil
	}
	return map[string]any{
		"kind":      source.Kind,
		"refType":   source.RefType,
		"refId":     source.RefID,
		"runId":     source.RunID,
		"eventId":   source.EventID,
		"eventType": source.EventType,
		"label":     source.Label,
	}
}

func (service ScopeAPIService) historyMessagesFromVisibleRefs(ctx context.Context, workspaceID string, refs []VisibleMessageRef) ([]HistoryMessage, error) {
	presentations, attachmentsByMessageID, err := service.loadVisibleRefPresentation(ctx, workspaceID, refs)
	if err != nil {
		return nil, err
	}
	threadShareCardTranscripts, err := service.loadThreadShareCardTranscripts(ctx, workspaceID, refs)
	if err != nil {
		return nil, err
	}
	messages := make([]HistoryMessage, 0, len(refs))
	for _, ref := range refs {
		metadata, err := DecodeMessageMetadata(ref.Message.Metadata)
		if err != nil {
			return nil, err
		}
		presentation := presentations[strings.TrimSpace(ref.Message.AuthorMemberID)]
		authorProfile := presentation.AuthorProfile()
		messages = append(messages, HistoryMessage{
			Seq:           ref.Message.Seq,
			TargetKey:     ref.Message.ScopeID,
			TargetSeq:     ref.Message.Seq,
			ID:            ref.Message.ID,
			AuthorProfile: authorProfile,
			Content:       FormatThreadShareCardTranscript(ref.Message, threadShareCardTranscripts[strings.TrimSpace(ref.Message.ID)]),
			Metadata:      metadata,
			CreatedAt:     ref.Message.CreatedAt,
			Attachments:   attachmentsByMessageID[ref.Message.ID],
		})
	}
	if err := AttachContributionSourcesForHistoryMessages(ctx, service.db, workspaceID, messages); err != nil {
		return nil, err
	}
	return messages, nil
}

func (service ScopeAPIService) appMessagesFromVisibleRefs(ctx context.Context, workspaceID string, refs []VisibleMessageRef) ([]AppMessage, error) {
	presentations, attachmentsByMessageID, err := service.loadVisibleRefPresentation(ctx, workspaceID, refs)
	if err != nil {
		return nil, err
	}
	routeContexts, err := loadAppMessageRouteContexts(ctx, service.db, workspaceID, visibleRefScopeIDs(refs))
	if err != nil {
		return nil, err
	}
	messages := make([]AppMessage, 0, len(refs))
	for _, ref := range refs {
		metadata, err := DecodeMessageMetadata(ref.Message.Metadata)
		if err != nil {
			return nil, err
		}
		presentation := presentations[strings.TrimSpace(ref.Message.AuthorMemberID)]
		appMessage := AppMessage{
			ID:               ref.Message.ID,
			Seq:              ref.Message.Seq,
			ChannelID:        ref.Message.ScopeID,
			Content:          ref.Message.Content,
			Metadata:         metadata,
			Attachments:      attachmentsByMessageID[ref.Message.ID],
			CreatedAt:        ref.Message.CreatedAt,
			EditedAt:         ref.Message.EditedAt,
			EditedByMemberID: ref.Message.EditedByMemberID,
			EditRevision:     ref.Message.EditRevision,
			MessageType:      ref.Message.MessageType,
		}
		if routeContext, ok := routeContexts[strings.TrimSpace(ref.Message.ScopeID)]; ok {
			applyAppMessageRouteContext(&appMessage, routeContext)
		}
		appMessage.ApplyAuthorProfile(presentation.AuthorProfile())
		messages = append(messages, appMessage)
	}
	if err := AttachContributionSourcesForAppMessages(ctx, service.db, workspaceID, messages); err != nil {
		return nil, err
	}
	return messages, nil
}

func sortSearchVisibilityCandidates(candidates []ScopeMessageVisibilityCandidate, scoped bool) {
	sort.SliceStable(candidates, func(i int, j int) bool {
		if candidates[i].SearchRank != candidates[j].SearchRank {
			return candidates[i].SearchRank < candidates[j].SearchRank
		}
		left := candidates[i].Message
		right := candidates[j].Message
		if scoped {
			if left.Seq != right.Seq {
				return left.Seq > right.Seq
			}
			return left.ID < right.ID
		}
		if left.WorkspaceGlobalSeq != right.WorkspaceGlobalSeq {
			return left.WorkspaceGlobalSeq > right.WorkspaceGlobalSeq
		}
		if left.ScopeID != right.ScopeID {
			return left.ScopeID < right.ScopeID
		}
		if left.Seq != right.Seq {
			return left.Seq > right.Seq
		}
		return left.ID < right.ID
	})
}

func (service ScopeAPIService) deliveryMessageStartsReplyThread(ctx context.Context, workspaceID string, messageID string) (bool, error) {
	roots, err := service.loadReplyThreadRootScopeIDs(ctx, workspaceID, []string{messageID})
	if err != nil {
		return false, err
	}
	return strings.TrimSpace(roots[strings.TrimSpace(messageID)]) != "", nil
}

func (service ScopeAPIService) loadReplyThreadRootScopeIDs(ctx context.Context, workspaceID string, messageIDs []string) (map[string]string, error) {
	normalizedMessageIDs := normalizeNonEmptyStrings(messageIDs)
	if len(normalizedMessageIDs) == 0 {
		return map[string]string{}, nil
	}
	requestedMessageIDsCTE, requestedMessageIDArgs, _, err := coredb.BuildStringValuesCTE("requested_message_ids", "message_id", normalizedMessageIDs, 2)
	if err != nil {
		return nil, fmt.Errorf("build reply-thread root message query: %w", err)
	}
	rows, err := service.db.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT requested.message_id, child.id
FROM requested_message_ids requested
JOIN scope_messages root_message
  ON root_message.id = requested.message_id
 AND root_message.workspace_id = $1
JOIN scopes child
  ON child.workspace_id = $1
 AND child.parent_scope_id = root_message.scope_id
 AND child.root_message_id = root_message.id
 AND child.type = 'thread'
 AND child.status = 'active'
 AND child.archived_at IS NULL
ORDER BY requested.message_id ASC, child.id ASC
`, requestedMessageIDsCTE), append([]any{strings.TrimSpace(workspaceID)}, requestedMessageIDArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("load reply-thread root scopes: %w", err)
	}
	defer rows.Close()

	result := make(map[string]string, len(normalizedMessageIDs))
	for rows.Next() {
		var messageID string
		var threadScopeID string
		if err := rows.Scan(&messageID, &threadScopeID); err != nil {
			return nil, fmt.Errorf("scan reply-thread root scope: %w", err)
		}
		messageID = strings.TrimSpace(messageID)
		if messageID == "" {
			continue
		}
		if _, exists := result[messageID]; exists {
			continue
		}
		result[messageID] = strings.TrimSpace(threadScopeID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate reply-thread root scopes: %w", err)
	}
	return result, nil
}

func (service ScopeAPIService) loadVisibleRefPresentation(ctx context.Context, workspaceID string, refs []VisibleMessageRef) (map[string]scopeMemberPresentation, map[string][]attachment.AppAttachment, error) {
	memberIDs := make([]string, 0, len(refs))
	messageIDs := make([]string, 0, len(refs))
	for _, ref := range refs {
		memberIDs = append(memberIDs, ref.Message.AuthorMemberID)
		messageIDs = append(messageIDs, ref.Message.ID)
	}
	presentations, err := loadScopeMemberPresentations(ctx, service.db, workspaceID, memberIDs)
	if err != nil {
		return nil, nil, err
	}
	attachments, err := service.loadAttachments(ctx, workspaceID, messageIDs)
	if err != nil {
		return nil, nil, err
	}
	return presentations, attachments, nil
}

func (service ScopeAPIService) loadAttachments(ctx context.Context, workspaceID string, messageIDs []string) (map[string][]attachment.AppAttachment, error) {
	if service.store == nil {
		return map[string][]attachment.AppAttachment{}, nil
	}
	return service.store.loadAttachmentsForMessages(ctx, workspaceID, messageIDs)
}

func (service ScopeAPIService) requireDB() error {
	if service.db == nil {
		return fmt.Errorf("scope api db is nil")
	}
	return nil
}

func (store MessageStore) ListSearchVisibilityCandidates(ctx context.Context, workspaceID string, viewerMemberID string, scopeID string, query string, authorFilter MessageAuthorFilter, cursor MessageSearchCursor, limit int) ([]ScopeMessageVisibilityCandidate, error) {
	if err := store.requireExecer(); err != nil {
		return nil, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	viewerMemberID = strings.TrimSpace(viewerMemberID)
	query = strings.TrimSpace(query)
	if workspaceID == "" || viewerMemberID == "" || query == "" {
		return nil, invalidArgument("search visibility workspace, viewer, and query are required")
	}
	if limit <= 0 {
		limit = 50
	}
	cursor, err := normalizeMessageSearchCursor(cursor)
	if err != nil {
		return nil, err
	}
	searchSQL, args, err := buildScopeSearchVisibilityQuery(workspaceID, viewerMemberID, scopeID, query, authorFilter, cursor, limit)
	if err != nil {
		return nil, err
	}
	rows, err := store.execer.Query(ctx, searchSQL, args...)
	if err != nil {
		return nil, fmt.Errorf("list scope search visibility candidates: %w", err)
	}
	defer rows.Close()
	return scanScopeMessageVisibilityCandidates(rows, true)
}

func buildScopeSearchVisibilityQuery(workspaceID string, viewerMemberID string, scopeID string, query string, authorFilter MessageAuthorFilter, cursor MessageSearchCursor, limit int) (string, []any, error) {
	return buildScopeSearchVisibilityQueryWithMatchCondition(workspaceID, viewerMemberID, scopeID, query, authorFilter, cursor, limit, BuildTiDBFTSMatchCondition(query, "docs.content"))
}

func buildScopeSearchVisibilityQueryWithMatchCondition(workspaceID string, viewerMemberID string, scopeID string, query string, authorFilter MessageAuthorFilter, cursor MessageSearchCursor, limit int, matchCondition string) (string, []any, error) {
	filterSQL, filterArgs, err := buildMessageAuthorFilterSQL(authorFilter, 9)
	if err != nil {
		return "", nil, err
	}
	authorDocFilterSQL := "TRUE"
	if !authorFilter.IsZero() {
		authorDocFilterSQL = `EXISTS (
      SELECT 1
      FROM scope_messages m
      WHERE m.id = docs.message_id
        AND m.workspace_id = docs.workspace_id
        AND (` + filterSQL + `)
    )`
	}
	searchSQL := strings.Replace(scopeSearchVisibilityCandidatesQuery.SQL, "__MATCH_CONDITION__", matchCondition, 1)
	searchSQL = strings.Replace(searchSQL, "__AUTHOR_DOC_FILTER__", authorDocFilterSQL, 1)
	rankSQL, rankArgs, limitPlaceholder := buildScopeSearchRankSQL(query, 9+len(filterArgs))
	searchSQL = strings.Replace(searchSQL, "__SEARCH_RANK__", rankSQL, 1)
	searchSQL = strings.Replace(searchSQL, "__LIMIT_PLACEHOLDER__", limitPlaceholder, 1)
	args := []any{workspaceID, viewerMemberID, strings.TrimSpace(scopeID), cursor.GlobalSeq, cursor.ScopeID, cursor.Seq, cursor.MessageID, cursor.Rank}
	args = append(args, filterArgs...)
	args = append(args, rankArgs...)
	args = append(args, limit)
	return searchSQL, args, nil
}

func buildMessageAuthorFilterSQL(filter MessageAuthorFilter, firstPlaceholder int) (string, []any, error) {
	if filter.IsZero() {
		return "TRUE", nil, nil
	}
	placeholder := fmt.Sprintf("$%d", firstPlaceholder)
	switch filter.Kind {
	case MessageAuthorFilterMember:
		return `m.author_type = 'human' AND EXISTS (
    SELECT 1
    FROM members author_member
    WHERE author_member.workspace_id = m.workspace_id
      AND author_member.id = ` + placeholder + `
      AND author_member.type = 'human'
      AND author_member.id = m.author_member_id
  )`, []any{filter.ID}, nil
	case MessageAuthorFilterAgent:
		return `m.author_type = 'agent' AND EXISTS (
    SELECT 1
    FROM agents author_agent
    WHERE author_agent.workspace_id = m.workspace_id
      AND author_agent.id = ` + placeholder + `
      AND author_agent.member_id = m.author_member_id
  )`, []any{filter.ID}, nil
	case MessageAuthorFilterMemberAgents:
		return `m.author_type = 'agent' AND EXISTS (
    SELECT 1
    FROM members owner_member
    JOIN machines owned_machine
      ON owned_machine.workspace_id = owner_member.workspace_id
     AND owned_machine.created_by_user_id = owner_member.user_id
    JOIN agents owned_agent
      ON owned_agent.workspace_id = owned_machine.workspace_id
     AND owned_agent.machine_id = owned_machine.id
    WHERE owner_member.workspace_id = m.workspace_id
      AND owner_member.id = ` + placeholder + `
      AND owner_member.type = 'human'
      AND owned_agent.member_id = m.author_member_id
  )`, []any{filter.ID}, nil
	default:
		return "", nil, badRequest("invalid author kind")
	}
}

func (store SearchStore) RequireReadReady(ctx context.Context, workspaceID string, scopeID string) error {
	if err := store.requireExecer(); err != nil {
		return err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	if workspaceID == "" {
		return invalidArgument("scope search readiness workspace is required")
	}
	var readiness ScopeSearchReadiness
	readiness.WorkspaceID = workspaceID
	readiness.ScopeID = strings.TrimSpace(scopeID)
	readiness.ProjectionName = TiDBMessageSearchProjectionName
	err := store.execer.QueryRow(ctx, scopeSearchReadinessQuery.SQL, workspaceID, readiness.ScopeID, readiness.ProjectionName).Scan(
		&readiness.MigrationStatus,
		&readiness.BackfillStatus,
		&readiness.FinalizeStatus,
		&readiness.ReadReady,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return requestError{kind: ErrMessageSearchUnavailable, message: "scope message search is unavailable because the scope projection state is missing"}
		}
		return fmt.Errorf("load scope message search readiness: %w", err)
	}
	if !readiness.Ready() {
		return requestError{kind: ErrMessageSearchUnavailable, message: "scope message search is unavailable until the scope projection is ready"}
	}
	return nil
}

func (store MessageStore) LoadVisibilityCandidatesByMessageID(ctx context.Context, workspaceID string, viewerMemberID string, messageID string) ([]ScopeMessageVisibilityCandidate, error) {
	if err := store.requireExecer(); err != nil {
		return nil, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	viewerMemberID = strings.TrimSpace(viewerMemberID)
	messageID = strings.TrimSpace(messageID)
	if workspaceID == "" || viewerMemberID == "" || messageID == "" {
		return nil, invalidArgument("message read check workspace, viewer, and message are required")
	}
	rows, err := store.execer.Query(ctx, scopeMessageVisibilityByIDQuery.SQL, workspaceID, viewerMemberID, messageID, 20)
	if err != nil {
		return nil, fmt.Errorf("load scope message visibility candidate: %w", err)
	}
	defer rows.Close()
	return scanScopeMessageVisibilityCandidates(rows, false)
}

func (store ScopeStore) ListMemberDisplayRows(ctx context.Context, workspaceID string, scopeID string, viewerMemberID string) ([]ScopeMemberDisplayRow, error) {
	page, err := store.ListMemberDisplayRowsPage(ctx, ScopeMemberListRequest{
		WorkspaceID:    workspaceID,
		ScopeID:        scopeID,
		ViewerMemberID: viewerMemberID,
		Limit:          100,
	})
	if err != nil {
		return nil, err
	}
	return page.Members, nil
}

func (store ScopeStore) ListMemberDisplayRowsPage(ctx context.Context, request ScopeMemberListRequest) (ScopeMemberListPage, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMemberListPage{}, err
	}
	if strings.TrimSpace(request.WorkspaceID) == "" || strings.TrimSpace(request.ScopeID) == "" || strings.TrimSpace(request.ViewerMemberID) == "" {
		return ScopeMemberListPage{}, invalidArgument("scope member display workspace, scope, and viewer are required")
	}
	limit := request.Limit
	switch {
	case limit <= 0:
		limit = 20
	case limit > 100:
		limit = 100
	}
	offset, err := strconv.Atoi(firstNonEmpty(strings.TrimSpace(request.Cursor), "0"))
	if err != nil || offset < 0 {
		return ScopeMemberListPage{}, invalidArgument("invalid scope members cursor")
	}
	args := []any{strings.TrimSpace(request.WorkspaceID), strings.TrimSpace(request.ScopeID), strings.TrimSpace(request.ViewerMemberID)}
	filters := []string{}
	if query := strings.TrimSpace(request.Query); query != "" {
		args = append(args, query)
		filters = append(filters, fmt.Sprintf("(sm.member_id = $%[1]d OR m.type = $%[1]d OR COALESCE(a.id, '') = $%[1]d OR COALESCE(a.name, '') = $%[1]d OR COALESCE(u.id, '') = $%[1]d OR COALESCE(u.name, '') = $%[1]d)", len(args)))
	}
	if memberType := strings.TrimSpace(request.MemberType); memberType != "" {
		args = append(args, memberType)
		filters = append(filters, fmt.Sprintf("m.type = $%d", len(args)))
	}
	if visibility := strings.TrimSpace(request.Visibility); visibility != "" {
		args = append(args, visibility)
		filters = append(filters, fmt.Sprintf("sm.visibility = $%d", len(args)))
	}
	args = append(args, limit+1, offset)
	query := scopeMemberDisplayRowsQuery.SQL
	if len(filters) > 0 {
		query = strings.Replace(query, "ORDER BY sm.visibility ASC", "  AND "+strings.Join(filters, "\n  AND ")+"\nORDER BY sm.visibility ASC", 1)
	}
	query += fmt.Sprintf("\nLIMIT $%d OFFSET $%d", len(args)-1, len(args))
	rows, err := store.execer.Query(ctx, query, args...)
	if err != nil {
		return ScopeMemberListPage{}, fmt.Errorf("list scope member display rows: %w", err)
	}
	defer rows.Close()
	members := make([]ScopeMemberDisplayRow, 0)
	for rows.Next() {
		var row ScopeMemberDisplayRow
		if err := rows.Scan(&row.MemberID, &row.MemberType, &row.PublicID, &row.DisplayName, &row.Visibility, &row.OwnerMemberID); err != nil {
			return ScopeMemberListPage{}, fmt.Errorf("scan scope member display row: %w", err)
		}
		members = append(members, row)
	}
	if err := rows.Err(); err != nil {
		return ScopeMemberListPage{}, fmt.Errorf("iterate scope member display rows: %w", err)
	}
	hasMore := len(members) > limit
	if hasMore {
		members = members[:limit]
	}
	nextCursor := ""
	if hasMore {
		nextCursor = strconv.Itoa(offset + limit)
	}
	return ScopeMemberListPage{Members: members, Limit: limit, HasMore: hasMore, NextCursor: nextCursor}, nil
}

func loadScopePresentations(ctx context.Context, queryer coredb.Queryer, workspaceID string, scopeIDs []string) (map[string]scopePresentation, error) {
	ids := dedupeTrimmed(scopeIDs)
	result := make(map[string]scopePresentation, len(ids))
	if len(ids) == 0 {
		return result, nil
	}
	placeholders, args := scopeAPIPlaceholders(2, ids)
	args = append([]any{strings.TrimSpace(workspaceID)}, args...)
	query := strings.Replace(scopePresentationsByIDQuery.SQL, "__SCOPE_IDS__", placeholders, 1)
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load scope presentations: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var row scopePresentation
		if err := rows.Scan(&row.ID, &row.Type, &row.Name, &row.DisplayName, &row.ParentID, &row.ParentType, &row.ParentName, &row.ParentDisplayName); err != nil {
			return nil, fmt.Errorf("scan scope presentation: %w", err)
		}
		result[row.ID] = row
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate scope presentations: %w", err)
	}
	for _, id := range ids {
		if _, ok := result[id]; !ok {
			result[id] = scopePresentation{ID: id, Type: "scope", Name: id, DisplayName: id}
		}
	}
	return result, nil
}

func loadScopeMemberPresentations(ctx context.Context, queryer coredb.Queryer, workspaceID string, memberIDs []string) (map[string]scopeMemberPresentation, error) {
	ids := dedupeTrimmed(memberIDs)
	result := make(map[string]scopeMemberPresentation, len(ids))
	if len(ids) == 0 {
		return result, nil
	}
	placeholders, args := scopeAPIPlaceholders(2, ids)
	args = append([]any{strings.TrimSpace(workspaceID)}, args...)
	query := strings.Replace(scopeMemberPresentationsByIDQuery.SQL, "__MEMBER_IDS__", placeholders, 1)
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load scope member presentations: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var row scopeMemberPresentation
		if err := rows.Scan(
			&row.MemberID,
			&row.MemberType,
			&row.PublicID,
			&row.PrincipalID,
			&row.TargetID,
			&row.DisplayName,
			&row.AvatarURL,
			&row.ServiceKind,
			&row.ServiceSlug,
		); err != nil {
			return nil, fmt.Errorf("scan scope member presentation: %w", err)
		}
		result[row.MemberID] = row
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate scope member presentations: %w", err)
	}
	for _, id := range ids {
		if _, ok := result[id]; !ok {
			result[id] = scopeMemberPresentation{MemberID: id, MemberType: "service", PublicID: id, PrincipalID: id, TargetID: id, DisplayName: id}
		}
	}
	return result, nil
}

func normalizeScopeAPILimit(limit int, fallback int) int {
	if limit <= 0 {
		limit = fallback
	}
	if limit <= 0 {
		limit = 50
	}
	if limit > 200 {
		return 200
	}
	return limit
}

func dedupeTrimmed(values []string) []string {
	seen := make(map[string]struct{}, len(values))
	result := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		result = append(result, trimmed)
	}
	return result
}

func scopeAPIPlaceholders(start int, values []string) (string, []any) {
	placeholders := make([]string, len(values))
	args := make([]any, len(values))
	for index, value := range values {
		placeholders[index] = "$" + strconv.Itoa(start+index)
		args[index] = value
	}
	return strings.Join(placeholders, ", "), args
}