package app

import (
	"context"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

const (
	PullRequestSubscriptionProviderGitHub = "github"
	PullRequestSubscriptionProviderGitea  = "gitea"

	PullRequestSubscriptionResourcePullRequest = "pull_request"

	ServiceEventSubscriptionStatusActive    = "active"
	ServiceEventSubscriptionStatusPaused    = "paused"
	ServiceEventSubscriptionStatusCompleted = "completed"
	ServiceEventSubscriptionStatusBlocked   = "blocked"

	pullRequestSubscriptionDeliveryPolicy = "app_event_for_principal"
)

type PullRequestSubscriptionResource struct {
	Provider    string
	ResourceRef string
	ResourceKey string
	Owner       string
	Repo        string
	Number      int
}

type CreateServiceEventSubscriptionParams struct {
	Provider         string
	InstallationID   string
	Actor            ServiceEventActor
	Target           ServiceEventTarget
	ResourceRef      string
	ResourceKey      string
	EventFilters     map[string]any
	ValidateResource bool
	NotifyPrincipal  *bool
	CreatedVia       string
	CorrelationID    string
	TriggerID        string
	Metadata         map[string]any
	BindingMetadata  map[string]any
	PausedReason     string
}

type ServiceEventSubscriptionResult struct {
	Status        string
	AlreadyExists bool
	Subscription  ServiceEventSubscriptionRecord
	BindingID     string
}

type ServiceEventSubscriptionRecord struct {
	ID                    string
	WorkspaceID           string
	OwnerUserID           string
	Provider              string
	AppID                 string
	AppInstallationID     string
	AppBindingID          string
	ProviderConnectionRef string
	TriggerID             string
	TargetKind            string
	TargetRef             string

	DeliveryScopeID          string
	PermissionCheckScopeID   string
	AuthoritySubjectMemberID string
	DeliveryContract         []byte
	ScopeSnapshot            []byte
	ResourceKind             string
	ResourceRef              string
	ResourceKey              string
	EventFilters             []byte
	Status                   string
	PausedReason             string
	CreatedByMemberID        string
	CreatedByActorType       string
	CreatedByActorID         string
	AuthorizerUserID         string
	DeliveryPolicy           string
	NotifyPrincipal          bool
	CreatedVia               string
	CorrelationID            string
	Metadata                 []byte
	CreatedAt                time.Time
	UpdatedAt                time.Time
	PausedAt                 *time.Time
	RemovedAt                *time.Time
	LastEventAt              *time.Time
	LastFailureAt            *time.Time
	LastFailureCode          string
	LastFailureMessage       string
}

// ListServiceEventSubscriptionsOptions applies bounded SQL filtering for the
// canonical service-event subscription list path.
type ListServiceEventSubscriptionsOptions struct {
	ResourceKey string
	Limit       int
	Cursor      string
	Page        bool
}

type ServiceEventSubscriptionPage struct {
	Subscriptions []ServiceEventSubscriptionRecord
	Limit         int
	Cursor        string
	HasMore       bool
	NextCursor    string
}

// CreateServiceEventSubscription creates or reuses a pull-request service-event
// subscription through the canonical Service Actor store.
func (store *Store) CreateServiceEventSubscription(ctx context.Context, workspaceID string, params CreateServiceEventSubscriptionParams) (ServiceEventSubscriptionResult, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return ServiceEventSubscriptionResult{}, badRequest("workspaceId is required")
	}
	provider := normalizePullRequestSubscriptionProvider(params.Provider)
	appID, err := pullRequestSubscriptionProviderAppID(provider)
	if err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	installationID := strings.TrimSpace(params.InstallationID)
	if installationID == "" {
		return ServiceEventSubscriptionResult{}, badRequest("installationId is required")
	}
	if err := validateServiceEventActor(params.Actor); err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	if err := validateServiceEventTarget(params.Target); err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	resourceRef := strings.TrimSpace(params.ResourceRef)
	resourceKey := strings.TrimSpace(params.ResourceKey)
	if resourceRef == "" {
		return ServiceEventSubscriptionResult{}, badRequest("resourceRef is required")
	}
	if resourceKey == "" {
		return ServiceEventSubscriptionResult{}, badRequest("resourceKey is required")
	}
	createdVia := normalizeCreatedVia(params.CreatedVia)
	if !validPullRequestSubscriptionCreatedVia(createdVia) {
		return ServiceEventSubscriptionResult{}, badRequest("createdVia is not supported")
	}
	eventFiltersJSON, err := marshalPullRequestSubscriptionEventFilters(params.EventFilters)
	if err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	repositoryFullName, err := pullRequestSubscriptionRepositoryFullName(provider, resourceRef, resourceKey)
	if err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	subscriptionMetadata := cloneServiceEventSubscriptionMetadata(params.Metadata)
	expectedConnectionRef := serviceEventSubscriptionExpectedConnectionRef(subscriptionMetadata)

	bindingMetadataJSON, err := marshalServiceEventSubscriptionJSONMap(params.BindingMetadata)
	if err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	scopeSnapshotJSON, err := marshalServiceEventSubscriptionJSONMap(params.Target.ScopeSnapshot)
	if err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	deliveryOptions, err := normalizeServiceEventSubscriptionDeliveryOptions(params.NotifyPrincipal)
	if err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	installationView, err := store.installationFromServiceActor(ctx, trimmedWorkspaceID, installationID)
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return ServiceEventSubscriptionResult{}, storeerr.BadRequest("app installation is not enabled")
		}
		return ServiceEventSubscriptionResult{}, err
	}
	if strings.TrimSpace(installationView.AppID) != appID || !installationView.Enabled {
		return ServiceEventSubscriptionResult{}, storeerr.BadRequest("app installation is not enabled")
	}
	if params.ValidateResource {
		resource, err := NormalizePullRequestSubscriptionRef(provider, resourceRef)
		if err != nil {
			return ServiceEventSubscriptionResult{}, err
		}
		if resource.ResourceKey != resourceKey {
			return ServiceEventSubscriptionResult{}, storeerr.BadRequest(
				"pull request resource is inconsistent",
			)
		}
		if err := validatePullRequestSubscriptionResource(
			ctx,
			store,
			trimmedWorkspaceID,
			installationID,
			resource,
		); err != nil {
			return ServiceEventSubscriptionResult{}, err
		}
	}
	installation := serviceEventSubscriptionInstallation{
		ID:          installationView.ID,
		AppID:       installationView.AppID,
		WorkspaceID: trimmedWorkspaceID,
		MemberID:    installationView.MemberID,
		Handle:      installationView.Handle,
		Enabled:     installationView.Enabled,
	}

	var result ServiceEventSubscriptionResult
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		actorForSubscription := params.Actor
		if strings.TrimSpace(params.Actor.ActorType) == "human" {
			// Current-read the human installation authority inside the
			// write transaction: a grant revoked after any preflight must
			// fence the create.
			if err := requireHumanInstallationUse(ctx, tx, trimmedWorkspaceID, params.Actor.MemberID, installation.ID); err != nil {
				return err
			}
		}
		if strings.TrimSpace(params.Actor.ActorType) == "agent" {
			// The authorization row is the source of truth for the authorizer audit
			// field. A stale or forged caller projection must never be persisted.
			authorization, err := checkAgentAppAuthorization(ctx, tx, trimmedWorkspaceID, params.Actor.ActorID, installation.ID, []string{ServiceEventSubscriptionScopeWrite})
			if err != nil {
				return err
			}
			actorForSubscription.AuthorizerUserID = strings.TrimSpace(authorization.AuthorizedByMemberID)
			if actorForSubscription.AuthorizerUserID == "" {
				return storeerr.Forbidden("app_authorization_authorizer_missing")
			}
		}
		if err := recordPullRequestSubscriptionRateLimitEvent(ctx, tx, pullRequestSubscriptionRateLimitEvent{
			WorkspaceID:       trimmedWorkspaceID,
			AppInstallationID: installation.ID,
			ActorType:         params.Actor.ActorType,
			ActorID:           params.Actor.ActorID,
			Action:            pullRequestSubscriptionRateLimitActionCreate,
			TargetKind:        params.Target.TargetKind,
			TargetRef:         params.Target.TargetRef,
			ResourceKey:       resourceKey,
			CorrelationID:     params.CorrelationID,
		}); err != nil {
			return err
		}
		connectionRef, err := store.requirePullRequestSubscriptionConnectionForInstallation(ctx, provider, installation.ID, repositoryFullName, expectedConnectionRef)
		if err != nil {
			return err
		}
		principalMemberID, err := serviceEventPrincipalMemberIDForTarget(ctx, tx, trimmedWorkspaceID, params.Actor, params.Target)
		if err != nil {
			return err
		}
		if strings.TrimSpace(params.Actor.ActorType) == "human" {
			// Human creates are always self-authorized; do not trust a caller's
			// authorizer projection either.
			actorForSubscription.AuthorizerUserID = principalMemberID
		} else if strings.TrimSpace(actorForSubscription.AuthorizerUserID) == "" {
			actorForSubscription.AuthorizerUserID = principalMemberID
		}
		// This key is server-owned. Remove any caller value first, then stamp it
		// only after a live agent backing-machine owner has been resolved above.
		// Other caller metadata remains untouched.
		delete(subscriptionMetadata, serviceEventSubscriptionOwnerMemberIDSourceMetadataKey)
		if strings.TrimSpace(params.Actor.ActorType) == "agent" {
			subscriptionMetadata[serviceEventSubscriptionOwnerMemberIDSourceMetadataKey] = serviceEventSubscriptionOwnerMemberIDSourceAgentMachineV1
		}
		subscriptionMetadata["provider_connection_ref"] = connectionRef
		subscriptionMetadataJSON, err := marshalServiceEventSubscriptionJSONMap(subscriptionMetadata)
		if err != nil {
			return err
		}
		subscription, serviceBindingID, alreadyExists, err := store.upsertServiceEventSubscription(ctx, tx, serviceEventSubscriptionWrite{
			WorkspaceID:           trimmedWorkspaceID,
			Provider:              provider,
			Installation:          installation,
			ProviderConnectionRef: connectionRef,
			Actor:                 actorForSubscription,
			PrincipalMemberID:     principalMemberID,
			Target:                params.Target,
			ResourceRef:           resourceRef,
			ResourceKey:           resourceKey,
			EventFilters:          eventFiltersJSON,
			CreatedVia:            createdVia,
			CorrelationID:         params.CorrelationID,
			RequestedID:           params.TriggerID,
			Metadata:              subscriptionMetadataJSON,
			ScopeSnapshot:         scopeSnapshotJSON,
			BindingMetadata:       bindingMetadataJSON,
			NotifyPrincipal:       deliveryOptions.NotifyPrincipal,
		})
		if err != nil {
			return err
		}
		result = ServiceEventSubscriptionResult{
			Status:        subscription.Status,
			AlreadyExists: alreadyExists,
			Subscription:  subscription,
			BindingID:     serviceBindingID,
		}
		return nil
	}); err != nil {
		return ServiceEventSubscriptionResult{}, err
	}
	return result, nil
}

