package packs

import (
	"context"
	"database/sql"
	_ "embed"
	"encoding/json"
	"fmt"
	"log"
	"os"
	"strings"
	"time"

	"github.com/nexuscrm/backend/internal/bootstrap"
	"github.com/nexuscrm/backend/internal/features/automation/autonumber"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

//go:embed record_types.json
var recordTypesJSON []byte

//go:embed standard_listfields.json
var standardListFieldsJSON []byte

//go:embed standard_validation_rules.json
var standardValidationRulesJSON []byte

// ---- Types ----

type recordTypeSeedEntry struct {
	ID   string `json:"id"`
	Name string `json:"name"`
}

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 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 (
	// Standard validation-rule seeds use the vrule_ prefix so the pack can
	// retire rows removed from standard_validation_rules.json without claiming
	// random-id admin rows as pack-owned metadata.
	standardValidationRuleIDPrefix = "vrule_"
)

// ---- Pack ----

// PlatformConfigPack manages platform-owned product configuration:
// roles, record types, standard list fields, folders, and auto-numbers.
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)
	}
	if err := ApplyRecordTypes(deps.DB, recordTypesJSON); err != nil {
		return fmt.Errorf("record types: %w", err)
	}
	applyStandardListFields(deps.DB)
	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)
	}

	if err := ApplyAutoNumberFormats(deps.DB, AutoNumberFormatSeeds, RecordTypeCodeSeeds); err != nil {
		return fmt.Errorf("auto-number formats: %w", err)
	}

	// Seed `_System_AutoNumberCounter` from live-max + legacy + start
	// and run the V1-V6 verify gate. Runs HERE (after
	// ApplyAutoNumberFormats) so both steps read the seed list we just
	// wrote to `_System_Field`, and so we pass the generator's full seed
	// slice — including LegacySource — to BuildScopeSourceMapFromSeeds.
	//
	// Round-5 Codex V1-P1 / V2-C1: the previous implementation lived
	// in bootstrap phase5 and re-read `_System_Field` via a 5-column
	// query that dropped LegacySource, forcing every counter to seed
	// from (live+1 ∨ start) and ignoring the legacy floor. Running in
	// packs.Apply resolves the bootstrap ↔ packs import cycle and the
	// column-drop bug simultaneously.
	//
	// Lock context: this call runs under the outer
	// `nexuscrm_controlplane_apply` advisory lock held by
	// controlplane.ApplyAll (operations.go), so multi-pod concurrency
	// is serialized without needing a nested GET_LOCK. That matches
	// the existing F1 contract documented in
	// bootstrap/autonumber_seed.go.
	seeds := BootstrapSeeds()
	scopeMap := bootstrap.BuildScopeSourceMapFromSeeds(seeds)
	if err := bootstrap.SeedAutoNumberCounters(ctx, deps.DB, seeds, scopeMap); err != nil {
		return fmt.Errorf("seed autonumber counters: %w", err)
	}
	if err := bootstrap.VerifyAutoNumberInvariants(ctx, deps.DB); err != nil {
		// Production-migration escape hatch — same env gate as the
		// seed scan (autonumber_seed.go). When legacy rows force
		// seed to skip-and-warn, the post-seed invariant check
		// observes V6 RecordType-code gaps + V7 per_prefix bucket
		// drift that reflect those skipped rows (not new bugs).
		// Surface as WARN so operators see the list via
		// `cmd/setup verify --deep`, but do not abort Apply — the
		// container has to come up so the running service can
		// accept new inserts via the v2 format. Unset / "false"
		// preserves strict behavior for CI + local dev.
		if os.Getenv("AUTONUMBER_SEED_TOLERATE_LEGACY") == "true" {
			log.Printf("⚠️  post-seed verify invariants reported violations — continuing (AUTONUMBER_SEED_TOLERATE_LEGACY=true): %v", err)
		} else {
			return fmt.Errorf("verify autonumber invariants: %w", err)
		}
	}

	if err := bootstrap.InitializeFieldMappings(deps.DB); err != nil {
		return fmt.Errorf("field mappings: %w", err)
	}

	if err := applyStandardValidationRules(ctx, deps.DB); err != nil {
		return fmt.Errorf("standard validation rules: %w", err)
	}

	if err := bootstrap.InitializeStandardFlows(deps.DB); err != nil {
		return fmt.Errorf("standard flows: %w", err)
	}

	if err := bootstrap.InitializeStandardActions(deps.DB); err != nil {
		return fmt.Errorf("standard actions: %w", err)
	}

	// Validators for the Quote/Order cutover's new metadata surfaces
	// (ValidateInsertInheritFromLookupRules, ValidateObjectChildCloneRules)
	// are invoked by the orchestrator (controlplane.ApplyPacks +
	// ApplyTenant) AFTER the cache refresh that follows each pack, so
	// they run against authoritative post-apply state instead of a
	// stale cache. Pack.Apply stays pure writes.

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

