package bootstrap

import (
	"crypto/sha1"
	"database/sql"
	_ "embed"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"log"
	"strings"
	"time"

	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

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

// StandardActionDef describes one row in the _System_StandardAction registry
// together with its input/output schema, per-object field defaults, and
// permission guards. Authored as JSON and seeded idempotently at bootstrap
// with IsCustomized=1 rows treated as admin-owned and never overwritten,
// except for internal access scopes, which are L1 standard core only.
type StandardActionDef struct {
	Name                string   `json:"name"`
	Label               string   `json:"label"`
	Description         string   `json:"description,omitempty"`
	FlowDeveloperName   string   `json:"flowDeveloperName"`
	Category            string   `json:"category,omitempty"`
	RequiredPermissions []string `json:"requiredPermissions,omitempty"`
	// TargetObjects are the SObject API names this action applies to
	// (e.g. ["Lead"] for convertLead). An EMPTY slice means the action
	// has no object-level placement — it is not rendered on any object
	// page. Admins who want an action visible on a given SObject MUST
	// list that SObject explicitly; silence = hidden. This is metadata-
	// driven's explicit-over-implicit rule: the default state of "where
	// does this show up" is "nowhere" unless declared. If we ever need a
	// true "all objects" placement (e.g. a global-quick-action bar), add
	// a dedicated field like IsGlobal / PlacementScope rather than
	// overloading the empty array with a second semantic.
	//
	// Drives metadata-driven button discovery: GET /actions/standard
	// returns this field, frontend filters actions by membership in the
	// current object's name — no more hardcoded `objectApiName === 'lead'`.
	TargetObjects []string                   `json:"targetObjects,omitempty"`
	IsAtomic      bool                       `json:"isAtomic,omitempty"`
	SortOrder     int                        `json:"sortOrder,omitempty"`
	Inputs        []StandardActionSchemaDef  `json:"inputs,omitempty"`
	Outputs       []StandardActionSchemaDef  `json:"outputs,omitempty"`
	FieldDefaults []StandardActionDefaultDef `json:"fieldDefaults,omitempty"`
	InputGuards   []StandardActionGuardDef   `json:"inputGuards,omitempty"`
	// InternalAccessScopes declares conversion/bookkeeping reads and writes
	// owned by the standard action itself. Runtime consumes these as scoped
	// metadata; no action/object special-case branch is allowed in query or
	// persistence code.
	InternalAccessScopes []StandardActionInternalAccessScopeDef `json:"internalAccessScopes,omitempty"`
	// CustomPermissions declares the _System_CustomPermission rows this
	// action depends on. Declaring them inline keeps the action self-
	// describing — adding a new policy-style permission is a pure data
	// change in standard_actions.json, no Go edit required.
	CustomPermissions []StandardActionCustomPermissionDef `json:"customPermissions,omitempty"`
}

// StandardActionCustomPermissionDef is one row in _System_CustomPermission.
type StandardActionCustomPermissionDef struct {
	APIName     string `json:"apiName"`
	Label       string `json:"label"`
	Description string `json:"description,omitempty"`
}

// StandardActionSchemaDef is one row in _System_ActionSchema.
type StandardActionSchemaDef struct {
	APIName             string `json:"apiName"`
	DataType            string `json:"dataType"`
	ReferenceTo         string `json:"referenceTo,omitempty"`
	Label               string `json:"label,omitempty"`
	UISection           string `json:"uiSection,omitempty"`
	UIMode              string `json:"uiMode,omitempty"`
	TargetObject        string `json:"targetObject,omitempty"`
	TargetFieldAPIName  string `json:"targetFieldAPIName,omitempty"`
	RequiredPicklistTag string `json:"requiredPicklistTag,omitempty"`
	MappingSourceObject string `json:"mappingSourceObject,omitempty"`
	MappingActionType   string `json:"mappingActionType,omitempty"`
	IsRequired          bool   `json:"isRequired,omitempty"`
	Description         string `json:"description,omitempty"`
	SortOrder           int    `json:"sortOrder,omitempty"`
}

// StandardActionDefaultDef is one row in _System_ActionFieldDefault.
type StandardActionDefaultDef struct {
	TargetObject string `json:"targetObject"`
	FieldAPIName string `json:"fieldAPIName"`
	ValueFormula string `json:"valueFormula"`
	Condition    string `json:"condition,omitempty"`
	Priority     int    `json:"priority,omitempty"`
}

// StandardActionGuardDef is one row in _System_ActionInputGuard.
type StandardActionGuardDef struct {
	Condition          string `json:"condition"`
	RequiredPermission string `json:"requiredPermission,omitempty"`
	RequiredObject     string `json:"requiredObject,omitempty"`
	RequiredOperation  string `json:"requiredOperation,omitempty"`
	ErrorMessage       string `json:"errorMessage,omitempty"`
	// GatedInput is the action-input APIName or mode-control input this guard
	// targets (e.g. "doNotCreateOpportunity" for the SkipOpportunityOnConvert
	// guard, or "accountId" for Account create-vs-reuse guards). When non-empty,
	// GET /actions/standard surfaces the per-caller permission verdict so
	// clients can render, require, disable, or hide the matching input path. When
	// empty, the guard is a whole-action precondition.
	GatedInput string `json:"gatedInput,omitempty"`
	SortOrder  int    `json:"sortOrder,omitempty"`
}

// StandardActionInternalAccessScopeDef is one row in
// _System_StandardActionInternalAccessScope.
type StandardActionInternalAccessScopeDef struct {
	ObjectAPIName string `json:"objectApiName"`
	Operation     string `json:"operation"`
	Reason        string `json:"reason,omitempty"`
}

// StandardActionInternalAccessScopeRow is the runtime projection consumed by
// the dispatcher before it invokes a standard action flow.
type StandardActionInternalAccessScopeRow struct {
	StandardActionID string
	ObjectAPIName    string
	Operation        string
	Reason           string
}

type standardActionInternalAccessOwner struct {
	Name              string
	FlowDeveloperName string
	IsCustomized      bool
}

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"
	// 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 = "v3_action_guard_id_rekey"
)

