package packs

import (
	"database/sql"
	"errors"
	"fmt"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/pingcap-inc/nexuscrm-controlplane/specdrift"
)

type packPhysicalIndexTestRow struct {
	tableName string
	indexName string
	nonUnique int
	columns   string
}

func expectPackPhysicalTableSet(mock sqlmock.Sqlmock, tableNames ...string) {
	rows := sqlmock.NewRows([]string{"TABLE_NAME"})
	for _, tableName := range tableNames {
		rows.AddRow(tableName)
	}
	mock.ExpectQuery(regexp.QuoteMeta("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()")).
		WillReturnRows(rows)
}

func expectPackPhysicalIndexSet(mock sqlmock.Sqlmock, indexRows ...packPhysicalIndexTestRow) {
	rows := sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)"})
	for _, row := range indexRows {
		rows.AddRow(row.tableName, row.indexName, row.nonUnique, row.columns)
	}
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT").
		WillReturnRows(rows)
}

func expectObjectSpecUniqueFieldRegistryRows(mock sqlmock.Sqlmock, rows *sqlmock.Rows) {
	mock.ExpectQuery("SELECT o.`ApiName`, o.`TableType`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), COALESCE\\(f.`IsUnique`, 0\\) FROM `_System_Field`").
		WillReturnRows(rows)
}

