package message

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

	"github.com/stretchr/testify/require"

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

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

	cursor := ConversationCursor{SortAt: time.Date(2026, 5, 25, 9, 0, 0, 0, time.UTC), ScopeID: "dm-9"}
	query, args, err := BuildListConversationsForUserScopedQuery("wsp-1", "mem-1", "usr-1", 25, cursor)
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "", cursor.SortAt, "dm-9", 26}, args)
	require.Contains(t, query, "visible_conversation_refs AS (")
	require.Contains(t, query, "channel_conversation_candidate_rows AS (")
	require.Contains(t, query, "channel_conversation_refs AS (")
	require.Contains(t, query, "dm_conversation_candidate_rows AS (")
	require.Contains(t, query, "dm_conversation_refs AS (")
	require.Contains(t, query, "thread_conversation_candidate_rows AS (")
	require.Contains(t, query, "thread_conversation_refs AS (")
	require.Contains(t, query, "service_account_conversation_candidate_rows AS (")
	require.Contains(t, query, "service_account_conversation_refs AS (")
	require.Contains(t, query, "FROM channel_conversation_candidate_rows")
	require.Contains(t, query, "FROM dm_conversation_candidate_rows")
	require.Contains(t, query, "FROM thread_conversation_candidate_rows")
	require.Contains(t, query, "FROM service_account_conversation_candidate_rows")
	require.Contains(t, query, "LEFT JOIN conversation_member_states cms")
	require.Contains(t, query, "cms.manual_unread_at IS NOT NULL")
	require.NotContains(t, query, "task_inbox_active")
	require.NotContains(t, query, "task_inbox_sort_at")
	require.Contains(t, query, "ORDER BY pinned_rank ASC, sort_at DESC, scope_id ASC")
	require.GreaterOrEqual(t, strings.Count(query, "LIMIT $7"), 5)
	require.Contains(t, query, "base_conversations AS (")
	require.Contains(t, query, "visible_conversations AS (")
	require.Contains(t, query, "page_conversations AS (")
	require.NotContains(t, query, "unread_counts AS (")
	require.NotContains(t, query, "task_projected_unread_counts AS (")
	require.Contains(t, query, "latest_visible_messages_ranked AS (")
	require.Contains(t, query, "m.created_at = pc.visible_last_message_at")
	require.Contains(t, query, "WHERE pc.visible_message_count > 0")
	require.Contains(t, query, "WHERE sidebar_hidden = FALSE")
	require.Contains(t, query, "pinned_rank = 1")
	require.Contains(t, query, "AND (sort_at < $5 OR (sort_at = $5 AND scope_id > $6))")
	require.Contains(t, query, "LIMIT $7")
	require.Contains(t, query, "s.type IN ('channel', 'dm', 'thread', 'service_account')")
	require.Contains(t, query, "s.type NOT IN ('dm', 'service_account')")
	require.Contains(t, query, "s.type = 'service_account' AND service_account.id IS NOT NULL")
	require.Contains(t, query, "LEFT JOIN service_accounts service_account")
	require.Contains(t, query, "NULLIF(service_account.name, '')")
	require.NotContains(t, query, "service_account.display_name")
	require.NotContains(t, query, "service_account.handle")
	require.Contains(t, query, "dm_peer.type = 'human' AND dm_peer_user.id IS NOT NULL AND dm_peer_user.id <> ''")
	require.Contains(t, query, "dm_peer.type = 'agent' AND dm_peer_agent.id IS NOT NULL AND dm_peer_agent.id <> ''")
	require.Contains(t, query, "AND (s.type <> 'channel' OR cp.subsystem IN ('im', 'team', 'tutorial'))")
	require.NotContains(t, query, "JOIN tasks active_tasks")
	require.NotContains(t, query, "last_high_signal_at")
	require.NotContains(t, query, "tsk.last_activity_at")
	require.NotContains(t, query, "task_message_unread_count")
	require.NotContains(t, query, "task_activity_unread_count")
	require.NotContains(t, strings.ToUpper(query), strings.ToUpper("coa"+"lesce"))
	require.Contains(t, query, "LEFT JOIN conversation_member_states")
	require.Contains(t, query, "cms.hidden_at IS NOT NULL")
	require.Contains(t, query, "ORDER BY pinned_rank ASC, sort_at DESC, scope_id ASC")
	require.NotContains(t, query, "JOIN scopes unread_scope")
	require.NotContains(t, query, "unread_scope.latest_seq > sm.last_read_seq")
	require.NotContains(t, query, "m.seq > sm.last_read_seq")
	require.Contains(t, query, "0 AS unread_count")
	require.Contains(t, query, "LEFT JOIN dm_view_states")
	require.Contains(t, query, "LEFT JOIN workspace_teams channel_team")
	require.Contains(t, query, "channel_team.channel_id = s.id")
	require.Contains(t, query, "AS subsystem")
	require.Contains(t, query, "AS team_id")
	require.Contains(t, query, "AS team_handle")
	require.Contains(t, query, "AS team_display_name")
	require.NotContains(t, query, "visible_thread_refs AS (")
	require.NotContains(t, query, "JOIN visible_thread_refs")
	require.NotContains(t, query, legacyTableNameForMessageTest("space", "subscriptions"))
	require.NotContains(t, query, legacyTableNameForMessageTest("space", "participants"))
	require.NotContains(t, query, legacyTableNameForMessageTest("dm", "threads"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("FROM", "threads"))
}

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

	query, args, err := BuildConversationForUserScopedQuery("wsp-1", "mem-1", "usr-1", "chn-1", false)
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "chn-1", "chn-1", false}, args)
	require.Contains(t, query, "visible_thread_refs AS (")
	require.Contains(t, query, "s.id = $4")
	require.Contains(t, query, "s.parent_scope_id = $4")
	require.Contains(t, query, "WHERE scope_id = $5")
	require.NotContains(t, query, "($4 = '' OR visible_threads.channel_id = $4 OR visible_threads.parent_scope_id = $4)")
}

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

	now := time.Date(2026, 6, 12, 10, 30, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	require.NoError(t, ClearConversationManualUnreadState(context.Background(), execer, "mem_1", "scope_1", now))
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "manual_unread_at = NULL")
	require.Contains(t, execer.queries[0], "manual_unread_seq = NULL")
	require.Contains(t, execer.queries[0], "manual_unread_at IS NOT NULL")
	require.NotContains(t, execer.queries[0], "task_message_unread_count")
	require.NotContains(t, execer.queries[0], "task_activity_unread_count")
}

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

	now := time.Date(2026, 6, 12, 10, 35, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	require.NoError(t, upsertConversationManualUnreadState(context.Background(), execer, "mem_1", "scope_1", 42, now))
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "updated_at = CASE")
	require.Contains(t, execer.queries[0], "manual_unread_seq <> VALUES(manual_unread_seq)")
	require.Contains(t, execer.queries[0], "ELSE updated_at")
	require.NotContains(t, execer.queries[0], "task_message_unread_count")
	require.NotContains(t, execer.queries[0], "task_activity_unread_count")
}

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

	query, args, err := BuildListConversationsForUserScopedQueryFiltered("wsp-1", "mem-1", "usr-1", 25, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"replyThread"}})
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "", "thread", 26}, args)
	require.Contains(t, query, "AND kind IN ($5)")
	require.Contains(t, query, "LIMIT $6")
	require.Contains(t, query, "thread_conversation_candidate_rows AS (")
	require.NotContains(t, query, "channel_conversation_candidate_rows AS (")
	require.NotContains(t, query, "dm_conversation_candidate_rows AS (")
	require.NotContains(t, query, "service_account_conversation_candidate_rows AS (")

	dmQuery, dmArgs, err := BuildListConversationsForUserScopedQueryFiltered("wsp-1", "mem-1", "usr-1", 25, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"dm"}})
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "", "dm", 26}, dmArgs)
	require.Contains(t, dmQuery, "dm_conversation_candidate_rows AS (")
	require.NotContains(t, dmQuery, "channel_conversation_candidate_rows AS (")
	require.NotContains(t, dmQuery, "thread_conversation_candidate_rows AS (")
	require.NotContains(t, dmQuery, "service_account_conversation_candidate_rows AS (")

	serviceAccountQuery, serviceAccountArgs, err := BuildListConversationsForUserScopedQueryFiltered("wsp-1", "mem-1", "usr-1", 25, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"serviceAccount"}})
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "", ConversationKindServiceAccount, 26}, serviceAccountArgs)
	require.Contains(t, serviceAccountQuery, "service_account_conversation_candidate_rows AS (")
	require.NotContains(t, serviceAccountQuery, "channel_conversation_candidate_rows AS (")
	require.NotContains(t, serviceAccountQuery, "dm_conversation_candidate_rows AS (")
	require.NotContains(t, serviceAccountQuery, "thread_conversation_candidate_rows AS (")

	visibleScopeQuery, visibleScopeArgs, err := BuildVisibleConversationScopeIDsForUserScopedQueryFiltered("wsp-1", "mem-1", "usr-1", ConversationKindFilter{Kinds: []string{"dm"}})
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "", "dm"}, visibleScopeArgs)
	require.Contains(t, visibleScopeQuery, "visible_thread_refs AS (")
	require.Contains(t, visibleScopeQuery, "AND s.type = 'dm'")
	require.NotContains(t, visibleScopeQuery, "WHERE s.workspace_id = $1\n          AND s.type = 'channel'\n          AND s.status = 'active'")
	require.NotContains(t, visibleScopeQuery, "WHERE s.workspace_id = $1\n          AND s.type = 'thread'\n          AND s.status = 'active'")
}

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

	query, args, err := BuildVisibleConversationScopeIDsForMemberPageQuery(
		"wsp-1",
		"mem-1",
		"usr-1",
		"chn-after",
		25,
	)
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "usr-1", "", "chn-after", 25}, args)
	require.Contains(t, query, "WHERE sidebar_hidden = FALSE")
	require.Contains(t, query, "s.status = 'active'")
	require.Contains(t, query, "s.archived_at IS NULL")
	require.Contains(t, query, "s.type IN ('channel', 'dm', 'thread', 'service_account')")
	require.Contains(t, query, "visible_inbox_scopes.scope_id > $5")
	require.Contains(t, query, "ORDER BY visible_inbox_scopes.scope_id")
	require.Contains(t, query, "LIMIT $6")
}

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

	pinnedAt := time.Date(2026, 5, 25, 9, 35, 0, 0, time.UTC)
	cursor := ConversationCursor{PinnedAt: &pinnedAt, SortAt: time.Date(2026, 5, 25, 9, 30, 0, 0, time.UTC), ScopeID: "chn-1"}
	decoded, err := normalizeConversationCursor(cursor)
	require.NoError(t, err)
	require.Equal(t, cursor, decoded)
}

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

	_, err := normalizeConversationCursor(ConversationCursor{ScopeID: "chn-1"})
	require.ErrorContains(t, err, "invalid conversation cursor")
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 10, 0, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "unified", now)

	store := New(db, nil, 0, Options{})
	page, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 2, ConversationCursor{})
	require.NoError(t, err)
	require.True(t, page.HasMore)
	require.Len(t, page.Items, 2)
	require.Equal(t, fixture.threadID, page.Items[0].ScopeID)
	require.Equal(t, ConversationKindThread, page.Items[0].Kind)
	require.Equal(t, fixture.channelID, page.Items[0].ParentScopeID)
	require.Equal(t, "reply hello", page.Items[0].Preview)
	require.Equal(t, int64(0), page.Items[0].UnreadCount)
	require.False(t, page.Items[0].SidebarHidden)

	require.Equal(t, fixture.channelID, page.Items[1].ScopeID)
	require.Equal(t, ConversationKindChannel, page.Items[1].Kind)
	require.Equal(t, "General", page.Items[1].Title)
	require.Equal(t, "thread root", page.Items[1].Preview)
	require.Equal(t, int64(0), page.Items[1].UnreadCount)
	require.False(t, page.Items[1].SidebarHidden)
	require.NotEmpty(t, page.NextCursor.ScopeID)

	next, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 10, page.NextCursor)
	require.NoError(t, err)
	require.False(t, next.HasMore)
	require.Len(t, next.Items, 1)
	require.Equal(t, fixture.humanDMID, next.Items[0].ScopeID)
	require.Equal(t, ConversationKindDM, next.Items[0].Kind)
	require.Equal(t, "human", next.Items[0].PeerType)
	require.Equal(t, fixture.peerUserID, next.Items[0].PeerID)
	require.Equal(t, "human dm hello", next.Items[0].Preview)
	for _, item := range append(page.Items, next.Items...) {
		require.NotEqual(t, fixture.removedPeerDMID, item.ScopeID, "DM conversations require an active human/agent peer")
		require.NotEqual(t, fixture.dmID, item.ScopeID, "hidden DM conversations should not occupy list pages")
	}
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 10, 15, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "visible_scope_page", now)

	query, args, err := BuildVisibleConversationScopeIDsForMemberPageQuery(
		fixture.workspaceID,
		fixture.ownerMemberID,
		fixture.ownerUserID,
		"",
		20,
	)
	require.NoError(t, err)
	rows, err := db.Query(ctx, query, args...)
	require.NoError(t, err)
	defer rows.Close()

	scopeIDs := make([]string, 0)
	for rows.Next() {
		var scopeID string
		require.NoError(t, rows.Scan(&scopeID))
		scopeIDs = append(scopeIDs, scopeID)
	}
	require.NoError(t, rows.Err())
	require.ElementsMatch(
		t,
		[]string{fixture.channelID, fixture.humanDMID, fixture.threadID},
		scopeIDs,
	)
	require.NotContains(t, scopeIDs, fixture.dmID, "hidden DM must not participate in the Inbox unread aggregate")
	require.NotContains(t, scopeIDs, fixture.removedPeerDMID, "orphaned DM must not participate in the Inbox unread aggregate")
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 10, 30, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "filtered", now)
	store := New(db, nil, 0, Options{})

	page, err := store.ListConversationsForUserFiltered(ctx, fixture.ownerUserID, fixture.workspaceID, 10, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"replyThread"}})
	require.NoError(t, err)
	require.False(t, page.HasMore)
	require.Len(t, page.Items, 1)
	require.Equal(t, fixture.threadID, page.Items[0].ScopeID)
	require.Equal(t, ConversationKindThread, page.Items[0].Kind)

	dmPage, err := store.ListConversationsForUserFiltered(ctx, fixture.ownerUserID, fixture.workspaceID, 10, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"dm"}})
	require.NoError(t, err)
	require.Len(t, dmPage.Items, 1)
	require.Equal(t, fixture.humanDMID, dmPage.Items[0].ScopeID)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 10, 45, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "kb_hidden", now)
	kbChannelID := "chn_conv_kb_hidden_carrier"
	kbRootMessageID := "msg_conv_kb_hidden_carrier_root"
	kbThreadID := "thr_conv_kb_hidden_carrier"

	insertMessagingChannel(t, ctx, db, kbChannelID, fixture.workspaceID, "kb-hidden-carrier", fixture.ownerMemberID, "", now.Add(8*time.Minute))
	mustExecMessaging(t, ctx, db, `
UPDATE scope_channel_profiles
SET subsystem = $2, updated_at = $3
WHERE scope_id = $1
`, kbChannelID, ChannelSubsystemKB, now.Add(8*time.Minute))
	insertMessagingHumanScopeMessage(t, ctx, db, kbRootMessageID, fixture.workspaceID, kbChannelID, "", fixture.peerMemberID, "kb carrier unread", 1, 7, now.Add(9*time.Minute))
	insertMessagingReplyThread(t, ctx, db, kbThreadID, fixture.workspaceID, kbChannelID, kbRootMessageID, "kbhidden", "", now.Add(10*time.Minute))
	mustExecMessaging(t, ctx, db, `
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)
`, kbThreadID, fixture.ownerMemberID, now.Add(10*time.Minute))
	mustExecMessaging(t, ctx, db, `
INSERT INTO scope_notification_settings (scope_id, member_id, state, created_at, updated_at)
VALUES ($1, $2, 'active', $3, $3)
`, kbThreadID, fixture.ownerMemberID, now.Add(10*time.Minute))
	insertMessagingHumanScopeMessage(t, ctx, db, "msg_conv_kb_hidden_reply", fixture.workspaceID, kbThreadID, "", fixture.peerMemberID, "kb retrieval reply", 1, 8, now.Add(11*time.Minute))

	store := New(db, nil, 0, Options{})
	channelPage, err := store.ListConversationsForUserFiltered(ctx, fixture.ownerUserID, fixture.workspaceID, 20, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"channel"}})
	require.NoError(t, err)
	for _, item := range channelPage.Items {
		require.NotEqual(t, kbChannelID, item.ScopeID, "hidden KB subsystem channel must not appear as an Inbox channel row")
	}

	threadPage, err := store.ListConversationsForUserFiltered(ctx, fixture.ownerUserID, fixture.workspaceID, 20, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"replyThread"}})
	require.NoError(t, err)
	require.Contains(t, conversationScopeIDsForTest(threadPage.Items), kbThreadID, "KB retrieval threads stay visible in Inbox")
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 11, 0, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "pinned", now)
	humanDMPinnedAt := now.Add(20 * time.Minute)
	channelPinnedAt := now.Add(10 * time.Minute)

	mustExecMessaging(t, ctx, db, `
UPDATE scope_memberships
SET last_read_seq = 1,
    last_read_global_seq = 5,
    updated_at = $3
WHERE scope_id = $1 AND member_id = $2
`, fixture.humanDMID, fixture.ownerMemberID, now)
	mustExecMessaging(t, ctx, db, `
INSERT INTO conversation_member_states (
    scope_id, member_id, pinned_at, manual_unread_at, manual_unread_seq,
    hidden_at, hidden_through_seq, created_at, updated_at
) VALUES
    ($1, $3, $4, $4, 1, NULL, NULL, $6, $6),
    ($2, $3, $5, NULL, NULL, NULL, NULL, $6, $6)
`, fixture.humanDMID, fixture.channelID, fixture.ownerMemberID, humanDMPinnedAt, channelPinnedAt, now)

	store := New(db, nil, 0, Options{})
	page, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 1, ConversationCursor{})
	require.NoError(t, err)
	require.True(t, page.HasMore)
	require.Len(t, page.Items, 1)
	require.Equal(t, fixture.channelID, page.Items[0].ScopeID)
	require.Equal(t, channelPinnedAt, *page.Items[0].PinnedAt)
	require.False(t, page.Items[0].ManualUnread)
	require.Equal(t, int64(0), page.Items[0].UnreadCount, "manual unread should not fabricate a message count")
	require.NotNil(t, page.NextCursor.PinnedAt)

	next, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 10, page.NextCursor)
	require.NoError(t, err)
	require.False(t, next.HasMore)
	require.GreaterOrEqual(t, len(next.Items), 2)
	require.Equal(t, fixture.humanDMID, next.Items[0].ScopeID)
	require.Equal(t, humanDMPinnedAt, *next.Items[0].PinnedAt)
	require.True(t, next.Items[0].ManualUnread)
	require.Equal(t, fixture.threadID, next.Items[1].ScopeID)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 12, 0, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "actions", now)
	store := New(db, nil, 0, Options{})

	var initialReadSeq int64
	var initialReadGlobal int64
	require.NoError(t, db.QueryRow(ctx, `
SELECT last_read_seq, last_read_global_seq
FROM scope_memberships
WHERE scope_id = $1 AND member_id = $2
`, fixture.channelID, fixture.ownerMemberID).Scan(&initialReadSeq, &initialReadGlobal))

	manual, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "channel:"+fixture.channelID, ConversationActionMarkUnread)
	require.NoError(t, err)
	require.Equal(t, fixture.channelID, manual.ScopeID)
	require.True(t, manual.ManualUnread)
	require.True(t, manual.ManualUnreadGenerationKnown)
	require.Greater(t, manual.ManualUnreadGeneration, int64(0))

	var readSeqBefore int64
	var readGlobalBefore int64
	require.NoError(t, db.QueryRow(ctx, `
SELECT last_read_seq, last_read_global_seq
FROM scope_memberships
WHERE scope_id = $1 AND member_id = $2
`, fixture.channelID, fixture.ownerMemberID).Scan(&readSeqBefore, &readGlobalBefore))
	require.Equal(t, initialReadSeq, readSeqBefore, "mark unread must not roll back the real read cursor")
	require.Equal(t, initialReadGlobal, readGlobalBefore, "mark unread must not roll back the real read cursor")

	read, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "channel:"+fixture.channelID, ConversationActionMarkRead)
	require.NoError(t, err)
	require.False(t, read.ManualUnread)
	require.Equal(t, int64(0), read.UnreadCount)
	require.True(t, read.ManualUnreadGenerationKnown)
	require.Greater(t, read.ManualUnreadGeneration, manual.ManualUnreadGeneration)
	var readSeqAfter int64
	var readGlobalAfter int64
	require.NoError(t, db.QueryRow(ctx, `
SELECT last_read_seq, last_read_global_seq
FROM scope_memberships
WHERE scope_id = $1 AND member_id = $2
`, fixture.channelID, fixture.ownerMemberID).Scan(&readSeqAfter, &readGlobalAfter))
	require.GreaterOrEqual(t, readSeqAfter, readSeqBefore)
	require.GreaterOrEqual(t, readGlobalAfter, readGlobalBefore)

	pinned, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "thread:"+fixture.threadID, ConversationActionPin)
	require.NoError(t, err)
	require.NotNil(t, pinned.PinnedAt)
	unpinned, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "thread:"+fixture.threadID, ConversationActionUnpin)
	require.NoError(t, err)
	require.Nil(t, unpinned.PinnedAt)

	muted, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "thread:"+fixture.threadID, ConversationActionMute)
	require.NoError(t, err)
	require.Equal(t, HumanScopeSubscriptionStateMute, muted.SubscriptionState)
	unmuted, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "thread:"+fixture.threadID, ConversationActionUnmute)
	require.NoError(t, err)
	require.Equal(t, HumanScopeSubscriptionStateActive, unmuted.SubscriptionState)

	hidden, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "dm:"+fixture.humanDMID, ConversationActionHide)
	require.NoError(t, err)
	require.True(t, hidden.SidebarHidden)
	require.Positive(t, hidden.HiddenThroughSeq)

	page, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 20, ConversationCursor{})
	require.NoError(t, err)
	for _, item := range page.Items {
		require.NotEqual(t, fixture.humanDMID, item.ScopeID)
	}
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 12, 10, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "deleted_agent_hide", now)
	store := New(db, nil, 0, Options{})

	mustExecMessaging(t, ctx, db, `
DELETE FROM agents
WHERE workspace_id = $1 AND id = $2
`, fixture.workspaceID, fixture.agentID)

	_, err := store.LoadConversationForUser(ctx, fixture.ownerUserID, fixture.workspaceID, fixture.dmID, true)
	require.ErrorIs(t, err, ErrNotFound)

	page, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 20, ConversationCursor{})
	require.NoError(t, err)
	for _, item := range page.Items {
		require.NotEqual(t, fixture.dmID, item.ScopeID)
	}

	hidden, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "dm:"+fixture.dmID, ConversationActionHide)
	require.NoError(t, err)
	require.Equal(t, fixture.dmID, hidden.ScopeID)
	require.Equal(t, ConversationKindDM, hidden.Kind)
	require.Equal(t, "agent", hidden.PeerType)
	require.True(t, hidden.SidebarHidden)
	require.Equal(t, int64(1), hidden.HiddenThroughSeq)

	var hiddenAt time.Time
	var hiddenThroughSeq int64
	require.NoError(t, db.QueryRow(ctx, `
SELECT hidden_at, hidden_through_seq
FROM conversation_member_states
WHERE scope_id = $1 AND member_id = $2
`, fixture.dmID, fixture.ownerMemberID).Scan(&hiddenAt, &hiddenThroughSeq))
	require.False(t, hiddenAt.IsZero())
	require.Equal(t, int64(1), hiddenThroughSeq)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 7, 14, 12, 20, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "deleted_platform_sa_hide", now)
	store := New(db, nil, 0, Options{})
	scopeID := "sasc_conv_deleted_platform_sa"
	platformAccountID := "psa_conv_deleted_platform_sa"
	accountID := "sacct_conv_deleted_platform_sa"
	installationID := "svcinst_conv_deleted_platform_sa"
	serviceProjectionID := "svcmem_conv_deleted_platform_sa"
	serviceAuthorMemberID := "mem_conv_deleted_platform_sa"

	insertMessagingServiceMember(t, ctx, db, serviceAuthorMemberID, fixture.workspaceID, "Deleted Platform Service", now)
	repos := NewScopeRepositories(db)
	require.NoError(t, repos.Scopes.UpsertScope(ctx, ScopeRecord{
		ID:                scopeID,
		WorkspaceID:       fixture.workspaceID,
		Type:              ConversationKindServiceAccount,
		IdentityKey:       "service_account:" + fixture.workspaceID + ":" + accountID,
		LatestSeq:         1,
		LatestGlobalSeq:   11,
		LatestMessageAt:   &now,
		Status:            "active",
		CreatedByMemberID: fixture.ownerMemberID,
		CreatedAt:         now,
		UpdatedAt:         now,
	}))
	require.NoError(t, repos.Memberships.Upsert(ctx, ScopeMembershipRecord{
		ScopeID:           scopeID,
		MemberID:          fixture.ownerMemberID,
		Visibility:        "public",
		EffectiveFromSeq:  1,
		LastReadSeq:       0,
		LastReadGlobalSeq: 0,
		CreatedAt:         now,
		UpdatedAt:         now,
	}))
	mustExecMessaging(t, ctx, db, `
INSERT INTO platform_service_accounts (
    id, name, description, provisioning_state, created_by_operator_user_id, created_at, updated_at
) VALUES ($1, 'Deleted Platform Service', '', 'active', $2, $3, $3)
`, platformAccountID, fixture.ownerUserID, now)
	mustExecMessaging(t, ctx, db, `
INSERT INTO service_installations (
    id, workspace_id, service_definition_id, service_kind, instance_key,
    created_by_member_id, created_by_actor_type, created_by_actor_id,
    created_at, updated_at
) VALUES ($1, $2, 'svcdef_service_account', 'service_account', $3, $4, 'human', $4, $5, $5)
`, installationID, fixture.workspaceID, accountID, fixture.ownerMemberID, now)
	mustExecMessaging(t, ctx, db, `
INSERT INTO service_members (
    id, workspace_id, service_definition_id, service_installation_id,
    member_id, service_kind, handle, display_name, status, created_at, updated_at
) VALUES ($1, $2, 'svcdef_service_account', $3, $4, 'service_account', 'deleted-platform-service', 'Deleted Platform Service', 'active', $5, $5)
`, serviceProjectionID, fixture.workspaceID, installationID, serviceAuthorMemberID, now)
	mustExecMessaging(t, ctx, db, `
INSERT INTO service_accounts (
    id, principal_id, workspace_id, management_kind, platform_service_account_id,
    service_installation_id, service_member_id, author_member_id,
    message_scope_id, name, description,
    applied_platform_profile_version, audience_policy_version, effective_audience_version,
    audience_state, version, created_by_operator_user_id, created_at, updated_at
) VALUES (
    $1, $2, $3, 'platform', $2,
    $4, $5, $6,
    $7, 'Deleted Platform Service', '',
    1, 1, 1,
    'ready', 1, $8, $9, $9
)
`, accountID, platformAccountID, fixture.workspaceID, installationID, serviceProjectionID, serviceAuthorMemberID, scopeID, fixture.ownerUserID, now)
	require.NoError(t, repos.Messages.Insert(ctx, ScopeMessageRecord{
		ID:                 "msg_conv_deleted_platform_sa",
		WorkspaceID:        fixture.workspaceID,
		ScopeID:            scopeID,
		Seq:                1,
		WorkspaceGlobalSeq: 11,
		AuthorMemberID:     serviceAuthorMemberID,
		AuthorType:         "service",
		MessageType:        "text",
		Content:            "this service account will be deleted",
		Metadata:           []byte(`{}`),
		CreatedAt:          now,
		UpdatedAt:          now,
	}))

	active, err := store.LoadConversationForUser(ctx, fixture.ownerUserID, fixture.workspaceID, scopeID, true)
	require.NoError(t, err)
	require.Equal(t, ConversationKindServiceAccount, active.Kind)
	require.Equal(t, scopeID, active.ScopeID)

	mustExecMessaging(t, ctx, db, `
DELETE FROM service_accounts
WHERE id = $1 AND workspace_id = $2
`, accountID, fixture.workspaceID)
	mustExecMessaging(t, ctx, db, `DELETE FROM service_installations WHERE id = $1`, installationID)
	mustExecMessaging(t, ctx, db, `DELETE FROM scopes WHERE id = $1 AND workspace_id = $2`, scopeID, fixture.workspaceID)
	mustExecMessaging(t, ctx, db, `DELETE FROM members WHERE id = $1 AND workspace_id = $2 AND type = 'service'`, serviceAuthorMemberID, fixture.workspaceID)
	mustExecMessaging(t, ctx, db, `DELETE FROM platform_service_accounts WHERE id = $1`, platformAccountID)

	_, err = store.LoadConversationForUser(ctx, fixture.ownerUserID, fixture.workspaceID, scopeID, true)
	require.ErrorIs(t, err, ErrNotFound)

	_, err = store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "serviceAccount:"+scopeID, ConversationActionHide)
	require.ErrorIs(t, err, ErrNotFound)
	_, err = store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "serviceAccount:"+scopeID, ConversationActionMarkRead)
	require.ErrorIs(t, err, ErrNotFound)
	_, err = store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "channel:"+scopeID, ConversationActionHide)
	require.ErrorIs(t, err, ErrNotFound)
	_, err = store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "serviceAccount:not_a_service_account_scope", ConversationActionHide)
	require.ErrorIs(t, err, ErrNotFound)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 25, 12, 30, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "bulk_read", now)
	store := New(db, nil, 0, Options{})

	_, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "channel:"+fixture.channelID, ConversationActionMarkUnread)
	require.NoError(t, err)
	_, err = store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "thread:"+fixture.threadID, ConversationActionMarkUnread)
	require.NoError(t, err)

	result, err := store.MarkConversationsReadForUser(ctx, fixture.ownerUserID, fixture.workspaceID, ConversationKindFilter{Kinds: []string{"replyThread"}})
	require.NoError(t, err)
	require.Equal(t, int64(1), result.UpdatedCount)

	thread, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "thread:"+fixture.threadID, ConversationActionPin)
	require.NoError(t, err)
	require.False(t, thread.ManualUnread)
	require.Equal(t, int64(0), thread.UnreadCount)

	channelPage, err := store.ListConversationsForUserFiltered(ctx, fixture.ownerUserID, fixture.workspaceID, 10, ConversationCursor{}, ConversationKindFilter{Kinds: []string{"channel"}})
	require.NoError(t, err)
	require.Len(t, channelPage.Items, 1)
	require.True(t, channelPage.Items[0].ManualUnread, "bulk thread read must not clear channel manual unread")
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 7, 10, 7, 0, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "activity_scope_seq", now)
	mustExecMessaging(t, ctx, db, `
INSERT INTO scope_activity_events (
    id, workspace_id, scope_id, seq, activity_type, activity_ref_type,
    activity_ref_id, activity_global_seq, actor_member_id, reason, payload, created_at
) VALUES ($1, $2, $3, 3, 'reaction.added', 'reaction', $1, 9003, $4, 'reaction', '{}', $5)
`, "act_conv_scope_seq", fixture.workspaceID, fixture.channelID, fixture.peerMemberID, now.Add(10*time.Minute))

	store := New(db, nil, 0, Options{})
	read, err := store.PerformConversationActionForUser(ctx, fixture.ownerUserID, fixture.workspaceID, "channel:"+fixture.channelID, ConversationActionMarkRead)
	require.NoError(t, err)
	require.Equal(t, int64(3), read.LastActivityReadSeq)

	var persisted int64
	require.NoError(t, db.QueryRow(ctx, `
SELECT last_activity_global_seq
FROM scope_memberships
WHERE scope_id = $1 AND member_id = $2 AND effective_to_seq IS NULL
`, fixture.channelID, fixture.ownerMemberID).Scan(&persisted))
	require.Equal(t, int64(3), persisted, "activity cursor uses scope-local seq, not historical activity_global_seq")
}

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

	sourceBytes, err := os.ReadFile("conversations.go")
	require.NoError(t, err)
	source := string(sourceBytes)
	require.NotContains(t, source, legacyTableNameForMessageTest("space", "subscriptions"))
	require.NotContains(t, source, legacyTableNameForMessageTest("space", "participants"))
	require.NotContains(t, source, legacyTableNameForMessageTest("dm", "threads"))
	require.NotContains(t, source, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, source, legacySQLTableRefForMessageTest("FROM", "threads"))
	require.NotContains(t, strings.ToUpper(source), strings.ToUpper("coa"+"lesce"))
}

