package schemautil

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// NormalizedMultiPicklistJSON marks a multi-picklist value that has already
// been normalized into the authoritative storage JSON-array shape during the
// current write pipeline. This is the disambiguator for optionless/runtime-
// scoped fields where a plain string like `["Others"]` can be either a user
// literal or the stored JSON payload, and bytes alone are insufficient.
type NormalizedMultiPicklistJSON string

// ExactDecimal preserves a DECIMAL storage value that must not be rounded
// through float64 before a downstream metadata-aware reader chooses its domain.
type ExactDecimal string

func (d ExactDecimal) String() string {
	return string(d)
}

func (d ExactDecimal) MarshalJSON() ([]byte, error) {
	value := strings.TrimSpace(string(d))
	if !isExactDecimalString(value) {
		return nil, fmt.Errorf("invalid exact decimal %q", string(d))
	}
	return []byte(value), nil
}

// CoefficientAndScale returns the canonical signed base-10 coefficient and
// the number of fractional digits carried by the storage value. The public
// float value remains available for legacy consumers; selected-root
// projection must use these parts rather than reconstructing an exact decimal
// from float64.
func (d ExactDecimal) CoefficientAndScale() (coefficient string, scale uint32, ok bool) {
	return exactDecimalCoefficientAndScale(strings.TrimSpace(d.String()))
}

func SetExactDecimalForField(record models.SObject, field string, decimal ExactDecimal) {
	if record == nil || strings.TrimSpace(field) == "" {
		return
	}
	value := strings.TrimSpace(decimal.String())
	if !isExactDecimalString(value) {
		return
	}
	decimals, _ := record[models.ExactDecimalMetadataKey].(map[string]ExactDecimal)
	if decimals == nil {
		decimals = make(map[string]ExactDecimal)
		record[models.ExactDecimalMetadataKey] = decimals
	}
	decimals[strings.TrimSpace(field)] = ExactDecimal(value)
}

func ExactDecimalForField(record models.SObject, field string) (ExactDecimal, bool) {
	if record == nil || strings.TrimSpace(field) == "" {
		return "", false
	}
	raw, ok := record[models.ExactDecimalMetadataKey]
	if !ok {
		return "", false
	}
	field = strings.TrimSpace(field)
	switch decimals := raw.(type) {
	case map[string]ExactDecimal:
		decimal, ok := decimals[field]
		if ok && isExactDecimalString(decimal.String()) {
			return decimal, true
		}
	case map[string]string:
		decimal := strings.TrimSpace(decimals[field])
		if isExactDecimalString(decimal) {
			return ExactDecimal(decimal), true
		}
	case map[string]interface{}:
		switch decimal := decimals[field].(type) {
		case ExactDecimal:
			if isExactDecimalString(decimal.String()) {
				return decimal, true
			}
		case string:
			trimmed := strings.TrimSpace(decimal)
			if isExactDecimalString(trimmed) {
				return ExactDecimal(trimmed), true
			}
		}
	}
	return "", false
}

// CopyExactDecimalsForPresentFields copies only decimal carriers whose public
// field is present in target. It is used after field authorization/projection,
// so a hidden or absent field cannot leave a side-channel entry behind.
func CopyExactDecimalsForPresentFields(source, target models.SObject) {
	if source == nil || target == nil {
		return
	}
	decimals := make(map[string]ExactDecimal)
	for field := range target {
		if models.IsRecordMetadataKey(field) {
			continue
		}
		if decimal, ok := ExactDecimalForField(source, field); ok {
			decimals[field] = decimal
		}
	}
	delete(target, models.ExactDecimalMetadataKey)
	if len(decimals) > 0 {
		target[models.ExactDecimalMetadataKey] = decimals
	}
}

