package serviceaccount

import (
	"context"
	"encoding/json"
	"fmt"
	"os"
	"strings"
	"sync"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/coredb/testkit"
	"github.com/tidbcloud/AgentTeam/internal/store/approval"
	"github.com/tidbcloud/AgentTeam/internal/store/attachment"
	serviceaccountstore "github.com/tidbcloud/AgentTeam/internal/store/serviceaccount"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
	"github.com/tidbcloud/AgentTeam/internal/store/testfixture"
)

var materializerTiDB testkit.TiDBCoreDBFixture

func TestMain(m *testing.M) {
	os.Exit(testkit.RunTestsWithTiDBCoreDBFixture(m, "serviceaccountflow", &materializerTiDB))
}

func TestPrepareWorkspaceMaterializationAddsCreatorAsFirstManager(t *testing.T) {
	for _, test := range []struct {
		name               string
		managerMemberIDs   []string
		wantManagerMembers []string
	}{
		{
			name:               "all managers omitted",
			wantManagerMembers: []string{"mem_z_creator"},
		},
		{
			name:               "creator omitted from additional managers",
			managerMemberIDs:   []string{"mem_a_manager"},
			wantManagerMembers: []string{"mem_z_creator", "mem_a_manager"},
		},
	} {
		t.Run(test.name, func(t *testing.T) {
			prepared, err := prepareWorkspaceMaterialization(WorkspaceMaterialization{
				Identity: IdentityRefs{
					AccountID: "sac_creator_manager_boundary",
				},
				WorkspaceID:      "ws_creator_manager_boundary",
				CreatorMemberID:  "mem_z_creator",
				Name:             "Creator Manager Boundary",
				ManagerMemberIDs: test.managerMemberIDs,
			})
			require.NoError(t, err)
			require.Equal(t, test.wantManagerMembers, prepared.ManagerMemberIDs)
			require.Equal(t, prepared.ManagerMemberIDs, prepared.WorkspaceMaterialization.ManagerMemberIDs)
			require.NotNil(t, prepared.Receipt.Workspace)
			require.Equal(t, prepared.ManagerMemberIDs, prepared.Receipt.Workspace.ManagerMemberIDs)
		})
	}
}

func TestMaterializeWorkspaceCreatesServiceSubstrateAndReplays(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 10, 8, 0, 0, 0, time.UTC)
	seedMaterializerWorkspace(t, ctx, db, "ws_materialize", now, map[string]string{
		"mem_admin":    "active",
		"mem_member":   "active",
		"mem_reader":   "active",
		"mem_inactive": "inactive",
	})

	input := WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID:             "sac_workspace_news",
			ServiceInstallationID: "svcinst_workspace_news",
			ServiceMemberID:       "svcmem_workspace_news",
			AuthorMemberID:        "svcnews00001",
			MessageScopeID:        "sasc_workspace_news",
			NameClaimID:           "sancl_workspace_news",
		},
		WorkspaceID:       "ws_materialize",
		CreatorMemberID:   "mem_admin",
		Name:              "News Desk",
		Description:       "Workspace announcements",
		AvatarAssetID:     "asset_news",
		AvatarURL:         "https://assets.example/news.png",
		ManagerMemberIDs:  []string{"mem_admin", "mem_member"},
		AuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		CorrelationID:     "corr_workspace_news",
		Now:               now,
	}

	result, err := New(db).MaterializeWorkspace(ctx, input)
	require.NoError(t, err)
	require.True(t, result.Created)
	require.Equal(t, "sac_workspace_news", result.Account.ID)
	require.Equal(t, "sac_workspace_news", result.Account.PrincipalID)
	require.Equal(t, "News Desk", result.Account.Name)
	require.Equal(t, serviceaccountstore.AudienceStatePending, result.Account.AudienceState)
	require.EqualValues(t, 0, result.Account.EffectiveAudienceVersion)
	require.EqualValues(t, 1, result.Account.Version)

	var memberType, memberDisplayName string
	require.NoError(t, db.QueryRow(ctx, `SELECT type, display_name FROM members WHERE id = $1`, result.Account.AuthorMemberID).Scan(&memberType, &memberDisplayName))
	require.Equal(t, "service", memberType)
	require.Equal(t, "News Desk", memberDisplayName)

	var installationInstance, installationKind, installationCreatorType, installationCreatorID string
	require.NoError(t, db.QueryRow(ctx, `
SELECT instance_key, service_kind, created_by_actor_type, created_by_actor_id
FROM service_installations
WHERE id = $1
`, result.Account.ServiceInstallationID).Scan(&installationInstance, &installationKind, &installationCreatorType, &installationCreatorID))
	require.Equal(t, result.Account.PrincipalID, installationInstance)
	require.Equal(t, serviceactor.KindServiceAccount, installationKind)
	require.Equal(t, "human", installationCreatorType)
	require.Equal(t, "mem_admin", installationCreatorID)

	var serviceMemberRecordID, serviceMemberMemberID, serviceMemberNameProjection, serviceMemberAvatar string
	require.NoError(t, db.QueryRow(ctx, `
SELECT id, member_id, handle, avatar_url
FROM service_members
WHERE service_installation_id = $1
`, result.Account.ServiceInstallationID).Scan(&serviceMemberRecordID, &serviceMemberMemberID, &serviceMemberNameProjection, &serviceMemberAvatar))
	require.Equal(t, result.Account.ServiceMemberID, serviceMemberRecordID)
	require.Equal(t, result.Account.AuthorMemberID, serviceMemberMemberID)
	require.Equal(t, "News Desk", serviceMemberNameProjection)
	require.Equal(t, "https://assets.example/news.png", serviceMemberAvatar)

	var scopeType, scopeIdentityKey string
	require.NoError(t, db.QueryRow(ctx, `SELECT type, identity_key FROM scopes WHERE id = $1`, result.Account.MessageScopeID).Scan(&scopeType, &scopeIdentityKey))
	require.Equal(t, "service_account", scopeType)
	require.Equal(t, "service_account:ws_materialize:sac_workspace_news", scopeIdentityKey)

	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_grants WHERE service_installation_id = $1 AND scope = 'message.write' AND revoked_at IS NULL`, result.Account.ServiceInstallationID))
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_target_bindings WHERE target_kind = 'service_account' AND target_ref = $1 AND status = 'active'`, serviceactor.ServiceAccountTargetRef(result.Account.ID)))
	require.Equal(t, int64(2), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_managers WHERE service_account_id = $1 AND status = 'active'`, result.Account.ID))
	require.Equal(t, int64(2), countRows(t, ctx, db, `SELECT COUNT(*) FROM scope_memberships WHERE scope_id = $1 AND effective_to_seq IS NULL AND removed_at IS NULL`, result.Account.MessageScopeID), "only the bounded initial manager membership baseline is synchronous")
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM scope_memberships WHERE scope_id = $1 AND member_id = 'mem_reader'`, result.Account.MessageScopeID), "the rest of the all-Human audience remains pending for the reconciler")
	require.Equal(t, int64(2), countRows(t, ctx, db, `SELECT COUNT(*) FROM scope_notification_settings WHERE scope_id = $1 AND state = 'active'`, result.Account.MessageScopeID), "initial managers receive the ordinary notification baseline")
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM scope_memberships WHERE scope_id = $1 AND member_id = 'mem_inactive'`, result.Account.MessageScopeID))

	var auditActorType, auditActorID, auditCauseType, auditCauseID string
	require.NoError(t, db.QueryRow(ctx, `
SELECT actor_type, actor_id, caused_by_actor_type, caused_by_actor_id
FROM service_account_audit_events
WHERE service_account_id = $1 AND event_type = 'service_account.created'
`, result.Account.ID).Scan(&auditActorType, &auditActorID, &auditCauseType, &auditCauseID))
	require.Equal(t, "service", auditActorType)
	require.Equal(t, result.Account.PrincipalID, auditActorID)
	require.Equal(t, "human", auditCauseType)
	require.Equal(t, "mem_admin", auditCauseID)

	var encodedMetadata []byte
	require.NoError(t, db.QueryRow(ctx, `
SELECT metadata
FROM service_account_audit_events
WHERE service_account_id = $1 AND event_type = 'service_account.created'
`, result.Account.ID).Scan(&encodedMetadata))
	var metadata createdAuditMetadata
	require.NoError(t, json.Unmarshal(encodedMetadata, &metadata))
	require.Equal(t, receiptDigestAlgorithm, metadata.Materialization.DigestAlgorithm)
	require.NotEmpty(t, metadata.Materialization.Digest)
	require.Equal(t, serviceaccountstore.ManagementKindWorkspace, metadata.Materialization.Receipt.Kind)
	require.Equal(t, reconcileStatePending, metadata.ReconcileIntent.Audience.State)
	storedDigest, err := digestMaterializationReceipt(metadata.Materialization.Receipt)
	require.NoError(t, err)
	require.Equal(t, storedDigest, metadata.Materialization.Digest)

	// Mutable profile and manager projections may evolve after creation. Replaying
	// the original logical input still resolves the resolved current account.
	_, err = db.Exec(ctx, `
UPDATE service_accounts
SET name = 'Renamed News Desk', description = 'Updated later', version = version + 1, updated_at = $1
WHERE id = $2
`, now.Add(time.Minute), result.Account.ID)
	require.NoError(t, err)
	_, err = db.Exec(ctx, `