func conversationScopeIDsForTest(items []ConversationItem) []string {
	ids := make([]string, 0, len(items))
	for _, item := range items {
		ids = append(ids, item.ScopeID)
	}
	return ids
}

type conversationListFixture struct {
	workspaceID         string
	ownerUserID         string
	ownerMemberID       string
	peerUserID          string
	peerMemberID        string
	agentMemberID       string
	removedPeerMemberID string
	agentID             string
	humanDMID           string
	removedPeerDMID     string
	machineID           string
	channelID           string
	dmID                string
	threadID            string
	parentMessage       string
}

func seedConversationListFixture(t *testing.T, ctx context.Context, db QueryExecerForConversationTest, suffix string, now time.Time) conversationListFixture {
	t.Helper()

	fixture := conversationListFixture{
		workspaceID:         "wsp_conv_" + suffix,
		ownerUserID:         "usr_conv_owner_" + suffix,
		ownerMemberID:       "mem_conv_owner_" + suffix,
		peerUserID:          "usr_conv_peer_" + suffix,
		peerMemberID:        "mem_conv_peer_" + suffix,
		agentMemberID:       "mem_conv_agent_" + suffix,
		removedPeerMemberID: "mem_conv_removed_peer_" + suffix,
		agentID:             "agt_conv_" + suffix,
		humanDMID:           "dm_human_conv_" + suffix,
		removedPeerDMID:     "dm_removed_peer_conv_" + suffix,
		machineID:           "mac_conv_" + suffix,
		channelID:           "chn_conv_" + suffix,
		dmID:                "dm_conv_" + suffix,
		threadID:            "thr_conv_" + suffix,
		parentMessage:       "msg_conv_parent_" + suffix,
	}

	insertMessagingWorkspace(t, ctx, db, fixture.workspaceID, now)
	insertMessagingUser(t, ctx, db, fixture.ownerUserID, fixture.ownerUserID+"@example.com", now)
	insertMessagingUser(t, ctx, db, fixture.peerUserID, fixture.peerUserID+"@example.com", now)
	insertMessagingHumanMember(t, ctx, db, fixture.ownerMemberID, fixture.workspaceID, fixture.ownerUserID, now)
	insertMessagingHumanMember(t, ctx, db, fixture.peerMemberID, fixture.workspaceID, fixture.peerUserID, now)
	insertMessagingAgentMember(t, ctx, db, fixture.agentMemberID, fixture.workspaceID, now)
	insertMessagingAgentMember(t, ctx, db, fixture.removedPeerMemberID, fixture.workspaceID, now)
	insertMessagingMachine(t, ctx, db, fixture.machineID, fixture.workspaceID, fixture.ownerUserID, now)
	insertMessagingAgent(t, ctx, db, fixture.agentID, fixture.workspaceID, fixture.machineID, fixture.agentMemberID, "helper-agent", now)
	insertMessagingDMThread(t, ctx, db, fixture.humanDMID, fixture.workspaceID, fixture.ownerMemberID, fixture.peerMemberID, "", now.Add(-time.Minute))
	insertMessagingHumanScopeMessage(t, ctx, db, "msg_conv_human_dm_"+suffix, fixture.workspaceID, fixture.humanDMID, "", fixture.peerMemberID, "human dm hello", 1, 5, now.Add(30*time.Second))
	insertMessagingDMThread(t, ctx, db, fixture.removedPeerDMID, fixture.workspaceID, fixture.ownerMemberID, fixture.removedPeerMemberID, "", now.Add(-2*time.Minute))
	insertMessagingMessage(t, ctx, db, "msg_conv_removed_peer_dm_"+suffix, fixture.workspaceID, fixture.removedPeerDMID, fixture.removedPeerMemberID, "removed peer should not surface", 1, 6, now.Add(45*time.Second))
	mustExecMessaging(t, ctx, db, `
UPDATE scope_memberships
SET removed_at = $3, updated_at = $3
WHERE scope_id = $1 AND member_id = $2
`, fixture.removedPeerDMID, fixture.removedPeerMemberID, now.Add(time.Minute))

	insertMessagingChannel(t, ctx, db, fixture.channelID, fixture.workspaceID, "general", fixture.ownerMemberID, "", now)
	mustExecMessaging(t, ctx, db, `UPDATE scope_channel_profiles SET display_name = 'General' WHERE scope_id = $1`, fixture.channelID)
	mustExecMessaging(t, ctx, db, `
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)
`, fixture.channelID, fixture.peerMemberID, now)

	insertMessagingHumanScopeMessage(t, ctx, db, "msg_conv_channel_"+suffix, fixture.workspaceID, fixture.channelID, "", fixture.peerMemberID, "channel hello", 1, 1, now.Add(time.Minute))
	mustExecMessaging(t, ctx, db, `
UPDATE scope_memberships
SET last_read_seq = 1,
    last_read_global_seq = 1
WHERE scope_id = $1 AND member_id = $2
`, fixture.channelID, fixture.ownerMemberID)

	insertMessagingHumanScopeMessage(t, ctx, db, fixture.parentMessage, fixture.workspaceID, fixture.channelID, "", fixture.ownerMemberID, "thread root", 2, 2, now.Add(2*time.Minute))
	insertMessagingReplyThread(t, ctx, db, fixture.threadID, fixture.workspaceID, fixture.channelID, fixture.parentMessage, "conv"+suffix, "", now.Add(3*time.Minute))
	mustExecMessaging(t, ctx, db, `
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)
`, fixture.threadID, fixture.ownerMemberID, now.Add(3*time.Minute))
	mustExecMessaging(t, ctx, db, `
INSERT INTO scope_notification_settings (scope_id, member_id, state, created_at, updated_at)
VALUES ($1, $2, 'active', $3, $3)
`, fixture.threadID, fixture.ownerMemberID, now.Add(3*time.Minute))
	insertMessagingHumanScopeMessage(t, ctx, db, "msg_conv_reply_"+suffix, fixture.workspaceID, fixture.threadID, "", fixture.peerMemberID, "reply hello", 1, 3, now.Add(4*time.Minute))

	insertMessagingDMThread(t, ctx, db, fixture.dmID, fixture.workspaceID, fixture.ownerMemberID, fixture.agentMemberID, "", now.Add(5*time.Minute))
	insertMessagingMessage(t, ctx, db, "msg_conv_dm_"+suffix, fixture.workspaceID, fixture.dmID, fixture.agentMemberID, "agent hello", 1, 4, now.Add(6*time.Minute))
	mustExecMessaging(t, ctx, db, `
INSERT INTO dm_view_states (dm_thread_id, member_id, hidden_through_seq, updated_at)
VALUES ($1, $2, 1, $3)
`, fixture.dmID, fixture.ownerMemberID, now.Add(7*time.Minute))

	return fixture
}

