package app

import (
	"context"
	"database/sql"
	"sort"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

// TestChannelAttachmentProjectionReadMatchesLegacy seeds channel + thread
// attachments of mixed kinds and MIME casing, then asserts the projection read
// path returns the same items in the same order as an INDEPENDENT legacy oracle
// (the still-present candidate builder + per-message attachment loader), across
// kind filters, search, member filter, and across cursor pages.
func TestChannelAttachmentProjectionReadMatchesLegacy(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 10, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_oracle", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_oracle_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_oracle", DisplayName: "cap_oracle"}
	peerUser := model.User{ID: peer.UserID, Name: "cap_oracle_peer", DisplayName: "cap_oracle_peer"}

	// Channel message 1: a pdf + an image, body does not mention "budget".
	msgBudget, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "files for the meeting", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_oracle_budget_pdf", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msgBudget.ID, "budget.pdf", "application/pdf", now)
	insertAttachment(t, pool, "cap_oracle_cover", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msgBudget.ID, "cover.png", "image/png", now.Add(time.Millisecond))
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msgBudget.ID))

	// Channel message 2 (peer): mixed-case MIME image.
	msgPeer, _, err := store.SendMessageForUser(ctx, peerUser, fixture.WorkspaceID, fixture.AllChannelID, "peer slide", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_oracle_peer_img", fixture.WorkspaceID, fixture.AllChannelID, peer.MemberID, msgPeer.ID, "slide.PNG", "Image/PNG", now.Add(time.Second))
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msgPeer.ID))

	// Thread message under a channel root: a text file.
	parent, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "thread root budget notes", nil)
	require.NoError(t, err)
	replyThread, err := message.OpenReplyThreadForMember(ctx, pool, fixture.MemberID, fixture.WorkspaceID, fixture.AllChannelID, parent.ID)
	require.NoError(t, err)
	msgThread, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, replyThread.ThreadChannelID, "thread file", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_oracle_thread", fixture.WorkspaceID, replyThread.ThreadChannelID, fixture.MemberID, msgThread.ID, "notes.txt", "text/plain", now.Add(2*time.Second))
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msgThread.ID))

	opts := []message.ChannelAttachmentListOptions{
		{IncludeThreads: true, Limit: 10},
		{IncludeThreads: false, Limit: 10},
		{Kind: message.ChannelAttachmentKindImage, IncludeThreads: true, Limit: 10},
		{Kind: message.ChannelAttachmentKindFile, IncludeThreads: true, Limit: 10},
		{Query: "budget", IncludeThreads: true, Limit: 10},
		{Query: "budget", Kind: message.ChannelAttachmentKindImage, IncludeThreads: true, Limit: 10},
		{FromMemberID: peer.MemberID, IncludeThreads: true, Limit: 10},
	}
	for _, opt := range opts {
		opt := opt
		t.Run(strings.TrimSpace(strings.Join([]string{string(opt.Kind), opt.Query, opt.FromMemberID, boolStr(opt.IncludeThreads)}, "|")), func(t *testing.T) {
			legacy, err := legacyListChannelAttachmentsForUser(ctx, store, fixture.WorkspaceID, fixture.MemberID, fixture.AllChannelID, opt)
			require.NoError(t, err)
			proj, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, opt)
			require.NoError(t, err)
			require.Equal(t, legacy.HasMore, proj.HasMore, "HasMore")
			require.Equal(t, attachmentItemViews(legacy.Items), attachmentItemViews(proj.Items), "items mismatch")
		})
	}

	// Cursor pagination differential: walk legacy and projection INDEPENDENTLY,
	// each advancing with its own NextCursor; compare every page (HasMore,
	// NextCursor, full item views) and the full collected set.
	t.Run("cursor_pages", func(t *testing.T) {
		base := message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 2}
		legacyCursor, projCursor := message.ChannelAttachmentCursor{}, message.ChannelAttachmentCursor{}
		legacyAll, projAll := make([]attachmentItemView, 0), make([]attachmentItemView, 0)
		reachedTerminal := false
		for i := 0; i < 20; i++ {
			legacyPage, err := legacyListChannelAttachmentsForUser(ctx, store, fixture.WorkspaceID, fixture.MemberID, fixture.AllChannelID, withCursor(base, legacyCursor))
			require.NoError(t, err)
			projPage, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, withCursor(base, projCursor))
			require.NoError(t, err)
			require.Equal(t, legacyPage.HasMore, projPage.HasMore, "HasMore page %d", i)
			require.Equal(t, legacyPage.NextCursor, projPage.NextCursor, "NextCursor page %d", i)
			require.Equal(t, attachmentItemViews(legacyPage.Items), attachmentItemViews(projPage.Items), "items page %d", i)
			legacyAll = append(legacyAll, attachmentItemViews(legacyPage.Items)...)
			projAll = append(projAll, attachmentItemViews(projPage.Items)...)
			if !projPage.HasMore {
				reachedTerminal = true
				break
			}
			legacyCursor = legacyPage.NextCursor
			projCursor = projPage.NextCursor
		}
		require.True(t, reachedTerminal, "cursor walk must reach the terminal page, not stop early")
		require.Equal(t, legacyAll, projAll, "full collected set must match across independent cursor walks")
	})

	// Thread-only listing differential: legacy candidate builder scoped to the
	// thread vs projection ListThreadAttachmentsForUser.
	t.Run("thread_listing", func(t *testing.T) {
		legacy, err := legacyListChannelAttachmentsForUser(ctx, store, fixture.WorkspaceID, fixture.MemberID, replyThread.ThreadChannelID, message.ChannelAttachmentListOptions{Limit: 10})
		require.NoError(t, err)
		proj, err := store.ListThreadAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, replyThread.ThreadChannelID, message.ChannelAttachmentListOptions{Limit: 10})
		require.NoError(t, err)
		require.Equal(t, legacy.HasMore, proj.HasMore)
		require.Equal(t, attachmentItemViews(legacy.Items), attachmentItemViews(proj.Items))
	})

	// Date-window differential. Spread the seeded messages' created_at to fixed,
	// distinct timestamps so a window meaningfully narrows (a proper subset, not
	// the whole set) and the predicate is actually exercised.
	t.Run("date_window", func(t *testing.T) {
		base := time.Date(2026, 6, 30, 10, 0, 0, 0, time.UTC)
		mustExec(t, pool, `UPDATE scope_messages SET created_at = $1 WHERE id = $2`, base, msgBudget.ID)
		mustExec(t, pool, `UPDATE scope_messages SET created_at = $1 WHERE id = $2`, base.Add(10*time.Second), msgPeer.ID)
		mustExec(t, pool, `UPDATE scope_messages SET created_at = $1 WHERE id = $2`, base.Add(20*time.Second), parent.ID)
		mustExec(t, pool, `UPDATE scope_messages SET created_at = $1 WHERE id = $2`, base.Add(30*time.Second), msgThread.ID)
		// message_created_at is snapshotted at reconcile time; refresh the rows so
		// the date filter sees the moved timestamps (created_at does not change in
		// production; this re-reconcile is a test artifact).
		for _, mid := range []string{msgBudget.ID, msgPeer.ID, msgThread.ID} {
			require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, mid))
		}
		from := base.Add(5 * time.Second)
		to := base.Add(15 * time.Second)
		legacy, err := legacyListChannelAttachmentsForUser(ctx, store, fixture.WorkspaceID, fixture.MemberID, fixture.AllChannelID, message.ChannelAttachmentListOptions{DateFrom: &from, DateTo: &to, IncludeThreads: true, Limit: 10})
		require.NoError(t, err)
		proj, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{DateFrom: &from, DateTo: &to, IncludeThreads: true, Limit: 10})
		require.NoError(t, err)
		require.Len(t, proj.Items, 1, "date window [T+5s,T+15s] must include only the T+10s message")
		require.Equal(t, legacy.HasMore, proj.HasMore)
		require.Equal(t, attachmentItemViews(legacy.Items), attachmentItemViews(proj.Items))
		// The predicate must actually narrow: a proper subset of the unfiltered set.
		allProj, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 50})
		require.NoError(t, err)
		require.Less(t, len(proj.Items), len(allProj.Items), "date window must narrow the result vs unfiltered")
	})
}

