package catalogapp

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"slices"
	"strings"

	"github.com/pingcap-inc/nexuscrm-contract/pkg/appauthority"
)

func (a *Authority) StageHandlerBinding(ctx context.Context, request HandlerBindingRequest) (HandlerBinding, error) {
	operationBound := strings.TrimSpace(request.OperationID) != ""
	activeBound := strings.TrimSpace(request.AppInstallationID) != "" || strings.TrimSpace(request.AppReleaseDigest) != ""
	if operationBound == activeBound {
		return HandlerBinding{}, fmt.Errorf("exactly one operation or active AppInstallation binding target is required")
	}
	if activeBound {
		if err := appauthority.ValidateAppInstallationID(request.AppInstallationID); err != nil {
			return HandlerBinding{}, err
		}
		if err := requireDigest("active AppReleaseDigest", request.AppReleaseDigest); err != nil {
			return HandlerBinding{}, err
		}
	}
	if err := appauthority.ValidateCapabilityID(request.CapabilityID); err != nil {
		return HandlerBinding{}, err
	}
	if err := appauthority.ValidateHandlerRef(request.HandlerRef); err != nil {
		return HandlerBinding{}, err
	}
	if strings.TrimSpace(request.WorkloadID) == "" || len(request.WorkloadID) > 1024 {
		return HandlerBinding{}, fmt.Errorf("workload ID is required and must fit 1024 bytes")
	}
	if err := requireDigest("service release digest", request.ServiceReleaseDigest); err != nil {
		return HandlerBinding{}, err
	}
	if err := requireDigest("compatibility receipt", request.CompatibilityReceiptDigest); err != nil {
		return HandlerBinding{}, err
	}
	if request.ExpectedOwnershipEpoch == ^uint64(0) {
		return HandlerBinding{}, fmt.Errorf("ownership epoch exhausted")
	}
	tx, err := a.db.BeginTx(ctx, nil)
	if err != nil {
		return HandlerBinding{}, err
	}
	defer rollback(tx)
	var operation Operation
	var installation installationRecord
	if operationBound {
		operation, installation, err = bindingOperationForUpdate(ctx, tx, request.OperationID)
		if err != nil {
			return HandlerBinding{}, err
		}
		if operation.Kind == OperationRemove || operation.State == StateFailed || operation.Step != "CANDIDATE_COMMITTED" {
			return HandlerBinding{}, ErrInvalidTransition
		}
	} else {
		operation, installation, err = activeBindingTargetForUpdate(ctx, tx, request.AppInstallationID, request.AppReleaseDigest)
		if err != nil {
			return HandlerBinding{}, err
		}
	}
	var kind, handlerRef string
	var requiredJSON []byte
	err = tx.QueryRowContext(ctx, `SELECT Kind, COALESCE(HandlerRef, ''), RequiredRuntimeCapabilities
FROM _System_CatalogAppReleaseCapability WHERE AppId = ? AND AppReleaseDigest = ? AND CapabilityId = ? FOR UPDATE`,
		operation.AppID, operation.TargetAppReleaseDigest, request.CapabilityID).Scan(&kind, &handlerRef, &requiredJSON)
	if errors.Is(err, sql.ErrNoRows) {
		return HandlerBinding{}, ErrNotFound
	}
	if err != nil {
		return HandlerBinding{}, err
	}
	if kind != appauthority.CapabilityKindServiceHandler || handlerRef != request.HandlerRef {
		return HandlerBinding{}, ErrConflict
	}
	var required []string
	if err := json.Unmarshal(requiredJSON, &required); err != nil {
		return HandlerBinding{}, err
	}
	if !operationBound {
		if err := validateActiveHandlerProjectionCapabilityTx(
			ctx, tx, installation, request.CapabilityID,
		); err != nil {
			return HandlerBinding{}, err
		}
	}
	grantedCapabilities, authorizedWorkloads := installation.CandidateCapabilities, installation.CandidateWorkloads
	if !operationBound {
		grantedCapabilities, authorizedWorkloads = installation.ActiveCapabilities, installation.ActiveWorkloads
	}
	if !isSubset(required, grantedCapabilities) || !slices.Contains(authorizedWorkloads, request.WorkloadID) {
		return HandlerBinding{}, fmt.Errorf("%w: binding exceeds the committed candidate grants", ErrConflict)
	}
	grantOperation := operation
	if !operationBound {
		operationID, found, lookupErr := activeHandlerProjectionOperationIDTx(
			ctx, tx, installation,
		)
		if lookupErr != nil {
			return HandlerBinding{}, lookupErr
		}
		if found {
			grantOperation.OperationID = operationID
		} else {
			grantOperation.OperationID = ""
		}
	}
	if grantOperation.OperationID != "" {
		consumerGrants, readErr := readOperationConsumerGrantsTx(
			ctx, tx, grantOperation,
		)
		if readErr != nil {
			return HandlerBinding{}, readErr
		}
		canonical, canonicalErr := canonicalConsumerGrantShape(consumerGrants)
		if canonicalErr != nil || !slices.Equal(consumerGrants, canonical) {
			return HandlerBinding{}, fmt.Errorf(
				"%w: operation consumer grant set is not canonical",
				ErrCorrupt,
			)
		}
		if commitmentErr := ensureConsumerGrantSetForOperationRetryTx(
			ctx, tx, grantOperation, consumerGrants,
		); commitmentErr != nil {
			return HandlerBinding{}, commitmentErr
		}
		if grantErr := validateHandlerConsumerGrantAuthority(
			required, request.WorkloadID, consumerGrants,
		); grantErr != nil {
			return HandlerBinding{}, grantErr
		}
	}
	newEpoch := request.ExpectedOwnershipEpoch + 1
	naturalKey := hashKey(operation.AppInstallationID, request.CapabilityID, epochText(newEpoch))
	var existing HandlerBinding
	var rawExistingEpoch []byte
	err = tx.QueryRowContext(ctx, `SELECT HandlerBindingId, OrgId, AppInstallationId, AppReleaseDigest, CapabilityId,
HandlerRef, WorkloadId, ServiceReleaseDigest, OwnershipEpoch, State, CompatibilityReceiptDigest
FROM _System_CatalogHandlerBinding WHERE NaturalKey = ? FOR UPDATE`, naturalKey).Scan(
		&existing.HandlerBindingID, &existing.OrgID, &existing.AppInstallationID, &existing.AppReleaseDigest,
		&existing.CapabilityID, &existing.HandlerRef, &existing.WorkloadID, &existing.ServiceReleaseDigest,
		&rawExistingEpoch, &existing.State, &existing.CompatibilityReceiptDigest)
	if err == nil {
		existing.OwnershipEpoch, err = parseEpoch(rawExistingEpoch)
		if err != nil {
			return HandlerBinding{}, err
		}
		if existing.OrgID != operation.OrgID || existing.AppInstallationID != operation.AppInstallationID ||
			existing.AppReleaseDigest != operation.TargetAppReleaseDigest || existing.CapabilityID != request.CapabilityID ||
			existing.HandlerRef != request.HandlerRef || existing.WorkloadID != request.WorkloadID ||
			existing.ServiceReleaseDigest != request.ServiceReleaseDigest || existing.CompatibilityReceiptDigest != request.CompatibilityReceiptDigest {
			return HandlerBinding{}, ErrConflict
		}
		if existing.State == BindingFailed {
			return HandlerBinding{}, fmt.Errorf("%w: failed handler ownership epochs are never reused", ErrInvalidTransition)
		}
		if err := tx.Commit(); err != nil {
			return HandlerBinding{}, err
		}
		return existing, nil
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return HandlerBinding{}, err
	}
	var rawCurrent []byte
	err = tx.QueryRowContext(ctx, `SELECT OwnershipEpoch FROM _System_CatalogHandlerBinding
WHERE AppInstallationId = ? AND CapabilityId = ? ORDER BY OwnershipEpoch DESC LIMIT 1 FOR UPDATE`, operation.AppInstallationID, request.CapabilityID).Scan(&rawCurrent)
	current := uint64(0)
	if err == nil {
		current, err = parseEpoch(rawCurrent)
		if err != nil {
			return HandlerBinding{}, err
		}
	} else if !errors.Is(err, sql.ErrNoRows) {
		return HandlerBinding{}, err
	}
	if current != request.ExpectedOwnershipEpoch {
		return HandlerBinding{}, ErrConflict
	}
	id, err := a.newID(a.now())
	if err != nil {
		return HandlerBinding{}, err
	}
	if err := appauthority.ValidateHandlerBindingID(id); err != nil {
		return HandlerBinding{}, err
	}
	_, err = tx.ExecContext(ctx, `INSERT INTO _System_CatalogHandlerBinding
(HandlerBindingId, OrgId, AppInstallationId, AppReleaseDigest, CapabilityId, HandlerRef, WorkloadId,
ServiceReleaseDigest, OwnershipEpoch, State, CompatibilityReceiptDigest, NaturalKey, ActiveKey, OperationId,
CreatedDate, LastModifiedDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'STAGED', ?, ?, NULL, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6))`,
		id, operation.OrgID, operation.AppInstallationID, operation.TargetAppReleaseDigest, request.CapabilityID,
		request.HandlerRef, request.WorkloadID, request.ServiceReleaseDigest, epochText(newEpoch),
		request.CompatibilityReceiptDigest, naturalKey, nullableString(operation.OperationID))
	if isDuplicate(err) {
		return HandlerBinding{}, ErrConflict
	}
	if err != nil {
		return HandlerBinding{}, err
	}
	if err := tx.Commit(); err != nil {
		return HandlerBinding{}, err
	}
	return HandlerBinding{HandlerBindingID: id, OrgID: operation.OrgID, AppInstallationID: operation.AppInstallationID,
		AppReleaseDigest: operation.TargetAppReleaseDigest, CapabilityID: request.CapabilityID, HandlerRef: request.HandlerRef,
		WorkloadID: request.WorkloadID, ServiceReleaseDigest: request.ServiceReleaseDigest, OwnershipEpoch: newEpoch,
		State: BindingStaged, CompatibilityReceiptDigest: request.CompatibilityReceiptDigest}, nil
}

