package packs

import (
	"context"
	"database/sql"
	_ "embed"
	"encoding/json"
	"fmt"
	"log"
	"strings"
	"time"

	"github.com/nexuscrm/backend/internal/bootstrap"
	"github.com/nexuscrm/backend/internal/controlplane/layoutownership"
	"github.com/nexuscrm/backend/internal/features/metadata"
	"github.com/nexuscrm/backend/pkg/actionvalidate"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	zlog "github.com/rs/zerolog/log"
)

//go:embed themes.json
var themesJSON []byte

//go:embed ui_components.json
var uiComponentsJSON []byte

//go:embed setup_pages.json
var setupPagesJSON []byte

//go:embed standard_actions.json
var standardActionsJSON []byte

// ---- Types ----

type themesData struct {
	Themes []models.Theme `json:"themes"`
}

type uiComponentsData struct {
	Components []models.UIComponent `json:"components"`
}

type standardActionDef struct {
	Name          string                 `json:"name"`
	Label         string                 `json:"label"`
	Type          string                 `json:"type"`
	Icon          string                 `json:"icon"`
	ObjectAPIName string                 `json:"objectApiName,omitempty"`
	AppliesTo     []string               `json:"appliesTo,omitempty"`
	TargetObject  string                 `json:"targetObject,omitempty"`
	Config        map[string]interface{} `json:"config,omitempty"`
}

// StandardActionID produces the deterministic `_System_Action.Id` used
// for platform-seeded actions on a given schema. Shared with
// controlplane/tenants packages so overlay writes target the same
// canonical row the platform pack seeds.
func StandardActionID(objectAPIName, actionName string) string {
	return fmt.Sprintf("act_%s_%s", strings.ToLower(objectAPIName), strings.ToLower(actionName))
}

// appliesToSchema returns true when the action should be seeded onto the given schema.
// Precedence: ObjectAPIName (single-object) > AppliesTo (allowlist) > all schemas.
func (a standardActionDef) appliesToSchema(schemaAPIName string) bool {
	if a.ObjectAPIName != "" {
		return strings.EqualFold(a.ObjectAPIName, schemaAPIName)
	}
	if len(a.AppliesTo) == 0 {
		return true
	}
	for _, name := range a.AppliesTo {
		if strings.EqualFold(name, schemaAPIName) {
			return true
		}
	}
	return false
}

// ---- Pack ----

// PlatformUXPack manages platform-owned UX/UI metadata: themes, UI components,
// setup pages, standard actions, flows, and layout migration + backfill.
type PlatformUXPack struct{}

const standardPageLayoutSource = "standard_bootstrap"

func (p *PlatformUXPack) Name() string       { return "platform-ux" }
func (p *PlatformUXPack) Category() Category { return PlatformUX }

func (p *PlatformUXPack) Apply(ctx context.Context, deps ApplyDeps) error {
	log.Println("📦 Applying platform-ux pack...")

	// Layout migration + related list backfill
	applyLayoutMigration(ctx, deps)

	// Curated User page layout (issue #656). Runs after applyLayoutMigration so
	// the curated row supersedes whatever the auto-generator persisted.
	if err := applyUserPageLayout(ctx, deps.DB); err != nil {
		return fmt.Errorf("user page layout: %w", err)
	}
	if err := applyOpportunityPageLayout(ctx, deps.DB); err != nil {
		return fmt.Errorf("opportunity page layout: %w", err)
	}
	if err := applyStandardPageLayouts(ctx, deps.DB); err != nil {
		return fmt.Errorf("standard page layouts: %w", err)
	}

	// Standard actions
	if err := applyStandardActions(ctx, deps.Metadata, deps.DB); err != nil {
		return fmt.Errorf("standard actions: %w", err)
	}

	// Themes
	if err := applyThemes(ctx, deps.Metadata); err != nil {
		return fmt.Errorf("themes: %w", err)
	}

	// UI components
	if err := applyUIComponents(ctx, deps.Metadata); err != nil {
		return fmt.Errorf("UI components: %w", err)
	}

	// Setup pages
	if err := applySetupPages(ctx, deps.Metadata); err != nil {
		return fmt.Errorf("setup pages: %w", err)
	}

	// Flows
	if err := applyFlows(ctx, deps.Metadata); err != nil {
		return fmt.Errorf("flows: %w", err)
	}

	log.Println("✅ platform-ux pack applied")
	return nil
}

