package bootstrap

import (
	"database/sql"
	"fmt"
	"log"
	"regexp"
	"strings"
	"sync"
	"time"

	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/fieldcapabilities"
	"github.com/nexuscrm/shared/pkg/models"
)

// DiscoverAndRegisterPhysicalTables scans the DB for physical tables that exist
// but are not yet registered in _System_Object. This handles the case where
// business tables (account, order, etc.) were imported via data migration
// and need to be registered as metadata objects for the CRM to manage them.
func DiscoverAndRegisterPhysicalTables(dbInstance *database.TiDBConnection) error {
	rawDB := dbInstance.DB()

	// Field-metadata overrides must run on every startup, not only when new
	// tables are discovered. The early returns for "all tables already
	// registered" and "no missing tables" previously short-circuited past the
	// reconcile, leaving steady-state environments stuck with stale values.
	// Defer makes it the last step of every success path; the overrides are
	// idempotent so running on no-op paths is safe.
	//
	// `reconcileOK` defaults to false so any error path — including ones
	// added later — is fail-safe: the UPDATEs only fire after the caller has
	// committed to a clean `return nil`. This covers not just DB-read
	// failures but partial-write failures in registerMissingTables etc.,
	// where running 28 UPDATEs against half-populated _System_Object rows
	// would join on missing parents and produce spurious log noise.
	reconcileOK := false
	defer func() {
		if reconcileOK {
			reconcileDiscoveredFieldOverrides(rawDB)
		}
	}()

	// Step 1: Get all physical tables in the DB
	physicalTables, err := getPhysicalTables(rawDB)
	if err != nil {
		return fmt.Errorf("failed to get physical tables: %w", err)
	}

	// Step 2: Get all already-registered objects in _System_Object
	registeredObjects, err := getRegisteredObjects(rawDB)
	if err != nil {
		return fmt.Errorf("failed to get registered objects: %w", err)
	}

	// Step 3: Find missing tables (physical tables not in _System_Object)
	registeredLower := make(map[string]bool)
	for _, name := range registeredObjects {
		registeredLower[strings.ToLower(name)] = true
	}

	var missing []string
	for _, table := range physicalTables {
		lower := strings.ToLower(table)
		// Skip system tables, internal state tables, data-pipeline checkpoints, and already registered.
		//   _system_       — core CRM metadata tables
		//   _fivetran_     — Fivetran sync state
		//   _dev_          — dev session and audit tables (internal runtime state)
		//   _bq_           — BigQuery sync checkpoints (internal pipeline state)
		//   v_             — views, not physical tables
		if strings.HasPrefix(lower, "_system_") ||
			strings.HasPrefix(lower, "_fivetran_") ||
			strings.HasPrefix(lower, "_dev_") ||
			strings.HasPrefix(lower, "_bq_") ||
			strings.HasPrefix(lower, "v_") ||
			registeredLower[lower] {
			continue
		}
		missing = append(missing, table)
	}

	// Also check for objects that are registered but have 0 fields.
	// Use a single aggregate query instead of per-table COUNT(*) (~355 → 1 SQL call).
	var needFields []string
	zeroFieldRows, err := rawDB.Query(
		fmt.Sprintf(
			"SELECT o.`%s` FROM `%s` o LEFT JOIN `%s` f ON o.Id = f.ObjectId "+
				"WHERE o.`%s` NOT LIKE '_System_%%' AND o.`%s` NOT LIKE '_fivetran_%%' AND o.`%s` NOT LIKE 'v_%%' "+
				"GROUP BY o.`%s`, o.Id HAVING COUNT(f.Id) = 0",
			constants.FieldAPIName, constants.TableObject, constants.TableField,
			constants.FieldAPIName, constants.FieldAPIName, constants.FieldAPIName,
			constants.FieldAPIName,
		),
	)
	if err != nil {
		log.Printf("   ⚠️  Failed to find zero-field objects: %v", err)
	} else {
		defer zeroFieldRows.Close()
		for zeroFieldRows.Next() {
			var name string
			if zeroFieldRows.Scan(&name) == nil {
				needFields = append(needFields, name)
			}
		}
		if err := zeroFieldRows.Err(); err != nil {
			log.Printf("   ⚠️  Error iterating zero-field objects: %v", err)
		}
	}

	if len(missing) == 0 && len(needFields) == 0 {
		log.Println("✅ All physical tables are already registered in _System_Object")
		reconcileOK = true
		return nil
	}

	if len(needFields) > 0 {
		log.Printf("🔍 Found %d registered objects with 0 fields, registering fields from physical schema...", len(needFields))
		if err := registerFieldsForDiscoveredTables(rawDB, needFields); err != nil {
			log.Printf("   ⚠️  Failed to register fields: %v", err)
		}
	}

	if len(missing) == 0 {
		reconcileOK = true
		return nil
	}

	log.Printf("🔍 Discovered %d unregistered physical tables, registering...", len(missing))

	// Step 4: Register missing tables
	// Use a single batch INSERT IGNORE to avoid conflicts with Label UNIQUE constraint
	err = registerMissingTables(rawDB, missing, registeredObjects)
	if err != nil {
		return fmt.Errorf("failed to register missing tables: %w", err)
	}

	log.Printf("✅ Registered %d physical tables in _System_Object", len(missing))

	// Step 5: Reconcile orphaned _System_Field records
	// Business objects imported before bootstrap may have _System_Field records
	// pointing to old ObjectIds that no longer exist in _System_Object.
	if err := reconcileOrphanedFields(rawDB); err != nil {
		log.Printf("⚠️  Failed to reconcile orphaned fields: %v", err)
	}

	// Step 6: Clean up known stale fields (e.g. EffectiveDate on Contract is superseded by StartDate)
	cleanupStaleFields(rawDB)

	// Step 7: Correct field metadata overrides — handled by the deferred
	// reconcileDiscoveredFieldOverrides call at the top of this function so
	// steady-state environments also converge on the desired state.

	reconcileOK = true
	return nil
}

