package packs

import (
	"crypto/sha1"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"io/fs"
	"log"
	"strings"
	"time"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	kernelseedrows "github.com/nexuscrm/kernel/pkg/seedrows"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

const (
	tableStandardAction                    = "_System_StandardAction"
	tableActionSchema                      = "_System_ActionSchema"
	tableActionFieldDefault                = "_System_ActionFieldDefault"
	tableActionInputGuard                  = "_System_ActionInputGuard"
	tableStandardActionInternalAccessScope = "_System_StandardActionInternalAccessScope"

	// standardActionsPackStateKey identifies the BootstrapState row that
	// caches the sha256 of the last-successful standard_actions seed payload.
	// Boot skips the seed + reconcile pass when the shipped pack plus seeder
	// version is byte-identical to the stamped hash (fast-path for warm installs).
	standardActionsPackStateKey = "standard_actions_pack"
	// standardActionsPersistenceStateKey is the full-ApplyAll receipt for the
	// seeder semantics shared by ownerless and source-backed action packs. The
	// completed-marker startup path checks this receipt before it may skip: a
	// seeder-version bump must replay every installed action pack even when its
	// authored bytes and installed payload hash did not change.
	standardActionsPersistenceStateKey = "standard_actions_persistence"
	// Include the seeder contract version in the fast-path hash. Some fixes
	// change how pack rows are written without changing standard_actions.json
	// bytes; bumping this salt forces one reconcile pass on warm installs.
	standardActionsPackSeedVersion = "v9_action_schema_collection"
)

// ParseStandardActionDefs decodes the authored standard-action pack payload.
//
// Relocated verbatim to the kernel module's seedrows package (Wave 0) — it
// needs apperrors so it cannot live in the stdlib-only contract module. This
// alias keeps existing importers compiling unchanged.
var ParseStandardActionDefs = kernelseedrows.ParseStandardActionDefs

func standardActionHeaderFallbackEligible(def StandardActionDef) bool {
	if def.HeaderFallbackEligible == nil {
		return true
	}
	return *def.HeaderFallbackEligible
}

func ValidateStandardActionDefs(defs []StandardActionDef) error {
	for _, def := range defs {
		if err := validateStandardActionDef(def); err != nil {
			return err
		}
		if len(def.CustomPermissions) != 0 {
			return apperrors.NewValidationError(
				"customPermissions",
				fmt.Sprintf(
					"standard action %q must declare custom permissions as security-phase custom_permission components; inline custom permission %q is unsupported",
					def.Name,
					def.CustomPermissions[0].APIName,
				),
			)
		}
	}
	return nil
}

// standardActionRowID returns the deterministic synthetic Id for a
// _System_StandardAction row. Stable across seed runs so ON DUPLICATE KEY
// UPDATE can reconcile. (Renamed from the bootstrap-resident StandardActionID
// to avoid colliding with the existing packs StandardActionID, which derives a
// different act_<object>_<action> id for source-backed object actions.)
func standardActionRowID(name string) string {
	return "sa_" + strings.ToLower(name)
}

func actionSchemaID(actionID, direction, apiName string) string {
	return fmt.Sprintf("as_%s_%s_%s", strings.TrimPrefix(actionID, "sa_"), direction, strings.ToLower(apiName))
}

func actionFieldDefaultID(actionID, targetObject, fieldAPIName string, priority int) string {
	return fmt.Sprintf("afd_%s_%s_%s_%d", strings.TrimPrefix(actionID, "sa_"), strings.ToLower(targetObject), strings.ToLower(fieldAPIName), priority)
}

// actionInputGuardID derives a stable Id from the row's logical unique key
// (StandardActionId, Condition, GuardKey) — matching the `uq_aig_rule`
// index declared in system_tables.json.
//
// The prior shape keyed by row position in the pack (index), which broke
// idempotency the moment the pack reordered guards: reordering produced a
// new synthetic Id for a row that was unchanged by its unique key, and
// the ON DUPLICATE KEY UPDATE on `uq_aig_rule` then masked the mistake by
// updating the pre-existing row — but the stale Id-keyed row was never
// reconciled. The sha1 of "condition|guardKey" is short,
// deterministic, and safe to embed in an Id column (bounded length,
// lowercase hex).
func actionInputGuardID(actionID, condition, guardKey string) string {
	sum := sha1.Sum([]byte(condition + "|" + guardKey))
	return fmt.Sprintf("aig_%s_%s",
		strings.TrimPrefix(actionID, "sa_"),
		hex.EncodeToString(sum[:8]))
}

func actionInputGuardKey(row StandardActionGuardDef) string {
	if strings.TrimSpace(row.RequiredPermission) != "" {
		return "permission:" + strings.TrimSpace(row.RequiredPermission)
	}
	return "object:" + strings.TrimSpace(row.RequiredObject) + ":" + strings.TrimSpace(row.RequiredOperation)
}

func actionInternalAccessScopeID(actionID, objectAPIName, operation string) string {
	return fmt.Sprintf("saias_%s_%s_%s",
		strings.TrimPrefix(actionID, "sa_"),
		strings.ToLower(strings.TrimSpace(objectAPIName)),
		strings.ToLower(strings.TrimSpace(operation)))
}

// InitializeStandardActionsForOwnerFromPayload seeds the _System_StandardAction
// registry and its satellite tables from the caller-owned payload. Custom
// permissions are security-phase custom_permission components and are never
// created or reconciled by this automation-phase component.
// Idempotent — re-running:
//  1. Upserts every row in the shipped pack. IsCustomized=1 rows keep their
//     admin-edited values; IsCustomized=0 rows are refreshed to the pack.
//  2. Reconciles each satellite table against the pack for every seeded
//     action: live IsCustomized=0 rows absent from customizable satellites are
//     soft-deleted (IsActive=0, IsDeleted=1), while IsCustomized=1 orphans are
//     preserved. Internal access scopes are L1-only, so every non-pack row in
//     that table is soft-deleted regardless of IsCustomized.
//  3. Re-activates rows the pack re-introduces after a prior removal
//     (IsDeleted=1 → IsDeleted=0 on the same unique key) via the new
//     IsDeleted branch in each ON DUPLICATE KEY UPDATE clause.
func InitializeStandardActionsForOwnerFromPayload(rawDB *sql.DB, ownerPack string, raw []byte, flowFS fs.FS, flowRoot string) error {
	flowSeeds, err := loadStandardFlowSeedsFromFS(flowFS, flowRoot)
	if err != nil {
		return err
	}
	return initializeStandardActionsForOwner(rawDB, ownerPack, raw, flowSeeds)
}

func InitializeStandardActionsForOwnerFromPayloads(rawDB *sql.DB, ownerPack string, raw []byte, flowPayloads map[string][]byte) error {
	flowSeeds, err := LoadStandardFlowSeedsFromPayloads(flowPayloads)
	if err != nil {
		return err
	}
	return initializeStandardActionsForOwner(rawDB, ownerPack, raw, flowSeeds)
}

func initializeStandardActionsForOwner(rawDB *sql.DB, ownerPack string, raw []byte, flowSeeds []standardFlowSeed) error {
	log.Println("🔧 Initializing standard actions...")
	start := time.Now()

	defs, err := ParseStandardActionDefs(raw)
	if err != nil {
		return err
	}

	if len(defs) == 0 {
		log.Println("   ↩ No standard actions to seed")
		return nil
	}

	// Validate the whole pack up-front so a shape error short-circuits
	// before we touch the DB. Keeps the validation-error path hermetic
	// (no sqlmock expectations needed in tests) and fails fast for ops.
	if err := ValidateStandardActionDefs(defs); err != nil {
		return err
	}
	if err := claimStandardActionBusinessKeys(rawDB, defs, ownerPack); err != nil {
		return err
	}
	if err := validateStandardActionFlowBindings(defs, flowSeeds); err != nil {
		return err
	}

	now := time.Now().Format("2006-01-02 15:04:05")

	if ownerPack == "" {
		// Legacy ownerless bootstrap fast-path. App-pack apply must always run
		// reconcile because _System_InstalledPack is the app-pack SSoT and
		// setup apply-packs is expected to repair live drift even when the
		// shipped payload bytes did not change.
		packHash := standardActionsPackHashFromPayload(raw)
		applied, err := isPackHashApplied(rawDB, standardActionsPackStateKey, packHash)
		if err != nil {
			return err
		}
		if applied {
			log.Printf("   ↩ standard_actions.json unchanged since last successful seed (sha256=%s); skipping reconcile", packHash[:12])
			return nil
		}
	}

	for _, def := range defs {
		if err := upsertStandardAction(rawDB, def, now, ownerPack); err != nil {
			return err
		}
	}

	// Parent-table orphan reconcile.
	//
	// Must run AFTER all upserts so `keep` reflects the persisted set. We
	// build `keep` from real row Ids (SELECT by Name per def), NOT from
	// StandardActionID(def.Name). Reason: _System_StandardAction's unique
	// key is Name (uq_sa_name), and INSERT ... ON DUPLICATE KEY UPDATE
	// preserves a pre-existing row's Id when Name collides. A legacy /
	// migrated / manually-loaded row can therefore carry a non-canonical
	// Id through upsert. Re-deriving the Id here would miss that row,
	// and the reconcile would wrongly deactivate it as an orphan. This is
	// the same lesson the dispatcher learned when it stopped re-deriving
	// Ids from Name.
	keep, err := loadPersistedStandardActionIds(rawDB, defs, ownerPack)
	if err != nil {
		return err
	}
	if report, err := reconcileStandardActionRemovals(rawDB, keep, ownerPack, now); err != nil {
		return err
	} else if report.Deactivated > 0 {
		log.Printf("   ⟳ Reconciled %s: deactivated %d orphan action(s)",
			tableStandardAction, report.Deactivated)
	}

	// Cascade: a dropped parent standard-action must not leave live satellite
	// rows behind. Runs after the parent reconcile so it observes the final
	// live-parent set and retires dependent schema/field-default/guard/
	// access-scope rows that no longer resolve to a live parent — the write-
	// side dual of controlplane.VerifyActionTableConsistency.
	if cascaded, err := cascadeDeactivateOrphanedStandardActionSatellites(rawDB, now); err != nil {
		return err
	} else if cascaded > 0 {
		log.Printf("   ⟳ Reconciled standard-action satellites: deactivated %d orphan row(s)", cascaded)
	}
	if ownerPack == "" {
		// Stamp the legacy bootstrap hash ONLY on full success. Owner-scoped
		// app packs are stamped by _System_InstalledPack after pack Verify.
		if err := markPackHashApplied(rawDB, standardActionsPackStateKey, standardActionsPackHashFromPayload(raw), now); err != nil {
			return err
		}
	}

	log.Printf("✅ Standard actions initialized (%d seeded, %v)",
		len(defs), time.Since(start).Round(time.Millisecond))
	return nil
}

// reconcileSatelliteToPack is a thin wrapper around the shared
// ReconcilePackTable utility (see pack_reconcile.go). Returns just the
// deactivation count so the log line in upsertStandardAction stays compact;
// callers that want the structured ReconcileReport use ReconcilePackTable
// directly.
func reconcileSatelliteToPack(
	db *sql.DB,
	tbl PackTable,
	scopeVal string,
	keep map[string]struct{},
	now string,
) (int, error) {
	report, err := ReconcilePackTable(db, tbl, scopeVal, keep, now)
	if err != nil {
		return 0, err
	}
	return report.Deactivated, nil
}

// loadPersistedStandardActionIds returns the set of Ids actually stored in
// _System_StandardAction for the given pack defs, keyed by Name (uq_sa_name).
// Used by InitializeStandardActions to build the `keep` set for the parent
// reconcile pass from persisted row Ids rather than re-derived ones.
//
// A pack def whose Name has no live non-customized row is silently omitted
// from keep: either the row does not exist yet (nothing to preserve) or
// the row is customized (already filtered out at the reconcile level by
// the IsCustomized=0 predicate). Either way it is not an orphan candidate.
func loadPersistedStandardActionIds(db *sql.DB, defs []StandardActionDef, ownerPack string) (map[string]struct{}, error) {
	keep := make(map[string]struct{}, len(defs))
	if len(defs) == 0 {
		return keep, nil
	}

	placeholders := strings.TrimRight(strings.Repeat("?,", len(defs)), ",")
	args := make([]interface{}, 0, len(defs))
	for _, def := range defs {
		args = append(args, def.Name)
	}
	ownerPredicate := ""
	if ownerPack != "" {
		ownerPredicate = " AND OwnerPack = ?"
		args = append(args, ownerPack)
	}

	rows, err := db.Query(
		fmt.Sprintf(
			"SELECT Id FROM `%s` WHERE Name IN (%s) AND IsDeleted = 0%s",
			tableStandardAction, placeholders, ownerPredicate),
		args...,
	)
	if err != nil {
		return nil, apperrors.NewInternalError(
			"load persisted standard-action Ids for parent reconcile", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, apperrors.NewInternalError(
				"scan persisted standard-action Id", err)
		}
		keep[id] = struct{}{}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError(
			"iterate persisted standard-action Ids", err)
	}
	return keep, nil
}

func reconcileStandardActionRemovals(db *sql.DB, keep map[string]struct{}, ownerPack, now string) (ReconcileReport, error) {
	if ownerPack == "" {
		return ReconcilePackTable(db, packTableStandardAction, "", keep, now)
	}

	report := ReconcileReport{Table: tableStandardAction, Scope: ownerPack}
	rows, err := db.Query(
		fmt.Sprintf("SELECT Id FROM `%s` WHERE OwnerPack = ? AND IsCustomized = 0 AND IsDeleted = 0", tableStandardAction),
		ownerPack,
	)
	if err != nil {
		return report, apperrors.NewInternalError("reconcile standard actions: SELECT owner-scoped rows", err)
	}
	defer rows.Close()

	var orphans []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return report, apperrors.NewInternalError("reconcile standard actions: scan Id", err)
		}
		if _, ok := keep[id]; !ok {
			orphans = append(orphans, id)
		}
	}
	if err := rows.Err(); err != nil {
		return report, apperrors.NewInternalError("reconcile standard actions: iterate rows", err)
	}
	if len(orphans) == 0 {
		return report, nil
	}

	placeholders := strings.TrimRight(strings.Repeat("?,", len(orphans)), ",")
	args := make([]interface{}, 0, len(orphans)+1)
	args = append(args, now)
	for _, id := range orphans {
		args = append(args, id)
	}
	if _, err := db.Exec(
		fmt.Sprintf("UPDATE `%s` SET IsActive = 0, IsDeleted = 1, LastModifiedDate = ? WHERE Id IN (%s)", tableStandardAction, placeholders),
		args...,
	); err != nil {
		return report, apperrors.NewInternalError("reconcile standard actions: deactivate owner-scoped orphans", err)
	}
	report.Deactivated = len(orphans)
	return report, nil
}