func (p *PlatformUXPack) Verify(_ context.Context, deps VerifyDeps) error {
	var errs []string

	// 1. Standard actions: every action defined in standard_actions.json must
	//    have at least one row in _System_Action. We check by distinct Name.
	if err := verifyStandardActions(deps.DB); err != nil {
		errs = append(errs, err.Error())
	}

	// 2. Themes
	if err := verifyMinRows(deps.DB, constants.TableTheme, "themes.json", len(parseThemes())); err != nil {
		errs = append(errs, err.Error())
	}

	// 3. UI components
	if err := verifyMinRows(deps.DB, constants.TableUIComponent, "ui_components.json", len(parseUIComponents())); err != nil {
		errs = append(errs, err.Error())
	}

	// 4. Setup pages
	if err := verifyMinRows(deps.DB, constants.TableSetupPage, "setup_pages.json", len(parseSetupPages())); err != nil {
		errs = append(errs, err.Error())
	}

	// 5. Curated User page layout (issue #656)
	if err := verifyCuratedUserLayout(deps.DB); err != nil {
		errs = append(errs, err.Error())
	}
	if err := verifyCuratedOpportunityLayout(deps.DB); err != nil {
		errs = append(errs, err.Error())
	}

	// Flows are excluded: they are user-created data (not platform-seeded),
	// and their backfill is best-effort — missing trigger info is not fatal.

	if len(errs) > 0 {
		return fmt.Errorf("platform-ux verify failed:\n  - %s", strings.Join(errs, "\n  - "))
	}
	return nil
}

func verifyStandardActions(db *sql.DB) error {
	var actions []standardActionDef
	if err := json.Unmarshal(standardActionsJSON, &actions); err != nil {
		return fmt.Errorf("parse standard_actions.json: %w", err)
	}

	expectedNames := make([]string, 0, len(actions))
	for _, a := range actions {
		expectedNames = append(expectedNames, a.Name)
	}

	placeholders := make([]string, len(expectedNames))
	args := make([]interface{}, len(expectedNames))
	for i, n := range expectedNames {
		placeholders[i] = "?"
		args[i] = n
	}

	query := fmt.Sprintf(
		"SELECT DISTINCT `%s` FROM `%s` WHERE `%s` IN (%s)",
		constants.FieldSysAction_Name, constants.TableAction,
		constants.FieldSysAction_Name, strings.Join(placeholders, ","),
	)
	rows, err := db.Query(query, args...)
	if err != nil {
		return fmt.Errorf("query _System_Action: %w", err)
	}
	defer rows.Close()

	found := make(map[string]bool)
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return fmt.Errorf("scan action name: %w", err)
		}
		found[name] = true
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate action rows: %w", err)
	}

	var missing []string
	for _, n := range expectedNames {
		if !found[n] {
			missing = append(missing, n)
		}
	}
	if len(missing) > 0 {
		return fmt.Errorf("standard actions missing from %s: %s (run 'go run ./cmd/setup apply' to seed)",
			constants.TableAction, strings.Join(missing, ", "))
	}
	return nil
}

func verifyMinRows(db *sql.DB, table, source string, expected int) error {
	var count int
	query := fmt.Sprintf("SELECT COUNT(*) FROM `%s`", table)
	if err := db.QueryRow(query).Scan(&count); err != nil {
		return fmt.Errorf("count %s: %w", table, err)
	}
	if count < expected {
		return fmt.Errorf("%s has %d rows, expected >= %d from %s (run 'go run ./cmd/setup apply' to seed)",
			table, count, expected, source)
	}
	return nil
}

func parseThemes() []models.Theme {
	var data themesData
	if err := json.Unmarshal(themesJSON, &data); err != nil {
		panic("corrupt embedded themes.json: " + err.Error())
	}
	return data.Themes
}

func parseUIComponents() []models.UIComponent {
	var data uiComponentsData
	if err := json.Unmarshal(uiComponentsJSON, &data); err != nil {
		panic("corrupt embedded ui_components.json: " + err.Error())
	}
	return data.Components
}

func parseSetupPages() []models.SetupPage {
	var pages []models.SetupPage
	if err := json.Unmarshal(setupPagesJSON, &pages); err != nil {
		panic("corrupt embedded setup_pages.json: " + err.Error())
	}
	return pages
}