func getPhysicalTables(db *sql.DB) ([]string, error) {
	// Use SHOW TABLES instead of INFORMATION_SCHEMA to avoid stale schema cache
	// after bootstrap DDL operations. SHOW TABLES always reflects the current state.
	rows, err := db.Query("SHOW TABLES")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		tables = append(tables, name)
	}
	return tables, rows.Err()
}

func getRegisteredObjects(db *sql.DB) ([]string, error) {
	rows, err := db.Query(
		fmt.Sprintf("SELECT `%s` FROM `%s`", constants.FieldAPIName, constants.TableObject),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var names []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		names = append(names, name)
	}
	return names, rows.Err()
}

func registerMissingTables(db *sql.DB, missing []string, existingObjects []string) error {
	// Build a set of existing Labels (case-insensitive) to avoid UNIQUE constraint violations
	existingLabels := make(map[string]bool)
	rows, err := db.Query(
		fmt.Sprintf("SELECT `%s` FROM `%s`", "Label", constants.TableObject),
	)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var label string
		if err := rows.Scan(&label); err != nil {
			return err
		}
		existingLabels[strings.ToLower(label)] = true
	}
	if err := rows.Err(); err != nil {
		return err
	}

	// Build INSERT statements
	var valueParts []string
	var params []interface{}

	for _, table := range missing {
		label := tableNameToLabel(table)
		// If label would conflict, append " (Data)" suffix
		if existingLabels[strings.ToLower(label)] {
			label = label + " (Data)"
		}
		// Double-check after suffix
		if existingLabels[strings.ToLower(label)] {
			label = label + " " + table
		}

		plural := pluralize(label)
		isCustom := strings.HasSuffix(strings.ToLower(table), "__c")
		customInt := 0
		if isCustom {
			customInt = 1
		}

		// Use the canonical (lowercase-normalized) ID scheme so all write paths —
		// discovery, pack apply, admin UI — converge on the same Id for the
		// same logical object. Previously discovery used `disc_<table>` which
		// collided with pack apply's `obj_<ApiName>_<hash>` on the
		// case-insensitive UNIQUE(ApiName) and produced orphan field rows.
		id := schema.GenerateObjectID(table)
		valueParts = append(valueParts, "(?, ?, 'custom_object', ?, ?, ?, ?, 1, 'Private', 0, NOW(), NOW())")
		params = append(params, id, table, label, plural, table, customInt)

		existingLabels[strings.ToLower(label)] = true // Track for next iteration
	}

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

	// Table name is interpolated via Sprintf because SQL parameters (?) cannot substitute identifiers.
	// constants.TableObject is a compile-time constant — no injection risk.
	sql := fmt.Sprintf(
		"INSERT IGNORE INTO `%s` (`Id`, `ApiName`, `TableType`, `Label`, `PluralLabel`, `Description`, `IsCustom`, `Searchable`, `SharingModel`, `IsDeleted`, `CreatedDate`, `LastModifiedDate`) VALUES %s",
		constants.TableObject,
		strings.Join(valueParts, ", "),
	)

	result, err := db.Exec(sql, params...)
	if err != nil {
		return fmt.Errorf("batch insert failed: %w", err)
	}

	affected, _ := result.RowsAffected()
	log.Printf("   📦 Inserted %d/%d tables (some may have been skipped due to conflicts)", affected, len(missing))

	// Step 5: Also register fields for the newly discovered tables
	// by scanning INFORMATION_SCHEMA.COLUMNS
	if err := registerFieldsForDiscoveredTables(db, missing); err != nil {
		log.Printf("   ⚠️  Failed to register fields for discovered tables: %v", err)
		// Non-fatal: objects will show with 0 fields, but at least they'll be visible
	}

	return nil
}

