package persistence

// STRICT ALIASING ENFORCEMENT
// This file manipulates physical schema columns.
// - DDL Operations: Must use PHYSICAL names (col.PhysicalName or constants.ColID).
// - Query Builder: Must use LOGICAL names (constants.FieldID) which are then aliased.
// Do not mix these contexts.

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

	"github.com/nexuscrm/backend/internal/domain/schema"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

var (
	// columnNameRegex validates names for tables and columns.
	// Allows alphanumeric (PascalCase, camelCase, snake_case) + underscores.
	columnNameRegex = regexp.MustCompile(`^[a-zA-Z_][a-zA-Z0-9_]*$`)
)

// AddColumn adds a column to the table and registers it
func (r *SchemaRepository) AddColumn(tableName string, col schema.ColumnDefinition) error {
	log.Printf("➕ Adding column %s to table %s", col.Name, tableName)

	// VALIDATION: Table Name
	// System tables (starting with _System_) are exempt from strict snake_case but we generally don't add columns to them dynamically
	if !constants.IsSystemTable(tableName) {
		if !columnNameRegex.MatchString(tableName) {
			return apperrors.NewValidationError("tableName", fmt.Sprintf("invalid table name '%s': must be alphanumeric (PascalCase/snake_case)", tableName))
		}
	}

	// VALIDATION: Field Definition
	if err := r.ValidateFieldDefinition(col); err != nil {
		return err
	}

	// 0. IDEMPOTENCY CHECK: Check if column already exists (Zombie/Orphan recovery)
	exists, err := r.checkColumnExists(tableName, col.Name)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to check column existence for %s.%s", tableName, col.Name), err)
	}

	if exists {
		log.Printf("⚠️  Orphan column detected: %s.%s exists in DB but missing in metadata.", tableName, col.Name)

		// Nullability drift reconcile (relax-only): if spec says nullable but physical
		// column is NOT NULL, emit MODIFY ... NULL. If spec says NOT NULL but physical
		// is nullable, WARN only — auto-tightening is unsafe on live data and requires
		// an explicit admin action.
		if err := r.reconcileNullability(tableName, col); err != nil {
			log.Printf("   ⚠️  Nullability reconcile failed for %s.%s: %v. Proceeding with adoption.", tableName, col.Name, err)
		}

		// ROOT FIX: Check if existing column type matches expected type
		// If not, we need to MODIFY the column to fix the type mismatch
		existingType, err := r.getColumnType(tableName, col.Name)
		if err != nil {
			log.Printf("   ⚠️  Could not determine existing column type: %v. Proceeding with adoption.", err)
		} else {
			expectedType := col.Type // col.Type is already the SQL type (mapped in metadata_field_crud.go)
			if !r.isTypeCompatible(existingType, expectedType) {
				log.Printf("   🔄 Type mismatch detected: existing=%s, expected=%s. Executing MODIFY COLUMN...", existingType, expectedType)

				// TiDB Fix: Strip UNIQUE from MODIFY COLUMN and handle separately
				colDDL := r.buildColumnDDL(col, false)
				isUnique := col.Unique
				// No need to replace " UNIQUE" anymore

				modifyDDL := fmt.Sprintf("ALTER TABLE `%s` MODIFY COLUMN %s", tableName, colDDL)
				log.Printf("   🏁 Executing DDL: %s", modifyDDL)

				if _, err := r.db.Exec(modifyDDL); err != nil {
					log.Printf("   ⚠️  MODIFY COLUMN failed (data may be incompatible): %v. Proceeding with adoption.", err)
				} else {
					log.Printf("   ✅ Column type corrected successfully")

					// Add Unique Index if needed
					if isUnique {
						idxName := schema.GenerateUniqueIndexName(tableName, col.Name)
						idxDDL := fmt.Sprintf("ALTER TABLE `%s` ADD UNIQUE KEY `%s` (`%s`)", tableName, idxName, col.Name)
						log.Printf("   🔑 Adding Unique Constraint: %s", idxDDL)
						if _, err := r.db.Exec(idxDDL); err != nil {
							if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
								log.Printf("   ⚠️  Unique Index already exists, skipping.")
							} else {
								log.Printf("   ⚠️  Failed to add Unique Index: %v", err)
							}
						}
					}
				}
			} else {
				log.Printf("   ✅ Existing type is compatible. Adopting column...")

				// Ensure Unique Constraint if required (Idempotent)
				if col.Unique {
					idxName := schema.GenerateUniqueIndexName(tableName, col.Name)
					idxDDL := fmt.Sprintf("ALTER TABLE `%s` ADD UNIQUE KEY `%s` (`%s`)", tableName, idxName, col.Name)
					log.Printf("   🔑 Ensuring Unique Constraint: %s", idxDDL)
					if _, err := r.db.Exec(idxDDL); err != nil {
						if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
							log.Printf("   ⚠️  Unique Index already exists, skipping.")
						} else {
							return apperrors.NewInternalError(fmt.Sprintf("failed to add Unique Index during adoption for %s.%s", tableName, col.Name), err)
						}
					}
				}
			}
		}
	} else {
		// 1. DDL: ALTER TABLE ADD COLUMN
		colDDL := r.buildColumnDDL(col, false)
		var ddl string
		var indexDDL string

		// TiDB Fix: Separate UNIQUE constraint
		if col.Unique {
			// No need to replace " UNIQUE" anymore
			idxName := schema.GenerateUniqueIndexName(tableName, col.Name)

			ddl = fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN %s", tableName, colDDL)
			indexDDL = fmt.Sprintf("ALTER TABLE `%s` ADD UNIQUE KEY `%s` (`%s`)", tableName, idxName, col.Name)
		} else {
			ddl = fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN %s", tableName, colDDL)
		}

		log.Printf("   🏁 Executing DDL: %s", ddl)
		if _, err := r.db.Exec(ddl); err != nil {
			log.Printf("   ❌ DDL execution failed: %v", err)
			return apperrors.NewInternalError(fmt.Sprintf("failed to add column to table %s", tableName), err)
		}

		// Execute Index DDL if present
		if indexDDL != "" {
			log.Printf("   🏁 Executing Index DDL: %s", indexDDL)
			if _, err := r.db.Exec(indexDDL); err != nil {
				// If index exists (duplicate), ignore. Else fail.
				if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
					log.Printf("   ⚠️  Unique Index already exists, skipping.")
				} else {
					return apperrors.NewInternalError("failed to add Unique Index", err)
				}
			}
		}

		log.Printf("   ✅ DDL execution complete")
	}

	// If it's a reference field (single target), we handle the Foreign Key in a separate statement
	var fkDDL string
	// Fix: Generate safe column name (max 64 chars) using helper
	safeColName := schema.GenerateSafeIdentifier(col.Name, "col")

	if len(col.ReferenceTo) == 1 && !strings.EqualFold(col.ReferenceTo[0], "parent") {
		rawFKName := fmt.Sprintf("fk_%s_%s", tableName, safeColName)
		fkName := schema.GenerateSafeIdentifier(rawFKName, "fk")

		// Use physical ID column name for FK target
		targetPK := constants.ColID

		fkDDL = fmt.Sprintf("ALTER TABLE `%s` ADD CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s` (`%s`)",
			tableName, fkName, safeColName, col.ReferenceTo[0], targetPK)

		if col.OnDelete != "" {
			fkDDL += fmt.Sprintf(" ON DELETE %s", col.OnDelete)
		}
	}

	// 1.5. DDL: ADD FOREIGN KEY (if applicable)
	if fkDDL != "" {
		log.Printf("   🔗 Adding Foreign Key constraint...")
		if _, err := r.db.Exec(fkDDL); err != nil {
			// Handle "Duplicate constraint" error for idempotency
			if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
				log.Printf("⚠️  FK Constraint %s already exists, skipping...", fmt.Sprintf("fk_%s_%s", tableName, col.Name))
			} else {
				// Only rollback if we actually created the column in this run
				if !exists {
					log.Printf("⚠️ Failed to add FK, rolling back column: %v", err)
					if dropErr := r.DropColumn(tableName, col.Name); dropErr != nil {
						log.Printf("⚠️ Rollback column drop failed: %v", dropErr)
					}
				}
				return apperrors.NewInternalError("failed to add foreign key constraint", err)
			}
		}
	}

	// 2. Register in _System_Field
	if err := r.registerField(tableName, col, r.db); err != nil {
		log.Printf("⚠️  Failed to register field %s.%s: %v. Attempting rollback...", tableName, col.Name, err)

		// COMPENSATION
		if !exists {
			rollbackDDL := fmt.Sprintf("ALTER TABLE `%s` DROP COLUMN `%s`", tableName, col.Name)
			if _, rbErr := r.db.Exec(rollbackDDL); rbErr != nil {
				// Critical error: Data vs Metadata inconsistency
				log.Printf("🔥 CRITICAL: Failed to rollback column %s.%s after metadata failure: %v", tableName, col.Name, rbErr)
				return apperrors.NewInternalError("failed to register field AND failed to rollback DDL (critical inconsistency)", err)
			}
			log.Printf("   ✅ Rollback successful: Column %s.%s dropped", tableName, col.Name)
		} else {
			log.Printf("⚠️  Skipping rollback for adopted orphan column %s.%s to preserve data.", tableName, col.Name)
		}

		return apperrors.NewInternalError(fmt.Sprintf("failed to register field metadata (DDL rolled back: %v)", !exists), err)
	}

	log.Printf("   ✅ Column added/registered: %s.%s", tableName, col.Name)
	return nil
}

