package catalogsystem

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

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/go-sql-driver/mysql"
	systemauthorityv1 "github.com/pingcap-inc/nexuscrm-contract/pkg/systemlayoutbundleauthorityv1"
)

var (
	commandColumns = []string{
		"CommandId",
		"PrincipalId",
		"PrincipalAuditDigest",
		"RequestDigest",
		"Operation",
		"TargetBundleKeyVersion",
		"TargetSlotOwnerKind",
		"TargetSlotOwnerNamespace",
		"TargetLocalComponentKey",
		"DesiredValueVersion",
		"DesiredBundleKeyVersion",
		"DesiredSlotOwnerKind",
		"DesiredSlotOwnerNamespace",
		"DesiredLocalComponentKey",
		"DesiredState",
		"ExpectedPriorVariant",
		"ExpectedPriorDigest",
	}
	outcomeColumns = []string{
		"CommandId",
		"RequestDigest",
		"PrincipalId",
		"PrincipalAuditDigest",
		"Result",
		"OutcomeDigest",
	}
	currentColumns = []string{
		"BundleKeyVersion",
		"SlotOwnerKind",
		"SlotOwnerNamespace",
		"LocalComponentKey",
		"ValueVersion",
		"State",
		"ValueDigest",
		"LastCommandId",
	}
	historyColumns = []string{
		"BundleKeyVersion",
		"SlotOwnerKind",
		"SlotOwnerNamespace",
		"LocalComponentKey",
		"TransitionOrdinal",
		"CommandId",
		"ValueVersion",
		"State",
		"ValueDigest",
		"PriorValueDigest",
	}
)

func TestUnauthorizedCommandPerformsNoSQL(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("2")
	if _, err := writer.applyCommand(
		context.Background(),
		testCommand1,
		request,
	); !errors.Is(err, ErrUnauthorized) {
		t.Fatalf("applyCommand error = %v, want ErrUnauthorized", err)
	}
	assertSQLExpectations(t, mock)
}

func TestCreatePersistsCommandCurrentHistoryAndOutcomeAtomically(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)
	desired := mustSlotDigest(t, request.DesiredSlotValue)
	expectedOutcome := mustOutcome(t, command, ResultApplied)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand1)
	expectInsertCommand(mock, command, nil)
	expectCurrentMissing(mock, request.TargetBundleKey)
	expectHistoryMissing(mock, request.TargetBundleKey)
	mock.ExpectExec(regexp.QuoteMeta("SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectInsertCurrent(mock, command, desired, nil)
	expectInsertHistory(mock, command, 1, desired, nil, nil)
	mock.ExpectExec(regexp.QuoteMeta("RELEASE SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectInsertOutcome(mock, expectedOutcome, nil)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(context.Background(), testCommand1, request)
	if err != nil {
		t.Fatal(err)
	}
	if outcome != publicOutcome(expectedOutcome) {
		t.Fatalf("outcome = %#v, want %#v", outcome, publicOutcome(expectedOutcome))
	}
	assertSQLExpectations(t, mock)
}

func TestExactRetryReturnsOutcomeBeforeCurrentOrHistoryRead(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)
	expectedOutcome := mustOutcome(t, command, ResultApplied)

	expectAuthorityBegin(mock)
	expectCommand(mock, command)
	expectOutcome(mock, expectedOutcome)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(context.Background(), testCommand1, request)
	if err != nil {
		t.Fatal(err)
	}
	if outcome != publicOutcome(expectedOutcome) {
		t.Fatalf("outcome = %#v, want %#v", outcome, publicOutcome(expectedOutcome))
	}
	assertSQLExpectations(t, mock)
}

func TestExactFailureRetryAlsoReturnsBeforeCurrentOrHistoryRead(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)
	expectedOutcome := mustOutcome(t, command, ResultAuthorityStateConflict)

	expectAuthorityBegin(mock)
	expectCommand(mock, command)
	expectOutcome(mock, expectedOutcome)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(context.Background(), testCommand1, request)
	if err != nil {
		t.Fatal(err)
	}
	if outcome != publicOutcome(expectedOutcome) {
		t.Fatalf("outcome = %#v, want %#v", outcome, publicOutcome(expectedOutcome))
	}
	assertSQLExpectations(t, mock)
}

func TestChangedPrincipalConflictsBeforeOutcomeOrStateRead(t *testing.T) {
	otherPrincipal := authenticatedPrincipal{
		PrincipalID: "other-system-slot-writer",
		AuditDigest: testDigest("b"),
	}
	writer, mock, cleanup := newMockWriterWithPrincipal(
		t,
		otherPrincipal,
		[]writerGrant{{
			BundleKey:   testBundleKey("1"),
			AllowCreate: true,
		}},
	)
	defer cleanup()
	request := testCreateRequest("1")
	stored := testDurableCommand(t, testCommand1, request, testPrincipal)

	expectAuthorityBegin(mock)
	expectCommand(mock, stored)
	expectAuthorityRollback(mock)

	if _, err := writer.applyCommand(
		context.Background(),
		testCommand1,
		request,
	); !errors.Is(err, ErrConflict) {
		t.Fatalf("applyCommand error = %v, want ErrConflict", err)
	}
	assertSQLExpectations(t, mock)
}

func TestChangedRequestConflictsBeforeOutcomeOrStateRead(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
		AllowRetire: true,
	}})
	defer cleanup()
	storedRequest := testCreateRequest("1")
	stored := testDurableCommand(t, testCommand1, storedRequest, testPrincipal)
	changedRequest := testRetireRequest(t, "1")

	expectAuthorityBegin(mock)
	expectCommand(mock, stored)
	expectAuthorityRollback(mock)

	if _, err := writer.applyCommand(
		context.Background(),
		testCommand1,
		changedRequest,
	); !errors.Is(err, ErrConflict) {
		t.Fatalf("applyCommand error = %v, want ErrConflict", err)
	}
	assertSQLExpectations(t, mock)
}

