package packs

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"

	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

type objectSpecChildCloneRuleApplySnapshot struct {
	id                string
	apiName           string
	liveRules         sql.NullString
	customized        bool
	packName          string
	packBusinessKey   string
	packManageability string
	packRules         sql.NullString
}

type childCloneRuleApplyOwnershipOptions struct {
	convergeSidecarlessEmptyDefaultsOnly bool
}

func prepareObjectSpecChildCloneRulePackOwnershipForApply(ctx context.Context, db *sql.DB, packName, businessKey string, manageability Manageability, specs []ObjectSpec) error {
	return prepareObjectSpecChildCloneRulePackOwnershipForApplyWithOptions(ctx, db, packName, businessKey, manageability, specs, childCloneRuleApplyOwnershipOptions{})
}

func prepareObjectSpecChildCloneRulePackOwnershipForApplyWithOptions(ctx context.Context, db *sql.DB, packName, businessKey string, manageability Manageability, specs []ObjectSpec, opts childCloneRuleApplyOwnershipOptions) error {
	rows, err := loadObjectSpecChildCloneRuleApplySnapshots(ctx, db, specs)
	if err != nil {
		return err
	}
	for _, row := range rows {
		if err := validateObjectSpecChildCloneRuleApplyOwnership(packName, businessKey, row); err != nil {
			return err
		}
	}

	var markCustomizedIDs []string
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		row, ok := rows[domainschema.GenerateObjectID(apiName)]
		if !ok {
			continue
		}
		packValue, err := canonicalChildCloneRulesPackValue(spec.ChildCloneRules)
		if err != nil {
			return fmt.Errorf("encode pack ChildCloneRules ownership value for %s: %w", apiName, err)
		}
		if opts.convergeSidecarlessEmptyDefaultsOnly && shouldConvergeSidecarlessEmptyDefaultsOnlyChildCloneRules(row, manageability, packValue) {
			continue
		}
		mark, err := shouldMarkLegacyChildCloneRulesCustomized(row, packName, businessKey, manageability, packValue)
		if err != nil {
			return err
		}
		if mark {
			markCustomizedIDs = append(markCustomizedIDs, row.id)
		}
	}
	if err := markChildCloneRulesCustomized(ctx, db, markCustomizedIDs); err != nil {
		return err
	}
	return nil
}

func shouldConvergeSidecarlessEmptyDefaultsOnlyChildCloneRules(row objectSpecChildCloneRuleApplySnapshot, manageability Manageability, currentPackValue string) bool {
	if manageability != ManageabilityDefaultsOnly || currentPackValue != "[]" || row.customized {
		return false
	}
	if !row.liveRules.Valid || strings.TrimSpace(row.liveRules.String) == "" {
		return false
	}
	return row.packName == "" && row.packBusinessKey == ""
}

func persistObjectSpecChildCloneRulePackOwnership(ctx context.Context, db *sql.DB, packName, businessKey string, manageability Manageability, specs []ObjectSpec, requireRows bool) error {
	if db == nil {
		return fmt.Errorf("db is unavailable for ChildCloneRules pack ownership persistence")
	}
	packName = strings.TrimSpace(packName)
	businessKey = strings.TrimSpace(businessKey)
	if packName == "" || businessKey == "" {
		return fmt.Errorf("ChildCloneRules pack ownership requires pack name and object_spec business key")
	}
	query := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = NOW() WHERE `%s` = ?",
		constants.TableObject,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackManageability,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		encoded, err := canonicalChildCloneRulesPackValue(spec.ChildCloneRules)
		if err != nil {
			return fmt.Errorf("encode pack ChildCloneRules ownership value for %s: %w", apiName, err)
		}
		res, err := db.ExecContext(
			ctx,
			query,
			packName,
			businessKey,
			string(manageability),
			encoded,
			domainschema.GenerateObjectID(apiName),
		)
		if err != nil {
			return fmt.Errorf("persist ChildCloneRules pack ownership for %s: %w", apiName, err)
		}
		if requireRows {
			affected, _ := res.RowsAffected()
			if affected == 0 {
				exists, existsErr := objectSpecRowExists(ctx, db, domainschema.GenerateObjectID(apiName))
				if existsErr != nil {
					return fmt.Errorf("persist ChildCloneRules pack ownership for %s: check row existence: %w", apiName, existsErr)
				}
				if !exists {
					return fmt.Errorf("persist ChildCloneRules pack ownership for %s updated 0 rows", apiName)
				}
			}
		}
	}
	return nil
}

