package persistence

import (
	"strings"
	"testing"
	"time"

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

// -----------------------------------------------------------------------
// getSystemFieldLabel  (schema_metadata_persist.go)
// -----------------------------------------------------------------------

func TestGetSystemFieldLabel(t *testing.T) {
	r := &SchemaRepository{db: nil}

	tests := []struct {
		name     string
		input    string
		expected string
	}{
		{"Id field", constants.FieldID, "ID"},
		{"CreatedDate", constants.FieldCreatedDate, "Created Date"},
		{"LastModifiedDate", constants.FieldLastModifiedDate, "Last Modified Date"},
		{"CreatedById", constants.FieldCreatedByID, "Created By"},
		{"LastModifiedById", constants.FieldLastModifiedByID, "Last Modified By"},
		{"OwnerId", constants.FieldOwnerID, "Owner"},
		{"IsDeleted", constants.FieldIsDeleted, "Deleted"},
		{"custom field snake_case", "first_name", "First Name"},
		{"custom field single word", "status", "Status"},
		{"empty string", "", ""},
		{"already title case", "MyField", "Myfield"},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := r.getSystemFieldLabel(tt.input)
			if got != tt.expected {
				t.Errorf("getSystemFieldLabel(%q) = %q, want %q", tt.input, got, tt.expected)
			}
		})
	}
}

// -----------------------------------------------------------------------
// configureBatchUpdates  (schema_metadata_persist.go)
// -----------------------------------------------------------------------

func TestConfigureBatchUpdates(t *testing.T) {
	r := &SchemaRepository{db: nil}

	t.Run("standard fields get VALUES() wrapping", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{"Label", "Type", "Required"}
		r.configureBatchUpdates(updates, fields)

		if len(updates) != 3 {
			t.Fatalf("expected 3 entries, got %d", len(updates))
		}

		for _, field := range fields {
			raw, ok := updates[field].(query.Raw)
			if !ok {
				t.Errorf("updates[%q] should be query.Raw, got %T", field, updates[field])
				continue
			}
			expected := "VALUES(`" + field + "`)"
			if string(raw) != expected {
				t.Errorf("updates[%q] = %q, want %q", field, string(raw), expected)
			}
		}
	})

	t.Run("LastModifiedDate gets NOW()", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{constants.FieldLastModifiedDate}
		r.configureBatchUpdates(updates, fields)

		raw, ok := updates[constants.FieldLastModifiedDate].(query.Raw)
		if !ok {
			t.Fatalf("LastModifiedDate should be query.Raw, got %T", updates[constants.FieldLastModifiedDate])
		}
		if string(raw) != "NOW()" {
			t.Errorf("LastModifiedDate = %q, want NOW()", string(raw))
		}
	})

	t.Run("empty fields produces empty map", func(t *testing.T) {
		updates := make(map[string]interface{})
		r.configureBatchUpdates(updates, []string{})
		if len(updates) != 0 {
			t.Errorf("expected empty map, got %d entries", len(updates))
		}
	})

	t.Run("mixed fields with LastModifiedDate", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{"Label", constants.FieldLastModifiedDate, "Type"}
		r.configureBatchUpdates(updates, fields)

		if len(updates) != 3 {
			t.Fatalf("expected 3 entries, got %d", len(updates))
		}

		// Label should use VALUES()
		labelRaw, ok := updates["Label"].(query.Raw)
		if !ok {
			t.Fatal("Label should be Raw")
		}
		if !strings.Contains(string(labelRaw), "VALUES") {
			t.Errorf("Label should use VALUES(), got %q", string(labelRaw))
		}

		// LastModifiedDate should use NOW()
		lmdRaw, ok := updates[constants.FieldLastModifiedDate].(query.Raw)
		if !ok {
			t.Fatal("LastModifiedDate should be Raw")
		}
		if string(lmdRaw) != "NOW()" {
			t.Errorf("LastModifiedDate should be NOW(), got %q", string(lmdRaw))
		}
	})
}

