package bootstrapimpl

import (
	"testing"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/contract"
)

// TestFlowAuditLogRequiredReadPermissionPinned locks in the literal
// requiredReadPermission value for _System_FlowAuditLog. Audit-payload
// columns (InputValues, OutputValues, ErrorMessage) carry L3 record values;
// gating with ViewSetup would expose them to setup-only admins who lack
// access to the underlying records. The fix (issue #503, Option C-extended)
// pins the gate to "ViewAllData,ModifyAllData" — the same privileged-data
// tier that record_access.go uses for all L3 access decisions.
//
// This test uses the same GetManagedTableDefinitions() path as the runtime
// assertFlowAuditLogReadPermissionPinned assertion so that both the test
// and the server startup guard check identical parsed values.
func TestFlowAuditLogRequiredReadPermissionPinned(t *testing.T) {
	const wantPerm = flowAuditLogRequiredReadPermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_FlowAuditLog" {
			continue
		}
		if def.RequiredReadPermission != wantPerm {
			t.Fatalf(
				"_System_FlowAuditLog.requiredReadPermission = %q, want %q; "+
					"audit-payload columns carry L3 record values and require "+
					"VAD/MAD gating per #503 (regression guard)",
				def.RequiredReadPermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_FlowAuditLog entry missing from system_tables.json; cannot verify audit read gate")
}

// TestAuditLogRequiredReadPermissionPinned locks in the literal
// requiredReadPermission value for _System_AuditLog. Audit-payload
// columns (OldValue, NewValue, ChangesJSON, ClientIp, SessionId, ActualUserId)
// carry L3 record values and session forensics data; gating with ViewSetup or
// no permission would expose them to unauthorized users. The fix (issue #600,
// Option C-extended, sibling to #503/#564) pins the gate to
// "ViewAllData,ModifyAllData".
func TestAuditLogRequiredReadPermissionPinned(t *testing.T) {
	const wantPerm = auditLogRequiredReadPermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_AuditLog" {
			continue
		}
		if def.RequiredReadPermission != wantPerm {
			t.Fatalf(
				"_System_AuditLog.requiredReadPermission = %q, want %q; "+
					"audit-payload columns carry L3 record values and require "+
					"VAD/MAD gating per #600 (regression guard)",
				def.RequiredReadPermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_AuditLog entry missing from system_tables.json; cannot verify audit read gate")
}

// TestProcessInstanceHistoryTargetRecordReadPolicyPinned locks the generic
// metadata-owned row gate. Approval comments can carry L3 record context, so
// ordinary reads must be filtered through the target record's own visibility
// rather than a ProcessInstanceHistory-specific SOQL handler.
func TestProcessInstanceHistoryTargetRecordReadPolicyPinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "ProcessInstanceHistory" {
			continue
		}
		if def.RequiredReadPermission != "" {
			t.Fatalf("ProcessInstanceHistory.requiredReadPermission = %q, want empty; row visibility must come from accessPolicy.rowRead", def.RequiredReadPermission)
		}
		if def.AccessPolicy == nil || def.AccessPolicy.RowRead == nil {
			t.Fatalf("ProcessInstanceHistory.accessPolicy.rowRead missing: %+v", def.AccessPolicy)
		}
		rowRead := def.AccessPolicy.RowRead
		if rowRead.Kind != "targetRecordRead" ||
			rowRead.TargetObjectField != "TargetObjectType" ||
			rowRead.TargetRecordField != "TargetObjectId" {
			t.Fatalf("ProcessInstanceHistory rowRead policy = %+v, want targetRecordRead(TargetObjectType, TargetObjectId)", rowRead)
		}
		hasTargetObjectType := false
		for _, col := range def.Columns {
			if col.Name == "TargetObjectType" {
				hasTargetObjectType = true
				break
			}
		}
		if !hasTargetObjectType {
			t.Fatal("ProcessInstanceHistory.TargetObjectType column missing; rowRead cannot discriminate target object API name")
		}
		return
	}
	t.Fatal("ProcessInstanceHistory entry missing from system_tables.json; cannot verify approval history row gate")
}