// RegisterFieldOnly writes metadata to _System_Field without touching the physical table.
// Used for active formula fields whose values are computed by TiDB Views.
func (r *SchemaRepository) RegisterFieldOnly(tableName string, col schema.ColumnDefinition) error {
	return r.registerField(tableName, col, r.db)
}

// EnsureColumn guarantees a column exists and matches the metadata definition.
// It acts as a "Reconcile" function handling all 4 states:
// 1. Fresh Create (Missing Physical, Missing Meta)
// 2. Orphan Adoption (Existing Physical, Missing Meta)
// 3. Ghost Repair (Missing Physical, Existing Meta)
// 4. Consistency Check (Existing Physical, Existing Meta) - Verifies type & Upserts Valid Meta
func (r *SchemaRepository) EnsureColumn(tableName string, col schema.ColumnDefinition) error {
	// We deliberately delegate to AddColumn instead of short-circuiting on checkColumnExists.
	// AddColumn handles the complex logic of:
	// - Checking if physical column exists (Idempotency)
	// - Checking type compatibility (Drift Repair)
	// - Upserting metadata (Consistency)
	log.Printf("🛡️  Ensuring column %s.%s is reconciled...", tableName, col.Name)
	return r.AddColumn(tableName, col)
}

// checkColumnExists queries INFORMATION_SCHEMA to see if a column exists
func (r *SchemaRepository) checkColumnExists(tableName, columnName string) (bool, error) {
	query := `
		SELECT COUNT(*) 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_SCHEMA = DATABASE() 
		  AND TABLE_NAME = ? 
		  AND COLUMN_NAME = ?
	`
	var count int
	if err := r.db.QueryRow(query, tableName, columnName).Scan(&count); err != nil {
		return false, err
	}
	return count > 0, nil
}

