package bootstrap

import (
	"context"
	"fmt"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"

	"github.com/nexuscrm/backend/internal/features/automation/autonumber"
)

// TestRunV7_HappyPath_CoversAllLivePrefixes pins the V7 contract:
// every prefix observed in live data MUST have a counter row with
// NextValue > live_max. Two live prefixes, two corresponding counter
// rows with NextValue = max+1 → zero violations.
func TestRunV7_HappyPath_CoversAllLivePrefixes(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	fields := []autonumberField{{
		ID:         "fld_quote_name",
		ObjectName: "Quote",
		FieldName:  "Name",
		Format:     "{Account.BillingCountryCode}-QT-{YY}{MM}{DD}-{00}",
		Scope:      autonumber.ScopePerPrefix,
		Start:      1,
	}}
	counters := map[string]counterRow{
		"fld_quote_name::JP-QT-260418-": {FieldID: "fld_quote_name", ScopeKey: "JP-QT-260418-", NextValue: 8},
		"fld_quote_name::US-QT-260418-": {FieldID: "fld_quote_name", ScopeKey: "US-QT-260418-", NextValue: 3},
	}

	mock.ExpectQuery("INFORMATION_SCHEMA.COLUMNS").
		WithArgs("Quote", "Name").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT `Id`, `Name` FROM `Quote`").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("r1", "JP-QT-260418-07").
			AddRow("r2", "JP-QT-260418-03").
			AddRow("r3", "US-QT-260418-02"))

	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV7(context.Background(), db, fields, counters, add); err != nil {
		t.Fatalf("runV7: unexpected error: %v", err)
	}
	if len(violations) != 0 {
		t.Errorf("expected zero violations, got %d: %v", len(violations), violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("unmet sqlmock expectations: %v", err)
	}
}

// TestRunV7_MissingCounterRow simulates the mid-seed race where a new
// prefix bucket appeared AFTER the seed scan: live data has a prefix
// with no counter row. V7 must flag the drift.
func TestRunV7_MissingCounterRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	fields := []autonumberField{{
		ID:         "fld_quote_name",
		ObjectName: "Quote",
		FieldName:  "Name",
		Format:     "{Account.BillingCountryCode}-QT-{YY}{MM}{DD}-{00}",
		Scope:      autonumber.ScopePerPrefix,
		Start:      1,
	}}
	counters := map[string]counterRow{
		"fld_quote_name::JP-QT-260418-": {FieldID: "fld_quote_name", ScopeKey: "JP-QT-260418-", NextValue: 2},
		// US-QT-260418- bucket MISSING — simulates seed missing this bucket.
	}

	mock.ExpectQuery("INFORMATION_SCHEMA.COLUMNS").
		WithArgs("Quote", "Name").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT `Id`, `Name` FROM `Quote`").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("r1", "JP-QT-260418-01").
			AddRow("r2", "US-QT-260418-05"))

	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV7(context.Background(), db, fields, counters, add); err != nil {
		t.Fatalf("runV7: unexpected error: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("expected 1 violation for missing counter row, got %d: %v", len(violations), violations)
	}
	if !strings.Contains(violations[0], "US-QT-260418-") || !strings.Contains(violations[0], "no counter row") {
		t.Errorf("violation should name the missing bucket: %q", violations[0])
	}
}

