package app

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	"github.com/tidbcloud/AgentTeam/internal/store/channel"
	"github.com/tidbcloud/AgentTeam/internal/store/message"
)

const (
	channelAgentEntryVisibilityPublic  = "public"
	channelAgentEntryVisibilityPrivate = "private"
)

type agentEntryOwner struct {
	AgentID       string
	AgentMemberID string
	OwnerMemberID string
	OwnerUserID   string
}

type activeChannelAgentEntry struct {
	Visibility    string
	OwnerMemberID sql.NullString
}

type privateAgentEntryOwnerHostChannel struct {
	OwnerMemberID string
	HostChannelID string
}

func (store *Store) isMemberInChannel(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, memberID string, channelID string) (bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
`, strings.TrimSpace(channelID), strings.TrimSpace(memberID))
	var count int
	if err := row.Scan(&count); err != nil {
		return false, fmt.Errorf("check channel membership projection: %w", err)
	}
	return count > 0, nil
}

func (store *Store) ensureChannelScopeProjection(ctx context.Context, execer coredb.QueryExecer, channelID string) (string, error) {
	trimmedChannelID := strings.TrimSpace(channelID)
	if trimmedChannelID == "" {
		return "", badRequest("channelId is required")
	}

	if _, err := message.EnsureScopeProjectionForThreadAccess(ctx, execer, message.ThreadAccess{
		ThreadType: domain.ThreadTypeChannel,
		ChannelID:  trimmedChannelID,
	}, "", time.Now().UTC()); err != nil {
		return "", err
	}
	return trimmedChannelID, nil
}

func (store *Store) ensureDMScopeProjection(ctx context.Context, execer coredb.QueryExecer, dmThreadID string) (string, error) {
	trimmedDMThreadID := strings.TrimSpace(dmThreadID)
	if trimmedDMThreadID == "" {
		return "", badRequest("dmThreadId is required")
	}

	if _, err := message.EnsureScopeProjectionForThreadAccess(ctx, execer, message.ThreadAccess{
		ThreadType: domain.ThreadTypeDM,
		DMThreadID: trimmedDMThreadID,
	}, "", time.Now().UTC()); err != nil {
		return "", err
	}
	return trimmedDMThreadID, nil
}

func (store *Store) ensureReplyThreadScopeProjection(ctx context.Context, execer coredb.QueryExecer, threadChannelID string) (string, error) {
	trimmedThreadChannelID := strings.TrimSpace(threadChannelID)
	if trimmedThreadChannelID == "" {
		return "", badRequest("threadChannelId is required")
	}

	if _, err := message.EnsureScopeProjectionForThreadAccess(ctx, execer, message.ThreadAccess{
		ThreadType:  domain.ThreadTypeChannel,
		ChannelID:   trimmedThreadChannelID,
		ReplyThread: &message.ReplyThreadTarget{},
	}, "", time.Now().UTC()); err != nil {
		return "", err
	}
	return trimmedThreadChannelID, nil
}

func (store *Store) ensureScopeParticipantProjection(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, agentState string, createdAt time.Time) error {
	return store.ensureScopeParticipantProjectionWithOwner(ctx, execer, scopeID, memberID, "", agentState, createdAt)
}

func (store *Store) ensureScopeParticipantProjectionWithOwner(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, ownerMemberID string, agentState string, createdAt time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedScopeID == "" || trimmedMemberID == "" {
		return nil
	}
	now := createdAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	var latestSeq, latestGlobalSeq int64
	if err := execer.QueryRow(ctx, `
SELECT latest_seq, latest_global_seq
FROM scopes
WHERE id = $1
`, trimmedScopeID).Scan(&latestSeq, &latestGlobalSeq); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load scope bounds for membership projection: %w", err)
	}
	if err := message.EnsureScopeMembership(ctx, execer, trimmedScopeID, trimmedMemberID, "public", strings.TrimSpace(ownerMemberID), strings.TrimSpace(agentState), latestSeq, latestGlobalSeq, now); err != nil {
		return fmt.Errorf("insert scope membership projection: %w", err)
	}
	return nil
}

func (store *Store) ensureChannelScopeParticipantProjection(ctx context.Context, execer coredb.QueryExecer, channelID string, memberID string, agentState string, createdAt time.Time) error {
	if _, err := store.ensureChannelScopeProjection(ctx, execer, channelID); err != nil {
		return err
	}
	return store.ensureScopeParticipantProjection(ctx, execer, channelID, memberID, agentState, createdAt)
}

func (store *Store) ensureChannelServiceScopeParticipantProjection(ctx context.Context, execer coredb.QueryExecer, channelID string, memberID string, ownerMemberID string, agentState string, createdAt time.Time) error {
	if _, err := store.ensureChannelScopeProjection(ctx, execer, channelID); err != nil {
		return err
	}
	return store.ensureScopeParticipantProjectionWithOwner(ctx, execer, channelID, memberID, ownerMemberID, agentState, createdAt)
}

func (store *Store) setScopeParticipantArchivedState(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, archivedAt *time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedScopeID == "" || trimmedMemberID == "" {
		return nil
	}
	now := time.Now().UTC()
	if archivedAt != nil {
		now = archivedAt.UTC()
		if now.IsZero() {
			now = time.Now().UTC()
		}
		if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    archived_at = COALESCE(archived_at, $3),
    updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND removed_at IS NULL
`, trimmedScopeID, trimmedMemberID, now); err != nil {
			return fmt.Errorf("archive scope membership projection: %w", err)
		}
		return nil
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    authority_from_at = LEAST($3, CURRENT_TIMESTAMP(6)),
    authority_to_at = NULL,
    archived_at = NULL,
    updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND archived_at IS NOT NULL
