package connector_callout

import (
	"context"
	"errors"
	"strings"
	"testing"

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

// alwaysActivePackResolver is a fake PackResolver that always reports the pack as active.
// Used in happy-path tests that do not exercise the pack-active gate.
type alwaysActivePackResolver struct{}

func (alwaysActivePackResolver) ResolveConnectorPackByNamespace(_ context.Context, _ string) (bool, error) {
	return true, nil
}

// inactivePackResolver is a fake PackResolver that always reports the pack as inactive.
type inactivePackResolver struct{}

func (inactivePackResolver) ResolveConnectorPackByNamespace(_ context.Context, _ string) (bool, error) {
	return false, nil
}

// errorPackResolver is a fake PackResolver that always returns an error.
type errorPackResolver struct{ err error }

func (r errorPackResolver) ResolveConnectorPackByNamespace(_ context.Context, _ string) (bool, error) {
	return false, r.err
}

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

	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "noop").
		WillReturnRows(sqlmock.NewRows([]string{
			"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
			"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
		}))

	_, err = LoadProfile(context.Background(), db, alwaysActivePackResolver{}, "test-pack", "noop")
	if !errors.Is(err, ErrProfileNotFound) {
		t.Fatalf("expected ErrProfileNotFound, got %v", err)
	}

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

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

	retryJSON := `{"MaxAttempts":3,"BaseDelay":100000000,"Multiplier":2,"MaxDelay":0,"RetryableStatusCodes":[429,503]}`
	pageJSON := `{"NextCursorJSONPath":"paging.next","CursorRequestParam":"after","PageSizeRequestParam":"limit","PageSizeValue":50}`
	batchJSON := `{"Method":"POST","RelativePath":"/batch","CredentialRef":"","EndpointClass":"","MaxBatchSize":100,"IDsRequestField":"ids","IDResponseField":"id","allowCanonicalIdAliases":true}`
	objMap := `{"contact":"/crm/v3/objects/contacts"}`

	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"test-pack", "default", "my-nc", "crm.objects.read",
		retryJSON, pageJSON, batchJSON, objMap, "test profile",
	)

	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "default").
		WillReturnRows(rows)

	p, err := LoadProfile(context.Background(), db, alwaysActivePackResolver{}, "test-pack", "default")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	if p.ProfileName != "default" {
		t.Errorf("ProfileName: want 'default', got %q", p.ProfileName)
	}
	if p.CredentialRef != "my-nc" {
		t.Errorf("CredentialRef: want 'my-nc', got %q", p.CredentialRef)
	}
	if p.EndpointClass != "crm.objects.read" {
		t.Errorf("EndpointClass: want 'crm.objects.read', got %q", p.EndpointClass)
	}
	if p.RetryPolicy.MaxAttempts != 3 {
		t.Errorf("RetryPolicy.MaxAttempts: want 3, got %d", p.RetryPolicy.MaxAttempts)
	}
	if len(p.RetryPolicy.RetryableStatusCodes) != 2 {
		t.Errorf("RetryPolicy.RetryableStatusCodes: want 2 codes, got %v", p.RetryPolicy.RetryableStatusCodes)
	}
	if p.PageSpec.PageSizeValue != 50 {
		t.Errorf("PageSpec.PageSizeValue: want 50, got %d", p.PageSpec.PageSizeValue)
	}
	if p.BatchSpec.MaxBatchSize != 100 {
		t.Errorf("BatchSpec.MaxBatchSize: want 100, got %d", p.BatchSpec.MaxBatchSize)
	}
	if !p.BatchSpec.AllowCanonicalIDAliases {
		t.Errorf("BatchSpec.AllowCanonicalIDAliases: want true")
	}
	if p.ObjectPathMap["contact"] != "/crm/v3/objects/contacts" {
		t.Errorf("ObjectPathMap[contact]: want '/crm/v3/objects/contacts', got %q", p.ObjectPathMap["contact"])
	}
	if p.Description != "test profile" {
		t.Errorf("Description: want 'test profile', got %q", p.Description)
	}

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

