package tenantoverlay

import (
	"context"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"testing"

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

func TestAssertTenantInsertRulesSurfacesLoadableAcceptsObjectChildCloneRulesFile(t *testing.T) {
	dir := t.TempDir()
	if err := os.WriteFile(filepath.Join(dir, ObjectChildCloneRulesFile), []byte(`{}`), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectChildCloneRulesFile, err)
	}

	if err := New(dir).assertTenantInsertRulesSurfacesLoadable(); err != nil {
		t.Fatalf("assertTenantInsertRulesSurfacesLoadable = %v, want nil", err)
	}
}

func TestValidateTenantDefaultChildRowsSpecsRejectsStandardObjectOverlay(t *testing.T) {
	err := validateTenantDefaultChildRowsSpecs([]packs.ObjectSpec{{
		APIName:   "Campaign",
		TableType: "standard_object",
		DefaultChildRows: []models.DefaultChildRow{{
			TargetObject:      "CampaignMemberStatus",
			TargetLookupField: "CampaignId",
		}},
	}})
	if err == nil {
		t.Fatal("validateTenantDefaultChildRowsSpecs = nil, want standard-object overlay rejection")
	}
	if !strings.Contains(err.Error(), "unsupported") {
		t.Fatalf("validateTenantDefaultChildRowsSpecs error = %v, want unsupported detail", err)
	}
}

func TestValidateTenantChildCloneRulesSpecsAllowsCustomObjectAuthoring(t *testing.T) {
	err := validateTenantChildCloneRulesSpecs([]packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{{
			ParentLookupField: "Parent__c",
			SourceObject:      "Source__c",
			SourceLookupField: "Parent__c",
			TargetObject:      "Target__c",
			TargetLookupField: "Custom__c",
			FieldMappings:     map[string]string{"Name": "Name"},
		}},
	}})
	if err != nil {
		t.Fatalf("validateTenantChildCloneRulesSpecs custom object = %v, want nil", err)
	}
}

func TestValidateTenantChildCloneRulesSpecsRejectsStandardObjectAuthoring(t *testing.T) {
	err := validateTenantChildCloneRulesSpecs([]packs.ObjectSpec{{
		APIName:         "Account",
		TableType:       string(constants.TableTypeStandardObject),
		ChildCloneRules: []models.ChildCloneRule{},
	}})
	if err == nil {
		t.Fatal("validateTenantChildCloneRulesSpecs = nil, want standard-object rejection")
	}
	if !strings.Contains(err.Error(), ObjectChildCloneRulesFile) {
		t.Fatalf("validateTenantChildCloneRulesSpecs error = %v, want %s detail", err, ObjectChildCloneRulesFile)
	}
}

func TestApplyRejectsInvalidTenantDisplayNameFieldBeforePersistence(t *testing.T) {
	dir := t.TempDir()
	payload := `[
		{
			"apiName": "CustomDisplay__c",
			"tableType": "custom_object",
			"displayNameField": "Missing__c",
			"fields": [
				{"apiName": "Name", "type": "string"}
			]
		}
	]`
	if err := os.WriteFile(filepath.Join(dir, ObjectsSpecFile), []byte(payload), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectsSpecFile, err)
	}

	err := New(dir).Apply(context.Background(), packs.ApplyDeps{})
	if err == nil {
		t.Fatal("Apply = nil, want displayNameField validation error before persistence")
	}
	if !strings.Contains(err.Error(), "displayNameField validation failed") {
		t.Fatalf("Apply error = %v, want displayNameField validation detail", err)
	}
	if !strings.Contains(err.Error(), "CustomDisplay__c.displayNameField") {
		t.Fatalf("Apply error = %v, want tenant object path detail", err)
	}
}

func TestBuildTenantObjectMetadataLeavesChildCloneRulesToDedicatedWriter(t *testing.T) {
	objects := buildTenantObjectMetadata([]packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{{
			ParentLookupField: "Parent__c",
			SourceObject:      "Source__c",
			SourceLookupField: "Parent__c",
			TargetObject:      "Target__c",
			TargetLookupField: "Custom__c",
			FieldMappings:     map[string]string{"Name": "Name"},
		}},
	}})
	if len(objects) != 1 {
		t.Fatalf("buildTenantObjectMetadata objects = %d, want 1", len(objects))
	}
	if objects[0].ChildCloneRules != nil {
		t.Fatalf("buildTenantObjectMetadata ChildCloneRules = %#v, want nil for dedicated writer", objects[0].ChildCloneRules)
	}
	if objects[0].ChildCloneRulesCustomized {
		t.Fatal("buildTenantObjectMetadata ChildCloneRulesCustomized = true, want false for dedicated writer")
	}
}