// StandardActionID returns the deterministic synthetic Id for a standard-action
// row. Stable across bootstrap runs so ON DUPLICATE KEY UPDATE can reconcile.
func StandardActionID(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)))
}

// InitializeStandardActions seeds the _System_StandardAction registry and its
// four satellite tables from standard_actions.json. 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 InitializeStandardActions(rawDB *sql.DB) error {
	log.Println("🔧 Initializing standard actions...")
	start := time.Now()

	var defs []StandardActionDef
	if err := json.Unmarshal(standardActionsJSON, &defs); err != nil {
		return apperrors.NewInternalError("failed to parse standard_actions.json", 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.
	for _, def := range defs {
		if err := validateStandardActionDef(def); err != nil {
			return err
		}
	}
	if flowSeeds, err := loadStandardFlowSeeds(); err == nil {
		if err := validateStandardActionFlowBindings(defs, flowSeeds); err != nil {
			return err
		}
	} else {
		return err
	}

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

	// Pack-hash fast-path: if the shipped JSON is byte-identical to the
	// last successful seed, skip the entire seed + reconcile pass.
	// Writes: one SELECT on _System_BootstrapState per boot on warm installs.
	// The hash is stored in Phase (free-form text, no DDL change needed).
	packHash := standardActionsPackHash()
	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); 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)
	if err != nil {
		return err
	}
	if report, err := ReconcilePackTable(rawDB, packTableStandardAction, "", keep, now); err != nil {
		return err
	} else if report.Deactivated > 0 {
		log.Printf("   ⟳ Reconciled %s: deactivated %d orphan action(s)",
			tableStandardAction, report.Deactivated)
	}

	// Stamp the hash ONLY on full success. Any error above leaves the
	// gate row untouched, so the next boot re-runs the full pass.
	if err := markPackHashApplied(rawDB, standardActionsPackStateKey, packHash, 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) (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)
	}

	rows, err := db.Query(
		fmt.Sprintf(
			"SELECT Id FROM `%s` WHERE Name IN (%s) AND IsDeleted = 0",
			tableStandardAction, placeholders),
		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 standardActionsPackHash() string {
	payload := make([]byte, 0, len(standardActionsPackSeedVersion)+1+len(standardActionsJSON))
	payload = append(payload, standardActionsPackSeedVersion...)
	payload = append(payload, '\n')
	payload = append(payload, standardActionsJSON...)
	return sha256Hex(payload)
}

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 loadPersistedStandardActionName(db *sql.DB, id string) (string, error) {
	var name string
	err := db.QueryRow(
		fmt.Sprintf("SELECT Name FROM `%s` WHERE Id = ? AND IsDeleted = 0", tableStandardAction),
		id,
	).Scan(&name)
	switch {
	case err == sql.ErrNoRows:
		return "", nil
	case err != nil:
		return "", apperrors.NewInternalError(
			fmt.Sprintf("load persisted standard-action Name for %q", id), err)
	default:
		return name, 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))
			}
		}
	}
	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) error {
	insertActionID := StandardActionID(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, IsAtomic, SortOrder,
		 IsCustomized, IsActive, IsDeleted,
		 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),
		 IsAtomic            = IF(IsCustomized = 0, VALUES(IsAtomic), IsAtomic),
		 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)`,
		tableStandardAction,
	)

	if _, err := db.Exec(insertSQL,
		insertActionID, def.Name, def.Label, nullableString(def.Description),
		def.FlowDeveloperName, nullableString(def.Category),
		reqPermsJSON, targetObjectsJSON, boolToInt(def.IsAtomic), def.SortOrder,
		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)
	}

	if err := upsertActionCustomPermissions(db, def.CustomPermissions, now); err != nil {
		return err
	}
	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 upsertActionCustomPermissions(db *sql.DB, rows []StandardActionCustomPermissionDef, now string) error {
	if len(rows) == 0 {
		return nil
	}
	// IsCustomized=0 rows get pack content refreshed on every seed; IsCustomized=1
	// rows are preserved (admin-owned). IsActive/IsDeleted only flip when the row
	// was reconcile-soft-deleted, so an admin's `IsActive=0` toggle survives pack
	// updates — load-bearing because perm_system evaluates `cp.IsActive = 1`.
	insertSQL := `INSERT INTO _System_CustomPermission
		(Id, ApiName, Label, Description, IsCustomized, IsActive, IsDeleted,
		 CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, 0, 1, 0, ?, ?)
		ON DUPLICATE KEY UPDATE
		 Label            = IF(IsCustomized = 0, VALUES(Label), Label),
		 Description      = IF(IsCustomized = 0, VALUES(Description), Description),
		 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)`
	for _, row := range rows {
		id := "cperm_" + strings.ToLower(row.APIName)
		if _, err := db.Exec(insertSQL,
			id, row.APIName, row.Label, nullableString(row.Description),
			now, now,
		); err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("failed to seed custom permission %q", row.APIName), err)
		}
	}
	return nil
}

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,
		 RequiredPicklistTag,
		 MappingSourceObject, MappingActionType,
		 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),
		 RequiredPicklistTag = IF(IsCustomized = 0, VALUES(RequiredPicklistTag), RequiredPicklistTag),
		 MappingSourceObject = IF(IsCustomized = 0, VALUES(MappingSourceObject), MappingSourceObject),
		 MappingActionType   = IF(IsCustomized = 0, VALUES(MappingActionType), MappingActionType),
		 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.RequiredPicklistTag),
			nullableString(row.MappingSourceObject), nullableString(row.MappingActionType),
			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
}

func GetStandardActionInternalAccessScopes(db *sql.DB, standardActionID string) ([]StandardActionInternalAccessScopeRow, error) {
	if db == nil || strings.TrimSpace(standardActionID) == "" {
		return nil, nil
	}
	owner, err := loadStandardActionInternalAccessOwner(db, standardActionID)
	if err != nil {
		return nil, err
	}
	if owner == nil || owner.IsCustomized {
		return nil, nil
	}
	packDef, err := standardActionDefByName(owner.Name)
	if err != nil {
		return nil, err
	}
	if packDef == nil || len(packDef.InternalAccessScopes) == 0 {
		return nil, nil
	}
	if owner.FlowDeveloperName != packDef.FlowDeveloperName {
		return nil, nil
	}
	flowPackOwned, err := StandardFlowActiveVersionIsPackOwned(db, owner.FlowDeveloperName)
	if err != nil {
		return nil, err
	}
	if !flowPackOwned {
		return nil, nil
	}
	args := make([]interface{}, 0, 1+len(packDef.InternalAccessScopes)*3)
	args = append(args, standardActionID)
	clauses := make([]string, 0, len(packDef.InternalAccessScopes))
	for _, def := range packDef.InternalAccessScopes {
		clauses = append(clauses, "(Id = ? AND ObjectApiName = ? AND Operation = ?)")
		args = append(args,
			actionInternalAccessScopeID(standardActionID, def.ObjectAPIName, def.Operation),
			def.ObjectAPIName,
			def.Operation,
		)
	}
	rows, err := db.Query(
		fmt.Sprintf(
			`SELECT StandardActionId, ObjectApiName, Operation, Reason
			   FROM %s
			  WHERE StandardActionId = ? AND IsActive = 1 AND IsDeleted = 0
			    AND (%s)`,
			tableStandardActionInternalAccessScope,
			strings.Join(clauses, " OR ")),
		args...,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []StandardActionInternalAccessScopeRow
	for rows.Next() {
		var row StandardActionInternalAccessScopeRow
		var reason sql.NullString
		if err := rows.Scan(&row.StandardActionID, &row.ObjectAPIName, &row.Operation, &reason); err != nil {
			return nil, err
		}
		if reason.Valid {
			row.Reason = reason.String
		}
		out = append(out, row)
	}
	return out, rows.Err()
}

func loadStandardActionInternalAccessOwner(db *sql.DB, standardActionID string) (*standardActionInternalAccessOwner, error) {
	var owner standardActionInternalAccessOwner
	var flowName sql.NullString
	var isCustomized sql.NullBool
	err := db.QueryRow(
		fmt.Sprintf(
			`SELECT Name, FlowDeveloperName, IsCustomized
			   FROM %s
			  WHERE Id = ? AND IsDeleted = 0`,
			tableStandardAction),
		standardActionID,
	).Scan(&owner.Name, &flowName, &isCustomized)
	switch {
	case err == sql.ErrNoRows:
		return nil, nil
	case err != nil:
		return nil, err
	}
	if flowName.Valid {
		owner.FlowDeveloperName = flowName.String
	}
	if isCustomized.Valid {
		owner.IsCustomized = isCustomized.Bool
	}
	return &owner, nil
}

func standardActionDefByName(actionName string) (*StandardActionDef, error) {
	var defs []StandardActionDef
	if err := json.Unmarshal(standardActionsJSON, &defs); err != nil {
		return nil, apperrors.NewInternalError("failed to parse standard_actions.json", err)
	}
	for i := range defs {
		def := &defs[i]
		if def.Name == actionName {
			return def, nil
		}
	}
	return nil, nil
}

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

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}