package metadata

import (
	"context"
	"fmt"
	"log"
	"reflect"
	"strings"
	"time"

	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/events"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/nexuscrm/shared/pkg/salesforce"
	zerolog "github.com/rs/zerolog/log"
)

const flowSourceCustom = "custom"

// GetAllObjects returns all object definitions visible to the user
func (ms *MetadataService) GetAllObjects(ctx context.Context, user *models.UserSession) ([]models.ObjectMetadata, error) {
	ms.mu.RLock()
	defer ms.mu.RUnlock()

	schemas, err := ms.repo.GetAllSchemas(ctx)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query objects", err)
	}
	res := make([]models.ObjectMetadata, len(schemas))
	for i, s := range schemas {
		res[i] = *s
	}
	return res, nil
}

// GetSystemFields returns all system fields for an object using the metadata cache.
// System fields are those with IsSystem=true OR IsNameField=true.
// Uses cached schema snapshot instead of querying the database on every call (#357).
func (ms *MetadataService) GetSystemFields(ctx context.Context, objectAPIName string) []string {
	// Use cached schema (lock-free atomic read) instead of DB query
	schema := ms.GetSchema(ctx, objectAPIName)
	if schema == nil {
		return []string{}
	}

	// Virtual fields (IsVirtual=true) are intentionally excluded even when
	// they're marked IsSystem=true. GetSystemFields feeds the query-service
	// visibleFields seed (analytics/query_service.go), which becomes the
	// SQL SELECT column list in QueryRepository.Find(). Including a virtual
	// field here produces an "Unknown column" error against the physical
	// storage table — the virtual's value is materialized post-fetch by
	// projectPermissionSetGrants / hydrateVirtualFields, never read from a
	// raw column.
	//
	// Why a virtual is marked IsSystem=true in the first place: the
	// SchemaDecorator sets IsSystem to match Salesforce's describe shape
	// for Permissions<X> (Custom=false, part of the standard PermissionSet
	// object). That describe-DTO intent and this SQL-seeding intent diverge
	// — hence the filter here rather than a decorator change.
	var systemFields []string
	for _, field := range schema.Fields {
		if field.IsVirtual {
			continue
		}
		if field.IsSystem || field.IsNameField {
			systemFields = append(systemFields, field.APIName)
		}
	}

	return systemFields
}

// GetSupportedEvents returns a list of all event types supported by the system
func (ms *MetadataService) GetSupportedEvents() []string {
	return []string{
		string(events.RecordBeforeCreate),
		string(events.RecordAfterCreate),
		string(events.RecordBeforeUpdate),
		string(events.RecordAfterUpdate),
		string(events.RecordBeforeDelete),
		string(events.RecordAfterDelete),
	}
}

// HasActiveRecordTriggeredFlow reports whether any cached FlowDefinition
// is wired as an active record-trigger against objectAPIName. Used by
// OutboxService to decide whether outboxNoSubscriberObjects events for
// this object can be dropped: dropping is only safe when no flow is
// listening, otherwise the dispatcher would silently never fire.
//
// Match rules mirror flow_trigger_listener.executeRecordTriggerFlows:
//   - ActiveVersionId must be non-empty (an inactive draft does not
//     subscribe to events even if its TriggerType is set).
//   - TriggerType must be one of the four record-trigger values.
//   - TriggerObjectOrEvent compared case-insensitively.
//
// Returns true (fail-safe = "subscriber present") on any cache failure
// so the OutboxService never silently drops events when the listener
// state is unknown.
func (ms *MetadataService) HasActiveRecordTriggeredFlow(ctx context.Context, objectAPIName string) bool {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f HasActiveRecordTriggeredFlow: cache init failed for %s: %v", objectAPIName, err)
		return true
	}
	snap := ms.loadCache()
	if snap == nil {
		return true
	}
	for _, def := range snap.flowDefinitions {
		if def.ActiveVersionId == "" {
			continue
		}
		if !isRecordTriggerType(def.TriggerType) {
			continue
		}
		if strings.EqualFold(def.TriggerObjectOrEvent, objectAPIName) {
			return true
		}
	}
	return false
}

