package serviceactor

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

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

// TargetBinding records where a service actor may operate or deliver.
type TargetBinding struct {
	ID                       string
	WorkspaceID              string
	ServiceDefinitionID      string
	ServiceInstallationID    string
	ServiceMemberID          string
	ServiceKind              string
	TargetKind               string
	TargetRef                string
	DeliveryScopeID          string
	PermissionCheckScopeID   string
	AuthoritySubjectMemberID string
	DeliveryContract         []byte
	Status                   string
	DisabledReason           string
	CreatedByMemberID        string
	CreatedByActorType       string
	CreatedByActorID         string
	CreatedVia               string
	ScopeSnapshot            []byte
	Metadata                 []byte
	CreatedAt                time.Time
	UpdatedAt                time.Time
	RemovedAt                *time.Time
	ActiveBindingKey         string
}

// TargetBindingUpsert describes a target binding write.
type TargetBindingUpsert struct {
	ID                       string
	WorkspaceID              string
	ServiceDefinitionID      string
	ServiceInstallationID    string
	ServiceMemberID          string
	ServiceKind              string
	TargetKind               string
	TargetRef                string
	DeliveryScopeID          string
	PermissionCheckScopeID   string
	AuthoritySubjectMemberID string
	DeliveryContract         []byte
	CreatedByMemberID        string
	CreatedByActorType       string
	CreatedByActorID         string
	CreatedVia               string
	ScopeSnapshot            []byte
	Metadata                 []byte
	Now                      time.Time
	ActiveBindingKey         string
}

// UpsertTargetBinding creates, restores, or reuses an active target binding.
func (store *TxStore) UpsertTargetBinding(ctx context.Context, params TargetBindingUpsert) (TargetBinding, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return TargetBinding{}, err
	}
	return upsertTargetBinding(ctx, execer, params)
}

// LoadActiveTargetBindingForUpdate locks an active target binding for one service and target.
func (store *TxStore) LoadActiveTargetBindingForUpdate(ctx context.Context, workspaceID string, targetKind string, targetRef string, serviceInstallationID string) (TargetBinding, bool, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return TargetBinding{}, false, err
	}
	return loadCurrentTargetBindingForUpdate(ctx, execer, workspaceID, targetKind, targetRef, serviceInstallationID)
}

// ListActiveTargetBindingsForTarget lists active service bindings for a AgentTeam target.
func (store *Store) ListActiveTargetBindingsForTarget(ctx context.Context, workspaceID string, targetKind string, targetRef string) ([]TargetBinding, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listActiveTargetBindingsForTarget(ctx, queryer, workspaceID, targetKind, targetRef)
}

// DisableTargetBinding disables an active target binding and clears its active idempotency key.
func (store *TxStore) DisableTargetBinding(ctx context.Context, workspaceID string, targetKind string, targetRef string, serviceInstallationID string, reason string, now time.Time) (TargetBinding, bool, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return TargetBinding{}, false, err
	}
	return disableTargetBinding(ctx, execer, workspaceID, targetKind, targetRef, serviceInstallationID, reason, now)
}