func (a *Authority) StageFrontendBinding(ctx context.Context, request FrontendBindingRequest) (FrontendBinding, error) {
	operationBound := strings.TrimSpace(request.OperationID) != ""
	activeBound := strings.TrimSpace(request.AppInstallationID) != "" || strings.TrimSpace(request.AppReleaseDigest) != ""
	if operationBound == activeBound {
		return FrontendBinding{}, fmt.Errorf("exactly one operation or active AppInstallation binding target is required")
	}
	if activeBound {
		if err := appauthority.ValidateAppInstallationID(request.AppInstallationID); err != nil {
			return FrontendBinding{}, err
		}
		if err := requireDigest("active AppReleaseDigest", request.AppReleaseDigest); err != nil {
			return FrontendBinding{}, err
		}
	}
	if err := requireDigest("frontend release digest", request.FrontendReleaseDigest); err != nil {
		return FrontendBinding{}, err
	}
	if err := requireDigest("compatibility receipt", request.CompatibilityReceiptDigest); err != nil {
		return FrontendBinding{}, err
	}
	tx, err := a.db.BeginTx(ctx, nil)
	if err != nil {
		return FrontendBinding{}, err
	}
	defer rollback(tx)
	var operation Operation
	if operationBound {
		operation, _, err = bindingOperationForUpdate(ctx, tx, request.OperationID)
		if err != nil {
			return FrontendBinding{}, err
		}
		if operation.Kind == OperationRemove || operation.State == StateFailed || operation.Step != "CANDIDATE_COMMITTED" {
			return FrontendBinding{}, ErrInvalidTransition
		}
	} else {
		operation, _, err = activeBindingTargetForUpdate(ctx, tx, request.AppInstallationID, request.AppReleaseDigest)
		if err != nil {
			return FrontendBinding{}, err
		}
	}
	manifest, err := readManifestTx(ctx, tx, operation.AppID, operation.TargetAppReleaseDigest)
	if err != nil {
		return FrontendBinding{}, err
	}
	if manifest.FrontendCompatibility.Mode != appauthority.FrontendModeRequired {
		return FrontendBinding{}, ErrInvalidTransition
	}
	var existing FrontendBinding
	operationKey := ""
	stagingKey := ""
	lookupColumn, lookupValue := "OperationKey", ""
	if operationBound {
		operationKey = hashKey(request.OperationID)
		lookupValue = operationKey
	} else {
		stagingKey = hashKey(operation.AppInstallationID, "frontend-staged")
		lookupColumn, lookupValue = "StagingKey", stagingKey
	}
	err = tx.QueryRowContext(ctx, `SELECT FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest,
FrontendReleaseDigest, State, CompatibilityReceiptDigest FROM _System_CatalogFrontendBinding WHERE `+lookupColumn+` = ? FOR UPDATE`, lookupValue).Scan(
		&existing.FrontendBindingID, &existing.OrgID, &existing.AppInstallationID, &existing.AppReleaseDigest,
		&existing.FrontendReleaseDigest, &existing.State, &existing.CompatibilityReceiptDigest)
	if err == nil {
		if existing.FrontendReleaseDigest != request.FrontendReleaseDigest || existing.CompatibilityReceiptDigest != request.CompatibilityReceiptDigest {
			return FrontendBinding{}, ErrConflict
		}
		if err := tx.Commit(); err != nil {
			return FrontendBinding{}, err
		}
		return existing, nil
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return FrontendBinding{}, err
	}
	id, err := a.newID(a.now())
	if err != nil {
		return FrontendBinding{}, err
	}
	_, err = tx.ExecContext(ctx, `INSERT INTO _System_CatalogFrontendBinding
(FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest, FrontendReleaseDigest, State,
CompatibilityReceiptDigest, ActiveKey, OperationId, OperationKey, StagingKey, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, 'STAGED', ?, NULL, ?, ?, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6))`, id, operation.OrgID,
		operation.AppInstallationID, operation.TargetAppReleaseDigest, request.FrontendReleaseDigest,
		request.CompatibilityReceiptDigest, nullableString(operation.OperationID), nullableString(operationKey), nullableString(stagingKey))
	if isDuplicate(err) {
		return FrontendBinding{}, ErrConflict
	}
	if err != nil {
		return FrontendBinding{}, err
	}
	if err := tx.Commit(); err != nil {
		return FrontendBinding{}, err
	}
	return FrontendBinding{FrontendBindingID: id, OrgID: operation.OrgID, AppInstallationID: operation.AppInstallationID,
		AppReleaseDigest: operation.TargetAppReleaseDigest, FrontendReleaseDigest: request.FrontendReleaseDigest,
		State: BindingStaged, CompatibilityReceiptDigest: request.CompatibilityReceiptDigest}, nil
}

