package automation

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/nexuscrm/backend/internal/domain/ports"
	"github.com/nexuscrm/backend/internal/features/permissions"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/formula"
	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// Domain errors for approval operations (typed)
var (
	ErrNoActiveApprovalProcess = apperrors.NewNotFoundError("approval process", "")
	ErrRecordAlreadyPending    = apperrors.NewConflictError("approval", "status", "pending")
	ErrWorkItemNotFound        = apperrors.NewNotFoundError("work item", "")
	ErrNotAuthorized           = apperrors.NewPermissionError("approve/reject", "approval work item")
	ErrNotSubmitter            = apperrors.NewPermissionError("recall", "approval — only the submitter can recall")
	ErrSubmitPermissionDenied  = errors.New("you don't have permission to submit this record for approval")
	ErrInstanceNotPending      = apperrors.NewConflictError("approval instance", "status", "not pending")
	ErrEntryCriteriaNotMet     = apperrors.NewValidationError("entryCriteria", "record does not meet the entry criteria for this approval process")
	ErrDelegationNotAllowed    = apperrors.NewPermissionError("reassign", "delegation not allowed for this approval step")
)

// SubmitRequest represents a request to submit a record for approval
type SubmitRequest struct {
	ObjectAPIName     string `json:"object_api_name"`
	RecordID          string `json:"record_id"`
	Comments          string `json:"comments"`
	SkipEntryCriteria bool   `json:"skip_entry_criteria"`
}

// PendingApprovalView is the backend-facing projection used by the approval queue UI.
type PendingApprovalView struct {
	WorkItemID          string `json:"workItemId"`
	ProcessInstanceID   string `json:"processInstanceId"`
	ProcessDefinitionID string `json:"processDefinitionId"`
	TargetObjectID      string `json:"targetObjectId"`
	ObjectAPIName       string `json:"objectApiName"`
	SubmittedByID       string `json:"submittedById"`
	SubmittedByName     string `json:"submittedByName"`
	SubmittedDate       string `json:"submittedDate"`
	ActorID             string `json:"actorId"`
	OriginalActorID     string `json:"originalActorId"`
	Comments            string `json:"comments,omitempty"`
	RecordName          string `json:"recordName,omitempty"`
}

// ProcessInstanceSummary is the backend-facing projection used by record detail approval UI.
type ProcessInstanceSummary struct {
	ID                  string `json:"id"`
	ProcessDefinitionID string `json:"processDefinitionId"`
	TargetObjectID      string `json:"targetObjectId"`
	Status              string `json:"status"`
	SubmittedByID       string `json:"submittedById"`
	SubmittedByName     string `json:"submittedByName"`
	CreatedDate         string `json:"createdDate"`
	CompletedDate       string `json:"completedDate,omitempty"`
}

// ApprovalService handles approval workflow management
type ApprovalService struct {
	persistence   PersistenceProvider
	query         ports.QueryService
	permissions   *permissions.PermissionService
	flowExecutor  *FlowExecutor
	interviews    *FlowInterviewService
	actions       *ActionService
	schema        SchemaLookup
	logger        *logging.Logger
	formulaEngine *formula.Engine
}

// NewApprovalService creates a new ApprovalService.
// `schema` is optional; when nil, lookup-field target resolution falls back
// to a restricted heuristic (only standard `<Name>Id` fields resolve).
func NewApprovalService(
	persistence PersistenceProvider,
	query ports.QueryService,
	permissions *permissions.PermissionService,
	flowExecutor *FlowExecutor,
	interviews *FlowInterviewService,
	actions *ActionService,
	schema SchemaLookup,
	logger *logging.Logger,
) *ApprovalService {
	if logger == nil {
		logger = logging.Default
	}
	return &ApprovalService{
		persistence:   persistence,
		query:         query,
		permissions:   permissions,
		flowExecutor:  flowExecutor,
		interviews:    interviews,
		actions:       actions,
		schema:        schema,
		logger:        logger,
		formulaEngine: formula.NewEngine(),
	}
}

func normalizeRejectBehaviorForProcessNode(rejectBehavior string) string {
	switch strings.TrimSpace(rejectBehavior) {
	case "BackToPrevious":
		return "BackToSubmitter"
	case "RejectRequest", "":
		return "FinalRejection"
	default:
		return rejectBehavior
	}
}

func approvalStringValue(value interface{}) string {
	switch v := value.(type) {
	case nil:
		return ""
	case string:
		return v
	case []byte:
		return string(v)
	case time.Time:
		return v.UTC().Format(time.RFC3339)
	default:
		return fmt.Sprint(v)
	}
}

func firstNonEmptyApprovalValue(values ...interface{}) string {
	for _, value := range values {
		if resolved := strings.TrimSpace(approvalStringValue(value)); resolved != "" {
			return resolved
		}
	}
	return ""
}

func buildProcessNodeFromMetadata(step NormalizedApprovalStep, processDefID string, order int) models.SObject {
	record := models.SObject{
		"Name":                step.Label,
		"DeveloperName":       step.Name,
		"Description":         step.Description,
		"ProcessDefinitionId": processDefID,
		"AllowComments":       true,
		"RequireComments":     false,
		"StepOrder":           order,
		"RejectBehavior":      normalizeRejectBehaviorForProcessNode(step.RejectBehavior),
	}
	if record["Name"] == "" {
		record["Name"] = step.Name
	}
	if len(step.Approvers) == 0 {
		return record
	}

	firstApprover := step.Approvers[0]
	switch firstApprover.Type {
	case "relatedUserField":
		record["ApproverType"] = firstApprover.Type
		record["ApproverRelatedField"] = firstApprover.Name
	case "hierarchy", "queue", "role":
		record["ApproverType"] = firstApprover.Type
	case "user":
		record["ApproverType"] = firstApprover.Type
		userRefs := make([]string, 0, len(step.Approvers))
		for _, approver := range step.Approvers {
			if approver.Type != "user" {
				continue
			}
			ref := strings.TrimSpace(approver.Name)
			if ref != "" {
				userRefs = append(userRefs, ref)
			}
		}
		if len(userRefs) > 0 {
			record["ApproverUserId"] = strings.Join(userRefs, ",")
		}
	}

	return record
}

func (s *ApprovalService) ensureProcessNodesForDefinition(
	ctx context.Context,
	processDef models.SObject,
	user *models.UserSession,
) ([]models.SObject, error) {
	processDefID, _ := processDef[constants.FieldID].(string)
	if processDefID == "" {
		return nil, nil
	}

	nodeFilter := fmt.Sprintf("ProcessDefinitionId == '%s'", processDefID)
	nodeResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessNode, nodeFilter, user, "StepOrder", "ASC", 100)
	if err == nil && nodeResult != nil && len(nodeResult.Records) > 0 {
		return nodeResult.Records, nil
	}
	if err != nil {
		s.logger.Error("Failed to query ProcessNode for "+processDefID, err)
		return nil, apperrors.NewInternalError("failed to load approval process steps", err)
	}

	rawMetadata, _ := processDef["Metadata"].(string)
	metadata, parseErr := ParseApprovalProcessMetadata(rawMetadata)
	if parseErr != nil || metadata == nil || len(metadata.Steps) == 0 {
		if parseErr != nil {
			s.logger.Error("Failed to parse approval metadata for "+processDefID, parseErr)
		}
		return nil, nil
	}

	createdNodes := make([]models.SObject, 0, len(metadata.Steps))
	for idx, step := range metadata.Steps {
		record := buildProcessNodeFromMetadata(step, processDefID, idx+1)
		inserted, insertErr := s.persistence.Insert(processTableWriteContext(ctx, "ApprovalService: rebuild ProcessNode"), constants.TableProcessNode, record, user)
		if insertErr != nil {
			return nil, apperrors.NewInternalError("failed to rebuild approval process steps from metadata", insertErr)
		}
		createdNodes = append(createdNodes, inserted)
	}

	return createdNodes, nil
}

func (s *ApprovalService) ifCriteriaNotMetBehaviorForNode(processDef models.SObject, node models.SObject) string {
	behavior := ""
	if metaStep := s.metadataStepForNode(processDef, node); metaStep != nil {
		behavior = normalizeIfCriteriaNotMet(metaStep.IfCriteriaNotMet)
	}
	if behavior == "GotoNextStep" {
		return behavior
	}
	return "ApproveRecord"
}

func (s *ApprovalService) selectInitialApprovalStep(
	ctx context.Context,
	processDef models.SObject,
	nodeRecords []models.SObject,
	objectName string,
	targetObjectID string,
	submittedByID string,
	user *models.UserSession,
) (models.SObject, []string, bool, error) {
	if len(nodeRecords) == 0 {
		return nil, []string{submittedByID}, true, nil
	}

	for idx, node := range nodeRecords {
		if s.stepCriteriaMatches(ctx, processDef, node, objectName, targetObjectID, user) {
			actorIDs, resolveErr := s.resolveApproverIDsForNodeWithContext(ctx, processDef, node, targetObjectID, user, approvalApproverResolutionContext{
				submitterID: submittedByID,
			})
			if resolveErr != nil {
				return nil, nil, false, resolveErr
			}
			if len(actorIDs) == 0 {
				actorIDs = []string{submittedByID}
			}
			return node, actorIDs, true, nil
		}

		behavior := s.ifCriteriaNotMetBehaviorForNode(processDef, node)
		nodeName, _ := node["Name"].(string)
		s.logger.Info(fmt.Sprintf("stepCriteria: initial step %q (idx=%d) did not match; ifCriteriaNotMet=%s; record=%s", nodeName, idx, behavior, targetObjectID))
		if behavior == "GotoNextStep" {
			continue
		}
		return nil, nil, false, nil
	}

	return nil, nil, false, nil
}

// Submit submits a record for approval.
// 1. Finds a matching active ProcessDefinition for the object
// 2. Evaluates Entry Criteria
// 3. Checks no pending ProcessInstance already exists
// 4. Reads ProcessNode steps and starts the first metadata-matching step
// 5. Creates ProcessInstance + ProcessInstanceNode + ProcessInstanceWorkitem
func (s *ApprovalService) Submit(ctx context.Context, req SubmitRequest, user *models.UserSession) (models.SObject, error) {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: managing approval system tables")

	// Step 1: Validate and normalize object name (ensure PascalCase for DB match)
	objectName := req.ObjectAPIName
	if objectName == "" {
		return nil, apperrors.NewValidationError("ObjectApiName", "ObjectApiName is required for Submit")
	}
	if len(objectName) > 0 {
		objectName = strings.ToUpper(objectName[:1]) + objectName[1:]
	}

	// Step 2: Find ALL active ProcessDefinitions for this object, then match by entry criteria
	filterExpr := fmt.Sprintf("TableEnumOrId == '%s' && State == 'Active'", objectName)
	result, err := s.query.QueryWithFilter(ctx, constants.TableProcessDefinition, filterExpr, user, constants.ColProcessDefinition_ProcessOrder, "ASC", 100)
	if err != nil {
		s.logger.Error("Failed to query ProcessDefinition for "+objectName, err)
		return nil, ErrNoActiveApprovalProcess
	}
	if result.TotalSize == 0 || len(result.Records) == 0 {
		return nil, ErrNoActiveApprovalProcess
	}

	// Step 3: Iterate through processes and find the first one whose entry criteria match
	var processDef models.SObject
	var processDefId string

	if req.SkipEntryCriteria || result.TotalSize == 1 {
		// Skip criteria or only one process — use it directly
		processDef = result.Records[0]
		processDefId, _ = processDef[constants.FieldID].(string)
		// Still evaluate entry criteria for single process (unless skipped)
		if !req.SkipEntryCriteria {
			entryFilter, _ := processDef["EntryFilter"].(string)
			if entryFilter != "" {
				matched, evalErr := s.matchEntryCriteria(ctx, entryFilter, objectName, req.RecordID, user)
				if evalErr != nil {
					s.logger.Warn("Entry criteria evaluation error, allowing: " + evalErr.Error())
				} else if !matched {
					return nil, ErrEntryCriteriaNotMet
				}
			}
		}
	} else {
		// Multiple active processes — find the first matching one
		matched := false
		for _, candidate := range result.Records {
			candidateId, _ := candidate[constants.FieldID].(string)
			entryFilter, _ := candidate["EntryFilter"].(string)

			if entryFilter == "" {
				// No entry criteria — matches all records
				processDef = candidate
				processDefId = candidateId
				matched = true
				break
			}

			ok, evalErr := s.matchEntryCriteria(ctx, entryFilter, objectName, req.RecordID, user)
			if evalErr != nil {
				s.logger.Warn(fmt.Sprintf("Entry criteria evaluation error for process %s, skipping: %v", candidateId, evalErr))
				continue
			}
			if ok {
				processDef = candidate
				processDefId = candidateId
				matched = true
				s.logger.Info(fmt.Sprintf("Matched approval process %s (%s) for record %s", candidateId, candidate["Name"], req.RecordID))
				break
			}
		}
		if !matched {
			return nil, ErrEntryCriteriaNotMet
		}
	}

	// Step 4: Enforce submitter authorization once the process is selected.
	canSubmit, err := s.canUserSubmitForProcess(ctx, processDef, objectName, req.RecordID, user)
	if err != nil {
		s.logger.Error("Failed to evaluate allowed submitters for "+processDefId, err)
		return nil, apperrors.NewInternalError("failed to evaluate approval submitter permissions", err)
	}
	if !canSubmit {
		return nil, ErrSubmitPermissionDenied
	}

	// Step 5: Check for existing pending approval
	pendingFilter := fmt.Sprintf("TargetObjectId == '%s' && Status == '%s'", req.RecordID, constants.ApprovalStatusPending)
	pendingResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstance, pendingFilter, user, "", "", 1)
	if err == nil && pendingResult.TotalSize > 0 {
		return nil, ErrRecordAlreadyPending
	}

	// Step 6: Find the initial step from ProcessNode table, rebuilding from
	// metadata when older records are missing child nodes. Initial routing uses
	// the same step criteria / ifCriteriaNotMet semantics as downstream
	// advancement.
	nodeRecords, err := s.ensureProcessNodesForDefinition(ctx, processDef, user)
	if err != nil {
		return nil, err
	}
	var firstNode models.SObject
	var firstNodeId, firstNodeName string
	actorIDs := []string{}
	startPendingApproval := false
	firstNode, actorIDs, startPendingApproval, err = s.selectInitialApprovalStep(ctx, processDef, nodeRecords, objectName, req.RecordID, user.ID, user)
	if err != nil {
		return nil, err
	}
	if firstNode != nil {
		firstNodeId, _ = firstNode[constants.FieldID].(string)
		firstNodeName, _ = firstNode["Name"].(string)
	}

	// Step 7: Create ProcessInstance
	now := time.Now().UTC().Format(time.RFC3339)
	initialStatus := constants.ApprovalStatusPending
	if !startPendingApproval {
		initialStatus = constants.ApprovalStatusApproved
	}
	instanceRecord := models.SObject{
		"ProcessDefinitionId": processDefId,
		"TargetObjectId":      req.RecordID,
		"Status":              initialStatus,
		"SubmittedById":       user.ID,
		"CreatedById":         user.ID,
		"CreatedDate":         now,
		"LastModifiedDate":    now,
	}
	if !startPendingApproval {
		instanceRecord["CompletedDate"] = now
	}

	// Steps 7-9 + Submit-event FieldUpdate actions are wrapped in a transaction
	// so that a failed linked-record update does not leave behind a half-created
	// ProcessInstance / ProcessNode / Workitem set. Alert (notification) actions
	// remain best-effort and run after the transaction commits.
	var instanceId string
	var workItemIDs []string
	var submitNotifications pendingNotifications
	var finalNotifications pendingNotifications

	txErr := s.persistence.RunInTransaction(ctx, func(txCtx context.Context) error {
		// Reset closure-scoped state on each deadlock-retry attempt.
		instanceId = ""
		workItemIDs = nil
		submitNotifications = pendingNotifications{}
		finalNotifications = pendingNotifications{}

		createdInstance, err := s.persistence.Insert(processTableWriteContext(txCtx, "ApprovalService: create ProcessInstance"), constants.TableProcessInstance, instanceRecord, user)
		if err != nil {
			s.logger.Error("Failed to create ProcessInstance", err)
			return apperrors.NewInternalError("failed to create approval instance", err)
		}
		instanceId, _ = createdInstance[constants.FieldID].(string)

		// Step 8: Create ProcessInstanceNode for the first step
		if startPendingApproval && firstNodeId != "" {
			nodeInstanceRecord := models.SObject{
				"ProcessInstanceId": instanceId,
				"ProcessNodeId":     firstNodeId,
				"ProcessNodeName":   firstNodeName,
				"NodeStatus":        constants.ApprovalStatusPending,
				"CreatedDate":       now,
			}
			if _, err = s.persistence.Insert(processTableWriteContext(txCtx, "ApprovalService: create ProcessInstanceNode"), constants.TableProcessInstanceNode, nodeInstanceRecord, user); err != nil {
				s.logger.Error("Failed to create ProcessInstanceNode", err)
			}
		}

		// Step 9: Create one pending work item per approver for the first step.
		if startPendingApproval {
			workItemIDs, err = s.createWorkitems(txCtx, instanceId, actorIDs, req.Comments, now, user)
			if err != nil {
				s.logger.Error("Failed to create ProcessInstanceWorkitem", err)
				return apperrors.NewInternalError("failed to create approval work item", err)
			}
		}

		// Submit-event process actions: FieldUpdates run inside the tx, Alerts
		// are stashed for post-commit execution.
		submitActions, fetchErr := s.fetchProcessActions(txCtx, processDefId, "", "Submit", user)
		if fetchErr != nil {
			s.logger.Warn(fmt.Sprintf("Failed to query Submit ProcessActions for processDefId=%s: %v", processDefId, fetchErr))
		} else if len(submitActions) > 0 {
			if err := s.executeProcessActionMutations(txCtx, submitActions, objectName, req.RecordID, user); err != nil {
				return err
			}
			submitNotifications = pendingNotifications{
				actions:       submitActions,
				objectApiName: objectName,
				targetID:      req.RecordID,
				actorIDs:      actorIDs,
			}
		}

		if !startPendingApproval {
			finalActions, fetchErr := s.fetchProcessActions(txCtx, processDefId, "", "FinalApproval", user)
			if fetchErr != nil {
				s.logger.Warn(fmt.Sprintf("Failed to query FinalApproval ProcessActions for processDefId=%s: %v", processDefId, fetchErr))
			} else if len(finalActions) > 0 {
				if err := s.executeProcessActionMutations(txCtx, finalActions, objectName, req.RecordID, user); err != nil {
					return err
				}
				finalNotifications = pendingNotifications{
					actions:       finalActions,
					objectApiName: objectName,
					targetID:      req.RecordID,
				}
			}
		}
		return nil
	})
	if txErr != nil {
		return nil, txErr
	}

	// Post-commit: run notification actions (Alert) best-effort.
	var actionFailures []ProcessActionFailure
	for _, batch := range []pendingNotifications{submitNotifications, finalNotifications} {
		if failures := s.executeProcessActionNotifications(ctx, batch.actions, batch.objectApiName, batch.targetID, batch.actorIDs, user); len(failures) > 0 {
			actionFailures = append(actionFailures, failures...)
		}
	}

	responseObj := models.SObject{
		"ProcessInstanceId": instanceId,
		"TargetObjectId":    req.RecordID,
		"InstanceStatus":    initialStatus,
		"ActorIds":          actorIDs,
		"NewWorkitemIds":    workItemIDs,
	}
	if len(workItemIDs) > 0 {
		responseObj[constants.FieldID] = workItemIDs[0]
	}
	if len(actorIDs) > 0 {
		responseObj["ActorId"] = actorIDs[0]
	}
	if len(actionFailures) > 0 {
		responseObj["ActionFailures"] = actionFailures
	}

	return responseObj, nil
}