func upsertTargetBinding(ctx context.Context, execer coredb.QueryExecer, params TargetBindingUpsert) (TargetBinding, error) {
	workspaceID := trim(params.WorkspaceID)
	definitionID := trim(params.ServiceDefinitionID)
	installationID := trim(params.ServiceInstallationID)
	serviceMemberID := trim(params.ServiceMemberID)
	serviceKind := trim(params.ServiceKind)
	targetKind := trim(params.TargetKind)
	targetRef := trim(params.TargetRef)
	deliveryScopeID := trim(params.DeliveryScopeID)
	permissionScopeID := trim(params.PermissionCheckScopeID)
	authoritySubjectMemberID := trim(params.AuthoritySubjectMemberID)
	createdByMemberID := trim(params.CreatedByMemberID)
	activeBindingKey := trim(params.ActiveBindingKey)
	if workspaceID == "" || definitionID == "" || installationID == "" || serviceMemberID == "" || serviceKind == "" || targetKind == "" || targetRef == "" || deliveryScopeID == "" || permissionScopeID == "" || createdByMemberID == "" {
		return TargetBinding{}, badRequest("workspaceId, service identity, target, scopes, and createdByMemberId are required")
	}
	if err := validateTargetBindingSubstrate(ctx, execer, targetBindingSubstrate{
		WorkspaceID:              workspaceID,
		ServiceDefinitionID:      definitionID,
		ServiceInstallationID:    installationID,
		ServiceMemberID:          serviceMemberID,
		ServiceKind:              serviceKind,
		TargetKind:               targetKind,
		TargetRef:                targetRef,
		DeliveryScopeID:          deliveryScopeID,
		PermissionCheckScopeID:   permissionScopeID,
		AuthoritySubjectMemberID: authoritySubjectMemberID,
		CreatedByMemberID:        createdByMemberID,
	}); err != nil {
		return TargetBinding{}, err
	}
	immutableIdentity := targetBindingImmutableIdentity{
		WorkspaceID:              workspaceID,
		ServiceDefinitionID:      definitionID,
		ServiceInstallationID:    installationID,
		ServiceMemberID:          serviceMemberID,
		ServiceKind:              serviceKind,
		TargetKind:               targetKind,
		TargetRef:                targetRef,
		DeliveryScopeID:          deliveryScopeID,
		PermissionCheckScopeID:   permissionScopeID,
		AuthoritySubjectMemberID: authoritySubjectMemberID,
		DeliveryContract:         params.DeliveryContract,
		ActiveBindingKey:         activeBindingKey,
	}
	if binding, reused, err := reuseOrRestoreTargetBinding(ctx, execer, params, immutableIdentity); err != nil {
		return TargetBinding{}, err
	} else if reused {
		return binding, nil
	}

	bindingID := trim(params.ID)
	var err error
	if bindingID == "" {
		bindingID, err = identifiers.NewEntityID("svctb")
		if err != nil {
			return TargetBinding{}, fmt.Errorf("generate service target binding id: %w", err)
		}
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO service_target_bindings (
    id, workspace_id, service_definition_id, service_installation_id,
    service_member_id, service_kind, target_kind, target_ref,
    delivery_scope_id, permission_check_scope_id, authority_subject_member_id,
    delivery_contract, status, disabled_reason, created_by_member_id,
    created_by_actor_type, created_by_actor_id, created_via,
    scope_snapshot, metadata, created_at, updated_at, active_binding_key
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, $8,
    $9, $10, $11,
    $12, 'active', '', $13,
    $14, $15, $16,
    $17, $18, $19, $19, $20
)
`, bindingID, workspaceID, definitionID, installationID, serviceMemberID, serviceKind, targetKind, targetRef, deliveryScopeID, permissionScopeID, authoritySubjectMemberID, params.DeliveryContract, createdByMemberID, trim(params.CreatedByActorType), trim(params.CreatedByActorID), trim(params.CreatedVia), params.ScopeSnapshot, params.Metadata, now, nullableString(activeBindingKey)); err != nil {
		if constraint, ok := coredb.UniqueViolationConstraint(err); ok && constraint == "service_target_bindings_active_key" {
			binding, loadErr := loadTargetBindingByActiveKey(ctx, execer, activeBindingKey)
			if loadErr != nil {
				return TargetBinding{}, loadErr
			}
			if binding.Status != StatusActive || binding.RemovedAt != nil || !activeTargetBindingReplayMatches(binding, immutableIdentity) {
				return TargetBinding{}, conflict("Active service target binding key is already bound to another immutable identity.")
			}
			return binding, nil
		}
		return TargetBinding{}, fmt.Errorf("insert service target binding: %w", mapTargetBindingWriteError(err))
	}
	return loadTargetBindingByID(ctx, execer, bindingID)
}

func reuseOrRestoreTargetBinding(ctx context.Context, execer coredb.QueryExecer, params TargetBindingUpsert, identity targetBindingImmutableIdentity) (TargetBinding, bool, error) {
	binding, found, err := loadCurrentTargetBindingForUpdate(ctx, execer, identity.WorkspaceID, identity.TargetKind, identity.TargetRef, identity.ServiceInstallationID)
	if err != nil || !found {
		return TargetBinding{}, false, err
	}
	if binding.Status == StatusActive {
		if binding.ServiceKind == KindApp &&
			appActiveTargetBindingReplayMatches(binding, identity) {
			reconciled, err := reconcileActiveAppTargetBinding(
				ctx, execer, binding, params, identity,
			)
			return reconciled, err == nil, err
		}
		if !activeTargetBindingReplayMatches(binding, identity) {
			return TargetBinding{}, false, conflict("Active service target binding replay does not match its immutable identity.")
		}
		return binding, true, nil
	}
	if binding.Status != StatusDisabled {
		return TargetBinding{}, false, conflict("service target binding is unavailable")
	}
	if !disabledTargetBindingRestoreMatches(binding, identity) {
		return TargetBinding{}, false, conflict("Disabled service target binding does not match its durable identity.")
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
UPDATE service_target_bindings
SET status = 'active',
    disabled_reason = '',
    delivery_scope_id = $4,
    permission_check_scope_id = $5,
    authority_subject_member_id = $6,
    delivery_contract = $7,
    scope_snapshot = $8,
    metadata = $9,
    updated_at = $10,
    removed_at = NULL,
    active_binding_key = $11
WHERE id = $1 AND workspace_id = $2 AND service_installation_id = $3
`, binding.ID, identity.WorkspaceID, identity.ServiceInstallationID, identity.DeliveryScopeID, identity.PermissionCheckScopeID, identity.AuthoritySubjectMemberID, params.DeliveryContract, params.ScopeSnapshot, params.Metadata, now, nullableString(identity.ActiveBindingKey)); err != nil {
		return TargetBinding{}, false, fmt.Errorf("restore service target binding: %w", mapTargetBindingWriteError(err))
	}
	restored, err := loadTargetBindingByID(ctx, execer, binding.ID)
	return restored, err == nil, err
}