// DropColumn drops a column from the table and unregisters it
func (r *SchemaRepository) DropColumn(tableName string, columnName string) error {
	log.Printf("➖ Dropping column %s from table %s", columnName, tableName)

	// VALIDATION: Table/Column Name
	if !constants.IsSystemTable(tableName) {
		if !columnNameRegex.MatchString(tableName) {
			return apperrors.NewValidationError("tableName", fmt.Sprintf("invalid table name '%s': must be alphanumeric (PascalCase/snake_case)", tableName))
		}
		if !columnNameRegex.MatchString(columnName) {
			return apperrors.NewValidationError("columnName", fmt.Sprintf("invalid column name '%s': must be alphanumeric (PascalCase/snake_case)", columnName))
		}
	}

	// 0. IDEMPOTENCY CHECK: Check if column exists
	exists, err := r.checkColumnExists(tableName, columnName)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to check column existence for %s.%s", tableName, columnName), err)
	}

	if !exists {
		log.Printf("⚠️  Ghost column detected: %s.%s missing from DB but exists in metadata. Skipping DDL and removing metadata...", tableName, columnName)
	} else {
		// 1. DDL: ALTER TABLE DROP COLUMN
		ddl := fmt.Sprintf("ALTER TABLE `%s` DROP COLUMN `%s`", tableName, columnName)
		if _, err := r.db.Exec(ddl); err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("failed to drop column from table %s", tableName), err)
		}
	}

	// 2. Unregister from _System_Field
	// We must look up the actual field ID from the database, not generate a new one
	// because GenerateFieldID() uses UUID which won't match the existing record.
	// 2. Unregister from _System_Field
	// We must look up the actual field ID from the database, not generate a new one
	// because GenerateFieldID() uses UUID which won't match the existing record.
	subQ := query.From(constants.TableObject).
		Select([]string{constants.FieldID}).
		Where(constants.FieldSysObject_APIName, tableName).
		Build()

	qr := query.Delete(constants.TableField).
		WhereRaw(fmt.Sprintf("%s IN (%s)", constants.FieldObjectID, subQ.SQL), subQ.Params).
		Where(constants.FieldSysField_APIName, columnName).
		Build()

	if _, err := r.db.Exec(qr.SQL, qr.Params...); err != nil {
		log.Printf("⚠️  Warning: Failed to unregister field %s.%s: %v", tableName, columnName, err)
	}

	log.Printf("   ✅ Column dropped: %s.%s", tableName, columnName)
	return nil
}