// TestSystemListViewOwnerVisibilityRowReadPolicyPinned locks the generic
// read guard for custom list views. Private rows are visible only to their
// owner through the metadata picker and every generic read surface; legacy
// NULL/NULL rows remain catalog-compatible only through explicit list-view
// readers, not by falling through public OWD.
func TestSystemListViewOwnerVisibilityRowReadPolicyPinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != constants.TableListView {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.RowRead == nil {
			t.Fatalf("%s.accessPolicy.rowRead missing: %+v", constants.TableListView, def.AccessPolicy)
		}
		rowRead := def.AccessPolicy.RowRead
		if rowRead.Kind != "ownerVisibility" ||
			rowRead.OwnerField != constants.FieldSysListView_OwnerID ||
			rowRead.VisibilityField != constants.FieldSysListView_Visibility {
			t.Fatalf("%s rowRead policy = %+v, want ownerVisibility(%s, %s)",
				constants.TableListView,
				rowRead,
				constants.FieldSysListView_OwnerID,
				constants.FieldSysListView_Visibility,
			)
		}
		if len(rowRead.OwnerVisibleValues) != 1 || rowRead.OwnerVisibleValues[0] != "Private" {
			t.Fatalf("%s owner-visible values = %#v, want [Private]", constants.TableListView, rowRead.OwnerVisibleValues)
		}
		if len(rowRead.PublicVisibleValues) != 0 {
			t.Fatalf("%s public-visible values = %#v, want none for PR2", constants.TableListView, rowRead.PublicVisibleValues)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify list-view row gate", constants.TableListView)
}

func TestProcessInstanceHistoryStepsAndWorkitemsRelationshipPinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "ProcessInstanceHistory" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name != "ProcessInstanceId" {
				continue
			}
			if col.ChildRelationshipName != "StepsAndWorkitems" {
				t.Fatalf("ProcessInstanceHistory.ProcessInstanceId childRelationshipName = %q, want StepsAndWorkitems", col.ChildRelationshipName)
			}
			return
		}
		t.Fatal("ProcessInstanceHistory.ProcessInstanceId column missing from system_tables.json")
		return
	}
	t.Fatal("ProcessInstanceHistory entry missing from system_tables.json; cannot verify StepsAndWorkitems relationship")
}

func TestProcessInstanceWorkitemPendingWorkitemsRelationshipPinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "ProcessInstanceWorkitem" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name != "ProcessInstanceId" {
				continue
			}
			if col.ChildRelationshipName != "PendingWorkitems" {
				t.Fatalf("ProcessInstanceWorkitem.ProcessInstanceId childRelationshipName = %q, want PendingWorkitems", col.ChildRelationshipName)
			}
			return
		}
		t.Fatal("ProcessInstanceWorkitem.ProcessInstanceId column missing from system_tables.json")
		return
	}
	t.Fatal("ProcessInstanceWorkitem entry missing from system_tables.json; cannot verify PendingWorkitems relationship")
}

func TestStandardActionHeaderFallbackEligibleColumnPinned(t *testing.T) {
	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != "_System_StandardAction" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name != "HeaderFallbackEligible" {
				continue
			}
			if col.Type != "TINYINT(1)" {
				t.Fatalf("_System_StandardAction.HeaderFallbackEligible type = %q, want TINYINT(1)", col.Type)
			}
			if col.Nullable {
				t.Fatal("_System_StandardAction.HeaderFallbackEligible must be NOT NULL so legacy rows default deterministically")
			}
			if col.Default != "1" {
				t.Fatalf("_System_StandardAction.HeaderFallbackEligible default = %q, want 1", col.Default)
			}
			return
		}
		t.Fatal("_System_StandardAction.HeaderFallbackEligible column missing")
	}
	t.Fatal("_System_StandardAction entry missing from system_tables.json")
}

func TestActionSchemaCollectionAuthorityPinned(t *testing.T) {
	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != "_System_ActionSchema" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name != "IsCollection" {
				continue
			}
			if col.Type != "TINYINT(1)" || col.Nullable || col.Default != "0" {
				t.Fatalf("_System_ActionSchema.IsCollection shape = type %q nullable %v default %q, want TINYINT(1) NOT NULL DEFAULT 0", col.Type, col.Nullable, col.Default)
			}
			return
		}
		t.Fatal("_System_ActionSchema.IsCollection column missing from the pinned kernel catalog")
	}
	t.Fatal("_System_ActionSchema entry missing from the pinned kernel catalog")
}

