package bootstrapimpl

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

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

// TestFlowAuditLogReadGatePinned_CorrectValue verifies that the assertion
// passes when _System_FlowAuditLog carries the canonical pinned permission.
func TestFlowAuditLogReadGatePinned_CorrectValue(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_FlowAuditLog",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: flowAuditLogRequiredReadPermission,
		},
	}
	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

// TestFlowAuditLogReadGatePinned_ViewSetupDenied verifies that "ViewSetup" —
// the pre-#503 value — triggers an error-severity violation.
func TestFlowAuditLogReadGatePinned_ViewSetupDenied(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_FlowAuditLog",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: "ViewSetup",
		},
	}
	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for ViewSetup, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
	if v.Category != "AuditReadGate" {
		t.Errorf("want category AuditReadGate, got %q", v.Category)
	}
}

// TestFlowAuditLogReadGatePinned_EmptyPermissionDenied verifies that an empty
// requiredReadPermission is also caught (would open the table to all authenticated users).
func TestFlowAuditLogReadGatePinned_EmptyPermissionDenied(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_FlowAuditLog",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: "",
		},
	}
	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for empty permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

// TestFlowAuditLogReadGatePinned_MissingTable verifies that a missing
// _System_FlowAuditLog row itself is flagged as an error-severity violation.
func TestFlowAuditLogReadGatePinned_MissingTable(t *testing.T) {
	defs := []schema.TableDefinition{
		{TableName: "_System_SomeOtherTable", TableType: string(constants.TableTypeSystemCore)},
	}
	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for missing table, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
}

// TestFlowAuditLogReadGatePinned_LiveSpec verifies the assertion passes
// against the actual embedded system_tables.json — the same check the server
// runs on startup via RunAssertions.
func TestFlowAuditLogReadGatePinned_LiveSpec(t *testing.T) {
	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionPinned(GetSystemTableDefinitions(), result)
	if len(result.Violations) != 0 {
		t.Fatalf("live system_tables.json fails FlowAuditLog read-gate pin: %+v",
			result.Violations)
	}
}

// --- Live-DB assertion tests (assertFlowAuditLogReadPermissionLiveDB) ---

// liveDBQuery is the regexp pattern for the SELECT issued by the live-DB assertion.
// Uses QuoteMeta on the literal because backtick-quoting makes the raw string
// awkward to match; the sqlmock default uses regexp matching.
var liveDBQuery = regexp.QuoteMeta(
	"SELECT `RequiredReadPermission` FROM `_System_Object` WHERE `ApiName` = ? LIMIT 1",
)

