package bootstrapimpl

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"sort"
	"strings"

	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func normalizeJSONForComparison(raw string) (string, error) {
	trimmed := strings.TrimSpace(raw)
	if trimmed == "" {
		return "", nil
	}

	var decoded any
	if err := json.Unmarshal([]byte(trimmed), &decoded); err != nil {
		return "", err
	}

	normalized, err := json.Marshal(decoded)
	if err != nil {
		return "", err
	}
	return string(normalized), nil
}

func normalizeAccessPolicyForComparison(raw string, stripPackOwnedRecordMutation, stripPackOwnedLifecycle bool) (string, error) {
	trimmed := strings.TrimSpace(raw)
	if trimmed == "" {
		return "", nil
	}
	var policy map[string]json.RawMessage
	if err := json.Unmarshal([]byte(trimmed), &policy); err != nil {
		return "", err
	}
	if stripPackOwnedRecordMutation {
		delete(policy, "recordMutation")
	}
	if stripPackOwnedLifecycle {
		delete(policy, "lifecycle")
	}
	if len(policy) == 0 {
		return "", nil
	}
	normalized, err := json.Marshal(policy)
	if err != nil {
		return "", err
	}
	return normalizeJSONForComparison(string(normalized))
}

func managedTableAccessPolicies(tableDefs []domainschema.TableDefinition) map[string]*models.ObjectAccessPolicy {
	policies := make(map[string]*models.ObjectAccessPolicy)
	for _, def := range tableDefs {
		if def.AccessPolicy == nil {
			continue
		}
		policies[strings.ToLower(def.TableName)] = def.AccessPolicy
	}
	return policies
}

// AssertManagedTableAccessPolicyConsistency checks access-policy drift for
// L0-owned managed tables that are not necessarily standard objects. The
// expected map is caller-owned so pack verification can stay scoped to its own
// object payloads.
func AssertManagedTableAccessPolicyConsistency(db *sql.DB, result *AssertionResult, expectedPolicies map[string]*models.ObjectAccessPolicy) {
	if len(expectedPolicies) == 0 {
		return
	}

	log.Println("   🔍 Checking managed table access policy consistency...")

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, `%s` FROM `%s` WHERE COALESCE(`%s`, 0) = 0",
		constants.FieldAPIName,
		constants.FieldSysObject_AccessPolicy,
		constants.FieldSysObject_AccessPolicyRecordMutationPackName,
		constants.FieldSysObject_AccessPolicyLifecyclePackName,
		constants.TableObject,
		constants.FieldIsDeleted,
	))
	if err != nil {
		log.Printf("   ⚠️  Failed to query managed table access policies: %v", err)
		return
	}
	defer rows.Close()

	type policyRow struct {
		apiName                string
		accessPolicyJSON       sql.NullString
		recordMutationPackName sql.NullString
		lifecyclePackName      sql.NullString
	}
	byName := make(map[string]policyRow)
	for rows.Next() {
		var r policyRow
		if err := rows.Scan(&r.apiName, &r.accessPolicyJSON, &r.recordMutationPackName, &r.lifecyclePackName); err != nil {
			log.Printf("   ⚠️  Failed to scan managed table access policy row: %v", err)
			return
		}
		byName[strings.ToLower(r.apiName)] = r
	}

	apiNames := make([]string, 0, len(expectedPolicies))
	for apiName := range expectedPolicies {
		apiNames = append(apiNames, apiName)
	}
	sort.Strings(apiNames)

	for _, apiName := range apiNames {
		policy := expectedPolicies[apiName]
		row, found := byName[apiName]
		if !found {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityWarning,
				Object:      apiName,
				Description: fmt.Sprintf("managed table %s not found in _System_Object", apiName),
			})
			continue
		}

		var wantAccessPolicy string
		if policy != nil {
			b, err := json.Marshal(policy)
			if err != nil {
				log.Printf("   ⚠️  Failed to marshal managed table access policy for %s: %v", apiName, err)
				continue
			}
			wantAccessPolicy = string(b)
		}
		gotAccessPolicy := strings.TrimSpace(row.accessPolicyJSON.String)
		normalizedGot, err := normalizeAccessPolicyForComparison(gotAccessPolicy, strings.TrimSpace(row.recordMutationPackName.String) != "", strings.TrimSpace(row.lifecyclePackName.String) != "")
		if err != nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityError,
				Object:      apiName,
				Description: fmt.Sprintf("AccessPolicy drift: invalid persisted JSON %q (%v)", gotAccessPolicy, err),
			})
			continue
		}
		normalizedWant, err := normalizeAccessPolicyForComparison(wantAccessPolicy, false, false)
		if err != nil {
			log.Printf("   ⚠️  Failed to normalize managed table access policy for %s: %v", apiName, err)
			continue
		}
		if normalizedGot != normalizedWant {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityError,
				Object:      apiName,
				Description: fmt.Sprintf("AccessPolicy drift: persisted=%q expected=%q", gotAccessPolicy, wantAccessPolicy),
			})
		}
	}
}