func loadObjectSpecChildCloneRuleApplySnapshots(ctx context.Context, db *sql.DB, specs []ObjectSpec) (map[string]objectSpecChildCloneRuleApplySnapshot, error) {
	if db == nil {
		return nil, fmt.Errorf("db is unavailable for ChildCloneRules pack ownership preparation")
	}
	ids := make([]string, 0, len(specs))
	seen := map[string]struct{}{}
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		id := domainschema.GenerateObjectID(apiName)
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	sort.Strings(ids)
	out := make(map[string]objectSpecChildCloneRuleApplySnapshot, len(ids))
	if len(ids) == 0 {
		return out, nil
	}

	placeholders := make([]string, 0, len(ids))
	args := make([]interface{}, 0, len(ids))
	for _, id := range ids {
		placeholders = append(placeholders, "?")
		args = append(args, id)
	}
	query := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, COALESCE(`%s`, 0), COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, ''), `%s` FROM `%s` "+
			"WHERE COALESCE(`%s`, 0) = 0 AND `%s` IN (%s) ORDER BY `%s`",
		constants.FieldID,
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_ChildCloneRules,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackManageability,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.TableObject,
		constants.FieldSysObject_IsDeleted,
		constants.FieldID,
		strings.Join(placeholders, ","),
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("query ChildCloneRules object_spec ownership snapshots: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var row objectSpecChildCloneRuleApplySnapshot
		var customized int
		if err := rows.Scan(&row.id, &row.apiName, &row.liveRules, &customized, &row.packName, &row.packBusinessKey, &row.packManageability, &row.packRules); err != nil {
			return nil, fmt.Errorf("scan ChildCloneRules object_spec ownership snapshot: %w", err)
		}
		row.id = strings.TrimSpace(row.id)
		row.apiName = strings.TrimSpace(row.apiName)
		row.packName = strings.TrimSpace(row.packName)
		row.packBusinessKey = strings.TrimSpace(row.packBusinessKey)
		row.packManageability = strings.TrimSpace(row.packManageability)
		row.customized = customized != 0
		if row.id != "" {
			out[row.id] = row
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate ChildCloneRules object_spec ownership snapshots: %w", err)
	}
	return out, nil
}

func validateObjectSpecChildCloneRuleApplyOwnership(packName, businessKey string, row objectSpecChildCloneRuleApplySnapshot) error {
	packName = strings.TrimSpace(packName)
	businessKey = strings.TrimSpace(businessKey)
	hasPackOwner := row.packName != "" || row.packBusinessKey != ""
	if !hasPackOwner {
		return nil
	}
	if row.packName == packName && row.packBusinessKey == businessKey {
		return nil
	}
	return fmt.Errorf("object %s ChildCloneRules already owned by pack %s object_spec %s; refusing to apply pack %s object_spec %s",
		row.apiName, row.packName, row.packBusinessKey, packName, businessKey)
}

func shouldMarkLegacyChildCloneRulesCustomized(row objectSpecChildCloneRuleApplySnapshot, packName, businessKey string, manageability Manageability, currentPackValue string) (bool, error) {
	if manageability != ManageabilityDefaultsOnly || row.customized {
		return false, nil
	}
	if !row.liveRules.Valid || strings.TrimSpace(row.liveRules.String) == "" {
		return false, nil
	}
	live, err := canonicalChildCloneRulesJSON(row.liveRules)
	if err != nil {
		return false, fmt.Errorf("canonicalize live ChildCloneRules for %s: %w", row.apiName, err)
	}
	compareTo := currentPackValue
	hasPackOwner := row.packName != "" || row.packBusinessKey != ""
	if hasPackOwner {
		if row.packName != strings.TrimSpace(packName) || row.packBusinessKey != strings.TrimSpace(businessKey) {
			return false, nil
		}
		previousPack, err := canonicalChildCloneRulesJSON(row.packRules)
		if err != nil {
			return false, fmt.Errorf("canonicalize previous pack ChildCloneRules for %s: %w", row.apiName, err)
		}
		if string(previousPack) != currentPackValue {
			return false, nil
		}
		compareTo = string(previousPack)
	}
	return string(live) != compareTo, nil
}