// TestFlowAuditLogReadGateLiveDB_CorrectValue — DB row has the canonical
// value → no violations.
func TestFlowAuditLogReadGateLiveDB_CorrectValue(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_FlowAuditLog").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow(flowAuditLogRequiredReadPermission))

	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct DB value, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestFlowAuditLogReadGateLiveDB_ViewSetupDrifted — DB row carries the
// pre-#503 value "ViewSetup". This is the primary drift case IANTHEREAL
// described: runtime gate reads this stale value and serves audit rows to
// setup-only users. Must produce an error-severity violation.
func TestFlowAuditLogReadGateLiveDB_ViewSetupDrifted(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_FlowAuditLog").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow("ViewSetup"))

	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionLiveDB(db, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for drifted ViewSetup, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
	if v.Category != "AuditReadGate" {
		t.Errorf("want category AuditReadGate, got %q", v.Category)
	}
}

// TestFlowAuditLogReadGateLiveDB_RowMissing — fresh DB (row not yet seeded by
// ApplyAll). The assertion must SKIP (return 0 violations) so that a brand-new
// deployment can start up and run its first ApplyAll. Failing here would create
// a bootstrapping deadlock.
func TestFlowAuditLogReadGateLiveDB_RowMissing(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_FlowAuditLog").
		WillReturnError(sql.ErrNoRows)

	result := &AssertionResult{}
	assertFlowAuditLogReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for missing row (fresh DB), got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// --- _System_AuditLog parse-time assertion tests ---

// TestAuditLogReadGatePinned_CorrectValue verifies that the assertion
// passes when _System_AuditLog carries the canonical pinned permission.
func TestAuditLogReadGatePinned_CorrectValue(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_AuditLog",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: auditLogRequiredReadPermission,
		},
	}
	result := &AssertionResult{}
	assertAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

// TestAuditReadGatePinned_ViewSetupDenied verifies that "ViewSetup" triggers an
// error-severity violation for both audit surfaces (two tables, same gate logic).
func TestAuditReadGatePinned_ViewSetupDenied(t *testing.T) {
	tests := []struct {
		name      string
		tableName string
		assert    func([]schema.TableDefinition, *AssertionResult)
	}{
		{"AuditLog", "_System_AuditLog", assertAuditLogReadPermissionPinned},
		{"SetupAuditTrail", "_System_SetupAuditTrail", assertSetupAuditTrailReadPermissionPinned},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			defs := []schema.TableDefinition{
				{
					TableName:              tc.tableName,
					TableType:              string(constants.TableTypeSystemCore),
					RequiredReadPermission: "ViewSetup",
				},
			}
			result := &AssertionResult{}
			tc.assert(defs, result)
			if len(result.Violations) != 1 {
				t.Fatalf("expected 1 violation for ViewSetup, got %d: %+v",
					len(result.Violations), result.Violations)
			}
			v := result.Violations[0]
			if v.Severity != constants.SeverityError {
				t.Errorf("want SeverityError, got %q", v.Severity)
			}
			if v.Category != "AuditReadGate" {
				t.Errorf("want category AuditReadGate, got %q", v.Category)
			}
		})
	}
}

// TestAuditLogReadGatePinned_EmptyPermissionDenied verifies that an empty
// requiredReadPermission is also caught (would open the table to all users).
func TestAuditLogReadGatePinned_EmptyPermissionDenied(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_AuditLog",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: "",
		},
	}
	result := &AssertionResult{}
	assertAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for empty permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

// TestAuditLogReadGatePinned_MissingTable verifies that a missing
// _System_AuditLog entry in defs is flagged as an error-severity violation.
func TestAuditLogReadGatePinned_MissingTable(t *testing.T) {
	defs := []schema.TableDefinition{
		{TableName: "_System_SomeOtherTable", TableType: string(constants.TableTypeSystemCore)},
	}
	result := &AssertionResult{}
	assertAuditLogReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for missing table, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
}

// TestAuditLogReadGatePinned_LiveSpec verifies the assertion passes
// against the actual embedded system_tables.json.
func TestAuditLogReadGatePinned_LiveSpec(t *testing.T) {
	result := &AssertionResult{}
	assertAuditLogReadPermissionPinned(GetSystemTableDefinitions(), result)
	if len(result.Violations) != 0 {
		t.Fatalf("live system_tables.json fails AuditLog read-gate pin: %+v",
			result.Violations)
	}
}

// --- _System_AuditLog live-DB assertion tests ---

// TestAuditLogReadGateLiveDB_CorrectValue — DB row has the canonical
// value → no violations.
func TestAuditLogReadGateLiveDB_CorrectValue(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_AuditLog").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow(auditLogRequiredReadPermission))

	result := &AssertionResult{}
	assertAuditLogReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct DB value, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestAuditReadGateLiveDB_ViewSetupDrifted — DB row carries "ViewSetup".
// Must produce an error-severity violation for both audit surfaces.
func TestAuditReadGateLiveDB_ViewSetupDrifted(t *testing.T) {
	tests := []struct {
		name      string
		tableName string
		assert    func(*sql.DB, *AssertionResult)
	}{
		{"AuditLog", "_System_AuditLog", assertAuditLogReadPermissionLiveDB},
		{"SetupAuditTrail", "_System_SetupAuditTrail", assertSetupAuditTrailReadPermissionLiveDB},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock.New: %v", err)
			}
			defer db.Close()

			mock.ExpectQuery(liveDBQuery).
				WithArgs(tc.tableName).
				WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
					AddRow("ViewSetup"))

			result := &AssertionResult{}
			tc.assert(db, result)
			if len(result.Violations) != 1 {
				t.Fatalf("expected 1 violation for drifted ViewSetup, got %d: %+v",
					len(result.Violations), result.Violations)
			}
			v := result.Violations[0]
			if v.Severity != constants.SeverityError {
				t.Errorf("want SeverityError, got %q", v.Severity)
			}
			if v.Category != "AuditReadGate" {
				t.Errorf("want category AuditReadGate, got %q", v.Category)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations unmet: %v", err)
			}
		})
	}
}