UPDATE service_account_managers
SET status = 'ended', ended_at = $1, updated_at = $1, version = version + 1
WHERE service_account_id = $2 AND member_id = 'mem_member'
`, now.Add(time.Minute), result.Account.ID)
	require.NoError(t, err)

	retry := input
	retry.CorrelationID = "corr_retry_is_not_logical_input"
	retry.Now = now.Add(10 * time.Minute)
	replay, err := New(db).MaterializeWorkspace(ctx, retry)
	require.NoError(t, err)
	require.False(t, replay.Created)
	require.Equal(t, result.Account.ID, replay.Account.ID)
	require.Equal(t, "Renamed News Desk", replay.Account.Name)
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE id = $1`, result.Account.ID))
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM members WHERE workspace_id = $1 AND type = 'service'`, input.WorkspaceID))
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_audit_events WHERE service_account_id = $1 AND event_type = 'service_account.created'`, result.Account.ID))

	for name, mutate := range map[string]func(*WorkspaceMaterialization){
		"avatar URL": func(value *WorkspaceMaterialization) {
			value.AvatarURL = "https://assets.example/other.png"
		},
		"authorization path": func(value *WorkspaceMaterialization) {
			value.AuthorizationPath = serviceaccountstore.AuthorizationPathApprovalRequired
		},
		"explicit identity ref": func(value *WorkspaceMaterialization) {
			value.Identity.ServiceMemberID = "svcmem_different"
		},
		"initial managers": func(value *WorkspaceMaterialization) {
			value.ManagerMemberIDs = []string{"mem_admin"}
		},
	} {
		t.Run("conflicts on changed "+name, func(t *testing.T) {
			conflicting := input
			mutate(&conflicting)
			_, conflictErr := New(db).MaterializeWorkspace(ctx, conflicting)
			require.ErrorIs(t, conflictErr, serviceaccountstore.ErrConflict)
		})
	}
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM members WHERE workspace_id = $1 AND type = 'service'`, input.WorkspaceID))
}

func TestUpdateWorkspaceAccountAvatarUpdatesResolvedAuthorProjection(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 12, 2, 30, 0, 0, time.UTC)
	workspaceID := "ws_avatar_update"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":  "active",
		"mem_reader": "active",
	})
	_, err := db.Exec(ctx, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	require.NoError(t, err)

	service := New(db)
	created, err := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID: "sac_avatar", ServiceInstallationID: "svcinst_avatar",
			ServiceMemberID: "svcmem_avatar", AuthorMemberID: "mem_service_avatar",
			MessageScopeID: "sasc_avatar", NameClaimID: "sancl_avatar",
		},
		WorkspaceID: workspaceID, CreatorMemberID: "mem_admin",
		Name: "Avatar News", ManagerMemberIDs: []string{"mem_admin"},
		AuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		Now:               now,
	})
	require.NoError(t, err)
	avatarAssetID, err := attachment.New(db, nil, nil, 0, attachment.Options{}).UploadResourceAvatar(
		ctx, workspaceID, "service_account", created.Account.ID,
		attachment.CreateAttachmentParams{
			Filename: "service-account.png", ContentType: "image/png", Blob: []byte("service-account-avatar"),
		},
	)
	require.NoError(t, err)

	updated, err := service.UpdateWorkspaceAccountAvatarForUser(
		ctx,
		"usr_"+workspaceID+"_mem_admin",
		workspaceID,
		created.Account.ID,
		avatarAssetID,
		now.Add(time.Minute),
	)
	require.NoError(t, err)
	require.Equal(t, avatarAssetID, updated.AvatarAssetID)
	require.Equal(
		t,
		attachment.AvatarAssetURL(avatarAssetID),
		updated.AvatarURL,
	)
	require.EqualValues(t, created.Account.Version+1, updated.Version)

	var projectedAvatar string
	require.NoError(t, db.QueryRow(ctx, `
SELECT avatar_url
FROM service_members
WHERE id = $1
`, updated.ServiceMemberID).Scan(&projectedAvatar))
	require.Equal(t, updated.AvatarURL, projectedAvatar)
	require.Equal(t, int64(1), countRows(t, ctx, db, `
SELECT COUNT(*)
FROM service_account_audit_events
WHERE service_account_id = $1
  AND event_type = 'service_account.avatar_updated'
`, updated.ID))
	var permissionBasisKind, permissionBasisRef string
	require.NoError(t, db.QueryRow(ctx, `
SELECT permission_basis_kind, permission_basis_ref
FROM service_account_audit_events
WHERE service_account_id = $1
  AND event_type = 'service_account.avatar_updated'
`, updated.ID).Scan(&permissionBasisKind, &permissionBasisRef))
	require.Equal(t, "workspace_role", permissionBasisKind)
	require.Equal(t, "admin", permissionBasisRef)

	_, err = service.UpdateWorkspaceAccountAvatarForUser(
		ctx,
		"usr_"+workspaceID+"_mem_reader",
		workspaceID,
		created.Account.ID,
		"avt_forbidden",
		now.Add(2*time.Minute),
	)
	require.ErrorIs(t, err, storeerr.ErrForbidden)

	_, err = service.UpdateWorkspaceAccountAvatarForUser(
		ctx,
		"usr_"+workspaceID+"_mem_admin",
		workspaceID,
		created.Account.ID,
		"avt_missing",
		now.Add(3*time.Minute),
	)
	require.ErrorIs(t, err, storeerr.ErrBadRequest)
}

func TestSubmitWorkspaceCreateRequestDirectAndApprovalPaths(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 11, 1, 0, 0, 0, time.UTC)
	workspaceID := "ws_create_request"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":   "active",
		"mem_admin_2": "active",
		"mem_member":  "active",
	})
	_, err := db.Exec(ctx, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	require.NoError(t, err)

	service := New(db)
	directInput := SubmitWorkspaceCreateRequest{
		WorkspaceID:               workspaceID,
		RequesterUserID:           "usr_" + workspaceID + "_mem_admin",
		ClientRequestID:           "client-direct",
		ExpectedAuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		Payload: WorkspaceCreatePayload{
			Name: "Admin News",
		},
		Now: now,
	}
	direct, err := service.SubmitWorkspaceCreateRequest(ctx, directInput)
	require.NoError(t, err)
	require.True(t, direct.Created)
	require.Equal(t, serviceaccountstore.AuthorizationPathDirectCreate, direct.Request.AuthorizationPath)
	require.Equal(t, serviceaccountstore.RequestStateProvisioning, direct.Request.BusinessState)
	require.NotNil(t, direct.Account)
	require.Equal(t, direct.Account.ID, direct.Request.CreatedServiceAccountID)
	require.Nil(t, direct.Approval)

	directReplay, err := service.SubmitWorkspaceCreateRequest(ctx, directInput)
	require.NoError(t, err)
	require.False(t, directReplay.Created)
	require.Equal(t, direct.Request.ID, directReplay.Request.ID)
	require.NotNil(t, directReplay.Account)
	require.Equal(t, direct.Account.ID, directReplay.Account.ID)
	require.Equal(t, int64(1), countRows(t, ctx, db, `
SELECT COUNT(*) FROM service_account_create_requests
WHERE workspace_id = $1 AND requested_by_member_id = 'mem_admin' AND client_request_id = 'client-direct'
`, workspaceID))

	// Replays use the authorization path committed with the immutable request,
	// not the requester's current role.
	_, err = db.Exec(ctx, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin_2' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	require.NoError(t, err)
	directAfterRoleChange, err := service.SubmitWorkspaceCreateRequest(ctx, directInput)
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.AuthorizationPathDirectCreate, directAfterRoleChange.Request.AuthorizationPath)
	require.Equal(t, direct.Account.ID, directAfterRoleChange.Account.ID)

	_, err = db.Exec(ctx, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	require.NoError(t, err)

	directIntentFromMember := SubmitWorkspaceCreateRequest{
		WorkspaceID:               workspaceID,
		RequesterUserID:           "usr_" + workspaceID + "_mem_member",
		ClientRequestID:           "client-stale-direct-intent",
		ExpectedAuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		Payload: WorkspaceCreatePayload{
			Name: "Must Not Open Approval",
		},
		Now: now.Add(time.Second),
	}
	_, err = service.SubmitWorkspaceCreateRequest(ctx, directIntentFromMember)
	require.ErrorIs(t, err, storeerr.ErrForbidden)
	require.Equal(t, int64(0), countRows(t, ctx, db, `
SELECT COUNT(*) FROM service_account_create_requests
WHERE workspace_id = $1 AND requested_by_member_id = 'mem_member' AND client_request_id = 'client-stale-direct-intent'
`, workspaceID))
	require.Equal(t, int64(0), countRows(t, ctx, db, `
SELECT COUNT(*) FROM approval_instances
WHERE workspace_id = $1 AND subject_type = 'service_account_create_request'
`, workspaceID))

	approvalIntentFromAdmin := SubmitWorkspaceCreateRequest{
		WorkspaceID:               workspaceID,
		RequesterUserID:           "usr_" + workspaceID + "_mem_admin",
		ClientRequestID:           "client-stale-approval-intent",
		ExpectedAuthorizationPath: serviceaccountstore.AuthorizationPathApprovalRequired,
		Payload: WorkspaceCreatePayload{
			Name: "Must Stay Direct",
		},
		Now: now.Add(time.Second),
	}
	_, err = service.SubmitWorkspaceCreateRequest(ctx, approvalIntentFromAdmin)
	require.ErrorIs(t, err, storeerr.ErrConflict)
	require.Equal(t, int64(0), countRows(t, ctx, db, `