func markChildCloneRulesCustomized(ctx context.Context, db *sql.DB, ids []string) error {
	if len(ids) == 0 {
		return nil
	}
	sort.Strings(ids)
	query := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NOW() WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0",
		constants.TableObject,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysObject_ChildCloneRulesCustomized,
	)
	for _, id := range ids {
		if _, err := db.ExecContext(ctx, query, id); err != nil {
			return fmt.Errorf("mark ChildCloneRules customized for %s: %w", id, err)
		}
	}
	return nil
}

type ObjectSpecChildCloneRulePackOwnershipSnapshot struct {
	rows []objectSpecChildCloneRuleSidecarSnapshot
}

type objectSpecChildCloneRuleSidecarSnapshot struct {
	id                string
	exists            bool
	customized        bool
	packName          sql.NullString
	packBusinessKey   sql.NullString
	packManageability sql.NullString
	packRules         sql.NullString
}

func SnapshotObjectSpecChildCloneRulePackOwnershipForPack(ctx context.Context, db *sql.DB, pack *SourceBackedPack) (*ObjectSpecChildCloneRulePackOwnershipSnapshot, error) {
	if db == nil || pack == nil {
		return &ObjectSpecChildCloneRulePackOwnershipSnapshot{}, nil
	}
	ids, err := objectSpecChildCloneRuleObjectIDsForPack(pack)
	if err != nil {
		return nil, err
	}
	return snapshotObjectSpecChildCloneRulePackOwnership(ctx, db, ids)
}

func objectSpecChildCloneRuleObjectIDsForPack(pack *SourceBackedPack) ([]string, error) {
	seen := map[string]struct{}{}
	for _, component := range pack.Components() {
		if component.Kind() != ComponentKindObjectSpec {
			continue
		}
		specs, err := expectedObjectSpecsForChildCloneRulesOwnership(pack.Name(), component, "snapshot")
		if err != nil {
			return nil, err
		}
		for _, spec := range specs {
			apiName := strings.TrimSpace(spec.APIName)
			if apiName == "" {
				continue
			}
			seen[domainschema.GenerateObjectID(apiName)] = struct{}{}
		}
	}
	ids := make([]string, 0, len(seen))
	for id := range seen {
		ids = append(ids, id)
	}
	sort.Strings(ids)
	return ids, nil
}

func snapshotObjectSpecChildCloneRulePackOwnership(ctx context.Context, db *sql.DB, ids []string) (*ObjectSpecChildCloneRulePackOwnershipSnapshot, error) {
	snapshot := &ObjectSpecChildCloneRulePackOwnershipSnapshot{
		rows: make([]objectSpecChildCloneRuleSidecarSnapshot, 0, len(ids)),
	}
	if len(ids) == 0 {
		return snapshot, nil
	}
	byID := make(map[string]int, len(ids))
	for _, id := range ids {
		row := objectSpecChildCloneRuleSidecarSnapshot{id: id}
		snapshot.rows = append(snapshot.rows, row)
		byID[id] = len(snapshot.rows) - 1
	}

	placeholders := make([]string, 0, len(ids))
	args := make([]interface{}, 0, len(ids))
	for _, id := range ids {
		placeholders = append(placeholders, "?")
		args = append(args, id)
	}
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, 0), `%s`, `%s`, `%s`, `%s` FROM `%s` WHERE `%s` IN (%s) ORDER BY `%s`",
		constants.FieldID,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackManageability,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.TableObject,
		constants.FieldID,
		strings.Join(placeholders, ","),
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("snapshot ChildCloneRules pack ownership sidecars: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var row objectSpecChildCloneRuleSidecarSnapshot
		var customized int
		if err := rows.Scan(&row.id, &customized, &row.packName, &row.packBusinessKey, &row.packManageability, &row.packRules); err != nil {
			return nil, fmt.Errorf("scan ChildCloneRules pack ownership sidecar snapshot: %w", err)
		}
		row.exists = true
		row.customized = customized != 0
		if index, ok := byID[row.id]; ok {
			snapshot.rows[index] = row
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate ChildCloneRules pack ownership sidecar snapshot: %w", err)
	}
	return snapshot, nil
}

