package serviceactor

import (
	"context"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	"github.com/tidbcloud/AgentTeam/internal/core/runeffect"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
)

type serviceActorFixture struct {
	UserID          string
	WorkspaceID     string
	HumanMemberID   string
	ServiceMemberID string
	ScopeID         string
}

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

	ctx := context.Background()
	db := startServiceActorRollbackDB(t, ctx)
	fixture := seedServiceActorFixture(t, db, "read_backfilled_app", time.Now().UTC())

	store := New(db)
	definition, err := store.LoadDefinitionBySlug(ctx, KindApp, "github")
	require.NoError(t, err)
	require.Equal(t, "app_github", definition.ID)
	require.Equal(t, InstallationModeSingleton, definition.InstallationMode)

	installation, err := store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_read_backfilled_app",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: definition.ID,
		ServiceKind:         KindApp,
		Status:              StatusActive,
		InstalledVersion:    "1.0.0",
		Config:              []byte(`{"mode":"test"}`),
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.NoError(t, err)
	require.Equal(t, DefaultInstanceKey, installation.InstanceKey)
	require.Equal(t, "human", installation.CreatedByActorType)
	require.Equal(t, fixture.HumanMemberID, installation.CreatedByActorID)

	serviceMember, err := store.UpsertMember(ctx, MemberUpsert{
		ID:                    "svcmem_read_backfilled_app",
		WorkspaceID:           fixture.WorkspaceID,
		ServiceDefinitionID:   definition.ID,
		ServiceInstallationID: installation.ID,
		MemberID:              fixture.ServiceMemberID,
		ServiceKind:           KindApp,
		Handle:                "github",
		DisplayName:           "GitHub",
		Status:                StatusActive,
	})
	require.NoError(t, err)
	require.Equal(t, fixture.ServiceMemberID, serviceMember.MemberID)

	connection, err := store.UpsertConnection(ctx, ConnectionUpsert{
		ID:                    "svcconn_read_backfilled_app",
		WorkspaceID:           fixture.WorkspaceID,
		ServiceDefinitionID:   definition.ID,
		ServiceInstallationID: installation.ID,
		ServiceKind:           KindApp,
		ExternalSubjectRef:    "789",
		DisplayLabel:          "acme",
		Credentials:           []byte(`{"_storage":"aes-gcm-v1","ciphertext":"encrypted"}`),
		Config:                []byte(`{"installationId":789}`),
		Status:                StatusActive,
		CreatedByMemberID:     fixture.HumanMemberID,
	})
	require.NoError(t, err)
	require.JSONEq(t, `{"_storage":"aes-gcm-v1","ciphertext":"encrypted"}`, string(connection.Credentials))

	endpoint, err := store.UpsertWebhookEndpoint(ctx, WebhookEndpointUpsert{
		ID:                      "svcwh_read_backfilled_app",
		WorkspaceID:             fixture.WorkspaceID,
		ServiceDefinitionID:     definition.ID,
		ServiceInstallationID:   installation.ID,
		ServiceConnectionID:     connection.ID,
		ServiceKind:             KindApp,
		EndpointKind:            EndpointKindProviderWebhook,
		ExternalSubjectRef:      connection.ExternalSubjectRef,
		CallbackPath:            "/api/apps/github/webhook",
		SigningSecretCiphertext: string(connection.Credentials),
		Config:                  []byte(`{"provider":"github","secretMaterial":"encrypted_connection_credentials"}`),
		Status:                  StatusActive,
		CreatedByMemberID:       fixture.HumanMemberID,
		ActiveEndpointKey:       "svc-endpoint-read-backfilled-app",
	})
	require.NoError(t, err)
	require.Equal(t, connection.ID, endpoint.ServiceConnectionID)

	loadedEndpoint, err := store.LoadWebhookEndpointForConnection(ctx, connection.ID, EndpointKindProviderWebhook)
	require.NoError(t, err)
	require.Equal(t, endpoint.ID, loadedEndpoint.ID)
	require.JSONEq(t, `{"provider":"github","secretMaterial":"encrypted_connection_credentials"}`, string(loadedEndpoint.Config))
}

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

	ctx := context.Background()
	db := startServiceActorRollbackDB(t, ctx)
	fixture := seedServiceActorFixture(t, db, "named_instances", time.Now().UTC())
	store := New(db)

	serviceAccountDefinition, err := store.LoadDefinitionBySlug(ctx, KindServiceAccount, "service-account")
	require.NoError(t, err)
	require.Equal(t, InstallationModeNamedMultiInstance, serviceAccountDefinition.InstallationMode)
	serviceAccountDefinition, err = store.UpsertDefinition(ctx, DefinitionUpsert{
		ID:          serviceAccountDefinition.ID,
		ServiceKind: serviceAccountDefinition.ServiceKind,
		Slug:        serviceAccountDefinition.Slug,
		Name:        serviceAccountDefinition.Name,
	})
	require.NoError(t, err)
	require.Equal(t, InstallationModeNamedMultiInstance, serviceAccountDefinition.InstallationMode)

	first, err := store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_first",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: serviceAccountDefinition.ID,
		ServiceKind:         KindServiceAccount,
		InstanceKey:         "sacct_named_instances_first",
		CreatedByActorType:  "platform_operator",
		CreatedByActorID:    fixture.UserID,
	})
	require.NoError(t, err)
	require.Empty(t, first.CreatedByMemberID)
	require.Equal(t, "platform_operator", first.CreatedByActorType)
	require.Equal(t, fixture.UserID, first.CreatedByActorID)

	second, err := store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_second",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: serviceAccountDefinition.ID,
		ServiceKind:         KindServiceAccount,
		InstanceKey:         "sacct_named_instances_second",
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.NoError(t, err)
	require.Equal(t, "human", second.CreatedByActorType)
	require.Equal(t, fixture.HumanMemberID, second.CreatedByActorID)

	loadedFirst, err := store.LoadInstallationByInstance(ctx, fixture.WorkspaceID, serviceAccountDefinition.ID, first.InstanceKey)
	require.NoError(t, err)
	require.Equal(t, first.ID, loadedFirst.ID)
	loadedSecond, err := store.LoadInstallationByInstance(ctx, fixture.WorkspaceID, serviceAccountDefinition.ID, second.InstanceKey)
	require.NoError(t, err)
	require.Equal(t, second.ID, loadedSecond.ID)

	instances, err := store.ListInstallationsByDefinition(ctx, fixture.WorkspaceID, serviceAccountDefinition.ID)
	require.NoError(t, err)
	require.Len(t, instances, 2)
	require.Equal(t, first.InstanceKey, instances[0].InstanceKey)
	require.Equal(t, second.InstanceKey, instances[1].InstanceKey)

	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		locked, found, err := store.InTx(tx).LoadInstallationByInstanceForUpdate(ctx, fixture.WorkspaceID, serviceAccountDefinition.ID, second.InstanceKey)
		require.NoError(t, err)
		require.True(t, found)
		require.Equal(t, second.ID, locked.ID)
		return nil
	})
	require.NoError(t, err)

	_, err = store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_missing_key",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: serviceAccountDefinition.ID,
		ServiceKind:         KindServiceAccount,
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.ErrorIs(t, err, ErrBadRequest)

	_, err = store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_wrong_kind",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: serviceAccountDefinition.ID,
		ServiceKind:         KindApp,
		InstanceKey:         "sacct_named_instances_wrong_kind",
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.ErrorIs(t, err, ErrBadRequest)

	_, err = store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_long_key",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: serviceAccountDefinition.ID,
		ServiceKind:         KindServiceAccount,
		InstanceKey:         strings.Repeat("x", 65),
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.ErrorIs(t, err, ErrBadRequest)

	singletonDefinition, err := store.UpsertDefinition(ctx, DefinitionUpsert{
		ID:          "svc_named_instances_singleton",
		ServiceKind: "fixture",
		Slug:        "named-instances-singleton",
		Name:        "Singleton",
	})
	require.NoError(t, err)
	require.Equal(t, InstallationModeSingleton, singletonDefinition.InstallationMode)

	singleton, err := store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_singleton",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: singletonDefinition.ID,
		ServiceKind:         singletonDefinition.ServiceKind,
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.NoError(t, err)
	require.Equal(t, DefaultInstanceKey, singleton.InstanceKey)

	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		locked, found, err := store.InTx(tx).LoadInstallationByInstanceForUpdate(ctx, fixture.WorkspaceID, singletonDefinition.ID, DefaultInstanceKey)
		require.NoError(t, err)
		require.True(t, found)
		require.Equal(t, singleton.ID, locked.ID)
		return nil
	})
	require.NoError(t, err)

	_, err = store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_named_instances_invalid_singleton",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: singletonDefinition.ID,
		ServiceKind:         singletonDefinition.ServiceKind,
		InstanceKey:         "not-default",
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.ErrorIs(t, err, ErrBadRequest)
}

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

	ctx := context.Background()
	db := startServiceActorRollbackDB(t, ctx)
	fixture := seedServiceActorFixture(t, db, "write_seed", time.Now().UTC())
	store := New(db)

	err := coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		definition, err := txStore.UpsertDefinition(ctx, DefinitionUpsert{
			ID:          "svc_write_seed",
			ServiceKind: "fixture",
			Slug:        "reviewer",
			Name:        "Reviewer",
			Status:      StatusActive,
		})
		if err != nil {
			return err
		}
		installation, err := txStore.UpsertInstallation(ctx, InstallationUpsert{
			ID:                  "svcinst_write_seed",
			WorkspaceID:         fixture.WorkspaceID,
			ServiceDefinitionID: definition.ID,
			ServiceKind:         definition.ServiceKind,
			Status:              StatusActive,
			CreatedByMemberID:   fixture.HumanMemberID,
		})
		if err != nil {
			return err
		}
		member, err := txStore.UpsertMember(ctx, MemberUpsert{
			ID:                    "svcmem_write_seed",
			WorkspaceID:           fixture.WorkspaceID,
			ServiceDefinitionID:   definition.ID,
			ServiceInstallationID: installation.ID,
			MemberID:              fixture.ServiceMemberID,
			ServiceKind:           definition.ServiceKind,
			Handle:                "reviewer",
			DisplayName:           "Reviewer",
			Status:                StatusActive,
		})
		if err != nil {
			return err
		}
		if _, err := txStore.UpsertCapability(ctx, CapabilityUpsert{
			ID:                  "svccap_write_message",
			ServiceDefinitionID: definition.ID,
			ServiceKind:         definition.ServiceKind,
			CapabilityKey:       CapabilityMessageWrite,
			DefaultEnabled:      true,
		}); err != nil {
			return err
		}
		if err := txStore.SyncGrantScopes(ctx, installation, fixture.HumanMemberID, []string{CapabilityMessageWrite, CapabilityMCPTools}, time.Now().UTC()); err != nil {
			return err
		}
		connection, err := txStore.UpsertConnection(ctx, ConnectionUpsert{
			ID:                    "svcconn_write_seed",
			WorkspaceID:           fixture.WorkspaceID,
			ServiceDefinitionID:   definition.ID,
			ServiceInstallationID: installation.ID,
			ServiceKind:           definition.ServiceKind,
			ExternalSubjectRef:    "reviewer-main",
			DisplayLabel:          "Reviewer main",
			Credentials:           []byte(`{}`),
			Status:                StatusActive,
			CreatedByMemberID:     fixture.HumanMemberID,
		})
		if err != nil {
			return err
		}
		if _, err := txStore.UpsertPrincipalAuthorization(ctx, PrincipalAuthorizationUpsert{
			ID:                     "svcauth_write_seed",
			WorkspaceID:            fixture.WorkspaceID,
			ServiceDefinitionID:    definition.ID,
			ServiceInstallationID:  installation.ID,
			ServiceKind:            definition.ServiceKind,
			PrincipalKind:          "agent",
			PrincipalRef:           "agt_write_seed",
			Scopes:                 []string{CapabilityMCPTools},
			AuthorizedByMemberID:   fixture.HumanMemberID,
			ActiveAuthorizationKey: "svc-auth-write-seed",
		}); err != nil {
			return err
		}
		binding, err := txStore.UpsertTargetBinding(ctx, TargetBindingUpsert{
			ID:                       "svctb_write_seed",
			WorkspaceID:              fixture.WorkspaceID,
			ServiceDefinitionID:      definition.ID,
			ServiceInstallationID:    installation.ID,
			ServiceMemberID:          member.ID,
			ServiceKind:              definition.ServiceKind,
			TargetKind:               "channel",
			TargetRef:                fixture.ScopeID,
			DeliveryScopeID:          fixture.ScopeID,
			PermissionCheckScopeID:   fixture.ScopeID,
			AuthoritySubjectMemberID: fixture.HumanMemberID,
			CreatedByMemberID:        fixture.HumanMemberID,
			CreatedByActorType:       "human",
			CreatedByActorID:         fixture.UserID,
			CreatedVia:               "test",
			ActiveBindingKey:         "svc-binding-write-seed",
		})
		if err != nil {
			return err
		}
		if _, _, err := txStore.UpsertEventSubscription(ctx, EventSubscriptionUpsert{
			ID:                     "svcesub_write_seed",
			WorkspaceID:            fixture.WorkspaceID,
			ServiceDefinitionID:    definition.ID,
			ServiceInstallationID:  installation.ID,
			ServiceMemberID:        member.ID,
			ServiceConnectionID:    connection.ID,
			ServiceConnectionRef:   connection.ExternalSubjectRef,
			ServiceTargetBindingID: binding.ID,
			ServiceKind:            definition.ServiceKind,
			SubscriptionKind:       SubscriptionKindPullRequest,
			TargetKind:             binding.TargetKind,
			TargetRef:              binding.TargetRef,
			DeliveryScopeID:        binding.DeliveryScopeID,
			PermissionCheckScopeID: binding.PermissionCheckScopeID,
			OwnerMemberID:          fixture.HumanMemberID,
			AuthorizerMemberID:     fixture.HumanMemberID,
			CreatedByActorType:     "human",
			CreatedByActorID:       fixture.UserID,
			CreatedVia:             "test",
			ExternalResourceType:   "pull_request",
			ExternalResourceKey:    "github:acme/agentteam#42",
			ExternalResourceRef:    "https://github.com/acme/agentteam/pull/42",
			EventFilterKey:         "comments",
			EventFilters:           []byte(`{"events":["comments"]}`),
			DeliveryPolicy:         "principal",
			NotifyPrincipal:        true,
			ActiveSubscriptionKey:  "svc-event-write-seed",
			Metadata:               []byte(`{"source":"test"}`),
		}); err != nil {
			return err
		}
		endpoint, err := txStore.UpsertWebhookEndpoint(ctx, WebhookEndpointUpsert{
			ID:                      "svcwh_write_seed",
			WorkspaceID:             fixture.WorkspaceID,
			ServiceDefinitionID:     definition.ID,
			ServiceInstallationID:   installation.ID,
			ServiceConnectionID:     connection.ID,
			ServiceKind:             definition.ServiceKind,
			EndpointKind:            EndpointKindProviderWebhook,
			CallbackPath:            "/api/services/reviewer/webhook",
			SigningSecretCiphertext: "secret-envelope",
			Status:                  StatusActive,
			CreatedByMemberID:       fixture.HumanMemberID,
			ActiveEndpointKey:       "svc-endpoint-write-seed",
		})
		if err != nil {
			return err
		}
		receipt, inserted, err := txStore.ReserveWebhookReceipt(ctx, WebhookReceiptReserve{
			ID:                       "svcwhr_write_seed",
			WorkspaceID:              fixture.WorkspaceID,
			ServiceDefinitionID:      definition.ID,
			ServiceInstallationID:    installation.ID,
			ServiceConnectionID:      connection.ID,
			ServiceConnectionRef:     connection.ExternalSubjectRef,
			ServiceWebhookEndpointID: endpoint.ID,
			ServiceKind:              definition.ServiceKind,
			EndpointKind:             EndpointKindProviderWebhook,
			DeliveryKey:              "delivery-write-seed",
			EventType:                "issue_comment_created",
			EventAction:              "created",
			EventID:                  "github:delivery-write-seed",
			EventKey:                 fixture.WorkspaceID + ":github:delivery-write-seed",
			ExternalResourceType:     "pull_request",
			ExternalResourceKey:      "github:acme/agentteam#42",
			ExternalResourceRef:      "github:acme/agentteam#42",
			ExternalResourceURL:      "https://github.com/acme/agentteam/pull/42",
			PayloadExcerpt:           `{"action":"created"}`,
			Payload:                  []byte(`{"action":"created"}`),
			NormalizedEvent:          []byte(`{"delivery_key":"delivery-write-seed"}`),
		})
		if err != nil {
			return err
		}
		if !inserted {
			return conflict("expected new service webhook receipt")
		}
		if err := txStore.CompleteWebhookReceipt(ctx, WebhookReceiptOutcome{
			ID:                       receipt.ID,
			MatchedSubscriptionCount: 1,
			Status:                   WebhookReceiptStatusProcessed,
			NormalizedEvent:          []byte(`{"delivery_key":"delivery-write-seed","status":"processed"}`),
		}); err != nil {
			return err
		}
		delivery, duplicate, err := txStore.ReserveEventDelivery(ctx, EventDeliveryReserve{
			ID:                  "svced_write_seed",
			WorkspaceID:         fixture.WorkspaceID,
			EventSubscriptionID: "svcesub_write_seed",
			WebhookReceiptID:    receipt.ID,
			EventType:           "issue_comment_created",
			EventAction:         "created",
			EventID:             "github:delivery-write-seed",
			EventKey:            fixture.WorkspaceID + ":github:delivery-write-seed",
			EventPayload:        []byte(`{"delivery_key":"delivery-write-seed"}`),
			ExternalResourceRef: "github:acme/agentteam#42",
			ExternalResourceURL: "https://github.com/acme/agentteam/pull/42#issuecomment-1",
			IdempotencyKey:      "svc-event-delivery-write-seed",
		})
		if err != nil {
			return err
		}
		if duplicate {
			return conflict("expected new service event delivery")
		}
		return txStore.CompleteEventDelivery(ctx, EventDeliveryCompletion{
			ID:     delivery.ID,
			Status: runeffect.StatusSucceeded,
			Effect: runeffect.TaskDiscussionEffect("task_entry_service_event_delivery"),
		})
	})
	require.NoError(t, err)

	scopes, err := store.ListActiveGrantScopes(ctx, "svcinst_write_seed")
	require.NoError(t, err)
	require.Equal(t, []string{CapabilityMCPTools, CapabilityMessageWrite}, scopes)

	hasMessageWrite, err := store.HasEnabledCapability(ctx, "svc_write_seed", CapabilityMessageWrite)
	require.NoError(t, err)
	require.True(t, hasMessageWrite)

	bindings, err := store.ListActiveTargetBindingsForTarget(ctx, fixture.WorkspaceID, "channel", fixture.ScopeID)
	require.NoError(t, err)
	require.Len(t, bindings, 1)
	require.Equal(t, "svctb_write_seed", bindings[0].ID)

	subscription, err := store.LoadEventSubscription(ctx, fixture.WorkspaceID, "svcesub_write_seed")
	require.NoError(t, err)
	require.Equal(t, "svc_write_seed", subscription.ServiceDefinitionID)
	require.Equal(t, "svcconn_write_seed", subscription.ServiceConnectionID)
	require.Equal(t, "reviewer-main", subscription.ServiceConnectionRef)
	require.Equal(t, "svctb_write_seed", subscription.ServiceTargetBindingID)
	require.Equal(t, SubscriptionKindPullRequest, subscription.SubscriptionKind)
	require.Equal(t, "github:acme/agentteam#42", subscription.ExternalResourceKey)
	require.True(t, subscription.NotifyPrincipal)
	require.JSONEq(t, `{"events":["comments"]}`, string(subscription.EventFilters))

	listedSubscriptions, err := store.ListEventSubscriptions(ctx, EventSubscriptionListQuery{
		WorkspaceID:           fixture.WorkspaceID,
		ServiceDefinitionIDs:  []string{"svc_write_seed"},
		ServiceInstallationID: "svcinst_write_seed",
		SubscriptionKind:      SubscriptionKindPullRequest,
		ExternalResourceKey:   "github:acme/agentteam#42",
		OwnerMemberID:         fixture.HumanMemberID,
	})
	require.NoError(t, err)
	require.Len(t, listedSubscriptions, 1)
	require.Equal(t, subscription.ID, listedSubscriptions[0].ID)

	var receiptStatus string
	var receiptMatched int
	var receiptDeliveryKey string
	require.NoError(t, db.QueryRow(ctx, `
SELECT status, matched_subscription_count, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(normalized_event, '$.delivery_key')), '')
FROM service_webhook_receipts
WHERE id = 'svcwhr_write_seed'
`).Scan(&receiptStatus, &receiptMatched, &receiptDeliveryKey))
	require.Equal(t, WebhookReceiptStatusProcessed, receiptStatus)
	require.Equal(t, 1, receiptMatched)
	require.Equal(t, "delivery-write-seed", receiptDeliveryKey)

	var deliveryStatus string
	var deliveryEffectKind string
	var deliveryEffectRef string
	var deliveryMessageID string
	var deliveryReceiptID string
	var deliveryPayloadKey string
	require.NoError(t, db.QueryRow(ctx, `
SELECT status, effect_kind, effect_ref, COALESCE(message_id, ''),
       COALESCE(service_webhook_receipt_id, ''),
       COALESCE(JSON_UNQUOTE(JSON_EXTRACT(event_payload, '$.delivery_key')), '')
FROM service_event_deliveries
WHERE id = 'svced_write_seed'
`).Scan(&deliveryStatus, &deliveryEffectKind, &deliveryEffectRef, &deliveryMessageID, &deliveryReceiptID, &deliveryPayloadKey))
	require.Equal(t, string(runeffect.StatusSucceeded), deliveryStatus)
	require.Equal(t, string(runeffect.EffectKindTaskDiscussion), deliveryEffectKind)
	require.Equal(t, "task_entry_service_event_delivery", deliveryEffectRef)
	require.Empty(t, deliveryMessageID)
	require.Equal(t, "svcwhr_write_seed", deliveryReceiptID)
	require.Equal(t, "delivery-write-seed", deliveryPayloadKey)
}

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

	ctx := context.Background()
	db := startServiceActorRollbackDB(t, ctx)
	fixture := seedServiceActorFixture(t, db, "nullable_active_keys", time.Now().UTC())
	store := New(db)

	secondScopeID := fixture.ScopeID + "_second"
	mustExec(t, db, `
INSERT INTO scopes (id, workspace_id, type, identity_key, status, created_by_member_id, created_at, updated_at)
VALUES ($1, $2, 'channel', $3, 'active', $4, $5, $5)
`, secondScopeID, fixture.WorkspaceID, "channel:"+secondScopeID, fixture.HumanMemberID, time.Now().UTC())

	err := coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		txStore := store.InTx(tx)
		definition, err := txStore.UpsertDefinition(ctx, DefinitionUpsert{
			ID:          "svc_nullable_active_keys",
			ServiceKind: "fixture",
			Slug:        "nullable-active-keys",
			Name:        "Nullable Active Keys",
			Status:      StatusActive,
		})
		if err != nil {
			return err
		}
		installation, err := txStore.UpsertInstallation(ctx, InstallationUpsert{
			ID:                  "svcinst_nullable_active_keys",
			WorkspaceID:         fixture.WorkspaceID,
			ServiceDefinitionID: definition.ID,
			ServiceKind:         definition.ServiceKind,
			Status:              StatusActive,
			CreatedByMemberID:   fixture.HumanMemberID,
		})
		if err != nil {
			return err
		}
		member, err := txStore.UpsertMember(ctx, MemberUpsert{
			ID:                    "svcmem_nullable_active_keys",
			WorkspaceID:           fixture.WorkspaceID,
			ServiceDefinitionID:   definition.ID,
			ServiceInstallationID: installation.ID,
			MemberID:              fixture.ServiceMemberID,
			ServiceKind:           definition.ServiceKind,
			Handle:                "nullable-active-keys",
			DisplayName:           "Nullable Active Keys",
			Status:                StatusActive,
		})
		if err != nil {
			return err
		}
		for _, principalRef := range []string{"agt_nullable_one", "agt_nullable_two"} {
			if _, err := txStore.UpsertPrincipalAuthorization(ctx, PrincipalAuthorizationUpsert{
				ID:                    "svcauth_" + principalRef,
				WorkspaceID:           fixture.WorkspaceID,
				ServiceDefinitionID:   definition.ID,
				ServiceInstallationID: installation.ID,
				ServiceKind:           definition.ServiceKind,
				PrincipalKind:         "agent",
				PrincipalRef:          principalRef,
				Scopes:                []string{CapabilityMCPTools},
				AuthorizedByMemberID:  fixture.HumanMemberID,
			}); err != nil {
				return err
			}
		}
		for idx, scopeID := range []string{fixture.ScopeID, secondScopeID} {
			if _, err := txStore.UpsertTargetBinding(ctx, TargetBindingUpsert{
				ID:                     []string{"svctb_nullable_active_keys_one", "svctb_nullable_active_keys_two"}[idx],
				WorkspaceID:            fixture.WorkspaceID,
				ServiceDefinitionID:    definition.ID,
				ServiceInstallationID:  installation.ID,
				ServiceMemberID:        member.ID,
				ServiceKind:            definition.ServiceKind,
				TargetKind:             "channel",
				TargetRef:              scopeID,
				DeliveryScopeID:        scopeID,
				PermissionCheckScopeID: scopeID,
				CreatedByMemberID:      fixture.HumanMemberID,
				CreatedByActorType:     "human",
				CreatedByActorID:       fixture.UserID,
				CreatedVia:             "test",
			}); err != nil {
				return err
			}
		}
		return nil
	})
	require.NoError(t, err)

	requireServiceActorSingleInt(t, ctx, db, 2, `
SELECT COUNT(*)
FROM service_principal_authorizations
WHERE service_installation_id = 'svcinst_nullable_active_keys'
  AND active_authorization_key IS NULL
`)
	requireServiceActorSingleInt(t, ctx, db, 2, `
SELECT COUNT(*)
FROM service_target_bindings
WHERE service_installation_id = 'svcinst_nullable_active_keys'
  AND active_binding_key IS NULL
`)
}

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

	ctx := context.Background()
	db := startServiceActorRollbackDB(t, ctx)
	now := time.Now().UTC()
	fixture := seedServiceActorFixture(t, db, "member_unique_conflict", now)
	store := New(db)

	const secondServiceMemberID = "mem_svcactor_service_member_unique_conflict_second"
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, workspace_role, status, created_at, updated_at)
VALUES ($1, $2, 'service', 'Service Second', NULL, 'active', $3, $3)
`, secondServiceMemberID, fixture.WorkspaceID, now)

	firstDefinition, err := store.UpsertDefinition(ctx, DefinitionUpsert{
		ID:          "svc_member_unique_conflict_one",
		ServiceKind: "fixture",
		Slug:        "member-unique-conflict-one",
		Name:        "Member Unique Conflict One",
		Status:      StatusActive,
	})
	require.NoError(t, err)
	firstInstallation, err := store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_member_unique_conflict_one",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: firstDefinition.ID,
		ServiceKind:         firstDefinition.ServiceKind,
		Status:              StatusActive,
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.NoError(t, err)
	_, err = store.UpsertMember(ctx, MemberUpsert{
		ID:                    "svcmem_member_unique_conflict_one",
		WorkspaceID:           fixture.WorkspaceID,
		ServiceDefinitionID:   firstDefinition.ID,
		ServiceInstallationID: firstInstallation.ID,
		MemberID:              fixture.ServiceMemberID,
		ServiceKind:           firstDefinition.ServiceKind,
		Handle:                "reviewer",
		DisplayName:           "Reviewer One",
		Status:                StatusActive,
	})
	require.NoError(t, err)

	secondDefinition, err := store.UpsertDefinition(ctx, DefinitionUpsert{
		ID:          "svc_member_unique_conflict_two",
		ServiceKind: "fixture",
		Slug:        "member-unique-conflict-two",
		Name:        "Member Unique Conflict Two",
		Status:      StatusActive,
	})
	require.NoError(t, err)
	secondInstallation, err := store.UpsertInstallation(ctx, InstallationUpsert{
		ID:                  "svcinst_member_unique_conflict_two",
		WorkspaceID:         fixture.WorkspaceID,
		ServiceDefinitionID: secondDefinition.ID,
		ServiceKind:         secondDefinition.ServiceKind,
		Status:              StatusActive,
		CreatedByMemberID:   fixture.HumanMemberID,
	})
	require.NoError(t, err)

	_, err = store.UpsertMember(ctx, MemberUpsert{
		ID:                    "svcmem_member_unique_conflict_two",
		WorkspaceID:           fixture.WorkspaceID,
		ServiceDefinitionID:   secondDefinition.ID,
		ServiceInstallationID: secondInstallation.ID,
		MemberID:              secondServiceMemberID,
		ServiceKind:           secondDefinition.ServiceKind,
		Handle:                "reviewer",
		DisplayName:           "Reviewer Two",
		Status:                StatusActive,
	})
	require.ErrorIs(t, err, ErrConflict)

	firstMember, err := store.LoadMemberByInstallation(ctx, firstInstallation.ID)
	require.NoError(t, err)
	require.Equal(t, fixture.ServiceMemberID, firstMember.MemberID)
	require.Equal(t, "Reviewer One", firstMember.DisplayName)

	_, err = store.LoadMemberByInstallation(ctx, secondInstallation.ID)
	require.ErrorIs(t, err, ErrNotFound)
}

func seedServiceActorFixture(t testing.TB, db coredb.QueryExecer, suffix string, now time.Time) serviceActorFixture {
	t.Helper()
	fixture := serviceActorFixture{
		UserID:          "usr_svcactor_" + suffix,
		WorkspaceID:     "wsp_svcactor_" + suffix,
		HumanMemberID:   "mem_svcactor_human_" + suffix,
		ServiceMemberID: "mem_svcactor_service_" + suffix,
		ScopeID:         "scp_svcactor_" + suffix,
	}
	orgID := "org_svcactor_" + 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', 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, 'channel', $3, 'active', $4, $5, $5)
`, fixture.ScopeID, fixture.WorkspaceID, "channel:"+fixture.ScopeID, fixture.HumanMemberID, now)
	return fixture
}

func TestServiceActorStoreUsesCredentialEnvelopeConstant(t *testing.T) {
	require.Equal(t, "aes-gcm-v1", model.CredentialStorageScheme)
}

func requireServiceActorSingleInt(t *testing.T, ctx context.Context, db coredb.QueryRower, want int, query string, args ...any) {
	t.Helper()
	var got int
	require.NoError(t, db.QueryRow(ctx, query, args...).Scan(&got))
	require.Equal(t, want, got)
}