// GetServiceEventSubscriptionForAppInstallation loads a canonical
// pull-request subscription scoped to one installed provider app. App-scoped
// MCP callers use this before id-based mutations so a token for one provider
// app cannot operate on another app's subscription.
func (store *Store) GetServiceEventSubscriptionForAppInstallation(ctx context.Context, workspaceID string, actor ServiceEventActor, provider string, installationID string, subscriptionID string, requiredScopes []string) (ServiceEventSubscriptionRecord, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	serviceDefinitionID, err := pullRequestSubscriptionProviderAppID(provider)
	if err != nil {
		return ServiceEventSubscriptionRecord{}, err
	}
	trimmedInstallationID := strings.TrimSpace(installationID)
	trimmedSubscriptionID := strings.TrimSpace(subscriptionID)
	if trimmedWorkspaceID == "" || trimmedInstallationID == "" || trimmedSubscriptionID == "" {
		return ServiceEventSubscriptionRecord{}, badRequest("workspaceId, installationId, and subscriptionId are required")
	}
	if err := validateServiceEventActor(actor); err != nil {
		return ServiceEventSubscriptionRecord{}, err
	}
	var subscription ServiceEventSubscriptionRecord
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := requireServiceEventActorAuthorization(ctx, tx, trimmedWorkspaceID, actor, trimmedInstallationID, requiredScopes); err != nil {
			return err
		}
		loaded, err := loadServiceEventSubscriptionByID(ctx, tx, trimmedWorkspaceID, trimmedSubscriptionID)
		if err != nil {
			if coredb.IsNotFound(err) {
				return storeerr.NotFound("watch_not_found")
			}
			return err
		}
		if loaded.RemovedAt != nil ||
			strings.TrimSpace(loaded.AppID) != serviceDefinitionID ||
			strings.TrimSpace(loaded.AppInstallationID) != trimmedInstallationID ||
			!isPullRequestServiceEventSubscription(loaded) {
			return storeerr.NotFound("watch_not_found")
		}
		if err := requireServiceEventActorOwnsSubscription(ctx, tx, trimmedWorkspaceID, actor, loaded); err != nil {
			return err
		}
		subscription = loaded
		return nil
	}); err != nil {
		return ServiceEventSubscriptionRecord{}, err
	}
	return subscription, nil
}

