package message

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
)

var (
	ErrForbidden                = errors.New("forbidden")
	ErrNotFound                 = errors.New("not found")
	ErrConflict                 = errors.New("conflict")
	ErrMessageSearchUnavailable = errors.New("message search is unavailable")
	ErrContentModerationBlocked = errors.New("content moderation blocked")
)

const ContentModerationBlockedCode = "content_moderation_blocked"

type requestError struct {
	kind    error
	message string
}

func (err requestError) Error() string {
	return err.message
}

func (err requestError) Unwrap() error {
	return err.kind
}

type requestErrorWithCause struct {
	kind    error
	cause   error
	message string
}

func (err requestErrorWithCause) Error() string {
	return err.message
}

func (err requestErrorWithCause) Unwrap() []error {
	if err.cause == nil {
		return []error{err.kind}
	}
	return []error{err.kind, err.cause}
}

func invalidArgument(message string) error {
	return requestError{kind: domain.ErrInvalidArgument, message: message}
}

func conflict(message string) error {
	return requestError{kind: ErrConflict, message: message}
}

func ContentModerationBlocked(message string) error {
	return requestError{kind: ErrContentModerationBlocked, message: message}
}

// MemberActor is the minimal message-space actor identity shared by human and agent flows.
type MemberActor struct {
	WorkspaceID string
	MemberID    string
}

// ReplyThreadTarget describes one reply-thread target.
type ReplyThreadTarget struct {
	ParentMessageID   string
	ShortID           string
	ParentChannelID   string
	ParentChannelName string
}

// ThreadAccess describes one accessible channel / DM / reply-thread target.
type ThreadAccess struct {
	ThreadID     string
	ThreadType   domain.ThreadType
	ChannelID    string
	ChannelName  string
	DMThreadID   string
	PeerMemberID string
	ReplyThread  *ReplyThreadTarget
}

// ReplyThreadShape describes one reply-thread channel and its parent topology.
// It intentionally does not authorize access; callers must apply the relevant
// human/member/agent operation authority before returning or mutating data.
type ReplyThreadShape struct {
	ThreadID           string
	ParentThreadType   domain.ThreadType
	ParentScopeID      string
	PeerMemberID       string
	ParentChannelID    string
	ParentChannelName  string
	ParentMessageID    string
	ParentMessageShort string
}

// ReplyThreadParentAccess identifies the accessible parent channel or DM for reply-thread APIs.
type ReplyThreadParentAccess struct {
	ThreadID          string
	ThreadType        domain.ThreadType
	ParentChannelID   string
	ParentChannelName string
	ParentSubsystem   string
}

// ReplyThreadSummary is the ThreadSummary value returned by GET /channels/:channelId/threads.
type ReplyThreadSummary struct {
	ThreadChannelID        string
	RootMessageID          string
	ParentMessagePreview   string
	ParentMessageMetadata  map[string]any
	ParentMessageCreatedAt *time.Time
	SubscriptionState      string
	ReplyCount             int64
	LastReplyAt            *time.Time
	LastReplyPreview       string
	LastReplyAuthor        string
	LastReplyAuthorID      string
	LastReplyAuthorType    string
	ParticipantIDs         []string
	Participants           []ReplyThreadParticipant
	ReplyAuthorIDs         []string
	ReplyAuthors           []ReplyThreadParticipant
}

// ReplyThreadParticipant is a resolved, display-ready participant snapshot for
// reply-thread summary UI. It avoids requiring clients to guess names/avatars
// from a partial workspace member or agent directory.
type ReplyThreadParticipant struct {
	ID            string
	Type          string
	DisplayName   string
	AvatarURL     string
	MemberID      string
	OwnerMemberID string
	OwnerUserID   string
	Visibility    string
}

// ReplyThreadUpdated is the Socket.IO payload for the `thread:updated` event.
type ReplyThreadUpdated struct {
	ParentMessageID string

	ThreadChannelID string
	ReplyCount      int64
	LastReplyAt     *time.Time
	ParticipantIDs  []string
	Participants    []ReplyThreadParticipant
	ReplyAuthorIDs  []string
	ReplyAuthors    []ReplyThreadParticipant
}

// DMChannel is the response item returned by GET /channels/dm and POST /channels/dm.
type DMChannel struct {
	ID              string
	PeerType        string
	PeerID          string
	PeerName        string
	PeerDisplayName string
	PeerAvatarURL   string
	LastMessageAt   *time.Time
	SidebarHidden   bool
}

// DMParticipantUser identifies a human participant in an active DM scope.
type DMParticipantUser struct {
	UserID   string
	MemberID string
}

type channelAccess struct {
	ChannelID string
	ThreadID  string
}

type resolvedPeerMember struct {
	MemberID   string
	PublicName string
}

type replyThreadLookup struct {
	ThreadChannelID string
	ThreadID        string
	ParentMessageID string
	ShortID         string
}

var errDMDoesNotExist = errors.New("dm does not exist; send a message to start a new DM")

// ResolveChannelIDForAgentTarget maps a daemon-style target (for example `#general`, `dm:@peer`,
// `#general:shortid`) into the app-compatible `channelId` value used by attachment uploads.
func ResolveChannelIDForAgentTarget(ctx context.Context, queryer coredb.QueryExecer, actor MemberActor, target string) (string, error) {
	reference, err := parseConversationMessageTarget(target)
	if err != nil {
		return "", err
	}

	var channelID string
	switch {
	case strings.TrimSpace(reference.ShortID) != "":
		resolved, err := resolveReplyThreadSendTargetForAgent(ctx, queryer, actor, reference)
		if err != nil {
			return "", err
		}
		channelID = resolved.ChannelID
	case reference.Type == domain.ThreadTypeChannel:
		resolved, err := resolveAccessibleChannel(ctx, queryer, actor, reference.ChannelSlug)
		if err != nil {
			return "", err
		}
		channelID = resolved.ChannelID
	default:
		resolved, err := EnsureDMThread(ctx, queryer, actor, reference.PeerMemberID)
		if err != nil {
			return "", err
		}
		channelID = resolved.DMThreadID
	}

	channelID = strings.TrimSpace(channelID)
	if channelID == "" {
		return "", fmt.Errorf("failed to resolve channelId for target")
	}

	return channelID, nil
}

// ResolveSendTarget resolves a daemon send target reference, creating a DM thread only when `dmTo` is used.
func ResolveSendTarget(ctx context.Context, queryer coredb.QueryExecer, actor MemberActor, targetRef string, dmTo string) (ThreadAccess, error) {
	if dmTo != "" {
		if err := domain.ValidateMemberReference(dmTo); err != nil {
			return ThreadAccess{}, err
		}
		return EnsureDMThread(ctx, queryer, actor, dmTo)
	}

	reference, err := parseConversationMessageTarget(targetRef)
	if err != nil {
		return ThreadAccess{}, err
	}
	if strings.TrimSpace(reference.ShortID) != "" {
		return resolveReplyThreadSendTargetForAgent(ctx, queryer, actor, reference)
	}
	if reference.Type == domain.ThreadTypeDM {
		return resolveExistingDMThread(ctx, queryer, actor, reference.PeerMemberID)
	}

	resolvedChannel, err := resolveAccessibleChannel(ctx, queryer, actor, reference.ChannelSlug)
	if err != nil {
		return ThreadAccess{}, err
	}
	return ThreadAccess{
		ThreadID:    resolvedChannel.ThreadID,
		ThreadType:  domain.ThreadTypeChannel,
		ChannelID:   resolvedChannel.ChannelID,
		ChannelName: reference.ChannelSlug,
	}, nil
}

func parseConversationMessageTarget(target string) (domain.ThreadReference, error) {
	parsed, err := domain.ParseMessageTargetReference(target)
	if err != nil {
		return domain.ThreadReference{}, err
	}
	switch parsed.Kind {
	case domain.MessageTargetKindChannel, domain.MessageTargetKindReplyThread, domain.MessageTargetKindDM:
		return parsed.Thread, nil
	case domain.MessageTargetKindTask:
		return domain.ThreadReference{}, invalidArgument("task target requires the task discussion message path")
	case domain.MessageTargetKindTeam:
		return domain.ThreadReference{}, invalidArgument("Team target requires Team-aware resolution")
	default:
		return domain.ThreadReference{}, invalidArgument("unsupported message target")
	}
}

// ResolveOrEnsureChannelReplyThreadTarget resolves a channel reply-thread target
// after the caller has already authorized access to the parent channel. It keeps
// the daemon short-id behavior shared with agent sends: 6-8 char prefixes are
// accepted, existing reply threads are reused, and a missing reply thread is
// opened when the parent message can be resolved.
func ResolveOrEnsureChannelReplyThreadTarget(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, parentChannelID string, parentChannelName string, shortID string) (ThreadAccess, error) {
	trimmedShortID := strings.TrimSpace(shortID)
	if trimmedShortID == "" {
		return ThreadAccess{}, fmt.Errorf("thread short id is required")
	}

	lookup, err := loadReplyThreadLookupByShortID(ctx, queryer, parentChannelID, trimmedShortID)
	if errors.Is(err, ErrNotFound) {
		parentMessageID, resolvedShortID, resolveErr := resolveParentMessageIDByShortID(ctx, queryer, parentChannelID, trimmedShortID)
		if resolveErr != nil {
			return ThreadAccess{}, resolveErr
		}
		created, createErr := createReplyThread(ctx, queryer, workspaceID, parentChannelID, parentMessageID, resolvedShortID)
		if createErr != nil {
			return ThreadAccess{}, createErr
		}
		if created.ThreadID == "" {
			lookup, err = loadReplyThreadLookupByShortID(ctx, queryer, parentChannelID, resolvedShortID)
		} else {
			lookup = created
			err = nil
		}
	}
	if err != nil {
		return ThreadAccess{}, err
	}

	return ThreadAccess{
		ThreadID:   lookup.ThreadID,
		ThreadType: domain.ThreadTypeChannel,
		ChannelID:  lookup.ThreadChannelID,
		ReplyThread: &ReplyThreadTarget{
			ParentMessageID:   lookup.ParentMessageID,
			ShortID:           lookup.ShortID,
			ParentChannelID:   strings.TrimSpace(parentChannelID),
			ParentChannelName: strings.TrimSpace(parentChannelName),
		},
	}, nil
}