func TestCreateHistoryResidueCommitsOnlyTerminalConflict(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)
	expectedOutcome := mustOutcome(t, command, ResultAuthorityStateConflict)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand1)
	expectInsertCommand(mock, command, nil)
	expectCurrentMissing(mock, request.TargetBundleKey)
	expectHistoryPresent(mock, request.TargetBundleKey)
	expectInsertOutcome(mock, expectedOutcome, nil)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(context.Background(), testCommand1, request)
	if err != nil {
		t.Fatal(err)
	}
	if outcome.Result != ResultAuthorityStateConflict {
		t.Fatalf("result = %q, want %q", outcome.Result, ResultAuthorityStateConflict)
	}
	assertSQLExpectations(t, mock)
}

func TestCreateDuplicateHistoryRollsBackStateAndCommitsConflictOutcome(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)
	desired := mustSlotDigest(t, request.DesiredSlotValue)
	expectedOutcome := mustOutcome(t, command, ResultAuthorityStateConflict)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand1)
	expectInsertCommand(mock, command, nil)
	expectCurrentMissing(mock, request.TargetBundleKey)
	expectHistoryMissing(mock, request.TargetBundleKey)
	mock.ExpectExec(regexp.QuoteMeta("SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectInsertCurrent(mock, command, desired, nil)
	expectInsertHistory(
		mock,
		command,
		1,
		desired,
		nil,
		&mysql.MySQLError{Number: 1062, Message: "duplicate history"},
	)
	mock.ExpectExec(regexp.QuoteMeta("ROLLBACK TO SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta("RELEASE SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectInsertOutcome(mock, expectedOutcome, nil)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(context.Background(), testCommand1, request)
	if err != nil {
		t.Fatal(err)
	}
	if outcome.Result != ResultAuthorityStateConflict {
		t.Fatalf("result = %q, want %q", outcome.Result, ResultAuthorityStateConflict)
	}
	assertSQLExpectations(t, mock)
}

func TestRetireValidatesCreationLineageAndPersistsOrdinalTwo(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowRetire: true,
	}})
	defer cleanup()
	createRequest := testCreateRequest("1")
	createCommand := testDurableCommand(t, testCommand1, createRequest, testPrincipal)
	createOutcome := mustOutcome(t, createCommand, ResultApplied)
	activeDigest := mustSlotDigest(t, createRequest.DesiredSlotValue)
	retireRequest := testRetireRequest(t, "1")
	retireCommand := testDurableCommand(t, testCommand2, retireRequest, testPrincipal)
	tombstoneDigest := mustSlotDigest(t, retireRequest.DesiredSlotValue)
	retireOutcome := mustOutcome(t, retireCommand, ResultApplied)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand2)
	expectInsertCommand(mock, retireCommand, nil)
	expectCurrent(
		mock,
		createRequest.DesiredSlotValue,
		activeDigest,
		testCommand1,
	)
	expectHistory(mock, historyEntry{
		CommandID:         testCommand1,
		TransitionOrdinal: 1,
		Value:             createRequest.DesiredSlotValue,
		Digest:            activeDigest,
	})
	expectCommand(mock, createCommand)
	expectOutcome(mock, createOutcome)
	mock.ExpectExec(regexp.QuoteMeta("SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectRetireCurrent(
		mock,
		retireCommand,
		tombstoneDigest,
		activeDigest,
		testCommand1,
		nil,
	)
	expectInsertHistory(
		mock,
		retireCommand,
		2,
		tombstoneDigest,
		&activeDigest,
		nil,
	)
	mock.ExpectExec(regexp.QuoteMeta("RELEASE SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectInsertOutcome(mock, retireOutcome, nil)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(
		context.Background(),
		testCommand2,
		retireRequest,
	)
	if err != nil {
		t.Fatal(err)
	}
	if outcome != publicOutcome(retireOutcome) {
		t.Fatalf("outcome = %#v, want %#v", outcome, publicOutcome(retireOutcome))
	}
	assertSQLExpectations(t, mock)
}

func TestRetireDuplicateHistoryCommitsConflictWithoutMutation(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowRetire: true,
	}})
	defer cleanup()
	createRequest := testCreateRequest("1")
	activeDigest := mustSlotDigest(t, createRequest.DesiredSlotValue)
	retireRequest := testRetireRequest(t, "1")
	retireCommand := testDurableCommand(t, testCommand2, retireRequest, testPrincipal)
	tombstoneDigest := mustSlotDigest(t, retireRequest.DesiredSlotValue)
	expectedOutcome := mustOutcome(t, retireCommand, ResultAuthorityStateConflict)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand2)
	expectInsertCommand(mock, retireCommand, nil)
	expectCurrent(
		mock,
		createRequest.DesiredSlotValue,
		activeDigest,
		testCommand1,
	)
	expectHistory(
		mock,
		historyEntry{
			CommandID:         testCommand1,
			TransitionOrdinal: 1,
			Value:             createRequest.DesiredSlotValue,
			Digest:            activeDigest,
		},
		historyEntry{
			CommandID:         testCommand3,
			TransitionOrdinal: 2,
			Value:             retireRequest.DesiredSlotValue,
			Digest:            tombstoneDigest,
			PriorDigest: sql.NullString{
				String: activeDigest,
				Valid:  true,
			},
		},
	)
	expectInsertOutcome(mock, expectedOutcome, nil)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(
		context.Background(),
		testCommand2,
		retireRequest,
	)
	if err != nil {
		t.Fatal(err)
	}
	if outcome.Result != ResultAuthorityStateConflict {
		t.Fatalf("result = %q, want %q", outcome.Result, ResultAuthorityStateConflict)
	}
	assertSQLExpectations(t, mock)
}