func bindingOperationForUpdate(ctx context.Context, tx *sql.Tx, operationID string) (Operation, installationRecord, error) {
	authority := &Authority{}
	operation, err := authority.readOperation(ctx, tx, operationID, true)
	if err != nil {
		return Operation{}, installationRecord{}, err
	}
	installation, err := readInstallationForUpdate(ctx, tx, operation.AppInstallationID)
	if err != nil {
		return Operation{}, installationRecord{}, err
	}
	if installation.ActiveOperationID != operationID {
		return Operation{}, installationRecord{}, ErrConflict
	}
	return operation, installation, nil
}

func activeBindingTargetForUpdate(ctx context.Context, tx *sql.Tx, installationID, releaseDigest string) (Operation, installationRecord, error) {
	installation, err := readInstallationForUpdate(ctx, tx, installationID)
	if err != nil {
		return Operation{}, installationRecord{}, err
	}
	if installation.State != StateActive || installation.ActiveOperationID != "" || installation.ActiveRelease != releaseDigest {
		return Operation{}, installationRecord{}, ErrConflict
	}
	return Operation{AppInstallationID: installation.ID, OrgID: installation.OrgID, AppID: installation.AppID,
		State: StateActive, Step: "ACTIVATED", TargetAppReleaseDigest: installation.ActiveRelease}, installation, nil
}

