package tenantoverlay

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

	"github.com/nexuscrm/records"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

type exclusiveSiblingFieldsPackOwnership struct {
	ObjectAPIName        string
	PackName             string
	BusinessKey          string
	Manageability        packs.Manageability
	PackValue            string
	Fields               []models.ExclusiveSiblingField
	MissingPackOwnership bool
}

func validateTenantExclusiveSiblingFieldsSpecs(specs []packs.ObjectSpec) error {
	for _, spec := range specs {
		if !tenantObjectSpecDeclaresExclusiveSiblingFields(spec) {
			continue
		}
		if !isTenantCustomObjectSpec(spec) {
			return fmt.Errorf("tenant object %s declares exclusiveSiblingFields on non-custom object; standard-object overlay declarations are unsupported in %s, use %s", spec.APIName, ObjectsSpecFile, ObjectExclusiveSiblingFieldsFile)
		}
	}
	return nil
}

func tenantObjectSpecDeclaresExclusiveSiblingFields(spec packs.ObjectSpec) bool {
	return spec.ExclusiveSiblingFields != nil
}

func validateTenantExclusiveSiblingFieldsPreflight(ctx context.Context, deps packs.ApplyDeps, specs []packs.ObjectSpec) error {
	if len(specs) == 0 {
		return nil
	}
	var fallback records.DefaultChildRowsSchemaSnapshot
	if deps.Metadata != nil {
		fallback = deps.Metadata
	}
	if err := packs.ValidateExclusiveSiblingFieldsForSpecs(ctx, specs, fallback, false); err != nil {
		return fmt.Errorf("tenant exclusiveSiblingFields preflight: %w", err)
	}
	return nil
}

// applyObjectExclusiveSiblingFieldsOverlay updates standard-object
// `_System_Object.ExclusiveSiblingFields` from the managed L2 overlay file.
// Custom-object declarations remain owned by tenant_objects_spec.json so the
// same executable metadata fact has one authoring surface per object layer.
func (p *Pack) applyObjectExclusiveSiblingFieldsOverlay(ctx context.Context, db *sql.DB, specs []packs.ObjectSpec, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	fieldsByObject, ok, err := p.readObjectExclusiveSiblingFieldsFile()
	if err != nil {
		return err
	}
	if !ok {
		return nil
	}
	if err := rejectCustomObjectExclusiveSiblingFieldsFileObjects(specs, fieldsByObject); err != nil {
		return err
	}
	if err := rejectLockedObjectExclusiveSiblingFieldsOverlays(fieldsByObject, ownership); err != nil {
		return err
	}
	keep := exclusiveSiblingFieldsOverlayKeepSet(fieldsByObject)
	keepClause, args, err := preflightMissingExclusiveSiblingFieldsOverlayCleanup(ctx, db, keep, ObjectExclusiveSiblingFieldsFile, ownership)
	if err != nil {
		return err
	}
	if err := applyExclusiveSiblingFieldsOverlayMap(ctx, db, fieldsByObject, ObjectExclusiveSiblingFieldsFile); err != nil {
		return err
	}
	return mutateMissingExclusiveSiblingFieldsOverlays(ctx, db, keep, keepClause, args, ObjectExclusiveSiblingFieldsFile, ownership)
}

func applyExclusiveSiblingFieldsOverlayMap(ctx context.Context, db *sql.DB, fieldsByObject map[string][]models.ExclusiveSiblingField, source string) error {
	if len(fieldsByObject) == 0 {
		return nil
	}
	if db == nil {
		return fmt.Errorf("db is unavailable for tenant %s exclusiveSiblingFields apply", source)
	}
	apiNames := make([]string, 0, len(fieldsByObject))
	for apiName := range fieldsByObject {
		apiNames = append(apiNames, apiName)
	}
	sort.Strings(apiNames)
	for _, apiName := range apiNames {
		fields := fieldsByObject[apiName]
		if fields == nil {
			fields = []models.ExclusiveSiblingField{}
		}
		raw, err := json.Marshal(fields)
		if err != nil {
			return fmt.Errorf("encode ExclusiveSiblingFields for %s: %w", apiName, err)
		}
		if err := updateExclusiveSiblingFieldsOverlay(ctx, db, apiName, string(raw), source); err != nil {
			return err
		}
		log.Printf("   ✅ ExclusiveSiblingFields seeded on %s (%d rules)", apiName, len(fields))
	}
	return nil
}