func TestRetireMissingCurrentCommitsTerminalPriorMismatch(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowRetire: true,
	}})
	defer cleanup()
	request := testRetireRequest(t, "1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)
	expectedOutcome := mustOutcome(t, command, ResultExpectedPriorMismatch)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand1)
	expectInsertCommand(mock, command, nil)
	expectCurrentMissing(mock, request.TargetBundleKey)
	expectInsertOutcome(mock, expectedOutcome, nil)
	expectAuthorityCommit(mock)

	outcome, err := writer.applyCommand(context.Background(), testCommand1, request)
	if err != nil {
		t.Fatal(err)
	}
	if outcome.Result != ResultExpectedPriorMismatch {
		t.Fatalf("result = %q, want %q", outcome.Result, ResultExpectedPriorMismatch)
	}
	assertSQLExpectations(t, mock)
}

func TestTransitionStorageFailureRollsBackCommandAndState(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	request := testCreateRequest("1")
	command := testDurableCommand(t, testCommand1, request, testPrincipal)

	expectAuthorityBegin(mock)
	expectCommandMissing(mock, testCommand1)
	expectInsertCommand(mock, command, nil)
	expectCurrentMissing(mock, request.TargetBundleKey)
	expectHistoryMissing(mock, request.TargetBundleKey)
	mock.ExpectExec(regexp.QuoteMeta("SAVEPOINT " + transitionSavepoint)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectInsertCurrent(
		mock,
		command,
		mustSlotDigest(t, request.DesiredSlotValue),
		errors.New("storage unavailable"),
	)
	expectAuthorityRollback(mock)

	if _, err := writer.applyCommand(
		context.Background(),
		testCommand1,
		request,
	); err == nil {
		t.Fatal("storage failure was accepted")
	}
	assertSQLExpectations(t, mock)
}

func TestFailedGlobalFenceDoesNotBeginTransaction(t *testing.T) {
	writer, mock, cleanup := newMockWriter(t, []writerGrant{{
		BundleKey:   testBundleKey("1"),
		AllowCreate: true,
	}})
	defer cleanup()
	expectAuthoritySessionPessimistic(mock)
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT GET_LOCK('" + authorityLockName + "', 600)",
	)).WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(0))
	expectAuthoritySessionRestore(mock)

	if _, err := writer.applyCommand(
		context.Background(),
		testCommand1,
		testCreateRequest("1"),
	); !errors.Is(err, ErrConflict) {
		t.Fatalf("applyCommand error = %v, want ErrConflict", err)
	}
	assertSQLExpectations(t, mock)
}

