package tenantoverlay

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

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

func TestValidateTenantExclusiveSiblingFieldsSpecsRejectsStandardObjectOverlay(t *testing.T) {
	err := validateTenantExclusiveSiblingFieldsSpecs([]packs.ObjectSpec{{
		APIName:   "CampaignMemberStatus",
		TableType: string(constants.TableTypeStandardObject),
		ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
			FieldAPIName:          "IsDefault",
			PartitionFieldAPIName: "CampaignId",
		}},
	}})
	if err == nil {
		t.Fatal("validateTenantExclusiveSiblingFieldsSpecs = nil, want standard-object rejection")
	}
	if !strings.Contains(err.Error(), "standard-object overlay declarations are unsupported") {
		t.Fatalf("validateTenantExclusiveSiblingFieldsSpecs error = %v, want unsupported detail", err)
	}
}

func TestApplyRejectsInvalidTenantExclusiveSiblingFieldsBeforePersistence(t *testing.T) {
	dir := t.TempDir()
	payload := `[
		{
			"apiName": "Parent__c",
			"tableType": "custom_object",
			"fields": [
				{"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true}
			]
		},
		{
			"apiName": "Child__c",
			"tableType": "custom_object",
			"exclusiveSiblingFields": [
				{"fieldApiName": "Missing__c", "partitionFieldApiName": "Parent__c"}
			],
			"fields": [
				{"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
				{"apiName": "Parent__c", "label": "Parent", "type": "reference", "referenceTo": ["Parent__c"]},
				{"apiName": "Primary__c", "label": "Primary", "type": "boolean"}
			]
		}
	]`
	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 exclusiveSiblingFields validation error before persistence")
	}
	if !strings.Contains(err.Error(), "tenant exclusiveSiblingFields preflight") {
		t.Fatalf("Apply error = %v, want tenant exclusiveSiblingFields preflight detail", err)
	}
	if !strings.Contains(err.Error(), "Missing__c") {
		t.Fatalf("Apply error = %v, want missing field detail", err)
	}
}

func TestValidateTenantExclusiveSiblingFieldsPreflightRejectsInvalidFieldShapes(t *testing.T) {
	specs := []packs.ObjectSpec{
		{
			APIName:   "Parent__c",
			TableType: string(constants.TableTypeCustomObject),
			Fields: []packs.FieldSpec{
				{APIName: "Name", Label: "Name", Type: string(constants.FieldTypeString), Required: true, IsNameField: true},
			},
		},
		{
			APIName:   "Child__c",
			TableType: string(constants.TableTypeCustomObject),
			ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
				FieldAPIName:          "Primary__c",
				PartitionFieldAPIName: "Partition__c",
			}},
			Fields: []packs.FieldSpec{
				{APIName: "Name", Label: "Name", Type: string(constants.FieldTypeString), Required: true, IsNameField: true},
				{APIName: "Primary__c", Label: "Primary", Type: string(constants.FieldTypeString)},
				{APIName: "Partition__c", Label: "Partition", Type: string(constants.FieldTypeString)},
			},
		},
	}

	err := validateTenantExclusiveSiblingFieldsPreflight(context.Background(), packs.ApplyDeps{}, specs)
	if err == nil {
		t.Fatal("validateTenantExclusiveSiblingFieldsPreflight = nil, want validation error")
	}
	if !strings.Contains(err.Error(), "must be boolean") {
		t.Fatalf("validateTenantExclusiveSiblingFieldsPreflight error = %v, want boolean detail", err)
	}
	if !strings.Contains(err.Error(), "must be a reference or master-detail field") {
		t.Fatalf("validateTenantExclusiveSiblingFieldsPreflight error = %v, want partition reference detail", err)
	}
}

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

	err := New(dir).validateObjectExclusiveSiblingFieldsFilePayload(context.Background(), packs.ApplyDeps{}, []packs.ObjectSpec{{
		APIName:   "Custom__c",
		TableType: string(constants.TableTypeCustomObject),
	}}, map[string]exclusiveSiblingFieldsPackOwnership{
		"custom__c": {
			ObjectAPIName: "Custom__c",
			PackName:      "custom-pack",
			BusinessKey:   "objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			PackValue:     "[]",
		},
	})
	if err == nil {
		t.Fatal("validateObjectExclusiveSiblingFieldsFilePayload = nil, want custom-object rejection")
	}
	if !strings.Contains(err.Error(), ObjectExclusiveSiblingFieldsFile) || !strings.Contains(err.Error(), ObjectsSpecFile) {
		t.Fatalf("validateObjectExclusiveSiblingFieldsFilePayload error = %v, want file boundary detail", err)
	}
}

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

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

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

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