func registerFieldsForDiscoveredTables(db *sql.DB, tables []string) error {
	// Get ObjectId for each table
	for _, table := range tables {
		var objectId string
		err := db.QueryRow(
			fmt.Sprintf("SELECT `Id` FROM `%s` WHERE `%s` = ?", constants.TableObject, constants.FieldAPIName),
			table,
		).Scan(&objectId)
		if err != nil {
			log.Printf("   ⚠️  Could not find ObjectId for table %s: %v", table, err)
			continue
		}

		// Get columns from INFORMATION_SCHEMA
		colRows, err := db.Query(
			"SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION",
			table,
		)
		if err != nil {
			log.Printf("   ⚠️  Could not get columns for table %s: %v", table, err)
			continue
		}

		var fieldValues []string
		var fieldParams []interface{}

		for colRows.Next() {
			var colName, dataType, isNullable string
			var colDefault, colKey sql.NullString
			if err := colRows.Scan(&colName, &dataType, &isNullable, &colDefault, &colKey); err != nil {
				continue
			}

			fieldId := fmt.Sprintf("fld_%s_%s", strings.ReplaceAll(table, "__", "_"), strings.ToLower(colName))
			fieldType := sqlTypeToFieldType(dataType)
			required := isNullable == "NO" && colName != "Id"
			requiredInt := 0
			if required {
				requiredInt = 1
			}

			fieldLabel := fieldNameToLabel(colName)
			fieldValues = append(fieldValues, "(?, ?, ?, ?, ?, ?, 0)")
			fieldParams = append(fieldParams, fieldId, objectId, colName, fieldLabel, fieldType, requiredInt)
		}
		colRows.Close()

		if len(fieldValues) == 0 {
			continue
		}

		insertSQL := fmt.Sprintf(
			"INSERT IGNORE INTO `%s` (`Id`, `ObjectId`, `ApiName`, `Label`, `Type`, `Required`, `IsDeleted`) VALUES %s",
			constants.TableField,
			strings.Join(fieldValues, ", "),
		)

		result, err := db.Exec(insertSQL, fieldParams...)
		if err != nil {
			log.Printf("   ⚠️  Failed to insert fields for %s: %v", table, err)
			continue
		}
		affected, _ := result.RowsAffected()
		if affected > 0 {
			log.Printf("   📋 Registered %d fields for %s", affected, table)
		}
	}

	return nil
}

// fieldNameToLabel converts a field API name to a human-readable label
// e.g. "ApprovedPrice__c" -> "Approved Price", "AccountId" -> "Account"
func fieldNameToLabel(fieldName string) string {
	name := fieldName
	if strings.HasSuffix(name, "__c") {
		name = strings.TrimSuffix(name, "__c")
	}

	// Strip trailing "Id" for reference-style fields (e.g. "AccountId" -> "Account").
	// Only for field names, not table names. Safe: "Valid"/"Rapid" end in lowercase "id".
	if len(name) > 4 && strings.HasSuffix(name, "Id") {
		name = strings.TrimSuffix(name, "Id")
	}

	name = splitCamelCase(name)
	name = strings.ReplaceAll(name, "_", " ")
	parts := strings.Fields(name)
	for i, part := range parts {
		if len(part) > 0 {
			parts[i] = strings.ToUpper(part[:1]) + part[1:]
		}
	}
	return strings.Join(parts, " ")
}