func readManifestTx(ctx context.Context, tx *sql.Tx, appID, releaseDigest string) (appauthority.AppManifest, error) {
	var raw []byte
	err := tx.QueryRowContext(ctx, `SELECT CanonicalManifest FROM _System_CatalogAppRelease
WHERE AppId = ? AND AppReleaseDigest = ? FOR UPDATE`, appID, releaseDigest).Scan(&raw)
	if errors.Is(err, sql.ErrNoRows) {
		return appauthority.AppManifest{}, ErrNotFound
	}
	if err != nil {
		return appauthority.AppManifest{}, err
	}
	return appauthority.ParseAppManifest(raw)
}

func isSubset(required, granted []string) bool {
	for _, value := range required {
		if !slices.Contains(granted, value) {
			return false
		}
	}
	return true
}

func activateBindingsTx(ctx context.Context, tx *sql.Tx, operation Operation) error {
	if err := verifyOperationConsumerGrantsTx(ctx, tx, operation); err != nil {
		return err
	}
	if err := verifyOperationIntegrationBindingsTx(ctx, tx, operation); err != nil {
		return err
	}
	if operation.Kind == OperationRemove {
		if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding SET State = 'DRAINING', ActiveKey = NULL,
LastModifiedDate = CURRENT_TIMESTAMP(6) WHERE AppInstallationId = ? AND State = 'ACTIVE'`, operation.AppInstallationID); err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding SET State = 'DRAINING', ActiveKey = NULL,
LastModifiedDate = CURRENT_TIMESTAMP(6) WHERE AppInstallationId = ? AND State = 'ACTIVE'`, operation.AppInstallationID); err != nil {
			return err
		}
		return nil
	}
	if err := validateStagedBindingCoverage(ctx, tx, operation); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding SET State = 'DRAINING', ActiveKey = NULL,