`, trimmedScopeID, trimmedMemberID, now); err != nil {
		return fmt.Errorf("unarchive scope membership projection: %w", err)
	}
	return nil
}

func (store *Store) ensurePublicChannelAgentEntryProjection(ctx context.Context, execer coredb.QueryExecer, workspaceID string, channelID string, agentMemberID string, createdAt time.Time) error {
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if trimmedChannelID == "" || trimmedAgentMemberID == "" {
		return nil
	}
	if _, err := store.ensureChannelScopeProjection(ctx, execer, trimmedChannelID); err != nil {
		return err
	}
	now := createdAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if err := message.EnsureScopeMembership(ctx, execer, trimmedChannelID, trimmedAgentMemberID, channelAgentEntryVisibilityPublic, "", "", 0, 0, now); err != nil {
		return fmt.Errorf("insert public channel agent scope membership: %w", err)
	}
	_ = workspaceID
	return nil
}

func (store *Store) archiveScopeProjection(ctx context.Context, execer coredb.QueryExecer, scopeID string, archivedAt time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	if trimmedScopeID == "" {
		return nil
	}
	now := archivedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	// Canonical channel-writer lock order starts at the scope row. Member
	// mutations then take profile and membership locks in that order, so
	// archiving must not begin with notification/profile locks and later wait
	// on the scope (the reverse order can deadlock with a concurrent removal).
	if _, err := execer.Exec(ctx, `
UPDATE scopes
SET status = 'archived',
    archived_at = COALESCE(archived_at, $2),
    updated_at = $2
WHERE id = $1
`, trimmedScopeID, now); err != nil {
		return fmt.Errorf("archive scope projection: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_channel_profiles
SET slug = $2,
    updated_at = $3
WHERE scope_id = $1
  AND slug <> $2
`, trimmedScopeID, channel.ArchivedScopeProfileSlug(trimmedScopeID), now); err != nil {
		return fmt.Errorf("release archived scope slug: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $2,
    archived_at = COALESCE(archived_at, $2),
    updated_at = $2
WHERE scope_id = $1
  AND removed_at IS NULL
`, trimmedScopeID, now); err != nil {
		return fmt.Errorf("archive scope memberships: %w", err)
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM scope_notification_settings
WHERE scope_id = $1
`, trimmedScopeID); err != nil {
		return fmt.Errorf("delete archived scope notification settings: %w", err)
	}
	return nil
}