func applyStandardValidationRules(ctx context.Context, db *sql.DB) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}

	var data standardValidationRulesData
	if err := json.Unmarshal(standardValidationRulesJSON, &data); err != nil {
		return fmt.Errorf("parse standard_validation_rules.json: %w", err)
	}

	return applyStandardValidationRuleSeeds(ctx, db, data.ValidationRules)
}

func applyStandardValidationRuleSeeds(ctx context.Context, db *sql.DB, seeds []models.ValidationRule) 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 := protectLegacyValidationRuleOwnership(ctx, db, rules); err != nil {
		return err
	}

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

	return reconcileStandardValidationRuleRemovals(ctx, db, rules)
}

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

	businessKeyPredicates := make([]string, 0, len(packOwnedBusinessKeys))
	for range packOwnedBusinessKeys {
		businessKeyPredicates = append(businessKeyPredicates, "(ObjectApiName = ? AND Name = ?)")
	}
	query := fmt.Sprintf(`
UPDATE _System_ValidationRule
SET IsCustomized = 1
WHERE (IsCustomized = 0 OR IsCustomized IS NULL)
  AND LEFT(Id, ?) <> ?`)
	if len(packOwnedIDs) > 0 {
		placeholders := strings.TrimRight(strings.Repeat("?,", len(packOwnedIDs)), ",")
		query += fmt.Sprintf(`
  AND Id NOT IN (%s)`, placeholders)
	}
	if len(businessKeyPredicates) > 0 {
		query += fmt.Sprintf(`
  AND NOT (%s)`, strings.Join(businessKeyPredicates, " OR "))
	}

	args := make([]interface{}, 0, 2+len(packOwnedIDs)+len(packOwnedBusinessKeys)*2)
	args = append(args, len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix)
	for _, id := range packOwnedIDs {
		args = append(args, id)
	}
	for _, rule := range packOwnedBusinessKeys {
		args = append(args, rule.ObjectAPIName, rule.Name)
	}
	if _, err := db.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("protect legacy validation rule ownership: %w", err)
	}
	return nil
}