// assertManagedWriteValidationCapabilitiesReconciled verifies that every
// managed table whose embedded definition declares write-validation
// canonicalizer/validator capabilities also carries those capability bindings
// in persisted _System_Object.AccessPolicy. The records write-validation seam
// fail-closes when required security capabilities are absent from runtime
// object metadata, so warm-upgraded orgs whose completed installs skip setup
// apply must fail readiness before ordinary writes hit missing-metadata errors.
func assertManagedWriteValidationCapabilitiesReconciled(db *sql.DB, result *AssertionResult) {
	AssertManagedWriteValidationCapabilitiesReconciled(db, result, managedWriteValidationCapabilityPolicies(GetManagedTableDefinitions()))
}

func managedWriteValidationCapabilityPolicies(tableDefs []domainschema.TableDefinition) map[string]*models.RecordWriteValidationAccessPolicy {
	expected := make(map[string]*models.RecordWriteValidationAccessPolicy)
	for _, def := range tableDefs {
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil {
			continue
		}
		validators := append([]models.RecordWriteValidationPolicy(nil), def.AccessPolicy.WriteValidation.Validators...)
		canonicalizers := append([]models.RecordWriteCanonicalizationPolicy(nil), def.AccessPolicy.WriteValidation.Canonicalizers...)
		if len(validators) == 0 && len(canonicalizers) == 0 {
			continue
		}
		expected[strings.ToLower(def.TableName)] = &models.RecordWriteValidationAccessPolicy{
			Validators:     validators,
			Canonicalizers: canonicalizers,
		}
	}
	return expected
}