// Approve approves a pending work item.
// 1. Finds the ProcessInstanceWorkitem
// 2. Updates the ProcessInstance status to Approved
// 3. Deletes the work item (marking it as processed)
func (s *ApprovalService) Approve(ctx context.Context, workItemID, comments string, user *models.UserSession) (models.SObject, error) {
	return s.processAction(ctx, workItemID, constants.ApprovalStatusApproved, comments, user)
}

// Reject rejects a pending work item.
// 1. Finds the ProcessInstanceWorkitem
// 2. Updates the ProcessInstance status to Rejected
// 3. Deletes the work item (marking it as processed)
func (s *ApprovalService) Reject(ctx context.Context, workItemID, comments string, user *models.UserSession) (models.SObject, error) {
	return s.processAction(ctx, workItemID, constants.ApprovalStatusRejected, comments, user)
}

// ListPendingApprovals returns the current user's pending work items enriched
// with the linked record and process metadata. This read path intentionally
// uses internal service-context lookups so the UI does not depend on public
// ProcessInstance record-query visibility.
func (s *ApprovalService) ListPendingApprovals(ctx context.Context, user *models.UserSession) ([]PendingApprovalView, error) {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: read pending approvals")

	filter := fmt.Sprintf("ActorId == '%s'", user.ID)
	workItems, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstanceWorkitem, filter, user, "CreatedDate", "desc", 200)
	if err != nil {
		return nil, err
	}

	views := make([]PendingApprovalView, 0, len(workItems.Records))
	for _, workItem := range workItems.Records {
		workItemID := approvalStringValue(workItem[constants.FieldID])
		processInstanceID := approvalStringValue(workItem["ProcessInstanceId"])
		if workItemID == "" || processInstanceID == "" {
			continue
		}

		instance, err := s.persistence.GetRaw(ctx, constants.TableProcessInstance, processInstanceID)
		if err != nil {
			s.logger.Warn(fmt.Sprintf("ListPendingApprovals: failed to read ProcessInstance %s: %v", processInstanceID, err))
			continue
		}
		if approvalStringValue(instance["Status"]) != constants.ApprovalStatusPending {
			continue
		}

		processDefinitionID := approvalStringValue(instance["ProcessDefinitionId"])
		targetObjectID := approvalStringValue(instance["TargetObjectId"])
		submittedByID := approvalStringValue(instance["SubmittedById"])
		submittedDate := approvalStringValue(instance["CreatedDate"])

		objectAPIName := ""
		if processDefinitionID != "" {
			if processDef, err := s.persistence.GetRaw(ctx, constants.TableProcessDefinition, processDefinitionID); err == nil {
				objectAPIName = approvalStringValue(processDef["TableEnumOrId"])
			}
		}

		submittedByName := ""
		if submittedByID != "" {
			if submitter, err := s.persistence.GetRaw(ctx, constants.TableUser, submittedByID); err == nil {
				submittedByName = firstNonEmptyApprovalValue(submitter["Name"], submitter["FirstName"])
			}
		}

		recordName := ""
		if objectAPIName != "" && targetObjectID != "" {
			if record, err := s.persistence.GetRaw(ctx, objectAPIName, targetObjectID); err == nil {
				recordName = firstNonEmptyApprovalValue(record["Name"], record["Subject"], record["LastName"])
			}
		}

		views = append(views, PendingApprovalView{
			WorkItemID:          workItemID,
			ProcessInstanceID:   processInstanceID,
			ProcessDefinitionID: processDefinitionID,
			TargetObjectID:      targetObjectID,
			ObjectAPIName:       objectAPIName,
			SubmittedByID:       submittedByID,
			SubmittedByName:     submittedByName,
			SubmittedDate:       submittedDate,
			ActorID:             approvalStringValue(workItem["ActorId"]),
			OriginalActorID:     approvalStringValue(workItem["OriginalActorId"]),
			Comments:            approvalStringValue(workItem["Comments"]),
			RecordName:          recordName,
		})
	}

	return views, nil
}

// GetLatestProcessInstance returns the newest approval instance for the record.
func (s *ApprovalService) GetLatestProcessInstance(ctx context.Context, recordID string, user *models.UserSession) (*ProcessInstanceSummary, error) {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: read record approval instance")

	filter := fmt.Sprintf("TargetObjectId == '%s'", recordID)
	result, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstance, filter, user, "CreatedDate", "desc", 1)
	if err != nil {
		return nil, err
	}
	if result.TotalSize == 0 || len(result.Records) == 0 {
		return nil, nil
	}

	instance := result.Records[0]
	submittedByID := approvalStringValue(instance["SubmittedById"])
	submittedByName := ""
	if submittedByID != "" {
		if submitter, err := s.persistence.GetRaw(ctx, constants.TableUser, submittedByID); err == nil {
			submittedByName = firstNonEmptyApprovalValue(submitter["Name"], submitter["FirstName"])
		}
	}

	return &ProcessInstanceSummary{
		ID:                  approvalStringValue(instance[constants.FieldID]),
		ProcessDefinitionID: approvalStringValue(instance["ProcessDefinitionId"]),
		TargetObjectID:      approvalStringValue(instance["TargetObjectId"]),
		Status:              approvalStringValue(instance["Status"]),
		SubmittedByID:       submittedByID,
		SubmittedByName:     submittedByName,
		CreatedDate:         approvalStringValue(instance["CreatedDate"]),
		CompletedDate:       approvalStringValue(instance["CompletedDate"]),
	}, nil
}

// ProjectedNodeOutcome describes the projected runtime outcome for a single
// ProcessNode given the current state of the target record. Issued by
// ProjectChain so the UI can render conditional approval steps as Skipped /
// Not Required immediately on submission, before any runtime transition has
// reached them.
type ProjectedNodeOutcome string

const (
	// ProjectedOutcomeWillRun — step's entry criteria match (or step has no
	// criteria). The step will be instantiated when the runtime reaches it.
	ProjectedOutcomeWillRun ProjectedNodeOutcome = "WillRun"
	// ProjectedOutcomeWillSkip — step's entry criteria do not match the
	// record, OR an earlier step's criteria failed with ifCriteriaNotMet =
	// ApproveRecord (which auto-finalizes the process before this step is
	// reached). Either way, the step will not run for this record.
	ProjectedOutcomeWillSkip ProjectedNodeOutcome = "WillSkip"
)

// ProjectedNode is a single ProcessNode's projected outcome.
type ProjectedNode struct {
	ProcessNodeID string               `json:"processNodeId"`
	Outcome       ProjectedNodeOutcome `json:"outcome"`
}

// ProjectChain pre-evaluates every ProcessNode's entry criteria for the given
// target record and returns the projected outcome (WillRun / WillSkip) per
// node, ordered by StepOrder. This is the read-side counterpart to
// tryAdvanceToNextStep — it answers "if the runtime were to advance through
// every step right now, which would actually run?" without mutating any
// runtime state.
//
// The frontend uses these projections to render conditional approval steps
// that have not yet been instantiated (no ProcessInstanceNode) as Skipped
// when their criteria don't match the record, instead of showing them as
// Upcoming and misleading the submitter about which approvers are required.
//
// Authorization model — Salesforce-parity participant gate:
//
// Approval criteria evaluation runs under SystemContext both at runtime
// (tryAdvanceToNextStep) and here. That is intentional: Salesforce's
// approval engine evaluates criteria in system mode (FLS / sharing don't
// filter), and commit 800dac2e codified the same elevation in this codebase
// after non-admin Approve/Reject/Recall paths broke under lower contexts
// (issues #1741, #1870, #1907, #1911). Downgrading the criteria path to
// caller context, or per-field FLS-walking every formula, both re-introduce
// that class of bug.
//
// Instead, the gate sits at the entry point, matching how Salesforce exposes
// the approval chain UI: only participants of an active ProcessInstance see
// the projection. A participant is one of:
//
//   - SubmittedById == user.ID (submitter has already proven record access).
//   - A ProcessInstanceWorkitem exists for this instance with ActorId or
//     OriginalActorId == user.ID (current or historical approver — Salesforce
//     grants implicit sharing to the record while the workitem is open).
//   - The user holds ModifyAllData (admin escape hatch — same primitive
//     Salesforce uses for org-wide approval visibility).
//
// Non-participants receive a permission error before any criteria load, so
// the WillRun/WillSkip side-channel is bounded to the same audience that
// already sees the engine's behavior at runtime.
//
// Walks ProcessNode rows in StepOrder ASC. For each:
//   - stepCriteriaMatches → WillRun.
//   - criteria don't match + ifCriteriaNotMet=GotoNextStep → WillSkip,
//     keep evaluating later steps.
//   - criteria don't match + ifCriteriaNotMet=ApproveRecord (or empty) →
//     WillSkip, AND mark all later steps as WillSkip too because the
//     process would auto-finalize before reaching them.
func (s *ApprovalService) ProjectChain(ctx context.Context, processDefID, objectAPIName, recordID string, user *models.UserSession) ([]ProjectedNode, error) {
	if processDefID == "" {
		return nil, apperrors.NewValidationError("processDefId", "required")
	}
	if recordID == "" {
		return nil, apperrors.NewValidationError("recordId", "required")
	}

	// Approval engine context — see the function comment above for the
	// Salesforce-parity rationale. The caller's session ctx is preserved
	// only for documentation; everything below runs under sysCtx because
	// (a) the metadata reads need it for RLS bypass on Process tables and
	// (b) the participant gate downstream is what restricts exposure, not
	// per-table ctx switching.
	sysCtx := constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: project approval chain")

	// Load ProcessDefinition for metadata access. Quote-escape filter args
	// defense-in-depth — the SQL walker uses parameterized queries, but a
	// crafted ID could otherwise inject extra filter clauses against the
	// metadata table. Pattern matches stepCriteriaMatches/resolveFieldUpdate.
	safeProcessDefID := strings.ReplaceAll(processDefID, "'", "''")
	safeRecordID := strings.ReplaceAll(recordID, "'", "''")
	safeUserID := strings.ReplaceAll(user.ID, "'", "''")

	pdFilter := fmt.Sprintf("Id == '%s'", safeProcessDefID)
	pdResult, err := s.query.QueryWithFilter(sysCtx, constants.TableProcessDefinition, pdFilter, user, "", "", 1)
	if err != nil {
		return nil, err
	}
	if pdResult == nil || len(pdResult.Records) == 0 {
		return nil, ErrNoActiveApprovalProcess
	}
	processDef := pdResult.Records[0]

	// The ProcessDefinition's TableEnumOrId is the only trusted source for the
	// object name. A caller-supplied objectApiName must match — otherwise an
	// attacker could bind a process owned by one object but probe records
	// from another table via the participant gate + criteria side-channel.
	canonicalObject, _ := processDef["TableEnumOrId"].(string)
	if canonicalObject == "" {
		return nil, apperrors.NewValidationError("processDefinition", "missing TableEnumOrId")
	}
	if objectAPIName != "" && objectAPIName != canonicalObject {
		return nil, apperrors.NewValidationError("objectApiName", "does not match ProcessDefinition")
	}
	objectAPIName = canonicalObject

	// Participant gate — locate the *active* ProcessInstance for this record +
	// process pair, then verify the caller is the submitter, an approver
	// (current or historical workitem), or a system admin. See the function
	// comment for the Salesforce-parity rationale.
	//
	// Status == 'Pending' is load-bearing: without it, a stale submitter of an
	// already-Approved/Rejected/Removed instance can keep calling this endpoint
	// long after their approval is closed, receiving WillRun/WillSkip values
	// computed from the record's CURRENT criteria fields under SystemContext —
	// even after their RLS / FLS access to those fields has changed. Filtering
	// on active status caps the projection's exposure window to the lifetime
	// of the live approval, matching Salesforce's "approval chain UI is for
	// participants of the live process" model.
	piFilter := fmt.Sprintf("TargetObjectId == '%s' AND ProcessDefinitionId == '%s' AND Status == '%s'",
		safeRecordID, safeProcessDefID, constants.ApprovalStatusPending)
	piResult, err := s.query.QueryWithFilter(sysCtx, constants.TableProcessInstance, piFilter, user, "CreatedDate", "DESC", 1)
	if err != nil {
		return nil, err
	}
	if piResult == nil || len(piResult.Records) == 0 {
		return nil, ErrNoActiveApprovalProcess
	}
	instance := piResult.Records[0]
	instanceID, _ := instance[constants.FieldID].(string)
	submittedByID, _ := instance["SubmittedById"].(string)

	allowed := submittedByID == user.ID
	if !allowed && instanceID != "" {
		// Approver path — workitem assigned to or originally assigned to this user.
		safeInstanceID := strings.ReplaceAll(instanceID, "'", "''")
		wiFilter := fmt.Sprintf("ProcessInstanceId == '%s' AND (ActorId == '%s' OR OriginalActorId == '%s')",
			safeInstanceID, safeUserID, safeUserID)
		wiResult, err := s.query.QueryWithFilter(sysCtx, constants.TableProcessInstanceWorkitem, wiFilter, user, "", "", 1)
		if err != nil {
			return nil, err
		}
		if wiResult != nil && wiResult.TotalSize > 0 {
			allowed = true
		}
	}
	if !allowed && s.permissions != nil {
		// Admin escape hatch — Salesforce equivalent: ModifyAllData grants
		// visibility into approvals across all objects.
		if s.permissions.CheckSystemPermission(sysCtx, user, string(constants.PermModifyAllData)) {
			allowed = true
		}
	}
	if !allowed {
		return nil, apperrors.NewPermissionError("read", "approval projection")
	}

	// Load all ProcessNodes ordered by StepOrder.
	nodeFilter := fmt.Sprintf("ProcessDefinitionId == '%s'", safeProcessDefID)
	nodeResult, err := s.query.QueryWithFilter(sysCtx, "ProcessNode", nodeFilter, user, "StepOrder", "ASC", 100)
	if err != nil {
		return nil, err
	}
	if nodeResult == nil || nodeResult.TotalSize == 0 {
		return []ProjectedNode{}, nil
	}

	// Locate the current step in the runtime — i.e. the active PENDING
	// ProcessInstanceNode. The runtime path (tryAdvanceToNextStep) treats this
	// node as the current step and starts evaluating downstream candidates
	// from currentIdx + 1 only when this step is approved. The projection
	// MUST mirror that.
	//
	// Picking the highest *historical* node index instead would mismatch the
	// runtime in BackToPrevious flows: e.g. n1 approved → n2 approved → n3
	// rejected → n2 pending (rollback). Historical max is n3 (idx 2), so a
	// projection rooted there would walk from n4 — but tryAdvanceToNextStep
	// will start from the rolled-back pending n2 (idx 1) and re-evaluate n3,
	// possibly finalizing the process before n4 is reached. The projection
	// would falsely call n4 WillRun.
	//
	// Submit creates the first ProcessInstanceNode unconditionally without
	// evaluating its own criteria, so re-evaluating the current step's
	// criteria here would also let a current-step criteria mismatch
	// propagate processFinalized=true and silently mark every later step
	// WillSkip — even though the runtime would happily evaluate them after
	// the current step is approved.
	safeInstanceIDForNodes := strings.ReplaceAll(instanceID, "'", "''")
	pendingNodeFilter := fmt.Sprintf("ProcessInstanceId == '%s' AND NodeStatus == '%s'",
		safeInstanceIDForNodes, constants.ApprovalStatusPending)
	pendingNodeResult, err := s.query.QueryWithFilter(sysCtx, constants.TableProcessInstanceNode, pendingNodeFilter, user, "CreatedDate", "DESC", 1)
	if err != nil {
		return nil, err
	}
	currentIdx := -1
	if pendingNodeResult != nil && len(pendingNodeResult.Records) > 0 {
		pendingProcessNodeID, _ := pendingNodeResult.Records[0]["ProcessNodeId"].(string)
		if pendingProcessNodeID != "" {
			for i, node := range nodeResult.Records {
				if id, ok := node[constants.FieldID].(string); ok && id == pendingProcessNodeID {
					currentIdx = i
					break
				}
			}
		}
	}
	// If no pending ProcessInstanceNode is found for an active instance, we
	// can't pin the runtime's execution position — refuse to project rather
	// than guess. The frontend's runtime data still drives the chain UI for
	// instantiated nodes; un-instantiated nodes fall back to UPCOMING via the
	// builder's no-projection path. This is the conservative default that
	// avoids the BackToPrevious mis-projection class entirely.
	if currentIdx == -1 {
		return []ProjectedNode{}, nil
	}

	projections := make([]ProjectedNode, 0, len(nodeResult.Records))
	processFinalized := false

	for i, node := range nodeResult.Records {
		nodeID, _ := node[constants.FieldID].(string)
		if nodeID == "" {
			continue
		}

		// Already-instantiated step (i ≤ currentIdx). The runtime owns this
		// step's outcome via ProcessInstanceNode; the frontend reads runtime
		// data for these and ignores the projection map. Emit WillRun so the
		// shape stays predictable, and crucially DO NOT evaluate criteria or
		// touch processFinalized — that would diverge from runtime semantics.
		if i <= currentIdx {
			projections = append(projections, ProjectedNode{ProcessNodeID: nodeID, Outcome: ProjectedOutcomeWillRun})
			continue
		}

		if processFinalized {
			projections = append(projections, ProjectedNode{ProcessNodeID: nodeID, Outcome: ProjectedOutcomeWillSkip})
			continue
		}

		if s.stepCriteriaMatches(sysCtx, processDef, node, objectAPIName, recordID, user) {
			projections = append(projections, ProjectedNode{ProcessNodeID: nodeID, Outcome: ProjectedOutcomeWillRun})
			continue
		}

		// Criteria don't match — outcome depends on ifCriteriaNotMet.
		// Salesforce default (field absent) is ApproveRecord, which finalizes
		// the process. GotoNextStep keeps walking subsequent steps.
		behavior := "ApproveRecord"
		if metaStep := s.metadataStepForNode(processDef, node); metaStep != nil && metaStep.IfCriteriaNotMet != "" {
			behavior = metaStep.IfCriteriaNotMet
		}
		projections = append(projections, ProjectedNode{ProcessNodeID: nodeID, Outcome: ProjectedOutcomeWillSkip})
		if behavior != "GotoNextStep" {
			processFinalized = true
		}
	}

	return projections, nil
}