// TestRunV7_StaleCounter simulates the mid-seed race where the counter
// row exists but its NextValue is stale because a concurrent writer
// inserted into the bucket after the scan: live_max >= NextValue.
func TestRunV7_StaleCounter(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	fields := []autonumberField{{
		ID:         "fld_quote_name",
		ObjectName: "Quote",
		FieldName:  "Name",
		Format:     "{Account.BillingCountryCode}-QT-{YY}{MM}{DD}-{00}",
		Scope:      autonumber.ScopePerPrefix,
		Start:      1,
	}}
	counters := map[string]counterRow{
		// NextValue=8 expects live_max ≤ 7. But live has 09 → stale.
		"fld_quote_name::JP-QT-260418-": {FieldID: "fld_quote_name", ScopeKey: "JP-QT-260418-", NextValue: 8},
	}

	mock.ExpectQuery("INFORMATION_SCHEMA.COLUMNS").
		WithArgs("Quote", "Name").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT `Id`, `Name` FROM `Quote`").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("r1", "JP-QT-260418-09"))

	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV7(context.Background(), db, fields, counters, add); err != nil {
		t.Fatalf("runV7: unexpected error: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("expected 1 stale-counter violation, got %d: %v", len(violations), violations)
	}
	if !strings.Contains(violations[0], "stale") ||
		!strings.Contains(violations[0], "NextValue=8") ||
		!strings.Contains(violations[0], "live_max=9") {
		t.Errorf("violation should describe NextValue ≤ live_max: %q", violations[0])
	}
}

// TestRunV7_TolerantToMalformedRow — Codex round-4 finding: V7 must
// match V5's tolerant policy so a single malformed legacy row does
// NOT short-circuit drift detection for other buckets on the same
// field. The malformed row is reported as its own violation; the
// good bucket's stale-counter is still flagged.
func TestRunV7_TolerantToMalformedRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	fields := []autonumberField{{
		ID:         "fld_quote_name",
		ObjectName: "Quote",
		FieldName:  "Name",
		Format:     "{Account.BillingCountryCode}-QT-{YY}{MM}{DD}-{00}",
		Scope:      autonumber.ScopePerPrefix,
		Start:      1,
	}}
	counters := map[string]counterRow{
		// US bucket has stale NextValue vs live_max=5 — must be flagged
		// even though an unrelated row is malformed.
		"fld_quote_name::US-QT-260418-": {FieldID: "fld_quote_name", ScopeKey: "US-QT-260418-", NextValue: 3},
	}

	mock.ExpectQuery("INFORMATION_SCHEMA.COLUMNS").
		WithArgs("Quote", "Name").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT `Id`, `Name` FROM `Quote`").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).
			AddRow("r_bad", "BAD-VALUE").      // malformed — skip, but report
			AddRow("r_good", "US-QT-260418-05")) // good — must be evaluated

	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV7(context.Background(), db, fields, counters, add); err != nil {
		t.Fatalf("runV7: unexpected error: %v", err)
	}
	// Expect 2 violations: 1 for malformed row, 1 for stale US counter.
	if len(violations) != 2 {
		t.Fatalf("expected 2 violations (malformed row + stale counter), got %d: %v", len(violations), violations)
	}
	haveMalformed := false
	haveStale := false
	for _, v := range violations {
		if strings.Contains(v, "BAD-VALUE") && strings.Contains(v, "does not match") {
			haveMalformed = true
		}
		if strings.Contains(v, "US-QT-260418-") && strings.Contains(v, "stale") {
			haveStale = true
		}
	}
	if !haveMalformed {
		t.Errorf("expected malformed-row violation; got %v", violations)
	}
	if !haveStale {
		t.Errorf("expected stale-counter violation for US bucket (was NOT suppressed by malformed row); got %v", violations)
	}
}

// installDeclaredSeeds is a test helper that:
//   - registers the given seed keys AND restores the prior registry
//     state at test cleanup (so tests don't stomp a live 132-seed
//     registry if a future import pulls controlplane/packs into this
//     test binary);
//   - neutralizes AUTONUMBER_V8_FLOOR for the test's lifetime. CI
//     runners sometimes export this variable to relax V8 for unrelated
//     harness stages; without clearing it, the env-override branch
//     in runV8 short-circuits before the declared-seed path is
//     exercised and every V8 test fails in a confusing way.
//     `t.Setenv(_, "")` is equivalent to "unset" because `os.Getenv`
//     returns "" in both cases and runV8 short-circuits the override
//     branch on `override != ""`.
func installDeclaredSeeds(t *testing.T, keys []AutoNumberDeclaredSeedKey) {
	t.Helper()
	t.Setenv("AUTONUMBER_V8_FLOOR", "")
	prior := snapshotDeclaredSeedKeys()
	RegisterAutoNumberDeclaredSeeds(keys)
	t.Cleanup(func() { RegisterAutoNumberDeclaredSeeds(prior) })
}