func TestConfigureObjectBatchUpdatesGuardsCustomizedObjectColumns(t *testing.T) {
	r := &SchemaRepository{db: nil}
	updates := make(map[string]interface{})
	fields := []string{
		constants.FieldSysObject_Label,
		constants.FieldSysObject_Description,
		constants.FieldSysObject_ListFields,
		constants.FieldSysObject_PathField,
		constants.FieldSysObject_SharingModel,
		constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_AccessPolicy,
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_TableType,
		constants.FieldLastModifiedDate,
	}

	r.configureObjectBatchUpdates(updates, fields)

	for _, field := range []string{
		constants.FieldSysObject_Label,
		constants.FieldSysObject_Description,
		constants.FieldSysObject_ListFields,
		constants.FieldSysObject_PathField,
		constants.FieldSysObject_SharingModel,
		constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_AccessPolicy,
	} {
		raw, ok := updates[field].(query.Raw)
		if !ok {
			t.Fatalf("%s should be query.Raw, got %T", field, updates[field])
		}
		expected := "IF(`IsCustomized` = 0 OR `IsCustomized` IS NULL, VALUES(`" + field + "`), `" + field + "`)"
		if string(raw) != expected {
			t.Errorf("%s update = %q, want %q", field, string(raw), expected)
		}
	}

	for _, field := range []string{
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_TableType,
	} {
		raw := string(updates[field].(query.Raw))
		expected := "VALUES(`" + field + "`)"
		if raw != expected {
			t.Errorf("%s update = %q, want %q", field, raw, expected)
		}
	}

	lastModifiedRaw := string(updates[constants.FieldLastModifiedDate].(query.Raw))
	expectedLastModified := "IF(`IsCustomized` = 0 OR `IsCustomized` IS NULL, NOW(), `LastModifiedDate`)"
	if lastModifiedRaw != expectedLastModified {
		t.Errorf("LastModifiedDate update = %q, want %q", lastModifiedRaw, expectedLastModified)
	}
}

// -----------------------------------------------------------------------
// configureBatchUpdatesGuarded  (schema_metadata_persist.go)
// -----------------------------------------------------------------------

func TestConfigureBatchUpdatesGuarded(t *testing.T) {
	r := &SchemaRepository{db: nil}

	t.Run("guarded fields wrapped in IF(IsCustomized=0 OR NULL)", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{
			constants.FieldSysField_Type,
			constants.FieldSysField_Required,
			constants.FieldSysField_DefaultValue,
			constants.FieldSysField_Options,
		}
		guarded := fields
		r.configureBatchUpdatesGuarded(updates, fields, guarded)

		for _, f := range fields {
			raw, ok := updates[f].(query.Raw)
			if !ok {
				t.Fatalf("updates[%q] should be query.Raw, got %T", f, updates[f])
			}
			expected := "IF(`IsCustomized` = 0 OR `IsCustomized` IS NULL, VALUES(`" + f + "`), `" + f + "`)"
			if string(raw) != expected {
				t.Errorf("updates[%q] = %q, want %q", f, string(raw), expected)
			}
		}
	})

	t.Run("non-guarded fields use plain VALUES()", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{
			constants.FieldSysField_Type,         // guarded
			constants.FieldSysField_HelpText,     // not guarded
			constants.FieldSysField_IsSystem,     // not guarded
			constants.FieldSysField_DefaultValue, // guarded
		}
		guarded := []string{
			constants.FieldSysField_Type,
			constants.FieldSysField_DefaultValue,
		}
		r.configureBatchUpdatesGuarded(updates, fields, guarded)

		for _, f := range []string{constants.FieldSysField_HelpText, constants.FieldSysField_IsSystem} {
			raw := string(updates[f].(query.Raw))
			expected := "VALUES(`" + f + "`)"
			if raw != expected {
				t.Errorf("non-guarded %q = %q, want %q", f, raw, expected)
			}
		}
		for _, f := range []string{constants.FieldSysField_Type, constants.FieldSysField_DefaultValue} {
			raw := string(updates[f].(query.Raw))
			if !strings.Contains(raw, "IF(`IsCustomized`") {
				t.Errorf("guarded %q missing IF guard: %q", f, raw)
			}
		}
	})

	t.Run("LastModifiedDate stays NOW() even if listed as guarded", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{constants.FieldLastModifiedDate}
		guarded := []string{constants.FieldLastModifiedDate}
		r.configureBatchUpdatesGuarded(updates, fields, guarded)

		raw := string(updates[constants.FieldLastModifiedDate].(query.Raw))
		if raw != "NOW()" {
			t.Errorf("LastModifiedDate = %q, want NOW()", raw)
		}
	})

	t.Run("empty guarded set behaves like configureBatchUpdates", func(t *testing.T) {
		updates := make(map[string]interface{})
		fields := []string{constants.FieldSysField_Type, constants.FieldSysField_HelpText}
		r.configureBatchUpdatesGuarded(updates, fields, nil)

		for _, f := range fields {
			raw := string(updates[f].(query.Raw))
			expected := "VALUES(`" + f + "`)"
			if raw != expected {
				t.Errorf("with empty guarded, %q = %q, want %q", f, raw, expected)
			}
		}
	})
}

