package persistence

import (
	"testing"

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

func TestResolvePhysicalName(t *testing.T) {
	tests := []struct {
		name     string
		schema   *models.ObjectMetadata
		apiName  string
		expected string
	}{
		{
			name:     "nil schema returns original name",
			schema:   nil,
			apiName:  "Name",
			expected: "Name",
		},
		{
			name: "exact match with physical name",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "Name", PhysicalName: "name_col"},
				},
			},
			apiName:  "Name",
			expected: "name_col",
		},
		{
			name: "case-insensitive match",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "Name", PhysicalName: "name_col"},
				},
			},
			apiName:  "name",
			expected: "name_col",
		},
		{
			name: "match without physical name returns API name",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "Email", PhysicalName: ""},
				},
			},
			apiName:  "Email",
			expected: "Email",
		},
		{
			name: "backticked alias metadata falls back to API name",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "created_date", PhysicalName: "`CreatedDate`"},
				},
			},
			apiName:  "created_date",
			expected: "created_date",
		},
		{
			name: "no match returns original name",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "Name", PhysicalName: "name_col"},
				},
			},
			apiName:  "NotInSchema",
			expected: "NotInSchema",
		},
		{
			name: "empty fields returns original name",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{},
			},
			apiName:  "SomeField",
			expected: "SomeField",
		},
	}

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

func TestNewFieldMapper(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Amount", PhysicalName: "amount_col"},
			{APIName: "Name", PhysicalName: ""},
		},
	}

	mapper := newFieldMapper(schema)

	tests := []struct {
		input    string
		expected string
	}{
		{"Amount", "amount_col"},
		{"amount", "amount_col"},
		{"Name", "Name"},
		{"Unknown", "Unknown"},
	}

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

	// nil schema mapper
	nilMapper := newFieldMapper(nil)
	if got := nilMapper("anything"); got != "anything" {
		t.Errorf("nil schema mapper(%q) = %q, want %q", "anything", got, "anything")
	}
}

func TestSchemaHasIsDeleted(t *testing.T) {
	tests := []struct {
		name     string
		schema   *models.ObjectMetadata
		expected bool
	}{
		{
			name: "has IsDeleted field",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: constants.FieldIsDeleted},
				},
			},
			expected: true,
		},
		{
			name: "has IsDeleted field case-insensitive",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "isdeleted"},
				},
			},
			expected: true,
		},
		{
			name: "no IsDeleted field",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{
					{APIName: "Name"},
					{APIName: "Email"},
				},
			},
			expected: false,
		},
		{
			name: "empty fields",
			schema: &models.ObjectMetadata{
				Fields: []models.FieldMetadata{},
			},
			expected: false,
		},
	}

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

