package bootstrap

import (
	"context"
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"regexp"
	"strings"
	"testing"

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

// TestUpsertFieldMappingGroups_ConvergentUpdateSQL verifies the
// ON DUPLICATE KEY UPDATE now writes mutable columns from VALUES() rather
// than being a true no-op. Regression guard for round-3 reviewer
// finding #3: the previous semantics meant DefaultValue / TargetField
// edits in a seed never reached upgraded orgs on reapply.
func TestUpsertFieldMappingGroups_ConvergentUpdateSQL(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherFunc(rejectOwnerMutationMatcher)))
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// The INSERT SQL must reference VALUES(TargetField) etc. inside
	// IF(IsCustomized=1, ...) — that's the convergent contract. Source is
	// deliberately absent from the duplicate-update list because owner is
	// part of row identity, not mutable row payload.
	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping.*ON DUPLICATE KEY UPDATE.*" +
		"TargetField\\s*=\\s*IF\\(IsCustomized=1,\\s*TargetField,\\s*VALUES\\(TargetField\\)\\).*" +
		"DefaultValue\\s*=\\s*IF\\(IsCustomized=1,\\s*DefaultValue,\\s*VALUES\\(DefaultValue\\)\\).*" +
		"SourceLookupField\\s*=\\s*IF\\(IsCustomized=1,\\s*SourceLookupField,\\s*VALUES\\(SourceLookupField\\)\\).*" +
		"IsDeleted\\s*=\\s*IF\\(IsCustomized=1,\\s*IsDeleted,\\s*0\\)").
		WillReturnResult(sqlmock.NewResult(0, 1))
	// Reconcile pass for the single tuple in the input.
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1").
		WillReturnResult(sqlmock.NewResult(0, 0))

	groups := []FieldMappingGroup{
		{
			SourceObject: "Opportunity",
			TargetObject: "Quote",
			ActionType:   "insertInheritFromLookup",
			Mappings: []FieldMappingDef{
				{SourceField: "AccountId", TargetField: "AccountId", SourceLookupField: "OpportunityId"},
			},
		},
	}
	if err := UpsertFieldMappingGroups(context.Background(), db, groups); err != nil {
		t.Fatalf("UpsertFieldMappingGroups: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

func rejectOwnerMutationMatcher(expectedSQL, actualSQL string) error {
	if regexp.MustCompile(`(?is)\bSource\s*=\s*VALUES\s*\(\s*Source\s*\)`).MatchString(actualSQL) {
		return fmt.Errorf("object field mapping upsert must not mutate Source owner: %s", actualSQL)
	}
	return sqlmock.QueryMatcherRegexp.Match(expectedSQL, actualSQL)
}

func TestObjectFieldMappingUniqueKeyIncludesOwner(t *testing.T) {
	var tables []struct {
		TableName string `json:"tableName"`
		Columns   []struct {
			Name    string `json:"name"`
			Default string `json:"default"`
		} `json:"columns"`
		Indices []struct {
			Name    string   `json:"name"`
			Columns []string `json:"columns"`
			Unique  bool     `json:"unique"`
		} `json:"indices"`
	}
	if err := json.Unmarshal(systemTablesJSON, &tables); err != nil {
		t.Fatalf("parse system_tables.json: %v", err)
	}

	for _, table := range tables {
		if table.TableName != tableObjectFieldMapping {
			continue
		}
		foundSource := false
		for _, col := range table.Columns {
			if col.Name != "Source" {
				continue
			}
			foundSource = true
			if col.Name == "Source" && col.Default != "'legacy'" {
				t.Fatalf("ObjectFieldMapping.Source default = %q, want 'legacy'", col.Default)
			}
		}
		if !foundSource {
			t.Fatalf("ObjectFieldMapping.Source column missing")
		}
		for _, idx := range table.Indices {
			if idx.Name == "uq_ofm_mapping" {
				want := []string{"Source", "SourceObject", "TargetObject", "ActionType", "SourceField"}
				if !idx.Unique {
					t.Fatalf("uq_ofm_mapping must be unique")
				}
				if strings.Join(idx.Columns, ",") != strings.Join(want, ",") {
					t.Fatalf("uq_ofm_mapping columns = %v, want %v", idx.Columns, want)
				}
				return
			}
		}
		t.Fatalf("uq_ofm_mapping missing from %s", tableObjectFieldMapping)
	}
	t.Fatalf("%s missing from system_tables.json", tableObjectFieldMapping)
}

func TestFieldMappingIDIncludesOwner(t *testing.T) {
	group := FieldMappingGroup{
		SourceObject: "Lead",
		TargetObject: "Contact",
		ActionType:   "convert",
	}
	mapping := FieldMappingDef{SourceField: "Email", TargetField: "Email"}

	standardID := fieldMappingID("standard", group, mapping)
	tenantID := fieldMappingID("tenant:pingcap", group, mapping)
	if standardID == tenantID {
		t.Fatalf("fieldMappingID must include owner; both owners produced %q", standardID)
	}
	if !strings.Contains(standardID, "standard") || !strings.Contains(tenantID, "tenant:pingcap") {
		t.Fatalf("fieldMappingID should make owner visible: standard=%q tenant=%q", standardID, tenantID)
	}
}

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

	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET Source = \\?, LastModifiedDate = \\?\\s+WHERE Source = \\?\\s+AND IsCustomized = 0\\s+AND \\(\\(SourceObject = \\? AND TargetObject = \\? AND ActionType = \\? AND SourceField IN \\(\\?\\)\\)\\)").
		WithArgs("tenant:test", sqlmock.AnyArg(), legacyObjectFieldMappingOwner, "Lead", "Contact", "convert", "Department__c").
		WillReturnResult(sqlmock.NewResult(0, 1))
	expectStaleTenantFieldMappingDeactivate(mock, "tenant:test", FieldMappingGroup{SourceObject: "Lead", TargetObject: "Contact", ActionType: "convert"})
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+").
		WithArgs(sqlmock.AnyArg(), "Lead", "Contact", "convert", "tenant:test", "Department__c").
		WillReturnResult(sqlmock.NewResult(0, 0))

	groups := []FieldMappingGroup{{
		SourceObject: "Lead",
		TargetObject: "Contact",
		ActionType:   "convert",
		Mappings: []FieldMappingDef{
			{SourceField: "Department__c", TargetField: "Department"},
		},
	}}
	if err := UpsertFieldMappingGroupsForOwner(context.Background(), db, groups, "tenant:test"); err != nil {
		t.Fatalf("UpsertFieldMappingGroupsForOwner: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	// The claim includes explicit retired source fields. That is load-bearing
	// for upgraded orgs: when the current seed removes a mapping, the old
	// non-customized legacy row must move to the owner before reconcile can
	// soft-delete it, while unrelated legacy rows remain unclaimed.
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET Source = \\?, LastModifiedDate = \\?\\s+"+
		"WHERE Source = \\?\\s+"+
		"AND IsCustomized = 0\\s+"+
		"AND \\(\\(SourceObject = \\? AND TargetObject = \\? AND ActionType = \\? AND SourceField IN \\(\\?,\\?,\\?\\)\\)\\)").
		WithArgs(
			"tenant:test", sqlmock.AnyArg(), legacyObjectFieldMappingOwner,
			"Opportunity", "Quote", "insertInheritFromLookup",
			"AccountId", "Product_Family__c", "Overall_Start_Date__c",
		).
		WillReturnResult(sqlmock.NewResult(0, 2))
	expectStaleTenantFieldMappingDeactivate(mock, "tenant:test", FieldMappingGroup{SourceObject: "Opportunity", TargetObject: "Quote", ActionType: "insertInheritFromLookup"})
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+"+
		"AND IsCustomized = 0 AND IsDeleted = 0\\s+"+
		"AND SourceField NOT IN \\(\\?\\)").
		WithArgs(sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "tenant:test", "AccountId").
		WillReturnResult(sqlmock.NewResult(0, 2))

	groups := []FieldMappingGroup{{
		SourceObject:        "Opportunity",
		TargetObject:        "Quote",
		ActionType:          "insertInheritFromLookup",
		RetiredSourceFields: []string{"Product_Family__c", "Overall_Start_Date__c"},
		Mappings: []FieldMappingDef{
			{SourceField: "AccountId", TargetField: "AccountId", SourceLookupField: "OpportunityId"},
		},
	}}
	if err := UpsertFieldMappingGroupsForOwner(context.Background(), db, groups, "tenant:test"); err != nil {
		t.Fatalf("UpsertFieldMappingGroupsForOwner: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	group := FieldMappingGroup{
		SourceObject: "Opportunity",
		TargetObject: "Quote",
		ActionType:   "insertInheritFromLookup",
		Mappings: []FieldMappingDef{
			{SourceField: "AccountId", TargetField: "AccountId", SourceLookupField: "OpportunityId"},
		},
	}
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET Source = \\?, LastModifiedDate = \\?\\s+WHERE Source = \\?\\s+AND IsCustomized = 0").
		WithArgs("tenant:new", sqlmock.AnyArg(), legacyObjectFieldMappingOwner, "Opportunity", "Quote", "insertInheritFromLookup", "AccountId").
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectStaleTenantFieldMappingDeactivate(mock, "tenant:new", group)
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+").
		WithArgs(sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "tenant:new", "AccountId").
		WillReturnResult(sqlmock.NewResult(0, 0))

	if err := UpsertFieldMappingGroupsForOwner(context.Background(), db, []FieldMappingGroup{group}, "tenant:new"); err != nil {
		t.Fatalf("UpsertFieldMappingGroupsForOwner: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

func TestFieldMappingBusinessKeyClauses_IncludesDefaultRowsAndRetiredFields(t *testing.T) {
	clauses, args := fieldMappingBusinessKeyClauses([]FieldMappingGroup{{
		SourceObject:        "Opportunity",
		TargetObject:        "Quote",
		ActionType:          "insertInheritFromLookup",
		RetiredSourceFields: []string{"Product_Family__c"},
		Mappings: []FieldMappingDef{
			{SourceField: "AccountId", TargetField: "AccountId"},
			{SourceField: "", TargetField: "Status", DefaultValue: "Pending"},
		},
	}})

	if len(clauses) != 1 {
		t.Fatalf("clauses = %v, want 1", clauses)
	}
	if !strings.Contains(clauses[0], "SourceField IN (?,?,?)") {
		t.Fatalf("claim clause = %q, want current fields plus retired tombstone", clauses[0])
	}
	wantArgs := []interface{}{
		"Opportunity", "Quote", "insertInheritFromLookup",
		"AccountId", "", "Product_Family__c",
	}
	if fmt.Sprint(args) != fmt.Sprint(wantArgs) {
		t.Fatalf("args = %#v, want %#v", args, wantArgs)
	}
}

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

	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping.*"+
		"ON DUPLICATE KEY UPDATE.*"+
		"IsCustomized\\s*=\\s*IF\\(IsCustomized=1,\\s*IsCustomized,\\s*VALUES\\(IsCustomized\\)\\)").
		WithArgs(
			sqlmock.AnyArg(),
			"Lead",
			"Contact",
			"convert",
			"TenantSource",
			"TenantTarget",
			nil,
			nil,
			0,
			1,
			"standard",
			sqlmock.AnyArg(),
			sqlmock.AnyArg(),
		).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1").
		WillReturnResult(sqlmock.NewResult(0, 0))

	groups := []FieldMappingGroup{{
		SourceObject: "Lead",
		TargetObject: "Contact",
		ActionType:   "convert",
		IsCustomized: true,
		Mappings: []FieldMappingDef{
			{SourceField: "TenantSource", TargetField: "TenantTarget"},
		},
	}}
	if err := UpsertFieldMappingGroups(context.Background(), db, groups); err != nil {
		t.Fatalf("UpsertFieldMappingGroups: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

// TestUpsertFieldMappingGroups_ReconcileScopedByTuple asserts the
// soft-delete pass is scoped to (SourceObject, TargetObject, ActionType)
// and owner Source.
// Critical for multi-tenant safety: a tenant-overlay call with
// a partial Lead→Contact(convert) tuple must not soft-delete standard
// Lead Convert rows owned by Source='standard'.
func TestUpsertFieldMappingGroups_ReconcileScopedByTuple(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.MatchExpectationsInOrder(false)
	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping").
		WillReturnResult(sqlmock.NewResult(0, 2))

	// Two tuples present in the input → two reconcile queries, each
	// scoped to its own tuple and owner. Positional args proven via
	// WithArgs.
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_ObjectFieldMapping")).
		WithArgs(sqlmock.AnyArg(), "Lead", "Account", "convert", "standard", "Company").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_ObjectFieldMapping")).
		WithArgs(sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "standard", "AccountId").
		WillReturnResult(sqlmock.NewResult(0, 0))

	groups := []FieldMappingGroup{
		{
			SourceObject: "Lead",
			TargetObject: "Account",
			ActionType:   "convert",
			Mappings: []FieldMappingDef{
				{SourceField: "Company", TargetField: "Name"},
			},
		},
		{
			SourceObject: "Opportunity",
			TargetObject: "Quote",
			ActionType:   "insertInheritFromLookup",
			Mappings: []FieldMappingDef{
				{SourceField: "AccountId", TargetField: "AccountId", SourceLookupField: "OpportunityId"},
			},
		},
	}
	if err := UpsertFieldMappingGroups(context.Background(), db, groups); err != nil {
		t.Fatalf("UpsertFieldMappingGroups: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

// TestUpsertFieldMappingGroups_TenantOwnerDoesNotDeleteStandardLeadContactMappings
// covers the PR blocker directly: seed the standard Lead Convert mappings,
// then apply a partial tenant-owned Lead→Contact(convert) overlay. The tenant
// reconcile must be scoped to Source='tenant:test', so the 26 standard
// Lead→Contact rows owned by Source='standard' cannot match the delete query.
func TestUpsertFieldMappingGroups_TenantOwnerDoesNotDeleteStandardLeadContactMappings(t *testing.T) {
	var standardGroups []FieldMappingGroup
	if err := json.Unmarshal(standardFieldMappingsJSON, &standardGroups); err != nil {
		t.Fatalf("parse standard field mappings: %v", err)
	}

	var standardLeadContact *FieldMappingGroup
	for i := range standardGroups {
		g := &standardGroups[i]
		if g.SourceObject == "Lead" && g.TargetObject == "Contact" && g.ActionType == "convert" {
			standardLeadContact = g
			break
		}
	}
	if standardLeadContact == nil {
		t.Fatal("standard Lead→Contact convert mappings missing")
	}
	if got := len(standardLeadContact.Mappings); got != 26 {
		t.Fatalf("standard Lead→Contact convert mappings = %d, want 26", got)
	}

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(false)

	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping.*ON DUPLICATE KEY UPDATE.*TargetField\\s*=\\s*IF").
		WillReturnResult(sqlmock.NewResult(0, 44))
	for _, group := range standardGroups {
		expectFieldMappingReconcile(t, mock, group, "standard")
	}

	expectLegacyFieldMappingClaim(mock, "tenant:test")
	expectStaleTenantFieldMappingDeactivate(mock, "tenant:test", FieldMappingGroup{SourceObject: "Lead", TargetObject: "Contact", ActionType: "convert"})
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping.*ON DUPLICATE KEY UPDATE.*TargetField\\s*=\\s*IF").
		WillReturnResult(sqlmock.NewResult(0, 1))
	tenantOverlay := []FieldMappingGroup{{
		SourceObject: "Lead",
		TargetObject: "Contact",
		ActionType:   "convert",
		Mappings: []FieldMappingDef{
			{SourceField: "Department__c", TargetField: "Department"},
		},
	}}
	expectFieldMappingReconcile(t, mock, tenantOverlay[0], "tenant:test")

	if err := UpsertFieldMappingGroups(context.Background(), db, standardGroups); err != nil {
		t.Fatalf("seed standard mappings: %v", err)
	}
	if err := UpsertFieldMappingGroupsForOwner(context.Background(), db, tenantOverlay, "tenant:test"); err != nil {
		t.Fatalf("apply tenant overlay: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

func expectLegacyFieldMappingClaim(mock sqlmock.Sqlmock, owner string) {
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET Source = \\?, LastModifiedDate = \\?\\s+WHERE Source = \\?\\s+AND IsCustomized = 0").
		WillReturnResult(sqlmock.NewResult(0, 0))
}

func expectStaleTenantFieldMappingDeactivate(mock sqlmock.Sqlmock, owner string, groups ...FieldMappingGroup) {
	args := []driver.Value{sqlmock.AnyArg(), owner}
	for _, group := range groups {
		args = append(args, group.SourceObject, group.TargetObject, group.ActionType)
	}
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1, LastModifiedDate = \\?\\s+" +
		"WHERE Source LIKE 'tenant:%'\\s+" +
		"AND Source <> \\?\\s+" +
		"AND IsCustomized = 0\\s+" +
		"AND IsDeleted = 0\\s+" +
		"AND \\(").
		WithArgs(args...).
		WillReturnResult(sqlmock.NewResult(0, 0))
}

func expectFieldMappingReconcile(t *testing.T, mock sqlmock.Sqlmock, group FieldMappingGroup, owner string) {
	t.Helper()
	if len(group.Mappings) == 0 {
		t.Fatalf("test helper expects non-empty mappings for %s→%s(%s)", group.SourceObject, group.TargetObject, group.ActionType)
	}

	placeholderPattern := strings.TrimRight(strings.Repeat("\\?,", len(group.Mappings)), ",")
	pattern := "UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+" +
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+" +
		"AND Source = \\?\\s+" +
		"AND IsCustomized = 0 AND IsDeleted = 0\\s+" +
		"AND SourceField NOT IN \\(" + placeholderPattern + "\\)"

	args := []driver.Value{
		sqlmock.AnyArg(),
		group.SourceObject,
		group.TargetObject,
		group.ActionType,
		owner,
	}
	for _, mapping := range group.Mappings {
		args = append(args, mapping.SourceField)
	}

	mock.ExpectExec(pattern).
		WithArgs(args...).
		WillReturnResult(sqlmock.NewResult(0, 0))
}

// TestUpsertFieldMappingGroups_EmptyMappingsClearsTuple asserts that a
// group with zero Mappings still triggers the tuple's reconcile pass
// without issuing the INSERT — expressing "this tuple should no longer
// have ANY rows" through seed authoring. The reconcile UPDATE uses the
// empty-keep-ids branch (no NOT IN clause).
func TestUpsertFieldMappingGroups_EmptyMappingsClearsTuple(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(true)

	// No INSERT expected (valueParts is empty) — first claim legacy rows
	// in the tuple so owner-scoped reconcile can clear upgraded orgs, then
	// run the reconcile.
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET Source = \\?, LastModifiedDate = \\?\\s+"+
		"WHERE Source = \\?\\s+"+
		"AND IsCustomized = 0\\s+"+
		"AND \\(\\(SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\)\\)").
		WithArgs("standard", sqlmock.AnyArg(), legacyObjectFieldMappingOwner, "Opportunity", "Quote", "insertInheritFromLookup").
		WillReturnResult(sqlmock.NewResult(0, 2))
	// The UPDATE signature has no "SourceField NOT IN" clause when the keep-set is
	// empty; this matcher confirms the branch.
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+"+
		"AND IsCustomized = 0 AND IsDeleted = 0").
		WithArgs(sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "standard").
		WillReturnResult(sqlmock.NewResult(0, 3))

	groups := []FieldMappingGroup{
		{
			SourceObject: "Opportunity",
			TargetObject: "Quote",
			ActionType:   "insertInheritFromLookup",
			Mappings:     nil,
		},
	}
	if err := UpsertFieldMappingGroups(context.Background(), db, groups); err != nil {
		t.Fatalf("UpsertFieldMappingGroups on empty mappings: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

// TestUpsertFieldMappingGroups_NoGroupsIsNoOp guards against a
// regression where empty input would still issue SQL and fail on
// missing tuples. Pure no-op — no DB calls expected.
func TestUpsertFieldMappingGroups_NoGroupsIsNoOp(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	if err := UpsertFieldMappingGroups(context.Background(), db, nil); err != nil {
		t.Fatalf("UpsertFieldMappingGroups with nil groups: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations (expected none): %v", err)
	}
}

// TestUpsertFieldMappingGroups_ReapplyConverges simulates an upgraded
// org: first apply seeds state A, then a later seed edit ships state B
// (one row updated, one row added, one row removed). The second apply
// must leave DB in state B. This is the reapply-convergence contract
// that round-3 reviewer finding #3 flagged as missing.
//
// The sqlmock can't persist state between Exec calls, so we prove
// convergence by asserting the second-apply SQL shape contains the
// kept SourceFields in the NOT IN reconcile clause, and the reconcile
// UPDATE is scoped to the seed's tuple. That is the
// set of SQL-level observables that must be true on the upgrade
// path; a real database consuming this SQL would land in state B.
func TestUpsertFieldMappingGroups_ReapplyConverges(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(true)

	// --- First apply: seed state A = [AccountId, Description] ---
	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping").
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE _System_ObjectFieldMapping")).
		WillReturnResult(sqlmock.NewResult(0, 0))

	stateA := []FieldMappingGroup{{
		SourceObject: "Opportunity",
		TargetObject: "Quote",
		ActionType:   "insertInheritFromLookup",
		Mappings: []FieldMappingDef{
			{SourceField: "AccountId", TargetField: "AccountId", SourceLookupField: "OpportunityId"},
			{SourceField: "Description", TargetField: "Description", SourceLookupField: "OpportunityId"},
		},
	}}
	if err := UpsertFieldMappingGroups(context.Background(), db, stateA); err != nil {
		t.Fatalf("first apply (state A): %v", err)
	}

	// --- Second apply: seed state B ---
	//   - AccountId: kept
	//   - Description: REMOVED
	//   - CurrencyIsoCode: NEW
	// The INSERT batch has two rows (AccountId, CurrencyIsoCode). The
	// reconcile UPDATE's NOT IN clause carries those two SourceFields; the
	// previous Description row is NOT in the keep-list and is
	// soft-deleted. Non-deterministic map iteration is irrelevant here
	// because there's only one tuple.
	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping.*ON DUPLICATE KEY UPDATE.*" +
		"TargetField\\s*=\\s*IF\\(IsCustomized=1,\\s*TargetField,\\s*VALUES\\(TargetField\\)\\)").
		WillReturnResult(sqlmock.NewResult(0, 2))
	// Reconcile query pattern — tuple scoped and keyed by SourceField,
	// not synthetic Id. This is the upgraded-org fix: when only
	// TargetField changes, the row must stay alive under the same
	// business key.
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+"+
		"AND IsCustomized = 0 AND IsDeleted = 0\\s+"+
		"AND SourceField NOT IN \\(\\?,\\?\\)").
		WithArgs(
			sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "standard",
			// The two kept SourceFields. Order is
			// insertion order — which matches the seed order per
			// UpsertFieldMappingGroups's iteration.
			"AccountId",
			"CurrencyIsoCode",
		).
		WillReturnResult(sqlmock.NewResult(0, 1)) // simulates Description row getting soft-deleted

	stateB := []FieldMappingGroup{{
		SourceObject: "Opportunity",
		TargetObject: "Quote",
		ActionType:   "insertInheritFromLookup",
		Mappings: []FieldMappingDef{
			{SourceField: "AccountId", TargetField: "AccountId", SourceLookupField: "OpportunityId"},
			{SourceField: "CurrencyIsoCode", TargetField: "CurrencyIsoCode", SourceLookupField: "OpportunityId"},
		},
	}}
	if err := UpsertFieldMappingGroups(context.Background(), db, stateB); err != nil {
		t.Fatalf("second apply (state B): %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

// TestUpsertFieldMappingGroups_TargetFieldEditKeepsBusinessKeyAlive guards the
// specific upgraded-org bug that broke PR #1853's ci-test tenant apply: a seed
// edit changes only TargetField for an existing business key. Upsert lands the
// new value via uq_ofm_mapping, so reconcile must keep by SourceField rather
// than by the synthetic Id (which still reflects the old TargetField).
func TestUpsertFieldMappingGroups_TargetFieldEditKeepsBusinessKeyAlive(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(true)

	oldSeed := []FieldMappingGroup{{
		SourceObject: "Opportunity",
		TargetObject: "Quote",
		ActionType:   "insertInheritFromLookup",
		Mappings: []FieldMappingDef{
			{SourceField: "NetFees__c", TargetField: "Net_Fees__c", SourceLookupField: "OpportunityId"},
		},
	}}
	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+"+
		"AND IsCustomized = 0 AND IsDeleted = 0\\s+"+
		"AND SourceField NOT IN \\(\\?\\)").
		WithArgs(sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "standard", "NetFees__c").
		WillReturnResult(sqlmock.NewResult(0, 0))
	if err := UpsertFieldMappingGroups(context.Background(), db, oldSeed); err != nil {
		t.Fatalf("first apply (old seed): %v", err)
	}

	newSeed := []FieldMappingGroup{{
		SourceObject: "Opportunity",
		TargetObject: "Quote",
		ActionType:   "insertInheritFromLookup",
		Mappings: []FieldMappingDef{
			{SourceField: "NetFees__c", TargetField: "NetFees__c", SourceLookupField: "OpportunityId"},
		},
	}}
	expectLegacyFieldMappingClaim(mock, "standard")
	mock.ExpectExec("INSERT INTO _System_ObjectFieldMapping.*ON DUPLICATE KEY UPDATE.*" +
		"TargetField\\s*=\\s*IF\\(IsCustomized=1,\\s*TargetField,\\s*VALUES\\(TargetField\\)\\)").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ObjectFieldMapping\\s+SET IsDeleted = 1,\\s+LastModifiedDate = \\?\\s+"+
		"WHERE SourceObject = \\? AND TargetObject = \\? AND ActionType = \\?\\s+"+
		"AND Source = \\?\\s+"+
		"AND IsCustomized = 0 AND IsDeleted = 0\\s+"+
		"AND SourceField NOT IN \\(\\?\\)").
		WithArgs(sqlmock.AnyArg(), "Opportunity", "Quote", "insertInheritFromLookup", "standard", "NetFees__c").
		WillReturnResult(sqlmock.NewResult(0, 0))
	if err := UpsertFieldMappingGroups(context.Background(), db, newSeed); err != nil {
		t.Fatalf("second apply (new seed): %v", err)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT Source, SourceField, TargetField, DefaultValue, SourceLookupField\s+FROM _System_ObjectFieldMapping.*ORDER BY CASE`).
		WithArgs("Lead", "Contact", "convert").
		WillReturnRows(sqlmock.NewRows([]string{"Source", "SourceField", "TargetField", "DefaultValue", "SourceLookupField"}).
			AddRow("tenant:pingcap", "Email", "TenantEmail", nil, nil).
			AddRow("standard", "Email", "Email", nil, nil).
			AddRow("standard", "Phone", "Phone", nil, nil))

	mappings, err := GetFieldMappings(db, "Lead", "Contact", "convert")
	if err != nil {
		t.Fatalf("GetFieldMappings: %v", err)
	}
	if len(mappings) != 2 {
		t.Fatalf("mappings len = %d, want 2: %#v", len(mappings), mappings)
	}
	if mappings[0].SourceField != "Email" || mappings[0].TargetField != "TenantEmail" {
		t.Fatalf("tenant row should win duplicate Email mapping, got %#v", mappings[0])
	}
	if mappings[1].SourceField != "Phone" || mappings[1].TargetField != "Phone" {
		t.Fatalf("standard-only Phone mapping should survive, got %#v", mappings[1])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT Source, SourceField, TargetField, DefaultValue, SourceLookupField\s+FROM _System_ObjectFieldMapping.*WHEN IsCustomized = 1 THEN 0.*WHEN Source = 'standard' THEN 2`).
		WithArgs("Lead", "Contact", "convert").
		WillReturnRows(sqlmock.NewRows([]string{"Source", "SourceField", "TargetField", "DefaultValue", "SourceLookupField"}).
			AddRow("legacy", "Email", "AdminEmail", nil, nil).
			AddRow("standard", "Email", "Email", nil, nil).
			AddRow("standard", "Phone", "Phone", nil, nil))

	mappings, err := GetFieldMappings(db, "Lead", "Contact", "convert")
	if err != nil {
		t.Fatalf("GetFieldMappings: %v", err)
	}
	if len(mappings) != 2 {
		t.Fatalf("mappings len = %d, want 2: %#v", len(mappings), mappings)
	}
	if mappings[0].SourceField != "Email" || mappings[0].TargetField != "AdminEmail" {
		t.Fatalf("customized legacy Email mapping should win over standard, got %#v", mappings[0])
	}
	if mappings[1].SourceField != "Phone" || mappings[1].TargetField != "Phone" {
		t.Fatalf("standard-only Phone mapping should survive, got %#v", mappings[1])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT Source, SourceObject, SourceField, TargetField, DefaultValue, SourceLookupField\s+FROM _System_ObjectFieldMapping.*ORDER BY CASE`).
		WithArgs("Quote", "insertInheritFromLookup").
		WillReturnRows(sqlmock.NewRows([]string{"Source", "SourceObject", "SourceField", "TargetField", "DefaultValue", "SourceLookupField"}).
			AddRow("tenant:pingcap", "Opportunity", "Amount", "TenantAmount", nil, "OpportunityId").
			AddRow("standard", "Opportunity", "Amount", "Amount", nil, "OpportunityId").
			AddRow("standard", "Opportunity", "Description", "Description", nil, "OpportunityId"))

	mappings, err := GetFieldMappingsByTarget(db, "Quote", "insertInheritFromLookup")
	if err != nil {
		t.Fatalf("GetFieldMappingsByTarget: %v", err)
	}
	if len(mappings) != 2 {
		t.Fatalf("mappings len = %d, want 2: %#v", len(mappings), mappings)
	}
	if mappings[0].SourceObject != "Opportunity" || mappings[0].SourceField != "Amount" || mappings[0].TargetField != "TenantAmount" {
		t.Fatalf("tenant row should win duplicate Opportunity.Amount mapping, got %#v", mappings[0])
	}
	if mappings[1].SourceObject != "Opportunity" || mappings[1].SourceField != "Description" {
		t.Fatalf("standard-only Opportunity.Description mapping should survive, got %#v", mappings[1])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT Source, SourceObject, SourceField, TargetField, DefaultValue, SourceLookupField\s+FROM _System_ObjectFieldMapping.*WHEN IsCustomized = 1 THEN 0.*WHEN Source = 'standard' THEN 2`).
		WithArgs("Quote", "insertInheritFromLookup").
		WillReturnRows(sqlmock.NewRows([]string{"Source", "SourceObject", "SourceField", "TargetField", "DefaultValue", "SourceLookupField"}).
			AddRow("legacy", "Opportunity", "Amount", "AdminAmount", nil, "OpportunityId").
			AddRow("standard", "Opportunity", "Amount", "Amount", nil, "OpportunityId").
			AddRow("standard", "Opportunity", "Description", "Description", nil, "OpportunityId"))

	mappings, err := GetFieldMappingsByTarget(db, "Quote", "insertInheritFromLookup")
	if err != nil {
		t.Fatalf("GetFieldMappingsByTarget: %v", err)
	}
	if len(mappings) != 2 {
		t.Fatalf("mappings len = %d, want 2: %#v", len(mappings), mappings)
	}
	if mappings[0].SourceObject != "Opportunity" || mappings[0].SourceField != "Amount" || mappings[0].TargetField != "AdminAmount" {
		t.Fatalf("customized legacy Opportunity.Amount mapping should win over standard, got %#v", mappings[0])
	}
	if mappings[1].SourceObject != "Opportunity" || mappings[1].SourceField != "Description" {
		t.Fatalf("standard-only Opportunity.Description mapping should survive, got %#v", mappings[1])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}