func TestObjectPermissionsWriteValidationPolicyPinned(t *testing.T) {
	type writeValidationPhaseCapability struct {
		Phase      string
		Capability string
	}
	const objectPermissionsCapability = "platform.security.write.object-permissions"
	const systemProfileOwnedImmutableCapability = "platform.security.write.system-profile-owned-immutable"
	const managedPermissionSetDefinitionCapability = "platform.security.write.managed-permission-set-definition"
	want := map[writeValidationPhaseCapability]struct{}{
		{Phase: string(contract.RecordWriteValidationBeforeCreate), Capability: objectPermissionsCapability}:               {},
		{Phase: string(contract.RecordWriteValidationBeforeUpdate), Capability: objectPermissionsCapability}:               {},
		{Phase: string(contract.RecordWriteValidationBeforeRestore), Capability: objectPermissionsCapability}:              {},
		{Phase: string(contract.RecordWriteValidationBeforeCreate), Capability: systemProfileOwnedImmutableCapability}:     {},
		{Phase: string(contract.RecordWriteValidationBeforeUpdate), Capability: systemProfileOwnedImmutableCapability}:     {},
		{Phase: string(contract.RecordWriteValidationBeforeDelete), Capability: systemProfileOwnedImmutableCapability}:     {},
		{Phase: string(contract.RecordWriteValidationBeforeRestore), Capability: systemProfileOwnedImmutableCapability}:    {},
		{Phase: string(contract.RecordWriteValidationBeforePurge), Capability: systemProfileOwnedImmutableCapability}:      {},
		{Phase: string(contract.RecordWriteValidationBeforeCreate), Capability: managedPermissionSetDefinitionCapability}:  {},
		{Phase: string(contract.RecordWriteValidationBeforeUpdate), Capability: managedPermissionSetDefinitionCapability}:  {},
		{Phase: string(contract.RecordWriteValidationBeforeDelete), Capability: managedPermissionSetDefinitionCapability}:  {},
		{Phase: string(contract.RecordWriteValidationBeforeRestore), Capability: managedPermissionSetDefinitionCapability}: {},
		{Phase: string(contract.RecordWriteValidationBeforePurge), Capability: managedPermissionSetDefinitionCapability}:   {},
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableObjectPermissions {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableObjectPermissions, def.AccessPolicy)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(want) {
			t.Fatalf("%s writeValidation validators = %+v, want exactly %+v", constants.TableObjectPermissions, validators, want)
		}
		for _, validator := range validators {
			key := writeValidationPhaseCapability{Phase: validator.Phase, Capability: validator.Capability}
			if _, ok := want[key]; !ok {
				t.Fatalf("%s writeValidation includes unexpected phase/capability %q/%q in %+v", constants.TableObjectPermissions, validator.Phase, validator.Capability, validators)
			}
			delete(want, key)
		}
		if len(want) != 0 {
			t.Fatalf("%s writeValidation missing phase/capability pair(s): %+v", constants.TableObjectPermissions, want)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableObjectPermissions)
}

func TestPermissionSetAssignmentWriteValidationPolicyPinned(t *testing.T) {
	const wantCapability = "platform.security.write.permission-set-assignment-target"
	want := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate):  wantCapability,
		string(contract.RecordWriteValidationBeforeUpdate):  wantCapability,
		string(contract.RecordWriteValidationBeforeDelete):  wantCapability,
		string(contract.RecordWriteValidationBeforeRestore): wantCapability,
		string(contract.RecordWriteValidationBeforePurge):   wantCapability,
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TablePermissionSetAssignment {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TablePermissionSetAssignment, def.AccessPolicy)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(want) {
			t.Fatalf("%s writeValidation validators = %+v, want exactly all mutation phases %q", constants.TablePermissionSetAssignment, validators, wantCapability)
		}
		for _, validator := range validators {
			capability, ok := want[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TablePermissionSetAssignment, validator.Phase, validators)
			}
			if validator.Capability != capability {
				t.Fatalf("%s writeValidation[%s] capability = %q, want %q", constants.TablePermissionSetAssignment, validator.Phase, validator.Capability, capability)
			}
			delete(want, validator.Phase)
		}
		if len(want) != 0 {
			t.Fatalf("%s writeValidation missing phase(s): %+v", constants.TablePermissionSetAssignment, want)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TablePermissionSetAssignment)
}

func TestGroupMemberRoleBackedWriteValidationPolicyPinned(t *testing.T) {
	const wantCapability = "platform.security.write.role-backed-group-member"
	want := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate): wantCapability,
		string(contract.RecordWriteValidationBeforeUpdate): wantCapability,
		string(contract.RecordWriteValidationBeforeDelete): wantCapability,
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableGroupMember {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableGroupMember, def.AccessPolicy)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(want) {
			t.Fatalf("%s writeValidation validators = %+v, want exactly before_create/before_update/before_delete %q", constants.TableGroupMember, validators, wantCapability)
		}
		for _, validator := range validators {
			capability, ok := want[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TableGroupMember, validator.Phase, validators)
			}
			if validator.Capability != capability {
				t.Fatalf("%s writeValidation[%s] capability = %q, want %q", constants.TableGroupMember, validator.Phase, validator.Capability, capability)
			}
			delete(want, validator.Phase)
		}
		if len(want) != 0 {
			t.Fatalf("%s writeValidation missing phase(s): %+v", constants.TableGroupMember, want)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableGroupMember)
}

func TestGroupUserRoleWriteValidationPolicyPinned(t *testing.T) {
	want := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate): "platform.security.write.group-user-role-reference",
		string(contract.RecordWriteValidationBeforeUpdate): "platform.security.write.group-user-role-reference",
		string(contract.RecordWriteValidationBeforeDelete): "platform.security.write.soft-delete-reference-guard",
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableGroup {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableGroup, def.AccessPolicy)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(want) {
			t.Fatalf("%s writeValidation validators = %+v, want create/update role-reference and delete reference guard", constants.TableGroup, validators)
		}
		for _, validator := range validators {
			capability, ok := want[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TableGroup, validator.Phase, validators)
			}
			if validator.Capability != capability {
				t.Fatalf("%s writeValidation[%s] capability = %q, want %q", constants.TableGroup, validator.Phase, validator.Capability, capability)
			}
			delete(want, validator.Phase)
		}
		if len(want) != 0 {
			t.Fatalf("%s writeValidation missing phase(s): %+v", constants.TableGroup, want)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableGroup)
}