func expectObjectSpecRequiredFieldRegistryRows(mock sqlmock.Sqlmock, rows *sqlmock.Rows) {
	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`Required`, 0\\) FROM `_System_Field`").
		WillReturnRows(rows)
}

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

	expectPackPhysicalTableSet(mock, "Lead")
	expectPackPhysicalIndexSet(mock)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Lead` ADD INDEX `idx_Lead_HubSpot_Id__c` (`HubSpot_Id__c`)")).
		WillReturnError(errors.New("invalid connection"))
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Lead",
		indexName: "idx_Lead_HubSpot_Id__c",
		nonUnique: 1,
		columns:   "HubSpot_Id__c",
	})

	defs := []domainschema.TableDefinition{{
		TableName: "Lead",
		Indices: []domainschema.IndexDefinition{{
			Columns: []string{"HubSpot_Id__c"},
		}},
	}}
	if err := reconcilePackTableIndices(db, defs); err != nil {
		t.Fatalf("reconcilePackTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Lead")
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Lead",
		indexName: "idx_Lead_HubSpot_Id__c",
		nonUnique: 1,
		columns:   "Legacy_HubSpot_Id__c",
	})
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Lead` ADD INDEX `idx_Lead_HubSpot_Id__c` (`HubSpot_Id__c`)")).
		WillReturnError(errors.New("Error 1061: Duplicate key name 'idx_Lead_HubSpot_Id__c'"))
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Lead",
		indexName: "idx_Lead_HubSpot_Id__c",
		nonUnique: 1,
		columns:   "Legacy_HubSpot_Id__c",
	})

	defs := []domainschema.TableDefinition{{
		TableName: "Lead",
		Indices: []domainschema.IndexDefinition{{
			Columns: []string{"HubSpot_Id__c"},
		}},
	}}
	err = reconcilePackTableIndices(db, defs)
	if err == nil {
		t.Fatal("expected same-name/different-column index collision to fail closed")
	}
	if got, want := err.Error(), "refusing to replace without ownership proof"; !strings.Contains(got, want) {
		t.Fatalf("expected collision error containing %q, got %q", want, got)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	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, nil, "", "", "", 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", "ExternalRemoteId__c", "pack", "crm", 0, nil))

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want missing unique-index drift")
	}
	if !strings.Contains(err.Error(), "physical unique index on Widget(ExternalRemoteId__c) is missing") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want missing unique-index drift", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT CAST(`ExternalRemoteId__c` AS CHAR) AS duplicate_value, COUNT(*) FROM `Widget` WHERE `ExternalRemoteId__c` IS NOT NULL GROUP BY `ExternalRemoteId__c` HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5")).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}).
			AddRow("legacy-duplicate", 2))

	err = preflightObjectSpecUniqueLegacyDuplicates(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}})
	if err == nil {
		t.Fatal("preflightObjectSpecUniqueLegacyDuplicates = nil, want duplicate legacy value error")
	}
	if !strings.Contains(err.Error(), `duplicate legacy values`) ||
		!strings.Contains(err.Error(), `"legacy-duplicate" x2`) ||
		!strings.Contains(err.Error(), "ApplyOptions.RepairLegacyUniqueDuplicates") {
		t.Fatalf("preflightObjectSpecUniqueLegacyDuplicates error = %v, want duplicate sample and repair option", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "Id", "varchar(36)", "NO", sql.NullString{}, sql.NullString{}).
			AddRow("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	expectObjectSpecRequiredFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"ApiName", "ApiName", "Required"}).
			AddRow("Widget", "ExternalRemoteId__c", 0))
	duplicateQuery := regexp.QuoteMeta("SELECT CAST(`ExternalRemoteId__c` AS CHAR) AS duplicate_value, COUNT(*) FROM `Widget` WHERE `ExternalRemoteId__c` IS NOT NULL GROUP BY `ExternalRemoteId__c` HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5")
	mock.ExpectQuery(duplicateQuery).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}).
			AddRow("legacy-duplicate", 2))
	mock.ExpectExec("(?s)UPDATE `Widget` AS target.*ROW_NUMBER\\(\\) OVER \\(PARTITION BY `ExternalRemoteId__c` ORDER BY `Id`\\).*SET target\\.`ExternalRemoteId__c` = NULL").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectQuery(duplicateQuery).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}))

	err = preflightOrRepairObjectSpecUniqueLegacyDuplicates(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, true, newObjectSpecRequiredFieldIndex([]ObjectSpec{{
		APIName: "Widget",
		Fields: []FieldSpec{{
			APIName: "ExternalRemoteId__c",
		}},
	}}))
	if err != nil {
		t.Fatalf("preflightOrRepairObjectSpecUniqueLegacyDuplicates repair = %v, want nil", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "Id", "varchar(36)", "NO", sql.NullString{}, sql.NullString{}).
			AddRow("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	expectObjectSpecRequiredFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"ApiName", "ApiName", "Required"}).
			AddRow("Widget", "ExternalRemoteId__c", 0))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT CAST(`ExternalRemoteId__c` AS CHAR) AS duplicate_value, COUNT(*) FROM `Widget` WHERE `ExternalRemoteId__c` IS NOT NULL GROUP BY `ExternalRemoteId__c` HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5")).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}).
			AddRow("legacy-duplicate", 2))

	err = preflightOrRepairObjectSpecUniqueLegacyDuplicates(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, true, newObjectSpecRequiredFieldIndex([]ObjectSpec{{
		APIName: "Widget",
		Fields: []FieldSpec{{
			APIName:  "ExternalRemoteId__c",
			Required: true,
		}},
	}}))
	if err == nil {
		t.Fatal("preflightOrRepairObjectSpecUniqueLegacyDuplicates repair = nil, want required-field failure")
	}
	if !strings.Contains(err.Error(), "logically required") ||
		!strings.Contains(err.Error(), "repair duplicate values explicitly") {
		t.Fatalf("preflightOrRepairObjectSpecUniqueLegacyDuplicates error = %v, want required explicit-repair message", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "Id", "varchar(36)", "NO", sql.NullString{}, sql.NullString{}).
			AddRow("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	expectObjectSpecRequiredFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"ApiName", "ApiName", "Required"}).
			AddRow("Widget", "ExternalRemoteId__c", 1))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT CAST(`ExternalRemoteId__c` AS CHAR) AS duplicate_value, COUNT(*) FROM `Widget` WHERE `ExternalRemoteId__c` IS NOT NULL GROUP BY `ExternalRemoteId__c` HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5")).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}).
			AddRow("legacy-duplicate", 2))

	err = preflightOrRepairObjectSpecUniqueLegacyDuplicates(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, true, newObjectSpecRequiredFieldIndex([]ObjectSpec{{
		APIName: "Widget",
		Fields: []FieldSpec{{
			APIName: "ExternalRemoteId__c",
		}},
	}}))
	if err == nil {
		t.Fatal("preflightOrRepairObjectSpecUniqueLegacyDuplicates repair = nil, want live required-field failure")
	}
	if !strings.Contains(err.Error(), "logically required") {
		t.Fatalf("preflightOrRepairObjectSpecUniqueLegacyDuplicates error = %v, want live required message", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "Id", "varchar(36)", "NO", sql.NullString{}, sql.NullString{}).
			AddRow("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	expectObjectSpecRequiredFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"ApiName", "ApiName", "Required"}).
			AddRow("Widget", "ExternalRemoteId__c", 0))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT CAST(`ExternalRemoteId__c` AS CHAR) AS duplicate_value, COUNT(*) FROM `Widget` WHERE `ExternalRemoteId__c` IS NOT NULL GROUP BY `ExternalRemoteId__c` HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5")).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}).
			AddRow("legacy-duplicate", 2))

	err = preflightOrRepairObjectSpecUniqueLegacyDuplicates(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, true)
	if err == nil {
		t.Fatal("preflightOrRepairObjectSpecUniqueLegacyDuplicates repair = nil, want unknown-required failure")
	}
	if !strings.Contains(err.Error(), "logical required metadata is unavailable") {
		t.Fatalf("preflightOrRepairObjectSpecUniqueLegacyDuplicates error = %v, want unknown required metadata message", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "Id", "varchar(36)", "NO", sql.NullString{}, sql.NullString{}).
			AddRow("Widget", "ExternalRemoteId__c", "varchar(255)", "NO", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	expectObjectSpecRequiredFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"ApiName", "ApiName", "Required"}).
			AddRow("Widget", "ExternalRemoteId__c", 0))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT CAST(`ExternalRemoteId__c` AS CHAR) AS duplicate_value, COUNT(*) FROM `Widget` WHERE `ExternalRemoteId__c` IS NOT NULL GROUP BY `ExternalRemoteId__c` HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5")).
		WillReturnRows(sqlmock.NewRows([]string{"duplicate_value", "COUNT(*)"}).
			AddRow("legacy-duplicate", 2))

	err = preflightOrRepairObjectSpecUniqueLegacyDuplicates(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, true, newObjectSpecRequiredFieldIndex([]ObjectSpec{{
		APIName: "Widget",
		Fields: []FieldSpec{{
			APIName: "ExternalRemoteId__c",
		}},
	}}))
	if err == nil {
		t.Fatal("preflightOrRepairObjectSpecUniqueLegacyDuplicates repair = nil, want non-nullable failure")
	}
	if !strings.Contains(err.Error(), "not nullable") {
		t.Fatalf("preflightOrRepairObjectSpecUniqueLegacyDuplicates error = %v, want non-nullable message", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Widget",
		indexName: "uniq_Widget_ExternalRemoteId__c",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	})
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `Widget` DROP INDEX `uniq_Widget_ExternalRemoteId__c`")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := dropOwnedObjectSpecUniqueDowngrades(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
	}}); err != nil {
		t.Fatalf("dropOwnedObjectSpecUniqueDowngrades: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestObjectSpecUniquePhysicalConstraintDefinitionsHonorDefaultsOnlyCustomizedLiveMetadata(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}
	fieldRegistry := objectSpecUniqueFieldRegistry{
		newObjectSpecUniqueFieldKey("standard_object", "Widget", "ExternalRemoteId__c"): {
			fieldAPIName: "ExternalRemoteId__c",
			ownerKind:    "pack",
			ownerPack:    "crm",
			isCustomized: true,
			isUnique:     false,
		},
	}

	effective := objectSpecUniquePhysicalConstraintDefinitions(defs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})
	if effective[0].Columns[0].Unique {
		t.Fatal("defaults_only customized live IsUnique=false should disable physical unique enforcement")
	}
	if !defs[0].Columns[0].Unique {
		t.Fatal("resolver mutated original table definitions")
	}

	locked := objectSpecUniquePhysicalConstraintDefinitions(defs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityLocked, row)
	})
	if !locked[0].Columns[0].Unique {
		t.Fatal("locked metadata must keep the pack unique declaration even when live row is customized")
	}

	nonUniqueDefs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
	}}
	fieldRegistry[newObjectSpecUniqueFieldKey("standard_object", "Widget", "ExternalRemoteId__c")] = packFieldRegistryRow{
		fieldAPIName: "ExternalRemoteId__c",
		ownerKind:    "pack",
		ownerPack:    "crm",
		isCustomized: true,
		isUnique:     true,
	}

	effective = objectSpecUniquePhysicalConstraintDefinitions(nonUniqueDefs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})
	if !effective[0].Columns[0].Unique {
		t.Fatal("defaults_only customized live IsUnique=true should enable physical unique enforcement")
	}
	if nonUniqueDefs[0].Columns[0].Unique {
		t.Fatal("resolver mutated original non-unique table definitions")
	}

	locked = objectSpecUniquePhysicalConstraintDefinitions(nonUniqueDefs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityLocked, row)
	})
	if locked[0].Columns[0].Unique {
		t.Fatal("locked metadata must keep the pack non-unique declaration even when live row is customized")
	}
}