// TestChannelAttachmentProjectionReconciledOnClaim verifies the
// applyMessagePlanTx reconcile hook populates the projection when attachments
// are claimed on send (no manual reconcile).
func TestChannelAttachmentProjectionReconciledOnClaim(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 11, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_claim", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_claim_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_claim", DisplayName: "cap_claim"}

	insertAttachment(t, pool, "cap_claim_att", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, "", "doc.pdf", "application/pdf", now)

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "claim body", []string{"cap_claim_att"})
	require.NoError(t, err)

	var count int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM channel_attachment_projection WHERE message_id = $1`, msg.ID).Scan(&count))
	require.Equal(t, 1, count, "projection row should be auto-created on claim")
}

// TestChannelAttachmentProjectionScopeMismatchSkipped (NEW 5) verifies that an
// attachment whose scope_id disagrees with its message's scope_id is NOT
// projected, so a member of the attachment's scope cannot read live content
// belonging to the message's scope (matching the legacy authority, which
// authorizes by the message's scope).
func TestChannelAttachmentProjectionScopeMismatchSkipped(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 13, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_scope_mismatch", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_scope_mismatch_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_scope_mismatch", DisplayName: "cap_scope_mismatch"}

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "original body in channel A", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_scope_mismatch_att", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "doc.txt", "text/plain", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	var count int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM channel_attachment_projection WHERE message_id = $1`, msg.ID).Scan(&count))
	require.Equal(t, 1, count, "consistent-scope attachment is projected")

	// Open a reply thread so we have a real, distinct scope to move the
	// attachment to (its message stays in the channel).
	threadParent, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "thread parent for other scope", nil)
	require.NoError(t, err)
	replyThread, err := message.OpenReplyThreadForMember(ctx, pool, fixture.MemberID, fixture.WorkspaceID, fixture.AllChannelID, threadParent.ID)
	require.NoError(t, err)
	require.NotEqual(t, fixture.AllChannelID, replyThread.ThreadChannelID, "test needs a distinct scope")

	// Corrupt: move the attachment to the thread scope while its message stays in
	// the channel scope (attachment.scope != message.scope).
	mustExec(t, pool, `UPDATE attachments SET scope_id = $1 WHERE id = $2`, replyThread.ThreadChannelID, "cap_scope_mismatch_att")
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM channel_attachment_projection WHERE message_id = $1`, msg.ID).Scan(&count))
	require.Equal(t, 0, count, "scope-mismatched attachment must not be projected (no cross-scope content read)")
}

// TestChannelAttachmentProjectionContentLiveJoinedOnBodyUpdate (I2) verifies
// the message body is live-joined: updating scope_messages.content after the
// projection row exists (without re-reconciling) is reflected in the read
// snippet immediately. Covers notification update / edit / any mutation path.
func TestChannelAttachmentProjectionContentLiveJoinedOnBodyUpdate(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 13, 30, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_body_update", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_body_update_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_body_update", DisplayName: "cap_body_update"}

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "first body text", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_body_update_att", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "doc.txt", "text/plain", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	before, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, before.Items, 1)
	require.Contains(t, before.Items[0].Snippet, "first body text")

	// Mutate the body directly (simulates notification update / edit / any path).
	mustExec(t, pool, `UPDATE scope_messages SET content = $1 WHERE id = $2`, "second body text after update", msg.ID)

	after, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, after.Items, 1)
	require.Contains(t, after.Items[0].Snippet, "second body text after update", "snippet must reflect the live-joined current body, not the stale snapshot")
	require.NotContains(t, after.Items[0].Snippet, "first body text")

	// Search freshness via the live-joined body: the old body must miss and the
	// new body must hit.
	oldQuery, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{Query: "first body", IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Empty(t, oldQuery.Items, "search for the old body must miss after the update")
	newQuery, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{Query: "second body", IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, newQuery.Items, 1)
	require.Equal(t, "cap_body_update_att", newQuery.Items[0].ID)
}

// TestChannelAttachmentProjectionThreadDirectChildMembership (NEW 4) verifies a
// viewer who holds ONLY a direct thread-scope (child) membership -- no parent
// channel membership -- can list the thread's attachments. This exercises the
// restored direct-child membership branch (sm.scope_id = p.scope_id); the
// earlier equi-key collapse dropped it and returned an empty page despite the
// API authorizing the viewer.
func TestChannelAttachmentProjectionThreadDirectChildMembership(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 14, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_child", now)
	// peer2: workspace member that will get ONLY a thread-scope membership.
	peer2 := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_child_peer2", now)
	user := model.User{ID: fixture.UserID, Name: "cap_child", DisplayName: "cap_child"}

	parent, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "thread parent", nil)
	require.NoError(t, err)
	replyThread, err := message.OpenReplyThreadForMember(ctx, pool, fixture.MemberID, fixture.WorkspaceID, fixture.AllChannelID, parent.ID)
	require.NoError(t, err)
	threadMsg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, replyThread.ThreadChannelID, "thread file", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_child_att", fixture.WorkspaceID, replyThread.ThreadChannelID, fixture.MemberID, threadMsg.ID, "thread.txt", "text/plain", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, threadMsg.ID))

	// Seed peer2 a direct thread-scope (child) membership with NO parent channel
	// membership, raw at the scope_memberships layer. This is deliberately NOT
	// routed through EnsureScopeMembership: #3459 added a write-path fence
	// (ErrScopeParentMembershipInactive) that correctly refuses to grant a
	// channel reply-thread child membership to a member not active in the parent
	// channel, so a removed member cannot be revived on a reply thread. This raw
	// seed models a pre-existing / low-level membership state (created before
	// that fence, or via an admin path) the read path must still serve
	// correctly; it does NOT mean the public API allows creating this state. The
	// test's scope remains the projection READ direct-child branch (see above).
	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)
`, replyThread.ThreadChannelID, peer2.MemberID, now)

	// Exercise the projection membership directly (the API gate may require
	// parent-channel access for channel reply threads; the direct-child branch
	// is what NEW 4 restored, so test it at the projection layer).
	page, err := store.messageStore().ListChannelAttachmentsFromProjection(ctx, message.ChannelAttachmentProjectionListRequest{
		WorkspaceID:    fixture.WorkspaceID,
		ViewerMemberID: peer2.MemberID,
		ThreadScopeID:  replyThread.ThreadChannelID,
		Limit:          10,
	})
	require.NoError(t, err)
	require.Len(t, page.Items, 1, "direct-child thread membership must surface the thread attachment")
	require.Equal(t, "cap_child_att", page.Items[0].ID)
}

