package bootstrap

import (
	"database/sql"
	"fmt"
	"log"
	"strings"

	"github.com/nexuscrm/backend/internal/infrastructure/database"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
)

// AssertionViolation represents a single design violation
type AssertionViolation struct {
	Category    string // e.g., "SystemFields", "DuplicateAction"
	Severity    string // "error" or "warning"
	Object      string // Table/Object name affected
	Description string
}

// AssertionResult contains all violations found during assertion checks
type AssertionResult struct {
	Violations []AssertionViolation
	Passed     bool
}

// RunAssertions executes all design assertions and returns violations
// By default, violations are logged as warnings. Use strictMode=true to fail on violations.
func RunAssertions(db *database.TiDBConnection, strictMode bool) (*AssertionResult, error) {
	log.Println("🔍 Running startup assertions...")

	result := &AssertionResult{
		Violations: []AssertionViolation{},
		Passed:     true,
	}

	// Run all assertions
	assertSystemFieldsConsistency(db.DB(), result)
	assertNoDuplicateActions(db.DB(), result)
	assertNoDuplicateFlows(db.DB(), result)
	assertNoOrphanedSharingRules(db.DB(), result)
	assertDefaultThemeExists(db.DB(), result)

	assertSystemAdminProfileExists(db.DB(), result)
	assertSystemAdminUserExists(db.DB(), result)
	assertCriticalTablesExist(db.DB(), result)
	assertManagedSystemObjectVisibility(db.DB(), result)
	assertObjectTableMapping(db.DB(), result)
	assertFieldColumnMapping(db.DB(), result)
	assertRelationshipIntegrity(db.DB(), result)
	assertConstraintConsistency(db.DB(), result)
	assertNamingConventions(db.DB(), result)
	assertFormulaValidity(db.DB(), result)

	// Managed metadata drift detection (catches overlays-were-masking-DB-drift bugs)
	assertManagedAccessPolicyConsistency(db.DB(), result)
	assertManagedRelationshipConsistency(db.DB(), result)

	// Runtime-required infrastructure (verify gate Section 7.3.1)
	assertRequiredConnectedAppsExist(db.DB(), result)
	assertPermissionPolicySeedingComplete(db.DB(), result)
	assertDerivedArtifactsPresent(db.DB(), result)

	// Report results
	if len(result.Violations) == 0 {
		log.Println("✅ All assertions passed")
		return result, nil
	}

	result.Passed = false
	log.Printf("⚠️  Found %d assertion violation(s):", len(result.Violations))
	for i, v := range result.Violations {
		log.Printf("   %d. [%s] %s: %s", i+1, v.Severity, v.Category, v.Description)
	}

	// Check for errors
	errorCount := 0
	for _, v := range result.Violations {
		if v.Severity == constants.SeverityError {
			errorCount++
		}
	}

	if strictMode && errorCount > 0 {
		return result, apperrors.NewInternalError(fmt.Sprintf("assertion failures in strict mode: %d critical violation(s) and %d warning(s)", errorCount, len(result.Violations)-errorCount), nil)
	}

	return result, nil
}