// ---- Implementation ----

func applyLayoutMigration(_ context.Context, deps ApplyDeps) {
	// Legacy layout-to-assignment cutover is operator-gated through data-specs.
	// Ordinary pack apply only runs non-destructive layout maintenance.
	if err := BackfillLayoutRelatedLists(deps.DB); err != nil {
		log.Printf("   ⚠️  Related list backfill failed: %v", err)
	}
}

func applyStandardPageLayouts(ctx context.Context, db *sql.DB) error {
	layouts, err := bootstrap.LoadStandardPageLayouts()
	if err != nil {
		return err
	}
	for i := range layouts {
		if err := applyStandardPageLayout(ctx, db, layouts[i]); err != nil {
			return err
		}
	}
	return nil
}

func applyStandardPageLayout(ctx context.Context, db *sql.DB, layout models.PageLayout) error {
	if err := HydrateLayoutRelatedListsFromMetadata(db, &layout); err != nil {
		return fmt.Errorf("hydrate %s standard layout related lists: %w", layout.ObjectAPIName, err)
	}
	layout.Source = standardPageLayoutSource
	layout.GenerationVersion = 0
	layout.IsCustomized = false
	layout.LayoutVersion = 0

	currentRow, err := layoutownership.LoadPageLayoutRow(ctx, db, layout.ID)
	if err != nil {
		return fmt.Errorf("query standard %s layout %q: %w", layout.ObjectAPIName, layout.ID, err)
	}
	if currentRow != nil && layoutownership.IsAdminOwnedPageLayout(currentRow.IsCustomized, currentRow.Source) {
		log.Printf("   ⚠️  standard %s layout %q is admin-owned; preserving row", layout.ObjectAPIName, layout.ID)
		return nil
	}
	if currentRow != nil && layoutownership.IsTenantOverlayManagedPageLayout(currentRow.IsCustomized, currentRow.Source) {
		log.Printf("   ⚠️  standard %s layout %q is tenant-overlay managed; preserving row", layout.ObjectAPIName, layout.ID)
		return nil
	}
	if err := layoutownership.UpsertManagedPageLayout(ctx, db, layout, layout.Source); err != nil {
		return fmt.Errorf("upsert standard %s layout %q: %w", layout.ObjectAPIName, layout.ID, err)
	}
	return nil
}

func applyThemes(ctx context.Context, metaSvc *metadata.MetadataService) error {
	log.Println("🔧 Initializing themes...")
	var data themesData
	if err := json.Unmarshal(themesJSON, &data); err != nil {
		return apperrors.NewInternalError("failed to parse themes.json", err)
	}
	for _, theme := range data.Themes {
		if err := metaSvc.UpsertTheme(ctx, &theme); err != nil {
			log.Printf("   ⚠️  Failed to ensure theme %s: %v", theme.Name, err)
		} else {
			log.Printf("   ✅ %s theme ensured", theme.Name)
		}
	}
	return nil
}

func applyUIComponents(ctx context.Context, metaSvc *metadata.MetadataService) error {
	log.Println("🔧 Initializing UI components...")
	var data uiComponentsData
	if err := json.Unmarshal(uiComponentsJSON, &data); err != nil {
		return apperrors.NewInternalError("failed to parse ui_components.json", err)
	}
	for _, comp := range data.Components {
		if comp.Description != nil && *comp.Description == "" {
			comp.Description = nil
		}
		if err := metaSvc.UpsertUIComponent(ctx, &comp); err != nil {
			log.Printf("   ⚠️  Failed to ensure component %s: %v", comp.Name, err)
		}
	}
	log.Printf("   ✅ Ensure %d UI components", len(data.Components))
	return nil
}

func applySetupPages(ctx context.Context, metaSvc *metadata.MetadataService) error {
	log.Println("🔧 Initializing setup pages...")
	var pages []models.SetupPage
	if err := json.Unmarshal(setupPagesJSON, &pages); err != nil {
		return apperrors.NewInternalError("failed to parse setup_pages.json", err)
	}
	for _, page := range pages {
		if err := metaSvc.UpsertSetupPage(ctx, &page); err != nil {
			log.Printf("   ⚠️  Failed to ensure setup page %s: %v", page.Label, err)
		}
	}
	log.Printf("   ✅ Ensure %d setup pages", len(pages))
	return nil
}