// queryScopeMembershipRows runs a (scope_id, member_id, effective_from_seq)
// projection — the scope_memberships PK — of the rows matching the canonical
// cleanup predicate. Callers pass the SAME predicate used to identify retireable
// rows, with FOR UPDATE. The captured PKs let the helper narrow its retirement
// UPDATE to exactly these rows, so captured == retired holds even if a concurrent
// writer inserts/revives a matching child row between this SELECT and the UPDATE
// (TiDB takes no gap lock; the narrow UPDATE simply does not touch the new PK).
func queryScopeMembershipRows(ctx context.Context, queryer coredb.Queryer, query string, args ...any) ([]scopeMembershipRow, error) {
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make([]scopeMembershipRow, 0)
	for rows.Next() {
		var row scopeMembershipRow
		if err := rows.Scan(&row.ScopeID, &row.MemberID, &row.EffectiveFromSeq); err != nil {
			return nil, err
		}
		row.ScopeID = strings.TrimSpace(row.ScopeID)
		row.MemberID = strings.TrimSpace(row.MemberID)
		if row.ScopeID != "" && row.MemberID != "" {
			out = append(out, row)
		}
	}
	return out, rows.Err()
}

// retireScopeMembershipsByPKs retires exactly the captured membership rows by
// their full PK. Because the caller captured them with FOR UPDATE, those rows are
// locked for the life of this transaction, so this UPDATE retires precisely the
// captured set — never a concurrently-inserted/revived row that shares the broad
// cleanup predicate but not these PKs.
func retireScopeMembershipsByPKs(ctx context.Context, execer coredb.QueryExecer, rows []scopeMembershipRow, removedAt time.Time) error {
	if len(rows) == 0 {
		return nil
	}
	var (
		placeholders strings.Builder
		args         = make([]any, 0, 1+len(rows)*3)
	)
	args = append(args, removedAt) // $1 for removed_at + updated_at
	for index, row := range rows {
		if index > 0 {
			placeholders.WriteByte(',')
		}
		base := 2 + index*3
		fmt.Fprintf(&placeholders, "($%d,$%d,$%d)", base, base+1, base+2)
		args = append(args, row.ScopeID, row.MemberID, row.EffectiveFromSeq)
	}
	query := fmt.Sprintf(`
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $1,
    updated_at = $1
WHERE (scope_id, member_id, effective_from_seq) IN (%s)
`, placeholders.String())
	if _, err := execer.Exec(ctx, query, args...); err != nil {
		return fmt.Errorf("retire captured scope memberships by PK: %w", err)
	}
	return nil
}

func (store *Store) markScopeParticipantProjectionRemoved(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, removedAt time.Time, changed *[]scopeMembershipRow) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedScopeID == "" || trimmedMemberID == "" {
		return nil
	}
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM scope_notification_settings
WHERE scope_id = $1 AND member_id = $2
`, trimmedScopeID, trimmedMemberID); err != nil {
		return fmt.Errorf("delete scope notification setting: %w", err)
	}
	if changed != nil {
		captured, err := queryScopeMembershipRows(ctx, execer, `
SELECT scope_id, member_id, effective_from_seq
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND removed_at IS NULL
FOR UPDATE
`, trimmedScopeID, trimmedMemberID)
		if err != nil {
			return fmt.Errorf("capture public scope membership removal: %w", err)
		}
		*changed = append(*changed, captured...)
		return retireScopeMembershipsByPKs(ctx, execer, captured, now)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $3,
    updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND removed_at IS NULL
`, trimmedScopeID, trimmedMemberID, now); err != nil {
		return fmt.Errorf("mark public scope membership removed: %w", err)
	}
	return nil
}