// ModifyColumn modifies a column's type (widening only: Boolean -> LongTextArea, etc.)
func (r *SchemaRepository) ModifyColumn(tableName, columnName string, newCol schema.ColumnDefinition) error {
	log.Printf("🔧 Modifying column %s.%s to type %s", tableName, columnName, newCol.Type)

	// VALIDATION: Table/Column Name
	if !constants.IsSystemTable(tableName) {
		if !columnNameRegex.MatchString(tableName) {
			return apperrors.NewValidationError("tableName", fmt.Sprintf("invalid table name '%s': must be alphanumeric (PascalCase/snake_case)", tableName))
		}
		if !columnNameRegex.MatchString(columnName) {
			return apperrors.NewValidationError("columnName", fmt.Sprintf("invalid column name '%s': must be alphanumeric (PascalCase/snake_case)", columnName))
		}
	}

	// Check column exists
	exists, err := r.checkColumnExists(tableName, columnName)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to check column existence for %s.%s", tableName, columnName), err)
	}
	if !exists {
		return apperrors.NewNotFoundError("column", fmt.Sprintf("%s.%s", tableName, columnName))
	}

	// Execute ALTER TABLE MODIFY COLUMN
	// TiDB Fix: Separate UNIQUE constraint
	colDDL := r.buildColumnDDL(newCol, false)
	isUnique := newCol.Unique
	// No need to replace " UNIQUE" anymore

	ddl := fmt.Sprintf("ALTER TABLE `%s` MODIFY COLUMN %s", tableName, colDDL)
	log.Printf("   🏁 Executing DDL: %s", ddl)
	if _, err := r.db.Exec(ddl); err != nil {
		log.Printf("   ❌ DDL execution failed: %v", err)
		return apperrors.NewInternalError(fmt.Sprintf("failed to modify column %s.%s", tableName, columnName), err)
	}

	// Add Unique Index if needed
	if isUnique {
		idxName := schema.GenerateUniqueIndexName(tableName, newCol.Name)
		idxDDL := fmt.Sprintf("ALTER TABLE `%s` ADD UNIQUE KEY `%s` (`%s`)", tableName, idxName, newCol.Name)
		log.Printf("   🔑 Adding Unique Constraint: %s", idxDDL)
		if _, err := r.db.Exec(idxDDL); err != nil {
			if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
				log.Printf("   ⚠️  Unique Index already exists, skipping.")
			} else {
				return apperrors.NewInternalError("failed to add Unique Index", err)
			}
		}
	}

	log.Printf("   ✅ DDL execution complete")

	// Update metadata in _System_Field
	if err := r.registerField(tableName, newCol, r.db); err != nil {
		log.Printf("⚠️  Failed to update field metadata for %s.%s: %v", tableName, columnName, err)
		// Don't rollback - the DDL succeeded and data is safe with the new type
		return apperrors.NewInternalError(fmt.Sprintf("column %s.%s modified but metadata update failed", tableName, columnName), err)
	}

	log.Printf("   ✅ Column modified: %s.%s", tableName, columnName)
	return nil
}

// registerField registers a single field in _System_Field
func (r *SchemaRepository) registerField(tableName string, col schema.ColumnDefinition, exec Executor) error {
	if exec == nil {
		exec = r.db
	}

	// Fix: Lookup actual Object ID from DB to ensure FK integrity
	// We cannot use GenerateObjectID because it is now random/UUID based
	var objectID string
	qr := query.From(constants.TableObject).
		Select([]string{constants.FieldID}).
		Where(constants.FieldSysObject_APIName, tableName).
		Limit(1).
		Build()

	// Use the provided executor (tx or db)
	// QueryRow is supported by both *sql.DB and *sql.Tx
	row := exec.QueryRow(qr.SQL, qr.Params...)
	if err := row.Scan(&objectID); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to resolve object ID for table %s", tableName), err)
	}

	fieldID := schema.GenerateFieldID(tableName, col.Name)
	field := r.buildFieldMetadataFromColumn(col)

	return r.SaveFieldMetadataWithIDs(context.Background(), field, objectID, tableName, fieldID, exec)
}

// getColumnNullable retrieves IS_NULLABLE ('YES'/'NO') for the given column.
func (r *SchemaRepository) getColumnNullable(tableName, columnName string) (bool, error) {
	const q = `
		SELECT IS_NULLABLE
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND COLUMN_NAME = ?
	`
	var isNullable string
	if err := r.db.QueryRow(q, tableName, columnName).Scan(&isNullable); err != nil {
		return false, err
	}
	return strings.EqualFold(isNullable, "YES"), nil
}