func TestObjectSpecUniquePhysicalConstraintDefinitionsScopeLiveMetadataByTableType(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}, {
		TableName: "Widget",
		TableType: "custom_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}
	fieldRegistry := objectSpecUniqueFieldRegistry{
		newObjectSpecUniqueFieldKey("custom_object", "Widget", "ExternalRemoteId__c"): {
			fieldAPIName: "ExternalRemoteId__c",
			ownerKind:    "pack",
			ownerPack:    "crm",
			isCustomized: true,
			isUnique:     false,
		},
	}

	effective := objectSpecUniquePhysicalConstraintDefinitions(defs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})
	if !effective[0].Columns[0].Unique {
		t.Fatal("custom object live unique metadata must not override the colliding standard object")
	}
	if effective[1].Columns[0].Unique {
		t.Fatal("custom object live unique metadata should override the matching custom object only")
	}
}

func TestObjectSpecUniquePhysicalConstraintDefinitionsScopeLiveMetadataToAuthoredFields(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "OwnerId",
			Type:     "VARCHAR(36)",
			Nullable: true,
			Unique:   false,
		}, {
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_owner",
			Columns: []string{"OwnerId"},
			Unique:  false,
		}, {
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  false,
		}},
	}}
	fieldRegistry := objectSpecUniqueFieldRegistry{
		newObjectSpecUniqueFieldKey("standard_object", "Widget", "OwnerId"): {
			fieldAPIName: "OwnerId",
			ownerKind:    "pack",
			ownerPack:    "crm",
			isCustomized: true,
			isUnique:     true,
		},
		newObjectSpecUniqueFieldKey("standard_object", "Widget", "ExternalRemoteId__c"): {
			fieldAPIName: "ExternalRemoteId__c",
			ownerKind:    "pack",
			ownerPack:    "crm",
			isCustomized: true,
			isUnique:     true,
		},
	}
	authoredFields := newObjectSpecAuthoredFieldSet([]ObjectSpec{{
		APIName:   "Widget",
		TableType: "standard_object",
		Fields: []FieldSpec{{
			APIName: "ExternalRemoteId__c",
		}},
	}})

	effective := objectSpecUniquePhysicalConstraintDefinitionsForAuthoredFields(defs, fieldRegistry, authoredFields, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})
	if effective[0].Columns[0].Unique {
		t.Fatal("injected system field OwnerId must not enter object_spec live unique enforcement")
	}
	if effective[0].Indices[0].Unique {
		t.Fatal("injected system field index must not follow live unique metadata")
	}
	if !effective[0].Columns[1].Unique {
		t.Fatal("authored object_spec field should still follow live unique metadata")
	}
	if !effective[0].Indices[1].Unique {
		t.Fatal("authored field same-column index should follow live unique metadata")
	}
}