func newMockWriter(
	t *testing.T,
	grants []writerGrant,
) (*systemSlotWriter, sqlmock.Sqlmock, func()) {
	t.Helper()
	return newMockWriterWithPrincipal(t, testPrincipal, grants)
}

func newMockWriterWithPrincipal(
	t *testing.T,
	principal authenticatedPrincipal,
	grants []writerGrant,
) (*systemSlotWriter, sqlmock.Sqlmock, func()) {
	t.Helper()
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	authority, err := New(db)
	if err != nil {
		_ = db.Close()
		t.Fatal(err)
	}
	writer, err := authority.bindAuthenticatedWriter(principal, grants)
	if err != nil {
		_ = db.Close()
		t.Fatal(err)
	}
	return writer, mock, func() {
		mock.ExpectClose()
		if err := db.Close(); err != nil {
			t.Errorf("close mock database: %v", err)
		}
	}
}

func expectAuthorityBegin(mock sqlmock.Sqlmock) {
	expectAuthoritySessionPessimistic(mock)
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT GET_LOCK('" + authorityLockName + "', 600)",
	)).WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	mock.ExpectBegin()
}

func expectAuthorityCommit(mock sqlmock.Sqlmock) {
	mock.ExpectCommit()
	expectAuthorityRelease(mock)
	expectAuthoritySessionRestore(mock)
}

func expectAuthorityRollback(mock sqlmock.Sqlmock) {
	mock.ExpectRollback()
	expectAuthorityRelease(mock)
	expectAuthoritySessionRestore(mock)
}

func expectAuthorityRelease(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT RELEASE_LOCK('" + authorityLockName + "')",
	)).WillReturnRows(sqlmock.NewRows([]string{"RELEASE_LOCK"}).AddRow(1))
}

func expectAuthoritySessionPessimistic(mock sqlmock.Sqlmock) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT @@SESSION.tidb_txn_mode",
	)).WillReturnRows(
		sqlmock.NewRows([]string{"tidb_txn_mode"}).AddRow("optimistic"),
	)
	mock.ExpectExec(regexp.QuoteMeta(
		"SET SESSION tidb_txn_mode = 'pessimistic'",
	)).WillReturnResult(sqlmock.NewResult(0, 0))
}