func updateExclusiveSiblingFieldsOverlay(ctx context.Context, db *sql.DB, apiName string, encoded any, source string) error {
	query := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ?, `%s` = 1, `%s` = NOW() WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0",
		constants.TableObject,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
		constants.FieldLastModifiedDate,
		constants.FieldAPIName,
		constants.FieldIsDeleted,
	)
	res, err := db.ExecContext(ctx, query, encoded, apiName)
	if err != nil {
		return fmt.Errorf("update ExclusiveSiblingFields for %s from %s: %w", apiName, source, err)
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("read ExclusiveSiblingFields update result for %s from %s: %w", apiName, source, err)
	}
	if affected == 0 {
		var exists int
		existsQuery := fmt.Sprintf(
			"SELECT COUNT(*) FROM `%s` WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0",
			constants.TableObject,
			constants.FieldAPIName,
			constants.FieldIsDeleted,
		)
		if err := db.QueryRowContext(ctx, existsQuery, apiName).Scan(&exists); err != nil {
			return fmt.Errorf("verify authoritative object row for ExclusiveSiblingFields %s from %s: %w", apiName, source, err)
		}
		if exists != 1 {
			return fmt.Errorf(
				"ExclusiveSiblingFields overlay %s for %q updated 0 rows on %s -- "+
					"authoritative non-deleted ApiName row missing. Check apply-tenant ordering: object metadata must land before ExclusiveSiblingFields",
				source, apiName, constants.TableObject,
			)
		}
	}
	return nil
}

func (p *Pack) readObjectExclusiveSiblingFieldsFile() (map[string][]models.ExclusiveSiblingField, bool, error) {
	raw, ok, err := p.readOptional(ObjectExclusiveSiblingFieldsFile)
	if err != nil {
		return nil, false, err
	}
	if !ok {
		return nil, false, nil
	}
	fieldsByObject := map[string][]models.ExclusiveSiblingField{}
	if err := json.Unmarshal(raw, &fieldsByObject); err != nil {
		return nil, false, fmt.Errorf("tenant %s is not parseable: %w", ObjectExclusiveSiblingFieldsFile, err)
	}
	return fieldsByObject, true, nil
}

func (p *Pack) preflightObjectExclusiveSiblingFieldsOverlayCleanup(ctx context.Context, db *sql.DB, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	fieldsByObject, ok, err := p.readObjectExclusiveSiblingFieldsFile()
	if err != nil || !ok {
		return err
	}
	_, _, err = preflightMissingExclusiveSiblingFieldsOverlayCleanup(ctx, db, exclusiveSiblingFieldsOverlayKeepSet(fieldsByObject), ObjectExclusiveSiblingFieldsFile, ownership)
	return err
}

func exclusiveSiblingFieldsOverlayKeepSet(fieldsByObject map[string][]models.ExclusiveSiblingField) map[string]bool {
	keep := make(map[string]bool, len(fieldsByObject))
	for apiName := range fieldsByObject {
		key := strings.ToLower(strings.TrimSpace(apiName))
		if key != "" {
			keep[key] = true
		}
	}
	return keep
}

func (p *Pack) validateObjectExclusiveSiblingFieldsFilePayload(ctx context.Context, deps packs.ApplyDeps, specs []packs.ObjectSpec, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	fieldsByObject, ok, err := p.readObjectExclusiveSiblingFieldsFile()
	if err != nil || !ok {
		return err
	}
	if err := rejectCustomObjectExclusiveSiblingFieldsFileObjects(specs, fieldsByObject); err != nil {
		return err
	}
	if err := rejectLockedObjectExclusiveSiblingFieldsOverlays(fieldsByObject, ownership); err != nil {
		return err
	}
	return validateObjectExclusiveSiblingFieldsFilePreflight(ctx, deps, specs, fieldsByObject)
}