// NormalizeSObject keys to match schema (case-insensitive) and coerce values to correct type.
// Returns a new SObject with normalized data.
func NormalizeSObject(schema *models.ObjectMetadata, input models.SObject) models.SObject {
	normalized := make(models.SObject)

	for k, v := range input {
		found := false
		field := FindField(schema, k)
		if field != nil {
			f := *field
			// Key match found

			// Type Coercion
			var val interface{} = v
			if v != nil {
				// Cross-object RecordLookup / DB-driver reads commonly surface text-like
				// columns as []byte. Collapse that carrier to string first so the
				// existing field-type coercion rules (number/bool/json/multipicklist/
				// reference/string) run on one canonical scalar shape.
				if rawBytes, ok := v.([]byte); ok {
					val = string(rawBytes)
					v = val
				}

				// Handle Number/Currency/Percent
				isNumber := f.Type == constants.FieldTypeDouble ||
					f.Type == constants.FieldTypeCurrency ||
					f.Type == constants.FieldTypePercent

				if isNumber {
					if s, ok := v.(string); ok && s != "" {
						// Attempt parse
						if fVal, err := strconv.ParseFloat(s, 64); err == nil {
							val = fVal
						}
					}
				}
				// Handle Boolean. The coercion set MIRRORS what
				// ValidateSchemaCompliance.validateType accepts for physical
				// boolean fields (validation/schema_validator.go:111): bool,
				// float64 in {0,1}, and any string that strconv.ParseBool
				// accepts. Lock-step with Validate is what makes virtual
				// boolean fields (Permissions<X>) travel through the same
				// pipeline as physical booleans — review #1831 P2 flagged
				// that pre-fix, `PermissionsViewSetup: "1"` was rejected
				// while an equivalent physical field would pass.
				//
				// Types Validate does NOT accept (int, int64, []uint8,
				// maps, slices, unparseable strings) are LEFT AS-IS so the
				// downstream guard can emit a clear error — for physicals
				// that guard is ValidateSchemaCompliance; for virtual
				// SystemPermission fields it's ExtractPermissionGrants's
				// fail-loud check. Either way, no silent coerce-to-false
				// (the review #1831 P1 regression).
				if f.Type == constants.FieldTypeBoolean {
					switch b := v.(type) {
					case bool:
						val = b
					case float64:
						if b == 0 || b == 1 {
							val = b != 0
						}
						// Other float64 values left as-is; Validate rejects.
					case string:
						if parsed, err := strconv.ParseBool(b); err == nil {
							val = parsed
						}
						// Unparseable string: leave as-is.
					}
					// default: leave as-is for downstream guard.
				}

				// Handle Picklist — coerce JSON booleans to their string representation
				// ("true"/"false") before type validation. This is a NexusCRM declared
				// delta from strict Salesforce v63 behavior (SF returns
				// INVALID_TYPE_ON_FIELD_IN_RECORD for JSON booleans on picklist fields).
				// The coercion is schema-shape based (Field.Type == Picklist), generic
				// across all orgs, and backward-compatible: the downstream
				// validatePicklistOption still enforces the admin-configured option-set
				// membership, so if "true"/"false" are not valid options the request is
				// still rejected. Float/int inputs are intentionally omitted — only the
				// JSON-native bool type is coerced, matching the reported bug scope.
				if f.Type == constants.FieldTypePicklist {
					if b, ok := v.(bool); ok {
						val = strconv.FormatBool(b)
					}
					if len(f.Options) > 0 && !models.IsRuntimeScopedPicklist(f) {
						if s, ok := val.(string); ok {
							if !isDeclaredSinglePicklistOption(f.Options, s) {
								trimmed := strings.TrimSpace(s)
								if trimmed == "" {
									val = nil
								} else {
									val = trimmed
								}
							}
						}
					}
				}

				// Handle JSON fields - marshal objects/arrays to string for DB driver.
				// Plain strings that are not valid JSON (e.g. rich-text HTML) are wrapped
				// as a JSON string literal to avoid TiDB Error 3140 (Invalid JSON text).
				if f.Type == constants.FieldTypeJson {
					switch tv := v.(type) {
					case map[string]interface{}, []interface{}, []map[string]interface{}:
						if jsonBytes, err := json.Marshal(v); err == nil {
							val = string(jsonBytes)
						}
					case string:
						if json.Valid([]byte(tv)) {
							val = tv // already valid JSON — pass through as-is
						} else if jsonBytes, err := json.Marshal(tv); err == nil {
							val = string(jsonBytes) // wrap plain text as a JSON string literal
						}
					}
				}

				// Handle MultiPicklist — TiDB column is JSON, and every accepted
				// write shape converges to a JSON array string. Field metadata
				// disambiguates legal option literals that look like JSON from
				// JSON-array envelopes. NormalizeFieldType accepts the
				// "MultiselectPicklist" alias added alongside this PR.
				if constants.NormalizeFieldType(string(f.Type)) == constants.FieldTypeMultiPicklist {
					// Delegate to the field-aware helper introduced by this
					// PR. The helper preserves the NormalizedMultiPicklistJSON
					// sentinel, honors declared-option values for leading-`[`
					// scalar inputs (so `["Others"]` stays a scalar option),
					// and falls through to semicolon splitting + JSON-array
					// canonicalization for all other shapes — covering the
					// pre-existing inline logic plus the legacy double-wrap
					// case the helper exists to consolidate.
					val = NormalizeMultiPicklistStorageForField(v, &f)
				}
			}

			normalized[f.APIName] = val
			found = true
		}

		if !found {
			// Keep original if not found in schema (preserving extra data like 'id' if needed)
			normalized[k] = v
		}
	}
	return normalized
}

