package analytics

import (
	"context"
	"errors"
	"strings"
	"testing"

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

func TestCompileLookupValue(t *testing.T) {
	ast, err := Parse(`LOOKUPVALUE("StatusCatalog__c", "IsTerminal__c", "WorkflowId__c", WorkflowId__c, "Label__c", Status__c)`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	sql, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		AllowedLookupValues: statusCatalogLookupRules(),
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}

	want := "(SELECT lv.`IsTerminal__c` FROM `StatusCatalog__c` lv WHERE lv.`WorkflowId__c` = t.`WorkflowId__c` AND lv.`Label__c` = t.`Status__c` AND (lv.`IsDeleted` = 0 OR lv.`IsDeleted` IS NULL) LIMIT 1)"
	if sql != want {
		t.Fatalf("Compile() SQL mismatch\nwant: %s\n got: %s", want, sql)
	}
}

func TestCompileISPICKVALUsesInjectedExecutableBundleEvaluator(t *testing.T) {
	ast, err := Parse(`ISPICKVAL(Status, "Closed")`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	_, err = Compile(ast, &CompileContext{TableAlias: "t", QualifyFields: true})
	if err == nil || !strings.Contains(err.Error(), "ISPICKVAL") {
		t.Fatalf("Compile without bundle registry error = %v, want ISPICKVAL registration error", err)
	}

	executables := testISPICKVALFunctionExecutables(t, "NOT(value == expected)")
	_, err = Compile(ast, &CompileContext{
		TableAlias:       "t",
		QualifyFields:    true,
		FunctionRegistry: executables.SpecRegistry(),
	})
	if err == nil || !strings.Contains(err.Error(), "unsupported function: ISPICKVAL") {
		t.Fatalf("Compile with spec-only bundle registry error = %v, want unsupported ISPICKVAL SQL lowering", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		FunctionExecutables: executables,
	})
	if err != nil {
		t.Fatalf("Compile with bundle executables error = %v", err)
	}
	want := "(NOT (t.`Status` = 'Closed'))"
	if got != want {
		t.Fatalf("Compile with bundle executables = %q, want %q", got, want)
	}
}

func TestCompileINCLUDESRequiresInjectedBundleRegistry(t *testing.T) {
	ast, err := Parse(`INCLUDES(Regions__c, "North")`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	_, err = Compile(ast, &CompileContext{TableAlias: "t", QualifyFields: true})
	if err == nil || !strings.Contains(err.Error(), "INCLUDES") {
		t.Fatalf("Compile without bundle registry error = %v, want INCLUDES registration error", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		FunctionExecutables: testINCLUDESFunctionExecutables(t),
	})
	if err != nil {
		t.Fatalf("Compile with INCLUDES bundle executables error = %v", err)
	}
	want := utils.MultiPicklistJSONContainsSQL("t.`Regions__c`", "'North'", true)
	if got != want {
		t.Fatalf("Compile with INCLUDES bundle executables = %q, want %q", got, want)
	}
}

func TestCompileINCLUDESMultiTokenRHSLiteralUsesANDPredicate(t *testing.T) {
	ast, err := Parse(`INCLUDES(Regions__c, "North;East")`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		FunctionExecutables: testINCLUDESFunctionExecutables(t),
	})
	if err != nil {
		t.Fatalf("Compile with INCLUDES bundle executables error = %v", err)
	}
	want := "(" + utils.MultiPicklistJSONContainsSQL("t.`Regions__c`", "'North'", true) + " AND " + utils.MultiPicklistJSONContainsSQL("t.`Regions__c`", "'East'", true) + ")"
	if got != want {
		t.Fatalf("Compile multi-token INCLUDES = %q, want %q", got, want)
	}
}

func TestCompileINCLUDESNonLiteralRHSKeepsSinglePredicate(t *testing.T) {
	ast, err := Parse(`INCLUDES(Regions__c, WantedTag__c)`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		FunctionExecutables: testINCLUDESFunctionExecutables(t),
	})
	if err != nil {
		t.Fatalf("Compile with INCLUDES bundle executables error = %v", err)
	}
	want := utils.MultiPicklistJSONContainsSQL("t.`Regions__c`", "t.`WantedTag__c`", true)
	if got != want {
		t.Fatalf("Compile dynamic INCLUDES = %q, want %q", got, want)
	}
}

func TestCompileHYPERLINKUsesInjectedExecutableBundleEvaluator(t *testing.T) {
	for _, tc := range []struct {
		name string
		expr string
		want string
	}{
		{
			name: "two args",
			expr: `HYPERLINK(URL__c, Label__c)`,
			want: "t.`Label__c`",
		},
		{
			name: "optional target ignored",
			expr: `HYPERLINK(URL__c, Label__c, "_blank")`,
			want: "t.`Label__c`",
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			ast, err := Parse(tc.expr)
			if err != nil {
				t.Fatalf("Parse() error = %v", err)
			}

			_, err = Compile(ast, &CompileContext{TableAlias: "t", QualifyFields: true})
			if err == nil || !strings.Contains(err.Error(), "HYPERLINK") {
				t.Fatalf("Compile without bundle registry error = %v, want HYPERLINK registration error", err)
			}

			executables := testHYPERLINKFunctionExecutables(t)
			_, err = Compile(ast, &CompileContext{
				TableAlias:       "t",
				QualifyFields:    true,
				FunctionRegistry: executables.SpecRegistry(),
			})
			if err == nil || !strings.Contains(err.Error(), "unsupported function: HYPERLINK") {
				t.Fatalf("Compile with spec-only bundle registry error = %v, want unsupported HYPERLINK SQL lowering", err)
			}

			got, err := Compile(ast, &CompileContext{
				TableAlias:          "t",
				QualifyFields:       true,
				FunctionExecutables: executables,
			})
			if err != nil {
				t.Fatalf("Compile with bundle executables error = %v", err)
			}
			if got != tc.want {
				t.Fatalf("Compile with bundle executables = %q, want %q", got, tc.want)
			}
		})
	}
}