// isRecordTriggerType returns true for the four FlowDefinition.TriggerType
// values that produce subscribers in flow_trigger_listener.Subscribe.
// Scheduled / autolaunched / platform-event triggers are excluded
// because they do not consume RecordCreated/Updated/Deleted events.
//
// Comparison is case-insensitive for consistency with the listener's
// EqualFold match: flows imported from external tooling may store the
// trigger type with non-canonical casing.
func isRecordTriggerType(t string) bool {
	switch {
	case strings.EqualFold(t, "RecordBeforeSave"),
		strings.EqualFold(t, "RecordAfterSave"),
		strings.EqualFold(t, "RecordBeforeDelete"),
		strings.EqualFold(t, "RecordAfterDelete"):
		return true
	}
	return false
}

// GetFlowDefinitions returns all flow definitions from cache
func (ms *MetadataService) GetFlowDefinitions(ctx context.Context) []*models.FlowDefinition {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f Failed to initialize cache in GetFlowDefinitions: %v", err)
		return []*models.FlowDefinition{}
	}
	snap := ms.loadCache()
	if snap == nil {
		return []*models.FlowDefinition{}
	}
	return snap.flowDefinitions
}

// GetFlowDefinition returns a specific flow definition by ID
func (ms *MetadataService) GetFlowDefinition(ctx context.Context, id string) *models.FlowDefinition {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f Failed to initialize cache in GetFlowDefinition: %v", err)
		return nil
	}
	snap := ms.loadCache()
	if snap == nil {
		return nil
	}
	return snap.flowDefinitionMap[id]
}

// GetFlowDefinitionByName returns a flow definition by developer name
func (ms *MetadataService) GetFlowDefinitionByName(ctx context.Context, developerName string) *models.FlowDefinition {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f Failed to initialize cache in GetFlowDefinitionByName: %v", err)
		return nil
	}
	snap := ms.loadCache()
	if snap == nil {
		return nil
	}
	for _, def := range snap.flowDefinitions {
		if def.DeveloperName == developerName {
			return def
		}
	}
	return nil
}

// GetScheduledFlowDefinitions returns all flow definitions with trigger_type = "Scheduled"
func (ms *MetadataService) GetScheduledFlowDefinitions(ctx context.Context) []*models.FlowDefinition {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f Failed to initialize cache in GetScheduledFlowDefinitions: %v", err)
		return []*models.FlowDefinition{}
	}

	snap := ms.loadCache()
	if snap == nil {
		return []*models.FlowDefinition{}
	}

	// Filter for scheduled flows from cache
	var scheduled []*models.FlowDefinition
	for _, def := range snap.flowDefinitions {
		if def.TriggerType == "Scheduled" {
			scheduled = append(scheduled, def)
		}
	}
	return scheduled
}

// GetFlowVersionsByIDs retrieves multiple flow versions by IDs in a single query.
func (ms *MetadataService) GetFlowVersionsByIDs(ctx context.Context, ids []string) map[string]*models.FlowVersion {
	if len(ids) == 0 {
		return nil
	}
	result, err := ms.repo.GetFlowVersionsByIDs(ctx, ids)
	if err != nil {
		log.Printf("\u26a0\ufe0f Failed to bulk get flow versions: %v", err)
		return nil
	}
	return result
}

// GetFlowVersionById retrieves a flow version by ID from the database
// Note: This is not cached, as flow versions contain large Metadata JSON
func (ms *MetadataService) GetFlowVersionById(ctx context.Context, versionId string) *models.FlowVersion {
	if versionId == "" {
		return nil
	}

	version, err := ms.repo.GetFlowVersion(ctx, versionId)
	if err != nil {
		log.Printf("\u26a0\ufe0f Failed to get flow version %s: %v", versionId, err)
		return nil
	}
	return version
}

func (ms *MetadataService) GetValidationRules(ctx context.Context, objectAPIName string) []*models.ValidationRule {
	// Skip validation rule lookup for system objects - they never have custom rules
	if constants.IsSystemTable(objectAPIName) {
		return []*models.ValidationRule{}
	}

	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f Failed to initialize cache in GetValidationRules: %v", err)
		return []*models.ValidationRule{}
	}

	snap := ms.loadCache()
	if snap == nil {
		return []*models.ValidationRule{}
	}
	rules := snap.validationRulesMap[strings.ToLower(objectAPIName)]
	if rules == nil {
		return []*models.ValidationRule{}
	}
	return rules
}