// standardActionSatelliteTables lists the satellite tables whose rows hang off
// a _System_StandardAction parent by StandardActionId, paired with whether the
// table has an admin-customization author. Customizable satellites preserve
// IsCustomized=1 orphans (admin-owned, per the reconcile contract) and so are
// swept only at IsCustomized=0; the L1-only internal-access-scope table has no
// admin author and is swept in full. Mirrors the PackTable descriptors in
// pack_reconcile_tables.go and the verify gate in
// controlplane/verify_action_table_consistency.go.
var standardActionSatelliteTables = []struct {
	name         string
	customizable bool
}{
	{tableActionSchema, true},
	{tableActionFieldDefault, true},
	{tableActionInputGuard, true},
	{tableStandardActionInternalAccessScope, false},
}

// cascadeDeactivateOrphanedStandardActionSatellites soft-deletes satellite rows
// whose StandardActionId no longer resolves to a PRESENT (non-deleted)
// _System_StandardAction parent. Liveness keys on existence (IsDeleted), NOT
// visibility: IsActive=0 is an admin disable-without-delete toggle (preserved
// across pack updates per upsertStandardAction), so a disabled parent still
// owns its satellites and they are left intact. Reconcile-dropped parents set
// IsActive=0 AND IsDeleted=1 together, so IsDeleted alone still catches them.
// It runs after the parent reconcile pass so
// dropping a standard action also retires its dependent rows — making the
// action-table-bridge invariant true rather than leaving orphans for
// cmd/setup verify to reject. Keyed off live parent state (not this run's
// orphan Id set), so it covers both the owner-scoped and legacy ownerless
// reconcile paths and is idempotent: a clean tree deactivates nothing.
func cascadeDeactivateOrphanedStandardActionSatellites(db *sql.DB, now string) (int, error) {
	total := 0
	for _, sat := range standardActionSatelliteTables {
		res, err := db.Exec(satelliteOrphanCascadeQuery(sat.name, sat.customizable), now)
		if err != nil {
			return total, apperrors.NewInternalError(
				fmt.Sprintf("cascade-deactivate orphaned %s rows", sat.name), err)
		}
		if n, err := res.RowsAffected(); err == nil {
			total += int(n)
		}
	}
	return total, nil
}