// assertSystemFieldsConsistency checks that all MANAGED tables have required system fields.
//
// Only tables declared in system_tables.json (or returned by GetManagedTableDefinitions())
// are checked. Everything else is exempted:
//   - Discovered tables registered by DiscoverAndRegisterPhysicalTables (Id prefix "disc_")
//   - Salesforce-imported legacy tables with UUID-style _System_Object Ids
//     (e.g. addresshistory, quotehistory, userhistory, orgemailaddresssecurity)
//   - Stale managed registrations whose ApiName has since been removed from JSON
//     (e.g. _Dev_Session)
//
// Rationale: system_tables.json is the single source of truth for tables that NexusCRM
// owns. Tables outside this file have schemas defined externally (Salesforce, data
// migration, legacy imports). Many Salesforce audit/share tables legitimately lack
// CreatedDate or LastModifiedDate (append-only semantics — UserHistory has no
// LastModifiedDate by design), and enforcing the rule on them would produce false
// positives. Bootstrap only owns what it declares.
//
// The previous "Id NOT LIKE 'disc_%'" filter missed UUID-id legacy imports and stale
// `obj_` rows; switching to a positive whitelist resolves those false positives.
func assertSystemFieldsConsistency(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking system fields consistency...")

	// Required system fields for all tables
	requiredFields := constants.GetSystemFieldNames()

	// Build the managed-tables whitelist from the bootstrap definitions.
	// Keys are lowercased to match TiDB's case-insensitive name resolution.
	managedSet := make(map[string]bool)
	for _, def := range GetManagedTableDefinitions() {
		managedSet[strings.ToLower(def.TableName)] = true
	}

	// Query all objects from metadata, then filter to managed-only in Go.
	objSQL := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = 0",
		constants.FieldAPIName, constants.TableObject, constants.FieldIsDeleted,
	)

	rows, err := db.Query(objSQL)
	if err != nil {
		log.Printf("   ⚠️  Could not query objects: %v", err)
		return
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err == nil {
			// Whitelist: only check tables declared in managed schema definitions.
			if managedSet[strings.ToLower(name)] {
				tables = append(tables, name)
			}
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating objects: %v", err)
		return
	}

	// Check each managed table has all required fields in metadata
	for _, tableName := range tables {
		// Query fields for this object
		fieldSQL := fmt.Sprintf(
			"SELECT f.`%s` FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` WHERE o.`%s` = ? AND (f.`%s` = ? OR f.`%s` IS NULL)",
			constants.FieldAPIName,
			constants.TableField,
			constants.TableObject,
			constants.FieldObjectID,
			constants.ColID,
			constants.FieldAPIName,
			constants.FieldIsDeleted,
			constants.FieldIsDeleted,
		)

		fieldRows, err := db.Query(fieldSQL, tableName, false)
		if err != nil {
			continue
		}

		existingFields := make(map[string]bool)
		for fieldRows.Next() {
			var fieldName string
			if err := fieldRows.Scan(&fieldName); err == nil {
				existingFields[fieldName] = true
			}
		}
		if err := fieldRows.Err(); err != nil {
			log.Printf("   ⚠️  Error iterating fields for %s: %v", tableName, err)
		}
		_ = fieldRows.Close()

		// Check for missing required fields
		var missing []string
		for _, required := range requiredFields {
			if !existingFields[required] {
				missing = append(missing, required)
			}
		}

		if len(missing) > 0 {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "SystemFields",
				Severity:    constants.SeverityError,
				Object:      tableName,
				Description: fmt.Sprintf("Table '%s' missing system fields in metadata: %s", tableName, strings.Join(missing, ", ")),
			})
		}
	}
}

// assertNoDuplicateActions checks that no object has duplicate action names
func assertNoDuplicateActions(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for duplicate actions...")

	query := fmt.Sprintf(
		"SELECT `%s`, `%s`, COUNT(*) as cnt FROM `%s` GROUP BY `%s`, `%s` HAVING cnt > 1",
		constants.FieldObjectAPIName,
		constants.FieldName,
		constants.TableAction,
		constants.FieldObjectAPIName,
		constants.FieldName,
	)

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("   ⚠️  Could not query actions: %v", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var objectName, actionName string
		var count int
		if err := rows.Scan(&objectName, &actionName, &count); err == nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "DuplicateAction",
				Severity:    constants.SeverityError,
				Object:      objectName,
				Description: fmt.Sprintf("Object '%s' has %d duplicate actions named '%s'", objectName, count, actionName),
			})
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating actions: %v", err)
	}
}

