package app

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

	"github.com/go-sql-driver/mysql"
	"github.com/stretchr/testify/require"

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

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

	source, err := os.ReadFile("app_channels.go")
	require.NoError(t, err)
	transferSource := sourceFromFunctionForTest(t, string(source), "func (store *Store) TransferChannelOwnershipForUser")
	require.Contains(t, transferSource, "JOIN scope_memberships sm")

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 5, 14, 9, 7, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "transfer_scope_owner", now)
	nextOwner := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "transfer_scope_next", now)
	closedMember := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "transfer_scope_closed", now)

	const channelID = "chn_transfer_scope_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "transfer-scope-room", "", "private", fixture.MemberID, fixture.MemberID, now)

	mustExec(t, pool, `
INSERT INTO scope_memberships (
  scope_id, member_id, visibility, owner_member_id, last_read_seq,
  last_read_global_seq, owner_context_dismissed_through_global_seq,
  effective_from_seq, effective_to_seq, created_at, updated_at
) VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 1, $3, $3)
`, channelID, closedMember.MemberID, now)
	err = store.TransferChannelOwnershipForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID, closedMember.UserID)
	require.Error(t, err)
	require.Contains(t, err.Error(), "new owner must already be joined to the channel")

	mustExec(t, pool, `
INSERT INTO scope_memberships (
  scope_id, member_id, visibility, owner_member_id, last_read_seq,
  last_read_global_seq, owner_context_dismissed_through_global_seq,
  effective_from_seq, created_at, updated_at
) VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, $3, $3)
`, channelID, nextOwner.MemberID, now)

	var scopeRows int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND removed_at IS NULL
  AND archived_at IS NULL
  AND effective_to_seq IS NULL
`, channelID, nextOwner.MemberID).Scan(&scopeRows))
	require.Equal(t, 1, scopeRows)

	require.NoError(t, store.TransferChannelOwnershipForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID, nextOwner.UserID))

	var scopeProfileOwnerID string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT owner_member_id
FROM scope_channel_profiles
WHERE workspace_id = $1 AND scope_id = $2
`, fixture.WorkspaceID, channelID).Scan(&scopeProfileOwnerID))
	require.Equal(t, nextOwner.MemberID, scopeProfileOwnerID)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 5, 14, 13, 45, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "channel_archived_scope", now)

	mustExec(t, pool, `
UPDATE scope_memberships
SET archived_at = $3, updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
`, fixture.AllChannelID, fixture.MemberID, now.Add(time.Minute))
	archived, err := channelArchivedForMember(ctx, pool, fixture.MemberID, fixture.AllChannelID)
	require.NoError(t, err)
	require.True(t, archived)

	description := "updated while archived"
	updated, err := store.UpdateChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, nil, &description, nil)
	require.NoError(t, err)
	require.True(t, updated.Archived)
	require.Equal(t, description, updated.Description)

	var scopeDescription string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT description
FROM scope_channel_profiles
WHERE scope_id = $1
`, fixture.AllChannelID).Scan(&scopeDescription))
	require.Equal(t, description, scopeDescription)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 7, 9, 12, 10, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "channel_archive_visibility", now)
	channelID := "chn_channel_archive_visibility_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "archive-visibility", "", "public", fixture.MemberID, fixture.MemberID, now)
	mustExec(t, pool, `
UPDATE scope_channel_profiles
SET subsystem = $2
WHERE scope_id = $1
`, channelID, message.ChannelSubsystemTutorial)

	archived, err := store.ArchiveChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID)
	require.NoError(t, err)
	require.True(t, archived.Archived)
	require.True(t, archived.Tutorial)
	require.Equal(t, "public", archived.Visibility)

	unarchived, err := store.UnarchiveChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID)
	require.NoError(t, err)
	require.False(t, unarchived.Archived)
	require.True(t, unarchived.Tutorial)
	require.Equal(t, "public", unarchived.Visibility)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 7, 9, 10, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "channel_visibility_update", now)
	channelID := "chn_channel_visibility_update_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "visibility-room", "", "private", fixture.MemberID, fixture.MemberID, now)

	visibility := "public"
	updated, err := store.UpdateChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID, nil, nil, &visibility)
	require.NoError(t, err)
	require.Equal(t, "public", updated.Visibility)

	var storedVisibility string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT visibility
FROM scope_channel_profiles
WHERE workspace_id = $1 AND scope_id = $2
`, fixture.WorkspaceID, channelID).Scan(&storedVisibility))
	require.Equal(t, "public", storedVisibility)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 7, 9, 10, 5, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "channel_visibility_invalid", now)
	channelID := "chn_channel_visibility_invalid_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "visibility-invalid", "", "private", fixture.MemberID, fixture.MemberID, now)

	visibility := "workspace"
	_, err := store.UpdateChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID, nil, nil, &visibility)
	require.Error(t, err)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 7, 9, 10, 10, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "channel_visibility_all", now)

	visibility := "private"
	_, err := store.UpdateChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, nil, nil, &visibility)
	require.Error(t, err)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 7, 30, 14, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "channel_tutorial_rename", now)
	channelID := "chn_channel_tutorial_rename"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "quick-start-owner", "", "private", fixture.MemberID, fixture.MemberID, now)
	mustExec(t, pool, `
UPDATE scope_channel_profiles
SET subsystem = $2, display_name = 'quick start'
WHERE scope_id = $1
`, channelID, message.ChannelSubsystemTutorial)

	name := "Renamed"
	_, err := store.UpdateChannelForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelID, &name, nil, nil)
	require.Error(t, err)
	require.Contains(t, err.Error(), "cannot be renamed")

	var storedName string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT display_name
