package serviceaccount

import (
	"context"
	"crypto/sha256"
	"encoding/hex"
	"fmt"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

type serviceAccountFixture struct {
	UserID                string
	WorkspaceID           string
	HumanMemberID         string
	ServiceMemberID       string
	ServiceInstallationID string
	ServiceProjectionID   string
	ScopeID               string
}

func TestServiceAccountStoreMaterializedAccountAndManagers(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startServiceAccountRollbackDB(t, ctx)
	now := time.Now().UTC().Truncate(time.Second)
	fixture := seedServiceAccountFixture(t, db, "lifecycle", "News Room", "sacct_lifecycle", now)
	store := New(db)

	var inserted Account
	err := coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		platformID, err := txStore.LockNameNamespaceAt(ctx, " News Room ", now)
		if err != nil {
			return err
		}
		require.Empty(t, platformID)

		inserted, err = txStore.InsertAccount(ctx, AccountInsert{Account: Account{
			ID:                    "sacct_lifecycle",
			PrincipalID:           "sacct_lifecycle",
			WorkspaceID:           fixture.WorkspaceID,
			ManagementKind:        ManagementKindWorkspace,
			ServiceInstallationID: fixture.ServiceInstallationID,
			ServiceMemberID:       fixture.ServiceProjectionID,
			AuthorMemberID:        fixture.ServiceMemberID,
			MessageScopeID:        fixture.ScopeID,
			Name:                  "News Room",
			Description:           "Workspace news",
			CreatedByMemberID:     fixture.HumanMemberID,
			CreatedAt:             now,
			UpdatedAt:             now,
		}})
		if err != nil {
			return err
		}
		if err := txStore.InsertWorkspaceNameClaim(ctx, "sancl_lifecycle", fixture.WorkspaceID, inserted.Name, inserted.ID, now); err != nil {
			return err
		}
		if _, err := txStore.UpsertManager(ctx, ManagerUpsert{
			ID:                 "samanager_lifecycle",
			WorkspaceID:        fixture.WorkspaceID,
			ServiceAccountID:   inserted.ID,
			MemberID:           fixture.HumanMemberID,
			GrantedByMemberID:  fixture.HumanMemberID,
			GrantedByActorType: "human",
			GrantedByActorID:   fixture.HumanMemberID,
			Now:                now,
		}); err != nil {
			return err
		}
		inserted, err = txStore.MarkAudienceReady(ctx, fixture.WorkspaceID, inserted.ID, inserted.Version, inserted.AudiencePolicyVersion, now)
		if err != nil {
			return err
		}
		return txStore.InsertAuditEvent(ctx, AuditEvent{
			ID:                  "saaud_lifecycle",
			WorkspaceID:         fixture.WorkspaceID,
			ServiceAccountID:    inserted.ID,
			EventType:           "service_account.created",
			SubjectType:         ServiceKind,
			SubjectID:           inserted.ID,
			SubjectVersion:      inserted.Version,
			ActorType:           "human",
			ActorID:             fixture.HumanMemberID,
			PermissionBasisKind: "workspace_role",
			PermissionBasisRef:  "owner",
			Metadata:            []byte(`{"authorizationPath":"direct_create"}`),
			CreatedAt:           now,
		})
	})
	require.NoError(t, err)

	loaded, err := store.LoadAccount(ctx, fixture.WorkspaceID, inserted.ID)
	require.NoError(t, err)
	require.Equal(t, AudienceStateReady, loaded.AudienceState)
	require.Equal(t, loaded.AudiencePolicyVersion, loaded.EffectiveAudienceVersion)
	require.Equal(t, fixture.ServiceMemberID, loaded.AuthorMemberID)

	byScope, err := store.LoadAccountByScope(ctx, fixture.WorkspaceID, fixture.ScopeID)
	require.NoError(t, err)
	require.Equal(t, loaded.ID, byScope.ID)

	accounts, err := store.ListAccounts(ctx, fixture.WorkspaceID)
	require.NoError(t, err)
	require.Len(t, accounts, 1)
	accountPage, err := store.ListAccountPage(ctx, fixture.WorkspaceID, "", "workspace news", 100)
	require.NoError(t, err)
	require.Len(t, accountPage, 1)
	require.Equal(t, inserted.ID, accountPage[0].ID)

	managers, err := store.ListManagers(ctx, fixture.WorkspaceID, loaded.ID, true)
	require.NoError(t, err)
	require.Len(t, managers, 1)
	hasManager, err := store.HasActiveManager(ctx, fixture.WorkspaceID, loaded.ID, fixture.HumanMemberID)
	require.NoError(t, err)
	require.True(t, hasManager)
	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		replayed, err := store.InTx(tx).UpsertManager(ctx, ManagerUpsert{
			ID:                 "samanager_lifecycle_replay",
			WorkspaceID:        fixture.WorkspaceID,
			ServiceAccountID:   loaded.ID,
			MemberID:           fixture.HumanMemberID,
			GrantedByMemberID:  fixture.HumanMemberID,
			GrantedByActorType: "human",
			GrantedByActorID:   fixture.HumanMemberID,
			Now:                now.Add(time.Second),
		})
		require.NoError(t, err)
		require.Equal(t, "samanager_lifecycle", replayed.ID)
		return nil
	}))

	events, err := store.ListAuditEvents(ctx, fixture.WorkspaceID, loaded.ID, 20)
	require.NoError(t, err)
	require.Len(t, events, 1)
	require.Equal(t, "service_account.created", events[0].EventType)
	require.JSONEq(t, `{"authorizationPath":"direct_create"}`, string(events[0].Metadata))
}