type targetBindingSubstrate struct {
	WorkspaceID              string
	ServiceDefinitionID      string
	ServiceInstallationID    string
	ServiceMemberID          string
	ServiceKind              string
	TargetKind               string
	TargetRef                string
	DeliveryScopeID          string
	PermissionCheckScopeID   string
	AuthoritySubjectMemberID string
	CreatedByMemberID        string
}

type targetBindingScopeSubstrate struct {
	WorkspaceID string
	Type        string
	Status      string
	ArchivedAt  *time.Time
}

type targetBindingDefinitionSubstrate struct {
	ServiceKind string
	Status      string
}

func validateTargetBindingSubstrate(ctx context.Context, queryer coredb.QueryRower, substrate targetBindingSubstrate) error {
	definition, found, err := lockTargetBindingDefinitionSubstrate(ctx, queryer, substrate.ServiceDefinitionID)
	if err != nil {
		return err
	}
	if !found || definition.ServiceKind != substrate.ServiceKind {
		return conflict("Service target binding requires an active definition of the same kind.")
	}
	if !strings.EqualFold(definition.Status, StatusActive) {
		return conflict("Service target binding requires an active definition of the same kind.")
	}

	installation, found, err := findInstallationByIDForUpdate(ctx, queryer, substrate.ServiceInstallationID)
	if err != nil {
		return err
	}
	if !found || !installationIdentityMatches(installation, substrate.WorkspaceID, substrate.ServiceDefinitionID, substrate.ServiceKind, installation.InstanceKey) || installation.Status != StatusActive || installation.DisabledAt != nil {
		return conflict("Service target binding requires an active installation in the same workspace, definition, and kind.")
	}

	serviceMember, found, err := findMemberForUpdate(ctx, queryer, `id = $1`, trim(substrate.ServiceMemberID))
	if err != nil {
		return err
	}
	if !found || !memberSubstrateIdentityMatches(serviceMember, substrate.WorkspaceID, substrate.ServiceDefinitionID, substrate.ServiceInstallationID, serviceMember.MemberID, substrate.ServiceKind) || serviceMember.Status != StatusActive {
		return conflict("Service target binding requires an active member in the same workspace, definition, installation, and kind.")
	}
	if err := validateServiceMemberSubstrate(ctx, queryer, substrate.WorkspaceID, serviceMember.MemberID); err != nil {
		return err
	}
	if substrate.ServiceKind == KindServiceAccount {
		if err := validateServiceAccountTargetSubstrate(ctx, queryer, substrate, serviceMember); err != nil {
			return err
		}
	}

	if err := validateTargetBindingAuthoritySubject(ctx, queryer, substrate, serviceMember); err != nil {
		return err
	}
	if err := validateTargetBindingCreator(ctx, queryer, substrate.WorkspaceID, substrate.CreatedByMemberID); err != nil {
		return err
	}

	deliveryScope, found, err := lockTargetBindingScopeSubstrate(ctx, queryer, substrate.DeliveryScopeID)
	if err != nil {
		return err
	}
	if !found || !deliveryScope.activeInWorkspace(substrate.WorkspaceID) || trim(substrate.TargetKind) != trim(deliveryScope.Type) {
		return conflict("Service target binding delivery scope must be active in the same workspace and match the target kind.")
	}
	permissionScope, found, err := lockTargetBindingScopeSubstrate(ctx, queryer, substrate.PermissionCheckScopeID)
	if err != nil {
		return err
	}
	if !found || !permissionScope.activeInWorkspace(substrate.WorkspaceID) || !targetBindingPermissionScopeTypeMatches(substrate.TargetKind, permissionScope.Type) {
		return conflict("Service target binding permission-check scope must be active in the same workspace and match the target kind.")
	}
	return nil
}

