package packs

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

	"github.com/nexuscrm/automation/autonumber"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// ---- Types ----

type recordTypeSeedEntry struct {
	ID                string  `json:"id,omitempty"`
	Name              string  `json:"name"`
	Code              *string `json:"code,omitempty"`
	BusinessProcessID *string `json:"businessProcessId,omitempty"`
}

type recordTypeObjectSeed struct {
	ObjectApiName string                `json:"objectApiName"`
	Types         []recordTypeSeedEntry `json:"types"`
}

type recordTypeSeedData struct {
	RecordTypes []recordTypeObjectSeed `json:"recordTypes"`
}

type rolesData struct {
	Roles []systemRoleJSON `json:"roles"`
}

type defaultReportFolder struct {
	ID            string
	Name          string
	DeveloperName string
	Type          string
	AccessType    string
}

type systemRoleJSON struct {
	ID           string  `json:"id"`
	Name         string  `json:"name"`
	ParentRoleID *string `json:"parent_role_id,omitempty"`
}

type standardValidationRulesData struct {
	ValidationRules []models.ValidationRule `json:"validationRules"`
}

const (
	// Pack validation-rule seeds keep deterministic vrule_ Ids for first
	// insert compatibility. Ownership and reconcile scope come from OwnerPack,
	// not from this synthetic Id prefix.
	standardValidationRuleIDPrefix = "vrule_"
)

// ---- Pack ----

// PlatformConfigPack manages platform-owned configuration:
// roles, folders, and folder organization shares.
type PlatformConfigPack struct{}

func (p *PlatformConfigPack) Name() string       { return "platform-config" }
func (p *PlatformConfigPack) Category() Category { return PlatformConfig }

func (p *PlatformConfigPack) Apply(ctx context.Context, deps ApplyDeps) error {
	log.Println("📦 Applying platform-config pack...")

	if err := applyRoles(deps.DB); err != nil {
		return fmt.Errorf("roles: %w", err)
	}
	cleanupSystemListFields(deps.DB)
	if err := applyFolders(deps.DB); err != nil {
		return fmt.Errorf("folders: %w", err)
	}

	// Folder organization shares MUST run after applyFolders (needs ReportFolder rows to exist)
	if err := ensureFolderOrganizationShares(ctx, deps.DB); err != nil {
		return fmt.Errorf("folder organization shares: %w", err)
	}

	log.Println("✅ platform-config pack applied")
	return nil
}

func applyStandardValidationRuleSeeds(ctx context.Context, db *sql.DB, seeds []models.ValidationRule, ownerPack string) error {
	rules := make([]models.ValidationRule, 0, len(seeds))
	for i := range seeds {
		rule := normalizeValidationRuleSeed(seeds[i])
		if rule.ID == "" {
			return fmt.Errorf("validation rule seed %q is missing id", rule.Name)
		}
		if !strings.HasPrefix(rule.ID, standardValidationRuleIDPrefix) {
			return fmt.Errorf("validation rule seed %q id %q must use %s prefix", rule.Name, rule.ID, standardValidationRuleIDPrefix)
		}
		rules = append(rules, rule)
	}

	if err := validateValidationRuleBusinessKeyOwnership(ctx, db, rules, ownerPack); err != nil {
		return err
	}

	for _, rule := range rules {
		if err := upsertStandardValidationRule(ctx, db, rule, ownerPack); err != nil {
			return err
		}
	}

	return reconcileStandardValidationRuleRemovals(ctx, db, rules, ownerPack)
}

func validateValidationRuleBusinessKeyOwnership(ctx context.Context, db *sql.DB, rules []models.ValidationRule, ownerPack string) error {
	if ownerPack == "" || len(rules) == 0 {
		return nil
	}
	packOwnedBusinessKeys := make([]models.ValidationRule, 0, len(rules))
	for i := range rules {
		rule := normalizeValidationRuleSeed(rules[i])
		if rule.ObjectAPIName != "" && rule.Name != "" {
			packOwnedBusinessKeys = append(packOwnedBusinessKeys, rule)
		}
	}

	for _, rule := range packOwnedBusinessKeys {
		var existingID string
		var existingOwner sql.NullString
		err := db.QueryRowContext(ctx, `
SELECT Id, OwnerPack
FROM _System_ValidationRule
WHERE ObjectApiName = ? AND Name = ?
LIMIT 1`, rule.ObjectAPIName, rule.Name).Scan(&existingID, &existingOwner)
		if err == sql.ErrNoRows {
			continue
		}
		if err != nil {
			return fmt.Errorf("query validation rule ownership %s/%s: %w", rule.ObjectAPIName, rule.Name, err)
		}
		gotOwner := ""
		if existingOwner.Valid {
			gotOwner = strings.TrimSpace(existingOwner.String)
		}
		if gotOwner != ownerPack {
			return fmt.Errorf("validation rule %s/%s OwnerPack = %q (row %s), refusing to install for %q",
				rule.ObjectAPIName, rule.Name, gotOwner, existingID, ownerPack)
		}
	}
	return nil
}

func reconcileStandardValidationRuleRemovals(ctx context.Context, db *sql.DB, rules []models.ValidationRule, ownerPack string) error {
	if ownerPack == "" {
		return nil
	}

	businessKeyPredicates := make([]string, 0, len(rules))
	args := make([]interface{}, 0, 1+len(rules)*2)
	args = append(args, ownerPack)
	for i := range rules {
		rule := normalizeValidationRuleSeed(rules[i])
		if rule.ObjectAPIName != "" && rule.Name != "" {
			businessKeyPredicates = append(businessKeyPredicates, "(ObjectApiName = ? AND Name = ?)")
			args = append(args, rule.ObjectAPIName, rule.Name)
		}
	}

	query := `
UPDATE _System_ValidationRule
SET Active = 0, LastModifiedDate = CURRENT_TIMESTAMP
WHERE (IsCustomized = 0 OR IsCustomized IS NULL)
  AND OwnerPack = ?
  AND Active = 1`
	if len(businessKeyPredicates) > 0 {
		query += fmt.Sprintf(`
  AND NOT (%s)`, strings.Join(businessKeyPredicates, " OR "))
	}

	if _, err := db.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("reconcile removed standard validation rules: %w", err)
	}
	return nil
}