// TestCompileIMAGEUsesInjectedExecutableBundleEvaluator verifies the
// active formula-view DDL path lowers IMAGE through its formula_expr_v1
// executable bundle. IMAGE has body "arg0" (URL passthrough). Optional
// alt-text, height, and width args must be ignored — the lowered SQL
// must be the first mapped argument. Without the bundle the compiler
// must fail closed, and a spec-only registry (no executables) must
// also fail with "unsupported function: IMAGE".
func TestCompileIMAGEUsesInjectedExecutableBundleEvaluator(t *testing.T) {
	for _, tc := range []struct {
		name string
		expr string
		want string
	}{
		{
			name: "two args",
			expr: `IMAGE(URL__c, Label__c)`,
			want: "t.`URL__c`",
		},
		{
			name: "optional height and width ignored",
			expr: `IMAGE(URL__c, Label__c, 80, 80)`,
			want: "t.`URL__c`",
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			ast, err := Parse(tc.expr)
			if err != nil {
				t.Fatalf("Parse() error = %v", err)
			}

			_, err = Compile(ast, &CompileContext{TableAlias: "t", QualifyFields: true})
			if err == nil || !strings.Contains(err.Error(), "IMAGE") {
				t.Fatalf("Compile without bundle registry error = %v, want IMAGE registration error", err)
			}

			executables := testIMAGEFunctionExecutables(t)
			_, err = Compile(ast, &CompileContext{
				TableAlias:       "t",
				QualifyFields:    true,
				FunctionRegistry: executables.SpecRegistry(),
			})
			if err == nil || !strings.Contains(err.Error(), "unsupported function: IMAGE") {
				t.Fatalf("Compile with spec-only bundle registry error = %v, want unsupported IMAGE SQL lowering", err)
			}

			got, err := Compile(ast, &CompileContext{
				TableAlias:          "t",
				QualifyFields:       true,
				FunctionExecutables: executables,
			})
			if err != nil {
				t.Fatalf("Compile with bundle executables error = %v", err)
			}
			if got != tc.want {
				t.Fatalf("Compile with bundle executables = %q, want %q", got, tc.want)
			}
		})
	}
}

func testISPICKVALFunctionExecutables(t *testing.T, body string) *formula.ExecutableFunctionRegistry {
	t.Helper()
	payload := strings.ReplaceAll(`{
		"functions": [
			{
				"name": "ISPICKVAL",
				"minArgs": 2,
				"maxArgs": 2,
				"return": { "kind": "fixed", "type": "boolean" },
				"args": [
					{ "type": "any", "nullable": true },
					{ "type": "string", "nullable": true }
				],
				"evaluator": {
					"kind": "formula_expr_v1",
					"params": ["value", "expected"],
					"body": "__BODY__"
				}
			}
		]
	}`, "__BODY__", body)
	bundle, err := formula.ParseFunctionBundlePayload([]byte(payload))
	if err != nil {
		t.Fatalf("ParseFunctionBundlePayload: %v", err)
	}
	registry, err := formula.ExecutableFunctionRegistryWithBundles([]formula.FunctionBundle{bundle})
	if err != nil {
		t.Fatalf("ExecutableFunctionRegistryWithBundles: %v", err)
	}
	return registry
}

func testINCLUDESFunctionExecutables(t *testing.T) *formula.ExecutableFunctionRegistry {
	t.Helper()
	bundle, err := formula.ParseFunctionBundlePayload([]byte(`{
		"functions": [
			{
				"name": "INCLUDES",
				"minArgs": 2,
				"maxArgs": 2,
				"return": { "kind": "fixed", "type": "boolean" },
				"args": [
					{ "type": "string", "nullable": true },
					{ "type": "string", "nullable": true }
				],
				"evaluator": {
					"kind": "formula_expr_v1",
					"params": ["field", "value"],
					"body": "INCLUDES(field, value)"
				}
			}
		]
	}`))
	if err != nil {
		t.Fatalf("ParseFunctionBundlePayload: %v", err)
	}
	registry, err := formula.ExecutableFunctionRegistryWithBundles([]formula.FunctionBundle{bundle})
	if err != nil {
		t.Fatalf("ExecutableFunctionRegistryWithBundles: %v", err)
	}
	return registry
}

func testHYPERLINKFunctionExecutables(t *testing.T) *formula.ExecutableFunctionRegistry {
	t.Helper()
	bundle, err := formula.ParseFunctionBundlePayload([]byte(`{
		"functions": [
			{
				"name": "HYPERLINK",
				"minArgs": 2,
				"maxArgs": 3,
				"return": { "kind": "fixed", "type": "string" },
				"evaluator": {
					"kind": "formula_expr_v1",
					"params": ["url", "friendlyName"],
					"body": "friendlyName"
				}
			}
		]
	}`))
	if err != nil {
		t.Fatalf("ParseFunctionBundlePayload: %v", err)
	}
	registry, err := formula.ExecutableFunctionRegistryWithBundles([]formula.FunctionBundle{bundle})
	if err != nil {
		t.Fatalf("ExecutableFunctionRegistryWithBundles: %v", err)
	}
	return registry
}

func testIMAGEFunctionExecutables(t *testing.T) *formula.ExecutableFunctionRegistry {
	t.Helper()
	bundle, err := formula.ParseFunctionBundlePayload([]byte(`{
		"functions": [
			{
				"name": "IMAGE",
				"minArgs": 2,
				"maxArgs": 4,
				"return": { "kind": "fixed", "type": "string" },
				"args": [{ "type": "string", "nullable": true }],
				"evaluator": {
					"kind": "formula_expr_v1",
					"params": ["arg0", "arg1"],
					"body": "arg0"
				}
			}
		]
	}`))
	if err != nil {
		t.Fatalf("ParseFunctionBundlePayload: %v", err)
	}
	registry, err := formula.ExecutableFunctionRegistryWithBundles([]formula.FunctionBundle{bundle})
	if err != nil {
		t.Fatalf("ExecutableFunctionRegistryWithBundles: %v", err)
	}
	return registry
}

func TestCompileLookupValueRequiresAllowlist(t *testing.T) {
	ast, err := Parse(`LOOKUPVALUE("StatusCatalog__c", "IsTerminal__c", "WorkflowId__c", WorkflowId__c, "Label__c", Status__c)`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	_, err = Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
	})
	if !errors.Is(err, ErrRestrictedFormulaPrimitive) {
		t.Fatalf("Compile() error = %v, want ErrRestrictedFormulaPrimitive", err)
	}
}

func TestCompileLookupValueRequiresExactSourceFields(t *testing.T) {
	ast, err := Parse(`LOOKUPVALUE("StatusCatalog__c", "IsTerminal__c", "WorkflowId__c", OwnerId, "Label__c", Status__c)`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	_, err = Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		AllowedLookupValues: statusCatalogLookupRules(),
	})
	if !errors.Is(err, ErrRestrictedFormulaPrimitive) {
		t.Fatalf("Compile() error = %v, want ErrRestrictedFormulaPrimitive", err)
	}
}

