package packs

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

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

func TestPersistObjectSpecExclusiveSiblingFieldPackOwnershipWritesDBSidecar(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 `ExclusiveSiblingFieldsPackName` = ?, `ExclusiveSiblingFieldsPackBusinessKey` = ?, `ExclusiveSiblingFieldsPackManageability` = ?, `ExclusiveSiblingFieldsPackValue` = ?, `LastModifiedDate` = NOW() WHERE `Id` = ?")
	mock.ExpectExec(query).
		WithArgs(
			"salesforce-crm-standard",
			"objects",
			string(ManageabilityDefaultsOnly),
			`[{"fieldApiName":"IsDefault","partitionFieldApiName":"CampaignId"}]`,
			domainschema.GenerateObjectID("CampaignMemberStatus"),
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err = persistObjectSpecExclusiveSiblingFieldPackOwnership(context.Background(), db, "salesforce-crm-standard", "objects", ManageabilityDefaultsOnly, []ObjectSpec{{
		APIName: "CampaignMemberStatus",
		ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
			FieldAPIName:          "IsDefault",
			PartitionFieldAPIName: "CampaignId",
		}},
	}}, true)
	if err != nil {
		t.Fatalf("persistObjectSpecExclusiveSiblingFieldPackOwnership = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	id := domainschema.GenerateObjectID("Parent__c")
	selectSQL := regexp.QuoteMeta("SELECT `Id`, `ApiName`, `ExclusiveSiblingFields`, COALESCE(`ExclusiveSiblingFieldsCustomized`, 0), COALESCE(`ExclusiveSiblingFieldsPackName`, ''), COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, ''), COALESCE(`ExclusiveSiblingFieldsPackManageability`, ''), `ExclusiveSiblingFieldsPackValue` FROM `_System_Object` WHERE COALESCE(`IsDeleted`, 0) = 0 AND `Id` IN (?) ORDER BY `Id`")
	mock.ExpectQuery(selectSQL).
		WithArgs(id).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ApiName", "ExclusiveSiblingFields", "ExclusiveSiblingFieldsCustomized", "ExclusiveSiblingFieldsPackName", "ExclusiveSiblingFieldsPackBusinessKey", "ExclusiveSiblingFieldsPackManageability", "ExclusiveSiblingFieldsPackValue"}).
			AddRow(id, "Parent__c", nil, 0, "", "", "", nil))

	err = persistObjectSpecExclusiveSiblingFieldPackOwnership(context.Background(), db, "salesforce-crm-standard", "objects", ManageabilityDefaultsOnly, []ObjectSpec{{
		APIName: "Parent__c",
	}}, true)
	if err != nil {
		t.Fatalf("persistObjectSpecExclusiveSiblingFieldPackOwnership = %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectExclusiveSiblingFieldsDefersLegacySidecarlessEmptyRows(t *testing.T) {
	err := verifyPackObjectExclusiveSiblingFieldsAgainstRegistry(
		"salesforce-crm-standard",
		"objects",
		[]ObjectSpec{{
			APIName: "CampaignMemberStatus",
		}},
		ManageabilityDefaultsOnly,
		map[string]packObjectExclusiveSiblingRegistryRow{
			"campaignmemberstatus": {
				exclusiveSiblingFields: sql.NullString{
					String: `[]`,
					Valid:  true,
				},
			},
		},
	)
	if err != nil {
		t.Fatalf("verifyPackObjectExclusiveSiblingFieldsAgainstRegistry = %v, want nil", err)
	}
}

func TestVerifyPackObjectExclusiveSiblingFieldsFailsClosedWhenSiblingSidecarsArePartiallyHydrated(t *testing.T) {
	err := verifyPackObjectExclusiveSiblingFieldsAgainstRegistry(
		"salesforce-crm-standard",
		"objects",
		[]ObjectSpec{{
			APIName: "CampaignMemberStatus",
		}, {
			APIName: "CampaignMemberStatusValue",
			ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
				FieldAPIName:          "IsDefault",
				PartitionFieldAPIName: "CampaignId",
			}},
		}},
		ManageabilityLocked,
		map[string]packObjectExclusiveSiblingRegistryRow{
			"campaignmemberstatus": {
				exclusiveSiblingFields:              sql.NullString{String: `[]`, Valid: true},
				exclusiveSiblingFieldsPackName:      "salesforce-crm-standard",
				exclusiveSiblingFieldsBusinessKey:   "objects",
				exclusiveSiblingFieldsManageability: string(ManageabilityLocked),
				exclusiveSiblingFieldsPackValue:     sql.NullString{String: `[]`, Valid: true},
			},
			"campaignmemberstatusvalue": {
				exclusiveSiblingFields: sql.NullString{String: `[]`, Valid: true},
			},
		},
	)
	if err == nil {
		t.Fatal("verifyPackObjectExclusiveSiblingFieldsAgainstRegistry = nil, want missing sidecar detail")
	}
	if !strings.Contains(err.Error(), "ExclusiveSiblingFieldsPackName") {
		t.Fatalf("error = %v, want pack sidecar detail", err)
	}
}