func TestUserRoleWriteValidationPolicyPinned(t *testing.T) {
	const wantCapability = "platform.security.write.user-role-parent"
	want := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate): wantCapability,
		string(contract.RecordWriteValidationBeforeUpdate): wantCapability,
		string(contract.RecordWriteValidationBeforeDelete): wantCapability,
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableUserRole {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableUserRole, def.AccessPolicy)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(want) {
			t.Fatalf("%s writeValidation validators = %+v, want create/update/delete %q", constants.TableUserRole, validators, wantCapability)
		}
		for _, validator := range validators {
			capability, ok := want[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TableUserRole, validator.Phase, validators)
			}
			if validator.Capability != capability {
				t.Fatalf("%s writeValidation[%s] capability = %q, want %q", constants.TableUserRole, validator.Phase, validator.Capability, capability)
			}
			delete(want, validator.Phase)
		}
		if len(want) != 0 {
			t.Fatalf("%s writeValidation missing phase(s): %+v", constants.TableUserRole, want)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableUserRole)
}

func TestSharingRuleWriteValidationPolicyPinned(t *testing.T) {
	want := map[string]map[string]struct{}{
		string(contract.RecordWriteValidationBeforeCreate): {
			"platform.security.write.sharing-rule-owner-references": {},
			"platform.security.write.sharing-rule-shape":            {},
		},
		string(contract.RecordWriteValidationBeforeUpdate): {
			"platform.security.write.sharing-rule-owner-references": {},
			"platform.security.write.sharing-rule-shape":            {},
		},
		string(contract.RecordWriteValidationBeforeRestore): {
			"platform.security.write.sharing-rule-owner-references": {},
			"platform.security.write.sharing-rule-shape":            {},
		},
	}
	wantCount := 0
	for _, capabilities := range want {
		wantCount += len(capabilities)
	}
	wantCanonicalizers := map[string]map[string]struct{}{
		string(contract.RecordWriteValidationBeforeCreate): {
			"platform.security.write.sharing-rule-canonicalization": {},
		},
		string(contract.RecordWriteValidationBeforeUpdate): {
			"platform.security.write.sharing-rule-canonicalization": {},
		},
		string(contract.RecordWriteValidationBeforeRestore): {
			"platform.security.write.sharing-rule-canonicalization": {},
		},
	}
	wantCanonicalizerCount := 0
	for _, capabilities := range wantCanonicalizers {
		wantCanonicalizerCount += len(capabilities)
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableSharingRule {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableSharingRule, def.AccessPolicy)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != wantCount {
			t.Fatalf("%s writeValidation validators = %+v, want exactly %+v", constants.TableSharingRule, validators, want)
		}
		for _, validator := range validators {
			capabilities, ok := want[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TableSharingRule, validator.Phase, validators)
			}
			if _, ok := capabilities[validator.Capability]; !ok {
				t.Fatalf("%s writeValidation[%s] capability = %q, want one of %+v", constants.TableSharingRule, validator.Phase, validator.Capability, capabilities)
			}
			delete(capabilities, validator.Capability)
			if len(capabilities) == 0 {
				delete(want, validator.Phase)
			}
		}
		if len(want) != 0 {
			t.Fatalf("%s writeValidation missing phase/capability pair(s): %+v", constants.TableSharingRule, want)
		}
		if required := def.AccessPolicy.WriteValidation.RequiredValidationRules; len(required) != 0 {
			t.Fatalf("%s writeValidation requiredValidationRules = %+v, want none; SharingRule shape is an L0 sharing-substrate capability", constants.TableSharingRule, required)
		}
		canonicalizers := def.AccessPolicy.WriteValidation.Canonicalizers
		if len(canonicalizers) != wantCanonicalizerCount {
			t.Fatalf("%s writeValidation canonicalizers = %+v, want exactly %+v", constants.TableSharingRule, canonicalizers, wantCanonicalizers)
		}
		for _, canonicalizer := range canonicalizers {
			capabilities, ok := wantCanonicalizers[canonicalizer.Phase]
			if !ok {
				t.Fatalf("%s writeValidation canonicalizers include unexpected phase %q in %+v", constants.TableSharingRule, canonicalizer.Phase, canonicalizers)
			}
			if _, ok := capabilities[canonicalizer.Capability]; !ok {
				t.Fatalf("%s writeValidation canonicalizer[%s] capability = %q, want one of %+v", constants.TableSharingRule, canonicalizer.Phase, canonicalizer.Capability, capabilities)
			}
			delete(capabilities, canonicalizer.Capability)
			if len(capabilities) == 0 {
				delete(wantCanonicalizers, canonicalizer.Phase)
			}
		}
		if len(wantCanonicalizers) != 0 {
			t.Fatalf("%s writeValidation canonicalizers missing phase/capability pair(s): %+v", constants.TableSharingRule, wantCanonicalizers)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableSharingRule)
}

