package app

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"
	"unicode/utf8"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/pinyinsearch"
	"github.com/tidbcloud/AgentTeam/internal/store/workspace"
)

type workspaceMemberRows interface {
	Next() bool
	Scan(dest ...any) error
	Err() error
	Close()
}

func (store *Store) ListWorkspaceMembersForUser(ctx context.Context, userID string, workspaceID string, options workspace.WorkspaceMemberListOptions) (workspace.WorkspaceMemberPage, error) {
	if _, err := store.GetWorkspaceForUser(ctx, userID, workspaceID); err != nil {
		return workspace.WorkspaceMemberPage{}, err
	}

	pageSize := normalizeWorkspaceMemberDirectoryLimit(options.Limit)
	cursor, err := decodeWorkspaceMemberDirectoryCursor(options.Cursor)
	if err != nil {
		return workspace.WorkspaceMemberPage{}, err
	}
	searchQuery := normalizeWorkspaceMemberSearchQuery(options.Query)
	if strings.TrimSpace(options.Query) != "" && utf8.RuneCountInString(searchQuery) < 2 {
		return workspace.WorkspaceMemberPage{Members: []workspace.WorkspaceMember{}}, nil
	}
	excludedUserIDs := normalizeNonEmptyStrings(options.ExcludeUserIDs)
	if len(excludedUserIDs) > workspace.MaxWorkspaceMemberDirectoryExcludeUserIDs {
		return workspace.WorkspaceMemberPage{}, badRequest("too many excluded user ids")
	}

	withClauses := make([]string, 0, 1)
	args := []any{workspaceID}
	if len(excludedUserIDs) > 0 {
		cte, cteArgs, _, err := coredb.BuildStringValuesCTE("excluded_user_ids", "user_id", excludedUserIDs, 2)
		if err != nil {
			return workspace.WorkspaceMemberPage{}, fmt.Errorf("build excluded user ids: %w", err)
		}
		withClauses = append(withClauses, cte)
		args = append(args, cteArgs...)
	}

	query := `
SELECT
    u.id,
    u.email,
    m.id,
    u.display_name,
    m.display_name,
    u.name,
    u.avatar_url,
    m.workspace_role,
    m.created_at
FROM members m
JOIN users u ON u.id = m.user_id
WHERE m.workspace_id = $1
  AND m.type = 'human'
  AND m.status = 'active'
`
	if len(withClauses) > 0 {
		query = "WITH " + strings.Join(withClauses, ",\n") + query
	}
	if cursor != (workspaceMemberDirectoryCursor{}) {
		joinedAtPosition := len(args) + 1
		memberIDPosition := len(args) + 2
		query += "  AND (m.created_at > $" + strconv.Itoa(joinedAtPosition) + " OR (m.created_at = $" + strconv.Itoa(joinedAtPosition) + " AND m.id > $" + strconv.Itoa(memberIDPosition) + "))\n"
		args = append(args, cursor.JoinedAt, cursor.MemberID)
	}
	if len(excludedUserIDs) > 0 {
		query += "  AND NOT EXISTS (SELECT 1 FROM excluded_user_ids excluded WHERE excluded.user_id = m.user_id)\n"
	}
	if searchQuery != "" {
		patternPosition := len(args) + 1
		patternPlaceholder := "$" + strconv.Itoa(patternPosition)
		query += "  AND (LOWER(u.display_name) LIKE " + patternPlaceholder + " OR LOWER(m.display_name) LIKE " + patternPlaceholder + " OR LOWER(u.name) LIKE " + patternPlaceholder + " OR LOWER(u.email) LIKE " + patternPlaceholder + " OR LOWER(m.id) LIKE " + patternPlaceholder + ")\n"
		args = append(args, searchQuery+"%")
	}
	limitPosition := len(args) + 1
	query += "ORDER BY m.created_at ASC, m.id ASC\n"
	query += "LIMIT $" + strconv.Itoa(limitPosition) + "\n"
	args = append(args, pageSize+1)

	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return workspace.WorkspaceMemberPage{}, fmt.Errorf("list workspace members: %w", err)
	}
	defer rows.Close()

	members, err := scanWorkspaceMembers(rows, pageSize+1)
	if err != nil {
		return workspace.WorkspaceMemberPage{}, err
	}
	hasMore := len(members) > pageSize
	if hasMore {
		members = members[:pageSize]
	}
	nextCursor := ""
	if hasMore && len(members) > 0 {
		lastMember := members[len(members)-1]
		nextCursor, err = encodeWorkspaceMemberDirectoryCursor(workspaceMemberDirectoryCursor{
			JoinedAt: lastMember.JoinedAt,
			MemberID: lastMember.MemberID,
		})
		if err != nil {
			return workspace.WorkspaceMemberPage{}, err
		}
	}
	return workspace.WorkspaceMemberPage{
		Members:    members,
		HasMore:    hasMore,
		NextCursor: nextCursor,
	}, nil
}