func TestCompileISBLANKDateUsesNullOnly(t *testing.T) {
	got := compileFormulaForTest(t, `ISBLANK(End_Date__c)`, map[string]string{
		"End_Date__c": string(constants.FieldTypeDate),
	})

	want := "(t.`End_Date__c` IS NULL)"
	if got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

// TestCompileISBLANKNumericSeesBlankAsZero pins the #4152 interaction: numeric
// operands read through the blank-as-zero coalesce, so ISBLANK over a numeric
// FIELD compiles to a vacuously false predicate — matching the vendor's
// documented BlankAsZero behavior, where blank and 0 are indistinguishable to
// blank tests on number/currency/percent fields. The predicate shape is still
// null-only (no empty-string arm).
func TestCompileISBLANKNumericSeesBlankAsZero(t *testing.T) {
	for _, typ := range []string{"double", "currency", "percent", "int"} {
		t.Run(typ, func(t *testing.T) {
			got := compileFormulaForTest(t, `ISBLANK(Months_Field__c)`, map[string]string{
				"Months_Field__c": typ,
			})

			want := "(COALESCE(t.`Months_Field__c`, 0) IS NULL)"
			if typ == "percent" {
				want = "((COALESCE(t.`Months_Field__c`, 0) / 100.0) IS NULL)"
			}
			if got != want {
				t.Fatalf("Compile() = %q, want %q", got, want)
			}
		})
	}
}

func TestCompileNormalizesPercentFieldRefsToFormulaDomain(t *testing.T) {
	tests := []struct {
		name       string
		formula    string
		fieldTypes map[string]string
		want       string
	}{
		{
			name:       "bare field",
			formula:    `Probability > 0.4`,
			fieldTypes: map[string]string{"Probability": string(constants.FieldTypePercent)},
			want:       "((COALESCE(t.`Probability`, 0) / 100.0) > 0.4)",
		},
		{
			name:       "relationship field",
			formula:    `Parent__r.Score__c * 2`,
			fieldTypes: map[string]string{"Parent__r.Score__c": string(constants.FieldTypePercent)},
			want:       "((COALESCE(j_parent.`Score__c`, 0) / 100.0) * 2)",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			ast, err := Parse(tt.formula)
			if err != nil {
				t.Fatalf("Parse(%q) error = %v", tt.formula, err)
			}
			got, err := Compile(ast, &CompileContext{
				TableAlias:    "t",
				QualifyFields: true,
				JoinAliases: map[string]string{
					"Parent__r": "j_parent",
				},
				FieldTypes: tt.fieldTypes,
			})
			if err != nil {
				t.Fatalf("Compile(%q) error = %v", tt.formula, err)
			}
			if got != tt.want {
				t.Fatalf("Compile() = %q, want %q", got, tt.want)
			}
		})
	}
}

func TestCompileISBLANKByOperandShape(t *testing.T) {
	tests := []struct {
		name       string
		formula    string
		fieldTypes map[string]string
		want       string
	}{
		{
			name:       "unknown field uses cast fallback",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{},
			want:       "(IFNULL(CAST(t.`Foo__c` AS CHAR) = '', TRUE))",
		},
		{
			name:       "json field uses cast fallback",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": string(constants.FieldTypeJson)},
			want:       "(IFNULL(CAST(t.`Foo__c` AS CHAR) = '', TRUE))",
		},
		{
			name:       "boolean field uses null only",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": string(constants.FieldTypeBoolean)},
			want:       "(t.`Foo__c` IS NULL)",
		},
		{
			name:       "checkbox alias uses null only",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": "checkbox"},
			want:       "(t.`Foo__c` IS NULL)",
		},
		{
			name:       "reference field uses blank-aware predicate",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": string(constants.FieldTypeReference)},
			want:       "(IFNULL(t.`Foo__c` = '', TRUE))",
		},
		{
			name:       "master-detail field uses blank-aware predicate",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": string(constants.FieldTypeMasterDetail)},
			want:       "(IFNULL(t.`Foo__c` = '', TRUE))",
		},
		{
			name:       "lookup alias uses blank-aware predicate",
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": "lookup"},
			want:       "(IFNULL(t.`Foo__c` = '', TRUE))",
		},
		{
			name:       "date returning function uses null only",
			formula:    `ISBLANK(__DATE_ADD_MONTHS__(D__c, 1))`,
			fieldTypes: map[string]string{"D__c": string(constants.FieldTypeDate)},
			want:       "(DATE(GREATEST(DATE_ADD(t.`D__c`, INTERVAL 1 MONTH), IFNULL(DATE_SUB(DATE_ADD(IFNULL(DATE_ADD(t.`D__c`, INTERVAL 1 DAY), DATE('9999-12-31')), INTERVAL 1 MONTH), INTERVAL 1 DAY), DATE('9999-12-31')))) IS NULL)",
		},
		{
			name:    "string literal uses text shape",
			formula: `ISBLANK("foo")`,
			want:    "(IFNULL('foo' = '', TRUE))",
		},
		{
			name:    "number literal uses null only",
			formula: `ISBLANK(0)`,
			want:    "(0 IS NULL)",
		},
		{
			name:    "boolean literal uses null only",
			formula: `ISBLANK(TRUE)`,
			want:    "(1 IS NULL)",
		},
		{
			name:    "null literal uses cast fallback",
			formula: `ISBLANK(null)`,
			want:    "(IFNULL(CAST(NULL AS CHAR) = '', TRUE))",
		},
	}

	textLikeTypes := []constants.SchemaFieldType{
		constants.FieldTypeString,
		constants.FieldTypeTextarea,
		constants.FieldTypeRichtextarea,
		constants.FieldTypeEmail,
		constants.FieldTypePhone,
		constants.FieldTypeUrl,
		constants.FieldTypePicklist,
		constants.FieldTypeMultiPicklist,
		constants.FieldTypeAutonumber,
		constants.FieldTypeEncryptedString,
		constants.FieldTypePassword,
	}
	for _, typ := range textLikeTypes {
		tests = append(tests, struct {
			name       string
			formula    string
			fieldTypes map[string]string
			want       string
		}{
			name:       "text-like field preserves text shape " + string(typ),
			formula:    `ISBLANK(Foo__c)`,
			fieldTypes: map[string]string{"Foo__c": string(typ)},
			want:       "(IFNULL(t.`Foo__c` = '', TRUE))",
		})
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := compileFormulaForTest(t, tt.formula, tt.fieldTypes)
			if got != tt.want {
				t.Fatalf("Compile() = %q, want %q", got, tt.want)
			}
		})
	}
}