// TestChannelAttachmentProjectionRootScopeGuardCrossChannel (I3) verifies the
// root-scope guard in the single-query read: a thread attachment whose
// thread_root_message_id is malformed to point at a message in a DIFFERENT
// channel must not surface that channel's text as the thread title.
func TestChannelAttachmentProjectionRootScopeGuardCrossChannel(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 15, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_root_guard", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_root_guard_peer", now)
	channelA := fixture.AllChannelID
	const channelB = "chn_cap_root_guard_B"
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelA, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, channelB, "b", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_root_guard", DisplayName: "cap_root_guard"}

	parent, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, channelA, "root in A", nil)
	require.NoError(t, err)
	replyThread, err := message.OpenReplyThreadForMember(ctx, pool, fixture.MemberID, fixture.WorkspaceID, channelA, parent.ID)
	require.NoError(t, err)
	threadMsg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, replyThread.ThreadChannelID, "thread file", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_root_guard_att", fixture.WorkspaceID, replyThread.ThreadChannelID, fixture.MemberID, threadMsg.ID, "t.txt", "text/plain", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, threadMsg.ID))

	// A message row in a different channel (direct insert -- the cross-scope root).
	mustExec(t, pool, `
INSERT INTO scope_messages (id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id, author_type, client_message_id, message_type, content, metadata, created_at, updated_at)
VALUES ($1, $2, $3, 1, 1, $4, 'human', NULL, 'user', $5, '{}', $6, $6)
`, "cap_root_guard_cross", fixture.WorkspaceID, channelB, fixture.MemberID, "secret from channel B", now)

	// Malformed topology: repoint the thread attachment's root at the channel-B message.
	mustExec(t, pool, `UPDATE channel_attachment_projection SET thread_root_message_id = $1 WHERE id = $2`, "cap_root_guard_cross", "cap_root_guard_att")

	page, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, channelA, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, page.Items, 1)
	require.Empty(t, page.Items[0].Message.ThreadTitle, "cross-channel root must not leak as the thread title")
	require.NotContains(t, page.Items[0].Message.ThreadTitle, "secret from channel B")
}