// FromStorageRecord hydrates an SObject from a database row (converting 1/0 to bool, etc).
// Formula hydration is handled by QueryService. This function focuses on static type conversion.
func FromStorageRecord(schema *models.ObjectMetadata, record models.SObject, visibleFields []string) models.SObject {
	// Filter visible fields if provided
	if len(visibleFields) > 0 {
		filtered := make(models.SObject)
		for _, f := range visibleFields {
			if val, ok := record[f]; ok {
				filtered[f] = val
			}
		}
		// Also keep ID if not in visible list but in record?
		if val, ok := record[constants.FieldID]; ok {
			filtered[constants.FieldID] = val
		}
		// Also keep polymorphic _type fields for visible polymorphic fields
		for _, f := range visibleFields {
			fieldMeta := FindField(schema, f)
			if fieldMeta != nil && fieldMeta.IsPolymorphic {
				typeCol := GetPolymorphicTypeColumnName(f)
				if val, ok := record[typeCol]; ok {
					filtered[typeCol] = val
				}
			}
		}
		record = filtered
	}

	// Iterate to convert types
	for _, field := range schema.Fields {
		// [System Field Aliasing]
		// Check both APIName and PhysicalName in the record
		// 1. Try APIName (Standard behavior)
		val, exists := record[field.APIName]

		// 2. If not found, and we have a PhysicalName, try that (Aliasing behavior)
		if !exists && field.PhysicalName != "" {
			val, exists = record[field.PhysicalName]
		}

		if !exists || val == nil {
			continue
		}

		// [System Field Aliasing]
		// If we found the value via PhysicalName, ensure it's set on the APIName key
		// This is critical for String types which don't hit the type conversion blocks below
		if exists && field.PhysicalName != "" && field.APIName != field.PhysicalName {
			record[field.APIName] = val
		}

		if EffectiveFieldType(field) == constants.FieldTypeDate {
			record[field.APIName] = normalizeDateOnlyValue(val)
			continue
		}

		if EffectiveFieldType(field) == constants.FieldTypeBoolean {
			// Recognized types get coerced; everything else is left as-is
			// so ValidateSchemaCompliance can reject it cleanly.
			//
			// The previous default case did `fmt.Sprintf("%v", v) == "1" || == "true"`
			// which silently mapped unrecognized types (maps, slices, nonstandard
			// strings, struct values) to `false`. For physical boolean fields this
			// disguised bad input as "not checked"; for virtual permission fields
			// (schema-driven from the SchemaDecorator) it silently revoked the
			// grant — the regression flagged in review #1831 P1. Leaving unknown
			// types alone means Validate gets to emit a clear "expects a Boolean
			// value, got <type>" error instead of the write appearing to succeed.
			switch v := val.(type) {
			case int64:
				record[field.APIName] = v != 0
			case int:
				record[field.APIName] = v != 0
			case bool:
				record[field.APIName] = v
			case []uint8: // MySQL sometimes returns bytes for bits/bools
				// simplified check
				record[field.APIName] = len(v) > 0 && v[0] != 0 // very rough, depends on driver
			case string:
				if parsed, err := strconv.ParseBool(v); err == nil {
					record[field.APIName] = parsed
				}
				// Unparseable string: leave as-is so Validate rejects it.
			default:
				// Leave as-is — Validate will reject on type check.
			}
		}

		// Handle DECIMAL-backed numeric types which might be bytes or strings.
		// Preserve the driver text before producing the historical float64 field
		// value. Formula/rollup fields use their effective return type.
		effectiveType := EffectiveFieldType(field)
		if effectiveType == constants.FieldTypeInt {
			if integer, ok := decimalBackedIntegerFromStorageValue(val); ok {
				record[field.APIName] = integer
			}
			continue
		}
		if effectiveType == constants.FieldTypeDouble ||
			effectiveType == constants.FieldTypeCurrency ||
			effectiveType == constants.FieldTypePercent {

			if decimal, ok := exactDecimalFromStorageValue(val); ok {
				SetExactDecimalForField(record, field.APIName, decimal)
			}

			// Check for []uint8 (bytes from DB)
			if b, ok := val.([]uint8); ok {
				if s := string(b); s != "" {
					if f, err := strconv.ParseFloat(s, 64); err == nil {
						record[field.APIName] = f
					}
				}
			} else if s, ok := val.(string); ok && s != "" {
				if f, err := strconv.ParseFloat(s, 64); err == nil {
					record[field.APIName] = f
				}
			}
		}

		// Handle JSON types: Unmarshal string/bytes back to interface{}
		if field.Type == constants.FieldTypeJson {
			var jsonVal interface{}
			var err error

			if b, ok := val.([]uint8); ok {
				err = json.Unmarshal(b, &jsonVal)
			} else if s, ok := val.(string); ok && s != "" {
				err = json.Unmarshal([]byte(s), &jsonVal)
			}

			if err == nil && jsonVal != nil {
				record[field.APIName] = jsonVal
			}
		}

		// Handle MultiPicklist: DB stores JSON array string like '["AWS","GCP"]'.
		// Public REST/SOQL reads use Salesforce-compatible semicolon text.
		if constants.NormalizeFieldType(string(field.Type)) == constants.FieldTypeMultiPicklist {
			record[field.APIName] = MultiPicklistWireValueForField(val, &field)
		}
	}

	return record
}

// decimalBackedIntegerFromStorageValue converts a DECIMAL driver value to the
// public integer form only when its fractional component is exactly zero. COUNT
// rollups are stored as DECIMAL for aggregate safety but are API integers.
func decimalBackedIntegerFromStorageValue(value interface{}) (int64, bool) {
	decimal, ok := exactDecimalFromStorageValue(value)
	if !ok {
		return 0, false
	}
	coefficient, scale, ok := decimal.CoefficientAndScale()
	if !ok {
		return 0, false
	}
	negative := strings.HasPrefix(coefficient, "-")
	digits := strings.TrimPrefix(coefficient, "-")
	if scale > 0 {
		if uint32(len(digits)) <= scale {
			if strings.Trim(digits, "0") != "" {
				return 0, false
			}
			digits = "0"
		} else {
			fractional := digits[len(digits)-int(scale):]
			if strings.Trim(fractional, "0") != "" {
				return 0, false
			}
			digits = digits[:len(digits)-int(scale)]
		}
	}
	digits = strings.TrimLeft(digits, "0")
	if digits == "" {
		return 0, true
	}
	if negative {
		digits = "-" + digits
	}
	integer, err := strconv.ParseInt(digits, 10, 64)
	if err != nil {
		return 0, false
	}
	return integer, true
}