// -----------------------------------------------------------------------
// PrepareFieldForBatch — extended  (schema_metadata_persist.go)
// -----------------------------------------------------------------------

func TestPrepareFieldForBatchExtended(t *testing.T) {
	r := &SchemaRepository{db: nil}

	t.Run("IDs left empty for batch generation", func(t *testing.T) {
		col := schema.ColumnDefinition{
			Name:     "Amount",
			Type:     "DECIMAL(18,6)",
			Nullable: true,
			Unique:   false,
		}

		result := r.PrepareFieldForBatch("Account", col)

		// IDs should be empty (left for BatchSaveFieldMetadata to generate)
		if result.ObjectID != "" {
			t.Errorf("ObjectID should be empty, got %q", result.ObjectID)
		}
		if result.FieldID != "" {
			t.Errorf("FieldID should be empty, got %q", result.FieldID)
		}
	})

	t.Run("preserves all column properties including reference", func(t *testing.T) {
		col := schema.ColumnDefinition{
			Name:        "AccountId",
			Type:        "VARCHAR(255)",
			LogicalType: "reference",
			Nullable:    false,
			Unique:      true,
			ReferenceTo: []string{"Account"},
			Formula:     "",
			ReturnType:  "",
		}

		result := r.PrepareFieldForBatch("Contact", col)

		if result.ColumnDefinition.LogicalType != "reference" {
			t.Errorf("LogicalType = %q, want reference", result.ColumnDefinition.LogicalType)
		}
		if !result.ColumnDefinition.Unique {
			t.Error("Unique should be true")
		}
		if result.ColumnDefinition.Nullable {
			t.Error("Nullable should be false")
		}
		if len(result.ColumnDefinition.ReferenceTo) != 1 || result.ColumnDefinition.ReferenceTo[0] != "Account" {
			t.Errorf("ReferenceTo = %v, want [Account]", result.ColumnDefinition.ReferenceTo)
		}
	})
}

// -----------------------------------------------------------------------
// remapPhysicalToLogical  (record_repository.go)
// -----------------------------------------------------------------------

func TestRemapPhysicalToLogical(t *testing.T) {
	r := &RecordRepository{db: nil}

	t.Run("maps physical to logical names", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{
				{APIName: "Name", PhysicalName: "name_col"},
				{APIName: "Email", PhysicalName: "email_addr"},
			},
		}

		record := models.SObject{
			"name_col":   "Alice",
			"email_addr": "alice@example.com",
		}

		got := r.remapPhysicalToLogical(record, schema)

		if got["Name"] != "Alice" {
			t.Errorf("Name = %v, want Alice", got["Name"])
		}
		if got["Email"] != "alice@example.com" {
			t.Errorf("Email = %v, want alice@example.com", got["Email"])
		}
	})

	t.Run("preserves keys not in schema", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{
				{APIName: "Name", PhysicalName: "Name"},
			},
		}

		record := models.SObject{
			"Name":          "Alice",
			"unknown_field": "some_value",
		}

		got := r.remapPhysicalToLogical(record, schema)

		if got["Name"] != "Alice" {
			t.Errorf("Name = %v, want Alice", got["Name"])
		}
		if got["unknown_field"] != "some_value" {
			t.Errorf("unknown_field should be preserved, got %v", got["unknown_field"])
		}
	})

	t.Run("case-insensitive matching", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{
				{APIName: "FirstName", PhysicalName: "firstname"},
			},
		}

		record := models.SObject{
			"firstname": "Bob",
		}

		got := r.remapPhysicalToLogical(record, schema)

		if got["FirstName"] != "Bob" {
			t.Errorf("FirstName = %v, want Bob", got["FirstName"])
		}
	})

	t.Run("id field normalized to constant", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{},
		}

		record := models.SObject{
			"id": "abc-123",
		}

		got := r.remapPhysicalToLogical(record, schema)

		if got[constants.FieldID] != "abc-123" {
			t.Errorf("Id = %v, want abc-123", got[constants.FieldID])
		}
	})

	t.Run("empty record", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{
				{APIName: "Name", PhysicalName: "name_col"},
			},
		}

		record := models.SObject{}
		got := r.remapPhysicalToLogical(record, schema)

		if len(got) != 0 {
			t.Errorf("expected empty result, got %d entries", len(got))
		}
	})

	t.Run("physical name same as api name", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{
				{APIName: "Amount", PhysicalName: "Amount"},
			},
		}

		record := models.SObject{
			"Amount": 42.5,
		}

		got := r.remapPhysicalToLogical(record, schema)

		if got["Amount"] != 42.5 {
			t.Errorf("Amount = %v, want 42.5", got["Amount"])
		}
	})

	t.Run("api name takes priority over physical name", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			Fields: []models.FieldMetadata{
				{APIName: "DisplayName", PhysicalName: "name_col"},
			},
		}

		// If both API and physical are in the record, both get mapped
		record := models.SObject{
			"displayname": "Via API",
			"name_col":    "Via Physical",
		}

		got := r.remapPhysicalToLogical(record, schema)

		// The last write wins since both map to DisplayName
		if got["DisplayName"] == nil {
			t.Fatal("DisplayName should have a value")
		}
	})
}