// satelliteOrphanCascadeQuery builds the UPDATE that soft-deletes orphaned rows
// of one satellite table. Pure (no I/O) so the IsCustomized-scoping contract is
// unit-testable: customizable satellites add an IsCustomized=0 predicate so
// admin-customized orphans are preserved; the L1-only access-scope table omits
// it so every orphan is swept. Bound to the single `now` placeholder argument.
func satelliteOrphanCascadeQuery(satName string, customizable bool) string {
	conds := []string{"COALESCE(IsDeleted, 0) = 0"}
	if customizable {
		conds = append(conds, "COALESCE(IsCustomized, 0) = 0")
	}
	conds = append(conds, fmt.Sprintf(
		"NOT EXISTS (SELECT 1 FROM `%s` a WHERE a.Id = `%s`.StandardActionId "+
			"AND COALESCE(a.IsDeleted, 0) = 0)",
		tableStandardAction, satName))
	return fmt.Sprintf("UPDATE `%s` SET IsActive = 0, IsDeleted = 1, LastModifiedDate = ? WHERE %s",
		satName, strings.Join(conds, " AND "))
}

func standardActionsPackHashFromPayload(raw []byte) string {
	return sha256Hex(StandardActionsPackPayloadFromPayload(raw))
}

func StandardActionsPersistenceSemanticsApplied(db *sql.DB) (bool, error) {
	return isPackHashApplied(db, standardActionsPersistenceStateKey, standardActionsPersistenceSemanticsHash())
}

