package packs

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

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

type objectSpecExclusiveSiblingFieldApplySnapshot struct {
	id                string
	apiName           string
	liveFields        sql.NullString
	customized        bool
	packName          string
	packBusinessKey   string
	packManageability string
	packFields        sql.NullString
}

func prepareObjectSpecExclusiveSiblingFieldPackOwnershipForApply(ctx context.Context, db *sql.DB, packName, businessKey string, manageability Manageability, specs []ObjectSpec) error {
	packName = strings.TrimSpace(packName)
	businessKey = strings.TrimSpace(businessKey)
	rows, err := loadObjectSpecExclusiveSiblingFieldApplySnapshots(ctx, db, specs)
	if err != nil {
		return err
	}
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		row, ok := rows[domainschema.GenerateObjectID(apiName)]
		if !ok || !exclusiveSiblingFieldsSpecNeedsApplyOwnership(packName, businessKey, spec, row) {
			continue
		}
		if err := validateObjectSpecExclusiveSiblingFieldApplyOwnership(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
		}
		if !exclusiveSiblingFieldsSpecNeedsApplyOwnership(packName, businessKey, spec, row) {
			continue
		}
		packValue, err := canonicalExclusiveSiblingFieldsPackValue(spec.ExclusiveSiblingFields)
		if err != nil {
			return fmt.Errorf("encode pack ExclusiveSiblingFields ownership value for %s: %w", apiName, err)
		}
		mark, err := shouldMarkLegacyExclusiveSiblingFieldsCustomized(row, packName, businessKey, manageability, packValue)
		if err != nil {
			return err
		}
		if mark {
			markCustomizedIDs = append(markCustomizedIDs, row.id)
		}
	}
	return markExclusiveSiblingFieldsCustomized(ctx, db, markCustomizedIDs)
}

func persistObjectSpecExclusiveSiblingFieldPackOwnership(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 ExclusiveSiblingFields pack ownership persistence")
	}
	packName = strings.TrimSpace(packName)
	businessKey = strings.TrimSpace(businessKey)
	if packName == "" || businessKey == "" {
		return fmt.Errorf("ExclusiveSiblingFields pack ownership requires pack name and object_spec business key")
	}
	var rows map[string]objectSpecExclusiveSiblingFieldApplySnapshot
	for _, spec := range specs {
		if objectSpecDeclaresExclusiveSiblingFields(spec) {
			continue
		}
		var err error
		rows, err = loadObjectSpecExclusiveSiblingFieldApplySnapshots(ctx, db, specs)
		if err != nil {
			return err
		}
		break
	}
	query := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = ?, `%s` = ?, `%s` = ?, `%s` = NOW() WHERE `%s` = ?",
		constants.TableObject,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		if !objectSpecDeclaresExclusiveSiblingFields(spec) {
			row, ok := rows[domainschema.GenerateObjectID(apiName)]
			if !ok || !exclusiveSiblingFieldsApplySnapshotOwnedBy(row, packName, businessKey) {
				continue
			}
		}
		encoded, err := canonicalExclusiveSiblingFieldsPackValue(spec.ExclusiveSiblingFields)
		if err != nil {
			return fmt.Errorf("encode pack ExclusiveSiblingFields 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 ExclusiveSiblingFields 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 ExclusiveSiblingFields pack ownership for %s: check row existence: %w", apiName, existsErr)
				}
				if !exists {
					return fmt.Errorf("persist ExclusiveSiblingFields pack ownership for %s updated 0 rows", apiName)
				}
			}
		}
	}
	return nil
}