func rejectCustomObjectExclusiveSiblingFieldsFileObjects(specs []packs.ObjectSpec, fieldsByObject map[string][]models.ExclusiveSiblingField) error {
	customSpecs := map[string]bool{}
	for _, spec := range specs {
		if !isTenantCustomObjectSpec(spec) {
			continue
		}
		key := strings.ToLower(strings.TrimSpace(spec.APIName))
		if key != "" {
			customSpecs[key] = true
		}
	}
	for apiName := range fieldsByObject {
		trimmed := strings.TrimSpace(apiName)
		key := strings.ToLower(trimmed)
		if key == "" {
			continue
		}
		if customSpecs[key] || strings.HasSuffix(key, "__c") {
			return fmt.Errorf(
				"%s declares exclusiveSiblingFields for custom object %s; custom-object exclusiveSiblingFields belong in %s, while %s is reserved for standard-object L2 overrides",
				ObjectExclusiveSiblingFieldsFile,
				trimmed,
				ObjectsSpecFile,
				ObjectExclusiveSiblingFieldsFile,
			)
		}
	}
	return nil
}

func rejectLockedObjectExclusiveSiblingFieldsOverlays(fieldsByObject map[string][]models.ExclusiveSiblingField, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	for apiName := range fieldsByObject {
		key := strings.ToLower(strings.TrimSpace(apiName))
		owner, ok := ownership[key]
		if !ok || owner.MissingPackOwnership {
			return fmt.Errorf(
				"%s declares exclusiveSiblingFields for standard object %s, but _System_Object has no persisted ExclusiveSiblingFields pack ownership after active-pack ownership backfill; reapply the installed source-backed pack before managed L2 overrides",
				ObjectExclusiveSiblingFieldsFile,
				strings.TrimSpace(apiName),
			)
		}
		if owner.Manageability != packs.ManageabilityLocked {
			continue
		}
		return fmt.Errorf(
			"%s declares exclusiveSiblingFields for locked pack object %s owned by %s object_spec %s; locked object_spec ExclusiveSiblingFields are not L2-overridable",
			ObjectExclusiveSiblingFieldsFile,
			strings.TrimSpace(apiName),
			owner.PackName,
			owner.BusinessKey,
		)
	}
	return nil
}

func validateObjectExclusiveSiblingFieldsFilePreflight(ctx context.Context, deps packs.ApplyDeps, specs []packs.ObjectSpec, fieldsByObject map[string][]models.ExclusiveSiblingField) error {
	if len(fieldsByObject) == 0 {
		return nil
	}
	var fallback records.DefaultChildRowsSchemaSnapshot
	if deps.Metadata != nil {
		fallback = deps.Metadata
	}
	snapshot := newTenantChildCloneRulePreflightSnapshot(specs, fallback)
	parentSchemas := make([]*models.ObjectMetadata, 0, len(fieldsByObject))
	var missing []string
	for apiName, fields := range fieldsByObject {
		trimmed := strings.TrimSpace(apiName)
		if trimmed == "" {
			missing = append(missing, "<empty object apiName>")
			continue
		}
		schema := snapshot.GetSchema(ctx, trimmed)
		if schema == nil {
			missing = append(missing, trimmed)
			continue
		}
		clone := models.CloneWithAdditionalFields(schema, nil)
		clone.ExclusiveSiblingFields = append([]models.ExclusiveSiblingField(nil), fields...)
		parentSchemas = append(parentSchemas, clone)
	}
	if len(missing) > 0 {
		sort.Strings(missing)
		return fmt.Errorf("tenant %s preflight: object(s) not found in metadata: %s", ObjectExclusiveSiblingFieldsFile, strings.Join(missing, ", "))
	}
	if err := packs.ValidateObjectExclusiveSiblingFields(ctx, snapshot, parentSchemas); err != nil {
		return fmt.Errorf("tenant %s preflight: %w", ObjectExclusiveSiblingFieldsFile, err)
	}
	return nil
}