func TestReportFolderWriteValidationPolicyPinned(t *testing.T) {
	wantValidators := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate):  "platform.folder.write.folder-type-permission",
		string(contract.RecordWriteValidationBeforeUpdate):  "platform.folder.write.folder-type-permission",
		string(contract.RecordWriteValidationBeforeDelete):  "platform.folder.write.folder-type-permission",
		string(contract.RecordWriteValidationBeforeRestore): "platform.folder.write.folder-type-permission",
		string(contract.RecordWriteValidationBeforePurge):   "platform.folder.write.folder-type-permission",
	}
	wantCanonicalizers := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate):  "platform.folder.write.folder-type-canonicalization",
		string(contract.RecordWriteValidationBeforeUpdate):  "platform.folder.write.folder-type-canonicalization",
		string(contract.RecordWriteValidationBeforeRestore): "platform.folder.write.folder-type-canonicalization",
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableReportFolder {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableReportFolder, def.AccessPolicy)
		}
		if required := def.AccessPolicy.WriteValidation.RequiredValidationRules; len(required) != 0 {
			t.Fatalf("%s writeValidation requiredValidationRules = %+v, want none; folder type permissions are enforced by a declared folder-substrate capability", constants.TableReportFolder, required)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(wantValidators) {
			t.Fatalf("%s writeValidation validators = %+v, want exactly %+v", constants.TableReportFolder, validators, wantValidators)
		}
		for _, validator := range validators {
			want, ok := wantValidators[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TableReportFolder, validator.Phase, validators)
			}
			if validator.Capability != want {
				t.Fatalf("%s writeValidation[%s] capability = %q, want %q", constants.TableReportFolder, validator.Phase, validator.Capability, want)
			}
			delete(wantValidators, validator.Phase)
		}
		if len(wantValidators) != 0 {
			t.Fatalf("%s writeValidation missing validator phase(s): %+v", constants.TableReportFolder, wantValidators)
		}

		canonicalizers := def.AccessPolicy.WriteValidation.Canonicalizers
		if len(canonicalizers) != len(wantCanonicalizers) {
			t.Fatalf("%s writeValidation canonicalizers = %+v, want exactly %+v", constants.TableReportFolder, canonicalizers, wantCanonicalizers)
		}
		for _, canonicalizer := range canonicalizers {
			want, ok := wantCanonicalizers[canonicalizer.Phase]
			if !ok {
				t.Fatalf("%s writeValidation canonicalizers include unexpected phase %q in %+v", constants.TableReportFolder, canonicalizer.Phase, canonicalizers)
			}
			if canonicalizer.Capability != want {
				t.Fatalf("%s writeValidation canonicalizer[%s] capability = %q, want %q", constants.TableReportFolder, canonicalizer.Phase, canonicalizer.Capability, want)
			}
			delete(wantCanonicalizers, canonicalizer.Phase)
		}
		if len(wantCanonicalizers) != 0 {
			t.Fatalf("%s writeValidation missing canonicalizer phase(s): %+v", constants.TableReportFolder, wantCanonicalizers)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableReportFolder)
}

func TestConnectedAppWriteValidationPolicyPinned(t *testing.T) {
	wantValidators := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate): "platform.security.write.connected-app-policy",
		string(contract.RecordWriteValidationBeforeUpdate): "platform.security.write.connected-app-policy",
	}
	wantCanonicalizers := map[string]string{
		string(contract.RecordWriteValidationBeforeCreate): "platform.security.write.connected-app-policy-canonicalization",
		string(contract.RecordWriteValidationBeforeUpdate): "platform.security.write.connected-app-policy-canonicalization",
	}

	for _, def := range GetManagedTableDefinitions() {
		if def.TableName != constants.TableConnectedApp {
			continue
		}
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			t.Fatalf("%s accessPolicy.writeValidation missing: %+v", constants.TableConnectedApp, def.AccessPolicy)
		}
		if required := def.AccessPolicy.WriteValidation.RequiredValidationRules; len(required) != 0 {
			t.Fatalf("%s writeValidation requiredValidationRules = %+v, want none; ConnectedApp policy grammar is enforced by a declared auth-substrate capability", constants.TableConnectedApp, required)
		}
		validators := def.AccessPolicy.WriteValidation.Validators
		if len(validators) != len(wantValidators) {
			t.Fatalf("%s writeValidation validators = %+v, want exactly %+v", constants.TableConnectedApp, validators, wantValidators)
		}
		for _, validator := range validators {
			want, ok := wantValidators[validator.Phase]
			if !ok {
				t.Fatalf("%s writeValidation includes unexpected phase %q in %+v", constants.TableConnectedApp, validator.Phase, validators)
			}
			if validator.Capability != want {
				t.Fatalf("%s writeValidation[%s] capability = %q, want %q", constants.TableConnectedApp, validator.Phase, validator.Capability, want)
			}
			delete(wantValidators, validator.Phase)
		}
		if len(wantValidators) != 0 {
			t.Fatalf("%s writeValidation missing phase/capability pair(s): %+v", constants.TableConnectedApp, wantValidators)
		}
		canonicalizers := def.AccessPolicy.WriteValidation.Canonicalizers
		if len(canonicalizers) != len(wantCanonicalizers) {
			t.Fatalf("%s writeValidation canonicalizers = %+v, want exactly %+v", constants.TableConnectedApp, canonicalizers, wantCanonicalizers)
		}
		for _, canonicalizer := range canonicalizers {
			want, ok := wantCanonicalizers[canonicalizer.Phase]
			if !ok {
				t.Fatalf("%s writeValidation canonicalizers include unexpected phase %q in %+v", constants.TableConnectedApp, canonicalizer.Phase, canonicalizers)
			}
			if canonicalizer.Capability != want {
				t.Fatalf("%s writeValidation canonicalizer[%s] capability = %q, want %q", constants.TableConnectedApp, canonicalizer.Phase, canonicalizer.Capability, want)
			}
			delete(wantCanonicalizers, canonicalizer.Phase)
		}
		if len(wantCanonicalizers) != 0 {
			t.Fatalf("%s writeValidation canonicalizers missing phase/capability pair(s): %+v", constants.TableConnectedApp, wantCanonicalizers)
		}
		return
	}
	t.Fatalf("%s entry missing from system_tables.json; cannot verify writeValidation policy", constants.TableConnectedApp)
}