// physicalColumnSpec captures the attributes of an existing physical column
// that MODIFY COLUMN would silently drop if omitted from the new spec
// (MySQL/TiDB MODIFY replaces the entire column definition).
//
// Sourced from INFORMATION_SCHEMA.COLUMNS, so it reflects the live DB — never
// the logical schema spec (which does not carry COLLATE / COMMENT / ON UPDATE).
type physicalColumnSpec struct {
	ColumnType    string // e.g. "varchar(255)", "longtext", "int(11)"
	IsNullable    bool
	ColumnDefault *string // NULL default is a valid state distinct from "no default"
	Extra         string  // e.g. "on update CURRENT_TIMESTAMP", "auto_increment"
	Comment       string
	CollationName *string // NULL for non-string types
}

// getPhysicalColumnSpec reads the full attribute set of an existing physical
// column so a nullability relax ALTER can rebuild MODIFY COLUMN without
// dropping DEFAULT / COLLATE / COMMENT / ON UPDATE.
func (r *SchemaRepository) getPhysicalColumnSpec(tableName, columnName string) (*physicalColumnSpec, error) {
	const q = `
		SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND COLUMN_NAME = ?
	`
	var (
		spec       physicalColumnSpec
		isNullable string
		colDefault sql.NullString
		extra      sql.NullString
		comment    sql.NullString
		collation  sql.NullString
	)
	if err := r.db.QueryRow(q, tableName, columnName).Scan(
		&spec.ColumnType, &isNullable, &colDefault, &extra, &comment, &collation,
	); err != nil {
		return nil, err
	}
	spec.IsNullable = strings.EqualFold(isNullable, "YES")
	if colDefault.Valid {
		v := colDefault.String
		spec.ColumnDefault = &v
	}
	if extra.Valid {
		spec.Extra = extra.String
	}
	if comment.Valid {
		spec.Comment = comment.String
	}
	if collation.Valid {
		v := collation.String
		spec.CollationName = &v
	}
	return &spec, nil
}

// buildRelaxNullabilityDDL rebuilds a MODIFY COLUMN clause that preserves every
// attribute of the physical column (type, default, collate, comment, extra)
// and only flips NOT NULL -> NULL.
//
// MySQL/TiDB MODIFY COLUMN replaces the full column spec: anything omitted is
// dropped. So we must restate each attribute the physical column carries.
//
// Ordering follows MySQL column-definition syntax:
//
//	<type> [COLLATE] [NULL|NOT NULL] [DEFAULT] [ON UPDATE] [AUTO_INCREMENT] [COMMENT]
func buildRelaxNullabilityDDL(tableName, columnName string, spec *physicalColumnSpec) string {
	var sb strings.Builder
	fmt.Fprintf(&sb, "ALTER TABLE `%s` MODIFY COLUMN `%s` %s", tableName, columnName, spec.ColumnType)

	if spec.CollationName != nil && *spec.CollationName != "" {
		fmt.Fprintf(&sb, " COLLATE %s", *spec.CollationName)
	}

	// The relax path: always emit NULL regardless of physical IS_NULLABLE.
	sb.WriteString(" NULL")

	if spec.ColumnDefault != nil {
		// INFORMATION_SCHEMA returns the default as a bare literal / expression
		// (e.g. "pending", "CURRENT_TIMESTAMP", "0"). Reuse the CURRENT_TIMESTAMP
		// / NULL bareword convention; other values are string-quoted.
		def := *spec.ColumnDefault
		upper := strings.ToUpper(strings.TrimSpace(def))
		if upper == "CURRENT_TIMESTAMP" || strings.HasPrefix(upper, "CURRENT_TIMESTAMP(") || upper == "NULL" {
			fmt.Fprintf(&sb, " DEFAULT %s", def)
		} else {
			fmt.Fprintf(&sb, " DEFAULT '%s'", strings.ReplaceAll(def, "'", "''"))
		}
	}

	extraLower := strings.ToLower(strings.TrimSpace(spec.Extra))
	// EXTRA may contain "on update CURRENT_TIMESTAMP", "auto_increment", etc.
	// Preserve verbatim; MySQL accepts its own EXTRA output as valid DDL input.
	if extraLower != "" {
		fmt.Fprintf(&sb, " %s", spec.Extra)
	}

	if spec.Comment != "" {
		fmt.Fprintf(&sb, " COMMENT '%s'", strings.ReplaceAll(spec.Comment, "'", "''"))
	}

	return sb.String()
}

