package bootstrap

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

	"github.com/nexuscrm/backend/pkg/formula"
	"github.com/nexuscrm/shared/pkg/constants"
)

// --- Metadata/Schema Assertions ---

// assertObjectTableMapping verifies that every Object metadata entry has a corresponding DB table
func assertObjectTableMapping(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking object-table mapping verification...")

	// 1. Get all objects from metadata
	objSQL := fmt.Sprintf("SELECT `%s` FROM `%s`", constants.FieldAPIName, constants.TableObject)
	rows, err := db.Query(objSQL)
	if err != nil {
		log.Printf("   ⚠️  Could not query objects: %v", err)
		return
	}
	defer rows.Close()

	var objectNames []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err == nil {
			objectNames = append(objectNames, name)
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating objects: %v", err)
		return
	}

	// 2. Get all actual tables in one query.
	// Keys are lowercased because TiDB's lower_case_table_names=2 stores table names
	// as-created but compares case-insensitively; metadata ApiName casing may not
	// match physical table casing (e.g. "Contract" vs "contract").
	actualTables := make(map[string]bool)
	tRows, err := db.Query("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()")
	if err != nil {
		log.Printf("   ⚠️  Could not query information_schema.tables: %v", err)
		return
	}
	defer tRows.Close()
	for tRows.Next() {
		var name string
		if err := tRows.Scan(&name); err == nil {
			actualTables[strings.ToLower(name)] = true
		}
	}

	// 3. Diff (case-insensitive, matching TiDB resolution semantics)
	for _, name := range objectNames {
		if !actualTables[strings.ToLower(name)] {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "SchemaMismatch",
				Severity:    constants.SeverityError,
				Object:      name,
				Description: fmt.Sprintf("Metadata exists for object '%s', but the database table is missing.", name),
			})
		}
	}
}

// assertFieldColumnMapping verifies that every metadata field has a corresponding physical column.
// Uses two bulk queries (all fields + all columns) instead of per-object queries to avoid N+1.
func assertFieldColumnMapping(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking field-column mapping verification...")

	// 1. Bulk load all fields with their object names (single query)
	fieldSQL := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s`, f.`%s` FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` WHERE f.`%s` = 0",
		constants.FieldAPIName, constants.FieldAPIName, constants.FieldSysField_PhysicalName,
		constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.FieldIsDeleted,
	)

	fieldRows, err := db.Query(fieldSQL)
	if err != nil {
		log.Printf("   ⚠️  Could not bulk-query fields: %v", err)
		return
	}
	defer fieldRows.Close()

	// expectedCols[lowerTableName] = set of expected column names (lowercased).
	// Both table and column keys are lowercased to match TiDB's case-insensitive
	// name resolution (lower_case_table_names=2) — avoids false positives where
	// metadata and physical schema only differ in casing.
	expectedCols := make(map[string]map[string]bool)
	// Keep original object name for error reporting
	objNameByLower := make(map[string]string)
	for fieldRows.Next() {
		var objName, apiName string
		var physicalName sql.NullString
		if err := fieldRows.Scan(&objName, &apiName, &physicalName); err != nil {
			continue
		}
		colName := apiName
		if physicalName.Valid {
			candidate := strings.TrimSpace(physicalName.String)
			if !(strings.HasPrefix(candidate, "`") && strings.HasSuffix(candidate, "`")) && candidate != "" {
				colName = candidate
			}
		}
		lowerObj := strings.ToLower(objName)
		if expectedCols[lowerObj] == nil {
			expectedCols[lowerObj] = make(map[string]bool)
		}
		expectedCols[lowerObj][strings.ToLower(colName)] = true
		if _, ok := objNameByLower[lowerObj]; !ok {
			objNameByLower[lowerObj] = objName
		}
	}
	if err := fieldRows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating fields: %v", err)
		return
	}

	// 2. Bulk load all columns from information_schema (single query)
	colRows, err := db.Query("SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = DATABASE()")
	if err != nil {
		log.Printf("   ⚠️  Could not bulk-query information_schema.columns: %v", err)
		return
	}
	defer colRows.Close()

	// actualCols[lowerTableName] = set of actual column names (lowercased)
	actualCols := make(map[string]map[string]bool)
	for colRows.Next() {
		var tableName, colName string
		if err := colRows.Scan(&tableName, &colName); err == nil {
			lowerTbl := strings.ToLower(tableName)
			if actualCols[lowerTbl] == nil {
				actualCols[lowerTbl] = make(map[string]bool)
			}
			actualCols[lowerTbl][strings.ToLower(colName)] = true
		}
	}
	if err := colRows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating columns: %v", err)
		return
	}

	// 3. Diff in memory (case-insensitive)
	for lowerObj, expected := range expectedCols {
		actual := actualCols[lowerObj]
		var missing []string
		for col := range expected {
			if actual == nil || !actual[col] {
				missing = append(missing, col)
			}
		}
		if len(missing) > 0 {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "SchemaMismatch",
				Severity:    constants.SeverityError,
				Object:      objNameByLower[lowerObj],
				Description: fmt.Sprintf("Table '%s' is missing physical columns for fields: %s", objNameByLower[lowerObj], strings.Join(missing, ", ")),
			})
		}
	}
}