// tableNameToLabel converts a table name to a human-readable label
// e.g. "account" -> "Account", "quote_product__c" -> "Quote Product"
func tableNameToLabel(tableName string) string {
	// Remove __c suffix for custom objects
	name := tableName
	if strings.HasSuffix(name, "__c") {
		name = strings.TrimSuffix(name, "__c")
	}

	// NO "Id" stripping for table names — only fieldNameToLabel does that

	// Apply camelCase splitting (e.g. "FirstName" -> "First Name")
	name = splitCamelCase(name)

	// Split by underscore and capitalize each word
	parts := strings.Split(name, "_")
	for i, part := range parts {
		if len(part) > 0 {
			parts[i] = strings.ToUpper(part[:1]) + part[1:]
		}
	}

	return strings.Join(parts, " ")
}

// reconcileOrphanedFields finds _System_Field records whose ObjectId doesn't match
// any current _System_Object.Id, and re-links them based on the ApiName stored
// in the old _System_Object entry (which may still exist with a different Id due to
// bootstrap UPSERT).
func reconcileOrphanedFields(db *sql.DB) error {
	// Find orphan ObjectIds in _System_Field that don't match any _System_Object.Id
	// For each orphan, try to find the correct _System_Object by matching the table name
	query := fmt.Sprintf(`
		SELECT DISTINCT f.ObjectId
		FROM %s f
		LEFT JOIN %s o ON f.ObjectId = o.Id
		WHERE o.Id IS NULL
	`, "`"+constants.TableField+"`", "`"+constants.TableObject+"`")

	rows, err := db.Query(query)
	if err != nil {
		return fmt.Errorf("failed to find orphan ObjectIds: %w", err)
	}
	defer rows.Close()

	var orphanIds []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			continue
		}
		orphanIds = append(orphanIds, id)
	}
	if err := rows.Err(); err != nil {
		return err
	}

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

	log.Printf("🔗 Found %d orphaned ObjectIds in _System_Field, attempting to reconcile...", len(orphanIds))

	// For each orphan ObjectId, check if there's an _System_Object with matching ApiName
	// We need to find the ApiName associated with the orphan ObjectId by looking at
	// what table the fields belong to (check physical columns)
	reconciled := 0
	for _, orphanId := range orphanIds {
		// Get a sample field from this orphan group to identify the table
		var sampleApiName string
		err := db.QueryRow(
			fmt.Sprintf("SELECT `%s` FROM `%s` WHERE ObjectId = ? LIMIT 1", constants.FieldAPIName, constants.TableField),
			orphanId,
		).Scan(&sampleApiName)
		if err != nil {
			continue
		}

		// Try to find the table this field belongs to by checking INFORMATION_SCHEMA
		tableRows, err := db.Query(
			"SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND COLUMN_NAME = ? AND TABLE_NAME NOT LIKE '_System_%'",
			sampleApiName,
		)
		if err != nil {
			continue
		}

		var candidateTables []string
		for tableRows.Next() {
			var tName string
			if err := tableRows.Scan(&tName); err == nil {
				candidateTables = append(candidateTables, tName)
			}
		}
		tableRows.Close()

		// For each candidate, check if there's a matching _System_Object
		for _, candidate := range candidateTables {
			var newObjectId string
			err := db.QueryRow(
				fmt.Sprintf("SELECT `Id` FROM `%s` WHERE LOWER(`%s`) = LOWER(?)", constants.TableObject, constants.FieldAPIName),
				candidate,
			).Scan(&newObjectId)
			if err != nil {
				continue
			}

			// Found a match! Update all fields from orphanId to newObjectId
			result, err := db.Exec(
				fmt.Sprintf("UPDATE `%s` SET ObjectId = ? WHERE ObjectId = ?", constants.TableField),
				newObjectId, orphanId,
			)
			if err != nil {
				log.Printf("   ⚠️  Failed to update ObjectId %s → %s: %v", orphanId, newObjectId, err)
				continue
			}
			affected, _ := result.RowsAffected()
			if affected > 0 {
				log.Printf("   🔗 Reconciled %d fields: %s → %s (%s)", affected, orphanId, newObjectId, candidate)
				reconciled++
			}
			break // Found the match, stop looking
		}
	}

	if reconciled > 0 {
		log.Printf("✅ Reconciled %d orphaned field groups", reconciled)
	} else if len(orphanIds) > 0 {
		log.Printf("⚠️  Could not reconcile %d orphaned ObjectIds (fields may not match any physical table)", len(orphanIds))
	}

	return nil
}

