package persistence

import (
	"database/sql"
	"testing"
	"time"

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

func TestFindField(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Name"},
			{APIName: "Email"},
			{APIName: "Phone"},
		},
	}

	tests := []struct {
		name      string
		schema    *models.ObjectMetadata
		fieldName string
		wantNil   bool
		wantAPI   string
	}{
		{"exact match", schema, "Name", false, "Name"},
		{"case insensitive", schema, "name", false, "Name"},
		{"case insensitive upper", schema, "EMAIL", false, "Email"},
		{"not found", schema, "Address", true, ""},
		{"nil schema", nil, "Name", true, ""},
		{"nil fields", &models.ObjectMetadata{}, "Name", true, ""},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := FindField(tt.schema, tt.fieldName)
			if tt.wantNil {
				if got != nil {
					t.Errorf("FindField() = %v, want nil", got)
				}
				return
			}
			if got == nil {
				t.Fatalf("FindField() = nil, want non-nil")
			}
			if got.APIName != tt.wantAPI {
				t.Errorf("FindField().APIName = %q, want %q", got.APIName, tt.wantAPI)
			}
		})
	}
}

func TestFindFieldWithFieldsMap(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Name"},
			{APIName: "Email"},
		},
		FieldsMap: map[string]*models.FieldMetadata{
			"name":  {APIName: "Name"},
			"email": {APIName: "Email"},
		},
	}

	tests := []struct {
		name      string
		fieldName string
		wantNil   bool
		wantAPI   string
	}{
		{"map lookup exact", "name", false, "Name"},
		{"map lookup email", "email", false, "Email"},
		{"map miss", "phone", true, ""},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := FindField(schema, tt.fieldName)
			if tt.wantNil {
				if got != nil {
					t.Errorf("FindField() = %v, want nil", got)
				}
				return
			}
			if got == nil {
				t.Fatalf("FindField() = nil, want non-nil")
			}
			if got.APIName != tt.wantAPI {
				t.Errorf("FindField().APIName = %q, want %q", got.APIName, tt.wantAPI)
			}
		})
	}
}

func TestGetPolymorphicTypeColumnName(t *testing.T) {
	tests := []struct {
		input    string
		expected string
	}{
		{"WhatId", "WhatId" + constants.PolymorphicTypeSuffix},
		{"WhoId", "WhoId" + constants.PolymorphicTypeSuffix},
		{"OwnerId", "OwnerId" + constants.PolymorphicTypeSuffix},
		{"", constants.PolymorphicTypeSuffix},
	}

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

func TestToNullString(t *testing.T) {
	tests := []struct {
		name     string
		input    *string
		expected sql.NullString
	}{
		{
			name:     "nil pointer",
			input:    nil,
			expected: sql.NullString{Valid: false},
		},
		{
			name:     "valid string",
			input:    strPtr("hello"),
			expected: sql.NullString{String: "hello", Valid: true},
		},
		{
			name:     "empty string",
			input:    strPtr(""),
			expected: sql.NullString{String: "", Valid: true},
		},
	}

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

func TestToNullInt64(t *testing.T) {
	intVal := 42
	int64Val := int64(100)

	tests := []struct {
		name      string
		input     interface{}
		wantValid bool
		wantInt64 int64
	}{
		{"nil interface", nil, false, 0},
		{"*int pointer", &intVal, true, 42},
		{"*int64 pointer", &int64Val, true, 100},
		{"int value", 10, true, 10},
		{"nil *int", (*int)(nil), false, 0},
		{"nil *int64", (*int64)(nil), false, 0},
		{"unsupported type string", "hello", false, 0},
		{"unsupported type float", 3.14, false, 0},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := ToNullInt64(tt.input)
			if got.Valid != tt.wantValid {
				t.Errorf("ToNullInt64().Valid = %v, want %v", got.Valid, tt.wantValid)
			}
			if got.Valid && got.Int64 != tt.wantInt64 {
				t.Errorf("ToNullInt64().Int64 = %d, want %d", got.Int64, tt.wantInt64)
			}
		})
	}
}