func TestVerifyPackObjectExclusiveSiblingFieldsSkipsEmptyOwnerlessSpecs(t *testing.T) {
	err := verifyPackObjectExclusiveSiblingFieldsAgainstRegistry(
		"salesforce-crm-standard",
		"objects",
		[]ObjectSpec{{
			APIName: "CampaignMemberStatus",
		}, {
			APIName: "CampaignMemberStatusValue",
		}},
		ManageabilityLocked,
		map[string]packObjectExclusiveSiblingRegistryRow{
			"campaignmemberstatus": {
				exclusiveSiblingFields:              sql.NullString{String: `[]`, Valid: true},
				exclusiveSiblingFieldsPackName:      "salesforce-crm-standard",
				exclusiveSiblingFieldsBusinessKey:   "objects",
				exclusiveSiblingFieldsManageability: string(ManageabilityLocked),
				exclusiveSiblingFieldsPackValue:     sql.NullString{String: `[]`, Valid: true},
			},
			"campaignmemberstatusvalue": {
				exclusiveSiblingFields: sql.NullString{String: `[]`, Valid: true},
			},
		},
	)
	if err != nil {
		t.Fatalf("verifyPackObjectExclusiveSiblingFieldsAgainstRegistry = %v, want nil for empty ownerless sibling spec", err)
	}
}

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

	mock.ExpectQuery("SELECT Name, Category, ManifestFormatVersion, SourceKind, SourceURI").
		WithArgs(string(AppPack)).
		WillReturnRows(sqlmock.NewRows([]string{"Name", "Category", "ManifestFormatVersion", "SourceKind", "SourceURI"}).
			AddRow("jira-like-sample", string(AppPack), SupportedManifestFormatVersion, string(PackSourceKindLocalPath), sourceDir))

	updateSQL := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFieldsPackName` = ?, `ExclusiveSiblingFieldsPackBusinessKey` = ?, `ExclusiveSiblingFieldsPackManageability` = ?, `ExclusiveSiblingFieldsPackValue` = ?, `LastModifiedDate` = NOW() WHERE `Id` = ? AND COALESCE(`IsDeleted`, 0) = 0 AND ((COALESCE(`ExclusiveSiblingFieldsPackName`, '') = '' AND COALESCE(`ExclusiveSiblingFieldsPackBusinessKey`, '') = '') OR (`ExclusiveSiblingFieldsPackName` = ? AND `ExclusiveSiblingFieldsPackBusinessKey` = ? AND (`ExclusiveSiblingFieldsPackValue` IS NULL OR TRIM(`ExclusiveSiblingFieldsPackValue`) = '')))")
	mock.ExpectExec(updateSQL).
		WithArgs("jira-like-sample", "objects", string(ManageabilityLocked), `[{"fieldApiName":"IsDefault__c","partitionFieldApiName":"Parent__c"}]`, domainschema.GenerateObjectID("Status__c"), "jira-like-sample", "objects").
		WillReturnResult(sqlmock.NewResult(0, 1))

	updated, err := BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacks(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacks = %v", err)
	}
	if updated != 1 {
		t.Fatalf("updated = %d, want 1", updated)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectQuery("SELECT Name, Category, ManifestFormatVersion, SourceKind, SourceURI").
		WithArgs(string(AppPack)).
		WillReturnRows(sqlmock.NewRows([]string{"Name", "Category", "ManifestFormatVersion", "SourceKind", "SourceURI"}).
			AddRow("jira-like-sample", string(AppPack), SupportedManifestFormatVersion, string(PackSourceKindLocalPath), sourceDir))

	updated, err := BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacks(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillObjectSpecExclusiveSiblingFieldPackOwnershipFromActivePacks = %v", err)
	}
	if updated != 0 {
		t.Fatalf("updated = %d, want 0", updated)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	updated, err := backfillObjectSpecExclusiveSiblingFieldPackOwnership(
		context.Background(),
		db,
		"jira-like-sample",
		"objects",
		ManageabilityDefaultsOnly,
		[]ObjectSpec{{APIName: "Parent__c"}},
	)
	if err != nil {
		t.Fatalf("backfillObjectSpecExclusiveSiblingFieldPackOwnership = %v", err)
	}
	if updated != 0 {
		t.Fatalf("updated = %d, want 0", updated)
	}
}

func writeExclusiveSiblingFieldOwnershipSource(t *testing.T) string {
	t.Helper()
	root := writePackFixture(t, "exclusive-sibling-ownership", validPackManifest(`[
    {
      "kind": "object_spec",
      "businessKey": "objects",
      "manageability": "locked",
      "path": "objects/objects.json"
    }
  ]`, `"dependencies": [],`))
	writeFile(t, root, "objects/objects.json", `[
  {
    "apiName": "Parent__c",
    "label": "Parent",
    "tableType": "custom_object",
    "sharingModel": "Private",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "isNameField": true}
    ]
  },
  {
    "apiName": "Status__c",
    "label": "Status",
    "tableType": "custom_object",
    "sharingModel": "Private",
    "exclusiveSiblingFields": [
      {"fieldApiName": "IsDefault__c", "partitionFieldApiName": "Parent__c"}
    ],
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "isNameField": true},
      {"apiName": "Parent__c", "label": "Parent", "type": "reference", "referenceTo": ["Parent__c"], "createable": true, "updateable": true},
      {"apiName": "IsDefault__c", "label": "Default", "type": "boolean", "createable": true, "updateable": true}
    ]
  }
]`)
	return root
}

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

	id := domainschema.GenerateObjectID("CampaignMemberStatus")
	selectSQL := regexp.QuoteMeta("SELECT `Id`, COALESCE(`ExclusiveSiblingFieldsCustomized`, 0), `ExclusiveSiblingFieldsPackName`, `ExclusiveSiblingFieldsPackBusinessKey`, `ExclusiveSiblingFieldsPackManageability`, `ExclusiveSiblingFieldsPackValue` FROM `_System_Object` WHERE `Id` IN (?) ORDER BY `Id`")
	mock.ExpectQuery(selectSQL).
		WithArgs(id).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ExclusiveSiblingFieldsCustomized", "ExclusiveSiblingFieldsPackName", "ExclusiveSiblingFieldsPackBusinessKey", "ExclusiveSiblingFieldsPackManageability", "ExclusiveSiblingFieldsPackValue"}).
			AddRow(id, 1, "salesforce-crm-standard", "objects", string(ManageabilityDefaultsOnly), `[]`))

	snapshot, err := snapshotObjectSpecExclusiveSiblingFieldPackOwnership(context.Background(), db, []string{id})
	if err != nil {
		t.Fatalf("snapshotObjectSpecExclusiveSiblingFieldPackOwnership = %v", err)
	}

	restoreSQL := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFieldsCustomized` = ?, `ExclusiveSiblingFieldsPackName` = ?, `ExclusiveSiblingFieldsPackBusinessKey` = ?, `ExclusiveSiblingFieldsPackManageability` = ?, `ExclusiveSiblingFieldsPackValue` = ?, `LastModifiedDate` = NOW() WHERE `Id` = ?")
	mock.ExpectExec(restoreSQL).
		WithArgs(1, "salesforce-crm-standard", "objects", string(ManageabilityDefaultsOnly), `[]`, id).
		WillReturnResult(sqlmock.NewResult(0, 1))

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

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

	id := domainschema.GenerateObjectID("CampaignMemberStatus")
	selectSQL := regexp.QuoteMeta("SELECT `Id`, COALESCE(`ExclusiveSiblingFieldsCustomized`, 0), `ExclusiveSiblingFieldsPackName`, `ExclusiveSiblingFieldsPackBusinessKey`, `ExclusiveSiblingFieldsPackManageability`, `ExclusiveSiblingFieldsPackValue` FROM `_System_Object` WHERE `Id` IN (?) ORDER BY `Id`")
	mock.ExpectQuery(selectSQL).
		WithArgs(id).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ExclusiveSiblingFieldsCustomized", "ExclusiveSiblingFieldsPackName", "ExclusiveSiblingFieldsPackBusinessKey", "ExclusiveSiblingFieldsPackManageability", "ExclusiveSiblingFieldsPackValue"}))

	snapshot, err := snapshotObjectSpecExclusiveSiblingFieldPackOwnership(context.Background(), db, []string{id})
	if err != nil {
		t.Fatalf("snapshotObjectSpecExclusiveSiblingFieldPackOwnership = %v", err)
	}

	restoreSQL := regexp.QuoteMeta("UPDATE `_System_Object` SET `ExclusiveSiblingFieldsCustomized` = ?, `ExclusiveSiblingFieldsPackName` = ?, `ExclusiveSiblingFieldsPackBusinessKey` = ?, `ExclusiveSiblingFieldsPackManageability` = ?, `ExclusiveSiblingFieldsPackValue` = ?, `LastModifiedDate` = NOW() WHERE `Id` = ?")
	mock.ExpectExec(restoreSQL).
		WithArgs(0, nil, nil, nil, nil, id).
		WillReturnResult(sqlmock.NewResult(0, 1))

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