// TestChannelAttachmentProjectionScrubbedOnRecall verifies the tombstone step
// deletes projection rows when a message is recalled.
func TestChannelAttachmentProjectionScrubbedOnRecall(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 12, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_recall", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_recall_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_recall", DisplayName: "cap_recall"}

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "recall me", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_recall_att", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "gone.txt", "text/plain", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	_, err = store.RecallMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, msg.ID, message.HumanMessageRecallOptions{})
	require.NoError(t, err)

	var count int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM channel_attachment_projection WHERE message_id = $1`, msg.ID).Scan(&count))
	require.Equal(t, 0, count, "projection row should be scrubbed on recall")
}

// TestChannelAttachmentProjectionThreadRootRecallUpdatesLiveTitle (Critical)
// verifies that recalling a thread root updates the thread title shown on reply
// attachment rows. The projection must NOT serve stale pre-recall root text;
// it live-joins the root message, so a recalled root yields an empty title.
func TestChannelAttachmentProjectionThreadRootRecallUpdatesLiveTitle(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 13, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_root_recall", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_root_recall_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_root_recall", DisplayName: "cap_root_recall"}

	parent, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "secret budget title", nil)
	require.NoError(t, err)
	replyThread, err := message.OpenReplyThreadForMember(ctx, pool, fixture.MemberID, fixture.WorkspaceID, fixture.AllChannelID, parent.ID)
	require.NoError(t, err)
	threadMsg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, replyThread.ThreadChannelID, "reply file", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_root_recall_att", fixture.WorkspaceID, replyThread.ThreadChannelID, fixture.MemberID, threadMsg.ID, "reply.txt", "text/plain", now.Add(time.Second))
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, threadMsg.ID))

	before, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.NotEmpty(t, before.Items)
	require.Equal(t, "secret budget title", before.Items[0].Message.ThreadTitle, "thread title reflects current root text before recall")

	_, err = store.RecallMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, parent.ID, message.HumanMessageRecallOptions{})
	require.NoError(t, err)

	after, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.NotEmpty(t, after.Items)
	require.Equal(t, parent.ID, after.Items[0].Message.ThreadRootMessageID)
	require.NotEqual(t, "secret budget title", after.Items[0].Message.ThreadTitle, "thread title must reflect the recalled root, not stale pre-recall text")
	require.NotContains(t, after.Items[0].Message.ThreadTitle, "budget", "recalled root text must not leak into thread title")
}

// TestChannelAttachmentProjectionThreadRootEditUpdatesLiveTitle (Critical)
// verifies that editing a thread root updates the thread title shown on reply
// attachment rows (the same reverse-dependency as recall, for edits).
func TestChannelAttachmentProjectionThreadRootEditUpdatesLiveTitle(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 13, 30, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_root_edit", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_root_edit_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_root_edit", DisplayName: "cap_root_edit"}

	parent, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "old title", nil)
	require.NoError(t, err)
	replyThread, err := message.OpenReplyThreadForMember(ctx, pool, fixture.MemberID, fixture.WorkspaceID, fixture.AllChannelID, parent.ID)
	require.NoError(t, err)
	threadMsg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, replyThread.ThreadChannelID, "reply file", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_root_edit_att", fixture.WorkspaceID, replyThread.ThreadChannelID, fixture.MemberID, threadMsg.ID, "reply.txt", "text/plain", now.Add(time.Second))
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, threadMsg.ID))

	_, err = store.EditMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, parent.ID, "new title after edit", message.HumanMessageEditOptions{})
	require.NoError(t, err)

	page, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.NotEmpty(t, page.Items)
	require.Equal(t, "new title after edit", page.Items[0].Message.ThreadTitle, "thread title must reflect the edited root, not stale pre-edit text")
}

// TestChannelAttachmentProjectionSearchIsMessageLevel (Important) verifies that
// a search match is message-level: when a message has a matching attachment
// plus a non-matching attachment, BOTH are returned (legacy contract). The
// row-level regression returned only the matching row.
func TestChannelAttachmentProjectionSearchIsMessageLevel(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 14, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_search", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_search_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_search", DisplayName: "cap_search"}

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "files for the meeting", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_search_budget", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "budget.pdf", "application/pdf", now)
	insertAttachment(t, pool, "cap_search_cover", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "cover.png", "image/png", now.Add(time.Millisecond))
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	page, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{Query: "budget", IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	ids := make([]string, 0, len(page.Items))
	for _, item := range page.Items {
		ids = append(ids, item.ID)
	}
	require.ElementsMatch(t, []string{"cap_search_budget", "cap_search_cover"}, ids, "message-level search must return every attachment of a matching message")

	imagesOnly, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{Query: "budget", Kind: message.ChannelAttachmentKindImage, IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, imagesOnly.Items, 1)
	require.Equal(t, "cap_search_cover", imagesOnly.Items[0].ID)
}

// TestChannelAttachmentProjectionMIMEKindCaseInsensitive (Important) verifies
// that mixed-case MIME content types (e.g. "Image/PNG") are classified and
// filtered consistently with the lowercase canonical form.
func TestChannelAttachmentProjectionMIMEKindCaseInsensitive(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 14, 30, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_mime", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_mime_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_mime", DisplayName: "cap_mime"}

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "mixed case mime", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_mime_img", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "Slide.PNG", "Image/PNG", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	images, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{Kind: message.ChannelAttachmentKindImage, IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, images.Items, 1)
	require.Equal(t, message.ChannelAttachmentKindImage, images.Items[0].Kind)
	require.Equal(t, "Image/PNG", images.Items[0].Attachment.ContentType)

	files, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{Kind: message.ChannelAttachmentKindFile, IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Empty(t, files.Items, "mixed-case image MIME must not leak into the file kind")
}

// TestChannelAttachmentProjectionAuthorLiveJoinedAfterProfileChange (Important)
// verifies the author display name is live-joined: updating the author's
// profile after the projection row was written is reflected without reconcile.
func TestChannelAttachmentProjectionAuthorLiveJoinedAfterProfileChange(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 15, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_author", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_author_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_author", DisplayName: "cap_author"}

	msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "author change", nil)
	require.NoError(t, err)
	insertAttachment(t, pool, "cap_author_att", fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "doc.txt", "text/plain", now)
	require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))

	mustExec(t, pool, `UPDATE users SET display_name = $1 WHERE id = $2`, "Original Name", fixture.UserID)

	before, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, before.Items, 1)
	require.Equal(t, "Original Name", before.Items[0].Author.DisplayName)

	mustExec(t, pool, `UPDATE users SET display_name = $1 WHERE id = $2`, "Updated Name", fixture.UserID)

	after, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 10})
	require.NoError(t, err)
	require.Len(t, after.Items, 1)
	require.Equal(t, "Updated Name", after.Items[0].Author.DisplayName, "author display name must be live-joined, not stale")
}

// TestChannelAttachmentProjectionQueryUsesOrderCompatibleIndex (Important)
// provides EXPLAIN evidence that the channel attachment projection read uses
// the order-compatible DESC index (cap_workspace_parent_order_idx) as a bounded
// range scan for both the first page and a deep page.
//
// The ORDER BY + LIMIT is executed by TiDB as a bounded TopN (a heap of size
// LIMIT, pushed to TiKV) rather than an in-order index stream, because the
// visibility membership predicate (direct-scope OR thread-via-parent) is
// decorrelated into a semi-join that does not preserve the index order. A
// bounded TopN over a bounded range scan is the efficient paginated pattern
// (not a full table sort) and is a single pass versus the retired legacy
// 14-JOIN x 8-batch candidate path.
func TestChannelAttachmentProjectionQueryUsesOrderCompatibleIndex(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{})
	now := time.Date(2026, 6, 30, 16, 0, 0, 0, time.UTC)
	fixture := seedAppAdminFixture(t, pool, "cap_explain", now)
	peer := seedWorkspaceMemberFixture(t, pool, fixture.WorkspaceID, "cap_explain_peer", now)
	seedChannelParticipantProjectionForTests(t, pool, fixture.WorkspaceID, fixture.AllChannelID, "all", "", "public", fixture.MemberID, peer.MemberID, now)
	user := model.User{ID: fixture.UserID, Name: "cap_explain", DisplayName: "cap_explain"}

	for i := 0; i < 40; i++ {
		msg, _, err := store.SendMessageForUser(ctx, user, fixture.WorkspaceID, fixture.AllChannelID, "explain row", nil)
		require.NoError(t, err)
		insertAttachment(t, pool, "cap_explain_att_"+strconvI(i), fixture.WorkspaceID, fixture.AllChannelID, fixture.MemberID, msg.ID, "file.txt", "text/plain", now.Add(time.Duration(i)*time.Second))
		require.NoError(t, message.ReconcileChannelAttachmentProjectionForMessage(ctx, pool, msg.ID))
	}

	// Sanity: first page of 30 with more to come (large sample, not 5 rows).
	page, err := store.ListChannelAttachmentsForUser(ctx, fixture.UserID, fixture.WorkspaceID, fixture.AllChannelID, message.ChannelAttachmentListOptions{IncludeThreads: true, Limit: 30})
	require.NoError(t, err)
	require.Len(t, page.Items, 30)
	require.True(t, page.HasMore)

	// EXPLAIN on the large sample, first page and a real deep page (cursor at the
	// 30th item, not an empty tail). Plain EXPLAIN (the harness rows scan is
	// fixed-width); the operator structure is what the assertion checks.
	cases := []struct {
		name   string
		cursor message.ChannelAttachmentCursor
	}{
		{name: "first_page", cursor: message.ChannelAttachmentCursor{}},
		{name: "deep_page", cursor: page.Items[29].Cursor},
	}
	for _, c := range cases {
		c := c
		t.Run(c.name, func(t *testing.T) {
			req := message.ChannelAttachmentProjectionListRequest{
				WorkspaceID:    fixture.WorkspaceID,
				ViewerMemberID: fixture.MemberID,
				ChannelID:      fixture.AllChannelID,
				IncludeThreads: true,
				Limit:          30,
				Cursor:         c.cursor,
			}
			query, args, err := message.BuildChannelAttachmentProjectionListQuery(req)
			require.NoError(t, err)
			plan := explainQuery(t, ctx, pool, "EXPLAIN "+query, args)
			t.Logf("EXPLAIN %s:\n%s", c.name, plan)
			require.Contains(t, plan, "cap_workspace_parent_order_idx", "read should use the order-compatible DESC index")
			require.Contains(t, plan, "IndexRangeScan", "read should be a bounded index range scan")
			require.NotContains(t, plan, "TableFullScan", "read must not full-scan the projection table")
		})
	}
}

// legacyListChannelAttachmentsForUser is an INDEPENDENT oracle reproducing the
// pre-projection flow (batched candidate query -> per-message attachment loader
// -> kind filter -> sort -> paginate) via the still-present legacy candidate
// builder. It does not touch the projection read path.
func legacyListChannelAttachmentsForUser(ctx context.Context, store *Store, workspaceID string, viewerMemberID string, channelID string, options message.ChannelAttachmentListOptions) (message.ChannelAttachmentPage, error) {
	const candidateBatches = 8
	options.Kind = message.NormalizeChannelAttachmentKind(options.Kind)
	if options.Limit <= 0 {
		options.Limit = 30
	}
	if options.Limit > 100 {
		options.Limit = 100
	}

	items := make([]message.ChannelAttachmentItem, 0, options.Limit+1)
	candidateCursor := message.ChannelAttachmentCursor{
		CreatedAt:       options.Cursor.CreatedAt,
		MessageID:       strings.TrimSpace(options.Cursor.MessageID),
		ResourceOrdinal: options.Cursor.ResourceOrdinal,
	}
	startAfterOrdinal := options.Cursor.ResourceOrdinal
	for batch := 0; batch < candidateBatches && len(items) <= options.Limit; batch++ {
		candidates, err := store.messageStore().ListChannelAttachmentMessageCandidates(ctx, message.ChannelAttachmentMessageCandidateRequest{
			WorkspaceID:    workspaceID,
			ViewerMemberID: viewerMemberID,
			ChannelID:      channelID,
			Kind:           options.Kind,
			Query:          options.Query,
			FromMemberID:   options.FromMemberID,
			DateFrom:       options.DateFrom,
			DateTo:         options.DateTo,
			IncludeThreads: options.IncludeThreads,
			Cursor:         candidateCursor,
			CandidateLimit: options.Limit + 1,
		})
		if err != nil {
			return message.ChannelAttachmentPage{}, err
		}
		if len(candidates) == 0 {
			break
		}
		messageIDs := make([]string, 0, len(candidates))
		for _, candidate := range candidates {
			messageIDs = append(messageIDs, candidate.ID)
		}
		byMessage, err := store.loadAttachmentsForMessages(ctx, workspaceID, messageIDs)
		if err != nil {
			return message.ChannelAttachmentPage{}, err
		}
		for _, candidate := range candidates {
			resources := legacyResourcesForCandidate(candidate, byMessage[candidate.ID], options.Kind)
			if candidate.ID == options.Cursor.MessageID && startAfterOrdinal > 0 {
				resources = legacyResourcesAfterOrdinal(resources, startAfterOrdinal)
			}
			items = append(items, resources...)
			if len(items) > options.Limit {
				break
			}
		}
		last := candidates[len(candidates)-1]
		candidateCursor = message.ChannelAttachmentCursor{CreatedAt: last.CreatedAt, MessageID: last.ID}
		startAfterOrdinal = 0
		if len(candidates) < options.Limit+1 {
			break
		}
	}

	sort.SliceStable(items, func(i, j int) bool {
		if !items[i].CreatedAt.Equal(items[j].CreatedAt) {
			return items[i].CreatedAt.After(items[j].CreatedAt)
		}
		if items[i].Message.MessageID != items[j].Message.MessageID {
			return items[i].Message.MessageID < items[j].Message.MessageID
		}
		return items[i].Cursor.ResourceOrdinal < items[j].Cursor.ResourceOrdinal
	})

	page := message.ChannelAttachmentPage{Items: items}
	if len(items) > options.Limit {
		page.HasMore = true
		page.Items = items[:options.Limit]
		page.NextCursor = page.Items[len(page.Items)-1].Cursor
	}
	return page, nil
}

func legacyResourcesForCandidate(candidate message.ChannelAttachmentMessageCandidate, attachments []attachment.AppAttachment, kind message.ChannelAttachmentKind) []message.ChannelAttachmentItem {
	items := make([]message.ChannelAttachmentItem, 0, len(attachments)+2)
	ordinal := 0
	location := legacyResourceLocation(candidate)
	for _, attachmentItem := range attachments {
		ordinal++
		detail := message.ChannelAttachmentDetailFromAppAttachment(attachmentItem)
		resourceKind := message.ChannelAttachmentKindFile
		if strings.HasPrefix(strings.ToLower(strings.TrimSpace(detail.ContentType)), "image/") {
			resourceKind = message.ChannelAttachmentKindImage
		}
		if kind != message.ChannelAttachmentKindAll && kind != resourceKind {
			continue
		}
		title := strings.TrimSpace(detail.Filename)
		if title == "" {
			title = strings.TrimSpace(detail.ID)
		}
		items = append(items, message.ChannelAttachmentItem{
			ID:         strings.TrimSpace(detail.ID),
			Kind:       resourceKind,
			Title:      title,
			CreatedAt:  candidate.CreatedAt,
			Author:     candidate.AuthorProfile,
			Message:    location,
			Snippet:    legacyResourceSnippet(candidate.Content),
			Attachment: &detail,
			Cursor: message.ChannelAttachmentCursor{
				CreatedAt:       candidate.CreatedAt,
				MessageID:       candidate.ID,
				ResourceOrdinal: ordinal,
			},
		})
	}
	return items
}

func legacyResourcesAfterOrdinal(items []message.ChannelAttachmentItem, ordinal int) []message.ChannelAttachmentItem {
	if ordinal <= 0 {
		return items
	}
	filtered := items[:0]
	for _, item := range items {
		if item.Cursor.ResourceOrdinal > ordinal {
			filtered = append(filtered, item)
		}
	}
	return filtered
}

func legacyResourceLocation(candidate message.ChannelAttachmentMessageCandidate) message.ChannelAttachmentMessageLocation {
	scopeType := strings.TrimSpace(candidate.ScopeType)
	parentChannelID := strings.TrimSpace(candidate.ParentChannelID)
	if parentChannelID == "" {
		parentChannelID = strings.TrimSpace(candidate.ScopeID)
	}
	location := message.ChannelAttachmentMessageLocation{
		MessageID:       strings.TrimSpace(candidate.ID),
		ScopeID:         strings.TrimSpace(candidate.ScopeID),
		ScopeType:       scopeType,
		ParentChannelID: parentChannelID,
	}
	if scopeType == "thread" {
		location.ThreadChannelID = strings.TrimSpace(candidate.ScopeID)
		location.ThreadRootMessageID = strings.TrimSpace(candidate.ThreadRootMessageID)
		location.ThreadTitle = legacyResourceSnippet(candidate.ThreadRootMessageText)
	}
	return location
}

func legacyResourceSnippet(content string) string {
	trimmed := strings.TrimSpace(strings.Join(strings.Fields(content), " "))
	if trimmed == "" {
		return ""
	}
	const limit = 180
	runes := []rune(trimmed)
	if len(runes) <= limit {
		return trimmed
	}
	return string(runes[:limit]) + "…"
}

type attachmentItemView struct {
	ID                string
	Kind              string
	Title             string
	Snippet           string
	CreatedAt         time.Time
	AuthorActorType   string
	AuthorPrincipalID string
	AuthorMemberID    string
	AuthorTargetID    string
	AuthorDisplayName string
	AuthorAvatarURL   string
	MessageID         string
	ScopeID           string
	ScopeType         string
	ParentChannelID   string
	ThreadChannelID   string
	ThreadRootID      string
	ThreadTitle       string
	AttachmentID      string
	Filename          string
	ContentType       string
	SizeBytes         int64
	ThumbnailURL      string
	CursorCreatedAt   time.Time
	CursorMessageID   string
	CursorOrdinal     int
}

func attachmentItemViews(items []message.ChannelAttachmentItem) []attachmentItemView {
	views := make([]attachmentItemView, 0, len(items))
	for _, item := range items {
		view := attachmentItemView{
			ID:                item.ID,
			Kind:              string(item.Kind),
			Title:             item.Title,
			Snippet:           item.Snippet,
			CreatedAt:         item.CreatedAt,
			AuthorActorType:   string(item.Author.ActorType),
			AuthorPrincipalID: item.Author.PrincipalID,
			AuthorMemberID:    item.Author.MemberID,
			AuthorTargetID:    item.Author.TargetID,
			AuthorDisplayName: item.Author.DisplayName,
			AuthorAvatarURL:   item.Author.AvatarURL,
			MessageID:         item.Message.MessageID,
			ScopeID:           item.Message.ScopeID,
			ScopeType:         item.Message.ScopeType,
			ParentChannelID:   item.Message.ParentChannelID,
			ThreadChannelID:   item.Message.ThreadChannelID,
			ThreadRootID:      item.Message.ThreadRootMessageID,
			ThreadTitle:       item.Message.ThreadTitle,
			CursorCreatedAt:   item.Cursor.CreatedAt,
			CursorMessageID:   item.Cursor.MessageID,
			CursorOrdinal:     item.Cursor.ResourceOrdinal,
		}
		if item.Attachment != nil {
			view.AttachmentID = item.Attachment.ID
			view.Filename = item.Attachment.Filename
			view.ContentType = item.Attachment.ContentType
			view.SizeBytes = item.Attachment.SizeBytes
			view.ThumbnailURL = item.Attachment.ThumbnailURL
		}
		views = append(views, view)
	}
	return views
}

func withCursor(base message.ChannelAttachmentListOptions, cursor message.ChannelAttachmentCursor) message.ChannelAttachmentListOptions {
	out := base
	out.Cursor = cursor
	return out
}

func boolStr(b bool) string {
	if b {
		return "inclThreads"
	}
	return "noThreads"
}

func insertAttachment(t *testing.T, pool coredb.QueryExecer, id, workspaceID, scopeID, memberID, messageID, filename, contentType string, createdAt time.Time) {
	t.Helper()
	if messageID == "" {
		mustExec(t, pool, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, message_id, filename, content_type, size_bytes, payload, created_at)
VALUES ($1, $2, $3, $4, NULL, $5, $6, 1, $7, $8)
`, id, workspaceID, scopeID, memberID, filename, contentType, []byte(filename), createdAt)
		return
	}
	mustExec(t, pool, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, message_id, filename, content_type, size_bytes, payload, created_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, 1, $8, $9)
