package persistence

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
	securityprojectionv1 "github.com/pingcap-inc/nexuscrm-contract/pkg/runtime/securityprojectionv1"
)

const (
	runtimeSecurityStateTable        = "_System_RuntimeSecurityState"
	runtimeSecurityBindingTable      = "_System_RuntimeSecurityBinding"
	runtimeSecurityLeaseTable        = "_System_RuntimeSecurityLease"
	runtimeDelegationRevocationTable = "_System_RuntimeDelegationRevocation"
)

var (
	ErrRuntimeSecurityEpochConflict = errors.New("runtime security projection: epoch conflict")
	ErrRuntimeSecurityStateNotFound = errors.New("runtime security projection: state not found")
)

// RuntimeSecurityBinding preserves the persistence public name for the neutral
// projection value identity owned by nexuscrm-contract.
type RuntimeSecurityBinding = securityprojectionv1.Binding

// RuntimeSecurityProjection atomically replaces every active installation
// binding for one Org at a monotonically increasing SecurityEpoch.
type RuntimeSecurityProjection = securityprojectionv1.Projection

// RuntimeReplicaSecurityContext is the authoritative pre-verification context
// selected by infrastructure replica ID. Configuration selects the replica;
// this database fact, not configuration, supplies Org and Runtime audience.
type RuntimeReplicaSecurityContext struct {
	OrgID              string
	RuntimeAudience    string
	SecurityEpoch      uint64
	LeaseSecurityEpoch uint64
	LeaseExpiresAt     time.Time
	// AuthorityNow is the database-authored base of the durable lease expiry.
	// It is derived from the stored expiry and approved duration rather than a
	// second clock sample. A replica converts the remaining duration onto its
	// local clock using the time captured before the round trip, so clock skew
	// and query latency can only shorten readiness.
	AuthorityNow time.Time
}

// RuntimeSecurityAdmissionState is one SQL-statement snapshot of the facts
// needed after caller-bound signature verification. Consumers classify the
// neutral state without performing another installation or lease read.
type RuntimeSecurityAdmissionState struct {
	StateFound         bool
	InstallationFound  bool
	InstallationActive bool
	Capabilities       []string
	SecurityEpoch      uint64
	LeaseFound         bool
	LeaseSecurityEpoch uint64
	LeaseExpiresAt     time.Time
	DelegationRevoked  bool
	AuthorityNow       time.Time
}

// RuntimeSecurityIssuanceState is the one-statement issuer projection selected
// by infrastructure replica identity plus an authenticated workload and the
// requested AppInstallation. Org, audience, epoch and lease are returned from
// the Runtime-owned state; none is accepted from the caller.
type RuntimeSecurityIssuanceState struct {
	StateFound         bool
	BindingFound       bool
	BindingActive      bool
	OrgID              string
	RuntimeAudience    string
	Capabilities       []string
	SecurityEpoch      uint64
	LeaseFound         bool
	LeaseSecurityEpoch uint64
	LeaseExpiresAt     time.Time
	AuthorityNow       time.Time
}

type RuntimeSecurityRepository struct{ db *sql.DB }

func NewRuntimeSecurityRepository(db *sql.DB) *RuntimeSecurityRepository {
	return &RuntimeSecurityRepository{db: db}
}