func (s *ObjectSpecChildCloneRulePackOwnershipSnapshot) Restore(ctx context.Context, db *sql.DB) error {
	if s == nil || db == nil || len(s.rows) == 0 {
		return nil
	}
	query := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = NOW() WHERE `%s` = ?",
		constants.TableObject,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackManageability,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)
	for _, row := range s.rows {
		if _, err := db.ExecContext(ctx, query, snapshotBool(row.exists, row.customized), nullableSnapshotString(row.exists, row.packName), nullableSnapshotString(row.exists, row.packBusinessKey), nullableSnapshotString(row.exists, row.packManageability), nullableSnapshotString(row.exists, row.packRules), row.id); err != nil {
			return fmt.Errorf("restore ChildCloneRules pack ownership sidecar for %s: %w", row.id, err)
		}
	}
	return nil
}

func snapshotBool(exists, value bool) int {
	if !exists || !value {
		return 0
	}
	return 1
}

func nullableSnapshotString(exists bool, value sql.NullString) interface{} {
	if !exists || !value.Valid {
		return nil
	}
	return value.String
}

func objectSpecRowExists(ctx context.Context, db *sql.DB, objectID string) (bool, error) {
	query := fmt.Sprintf(
		"SELECT 1 FROM `%s` WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0 LIMIT 1",
		constants.TableObject,
		constants.FieldID,
		constants.FieldSysObject_IsDeleted,
	)
	var v int
	err := db.QueryRowContext(ctx, query, objectID).Scan(&v)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}

func canonicalChildCloneRulesPackValue(rules []models.ChildCloneRule) (string, error) {
	raw, err := canonicalChildCloneRules(rules)
	if err != nil {
		return "", err
	}
	return string(raw), nil
}

// BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacks repairs rows
// installed before the ChildCloneRulesPack* sidecar columns existed. The active
// _System_InstalledPack rows are the app-pack ownership SSoT; each resolved
// source-backed object_spec component supplies the pack/business-key/
// manageability identity and canonical pack value for its objects.
func BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacks(ctx context.Context, db *sql.DB) (int, error) {
	return BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacksWithOptions(ctx, db, ActivePackBackfillOptions{})
}

// BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacksWithComponentKindRegistrationSet
// carries one distribution-owned registration snapshot through active-source resolution.
func BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacksWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, registrations ComponentKindRegistrationSet) (int, error) {
	return BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacksWithOptions(ctx, db, ActivePackBackfillOptions{ComponentKindRegistrationSet: registrations})
}

// BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacksWithOptions
// defers packs already planned for component apply and resolves every other
// active declaration from its installed source.
func BackfillObjectSpecChildCloneRulePackOwnershipFromActivePacksWithOptions(ctx context.Context, db *sql.DB, options ActivePackBackfillOptions) (int, error) {
	if db == nil {
		return 0, nil
	}
	rows, err := db.QueryContext(ctx, `
SELECT Name, Category, ManifestFormatVersion, SourceKind, SourceURI
FROM _System_InstalledPack
WHERE Category = ?
  AND Status = 'active'
  AND COALESCE(IsDeleted, 0) = 0
  AND COALESCE(SourceKind, '') <> ''
  AND COALESCE(SourceURI, '') <> ''
ORDER BY Name`, string(AppPack))
	if err != nil {
		return 0, fmt.Errorf("query active source-backed packs for ChildCloneRules ownership backfill: %w", err)
	}
	defer rows.Close()

	var total int
	for rows.Next() {
		var name, category string
		var manifestFormatVersion sql.NullString
		var sourceKind, sourceURI sql.NullString
		if err := rows.Scan(&name, &category, &manifestFormatVersion, &sourceKind, &sourceURI); err != nil {
			return total, fmt.Errorf("scan active source-backed pack for ChildCloneRules ownership backfill: %w", err)
		}
		name = strings.TrimSpace(name)
		if name == "" {
			continue
		}
		if options.defers(name) {
			continue
		}
		pack, err := resolveActiveInstalledSourceBackedPackWithStoredManifestVersionAndComponentKindRegistrationSet(ctx, db, name, Category(strings.TrimSpace(category)), sourceKind.String, sourceURI.String, manifestFormatVersion.String, &options.ComponentKindRegistrationSet)
		if err != nil {
			return total, fmt.Errorf("resolve active source-backed pack %s for ChildCloneRules ownership backfill: %w", name, err)
		}
		updated, err := backfillObjectSpecChildCloneRulePackOwnershipFromPack(ctx, db, pack)
		if err != nil {
			return total, err
		}
		total += updated
	}
	if err := rows.Err(); err != nil {
		return total, fmt.Errorf("iterate active source-backed packs for ChildCloneRules ownership backfill: %w", err)
	}
	if total > 0 {
		if _, err := ClassifyLegacyChildCloneRulesCustomized(ctx, db); err != nil {
			return total, fmt.Errorf("classify ChildCloneRules ownership after pack sidecar backfill: %w", err)
		}
	}
	return total, nil
}