// Reassign changes the assigned approver on a pending work item.
// Only the current ActorId or a system admin can reassign.
func (s *ApprovalService) Reassign(ctx context.Context, workItemID, newActorID string, user *models.UserSession) error {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: managing approval system tables")

	workItemFilter := fmt.Sprintf("Id == '%s'", workItemID)
	wiResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstanceWorkitem, workItemFilter, user, "", "", 1)
	if err != nil || wiResult.TotalSize == 0 || len(wiResult.Records) == 0 {
		return ErrWorkItemNotFound
	}
	workItem := wiResult.Records[0]
	instanceId, _ := workItem["ProcessInstanceId"].(string)

	// Authorization: only current actor or system admin can reassign
	currentActorId, _ := workItem["ActorId"].(string)
	if currentActorId != user.ID && !user.IsSystemAdmin {
		return ErrNotAuthorized
	}

	// Check if the current approval step allows delegation (upstream #894):
	// Done before the transaction so an authorization failure doesn't touch any
	// state. Pure read path — no mutation.
	if instanceId != "" {
		// Find the current pending ProcessInstanceNode for this instance
		nodeFilter := fmt.Sprintf("ProcessInstanceId == '%s' && NodeStatus == '%s'", instanceId, constants.ApprovalStatusPending)
		nodeResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstanceNode, nodeFilter, user, "", "", 1)
		if err == nil && len(nodeResult.Records) > 0 {
			processNodeName, _ := nodeResult.Records[0]["ProcessNodeName"].(string)

			// Get the ProcessDefinition to check the AllowDelegate flag for this step
			instanceFilter := fmt.Sprintf("Id == '%s'", instanceId)
			instResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstance, instanceFilter, user, "", "", 1)
			if err == nil && len(instResult.Records) > 0 {
				instance := instResult.Records[0]
				processDefID, _ := instance["ProcessDefinitionId"].(string)

				if processDefID != "" {
					pdFilter := fmt.Sprintf("Id == '%s'", processDefID)
					pdResult, pdErr := s.query.QueryWithFilter(ctx, constants.TableProcessDefinition, pdFilter, user, "", "", 1)
					if pdErr == nil && len(pdResult.Records) > 0 {
						metadataStr, _ := pdResult.Records[0]["Metadata"].(string)
						if metadataStr != "" {
							// Parse the approval process metadata to check AllowDelegate for the current step
							parsedMetadata, parseErr := ParseApprovalProcessMetadata(metadataStr)
							if parseErr == nil && parsedMetadata != nil {
								// Look for the step with the matching name
								for _, step := range parsedMetadata.Steps {
									if step.Name == processNodeName || step.Label == processNodeName {
										// Only deny delegation if it's explicitly set to false
										if !step.AllowDelegate {
											return ErrDelegationNotAllowed
										}
										break
									}
								}
							}
						}
					}
				}
			}
		}
	}

	var reassignNotifications *pendingNotifications

	txErr := s.persistence.RunInTransaction(ctx, func(txCtx context.Context) error {
		// Reset closure-scoped state so deadlock-retry attempts are clean.
		reassignNotifications = nil

		// Update ActorId (leave OriginalActorId unchanged)
		updates := models.SObject{
			"ActorId": newActorID,
		}
		if err := s.persistence.Update(processTableWriteContext(txCtx, "ApprovalService: update ProcessInstanceWorkitem"), constants.TableProcessInstanceWorkitem, workItemID, updates, user); err != nil {
			return err
		}

		if instanceId == "" {
			return nil
		}

		instanceFilter := fmt.Sprintf("Id == '%s'", instanceId)
		instResult, err := s.query.QueryWithFilter(txCtx, constants.TableProcessInstance, instanceFilter, user, "", "", 1)
		if err != nil || instResult.TotalSize == 0 || len(instResult.Records) == 0 {
			return nil
		}
		instance := instResult.Records[0]
		processDefID, _ := instance["ProcessDefinitionId"].(string)
		targetObjectID, _ := instance["TargetObjectId"].(string)
		if processDefID == "" || targetObjectID == "" {
			return nil
		}

		objectAPIName, resolveErr := s.resolveProcessObjectAPIName(txCtx, processDefID, "", user)
		if resolveErr != nil || objectAPIName == "" {
			return nil
		}

		actions, fetchErr := s.fetchProcessActions(txCtx, processDefID, "", "Reassign", user)
		if fetchErr != nil {
			s.logger.Warn(fmt.Sprintf("Failed to query Reassign ProcessActions for processDefId=%s: %v", processDefID, fetchErr))
			return nil
		}
		if len(actions) == 0 {
			return nil
		}
		if err := s.executeProcessActionMutations(txCtx, actions, objectAPIName, targetObjectID, user); err != nil {
			return err
		}
		reassignNotifications = &pendingNotifications{
			actions:       actions,
			objectApiName: objectAPIName,
			targetID:      targetObjectID,
			actorIDs:      []string{newActorID},
		}
		return nil
	})
	if txErr != nil {
		return txErr
	}

	if reassignNotifications != nil {
		s.executeProcessActionNotifications(ctx, reassignNotifications.actions, reassignNotifications.objectApiName, reassignNotifications.targetID, reassignNotifications.actorIDs, user)
	}
	return nil
}

// processAction is the shared logic for Approve and Reject actions.
// For Approve: checks if there is a next step in the process definition.
//   - If a next step exists, advances the chain (creates new node + workitem) without finalising.
//   - If no next step, marks the ProcessInstance as Approved.
//
// For Reject: always finalises the ProcessInstance as Rejected.
func (s *ApprovalService) processAction(ctx context.Context, workItemID, newStatus, comments string, user *models.UserSession) (models.SObject, error) {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: managing approval system tables")

	// Step 1: Find the work item
	workItemFilter := fmt.Sprintf("Id == '%s'", workItemID)
	wiResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstanceWorkitem, workItemFilter, user, "", "", 1)
	if err != nil || wiResult.TotalSize == 0 || len(wiResult.Records) == 0 {
		return nil, ErrWorkItemNotFound
	}
	workItem := wiResult.Records[0]
	instanceId, _ := workItem["ProcessInstanceId"].(string)
	if instanceId == "" {
		return nil, ErrWorkItemNotFound
	}

	// Authorization: verify the caller is the assigned approver (or a system admin).
	// Fail-closed: deny unless user IS the actor or a system admin.
	workItemActorId, _ := workItem["ActorId"].(string)
	if workItemActorId != user.ID && !user.IsSystemAdmin {
		return nil, ErrNotAuthorized
	}

	// Step 2: Get the ProcessInstance to find the ProcessDefinitionId and TargetObjectId
	instanceFilter := fmt.Sprintf("Id == '%s'", instanceId)
	instResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstance, instanceFilter, user, "", "", 1)
	if err != nil || instResult.TotalSize == 0 || len(instResult.Records) == 0 {
		return nil, apperrors.NewInternalError("failed to look up ProcessInstance "+instanceId, err)
	}
	instance := instResult.Records[0]
	processDefId, _ := instance["ProcessDefinitionId"].(string)
	targetObjectId, _ := instance["TargetObjectId"].(string)
	submittedByID, _ := instance["SubmittedById"].(string)

	now := time.Now().UTC().Format(time.RFC3339)

	// Run steps 3-6 in a transaction to ensure atomicity.
	// If tryAdvanceToNextStep fails, the node update and workitem deletion
	// are rolled back, preventing premature finalization.
	var nextActorIDs []string
	var newWorkItemIDs []string
	advancedToNext := false
	stepStillPending := false
	currentProcessNodeDefID := ""
	processObjectAPIName := ""
	var pendingNotificationBatches []pendingNotifications

	txErr := s.persistence.RunInTransaction(ctx, func(txCtx context.Context) error {
		// Reset closure variables on each attempt (safe across deadlock retries)
		advancedToNext = false
		stepStillPending = false
		nextActorIDs = nil
		newWorkItemIDs = nil
		currentProcessNodeDefID = ""
		processObjectAPIName = ""
		pendingNotificationBatches = nil

		var processDef models.SObject
		if processDefId != "" {
			defFilter := fmt.Sprintf("Id == '%s'", processDefId)
			defResult, defErr := s.query.QueryWithFilter(txCtx, constants.TableProcessDefinition, defFilter, user, "", "", 1)
			if defErr == nil && defResult != nil && len(defResult.Records) > 0 {
				processDef = defResult.Records[0]
				processObjectAPIName, _ = processDef["TableEnumOrId"].(string)
			}
		}

		// Step 3: Find the current pending ProcessInstanceNode and its backing ProcessNode.
		var currentProcessNode models.SObject
		var currentNodeID string
		nodeFilter := fmt.Sprintf("ProcessInstanceId == '%s' && NodeStatus == '%s'", instanceId, constants.ApprovalStatusPending)
		nodeResult, err := s.query.QueryWithFilter(txCtx, constants.TableProcessInstanceNode, nodeFilter, user, "", "", 1)
		if err == nil && len(nodeResult.Records) > 0 {
			currentNodeID, _ = nodeResult.Records[0][constants.FieldID].(string)
			processNodeID, _ := nodeResult.Records[0]["ProcessNodeId"].(string)
			currentProcessNodeDefID = processNodeID
			processNodeName, _ := nodeResult.Records[0]["ProcessNodeName"].(string)
			if processNodeID != "" {
				processNodeFilter := fmt.Sprintf("Id == '%s'", processNodeID)
				processNodeResult, nodeErr := s.query.QueryWithFilter(txCtx, constants.TableProcessNode, processNodeFilter, user, "", "", 1)
				if nodeErr == nil && processNodeResult != nil && len(processNodeResult.Records) > 0 {
					currentProcessNode = processNodeResult.Records[0]
				} else {
					currentProcessNode = models.SObject{
						constants.FieldID: processNodeID,
						"Name":            processNodeName,
					}
				}
			}
		}

		// Step 4: Gather pending work items for the current step so we can
		// enforce FirstResponse vs Unanimous behavior transactionally.
		workItemsFilter := fmt.Sprintf("ProcessInstanceId == '%s'", instanceId)
		workItemsResult, err := s.query.QueryWithFilter(txCtx, constants.TableProcessInstanceWorkitem, workItemsFilter, user, "", "", 100)
		if err != nil {
			return fmt.Errorf("failed to query ProcessInstanceWorkitem siblings: %w", err)
		}

		pendingWorkItemIDs := make([]string, 0, len(workItemsResult.Records))
		remainingActorIDs := make([]string, 0, len(workItemsResult.Records))
		remainingWorkItemIDs := make([]string, 0, len(workItemsResult.Records))
		for _, item := range workItemsResult.Records {
			itemID, _ := item[constants.FieldID].(string)
			if itemID == "" {
				continue
			}
			pendingWorkItemIDs = append(pendingWorkItemIDs, itemID)
			if itemID == workItemID {
				continue
			}
			remainingWorkItemIDs = append(remainingWorkItemIDs, itemID)
			if actorID, _ := item["ActorId"].(string); actorID != "" {
				remainingActorIDs = append(remainingActorIDs, actorID)
			}
		}

		approvalMode := "FirstResponse"
		if processDef != nil && currentProcessNode != nil {
			approvalMode = s.approvalModeForNode(processDef, currentProcessNode)
		}

		// In Unanimous mode, a single approval only clears that approver's work
		// item while the step remains pending until every approver has responded.
		finalizeCurrentStep := true
		deleteIDs := pendingWorkItemIDs
		if newStatus == constants.ApprovalStatusApproved && strings.EqualFold(approvalMode, "Unanimous") && len(remainingWorkItemIDs) > 0 {
			finalizeCurrentStep = false
			stepStillPending = true
			nextActorIDs = remainingActorIDs
			newWorkItemIDs = remainingWorkItemIDs
			deleteIDs = []string{workItemID}
		}

		for _, deleteID := range deleteIDs {
			if delErr := s.persistence.Delete(processTableWriteContext(txCtx, "ApprovalService: delete ProcessInstanceWorkitem"), constants.TableProcessInstanceWorkitem, deleteID, user); delErr != nil {
				return fmt.Errorf("failed to delete ProcessInstanceWorkitem %s: %w", deleteID, delErr)
			}
		}

		// Execute step-level actions immediately when this specific work item is processed,
		// regardless of whether the entire step is still pending or completed.
		// FieldUpdates run inside the tx; Alerts are stashed for post-commit.
		if currentProcessNodeDefID != "" && targetObjectId != "" && processObjectAPIName != "" {
			actionEvent := ""
			if newStatus == constants.ApprovalStatusApproved {
				actionEvent = "StepApproval"
			} else if newStatus == constants.ApprovalStatusRejected {
				actionEvent = "StepRejection"
			}
			if actionEvent != "" {
				stepActions, fetchErr := s.fetchProcessActions(txCtx, processDefId, currentProcessNodeDefID, actionEvent, user)
				if fetchErr != nil {
					s.logger.Warn(fmt.Sprintf("Failed to query %s ProcessActions for node=%s: %v", actionEvent, currentProcessNodeDefID, fetchErr))
				} else if len(stepActions) > 0 {
					if err := s.executeProcessActionMutations(txCtx, stepActions, processObjectAPIName, targetObjectId, user); err != nil {
						return err
					}
					pendingNotificationBatches = append(pendingNotificationBatches, pendingNotifications{
						actions:       stepActions,
						objectApiName: processObjectAPIName,
						targetID:      targetObjectId,
					})
				}
			}
		}

		if !finalizeCurrentStep {
			return nil
		}

		if currentNodeID != "" {
			nodeUpdates := models.SObject{
				"NodeStatus":    newStatus,
				"CompletedDate": now,
				"LastActorId":   user.ID,
				"Comments":      comments,
			}
			if updateErr := s.persistence.Update(processTableWriteContext(txCtx, "ApprovalService: update ProcessInstanceNode"), constants.TableProcessInstanceNode, currentNodeID, nodeUpdates, user); updateErr != nil {
				return fmt.Errorf("failed to update ProcessInstanceNode: %w", updateErr)
			}
		}

		// Step 5: For Approve, advance to the next step if one exists
		if newStatus == constants.ApprovalStatusApproved && processDefId != "" {
			var advanceErr error
			advancedToNext, nextActorIDs, newWorkItemIDs, advanceErr = s.tryAdvanceToNextStep(txCtx, processDef, instanceId, currentProcessNode, targetObjectId, submittedByID, now, user)
			if advanceErr != nil {
				return advanceErr
			}
		}

		// Step 5b: For Reject, check RejectBehavior on the current ProcessNode
		if newStatus == constants.ApprovalStatusRejected && processDefId != "" {
			var rejectErr error
			advancedToNext, nextActorIDs, newWorkItemIDs, rejectErr = s.tryRejectBackToPrevious(txCtx, processDef, instanceId, currentProcessNode, targetObjectId, submittedByID, now, user)
			if rejectErr != nil {
				return rejectErr
			}
		}

		// Step 6: If we did NOT advance/rollback to another step, finalise the ProcessInstance
		if !advancedToNext {
			instanceUpdates := models.SObject{
				"Status":        newStatus,
				"CompletedDate": now,
			}
			if updateErr := s.persistence.Update(processTableWriteContext(txCtx, "ApprovalService: update ProcessInstance"), constants.TableProcessInstance, instanceId, instanceUpdates, user); updateErr != nil {
				return fmt.Errorf("failed to finalize ProcessInstance: %w", updateErr)
			}
		}

		// Step 7: Terminal and next-step lifecycle actions — run FieldUpdates
		// inside the tx so they share atomicity with the state transition.
		// Alerts are stashed for post-commit best-effort delivery.
		if !advancedToNext && !stepStillPending && processDefId != "" {
			terminalEvent := ""
			if newStatus == "Approved" {
				terminalEvent = "FinalApproval"
			} else if newStatus == "Rejected" {
				terminalEvent = "FinalRejection"
			}
			if terminalEvent != "" {
				objApiName, err := s.resolveProcessObjectAPIName(txCtx, processDefId, processObjectAPIName, user)
				if err == nil && objApiName != "" {
					terminalActions, fetchErr := s.fetchProcessActions(txCtx, processDefId, "", terminalEvent, user)
					if fetchErr != nil {
						s.logger.Warn(fmt.Sprintf("Failed to query %s ProcessActions for processDefId=%s: %v", terminalEvent, processDefId, fetchErr))
					} else if len(terminalActions) > 0 {
						if err := s.executeProcessActionMutations(txCtx, terminalActions, objApiName, targetObjectId, user); err != nil {
							return err
						}
						pendingNotificationBatches = append(pendingNotificationBatches, pendingNotifications{
							actions:       terminalActions,
							objectApiName: objApiName,
							targetID:      targetObjectId,
						})
					}
				}
			}
		}

		if newStatus == "Approved" && advancedToNext && processDefId != "" && len(nextActorIDs) > 0 {
			objApiName, err := s.resolveProcessObjectAPIName(txCtx, processDefId, processObjectAPIName, user)
			if err == nil && objApiName != "" {
				nextStepActions, fetchErr := s.fetchProcessActions(txCtx, processDefId, "", "StepApproval", user)
				if fetchErr != nil {
					s.logger.Warn(fmt.Sprintf("Failed to query StepApproval ProcessActions for processDefId=%s: %v", processDefId, fetchErr))
				} else if len(nextStepActions) > 0 {
					if err := s.executeProcessActionMutations(txCtx, nextStepActions, objApiName, targetObjectId, user); err != nil {
						return err
					}
					pendingNotificationBatches = append(pendingNotificationBatches, pendingNotifications{
						actions:       nextStepActions,
						objectApiName: objApiName,
						targetID:      targetObjectId,
						actorIDs:      nextActorIDs,
					})
				}
			}
		}

		return nil
	})

	if txErr != nil {
		return nil, txErr
	}

	// Determine actual instance status for the response
	actualStatus := newStatus
	if advancedToNext || stepStillPending {
		actualStatus = constants.ApprovalStatusPending
	}

	// Post-commit: notifications (Alerts) run best-effort. Terminal-state
	// (FinalApproval / FinalRejection) and next-step (StepApproval) mutations
	// are already dispatched inside the transaction above; only Alerts remain
	// here.
	var actionFailures []ProcessActionFailure
	for _, batch := range pendingNotificationBatches {
		if fs := s.executeProcessActionNotifications(ctx, batch.actions, batch.objectApiName, batch.targetID, batch.actorIDs, user); len(fs) > 0 {
			actionFailures = append(actionFailures, fs...)
		}
	}

	responseObj := models.SObject{
		"ProcessInstanceId": instanceId,
		"TargetObjectId":    targetObjectId,
		"InstanceStatus":    actualStatus,
		"ActorIds":          nextActorIDs,
		"NewWorkitemIds":    newWorkItemIDs,
	}
	if len(actionFailures) > 0 {
		responseObj["ActionFailures"] = actionFailures
	}
	if len(newWorkItemIDs) > 0 {
		responseObj[constants.FieldID] = newWorkItemIDs[0]
	} else {
		responseObj[constants.FieldID] = workItemID
	}
	if len(nextActorIDs) > 0 {
		responseObj["ActorId"] = nextActorIDs[0]
	} else {
		responseObj["ActorId"] = user.ID
	}

	return responseObj, nil
}