// ResolveAccessibleThread resolves a daemon history/read target without creating new threads.
func ResolveAccessibleThread(ctx context.Context, queryer coredb.Queryer, actor MemberActor, reference domain.ThreadReference) (ThreadAccess, error) {
	if strings.TrimSpace(reference.ShortID) != "" {
		return resolveReplyThreadForAgent(ctx, queryer, actor, reference)
	}
	if reference.Type == domain.ThreadTypeChannel {
		channel, err := resolveAccessibleChannel(ctx, queryer, actor, reference.ChannelSlug)
		if err != nil {
			return ThreadAccess{}, err
		}
		return ThreadAccess{
			ThreadID:    channel.ThreadID,
			ThreadType:  domain.ThreadTypeChannel,
			ChannelID:   channel.ChannelID,
			ChannelName: reference.ChannelSlug,
		}, nil
	}
	return resolveExistingDMThread(ctx, queryer, actor, reference.PeerMemberID)
}

// EnsureDMThread loads or creates a DM thread between the actor and peer member.
func EnsureDMThread(ctx context.Context, queryer coredb.QueryExecer, actor MemberActor, peerMemberID string) (ThreadAccess, error) {
	peer, err := resolvePeerMemberReference(ctx, queryer, actor.WorkspaceID, peerMemberID)
	if err != nil {
		return ThreadAccess{}, err
	}
	if peer.MemberID == actor.MemberID {
		return ThreadAccess{}, invalidArgument("cannot DM yourself")
	}

	thread, err := resolveExistingDMThread(ctx, queryer, actor, peerMemberID)
	if err == nil {
		return thread, nil
	}
	if !errors.Is(err, errDMDoesNotExist) {
		return ThreadAccess{}, err
	}

	memberAID, memberBID := orderedMembers(actor.MemberID, peer.MemberID)
	dmThreadID, err := identifiers.NewEntityID("dm")
	if err != nil {
		return ThreadAccess{}, fmt.Errorf("generate dm thread id: %w", err)
	}
	now := time.Now().UTC()

	record, err := ensureDMScopeProjectionForMembers(ctx, queryer, actor.WorkspaceID, dmThreadID, memberAID, memberBID, actor.MemberID, now)
	if err != nil {
		return ThreadAccess{}, err
	}
	dmThreadID = record.ID

	return ThreadAccess{
		ThreadID:     dmThreadID,
		ThreadType:   domain.ThreadTypeDM,
		ChannelID:    dmThreadID,
		DMThreadID:   dmThreadID,
		ChannelName:  peer.PublicName,
		PeerMemberID: peer.MemberID,
	}, nil
}

// LoadDMChannelByID loads one app-compatible DM channel summary for a participant.
func LoadDMChannelByID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, memberID string, dmThreadID string) (DMChannel, error) {
	row := queryer.QueryRow(ctx, `
SELECT
    s.id,
    peer.type,
    COALESCE(u.id, ''),
    CASE WHEN peer.type = 'human' THEN peer.id ELSE '' END,
    COALESCE(NULLIF(u.display_name, ''), NULLIF(peer.display_name, ''), ''),
    COALESCE(u.avatar_url, ''),
    COALESCE(a.id, ''),
    COALESCE(a.name, ''),
    COALESCE(peer.display_name, ''),
    COALESCE(a.avatar_url, ''),
	COALESCE(sm.service_installation_id, ''),
	COALESCE(sm.handle, ''),
	COALESCE(NULLIF(sm.display_name, ''), NULLIF(peer.display_name, ''), NULLIF(sd.name, ''), NULLIF(sm.handle, ''), ''),
	COALESCE(NULLIF(sm.avatar_url, ''), sd.avatar_url, ''),
    s.latest_message_at,
    CASE
        WHEN COALESCE(dvs.hidden_through_seq, 0) > 0
         AND COALESCE(s.latest_seq, 0) <= dvs.hidden_through_seq
        THEN TRUE
        ELSE FALSE
    END AS sidebar_hidden
FROM scopes s
JOIN scope_memberships self_sm
  ON self_sm.scope_id = s.id
 AND self_sm.member_id = $2
 AND self_sm.removed_at IS NULL
 AND self_sm.archived_at IS NULL
 AND self_sm.effective_to_seq IS NULL
JOIN scope_memberships peer_sm
  ON peer_sm.scope_id = s.id
 AND peer_sm.member_id <> $2
 AND peer_sm.removed_at IS NULL
 AND peer_sm.archived_at IS NULL
 AND peer_sm.effective_to_seq IS NULL
JOIN members peer ON peer.id = peer_sm.member_id
LEFT JOIN users u ON peer.type = 'human' AND u.id = peer.user_id
LEFT JOIN agents a ON peer.type = 'agent' AND a.member_id = peer.id
LEFT JOIN service_members sm
  ON peer.type = 'service'
 AND sm.workspace_id = s.workspace_id
 AND sm.member_id = peer.id
LEFT JOIN service_definitions sd
  ON sd.id = sm.service_definition_id
 AND sd.service_kind = sm.service_kind
LEFT JOIN dm_view_states dvs ON dvs.dm_thread_id = s.id AND dvs.member_id = $2
WHERE s.workspace_id = $1
  AND s.id = $3
  AND s.type = 'dm'
  AND s.status = 'active'
  AND s.archived_at IS NULL
ORDER BY peer_sm.member_id ASC
LIMIT 1
`, workspaceID, memberID, dmThreadID)

	var dm DMChannel
	var peerType string
	var userID string
	var humanPublicName string
	var userDisplayName string
	var userAvatarURL string
	var agentID string
	var agentName string
	var agentDisplayName string
	var agentAvatarURL string
	var serviceInstallationID string
	var serviceHandle string
	var serviceDisplayName string
	var serviceAvatarURL string
	if err := row.Scan(&dm.ID, &peerType, &userID, &humanPublicName, &userDisplayName, &userAvatarURL, &agentID, &agentName, &agentDisplayName, &agentAvatarURL, &serviceInstallationID, &serviceHandle, &serviceDisplayName, &serviceAvatarURL, &dm.LastMessageAt, &dm.SidebarHidden); err != nil {
		if coredb.IsNotFound(err) {
			return DMChannel{}, ErrNotFound
		}
		return DMChannel{}, fmt.Errorf("load dm channel: %w", err)
	}

	switch peerType {
	case "human":
		dm.PeerType = "user"
		dm.PeerID = userID
		dm.PeerName = humanPublicName
		dm.PeerDisplayName = userDisplayName
		dm.PeerAvatarURL = userAvatarURL
	case "agent":
		dm.PeerType = "agent"
		dm.PeerID = agentID
		dm.PeerName = agentName
		dm.PeerDisplayName = agentDisplayName
		dm.PeerAvatarURL = agentAvatarURL
	case "service":
		dm.PeerType = "service"
		dm.PeerID = firstNonEmpty(serviceInstallationID, serviceHandle)
		dm.PeerName = firstNonEmpty(serviceHandle, dm.PeerID)
		dm.PeerDisplayName = firstNonEmpty(serviceDisplayName, dm.PeerName)
		dm.PeerAvatarURL = serviceAvatarURL
	default:
		return DMChannel{}, fmt.Errorf("invalid dm peer type %q", peerType)
	}

	return dm, nil
}

// ResolveDMThreadByIDForMember resolves a DM thread id into thread access for one participant.
func ResolveDMThreadByIDForMember(ctx context.Context, queryer coredb.QueryRower, currentMemberID string, workspaceID string, dmThreadID string) (ThreadAccess, error) {
	row := queryer.QueryRow(ctx, `
SELECT
    s.id,
    s.id,
    peer_sm.member_id,
    CASE WHEN peer.type = 'human' THEN peer.id ELSE '' END,
	COALESCE(peer_agent.name, ''),
	COALESCE(peer_service.handle, '')
FROM scopes s
JOIN scope_memberships self_sm
  ON self_sm.scope_id = s.id
 AND self_sm.member_id = $2
 AND self_sm.removed_at IS NULL
 AND self_sm.archived_at IS NULL
 AND self_sm.effective_to_seq IS NULL
JOIN scope_memberships peer_sm
  ON peer_sm.scope_id = s.id
 AND peer_sm.member_id <> $2
 AND peer_sm.removed_at IS NULL
 AND peer_sm.archived_at IS NULL
 AND peer_sm.effective_to_seq IS NULL
JOIN members peer ON peer.id = peer_sm.member_id
LEFT JOIN users peer_user ON peer.type = 'human' AND peer_user.id = peer.user_id
LEFT JOIN agents peer_agent ON peer.type = 'agent' AND peer_agent.member_id = peer.id
LEFT JOIN service_members peer_service
  ON peer.type = 'service'
 AND peer_service.workspace_id = s.workspace_id
 AND peer_service.member_id = peer.id
WHERE s.workspace_id = $1
  AND s.id = $3
  AND s.type = 'dm'
  AND s.status = 'active'
  AND s.archived_at IS NULL
ORDER BY peer_sm.member_id ASC
LIMIT 1
`, workspaceID, currentMemberID, dmThreadID)
	var resolvedDMThreadID string
	var threadID string
	var peerMemberID string
	var humanPublicName string
	var peerAgentName string
	var peerServiceHandle string
	if err := row.Scan(&resolvedDMThreadID, &threadID, &peerMemberID, &humanPublicName, &peerAgentName, &peerServiceHandle); err != nil {
		if coredb.IsNotFound(err) {
			return ThreadAccess{}, ErrNotFound
		}
		return ThreadAccess{}, fmt.Errorf("resolve dm thread by id: %w", err)
	}
	channelName := peerMemberID
	if trimmedHumanPublicName := strings.TrimSpace(humanPublicName); trimmedHumanPublicName != "" {
		channelName = trimmedHumanPublicName
	} else if strings.TrimSpace(peerAgentName) != "" {
		channelName = peerAgentName
	} else if strings.TrimSpace(peerServiceHandle) != "" {
		channelName = peerServiceHandle
	}
	return ThreadAccess{
		ThreadID:     threadID,
		ThreadType:   domain.ThreadTypeDM,
		ChannelID:    resolvedDMThreadID,
		DMThreadID:   resolvedDMThreadID,
		ChannelName:  channelName,
		PeerMemberID: peerMemberID,
	}, nil
}

// ThreadIDForChannelAccess resolves one app-compatible channel id into the underlying thread id.
func ThreadIDForChannelAccess(ctx context.Context, queryer coredb.QueryRower, memberID string, workspaceID string, channelID string) (string, error) {
	channel, err := lookupChannel(ctx, queryer, workspaceID, channelID)
	if err != nil && !errors.Is(err, ErrNotFound) {
		return "", err
	}
	if err == nil {
		switch channel.Type {
		case "channel":
			joined, err := access.IsPublicMemberInChannel(ctx, queryer, memberID, channelID)
			if err != nil {
				return "", err
			}
			if !joined {
				return "", ErrForbidden
			}
			return channel.ThreadID, nil
		case "thread":
			shape, err := RequireMemberReplyThreadRead(ctx, queryer, memberID, workspaceID, channel.ChannelID)
			if err != nil {
				return "", err
			}
			return shape.ThreadID, nil
		default:
			return "", fmt.Errorf("invalid channel type %q", channel.Type)
		}
	}

	target, err := ResolveDMThreadByIDForMember(ctx, queryer, memberID, workspaceID, channelID)
	if err != nil {
		return "", err
	}
	return target.ThreadID, nil
}

