package message

import (
	"context"
	"errors"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 6, 16, 8, 0, 0, 0, time.UTC)

	workspaceID := "wsp_pin_store_methods"
	userID := "usr_pin_store_methods"
	memberID := "mem_pin_store_methods"
	scopeID := "scp_pin_store_methods"
	messageID := "msg_pin_store_methods"

	insertMessagePinFixture(t, ctx, db, workspaceID, userID, memberID, scopeID, messageID, now)

	store := New(db, nil, 0, Options{})
	firstPin, err := store.PinMessageForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, firstPin.Changed)
	require.NotNil(t, firstPin.Pin)
	require.Equal(t, firstPin.PinID, firstPin.Pin.ID)
	require.Len(t, firstPin.List.Pins, 1)

	repeatedPin, err := store.PinMessageForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.False(t, repeatedPin.Changed)
	require.Equal(t, firstPin.PinID, repeatedPin.PinID)
	require.NotNil(t, repeatedPin.Pin)
	require.True(t, firstPin.Pin.PinnedAt.Equal(repeatedPin.Pin.PinnedAt))

	firstTop, err := store.TopMessageForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, firstTop.Changed)
	require.NotNil(t, firstTop.TopNotice)
	require.Equal(t, messageID, firstTop.TopNotice.MessageID)

	repeatedTop, err := store.TopMessageForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.False(t, repeatedTop.Changed)
	require.Equal(t, firstTop.TopNoticeID, repeatedTop.TopNoticeID)
	require.True(t, firstTop.TopNotice.SetAt.Equal(repeatedTop.TopNotice.SetAt))

	firstHide, err := store.HideMessageTopNoticeBannerForUser(ctx, userID, workspaceID, scopeID, messageID, firstTop.TopNoticeID)
	require.NoError(t, err)
	require.True(t, firstHide.Changed)
	require.NotNil(t, firstHide.State.TopNotice)
	require.True(t, firstHide.State.TopNotice.ViewerState.HiddenFromBannerForMe)
	require.True(t, firstHide.State.TopNotice.ViewerCapabilities.CanShowInBannerAgain)
	require.False(t, firstHide.State.TopNotice.ViewerCapabilities.CanHideFromBannerForMe)

	repeatedHide, err := store.HideMessageTopNoticeBannerForUser(ctx, userID, workspaceID, scopeID, messageID, firstTop.TopNoticeID)
	require.NoError(t, err)
	require.False(t, repeatedHide.Changed)

	firstShow, err := store.ShowMessageTopNoticeBannerForUser(ctx, userID, workspaceID, scopeID, messageID, firstTop.TopNoticeID)
	require.NoError(t, err)
	require.True(t, firstShow.Changed)
	require.NotNil(t, firstShow.State.TopNotice)
	require.False(t, firstShow.State.TopNotice.ViewerState.HiddenFromBannerForMe)

	repeatedShow, err := store.ShowMessageTopNoticeBannerForUser(ctx, userID, workspaceID, scopeID, messageID, firstTop.TopNoticeID)
	require.NoError(t, err)
	require.False(t, repeatedShow.Changed)

	_, err = store.HideMessageTopNoticeBannerForUser(ctx, userID, workspaceID, scopeID, messageID, firstTop.TopNoticeID)
	require.NoError(t, err)
	reset, err := store.ResetMessageTopNoticeHidesForUser(ctx, userID, workspaceID, scopeID)
	require.NoError(t, err)
	require.True(t, reset.Changed)
	require.NotNil(t, reset.State.TopNotice)
	require.False(t, reset.State.TopNotice.ViewerState.HiddenFromBannerForMe)

	repeatedReset, err := store.ResetMessageTopNoticeHidesForUser(ctx, userID, workspaceID, scopeID)
	require.NoError(t, err)
	require.False(t, repeatedReset.Changed)

	firstUntop, err := store.UntopMessageForEveryoneForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, firstUntop.Changed)
	require.Nil(t, firstUntop.State.TopNotice)

	firstUnpin, err := store.UnpinMessageForEveryoneForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, firstUnpin.Changed)
	require.Empty(t, firstUnpin.List.Pins)
	require.Equal(t, int64(0), countScopeRows(t, ctx, db, "scope_message_pins", "scope_id = $1", scopeID))
	require.Equal(t, int64(0), countScopeRows(t, ctx, db, "scope_message_top_notice_hides", "scope_id = $1", scopeID))

	repeatedUnpin, err := store.UnpinMessageForEveryoneForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.False(t, repeatedUnpin.Changed)

	repin, err := store.PinMessageForUser(ctx, userID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, repin.Changed)
	require.NotEqual(t, firstPin.PinID, repin.PinID)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 6, 16, 8, 30, 0, 0, time.UTC)

	workspaceID := "wsp_pin_unpin_writer"
	ownerUserID := "usr_pin_unpin_owner"
	ownerMemberID := "mem_pin_unpin_owner"
	writerUserID := "usr_pin_unpin_writer"
	writerMemberID := "mem_pin_unpin_writer"
	scopeID := "scp_pin_unpin_writer"
	messageID := "msg_pin_unpin_writer"

	insertMessagePinFixture(t, ctx, db, workspaceID, ownerUserID, ownerMemberID, scopeID, messageID, now)
	insertMessagingUser(t, ctx, db, writerUserID, writerUserID+"@example.com", now)
	insertMessagingHumanMember(t, ctx, db, writerMemberID, workspaceID, writerUserID, now)
	repos := NewScopeRepositories(db)
	require.NoError(t, repos.Memberships.Upsert(ctx, ScopeMembershipRecord{
		ScopeID:                               scopeID,
		MemberID:                              writerMemberID,
		Visibility:                            "public",
		LastReadSeq:                           1,
		LastReadGlobalSeq:                     1,
		OwnerContextDismissedThroughGlobalSeq: 1,
		CreatedAt:                             now,
		UpdatedAt:                             now,
	}))

	store := New(db, nil, 0, Options{})
	pinned, err := store.PinMessageForUser(ctx, ownerUserID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, pinned.Changed)
	require.Equal(t, ownerMemberID, pinned.Pin.PinnedByMemberID)

	unpinned, err := store.UnpinMessageForEveryoneForUser(ctx, writerUserID, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, unpinned.Changed)
	require.Equal(t, pinned.PinID, unpinned.PinID)
	require.Equal(t, int64(0), countScopeRows(t, ctx, db, "scope_message_pins", "scope_id = $1", scopeID))
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 6, 16, 8, 40, 0, 0, time.UTC)

	workspaceID := "wsp_pin_write_forbidden"
	ownerUserID := "usr_pin_write_owner"
	ownerMemberID := "mem_pin_write_owner"
	outsiderUserID := "usr_pin_write_outsider"
	outsiderMemberID := "mem_pin_write_outsider"
	scopeID := "scp_pin_write_forbidden"
	messageID := "msg_pin_write_forbidden"

	insertMessagePinFixture(t, ctx, db, workspaceID, ownerUserID, ownerMemberID, scopeID, messageID, now)
	insertMessagingUser(t, ctx, db, outsiderUserID, outsiderUserID+"@example.com", now)
	insertMessagingHumanMember(t, ctx, db, outsiderMemberID, workspaceID, outsiderUserID, now)

	store := New(db, nil, 0, Options{})
	_, err := store.PinMessageForUser(ctx, outsiderUserID, workspaceID, scopeID, messageID)
	require.Error(t, err)
	require.True(t, errors.Is(err, ErrForbidden) || errors.Is(err, ErrNotFound), "got %v", err)

	taskScopeID := "tsk_pin_write_forbidden"
	require.NoError(t, NewScopeRepositories(db).Scopes.UpsertScope(ctx, ScopeRecord{
		ID:                taskScopeID,
		WorkspaceID:       workspaceID,
		Type:              "task",
		IdentityKey:       "task:" + taskScopeID,
		Status:            "active",
		CreatedByMemberID: ownerMemberID,
		CreatedAt:         now,
		UpdatedAt:         now,
	}))
	_, err = store.ListMessagePinsForUser(ctx, ownerUserID, workspaceID, taskScopeID, MessagePinCursor{}, 10)
	require.ErrorIs(t, err, ErrNotFound)
	_, err = store.PinMessageForUser(ctx, ownerUserID, workspaceID, taskScopeID, messageID)
	require.ErrorIs(t, err, ErrNotFound)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 6, 16, 8, 50, 0, 0, time.UTC)

	workspaceID := "wsp_pin_thread_parent"
	userID := "usr_pin_thread_parent"
	memberID := "mem_pin_thread_parent"
	channelID := "chn_pin_thread_parent"
	parentMessageID := "msg_pin_thread_parent"
	threadID := "thr_pin_thread_parent"
	threadMessageID := "msg_pin_thread_reply"

	insertMessagePinFixture(t, ctx, db, workspaceID, userID, memberID, channelID, parentMessageID, now)
	insertMessagingReplyThread(t, ctx, db, threadID, workspaceID, channelID, parentMessageID, "pinthr01", "", now)
	require.NoError(t, NewScopeRepositories(db).Messages.Insert(ctx, ScopeMessageRecord{
		ID:                 threadMessageID,
		WorkspaceID:        workspaceID,
		ScopeID:            threadID,
		Seq:                1,
		WorkspaceGlobalSeq: 2,
		AuthorMemberID:     memberID,
		AuthorType:         "human",
		MessageType:        "text",
		Content:            "thread pin",
		Metadata:           []byte(`{}`),
		CreatedAt:          now.Add(time.Minute),
		UpdatedAt:          now.Add(time.Minute),
	}))

	store := New(db, nil, 0, Options{})
	channelPin, err := store.PinMessageForUser(ctx, userID, workspaceID, channelID, parentMessageID)
	require.NoError(t, err)
	threadPin, err := store.PinMessageForUser(ctx, userID, workspaceID, threadID, threadMessageID)
	require.NoError(t, err)
	require.Equal(t, threadID, threadPin.List.ScopeID)
	require.NotNil(t, threadPin.Pin)
	require.Equal(t, threadID, threadPin.Pin.ScopeID)
	require.Equal(t, int64(1), countScopeRows(t, ctx, db, "scope_message_pins", "scope_id = $1", channelID))
	require.Equal(t, int64(1), countScopeRows(t, ctx, db, "scope_message_pins", "scope_id = $1", threadID))

	channelList, err := store.ListMessagePinsForUser(ctx, userID, workspaceID, channelID, MessagePinCursor{}, 10)
	require.NoError(t, err)
	require.Equal(t, channelID, channelList.ScopeID)
	require.Len(t, channelList.Pins, 1)
	require.Equal(t, channelPin.PinID, channelList.Pins[0].ID)
	require.Equal(t, parentMessageID, channelList.Pins[0].MessageID)

	threadList, err := store.ListMessagePinsForUser(ctx, userID, workspaceID, threadID, MessagePinCursor{}, 10)
	require.NoError(t, err)
	require.Equal(t, threadID, threadList.ScopeID)
	require.Len(t, threadList.Pins, 1)
	require.Equal(t, threadPin.PinID, threadList.Pins[0].ID)
	require.Equal(t, threadMessageID, threadList.Pins[0].MessageID)

	unpinned, err := store.UnpinMessageForEveryoneForUser(ctx, userID, workspaceID, threadID, threadMessageID)
	require.NoError(t, err)
	require.True(t, unpinned.Changed)
	require.Equal(t, threadPin.PinID, unpinned.PinID)
	require.Equal(t, threadID, unpinned.List.ScopeID)
	require.Empty(t, unpinned.List.Pins)
	require.Equal(t, int64(1), countScopeRows(t, ctx, db, "scope_message_pins", "scope_id = $1", channelID))
	require.Equal(t, int64(0), countScopeRows(t, ctx, db, "scope_message_pins", "scope_id = $1", threadID))
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 6, 16, 9, 2, 0, 0, time.UTC)

	workspaceID := "wsp_pin_list_scope"
	userID := "usr_pin_list_scope"
	memberID := "mem_pin_list_scope"
	channelID := "chn_pin_list_scope"
	parentMessageID := "msg_pin_list_scope_parent"
	otherChannelID := "chn_pin_list_scope_other"
	otherParentMessageID := "msg_pin_list_scope_other_parent"
	otherThreadID := "thr_pin_list_scope_other"
	otherThreadMessageID := "msg_pin_list_scope_other_reply"

	insertMessagePinFixture(t, ctx, db, workspaceID, userID, memberID, channelID, parentMessageID, now)
	repos := NewScopeRepositories(db)
	require.NoError(t, repos.Scopes.UpsertScope(ctx, ScopeRecord{
		ID:                otherChannelID,
		WorkspaceID:       workspaceID,
		Type:              "channel",
		IdentityKey:       "channel:" + otherChannelID,
		LatestSeq:         1,
		LatestGlobalSeq:   2,
		LatestMessageAt:   &now,
		Status:            "active",
		CreatedByMemberID: memberID,
		CreatedAt:         now,
		UpdatedAt:         now,
	}))
	require.NoError(t, repos.Scopes.UpsertChannelProfile(ctx, ScopeChannelProfileRecord{
		ScopeID:       otherChannelID,
		WorkspaceID:   workspaceID,
		Slug:          otherChannelID,
		DisplayName:   "Other Pins",
		Description:   "other pin fixture",
		Visibility:    "private",
		OwnerMemberID: memberID,
		CreatedAt:     now,
		UpdatedAt:     now,
	}))
	require.NoError(t, repos.Memberships.Upsert(ctx, ScopeMembershipRecord{
		ScopeID:                               otherChannelID,
		MemberID:                              memberID,
		Visibility:                            "public",
		LastReadSeq:                           1,
		LastReadGlobalSeq:                     2,
		OwnerContextDismissedThroughGlobalSeq: 2,
		CreatedAt:                             now,
		UpdatedAt:                             now,
	}))
	require.NoError(t, repos.Messages.Insert(ctx, ScopeMessageRecord{
		ID:                 otherParentMessageID,
		WorkspaceID:        workspaceID,
		ScopeID:            otherChannelID,
		Seq:                1,
		WorkspaceGlobalSeq: 2,
		AuthorMemberID:     memberID,
		AuthorType:         "human",
		MessageType:        "text",
		Content:            "other parent",
		Metadata:           []byte(`{}`),
		CreatedAt:          now,
		UpdatedAt:          now,
	}))
	insertMessagingReplyThread(t, ctx, db, otherThreadID, workspaceID, otherChannelID, otherParentMessageID, "pinoth01", "", now)
	require.NoError(t, repos.Messages.Insert(ctx, ScopeMessageRecord{
		ID:                 otherThreadMessageID,
		WorkspaceID:        workspaceID,
		ScopeID:            otherThreadID,
		Seq:                1,
		WorkspaceGlobalSeq: 3,
		AuthorMemberID:     memberID,
		AuthorType:         "human",
		MessageType:        "text",
		Content:            "other thread pin",
		Metadata:           []byte(`{}`),
		CreatedAt:          now.Add(time.Minute),
		UpdatedAt:          now.Add(time.Minute),
	}))
	inserted, err := repos.Pins.InsertActivePin(ctx, ScopeMessagePinRecord{
		ID:               "pin_list_scope_cross_parent",
		WorkspaceID:      workspaceID,
		ScopeID:          channelID,
		MessageID:        otherThreadMessageID,
		PinnedByMemberID: memberID,
		PinnedAt:         now.Add(2 * time.Minute),
		CreatedAt:        now.Add(2 * time.Minute),
		UpdatedAt:        now.Add(2 * time.Minute),
	})
	require.NoError(t, err)
	require.True(t, inserted.Changed)

	rows, err := repos.Pins.ListActivePins(ctx, workspaceID, channelID, memberID, MessagePinCursor{}, 10)
	require.NoError(t, err)
	require.Empty(t, rows, "a Pin list must not include a message from another scope")
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	now := time.Date(2026, 6, 16, 9, 5, 0, 0, time.UTC)

	workspaceID := "wsp_pin_invisible"
	userID := "usr_pin_invisible"
	memberID := "mem_pin_invisible"
	scopeID := "scp_pin_invisible"
	messageID := "msg_pin_invisible"

	insertMessagePinFixture(t, ctx, db, workspaceID, userID, memberID, scopeID, messageID, now)
	mustExecMessaging(t, ctx, db, `
UPDATE scope_memberships
SET effective_from_seq = 2
WHERE scope_id = $1 AND member_id = $2
`, scopeID, memberID)
	inserted, err := NewScopeRepositories(db).Pins.InsertActivePin(ctx, ScopeMessagePinRecord{
		ID:               "pin_invisible_before_join",
		WorkspaceID:      workspaceID,
		ScopeID:          scopeID,
		MessageID:        messageID,
		PinnedByMemberID: memberID,
		PinnedAt:         now,
		CreatedAt:        now,
		UpdatedAt:        now,
	})
	require.NoError(t, err)
	require.True(t, inserted.Changed)

	list, err := New(db, nil, 0, Options{}).ListMessagePinsForUser(ctx, userID, workspaceID, scopeID, MessagePinCursor{}, 10)
	require.NoError(t, err)
	require.Empty(t, list.Pins)
	require.Nil(t, list.LatestPin)
}

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

	ctx := context.Background()
	db := startMessagingTestPool(t, ctx)
	repos := NewScopeRepositories(db)
	now := time.Date(2026, 6, 16, 9, 0, 0, 0, time.UTC)

	workspaceID := "wsp_pin_primitives"
	userID := "usr_pin_primitives"
	memberID := "mem_pin_primitives"
	scopeID := "scp_pin_primitives"
	messageID := "msg_pin_primitives"

	insertMessagePinFixture(t, ctx, db, workspaceID, userID, memberID, scopeID, messageID, now)

	firstPinnedAt := now.Add(time.Minute)
	first, err := repos.Pins.InsertActivePin(ctx, ScopeMessagePinRecord{
		ID:               "pin_primitives_first",
		WorkspaceID:      workspaceID,
		ScopeID:          scopeID,
		MessageID:        messageID,
		PinnedByMemberID: memberID,
		PinnedAt:         firstPinnedAt,
		CreatedAt:        firstPinnedAt,
		UpdatedAt:        firstPinnedAt,
	})
	require.NoError(t, err)
	require.True(t, first.Changed)

	duplicatePinnedAt := now.Add(2 * time.Minute)
	duplicate, err := repos.Pins.InsertActivePin(ctx, ScopeMessagePinRecord{
		ID:               "pin_primitives_duplicate",
		WorkspaceID:      workspaceID,
		ScopeID:          scopeID,
		MessageID:        messageID,
		PinnedByMemberID: memberID,
		PinnedAt:         duplicatePinnedAt,
		CreatedAt:        duplicatePinnedAt,
		UpdatedAt:        duplicatePinnedAt,
	})
	require.NoError(t, err)
	require.False(t, duplicate.Changed)
	require.Equal(t, first.Pin.ID, duplicate.Pin.ID)
	require.True(t, firstPinnedAt.Equal(duplicate.Pin.PinnedAt), "idempotent pin must not refresh ordering")

	list, err := repos.Pins.ListActivePins(ctx, workspaceID, scopeID, memberID, MessagePinCursor{}, 10)
	require.NoError(t, err)
	require.Len(t, list, 1)

	deleted, err := repos.Pins.DeleteActivePin(ctx, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, deleted.Changed)

	repinnedAt := now.Add(5 * time.Minute)
	repinned, err := repos.Pins.InsertActivePin(ctx, ScopeMessagePinRecord{
		ID:               "pin_primitives_second_instance",
		WorkspaceID:      workspaceID,
		ScopeID:          scopeID,
		MessageID:        messageID,
		PinnedByMemberID: memberID,
		PinnedAt:         repinnedAt,
		CreatedAt:        repinnedAt,
		UpdatedAt:        repinnedAt,
	})
	require.NoError(t, err)
	require.True(t, repinned.Changed)
	require.NotEqual(t, first.Pin.ID, repinned.Pin.ID)

	topAt := now.Add(6 * time.Minute)
	top, err := repos.Pins.UpsertActiveTopNotice(ctx, ScopeMessageTopNoticeRecord{
		ID:            "top_primitives_first",
		WorkspaceID:   workspaceID,
		ScopeID:       scopeID,
		MessageID:     messageID,
		SetByMemberID: memberID,
		SetAt:         topAt,
		CreatedAt:     topAt,
		UpdatedAt:     topAt,
	})
	require.NoError(t, err)
	require.True(t, top.Changed)

	duplicateTop, err := repos.Pins.UpsertActiveTopNotice(ctx, ScopeMessageTopNoticeRecord{
		ID:            "top_primitives_duplicate",
		WorkspaceID:   workspaceID,
		ScopeID:       scopeID,
		MessageID:     messageID,
		SetByMemberID: memberID,
		SetAt:         now.Add(7 * time.Minute),
		CreatedAt:     now.Add(7 * time.Minute),
		UpdatedAt:     now.Add(7 * time.Minute),
	})
	require.NoError(t, err)
	require.False(t, duplicateTop.Changed)
	require.Equal(t, top.TopNotice.ID, duplicateTop.TopNotice.ID)
	require.True(t, topAt.Equal(duplicateTop.TopNotice.SetAt), "idempotent top notice must not refresh ordering")

	hideAt := now.Add(8 * time.Minute)
	hide, err := repos.Pins.InsertTopNoticeHide(ctx, ScopeMessageTopNoticeHideRecord{
		ID:               "hide_primitives_first",
		WorkspaceID:      workspaceID,
		ScopeID:          scopeID,
		TopNoticeID:      top.TopNotice.ID,
		MessageID:        messageID,
		HiddenByMemberID: memberID,
		HiddenAt:         hideAt,
		CreatedAt:        hideAt,
		UpdatedAt:        hideAt,
	})
	require.NoError(t, err)
	require.True(t, hide.Changed)

	duplicateHide, err := repos.Pins.InsertTopNoticeHide(ctx, ScopeMessageTopNoticeHideRecord{
		ID:               "hide_primitives_duplicate",
		WorkspaceID:      workspaceID,
		ScopeID:          scopeID,
		TopNoticeID:      top.TopNotice.ID,
		MessageID:        messageID,
		HiddenByMemberID: memberID,
		HiddenAt:         now.Add(9 * time.Minute),
		CreatedAt:        now.Add(9 * time.Minute),
		UpdatedAt:        now.Add(9 * time.Minute),
	})
	require.NoError(t, err)
	require.False(t, duplicateHide.Changed)
	require.Equal(t, hide.Hide.ID, duplicateHide.Hide.ID)
	require.True(t, hideAt.Equal(duplicateHide.Hide.HiddenAt), "idempotent hide must not refresh ordering")

	row, found, err := repos.Pins.GetActiveTopNotice(ctx, workspaceID, scopeID, memberID)
	require.NoError(t, err)
	require.True(t, found)
	require.True(t, row.HasHide)
	require.Equal(t, top.TopNotice.ID, row.Hide.TopNoticeID)

	removedTop, err := repos.Pins.DeleteActiveTopNotice(ctx, workspaceID, scopeID, messageID)
	require.NoError(t, err)
	require.True(t, removedTop.Changed)
	require.Equal(t, int64(0), countScopeRows(t, ctx, db, "scope_message_top_notice_hides", "top_notice_id = $1", top.TopNotice.ID), "shared untop must cascade old hide rows")
}

