package persistence

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

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

// 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 {
				// 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)
					}
				}

				// 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, value may arrive as:
				//   - string "A;B;C" (Salesforce semicolon-separated) → convert to JSON array
				//   - []interface{}{"A","B","C"} (already array from API) → marshal to JSON string
				if f.Type == constants.FieldTypeMultiPicklist {
					switch tv := v.(type) {
					case string:
						if tv == "" {
							val = nil
						} else {
							parts := strings.Split(tv, ";")
							trimmed := make([]string, 0, len(parts))
							for _, p := range parts {
								p = strings.TrimSpace(p)
								if p != "" {
									trimmed = append(trimmed, p)
								}
							}
							if jsonBytes, err := json.Marshal(trimmed); err == nil {
								val = string(jsonBytes)
							}
						}
					case []interface{}:
						if jsonBytes, err := json.Marshal(tv); err == nil {
							val = string(jsonBytes)
						}
					case []string:
						if jsonBytes, err := json.Marshal(tv); err == nil {
							val = string(jsonBytes)
						}
					}
				}
			}

			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 field.Type == constants.FieldTypeDate {
			record[field.APIName] = normalizeDateOnlyValue(val)
			continue
		}

		if field.Type == 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 Numeric types which might be bytes or strings
		if field.Type == constants.FieldTypeDouble ||
			field.Type == constants.FieldTypeCurrency ||
			field.Type == constants.FieldTypePercent {

			// 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"]'
		// Deserialize to []interface{} so the API returns a proper JSON array.
		if field.Type == constants.FieldTypeMultiPicklist {
			var arr []string
			var err error

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

			if err == nil && arr != nil {
				// Convert to Salesforce semicolon-separated format for API compatibility
				record[field.APIName] = strings.Join(arr, ";")
			}
		}
	}

	return record
}

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
}

// 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)
}