package persistence

import (
	"strings"
	"testing"

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

func TestVirtualCompoundFieldMapperUsesComponentExpression(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: constants.TableUser,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: constants.FieldFirstName, Type: constants.FieldTypeString},
			{APIName: constants.FieldLastName, Type: constants.FieldTypeString},
			{
				APIName:      constants.FieldName,
				Type:         constants.FieldTypeFormula,
				IsVirtual:    true,
				VirtualKind:  constants.VirtualFieldCompound,
				IsCompound:   true,
				ComposedFrom: []string{constants.FieldFirstName, constants.FieldLastName},
			},
		},
	}

	sqlWhere, args, err := formula.ToSQLWithMapper(
		"Name != null",
		newFieldMapperForTable(schema, constants.TableUser),
	)
	if err != nil {
		t.Fatalf("ToSQLWithMapper: %v", err)
	}
	if len(args) != 0 {
		t.Fatalf("args = %#v, want none", args)
	}
	if !strings.Contains(sqlWhere, "CONCAT_WS") {
		t.Fatalf("sqlWhere = %q, want compound CONCAT_WS expression", sqlWhere)
	}
	if !strings.Contains(sqlWhere, "`User`.`FirstName`") || !strings.Contains(sqlWhere, "`User`.`LastName`") {
		t.Fatalf("sqlWhere = %q, want qualified component columns", sqlWhere)
	}
	if strings.Contains(sqlWhere, "`User`.`Name`") {
		t.Fatalf("sqlWhere = %q, must not reference physical User.Name", sqlWhere)
	}
	if !strings.Contains(sqlWhere, "IS NOT NULL") {
		t.Fatalf("sqlWhere = %q, want null comparison rewrite", sqlWhere)
	}
}

func TestSOQLDateTimeLiteralFilterLowersToMappedSQL(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "Account",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: constants.FieldCreatedDate, Type: constants.FieldTypeDatetime},
		},
	}

	sqlWhere, args, err := formula.ToSQLWithMapper(
		"CreatedDate >= '2026-06-01 04:05:06'",
		newFieldMapperForTable(schema, "Account"),
	)
	if err != nil {
		t.Fatalf("ToSQLWithMapper: %v", err)
	}
	if !strings.Contains(sqlWhere, "CreatedDate >= ?") {
		t.Fatalf("sqlWhere = %q, want CreatedDate predicate", sqlWhere)
	}
	if len(args) != 1 || args[0] != "2026-06-01 04:05:06" {
		t.Fatalf("args = %#v, want normalized datetime arg", args)
	}
}

func TestSearchFieldSQLExpressionUsesVirtualCompoundComponents(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: constants.TableUser,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: constants.FieldFirstName, Type: constants.FieldTypeString},
			{APIName: constants.FieldLastName, Type: constants.FieldTypeString},
			{
				APIName:      constants.FieldName,
				Type:         constants.FieldTypeFormula,
				IsVirtual:    true,
				VirtualKind:  constants.VirtualFieldCompound,
				IsCompound:   true,
				ComposedFrom: []string{constants.FieldFirstName, constants.FieldLastName},
			},
		},
	}

	expr, err := searchFieldSQLExpression(schema, constants.FieldName, constants.TableUser)
	if err != nil {
		t.Fatalf("searchFieldSQLExpression: %v", err)
	}
	if !strings.Contains(expr, "CONCAT_WS") {
		t.Fatalf("expr = %q, want compound CONCAT_WS expression", expr)
	}
	if !strings.Contains(expr, "`User`.`FirstName`") || !strings.Contains(expr, "`User`.`LastName`") {
		t.Fatalf("expr = %q, want qualified component columns", expr)
	}
	if strings.Contains(expr, "`User`.`Name`") {
		t.Fatalf("expr = %q, must not reference physical User.Name", expr)
	}
}

func TestSearchFieldReadExpressionRejectsViewOnlyFieldOnBaseReadTarget(t *testing.T) {
	active := "active"
	formulaExpr := "Amount__c * 10"
	schema := &models.ObjectMetadata{
		APIName: "Widget__c",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "Amount__c", Type: constants.FieldTypeDouble},
			{
				APIName:     "Score__c",
				Type:        constants.FieldTypeFormula,
				Formula:     &formulaExpr,
				FormulaMode: &active,
			},
		},
	}

	_, err := searchFieldReadExpression(schema, "Score__c", "Widget__c")
	if err == nil {
		t.Fatal("searchFieldReadExpression = nil err, want view-only field rejection on base read target")
	}
	if !strings.Contains(err.Error(), "not available on the base read target") {
		t.Fatalf("err = %v, want base read target rejection", err)
	}
}

func TestSearchFieldReadExpressionAllowsViewOnlyFieldOnFormulaView(t *testing.T) {
	active := "active"
	formulaExpr := "Amount__c * 10"
	schema := &models.ObjectMetadata{
		APIName: "Widget__c",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "Amount__c", Type: constants.FieldTypeDouble},
			{
				APIName:     "Score__c",
				Type:        constants.FieldTypeFormula,
				Formula:     &formulaExpr,
				FormulaMode: &active,
			},
		},
	}

	expr, err := searchFieldReadExpression(schema, "Score__c", "v_widget__c")
	if err != nil {
		t.Fatalf("searchFieldReadExpression: %v", err)
	}
	if expr.SQL != "`v_widget__c`.`Score__c`" {
		t.Fatalf("expr.SQL = %q, want logical formula-view column", expr.SQL)
	}
}

func TestSearchFieldReadExpressionUsesPhysicalNameOnlyOnBaseReadTarget(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "RecordType",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, PhysicalName: "record_type_name"},
		},
	}

	baseExpr, err := searchFieldReadExpression(schema, constants.FieldName, "_System_RecordType")
	if err != nil {
		t.Fatalf("searchFieldReadExpression base: %v", err)
	}
	if baseExpr.SQL != "`_System_RecordType`.`record_type_name`" {
		t.Fatalf("base expr.SQL = %q, want physical readSource column", baseExpr.SQL)
	}

	viewExpr, err := searchFieldReadExpression(schema, constants.FieldName, "v_recordtype")
	if err != nil {
		t.Fatalf("searchFieldReadExpression view: %v", err)
	}
	if viewExpr.SQL != "`v_recordtype`.`Name`" {
		t.Fatalf("view expr.SQL = %q, want logical formula-view column", viewExpr.SQL)
	}
}

func TestSearchFieldReadExpressionRejectsCompoundWithMissingMember(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "Person__c",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{
				APIName:      constants.FieldName,
				Type:         constants.FieldTypeString,
				IsVirtual:    true,
				VirtualKind:  constants.VirtualFieldCompound,
				IsCompound:   true,
				ComposedFrom: []string{"FirstName__c", "LastName__c"},
			},
			{APIName: "FirstName__c", Type: constants.FieldTypeString},
		},
	}

	_, err := searchFieldReadExpression(schema, constants.FieldName, "Person__c")
	if err == nil {
		t.Fatal("searchFieldReadExpression = nil err, want missing compound member rejection")
	}
	if !strings.Contains(err.Error(), "LastName__c") {
		t.Fatalf("err = %v, want missing member named", err)
	}
}