func MarkStandardActionsPersistenceSemanticsApplied(db *sql.DB, now string) error {
	return markPackHashApplied(db, standardActionsPersistenceStateKey, standardActionsPersistenceSemanticsHash(), now)
}

func standardActionsPersistenceSemanticsHash() string {
	return sha256Hex([]byte(standardActionsPackSeedVersion))
}

func StandardActionsPackPayloadFromPayload(raw []byte) []byte {
	payload := make([]byte, 0, len(standardActionsPackSeedVersion)+1+len(raw))
	payload = append(payload, standardActionsPackSeedVersion...)
	payload = append(payload, '\n')
	payload = append(payload, raw...)
	return payload
}

// StandardActionsPackHashFromPayload returns the shipped standard-action payload hash.
// App-pack registry verification uses it as part of the owning pack hash.
func StandardActionsPackHashFromPayload(raw []byte) string {
	return standardActionsPackHashFromPayload(raw)
}

func loadPersistedStandardActionID(db *sql.DB, name string) (string, error) {
	var id string
	if err := db.QueryRow(
		fmt.Sprintf("SELECT Id FROM `%s` WHERE Name = ?", tableStandardAction),
		name,
	).Scan(&id); err != nil {
		return "", apperrors.NewInternalError(
			fmt.Sprintf("load persisted standard-action Id for %q", name), err)
	}
	return id, nil
}

func claimStandardActionBusinessKeys(db *sql.DB, defs []StandardActionDef, ownerPack string) error {
	if ownerPack == "" {
		return nil
	}
	for _, def := range defs {
		var existingID string
		var existingOwner sql.NullString
		err := db.QueryRow(
			fmt.Sprintf("SELECT Id, OwnerPack FROM `%s` WHERE Name = ? LIMIT 1", tableStandardAction),
			def.Name,
		).Scan(&existingID, &existingOwner)
		if err == sql.ErrNoRows {
			continue
		}
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("query standard action ownership %q", def.Name), err)
		}
		if existingOwner.Valid && existingOwner.String != "" && existingOwner.String != ownerPack {
			return apperrors.NewValidationError("standardAction.ownerPack",
				fmt.Sprintf("standard action %s is owned by %q (row %s), cannot claim for %q", def.Name, existingOwner.String, existingID, ownerPack))
		}
		if !existingOwner.Valid || existingOwner.String == "" {
			if _, err := db.Exec(
				fmt.Sprintf("UPDATE `%s` SET OwnerPack = ?, LastModifiedDate = CURRENT_TIMESTAMP WHERE Id = ? AND (OwnerPack = '' OR OwnerPack IS NULL)", tableStandardAction),
				ownerPack, existingID,
			); err != nil {
				return apperrors.NewInternalError(fmt.Sprintf("claim standard action %q for %s", def.Name, ownerPack), err)
			}
		}
	}
	return nil
}

// validateStandardActionDef enforces the shape rules the upsert code assumes.
// Runs up-front in InitializeStandardActions so shape errors short-circuit
// before any DB mutation.
func validateStandardActionDef(def StandardActionDef) error {
	if strings.TrimSpace(def.Name) == "" {
		return apperrors.NewValidationError("name", "standard action name is required")
	}
	if strings.TrimSpace(def.FlowDeveloperName) == "" {
		return apperrors.NewValidationError("flowDeveloperName", fmt.Sprintf("standard action %q requires flowDeveloperName", def.Name))
	}
	for i, guard := range def.InputGuards {
		if strings.TrimSpace(guard.Condition) == "" {
			return apperrors.NewValidationError("inputGuards.condition",
				fmt.Sprintf("standard action %q input guard %d requires condition", def.Name, i))
		}
		hasPermission := strings.TrimSpace(guard.RequiredPermission) != ""
		hasObject := strings.TrimSpace(guard.RequiredObject) != "" || strings.TrimSpace(guard.RequiredOperation) != ""
		if hasPermission == hasObject {
			return apperrors.NewValidationError("inputGuards",
				fmt.Sprintf("standard action %q input guard %d must declare exactly one target: requiredPermission or requiredObject+requiredOperation", def.Name, i))
		}
		if !hasObject {
			continue
		}
		if strings.TrimSpace(guard.RequiredObject) == "" || strings.TrimSpace(guard.RequiredOperation) == "" {
			return apperrors.NewValidationError("inputGuards.requiredObject",
				fmt.Sprintf("standard action %q input guard %d must declare both requiredObject and requiredOperation", def.Name, i))
		}
		switch strings.TrimSpace(guard.RequiredOperation) {
		case constants.PermRead, constants.PermCreate, constants.PermEdit, constants.PermDelete:
		default:
			return apperrors.NewValidationError("inputGuards.requiredOperation",
				fmt.Sprintf("standard action %q input guard %d has unsupported requiredOperation %q", def.Name, i, guard.RequiredOperation))
		}
	}
	for i, scope := range def.InternalAccessScopes {
		if strings.TrimSpace(scope.ObjectAPIName) == "" {
			return apperrors.NewValidationError("internalAccessScopes.objectApiName",
				fmt.Sprintf("standard action %q internal access scope %d requires objectApiName", def.Name, i))
		}
		switch strings.TrimSpace(scope.Operation) {
		case constants.PermRead, constants.PermCreate, constants.PermEdit, constants.PermDelete:
		default:
			return apperrors.NewValidationError("internalAccessScopes.operation",
				fmt.Sprintf("standard action %q internal access scope %d has unsupported operation %q", def.Name, i, scope.Operation))
		}
	}
	return nil
}

