package packs

import (
	"database/sql"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// setupVerifyPhysicalSchemaMock registers the four standard infrastructure
// queries that verifyPackObjectPhysicalSchema always issues before reaching
// the LockRule comparison. lockRuleValue is the live _System_Object.LockRule
// value returned by the mock (nil = SQL NULL). isCustomized=0 (pack owns).
func setupVerifyPhysicalSchemaMock(t *testing.T, mock sqlmock.Sqlmock, lockRuleValue interface{}) {
	t.Helper()
	setupVerifyPhysicalSchemaMockWithCustomized(t, mock, lockRuleValue, 0)
}

// setupVerifyPhysicalSchemaMockWithCustomized is the same as
// setupVerifyPhysicalSchemaMock but lets callers control the IsCustomized
// column so tests can exercise the admin-override branch. LockRuleCustomized
// defaults to 0 (pack owns LockRule).
func setupVerifyPhysicalSchemaMockWithCustomized(t *testing.T, mock sqlmock.Sqlmock, lockRuleValue interface{}, isCustomized int) {
	t.Helper()
	setupVerifyPhysicalSchemaMockFull(t, mock, lockRuleValue, isCustomized, 0)
}

// setupVerifyPhysicalSchemaMockFull is the lowest-level mock helper that lets
// callers control IsCustomized, LockRuleCustomized, and the live LockRule value
// independently. All higher-level helpers delegate here.
func setupVerifyPhysicalSchemaMockFull(t *testing.T, mock sqlmock.Sqlmock, lockRuleValue interface{}, isCustomized, lockRuleCustomized int) {
	t.Helper()
	setupVerifyPhysicalSchemaMockWithReadSource(t, mock, nil, lockRuleValue, isCustomized, lockRuleCustomized)
}

func setupVerifyPhysicalSchemaMockWithReadSource(t *testing.T, mock sqlmock.Sqlmock, readSourceValue, lockRuleValue interface{}, isCustomized, lockRuleCustomized int) {
	t.Helper()
	setupVerifyPhysicalSchemaMockWithDisplayName(t, mock, readSourceValue, lockRuleValue, nil, isCustomized, lockRuleCustomized)
}

func setupVerifyPhysicalSchemaMockWithDisplayName(t *testing.T, mock sqlmock.Sqlmock, readSourceValue, lockRuleValue, displayNameFieldValue interface{}, isCustomized, lockRuleCustomized int) {
	t.Helper()
	tableRows := sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("Widget")
	if readSourceValue != nil {
		tableRows.AddRow("_System_RecordType")
	}
	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA\\.TABLES").
		WillReturnRows(tableRows)
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA\\.COLUMNS").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"}))
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "cols"}))
	mock.ExpectQuery("SELECT `TableName`, `TableType`, COALESCE\\(`Category`, ''\\), COALESCE\\(`IsManaged`, 0\\), COALESCE\\(`IsDeleted`, 0\\) FROM `_System_Table`").
		WillReturnRows(sqlmock.NewRows([]string{"TableName", "TableType", "Category", "IsManaged", "IsDeleted"}).
			AddRow("Widget", "standard_object", "crm", 1, 0))
	mock.ExpectQuery("SELECT `ApiName`, `TableType`, `KeyPrefix`, `DefaultChildRows`, `ChildCloneRules`, `ReadSource`, COALESCE\\(`ChildCloneRulesPackName`, ''\\), COALESCE\\(`ChildCloneRulesPackBusinessKey`, ''\\), COALESCE\\(`ChildCloneRulesPackManageability`, ''\\), `ChildCloneRulesPackValue`, COALESCE\\(`IsCustomized`, 0\\), COALESCE\\(`DefaultChildRowsCustomized`, 0\\), COALESCE\\(`ChildCloneRulesCustomized`, 0\\), COALESCE\\(`LockRuleCustomized`, 0\\), COALESCE\\(`IsDeleted`, 0\\), `LockRule`, `DisplayNameField` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "TableType", "KeyPrefix", "DefaultChildRows", "ChildCloneRules", "ReadSource", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, readSourceValue, "", "", "", nil, isCustomized, 0, 0, lockRuleCustomized, 0, lockRuleValue, displayNameFieldValue))
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}))
	if readSourceValue != nil {
		mock.ExpectQuery("(?s)SELECT f\\.ApiName,\\s*COALESCE\\(f\\.OwnerKind, ''\\),\\s*COALESCE\\(f\\.OwnerPack, ''\\),\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\)\\s*FROM _System_Field f\\s*JOIN _System_Object o ON o\\.Id = f\\.ObjectId\\s*WHERE o\\.ApiName = \\? AND COALESCE\\(f\\.IsDeleted, 0\\) = 0 AND COALESCE\\(o\\.IsDeleted, 0\\) = 0").
			WithArgs("Widget").
			WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerKind", "OwnerPack", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}))
	}
}