func TestValidateObjectChildCloneRulesFilePayloadRejectsCustomObjectAuthoring(t *testing.T) {
	dir := t.TempDir()
	if err := os.WriteFile(filepath.Join(dir, ObjectChildCloneRulesFile), []byte(`{"Custom__c":[]}`), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectChildCloneRulesFile, err)
	}

	err := New(dir).validateObjectChildCloneRulesFilePayload(context.Background(), packs.ApplyDeps{}, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
	}}, nil)
	if err == nil {
		t.Fatal("validateObjectChildCloneRulesFilePayload = nil, want custom-object rejection")
	}
	if !strings.Contains(err.Error(), ObjectsSpecFile) {
		t.Fatalf("validateObjectChildCloneRulesFilePayload error = %v, want %s detail", err, ObjectsSpecFile)
	}
}

func TestValidateObjectChildCloneRulesFilePayloadRejectsInvalidStandardOverridePreflight(t *testing.T) {
	dir := t.TempDir()
	payload := `{
		"Account": [
			{
				"parentLookupField": "ParentId",
				"sourceObject": "Contact",
				"sourceLookupField": "AccountId",
				"targetObject": "Contact",
				"targetLookupField": "AccountId",
				"fieldMappings": {"Missing__c": "LastName"}
			}
		]
	}`
	if err := os.WriteFile(filepath.Join(dir, ObjectChildCloneRulesFile), []byte(payload), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectChildCloneRulesFile, err)
	}

	specs := []packs.ObjectSpec{
		{
			APIName:   "Account",
			TableType: string(constants.TableTypeStandardObject),
			Fields: []packs.FieldSpec{
				{APIName: "ParentId", Type: string(constants.FieldTypeReference), ReferenceTo: []string{"Account"}},
				{APIName: "Name", Type: string(constants.FieldTypeString)},
			},
		},
		{
			APIName:   "Contact",
			TableType: string(constants.TableTypeStandardObject),
			Fields: []packs.FieldSpec{
				{APIName: "AccountId", Type: string(constants.FieldTypeReference), ReferenceTo: []string{"Account"}},
				{APIName: "LastName", Type: string(constants.FieldTypeString)},
			},
		},
	}
	err := New(dir).validateObjectChildCloneRulesFilePayload(context.Background(), packs.ApplyDeps{}, specs, map[string]childCloneRulePackOwnership{
		"account": {
			ObjectAPIName: "Account",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
		},
	})
	if err == nil {
		t.Fatal("validateObjectChildCloneRulesFilePayload = nil, want preflight validation error")
	}
	if !strings.Contains(err.Error(), "Missing__c") {
		t.Fatalf("validateObjectChildCloneRulesFilePayload error = %v, want missing field detail", err)
	}
}

func TestValidateObjectChildCloneRulesFilePayloadRejectsLockedPackObjectOverride(t *testing.T) {
	dir := t.TempDir()
	if err := os.WriteFile(filepath.Join(dir, ObjectChildCloneRulesFile), []byte(`{"Account":[]}`), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectChildCloneRulesFile, err)
	}

	err := New(dir).validateObjectChildCloneRulesFilePayload(context.Background(), packs.ApplyDeps{}, nil, map[string]childCloneRulePackOwnership{
		"account": {
			ObjectAPIName: "Account",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityLocked,
		},
	})
	if err == nil {
		t.Fatal("validateObjectChildCloneRulesFilePayload = nil, want locked object rejection")
	}
	if !strings.Contains(err.Error(), "locked pack object Account") {
		t.Fatalf("validateObjectChildCloneRulesFilePayload error = %v, want locked object detail", err)
	}
}