// TestRunV8_FixedFloorFallback pins the Codex round-8 V2 CRITICAL fix
// under the fixed-floor fallback path (no declared-seed registry):
// verify must NOT silently pass when _System_Field.auto_number_format
// is empty (cmd/setup apply never ran). V1-V7 iterate the returned
// field set and report no violations on an empty slice, so without
// V8 the gate would let the engine boot with zero rules and silently
// skip autonumber assignment on every insert.
//
// Bootstrap-only unit tests do not import controlplane/packs, so the
// declared-seed registry is empty here and V8 falls back to the fixed
// floor. See TestRunV8_DeclaredReachable_* for the primary path.
func TestRunV8_FixedFloorFallback(t *testing.T) {
	// Defensive guard: if a future test file in this package imports
	// controlplane/packs (whose init() registers 132 real seeds), the
	// fallback path would stop firing and these assertions would fail
	// confusingly. Pin the registry to empty for this test and restore
	// the prior state afterwards.
	installDeclaredSeeds(t, nil)

	ctx := context.Background()
	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}

	// Empty cohort: simulates cmd/setup apply never ran.
	if err := runV8(ctx, nil, nil, add); err != nil {
		t.Fatalf("empty cohort: runV8 err: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("empty cohort: expected 1 V8 violation, got %d: %v", len(violations), violations)
	}
	if !strings.Contains(violations[0], "only 0") || !strings.Contains(violations[0], "rerun the applier") {
		t.Errorf("violation message should name the count and the remedy: %q", violations[0])
	}

	// Under-threshold cohort: simulates partial seed.
	var sparse []autonumberField
	for i := 0; i < MinExpectedAutoNumberFieldsV8-1; i++ {
		sparse = append(sparse, autonumberField{})
	}
	violations = nil
	if err := runV8(ctx, nil, sparse, add); err != nil {
		t.Fatalf("under-threshold: runV8 err: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("under-threshold cohort: expected 1 V8 violation, got %d: %v", len(violations), violations)
	}

	// At-or-above-threshold cohort: passes cleanly.
	full := make([]autonumberField, MinExpectedAutoNumberFieldsV8)
	violations = nil
	if err := runV8(ctx, nil, full, add); err != nil {
		t.Fatalf("at-threshold: runV8 err: %v", err)
	}
	if len(violations) != 0 {
		t.Errorf("at-threshold cohort should NOT trigger V8; got %v", violations)
	}
}

// TestRunV8_DeclaredReachable_PartialManagedPackageOrg pins issue #1632:
// on an org that does not have every ISV managed-package installed, the
// applier silently skips unmatched seeds and ends up with fewer than
// 100 configured rows. The previous fixed `>= 100` threshold
// false-positived this legitimate state. The declared-vs-reachable
// rewrite compares actual count against the number of declared seeds
// whose target (Object, Field) pair exists in _System_Field — so an
// org with 35 reachable seeds passes V8 cleanly when all 35 are
// written.
func TestRunV8_DeclaredReachable_PartialManagedPackageOrg(t *testing.T) {
	ctx := context.Background()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Declare 132 seeds but only 35 have matching rows in _System_Field
	// (simulates production-synced org without all ISV packs).
	declared := make([]AutoNumberDeclaredSeedKey, 0, 132)
	for i := 0; i < 132; i++ {
		declared = append(declared, AutoNumberDeclaredSeedKey{
			ObjectName:   fmt.Sprintf("Obj%d", i),
			FieldApiName: "Name",
		})
	}
	installDeclaredSeeds(t, declared)

	existing := sqlmock.NewRows([]string{"o.ApiName", "f.ApiName"})
	for i := 0; i < 35; i++ {
		existing = existing.AddRow(fmt.Sprintf("Obj%d", i), "Name")
	}
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(existing)

	// Actual cohort: all 35 reachable seeds ARE written (Object + Field
	// match the reachable set). V8 intersects on the pair, not raw
	// count, so this must pass.
	fields := make([]autonumberField, 0, 35)
	for i := 0; i < 35; i++ {
		fields = append(fields, autonumberField{
			ID:         fmt.Sprintf("fld_%d", i),
			ObjectName: fmt.Sprintf("Obj%d", i),
			FieldName:  "Name",
		})
	}
	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV8(ctx, db, fields, add); err != nil {
		t.Fatalf("runV8: %v", err)
	}
	if len(violations) != 0 {
		t.Errorf("partial-managed-package org should NOT trigger V8 when "+
			"all 35 reachable seeds are written; got %v", violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("sqlmock expectations: %v", err)
	}
}

// TestRunV8_DeclaredReachable_WriterDidNotFinish pins the inverse:
// when the actual count is below the reachable-declared count, V8
// fires with a helpful message naming both numbers.
func TestRunV8_DeclaredReachable_WriterDidNotFinish(t *testing.T) {
	ctx := context.Background()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	declared := []AutoNumberDeclaredSeedKey{
		{ObjectName: "Account", FieldApiName: "Account_ID__c"},
		{ObjectName: "Quote", FieldApiName: "Name"},
		{ObjectName: "Order", FieldApiName: "OrderNumber"},
	}
	installDeclaredSeeds(t, declared)

	// All 3 declared seeds have targets present on this DB (reachable=3).
	existing := sqlmock.NewRows([]string{"o.ApiName", "f.ApiName"}).
		AddRow("Account", "Account_ID__c").
		AddRow("Quote", "Name").
		AddRow("Order", "OrderNumber")
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(existing)

	// Actual cohort: only 1 of 3 reachable declared seeds is written.
	// Quote.Name and Order.OrderNumber are MISSING — V8 should name
	// them.
	fields := []autonumberField{{
		ID: "f1", ObjectName: "Account", FieldName: "Account_ID__c",
	}}
	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV8(ctx, db, fields, add); err != nil {
		t.Fatalf("runV8: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("expected 1 violation for under-reachable cohort; got %d: %v",
			len(violations), violations)
	}
	if !strings.Contains(violations[0], "2 of 3 reachable") ||
		!strings.Contains(violations[0], "Quote.Name") ||
		!strings.Contains(violations[0], "Order.OrderNumber") {
		t.Errorf("violation must name the counts and the missing pairs: %q",
			violations[0])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("sqlmock expectations: %v", err)
	}
}

// TestRunV8_DeclaredReachable_CustomFieldsDontMaskMissingSeeds pins
// the Copilot adversarial finding on PR #1641: if runV8 compared raw
// len(fields) against the reachable count, an org with custom /
// admin-created autonumber fields could inflate the count enough to
// meet `expected` while a reachable declared seed was still missing.
// The intersection-on-(Object, Field) implementation fails V8 when
// ANY reachable declared seed is not written, regardless of unrelated
// field count.
func TestRunV8_DeclaredReachable_CustomFieldsDontMaskMissingSeeds(t *testing.T) {
	ctx := context.Background()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	installDeclaredSeeds(t, []AutoNumberDeclaredSeedKey{
		{ObjectName: "Quote", FieldApiName: "Name"},
		{ObjectName: "Order", FieldApiName: "OrderNumber"},
	})

	// Both reachable on this DB.
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"o.ApiName", "f.ApiName"}).
			AddRow("Quote", "Name").
			AddRow("Order", "OrderNumber"))

	// Cohort: 50 unrelated custom autonumber fields + only 1 of 2
	// reachable declared seeds written. Raw len(fields)=51 comfortably
	// exceeds `expected`=2 under the old rule — but the new rule
	// intersects and sees Order.OrderNumber is missing.
	fields := make([]autonumberField, 0, 51)
	for i := 0; i < 50; i++ {
		fields = append(fields, autonumberField{
			ID:         fmt.Sprintf("custom_%d", i),
			ObjectName: fmt.Sprintf("CustomObj%d__c", i),
			FieldName:  "Serial__c",
		})
	}
	fields = append(fields, autonumberField{
		ID: "f1", ObjectName: "Quote", FieldName: "Name",
	})

	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV8(ctx, db, fields, add); err != nil {
		t.Fatalf("runV8: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("expected 1 violation (1 missing declared seed); got %d: %v",
			len(violations), violations)
	}
	if !strings.Contains(violations[0], "1 of 2 reachable") ||
		!strings.Contains(violations[0], "Order.OrderNumber") {
		t.Errorf("violation must name the missing declared seed even "+
			"with custom fields inflating the cohort: %q", violations[0])
	}
}