// ActivateProjection compare-and-swaps one Org projection. The epoch update,
// complete binding replacement, and invalidation of every old replica lease
// commit atomically, so admission observes either the old or new view.
func (r *RuntimeSecurityRepository) ActivateProjection(ctx context.Context, expectedEpoch uint64, projection RuntimeSecurityProjection) error {
	if r == nil || r.db == nil {
		return errors.New("runtime security projection: repository is unavailable")
	}
	bindings, err := canonicalRuntimeSecurityBindings(projection)
	if err != nil {
		return err
	}
	if projection.SecurityEpoch == 0 || projection.SecurityEpoch <= expectedEpoch {
		return errors.New("runtime security projection: new epoch must advance")
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	current, found, err := readRuntimeSecurityEpochForUpdate(ctx, tx, projection.OrgID)
	if err != nil {
		return err
	}
	if found && current == projection.SecurityEpoch {
		matches, err := runtimeSecurityProjectionMatches(ctx, tx, projection, bindings)
		if err != nil {
			return err
		}
		if !matches {
			return ErrRuntimeSecurityEpochConflict
		}
		return tx.Commit()
	}
	if (!found && expectedEpoch != 0) || (found && current != expectedEpoch) {
		return ErrRuntimeSecurityEpochConflict
	}

	if found {
		if _, err := tx.ExecContext(ctx, "UPDATE `"+runtimeSecurityStateTable+"` SET `RuntimeAudience` = ?, `SecurityEpoch` = ?, `LastModifiedDate` = CURRENT_TIMESTAMP(6) WHERE `OrgId` = ?", projection.RuntimeAudience, epochString(projection.SecurityEpoch), projection.OrgID); err != nil {
			return err
		}
	} else {
		if _, err := tx.ExecContext(ctx, "INSERT INTO `"+runtimeSecurityStateTable+"` (`OrgId`, `RuntimeAudience`, `SecurityEpoch`, `CreatedDate`, `LastModifiedDate`) VALUES (?, ?, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6))", projection.OrgID, projection.RuntimeAudience, epochString(projection.SecurityEpoch)); err != nil {
			if isDuplicateKeyError(err) {
				return ErrRuntimeSecurityEpochConflict
			}
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, "DELETE FROM `"+runtimeSecurityBindingTable+"` WHERE `OrgId` = ?", projection.OrgID); err != nil {
		return err
	}
	for _, binding := range bindings {
		capabilities, err := json.Marshal(binding.Capabilities)
		if err != nil {
			return err
		}
		active := 0
		if binding.Active {
			active = 1
		}
		if _, err := tx.ExecContext(ctx, "INSERT INTO `"+runtimeSecurityBindingTable+"` (`Id`, `OrgId`, `AppInstallationId`, `CallerWorkloadId`, `CallerWorkloadDigest`, `Capabilities`, `IsActive`, `CreatedDate`, `LastModifiedDate`) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6))",
			securityBindingID(binding), binding.OrgID, binding.AppInstallationID,
			binding.CallerWorkloadID, digestString(binding.CallerWorkloadID),
			capabilities, active); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, "DELETE FROM `"+runtimeSecurityLeaseTable+"` WHERE `OrgId` = ?", projection.OrgID); err != nil {
		return err
	}
	return tx.Commit()
}

// IssueLease publishes a replica lease only for the currently active epoch.
// Its duration is caller-selected here because P6-D, not persistence, owns the
func (r *RuntimeSecurityRepository) IssueLease(ctx context.Context, orgID, replicaID string, securityEpoch uint64, expiresAt time.Time) error {
	if r == nil || r.db == nil {
		return errors.New("runtime security lease: repository is unavailable")
	}
	if strings.TrimSpace(orgID) == "" || strings.TrimSpace(replicaID) == "" || securityEpoch == 0 || expiresAt.IsZero() {
		return errors.New("runtime security lease: complete binding is required")
	}
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	current, found, err := readRuntimeSecurityEpochForUpdate(ctx, tx, orgID)
	if err != nil {
		return err
	}
	if !found {
		return ErrRuntimeSecurityStateNotFound
	}
	if current != securityEpoch {
		return ErrRuntimeSecurityEpochConflict
	}
	_, err = tx.ExecContext(ctx, "INSERT INTO `"+runtimeSecurityLeaseTable+"` (`RuntimeReplicaId`, `OrgId`, `SecurityEpoch`, `ExpiresAt`, `CreatedDate`, `LastModifiedDate`) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)) ON DUPLICATE KEY UPDATE `OrgId` = VALUES(`OrgId`), `SecurityEpoch` = VALUES(`SecurityEpoch`), `ExpiresAt` = VALUES(`ExpiresAt`), `LastModifiedDate` = CURRENT_TIMESTAMP(6)", replicaID, orgID, epochString(securityEpoch), expiresAt.UTC())
	if err != nil {
		return err
	}
	return tx.Commit()
}

// RenewReplicaLease binds a replica to the currently active security epoch and
// derives the expiry exclusively from database time after every contended row
// lock is owned. The caller selects the deployment Org and approved duration,
// but cannot author the epoch, audience, absolute expiry, or a stale lease
// continuation.
func (r *RuntimeSecurityRepository) RenewReplicaLease(ctx context.Context, orgID, replicaID string, duration time.Duration) (RuntimeReplicaSecurityContext, bool, error) {
	if r == nil || r.db == nil {
		return RuntimeReplicaSecurityContext{}, false, errors.New("runtime security lease renewal: repository is unavailable")
	}
	if strings.TrimSpace(orgID) == "" || strings.TrimSpace(replicaID) == "" || duration <= 0 {
		return RuntimeReplicaSecurityContext{}, false, errors.New("runtime security lease renewal: org, replica, and positive duration are required")
	}
	microseconds := duration.Microseconds()
	if microseconds <= 0 {
		return RuntimeReplicaSecurityContext{}, false, errors.New("runtime security lease renewal: duration is below database precision")
	}
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	defer func() { _ = tx.Rollback() }()
	var result RuntimeReplicaSecurityContext
	var epochRaw []byte
	err = tx.QueryRowContext(ctx, "SELECT `RuntimeAudience`, `SecurityEpoch` FROM `"+runtimeSecurityStateTable+"` WHERE `OrgId` = ? FOR UPDATE", orgID).
		Scan(&result.RuntimeAudience, &epochRaw)
	if errors.Is(err, sql.ErrNoRows) {
		return RuntimeReplicaSecurityContext{}, false, nil
	}
	if err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	result.SecurityEpoch, err = parseEpoch(epochRaw)
	if err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	result.OrgID = orgID
	result.LeaseSecurityEpoch = result.SecurityEpoch
	// Acquire the replica lease row before starting the new lifetime. A direct
	// TIMESTAMPADD in this upsert would evaluate before a duplicate-key lock
	// wait and could return an already-aged or expired lease.
	if _, err := tx.ExecContext(ctx, "INSERT INTO `"+runtimeSecurityLeaseTable+"` (`RuntimeReplicaId`, `OrgId`, `SecurityEpoch`, `ExpiresAt`, `CreatedDate`, `LastModifiedDate`) VALUES (?, ?, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)) ON DUPLICATE KEY UPDATE `RuntimeReplicaId` = VALUES(`RuntimeReplicaId`)", replicaID, orgID, epochString(result.SecurityEpoch)); err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	var lockedReplicaID string
	if err := tx.QueryRowContext(ctx, "SELECT `RuntimeReplicaId` FROM `"+runtimeSecurityLeaseTable+"` WHERE `RuntimeReplicaId` = ? FOR UPDATE", replicaID).Scan(&lockedReplicaID); err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	if lockedReplicaID != replicaID {
		return RuntimeReplicaSecurityContext{}, false, errors.New("runtime security lease renewal: locked replica identity changed")
	}
	if _, err := tx.ExecContext(ctx, "UPDATE `"+runtimeSecurityLeaseTable+"` SET `OrgId` = ?, `SecurityEpoch` = ?, `ExpiresAt` = TIMESTAMPADD(MICROSECOND, ?, CURRENT_TIMESTAMP(6)), `LastModifiedDate` = CURRENT_TIMESTAMP(6) WHERE `RuntimeReplicaId` = ?", orgID, epochString(result.SecurityEpoch), microseconds, replicaID); err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	if err := tx.QueryRowContext(ctx, "SELECT `ExpiresAt` FROM `"+runtimeSecurityLeaseTable+"` WHERE `RuntimeReplicaId` = ? AND `OrgId` = ? AND `SecurityEpoch` = ?", replicaID, orgID, epochString(result.SecurityEpoch)).Scan(&result.LeaseExpiresAt); err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	result.AuthorityNow = result.LeaseExpiresAt.Add(-time.Duration(microseconds) * time.Microsecond)
	if err := tx.Commit(); err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	return result, true, nil
}

// RevokeDelegation installs an authoritative tombstone through the token's
// latest possible acceptance time. Repeating a revocation can only extend, not
// shorten, the tombstone.
func (r *RuntimeSecurityRepository) RevokeDelegation(ctx context.Context, orgID, delegationID string, expiresAt time.Time) error {
	if r == nil || r.db == nil {
		return errors.New("runtime delegation revocation: repository is unavailable")
	}
	if strings.TrimSpace(orgID) == "" || strings.TrimSpace(delegationID) == "" || expiresAt.IsZero() {
		return errors.New("runtime delegation revocation: complete binding is required")
	}
	_, err := r.db.ExecContext(ctx, "INSERT INTO `"+runtimeDelegationRevocationTable+"` (`Id`, `OrgId`, `DelegationId`, `ExpiresAt`, `CreatedDate`, `LastModifiedDate`) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)) ON DUPLICATE KEY UPDATE `ExpiresAt` = GREATEST(`ExpiresAt`, VALUES(`ExpiresAt`)), `LastModifiedDate` = CURRENT_TIMESTAMP(6)", digestParts(orgID, delegationID), orgID, delegationID, expiresAt.UTC())
	return err
}

// ReadReplicaSecurityContext resolves the process's infrastructure replica ID
// to its database-owned Org/audience and current lease. It is also the one
// readiness input: absent, mismatched, or expired state must not serve.
func (r *RuntimeSecurityRepository) ReadReplicaSecurityContext(ctx context.Context, replicaID string) (RuntimeReplicaSecurityContext, bool, error) {
	if r == nil || r.db == nil {
		return RuntimeReplicaSecurityContext{}, false, errors.New("runtime security replica: repository is unavailable")
	}
	const query = "SELECT s.`OrgId`, s.`RuntimeAudience`, s.`SecurityEpoch`, l.`SecurityEpoch`, l.`ExpiresAt` FROM `" + runtimeSecurityLeaseTable + "` l INNER JOIN `" + runtimeSecurityStateTable + "` s ON s.`OrgId` = l.`OrgId` WHERE l.`RuntimeReplicaId` = ? LIMIT 1"
	var result RuntimeReplicaSecurityContext
	var stateEpochRaw, leaseEpochRaw []byte
	err := r.db.QueryRowContext(ctx, query, replicaID).Scan(&result.OrgID, &result.RuntimeAudience, &stateEpochRaw, &leaseEpochRaw, &result.LeaseExpiresAt)
	if errors.Is(err, sql.ErrNoRows) {
		return RuntimeReplicaSecurityContext{}, false, nil
	}
	if err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	result.SecurityEpoch, err = parseEpoch(stateEpochRaw)
	if err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	result.LeaseSecurityEpoch, err = parseEpoch(leaseEpochRaw)
	if err != nil {
		return RuntimeReplicaSecurityContext{}, false, err
	}
	return result, true, nil
}

// ReadAdmissionState returns one statement snapshot selected only with claims
// that have already passed caller-bound cryptographic verification.
func (r *RuntimeSecurityRepository) ReadAdmissionState(ctx context.Context, replicaID, orgID, appInstallationID, callerWorkloadID, delegationID string, now time.Time) (RuntimeSecurityAdmissionState, error) {
	if r == nil || r.db == nil {
		return RuntimeSecurityAdmissionState{}, errors.New("runtime security admission: repository is unavailable")
	}
	const query = "SELECT s.`SecurityEpoch`, b.`Capabilities`, b.`IsActive`, l.`SecurityEpoch`, l.`ExpiresAt`, CASE WHEN d.`Id` IS NULL THEN 0 ELSE 1 END, CURRENT_TIMESTAMP(6) " +
		"FROM `" + runtimeSecurityStateTable + "` s " +
		"LEFT JOIN `" + runtimeSecurityBindingTable + "` b ON b.`OrgId` = s.`OrgId` AND b.`AppInstallationId` = ? AND b.`CallerWorkloadDigest` = ? AND b.`CallerWorkloadId` = ? " +
		"LEFT JOIN `" + runtimeSecurityLeaseTable + "` l ON l.`OrgId` = s.`OrgId` AND l.`RuntimeReplicaId` = ? " +
		"LEFT JOIN `" + runtimeDelegationRevocationTable + "` d ON d.`OrgId` = s.`OrgId` AND d.`DelegationId` = ? AND d.`ExpiresAt` > ? " +
		"WHERE s.`OrgId` = ? LIMIT 1"

	var state RuntimeSecurityAdmissionState
	var stateEpochRaw []byte
	var capabilitiesRaw []byte
	var active sql.NullBool
	var leaseEpochRaw []byte
	var leaseExpiry sql.NullTime
	var revoked int
	err := r.db.QueryRowContext(ctx, query, appInstallationID, digestString(callerWorkloadID), callerWorkloadID, replicaID, delegationID, now.UTC(), orgID).
		Scan(&stateEpochRaw, &capabilitiesRaw, &active, &leaseEpochRaw, &leaseExpiry, &revoked, &state.AuthorityNow)
	if errors.Is(err, sql.ErrNoRows) {
		return state, nil
	}
	if err != nil {
		return RuntimeSecurityAdmissionState{}, err
	}
	state.StateFound = true
	state.SecurityEpoch, err = parseEpoch(stateEpochRaw)
	if err != nil {
		return RuntimeSecurityAdmissionState{}, err
	}
	if active.Valid {
		state.InstallationFound = true
		state.InstallationActive = active.Bool
		if err := json.Unmarshal(capabilitiesRaw, &state.Capabilities); err != nil {
			return RuntimeSecurityAdmissionState{}, fmt.Errorf("runtime security admission: decode capabilities: %w", err)
		}
	}
	if leaseExpiry.Valid {
		state.LeaseFound = true
		state.LeaseExpiresAt = leaseExpiry.Time
		state.LeaseSecurityEpoch, err = parseEpoch(leaseEpochRaw)
		if err != nil {
			return RuntimeSecurityAdmissionState{}, err
		}
	}
	state.DelegationRevoked = revoked != 0
	return state, nil
}

// ReadIssuanceState derives the complete Runtime-owned issuance tuple without
// accepting caller-authored Org, audience or epoch selectors. The binding join
// uses both the canonical workload value and its digest so a hash collision or
// untrusted opaque workload handle cannot select a grant.
func (r *RuntimeSecurityRepository) ReadIssuanceState(ctx context.Context, replicaID, appInstallationID, callerWorkloadID string) (RuntimeSecurityIssuanceState, error) {
	if r == nil || r.db == nil {
		return RuntimeSecurityIssuanceState{}, errors.New("runtime security issuance: repository is unavailable")
	}
	if strings.TrimSpace(replicaID) == "" || strings.TrimSpace(appInstallationID) == "" || strings.TrimSpace(callerWorkloadID) == "" {
		return RuntimeSecurityIssuanceState{}, errors.New("runtime security issuance: complete selectors are required")
	}
	const query = "SELECT s.`OrgId`, s.`RuntimeAudience`, s.`SecurityEpoch`, b.`Capabilities`, b.`IsActive`, l.`SecurityEpoch`, l.`ExpiresAt`, CURRENT_TIMESTAMP(6) " +
		"FROM `" + runtimeSecurityLeaseTable + "` l INNER JOIN `" + runtimeSecurityStateTable + "` s ON s.`OrgId` = l.`OrgId` " +
		"LEFT JOIN `" + runtimeSecurityBindingTable + "` b ON b.`OrgId` = s.`OrgId` AND b.`AppInstallationId` = ? AND b.`CallerWorkloadDigest` = ? AND b.`CallerWorkloadId` = ? " +
		"WHERE l.`RuntimeReplicaId` = ? LIMIT 1"

	var state RuntimeSecurityIssuanceState
	var stateEpochRaw, leaseEpochRaw []byte
	var capabilitiesRaw []byte
	var active sql.NullBool
	var leaseExpiry sql.NullTime
	err := r.db.QueryRowContext(ctx, query, appInstallationID, digestString(callerWorkloadID), callerWorkloadID, replicaID).
		Scan(&state.OrgID, &state.RuntimeAudience, &stateEpochRaw, &capabilitiesRaw, &active, &leaseEpochRaw, &leaseExpiry, &state.AuthorityNow)
	if errors.Is(err, sql.ErrNoRows) {
		return state, nil
	}
	if err != nil {
		return RuntimeSecurityIssuanceState{}, err
	}
	state.StateFound = true
	state.SecurityEpoch, err = parseEpoch(stateEpochRaw)
	if err != nil {
		return RuntimeSecurityIssuanceState{}, err
	}
	if active.Valid {
		state.BindingFound = true
		state.BindingActive = active.Bool
		if err := json.Unmarshal(capabilitiesRaw, &state.Capabilities); err != nil {
			return RuntimeSecurityIssuanceState{}, fmt.Errorf("runtime security issuance: decode capabilities: %w", err)
		}
	}
	if leaseExpiry.Valid {
		state.LeaseFound = true
		state.LeaseExpiresAt = leaseExpiry.Time
		state.LeaseSecurityEpoch, err = parseEpoch(leaseEpochRaw)
		if err != nil {
			return RuntimeSecurityIssuanceState{}, err
		}
	}
	return state, nil
}

func readRuntimeSecurityEpochForUpdate(ctx context.Context, tx *sql.Tx, orgID string) (uint64, bool, error) {
	var raw []byte
	err := tx.QueryRowContext(ctx, "SELECT `SecurityEpoch` FROM `"+runtimeSecurityStateTable+"` WHERE `OrgId` = ? FOR UPDATE", orgID).Scan(&raw)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, err
	}
	epoch, err := parseEpoch(raw)
	return epoch, err == nil, err
}

func runtimeSecurityProjectionMatches(ctx context.Context, tx *sql.Tx, projection RuntimeSecurityProjection, expected []RuntimeSecurityBinding) (bool, error) {
	var audience string
	if err := tx.QueryRowContext(ctx, "SELECT `RuntimeAudience` FROM `"+runtimeSecurityStateTable+"` WHERE `OrgId` = ? FOR UPDATE", projection.OrgID).Scan(&audience); err != nil {
		return false, err
	}
	if audience != projection.RuntimeAudience {
		return false, nil
	}
	rows, err := tx.QueryContext(ctx, "SELECT `Id`, `CallerWorkloadId`, `CallerWorkloadDigest`, `AppInstallationId`, `Capabilities`, `IsActive` FROM `"+runtimeSecurityBindingTable+"` WHERE `OrgId` = ? ORDER BY `Id` FOR UPDATE", projection.OrgID)
	if err != nil {
		return false, err
	}
	defer rows.Close()
	actual := make([]RuntimeSecurityBinding, 0, len(expected))
	for rows.Next() {
		var binding RuntimeSecurityBinding
		var id, workloadDigest string
		var capabilities []byte
		if err := rows.Scan(&id, &binding.CallerWorkloadID, &workloadDigest, &binding.AppInstallationID, &capabilities, &binding.Active); err != nil {
			return false, err
		}
		binding.OrgID = projection.OrgID
		if id != securityBindingID(binding) || workloadDigest != digestString(binding.CallerWorkloadID) {
			return false, nil
		}
		if err := json.Unmarshal(capabilities, &binding.Capabilities); err != nil {
			return false, fmt.Errorf("runtime security projection: decode existing capabilities: %w", err)
		}
		actual = append(actual, binding)
	}
	if err := rows.Err(); err != nil {
		return false, err
	}
	if len(actual) != len(expected) {
		return false, nil
	}
	for i := range expected {
		if actual[i].CallerWorkloadID != expected[i].CallerWorkloadID ||
			actual[i].AppInstallationID != expected[i].AppInstallationID ||
			actual[i].Active != expected[i].Active ||
			!equalStrings(actual[i].Capabilities, expected[i].Capabilities) {
			return false, nil
		}
	}
	return true, nil
}

func equalStrings(left, right []string) bool {
	if len(left) != len(right) {
		return false
	}
	for i := range left {
		if left[i] != right[i] {
			return false
		}
	}
	return true
}

func canonicalRuntimeSecurityBindings(projection RuntimeSecurityProjection) ([]RuntimeSecurityBinding, error) {
	if strings.TrimSpace(projection.OrgID) == "" || strings.TrimSpace(projection.RuntimeAudience) == "" {
		return nil, errors.New("runtime security projection: OrgID and RuntimeAudience are required")
	}
	bindings := append([]RuntimeSecurityBinding(nil), projection.Bindings...)
	seen := make(map[string]struct{}, len(bindings))
	for i := range bindings {
		binding := &bindings[i]
		if binding.OrgID != projection.OrgID || strings.TrimSpace(binding.CallerWorkloadID) == "" || strings.TrimSpace(binding.AppInstallationID) == "" {
			return nil, errors.New("runtime security projection: incomplete binding")
		}
		if !sort.StringsAreSorted(binding.Capabilities) || len(binding.Capabilities) == 0 {
			return nil, errors.New("runtime security projection: capabilities must be non-empty and canonical")
		}
		for j, capability := range binding.Capabilities {
			if strings.TrimSpace(capability) == "" || (j > 0 && capability == binding.Capabilities[j-1]) {
				return nil, errors.New("runtime security projection: capabilities must be non-empty and canonical")
			}
		}
		id := securityBindingID(*binding)
		if _, exists := seen[id]; exists {
			return nil, errors.New("runtime security projection: duplicate binding")
		}
		seen[id] = struct{}{}
		binding.Capabilities = append([]string(nil), binding.Capabilities...)
	}
	sort.Slice(bindings, func(i, j int) bool { return securityBindingID(bindings[i]) < securityBindingID(bindings[j]) })
	return bindings, nil
}

func securityBindingID(binding RuntimeSecurityBinding) string {
	return digestParts(binding.OrgID, binding.AppInstallationID, binding.CallerWorkloadID)
}

func digestString(value string) string { return digestParts(value) }

func digestParts(parts ...string) string {
	hash := sha256.New()
	for _, part := range parts {
		_, _ = hash.Write([]byte{0})
		_, _ = hash.Write([]byte(part))
	}
	return hex.EncodeToString(hash.Sum(nil))
}

func epochString(epoch uint64) string { return strconv.FormatUint(epoch, 10) }

func parseEpoch(raw []byte) (uint64, error) {
	epoch, err := strconv.ParseUint(string(raw), 10, 64)
	if err != nil || epoch == 0 {
		return 0, errors.New("runtime security projection: invalid epoch")
	}
	return epoch, nil
}

func isDuplicateKeyError(err error) bool {
	var mysqlError *mysql.MySQLError
	return errors.As(err, &mysqlError) && mysqlError.Number == 1062
}