// tryAdvanceToNextStep finds the next step from ProcessNode order and starts it.
// Returns (true, actorIDs, workItemIDs, nil) if advanced, (false, nil, nil, nil) otherwise.
func (s *ApprovalService) tryAdvanceToNextStep(ctx context.Context, processDef models.SObject, instanceId string, currentNode models.SObject, targetObjectId, submittedByID, now string, user *models.UserSession) (bool, []string, []string, error) {
	if processDef == nil || currentNode == nil {
		return false, nil, nil, nil
	}
	processDefID, _ := processDef[constants.FieldID].(string)

	// Fetch all ProcessNodes ordered by StepOrder
	nodeFilter := fmt.Sprintf("ProcessDefinitionId == '%s'", processDefID)
	nodeResult, err := s.query.QueryWithFilter(ctx, "ProcessNode", nodeFilter, user, "StepOrder", "ASC", 100)
	if err != nil || nodeResult.TotalSize < 2 {
		return false, nil, nil, nil
	}

	// Find current step index
	currentProcessNodeID, _ := currentNode[constants.FieldID].(string)
	currentIdx := -1
	for i, node := range nodeResult.Records {
		nodeID, _ := node[constants.FieldID].(string)
		if nodeID != "" && nodeID == currentProcessNodeID {
			currentIdx = i
			break
		}
	}

	if currentIdx < 0 || currentIdx >= len(nodeResult.Records)-1 {
		return false, nil, nil, nil
	}

	objectName, _ := processDef["TableEnumOrId"].(string)

	// Walk downstream steps in order. For each candidate:
	//   - If step criteria match, start it.
	//   - If criteria do NOT match, consult ifCriteriaNotMet:
	//       "GotoNextStep"          → skip forward to the next candidate.
	//       "ApproveRecord" or ""   → finalize the process as Approved.
	// Running off the end of the chain also finalizes as Approved.
	// Salesforce default (field absent) is ApproveRecord.
	for idx := currentIdx + 1; idx < len(nodeResult.Records); idx++ {
		nextNode := nodeResult.Records[idx]

		if s.stepCriteriaMatches(ctx, processDef, nextNode, objectName, targetObjectId, user) {
			nextNodeId, _ := nextNode[constants.FieldID].(string)
			nextNodeName, _ := nextNode["Name"].(string)

			nextActorIDs, resolveErr := s.resolveApproverIDsForNodeWithContext(ctx, processDef, nextNode, targetObjectId, user, approvalApproverResolutionContext{
				submitterID:     submittedByID,
				previousActorID: user.ID,
			})
			if resolveErr != nil {
				return false, nil, nil, resolveErr
			}
			if len(nextActorIDs) == 0 {
				nextActorIDs = []string{user.ID}
			}

			advanced, actorIDs, workItemIDs := s.createNextStepRecords(ctx, instanceId, nextNodeId, nextNodeName, nextActorIDs, now, user)
			return advanced, actorIDs, workItemIDs, nil
		}

		behavior := s.ifCriteriaNotMetBehaviorForNode(processDef, nextNode)
		nextNodeName, _ := nextNode["Name"].(string)
		s.logger.Info(fmt.Sprintf("stepCriteria: step %q (idx=%d) did not match; ifCriteriaNotMet=%s; instance=%s", nextNodeName, idx, behavior, instanceId))
		if behavior == "GotoNextStep" {
			continue
		}
		return false, nil, nil, nil
	}

	return false, nil, nil, nil
}

// tryRejectBackToPrevious checks whether rejection should roll back to a previous step.
// Returns (true, actorIDs, workItemIDs, nil) if rolled back, (false, nil, nil, nil) otherwise.
func (s *ApprovalService) tryRejectBackToPrevious(ctx context.Context, processDef models.SObject, instanceId string, currentNode models.SObject, targetObjectId, submittedByID, now string, user *models.UserSession) (bool, []string, []string, error) {
	if processDef == nil || currentNode == nil {
		return false, nil, nil, nil
	}
	processDefID, _ := processDef[constants.FieldID].(string)

	// Fetch all ProcessNodes ordered by StepOrder
	nodeFilter := fmt.Sprintf("ProcessDefinitionId == '%s'", processDefID)
	nodeResult, err := s.query.QueryWithFilter(ctx, "ProcessNode", nodeFilter, user, "StepOrder", "ASC", 100)
	if err != nil || nodeResult.TotalSize == 0 {
		return false, nil, nil, nil
	}

	// Find the current step
	currentProcessNodeID, _ := currentNode[constants.FieldID].(string)
	currentIdx := -1
	for i, node := range nodeResult.Records {
		nodeID, _ := node[constants.FieldID].(string)
		if nodeID != "" && nodeID == currentProcessNodeID {
			currentIdx = i
			break
		}
	}

	if currentIdx < 0 {
		return false, nil, nil, nil
	}

	// Check RejectBehavior on the current node
	rejectBehavior, _ := nodeResult.Records[currentIdx]["RejectBehavior"].(string)
	if metadataStep := s.metadataStepForNode(processDef, nodeResult.Records[currentIdx]); metadataStep != nil && metadataStep.RejectBehavior != "" {
		rejectBehavior = metadataStep.RejectBehavior
	}
	switch strings.ToLower(rejectBehavior) {
	case "backtosubmitter", "backtoprevious":
		// Roll back to the previous step (or first step if at step 1)
		prevIdx := currentIdx - 1
		if prevIdx < 0 {
			prevIdx = 0
		}
		prevNode := nodeResult.Records[prevIdx]
		prevNodeId, _ := prevNode[constants.FieldID].(string)
		prevNodeName, _ := prevNode["Name"].(string)

		previousActorID, historyErr := s.previousActorIDForStepIndex(ctx, instanceId, nodeResult.Records, prevIdx, user)
		if historyErr != nil {
			return false, nil, nil, historyErr
		}
		prevActorIDs, resolveErr := s.resolveApproverIDsForNodeWithContext(ctx, processDef, prevNode, targetObjectId, user, approvalApproverResolutionContext{
			submitterID:     submittedByID,
			previousActorID: previousActorID,
		})
		if resolveErr != nil {
			return false, nil, nil, resolveErr
		}
		if len(prevActorIDs) == 0 {
			prevActorIDs = []string{user.ID}
		}

		advanced, actorIDs, workItemIDs := s.createNextStepRecords(ctx, instanceId, prevNodeId, prevNodeName, prevActorIDs, now, user)
		return advanced, actorIDs, workItemIDs, nil
	case "rejectrequest":
		// "RejectRequest" — finalize as rejected (same as FinalRejection)
		return false, nil, nil, nil
	default:
		// "FinalRejection" or empty — finalize as rejected
		return false, nil, nil, nil
	}
}

// resolveApproverFromNode resolves an approver User ID from ProcessNode fields.
// Handles ApproverType "User" (direct lookup) and "relatedUserField" (field on target record).
func (s *ApprovalService) resolveApproverFromNode(ctx context.Context, node models.SObject, targetRecordId, objectName string, user *models.UserSession) string {
	approverType, _ := node["ApproverType"].(string)
	approverUserId, _ := node["ApproverUserId"].(string)

	if approverUserId == "" {
		return ""
	}

	// For "relateduser" type, we can't support multiple users from a single field reference
	// So we just return the first one
	switch strings.ToLower(approverType) {
	case "user":
		// Split comma-separated values to support multiple approvers
		approverValues := strings.Split(approverUserId, ",")
		for _, val := range approverValues {
			trimmedVal := strings.TrimSpace(val)
			if trimmedVal == "" {
				continue
			}

			// ApproverUserId could be a User ID or a username/email
			// First try as direct ID
			safeId := strings.ReplaceAll(trimmedVal, "'", "''")
			userFilter := fmt.Sprintf("Id == '%s'", safeId)
			userResult, err := s.query.QueryWithFilter(ctx, "User", userFilter, user, "", "", 1)
			if err == nil && len(userResult.Records) > 0 {
				uid, _ := userResult.Records[0][constants.FieldID].(string)
				return uid
			}
			// Try as Username/Email
			userFilter = fmt.Sprintf("Username == '%s'", safeId)
			userResult, err = s.query.QueryWithFilter(ctx, "User", userFilter, user, "", "", 1)
			if err == nil && len(userResult.Records) > 0 {
				uid, _ := userResult.Records[0][constants.FieldID].(string)
				return uid
			}
		}
		return ""

	case "relateduser", "relateduserfield":
		// The field name may be in ApproverRelatedField (new deploy) or ApproverUserId (legacy SF sync)
		approverRelatedField, _ := node["ApproverRelatedField"].(string)
		fieldName := approverRelatedField
		if fieldName == "" {
			fieldName = approverUserId // fallback for legacy data
		}
		if fieldName == "" || targetRecordId == "" || objectName == "" {
			return ""
		}
		recFilter := fmt.Sprintf("Id == '%s'", strings.ReplaceAll(targetRecordId, "'", "''"))
		recResult, err := s.query.QueryWithFilter(ctx, objectName, recFilter, user, "", "", 1)
		if err != nil || len(recResult.Records) == 0 {
			return ""
		}
		relatedUserId, _ := recResult.Records[0][fieldName].(string)
		return relatedUserId

	default:
		return ""
	}
}

