package packs

import (
	"bytes"
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"sort"
	"strings"
	"time"

	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/kernel/pkg/actionvalidate"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

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"`
}

type standardActionRow struct {
	ID            string
	ObjectAPIName string
	Name          string
	Label         string
	Type          string
	Icon          string
	TargetObject  string
	Config        string
	OwnerPack     string
}

// StandardActionID produces a deterministic `_System_Action.Id` for new rows.
// Component ownership and drift checks use the business key
// (ObjectApiName, Name), because warm-upgraded orgs can already have a
// different persisted row Id for the same action component.
func StandardActionID(objectAPIName, actionName string) string {
	return fmt.Sprintf("act_%s_%s", strings.ToLower(objectAPIName), strings.ToLower(actionName))
}

func (a standardActionDef) hasExplicitScope() bool {
	return strings.TrimSpace(a.ObjectAPIName) != "" || len(a.AppliesTo) > 0
}

func (a standardActionDef) scopedObjectAPINames() []string {
	if strings.TrimSpace(a.ObjectAPIName) != "" {
		return []string{strings.TrimSpace(a.ObjectAPIName)}
	}
	names := make([]string, 0, len(a.AppliesTo))
	for _, name := range a.AppliesTo {
		if trimmed := strings.TrimSpace(name); trimmed != "" {
			names = append(names, trimmed)
		}
	}
	return names
}

func standardActionBusinessKey(objectAPIName, name string) string {
	return strings.ToLower(strings.TrimSpace(objectAPIName)) + "\x00" + strings.ToLower(strings.TrimSpace(name))
}