// validateStandardActionFieldMappings checks a CreateRecord action's
// Config.workflow.fieldMappings against live metadata. Returns a list
// of human-readable warnings; empty when the mapping is clean.
// Non-CreateRecord actions return nil immediately.
//
// workflow.childCopy is NOT validated here. Phase 2 retired that surface;
// CheckStrictActionChildCopyKeys (which wraps actionvalidate.StrictChildCopyInConfig)
// hard-rejects any non-empty workflow.childCopy at pack-apply time, so
// a shape-validation pass would be either redundant (the content never
// lands) or misleading (suggesting the shape matters when the whole
// subtree is dead).
//
// Pure-ish: takes only the action and a metadata lookup. The metadata service
// is passed as an interface so tests can stub it without spinning a full
// MetadataService.
func validateStandardActionFieldMappings(ctx context.Context, action standardActionDef, schema schemaLookup) []string {
	if action.Type != constants.ActionTypeCreateRecord {
		return nil
	}
	if action.Config == nil {
		return nil
	}
	workflowRaw, ok := action.Config["workflow"].(map[string]interface{})
	if !ok {
		return nil
	}

	var warnings []string
	sourceObject := action.ObjectAPIName
	targetObject := action.TargetObject

	sourceSchema := schema.GetSchema(ctx, sourceObject)
	targetSchema := schema.GetSchema(ctx, targetObject)
	if sourceObject != "" && sourceSchema == nil {
		warnings = append(warnings, fmt.Sprintf("action %q: sourceObject %q not found in metadata", action.Name, sourceObject))
	}
	if targetObject != "" && targetSchema == nil {
		warnings = append(warnings, fmt.Sprintf("action %q: targetObject %q not found in metadata", action.Name, targetObject))
	}

	// Top-level fieldMappings: key = target field on TargetObject, value = source
	// field on SourceObject (string form) or {"sourceField": "...", "defaultValue": ...}.
	if mappings, ok := workflowRaw["fieldMappings"].(map[string]interface{}); ok {
		for targetField, raw := range mappings {
			srcField := extractSourceField(raw)
			if srcField == "" {
				continue
			}
			warnings = append(warnings, validateMappedSourceField(ctx, action.Name, fmt.Sprintf("fieldMappings[%q]", targetField), srcField, sourceObject, sourceSchema, schema)...)
			if targetSchema != nil && !metadataFieldExists(targetSchema, targetField) {
				warnings = append(warnings, fmt.Sprintf("action %q: fieldMappings[%q] references unknown target field on %q",
					action.Name, targetField, targetObject))
			}
		}
	}

	return warnings
}

// CheckStrictActionChildCopyKeys enforces that action-level
// workflow.childCopy entries do NOT carry sourceIdField or hydrateSource,
// applied across every action in a standard pack seed.
//
// The underlying primitive lives in backend/pkg/actionvalidate so the
// admin/API writer path (MetadataService.CreateAction / UpdateAction)
// can call the same check without a circular import — see that package
// for the full rationale.
func CheckStrictActionChildCopyKeys(actions []standardActionDef) error {
	for _, action := range actions {
		if action.Type != constants.ActionTypeCreateRecord {
			continue
		}
		if err := actionvalidate.StrictChildCopyInConfig(action.Name, action.Config); err != nil {
			return err
		}
	}
	return nil
}

// CheckStrictChildCopyInConfig is kept as a thin wrapper around
// actionvalidate.StrictChildCopyInConfig for callers (tenant overlays,
// existing tests) that already reference the packs-package name.
// Prefer actionvalidate.StrictChildCopyInConfig for new code.
func CheckStrictChildCopyInConfig(actionName string, config map[string]interface{}) error {
	return actionvalidate.StrictChildCopyInConfig(actionName, config)
}