func expectAuthoritySessionRestore(mock sqlmock.Sqlmock) {
	mock.ExpectExec(regexp.QuoteMeta(
		"SET SESSION tidb_txn_mode = ?",
	)).WithArgs("optimistic").WillReturnResult(sqlmock.NewResult(0, 0))
}

func expectCommandMissing(
	mock sqlmock.Sqlmock,
	commandID systemauthorityv1.CommandID,
) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"FROM _System_CatalogSystemLayoutBundleSlotCommand WHERE CommandId = ? FOR UPDATE",
	)).WithArgs(string(commandID)).
		WillReturnRows(sqlmock.NewRows(commandColumns))
}

func expectCommand(mock sqlmock.Sqlmock, command durableCommand) {
	request := command.Request
	mock.ExpectQuery(regexp.QuoteMeta(
		"FROM _System_CatalogSystemLayoutBundleSlotCommand WHERE CommandId = ? FOR UPDATE",
	)).WithArgs(string(command.CommandID)).
		WillReturnRows(sqlmock.NewRows(commandColumns).AddRow(
			string(command.CommandID),
			command.Principal.PrincipalID,
			command.Principal.AuditDigest,
			command.RequestDigest,
			string(request.Operation),
			request.TargetBundleKey.BundleKeyVersion,
			string(request.TargetBundleKey.SlotOwnerKind),
			request.TargetBundleKey.SlotOwnerNamespace,
			string(request.TargetBundleKey.LocalComponentKey),
			request.DesiredSlotValue.ValueVersion,
			request.DesiredSlotValue.BundleKey.BundleKeyVersion,
			string(request.DesiredSlotValue.BundleKey.SlotOwnerKind),
			request.DesiredSlotValue.BundleKey.SlotOwnerNamespace,
			string(request.DesiredSlotValue.BundleKey.LocalComponentKey),
			string(request.DesiredSlotValue.State),
			string(request.ExpectedPrior.Variant),
			nullableDigest(request.ExpectedPrior.SlotValueDigest),
		))
}

func expectInsertCommand(
	mock sqlmock.Sqlmock,
	command durableCommand,
	execErr error,
) {
	request := command.Request
	expectation := mock.ExpectExec(regexp.QuoteMeta(
		"INSERT INTO _System_CatalogSystemLayoutBundleSlotCommand",
	)).WithArgs(
		string(command.CommandID),
		command.Principal.PrincipalID,
		command.Principal.AuditDigest,
		command.RequestDigest,
		string(request.Operation),
		request.TargetBundleKey.BundleKeyVersion,
		string(request.TargetBundleKey.SlotOwnerKind),
		request.TargetBundleKey.SlotOwnerNamespace,
		string(request.TargetBundleKey.LocalComponentKey),
		request.DesiredSlotValue.ValueVersion,
		request.DesiredSlotValue.BundleKey.BundleKeyVersion,
		string(request.DesiredSlotValue.BundleKey.SlotOwnerKind),
		request.DesiredSlotValue.BundleKey.SlotOwnerNamespace,
		string(request.DesiredSlotValue.BundleKey.LocalComponentKey),
		string(request.DesiredSlotValue.State),
		string(request.ExpectedPrior.Variant),
		nullableDigest(request.ExpectedPrior.SlotValueDigest),
	)
	if execErr != nil {
		expectation.WillReturnError(execErr)
	} else {
		expectation.WillReturnResult(sqlmock.NewResult(1, 1))
	}
}

func expectOutcome(mock sqlmock.Sqlmock, outcome durableOutcome) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"FROM _System_CatalogSystemLayoutBundleSlotCommandOutcome WHERE CommandId = ? FOR UPDATE",
	)).WithArgs(string(outcome.CommandID)).
		WillReturnRows(sqlmock.NewRows(outcomeColumns).AddRow(
			string(outcome.CommandID),
			outcome.RequestDigest,
			outcome.Principal.PrincipalID,
			outcome.Principal.AuditDigest,
			string(outcome.Result),
			outcome.OutcomeDigest,
		))
}