func TestEnsureHumanAudienceMembershipsInTxAddsNewMemberAtCurrentTailWithRetainedHistory(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startServiceAccountRollbackDB(t, ctx)
	now := time.Date(2026, 7, 13, 7, 30, 0, 0, time.UTC)
	fixture := seedServiceAccountFixture(t, db, "late_join", "Release Notes", "sacct_late_join", now)
	store := New(db)
	account := insertFixtureAccount(t, ctx, db, store, fixture, "Release Notes", "sacct_late_join", "", now)

	messageStore := message.NewScopeRepositories(db)
	for sequence, content := range []string{"First article", "Second article"} {
		seq := int64(sequence + 1)
		require.NoError(t, messageStore.Messages.Insert(ctx, message.ScopeMessageRecord{
			ID:                 fmt.Sprintf("msg_sa_late_join_%d", seq),
			WorkspaceID:        fixture.WorkspaceID,
			ScopeID:            fixture.ScopeID,
			Seq:                seq,
			WorkspaceGlobalSeq: seq + 10,
			AuthorMemberID:     fixture.ServiceMemberID,
			AuthorType:         "service",
			MessageType:        "text",
			Content:            content,
			Metadata:           []byte(`{}`),
			CreatedAt:          now.Add(time.Duration(seq) * time.Minute),
			UpdatedAt:          now.Add(time.Duration(seq) * time.Minute),
		}))
	}
	mustExec(t, db, `
UPDATE scopes
SET latest_seq = 2, latest_global_seq = 12, updated_at = $2
WHERE id = $1
`, fixture.ScopeID, now.Add(2*time.Minute))

	lateUserID := "usr_sa_late_join_reader"
	lateMemberID := "mem_sa_late_join_reader"
	mustExec(t, db, `
INSERT INTO users (id, email, name, display_name, password_hash, created_at, updated_at)
VALUES ($1, 'late-reader@example.com', 'late-reader', 'Late Reader', 'hash', $2, $2)
`, lateUserID, now.Add(3*time.Minute))
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, user_id, workspace_role, status, created_at, updated_at)
VALUES ($1, $2, 'human', 'Late Reader', $3, 'member', 'active', $4, $4)
`, lateMemberID, fixture.WorkspaceID, lateUserID, now.Add(3*time.Minute))

	for attempt := 0; attempt < 2; attempt++ {
		require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
			return store.EnsureHumanAudienceMembershipsInTx(
				ctx,
				tx,
				fixture.WorkspaceID,
				lateMemberID,
				now.Add(time.Duration(4+attempt)*time.Minute),
			)
		}))
	}

	var lastReadSeq, lastReadGlobalSeq, effectiveFromSeq, openMemberships int64
	require.NoError(t, db.QueryRow(ctx, `