// TestLoadProfileFailsClosedOnInactivePack verifies that LoadProfile returns
// ErrProfileUnavailable when the owning pack is inactive (B16).
func TestLoadProfileFailsClosedOnInactivePack(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	retryJSON := `{"MaxAttempts":1}`
	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"test-pack", "default", "my-nc", "crm.read",
		retryJSON, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "default").
		WillReturnRows(rows)

	_, err = LoadProfile(context.Background(), db, inactivePackResolver{}, "test-pack", "default")
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable for inactive pack, got %v", err)
	}

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

// TestLoadProfileFailsClosedOnNilResolver verifies that LoadProfile returns
// ErrProfileUnavailable when packs is nil (B16).
func TestLoadProfileFailsClosedOnNilResolver(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	retryJSON := `{"MaxAttempts":1}`
	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"test-pack", "default", "my-nc", "crm.read",
		retryJSON, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "default").
		WillReturnRows(rows)

	_, err = LoadProfile(context.Background(), db, nil, "test-pack", "default")
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable for nil resolver, got %v", err)
	}

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

// TestLoadProfileSucceedsWithActivePack verifies that LoadProfile returns a
// profile when the owning pack is active (B16 happy path).
func TestLoadProfileSucceedsWithActivePack(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	retryJSON := `{"MaxAttempts":2}`
	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"test-pack", "default", "my-nc", "crm.read",
		retryJSON, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "default").
		WillReturnRows(rows)

	p, err := LoadProfile(context.Background(), db, alwaysActivePackResolver{}, "test-pack", "default")
	if err != nil {
		t.Fatalf("expected success with active pack, got %v", err)
	}
	if p.EndpointClass != "crm.read" {
		t.Errorf("EndpointClass: want 'crm.read', got %q", p.EndpointClass)
	}
	if p.RetryPolicy.MaxAttempts != 2 {
		t.Errorf("RetryPolicy.MaxAttempts: want 2, got %d", p.RetryPolicy.MaxAttempts)
	}

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