// TestCompileNullComparisonLowersToIsNull covers NX#3908: a `Field = NULL` /
// `Field <> NULL` idiom must lower to SQL `IS NULL` / `IS NOT NULL` on the view
// projection path, matching Salesforce and the kernel evaluator, instead of the
// prior raw `(Field = NULL)` that never matched under three-valued logic.
func TestCompileNullComparisonLowersToIsNull(t *testing.T) {
	tests := []struct {
		name       string
		formula    string
		fieldTypes map[string]string
		want       string
	}{
		{
			name:    "equality field on left",
			formula: `Fee_Rate__c = NULL`,
			want:    "(t.`Fee_Rate__c` IS NULL)",
		},
		{
			name:    "equality null on left mirror",
			formula: `NULL = Fee_Rate__c`,
			want:    "(t.`Fee_Rate__c` IS NULL)",
		},
		{
			name:    "inequality bang-eq",
			formula: `Fee_Rate__c != NULL`,
			want:    "(t.`Fee_Rate__c` IS NOT NULL)",
		},
		{
			name:    "inequality angle-bracket",
			formula: `Fee_Rate__c <> NULL`,
			want:    "(t.`Fee_Rate__c` IS NOT NULL)",
		},
		{
			name:    "double-eq spelling normalizes",
			formula: `Fee_Rate__c == NULL`,
			want:    "(t.`Fee_Rate__c` IS NULL)",
		},
		{
			// #4152: the percent read coalesces blanks to zero before scaling,
			// so `field = NULL` over a numeric field is vacuously false — the
			// vendor's BlankAsZero contract (the field is never blank). The
			// blank-or-zero idiom keeps selecting its branch via the `= 0` arm.
			name:       "percent field coalesces under IS NULL (blank-as-zero)",
			formula:    `Fee_Rate__c = NULL`,
			fieldTypes: map[string]string{"Fee_Rate__c": string(constants.FieldTypePercent)},
			want:       "((COALESCE(t.`Fee_Rate__c`, 0) / 100.0) IS NULL)",
		},
		{
			name:    "null equals null constant-folds to true predicate",
			formula: `NULL = NULL`,
			want:    "(NULL IS NULL)",
		},
		{
			name:    "null not-equals null constant-folds to false predicate",
			formula: `NULL != NULL`,
			want:    "(NULL IS NOT NULL)",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := compileFormulaForTest(t, tt.formula, tt.fieldTypes)
			if got != tt.want {
				t.Fatalf("Compile() = %q, want %q", got, tt.want)
			}
		})
	}
}

// TestCompileNullComparisonReproBranchSelection is the NX#3908 end-to-end repro
// shape: OR(field = NULL, field = 0) must select the blank-or-zero branch when
// the field is blank. Before NX#3908 the `field = NULL` arm was never-true, so a
// blank field fell to the else branch and propagated NULL through the formula.
// Under blank-as-zero (#4152) the same guarantee holds through the OTHER arm:
// the coalesced read makes `field = NULL` vacuously false, and the blank field
// now satisfies `field = 0`. Branch selection is pinned end-to-end so neither
// change can silently flip the repro's outcome.
func TestCompileNullComparisonReproBranchSelection(t *testing.T) {
	got := compileFormulaForTest(t,
		`IF(OR(Fee_Rate__c = NULL, Fee_Rate__c = 0), 1, 2)`,
		map[string]string{"Fee_Rate__c": string(constants.FieldTypePercent)})

	// The first OR arm must be a genuine IS NULL test over the coalesced read,
	// not a raw `= NULL`.
	if !strings.Contains(got, "(COALESCE(t.`Fee_Rate__c`, 0) / 100.0) IS NULL") {
		t.Fatalf("expected IS NULL arm in repro lowering, got %q", got)
	}
	// The `= 0` arm is the one a blank field now takes: coalesced 0 = 0.
	if !strings.Contains(got, "(COALESCE(t.`Fee_Rate__c`, 0) / 100.0) = 0") {
		t.Fatalf("expected = 0 arm over the coalesced read, got %q", got)
	}
	if strings.Contains(got, "= NULL") {
		t.Fatalf("raw `= NULL` must not survive in lowering, got %q", got)
	}
}

// TestCompileBlankAndNullLoweringsUnchangedUnderGate pins the blank/NULL family
// against the null provenance NULL-provenance gate.
//
// null provenance was ORIGINALLY scoped with a "leaf normalization" prerequisite that
// would have wrapped string-like and boolean reads in an empty-string COALESCE. That
// prerequisite was DROPPED: it would have re-lowered ISBLANK/ISNULL, the NX#3908
// `= NULL` special case, the empty-string special case, and — fatally —
// multipicklist reads, where TiDB answers JSON_TYPE over a coalesced-to-empty
// multipicklist column with ERROR 3140 and fails the whole view SELECT.
//
// Every row below still consumes the RAW column. The blank-aware rows now apply
// IFNULL to the boolean comparison, never to the value. This test exists to
// make a future reintroduction of leaf normalization fail loudly here rather
// than silently in production, and to keep the NX#3908 ordering contract
// (empty-string special case wins over `= NULL`) visible next to it.
func TestCompileBlankAndNullLoweringsUnchangedUnderGate(t *testing.T) {
	stringTypes := map[string]string{
		"Str__c":  string(constants.FieldTypeString),
		"Pick__c": string(constants.FieldTypePicklist),
	}
	for _, row := range []struct{ name, formula, want string }{
		{"isblank", `ISBLANK(Str__c)`, "(IFNULL(t.`Str__c` = '', TRUE))"},
		{"isnull", `ISNULL(Str__c)`, "(t.`Str__c` IS NULL)"},
		{"empty_string_equality", `Str__c = ""`, "(IFNULL(t.`Str__c` = '', TRUE))"},
		{"null_equality", `Str__c = NULL`, "(t.`Str__c` IS NULL)"},
		{"len", `LEN(Str__c)`, "CHAR_LENGTH(t.`Str__c`)"},
		{"concat", `Str__c & Pick__c`, "CONCAT(t.`Str__c`, t.`Pick__c`)"},
	} {
		t.Run(row.name, func(t *testing.T) {
			if got := compileFormulaForTest(t, row.formula, stringTypes); got != row.want {
				t.Fatalf("blank/NULL lowering drifted:\n got: %s\nwant: %s", got, row.want)
			}
		})

		// Same lowering, now sitting inside an IF condition alongside a
		// field-denominator division — i.e. in the exact place the provenance
		// gate inspects. The same lowering must survive intact AND the gate must
		// stay shut, because a string leaf is blank-capable.
		t.Run(row.name+"/inside_gated_condition", func(t *testing.T) {
			types := map[string]string{
				"Str__c":  string(constants.FieldTypeString),
				"Pick__c": string(constants.FieldTypePicklist),
				"Num__c":  string(constants.FieldTypeDouble),
				"Den__c":  string(constants.FieldTypeDouble),
			}
			got := compileFormulaForTest(t,
				`IF(AND(`+row.formula+` = "", Num__c/Den__c > 0.5), 1, 2)`, types)
			if !strings.Contains(got, row.want) {
				t.Fatalf("lowering %q missing inside gated condition:\n%s", row.want, got)
			}
			if strings.Contains(got, "WHEN TRUE THEN") {
				t.Fatalf("blank-capable leaf must keep the gate shut:\n%s", got)
			}
		})
	}

	// The multipicklist row is separate: INCLUDES needs the L1 bundle registry.
	// It is the row that would have crashed production had leaf normalization
	// shipped, so it is asserted to contain NO COALESCE at all.
	ast, err := Parse(`INCLUDES(Regions__c, "Hot")`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}
	got, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		FieldTypes:          map[string]string{"Regions__c": string(constants.FieldTypeMultiPicklist)},
		FunctionExecutables: testINCLUDESFunctionExecutables(t),
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}
	want := utils.MultiPicklistJSONContainsSQL("t.`Regions__c`", "'Hot'", true)
	if got != want {
		t.Fatalf("INCLUDES lowering drifted:\n got: %s\nwant: %s", got, want)
	}
	if !strings.Contains(got, "JSON_TYPE(t.`Regions__c`)") {
		t.Fatalf("multipicklist read must stay raw — JSON_TYPE(COALESCE(NULL,'')) is TiDB ERROR 3140 "+
			"and would fail the whole view SELECT:\n%s", got)
	}
}