func (store *Store) GetWorkspaceMemberForUser(ctx context.Context, actorUserID string, workspaceID string, memberID string) (workspace.WorkspaceMember, error) {
	members, err := store.LookupWorkspaceMembersForUser(ctx, actorUserID, workspaceID, workspace.WorkspaceMemberLookupOptions{
		MemberIDs: []string{memberID},
	})
	if err != nil {
		return workspace.WorkspaceMember{}, err
	}
	if len(members) == 0 {
		return workspace.WorkspaceMember{}, ErrNotFound
	}
	return members[0], nil
}

func (store *Store) LookupWorkspaceMembersForUser(ctx context.Context, actorUserID string, workspaceID string, options workspace.WorkspaceMemberLookupOptions) ([]workspace.WorkspaceMember, error) {
	if _, err := store.GetWorkspaceForUser(ctx, actorUserID, workspaceID); err != nil {
		return nil, err
	}

	memberIDs := normalizeNonEmptyStrings(options.MemberIDs)
	userIDs := normalizeNonEmptyStrings(options.UserIDs)
	if len(memberIDs)+len(userIDs) == 0 {
		return nil, badRequest("memberIds or userIds are required")
	}
	if len(memberIDs)+len(userIDs) > workspace.MaxWorkspaceMemberLookupIDs {
		return nil, badRequest("too many member ids")
	}

	withClauses := make([]string, 0, 2)
	args := []any{workspaceID}
	nextParam := 2
	if len(memberIDs) > 0 {
		cte, cteArgs, next, err := coredb.BuildStringValuesCTE("requested_member_ids", "member_id", memberIDs, nextParam)
		if err != nil {
			return nil, fmt.Errorf("build requested member ids: %w", err)
		}
		withClauses = append(withClauses, cte)
		args = append(args, cteArgs...)
		nextParam = next
	}
	if len(userIDs) > 0 {
		cte, cteArgs, next, err := coredb.BuildStringValuesCTE("requested_user_ids", "user_id", userIDs, nextParam)
		if err != nil {
			return nil, fmt.Errorf("build requested user ids: %w", err)
		}
		withClauses = append(withClauses, cte)
		args = append(args, cteArgs...)
		nextParam = next
	}

	predicates := make([]string, 0, 2)
	if len(memberIDs) > 0 {
		predicates = append(predicates, "m.id IN (SELECT member_id FROM requested_member_ids)")
	}
	if len(userIDs) > 0 {
		predicates = append(predicates, "m.user_id IN (SELECT user_id FROM requested_user_ids)")
	}

	query := "WITH " + strings.Join(withClauses, ",\n") + `
SELECT
    u.id,
    u.email,
    m.id,
    u.display_name,
    m.display_name,
    u.name,
    u.avatar_url,
    m.workspace_role,
    m.created_at
FROM members m
JOIN users u ON u.id = m.user_id
WHERE m.workspace_id = $1
  AND m.type = 'human'
  AND m.status = 'active'
  AND (` + strings.Join(predicates, " OR ") + `)
ORDER BY m.created_at ASC, m.id ASC
`

	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("lookup workspace members: %w", err)
	}
	defer rows.Close()

	return scanWorkspaceMembers(rows, len(memberIDs)+len(userIDs))
}

func normalizeWorkspaceMemberSearchQuery(query string) string {
	trimmed := strings.TrimSpace(strings.ToLower(query))
	return strings.TrimPrefix(trimmed, "@")
}