LastModifiedDate = CURRENT_TIMESTAMP(6) WHERE AppInstallationId = ? AND State = 'ACTIVE'`, operation.AppInstallationID); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding SET State = 'DRAINING', ActiveKey = NULL,
LastModifiedDate = CURRENT_TIMESTAMP(6) WHERE AppInstallationId = ? AND State = 'ACTIVE'`, operation.AppInstallationID); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding SET State = 'ACTIVE', ActiveKey = SHA2(CONCAT(AppInstallationId, CHAR(0), CapabilityId), 256),
LastModifiedDate = CURRENT_TIMESTAMP(6) WHERE OperationId = ? AND State = 'STAGED'`, operation.OperationID); err != nil {
		if isDuplicate(err) {
			return ErrConflict
		}
		return err
	}
	if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding SET State = 'ACTIVE', ActiveKey = SHA2(CONCAT(AppInstallationId, CHAR(0), 'frontend'), 256),
StagingKey = NULL, LastModifiedDate = CURRENT_TIMESTAMP(6) WHERE OperationId = ? AND State = 'STAGED'`, operation.OperationID); err != nil {
		if isDuplicate(err) {
			return ErrConflict
		}
		return err
	}
	return nil
}

func validateStagedBindingCoverage(ctx context.Context, tx *sql.Tx, operation Operation) error {
	required, err := operationHandlerProjectionCapabilitiesTx(
		ctx, tx, operation.OperationID, operation.AppID,
		operation.TargetAppReleaseDigest, nil, nil,
		operation.AppInstallationID,
	)
	if err != nil {
		return err
	}
	rows, err := tx.QueryContext(ctx, `SELECT CapabilityId, OrgId, AppInstallationId, AppReleaseDigest FROM _System_CatalogHandlerBinding
WHERE OperationId = ? AND State = 'STAGED' ORDER BY CapabilityId FOR UPDATE`, operation.OperationID)
	if err != nil {
		return err
	}
	var staged []string
	for rows.Next() {
		var capability, orgID, installationID, releaseDigest string
		if err := rows.Scan(&capability, &orgID, &installationID, &releaseDigest); err != nil {
			rows.Close()
			return err
		}
		if orgID != operation.OrgID || installationID != operation.AppInstallationID || releaseDigest != operation.TargetAppReleaseDigest {
			rows.Close()
			return fmt.Errorf("%w: staged HandlerBinding disagrees with its operation", ErrConflict)
		}
		staged = append(staged, capability)
	}
	if err := rows.Close(); err != nil {
		return err
	}
	if !slices.Equal(required, staged) {
		return fmt.Errorf("%w: staged HandlerBindings do not cover the release capabilities", ErrNotReady)
	}
	manifest, err := readManifestTx(ctx, tx, operation.AppID, operation.TargetAppReleaseDigest)
	if err != nil {
		return err
	}
	rows, err = tx.QueryContext(ctx, `SELECT FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest FROM _System_CatalogFrontendBinding
WHERE OperationId = ? AND State = 'STAGED' FOR UPDATE`, operation.OperationID)
	if err != nil {
		return err
	}
	frontendCount := 0
	for rows.Next() {
		frontendCount++
		var bindingID, orgID, installationID, releaseDigest string
		if err := rows.Scan(&bindingID, &orgID, &installationID, &releaseDigest); err != nil {
			rows.Close()
			return err
		}
		if orgID != operation.OrgID || installationID != operation.AppInstallationID || releaseDigest != operation.TargetAppReleaseDigest {
			rows.Close()
			return fmt.Errorf("%w: staged FrontendBinding disagrees with its operation", ErrConflict)
		}
	}
	if err := rows.Close(); err != nil {
		return err
	}
	if (manifest.FrontendCompatibility.Mode == appauthority.FrontendModeRequired && frontendCount != 1) ||
		(manifest.FrontendCompatibility.Mode == appauthority.FrontendModeNone && frontendCount != 0) {
		return fmt.Errorf("%w: staged frontend binding does not match the release", ErrNotReady)
	}
	return nil
}