func expectInsertOutcome(
	mock sqlmock.Sqlmock,
	outcome durableOutcome,
	execErr error,
) {
	expectation := mock.ExpectExec(regexp.QuoteMeta(
		"INSERT INTO _System_CatalogSystemLayoutBundleSlotCommandOutcome",
	)).WithArgs(
		string(outcome.CommandID),
		outcome.RequestDigest,
		outcome.Principal.PrincipalID,
		outcome.Principal.AuditDigest,
		string(outcome.Result),
		outcome.OutcomeDigest,
	)
	if execErr != nil {
		expectation.WillReturnError(execErr)
	} else {
		expectation.WillReturnResult(sqlmock.NewResult(1, 1))
	}
}

func expectCurrentMissing(
	mock sqlmock.Sqlmock,
	key systemauthorityv1.BundleKey,
) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"FROM _System_CatalogSystemLayoutBundleSlot WHERE BundleKeyVersion = ?",
	)).WithArgs(
		key.BundleKeyVersion,
		string(key.SlotOwnerKind),
		key.SlotOwnerNamespace,
		string(key.LocalComponentKey),
	).WillReturnRows(sqlmock.NewRows(currentColumns))
}

func expectCurrent(
	mock sqlmock.Sqlmock,
	value systemauthorityv1.SystemLayoutBundleSlotV1,
	digest string,
	lastCommandID systemauthorityv1.CommandID,
) {
	key := value.BundleKey
	mock.ExpectQuery(regexp.QuoteMeta(
		"FROM _System_CatalogSystemLayoutBundleSlot WHERE BundleKeyVersion = ?",
	)).WithArgs(
		key.BundleKeyVersion,
		string(key.SlotOwnerKind),
		key.SlotOwnerNamespace,
		string(key.LocalComponentKey),
	).WillReturnRows(sqlmock.NewRows(currentColumns).AddRow(
		key.BundleKeyVersion,
		string(key.SlotOwnerKind),
		key.SlotOwnerNamespace,
		string(key.LocalComponentKey),
		value.ValueVersion,
		string(value.State),
		digest,
		string(lastCommandID),
	))
}

func expectHistoryMissing(
	mock sqlmock.Sqlmock,
	key systemauthorityv1.BundleKey,
) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT TransitionOrdinal FROM _System_CatalogSystemLayoutBundleSlotHistory",
	)).WithArgs(
		key.BundleKeyVersion,
		string(key.SlotOwnerKind),
		key.SlotOwnerNamespace,
		string(key.LocalComponentKey),
	).WillReturnRows(sqlmock.NewRows([]string{"TransitionOrdinal"}))
}

func expectHistoryPresent(
	mock sqlmock.Sqlmock,
	key systemauthorityv1.BundleKey,
) {
	mock.ExpectQuery(regexp.QuoteMeta(
		"SELECT TransitionOrdinal FROM _System_CatalogSystemLayoutBundleSlotHistory",
	)).WithArgs(
		key.BundleKeyVersion,
		string(key.SlotOwnerKind),
		key.SlotOwnerNamespace,
		string(key.LocalComponentKey),
	).WillReturnRows(
		sqlmock.NewRows([]string{"TransitionOrdinal"}).AddRow(1),
	)
}

func expectHistory(mock sqlmock.Sqlmock, entries ...historyEntry) {
	if len(entries) == 0 {
		panic("expectHistory needs at least one entry")
	}
	key := entries[0].Value.BundleKey
	rows := sqlmock.NewRows(historyColumns)
	for _, entry := range entries {
		rows.AddRow(
			entry.Value.BundleKey.BundleKeyVersion,
			string(entry.Value.BundleKey.SlotOwnerKind),
			entry.Value.BundleKey.SlotOwnerNamespace,
			string(entry.Value.BundleKey.LocalComponentKey),
			entry.TransitionOrdinal,
			string(entry.CommandID),
			entry.Value.ValueVersion,
			string(entry.Value.State),
			entry.Digest,
			nullableSQLString(entry.PriorDigest),
		)
	}
	mock.ExpectQuery(regexp.QuoteMeta(
		"FROM _System_CatalogSystemLayoutBundleSlotHistory WHERE BundleKeyVersion = ?",
	)).WithArgs(
		key.BundleKeyVersion,
		string(key.SlotOwnerKind),
		key.SlotOwnerNamespace,
		string(key.LocalComponentKey),
	).WillReturnRows(rows)
}