// TestVerifyPackObjectPhysicalSchemaDetectsLockRuleDrift verifies that when a
// pack spec declares lockRule="Status == 'Open'" but the live _System_Object row
// holds a different value (Locked, isCustomized=0), drift is reported.
func TestVerifyPackObjectPhysicalSchemaDetectsLockRuleDrift(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMock(t, mock, "IsArchived") // live value differs from spec

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  "Status == 'Open'", // spec value — differs from live
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want LockRule drift error")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaDetectsLockRuleCleared verifies that when a
// pack spec declares lockRule but the live row has NULL (cleared), drift is
// reported (Locked, isCustomized=0).
func TestVerifyPackObjectPhysicalSchemaDetectsLockRuleCleared(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMock(t, mock, sql.NullString{Valid: false}) // live = NULL

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  "Status == 'Open'", // spec declares a rule; live has none
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want LockRule cleared drift error")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaAcceptsMatchingLockRule verifies that when
// spec and live LockRule values match (Locked, isCustomized=0), verify passes.
func TestVerifyPackObjectPhysicalSchemaAcceptsMatchingLockRule(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	rule := "Status == 'Open'"
	setupVerifyPhysicalSchemaMock(t, mock, rule) // live matches spec

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  rule,
	}

	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil (matching LockRule)", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaAcceptsNilLockRuleWhenSpecOmits verifies
// that when neither spec nor live row has a LockRule (Locked, isCustomized=0),
// verify passes without error.
func TestVerifyPackObjectPhysicalSchemaAcceptsNilLockRuleWhenSpecOmits(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMock(t, mock, sql.NullString{Valid: false}) // live = NULL

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		// LockRule intentionally omitted — spec does not author this field
	}

	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil (no LockRule in spec or live)", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaDetectsLockRuleStaleWhenSpecOmits verifies
// that when the spec omits lockRule, IsCustomized=0, but the live row holds a
// stale non-empty value, verify reports drift (Locked, stale value not cleared).
func TestVerifyPackObjectPhysicalSchemaDetectsLockRuleStaleWhenSpecOmits(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// live = stale non-empty; IsCustomized=0 (pack owns, not admin)
	setupVerifyPhysicalSchemaMockWithCustomized(t, mock, "IsArchived", 0)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		// LockRule intentionally omitted — spec no longer authors this field
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want stale LockRule drift error")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaAllowsLockRuleAdminOverrideWhenSpecOmits
// verifies that when the spec omits lockRule, LockRuleCustomized=1 (admin
// explicitly took ownership of LockRule), and manageability is DefaultsOnly,
// verify passes. The column-scoped bit is what hands off ownership, not
// IsCustomized.
func TestVerifyPackObjectPhysicalSchemaAllowsLockRuleAdminOverrideWhenSpecOmits(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// live = non-empty; LockRuleCustomized=1 (admin explicitly authored LockRule)
	setupVerifyPhysicalSchemaMockFull(t, mock, "IsArchived", 1, 1)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		// LockRule intentionally omitted — admin owns live value via LockRuleCustomized=1
	}

	// DefaultsOnly + LockRuleCustomized=1: admin ownership is legitimate, skip LockRule check.
	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil (admin-override defaults_only, LockRuleCustomized=1, spec omits)", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaDetectsReadSourceDrift(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMockWithReadSource(
		t,
		mock,
		`{"kind":"physical_table","physicalTable":"_System_Other"}`,
		nil,
		0,
		0,
	)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want ReadSource drift error")
	}
	if !strings.Contains(err.Error(), "ReadSource") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of ReadSource", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRejectsReadSourceDriftOnCustomizedDefaultsOnlyRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMockWithReadSource(
		t,
		mock,
		`{"kind":"physical_table","physicalTable":"_System_Other"}`,
		nil,
		1,
		0,
	)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want ReadSource drift on customized defaults_only row")
	}
	if !strings.Contains(err.Error(), "ReadSource") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of ReadSource", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaAllowsMetadataOnlyReadSourceObject(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA\\.TABLES").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("_System_RecordType"))
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA\\.COLUMNS").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"}))
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "cols"}))
	mock.ExpectQuery("SELECT `TableName`, `TableType`, COALESCE\\(`Category`, ''\\), COALESCE\\(`IsManaged`, 0\\), COALESCE\\(`IsDeleted`, 0\\) FROM `_System_Table`").
		WillReturnRows(sqlmock.NewRows([]string{"TableName", "TableType", "Category", "IsManaged", "IsDeleted"}))
	mock.ExpectQuery("SELECT `ApiName`, `TableType`, `KeyPrefix`, `DefaultChildRows`, `ChildCloneRules`, `ReadSource`, COALESCE\\(`ChildCloneRulesPackName`, ''\\), COALESCE\\(`ChildCloneRulesPackBusinessKey`, ''\\), COALESCE\\(`ChildCloneRulesPackManageability`, ''\\), `ChildCloneRulesPackValue`, COALESCE\\(`IsCustomized`, 0\\), COALESCE\\(`DefaultChildRowsCustomized`, 0\\), COALESCE\\(`ChildCloneRulesCustomized`, 0\\), COALESCE\\(`LockRuleCustomized`, 0\\), COALESCE\\(`IsDeleted`, 0\\), `LockRule`, `DisplayNameField` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "TableType", "KeyPrefix", "DefaultChildRows", "ChildCloneRules", "ReadSource", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, `{"kind":"physical_table","physicalTable":"_System_RecordType"}`, "", "", "", nil, 0, 0, 0, 0, 0, nil, nil))
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}))
	mock.ExpectQuery("(?s)SELECT f\\.ApiName,\\s*COALESCE\\(f\\.OwnerKind, ''\\),\\s*COALESCE\\(f\\.OwnerPack, ''\\),\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\)\\s*FROM _System_Field f\\s*JOIN _System_Object o ON o\\.Id = f\\.ObjectId\\s*WHERE o\\.ApiName = \\? AND COALESCE\\(f\\.IsDeleted, 0\\) = 0 AND COALESCE\\(o\\.IsDeleted, 0\\) = 0").
		WithArgs("Widget").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerKind", "OwnerPack", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}))

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
	}

	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil for metadata-only readSource object", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRejectsStaleReadSourcePhysicalTableSurface(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA\\.TABLES").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("Widget").AddRow("_System_RecordType"))
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA\\.COLUMNS").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"}))
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "cols"}))
	mock.ExpectQuery("SELECT `TableName`, `TableType`, COALESCE\\(`Category`, ''\\), COALESCE\\(`IsManaged`, 0\\), COALESCE\\(`IsDeleted`, 0\\) FROM `_System_Table`").
		WillReturnRows(sqlmock.NewRows([]string{"TableName", "TableType", "Category", "IsManaged", "IsDeleted"}).
			AddRow("Widget", "standard_object", "crm", 1, 0))
	mock.ExpectQuery("SELECT `ApiName`, `TableType`, `KeyPrefix`, `DefaultChildRows`, `ChildCloneRules`, `ReadSource`, COALESCE\\(`ChildCloneRulesPackName`, ''\\), COALESCE\\(`ChildCloneRulesPackBusinessKey`, ''\\), COALESCE\\(`ChildCloneRulesPackManageability`, ''\\), `ChildCloneRulesPackValue`, COALESCE\\(`IsCustomized`, 0\\), COALESCE\\(`DefaultChildRowsCustomized`, 0\\), COALESCE\\(`ChildCloneRulesCustomized`, 0\\), COALESCE\\(`LockRuleCustomized`, 0\\), COALESCE\\(`IsDeleted`, 0\\), `LockRule`, `DisplayNameField` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "TableType", "KeyPrefix", "DefaultChildRows", "ChildCloneRules", "ReadSource", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, `{"kind":"physical_table","physicalTable":"_System_RecordType"}`, "", "", "", nil, 0, 0, 0, 0, 0, nil, nil))
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}))
	mock.ExpectQuery("(?s)SELECT f\\.ApiName,\\s*COALESCE\\(f\\.OwnerKind, ''\\),\\s*COALESCE\\(f\\.OwnerPack, ''\\),\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\)\\s*FROM _System_Field f\\s*JOIN _System_Object o ON o\\.Id = f\\.ObjectId\\s*WHERE o\\.ApiName = \\? AND COALESCE\\(f\\.IsDeleted, 0\\) = 0 AND COALESCE\\(o\\.IsDeleted, 0\\) = 0").
		WithArgs("Widget").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerKind", "OwnerPack", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}))

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want stale legacy surface error")
	}
	if !strings.Contains(err.Error(), "stale physical table") && !strings.Contains(err.Error(), "stale _System_Table row") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want stale readSource legacy surface detail", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRejectsUnsupportedReadSourceAdminField(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA\\.TABLES").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("_System_RecordType"))
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA\\.COLUMNS").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"}))
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "cols"}))
	mock.ExpectQuery("SELECT `TableName`, `TableType`, COALESCE\\(`Category`, ''\\), COALESCE\\(`IsManaged`, 0\\), COALESCE\\(`IsDeleted`, 0\\) FROM `_System_Table`").
		WillReturnRows(sqlmock.NewRows([]string{"TableName", "TableType", "Category", "IsManaged", "IsDeleted"}))
	mock.ExpectQuery("SELECT `ApiName`, `TableType`, `KeyPrefix`, `DefaultChildRows`, `ChildCloneRules`, `ReadSource`, COALESCE\\(`ChildCloneRulesPackName`, ''\\), COALESCE\\(`ChildCloneRulesPackBusinessKey`, ''\\), COALESCE\\(`ChildCloneRulesPackManageability`, ''\\), `ChildCloneRulesPackValue`, COALESCE\\(`IsCustomized`, 0\\), COALESCE\\(`DefaultChildRowsCustomized`, 0\\), COALESCE\\(`ChildCloneRulesCustomized`, 0\\), COALESCE\\(`LockRuleCustomized`, 0\\), COALESCE\\(`IsDeleted`, 0\\), `LockRule`, `DisplayNameField` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "TableType", "KeyPrefix", "DefaultChildRows", "ChildCloneRules", "ReadSource", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, `{"kind":"physical_table","physicalTable":"_System_RecordType"}`, "", "", "", nil, 0, 0, 0, 0, 0, nil, nil))
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}).
			AddRow("Widget", "CustomScore__c", "admin", "", 0, nil))
	mock.ExpectQuery("(?s)SELECT f\\.ApiName,\\s*COALESCE\\(f\\.OwnerKind, ''\\),\\s*COALESCE\\(f\\.OwnerPack, ''\\),\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\)\\s*FROM _System_Field f\\s*JOIN _System_Object o ON o\\.Id = f\\.ObjectId\\s*WHERE o\\.ApiName = \\? AND COALESCE\\(f\\.IsDeleted, 0\\) = 0 AND COALESCE\\(o\\.IsDeleted, 0\\) = 0").
		WithArgs("Widget").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerKind", "OwnerPack", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow("CustomScore__c", "admin", "", "", "", "", ""))

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want unsupported admin field error")
	}
	if !strings.Contains(err.Error(), "unsupported live field Widget.CustomScore__c") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want unsupported admin field detail", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRejectsUnbackedReadSourcePublishedField(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA\\.TABLES").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("_System_RecordType"))
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA\\.COLUMNS").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"}).
			AddRow("_System_RecordType", "Id", "varchar(18)", "NO", nil, nil))
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "cols"}))
	mock.ExpectQuery("SELECT `TableName`, `TableType`, COALESCE\\(`Category`, ''\\), COALESCE\\(`IsManaged`, 0\\), COALESCE\\(`IsDeleted`, 0\\) FROM `_System_Table`").
		WillReturnRows(sqlmock.NewRows([]string{"TableName", "TableType", "Category", "IsManaged", "IsDeleted"}))
	mock.ExpectQuery("SELECT `ApiName`, `TableType`, `KeyPrefix`, `DefaultChildRows`, `ChildCloneRules`, `ReadSource`, COALESCE\\(`ChildCloneRulesPackName`, ''\\), COALESCE\\(`ChildCloneRulesPackBusinessKey`, ''\\), COALESCE\\(`ChildCloneRulesPackManageability`, ''\\), `ChildCloneRulesPackValue`, COALESCE\\(`IsCustomized`, 0\\), COALESCE\\(`DefaultChildRowsCustomized`, 0\\), COALESCE\\(`ChildCloneRulesCustomized`, 0\\), COALESCE\\(`LockRuleCustomized`, 0\\), COALESCE\\(`IsDeleted`, 0\\), `LockRule`, `DisplayNameField` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "TableType", "KeyPrefix", "DefaultChildRows", "ChildCloneRules", "ReadSource", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, `{"kind":"physical_table","physicalTable":"_System_RecordType"}`, "", "", "", nil, 0, 0, 0, 0, 0, nil, nil))
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}).
			AddRow("Widget", constants.FieldCreatedByID, "pack", "crm", 0, nil))
	mock.ExpectQuery("(?s)SELECT f\\.ApiName,\\s*COALESCE\\(f\\.OwnerKind, ''\\),\\s*COALESCE\\(f\\.OwnerPack, ''\\),\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\)\\s*FROM _System_Field f\\s*JOIN _System_Object o ON o\\.Id = f\\.ObjectId\\s*WHERE o\\.ApiName = \\? AND COALESCE\\(f\\.IsDeleted, 0\\) = 0 AND COALESCE\\(o\\.IsDeleted, 0\\) = 0").
		WithArgs("Widget").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerKind", "OwnerPack", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}))

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
		Columns: []domainschema.ColumnDefinition{
			{Name: constants.FieldCreatedByID},
		},
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want readSource backing-column error")
	}
	if !strings.Contains(err.Error(), "readSource column Widget.CreatedById is missing from _System_RecordType") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want readSource backing-column detail", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRejectsReadSourceActiveComputedField(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA\\.TABLES").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("_System_RecordType"))
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA\\.COLUMNS").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"}))
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT\\(COLUMN_NAME ORDER BY SEQ_IN_INDEX\\)").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "cols"}))
	mock.ExpectQuery("SELECT `TableName`, `TableType`, COALESCE\\(`Category`, ''\\), COALESCE\\(`IsManaged`, 0\\), COALESCE\\(`IsDeleted`, 0\\) FROM `_System_Table`").
		WillReturnRows(sqlmock.NewRows([]string{"TableName", "TableType", "Category", "IsManaged", "IsDeleted"}))
	mock.ExpectQuery("SELECT `ApiName`, `TableType`, `KeyPrefix`, `DefaultChildRows`, `ChildCloneRules`, `ReadSource`, COALESCE\\(`ChildCloneRulesPackName`, ''\\), COALESCE\\(`ChildCloneRulesPackBusinessKey`, ''\\), COALESCE\\(`ChildCloneRulesPackManageability`, ''\\), `ChildCloneRulesPackValue`, COALESCE\\(`IsCustomized`, 0\\), COALESCE\\(`DefaultChildRowsCustomized`, 0\\), COALESCE\\(`ChildCloneRulesCustomized`, 0\\), COALESCE\\(`LockRuleCustomized`, 0\\), COALESCE\\(`IsDeleted`, 0\\), `LockRule`, `DisplayNameField` FROM `_System_Object`").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "TableType", "KeyPrefix", "DefaultChildRows", "ChildCloneRules", "ReadSource", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, `{"kind":"physical_table","physicalTable":"_System_RecordType"}`, "", "", "", nil, 0, 0, 0, 0, 0, nil, nil))
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}).
			AddRow("Widget", "FormulaName__c", "pack", "crm", 0, nil))
	mock.ExpectQuery("(?s)SELECT f\\.ApiName,\\s*COALESCE\\(f\\.OwnerKind, ''\\),\\s*COALESCE\\(f\\.OwnerPack, ''\\),\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\)\\s*FROM _System_Field f\\s*JOIN _System_Object o ON o\\.Id = f\\.ObjectId\\s*WHERE o\\.ApiName = \\? AND COALESCE\\(f\\.IsDeleted, 0\\) = 0 AND COALESCE\\(o\\.IsDeleted, 0\\) = 0").
		WithArgs("Widget").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerKind", "OwnerPack", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow("FormulaName__c", "pack", "crm", "active", "", "Name", ""))

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
		Columns: []domainschema.ColumnDefinition{
			{Name: "FormulaName__c"},
		},
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want active computed field error")
	}
	if !strings.Contains(err.Error(), "active computed field FormulaName__c") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want active computed field detail", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaDetectsDisplayNameFieldDrift(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMockWithDisplayName(t, mock, nil, nil, "Name", 0, 0)

	def := domainschema.TableDefinition{
		TableName:        "Widget",
		TableType:        "standard_object",
		Category:         "crm",
		DisplayNameField: "AccountId",
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want DisplayNameField drift error")
	}
	if !strings.Contains(err.Error(), "DisplayNameField") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of DisplayNameField", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaDetectsDisplayNameFieldStaleWhenSpecOmits(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMockWithDisplayName(t, mock, nil, nil, "AccountId", 0, 0)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want stale DisplayNameField drift error")
	}
	if !strings.Contains(err.Error(), "DisplayNameField") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of DisplayNameField", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaAllowsDisplayNameFieldAdminOverrideForDefaultsOnlyCustomizedRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMockWithDisplayName(t, mock, nil, nil, "Name", 1, 0)

	def := domainschema.TableDefinition{
		TableName:        "Widget",
		TableType:        "standard_object",
		Category:         "crm",
		DisplayNameField: "AccountId",
	}

	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil (defaults_only customized row owns DisplayNameField)", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaDetectsDisplayNameFieldDriftWhenDefaultsOnlyAndNotCustomized(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	setupVerifyPhysicalSchemaMockWithDisplayName(t, mock, nil, nil, "Name", 0, 0)

	def := domainschema.TableDefinition{
		TableName:        "Widget",
		TableType:        "standard_object",
		Category:         "crm",
		DisplayNameField: "AccountId",
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want DisplayNameField drift error (defaults_only, not customized)")
	}
	if !strings.Contains(err.Error(), "DisplayNameField") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of DisplayNameField", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaAllowsLockRuleAdminOverrideWhenSpecMismatches