// -----------------------------------------------------------------------
// mapFieldToColumnDef  (schema_column_ops.go)
// -----------------------------------------------------------------------

func TestMapFieldToColumnDefExtended(t *testing.T) {
	r := &SchemaRepository{db: nil}

	t.Run("basic string field", func(t *testing.T) {
		field := models.FieldMetadata{
			APIName:  "Name",
			Type:     constants.FieldTypeString,
			Required: true,
			IsUnique: false,
		}

		col := r.mapFieldToColumnDef(field)

		if col.Name != "Name" {
			t.Errorf("Name = %q, want Name", col.Name)
		}
		if col.Nullable {
			t.Error("Required field should not be nullable")
		}
		if col.Unique {
			t.Error("IsUnique=false should mean Unique=false")
		}
		if col.Type != SQLTypeVarchar255 {
			t.Errorf("Type = %q, want %q", col.Type, SQLTypeVarchar255)
		}
	})

	t.Run("reference field with delete rule Cascade", func(t *testing.T) {
		dr := models.DeleteRule(constants.DeleteRuleCascade)
		field := models.FieldMetadata{
			APIName:     "AccountId",
			Type:        constants.FieldTypeReference,
			Required:    false,
			ReferenceTo: []string{"Account"},
			DeleteRule:  &dr,
		}

		col := r.mapFieldToColumnDef(field)

		if col.Type != SQLTypeVarchar255 {
			t.Errorf("Type = %q, want %q", col.Type, SQLTypeVarchar255)
		}
		if col.OnDelete != "CASCADE" {
			t.Errorf("OnDelete = %q, want CASCADE", col.OnDelete)
		}
		if len(col.ReferenceTo) != 1 || col.ReferenceTo[0] != "Account" {
			t.Errorf("ReferenceTo = %v, want [Account]", col.ReferenceTo)
		}
		if !col.Nullable {
			t.Error("Non-required field should be nullable")
		}
	})

	t.Run("reference field with delete rule SetNull", func(t *testing.T) {
		dr := models.DeleteRule(constants.DeleteRuleSetNull)
		field := models.FieldMetadata{
			APIName:     "OwnerId",
			Type:        constants.FieldTypeReference,
			ReferenceTo: []string{"User"},
			DeleteRule:  &dr,
		}

		col := r.mapFieldToColumnDef(field)

		if col.OnDelete != "SET NULL" {
			t.Errorf("OnDelete = %q, want SET NULL", col.OnDelete)
		}
	})

	t.Run("reference field with delete rule Restrict", func(t *testing.T) {
		dr := models.DeleteRule(constants.DeleteRuleRestrict)
		field := models.FieldMetadata{
			APIName:     "ParentId",
			Type:        constants.FieldTypeReference,
			ReferenceTo: []string{"Account"},
			DeleteRule:  &dr,
		}

		col := r.mapFieldToColumnDef(field)

		if col.OnDelete != "RESTRICT" {
			t.Errorf("OnDelete = %q, want RESTRICT", col.OnDelete)
		}
	})

	t.Run("field with default value escapes quotes", func(t *testing.T) {
		defaultVal := "O'Reilly"
		field := models.FieldMetadata{
			APIName:      "Publisher",
			Type:         constants.FieldTypeString,
			DefaultValue: &defaultVal,
		}

		col := r.mapFieldToColumnDef(field)

		if col.Default != "'O''Reilly'" {
			t.Errorf("Default = %q, want 'O''Reilly'", col.Default)
		}
	})

	t.Run("formula field", func(t *testing.T) {
		formulaExpr := "price * quantity"
		rt := models.FieldType("double")
		field := models.FieldMetadata{
			APIName:    "Total",
			Type:       constants.FieldTypeFormula,
			Formula:    &formulaExpr,
			ReturnType: &rt,
		}

		col := r.mapFieldToColumnDef(field)

		if col.Formula != "price * quantity" {
			t.Errorf("Formula = %q, want price * quantity", col.Formula)
		}
		if col.ReturnType != "double" {
			t.Errorf("ReturnType = %q, want double", col.ReturnType)
		}
	})

	t.Run("picklist field with options", func(t *testing.T) {
		field := models.FieldMetadata{
			APIName: "Status",
			Type:    constants.FieldTypePicklist,
			Options: models.PicklistOptionsFromStrings([]string{"Open", "Closed", "Pending"}),
		}

		col := r.mapFieldToColumnDef(field)

		if len(col.Options) != 3 {
			t.Errorf("Options length = %d, want 3", len(col.Options))
		}
	})

	t.Run("master-detail field", func(t *testing.T) {
		field := models.FieldMetadata{
			APIName:        "ParentId",
			Type:           constants.FieldTypeMasterDetail,
			IsMasterDetail: true,
			ReferenceTo:    []string{"Account"},
		}

		col := r.mapFieldToColumnDef(field)

		if !col.IsMasterDetail {
			t.Error("IsMasterDetail should be true")
		}
		if col.Type != SQLTypeVarchar255 {
			t.Errorf("Type = %q, want %q", col.Type, SQLTypeVarchar255)
		}
	})

	t.Run("field with relationship name", func(t *testing.T) {
		relName := "Account"
		field := models.FieldMetadata{
			APIName:          "AccountId",
			Type:             constants.FieldTypeReference,
			ReferenceTo:      []string{"Account"},
			RelationshipName: &relName,
		}

		col := r.mapFieldToColumnDef(field)

		if col.RelationshipName != "Account" {
			t.Errorf("RelationshipName = %q, want Account", col.RelationshipName)
		}
	})

	t.Run("field with nil pointers", func(t *testing.T) {
		field := models.FieldMetadata{
			APIName: "Simple",
			Type:    constants.FieldTypeString,
		}

		col := r.mapFieldToColumnDef(field)

		if col.Default != "" {
			t.Errorf("Default should be empty, got %q", col.Default)
		}
		if col.Formula != "" {
			t.Errorf("Formula should be empty, got %q", col.Formula)
		}
		if col.ReturnType != "" {
			t.Errorf("ReturnType should be empty, got %q", col.ReturnType)
		}
		if col.OnDelete != "" {
			t.Errorf("OnDelete should be empty, got %q", col.OnDelete)
		}
		if col.RelationshipName != "" {
			t.Errorf("RelationshipName should be empty, got %q", col.RelationshipName)
		}
	})
}