func (p *Pack) loadExclusiveSiblingFieldsPackOwnershipForApply(ctx context.Context, db *sql.DB) (map[string]exclusiveSiblingFieldsPackOwnership, error) {
	if db == nil {
		return nil, nil
	}
	targets := map[string]bool{}
	fieldsByObject, ok, err := p.readObjectExclusiveSiblingFieldsFile()
	if err != nil {
		return nil, err
	}
	if !ok {
		return nil, nil
	}
	for apiName := range fieldsByObject {
		key := strings.ToLower(strings.TrimSpace(apiName))
		if key != "" {
			targets[key] = true
		}
	}
	return loadExclusiveSiblingFieldsPackOwnershipRows(ctx, db, targets)
}

func loadExclusiveSiblingFieldsPackOwnershipRows(ctx context.Context, db *sql.DB, targets map[string]bool) (map[string]exclusiveSiblingFieldsPackOwnership, error) {
	if db == nil {
		return nil, nil
	}
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, ''), `%s`, `%s` FROM `%s` "+
			"WHERE COALESCE(`%s`, 0) = 0 AND COALESCE(`%s`, 0) = 0 AND (COALESCE(`%s`, 0) <> 0",
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackValue,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.TableObject,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
	)
	args := make([]interface{}, 0, len(targets))
	keys := make([]string, 0, len(targets))
	for key := range targets {
		key = strings.ToLower(strings.TrimSpace(key))
		if key != "" {
			keys = append(keys, key)
		}
	}
	sort.Strings(keys)
	if len(keys) > 0 {
		placeholders := make([]string, 0, len(keys))
		for _, key := range keys {
			placeholders = append(placeholders, "?")
			args = append(args, key)
		}
		query += " OR LOWER(`" + constants.FieldSysObject_APIName + "`) IN (" + strings.Join(placeholders, ",") + ")"
	}
	query += ") ORDER BY `" + constants.FieldSysObject_APIName + "`"

	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("query ExclusiveSiblingFields pack ownership rows: %w", err)
	}
	defer rows.Close()

	ownership, err := scanExclusiveSiblingFieldsPackOwnershipRows(rows)
	if err != nil {
		return nil, err
	}
	if len(ownership) == 0 {
		return nil, nil
	}
	return ownership, nil
}