func (ms *MetadataService) GetListViews(ctx context.Context, objectAPIName string) []*models.ListView {
	ms.mu.RLock()
	defer ms.mu.RUnlock()
	views, err := ms.repo.GetListViews(ctx, objectAPIName)
	if err != nil {
		log.Printf("Failed to get list views: %v", err)
		return defaultListViews(objectAPIName)
	}
	if len(views) == 0 {
		return defaultListViews(objectAPIName)
	}
	return views
}

// defaultListViews returns the three Salesforce-standard list views (All, My,
// Recently Viewed) that every object provides by default. These are used as a
// fallback when _System_ListView has no rows for the object, preserving
// backward compatibility with environments that have not bootstrapped list views.
func defaultListViews(objectAPIName string) []*models.ListView {
	return []*models.ListView{
		{
			ID:            fmt.Sprintf("%s-all-view", objectAPIName),
			ObjectAPIName: objectAPIName,
			Label:         fmt.Sprintf("All %s", objectAPIName),
		},
		{
			ID:            fmt.Sprintf("%s-my-view", objectAPIName),
			ObjectAPIName: objectAPIName,
			Label:         fmt.Sprintf("My %s", objectAPIName),
			FilterExpr:    "OwnerId = @user_id",
		},
		{
			ID:            fmt.Sprintf("%s-recent-view", objectAPIName),
			ObjectAPIName: objectAPIName,
			Label:         "Recently Viewed",
		},
	}
}

// CreateListView creates a new list view
func (ms *MetadataService) CreateListView(ctx context.Context, view *models.ListView) error {
	ms.mu.Lock()
	defer ms.mu.Unlock()

	if view.ID == "" {
		view.ID = GenerateID()
	}

	if err := ms.repo.CreateListView(ctx, view); err != nil {
		return apperrors.NewInternalError("failed to create list view", err)
	}
	return nil
}

// UpdateListView updates an existing list view
func (ms *MetadataService) UpdateListView(ctx context.Context, id string, updates *models.ListView) error {
	ms.mu.Lock()
	defer ms.mu.Unlock()

	if err := ms.repo.UpdateListView(ctx, id, updates); err != nil {
		return apperrors.NewInternalError("failed to update list view", err)
	}
	return nil
}

// DeleteListView deletes a list view
func (ms *MetadataService) DeleteListView(ctx context.Context, id string) error {
	ms.mu.Lock()
	defer ms.mu.Unlock()

	if err := ms.repo.DeleteListView(ctx, id); err != nil {
		return apperrors.NewInternalError("failed to delete list view", err)
	}
	return nil
}

func (ms *MetadataService) GetSharingRules(ctx context.Context, objectAPIName string) []*models.SharingRule {
	ms.mu.RLock()
	defer ms.mu.RUnlock()
	rules, err := ms.repo.GetSharingRules(ctx, objectAPIName)
	if err != nil {
		log.Printf("Failed to get sharing rules: %v", err)
		return []*models.SharingRule{}
	}
	return rules
}

func (ms *MetadataService) GetActions(ctx context.Context, objectAPIName string) []*models.ActionMetadata {
	ms.mu.RLock()
	defer ms.mu.RUnlock()
	actions, err := ms.repo.GetActions(ctx, objectAPIName)
	if err != nil {
		log.Printf("Failed to get actions: %v", err)
		return []*models.ActionMetadata{}
	}
	return actions
}

// GetAllActions returns all actions from all objects
func (ms *MetadataService) GetAllActions(ctx context.Context) []*models.ActionMetadata {
	ms.mu.RLock()
	defer ms.mu.RUnlock()

	actions, err := ms.repo.GetAllActions(ctx)
	if err != nil {
		log.Printf("Failed to query actions: %v", err)
		return []*models.ActionMetadata{}
	}
	return actions
}

func (ms *MetadataService) GetActionByID(ctx context.Context, actionID string) *models.ActionMetadata {
	ms.mu.RLock()
	defer ms.mu.RUnlock()

	action, err := ms.repo.GetAction(ctx, actionID)
	if err != nil {
		return nil
	}
	return action
}