func TestObjectSpecUniquePhysicalConstraintDefinitionsNormalizeSingleColumnIndexesForLiveMetadata(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  false,
		}, {
			Name:    "idx_widget_remote_other",
			Columns: []string{"ExternalRemoteId__c", "Other__c"},
			Unique:  false,
		}},
	}}
	fieldRegistry := objectSpecUniqueFieldRegistry{
		newObjectSpecUniqueFieldKey("standard_object", "Widget", "ExternalRemoteId__c"): {
			fieldAPIName: "ExternalRemoteId__c",
			ownerKind:    "pack",
			ownerPack:    "crm",
			isCustomized: true,
			isUnique:     true,
		},
	}

	effective := objectSpecUniquePhysicalConstraintDefinitions(defs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})
	if !effective[0].Columns[0].Unique {
		t.Fatal("live IsUnique=true should enable the effective column unique bit")
	}
	if !effective[0].Indices[0].Unique {
		t.Fatal("same-column explicit index should follow the effective field unique bit")
	}
	if effective[0].Indices[1].Unique {
		t.Fatal("multi-column explicit index should remain a separate physical fact")
	}
	if defs[0].Indices[0].Unique {
		t.Fatal("resolver mutated original index definitions")
	}

	downgradeDefs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  true,
		}},
	}}
	fieldRegistry[newObjectSpecUniqueFieldKey("standard_object", "Widget", "ExternalRemoteId__c")] = packFieldRegistryRow{
		fieldAPIName: "ExternalRemoteId__c",
		ownerKind:    "pack",
		ownerPack:    "crm",
		isCustomized: true,
		isUnique:     false,
	}

	effective = objectSpecUniquePhysicalConstraintDefinitions(downgradeDefs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})
	if effective[0].Columns[0].Unique {
		t.Fatal("live IsUnique=false should disable the effective column unique bit")
	}
	if effective[0].Indices[0].Unique {
		t.Fatal("same-column explicit index should follow the effective live non-unique bit")
	}
	if !downgradeDefs[0].Indices[0].Unique {
		t.Fatal("resolver mutated original downgrade index definitions")
	}
}

func TestObjectSpecUniquePhysicalConstraintDefinitionsNormalizeAuthoredUniqueSingleColumnIndexes(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  false,
		}, {
			Name:    "idx_widget_name",
			Columns: []string{"Name"},
			Unique:  false,
		}},
	}}

	effective := objectSpecUniquePhysicalConstraintDefinitions(defs, nil, nil)
	if !effective[0].Indices[0].Unique {
		t.Fatal("authored unique field should normalize same-column explicit index to unique")
	}
	if effective[0].Indices[1].Unique {
		t.Fatal("unrelated explicit index should not be normalized")
	}
	if defs[0].Indices[0].Unique {
		t.Fatal("resolver mutated original authored index definitions")
	}

	applyDefs, err := objectSpecUniquePhysicalConstraintDefinitionsForApply(nil, "crm", defs, false)
	if err != nil {
		t.Fatalf("objectSpecUniquePhysicalConstraintDefinitionsForApply: %v", err)
	}
	if !applyDefs[0].Indices[0].Unique {
		t.Fatal("apply without preserveCustomized should still normalize authored unique indexes")
	}

	verifyDefs, err := objectSpecUniquePhysicalConstraintDefinitionsForVerify(nil, "crm", defs, ManageabilityLocked)
	if err != nil {
		t.Fatalf("objectSpecUniquePhysicalConstraintDefinitionsForVerify: %v", err)
	}
	if !verifyDefs[0].Indices[0].Unique {
		t.Fatal("locked verify should still normalize authored unique indexes")
	}
}