// TestRunV8_DeclaredReachable_ZeroReachableFallsBackToFixedFloor
// guards the edge case where a DB has NONE of the declared seed
// targets (e.g. fresh DB where schema apply never ran). Reachable=0
// must not silently pass — fall back to the fixed-floor fallback so
// empty-metadata silent-boot is still caught.
func TestRunV8_DeclaredReachable_ZeroReachableFallsBackToFixedFloor(t *testing.T) {
	ctx := context.Background()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	installDeclaredSeeds(t, []AutoNumberDeclaredSeedKey{
		{ObjectName: "Obj1", FieldApiName: "Name"},
	})

	// _System_Field has no seed-reachable pairs.
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"o.ApiName", "f.ApiName"}))

	fields := []autonumberField{} // empty cohort
	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV8(ctx, db, fields, add); err != nil {
		t.Fatalf("runV8: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("zero-reachable + empty cohort: expected 1 fallback violation; got %d: %v",
			len(violations), violations)
	}
	if !strings.Contains(violations[0], "fixed-floor fallback") {
		t.Errorf("violation must name the fallback; got: %q", violations[0])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("sqlmock expectations: %v", err)
	}
}

// TestRunV8_DeclaredReachable_CaseInsensitiveMatch guards against a
// collation-driven false-negative: TiDB's default utf8mb4 collation
// treats ApiName lookups case-insensitively, so the applier's
// `WHERE ApiName = ?` happily matches regardless of casing. If V8's
// Go-side intersection were case-sensitive, a DB row stored with
// different casing than the seed would miss and V8 would under-report
// `expected`, causing it to fail-open. countReachableDeclaredSeeds
// lowercases both sides to mirror the applier.
func TestRunV8_DeclaredReachable_CaseInsensitiveMatch(t *testing.T) {
	ctx := context.Background()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	installDeclaredSeeds(t, []AutoNumberDeclaredSeedKey{
		{ObjectName: "Account", FieldApiName: "Account_ID__c"},
	})

	// DB returns the row with different casing than the seed.
	existing := sqlmock.NewRows([]string{"o.ApiName", "f.ApiName"}).
		AddRow("account", "account_id__c")
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(existing)

	// Autonumber-configured field also stored with different casing —
	// intersection must normalize both sides.
	fields := []autonumberField{{
		ID: "f1", ObjectName: "account", FieldName: "account_id__c",
	}}
	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV8(ctx, db, fields, add); err != nil {
		t.Fatalf("runV8: %v", err)
	}
	if len(violations) != 0 {
		t.Errorf("case-different row should still intersect the seed; got %v", violations)
	}
}