SELECT MAX(last_read_seq), MAX(last_read_global_seq), MAX(effective_from_seq), COUNT(*)
FROM scope_memberships
WHERE scope_id = $1
  AND member_id = $2
  AND effective_to_seq IS NULL
  AND removed_at IS NULL
  AND archived_at IS NULL
`, fixture.ScopeID, lateMemberID).Scan(&lastReadSeq, &lastReadGlobalSeq, &effectiveFromSeq, &openMemberships))
	require.Equal(t, int64(2), lastReadSeq)
	require.Equal(t, int64(12), lastReadGlobalSeq)
	require.Equal(t, int64(3), effectiveFromSeq)
	require.Equal(t, int64(1), openMemberships, "member lifecycle replay must not duplicate an open audience interval")

	var notificationState string
	require.NoError(t, db.QueryRow(ctx, `
SELECT state
FROM scope_notification_settings
WHERE scope_id = $1 AND member_id = $2
`, fixture.ScopeID, lateMemberID).Scan(&notificationState))
	require.Equal(t, "active", notificationState)

	history, err := message.NewScopeAPIService(message.New(db, nil, 0, message.Options{})).ListHistory(ctx, message.ScopeAPIHistoryRequest{
		WorkspaceID:    fixture.WorkspaceID,
		ScopeID:        account.MessageScopeID,
		ViewerMemberID: lateMemberID,
		ViewerType:     message.ScopeActorHuman,
		RequestKind:    message.ScopeVisibilityRequestHistory,
		Limit:          10,
	})
	require.NoError(t, err)
	require.Len(t, history.Messages, 2, "a newly joined Human can browse retained service-account history")
}

func TestServiceAccountCreateRequestIdempotencyAndVersionFence(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startServiceAccountRollbackDB(t, ctx)
	now := time.Now().UTC().Truncate(time.Second)
	fixture := seedServiceAccountFixture(t, db, "request", "request", "sacct_request", now)
	store := New(db)
	payload := []byte(`{"name":"Release Notes"}`)
	digestBytes := sha256.Sum256(payload)
	digest := hex.EncodeToString(digestBytes[:])

	insert := func(tx coredb.Tx, requestID string, candidate []byte, candidateDigest string) (CreateRequest, error) {
		return store.InTx(tx).InsertCreateRequest(ctx, CreateRequestInsert{
			ID:                  requestID,
			WorkspaceID:         fixture.WorkspaceID,
			RequestedByMemberID: fixture.HumanMemberID,
			ClientRequestID:     "browser-mutation-1",
			PayloadSnapshot:     candidate,
			PayloadDigest:       candidateDigest,
			AuthorizationPath:   AuthorizationPathApprovalRequired,
			Now:                 now,
		})
	}

	var request CreateRequest
	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		var err error
		request, err = insert(tx, "sacreq_request", payload, digest)
		return err
	}))
	require.Equal(t, RequestStateAwaitingAuthorization, request.BusinessState)

	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		replayed, err := insert(tx, "sacreq_ignored", payload, digest)
		require.NoError(t, err)
		require.Equal(t, request.ID, replayed.ID)
		return nil
	}))

	otherPayload := []byte(`{"name":"Different"}`)
	otherDigestBytes := sha256.Sum256(otherPayload)
	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		_, err := insert(tx, "sacreq_conflict", otherPayload, hex.EncodeToString(otherDigestBytes[:]))
		return err
	}), ErrConflict)

	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		transitioned, err := store.InTx(tx).TransitionCreateRequest(ctx, fixture.WorkspaceID, request.ID, request.Version, RequestStateAuthorized, "appr_request", 1, "", now.Add(time.Second))
		require.NoError(t, err)
		require.Equal(t, RequestStateAuthorized, transitioned.BusinessState)
		require.Equal(t, int64(2), transitioned.Version)
		return nil
	}))

	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		_, err := store.InTx(tx).TransitionCreateRequest(ctx, fixture.WorkspaceID, request.ID, request.Version, RequestStateDeclined, "appr_request", 2, "", now.Add(2*time.Second))
		return err
	}), ErrConflict)

	requests, err := store.ListCreateRequests(ctx, fixture.WorkspaceID, fixture.HumanMemberID, RequestStateAuthorized, 20)
	require.NoError(t, err)
	require.Len(t, requests, 1)
}

func TestServiceAccountPlatformNameNamespaceAndWorkspaceBinding(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startServiceAccountRollbackDB(t, ctx)
	now := time.Now().UTC().Truncate(time.Second)
	fixture := seedServiceAccountFixture(t, db, "platform", "AgentTeam Official", "psacct_platform", now)
	store := New(db)

	var platform PlatformAccount
	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		claimedBy, err := txStore.LockNameNamespaceAt(ctx, "AgentTeam Official", now)
		if err != nil {
			return err
		}
		require.Empty(t, claimedBy)
		platform, err = txStore.InsertPlatformAccount(ctx, PlatformAccount{
			ID:                      "psacct_platform",
			Name:                    "AgentTeam Official",
			Description:             "Deployment announcements",
			CreatedByOperatorUserID: fixture.UserID,
			CreatedAt:               now,
			UpdatedAt:               now,
		})
		return err
	}))
	mustExec(t, db, `