func validateServiceAccountTargetSubstrate(ctx context.Context, queryer coredb.QueryRower, substrate targetBindingSubstrate, serviceMember Member) error {
	if substrate.TargetKind != KindServiceAccount ||
		substrate.DeliveryScopeID != substrate.PermissionCheckScopeID {
		return conflict("Service Account target binding must use its canonical typed target and message scope.")
	}
	var matches bool
	if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_accounts
    WHERE workspace_id = $1
      AND service_installation_id = $2
      AND service_member_id = $3
      AND author_member_id = $4
      AND message_scope_id = $5
      AND CONCAT('service-account:', id) = $6
)
`, substrate.WorkspaceID, substrate.ServiceInstallationID, substrate.ServiceMemberID, serviceMember.MemberID, substrate.DeliveryScopeID, substrate.TargetRef).Scan(&matches); err != nil {
		return fmt.Errorf("validate Service Account target binding account substrate: %w", err)
	}
	if !matches {
		return conflict("Service Account target binding must resolve to its account and message scope.")
	}
	return nil
}

func (substrate targetBindingScopeSubstrate) activeInWorkspace(workspaceID string) bool {
	return substrate.WorkspaceID == workspaceID && substrate.Status == StatusActive && substrate.ArchivedAt == nil
}

func lockTargetBindingDefinitionSubstrate(ctx context.Context, queryer coredb.QueryRower, definitionID string) (targetBindingDefinitionSubstrate, bool, error) {
	var definition targetBindingDefinitionSubstrate
	err := queryer.QueryRow(ctx, `
SELECT service_kind, status
FROM service_definitions
WHERE id = $1
FOR UPDATE
`, trim(definitionID)).Scan(&definition.ServiceKind, &definition.Status)
	if err != nil {
		if coredb.IsNotFound(err) {
			return targetBindingDefinitionSubstrate{}, false, nil
		}
		return targetBindingDefinitionSubstrate{}, false, fmt.Errorf("lock service target binding definition substrate: %w", err)
	}
	definition.ServiceKind = trim(definition.ServiceKind)
	definition.Status = trim(definition.Status)
	return definition, true, nil
}

func validateTargetBindingAuthoritySubject(ctx context.Context, queryer coredb.QueryRower, substrate targetBindingSubstrate, serviceMember Member) error {
	authoritySubjectMemberID := trim(substrate.AuthoritySubjectMemberID)
	if authoritySubjectMemberID == "" {
		if substrate.ServiceKind == KindServiceAccount {
			return conflict("Service Account target bindings require their service member as the authority subject.")
		}
		// Existing App target bindings may intentionally omit a routing authority.
		return nil
	}
	authority, found, err := lockWorkspaceMemberSubstrate(ctx, queryer, authoritySubjectMemberID)
	if err != nil {
		return err
	}
	if !found || authority.WorkspaceID != substrate.WorkspaceID {
		return conflict("Service target binding authority subject must belong to the same workspace.")
	}
	if substrate.ServiceKind == KindServiceAccount && (authority.Type != "service" || authority.ID != serviceMember.MemberID) {
		return conflict("Service Account target binding authority subject must be its service member.")
	}
	return nil
}

func validateTargetBindingCreator(ctx context.Context, queryer coredb.QueryRower, workspaceID string, createdByMemberID string) error {
	creator, found, err := lockWorkspaceMemberSubstrate(ctx, queryer, createdByMemberID)
	if err != nil {
		return err
	}
	if !found || creator.WorkspaceID != workspaceID {
		return conflict("Service target binding creator must belong to the same workspace.")
	}
	return nil
}

func lockTargetBindingScopeSubstrate(ctx context.Context, queryer coredb.QueryRower, scopeID string) (targetBindingScopeSubstrate, bool, error) {
	var scope targetBindingScopeSubstrate
	var archivedAt sql.NullTime
	err := queryer.QueryRow(ctx, `