// reconcileNullability detects nullability drift on an existing physical column
// and relaxes NOT NULL -> NULL when the spec declares the column nullable.
//
// Relax path (spec nullable, physical NOT NULL): emits
//   ALTER TABLE <t> MODIFY <col> <type> NULL
// This is safe: widening nullability never rejects existing data.
//
// Tighten path (spec NOT NULL, physical nullable): logged as WARN only.
// Auto-tightening is unsafe because existing NULL rows would fail the new
// constraint; the admin must resolve the data drift explicitly.
func (r *SchemaRepository) reconcileNullability(tableName string, col schema.ColumnDefinition) error {
	// Read the full physical spec in one query — we need nullability AND the
	// other attributes (DEFAULT / COLLATE / COMMENT / EXTRA) to rebuild a
	// MODIFY COLUMN clause that does not silently drop them (issue #1918).
	phys, err := r.getPhysicalColumnSpec(tableName, col.Name)
	if err != nil {
		return fmt.Errorf("failed to read physical column spec for %s.%s: %w", tableName, col.Name, err)
	}
	physicalNullable := phys.IsNullable

	switch {
	case col.Nullable && !physicalNullable:
		// Spec nullable, DB NOT NULL -> relax. Rebuild the full column spec
		// from INFORMATION_SCHEMA so MODIFY COLUMN preserves every attribute
		// (type, default, collate, comment, extra) and only flips NULL.
		ddl := buildRelaxNullabilityDDL(tableName, col.Name, phys)
		log.Printf("   🔓 Relaxing nullability (spec NULL, DB NOT NULL) for %s.%s: %s", tableName, col.Name, ddl)
		if _, err := r.db.Exec(ddl); err != nil {
			return fmt.Errorf("failed to relax nullability for %s.%s: %w", tableName, col.Name, err)
		}
		log.Printf("   ✅ Nullability relaxed: %s.%s is now NULL-able", tableName, col.Name)
	case !col.Nullable && physicalNullable:
		// Spec NOT NULL, DB nullable -> WARN only. Auto-tightening is unsafe.
		log.Printf("   ⚠️  Nullability drift: spec says %s.%s is NOT NULL but physical column is nullable. "+
			"Auto-tightening is unsafe (existing NULL rows would fail the constraint). "+
			"Admin must resolve via data cleanup + explicit MODIFY.",
			tableName, col.Name)
	}
	return nil
}

// getColumnType retrieves the actual column type from the database
func (r *SchemaRepository) getColumnType(tableName, columnName string) (string, error) {
	query := `
		SELECT COLUMN_TYPE 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_SCHEMA = DATABASE() 
		  AND TABLE_NAME = ? 
		  AND COLUMN_NAME = ?
	`
	var columnType string
	err := r.db.QueryRow(query, tableName, columnName).Scan(&columnType)
	if err != nil {
		return "", err
	}
	return strings.ToUpper(columnType), nil
}

// isTypeCompatible checks if existing DB type is compatible with expected type.
// This prevents unnecessary MODIFY operations for compatible types.
// Returns true if existing type can safely hold data of expected type.
func (r *SchemaRepository) isTypeCompatible(existing, expected string) bool {
	existing = strings.ToUpper(existing)
	expected = strings.ToUpper(expected)

	// Exact match (fastest path)
	if existing == expected {
		return true
	}

	// Extract base types: "DECIMAL(18,6)" -> "DECIMAL", "VARCHAR(255)" -> "VARCHAR"
	// Use package-level extractBaseType from schema_types.go
	existingBase := extractBaseType(existing)
	expectedBase := extractBaseType(expected)

	// Numeric types:
	// We require strict base type matching to avoid precision loss or overflow.
	// e.g. INT != DECIMAL, FLOAT != DECIMAL
	// Fall through to strict equality check at the end.

	// Text types: TEXT, MEDIUMTEXT, LONGTEXT are compatible with each other
	textTypes := map[string]bool{
		"TEXT": true, "MEDIUMTEXT": true, "LONGTEXT": true,
	}
	if textTypes[expectedBase] {
		return textTypes[existingBase]
	}

	// VARCHAR: exact base match (VARCHAR == VARCHAR)
	if existingBase == "VARCHAR" && expectedBase == "VARCHAR" {
		return true
	}

	// Default: require exact base type match
	return existingBase == expectedBase
}