SELECT COUNT(*) FROM service_account_create_requests
WHERE workspace_id = $1 AND requested_by_member_id = 'mem_admin' AND client_request_id = 'client-stale-approval-intent'
`, workspaceID))

	approvalInput := SubmitWorkspaceCreateRequest{
		WorkspaceID:               workspaceID,
		RequesterUserID:           "usr_" + workspaceID + "_mem_member",
		ClientRequestID:           "client-approval",
		ExpectedAuthorizationPath: serviceaccountstore.AuthorizationPathApprovalRequired,
		Payload: WorkspaceCreatePayload{
			Name: "Member News",
		},
		Now: now.Add(time.Second),
	}
	pending, err := service.SubmitWorkspaceCreateRequest(ctx, approvalInput)
	require.NoError(t, err)
	require.True(t, pending.Created)
	require.Equal(t, serviceaccountstore.AuthorizationPathApprovalRequired, pending.Request.AuthorizationPath)
	require.Equal(t, serviceaccountstore.RequestStateAwaitingAuthorization, pending.Request.BusinessState)
	require.NotNil(t, pending.Approval)
	require.Equal(t, pending.Approval.ID, pending.Request.ApprovalInstanceID)
	require.Equal(t, pending.Request.ID, pending.Approval.SubjectRef)
	require.Equal(t, pending.Request.PayloadDigest, pending.Approval.PayloadDigest)
	require.Len(t, pending.Assignments, 1)
	require.Equal(t, "mem_admin", pending.Assignments[0].ApproverMemberID)
	require.Nil(t, pending.Account)

	pendingReplay, err := service.SubmitWorkspaceCreateRequest(ctx, approvalInput)
	require.NoError(t, err)
	require.False(t, pendingReplay.Created)
	require.Equal(t, pending.Request.ID, pendingReplay.Request.ID)
	require.NotNil(t, pendingReplay.Approval)
	require.Equal(t, pending.Approval.ID, pendingReplay.Approval.ID)
	require.Equal(t, pending.Assignments[0].ID, pendingReplay.Assignments[0].ID)

	// Once the Approval is bound, replay returns its original assignment facts
	// even if the live workspace admin set has changed.
	_, err = db.Exec(ctx, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin_2' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	require.NoError(t, err)
	pendingAfterApproverChange, err := service.SubmitWorkspaceCreateRequest(ctx, approvalInput)
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.AuthorizationPathApprovalRequired, pendingAfterApproverChange.Request.AuthorizationPath)
	require.Len(t, pendingAfterApproverChange.Assignments, 1)
	require.Equal(t, "mem_admin", pendingAfterApproverChange.Assignments[0].ApproverMemberID)

	conflict := approvalInput
	conflict.Payload.Name = "Different"
	_, err = service.SubmitWorkspaceCreateRequest(ctx, conflict)
	require.ErrorIs(t, err, serviceaccountstore.ErrConflict)

	_, err = db.Exec(ctx, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	require.NoError(t, err)

	decision, err := approval.New(db).Decide(ctx, approval.DecideParams{
		WorkspaceID:      workspaceID,
		ApprovalID:       pending.Approval.ID,
		AssignmentID:     pending.Assignments[0].ID,
		ActorMemberID:    "mem_admin",
		ExpectedVersion:  pending.Approval.Version,
		ClientDecisionID: "approve-member-news",
		Decision:         approval.DecisionApproved,
		Now:              now.Add(2 * time.Second),
	})
	require.NoError(t, err)
	approved, err := service.ConsumeWorkspaceCreateApproval(ctx, ConsumeApprovalDecision{
		WorkspaceID:     workspaceID,
		RequestID:       pending.Request.ID,
		ApprovalID:      pending.Approval.ID,
		DecisionVersion: decision.Instance.Version,
		Now:             now.Add(3 * time.Second),
	})
	require.NoError(t, err)
	require.True(t, approved.Created)
	require.Equal(t, serviceaccountstore.RequestStateProvisioning, approved.Request.BusinessState)
	require.Equal(t, decision.Instance.Version, approved.Request.ConsumedDecisionVersion)
	require.NotNil(t, approved.Account)
	require.Equal(t, approved.Account.ID, approved.Request.CreatedServiceAccountID)

	approvedReplay, err := service.ConsumeWorkspaceCreateApproval(ctx, ConsumeApprovalDecision{
		WorkspaceID:     workspaceID,
		RequestID:       pending.Request.ID,
		ApprovalID:      pending.Approval.ID,
		DecisionVersion: decision.Instance.Version,
		Now:             now.Add(4 * time.Second),
	})
	require.NoError(t, err)
	require.False(t, approvedReplay.Created)
	require.Equal(t, approved.Account.ID, approvedReplay.Account.ID)
	ready, err := service.ReconcileAllWorkspaceHumans(
		ctx, workspaceID, approved.Account.ID,
		approved.Account.Version, approved.Account.AudiencePolicyVersion,
		now.Add(4*time.Second),
	)
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.AudienceStateReady, ready.AudienceState)
	require.Equal(t, ready.AudiencePolicyVersion, ready.EffectiveAudienceVersion)
	fulfilled, err := service.accounts.LoadCreateRequest(ctx, workspaceID, approved.Request.ID)
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.RequestStateFulfilled, fulfilled.BusinessState)
	require.NotNil(t, fulfilled.FulfilledAt)
	require.Equal(t, int64(3), countRows(t, ctx, db, `
SELECT COUNT(*) FROM scope_memberships
WHERE scope_id = $1 AND effective_to_seq IS NULL AND removed_at IS NULL
`, approved.Account.MessageScopeID))
	require.Equal(t, int64(1), countRows(t, ctx, db, `
SELECT COUNT(*) FROM scope_memberships
WHERE scope_id = $1 AND member_id = 'mem_admin'
  AND effective_from_seq = 1 AND last_read_seq = 0 AND last_read_global_seq = 0
`, approved.Account.MessageScopeID))

	rejectedSubmission := approvalInput
	rejectedSubmission.ClientRequestID = "client-rejected"
	rejectedSubmission.Payload.Name = "rejected-news"
	rejectedSubmission.Payload.Name = "Rejected News"
	rejected, err := service.SubmitWorkspaceCreateRequest(ctx, rejectedSubmission)
	require.NoError(t, err)
	rejectedDecision, err := approval.New(db).Decide(ctx, approval.DecideParams{
		WorkspaceID:      workspaceID,
		ApprovalID:       rejected.Approval.ID,
		AssignmentID:     rejected.Assignments[0].ID,
		ActorMemberID:    "mem_admin",
		ExpectedVersion:  rejected.Approval.Version,
		ClientDecisionID: "reject-news",
		Decision:         approval.DecisionRejected,
		Reason:           "not now",
		Now:              now.Add(5 * time.Second),
	})
	require.NoError(t, err)
	declined, err := service.ConsumeWorkspaceCreateApproval(ctx, ConsumeApprovalDecision{
		WorkspaceID:     workspaceID,
		RequestID:       rejected.Request.ID,
		ApprovalID:      rejected.Approval.ID,
		DecisionVersion: rejectedDecision.Instance.Version,
		Now:             now.Add(6 * time.Second),
	})
	require.NoError(t, err)
	require.True(t, declined.Created)
	require.Equal(t, serviceaccountstore.RequestStateDeclined, declined.Request.BusinessState)
	require.Empty(t, declined.Request.CreatedServiceAccountID)
	require.Nil(t, declined.Account)
}

func TestPromotedWorkspaceCreateRequestReplayCancelsObsoleteApprovalBeforeReturningConflict(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 16, 11, 0, 0, 0, time.UTC)
	workspaceID := "ws_promoted_create_replay"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":  "active",
		"mem_member": "active",
	})
	mustExec(t, db, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	service := New(db)
	input := SubmitWorkspaceCreateRequest{
		WorkspaceID: workspaceID, RequesterUserID: "usr_" + workspaceID + "_mem_member",
		ClientRequestID:           "promoted-replay",
		ExpectedAuthorizationPath: serviceaccountstore.AuthorizationPathApprovalRequired,
		Payload:                   WorkspaceCreatePayload{Name: "Promotion Must Not Materialize"},
		Now:                       now,
	}
	pending, err := service.SubmitWorkspaceCreateRequest(ctx, input)
	require.NoError(t, err)
	require.NotNil(t, pending.Approval)
	require.Len(t, pending.Assignments, 1)

	mustExec(t, db, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_member'`, workspaceID)
	_, err = service.SubmitWorkspaceCreateRequest(ctx, input)
	require.ErrorIs(t, err, storeerr.ErrConflict)

	cancelled, err := service.accounts.LoadCreateRequest(ctx, workspaceID, pending.Request.ID)
	require.NoError(t, err)
	require.True(t, IsPrivilegedWorkspaceCreateCancellation(cancelled))
	for name, mutate := range map[string]func(*serviceaccountstore.CreateRequest){
		"human client mutation": func(request *serviceaccountstore.CreateRequest) {
			request.CancelClientMutationID = "human-cancel"
		},
		"forged payload digest": func(request *serviceaccountstore.CreateRequest) {
			request.CancelPayloadDigest = strings.Repeat("0", 64)
		},
		"mismatched result version": func(request *serviceaccountstore.CreateRequest) {
			request.CancelResultVersion--
		},
	} {
		forged := cancelled
		mutate(&forged)
		require.False(t, IsPrivilegedWorkspaceCreateCancellation(forged), name)
	}
	require.Equal(t, serviceaccountstore.AuthorizationPathApprovalRequired, cancelled.AuthorizationPath)
	require.Empty(t, cancelled.CreatedServiceAccountID)
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE workspace_id = $1`, workspaceID))
	instance, err := approval.New(db).Load(ctx, workspaceID, pending.Approval.ID)
	require.NoError(t, err)
	require.Equal(t, approval.StateCancelled, instance.State)
	require.Equal(t, PrivilegedWorkspaceCreateCancellationReason, instance.DecisionReason)
	assignments, err := approval.New(db).ListAssignments(ctx, workspaceID, pending.Approval.ID)
	require.NoError(t, err)
	require.Len(t, assignments, 1)
	require.Equal(t, approval.AssignmentCancelled, assignments[0].Status)

	reconciled, err := service.ReconcilePrivilegedWorkspaceCreateRequests(ctx, workspaceID, 100, now.Add(time.Minute))
	require.NoError(t, err)
	require.Zero(t, reconciled, "already-cancelled role-promotion requests must be a reconciliation no-op")
}

func TestServiceAccountCatalogReadReconcilesPromotedCreateApproval(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 16, 11, 15, 0, 0, time.UTC)
	workspaceID := "ws_promoted_catalog_reconcile"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":  "active",
		"mem_member": "active",
	})
	mustExec(t, db, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	service := New(db)
	pending, err := service.SubmitWorkspaceCreateRequest(ctx, SubmitWorkspaceCreateRequest{
		WorkspaceID: workspaceID, RequesterUserID: "usr_" + workspaceID + "_mem_member",
		ClientRequestID: "catalog-reconcile",
		Payload:         WorkspaceCreatePayload{Name: "Catalog Reconcile"},
		Now:             now,
	})
	require.NoError(t, err)
	mustExec(t, db, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_member'`, workspaceID)

	catalog, err := service.ListAccountsForUser(
		ctx, "usr_"+workspaceID+"_mem_member", workspaceID, "", "", 20,
	)
	require.NoError(t, err)
	require.Empty(t, catalog)
	afterCatalog, err := service.accounts.LoadCreateRequest(ctx, workspaceID, pending.Request.ID)
	require.NoError(t, err)
	require.True(t, IsPrivilegedWorkspaceCreateCancellation(afterCatalog), "Service Account catalog reads must opportunistically settle promoted pending approvals")
}