// -----------------------------------------------------------------------
// NormalizeSObject — additional edge cases  (schema_utils.go)
// -----------------------------------------------------------------------

func TestNormalizeSObject_CurrencyAndPercent(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Price", Type: constants.FieldTypeCurrency},
			{APIName: "Discount", Type: constants.FieldTypePercent},
		},
	}

	t.Run("currency string coercion", func(t *testing.T) {
		input := models.SObject{"Price": "199.99"}
		got := NormalizeSObject(schema, input)
		if got["Price"] != 199.99 {
			t.Errorf("Price = %v (%T), want 199.99 (float64)", got["Price"], got["Price"])
		}
	})

	t.Run("percent string coercion", func(t *testing.T) {
		input := models.SObject{"Discount": "15.5"}
		got := NormalizeSObject(schema, input)
		if got["Discount"] != 15.5 {
			t.Errorf("Discount = %v (%T), want 15.5 (float64)", got["Discount"], got["Discount"])
		}
	})

	t.Run("non-numeric string not coerced", func(t *testing.T) {
		input := models.SObject{"Price": "not-a-number"}
		got := NormalizeSObject(schema, input)
		if got["Price"] != "not-a-number" {
			t.Errorf("Price = %v, want 'not-a-number' (unchanged)", got["Price"])
		}
	})

	t.Run("float64 value not changed", func(t *testing.T) {
		input := models.SObject{"Price": 42.5}
		got := NormalizeSObject(schema, input)
		if got["Price"] != 42.5 {
			t.Errorf("Price = %v, want 42.5", got["Price"])
		}
	})
}