func TestReconcilePackTableIndicesAcceptsNormalizedLiveUniqueOverride(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  false,
		}},
	}}
	fieldRegistry := objectSpecUniqueFieldRegistry{
		newObjectSpecUniqueFieldKey("standard_object", "Widget", "ExternalRemoteId__c"): {
			fieldAPIName: "ExternalRemoteId__c",
			ownerKind:    "pack",
			ownerPack:    "crm",
			isCustomized: true,
			isUnique:     true,
		},
	}
	effective := objectSpecUniquePhysicalConstraintDefinitions(defs, fieldRegistry, func(row packFieldRegistryRow) bool {
		return fieldMetadataExactRequired(ManageabilityDefaultsOnly, row)
	})

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()
	expectPackPhysicalTableSet(mock, "Widget")
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Widget",
		indexName: "uniq_Widget_ExternalRemoteId__c",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	})

	if err := reconcilePackTableIndices(db, effective); err != nil {
		t.Fatalf("reconcilePackTableIndices = %v, want nil after live unique normalization", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestReconcilePackTableIndicesAcceptsNormalizedAuthoredUniqueFieldIndex(t *testing.T) {
	defs := []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  false,
		}},
	}}
	effective := objectSpecUniquePhysicalConstraintDefinitions(defs, nil, nil)

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()
	expectPackPhysicalTableSet(mock, "Widget")
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Widget",
		indexName: "uniq_Widget_ExternalRemoteId__c",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	})

	if err := reconcilePackTableIndices(db, effective); err != nil {
		t.Fatalf("reconcilePackTableIndices = %v, want nil after authored unique normalization", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock)
	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, nil, "", "", "", 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", "ExternalRemoteId__c", "pack", "crm", 1, nil))
	expectObjectSpecUniqueFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"APIName", "TableType", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "IsUnique"}).
			AddRow("Widget", "standard_object", "ExternalRemoteId__c", "pack", "crm", 1, 0))

	if err := verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, ManageabilityDefaultsOnly); err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil for customized live unique override", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Widget",
		indexName: "uniq_Widget_ExternalRemoteId__c",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	})
	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", "ChildCloneRulesBusinessKey", "ChildCloneRulesManageability", "ChildCloneRulesPackValue", "IsCustomized", "DefaultChildRowsCustomized", "ChildCloneRulesCustomized", "LockRuleCustomized", "IsDeleted", "LockRule", "DisplayNameField"}).
			AddRow("Widget", "standard_object", nil, nil, nil, nil, "", "", "", 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", "ExternalRemoteId__c", "pack", "crm", 1, nil))
	expectObjectSpecUniqueFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"APIName", "TableType", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "IsUnique"}).
			AddRow("Widget", "standard_object", "ExternalRemoteId__c", "pack", "crm", 1, 0))

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   true,
		}},
	}}, ManageabilityDefaultsOnly)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want stale physical unique-index drift")
	}
	if !strings.Contains(err.Error(), "remains for non-unique field Widget.ExternalRemoteId__c") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want stale unique-index drift", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRequiresDefaultsOnlyCustomizedLiveUniqueOverridePhysicalIndex(t *testing.T) {
	run := func(t *testing.T, indexRows []packPhysicalIndexTestRow, wantErrSubstring string) {
		t.Helper()
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock.New: %v", err)
		}
		defer db.Close()

		expectPackPhysicalTableSet(mock, "Widget")
		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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
		expectPackPhysicalIndexSet(mock, indexRows...)
		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, nil, "", "", "", 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", "ExternalRemoteId__c", "pack", "crm", 1, nil))
		expectObjectSpecUniqueFieldRegistryRows(mock,
			sqlmock.NewRows([]string{"APIName", "TableType", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "IsUnique"}).
				AddRow("Widget", "standard_object", "ExternalRemoteId__c", "pack", "crm", 1, 1))

		err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{{
			TableName: "Widget",
			TableType: "standard_object",
			Category:  "crm",
			Columns: []domainschema.ColumnDefinition{{
				Name:     "ExternalRemoteId__c",
				Type:     "VARCHAR(255)",
				Nullable: true,
				Unique:   false,
			}},
		}}, ManageabilityDefaultsOnly)
		if wantErrSubstring == "" {
			if err != nil {
				t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil", err)
			}
		} else {
			if err == nil {
				t.Fatalf("verifyPackObjectPhysicalSchema = nil, want %q", wantErrSubstring)
			}
			if !strings.Contains(err.Error(), wantErrSubstring) {
				t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want %q", err, wantErrSubstring)
			}
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sqlmock expectations: %v", err)
		}
	}

	run(t, nil, "physical unique index on Widget(ExternalRemoteId__c) is missing for unique field Widget.ExternalRemoteId__c")
	run(t, []packPhysicalIndexTestRow{{
		tableName: "Widget",
		indexName: "uniq_Widget_ExternalRemoteId__c",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	}}, "")
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Widget",
		indexName: "uniq_Widget_ExternalRemoteId__c",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	})
	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, nil, "", "", "", 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", "ExternalRemoteId__c", "pack", "crm", 1, nil))
	expectObjectSpecUniqueFieldRegistryRows(mock,
		sqlmock.NewRows([]string{"APIName", "TableType", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "IsUnique"}).
			AddRow("Widget", "standard_object", "ExternalRemoteId__c", "pack", "crm", 1, 1))

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
		Indices: []domainschema.IndexDefinition{{
			Name:    "idx_widget_remote",
			Columns: []string{"ExternalRemoteId__c"},
			Unique:  false,
		}},
	}}, ManageabilityDefaultsOnly)
	if err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchema = %v, want nil after explicit index normalization", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

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

	expectPackPhysicalTableSet(mock, "Widget")
	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("Widget", "ExternalRemoteId__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}))
	expectPackPhysicalIndexSet(mock, packPhysicalIndexTestRow{
		tableName: "Widget",
		indexName: "external_unique",
		nonUnique: 0,
		columns:   "ExternalRemoteId__c",
	})
	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, nil, "", "", "", 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", "ExternalRemoteId__c", "pack", "crm", 0, nil))

	err = verifyPackObjectPhysicalSchema(db, "crm", []domainschema.TableDefinition{{
		TableName: "Widget",
		TableType: "standard_object",
		Category:  "crm",
		Columns: []domainschema.ColumnDefinition{{
			Name:     "ExternalRemoteId__c",
			Type:     "VARCHAR(255)",
			Nullable: true,
			Unique:   false,
		}},
	}}, ManageabilityLocked)
	if err == nil {
		t.Fatal("verifyPackObjectPhysicalSchema = nil, want stale unique-index drift")
	}
	if !strings.Contains(err.Error(), "remains for non-unique field Widget.ExternalRemoteId__c") {
		t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want stale unique-index drift", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestIsPackObjectPhysicalColumnMissingDrift(t *testing.T) {
	t.Run("accepts pure column-missing drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
		}}
		if !IsPackObjectPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected pure column-missing drift to be detected")
		}
	})

	t.Run("accepts multiple column-missing bullets", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
			`crm physical column Issue.Status is missing`,
		}}
		if !IsPackObjectPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected multiple column-missing bullets to be detected")
		}
	})

	t.Run("rejects mixed bullets (column-missing + keyprefix)", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
		}}
		if IsPackObjectPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected mixed drift to return false (not pure column-missing)")
		}
	})

	t.Run("rejects objectSpecVerifyError aggregate (diagnostic only, no aggregate)", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm physical column Issue.Subject is missing`,
			}},
		}}
		if IsPackObjectPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected aggregate error to return false (diagnostic only)")
		}
	})

	t.Run("rejects nil", func(t *testing.T) {
		if IsPackObjectPhysicalColumnMissingDrift(nil) {
			t.Fatalf("expected nil to return false")
		}
	})
}

func TestIsPackObjectAnyPhysicalColumnMissingDrift(t *testing.T) {
	t.Run("accepts pure column-missing drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
		}}
		if !IsPackObjectAnyPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected column-missing drift to be detected")
		}
	})

	t.Run("accepts objectSpecVerifyError aggregate with column-missing child", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
				`crm physical column Issue.Subject is missing`,
			}},
		}}
		if !IsPackObjectAnyPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected mixed aggregate with column-missing to be detected")
		}
	})

	t.Run("rejects pure keyprefix drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
		}}
		if IsPackObjectAnyPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected pure keyprefix drift to not be detected as column-missing")
		}
	})

	t.Run("rejects MissingLiveFieldRowsError", func(t *testing.T) {
		err := &specdrift.MissingLiveFieldRowsError{SpecName: "issue", Count: 2}
		if IsPackObjectAnyPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected MissingLiveFieldRowsError to not be detected as column-missing")
		}
	})

	t.Run("rejects objectSpecVerifyError aggregate with no column-missing", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
			&specdrift.MissingLiveFieldRowsError{SpecName: "issue", Count: 1},
		}}
		if IsPackObjectAnyPhysicalColumnMissingDrift(err) {
			t.Fatalf("expected aggregate without column-missing to return false")
		}
	})

	t.Run("rejects nil", func(t *testing.T) {
		if IsPackObjectAnyPhysicalColumnMissingDrift(nil) {
			t.Fatalf("expected nil to return false")
		}
	})
}

func TestIsPackObjectBackgroundReconcileRepairableDrift(t *testing.T) {
	t.Run("accepts pure keyprefix drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
		}}
		if !IsPackObjectBackgroundReconcileRepairableDrift(err) {
			t.Fatalf("expected pure keyprefix drift to be repairable")
		}
	})

	t.Run("accepts MissingLiveFieldRowsError", func(t *testing.T) {
		err := &specdrift.MissingLiveFieldRowsError{SpecName: "issue", Count: 2}
		if !IsPackObjectBackgroundReconcileRepairableDrift(err) {
			t.Fatalf("expected MissingLiveFieldRowsError to be repairable")
		}
	})

	t.Run("accepts objectSpecVerifyError aggregate with keyprefix-only children", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
			&specdrift.MissingLiveFieldRowsError{SpecName: "issue", Count: 1},
		}}
		if !IsPackObjectBackgroundReconcileRepairableDrift(err) {
			t.Fatalf("expected keyprefix+MissingLiveFieldRows aggregate to be repairable")
		}
	})

	t.Run("rejects column-missing drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
		}}
		if IsPackObjectBackgroundReconcileRepairableDrift(err) {
			t.Fatalf("expected column-missing drift to be rejected (fail-closed)")
		}
	})

	t.Run("rejects mixed keyprefix+column-missing drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			`crm physical column Issue.Subject is missing`,
		}}
		if IsPackObjectBackgroundReconcileRepairableDrift(err) {
			t.Fatalf("expected mixed drift with column-missing to be rejected")
		}
	})

	t.Run("rejects objectSpecVerifyError aggregate containing column-missing", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm physical column Issue.Subject is missing`,
			}},
		}}
		if IsPackObjectBackgroundReconcileRepairableDrift(err) {
			t.Fatalf("expected aggregate with column-missing child to be rejected")
		}
	})

	t.Run("rejects generic error", func(t *testing.T) {
		if IsPackObjectBackgroundReconcileRepairableDrift(fmt.Errorf("some error")) {
			t.Fatalf("expected generic error to be rejected")
		}
	})

	t.Run("rejects nil", func(t *testing.T) {
		if IsPackObjectBackgroundReconcileRepairableDrift(nil) {
			t.Fatalf("expected nil to return false")
		}
	})
}