func exactDecimalFromStorageValue(value interface{}) (ExactDecimal, bool) {
	switch v := value.(type) {
	case ExactDecimal:
		if isExactDecimalString(v.String()) {
			return v, true
		}
	case []byte:
		trimmed := strings.TrimSpace(string(v))
		if isExactDecimalString(trimmed) {
			return ExactDecimal(trimmed), true
		}
	case string:
		trimmed := strings.TrimSpace(v)
		if isExactDecimalString(trimmed) {
			return ExactDecimal(trimmed), true
		}
	}
	return "", false
}

func isExactDecimalString(value string) bool {
	_, _, ok := exactDecimalCoefficientAndScale(value)
	return ok
}

func exactDecimalCoefficientAndScale(value string) (string, uint32, bool) {
	if value == "" {
		return "", 0, false
	}
	negative := value[0] == '-'
	if negative {
		value = value[1:]
		if value == "" {
			return "", 0, false
		}
	}
	if strings.Count(value, ".") > 1 {
		return "", 0, false
	}
	integer, fraction, hasFraction := strings.Cut(value, ".")
	if integer == "" || (hasFraction && fraction == "") {
		return "", 0, false
	}
	if len(integer) > 1 && integer[0] == '0' {
		return "", 0, false
	}
	for _, digit := range integer + fraction {
		if digit < '0' || digit > '9' {
			return "", 0, false
		}
	}
	coefficient := strings.TrimLeft(integer+fraction, "0")
	if coefficient == "" {
		coefficient = "0"
	} else if negative {
		coefficient = "-" + coefficient
	}
	return coefficient, uint32(len(fraction)), true
}

func normalizeDateOnlyValue(val interface{}) interface{} {
	switch v := val.(type) {
	case time.Time:
		return v.Format("2006-01-02")
	case []uint8:
		return normalizeDateOnlyString(string(v))
	case string:
		return normalizeDateOnlyString(v)
	default:
		return val
	}
}

func normalizeDateOnlyString(value string) string {
	trimmed := strings.TrimSpace(value)
	if len(trimmed) >= len("2006-01-02") && trimmed[4] == '-' && trimmed[7] == '-' {
		return trimmed[:10]
	}

	for _, layout := range []string{
		"2006-01-02",
		"2006-01-02 15:04:05",
		time.RFC3339,
	} {
		if parsed, err := time.Parse(layout, trimmed); err == nil {
			return parsed.Format("2006-01-02")
		}
	}

	return trimmed
}

// EffectiveFieldType returns the storage/result type that consumers must use
// for a field. Formula metadata carries its scalar type in ReturnType. A
// rollup's canonical scalar result is the resolver-owned
// RollupConfig.ResultType; ReturnType is the hydrated compatibility mirror for
// metadata snapshots that do not carry that config facet. Every other field is
// its normalized declared type.
//
// Keeping this resolver beside FromStorageRecord prevents selected projection
// and hydration from developing competing interpretations of the same field.
func EffectiveFieldType(field models.FieldMetadata) constants.SchemaFieldType {
	base := constants.NormalizeFieldType(string(field.Type))
	if base != constants.FieldTypeFormula && base != constants.FieldTypeRollupSummary {
		return base
	}
	if base == constants.FieldTypeRollupSummary && field.RollupConfig != nil && field.RollupConfig.ResultType != nil {
		if normalized := constants.NormalizeFieldType(string(*field.RollupConfig.ResultType)); normalized != "" {
			return normalized
		}
	}
	if field.ReturnType != nil {
		if normalized := constants.NormalizeFieldType(string(*field.ReturnType)); normalized != "" {
			return normalized
		}
	}
	return base
}

// IsBlankAsZeroNumericType reports whether a scalar field type participates in
// blank-as-zero formula-operand semantics (the Salesforce
// formulaTreatBlanksAs=BlankAsZero default): a blank operand of one of these
// types reads as 0 inside formula-field evaluation. Booleans are excluded on
// purpose — they surface through storage as tinyint 1/0, and coercing a blank
// boolean to 0 would silently turn "unset" into "false" for gate formulas.
// Date/datetime are excluded because zero is not a meaningful date.
//
// The accepted spellings mirror the analytics compiler's numeric bucket so the
// SQL projection and the Go evaluator classify operands identically; callers
// pass either a raw metadata type string or a normalized EffectiveFieldType.
func IsBlankAsZeroNumericType(fieldType string) bool {
	switch strings.ToLower(strings.TrimSpace(fieldType)) {
	case "currency", "double", "int", "integer", "long", "number", "percent":
		return true
	}
	return false
}

// NormalizeMultiPicklistStorage canonicalizes any recognized multi-select
// picklist write/read-repair shape into the TiDB JSON-column storage shape.
// It returns nil for empty selections so blank writes clear the field instead
// of storing an empty wrapper.
func NormalizeMultiPicklistStorage(value interface{}) interface{} {
	return normalizeMultiPicklistStorage(value, nil, multiPicklistDecodeWrite)
}