func (ms *MetadataService) GetRecordTypes(ctx context.Context, objectAPIName string) []*models.RecordType {
	ms.mu.RLock()
	defer ms.mu.RUnlock()
	rts, err := ms.repo.GetRecordTypes(ctx, objectAPIName)
	if err != nil {
		log.Printf("ERROR: GetRecordTypes(%s): %v", objectAPIName, err)
		return []*models.RecordType{}
	}
	return rts
}

func (ms *MetadataService) GetAutoNumbers(ctx context.Context, objectAPIName string) []*models.AutoNumber {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		log.Printf("\u26a0\ufe0f Failed to initialize cache in GetAutoNumbers: %v", err)
		return []*models.AutoNumber{}
	}

	snap := ms.loadCache()
	if snap == nil {
		return []*models.AutoNumber{}
	}
	ans := snap.autoNumbersMap[strings.ToLower(objectAPIName)]
	if ans == nil {
		return []*models.AutoNumber{}
	}
	return ans
}

// GetIdentifierRule returns the active IdentifierRule for the given object,
// or (nil, nil) if the object has no active rule. The result is served from
// the metadata cache (built lazily on first call, refreshed via
// RefreshCache, invalidated via InvalidateCache) — same pattern as
// GetAutoNumbers.
//
// Returning (nil, nil) instead of an error is intentional: most objects do
// not have an identifier rule, and callers (the new identifier engine /
// fallback CompoundAutoNumberListener) treat absence as "no rule applies"
// rather than an error condition. An error is only returned when the cache
// cannot be initialized at all.
//
// See docs/design/unified-identifier-engine.md.
func (ms *MetadataService) GetIdentifierRule(ctx context.Context, objectAPIName string) (*models.IdentifierRule, error) {
	if err := ms.ensureCacheInitialized(ctx); err != nil {
		return nil, fmt.Errorf("initialize metadata cache for identifier rule lookup: %w", err)
	}

	snap := ms.loadCache()
	if snap == nil {
		return nil, nil
	}
	if rule, ok := snap.identifierRulesMap[strings.ToLower(objectAPIName)]; ok {
		return rule, nil
	}
	return nil, nil
}

func (ms *MetadataService) GetRelationships(ctx context.Context, objectAPIName string) []*models.Relationship {
	if err := ms.ensureCoreCacheInitialized(ctx); err != nil {
		log.Printf("⚠️ Failed to initialize core cache in GetRelationships(%s): %v", objectAPIName, err)
		return []*models.Relationship{}
	}

	snap := ms.loadCoreCache()
	if snap == nil {
		return []*models.Relationship{}
	}

	rels := snap.relationshipsMap[strings.ToLower(objectAPIName)]
	if len(rels) == 0 {
		return []*models.Relationship{}
	}
	return rels
}

func (ms *MetadataService) GetFieldDependencies(ctx context.Context, objectAPIName string) []*models.FieldDependency {
	ms.mu.RLock()
	defer ms.mu.RUnlock()
	fds, err := ms.repo.GetFieldDependencies(ctx, objectAPIName)
	if err != nil {
		return []*models.FieldDependency{}
	}
	return fds
}

func (ms *MetadataService) GetChildRelationships(ctx context.Context, parentObjectAPIName string) []*models.ObjectMetadata {
	if err := ms.ensureCoreCacheInitialized(ctx); err != nil {
		log.Printf("⚠️ Failed to initialize core cache in GetChildRelationships(%s): %v", parentObjectAPIName, err)
		return []*models.ObjectMetadata{}
	}

	snap := ms.loadCoreCache()
	if snap == nil {
		return []*models.ObjectMetadata{}
	}

	children := snap.childRelationshipsMap[strings.ToLower(parentObjectAPIName)]
	if len(children) == 0 {
		return []*models.ObjectMetadata{}
	}

	return children
}

// Flow Definition CRUD (Tooling API)

// GetFlowDefinitionById returns a flow definition by ID (alias for GetFlowDefinition)
func (ms *MetadataService) GetFlowDefinitionById(ctx context.Context, id string) *models.FlowDefinition {
	return ms.GetFlowDefinition(ctx, id)
}