// TestRunV8_EnvOverride_TakesPrecedence documents that the legacy
// AUTONUMBER_V8_FLOOR escape hatch still works: setting it short-
// circuits both the declared-seed path and the fixed-floor fallback.
// `=0` disables the gate entirely (matches prior behavior).
func TestRunV8_EnvOverride_TakesPrecedence(t *testing.T) {
	ctx := context.Background()
	// Register seeds so we would otherwise hit the declared-seed path.
	installDeclaredSeeds(t, []AutoNumberDeclaredSeedKey{
		{ObjectName: "Obj1", FieldApiName: "Name"},
	})

	add := func(format string, args ...any) {}

	t.Setenv("AUTONUMBER_V8_FLOOR", "0")
	// Pass nil DB — env override path must not query.
	if err := runV8(ctx, nil, nil, add); err != nil {
		t.Errorf("AUTONUMBER_V8_FLOOR=0 must disable gate without querying DB: %v", err)
	}

	t.Setenv("AUTONUMBER_V8_FLOOR", "3")
	var violations []string
	addCollect := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV8(ctx, nil, make([]autonumberField, 2), addCollect); err != nil {
		t.Errorf("AUTONUMBER_V8_FLOOR=3 override: %v", err)
	}
	if len(violations) != 1 {
		t.Fatalf("override floor=3 with 2 fields: expected 1 violation; got %d", len(violations))
	}
	if !strings.Contains(violations[0], "AUTONUMBER_V8_FLOOR override") {
		t.Errorf("override violation must name the env var; got: %q", violations[0])
	}
}

