package persistence

import (
	"testing"

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

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

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

	tests := []struct {
		name     string
		input    string
		expected bool
	}{
		{"Id", constants.FieldID, true},
		{"CreatedDate", constants.FieldCreatedDate, true},
		{"LastModifiedDate", constants.FieldLastModifiedDate, true},
		{"CreatedById", constants.FieldCreatedByID, true},
		{"LastModifiedById", constants.FieldLastModifiedByID, true},
		{"OwnerId", constants.FieldOwnerID, true},
		{"IsDeleted", constants.FieldIsDeleted, true},
		{"Name", "Name", false},
		{"Amount", "Amount", false},
		{"custom_field__c", "custom_field__c", false},
		{"empty", "", false},
	}

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

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

	// Extended tests beyond schema_type_compat_test.go - focus on edge cases
	tests := []struct {
		name     string
		sqlType  string
		expected string
	}{
		// INTEGER (not covered in existing tests)
		{"INTEGER", "INTEGER", string(constants.FieldTypeDouble)},

		// NUMERIC (not covered)
		{"NUMERIC(10,2)", "NUMERIC(10,2)", string(constants.FieldTypeDouble)},

		// Unknown type fallback
		{"unknown type", "FOOBAR", string(constants.FieldTypeString)},
		{"empty string", "", string(constants.FieldTypeString)},
	}

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

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

	tests := []struct {
		name     string
		input    string
		expected string
	}{
		{"string", string(constants.FieldTypeString), SQLTypeVarchar255},
		{"boolean", string(constants.FieldTypeBoolean), SQLTypeBoolean},
		{"double", string(constants.FieldTypeDouble), "DECIMAL(65,30)"},
		{"date", string(constants.FieldTypeDate), SQLTypeDate},
		{"datetime", string(constants.FieldTypeDatetime), SQLTypeDateTime},
		{"reference", string(constants.FieldTypeReference), SQLTypeVarchar255},
		{"INT passthrough", "INT", "INT"},
		{"BIGINT passthrough", "BIGINT", "BIGINT"},
		{"invalid type", "COMPLETELY_INVALID_TYPE_XYZ", "INVALID_TYPE_ERROR"},
	}

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

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

	// Extended edge cases beyond schema_type_compat_test.go
	tests := []struct {
		name     string
		existing string
		expected string
		want     bool
	}{
		// Cross-family incompatibility
		{"BOOLEAN vs INT", "BOOLEAN", "INT", false},
		{"JSON vs TEXT", "JSON", "TEXT", false},
		{"DATE vs DATETIME", "DATE", "DATETIME", false},

		// Empty strings
		{"empty vs empty", "", "", true},
		{"empty vs INT", "", "INT", false},
	}

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

func TestGetStandardSystemColumns(t *testing.T) {
	// Use a nil db since GetStandardSystemColumns doesn't touch the database
	r := &SchemaRepository{db: nil}

	cols := r.GetStandardSystemColumns()

	// Verify minimum expected columns
	expectedNames := []string{
		constants.FieldID,
		constants.FieldOwnerID,
		constants.FieldCreatedByID,
		constants.FieldLastModifiedByID,
		constants.FieldCreatedDate,
		constants.FieldLastModifiedDate,
		constants.FieldIsDeleted,
	}

	if len(cols) < len(expectedNames) {
		t.Fatalf("GetStandardSystemColumns() returned %d columns, expected at least %d", len(cols), len(expectedNames))
	}

	colNames := make(map[string]bool)
	for _, col := range cols {
		colNames[col.Name] = true
	}

	for _, name := range expectedNames {
		if !colNames[name] {
			t.Errorf("GetStandardSystemColumns() missing expected column %q", name)
		}
	}

	// Verify Id is primary key
	for _, col := range cols {
		if col.Name == constants.FieldID {
			if !col.PrimaryKey {
				t.Error("Id column should be primary key")
			}
			if col.Nullable {
				t.Error("Id column should not be nullable")
			}
		}
	}

	// Verify IsDeleted defaults to "0"
	for _, col := range cols {
		if col.Name == constants.FieldIsDeleted {
			if col.Default != "0" {
				t.Errorf("IsDeleted default should be '0', got %q", col.Default)
			}
			if col.Nullable {
				t.Error("IsDeleted should not be nullable")
			}
		}
	}
}

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

	fields := r.GetStandardFieldMetadata()

	if len(fields) == 0 {
		t.Fatal("GetStandardFieldMetadata() returned empty slice")
	}

	// Build a lookup by APIName
	fieldMap := make(map[string]models.FieldMetadata)
	for _, f := range fields {
		fieldMap[f.APIName] = f
	}

	// Check Id field
	if idField, ok := fieldMap[constants.FieldID]; ok {
		if idField.Label != "ID" {
			t.Errorf("Id field label = %q, want 'ID'", idField.Label)
		}
		if !idField.IsSystem {
			t.Error("Id field should be marked as system")
		}
	} else {
		t.Error("GetStandardFieldMetadata() missing Id field")
	}

	// Check OwnerId field should have reference type
	if ownerField, ok := fieldMap[constants.FieldOwnerID]; ok {
		if ownerField.Type != constants.FieldTypeReference {
			t.Errorf("OwnerId type = %q, want reference", ownerField.Type)
		}
		if len(ownerField.ReferenceTo) == 0 || ownerField.ReferenceTo[0] != constants.TableUser {
			t.Errorf("OwnerId ReferenceTo should contain %q", constants.TableUser)
		}
	} else {
		t.Error("GetStandardFieldMetadata() missing OwnerId field")
	}

	// Check CreatedDate field
	if cdField, ok := fieldMap[constants.FieldCreatedDate]; ok {
		if cdField.Label != "Created Date" {
			t.Errorf("CreatedDate label = %q, want 'Created Date'", cdField.Label)
		}
	} else {
		t.Error("GetStandardFieldMetadata() missing CreatedDate field")
	}

	// Fields recognized by IsSystemColumn should be marked IsSystem.
	// Note: SystemModstamp is in GetStandardSystemColumns but not in IsSystemColumn's list,
	// so it won't be marked IsSystem. We verify the known system fields explicitly instead.
	knownSystemFields := map[string]bool{
		constants.FieldID: true, constants.FieldCreatedDate: true,
		constants.FieldLastModifiedDate: true, constants.FieldCreatedByID: true,
		constants.FieldLastModifiedByID: true, constants.FieldOwnerID: true,
		constants.FieldIsDeleted: true,
	}
	for _, f := range fields {
		if knownSystemFields[f.APIName] && !f.IsSystem {
			t.Errorf("Standard field %q should be IsSystem=true", f.APIName)
		}
	}
}