// ActivateStagedHandlerBinding performs a service-only binding ownership flip.
// It never changes AppRelease, AppInstallation lifecycle, CatalogRevision, or
// Runtime security projection state.
func (a *Authority) ActivateStagedHandlerBinding(ctx context.Context, bindingID string, expectedOwnershipEpoch uint64) (HandlerBinding, error) {
	if err := appauthority.ValidateHandlerBindingID(bindingID); err != nil {
		return HandlerBinding{}, err
	}
	if expectedOwnershipEpoch == ^uint64(0) {
		return HandlerBinding{}, fmt.Errorf("ownership epoch exhausted")
	}
	tx, err := a.db.BeginTx(ctx, nil)
	if err != nil {
		return HandlerBinding{}, err
	}
	defer rollback(tx)
	installationID, err := handlerBindingInstallationID(ctx, tx, bindingID)
	if err != nil {
		return HandlerBinding{}, err
	}
	installation, err := readInstallationForUpdate(ctx, tx, installationID)
	if err != nil {
		return HandlerBinding{}, err
	}
	var binding HandlerBinding
	var rawEpoch []byte
	var operationID sql.NullString
	err = tx.QueryRowContext(ctx, `SELECT HandlerBindingId, OrgId, AppInstallationId, AppReleaseDigest,
CapabilityId, HandlerRef, WorkloadId, ServiceReleaseDigest, OwnershipEpoch, State, CompatibilityReceiptDigest, OperationId
FROM _System_CatalogHandlerBinding WHERE HandlerBindingId = ? FOR UPDATE`, bindingID).Scan(&binding.HandlerBindingID,
		&binding.OrgID, &binding.AppInstallationID, &binding.AppReleaseDigest, &binding.CapabilityID, &binding.HandlerRef,
		&binding.WorkloadID, &binding.ServiceReleaseDigest, &rawEpoch, &binding.State, &binding.CompatibilityReceiptDigest, &operationID)
	if errors.Is(err, sql.ErrNoRows) {
		return HandlerBinding{}, ErrNotFound
	}
	if err != nil {
		return HandlerBinding{}, err
	}
	binding.OwnershipEpoch, err = parseEpoch(rawEpoch)
	if err != nil {
		return HandlerBinding{}, err
	}
	if operationID.Valid {
		return HandlerBinding{}, ErrInvalidTransition
	}
	if binding.OwnershipEpoch != expectedOwnershipEpoch+1 {
		return HandlerBinding{}, ErrConflict
	}
	if binding.State == BindingActive {
		if err := tx.Commit(); err != nil {
			return HandlerBinding{}, err
		}
		return binding, nil
	}
	if binding.State != BindingStaged {
		return HandlerBinding{}, ErrInvalidTransition
	}
	if installation.OrgID != binding.OrgID || installation.State != StateActive || installation.ActiveOperationID != "" ||
		installation.ActiveRelease != binding.AppReleaseDigest {
		return HandlerBinding{}, ErrConflict
	}
	if err := validateActiveHandlerProjectionCapabilityTx(
		ctx, tx, installation, binding.CapabilityID,
	); err != nil {
		return HandlerBinding{}, err
	}
	var rawPrior []byte
	err = tx.QueryRowContext(ctx, `SELECT OwnershipEpoch FROM _System_CatalogHandlerBinding
WHERE AppInstallationId = ? AND CapabilityId = ? AND HandlerBindingId <> ?
ORDER BY OwnershipEpoch DESC LIMIT 1 FOR UPDATE`, binding.AppInstallationID, binding.CapabilityID, binding.HandlerBindingID).Scan(&rawPrior)
	priorEpoch := uint64(0)
	if err == nil {
		priorEpoch, err = parseEpoch(rawPrior)
		if err != nil {
			return HandlerBinding{}, err
		}
	} else if !errors.Is(err, sql.ErrNoRows) {
		return HandlerBinding{}, err
	}
	if priorEpoch != expectedOwnershipEpoch {
		return HandlerBinding{}, ErrConflict
	}
	if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding
SET State = 'DRAINING', ActiveKey = NULL, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE AppInstallationId = ? AND CapabilityId = ? AND State = 'ACTIVE'`, binding.AppInstallationID, binding.CapabilityID); err != nil {
		return HandlerBinding{}, err
	}
	result, err := tx.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding
SET State = 'ACTIVE', ActiveKey = SHA2(CONCAT(AppInstallationId, CHAR(0), CapabilityId), 256), LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE HandlerBindingId = ? AND State = 'STAGED' AND OperationId IS NULL AND OwnershipEpoch = ?`,
		binding.HandlerBindingID, epochText(binding.OwnershipEpoch))
	if isDuplicate(err) {
		return HandlerBinding{}, ErrConflict
	}
	if err != nil {
		return HandlerBinding{}, err
	}
	affected, err := result.RowsAffected()
	if err != nil || affected != 1 {
		return HandlerBinding{}, ErrConflict
	}
	if err := tx.Commit(); err != nil {
		return HandlerBinding{}, err
	}
	binding.State = BindingActive
	return binding, nil
}