func TestValidateObjectExclusiveSiblingFieldsFilePayloadRejectsInvalidStandardOverridePreflight(t *testing.T) {
	dir := t.TempDir()
	payload := `{
		"CampaignMemberStatus": [
			{"fieldApiName": "IsDefault", "partitionFieldApiName": "CampaignId"}
		]
	}`
	if err := os.WriteFile(filepath.Join(dir, ObjectExclusiveSiblingFieldsFile), []byte(payload), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectExclusiveSiblingFieldsFile, err)
	}
	specs := []packs.ObjectSpec{
		{
			APIName:   "Campaign",
			TableType: string(constants.TableTypeStandardObject),
		},
		{
			APIName:   "CampaignMemberStatus",
			TableType: string(constants.TableTypeStandardObject),
			Fields: []packs.FieldSpec{
				{APIName: "IsDefault", Type: string(constants.FieldTypeString)},
				{APIName: "CampaignId", Type: string(constants.FieldTypeReference), ReferenceTo: []string{"Campaign"}},
			},
		},
	}

	err := New(dir).validateObjectExclusiveSiblingFieldsFilePayload(context.Background(), packs.ApplyDeps{}, specs, map[string]exclusiveSiblingFieldsPackOwnership{
		"campaignmemberstatus": {
			ObjectAPIName: "CampaignMemberStatus",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			PackValue:     "[]",
		},
	})
	if err == nil {
		t.Fatal("validateObjectExclusiveSiblingFieldsFilePayload = nil, want preflight validation error")
	}
	if !strings.Contains(err.Error(), ObjectExclusiveSiblingFieldsFile) || !strings.Contains(err.Error(), "must be boolean") {
		t.Fatalf("validateObjectExclusiveSiblingFieldsFilePayload error = %v, want boolean validation detail", err)
	}
}

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

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

	query := regexp.QuoteMeta("SELECT `ApiName`, COALESCE(`ExclusiveSiblingFieldsPackName`, ''), COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, ''), COALESCE(`ExclusiveSiblingFieldsPackManageability`, ''), `ExclusiveSiblingFieldsPackValue`, `ExclusiveSiblingFields` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND COALESCE(`IsCustom`, 0) = 0 AND (COALESCE(`ExclusiveSiblingFieldsCustomized`, 0) <> 0 OR LOWER(`ApiName`) IN (?)) ORDER BY `ApiName`")
	mock.ExpectQuery(query).
		WithArgs("campaignmemberstatus").
		WillReturnRows(sqlmock.NewRows([]string{
			"ApiName",
			"ExclusiveSiblingFieldsPackName",
			"ExclusiveSiblingFieldsPackBusinessKey",
			"ExclusiveSiblingFieldsPackManageability",
			"ExclusiveSiblingFieldsPackValue",
			"ExclusiveSiblingFields",
		}).AddRow("CampaignMemberStatus", "salesforce-crm-standard", "crm-objects", string(packs.ManageabilityDefaultsOnly), `[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`, nil))

	ownership, err := New(dir).loadExclusiveSiblingFieldsPackOwnershipForApply(context.Background(), db)
	if err != nil {
		t.Fatalf("loadExclusiveSiblingFieldsPackOwnershipForApply = %v", err)
	}
	got, ok := ownership["campaignmemberstatus"]
	if !ok {
		t.Fatalf("ownership missing CampaignMemberStatus: %#v", ownership)
	}
	if got.PackName != "salesforce-crm-standard" || got.BusinessKey != "crm-objects" || got.Manageability != packs.ManageabilityDefaultsOnly {
		t.Fatalf("ownership = %#v, want persisted pack metadata", got)
	}
	if len(got.Fields) != 1 || got.Fields[0].FieldAPIName != "IsDefault" || got.PackValue == "" {
		t.Fatalf("ownership fields = %#v packValue=%q, want persisted ExclusiveSiblingFields", got.Fields, got.PackValue)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	ownership, err := New(t.TempDir()).loadExclusiveSiblingFieldsPackOwnershipForApply(context.Background(), db)
	if err != nil {
		t.Fatalf("loadExclusiveSiblingFieldsPackOwnershipForApply = %v", err)
	}
	if len(ownership) != 0 {
		t.Fatalf("ownership = %#v, want empty when %s is absent", ownership, ObjectExclusiveSiblingFieldsFile)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	updateSQL := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = ?, `ExclusiveSiblingFieldsCustomized` = 1, `LastModifiedDate` = NOW() WHERE `ApiName` = ? AND COALESCE(`IsDeleted`, 0) = 0")
	mock.ExpectExec(updateSQL).
		WithArgs("[]", "CampaignMemberStatus").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = applyExclusiveSiblingFieldsOverlayMap(context.Background(), db, map[string][]models.ExclusiveSiblingField{
		"CampaignMemberStatus": []models.ExclusiveSiblingField{},
	}, ObjectExclusiveSiblingFieldsFile)
	if err != nil {
		t.Fatalf("applyExclusiveSiblingFieldsOverlayMap = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	err = New(t.TempDir()).applyObjectExclusiveSiblingFieldsOverlay(context.Background(), db, nil, map[string]exclusiveSiblingFieldsPackOwnership{
		"campaignmemberstatus": {
			ObjectAPIName: "CampaignMemberStatus",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			PackValue:     `[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`,
		},
	})
	if err != nil {
		t.Fatalf("applyObjectExclusiveSiblingFieldsOverlay = %v, want nil when %s is absent", err, ObjectExclusiveSiblingFieldsFile)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestApplyObjectExclusiveSiblingFieldsOverlayPreflightsCleanupBeforeWrites(t *testing.T) {
	dir := t.TempDir()
	payload := `{
		"CampaignMemberStatus": [
			{"fieldApiName": "IsDefault", "partitionFieldApiName": "CampaignId"}
		]
	}`
	if err := os.WriteFile(filepath.Join(dir, ObjectExclusiveSiblingFieldsFile), []byte(payload), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectExclusiveSiblingFieldsFile, err)
	}
	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 COALESCE(`IsCustom`, 0) = 0 AND `ExclusiveSiblingFields` IS NOT NULL AND TRIM(`ExclusiveSiblingFields`) <> '' AND TRIM(`ExclusiveSiblingFields`) <> '[]' AND COALESCE(`ExclusiveSiblingFieldsPackName`, '') = '' AND COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, '') = '' AND LOWER(`ApiName`) NOT IN (?) ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WithArgs("campaignmemberstatus").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}).AddRow("LegacyStatus"))

	err = New(dir).applyObjectExclusiveSiblingFieldsOverlay(context.Background(), db, nil, map[string]exclusiveSiblingFieldsPackOwnership{
		"campaignmemberstatus": {
			ObjectAPIName: "CampaignMemberStatus",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			PackValue:     `[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`,
		},
	})
	if err == nil || !strings.Contains(err.Error(), "unresolved sidecar-less ExclusiveSiblingFields rows") || !strings.Contains(err.Error(), "LegacyStatus") {
		t.Fatalf("applyObjectExclusiveSiblingFieldsOverlay error = %v, want cleanup preflight failure before writes", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestApplyPreflightsStandardExclusiveSiblingCleanupBeforeCustomObjectWrites(t *testing.T) {
	dir := t.TempDir()
	spec := `[
		{
			"apiName": "Campaign",
			"tableType": "standard_object"
		},
		{
			"apiName": "CampaignMemberStatus",
			"tableType": "standard_object",
			"fields": [
				{"apiName": "IsDefault", "label": "Default", "type": "boolean"},
				{"apiName": "CampaignId", "label": "Campaign", "type": "reference", "referenceTo": ["Campaign"]}
			]
		},
		{
			"apiName": "Parent__c",
			"tableType": "custom_object",
			"fields": [
				{"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true}
			]
		},
		{
			"apiName": "Child__c",
			"tableType": "custom_object",
			"exclusiveSiblingFields": [
				{"fieldApiName": "Primary__c", "partitionFieldApiName": "Parent__c"}
			],
			"fields": [
				{"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
				{"apiName": "Parent__c", "label": "Parent", "type": "reference", "referenceTo": ["Parent__c"]},
				{"apiName": "Primary__c", "label": "Primary", "type": "boolean"}
			]
		}
	]`
	if err := os.WriteFile(filepath.Join(dir, ObjectsSpecFile), []byte(spec), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectsSpecFile, err)
	}
	standardOverlay := `{
		"CampaignMemberStatus": [
			{"fieldApiName": "IsDefault", "partitionFieldApiName": "CampaignId"}
		]
	}`
	if err := os.WriteFile(filepath.Join(dir, ObjectExclusiveSiblingFieldsFile), []byte(standardOverlay), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectExclusiveSiblingFieldsFile, err)
	}

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

	childCloneOwnershipQuery := 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(childCloneOwnershipQuery).
		WillReturnRows(sqlmock.NewRows([]string{
			"ApiName",
			"ChildCloneRulesPackName",
			"ChildCloneRulesPackBusinessKey",
			"ChildCloneRulesPackManageability",
			"ChildCloneRulesPackValue",
		}))
	ownershipQuery := regexp.QuoteMeta("SELECT `ApiName`, COALESCE(`ExclusiveSiblingFieldsPackName`, ''), COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, ''), COALESCE(`ExclusiveSiblingFieldsPackManageability`, ''), `ExclusiveSiblingFieldsPackValue`, `ExclusiveSiblingFields` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND COALESCE(`IsCustom`, 0) = 0 AND (COALESCE(`ExclusiveSiblingFieldsCustomized`, 0) <> 0 OR LOWER(`ApiName`) IN (?)) ORDER BY `ApiName`")
	mock.ExpectQuery(ownershipQuery).
		WithArgs("campaignmemberstatus").
		WillReturnRows(sqlmock.NewRows([]string{
			"ApiName",
			"ExclusiveSiblingFieldsPackName",
			"ExclusiveSiblingFieldsPackBusinessKey",
			"ExclusiveSiblingFieldsPackManageability",
			"ExclusiveSiblingFieldsPackValue",
			"ExclusiveSiblingFields",
		}).AddRow(
			"CampaignMemberStatus",
			"salesforce-crm-standard",
			"crm-objects",
			string(packs.ManageabilityDefaultsOnly),
			`[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`,
			nil,
		))
	unresolvedQuery := regexp.QuoteMeta("SELECT `ApiName` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND COALESCE(`IsCustom`, 0) = 0 AND `ExclusiveSiblingFields` IS NOT NULL AND TRIM(`ExclusiveSiblingFields`) <> '' AND TRIM(`ExclusiveSiblingFields`) <> '[]' AND COALESCE(`ExclusiveSiblingFieldsPackName`, '') = '' AND COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, '') = '' AND LOWER(`ApiName`) NOT IN (?) ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WithArgs("campaignmemberstatus").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}).AddRow("LegacyStatus"))

	err = New(dir).Apply(context.Background(), packs.ApplyDeps{
		DB: db,
	})
	if err == nil || !strings.Contains(err.Error(), "tenant object exclusiveSiblingFields cleanup preflight") || !strings.Contains(err.Error(), "LegacyStatus") {
		t.Fatalf("Apply error = %v, want standard cleanup preflight failure before custom-object writes", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestClearMissingExclusiveSiblingFieldsOverlaysRestoresPackValueWhenOverrideRemoved(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 COALESCE(`IsCustom`, 0) = 0 AND `ExclusiveSiblingFields` IS NOT NULL AND TRIM(`ExclusiveSiblingFields`) <> '' AND TRIM(`ExclusiveSiblingFields`) <> '[]' AND COALESCE(`ExclusiveSiblingFieldsPackName`, '') = '' AND COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, '') = '' ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))
	restoreQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = ?, `ExclusiveSiblingFieldsCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`IsCustom`, 0) = 0 AND COALESCE(`ExclusiveSiblingFieldsCustomized`, 0) <> 0 AND LOWER(`ApiName`) = ?")
	mock.ExpectExec(restoreQuery).
		WithArgs(`[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`, "campaignmemberstatus").
		WillReturnResult(sqlmock.NewResult(0, 1))
	clearQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = NULL, `ExclusiveSiblingFieldsCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`IsCustom`, 0) = 0 AND COALESCE(`ExclusiveSiblingFieldsCustomized`, 0) <> 0")
	mock.ExpectExec(clearQuery).
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = clearMissingExclusiveSiblingFieldsOverlays(context.Background(), db, nil, ObjectExclusiveSiblingFieldsFile, map[string]exclusiveSiblingFieldsPackOwnership{
		"campaignmemberstatus": {
			ObjectAPIName: "CampaignMemberStatus",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			PackValue:     `[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`,
		},
	})
	if err != nil {
		t.Fatalf("clearMissingExclusiveSiblingFieldsOverlays = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestApplyObjectExclusiveSiblingFieldsOverlayWithEmptyFileClearsRemovedOverrides(t *testing.T) {
	dir := t.TempDir()
	if err := os.WriteFile(filepath.Join(dir, ObjectExclusiveSiblingFieldsFile), []byte(`{}`), 0o600); err != nil {
		t.Fatalf("write %s: %v", ObjectExclusiveSiblingFieldsFile, err)
	}
	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 COALESCE(`IsCustom`, 0) = 0 AND `ExclusiveSiblingFields` IS NOT NULL AND TRIM(`ExclusiveSiblingFields`) <> '' AND TRIM(`ExclusiveSiblingFields`) <> '[]' AND COALESCE(`ExclusiveSiblingFieldsPackName`, '') = '' AND COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, '') = '' ORDER BY `ApiName`")
	mock.ExpectQuery(unresolvedQuery).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}))
	restoreQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = ?, `ExclusiveSiblingFieldsCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`IsCustom`, 0) = 0 AND COALESCE(`ExclusiveSiblingFieldsCustomized`, 0) <> 0 AND LOWER(`ApiName`) = ?")
	mock.ExpectExec(restoreQuery).
		WithArgs(`[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`, "campaignmemberstatus").
		WillReturnResult(sqlmock.NewResult(0, 1))
	clearQuery := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = NULL, `ExclusiveSiblingFieldsCustomized` = 0, `LastModifiedDate` = NOW() WHERE COALESCE(`IsCustom`, 0) = 0 AND COALESCE(`ExclusiveSiblingFieldsCustomized`, 0) <> 0")
	mock.ExpectExec(clearQuery).
		WillReturnResult(sqlmock.NewResult(0, 0))

	err = New(dir).applyObjectExclusiveSiblingFieldsOverlay(context.Background(), db, nil, map[string]exclusiveSiblingFieldsPackOwnership{
		"campaignmemberstatus": {
			ObjectAPIName: "CampaignMemberStatus",
			PackName:      "salesforce-crm-standard",
			BusinessKey:   "crm-objects",
			Manageability: packs.ManageabilityDefaultsOnly,
			PackValue:     `[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`,
		},
	})
	if err != nil {
		t.Fatalf("applyObjectExclusiveSiblingFieldsOverlay = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestClearMissingExclusiveSiblingFieldsOverlaysFailsClosedOnUnresolvedStandardRowsWithoutSidecar(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 COALESCE(`IsCustom`, 0) = 0 AND `ExclusiveSiblingFields` IS NOT NULL AND TRIM(`ExclusiveSiblingFields`) <> '' AND TRIM(`ExclusiveSiblingFields`) <> '[]' AND COALESCE(`ExclusiveSiblingFieldsPackName`, '') = '' AND COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, '') = '' ORDER BY `ApiName`")
	mock.ExpectQuery(query).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName"}).AddRow("CampaignMemberStatus"))

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

func TestRejectMissingExclusiveSiblingFieldsPackOwnershipForCleanupFailsOnIncompleteOwnership(t *testing.T) {
	err := rejectMissingExclusiveSiblingFieldsPackOwnershipForCleanup(nil, map[string]exclusiveSiblingFieldsPackOwnership{
		"campaignmemberstatus": {
			ObjectAPIName:        "CampaignMemberStatus",
			MissingPackOwnership: true,
		},
	}, ObjectExclusiveSiblingFieldsFile)
	if err == nil || !strings.Contains(err.Error(), "incomplete pack ownership") || !strings.Contains(err.Error(), "CampaignMemberStatus") {
		t.Fatalf("rejectMissingExclusiveSiblingFieldsPackOwnershipForCleanup error = %v, want incomplete ownership failure", err)
	}

	err = rejectMissingExclusiveSiblingFieldsPackOwnershipForCleanup(nil, map[string]exclusiveSiblingFieldsPackOwnership{
		"account": {
			ObjectAPIName:        "Account",
			MissingPackOwnership: true,
		},
	}, ObjectExclusiveSiblingFieldsFile)
	if err == nil || !strings.Contains(err.Error(), "Account") {
		t.Fatalf("rejectMissingExclusiveSiblingFieldsPackOwnershipForCleanup empty live value = %v, want fail-closed error", err)
	}
}

func TestBuildTenantObjectMetadataCopiesExclusiveSiblingFieldsAndMarksCustomized(t *testing.T) {
	objects := buildTenantObjectMetadata([]packs.ObjectSpec{{
		APIName:   "Child__c",
		TableType: string(constants.TableTypeCustomObject),
		ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
			FieldAPIName:          "Primary__c",
			PartitionFieldAPIName: "Parent__c",
		}},
	}})
	if len(objects) != 1 {
		t.Fatalf("buildTenantObjectMetadata objects = %d, want 1", len(objects))
	}
	if len(objects[0].ExclusiveSiblingFields) != 1 {
		t.Fatalf("buildTenantObjectMetadata ExclusiveSiblingFields = %#v, want copied declaration", objects[0].ExclusiveSiblingFields)
	}
	if !objects[0].ExclusiveSiblingFieldsCustomized {
		t.Fatal("buildTenantObjectMetadata ExclusiveSiblingFieldsCustomized = false, want true for L2-owned value")
	}
}

func TestRejectPackOwnedTenantObjectSpecExclusiveSiblingFields(t *testing.T) {
	err := rejectPackOwnedTenantObjectSpecExclusiveSiblingFields([]packs.ObjectSpec{{
		APIName:                "PackOwned__c",
		TableType:              string(constants.TableTypeCustomObject),
		ExclusiveSiblingFields: []models.ExclusiveSiblingField{},
		Fields:                 []packs.FieldSpec{{APIName: "Name", Type: string(constants.FieldTypeString)}},
		SharingModel:           string(constants.SharingModelPrivate),
		EnableHierarchySharing: false,
	}}, map[string]exclusiveSiblingFieldsPackOwnership{
		"packowned__c": {
			ObjectAPIName: "PackOwned__c",
			PackName:      "sample-pack",
			BusinessKey:   "objects",
			Manageability: packs.ManageabilityDefaultsOnly,
		},
	})
	if err == nil {
		t.Fatal("rejectPackOwnedTenantObjectSpecExclusiveSiblingFields = nil, want pack-owned rejection")
	}
	if !strings.Contains(err.Error(), "source-backed custom object PackOwned__c") {
		t.Fatalf("rejectPackOwnedTenantObjectSpecExclusiveSiblingFields error = %v, want source-backed object detail", err)
	}
}

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

	mock.ExpectQuery("SELECT o\\.`ApiName`.*ExclusiveSiblingFieldsPackName.*FROM `_System_Object` o WHERE.*LOWER\\(o\\.`ApiName`\\) IN \\(\\?\\)").
		WithArgs("packowned__c").
		WillReturnRows(sqlmock.NewRows([]string{
			"ApiName",
			"ExclusiveSiblingFieldsPackName",
			"ExclusiveSiblingFieldsPackBusinessKey",
			"ExclusiveSiblingFieldsPackManageability",
			"ExclusiveSiblingFields",
			"ExclusiveSiblingFieldsCustomized",
			"packOwnedFieldCount",
		}).AddRow("PackOwned__c", "", "", "", nil, 0, 1))

	ownership, err := loadCustomExclusiveSiblingFieldsPackOwnershipForApply(context.Background(), db, []packs.ObjectSpec{{
		APIName:   "PackOwned__c",
		TableType: string(constants.TableTypeCustomObject),
	}})
	if err != nil {
		t.Fatalf("loadCustomExclusiveSiblingFieldsPackOwnershipForApply: %v", err)
	}
	got, ok := ownership["packowned__c"]
	if !ok || !got.MissingPackOwnership {
		t.Fatalf("ownership = %#v, want missing pack ownership for pack-owned custom object", ownership)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	updateSQL := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = ?, `ExclusiveSiblingFieldsCustomized` = 1, `LastModifiedDate` = NOW() WHERE `ApiName` = ? AND COALESCE(`IsDeleted`, 0) = 0")
	mock.ExpectExec(updateSQL).
		WithArgs(nil, "Clear__c").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(updateSQL).
		WithArgs(`[{"fieldApiName":"Primary__c","partitionFieldApiName":"Parent__c"}]`, "Primary__c").
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = applyTenantObjectSpecExclusiveSiblingFields(context.Background(), db, []packs.ObjectSpec{
		{
			APIName:   "Primary__c",
			TableType: string(constants.TableTypeCustomObject),
			ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
				FieldAPIName:          "Primary__c",
				PartitionFieldAPIName: "Parent__c",
			}},
		},
		{
			APIName:   "Clear__c",
			TableType: string(constants.TableTypeCustomObject),
		},
	}, nil)
	if err != nil {
		t.Fatalf("applyTenantObjectSpecExclusiveSiblingFields: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestUpdateExclusiveSiblingFieldsOverlayAcceptsIdempotentNoChangeOnlyWhenAuthorityExists(t *testing.T) {
	for _, tc := range []struct {
		name    string
		exists  int
		wantErr bool
	}{
		{name: "existing authoritative row", exists: 1},
		{name: "missing authoritative row", exists: 0, wantErr: true},
	} {
		t.Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock: %v", err)
			}
			defer db.Close()

			updateSQL := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFields` = ?, `ExclusiveSiblingFieldsCustomized` = 1, `LastModifiedDate` = NOW() WHERE `ApiName` = ? AND COALESCE(`IsDeleted`, 0) = 0")
			mock.ExpectExec(updateSQL).
				WithArgs("[]", "Example__c").
				WillReturnResult(sqlmock.NewResult(0, 0))
			existsSQL := regexp.QuoteMeta("SELECT COUNT(*) FROM `_System_Object` WHERE `ApiName` = ? AND COALESCE(`IsDeleted`, 0) = 0")
			mock.ExpectQuery(existsSQL).
				WithArgs("Example__c").
				WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(tc.exists))

			err = updateExclusiveSiblingFieldsOverlay(context.Background(), db, "Example__c", "[]", ObjectsSpecFile)
			if tc.wantErr && err == nil {
				t.Fatal("updateExclusiveSiblingFieldsOverlay = nil, want missing-authority error")
			}
			if !tc.wantErr && err != nil {
				t.Fatalf("updateExclusiveSiblingFieldsOverlay: %v", err)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}