func TestPromotedWorkspaceCreateApprovalTerminalDecisionIsPreservedButNeverMaterialized(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 16, 11, 30, 0, 0, time.UTC)
	workspaceID := "ws_promoted_terminal_approval"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":  "active",
		"mem_member": "active",
	})
	mustExec(t, db, `
UPDATE members
SET workspace_role = CASE WHEN id = 'mem_admin' THEN 'admin' ELSE 'member' END
WHERE workspace_id = $1
`, workspaceID)
	service := New(db)
	pending, err := service.SubmitWorkspaceCreateRequest(ctx, SubmitWorkspaceCreateRequest{
		WorkspaceID: workspaceID, RequesterUserID: "usr_" + workspaceID + "_mem_member",
		ClientRequestID: "terminal-before-promotion",
		Payload:         WorkspaceCreatePayload{Name: "Approved But Never Created"},
		Now:             now,
	})
	require.NoError(t, err)
	decision, err := approval.New(db).Decide(ctx, approval.DecideParams{
		WorkspaceID: workspaceID, ApprovalID: pending.Approval.ID,
		AssignmentID: pending.Assignments[0].ID, ActorMemberID: "mem_admin",
		ExpectedVersion: pending.Approval.Version, ClientDecisionID: "approve-before-promotion",
		Decision: approval.DecisionApproved, Now: now.Add(time.Second),
	})
	require.NoError(t, err)
	mustExec(t, db, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_member'`, workspaceID)

	consumed, err := service.ConsumeWorkspaceCreateApproval(ctx, ConsumeApprovalDecision{
		WorkspaceID: workspaceID, RequestID: pending.Request.ID,
		ApprovalID: pending.Approval.ID, DecisionVersion: decision.Instance.Version,
		Now: now.Add(2 * time.Second),
	})
	require.NoError(t, err)
	require.True(t, IsPrivilegedWorkspaceCreateCancellation(consumed.Request))
	require.Nil(t, consumed.Account)
	require.False(t, consumed.Created)
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE workspace_id = $1`, workspaceID))

	preserved, err := approval.New(db).Load(ctx, workspaceID, pending.Approval.ID)
	require.NoError(t, err)
	require.Equal(t, approval.StateApproved, preserved.State)
	require.Equal(t, decision.Instance.Version, preserved.Version)
	preservedAssignments, err := approval.New(db).ListAssignments(ctx, workspaceID, pending.Approval.ID)
	require.NoError(t, err)
	require.Len(t, preservedAssignments, 1)
	require.Equal(t, approval.AssignmentApproved, preservedAssignments[0].Status)
	require.Equal(t, int64(1), countRows(t, ctx, db, `
SELECT COUNT(*) FROM approval_decision_receipts
WHERE approval_instance_id = $1 AND decision = 'approved'
  AND result_version = $2 AND actor_member_id = 'mem_admin'
`, pending.Approval.ID, decision.Instance.Version))

	mustExec(t, db, `UPDATE members SET workspace_role = 'member' WHERE workspace_id = $1 AND id = 'mem_member'`, workspaceID)
	rejectedPending, err := service.SubmitWorkspaceCreateRequest(ctx, SubmitWorkspaceCreateRequest{
		WorkspaceID: workspaceID, RequesterUserID: "usr_" + workspaceID + "_mem_member",
		ClientRequestID: "rejected-before-promotion",
		Payload:         WorkspaceCreatePayload{Name: "Rejected But Never Resulted"},
		Now:             now.Add(3 * time.Second),
	})
	require.NoError(t, err)
	rejectedDecision, err := approval.New(db).Decide(ctx, approval.DecideParams{
		WorkspaceID: workspaceID, ApprovalID: rejectedPending.Approval.ID,
		AssignmentID: rejectedPending.Assignments[0].ID, ActorMemberID: "mem_admin",
		ExpectedVersion: rejectedPending.Approval.Version, ClientDecisionID: "reject-before-promotion",
		Decision: approval.DecisionRejected, Reason: "legacy rejection retained", Now: now.Add(4 * time.Second),
	})
	require.NoError(t, err)
	mustExec(t, db, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_member'`, workspaceID)
	rejectedConsumed, err := service.ConsumeWorkspaceCreateApproval(ctx, ConsumeApprovalDecision{
		WorkspaceID: workspaceID, RequestID: rejectedPending.Request.ID,
		ApprovalID: rejectedPending.Approval.ID, DecisionVersion: rejectedDecision.Instance.Version,
		Now: now.Add(5 * time.Second),
	})
	require.NoError(t, err)
	require.True(t, IsPrivilegedWorkspaceCreateCancellation(rejectedConsumed.Request))
	require.Nil(t, rejectedConsumed.Account)
	preservedRejected, err := approval.New(db).Load(ctx, workspaceID, rejectedPending.Approval.ID)
	require.NoError(t, err)
	require.Equal(t, approval.StateRejected, preservedRejected.State)
	require.Equal(t, approval.DecisionRejected, preservedRejected.Decision)
	require.Equal(t, "legacy rejection retained", preservedRejected.DecisionReason)
	require.Equal(t, "mem_admin", preservedRejected.DecidedByMemberID)
	require.Equal(t, rejectedDecision.Instance.Version, preservedRejected.Version)
	preservedRejectedAssignments, err := approval.New(db).ListAssignments(ctx, workspaceID, rejectedPending.Approval.ID)
	require.NoError(t, err)
	require.Len(t, preservedRejectedAssignments, 1)
	require.Equal(t, approval.AssignmentRejected, preservedRejectedAssignments[0].Status)
	require.Equal(t, int64(1), countRows(t, ctx, db, `
SELECT COUNT(*) FROM approval_decision_receipts
WHERE approval_instance_id = $1 AND decision = 'rejected'
  AND reason = 'legacy rejection retained' AND result_version = $2
`, rejectedPending.Approval.ID, rejectedDecision.Instance.Version))
}