SELECT workspace_id, type, status, archived_at
FROM scopes
WHERE id = $1
FOR UPDATE
`, trim(scopeID)).Scan(&scope.WorkspaceID, &scope.Type, &scope.Status, &archivedAt)
	if err != nil {
		if coredb.IsNotFound(err) {
			return targetBindingScopeSubstrate{}, false, nil
		}
		return targetBindingScopeSubstrate{}, false, fmt.Errorf("lock service target binding scope substrate: %w", err)
	}
	scope.WorkspaceID = trim(scope.WorkspaceID)
	scope.Type = trim(scope.Type)
	scope.Status = trim(scope.Status)
	scope.ArchivedAt = ptrFromNullTime(archivedAt)
	return scope, true, nil
}

func targetBindingPermissionScopeTypeMatches(targetKind string, scopeType string) bool {
	switch trim(targetKind) {
	case "thread":
		return trim(scopeType) == "channel" || trim(scopeType) == "dm"
	case "task":
		return trim(scopeType) == "channel"
	default:
		return trim(targetKind) == trim(scopeType)
	}
}

type targetBindingImmutableIdentity struct {
	WorkspaceID              string
	ServiceDefinitionID      string
	ServiceInstallationID    string
	ServiceMemberID          string
	ServiceKind              string
	TargetKind               string
	TargetRef                string
	DeliveryScopeID          string
	PermissionCheckScopeID   string
	AuthoritySubjectMemberID string
	DeliveryContract         []byte
	ActiveBindingKey         string
}

func activeTargetBindingReplayMatches(binding TargetBinding, identity targetBindingImmutableIdentity) bool {
	if binding.ServiceKind == KindApp {
		// App bindings are operational projections shared by every publisher
		// for one installation target. Existing rows are replayed and converged
		// by tuple instead of turning historical projection metadata into a
		// user-visible conflict.
		return appActiveTargetBindingReplayMatches(binding, identity)
	}
	if !disabledTargetBindingRestoreMatches(binding, identity) ||
		binding.DeliveryScopeID != trim(identity.DeliveryScopeID) ||
		binding.PermissionCheckScopeID != trim(identity.PermissionCheckScopeID) ||
		binding.ActiveBindingKey != trim(identity.ActiveBindingKey) {
		return false
	}
	return binding.AuthoritySubjectMemberID == trim(identity.AuthoritySubjectMemberID) &&
		model.JSONDocumentsEqual(binding.DeliveryContract, identity.DeliveryContract)
}

func appActiveTargetBindingReplayMatches(
	binding TargetBinding,
	identity targetBindingImmutableIdentity,
) bool {
	return binding.WorkspaceID == trim(identity.WorkspaceID) &&
		binding.ServiceDefinitionID == trim(identity.ServiceDefinitionID) &&
		binding.ServiceInstallationID == trim(identity.ServiceInstallationID) &&
		binding.ServiceKind == KindApp &&
		trim(identity.ServiceKind) == KindApp &&
		binding.TargetKind == trim(identity.TargetKind) &&
		binding.TargetRef == trim(identity.TargetRef)
}

func reconcileActiveAppTargetBinding(
	ctx context.Context,
	execer coredb.QueryExecer,
	binding TargetBinding,
	params TargetBindingUpsert,
	identity targetBindingImmutableIdentity,
) (TargetBinding, error) {
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	// active_binding_key is intentionally retained. It is an opaque historical
	// idempotency token, not delivery authority; rewriting it creates rollout
	// conflicts between Bot- and App-era writers. The locked target tuple is
	// the canonical App binding identity.
	if _, err := execer.Exec(ctx, `