func reconcileStandardValidationRuleRemovals(ctx context.Context, db *sql.DB, rules []models.ValidationRule) error {
	packOwnedIDs := make([]string, 0, len(rules))
	for i := range rules {
		rule := normalizeValidationRuleSeed(rules[i])
		if rule.ID != "" {
			packOwnedIDs = append(packOwnedIDs, rule.ID)
		}
	}

	query := `
UPDATE _System_ValidationRule
SET Active = 0, LastModifiedDate = CURRENT_TIMESTAMP
WHERE (IsCustomized = 0 OR IsCustomized IS NULL)
  AND LEFT(Id, ?) = ?
  AND Active = 1`
	args := make([]interface{}, 0, 2+len(packOwnedIDs))
	args = append(args, len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix)
	if len(packOwnedIDs) > 0 {
		placeholders := strings.TrimRight(strings.Repeat("?,", len(packOwnedIDs)), ",")
		query += fmt.Sprintf(`
  AND Id NOT IN (%s)`, placeholders)
		for _, id := range packOwnedIDs {
			args = append(args, id)
		}
	}

	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) error {
	const query = `
INSERT INTO _System_ValidationRule
  (Id, ObjectApiName, Name, Description, Active, ` + "`Condition`" + `, ErrorMessage,
   ErrorDisplayField, IsCustomized, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE
  ObjectApiName = IF(IsCustomized = 0, VALUES(ObjectApiName), ObjectApiName),
  Name = IF(IsCustomized = 0, VALUES(Name), Name),
  Description = IF(IsCustomized = 0, VALUES(Description), Description),
  Active = IF(IsCustomized = 0, VALUES(Active), Active),
  ` + "`Condition`" + ` = IF(IsCustomized = 0, VALUES(` + "`Condition`" + `), ` + "`Condition`" + `),
  ErrorMessage = IF(IsCustomized = 0, VALUES(ErrorMessage), ErrorMessage),
  ErrorDisplayField = IF(IsCustomized = 0, VALUES(ErrorDisplayField), ErrorDisplayField),
  LastModifiedDate = IF(IsCustomized = 0, CURRENT_TIMESTAMP, LastModifiedDate)`

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

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

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 {
	// Two contracts:
	//
	//   * Pod readiness (DeepChecks=false) — run ONLY metadata-only
	//     gates (V1-V4, V6, V8). Cheap, deterministic, tolerant of
	//     concurrent writers. Fail-closes empty-metadata silent-boot,
	//     parser/hash drift, missing counter rows, and missing
	//     RecordTypeCode entries. Codex round-9 V2 HIGH: running the
	//     full verifier here makes readiness latency proportional to
	//     live table size and couples pod boot to writer quiescence,
	//     producing verify→apply→verify oscillation during rolling
	//     deploys.
	//
	//   * Operator `cmd/setup verify` (DeepChecks=true) — additionally
	//     runs V5 + V7 (live-data scans). Operator-initiated so
	//     writers-quiesced precondition is satisfied; this preserves
	//     the original cmd/setup verify coverage contract.
	// Same env gate as Apply's post-seed verify (AUTONUMBER_SEED_TOLERATE_LEGACY).
	// When legacy live rows force the seed scan to skip, V6 RecordType-code
	// gaps and V7 per_prefix drift surface here too — and on every pod boot
	// because ApplyAll's step-7 final verify calls this, and entrypoint.sh
	// calls `cmd/setup verify` before each start. Surface as WARN in deploy
	// envs so the pipeline proceeds, but leave operator-invoked strict verify
	// (env unset / "false") fail-closed so admins still see the violations
	// explicitly via `cmd/setup verify` without the env var.
	tolerate := os.Getenv("AUTONUMBER_SEED_TOLERATE_LEGACY") == "true"

	if deps.DeepChecks {
		if err := bootstrap.VerifyAutoNumberInvariants(ctx, deps.DB); err != nil {
			if tolerate {
				log.Printf("⚠️  verify autonumber invariants (deep) reported violations — continuing (AUTONUMBER_SEED_TOLERATE_LEGACY=true): %v", err)
				return nil
			}
			return fmt.Errorf("verify autonumber invariants (deep): %w", err)
		}
		return nil
	}
	if err := bootstrap.VerifyAutoNumberMetadataInvariants(ctx, deps.DB); err != nil {
		if tolerate {
			log.Printf("⚠️  verify autonumber metadata invariants reported violations — continuing (AUTONUMBER_SEED_TOLERATE_LEGACY=true): %v", err)
			return nil
		}
		return fmt.Errorf("verify autonumber metadata invariants: %w", err)
	}
	return 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 standard (record_types.json) and
// tenant (tenant_record_types.json) seeds flow through the same writer
// without Config/Tenant packs duplicating the upsert SQL.
func ApplyRecordTypes(rawDB *sql.DB, jsonBytes []byte) 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()
	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 {
			log.Printf("   ⚠️  Object %s not found, skipping record types: %v", obj.ObjectApiName, err)
			continue
		}

		for _, rt := range obj.Types {
			query := fmt.Sprintf(
				`INSERT INTO %s (%s, %s, %s, %s, %s, Description, CreatedDate, LastModifiedDate)
				VALUES (?, ?, ?, 1, 0, '', NOW(), NOW())
				ON DUPLICATE KEY UPDATE
					%s = VALUES(%s),
					LastModifiedDate = NOW()`,
				constants.TableRecordType,
				constants.FieldID, constants.FieldObjectID, constants.FieldSysRecordType_Name,
				constants.FieldSysRecordType_IsActive, constants.FieldSysRecordType_IsMaster,
				constants.FieldSysRecordType_Name, constants.FieldSysRecordType_Name,
			)

			if _, err := rawDB.ExecContext(ctx, query, rt.ID, objectID, rt.Name); err != nil {
				return fmt.Errorf("ensure record type %s/%s: %w", obj.ObjectApiName, rt.Name, err)
			}
			log.Printf("   ✅ Ensured record type: %s/%s", obj.ObjectApiName, rt.Name)
		}
	}
	return nil
}