func (store *Store) markPublicChannelAgentEntryProjectionRemoved(ctx context.Context, execer coredb.QueryExecer, channelID string, agentMemberID string, removedAt time.Time, changed *[]scopeMembershipRow) error {
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if trimmedChannelID == "" || trimmedAgentMemberID == "" {
		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 scopes scope
  ON scope.id = sns.scope_id
WHERE sns.member_id = $2
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
`, trimmedChannelID, trimmedAgentMemberID); err != nil {
		return fmt.Errorf("delete public channel agent scope notification: %w", err)
	}
	if changed != nil {
		captured, err := queryScopeMembershipRows(ctx, execer, `
SELECT sm.scope_id, sm.member_id, sm.effective_from_seq
FROM scope_memberships sm
JOIN scopes scope
  ON scope.id = sm.scope_id
WHERE sm.member_id = $2
  AND sm.visibility = 'public'
  AND sm.removed_at IS NULL
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
FOR UPDATE
`, trimmedChannelID, trimmedAgentMemberID)
		if err != nil {
			return fmt.Errorf("capture public channel agent scope membership removal: %w", err)
		}
		*changed = append(*changed, captured...)
		if preChannelMemberCleanupUpdateHookForTest != nil {
			preChannelMemberCleanupUpdateHookForTest(ctx)
		}
		return retireScopeMembershipsByPKs(ctx, execer, captured, now)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships sm
JOIN scopes scope
  ON scope.id = sm.scope_id
SET sm.removed_at = $3,
    sm.updated_at = $3,
    sm.unread_generation = sm.unread_generation + 1
WHERE sm.member_id = $2
  AND sm.visibility = 'public'
  AND sm.removed_at IS NULL
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
`, trimmedChannelID, trimmedAgentMemberID, now); err != nil {
		return fmt.Errorf("mark public channel agent scope membership removed: %w", err)
	}
	return nil
}

func (store *Store) loadAgentEntryOwner(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, workspaceID string, agentID string) (agentEntryOwner, error) {
	if tx, ok := queryer.(coredb.Tx); ok {
		return store.lockAgentEntryOwnerForUpdate(ctx, tx, workspaceID, agentID)
	}
	row := queryer.QueryRow(ctx, `
SELECT a.id, a.member_id, owner_member.id, owner_member.user_id
FROM agents a
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
JOIN members owner_member
  ON owner_member.workspace_id = a.workspace_id
 AND owner_member.user_id = mac.created_by_user_id
 AND owner_member.type = 'human'
 AND owner_member.status = 'active'
WHERE a.workspace_id = $1
  AND a.id = $2
  AND a.status <> 'archived'
  AND mac.status = 'active'
`, strings.TrimSpace(workspaceID), strings.TrimSpace(agentID))

	var owner agentEntryOwner
	if err := row.Scan(&owner.AgentID, &owner.AgentMemberID, &owner.OwnerMemberID, &owner.OwnerUserID); err != nil {
		if coredb.IsNotFound(err) {
			return agentEntryOwner{}, ErrNotFound
		}
		return agentEntryOwner{}, fmt.Errorf("load agent entry owner: %w", err)
	}
	return owner, nil
}