// TestSharingRuleCriteriaIsNullableInSpec locks in the schema-level fact that
// SharingRule.Criteria carries Nullable=true. The fact is the SSoT for the
// downstream contract that owner-based SharingRule POSTs (which omit Criteria)
// pass the generic required-field gate; see
// backend/internal/features/records/security_contracts.go for the validator
// that depends on this column being optional and pingcap-inc/NexusCRM#395 for
// the bug this lock-in prevents from regressing.
//
// Because the JSON struct tag is `json:"nullable,omitempty"`, an absent
// "nullable" key deserializes to false and reintroduces the bug silently. A
// regression on this column would not be caught by validator unit tests
// alone — those exercise validateObjectSecurityContract directly and bypass
// the schema-derivation pipeline. This test guards the schema input to that
// pipeline.
func TestSharingRuleCriteriaIsNullableInSpec(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "SharingRule" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name != "Criteria" {
				continue
			}
			if !col.Nullable {
				t.Fatalf("SharingRule.Criteria Nullable = false, want true; "+
					"absent or false-valued nullable flag in system_tables.json "+
					"causes the generic required-field gate to reject all "+
					"owner-based SharingRule POSTs (regression of #395). "+
					"column = %+v", col)
			}
			return
		}
		t.Fatal("SharingRule.Criteria column missing from system_tables.json")
		return
	}
	t.Fatal("SharingRule table missing from system_tables.json")
}

// TestUserFirstNameIsNullableInSpec locks the managed schema authority for the
// #974/#1491 user-create contract. The repair lane for warm-upgraded orgs must
// converge both `_System_Field.Required=0` and the physical `User.FirstName`
// column shape; if system_tables.json omits `"nullable": true`, the bootstrap
// schema definition silently defaults back to NOT NULL and `setup apply` can
// preserve or re-tighten storage despite describe/runtime reporting optional.
func TestUserFirstNameIsNullableInSpec(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "User" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name != "FirstName" {
				continue
			}
			if !col.Nullable {
				t.Fatalf("User.FirstName Nullable = false, want true; absent or false nullable in system_tables.json makes bootstrap schema reconciliation preserve NOT NULL storage and breaks the #974/#1491 optional-on-create contract. column = %+v", col)
			}
			return
		}
		t.Fatal("User.FirstName column missing from system_tables.json")
		return
	}
	t.Fatal("User table missing from system_tables.json")
}

// TestSetupAuditTrailRequiredReadPermissionPinned locks in the literal
// requiredReadPermission value for _System_SetupAuditTrail. Setup-trail entries
// describe admin actions on private metadata; gating with ViewSetup would expose
// admin activity logs to setup-only admins who may lack full audit access. The
// fix (issue #604, following #503 Option C-extended precedent for FlowAuditLog)
// pins the gate to "ViewSetupAuditTrail,ViewAllData,ModifyAllData" — the OR-set
// that grants to users with ViewSetupAuditTrail OR the broad privileged-data tier.
//
// This test uses the same GetManagedTableDefinitions() path as the runtime
// assertSetupAuditTrailReadPermissionPinned assertion so that both the test
// and the server startup guard check identical parsed values.
func TestSetupAuditTrailRequiredReadPermissionPinned(t *testing.T) {
	const wantPerm = setupAuditTrailRequiredReadPermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_SetupAuditTrail" {
			continue
		}
		if def.RequiredReadPermission != wantPerm {
			t.Fatalf(
				"_System_SetupAuditTrail.requiredReadPermission = %q, want %q; "+
					"setup-trail entries expose admin actions and require "+
					"ViewSetupAuditTrail/VAD/MAD gating per #604 (regression guard)",
				def.RequiredReadPermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_SetupAuditTrail entry missing from system_tables.json; cannot verify audit read gate")
}

// TestRequestCaptureRequiredReadPermissionPinned locks in the literal
// requiredReadPermission value for _System_RequestCapture. Audit-payload
// columns (Body, ResponseBody, Headers) carry L3 record values and secrets
// (auth headers, API keys); gating with an open or ViewSetup value would
// expose them to users who lack access to the underlying records or secrets.
// Issue #601 pins the gate to "ViewAllData,ModifyAllData" — the same
// privileged-data tier that record_access.go uses for all L3 access decisions.
func TestRequestCaptureRequiredReadPermissionPinned(t *testing.T) {
	const wantPerm = requestCaptureRequiredReadPermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_RequestCapture" {
			continue
		}
		if def.RequiredReadPermission != wantPerm {
			t.Fatalf(
				"_System_RequestCapture.requiredReadPermission = %q, want %q; "+
					"audit-payload columns carry L3 record values + secrets and require "+
					"VAD/MAD gating per #601 (regression guard)",
				def.RequiredReadPermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_RequestCapture entry missing from system_tables.json; cannot verify audit read gate")
}

// TestInstalledPackPackInstallIdColumnPresent locks in the PackInstallId
// column on _System_InstalledPack. This column is the per-incarnation
// identity that NCRM-INT-05's connector event queue (#1498) keys queue
// state on to prevent cross-rebirth contamination. Removing it would
// silently re-introduce the queue-contamination class regressed in
// PR #1498 Codex round 6.
func TestInstalledPackPackInstallIdColumnPresent(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_InstalledPack" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name == "PackInstallId" {
				if col.Nullable {
					t.Fatalf("_System_InstalledPack.PackInstallId must be NOT NULL — incarnation scoping cannot tolerate null values")
				}
				return
			}
		}
		t.Fatalf("_System_InstalledPack.PackInstallId missing — would re-introduce queue-contamination class (PR #1498 round 6)")
	}
	t.Fatal("_System_InstalledPack entry missing from system_tables.json")
}