FROM scope_channel_profiles
WHERE scope_id = $1
`, channelID).Scan(&storedName))
	require.Equal(t, "quick start", storedName)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 5, 25, 13, 30, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "member_event", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "member_event_peer", now)

	channelID := "chn_member_event_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "member-event-room", "", "private", fixture.MemberID, fixture.MemberID, now)

	added, err := store.AddChannelHumanForUserWithEvent(ctx, fixture.UserID, fixture.WorkspaceID, channelID, peer.UserID)
	require.NoError(t, err)
	require.True(t, added.Changed)
	require.Equal(t, message.MessageTypeSystem, added.Message.MessageType)
	require.Empty(t, added.Delivery.Recipients)
	require.Empty(t, added.Delivery.WakeRecipients)
	require.Equal(t, channelID, added.Message.ChannelID)
	addedEvent, ok := added.Message.Metadata[message.ChannelMemberEventMetadataKey].(map[string]any)
	require.True(t, ok)
	require.Equal(t, message.ChannelMemberEventActionAdded, addedEvent["action"])
	require.Equal(t, fixture.MemberID, addedEvent["actorMemberId"])
	require.Equal(t, peer.MemberID, addedEvent["targetMemberId"])
	require.Equal(t, message.ChannelMemberEventTargetHuman, addedEvent["targetKind"])
	require.Equal(t, 0, channelMemberEventOutboxCount(t, pool, channelID))

	again, err := store.AddChannelHumanForUserWithEvent(ctx, fixture.UserID, fixture.WorkspaceID, channelID, peer.UserID)
	require.NoError(t, err)
	require.False(t, again.Changed)
	require.Equal(t, 1, channelMemberEventMessageCount(t, pool, channelID))

	removed, err := store.RemoveChannelHumanForUserWithEvent(ctx, fixture.UserID, fixture.WorkspaceID, channelID, peer.UserID)
	require.NoError(t, err)
	require.True(t, removed.Changed)
	removedEvent, ok := removed.Message.Metadata[message.ChannelMemberEventMetadataKey].(map[string]any)
	require.True(t, ok)
	require.Equal(t, message.ChannelMemberEventActionRemoved, removedEvent["action"])
	require.Equal(t, peer.MemberID, removedEvent["targetMemberId"])

	removedAgain, err := store.RemoveChannelHumanForUserWithEvent(ctx, fixture.UserID, fixture.WorkspaceID, channelID, peer.UserID)
	require.NoError(t, err)
	require.False(t, removedAgain.Changed)
	require.Equal(t, 2, channelMemberEventMessageCount(t, pool, channelID))
	require.Equal(t, 0, channelMemberEventOutboxCount(t, pool, channelID))
}

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

	source, err := os.ReadFile("app_channels.go")
	require.NoError(t, err)
	runSource := sourceFromFunctionForTest(t, string(source), "func runChannelMemberEventTx")
	require.Contains(t, runSource, "lockChannelMemberEventScope")
	lockSource := sourceFromFunctionForTest(t, string(source), "func lockChannelMemberEventScope")
	require.Contains(t, lockSource, "FOR UPDATE")
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 2, 12, 5, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "batch_member_event", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "batch_member_event_peer", now)
	secondPeer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "batch_member_event_second_peer", now)

	channelID := "chn_batch_member_event_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "batch-member-event-room", "", "private", fixture.MemberID, fixture.MemberID, now)

	results, err := store.AddChannelMembersForUserWithEvents(ctx, fixture.UserID, fixture.WorkspaceID, channelID, []message.ChannelMemberAddRequest{
		{UserID: peer.UserID},
		{UserID: secondPeer.UserID},
	})
	require.NoError(t, err)
	require.Len(t, results, 1)
	require.Equal(t, 2, results[0].ChangedCount)
	require.Equal(t, 1, activeChannelMemberProjectionCount(t, pool, channelID, peer.MemberID))
	require.Equal(t, 1, activeChannelMemberProjectionCount(t, pool, channelID, secondPeer.MemberID))
	require.Equal(t, 1, channelMemberEventMessageCount(t, pool, channelID))

	rollbackChannelID := "chn_batch_member_event_rollback"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, rollbackChannelID, "batch-member-event-rollback", "", "private", fixture.MemberID, fixture.MemberID, now)
	_, err = store.AddChannelMembersForUserWithEvents(ctx, fixture.UserID, fixture.WorkspaceID, rollbackChannelID, []message.ChannelMemberAddRequest{
		{UserID: peer.UserID},
		{UserID: "usr_missing_for_batch_rollback"},
	})
	require.Error(t, err)
	require.Equal(t, 0, activeChannelMemberProjectionCount(t, pool, rollbackChannelID, peer.MemberID))
	require.Equal(t, 0, channelMemberEventMessageCount(t, pool, rollbackChannelID))

	subsystemChannelID := "chn_batch_member_event_kb"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, subsystemChannelID, "batch-member-event-kb", "", "private", fixture.MemberID, fixture.MemberID, now)
	mustExec(t, pool, `