// TestCompileEmptyStringVsNullPrecedence locks the ordering guarantee: the
// degenerate `"" = NULL` must keep the empty-string blank-aware shape (empty
// string helper runs first), not be read as `(” IS NULL)`.
func TestCompileEmptyStringVsNullPrecedence(t *testing.T) {
	got := compileFormulaForTest(t, `"" = NULL`, nil)
	// Empty-string literal comparison wins; assert the exact blank-aware
	// predicate over the NULL operand so a differently-wrong lowering (e.g.
	// `('' IS NULL)`) cannot slip through.
	want := "(IFNULL(CAST(NULL AS CHAR) = '', TRUE))"
	if got != want {
		t.Fatalf("empty-string vs NULL precedence: Compile() = %q, want %q", got, want)
	}
}

// TestCompileNullComparisonAcrossRefShapes locks the operand-wrapping across a
// relationship (cross-ref) operand, including the percent-domain scaling that is
// exactly the shape NX#3908 cares about on joined fields.
func TestCompileNullComparisonAcrossRefShapes(t *testing.T) {
	ast, err := Parse(`Parent__r.Fee_Rate__c = NULL`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}
	got, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		JoinAliases:   map[string]string{"Parent__r": "j_parent"},
		FieldTypes:    map[string]string{"Parent__r.Fee_Rate__c": string(constants.FieldTypePercent)},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}
	want := "((COALESCE(j_parent.`Fee_Rate__c`, 0) / 100.0) IS NULL)"
	if got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

func compileFormulaForTest(t *testing.T, formula string, fieldTypes map[string]string) string {
	t.Helper()
	ast, err := Parse(formula)
	if err != nil {
		t.Fatalf("Parse(%q) error = %v", formula, err)
	}
	sql, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		FieldTypes:    fieldTypes,
	})
	if err != nil {
		t.Fatalf("Compile(%q) error = %v", formula, err)
	}
	return sql
}

func TestCompileLookupValueRejectsNonLiteralIdentifiers(t *testing.T) {
	tests := []struct {
		name       string
		formula    string
		wantErrSub string
	}{
		{
			name:       "target table",
			formula:    `LOOKUPVALUE(StatusCatalog__c, "IsTerminal__c", "WorkflowId__c", WorkflowId__c)`,
			wantErrSub: "target table must be a string literal",
		},
		{
			name:       "output field",
			formula:    `LOOKUPVALUE("StatusCatalog__c", IsTerminal__c, "WorkflowId__c", WorkflowId__c)`,
			wantErrSub: "output field must be a string literal",
		},
		{
			name:       "match field",
			formula:    `LOOKUPVALUE("StatusCatalog__c", "IsTerminal__c", WorkflowId__c, WorkflowId__c)`,
			wantErrSub: "match field at argument 3 must be a string literal",
		},
		{
			name:       "unsafe identifier",
			formula:    `LOOKUPVALUE("StatusCatalog__c;DROP", "IsTerminal__c", "WorkflowId__c", WorkflowId__c)`,
			wantErrSub: "invalid LOOKUPVALUE target table identifier",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			ast, err := Parse(tt.formula)
			if err != nil {
				t.Fatalf("Parse() error = %v", err)
			}

			_, err = Compile(ast, &CompileContext{
				TableAlias:    "t",
				QualifyFields: true,
			})
			if err == nil {
				t.Fatal("Compile() error = nil")
			}
			if !strings.Contains(err.Error(), tt.wantErrSub) {
				t.Fatalf("Compile() error = %q, want substring %q", err.Error(), tt.wantErrSub)
			}
		})
	}
}

func TestGetBaseFieldsExcludesActiveComputedStandardFields(t *testing.T) {
	formula := "IF(ISBLANK(ReviewerId), Project__r.Email__c, Reviewer.Email)"
	mode := "active"
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			{
				APIName:     "Email",
				Type:        constants.FieldTypeEmail,
				Formula:     &formula,
				FormulaMode: &mode,
			},
		},
	}

	if !formulastorage.FieldRequiresPhysicalColumn(&schema.Fields[0]) {
		t.Fatal("FieldRequiresPhysicalColumn(Id) = false, want true")
	}
	if formulastorage.FieldRequiresPhysicalColumn(&schema.Fields[1]) {
		t.Fatal("FieldRequiresPhysicalColumn(Email active computed) = true, want false")
	}

	computedFields := formulastorage.ActiveComputedFields(schema)
	if len(computedFields) != 1 || computedFields[0].APIName != "Email" {
		t.Fatalf("ActiveComputedFields() = %#v, want Email", computedFields)
	}
}

func TestResolveTableNameActiveComputedFieldsFailClosedToView(t *testing.T) {
	formula := "IF(ISBLANK(ReviewerId), Project__r.Email__c, Reviewer.Email)"
	mode := "active"
	schema := &models.ObjectMetadata{
		APIName: "WorkItem",
		Fields: []models.FieldMetadata{
			{
				APIName:     "Email",
				Type:        constants.FieldTypeEmail,
				Formula:     &formula,
				FormulaMode: &mode,
			},
		},
	}

	service := &FormulaViewService{}
	if got := service.ResolveTableName(context.Background(), schema); got != "v_workitem" {
		t.Fatalf("ResolveTableName() = %q, want v_workitem", got)
	}
}