// assertRelationshipIntegrity ensures Lookup/MasterDetail fields point to valid objects.
//
// Only fields that are explicitly declared in the managed schema (system_tables.json
// + standard_objects.json via GetManagedTableDefinitions) are checked. Fields
// imported via Salesforce sync or discovery are skipped because they reference
// SF standard objects (e.g. ExternalDataSource, Calendar, Individual, BrandTemplate,
// CallCenter, FlowOrchestrationInstance, etc.) that NexusCRM does not implement.
// Those references are dangling foreign keys with no enforcement at the DB level
// (VARCHAR columns, no FK constraint), and runtime queries don't traverse them
// because the target tables don't exist.
//
// This mirrors the same whitelist principle used by assertSystemFieldsConsistency:
// the assertion's job is to verify the schemas NexusCRM owns, not the schemas it
// merely mirrors from Salesforce.
func assertRelationshipIntegrity(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking relationship integrity...")

	// Build the managed-fields whitelist: parent table -> set of declared field ApiNames.
	// Both keys are lowercased to match TiDB's case-insensitive name resolution.
	managedFields := make(map[string]map[string]bool)
	for _, def := range GetManagedTableDefinitions() {
		parent := strings.ToLower(def.TableName)
		if managedFields[parent] == nil {
			managedFields[parent] = make(map[string]bool)
		}
		for _, col := range def.Columns {
			managedFields[parent][strings.ToLower(col.Name)] = true
		}
	}

	// Get all object API names for validation
	objSQL := fmt.Sprintf("SELECT `%s` FROM `%s`", constants.FieldAPIName, constants.TableObject)
	rows, err := db.Query(objSQL)
	if err != nil {
		log.Printf("   ⚠️  Could not query objects: %v", err)
		return
	}
	defer rows.Close()
	validObjects := make(map[string]bool)
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			continue
		}
		validObjects[strings.ToLower(name)] = true
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating objects: %v", err)
	}

	// Check Lookup/MasterDetail fields
	refSQL := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s`, f.`%s`, f.`%s` FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` WHERE f.`%s` IN (?, ?) AND f.`%s` = 0",
		constants.FieldAPIName, constants.FieldAPIName, constants.FieldReferenceTo, constants.FieldType,
		constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.FieldType,
		constants.FieldIsDeleted,
	)

	fRows, err := db.Query(refSQL, constants.FieldTypeReference, constants.FieldTypeMasterDetail)
	if err != nil {
		log.Printf("   ⚠️  Could not query fields: %v", err)
		return
	}
	defer func() { fRows.Close() }()

	for fRows.Next() {
		var objName, fieldName, refToJSON, fType string
		if err := fRows.Scan(&objName, &fieldName, &refToJSON, &fType); err != nil {
			continue
		}

		// Whitelist: only validate fields that are explicitly declared in the
		// managed schema definitions. SF-imported and discovered fields are skipped.
		parentLower := strings.ToLower(objName)
		fieldLower := strings.ToLower(fieldName)
		if managedFields[parentLower] == nil || !managedFields[parentLower][fieldLower] {
			continue
		}

		// reference_to is stored as JSON array
		if strings.HasPrefix(refToJSON, "[") {
			refs := strings.ReplaceAll(strings.ReplaceAll(strings.ReplaceAll(refToJSON, "[", ""), "]", ""), "\"", "")
			targets := strings.Split(refs, ",")
			for _, target := range targets {
				target = strings.TrimSpace(target)
				if target != "" && !validObjects[strings.ToLower(target)] {
					result.Violations = append(result.Violations, AssertionViolation{
						Category:    "BrokenRelationship",
						Severity:    constants.SeverityError,
						Object:      objName,
						Description: fmt.Sprintf("Field '%s' references non-existent object '%s'", fieldName, target),
					})
				}
			}
		} else {
			// Invalid format - should be JSON array
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "InvalidFormat",
				Severity:    constants.SeverityError,
				Object:      objName,
				Description: fmt.Sprintf("Field '%s' has invalid reference_to format (expected JSON array): %s", fieldName, refToJSON),
			})
		}
	}
	if err := fRows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating relationship fields: %v", err)
	}
}