// CreateFlowDefinition creates a new flow definition.
//
// Invariant guard for ActiveVersionId: this is the fourth writer for the
// FlowDefinition.ActiveVersionId fact (alongside ActivateFlowVersion,
// DeleteFlowVersion, and UpdateFlowDefinition). A Tooling create must
// reject any non-empty ActiveVersionId — at create time no Flow versions
// exist yet for this definition, so any value is by construction either
// missing, deleted, or cross-definition. Forcing ActiveVersionId to
// empty/NULL on create is consistent with Salesforce semantics
// (FlowDefinition is created without an active version; activation is a
// separate operation via ActivateFlowVersion or the Metadata.ActiveVersionNumber
// PATCH path) and closes the last writer that could produce orphan
// pointers.
func (ms *MetadataService) CreateFlowDefinition(ctx context.Context, def *models.FlowDefinition) error {
	if def.Id == "" {
		def.Id = generateStandardSObjectID("FlowDefinition")
	}
	if def.ActiveVersionId != "" {
		return fmt.Errorf("FlowDefinition.ActiveVersionId must be empty on create (no Flow versions exist for this definition yet); activate via ActivateFlowVersion or PATCH Metadata.ActiveVersionNumber after the version is created")
	}
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		if err := ms.repo.CreateFlowDefinition(ctx, def); err != nil {
			return err
		}
		return ms.markFlowDefinitionCustomized(ctx, def.Id)
	})
}

// UpdateFlowDefinition applies a sparse Tooling PATCH to an existing
// FlowDefinition and marks the row customized in the same transaction so
// future apply passes preserve admin ownership of the definition.
//
// Invariant guard for ActiveVersionId: this is the third writer for the
// FlowDefinition.ActiveVersionId fact (alongside ActivateFlowVersion and
// DeleteFlowVersion). Direct PATCH writes must reject any value that
// would create the same orphan / inactive pointer the bootstrap reconciler
// repairs — otherwise admins could re-introduce HTTP-500 drift via Tooling.
// A non-empty ActiveVersionId in the patch must point at an existing,
// non-deleted, Status='Active' Flow row whose DefinitionId matches this
// definition. An empty string is allowed (clearing the pointer is a valid
// admin op; the dispatcher treats NULL as "no active version").
func (ms *MetadataService) UpdateFlowDefinition(ctx context.Context, id string, patch *models.FlowDefinitionPatch) error {
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		if patch == nil || patch.Empty() {
			return nil
		}
		if patch.ActiveVersionId != nil && *patch.ActiveVersionId != "" {
			candidate := *patch.ActiveVersionId
			version := ms.GetFlowVersionById(ctx, candidate)
			if version == nil || version.IsDeleted || version.DefinitionId != id || version.Status != "Active" {
				return fmt.Errorf("ActiveVersionId %q is not a valid active flow version on FlowDefinition %q", candidate, id)
			}
		}
		if err := ms.repo.PatchFlowDefinition(ctx, id, patch); err != nil {
			return err
		}
		return ms.markFlowDefinitionCustomized(ctx, id)
	})
}

// WithToolingWriteTx exposes the tooling write transaction boundary so handlers
// can wrap multi-step user intents in one transaction.
func (ms *MetadataService) WithToolingWriteTx(ctx context.Context, fn func(ctx context.Context) error) error {
	return ms.inToolingWriteTx(ctx, fn)
}

func (ms *MetadataService) inToolingWriteTx(ctx context.Context, fn func(ctx context.Context) error) error {
	if ms.txManager == nil {
		err := fn(ctx)
		if err == nil {
			ms.InvalidateCache()
		}
		return err
	}
	if ms.txManager.IsTransactionActive(ctx) {
		return fn(ctx)
	}
	err := ms.txManager.WithTransaction(ctx, fn)
	if err == nil {
		ms.InvalidateCache()
	}
	return err
}

func (ms *MetadataService) markFlowDefinitionCustomized(ctx context.Context, definitionID string) error {
	if definitionID == "" {
		return nil
	}
	return ms.repo.MarkFlowDefinitionCustomized(ctx, definitionID)
}