func TestResolveTableNameLookupValueProjectionFailClosedToView(t *testing.T) {
	mode := "active"
	schema := &models.ObjectMetadata{
		APIName: "PricebookEntry",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "Product2Id", Type: constants.FieldTypeReference, ReferenceTo: []string{"Product2"}},
			{
				APIName:     constants.FieldName,
				Type:        constants.FieldTypeString,
				IsNameField: true,
				FormulaMode: &mode,
				ReturnType:  fieldTypePtr(constants.FieldTypeString),
				ProjectionConfig: &models.FieldProjectionConfig{
					Kind:         "lookupValue",
					TargetObject: "Product2",
					SelectField:  constants.FieldName,
					Matches: []models.FieldProjectionMatch{
						{TargetField: constants.FieldID, SourceField: "Product2Id"},
					},
					ExcludeDeleted: true,
				},
			},
		},
	}

	for i := range schema.Fields {
		field := &schema.Fields[i]
		if field.APIName == constants.FieldName {
			if formulastorage.FieldRequiresPhysicalColumn(field) {
				t.Fatal("FieldRequiresPhysicalColumn(projected Name) = true, want false")
			}
			continue
		}
		if !formulastorage.FieldRequiresPhysicalColumn(field) {
			t.Fatalf("FieldRequiresPhysicalColumn(%s) = false, want true", field.APIName)
		}
	}

	computedFields := formulastorage.ActiveComputedFields(schema)
	if len(computedFields) != 1 || computedFields[0].APIName != constants.FieldName {
		t.Fatalf("ActiveComputedFields() = %#v, want projected Name only", computedFields)
	}

	service := &FormulaViewService{}
	if got := service.ResolveTableName(context.Background(), schema); got != "v_pricebookentry" {
		t.Fatalf("ResolveTableName() = %q, want v_pricebookentry", got)
	}
}

func TestCompileCrossRefUsesLogicalFieldAlias(t *testing.T) {
	ast, err := Parse("Project__r.Name")
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		JoinAliases: map[string]string{
			"Project__r": "j_project",
		},
		CrossRefFieldAliases: map[string]string{
			"Project__r.Name": "DisplayName__c",
		},
		QualifyFields: true,
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}
	if want := "j_project.`DisplayName__c`"; got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

func TestCompileCrossRefRequiresMetadataJoin(t *testing.T) {
	ast, err := Parse("Project.Name")
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	_, err = Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
	})
	if err == nil {
		t.Fatal("Compile() error = nil, want unresolved cross-reference failure")
	}
	if !strings.Contains(err.Error(), "unresolved cross-reference Project.Name") {
		t.Fatalf("Compile() error = %q, want unresolved Project.Name failure", err.Error())
	}
}

func TestCompileDeepCrossRefUsesResolvedJoinChain(t *testing.T) {
	ast, err := Parse("Owner.Profile.Name")
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		DeepJoinMap: map[string]string{
			"Owner.Profile.Name": "dj_owner_profile.`Name`",
		},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}
	if want := "dj_owner_profile.`Name`"; got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

func TestCompileDeepCrossRefUsesResolvedJoinChainCaseInsensitive(t *testing.T) {
	ast, err := Parse("owner.profile.name")
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		DeepJoinMap: map[string]string{
			"Owner.Profile.Name": "dj_owner_profile.`Name`",
		},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}
	if want := "dj_owner_profile.`Name`"; got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

func TestCompileAggregateMarkerPreservesExplicitForeignKey(t *testing.T) {
	sql, err := Compile(&AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Hours__c",
		ForeignKey:  "ParentIssue__c",
	}, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}

	const want = "##AGG##SUM##WorkLog__c##Hours__c##ParentIssue__c##"
	if sql != want {
		t.Fatalf("Compile() = %q, want %q", sql, want)
	}
}

func TestCompileCountWildcardAggregateMarker(t *testing.T) {
	sql, err := Compile(&AggregateExpr{
		Func:        "COUNT",
		ChildObject: "WorkLog__c",
		ChildField:  "*",
	}, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}

	const want = "##AGG##COUNT##WorkLog__c##*##_##"
	if sql != want {
		t.Fatalf("Compile() = %q, want %q", sql, want)
	}
}

func TestParseRejectsNonCountWildcardAggregate(t *testing.T) {
	for _, formula := range []string{
		"SUM(WorkLog__c.*)",
		"AVG(WorkLog__c.*)",
		"MIN(WorkLog__c.*)",
		"MAX(WorkLog__c.*)",
	} {
		if _, err := Parse(formula); err == nil {
			t.Fatalf("Parse(%q) error = nil, want wildcard aggregate rejection", formula)
		}
	}
}

func TestCompileTextCrossRefCoalescesNullToEmptyString(t *testing.T) {
	ast, err := Parse("TEXT(Opportunity.Sales_Channel_Type__c)")
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		JoinAliases: map[string]string{
			"Opportunity": "j_opp",
		},
		QualifyFields: true,
		FieldTypes: map[string]string{
			"Opportunity.Sales_Channel_Type__c": string(constants.FieldTypePicklist),
		},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}

	want := "COALESCE(j_opp.`Sales_Channel_Type__c`, '')"
	if got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

func TestCompileEmptyStringComparisonUsesOperandShape(t *testing.T) {
	tests := []struct {
		name    string
		formula string
		ctx     *CompileContext
		want    string
	}{
		{
			name:    "reference equality uses blank-aware semantics",
			formula: `Partner_Name__c = ""`,
			ctx: &CompileContext{
				TableAlias:    "t",
				QualifyFields: true,
				FieldTypes: map[string]string{
					"Partner_Name__c": string(constants.FieldTypeReference),
				},
			},
			want: "(IFNULL(t.`Partner_Name__c` = '', TRUE))",
		},
		{
			name:    "reference inequality uses blank-aware semantics",
			formula: `Partner_Name__c <> ""`,
			ctx: &CompileContext{
				TableAlias:    "t",
				QualifyFields: true,
				FieldTypes: map[string]string{
					"Partner_Name__c": string(constants.FieldTypeReference),
				},
			},
			want: "(NOT (IFNULL(t.`Partner_Name__c` = '', TRUE)))",
		},
		{
			name:    "cross-ref TEXT equality uses string semantics",
			formula: `TEXT(Opportunity.Sales_Channel_Type__c) = ""`,
			ctx: &CompileContext{
				JoinAliases: map[string]string{
					"Opportunity": "j_opp",
				},
				QualifyFields: true,
				FieldTypes: map[string]string{
					"Opportunity.Sales_Channel_Type__c": string(constants.FieldTypePicklist),
				},
			},
			want: "(IFNULL(COALESCE(j_opp.`Sales_Channel_Type__c`, '') = '', TRUE))",
		},
		{
			name:    "unknown field keeps cast fallback",
			formula: `Foo__c = ""`,
			ctx: &CompileContext{
				TableAlias:    "t",
				QualifyFields: true,
			},
			want: "(IFNULL(CAST(t.`Foo__c` AS CHAR) = '', TRUE))",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			ast, err := Parse(tt.formula)
			if err != nil {
				t.Fatalf("Parse() error = %v", err)
			}

			got, err := Compile(ast, tt.ctx)
			if err != nil {
				t.Fatalf("Compile() error = %v", err)
			}
			if got != tt.want {
				t.Fatalf("Compile() = %q, want %q", got, tt.want)
			}
		})
	}
}