// ResolveReplyThreadParentForMember resolves the parent channel or DM backing one reply-thread request.
func ResolveReplyThreadParentForMember(ctx context.Context, queryer coredb.QueryRower, memberID string, workspaceID string, channelID string) (ReplyThreadParentAccess, error) {
	channel, err := lookupChannel(ctx, queryer, workspaceID, channelID)
	if err == nil {
		if channel.Type != "channel" {
			return ReplyThreadParentAccess{}, invalidArgument("threads cannot be nested")
		}
		joined, err := access.IsPublicMemberInChannel(ctx, queryer, memberID, channelID)
		if err != nil {
			return ReplyThreadParentAccess{}, err
		}
		if err := requireReplyThreadParentChannelAccess(channel, joined); err != nil {
			return ReplyThreadParentAccess{}, err
		}
		return ReplyThreadParentAccess{
			ThreadID:          channel.ThreadID,
			ThreadType:        domain.ThreadTypeChannel,
			ParentChannelID:   channel.ChannelID,
			ParentChannelName: channel.Slug,
			ParentSubsystem:   channel.Subsystem,
		}, nil
	}
	if !errors.Is(err, ErrNotFound) {
		return ReplyThreadParentAccess{}, err
	}

	target, err := ResolveDMThreadByIDForMember(ctx, queryer, memberID, workspaceID, channelID)
	if err != nil {
		return ReplyThreadParentAccess{}, err
	}
	return ReplyThreadParentAccess{
		ThreadID:   target.ThreadID,
		ThreadType: domain.ThreadTypeDM,
	}, nil
}

// LoadReplyThreadShape resolves a reply-thread channel id into parent topology
// without applying viewer authorization.
func LoadReplyThreadShape(ctx context.Context, queryer coredb.QueryRower, workspaceID string, threadChannelID string) (ReplyThreadShape, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedThreadChannelID := strings.TrimSpace(threadChannelID)
	channel, err := lookupChannel(ctx, queryer, trimmedWorkspaceID, trimmedThreadChannelID)
	if err != nil {
		return ReplyThreadShape{}, err
	}
	if channel.Type != "thread" {
		return ReplyThreadShape{}, invalidArgument("channel is not a thread")
	}

	row := queryer.QueryRow(ctx, `
SELECT
    parent_scope.type,
    parent_scope.id,
    COALESCE(parent_profile.slug, ''),
    COALESCE(thread_scope.root_message_id, ''),
    COALESCE(thread_scope.root_message_short_id, '')
FROM scopes thread_scope
JOIN scopes parent_scope
  ON parent_scope.id = thread_scope.parent_scope_id
 AND parent_scope.workspace_id = thread_scope.workspace_id
 AND parent_scope.status = 'active'
LEFT JOIN scope_channel_profiles parent_profile
  ON parent_profile.workspace_id = parent_scope.workspace_id
 AND parent_profile.scope_id = parent_scope.id
WHERE thread_scope.workspace_id = $1
  AND thread_scope.id = $2
  AND thread_scope.type = 'thread'
  AND thread_scope.status = 'active'
`, trimmedWorkspaceID, trimmedThreadChannelID)

	var parentThreadType string
	var parentScopeID string
	var parentChannelName string
	var parentMessageID string
	var parentMessageShortID string
	if err := row.Scan(&parentThreadType, &parentScopeID, &parentChannelName, &parentMessageID, &parentMessageShortID); err != nil {
		if coredb.IsNotFound(err) {
			return ReplyThreadShape{}, ErrNotFound
		}
		return ReplyThreadShape{}, fmt.Errorf("load reply thread shape: %w", err)
	}

	shape := ReplyThreadShape{
		ThreadID:           channel.ThreadID,
		ParentScopeID:      strings.TrimSpace(parentScopeID),
		ParentMessageID:    parentMessageID,
		ParentMessageShort: parentMessageShortID,
	}
	switch parentThreadType {
	case string(domain.ThreadTypeChannel):
		shape.ParentThreadType = domain.ThreadTypeChannel
		shape.ParentChannelID = strings.TrimSpace(parentScopeID)
		shape.ParentChannelName = strings.TrimSpace(parentChannelName)
	case string(domain.ThreadTypeDM):
		shape.ParentThreadType = domain.ThreadTypeDM
	default:
		return ReplyThreadShape{}, fmt.Errorf("invalid parent thread type %q", parentThreadType)
	}

	return shape, nil
}