func verifyStandardActions(db *sql.DB, ownerPack string, payload []byte, source string) error {
	actions, err := parseStandardActionPayload(payload, source)
	if err != nil {
		return err
	}
	if err := validateStandardActionPayload(actions, ownerPack, source, true); err != nil {
		return err
	}
	expected, err := expandStandardActionRows(actions, ownerPack)
	if err != nil {
		return err
	}

	var errs []string
	for _, want := range sortedStandardActionRows(expected) {
		var got standardActionRow
		var icon, targetObject, config, owner sql.NullString
		var isDeleted int
		err := db.QueryRow(fmt.Sprintf(
			"SELECT `%s`, `%s`, `%s`, `%s`, `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, ''), `%s` "+
				"FROM `%s` WHERE `%s` = ? AND `%s` = ?",
			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.FieldSysAction_OwnerPack,
			constants.FieldSysAction_IsDeleted,
			constants.TableAction,
			constants.FieldSysAction_ObjectAPIName,
			constants.FieldSysAction_Name,
		), want.ObjectAPIName, want.Name).Scan(&got.ID, &got.ObjectAPIName, &got.Name, &got.Label, &got.Type, &icon, &targetObject, &config, &owner, &isDeleted)
		if err == sql.ErrNoRows {
			errs = append(errs, fmt.Sprintf("action %s.%s is missing", want.ObjectAPIName, want.Name))
			continue
		}
		if err != nil {
			return fmt.Errorf("query action %s.%s: %w", want.ObjectAPIName, want.Name, err)
		}
		got.Icon = icon.String
		got.TargetObject = targetObject.String
		got.OwnerPack = owner.String
		got.Config, err = canonicalActionConfigString(config.String)
		if err != nil {
			errs = append(errs, fmt.Sprintf("action %s.%s Config is not valid JSON: %v", want.ObjectAPIName, want.Name, err))
		}

		if isDeleted != 0 {
			errs = append(errs, fmt.Sprintf("action %s.%s IsDeleted = %d, want 0", want.ObjectAPIName, want.Name, isDeleted))
		}
		if got.OwnerPack != ownerPack {
			errs = append(errs, fmt.Sprintf("action %s.%s OwnerPack = %q, want %q", want.ObjectAPIName, want.Name, got.OwnerPack, ownerPack))
		}
		if got.ObjectAPIName != want.ObjectAPIName {
			errs = append(errs, fmt.Sprintf("action %s.%s ObjectApiName = %q, want %q", want.ObjectAPIName, want.Name, got.ObjectAPIName, want.ObjectAPIName))
		}
		if got.Name != want.Name {
			errs = append(errs, fmt.Sprintf("action %s.%s Name = %q, want %q", want.ObjectAPIName, want.Name, got.Name, want.Name))
		}
		if got.Label != want.Label {
			errs = append(errs, fmt.Sprintf("action %s.%s Label = %q, want %q", want.ObjectAPIName, want.Name, got.Label, want.Label))
		}
		if got.Type != want.Type {
			errs = append(errs, fmt.Sprintf("action %s.%s Type = %q, want %q", want.ObjectAPIName, want.Name, got.Type, want.Type))
		}
		if got.Icon != want.Icon {
			errs = append(errs, fmt.Sprintf("action %s.%s Icon = %q, want %q", want.ObjectAPIName, want.Name, got.Icon, want.Icon))
		}
		if got.TargetObject != want.TargetObject {
			errs = append(errs, fmt.Sprintf("action %s.%s TargetObject = %q, want %q", want.ObjectAPIName, want.Name, got.TargetObject, want.TargetObject))
		}
		if got.Config != want.Config {
			errs = append(errs, fmt.Sprintf("action %s.%s Config drift", want.ObjectAPIName, want.Name))
		}
	}

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s` FROM `%s` WHERE COALESCE(`%s`, '') = ? AND `%s` = 0 ORDER BY `%s`",
		constants.FieldSysAction_ID,
		constants.FieldSysAction_ObjectAPIName,
		constants.FieldSysAction_Name,
		constants.TableAction,
		constants.FieldSysAction_OwnerPack,
		constants.FieldSysAction_IsDeleted,
		constants.FieldSysAction_ID,
	), ownerPack)
	if err != nil {
		return fmt.Errorf("query actions owned by %s: %w", ownerPack, err)
	}
	defer rows.Close()
	for rows.Next() {
		var id, objectAPIName, name string
		if err := rows.Scan(&id, &objectAPIName, &name); err != nil {
			return fmt.Errorf("scan action owned by %s: %w", ownerPack, err)
		}
		if _, ok := expected[standardActionBusinessKey(objectAPIName, name)]; !ok {
			errs = append(errs, fmt.Sprintf("action %s (%s.%s) is active but not declared by %s", id, objectAPIName, name, source))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate actions owned by %s: %w", ownerPack, err)
	}

	if len(errs) > 0 {
		return fmt.Errorf("standard action catalog %s drift:\n  - %s", ownerPack, strings.Join(errs, "\n  - "))
	}
	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 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
}

func parseStandardActionPayload(payload []byte, source string) ([]standardActionDef, error) {
	var actions []standardActionDef
	if err := json.Unmarshal(payload, &actions); err != nil {
		return nil, fmt.Errorf("parse %s: %w", source, err)
	}
	return actions, nil
}

func validateStandardActionPayload(actions []standardActionDef, ownerPack, source string, requireExplicitScope bool) error {
	if strings.TrimSpace(ownerPack) == "" {
		return fmt.Errorf("standard action catalog owner pack is required")
	}
	seen := make(map[string]struct{}, len(actions))
	for _, action := range actions {
		if strings.TrimSpace(action.Name) == "" {
			return fmt.Errorf("%s contains action with empty name", source)
		}
		if strings.TrimSpace(action.Label) == "" {
			return fmt.Errorf("%s action %s has empty label", source, action.Name)
		}
		if strings.TrimSpace(action.Type) == "" {
			return fmt.Errorf("%s action %s has empty type", source, action.Name)
		}
		if requireExplicitScope && !action.hasExplicitScope() {
			return fmt.Errorf("%s action %s has no objectApiName/appliesTo scope; app-pack action catalogs must not apply to every schema", source, action.Name)
		}
		scopes := action.scopedObjectAPINames()
		if requireExplicitScope && len(scopes) == 0 {
			return fmt.Errorf("%s action %s has an empty object scope", source, action.Name)
		}
		for _, objectAPIName := range scopes {
			key := standardActionBusinessKey(objectAPIName, action.Name)
			if _, ok := seen[key]; ok {
				return fmt.Errorf("%s action %s declares duplicate scope %s", source, action.Name, objectAPIName)
			}
			seen[key] = struct{}{}
		}
	}
	return nil
}

func expandStandardActionRows(actions []standardActionDef, ownerPack string) (map[string]standardActionRow, error) {
	rows := make(map[string]standardActionRow)
	for _, action := range actions {
		config, err := canonicalActionConfigMap(action.Config)
		if err != nil {
			return nil, fmt.Errorf("action %s config: %w", action.Name, err)
		}
		for _, objectAPIName := range action.scopedObjectAPINames() {
			id := StandardActionID(objectAPIName, action.Name)
			key := standardActionBusinessKey(objectAPIName, action.Name)
			targetObject := ""
			if action.TargetObject != "" {
				targetObject = action.TargetObject
			}
			row := standardActionRow{
				ID:            id,
				ObjectAPIName: objectAPIName,
				Name:          action.Name,
				Label:         action.Label,
				Type:          action.Type,
				Icon:          action.Icon,
				TargetObject:  targetObject,
				Config:        config,
				OwnerPack:     ownerPack,
			}
			if _, ok := rows[key]; ok {
				return nil, fmt.Errorf("duplicate action business key %s.%s", objectAPIName, action.Name)
			}
			rows[key] = row
		}
	}
	return rows, nil
}

func sortedStandardActionRows(rows map[string]standardActionRow) []standardActionRow {
	keys := make([]string, 0, len(rows))
	for key := range rows {
		keys = append(keys, key)
	}
	sort.Strings(keys)
	out := make([]standardActionRow, 0, len(keys))
	for _, key := range keys {
		out = append(out, rows[key])
	}
	return out
}

func canonicalActionConfigMap(config map[string]interface{}) (string, error) {
	if len(config) == 0 {
		return "", nil
	}
	raw, err := json.Marshal(config)
	if err != nil {
		return "", err
	}
	return canonicalActionConfigString(string(raw))
}

func canonicalActionConfigString(raw string) (string, error) {
	trimmed := bytes.TrimSpace([]byte(raw))
	if len(trimmed) == 0 || bytes.Equal(trimmed, []byte("null")) {
		return "", nil
	}
	var decoded interface{}
	if err := json.Unmarshal(trimmed, &decoded); err != nil {
		return "", err
	}
	canonical, err := json.Marshal(decoded)
	if err != nil {
		return "", err
	}
	return string(canonical), nil
}

// 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.
// Pack-owned action rows are package metadata: apply revives expected rows and
// marks stale rows deleted so runtime reads match the installed pack payload.
func buildStandardActionsUpsertSQL(valueParts []string) string {
	canOwn := fmt.Sprintf("COALESCE(`%s`, '') IN ('', VALUES(`%s`))",
		constants.FieldSysAction_OwnerPack, constants.FieldSysAction_OwnerPack)
	updateOwned := func(field string) string {
		return fmt.Sprintf("`%s` = IF(%s, VALUES(`%s`), `%s`)", field, canOwn, field, field)
	}
	updates := []string{
		updateOwned(constants.FieldSysAction_Label),
		updateOwned(constants.FieldSysAction_Type),
		updateOwned(constants.FieldSysAction_Icon),
		updateOwned(constants.FieldSysAction_TargetObject),
		updateOwned(constants.FieldSysAction_Config),
		updateOwned(constants.FieldSysAction_OwnerPack),
		updateOwned(constants.FieldSysAction_IsDeleted),
		updateOwned(constants.FieldLastModifiedDate),
	}
	return fmt.Sprintf(
		"INSERT INTO `%s` (`%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`) VALUES %s "+
			"ON DUPLICATE KEY UPDATE %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.FieldSysAction_OwnerPack,
		constants.FieldCreatedDate, constants.FieldLastModifiedDate,
		constants.FieldSysAction_IsDeleted,
		strings.Join(valueParts, ", "),
		strings.Join(updates, ", "),
	)
}

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

	actions, err := parseStandardActionPayload(payload, source)
	if err != nil {
		return apperrors.NewInternalError("failed to parse standard actions", err)
	}
	if err := validateStandardActionPayload(actions, ownerPack, source, true); err != nil {
		return err
	}

	// Tenant-specific action content (fullActions + overlays) is applied
	// separately by `cmd/setup apply-tenant`, layered on top of the rows this
	// function writes.

	log.Printf("   ...Ensuring %d standard actions for owner pack %s...", len(actions), ownerPack)

	// 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)
	}

	expected, err := expandStandardActionRows(actions, ownerPack)
	if err != nil {
		return err
	}
	if err := ensureStandardActionRowsClaimable(rawDB, ownerPack, expected); err != nil {
		return err
	}

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

	for _, row := range sortedStandardActionRows(expected) {
		var targetObj interface{} = nil
		if row.TargetObject != "" {
			targetObj = row.TargetObject
		}
		var config interface{} = nil
		if row.Config != "" {
			config = row.Config
		}
		valueParts = append(valueParts, "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)")
		params = append(params, row.ID, row.ObjectAPIName, row.Name, row.Label,
			row.Type, row.Icon, targetObj, config, row.OwnerPack, 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, OwnerPack, IsDeleted)
	// propagate to existing rows on restart. OwnerPack makes this a real
	// pack-owned catalog instead of an implicit act_* naming convention.
	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*11 : end*11]

		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
		}
	}

	if firstErr != nil {
		return apperrors.NewInternalError("some action chunks failed to insert", firstErr)
	}
	if err := ensureStandardActionRowsOwned(rawDB, ownerPack, expected); err != nil {
		return err
	}
	if err := markStalePackActionsDeleted(rawDB, ownerPack, expected); err != nil {
		return err
	}

	log.Printf("   ✅ Standard actions ensured: %d total (%v)",
		len(valueParts), time.Since(start).Round(time.Millisecond))
	return nil
}

func markStalePackActionsDeleted(db *sql.DB, ownerPack string, expected map[string]standardActionRow) error {
	args := make([]interface{}, 0, len(expected)*2+1)
	args = append(args, ownerPack)
	where := fmt.Sprintf("COALESCE(`%s`, '') = ? AND `%s` = 0",
		constants.FieldSysAction_OwnerPack,
		constants.FieldSysAction_IsDeleted)
	if len(expected) > 0 {
		var keep []string
		for _, row := range sortedStandardActionRows(expected) {
			keep = append(keep, fmt.Sprintf("(`%s` = ? AND `%s` = ?)",
				constants.FieldSysAction_ObjectAPIName,
				constants.FieldSysAction_Name))
			args = append(args, row.ObjectAPIName, row.Name)
		}
		where += " AND NOT (" + strings.Join(keep, " OR ") + ")"
	}

	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NOW() WHERE %s",
		constants.TableAction,
		constants.FieldSysAction_IsDeleted,
		constants.FieldLastModifiedDate,
		where,
	)
	if _, err := db.Exec(sqlStr, args...); err != nil {
		return fmt.Errorf("mark stale actions deleted for %s: %w", ownerPack, err)
	}
	return nil
}

func ensureStandardActionRowsClaimable(db *sql.DB, ownerPack string, expected map[string]standardActionRow) error {
	for _, row := range sortedStandardActionRows(expected) {
		var id string
		var existingOwner sql.NullString
		err := db.QueryRow(fmt.Sprintf(
			"SELECT `%s`, `%s` FROM `%s` WHERE `%s` = ? AND `%s` = ? LIMIT 1",
			constants.FieldSysAction_ID,
			constants.FieldSysAction_OwnerPack,
			constants.TableAction,
			constants.FieldSysAction_ObjectAPIName,
			constants.FieldSysAction_Name,
		), row.ObjectAPIName, row.Name).Scan(&id, &existingOwner)
		if err == sql.ErrNoRows {
			continue
		}
		if err != nil {
			return fmt.Errorf("query action ownership for %s.%s: %w", row.ObjectAPIName, row.Name, err)
		}
		if existingOwner.Valid && existingOwner.String != "" && existingOwner.String != ownerPack {
			return fmt.Errorf("action %s.%s is owned by %q (row %s), cannot claim for %q",
				row.ObjectAPIName, row.Name, existingOwner.String, id, ownerPack)
		}
	}
	return nil
}

func ensureStandardActionRowsOwned(db *sql.DB, ownerPack string, expected map[string]standardActionRow) error {
	for _, row := range sortedStandardActionRows(expected) {
		var id string
		var existingOwner sql.NullString
		var isDeleted int
		err := db.QueryRow(fmt.Sprintf(
			"SELECT `%s`, `%s`, `%s` FROM `%s` WHERE `%s` = ? AND `%s` = ? LIMIT 1",
			constants.FieldSysAction_ID,
			constants.FieldSysAction_OwnerPack,
			constants.FieldSysAction_IsDeleted,
			constants.TableAction,
			constants.FieldSysAction_ObjectAPIName,
			constants.FieldSysAction_Name,
		), row.ObjectAPIName, row.Name).Scan(&id, &existingOwner, &isDeleted)
		if err != nil {
			return fmt.Errorf("query action ownership after apply for %s.%s: %w", row.ObjectAPIName, row.Name, err)
		}
		if !existingOwner.Valid || existingOwner.String != ownerPack {
			return fmt.Errorf("action %s.%s row %s OwnerPack = %q, want %q",
				row.ObjectAPIName, row.Name, id, existingOwner.String, ownerPack)
		}
		if isDeleted != 0 {
			return fmt.Errorf("action %s.%s row %s IsDeleted = %d, want 0", row.ObjectAPIName, row.Name, id, isDeleted)
		}
	}
	return nil
}