// sqlTypeToFieldType maps SQL data types to CRM field types
func sqlTypeToFieldType(sqlType string) string {
	switch strings.ToUpper(sqlType) {
	case "VARCHAR", "CHAR", "TEXT", "MEDIUMTEXT", "LONGTEXT", "TINYTEXT":
		return "Text"
	case "INT", "BIGINT", "SMALLINT", "TINYINT", "MEDIUMINT":
		return "Number"
	case "DECIMAL", "DOUBLE", "FLOAT", "NUMERIC":
		return "Currency"
	case "DATETIME", "TIMESTAMP":
		return "DateTime"
	case "DATE":
		return "Date"
	case "TINYINT(1)", "BOOLEAN", "BOOL":
		return "Checkbox"
	case "JSON":
		return "TextArea"
	default:
		return "Text"
	}
}

// EnsureStandardIndexes creates standard indexes (IsDeleted, CreatedDate, LastModifiedDate,
// OwnerId) on all non-system physical tables that lack them. Discovered tables from data
// migration have no indexes by default — this fixes full-table-scan performance issues.
// Uses 2 bulk queries (SHOW TABLES + INFORMATION_SCHEMA.STATISTICS) instead of per-table checks.
func EnsureStandardIndexes(dbInstance *database.TiDBConnection) error {
	rawDB := dbInstance.DB()
	start := time.Now()
	log.Println("🔧 Ensuring standard indexes on business tables...")

	// Standard columns that should be indexed on every business table
	standardCols := []string{"IsDeleted", "CreatedDate", "LastModifiedDate", "OwnerId"}

	// Step 1: Get all physical tables
	physicalTables, err := getPhysicalTables(rawDB)
	if err != nil {
		return err
	}

	// Step 2: Bulk-load existing indexes from INFORMATION_SCHEMA
	existingIndexes := make(map[string]map[string]bool) // table → set of indexed columns
	idxRows, err := rawDB.Query(
		"SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE()",
	)
	if err != nil {
		log.Printf("   ⚠️  Cannot query index info: %v", err)
		return err
	}
	defer idxRows.Close()
	for idxRows.Next() {
		var tbl, col string
		if idxRows.Scan(&tbl, &col) == nil {
			if existingIndexes[tbl] == nil {
				existingIndexes[tbl] = make(map[string]bool)
			}
			existingIndexes[tbl][col] = true
		}
	}
	if err := idxRows.Err(); err != nil {
		return err
	}

	// Step 3: Bulk-load existing columns
	existingCols := make(map[string]map[string]bool)
	colRows, err := rawDB.Query(
		"SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()",
	)
	if err != nil {
		return err
	}
	defer colRows.Close()
	for colRows.Next() {
		var tbl, col string
		if colRows.Scan(&tbl, &col) == nil {
			if existingCols[tbl] == nil {
				existingCols[tbl] = make(map[string]bool)
			}
			existingCols[tbl][col] = true
		}
	}
	if err := colRows.Err(); err != nil {
		return err
	}

	// Step 4: Collect all missing indexes
	type indexJob struct {
		table, col, indexName string
	}
	var jobs []indexJob
	for _, table := range physicalTables {
		lower := strings.ToLower(table)
		if strings.HasPrefix(lower, "_system_") ||
			strings.HasPrefix(lower, "_fivetran_") ||
			strings.HasPrefix(lower, "v_") {
			continue
		}

		cols := existingCols[table]
		idxs := existingIndexes[table]
		if cols == nil {
			continue
		}

		for _, col := range standardCols {
			if !cols[col] {
				continue
			}
			if idxs != nil && idxs[col] {
				continue
			}
			jobs = append(jobs, indexJob{
				table:     table,
				col:       col,
				indexName: fmt.Sprintf("idx_%s_%s", strings.ToLower(table), strings.ToLower(col)),
			})
		}
	}

	if len(jobs) == 0 {
		log.Printf("✅ Standard indexes ensured: 0 created (%v)", time.Since(start).Round(time.Millisecond))
		return nil
	}

	log.Printf("   📑 Creating %d indexes (parallel, 8 workers)...", len(jobs))

	// Create indexes in parallel with limited concurrency
	sem := make(chan struct{}, 8) // 8 concurrent DDL operations
	var mu sync.Mutex
	created := 0
	var wg sync.WaitGroup
	for _, job := range jobs {
		wg.Add(1)
		go func(j indexJob) {
			defer wg.Done()
			sem <- struct{}{}        // acquire
			defer func() { <-sem }() // release

			ddl := fmt.Sprintf("CREATE INDEX `%s` ON `%s` (`%s`)", j.indexName, j.table, j.col)
			if _, err := rawDB.Exec(ddl); err != nil {
				if !strings.Contains(err.Error(), "Duplicate") {
					log.Printf("   ⚠️  Failed: %s: %v", j.indexName, err)
				}
			} else {
				mu.Lock()
				created++
				mu.Unlock()
			}
		}(job)
	}
	wg.Wait()

	log.Printf("✅ Standard indexes ensured: %d created (%v)", created, time.Since(start).Round(time.Millisecond))
	return nil
}