func TestIsPackObjectReconcileVerifyRepairableDrift(t *testing.T) {
	t.Run("accepts pure keyprefix physical drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
		}}
		if !IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("expected keyprefix drift to be repairable by background reconcile path")
		}
	})

	t.Run("accepts MissingLiveFieldRowsError", func(t *testing.T) {
		err := &specdrift.MissingLiveFieldRowsError{SpecName: "crm", Count: 3}
		if !IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("expected MissingLiveFieldRowsError to be repairable by background reconcile path")
		}
	})

	t.Run("rejects pack-owned _System_Field row loss", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Field row for Account.CustomField__c is missing`,
		}}
		if IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("expected pack-owned _System_Field row loss to be non-repairable in background reconcile path")
		}
	})

	t.Run("rejects missing physical column", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Account.custom_field__c is missing`,
		}}
		if IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("expected missing physical column to be non-repairable in background reconcile path")
		}
	})

	t.Run("accepts aggregate with keyprefix-only and MissingLiveFieldRowsError children", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
			&specdrift.MissingLiveFieldRowsError{SpecName: "crm", Count: 1},
		}}
		if !IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("expected aggregate of keyprefix+MissingLiveFieldRows to be repairable")
		}
	})

	t.Run("rejects aggregate where any child has field row missing drift", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Field row for Account.CustomField__c is missing`,
			}},
		}}
		if IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("expected aggregate with field row missing to be non-repairable in background reconcile path")
		}
	})

	t.Run("rejects nil error", func(t *testing.T) {
		if IsPackObjectReconcileVerifyRepairableDrift(nil) {
			t.Fatal("expected nil to return false")
		}
	})

	t.Run("is strictly narrower than IsPackObjectInstalledPackRepairableDrift for field row missing", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Field row for Account.CustomField__c is missing`,
		}}
		// broad classifier accepts it; strict classifier rejects it
		if !IsPackObjectInstalledPackRepairableDrift(err) {
			t.Fatal("broad classifier must accept field row missing (test setup error)")
		}
		if IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("strict classifier must reject field row missing")
		}
	})

	t.Run("is strictly narrower than IsPackObjectInstalledPackRepairableDrift for missing physical column", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Account.custom_field__c is missing`,
		}}
		// broad classifier accepts it; strict classifier rejects it
		if !IsPackObjectInstalledPackRepairableDrift(err) {
			t.Fatal("broad classifier must accept missing physical column (test setup error)")
		}
		if IsPackObjectReconcileVerifyRepairableDrift(err) {
			t.Fatal("strict classifier must reject missing physical column")
		}
	})
}

func TestIsPackObjectInstalledPackRepairableDriftForUniqueFieldLoss(t *testing.T) {
	t.Run("accepts clean unique field row column and index loss", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm physical unique index on Issue(ExternalRemoteId__c) is missing for unique field Issue.ExternalRemoteId__c`,
				`crm physical column Issue.ExternalRemoteId__c is missing`,
				`crm _System_Field row for Issue.ExternalRemoteId__c is missing`,
			}},
		}}
		if !IsPackObjectInstalledPackRepairableDrift(err) {
			t.Fatal("expected clean unique field row+column+index loss to be installed-pack repairable")
		}
	})

	t.Run("rejects unique index loss without clean field loss", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical unique index on Issue(ExternalRemoteId__c) is missing for unique field Issue.ExternalRemoteId__c`,
		}}
		if IsPackObjectInstalledPackRepairableDrift(err) {
			t.Fatal("expected unique index loss alone to remain non-repairable")
		}
	})
}

func TestIsPackObjectKeyPrefixDrift(t *testing.T) {
	t.Run("accepts pure keyprefix physical drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			`crm _System_Object row for Contact KeyPrefix = NULL, want "003"`,
		}}
		if !IsPackObjectKeyPrefixDrift(err) {
			t.Fatalf("expected pure keyprefix drift to be repairable")
		}
	})

	t.Run("rejects mixed physical drift bullets", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			`crm _System_Table row for Account IsManaged = 0, want 1`,
		}}
		if IsPackObjectKeyPrefixDrift(err) {
			t.Fatalf("expected mixed physical drift to fail closed")
		}
	})

	t.Run("accepts object spec aggregate when every child is keyprefix-only", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
		}}
		if !IsPackObjectKeyPrefixDrift(err) {
			t.Fatalf("expected pure keyprefix aggregate to be repairable")
		}
	})

	t.Run("rejects object spec aggregate with non keyprefix sibling drift", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
			}},
			fmt.Errorf("spec projection drift"),
		}}
		if IsPackObjectKeyPrefixDrift(err) {
			t.Fatalf("expected aggregate drift to fail closed")
		}
	})
}

func TestIsPackObjectColumnMissingAloneWithoutFieldRowMissing(t *testing.T) {
	t.Run("returns true when column missing but field row present", func(t *testing.T) {
		// Structural corruption: physical column gone but _System_Field row still exists.
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
		}}
		if !IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected column-missing-alone to be detected (structural corruption)")
		}
	})

	t.Run("returns false when both column and field row are missing", func(t *testing.T) {
		// Clean field removal: both physical column and _System_Field row gone together.
		// This is repairable by ApplyAll.
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Field row for Epic.TotalPoints is missing`,
			`crm physical column Epic.TotalPoints is missing`,
		}}
		if IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected column+field-row missing together to NOT be structural corruption")
		}
	})

	t.Run("returns false when only field row is missing (no column drift)", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Field row for Issue.Subject is missing`,
		}}
		if IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected field-row-missing-only to not be detected as structural corruption")
		}
	})

	t.Run("returns false for keyprefix-only drift", func(t *testing.T) {
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm _System_Object row for Account KeyPrefix = NULL, want "001"`,
		}}
		if IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected keyprefix-only drift to not be detected as structural corruption")
		}
	})

	t.Run("returns true for objectSpecVerifyError aggregate with column-missing-alone child", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm physical column Issue.Subject is missing`,
			}},
		}}
		if !IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected aggregate with column-missing-alone child to be detected")
		}
	})

	t.Run("returns false for objectSpecVerifyError aggregate with paired column+field-row child", func(t *testing.T) {
		err := &objectSpecVerifyError{errs: []error{
			&packObjectPhysicalSchemaDriftError{drifts: []string{
				`crm _System_Field row for Epic.TotalPoints is missing`,
				`crm physical column Epic.TotalPoints is missing`,
			}},
		}}
		if IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected aggregate with paired column+field-row to not be structural corruption")
		}
	})

	t.Run("returns true for mixed error: one field corrupt, one fully removed", func(t *testing.T) {
		// Field1 (Issue.Subject): column missing but field row present (structural corruption).
		// Field2 (Issue.Priority): both column and field row missing (clean full removal).
		// The function must detect Field1's corruption even though Field2 provides a
		// paired field-row-missing entry. Per-field key correlation is required.
		err := &packObjectPhysicalSchemaDriftError{drifts: []string{
			`crm physical column Issue.Subject is missing`,
			`crm _System_Field row for Issue.Priority is missing`,
			`crm physical column Issue.Priority is missing`,
		}}
		if !IsPackObjectColumnMissingAloneWithoutFieldRowMissing(err) {
			t.Fatalf("expected mixed error with one corrupted field to be detected as structural corruption")
		}
	})

	t.Run("returns false for nil", func(t *testing.T) {
		if IsPackObjectColumnMissingAloneWithoutFieldRowMissing(nil) {
			t.Fatalf("expected nil to return false")
		}
	})
}