// BatchAddColumns adds multiple columns and registers their metadata (Strict/Optimized)
func (r *SchemaRepository) BatchAddColumns(tableName string, fields []models.FieldMetadata) error {
	log.Printf("➕➕ Batch adding %d columns to table %s", len(fields), tableName)

	if len(fields) == 0 {
		return nil
	}

	// VALIDATION: Table Name
	if !constants.IsSystemTable(tableName) {
		if !columnNameRegex.MatchString(tableName) {
			return apperrors.NewValidationError("tableName", fmt.Sprintf("invalid table name '%s': must be alphanumeric (PascalCase/snake_case)", tableName))
		}
	}

	// VALIDATION: Column Names (Fail Fast)
	for _, field := range fields {
		if !columnNameRegex.MatchString(field.APIName) {
			return apperrors.NewValidationError("columnName", fmt.Sprintf("invalid column name '%s': must be alphanumeric (PascalCase/snake_case)", field.APIName))
		}
	}

	// Resolve Object ID ONCE for metadata registration
	var objectID string
	qr := query.From(constants.TableObject).
		Select([]string{constants.FieldID}).
		Where(constants.FieldSysObject_APIName, tableName).
		Limit(1).
		Build()

	if err := r.db.QueryRow(qr.SQL, qr.Params...).Scan(&objectID); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to resolve object ID for table %s", tableName), err)
	}

	var newCols []schema.ColumnDefinition
	var colsToModify []schema.ColumnDefinition
	var fkDDLs []string
	var uniqueClauses []string // Global scope for batch
	var fieldsToRegister []schema.FieldWithContext

	// 1. Process Fields
	for _, field := range fields {
		// Validating metadata structure implicitly via Service, but good to be safe?
		// Repo usually assumes valid domain objects from Service.

		// Map to ColumnDefinition for DDL
		col := r.mapFieldToColumnDef(field)

		// Check existence
		exists, err := r.checkColumnExists(tableName, col.Name)
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("failed to check column %s", col.Name), err)
		}

		// Prepare for Metadata Registration (Always register/update metadata for consistency)
		fieldID := schema.GenerateFieldID(tableName, field.APIName)
		f := field // copy

		// Ensure PhysicalName is always populated (Explicit Mapping)
		f.PhysicalName = col.Name

		fieldsToRegister = append(fieldsToRegister, schema.FieldWithContext{
			Field:            &f,
			ColumnDefinition: col,
			ObjectID:         objectID,
			FieldID:          fieldID,
		})

		if exists {
			// Check Type Mismatch
			// We only modify if types are incompatible (e.g. INT -> VARCHAR) AND not identical.
			existingType, err := r.getColumnType(tableName, col.Name)
			if err == nil && !r.isTypeCompatible(existingType, col.Type) {
				log.Printf("🔄 Type mismatch for %s.%s: %s vs %s. Queueing MODIFY.", tableName, col.Name, existingType, col.Type)
				colsToModify = append(colsToModify, col)
			} else {
				if err != nil {
					log.Printf("⚠️ Could not determine existing type for %s.%s: %v", tableName, col.Name, err)
				}
				log.Printf("⚠️ Column %s.%s already exists (compatible). Skipping DDL, will ensure metadata.", tableName, col.Name)
			}

			// Ensure Unique Constraint checks (Idempotent fix)
			if col.Unique {
				idxName := schema.GenerateUniqueIndexName(tableName, col.Name)
				uniqueClauses = append(uniqueClauses, fmt.Sprintf("ADD UNIQUE KEY `%s` (`%s`)", idxName, col.Name))
			}
			continue
		}

		newCols = append(newCols, col)
	}

	// 2. Build Bulk DDL
	var ddlClauses []string

	if len(newCols) > 0 {
		for _, col := range newCols {
			// Fix: Generate safe column name (max 64 chars) using helper
			col.Name = schema.GenerateSafeIdentifier(col.Name, "col")

			colDDL := r.buildColumnDDL(col, false)
			ddlClauses = append(ddlClauses, fmt.Sprintf("ADD COLUMN %s", colDDL))

			if col.Unique {
				idxName := schema.GenerateUniqueIndexName(tableName, col.Name)
				uniqueClauses = append(uniqueClauses, fmt.Sprintf("ADD UNIQUE KEY `%s` (`%s`)", idxName, col.Name))
			}

			// Collect FKs
			if len(col.ReferenceTo) == 1 && !strings.EqualFold(col.ReferenceTo[0], "parent") {
				// Fix: Generate safe FK name using helper
				rawFKName := fmt.Sprintf("fk_%s_%s", tableName, col.Name)
				fkName := schema.GenerateSafeIdentifier(rawFKName, "fk")

				targetPK := constants.ColID

				fkDDL := fmt.Sprintf("ALTER TABLE `%s` ADD CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s` (`%s`)",
					tableName, fkName, col.Name, col.ReferenceTo[0], targetPK)

				if col.OnDelete != "" {
					fkDDL += fmt.Sprintf(" ON DELETE %s", col.OnDelete)
				}
				fkDDLs = append(fkDDLs, fkDDL)
			}
		}
	}

	// Process Modifications
	if len(colsToModify) > 0 {
		for _, col := range colsToModify {
			// Fix: Generate safe column name
			col.Name = schema.GenerateSafeIdentifier(col.Name, "col")

			colDDL := r.buildColumnDDL(col, false)
			ddlClauses = append(ddlClauses, fmt.Sprintf("MODIFY COLUMN %s", colDDL))

			// Unique constraints are handled via uniqueClauses (global)
			// modifyClauses only needs to handle the type change
		}
	}

	if len(ddlClauses) > 0 {
		// Execute Column Additions/Modifications
		fullDDL := fmt.Sprintf("ALTER TABLE `%s` %s", tableName, strings.Join(ddlClauses, ", "))
		log.Printf("   🏁 Executing Batch DDL: %s", fullDDL)

		if _, err := r.db.Exec(fullDDL); err != nil {
			return apperrors.NewInternalError("batch DDL failed", err)
		}
		log.Printf("   ✅ Batch Columns Processed")
	}

	// 3. Execute Unique Keys (Separate Statements, includes keys for existing columns if needed)
	if len(uniqueClauses) > 0 {
		for _, clause := range uniqueClauses {
			stmt := fmt.Sprintf("ALTER TABLE `%s` %s", tableName, clause)
			log.Printf("   🏁 Executing Unique Key DDL: %s", stmt)
			if _, err := r.db.Exec(stmt); err != nil {
				if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
					log.Printf("   ⚠️  Unique Index already exists, skipping.")
				} else {
					return apperrors.NewInternalError(fmt.Sprintf("unique DDL failed (%s)", stmt), err)
				}
			}
		}
	}

	// 3. Execute FK DDLs
	var fkErrors []string
	for _, ddl := range fkDDLs {
		log.Printf("   🔗 Adding Foreign Key constraint...")
		if _, err := r.db.Exec(ddl); err != nil {
			// Skip duplicate FK (idempotent) - these are expected in resume scenarios
			if strings.Contains(err.Error(), "Duplicate") || strings.Contains(err.Error(), "already exists") {
				log.Printf("   ⚠️ FK already exists, skipping")
				continue
			}
			log.Printf("❌ FK creation failed: %v", err)
			fkErrors = append(fkErrors, err.Error())
		}
	}
	if len(fkErrors) > 0 {
		return apperrors.NewInternalError(fmt.Sprintf("FK creation failed: %v", fkErrors), fmt.Errorf("foreign key errors: %s", strings.Join(fkErrors, "; ")))
	}

	// 4. Register Metadata (Optimized Batch Insert)
	if err := r.BatchSaveFieldMetadata(context.Background(), fieldsToRegister, r.db); err != nil {
		return apperrors.NewInternalError("failed to batch register field metadata", err)
	}

	return nil
}