func scanExclusiveSiblingFieldsPackOwnershipRows(rows *sql.Rows) (map[string]exclusiveSiblingFieldsPackOwnership, error) {
	ownership := map[string]exclusiveSiblingFieldsPackOwnership{}
	for rows.Next() {
		var apiName, packName, businessKey, manageability string
		var rawFields, liveFields sql.NullString
		if err := rows.Scan(&apiName, &packName, &businessKey, &manageability, &rawFields, &liveFields); err != nil {
			return nil, fmt.Errorf("scan ExclusiveSiblingFields pack ownership row: %w", err)
		}
		apiName = strings.TrimSpace(apiName)
		packName = strings.TrimSpace(packName)
		businessKey = strings.TrimSpace(businessKey)
		manageability = strings.TrimSpace(manageability)
		if apiName == "" {
			continue
		}
		key := strings.ToLower(apiName)
		if packName == "" || businessKey == "" || manageability == "" || !rawFields.Valid || strings.TrimSpace(rawFields.String) == "" {
			ownership[key] = exclusiveSiblingFieldsPackOwnership{
				ObjectAPIName:        apiName,
				MissingPackOwnership: true,
			}
			continue
		}
		var fields []models.ExclusiveSiblingField
		if err := json.Unmarshal([]byte(rawFields.String), &fields); err != nil {
			return nil, fmt.Errorf("parse pack ExclusiveSiblingFields ownership value for %s: %w", apiName, err)
		}
		if prev, exists := ownership[key]; exists {
			return nil, fmt.Errorf(
				"object %s exclusiveSiblingFields ownership declared by both %s object_spec %s and %s object_spec %s",
				apiName,
				prev.PackName,
				prev.BusinessKey,
				packName,
				businessKey,
			)
		}
		ownership[key] = exclusiveSiblingFieldsPackOwnership{
			ObjectAPIName: apiName,
			PackName:      packName,
			BusinessKey:   businessKey,
			Manageability: packs.Manageability(manageability),
			PackValue:     strings.TrimSpace(rawFields.String),
			Fields:        append([]models.ExclusiveSiblingField(nil), fields...),
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate ExclusiveSiblingFields pack ownership rows: %w", err)
	}
	return ownership, nil
}

func clearMissingExclusiveSiblingFieldsOverlays(ctx context.Context, db *sql.DB, keep map[string]bool, source string, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	keepClause, args, err := preflightMissingExclusiveSiblingFieldsOverlayCleanup(ctx, db, keep, source, ownership)
	if err != nil {
		return err
	}
	return mutateMissingExclusiveSiblingFieldsOverlays(ctx, db, keep, keepClause, args, source, ownership)
}

func preflightMissingExclusiveSiblingFieldsOverlayCleanup(ctx context.Context, db *sql.DB, keep map[string]bool, source string, ownership map[string]exclusiveSiblingFieldsPackOwnership) (string, []interface{}, error) {
	if db == nil {
		return "", nil, nil
	}
	clearKeep := make(map[string]bool, len(keep))
	for key, value := range keep {
		if value {
			clearKeep[strings.ToLower(strings.TrimSpace(key))] = true
		}
	}
	keepClause, args := exclusiveSiblingFieldsKeepExclusion(clearKeep)
	if err := rejectUnresolvedSidecarlessExclusiveSiblingFields(ctx, db, source, keepClause, args); err != nil {
		return "", nil, err
	}
	if err := rejectMissingExclusiveSiblingFieldsPackOwnershipForCleanup(keep, ownership, source); err != nil {
		return "", nil, err
	}
	return keepClause, args, nil
}

func mutateMissingExclusiveSiblingFieldsOverlays(ctx context.Context, db *sql.DB, keep map[string]bool, keepClause string, args []interface{}, source string, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	if db == nil {
		return nil
	}
	if _, err := restoreMissingPackExclusiveSiblingFieldsOverlays(ctx, db, keep, ownership, source); err != nil {
		return err
	}
	query := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = NULL, `%s` = 0, `%s` = NOW() WHERE COALESCE(`%s`, 0) = 0 AND COALESCE(`%s`, 0) <> 0",
		constants.TableObject,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
		constants.FieldLastModifiedDate,
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
	)
	query += keepClause
	res, err := db.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("clear stale ExclusiveSiblingFields overlays for %s: %w", source, err)
	}
	affected, _ := res.RowsAffected()
	if affected > 0 {
		log.Printf("   ✅ cleared %d stale ExclusiveSiblingFields overlay rows for %s", affected, source)
	}
	return nil
}

func exclusiveSiblingFieldsKeepExclusion(clearKeep map[string]bool) (string, []interface{}) {
	args := make([]interface{}, 0, len(clearKeep))
	if len(clearKeep) == 0 {
		return "", args
	}
	keys := make([]string, 0, len(clearKeep))
	for key := range clearKeep {
		if strings.TrimSpace(key) != "" {
			keys = append(keys, strings.ToLower(strings.TrimSpace(key)))
		}
	}
	sort.Strings(keys)
	if len(keys) == 0 {
		return "", args
	}
	placeholders := make([]string, 0, len(keys))
	for _, key := range keys {
		placeholders = append(placeholders, "?")
		args = append(args, key)
	}
	return " AND LOWER(`" + constants.FieldSysObject_APIName + "`) NOT IN (" + strings.Join(placeholders, ",") + ")", args
}

func rejectMissingExclusiveSiblingFieldsPackOwnershipForCleanup(keep map[string]bool, ownership map[string]exclusiveSiblingFieldsPackOwnership, source string) error {
	if len(ownership) == 0 {
		return nil
	}
	var unresolved []string
	for key, owner := range ownership {
		if keep[strings.ToLower(strings.TrimSpace(key))] {
			continue
		}
		if owner.MissingPackOwnership {
			unresolved = append(unresolved, owner.ObjectAPIName)
		}
	}
	if len(unresolved) == 0 {
		return nil
	}
	sort.Strings(unresolved)
	return fmt.Errorf(
		"cannot safely clean ExclusiveSiblingFields rows with incomplete pack ownership while applying %s: %s. "+
			"Run the active-pack ExclusiveSiblingFields ownership backfill/reconcile before apply-tenant, "+
			"or explicitly reauthor/clear the tenant-owned value through current metadata before removing the legacy source.",
		source,
		strings.Join(unresolved, ", "),
	)
}