// ActivateStagedFrontendBinding performs a frontend-only binding ownership
// flip fenced by the exact current active binding ID. An empty expected ID is
// valid only when no frontend binding is active.
func (a *Authority) ActivateStagedFrontendBinding(ctx context.Context, bindingID, expectedActiveBindingID string) (FrontendBinding, error) {
	if strings.TrimSpace(bindingID) == "" {
		return FrontendBinding{}, fmt.Errorf("frontend binding ID is required")
	}
	tx, err := a.db.BeginTx(ctx, nil)
	if err != nil {
		return FrontendBinding{}, err
	}
	defer rollback(tx)
	installationID, err := frontendBindingInstallationID(ctx, tx, bindingID)
	if err != nil {
		return FrontendBinding{}, err
	}
	installation, err := readInstallationForUpdate(ctx, tx, installationID)
	if err != nil {
		return FrontendBinding{}, err
	}
	var binding FrontendBinding
	var operationID, activationPredecessorID sql.NullString
	err = tx.QueryRowContext(ctx, `SELECT FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest,
FrontendReleaseDigest, State, CompatibilityReceiptDigest, OperationId, ActivationPredecessorId FROM _System_CatalogFrontendBinding
WHERE FrontendBindingId = ? FOR UPDATE`, bindingID).Scan(&binding.FrontendBindingID, &binding.OrgID,
		&binding.AppInstallationID, &binding.AppReleaseDigest, &binding.FrontendReleaseDigest, &binding.State,
		&binding.CompatibilityReceiptDigest, &operationID, &activationPredecessorID)
	if errors.Is(err, sql.ErrNoRows) {
		return FrontendBinding{}, ErrNotFound
	}
	if err != nil {
		return FrontendBinding{}, err
	}
	if operationID.Valid {
		return FrontendBinding{}, ErrInvalidTransition
	}
	if binding.State == BindingActive {
		if activationPredecessorID.String != expectedActiveBindingID {
			return FrontendBinding{}, ErrConflict
		}
		if err := tx.Commit(); err != nil {
			return FrontendBinding{}, err
		}
		return binding, nil
	}
	if binding.State != BindingStaged {
		return FrontendBinding{}, ErrInvalidTransition
	}
	if installation.OrgID != binding.OrgID || installation.State != StateActive || installation.ActiveOperationID != "" ||
		installation.ActiveRelease != binding.AppReleaseDigest {
		return FrontendBinding{}, ErrConflict
	}
	var currentID string
	err = tx.QueryRowContext(ctx, `SELECT FrontendBindingId FROM _System_CatalogFrontendBinding
WHERE AppInstallationId = ? AND State = 'ACTIVE' FOR UPDATE`, binding.AppInstallationID).Scan(&currentID)
	if errors.Is(err, sql.ErrNoRows) {
		currentID = ""
	} else if err != nil {
		return FrontendBinding{}, err
	}
	if currentID != expectedActiveBindingID {
		return FrontendBinding{}, ErrConflict
	}
	if _, err := tx.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding
SET State = 'DRAINING', ActiveKey = NULL, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE AppInstallationId = ? AND State = 'ACTIVE'`, binding.AppInstallationID); err != nil {
		return FrontendBinding{}, err
	}
	result, err := tx.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding
SET State = 'ACTIVE', ActiveKey = SHA2(CONCAT(AppInstallationId, CHAR(0), 'frontend'), 256),
StagingKey = NULL, ActivationPredecessorId = ?, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE FrontendBindingId = ? AND State = 'STAGED' AND OperationId IS NULL`,
		nullableString(expectedActiveBindingID), binding.FrontendBindingID)
	if isDuplicate(err) {
		return FrontendBinding{}, ErrConflict
	}
	if err != nil {
		return FrontendBinding{}, err
	}
	affected, err := result.RowsAffected()
	if err != nil || affected != 1 {
		return FrontendBinding{}, ErrConflict
	}
	if err := tx.Commit(); err != nil {
		return FrontendBinding{}, err
	}
	binding.State = BindingActive
	return binding, nil
}