// TestVerifyAutoNumberMetadataInvariants_DoesNotScanLiveTables pins
// the Codex round-9 V2 HIGH fix: the pod-readiness variant must run
// V1-V4 / V6 / V8 only and MUST NOT touch live object tables (V5 /
// V7 are setup-path-only). This test sets up a non-empty cohort and
// a matching counter row, then asserts all sqlmock expectations were
// met with NO Quote/Order/Contract live SELECTs — any unexpected
// query would fail the test.
func TestVerifyAutoNumberMetadataInvariants_DoesNotScanLiveTables(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Build a cohort that clears V8's floor without referencing
	// RecordType (so V6 short-circuits without hitting the RT table).
	// Use a simple scope=global format so V3 expects a counter row
	// with ScopeKey=''.
	rows := sqlmock.NewRows([]string{
		"Id", "ApiName", "ApiName",
		"auto_number_format", "auto_number_scope", "auto_number_start",
		"token_refs", "canonical_hash",
	})
	fieldCount := MinExpectedAutoNumberFieldsV8
	for i := 0; i < fieldCount; i++ {
		rows = rows.AddRow(
			fmt.Sprintf("fld_%d", i),
			"Quote", fmt.Sprintf("Field%d", i),
			"{00000000}", autonumber.ScopeGlobal, int64(1),
			"", "",
		)
	}
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(rows)

	// _System_AutoNumberCounter exists.
	mock.ExpectQuery("INFORMATION_SCHEMA.TABLES").
		WithArgs("_System_AutoNumberCounter").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	// Counter rows for every field so V3 passes.
	counterRows := sqlmock.NewRows([]string{"FieldId", "ScopeKey", "NextValue"})
	for i := 0; i < fieldCount; i++ {
		counterRows = counterRows.AddRow(fmt.Sprintf("fld_%d", i), "", int64(1))
	}
	mock.ExpectQuery("SELECT `FieldId`, `ScopeKey`, `NextValue` FROM `_System_AutoNumberCounter`").
		WillReturnRows(counterRows)

	// V6 short-circuits because no format contains `{RecordType.*}` —
	// no further queries expected. V8 reads the in-memory slice only.
	// Any live-table SELECT (Quote, Order, etc.) would fail sqlmock.

	if err := VerifyAutoNumberMetadataInvariants(context.Background(), db); err != nil {
		t.Fatalf("metadata verify: unexpected error: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations not met: %v", err)
	}
}

