package message

import (
	"context"
	"fmt"
	"os"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
	"github.com/tidbcloud/AgentTeam/internal/store/testfixture"
)

func TestEnsureDMThreadCreatesAndReusesThread(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 3, 19, 13, 0, 0, 0, time.UTC)

	const (
		workspaceID = "wspmsg1"
		userID      = "usrmsg1"
		humanID     = "human01"
		peerUserID  = "usrmsg2"
		peerID      = "human02"
		agentID     = "agent01"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "human-1@example.com", now)
	insertMessagingUser(t, ctx, pool, peerUserID, "human-2@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, humanID, workspaceID, userID, now)
	insertMessagingHumanMember(t, ctx, pool, peerID, workspaceID, peerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, agentID, workspaceID, now)

	actor := MemberActor{WorkspaceID: workspaceID, MemberID: agentID}

	first, err := EnsureDMThread(ctx, db, actor, peerID)
	require.NoError(t, err)
	require.NotEmpty(t, first.DMThreadID)
	require.NotEmpty(t, first.ThreadID)
	require.Equal(t, first.DMThreadID, first.ThreadID)
	require.Equal(t, first.DMThreadID, first.ChannelID)
	require.Equal(t, peerID, first.PeerMemberID)

	second, err := EnsureDMThread(ctx, db, actor, peerID)
	require.NoError(t, err)
	require.Equal(t, first.DMThreadID, second.DMThreadID)
	require.Equal(t, first.ThreadID, second.ThreadID)
	require.Equal(t, first.ChannelID, second.ChannelID)

	var scopeCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM scopes
WHERE workspace_id = $1
  AND id = $2
  AND type = 'dm'
  AND status = 'active'
  AND archived_at IS NULL
`, workspaceID, first.DMThreadID).Scan(&scopeCount))
	require.Equal(t, 1, scopeCount)

	var membershipCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM scope_memberships
WHERE scope_id = $1
  AND member_id IN ($2, $3)
  AND removed_at IS NULL
  AND archived_at IS NULL
  AND effective_to_seq IS NULL
`, first.DMThreadID, agentID, peerID).Scan(&membershipCount))
	require.Equal(t, 2, membershipCount)

}

func TestProjectionHelpersListChannelsAndOpenDM(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 3, 19, 13, 15, 0, 0, time.UTC)

	const (
		workspaceID = "wspmsgp"
		userID      = "usrmsgp"
		memberID    = "human10"
		peerUserID  = "usrmsgq"
		peerMember  = "human11"
		serviceID   = "svcmsgp1"
		serviceDef  = "svcdefmsgp1"
		serviceInst = "svcinstmsgp1"
		serviceRow  = "svcmemmsgp1"
		channelID   = "chnmsgp1"
		channelThr  = "thrmsgp1"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "projection-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, peerUserID, "projection-peer@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingHumanMember(t, ctx, pool, peerMember, workspaceID, peerUserID, now)
	insertMessagingServiceMember(t, ctx, pool, serviceID, workspaceID, "GitHub", now)
	insertMessagingAutomationServiceActor(t, ctx, pool, messagingAutomationServiceActor{
		WorkspaceID:         workspaceID,
		CreatorMemberID:     memberID,
		DefinitionID:        serviceDef,
		InstallationID:      serviceInst,
		ServiceMemberRowID:  serviceRow,
		MemberID:            serviceID,
		Slug:                "github-projection-test",
		Handle:              "github",
		DefinitionName:      "GitHub",
		MemberDisplayName:   "GitHub",
		DefinitionAvatarURL: "https://example.test/github-definition.png",
		MemberAvatarURL:     "https://example.test/github-installed.png",
		Now:                 now,
	})
	insertMessagingChannel(
		t,
		ctx,
		pool,
		channelID,
		workspaceID,
		"general",
		memberID,
		channelThr,
		now,
	)
	mustExecMessaging(t, ctx, pool, `
UPDATE scope_channel_profiles
SET display_name = 'General Discussion'
WHERE scope_id = $1
`, channelID)
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, serviceID, "public", "", "active", 0, 0, now))

	channels, err := ListChannelsForMember(ctx, db, memberID, workspaceID)
	require.NoError(t, err)
	require.Len(t, channels, 1)
	require.Equal(t, channelID, channels[0].ChannelID)
	require.Equal(t, "general", channels[0].Slug)
	require.Equal(t, "General Discussion", channels[0].DisplayName)
	require.Equal(t, "active", channels[0].SubscriptionState)
	require.True(t, channels[0].Joined)
	require.Nil(t, channels[0].PinnedAt)

	pinnedAt := now.Add(2 * time.Minute)
	require.NoError(t, upsertConversationPinnedState(
		ctx,
		pool,
		memberID,
		channelID,
		&pinnedAt,
		pinnedAt,
	))
	pinnedChannels, err := ListChannelsForMember(ctx, db, memberID, workspaceID)
	require.NoError(t, err)
	require.Len(t, pinnedChannels, 1)
	require.NotNil(t, pinnedChannels[0].PinnedAt)
	require.True(t, pinnedChannels[0].PinnedAt.Equal(pinnedAt))

	require.NoError(t, UpsertScopeNotificationState(
		ctx,
		pool,
		memberID,
		channelID,
		HumanScopeSubscriptionStateMute,
		now.Add(time.Minute),
	))
	mutedChannels, err := ListChannelsForMember(ctx, db, memberID, workspaceID)
	require.NoError(t, err)
	require.Len(t, mutedChannels, 1)
	require.Equal(t, HumanScopeSubscriptionStateMute, mutedChannels[0].SubscriptionState)

	members, err := ListChannelMembers(ctx, db, channelID)
	require.NoError(t, err)
	require.Len(t, members.Humans, 1)
	require.Empty(t, members.Agents)
	require.Len(t, members.Services, 1)
	require.Equal(t, userID, members.Humans[0].ID)
	require.Equal(t, memberID, members.Humans[0].Name)
	require.Equal(t, serviceID, members.Services[0].ID)
	require.Equal(t, serviceID, members.Services[0].MemberID)
	require.Equal(t, "github", members.Services[0].Name)
	require.Equal(t, "GitHub", members.Services[0].DisplayName)
	require.Equal(t, "https://example.test/github-installed.png", members.Services[0].AvatarURL)

	var serviceMemberCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_members
WHERE member_id = $1
`, serviceID).Scan(&serviceMemberCount))
	require.Equal(t, 1, serviceMemberCount)

	mustExecMessaging(t, ctx, pool, `
UPDATE service_members
SET avatar_url = ''
WHERE id = $1
`, serviceRow)
	members, err = ListChannelMembers(ctx, db, channelID)
	require.NoError(t, err)
	require.Len(t, members.Services, 1)
	require.Equal(t, "https://example.test/github-definition.png", members.Services[0].AvatarURL)

	dm, err := OpenDMChannelForMember(ctx, db, MemberActor{WorkspaceID: workspaceID, MemberID: memberID}, peerMember)
	require.NoError(t, err)
	require.Equal(t, "user", dm.PeerType)
	require.Equal(t, peerUserID, dm.PeerID)
	require.Equal(t, peerMember, dm.PeerName)

	dms, err := ListDMChannelsForMember(ctx, db, memberID, workspaceID)
	require.NoError(t, err)
	require.Len(t, dms, 1)
	require.Equal(t, dm.ID, dms[0].ID)
	require.Equal(t, peerMember, dms[0].PeerName)
}

func TestEnsureScopeMembershipReusesMigratedOpenRows(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 18, 16, 10, 0, 0, time.UTC)

	const (
		workspaceID = "wsp_scope_member_reuse"
		userID      = "usr_scope_member_reuse"
		memberID    = "mem_scope_member_reuse"
		channelID   = "chn_scope_member_reuse"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "scope-member-reuse@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "all", memberID, "", now)

	ensureAt := now.Add(time.Minute)
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, memberID, "public", "", "active", 5, 50, ensureAt))
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, memberID, "public", "", "active", 5, 50, ensureAt))

	var membershipCount int
	var lastReadSeq int64
	var lastReadGlobalSeq int64
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*), MAX(last_read_seq), MAX(last_read_global_seq)
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND effective_to_seq IS NULL
  AND removed_at IS NULL
  AND archived_at IS NULL
`, channelID, memberID).Scan(&membershipCount, &lastReadSeq, &lastReadGlobalSeq))
	require.Equal(t, 1, membershipCount)
	require.Equal(t, int64(5), lastReadSeq)
	require.Equal(t, int64(50), lastReadGlobalSeq)

	var notificationCount int
	var notificationState string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*), MAX(state)
FROM scope_notification_settings
WHERE scope_id = $1
  AND member_id = $2
`, channelID, memberID).Scan(&notificationCount, &notificationState))
	require.Equal(t, 1, notificationCount)
	require.Equal(t, "active", notificationState)
}

func TestEnsureWorkflowScopeParticipantMembershipPreservesExistingAuthority(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 8, 7, 9, 30, 0, 0, time.UTC)

	const (
		workspaceID      = "wsp_workflow_participant"
		ownerUserID      = "usr_workflow_participant"
		ownerMemberID    = "mem_workflow_owner"
		creatorAgentID   = "mem_workflow_creator_agent"
		selectedAgentID  = "mem_workflow_selected_agent"
		workflowScopeID  = "wfs_workflow_participant"
		workflowIdentity = "workflow:plan_workflow_participant"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "workflow-participant@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, creatorAgentID, workspaceID, now)
	insertMessagingAgentMember(t, ctx, pool, selectedAgentID, workspaceID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scopes (
    id, workspace_id, type, identity_key, status, created_by_member_id,
    created_at, updated_at
)
VALUES ($1, $2, 'workflow', $3, 'active', $4, $5, $5)
`, workflowScopeID, workspaceID, workflowIdentity, creatorAgentID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id,
    last_read_seq, last_read_global_seq, last_activity_global_seq,
    owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq,
    created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'private', $3, 0, 0, 0, 0, 1, NULL, $4, $4, NULL, NULL)