// assertNoDuplicateFlows checks that no object has duplicate flow triggers
func assertNoDuplicateFlows(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for duplicate flows...")

	query := fmt.Sprintf(`SELECT fd.%s, fd.%s, COUNT(*) as cnt
		FROM %s f
		JOIN %s fd ON f.%s = fd.%s
		WHERE f.%s = 'Active' AND fd.%s != 'schedule'
		GROUP BY fd.%s, fd.%s
		HAVING cnt > 1`,
		constants.FieldFlowDefinition_TriggerObjectOrEvent,
		constants.FieldFlowDefinition_TriggerType,
		constants.TableFlow,
		constants.TableFlowDefinition,
		constants.FieldFlow_DefinitionID,
		constants.FieldFlowDefinition_ID,
		constants.FieldFlow_Status,
		constants.FieldFlowDefinition_TriggerType,
		constants.FieldFlowDefinition_TriggerObjectOrEvent,
		constants.FieldFlowDefinition_TriggerType,
	)

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("   ⚠️  Could not query flows: %v", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var objectName, triggerType string
		var count int
		if err := rows.Scan(&objectName, &triggerType, &count); err == nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "DuplicateFlow",
				Severity:    constants.SeverityWarning,
				Object:      objectName,
				Description: fmt.Sprintf("Object '%s' has %d active flows with trigger '%s'", objectName, count, triggerType),
			})
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating flows: %v", err)
	}
}

// assertNoOrphanedSharingRules checks that sharing rules point to valid groups/users
func assertNoOrphanedSharingRules(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for orphaned sharing rules...")

	query := fmt.Sprintf("SELECT sr.%s, sr.%s, sr.%s FROM %s sr LEFT JOIN `%s` g ON sr.%s = g.%s WHERE sr.%s IS NOT NULL AND g.%s IS NULL",
		constants.ColID, constants.FieldName, "ShareWithGroupId", constants.TableSharingRule, "Group", "ShareWithGroupId", constants.ColID, "ShareWithGroupId", constants.ColID)

	rows, err := db.Query(query)
	if err != nil {
		log.Printf("   ⚠️  Could not query sharing rules: %v", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var ruleID, ruleName, groupID string
		if err := rows.Scan(&ruleID, &ruleName, &groupID); err == nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "OrphanedSharingRule",
				Severity:    constants.SeverityWarning,
				Object:      ruleName,
				Description: fmt.Sprintf("Sharing rule '%s' references non-existent group '%s'", ruleName, groupID),
			})
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating sharing rules: %v", err)
	}
}

// assertDefaultThemeExists checks if a theme exists
func assertDefaultThemeExists(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for themes...")

	var count int
	// Plain count check
	err := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM %s", constants.TableTheme)).Scan(&count)
	if err != nil {
		log.Printf("   ⚠️  Could not query themes: %v", err)
		return
	}

	if count == 0 {
		result.Violations = append(result.Violations, AssertionViolation{
			Category:    "MissingData",
			Severity:    constants.SeverityWarning,
			Object:      constants.TableTheme,
			Description: "No themes defined. UI will not load correctly.",
		})
	}
}

// assertSystemAdminProfileExists ensures the admin profile is present AND alive.
// A soft-deleted (IsDeleted=1) row is treated as missing — issue #322 showed the
// old check passed even after the admin profile had been soft-deleted, leaving
// the tenant in a locked-out state while verify reported ✅.
func assertSystemAdminProfileExists(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for System Admin profile...")

	var count int
	err := db.QueryRow(
		fmt.Sprintf("SELECT COUNT(*) FROM `%s` WHERE `%s` = ? AND `%s` = 0",
			constants.TableProfile, constants.ColID, constants.FieldIsDeleted),
		constants.ProfileSystemAdmin,
	).Scan(&count)
	if err != nil {
		log.Printf("   ⚠️  Could not query profiles: %v", err)
		return
	}

	if count == 0 {
		result.Violations = append(result.Violations, AssertionViolation{
			Category:    "MissingSystemData",
			Severity:    constants.SeverityError,
			Object:      constants.TableProfile,
			Description: fmt.Sprintf("Critical: System Admin profile '%s' is missing or soft-deleted.", constants.ProfileSystemAdmin),
		})
	}
}