// TestVerifyAutoNumberMetadataInvariants_V6ChecksRecordType extends
// the hot-path coverage to the V6 branch: when any format references
// `{RecordType.*}`, the verifier must query _System_RecordType but
// MUST NOT touch any live object table. This closes the round-9 LOW
// that the prior hot-path test did not exercise V6.
func TestVerifyAutoNumberMetadataInvariants_V6ChecksRecordType(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	fieldCount := MinExpectedAutoNumberFieldsV8
	rows := sqlmock.NewRows([]string{
		"Id", "ApiName", "ApiName",
		"auto_number_format", "auto_number_scope", "auto_number_start",
		"token_refs", "canonical_hash",
	})
	for i := 0; i < fieldCount; i++ {
		// One field references {RecordType.*} → V6 fires.
		format := "{00000000}"
		if i == 0 {
			format = "{RecordType.RecordTypeCode}-{00000000}"
		}
		rows = rows.AddRow(
			fmt.Sprintf("fld_%d", i),
			"Quote", fmt.Sprintf("Field%d", i),
			format, autonumber.ScopeGlobal, int64(1),
			"", "",
		)
	}
	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(rows)

	mock.ExpectQuery("INFORMATION_SCHEMA.TABLES").
		WithArgs("_System_AutoNumberCounter").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	counterRows := sqlmock.NewRows([]string{"FieldId", "ScopeKey", "NextValue"})
	for i := 0; i < fieldCount; i++ {
		counterRows = counterRows.AddRow(fmt.Sprintf("fld_%d", i), "", int64(1))
	}
	mock.ExpectQuery("SELECT `FieldId`, `ScopeKey`, `NextValue` FROM `_System_AutoNumberCounter`").
		WillReturnRows(counterRows)

	// V6: _System_RecordType exists, then object-scope lookup +
	// filtered RecordType scan (scope-by-Object narrowing). The test
	// uses "Quote" as the parent object for the `{RecordType.*}`
	// format; cutover's actual Contract binding exercises the same
	// code path.
	mock.ExpectQuery("INFORMATION_SCHEMA.TABLES").
		WithArgs("_System_RecordType").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT Id FROM `_System_Object` WHERE ApiName IN").
		WithArgs("Quote").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("obj_quote_id"))
	mock.ExpectQuery("SELECT Id, Name FROM `_System_RecordType`").
		WithArgs("obj_quote_id").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}))

	// Any Quote / Account / Order live-table SELECT would fail sqlmock.
	if err := VerifyAutoNumberMetadataInvariants(context.Background(), db); err != nil {
		t.Fatalf("metadata verify: unexpected error: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations not met: %v", err)
	}
}

// TestVerifyAutoNumberMetadataInvariants_EmptyCohortFailsClosed pins
// the V8 gate on the pod-readiness path: a DB where `cmd/setup apply`
// never ran (empty _System_Field cohort) must fail readiness so the
// pod does not boot into silent-no-op autonumber assignment.
func TestVerifyAutoNumberMetadataInvariants_EmptyCohortFailsClosed(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("FROM `_System_Field` f JOIN `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{
			"Id", "ApiName", "ApiName",
			"auto_number_format", "auto_number_scope", "auto_number_start",
			"token_refs", "canonical_hash",
		}))
	mock.ExpectQuery("INFORMATION_SCHEMA.TABLES").
		WithArgs("_System_AutoNumberCounter").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))

	err = VerifyAutoNumberMetadataInvariants(context.Background(), db)
	if err == nil {
		t.Fatal("expected V8 empty-cohort violation, got nil")
	}
	if !strings.Contains(err.Error(), "V8") {
		t.Errorf("expected V8 in error, got: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations not met: %v", err)
	}
}

// TestRunV7_SkipsNonPerPrefixFields ensures global / per_day fields
// bypass V7 entirely — those are covered by V3/V5.
func TestRunV7_SkipsNonPerPrefixFields(t *testing.T) {
	db, _, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	fields := []autonumberField{
		{
			ID:         "fld_quote_number",
			ObjectName: "Quote",
			FieldName:  "QuoteNumber",
			Format:     "{00000000}",
			Scope:      autonumber.ScopeGlobal,
			Start:      1,
		},
		{
			ID:         "fld_per_day",
			ObjectName: "Foo",
			FieldName:  "Bar",
			Format:     "{YYYY}{MM}{DD}-{0000}",
			Scope:      autonumber.ScopePerDay,
			Start:      1,
		},
	}

	var violations []string
	add := func(format string, args ...any) {
		violations = append(violations, fmt.Sprintf(format, args...))
	}
	if err := runV7(context.Background(), db, fields, nil, add); err != nil {
		t.Fatalf("runV7: unexpected error: %v", err)
	}
	if len(violations) != 0 {
		t.Errorf("expected 0 violations for non-per_prefix fields, got %d: %v", len(violations), violations)
	}
}