// verifies that when the spec declares lockRule but LockRuleCustomized=1 (admin
// explicitly took ownership) and manageability is DefaultsOnly, verify passes.
// The column-scoped LockRuleCustomized bit transfers ownership to the admin.
func TestVerifyPackObjectPhysicalSchemaAllowsLockRuleAdminOverrideWhenSpecMismatches(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// live = "IsArchived"; spec says "Status == 'Open'"; LockRuleCustomized=1 (admin owns LockRule)
	setupVerifyPhysicalSchemaMockFull(t, mock, "IsArchived", 1, 1)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  "Status == 'Open'", // pack spec value — admin has overridden it via LockRuleCustomized=1
	}

	// DefaultsOnly + LockRuleCustomized=1: admin override is legitimate, skip LockRule check.
	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil (admin-override defaults_only, LockRuleCustomized=1, spec mismatch allowed)", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaLockedOverridesAdminWhenIsCustomizedTrueAndDiffers
// verifies that when manageability is Locked and IsCustomized=1, a LockRule
// mismatch is still reported as drift. Locked manageability means the pack owns
// LockRule regardless of admin edits — isCustomized=1 is structural drift, not
// a legitimate override.
func TestVerifyPackObjectPhysicalSchemaLockedOverridesAdminWhenIsCustomizedTrueAndDiffers(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// live = different value; IsCustomized=1 (admin wrote it); but Locked
	setupVerifyPhysicalSchemaMockWithCustomized(t, mock, "IsArchived", 1)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  "Status == 'Open'", // spec — locked pack owns this
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want DRIFT (Locked overrides admin for LockRule)")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaDetectsLockRuleDriftWhenDefaultsOnlyAndNotCustomized
// verifies that when spec.LockRule is set, manageability is DefaultsOnly,
// IsCustomized=0 (admin has not taken ownership), and the live value differs,
// verify still reports drift. The pack retains ownership because the admin
// never customized the field.
func TestVerifyPackObjectPhysicalSchemaDetectsLockRuleDriftWhenDefaultsOnlyAndNotCustomized(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// live = "Status == 'Closed'"; spec says "Status == 'Open'"; IsCustomized=0 (pack owns)
	setupVerifyPhysicalSchemaMockWithCustomized(t, mock, "Status == 'Closed'", 0)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  "Status == 'Open'", // spec value — differs from live
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want LockRule drift error (defaults_only, not customized)")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaLockedOverridesAdminWhenIsCustomizedTrueAndStale
// verifies that when the spec omits LockRule, manageability is Locked, and
// IsCustomized=1 with a non-empty live value, drift is still reported.
// Locked manageability means the spec is authoritative even when isCustomized=1.
func TestVerifyPackObjectPhysicalSchemaLockedOverridesAdminWhenIsCustomizedTrueAndStale(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// live = non-empty stale; IsCustomized=1; Locked manageability
	setupVerifyPhysicalSchemaMockWithCustomized(t, mock, "IsArchived", 1)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		// LockRule omitted — spec no longer authors it; live has stale value
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want DRIFT (Locked + stale isCustomized=1)")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

// TestVerifyPackObjectPhysicalSchemaDetectsLockRuleDriftWhenObjectCustomizedButLockRuleNot
// is the fail-closed matrix test required by the gate-merge bot (R6 HIGH finding):
// manageability=defaults_only, _System_Object.IsCustomized=1 (unrelated admin edit,
// e.g. label change), LockRuleCustomized=0 (admin never touched LockRule), live
// LockRule differs from spec → verify must STILL report drift.
//
// Without LockRuleCustomized: ResolveLockRuleValueOwnership(defaults_only, isCustomized=1)
// returns LiveOverrideAllowed, silently swallowing the pack-authored LockRule contract.
// With LockRuleCustomized: ownership is column-scoped; IsCustomized=1 alone does NOT
// hand off LockRule to the admin.
func TestVerifyPackObjectPhysicalSchemaDetectsLockRuleDriftWhenObjectCustomizedButLockRuleNot(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	// IsCustomized=1 (unrelated admin edit), LockRuleCustomized=0 (admin never
	// customized LockRule), live value differs from spec.
	setupVerifyPhysicalSchemaMockFull(t, mock, "IsArchived", 1, 0)

	def := domainschema.TableDefinition{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		LockRule:  "Status == 'Open'", // spec — pack still owns LockRule
	}

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{def}, ManageabilityDefaultsOnly)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want LockRule drift (IsCustomized=1 but LockRuleCustomized=0 must not suppress check)")
	}
	if !strings.Contains(err.Error(), "LockRule") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want mention of LockRule", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}