// DeleteFlowDefinition deletes a flow definition.
func (ms *MetadataService) DeleteFlowDefinition(ctx context.Context, id string) error {
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		return ms.repo.DeleteFlowDefinition(ctx, id)
	})
}

// Flow Version CRUD (Tooling API)

// GetFlowVersions returns all versions for a flow definition
func (ms *MetadataService) GetFlowVersions(ctx context.Context, definitionId string) ([]*models.FlowVersion, error) {
	if definitionId == "" {
		// Return all versions if no definition ID specified
		return []*models.FlowVersion{}, nil
	}
	return ms.repo.GetFlowVersions(ctx, definitionId)
}

// CreateFlowVersion creates a new flow version, updates the parent
// FlowDefinition projection fields, and marks the definition customized in one
// transaction.
func (ms *MetadataService) CreateFlowVersion(ctx context.Context, version *models.FlowVersion) error {
	if version.Id == "" {
		version.Id = generateStandardSObjectID("Flow")
	}
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		if version.VersionNumber <= 0 && version.DefinitionId != "" {
			existing, err := ms.repo.GetFlowVersions(ctx, version.DefinitionId)
			if err != nil {
				return fmt.Errorf("failed to query existing versions for auto-increment: %w", err)
			}
			maxVer := 0
			for _, v := range existing {
				if v.VersionNumber > maxVer {
					maxVer = v.VersionNumber
				}
			}
			version.VersionNumber = maxVer + 1
		}
		if version.VersionNumber <= 0 {
			version.VersionNumber = 1
		}

		if err := ms.repo.CreateFlowVersion(ctx, version); err != nil {
			return err
		}
		if version.DefinitionId != "" {
			if err := ms.repo.SetLatestVersionId(ctx, version.DefinitionId, version.Id); err != nil {
				return fmt.Errorf("update LatestVersionId: %w", err)
			}
			if err := ms.syncTriggerInfo(ctx, version.DefinitionId, &version.Metadata); err != nil {
				return fmt.Errorf("sync trigger info: %w", err)
			}
			return ms.markFlowDefinitionCustomized(ctx, version.DefinitionId)
		}
		return nil
	})
}

// UpdateFlowVersion updates a flow version and marks the parent definition
// customized in the same transaction.
//
// Invariant guard for ActiveVersionId: this is the fifth writer that can
// affect the FlowDefinition.ActiveVersionId invariant — UpdateFlowVersion
// overwrites Flow.Status verbatim from the sparse struct
// (repo_flows.go UpdateFlowVersion). If the version being patched is the
// one currently referenced by FlowDefinition.ActiveVersionId, allowing a
// Status change away from 'Active' (or to empty) would leave the
// FlowDefinition pointing at a now-non-Active row — recreating the same
// invariant violation the bootstrap reconciler repairs.
//
// Reject Status changes on the active version. Callers wanting to
// deactivate the active version must use DeactivateFlowVersion (which
// also clears FlowDefinition.ActiveVersionId atomically) or
// ActivateFlowVersion to rotate the active pointer to another version.
func (ms *MetadataService) UpdateFlowVersion(ctx context.Context, id string, version *models.FlowVersion) error {
	existing := ms.GetFlowVersionById(ctx, id)
	// Status invariant: the repo's UpdateFlowVersion overwrites Flow.Status
	// verbatim from the sparse struct (repo_flows.go), so a sparse PATCH
	// like {"Description": "..."} that decodes to Status="" would otherwise
	// blank out the existing Status. Two-step guard:
	//   (1) If the caller did not provide a Status (empty string), preserve
	//       the existing Status — sparse PATCH must not stomp it.
	//   (2) If the caller IS changing Status AND this version is currently
	//       FlowDefinition.ActiveVersionId, reject the change. Status
	//       transitions on the active version must go through
	//       ActivateFlowVersion / DeactivateFlowVersion which rotate the
	//       FlowDefinition pointer atomically.
	if existing != nil && version != nil {
		if version.Status == "" {
			version.Status = existing.Status
		}
		// Metadata field preservation: SerializeMetadata serializes a zero
		// FlowMetadata struct to "{}" when MetadataRaw is empty, so a sparse
		// PATCH like {"Description": "..."} would otherwise overwrite the
		// stored flow graph with "{}" — equivalent to deleting the canvas.
		// If the caller provided neither raw JSON nor a populated typed
		// metadata struct, copy the existing version's metadata forward so
		// the repo write is a no-op for the graph fields.
		rawEmpty := len(version.MetadataRaw) == 0 || string(version.MetadataRaw) == "{}"
		typedZero := reflect.DeepEqual(version.Metadata, models.FlowMetadata{})
		if rawEmpty && typedZero {
			version.MetadataRaw = existing.MetadataRaw
			version.MetadataJSON = existing.MetadataJSON
			version.Metadata = existing.Metadata
		}
		if version.Status != existing.Status {
			defID := existing.DefinitionId
			if defID == "" {
				defID = version.DefinitionId
			}
			if defID != "" {
				if def, err := ms.repo.GetFlowDefinition(ctx, defID); err == nil && def != nil && def.ActiveVersionId == id {
					return fmt.Errorf("cannot change Status of Flow %q via UpdateFlowVersion while it is the FlowDefinition.ActiveVersionId; use ActivateFlowVersion / DeactivateFlowVersion instead", id)
				}
			}
		}
	}
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		if err := ms.repo.UpdateFlowVersion(ctx, id, version); err != nil {
			return err
		}
		definitionID := ""
		var metadata *models.FlowMetadata
		if version != nil {
			definitionID = version.DefinitionId
			metadata = &version.Metadata
		}
		if definitionID == "" && existing != nil {
			definitionID = existing.DefinitionId
		}
		if definitionID != "" {
			if err := ms.syncTriggerInfo(ctx, definitionID, metadata); err != nil {
				return fmt.Errorf("sync trigger info: %w", err)
			}
			return ms.markFlowDefinitionCustomized(ctx, definitionID)
		}
		return nil
	})
}