`, id, workspaceID, scopeID, memberID, messageID, filename, contentType, []byte(filename), createdAt)
}

func strconvI(i int) string {
	if i == 0 {
		return "0"
	}
	digits := []byte{}
	n := i
	for n > 0 {
		digits = append([]byte{byte('0' + n%10)}, digits...)
		n /= 10
	}
	return string(digits)
}

func explainQuery(t *testing.T, ctx context.Context, pool coredb.DB, query string, args []any) string {
	t.Helper()
	rows, err := pool.Query(ctx, query, args...)
	require.NoError(t, err)
	defer rows.Close()
	// TiDB plain EXPLAIN columns: id, estRows, task, access_object, operator_info.
	var sb strings.Builder
	for rows.Next() {
		var id, estRows, task, accessObject, operatorInfo sql.NullString
		require.NoError(t, rows.Scan(&id, &estRows, &task, &accessObject, &operatorInfo))
		sb.WriteString(id.String)
		sb.WriteString(" | task=")
		sb.WriteString(task.String)
		sb.WriteString(" | access=")
		sb.WriteString(accessObject.String)
		sb.WriteString(" | op=")
		sb.WriteString(operatorInfo.String)
		sb.WriteString("\n")
	}
	require.NoError(t, rows.Err())
	return sb.String()
}