// mapFieldToColumnDef converts FieldMetadata to ColumnDefinition for DDL generation
func (r *SchemaRepository) mapFieldToColumnDef(field models.FieldMetadata) schema.ColumnDefinition {
	// Logic mirrors BatchCreateFields service logic but centralized here
	var relationshipName string
	if field.RelationshipName != nil {
		relationshipName = *field.RelationshipName
	}

	// Target physical name is identical to API name under Salesforce Native model
	physicalName := field.APIName

	colDef := schema.ColumnDefinition{
		Name:             physicalName,
		Label:            field.Label,
		Type:             r.MapFieldTypeToSQL(string(field.Type)),
		LogicalType:      string(field.Type),
		Nullable:         !field.Required,
		Unique:           field.IsUnique,
		IsMasterDetail:   field.IsMasterDetail,
		RelationshipName: relationshipName,
	}

	if field.DefaultValue != nil {
		// SECURITY: Escape single quotes to prevent SQL injection in DEFAULT value (e.g. O'Reilly -> O''Reilly)
		escapedVal := strings.ReplaceAll(*field.DefaultValue, "'", "''")
		colDef.Default = "'" + escapedVal + "'"
	}
	colDef.ReferenceTo = field.ReferenceTo
	if field.Formula != nil {
		colDef.Formula = *field.Formula
	}
	if field.ReturnType != nil {
		colDef.ReturnType = string(*field.ReturnType)
	}

	// Delete Rule
	if field.DeleteRule != nil {
		if *field.DeleteRule == constants.DeleteRuleCascade {
			colDef.OnDelete = "CASCADE"
		} else if *field.DeleteRule == constants.DeleteRuleSetNull {
			colDef.OnDelete = "SET NULL"
		} else {
			colDef.OnDelete = "RESTRICT"
		}
	}

	if len(field.Options) > 0 {
		colDef.Options = field.Options
	}

	return colDef
}

// camelCaseSplitRe matches a lowercase letter followed by an uppercase letter.
var camelCaseSplitRe = regexp.MustCompile(`([a-z])([A-Z])`)

// splitCamelCase inserts a space before each uppercase letter that follows a lowercase letter.
// e.g. "FirstName" -> "First Name", "SystemModstamp" -> "System Modstamp"
func splitCamelCase(s string) string {
	return camelCaseSplitRe.ReplaceAllString(s, "$1 $2")
}