`, workflowScopeID, creatorAgentID, ownerMemberID, now)

	participatedAt := now.Add(time.Minute)
	require.NoError(t, EnsureWorkflowScopeParticipantMembership(
		ctx, pool, workflowScopeID, creatorAgentID, participatedAt,
	))
	require.NoError(t, EnsureWorkflowScopeParticipantMembership(
		ctx, pool, workflowScopeID, selectedAgentID, participatedAt,
	))

	var creatorVisibility, creatorOwner string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT visibility, COALESCE(owner_member_id, '')
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND effective_to_seq IS NULL
  AND removed_at IS NULL
  AND archived_at IS NULL
`, workflowScopeID, creatorAgentID).Scan(&creatorVisibility, &creatorOwner))
	require.Equal(t, "private", creatorVisibility)
	require.Equal(t, ownerMemberID, creatorOwner)

	var selectedVisibility, selectedOwner string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT visibility, COALESCE(owner_member_id, '')
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND effective_to_seq IS NULL
  AND removed_at IS NULL
  AND archived_at IS NULL
`, workflowScopeID, selectedAgentID).Scan(&selectedVisibility, &selectedOwner))
	require.Equal(t, "public", selectedVisibility)
	require.Empty(t, selectedOwner)
}

func TestListChannelsForMemberDeduplicatesOpenMembershipVersions(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 18, 16, 20, 0, 0, time.UTC)

	const (
		workspaceID = "wsp_channel_dedupe"
		userID      = "usr_channel_dedupe"
		memberID    = "mem_channel_dedupe"
		channelID   = "chn_channel_dedupe"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "channel-dedupe@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "all", memberID, "", now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 1, NULL, $3, $3, NULL, NULL)
`, channelID, memberID, now.Add(time.Minute))

	channels, err := ListChannelsForMember(ctx, pool, memberID, workspaceID)
	require.NoError(t, err)
	require.Len(t, channels, 1)
	require.Equal(t, channelID, channels[0].ChannelID)
	require.Equal(t, "all", channels[0].Slug)
}

func TestProjectionHelpersUseScopeTopology(t *testing.T) {
	t.Parallel()

	projectionBytes, err := os.ReadFile("projections.go")
	require.NoError(t, err)
	projectionSource := string(projectionBytes)
	for _, signature := range []string{
		"func ListChannelsForMember",
		"func ListDMChannelsForMemberWithAgentOwnerLimit",
		"func ListChannelMembers",
	} {
		source := sourceFromFunctionForMessageTest(t, projectionSource, signature)
		require.Contains(t, source, "scope_memberships", signature)
		require.NotContains(t, source, legacyTableNameForMessageTest("space", "participants"), signature)
		require.NotContains(t, source, legacyTableNameForMessageTest("space", "subscriptions"), signature)
		require.NotContains(t, source, legacyTableNameForMessageTest("channel", "agent", "entries"), signature)
		require.NotContains(t, source, legacyTableNameForMessageTest("dm", "threads"), signature)
		require.NotContains(t, source, legacySQLTableRefForMessageTest("JOIN", "threads"), signature)
		require.NotContains(t, source, legacySQLTableRefForMessageTest("FROM", "threads"), signature)
	}

	serviceBytes, err := os.ReadFile("service.go")
	require.NoError(t, err)
	loadDMSource := sourceFromFunctionForMessageTest(t, string(serviceBytes), "func LoadDMChannelByID")
	require.Contains(t, loadDMSource, "FROM scopes s")
	require.Contains(t, loadDMSource, "scope_memberships")
	require.NotContains(t, loadDMSource, legacyTableNameForMessageTest("dm", "threads"))
	require.NotContains(t, loadDMSource, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, loadDMSource, legacySQLTableRefForMessageTest("FROM", "threads"))

	for _, signature := range []string{
		"func ResolveDMThreadByIDForMember",
		"func resolveExistingDMThread",
	} {
		source := sourceFromFunctionForMessageTest(t, string(serviceBytes), signature)
		require.Contains(t, source, "FROM scopes s", signature)
		require.Contains(t, source, "scope_memberships", signature)
		require.NotContains(t, source, legacyTableNameForMessageTest("dm", "threads"), signature)
		require.NotContains(t, source, legacySQLTableRefForMessageTest("FROM", "threads"), signature)
	}
}

func TestScopeRuntimeProjectionReadsAuthoritativeScopesOnly(t *testing.T) {
	t.Parallel()

	sourceBytes, err := os.ReadFile("scope_runtime_projection.go")
	require.NoError(t, err)
	ensureSource := sourceFromFunctionForMessageTest(t, string(sourceBytes), "func EnsureScopeProjectionForThreadAccess")
	require.Contains(t, ensureSource, "LoadScope")
	require.NotContains(t, ensureSource, legacyTableNameForMessageTest("dm", "threads"))
	require.NotContains(t, ensureSource, legacyTableNameForMessageTest("reply", "threads"))
	require.NotContains(t, ensureSource, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, ensureSource, legacySQLTableRefForMessageTest("FROM", "threads"))

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 14, 14, 50, 0, 0, time.UTC)

	const (
		workspaceID = "wspmsg_scope_runtime"
		userID      = "usrmsg_scope_runtime"
		memberID    = "mem_scope_runtime"
		channelID   = "chn_scope_runtime"
		threadID    = "thr_scope_runtime"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "scope-runtime@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	_, err = EnsureScopeProjectionForThreadAccess(ctx, pool, ThreadAccess{ChannelID: channelID}, memberID, now)
	require.ErrorIs(t, err, ErrNotFound)

	require.NoError(t, NewScopeRepositories(pool).Scopes.UpsertScope(ctx, ScopeRecord{
		ID:                channelID,
		WorkspaceID:       workspaceID,
		Type:              "channel",
		IdentityKey:       "channel:" + channelID,
		Status:            "active",
		CreatedByMemberID: memberID,
		CreatedAt:         now,
		UpdatedAt:         now,
	}))
	record, err := EnsureScopeProjectionForThreadAccess(ctx, pool, ThreadAccess{ChannelID: channelID}, memberID, now)
	require.NoError(t, err)
	require.Equal(t, channelID, record.ID)
}

func TestReplyThreadShortIDLookupUsesScopeTopology(t *testing.T) {
	t.Parallel()

	sourceBytes, err := os.ReadFile("service.go")
	require.NoError(t, err)
	source := string(sourceBytes)

	lookupSource := sourceFromFunctionForMessageTest(t, source, "func loadReplyThreadLookupByShortID")
	require.Contains(t, lookupSource, "FROM scopes thread_scope")
	require.NotContains(t, lookupSource, legacySQLTableRefForMessageTest("LEFT JOIN", "threads"))
	require.NotContains(t, lookupSource, legacySQLTableRefForMessageTest("FROM", "reply", "threads"))
	require.NotContains(t, lookupSource, legacySQLTableRefForMessageTest("JOIN", "reply", "threads"))

	resolveSource := sourceFromFunctionForMessageTest(t, source, "func resolveParentMessageIDByShortID")
	require.Contains(t, resolveSource, "FROM scope_messages")
	require.NotContains(t, resolveSource, legacyTableNameForMessageTest("reply", "threads"))
	require.NotContains(t, resolveSource, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, resolveSource, legacySQLTableRefForMessageTest("FROM", "threads"))

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 14, 14, 55, 0, 0, time.UTC)

	const (
		workspaceID     = "wsp_reply_lookup_scope"
		userID          = "usr_reply_lookup_scope"
		memberID        = "mem_reply_lookup_scope"
		channelID       = "chn_reply_lookup_parent"
		parentThreadID  = "thr_reply_lookup_parent"
		parentMessageID = "lookup121234567890"
		shortID         = "lookup12"
		replyChannelID  = "chn_reply_lookup_child"
		replyThreadID   = "thr_reply_lookup_child"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "reply-lookup-scope@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "reply-lookup", memberID, parentThreadID, now)
	insertMessagingMessage(t, ctx, pool, parentMessageID, workspaceID, channelID, memberID, "parent", 1, 1, now)
	insertMessagingReplyThread(t, ctx, pool, replyChannelID, workspaceID, channelID, parentMessageID, shortID, replyThreadID, now)

	lookup, err := loadReplyThreadLookupByShortID(ctx, pool, channelID, shortID)
	require.NoError(t, err)
	require.Equal(t, replyChannelID, lookup.ThreadChannelID)
	require.Equal(t, replyChannelID, lookup.ThreadID)
	require.Equal(t, parentMessageID, lookup.ParentMessageID)
	require.Equal(t, shortID, lookup.ShortID)

	visibleThreadTargetShortID := "feedcafe"
	mustExecMessaging(t, ctx, pool, `
UPDATE scope_channel_profiles
SET slug = $2, display_name = $2, updated_at = $3
WHERE scope_id = $1
`, replyChannelID, "thread-"+visibleThreadTargetShortID, now)
	lookupByVisibleTarget, err := loadReplyThreadLookupByShortID(ctx, pool, channelID, visibleThreadTargetShortID)
	require.NoError(t, err)
	require.Equal(t, replyChannelID, lookupByVisibleTarget.ThreadChannelID)
	require.Equal(t, parentMessageID, lookupByVisibleTarget.ParentMessageID)
	require.Equal(t, shortID, lookupByVisibleTarget.ShortID)

	lookupByVisibleTargetPrefix, err := loadReplyThreadLookupByShortID(ctx, pool, channelID, visibleThreadTargetShortID[:6])
	require.NoError(t, err)
	require.Equal(t, replyChannelID, lookupByVisibleTargetPrefix.ThreadChannelID)

	resolvedParentMessageID, resolvedShortID, err := resolveParentMessageIDByShortID(ctx, pool, channelID, shortID[:6])
	require.NoError(t, err)
	require.Equal(t, parentMessageID, resolvedParentMessageID)
	require.Equal(t, shortID, resolvedShortID)

	mustExecMessaging(t, ctx, pool, `
UPDATE scopes
SET archived_at = $2, updated_at = $2
WHERE id = $1
`, replyChannelID, now.Add(time.Minute))
	_, err = loadReplyThreadLookupByShortID(ctx, pool, channelID, shortID)
	require.ErrorIs(t, err, ErrNotFound)
}