UPDATE service_target_bindings
SET service_member_id = $2,
    delivery_scope_id = $3,
    permission_check_scope_id = $4,
    authority_subject_member_id = $5,
    delivery_contract = $6,
    scope_snapshot = $7,
    metadata = $8,
    updated_at = $9
WHERE id = $1
  AND status = 'active'
  AND removed_at IS NULL
`, binding.ID, identity.ServiceMemberID, identity.DeliveryScopeID,
		identity.PermissionCheckScopeID, nullableString(identity.AuthoritySubjectMemberID),
		identity.DeliveryContract, params.ScopeSnapshot, params.Metadata, now,
	); err != nil {
		return TargetBinding{}, fmt.Errorf(
			"reconcile active App target binding: %w",
			mapTargetBindingWriteError(err),
		)
	}
	return loadTargetBindingByID(ctx, execer, binding.ID)
}

func disabledTargetBindingRestoreMatches(binding TargetBinding, identity targetBindingImmutableIdentity) bool {
	return binding.WorkspaceID == trim(identity.WorkspaceID) &&
		binding.ServiceDefinitionID == trim(identity.ServiceDefinitionID) &&
		binding.ServiceInstallationID == trim(identity.ServiceInstallationID) &&
		binding.ServiceMemberID == trim(identity.ServiceMemberID) &&
		binding.ServiceKind == trim(identity.ServiceKind) &&
		binding.TargetKind == trim(identity.TargetKind) &&
		binding.TargetRef == trim(identity.TargetRef)
}

func loadCurrentTargetBindingForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, targetKind string, targetRef string, serviceInstallationID string) (TargetBinding, bool, error) {
	row := queryer.QueryRow(ctx, targetBindingSelectSQL()+`
WHERE workspace_id = $1
  AND target_kind = $2
  AND target_ref = $3
  AND service_installation_id = $4
  AND removed_at IS NULL
LIMIT 1
FOR UPDATE
`, trim(workspaceID), trim(targetKind), trim(targetRef), trim(serviceInstallationID))
	binding, err := scanTargetBinding(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return TargetBinding{}, false, nil
		}
		return TargetBinding{}, false, fmt.Errorf("load current service target binding: %w", err)
	}
	return binding, true, nil
}

func loadTargetBindingByID(ctx context.Context, queryer coredb.QueryRower, bindingID string) (TargetBinding, error) {
	row := queryer.QueryRow(ctx, targetBindingSelectSQL()+` WHERE id = $1`, trim(bindingID))
	binding, err := scanTargetBinding(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return TargetBinding{}, notFound("service target binding not found")
		}
		return TargetBinding{}, fmt.Errorf("load service target binding: %w", err)
	}
	return binding, nil
}

func loadTargetBindingByActiveKey(ctx context.Context, queryer coredb.QueryRower, activeKey string) (TargetBinding, error) {
	row := queryer.QueryRow(ctx, targetBindingSelectSQL()+`
WHERE active_binding_key = $1
LIMIT 1
`, trim(activeKey))
	binding, err := scanTargetBinding(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return TargetBinding{}, notFound("service target binding not found")
		}
		return TargetBinding{}, fmt.Errorf("load service target binding by active key: %w", err)
	}
	return binding, nil
}

func listActiveTargetBindingsForTarget(ctx context.Context, queryer coredb.Queryer, workspaceID string, targetKind string, targetRef string) ([]TargetBinding, error) {
	rows, err := queryer.Query(ctx, targetBindingSelectSQL()+`
WHERE workspace_id = $1
  AND target_kind = $2
  AND target_ref = $3
  AND status = 'active'
  AND removed_at IS NULL