// AssertManagedWriteValidationCapabilitiesReconciled checks write-validation
// capability reconciliation for a caller-owned policy map. Only the phase /
// capability declarations are compared; adjacent writeValidation subfacts such
// as ownerResolution remain owned by their dedicated readiness assertions.
func AssertManagedWriteValidationCapabilitiesReconciled(db *sql.DB, result *AssertionResult, expected map[string]*models.RecordWriteValidationAccessPolicy) {
	if len(expected) == 0 {
		return
	}

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s` FROM `%s` WHERE COALESCE(`%s`, 0) = 0",
		constants.FieldAPIName,
		constants.FieldSysObject_AccessPolicy,
		constants.TableObject,
		constants.FieldIsDeleted,
	))
	if err != nil {
		log.Printf("   ⚠️  Failed to query managed write-validation reconciliation: %v", err)
		return
	}
	defer rows.Close()

	persisted := make(map[string]sql.NullString)
	for rows.Next() {
		var apiName string
		var accessPolicyJSON sql.NullString
		if err := rows.Scan(&apiName, &accessPolicyJSON); err != nil {
			log.Printf("   ⚠️  Failed to scan write-validation reconciliation row: %v", err)
			return
		}
		persisted[strings.ToLower(apiName)] = accessPolicyJSON
	}

	names := make([]string, 0, len(expected))
	for name := range expected {
		names = append(names, name)
	}
	sort.Strings(names)

	for _, name := range names {
		expectedPolicy := expected[name]
		accessPolicyJSON, found := persisted[name]
		if !found {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedWriteValidationReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares writeValidation capabilities but is absent from _System_Object; run setup apply to reconcile", name),
			})
			continue
		}
		trimmed := strings.TrimSpace(accessPolicyJSON.String)
		var policy models.ObjectAccessPolicy
		if trimmed == "" {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedWriteValidationReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares writeValidation capabilities but persisted _System_Object.AccessPolicy is empty; run setup apply to reconcile", name),
			})
			continue
		}
		if err := json.Unmarshal([]byte(trimmed), &policy); err != nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedWriteValidationReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares writeValidation capabilities but persisted AccessPolicy JSON is invalid (%v); run setup apply to reconcile", name, err),
			})
			continue
		}
		if missing := missingManagedWriteValidationCapabilities(policy.WriteValidation, expectedPolicy); len(missing) > 0 {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedWriteValidationReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares writeValidation capability pair(s) %v in its embedded definition but persisted _System_Object.AccessPolicy does not carry them; run setup apply to reconcile", name, missing),
			})
		}
	}
}

func missingManagedWriteValidationCapabilities(actual, expected *models.RecordWriteValidationAccessPolicy) []string {
	actualValidators := managedWriteValidationCapabilitySetFromValidators(nil)
	actualCanonicalizers := managedWriteValidationCapabilitySetFromCanonicalizers(nil)
	if actual != nil {
		actualValidators = managedWriteValidationCapabilitySetFromValidators(actual.Validators)
		actualCanonicalizers = managedWriteValidationCapabilitySetFromCanonicalizers(actual.Canonicalizers)
	}

	var missing []string
	if expected != nil {
		for _, validator := range expected.Validators {
			key, label := managedWriteValidationCapabilityKey("validator", validator.Phase, validator.Capability)
			if key == "" {
				continue
			}
			if _, ok := actualValidators[key]; !ok {
				missing = append(missing, label)
			}
		}
		for _, canonicalizer := range expected.Canonicalizers {
			key, label := managedWriteValidationCapabilityKey("canonicalizer", canonicalizer.Phase, canonicalizer.Capability)
			if key == "" {
				continue
			}
			if _, ok := actualCanonicalizers[key]; !ok {
				missing = append(missing, label)
			}
		}
	}
	sort.Strings(missing)
	return missing
}

func managedWriteValidationCapabilitySetFromValidators(validators []models.RecordWriteValidationPolicy) map[string]struct{} {
	out := make(map[string]struct{}, len(validators))
	for _, validator := range validators {
		key, _ := managedWriteValidationCapabilityKey("validator", validator.Phase, validator.Capability)
		if key != "" {
			out[key] = struct{}{}
		}
	}
	return out
}

func managedWriteValidationCapabilitySetFromCanonicalizers(canonicalizers []models.RecordWriteCanonicalizationPolicy) map[string]struct{} {
	out := make(map[string]struct{}, len(canonicalizers))
	for _, canonicalizer := range canonicalizers {
		key, _ := managedWriteValidationCapabilityKey("canonicalizer", canonicalizer.Phase, canonicalizer.Capability)
		if key != "" {
			out[key] = struct{}{}
		}
	}
	return out
}

func managedWriteValidationCapabilityKey(kind, phase, capability string) (string, string) {
	kind = strings.ToLower(strings.TrimSpace(kind))
	phase = strings.ToLower(strings.TrimSpace(phase))
	capability = strings.ToLower(strings.TrimSpace(capability))
	if kind == "" || phase == "" || capability == "" {
		return "", ""
	}
	label := fmt.Sprintf("%s %s/%s", kind, phase, capability)
	return label, label
}

// assertManagedOwnerResolutionReconciled verifies that every managed table
// whose embedded definition authors an OwnerResolutionSpec also carries the
// subfact in its persisted _System_Object.AccessPolicy. The write-validation
// seam fail-closes every write to an object whose definition declares
// ownerResolution while the persisted schema lacks the subfact, so an org
// upgraded from a pre-ownerResolution binary (whose completed install skips
// setup apply) must not pass pod-readiness until setup apply reconciles the
// subfact — otherwise the drift surfaces as broken permission-admin writes
// after deploy rather than at startup. setup apply reconciles the subfact via
// reconcileManagedStandardObjectAccessPolicyJSON, which marshals the full
// definition AccessPolicy (including ownerResolution) into _System_Object.
func assertManagedOwnerResolutionReconciled(db *sql.DB, result *AssertionResult) {
	AssertManagedOwnerResolutionReconciled(db, result, managedOwnerResolutionTableNames(GetManagedTableDefinitions()))
}

func managedOwnerResolutionTableNames(tableDefs []domainschema.TableDefinition) map[string]struct{} {
	expected := make(map[string]struct{})
	for _, def := range tableDefs {
		if def.AccessPolicy == nil || def.AccessPolicy.WriteValidation == nil || def.AccessPolicy.WriteValidation.OwnerResolution == nil {
			continue
		}
		expected[strings.ToLower(def.TableName)] = struct{}{}
	}
	return expected
}

// AssertManagedOwnerResolutionReconciled checks owner-resolution subfact
// reconciliation for a caller-owned set of managed table names. Each name in
// expected must carry a persisted _System_Object.AccessPolicy whose
// writeValidation.ownerResolution subfact is present; otherwise the
// write-validation seam fail-closes every write to that object until setup
// apply reconciles it. The expected map is caller-owned so the assertion stays
// testable and pack verification can stay scoped.
func AssertManagedOwnerResolutionReconciled(db *sql.DB, result *AssertionResult, expected map[string]struct{}) {
	if len(expected) == 0 {
		return
	}

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s` FROM `%s` WHERE COALESCE(`%s`, 0) = 0",
		constants.FieldAPIName,
		constants.FieldSysObject_AccessPolicy,
		constants.TableObject,
		constants.FieldIsDeleted,
	))
	if err != nil {
		log.Printf("   ⚠️  Failed to query managed owner-resolution reconciliation: %v", err)
		return
	}
	defer rows.Close()

	persisted := make(map[string]sql.NullString)
	for rows.Next() {
		var apiName string
		var accessPolicyJSON sql.NullString
		if err := rows.Scan(&apiName, &accessPolicyJSON); err != nil {
			log.Printf("   ⚠️  Failed to scan owner-resolution reconciliation row: %v", err)
			return
		}
		persisted[strings.ToLower(apiName)] = accessPolicyJSON
	}

	names := make([]string, 0, len(expected))
	for name := range expected {
		names = append(names, name)
	}
	sort.Strings(names)

	for _, name := range names {
		accessPolicyJSON, found := persisted[name]
		if !found {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedOwnerResolutionReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares ownerResolution but is absent from _System_Object; run setup apply to reconcile", name),
			})
			continue
		}
		trimmed := strings.TrimSpace(accessPolicyJSON.String)
		var policy models.ObjectAccessPolicy
		if trimmed == "" {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedOwnerResolutionReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares ownerResolution but persisted _System_Object.AccessPolicy is empty; run setup apply to reconcile", name),
			})
			continue
		}
		if err := json.Unmarshal([]byte(trimmed), &policy); err != nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedOwnerResolutionReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares ownerResolution but persisted AccessPolicy JSON is invalid (%v); run setup apply to reconcile", name, err),
			})
			continue
		}
		if policy.WriteValidation == nil || policy.WriteValidation.OwnerResolution == nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedOwnerResolutionReconciliation",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("managed table %s declares ownerResolution in its embedded definition but the persisted _System_Object.AccessPolicy does not carry the subfact; run setup apply to reconcile", name),
			})
		}
	}
}