// resolveApproverIDsFromNode resolves multiple approver User IDs from ProcessNode fields.
// Supports comma-separated values in the ApproverUserId field for multiple approvers.
func (s *ApprovalService) resolveApproverIDsFromNode(ctx context.Context, node models.SObject, targetRecordId, objectName string, user *models.UserSession) []string {
	approverType, _ := node["ApproverType"].(string)
	approverUserId, _ := node["ApproverUserId"].(string)

	if approverUserId == "" {
		return nil
	}

	switch strings.ToLower(approverType) {
	case "user":
		// Split comma-separated values to support multiple approvers
		approverValues := strings.Split(approverUserId, ",")
		var approverIDs []string
		seen := make(map[string]struct{})

		for _, val := range approverValues {
			trimmedVal := strings.TrimSpace(val)
			if trimmedVal == "" {
				continue
			}

			// ApproverUserId could be a User ID or a username/email
			// First try as direct ID
			safeId := strings.ReplaceAll(trimmedVal, "'", "''")
			userFilter := fmt.Sprintf("Id == '%s'", safeId)
			userResult, err := s.query.QueryWithFilter(ctx, "User", userFilter, user, "", "", 1)
			if err == nil && len(userResult.Records) > 0 {
				uid, _ := userResult.Records[0][constants.FieldID].(string)
				if uid != "" {
					if _, exists := seen[uid]; exists {
						continue
					}
					seen[uid] = struct{}{}
					approverIDs = append(approverIDs, uid)
				}
				continue
			}
			// Try as Username/Email
			userFilter = fmt.Sprintf("Username == '%s'", safeId)
			userResult, err = s.query.QueryWithFilter(ctx, "User", userFilter, user, "", "", 1)
			if err == nil && len(userResult.Records) > 0 {
				uid, _ := userResult.Records[0][constants.FieldID].(string)
				if uid != "" {
					if _, exists := seen[uid]; exists {
						continue
					}
					seen[uid] = struct{}{}
					approverIDs = append(approverIDs, uid)
				}
			}
		}
		return approverIDs

	case "relateduser", "relateduserfield":
		// For related user fields, we still only support single user
		// The field name may be in ApproverRelatedField (new deploy) or ApproverUserId (legacy SF sync)
		approverRelatedField, _ := node["ApproverRelatedField"].(string)
		fieldName := approverRelatedField
		if fieldName == "" {
			fieldName = approverUserId // fallback for legacy data
		}
		if fieldName == "" || targetRecordId == "" || objectName == "" {
			return nil
		}
		recFilter := fmt.Sprintf("Id == '%s'", strings.ReplaceAll(targetRecordId, "'", "''"))
		recResult, err := s.query.QueryWithFilter(ctx, objectName, recFilter, user, "", "", 1)
		if err != nil || len(recResult.Records) == 0 {
			return nil
		}
		relatedUserId, _ := recResult.Records[0][fieldName].(string)
		if relatedUserId != "" {
			return []string{relatedUserId}
		}
		return nil

	default:
		return nil
	}
}

// createNextStepRecords creates ProcessInstanceNode and one or more ProcessInstanceWorkitems.
func (s *ApprovalService) createNextStepRecords(ctx context.Context, instanceId, nextNodeId, nextNodeName string, nextActorIDs []string, now string, user *models.UserSession) (bool, []string, []string) {
	nodeInstanceRecord := models.SObject{
		"ProcessInstanceId": instanceId,
		"ProcessNodeId":     nextNodeId,
		"ProcessNodeName":   nextNodeName,
		"NodeStatus":        constants.ApprovalStatusPending,
		"CreatedDate":       now,
	}
	if _, insertErr := s.persistence.Insert(processTableWriteContext(ctx, "ApprovalService: create next-step ProcessInstanceNode"), constants.TableProcessInstanceNode, nodeInstanceRecord, user); insertErr != nil {
		s.logger.Error("Failed to create ProcessInstanceNode for next step", insertErr)
	}

	workItemIDs, insertErr := s.createWorkitems(ctx, instanceId, nextActorIDs, "", now, user)
	if insertErr != nil {
		s.logger.Error("Failed to create ProcessInstanceWorkitem for next step", insertErr)
		return false, nil, nil
	}

	return true, nextActorIDs, workItemIDs
}

// Recall allows the submitter to withdraw a pending approval.
// 1. Finds the ProcessInstanceWorkitem by ID
// 2. Gets the ProcessInstance
// 3. Verifies the current user is the submitter
// 4. Updates ProcessInstance status to Removed
// 5. Deletes the pending WorkItem
func (s *ApprovalService) Recall(ctx context.Context, workItemID, comments string, user *models.UserSession) (models.SObject, error) {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: managing approval system tables")

	// Step 1: Find the work item
	workItemFilter := fmt.Sprintf("Id == '%s'", workItemID)
	wiResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessInstanceWorkitem, workItemFilter, user, "", "", 1)
	if err != nil || wiResult.TotalSize == 0 || len(wiResult.Records) == 0 {
		return nil, ErrWorkItemNotFound
	}
	workItem := wiResult.Records[0]
	instanceId, _ := workItem["ProcessInstanceId"].(string)
	if instanceId == "" {
		return nil, ErrWorkItemNotFound
	}

	now := time.Now().UTC().Format(time.RFC3339)
	var targetObjectId string
	var recallProcessDefId string
	var recallActorIDs []string
	var recallNotifications *pendingNotifications

	// Steps 2-7 wrapped in a transaction for atomicity (prevents TOCTOU race
	// between concurrent Recall and Approve, and ensures no partial state).
	// The status and submitter checks MUST be inside the transaction to avoid
	// a time-of-check-time-of-use race where a concurrent Approve could change
	// the status between the check and the update.
	txErr := s.persistence.RunInTransaction(ctx, func(txCtx context.Context) error {
		// Reset closure-scoped state so deadlock-retry attempts are clean.
		targetObjectId = ""
		recallProcessDefId = ""
		recallActorIDs = nil
		recallNotifications = nil

		// Step 2: Get the ProcessInstance (inside transaction)
		instanceFilter := fmt.Sprintf("Id == '%s'", instanceId)
		instResult, err := s.query.QueryWithFilter(txCtx, constants.TableProcessInstance, instanceFilter, user, "", "", 1)
		if err != nil || instResult.TotalSize == 0 || len(instResult.Records) == 0 {
			return apperrors.NewInternalError("failed to look up ProcessInstance "+instanceId, err)
		}
		instance := instResult.Records[0]
		recallProcessDefId, _ = instance["ProcessDefinitionId"].(string)

		// Step 3: Verify the instance is still Pending (inside transaction)
		status, _ := instance["Status"].(string)
		if status != constants.ApprovalStatusPending {
			return ErrInstanceNotPending
		}

		// Step 4: Verify the current user is the submitter (inside transaction)
		submittedById, _ := instance["SubmittedById"].(string)
		if submittedById != user.ID {
			return ErrNotSubmitter
		}

		targetObjectId, _ = instance["TargetObjectId"].(string)

		// Step 5: Update ProcessInstance status to Removed
		instanceUpdates := models.SObject{
			"Status":        constants.ApprovalStatusRemoved,
			"CompletedDate": now,
		}
		if updateErr := s.persistence.Update(processTableWriteContext(txCtx, "ApprovalService: recall ProcessInstance"), constants.TableProcessInstance, instanceId, instanceUpdates, user); updateErr != nil {
			return fmt.Errorf("failed to recall approval instance: %w", updateErr)
		}

		// Step 6: Update any pending ProcessInstanceNode to Removed
		nodeFilter := fmt.Sprintf("ProcessInstanceId == '%s' && NodeStatus == '%s'", instanceId, constants.ApprovalStatusPending)
		nodeResult, lookupErr := s.query.QueryWithFilter(txCtx, constants.TableProcessInstanceNode, nodeFilter, user, "", "", 1)
		if lookupErr == nil && len(nodeResult.Records) > 0 {
			nodeId, _ := nodeResult.Records[0][constants.FieldID].(string)
			if nodeId != "" {
				nodeUpdates := models.SObject{
					"NodeStatus":    constants.ApprovalStatusRemoved,
					"CompletedDate": now,
					"LastActorId":   user.ID,
				}
				if updateErr := s.persistence.Update(processTableWriteContext(txCtx, "ApprovalService: recall ProcessInstanceNode"), constants.TableProcessInstanceNode, nodeId, nodeUpdates, user); updateErr != nil {
					s.logger.Error("Failed to update ProcessInstanceNode "+nodeId, updateErr)
				}
			}
		}

		// Step 7: Delete the pending work item
		workItemsFilter := fmt.Sprintf("ProcessInstanceId == '%s'", instanceId)
		workItemsResult, lookupErr := s.query.QueryWithFilter(txCtx, constants.TableProcessInstanceWorkitem, workItemsFilter, user, "", "", 100)
		if lookupErr == nil {
			seenActorIDs := make(map[string]struct{})
			for _, pendingItem := range workItemsResult.Records {
				pendingItemID, _ := pendingItem[constants.FieldID].(string)
				if pendingItemID == "" {
					continue
				}
				actorID, _ := pendingItem["ActorId"].(string)
				if actorID != "" {
					if _, exists := seenActorIDs[actorID]; !exists {
						seenActorIDs[actorID] = struct{}{}
						recallActorIDs = append(recallActorIDs, actorID)
					}
				}
				if delErr := s.persistence.Delete(processTableWriteContext(txCtx, "ApprovalService: recall ProcessInstanceWorkitem"), constants.TableProcessInstanceWorkitem, pendingItemID, user); delErr != nil {
					s.logger.Error("Failed to delete ProcessInstanceWorkitem "+pendingItemID, delErr)
				}
			}
		} else if delErr := s.persistence.Delete(processTableWriteContext(txCtx, "ApprovalService: recall ProcessInstanceWorkitem"), constants.TableProcessInstanceWorkitem, workItemID, user); delErr != nil {
			s.logger.Error("Failed to delete ProcessInstanceWorkitem "+workItemID, delErr)
		}

		// Step 8: Execute Recall ProcessAction FieldUpdates inside the tx so a
		// failed linked-record update rolls back the recall. Alerts are stashed
		// for post-commit best-effort delivery.
		if recallProcessDefId != "" && targetObjectId != "" {
			objApiName, resolveErr := s.resolveProcessObjectAPIName(txCtx, recallProcessDefId, "", user)
			if resolveErr == nil && objApiName != "" {
				actions, fetchErr := s.fetchProcessActions(txCtx, recallProcessDefId, "", "Recall", user)
				if fetchErr != nil {
					s.logger.Warn(fmt.Sprintf("Failed to query Recall ProcessActions for processDefId=%s: %v", recallProcessDefId, fetchErr))
				} else if len(actions) > 0 {
					if err := s.executeProcessActionMutations(txCtx, actions, objApiName, targetObjectId, user); err != nil {
						return err
					}
					recallNotifications = &pendingNotifications{
						actions:       actions,
						objectApiName: objApiName,
						targetID:      targetObjectId,
						actorIDs:      recallActorIDs,
					}
				}
			}
		}

		return nil
	})

	if txErr != nil {
		return nil, txErr
	}

	// Post-commit: Alerts run best-effort.
	var recallFailures []ProcessActionFailure
	if recallNotifications != nil {
		recallFailures = s.executeProcessActionNotifications(ctx, recallNotifications.actions, recallNotifications.objectApiName, recallNotifications.targetID, recallNotifications.actorIDs, user)
	}

	responseObj := models.SObject{
		constants.FieldID:   workItemID,
		"ProcessInstanceId": instanceId,
		"TargetObjectId":    targetObjectId,
		"ActorId":           user.ID,
	}
	if len(recallFailures) > 0 {
		responseObj["ActionFailures"] = recallFailures
	}

	return responseObj, nil
}

// ProcessActionFailure is a structured record of a ProcessAction that failed
// during best-effort notification execution (Alert, unknown action types).
// FieldUpdate failures are not surfaced here — they abort the enclosing
// approval transaction so the approval state and the linked-record update
// cannot commit in a split-brain. Callers attach these to the parent
// response payload so the UI can surface a warning banner.
type ProcessActionFailure struct {
	ActionName   string `json:"actionName"`
	ActionType   string `json:"actionType"`
	TargetObject string `json:"targetObject,omitempty"`
	TargetID     string `json:"targetId,omitempty"`
	Field        string `json:"field,omitempty"`
	Reason       string `json:"reason"`
}

// ApprovalActionError is returned from executeProcessActionMutations when a
// FieldUpdate action fails inside the approval transaction. Callers type-assert
// to distinguish a linked-record update failure from an infrastructure error.
type ApprovalActionError struct {
	ActionName   string
	ActionType   string
	TargetObject string
	TargetID     string
	Field        string
	Reason       string
}

func (e *ApprovalActionError) Error() string {
	return fmt.Sprintf("approval action %q (%s) failed on %s.%s id=%s: %s",
		e.ActionName, e.ActionType, e.TargetObject, e.Field, e.TargetID, e.Reason)
}

// pendingNotifications carries ProcessAction records whose FieldUpdate
// mutations were already applied inside a transaction and whose Alert
// notifications still need to run best-effort after the tx commits.
type pendingNotifications struct {
	actions       []models.SObject
	objectApiName string
	targetID      string
	actorIDs      []string
}

// fetchProcessActions queries ProcessAction records for a given lifecycle
// event and filters by ProcessNodeId scoping: when processNodeId is set, only
// actions attached to that node are returned; otherwise only process-level
// actions (no ProcessNodeId) are returned.
func (s *ApprovalService) fetchProcessActions(ctx context.Context, processDefId, processNodeId, actionEvent string, user *models.UserSession) ([]models.SObject, error) {
	if processDefId == "" {
		return nil, nil
	}
	filter := fmt.Sprintf("ProcessDefinitionId == '%s' && ActionEvent == '%s'", processDefId, actionEvent)
	result, err := s.query.QueryWithFilter(ctx, "ProcessAction", filter, user, "Order", "ASC", 100)
	if err != nil {
		return nil, err
	}
	if result == nil || result.TotalSize == 0 {
		return nil, nil
	}
	matched := make([]models.SObject, 0, len(result.Records))
	for _, action := range result.Records {
		actionProcessNodeID, _ := action["ProcessNodeId"].(string)
		if processNodeId != "" {
			if actionProcessNodeID != processNodeId {
				continue
			}
		} else if actionProcessNodeID != "" {
			continue
		}
		matched = append(matched, action)
	}
	return matched, nil
}

// executeProcessActionMutations runs FieldUpdate actions from the given
// ProcessAction slice. It MUST be called with a transaction context — a
// failure returns *ApprovalActionError and aborts the caller's transaction so
// the approval state and the linked-record update cannot commit in a
// split-brain. Non-FieldUpdate actions are ignored here.
func (s *ApprovalService) executeProcessActionMutations(ctx context.Context, actions []models.SObject, objectApiName, targetObjectId string, user *models.UserSession) error {
	for _, action := range actions {
		actionType, _ := action["ActionType"].(string)
		if actionType != "FieldUpdate" {
			continue
		}
		actionName, _ := action["Name"].(string)
		fieldApiName, _ := action["FieldApiName"].(string)
		fieldValue, _ := action["FieldValue"].(string)
		if fieldApiName == "" || objectApiName == "" {
			reason := fmt.Sprintf("missing fieldApiName or objectApiName (fieldApiName=%q objectApiName=%q)", fieldApiName, objectApiName)
			s.logger.Error(fmt.Sprintf("APPROVAL_ACTION_FAILURE ProcessAction FieldUpdate '%s' skipped: %s", actionName, reason), nil)
			return &ApprovalActionError{ActionName: actionName, ActionType: actionType, Reason: reason}
		}
		updateObjectName, updateRecordID, updateFieldAPIName, resolveErr := s.resolveFieldUpdateTarget(ctx, objectApiName, targetObjectId, fieldApiName)
		if resolveErr != nil {
			s.logger.Error(fmt.Sprintf("APPROVAL_ACTION_FAILURE ProcessAction FieldUpdate '%s' could not resolve target %q on %s/%s", actionName, fieldApiName, objectApiName, targetObjectId), resolveErr)
			return &ApprovalActionError{ActionName: actionName, ActionType: actionType, Field: fieldApiName, Reason: resolveErr.Error()}
		}
		// TODO: migrate away from Salesforce-specific "Literal: " prefix in
		// FieldValue. Once all ProcessAction records use plain values, remove
		// this TrimPrefix and the Salesforce FieldUpdate value-type grammar.
		fieldValue = strings.TrimPrefix(fieldValue, "Literal: ")
		updates := models.SObject{
			updateFieldAPIName: fieldValue,
		}
		s.logger.Info(fmt.Sprintf("ProcessAction FieldUpdate '%s': setting %s.%s = %q on %s", actionName, updateObjectName, updateFieldAPIName, fieldValue, updateRecordID))
		if updateErr := s.persistence.Update(ctx, updateObjectName, updateRecordID, updates, user); updateErr != nil {
			s.logger.Error(fmt.Sprintf("APPROVAL_ACTION_FAILURE ProcessAction FieldUpdate '%s' failed for %s.%s id=%s", actionName, updateObjectName, updateFieldAPIName, updateRecordID), updateErr)
			return &ApprovalActionError{
				ActionName:   actionName,
				ActionType:   actionType,
				TargetObject: updateObjectName,
				TargetID:     updateRecordID,
				Field:        updateFieldAPIName,
				Reason:       updateErr.Error(),
			}
		}
	}
	return nil
}