func TestSliceToNullJSON(t *testing.T) {
	tests := []struct {
		name      string
		input     []string
		wantValid bool
		wantJSON  string
	}{
		{"nil slice", nil, false, ""},
		{"empty slice", []string{}, false, ""},
		{"single element", []string{"Account"}, true, `["Account"]`},
		{"multiple elements", []string{"Account", "Contact"}, true, `["Account","Contact"]`},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := SliceToNullJSON(tt.input)
			if got.Valid != tt.wantValid {
				t.Errorf("SliceToNullJSON().Valid = %v, want %v", got.Valid, tt.wantValid)
			}
			if got.Valid && got.String != tt.wantJSON {
				t.Errorf("SliceToNullJSON().String = %q, want %q", got.String, tt.wantJSON)
			}
		})
	}
}

func TestParseDBTime(t *testing.T) {
	tests := []struct {
		name    string
		input   string
		wantErr bool
		wantY   int
		wantM   time.Month
		wantD   int
	}{
		{
			name:  "SQL format",
			input: "2024-06-15 10:30:45",
			wantY: 2024, wantM: time.June, wantD: 15,
		},
		{
			name:  "RFC3339 format",
			input: "2024-06-15T10:30:45Z",
			wantY: 2024, wantM: time.June, wantD: 15,
		},
		{
			name:    "invalid format",
			input:   "not-a-date",
			wantErr: true,
		},
		{
			name:    "empty string",
			input:   "",
			wantErr: true,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got, err := ParseDBTime([]byte(tt.input))
			if (err != nil) != tt.wantErr {
				t.Errorf("ParseDBTime() error = %v, wantErr %v", err, tt.wantErr)
				return
			}
			if !tt.wantErr {
				if got.Year() != tt.wantY || got.Month() != tt.wantM || got.Day() != tt.wantD {
					t.Errorf("ParseDBTime() = %v, want year=%d month=%v day=%d", got, tt.wantY, tt.wantM, tt.wantD)
				}
			}
		})
	}
}

func TestNormalizeSObject(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Name", Type: constants.FieldTypeString},
			{APIName: "Amount", Type: constants.FieldTypeDouble},
			{APIName: "IsActive", Type: constants.FieldTypeBoolean},
			{APIName: "Data", Type: constants.FieldTypeJson},
		},
	}

	tests := []struct {
		name     string
		input    models.SObject
		checkKey string
		checkVal interface{}
	}{
		{
			name:     "string field preserved",
			input:    models.SObject{"Name": "Test"},
			checkKey: "Name",
			checkVal: "Test",
		},
		{
			name:     "double from string coerced",
			input:    models.SObject{"Amount": "42.5"},
			checkKey: "Amount",
			checkVal: 42.5,
		},
		{
			name:     "double from empty string not coerced",
			input:    models.SObject{"Amount": ""},
			checkKey: "Amount",
			checkVal: "",
		},
		{
			name:     "boolean true from string",
			input:    models.SObject{"IsActive": "true"},
			checkKey: "IsActive",
			checkVal: true,
		},
		{
			name:     "boolean false from string",
			input:    models.SObject{"IsActive": "false"},
			checkKey: "IsActive",
			checkVal: false,
		},
		{
			name:     "nil value preserved",
			input:    models.SObject{"Amount": nil},
			checkKey: "Amount",
			checkVal: nil,
		},
		{
			name:     "unknown field passed through",
			input:    models.SObject{"Unknown": "value"},
			checkKey: "Unknown",
			checkVal: "value",
		},
		{
			name:     "case insensitive field matching",
			input:    models.SObject{"name": "Test"},
			checkKey: "Name",
			checkVal: "Test",
		},
		{
			name:     "json map marshaled to string",
			input:    models.SObject{"Data": map[string]interface{}{"key": "val"}},
			checkKey: "Data",
			checkVal: `{"key":"val"}`,
		},
		{
			name:     "json array marshaled to string",
			input:    models.SObject{"Data": []interface{}{"a", "b"}},
			checkKey: "Data",
			checkVal: `["a","b"]`,
		},
		// Issue #585: plain string in JSON-typed column must be coerced to valid JSON
		// Before the fix, this falls through the switch and passes the raw string to TiDB,
		// causing Error 3140 (Invalid JSON text).
		// Note: json.Marshal HTML-escapes < > & to \u003c etc. for safety — the result is
		// still valid JSON and TiDB accepts it; FromStorageRecord decodes it back correctly.
		{
			name:     "json plain text string wrapped as JSON string literal",
			input:    models.SObject{"Data": "plain text content"},
			checkKey: "Data",
			checkVal: `"plain text content"`,
		},
		{
			name:     "json already-valid JSON object string passed through unchanged",
			input:    models.SObject{"Data": `{"key":"val"}`},
			checkKey: "Data",
			checkVal: `{"key":"val"}`,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := NormalizeSObject(schema, tt.input)
			val, exists := got[tt.checkKey]
			if !exists {
				t.Fatalf("NormalizeSObject() result missing key %q, got keys: %v", tt.checkKey, keysOf(got))
			}
			if val != tt.checkVal {
				t.Errorf("NormalizeSObject()[%q] = %v (%T), want %v (%T)", tt.checkKey, val, val, tt.checkVal, tt.checkVal)
			}
		})
	}
}