// camelCaseRe matches a lowercase letter followed by an uppercase letter.
var camelCaseRe = 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 camelCaseRe.ReplaceAllString(s, "$1 $2")
}

// cleanupStaleFields marks known stale fields as deleted so they no longer
// appear in the UI. For example, Contract.EffectiveDate is superseded by
// StartDate and was annotated "待删除" (to be deleted) in the field label.
func cleanupStaleFields(db *sql.DB) {
	staleFields := []struct{ object, field string }{
		{"Contract", "EffectiveDate"},
	}
	for _, sf := range staleFields {
		result, err := db.Exec(
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = 1 WHERE `%s` IN (SELECT `Id` FROM `%s` WHERE `%s` = ?) AND `%s` = ? AND `%s` = 0",
				constants.TableField, constants.FieldIsDeleted,
				constants.FieldSysField_ObjectID, constants.TableObject, constants.FieldAPIName,
				constants.FieldAPIName,
				constants.FieldIsDeleted,
			),
			sf.object, sf.field,
		)
		if err != nil {
			log.Printf("   ⚠️  Failed to clean up stale field %s.%s: %v", sf.object, sf.field, err)
			continue
		}
		if affected, _ := result.RowsAffected(); affected > 0 {
			log.Printf("   🧹 Marked stale field %s.%s as deleted", sf.object, sf.field)
		}
	}
}