// lockAgentEntryOwnerForUpdate discovers the expected relationship from the
// caller's snapshot, then revalidates it with the shared canonical principal
// lock order (manager member -> machine -> Agent -> Agent member). The locked
// revalidation is the authority; the optimistic discovery never grants access.
func (store *Store) lockAgentEntryOwnerForUpdate(ctx context.Context, tx coredb.Tx, workspaceID string, agentID string) (agentEntryOwner, error) {
	workspaceID = strings.TrimSpace(workspaceID)
	agentID = strings.TrimSpace(agentID)
	if workspaceID == "" || agentID == "" {
		return agentEntryOwner{}, ErrNotFound
	}

	var expected access.AuthenticatedAgentPrincipal
	var manager access.HumanManager
	expected.WorkspaceID = workspaceID
	expected.AgentID = agentID
	if err := tx.QueryRow(ctx, `
SELECT a.machine_id, a.member_id, mac.created_by_user_id, owner_member.id
FROM agents a
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
JOIN members owner_member
  ON owner_member.workspace_id = a.workspace_id
 AND owner_member.user_id = mac.created_by_user_id
 AND owner_member.type = 'human'
WHERE a.workspace_id = $1
  AND a.id = $2
`, workspaceID, agentID).Scan(
		&expected.MachineID,
		&expected.MemberID,
		&manager.UserID,
		&manager.MemberID,
	); err != nil {
		if coredb.IsNotFound(err) {
			return agentEntryOwner{}, ErrNotFound
		}
		return agentEntryOwner{}, fmt.Errorf("discover agent entry owner: %w", err)
	}
	if err := access.LockAndRevalidateAgentPrincipalManager(ctx, tx, expected, manager); err != nil {
		if errors.Is(err, ErrForbidden) || errors.Is(err, ErrNotFound) {
			return agentEntryOwner{}, ErrNotFound
		}
		return agentEntryOwner{}, fmt.Errorf("lock agent entry owner: %w", err)
	}
	return agentEntryOwner{
		AgentID:       expected.AgentID,
		AgentMemberID: expected.MemberID,
		OwnerMemberID: manager.MemberID,
		OwnerUserID:   manager.UserID,
	}, nil
}

func (store *Store) loadActiveChannelAgentEntry(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, channelID string, agentMemberID string) (activeChannelAgentEntry, bool, error) {
	query := `
SELECT visibility, owner_member_id
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND visibility IN ('public', 'private')
  AND removed_at IS NULL
  AND archived_at IS NULL
  AND effective_to_seq IS NULL
ORDER BY CASE WHEN visibility = 'private' THEN 0 ELSE 1 END, effective_from_seq DESC
LIMIT 1
`
	if _, ok := queryer.(coredb.Tx); ok {
		query += "FOR UPDATE\n"
	}
	row := queryer.QueryRow(ctx, query, strings.TrimSpace(channelID), strings.TrimSpace(agentMemberID))

	var entry activeChannelAgentEntry
	if err := row.Scan(&entry.Visibility, &entry.OwnerMemberID); err != nil {
		if coredb.IsNotFound(err) {
			return activeChannelAgentEntry{}, false, nil
		}
		return activeChannelAgentEntry{}, false, fmt.Errorf("load active channel agent entry: %w", err)
	}
	return entry, true, nil
}

func (store *Store) ensurePublicChannelAgentEntryAllowed(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, channelID string, agentMemberID string) error {
	entry, ok, err := store.loadActiveChannelAgentEntry(ctx, queryer, channelID, agentMemberID)
	if err != nil || !ok {
		return err
	}
	if strings.TrimSpace(entry.Visibility) != channelAgentEntryVisibilityPublic {
		return conflict("agent already has an active private entry in this channel")
	}
	return nil
}