// TestAuditLogReadGateLiveDB_EmptyPermissionDrifted — DB row carries empty string.
// Must produce an error-severity violation (empty string opens table to all users).
func TestAuditLogReadGateLiveDB_EmptyPermissionDrifted(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_AuditLog").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow(""))

	result := &AssertionResult{}
	assertAuditLogReadPermissionLiveDB(db, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for empty permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
}

// TestAuditLogReadGateLiveDB_RowMissing — fresh DB (row not yet seeded).
// The assertion must SKIP (return 0 violations) to avoid bootstrap deadlock.
func TestAuditLogReadGateLiveDB_RowMissing(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_AuditLog").
		WillReturnError(sql.ErrNoRows)

	result := &AssertionResult{}
	assertAuditLogReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for missing row (fresh DB), got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// --- SetupAuditTrail: parse-time assertion tests ---

func TestSetupAuditTrailReadGatePinned_CorrectValue(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_SetupAuditTrail",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: setupAuditTrailRequiredReadPermission,
		},
	}
	result := &AssertionResult{}
	assertSetupAuditTrailReadPermissionPinned(defs, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

func TestSetupAuditTrailReadGatePinned_EmptyPermissionDenied(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_SetupAuditTrail",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: "",
		},
	}
	result := &AssertionResult{}
	assertSetupAuditTrailReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for empty permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

func TestSetupAuditTrailReadGatePinned_MissingTable(t *testing.T) {
	defs := []schema.TableDefinition{
		{TableName: "_System_SomeOtherTable", TableType: string(constants.TableTypeSystemCore)},
	}
	result := &AssertionResult{}
	assertSetupAuditTrailReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for missing table, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
}

func TestSetupAuditTrailReadGatePinned_LiveSpec(t *testing.T) {
	result := &AssertionResult{}
	assertSetupAuditTrailReadPermissionPinned(GetSystemTableDefinitions(), result)
	if len(result.Violations) != 0 {
		t.Fatalf("live system_tables.json fails SetupAuditTrail read-gate pin: %+v",
			result.Violations)
	}
}

// --- SetupAuditTrail: live-DB assertion tests ---

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

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_SetupAuditTrail").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow(setupAuditTrailRequiredReadPermission))

	result := &AssertionResult{}
	assertSetupAuditTrailReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct DB value, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

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

	mock.ExpectQuery(liveDBQuery).
		WithArgs("_System_SetupAuditTrail").
		WillReturnError(sql.ErrNoRows)

	result := &AssertionResult{}
	assertSetupAuditTrailReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for missing row (fresh DB), got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// --- _System_RequestCapture read-gate tests (T1–T8) ---

// TestRequestCaptureReadGatePinned_CorrectValue verifies that the assertion
// passes when _System_RequestCapture carries the canonical pinned permission.
func TestRequestCaptureReadGatePinned_CorrectValue(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_RequestCapture",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: requestCaptureRequiredReadPermission,
		},
	}
	result := &AssertionResult{}
	assertRequestCaptureReadPermissionPinned(defs, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

// TestRequestCaptureReadGatePinned_ViewSetupDenied verifies that "ViewSetup"
// triggers an error-severity violation.
func TestRequestCaptureReadGatePinned_ViewSetupDenied(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_RequestCapture",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: "ViewSetup",
		},
	}
	result := &AssertionResult{}
	assertRequestCaptureReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for ViewSetup, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
	if v.Category != "AuditReadGate" {
		t.Errorf("want category AuditReadGate, got %q", v.Category)
	}
}