func upsertStandardValidationRule(ctx context.Context, db *sql.DB, rule models.ValidationRule, ownerPack string) error {
	const query = `
INSERT INTO _System_ValidationRule
  (Id, ObjectApiName, Name, Description, Active, ` + "`Condition`" + `, ErrorMessage,
   ErrorDisplayField, RecordSetBindings, IsCustomized, OwnerPack, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
	ON DUPLICATE KEY UPDATE
	  ObjectApiName = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(ObjectApiName), ObjectApiName),
	  Name = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(Name), Name),
	  Description = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(Description), Description),
	  Active = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(Active), Active),
	  ` + "`Condition`" + ` = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(` + "`Condition`" + `), ` + "`Condition`" + `),
	  ErrorMessage = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(ErrorMessage), ErrorMessage),
	  ErrorDisplayField = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(ErrorDisplayField), ErrorDisplayField),
	  RecordSetBindings = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), VALUES(RecordSetBindings), RecordSetBindings),
	  OwnerPack = OwnerPack,
	  LastModifiedDate = IF(OwnerPack = VALUES(OwnerPack) AND (IsCustomized = 0 OR IsCustomized IS NULL), CURRENT_TIMESTAMP, LastModifiedDate)`

	recordSetBindings, err := nullableValidationRuleRecordSetBindings(rule.RecordSetBindings)
	if err != nil {
		return err
	}
	if _, err := db.ExecContext(ctx, query,
		rule.ID,
		rule.ObjectAPIName,
		rule.Name,
		nullableValidationRuleString(rule.Description),
		rule.Active,
		rule.Condition,
		rule.ErrorMessage,
		nullableValidationRuleString(rule.ErrorDisplayField),
		recordSetBindings,
		ownerPack,
	); err != nil {
		return fmt.Errorf("upsert validation rule %s/%s: %w", rule.ObjectAPIName, rule.Name, err)
	}

	var existingID string
	var existingOwner sql.NullString
	if err := db.QueryRowContext(ctx, `
SELECT Id, OwnerPack
FROM _System_ValidationRule
WHERE ObjectApiName = ? AND Name = ?
LIMIT 1`, rule.ObjectAPIName, rule.Name).Scan(&existingID, &existingOwner); err != nil {
		return fmt.Errorf("query validation rule ownership after upsert %s/%s: %w", rule.ObjectAPIName, rule.Name, err)
	}
	gotOwner := ""
	if existingOwner.Valid {
		gotOwner = strings.TrimSpace(existingOwner.String)
	}
	if gotOwner != ownerPack {
		return fmt.Errorf("validation rule %s/%s OwnerPack = %q (row %s), refusing to install for %q",
			rule.ObjectAPIName, rule.Name, gotOwner, existingID, ownerPack)
	}
	return nil
}

func nullableValidationRuleString(value *string) interface{} {
	return nullableTrimmedString(value)
}

func nullableTrimmedString(value *string) interface{} {
	if value == nil {
		return nil
	}
	trimmed := strings.TrimSpace(*value)
	if trimmed == "" {
		return nil
	}
	return trimmed
}

func nullableValidationRuleRecordSetBindings(bindings []models.ValidationRuleRecordSetBinding) (interface{}, error) {
	if len(bindings) == 0 {
		return nil, nil
	}
	data, err := json.Marshal(bindings)
	if err != nil {
		return nil, fmt.Errorf("marshal validation rule recordSetBindings: %w", err)
	}
	return string(data), nil
}

func normalizeValidationRuleSeed(rule models.ValidationRule) models.ValidationRule {
	rule.ID = strings.TrimSpace(rule.ID)
	rule.ObjectAPIName = strings.TrimSpace(rule.ObjectAPIName)
	rule.Name = strings.TrimSpace(rule.Name)
	rule.Condition = strings.TrimSpace(rule.Condition)
	rule.ErrorMessage = strings.TrimSpace(rule.ErrorMessage)
	if rule.Description != nil {
		desc := strings.TrimSpace(*rule.Description)
		if desc == "" {
			rule.Description = nil
		} else {
			rule.Description = &desc
		}
	}
	if rule.ErrorDisplayField != nil {
		field := strings.TrimSpace(*rule.ErrorDisplayField)
		if field == "" {
			rule.ErrorDisplayField = nil
		} else {
			rule.ErrorDisplayField = &field
		}
	}
	return rule
}

func (p *PlatformConfigPack) Verify(ctx context.Context, deps VerifyDeps) error {
	// App-specific metadata verification moved with the app-owned seeds.
	// Keeping runtime-required verification clean avoids making L0 readiness
	// require an app pack. Installed app packs verify through their
	// source-backed installed-pack rows.
	declared, err := DeclaredComponentsForPack(p)
	if err != nil {
		return err
	}
	return VerifyDeclaredComponentRows(ctx, deps.DB, p.Name(), declared, OwnerlessRuntimeComponentSurfaces(),
		ComponentKindReportFolder,
		ComponentKindFolderShare,
	)
}

func (p *PlatformConfigPack) DeclaredComponents() ([]DeclaredComponent, error) {
	folders := defaultReportFolders()
	components := make([]DeclaredComponent, 0, len(folders)*2)
	for _, folder := range folders {
		folderPayload := reportFolderPayload(folder)
		folderHash, err := ComponentPayloadHash(folderPayload)
		if err != nil {
			return nil, err
		}
		folderKey := folder.ID
		components = append(components, DeclaredComponent{
			PackName:      p.Name(),
			Kind:          ComponentKindReportFolder,
			BusinessKey:   folderKey,
			Required:      true,
			Manageability: ManageabilityLocked,
			PayloadHash:   folderHash,
		})

		sharePayload := folderOrganizationSharePayload(folder)
		shareHash, err := ComponentPayloadHash(sharePayload)
		if err != nil {
			return nil, err
		}
		components = append(components, DeclaredComponent{
			PackName:      p.Name(),
			Kind:          ComponentKindFolderShare,
			BusinessKey:   folderOrganizationShareID(folder.ID),
			Required:      true,
			Manageability: ManageabilityLocked,
			PayloadHash:   shareHash,
			References: []ComponentReference{
				{Kind: ComponentKindReportFolder, BusinessKey: folderKey, Required: true},
			},
		})
	}
	return components, nil
}

// ---- Implementation ----

//go:embed roles.json
var rolesJSON []byte