func rejectUnresolvedSidecarlessExclusiveSiblingFields(ctx context.Context, db *sql.DB, source string, keepClause string, args []interface{}) error {
	query := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE COALESCE(`%s`, 0) = 0 AND COALESCE(`%s`, 0) = 0 AND `%s` IS NOT NULL "+
			"AND TRIM(`%s`) <> '' AND TRIM(`%s`) <> '[]' AND COALESCE(`%s`, '') = '' AND COALESCE(`%s`, '') = ''",
		constants.FieldSysObject_APIName,
		constants.TableObject,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
	)
	query += keepClause
	query += " ORDER BY `" + constants.FieldSysObject_APIName + "`"
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("query unresolved sidecar-less ExclusiveSiblingFields rows for %s: %w", source, err)
	}
	defer rows.Close()
	var unresolved []string
	for rows.Next() {
		var apiName string
		if err := rows.Scan(&apiName); err != nil {
			return fmt.Errorf("scan unresolved sidecar-less ExclusiveSiblingFields rows for %s: %w", source, err)
		}
		unresolved = append(unresolved, apiName)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate unresolved sidecar-less ExclusiveSiblingFields rows for %s: %w", source, err)
	}
	if len(unresolved) == 0 {
		return nil
	}
	return fmt.Errorf(
		"cannot safely clean unresolved sidecar-less ExclusiveSiblingFields rows while applying %s: %s. "+
			"Run the active-pack ExclusiveSiblingFields ownership backfill/reconcile before apply-tenant, "+
			"or explicitly reauthor/clear the tenant-owned value through current metadata before removing the legacy source.",
		source,
		strings.Join(unresolved, ", "),
	)
}

func restoreMissingPackExclusiveSiblingFieldsOverlays(ctx context.Context, db *sql.DB, keep map[string]bool, ownership map[string]exclusiveSiblingFieldsPackOwnership, source string) (map[string]bool, error) {
	if len(ownership) == 0 {
		return nil, nil
	}
	restored := make(map[string]bool)
	keys := make([]string, 0, len(ownership))
	for key := range ownership {
		keys = append(keys, key)
	}
	sort.Strings(keys)
	for _, key := range keys {
		if keep[strings.ToLower(strings.TrimSpace(key))] {
			continue
		}
		owner := ownership[key]
		if owner.MissingPackOwnership {
			continue
		}
		encoded := owner.PackValue
		if strings.TrimSpace(encoded) == "" {
			fields := owner.Fields
			if fields == nil {
				fields = []models.ExclusiveSiblingField{}
			}
			raw, err := json.Marshal(fields)
			if err != nil {
				return nil, fmt.Errorf("encode pack ExclusiveSiblingFields for %s: %w", owner.ObjectAPIName, err)
			}
			encoded = string(raw)
		}
		query := fmt.Sprintf(
			"UPDATE `%s` SET `%s` = ?, `%s` = 0, `%s` = NOW() WHERE COALESCE(`%s`, 0) = 0 AND COALESCE(`%s`, 0) <> 0 AND LOWER(`%s`) = ?",
			constants.TableObject,
			constants.FieldSysObject_ExclusiveSiblingFields,
			constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
			constants.FieldLastModifiedDate,
			constants.FieldSysObject_IsCustom,
			constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
			constants.FieldSysObject_APIName,
		)
		res, err := db.ExecContext(ctx, query, encoded, key)
		if err != nil {
			return nil, fmt.Errorf("restore pack ExclusiveSiblingFields for %s after removing %s override: %w", owner.ObjectAPIName, source, err)
		}
		affected, _ := res.RowsAffected()
		if affected > 0 {
			log.Printf("   ✅ restored %s ExclusiveSiblingFields from pack %s after removing %s overlay", owner.ObjectAPIName, owner.PackName, source)
			restored[key] = true
		}
	}
	return restored, nil
}