UPDATE scope_channel_profiles
SET subsystem = $2
WHERE workspace_id = $1 AND scope_id = $3
`, fixture.WorkspaceID, message.ChannelSubsystemKB, subsystemChannelID)
	_, err = store.AddChannelMembersForUserWithEvents(ctx, fixture.UserID, fixture.WorkspaceID, subsystemChannelID, []message.ChannelMemberAddRequest{
		{UserID: peer.UserID},
	})
	require.ErrorContains(t, err, "subsystem channels cannot be shared with human members")
	require.Equal(t, 0, activeChannelMemberProjectionCount(t, pool, subsystemChannelID, peer.MemberID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	retryingDB := &retryScopeMessageInsertDB{DB: pool}
	store := New(retryingDB, Options{})
	now := time.Date(2026, 5, 29, 4, 20, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "member_event_retry", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "member_event_retry_peer", now)

	channelID := "chn_member_event_retry_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "member-event-retry-room", "", "private", fixture.MemberID, fixture.MemberID, now)

	added, err := store.AddChannelHumanForUserWithEvent(ctx, fixture.UserID, fixture.WorkspaceID, channelID, peer.UserID)
	require.NoError(t, err)
	require.True(t, retryingDB.wasInjected(), "test should inject a retryable scope_messages write error")
	require.True(t, added.Changed)
	require.Equal(t, message.MessageTypeSystem, added.Message.MessageType)
	require.Equal(t, channelID, added.Message.ChannelID)
	require.Equal(t, 1, activeChannelMemberProjectionCount(t, pool, channelID, peer.MemberID))
	require.Equal(t, 1, channelMemberEventMessageCount(t, pool, channelID), "retry should not duplicate member event messages")
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	retryingDB := &retryScopeMessageInsertDB{DB: pool}
	store := New(retryingDB, Options{})
	now := time.Date(2026, 8, 8, 0, 25, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "batch_member_event_retry", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "batch_member_event_retry_peer", now)

	channelID := "chn_batch_member_event_retry_room"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelID, "batch-member-event-retry-room", "", "private", fixture.MemberID, fixture.MemberID, now)

	results, err := store.AddChannelMembersForUserWithEvents(
		ctx,
		fixture.UserID,
		fixture.WorkspaceID,
		channelID,
		[]message.ChannelMemberAddRequest{{UserID: peer.UserID}},
	)
	require.NoError(t, err)
	require.True(t, retryingDB.wasInjected(), "test should inject a retryable scope_messages write error")
	require.Len(t, results, 1)
	require.Equal(t, 1, results[0].ChangedCount)
	require.Equal(t, 1, activeChannelMemberProjectionCount(t, pool, channelID, peer.MemberID))
	require.Equal(t, 1, channelMemberEventMessageCount(t, pool, channelID), "retry should not duplicate member event messages")
}

type retryScopeMessageInsertDB struct {
	coredb.DB
	mu       sync.Mutex
	injected bool
}

func (db *retryScopeMessageInsertDB) BeginTx(ctx context.Context, options coredb.TxOptions) (coredb.Tx, error) {
	tx, err := db.DB.BeginTx(ctx, options)
	if err != nil {
		return nil, err
	}
	return &retryScopeMessageInsertTx{Tx: tx, parent: db}, nil
}

func (db *retryScopeMessageInsertDB) wasInjected() bool {
	db.mu.Lock()
	defer db.mu.Unlock()
	return db.injected
}

type retryScopeMessageInsertTx struct {
	coredb.Tx
	parent *retryScopeMessageInsertDB
}

func (tx *retryScopeMessageInsertTx) Exec(ctx context.Context, query string, args ...any) (coredb.Result, error) {
	if strings.Contains(query, "INSERT INTO scope_messages") {
		tx.parent.mu.Lock()
		if !tx.parent.injected {
			tx.parent.injected = true
			tx.parent.mu.Unlock()
			return nil, &mysql.MySQLError{Number: 1213, Message: "Deadlock found when trying to get lock; try restarting transaction"}
		}
		tx.parent.mu.Unlock()
	}
	return tx.Tx.Exec(ctx, query, args...)
}

func channelMemberEventMessageCount(t *testing.T, pool coredb.QueryExecer, channelID string) int {
	t.Helper()

	var count int
	require.NoError(t, pool.QueryRow(context.Background(), `