func TestNormalizeSObject_BooleanEdgeCases(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Active", Type: constants.FieldTypeBoolean},
		},
	}

	tests := []struct {
		name     string
		input    interface{}
		expected interface{}
	}{
		{"bool true passthrough", true, true},
		{"bool false passthrough", false, false},
		{"string 'true'", "true", true},
		{"string 'false'", "false", false},
		{"string other stays string", "yes", "yes"},
		{"int not converted (type mismatch)", 1, 1},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			input := models.SObject{"Active": tt.input}
			got := NormalizeSObject(schema, input)
			if got["Active"] != tt.expected {
				t.Errorf("Active = %v (%T), want %v (%T)", got["Active"], got["Active"], tt.expected, tt.expected)
			}
		})
	}
}

// -----------------------------------------------------------------------
// FromStorageRecord — polymorphic visible field filtering
// -----------------------------------------------------------------------

func TestFromStorageRecord_PolymorphicVisibleFields(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "WhatId", Type: constants.FieldTypeReference, IsPolymorphic: true},
			{APIName: "Name", Type: constants.FieldTypeString},
		},
	}

	typeCol := GetPolymorphicTypeColumnName("WhatId")

	record := models.SObject{
		"Id":     "task-1",
		"WhatId": "acct-1",
		typeCol:  "Account",
		"Name":   "Test Task",
	}

	got := FromStorageRecord(schema, record, []string{"WhatId"})

	// WhatId should be visible
	if got["WhatId"] != "acct-1" {
		t.Errorf("WhatId = %v, want acct-1", got["WhatId"])
	}

	// Polymorphic type column should be included
	if got[typeCol] != "Account" {
		t.Errorf("%s = %v, want Account", typeCol, got[typeCol])
	}

	// Id should always be present
	if got["Id"] != "task-1" {
		t.Errorf("Id = %v, want task-1", got["Id"])
	}

	// Name should be filtered out (not in visible fields)
	if _, exists := got["Name"]; exists {
		t.Error("Name should be filtered out by visibleFields")
	}
}

// -----------------------------------------------------------------------
// writeEscapedValue — additional type coverage
// -----------------------------------------------------------------------

func TestWriteEscapedValue_AdditionalTypes(t *testing.T) {
	tests := []struct {
		name     string
		input    interface{}
		expected string
	}{
		{
			name:     "zero time",
			input:    time.Time{},
			expected: "'0001-01-01 00:00:00'",
		},
		{
			name:     "int zero",
			input:    0,
			expected: "0",
		},
		{
			name:     "int64 zero",
			input:    int64(0),
			expected: "0",
		},
		{
			name:     "int64 negative",
			input:    int64(-42),
			expected: "-42",
		},
		{
			name:     "empty map",
			input:    map[string]interface{}{},
			expected: "'{}'",
		},
		{
			name:     "string with multiple special chars",
			input:    "it's a \"test\" with \\backslash",
			expected: "'it''s a \"test\" with \\\\backslash'",
		},
		{
			name:     "float64 very small",
			input:    0.000001,
			expected: "0.000001",
		},
		{
			name:     "float64 negative",
			input:    -99.5,
			expected: "-99.5",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			var sb strings.Builder
			writeEscapedValue(&sb, tt.input)
			got := sb.String()
			if got != tt.expected {
				t.Errorf("writeEscapedValue(%v) = %q, want %q", tt.input, got, tt.expected)
			}
		})
	}
}

// -----------------------------------------------------------------------
// isTypeCompatible — boundary edge cases
// -----------------------------------------------------------------------

func TestIsTypeCompatible_NullishCases(t *testing.T) {
	r := &SchemaRepository{db: nil}

	tests := []struct {
		name     string
		existing string
		expected string
		want     bool
	}{
		{"LONGTEXT vs TEXT compatible", "LONGTEXT", "TEXT", true},
		{"TEXT vs MEDIUMTEXT compatible", "TEXT", "MEDIUMTEXT", true},
		{"MEDIUMTEXT vs TEXT compatible", "MEDIUMTEXT", "TEXT", true},
		{"LONGTEXT vs MEDIUMTEXT compatible", "LONGTEXT", "MEDIUMTEXT", true},
		{"INT vs TINYINT not compatible", "INT", "TINYINT", false},
		{"TIMESTAMP vs DATETIME not exact base", "TIMESTAMP", "DATETIME", false},
		{"lowercase comparison", "varchar(50)", "varchar(255)", true},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := r.isTypeCompatible(tt.existing, tt.expected)
			if got != tt.want {
				t.Errorf("isTypeCompatible(%q, %q) = %v, want %v", tt.existing, tt.expected, got, tt.want)
			}
		})
	}
}