func loadCustomExclusiveSiblingFieldsPackOwnershipForApply(ctx context.Context, db *sql.DB, specs []packs.ObjectSpec) (map[string]exclusiveSiblingFieldsPackOwnership, error) {
	if db == nil {
		return nil, nil
	}
	targets := map[string]bool{}
	for _, spec := range specs {
		if !isTenantCustomObjectSpec(spec) {
			continue
		}
		key := strings.ToLower(strings.TrimSpace(spec.APIName))
		if key != "" {
			targets[key] = true
		}
	}
	return loadCustomExclusiveSiblingFieldsPackOwnershipRows(ctx, db, targets)
}

func loadCustomExclusiveSiblingFieldsPackOwnershipRows(ctx context.Context, db *sql.DB, targets map[string]bool) (map[string]exclusiveSiblingFieldsPackOwnership, error) {
	if db == nil || len(targets) == 0 {
		return nil, nil
	}
	query := fmt.Sprintf(
		"SELECT o.`%s`, COALESCE(o.`%s`, ''), COALESCE(o.`%s`, ''), COALESCE(o.`%s`, ''), o.`%s`, COALESCE(o.`%s`, 0), "+
			"COALESCE((SELECT COUNT(*) FROM `%s` f WHERE f.`%s` = o.`%s` AND COALESCE(f.`%s`, 0) = 0 AND COALESCE(f.`%s`, '') IN ('%s', '%s') AND COALESCE(f.`%s`, '') <> ''), 0) "+
			"FROM `%s` o WHERE COALESCE(o.`%s`, 0) = 0 AND COALESCE(o.`%s`, 0) <> 0 AND LOWER(o.`%s`) IN (%s) ORDER BY o.`%s`",
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackName,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackBusinessKey,
		constants.FieldSysObject_ExclusiveSiblingFieldsPackManageability,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
		constants.TableField,
		constants.FieldSysField_ObjectID,
		constants.FieldID,
		constants.FieldSysField_IsDeleted,
		constants.FieldSysField_OwnerKind,
		constants.FieldOwnerKindPack,
		constants.FieldOwnerKindPackRemoving,
		constants.FieldSysField_OwnerPack,
		constants.TableObject,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_APIName,
		strings.Join(sqlPlaceholders(len(targets)), ","),
		constants.FieldSysObject_APIName,
	)
	args := make([]interface{}, 0, len(targets))
	keys := make([]string, 0, len(targets))
	for key := range targets {
		key = strings.ToLower(strings.TrimSpace(key))
		if key != "" {
			keys = append(keys, key)
		}
	}
	sort.Strings(keys)
	for _, key := range keys {
		args = append(args, key)
	}

	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("query custom ExclusiveSiblingFields pack ownership rows: %w", err)
	}
	defer rows.Close()

	ownership, err := scanCustomExclusiveSiblingFieldsPackOwnershipRows(rows)
	if err != nil {
		return nil, err
	}
	if len(ownership) == 0 {
		return nil, nil
	}
	return ownership, nil
}