// validateStandardActionFlowBindings keeps active create-panel action inputs
// and Flow recordCreate assignments in one metadata contract. It is generic:
// any standard action can declare UIMode=create + TargetObject +
// TargetFieldAPIName; the referenced Flow must expose the input variable and
// write it to the same target object field. Missing flow packs are checked by
// TestStdConvertLead_StandardActionRowMatchesFlowPack; this validator skips
// absent flows so unit tests can seed synthetic actions without shipping a
// matching fixture.
func validateStandardActionFlowBindings(defs []StandardActionDef, seeds []standardFlowSeed) error {
	flowsByName := make(map[string]models.FlowMetadata, len(seeds))
	for _, seed := range seeds {
		var meta models.FlowMetadata
		if err := json.Unmarshal(seed.MetadataRaw, &meta); err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("parse standard flow %q metadata", seed.DeveloperName), err)
		}
		flowsByName[seed.DeveloperName] = meta
	}

	for _, def := range defs {
		flowMeta, ok := flowsByName[def.FlowDeveloperName]
		if !ok {
			continue
		}
		inputVars := map[string]struct{}{}
		for _, variable := range flowMeta.Variables {
			if variable.IsInput {
				inputVars[variable.Name] = struct{}{}
			}
		}
		for _, row := range def.Inputs {
			if strings.TrimSpace(row.UIMode) != "create" {
				continue
			}
			targetObject := strings.TrimSpace(row.TargetObject)
			targetField := strings.TrimSpace(row.TargetFieldAPIName)
			if targetObject == "" || targetField == "" {
				return apperrors.NewValidationError("inputs.targetFieldAPIName",
					fmt.Sprintf("standard action %q input %q declares UIMode=create but lacks targetObject or targetFieldAPIName", def.Name, row.APIName))
			}
			if _, ok := inputVars[row.APIName]; !ok {
				return apperrors.NewValidationError("inputs.apiName",
					fmt.Sprintf("standard action %q input %q is not an input variable on flow %q", def.Name, row.APIName, def.FlowDeveloperName))
			}
			if !flowRecordCreateConsumesInput(flowMeta.RecordCreates, targetObject, targetField, row.APIName) {
				return apperrors.NewValidationError("inputs.targetFieldAPIName",
					fmt.Sprintf("standard action %q input %q targets %s.%s but flow %q has no matching recordCreate inputAssignment", def.Name, row.APIName, targetObject, targetField, def.FlowDeveloperName))
			}
		}
		// Validate preview flow binding if declared: the referenced Flow must
		// exist in the pack's manifest-declared flow set.
		if def.PreviewFlowDeveloperName != nil && strings.TrimSpace(*def.PreviewFlowDeveloperName) != "" {
			if _, ok := flowsByName[*def.PreviewFlowDeveloperName]; !ok {
				return apperrors.NewValidationError("previewFlowDeveloperName",
					fmt.Sprintf("standard action %q references preview flow %q but it is not declared in the pack", def.Name, *def.PreviewFlowDeveloperName))
			}
		}
	}
	return nil
}

func flowRecordCreateConsumesInput(creates []models.FlowRecordCreate, targetObject, targetField, inputName string) bool {
	for _, create := range creates {
		if create.Object != targetObject {
			continue
		}
		for _, assignment := range create.InputAssignments {
			if assignment.Field != targetField {
				continue
			}
			if elementReferenceName(assignment.Value) == inputName {
				return true
			}
		}
	}
	return false
}

func elementReferenceName(value interface{}) string {
	obj, ok := value.(map[string]interface{})
	if !ok {
		return ""
	}
	ref, _ := obj["elementReference"].(string)
	return ref
}