// -----------------------------------------------------------------------
// mapSQLTypeToLogical — coverage for SystemModstamp type
// -----------------------------------------------------------------------

func TestMapSQLTypeToLogical_SystemModstamp(t *testing.T) {
	r := &SchemaRepository{db: nil}

	// SystemModstamp column uses DATETIME, verify it maps correctly
	got := r.mapSQLTypeToLogical("DATETIME")
	if got != string(constants.FieldTypeDatetime) {
		t.Errorf("mapSQLTypeToLogical(DATETIME) = %q, want %q", got, string(constants.FieldTypeDatetime))
	}
}

// -----------------------------------------------------------------------
// ValidateFormula  (schema_ddl.go)
// -----------------------------------------------------------------------

func TestValidateFormula(t *testing.T) {
	r := &SchemaRepository{db: nil}

	t.Run("valid arithmetic formula", func(t *testing.T) {
		err := r.ValidateFormula("price * quantity", map[string]interface{}{
			"price":    1.0,
			"quantity": 1.0,
		})
		if err != nil {
			t.Errorf("ValidateFormula() unexpected error: %v", err)
		}
	})

	t.Run("valid formula with constants", func(t *testing.T) {
		err := r.ValidateFormula("amount * 0.1", map[string]interface{}{
			"amount": 100.0,
		})
		if err != nil {
			t.Errorf("ValidateFormula() unexpected error: %v", err)
		}
	})

	t.Run("empty formula", func(t *testing.T) {
		err := r.ValidateFormula("", nil)
		// An empty formula may or may not error depending on engine implementation
		// but it should not panic
		_ = err
	})
}

// -----------------------------------------------------------------------
// MapFieldTypeToSQL — error fallback path
// -----------------------------------------------------------------------

func TestMapFieldTypeToSQL_InvalidType(t *testing.T) {
	r := &SchemaRepository{db: nil}

	// Completely invalid type should return INVALID_TYPE_ERROR
	got := r.MapFieldTypeToSQL("COMPLETELY_INVALID_XYZ")
	if got != "INVALID_TYPE_ERROR" {
		t.Errorf("MapFieldTypeToSQL(invalid) = %q, want INVALID_TYPE_ERROR", got)
	}
}

func TestMapFieldTypeToSQL_AllLogicalTypes(t *testing.T) {
	r := &SchemaRepository{db: nil}

	validTypes := []struct {
		input string
		want  string
	}{
		{string(constants.FieldTypeString), SQLTypeVarchar255},
		{string(constants.FieldTypeTextarea), SQLTypeText},
		{string(constants.FieldTypeRichtextarea), SQLTypeLongText},
		{string(constants.FieldTypeDouble), "DECIMAL(65,30)"},
		{string(constants.FieldTypeCurrency), "DECIMAL(65,30)"},
		{string(constants.FieldTypePercent), "DECIMAL(65,30)"},
		{string(constants.FieldTypeBoolean), SQLTypeBoolean},
		{string(constants.FieldTypeDate), SQLTypeDate},
		{string(constants.FieldTypeDatetime), SQLTypeDateTime},
		{string(constants.FieldTypeEmail), SQLTypeVarchar255},
		{string(constants.FieldTypePhone), SQLTypeVarchar50},
		{string(constants.FieldTypeUrl), SQLTypeVarchar255},
		{string(constants.FieldTypePicklist), SQLTypeVarchar255},
		{string(constants.FieldTypeMultiPicklist), SQLTypeJSON},
		{string(constants.FieldTypeReference), SQLTypeVarchar255},
		{string(constants.FieldTypeMasterDetail), SQLTypeVarchar255},
		{string(constants.FieldTypeAutonumber), SQLTypeVarchar255},
		{string(constants.FieldTypeFormula), SQLTypeVarchar255},
		{string(constants.FieldTypePassword), SQLTypeText},
		{string(constants.FieldTypeEncryptedString), SQLTypeText},
		{string(constants.FieldTypeJson), SQLTypeJSON},
	}

	for _, tt := range validTypes {
		t.Run(tt.input, func(t *testing.T) {
			got := r.MapFieldTypeToSQL(tt.input)
			if got != tt.want {
				t.Errorf("MapFieldTypeToSQL(%q) = %q, want %q", tt.input, got, tt.want)
			}
		})
	}
}

// -----------------------------------------------------------------------
// buildColumnDDL — additional edge cases
// -----------------------------------------------------------------------