// RequireMemberReplyThreadRead resolves reply-thread topology after applying
// the generic member-level thread read gate. Operation-specific callers may
// still add stricter checks after this helper.
func RequireMemberReplyThreadRead(ctx context.Context, queryer coredb.QueryRower, memberID string, workspaceID string, threadChannelID string) (ReplyThreadShape, error) {
	trimmedMemberID := strings.TrimSpace(memberID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedThreadChannelID := strings.TrimSpace(threadChannelID)
	shape, err := LoadReplyThreadShape(ctx, queryer, trimmedWorkspaceID, trimmedThreadChannelID)
	if err != nil {
		return ReplyThreadShape{}, err
	}

	switch shape.ParentThreadType {
	case domain.ThreadTypeChannel:
		parentChannel, err := lookupChannel(ctx, queryer, trimmedWorkspaceID, shape.ParentChannelID)
		if err != nil {
			return ReplyThreadShape{}, err
		}
		parentJoined, err := access.IsPublicMemberInChannel(ctx, queryer, trimmedMemberID, shape.ParentChannelID)
		if err != nil {
			return ReplyThreadShape{}, err
		}
		if err := requireReplyThreadParentChannelAccess(parentChannel, parentJoined); err != nil {
			return ReplyThreadShape{}, err
		}
		shape.ParentChannelName = parentChannel.Slug
	case domain.ThreadTypeDM:
		childJoined, err := isActiveMemberInScopeForMessage(ctx, queryer, trimmedMemberID, trimmedThreadChannelID)
		if err != nil {
			return ReplyThreadShape{}, err
		}
		peerMemberID, parentParticipant, err := loadDMPeerFromScopeForMessage(ctx, queryer, trimmedWorkspaceID, trimmedMemberID, shape.ParentScopeID)
		if err != nil {
			return ReplyThreadShape{}, err
		}
		if !childJoined && !parentParticipant {
			return ReplyThreadShape{}, ErrForbidden
		}
		shape.PeerMemberID = peerMemberID
	default:
		return ReplyThreadShape{}, fmt.Errorf("invalid parent thread type %q", shape.ParentThreadType)
	}

	return shape, nil
}

func isActiveMemberInScopeForMessage(ctx context.Context, queryer coredb.QueryRower, memberID string, scopeID string) (bool, error) {
	trimmedMemberID := strings.TrimSpace(memberID)
	trimmedScopeID := strings.TrimSpace(scopeID)
	if trimmedMemberID == "" || trimmedScopeID == "" {
		return false, nil
	}
	row := queryer.QueryRow(ctx, `
SELECT 1
FROM scope_memberships
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND archived_at IS NULL
  AND effective_to_seq IS NULL
LIMIT 1
`, trimmedMemberID, trimmedScopeID)
	var marker int
	if err := row.Scan(&marker); err != nil {
		if coredb.IsNotFound(err) {
			return false, nil
		}
		return false, fmt.Errorf("load active scope membership: %w", err)
	}
	return true, nil
}

func loadDMPeerFromScopeForMessage(ctx context.Context, queryer coredb.QueryRower, workspaceID string, memberID string, dmScopeID string) (string, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT
    COALESCE(self_sm.member_id, ''),
    COALESCE(peer_sm.member_id, '')
FROM scopes dm_scope
LEFT JOIN scope_memberships self_sm
  ON self_sm.scope_id = dm_scope.id
 AND self_sm.member_id = $2
 AND self_sm.removed_at IS NULL
 AND self_sm.archived_at IS NULL
 AND self_sm.effective_to_seq IS NULL
LEFT JOIN scope_memberships peer_sm
  ON peer_sm.scope_id = dm_scope.id
 AND peer_sm.member_id <> $2
 AND peer_sm.removed_at IS NULL
 AND peer_sm.archived_at IS NULL
 AND peer_sm.effective_to_seq IS NULL
WHERE dm_scope.workspace_id = $1
  AND dm_scope.id = $3
  AND dm_scope.type = 'dm'
  AND dm_scope.status = 'active'
ORDER BY peer_sm.member_id ASC
LIMIT 1
`, strings.TrimSpace(workspaceID), strings.TrimSpace(memberID), strings.TrimSpace(dmScopeID))

	var selfMemberID string
	var peerMemberID string
	if err := row.Scan(&selfMemberID, &peerMemberID); err != nil {
		if coredb.IsNotFound(err) {
			return "", false, ErrNotFound
		}
		return "", false, fmt.Errorf("load dm scope peer: %w", err)
	}
	return strings.TrimSpace(peerMemberID), strings.TrimSpace(selfMemberID) == strings.TrimSpace(memberID), nil
}

func requireReplyThreadParentChannelAccess(channel access.Channel, joined bool) error {
	if channel.Slug != "all" {
		if channel.OwnerMemberID == nil {
			return ErrNotFound
		}
		if channel.Visibility == "private" && !joined {
			return ErrNotFound
		}
	}
	if !joined {
		return ErrForbidden
	}
	return nil
}

// OpenReplyThreadForMember creates or loads a reply-thread channel for one accessible parent message.
func OpenReplyThreadForMember(ctx context.Context, queryer coredb.QueryExecer, memberID string, workspaceID string, channelID string, parentMessageID string) (ReplyThreadSummary, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedParentMessageID := strings.TrimSpace(parentMessageID)
	if trimmedChannelID == "" {
		return ReplyThreadSummary{}, invalidArgument("channelId is required")
	}
	if trimmedParentMessageID == "" {
		return ReplyThreadSummary{}, invalidArgument("parentMessageId is required")
	}

	parent, err := ResolveReplyThreadParentForMember(ctx, queryer, strings.TrimSpace(memberID), trimmedWorkspaceID, trimmedChannelID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}
	if parent.ThreadType == domain.ThreadTypeChannel && parent.ParentChannelName == "" {
		return ReplyThreadSummary{}, fmt.Errorf("invalid parent channel slug")
	}
	if parent.ThreadType == domain.ThreadTypeChannel && parent.ParentChannelID == "" {
		return ReplyThreadSummary{}, fmt.Errorf("invalid parent channel id")
	}

	shortID, err := threadShortIDFromMessageID(trimmedParentMessageID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}

	parentScopeID := replyThreadParentScopeID(parent, trimmedChannelID)
	row := queryer.QueryRow(ctx, `
SELECT 1
FROM scope_messages
WHERE scope_id = $2
  AND id = $1
`, trimmedParentMessageID, parentScopeID)
	var marker int
	if err := row.Scan(&marker); err != nil {
		if coredb.IsNotFound(err) {
			return ReplyThreadSummary{}, ErrNotFound
		}
		return ReplyThreadSummary{}, fmt.Errorf("verify parent message: %w", err)
	}

	_, summary, err := loadReplyThreadSummaryByParentMessageID(ctx, queryer, trimmedWorkspaceID, parentScopeID, trimmedParentMessageID)
	if err == nil {
		return summary, nil
	}
	if !errors.Is(err, ErrNotFound) {
		return ReplyThreadSummary{}, err
	}

	if _, err := createReplyThread(ctx, queryer, trimmedWorkspaceID, parentScopeID, trimmedParentMessageID, shortID); err != nil {
		return ReplyThreadSummary{}, err
	}

	_, summary, err = loadReplyThreadSummaryByParentMessageID(ctx, queryer, trimmedWorkspaceID, parentScopeID, trimmedParentMessageID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}
	return summary, nil
}

// OpenReplyThreadForReservedParentMessage creates or loads a reply-thread
// scope for a message id that the caller has reserved but has not written yet.
// It is used by start-agent-thread sends where the first message lives in the
// newly created thread scope and is also that thread's root message.
func OpenReplyThreadForReservedParentMessage(ctx context.Context, queryer coredb.QueryExecer, memberID string, workspaceID string, channelID string, parentMessageID string) (ReplyThreadSummary, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedParentMessageID := strings.TrimSpace(parentMessageID)
	if trimmedChannelID == "" {
		return ReplyThreadSummary{}, invalidArgument("channelId is required")
	}
	if trimmedParentMessageID == "" {
		return ReplyThreadSummary{}, invalidArgument("parentMessageId is required")
	}

	parent, err := ResolveReplyThreadParentForMember(ctx, queryer, strings.TrimSpace(memberID), trimmedWorkspaceID, trimmedChannelID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}
	if parent.ThreadType == domain.ThreadTypeChannel && parent.ParentChannelName == "" {
		return ReplyThreadSummary{}, fmt.Errorf("invalid parent channel slug")
	}
	if parent.ThreadType == domain.ThreadTypeChannel && parent.ParentChannelID == "" {
		return ReplyThreadSummary{}, fmt.Errorf("invalid parent channel id")
	}

	shortID, err := threadShortIDFromMessageID(trimmedParentMessageID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}
	parentScopeID := replyThreadParentScopeID(parent, trimmedChannelID)
	_, summary, err := loadReplyThreadSummaryByParentMessageID(ctx, queryer, trimmedWorkspaceID, parentScopeID, trimmedParentMessageID)
	if err == nil {
		return summary, nil
	}
	if !errors.Is(err, ErrNotFound) {
		return ReplyThreadSummary{}, err
	}

	if _, err := createReplyThread(ctx, queryer, trimmedWorkspaceID, parentScopeID, trimmedParentMessageID, shortID); err != nil {
		return ReplyThreadSummary{}, err
	}
	_, summary, err = loadReplyThreadSummaryByParentMessageID(ctx, queryer, trimmedWorkspaceID, parentScopeID, trimmedParentMessageID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}
	return summary, nil
}

// LoadReplyThreadSummaryForParentMessage loads an existing reply-thread summary
// for one accessible parent message without creating a reply thread.
func LoadReplyThreadSummaryForParentMessage(ctx context.Context, queryer coredb.Queryer, memberID string, workspaceID string, channelID string, parentMessageID string) (ReplyThreadSummary, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedParentMessageID := strings.TrimSpace(parentMessageID)
	if trimmedChannelID == "" {
		return ReplyThreadSummary{}, invalidArgument("channelId is required")
	}
	if trimmedParentMessageID == "" {
		return ReplyThreadSummary{}, invalidArgument("parentMessageId is required")
	}

	parent, err := ResolveReplyThreadParentForMember(ctx, queryer, strings.TrimSpace(memberID), trimmedWorkspaceID, trimmedChannelID)
	if err != nil {
		return ReplyThreadSummary{}, err
	}
	if parent.ThreadType == domain.ThreadTypeChannel && parent.ParentChannelName == "" {
		return ReplyThreadSummary{}, fmt.Errorf("invalid parent channel slug")
	}
	if parent.ThreadType == domain.ThreadTypeChannel && parent.ParentChannelID == "" {
		return ReplyThreadSummary{}, fmt.Errorf("invalid parent channel id")
	}
	_, summary, err := loadReplyThreadSummaryByParentMessageID(ctx, queryer, trimmedWorkspaceID, replyThreadParentScopeID(parent, trimmedChannelID), trimmedParentMessageID)
	return summary, err
}

// ListReplyThreadSummariesForMember lists the reply-thread summaries visible from one parent channel or DM.
func ListReplyThreadSummariesForMember(ctx context.Context, queryer coredb.Queryer, memberID string, workspaceID string, channelID string) (map[string]ReplyThreadSummary, error) {
	return ListReplyThreadSummariesForMemberWithLimit(ctx, queryer, memberID, workspaceID, channelID, 0)
}

func ListReplyThreadSummariesForMemberWithLimit(ctx context.Context, queryer coredb.Queryer, memberID string, workspaceID string, channelID string, limit int) (map[string]ReplyThreadSummary, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedMemberID := strings.TrimSpace(memberID)
	trimmedChannelID := strings.TrimSpace(channelID)
	if trimmedChannelID == "" {
		return nil, invalidArgument("channelId is required")
	}

	parent, err := ResolveReplyThreadParentForMember(ctx, queryer, trimmedMemberID, trimmedWorkspaceID, trimmedChannelID)
	if err != nil {
		return nil, err
	}

	parentScopeID := replyThreadParentScopeID(parent, trimmedChannelID)
	if parentScopeID == "" {
		return nil, fmt.Errorf("reply-thread parent scope is missing")
	}
	parentSubsystem := strings.TrimSpace(parent.ParentSubsystem)
	if parent.ThreadType == domain.ThreadTypeChannel && parentSubsystem == "" {
		parentSubsystem = ChannelSubsystemIM
	}

	query := `
SELECT child.root_message_id, child.id, COALESCE(root.content, ''), COALESCE(root.metadata, '{}'), root.created_at,
       COALESCE(viewer_subscription.state, '') AS subscription_state,
       child.visible_message_count, child.visible_last_message_at,
       COALESCE((
         SELECT sm.content FROM scope_messages sm
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_preview,
       COALESCE((
         SELECT m.type FROM scope_messages sm
         JOIN members m ON m.id = sm.author_member_id AND m.workspace_id = sm.workspace_id
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_author_type,
       COALESCE((
         SELECT CASE
           WHEN m.type = 'human' THEN COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), NULLIF(u.name, ''), '')
           WHEN m.type = 'agent' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), '')
           ELSE ''
         END
         FROM scope_messages sm
         JOIN members m ON m.id = sm.author_member_id AND m.workspace_id = sm.workspace_id
         LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
         LEFT JOIN agents a ON m.type = 'agent' AND a.workspace_id = m.workspace_id AND a.member_id = m.id
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_author,
       COALESCE((
         SELECT CASE
           WHEN m.type = 'human' THEN COALESCE(u.id, '')
           WHEN m.type = 'agent' THEN COALESCE(a.id, '')
           ELSE ''
         END
         FROM scope_messages sm
         JOIN members m ON m.id = sm.author_member_id AND m.workspace_id = sm.workspace_id
         LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
         LEFT JOIN agents a ON m.type = 'agent' AND a.workspace_id = m.workspace_id AND a.member_id = m.id
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_author_id
FROM scopes AS child
JOIN scope_channel_profiles AS parent_cp
  ON parent_cp.scope_id = child.parent_scope_id
 AND parent_cp.workspace_id = child.workspace_id
 AND parent_cp.subsystem = $3
JOIN scope_messages AS root
  ON root.workspace_id = child.workspace_id
 AND root.scope_id = child.parent_scope_id
 AND root.id = child.root_message_id
LEFT JOIN scope_notification_settings AS viewer_subscription
  ON viewer_subscription.scope_id = child.id
 AND viewer_subscription.member_id = $4
 AND viewer_subscription.state IN ('active', 'quiet', 'mute')
WHERE child.workspace_id = $1
  AND child.parent_scope_id = $2
  AND child.type = 'thread'
  AND child.status = 'active'
  AND child.archived_at IS NULL
ORDER BY child.root_message_id ASC`
	args := []any{trimmedWorkspaceID, parentScopeID, parentSubsystem, trimmedMemberID}
	if limit > 0 {
		query = `
WITH recent_parent_messages AS (
    SELECT id
    FROM scope_messages
    WHERE workspace_id = $1
      AND scope_id = $2
    ORDER BY seq DESC
    LIMIT $5
)
SELECT child.root_message_id, child.id, COALESCE(root.content, ''), COALESCE(root.metadata, '{}'), root.created_at,
       COALESCE(viewer_subscription.state, '') AS subscription_state,
       child.visible_message_count, child.visible_last_message_at,
       COALESCE((
         SELECT sm.content FROM scope_messages sm
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_preview,
       COALESCE((
         SELECT m.type FROM scope_messages sm
         JOIN members m ON m.id = sm.author_member_id AND m.workspace_id = sm.workspace_id
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_author_type,
       COALESCE((
         SELECT CASE
           WHEN m.type = 'human' THEN COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), NULLIF(u.name, ''), '')
           WHEN m.type = 'agent' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), '')
           ELSE ''
         END
         FROM scope_messages sm
         JOIN members m ON m.id = sm.author_member_id AND m.workspace_id = sm.workspace_id
         LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
         LEFT JOIN agents a ON m.type = 'agent' AND a.workspace_id = m.workspace_id AND a.member_id = m.id
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_author,
       COALESCE((
         SELECT CASE
           WHEN m.type = 'human' THEN COALESCE(u.id, '')
           WHEN m.type = 'agent' THEN COALESCE(a.id, '')
           ELSE ''
         END
         FROM scope_messages sm
         JOIN members m ON m.id = sm.author_member_id AND m.workspace_id = sm.workspace_id
         LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
         LEFT JOIN agents a ON m.type = 'agent' AND a.workspace_id = m.workspace_id AND a.member_id = m.id
         WHERE sm.workspace_id = child.workspace_id AND sm.scope_id = child.id
         ORDER BY sm.seq DESC LIMIT 1
       ), '') AS last_reply_author_id
FROM recent_parent_messages rpm
JOIN scopes AS child
  ON child.workspace_id = $1
 AND child.parent_scope_id = $2
 AND child.type = 'thread'
 AND child.status = 'active'
 AND child.archived_at IS NULL
 AND child.root_message_id = rpm.id
JOIN scope_channel_profiles AS parent_cp
  ON parent_cp.scope_id = child.parent_scope_id
 AND parent_cp.workspace_id = child.workspace_id
 AND parent_cp.subsystem = $3
JOIN scope_messages AS root
  ON root.workspace_id = child.workspace_id
 AND root.scope_id = child.parent_scope_id
 AND root.id = child.root_message_id
LEFT JOIN scope_notification_settings AS viewer_subscription
  ON viewer_subscription.scope_id = child.id
 AND viewer_subscription.member_id = $4
 AND viewer_subscription.state IN ('active', 'quiet', 'mute')
ORDER BY child.root_message_id DESC`
		args = append(args, limit)
	}

	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list reply threads: %w", err)
	}
	defer rows.Close()

	summaries := make(map[string]ReplyThreadSummary)
	threadChannelIDs := make([]string, 0)
	for rows.Next() {
		var parentMessageID string
		var threadChannelID string
		var replyCount int64
		var lastReplyAt *time.Time
		var parentMessagePreview string
		var rawParentMessageMetadata []byte
		var parentMessageCreatedAt time.Time
		var subscriptionState string
		var lastReplyPreview string
		var lastReplyAuthor string
		var lastReplyAuthorID string
		var lastReplyAuthorType string
		if err := rows.Scan(&parentMessageID, &threadChannelID, &parentMessagePreview, &rawParentMessageMetadata, &parentMessageCreatedAt, &subscriptionState, &replyCount, &lastReplyAt, &lastReplyPreview, &lastReplyAuthorType, &lastReplyAuthor, &lastReplyAuthorID); err != nil {
			return nil, fmt.Errorf("scan reply thread summary: %w", err)
		}
		parentMessageMetadata, err := DecodeMessageMetadata(rawParentMessageMetadata)
		if err != nil {
			return nil, err
		}
		createdAt := parentMessageCreatedAt
		summaries[parentMessageID] = ReplyThreadSummary{
			ThreadChannelID:        threadChannelID,
			RootMessageID:          parentMessageID,
			ParentMessagePreview:   parentMessagePreview,
			ParentMessageMetadata:  parentMessageMetadata,
			ParentMessageCreatedAt: &createdAt,
			SubscriptionState:      subscriptionState,
			ReplyCount:             replyCount,
			LastReplyAt:            lastReplyAt,
			LastReplyPreview:       lastReplyPreview,
			LastReplyAuthor:        lastReplyAuthor,
			LastReplyAuthorID:      lastReplyAuthorID,
			LastReplyAuthorType:    lastReplyAuthorType,
		}
		threadChannelIDs = append(threadChannelIDs, threadChannelID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate reply thread summaries: %w", err)
	}

	participantMap, authorMap, err := loadReplyThreadParticipantAndAuthorDetailsMaps(ctx, queryer, threadChannelIDs)
	if err != nil {
		return nil, err
	}
	for parentMessageID, summary := range summaries {
		summary.Participants = participantMap[summary.ThreadChannelID]
		summary.ParticipantIDs = replyThreadParticipantIDsFromDetails(summary.Participants)
		summary.ReplyAuthors = authorMap[summary.ThreadChannelID]
		summary.ReplyAuthorIDs = ReplyThreadRecentAuthorIDsFromDetails(summary.ReplyAuthors)
		summaries[parentMessageID] = summary
	}

	return summaries, nil
}

// ListReplyThreadSummariesForParentMessageIDs loads summaries for a bounded
// page of already-authorized parent messages.
func ListReplyThreadSummariesForParentMessageIDs(ctx context.Context, queryer coredb.Queryer, workspaceID string, parentThreadID string, parentMessageIDs []string) (map[string]ReplyThreadSummary, error) {
	normalizedParentMessageIDs := normalizeNonEmptyStrings(parentMessageIDs)
	if len(normalizedParentMessageIDs) == 0 {
		return map[string]ReplyThreadSummary{}, nil
	}

	requestedParentMessagesCTE, requestedParentMessageArgs, _, err := coredb.BuildStringValuesCTE("requested_parent_messages", "parent_message_id", normalizedParentMessageIDs, 3)
	if err != nil {
		return nil, fmt.Errorf("build reply thread summary page query: %w", err)
	}

	args := make([]any, 0, 2+len(requestedParentMessageArgs))
	args = append(args, strings.TrimSpace(workspaceID), strings.TrimSpace(parentThreadID))
	args = append(args, requestedParentMessageArgs...)

	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
parent_scope AS (
    SELECT id
    FROM scopes
    WHERE workspace_id = $1
      AND id = $2
      AND status = 'active'
      AND archived_at IS NULL
    LIMIT 1
)
SELECT child.root_message_id, child.id, COALESCE(root.content, ''), COALESCE(root.metadata, '{}'), root.created_at, child.visible_message_count, child.visible_last_message_at
FROM requested_parent_messages AS requested
JOIN parent_scope
JOIN scopes AS child
  ON child.workspace_id = $1
 AND child.parent_scope_id = parent_scope.id
 AND child.type = 'thread'
 AND child.status = 'active'
 AND child.archived_at IS NULL
 AND child.root_message_id = requested.parent_message_id
JOIN scope_messages AS root
  ON root.workspace_id = child.workspace_id
 AND root.scope_id = child.parent_scope_id
 AND root.id = child.root_message_id
ORDER BY child.root_message_id ASC
`, requestedParentMessagesCTE), args...)
	if err != nil {
		return nil, fmt.Errorf("list reply thread summaries for parent messages: %w", err)
	}
	defer rows.Close()

	summaries := make(map[string]ReplyThreadSummary)
	threadChannelIDs := make([]string, 0)
	for rows.Next() {
		var parentMessageID string
		var threadChannelID string
		var replyCount int64
		var lastReplyAt *time.Time
		var parentMessagePreview string
		var rawParentMessageMetadata []byte
		var parentMessageCreatedAt time.Time
		if err := rows.Scan(&parentMessageID, &threadChannelID, &parentMessagePreview, &rawParentMessageMetadata, &parentMessageCreatedAt, &replyCount, &lastReplyAt); err != nil {
			return nil, fmt.Errorf("scan reply thread summary for parent message: %w", err)
		}
		parentMessageMetadata, err := DecodeMessageMetadata(rawParentMessageMetadata)
		if err != nil {
			return nil, err
		}
		createdAt := parentMessageCreatedAt
		summaries[parentMessageID] = ReplyThreadSummary{
			ThreadChannelID:        threadChannelID,
			RootMessageID:          parentMessageID,
			ParentMessagePreview:   parentMessagePreview,
			ParentMessageMetadata:  parentMessageMetadata,
			ParentMessageCreatedAt: &createdAt,
			ReplyCount:             replyCount,
			LastReplyAt:            lastReplyAt,
		}
		threadChannelIDs = append(threadChannelIDs, threadChannelID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate reply thread summaries for parent messages: %w", err)
	}

	participantMap, authorMap, err := loadReplyThreadParticipantAndAuthorDetailsMaps(ctx, queryer, threadChannelIDs)
	if err != nil {
		return nil, err
	}
	for parentMessageID, summary := range summaries {
		summary.Participants = participantMap[summary.ThreadChannelID]
		summary.ParticipantIDs = replyThreadParticipantIDsFromDetails(summary.Participants)
		summary.ReplyAuthors = authorMap[summary.ThreadChannelID]
		summary.ReplyAuthorIDs = ReplyThreadRecentAuthorIDsFromDetails(summary.ReplyAuthors)
		summaries[parentMessageID] = summary
	}

	return summaries, nil
}

// ParseReplyThreadParticipantIDs normalizes the stored participant id list.
func ParseReplyThreadParticipantIDs(raw []byte) ([]string, error) {
	if len(raw) == 0 {
		return []string{}, nil
	}

	var participantIDs []string
	if err := model.UnmarshalJSONObject("reply thread participant ids", raw, &participantIDs); err != nil {
		return nil, fmt.Errorf("parse reply thread participant ids: %w", err)
	}
	return NormalizeReplyThreadParticipantIDs(participantIDs), nil
}

func normalizeReplyThreadChannelIDs(threadChannelIDs []string) []string {
	normalized := make([]string, 0, len(threadChannelIDs))
	seen := make(map[string]struct{}, len(threadChannelIDs))
	for _, threadChannelID := range threadChannelIDs {
		trimmedThreadChannelID := strings.TrimSpace(threadChannelID)
		if trimmedThreadChannelID == "" {
			continue
		}
		if _, exists := seen[trimmedThreadChannelID]; exists {
			continue
		}
		seen[trimmedThreadChannelID] = struct{}{}
		normalized = append(normalized, trimmedThreadChannelID)
	}
	return normalized
}

func replyThreadParticipantIDsFromDetails(participants []ReplyThreadParticipant) []string {
	if len(participants) == 0 {
		return []string{}
	}
	ids := make([]string, 0, len(participants))
	for _, participant := range participants {
		if id := strings.TrimSpace(participant.ID); id != "" {
			ids = append(ids, id)
		}
	}
	return NormalizeReplyThreadParticipantIDs(ids)
}

func loadReplyThreadParticipantMap(ctx context.Context, queryer coredb.Queryer, threadChannelIDs []string) (map[string][]string, error) {
	normalizedThreadChannelIDs := normalizeReplyThreadChannelIDs(threadChannelIDs)
	if len(normalizedThreadChannelIDs) == 0 {
		return map[string][]string{}, nil
	}

	requestedThreadIDsCTE, requestedThreadIDArgs, _, err := coredb.BuildStringValuesCTE("requested_thread_channel_ids", "thread_channel_id", normalizedThreadChannelIDs, 1)
	if err != nil {
		return nil, fmt.Errorf("build reply thread participant query: %w", err)
	}

	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT
    requested.thread_channel_id,
    CASE
        WHEN m.type = 'human' THEN COALESCE(m.user_id, '')
        WHEN m.type = 'agent' THEN COALESCE(a.id, '')
        ELSE ''
    END AS participant_id
FROM requested_thread_channel_ids AS requested
LEFT JOIN scope_memberships sp
  ON sp.scope_id = requested.thread_channel_id
 AND sp.removed_at IS NULL
 AND sp.archived_at IS NULL
 AND sp.effective_to_seq IS NULL
LEFT JOIN members m
  ON m.id = sp.member_id
LEFT JOIN agents a
  ON m.type = 'agent'
 AND a.member_id = sp.member_id
ORDER BY requested.thread_channel_id ASC, participant_id ASC
`, requestedThreadIDsCTE), requestedThreadIDArgs...)
	if err != nil {
		return nil, fmt.Errorf("load reply thread participants: %w", err)
	}
	defer rows.Close()

	participantMap := make(map[string][]string, len(normalizedThreadChannelIDs))
	for _, threadChannelID := range normalizedThreadChannelIDs {
		participantMap[threadChannelID] = []string{}
	}

	for rows.Next() {
		var threadChannelID string
		var participantID *string
		if err := rows.Scan(&threadChannelID, &participantID); err != nil {
			return nil, fmt.Errorf("scan reply thread participant: %w", err)
		}
		if participantID == nil {
			continue
		}
		trimmedParticipantID := strings.TrimSpace(*participantID)
		if trimmedParticipantID == "" {
			continue
		}
		participantMap[threadChannelID] = append(participantMap[threadChannelID], trimmedParticipantID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate reply thread participants: %w", err)
	}
	for threadChannelID, participantIDs := range participantMap {
		participantMap[threadChannelID] = NormalizeReplyThreadParticipantIDs(participantIDs)
	}
	return participantMap, nil
}

func loadReplyThreadSubscriberParticipantMap(ctx context.Context, queryer coredb.Queryer, threadChannelIDs []string) (map[string][]string, error) {
	normalizedThreadChannelIDs := normalizeReplyThreadChannelIDs(threadChannelIDs)
	if len(normalizedThreadChannelIDs) == 0 {
		return map[string][]string{}, nil
	}

	participantDetailsMap, _, err := loadReplyThreadParticipantAndAuthorDetailsMaps(ctx, queryer, normalizedThreadChannelIDs)
	if err != nil {
		return nil, err
	}
	participantMap := make(map[string][]string, len(normalizedThreadChannelIDs))
	for _, threadChannelID := range normalizedThreadChannelIDs {
		participantMap[threadChannelID] = replyThreadParticipantIDsFromDetails(participantDetailsMap[threadChannelID])
	}
	return participantMap, nil
}

func loadReplyThreadParticipantDetailsMap(ctx context.Context, queryer coredb.Queryer, threadChannelIDs []string) (map[string][]ReplyThreadParticipant, error) {
	participantMap, _, err := loadReplyThreadParticipantAndAuthorDetailsMaps(ctx, queryer, threadChannelIDs)
	return participantMap, err
}

func loadReplyThreadParticipantAndAuthorDetailsMaps(
	ctx context.Context,
	queryer coredb.Queryer,
	threadChannelIDs []string,
) (map[string][]ReplyThreadParticipant, map[string][]ReplyThreadParticipant, error) {
	normalizedThreadChannelIDs := normalizeReplyThreadChannelIDs(threadChannelIDs)
	if len(normalizedThreadChannelIDs) == 0 {
		return map[string][]ReplyThreadParticipant{}, map[string][]ReplyThreadParticipant{}, nil
	}

	requestedThreadIDsCTE, requestedThreadIDArgs, _, err := coredb.BuildStringValuesCTE("requested_thread_channel_ids", "thread_channel_id", normalizedThreadChannelIDs, 1)
	if err != nil {
		return nil, nil, fmt.Errorf("build reply thread participant query: %w", err)
	}

	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s,
recent_author_slots AS (
    SELECT 0 AS author_ordinality
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
),
requested_rows AS (
    SELECT
        requested.thread_channel_id,
        'participant' AS row_kind,
        0 AS row_ordinality,
        sp.member_id,
        COALESCE(sp.owner_member_id, '') AS owner_member_id,
        COALESCE(sp.visibility, '') AS visibility
    FROM requested_thread_channel_ids AS requested
    JOIN scope_memberships sp
      ON sp.scope_id = requested.thread_channel_id
     AND sp.removed_at IS NULL
     AND sp.archived_at IS NULL
     AND sp.effective_to_seq IS NULL
    JOIN scope_notification_settings sns
      ON sns.scope_id = sp.scope_id
     AND sns.member_id = sp.member_id
    JOIN members participant_member
      ON participant_member.id = sp.member_id
     AND (
         sns.state IN ('active', 'quiet', 'mute')
         OR (participant_member.type = 'agent' AND sns.state IN ('auto-wake', 'no-wake'))
     )

    UNION ALL

    SELECT
        requested.thread_channel_id,
        'author' AS row_kind,
        recent_author_slots.author_ordinality AS row_ordinality,
        JSON_UNQUOTE(JSON_EXTRACT(
            thread_scope.recent_author_member_ids,
            CONCAT('$[', recent_author_slots.author_ordinality, ']')
        )) AS member_id,
        '' AS owner_member_id,
        '' AS visibility
    FROM requested_thread_channel_ids AS requested
    JOIN scopes thread_scope
      ON thread_scope.id = requested.thread_channel_id
     AND thread_scope.type = 'thread'
     AND thread_scope.status = 'active'
     AND thread_scope.archived_at IS NULL
    JOIN recent_author_slots
      ON recent_author_slots.author_ordinality
       < JSON_LENGTH(COALESCE(thread_scope.recent_author_member_ids, JSON_ARRAY()))
)
SELECT
    requested_rows.thread_channel_id,
    requested_rows.row_kind,
    requested_rows.row_ordinality,
    CASE
        WHEN m.type = 'human' THEN COALESCE(m.user_id, '')
        WHEN m.type = 'agent' THEN COALESCE(a.id, '')
        ELSE m.id
    END AS participant_id,
    COALESCE(m.type, '') AS participant_type,
    CASE
        WHEN m.type = 'human' THEN COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), NULLIF(u.name, ''), m.id)
        WHEN m.type = 'agent' THEN COALESCE(NULLIF(m.display_name, ''), NULLIF(a.name, ''), m.id)
        ELSE COALESCE(NULLIF(m.display_name, ''), m.id)
    END AS display_name,
    CASE
        WHEN m.type = 'human' THEN COALESCE(u.avatar_url, '')
        WHEN m.type = 'agent' THEN COALESCE(a.avatar_url, '')
        ELSE ''
    END AS avatar_url,
    m.id AS member_id,
    CASE WHEN m.type = 'agent' AND requested_rows.row_kind = 'participant'
         THEN COALESCE(NULLIF(requested_rows.owner_member_id, ''), owner_member.id, '')
         ELSE ''
    END AS owner_member_id,
    CASE WHEN m.type = 'agent' AND requested_rows.row_kind = 'participant'
         THEN COALESCE(owner_user.id, '')
         ELSE ''
    END AS owner_user_id,
    CASE WHEN requested_rows.row_kind = 'participant' THEN requested_rows.visibility ELSE '' END AS visibility
FROM requested_rows
JOIN members m
  ON m.id = requested_rows.member_id
LEFT JOIN users u
  ON m.type = 'human'
 AND u.id = m.user_id
LEFT JOIN agents a
  ON m.type = 'agent'
 AND a.workspace_id = m.workspace_id
 AND a.member_id = requested_rows.member_id
LEFT JOIN machines agent_machine
  ON m.type = 'agent'
 AND agent_machine.workspace_id = a.workspace_id
 AND agent_machine.id = a.machine_id
LEFT JOIN members owner_member
  ON m.type = 'agent'
 AND owner_member.workspace_id = a.workspace_id
 AND owner_member.type = 'human'
 AND owner_member.status = 'active'
 AND owner_member.user_id = agent_machine.created_by_user_id
LEFT JOIN users owner_user
  ON owner_user.id = COALESCE(owner_member.user_id, agent_machine.created_by_user_id)
ORDER BY requested_rows.thread_channel_id ASC,
         CASE requested_rows.row_kind WHEN 'participant' THEN 0 ELSE 1 END ASC,
         CASE WHEN requested_rows.row_kind = 'participant' THEN participant_id ELSE '' END ASC,
         requested_rows.row_ordinality ASC
`, requestedThreadIDsCTE), requestedThreadIDArgs...)
	if err != nil {
		return nil, nil, fmt.Errorf("load reply thread participant and author details: %w", err)
	}
	defer rows.Close()

	participantMap := make(map[string][]ReplyThreadParticipant, len(normalizedThreadChannelIDs))
	authorMap := make(map[string][]ReplyThreadParticipant, len(normalizedThreadChannelIDs))
	participantSeen := make(map[string]map[string]struct{}, len(normalizedThreadChannelIDs))
	authorSeen := make(map[string]map[string]struct{}, len(normalizedThreadChannelIDs))
	for _, threadChannelID := range normalizedThreadChannelIDs {
		participantMap[threadChannelID] = []ReplyThreadParticipant{}
		authorMap[threadChannelID] = []ReplyThreadParticipant{}
		participantSeen[threadChannelID] = map[string]struct{}{}
		authorSeen[threadChannelID] = map[string]struct{}{}
	}

	for rows.Next() {
		var threadChannelID string
		var rowKind string
		var rowOrdinality int
		var participant ReplyThreadParticipant
		if err := rows.Scan(
			&threadChannelID,
			&rowKind,
			&rowOrdinality,
			&participant.ID,
			&participant.Type,
			&participant.DisplayName,
			&participant.AvatarURL,
			&participant.MemberID,
			&participant.OwnerMemberID,
			&participant.OwnerUserID,
			&participant.Visibility,
		); err != nil {
			return nil, nil, fmt.Errorf("scan reply thread participant or author: %w", err)
		}
		threadChannelID = strings.TrimSpace(threadChannelID)
		rowKind = strings.TrimSpace(rowKind)
		participant.ID = strings.TrimSpace(participant.ID)
		participant.Type = strings.TrimSpace(participant.Type)
		participant.DisplayName = strings.TrimSpace(participant.DisplayName)
		participant.AvatarURL = strings.TrimSpace(participant.AvatarURL)
		participant.MemberID = strings.TrimSpace(participant.MemberID)
		participant.OwnerMemberID = strings.TrimSpace(participant.OwnerMemberID)
		participant.OwnerUserID = strings.TrimSpace(participant.OwnerUserID)
		participant.Visibility = strings.TrimSpace(participant.Visibility)
		if threadChannelID == "" || participant.ID == "" || participant.Type == "" || participant.DisplayName == "" {
			continue
		}
		switch rowKind {
		case "participant":
			if _, exists := participantSeen[threadChannelID][participant.ID]; exists {
				continue
			}
			participantSeen[threadChannelID][participant.ID] = struct{}{}
			participantMap[threadChannelID] = append(participantMap[threadChannelID], participant)
		case "author":
			if _, exists := authorSeen[threadChannelID][participant.ID]; exists {
				continue
			}
			authorSeen[threadChannelID][participant.ID] = struct{}{}
			participant.OwnerMemberID = ""
			participant.OwnerUserID = ""
			participant.Visibility = ""
			authorMap[threadChannelID] = append(authorMap[threadChannelID], participant)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, nil, fmt.Errorf("iterate reply thread participant and author details: %w", err)
	}
	return participantMap, authorMap, nil
}

func lookupChannel(ctx context.Context, queryer coredb.QueryRower, workspaceID string, channelID string) (access.Channel, error) {
	channel, err := access.LookupChannel(ctx, queryer, workspaceID, channelID)
	if errors.Is(err, access.ErrNotFound) {
		return access.Channel{}, ErrNotFound
	}
	if err != nil {
		return access.Channel{}, err
	}
	channel.ThreadID = channel.ChannelID
	return channel, nil
}

func resolveAccessibleChannel(ctx context.Context, queryer coredb.QueryRower, actor MemberActor, slug string) (channelAccess, error) {
	channel, err := access.ResolveAccessibleChannelForMember(ctx, queryer, actor.WorkspaceID, actor.MemberID, slug)
	if errors.Is(err, access.ErrForbidden) {
		return channelAccess{}, ErrForbidden
	}
	if err != nil {
		return channelAccess{}, err
	}
	return channelAccess{
		ChannelID: channel.ChannelID,
		ThreadID:  channel.ChannelID,
	}, nil
}

func resolveExistingDMThread(ctx context.Context, queryer coredb.QueryRower, actor MemberActor, peerMemberID string) (ThreadAccess, error) {
	peer, err := resolvePeerMemberReference(ctx, queryer, actor.WorkspaceID, peerMemberID)
	if err != nil {
		return ThreadAccess{}, err
	}

	memberAID, memberBID := orderedMembers(actor.MemberID, peer.MemberID)
	row := queryer.QueryRow(ctx, `
SELECT
    s.id,
    s.id
FROM scopes s
JOIN scope_memberships member_a
  ON member_a.scope_id = s.id
 AND member_a.member_id = $2
 AND member_a.removed_at IS NULL
 AND member_a.archived_at IS NULL
 AND member_a.effective_to_seq IS NULL
JOIN scope_memberships member_b
  ON member_b.scope_id = s.id
 AND member_b.member_id = $3
 AND member_b.removed_at IS NULL
 AND member_b.archived_at IS NULL
 AND member_b.effective_to_seq IS NULL
WHERE s.workspace_id = $1
  AND s.type = 'dm'
  AND s.status = 'active'
  AND s.archived_at IS NULL
ORDER BY s.created_at ASC, s.id ASC
LIMIT 1
`, actor.WorkspaceID, memberAID, memberBID)
	var dmThreadID string
	var threadID string
	if err := row.Scan(&dmThreadID, &threadID); err != nil {
		if coredb.IsNotFound(err) {
			return ThreadAccess{}, requestErrorWithCause{
				kind:    domain.ErrInvalidArgument,
				cause:   errDMDoesNotExist,
				message: errDMDoesNotExist.Error(),
			}
		}
		return ThreadAccess{}, fmt.Errorf("resolve dm thread: %w", err)
	}

	return ThreadAccess{
		ThreadID:     threadID,
		ThreadType:   domain.ThreadTypeDM,
		ChannelID:    dmThreadID,
		DMThreadID:   dmThreadID,
		ChannelName:  peer.PublicName,
		PeerMemberID: peer.MemberID,
	}, nil
}

func resolvePeerMemberReference(ctx context.Context, queryer coredb.QueryRower, workspaceID string, peerReference string) (resolvedPeerMember, error) {
	trimmedReference := strings.TrimSpace(peerReference)
	if err := domain.ValidateMemberReference(trimmedReference); err != nil {
		return resolvedPeerMember{}, err
	}

	row := queryer.QueryRow(ctx, `
SELECT
    m.id,
    CASE
        WHEN m.type = 'agent' THEN COALESCE(a.name, '')
        WHEN m.type = 'human' THEN m.id
		WHEN m.type = 'service' THEN COALESCE(sm.handle, '')
        ELSE ''
    END AS public_name
FROM members m
LEFT JOIN agents a ON a.workspace_id = m.workspace_id AND a.member_id = m.id
LEFT JOIN users u ON u.id = m.user_id
LEFT JOIN service_members sm
  ON sm.workspace_id = m.workspace_id
 AND sm.member_id = m.id
WHERE m.workspace_id = $1
  AND (
      m.id = $2
      OR (m.type = 'agent' AND LOWER(a.name) = LOWER($2))
      OR (m.type = 'human' AND LOWER(u.name) = LOWER($2))
  )
ORDER BY
    CASE
        WHEN m.id = $2 THEN 0
        WHEN m.type = 'agent' AND LOWER(a.name) = LOWER($2) THEN 1
        WHEN m.type = 'human' AND LOWER(u.name) = LOWER($2) THEN 2
        ELSE 3
    END,
    m.id ASC
LIMIT 1
`, strings.TrimSpace(workspaceID), trimmedReference)

	var resolved resolvedPeerMember
	var publicName string
	if err := row.Scan(&resolved.MemberID, &publicName); err != nil {
		if coredb.IsNotFound(err) {
			return resolvedPeerMember{}, ErrNotFound
		}
		return resolvedPeerMember{}, fmt.Errorf("load peer member reference: %w", err)
	}
	resolved.PublicName = strings.TrimSpace(publicName)
	if resolved.PublicName == "" {
		resolved.PublicName = resolved.MemberID
	}
	return resolved, nil
}

func loadReplyThreadLookupByShortID(ctx context.Context, queryer coredb.Queryer, parentScopeID string, shortID string) (replyThreadLookup, error) {
	normalized := strings.ToLower(strings.TrimSpace(shortID))
	if normalized == "" {
		return replyThreadLookup{}, invalidArgument("invalid thread identifier")
	}

	if len(normalized) == 8 {
		rows, err := queryer.Query(ctx, `
SELECT
    thread_scope.id,
    thread_scope.id,
    COALESCE(thread_scope.root_message_id, ''),
    COALESCE(thread_scope.root_message_short_id, '')
FROM scopes thread_scope
LEFT JOIN scope_channel_profiles thread_profile
  ON thread_profile.workspace_id = thread_scope.workspace_id
 AND thread_profile.scope_id = thread_scope.id
WHERE thread_scope.parent_scope_id = $1
  AND (
      thread_scope.root_message_short_id = $2
      OR LOWER(COALESCE(thread_profile.slug, '')) = CONCAT('thread-', $2)
  )
  AND thread_scope.type = 'thread'
  AND thread_scope.status = 'active'
  AND thread_scope.archived_at IS NULL
ORDER BY
    CASE WHEN thread_scope.root_message_short_id = $2 THEN 0 ELSE 1 END,
    thread_scope.root_message_short_id ASC,
    thread_scope.id ASC
LIMIT 2
`, parentScopeID, normalized)
		if err != nil {
			return replyThreadLookup{}, fmt.Errorf("load reply thread by short id: %w", err)
		}
		return scanReplyThreadLookupMatches(rows, "load reply thread by short id")
	}

	rows, err := queryer.Query(ctx, `
SELECT
    thread_scope.id,
    thread_scope.id,
    COALESCE(thread_scope.root_message_id, ''),
    COALESCE(thread_scope.root_message_short_id, '')
FROM scopes thread_scope
LEFT JOIN scope_channel_profiles thread_profile
  ON thread_profile.workspace_id = thread_scope.workspace_id
 AND thread_profile.scope_id = thread_scope.id
WHERE thread_scope.parent_scope_id = $1
  AND (
      thread_scope.root_message_short_id LIKE $2
      OR LOWER(COALESCE(thread_profile.slug, '')) LIKE CONCAT('thread-', $2)
  )
  AND thread_scope.type = 'thread'
  AND thread_scope.status = 'active'
  AND thread_scope.archived_at IS NULL
ORDER BY thread_scope.root_message_short_id ASC, thread_scope.id ASC
LIMIT 2
`, parentScopeID, normalized+"%")
	if err != nil {
		return replyThreadLookup{}, fmt.Errorf("load reply thread by short id: %w", err)
	}
	return scanReplyThreadLookupMatches(rows, "load reply thread by short id")
}

func scanReplyThreadLookupMatches(rows coredb.Rows, contextLabel string) (replyThreadLookup, error) {
	defer rows.Close()

	matches := make([]replyThreadLookup, 0, 2)
	for rows.Next() {
		var lookup replyThreadLookup
		if err := rows.Scan(&lookup.ThreadChannelID, &lookup.ThreadID, &lookup.ParentMessageID, &lookup.ShortID); err != nil {
			return replyThreadLookup{}, fmt.Errorf("scan %s: %w", contextLabel, err)
		}
		matches = append(matches, lookup)
	}
	if err := rows.Err(); err != nil {
		return replyThreadLookup{}, fmt.Errorf("iterate %s matches: %w", contextLabel, err)
	}

	switch len(matches) {
	case 0:
		return replyThreadLookup{}, ErrNotFound
	case 1:
		return matches[0], nil
	default:
		return replyThreadLookup{}, conflict("thread identifier is ambiguous")
	}
}

func replyThreadParentScopeID(parent ReplyThreadParentAccess, requestedChannelID string) string {
	if parentScopeID := strings.TrimSpace(parent.ParentChannelID); parentScopeID != "" {
		return parentScopeID
	}
	return strings.TrimSpace(requestedChannelID)
}

func loadReplyThreadSummaryByParentMessageID(ctx context.Context, queryer coredb.Queryer, workspaceID string, parentScopeID string, parentMessageID string) (replyThreadLookup, ReplyThreadSummary, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedParentScopeID := strings.TrimSpace(parentScopeID)
	trimmedParentMessageID := strings.TrimSpace(parentMessageID)
	if trimmedParentScopeID == "" {
		return replyThreadLookup{}, ReplyThreadSummary{}, fmt.Errorf("reply-thread parent scope is missing")
	}

	row := queryer.QueryRow(ctx, `
SELECT
    child.id,
    child.root_message_id,
    COALESCE(child.root_message_short_id, ''),
    child.visible_message_count,
    child.visible_last_message_at
FROM scopes AS child
WHERE child.workspace_id = $1
  AND child.parent_scope_id = $2
  AND child.type = 'thread'
  AND child.status = 'active'
  AND child.archived_at IS NULL
  AND child.root_message_id = $3
`, trimmedWorkspaceID, trimmedParentScopeID, trimmedParentMessageID)

	var lookup replyThreadLookup
	var replyCount int64
	var lastReplyAt *time.Time
	if err := row.Scan(&lookup.ThreadChannelID, &lookup.ParentMessageID, &lookup.ShortID, &replyCount, &lastReplyAt); err != nil {
		if coredb.IsNotFound(err) {
			return replyThreadLookup{}, ReplyThreadSummary{}, ErrNotFound
		}
		return replyThreadLookup{}, ReplyThreadSummary{}, fmt.Errorf("load reply thread: %w", err)
	}

	participantMap, authorMap, err := loadReplyThreadParticipantAndAuthorDetailsMaps(ctx, queryer, []string{lookup.ThreadChannelID})
	if err != nil {
		return replyThreadLookup{}, ReplyThreadSummary{}, err
	}
	participants := participantMap[lookup.ThreadChannelID]
	replyAuthors := authorMap[lookup.ThreadChannelID]

	return lookup, ReplyThreadSummary{
		ThreadChannelID: lookup.ThreadChannelID,
		ReplyCount:      replyCount,
		LastReplyAt:     lastReplyAt,
		ParticipantIDs:  replyThreadParticipantIDsFromDetails(participants),
		Participants:    participants,
		ReplyAuthorIDs:  ReplyThreadRecentAuthorIDsFromDetails(replyAuthors),
		ReplyAuthors:    replyAuthors,
	}, nil
}

func createReplyThread(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, parentScopeID string, parentMessageID string, shortID string) (replyThreadLookup, error) {
	threadChannelID, err := identifiers.NewEntityID("chn")
	if err != nil {
		return replyThreadLookup{}, fmt.Errorf("generate reply thread channel id: %w", err)
	}

	now := time.Now().UTC()
	scope, err := ensureReplyThreadScopeProjectionForParent(ctx, queryer, workspaceID, threadChannelID, parentScopeID, parentMessageID, shortID, "", now)
	if err != nil {
		return replyThreadLookup{}, err
	}

	return replyThreadLookup{
		ThreadChannelID: scope.ID,
		ThreadID:        scope.ID,
		ParentMessageID: parentMessageID,
		ShortID:         shortID,
	}, nil
}

// EnsureReplyThread ensures that the reply-thread scope exists for the given parent message.
func EnsureReplyThread(ctx context.Context, queryer coredb.QueryExecer, workspaceID string, parentScopeID string, parentMessageID string, shortID string) error {
	created, err := createReplyThread(ctx, queryer, workspaceID, parentScopeID, parentMessageID, shortID)
	if err != nil {
		return err
	}
	if strings.TrimSpace(created.ThreadID) != "" {
		return nil
	}
	_, err = loadReplyThreadLookupByShortID(ctx, queryer, parentScopeID, shortID)
	return err
}

func resolveReplyThreadSendTargetForAgent(ctx context.Context, queryer coredb.QueryExecer, actor MemberActor, reference domain.ThreadReference) (ThreadAccess, error) {
	if strings.TrimSpace(reference.ShortID) == "" {
		return ThreadAccess{}, fmt.Errorf("thread short id is required")
	}

	switch reference.Type {
	case domain.ThreadTypeChannel:
		parentChannel, err := resolveAccessibleChannel(ctx, queryer, actor, reference.ChannelSlug)
		if err != nil {
			return ThreadAccess{}, err
		}

		lookup, err := loadReplyThreadLookupByShortID(ctx, queryer, parentChannel.ChannelID, reference.ShortID)
		if errors.Is(err, ErrNotFound) {
			parentMessageID, resolvedShortID, resolveErr := resolveParentMessageIDByShortID(ctx, queryer, parentChannel.ChannelID, reference.ShortID)
			if resolveErr != nil {
				return ThreadAccess{}, resolveErr
			}
			created, createErr := createReplyThread(ctx, queryer, actor.WorkspaceID, parentChannel.ChannelID, parentMessageID, resolvedShortID)
			if createErr != nil {
				return ThreadAccess{}, createErr
			}
			if created.ThreadID == "" {
				lookup, err = loadReplyThreadLookupByShortID(ctx, queryer, parentChannel.ChannelID, resolvedShortID)
			} else {
				lookup = created
				err = nil
			}
		}
		if err != nil {
			return ThreadAccess{}, err
		}

		return ThreadAccess{
			ThreadID:   lookup.ThreadID,
			ThreadType: domain.ThreadTypeChannel,
			ChannelID:  lookup.ThreadChannelID,
			ReplyThread: &ReplyThreadTarget{
				ParentMessageID:   lookup.ParentMessageID,
				ShortID:           lookup.ShortID,
				ParentChannelID:   parentChannel.ChannelID,
				ParentChannelName: reference.ChannelSlug,
			},
		}, nil
	case domain.ThreadTypeDM:
		parentDM, err := resolveExistingDMThread(ctx, queryer, actor, reference.PeerMemberID)
		if err != nil {
			return ThreadAccess{}, err
		}

		lookup, err := loadReplyThreadLookupByShortID(ctx, queryer, parentDM.DMThreadID, reference.ShortID)
		if errors.Is(err, ErrNotFound) {
			parentMessageID, resolvedShortID, resolveErr := resolveParentMessageIDByShortID(ctx, queryer, parentDM.DMThreadID, reference.ShortID)
			if resolveErr != nil {
				return ThreadAccess{}, resolveErr
			}
			created, createErr := createReplyThread(ctx, queryer, actor.WorkspaceID, parentDM.DMThreadID, parentMessageID, resolvedShortID)
			if createErr != nil {
				return ThreadAccess{}, createErr
			}
			if created.ThreadID == "" {
				lookup, err = loadReplyThreadLookupByShortID(ctx, queryer, parentDM.DMThreadID, resolvedShortID)
			} else {
				lookup = created
				err = nil
			}
		}
		if err != nil {
			return ThreadAccess{}, err
		}

		return ThreadAccess{
			ThreadID:     lookup.ThreadID,
			ThreadType:   domain.ThreadTypeDM,
			ChannelID:    lookup.ThreadChannelID,
			PeerMemberID: parentDM.PeerMemberID,
			ReplyThread: &ReplyThreadTarget{
				ParentMessageID: lookup.ParentMessageID,
				ShortID:         lookup.ShortID,
			},
		}, nil
	default:
		return ThreadAccess{}, fmt.Errorf("invalid thread type %q", reference.Type)
	}
}

func resolveParentMessageIDByShortID(ctx context.Context, queryer coredb.Queryer, parentScopeID string, shortID string) (string, string, error) {
	normalized := strings.ToLower(strings.TrimSpace(shortID))
	if normalized == "" {
		return "", "", invalidArgument("invalid thread identifier")
	}

	rows, err := queryer.Query(ctx, `
SELECT m.id
FROM scope_messages m
WHERE m.scope_id = $1
  AND m.id LIKE $2
ORDER BY m.id ASC
LIMIT 2
`, parentScopeID, normalized+"%")
	if err != nil {
		return "", "", fmt.Errorf("resolve parent message id by short id: %w", err)
	}
	defer rows.Close()

	matches := make([]string, 0, 2)
	for rows.Next() {
		var messageID string
		if err := rows.Scan(&messageID); err != nil {
			return "", "", fmt.Errorf("scan parent message id: %w", err)
		}
		matches = append(matches, messageID)
	}
	if err := rows.Err(); err != nil {
		return "", "", fmt.Errorf("iterate parent message id matches: %w", err)
	}

	switch len(matches) {
	case 0:
		return "", "", requestError{kind: ErrNotFound, message: "parent message not found"}
	case 1:
		messageID := strings.TrimSpace(matches[0])
		if len(messageID) < 8 {
			return "", "", fmt.Errorf("invalid parent message id")
		}
		return messageID, messageID[:8], nil
	default:
		return "", "", conflict("thread identifier is ambiguous")
	}
}

func resolveReplyThreadForAgent(ctx context.Context, queryer coredb.Queryer, actor MemberActor, reference domain.ThreadReference) (ThreadAccess, error) {
	if strings.TrimSpace(reference.ShortID) == "" {
		return ThreadAccess{}, fmt.Errorf("thread short id is required")
	}

	switch reference.Type {
	case domain.ThreadTypeChannel:
		parentChannel, err := resolveAccessibleChannel(ctx, queryer, actor, reference.ChannelSlug)
		if err != nil {
			return ThreadAccess{}, err
		}
		lookup, err := loadReplyThreadLookupByShortID(ctx, queryer, parentChannel.ChannelID, reference.ShortID)
		if err != nil {
			return ThreadAccess{}, err
		}
		return ThreadAccess{
			ThreadID:   lookup.ThreadID,
			ThreadType: domain.ThreadTypeChannel,
			ChannelID:  lookup.ThreadChannelID,
		}, nil
	case domain.ThreadTypeDM:
		parentDM, err := resolveExistingDMThread(ctx, queryer, actor, reference.PeerMemberID)
		if err != nil {
			return ThreadAccess{}, err
		}
		lookup, err := loadReplyThreadLookupByShortID(ctx, queryer, parentDM.DMThreadID, reference.ShortID)
		if err != nil {
			return ThreadAccess{}, err
		}
		return ThreadAccess{
			ThreadID:     lookup.ThreadID,
			ThreadType:   domain.ThreadTypeDM,
			ChannelID:    lookup.ThreadChannelID,
			PeerMemberID: parentDM.PeerMemberID,
		}, nil
	default:
		return ThreadAccess{}, fmt.Errorf("invalid thread type %q", reference.Type)
	}
}

func threadShortIDFromMessageID(messageID string) (string, error) {
	trimmed := strings.TrimSpace(messageID)
	if trimmed == "" {
		return "", invalidArgument("parentMessageId is required")
	}
	if len(trimmed) < 8 {
		return "", invalidArgument("invalid parentMessageId")
	}

	shortID := strings.ToLower(trimmed[:8])
	for _, r := range shortID {
		if r >= '0' && r <= '9' {
			continue
		}
		if r >= 'a' && r <= 'f' {
			continue
		}
		return "", invalidArgument("invalid parentMessageId")
	}

	return shortID, nil
}

func orderedMembers(left string, right string) (string, string) {
	if left < right {
		return left, right
	}
	return right, left
}