func TestWorkspaceAudienceReconcilePagesAndClosesStaleMemberships(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 11, 4, 0, 0, 0, time.UTC)
	members := map[string]string{"mem_admin": "active"}
	for index := 0; index < 500; index++ {
		members[fmt.Sprintf("mem_reader_%03d", index)] = "active"
	}
	seedMaterializerWorkspace(t, ctx, db, "ws_audience_paged", now, members)

	service := New(db)
	created, err := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity:    IdentityRefs{AccountID: "sac_audience_paged"},
		WorkspaceID: "ws_audience_paged", CreatorMemberID: "mem_admin",
		Name:             "Audience Paged",
		ManagerMemberIDs: []string{"mem_admin"},
		Now:              now,
	})
	require.NoError(t, err)
	ready, err := service.ReconcileAllWorkspaceHumans(
		ctx, created.Account.WorkspaceID, created.Account.ID,
		created.Account.Version, created.Account.AudiencePolicyVersion, now.Add(time.Second),
	)
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.AudienceStateReady, ready.AudienceState)
	require.Equal(t, int64(501), countRows(t, ctx, db, `
SELECT COUNT(*) FROM scope_memberships
WHERE scope_id = $1 AND effective_to_seq IS NULL AND removed_at IS NULL
`, ready.MessageScopeID))

	_, err = db.Exec(ctx, `UPDATE members SET status = 'removed', updated_at = $1 WHERE id = 'mem_reader_250'`, now.Add(2*time.Second))
	require.NoError(t, err)
	replayed, err := service.ReconcileAllWorkspaceHumans(
		ctx, ready.WorkspaceID, ready.ID, ready.Version, ready.AudiencePolicyVersion, now.Add(3*time.Second),
	)
	require.NoError(t, err)
	require.Equal(t, ready.Version, replayed.Version, "an already-ready policy does not churn account version")
	require.Equal(t, int64(500), countRows(t, ctx, db, `
SELECT COUNT(*) FROM scope_memberships
WHERE scope_id = $1 AND effective_to_seq IS NULL AND removed_at IS NULL
`, ready.MessageScopeID))
	var effectiveTo, unreadGeneration int64
	var removedAt time.Time
	require.NoError(t, db.QueryRow(ctx, `
SELECT effective_to_seq, unread_generation, removed_at
FROM scope_memberships
WHERE scope_id = $1 AND member_id = 'mem_reader_250'
ORDER BY created_at DESC LIMIT 1
`, ready.MessageScopeID).Scan(&effectiveTo, &unreadGeneration, &removedAt))
	require.Equal(t, int64(0), effectiveTo)
	require.GreaterOrEqual(t, unreadGeneration, int64(1))
	require.Equal(t, now.Add(3*time.Second), removedAt.UTC())
}

func TestWorkspaceAudienceAlwaysIncludesEveryActiveHuman(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 11, 4, 30, 0, 0, time.UTC)
	seedMaterializerWorkspace(t, ctx, db, "ws_audience_selected", now, map[string]string{
		"mem_admin": "active", "mem_selected": "active", "mem_other": "active",
	})
	service := New(db)
	created, err := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity:    IdentityRefs{AccountID: "sac_audience_selected"},
		WorkspaceID: "ws_audience_selected", CreatorMemberID: "mem_admin",
		Name:             "Audience Selected",
		ManagerMemberIDs: []string{"mem_admin"},
		Now:              now,
	})
	require.NoError(t, err)
	ready, err := service.ReconcileAllWorkspaceHumans(
		ctx, created.Account.WorkspaceID, created.Account.ID,
		created.Account.Version, created.Account.AudiencePolicyVersion, now.Add(time.Second),
	)
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.AudienceStateReady, ready.AudienceState)
	require.Equal(t, int64(3), countRows(t, ctx, db, `
SELECT COUNT(*) FROM scope_memberships
WHERE scope_id = $1 AND effective_to_seq IS NULL AND removed_at IS NULL
`, ready.MessageScopeID))
	require.Equal(t, int64(1), countRows(t, ctx, db, `
SELECT COUNT(*) FROM scope_memberships WHERE scope_id = $1 AND member_id = 'mem_other'
`, ready.MessageScopeID))
}

func TestMaterializeWorkspaceReplayKeepsOmittedIdentityRefsStable(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 10, 8, 30, 0, 0, time.UTC)
	seedMaterializerWorkspace(t, ctx, db, "ws_generated_refs", now, map[string]string{"mem_admin": "active"})
	input := WorkspaceMaterialization{
		Identity:         IdentityRefs{AccountID: "sac_generated_refs"},
		WorkspaceID:      "ws_generated_refs",
		CreatorMemberID:  "mem_admin",
		Name:             "Generated Refs",
		ManagerMemberIDs: []string{"mem_admin"},
		Now:              now,
	}
	created, err := New(db).MaterializeWorkspace(ctx, input)
	require.NoError(t, err)
	require.True(t, created.Created)
	require.NotEmpty(t, created.Account.ServiceInstallationID)
	require.NotEmpty(t, created.Account.ServiceMemberID)
	require.NotEmpty(t, created.Account.AuthorMemberID)
	require.NotEmpty(t, created.Account.MessageScopeID)

	replay, err := New(db).MaterializeWorkspace(ctx, input)
	require.NoError(t, err)
	require.False(t, replay.Created)
	require.Equal(t, created.Account, replay.Account)
}

func TestMaterializeWorkspaceRollsBackEveryProjectionOnLateWriteFailure(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 10, 9, 0, 0, 0, time.UTC)
	seedMaterializerWorkspace(t, ctx, db, "ws_rollback", now, map[string]string{"mem_admin": "active"})

	baseline, err := New(db).MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID:   "sac_baseline",
			NameClaimID: "sancl_shared_collision",
		},
		WorkspaceID:      "ws_rollback",
		CreatorMemberID:  "mem_admin",
		Name:             "Baseline News",
		ManagerMemberIDs: []string{"mem_admin"},
		Now:              now,
	})
	require.NoError(t, err)
	require.True(t, baseline.Created)

	_, err = New(db).MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID:             "sac_rollback",
			ServiceInstallationID: "svcinst_rollback",
			ServiceMemberID:       "svcmem_rollback",
			AuthorMemberID:        "svcrollback1",
			MessageScopeID:        "sasc_rollback",
			NameClaimID:           "sancl_shared_collision",
		},
		WorkspaceID:      "ws_rollback",
		CreatorMemberID:  "mem_admin",
		Name:             "Rollback News",
		ManagerMemberIDs: []string{"mem_admin"},
		Now:              now,
	})
	require.Error(t, err)
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_name_namespaces WHERE normalized_name = 'rollback-news'`))
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE id = 'sac_rollback'`))
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM members WHERE id = 'svcrollback1'`))
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM scopes WHERE id = 'sasc_rollback'`))
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_installations WHERE id = 'svcinst_rollback'`))
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM members WHERE workspace_id = 'ws_rollback' AND type = 'service'`))
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM scopes WHERE workspace_id = 'ws_rollback' AND type = 'service_account'`))
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_installations WHERE workspace_id = 'ws_rollback' AND service_kind = 'service_account'`))
}

func TestMaterializePlatformInstanceCreatesPendingReconcileIntentAndReplays(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 10, 10, 0, 0, 0, time.UTC)
	operatorUserID := seedMaterializerWorkspace(t, ctx, db, "ws_platform", now, map[string]string{
		"mem_one": "active",
		"mem_two": "active",
	})
	insertPlatformAccount(t, ctx, db, serviceaccountstore.PlatformAccount{
		ID:                            "psa_agentteam_official",
		Name:                          "AgentTeam Official",
		Description:                   "Platform announcements",
		AvatarAssetID:                 "asset_official",
		ProfileVersion:                3,
		Version:                       1,
		ProvisioningMode:              "all_workspaces",
		ProvisioningState:             "pending",
		AppliedProvisioningGeneration: 0,
		AudiencePolicyVersion:         1,
		LatestBroadcastSeq:            7,
		CreatedByOperatorUserID:       operatorUserID,
		CreatedAt:                     now,
		UpdatedAt:                     now,
	})

	input := PlatformInstanceMaterialization{
		Identity: IdentityRefs{
			AccountID:             "sac_agentteam_official_ws_platform",
			ServiceInstallationID: "svcinst_agentteam_official_ws_platform",
			ServiceMemberID:       "svcmem_agentteam_official_ws_platform",
			AuthorMemberID:        "svcofficial1",
			MessageScopeID:        "sasc_agentteam_official_ws_platform",
			PlatformInstanceID:    "sapwi_agentteam_official_ws_platform",
		},
		WorkspaceID:                  "ws_platform",
		PlatformServiceAccountID:     "psa_agentteam_official",
		OperatorUserID:               operatorUserID,
		AvatarURL:                    "https://assets.example/official.png",
		OpenedProvisioningGeneration: 4,
		CorrelationID:                "corr_platform_instance",
		Now:                          now,
	}
	result, err := New(db).MaterializePlatformInstance(ctx, input)
	require.NoError(t, err)
	require.True(t, result.Created)
	require.Equal(t, input.Identity.AccountID, result.Account.ID)
	require.Equal(t, input.PlatformServiceAccountID, result.Account.PrincipalID)
	require.Equal(t, input.PlatformServiceAccountID, result.Account.PlatformServiceAccountID)
	require.EqualValues(t, 3, result.Account.AppliedPlatformProfileVersion)
	require.Equal(t, serviceaccountstore.AudienceStatePending, result.Account.AudienceState)
	require.EqualValues(t, 0, result.Account.EffectiveAudienceVersion)
	require.EqualValues(t, 1, result.Account.Version)
	require.Empty(t, result.Account.CreatedByMemberID)
	require.Equal(t, operatorUserID, result.Account.CreatedByOperatorUserID)

	var generation, effectiveFrom, lastApplied, profileVersion int64
	var provisioningState string
	require.NoError(t, db.QueryRow(ctx, `
SELECT opened_provisioning_generation, effective_from_broadcast_seq,
       last_applied_broadcast_seq, applied_profile_version, provisioning_state
FROM platform_service_account_workspace_instances
WHERE platform_service_account_id = $1 AND workspace_id = $2
`, input.PlatformServiceAccountID, input.WorkspaceID).Scan(&generation, &effectiveFrom, &lastApplied, &profileVersion, &provisioningState))
	require.EqualValues(t, 4, generation)
	require.EqualValues(t, 8, effectiveFrom)
	require.EqualValues(t, 7, lastApplied)
	require.EqualValues(t, 3, profileVersion)
	require.Equal(t, reconcileStatePending, provisioningState)

	var installationCreatorMember, installationCreatorType, installationCreatorID string
	require.NoError(t, db.QueryRow(ctx, `
SELECT COALESCE(created_by_member_id, ''), created_by_actor_type, created_by_actor_id
FROM service_installations
WHERE id = $1
`, result.Account.ServiceInstallationID).Scan(&installationCreatorMember, &installationCreatorType, &installationCreatorID))
	require.Empty(t, installationCreatorMember)
	require.Equal(t, "platform_operator", installationCreatorType)
	require.Equal(t, operatorUserID, installationCreatorID)

	var grantMemberID, bindingCreatorMemberID string
	require.NoError(t, db.QueryRow(ctx, `SELECT granted_by_member_id FROM service_grants WHERE service_installation_id = $1 AND scope = 'message.write' AND revoked_at IS NULL`, result.Account.ServiceInstallationID).Scan(&grantMemberID))
	require.NoError(t, db.QueryRow(ctx, `SELECT created_by_member_id FROM service_target_bindings WHERE service_installation_id = $1 AND status = 'active'`, result.Account.ServiceInstallationID).Scan(&bindingCreatorMemberID))
	require.Equal(t, result.Account.AuthorMemberID, grantMemberID)
	require.Equal(t, result.Account.AuthorMemberID, bindingCreatorMemberID)
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM scope_memberships WHERE scope_id = $1 AND removed_at IS NULL`, result.Account.MessageScopeID))
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_managers WHERE service_account_id = $1`, result.Account.ID))
	require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_name_claims WHERE service_account_id = $1`, result.Account.ID))

	var encodedMetadata []byte
	require.NoError(t, db.QueryRow(ctx, `