// DeleteFlowVersion deletes a flow version.
// Salesforce behavior: when the last version of a FlowDefinition is deleted,
// the FlowDefinition itself is automatically removed.
//
// Invariant guard: when the deleted Flow is the one currently referenced by
// FlowDefinition.ActiveVersionId, the pointer would otherwise become orphan
// and break the runtime dispatcher (HTTP 500 on every record save that
// dispatches the flow). Re-establish the invariant at write time:
//   - prefer redirecting to another Active version still alive on this
//     definition (smallest Id, deterministic — same rule the bootstrap
//     reconciler uses), OR
//   - NULL the pointer when no replacement exists; the dispatcher treats
//     NULL as "no active version" and skips silently.
//
// This mirrors backend/internal/bootstrap/reconcile_orphan_flow_active_version.go
// so the bootstrap reconciler is a pure backstop, not the only writer that
// preserves the invariant.
func (ms *MetadataService) DeleteFlowVersion(ctx context.Context, id string) error {
	version := ms.GetFlowVersionById(ctx, id)
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		// Snapshot the definition pointer BEFORE the delete so we can detect
		// whether the row we are about to remove is the active one.
		var preDefActiveVersionID string
		if version != nil && version.DefinitionId != "" {
			if def, err := ms.repo.GetFlowDefinition(ctx, version.DefinitionId); err == nil && def != nil {
				preDefActiveVersionID = def.ActiveVersionId
			}
		}
		if err := ms.repo.DeleteFlowVersion(ctx, id); err != nil {
			return err
		}
		if version == nil || version.DefinitionId == "" {
			return nil
		}
		remaining, err := ms.repo.GetFlowVersions(ctx, version.DefinitionId)
		if err != nil {
			return fmt.Errorf("check remaining versions before auto-cleanup: %w", err)
		}
		if len(remaining) > 0 {
			// Re-establish the ActiveVersionId invariant before marking
			// customized — see function-level doc comment.
			if preDefActiveVersionID == id {
				replacement := ""
				for _, v := range remaining {
					if v == nil || v.IsDeleted || v.Status != "Active" {
						continue
					}
					if replacement == "" || v.Id < replacement {
						replacement = v.Id
					}
				}
				newActive := replacement
				patch := &models.FlowDefinitionPatch{ActiveVersionId: &newActive}
				if err := ms.repo.PatchFlowDefinition(ctx, version.DefinitionId, patch); err != nil {
					return fmt.Errorf("re-point FlowDefinition.ActiveVersionId after deleting active version %q: %w", id, err)
				}
			}
			return ms.markFlowDefinitionCustomized(ctx, version.DefinitionId)
		}
		if err := ms.repo.DeleteFlowDefinition(ctx, version.DefinitionId); err != nil {
			return fmt.Errorf("auto-delete empty FlowDefinition: %w", err)
		}
		return nil
	})
}