func TestListChannelMembersIncludesAgentMemberRefs(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 3, 19, 13, 20, 0, 0, time.UTC)

	const (
		workspaceID   = "wspmsg_agents"
		userID        = "usrmsg_agents"
		humanMemberID = "human_agents"
		agentMemberID = "agent_member_1"
		agentID       = "agtmsg1"
		machineID     = "machmsg1"
		channelID     = "chnmsg_agents"
		channelThread = "thrmsg_agents"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "agent-projection@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, humanMemberID, workspaceID, userID, now)
	mustExecMessaging(t, ctx, pool, `
UPDATE users
SET display_name = 'Agent Owner'
WHERE id = $1
`, userID)
	insertMessagingMachine(t, ctx, pool, machineID, workspaceID, userID, now)
	insertMessagingAgentMember(t, ctx, pool, agentMemberID, workspaceID, now)
	insertMessagingAgent(t, ctx, pool, agentID, workspaceID, machineID, agentMemberID, "Builder", now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "all", humanMemberID, channelThread, now)
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, agentMemberID, "public", "", "auto-wake", 0, 0, now))

	members, err := ListChannelMembers(ctx, db, channelID)
	require.NoError(t, err)
	require.Len(t, members.Agents, 1)
	require.Equal(t, agentID, members.Agents[0].ID)
	require.Equal(t, "Builder", members.Agents[0].Name)
	require.Equal(t, agentMemberID, members.Agents[0].MemberID)
	require.Equal(t, "gpt-5", members.Agents[0].Model)
	require.Equal(t, "public", members.Agents[0].Visibility)
	require.Equal(t, userID, members.Agents[0].OwnerProfile.PrincipalID)
	require.Equal(t, humanMemberID, members.Agents[0].OwnerProfile.MemberID)
	require.Equal(t, "Agent Owner", members.Agents[0].OwnerProfile.DisplayName)
}

func TestListChannelMembersPageSearchFindsMemberOutsideFirstPage(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 3, 19, 13, 25, 0, 0, time.UTC)

	const (
		workspaceID   = "wspmsg_member_search"
		ownerUserID   = "usr_member_search_owner"
		ownerMemberID = "human_member_search_owner"
		channelID     = "chn_member_search"
		channelThread = "thr_member_search"
		serviceID     = "svc_member_search_bot"
		serviceDefID  = "svcdef_member_search_bot"
		serviceInstID = "svcinst_member_search_bot"
		serviceRowID  = "svcmem_member_search_bot"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "member-search-owner@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "member-search", ownerMemberID, channelThread, now)

	for i := 0; i < 60; i++ {
		userID := fmt.Sprintf("usr_member_search_%02d", i)
		memberID := fmt.Sprintf("human_member_search_%02d", i)
		if i < 10 {
			memberID = fmt.Sprintf("human-target-app-%02d", i)
		}
		insertMessagingUser(t, ctx, pool, userID, userID+"@example.com", now)
		insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
		require.NoError(t, EnsureScopeMembership(
			ctx,
			pool,
			channelID,
			memberID,
			"public",
			"",
			"seed",
			0,
			0,
			now.Add(time.Duration(i+1)*time.Second),
		))
	}

	insertMessagingServiceMember(
		t,
		ctx,
		pool,
		serviceID,
		workspaceID,
		"Service substrate label",
		now,
	)
	insertMessagingAutomationServiceActor(t, ctx, pool, messagingAutomationServiceActor{
		WorkspaceID:         workspaceID,
		CreatorMemberID:     ownerMemberID,
		DefinitionID:        serviceDefID,
		InstallationID:      serviceInstID,
		ServiceMemberRowID:  serviceRowID,
		MemberID:            serviceID,
		Slug:                "target-service",
		Handle:              "target-app",
		DefinitionName:      "Target App Definition",
		MemberDisplayName:   "Resolved Target App",
		DefinitionAvatarURL: "https://example.test/target-definition.png",
		MemberAvatarURL:     "https://example.test/target-app.png",
		Now:                 now,
	})
	require.NoError(t, EnsureScopeMembership(
		ctx,
		pool,
		channelID,
		serviceID,
		"public",
		"",
		"seed",
		0,
		0,
		now.Add(2*time.Minute),
	))

	firstPage, err := ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{Limit: 50})
	require.NoError(t, err)
	require.True(t, firstPage.HasMore)
	require.Empty(t, firstPage.Services)
	require.Equal(t, 62, firstPage.TotalCount)

	var searchPage ChannelMemberList
	for _, query := range []string{
		"target-app",
		"resolved target app",
		"target app definition",
	} {
		searchPage, err = ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{
			Limit: 8,
			Query: query,
		})
		require.NoError(t, err)
		require.Len(t, searchPage.Services, 1)
		require.Equal(t, serviceID, searchPage.Services[0].ID)
		require.Equal(t, "target-app", searchPage.Services[0].Name)
		require.Equal(t, "Resolved Target App", searchPage.Services[0].DisplayName)
		require.Equal(
			t,
			"https://example.test/target-app.png",
			searchPage.Services[0].AvatarURL,
		)
	}
	require.Equal(t, firstPage.TotalCount, searchPage.TotalCount)
	require.LessOrEqual(
		t,
		len(searchPage.Humans)+len(searchPage.Agents)+len(searchPage.Services),
		8,
	)
}

func TestListChannelMembersPageIncludesPrivateAgentsWhenPublicPageHasMore(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 3, 19, 13, 27, 0, 0, time.UTC)

	const (
		workspaceID        = "wspmsg_private_agent_page"
		ownerUserID        = "usr_private_agent_page_owner"
		ownerMemberID      = "human_private_agent_page_owner"
		channelID          = "chn_private_agent_page"
		channelThread      = "thr_private_agent_page"
		privateAgentMember = "agent_private_agent_page_member"
		privateAgentID     = "agt_private_agent_page"
		machineID          = "mach_private_agent_page"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "private-agent-page-owner@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "private-agent-page", ownerMemberID, channelThread, now)

	for i := 0; i < 60; i++ {
		userID := fmt.Sprintf("usr_private_agent_page_%02d", i)
		memberID := fmt.Sprintf("human_private_agent_page_%02d", i)
		insertMessagingUser(t, ctx, pool, userID, userID+"@example.com", now)
		insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
		require.NoError(t, EnsureScopeMembership(
			ctx,
			pool,
			channelID,
			memberID,
			"public",
			"",
			"seed",
			0,
			0,
			now.Add(time.Duration(i+1)*time.Second),
		))
	}

	insertMessagingMachine(t, ctx, pool, machineID, workspaceID, ownerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, privateAgentMember, workspaceID, now)
	insertMessagingAgent(t, ctx, pool, privateAgentID, workspaceID, machineID, privateAgentMember, "private-page-agent", now)
	require.NoError(t, EnsureScopeMembership(
		ctx,
		pool,
		channelID,
		privateAgentMember,
		"private",
		ownerMemberID,
		"auto-wake",
		0,
		0,
		now.Add(2*time.Minute),
	))

	firstPage, err := ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{Limit: 50})
	require.NoError(t, err)
	require.True(t, firstPage.HasMore)
	require.Len(t, firstPage.PrivateAgents, 1)
	require.Equal(t, 62, firstPage.TotalCount)
	require.Equal(t, privateAgentID, firstPage.PrivateAgents[0].ID)
	require.Equal(t, "gpt-5", firstPage.PrivateAgents[0].Model)
	require.Equal(t, "private", firstPage.PrivateAgents[0].Visibility)

	nextPage, err := ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{
		Limit:  50,
		Cursor: firstPage.NextCursor,
	})
	require.NoError(t, err)
	require.Empty(t, nextPage.PrivateAgents)
	require.Equal(t, firstPage.TotalCount, nextPage.TotalCount)
}

func TestListChannelMembersPagePrivateAgentNameFallsBackToMemberID(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 7, 6, 9, 38, 0, 0, time.UTC)

	const (
		workspaceID          = "wspmsg_private_agent_blank_name"
		ownerUserID          = "usr_private_agent_blank_name_owner"
		ownerMemberID        = "human_private_agent_blank_name_owner"
		channelID            = "chn_private_agent_blank_name"
		channelThread        = "thr_private_agent_blank_name"
		privateAgentMemberID = "agent_private_agent_blank_name_member"
		privateAgentID       = "agt_private_agent_blank_name"
		machineID            = "mach_private_agent_blank_name"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "private-agent-blank-name-owner@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "private-agent-blank-name", ownerMemberID, channelThread, now)
	insertMessagingMachine(t, ctx, pool, machineID, workspaceID, ownerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, privateAgentMemberID, workspaceID, now)
	mustExecMessaging(t, ctx, pool, `
UPDATE members
SET display_name = ''
WHERE id = $1
`, privateAgentMemberID)
	insertMessagingAgent(t, ctx, pool, privateAgentID, workspaceID, machineID, privateAgentMemberID, "", now)
	require.NoError(t, EnsureScopeMembership(
		ctx,
		pool,
		channelID,
		privateAgentMemberID,
		"private",
		ownerMemberID,
		"auto-wake",
		0,
		0,
		now.Add(time.Minute),
	))

	page, err := ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{Limit: 50})
	require.NoError(t, err)
	require.Len(t, page.PrivateAgents, 1)
	require.Equal(t, privateAgentID, page.PrivateAgents[0].ID)
	require.Equal(t, privateAgentMemberID, page.PrivateAgents[0].Name)
	require.Equal(t, privateAgentMemberID, page.PrivateAgents[0].DisplayName)
	require.Equal(t, privateAgentMemberID, page.PrivateAgents[0].MemberID)
}