// TestLoadProfilePropagatesResolverError verifies that LoadProfile wraps and
// propagates errors returned by PackResolver (B16).
func TestLoadProfilePropagatesResolverError(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	retryJSON := `{"MaxAttempts":1}`
	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"test-pack", "default", "my-nc", "crm.read",
		retryJSON, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "default").
		WillReturnRows(rows)

	resolverErr := errors.New("io timeout")
	_, err = LoadProfile(context.Background(), db, errorPackResolver{err: resolverErr}, "test-pack", "default")
	if err == nil {
		t.Fatal("expected error from resolver propagation, got nil")
	}
	if !errors.Is(err, resolverErr) {
		t.Errorf("expected wrapped resolverErr, got %v", err)
	}
	if !strings.Contains(err.Error(), "resolve owner pack") {
		t.Errorf("expected 'resolve owner pack' in error message, got: %v", err)
	}

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

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("active", "hash", "hash"))
	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))

	rows := sqlmock.NewRows([]string{
		"InstalledPackId", "OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"install-active", "test-pack", "default", "my-nc", "crm.read",
		`{"MaxAttempts":1}`, `{}`, `{"Method":"POST","RelativePath":"/batch","CredentialRef":"my-nc","EndpointClass":"crm.read","MaxBatchSize":10,"IDsRequestField":"ids","IDResponseField":"id"}`, `{"contact":"/crm/v3/objects/contacts"}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(rows)

	profiles, err := LoadProfilesForInboundWorker(context.Background(), db, "test-pack", "install-active", BatchProfileReadActiveOnly)
	if err != nil {
		t.Fatalf("LoadProfilesForInboundWorker: %v", err)
	}
	if len(profiles) != 1 || profiles[0].ProfileName != "default" {
		t.Fatalf("profiles = %+v, want one default profile", profiles)
	}
	if profiles[0].OwnerPack != "test-pack" {
		t.Fatalf("profiles[0].OwnerPack = %q, want test-pack", profiles[0].OwnerPack)
	}
	if profiles[0].InstalledPackID != "install-active" {
		t.Fatalf("profiles[0].InstalledPackID = %q, want install-active", profiles[0].InstalledPackID)
	}
	if profiles[0].EndpointClass != "crm.read" {
		t.Fatalf("profiles[0].EndpointClass = %q, want crm.read", profiles[0].EndpointClass)
	}

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

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

	mock.ExpectQuery(`SELECT PackInstallId\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", connectorPackStatusActive).
		WillReturnRows(sqlmock.NewRows([]string{"PackInstallId"}).AddRow("install-active"))
	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
	rows := sqlmock.NewRows([]string{
		"InstalledPackId", "OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"install-active", "test-pack", "default", "my-nc", "crm.write",
		`{"MaxAttempts":1}`, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT c\.InstalledPackId, c\.OwnerPack, c\.ProfileName`).
		WithArgs("test-pack", "install-active", "default", connectorPackStatusActive).
		WillReturnRows(rows)

	profile, err := LoadActiveProfile(context.Background(), db, "test-pack", "default")
	if err != nil {
		t.Fatalf("LoadActiveProfile: %v", err)
	}
	if profile.InstalledPackID != "install-active" {
		t.Fatalf("InstalledPackID = %q, want install-active", profile.InstalledPackID)
	}
	if profile.CredentialRef != "my-nc" || profile.EndpointClass != "crm.write" {
		t.Fatalf("profile authority not threaded: credential=%q endpoint=%q", profile.CredentialRef, profile.EndpointClass)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("mock expectations not met: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT PackInstallId\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", connectorPackStatusActive).
		WillReturnRows(sqlmock.NewRows([]string{"PackInstallId"}).AddRow("install-active"))
	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	_, err = LoadActiveProfile(context.Background(), db, "test-pack", "default")
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable for install drift, got %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("mock expectations not met: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "generation-install-2").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))
	rows := sqlmock.NewRows([]string{
		"InstalledPackId", "OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"generation-install-2", "test-pack", "default", "my-nc", "crm.write",
		`{"MaxAttempts":1}`, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`(?s)FROM _System_ConnectorCalloutProfile c\s+WHERE c\.OwnerPack = \?\s+AND c\.InstalledPackId = \?\s+AND c\.ProfileName = \?`).
		WithArgs("test-pack", "generation-install-2", "default").
		WillReturnRows(rows)

	profile, err := LoadProfileForPackInstall(context.Background(), db, " test-pack ", " generation-install-2 ", " DEFAULT ")
	if err != nil {
		t.Fatalf("LoadProfileForPackInstall: %v", err)
	}
	if profile.InstalledPackID != "generation-install-2" || profile.CredentialRef != "my-nc" || profile.EndpointClass != "crm.write" {
		t.Fatalf("profile = %#v, want the caller-selected generation", profile)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("mock expectations not met: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "generation-install-2").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	_, err = LoadProfileForPackInstall(context.Background(), db, "test-pack", "generation-install-2", "default")
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable for install drift, got %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("mock expectations not met: %v", err)
	}
}

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("active", "hash", "hash"))
	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))

	_, err = LoadProfilesForInboundWorker(context.Background(), db, "test-pack", "install-active", BatchProfileReadActiveOnly)
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable for install drift, got %v", err)
	}

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

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-missing").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}))

	_, err = LoadProfilesForInboundWorker(context.Background(), db, "test-pack", "install-missing", BatchProfileReadActiveOnly)
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable, got %v", err)
	}

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

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("removing", "hash", "hash"))

	_, err = LoadProfilesForInboundWorker(context.Background(), db, "test-pack", "install-removing", BatchProfileReadActiveOnly)
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable, got %v", err)
	}

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

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("active", "old-hash", "new-hash"))

	_, err = LoadProfilesForInboundWorker(context.Background(), db, "test-pack", "install-active", BatchProfileReadActiveOnly)
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable, got %v", err)
	}

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

// BatchProfileReadAllowDrain still uses install-scoped rows; it only relaxes
// the active-status requirement for callers that can read non-active installs.
func TestLoadProfilesForInboundWorker_AllowDrainReadsLiveProfilesForGenericCallers(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("removing", "hash", "hash"))
	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))

	rows := sqlmock.NewRows([]string{
		"InstalledPackId", "OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"install-removing", "test-pack", "default", "my-nc", "crm.drain",
		`{"MaxAttempts":1}`, `{}`, `{"Method":"POST","RelativePath":"/batch","CredentialRef":"my-nc","EndpointClass":"crm.drain","MaxBatchSize":10,"IDsRequestField":"ids","IDResponseField":"id"}`, `{"contact":"/crm/v3/objects/contacts"}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(rows)

	profiles, err := LoadProfilesForInboundWorker(context.Background(), db, "test-pack", "install-removing", BatchProfileReadAllowDrain)
	if err != nil {
		t.Fatalf("LoadProfilesForInboundWorker: %v", err)
	}
	if len(profiles) != 1 {
		t.Fatalf("len(profiles) = %d, want 1", len(profiles))
	}
	if profiles[0].EndpointClass != "crm.drain" {
		t.Fatalf("profiles[0].EndpointClass = %q, want crm.drain", profiles[0].EndpointClass)
	}
	if profiles[0].BatchSpec.EndpointClass != "crm.drain" {
		t.Fatalf("profiles[0].BatchSpec.EndpointClass = %q, want crm.drain", profiles[0].BatchSpec.EndpointClass)
	}

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

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("removing", "hash", "hash"))
	mock.ExpectQuery(`SELECT COUNT\(\*\)\s+FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(0))

	rows := sqlmock.NewRows([]string{
		"InstalledPackId", "OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"install-removing", "test-pack", "default", "my-nc", "crm.drain",
		`{"MaxAttempts":1}`, `{}`, `{"Method":"POST","RelativePath":"/batch","CredentialRef":"my-nc","EndpointClass":"crm.drain","MaxBatchSize":10,"IDsRequestField":"ids","IDResponseField":"id"}`, `{"contact":"/crm/v3/objects/contacts"}`, "",
	)
	mock.ExpectQuery(`FROM _System_ConnectorCalloutProfile`).
		WithArgs("test-pack", "install-removing").
		WillReturnRows(rows)

	profiles, err := LoadProfilesForInboundWorkerDrainOnly(context.Background(), db, "test-pack", "install-removing")
	if err != nil {
		t.Fatalf("LoadProfilesForInboundWorkerDrainOnly: %v", err)
	}
	if len(profiles) != 1 {
		t.Fatalf("len(profiles) = %d, want 1", len(profiles))
	}
	if profiles[0].ProfileName != "default" || profiles[0].EndpointClass != "crm.drain" {
		t.Fatalf("profiles[0] = %+v, want default crm.drain", profiles[0])
	}

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

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

	mock.ExpectQuery(`SELECT Status,\s+COALESCE\(AppliedPayloadHash, ''\),\s+COALESCE\(DesiredPayloadHash, ''\)\s+FROM _System_InstalledPack`).
		WithArgs("test-pack", "install-active").
		WillReturnRows(sqlmock.NewRows([]string{"Status", "AppliedPayloadHash", "DesiredPayloadHash"}).AddRow("active", "hash", "hash"))

	_, err = LoadProfilesForInboundWorkerDrainOnly(context.Background(), db, "test-pack", "install-active")
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable, got %v", err)
	}

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

// namedPackResolver is a fake PackResolver that tracks which namespace was queried
// and returns active=true only when it matches the configured expected namespace.
type namedPackResolver struct {
	expectedNamespace string
	queriedNamespace  string
}

func (r *namedPackResolver) ResolveConnectorPackByNamespace(_ context.Context, namespace string) (bool, error) {
	r.queriedNamespace = namespace
	return namespace == r.expectedNamespace, nil
}

// TestLoadProfile_MixedCaseConnectorPackName_LoadsSuccessfully is a regression test
// for IANTHEREAL P1 on PR #1599: a connector pack with a mixed-case name (e.g.
// "Acme-Connector") must not be shadowed by case normalization.
//
// Before the fix, NewConnectorCalloutProfileComponent and upsertConnectorCalloutProfileDefinition
// stored OwnerPack as strings.ToLower(packName), and LoadProfile lowercased its ownerPack
// argument before querying. This meant LoadProfile passed "acme-connector" to
// ResolveConnectorPackByNamespace, which queried _System_InstalledPack.Name = ? with exact
// match — failing closed because the row was written verbatim as "Acme-Connector".
//
// After the fix, OwnerPack is stored verbatim (TrimSpace only) and LoadProfile passes the
// trimmed-but-case-preserved ownerPack to the resolver, so it can match the installed-pack
// row exactly.
func TestLoadProfile_MixedCaseConnectorPackName_LoadsSuccessfully(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Simulate a DB row written by a connector pack named "Acme-Connector":
	// OwnerPack is stored verbatim (TrimSpace, not ToLower) after the fix.
	retryJSON := `{"MaxAttempts":2,"BaseDelay":0,"Multiplier":1,"MaxDelay":0}`
	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"Acme-Connector", "default", "acme-nc", "acme.api",
		retryJSON, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("Acme-Connector", "default").
		WillReturnRows(rows)

	resolver := &namedPackResolver{expectedNamespace: "Acme-Connector"}
	p, err := LoadProfile(context.Background(), db, resolver, "Acme-Connector", "default")
	if err != nil {
		t.Fatalf("LoadProfile with mixed-case pack name: expected success, got %v", err)
	}
	if p.ProfileName != "default" {
		t.Errorf("ProfileName: want 'default', got %q", p.ProfileName)
	}
	if resolver.queriedNamespace != "Acme-Connector" {
		t.Errorf("resolver queried namespace: want 'Acme-Connector', got %q (case normalization regression)", resolver.queriedNamespace)
	}

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

// TestLoadProfile_NoMatchingInstalledPack_FailsClosed is a regression guard ensuring
// that a profile whose owning pack is inactive (or unavailable) still returns
// ErrProfileUnavailable — the pack-gate must remain closed even after the verbatim
// OwnerPack fix.
func TestLoadProfile_NoMatchingInstalledPack_FailsClosed(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	retryJSON := `{"MaxAttempts":1,"BaseDelay":0,"Multiplier":1,"MaxDelay":0}`
	rows := sqlmock.NewRows([]string{
		"OwnerPack", "ProfileName", "CredentialRef", "EndpointClass",
		"RetryPolicy", "PageSpec", "BatchSpec", "ObjectPathMap", "Description",
	}).AddRow(
		"Acme-Connector", "default", "acme-nc", "acme.api",
		retryJSON, `{}`, `{}`, `{}`, "",
	)
	mock.ExpectQuery(`SELECT .+ FROM _System_ConnectorCalloutProfile`).
		WithArgs("Acme-Connector", "default").
		WillReturnRows(rows)

	// inactivePackResolver always returns active=false, simulating a missing or
	// inactive installed-pack row — the pack gate must still close.
	_, err = LoadProfile(context.Background(), db, inactivePackResolver{}, "Acme-Connector", "default")
	if !errors.Is(err, ErrProfileUnavailable) {
		t.Fatalf("expected ErrProfileUnavailable when pack is inactive, got %v", err)
	}

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

// TestLoadProfileEndpointClassDrivesDo verifies that profile.EndpointClass
// and profile.CredentialRef are sufficient to build a CalloutRequest that
// drives Engine.Do — a unit-level proof of the profile completeness contract.
func TestLoadProfileEndpointClassDrivesDo(t *testing.T) {
	// Construct a profile with all required top-level facts only.
	profile := &ConnectorCalloutProfile{
		OwnerPack:     "test-pack",
		ProfileName:   "default",
		CredentialRef: "test-nc",
		EndpointClass: "crm.objects.read",
		RetryPolicy: RetryPolicy{
			MaxAttempts: 1,
			Multiplier:  1,
		},
	}

	// Verify that profile.EndpointClass + profile.CredentialRef are
	// sufficient to build a CalloutRequest that has a non-empty throttle classifier.
	req := CalloutRequest{
		Method:        "GET",
		CredentialRef: profile.CredentialRef,
		EndpointClass: profile.EndpointClass,
	}
	if req.EndpointClass == "" {
		t.Error("EndpointClass should be non-empty when derived from profile.EndpointClass")
	}
	if req.CredentialRef == "" {
		t.Error("CredentialRef should be non-empty when derived from profile.CredentialRef")
	}
}