func applyRoles(rawDB *sql.DB) error {
	log.Println("🔧 Initializing roles...")

	var data rolesData
	if err := json.Unmarshal(rolesJSON, &data); err != nil {
		return fmt.Errorf("parse roles.json: %w", err)
	}
	if len(data.Roles) == 0 {
		log.Println("   ℹ️  No roles to seed")
		return nil
	}

	ctx := context.Background()
	for _, role := range data.Roles {
		query := fmt.Sprintf(
			`INSERT INTO %s (%s, %s, %s, RollupDescription, IsDeleted, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, '', 0, NOW(), NOW())
			ON DUPLICATE KEY UPDATE
				%s = VALUES(%s),
				LastModifiedDate = NOW()`,
			constants.TableUserRole,
			constants.FieldID, constants.FieldUserRole_Name, constants.FieldUserRole_ParentRoleID,
			constants.FieldUserRole_Name, constants.FieldUserRole_Name,
		)

		var parentRoleID interface{}
		if role.ParentRoleID != nil {
			parentRoleID = *role.ParentRoleID
		}

		if _, err := rawDB.ExecContext(ctx, query, role.ID, role.Name, parentRoleID); err != nil {
			return fmt.Errorf("ensure role %s: %w", role.Name, err)
		}
		log.Printf("   ✅ Ensured role: %s", role.Name)
	}
	return nil
}

// ApplyRecordTypes upserts RecordType rows from the supplied JSON payload.
// Caller supplies the JSON bytes so app-pack and tenant seeds flow through
// the same writer without duplicating the upsert SQL.
func ApplyRecordTypes(rawDB *sql.DB, jsonBytes []byte) error {
	return applyRecordTypes(rawDB, jsonBytes, "", false)
}

func ApplyRecordTypesForOwner(rawDB *sql.DB, jsonBytes []byte, ownerPack string) error {
	return applyRecordTypes(rawDB, jsonBytes, ownerPack, true)
}

func applyRecordTypes(rawDB *sql.DB, jsonBytes []byte, ownerPack string, requireObjects bool) error {
	log.Println("🔧 Initializing record types...")

	var data recordTypeSeedData
	if err := json.Unmarshal(jsonBytes, &data); err != nil {
		return fmt.Errorf("parse record types: %w", err)
	}

	ctx := context.Background()
	keep := map[recordTypeBusinessKey]struct{}{}
	// Fail-closed DeveloperName collision check per object: two distinct Names that
	// sanitize to the same DeveloperName would cause ON DUPLICATE KEY UPDATE to
	// silently overwrite the first row instead of rejecting the second.
	type rtDedupKey struct{ objectID, developerName string }
	developerNameSeen := make(map[rtDedupKey]string) // key → first Name that claimed it

	for _, obj := range data.RecordTypes {
		var objectID string
		err := rawDB.QueryRowContext(ctx,
			fmt.Sprintf("SELECT %s FROM %s WHERE %s = ?", constants.FieldID, constants.TableObject, constants.FieldSysObject_APIName),
			obj.ObjectApiName,
		).Scan(&objectID)
		if err != nil {
			if requireObjects {
				return fmt.Errorf("record types for %s require object to exist: %w", obj.ObjectApiName, err)
			}
			log.Printf("   ⚠️  Object %s not found, skipping record types: %v", obj.ObjectApiName, err)
			continue
		}

		for _, rt := range obj.Types {
			name := strings.TrimSpace(rt.Name)
			if name == "" {
				return fmt.Errorf("record type for %s has empty name", obj.ObjectApiName)
			}
			dn := utils.SanitizeDeveloperName(name)
			canonicalDeveloperNameKey := recordTypeDeveloperNameKey(dn)
			if first, clash := developerNameSeen[rtDedupKey{objectID, canonicalDeveloperNameKey}]; clash {
				return fmt.Errorf("apply record types: DeveloperName collision on object %s — %q and %q both sanitize to %q; rename one record type before applying", obj.ObjectApiName, first, name, dn)
			}
			developerNameSeen[rtDedupKey{objectID, canonicalDeveloperNameKey}] = name
			id := strings.TrimSpace(rt.ID)
			explicitID := id != ""
			if id == "" {
				if ownerPack == "" {
					return fmt.Errorf("record type %s/%s is missing id", obj.ObjectApiName, name)
				}
				id = generatedRecordTypeID(ownerPack, obj.ObjectApiName, dn)
			}
			code, err := normalizedRecordTypeCode(rt.Code, obj.ObjectApiName, name)
			if err != nil {
				return err
			}
			if ownerPack == "" && rt.Code != nil {
				return fmt.Errorf("record type %s/%s declares code but ownerless record type codes must use recordTypeCodeSeeds", obj.ObjectApiName, name)
			}
			expectation, err := ensureRecordTypeOwnerCompatible(ctx, rawDB, objectID, obj.ObjectApiName, name, dn, ownerPack, id, explicitID)
			if err != nil {
				return err
			}
			if explicitID && expectation.expectedID != "" && expectation.expectedID != id {
				return fmt.Errorf("record type %s/%s explicit id %s resolves to existing canonical row %s; resolve id drift before applying", obj.ObjectApiName, name, id, expectation.expectedID)
			}
			if err := applyResolvedIDRecordType(ctx, rawDB, expectation.expectedID, objectID, obj.ObjectApiName, name, dn, ownerPack, nullableTrimmedString(rt.BusinessProcessID), code); err != nil {
				return fmt.Errorf("ensure record type %s/%s: %w", obj.ObjectApiName, name, err)
			}
			if err := verifyRecordTypeUpsertWinner(ctx, rawDB, obj.ObjectApiName, name, dn, expectation); err != nil {
				return err
			}
			if ownerPack != "" {
				keep[recordTypeBusinessKey{ObjectID: objectID, DeveloperName: canonicalDeveloperNameKey}] = struct{}{}
			}
			log.Printf("   ✅ Ensured record type: %s/%s", obj.ObjectApiName, name)
		}
	}
	if err := reconcileRecordTypeRemovals(ctx, rawDB, ownerPack, keep); err != nil {
		return err
	}
	return nil
}

type recordTypeBusinessKey struct {
	ObjectID      string
	DeveloperName string
}

func recordTypeDeveloperNameKey(developerName string) string {
	return strings.ToLower(strings.TrimSpace(developerName))
}

func normalizedRecordTypeCode(raw *string, objectAPIName string, name string) (*string, error) {
	if raw == nil {
		return nil, nil
	}
	trimmed := strings.TrimSpace(*raw)
	if trimmed == "" {
		return nil, fmt.Errorf("record type %s/%s has blank code", objectAPIName, name)
	}
	return &trimmed, nil
}