func (store *Store) removePrivateEntriesForOwnerHostChannel(ctx context.Context, execer coredb.QueryExecer, ownerMemberID string, hostChannelID string, removedAt time.Time, changed *[]scopeMembershipRow) error {
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	deleteOwnerPrivateWakeSubscriptions := func() error {
		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
JOIN scopes scope
  ON scope.id = sns.scope_id
WHERE sm.owner_member_id = $2
  AND sm.visibility = 'private'
  AND sm.removed_at IS NULL
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
`, strings.TrimSpace(hostChannelID), strings.TrimSpace(ownerMemberID)); err != nil {
			return fmt.Errorf("delete owner private wake subscriptions for channel: %w", err)
		}
		return nil
	}
	if changed != nil {
		// Narrow (effects) path: capture+lock memberships FIRST, then the
		// notification DELETE (its JOIN still resolves while memberships are not yet
		// retired), then narrow retire by the captured PKs. This matches the writer
		// fence lock order (membership before notification) and avoids a
		// notification→membership vs remover membership→notification reverse-order
		// deadlock. The browser path below keeps the original order/lock graph.
		captured, err := queryScopeMembershipRows(ctx, execer, `
SELECT sm.scope_id, sm.member_id, sm.effective_from_seq
FROM scope_memberships sm
WHERE sm.scope_id IN (
    SELECT id
    FROM scopes
    WHERE id = $1 OR parent_scope_id = $1
  )
  AND sm.owner_member_id = $2
  AND sm.visibility = 'private'
  AND sm.removed_at IS NULL
FOR UPDATE
`, strings.TrimSpace(hostChannelID), strings.TrimSpace(ownerMemberID))
		if err != nil {
			return fmt.Errorf("capture owner private channel agent scope membership removal: %w", err)
		}
		*changed = append(*changed, captured...)
		if err := deleteOwnerPrivateWakeSubscriptions(); err != nil {
			return err
		}
		return retireScopeMembershipsByPKs(ctx, execer, captured, now)
	}
	if err := deleteOwnerPrivateWakeSubscriptions(); err != nil {
		return err
	}
	return markOwnerPrivateScopeMembershipsRemovedForHostChannel(ctx, execer, hostChannelID, ownerMemberID, now)
}

func (store *Store) deletePrivateAgentWakeSubscriptionsForHostChannel(ctx context.Context, execer coredb.QueryExecer, hostChannelID string, agentMemberID string) error {
	trimmedHostChannelID := strings.TrimSpace(hostChannelID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if trimmedHostChannelID == "" || trimmedAgentMemberID == "" {
		return nil
	}
	if _, err := execer.Exec(ctx, `
DELETE sns
FROM scope_notification_settings sns
JOIN scopes scope
  ON scope.id = sns.scope_id
WHERE sns.member_id = $2
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
`, trimmedHostChannelID, trimmedAgentMemberID); err != nil {
		return fmt.Errorf("delete private agent wake subscriptions: %w", err)
	}
	return nil
}

func (store *Store) RemoveChannelPrivateAgentForUser(ctx context.Context, userID string, workspaceID string, channelID string, agentID string) error {
	channelAccess, err := store.loadAuthorizedHumanChannelAccess(ctx, store.db, userID, workspaceID, channelID, channelReadAction)
	if err != nil {
		return err
	}
	if channelAccess.Channel.Type != "channel" {
		return badRequest("thread channels do not have direct members")
	}
	if channelAccess.Channel.Slug == "all" {
		return badRequest("cannot remove members from #all")
	}

	agent, err := store.loadAgentEntryOwner(ctx, store.db, workspaceID, agentID)
	if err != nil {
		return err
	}
	if strings.TrimSpace(agent.OwnerMemberID) != strings.TrimSpace(channelAccess.WorkspaceMember.Member.ID) {
		return forbidden("only the agent owner can remove it as private")
	}

	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		now := time.Now().UTC()
		entry, ok, err := store.loadActiveChannelAgentEntry(ctx, tx, channelID, agent.AgentMemberID)
		if err != nil {
			return err
		}
		if !ok || strings.TrimSpace(entry.Visibility) != channelAgentEntryVisibilityPrivate || strings.TrimSpace(entry.OwnerMemberID.String) != strings.TrimSpace(agent.OwnerMemberID) {
			return ErrNotFound
		}
		if err := store.deletePrivateAgentWakeSubscriptionsForHostChannel(ctx, tx, channelID, agent.AgentMemberID); err != nil {
			return err
		}
		if err := markPrivateAgentScopeMembershipRemoved(ctx, tx, channelID, agent.AgentMemberID, agent.OwnerMemberID, now, nil); err != nil {
			return err
		}
		return nil
	}); err != nil {
		return err
	}
	return nil
}

func markPrivateAgentScopeMembershipRemoved(ctx context.Context, execer coredb.QueryExecer, channelID string, agentMemberID string, ownerMemberID string, removedAt time.Time, changed *[]scopeMembershipRow) error {
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	trimmedOwnerMemberID := strings.TrimSpace(ownerMemberID)
	if trimmedChannelID == "" || trimmedAgentMemberID == "" || trimmedOwnerMemberID == "" {
		return nil
	}
	if changed != nil {
		captured, err := queryScopeMembershipRows(ctx, execer, `
SELECT scope_id, member_id, effective_from_seq
FROM scope_memberships
WHERE scope_id IN (
    SELECT id
    FROM scopes
    WHERE id = $1 OR parent_scope_id = $1
  )
  AND member_id = $2
  AND owner_member_id = $3
  AND visibility = 'private'
  AND removed_at IS NULL
FOR UPDATE
`, trimmedChannelID, trimmedAgentMemberID, trimmedOwnerMemberID)
		if err != nil {
			return fmt.Errorf("capture private channel agent scope membership removal: %w", err)
		}
		*changed = append(*changed, captured...)
		return retireScopeMembershipsByPKs(ctx, execer, captured, removedAt.UTC())
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $4,
    updated_at = $4
WHERE scope_id IN (
    SELECT id
    FROM scopes
    WHERE id = $1 OR parent_scope_id = $1
  )
  AND member_id = $2
  AND owner_member_id = $3
  AND visibility = 'private'
  AND removed_at IS NULL
`, trimmedChannelID, trimmedAgentMemberID, trimmedOwnerMemberID, removedAt.UTC()); err != nil {
		return fmt.Errorf("remove private channel agent scope membership: %w", err)
	}
	return nil
}