func isPullRequestServiceEventSubscription(subscription ServiceEventSubscriptionRecord) bool {
	if pullRequestSubscriptionProviderFromServiceDefinitionID(subscription.AppID) == "" {
		return false
	}
	return strings.TrimSpace(subscription.ResourceKind) == PullRequestSubscriptionResourcePullRequest
}

func (store *Store) ListServiceEventSubscriptions(ctx context.Context, workspaceID string, actor ServiceEventActor, installationID string, targetKind string, targetRef string, status string, options ListServiceEventSubscriptionsOptions) ([]ServiceEventSubscriptionRecord, error) {
	page, err := store.ListServiceEventSubscriptionsPage(ctx, workspaceID, actor, installationID, targetKind, targetRef, status, options)
	if err != nil {
		return nil, err
	}
	return page.Subscriptions, nil
}

func (store *Store) ListServiceEventSubscriptionsPage(ctx context.Context, workspaceID string, actor ServiceEventActor, installationID string, targetKind string, targetRef string, status string, options ListServiceEventSubscriptionsOptions) (ServiceEventSubscriptionPage, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return ServiceEventSubscriptionPage{}, badRequest("workspaceId is required")
	}
	if err := validateServiceEventActor(actor); err != nil {
		return ServiceEventSubscriptionPage{}, err
	}
	pageLimit := normalizeServiceEventSubscriptionListLimit(options.Limit)
	offset, err := parseServiceEventSubscriptionListCursor(options.Cursor)
	if err != nil {
		return ServiceEventSubscriptionPage{}, err
	}
	trimmedInstallationID := strings.TrimSpace(installationID)
	serviceDefinitionIDs := pullRequestSubscriptionProviderServiceDefinitionIDs()
	if trimmedInstallationID != "" {
		serviceDefinitionID, err := pullRequestSubscriptionServiceDefinitionForInstallation(ctx, store.db, trimmedWorkspaceID, trimmedInstallationID)
		if err != nil {
			return ServiceEventSubscriptionPage{}, err
		}
		serviceDefinitionIDs = []string{serviceDefinitionID}
	}
	if strings.TrimSpace(actor.ActorType) == "agent" {
		if trimmedInstallationID == "" {
			return ServiceEventSubscriptionPage{}, badRequest("installationId is required for agent subscription list")
		}
		if _, err := store.CheckAgentAppAuthorization(ctx, trimmedWorkspaceID, actor.ActorID, trimmedInstallationID, []string{ServiceEventSubscriptionScopeRead}); err != nil {
			return ServiceEventSubscriptionPage{}, err
		}
	}
	principal, err := serviceEventActorPrincipal(ctx, store.db, trimmedWorkspaceID, actor)
	if err != nil {
		return ServiceEventSubscriptionPage{}, err
	}
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if strings.TrimSpace(actor.ActorType) == "human" && trimmedInstallationID != "" {
			if err := requireHumanInstallationUse(ctx, tx, trimmedWorkspaceID, actor.MemberID, trimmedInstallationID); err != nil {
				return err
			}
		}
		return recordPullRequestSubscriptionRateLimitEvent(ctx, tx, pullRequestSubscriptionRateLimitEvent{
			WorkspaceID:       trimmedWorkspaceID,
			AppInstallationID: trimmedInstallationID,
			ActorType:         actor.ActorType,
			ActorID:           actor.ActorID,
			Action:            pullRequestSubscriptionRateLimitActionList,
			TargetKind:        targetKind,
			TargetRef:         targetRef,
			ResourceKey:       options.ResourceKey,
		})
	}); err != nil {
		return ServiceEventSubscriptionPage{}, err
	}
	queryLimit := pageLimit
	if options.Page {
		queryLimit = pageLimit + 1
	}
	query, args := buildListServiceEventSubscriptionsQuery(listServiceEventSubscriptionsQueryParams{
		WorkspaceID:           trimmedWorkspaceID,
		ServiceDefinitionIDs:  serviceDefinitionIDs,
		ServiceInstallationID: trimmedInstallationID,
		SubscriptionKind:      serviceactor.SubscriptionKindPullRequest,
		TargetKind:            targetKind,
		TargetRef:             targetRef,
		Status:                status,
		ResourceKey:           options.ResourceKey,
		OwnerMemberID:         principal.MemberID,
		OwnerUserID:           serviceEventSubscriptionHumanOwnerUserID(actor, principal),
		WorkspaceWide:         strings.TrimSpace(actor.ActorType) == "human" && principal.CanManageWorkspace,
		RequestedLimit:        queryLimit,
		Offset:                offset,
		Page:                  options.Page,
	})
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return ServiceEventSubscriptionPage{}, fmt.Errorf("list service event subscriptions: %w", err)
	}
	defer rows.Close()
	subscriptions := make([]ServiceEventSubscriptionRecord, 0)
	for rows.Next() {
		subscription, err := scanServiceEventSubscriptionRecord(rows)
		if err != nil {
			return ServiceEventSubscriptionPage{}, err
		}
		subscriptions = append(subscriptions, subscription)
	}
	if err := rows.Err(); err != nil {
		return ServiceEventSubscriptionPage{}, fmt.Errorf("iterate service event subscriptions: %w", err)
	}
	hasMore := false
	nextCursor := ""
	if options.Page && len(subscriptions) > pageLimit {
		hasMore = true
		subscriptions = subscriptions[:pageLimit]
		nextCursor = strconv.Itoa(offset + pageLimit)
	}
	return ServiceEventSubscriptionPage{
		Subscriptions: subscriptions,
		Limit:         pageLimit,
		Cursor:        strings.TrimSpace(options.Cursor),
		HasMore:       hasMore,
		NextCursor:    nextCursor,
	}, nil
}