func TestValidateObjectChildCloneRulesFilePayloadRejectsMissingPackOwnership(t *testing.T) {
	dir := t.TempDir()
	if err := os.WriteFile(filepath.Join(dir, ObjectChildCloneRulesFile), []byte(`{"Account":[]}`), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectChildCloneRulesFile, err)
	}

	err := New(dir).validateObjectChildCloneRulesFilePayload(context.Background(), packs.ApplyDeps{}, nil, nil)
	if err == nil {
		t.Fatal("validateObjectChildCloneRulesFilePayload = nil, want missing ownership rejection")
	}
	if !strings.Contains(err.Error(), "no persisted ChildCloneRules pack ownership") {
		t.Fatalf("validateObjectChildCloneRulesFilePayload error = %v, want missing ownership detail", err)
	}
}

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

	query := regexp.QuoteMeta("UPDATE `_System_Object` SET `ChildCloneRules` = ?, `ChildCloneRulesCustomized` = 1, `LastModifiedDate` = NOW() WHERE `Id` = ?")
	mock.ExpectExec(query).
		WithArgs("[]", domainschema.GenerateObjectID("Custom__c")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = applyTenantObjectSpecChildCloneRules(context.Background(), db, []packs.ObjectSpec{{
		APIName:         "Custom__c",
		TableType:       string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{},
	}})
	if err != nil {
		t.Fatalf("applyTenantObjectSpecChildCloneRules = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	err = applyTenantObjectSpecChildCloneRules(context.Background(), db, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
	}})
	if err != nil {
		t.Fatalf("applyTenantObjectSpecChildCloneRules = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRejectLockedTenantObjectSpecChildCloneRuleOverlaysRejectsLockedPackCustomObject(t *testing.T) {
	err := rejectLockedTenantObjectSpecChildCloneRuleOverlays([]packs.ObjectSpec{{
		APIName:         "Custom__c",
		TableType:       string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{},
	}}, map[string]childCloneRulePackOwnership{
		"custom__c": {
			ObjectAPIName: "Custom__c",
			PackName:      "custom-pack",
			BusinessKey:   "custom-objects",
			Manageability: packs.ManageabilityLocked,
		},
	})
	if err == nil {
		t.Fatal("rejectLockedTenantObjectSpecChildCloneRuleOverlays = nil, want locked custom object rejection")
	}
	if !strings.Contains(err.Error(), "locked pack-owned custom object Custom__c") {
		t.Fatalf("rejectLockedTenantObjectSpecChildCloneRuleOverlays error = %v, want locked custom object detail", err)
	}
}

func TestRejectLockedTenantObjectSpecChildCloneRuleOverlaysRejectsMissingPackOwnership(t *testing.T) {
	err := rejectLockedTenantObjectSpecChildCloneRuleOverlays([]packs.ObjectSpec{{
		APIName:         "Custom__c",
		TableType:       string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{},
	}}, map[string]childCloneRulePackOwnership{
		"custom__c": {
			ObjectAPIName:        "Custom__c",
			MissingPackOwnership: true,
		},
	})
	if err == nil {
		t.Fatal("rejectLockedTenantObjectSpecChildCloneRuleOverlays = nil, want missing ownership rejection")
	}
	if !strings.Contains(err.Error(), "no persisted ChildCloneRules pack ownership") {
		t.Fatalf("rejectLockedTenantObjectSpecChildCloneRuleOverlays error = %v, want missing ownership detail", err)
	}
}

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

	query := regexp.QuoteMeta("UPDATE `_System_Object` SET `ChildCloneRules` = ?, `ChildCloneRulesCustomized` = 1, `LastModifiedDate` = NOW() WHERE `Id` = ?")
	mock.ExpectExec(query).
		WithArgs(`[{"parentLookupField":"Parent__c","sourceObject":"Source__c","sourceLookupField":"Parent__c","targetObject":"Target__c","targetLookupField":"Custom__c","fieldMappings":{"Name":"Name"}}]`, domainschema.GenerateObjectID("Custom__c")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = applyTenantObjectSpecChildCloneRules(context.Background(), db, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{{
			ParentLookupField: "Parent__c",
			SourceObject:      "Source__c",
			SourceLookupField: "Parent__c",
			TargetObject:      "Target__c",
			TargetLookupField: "Custom__c",
			FieldMappings:     map[string]string{"Name": "Name"},
		}},
	}})
	if err != nil {
		t.Fatalf("applyTenantObjectSpecChildCloneRules = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	query := regexp.QuoteMeta("SELECT `ApiName`, COALESCE(`ChildCloneRulesPackName`, ''), COALESCE(`ChildCloneRulesPackBusinessKey`, ''), COALESCE(`ChildCloneRulesPackManageability`, ''), `ChildCloneRulesPackValue` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND COALESCE(`IsCustom`, 0) = 0 AND (COALESCE(`ChildCloneRulesCustomized`, 0) <> 0) ORDER BY `ApiName`")
	mock.ExpectQuery(query).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue"}))

	ownership, err := New(t.TempDir()).loadChildCloneRulePackOwnershipForApply(context.Background(), db)
	if err != nil {
		t.Fatalf("loadChildCloneRulePackOwnershipForApply = %v", err)
	}
	if ownership != nil {
		t.Fatalf("ownership = %#v, want nil when only tenant custom-object rows are customized", ownership)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestLoadChildCloneRulePackOwnershipForApplyReadsPersistedObjectRows(t *testing.T) {
	dir := t.TempDir()
	if err := os.WriteFile(filepath.Join(dir, ObjectChildCloneRulesFile), []byte(`{"Account":[]}`), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectChildCloneRulesFile, err)
	}

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

	query := regexp.QuoteMeta("SELECT `ApiName`, COALESCE(`ChildCloneRulesPackName`, ''), COALESCE(`ChildCloneRulesPackBusinessKey`, ''), COALESCE(`ChildCloneRulesPackManageability`, ''), `ChildCloneRulesPackValue` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND COALESCE(`IsCustom`, 0) = 0 AND (COALESCE(`ChildCloneRulesCustomized`, 0) <> 0 OR LOWER(`ApiName`) IN (?)) ORDER BY `ApiName`")
	mock.ExpectQuery(query).
		WithArgs("account").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue"}).
			AddRow("Account", "salesforce-crm-standard", "crm-objects", string(packs.ManageabilityLocked), `[{"parentLookupField":"ParentId","sourceObject":"Contact","sourceLookupField":"AccountId","targetObject":"Contact","targetLookupField":"AccountId","fieldMappings":{"LastName":"LastName"}}]`))

	ownership, err := New(dir).loadChildCloneRulePackOwnershipForApply(context.Background(), db)
	if err != nil {
		t.Fatalf("loadChildCloneRulePackOwnershipForApply = %v", err)
	}
	got, ok := ownership["account"]
	if !ok {
		t.Fatalf("ownership missing account: %#v", ownership)
	}
	if got.PackName != "salesforce-crm-standard" || got.BusinessKey != "crm-objects" || got.Manageability != packs.ManageabilityLocked {
		t.Fatalf("ownership = %#v, want persisted pack metadata", got)
	}
	if len(got.Rules) != 1 || got.Rules[0].SourceObject != "Contact" {
		t.Fatalf("ownership rules = %#v, want persisted ChildCloneRules", got.Rules)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	query := regexp.QuoteMeta("SELECT o.`ApiName`, COALESCE(o.`ChildCloneRulesPackName`, ''), COALESCE(o.`ChildCloneRulesPackBusinessKey`, ''), COALESCE(o.`ChildCloneRulesPackManageability`, ''), o.`ChildCloneRulesPackValue`, o.`ChildCloneRules`, COALESCE(o.`ChildCloneRulesCustomized`, 0), COALESCE((SELECT COUNT(*) FROM `_System_Field` f WHERE f.`ObjectId` = o.`Id` AND COALESCE(f.`IsDeleted`, 0) = 0 AND COALESCE(f.`OwnerKind`, '') IN ('pack', 'pack_removing') AND COALESCE(f.`OwnerPack`, '') <> ''), 0) FROM `_System_Object` o WHERE COALESCE(o.`IsDeleted`, 0) = 0 AND COALESCE(o.`IsCustom`, 0) <> 0 AND (COALESCE(o.`ChildCloneRulesCustomized`, 0) <> 0 OR LOWER(o.`ApiName`) IN (?)) ORDER BY o.`ApiName`")
	mock.ExpectQuery(query).
		WithArgs("custom__c").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "ChildCloneRules", "ChildCloneRulesCustomized", "PackOwnedFieldCount"}).
			AddRow("Custom__c", "custom-pack", "custom-objects", string(packs.ManageabilityLocked), `[]`, nil, 0, 0))

	ownership, err := loadCustomChildCloneRulePackOwnershipForApply(context.Background(), db, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
	}})
	if err != nil {
		t.Fatalf("loadCustomChildCloneRulePackOwnershipForApply = %v", err)
	}
	got, ok := ownership["custom__c"]
	if !ok {
		t.Fatalf("ownership missing custom__c: %#v", ownership)
	}
	if got.PackName != "custom-pack" || got.BusinessKey != "custom-objects" || got.Manageability != packs.ManageabilityLocked {
		t.Fatalf("ownership = %#v, want persisted custom pack metadata", got)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	liveRules := `[{"parentLookupField":"Parent__c","sourceObject":"Child__c","sourceLookupField":"Parent__c","targetObject":"Child__c","targetLookupField":"Parent__c","fieldMappings":{"Name":"Name"}}]`
	query := regexp.QuoteMeta("SELECT o.`ApiName`, COALESCE(o.`ChildCloneRulesPackName`, ''), COALESCE(o.`ChildCloneRulesPackBusinessKey`, ''), COALESCE(o.`ChildCloneRulesPackManageability`, ''), o.`ChildCloneRulesPackValue`, o.`ChildCloneRules`, COALESCE(o.`ChildCloneRulesCustomized`, 0), COALESCE((SELECT COUNT(*) FROM `_System_Field` f WHERE f.`ObjectId` = o.`Id` AND COALESCE(f.`IsDeleted`, 0) = 0 AND COALESCE(f.`OwnerKind`, '') IN ('pack', 'pack_removing') AND COALESCE(f.`OwnerPack`, '') <> ''), 0) FROM `_System_Object` o WHERE COALESCE(o.`IsDeleted`, 0) = 0 AND COALESCE(o.`IsCustom`, 0) <> 0 AND (COALESCE(o.`ChildCloneRulesCustomized`, 0) <> 0 OR LOWER(o.`ApiName`) IN (?)) ORDER BY o.`ApiName`")
	mock.ExpectQuery(query).
		WithArgs("custom__c").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "ChildCloneRules", "ChildCloneRulesCustomized", "PackOwnedFieldCount"}).
			AddRow("Custom__c", "", "", "", nil, liveRules, 0, 0))

	ownership, err := loadCustomChildCloneRulePackOwnershipForApply(context.Background(), db, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
	}})
	if err != nil {
		t.Fatalf("loadCustomChildCloneRulePackOwnershipForApply = %v", err)
	}
	got, ok := ownership["custom__c"]
	if !ok {
		t.Fatalf("ownership missing custom__c: %#v", ownership)
	}
	if !got.MissingPackOwnership {
		t.Fatalf("ownership = %#v, want missing pack ownership marker", got)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	query := regexp.QuoteMeta("SELECT o.`ApiName`, COALESCE(o.`ChildCloneRulesPackName`, ''), COALESCE(o.`ChildCloneRulesPackBusinessKey`, ''), COALESCE(o.`ChildCloneRulesPackManageability`, ''), o.`ChildCloneRulesPackValue`, o.`ChildCloneRules`, COALESCE(o.`ChildCloneRulesCustomized`, 0), COALESCE((SELECT COUNT(*) FROM `_System_Field` f WHERE f.`ObjectId` = o.`Id` AND COALESCE(f.`IsDeleted`, 0) = 0 AND COALESCE(f.`OwnerKind`, '') IN ('pack', 'pack_removing') AND COALESCE(f.`OwnerPack`, '') <> ''), 0) FROM `_System_Object` o WHERE COALESCE(o.`IsDeleted`, 0) = 0 AND COALESCE(o.`IsCustom`, 0) <> 0 AND (COALESCE(o.`ChildCloneRulesCustomized`, 0) <> 0 OR LOWER(o.`ApiName`) IN (?)) ORDER BY o.`ApiName`")
	mock.ExpectQuery(query).
		WithArgs("custom__c").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "ChildCloneRulesPackName", "ChildCloneRulesPackBusinessKey", "ChildCloneRulesPackManageability", "ChildCloneRulesPackValue", "ChildCloneRules", "ChildCloneRulesCustomized", "PackOwnedFieldCount"}).
			AddRow("Custom__c", "", "", "", nil, nil, 0, 1))

	ownership, err := loadCustomChildCloneRulePackOwnershipForApply(context.Background(), db, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
	}})
	if err != nil {
		t.Fatalf("loadCustomChildCloneRulePackOwnershipForApply = %v", err)
	}
	got, ok := ownership["custom__c"]
	if !ok {
		t.Fatalf("ownership missing custom__c: %#v", ownership)
	}
	if !got.MissingPackOwnership {
		t.Fatalf("ownership = %#v, want missing pack ownership marker", got)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestValidateTenantChildCloneRulesPreflightMergesStandardOverlayWithLiveMetadata(t *testing.T) {
	specs := []packs.ObjectSpec{
		{
			APIName:   "Account",
			TableType: string(constants.TableTypeStandardObject),
			Fields: []packs.FieldSpec{{
				APIName: "TenantFlag__c",
				Type:    string(constants.FieldTypeString),
			}},
		},
		{
			APIName:   "Parent__c",
			TableType: string(constants.TableTypeCustomObject),
			Fields: []packs.FieldSpec{
				{
					APIName:     "Account__c",
					Type:        string(constants.FieldTypeReference),
					ReferenceTo: []string{"Account"},
				},
				{
					APIName: "Name",
					Type:    string(constants.FieldTypeString),
				},
			},
			ChildCloneRules: []models.ChildCloneRule{{
				ParentLookupField: "Account__c",
				SourceObject:      "Account",
				SourceLookupField: "Id",
				TargetObject:      "Child__c",
				TargetLookupField: "Parent__c",
				FieldMappings:     map[string]string{"Name": "Name"},
			}},
		},
		{
			APIName:   "Child__c",
			TableType: string(constants.TableTypeCustomObject),
			Fields: []packs.FieldSpec{
				{
					APIName:     "Parent__c",
					Type:        string(constants.FieldTypeReference),
					ReferenceTo: []string{"Parent__c"},
				},
				{
					APIName: "Name",
					Type:    string(constants.FieldTypeString),
				},
			},
		},
	}
	fallback := tenantOverlayTestSchemaSnapshot{
		"account": {
			APIName: "Account",
			Fields: []models.FieldMetadata{
				{APIName: "Id", Type: constants.FieldTypeString},
				{APIName: "Name", Type: constants.FieldTypeString, Createable: true, Updateable: true},
			},
		},
	}

	if err := validateTenantChildCloneRulesPreflightWithFallback(context.Background(), fallback, specs); err != nil {
		t.Fatalf("validateTenantChildCloneRulesPreflightWithFallback = %v, want nil", err)
	}
}

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

	unresolvedQuery := regexp.QuoteMeta("SELECT `ApiName` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND `ChildCloneRules` IS NOT NULL AND TRIM(`ChildCloneRules`) <> '' AND TRIM(`ChildCloneRules`) <> '[]' AND COALESCE(`ChildCloneRulesPackName`, '') = '' AND COALESCE(`ChildCloneRulesPackBusinessKey`, '') = '' AND LOWER(`ApiName`) NOT IN (?) ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WithArgs("custom__c").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))
	clearQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ChildCloneRules` = NULL, `ChildCloneRulesCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`ChildCloneRulesCustomized`, 0) <> 0 AND LOWER(`ApiName`) NOT IN (?)")
	mock.ExpectExec(clearQuery).
		WithArgs("custom__c").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = New(t.TempDir()).applyObjectChildCloneRulesOverlay(context.Background(), db, []packs.ObjectSpec{{
		APIName:         "Custom__c",
		TableType:       string(constants.TableTypeCustomObject),
		ChildCloneRules: []models.ChildCloneRule{},
	}})
	if err != nil {
		t.Fatalf("applyObjectChildCloneRulesOverlay = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	unresolvedQuery := regexp.QuoteMeta("SELECT `ApiName` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND `ChildCloneRules` IS NOT NULL AND TRIM(`ChildCloneRules`) <> '' AND TRIM(`ChildCloneRules`) <> '[]' AND COALESCE(`ChildCloneRulesPackName`, '') = '' AND COALESCE(`ChildCloneRulesPackBusinessKey`, '') = '' AND LOWER(`ApiName`) NOT IN (?) ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WithArgs("custom__c").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))
	clearQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ChildCloneRules` = NULL, `ChildCloneRulesCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`ChildCloneRulesCustomized`, 0) <> 0 AND LOWER(`ApiName`) NOT IN (?)")
	mock.ExpectExec(clearQuery).
		WithArgs("custom__c").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = clearMissingChildCloneRuleOverlays(context.Background(), db, map[string]bool{"custom__c": true}, ObjectChildCloneRulesFile)
	if err != nil {
		t.Fatalf("clearMissingChildCloneRuleOverlays = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	query := regexp.QuoteMeta("SELECT `ApiName` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND `ChildCloneRules` IS NOT NULL AND TRIM(`ChildCloneRules`) <> '' AND TRIM(`ChildCloneRules`) <> '[]' AND COALESCE(`ChildCloneRulesPackName`, '') = '' AND COALESCE(`ChildCloneRulesPackBusinessKey`, '') = '' ORDER BY `ApiName`")
	mock.ExpectQuery(query).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}).AddRow("Legacy__c"))

	err = clearMissingChildCloneRuleOverlays(context.Background(), db, nil, ObjectChildCloneRulesFile)
	if err == nil || !strings.Contains(err.Error(), "unresolved sidecar-less ChildCloneRules rows") || !strings.Contains(err.Error(), "Legacy__c") {
		t.Fatalf("clearMissingChildCloneRuleOverlays error = %v, want unresolved Legacy__c failure", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	query := regexp.QuoteMeta("SELECT `ApiName` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND `ChildCloneRules` IS NOT NULL AND TRIM(`ChildCloneRules`) <> '' AND TRIM(`ChildCloneRules`) <> '[]' AND COALESCE(`ChildCloneRulesPackName`, '') = '' AND COALESCE(`ChildCloneRulesPackBusinessKey`, '') = '' ORDER BY `ApiName`")
	mock.ExpectQuery(query).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}).AddRow("Custom__c"))

	err = clearMissingChildCloneRuleOverlays(context.Background(), db, nil, ObjectChildCloneRulesFile, map[string]childCloneRulePackOwnership{
		"custom__c": {
			ObjectAPIName:        "Custom__c",
			MissingPackOwnership: true,
		},
	})
	if err == nil || !strings.Contains(err.Error(), "Custom__c") {
		t.Fatalf("clearMissingChildCloneRuleOverlays error = %v, want unresolved Custom__c failure", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	rules := []models.ChildCloneRule{{
		ParentLookupField: "ParentId",
		SourceObject:      "Contact",
		SourceLookupField: "AccountId",
		TargetObject:      "Contact",
		TargetLookupField: "AccountId",
		FieldMappings:     map[string]string{"LastName": "LastName"},
	}}
	unresolvedQuery := regexp.QuoteMeta("SELECT `ApiName` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND `ChildCloneRules` IS NOT NULL AND TRIM(`ChildCloneRules`) <> '' AND TRIM(`ChildCloneRules`) <> '[]' AND COALESCE(`ChildCloneRulesPackName`, '') = '' AND COALESCE(`ChildCloneRulesPackBusinessKey`, '') = '' AND LOWER(`ApiName`) NOT IN (?) ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WithArgs("custom__c").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))
	restoreQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ChildCloneRules` = ?, `ChildCloneRulesCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`ChildCloneRulesCustomized`, 0) <> 0 AND LOWER(`ApiName`) = ?")
	mock.ExpectExec(restoreQuery).
		WithArgs(`[{"parentLookupField":"ParentId","sourceObject":"Contact","sourceLookupField":"AccountId","targetObject":"Contact","targetLookupField":"AccountId","fieldMappings":{"LastName":"LastName"}}]`, "account").
		WillReturnResult(sqlmock.NewResult(0, 1))
	clearQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ChildCloneRules` = NULL, `ChildCloneRulesCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`ChildCloneRulesCustomized`, 0) <> 0 AND LOWER(`ApiName`) NOT IN (?)")
	mock.ExpectExec(clearQuery).
		WithArgs("custom__c").
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = clearMissingChildCloneRuleOverlays(context.Background(), db, map[string]bool{"custom__c": true}, ObjectChildCloneRulesFile, map[string]childCloneRulePackOwnership{
		"account": {
			ObjectAPIName: "Account",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			Rules:         rules,
		},
	})
	if err != nil {
		t.Fatalf("clearMissingChildCloneRuleOverlays = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyInsertRulesLandedChecksTenantObjectSpecChildCloneRules(t *testing.T) {
	dir := t.TempDir()
	spec := `[
		{
			"apiName": "Custom__c",
			"tableType": "custom_object",
			"childCloneRules": [
				{
					"parentLookupField": "Parent__c",
					"sourceObject": "Source__c",
					"sourceLookupField": "Parent__c",
					"targetObject": "Target__c",
					"targetLookupField": "Custom__c",
					"fieldMappings": {"Name": "Name"}
				}
			]
		}
	]`
	if err := os.WriteFile(filepath.Join(dir, ObjectsSpecFile), []byte(spec), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectsSpecFile, err)
	}

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

	stored := `[{"parentLookupField":"Parent__c","sourceObject":"Source__c","sourceLookupField":"Parent__c","targetObject":"Target__c","targetLookupField":"Custom__c","fieldMappings":{"Name":"Name"}}]`
	query := regexp.QuoteMeta("SELECT `ChildCloneRules`, COALESCE(`ChildCloneRulesCustomized`, 0) FROM `_System_Object` WHERE `Id` = ?")
	mock.ExpectQuery(query).
		WithArgs(domainschema.GenerateObjectID("Custom__c")).
		WillReturnRows(sqlmock.NewRows([]string{"ChildCloneRules", "ChildCloneRulesCustomized"}).AddRow(stored, 1))

	if err := New(dir).VerifyInsertRulesLanded(context.Background(), db); err != nil {
		t.Fatalf("VerifyInsertRulesLanded = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestValidateTenantDefaultChildRowsPreflightRejectsUnsatisfiedRequiredField(t *testing.T) {
	specs := []packs.ObjectSpec{
		{
			APIName:      "Parent__c",
			TableType:    string(constants.TableTypeCustomObject),
			SharingModel: string(constants.SharingModelPrivate),
			DefaultChildRows: []models.DefaultChildRow{{
				TargetObject:      "Child__c",
				TargetLookupField: "Parent__c",
				FieldValues:       models.SObject{"Optional__c": "seeded"},
			}},
			Fields: []packs.FieldSpec{
				{APIName: "Name", Label: "Name", Type: "string", Required: true, IsNameField: true},
			},
		},
		{
			APIName:      "Child__c",
			TableType:    string(constants.TableTypeCustomObject),
			SharingModel: string(constants.SharingModelPrivate),
			Fields: []packs.FieldSpec{
				{APIName: "Name", Label: "Name", Type: "string", Required: true, IsNameField: true},
				{APIName: "Parent__c", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}},
				{APIName: "RequiredText__c", Label: "Required Text", Type: "string", Required: true},
				{APIName: "Optional__c", Label: "Optional", Type: "string"},
			},
		},
	}

	err := validateTenantDefaultChildRowsPreflight(context.Background(), packs.ApplyDeps{}, specs, nil)
	if err == nil {
		t.Fatal("validateTenantDefaultChildRowsPreflight = nil, want required-field failure")
	}
	if !strings.Contains(err.Error(), "required target field") {
		t.Fatalf("validateTenantDefaultChildRowsPreflight error = %v, want required-target-field detail", err)
	}
}

func TestValidateTenantDefaultChildRowsPreflightRejectsSameOverlayEngineOwnedField(t *testing.T) {
	specs := []packs.ObjectSpec{
		{
			APIName:      "Parent__c",
			TableType:    string(constants.TableTypeCustomObject),
			SharingModel: string(constants.SharingModelPrivate),
			DefaultChildRows: []models.DefaultChildRow{{
				TargetObject:      "Child__c",
				TargetLookupField: "Parent__c",
				FieldValues:       models.SObject{"Name": "Seeded child", "EngineOwned__c": "seeded"},
			}},
			Fields: []packs.FieldSpec{
				{APIName: "Name", Label: "Name", Type: "string", Required: true, IsNameField: true},
			},
		},
		{
			APIName:      "Child__c",
			TableType:    string(constants.TableTypeCustomObject),
			SharingModel: string(constants.SharingModelPrivate),
			Fields: []packs.FieldSpec{
				{APIName: "Name", Label: "Name", Type: "string", Required: true, IsNameField: true},
				{APIName: "Parent__c", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}},
				{APIName: "EngineOwned__c", Label: "Engine Owned", Type: "string"},
			},
		},
	}
	autoNumberSeeds := []autoNumberFormatSeed{{
		ObjectName:   "Child__c",
		FieldApiName: "EngineOwned__c",
		Format:       "ENG-{0000}",
		Scope:        "global",
		Start:        1,
	}}

	err := validateTenantDefaultChildRowsPreflight(context.Background(), packs.ApplyDeps{}, specs, autoNumberSeeds)
	if err == nil {
		t.Fatal("validateTenantDefaultChildRowsPreflight = nil, want engine-owned field failure")
	}
	if !strings.Contains(err.Error(), "EngineOwned__c") {
		t.Fatalf("validateTenantDefaultChildRowsPreflight error = %v, want engine-owned field detail", err)
	}
	if !strings.Contains(err.Error(), "engine-owned") {
		t.Fatalf("validateTenantDefaultChildRowsPreflight error = %v, want engine-owned rejection", err)
	}
}

type tenantOverlayTestSchemaSnapshot map[string]*models.ObjectMetadata

func (s tenantOverlayTestSchemaSnapshot) GetSchema(_ context.Context, apiName string) *models.ObjectMetadata {
	return s[strings.ToLower(strings.TrimSpace(apiName))]
}