func TestRecycleBinDeleteOperationIdColumnIsNullable(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_RecycleBin" {
			continue
		}
		for _, col := range def.Columns {
			if col.Name == "DeleteOperationId" {
				if !col.Nullable {
					t.Fatalf("_System_RecycleBin.DeleteOperationId must remain nullable because legacy row backfill/migration is intentionally outside the platform PR")
				}
				return
			}
		}
		t.Fatal("_System_RecycleBin.DeleteOperationId missing from system_tables.json")
	}
	t.Fatal("_System_RecycleBin entry missing from system_tables.json")
}

// TestRequestCaptureRequiredWritePermissionPinned locks in the literal
// requiredWritePermission value for _System_RequestCapture. Write access must
// be "BLOCKED" to prevent generic CRUD writes to secrets-bearing payload
// columns (Body, ResponseBody, Headers). Only the capture middleware (via
// direct SQL, bypassing the CRUD layer) may write to this table.
func TestRequestCaptureRequiredWritePermissionPinned(t *testing.T) {
	const wantPerm = requestCaptureRequiredWritePermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_RequestCapture" {
			continue
		}
		if def.RequiredWritePermission != wantPerm {
			t.Fatalf(
				"_System_RequestCapture.requiredWritePermission = %q, want %q; "+
					"must be BLOCKED to prevent generic CRUD writes to secrets-bearing "+
					"payload per #601 (regression guard)",
				def.RequiredWritePermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_RequestCapture entry missing from system_tables.json; cannot verify audit write gate")
}

// TestExternalDeliveryAttemptRequiredReadPermissionPinned locks in the literal
// requiredReadPermission value for _System_ExternalDeliveryAttempt. Frozen
// outbound payloads can contain L3 record data, recipient details, webhook
// endpoints, and delivery secrets; exposing them below the VAD/MAD tier would
// leak operator evidence and integration material to unauthorized users.
func TestExternalDeliveryAttemptRequiredReadPermissionPinned(t *testing.T) {
	const wantPerm = externalDeliveryAttemptRequiredReadPermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_ExternalDeliveryAttempt" {
			continue
		}
		if def.RequiredReadPermission != wantPerm {
			t.Fatalf(
				"_System_ExternalDeliveryAttempt.requiredReadPermission = %q, want %q; "+
					"frozen outbound payloads can contain L3 record data + delivery "+
					"secrets and require VAD/MAD gating (regression guard)",
				def.RequiredReadPermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_ExternalDeliveryAttempt entry missing from system_tables.json; cannot verify delivery read gate")
}

// TestExternalDeliveryAttemptRequiredWritePermissionPinned locks in the
// literal requiredWritePermission value for _System_ExternalDeliveryAttempt.
// Generic CRUD writes must stay BLOCKED so only the delivery service can
// create append-only evidence rows via direct SQL.
func TestExternalDeliveryAttemptRequiredWritePermissionPinned(t *testing.T) {
	const wantPerm = externalDeliveryAttemptRequiredWritePermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_ExternalDeliveryAttempt" {
			continue
		}
		if def.RequiredWritePermission != wantPerm {
			t.Fatalf(
				"_System_ExternalDeliveryAttempt.requiredWritePermission = %q, want %q; "+
					"writes must stay BLOCKED so generic CRUD cannot forge or rewrite "+
					"delivery evidence (regression guard)",
				def.RequiredWritePermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_ExternalDeliveryAttempt entry missing from system_tables.json; cannot verify delivery write gate")
}

// TestConnectorEventRequiredReadPermissionPinned locks in the literal
// requiredReadPermission value for _System_ConnectorEvent. Generic /query and
// REST table-read must be fully blocked; the only authorized read path is the
// connector_events service's ListDeadEvents API, which enforces ViewConnectorEvents
// and returns only dead-status rows. Setting requiredReadPermission to any named
// permission (rather than BLOCKED) would leak the DLQ-only intent to the generic
// CRUD surface for any user holding that permission — even read-only admin roles.
func TestConnectorEventRequiredReadPermissionPinned(t *testing.T) {
	const wantPerm = connectorEventRequiredReadPermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_ConnectorEvent" {
			continue
		}
		if def.RequiredReadPermission != wantPerm {
			t.Fatalf(
				"_System_ConnectorEvent.requiredReadPermission = %q, want %q; "+
					"_System_ConnectorEvent must be read-blocked at the generic CRUD layer — "+
					"only the connector_events service's ListDeadEvents (which gates on "+
					"ViewConnectorEvents) may read connector events (regression guard)",
				def.RequiredReadPermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_ConnectorEvent entry missing from system_tables.json; cannot verify read gate")
}

// TestConnectorEventRequiredWritePermissionPinned locks the write gate to
// BLOCKED so the generic CRUD layer cannot mutate connector event rows.
// Only the connector_events service layer (via direct SQL, bypassing CRUD)
// writes to this table.
func TestConnectorEventRequiredWritePermissionPinned(t *testing.T) {
	const wantPerm = connectorEventRequiredWritePermission
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_ConnectorEvent" {
			continue
		}
		if def.RequiredWritePermission != wantPerm {
			t.Fatalf(
				"_System_ConnectorEvent.requiredWritePermission = %q, want %q; "+
					"writes must be BLOCKED at the metadata layer — only the connector_events "+
					"service may write via direct SQL (regression guard, #339)",
				def.RequiredWritePermission, wantPerm,
			)
		}
		return
	}
	t.Fatal("_System_ConnectorEvent entry missing from system_tables.json; cannot verify write gate")
}

// TestWebhookSecretValueReadAndWriteGatesPinned locks in both the read and
// write BLOCKED gates for _System_WebhookSecretValue. Secret values are
// server-internal only; no API caller should be able to read or write them
// through generic SObject CRUD. Managed exclusively via the RotateSecret
// admin endpoint. This test guards against regression of either gate.
func TestWebhookSecretValueReadAndWriteGatesPinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_WebhookSecretValue" {
			continue
		}
		if def.RequiredReadPermission != "BLOCKED" {
			t.Fatalf(
				"_System_WebhookSecretValue.requiredReadPermission = %q, want %q; "+
					"secret values must be unreadable via generic SObject CRUD (regression guard)",
				def.RequiredReadPermission, "BLOCKED",
			)
		}
		if def.RequiredWritePermission != "BLOCKED" {
			t.Fatalf(
				"_System_WebhookSecretValue.requiredWritePermission = %q, want %q; "+
					"secret values must be unwritable via generic SObject CRUD — "+
					"only RotateSecret endpoint may write rows (regression guard)",
				def.RequiredWritePermission, "BLOCKED",
			)
		}
		return
	}
	t.Fatal("_System_WebhookSecretValue entry missing from system_tables.json; cannot verify secret value gates")
}