func validateMappedSourceField(
	ctx context.Context,
	actionName string,
	mappingLabel string,
	srcField string,
	sourceObject string,
	sourceSchema *models.ObjectMetadata,
	schema schemaLookup,
) []string {
	if relatedObject, relatedField, ok := parseOneHopActionSourcePath(srcField); ok {
		var warnings []string
		lookupField := relatedObject + "Id"
		if sourceSchema != nil && !metadataFieldExists(sourceSchema, lookupField) {
			warnings = append(warnings, fmt.Sprintf("action %q: %s references unknown relationship lookup field %q on %q",
				actionName, mappingLabel, lookupField, sourceObject))
		}
		relatedSchema := schema.GetSchema(ctx, relatedObject)
		if relatedSchema == nil {
			warnings = append(warnings, fmt.Sprintf("action %q: %s references unknown related object %q",
				actionName, mappingLabel, relatedObject))
			return warnings
		}
		if !metadataFieldExists(relatedSchema, relatedField) {
			warnings = append(warnings, fmt.Sprintf("action %q: %s references unknown related source field %q on %q",
				actionName, mappingLabel, relatedField, relatedObject))
		}
		return warnings
	}
	if strings.Contains(srcField, ".") {
		return []string{fmt.Sprintf("action %q: %s references unsupported source path %q; only one-hop Object.Field paths are supported",
			actionName, mappingLabel, srcField)}
	}
	if sourceSchema != nil && !metadataFieldExists(sourceSchema, srcField) {
		return []string{fmt.Sprintf("action %q: %s references unknown source field %q on %q",
			actionName, mappingLabel, srcField, sourceObject)}
	}
	return nil
}

func metadataFieldExists(schema *models.ObjectMetadata, fieldAPIName string) bool {
	if schema == nil {
		return false
	}
	if schema.FieldsMap != nil {
		if _, ok := schema.FieldsMap[fieldAPIName]; ok {
			return true
		}
		if _, ok := schema.FieldsMap[strings.ToLower(fieldAPIName)]; ok {
			return true
		}
	}
	for i := range schema.Fields {
		if strings.EqualFold(schema.Fields[i].APIName, fieldAPIName) {
			return true
		}
	}
	return false
}

// schemaLookup is the subset of metadata.MetadataService that
// validateStandardActionFieldMappings needs. Exposed as an interface so
// tests can inject a fake without constructing a full metadata service.
type schemaLookup interface {
	GetSchema(ctx context.Context, apiName string) *models.ObjectMetadata
}

// extractSourceField pulls the source-field name out of either the string
// shorthand ("SourceField") or the object form ({"sourceField": "..."}).
func extractSourceField(raw interface{}) string {
	switch v := raw.(type) {
	case string:
		return v
	case map[string]interface{}:
		s, _ := v["sourceField"].(string)
		return s
	}
	return ""
}

func parseOneHopActionSourcePath(mapping string) (string, string, bool) {
	parts := strings.Split(mapping, ".")
	if len(parts) != 2 {
		return "", "", false
	}
	objectAPIName := strings.TrimSpace(parts[0])
	fieldAPIName := strings.TrimSpace(parts[1])
	if objectAPIName == "" || fieldAPIName == "" {
		return "", "", false
	}
	if strings.Contains(objectAPIName, ".") || strings.Contains(fieldAPIName, ".") {
		return "", "", false
	}
	return objectAPIName, fieldAPIName, true
}

// buildStandardActionsUpsertSQL assembles the INSERT ... ON DUPLICATE KEY UPDATE
// statement for the bootstrap Action rows. Extracted so the UPSERT shape can be
// asserted in unit tests without needing a live DB or the metadata service.
// Note: IsDeleted is intentionally omitted from the UPDATE list so admin
// soft-deletes of bootstrap rows persist across restarts.
func buildStandardActionsUpsertSQL(valueParts []string) string {
	return fmt.Sprintf(
		"INSERT INTO `%s` (`%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`) VALUES %s "+
			"ON DUPLICATE KEY UPDATE `%s` = VALUES(`%s`), `%s` = VALUES(`%s`), `%s` = VALUES(`%s`), `%s` = VALUES(`%s`), `%s` = VALUES(`%s`), `%s` = VALUES(`%s`)",
		constants.TableAction,
		constants.FieldSysAction_ID, constants.FieldSysAction_ObjectAPIName,
		constants.FieldSysAction_Name, constants.FieldSysAction_Label,
		constants.FieldSysAction_Type, constants.FieldSysAction_Icon,
		constants.FieldSysAction_TargetObject, constants.FieldSysAction_Config,
		constants.FieldCreatedDate, constants.FieldLastModifiedDate,
		strings.Join(valueParts, ", "),
		constants.FieldSysAction_Label, constants.FieldSysAction_Label,
		constants.FieldSysAction_Type, constants.FieldSysAction_Type,
		constants.FieldSysAction_Icon, constants.FieldSysAction_Icon,
		constants.FieldSysAction_TargetObject, constants.FieldSysAction_TargetObject,
		constants.FieldSysAction_Config, constants.FieldSysAction_Config,
		constants.FieldLastModifiedDate, constants.FieldLastModifiedDate,
	)
}