// AssertManagedAccessPolicyConsistency checks persisted _System_Object rows
// against the caller-owned expected policy map. Pack Verify methods pass
// scoped maps so L0 verify does not read app-pack metadata.
func AssertManagedAccessPolicyConsistency(db *sql.DB, result *AssertionResult, managedPolicies map[string]domainschema.ManagedStandardObjectPolicy) {
	log.Println("   🔍 Checking managed access policy consistency...")

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s`, COALESCE(`%s`, 0), COALESCE(`%s`, 0), `%s`, `%s`, `%s` FROM `%s` WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0",
		constants.FieldAPIName,
		constants.FieldSysObject_SharingModel,
		constants.FieldSysObject_EnableHierarchySharing,
		constants.FieldSysObject_IsCustomized,
		constants.FieldSysObject_AccessPolicy,
		constants.FieldSysObject_AccessPolicyRecordMutationPackName,
		constants.FieldSysObject_AccessPolicyLifecyclePackName,
		constants.TableObject,
		constants.FieldSysObject_TableType,
		constants.FieldIsDeleted,
	), string(constants.TableTypeStandardObject))
	if err != nil {
		log.Printf("   ⚠️  Failed to query standard objects: %v", err)
		return
	}
	defer rows.Close()

	type objRow struct {
		apiName                string
		sharingModel           string
		enableHierarchySharing bool
		isCustomized           bool
		accessPolicyJSON       sql.NullString
		recordMutationPackName sql.NullString
		lifecyclePackName      sql.NullString
	}
	byName := make(map[string]objRow)
	for rows.Next() {
		var r objRow
		if err := rows.Scan(&r.apiName, &r.sharingModel, &r.enableHierarchySharing, &r.isCustomized, &r.accessPolicyJSON, &r.recordMutationPackName, &r.lifecyclePackName); err != nil {
			log.Printf("   ⚠️  Failed to scan standard object row: %v", err)
			return
		}
		byName[strings.ToLower(r.apiName)] = r
	}

	apiNames := make([]string, 0, len(managedPolicies))
	for apiName := range managedPolicies {
		apiNames = append(apiNames, apiName)
	}
	sort.Strings(apiNames)

	// Check each managed standard object
	for _, apiName := range apiNames {
		policy := managedPolicies[apiName]

		row, found := byName[apiName]
		if !found {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityWarning,
				Object:      apiName,
				Description: fmt.Sprintf("managed standard object %s not found in _System_Object", apiName),
			})
			continue
		}

		// SharingModel — only check pack-managed rows. Historical CRM sharing
		// repairs belong in explicit migrations, not every-boot assertions.
		if !row.isCustomized {
			if !strings.EqualFold(row.sharingModel, string(policy.SharingModel)) {
				result.Violations = append(result.Violations, AssertionViolation{
					Category:    "ManagedAccessPolicyDrift",
					Severity:    constants.SeverityError,
					Object:      apiName,
					Description: fmt.Sprintf("SharingModel drift: persisted=%q expected=%q", row.sharingModel, policy.SharingModel),
				})
			}
		}

		// EnableHierarchySharing — unconditional (same as overlay and reconciler)
		if row.enableHierarchySharing != policy.EnableHierarchySharing {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityError,
				Object:      apiName,
				Description: fmt.Sprintf("EnableHierarchySharing drift: persisted=%v expected=%v", row.enableHierarchySharing, policy.EnableHierarchySharing),
			})
		}

		var wantAccessPolicy string
		if policy.AccessPolicy != nil {
			b, err := json.Marshal(policy.AccessPolicy)
			if err != nil {
				log.Printf("   ⚠️  Failed to marshal managed access policy for %s: %v", apiName, err)
				continue
			}
			wantAccessPolicy = string(b)
		}
		gotAccessPolicy := strings.TrimSpace(row.accessPolicyJSON.String)
		normalizedGot, err := normalizeAccessPolicyForComparison(gotAccessPolicy, strings.TrimSpace(row.recordMutationPackName.String) != "", strings.TrimSpace(row.lifecyclePackName.String) != "")
		if err != nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityError,
				Object:      apiName,
				Description: fmt.Sprintf("AccessPolicy drift: invalid persisted JSON %q (%v)", gotAccessPolicy, err),
			})
			continue
		}
		normalizedWant, err := normalizeAccessPolicyForComparison(wantAccessPolicy, false, false)
		if err != nil {
			log.Printf("   ⚠️  Failed to normalize managed access policy for %s: %v", apiName, err)
			continue
		}
		if normalizedGot != normalizedWant {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedAccessPolicyDrift",
				Severity:    constants.SeverityError,
				Object:      apiName,
				Description: fmt.Sprintf("AccessPolicy drift: persisted=%q expected=%q", gotAccessPolicy, wantAccessPolicy),
			})
		}
	}
}

// AssertManagedRelationshipConsistency checks persisted _System_Relationship
// rows against caller-owned relationship definitions. Relationship ownership is
// by source object: the pack that owns ChildObjectApiName owns the assertion.
// Table definitions are required to determine whether a missing row is a drift
// violation: managed definitions that need an overlay row (plain reference fields
// with non-field-derived semantics like IsSharingParent=true) must have a
// persisted row after reconcile; if the row is missing, runtime loses the
// overlay semantics. Master-detail fields derive their semantics from field
// metadata alone, so a missing overlay row is not a drift.
func AssertManagedRelationshipConsistency(db *sql.DB, result *AssertionResult, defs []domainschema.ManagedRelationshipSemantics, tableDefs ...[]domainschema.TableDefinition) {
	log.Println("   🔍 Checking managed relationship consistency...")

	var fieldMetadata map[string]ManagedRelationshipFieldMetadata
	if len(tableDefs) > 0 && len(tableDefs[0]) > 0 {
		fieldMetadata = BuildManagedRelationshipFieldMetadata(tableDefs[0])
	}

	for _, def := range defs {
		var isSharingParent, cascadeDelete, restrictedDelete sql.NullBool
		err := db.QueryRow(`
			SELECT IsSharingParent, CascadeDelete, RestrictedDelete
			FROM _System_Relationship
			WHERE LOWER(ChildObjectApiName) = ?
				AND LOWER(ParentObjectApiName) = ?
				AND LOWER(FieldApiName) = ?
		`,
			strings.ToLower(def.ChildObjectAPIName),
			strings.ToLower(def.ParentObjectAPIName),
			strings.ToLower(def.FieldAPIName),
		).Scan(&isSharingParent, &cascadeDelete, &restrictedDelete)

		relKey := fmt.Sprintf("%s.%s→%s", def.ChildObjectAPIName, def.FieldAPIName, def.ParentObjectAPIName)

		if err == sql.ErrNoRows {
			// A missing row is a drift violation only when the managed
			// definition requires an overlay row that reconcile would have
			// created. For master-detail fields the resolver derives semantics
			// from field metadata alone, so a missing row is benign.
			if fieldMetadata != nil {
				fieldKey := ManagedRelationshipKey(def.ChildObjectAPIName, def.ParentObjectAPIName, def.FieldAPIName)
				if meta, ok := fieldMetadata[fieldKey]; ok && NeedsOverlayRow(meta, def) {
					result.Violations = append(result.Violations, AssertionViolation{
						Category:    "ManagedRelationshipDrift",
						Severity:    constants.SeverityError,
						Object:      relKey,
						Description: "required overlay row missing — runtime loses IsSharingParent/CascadeDelete semantics",
					})
				}
			}
			continue
		}
		if err != nil {
			log.Printf("   ⚠️  Failed to query managed relationship %s: %v", relKey, err)
			continue
		}

		if def.IsSharingParent != nil {
			got := isSharingParent.Valid && isSharingParent.Bool
			if got != *def.IsSharingParent {
				result.Violations = append(result.Violations, AssertionViolation{
					Category:    "ManagedRelationshipDrift",
					Severity:    constants.SeverityError,
					Object:      relKey,
					Description: fmt.Sprintf("IsSharingParent drift: persisted=%v expected=%v", got, *def.IsSharingParent),
				})
			}
		}
		if def.CascadeDelete != nil {
			got := cascadeDelete.Valid && cascadeDelete.Bool
			if got != *def.CascadeDelete {
				result.Violations = append(result.Violations, AssertionViolation{
					Category:    "ManagedRelationshipDrift",
					Severity:    constants.SeverityError,
					Object:      relKey,
					Description: fmt.Sprintf("CascadeDelete drift: persisted=%v expected=%v", got, *def.CascadeDelete),
				})
			}
		}
		if def.RestrictedDelete != nil {
			got := restrictedDelete.Valid && restrictedDelete.Bool
			if got != *def.RestrictedDelete {
				result.Violations = append(result.Violations, AssertionViolation{
					Category:    "ManagedRelationshipDrift",
					Severity:    constants.SeverityError,
					Object:      relKey,
					Description: fmt.Sprintf("RestrictedDelete drift: persisted=%v expected=%v", got, *def.RestrictedDelete),
				})
			}
		}
	}
}

// assertPermissionPolicySeedingComplete verifies that all bootstrap profiles
// have at least some ObjectPermissions seeded. A profile with zero permissions
// means the PlatformPolicy pack has not run or failed midway.
func assertPermissionPolicySeedingComplete(db *sql.DB, result *AssertionResult) {
	log.Println("   🔍 Checking permission-policy seeding completeness...")

	bootstrapProfiles := make([]string, 0, len(constants.PlatformDefaultProfileIDs))
	for profileID := range constants.PlatformDefaultProfileIDs {
		bootstrapProfiles = append(bootstrapProfiles, profileID)
	}
	sort.Strings(bootstrapProfiles)
	for _, profileID := range bootstrapProfiles {
		var count int
		err := db.QueryRow(
			fmt.Sprintf(`SELECT COUNT(*) FROM %s op
				JOIN %s ps ON op.ParentId = ps.Id
				WHERE ps.%s = ? AND ps.%s = 1`,
				constants.TableObjectPermissions,
				constants.TablePermissionSet,
				constants.FieldPermissionSet_ProfileID,
				constants.FieldPermissionSet_IsOwnedByProfile),
			profileID,
		).Scan(&count)
		if err != nil {
			log.Printf("   ⚠️  Failed to count permissions for profile %s: %v", profileID, err)
			continue
		}
		if count == 0 {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "PermissionPolicySeeding",
				Severity:    constants.SeverityWarning,
				Object:      profileID,
				Description: fmt.Sprintf("profile %q has 0 ObjectPermissions — PlatformPolicy pack may not have run", profileID),
			})
		}
	}
}

// assertDerivedArtifactsPresent verifies that the metadata cache is loadable
// and that critical derived state exists. This is a lightweight check — it
// does not rebuild anything, only verifies presence.
func assertDerivedArtifactsPresent(db *sql.DB, result *AssertionResult) {
	log.Println("   🔍 Checking derived artifacts...")

	// Check that at least some _System_Object rows exist (metadata cache source)
	var objectCount int
	if err := db.QueryRow(
		fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE COALESCE(%s, 0) = 0",
			constants.TableObject, constants.FieldIsDeleted),
	).Scan(&objectCount); err != nil {
		log.Printf("   ⚠️  Failed to count objects: %v", err)
		return
	}
	if objectCount == 0 {
		result.Violations = append(result.Violations, AssertionViolation{
			Category:    "DerivedArtifacts",
			Severity:    constants.SeverityError,
			Object:      constants.TableObject,
			Description: "no objects found in _System_Object — metadata cache will be empty",
		})
	}

	// Check that at least some _System_Field rows exist
	var fieldCount int
	if err := db.QueryRow(
		fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE COALESCE(%s, 0) = 0",
			constants.TableField, constants.FieldIsDeleted),
	).Scan(&fieldCount); err != nil {
		log.Printf("   ⚠️  Failed to count fields: %v", err)
		return
	}
	if fieldCount == 0 {
		result.Violations = append(result.Violations, AssertionViolation{
			Category:    "DerivedArtifacts",
			Severity:    constants.SeverityError,
			Object:      constants.TableField,
			Description: "no fields found in _System_Field — metadata cache will be empty",
		})
	}
}