func backfillObjectSpecChildCloneRulePackOwnershipFromPack(ctx context.Context, db *sql.DB, pack *SourceBackedPack) (int, error) {
	if pack == nil {
		return 0, nil
	}
	total := 0
	for _, component := range pack.Components() {
		if component.Kind() != ComponentKindObjectSpec {
			continue
		}
		specs, err := expectedObjectSpecsForChildCloneRulesOwnership(pack.Name(), component, "backfill")
		if err != nil {
			return total, err
		}
		updated, err := backfillObjectSpecChildCloneRulePackOwnership(ctx, db, pack.Name(), component.BusinessKey(), component.Manageability(), specs)
		if err != nil {
			return total, err
		}
		total += updated
	}
	return total, nil
}

func expectedObjectSpecsForChildCloneRulesOwnership(packName string, component PackComponent, purpose string) ([]ObjectSpec, error) {
	projection, err := component.ExpectedProjection()
	if err != nil {
		return nil, fmt.Errorf("%s object_spec %s projection for ChildCloneRules ownership %s: %w", packName, component.BusinessKey(), purpose, err)
	}
	specs, ok := projection.([]ObjectSpec)
	if !ok {
		return nil, fmt.Errorf("%s object_spec %s projection for ChildCloneRules ownership %s has type %T, want []ObjectSpec", packName, component.BusinessKey(), purpose, projection)
	}
	return append([]ObjectSpec(nil), specs...), nil
}

func backfillObjectSpecChildCloneRulePackOwnership(ctx context.Context, db *sql.DB, packName, businessKey string, manageability Manageability, specs []ObjectSpec) (int, error) {
	packName = strings.TrimSpace(packName)
	businessKey = strings.TrimSpace(businessKey)
	if packName == "" || businessKey == "" {
		return 0, fmt.Errorf("ChildCloneRules pack ownership backfill requires pack name and object_spec business key")
	}
	baseQuery := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = NOW() "+
			"WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0 AND ("+
			"(COALESCE(`%s`, '') = '' AND COALESCE(`%s`, '') = '') OR "+
			"(`%s` = ? AND `%s` = ? AND (`%s` IS NULL OR TRIM(`%s`) = '')))",
		constants.TableObject,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackManageability,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.FieldSysObject_ChildCloneRulesPackValue,
	)
	emptyDefaultsOnlyQuery := baseQuery + fmt.Sprintf(
		" AND (`%s` IS NULL OR TRIM(`%s`) = '' OR TRIM(`%s`) = '[]')",
		constants.FieldSysObject_ChildCloneRules,
		constants.FieldSysObject_ChildCloneRules,
		constants.FieldSysObject_ChildCloneRules,
	)
	updated := 0
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		encoded, err := canonicalChildCloneRulesPackValue(spec.ChildCloneRules)
		if err != nil {
			return updated, fmt.Errorf("encode pack ChildCloneRules ownership value for %s: %w", apiName, err)
		}
		query := baseQuery
		if manageability == ManageabilityDefaultsOnly && encoded == "[]" {
			// A sidecar-less defaults_only row with non-empty live rules may be a
			// removed old pack default or a retained L2 override. Repair must not
			// classify it before apply can converge the live value.
			query = emptyDefaultsOnlyQuery
		}
		objectID := domainschema.GenerateObjectID(apiName)
		res, err := db.ExecContext(
			ctx,
			query,
			packName,
			businessKey,
			string(manageability),
			encoded,
			objectID,
			packName,
			businessKey,
		)
		if err != nil {
			return updated, fmt.Errorf("backfill ChildCloneRules pack ownership for %s: %w", apiName, err)
		}
		if n, _ := res.RowsAffected(); n > 0 {
			updated += int(n)
		}
	}
	return updated, nil
}