func scanWorkspaceMembers(rows workspaceMemberRows, capacity int) ([]workspace.WorkspaceMember, error) {
	members := make([]workspace.WorkspaceMember, 0, capacity)
	for rows.Next() {
		var member workspace.WorkspaceMember
		var email sql.NullString
		var userDisplayName sql.NullString
		var memberDisplayName sql.NullString
		var legacyName sql.NullString
		var role sql.NullString
		if err := rows.Scan(&member.UserID, &email, &member.Name, &userDisplayName, &memberDisplayName, &legacyName, &member.AvatarURL, &role, &member.JoinedAt); err != nil {
			return nil, fmt.Errorf("scan workspace member: %w", err)
		}
		member.MemberID = strings.TrimSpace(member.Name)
		member.Email = trimmedNullString(email)
		member.DisplayName = preferredDisplayName(trimmedNullString(userDisplayName), trimmedNullString(memberDisplayName), member.MemberID)
		member.Role = trimmedNullString(role)
		member.LegacyTargetKeys = legacyTargetKeys(member.MemberID, trimmedNullString(legacyName))
		member.AccessSources = []string{}
		members = append(members, member)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace members: %w", err)
	}
	return members, nil
}

func (store *Store) UpdateWorkspaceMemberRoleForUser(ctx context.Context, userID string, workspaceID string, memberUserID string, role string) error {
	if err := store.requireWorkspaceMemberRoleManagement(ctx, userID, workspaceID); err != nil {
		return err
	}

	trimmedMemberUserID := strings.TrimSpace(memberUserID)
	if trimmedMemberUserID == "" {
		return badRequest("userId is required")
	}

	trimmedRole := strings.TrimSpace(role)
	if trimmedRole == "" {
		return badRequest("role is required")
	}
	workspaceRole := domain.WorkspaceRole(trimmedRole)
	switch workspaceRole {
	case domain.WorkspaceRoleAdmin, domain.WorkspaceRoleMember:
	default:
		return badRequest("invalid role")
	}

	row := store.db.QueryRow(ctx, `
SELECT workspace_role
FROM members
WHERE workspace_id = $1 AND user_id = $2 AND type = 'human' AND status = 'active'
`, workspaceID, trimmedMemberUserID)
	var currentRole string
	if err := row.Scan(&currentRole); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load workspace member role: %w", err)
	}
	if domain.WorkspaceRole(currentRole) == domain.WorkspaceRoleOwner {
		return badRequest("cannot change owner role")
	}
	if domain.WorkspaceRole(currentRole) == workspaceRole {
		return nil
	}

	commandTag, err := store.db.Exec(ctx, `
UPDATE members
SET workspace_role = $3, updated_at = NOW()
WHERE workspace_id = $1 AND user_id = $2 AND type = 'human' AND status = 'active'
`, workspaceID, trimmedMemberUserID, workspaceRole)
	if err != nil {
		return fmt.Errorf("update workspace member role: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (store *Store) WorkspaceMemberOffboardingImpactForUser(ctx context.Context, userID string, workspaceID string, memberUserID string) (workspace.WorkspaceMemberOffboardingImpact, error) {
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedMemberUserID := strings.TrimSpace(memberUserID)
	if trimmedWorkspaceID == "" || trimmedMemberUserID == "" {
		return workspace.WorkspaceMemberOffboardingImpact{}, badRequest("Invalid request")
	}
	if trimmedUserID == trimmedMemberUserID {
		return workspace.WorkspaceMemberOffboardingImpact{}, badRequest("cannot remove yourself")
	}
	if err := store.requireWorkspaceMemberManagement(ctx, trimmedUserID, trimmedWorkspaceID); err != nil {
		return workspace.WorkspaceMemberOffboardingImpact{}, err
	}

	currentRole, err := loadActiveWorkspaceMemberRole(ctx, store.db, trimmedWorkspaceID, trimmedMemberUserID, false)
	if err != nil {
		return workspace.WorkspaceMemberOffboardingImpact{}, err
	}
	if currentRole == domain.WorkspaceRoleOwner {
		return workspace.WorkspaceMemberOffboardingImpact{}, badRequest("cannot remove workspace owner")
	}

	impact, err := loadWorkspaceMemberOffboardingImpact(ctx, store.db, trimmedWorkspaceID, trimmedMemberUserID)
	if err != nil {
		return workspace.WorkspaceMemberOffboardingImpact{}, err
	}
	return impact, nil
}

func (store *Store) RemoveWorkspaceMemberForUser(ctx context.Context, userID string, workspaceID string, memberUserID string) ([]string, error) {
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedMemberUserID := strings.TrimSpace(memberUserID)
	if trimmedWorkspaceID == "" || trimmedMemberUserID == "" {
		return nil, badRequest("Invalid request")
	}
	if trimmedUserID == trimmedMemberUserID {
		return nil, badRequest("cannot remove yourself")
	}
	if err := store.requireWorkspaceMemberManagement(ctx, trimmedUserID, trimmedWorkspaceID); err != nil {
		return nil, err
	}

	// The offboarding transaction restarts when a personal installation the
	// discovery pass missed (a concurrently created App definition) surfaces
	// under the member fence. The member row lock guarantees convergence:
	// no installation transaction can sit between its member lock and commit.
	// Each attempt owns a private avatar-cleanup collector: a rolled-back
	// attempt may have transiently looked like the last installation, and its
	// collected targets must never leak into the committed attempt.
	var revokedMachineIDs []string
	var committedAvatarCleanup *appAvatarMediaCleanupCollector
	const offboardingMaxAttempts = 3
	for attempt := 0; attempt < offboardingMaxAttempts; attempt++ {
		attemptCtx, attemptAvatarCleanup := withAppAvatarMediaCleanup(ctx)
		committed, retry, machineIDs, err := store.removeWorkspaceMemberOnce(ctx, attemptCtx, trimmedUserID, trimmedWorkspaceID, trimmedMemberUserID)
		if retry {
			// The sentinel rides alongside retry=true; only real failures
			// escape the agentteam. The rolled-back attempt's collector is
			// discarded with it.
			if err != nil && !errors.Is(err, errOffboardingConcurrentPersonalInstall) {
				return nil, err
			}
			continue
		}
		if err != nil {
			return nil, err
		}
		if committed {
			revokedMachineIDs = machineIDs
			committedAvatarCleanup = attemptAvatarCleanup
			break
		}
		return nil, conflict("workspace member removal did not complete; retry")
	}
	if revokedMachineIDs == nil {
		return nil, conflict("workspace member kept creating personal Apps; retry removal")
	}
	store.finalizeAppAvatarMediaCleanup(ctx, committedAvatarCleanup)
	return revokedMachineIDs, nil
}

// errOffboardingConcurrentPersonalInstall signals the enclosing retry agentteam
// that the discovery pass missed a concurrently created personal App and the
// whole removal transaction must restart.
var errOffboardingConcurrentPersonalInstall = errors.New("concurrent personal app installation during offboarding")

func (store *Store) removeWorkspaceMemberOnce(
	ctx context.Context,
	cleanupCtx context.Context,
	userID string,
	workspaceID string,
	memberUserID string,
) (bool, bool, []string, error) {
	tx, err := store.db.BeginTx(cleanupCtx, coredb.TxOptions{})
	if err != nil {
		return false, false, nil, fmt.Errorf("begin remove workspace member tx: %w", err)
	}
	defer func() {
		_ = tx.Rollback(cleanupCtx)
	}()

	// Personal installations are removed before any member row lock:
	// their cleanup follows the canonical definition-first mutation order.
	if err := store.uninstallPersonalInstallationsForOffboardedMemberInTx(cleanupCtx, tx, workspaceID, memberUserID, userID); err != nil {
		return false, false, nil, err
	}

	currentRole, err := loadActiveWorkspaceMemberRole(cleanupCtx, tx, workspaceID, memberUserID, true)
	if err != nil {
		return false, false, nil, err
	}
	if currentRole == domain.WorkspaceRoleOwner {
		return false, false, nil, badRequest("cannot remove workspace owner")
	}

	var offboardedMemberID string
	if err := tx.QueryRow(cleanupCtx, `
SELECT id
FROM members
WHERE workspace_id = $1
  AND user_id = $2
  AND type = 'human'
LIMIT 1
FOR UPDATE
`, workspaceID, memberUserID).Scan(&offboardedMemberID); err != nil {
		return false, false, nil, fmt.Errorf("load offboarding member id: %w", err)
	}

	// Member fence reached: re-scan with a current read. Any personal
	// installation created after the discovery pass forces a restart so the
	// next attempt's discovery includes it.
	var stragglerCount int
	if err := tx.QueryRow(cleanupCtx, `
SELECT COUNT(*)
FROM service_installations si
JOIN members owner ON owner.id = si.owner_member_id
WHERE si.workspace_id = $1
  AND si.service_kind = 'app'
  AND si.ownership_kind = 'personal'
  AND si.status = 'active'
  AND owner.workspace_id = si.workspace_id
  AND owner.user_id = $2
FOR UPDATE
`, workspaceID, memberUserID).Scan(&stragglerCount); err != nil {
		return false, false, nil, fmt.Errorf("rescan offboarded member personal installations: %w", err)
	}
	if stragglerCount > 0 {
		return false, true, nil, errOffboardingConcurrentPersonalInstall
	}

	// Disable the member's held installation ACL grants while their member
	// row is locked, so a later re-invite cannot resurrect old authority.
	if err := store.serviceActorStore().InTx(tx).DisableMemberInstallationGrants(cleanupCtx, workspaceID, offboardedMemberID, time.Now().UTC()); err != nil {
		return false, false, nil, err
	}

	revokedMachineIDs, err := store.archiveWorkspaceMemberRuntimeResourcesInTx(cleanupCtx, tx, workspaceID, memberUserID)
	if err != nil {
		return false, false, nil, err
	}
	if _, err := workspace.DeactivateHumanWorkspaceMemberInTx(cleanupCtx, tx, workspaceID, memberUserID, store.markMemberScopeProjectionsRemoved, removePrivateEntriesForOwner); err != nil {
		return false, false, nil, err
	}
	if err := tx.Commit(cleanupCtx); err != nil {
		return false, false, nil, fmt.Errorf("commit remove workspace member tx: %w", err)
	}
	return true, false, revokedMachineIDs, nil
}

func loadActiveWorkspaceMemberRole(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, workspaceID string, memberUserID string, forUpdate bool) (domain.WorkspaceRole, error) {
	query := `
SELECT workspace_role
FROM members
WHERE workspace_id = $1 AND user_id = $2 AND type = 'human' AND status = 'active'
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}

	row := queryer.QueryRow(ctx, query, workspaceID, memberUserID)
	var currentRole string
	if err := row.Scan(&currentRole); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("load workspace member role: %w", err)
	}
	return domain.WorkspaceRole(currentRole), nil
}

func loadWorkspaceMemberOffboardingImpact(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, workspaceID string, memberUserID string) (workspace.WorkspaceMemberOffboardingImpact, error) {
	var impact workspace.WorkspaceMemberOffboardingImpact
	if err := queryer.QueryRow(ctx, `
SELECT
  (SELECT COUNT(*)
   FROM machines
   WHERE workspace_id = $1
     AND created_by_user_id = $2
     AND status = 'active') AS machine_count,
  (SELECT COUNT(*)
   FROM agents a
   JOIN machines mac
     ON mac.workspace_id = a.workspace_id
    AND mac.id = a.machine_id
   WHERE a.workspace_id = $1
     AND mac.created_by_user_id = $2
     AND a.status <> 'archived') AS agent_count
`, workspaceID, memberUserID).Scan(&impact.MachineCount, &impact.AgentCount); err != nil {
		return workspace.WorkspaceMemberOffboardingImpact{}, fmt.Errorf("load workspace member offboarding impact: %w", err)
	}
	return impact, nil
}

type workspaceMemberRuntimeAgent struct {
	AgentID  string
	MemberID string
}

func (store *Store) archiveWorkspaceMemberRuntimeResourcesInTx(ctx context.Context, tx coredb.Tx, workspaceID string, memberUserID string) ([]string, error) {
	revokedMachineIDs, err := loadWorkspaceMemberActiveMachineIDsForUpdate(ctx, tx, workspaceID, memberUserID)
	if err != nil {
		return nil, err
	}

	agents, err := loadWorkspaceMemberRuntimeAgentsForUpdate(ctx, tx, workspaceID, memberUserID)
	if err != nil {
		return nil, err
	}

	now := time.Now().UTC()
	agentIDs := make([]string, 0, len(agents))
	for _, item := range agents {
		agentIDs = append(agentIDs, item.AgentID)
		if err := store.markMemberScopeProjectionsRemoved(ctx, tx, item.MemberID, now); err != nil {
			return nil, err
		}
		if _, err := tx.Exec(ctx, `
UPDATE members
SET agent_display_name_key = NULL,
    updated_at = $2
WHERE id = $1
`, item.MemberID, now); err != nil {
			return nil, fmt.Errorf("release archived agent display name key: %w", err)
		}
	}
	if err := deleteKnowledgeBaseAgentGrantsForAgentIDs(ctx, tx, workspaceID, agentIDs); err != nil {
		return nil, err
	}
	if err := deleteMemoryAgentBindingsForAgentIDs(ctx, tx, workspaceID, agentIDs); err != nil {
		return nil, err
	}

	if _, err := tx.Exec(ctx, `
UPDATE agents a
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
SET a.status = 'archived',
    a.name = CONCAT('archived-', a.id),
    a.auth_token_hash = NULL,
    a.auto_wake_enabled = FALSE,
    a.updated_at = $3
WHERE a.workspace_id = $1
  AND mac.created_by_user_id = $2
  AND a.status <> 'archived'
`, workspaceID, memberUserID, now); err != nil {
		return nil, fmt.Errorf("archive member runtime agents: %w", err)
	}

	for _, item := range agents {
		pinyinFull, pinyinInitials := pinyinsearch.Variants("archived-" + item.AgentID)
		if _, err := tx.Exec(ctx, `
UPDATE agents
SET name_pinyin = $2,
    name_pinyin_initials = $3
WHERE id = $1
`, item.AgentID, pinyinFull, pinyinInitials); err != nil {
			return nil, fmt.Errorf("sync archived agent name pinyin: %w", err)
		}
	}

	if _, err := tx.Exec(ctx, `
UPDATE machines
SET status = 'revoked',
    updated_at = $3
WHERE workspace_id = $1
  AND created_by_user_id = $2
  AND status = 'active'
`, workspaceID, memberUserID, now); err != nil {
		return nil, fmt.Errorf("revoke member runtime machines: %w", err)
	}
	return revokedMachineIDs, nil
}

func loadWorkspaceMemberActiveMachineIDsForUpdate(ctx context.Context, queryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}, workspaceID string, memberUserID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT id
FROM machines
WHERE workspace_id = $1
  AND created_by_user_id = $2
  AND status = 'active'
ORDER BY id ASC
FOR UPDATE
`, workspaceID, memberUserID)
	if err != nil {
		return nil, fmt.Errorf("list member runtime machines for removal: %w", err)
	}
	defer rows.Close()

	revokedMachineIDs := make([]string, 0)
	for rows.Next() {
		var machineID string
		if err := rows.Scan(&machineID); err != nil {
			return nil, fmt.Errorf("scan member runtime machine for removal: %w", err)
		}
		revokedMachineIDs = append(revokedMachineIDs, strings.TrimSpace(machineID))
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate member runtime machines for removal: %w", err)
	}
	return revokedMachineIDs, nil
}