// ActivateFlowVersion activates a flow version and only marks the parent
// definition customized when the activated version is admin-owned
// (Source='custom').
func (ms *MetadataService) ActivateFlowVersion(ctx context.Context, id string) error {
	version := ms.GetFlowVersionById(ctx, id)
	// Invariant guard: refuse to activate a Flow row that does not exist
	// or has been soft-deleted. repo.ActivateFlowVersion writes
	// FlowDefinition.ActiveVersionId = id without an IsDeleted predicate,
	// so activating a tombstoned row would re-create the exact orphan
	// pointer the bootstrap reconciler is meant to repair.
	if version == nil {
		return fmt.Errorf("cannot activate Flow %q: version not found", id)
	}
	if version.IsDeleted {
		return fmt.Errorf("cannot activate Flow %q: version is soft-deleted", id)
	}
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		if err := ms.repo.ActivateFlowVersion(ctx, id); err != nil {
			return err
		}
		if version != nil && version.DefinitionId != "" {
			if err := ms.syncTriggerInfo(ctx, version.DefinitionId, &version.Metadata); err != nil {
				return fmt.Errorf("sync trigger info: %w", err)
			}
			if version.Source == flowSourceCustom {
				return ms.markFlowDefinitionCustomized(ctx, version.DefinitionId)
			}
		}
		return nil
	})
}

// DeactivateFlowVersion deactivates a flow version and marks the parent
// definition customized so bootstrap preserves the intentionally inactive
// state.
func (ms *MetadataService) DeactivateFlowVersion(ctx context.Context, id string) error {
	version := ms.GetFlowVersionById(ctx, id)
	return ms.inToolingWriteTx(ctx, func(ctx context.Context) error {
		if err := ms.repo.DeactivateFlowVersion(ctx, id); err != nil {
			return err
		}
		if version != nil && version.DefinitionId != "" {
			return ms.markFlowDefinitionCustomized(ctx, version.DefinitionId)
		}
		return nil
	})
}

// syncTriggerInfo extracts trigger info from flow metadata and updates the FlowDefinition.
func (ms *MetadataService) syncTriggerInfo(ctx context.Context, definitionId string, metadata *models.FlowMetadata) error {
	if metadata == nil || metadata.Start == nil {
		return nil
	}
	triggerType := metadata.Start.TriggerType
	objectOrEvent := metadata.Start.Object
	if triggerType == "" && objectOrEvent == "" {
		return nil
	}
	return ms.repo.SetTriggerInfo(ctx, definitionId, triggerType, objectOrEvent)
}

// BackfillTriggerInfo is the public version of syncTriggerInfo, used during bootstrap
// to fix FlowDefinitions that are missing trigger metadata.
func (ms *MetadataService) BackfillTriggerInfo(ctx context.Context, definitionId string, triggerType string, objectOrEvent string) {
	if err := ms.repo.SetTriggerInfo(ctx, definitionId, triggerType, objectOrEvent); err != nil {
		zerolog.Warn().Err(err).Str("definitionId", definitionId).Msg("Failed to backfill trigger info for FlowDefinition")
	}
	ms.InvalidateCache()
}

// generateStandardSObjectID generates a Salesforce-compatible 18-char record ID
// using the standard key prefix for the given SObject API name.
// The objectAPIName must be registered in shared/pkg/salesforce/compat.go.
func generateStandardSObjectID(objectAPIName string) string {
	prefix, ok := salesforce.StandardKeyPrefixForObject(objectAPIName)
	if !ok {
		panic(fmt.Sprintf("generateStandardSObjectID: no key prefix registered for %q", objectAPIName))
	}
	return fmt.Sprintf("%s%015d", prefix, time.Now().UnixNano()%1000000000000000)
}