SELECT metadata
FROM service_account_audit_events
WHERE service_account_id = $1 AND event_type = 'service_account.created'
`, result.Account.ID).Scan(&encodedMetadata))
	var metadata createdAuditMetadata
	require.NoError(t, json.Unmarshal(encodedMetadata, &metadata))
	require.Equal(t, serviceaccountstore.ManagementKindPlatform, metadata.Materialization.Receipt.Kind)
	require.Equal(t, reconcileStatePending, metadata.ReconcileIntent.Audience.State)
	require.NotNil(t, metadata.ReconcileIntent.PlatformProvisioning)
	require.Equal(t, reconcileStatePending, metadata.ReconcileIntent.PlatformProvisioning.State)
	require.EqualValues(t, 4, metadata.ReconcileIntent.PlatformProvisioning.OpenedProvisioningGeneration)
	require.Equal(t, "sapwi_agentteam_official_ws_platform", metadata.ReconcileIntent.PlatformProvisioning.PlatformWorkspaceInstanceID)

	// Global and local profile projections are mutable after creation. They do
	// not change the immutable logical input accepted by the materializer.
	_, err = db.Exec(ctx, `
UPDATE platform_service_accounts
SET name = 'AgentTeam Official v2', description = 'Updated platform profile',
    avatar_asset_id = 'asset_official_v2', profile_version = 4, version = version + 1, updated_at = $1
WHERE id = $2
`, now.Add(time.Minute), input.PlatformServiceAccountID)
	require.NoError(t, err)
	_, err = db.Exec(ctx, `
UPDATE service_accounts
SET name = 'AgentTeam Official v2', description = 'Updated platform profile',
    avatar_asset_id = 'asset_official_v2', applied_platform_profile_version = 4,
    version = version + 1, updated_at = $1
WHERE id = $2
`, now.Add(time.Minute), result.Account.ID)
	require.NoError(t, err)

	retry := input
	retry.CorrelationID = "corr_retry_is_not_logical_input"
	retry.Now = now.Add(10 * time.Minute)
	replay, err := New(db).MaterializePlatformInstance(ctx, retry)
	require.NoError(t, err)
	require.False(t, replay.Created)
	require.Equal(t, result.Account.ID, replay.Account.ID)
	require.Equal(t, "AgentTeam Official v2", replay.Account.Name)
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM platform_service_account_workspace_instances WHERE platform_service_account_id = $1 AND workspace_id = $2`, input.PlatformServiceAccountID, input.WorkspaceID))

	for name, mutate := range map[string]func(*PlatformInstanceMaterialization){
		"provisioning generation": func(value *PlatformInstanceMaterialization) {
			value.OpenedProvisioningGeneration++
		},
		"avatar URL": func(value *PlatformInstanceMaterialization) {
			value.AvatarURL = "https://assets.example/official-other.png"
		},
		"platform binding": func(value *PlatformInstanceMaterialization) {
			value.PlatformServiceAccountID = "psa_other"
		},
		"platform instance identity": func(value *PlatformInstanceMaterialization) {
			value.Identity.PlatformInstanceID = "sapwi_other"
		},
	} {
		t.Run("conflicts on changed "+name, func(t *testing.T) {
			conflicting := input
			mutate(&conflicting)
			_, conflictErr := New(db).MaterializePlatformInstance(ctx, conflicting)
			require.ErrorIs(t, conflictErr, serviceaccountstore.ErrConflict)
		})
	}

	_, err = New(db).DeleteWorkspaceAccountForUser(
		ctx, "usr_ws_platform_mem_one", input.WorkspaceID,
		replay.Account.ID, replay.Account.Version, now.Add(20*time.Minute),
	)
	require.ErrorIs(t, err, storeerr.ErrForbidden)
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE id = $1`, replay.Account.ID))
}

func TestMaterializePlatformReplayKeepsOmittedIdentityRefsStable(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 10, 10, 30, 0, 0, time.UTC)
	operatorUserID := seedMaterializerWorkspace(t, ctx, db, "ws_platform_generated", now, map[string]string{"mem_one": "active"})
	insertPlatformAccount(t, ctx, db, serviceaccountstore.PlatformAccount{
		ID:                            "psa_generated_refs",
		Name:                          "Platform Generated",
		ProfileVersion:                1,
		Version:                       1,
		ProvisioningMode:              "all_workspaces",
		ProvisioningState:             reconcileStatePending,
		AppliedProvisioningGeneration: 0,
		AudiencePolicyVersion:         1,
		CreatedByOperatorUserID:       operatorUserID,
		CreatedAt:                     now,
		UpdatedAt:                     now,
	})
	input := PlatformInstanceMaterialization{
		Identity:                     IdentityRefs{AccountID: "sac_platform_generated_refs"},
		WorkspaceID:                  "ws_platform_generated",
		PlatformServiceAccountID:     "psa_generated_refs",
		OperatorUserID:               operatorUserID,
		OpenedProvisioningGeneration: 1,
		Now:                          now,
	}
	created, err := New(db).MaterializePlatformInstance(ctx, input)
	require.NoError(t, err)
	require.True(t, created.Created)

	replay, err := New(db).MaterializePlatformInstance(ctx, input)
	require.NoError(t, err)
	require.False(t, replay.Created)
	require.Equal(t, created.Account, replay.Account)
	require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM platform_service_account_workspace_instances WHERE local_service_account_id = $1`, created.Account.ID))
}

func TestCompleteIdentityRefsRejectsOversizedCallerRefs(t *testing.T) {
	for _, testCase := range []struct {
		name   string
		mutate func(*IdentityRefs)
	}{
		{name: "account", mutate: func(refs *IdentityRefs) { refs.AccountID = strings.Repeat("a", 65) }},
		{name: "installation", mutate: func(refs *IdentityRefs) { refs.ServiceInstallationID = strings.Repeat("i", 256) }},
		{name: "service member", mutate: func(refs *IdentityRefs) { refs.ServiceMemberID = strings.Repeat("s", 256) }},
		{name: "author member", mutate: func(refs *IdentityRefs) { refs.AuthorMemberID = strings.Repeat("m", 256) }},
		{name: "scope", mutate: func(refs *IdentityRefs) { refs.MessageScopeID = strings.Repeat("c", 256) }},
		{name: "name claim", mutate: func(refs *IdentityRefs) { refs.NameClaimID = strings.Repeat("n", 65) }},
		{name: "platform instance", mutate: func(refs *IdentityRefs) { refs.PlatformInstanceID = strings.Repeat("p", 65) }},
	} {
		t.Run(testCase.name, func(t *testing.T) {
			refs := IdentityRefs{AccountID: "sac_valid"}
			testCase.mutate(&refs)
			_, err := completeIdentityRefs(refs, true)
			require.ErrorIs(t, err, serviceaccountstore.ErrBadRequest)
		})
	}
}

type afterServiceAccountOuterMembershipReadDB struct {
	coredb.DB
	once      sync.Once
	afterRead func()
}

func (db *afterServiceAccountOuterMembershipReadDB) QueryRow(ctx context.Context, query string, args ...any) coredb.Row {
	row := db.DB.QueryRow(ctx, query, args...)
	if !strings.Contains(query, "SELECT id, display_name, user_id, workspace_role") || strings.Contains(query, "FOR UPDATE") {
		return row
	}
	return afterServiceAccountOuterMembershipReadRow{Row: row, afterRead: func() {
		db.once.Do(db.afterRead)
	}}
}