func TestCompileIssue1301RegressionFormula(t *testing.T) {
	ast, err := Parse(`IF(TEXT(Opportunity.Sales_Channel_Type__c) = "", IF(Partner_Name__c = "", "Direct", "Indirect"), TEXT(Opportunity.Sales_Channel_Type__c))`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias: "t",
		JoinAliases: map[string]string{
			"Opportunity": "j_opp",
		},
		QualifyFields: true,
		FieldTypes: map[string]string{
			"Partner_Name__c":                   string(constants.FieldTypeReference),
			"Opportunity.Sales_Channel_Type__c": string(constants.FieldTypePicklist),
		},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}

	wantParts := []string{
		"CASE WHEN (IFNULL(COALESCE(j_opp.`Sales_Channel_Type__c`, '') = '', TRUE)) THEN",
		"CASE WHEN (IFNULL(t.`Partner_Name__c` = '', TRUE)) THEN 'Direct' ELSE 'Indirect' END",
		"ELSE COALESCE(j_opp.`Sales_Channel_Type__c`, '') END",
	}
	for _, want := range wantParts {
		if !strings.Contains(got, want) {
			t.Fatalf("Compile() missing %q in SQL:\n%s", want, got)
		}
	}
}

func TestCompileSupportsPublicActiveFunctionBatch(t *testing.T) {
	tests := []struct {
		name    string
		formula string
	}{
		{name: "BLANKVALUE", formula: `BLANKVALUE(Subject, "n/a")`},
		{name: "ISNUMBER", formula: `ISNUMBER(Subject)`},
		{name: "LPAD", formula: `LPAD(Subject, 5, "0")`},
		{name: "RPAD", formula: `RPAD(Subject, 5, " ")`},
		{name: "BR", formula: `BR()`},
		{name: "DATETIMEVALUE", formula: `DATETIMEVALUE(CreatedDate)`},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			ast, err := Parse(tt.formula)
			if err != nil {
				t.Fatalf("Parse() error = %v", err)
			}

			sql, err := Compile(ast, &CompileContext{
				TableAlias:    "t",
				QualifyFields: true,
			})
			if err != nil {
				t.Fatalf("Compile() error = %v", err)
			}
			if strings.TrimSpace(sql) == "" {
				t.Fatalf("Compile() SQL = %q, want non-empty output", sql)
			}
		})
	}
}

func statusCatalogLookupRules() []LookupValueRule {
	return []LookupValueRule{
		{
			TargetTable:    "StatusCatalog__c",
			OutputField:    "IsTerminal__c",
			ExcludeDeleted: true,
			MatchPairs: []LookupValueMatchRule{
				{
					MatchField:  "WorkflowId__c",
					SourceField: "WorkflowId__c",
				},
				{
					MatchField:  "Label__c",
					SourceField: "Status__c",
				},
			},
		},
	}
}

func fieldTypePtr(v constants.SchemaFieldType) *models.FieldType {
	fieldType := models.FieldType(v)
	return &fieldType
}