// NormalizeMultiPicklistStorageBackfill is the database backfill entry point.
// It reads an at-rest TiDB JSON column value and normalizes it to the canonical
// JSON array shape. The input comes from a TiDB JSON column and is one of:
//   - a JSON array string like `["Hot","Cold"]` → passed through unchanged
//   - a legacy JSON string like `"Hot;Cold"` → split by ; and returned as
//     the JSON array `["Hot","Cold"]`
//   - nil → passed through unchanged
//   - []byte → decoded to string first
//
// The function is idempotent: already-canonical JSON arrays round-trip
// through json.Unmarshal+Marshal and return byte-for-byte identical.
// Runtime INCLUDES/EXCLUDES predicates also understand the legacy JSON STRING
// envelope, so callers use this helper for explicit physical cleanup rather
// than deploy-critical query correctness.
func NormalizeMultiPicklistStorageBackfill(value interface{}) interface{} {
	switch v := value.(type) {
	case nil:
		return nil
	case []byte:
		return NormalizeMultiPicklistStorageBackfill(string(v))
	case string:
		s := strings.TrimSpace(v)
		if s == "" {
			return nil
		}
		// Parse the JSON column value to determine the JSON type.
		var raw interface{}
		if err := json.Unmarshal([]byte(s), &raw); err != nil {
			// Not valid JSON — return as-is so corrupt data remains visible.
			return s
		}
		switch jv := raw.(type) {
		case []interface{}:
			// Already a JSON array — canonical, pass through.
			// Round-trip through Marshal to normalise whitespace.
			b, _ := json.Marshal(jv)
			return string(b)
		case string:
			// Legacy semicolon-delimited JSON string — split and normalise.
			items := dedupeTrimmedMultiPicklistItems(strings.Split(jv, ";"))
			if len(items) == 0 {
				return nil
			}
			b, err := json.Marshal(items)
			if err != nil {
				return s
			}
			return string(b)
		default:
			return s
		}
	default:
		return value
	}
}

// NormalizeMultiPicklistStorageBackfillForField is the field-aware database
// backfill helper for double-wrapper repair tooling. It reads an at-rest TiDB
// JSON column value and, ONLY when that value is a JSON array whose every
// element is a string, applies the field-aware declared-option unwrapping that
// repairs historical double-wrapper arrays (for example,
// `["[\"A\",\"B\"]"]` to `["A","B"]`).
//
// Every other at-rest JSON shape is returned byte-for-byte unchanged so the
// repair tooling can never corrupt data it cannot interpret:
//   - JSON string (including any legacy semicolon value, which the 20260618
//     storage backfill owns), JSON null, JSON number, JSON object, JSON boolean;
//   - JSON arrays containing any non-string element (not a multipicklist
//     storage shape).
//
// The unwrap itself (delegated to normalizeMultiPicklistStorage in
// multiPicklistDecodeStorage mode) fires ONLY when every inner token of the
// nested array is a declared option (multiPicklistJSONArraySelectionsAreDeclared);
// arrays whose contents are not all declared options round-trip unchanged.
//
// A blank/whitespace-only value converges to nil (canonical empty -> NULL),
// matching the 20260618 runner's nil handling. Idempotent: canonical arrays and
// already-repaired rows round-trip unchanged. Callers must issue an UPDATE only
// when the normalized value differs from the original.
func NormalizeMultiPicklistStorageBackfillForField(value interface{}, field *models.FieldMetadata) interface{} {
	switch v := value.(type) {
	case nil:
		return nil
	case []byte:
		return NormalizeMultiPicklistStorageBackfillForField(string(v), field)
	case string:
		s := strings.TrimSpace(v)
		if s == "" {
			return nil
		}
		// Determine the at-rest JSON type before doing anything. Only a JSON
		// array whose every element is a string is a double-wrapper repair
		// candidate; every other JSON type (string, null, number, object,
		// boolean) and any array with a non-string element are returned
		// byte-for-byte unchanged so repair tooling never corrupts data it
		// cannot interpret.
		var raw interface{}
		if err := json.Unmarshal([]byte(s), &raw); err != nil {
			return v // not valid JSON; leave untouched
		}
		arr, isArray := raw.([]interface{})
		if !isArray {
			return v
		}
		for _, el := range arr {
			if _, ok := el.(string); !ok {
				return v // non-string element; not a multipicklist storage shape
			}
		}
		return normalizeMultiPicklistStorage(v, field, multiPicklistDecodeStorage)
	default:
		return value
	}
}

// HasUnresolvedMultiPicklistWrapperStorageForField reports whether an at-rest
// multipicklist JSON value still contains historical double-wrapper storage
// that cannot be treated as a declared option literal for this field.
func HasUnresolvedMultiPicklistWrapperStorageForField(value interface{}, field *models.FieldMetadata) bool {
	optionSet := multiPicklistOptionSet(field)
	items, ok := multiPicklistStorageJSONArray(value)
	if !ok {
		return false
	}
	for _, item := range items {
		itemString, ok := item.(string)
		if !ok {
			continue
		}
		trimmed := strings.TrimSpace(itemString)
		if trimmed == "" || isDeclaredMultiPicklistOption(optionSet, trimmed) {
			continue
		}
		if strings.HasPrefix(trimmed, "[") {
			var nested []interface{}
			if err := json.Unmarshal([]byte(trimmed), &nested); err == nil {
				return true
			}
		}
	}
	return false
}