func TestListChannelMembersPageHidesPrivateAgentWhenPublicMembershipIsOnLaterPage(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 3, 19, 13, 35, 0, 0, time.UTC)

	const (
		workspaceID          = "wspmsg_private_agent_dedup"
		ownerUserID          = "usr_private_agent_dedup_owner"
		ownerMemberID        = "human_private_agent_dedup_owner"
		channelID            = "chn_private_agent_dedup"
		channelThread        = "thr_private_agent_dedup"
		machineID            = "mach_private_agent_dedup"
		privateAgentID       = "agt_private_agent_dedup_target"
		privateAgentMemberID = "agent_private_agent_dedup_target"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "private-agent-dedup-owner@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "private-agent-dedup", ownerMemberID, channelThread, now)
	insertMessagingMachine(t, ctx, pool, machineID, workspaceID, ownerUserID, now)

	for i := 0; i < 60; i++ {
		fillAgentID := fmt.Sprintf("agt_private_agent_dedup_fill_%02d", i)
		fillMemberID := fmt.Sprintf("agent_private_agent_dedup_fill_%02d", i)
		insertMessagingAgentMember(t, ctx, pool, fillMemberID, workspaceID, now)
		insertMessagingAgent(t, ctx, pool, fillAgentID, workspaceID, machineID, fillMemberID, fmt.Sprintf("fill-%02d", i), now)
		require.NoError(t, EnsureScopeMembership(
			ctx,
			pool,
			channelID,
			fillMemberID,
			"public",
			"",
			"auto-wake",
			0,
			0,
			now.Add(time.Duration(i+1)*time.Second),
		))
	}

	insertMessagingAgentMember(t, ctx, pool, privateAgentMemberID, workspaceID, now)
	insertMessagingAgent(t, ctx, pool, privateAgentID, workspaceID, machineID, privateAgentMemberID, "target-private-agent", now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 1, NULL, $3, $3, NULL, NULL)
`, channelID, privateAgentMemberID, now.Add(2*time.Minute))
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'private', $3, 0, 0, 0, 0, 2, NULL, $4, $4, NULL, NULL)
`, channelID, privateAgentMemberID, ownerMemberID, now.Add(3*time.Minute))

	firstPage, err := ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{Limit: 50})
	require.NoError(t, err)
	require.True(t, firstPage.HasMore, "public roster should span more than one page")
	for _, agent := range firstPage.PrivateAgents {
		require.NotEqual(t, privateAgentID, agent.ID, "private agent with active public membership must not surface in PrivateAgents even when its public row is on a later page")
	}

	nextPage, err := ListChannelMembersPage(ctx, pool, channelID, ChannelMemberListOptions{
		Limit:  50,
		Cursor: firstPage.NextCursor,
	})
	require.NoError(t, err)
	foundPublic := false
	for _, agent := range nextPage.Agents {
		if agent.ID == privateAgentID {
			foundPublic = true
			break
		}
	}
	require.True(t, foundPublic, "target agent should still appear on the public roster of a later page")
	require.Empty(t, nextPage.PrivateAgents)
}

func TestThreadIDForChannelAccessSupportsChannelDMAndReplyThread(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 3, 19, 13, 30, 0, 0, time.UTC)

	const (
		workspaceID    = "wspmsg2"
		userID         = "usrmsg3"
		memberID       = "human03"
		peerUserID     = "usrmsg4"
		peerMemberID   = "human04"
		channelID      = "chnmsg21"
		channelThread  = "thrmsg21"
		dmThreadID     = "dmmsg221"
		dmThread       = "thrmsg22"
		replyChannelID = "chnmsg23"
		replyThreadID  = "thrmsg23"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "owner@example.com", now)
	insertMessagingUser(t, ctx, pool, peerUserID, "peer@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingHumanMember(t, ctx, pool, peerMemberID, workspaceID, peerUserID, now)
	insertMessagingChannel(
		t,
		ctx,
		pool,
		channelID,
		workspaceID,
		"general",
		memberID,
		channelThread,
		now,
	)
	insertMessagingDMThread(t, ctx, pool, dmThreadID, workspaceID, memberID, peerMemberID, dmThread, now)
	insertMessagingReplyThread(t, ctx, pool, replyChannelID, workspaceID, channelID, "abcdef12deadbeef", "abcdef12", replyThreadID, now)

	resolvedThreadID, err := ThreadIDForChannelAccess(ctx, db, memberID, workspaceID, channelID)
	require.NoError(t, err)
	require.Equal(t, channelID, resolvedThreadID)

	resolvedThreadID, err = ThreadIDForChannelAccess(ctx, db, memberID, workspaceID, dmThreadID)
	require.NoError(t, err)
	require.Equal(t, dmThreadID, resolvedThreadID)

	resolvedThreadID, err = ThreadIDForChannelAccess(ctx, db, memberID, workspaceID, replyChannelID)
	require.NoError(t, err)
	require.Equal(t, replyChannelID, resolvedThreadID)

	_, err = ThreadIDForChannelAccess(ctx, db, peerMemberID, workspaceID, channelID)
	require.ErrorIs(t, err, ErrForbidden)
}

func TestRequireMemberReplyThreadReadRequiresParentChannelMembership(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 5, 14, 4, 30, 0, 0, time.UTC)

	const (
		workspaceID         = "wspmsg_scope_access"
		ownerUserID         = "usrmsg_scope_owner"
		childUserID         = "usrmsg_scope_child"
		subOnlyUserID       = "usrmsg_scope_subonly"
		closedChildUserID   = "usrmsg_scope_closed_child"
		ownerMemberID       = "mem_scope_owner"
		childMemberID       = "mem_scope_child"
		subOnlyMemberID     = "mem_scope_subonly"
		closedChildMemberID = "mem_scope_closed_child"
		channelID           = "chnmsg_scope_parent"
		channelThreadID     = "thrmsg_scope_parent"
		replyChannelID      = "chnmsg_scope_reply"
		replyThreadID       = "thrmsg_scope_reply"
		parentMessageID     = "msg_scope_parent"
		parentMessageSID    = "scopemsg1"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "scope-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, childUserID, "scope-child@example.com", now)
	insertMessagingUser(t, ctx, pool, subOnlyUserID, "scope-subonly@example.com", now)
	insertMessagingUser(t, ctx, pool, closedChildUserID, "scope-closed-child@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingHumanMember(t, ctx, pool, childMemberID, workspaceID, childUserID, now)
	insertMessagingHumanMember(t, ctx, pool, subOnlyMemberID, workspaceID, subOnlyUserID, now)
	insertMessagingHumanMember(t, ctx, pool, closedChildMemberID, workspaceID, closedChildUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "scope-parent", ownerMemberID, channelThreadID, now)
	insertMessagingReplyThread(t, ctx, pool, replyChannelID, workspaceID, channelID, parentMessageID, parentMessageSID, replyThreadID, now)

	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 0, NULL, $3, $3, NULL, NULL)
`, replyChannelID, childMemberID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 7, 7, $3, $3, NULL, NULL)
`, replyChannelID, closedChildMemberID, now)

	_, err := RequireMemberReplyThreadRead(ctx, db, childMemberID, workspaceID, replyChannelID)
	require.ErrorIs(t, err, ErrNotFound)

	parentAccess, err := RequireMemberReplyThreadRead(ctx, db, ownerMemberID, workspaceID, replyChannelID)
	require.NoError(t, err)
	require.Equal(t, replyChannelID, parentAccess.ThreadID)
	require.Equal(t, channelID, parentAccess.ParentChannelID)
	require.Equal(t, parentMessageID, parentAccess.ParentMessageID)

	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_notification_settings (scope_id, member_id, state, created_at, updated_at)