// TestCompileDatetimeSubtractionUsesFractionalDays verifies issue #1684:
// datetime - datetime must emit TIMESTAMPDIFF(SECOND, …) / 86400.0 (fractional
// days), while date - date must stay as DATEDIFF (integer days).
func TestCompileDatetimeSubtractionUsesFractionalDays(t *testing.T) {
	tests := []struct {
		name           string
		formula        string
		fieldTypes     map[string]string
		mustContain    []string
		mustNotContain []string
	}{
		{
			name:    "datetime field minus NOW() uses TIMESTAMPDIFF",
			formula: `NOW() - Event_Time__c`,
			fieldTypes: map[string]string{
				"Event_Time__c": "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "chained *24 still works with TIMESTAMPDIFF",
			formula: `(NOW() - Event_Time__c) * 24`,
			fieldTypes: map[string]string{
				"Event_Time__c": "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0", "* 24"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "date field minus TODAY() stays DATEDIFF (regression guard)",
			formula: `TODAY() - CloseDate__c`,
			fieldTypes: map[string]string{
				"CloseDate__c": "date",
			},
			mustContain:    []string{"DATEDIFF"},
			mustNotContain: []string{"TIMESTAMPDIFF"},
		},
		{
			name:    "datetime field minus datetime field uses TIMESTAMPDIFF",
			formula: `A__c - B__c`,
			fieldTypes: map[string]string{
				"A__c": "datetime",
				"B__c": "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			// #1684 scope boundary (documented, intentional): mixed
			// datetime - date stays DATEDIFF. The fractional-day fix targets
			// datetime - datetime only; when one operand is date-only the
			// existing DATEDIFF path is unchanged (Salesforce's datetime-date
			// delta is a separate, out-of-scope concern).
			name:    "mixed datetime minus date stays DATEDIFF (out of scope, documented)",
			formula: `NOW() - CloseDate__c`,
			fieldTypes: map[string]string{
				"CloseDate__c": "date",
			},
			mustContain:    []string{"DATEDIFF"},
			mustNotContain: []string{"TIMESTAMPDIFF"},
		},
		// #1684 CI BLOCK fix: BinaryOp and merge-return FuncCall wrappers must
		// be recognised as datetime-like so wrapped/shifted subtraction picks
		// TIMESTAMPDIFF instead of falling through to raw SQL `-`.
		{
			name:    "BLANKVALUE(datetime, NOW()) minus datetime uses TIMESTAMPDIFF",
			formula: `BLANKVALUE(Resolved_At__c, NOW()) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "NULLVALUE(datetime, NOW()) minus datetime uses TIMESTAMPDIFF",
			formula: `NULLVALUE(Resolved_At__c, NOW()) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "BinaryOp (NOW() + 1) minus datetime uses TIMESTAMPDIFF",
			formula: `(NOW() + 1) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Opened_At__c": "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "BinaryOp (NOW() - 1) minus datetime uses TIMESTAMPDIFF",
			formula: `(NOW() - 1) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Opened_At__c": "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			// Regression guard: when BLANKVALUE merges date args (not datetime),
			// the result is date-only → falls through to DATEDIFF, not TIMESTAMPDIFF.
			name:    "BLANKVALUE(date, TODAY()) minus date stays DATEDIFF (regression)",
			formula: `BLANKVALUE(Due_Date__c, TODAY()) - Created_Date__c`,
			fieldTypes: map[string]string{
				"Due_Date__c":     "date",
				"Created_Date__c": "date",
			},
			mustContain:    []string{"DATEDIFF"},
			mustNotContain: []string{"TIMESTAMPDIFF"},
		},
		{
			// #1684 CI BLOCK round-2 fix: a NULL-literal merged argument must be
			// absorbed (mirror validator's mergeSchemaTypes), so a nullable
			// wrapper over a datetime still picks TIMESTAMPDIFF, not raw `-`.
			name:    "NULLVALUE(datetime, null) minus datetime uses TIMESTAMPDIFF",
			formula: `NULLVALUE(Resolved_At__c, null) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "BLANKVALUE(datetime, null) minus datetime uses TIMESTAMPDIFF",
			formula: `BLANKVALUE(Resolved_At__c, null) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			// #1684 CI BLOCK round-3 fix: DSL-passthrough coalescers
			// (IFNULL/COALESCE/NULLIF) are not in the catalog but are datetime
			// when their operands are; the shared nullCoalescingFuncs SSoT now
			// drives every coalescer, so wrapped subtraction picks TIMESTAMPDIFF.
			name:    "IFNULL(datetime, NOW()) minus datetime uses TIMESTAMPDIFF",
			formula: `IFNULL(Resolved_At__c, NOW()) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "COALESCE(datetime, NOW()) minus datetime uses TIMESTAMPDIFF",
			formula: `COALESCE(Resolved_At__c, NOW()) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
		{
			name:    "NULLIF(datetime, NOW()) minus datetime uses TIMESTAMPDIFF",
			formula: `NULLIF(Resolved_At__c, NOW()) - Opened_At__c`,
			fieldTypes: map[string]string{
				"Resolved_At__c": "datetime",
				"Opened_At__c":   "datetime",
			},
			mustContain:    []string{"TIMESTAMPDIFF(SECOND", "/ 86400.0"},
			mustNotContain: []string{"DATEDIFF"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			ast, err := Parse(tt.formula)
			if err != nil {
				t.Fatalf("Parse(%q) error = %v", tt.formula, err)
			}
			got, err := Compile(ast, &CompileContext{
				FieldTypes: tt.fieldTypes,
			})
			if err != nil {
				t.Fatalf("Compile(%q) error = %v", tt.formula, err)
			}
			for _, want := range tt.mustContain {
				if !strings.Contains(got, want) {
					t.Errorf("Compile(%q) = %q\n  missing expected substring %q", tt.formula, got, want)
				}
			}
			for _, notWant := range tt.mustNotContain {
				if strings.Contains(got, notWant) {
					t.Errorf("Compile(%q) = %q\n  unexpected substring %q", tt.formula, got, notWant)
				}
			}
		})
	}
}

// TestCompileLookupValueHoistedJoinAlias verifies that when a LookupValueRule carries
// a non-empty HoistedJoinAlias, compileLookupValue emits <alias>.<outputField>
// instead of the correlated scalar subquery form.
func TestCompileLookupValueHoistedJoinAlias(t *testing.T) {
	ast, err := Parse(`LOOKUPVALUE("Product2", "Name", "Id", Product2Id)`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	sql, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		AllowedLookupValues: []LookupValueRule{
			{
				TargetTable:      "Product2",
				OutputField:      constants.FieldName,
				ExcludeDeleted:   true,
				HoistedJoinAlias: "lv_product2",
				MatchPairs: []LookupValueMatchRule{
					{MatchField: constants.FieldID, SourceField: "Product2Id"},
				},
			},
		},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}

	want := "lv_product2.`" + constants.FieldName + "`"
	if sql != want {
		t.Fatalf("Compile() SQL mismatch\nwant: %s\n got: %s", want, sql)
	}
	// Must NOT be a correlated subquery.
	if strings.Contains(sql, "(SELECT") {
		t.Fatalf("Compile() emitted a subquery for a hoisted lookupValue: %s", sql)
	}
}

// TestCompileCASESAFEIDDSLLoweringUsesCONCAT verifies that the DSL SQL compiler
// lowers CASESAFEID's bundle body to CONCAT for string concatenation, not raw
// SQL + (which MySQL/TiDB would treat as numeric addition). The bundle body
// uses & (Salesforce text-concat operator) which the DSL parser handles as a
// separate binary operator and the DSL compiler lowers to CONCAT().
func TestCompileCASESAFEIDDSLLoweringUsesCONCAT(t *testing.T) {
	executables := testCASESAFEIDFunctionExecutables(t)
	ast, err := Parse(`CASESAFEID(RecordId__c)`)
	if err != nil {
		t.Fatalf("Parse() error = %v", err)
	}

	got, err := Compile(ast, &CompileContext{
		TableAlias:          "t",
		QualifyFields:       true,
		FunctionExecutables: executables,
	})
	if err != nil {
		t.Fatalf("Compile with bundle executables error = %v", err)
	}

	// The SQL output must contain CONCAT (from the & → CONCAT lowering)
	if !strings.Contains(got, "CONCAT") {
		t.Fatalf("Compile output = %q, want CONCAT for string concatenation", got)
	}
	// The SQL output must NOT contain raw + used as string concatenation
	// (numeric arithmetic + inside MID is fine).
	if strings.Contains(got, " + ") {
		// Allow + inside a function call (MID's numeric args) but not
		// + concatenating strings directly. A rough check: if + appears
		// outside CONCAT, flag it.
		// Simplest check: no raw + followed by MID/string lit
		if strings.Contains(got, "+ MID") || strings.Contains(got, "+ \"") || strings.Contains(got, "+ '") || strings.Contains(got, ")+") {
			t.Fatalf("Compile output contains suspicious raw + for concat: %q", got)
		}
	}
}

func testCASESAFEIDFunctionExecutables(t *testing.T) *formula.ExecutableFunctionRegistry {
	t.Helper()
	bundle, err := formula.ParseFunctionBundlePayload([]byte(`{
		"functions": [
			{
				"name": "CASESAFEID",
				"minArgs": 1,
				"maxArgs": 1,
				"return": { "kind": "fixed", "type": "string" },
				"args": [
					{ "type": "string", "nullable": true }
				],
				"evaluator": {
					"kind": "formula_expr_v1",
					"params": ["arg0"],
					"body": "IF(ISBLANK(arg0), arg0, IF(LEN(arg0) == 18, arg0, IF(LEN(arg0) == 15, arg0 & MID(\"ABC\", 1 + 1, 1) & MID(\"ABC\", 1 + 1, 1) & MID(\"ABC\", 1 + 1, 1), arg0)))"
				}
			}
		]
	}`))
	if err != nil {
		t.Fatalf("ParseFunctionBundlePayload: %v", err)
	}
	registry, err := formula.ExecutableFunctionRegistryWithBundles([]formula.FunctionBundle{bundle})
	if err != nil {
		t.Fatalf("ExecutableFunctionRegistryWithBundles: %v", err)
	}
	return registry
}