func multiPicklistStorageJSONArray(value interface{}) ([]interface{}, bool) {
	switch v := value.(type) {
	case nil:
		return nil, false
	case []byte:
		return multiPicklistStorageJSONArray(string(v))
	case string:
		s := strings.TrimSpace(v)
		if s == "" {
			return nil, false
		}
		var raw []interface{}
		if err := json.Unmarshal([]byte(s), &raw); err != nil {
			return nil, false
		}
		return raw, true
	case []string:
		items := make([]interface{}, 0, len(v))
		for _, item := range v {
			items = append(items, item)
		}
		return items, true
	case []interface{}:
		return v, true
	default:
		return nil, false
	}
}

// NormalizeMultiPicklistStorageForField is the metadata-aware variant of
// NormalizeMultiPicklistStorage. Declared option values win over compatibility
// decoding for JSON-looking scalar write inputs. The output is wrapped in a
// NormalizedMultiPicklistJSON sentinel so subsequent NormalizeSObject passes
// are idempotent on optionless/runtime-scoped fields where the bare string
// shape is ambiguous with a user literal.
func NormalizeMultiPicklistStorageForField(value interface{}, field *models.FieldMetadata) interface{} {
	if v, ok := value.(NormalizedMultiPicklistJSON); ok {
		return v
	}
	normalized := normalizeMultiPicklistStorage(value, field, multiPicklistDecodeWrite)
	if s, ok := normalized.(string); ok {
		return NormalizedMultiPicklistJSON(s)
	}
	return normalized
}

func normalizeMultiPicklistStorage(value interface{}, field *models.FieldMetadata, mode multiPicklistDecodeMode) interface{} {
	items := multiPicklistItems(value, field, mode)
	if len(items) == 0 {
		if isExplicitEmptyMultiPicklistArray(value) || (mode == multiPicklistDecodeStorage && isEmptyMultiPicklistJSONArrayString(value)) {
			return "[]"
		}
		return nil
	}
	jsonBytes, err := json.Marshal(items)
	if err != nil {
		return value
	}
	return string(jsonBytes)
}

// MultiPicklistWireValue renders a storage value using the Salesforce REST/SOQL
// semicolon wire representation.
func MultiPicklistWireValue(value interface{}) string {
	return MultiPicklistWireValueForField(value, nil)
}

// MultiPicklistWireValueForField renders a storage value while preserving
// declared option literals that happen to look like legacy JSON wrappers.
func MultiPicklistWireValueForField(value interface{}, field *models.FieldMetadata) string {
	return strings.Join(multiPicklistItems(value, field, multiPicklistDecodeStorage), ";")
}

// CanonicalMultiPicklistItems flattens the compatibility shapes accepted by
// multi-picklist consumers into trimmed, non-empty, de-duplicated selections.
// JSON arrays must contain strings; malformed or mixed-type arrays remain
// visible through the semicolon fallback instead of being silently accepted as
// valid storage.
func CanonicalMultiPicklistItems(value interface{}) []string {
	switch typed := value.(type) {
	case nil:
		return nil
	case []byte:
		return CanonicalMultiPicklistItems(string(typed))
	case NormalizedMultiPicklistJSON:
		return CanonicalMultiPicklistItems(string(typed))
	case string:
		trimmed := strings.TrimSpace(typed)
		if trimmed == "" {
			return nil
		}
		if strings.HasPrefix(trimmed, "[") {
			var items []string
			if err := json.Unmarshal([]byte(trimmed), &items); err == nil {
				return dedupeTrimmedMultiPicklistItems(items)
			}
		}
		return dedupeTrimmedMultiPicklistItems(strings.Split(trimmed, ";"))
	case []string:
		return dedupeTrimmedMultiPicklistItems(typed)
	case []interface{}:
		items := make([]string, 0, len(typed))
		for _, item := range typed {
			if stringItem, ok := item.(string); ok {
				items = append(items, stringItem)
			} else if item != nil {
				items = append(items, fmt.Sprintf("%v", item))
			}
		}
		return dedupeTrimmedMultiPicklistItems(items)
	default:
		return []string{fmt.Sprintf("%v", value)}
	}
}

// CanonicalMultiPicklistSetKey returns an order-independent key for any
// compatibility serialization accepted by CanonicalMultiPicklistItems.
func CanonicalMultiPicklistSetKey(value interface{}) string {
	items := CanonicalMultiPicklistItems(value)
	if len(items) == 0 {
		return ""
	}
	sort.Strings(items)
	return strings.Join(items, "\x00")
}