// TestRequestCaptureReadGatePinned_EmptyPermissionDenied verifies that an empty
// requiredReadPermission is also caught (would open the table to all authenticated users).
func TestRequestCaptureReadGatePinned_EmptyPermissionDenied(t *testing.T) {
	defs := []schema.TableDefinition{
		{
			TableName:              "_System_RequestCapture",
			TableType:              string(constants.TableTypeSystemCore),
			RequiredReadPermission: "",
		},
	}
	result := &AssertionResult{}
	assertRequestCaptureReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for empty permission, got %d: %+v",
			len(result.Violations), result.Violations)
	}
}

// TestRequestCaptureReadGatePinned_MissingTable verifies that a missing
// _System_RequestCapture row itself is flagged as an error-severity violation.
func TestRequestCaptureReadGatePinned_MissingTable(t *testing.T) {
	defs := []schema.TableDefinition{
		{TableName: "_System_SomeOtherTable", TableType: string(constants.TableTypeSystemCore)},
	}
	result := &AssertionResult{}
	assertRequestCaptureReadPermissionPinned(defs, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for missing table, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
}

// TestRequestCaptureReadGatePinned_LiveSpec verifies the assertion passes
// against the actual embedded system_tables.json — the same check the server
// runs on startup via RunAssertions.
func TestRequestCaptureReadGatePinned_LiveSpec(t *testing.T) {
	result := &AssertionResult{}
	assertRequestCaptureReadPermissionPinned(GetSystemTableDefinitions(), result)
	if len(result.Violations) != 0 {
		t.Fatalf("live system_tables.json fails RequestCapture read-gate pin: %+v",
			result.Violations)
	}
}

// --- Live-DB assertion tests for _System_RequestCapture RRP ---

// liveDBQueryRCP is the regexp pattern for the SELECT issued by the live-DB
// assertion for _System_RequestCapture. The SQL shape is identical to liveDBQuery
// (same columns/tables); only the arg differs at call time.
var liveDBQueryRCP = liveDBQuery

// TestRequestCaptureReadGateLiveDB_CorrectValue — DB row has the canonical
// value → no violations.
func TestRequestCaptureReadGateLiveDB_CorrectValue(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQueryRCP).
		WithArgs("_System_RequestCapture").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow(requestCaptureRequiredReadPermission))

	result := &AssertionResult{}
	assertRequestCaptureReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for correct DB value, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// TestRequestCaptureReadGateLiveDB_ViewSetupDrifted — DB row carries "ViewSetup".
// Must produce an error-severity violation.
func TestRequestCaptureReadGateLiveDB_ViewSetupDrifted(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQueryRCP).
		WithArgs("_System_RequestCapture").
		WillReturnRows(sqlmock.NewRows([]string{"RequiredReadPermission"}).
			AddRow("ViewSetup"))

	result := &AssertionResult{}
	assertRequestCaptureReadPermissionLiveDB(db, result)
	if len(result.Violations) != 1 {
		t.Fatalf("expected 1 violation for drifted ViewSetup, got %d: %+v",
			len(result.Violations), result.Violations)
	}
	v := result.Violations[0]
	if v.Severity != constants.SeverityError {
		t.Errorf("want SeverityError, got %q", v.Severity)
	}
	if v.Category != "AuditReadGate" {
		t.Errorf("want category AuditReadGate, got %q", v.Category)
	}
}

// TestRequestCaptureReadGateLiveDB_RowMissing — fresh DB (row not yet seeded by
// ApplyAll). The assertion must SKIP (return 0 violations) so that a brand-new
// deployment can start up and run its first ApplyAll.
func TestRequestCaptureReadGateLiveDB_RowMissing(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery(liveDBQueryRCP).
		WithArgs("_System_RequestCapture").
		WillReturnError(sql.ErrNoRows)

	result := &AssertionResult{}
	assertRequestCaptureReadPermissionLiveDB(db, result)
	if len(result.Violations) != 0 {
		t.Fatalf("expected 0 violations for missing row (fresh DB), got %d: %+v",
			len(result.Violations), result.Violations)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}