func expectInsertCurrent(
	mock sqlmock.Sqlmock,
	command durableCommand,
	digest string,
	execErr error,
) {
	value := command.Request.DesiredSlotValue
	expectation := mock.ExpectExec(regexp.QuoteMeta(
		"INSERT INTO _System_CatalogSystemLayoutBundleSlot",
	)).WithArgs(
		value.BundleKey.BundleKeyVersion,
		string(value.BundleKey.SlotOwnerKind),
		value.BundleKey.SlotOwnerNamespace,
		string(value.BundleKey.LocalComponentKey),
		value.ValueVersion,
		string(value.State),
		digest,
		string(command.CommandID),
	)
	if execErr != nil {
		expectation.WillReturnError(execErr)
	} else {
		expectation.WillReturnResult(sqlmock.NewResult(1, 1))
	}
}

func expectRetireCurrent(
	mock sqlmock.Sqlmock,
	command durableCommand,
	desiredDigest string,
	currentDigest string,
	currentCommandID systemauthorityv1.CommandID,
	execErr error,
) {
	value := command.Request.DesiredSlotValue
	expectation := mock.ExpectExec(regexp.QuoteMeta(
		"UPDATE _System_CatalogSystemLayoutBundleSlot SET ValueVersion = ?",
	)).WithArgs(
		value.ValueVersion,
		string(value.State),
		desiredDigest,
		string(command.CommandID),
		value.BundleKey.BundleKeyVersion,
		string(value.BundleKey.SlotOwnerKind),
		value.BundleKey.SlotOwnerNamespace,
		string(value.BundleKey.LocalComponentKey),
		currentDigest,
		string(currentCommandID),
	)
	if execErr != nil {
		expectation.WillReturnError(execErr)
	} else {
		expectation.WillReturnResult(sqlmock.NewResult(0, 1))
	}
}

func expectInsertHistory(
	mock sqlmock.Sqlmock,
	command durableCommand,
	ordinal uint8,
	digest string,
	priorDigest *string,
	execErr error,
) {
	value := command.Request.DesiredSlotValue
	var prior any
	if priorDigest != nil {
		prior = *priorDigest
	}
	expectation := mock.ExpectExec(regexp.QuoteMeta(
		"INSERT INTO _System_CatalogSystemLayoutBundleSlotHistory",
	)).WithArgs(
		value.BundleKey.BundleKeyVersion,
		string(value.BundleKey.SlotOwnerKind),
		value.BundleKey.SlotOwnerNamespace,
		string(value.BundleKey.LocalComponentKey),
		ordinal,
		string(command.CommandID),
		value.ValueVersion,
		string(value.State),
		digest,
		prior,
	)
	if execErr != nil {
		expectation.WillReturnError(execErr)
	} else {
		expectation.WillReturnResult(sqlmock.NewResult(1, 1))
	}
}

func testDurableCommand(
	t *testing.T,
	commandID systemauthorityv1.CommandID,
	request systemauthorityv1.SystemLayoutBundleSlotCommandRequest,
	principal authenticatedPrincipal,
) durableCommand {
	t.Helper()
	return durableCommand{
		CommandID:     commandID,
		Principal:     principal,
		Request:       request,
		RequestDigest: mustRequestDigest(t, request),
	}
}

func mustOutcome(
	t *testing.T,
	command durableCommand,
	result Result,
) durableOutcome {
	t.Helper()
	outcome, err := completeOutcome(command, result)
	if err != nil {
		t.Fatal(err)
	}
	return outcome
}

func assertSQLExpectations(t *testing.T, mock sqlmock.Sqlmock) {
	t.Helper()
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}