UPDATE service_installations
SET created_by_member_id = NULL,
    created_by_actor_type = 'platform_operator',
    created_by_actor_id = $2
WHERE id = $1
`, fixture.ServiceInstallationID, fixture.UserID)

	loaded, err := store.LoadPlatformAccount(ctx, platform.ID)
	require.NoError(t, err)
	require.Equal(t, "AgentTeam Official", loaded.Name)

	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		claimedBy, err := store.InTx(tx).LockNameNamespaceAt(ctx, " agentteam official ", now)
		require.NoError(t, err)
		require.Equal(t, platform.ID, claimedBy)
		return nil
	}))

	local := insertFixtureAccount(t, ctx, db, store, fixture, "AgentTeam Official", "sacct_platform_local", platform.ID, now)
	var binding PlatformWorkspaceInstance
	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		var err error
		binding, err = store.InTx(tx).InsertPlatformWorkspaceInstance(ctx, PlatformWorkspaceInstance{
			ID:                           "psawsi_platform",
			PlatformServiceAccountID:     platform.ID,
			WorkspaceID:                  fixture.WorkspaceID,
			LocalServiceAccountID:        local.ID,
			OpenedProvisioningGeneration: 1,
			EffectiveFromBroadcastSeq:    1,
			AppliedProfileVersion:        platform.ProfileVersion,
			ProvisioningState:            "ready",
			CreatedAt:                    now,
			UpdatedAt:                    now,
		})
		return err
	}))
	require.Equal(t, platform.ID, binding.PlatformServiceAccountID)
	require.Equal(t, local.ID, binding.LocalServiceAccountID)
}

func TestServiceAccountStoreRejectsCrossWorkspaceSubstrate(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startServiceAccountRollbackDB(t, ctx)
	now := time.Now().UTC().Truncate(time.Second)
	first := seedServiceAccountFixture(t, db, "tenant_first", "first-news", "sacct_tenant_first", now)
	second := seedServiceAccountFixture(t, db, "tenant_second", "second-news", "sacct_tenant_second", now)
	store := New(db)

	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		if _, err := txStore.LockNameNamespaceAt(ctx, "cross-tenant", now); err != nil {
			return err
		}
		_, err := txStore.InsertAccount(ctx, AccountInsert{Account: Account{
			ID:                    "sacct_cross_tenant",
			PrincipalID:           "sacct_cross_tenant",
			WorkspaceID:           first.WorkspaceID,
			ManagementKind:        ManagementKindWorkspace,
			ServiceInstallationID: second.ServiceInstallationID,
			ServiceMemberID:       second.ServiceProjectionID,
			AuthorMemberID:        second.ServiceMemberID,
			MessageScopeID:        second.ScopeID,
			Name:                  "Cross Tenant",
			CreatedByMemberID:     first.HumanMemberID,
			CreatedAt:             now,
			UpdatedAt:             now,
		}})
		return err
	}), ErrConflict)

	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		if _, err := txStore.LockNameNamespaceAt(ctx, "first-news", now); err != nil {
			return err
		}
		_, err := txStore.InsertAccount(ctx, AccountInsert{Account: Account{
			ID:                      "sacct_tenant_first",
			PrincipalID:             "sacct_tenant_first",
			WorkspaceID:             first.WorkspaceID,
			ManagementKind:          ManagementKindWorkspace,
			ServiceInstallationID:   first.ServiceInstallationID,
			ServiceMemberID:         first.ServiceProjectionID,
			AuthorMemberID:          first.ServiceMemberID,
			MessageScopeID:          first.ScopeID,
			Name:                    "Invalid Dual Creator",
			CreatedByMemberID:       first.HumanMemberID,
			CreatedByOperatorUserID: first.UserID,
			CreatedAt:               now,
			UpdatedAt:               now,
		}})
		return err
	}), ErrBadRequest)

	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		if _, err := txStore.LockNameNamespaceAt(ctx, "first-news", now); err != nil {
			return err
		}
		_, err := txStore.InsertAccount(ctx, AccountInsert{Account: Account{
			ID:                    "sacct_identity_mismatch",
			PrincipalID:           "sacct_identity_mismatch",
			WorkspaceID:           first.WorkspaceID,
			ManagementKind:        ManagementKindWorkspace,
			ServiceInstallationID: first.ServiceInstallationID,
			ServiceMemberID:       first.ServiceProjectionID,
			AuthorMemberID:        first.ServiceMemberID,
			MessageScopeID:        first.ScopeID,
			Name:                  "Identity Mismatch",
			CreatedByMemberID:     first.HumanMemberID,
			CreatedAt:             now,
			UpdatedAt:             now,
		}})
		return err
	}), ErrConflict)

	payload := []byte(`{"name":"Cross Tenant"}`)
	digestBytes := sha256.Sum256(payload)
	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		_, err := store.InTx(tx).InsertCreateRequest(ctx, CreateRequestInsert{
			ID:                  "sacreq_cross_tenant",
			WorkspaceID:         first.WorkspaceID,
			RequestedByMemberID: second.HumanMemberID,
			ClientRequestID:     "cross-tenant",
			PayloadSnapshot:     payload,
			PayloadDigest:       hex.EncodeToString(digestBytes[:]),
			AuthorizationPath:   AuthorizationPathApprovalRequired,
			Now:                 now,
		})
		return err
	}), ErrConflict)

	account := insertFixtureAccount(t, ctx, db, store, first, "first-news", "sacct_tenant_first", "", now)
	require.ErrorIs(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		_, err := store.InTx(tx).UpsertManager(ctx, ManagerUpsert{
			ID:                 "samanager_cross_tenant",
			WorkspaceID:        first.WorkspaceID,
			ServiceAccountID:   account.ID,
			MemberID:           second.HumanMemberID,
			GrantedByMemberID:  first.HumanMemberID,
			GrantedByActorType: "human",
			GrantedByActorID:   first.HumanMemberID,
			Now:                now,
		})
		return err
	}), ErrConflict)
}

func TestNormalizeServiceAccountName(t *testing.T) {
	t.Parallel()

	for input, want := range map[string]string{
		"News":           "News",
		" Release News ": "Release News",
		"服务号":            "服务号",
	} {
		got, err := NormalizeName(input)
		require.NoError(t, err)
		require.Equal(t, want, got)
	}
	for _, input := range []string{"", "news\nroom", strings.Repeat("名", 81)} {
		_, err := NormalizeName(input)
		require.ErrorIs(t, err, ErrBadRequest)
	}
}

func insertFixtureAccount(t *testing.T, ctx context.Context, db coredb.DB, store *Store, fixture serviceAccountFixture, name string, accountID string, platformID string, now time.Time) Account {
	t.Helper()
	var account Account
	require.NoError(t, coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		claimedBy, err := txStore.LockNameNamespaceAt(ctx, name, now)
		if err != nil {
			return err
		}
		managementKind := ManagementKindWorkspace
		principalID := accountID
		createdByMemberID := fixture.HumanMemberID
		createdByOperatorUserID := ""
		if platformID != "" {
			managementKind = ManagementKindPlatform
			principalID = platformID
			createdByMemberID = ""
			createdByOperatorUserID = fixture.UserID
			require.Equal(t, platformID, claimedBy)
		} else {
			require.Empty(t, claimedBy)
		}
		account, err = txStore.InsertAccount(ctx, AccountInsert{Account: Account{
			ID:                       accountID,
			PrincipalID:              principalID,
			WorkspaceID:              fixture.WorkspaceID,
			ManagementKind:           managementKind,
			PlatformServiceAccountID: platformID,
			ServiceInstallationID:    fixture.ServiceInstallationID,
			ServiceMemberID:          fixture.ServiceProjectionID,
			AuthorMemberID:           fixture.ServiceMemberID,
			MessageScopeID:           fixture.ScopeID,
			Name:                     name,
			CreatedByMemberID:        createdByMemberID,
			CreatedByOperatorUserID:  createdByOperatorUserID,
			CreatedAt:                now,
			UpdatedAt:                now,
		}})
		if err != nil {
			return err
		}
		if platformID != "" {
			return nil
		}
		return txStore.InsertWorkspaceNameClaim(ctx, "sancl_"+accountID, fixture.WorkspaceID, name, accountID, now)
	}))
	return account
}

func seedServiceAccountFixture(t testing.TB, db coredb.QueryExecer, suffix string, name string, principalID string, now time.Time) serviceAccountFixture {
	t.Helper()
	fixture := serviceAccountFixture{
		UserID:                "usr_sa_" + suffix,
		WorkspaceID:           "wsp_sa_" + suffix,
		HumanMemberID:         "mem_sa_human_" + suffix,
		ServiceMemberID:       "mem_sa_service_" + suffix,
		ServiceInstallationID: "svcinst_sa_" + suffix,
		ServiceProjectionID:   "svcmem_sa_" + suffix,
		ScopeID:               "sasc_" + suffix,
	}
	orgID := "org_sa_" + suffix
	mustExec(t, db, `