// CanonicalMultiPicklistSetKeyForField returns an order-independent selection
// key after applying the field-aware option-literal disambiguation owned by
// NormalizeMultiPicklistStorageForField. An already-normalized malformed
// sentinel remains byte-visible so corrupt storage cannot collapse into an
// unrelated valid selection set.
func CanonicalMultiPicklistSetKeyForField(value interface{}, field *models.FieldMetadata) string {
	normalized := NormalizeMultiPicklistStorageForField(value, field)
	var jsonArray string
	switch typed := normalized.(type) {
	case nil:
		return ""
	case NormalizedMultiPicklistJSON:
		jsonArray = string(typed)
	case string:
		jsonArray = typed
	default:
		return ""
	}

	trimmed := strings.TrimSpace(jsonArray)
	if trimmed == "" || trimmed == "[]" {
		return ""
	}
	var items []string
	if err := json.Unmarshal([]byte(trimmed), &items); err != nil {
		return trimmed
	}
	items = dedupeTrimmedMultiPicklistItems(items)
	if len(items) == 0 {
		return ""
	}
	sort.Strings(items)
	return strings.Join(items, "\x00")
}

type multiPicklistDecodeMode int

const (
	multiPicklistDecodeWrite multiPicklistDecodeMode = iota
	multiPicklistDecodeStorage
)

func multiPicklistItems(value interface{}, field *models.FieldMetadata, mode multiPicklistDecodeMode) []string {
	return dedupeTrimmedMultiPicklistItems(multiPicklistItemsRaw(value, multiPicklistOptionSet(field), mode))
}

func multiPicklistItemsRaw(value interface{}, optionSet map[string]struct{}, mode multiPicklistDecodeMode) []string {
	switch v := value.(type) {
	case nil:
		return nil
	case []byte:
		return multiPicklistItemsRaw(string(v), optionSet, mode)
	case NormalizedMultiPicklistJSON:
		if items, ok := multiPicklistItemsFromJSONArrayString(string(v), optionSet); ok {
			return items
		}
		return []string{string(v)}
	case string:
		s := strings.TrimSpace(v)
		if s == "" {
			return nil
		}
		if mode == multiPicklistDecodeWrite && isDeclaredMultiPicklistOption(optionSet, s) {
			return []string{s}
		}
		if strings.HasPrefix(s, "[") {
			if items, ok := multiPicklistItemsFromJSONArrayString(s, optionSet); ok &&
				(mode == multiPicklistDecodeStorage || multiPicklistJSONArraySelectionsAreDeclaredJSON(s, optionSet)) {
				return items
			}
			// Malformed JSON falls through to semicolon splitting so corrupt
			// values remain visible instead of collapsing to empty.
		}
		return strings.Split(s, ";")
	case []string:
		arr := make([]interface{}, 0, len(v))
		for _, item := range v {
			arr = append(arr, item)
		}
		return multiPicklistItemsRaw(arr, optionSet, mode)
	case []interface{}:
		out := make([]string, 0, len(v))
		for _, item := range v {
			switch typed := item.(type) {
			case nil:
				continue
			case string:
				trimmed := strings.TrimSpace(typed)
				if isDeclaredMultiPicklistOption(optionSet, trimmed) {
					out = append(out, typed)
					continue
				}
				if strings.HasPrefix(trimmed, "[") {
					var nested []interface{}
					if err := json.Unmarshal([]byte(trimmed), &nested); err == nil &&
						multiPicklistJSONArraySelectionsAreDeclared(nested, optionSet) {
						out = append(out, multiPicklistItemsFromJSONArray(nested, optionSet)...)
						continue
					}
				}
				out = append(out, typed)
			case []string, []interface{}:
				out = append(out, multiPicklistItemsRaw(typed, optionSet, mode)...)
			default:
				out = append(out, fmt.Sprint(item))
			}
		}
		return out
	default:
		return []string{fmt.Sprint(value)}
	}
}

func multiPicklistItemsFromJSONArrayString(value string, optionSet map[string]struct{}) ([]string, bool) {
	var arr []interface{}
	if err := json.Unmarshal([]byte(value), &arr); err != nil {
		return nil, false
	}
	return multiPicklistItemsFromJSONArray(arr, optionSet), true
}

func multiPicklistItemsFromJSONArray(arr []interface{}, optionSet map[string]struct{}) []string {
	out := make([]string, 0, len(arr))
	for _, item := range arr {
		s, ok := item.(string)
		if !ok {
			if item != nil {
				out = append(out, fmt.Sprint(item))
			}
			continue
		}
		trimmed := strings.TrimSpace(s)
		if trimmed == "" {
			continue
		}
		if isDeclaredMultiPicklistOption(optionSet, trimmed) {
			out = append(out, s)
			continue
		}
		if strings.HasPrefix(trimmed, "[") {
			var nested []interface{}
			if err := json.Unmarshal([]byte(trimmed), &nested); err == nil &&
				multiPicklistJSONArraySelectionsAreDeclared(nested, optionSet) {
				out = append(out, multiPicklistItemsFromJSONArray(nested, optionSet)...)
				continue
			}
		}
		out = append(out, s)
	}
	return out
}

func multiPicklistJSONArraySelectionsAreDeclaredJSON(value string, optionSet map[string]struct{}) bool {
	var arr []interface{}
	if err := json.Unmarshal([]byte(value), &arr); err != nil {
		return false
	}
	return multiPicklistJSONArraySelectionsAreDeclared(arr, optionSet)
}