type QueryExecerForConversationTest interface {
	coredb.QueryExecer
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 5, 28, 9, 30, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "taskexit", now)
	taskID := "tsk_conv_taskexit"
	taskScopeID := taskID
	insertConversationTaskForTest(t, ctx, db, conversationTaskFixture{
		WorkspaceID:       fixture.workspaceID,
		TaskID:            taskID,
		TaskScopeID:       taskScopeID,
		BoardID:           "tb_conv_taskexit",
		ChannelID:         fixture.channelID,
		TaskNumber:        177,
		Title:             "Excluded task",
		Status:            "in_progress",
		CreatedByMemberID: fixture.ownerMemberID,
		OwnerMemberID:     fixture.ownerMemberID,
		Now:               now.Add(8 * time.Minute),
	})
	insertMessagingHumanScopeMessage(t, ctx, db, "msg_conv_taskexit_discussion", fixture.workspaceID, taskScopeID, "", fixture.peerMemberID, "task discussion", 1, 7, now.Add(9*time.Minute))
	insertConversationTaskActivityForTest(t, ctx, db, fixture.workspaceID, taskScopeID, "tev_conv_taskexit_checkpoint", 1, "task.checkpoint_added", fixture.peerMemberID, "checkpoint", now.Add(10*time.Minute))

	store := New(db, nil, 0, Options{})
	page, err := store.ListConversationsForUser(ctx, fixture.ownerUserID, fixture.workspaceID, 20, ConversationCursor{})
	require.NoError(t, err)
	for _, item := range page.Items {
		require.NotEqual(t, taskScopeID, item.ScopeID, "task scopes must not appear in Inbox conversation list")
		require.NotEqual(t, ConversationKindTask, item.Kind, "task kind must not appear in Inbox conversation list")
	}

	_, err = store.LoadConversationForUser(ctx, fixture.ownerUserID, fixture.workspaceID, taskScopeID, false)
	require.ErrorIs(t, err, ErrNotFound, "task scopes are not loadable as Inbox conversations")
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 7, 29, 5, 0, 0, 0, time.UTC)
	fixture := seedConversationListFixture(t, ctx, db, "task_message_hide_frontier", now)
	taskID := "tsk_conv_task_message_hide_frontier"
	taskScopeID := taskID
	insertConversationTaskForTest(t, ctx, db, conversationTaskFixture{
		WorkspaceID:       fixture.workspaceID,
		TaskID:            taskID,
		TaskScopeID:       taskScopeID,
		BoardID:           "tb_conv_task_message_hide_frontier",
		ChannelID:         fixture.channelID,
		TaskNumber:        178,
		Title:             "Message-only Task hide",
		Status:            "in_progress",
		CreatedByMemberID: fixture.ownerMemberID,
		OwnerMemberID:     fixture.ownerMemberID,
		Now:               now,
	})

	hiddenAt := now.Add(time.Minute)
	mustExecMessaging(t, ctx, db, `
UPDATE scopes
SET latest_seq = 5,
    latest_activity_seq = 6,
    updated_at = $2
WHERE id = $1
`, taskScopeID, hiddenAt)
	mustExecMessaging(t, ctx, db, `
INSERT INTO conversation_member_states (
  scope_id, member_id, hidden_at, hidden_through_seq,
  hidden_through_activity_seq, created_at, updated_at
) VALUES ($1, $2, $3, 5, 5, $3, $3)
`, taskScopeID, fixture.ownerMemberID, hiddenAt)
	mustExecMessaging(t, ctx, db, `
INSERT INTO im_conversation_index (
  workspace_id, member_id, conversation_id, scope_id, kind, sort_at,
  last_event_seq, pinned_at, archived_at, projection_json, created_at, updated_at
) VALUES (
  $1, $2, CONCAT('task:', $3), $3, 'task', $4,
  1, NULL, $5, JSON_OBJECT('preview', 'Task projection'), $4, $4
)
`, fixture.workspaceID, fixture.ownerMemberID, taskScopeID, now, hiddenAt)

	store := New(db, nil, 0, Options{})
	_, err := store.LoadConversationForUser(
		ctx,
		fixture.ownerUserID,
		fixture.workspaceID,
		taskScopeID,
		false,
	)
	require.ErrorIs(t, err, ErrNotFound, "Activity beyond its recorded frontier must not reveal a hidden Task")

	hidden, err := store.LoadConversationForUser(
		ctx,
		fixture.ownerUserID,
		fixture.workspaceID,
		taskScopeID,
		true,
	)
	require.NoError(t, err)
	require.Equal(t, ConversationKindTask, hidden.Kind)
	require.True(t, hidden.SidebarHidden)
	require.True(t, hidden.Archived)

	mustExecMessaging(t, ctx, db, `
UPDATE scopes
SET latest_seq = 6,
    updated_at = $2
WHERE id = $1
`, taskScopeID, hiddenAt.Add(time.Minute))

	visible, err := store.LoadConversationForUser(
		ctx,
		fixture.ownerUserID,
		fixture.workspaceID,
		taskScopeID,
		false,
	)
	require.NoError(t, err)
	require.Equal(t, ConversationKindTask, visible.Kind)
	require.False(t, visible.SidebarHidden)
	require.False(t, visible.Archived)
}