func insertMessagePinFixture(t *testing.T, ctx context.Context, db coredb.QueryExecer, workspaceID, userID, memberID, scopeID, messageID string, now time.Time) {
	t.Helper()

	insertMessagingWorkspace(t, ctx, db, workspaceID, now)
	insertMessagingUser(t, ctx, db, userID, workspaceID+"@example.com", now)
	insertMessagingHumanMember(t, ctx, db, memberID, workspaceID, userID, now)
	repos := NewScopeRepositories(db)
	require.NoError(t, repos.Scopes.UpsertScope(ctx, ScopeRecord{
		ID:                scopeID,
		WorkspaceID:       workspaceID,
		Type:              "channel",
		IdentityKey:       "channel:" + scopeID,
		LatestSeq:         1,
		LatestGlobalSeq:   1,
		LatestMessageAt:   &now,
		Status:            "active",
		CreatedByMemberID: memberID,
		CreatedAt:         now,
		UpdatedAt:         now,
	}))
	require.NoError(t, repos.Scopes.UpsertChannelProfile(ctx, ScopeChannelProfileRecord{
		ScopeID:       scopeID,
		WorkspaceID:   workspaceID,
		Slug:          scopeID,
		DisplayName:   "Pins",
		Description:   "pin fixture",
		Visibility:    "private",
		OwnerMemberID: memberID,
		CreatedAt:     now,
		UpdatedAt:     now,
	}))
	require.NoError(t, repos.Memberships.Upsert(ctx, ScopeMembershipRecord{
		ScopeID:                               scopeID,
		MemberID:                              memberID,
		Visibility:                            "public",
		LastReadSeq:                           1,
		LastReadGlobalSeq:                     1,
		OwnerContextDismissedThroughGlobalSeq: 1,
		CreatedAt:                             now,
		UpdatedAt:                             now,
	}))
	require.NoError(t, repos.Messages.Insert(ctx, ScopeMessageRecord{
		ID:                 messageID,
		WorkspaceID:        workspaceID,
		ScopeID:            scopeID,
		Seq:                1,
		WorkspaceGlobalSeq: 1,
		AuthorMemberID:     memberID,
		AuthorType:         "human",
		MessageType:        "text",
		Content:            "pin me",
		Metadata:           []byte(`{}`),
		CreatedAt:          now,
		UpdatedAt:          now,
	}))
}