ORDER BY created_at ASC, id ASC
`, trim(workspaceID), trim(targetKind), trim(targetRef))
	if err != nil {
		return nil, fmt.Errorf("list service target bindings: %w", err)
	}
	defer rows.Close()
	bindings := make([]TargetBinding, 0)
	for rows.Next() {
		binding, err := scanTargetBinding(rows)
		if err != nil {
			return nil, fmt.Errorf("scan service target binding: %w", err)
		}
		bindings = append(bindings, binding)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service target bindings: %w", err)
	}
	return bindings, nil
}

func disableTargetBinding(ctx context.Context, execer coredb.QueryExecer, workspaceID string, targetKind string, targetRef string, serviceInstallationID string, reason string, now time.Time) (TargetBinding, bool, error) {
	trimmedWorkspaceID := trim(workspaceID)
	trimmedTargetKind := trim(targetKind)
	trimmedTargetRef := trim(targetRef)
	trimmedInstallationID := trim(serviceInstallationID)
	if trimmedWorkspaceID == "" || trimmedTargetKind == "" || trimmedTargetRef == "" || trimmedInstallationID == "" {
		return TargetBinding{}, false, badRequest("workspaceId, target, and serviceInstallationId are required")
	}
	binding, found, err := loadCurrentTargetBindingForUpdate(ctx, execer, trimmedWorkspaceID, trimmedTargetKind, trimmedTargetRef, trimmedInstallationID)
	if err != nil || !found {
		return TargetBinding{}, found, err
	}
	if binding.Status == StatusDisabled {
		return binding, true, nil
	}
	if binding.Status != StatusActive {
		return TargetBinding{}, true, conflict("service target binding is unavailable")
	}
	if now.IsZero() {
		now = time.Now().UTC()
	} else {
		now = now.UTC()
	}
	if _, err := execer.Exec(ctx, `
UPDATE service_target_bindings
SET status = 'disabled',
    disabled_reason = $5,
    active_binding_key = NULL,
    updated_at = $6
WHERE workspace_id = $1
  AND target_kind = $2
  AND target_ref = $3
  AND service_installation_id = $4
  AND removed_at IS NULL
`, trimmedWorkspaceID, trimmedTargetKind, trimmedTargetRef, trimmedInstallationID, trim(reason), now); err != nil {
		return TargetBinding{}, true, fmt.Errorf("disable service target binding: %w", err)
	}
	disabled, err := loadTargetBindingByID(ctx, execer, binding.ID)
	if err != nil {
		return TargetBinding{}, true, err
	}
	return disabled, true, nil
}

func targetBindingSelectSQL() string {
	return `
SELECT id, workspace_id, service_definition_id, service_installation_id,
       service_member_id, service_kind, target_kind, target_ref,
       delivery_scope_id, permission_check_scope_id, authority_subject_member_id,
       delivery_contract, status, disabled_reason, created_by_member_id,
       created_by_actor_type, created_by_actor_id, created_via,
       scope_snapshot, metadata, created_at, updated_at, removed_at,
       active_binding_key
FROM service_target_bindings`
}

func scanTargetBinding(row coredb.Row) (TargetBinding, error) {
	var binding TargetBinding
	var removedAt sql.NullTime
	var activeKey sql.NullString
	if err := row.Scan(
		&binding.ID,
		&binding.WorkspaceID,
		&binding.ServiceDefinitionID,
		&binding.ServiceInstallationID,
		&binding.ServiceMemberID,
		&binding.ServiceKind,
		&binding.TargetKind,
		&binding.TargetRef,
		&binding.DeliveryScopeID,
		&binding.PermissionCheckScopeID,
		&binding.AuthoritySubjectMemberID,
		&binding.DeliveryContract,
		&binding.Status,
		&binding.DisabledReason,
		&binding.CreatedByMemberID,
		&binding.CreatedByActorType,
		&binding.CreatedByActorID,
		&binding.CreatedVia,
		&binding.ScopeSnapshot,
		&binding.Metadata,
		&binding.CreatedAt,
		&binding.UpdatedAt,
		&removedAt,
		&activeKey,
	); err != nil {
		return TargetBinding{}, err
	}
	binding.ID = trim(binding.ID)
	binding.WorkspaceID = trim(binding.WorkspaceID)
	binding.ServiceDefinitionID = trim(binding.ServiceDefinitionID)
	binding.ServiceInstallationID = trim(binding.ServiceInstallationID)
	binding.ServiceMemberID = trim(binding.ServiceMemberID)
	binding.ServiceKind = trim(binding.ServiceKind)
	binding.TargetKind = trim(binding.TargetKind)
	binding.TargetRef = trim(binding.TargetRef)
	binding.Status = trim(binding.Status)
	binding.RemovedAt = ptrFromNullTime(removedAt)
	if activeKey.Valid {
		binding.ActiveBindingKey = trim(activeKey.String)
	}
	return binding, nil
}

func mapTargetBindingWriteError(err error) error {
	if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
		switch constraint {
		case "service_target_bindings_active_key":
			return conflict("Service target binding already exists.")
		}
	}
	return err
}