VALUES ($1, $2, 'active', $3, $3)
	`, replyChannelID, subOnlyMemberID, now)

	_, err = RequireMemberReplyThreadRead(ctx, db, subOnlyMemberID, workspaceID, replyChannelID)
	require.Error(t, err)

	_, err = RequireMemberReplyThreadRead(ctx, db, closedChildMemberID, workspaceID, replyChannelID)
	require.Error(t, err)
}

func TestReplyThreadSummaryQueriesUseScopeTopology(t *testing.T) {
	t.Parallel()

	sourceBytes, err := os.ReadFile("service.go")
	require.NoError(t, err)
	source := string(sourceBytes)

	listSource := sourceFromFunctionForMessageTest(t, source, "func ListReplyThreadSummariesForMemberWithLimit")
	require.Contains(t, listSource, "FROM scopes AS child")
	require.Contains(t, listSource, "JOIN scopes AS child")
	require.Contains(t, listSource, "visible_message_count")
	require.Contains(t, listSource, "visible_last_message_at")
	require.Contains(t, listSource, "parent_cp.subsystem = $3")
	require.Contains(t, listSource, "viewer_subscription.member_id = $4")
	require.Contains(t, listSource, "viewer_subscription.state IN ('active', 'quiet', 'mute')")
	require.Contains(t, listSource, "LIMIT $5")
	require.NotContains(t, listSource, "parent_cp.subsystem = 'im'")
	require.NotContains(t, listSource, "scope_messages messages")
	require.NotContains(t, listSource, "COUNT(messages.id)")
	require.NotContains(t, listSource, "MAX(messages.created_at)")
	require.NotContains(t, listSource, legacyTableNameForMessageTest("reply", "thread", "stats"))
	require.NotContains(t, listSource, legacyTableNameForMessageTest("reply", "threads"))
	require.NotContains(t, listSource, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, listSource, legacySQLTableRefForMessageTest("FROM", "threads"))

	boundedSource := sourceFromFunctionForMessageTest(t, source, "func ListReplyThreadSummariesForParentMessageIDs")
	require.Contains(t, boundedSource, "requested_parent_messages")
	require.Contains(t, boundedSource, "child.workspace_id = $1")
	require.Contains(t, boundedSource, "child.parent_scope_id = parent_scope.id")
	require.Contains(t, boundedSource, "child.root_message_id = requested.parent_message_id")
	require.Contains(t, boundedSource, "visible_message_count")
	require.Contains(t, boundedSource, "visible_last_message_at")
	require.NotContains(t, boundedSource, "scope_messages messages")
	require.NotContains(t, boundedSource, "COUNT(messages.id)")
	require.NotContains(t, boundedSource, "MAX(messages.created_at)")
	require.NotContains(t, boundedSource, legacyTableNameForMessageTest("reply", "thread", "stats"))

	loadSource := sourceFromFunctionForMessageTest(t, source, "func loadReplyThreadSummaryByParentMessageID")
	require.Contains(t, loadSource, "FROM scopes AS child")
	require.Contains(t, loadSource, "visible_message_count")
	require.Contains(t, loadSource, "visible_last_message_at")
	require.NotContains(t, loadSource, "scope_messages messages")
	require.NotContains(t, loadSource, "COUNT(messages.id)")
	require.NotContains(t, loadSource, "MAX(messages.created_at)")
	require.NotContains(t, loadSource, legacyTableNameForMessageTest("reply", "thread", "stats"))
	require.NotContains(t, loadSource, legacyTableNameForMessageTest("reply", "threads"))
	require.NotContains(t, loadSource, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, loadSource, legacySQLTableRefForMessageTest("FROM", "threads"))
}

func TestListReplyThreadSummariesForMemberWithLimitUsesParentChannelSpace(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 13, 10, 0, 0, 0, time.UTC)

	const (
		workspaceID    = "wspmsgsum"
		userID         = "usrmsgsum"
		memberID       = "humanmsgsum"
		channelID      = "chnmsgsum"
		threadID       = "thrmsgsum"
		kbChannelID    = "chnmsgsumkb"
		decoyChannelID = "chnmsgsumx"
		decoyThreadID  = "thrmsgsumx"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "summary-owner@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "summary", memberID, threadID, now)
	insertMessagingMessage(t, ctx, pool, "msg_old_parent", workspaceID, channelID, memberID, "old", 1, 1, now.Add(time.Second))
	insertMessagingMessage(t, ctx, pool, "msg_new_parent", workspaceID, channelID, memberID, "new", 2, 2, now.Add(2*time.Second))
	insertMessagingReplyThread(t, ctx, pool, "chnmsgsumr1", workspaceID, channelID, "msg_old_parent", "oldparen", "thrmsgsumr1", now)
	insertMessagingReplyThread(t, ctx, pool, "chnmsgsumr2", workspaceID, channelID, "msg_new_parent", "newparen", "thrmsgsumr2", now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_notification_settings (scope_id, member_id, state, created_at, updated_at)
VALUES ($1, $2, 'quiet', $3, $3)
`, "chnmsgsumr1", memberID, now)
	insertMessagingChannel(t, ctx, pool, kbChannelID, workspaceID, "summary-kb", memberID, "", now)
	mustExecMessaging(t, ctx, pool, `
UPDATE scope_channel_profiles
SET subsystem = $2, updated_at = $3
WHERE scope_id = $1
`, kbChannelID, ChannelSubsystemKB, now)
	insertMessagingMessage(t, ctx, pool, "msg_kb_parent", workspaceID, kbChannelID, memberID, "kb", 1, 3, now.Add(3*time.Second))
	insertMessagingReplyThread(t, ctx, pool, "chnmsgsumkbr1", workspaceID, kbChannelID, "msg_kb_parent", "kbparen", "thrmsgsumkbr1", now)
	insertMessagingChannel(t, ctx, pool, decoyChannelID, workspaceID, "summary-decoy", memberID, decoyThreadID, now)

	allSummaries, err := ListReplyThreadSummariesForMemberWithLimit(ctx, pool, memberID, workspaceID, channelID, 0)
	require.NoError(t, err)
	require.Contains(t, allSummaries, "msg_old_parent")
	require.Contains(t, allSummaries, "msg_new_parent")
	require.Equal(t, HumanScopeSubscriptionStateQuiet, allSummaries["msg_old_parent"].SubscriptionState)
	require.Empty(t, allSummaries["msg_new_parent"].SubscriptionState)

	summary, err := LoadReplyThreadSummaryForParentMessage(ctx, pool, memberID, workspaceID, channelID, "msg_new_parent")
	require.NoError(t, err)
	require.Equal(t, "chnmsgsumr2", summary.ThreadChannelID)

	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_notification_settings (scope_id, member_id, state, created_at, updated_at)
VALUES ($1, $2, 'mute', $3, $3)
`, "chnmsgsumr2", memberID, now)

	summaries, err := ListReplyThreadSummariesForMemberWithLimit(ctx, pool, memberID, workspaceID, channelID, 1)
	require.NoError(t, err)
	require.Contains(t, summaries, "msg_new_parent")
	require.NotContains(t, summaries, "msg_old_parent")
	require.Equal(t, HumanScopeSubscriptionStateMute, summaries["msg_new_parent"].SubscriptionState)

	kbSummaries, err := ListReplyThreadSummariesForMemberWithLimit(ctx, pool, memberID, workspaceID, kbChannelID, 0)
	require.NoError(t, err)
	require.Contains(t, kbSummaries, "msg_kb_parent")
	require.Equal(t, "chnmsgsumkbr1", kbSummaries["msg_kb_parent"].ThreadChannelID)

	limitedKBSummaries, err := ListReplyThreadSummariesForMemberWithLimit(ctx, pool, memberID, workspaceID, kbChannelID, 1)
	require.NoError(t, err)
	require.Contains(t, limitedKBSummaries, "msg_kb_parent")

	mustExecMessaging(t, ctx, pool, `
UPDATE scopes
SET archived_at = $2, updated_at = $2
WHERE id = $1
`, "chnmsgsumr2", now.Add(3*time.Second))
	_, err = LoadReplyThreadSummaryForParentMessage(ctx, pool, memberID, workspaceID, channelID, "msg_new_parent")
	require.ErrorIs(t, err, ErrNotFound)
}

func TestArchivedReplyThreadSummaryUsesScopeTopology(t *testing.T) {
	t.Parallel()

	sourceBytes, err := os.ReadFile("reply_threads_store.go")
	require.NoError(t, err)
	source := sourceFromFunctionForMessageTest(t, string(sourceBytes), "func loadReplyThreadSummary")
	require.Contains(t, source, "FROM parent_scope")
	require.Contains(t, source, "JOIN scopes child")
	require.Contains(t, source, "visible_message_count")
	require.Contains(t, source, "visible_last_message_at")
	require.NotContains(t, source, "scope_messages messages")
	require.NotContains(t, source, "COUNT(messages.id)")
	require.NotContains(t, source, "MAX(messages.created_at)")
	require.NotContains(t, source, legacyTableNameForMessageTest("reply", "thread", "stats"))
	require.NotContains(t, source, legacyTableNameForMessageTest("reply", "threads"))

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 14, 10, 12, 0, 0, time.UTC)

	const (
		workspaceID     = "wsp_arch_sum_scope"
		userID          = "usr_arch_sum_scope"
		memberID        = "mem_arch_sum_scope"
		channelID       = "chn_arch_sum_scope"
		parentThreadID  = "thr_arch_sum_parent"
		parentMessageID = "msg_arch_sum_parent"
		replyChannelID  = "chn_arch_sum_reply"
		replyThreadID   = "thr_arch_sum_reply"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "archived-summary-scope@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "archived-summary", memberID, parentThreadID, now)
	insertMessagingMessage(t, ctx, pool, parentMessageID, workspaceID, channelID, memberID, "parent", 1, 1, now)
	insertMessagingReplyThread(t, ctx, pool, replyChannelID, workspaceID, channelID, parentMessageID, "archsum", replyThreadID, now)
	require.NoError(t, EnsureReplyThreadParticipantProjection(ctx, pool, replyChannelID, memberID, "auto-wake", now))
	insertMessagingMessage(t, ctx, pool, "msg_arch_sum_reply_1", workspaceID, replyChannelID, memberID, "reply 1", 1, 2, now.Add(time.Minute))
	insertMessagingMessage(t, ctx, pool, "msg_arch_sum_reply_2", workspaceID, replyChannelID, memberID, "reply 2", 2, 3, now.Add(2*time.Minute))
	insertMessagingMessage(t, ctx, pool, "msg_arch_sum_reply_3", workspaceID, replyChannelID, memberID, "reply 3", 3, 4, now.Add(3*time.Minute))
	recomputeVisibleSummaryForMessageTest(t, ctx, pool, replyChannelID, now.Add(3*time.Minute))

	summary, err := loadReplyThreadSummary(ctx, pool, workspaceID, channelID, parentMessageID)
	require.NoError(t, err)
	require.Equal(t, replyChannelID, summary.ThreadChannelID)
	require.Equal(t, int64(3), summary.ReplyCount)
	require.NotNil(t, summary.LastReplyAt)
	require.Empty(t, summary.ParticipantIDs)

	mustExecMessaging(t, ctx, pool, `
UPDATE scopes
SET archived_at = $2, updated_at = $2
WHERE id = $1
`, replyChannelID, now.Add(2*time.Minute))
	_, err = loadReplyThreadSummary(ctx, pool, workspaceID, channelID, parentMessageID)
	require.ErrorIs(t, err, ErrNotFound)
}

func TestArchivedReplyThreadBoundsUseScopeArchivedUntilSeq(t *testing.T) {
	t.Parallel()

	sourceBytes, err := os.ReadFile("reply_threads_store.go")
	require.NoError(t, err)
	source := sourceFromFunctionForMessageTest(t, string(sourceBytes), "func loadArchivedUntilSeq")
	require.Contains(t, source, "FROM scopes")
	require.NotContains(t, source, legacySQLTableRefForMessageTest("FROM", "threads"))
	require.NotContains(t, source, legacySQLTableRefForMessageTest("JOIN", "threads"))

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 14, 10, 34, 0, 0, time.UTC)

	const (
		workspaceID = "wsp_msg_arch_bounds_scope"
		userID      = "usr_msg_arch_bounds_scope"
		memberID    = "mem_msg_arch_bounds_scope"
		channelID   = "chn_msg_arch_bounds_scope"
		threadID    = "thr_msg_arch_bounds_scope"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "message-arch-bounds-scope@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "message-arch-bounds", memberID, threadID, now)
	mustExecMessaging(t, ctx, pool, `