func upsertStandardAction(db *sql.DB, def StandardActionDef, now string, ownerPack string) error {
	insertActionID := standardActionRowID(def.Name)

	reqPermsJSON := "[]"
	if len(def.RequiredPermissions) > 0 {
		b, err := json.Marshal(def.RequiredPermissions)
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("marshal requiredPermissions for %q", def.Name), err)
		}
		reqPermsJSON = string(b)
	}

	targetObjectsJSON := "[]"
	if len(def.TargetObjects) > 0 {
		b, err := json.Marshal(def.TargetObjects)
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("marshal targetObjects for %q", def.Name), err)
		}
		targetObjectsJSON = string(b)
	}

	insertSQL := fmt.Sprintf(
		`INSERT INTO %s
			(Id, Name, Label, Description, FlowDeveloperName, Category,
			 RequiredPermissions, TargetObjects, RendererId, IsAtomic, SortOrder,
			 IsCustomized, IsActive, IsDeleted, OwnerPack,
			 VisibilityCondition, PreviewFlowDeveloperName, HeaderFallbackEligible,
			 CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1, 0, ?, ?, ?, ?, ?, ?)
			ON DUPLICATE KEY UPDATE
			 Label                        = IF(IsCustomized = 0, VALUES(Label), Label),
			 Description                  = IF(IsCustomized = 0, VALUES(Description), Description),
			 FlowDeveloperName            = IF(IsCustomized = 0, VALUES(FlowDeveloperName), FlowDeveloperName),
			 Category                     = IF(IsCustomized = 0, VALUES(Category), Category),
			 RequiredPermissions          = IF(IsCustomized = 0, VALUES(RequiredPermissions), RequiredPermissions),
			 TargetObjects                = IF(IsCustomized = 0, VALUES(TargetObjects), TargetObjects),
			 RendererId                   = VALUES(RendererId),
			 IsAtomic                     = IF(IsCustomized = 0, VALUES(IsAtomic), IsAtomic),
			 SortOrder                    = IF(IsCustomized = 0, VALUES(SortOrder), SortOrder),
			 OwnerPack                    = IF((OwnerPack = '' OR OwnerPack IS NULL), VALUES(OwnerPack), OwnerPack),
			 IsActive                     = IF(IsCustomized = 0 AND IsDeleted = 1, VALUES(IsActive), IsActive),
			 IsDeleted                    = IF(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted),
			 VisibilityCondition          = IF(IsCustomized = 0, VALUES(VisibilityCondition), VisibilityCondition),
			 PreviewFlowDeveloperName     = IF(IsCustomized = 0, VALUES(PreviewFlowDeveloperName), PreviewFlowDeveloperName),
			 HeaderFallbackEligible       = IF(IsCustomized = 0, VALUES(HeaderFallbackEligible), HeaderFallbackEligible),
			 LastModifiedDate             = IF(IsCustomized = 0, VALUES(LastModifiedDate), LastModifiedDate)`,
		tableStandardAction,
	)

	if _, err := db.Exec(insertSQL,
		insertActionID, def.Name, def.Label, nullableString(def.Description),
		def.FlowDeveloperName, nullableString(def.Category),
		reqPermsJSON, targetObjectsJSON, nullableString(def.RendererID), boolToInt(def.IsAtomic), def.SortOrder,
		ownerPack, def.VisibilityCondition, previewFlowNameVal(def.PreviewFlowDeveloperName),
		boolToInt(standardActionHeaderFallbackEligible(def)), now, now,
	); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to seed standard action %q", def.Name), err)
	}
	actionID, err := loadPersistedStandardActionID(db, def.Name)
	if err != nil {
		return err
	}

	// Satellites: upsert pack rows, then reconcile the live set to the pack
	// (soft-delete IsCustomized=0 rows absent from the pack). Reconcile
	// scope is always (StandardActionId = actionID) using the persisted parent
	// Id, not StandardActionID(def.Name). Parent upsert matches by Name and
	// preserves a noncanonical Id on collisions; runtime dispatch also loads
	// satellites by that persisted Id.
	inputIds, err := upsertActionSchemaRows(db, actionID, "input", def.Inputs, now)
	if err != nil {
		return err
	}
	outputIds, err := upsertActionSchemaRows(db, actionID, "output", def.Outputs, now)
	if err != nil {
		return err
	}
	keepSchema := make(map[string]struct{}, len(inputIds)+len(outputIds))
	for _, id := range inputIds {
		keepSchema[id] = struct{}{}
	}
	for _, id := range outputIds {
		keepSchema[id] = struct{}{}
	}
	if deactivated, err := reconcileSatelliteToPack(
		db, packTableActionSchema, actionID, keepSchema, now,
	); err != nil {
		return err
	} else if deactivated > 0 {
		log.Printf("   ⟳ Reconciled %s for %s: deactivated %d orphan rows",
			tableActionSchema, actionID, deactivated)
	}

	fieldIds, err := upsertActionFieldDefaults(db, actionID, def.FieldDefaults, now)
	if err != nil {
		return err
	}
	keepFields := toIdSet(fieldIds)
	if deactivated, err := reconcileSatelliteToPack(
		db, packTableActionFieldDefault, actionID, keepFields, now,
	); err != nil {
		return err
	} else if deactivated > 0 {
		log.Printf("   ⟳ Reconciled %s for %s: deactivated %d orphan rows",
			tableActionFieldDefault, actionID, deactivated)
	}

	guardIds, err := upsertActionInputGuards(db, actionID, def.InputGuards, now)
	if err != nil {
		return err
	}
	keepGuards := toIdSet(guardIds)
	if deactivated, err := reconcileSatelliteToPack(
		db, packTableActionInputGuard, actionID, keepGuards, now,
	); err != nil {
		return err
	} else if deactivated > 0 {
		log.Printf("   ⟳ Reconciled %s for %s: deactivated %d orphan rows",
			tableActionInputGuard, actionID, deactivated)
	}

	scopeIds, err := upsertActionInternalAccessScopes(db, actionID, def.InternalAccessScopes, now)
	if err != nil {
		return err
	}
	keepScopes := toIdSet(scopeIds)
	if deactivated, err := reconcileSatelliteToPack(
		db, packTableStandardActionInternalAccessScope, actionID, keepScopes, now,
	); err != nil {
		return err
	} else if deactivated > 0 {
		log.Printf("   ⟳ Reconciled %s for %s: deactivated %d orphan rows",
			tableStandardActionInternalAccessScope, actionID, deactivated)
	}

	return nil
}

// toIdSet is a small helper for the reconcile `keep` argument.
func toIdSet(ids []string) map[string]struct{} {
	out := make(map[string]struct{}, len(ids))
	for _, id := range ids {
		out[id] = struct{}{}
	}
	return out
}