// TestWebhookSecretDefinitionWriteGatePinned locks in the requiredWritePermission
// for _System_WebhookSecret. The definition table is managed by the connector-pack
// install lifecycle and must not be directly admin-editable via generic SObject CRUD;
// write access would bypass pack manifest authoring and the sweeper, creating a second
// source of truth. This test guards against regression of the BLOCKED write gate.
func TestWebhookSecretDefinitionWriteGatePinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_WebhookSecret" {
			continue
		}
		if def.RequiredReadPermission != "ViewSetup" {
			t.Fatalf(
				"_System_WebhookSecret.requiredReadPermission = %q, want %q; "+
					"setup admins need read access to webhook secret definitions (regression guard)",
				def.RequiredReadPermission, "ViewSetup",
			)
		}
		if def.RequiredWritePermission != "BLOCKED" {
			t.Fatalf(
				"_System_WebhookSecret.requiredWritePermission = %q, want %q; "+
					"definition rows must be unwritable via generic SObject CRUD — "+
					"managed exclusively by the connector-pack install lifecycle (regression guard)",
				def.RequiredWritePermission, "BLOCKED",
			)
		}
		return
	}
	t.Fatal("_System_WebhookSecret entry missing from system_tables.json; cannot verify write gate")
}

// TestOAuthCustomScopeWriteGatePinned locks the generic CRUD write gate for
// _System_OAuthCustomScope. Scope rows are pack-authored route/link policy for
// public OAuth issuance and enforcement; generic SObject writes would create a
// second mutation authority outside source-backed pack validation.
func TestOAuthCustomScopeWriteGatePinned(t *testing.T) {
	defs := GetManagedTableDefinitions()
	for _, def := range defs {
		if def.TableName != "_System_OAuthCustomScope" {
			continue
		}
		if def.RequiredReadPermission != "ViewSetup" {
			t.Fatalf(
				"_System_OAuthCustomScope.requiredReadPermission = %q, want %q; "+
					"setup admins need read access to installed OAuth scope policy",
				def.RequiredReadPermission, "ViewSetup",
			)
		}
		if def.RequiredWritePermission != "BLOCKED" {
			t.Fatalf(
				"_System_OAuthCustomScope.requiredWritePermission = %q, want %q; "+
					"scope route/link policy must be written only through source-backed pack apply/verify",
				def.RequiredWritePermission, "BLOCKED",
			)
		}
		return
	}
	t.Fatal("_System_OAuthCustomScope entry missing from system_tables.json; cannot verify write gate")
}