func TestIsValidFieldNameExtended(t *testing.T) {
	// Extended test cases beyond query_repository_test.go
	tests := []struct {
		name     string
		input    string
		expected bool
	}{
		{"simple name", "Name", true},
		{"underscore name", "first_name", true},
		{"with dot", "t.Name", true},
		{"numeric suffix", "field1", true},
		{"all uppercase", "ABC", true},
		{"quote", "field'name", false},
		{"hyphen", "field-name", false},
		{"parentheses", "field()", false},
		{"starts with number", "1field", true},
		{"underscore only", "_", true},
		{"double underscore", "__c", true},
		{"at sign", "field@name", false},
		{"equals sign", "field=value", false},
		{"curly brace", "field{}", false},
		{"tab character", "field\tname", false},
	}

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

func TestIsValidFilterOp(t *testing.T) {
	tests := []struct {
		op       string
		expected bool
	}{
		{"=", true},
		{"!=", true},
		{"<", true},
		{">", true},
		{"<=", true},
		{">=", true},
		{"LIKE", true},
		{"like", true},
		{"Like", true},
		{"IN", true},
		{"in", true},
		{"BETWEEN", false},
		{"DROP", false},
		{"", false},
		{"==", false},
	}

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

func TestReplaceIdentifiers(t *testing.T) {
	tests := []struct {
		name         string
		sql          string
		replacements map[string]string
		expected     string
	}{
		{
			name:         "simple replacement",
			sql:          "SELECT name FROM table",
			replacements: map[string]string{"name": "`t`.`name_col`"},
			expected:     "SELECT `t`.`name_col` FROM table",
		},
		{
			name:         "case insensitive replacement",
			sql:          "SELECT Name FROM table",
			replacements: map[string]string{"name": "`t`.`name_col`"},
			expected:     "SELECT `t`.`name_col` FROM table",
		},
		{
			name:         "no replacement inside single quotes",
			sql:          "SELECT name FROM table WHERE val = 'name'",
			replacements: map[string]string{"name": "`t`.`name_col`"},
			expected:     "SELECT `t`.`name_col` FROM table WHERE val = 'name'",
		},
		{
			name:         "no replacement inside double quotes",
			sql:          `SELECT name FROM table WHERE val = "name"`,
			replacements: map[string]string{"name": "`t`.`name_col`"},
			expected:     `SELECT ` + "`t`.`name_col`" + ` FROM table WHERE val = "name"`,
		},
		{
			name:         "no replacement inside backticks",
			sql:          "SELECT `name` FROM table",
			replacements: map[string]string{"name": "`t`.`name_col`"},
			expected:     "SELECT `name` FROM table",
		},
		{
			name:         "multiple replacements",
			sql:          "name + amount",
			replacements: map[string]string{"name": "`t`.`n`", "amount": "`t`.`a`"},
			expected:     "`t`.`n` + `t`.`a`",
		},
		{
			name:         "empty replacements",
			sql:          "SELECT name FROM table",
			replacements: map[string]string{},
			expected:     "SELECT name FROM table",
		},
		{
			name:         "empty SQL",
			sql:          "",
			replacements: map[string]string{"name": "col"},
			expected:     "",
		},
		{
			name:         "identifier at end of string",
			sql:          "SELECT name",
			replacements: map[string]string{"name": "col"},
			expected:     "SELECT col",
		},
	}

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

func TestBuildFieldReplacements(t *testing.T) {
	tests := []struct {
		name     string
		schema   *models.ObjectMetadata
		expected map[string]string
	}{
		{
			name: "fields with physical names",
			schema: &models.ObjectMetadata{
				APIName: "Account",
				Fields: []models.FieldMetadata{
					{APIName: "Name", PhysicalName: "name_col"},
					{APIName: "Email", PhysicalName: "email_col"},
				},
			},
			expected: map[string]string{
				"name":  "`Account`.`name_col`",
				"email": "`Account`.`email_col`",
			},
		},
		{
			name: "fields where physical equals api name are excluded",
			schema: &models.ObjectMetadata{
				APIName: "Account",
				Fields: []models.FieldMetadata{
					{APIName: "Name", PhysicalName: "Name"},
					{APIName: "Email", PhysicalName: "email_col"},
				},
			},
			expected: map[string]string{
				"email": "`Account`.`email_col`",
			},
		},
		{
			name: "empty physical name excluded",
			schema: &models.ObjectMetadata{
				APIName: "Account",
				Fields: []models.FieldMetadata{
					{APIName: "Name", PhysicalName: ""},
				},
			},
			expected: map[string]string{},
		},
		{
			name: "empty fields",
			schema: &models.ObjectMetadata{
				APIName: "Account",
				Fields:  []models.FieldMetadata{},
			},
			expected: map[string]string{},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := buildFieldReplacements(tt.schema)
			if len(got) != len(tt.expected) {
				t.Errorf("buildFieldReplacements() returned %d entries, want %d", len(got), len(tt.expected))
				return
			}
			for k, v := range tt.expected {
				if got[k] != v {
					t.Errorf("buildFieldReplacements()[%q] = %q, want %q", k, got[k], v)
				}
			}
		})
	}
}

func TestBuildQualifiedFieldReplacements(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "Account",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID},
			{APIName: "Name"},
			{APIName: "AnnualRevenue", PhysicalName: "annual_revenue"},
		},
	}

	got := buildQualifiedFieldReplacements(schema, "Account")

	expected := map[string]string{
		"id":             "`Account`.`Id`",
		"name":           "`Account`.`Name`",
		"annualrevenue":  "`Account`.`annual_revenue`",
		"annual_revenue": "`Account`.`annual_revenue`",
	}

	if len(got) != len(expected) {
		t.Fatalf("buildQualifiedFieldReplacements() returned %d entries, want %d", len(got), len(expected))
	}
	for k, v := range expected {
		if got[k] != v {
			t.Errorf("buildQualifiedFieldReplacements()[%q] = %q, want %q", k, got[k], v)
		}
	}
}

func TestReplaceIdentifiers_QualifiesMRUFilterColumns(t *testing.T) {
	sql := "(Id IN (?, ?)) AND (Name = ?)"
	replacements := buildQualifiedFieldReplacements(&models.ObjectMetadata{
		APIName: "Account",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID},
			{APIName: "Name"},
		},
	}, "Account")

	got := replaceIdentifiers(sql, replacements)

	if got != "(`Account`.`Id` IN (?, ?)) AND (`Account`.`Name` = ?)" {
		t.Errorf("replaceIdentifiers() = %q, want %q", got, "(`Account`.`Id` IN (?, ?)) AND (`Account`.`Name` = ?)")
	}
}