// The locator reads below are intentionally unlocked. Activation then locks
// the installation before it locks the binding row, matching staging and App
// operation lock order. Binding rows are immutable in identity and are never
// deleted; the subsequent FOR UPDATE read remains the authority for state and
// contents.
func handlerBindingInstallationID(ctx context.Context, tx *sql.Tx, bindingID string) (string, error) {
	var installationID string
	err := tx.QueryRowContext(ctx, `SELECT AppInstallationId FROM _System_CatalogHandlerBinding
WHERE HandlerBindingId = ?`, bindingID).Scan(&installationID)
	if errors.Is(err, sql.ErrNoRows) {
		return "", ErrNotFound
	}
	if err != nil {
		return "", err
	}
	return installationID, nil
}

func frontendBindingInstallationID(ctx context.Context, tx *sql.Tx, bindingID string) (string, error) {
	var installationID string
	err := tx.QueryRowContext(ctx, `SELECT AppInstallationId FROM _System_CatalogFrontendBinding
WHERE FrontendBindingId = ?`, bindingID).Scan(&installationID)
	if errors.Is(err, sql.ErrNoRows) {
		return "", ErrNotFound
	}
	if err != nil {
		return "", err
	}
	return installationID, nil
}

func (a *Authority) RetireDrainedHandlerBinding(ctx context.Context, bindingID string) error {
	result, err := a.db.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding SET State = 'RETIRED', LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE HandlerBindingId = ? AND State = 'DRAINING'`, bindingID)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected != 1 {
		binding, readErr := a.ReadHandlerBinding(ctx, bindingID)
		if readErr == nil && binding.State == BindingRetired {
			return nil
		}
		return ErrInvalidTransition
	}
	return nil
}

func (a *Authority) FailStagedHandlerBinding(ctx context.Context, bindingID string) error {
	result, err := a.db.ExecContext(ctx, `UPDATE _System_CatalogHandlerBinding b
LEFT JOIN _System_CatalogAppOperation o ON o.OperationId = b.OperationId
SET b.State = 'FAILED', b.LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE b.HandlerBindingId = ? AND b.State = 'STAGED'
AND (b.OperationId IS NULL OR o.Step = 'CANDIDATE_COMMITTED')`, bindingID)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected != 1 {
		binding, readErr := a.ReadHandlerBinding(ctx, bindingID)
		if readErr == nil && binding.State == BindingFailed {
			return nil
		}
		return ErrInvalidTransition
	}
	return nil
}

func (a *Authority) RetireDrainedFrontendBinding(ctx context.Context, bindingID string) error {
	result, err := a.db.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding SET State = 'RETIRED', LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE FrontendBindingId = ? AND State = 'DRAINING'`, bindingID)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected != 1 {
		binding, readErr := a.ReadFrontendBinding(ctx, bindingID)
		if readErr == nil && binding.State == BindingRetired {
			return nil
		}
		return ErrInvalidTransition
	}
	return nil
}

func (a *Authority) FailStagedFrontendBinding(ctx context.Context, bindingID string) error {
	result, err := a.db.ExecContext(ctx, `UPDATE _System_CatalogFrontendBinding b
LEFT JOIN _System_CatalogAppOperation o ON o.OperationId = b.OperationId
SET b.State = 'FAILED', b.OperationKey = NULL, b.StagingKey = NULL, b.LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE b.FrontendBindingId = ? AND b.State = 'STAGED'
AND (b.OperationId IS NULL OR o.Step = 'CANDIDATE_COMMITTED')`, bindingID)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected != 1 {
		binding, readErr := a.ReadFrontendBinding(ctx, bindingID)
		if readErr == nil && binding.State == BindingFailed {
			return nil
		}
		return ErrInvalidTransition
	}
	return nil
}