// assertSystemAdminUserExists ensures there is at least one active admin user
func assertSystemAdminUserExists(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for active System Admin user...")

	var count int
	err := db.QueryRow(
		fmt.Sprintf("SELECT COUNT(*) FROM `%s` WHERE `%s` = ? AND `%s` = ?", constants.TableUser, constants.FieldUser_ProfileID, constants.FieldUser_IsActive),
		constants.ProfileSystemAdmin,
		true,
	).Scan(&count)
	if err != nil {
		log.Printf("   ⚠️  Could not query users: %v", err)
		return
	}

	if count == 0 {
		result.Violations = append(result.Violations, AssertionViolation{
			Category:    "MissingData",
			Severity:    constants.SeverityError,
			Object:      constants.TableUser,
			Description: "No active System Admin users found. You may be locked out.",
		})
	}
}

// assertCriticalTablesExist checks for presence of core system tables
func assertCriticalTablesExist(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking for critical system tables...")

	tables := []string{
		constants.TableObject,
		constants.TableField,
		constants.TableProfile,
		constants.TableUser,
		constants.TableApp,
	}

	for _, t := range tables {
		// Use Raw SQL to avoid builder backticking "1" as a column
		query := fmt.Sprintf("SELECT 1 FROM %s LIMIT 1", t)
		_, err := db.Exec(query)
		if err != nil {
			// Check if error is actually "table doesn't exist" vs "empty table" (which Select 1 handles fine usually, but Exec might return error if no rows?)
			// Actually DB.Exec on Select might return result with 0 rows affected, and nil error.
			// Only if table doesn't exist, it returns specific error.

			// Wait, if table is empty, SELECT 1 LIMIT 1 returns 0 rows. Exec returns nil error?
			// Yes.

			// However, verify specifically for Table doesn't exist error code if possible, or just assume error = missing.
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "MissingTable",
				Severity:    constants.SeverityError,
				Object:      t,
				Description: fmt.Sprintf("Critical system table '%s' is missing from the database (Error: %v).", t, err),
			})
		}
	}
}

// assertManagedSystemObjectVisibility checks that bootstrap-owned system
// objects are present and not soft-deleted in _System_Object.
//
// This catches the specific drift mode that presents as a 404 on
// `/sobjects/_System_File/describe`: the physical table still exists, but the
// metadata row was hidden by `IsDeleted = 1`.
func assertManagedSystemObjectVisibility(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking managed system object visibility...")

	type rowState struct {
		exists    bool
		isDeleted bool
	}

	rows, err := db.Query(
		fmt.Sprintf("SELECT `%s`, `%s` FROM `%s`", constants.FieldAPIName, constants.FieldIsDeleted, constants.TableObject),
	)
	if err != nil {
		log.Printf("   ⚠️  Could not query managed object visibility: %v", err)
		return
	}
	defer rows.Close()

	visibility := make(map[string]rowState)
	for rows.Next() {
		var apiName string
		var deleted sql.NullBool
		if err := rows.Scan(&apiName, &deleted); err != nil {
			continue
		}
		visibility[strings.ToLower(apiName)] = rowState{
			exists:    true,
			isDeleted: deleted.Valid && deleted.Bool,
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating managed object visibility rows: %v", err)
		return
	}

	for _, def := range GetManagedTableDefinitions() {
		state, ok := visibility[strings.ToLower(def.TableName)]
		if !ok || state.isDeleted {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "MissingData",
				Severity:    constants.SeverityError,
				Object:      def.TableName,
				Description: fmt.Sprintf("Managed object '%s' is missing or soft-deleted in _System_Object; run setup apply to restore visibility.", def.TableName),
			})
		}
	}
}

// Metadata/Schema assertion functions are in assertions_metadata.go:
// - assertObjectTableMapping, assertFieldColumnMapping
// - assertRelationshipIntegrity, assertConstraintConsistency
// - assertNamingConventions, assertFormulaValidity