UPDATE scopes
SET archived_until_seq = 9, updated_at = $2
WHERE id = $1
`, channelID, now)

	archivedUntilSeq, err := loadArchivedUntilSeq(ctx, pool, channelID)
	require.NoError(t, err)
	require.EqualValues(t, 9, archivedUntilSeq)
}

func TestReplyThreadStateRuntimeUsesScopeTopology(t *testing.T) {
	t.Parallel()

	replyStateSourceBytes, err := os.ReadFile("reply_thread_state.go")
	require.NoError(t, err)
	statsSource := sourceFromFunctionForMessageTest(t, string(replyStateSourceBytes), "func LoadReplyThreadStatsForUpdate")
	require.Contains(t, statsSource, "FROM scopes child")
	require.Contains(t, statsSource, "visible_message_count")
	require.Contains(t, statsSource, "visible_last_message_at")
	require.NotContains(t, statsSource, "scope_messages messages")
	require.NotContains(t, statsSource, "COUNT(messages.id)")
	require.NotContains(t, statsSource, "MAX(messages.created_at)")
	require.NotContains(t, statsSource, legacyTableNameForMessageTest("reply", "thread", "stats"))
	require.NotContains(t, statsSource, legacyTableNameForMessageTest("reply", "threads"))

	routingSourceBytes, err := os.ReadFile("routing.go")
	require.NoError(t, err)
	participantSource := sourceFromFunctionForMessageTest(t, string(routingSourceBytes), "func loadReplyThreadParticipantIDs")
	require.Contains(t, participantSource, "FROM scopes")
	require.NotContains(t, participantSource, legacyTableNameForMessageTest("reply", "threads"))

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 14, 10, 58, 0, 0, time.UTC)

	const (
		workspaceID     = "wsp_msg_reply_state_scope"
		userID          = "usr_msg_reply_state_scope"
		memberID        = "mem_msg_reply_state_scope"
		channelID       = "chn_msg_reply_state_scope"
		parentThreadID  = "thr_msg_reply_state_scope"
		parentMessageID = "msg_reply_state_parent"
		replyChannelID  = "chn_reply_state_scope"
		replyThreadID   = "thr_reply_state_scope"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, userID, "reply-state-scope@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, memberID, workspaceID, userID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "reply-state", memberID, parentThreadID, now)
	insertMessagingMessage(t, ctx, pool, parentMessageID, workspaceID, channelID, memberID, "parent", 1, 1, now)
	insertMessagingReplyThread(t, ctx, pool, replyChannelID, workspaceID, channelID, parentMessageID, "rstscope", replyThreadID, now)
	require.NoError(t, EnsureReplyThreadParticipantProjection(ctx, pool, replyChannelID, memberID, "auto-wake", now))
	insertMessagingMessage(t, ctx, pool, "msg_reply_state_reply_1", workspaceID, replyChannelID, memberID, "reply 1", 1, 2, now.Add(time.Minute))
	insertMessagingMessage(t, ctx, pool, "msg_reply_state_reply_2", workspaceID, replyChannelID, memberID, "reply 2", 2, 3, now.Add(2*time.Minute))
	insertMessagingMessage(t, ctx, pool, "msg_reply_state_reply_3", workspaceID, replyChannelID, memberID, "reply 3", 3, 4, now.Add(3*time.Minute))
	insertMessagingMessage(t, ctx, pool, "msg_reply_state_reply_4", workspaceID, replyChannelID, memberID, "reply 4", 4, 5, now.Add(4*time.Minute))
	recomputeVisibleSummaryForMessageTest(t, ctx, pool, replyChannelID, now.Add(4*time.Minute))

	tx, err := pool.BeginTx(ctx, coredb.TxOptions{})
	require.NoError(t, err)
	defer tx.Rollback(ctx)
	replyCount, lastReplyAt, err := LoadReplyThreadStatsForUpdate(ctx, tx, replyChannelID, parentMessageID)
	require.NoError(t, err)
	require.EqualValues(t, 4, replyCount)
	require.NotNil(t, lastReplyAt)

	participantIDs, err := LoadReplyThreadParticipantIDs(ctx, tx, replyChannelID, parentMessageID)
	require.NoError(t, err)
	require.Equal(t, []string{userID}, participantIDs)
}

func TestResolveChannelIDForAgentTargetAcceptsHumanMemberIDForDMTargets(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 3, 19, 14, 0, 0, 0, time.UTC)

	const (
		workspaceID   = "wspmsg3"
		peerUserID    = "usrmsg5"
		peerMemberID  = "human05"
		agentMemberID = "agent05"
		channelID     = "chnmsg31"
		channelThread = "thrmsg31"
		messageID     = "abcdef12deadbeef"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, peerUserID, "peer-target@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, peerMemberID, workspaceID, peerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, agentMemberID, workspaceID, now)
	insertMessagingChannel(
		t,
		ctx,
		pool,
		channelID,
		workspaceID,
		"general",
		peerMemberID,
		channelThread,
		now,
	)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 0, NULL, $3, $3, NULL, NULL)
ON DUPLICATE KEY UPDATE removed_at = NULL, archived_at = NULL, updated_at = VALUES(updated_at)
`, channelID, agentMemberID, now)
	insertMessagingMessage(t, ctx, pool, messageID, workspaceID, channelID, agentMemberID, "hello", 1, 1, now)

	actor := MemberActor{WorkspaceID: workspaceID, MemberID: agentMemberID}

	dmChannelID, err := ResolveChannelIDForAgentTarget(ctx, db, actor, "dm:@"+peerMemberID)
	require.NoError(t, err)
	require.NotEmpty(t, dmChannelID)

	access, err := ResolveDMThreadByIDForMember(ctx, db, agentMemberID, workspaceID, dmChannelID)
	require.NoError(t, err)
	require.Equal(t, peerMemberID, access.ChannelName)
	require.Equal(t, peerMemberID, access.PeerMemberID)

	legacyDMChannelID, err := ResolveChannelIDForAgentTarget(ctx, db, actor, "dm:@"+peerUserID)
	require.NoError(t, err)
	require.Equal(t, dmChannelID, legacyDMChannelID)

	replyChannelID, err := ResolveChannelIDForAgentTarget(ctx, db, actor, "#general:abcdef12")
	require.NoError(t, err)
	require.NotEmpty(t, replyChannelID)

	reusedReplyChannelID, err := ResolveChannelIDForAgentTarget(ctx, db, actor, "#general:abcdef12")
	require.NoError(t, err)
	require.Equal(t, replyChannelID, reusedReplyChannelID)

	var replyScopeCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM scopes
WHERE workspace_id = $1
  AND id = $2
  AND type = 'thread'
  AND parent_scope_id = $3
  AND root_message_id = $4
  AND status = 'active'
  AND archived_at IS NULL
`, workspaceID, replyChannelID, channelID, messageID).Scan(&replyScopeCount))
	require.Equal(t, 1, replyScopeCount)

	var participantCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM scope_memberships
WHERE scope_id = $1
  AND removed_at IS NULL
  AND archived_at IS NULL
  AND effective_to_seq IS NULL
`, replyChannelID).Scan(&participantCount))
	require.Zero(t, participantCount)
}

func TestResolveReplyThreadParentForMemberHidesPrivateChannelFromNonMembers(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 3, 23, 10, 0, 0, 0, time.UTC)

	const (
		workspaceID   = "wspmsg_private_parent"
		ownerUserID   = "usrmsg_private_owner"
		ownerMemberID = "human_private_owner"
		guestUserID   = "usrmsg_private_guest"
		guestMemberID = "human_private_guest"
		channelID     = "chnmsg_private_parent"
		channelThread = "thrmsg_private_parent"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "private-parent-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, guestUserID, "private-parent-guest@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingHumanMember(t, ctx, pool, guestMemberID, workspaceID, guestUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "private-room", ownerMemberID, channelThread, now)
	_, err := ResolveReplyThreadParentForMember(ctx, pool, guestMemberID, workspaceID, channelID)
	require.ErrorIs(t, err, ErrNotFound)
}

func TestResolveReplyThreadParentForMemberRejectsPublicChannelBeforeJoin(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 3, 23, 10, 5, 0, 0, time.UTC)

	const (
		workspaceID   = "wspmsg_public_parent"
		ownerUserID   = "usrmsg_public_owner"
		ownerMemberID = "human_public_owner"
		guestUserID   = "usrmsg_public_guest"
		guestMemberID = "human_public_guest"
		channelID     = "chnmsg_public_parent"
		channelThread = "thrmsg_public_parent"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "public-parent-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, guestUserID, "public-parent-guest@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingHumanMember(t, ctx, pool, guestMemberID, workspaceID, guestUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "public-room", ownerMemberID, channelThread, now)
	mustExecMessaging(t, ctx, pool, `
UPDATE scope_channel_profiles
SET visibility = 'public'
WHERE scope_id = $1
`, channelID)
	_, err := ResolveReplyThreadParentForMember(ctx, pool, guestMemberID, workspaceID, channelID)
	require.ErrorIs(t, err, ErrForbidden)
}