func applyStandardListFields(rawDB *sql.DB) {
	log.Println("🔧 Initializing standard list fields...")
	start := time.Now()

	var listFieldsByObject map[string][]string
	if err := json.Unmarshal(standardListFieldsJSON, &listFieldsByObject); err != nil {
		log.Printf("   ⚠️  Failed to parse standard_listfields.json: %v", err)
		return
	}

	updated := 0
	for objectApiName, fields := range listFieldsByObject {
		fieldsJSON, err := json.Marshal(fields)
		if err != nil {
			log.Printf("   ⚠️  Failed to marshal list fields for %s: %v", objectApiName, err)
			continue
		}

		result, err := rawDB.Exec(
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = ? WHERE `%s` = ? AND (`%s` IS NULL OR `%s` = '' OR `%s` = '[]') AND (`%s` = 0 OR `%s` IS NULL)",
				constants.TableObject,
				constants.FieldSysObject_ListFields,
				constants.FieldAPIName,
				constants.FieldSysObject_ListFields,
				constants.FieldSysObject_ListFields,
				constants.FieldSysObject_ListFields,
				constants.FieldSysObject_IsCustomized,
				constants.FieldSysObject_IsCustomized,
			),
			string(fieldsJSON), objectApiName,
		)
		if err != nil {
			log.Printf("   ⚠️  Failed to update list fields for %s: %v", objectApiName, err)
			continue
		}
		if n, _ := result.RowsAffected(); n > 0 {
			updated++
		}
	}

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

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()

	defaultFolders := []struct {
		ID, Name, DeveloperName, Type, AccessType string
	}{
		{"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"},
	}

	for _, folder := range defaultFolders {
		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
}

// ApplyAutoNumberFormats seeds _System_Field.auto_number_* columns and
// _System_RecordType.RecordTypeCode from the supplied seed slices
// (see platform_config_autonumber_formats.go for the standard set;
// tenant seeds arrive via controlplane/tenants/<tenant>/autonumber_formats.go).
//
// Wired into PlatformConfigPack.Apply (Lane C1 → E, cutover landed):
// standard seeds apply during ApplyAll; tenant seeds apply during
// cmd/setup apply-tenant.
//
// 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 (e.g. Quote.Name's OpportunityId →
//     Opportunity.AccountId fallback). 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 — this matches existing platform_config behavior (see
// ApplyRecordTypes "Object not found, skipping").
// ApplyAutoNumberFormats upserts the supplied format/RT-code seed slices.
// Standard seeds flow in from Config (AutoNumberFormatSeeds, RecordTypeCodeSeeds);
// tenant seeds flow in from PlatformTenantPack (TenantAutoNumberFormatSeeds,
// TenantRecordTypeCodeSeeds). Seeds as parameters instead of reading globals so
// the same writer serves both packs, per issue #1609.
func ApplyAutoNumberFormats(rawDB *sql.DB, formats []AutoNumberFormatSeed, rtCodes []RecordTypeCodeSeed) 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 <=> ?))`

	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,
		)
		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. Today that is Quote.Name
	// (OpportunityId → Opportunity.AccountId → Account.BillingCountryCode
	// fallback 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 (PRD §4.1 P2-R3-canonicalize-owner).
	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 (f.auto_number_token_refs IS NULL
		    OR f.auto_number_token_refs <> ?)`
	var tokenRefsUpdated, tokenRefsSkipped int
	for _, seed := range formats {
		if seed.TokenRefsJSON == "" {
			continue
		}
		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)
		}
		canonicalStr := string(canonical)
		res, err := rawDB.ExecContext(ctx, tokenRefsQuery,
			canonicalStr,
			seed.ObjectName, seed.FieldApiName,
			canonicalStr,
		)
		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)
	}

	// Section 5.3: _System_RecordType.RecordTypeCode.
	// Matches by Name (exact) because _System_RecordType lacks a
	// DeveloperName column in the current schema. Name values are
	// seeded from record_types.json via ApplyRecordTypes above.
	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
}