// markOwnerPrivateScopeMembershipsRemovedForHostChannel is the broad retirement
// used by the browser (effects==nil) path of removePrivateEntriesForOwnerHostChannel.
// The narrow (effects) path is inlined in the caller so the membership capture/lock
// can precede the notification DELETE.
func markOwnerPrivateScopeMembershipsRemovedForHostChannel(ctx context.Context, execer coredb.QueryExecer, hostChannelID string, ownerMemberID string, removedAt time.Time) error {
	trimmedHostChannelID := strings.TrimSpace(hostChannelID)
	trimmedOwnerMemberID := strings.TrimSpace(ownerMemberID)
	if trimmedHostChannelID == "" || trimmedOwnerMemberID == "" {
		return nil
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships sm
SET sm.removed_at = $3,
    sm.updated_at = $3,
    sm.unread_generation = sm.unread_generation + 1
WHERE sm.scope_id IN (
    SELECT id
    FROM scopes
    WHERE id = $1 OR parent_scope_id = $1
  )
  AND sm.owner_member_id = $2
  AND sm.visibility = 'private'
  AND sm.removed_at IS NULL
`, trimmedHostChannelID, trimmedOwnerMemberID, removedAt.UTC()); err != nil {
		return fmt.Errorf("remove owner private channel agent scope memberships: %w", err)
	}
	return nil
}

func (store *Store) markMemberScopeProjectionsRemoved(ctx context.Context, execer coredb.QueryExecer, memberID string, removedAt time.Time) error {
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedMemberID == "" {
		return nil
	}
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM scope_notification_settings
WHERE member_id = $1
`, trimmedMemberID); err != nil {
		return fmt.Errorf("delete member scope notification settings: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $2,
    updated_at = $2
WHERE member_id = $1 AND removed_at IS NULL
`, trimmedMemberID, now); err != nil {
		return fmt.Errorf("mark member scope memberships removed: %w", err)
	}
	return nil
}