func scanCustomExclusiveSiblingFieldsPackOwnershipRows(rows *sql.Rows) (map[string]exclusiveSiblingFieldsPackOwnership, error) {
	ownership := map[string]exclusiveSiblingFieldsPackOwnership{}
	for rows.Next() {
		var apiName, packName, businessKey, manageability string
		var liveFields sql.NullString
		var customized, packOwnedFieldCount int
		if err := rows.Scan(&apiName, &packName, &businessKey, &manageability, &liveFields, &customized, &packOwnedFieldCount); err != nil {
			return nil, fmt.Errorf("scan custom ExclusiveSiblingFields pack ownership row: %w", err)
		}
		apiName = strings.TrimSpace(apiName)
		packName = strings.TrimSpace(packName)
		businessKey = strings.TrimSpace(businessKey)
		manageability = strings.TrimSpace(manageability)
		if apiName == "" {
			continue
		}
		key := strings.ToLower(apiName)
		if packName == "" {
			if packOwnedFieldCount > 0 || (customized == 0 && liveFields.Valid && strings.TrimSpace(liveFields.String) != "") {
				ownership[key] = exclusiveSiblingFieldsPackOwnership{
					ObjectAPIName:        apiName,
					MissingPackOwnership: true,
				}
			}
			continue
		}
		if prev, exists := ownership[key]; exists {
			return nil, fmt.Errorf(
				"object %s exclusiveSiblingFields ownership declared by both %s object_spec %s and %s object_spec %s",
				apiName,
				prev.PackName,
				prev.BusinessKey,
				packName,
				businessKey,
			)
		}
		ownership[key] = exclusiveSiblingFieldsPackOwnership{
			ObjectAPIName: apiName,
			PackName:      packName,
			BusinessKey:   businessKey,
			Manageability: packs.Manageability(manageability),
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate custom ExclusiveSiblingFields pack ownership rows: %w", err)
	}
	return ownership, nil
}

func rejectPackOwnedTenantObjectSpecExclusiveSiblingFields(specs []packs.ObjectSpec, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	if len(ownership) == 0 {
		return nil
	}
	for _, spec := range specs {
		if !tenantObjectSpecDeclaresExclusiveSiblingFields(spec) {
			continue
		}
		key := strings.ToLower(strings.TrimSpace(spec.APIName))
		if key == "" {
			continue
		}
		owner, ok := ownership[key]
		if !ok {
			continue
		}
		if owner.MissingPackOwnership {
			return fmt.Errorf(
				"%s declares exclusiveSiblingFields for source-backed custom object %s, but _System_Object has no persisted ExclusiveSiblingFields pack ownership after active-pack ownership backfill; reapply the owning source-backed pack before managed L2 overrides",
				ObjectsSpecFile,
				strings.TrimSpace(spec.APIName),
			)
		}
		return fmt.Errorf(
			"%s declares exclusiveSiblingFields for source-backed custom object %s owned by %s object_spec %s; source-backed custom-object ExclusiveSiblingFields overrides are not supported in this tenant custom-object slice",
			ObjectsSpecFile,
			strings.TrimSpace(spec.APIName),
			owner.PackName,
			owner.BusinessKey,
		)
	}
	return nil
}

func applyTenantObjectSpecExclusiveSiblingFields(ctx context.Context, db *sql.DB, specs []packs.ObjectSpec, ownership map[string]exclusiveSiblingFieldsPackOwnership) error {
	updates := tenantObjectSpecExclusiveSiblingFields(specs, ownership)
	if len(updates) == 0 {
		return nil
	}
	if db == nil {
		return fmt.Errorf("db is unavailable for tenant object-spec exclusiveSiblingFields apply")
	}
	apiNames := make([]string, 0, len(updates))
	for apiName := range updates {
		apiNames = append(apiNames, apiName)
	}
	sort.Strings(apiNames)
	for _, apiName := range apiNames {
		fields := updates[apiName]
		var encoded interface{}
		if len(fields) > 0 {
			raw, err := json.Marshal(fields)
			if err != nil {
				return fmt.Errorf("encode ExclusiveSiblingFields for %s: %w", apiName, err)
			}
			encoded = string(raw)
		}
		if err := updateExclusiveSiblingFieldsOverlay(ctx, db, apiName, encoded, ObjectsSpecFile); err != nil {
			return err
		}
		log.Printf("   ✅ ExclusiveSiblingFields seeded on %s (%d rules)", apiName, len(fields))
	}
	return nil
}

func tenantObjectSpecExclusiveSiblingFields(specs []packs.ObjectSpec, ownership map[string]exclusiveSiblingFieldsPackOwnership) map[string][]models.ExclusiveSiblingField {
	fieldsByObject := make(map[string][]models.ExclusiveSiblingField)
	for _, spec := range specs {
		if !isTenantCustomObjectSpec(spec) {
			continue
		}
		key := strings.ToLower(strings.TrimSpace(spec.APIName))
		if key == "" {
			continue
		}
		if _, packOwned := ownership[key]; packOwned {
			continue
		}
		fieldsByObject[spec.APIName] = append([]models.ExclusiveSiblingField(nil), spec.ExclusiveSiblingFields...)
	}
	return fieldsByObject
}

func sqlPlaceholders(count int) []string {
	if count <= 0 {
		return nil
	}
	out := make([]string, count)
	for i := range out {
		out[i] = "?"
	}
	return out
}