// reconcileDiscoveredFieldOverrides corrects field metadata where the
// discover path (or, for a handful of system_tables.json rows, the generic
// `Required = !col.Nullable && !isSystem` derivation in
// schema_field_metadata_builder.go) produced values that conflict with the
// intended Salesforce API parity.
//
// Each override is an idempotent UPDATE: re-applying on a row that already
// matches the target state is a no-op (0 rows affected). Missing rows
// (object or field absent) are silently skipped. Safe to run on every startup.
func reconcileDiscoveredFieldOverrides(db *sql.DB) {
	type fieldOverride struct {
		object    string
		field     string
		setClause string
		// When true, the UPDATE additionally guards with `f.IsCustomized = 0
		// OR f.IsCustomized IS NULL` so an admin who changed the value via
		// the field-edit UI (which sets `IsCustomized=1`) is not silently
		// overwritten on every startup.
		//
		// Required=1 "authority" entries (SF parity) deliberately do NOT
		// enable the guard — they are the source of truth even if an admin
		// toggled the flag off. Required=0 "inference-correction" entries
		// DO enable the guard — the override only exists because the
		// builder derived the wrong initial value, so explicit admin intent
		// should win.
		preserveAdminCustomization bool
	}

	overrides := []fieldOverride{
		// Issue #300: Campaign.Name label/required fix.
		// Note: IsSystem=0 override removed — IsSystem now only means "standard field" (custom=false),
		// it no longer affects editability. Createable/Updateable are the source of truth.
		{object: "Campaign", field: "Name", setClause: "f.Required = 1, f.Label = 'Campaign Name'"},
		{object: "Campaign", field: "OwnerId", setClause: "f.Label = 'Campaign Owner'"},
		// CampaignMember.Status behaves like a campaign-scoped runtime picklist in Salesforce.
		// Discovered-object metadata does not infer that contract, so the describe response must
		// be repaired here to point Status at CampaignMemberStatus rows filtered by CampaignId.
		{object: "CampaignMember", field: "Status", setClause: "f.Validator = 'runtime_scoped_picklist', f.ControllingField = 'CampaignId', f.ReferenceTo = '[\"CampaignMemberStatus\"]'"},
		// Issue #549: Lead required-field parity with Salesforce standard Lead.
		// Discover-path seeded these as Required=0; describe then emits nillable=true and the
		// form omits required markers + client validation, letting empty Leads save.
		// Both `LastName` (Salesforce standard) and `Name` (discovered compound-column
		// variant on some environments) are covered so whichever field the default layout
		// references picks up the required marker. Missing rows are no-ops.
		{object: "Lead", field: "LastName", setClause: "f.Required = 1"},
		{object: "Lead", field: "Name", setClause: "f.Required = 1"},
		{object: "Lead", field: "Company", setClause: "f.Required = 1"},
		{object: "Lead", field: "Status", setClause: "f.Required = 1"},
		// Issue #507: Contract, Quote, Order required field parity with Salesforce standard objects.
		// Discover-path seeded these as Required=0; describe then emits nillable=true and the
		// form omits required markers + client validation.
		{object: "Contract", field: "AccountId", setClause: "f.Required = 1"},
		{object: "Contract", field: "Status", setClause: "f.Required = 1"},
		{object: "Contract", field: "StartDate", setClause: "f.Required = 1"},
		{object: "Contract", field: "ContractTerm", setClause: "f.Required = 1"},
		{object: "Quote", field: "Name", setClause: "f.Required = 1"},
		{object: "Quote", field: "Status", setClause: "f.Required = 1"},
		{object: "Order", field: "AccountId", setClause: "f.Required = 1"},
		{object: "Order", field: "Status", setClause: "f.Required = 1"},
		{object: "Order", field: "EffectiveDate", setClause: "f.Required = 1"},
		// Required=0 overrides (2026-04-19): fields where the discover path
		// inferred Required=1 from NOT NULL physical columns, but the field
		// is either uncreatable (rollups / computed flags) or has no
		// user-facing UI entry point. Keeping Required=1 made describe emit
		// nillable=false and broke any metadata-driven form generator that
		// honors the flag.
		// Evidence: evidence/staging-required-fields-2026-04-19/staging_required_fields_matrix.csv
		// Campaign rollups (not computed by NexusCRM, no DefaultValue):
		{object: "Campaign", field: "AmountAllOpportunities", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Campaign", field: "AmountWonOpportunities", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Campaign", field: "NumberOfContacts", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Campaign", field: "NumberOfConvertedLeads", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Campaign", field: "NumberOfLeads", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Campaign", field: "NumberOfOpportunities", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Campaign", field: "NumberOfWonOpportunities", setClause: "f.Required = 0", preserveAdminCustomization: true},
		// Opportunity derived booleans (DefaultValue=False, Createable=0):
		{object: "Opportunity", field: "HasOpenActivity", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Opportunity", field: "HasOpportunityLineItem", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "Opportunity", field: "IsClosed", setClause: "f.Required = 0", preserveAdminCustomization: true},
		// OpportunityLineItem fields that should not be required (incorrectly inferred from NOT NULL columns):
		{object: "OpportunityLineItem", field: "PricebookEntryId", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "OpportunityLineItem", field: "Quantity", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "OpportunityLineItem", field: "Product2Id", setClause: "f.Required = 0", preserveAdminCustomization: true},
		// Order.StatusCode (derived from Status picklist, Createable=0, default=Draft):
		{object: "Order", field: "StatusCode", setClause: "f.Required = 0", preserveAdminCustomization: true},
		// User.Password / User.FailedLoginAttempts — User is defined in
		// system_tables.json (tableType=system_core). Password is set via
		// invite / reset-password flows, not via the metadata-driven user
		// create form; FailedLoginAttempts is a counter updated by auth, not
		// a user-supplied value. Both get inferred as Required=1 from the
		// NOT NULL physical columns. Relaxing to Required=0 matches the
		// actual write paths and unblocks describe-driven form consumers.
		{object: "User", field: "Password", setClause: "f.Required = 0", preserveAdminCustomization: true},
		{object: "User", field: "FailedLoginAttempts", setClause: "f.Required = 0", preserveAdminCustomization: true},
	}

	var totalFixed int64
	for _, ov := range overrides {
		where := "WHERE o.ApiName = ? AND f.ApiName = ?"
		if ov.preserveAdminCustomization {
			where += " AND (f.IsCustomized = 0 OR f.IsCustomized IS NULL)"
		}
		query := fmt.Sprintf(
			"UPDATE `%s` f JOIN `%s` o ON o.Id = f.ObjectId SET %s %s",
			constants.TableField, constants.TableObject, ov.setClause, where,
		)
		result, err := db.Exec(query, ov.object, ov.field)
		if err != nil {
			log.Printf("   ⚠️  Failed to fix %s.%s metadata: %v", ov.object, ov.field, err)
			continue
		}
		if affected, _ := result.RowsAffected(); affected > 0 {
			totalFixed += affected
		}
	}
	if totalFixed > 0 {
		log.Printf("   🔧 Corrected %d discovered field metadata overrides", totalFixed)
	}
}

// BackfillFieldCapabilities ensures all fields in _System_Field have correct
// Createable/Updateable values. Idempotent — safe to run on every startup.
// This covers fields that were inserted before the Createable/Updateable columns existed,
// or fields inserted via raw SQL (discover path) that bypassed ComputeIntrinsic.
//
// Note: the SELECT and minimal FieldMetadata MUST include every field that
// ComputeIntrinsic reads (e.g., Source, IsCompound for Layers 1d and 1e added
// in issue #1582 RFC v1.5 PR A2). Otherwise those layers become dead code
// during backfill and the stored Createable/Updateable columns diverge from
// the live ComputeIntrinsic truth.
func BackfillFieldCapabilities(dbConn *database.TiDBConnection) error {
	db := dbConn.DB()

	// Read all fields with their object API name, then use the shared ComputeIntrinsic
	// function as the single source of truth. No hardcoded SQL denylists.
	// Source / IsCompound are no longer stored; ComputeIntrinsic Layer 1d/1e
	// consult fieldprovenance.Classify* derived from L1 manifests (RFC v1.6).
	rows, err := db.Query(
		fmt.Sprintf("SELECT f.Id, f.ApiName, f.Type, f.Formula, f.Createable, f.Updateable, o.ApiName AS ObjApiName "+
			"FROM `%s` f JOIN `%s` o ON o.Id = f.ObjectId WHERE f.IsDeleted = 0",
			constants.TableField, constants.TableObject))
	if err != nil {
		return fmt.Errorf("backfill: failed to query fields: %w", err)
	}
	defer rows.Close()

	type fieldRow struct {
		id, apiName, objAPIName string
		fieldType               string
		formula                 sql.NullString
		curCreate, curUpdate    bool
	}
	var toUpdate []fieldRow

	for rows.Next() {
		var r fieldRow
		if err := rows.Scan(&r.id, &r.apiName, &r.fieldType, &r.formula, &r.curCreate, &r.curUpdate, &r.objAPIName); err != nil {
			continue
		}
		// Minimal FieldMetadata for ComputeIntrinsic. Layer 1d/1e delegate to
		// fieldprovenance.Classify* using the (object, field) pair — no
		// stored Source / IsCompound struct fields required.
		fm := &models.FieldMetadata{APIName: r.apiName, Type: models.FieldType(r.fieldType)}
		if r.formula.Valid && r.formula.String != "" {
			fm.Formula = &r.formula.String
		}
		wantC, wantU := fieldcapabilities.ComputeIntrinsic(r.objAPIName, fm)
		if wantC != r.curCreate || wantU != r.curUpdate {
			r.curCreate = wantC
			r.curUpdate = wantU
			toUpdate = append(toUpdate, r)
		}
	}

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

	// Batch update in chunks
	for _, r := range toUpdate {
		db.Exec(fmt.Sprintf("UPDATE `%s` SET Createable = ?, Updateable = ? WHERE Id = ?",
			constants.TableField), r.curCreate, r.curUpdate, r.id)
	}
	log.Printf("   🔧 Backfilled Createable/Updateable for %d fields", len(toUpdate))
	return nil
}