package controlplane

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/nexuscrm/db"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane/layoutownership"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

const l0SampleLimit = 5
const l0BootstrapStateTable = "_System_BootstrapState"

type l0Oracle struct {
	objectNames        map[string]struct{}
	tableNames         map[string]struct{}
	fieldKeys          map[string]struct{}
	relationshipKeys   map[string]struct{}
	listViewKeys       map[string]struct{}
	runtimePackNames   map[string]struct{}
	declaredComponents map[string]packs.DeclaredComponent
	permissionAPINames map[string]struct{}
	profileIDs         map[string]struct{}
	connectedApps      []packs.PlatformBootstrapConnectedApp
	objectNamesSorted  []string
	tableNamesSorted   []string
	profileIDsSorted   []string
	runtimePacksSorted []string
	permissionsSorted  []string
}

type l0DiagnosticReport struct {
	contamination []string
	ownershipGaps []string
}

type l0ObjectReferenceSurface struct {
	table      string
	column     string
	sampleExpr string
	where      string
}

type l0FieldReferenceSurface struct {
	table        string
	objectColumn string
	fieldColumn  string
	sampleExpr   string
	where        string
}

type l0ParentReferenceSurface struct {
	table      string
	column     string
	sampleExpr string
	where      string
}

var l0ObjectReferenceSurfaces = []l0ObjectReferenceSurface{
	{"ObjectPermissions", "SobjectType", "CONCAT(SobjectType, '/', ParentId)", "COALESCE(IsDeleted, 0) = 0"},
	{"FieldPermissions", "SobjectType", "CONCAT(SobjectType, '/', Field)", "COALESCE(IsDeleted, 0) = 0"},
	{"QueueSobject", "SobjectType", "CONCAT(QueueId, '/', SobjectType)", "1 = 1"},
	{"_System_ListView", "ObjectApiName", "CONCAT(ObjectApiName, '/', Label)", "1 = 1"},
	{"_System_Relationship", "ChildObjectApiName", "CONCAT(ChildObjectApiName, '.', FieldApiName, ' -> ', ParentObjectApiName)", "1 = 1"},
	{"_System_Relationship", "ParentObjectApiName", "CONCAT(ChildObjectApiName, '.', FieldApiName, ' -> ', ParentObjectApiName)", "1 = 1"},
	{"_System_PageLayout", "ObjectApiName", "CONCAT(ObjectApiName, '/', LayoutName)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ApprovalPageLayout", "ObjectApiName", "CONCAT(ObjectApiName, '/', LayoutName)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ObjectFieldMapping", "SourceObject", "CONCAT(SourceObject, ' -> ', TargetObject, '/', SourceField)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ObjectFieldMapping", "TargetObject", "CONCAT(SourceObject, ' -> ', TargetObject, '/', TargetField)", "COALESCE(IsDeleted, 0) = 0"},
	{"FlowDefinition", "TriggerObjectOrEvent", "DeveloperName", "COALESCE(IsDeleted, 0) = 0"},
	{"SharingRule", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
	{"ProfileLayout", "ObjectApiName", "CONCAT(ObjectApiName, '/', ProfileId, '/', LayoutId)", "1 = 1"},
	{"_System_ApprovalProcess", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
	{"ProcessDefinition", "TableEnumOrId", "CONCAT(TableEnumOrId, '/', COALESCE(DeveloperName, Name))", "1 = 1"},
	{"_System_LayoutAssignment", "ObjectApiName", "CONCAT(ObjectApiName, '/', LayoutId, '/', COALESCE(ProfileId, '*'))", "1 = 1"},
	{"_System_WorkflowAlert", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "1 = 1"},
	{"Report", "SourceObject", "CONCAT(SourceObject, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ConnectorInboundRemoteIDEvidence", "SourceObject", "CONCAT(ConnectorNamespace, '/', SourceObject, '/', RemoteIDField)", "1 = 1"},
	{"_System_ExternalDeliveryAttempt", "SourceObjectApiName", "CONCAT(Channel, '/', COALESCE(SourceObjectApiName, ''), '/', COALESCE(SourceRecordId, ''))", "COALESCE(SourceObjectApiName, '') <> ''"},
	{"_System_RecycleBin", "DeleteRootObjectApiName", "CONCAT(DeleteOperationId, '/', COALESCE(DeleteRootObjectApiName, ''), '/', COALESCE(DeleteRootRecordId, ''))", "COALESCE(DeleteRootObjectApiName, '') <> ''"},
	{"_System_RecycleBin", "CascadeParentObjectApiName", "CONCAT(DeleteOperationId, '/', COALESCE(CascadeParentObjectApiName, ''), '/', COALESCE(CascadeParentRecordId, ''))", "COALESCE(CascadeParentObjectApiName, '') <> ''"},
}

// Surfaces in this list have no trustworthy L0 component owner today. Rows
// pointing at non-L0 objects are contamination; rows pointing at L0 objects are
// ownership gaps because object ownership does not prove child metadata
// ownership.
var l0UnownedObjectComponentSurfaces = []l0ObjectReferenceSurface{
	{"QueueSobject", "SobjectType", "CONCAT(QueueId, '/', SobjectType)", "1 = 1"},
	{"SharingRule", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
	{"ProfileLayout", "ObjectApiName", "CONCAT(ObjectApiName, '/', ProfileId, '/', LayoutId)", "1 = 1"},
	{"_System_ApprovalPageLayout", "ObjectApiName", "CONCAT(ObjectApiName, '/', LayoutName)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ApprovalProcess", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
	{"ProcessDefinition", "TableEnumOrId", "CONCAT(TableEnumOrId, '/', COALESCE(DeveloperName, Name))", "1 = 1"},
	{"_System_ObjectFieldMapping", "SourceObject", "CONCAT(SourceObject, ' -> ', TargetObject, '/', SourceField)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ObjectFieldMapping", "TargetObject", "CONCAT(SourceObject, ' -> ', TargetObject, '/', TargetField)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_WorkflowAlert", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "1 = 1"},
	{"Report", "SourceObject", "CONCAT(SourceObject, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
}

var l0FieldReferenceSurfaces = []l0FieldReferenceSurface{
	{"_System_Object", "ApiName", "PathField", "CONCAT(ApiName, ' path=', COALESCE(PathField, ''))", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_Object", "ApiName", "DisplayNameField", "CONCAT(ApiName, ' display=', COALESCE(DisplayNameField, ''))", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ValidationRule", "ObjectApiName", "ErrorDisplayField", "CONCAT(ObjectApiName, '/', Name, ' field=', COALESCE(ErrorDisplayField, ''))", "1 = 1"},
	{"_System_ActionSchema", "TargetObject", "TargetFieldAPIName", "CONCAT(StandardActionId, '/', APIName, ' field=', COALESCE(TargetFieldAPIName, ''))", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ActionSchema", "MappingSourceObject", "PrefillSourceFieldAPIName", "CONCAT(StandardActionId, '/', APIName, ' prefill=', COALESCE(PrefillSourceFieldAPIName, ''))", "COALESCE(IsDeleted, 0) = 0 AND COALESCE(PrefillSourceFieldAPIName, '') <> ''"},
	{"_System_ActionFieldDefault", "TargetObject", "FieldAPIName", "CONCAT(StandardActionId, '/', TargetObject, '/', FieldAPIName)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ObjectFieldMapping", "SourceObject", "SourceField", "CONCAT(SourceObject, '.', SourceField, ' -> ', TargetObject, '.', TargetField)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ObjectFieldMapping", "TargetObject", "TargetField", "CONCAT(SourceObject, '.', SourceField, ' -> ', TargetObject, '.', TargetField)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ObjectFieldMapping", "SourceObject", "SourceLookupField", "CONCAT(SourceObject, '.', COALESCE(SourceLookupField, ''), ' lookup -> ', TargetObject)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ConnectorInboundProjectionSnapshot", "ObjectKind", "SourceField", "CONCAT(OwnerPackName, '/', CompanionPackName, '/', ObjectKind, '.', SourceField)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_RecycleBin", "CascadeParentObjectApiName", "CascadeFieldApiName", "CONCAT(DeleteOperationId, '/', COALESCE(CascadeParentObjectApiName, ''), '.', COALESCE(CascadeFieldApiName, ''))", "COALESCE(CascadeParentObjectApiName, '') <> '' AND COALESCE(CascadeFieldApiName, '') <> ''"},
}

var l0MissingOwnerPackSurfaceChecks = []struct {
	table      string
	sampleExpr string
	where      string
}{
	{"_System_RecordType", "Name", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ValidationRule", "CONCAT(ObjectApiName, '/', Name)", "1 = 1"},
	{"_System_ReportType", "FullName", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_Action", "CONCAT(ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_StandardAction", "Name", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ConnectorRateLimit", "EndpointClass", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_ConnectorCalloutProfile", "ProfileName", "COALESCE(IsDeleted, 0) = 0"},
}

var l0ProfileReferenceSurfaces = []l0ParentReferenceSurface{
	{"ProfileLayout", "ProfileId", "CONCAT(ProfileId, '/', ObjectApiName, '/', LayoutId)", "1 = 1"},
	{"_System_LayoutAssignment", "ProfileId", "CONCAT(ProfileId, '/', ObjectApiName, '/', LayoutId)", "1 = 1"},
	{"_System_ConnectedAppAccess", "ProfileId", "CONCAT(ProfileId, '/', ConnectedAppId)", "1 = 1"},
	{"ProfileCustomPermission", "ProfileId", "CONCAT(ProfileId, '/', CustomPermissionId)", "1 = 1"},
	{"ProfileTabSetting", "ProfileId", "CONCAT(ProfileId, '/', TabName)", "1 = 1"},
	{"ProfileAppVisibility", "ProfileId", "CONCAT(ProfileId, '/', AppId)", "1 = 1"},
}

var l0PermissionSetReferenceSurfaces = []l0ParentReferenceSurface{
	{"_System_ConnectedAppAccess", "PermissionSetId", "CONCAT(PermissionSetId, '/', ConnectedAppId)", "1 = 1"},
	{"_System_PermissionAuthoringCommand", "CanonicalPermissionSetId", "CONCAT(j.CommandId, '/', j.CanonicalPermissionSetId)", "j.CanonicalPermissionSetId IS NOT NULL"},
	{"_System_PermissionAuthoringConvergence", "CanonicalPermissionSetId", "CONCAT(j.CommandId, '/', j.CanonicalPermissionSetId)", "1 = 1"},
	{"_System_PermissionSetAssignmentPolicyClaim", "PermissionSetId", "CONCAT(PermissionSetId, '/', OwnerPack, '/', PolicyKey)", "1 = 1"},
	{"_System_PermissionSetAssignmentPolicySelector", "PermissionSetId", "CONCAT(PermissionSetId, '/', ProfileName)", "1 = 1"},
	{"_System_PermissionSetAssignmentPolicyTargetState", "PermissionSetId", "CONCAT(PermissionSetId, '/', TargetDomain)", "1 = 1"},
	{"PermissionSetCustomPermission", "PermissionSetId", "CONCAT(PermissionSetId, '/', CustomPermissionId)", "1 = 1"},
	{"PermissionSetRecordTypeVisibility", "PermissionSetId", "CONCAT(PermissionSetId, '/', RecordTypeId)", "1 = 1"},
	{"PermissionSetTabSetting", "PermissionSetId", "CONCAT(PermissionSetId, '/', TabName)", "1 = 1"},
	{"PermissionSetAppVisibility", "PermissionSetId", "CONCAT(PermissionSetId, '/', AppId)", "1 = 1"},
}

var l0PermissionReferenceSurfaces = []l0ParentReferenceSurface{
	{"_System_ActionInputGuard", "RequiredPermission", "CONCAT(StandardActionId, '/', GuardKey, ' permission=', COALESCE(RequiredPermission, ''))", "COALESCE(IsDeleted, 0) = 0"},
	{"_System_SetupPage", "PermissionRequired", "CONCAT(ComponentName, ' permission=', COALESCE(PermissionRequired, ''))", "COALESCE(IsEnabled, 1) = 1"},
	{"_System_Permission", "RequiresPermission", "CONCAT(ApiName, ' requires ', COALESCE(RequiresPermission, ''))", "COALESCE(IsActive, 1) = 1"},
	{"_System_MutingPermissionSetPermission", "PermissionApiName", "CONCAT(MutingPermissionSetId, '/', PermissionApiName)", "1 = 1"},
}

func (r *l0DiagnosticReport) addContamination(format string, args ...any) {
	r.contamination = append(r.contamination, fmt.Sprintf(format, args...))
}

func (r *l0DiagnosticReport) addOwnershipGap(format string, args ...any) {
	r.ownershipGaps = append(r.ownershipGaps, fmt.Sprintf(format, args...))
}

func (r *l0DiagnosticReport) err() error {
	if len(r.contamination) == 0 && len(r.ownershipGaps) == 0 {
		return nil
	}
	var b strings.Builder
	b.WriteString("clean L0 verification failed:")
	if len(r.contamination) > 0 {
		b.WriteString("\n  contamination:")
		for _, msg := range r.contamination {
			b.WriteString("\n    - ")
			b.WriteString(msg)
		}
	}
	if len(r.ownershipGaps) > 0 {
		b.WriteString("\n  ownership_gap:")
		for _, msg := range r.ownershipGaps {
			b.WriteString("\n    - ")
			b.WriteString(msg)
		}
	}
	return fmt.Errorf("%s", b.String())
}

// VerifyL0 verifies that the database contains only the generic platform
// substrate and runtime-required platform packs. It is intentionally stricter
// than normal readiness: any installed app pack or app-shaped metadata is a
// failure.
//
// VerifyL0 is intentionally bounded to L0 substrate + RuntimeRequiredPacks().
// It must not run tenant / app-aware validators over arbitrary live schemas.
func VerifyL0(ctx context.Context, db *database.TiDBConnection) error {
	if err := schemaProvisioner.VerifySchemaReadiness(db); err != nil {
		return fmt.Errorf("schema readiness: %w", err)
	}
	if _, err := schemaProvisioner.RunAssertions(db, true); err != nil {
		return fmt.Errorf("assertions: %w", err)
	}
	for _, p := range packs.RuntimeRequiredPacks() {
		if err := p.Verify(ctx, packs.VerifyDeps{DB: db.DB(), DeepChecks: false}); err != nil {
			return fmt.Errorf("pack %s verify: %w", p.Name(), err)
		}
	}
	if err := VerifyStandardFieldSpecDrift(db.DB()); err != nil {
		return fmt.Errorf("standard-field drift check: %w", err)
	}
	if err := VerifyOrphanSystemFields(db.DB()); err != nil {
		return fmt.Errorf("orphan field check: %w", err)
	}
	if err := verifyNoL0Contamination(ctx, db.DB()); err != nil {
		return err
	}
	return nil
}

// AllowedL0ObjectAPINames returns the positive L0 object oracle keyed by
// lower-case API name for case-insensitive SQL comparisons.
func AllowedL0ObjectAPINames(_ context.Context, _ *sql.DB) (map[string]struct{}, error) {
	oracle, err := buildL0Oracle()
	if err != nil {
		return nil, err
	}
	out := make(map[string]struct{}, len(oracle.objectNames))
	for _, name := range oracle.objectNamesSorted {
		out[name] = struct{}{}
	}
	return out, nil
}

func verifyNoL0Contamination(ctx context.Context, db *sql.DB) error {
	oracle, err := buildL0Oracle()
	if err != nil {
		return err
	}
	report := &l0DiagnosticReport{}

	if err := checkInstalledPackContamination(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkOwnerPackSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkStandardActionPayloadReferences(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkStandardActionSatellites(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkCanonicalFieldSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkEmbeddedObjectFieldReferences(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkDeepPayloadReferenceSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkObjectBackedSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkListViewComponents(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkRelationshipComponents(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkPageLayoutComponents(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkLayoutAssignmentComponents(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkOwnerlessRuntimeManifestSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkPermissionJunctions(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkPermissionComponentSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkPermissionReferenceSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkReferenceSurfaceInventory(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkConnectedApps(ctx, db, oracle, report); err != nil {
		return err
	}

	return report.err()
}

func checkInstalledPackContamination(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	// Use listInstalledSourceBackedPacks (Category IN app-pack, connector-pack)
	// rather than the old app-pack-only filter. A connector-pack install that
	// crashes mid-flight leaves a non-deleted row with Status = 'installing' or
	// 'failed'; the app-pack-only filter silently missed those rows, letting a
	// dirty L0 pass the contamination gate.
	installedRows, err := listInstalledSourceBackedPacks(ctx, db)
	if err != nil {
		return err
	}
	if len(installedRows) > 0 {
		samples := make([]string, 0, minInt(len(installedRows), l0SampleLimit))
		for i, row := range installedRows {
			if i >= l0SampleLimit {
				break
			}
			samples = append(samples, fmt.Sprintf("%s(category=%s, status=%s, desiredHash=%s, appliedHash=%s)",
				row.Name, row.Category, row.Status, nullStringSample(row.DesiredPayloadHash), nullStringSample(row.AppliedPayloadHash)))
		}
		report.addContamination("installed source-backed pack rows present: count=%d sample=%s", len(installedRows), strings.Join(samples, ", "))
	}

	predicate, args := notInPredicate("Name", oracle.runtimePacksSorted)
	where := "COALESCE(IsDeleted, 0) = 0 AND Status = 'active' AND " + predicate
	return addCountDiagnostic(ctx, db, report.addContamination,
		"active installed-pack rows outside runtime-required pack set",
		"SELECT COUNT(*) FROM _System_InstalledPack WHERE "+where,
		"SELECT Name FROM _System_InstalledPack WHERE "+where+" ORDER BY Name LIMIT 5",
		args...)
}

func checkOwnerPackSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	checks := []struct {
		table      string
		ownerExpr  string
		sampleExpr string
		where      string
	}{
		{"_System_RecordType", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', Name)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_ValidationRule", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', ObjectApiName, '/', Name)", "1 = 1"},
		{"_System_ReportType", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', FullName)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_Action", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_StandardAction", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', Name)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_Permission", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', ApiName)", "1 = 1"},
		{"_System_CustomPermission", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', ApiName)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_RecordTypePicklistValue", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', RecordTypeId, '/', FieldId, '/', `Value`)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_ConnectorRateLimit", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', EndpointClass)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_ConnectorCalloutProfile", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', ProfileName)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_EmailTemplate", "OwnerPack", "CONCAT(COALESCE(OwnerPack, ''), ':', DeveloperName)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_Object", "AccessPolicyRecordMutationPackName", "CONCAT(COALESCE(AccessPolicyRecordMutationPackName, ''), ':', ApiName)", "COALESCE(IsDeleted, 0) = 0"},
		{"_System_Object", "AccessPolicyLifecyclePackName", "CONCAT(COALESCE(AccessPolicyLifecyclePackName, ''), ':', ApiName)", "COALESCE(IsDeleted, 0) = 0"},
	}
	for _, check := range checks {
		baseWhere := check.where
		if check.table == "_System_ValidationRule" {
			var err error
			baseWhere, err = validationRuleLiveRowsPredicate(ctx, db)
			if err != nil {
				return err
			}
		}
		predicate, args := ownerOutsideRuntimePredicate(check.ownerExpr, oracle.runtimePacksSorted)
		where := baseWhere + " AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s rows owned by non-L0 packs", check.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", check.table, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", check.sampleExpr, check.table, where),
			args...); err != nil {
			return err
		}
	}
	if err := checkMissingOwnerPackSurfaces(ctx, db, report); err != nil {
		return err
	}
	if err := checkOwnerStampedDeclaredComponents(ctx, db, oracle, report); err != nil {
		return err
	}

	validationRuleWhere, err := validationRuleLiveRowsPredicate(ctx, db)
	if err != nil {
		return err
	}
	if err := checkRowsOutsideAllowedObjects(ctx, db, report, "_System_ValidationRule", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", validationRuleWhere, oracle); err != nil {
		return err
	}
	if err := checkRowsOutsideAllowedObjects(ctx, db, report, "_System_ReportType", "BaseObject", "FullName", "COALESCE(IsDeleted, 0) = 0", oracle); err != nil {
		return err
	}
	if err := checkRowsOutsideAllowedObjects(ctx, db, report, "_System_Action", "ObjectApiName", "CONCAT(ObjectApiName, '/', Name)", "COALESCE(IsDeleted, 0) = 0", oracle); err != nil {
		return err
	}
	if err := checkRowsOutsideAllowedObjects(ctx, db, report, "_System_Action", "TargetObject", "CONCAT(ObjectApiName, '/', Name, ' -> ', TargetObject)", "COALESCE(IsDeleted, 0) = 0", oracle); err != nil {
		return err
	}
	if err := checkSourceOwnedSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}

	return checkPermissionOwnershipGaps(ctx, db, oracle, report)
}

func checkOwnerStampedDeclaredComponents(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	checks := []struct {
		table      string
		kind       packs.ComponentKind
		from       string
		ownerExpr  string
		keyExpr    string
		sampleExpr string
		where      string
	}{
		{
			table:      "_System_RecordType",
			kind:       packs.ComponentKindRecordType,
			from:       "_System_RecordType rt LEFT JOIN _System_Object o ON o.Id = rt.ObjectId",
			ownerExpr:  "rt.OwnerPack",
			keyExpr:    "CONCAT(COALESCE(o.ApiName, ''), '/', rt.Name)",
			sampleExpr: "CONCAT(COALESCE(rt.OwnerPack, ''), ':', COALESCE(o.ApiName, '<missing-object>'), '/', rt.Name)",
			where:      "COALESCE(rt.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_ValidationRule",
			kind:       packs.ComponentKindValidationRule,
			from:       "_System_ValidationRule v",
			ownerExpr:  "v.OwnerPack",
			keyExpr:    "CONCAT(v.ObjectApiName, '/', v.Name)",
			sampleExpr: "CONCAT(COALESCE(v.OwnerPack, ''), ':', v.ObjectApiName, '/', v.Name)",
			where:      "1 = 1",
		},
		{
			table:      "_System_ReportType",
			kind:       packs.ComponentKindReportType,
			from:       "_System_ReportType r",
			ownerExpr:  "r.OwnerPack",
			keyExpr:    "r.FullName",
			sampleExpr: "CONCAT(COALESCE(r.OwnerPack, ''), ':', r.FullName)",
			where:      "COALESCE(r.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_Action",
			kind:       packs.ComponentKindUIAction,
			from:       "_System_Action a",
			ownerExpr:  "a.OwnerPack",
			keyExpr:    "CONCAT(a.ObjectApiName, '/', a.Name)",
			sampleExpr: "CONCAT(COALESCE(a.OwnerPack, ''), ':', a.ObjectApiName, '/', a.Name)",
			where:      "COALESCE(a.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_StandardAction",
			kind:       packs.ComponentKindInvocableAction,
			from:       "_System_StandardAction a",
			ownerExpr:  "a.OwnerPack",
			keyExpr:    "a.Name",
			sampleExpr: "CONCAT(COALESCE(a.OwnerPack, ''), ':', a.Name)",
			where:      "COALESCE(a.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_Permission",
			kind:       packs.ComponentKindPermission,
			from:       "_System_Permission p",
			ownerExpr:  "p.OwnerPack",
			keyExpr:    "p.ApiName",
			sampleExpr: "CONCAT(COALESCE(p.OwnerPack, ''), ':', p.ApiName)",
			where:      "1 = 1",
		},
		{
			table:      "_System_CustomPermission",
			kind:       packs.ComponentKindCustomPermission,
			from:       "_System_CustomPermission cp",
			ownerExpr:  "cp.OwnerPack",
			keyExpr:    "cp.ApiName",
			sampleExpr: "CONCAT(COALESCE(cp.OwnerPack, ''), ':', cp.ApiName)",
			where:      "COALESCE(cp.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_RecordTypePicklistValue",
			kind:       packs.ComponentKindRecordTypePicklistValue,
			from:       "_System_RecordTypePicklistValue rtpv JOIN _System_RecordType rt ON rt.Id = rtpv.RecordTypeId JOIN _System_Field f ON f.Id = rtpv.FieldId JOIN _System_Object o ON o.Id = rt.ObjectId AND o.Id = f.ObjectId",
			ownerExpr:  "rtpv.OwnerPack",
			keyExpr:    "CONCAT(COALESCE(o.ApiName, ''), '/', rt.Name, '/', f.ApiName)",
			sampleExpr: "CONCAT(COALESCE(rtpv.OwnerPack, ''), ':', rtpv.RecordTypeId, '/', rtpv.FieldId, '/', rtpv.`Value`)",
			where:      "COALESCE(rtpv.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_ConnectorRateLimit",
			kind:       packs.ComponentKindConnectorRateLimit,
			from:       "_System_ConnectorRateLimit crl",
			ownerExpr:  "crl.OwnerPack",
			keyExpr:    "CONCAT(COALESCE(crl.OwnerPack, ''), '/', crl.EndpointClass)",
			sampleExpr: "CONCAT(COALESCE(crl.OwnerPack, ''), ':', crl.EndpointClass)",
			where:      "COALESCE(crl.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_ConnectorInboundHandlerConfig",
			kind:       packs.ComponentKindConnectorInboundHandlerConfig,
			from:       "_System_ConnectorInboundHandlerConfig cihc",
			ownerExpr:  "cihc.Namespace",
			keyExpr:    "CONCAT(COALESCE(cihc.Namespace, ''), ':', cihc.CapabilityKind)",
			sampleExpr: "CONCAT(COALESCE(cihc.Namespace, ''), ':', cihc.CapabilityKind)",
			where:      "COALESCE(cihc.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_ConnectorCalloutProfile",
			kind:       packs.ComponentKindConnectorCalloutProfile,
			from:       "_System_ConnectorCalloutProfile ccp",
			ownerExpr:  "ccp.OwnerPack",
			keyExpr:    "CONCAT(COALESCE(ccp.OwnerPack, ''), '/', ccp.ProfileName)",
			sampleExpr: "CONCAT(COALESCE(ccp.OwnerPack, ''), ':', ccp.ProfileName)",
			where:      "COALESCE(ccp.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_EmailTemplate",
			kind:       packs.ComponentKindEmailTemplate,
			from:       "_System_EmailTemplate et",
			ownerExpr:  "et.OwnerPack",
			keyExpr:    "et.DeveloperName",
			sampleExpr: "CONCAT(COALESCE(et.OwnerPack, ''), ':', et.DeveloperName)",
			where:      "COALESCE(et.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_Object.AccessPolicyRecordMutation",
			kind:       packs.ComponentKindObjectRecordMutationPolicy,
			from:       "_System_Object o",
			ownerExpr:  "o.AccessPolicyRecordMutationPackName",
			keyExpr:    "o.AccessPolicyRecordMutationPackBusinessKey",
			sampleExpr: "CONCAT(COALESCE(o.AccessPolicyRecordMutationPackName, ''), ':', o.ApiName)",
			where:      "COALESCE(o.IsDeleted, 0) = 0",
		},
		{
			table:      "_System_Object.AccessPolicyLifecycle",
			kind:       packs.ComponentKindObjectLifecyclePolicy,
			from:       "_System_Object o",
			ownerExpr:  "o.AccessPolicyLifecyclePackName",
			keyExpr:    "o.AccessPolicyLifecyclePackBusinessKey",
			sampleExpr: "CONCAT(COALESCE(o.AccessPolicyLifecyclePackName, ''), ':', o.ApiName)",
			where:      "COALESCE(o.IsDeleted, 0) = 0",
		},
	}

	for _, check := range checks {
		predicate, args := inPredicate(check.ownerExpr, oracle.runtimePacksSorted)
		rows, err := db.QueryContext(ctx, fmt.Sprintf(`
			SELECT %s, %s, %s
			FROM %s
			WHERE %s
			  AND COALESCE(%s, '') <> ''
			  AND %s`,
			check.ownerExpr, check.keyExpr, check.sampleExpr,
			check.from,
			check.where,
			check.ownerExpr,
			predicate,
		), args...)
		if err != nil {
			return fmt.Errorf("query %s declared runtime components: %w", check.table, err)
		}

		var count int
		var samples []string
		for rows.Next() {
			var ownerPack, businessKey, sample string
			if err := rows.Scan(&ownerPack, &businessKey, &sample); err != nil {
				rows.Close()
				return fmt.Errorf("scan %s declared runtime component: %w", check.table, err)
			}
			if oracle.hasDeclaredComponent(ownerPack, check.kind, businessKey) {
				continue
			}
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, sample)
			}
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return fmt.Errorf("iterate %s declared runtime components: %w", check.table, err)
		}
		rows.Close()

		if count > 0 {
			report.addOwnershipGap("%s rows stamped with runtime OwnerPack but missing declared component manifest entry: count=%d sample=%s",
				check.table, count, strings.Join(samples, ", "))
		}
	}
	return nil
}

func checkMissingOwnerPackSurfaces(ctx context.Context, db *sql.DB, report *l0DiagnosticReport) error {
	for _, check := range l0MissingOwnerPackSurfaceChecks {
		baseWhere := check.where
		if check.table == "_System_ValidationRule" {
			var err error
			baseWhere, err = validationRuleLiveRowsPredicate(ctx, db)
			if err != nil {
				return err
			}
		}
		where := baseWhere + " AND COALESCE(OwnerPack, '') = ''"
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows with empty OwnerPack", check.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", check.table, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", check.sampleExpr, check.table, where)); err != nil {
			return err
		}
	}
	return nil
}

func checkObjectBackedSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := checkRowsOutsideAllowedObjects(ctx, db, report, "_System_Object", "ApiName", "ApiName", "COALESCE(IsDeleted, 0) = 0", oracle); err != nil {
		return err
	}

	predicate, args := notInPredicate("o.ApiName", oracle.objectNamesSorted)
	where := "COALESCE(f.IsDeleted, 0) = 0 AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_Field rows whose parent object is outside the L0 object set",
		"SELECT COUNT(*) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+where,
		"SELECT CONCAT(o.ApiName, '.', f.ApiName) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	predicate, args = notInPredicate("TableName", oracle.tableNamesSorted)
	where = "COALESCE(IsDeleted, 0) = 0 AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_Table rows outside the L0 table set",
		"SELECT COUNT(*) FROM _System_Table WHERE "+where,
		"SELECT TableName FROM _System_Table WHERE "+where+" ORDER BY TableName LIMIT 5",
		args...); err != nil {
		return err
	}

	predicate, args = notInPredicate("TABLE_NAME", oracle.tableNamesSorted)
	where = "TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"physical tables outside the L0 table set",
		"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE "+where,
		"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE "+where+" ORDER BY TABLE_NAME LIMIT 5",
		args...); err != nil {
		return err
	}

	for _, check := range l0ObjectReferenceSurfaces {
		if err := checkRowsOutsideAllowedObjects(ctx, db, report, check.table, check.column, check.sampleExpr, check.where, oracle); err != nil {
			return err
		}
	}
	return nil
}

func checkCanonicalFieldSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Field rows missing parent _System_Object",
		`SELECT COUNT(*)
		   FROM _System_Field f
		   LEFT JOIN _System_Object o ON o.Id = f.ObjectId
		  WHERE COALESCE(f.IsDeleted, 0) = 0
		    AND o.Id IS NULL`,
		`SELECT f.ApiName
		   FROM _System_Field f
		   LEFT JOIN _System_Object o ON o.Id = f.ObjectId
		  WHERE COALESCE(f.IsDeleted, 0) = 0
		    AND o.Id IS NULL
		  ORDER BY f.ApiName LIMIT 5`); err != nil {
		return err
	}

	rows, err := db.QueryContext(ctx, `
		SELECT o.ApiName, f.ApiName
		FROM _System_Field f
		JOIN _System_Object o ON o.Id = f.ObjectId
		WHERE COALESCE(f.IsDeleted, 0) = 0
		  AND COALESCE(o.IsDeleted, 0) = 0`)
	if err != nil {
		return fmt.Errorf("query _System_Field canonical component rows: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var objectAPIName, fieldAPIName string
		if err := rows.Scan(&objectAPIName, &fieldAPIName); err != nil {
			return fmt.Errorf("scan _System_Field canonical component row: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		if _, ok := oracle.fieldKeys[fieldComponentKey(objectAPIName, fieldAPIName)]; ok {
			continue
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, fmt.Sprintf("%s.%s", objectAPIName, fieldAPIName))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_Field canonical component rows: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_Field rows under L0 objects outside the canonical L0 field set: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkListViewComponents(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT ObjectApiName, Label
		FROM _System_ListView`)
	if err != nil {
		return fmt.Errorf("query _System_ListView components: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var objectAPIName, label string
		if err := rows.Scan(&objectAPIName, &label); err != nil {
			return fmt.Errorf("scan _System_ListView component: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		if _, ok := oracle.listViewKeys[listViewComponentKey(objectAPIName, label)]; ok {
			continue
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, fmt.Sprintf("%s/%s", objectAPIName, label))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_ListView components: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_ListView rows on L0 objects without L0 component ownership: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkRelationshipComponents(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT ChildObjectApiName, FieldApiName, ParentObjectApiName
		FROM _System_Relationship`)
	if err != nil {
		return fmt.Errorf("query _System_Relationship components: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var childObject, fieldAPIName, parentObject string
		if err := rows.Scan(&childObject, &fieldAPIName, &parentObject); err != nil {
			return fmt.Errorf("scan _System_Relationship component: %w", err)
		}
		if !containsSetFold(oracle.objectNames, childObject) || !containsSetFold(oracle.objectNames, parentObject) {
			continue
		}
		if _, ok := oracle.relationshipKeys[relationshipComponentKey(childObject, fieldAPIName, parentObject)]; ok {
			continue
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, fmt.Sprintf("%s.%s -> %s", childObject, fieldAPIName, parentObject))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_Relationship components: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_Relationship rows outside the canonical L0 relationship set: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkPageLayoutComponents(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT Id, ObjectApiName, LayoutName, COALESCE(Source, ''), COALESCE(IsCustomized, 0), COALESCE(Config, ''), COALESCE(OwnerPack, '')
		FROM _System_PageLayout
		WHERE COALESCE(IsDeleted, 0) = 0`)
	if err != nil {
		return fmt.Errorf("query _System_PageLayout declared components: %w", err)
	}
	defer rows.Close()

	var gapCount, driftCount int
	var gapSamples, driftSamples []string
	for rows.Next() {
		var id, objectAPIName, layoutName, source, configJSON, dbOwnerPack string
		var isCustomized int
		if err := rows.Scan(&id, &objectAPIName, &layoutName, &source, &isCustomized, &configJSON, &dbOwnerPack); err != nil {
			return fmt.Errorf("scan _System_PageLayout declared component: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		if isAdminAuthoredPageLayoutSource(source, isCustomized != 0) {
			continue
		}

		// Prefer the explicit OwnerPack column written by the pack apply path.
		// Fall back to the Source-string heuristic for rows written before PR-B
		// lands and not yet re-applied (OwnerPack=NULL / empty).
		ownerPack := dbOwnerPack
		if ownerPack == "" {
			ownerPack = l0PageLayoutOwnerPack(source)
		}
		businessKey := packs.JoinComponentBusinessKey(objectAPIName, id)
		component, ok := oracle.declaredComponent(ownerPack, packs.ComponentKindPageLayout, businessKey)
		if ownerPack == "" || !ok {
			gapCount++
			if len(gapSamples) < l0SampleLimit {
				gapSamples = append(gapSamples, fmt.Sprintf("%s/%s source=%s", objectAPIName, layoutName, source))
			}
			continue
		}
		if component.PayloadHash == "" {
			continue
		}
		liveHash, err := canonicalL0PageLayoutPayloadHash(configJSON)
		if err != nil {
			gapCount++
			if len(gapSamples) < l0SampleLimit {
				gapSamples = append(gapSamples, fmt.Sprintf("%s/%s invalid-config", objectAPIName, layoutName))
			}
			continue
		}
		if liveHash != component.PayloadHash {
			driftCount++
			if len(driftSamples) < l0SampleLimit {
				driftSamples = append(driftSamples, fmt.Sprintf("%s/%s", objectAPIName, layoutName))
			}
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_PageLayout declared components: %w", err)
	}

	if gapCount > 0 {
		report.addOwnershipGap("_System_PageLayout rows on L0 objects without declared L0 component ownership: count=%d sample=%s",
			gapCount, strings.Join(gapSamples, ", "))
	}
	if driftCount > 0 {
		report.addContamination("_System_PageLayout rows whose declared L0 component payload drifted: count=%d sample=%s",
			driftCount, strings.Join(driftSamples, ", "))
	}
	return nil
}

func checkLayoutAssignmentComponents(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT j.Id,
		       j.ObjectApiName,
		       j.LayoutId,
		       COALESCE(j.RouteSource, ''),
		       COALESCE(j.OwnerPack, ''),
		       COALESCE(l.Source, ''),
		       COALESCE(l.IsCustomized, 0)
		FROM _System_LayoutAssignment j
		LEFT JOIN _System_PageLayout l ON l.Id = j.LayoutId`)
	if err != nil {
		return fmt.Errorf("query _System_LayoutAssignment declared components: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var id, objectAPIName, layoutID, routeSource, dbOwnerPack, layoutSource string
		var layoutCustomized int
		if err := rows.Scan(&id, &objectAPIName, &layoutID, &routeSource, &dbOwnerPack, &layoutSource, &layoutCustomized); err != nil {
			return fmt.Errorf("scan _System_LayoutAssignment declared component: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		// Prefer the explicit OwnerPack column on the assignment row.
		// Fall back to deriving ownership from the joined layout's Source column
		// for rows written before PR-B lands and not yet re-applied (OwnerPack=NULL / empty).
		ownerPack := dbOwnerPack
		if ownerPack == "" {
			if strings.EqualFold(strings.TrimSpace(routeSource), models.LayoutAssignmentRouteSourceManagedSeed) &&
				strings.EqualFold(strings.TrimSpace(layoutSource), "curated") &&
				layoutCustomized == 0 {
				ownerPack = (&packs.PlatformUXPack{}).Name()
			}
		}
		businessKey := packs.JoinComponentBusinessKey(objectAPIName, id)
		if ownerPack != "" && oracle.hasDeclaredComponent(ownerPack, packs.ComponentKindLayoutAssignment, businessKey) {
			continue
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, fmt.Sprintf("%s/%s route=%s", objectAPIName, layoutID, routeSource))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_LayoutAssignment declared components: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_LayoutAssignment rows on L0 objects without declared L0 component ownership: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func l0PageLayoutOwnerPack(source string) string {
	if strings.EqualFold(strings.TrimSpace(source), "curated") {
		return (&packs.PlatformUXPack{}).Name()
	}
	return ""
}

func isAdminAuthoredPageLayoutSource(source string, isCustomized bool) bool {
	source = strings.ToLower(strings.TrimSpace(source))
	return isCustomized || source == "admin_created" || source == "cloned" || source == "custom"
}

func canonicalL0PageLayoutPayloadHash(raw string) (string, error) {
	layout, err := layoutownership.DecodePageLayoutConfig([]byte(raw))
	if err != nil {
		return "", err
	}
	layout.IsDefault = false
	layout.IsDeleted = false
	layout.CreatedDate = time.Time{}
	layout.LastModifiedDate = time.Time{}
	payload, err := json.Marshal(layout)
	if err != nil {
		return "", err
	}
	sum := sha256.Sum256(payload)
	return hex.EncodeToString(sum[:]), nil
}

func checkOwnerlessRuntimeManifestSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range packs.OwnerlessRuntimeComponentSurfaces() {
		live, err := surface.LoadLiveRows(ctx, db)
		if err != nil {
			return err
		}
		checkExactDeclaredComponents(oracle, report, surface.Kind, surface.Name, live)
	}
	return nil
}

func checkExactDeclaredComponents(oracle *l0Oracle, report *l0DiagnosticReport, kind packs.ComponentKind, label string, live []packs.LiveComponent) {
	expected := map[string]packs.DeclaredComponent{}
	for _, component := range oracle.declaredComponents {
		if component.Kind == kind {
			expected[component.BusinessKey] = component
		}
	}

	var extraCount, driftCount int
	var extraSamples, driftSamples []string
	for _, row := range live {
		component, ok := expected[row.BusinessKey]
		if !ok {
			extraCount++
			if len(extraSamples) < l0SampleLimit {
				extraSamples = append(extraSamples, row.Sample)
			}
			continue
		}
		if component.PayloadHash != "" && row.PayloadHash != component.PayloadHash {
			driftCount++
			if len(driftSamples) < l0SampleLimit {
				driftSamples = append(driftSamples, row.Sample)
			}
		}
	}

	if extraCount > 0 {
		report.addOwnershipGap("%s rows outside the runtime component manifest: count=%d sample=%s",
			label, extraCount, strings.Join(extraSamples, ", "))
	}
	if driftCount > 0 {
		report.addContamination("%s rows whose runtime component payload drifted: count=%d sample=%s",
			label, driftCount, strings.Join(driftSamples, ", "))
	}
}

func checkEmbeddedObjectFieldReferences(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT ApiName, COALESCE(ListFields, '')
		FROM _System_Object
		WHERE COALESCE(IsDeleted, 0) = 0
		  AND ListFields IS NOT NULL
		  AND TRIM(ListFields) <> ''`)
	if err != nil {
		return fmt.Errorf("query _System_Object.ListFields references: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var objectAPIName, listFieldsJSON string
		if err := rows.Scan(&objectAPIName, &listFieldsJSON); err != nil {
			return fmt.Errorf("scan _System_Object.ListFields reference: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		fields, err := parseOptionalJSONStringArray(listFieldsJSON)
		if err != nil {
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, fmt.Sprintf("%s:invalid-listfields", objectAPIName))
			}
			continue
		}
		for _, fieldAPIName := range fields {
			if _, ok := oracle.fieldKeys[fieldComponentKey(objectAPIName, fieldAPIName)]; ok {
				continue
			}
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, fmt.Sprintf("%s.%s", objectAPIName, fieldAPIName))
			}
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_Object.ListFields references: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_Object ListFields under L0 objects outside the canonical L0 field set: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkDeepPayloadReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := checkObjectOwnershipPayloadFlags(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkFieldControllingFieldReferences(ctx, db, oracle, report); err != nil {
		return err
	}
	return checkRelationshipRelatedListFields(ctx, db, oracle, report)
}

// checkObjectOwnershipPayloadFlags enumerates peer column-scoped customized-bit
// diagnostics for _System_Object so VerifyL0 still reports L0 contamination
// even though it does not invoke the broader app/tenant-aware validators.
func checkObjectOwnershipPayloadFlags(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	predicate, args := inPredicate("ApiName", oracle.objectNamesSorted)
	where := "COALESCE(IsDeleted, 0) = 0 AND AppId IS NOT NULL AND TRIM(AppId) <> '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Object rows with AppId on L0 objects without declared component ownership",
		"SELECT COUNT(*) FROM _System_Object WHERE "+where,
		"SELECT CONCAT(ApiName, ' app=', AppId) FROM _System_Object WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "COALESCE(IsDeleted, 0) = 0 AND COALESCE(ListFieldsCustomized, 0) <> 0 AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Object rows with customized ListFields on L0 objects without declared component ownership",
		"SELECT COUNT(*) FROM _System_Object WHERE "+where,
		"SELECT ApiName FROM _System_Object WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "COALESCE(IsDeleted, 0) = 0 AND COALESCE(DefaultChildRowsCustomized, 0) = 0 AND DefaultChildRows IS NOT NULL AND JSON_LENGTH(DefaultChildRows) > 0 AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Object DefaultChildRows payload on L0 objects without declared component ownership",
		"SELECT COUNT(*) FROM _System_Object WHERE "+where,
		"SELECT ApiName FROM _System_Object WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "COALESCE(IsDeleted, 0) = 0 AND COALESCE(DefaultChildRowsCustomized, 0) <> 0 AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Object rows with customized DefaultChildRows on L0 objects without declared component ownership",
		"SELECT COUNT(*) FROM _System_Object WHERE "+where,
		"SELECT ApiName FROM _System_Object WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "COALESCE(IsDeleted, 0) = 0 AND COALESCE(ChildCloneRulesCustomized, 0) = 0 AND ChildCloneRules IS NOT NULL AND JSON_LENGTH(ChildCloneRules) > 0 AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Object ChildCloneRules payload on L0 objects without declared component ownership",
		"SELECT COUNT(*) FROM _System_Object WHERE "+where,
		"SELECT ApiName FROM _System_Object WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "COALESCE(IsDeleted, 0) = 0 AND COALESCE(ChildCloneRulesCustomized, 0) <> 0 AND " + predicate
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Object rows with customized ChildCloneRules on L0 objects without declared component ownership",
		"SELECT COUNT(*) FROM _System_Object WHERE "+where,
		"SELECT ApiName FROM _System_Object WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...)
}

func checkFieldControllingFieldReferences(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT o.ApiName, f.ApiName, f.ControllingField
		FROM _System_Field f
		JOIN _System_Object o ON o.Id = f.ObjectId
		WHERE COALESCE(f.IsDeleted, 0) = 0
		  AND COALESCE(o.IsDeleted, 0) = 0
		  AND f.ControllingField IS NOT NULL
		  AND TRIM(f.ControllingField) <> ''`)
	if err != nil {
		return fmt.Errorf("query _System_Field.ControllingField references: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var objectAPIName, fieldAPIName, controllingField string
		if err := rows.Scan(&objectAPIName, &fieldAPIName, &controllingField); err != nil {
			return fmt.Errorf("scan _System_Field.ControllingField reference: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		fieldObject, controllingAPIName := splitFieldPermissionField(objectAPIName, controllingField)
		if strings.EqualFold(strings.TrimSpace(fieldObject), strings.TrimSpace(objectAPIName)) {
			if _, ok := oracle.fieldKeys[fieldComponentKey(objectAPIName, controllingAPIName)]; ok {
				continue
			}
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, fmt.Sprintf("%s.%s controls=%s", objectAPIName, fieldAPIName, controllingField))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_Field.ControllingField references: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_Field rows whose ControllingField is outside the canonical L0 field set: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkRelationshipRelatedListFields(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT ChildObjectApiName, ParentObjectApiName, FieldApiName, COALESCE(RelatedListFields, '')
		FROM _System_Relationship
		WHERE RelatedListFields IS NOT NULL
		  AND TRIM(RelatedListFields) <> ''`)
	if err != nil {
		return fmt.Errorf("query _System_Relationship.RelatedListFields references: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var childObject, parentObject, fieldAPIName, rawFields string
		if err := rows.Scan(&childObject, &parentObject, &fieldAPIName, &rawFields); err != nil {
			return fmt.Errorf("scan _System_Relationship.RelatedListFields reference: %w", err)
		}
		if !containsSetFold(oracle.objectNames, childObject) || !containsSetFold(oracle.objectNames, parentObject) {
			continue
		}
		fields, err := parseOptionalJSONStringArray(rawFields)
		if err != nil {
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, fmt.Sprintf("%s.%s:invalid-related-list-fields", childObject, fieldAPIName))
			}
			continue
		}
		for _, rawField := range fields {
			fieldObject, relatedFieldAPIName := splitFieldPermissionField(childObject, rawField)
			if strings.EqualFold(strings.TrimSpace(fieldObject), strings.TrimSpace(childObject)) {
				if _, ok := oracle.fieldKeys[fieldComponentKey(childObject, relatedFieldAPIName)]; ok {
					continue
				}
			}
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, fmt.Sprintf("%s.%s relatedListField=%s", childObject, fieldAPIName, rawField))
			}
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_Relationship.RelatedListFields references: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_Relationship rows whose RelatedListFields are outside the canonical L0 field set: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkStandardActionSatellites(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	satellites := []struct {
		table      string
		sampleExpr string
	}{
		{"_System_ActionSchema", "CONCAT(s.StandardActionId, '/', s.Direction, '/', s.APIName)"},
		{"_System_ActionFieldDefault", "CONCAT(s.StandardActionId, '/', s.TargetObject, '/', s.FieldAPIName)"},
		{"_System_ActionInputGuard", "CONCAT(s.StandardActionId, '/', s.GuardKey)"},
		{"_System_StandardActionInternalAccessScope", "CONCAT(s.StandardActionId, '/', s.ObjectApiName, '/', s.Operation)"},
	}
	for _, satellite := range satellites {
		baseJoin := fmt.Sprintf("%s s LEFT JOIN _System_StandardAction a ON a.Id = s.StandardActionId", satellite.table)
		baseWhere := "COALESCE(s.IsDeleted, 0) = 0"

		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows missing parent _System_StandardAction", satellite.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND a.Id IS NULL", baseJoin, baseWhere),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s AND a.Id IS NULL ORDER BY 1 LIMIT 5", satellite.sampleExpr, baseJoin, baseWhere)); err != nil {
			return err
		}

		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows whose parent action has empty OwnerPack", satellite.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND a.Id IS NOT NULL AND COALESCE(a.OwnerPack, '') = ''", baseJoin, baseWhere),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s AND a.Id IS NOT NULL AND COALESCE(a.OwnerPack, '') = '' ORDER BY 1 LIMIT 5", satellite.sampleExpr, baseJoin, baseWhere)); err != nil {
			return err
		}

		predicate, args := ownerOutsideRuntimePredicate("a.OwnerPack", oracle.runtimePacksSorted)
		where := baseWhere + " AND a.Id IS NOT NULL AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s rows under non-L0 standard actions", satellite.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", baseJoin, where),
			fmt.Sprintf("SELECT CONCAT(COALESCE(a.OwnerPack, ''), ':', %s) FROM %s WHERE %s ORDER BY 1 LIMIT 5", satellite.sampleExpr, baseJoin, where),
			args...); err != nil {
			return err
		}
	}

	for _, check := range []struct {
		table      string
		column     string
		sampleExpr string
	}{
		{"_System_ActionSchema", "TargetObject", "CONCAT(StandardActionId, '/', APIName, ' -> ', TargetObject)"},
		{"_System_ActionSchema", "ReferenceTo", "CONCAT(StandardActionId, '/', APIName, ' ref=', ReferenceTo)"},
		{"_System_ActionSchema", "MappingSourceObject", "CONCAT(StandardActionId, '/', APIName, ' source=', MappingSourceObject)"},
		{"_System_ActionFieldDefault", "TargetObject", "CONCAT(StandardActionId, '/', TargetObject, '/', FieldAPIName)"},
		{"_System_ActionInputGuard", "RequiredObject", "CONCAT(StandardActionId, '/', GuardKey, ' object=', RequiredObject)"},
		{"_System_StandardActionInternalAccessScope", "ObjectApiName", "CONCAT(StandardActionId, '/', ObjectApiName, '/', Operation)"},
	} {
		if err := checkRowsOutsideAllowedObjects(ctx, db, report, check.table, check.column, check.sampleExpr, "COALESCE(IsDeleted, 0) = 0", oracle); err != nil {
			return err
		}
	}
	return nil
}

func checkStandardActionPayloadReferences(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT Name, COALESCE(TargetObjects, ''), COALESCE(RequiredPermissions, '')
		FROM _System_StandardAction
		WHERE COALESCE(IsDeleted, 0) = 0`)
	if err != nil {
		return fmt.Errorf("query _System_StandardAction payload references: %w", err)
	}
	defer rows.Close()

	var targetCount, permissionCount int
	var targetSamples, permissionSamples []string
	for rows.Next() {
		var name, targetObjectsJSON, requiredPermissionsJSON string
		if err := rows.Scan(&name, &targetObjectsJSON, &requiredPermissionsJSON); err != nil {
			return fmt.Errorf("scan _System_StandardAction payload references: %w", err)
		}

		targetObjects, err := parseOptionalJSONStringArray(targetObjectsJSON)
		if err != nil {
			report.addOwnershipGap("_System_StandardAction %s has non-array TargetObjects: %v", name, err)
			continue
		}
		for _, objectName := range targetObjects {
			if containsSetFold(oracle.objectNames, objectName) {
				continue
			}
			targetCount++
			if len(targetSamples) < l0SampleLimit {
				targetSamples = append(targetSamples, fmt.Sprintf("%s:%s", name, objectName))
			}
		}

		requiredPermissions, err := parseOptionalJSONStringArray(requiredPermissionsJSON)
		if err != nil {
			report.addOwnershipGap("_System_StandardAction %s has non-array RequiredPermissions: %v", name, err)
			continue
		}
		for _, permissionName := range requiredPermissions {
			if containsSetFold(oracle.permissionAPINames, permissionName) {
				continue
			}
			permissionCount++
			if len(permissionSamples) < l0SampleLimit {
				permissionSamples = append(permissionSamples, fmt.Sprintf("%s:%s", name, permissionName))
			}
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_StandardAction payload references: %w", err)
	}

	if targetCount > 0 {
		report.addContamination("_System_StandardAction TargetObjects outside the L0 object set: count=%d sample=%s",
			targetCount, strings.Join(targetSamples, ", "))
	}
	if permissionCount > 0 {
		report.addContamination("_System_StandardAction RequiredPermissions outside the platform permission catalog: count=%d sample=%s",
			permissionCount, strings.Join(permissionSamples, ", "))
	}
	return nil
}

func checkPermissionJunctions(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	profilePredicate, profileArgs := notInPredicate("ProfileId", oracle.profileIDsSorted)
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_ProfilePermission rows outside the L0 profile set",
		"SELECT COUNT(*) FROM _System_ProfilePermission WHERE "+profilePredicate,
		"SELECT CONCAT(ProfileId, '/', PermissionApiName) FROM _System_ProfilePermission WHERE "+profilePredicate+" ORDER BY 1 LIMIT 5",
		profileArgs...); err != nil {
		return err
	}

	for _, table := range []string{"_System_ProfilePermission", "_System_PermissionSetPermission"} {
		predicate, args := ownerOutsideRuntimePredicate("p.OwnerPack", oracle.runtimePacksSorted)
		where := predicate
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s grants to non-L0 permissions", table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s j JOIN _System_Permission p ON p.ApiName = j.PermissionApiName WHERE %s", table, where),
			fmt.Sprintf("SELECT CONCAT(j.PermissionApiName, ' owner=', COALESCE(p.OwnerPack, '')) FROM %s j JOIN _System_Permission p ON p.ApiName = j.PermissionApiName WHERE %s ORDER BY 1 LIMIT 5", table, where),
			args...); err != nil {
			return err
		}

		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s grants missing _System_Permission owner row", table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s j LEFT JOIN _System_Permission p ON p.ApiName = j.PermissionApiName WHERE p.ApiName IS NULL", table),
			fmt.Sprintf("SELECT j.PermissionApiName FROM %s j LEFT JOIN _System_Permission p ON p.ApiName = j.PermissionApiName WHERE p.ApiName IS NULL ORDER BY 1 LIMIT 5", table)); err != nil {
			return err
		}

		predicate, args = notInPredicate("j.PermissionApiName", oracle.permissionsSorted)
		where = "COALESCE(p.OwnerPack, '') = '' AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s grants to permissions with empty OwnerPack outside the platform catalog", table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s j JOIN _System_Permission p ON p.ApiName = j.PermissionApiName WHERE %s", table, where),
			fmt.Sprintf("SELECT j.PermissionApiName FROM %s j JOIN _System_Permission p ON p.ApiName = j.PermissionApiName WHERE %s ORDER BY 1 LIMIT 5", table, where),
			args...); err != nil {
			return err
		}
	}
	return nil
}

func checkPermissionComponentSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, check := range []struct {
		table      string
		parentCol  string
		sampleExpr string
		where      string
	}{
		{"ObjectPermissions", "ParentId", "CONCAT(j.SobjectType, '/', j.ParentId)", "COALESCE(j.IsDeleted, 0) = 0"},
		{"FieldPermissions", "ParentId", "CONCAT(j.SobjectType, '.', j.Field, '/', j.ParentId)", "COALESCE(j.IsDeleted, 0) = 0"},
		{"_System_PermissionSetPermission", "PermissionSetId", "CONCAT(j.PermissionApiName, '/', j.PermissionSetId)", "1 = 1"},
	} {
		if err := checkPermissionSetParentScope(ctx, db, oracle, report, check.table, check.parentCol, check.sampleExpr, check.where); err != nil {
			return err
		}
	}
	return checkFieldPermissionCanonicalFields(ctx, db, oracle, report)
}

func checkPermissionSetParentScope(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport, table, parentCol, sampleExpr, baseWhere string) error {
	baseJoin := fmt.Sprintf("%s j LEFT JOIN PermissionSet ps ON ps.Id = j.%s", table, parentCol)

	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		fmt.Sprintf("%s rows missing parent PermissionSet", table),
		fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND ps.Id IS NULL", baseJoin, baseWhere),
		fmt.Sprintf("SELECT %s FROM %s WHERE %s AND ps.Id IS NULL ORDER BY 1 LIMIT 5", sampleExpr, baseJoin, baseWhere)); err != nil {
		return err
	}

	profilePredicate, args := inPredicate("ps.ProfileId", oracle.profileIDsSorted)
	where := baseWhere + " AND ps.Id IS NOT NULL AND NOT (COALESCE(ps.IsOwnedByProfile, 0) = 1 AND COALESCE(ps.IsDeleted, 0) = 0 AND " + profilePredicate + ")"
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		fmt.Sprintf("%s rows whose parent PermissionSet is outside the L0 profile-owned set", table),
		fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", baseJoin, where),
		fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", sampleExpr, baseJoin, where),
		args...)
}

func checkFieldPermissionCanonicalFields(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT ParentId, SobjectType, Field
		FROM FieldPermissions
		WHERE COALESCE(IsDeleted, 0) = 0`)
	if err != nil {
		return fmt.Errorf("query FieldPermissions canonical fields: %w", err)
	}
	defer rows.Close()

	var mismatchCount, nonCanonicalCount int
	var mismatchSamples, nonCanonicalSamples []string
	for rows.Next() {
		var parentID, sobjectType, rawField string
		if err := rows.Scan(&parentID, &sobjectType, &rawField); err != nil {
			return fmt.Errorf("scan FieldPermissions canonical field: %w", err)
		}
		fieldObject, fieldAPIName := splitFieldPermissionField(sobjectType, rawField)
		if !strings.EqualFold(strings.TrimSpace(fieldObject), strings.TrimSpace(sobjectType)) {
			mismatchCount++
			if len(mismatchSamples) < l0SampleLimit {
				mismatchSamples = append(mismatchSamples, fmt.Sprintf("%s field=%s parent=%s", sobjectType, rawField, parentID))
			}
			continue
		}
		if !containsSetFold(oracle.objectNames, sobjectType) {
			continue
		}
		if _, ok := oracle.fieldKeys[fieldComponentKey(sobjectType, fieldAPIName)]; ok {
			continue
		}
		nonCanonicalCount++
		if len(nonCanonicalSamples) < l0SampleLimit {
			nonCanonicalSamples = append(nonCanonicalSamples, fmt.Sprintf("%s.%s parent=%s", sobjectType, fieldAPIName, parentID))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate FieldPermissions canonical fields: %w", err)
	}

	if mismatchCount > 0 {
		report.addOwnershipGap("FieldPermissions rows whose Field object prefix does not match SobjectType: count=%d sample=%s",
			mismatchCount, strings.Join(mismatchSamples, ", "))
	}
	if nonCanonicalCount > 0 {
		report.addOwnershipGap("FieldPermissions rows under L0 objects outside the canonical L0 field set: count=%d sample=%s",
			nonCanonicalCount, strings.Join(nonCanonicalSamples, ", "))
	}
	return nil
}

func checkReferenceSurfaceInventory(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := checkUnownedObjectComponentSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkProfileInventory(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkFieldReferenceSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkFieldDependencyComponents(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkProfileReferenceSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkPermissionSetInventory(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkPermissionSetAssignmentInventory(ctx, db, report); err != nil {
		return err
	}
	if err := checkPermissionSetReferenceSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkQueueReferenceSurfaces(ctx, db, report); err != nil {
		return err
	}
	if err := checkPermissionSetGroupSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkConnectedAppAccessSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkConnectedAppDeclarationSurfaces(ctx, db, report); err != nil {
		return err
	}
	if err := checkConnectedAppStageSurfaces(ctx, db, report); err != nil {
		return err
	}
	if err := checkCustomPermissionGrantSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkTabAndAppVisibilitySurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkProcessComponentSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkLayoutReferenceSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkLayoutHistorySurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	if err := checkAutoNumberCounterSurfaces(ctx, db, oracle, report); err != nil {
		return err
	}
	return checkRecordTypeReferenceSurfaces(ctx, db, oracle, report)
}

func checkUnownedObjectComponentSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range l0UnownedObjectComponentSurfaces {
		if err := checkRowsOnAllowedObjectsWithoutL0Ownership(ctx, db, report, surface, oracle); err != nil {
			return err
		}
	}
	return nil
}

func checkFieldReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range l0FieldReferenceSurfaces {
		where := surface.where
		if surface.table == "_System_ValidationRule" {
			var err error
			where, err = validationRuleLiveRowsPredicate(ctx, db)
			if err != nil {
				return err
			}
		}
		rows, err := db.QueryContext(ctx, fmt.Sprintf(`
			SELECT %s, %s, %s
			FROM %s
			WHERE %s
			  AND %s IS NOT NULL AND TRIM(%s) <> ''
			  AND %s IS NOT NULL AND TRIM(%s) <> ''`,
			surface.objectColumn, surface.fieldColumn, surface.sampleExpr,
			surface.table,
			where,
			surface.objectColumn, surface.objectColumn,
			surface.fieldColumn, surface.fieldColumn,
		))
		if err != nil {
			return fmt.Errorf("query %s.%s component references: %w", surface.table, surface.fieldColumn, err)
		}

		var mismatchCount, nonCanonicalCount int
		var mismatchSamples, nonCanonicalSamples []string
		for rows.Next() {
			var objectAPIName, rawField, sample string
			if err := rows.Scan(&objectAPIName, &rawField, &sample); err != nil {
				rows.Close()
				return fmt.Errorf("scan %s.%s component reference: %w", surface.table, surface.fieldColumn, err)
			}
			if !containsSetFold(oracle.objectNames, objectAPIName) {
				continue
			}
			fieldObject, fieldAPIName := splitFieldPermissionField(objectAPIName, rawField)
			if !strings.EqualFold(strings.TrimSpace(fieldObject), strings.TrimSpace(objectAPIName)) {
				mismatchCount++
				if len(mismatchSamples) < l0SampleLimit {
					mismatchSamples = append(mismatchSamples, sample)
				}
				continue
			}
			if _, ok := oracle.fieldKeys[fieldComponentKey(objectAPIName, fieldAPIName)]; ok {
				continue
			}
			nonCanonicalCount++
			if len(nonCanonicalSamples) < l0SampleLimit {
				nonCanonicalSamples = append(nonCanonicalSamples, sample)
			}
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return fmt.Errorf("iterate %s.%s component references: %w", surface.table, surface.fieldColumn, err)
		}
		rows.Close()

		if mismatchCount > 0 {
			report.addOwnershipGap("%s rows whose %s object prefix does not match %s: count=%d sample=%s",
				surface.table, surface.fieldColumn, surface.objectColumn, mismatchCount, strings.Join(mismatchSamples, ", "))
		}
		if nonCanonicalCount > 0 {
			report.addOwnershipGap("%s rows whose %s is outside the canonical L0 field set: count=%d sample=%s",
				surface.table, surface.fieldColumn, nonCanonicalCount, strings.Join(nonCanonicalSamples, ", "))
		}
	}
	return nil
}

func checkFieldDependencyComponents(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	rows, err := db.QueryContext(ctx, `
		SELECT d.Id,
		       co.ApiName, cf.ApiName,
		       dep_o.ApiName, dep_f.ApiName
		FROM _System_FieldDependency d
		LEFT JOIN _System_Field cf ON cf.Id = d.ControllingFieldId
		LEFT JOIN _System_Object co ON co.Id = cf.ObjectId
		LEFT JOIN _System_Field dep_f ON dep_f.Id = d.DependentFieldId
		LEFT JOIN _System_Object dep_o ON dep_o.Id = dep_f.ObjectId`)
	if err != nil {
		return fmt.Errorf("query _System_FieldDependency component references: %w", err)
	}
	defer rows.Close()

	var missingCount, outsideObjectCount, nonCanonicalCount, l0DependencyCount int
	var missingSamples, outsideObjectSamples, nonCanonicalSamples, l0DependencySamples []string
	for rows.Next() {
		var id string
		var ctrlObject, ctrlField, depObject, depField sql.NullString
		if err := rows.Scan(&id, &ctrlObject, &ctrlField, &depObject, &depField); err != nil {
			return fmt.Errorf("scan _System_FieldDependency component reference: %w", err)
		}
		if !ctrlObject.Valid || !ctrlField.Valid || !depObject.Valid || !depField.Valid {
			missingCount++
			if len(missingSamples) < l0SampleLimit {
				missingSamples = append(missingSamples, id)
			}
			continue
		}

		ctrlL0 := containsSetFold(oracle.objectNames, ctrlObject.String)
		depL0 := containsSetFold(oracle.objectNames, depObject.String)
		if !ctrlL0 || !depL0 {
			outsideObjectCount++
			if len(outsideObjectSamples) < l0SampleLimit {
				outsideObjectSamples = append(outsideObjectSamples, fmt.Sprintf("%s:%s.%s -> %s.%s", id, ctrlObject.String, ctrlField.String, depObject.String, depField.String))
			}
			continue
		}

		ctrlCanonical := false
		if _, ok := oracle.fieldKeys[fieldComponentKey(ctrlObject.String, ctrlField.String)]; ok {
			ctrlCanonical = true
		}
		depCanonical := false
		if _, ok := oracle.fieldKeys[fieldComponentKey(depObject.String, depField.String)]; ok {
			depCanonical = true
		}
		if !ctrlCanonical || !depCanonical {
			nonCanonicalCount++
			if len(nonCanonicalSamples) < l0SampleLimit {
				nonCanonicalSamples = append(nonCanonicalSamples, fmt.Sprintf("%s:%s.%s -> %s.%s", id, ctrlObject.String, ctrlField.String, depObject.String, depField.String))
			}
			continue
		}

		l0DependencyCount++
		if len(l0DependencySamples) < l0SampleLimit {
			l0DependencySamples = append(l0DependencySamples, fmt.Sprintf("%s:%s.%s -> %s.%s", id, ctrlObject.String, ctrlField.String, depObject.String, depField.String))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_FieldDependency component references: %w", err)
	}

	if missingCount > 0 {
		report.addOwnershipGap("_System_FieldDependency rows missing referenced _System_Field rows: count=%d sample=%s",
			missingCount, strings.Join(missingSamples, ", "))
	}
	if outsideObjectCount > 0 {
		report.addContamination("_System_FieldDependency rows whose referenced field parent object is outside the L0 object set: count=%d sample=%s",
			outsideObjectCount, strings.Join(outsideObjectSamples, ", "))
	}
	if nonCanonicalCount > 0 {
		report.addOwnershipGap("_System_FieldDependency rows outside the canonical L0 field set: count=%d sample=%s",
			nonCanonicalCount, strings.Join(nonCanonicalSamples, ", "))
	}
	if l0DependencyCount > 0 {
		report.addOwnershipGap("_System_FieldDependency rows on canonical L0 fields without L0 component ownership: count=%d sample=%s",
			l0DependencyCount, strings.Join(l0DependencySamples, ", "))
	}
	return nil
}

func checkProfileReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range l0ProfileReferenceSurfaces {
		predicate, args := notInPredicate(surface.column, oracle.profileIDsSorted)
		where := fmt.Sprintf("%s AND %s IS NOT NULL AND TRIM(%s) <> '' AND %s", surface.where, surface.column, surface.column, predicate)
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows whose %s is outside the L0 profile set", surface.table, surface.column),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", surface.table, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, surface.table, where),
			args...); err != nil {
			return err
		}
	}
	return nil
}

func checkProfileInventory(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	predicate, args := notInPredicate("Id", oracle.profileIDsSorted)
	where := "COALESCE(IsDeleted, 0) = 0 AND " + predicate
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"Profile rows outside the L0 profile set",
		"SELECT COUNT(*) FROM Profile WHERE "+where,
		"SELECT CONCAT(Id, '/', Name) FROM Profile WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

func checkPermissionSetInventory(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	profilePredicate, args := inPredicate("ProfileId", oracle.profileIDsSorted)
	where := "COALESCE(IsDeleted, 0) = 0 AND NOT (COALESCE(IsOwnedByProfile, 0) = 1 AND " + profilePredicate + ")"
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSet rows outside the L0 profile-owned set",
		"SELECT COUNT(*) FROM PermissionSet WHERE "+where,
		"SELECT CONCAT(Id, '/', COALESCE(ProfileId, ''), '/', Name) FROM PermissionSet WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

func checkPermissionSetAssignmentInventory(ctx context.Context, db *sql.DB, report *l0DiagnosticReport) error {
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetAssignment rows present in clean L0",
		"SELECT COUNT(*) FROM PermissionSetAssignment WHERE COALESCE(IsDeleted, 0) = 0",
		"SELECT CONCAT(PermissionSetId, '/', AssigneeId) FROM PermissionSetAssignment WHERE COALESCE(IsDeleted, 0) = 0 ORDER BY 1 LIMIT 5")
}

func checkPermissionSetReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range l0PermissionSetReferenceSurfaces {
		if err := checkPermissionSetParentScope(ctx, db, oracle, report, surface.table, surface.column, surface.sampleExpr, surface.where); err != nil {
			return err
		}
	}
	return nil
}

func checkPermissionReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range l0PermissionReferenceSurfaces {
		predicate, args := notInPredicate(surface.column, oracle.permissionsSorted)
		where := fmt.Sprintf("%s AND %s IS NOT NULL AND TRIM(%s) <> '' AND %s", surface.where, surface.column, surface.column, predicate)
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s rows whose %s is outside the platform permission catalog", surface.table, surface.column),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", surface.table, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, surface.table, where),
			args...); err != nil {
			return err
		}
	}
	return nil
}

func checkQueueReferenceSurfaces(ctx context.Context, db *sql.DB, report *l0DiagnosticReport) error {
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"QueueSobject rows missing parent Queue group",
		"SELECT COUNT(*) FROM QueueSobject qs LEFT JOIN `Group` g ON g.Id = qs.QueueId WHERE g.Id IS NULL",
		"SELECT CONCAT(qs.QueueId, '/', qs.SobjectType) FROM QueueSobject qs LEFT JOIN `Group` g ON g.Id = qs.QueueId WHERE g.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"QueueSobject rows whose parent Group is not a Queue",
		"SELECT COUNT(*) FROM QueueSobject qs JOIN `Group` g ON g.Id = qs.QueueId WHERE LOWER(COALESCE(g.Type, '')) <> 'queue'",
		"SELECT CONCAT(qs.QueueId, '/', qs.SobjectType, ' type=', COALESCE(g.Type, '')) FROM QueueSobject qs JOIN `Group` g ON g.Id = qs.QueueId WHERE LOWER(COALESCE(g.Type, '')) <> 'queue' ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"Queue groups present in clean L0",
		"SELECT COUNT(*) FROM `Group` WHERE COALESCE(IsDeleted, 0) = 0 AND LOWER(COALESCE(Type, '')) = 'queue'",
		"SELECT CONCAT(Id, '/', COALESCE(DeveloperName, Name)) FROM `Group` WHERE COALESCE(IsDeleted, 0) = 0 AND LOWER(COALESCE(Type, '')) = 'queue' ORDER BY 1 LIMIT 5")
}

func checkPermissionSetGroupSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroup rows present in clean L0",
		"SELECT COUNT(*) FROM PermissionSetGroup WHERE COALESCE(IsDeleted, 0) = 0",
		"SELECT CONCAT(Id, '/', ApiName) FROM PermissionSetGroup WHERE COALESCE(IsDeleted, 0) = 0 ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	componentJoin := "PermissionSetGroupComponent c LEFT JOIN PermissionSetGroup g ON g.Id = c.GroupId LEFT JOIN PermissionSet ps ON ps.Id = c.PermissionSetId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroupComponent rows missing parent PermissionSetGroup",
		"SELECT COUNT(*) FROM "+componentJoin+" WHERE g.Id IS NULL",
		"SELECT CONCAT(c.GroupId, '/', c.PermissionSetId) FROM "+componentJoin+" WHERE g.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroupComponent rows missing parent PermissionSet",
		"SELECT COUNT(*) FROM "+componentJoin+" WHERE ps.Id IS NULL",
		"SELECT CONCAT(c.GroupId, '/', c.PermissionSetId) FROM "+componentJoin+" WHERE ps.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	profilePredicate, args := inPredicate("ps.ProfileId", oracle.profileIDsSorted)
	where := "ps.Id IS NOT NULL AND NOT (COALESCE(ps.IsOwnedByProfile, 0) = 1 AND COALESCE(ps.IsDeleted, 0) = 0 AND " + profilePredicate + ")"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroupComponent rows whose PermissionSet is outside the L0 profile-owned set",
		"SELECT COUNT(*) FROM "+componentJoin+" WHERE "+where,
		"SELECT CONCAT(c.GroupId, '/', c.PermissionSetId) FROM "+componentJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroupComponent rows present in clean L0",
		"SELECT COUNT(*) FROM PermissionSetGroupComponent",
		"SELECT CONCAT(GroupId, '/', PermissionSetId) FROM PermissionSetGroupComponent ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	assignmentJoin := "PermissionSetGroupAssignment a LEFT JOIN PermissionSetGroup g ON g.Id = a.GroupId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroupAssignment rows missing parent PermissionSetGroup",
		"SELECT COUNT(*) FROM "+assignmentJoin+" WHERE COALESCE(a.IsDeleted, 0) = 0 AND g.Id IS NULL",
		"SELECT CONCAT(a.GroupId, '/', a.AssigneeId) FROM "+assignmentJoin+" WHERE COALESCE(a.IsDeleted, 0) = 0 AND g.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"PermissionSetGroupAssignment rows present in clean L0",
		"SELECT COUNT(*) FROM PermissionSetGroupAssignment WHERE COALESCE(IsDeleted, 0) = 0",
		"SELECT CONCAT(GroupId, '/', AssigneeId) FROM PermissionSetGroupAssignment WHERE COALESCE(IsDeleted, 0) = 0 ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	mutingJoin := "MutingPermissionSet m LEFT JOIN PermissionSetGroup g ON g.Id = m.GroupId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"MutingPermissionSet rows missing parent PermissionSetGroup",
		"SELECT COUNT(*) FROM "+mutingJoin+" WHERE g.Id IS NULL",
		"SELECT CONCAT(m.GroupId, '/', m.ApiName) FROM "+mutingJoin+" WHERE g.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"MutingPermissionSet rows present in clean L0",
		"SELECT COUNT(*) FROM MutingPermissionSet",
		"SELECT CONCAT(GroupId, '/', ApiName) FROM MutingPermissionSet ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	mutingPermissionJoin := "_System_MutingPermissionSetPermission mp LEFT JOIN MutingPermissionSet m ON m.Id = mp.MutingPermissionSetId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_MutingPermissionSetPermission rows missing parent MutingPermissionSet",
		"SELECT COUNT(*) FROM "+mutingPermissionJoin+" WHERE m.Id IS NULL",
		"SELECT CONCAT(mp.MutingPermissionSetId, '/', mp.PermissionApiName) FROM "+mutingPermissionJoin+" WHERE m.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_MutingPermissionSetPermission rows present in clean L0",
		"SELECT COUNT(*) FROM _System_MutingPermissionSetPermission",
		"SELECT CONCAT(MutingPermissionSetId, '/', PermissionApiName) FROM _System_MutingPermissionSetPermission ORDER BY 1 LIMIT 5")
}

func checkConnectedAppAccessSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_ConnectedAppAccess rows with malformed profile/permission-set target",
		`SELECT COUNT(*) FROM _System_ConnectedAppAccess
		  WHERE (ProfileId IS NULL OR TRIM(ProfileId) = '') = (PermissionSetId IS NULL OR TRIM(PermissionSetId) = '')`,
		`SELECT CONCAT(ConnectedAppId, '/', COALESCE(ProfileId, ''), '/', COALESCE(PermissionSetId, ''))
		   FROM _System_ConnectedAppAccess
		  WHERE (ProfileId IS NULL OR TRIM(ProfileId) = '') = (PermissionSetId IS NULL OR TRIM(PermissionSetId) = '')
		  ORDER BY 1 LIMIT 5`); err != nil {
		return err
	}

	baseJoin := "_System_ConnectedAppAccess a LEFT JOIN _System_ConnectedApp ca ON ca.Id = a.ConnectedAppId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_ConnectedAppAccess rows missing parent _System_ConnectedApp",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE ca.Id IS NULL",
		"SELECT CONCAT(a.ConnectedAppId, '/', COALESCE(a.ProfileId, a.PermissionSetId, '')) FROM "+baseJoin+" WHERE ca.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	allowedPredicate, args := connectedAppAllowPredicateFor(oracle.connectedApps, "ca.Name", "ca.ConsumerKey")
	where := "ca.Id IS NOT NULL AND NOT (" + allowedPredicate + ")"
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_ConnectedAppAccess rows for non-platform connected apps",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
		"SELECT CONCAT(ca.Name, '/', ca.ConsumerKey) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "ca.Id IS NOT NULL AND (" + allowedPredicate + ")"
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_ConnectedAppAccess rows for platform connected apps without L0 component ownership",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
		"SELECT CONCAT(ca.Name, '/', COALESCE(a.ProfileId, a.PermissionSetId, '')) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

func checkConnectedAppDeclarationSurfaces(ctx context.Context, db *sql.DB, report *l0DiagnosticReport) error {
	baseJoin := "_System_ConnectedAppDeclaration d LEFT JOIN _System_ConnectedApp ca ON ca.Id = d.ConnectedAppId"
	baseWhere := "COALESCE(d.IsDeleted, 0) = 0"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_ConnectedAppDeclaration rows missing parent _System_ConnectedApp",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+baseWhere+" AND ca.Id IS NULL",
		"SELECT CONCAT(d.InstalledPackName, '/', d.ConnectedAppId) FROM "+baseJoin+" WHERE "+baseWhere+" AND ca.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	return addCountDiagnostic(ctx, db, report.addContamination,
		"_System_ConnectedAppDeclaration rows present in clean L0",
		"SELECT COUNT(*) FROM _System_ConnectedAppDeclaration d WHERE "+baseWhere,
		"SELECT CONCAT(d.InstalledPackName, '/', d.ConnectedAppId) FROM _System_ConnectedAppDeclaration d WHERE "+baseWhere+" ORDER BY 1 LIMIT 5")
}

func checkConnectedAppStageSurfaces(ctx context.Context, db *sql.DB, report *l0DiagnosticReport) error {
	baseJoin := "_System_ConnectedAppStage s LEFT JOIN _System_InstalledPack ip ON ip.Name = s.InstalledPackName"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_ConnectedAppStage rows missing owning _System_InstalledPack",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE ip.Name IS NULL",
		"SELECT CONCAT(s.InstalledPackName, '/', s.ConnectedAppId) FROM "+baseJoin+" WHERE ip.Name IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	return addCountDiagnostic(ctx, db, report.addContamination,
		"_System_ConnectedAppStage rows present in clean L0",
		"SELECT COUNT(*) FROM _System_ConnectedAppStage s",
		"SELECT CONCAT(s.InstalledPackName, '/', s.ConnectedAppId) FROM _System_ConnectedAppStage s ORDER BY 1 LIMIT 5")
}

func checkCustomPermissionGrantSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range []l0ParentReferenceSurface{
		{"ProfileCustomPermission", "CustomPermissionId", "CONCAT(j.ProfileId, '/', j.CustomPermissionId)", "1 = 1"},
		{"PermissionSetCustomPermission", "CustomPermissionId", "CONCAT(j.PermissionSetId, '/', j.CustomPermissionId)", "1 = 1"},
		{"_System_OAuthCustomScope", "CustomPermissionId", "CONCAT(j.Name, '/', COALESCE(j.CustomPermissionId, ''))", "COALESCE(j.IsActive, 1) = 1 AND j.CustomPermissionId IS NOT NULL AND TRIM(j.CustomPermissionId) <> ''"},
	} {
		baseJoin := fmt.Sprintf("%s j LEFT JOIN _System_CustomPermission cp ON cp.Id = j.%s", surface.table, surface.column)
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows missing parent _System_CustomPermission", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND cp.Id IS NULL", baseJoin, surface.where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s AND cp.Id IS NULL ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, surface.where)); err != nil {
			return err
		}

		predicate, args := ownerOutsideRuntimePredicate("cp.OwnerPack", oracle.runtimePacksSorted)
		where := surface.where + " AND cp.Id IS NOT NULL AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s rows whose custom permission is owned by non-L0 packs", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", baseJoin, where),
			fmt.Sprintf("SELECT CONCAT(%s, ' owner=', COALESCE(cp.OwnerPack, '')) FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, where),
			args...); err != nil {
			return err
		}

		where = surface.where + " AND cp.Id IS NOT NULL AND COALESCE(cp.OwnerPack, '') = ''"
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows whose custom permission has empty OwnerPack", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", baseJoin, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, where)); err != nil {
			return err
		}

		predicate, args = inPredicate("cp.OwnerPack", oracle.runtimePacksSorted)
		where = surface.where + " AND cp.Id IS NOT NULL AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows for L0 custom permissions without L0 grant ownership", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", baseJoin, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, where),
			args...); err != nil {
			return err
		}
	}

	return checkCustomPermissionConnectedAppReferences(ctx, db, oracle, report)
}

func checkCustomPermissionConnectedAppReferences(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	baseJoin := "_System_CustomPermission cp LEFT JOIN _System_ConnectedApp ca ON ca.Id = cp.ConnectedAppId"
	baseWhere := "COALESCE(cp.IsDeleted, 0) = 0 AND cp.ConnectedAppId IS NOT NULL AND TRIM(cp.ConnectedAppId) <> ''"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_CustomPermission rows missing parent _System_ConnectedApp",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+baseWhere+" AND ca.Id IS NULL",
		"SELECT CONCAT(cp.ApiName, '/', cp.ConnectedAppId) FROM "+baseJoin+" WHERE "+baseWhere+" AND ca.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	allowedPredicate, args := connectedAppAllowPredicateFor(oracle.connectedApps, "ca.Name", "ca.ConsumerKey")
	where := baseWhere + " AND ca.Id IS NOT NULL AND NOT (" + allowedPredicate + ")"
	return addCountDiagnostic(ctx, db, report.addContamination,
		"_System_CustomPermission rows linked to non-platform connected apps",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
		"SELECT CONCAT(cp.ApiName, '/', ca.Name, '/', ca.ConsumerKey) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

func checkTabAndAppVisibilitySurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range []l0ParentReferenceSurface{
		{"ProfileTabSetting", "TabName", "CONCAT(ProfileId, '/', TabName)", "1 = 1"},
		{"PermissionSetTabSetting", "TabName", "CONCAT(PermissionSetId, '/', TabName)", "1 = 1"},
	} {
		if err := checkRowsOutsideAllowedObjects(ctx, db, report, surface.table, surface.column, surface.sampleExpr, surface.where, oracle); err != nil {
			return err
		}
		if err := checkRowsOnAllowedObjectsWithoutL0Ownership(ctx, db, report, l0ObjectReferenceSurface(surface), oracle); err != nil {
			return err
		}
	}

	for _, surface := range []l0ParentReferenceSurface{
		{"ProfileAppVisibility", "AppId", "CONCAT(j.ProfileId, '/', j.AppId)", "1 = 1"},
		{"PermissionSetAppVisibility", "AppId", "CONCAT(j.PermissionSetId, '/', j.AppId)", "1 = 1"},
		{"AppMenuItem", "AppId", "CONCAT(j.Id, '/', COALESCE(j.AppId, ''))", "COALESCE(j.IsDeleted, 0) = 0 AND j.AppId IS NOT NULL AND TRIM(j.AppId) <> ''"},
	} {
		baseJoin := fmt.Sprintf("%s j LEFT JOIN _System_App a ON a.Id = j.%s", surface.table, surface.column)
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows missing parent _System_App", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND a.Id IS NULL", baseJoin, surface.where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s AND a.Id IS NULL ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, surface.where)); err != nil {
			return err
		}
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows with app visibility/menu metadata in clean L0", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND a.Id IS NOT NULL", baseJoin, surface.where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s AND a.Id IS NOT NULL ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, surface.where)); err != nil {
			return err
		}
	}

	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_App rows present in clean L0",
		"SELECT COUNT(*) FROM _System_App",
		"SELECT CONCAT(Id, '/', Name) FROM _System_App ORDER BY 1 LIMIT 5")
}

func checkLayoutReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	for _, surface := range []l0ParentReferenceSurface{
		{"ProfileLayout", "LayoutId", "CONCAT(j.ObjectApiName, '/', j.ProfileId, '/', j.LayoutId)", "1 = 1"},
		{"_System_LayoutAssignment", "LayoutId", "CONCAT(j.ObjectApiName, '/', COALESCE(j.ProfileId, '*'), '/', j.LayoutId)", "1 = 1"},
	} {
		baseJoin := fmt.Sprintf("%s j LEFT JOIN _System_PageLayout l ON l.Id = j.%s", surface.table, surface.column)
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows missing parent _System_PageLayout", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s AND l.Id IS NULL", baseJoin, surface.where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s AND l.Id IS NULL ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, surface.where)); err != nil {
			return err
		}

		predicate, args := notInPredicate("l.ObjectApiName", oracle.objectNamesSorted)
		where := surface.where + " AND l.Id IS NOT NULL AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s rows whose parent layout object is outside the L0 object set", surface.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", baseJoin, where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, baseJoin, where),
			args...); err != nil {
			return err
		}
	}
	return nil
}

func checkProcessComponentSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	processNodeJoin := "ProcessNode n LEFT JOIN ProcessDefinition pd ON pd.Id = n.ProcessDefinitionId"
	processNodeWhere := "COALESCE(n.IsDeleted, 0) = 0"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"ProcessNode rows missing parent ProcessDefinition",
		"SELECT COUNT(*) FROM "+processNodeJoin+" WHERE "+processNodeWhere+" AND pd.Id IS NULL",
		"SELECT CONCAT(n.ProcessDefinitionId, '/', n.Name) FROM "+processNodeJoin+" WHERE "+processNodeWhere+" AND pd.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	predicate, args := notInPredicate("pd.TableEnumOrId", oracle.objectNamesSorted)
	where := processNodeWhere + " AND pd.Id IS NOT NULL AND pd.TableEnumOrId IS NOT NULL AND TRIM(pd.TableEnumOrId) <> '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"ProcessNode rows whose parent ProcessDefinition object is outside the L0 object set",
		"SELECT COUNT(*) FROM "+processNodeJoin+" WHERE "+where,
		"SELECT CONCAT(pd.TableEnumOrId, '/', n.Name) FROM "+processNodeJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}
	predicate, args = inPredicate("pd.TableEnumOrId", oracle.objectNamesSorted)
	where = processNodeWhere + " AND pd.Id IS NOT NULL AND pd.TableEnumOrId IS NOT NULL AND TRIM(pd.TableEnumOrId) <> '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"ProcessNode rows under L0 ProcessDefinition without L0 component ownership",
		"SELECT COUNT(*) FROM "+processNodeJoin+" WHERE "+where,
		"SELECT CONCAT(pd.TableEnumOrId, '/', n.Name) FROM "+processNodeJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	processActionJoin := "ProcessAction a LEFT JOIN ProcessDefinition pd ON pd.Id = a.ProcessDefinitionId LEFT JOIN ProcessNode n ON n.Id = a.ProcessNodeId"
	processActionWhere := "COALESCE(a.IsDeleted, 0) = 0"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"ProcessAction rows missing parent ProcessDefinition",
		"SELECT COUNT(*) FROM "+processActionJoin+" WHERE "+processActionWhere+" AND pd.Id IS NULL",
		"SELECT CONCAT(a.ProcessDefinitionId, '/', a.Name) FROM "+processActionJoin+" WHERE "+processActionWhere+" AND pd.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"ProcessAction rows missing parent ProcessNode",
		"SELECT COUNT(*) FROM "+processActionJoin+" WHERE "+processActionWhere+" AND a.ProcessNodeId IS NOT NULL AND TRIM(a.ProcessNodeId) <> '' AND n.Id IS NULL",
		"SELECT CONCAT(a.ProcessNodeId, '/', a.Name) FROM "+processActionJoin+" WHERE "+processActionWhere+" AND a.ProcessNodeId IS NOT NULL AND TRIM(a.ProcessNodeId) <> '' AND n.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	predicate, args = notInPredicate("pd.TableEnumOrId", oracle.objectNamesSorted)
	where = processActionWhere + " AND pd.Id IS NOT NULL AND pd.TableEnumOrId IS NOT NULL AND TRIM(pd.TableEnumOrId) <> '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"ProcessAction rows whose parent ProcessDefinition object is outside the L0 object set",
		"SELECT COUNT(*) FROM "+processActionJoin+" WHERE "+where,
		"SELECT CONCAT(pd.TableEnumOrId, '/', a.Name) FROM "+processActionJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}
	predicate, args = inPredicate("pd.TableEnumOrId", oracle.objectNamesSorted)
	where = processActionWhere + " AND pd.Id IS NOT NULL AND pd.TableEnumOrId IS NOT NULL AND TRIM(pd.TableEnumOrId) <> '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"ProcessAction rows under L0 ProcessDefinition without L0 component ownership",
		"SELECT COUNT(*) FROM "+processActionJoin+" WHERE "+where,
		"SELECT CONCAT(pd.TableEnumOrId, '/', a.Name) FROM "+processActionJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	rows, err := db.QueryContext(ctx, `
		SELECT pd.TableEnumOrId, a.FieldApiName, CONCAT(pd.TableEnumOrId, '/', a.Name, ' field=', COALESCE(a.FieldApiName, ''))
		FROM ProcessAction a
		JOIN ProcessDefinition pd ON pd.Id = a.ProcessDefinitionId
		WHERE COALESCE(a.IsDeleted, 0) = 0
		  AND a.FieldApiName IS NOT NULL
		  AND TRIM(a.FieldApiName) <> ''`)
	if err != nil {
		return fmt.Errorf("query ProcessAction field references: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var objectAPIName, rawField, sample string
		if err := rows.Scan(&objectAPIName, &rawField, &sample); err != nil {
			return fmt.Errorf("scan ProcessAction field reference: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		fieldObject, fieldAPIName := splitFieldPermissionField(objectAPIName, rawField)
		if !strings.EqualFold(strings.TrimSpace(fieldObject), strings.TrimSpace(objectAPIName)) {
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, sample)
			}
			continue
		}
		if _, ok := oracle.fieldKeys[fieldComponentKey(objectAPIName, fieldAPIName)]; ok {
			continue
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, sample)
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate ProcessAction field references: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("ProcessAction rows whose FieldApiName is outside the canonical L0 field set: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkLayoutHistorySurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	baseJoin := "_System_PageLayoutHistory h LEFT JOIN _System_PageLayout l ON l.Id = h.LayoutId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_PageLayoutHistory rows missing parent _System_PageLayout",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE l.Id IS NULL",
		"SELECT CONCAT(h.LayoutId, '/v', h.VersionNumber) FROM "+baseJoin+" WHERE l.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	predicate, args := notInPredicate("l.ObjectApiName", oracle.objectNamesSorted)
	where := "l.Id IS NOT NULL AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_PageLayoutHistory rows whose parent layout object is outside the L0 object set",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
		"SELECT CONCAT(l.ObjectApiName, '/', h.LayoutId, '/v', h.VersionNumber) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	predicate, args = inPredicate("l.ObjectApiName", oracle.objectNamesSorted)
	where = "l.Id IS NOT NULL AND " + predicate
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_PageLayoutHistory rows under L0 layouts without L0 component ownership",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
		"SELECT CONCAT(l.ObjectApiName, '/', h.LayoutId, '/v', h.VersionNumber) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

func checkAutoNumberCounterSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	baseJoin := "_System_AutoNumberCounter c LEFT JOIN _System_Field f ON f.Id = c.FieldId LEFT JOIN _System_Object o ON o.Id = f.ObjectId"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_AutoNumberCounter rows missing parent _System_Field",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE f.Id IS NULL",
		"SELECT c.FieldId FROM "+baseJoin+" WHERE f.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}

	predicate, args := notInPredicate("o.ApiName", oracle.objectNamesSorted)
	where := "f.Id IS NOT NULL AND o.ApiName IS NOT NULL AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_AutoNumberCounter rows whose field parent object is outside the L0 object set",
		"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
		"SELECT CONCAT(o.ApiName, '.', f.ApiName) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	rows, err := db.QueryContext(ctx, `
		SELECT o.ApiName, f.ApiName
		FROM _System_AutoNumberCounter c
		JOIN _System_Field f ON f.Id = c.FieldId
		JOIN _System_Object o ON o.Id = f.ObjectId`)
	if err != nil {
		return fmt.Errorf("query _System_AutoNumberCounter field references: %w", err)
	}
	defer rows.Close()

	var count int
	var samples []string
	for rows.Next() {
		var objectAPIName, fieldAPIName string
		if err := rows.Scan(&objectAPIName, &fieldAPIName); err != nil {
			return fmt.Errorf("scan _System_AutoNumberCounter field reference: %w", err)
		}
		if !containsSetFold(oracle.objectNames, objectAPIName) {
			continue
		}
		if _, ok := oracle.fieldKeys[fieldComponentKey(objectAPIName, fieldAPIName)]; ok {
			count++
			if len(samples) < l0SampleLimit {
				samples = append(samples, fmt.Sprintf("%s.%s", objectAPIName, fieldAPIName))
			}
			continue
		}
		count++
		if len(samples) < l0SampleLimit {
			samples = append(samples, fmt.Sprintf("%s.%s", objectAPIName, fieldAPIName))
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate _System_AutoNumberCounter field references: %w", err)
	}
	if count > 0 {
		report.addOwnershipGap("_System_AutoNumberCounter rows under L0 fields without L0 component ownership: count=%d sample=%s",
			count, strings.Join(samples, ", "))
	}
	return nil
}

func checkRecordTypeReferenceSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	recordTypeJoin := "_System_RecordType rt LEFT JOIN _System_Object o ON o.Id = rt.ObjectId"
	recordTypeWhere := "COALESCE(rt.IsDeleted, 0) = 0"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_RecordType rows missing parent _System_Object",
		"SELECT COUNT(*) FROM "+recordTypeJoin+" WHERE "+recordTypeWhere+" AND o.Id IS NULL",
		"SELECT CONCAT(rt.ObjectId, '/', rt.Name) FROM "+recordTypeJoin+" WHERE "+recordTypeWhere+" AND o.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	predicate, args := notInPredicate("o.ApiName", oracle.objectNamesSorted)
	where := recordTypeWhere + " AND o.Id IS NOT NULL AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_RecordType rows whose parent object is outside the L0 object set",
		"SELECT COUNT(*) FROM "+recordTypeJoin+" WHERE "+where,
		"SELECT CONCAT(o.ApiName, '/', rt.Name) FROM "+recordTypeJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	for _, surface := range []l0ParentReferenceSurface{
		{"_System_LayoutAssignment", "RecordTypeId", "CONCAT(j.ObjectApiName, '/', j.RecordTypeId)", "j.RecordTypeId IS NOT NULL AND TRIM(j.RecordTypeId) <> ''"},
		{"PermissionSetRecordTypeVisibility", "RecordTypeId", "CONCAT(j.PermissionSetId, '/', j.RecordTypeId)", "1 = 1"},
		{"_System_RecordTypePicklistValue", "RecordTypeId", "CONCAT(j.RecordTypeId, '/', j.FieldId, '/', j.`Value`)", "COALESCE(j.IsDeleted, 0) = 0"},
	} {
		baseJoin := fmt.Sprintf("%s j LEFT JOIN _System_RecordType rt ON rt.Id = j.%s", surface.table, surface.column)
		baseWhere := surface.where
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows missing parent _System_RecordType", surface.table),
			"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+baseWhere+" AND rt.Id IS NULL",
			"SELECT "+surface.sampleExpr+" FROM "+baseJoin+" WHERE "+baseWhere+" AND rt.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
			return err
		}

		predicate, args = ownerOutsideRuntimePredicate("rt.OwnerPack", oracle.runtimePacksSorted)
		where = baseWhere + " AND rt.Id IS NOT NULL AND " + predicate
		if err := addCountDiagnostic(ctx, db, report.addContamination,
			fmt.Sprintf("%s rows whose parent RecordType is owned by non-L0 packs", surface.table),
			"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
			"SELECT CONCAT("+surface.sampleExpr+", ' owner=', COALESCE(rt.OwnerPack, '')) FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5",
			args...); err != nil {
			return err
		}

		where = baseWhere + " AND rt.Id IS NOT NULL AND COALESCE(rt.OwnerPack, '') = ''"
		if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
			fmt.Sprintf("%s rows whose parent RecordType has empty OwnerPack", surface.table),
			"SELECT COUNT(*) FROM "+baseJoin+" WHERE "+where,
			"SELECT "+surface.sampleExpr+" FROM "+baseJoin+" WHERE "+where+" ORDER BY 1 LIMIT 5"); err != nil {
			return err
		}
	}

	picklistValueJoin := "_System_RecordTypePicklistValue rtpv LEFT JOIN _System_Field f ON f.Id = rtpv.FieldId LEFT JOIN _System_RecordType rt ON rt.Id = rtpv.RecordTypeId"
	picklistValueWhere := "COALESCE(rtpv.IsDeleted, 0) = 0"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_RecordTypePicklistValue rows missing parent _System_Field",
		"SELECT COUNT(*) FROM "+picklistValueJoin+" WHERE "+picklistValueWhere+" AND f.Id IS NULL",
		"SELECT CONCAT(rtpv.RecordTypeId, '/', rtpv.FieldId, '/', rtpv.`Value`) FROM "+picklistValueJoin+" WHERE "+picklistValueWhere+" AND f.Id IS NULL ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_RecordTypePicklistValue rows whose RecordType and Field belong to different objects",
		"SELECT COUNT(*) FROM "+picklistValueJoin+" WHERE "+picklistValueWhere+" AND f.Id IS NOT NULL AND rt.Id IS NOT NULL AND f.ObjectId <> rt.ObjectId",
		"SELECT CONCAT(rtpv.RecordTypeId, '/', rtpv.FieldId, '/', rtpv.`Value`) FROM "+picklistValueJoin+" WHERE "+picklistValueWhere+" AND f.Id IS NOT NULL AND rt.Id IS NOT NULL AND f.ObjectId <> rt.ObjectId ORDER BY 1 LIMIT 5"); err != nil {
		return err
	}
	return nil
}

func checkConnectedApps(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	allowedPredicate, args := connectedAppAllowPredicateFor(oracle.connectedApps, "Name", "ConsumerKey")
	where := "NOT (" + allowedPredicate + ")"
	return addCountDiagnostic(ctx, db, report.addContamination,
		"_System_ConnectedApp rows outside platform bootstrap allowlist",
		"SELECT COUNT(*) FROM _System_ConnectedApp WHERE "+where,
		"SELECT CONCAT(Name, '/', ConsumerKey) FROM _System_ConnectedApp WHERE "+where+" ORDER BY Name LIMIT 5",
		args...)
}

func checkSourceOwnedSurfaces(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_PageLayout admin-authored rows present in clean L0",
		`SELECT COUNT(*) FROM _System_PageLayout
		  WHERE COALESCE(IsDeleted, 0) = 0
		    AND (COALESCE(IsCustomized, 0) = 1
		      OR LOWER(COALESCE(Source, '')) IN ('admin_created', 'cloned', 'custom'))`,
		`SELECT CONCAT(ObjectApiName, '/', LayoutName, ' source=', COALESCE(Source, ''))
		   FROM _System_PageLayout
		  WHERE COALESCE(IsDeleted, 0) = 0
		    AND (COALESCE(IsCustomized, 0) = 1
		      OR LOWER(COALESCE(Source, '')) IN ('admin_created', 'cloned', 'custom'))
		  ORDER BY 1 LIMIT 5`); err != nil {
		return err
	}

	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_ObjectFieldMapping tenant/admin-authored rows present in clean L0",
		`SELECT COUNT(*) FROM _System_ObjectFieldMapping
		  WHERE COALESCE(IsDeleted, 0) = 0
		    AND (COALESCE(IsCustomized, 0) = 1
		      OR LOWER(COALESCE(Source, '')) LIKE 'tenant:%'
		      OR LOWER(COALESCE(Source, '')) IN ('admin_created', 'cloned', 'custom'))`,
		`SELECT CONCAT(SourceObject, ' -> ', TargetObject, ' source=', COALESCE(Source, ''))
		   FROM _System_ObjectFieldMapping
		  WHERE COALESCE(IsDeleted, 0) = 0
		    AND (COALESCE(IsCustomized, 0) = 1
		      OR LOWER(COALESCE(Source, '')) LIKE 'tenant:%'
		      OR LOWER(COALESCE(Source, '')) IN ('admin_created', 'cloned', 'custom'))
		  ORDER BY 1 LIMIT 5`); err != nil {
		return err
	}

	allowedTriggerPredicate, args := inPredicate("TriggerObjectOrEvent", oracle.objectNamesSorted)
	flowDefinitionWhere := "COALESCE(IsDeleted, 0) = 0 AND (TriggerObjectOrEvent IS NULL OR TRIM(TriggerObjectOrEvent) = '' OR " + allowedTriggerPredicate + ")"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"FlowDefinition rows without a non-L0 trigger-object discriminator",
		"SELECT COUNT(*) FROM FlowDefinition WHERE "+flowDefinitionWhere,
		"SELECT DeveloperName FROM FlowDefinition WHERE "+flowDefinitionWhere+" ORDER BY DeveloperName LIMIT 5",
		args...); err != nil {
		return err
	}

	flowAllowedPredicate := strings.ReplaceAll(allowedTriggerPredicate, "TriggerObjectOrEvent", "fd.TriggerObjectOrEvent")
	flowWhere := "COALESCE(f.IsDeleted, 0) = 0 AND (fd.Id IS NULL OR fd.TriggerObjectOrEvent IS NULL OR TRIM(fd.TriggerObjectOrEvent) = '' OR " + flowAllowedPredicate + ")"
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"Flow rows without a non-L0 trigger-object discriminator",
		"SELECT COUNT(*) FROM Flow f LEFT JOIN FlowDefinition fd ON fd.Id = f.DefinitionId WHERE "+flowWhere,
		"SELECT CONCAT(COALESCE(fd.DeveloperName, f.DefinitionId), ' source=', COALESCE(f.Source, '')) FROM Flow f LEFT JOIN FlowDefinition fd ON fd.Id = f.DefinitionId WHERE "+flowWhere+" ORDER BY 1 LIMIT 5",
		args...); err != nil {
		return err
	}

	return nil
}

func checkPermissionOwnershipGaps(ctx context.Context, db *sql.DB, oracle *l0Oracle, report *l0DiagnosticReport) error {
	predicate, args := notInPredicate("ApiName", oracle.permissionsSorted)
	where := "COALESCE(OwnerPack, '') <> '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addContamination,
		"_System_Permission rows outside the platform permission catalog",
		"SELECT COUNT(*) FROM _System_Permission WHERE "+where,
		"SELECT CONCAT(ApiName, ' owner=', COALESCE(OwnerPack, '')) FROM _System_Permission WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	predicate, args = notInPredicate("ApiName", oracle.permissionsSorted)
	where = "COALESCE(OwnerPack, '') = '' AND " + predicate
	if err := addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_Permission rows with empty OwnerPack outside the platform catalog",
		"SELECT COUNT(*) FROM _System_Permission WHERE "+where,
		"SELECT ApiName FROM _System_Permission WHERE "+where+" ORDER BY ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	where = "COALESCE(IsDeleted, 0) = 0 AND COALESCE(OwnerPack, '') = ''"
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		"_System_CustomPermission rows with empty OwnerPack",
		"SELECT COUNT(*) FROM _System_CustomPermission WHERE "+where,
		"SELECT ApiName FROM _System_CustomPermission WHERE "+where+" ORDER BY ApiName LIMIT 5")
}

func checkRowsOutsideAllowedObjects(ctx context.Context, db *sql.DB, report *l0DiagnosticReport, table, column, sampleExpr, baseWhere string, oracle *l0Oracle) error {
	predicate, args := notInPredicate(column, oracle.objectNamesSorted)
	where := fmt.Sprintf("%s AND %s IS NOT NULL AND TRIM(%s) <> '' AND %s", baseWhere, column, column, predicate)
	return addCountDiagnostic(ctx, db, report.addContamination,
		fmt.Sprintf("%s rows where %s is outside the L0 object set", table, column),
		fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", table, where),
		fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", sampleExpr, table, where),
		args...)
}

func validationRuleLiveRowsPredicate(ctx context.Context, db *sql.DB) (string, error) {
	hasColumn, err := tableHasColumn(ctx, db, "_System_ValidationRule", "IsDeleted")
	if err != nil {
		return "", err
	}
	if hasColumn {
		return "COALESCE(IsDeleted, 0) = 0", nil
	}
	hasActiveColumn, err := tableHasColumn(ctx, db, "_System_ValidationRule", "Active")
	if err != nil {
		return "", err
	}
	if hasActiveColumn {
		return "COALESCE(Active, 0) = 1", nil
	}
	return "1 = 1", nil
}

func tableHasColumn(ctx context.Context, db *sql.DB, tableName, columnName string) (bool, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM information_schema.columns
		WHERE table_schema = DATABASE()
		  AND table_name = ?
		  AND column_name = ?`,
		tableName, columnName,
	).Scan(&count); err != nil {
		return false, fmt.Errorf("check column %s.%s: %w", tableName, columnName, err)
	}
	return count > 0, nil
}

func checkRowsOnAllowedObjectsWithoutL0Ownership(ctx context.Context, db *sql.DB, report *l0DiagnosticReport, surface l0ObjectReferenceSurface, oracle *l0Oracle) error {
	predicate, args := inPredicate(surface.column, oracle.objectNamesSorted)
	where := fmt.Sprintf("%s AND %s IS NOT NULL AND TRIM(%s) <> '' AND %s", surface.where, surface.column, surface.column, predicate)
	return addCountDiagnostic(ctx, db, report.addOwnershipGap,
		fmt.Sprintf("%s rows on L0 objects without L0 component ownership", surface.table),
		fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", surface.table, where),
		fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", surface.sampleExpr, surface.table, where),
		args...)
}

func addCountDiagnostic(ctx context.Context, db *sql.DB, add func(string, ...any), label, countSQL, sampleSQL string, args ...any) error {
	var count int
	if err := db.QueryRowContext(ctx, countSQL, args...).Scan(&count); err != nil {
		return fmt.Errorf("query %s count: %w", label, err)
	}
	if count == 0 {
		return nil
	}
	samples, err := queryStringSamples(ctx, db, sampleSQL, args...)
	if err != nil {
		return fmt.Errorf("query %s samples: %w", label, err)
	}
	add("%s: count=%d sample=%s", label, count, strings.Join(samples, ", "))
	return nil
}

func queryStringSamples(ctx context.Context, db *sql.DB, query string, args ...any) ([]string, error) {
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []string
	for rows.Next() {
		var s sql.NullString
		if err := rows.Scan(&s); err != nil {
			return nil, err
		}
		if s.Valid {
			out = append(out, s.String)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if len(out) == 0 {
		out = append(out, "<no sample>")
	}
	return out, nil
}

func ownerOutsideRuntimePredicate(ownerExpr string, runtimePacks []string) (string, []any) {
	predicate, args := notInPredicate(ownerExpr, runtimePacks)
	return fmt.Sprintf("COALESCE(%s, '') <> '' AND %s", ownerExpr, predicate), args
}

func notInPredicate(expr string, allowed []string) (string, []any) {
	if len(allowed) == 0 {
		return "1 = 1", nil
	}
	return fmt.Sprintf("LOWER(%s) NOT IN (%s)", expr, placeholders(len(allowed))), stringsToAny(allowed)
}

func inPredicate(expr string, allowed []string) (string, []any) {
	if len(allowed) == 0 {
		return "1 = 0", nil
	}
	return fmt.Sprintf("LOWER(%s) IN (%s)", expr, placeholders(len(allowed))), stringsToAny(allowed)
}

func connectedAppAllowPredicateFor(apps []packs.PlatformBootstrapConnectedApp, nameExpr, consumerKeyExpr string) (string, []any) {
	if len(apps) == 0 {
		return "1 = 0", nil
	}
	clauses := make([]string, 0, len(apps))
	args := make([]any, 0, len(apps)*2)
	for _, app := range apps {
		clauses = append(clauses, fmt.Sprintf("(LOWER(%s) = ? AND LOWER(%s) = ?)", nameExpr, consumerKeyExpr))
		args = append(args,
			strings.ToLower(strings.TrimSpace(app.Name)),
			strings.ToLower(strings.TrimSpace(app.ClientID)),
		)
	}
	return strings.Join(clauses, " OR "), args
}

func placeholders(n int) string {
	if n <= 0 {
		return ""
	}
	out := make([]string, n)
	for i := range out {
		out[i] = "?"
	}
	return strings.Join(out, ", ")
}

func stringsToAny(in []string) []any {
	out := make([]any, 0, len(in))
	for _, s := range in {
		out = append(out, strings.ToLower(strings.TrimSpace(s)))
	}
	return out
}

func parseOptionalJSONStringArray(raw string) ([]string, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		return nil, nil
	}
	var values []string
	if err := json.Unmarshal([]byte(raw), &values); err != nil {
		return nil, err
	}
	out := make([]string, 0, len(values))
	for _, value := range values {
		value = strings.TrimSpace(value)
		if value != "" {
			out = append(out, value)
		}
	}
	return out, nil
}

func containsSetFold(set map[string]struct{}, value string) bool {
	value = strings.TrimSpace(value)
	for existing := range set {
		if strings.EqualFold(existing, value) {
			return true
		}
	}
	return false
}

func fieldComponentKey(objectAPIName, fieldAPIName string) string {
	return componentKey(objectAPIName, fieldAPIName)
}

func relationshipComponentKey(childObjectAPIName, fieldAPIName, parentObjectAPIName string) string {
	return componentKey(childObjectAPIName, fieldAPIName, parentObjectAPIName)
}

func listViewComponentKey(objectAPIName, label string) string {
	return componentKey(objectAPIName, label)
}

func declaredComponentKey(packName string, kind packs.ComponentKind, businessKey string) string {
	return componentKey(packName, string(kind), businessKey)
}

func (o *l0Oracle) hasDeclaredComponent(packName string, kind packs.ComponentKind, businessKey string) bool {
	_, ok := o.declaredComponents[declaredComponentKey(packName, kind, businessKey)]
	return ok
}

func (o *l0Oracle) declaredComponent(packName string, kind packs.ComponentKind, businessKey string) (packs.DeclaredComponent, bool) {
	component, ok := o.declaredComponents[declaredComponentKey(packName, kind, businessKey)]
	return component, ok
}

func componentKey(parts ...string) string {
	normalized := make([]string, 0, len(parts))
	for _, part := range parts {
		normalized = append(normalized, strings.ToLower(strings.TrimSpace(part)))
	}
	return strings.Join(normalized, "\x00")
}

func splitFieldPermissionField(sobjectType, rawField string) (string, string) {
	sobjectType = strings.TrimSpace(sobjectType)
	rawField = strings.TrimSpace(rawField)
	if rawField == "" {
		return sobjectType, ""
	}
	if before, after, ok := strings.Cut(rawField, "."); ok {
		return strings.TrimSpace(before), strings.TrimSpace(after)
	}
	return sobjectType, rawField
}

func addSet(set map[string]struct{}, value string) {
	value = strings.TrimSpace(value)
	if value == "" {
		return
	}
	set[value] = struct{}{}
}

func sortedSet(set map[string]struct{}) []string {
	out := make([]string, 0, len(set))
	for value := range set {
		out = append(out, strings.ToLower(value))
	}
	sort.Strings(out)
	return out
}

func nullStringSample(s sql.NullString) string {
	if !s.Valid || s.String == "" {
		return "<null>"
	}
	return s.String
}

func minInt(a, b int) int {
	if a < b {
		return a
	}
	return b
}