package bootstrap

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

	"github.com/nexuscrm/backend/internal/controlplane/packspec"
	"github.com/nexuscrm/shared/pkg/constants"
)

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
}

// assertManagedAccessPolicyConsistency checks that persisted _System_Object
// rows for all managed standard objects match the expected access policies
// defined in domain/schema/managed_permissions.go. This detects drift that
// runtime overlays previously masked.
func assertManagedAccessPolicyConsistency(db *sql.DB, result *AssertionResult) {
	log.Println("   🔍 Checking managed access policy consistency...")

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s`, COALESCE(`%s`, 0), COALESCE(`%s`, 0), `%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.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
	}
	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); err != nil {
			log.Printf("   ⚠️  Failed to scan standard object row: %v", err)
			return
		}
		byName[strings.ToLower(r.apiName)] = r
	}

	managedPolicies := packspec.StandardObjectPolicies()
	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 for non-customized objects (same logic as overlay)
		if !row.isCustomized || packspec.IsLegacyStandardObjectSharingModel(apiName, constants.SharingModel(row.sharingModel)) {
			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 := normalizeJSONForComparison(gotAccessPolicy)
		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 := normalizeJSONForComparison(wantAccessPolicy)
		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 that persisted _System_Relationship
// rows for the 4 managed relationships match the expected semantics.
func assertManagedRelationshipConsistency(db *sql.DB, result *AssertionResult) {
	log.Println("   🔍 Checking managed relationship consistency...")

	for _, def := range packspec.StandardRelationshipSemantics() {
		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 {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "ManagedRelationshipDrift",
				Severity:    constants.SeverityError,
				Object:      relKey,
				Description: "managed relationship not found in _System_Relationship",
			})
			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),
				})
			}
		}
	}
}

// assertRequiredConnectedAppsExist verifies that runtime-required connected
// apps (e.g., nexuscrm-ui for frontend login) are present in the database.
func assertRequiredConnectedAppsExist(db *sql.DB, result *AssertionResult) {
	log.Println("   🔍 Checking required connected apps...")

	requiredApps := []string{"nexuscrm-ui"}
	for _, clientID := range requiredApps {
		var count int
		err := db.QueryRow(
			fmt.Sprintf("SELECT COUNT(*) FROM `%s` WHERE `%s` = ?",
				constants.TableConnectedApp, constants.FieldSysConnectedApp_ConsumerKey),
			clientID,
		).Scan(&count)
		if err != nil {
			log.Printf("   ⚠️  Failed to query connected app %s: %v", clientID, err)
			continue
		}
		if count == 0 {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "RequiredConnectedApp",
				Severity:    constants.SeverityError,
				Object:      clientID,
				Description: fmt.Sprintf("required connected app %q not found — frontend login will fail", clientID),
			})
		}
	}
}

// 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 := []string{constants.ProfileSystemAdmin, constants.ProfileStandardUser}
	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",
		})
	}
}