func serviceEventSubscriptionHumanOwnerUserID(actor ServiceEventActor, principal serviceEventPrincipal) string {
	if strings.TrimSpace(actor.ActorType) != "human" {
		return ""
	}
	return strings.TrimSpace(principal.UserID)
}

type serviceEventSubscriptionInstallation struct {
	ID          string
	AppID       string
	WorkspaceID string
	MemberID    string
	Handle      string
	Enabled     bool
}

type serviceEventSubscriptionWrite struct {
	WorkspaceID           string
	Provider              string
	Installation          serviceEventSubscriptionInstallation
	ProviderConnectionRef string
	Actor                 ServiceEventActor
	PrincipalMemberID     string
	Target                ServiceEventTarget
	ResourceRef           string
	ResourceKey           string
	EventFilters          []byte
	CreatedVia            string
	CorrelationID         string
	RequestedID           string
	Metadata              []byte
	ScopeSnapshot         []byte
	BindingMetadata       []byte
	NotifyPrincipal       bool
}

func (store *Store) upsertServiceEventSubscription(ctx context.Context, tx coredb.Tx, params serviceEventSubscriptionWrite) (ServiceEventSubscriptionRecord, string, bool, error) {
	serviceDefinitionID := pullRequestSubscriptionServiceDefinitionID(params.Provider)
	sourceType := pullRequestSubscriptionSourceType(params.Provider, PullRequestSubscriptionResourcePullRequest)
	sourceFilterKey := pullRequestSubscriptionSourceFilterKey(params.EventFilters)
	principalMemberID := strings.TrimSpace(params.PrincipalMemberID)
	if principalMemberID == "" {
		return ServiceEventSubscriptionRecord{}, "", false, badRequest("service event subscription principal member is required")
	}
	principalActor := params.Actor
	principalActor.AuthorizerUserID = principalMemberID
	activeSubscriptionKey := pullRequestSubscriptionKey(
		params.Installation.ID,
		params.Target.TargetKind,
		params.Target.TargetRef,
		sourceType,
		params.ResourceKey,
		sourceFilterKey,
		pullRequestSubscriptionDeliveryPolicy,
		principalMemberID,
		strconv.FormatBool(params.NotifyPrincipal),
	)

	txServiceActor := store.serviceActorStore().InTx(tx)
	serviceInstallation, err := txServiceActor.LoadInstallationForUpdate(ctx, params.Installation.ID)
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}
	if strings.TrimSpace(serviceInstallation.Status) != serviceactor.StatusActive {
		return ServiceEventSubscriptionRecord{}, "", false, storeerr.BadRequest("service installation is not active")
	}
	serviceMember, err := txServiceActor.LoadMemberByInstallation(ctx, params.Installation.ID)
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}
	serviceConnection, found, err := txServiceActor.LoadActiveConnectionForUpdate(ctx, params.Installation.ID, params.ProviderConnectionRef)
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}
	serviceConnectionID := ""
	if found {
		serviceConnectionID = serviceConnection.ID
	}
	serviceBinding, err := txServiceActor.UpsertTargetBinding(ctx, serviceactor.TargetBindingUpsert{
		WorkspaceID:              params.WorkspaceID,
		ServiceDefinitionID:      serviceDefinitionID,
		ServiceInstallationID:    params.Installation.ID,
		ServiceMemberID:          serviceMember.ID,
		ServiceKind:              serviceactor.KindApp,
		TargetKind:               params.Target.TargetKind,
		TargetRef:                params.Target.TargetRef,
		DeliveryScopeID:          params.Target.DeliveryScopeID,
		PermissionCheckScopeID:   params.Target.PermissionCheckScopeID,
		AuthoritySubjectMemberID: serviceEventAuthoritySubjectMemberID(principalActor, params.Target),
		DeliveryContract:         serviceEventDeliveryContractJSON(principalActor, params.Target),
		CreatedByMemberID:        principalMemberID,
		CreatedByActorType:       params.Actor.ActorType,
		CreatedByActorID:         params.Actor.ActorID,
		CreatedVia:               params.CreatedVia,
		ScopeSnapshot:            params.ScopeSnapshot,
		Metadata:                 []byte(`{}`),
		ActiveBindingKey:         pullRequestSubscriptionKey("service-event-target", params.WorkspaceID, params.Target.TargetKind, params.Target.TargetRef, params.Installation.ID),
	})
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}

	existing, existingFound, err := loadActiveServiceEventSubscriptionByKeyForUpdate(ctx, tx, params.WorkspaceID, activeSubscriptionKey)
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}
	subscriptionID := strings.TrimSpace(params.RequestedID)
	if subscriptionID == "" && existingFound {
		subscriptionID = existing.ID
	}
	if subscriptionID == "" {
		subscriptionID, err = identifiers.NewEntityID("trg")
		if err != nil {
			return ServiceEventSubscriptionRecord{}, "", false, fmt.Errorf("generate service event subscription id: %w", err)
		}
	}

	subscription, subscriptionAlreadyExists, err := txServiceActor.UpsertEventSubscription(ctx, serviceactor.EventSubscriptionUpsert{
		ID:                     subscriptionID,
		WorkspaceID:            params.WorkspaceID,
		ServiceDefinitionID:    serviceDefinitionID,
		ServiceInstallationID:  params.Installation.ID,
		ServiceMemberID:        serviceMember.ID,
		ServiceConnectionID:    serviceConnectionID,
		ServiceConnectionRef:   params.ProviderConnectionRef,
		ServiceTargetBindingID: serviceBinding.ID,
		ServiceKind:            serviceactor.KindApp,
		SubscriptionKind:       serviceactor.SubscriptionKindPullRequest,
		TargetKind:             params.Target.TargetKind,
		TargetRef:              params.Target.TargetRef,
		DeliveryScopeID:        params.Target.DeliveryScopeID,
		PermissionCheckScopeID: params.Target.PermissionCheckScopeID,
		OwnerMemberID:          principalMemberID,
		AuthorizerMemberID:     strings.TrimSpace(params.Actor.AuthorizerUserID),
		CreatedByActorType:     params.Actor.ActorType,
		CreatedByActorID:       params.Actor.ActorID,
		CreatedVia:             params.CreatedVia,
		ExternalResourceType:   PullRequestSubscriptionResourcePullRequest,
		ExternalResourceKey:    params.ResourceKey,
		ExternalResourceRef:    params.ResourceRef,
		EventFilterKey:         sourceFilterKey,
		EventFilters:           params.EventFilters,
		DeliveryPolicy:         pullRequestSubscriptionDeliveryPolicy,
		NotifyPrincipal:        params.NotifyPrincipal,
		Status:                 serviceactor.EventSubscriptionStatusActive,
		ActiveSubscriptionKey:  activeSubscriptionKey,
		Metadata:               params.Metadata,
	})
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}
	record, err := loadServiceEventSubscriptionByID(ctx, tx, params.WorkspaceID, subscription.ID)
	if err != nil {
		return ServiceEventSubscriptionRecord{}, "", false, err
	}
	return record, serviceBinding.ID, subscriptionAlreadyExists || existingFound, nil
}