func multiPicklistJSONArraySelectionsAreDeclared(arr []interface{}, optionSet map[string]struct{}) bool {
	if len(optionSet) == 0 || len(arr) == 0 {
		return false
	}
	seenValue := false
	for _, item := range arr {
		s, ok := item.(string)
		if !ok {
			return false
		}
		trimmed := strings.TrimSpace(s)
		if trimmed == "" {
			continue
		}
		seenValue = true
		if isDeclaredMultiPicklistOption(optionSet, trimmed) {
			continue
		}
		if strings.HasPrefix(trimmed, "[") {
			var nested []interface{}
			if err := json.Unmarshal([]byte(trimmed), &nested); err == nil &&
				multiPicklistJSONArraySelectionsAreDeclared(nested, optionSet) {
				continue
			}
		}
		return false
	}
	return seenValue
}

func multiPicklistOptionSet(field *models.FieldMetadata) map[string]struct{} {
	if field == nil || len(field.Options) == 0 {
		return nil
	}
	out := make(map[string]struct{}, len(field.Options))
	for _, option := range field.Options {
		if option.Value == "" {
			continue
		}
		out[option.Value] = struct{}{}
	}
	return out
}

func isDeclaredMultiPicklistOption(optionSet map[string]struct{}, value string) bool {
	if len(optionSet) == 0 {
		return false
	}
	_, ok := optionSet[value]
	return ok
}

func isDeclaredSinglePicklistOption(options models.PicklistOptions, value string) bool {
	for _, option := range options {
		if option.Value == value {
			return true
		}
	}
	return false
}

func isExplicitEmptyMultiPicklistArray(value interface{}) bool {
	switch v := value.(type) {
	case []string:
		return len(v) == 0
	case []interface{}:
		return len(v) == 0
	case NormalizedMultiPicklistJSON:
		return strings.TrimSpace(string(v)) == "[]"
	default:
		return false
	}
}

func isEmptyMultiPicklistJSONArrayString(value interface{}) bool {
	switch v := value.(type) {
	case string:
		return strings.TrimSpace(v) == "[]"
	case []byte:
		return strings.TrimSpace(string(v)) == "[]"
	default:
		return false
	}
}

func dedupeTrimmedMultiPicklistItems(in []string) []string {
	if len(in) == 0 {
		return nil
	}
	seen := make(map[string]struct{}, len(in))
	out := make([]string, 0, len(in))
	for _, item := range in {
		trimmed := strings.TrimSpace(item)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		out = append(out, trimmed)
	}
	return out
}

// FindField finds a field in the schema by name (case-insensitive)
func FindField(schema *models.ObjectMetadata, fieldName string) *models.FieldMetadata {
	if schema == nil {
		return nil
	}

	// O(1) Lookup
	if schema.FieldsMap != nil {
		if f, ok := schema.FieldsMap[strings.ToLower(fieldName)]; ok {
			return f
		}
	}

	// Fallback: linear scan (covers objects populated without a map, e.g. tests)
	for i := range schema.Fields {
		if strings.EqualFold(schema.Fields[i].APIName, fieldName) {
			return &schema.Fields[i]
		}
	}
	return nil
}

// GetPolymorphicTypeColumnName returns the standardized column name for the type discriminator of a polymorphic field
func GetPolymorphicTypeColumnName(fieldAPIName string) string {
	return fieldAPIName + constants.PolymorphicTypeSuffix
}

// GenerateObjectID generates a standardized ID for an object based on its API Name
// GenerateObjectID -> MOVED to domain/schema
// GenerateFieldID -> MOVED to domain/schema
// GenerateTableID -> MOVED to domain/schema
// GenerateAutoNumberID -> MOVED to domain/schema
// GenerateAppID -> MOVED to domain/schema

// --- SQL Helpers ---

// ToNullString converts a *string to sql.NullString for database operations
func ToNullString(s *string) sql.NullString {
	if s != nil {
		return sql.NullString{String: *s, Valid: true}
	}
	return sql.NullString{Valid: false}
}

// ToNullInt64 converts a *int or *int64 to sql.NullInt64
func ToNullInt64(i interface{}) sql.NullInt64 {
	if i == nil {
		return sql.NullInt64{Valid: false}
	}
	switch v := i.(type) {
	case *int:
		if v == nil {
			return sql.NullInt64{Valid: false}
		}
		return sql.NullInt64{Int64: int64(*v), Valid: true}
	case *int64:
		if v == nil {
			return sql.NullInt64{Valid: false}
		}
		return sql.NullInt64{Int64: *v, Valid: true}
	case int:
		return sql.NullInt64{Int64: int64(v), Valid: true}
	default:
		return sql.NullInt64{Valid: false}
	}
}

// SliceToNullJSON converts a []string to sql.NullString as JSON array
func SliceToNullJSON(slice []string) sql.NullString {
	if len(slice) == 0 {
		return sql.NullString{Valid: false}
	}
	b, err := json.Marshal(slice)
	if err != nil {
		return sql.NullString{Valid: false}
	}
	return sql.NullString{String: string(b), Valid: true}
}

// ParseDBTime parses a time string from the database which might be in different formats
// Supports generic SQL format (2006-01-02 15:04:05) and RFC3339
func ParseDBTime(raw []byte) (time.Time, error) {
	s := string(raw)
	if t, err := time.Parse("2006-01-02 15:04:05", s); err == nil {
		return t, nil
	}
	return time.Parse(time.RFC3339, s)
}