type afterServiceAccountOuterMembershipReadRow struct {
	coredb.Row
	afterRead func()
}

func (row afterServiceAccountOuterMembershipReadRow) Scan(dest ...any) error {
	err := row.Row.Scan(dest...)
	if err == nil && row.afterRead != nil {
		row.afterRead()
	}
	return err
}

func TestWorkspaceAccountMutationsRevalidateAdminRoleInTransaction(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 16, 8, 30, 0, 0, time.UTC)
	workspaceID := "ws_direct_mutation_role_revalidation"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_stale_admin": "active",
		"mem_manager":     "active",
		"mem_removable":   "active",
		"mem_target":      "active",
	})
	_, err := db.Exec(ctx, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_stale_admin'`, workspaceID)
	require.NoError(t, err)

	service := New(db)
	created, err := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID: "sac_role_revalidation", ServiceInstallationID: "svcinst_role_revalidation",
			ServiceMemberID: "svcmem_role_revalidation", AuthorMemberID: "mem_service_role_revalidation",
			MessageScopeID: "sasc_role_revalidation", NameClaimID: "sancl_role_revalidation",
		},
		WorkspaceID: workspaceID, CreatorMemberID: "mem_manager",
		Name: "Role Revalidation", ManagerMemberIDs: []string{"mem_manager", "mem_removable"},
		AuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		Now:               now,
	})
	require.NoError(t, err)
	managers, err := service.accounts.ListManagers(ctx, workspaceID, created.Account.ID, true)
	require.NoError(t, err)
	var removable serviceaccountstore.ManagerAssignment
	for _, manager := range managers {
		if manager.MemberID == "mem_removable" {
			removable = manager
		}
	}
	require.NotEmpty(t, removable.ID)

	staleAdminUserID := "usr_" + workspaceID + "_mem_stale_admin"
	guardedService := func() *Service {
		_, updateErr := db.Exec(ctx, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_stale_admin'`, workspaceID)
		require.NoError(t, updateErr)
		guardedDB := &afterServiceAccountOuterMembershipReadDB{DB: db, afterRead: func() {
			_, updateErr := db.Exec(ctx, `UPDATE members SET workspace_role = 'member' WHERE workspace_id = $1 AND id = 'mem_stale_admin'`, workspaceID)
			require.NoError(t, updateErr)
		}}
		return New(guardedDB)
	}

	t.Run("profile", func(t *testing.T) {
		guarded := guardedService()
		_, err := guarded.UpdateWorkspaceAccountAvatarForUser(ctx, staleAdminUserID, workspaceID, created.Account.ID, "", now.Add(time.Minute))
		require.ErrorIs(t, err, storeerr.ErrForbidden)
		persisted, loadErr := service.accounts.LoadAccount(ctx, workspaceID, created.Account.ID)
		require.NoError(t, loadErr)
		require.Equal(t, created.Account.Version, persisted.Version)
		require.Equal(t, int64(0), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_audit_events WHERE service_account_id = $1 AND event_type = 'service_account.avatar_updated'`, created.Account.ID))
	})

	t.Run("deletion", func(t *testing.T) {
		guarded := guardedService()
		_, err := guarded.DeleteWorkspaceAccountForUser(
			ctx, staleAdminUserID, workspaceID,
			created.Account.ID, created.Account.Version, now.Add(2*time.Minute),
		)
		require.ErrorIs(t, err, storeerr.ErrForbidden)
		require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE id = $1`, created.Account.ID))
	})

	t.Run("add manager", func(t *testing.T) {
		guarded := guardedService()
		_, err := guarded.AddManagerForUser(ctx, staleAdminUserID, workspaceID, created.Account.ID, "mem_target", now.Add(3*time.Minute))
		require.ErrorIs(t, err, storeerr.ErrForbidden)
		require.Equal(t, int64(0), countRows(t, ctx, db, `
SELECT COUNT(*) FROM service_account_managers
WHERE workspace_id = $1 AND service_account_id = $2 AND member_id = 'mem_target' AND status = 'active'
`, workspaceID, created.Account.ID))
	})

	t.Run("remove manager", func(t *testing.T) {
		guarded := guardedService()
		_, err := guarded.RemoveManagerForUser(ctx, staleAdminUserID, workspaceID, created.Account.ID, removable.ID, removable.Version, now.Add(4*time.Minute))
		require.ErrorIs(t, err, storeerr.ErrForbidden)
		require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_account_managers WHERE id = $1 AND status = 'active' AND ended_at IS NULL`, removable.ID))
	})
}

func TestWorkspaceDeleteAuthorityIsEqualForAdminCreatorAndActiveManager(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 21, 10, 0, 0, 0, time.UTC)
	workspaceID := "ws_direct_delete_authority"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":   "active",
		"mem_creator": "active",
		"mem_manager": "active",
		"mem_peer":    "active",
	})
	_, err := db.Exec(ctx, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_admin'`, workspaceID)
	require.NoError(t, err)
	service := New(db)

	createAccount := func(suffix string) serviceaccountstore.Account {
		created, createErr := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
			Identity: IdentityRefs{
				AccountID: "sac_direct_" + suffix, ServiceInstallationID: "svcinst_direct_" + suffix,
				ServiceMemberID: "svcmem_direct_" + suffix, AuthorMemberID: "svcauthor_direct_" + suffix,
				MessageScopeID: "sasc_direct_" + suffix, NameClaimID: "sancl_direct_" + suffix,
			},
			WorkspaceID: workspaceID, CreatorMemberID: "mem_creator",
			Name: "Direct " + suffix, ManagerMemberIDs: []string{"mem_manager"},
			AuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
			Now:               now,
		})
		require.NoError(t, createErr)
		return created.Account
	}
	assertDeletionAuditBasis := func(accountID, wantKind, wantRef string) {
		var gotKind, gotRef string
		require.NoError(t, db.QueryRow(ctx, `
SELECT permission_basis_kind, permission_basis_ref
FROM service_account_audit_events
WHERE subject_id = $1 AND event_type = 'service_account.deleted'
`, accountID).Scan(&gotKind, &gotRef))
		require.Equal(t, wantKind, gotKind)
		require.Equal(t, wantRef, gotRef)
	}

	t.Run("creator is an initial manager with the same direct delete authority", func(t *testing.T) {
		account := createAccount("creator_direct")
		creatorUserID := "usr_" + workspaceID + "_mem_creator"
		creatorAccess, accessErr := service.GetAccountForUser(ctx, creatorUserID, workspaceID, account.ID)
		require.NoError(t, accessErr)
		require.True(t, creatorAccess.CanManage)

		deleted, deleteErr := service.DeleteWorkspaceAccountForUser(
			ctx, creatorUserID, workspaceID, account.ID, account.Version, now.Add(time.Minute),
		)
		require.NoError(t, deleteErr)
		require.Equal(t, account.ID, deleted.ID)
		assertDeletionAuditBasis(account.ID, "service_account_manager", "mem_creator")
	})

	t.Run("creator loses authority when the initial manager assignment ends", func(t *testing.T) {
		account := createAccount("creator_removed")
		managerUserID := "usr_" + workspaceID + "_mem_manager"
		creatorUserID := "usr_" + workspaceID + "_mem_creator"
		managers, listErr := service.ListManagersForUser(ctx, managerUserID, workspaceID, account.ID)
		require.NoError(t, listErr)
		require.Len(t, managers, 2)
		var creatorManager serviceaccountstore.ManagerAssignment
		for _, manager := range managers {
			if manager.MemberID == "mem_creator" {
				creatorManager = manager
				break
			}
		}
		require.NotEmpty(t, creatorManager.ID)
		ended, removeErr := service.RemoveManagerForUser(
			ctx, managerUserID, workspaceID, account.ID,
			creatorManager.ID, creatorManager.Version, now.Add(2*time.Minute),
		)
		require.NoError(t, removeErr)
		require.Equal(t, serviceaccountstore.ManagerStatusEnded, ended.Status)

		creatorAccess, accessErr := service.GetAccountForUser(ctx, creatorUserID, workspaceID, account.ID)
		require.NoError(t, accessErr)
		require.False(t, creatorAccess.CanManage)
		_, deleteErr := service.DeleteWorkspaceAccountForUser(
			ctx, creatorUserID, workspaceID, account.ID, account.Version+73, now.Add(3*time.Minute),
		)
		require.ErrorIs(t, deleteErr, storeerr.ErrForbidden, "authority must be checked before the version fence")
		require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE id = $1`, account.ID))
	})

	t.Run("active manager deletes directly", func(t *testing.T) {
		account := createAccount("manager")
		managerUserID := "usr_" + workspaceID + "_mem_manager"
		managerAccess, accessErr := service.GetAccountForUser(ctx, managerUserID, workspaceID, account.ID)
		require.NoError(t, accessErr)
		require.True(t, managerAccess.CanManage)

		_, staleErr := service.DeleteWorkspaceAccountForUser(
			ctx, managerUserID, workspaceID, account.ID, account.Version+1, now.Add(4*time.Minute),
		)
		require.ErrorIs(t, staleErr, storeerr.ErrConflict)
		deleted, deleteErr := service.DeleteWorkspaceAccountForUser(
			ctx, managerUserID, workspaceID, account.ID, account.Version, now.Add(5*time.Minute),
		)
		require.NoError(t, deleteErr)
		require.Equal(t, account.ID, deleted.ID)
		assertDeletionAuditBasis(account.ID, "service_account_manager", "mem_manager")
	})

	t.Run("workspace admin deletes directly without a manager assignment", func(t *testing.T) {
		account := createAccount("admin")
		deleted, deleteErr := service.DeleteWorkspaceAccountForUser(
			ctx, "usr_"+workspaceID+"_mem_admin", workspaceID,
			account.ID, account.Version, now.Add(6*time.Minute),
		)
		require.NoError(t, deleteErr)
		require.Equal(t, account.ID, deleted.ID)
		assertDeletionAuditBasis(account.ID, "workspace_role", "admin")
	})

	t.Run("unrelated member fails closed without an approval fallback", func(t *testing.T) {
		account := createAccount("peer")
		_, deleteErr := service.DeleteWorkspaceAccountForUser(
			ctx, "usr_"+workspaceID+"_mem_peer", workspaceID,
			account.ID, account.Version+91, now.Add(7*time.Minute),
		)
		require.ErrorIs(t, deleteErr, storeerr.ErrForbidden)
		require.Equal(t, int64(1), countRows(t, ctx, db, `SELECT COUNT(*) FROM service_accounts WHERE id = $1`, account.ID))
	})
}

func TestWorkspaceAccountMutationUsesActiveManagerAuthority(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 16, 9, 0, 0, 0, time.UTC)
	workspaceID := "ws_active_manager_authority"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_manager": "active",
		"mem_other":   "active",
	})
	service := New(db)
	created, err := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID: "sac_manager_authority", ServiceInstallationID: "svcinst_manager_authority",
			ServiceMemberID: "svcmem_manager_authority", AuthorMemberID: "mem_service_manager_authority",
			MessageScopeID: "sasc_manager_authority", NameClaimID: "sancl_manager_authority",
		},
		WorkspaceID: workspaceID, CreatorMemberID: "mem_manager",
		Name: "Manager Authority", ManagerMemberIDs: []string{"mem_manager"},
		AuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		Now:               now,
	})
	require.NoError(t, err)

	updated, err := service.UpdateWorkspaceAccountAvatarForUser(
		ctx, "usr_"+workspaceID+"_mem_manager", workspaceID, created.Account.ID, "", now.Add(time.Minute),
	)
	require.NoError(t, err)
	require.Equal(t, created.Account.Version+1, updated.Version)
	var permissionBasisKind, permissionBasisRef string
	require.NoError(t, db.QueryRow(ctx, `
SELECT permission_basis_kind, permission_basis_ref
FROM service_account_audit_events
WHERE service_account_id = $1 AND event_type = 'service_account.avatar_updated'
`, created.Account.ID).Scan(&permissionBasisKind, &permissionBasisRef))
	require.Equal(t, "service_account_manager", permissionBasisKind)
	require.Equal(t, "mem_manager", permissionBasisRef)
}

func TestWorkspaceAdminImplicitlyManagesWorkspaceAccountWithoutManagerAssignment(t *testing.T) {
	ctx := context.Background()
	db := materializerTiDB.SerializedRollbackDB(t, ctx)
	now := time.Date(2026, 7, 21, 7, 0, 0, 0, time.UTC)
	workspaceID := "ws_implicit_admin_authority"
	seedMaterializerWorkspace(t, ctx, db, workspaceID, now, map[string]string{
		"mem_admin":   "active",
		"mem_manager": "active",
		"mem_target":  "active",
	})
	_, err := db.Exec(ctx, `UPDATE members SET workspace_role = 'admin' WHERE workspace_id = $1 AND id = 'mem_admin'`, workspaceID)
	require.NoError(t, err)

	service := New(db)
	created, err := service.MaterializeWorkspace(ctx, WorkspaceMaterialization{
		Identity: IdentityRefs{
			AccountID: "sac_implicit_admin", ServiceInstallationID: "svcinst_implicit_admin",
			ServiceMemberID: "svcmem_implicit_admin", AuthorMemberID: "mem_service_implicit_admin",
			MessageScopeID: "sasc_implicit_admin", NameClaimID: "sancl_implicit_admin",
		},
		WorkspaceID: workspaceID, CreatorMemberID: "mem_manager",
		Name: "Implicit Admin", ManagerMemberIDs: []string{"mem_manager"},
		AuthorizationPath: serviceaccountstore.AuthorizationPathDirectCreate,
		Now:               now,
	})
	require.NoError(t, err)
	ready, err := service.ReconcileAllWorkspaceHumans(
		ctx, workspaceID, created.Account.ID, created.Account.Version,
		created.Account.AudiencePolicyVersion, now.Add(time.Second),
	)
	require.NoError(t, err)

	adminUserID := "usr_" + workspaceID + "_mem_admin"
	accountAccess, err := service.GetAccountForUser(ctx, adminUserID, workspaceID, ready.ID)
	require.NoError(t, err)
	require.True(t, accountAccess.CanManage)
	require.True(t, accountAccess.CanPublish)
	require.True(t, accountAccess.CanModerate)
	require.Zero(t, countRows(t, ctx, db, `
SELECT COUNT(*) FROM service_account_managers
WHERE workspace_id = $1 AND service_account_id = $2 AND member_id = 'mem_admin'
`, workspaceID, ready.ID), "workspace admins must remain implicit instead of being materialized as managers")

	managers, err := service.ListManagersForUser(ctx, adminUserID, workspaceID, ready.ID)
	require.NoError(t, err)
	require.Len(t, managers, 1)
	require.Equal(t, "mem_manager", managers[0].MemberID)
	added, err := service.AddManagerForUser(ctx, adminUserID, workspaceID, ready.ID, "mem_target", now.Add(2*time.Second))
	require.NoError(t, err)
	require.Equal(t, "mem_target", added.MemberID)
	removed, err := service.RemoveManagerForUser(ctx, adminUserID, workspaceID, ready.ID, added.ID, added.Version, now.Add(3*time.Second))
	require.NoError(t, err)
	require.Equal(t, serviceaccountstore.ManagerStatusEnded, removed.Status)

	updated, err := service.UpdateWorkspaceAccountAvatarForUser(
		ctx, adminUserID, workspaceID, ready.ID, "", now.Add(4*time.Second),
	)
	require.NoError(t, err)
	var permissionBasisKind, permissionBasisRef string
	require.NoError(t, db.QueryRow(ctx, `
SELECT permission_basis_kind, permission_basis_ref
FROM service_account_audit_events
WHERE service_account_id = $1 AND event_type = 'service_account.avatar_updated'
`, ready.ID).Scan(&permissionBasisKind, &permissionBasisRef))
	require.Equal(t, "workspace_role", permissionBasisKind)
	require.Equal(t, "admin", permissionBasisRef)

	deleted, err := service.DeleteWorkspaceAccountForUser(
		ctx, adminUserID, workspaceID, updated.ID, updated.Version, now.Add(5*time.Second),
	)
	require.NoError(t, err)
	require.Equal(t, updated.ID, deleted.ID)
}

func seedMaterializerWorkspace(t testing.TB, ctx context.Context, db coredb.QueryExecer, workspaceID string, now time.Time, members map[string]string) string {
	t.Helper()
	organizationID := testfixture.SeedWorkspaceOrganization(t, ctx, db, workspaceID, now)
	_, err := db.Exec(ctx, `
INSERT INTO workspaces (id, organization_id, slug, name, latest_message_seq, created_at, updated_at)
VALUES ($1, $2, $1, $1, 0, $3, $3)
`, workspaceID, organizationID, now)
	require.NoError(t, err)
	for memberID, status := range members {
		userID := "usr_" + workspaceID + "_" + memberID
		_, err := db.Exec(ctx, `
INSERT INTO users (
    id, email, name, display_name, avatar_url, is_admin,
    password_hash, email_verified_at, created_at, updated_at
) VALUES ($1, $2, $3, $3, '', FALSE, 'hash', $4, $4, $4)
`, userID, userID+"@example.com", memberID, now)
		require.NoError(t, err)
		_, err = db.Exec(ctx, `
INSERT INTO members (
    id, workspace_id, type, display_name, user_id, workspace_role,
    status, created_at, updated_at
) VALUES ($1, $2, 'human', $1, $3, 'member', $4, $5, $5)
`, memberID, workspaceID, userID, status, now)
		require.NoError(t, err)
	}
	return "usr_owner_" + workspaceID
}

func insertPlatformAccount(t testing.TB, ctx context.Context, db coredb.DB, account serviceaccountstore.PlatformAccount) {
	t.Helper()
	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		store := serviceaccountstore.New(db).InTx(tx)
		if _, err := store.LockNameNamespaceAt(ctx, account.Name, account.CreatedAt); err != nil {
			return err
		}
		_, err := store.InsertPlatformAccount(ctx, account)
		return err
	}))
}

func countRows(t testing.TB, ctx context.Context, queryer coredb.QueryRower, query string, args ...any) int64 {
	t.Helper()
	var count int64
	require.NoError(t, queryer.QueryRow(ctx, query, args...).Scan(&count))
	return count
}

func mustExec(t testing.TB, execer coredb.QueryExecer, query string, args ...any) {
	t.Helper()
	_, err := execer.Exec(context.Background(), query, args...)
	require.NoError(t, err)
}