// executeProcessActionNotifications runs Alert and unknown-type actions from
// the given ProcessAction slice. Best-effort: failures are collected and
// returned so the caller can attach them to the response payload. Never
// called inside an approval transaction — Alerts should not block state.
func (s *ApprovalService) executeProcessActionNotifications(ctx context.Context, actions []models.SObject, objectApiName, targetObjectId string, actorIDs []string, user *models.UserSession) []ProcessActionFailure {
	if len(actions) == 0 {
		return nil
	}
	var failures []ProcessActionFailure
	var targetRecord models.SObject
	emailTargetLoaded := false
	for _, action := range actions {
		actionType, _ := action["ActionType"].(string)
		actionName, _ := action["Name"].(string)
		switch actionType {
		case "FieldUpdate":
			// Mutations ran inside the tx; nothing to do here.
			continue
		case "Alert":
			if !emailTargetLoaded {
				emailTargetLoaded = true
				if objectApiName != "" && targetObjectId != "" {
					record, loadErr := s.persistence.GetRaw(ctx, objectApiName, targetObjectId)
					if loadErr != nil {
						s.logger.Error(fmt.Sprintf("Failed to load %s %s for ProcessAction Alert", objectApiName, targetObjectId), loadErr)
					} else if record != nil {
						targetRecord = record
					}
				}
				if targetRecord == nil {
					targetRecord = models.SObject{constants.FieldID: targetObjectId}
				}
			}
			s.executeApprovalEmailAlert(ctx, action, objectApiName, targetRecord, actorIDs, user)
		default:
			reason := fmt.Sprintf("unknown ActionType %q", actionType)
			s.logger.Warn(fmt.Sprintf("ProcessAction '%s' skipped: %s", actionName, reason))
			failures = append(failures, ProcessActionFailure{ActionName: actionName, ActionType: actionType, Reason: reason})
		}
	}
	if len(failures) > 0 {
		s.logger.Error(fmt.Sprintf("APPROVAL_NOTIFICATION_FAILURE_SUMMARY objectApiName=%s targetId=%s failures=%d", objectApiName, targetObjectId, len(failures)), nil)
	}
	return failures
}

// resolveProcessObjectAPIName returns the API name (e.g. "Order") of the
// object whose records this process operates on. If the caller already has
// the value cached, we short-circuit; otherwise we fetch ProcessDefinition
// to read TableEnumOrId. Returns ("", nil) if the lookup yields nothing —
// callers should treat that as "skip actions" rather than an error.
func (s *ApprovalService) resolveProcessObjectAPIName(ctx context.Context, processDefId, cached string, user *models.UserSession) (string, error) {
	if cached != "" {
		return cached, nil
	}
	if processDefId == "" {
		return "", nil
	}
	pdFilter := fmt.Sprintf("Id == '%s'", processDefId)
	pdResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessDefinition, pdFilter, user, "", "", 1)
	if err != nil {
		return "", err
	}
	if pdResult == nil || len(pdResult.Records) == 0 {
		return "", nil
	}
	apiName, _ := pdResult.Records[0]["TableEnumOrId"].(string)
	return apiName, nil
}

func (s *ApprovalService) resolveFieldUpdateTarget(ctx context.Context, objectApiName, targetObjectId, fieldApiName string) (string, string, string, error) {
	fieldPath := strings.Split(fieldApiName, ".")
	if len(fieldPath) == 1 {
		if targetObjectId == "" {
			return "", "", "", fmt.Errorf("missing target object id")
		}
		return objectApiName, targetObjectId, fieldApiName, nil
	}

	currentObjectName := objectApiName
	currentRecordID := targetObjectId

	for _, lookupField := range fieldPath[:len(fieldPath)-1] {
		if currentObjectName == "" || currentRecordID == "" {
			return "", "", "", fmt.Errorf("missing target for lookup path %q", fieldApiName)
		}

		record, err := s.persistence.GetRaw(ctx, currentObjectName, currentRecordID)
		if err != nil {
			return "", "", "", fmt.Errorf("failed to load %s %s for lookup path %q: %w", currentObjectName, currentRecordID, fieldApiName, err)
		}
		if record == nil {
			return "", "", "", fmt.Errorf("%s %s not found for lookup path %q", currentObjectName, currentRecordID, fieldApiName)
		}

		nextRecordID, _ := record[lookupField].(string)
		if strings.TrimSpace(nextRecordID) == "" {
			return "", "", "", fmt.Errorf("lookup field %q is empty on %s %s", lookupField, currentObjectName, currentRecordID)
		}

		nextObjectName, resolveErr := s.resolveLookupTargetObject(ctx, currentObjectName, lookupField)
		if resolveErr != nil {
			return "", "", "", resolveErr
		}

		currentObjectName = nextObjectName
		currentRecordID = nextRecordID
	}

	return currentObjectName, currentRecordID, fieldPath[len(fieldPath)-1], nil
}

// resolveLookupTargetObject returns the object API name that a lookup field
// references, reading `referenceTo` from the object's metadata. Polymorphic
// fields (len(referenceTo) > 1) are not supported for FieldUpdate targets —
// the caller must specify a concrete path. Requires a schema resolver; tests
// must supply a stub rather than relying on a <Name>Id heuristic, which
// silently mis-resolved names like ParentId/WhoId on unfamiliar objects.
func (s *ApprovalService) resolveLookupTargetObject(ctx context.Context, currentObjectName, lookupField string) (string, error) {
	if s.schema == nil {
		return "", fmt.Errorf("approval service misconfigured: schema resolver is nil (cannot resolve lookup %q on %s)", lookupField, currentObjectName)
	}
	schema := s.schema.GetSchema(ctx, currentObjectName)
	if schema == nil {
		return "", fmt.Errorf("schema for %s not found; cannot resolve lookup %q", currentObjectName, lookupField)
	}
	fm, ok := schema.FieldsMap[lookupField]
	if !ok || fm == nil {
		return "", fmt.Errorf("field %q not found on %s", lookupField, currentObjectName)
	}
	switch {
	case len(fm.ReferenceTo) == 1:
		return fm.ReferenceTo[0], nil
	case len(fm.ReferenceTo) > 1:
		return "", fmt.Errorf("polymorphic lookup %q on %s is not supported in FieldUpdate (referenceTo=%v)", lookupField, currentObjectName, fm.ReferenceTo)
	default:
		return "", fmt.Errorf("field %q on %s has no referenceTo — not a lookup", lookupField, currentObjectName)
	}
}

func (s *ApprovalService) executeApprovalEmailAlert(ctx context.Context, action models.SObject, objectApiName string, targetRecord models.SObject, actorIDs []string, user *models.UserSession) {
	actionName, _ := action["Name"].(string)
	if s.actions == nil {
		s.logger.Warn(fmt.Sprintf("ProcessAction Alert '%s' skipped: ActionService is not configured", actionName))
		return
	}

	apiName, _ := action[constants.FieldAPIName].(string)
	apiName = strings.TrimSpace(apiName)

	// Route: WorkflowAlert path (has ApiName) vs legacy path (inline EmailTemplateId + actorIDs).
	if apiName != "" {
		s.logger.Info(fmt.Sprintf("ProcessAction Alert '%s': routing to WorkflowAlert path (apiName=%s)", actionName, apiName))
		s.executeWorkflowAlert(ctx, apiName, actionName, objectApiName, targetRecord, actorIDs, user)
		return
	}

	s.logger.Info(fmt.Sprintf("ProcessAction Alert '%s': routing to legacy path (no ApiName)", actionName))

	// Legacy path: use EmailTemplateId + actorIDs directly.
	templateID, _ := action["EmailTemplateId"].(string)
	if strings.TrimSpace(templateID) == "" {
		s.logger.Warn(fmt.Sprintf("ProcessAction Alert '%s' skipped: EmailTemplateId is required", actionName))
		return
	}

	if len(actorIDs) == 0 {
		s.logger.Warn(fmt.Sprintf("ProcessAction Alert '%s' skipped: no recipient actors resolved (actorIDs is nil/empty)", actionName))
		return
	}

	seenActorIDs := make(map[string]struct{})
	for _, actorID := range actorIDs {
		actorID = strings.TrimSpace(actorID)
		if actorID == "" {
			continue
		}
		if _, exists := seenActorIDs[actorID]; exists {
			continue
		}
		seenActorIDs[actorID] = struct{}{}

		actionMeta := &models.ActionMetadata{
			ID:            fmt.Sprintf("approval-email-%s-%s", actionName, actorID),
			Name:          actionName,
			Type:          constants.ActionTypeSendEmail,
			ObjectAPIName: objectApiName,
			Config: map[string]interface{}{
				constants.ConfigEmailTemplate: templateID,
				constants.ConfigRecipient:     actorID,
			},
		}
		actionCtx := &ActionContext{
			Record:  targetRecord,
			User:    user,
			Results: make(map[string]map[string]interface{}),
		}

		if err := s.actions.executeActionFromMetadata(ctx, actionMeta, actionCtx); err != nil {
			s.logger.Error(fmt.Sprintf("ProcessAction Alert '%s' failed for actor %s", actionName, actorID), err)
		}
	}
}

// executeWorkflowAlert looks up a _System_WorkflowAlert by name, resolves recipients,
// sender, and template, then sends individual emails.
func (s *ApprovalService) executeWorkflowAlert(ctx context.Context, apiName, actionName, objectApiName string, targetRecord models.SObject, actorIDs []string, user *models.UserSession) {
	// Look up the WorkflowAlert by Name
	filter := fmt.Sprintf("Name = '%s'", strings.ReplaceAll(apiName, "'", "''"))
	result, err := s.query.QueryWithFilter(ctx, constants.TableWorkflowAlert, filter, user, "", "", 1)
	if err != nil || result == nil || len(result.Records) == 0 {
		s.logger.Warn(fmt.Sprintf("ProcessAction Alert '%s': WorkflowAlert '%s' not found, skipping", actionName, apiName))
		return
	}
	s.sendWorkflowAlertEmails(ctx, result.Records[0], "ProcessAction Alert", actionName, apiName, objectApiName, targetRecord, user)
}

// DispatchEmailAlertForFlow is the Flow engine entry point for emailAlert actionCalls.
// It scopes the WorkflowAlert lookup by (Name, ObjectApiName) so that alerts with the
// same short name on different objects don't collide, then dispatches via the shared
// send pipeline used by ApprovalService.
//
// Errors are returned only for data-integrity failures (alert not found). Send-time
// errors are logged but swallowed, matching the "fire-and-forget" semantics of
// emailAlert in Salesforce — a failed recipient should not abort the flow or fault
// every record touched.
func (s *ApprovalService) DispatchEmailAlertForFlow(ctx context.Context, objectApiName, alertName string, targetRecord models.SObject, user *models.UserSession) error {
	alertName = strings.TrimSpace(alertName)
	objectApiName = strings.TrimSpace(objectApiName)
	if alertName == "" {
		return apperrors.NewValidationError("alertName", "emailAlert actionName is empty")
	}
	if objectApiName == "" {
		// Querying by Name alone could match an alert on a different object
		// (same short name → silent cross-object misfire, leaking data).
		// Salesforce always qualifies emailAlert references with the object
		// prefix; refuse to dispatch unqualified references rather than guess.
		return apperrors.NewValidationError("objectApiName",
			fmt.Sprintf("emailAlert '%s' must be qualified with an ObjectApiName (format 'ObjectApiName.AlertName') or the caller must supply Flow.Start.Object", alertName))
	}

	escName := strings.ReplaceAll(alertName, "'", "''")
	escObj := strings.ReplaceAll(objectApiName, "'", "''")
	filter := fmt.Sprintf("Name = '%s' AND ObjectApiName = '%s'", escName, escObj)

	result, err := s.query.QueryWithFilter(ctx, constants.TableWorkflowAlert, filter, user, "", "", 1)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to query WorkflowAlert '%s.%s'", objectApiName, alertName), err)
	}
	if result == nil || len(result.Records) == 0 {
		s.logger.Warn(fmt.Sprintf("Flow emailAlert: WorkflowAlert '%s.%s' not found, skipping", objectApiName, alertName))
		return apperrors.NewNotFoundError("WorkflowAlert", fmt.Sprintf("%s.%s", objectApiName, alertName))
	}

	alert := result.Records[0]
	resolvedName, _ := alert[constants.FieldSysWorkflowAlert_Name].(string)
	if strings.TrimSpace(resolvedName) == "" {
		resolvedName = alertName
	}
	s.sendWorkflowAlertEmails(ctx, alert, "Flow emailAlert", alertName, resolvedName, objectApiName, targetRecord, user)
	return nil
}

// sendWorkflowAlertEmails renders the alert's template and sends one email per
// resolved recipient. Misconfiguration (no recipients, no template, template
// render failure) is logged and the call returns without propagating the error,
// matching Salesforce's fire-and-forget emailAlert semantics.
func (s *ApprovalService) sendWorkflowAlertEmails(ctx context.Context, alert models.SObject, logPrefix, actionName, apiName, objectApiName string, targetRecord models.SObject, user *models.UserSession) {
	// Parse recipients from WorkflowAlert
	recipientsJSON, _ := alert[constants.FieldSysWorkflowAlert_Recipients].(string)
	s.logger.Debug(fmt.Sprintf("%s '%s': resolving recipients from JSON: %s", logPrefix, actionName, recipientsJSON))
	recipientEmails := s.resolveAlertRecipients(ctx, recipientsJSON, targetRecord, user)
	if len(recipientEmails) == 0 {
		s.logger.Warn(fmt.Sprintf("%s '%s': no recipients resolved from WorkflowAlert '%s'", logPrefix, actionName, apiName))
		return
	}
	s.logger.Info(fmt.Sprintf("%s '%s': resolved %d recipients: %v", logPrefix, actionName, len(recipientEmails), recipientEmails))

	// Resolve template ID — prefer WorkflowAlert.Template, fall back to ProcessAction.EmailTemplateId
	templateRef, _ := alert[constants.FieldSysWorkflowAlert_Template].(string)
	templateRef = strings.TrimSpace(templateRef)
	if templateRef == "" {
		s.logger.Warn(fmt.Sprintf("%s '%s': WorkflowAlert '%s' has no template", logPrefix, actionName, apiName))
		return
	}

	// TODO: migrate away from Salesforce folder-path template references
	// (e.g. "unfiled$public/Quote_Approved"). Once WorkflowAlert.Template
	// stores direct template IDs, remove this folder-path resolution block.
	templateID := templateRef
	if strings.Contains(templateRef, "/") {
		devName := templateRef[strings.LastIndex(templateRef, "/")+1:]
		s.logger.Debug(fmt.Sprintf("%s '%s': resolving template folder-path %q -> DeveloperName %q", logPrefix, actionName, templateRef, devName))
		tmplFilter := fmt.Sprintf("DeveloperName = '%s'", strings.ReplaceAll(devName, "'", "''"))
		tmplResult, tmplErr := s.query.QueryWithFilter(ctx, constants.TableEmailTemplate, tmplFilter, user, "", "", 1)
		if tmplErr != nil || tmplResult == nil || len(tmplResult.Records) == 0 {
			s.logger.Warn(fmt.Sprintf("%s '%s': template '%s' not found by DeveloperName '%s'", logPrefix, actionName, templateRef, devName))
			return
		}
		if resolvedID, ok := tmplResult.Records[0][constants.FieldID].(string); ok && resolvedID != "" {
			templateID = resolvedID
			s.logger.Debug(fmt.Sprintf("%s '%s': resolved template %q -> ID %s", logPrefix, actionName, templateRef, templateID))
		}
	}

	// Load and render the template
	actionCtx := &ActionContext{
		Record:  targetRecord,
		User:    user,
		Results: make(map[string]map[string]interface{}),
	}
	subject, htmlBody, plainBody, renderErr := s.actions.LoadAndRenderTemplate(ctx, templateID, actionCtx, objectApiName)
	if renderErr != nil {
		s.logger.Error(fmt.Sprintf("%s '%s': failed to load/render template %s", logPrefix, actionName, templateID), renderErr)
		return
	}

	// Resolve sender
	senderType, _ := alert[constants.FieldSysWorkflowAlert_SenderType].(string)
	senderAddress, _ := alert[constants.FieldSysWorkflowAlert_SenderAddress].(string)
	fromAddr := s.resolveSenderAddress(senderType, senderAddress, user)
	s.logger.Debug(fmt.Sprintf("%s '%s': sender resolved: type=%s from=%s", logPrefix, actionName, senderType, fromAddr))

	// Parse CC emails
	ccEmails, _ := alert[constants.FieldSysWorkflowAlert_CcEmails].(string)
	var ccList []string
	if strings.TrimSpace(ccEmails) != "" {
		for _, cc := range strings.Split(ccEmails, ",") {
			if trimmed := strings.TrimSpace(cc); trimmed != "" {
				ccList = append(ccList, trimmed)
			}
		}
	}

	// Send individual emails to each recipient (matches Salesforce behavior)
	for _, email := range recipientEmails {
		msg := &ports.EmailMessage{
			To:      []string{email},
			Cc:      ccList,
			From:    fromAddr,
			Subject: subject,
			Body:    plainBody,
			HTML:    htmlBody,
		}
		if sendErr := s.actions.emailSender.Send(ctx, msg); sendErr != nil {
			s.logger.Error(fmt.Sprintf("%s '%s': failed to send to %s", logPrefix, actionName, email), sendErr)
		} else {
			s.logger.Info(fmt.Sprintf("%s '%s': email sent to=%s subject=%q", logPrefix, actionName, email, subject))
		}
	}
}