func TestResolveChannelIDForAgentTargetAcceptsAgentPublicNameForDMTargets(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	db := pool
	now := time.Date(2026, 3, 23, 11, 0, 0, 0, time.UTC)

	const (
		workspaceID      = "wspmsg4"
		ownerUserID      = "usrmsg6"
		machineID        = "mchmsg41"
		alphaAgentID     = "agtmsg41"
		alphaAgentMember = "memmsg41"
		betaAgentID      = "agtmsg42"
		betaAgentMember  = "memmsg42"
		alphaAgentName   = "alpha-agent"
		betaAgentName    = "beta-agent"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "owner@example.com", now)
	insertMessagingMachine(t, ctx, pool, machineID, workspaceID, ownerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, alphaAgentMember, workspaceID, now)
	insertMessagingAgentMember(t, ctx, pool, betaAgentMember, workspaceID, now)
	insertMessagingAgent(t, ctx, pool, alphaAgentID, workspaceID, machineID, alphaAgentMember, alphaAgentName, now)
	insertMessagingAgent(t, ctx, pool, betaAgentID, workspaceID, machineID, betaAgentMember, betaAgentName, now)

	actor := MemberActor{WorkspaceID: workspaceID, MemberID: betaAgentMember}

	dmThreadID, err := ResolveChannelIDForAgentTarget(ctx, db, actor, "dm:@"+alphaAgentName)
	require.NoError(t, err)
	require.NotEmpty(t, dmThreadID)

	access, err := ResolveDMThreadByIDForMember(ctx, db, betaAgentMember, workspaceID, dmThreadID)
	require.NoError(t, err)
	require.Equal(t, dmThreadID, access.ChannelID)
	require.Equal(t, alphaAgentName, access.ChannelName)
	require.Equal(t, alphaAgentMember, access.PeerMemberID)

	legacyDMThreadID, err := ResolveChannelIDForAgentTarget(ctx, db, actor, "dm:@"+alphaAgentMember)
	require.NoError(t, err)
	require.Equal(t, dmThreadID, legacyDMThreadID)

	readable, err := ResolveAccessibleThread(ctx, db, actor, domain.ThreadReference{
		Type:         domain.ThreadTypeDM,
		PeerMemberID: alphaAgentName,
	})
	require.NoError(t, err)
	require.Equal(t, dmThreadID, readable.ChannelID)
	require.Equal(t, dmThreadID, readable.DMThreadID)
}

func TestLoadScopeMentionedAgentRecipientsDoesNotMatchDisplayName(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 20, 9, 0, 0, 0, time.UTC)

	const (
		workspaceID      = "wspmsg_mentions_display"
		ownerUserID      = "usrmsg_mentions_display"
		ownerMemberID    = "human_mentions_display"
		channelID        = "chnmsg_mentions_display"
		channelThread    = "thrmsg_mentions_display"
		machineID        = "mchmsg_mentions_display"
		alphaAgentID     = "agtmsg_mentions_alpha"
		alphaAgentMember = "memmsg_mentions_alpha"
		betaAgentID      = "agtmsg_mentions_beta"
		betaAgentMember  = "memmsg_mentions_beta"
		sharedDisplay    = "shared-agent-label"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "mentions-display@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "mentions-display", ownerMemberID, channelThread, now)
	insertMessagingMachine(t, ctx, pool, machineID, workspaceID, ownerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, alphaAgentMember, workspaceID, now)
	insertMessagingAgentMember(t, ctx, pool, betaAgentMember, workspaceID, now)
	insertMessagingAgent(t, ctx, pool, alphaAgentID, workspaceID, machineID, alphaAgentMember, "alpha-agent", now)
	insertMessagingAgent(t, ctx, pool, betaAgentID, workspaceID, machineID, betaAgentMember, "beta-agent", now)
	mustExecMessaging(t, ctx, pool, `
UPDATE members
SET display_name = $3
WHERE workspace_id = $1
  AND id IN ($2, $4)
`, workspaceID, alphaAgentMember, sharedDisplay, betaAgentMember)
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, alphaAgentMember, "public", "", "auto-wake", 0, 0, now))
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, betaAgentMember, "public", "", "auto-wake", 0, 0, now))

	tx, err := pool.BeginTx(ctx, coredb.TxOptions{})
	require.NoError(t, err)
	defer tx.Rollback(ctx)

	recipients, err := LoadScopeMentionedAgentRecipients(ctx, tx, channelID, ownerMemberID, "@"+sharedDisplay+" should not route")
	require.NoError(t, err)
	require.Empty(t, recipients)

	recipients, err = LoadScopeMentionedAgentRecipients(ctx, tx, channelID, ownerMemberID, "@alpha-agent @"+betaAgentMember)
	require.NoError(t, err)
	require.Equal(t, map[string]string{
		alphaAgentMember: AgentDeliveryReasonDirectMention,
		betaAgentMember:  AgentDeliveryReasonDirectMention,
	}, agentDeliveryReasonsByMemberForMessagingTest(recipients))
}

func TestResolvedMentionsIncludeHumanAndServiceMembers(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 15, 20, 0, 0, time.UTC)

	const (
		workspaceID   = "wspmsg_resolved_members"
		ownerUserID   = "usrmsg_resolved_owner"
		ownerMemberID = "memmsg_resolved_owner"
		peerUserID    = "usrmsg_resolved_peer"
		peerMemberID  = "andc5vq"
		serviceID     = "svcmsg_resolved_github"
		appInstID     = "appinstmsg_resolved_github"
		channelID     = "chnmsg_resolved_members"
		channelThread = "thrmsg_resolved_members"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "resolved-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, peerUserID, "resolved-peer@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingHumanMember(t, ctx, pool, peerMemberID, workspaceID, peerUserID, now)
	mustExecMessaging(t, ctx, pool, `
UPDATE users
SET name = 'tangenta', display_name = 'Tangenta Display'
WHERE id = $1
`, peerUserID)
	insertMessagingServiceMember(t, ctx, pool, serviceID, workspaceID, "GitHub Service", now)
	var appID string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT id
FROM service_definitions
WHERE slug = 'github'
`).Scan(&appID))
	mustExecMessaging(t, ctx, pool, `
INSERT INTO service_installations (
	id,
	workspace_id,
	service_definition_id,
	service_kind,
	instance_key,
	status,
	installed_version,
	config,
	metadata,
	created_by_member_id,
	created_by_actor_type,
	created_by_actor_id,
	created_at,
	updated_at
)
VALUES ($1, $3, $2, 'app', 'default', 'active', '1.0.0', '{}', NULL, $4, 'human', $4, $5, $5)
`, appInstID, appID, workspaceID, serviceID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO service_members (
	id,
	workspace_id,
	service_definition_id,
	service_installation_id,
	member_id,
	service_kind,
	handle,
	display_name,
	avatar_url,
	status,
	created_at,
	updated_at
)
VALUES ('svcmem_msg_resolved', $2, $3, $1, $4, 'app', 'github', 'GitHub Service', '', 'active', $5, $5)
`, appInstID, workspaceID, appID, serviceID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "resolved-members", ownerMemberID, channelThread, now)
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, peerMemberID, "public", "", "joined", 0, 0, now))
	require.NoError(t, EnsureScopeMembership(ctx, pool, channelID, serviceID, "public", "", "joined", 0, 0, now))

	message := AppMessage{
		ID:        "msg_resolved_members",
		ChannelID: channelID,
		Content:   "please ask @andc5vq and @github",
	}
	resolvedByViewer, err := ResolveAppMessageMentionsForViewers(ctx, pool, workspaceID, message, []string{ownerMemberID})
	require.NoError(t, err)
	require.Equal(t, []ResolvedMention{
		{
			Token:         "andc5vq",
			Type:          "human",
			ID:            peerUserID,
			DisplayName:   "Tangenta Display",
			ResolvedToken: "tangenta",
		},
		{
			Token:         "github",
			Type:          "service",
			ID:            serviceID,
			DisplayName:   "GitHub Service",
			ResolvedToken: "github",
		},
	}, resolvedByViewer[ownerMemberID])

	messages := []AppMessage{message}
	require.NoError(t, AttachResolvedMentionsForAppMessages(ctx, pool, workspaceID, ownerMemberID, messages))
	require.Equal(t, resolvedByViewer[ownerMemberID], messages[0].ResolvedMentions)
}

func agentDeliveryReasonsByMemberForMessagingTest(recipients []AgentRecipient) map[string]string {
	reasons := make(map[string]string, len(recipients))
	for _, recipient := range recipients {
		reasons[recipient.AgentMemberID] = recipient.DeliveryReason
	}
	return reasons
}

func insertMessagingWorkspace(t *testing.T, ctx context.Context, pool coredb.QueryExecer, workspaceID string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO workspaces (id, organization_id, slug, name, latest_message_seq, created_at, updated_at)
VALUES ($1, $2, $3, $3, 0, $4, $4)
`, workspaceID, testfixture.SeedWorkspaceOrganization(t, ctx, pool, workspaceID, now), workspaceID, now)
}

func insertMessagingUser(t *testing.T, ctx context.Context, pool coredb.QueryExecer, userID string, email string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO users (id, email, name, display_name, avatar_url, is_admin, password_hash, email_verified_at, created_at, updated_at)
VALUES ($1, $2, $1, $1, '', FALSE, 'hash', $3, $3, $3)
`, userID, email, now)
}

func insertMessagingHumanMember(t *testing.T, ctx context.Context, pool coredb.QueryExecer, memberID string, workspaceID string, userID string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO members (id, workspace_id, type, display_name, user_id, workspace_role, created_at, updated_at)
VALUES ($1, $2, 'human', $1, $3, 'member', $4, $4)
`, memberID, workspaceID, userID, now)
}

func insertMessagingAgentMember(t *testing.T, ctx context.Context, pool coredb.QueryExecer, memberID string, workspaceID string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO members (id, workspace_id, type, display_name, user_id, workspace_role, created_at, updated_at)
VALUES ($1, $2, 'agent', $1, NULL, NULL, $3, $3)
`, memberID, workspaceID, now)
}

type messagingAutomationServiceActor struct {
	WorkspaceID         string
	CreatorMemberID     string
	DefinitionID        string
	InstallationID      string
	ServiceMemberRowID  string
	MemberID            string
	Slug                string
	Handle              string
	DefinitionName      string
	MemberDisplayName   string
	DefinitionAvatarURL string
	MemberAvatarURL     string
	Now                 time.Time
}