func TestFromStorageRecord(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			{APIName: "IsActive", Type: constants.FieldTypeBoolean},
			{APIName: "Amount", Type: constants.FieldTypeDouble},
			{APIName: "Price", Type: constants.FieldTypeCurrency},
			{APIName: "Rate", Type: constants.FieldTypePercent},
			{APIName: "CloseDate", Type: constants.FieldTypeDate},
			{APIName: "Data", Type: constants.FieldTypeJson},
			{APIName: "Name", Type: constants.FieldTypeString, PhysicalName: "name_col"},
		},
	}

	t.Run("boolean from int64", func(t *testing.T) {
		record := models.SObject{"IsActive": int64(1), "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["IsActive"] != true {
			t.Errorf("expected true, got %v (%T)", got["IsActive"], got["IsActive"])
		}
	})

	t.Run("boolean from int64 zero", func(t *testing.T) {
		record := models.SObject{"IsActive": int64(0), "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["IsActive"] != false {
			t.Errorf("expected false, got %v (%T)", got["IsActive"], got["IsActive"])
		}
	})

	t.Run("boolean from int", func(t *testing.T) {
		record := models.SObject{"IsActive": int(1), "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["IsActive"] != true {
			t.Errorf("expected true, got %v (%T)", got["IsActive"], got["IsActive"])
		}
	})

	t.Run("boolean from bool passthrough", func(t *testing.T) {
		record := models.SObject{"IsActive": true, "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["IsActive"] != true {
			t.Errorf("expected true, got %v (%T)", got["IsActive"], got["IsActive"])
		}
	})

	t.Run("boolean from []uint8", func(t *testing.T) {
		record := models.SObject{"IsActive": []uint8{1}, "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["IsActive"] != true {
			t.Errorf("expected true, got %v (%T)", got["IsActive"], got["IsActive"])
		}
	})

	t.Run("boolean from string 1", func(t *testing.T) {
		record := models.SObject{"IsActive": "1", "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["IsActive"] != true {
			t.Errorf("expected true, got %v (%T)", got["IsActive"], got["IsActive"])
		}
	})

	t.Run("numeric from []uint8", func(t *testing.T) {
		record := models.SObject{"Amount": []uint8("42.5"), "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["Amount"] != 42.5 {
			t.Errorf("expected 42.5, got %v (%T)", got["Amount"], got["Amount"])
		}
	})

	t.Run("numeric from string", func(t *testing.T) {
		record := models.SObject{"Amount": "99.9", "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["Amount"] != 99.9 {
			t.Errorf("expected 99.9, got %v (%T)", got["Amount"], got["Amount"])
		}
	})

	t.Run("currency from string", func(t *testing.T) {
		record := models.SObject{"Price": "100.50", "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["Price"] != 100.50 {
			t.Errorf("expected 100.5, got %v (%T)", got["Price"], got["Price"])
		}
	})

	t.Run("percent from bytes", func(t *testing.T) {
		record := models.SObject{"Rate": []uint8("75.5"), "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["Rate"] != 75.5 {
			t.Errorf("expected 75.5, got %v (%T)", got["Rate"], got["Rate"])
		}
	})

	t.Run("json from string", func(t *testing.T) {
		record := models.SObject{"Data": `{"key":"val"}`, "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		m, ok := got["Data"].(map[string]interface{})
		if !ok {
			t.Fatalf("expected map, got %T", got["Data"])
		}
		if m["key"] != "val" {
			t.Errorf("expected key=val, got %v", m["key"])
		}
	})

	t.Run("json from []uint8", func(t *testing.T) {
		record := models.SObject{"Data": []uint8(`["a","b"]`), "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		arr, ok := got["Data"].([]interface{})
		if !ok {
			t.Fatalf("expected []interface{}, got %T", got["Data"])
		}
		if len(arr) != 2 {
			t.Errorf("expected 2 elements, got %d", len(arr))
		}
	})

	t.Run("date from time retains calendar day", func(t *testing.T) {
		record := models.SObject{
			"CloseDate": time.Date(2026, time.December, 30, 0, 0, 0, 0, time.FixedZone("CST", 8*60*60)),
			"Id":        "abc",
		}
		got := FromStorageRecord(schema, record, nil)
		if got["CloseDate"] != "2026-12-30" {
			t.Errorf("expected 2026-12-30, got %v (%T)", got["CloseDate"], got["CloseDate"])
		}
	})

	t.Run("date from rfc3339 string trims to date only", func(t *testing.T) {
		record := models.SObject{"CloseDate": "2026-12-30T00:00:00+08:00", "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		if got["CloseDate"] != "2026-12-30" {
			t.Errorf("expected 2026-12-30, got %v (%T)", got["CloseDate"], got["CloseDate"])
		}
	})

	t.Run("visible fields filter", func(t *testing.T) {
		record := models.SObject{"Name": "Test", "Amount": "42.5", "Id": "abc"}
		got := FromStorageRecord(schema, record, []string{"Name"})
		if _, exists := got["Amount"]; exists {
			t.Error("Amount should be filtered out by visibleFields")
		}
		if got["Name"] != "Test" {
			t.Errorf("Name should be preserved, got %v", got["Name"])
		}
		// Id is always preserved
		if got["Id"] != "abc" {
			t.Errorf("Id should always be preserved, got %v", got["Id"])
		}
	})

	t.Run("physical name aliasing", func(t *testing.T) {
		record := models.SObject{"name_col": "Test", "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		// The function should alias name_col to Name
		if got["Name"] != "Test" {
			t.Errorf("expected Name='Test' via physical alias, got %v", got["Name"])
		}
	})

	t.Run("nil value skipped", func(t *testing.T) {
		record := models.SObject{"Amount": nil, "Id": "abc"}
		got := FromStorageRecord(schema, record, nil)
		// nil values should not be type-converted
		if _, exists := got["Amount"]; exists {
			if got["Amount"] != nil {
				t.Errorf("nil value should remain nil, got %v", got["Amount"])
			}
		}
	})
}

// Helper to get string pointer
func strPtr(s string) *string {
	return &s
}

// Helper to get keys of a map
func keysOf(m models.SObject) []string {
	keys := make([]string, 0, len(m))
	for k := range m {
		keys = append(keys, k)
	}
	return keys
}