func upsertActionSchemaRows(db *sql.DB, actionID, direction string, rows []StandardActionSchemaDef, now string) ([]string, error) {
	if len(rows) == 0 {
		return nil, nil
	}
	// Reactivation branch: flip IsActive/IsDeleted back to (1, 0) ONLY when
	// IsCustomized=0 AND IsDeleted=1 — i.e. the pack previously dropped the
	// row and is now re-introducing it. Admin-disabled rows
	// (IsActive=0, IsDeleted=0) must survive pack upgrades, so those are NOT
	// touched; only reconcile-soft-deleted rows are ever re-activated.
	insertSQL := fmt.Sprintf(
		`INSERT INTO %s
		(Id, StandardActionId, Direction, APIName, DataType, ReferenceTo,
		 Label, UISection, UIMode, TargetObject, TargetFieldAPIName,
		 PrefillSourceFieldAPIName,
		 RequiredPicklistTag,
		 MappingSourceObject, MappingActionType,
		 IsCollection, IsRequired, Description, SortOrder, IsCustomized, IsActive, IsDeleted,
		 CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1, 0, ?, ?)
		ON DUPLICATE KEY UPDATE
		 DataType            = IF(IsCustomized = 0, VALUES(DataType), DataType),
		 ReferenceTo         = IF(IsCustomized = 0, VALUES(ReferenceTo), ReferenceTo),
		 Label               = IF(IsCustomized = 0, VALUES(Label), Label),
		 UISection           = IF(IsCustomized = 0, VALUES(UISection), UISection),
		 UIMode              = IF(IsCustomized = 0, VALUES(UIMode), UIMode),
		 TargetObject        = IF(IsCustomized = 0, VALUES(TargetObject), TargetObject),
		 TargetFieldAPIName  = IF(IsCustomized = 0, VALUES(TargetFieldAPIName), TargetFieldAPIName),
		 PrefillSourceFieldAPIName = IF(IsCustomized = 0, VALUES(PrefillSourceFieldAPIName), PrefillSourceFieldAPIName),
		 RequiredPicklistTag = IF(IsCustomized = 0, VALUES(RequiredPicklistTag), RequiredPicklistTag),
		 MappingSourceObject = IF(IsCustomized = 0, VALUES(MappingSourceObject), MappingSourceObject),
		 MappingActionType   = IF(IsCustomized = 0, VALUES(MappingActionType), MappingActionType),
		 IsCollection       = IF(IsCustomized = 0, VALUES(IsCollection), IsCollection),
		 IsRequired          = IF(IsCustomized = 0, VALUES(IsRequired), IsRequired),
		 Description         = IF(IsCustomized = 0, VALUES(Description), Description),
		 SortOrder           = IF(IsCustomized = 0, VALUES(SortOrder), SortOrder),
		 IsActive            = IF(IsCustomized = 0 AND IsDeleted = 1, VALUES(IsActive), IsActive),
		 IsDeleted           = IF(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted),
		 LastModifiedDate = IF(IsCustomized = 0, VALUES(LastModifiedDate), LastModifiedDate)`,
		tableActionSchema,
	)
	ids := make([]string, 0, len(rows))
	for _, row := range rows {
		id := actionSchemaID(actionID, direction, row.APIName)
		ids = append(ids, id)
		if _, err := db.Exec(insertSQL,
			id,
			actionID, direction, row.APIName, row.DataType,
			nullableString(row.ReferenceTo),
			nullableString(row.Label), nullableString(row.UISection), nullableString(row.UIMode),
			nullableString(row.TargetObject), nullableString(row.TargetFieldAPIName),
			nullableString(row.PrefillSourceFieldAPIName),
			nullableString(row.RequiredPicklistTag),
			nullableString(row.MappingSourceObject), nullableString(row.MappingActionType),
			boolToInt(row.IsCollection),
			boolToInt(row.IsRequired),
			nullableString(row.Description), row.SortOrder,
			now, now,
		); err != nil {
			return nil, apperrors.NewInternalError(fmt.Sprintf("failed to seed action schema %s/%s/%s", actionID, direction, row.APIName), err)
		}
	}
	return ids, nil
}

func upsertActionFieldDefaults(db *sql.DB, actionID string, rows []StandardActionDefaultDef, now string) ([]string, error) {
	if len(rows) == 0 {
		return nil, nil
	}
	insertSQL := fmt.Sprintf(
		`INSERT INTO %s
		(Id, StandardActionId, TargetObject, FieldAPIName, ValueFormula,
		 `+"`Condition`"+`, Priority, IsCustomized, IsActive, IsDeleted,
		 CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, ?, ?, ?, 0, 1, 0, ?, ?)
		ON DUPLICATE KEY UPDATE
		 ValueFormula     = IF(IsCustomized = 0, VALUES(ValueFormula), ValueFormula),
		 `+"`Condition`"+`= IF(IsCustomized = 0, VALUES(`+"`Condition`"+`), `+"`Condition`"+`),
		 IsActive         = IF(IsCustomized = 0 AND IsDeleted = 1, VALUES(IsActive), IsActive),
		 IsDeleted        = IF(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted),
		 LastModifiedDate = IF(IsCustomized = 0, VALUES(LastModifiedDate), LastModifiedDate)`,
		tableActionFieldDefault,
	)
	ids := make([]string, 0, len(rows))
	for _, row := range rows {
		id := actionFieldDefaultID(actionID, row.TargetObject, row.FieldAPIName, row.Priority)
		ids = append(ids, id)
		if _, err := db.Exec(insertSQL,
			id,
			actionID, row.TargetObject, row.FieldAPIName, row.ValueFormula,
			nullableString(row.Condition), row.Priority,
			now, now,
		); err != nil {
			return nil, apperrors.NewInternalError(fmt.Sprintf("failed to seed action field default %s/%s/%s", actionID, row.TargetObject, row.FieldAPIName), err)
		}
	}
	return ids, nil
}