func applyStandardActions(ctx context.Context, metaSvc *metadata.MetadataService, rawDB *sql.DB) error {
	log.Println("🔧 Initializing standard actions...")
	start := time.Now()

	var actions []standardActionDef
	if err := json.Unmarshal(standardActionsJSON, &actions); err != nil {
		return apperrors.NewInternalError("failed to parse standard_actions.json", err)
	}

	// Standard actions only. Tenant-specific action content (fullActions
	// + overlays) is applied separately by `cmd/setup apply-tenant`
	// against controlplane/tenants/<tenant>/actions.json, layered on top
	// of the rows this function writes. Platform binary ships zero
	// tenant identifiers (issue #1609).

	schemas := metaSvc.GetSchemas(ctx)
	log.Printf("   ...Ensuring %d standard actions for %d objects...", len(actions), len(schemas))

	// Validate CreateRecord field mappings at pack-apply time. Warn-level so
	// an in-place upgrade with an unexpectedly thin custom schema does not
	// fail to boot; warnings surface the bad mapping in startup logs so it
	// can be fixed before users click the action. Strict-fail is a future
	// tightening once production schemas are verified clean.
	for _, action := range actions {
		for _, warning := range validateStandardActionFieldMappings(ctx, action, metaSvc) {
			log.Printf("   ⚠️  %s", warning)
		}
	}

	// Hard-reject the two object-level-only keys on the action-level
	// surface. Closes the Class-1 contract drift the reviewer flagged
	// — warn-only would keep the soft contract where metadata lands
	// and runtime silently drops. See CheckStrictActionChildCopyKeys.
	if err := CheckStrictActionChildCopyKeys(actions); err != nil {
		return fmt.Errorf("action-level workflow.childCopy: %w", err)
	}

	var valueParts []string
	var params []interface{}
	now := time.Now().Format("2006-01-02 15:04:05")

	for _, schema := range schemas {
		for _, action := range actions {
			if !action.appliesToSchema(schema.APIName) {
				continue
			}
			configJSON, _ := json.Marshal(action.Config)
			id := StandardActionID(schema.APIName, action.Name)
			var targetObj interface{} = nil
			if action.TargetObject != "" {
				targetObj = action.TargetObject
			}
			valueParts = append(valueParts, "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
			params = append(params, id, schema.APIName, action.Name, action.Label,
				action.Type, action.Icon, targetObj, string(configJSON), now, now)
		}
	}

	if len(valueParts) == 0 {
		log.Println("   ✅ No actions to create")
		return nil
	}

	// UPSERT rather than INSERT IGNORE so shipped config edits
	// (Label, Type, Icon, TargetObject, Config) propagate to existing rows on
	// restart. Without this, new fields like `config.workflow` on
	// `act_opportunity_createquote` would only land on fresh databases, and
	// environments upgraded in-place would keep the stale config and silently
	// no-op the CreateRecord workflow. IsDeleted is intentionally NOT reset —
	// an admin soft-delete of a bootstrap action must persist across restarts.
	// To re-enable a soft-deleted bootstrap action, operators must flip
	// IsDeleted = 0 explicitly. Bootstrap ownership is scoped by the Id
	// prefix `act_` assembled above; user-created Actions never collide.
	chunkSize := 500
	var firstErr error
	for i := 0; i < len(valueParts); i += chunkSize {
		end := i + chunkSize
		if end > len(valueParts) {
			end = len(valueParts)
		}
		chunk := valueParts[i:end]
		chunkParams := params[i*10 : end*10]

		insertSQL := buildStandardActionsUpsertSQL(chunk)

		if _, err := rawDB.Exec(insertSQL, chunkParams...); err != nil {
			log.Printf("   ⚠️  Batch upsert failed for chunk %d-%d: %v", i, end, err)
			if firstErr == nil {
				firstErr = err
			}
			continue
		}
	}

	log.Printf("   ✅ Standard actions ensured: %d total (%v)",
		len(valueParts), time.Since(start).Round(time.Millisecond))
	if firstErr != nil {
		return apperrors.NewInternalError("some action chunks failed to insert", firstErr)
	}
	return nil
}

func applyFlows(ctx context.Context, metaSvc *metadata.MetadataService) error {
	// Backfill trigger info
	backfillStart := time.Now()
	backfillFlowTriggerInfo(ctx, metaSvc)
	zlog.Info().Dur("elapsed", time.Since(backfillStart)).Msg("   ⏱  backfillFlowTriggerInfo")

	// Refresh flow cache
	cacheStart := time.Now()
	flows := metaSvc.GetFlowDefinitions(ctx)
	zlog.Info().Dur("elapsed", time.Since(cacheStart)).Int("count", len(flows)).Msg("📋 Flow definitions loaded into cache")
	return nil
}

func backfillFlowTriggerInfo(ctx context.Context, metaSvc *metadata.MetadataService) {
	loadStart := time.Now()
	flows := metaSvc.GetFlowDefinitions(ctx)
	zlog.Info().Dur("elapsed", time.Since(loadStart)).Int("count", len(flows)).
		Msg("   ⏱  backfill: GetFlowDefinitions")

	var needsBackfill []int
	for i, def := range flows {
		if def.ActiveVersionId != "" && (def.TriggerType == "" || def.TriggerObjectOrEvent == "") {
			needsBackfill = append(needsBackfill, i)
		}
	}
	zlog.Info().Int("total", len(flows)).Int("needsBackfill", len(needsBackfill)).
		Msg("   ⏱  backfill: flow scan complete")
	if len(needsBackfill) == 0 {
		return
	}

	versionStart := time.Now()
	versionIDs := make([]string, 0, len(needsBackfill))
	for _, idx := range needsBackfill {
		versionIDs = append(versionIDs, flows[idx].ActiveVersionId)
	}
	versions := metaSvc.GetFlowVersionsByIDs(ctx, versionIDs)
	zlog.Info().Dur("elapsed", time.Since(versionStart)).Int("loaded", len(versions)).
		Msg("   ⏱  backfill: BulkGetFlowVersions")

	backfilled := 0
	for _, idx := range needsBackfill {
		def := flows[idx]
		version := versions[def.ActiveVersionId]
		if version == nil {
			continue
		}

		var triggerType, objectOrEvent string
		if version.Metadata.Start != nil {
			triggerType = version.Metadata.Start.TriggerType
			objectOrEvent = version.Metadata.Start.Object
		}

		// Fallback for Process Builder / Workflow flows (ProcessType=Workflow).
		// These flows have no Start element; instead the trigger object is stored
		// as the ObjectType of the "myVariable_current" SObject variable.
		// Salesforce Process Builder always fires after-save, so we use
		// "RecordAfterSave" as the TriggerType sentinel understood by the listener.
		// Only attempt the myVariable_current heuristic for Workflow flows — other
		// ProcessTypes (AutoLaunchedFlow, etc.) may coincidentally carry a variable
		// with that name without it encoding a record trigger.
		if triggerType == "" && objectOrEvent == "" && def.ProcessType == "Workflow" {
			objectOrEvent = workflowTriggerObject(version.Metadata.Variables)
			if objectOrEvent != "" {
				triggerType = "RecordAfterSave"
			}
		}

		if triggerType == "" && objectOrEvent == "" {
			continue
		}
		metaSvc.BackfillTriggerInfo(ctx, def.Id, triggerType, objectOrEvent)
		backfilled++
	}

	zlog.Info().Int("backfilled", backfilled).Int("needsBackfill", len(needsBackfill)).
		Msg("   ⏱  backfill: complete")
}

// workflowTriggerObject extracts the trigger object API name from the flow
// variables of a Process Builder (ProcessType=Workflow) flow. These flows do
// not have a Start element; instead Salesforce stores the triggering SObject
// type as the ObjectType of the implicit "myVariable_current" variable.
// Returns an empty string when no SObject variable named "myVariable_current"
// is found (i.e. the flow is not a Process Builder flow or has no trigger).
func workflowTriggerObject(variables []models.FlowVariable) string {
	for _, v := range variables {
		if strings.EqualFold(v.Name, "myVariable_current") && v.DataType == "SObject" && v.ObjectType != "" {
			return v.ObjectType
		}
	}
	return ""
}