// assertConstraintConsistency checks if is_unique fields have actual DB indexes.
// Uses a bulk query for statistics instead of per-field queries to avoid N+1.
func assertConstraintConsistency(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking uniqueness constraints...")

	// 1. Get all fields marked unique
	uniqueSQL := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s` FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` WHERE f.`%s` = 1 AND f.`%s` = 0",
		constants.FieldAPIName, constants.FieldAPIName,
		constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.FieldSysField_IsUnique,
		constants.FieldIsDeleted,
	)

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

	type uniqueField struct{ Table, Column string }
	var fields []uniqueField
	for rows.Next() {
		var f uniqueField
		if err := rows.Scan(&f.Table, &f.Column); err == nil {
			fields = append(fields, f)
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating unique fields: %v", err)
		return
	}

	if len(fields) == 0 {
		return
	}

	// 2. Bulk load all unique indexes (single query)
	// Key: "table:column" -> has unique index
	uniqueIndexes := make(map[string]bool)
	idxRows, err := db.Query(`
		SELECT table_name, column_name FROM information_schema.statistics
		WHERE table_schema = DATABASE() AND non_unique = 0
	`)
	if err != nil {
		log.Printf("   ⚠️  Could not bulk-query statistics: %v", err)
		return
	}
	defer idxRows.Close()
	for idxRows.Next() {
		var tbl, col string
		if err := idxRows.Scan(&tbl, &col); err == nil {
			uniqueIndexes[tbl+":"+col] = true
		}
	}

	// 3. Diff in memory
	for _, f := range fields {
		if !uniqueIndexes[f.Table+":"+f.Column] {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "MissingConstraint",
				Severity:    constants.SeverityWarning,
				Object:      f.Table,
				Description: fmt.Sprintf("Field '%s' is marked unique but has no unique index in DB.", f.Column),
			})
		}
	}
}