INSERT INTO users (id, email, name, display_name, password_hash, created_at, updated_at)
VALUES ($1, $2, $3, $3, 'hash', $4, $4)
`, fixture.UserID, suffix+"@example.com", suffix, now)
	mustExec(t, db, `
INSERT INTO organizations (id, slug, name, created_by_user_id, created_at, updated_at)
VALUES ($1, $2, $2, $3, $4, $4)
`, orgID, suffix, fixture.UserID, now)
	mustExec(t, db, `
INSERT INTO workspaces (id, organization_id, slug, name, created_at, updated_at)
VALUES ($1, $2, $3, $3, $4, $4)
`, fixture.WorkspaceID, orgID, suffix, now)
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, user_id, workspace_role, status, created_at, updated_at)
VALUES ($1, $2, 'human', 'Human', $3, 'owner', 'active', $4, $4)
`, fixture.HumanMemberID, fixture.WorkspaceID, fixture.UserID, now)
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, workspace_role, status, created_at, updated_at)
VALUES ($1, $2, 'service', 'Service Account', NULL, 'active', $3, $3)
`, fixture.ServiceMemberID, fixture.WorkspaceID, now)
	mustExec(t, db, `
INSERT INTO scopes (id, workspace_id, type, identity_key, status, created_by_member_id, created_at, updated_at)
VALUES ($1, $2, 'service_account', $3, 'active', $4, $5, $5)
`, fixture.ScopeID, fixture.WorkspaceID, "service_account:"+fixture.WorkspaceID+":"+principalID, fixture.HumanMemberID, now)
	mustExec(t, db, `
INSERT INTO service_installations (
    id, workspace_id, service_definition_id, service_kind, instance_key,
    created_by_member_id, created_by_actor_type, created_by_actor_id,
    created_at, updated_at
) VALUES ($1, $2, $3, 'service_account', $4, $5, 'human', $5, $6, $6)
`, fixture.ServiceInstallationID, fixture.WorkspaceID, ServiceDefinitionID, principalID, fixture.HumanMemberID, now)
	mustExec(t, db, `
INSERT INTO service_members (
    id, workspace_id, service_definition_id, service_installation_id, member_id,
    service_kind, handle, display_name, status, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, 'service_account', $6, $6, 'active', $7, $7)
`, fixture.ServiceProjectionID, fixture.WorkspaceID, ServiceDefinitionID, fixture.ServiceInstallationID, fixture.ServiceMemberID, name, now)
	return fixture
}