// resolveSenderAddress determines the From address based on SenderType.
func (s *ApprovalService) resolveSenderAddress(senderType, senderAddress string, user *models.UserSession) string {
	switch senderType {
	case "CurrentUser":
		if user != nil && user.Email != nil && *user.Email != "" {
			return *user.Email
		}
	case "OrgWideEmailAddress":
		if strings.TrimSpace(senderAddress) != "" {
			return senderAddress
		}
	}
	// DefaultWorkflowUser or fallback: return empty string → SMTP default is used
	return ""
}

// matchEntryCriteria checks whether a record meets the entry criteria.
// Returns (true, nil) if matched, (false, nil) if not matched, (false, err) on evaluation error.
//
// EntryFilter formats:
//   - Simple: "Field op Value AND Field op Value" (from CRM deploy)
//   - JSON criteriaItems: {"criteriaItems": [{"field":"Obj.Field","operation":"equals","value":"V1,V2"}]}
//   - JSON formula: {"formula": "..."} (SF native — cannot evaluate, treated as match)
func (s *ApprovalService) matchEntryCriteria(ctx context.Context, entryFilter, objectName, recordID string, user *models.UserSession) (bool, error) {
	trimmed := strings.TrimSpace(entryFilter)
	if trimmed == "" {
		return true, nil
	}

	var criteria []criterionItem
	var booleanFilter string

	if strings.HasPrefix(trimmed, "{") {
		// JSON format — parse with encoding/json
		parsed, formula, parsedBooleanFilter, err := parseJSONEntryCriteria(trimmed)
		if err != nil {
			return false, fmt.Errorf("invalid entry criteria format: %w", err)
		}

		// If it's a formula, evaluate it
		if formula != "" {
			return s.evaluateFormula(ctx, formula, objectName, recordID, user)
		}

		criteria = parsed
		booleanFilter = parsedBooleanFilter
	} else {
		// Simple text format: "Field op Value AND Field op Value"
		parsed, err := parseSimpleEntryCriteria(trimmed)
		if err != nil {
			s.logger.Warn("Could not parse entry criteria, treating as match: " + err.Error())
			return true, nil
		}
		criteria = parsed
	}

	if len(criteria) == 0 {
		return true, nil
	}

	// Fetch the target record
	recordFilter := fmt.Sprintf("Id == '%s'", recordID)
	recordResult, err := s.query.QueryWithFilter(ctx, objectName, recordFilter, user, "", "", 1)
	if err != nil || recordResult.TotalSize == 0 {
		s.logger.Error("Failed to retrieve record for entry criteria evaluation", err)
		return true, nil // Don't block if we can't read the record
	}
	if strings.TrimSpace(booleanFilter) != "" {
		return s.evaluateBooleanFilter(ctx, booleanFilter, criteria, objectName, recordResult.Records[0], user)
	}
	return s.evaluateCriteriaItems(ctx, criteria, objectName, recordResult.Records[0], user), nil
}

// evaluateCriteriaItems runs an AND-match of the given criteria against a
// preloaded record. Handles Salesforce's RecordType ID↔Name canonicalisation
// so callers can express "RecordType equals X" against the stored foreign-key
// column. Empty criteria is treated as match. `objectName` is the SObject the
// record was loaded from; it lets same-object and cross-object field
// references (e.g. "Opportunity.Region__c" on a Quote) be resolved correctly.
func (s *ApprovalService) evaluateCriteriaItems(ctx context.Context, criteria []criterionItem, objectName string, record models.SObject, user *models.UserSession) bool {
	for _, group := range groupCriteriaForImplicitOr(criteria) {
		groupMatched := false
		for _, c := range group {
			if s.matchesCriterion(ctx, c, objectName, record, user) {
				groupMatched = true
				break
			}
		}
		if !groupMatched {
			return false
		}
	}

	return true
}

func (s *ApprovalService) matchesCriterion(ctx context.Context, c criterionItem, objectName string, record models.SObject, user *models.UserSession) bool {
	rawField := strings.TrimSpace(c.field)

	// Cross-object reference handling. A dotted field whose prefix is NOT the
	// current object (e.g. "Opportunity.Region__c" on a Quote record) is
	// resolved by following the standard Salesforce FK convention
	// "<Parent>Id" on the record and loading the related row. Same-object
	// prefixes ("Quote.Autoapproval__c" on a Quote) fall through to the local
	// lookup below. RecordType cross-object refs are uncommon in SF and are
	// intentionally not handled here — they would require a metadata describe.
	if idx := strings.Index(rawField, "."); idx >= 0 {
		prefix := rawField[:idx]
		rest := rawField[idx+1:]
		if !strings.EqualFold(prefix, objectName) {
			value, _, err := s.resolveCrossObjectValue(ctx, prefix, rest, record, user)
			if err != nil {
				s.logger.Warn(fmt.Sprintf("Cross-object criterion lookup failed for %q: %s", rawField, err.Error()))
				return false
			}
			return evaluateCriterion(value, c.operation, c.value)
		}
	}

	normalizedField := normalizeCriterionFieldName(rawField)
	// Salesforce standard: entry criteria for record types are typically expressed
	// against the RecordType Name (e.g., 'RecordTypeId equals Partner' or
	// 'Contract.RecordType equals "Master Services Agreement"'), but records
	// store only the RecordType FK ID under the column 'RecordTypeId'.
	//
	// We canonicalize both forms here so the loop below can compare against
	// either an ID or a Name. See issue #532.
	isRecordTypeField := normalizedField == constants.FieldSysLayoutAssignment_RecordTypeID || normalizedField == "RecordType"
	recordIDForField := normalizedField
	if isRecordTypeField {
		recordIDForField = constants.FieldSysLayoutAssignment_RecordTypeID
	}
	recordValue := formatRecordValue(record[recordIDForField])
	if evaluateCriterion(recordValue, c.operation, c.value) {
		return true
	}

	if isRecordTypeField && recordValue != "" {
		rtName, lookupErr := s.resolveRecordTypeName(ctx, recordValue, user)
		if lookupErr != nil {
			s.logger.Warn(fmt.Sprintf("Failed to resolve RecordTypeId %q for entry criteria, treating as no match: %s", recordValue, lookupErr.Error()))
			return false
		}
		if rtName != "" && evaluateCriterion(rtName, c.operation, c.value) {
			return true
		}
	}

	return false
}

// formatRecordValue renders a record field value for criterion comparison.
// Returns "" for nil/missing keys (so `equals ""` can match blank fields,
// matching Salesforce's is-null semantics) instead of the literal "<nil>"
// that fmt.Sprintf("%v", nil) would produce.
func formatRecordValue(v interface{}) string {
	if v == nil {
		return ""
	}
	return fmt.Sprintf("%v", v)
}

// resolveCrossObjectValue follows a foreign-key relationship to read a leaf
// field value on a related record. `parentObj` is the related-object name
// from a cross-object criterion reference (e.g. "Opportunity" in
// "Opportunity.Region__c"). `rest` is everything after the first dot; if it
// contains a further dot a second hop is performed (e.g.
// "Opportunity.Account.Region__c" resolves Opportunity → Account →
// Region__c). Deeper paths are not supported and return ok=false.
//
// Returns the formatted value and ok=true on a successful hop, ("", false,
// nil) when the FK is empty/missing or the related record cannot be found,
// and a non-nil err only on DB lookup failure.
func (s *ApprovalService) resolveCrossObjectValue(ctx context.Context, parentObj, rest string, record models.SObject, user *models.UserSession) (string, bool, error) {
	parentObj = strings.TrimSpace(parentObj)
	rest = strings.TrimSpace(rest)
	if parentObj == "" || rest == "" {
		return "", false, nil
	}
	fkField := parentObj + "Id"
	raw, ok := record[fkField]
	if !ok || raw == nil {
		return "", false, nil
	}
	fkID := strings.TrimSpace(fmt.Sprintf("%v", raw))
	if fkID == "" {
		return "", false, nil
	}
	filter := fmt.Sprintf("Id == '%s'", strings.ReplaceAll(fkID, "'", "''"))
	res, err := s.query.QueryWithFilter(ctx, parentObj, filter, user, "", "", 1)
	if err != nil {
		return "", false, fmt.Errorf("cross-object lookup %s(%s): %w", parentObj, fkID, err)
	}
	if res == nil || res.TotalSize == 0 || len(res.Records) == 0 {
		return "", false, nil
	}
	related := res.Records[0]
	if idx := strings.Index(rest, "."); idx >= 0 {
		return s.resolveCrossObjectValue(ctx, rest[:idx], rest[idx+1:], related, user)
	}
	return formatRecordValue(related[rest]), true, nil
}

func groupCriteriaForImplicitOr(criteria []criterionItem) [][]criterionItem {
	groups := make([][]criterionItem, 0, len(criteria))
	groupIndexByKey := make(map[string]int)

	for _, criterion := range criteria {
		if key, ok := implicitOrGroupKey(criterion); ok {
			if idx, exists := groupIndexByKey[key]; exists {
				groups[idx] = append(groups[idx], criterion)
				continue
			}
			groupIndexByKey[key] = len(groups)
		}
		groups = append(groups, []criterionItem{criterion})
	}

	return groups
}

func implicitOrGroupKey(c criterionItem) (string, bool) {
	switch strings.ToLower(strings.TrimSpace(c.operation)) {
	case "equals", "includes":
		return normalizeCriterionFieldName(c.field) + "::" + strings.ToLower(strings.TrimSpace(c.operation)), true
	default:
		return "", false
	}
}

func normalizeCriterionFieldName(fieldName string) string {
	fieldName = strings.TrimSpace(fieldName)
	if idx := strings.Index(fieldName, "."); idx >= 0 {
		fieldName = fieldName[idx+1:]
	}
	return fieldName
}

// evaluateBooleanFilter evaluates a boolean filter expression like "1 OR 2", "1 AND 2", "(1 OR 2) AND 3"
// against the provided criteria and record. `objectName` is the SObject the
// record was loaded from and is propagated so individual terms can resolve
// cross-object field references.
func (s *ApprovalService) evaluateBooleanFilter(ctx context.Context, expr string, criteria []criterionItem, objectName string, record models.SObject, user *models.UserSession) (bool, error) {
	if len(criteria) == 0 {
		return false, fmt.Errorf("no criteria items to evaluate against")
	}

	result, err := s.evaluateBooleanFilterExpression(ctx, expr, criteria, objectName, record, user)
	if err != nil {
		return false, err
	}
	return result, nil
}

// evaluateBooleanFilterExpression is a recursive descent parser for boolean expressions
func (s *ApprovalService) evaluateBooleanFilterExpression(ctx context.Context, expr string, criteria []criterionItem, objectName string, record models.SObject, user *models.UserSession) (bool, error) {
	// Remove outer parentheses only if they enclose the whole expression.
	// Naively stripping whenever expr starts with '(' and ends with ')' is
	// wrong for shapes like "(3 OR 5) AND 4 AND (6 OR 7)" — the first
	// group's close is mid-expression, not terminal. We detect that by
	// checking whether balance reaches 0 before the final character.
	expr = strings.TrimSpace(expr)
	for len(expr) > 2 && expr[0] == '(' && expr[len(expr)-1] == ')' {
		balance := 0
		encloses := true
		for i := 0; i < len(expr); i++ {
			if expr[i] == '(' {
				balance++
			} else if expr[i] == ')' {
				balance--
				if balance == 0 && i < len(expr)-1 {
					encloses = false
					break
				}
			}
		}
		if !encloses || balance != 0 {
			break
		}
		expr = strings.TrimSpace(expr[1 : len(expr)-1])
	}

	// Find the lowest precedence operator that's not enclosed in parentheses
	// We need to find OR first (lower precedence), then AND (higher precedence)

	// Look for top-level OR operators
	ops := []string{" OR ", " AND "}

	for _, opSymbol := range ops {
		balance := 0
		for i := 0; i < len(expr)-len(opSymbol)+1; i++ {
			// Check if at position i we have the operator
			if balance == 0 && i+len(opSymbol) <= len(expr) && strings.EqualFold(expr[i:i+len(opSymbol)], opSymbol) {
				// Found top-level operator
				left := expr[:i]
				right := expr[i+len(opSymbol):]

				leftResult, leftErr := s.evaluateBooleanFilterExpression(ctx, strings.TrimSpace(left), criteria, objectName, record, user)
				if leftErr != nil {
					return false, leftErr
				}

				rightResult, rightErr := s.evaluateBooleanFilterExpression(ctx, strings.TrimSpace(right), criteria, objectName, record, user)
				if rightErr != nil {
					return false, rightErr
				}

				if opSymbol == " OR " {
					return leftResult || rightResult, nil
				} else if opSymbol == " AND " {
					return leftResult && rightResult, nil
				}
			}

			// Track parentheses balance
			if expr[i] == '(' {
				balance++
			} else if expr[i] == ')' {
				balance--
			}
		}
	}

	// If we got here, it's either a single term or something went wrong in parsing
	// It should be a single term like "1", "2", etc.
	term := strings.TrimSpace(expr)

	// Check if it's a number (criteria index)
	if _, err := strconv.Atoi(term); err == nil {
		num, _ := strconv.Atoi(term)
		// Criteria indices are 1-based in Salesforce
		if num < 1 || num > len(criteria) {
			// If the index is out of bounds, treat as false
			return false, nil
		}

		// Evaluate the indexed criteria against the record. matchesCriterion
		// handles both same-object prefix stripping and cross-object FK
		// resolution, so we hand the criterion through verbatim.
		criterion := criteria[num-1] // Convert to 0-based index
		tempCriteria := []criterionItem{criterion}
		return s.evaluateCriteriaItems(ctx, tempCriteria, objectName, record, user), nil
	}

	// Check if it's a parenthesized expression
	if len(term) >= 2 && term[0] == '(' && term[len(term)-1] == ')' {
		return s.evaluateBooleanFilterExpression(ctx, term[1:len(term)-1], criteria, objectName, record, user)
	}

	return false, fmt.Errorf("unable to parse boolean expression term: %s", term)
}

// stepCriteriaMatches reports whether a ProcessNode's step criteria (sourced
// from the ProcessDefinition metadata) match the target record.
//
// Formula-based criteria will now be evaluated instead of being treated as automatic matches.
// Boolean filters are still treated as matches (due to lack of support).
// Unreadable records are treated as a match so runtime behavior never blocks
// on partially-supported metadata.
func (s *ApprovalService) stepCriteriaMatches(ctx context.Context, def models.SObject, node models.SObject, objectName, recordID string, user *models.UserSession) bool {
	step := s.metadataStepForNode(def, node)
	if step == nil || step.StepCriteria == nil {
		return true
	}
	sc := step.StepCriteria

	// If there's a formula in the step criteria, evaluate it
	if sc.Formula != "" {
		result, err := s.evaluateFormula(ctx, sc.Formula, objectName, recordID, user)
		if err != nil {
			// If formula evaluation fails, log and treat as match to avoid blocking
			s.logger.Warn(fmt.Sprintf("stepCriteria: formula evaluation failed for %s/%s, treating as match: %s", objectName, recordID, err.Error()))
			return true
		}
		return result
	}

	if len(sc.CriteriaItems) == 0 {
		// Empty criteria (but not formula) - treat as match
		return true
	}
	if strings.TrimSpace(sc.BooleanFilter) != "" {
		// Parse and evaluate the boolean filter expression
		booleanFilterExpr := strings.TrimSpace(sc.BooleanFilter)

		// Build criterion items from criteria items in the step
		items := make([]criterionItem, 0, len(sc.CriteriaItems))
		for _, it := range sc.CriteriaItems {
			items = append(items, criterionItem{field: it.Field, operation: it.Operation, value: it.Value})
		}

		// Fetch the record for evaluation
		recordFilter := fmt.Sprintf("Id == '%s'", recordID)
		res, err := s.query.QueryWithFilter(ctx, objectName, recordFilter, user, "", "", 1)
		if err != nil || res == nil || res.TotalSize == 0 {
			s.logger.Warn(fmt.Sprintf("stepCriteria: cannot load %s/%s for booleanFilter evaluation, treating as match", objectName, recordID))
			return true
		}

		// Evaluate the boolean filter expression against the record
		match, evalErr := s.evaluateBooleanFilter(ctx, booleanFilterExpr, items, objectName, res.Records[0], user)
		if evalErr != nil {
			s.logger.Warn(fmt.Sprintf("stepCriteria: error evaluating booleanFilter %q, treating as match: %v", sc.BooleanFilter, evalErr))
			return true
		}

		return match
	}
	items := make([]criterionItem, 0, len(sc.CriteriaItems))
	for _, it := range sc.CriteriaItems {
		items = append(items, criterionItem{field: it.Field, operation: it.Operation, value: it.Value})
	}
	recordFilter := fmt.Sprintf("Id == '%s'", recordID)
	res, err := s.query.QueryWithFilter(ctx, objectName, recordFilter, user, "", "", 1)
	if err != nil || res == nil || res.TotalSize == 0 {
		s.logger.Warn(fmt.Sprintf("stepCriteria: cannot load %s/%s, treating as match", objectName, recordID))
		return true
	}
	return s.evaluateCriteriaItems(ctx, items, objectName, res.Records[0], user)
}