func recordTypeMatchesCanonicalIdentity(existingName, existingDeveloperName, incomingName, incomingDeveloperName string) bool {
	if strings.TrimSpace(existingName) == strings.TrimSpace(incomingName) {
		return true
	}
	existingDeveloperNameKey := recordTypeDeveloperNameKey(existingDeveloperName)
	if existingDeveloperNameKey == "" {
		existingDeveloperNameKey = recordTypeDeveloperNameKey(utils.SanitizeDeveloperName(strings.TrimSpace(existingName)))
	}
	return existingDeveloperNameKey != "" && existingDeveloperNameKey == recordTypeDeveloperNameKey(incomingDeveloperName)
}

func generatedRecordTypeID(ownerPack, objectAPIName, developerName string) string {
	sum := sha1.Sum([]byte(strings.ToLower(strings.TrimSpace(ownerPack)) + "\x00" + strings.ToLower(strings.TrimSpace(objectAPIName)) + "\x00" + recordTypeDeveloperNameKey(developerName)))
	return "rt_" + hex.EncodeToString(sum[:])
}

type recordTypeUpsertExpectation struct {
	expectedID          string
	expectedOwnerPack   string
	expectedObjectID    string
	expectedSobjectType string
}

func applyResolvedIDRecordType(ctx context.Context, db *sql.DB, id, objectID, objectAPIName, name, developerName, ownerPack string, businessProcessID interface{}, recordTypeCode *string) error {
	var existingObjectID, existingOwnerPack, existingName, existingDeveloperName string
	var existingIsDeleted bool
	err := db.QueryRowContext(ctx, `
SELECT ObjectId, COALESCE(OwnerPack, ''), Name, COALESCE(DeveloperName, ''), IsDeleted
FROM _System_RecordType
WHERE Id = ?
LIMIT 1`, id).Scan(&existingObjectID, &existingOwnerPack, &existingName, &existingDeveloperName, &existingIsDeleted)
	switch {
	case err == sql.ErrNoRows:
		_, err = db.ExecContext(ctx, `
INSERT INTO _System_RecordType
  (Id, ObjectId, Name, record_type_code, DeveloperName, IsActive, IsMaster, OwnerPack, BusinessProcessId, SobjectType, Description, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, 1, 0, ?, ?, ?, '', NOW(), NOW())`,
			id, objectID, name, nullableTrimmedString(recordTypeCode), developerName, ownerPack, businessProcessID, objectAPIName)
		if err != nil {
			return err
		}
		return nil
	case err != nil:
		return fmt.Errorf("query record type %s/%s by resolved id: %w", objectAPIName, name, err)
	}
	existingObjectID = strings.TrimSpace(existingObjectID)
	existingOwnerPack = strings.TrimSpace(existingOwnerPack)
	if existingObjectID != strings.TrimSpace(objectID) {
		return fmt.Errorf("record type %s/%s id %s collides with row on object %s; record type writes may only target the same object", objectAPIName, name, id, existingObjectID)
	}
	if !recordTypeMatchesCanonicalIdentity(existingName, existingDeveloperName, name, developerName) {
		return fmt.Errorf("record type %s/%s id %s already belongs to different canonical identity named %q / DeveloperName %q; same-object id reuse is not allowed",
			objectAPIName, name, id, strings.TrimSpace(existingName), strings.TrimSpace(existingDeveloperName))
	}
	var result sql.Result
	assignments := []string{
		"Name = ?",
		"DeveloperName = ?",
		"SobjectType = ?",
		"BusinessProcessId = ?",
	}
	args := []any{name, developerName, objectAPIName, businessProcessID}
	if recordTypeCode != nil {
		assignments = append(assignments, "record_type_code = ?")
		args = append(args, nullableTrimmedString(recordTypeCode))
	}
	assignments = append(assignments,
		"IsActive = 1",
		"IsDeleted = 0",
		"LastModifiedDate = NOW()",
	)
	if ownerPack == "" {
		if existingOwnerPack != "" {
			return fmt.Errorf("record type %s/%s id %s is owned by %q, ownerless apply cannot overwrite managed record types", objectAPIName, name, id, existingOwnerPack)
		}
		query := fmt.Sprintf(
			`UPDATE _System_RecordType
SET %s
WHERE Id = ? AND ObjectId = ? AND COALESCE(OwnerPack, '') = ''`,
			strings.Join(assignments, ", "),
		)
		args = append(args, id, objectID)
		result, err = db.ExecContext(ctx, query, args...)
	} else {
		if existingOwnerPack == "" && existingIsDeleted {
			assignments = append(assignments, "OwnerPack = ?")
			args = append(args, ownerPack)
			query := fmt.Sprintf(
				`UPDATE _System_RecordType
SET %s
WHERE Id = ? AND ObjectId = ? AND COALESCE(OwnerPack, '') = '' AND COALESCE(IsDeleted, 0) = 1`,
				strings.Join(assignments, ", "),
			)
			args = append(args, id, objectID)
			result, err = db.ExecContext(ctx, query, args...)
		} else {
			if existingOwnerPack == "" {
				return fmt.Errorf("record type %s/%s id %s collides with ownerless row on object %s; rename one before applying", objectAPIName, name, id, objectAPIName)
			}
			if existingOwnerPack != ownerPack {
				return fmt.Errorf("record type %s/%s id %s is owned by %q, cannot claim for %q", objectAPIName, name, id, existingOwnerPack, ownerPack)
			}
			query := fmt.Sprintf(
				`UPDATE _System_RecordType
SET %s
WHERE Id = ? AND ObjectId = ? AND COALESCE(OwnerPack, '') = ?`,
				strings.Join(assignments, ", "),
			)
			args = append(args, id, objectID, ownerPack)
			result, err = db.ExecContext(ctx, query, args...)
		}
	}
	if err != nil {
		return err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if rowsAffected != 1 {
		return fmt.Errorf("record type %s/%s resolved id %s did not update exactly one row", objectAPIName, name, id)
	}
	return nil
}

func reconcileRecordTypeRemovals(ctx context.Context, db *sql.DB, ownerPack string, keep map[recordTypeBusinessKey]struct{}) error {
	if ownerPack == "" {
		return nil
	}
	rows, err := db.QueryContext(ctx, `
SELECT Id, ObjectId, DeveloperName
FROM _System_RecordType
WHERE OwnerPack = ? AND IsDeleted = 0`, ownerPack)
	if err != nil {
		return fmt.Errorf("query record types for owner-scoped reconcile: %w", err)
	}
	defer rows.Close()

	var stale []string
	for rows.Next() {
		var id, objectID, developerName string
		if err := rows.Scan(&id, &objectID, &developerName); err != nil {
			return fmt.Errorf("scan record type for owner-scoped reconcile: %w", err)
		}
		key := recordTypeBusinessKey{ObjectID: objectID, DeveloperName: recordTypeDeveloperNameKey(developerName)}
		if _, ok := keep[key]; !ok {
			stale = append(stale, id)
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for _, id := range stale {
		if _, err := db.ExecContext(ctx, `
UPDATE _System_RecordType
SET IsActive = 0, IsDeleted = 1, LastModifiedDate = NOW()
WHERE Id = ? AND OwnerPack = ?`, id, ownerPack); err != nil {
			return fmt.Errorf("deactivate stale record type %s for %s: %w", id, ownerPack, err)
		}
	}
	return nil
}

func ensureRecordTypeOwnerCompatible(ctx context.Context, db *sql.DB, objectID, objectAPIName, name, developerName, ownerPack, recordTypeID string, explicitID bool) (recordTypeUpsertExpectation, error) {
	expectation := recordTypeUpsertExpectation{
		expectedID:          strings.TrimSpace(recordTypeID),
		expectedOwnerPack:   strings.TrimSpace(ownerPack),
		expectedObjectID:    strings.TrimSpace(objectID),
		expectedSobjectType: strings.TrimSpace(objectAPIName),
	}
	type deletedOwnerlessClaimant struct {
		id   string
		name string
	}
	matchedExisting := false
	var deletedOwnerlessDirect *deletedOwnerlessClaimant
	var deletedOwnerlessLegacy *deletedOwnerlessClaimant
	rows, err := db.QueryContext(ctx, `
	SELECT Id, OwnerPack, Name, IsDeleted
	FROM _System_RecordType
	WHERE ObjectId = ? AND (DeveloperName = ? OR Name = ?)`, objectID, developerName, name)
	if err != nil {
		return expectation, fmt.Errorf("query record type ownership %s/%s: %w", objectAPIName, name, err)
	}
	defer rows.Close()

	for rows.Next() {
		var existingID string
		var existingOwner sql.NullString
		var existingName string
		var isDeleted bool
		if err := rows.Scan(&existingID, &existingOwner, &existingName, &isDeleted); err != nil {
			return expectation, fmt.Errorf("scan record type ownership %s/%s: %w", objectAPIName, name, err)
		}
		existingOwnerPack := strings.TrimSpace(existingOwner.String)
		if ownerPack == "" {
			if existingOwnerPack == "" && strings.TrimSpace(existingID) == strings.TrimSpace(recordTypeID) {
				expectation.expectedID = strings.TrimSpace(existingID)
				continue
			}
			if existingOwnerPack == "" {
				state := "live"
				if isDeleted {
					state = "deleted"
				}
				return expectation, fmt.Errorf("record type %s/%s collides with %s ownerless row %s named %q for business name %q / DeveloperName %q; ownerless apply requires matching row id %q",
					objectAPIName, name, state, existingID, existingName, name, developerName, recordTypeID)
			}
			return expectation, fmt.Errorf("record type %s/%s collides with managed row %s owned by %q for business name %q / DeveloperName %q; ownerless apply cannot overwrite managed record types",
				objectAPIName, name, existingID, existingOwnerPack, name, developerName)
		}
		if existingOwnerPack == "" {
			if isDeleted {
				if deletedOwnerlessDirect != nil && deletedOwnerlessDirect.id != strings.TrimSpace(existingID) {
					return expectation, fmt.Errorf("record type %s/%s matches multiple deleted ownerless rows (%s, %s) across Name/DeveloperName identities; resolve tombstone drift before applying",
						objectAPIName, name, deletedOwnerlessDirect.id, existingID)
				}
				deletedOwnerlessDirect = &deletedOwnerlessClaimant{
					id:   strings.TrimSpace(existingID),
					name: strings.TrimSpace(existingName),
				}
				continue
			}
			state := "live"
			return expectation, fmt.Errorf("record type %s/%s collides with %s ownerless row %s named %q for business name %q / DeveloperName %q; rename one before applying",
				objectAPIName, name, state, existingID, existingName, name, developerName)
		}
		if existingOwnerPack != ownerPack {
			return expectation, fmt.Errorf("record type %s/%s is owned by %q (row %s), cannot claim for %q",
				objectAPIName, name, existingOwnerPack, existingID, ownerPack)
		}
		if matchedExisting && expectation.expectedID != strings.TrimSpace(existingID) {
			return expectation, fmt.Errorf("record type %s/%s matches multiple managed rows (%s, %s) across Name/DeveloperName identities; resolve drift before applying",
				objectAPIName, name, expectation.expectedID, existingID)
		}
		expectation.expectedID = strings.TrimSpace(existingID)
		expectation.expectedOwnerPack = existingOwnerPack
		matchedExisting = true
	}
	if err := rows.Err(); err != nil {
		return expectation, fmt.Errorf("iterate record type ownership %s/%s: %w", objectAPIName, name, err)
	}
	legacyRows, err := db.QueryContext(ctx, `
	SELECT Id, Name, IsDeleted, COALESCE(OwnerPack, '')
	FROM _System_RecordType
	WHERE ObjectId = ? AND COALESCE(DeveloperName, '') = ?`, objectID, "")
	if err != nil {
		return expectation, fmt.Errorf("query legacy record type canonical readiness %s/%s: %w", objectAPIName, name, err)
	}
	defer legacyRows.Close()
	for legacyRows.Next() {
		var legacyID, legacyName, legacyOwnerPack string
		var isDeleted bool
		if err := legacyRows.Scan(&legacyID, &legacyName, &isDeleted, &legacyOwnerPack); err != nil {
			return expectation, fmt.Errorf("scan legacy record type canonical readiness %s/%s: %w", objectAPIName, name, err)
		}
		legacyID = strings.TrimSpace(legacyID)
		legacyName = strings.TrimSpace(legacyName)
		legacyOwnerPack = strings.TrimSpace(legacyOwnerPack)
		if recordTypeDeveloperNameKey(utils.SanitizeDeveloperName(legacyName)) != recordTypeDeveloperNameKey(developerName) {
			continue
		}
		if ownerPack == "" {
			if legacyOwnerPack == "" && legacyID == strings.TrimSpace(recordTypeID) {
				expectation.expectedID = legacyID
				matchedExisting = true
				continue
			}
			state := "live"
			if isDeleted {
				state = "deleted"
			}
			if legacyOwnerPack == "" {
				return expectation, fmt.Errorf("record type %s/%s collides with %s ownerless row %s named %q for business name %q / DeveloperName %q; ownerless apply requires matching row id %q",
					objectAPIName, name, state, legacyID, legacyName, name, developerName, recordTypeID)
			}
			return expectation, fmt.Errorf("record type %s/%s collides with managed row %s owned by %q for business name %q / DeveloperName %q; ownerless apply cannot overwrite managed record types",
				objectAPIName, name, legacyID, legacyOwnerPack, name, developerName)
		}
		if legacyOwnerPack == "" {
			if isDeleted {
				if deletedOwnerlessDirect != nil {
					continue
				}
				isExactName := strings.TrimSpace(legacyName) == strings.TrimSpace(name)
				if deletedOwnerlessLegacy == nil ||
					(isExactName && strings.TrimSpace(deletedOwnerlessLegacy.name) != strings.TrimSpace(name)) ||
					(isExactName == (strings.TrimSpace(deletedOwnerlessLegacy.name) == strings.TrimSpace(name)) && legacyID < deletedOwnerlessLegacy.id) {
					deletedOwnerlessLegacy = &deletedOwnerlessClaimant{id: legacyID, name: strings.TrimSpace(legacyName)}
				}
				continue
			}
			state := "live"
			return expectation, fmt.Errorf("record type %s/%s collides with %s ownerless row %s named %q for business name %q / DeveloperName %q; rename one before applying",
				objectAPIName, name, state, legacyID, legacyName, name, developerName)
		}
		if legacyOwnerPack != ownerPack {
			return expectation, fmt.Errorf("record type %s/%s is owned by %q (row %s), cannot claim for %q",
				objectAPIName, name, legacyOwnerPack, legacyID, ownerPack)
		}
		if matchedExisting && expectation.expectedID != legacyID {
			return expectation, fmt.Errorf("record type %s/%s matches multiple managed rows (%s, %s) across canonical/legacy identities; resolve drift before applying",
				objectAPIName, name, expectation.expectedID, legacyID)
		}
		if explicitID && strings.TrimSpace(recordTypeID) != "" && legacyID != strings.TrimSpace(recordTypeID) {
			return expectation, fmt.Errorf("record type %s/%s explicit id %s conflicts with legacy canonical row %s; resolve id drift before applying",
				objectAPIName, name, recordTypeID, legacyID)
		}
		if ownerPack != "" && legacyOwnerPack == ownerPack &&
			(expectation.expectedID == "" || expectation.expectedID == legacyID || !matchedExisting) {
			expectation.expectedID = legacyID
			expectation.expectedOwnerPack = legacyOwnerPack
			matchedExisting = true
			continue
		}
	}
	if err := legacyRows.Err(); err != nil {
		return expectation, fmt.Errorf("iterate legacy record type canonical readiness %s/%s: %w", objectAPIName, name, err)
	}
	if ownerPack != "" && !matchedExisting {
		if deletedOwnerlessDirect != nil {
			expectation.expectedID = deletedOwnerlessDirect.id
			expectation.expectedOwnerPack = strings.TrimSpace(ownerPack)
			matchedExisting = true
		} else if deletedOwnerlessLegacy != nil {
			expectation.expectedID = deletedOwnerlessLegacy.id
			expectation.expectedOwnerPack = strings.TrimSpace(ownerPack)
			matchedExisting = true
		}
	}
	return expectation, nil
}

func verifyRecordTypeUpsertWinner(ctx context.Context, db *sql.DB, objectAPIName, name, developerName string, expectation recordTypeUpsertExpectation) error {
	var gotID, gotObjectID, gotOwnerPack, gotName, gotDeveloperName, gotSobjectType string
	var isDeleted bool
	err := db.QueryRowContext(ctx, `
SELECT Id, ObjectId, COALESCE(OwnerPack, ''), Name, COALESCE(DeveloperName, ''), COALESCE(SobjectType, ''), IsDeleted
FROM _System_RecordType
WHERE Id = ?
LIMIT 1`, expectation.expectedID).Scan(&gotID, &gotObjectID, &gotOwnerPack, &gotName, &gotDeveloperName, &gotSobjectType, &isDeleted)
	if err == sql.ErrNoRows {
		return fmt.Errorf("record type %s/%s lost post-upsert ownership race: expected row %s missing after apply", objectAPIName, name, expectation.expectedID)
	}
	if err != nil {
		return fmt.Errorf("verify record type %s/%s post-upsert winner: %w", objectAPIName, name, err)
	}
	if strings.TrimSpace(gotObjectID) != strings.TrimSpace(expectation.expectedObjectID) ||
		strings.TrimSpace(gotOwnerPack) != strings.TrimSpace(expectation.expectedOwnerPack) ||
		strings.TrimSpace(gotName) != strings.TrimSpace(name) ||
		strings.TrimSpace(gotDeveloperName) != strings.TrimSpace(developerName) ||
		strings.TrimSpace(gotSobjectType) != strings.TrimSpace(expectation.expectedSobjectType) ||
		isDeleted {
		return fmt.Errorf("record type %s/%s lost post-upsert ownership race: row %s now objectId=%q owner=%q name=%q developerName=%q sobjectType=%q deleted=%t, want objectId=%q owner=%q name=%q developerName=%q sobjectType=%q",
			objectAPIName, name, gotID, gotObjectID, gotOwnerPack, gotName, gotDeveloperName, gotSobjectType, isDeleted, expectation.expectedObjectID, expectation.expectedOwnerPack, name, developerName, expectation.expectedSobjectType)
	}
	return nil
}

func applyStandardListFieldsFromPayload(rawDB *sql.DB, payload []byte, source string) error {
	log.Println("🔧 Initializing standard list fields...")
	start := time.Now()

	var listFieldsByObject map[string][]string
	if err := json.Unmarshal(payload, &listFieldsByObject); err != nil {
		return fmt.Errorf("parse %s: %w", source, err)
	}

	updated := 0
	for objectApiName, fields := range listFieldsByObject {
		fieldsJSON, err := json.Marshal(fields)
		if err != nil {
			return fmt.Errorf("marshal list fields for %s: %w", objectApiName, err)
		}

		result, err := rawDB.Exec(
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = ?, `%s` = 0, `%s` = NOW() WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL)",
				constants.TableObject,
				constants.FieldSysObject_ListFields,
				constants.FieldSysObject_ListFieldsCustomized,
				constants.FieldLastModifiedDate,
				constants.FieldAPIName,
				constants.FieldSysObject_ListFieldsCustomized,
				constants.FieldSysObject_ListFieldsCustomized,
			),
			string(fieldsJSON), objectApiName,
		)
		if err != nil {
			return fmt.Errorf("update list fields for %s: %w", objectApiName, err)
		}
		if n, _ := result.RowsAffected(); n > 0 {
			updated++
		}
	}

	log.Printf("   ✅ Standard list fields: %d objects updated (%v)",
		updated, time.Since(start).Round(time.Millisecond))
	return nil
}

func cleanupSystemListFields(rawDB *sql.DB) {
	systemFields := map[string]struct{}{
		"CreatedById": {}, "CreatedDate": {},
		"LastModifiedById": {}, "LastModifiedDate": {},
		"IsDeleted": {}, "SystemModstamp": {},
	}

	rows, err := rawDB.QueryContext(context.Background(),
		fmt.Sprintf("SELECT `%s`, `%s` FROM `%s` WHERE `%s` IS NOT NULL AND `%s` != '' AND `%s` != '[]'",
			constants.FieldAPIName, constants.FieldSysObject_ListFields,
			constants.TableObject,
			constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFields, constants.FieldSysObject_ListFields))
	if err != nil {
		log.Printf("   ⚠️  Failed to query ListFields for cleanup: %v", err)
		return
	}
	defer rows.Close()

	cleaned := 0
	for rows.Next() {
		var apiName, listFieldsJSON string
		if err := rows.Scan(&apiName, &listFieldsJSON); err != nil {
			continue
		}

		var fields []string
		if err := json.Unmarshal([]byte(listFieldsJSON), &fields); err != nil {
			continue
		}

		filtered := make([]string, 0, len(fields))
		for _, f := range fields {
			if _, isSys := systemFields[f]; !isSys {
				filtered = append(filtered, f)
			}
		}

		if len(filtered) == len(fields) {
			continue
		}

		if len(filtered) == 0 {
			_, _ = rawDB.ExecContext(context.Background(),
				fmt.Sprintf("UPDATE `%s` SET `%s` = NULL WHERE `%s` = ?",
					constants.TableObject, constants.FieldSysObject_ListFields, constants.FieldAPIName),
				apiName)
		} else {
			newJSON, _ := json.Marshal(filtered)
			_, _ = rawDB.ExecContext(context.Background(),
				fmt.Sprintf("UPDATE `%s` SET `%s` = ? WHERE `%s` = ?",
					constants.TableObject, constants.FieldSysObject_ListFields, constants.FieldAPIName),
				string(newJSON), apiName)
		}
		log.Printf("   🧹 Cleaned system fields from %s ListFields (%d → %d fields)", apiName, len(fields), len(filtered))
		cleaned++
	}

	if cleaned > 0 {
		log.Printf("   🧹 System field cleanup: %d objects updated", cleaned)
	}
}

func applyFolders(rawDB *sql.DB) error {
	log.Println("🔧 Initializing default folders...")
	ctx := context.Background()

	for _, folder := range defaultReportFolders() {
		query := `
			INSERT INTO ReportFolder (Id, Name, DeveloperName, Type, AccessType, IsDeleted, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, ?, ?, 0, NOW(), NOW())
			ON DUPLICATE KEY UPDATE
				Name = VALUES(Name),
				DeveloperName = VALUES(DeveloperName),
				IsDeleted = 0,
				LastModifiedDate = NOW()
		`
		if _, err := rawDB.ExecContext(ctx, query, folder.ID, folder.Name, folder.DeveloperName, folder.Type, folder.AccessType); err != nil {
			return fmt.Errorf("ensure folder %s: %w", folder.Name, err)
		}
		log.Printf("   ✅ Ensured folder: %s", folder.Name)
	}
	return nil
}

func defaultReportFolders() []defaultReportFolder {
	return []defaultReportFolder{
		{"00000000-0000-0000-0000-000000000001", "Unfiled Public Reports", "Unfiled_Public_Reports", "Report", "Public"},
		{"00000000-0000-0000-0000-000000000002", "Unfiled Public Dashboards", "Unfiled_Public_Dashboards", "Dashboard", "Public"},
	}
}

func reportFolderPayload(folder defaultReportFolder) map[string]any {
	return map[string]any{
		"id":            folder.ID,
		"name":          folder.Name,
		"developerName": folder.DeveloperName,
		"type":          folder.Type,
		"accessType":    folder.AccessType,
	}
}

func folderOrganizationShareID(folderID string) string {
	return "FS_ORG_" + strings.TrimSpace(folderID)
}

func folderOrganizationSharePayload(folder defaultReportFolder) map[string]any {
	return map[string]any{
		"id":           folderOrganizationShareID(folder.ID),
		"folderId":     folder.ID,
		"sharedToId":   "ALL_INTERNAL",
		"sharedToType": "Organization",
		"accessLevel":  "View",
		"createdById":  "SYSTEM",
	}
}

// ApplyAutoNumberFormats seeds _System_Field.auto_number_* columns and
// _System_RecordType.RecordTypeCode from supplied seed slices. Source-backed
// app packs and tenant overlays pass auto-number format seeds through this
// writer; RecordTypeCode seeds stay on the ownerless tenant/bootstrap path
// because they are keyed only by record type name.
//
// Columns populated here:
//   - _System_Field.auto_number_format
//   - _System_Field.auto_number_scope
//   - _System_Field.auto_number_start
//   - _System_RecordType.RecordTypeCode
//
// Columns populated here when the seed specifies an explicit override
// (Round-5 Codex V2-Codex-H6):
//   - _System_Field.auto_number_token_refs — set when a seed declares
//     an explicit multi-chain. Other fields are left NULL so the engine
//     falls back to parser-derived refs.
//
// Columns DELIBERATELY LEFT NULL:
//   - _System_Field.auto_number_canonical_hash (if present)
//
// Idempotency:
//   - Format UPDATEs guard with `auto_number_format IS NULL OR != new_value`
//     to avoid redundant writes; RowsAffected() is therefore a valid
//     "would-have-changed" signal for tripwire tests.
//   - RecordTypeCode UPDATE guards with `RecordTypeCode IS NULL` so admin
//     edits survive re-seeding.
//
// Fields/RecordTypes that do not yet exist in the DB are silently skipped by
// the WHERE clause.
// ApplyAutoNumberFormats upserts the supplied format/RT-code seed slices.
// Bundled app and tenant seeds are passed as parameters instead of read from
// globals so the same writer serves both paths.
func ApplyAutoNumberFormats(rawDB *sql.DB, packName string, formats []AutoNumberFormatSeed, rtCodes []RecordTypeCodeSeed) error {
	if strings.TrimSpace(packName) != "" && len(rtCodes) > 0 {
		return fmt.Errorf("record type code seeds are not supported for owner-scoped packs; declare codes inline in record_types payload")
	}
	if err := ApplyAutoNumberFormatSeeds(rawDB, packName, formats); err != nil {
		return err
	}
	return ApplyRecordTypeCodeSeeds(rawDB, rtCodes)
}

// ApplyAutoNumberFormatSeeds upserts auto-number columns on _System_Field
// owned by `packName`. The OwnerPack predicate uses NULL-coalesce semantics:
//   - packName == ""  → matches rows where OwnerPack IS NULL (admin/tenant-overlay path)
//   - packName != ""  → matches rows where OwnerPack equals packName exactly
//
// Source-backed packs MUST pass their manifest name (non-empty); the
// empty-string slot is reserved for the tenant-overlay caller. Source-backed
// component constructors validate packName != "" at construction time so a
// malformed manifest cannot reach this function with packName=="".
func ApplyAutoNumberFormatSeeds(rawDB *sql.DB, packName string, formats []AutoNumberFormatSeed) error {
	log.Println("🔧 Initializing auto-number formats (Lane C1 seed)...")
	ctx := context.Background()

	// Section 5.1 + 5.2: _System_Field.auto_number_* columns.
	//
	// NULL-drift safety (Codex round-8 V1 HIGH): the `<>` operator does
	// not match NULL, so a partially-migrated row with the correct
	// format but NULL scope/start would be skipped here and then
	// COALESCEd to "global"/1 by MetadataSource.Refresh — collapsing
	// per_prefix fields into a shared global counter stream. Using
	// MySQL/TiDB's `<=>` (NULL-safe equality) inside NOT() treats NULL
	// as not-equal-to the seed value, so NULL columns are repaired on
	// re-apply.
	formatQuery := `UPDATE _System_Field f
		JOIN _System_Object o ON o.Id = f.ObjectId
		SET f.auto_number_format = ?,
		    f.auto_number_scope = ?,
		    f.auto_number_start = ?
		WHERE o.ApiName = ?
		  AND f.ApiName = ?
		  AND (NOT (f.auto_number_format <=> ?)
		    OR NOT (f.auto_number_scope <=> ?)
		    OR NOT (f.auto_number_start <=> ?))
		  AND COALESCE(f.OwnerPack, '') = ?`

	var formatsUpdated, formatsSkipped int
	for _, seed := range formats {
		res, err := rawDB.ExecContext(ctx, formatQuery,
			seed.Format, seed.Scope, seed.Start,
			seed.ObjectName, seed.FieldApiName,
			seed.Format, seed.Scope, seed.Start,
			packName,
		)
		if err != nil {
			return fmt.Errorf("seed auto-number format %s.%s: %w",
				seed.ObjectName, seed.FieldApiName, err)
		}
		n, _ := res.RowsAffected()
		if n > 0 {
			formatsUpdated++
		} else {
			formatsSkipped++
		}
	}
	log.Printf("   ✅ Auto-number formats: %d updated, %d already current",
		formatsUpdated, formatsSkipped)

	// Round-5 Codex V2-Codex-H6: write explicit AutoNumberTokenRefs for fields
	// whose TokenRefsJSON is non-empty, and clear the column back to NULL when
	// the pack payload no longer declares an override. Some app-pack formats use
	// multi-hop owner/lookup chains that the parser cannot derive. We
	// canonicalize the generator's JSON through autonumber.CanonicalizeTokenRefs
	// to guarantee byte-determinism with the engine's read-side round-trip check.
	tokenRefsQuery := `UPDATE _System_Field f
		JOIN _System_Object o ON o.Id = f.ObjectId
		SET f.auto_number_token_refs = ?
		WHERE o.ApiName = ?
		  AND f.ApiName = ?
		  AND NOT (f.auto_number_token_refs <=> ?)
		  AND COALESCE(f.OwnerPack, '') = ?`
	var tokenRefsUpdated, tokenRefsSkipped int
	for _, seed := range formats {
		var tokenRefsValue interface{}
		if seed.TokenRefsJSON == "" {
			tokenRefsValue = nil
		} else {
			var generic any
			if err := json.Unmarshal([]byte(seed.TokenRefsJSON), &generic); err != nil {
				return fmt.Errorf("parse TokenRefsJSON for %s.%s: %w",
					seed.ObjectName, seed.FieldApiName, err)
			}
			canonical, err := autonumber.CanonicalizeTokenRefs(generic)
			if err != nil {
				return fmt.Errorf("canonicalize TokenRefsJSON for %s.%s: %w",
					seed.ObjectName, seed.FieldApiName, err)
			}
			tokenRefsValue = string(canonical)
		}
		res, err := rawDB.ExecContext(ctx, tokenRefsQuery,
			tokenRefsValue,
			seed.ObjectName, seed.FieldApiName,
			tokenRefsValue,
			packName,
		)
		if err != nil {
			return fmt.Errorf("seed AutoNumberTokenRefs %s.%s: %w",
				seed.ObjectName, seed.FieldApiName, err)
		}
		n, _ := res.RowsAffected()
		if n > 0 {
			tokenRefsUpdated++
		} else {
			tokenRefsSkipped++
		}
	}
	if tokenRefsUpdated+tokenRefsSkipped > 0 {
		log.Printf("   ✅ Auto-number token refs: %d updated, %d already current",
			tokenRefsUpdated, tokenRefsSkipped)
	}

	return nil
}

func ApplyRecordTypeCodeSeeds(rawDB *sql.DB, rtCodes []RecordTypeCodeSeed) error {
	log.Println("🔧 Initializing record type codes...")
	ctx := context.Background()

	// Section 5.3: _System_RecordType.RecordTypeCode.
	// Matches by Name (exact) because RecordTypeCode seeds still key record
	// types by business Name. ApplyRecordTypes now persists DeveloperName, but
	// this seed surface intentionally remains Name-keyed in this PR.
	rtQuery := `UPDATE _System_RecordType
		SET record_type_code = ?
		WHERE Name = ?
		  AND record_type_code IS NULL`

	var rtUpdated, rtSkipped int
	for _, seed := range rtCodes {
		res, err := rawDB.ExecContext(ctx, rtQuery, seed.Code, seed.Name)
		if err != nil {
			return fmt.Errorf("seed RecordTypeCode %s: %w", seed.Name, err)
		}
		n, _ := res.RowsAffected()
		if n > 0 {
			rtUpdated++
		} else {
			rtSkipped++
		}
	}
	log.Printf("   ✅ RecordType codes: %d updated, %d already set or missing",
		rtUpdated, rtSkipped)

	return nil
}