func insertMessagingAutomationServiceActor(
	t *testing.T,
	ctx context.Context,
	db coredb.DB,
	fixture messagingAutomationServiceActor,
) {
	t.Helper()

	actors := serviceactor.New(db)
	_, err := actors.UpsertDefinition(ctx, serviceactor.DefinitionUpsert{
		ID:               fixture.DefinitionID,
		ServiceKind:      serviceactor.KindApp,
		InstallationMode: serviceactor.InstallationModeSingleton,
		Slug:             fixture.Slug,
		Name:             fixture.DefinitionName,
		AvatarURL:        fixture.DefinitionAvatarURL,
		Status:           serviceactor.StatusActive,
		Now:              fixture.Now,
	})
	require.NoError(t, err)
	_, err = actors.UpsertInstallation(ctx, serviceactor.InstallationUpsert{
		ID:                  fixture.InstallationID,
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: fixture.DefinitionID,
		ServiceKind:         serviceactor.KindApp,
		InstanceKey:         serviceactor.DefaultInstanceKey,
		Status:              serviceactor.StatusActive,
		InstalledVersion:    "1",
		Config:              []byte(`{}`),
		Metadata:            []byte(`{}`),
		CreatedByMemberID:   fixture.CreatorMemberID,
		CreatedByActorType:  "human",
		CreatedByActorID:    fixture.CreatorMemberID,
		Now:                 fixture.Now,
	})
	require.NoError(t, err)
	_, err = actors.UpsertMember(ctx, serviceactor.MemberUpsert{
		ID:                    fixture.ServiceMemberRowID,
		WorkspaceID:           fixture.WorkspaceID,
		ServiceDefinitionID:   fixture.DefinitionID,
		ServiceInstallationID: fixture.InstallationID,
		MemberID:              fixture.MemberID,
		ServiceKind:           serviceactor.KindApp,
		Handle:                fixture.Handle,
		DisplayName:           fixture.MemberDisplayName,
		AvatarURL:             fixture.MemberAvatarURL,
		Status:                serviceactor.StatusActive,
		Now:                   fixture.Now,
	})
	require.NoError(t, err)
}

func insertMessagingServiceMember(t *testing.T, ctx context.Context, pool coredb.QueryExecer, memberID string, workspaceID string, displayName string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO members (id, workspace_id, type, display_name, user_id, workspace_role, created_at, updated_at)
VALUES ($1, $2, 'service', $3, NULL, NULL, $4, $4)
`, memberID, workspaceID, displayName, now)
}

func insertMessagingMachine(t *testing.T, ctx context.Context, pool coredb.QueryExecer, machineID string, workspaceID string, userID string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO machines (id, workspace_id, created_by_user_id, name, api_key, created_at, updated_at)
VALUES ($1, $2, $3, $1, 'machine-key', $4, $4)
`, machineID, workspaceID, userID, now)
}

func insertMessagingAgent(t *testing.T, ctx context.Context, pool coredb.QueryExecer, agentID string, workspaceID string, machineID string, memberID string, name string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT INTO agents (id, workspace_id, machine_id, member_id, name, runtime, model, description, auth_token_hash, activity_detail, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, 'codex', 'gpt-5', '', NULL, '', $6, $6)
`, agentID, workspaceID, machineID, memberID, name, now)
}

func insertMessagingChannel(t *testing.T, ctx context.Context, pool coredb.QueryExecer, channelID string, workspaceID string, slug string, ownerMemberID string, _ string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scopes (
    id, workspace_id, type, identity_key, latest_seq, latest_global_seq,
    latest_message_at, archived_until_seq, status, created_by_member_id,
    created_at, updated_at
)
VALUES ($1, $2, 'channel', $3, 0, 0, NULL, 0, 'active', $4, $5, $5)
`, channelID, workspaceID, "channel:"+channelID, ownerMemberID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_channel_profiles (
    scope_id, workspace_id, slug, display_name, description, visibility,
    owner_member_id, is_all_channel, created_at, updated_at
)
VALUES ($1, $2, $3, '', '', 'private', $4, FALSE, $5, $5)
`, channelID, workspaceID, slug, ownerMemberID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 0, NULL, $3, $3, NULL, NULL)
`, channelID, ownerMemberID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_notification_settings (
    scope_id, member_id, state, created_at, updated_at
)
VALUES ($1, $2, 'active', $3, $3)
`, channelID, ownerMemberID, now)
}

func insertMessagingDMThread(t *testing.T, ctx context.Context, pool coredb.QueryExecer, dmThreadID string, workspaceID string, memberAID string, memberBID string, _ string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scopes (
    id, workspace_id, type, identity_key, latest_seq, latest_global_seq,
    latest_message_at, archived_until_seq, status, created_at, updated_at
)
VALUES ($1, $2, 'dm', $3, 0, 0, NULL, 0, 'active', $4, $4)
`, dmThreadID, workspaceID, "dm:"+dmThreadID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES
    ($1, $2, 'public', NULL, 0, 0, 0, 0, 0, NULL, $4, $4, NULL, NULL),
    ($1, $3, 'public', NULL, 0, 0, 0, 0, 0, NULL, $4, $4, NULL, NULL)
`, dmThreadID, memberAID, memberBID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_notification_settings (
    scope_id, member_id, state, created_at, updated_at
)
VALUES
    ($1, $2, 'active', $4, $4),
    ($1, $3, 'active', $4, $4)
`, dmThreadID, memberAID, memberBID, now)
}

func insertMessagingReplyThread(t *testing.T, ctx context.Context, pool coredb.QueryExecer, threadChannelID string, workspaceID string, parentScopeID string, parentMessageID string, shortID string, _ string, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scopes (
    id, workspace_id, type, parent_scope_id, root_message_id, root_message_short_id,
    identity_key, latest_seq, latest_global_seq, latest_message_at, archived_until_seq,
    status, created_at, updated_at
)
VALUES ($1, $2, 'thread', $3, $4, $5, $6, 0, 0, NULL, 0, 'active', $7, $7)
`, threadChannelID, workspaceID, parentScopeID, parentMessageID, shortID, "thread:"+threadChannelID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_channel_profiles (
    scope_id, workspace_id, slug, display_name, description, visibility,
    owner_member_id, is_all_channel, created_at, updated_at
)
VALUES ($1, $2, $3, $3, '', 'private', NULL, FALSE, $4, $4)
`, threadChannelID, workspaceID, "thread-"+shortID, now)
}

func insertMessagingMessage(t *testing.T, ctx context.Context, pool coredb.QueryExecer, messageID string, workspaceID string, scopeID string, senderMemberID string, content string, seq int64, globalSeq int64, now time.Time) {
	t.Helper()

	mustExecMessaging(t, ctx, pool, `
INSERT IGNORE INTO scope_messages (
    id, workspace_id, scope_id, seq, workspace_global_seq,
    author_member_id, author_type, message_type, content, metadata, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, 'agent', '', $7, NULL, $8, $8)
`, messageID, workspaceID, scopeID, seq, globalSeq, senderMemberID, content, now)
	mustExecMessaging(t, ctx, pool, `
UPDATE scopes
SET latest_seq = $2,
    latest_global_seq = $3,
    latest_message_at = $4,
    visible_message_count = visible_message_count + 1,
    visible_last_message_at = $4,
    updated_at = $4
WHERE id = $1
`, scopeID, seq, globalSeq, now)
}

func recomputeVisibleSummaryForMessageTest(t *testing.T, ctx context.Context, pool coredb.DB, scopeID string, updatedAt time.Time) {
	t.Helper()

	tx, err := pool.BeginTx(ctx, coredb.TxOptions{})
	require.NoError(t, err)
	defer tx.Rollback(ctx)
	require.NoError(t, RecomputeScopeVisibleMessageStats(ctx, tx, scopeID, updatedAt))
	require.NoError(t, tx.Commit(ctx))
}

func TestRecomputeScopeVisibleMessageStatsExcludesWorkflowRunFrozenAuthor(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 8, 13, 11, 0, 0, 0, time.UTC)
	seedScopeApplierBaseData(t, ctx, db, now)

	workflowMetadata, _, err := EncodeMessageMetadata(BuildWorkflowContextMetadata(WorkflowContextMetadataParams{
		WorkspaceID: "workspace-applier",
		RunID:       "wri-recompute",
		EffectID:    "wfx-recompute",
	}))
	require.NoError(t, err)
	repos := NewScopeRepositories(db)
	require.NoError(t, repos.Messages.Insert(ctx, ScopeMessageRecord{
		ID: "msg-recompute-workflow", WorkspaceID: "workspace-applier", ScopeID: "scope-applier-thread",
		Seq: 2, WorkspaceGlobalSeq: 102, AuthorMemberID: "mem-applier-owner", AuthorType: ScopeActorHuman,
		MessageType: "", Content: "automatic workflow message", Metadata: workflowMetadata,
		CreatedAt: now.Add(time.Minute), UpdatedAt: now.Add(time.Minute),
	}))
	require.NoError(t, repos.Messages.Insert(ctx, ScopeMessageRecord{
		ID: "msg-recompute-agent", WorkspaceID: "workspace-applier", ScopeID: "scope-applier-thread",
		Seq: 3, WorkspaceGlobalSeq: 103, AuthorMemberID: "mem-applier-private", AuthorType: ScopeActorAgent,
		MessageType: "", Content: "actual agent reply", Metadata: []byte("{}"),
		CreatedAt: now.Add(2 * time.Minute), UpdatedAt: now.Add(2 * time.Minute),
	}))
	recomputeVisibleSummaryForMessageTest(t, ctx, db, "scope-applier-thread", now.Add(3*time.Minute))

	require.Equal(t, []string{"mem-applier-private"}, loadScopeRecentAuthorMemberIDsForTest(t, ctx, db, "scope-applier-thread"))
}

func sourceFromFunctionForMessageTest(t *testing.T, source string, signature string) string {
	t.Helper()

	start := strings.Index(source, signature)
	require.NotEqual(t, -1, start)
	rest := source[start+len(signature):]
	next := strings.Index(rest, "\nfunc ")
	if next == -1 {
		return source[start:]
	}
	return source[start : start+len(signature)+next]
}

func mustExecMessaging(t *testing.T, ctx context.Context, pool coredb.QueryExecer, sql string, args ...any) {
	t.Helper()

	_, err := pool.Exec(ctx, sql, args...)
	require.NoError(t, err)
}