SELECT COUNT(*)
FROM scope_messages
WHERE scope_id = $1
  AND message_type = 'system'
  AND JSON_EXTRACT(metadata, '$.channelMemberEvent.action') IS NOT NULL
`, channelID).Scan(&count))
	return count
}

func channelMemberEventOutboxCount(t *testing.T, pool coredb.QueryExecer, channelID string) int {
	t.Helper()

	var count int
	require.NoError(t, pool.QueryRow(context.Background(), `
SELECT COUNT(*)
FROM scope_fanout_outbox
WHERE scope_id = $1
  AND event_ref_type = 'message'
`, channelID).Scan(&count))
	return count
}

func activeChannelMemberProjectionCount(t *testing.T, pool coredb.QueryExecer, channelID string, memberID string) int {
	t.Helper()

	var count int
	require.NoError(t, pool.QueryRow(context.Background(), `
SELECT COUNT(*)
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND removed_at IS NULL
  AND archived_at IS NULL
  AND effective_to_seq IS NULL
`, channelID, memberID).Scan(&count))
	return count
}

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

	channelsBytes, err := os.ReadFile("app_channels.go")
	require.NoError(t, err)
	channelsSource := string(channelsBytes)

	updateSource := sourceFromFunctionForTest(t, channelsSource, "func updateChannelProfileInTx")
	require.Contains(t, updateSource, "UPDATE scope_channel_profiles")

	archiveSource := sourceFromFunctionForTest(t, channelsSource, "func (store *Store) ArchiveChannelForUser")
	require.Contains(t, archiveSource, "DELETE FROM scope_notification_settings")

	deleteSource := sourceFromFunctionForTest(t, channelsSource, "func (store *Store) DeleteChannelOrDMForUser")
	require.Contains(t, deleteSource, "archiveScopeProjection")
	require.Contains(t, deleteSource, "deleteReplyThreadChannelsForParentScope")

	transferSource := sourceFromFunctionForTest(t, channelsSource, "func (store *Store) TransferChannelOwnershipForUser")
	require.Contains(t, transferSource, "UPDATE scope_channel_profiles")

	adapterBytes, err := os.ReadFile("domain_adapters.go")
	require.NoError(t, err)
	adapterSource := string(adapterBytes)
	readStateSource := sourceFromFunctionForTest(t, adapterSource, "func writeScopeParticipantReadStateWithExecer")
	require.Contains(t, readStateSource, "UPDATE scope_memberships")
	require.NotContains(t, adapterSource, "WriteScopeParticipantReadStateForThreadID")
}

// TestRemovePrivateEntriesNarrowPathLockOrderInSource is the source assertion
// that locks the narrow (effects) path call order of removePrivateEntriesForOwnerHostChannel:
// capture+lock memberships (queryScopeMembershipRows) -> notification DELETE
// (deleteOwnerPrivateWakeSubscriptions) -> narrow retire (retireScopeMembershipsByPKs).
// The DB test proves delete-before-retire final-state parity; this source assertion
// proves the capture-before-delete ordering that prevents a notification->membership
// vs remover membership->notification reverse-order deadlock (which the DB final
// state alone cannot distinguish).
func TestRemovePrivateEntriesNarrowPathLockOrderInSource(t *testing.T) {
	t.Parallel()

	source, err := os.ReadFile("scope_projection.go")
	require.NoError(t, err)
	fn := sourceFromFunctionForTest(t, string(source), "func (store *Store) removePrivateEntriesForOwnerHostChannel")

	idxCapture := strings.Index(fn, "queryScopeMembershipRows(")
	require.GreaterOrEqual(t, idxCapture, 0, "narrow path must capture membership rows")

	relDelete := strings.Index(fn[idxCapture:], "deleteOwnerPrivateWakeSubscriptions()")
	require.GreaterOrEqual(t, relDelete, 0, "narrow path must call the notification delete after capture")
	idxDelete := idxCapture + relDelete

	relRetire := strings.Index(fn[idxDelete:], "retireScopeMembershipsByPKs(")
	require.GreaterOrEqual(t, relRetire, 0, "narrow path must retire memberships after the notification delete")
	idxRetire := idxDelete + relRetire

	require.Less(t, idxCapture, idxDelete, "narrow path: capture must precede notification DELETE")
	require.Less(t, idxDelete, idxRetire, "narrow path: notification DELETE must precede retire (membership-before-notification lock order)")
}

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

	source, err := os.ReadFile("scope_projection.go")
	require.NoError(t, err)
	archiveSource := sourceFromFunctionForTest(t, string(source), "func (store *Store) archiveScopeProjection")

	scopeLock := strings.Index(archiveSource, "UPDATE scopes")
	profileLock := strings.Index(archiveSource, "UPDATE scope_channel_profiles")
	membershipLock := strings.Index(archiveSource, "UPDATE scope_memberships")
	notificationLock := strings.Index(archiveSource, "DELETE FROM scope_notification_settings")
	require.GreaterOrEqual(t, scopeLock, 0)
	require.Greater(t, profileLock, scopeLock)
	require.Greater(t, membershipLock, profileLock)
	require.Greater(t, notificationLock, membershipLock)
}

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

	queryer := &replyThreadChannelMessageCaptureQueryer{
		rows: &replyThreadChannelMessageRows{values: [][]string{
			{"thr_child_a", "msg_child_a1"},
			{"thr_child_a", "msg_child_a2"},
			{"thr_child_b", "msg_child_b1"},
		}},
	}

	got, err := loadReplyThreadChannelMessageIDsForDelete(context.Background(), queryer, " wsp_batch ", []string{"thr_child_a", "thr_child_b", "thr_child_a", " "})

	require.NoError(t, err)
	require.Equal(t, map[string][]string{
		"thr_child_a": []string{"msg_child_a1", "msg_child_a2"},
		"thr_child_b": []string{"msg_child_b1"},
	}, got)
	require.Len(t, queryer.queries, 1)
	require.Equal(t, []any{"wsp_batch", "thr_child_a", "thr_child_b"}, queryer.args[0])
	query := strings.Join(strings.Fields(queryer.queries[0]), " ")
	require.Contains(t, query, "WITH reply_thread_scope_ids AS")
	require.Contains(t, query, "JOIN scope_messages m")
	require.Contains(t, query, "ORDER BY requested.thread_scope_id ASC, m.seq ASC")
}

type replyThreadChannelMessageCaptureQueryer struct {
	queries []string
	args    [][]any
	rows    coredb.Rows
}

func (queryer *replyThreadChannelMessageCaptureQueryer) Query(_ context.Context, query string, args ...any) (coredb.Rows, error) {
	queryer.queries = append(queryer.queries, query)
	queryer.args = append(queryer.args, append([]any(nil), args...))
	if queryer.rows == nil {
		return &replyThreadChannelMessageRows{}, nil
	}
	return queryer.rows, nil
}

type replyThreadChannelMessageRows struct {
	values [][]string
	index  int
}

func (rows *replyThreadChannelMessageRows) Close() {}

func (rows *replyThreadChannelMessageRows) Err() error {
	return nil
}

func (rows *replyThreadChannelMessageRows) Next() bool {
	if rows.index >= len(rows.values) {
		return false
	}
	rows.index++
	return true
}

func (rows *replyThreadChannelMessageRows) Scan(dest ...any) error {
	value := rows.values[rows.index-1]
	*dest[0].(*string) = value[0]
	*dest[1].(*string) = value[1]
	return nil
}

func sourceFromFunctionForTest(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]
}