func TestBuildColumnDDL_LengthOverrideOnText(t *testing.T) {
	r := &SchemaRepository{db: nil}

	// Length override should work on TEXT type too
	col := schema.ColumnDefinition{
		Name:   "notes",
		Type:   "TEXT",
		Length: 500,
	}

	got := r.buildColumnDDL(col, false)

	if !strings.Contains(got, "VARCHAR(500)") {
		t.Errorf("buildColumnDDL with Length=500 on TEXT should produce VARCHAR(500), got: %q", got)
	}
}

func TestBuildColumnDDL_AllFlagsEnabled(t *testing.T) {
	r := &SchemaRepository{db: nil}

	col := schema.ColumnDefinition{
		Name:          "seq_id",
		Type:          "INT",
		Nullable:      false,
		Default:       "1",
		AutoIncrement: true,
		PrimaryKey:    true,
		Unique:        true,
	}

	got := r.buildColumnDDL(col, true)

	expected := []string{"NOT NULL", "DEFAULT 1", "AUTO_INCREMENT", "PRIMARY KEY", "UNIQUE"}
	for _, exp := range expected {
		if !strings.Contains(got, exp) {
			t.Errorf("buildColumnDDL() = %q, missing %q", got, exp)
		}
	}
}

// -----------------------------------------------------------------------
// buildIndexDDL — composite multi-column
// -----------------------------------------------------------------------

func TestBuildIndexDDL_MultipleColumns(t *testing.T) {
	r := &SchemaRepository{db: nil}

	idx := schema.IndexDefinition{
		Name:    "idx_compound",
		Columns: []string{"FirstName", "LastName", "Email"},
		Unique:  true,
	}

	got := r.buildIndexDDL("Contact", idx)

	if !strings.Contains(got, "UNIQUE KEY") {
		t.Errorf("expected UNIQUE KEY, got: %q", got)
	}
	if !strings.Contains(got, "`FirstName`, `LastName`, `Email`") {
		t.Errorf("expected all columns quoted, got: %q", got)
	}
}

// -----------------------------------------------------------------------
// convertFormulaToSQL — additional patterns
// -----------------------------------------------------------------------

func TestConvertFormulaToSQL_NestedFunctions(t *testing.T) {
	t.Skip("convertFormulaToSQL removed in TiDB Views branch")
}

// -----------------------------------------------------------------------
// extractBaseType — additional edge cases
// -----------------------------------------------------------------------

func TestExtractBaseType_EdgeCases(t *testing.T) {
	tests := []struct {
		name     string
		input    string
		expected string
	}{
		{"multiple parentheses", "FUNC(A,B)(C)", "FUNC"},
		{"space before paren", "TYPE (10)", "TYPE "},
		{"only opening paren", "VARCHAR(", "VARCHAR"},
		{"numbers in type name", "INT8", "INT8"},
		{"mixed case preserved after upper", "json", "JSON"},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := extractBaseType(tt.input)
			if got != tt.expected {
				t.Errorf("extractBaseType(%q) = %q, want %q", tt.input, got, tt.expected)
			}
		})
	}
}

// -----------------------------------------------------------------------
// Scannable interface — compile-time check (no DB needed)
// -----------------------------------------------------------------------

func TestScannableInterfaceDefined(t *testing.T) {
	// Ensure the Scannable type alias from metadata_scan_schema.go compiles.
	// This is a compile-time assertion only; it doesn't need a real scanner.
	var _ Scannable = (*fakeScanner)(nil)
}

type fakeScanner struct{}

func (f *fakeScanner) Scan(dest ...interface{}) error { return nil }

// -----------------------------------------------------------------------
// columnNameRegex — boundary patterns
// -----------------------------------------------------------------------

func TestColumnNameRegex_ExtendedPatterns(t *testing.T) {
	tests := []struct {
		name     string
		input    string
		expected bool
	}{
		{"double underscore prefix", "__internal", true},
		{"underscore suffix", "field_", true},
		{"single char", "x", true},
		{"single underscore", "_", true},
		{"unicode letter", "champ_ñ", false},
		{"tab char", "field\t", false},
		{"newline", "field\n", false},
		{"leading space", " field", false},
		{"trailing space", "field ", false},
		{"question mark", "field?", false},
		{"SQL keywords are valid identifiers", "SELECT", true},
		{"mixed alphanum", "abc123def456", true},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := columnNameRegex.MatchString(tt.input)
			if got != tt.expected {
				t.Errorf("columnNameRegex.MatchString(%q) = %v, want %v", tt.input, got, tt.expected)
			}
		})
	}
}