func loadObjectSpecExclusiveSiblingFieldApplySnapshots(ctx context.Context, db *sql.DB, specs []ObjectSpec) (map[string]objectSpecExclusiveSiblingFieldApplySnapshot, error) {
	if db == nil {
		return nil, fmt.Errorf("db is unavailable for ExclusiveSiblingFields 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]objectSpecExclusiveSiblingFieldApplySnapshot, 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_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		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 ExclusiveSiblingFields object_spec ownership snapshots: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var row objectSpecExclusiveSiblingFieldApplySnapshot
		var customized int
		if err := rows.Scan(&row.id, &row.apiName, &row.liveFields, &customized, &row.packName, &row.packBusinessKey, &row.packManageability, &row.packFields); err != nil {
			return nil, fmt.Errorf("scan ExclusiveSiblingFields 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 ExclusiveSiblingFields object_spec ownership snapshots: %w", err)
	}
	return out, nil
}

func validateObjectSpecExclusiveSiblingFieldApplyOwnership(packName, businessKey string, row objectSpecExclusiveSiblingFieldApplySnapshot) 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 ExclusiveSiblingFields 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 exclusiveSiblingFieldsSpecNeedsApplyOwnership(packName, businessKey string, spec ObjectSpec, row objectSpecExclusiveSiblingFieldApplySnapshot) bool {
	return objectSpecDeclaresExclusiveSiblingFields(spec) || exclusiveSiblingFieldsApplySnapshotOwnedBy(row, packName, businessKey)
}

func exclusiveSiblingFieldsApplySnapshotOwnedBy(row objectSpecExclusiveSiblingFieldApplySnapshot, packName, businessKey string) bool {
	return row.packName == strings.TrimSpace(packName) && row.packBusinessKey == strings.TrimSpace(businessKey) && (row.packName != "" || row.packBusinessKey != "")
}

func objectSpecDeclaresExclusiveSiblingFields(spec ObjectSpec) bool {
	return len(spec.ExclusiveSiblingFields) > 0
}

func shouldMarkLegacyExclusiveSiblingFieldsCustomized(row objectSpecExclusiveSiblingFieldApplySnapshot, packName, businessKey string, manageability Manageability, currentPackValue string) (bool, error) {
	if manageability != ManageabilityDefaultsOnly || row.customized {
		return false, nil
	}
	if !row.liveFields.Valid || strings.TrimSpace(row.liveFields.String) == "" {
		return false, nil
	}
	live, err := canonicalExclusiveSiblingFieldsJSON(row.liveFields)
	if err != nil {
		return false, fmt.Errorf("canonicalize live ExclusiveSiblingFields 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 := canonicalExclusiveSiblingFieldsJSON(row.packFields)
		if err != nil {
			return false, fmt.Errorf("canonicalize previous pack ExclusiveSiblingFields for %s: %w", row.apiName, err)
		}
		if string(previousPack) != currentPackValue {
			return false, nil
		}
		compareTo = string(previousPack)
	}
	return string(live) != compareTo, nil
}

func markExclusiveSiblingFieldsCustomized(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_ExclusiveSiblingFieldsCustomized,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
	)
	for _, id := range ids {
		if _, err := db.ExecContext(ctx, query, id); err != nil {
			return fmt.Errorf("mark ExclusiveSiblingFields customized for %s: %w", id, err)
		}
	}
	return nil
}

type ObjectSpecExclusiveSiblingFieldPackOwnershipSnapshot struct {
	rows []objectSpecExclusiveSiblingFieldSidecarSnapshot
}

type objectSpecExclusiveSiblingFieldSidecarSnapshot struct {
	id                string
	exists            bool
	customized        bool
	packName          sql.NullString
	packBusinessKey   sql.NullString
	packManageability sql.NullString
	packFields        sql.NullString
}

func SnapshotObjectSpecExclusiveSiblingFieldPackOwnershipForPack(ctx context.Context, db *sql.DB, pack *SourceBackedPack) (*ObjectSpecExclusiveSiblingFieldPackOwnershipSnapshot, error) {
	if db == nil || pack == nil {
		return &ObjectSpecExclusiveSiblingFieldPackOwnershipSnapshot{}, nil
	}
	ids, err := objectSpecExclusiveSiblingFieldObjectIDsForPack(pack)
	if err != nil {
		return nil, err
	}
	return snapshotObjectSpecExclusiveSiblingFieldPackOwnership(ctx, db, ids)
}

func objectSpecExclusiveSiblingFieldObjectIDsForPack(pack *SourceBackedPack) ([]string, error) {
	seen := map[string]struct{}{}
	for _, component := range pack.Components() {
		if component.Kind() != ComponentKindObjectSpec {
			continue
		}
		specs, err := expectedObjectSpecsForExclusiveSiblingFieldsOwnership(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 snapshotObjectSpecExclusiveSiblingFieldPackOwnership(ctx context.Context, db *sql.DB, ids []string) (*ObjectSpecExclusiveSiblingFieldPackOwnershipSnapshot, error) {
	snapshot := &ObjectSpecExclusiveSiblingFieldPackOwnershipSnapshot{
		rows: make([]objectSpecExclusiveSiblingFieldSidecarSnapshot, 0, len(ids)),
	}
	if len(ids) == 0 {
		return snapshot, nil
	}
	byID := make(map[string]int, len(ids))
	for _, id := range ids {
		row := objectSpecExclusiveSiblingFieldSidecarSnapshot{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_ExclusiveSiblingFieldsCustomized,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		constants.TableObject,
		constants.FieldID,
		strings.Join(placeholders, ","),
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("snapshot ExclusiveSiblingFields pack ownership sidecars: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var row objectSpecExclusiveSiblingFieldSidecarSnapshot
		var customized int
		if err := rows.Scan(&row.id, &customized, &row.packName, &row.packBusinessKey, &row.packManageability, &row.packFields); err != nil {
			return nil, fmt.Errorf("scan ExclusiveSiblingFields 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 ExclusiveSiblingFields pack ownership sidecar snapshot: %w", err)
	}
	return snapshot, nil
}

func (s *ObjectSpecExclusiveSiblingFieldPackOwnershipSnapshot) 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_ExclusiveSiblingFieldsCustomized,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		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.packFields), row.id); err != nil {
			return fmt.Errorf("restore ExclusiveSiblingFields pack ownership sidecar for %s: %w", row.id, err)
		}
	}
	return nil
}

// BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacks repairs
// rows installed before the ExclusiveSiblingFieldsPack* sidecar columns
// existed. Active _System_InstalledPack rows remain the app-pack ownership
// SSoT, mirroring ChildCloneRules ownership hydration.
func BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacks(ctx context.Context, db *sql.DB) (int, error) {
	return BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacksWithOptions(ctx, db, ActivePackBackfillOptions{})
}

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

// BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacksWithOptions
// defers packs already planned for component apply and resolves every other
// active declaration from its installed source.
func BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacksWithOptions(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 ExclusiveSiblingFields 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 ExclusiveSiblingFields 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 ExclusiveSiblingFields ownership backfill: %w", name, err)
		}
		updated, err := backfillObjectSpecExclusiveSiblingFieldPackOwnershipFromPack(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 ExclusiveSiblingFields ownership backfill: %w", err)
	}
	return total, nil
}

func backfillObjectSpecExclusiveSiblingFieldPackOwnershipFromPack(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 := expectedObjectSpecsForExclusiveSiblingFieldsOwnership(pack.Name(), component, "backfill")
		if err != nil {
			return total, err
		}
		updated, err := backfillObjectSpecExclusiveSiblingFieldPackOwnership(ctx, db, pack.Name(), component.BusinessKey(), component.Manageability(), specs)
		if err != nil {
			return total, err
		}
		total += updated
	}
	return total, nil
}

func expectedObjectSpecsForExclusiveSiblingFieldsOwnership(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 ExclusiveSiblingFields ownership %s: %w", packName, component.BusinessKey(), purpose, err)
	}
	specs, ok := projection.([]ObjectSpec)
	if !ok {
		return nil, fmt.Errorf("%s object_spec %s projection for ExclusiveSiblingFields ownership %s has type %T, want []ObjectSpec", packName, component.BusinessKey(), purpose, projection)
	}
	return append([]ObjectSpec(nil), specs...), nil
}

func backfillObjectSpecExclusiveSiblingFieldPackOwnership(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("ExclusiveSiblingFields 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_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
	)
	emptyDefaultsOnlyQuery := baseQuery + fmt.Sprintf(
		" AND (`%s` IS NULL OR TRIM(`%s`) = '' OR TRIM(`%s`) = '[]')",
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFields,
	)
	updated := 0
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		if !objectSpecDeclaresExclusiveSiblingFields(spec) {
			continue
		}
		encoded, err := canonicalExclusiveSiblingFieldsPackValue(spec.ExclusiveSiblingFields)
		if err != nil {
			return updated, fmt.Errorf("encode pack ExclusiveSiblingFields ownership value for %s: %w", apiName, err)
		}
		query := baseQuery
		if manageability == ManageabilityDefaultsOnly && encoded == "[]" {
			// A sidecar-less defaults_only row with non-empty live metadata may
			// be a retained L2 override. Repair must not claim that row until
			// 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 ExclusiveSiblingFields pack ownership for %s: %w", apiName, err)
		}
		if n, _ := res.RowsAffected(); n > 0 {
			updated += int(n)
		}
	}
	return updated, nil
}