// assertNamingConventions enforces snake_case for all API names
func assertNamingConventions(db *sql.DB, result *AssertionResult) {
	log.Println("   📋 Checking naming conventions...")

	validName := regexp.MustCompile(`^[a-z][a-z0-9_]*$`)

	// Check Objects
	objNamingSQL := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = 1 AND `%s` = 0",
		constants.FieldAPIName, constants.TableObject,
		constants.FieldIsCustom, constants.FieldIsDeleted,
	)

	rows, err := db.Query(objNamingSQL)
	if err == nil {
		for rows.Next() {
			var name string
			if err := rows.Scan(&name); err != nil {
				continue
			}
			if !validName.MatchString(name) {
				result.Violations = append(result.Violations, AssertionViolation{
					Category:    "NamingConvention",
					Severity:    constants.SeverityWarning,
					Object:      name,
					Description: fmt.Sprintf("Object API name '%s' should be snake_case (lowercase entry starting with letter).", name),
				})
			}
		}
		if err := rows.Err(); err != nil {
			log.Printf("   ⚠️  Error iterating objects: %v", err)
		}
		rows.Close()
	}

	// Check Fields
	// _System_Field does not have an IsCustom column. Treat fields as "custom"
	// when they live on a custom object or use the Salesforce-style __c suffix.
	fieldNamingSQL := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s` FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` "+
			"WHERE (o.`%s` = 1 OR LOWER(f.`%s`) REGEXP '__c$') AND f.`%s` = 0",
		constants.FieldAPIName, constants.FieldAPIName,
		constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.FieldSysObject_IsCustom, constants.FieldAPIName,
		constants.FieldIsDeleted,
	)

	fRows, err := db.Query(fieldNamingSQL)
	if err == nil {
		for fRows.Next() {
			var objName, fieldName string
			if err := fRows.Scan(&objName, &fieldName); err != nil {
				continue
			}
			if !validName.MatchString(fieldName) {
				result.Violations = append(result.Violations, AssertionViolation{
					Category:    "NamingConvention",
					Severity:    constants.SeverityWarning,
					Object:      objName,
					Description: fmt.Sprintf("Custom field '%s' should be snake_case.", fieldName),
				})
			}
		}
		if err := fRows.Err(); err != nil {
			log.Printf("   ⚠️  Error iterating custom fields: %v", err)
		}
		fRows.Close()
	}
}

// assertFormulaValidity checks if formula expressions can be parsed.
//
// DISABLED BY DEFAULT: The current formula.Engine does not support Salesforce
// relationship field references (e.g. "Account.Name", "Owner.IsActive",
// "CreatedBy.Profile") or several Salesforce-specific functions (e.g. CASESAFEID),
// producing 250+ false-positive "unknown name" errors on real production data.
//
// Set ENABLE_FORMULA_VALIDATION=true to re-enable once formula.Engine supports:
//   1. Relationship field resolution (dotted paths)
//   2. Field-set context (so undefined names can be resolved against the object's fields)
//   3. Salesforce-native functions that are currently missing
//
// Until then, syntax errors in formulas are still caught at runtime by ValidateRecord
// (validation_service.go) when records are created/updated.
func assertFormulaValidity(db *sql.DB, result *AssertionResult) {
	if os.Getenv("ENABLE_FORMULA_VALIDATION") != "true" {
		log.Println("   📋 Skipping formula validity check (set ENABLE_FORMULA_VALIDATION=true to enable)")
		return
	}
	log.Println("   📋 Checking formula validity...")

	engine := formula.NewEngine()

	formulaSQL := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s`, f.`%s` FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` WHERE f.`%s` IS NOT NULL AND f.`%s` != '' AND f.`%s` = 0",
		constants.FieldAPIName, constants.FieldAPIName, constants.FieldSysField_Formula,
		constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.FieldSysField_Formula, constants.FieldSysField_Formula,
		constants.FieldIsDeleted,
	)

	rows, err := db.Query(formulaSQL)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var objName, fieldName, expression string
		if err := rows.Scan(&objName, &fieldName, &expression); err != nil {
			log.Printf("Failed to scan formula row: %v", err)
			return
		}

		// Validate syntax (dry run)
		if err := engine.Validate(expression, map[string]interface{}{}); err != nil {
			result.Violations = append(result.Violations, AssertionViolation{
				Category:    "InvalidFormula",
				Severity:    constants.SeverityError,
				Object:      objName,
				Description: fmt.Sprintf("Field '%s' has invalid formula: %v", fieldName, err),
			})
		}
	}
	if err := rows.Err(); err != nil {
		log.Printf("   ⚠️  Error iterating formulas: %v", err)
	}
}