// evaluateFormula evaluates a formula against a record and returns true if the formula evaluates to true.
func (s *ApprovalService) evaluateFormula(ctx context.Context, formulaStr, objectName, recordID string, user *models.UserSession) (bool, error) {
	// Check if formulaEngine is available (defensive check for tests that may not initialize it)
	if s.formulaEngine == nil {
		s.logger.Warn("Formula evaluation skipped: formula engine not available, treating as match")
		return true, nil
	}

	// Fetch the target record to provide as context for the formula evaluation
	recordFilter := fmt.Sprintf("Id == '%s'", recordID)
	recordResult, err := s.query.QueryWithFilter(ctx, objectName, recordFilter, user, "", "", 1)
	if err != nil || recordResult.TotalSize == 0 {
		s.logger.Error("Failed to retrieve record for formula evaluation", err)
		return false, fmt.Errorf("could not load record %s/%s for formula evaluation: %w", objectName, recordID, err)
	}

	// Safely extract user info handling potential nil pointers
	emailValue := interface{}(nil)
	if user.Email != nil {
		emailValue = *user.Email
	}

	roleIdValue := interface{}(nil)
	if user.RoleID != nil {
		roleIdValue = *user.RoleID
	}

	roleNameValue := interface{}(nil)
	if user.RoleName != nil {
		roleNameValue = *user.RoleName
	}

	// Create a formula context with the record data
	formulaContext := &formula.Context{
		Record: recordResult.Records[0],
		User: map[string]interface{}{
			"ID":            user.ID,
			"Name":          user.Name,
			"Email":         emailValue,
			"ProfileId":     user.ProfileID,
			"ProfileName":   user.ProfileName,
			"RoleId":        roleIdValue,
			"RoleName":      roleNameValue,
			"IsSystemAdmin": user.IsSystemAdmin,
		},
		Env: map[string]interface{}{
			"Now":   time.Now().Format(time.RFC3339),
			"Today": time.Now().Truncate(24 * time.Hour).Format("2006-01-02"),
		},
	}

	// Evaluate the formula
	result, err := s.formulaEngine.Evaluate(formulaStr, formulaContext)
	if err != nil {
		s.logger.Error("Failed to evaluate formula", err)
		return false, fmt.Errorf("formula evaluation error: %w", err)
	}

	// Convert the result to boolean
	boolResult, ok := result.(bool)
	if !ok {
		// If the result is not a boolean, consider non-empty/true-like values as true
		// For numeric values, treat non-zero as true
		switch v := result.(type) {
		case string:
			boolResult = v != ""
		case int, int32, int64:
			boolResult = fmt.Sprintf("%v", v) != "0"
		case float32, float64:
			boolResult = fmt.Sprintf("%v", v) != "0"
		default:
			// Any non-nil value is treated as true
			boolResult = result != nil
		}
	}

	return boolResult, nil
}

// resolveRecordTypeName looks up the Name of a RecordType by its ID.
// Returns ("", nil) if the RecordType is not found.
func (s *ApprovalService) resolveRecordTypeName(ctx context.Context, recordTypeID string, user *models.UserSession) (string, error) {
	if recordTypeID == "" {
		return "", nil
	}
	filter := fmt.Sprintf("Id == '%s'", recordTypeID)
	result, err := s.query.QueryWithFilter(ctx, constants.TableRecordType, filter, user, "", "", 1)
	if err != nil {
		return "", fmt.Errorf("query RecordType by id: %w", err)
	}
	if result == nil || result.TotalSize == 0 || len(result.Records) == 0 {
		return "", nil
	}
	name, _ := result.Records[0][constants.FieldSysRecordType_Name].(string)
	return name, nil
}

// jsonEntryCriteria represents the JSON entry criteria format from Salesforce sync.
type jsonEntryCriteria struct {
	Formula       string             `json:"formula"`
	BooleanFilter string             `json:"booleanFilter"`
	CriteriaItems []jsonCriteriaItem `json:"criteriaItems"`
}

type jsonCriteriaItem struct {
	Field     string `json:"field"`
	Operation string `json:"operation"`
	Value     string `json:"value"`
}

// parseJSONEntryCriteria parses JSON entry criteria into criterionItems.
// Returns formula string if it's a formula-based criteria, otherwise returns regular criteriaItems.
func parseJSONEntryCriteria(raw string) ([]criterionItem, string, string, error) {
	var jc jsonEntryCriteria
	if err := json.Unmarshal([]byte(raw), &jc); err != nil {
		return nil, "", "", fmt.Errorf("invalid JSON: %w", err)
	}
	if jc.Formula != "" {
		return nil, jc.Formula, "", nil
	}
	if len(jc.CriteriaItems) == 0 {
		return nil, "", jc.BooleanFilter, nil
	}

	var result []criterionItem
	for _, item := range jc.CriteriaItems {
		// Preserve the raw field reference (including any dotted prefix).
		// matchesCriterion decides at evaluation time whether the prefix is
		// same-object (strip and look up locally) or cross-object (follow FK
		// and load the related record).
		result = append(result, criterionItem{
			field:     strings.TrimSpace(item.Field),
			operation: item.Operation,
			value:     item.Value,
		})
	}
	return result, "", jc.BooleanFilter, nil
}

type criterionItem struct {
	field     string
	operation string
	value     string
}

// parseSimpleEntryCriteria parses "Field op Value AND Field op Value" format.
func parseSimpleEntryCriteria(input string) ([]criterionItem, error) {
	var result []criterionItem
	// Split by " AND " (case-sensitive, as generated by our deploy code)
	parts := strings.Split(input, " AND ")
	for _, part := range parts {
		part = strings.TrimSpace(part)
		if part == "" {
			continue
		}
		// Each part: "Field operation Value"
		// Operations: equals, notEqual, greaterThan, lessThan, greaterOrEqual, lessOrEqual, contains, notContain, startsWith
		ops := []string{"greaterOrEqual", "lessOrEqual", "greaterThan", "lessThan", "notEqual", "notContain", "startsWith", "contains", "excludes", "includes", "equals"}
		var matched bool
		for _, op := range ops {
			idx := strings.Index(part, " "+op+" ")
			if idx >= 0 {
				// Preserve the raw field reference; prefix handling (same-
				// object strip or cross-object FK follow) happens at evaluation
				// time in matchesCriterion.
				field := strings.TrimSpace(part[:idx])
				value := strings.TrimSpace(part[idx+len(" "+op+" "):])
				result = append(result, criterionItem{field: field, operation: op, value: value})
				matched = true
				break
			}
		}
		if !matched {
			return nil, fmt.Errorf("unrecognized criterion: %s", part)
		}
	}
	return result, nil
}

// evaluateCriterion compares a record field value against a criterion.
// criterionValue may be comma-separated for multi-value matching (e.g., "NA,EMEA").
func evaluateCriterion(recordValue, operation, criterionValue string) bool {
	switch operation {
	case "equals":
		// Support comma-separated multi-value: "NA,EMEA" means recordValue matches ANY
		if strings.Contains(criterionValue, ",") {
			for _, v := range strings.Split(criterionValue, ",") {
				if strings.EqualFold(strings.TrimSpace(recordValue), strings.TrimSpace(v)) {
					return true
				}
			}
			return false
		}
		return strings.EqualFold(recordValue, criterionValue)
	case "notEqual":
		if strings.Contains(criterionValue, ",") {
			for _, v := range strings.Split(criterionValue, ",") {
				if strings.EqualFold(strings.TrimSpace(recordValue), strings.TrimSpace(v)) {
					return false
				}
			}
			return true
		}
		return !strings.EqualFold(recordValue, criterionValue)
	case "greaterThan":
		return compareNumeric(recordValue, criterionValue) > 0
	case "lessThan":
		return compareNumeric(recordValue, criterionValue) < 0
	case "greaterOrEqual":
		return compareNumeric(recordValue, criterionValue) >= 0
	case "lessOrEqual":
		return compareNumeric(recordValue, criterionValue) <= 0
	case "contains":
		return strings.Contains(strings.ToLower(recordValue), strings.ToLower(criterionValue))
	case "notContain":
		return !strings.Contains(strings.ToLower(recordValue), strings.ToLower(criterionValue))
	case "startsWith":
		return strings.HasPrefix(strings.ToLower(recordValue), strings.ToLower(criterionValue))
	case "includes":
		// Support comma-separated multi-value: "NA,EMEA" means recordValue matches ANY
		if strings.Contains(criterionValue, ",") {
			for _, v := range strings.Split(criterionValue, ",") {
				if strings.EqualFold(strings.TrimSpace(recordValue), strings.TrimSpace(v)) {
					return true
				}
			}
			return false
		}
		return strings.EqualFold(recordValue, criterionValue)
	case "excludes":
		// Support comma-separated multi-value: "NA,EMEA" means recordValue matches NONE
		if strings.Contains(criterionValue, ",") {
			for _, v := range strings.Split(criterionValue, ",") {
				if strings.EqualFold(strings.TrimSpace(recordValue), strings.TrimSpace(v)) {
					return false
				}
			}
			return true
		}
		return !strings.EqualFold(recordValue, criterionValue)
	default:
		return true // Unknown operator — don't block
	}
}

// Clone creates a duplicate of an existing approval process with all its steps and actions
func (s *ApprovalService) Clone(ctx context.Context, processDefID, newDeveloperName, newLabel string, user *models.UserSession) (*models.SObject, error) {
	ctx = constants.WithSecurityContext(ctx, constants.SystemContext, "ApprovalService: managing approval system tables")

	// Validate inputs
	if user == nil {
		return nil, apperrors.NewUnauthorizedError("authenticated user is required to clone approval processes")
	}
	if processDefID == "" {
		return nil, apperrors.NewValidationError("processDefID", "process definition ID is required")
	}
	if newDeveloperName == "" {
		return nil, apperrors.NewValidationError("newDeveloperName", "new developer name is required")
	}
	if newLabel == "" {
		return nil, apperrors.NewValidationError("newLabel", "new label is required")
	}
	if s.permissions == nil {
		return nil, apperrors.NewInternalError("permission service is required for approval process cloning", nil)
	}
	if !s.permissions.CheckSystemPermission(ctx, user, string(constants.PermModifyMetadata)) {
		return nil, apperrors.NewPermissionError("modify metadata for", "approval processes")
	}

	// Check if a process with the same DeveloperName already exists
	existingProcessQuery := fmt.Sprintf("DeveloperName == '%s'", newDeveloperName)
	existingProcessResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessDefinition, existingProcessQuery, user, "", "", 1)
	if err != nil {
		return nil, fmt.Errorf("failed to check for existing process with same DeveloperName: %w", err)
	}
	if len(existingProcessResult.Records) > 0 {
		return nil, apperrors.NewValidationError("newDeveloperName", "a process with this DeveloperName already exists")
	}

	// Load the source process through the query service because approval metadata
	// tables do not implement the generic soft-delete shape expected by GetRaw().
	originalProcessResult, err := s.query.QueryWithFilter(ctx, constants.TableProcessDefinition, fmt.Sprintf("Id == '%s'", processDefID), user, "", "", 1)
	if err != nil {
		return nil, fmt.Errorf("failed to read original process definition: %w", err)
	}
	if len(originalProcessResult.Records) == 0 {
		return nil, apperrors.NewNotFoundError(constants.TableProcessDefinition, processDefID)
	}
	originalProcess := originalProcessResult.Records[0]

	// Verify user has permission to clone (assuming they need read permission on the original process)
	objectName, _ := originalProcess["TableEnumOrId"].(string)
	if objectName != "" {
		hasPermission := s.permissions.CheckObjectPermissionWithUser(ctx, objectName, constants.PermRead, user)
		if !hasPermission {
			return nil, apperrors.NewPermissionError("read", objectName)
		}
	}

	var createdProcess models.SObject
	err = s.persistence.RunInTransaction(ctx, func(txCtx context.Context) error {
		newProcessDef := cloneApprovalMetadataRecord(originalProcess)
		newProcessDef["Name"] = newLabel
		newProcessDef["DeveloperName"] = newDeveloperName
		newProcessDef["State"] = "Inactive"
		newProcessDef["IsActive"] = false

		var insertErr error
		createdProcess, insertErr = s.persistence.Insert(processTableWriteContext(txCtx, "ApprovalService: clone ProcessDefinition"), constants.TableProcessDefinition, newProcessDef, user)
		if insertErr != nil {
			return fmt.Errorf("failed to create cloned process definition: %w", insertErr)
		}

		newProcessDefId, _ := createdProcess[constants.FieldID].(string)

		nodesQuery := fmt.Sprintf("ProcessDefinitionId == '%s'", processDefID)
		nodesResult, queryErr := s.query.QueryWithFilter(txCtx, constants.TableProcessNode, nodesQuery, user, "StepOrder", "ASC", 100)
		if queryErr != nil {
			return fmt.Errorf("failed to query process nodes for cloning: %w", queryErr)
		}

		nodeIDMap := make(map[string]string, len(nodesResult.Records))
		for _, node := range nodesResult.Records {
			oldNodeID, _ := node[constants.FieldID].(string)
			newNode := cloneApprovalMetadataRecord(node)
			newNode["ProcessDefinitionId"] = newProcessDefId

			createdNode, nodeErr := s.persistence.Insert(processTableWriteContext(txCtx, "ApprovalService: clone ProcessNode"), constants.TableProcessNode, newNode, user)
			if nodeErr != nil {
				return fmt.Errorf("failed to create cloned process node: %w", nodeErr)
			}

			if newNodeID, _ := createdNode[constants.FieldID].(string); oldNodeID != "" && newNodeID != "" {
				nodeIDMap[oldNodeID] = newNodeID
			}
		}

		actionsQuery := fmt.Sprintf("ProcessDefinitionId == '%s'", processDefID)
		actionsResult, queryErr := s.query.QueryWithFilter(txCtx, constants.TableProcessAction, actionsQuery, user, "Name", "ASC", 100)
		if queryErr != nil {
			return fmt.Errorf("failed to query process actions for cloning: %w", queryErr)
		}

		for _, action := range actionsResult.Records {
			newAction := cloneApprovalMetadataRecord(action)
			newAction["ProcessDefinitionId"] = newProcessDefId

			if oldNodeID, _ := action["ProcessNodeId"].(string); oldNodeID != "" {
				newNodeID, ok := nodeIDMap[oldNodeID]
				if !ok {
					actionName, _ := action["Name"].(string)
					if actionName == "" {
						actionName = oldNodeID
					}
					return fmt.Errorf("failed to remap ProcessNodeId %q for cloned action %q", oldNodeID, actionName)
				}
				newAction["ProcessNodeId"] = newNodeID
			}

			if _, actionErr := s.persistence.Insert(processTableWriteContext(txCtx, "ApprovalService: clone ProcessAction"), constants.TableProcessAction, newAction, user); actionErr != nil {
				return fmt.Errorf("failed to create cloned process action: %w", actionErr)
			}
		}

		return nil
	})
	if err != nil {
		return nil, err
	}

	return &createdProcess, nil
}

func cloneApprovalMetadataRecord(source models.SObject) models.SObject {
	cloned := make(models.SObject, len(source))
	for k, v := range source {
		if k == constants.FieldID ||
			k == constants.FieldCreatedDate ||
			k == constants.FieldCreatedByID ||
			k == constants.FieldLastModifiedDate ||
			k == constants.FieldLastModifiedByID ||
			strings.HasSuffix(k, "_Name") ||
			k == "attributes" {
			continue
		}

		if _, isNested := v.(map[string]interface{}); isNested {
			continue
		}
		cloned[k] = v
	}
	return cloned
}

// compareNumeric compares two string values as numbers. Returns -1, 0, or 1.
func compareNumeric(a, b string) int {
	var fa, fb float64
	fmt.Sscanf(a, "%f", &fa)
	fmt.Sscanf(b, "%f", &fb)
	if fa < fb {
		return -1
	}
	if fa > fb {
		return 1
	}
	return 0
}