func upsertActionInputGuards(db *sql.DB, actionID string, rows []StandardActionGuardDef, now string) ([]string, error) {
	if len(rows) == 0 {
		return nil, nil
	}
	// Id is intentionally converged on duplicate-key hits for pack-owned rows.
	// Warm upgrades can already have the same (StandardActionId, Condition,
	// GuardKey) row under the pre-GuardKey Id shape; reconcile keeps rows by Id,
	// so leaving that old Id in place would make the still-valid guard look like
	// an orphan and get soft-deleted.
	insertSQL := fmt.Sprintf(
		`INSERT INTO %s
		(Id, StandardActionId, `+"`Condition`"+`, GuardKey, RequiredPermission, RequiredObject, RequiredOperation, ErrorMessage,
		 GatedInput, SortOrder, IsCustomized, IsActive, IsDeleted,
		 CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1, 0, ?, ?)
		ON DUPLICATE KEY UPDATE
		 Id                 = IF(IsCustomized = 0, VALUES(Id), Id),
		 RequiredPermission = IF(IsCustomized = 0, VALUES(RequiredPermission), RequiredPermission),
		 RequiredObject     = IF(IsCustomized = 0, VALUES(RequiredObject), RequiredObject),
		 RequiredOperation  = IF(IsCustomized = 0, VALUES(RequiredOperation), RequiredOperation),
		 ErrorMessage     = IF(IsCustomized = 0, VALUES(ErrorMessage), ErrorMessage),
		 GatedInput       = IF(IsCustomized = 0, VALUES(GatedInput), GatedInput),
		 SortOrder        = IF(IsCustomized = 0, VALUES(SortOrder), SortOrder),
		 IsActive         = IF(IsCustomized = 0 AND IsDeleted = 1, VALUES(IsActive), IsActive),
		 IsDeleted        = IF(IsCustomized = 0 AND IsDeleted = 1, 0, IsDeleted),
		 LastModifiedDate = IF(IsCustomized = 0, VALUES(LastModifiedDate), LastModifiedDate)`,
		tableActionInputGuard,
	)
	expectedKeys := make([]string, 0, len(rows))
	seenKeys := make(map[string]struct{}, len(rows))
	for _, row := range rows {
		guardKey := actionInputGuardKey(row)
		id := actionInputGuardID(actionID, row.Condition, guardKey)
		identityKey := actionInputGuardIdentityKey(row.Condition, guardKey)
		if _, seen := seenKeys[identityKey]; !seen {
			seenKeys[identityKey] = struct{}{}
			expectedKeys = append(expectedKeys, identityKey)
		}
		if _, err := db.Exec(insertSQL,
			id,
			actionID, row.Condition, guardKey,
			nullableString(row.RequiredPermission),
			nullableString(row.RequiredObject),
			nullableString(row.RequiredOperation),
			nullableString(row.ErrorMessage), nullableString(row.GatedInput),
			row.SortOrder, now, now,
		); err != nil {
			return nil, apperrors.NewInternalError(fmt.Sprintf("failed to seed action input guard %s/%s", actionID, guardKey), err)
		}
	}
	return loadPersistedActionInputGuardIDs(db, actionID, expectedKeys)
}

func actionInputGuardIdentityKey(condition, guardKey string) string {
	return condition + "\x00" + guardKey
}

func loadPersistedActionInputGuardIDs(db *sql.DB, actionID string, expectedKeys []string) ([]string, error) {
	if len(expectedKeys) == 0 {
		return nil, nil
	}

	expected := make(map[string]struct{}, len(expectedKeys))
	for _, key := range expectedKeys {
		expected[key] = struct{}{}
	}

	rows, err := db.Query(fmt.Sprintf(
		`SELECT Id, `+"`Condition`"+`, GuardKey FROM %s WHERE StandardActionId = ? AND (IsDeleted = 0 OR IsCustomized = 1)`,
		tableActionInputGuard,
	), actionID)
	if err != nil {
		return nil, apperrors.NewInternalError(fmt.Sprintf("failed to reload action input guard ids for %s", actionID), err)
	}
	defer rows.Close()

	persistedByKey := make(map[string]string, len(expectedKeys))
	for rows.Next() {
		var id, condition, guardKey string
		if err := rows.Scan(&id, &condition, &guardKey); err != nil {
			return nil, apperrors.NewInternalError(fmt.Sprintf("failed to scan action input guard ids for %s", actionID), err)
		}
		identityKey := actionInputGuardIdentityKey(condition, guardKey)
		if _, ok := expected[identityKey]; ok {
			persistedByKey[identityKey] = id
		}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError(fmt.Sprintf("failed to iterate action input guard ids for %s", actionID), err)
	}

	ids := make([]string, 0, len(expectedKeys))
	for _, key := range expectedKeys {
		id, ok := persistedByKey[key]
		if !ok {
			return nil, apperrors.NewInternalError(fmt.Sprintf("seeded action input guard %s/%s was not persisted", actionID, key), nil)
		}
		ids = append(ids, id)
	}
	return ids, nil
}

func upsertActionInternalAccessScopes(db *sql.DB, actionID string, rows []StandardActionInternalAccessScopeDef, now string) ([]string, error) {
	if len(rows) == 0 {
		return nil, nil
	}
	insertSQL := fmt.Sprintf(
		`INSERT INTO %s
		(Id, StandardActionId, ObjectApiName, Operation, Reason,
		 IsActive, IsDeleted, CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, ?, 1, 0, ?, ?)
		ON DUPLICATE KEY UPDATE
		 Id               = VALUES(Id),
		 StandardActionId = VALUES(StandardActionId),
		 ObjectApiName    = VALUES(ObjectApiName),
		 Operation        = VALUES(Operation),
		 Reason           = VALUES(Reason),
		 IsActive         = VALUES(IsActive),
		 IsDeleted        = VALUES(IsDeleted),
		 LastModifiedDate = VALUES(LastModifiedDate)`,
		tableStandardActionInternalAccessScope,
	)
	ids := make([]string, 0, len(rows))
	for _, row := range rows {
		id := actionInternalAccessScopeID(actionID, row.ObjectAPIName, row.Operation)
		ids = append(ids, id)
		if _, err := db.Exec(insertSQL,
			id, actionID, row.ObjectAPIName, row.Operation, nullableString(row.Reason),
			now, now,
		); err != nil {
			return nil, apperrors.NewInternalError(fmt.Sprintf("failed to seed action internal access scope %s/%s.%s", actionID, row.ObjectAPIName, row.Operation), err)
		}
	}
	return ids, nil
}

// GetStandardActionInternalAccessScopes / loadStandardActionInternalAccessOwner
// (the runtime READ surface) STAY in internal/bootstrap
// (standard_action_internal_access_reader.go) because they are exposed to the
// standard-action service via the contract runtimeports.StandardActionReader
// port.

func nullableString(s string) interface{} {
	if strings.TrimSpace(s) == "" {
		return nil
	}
	return s
}

func previewFlowNameVal(p *string) interface{} {
	if p == nil || strings.TrimSpace(*p) == "" {
		return nil
	}
	return *p
}

// boolToInt is defined once for this package in field_mappings.go.