func loadWorkspaceMemberRuntimeAgentsForUpdate(ctx context.Context, queryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}, workspaceID string, memberUserID string) ([]workspaceMemberRuntimeAgent, error) {
	rows, err := queryer.Query(ctx, `
SELECT a.id, a.member_id
FROM agents a
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
WHERE a.workspace_id = $1
  AND mac.created_by_user_id = $2
  AND a.status <> 'archived'
ORDER BY a.id ASC
FOR UPDATE
`, workspaceID, memberUserID)
	if err != nil {
		return nil, fmt.Errorf("list member runtime agents for removal: %w", err)
	}
	defer rows.Close()

	agents := make([]workspaceMemberRuntimeAgent, 0)
	for rows.Next() {
		var item workspaceMemberRuntimeAgent
		if err := rows.Scan(&item.AgentID, &item.MemberID); err != nil {
			return nil, fmt.Errorf("scan member runtime agent for removal: %w", err)
		}
		agents = append(agents, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate member runtime agents for removal: %w", err)
	}
	return agents, nil
}

func removePrivateEntriesForOwner(ctx context.Context, execer coredb.QueryExecer, ownerMemberID string, removedAt time.Time) error {
	trimmedOwnerMemberID := strings.TrimSpace(ownerMemberID)
	if trimmedOwnerMemberID == "" {
		return nil
	}
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE sns
FROM scope_notification_settings sns
JOIN scope_memberships sm
  ON sm.scope_id = sns.scope_id
 AND sm.member_id = sns.member_id
WHERE sm.owner_member_id = $1
  AND sm.visibility = 'private'
  AND sm.removed_at IS NULL
`, trimmedOwnerMemberID); err != nil {
		return fmt.Errorf("delete owner private wake subscriptions: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $2,
    updated_at = $2
WHERE owner_member_id = $1
  AND visibility = 'private'
  AND removed_at IS NULL
`, trimmedOwnerMemberID, now); err != nil {
		return fmt.Errorf("remove owner private scope memberships: %w", err)
	}
	return nil
}