package analytics

import (
	"strings"
	"testing"

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

// TestFormulaDSLToSQLByteParityGolden is a CHARACTERIZATION golden that byte-pins
// the CURRENT output of the ANALYTICS formula-DSL SQL emitter
// (formula_dsl_compiler.go: Compile -> compile -> compileFuncCall and the
// INCLUDES branch at compileFuncCall:1333 -> compileIncludesMultiTokenSQL:1440 /
// utils.MultiPicklistJSONContainsSQL). It is the PR-0b sibling of the kernel
// golden (nexuscrm-kernel/pkg/formula/tosql_byte_parity_golden_test.go, PR-0a).
//
// WHY THIS EXISTS SEPARATELY FROM THE KERNEL GOLDEN: the analytics emitter is a
// SECOND, PARALLEL implementation of INCLUDES SQL lowering. It is NOT derived
// from the spec registry's SQL-lowering — it re-implements the multi-picklist
// membership predicate inline. So it can silently drift from the kernel emitter.
// This golden pins it INDEPENDENTLY, before the upcoming INCLUDES SQL-lowering
// carve, so that carve (which unifies the two emitters) has a byte-exact oracle
// on BOTH sides and cannot regress the analytics one unnoticed.
//
// TWO STRUCTURAL DIVERGENCES FROM THE KERNEL EMITTER — captured here on purpose,
// they are the reason a separate golden is needed:
//
//  1. NO BOUND ARGS. The analytics compiler emits SQL string LITERALS inline
//     (utils.EscapeSQLString wrapped in single quotes) instead of "?"
//     placeholders. Compile returns (string, error) — there is no args slice.
//     So the analytics INCLUDES multi-token SQL embeds JSON_QUOTE('North') /
//     JSON_QUOTE('East') directly, whereas the kernel emitter produces
//     JSON_QUOTE(?) with args ["North","East"]. Same predicate skeleton, different
//     value binding — a direct divergence the carve must reconcile.
//
//  2. DIFFERENT BASE-FUNCTION COVERAGE. Many functions that are EVAL-ONLY in the
//     kernel walker (TEXT/LEFT/RIGHT/MID/DATE/DATEVALUE/DATETIMEVALUE/SUBSTITUTE/
//     FIND/BLANKVALUE/ROUND/ABS/CEILING/MOD) DO have a SQL lowering here, and a
//     few kernel-supported forms differ (NOT -> "(NOT x)" not "NOT(x)";
//     CONTAINS -> LIKE CONCAT('%',x,'%') not "x LIKE ?"; TRUTHY is UNSUPPORTED
//     here). Each is pinned below so the carve is judged against what analytics
//     actually does today, not the kernel's shape.
//
// DISCIPLINE: this pins behavior as it is TODAY, warts included. Surprising
// output is flagged with an "ODDITY:" comment and captured as-is, NOT normalized.
// Do not "fix" a row to make it prettier — a characterization change is a real
// behavior change and belongs in the carve PR with its own justification.
//
// DRIVER: every row is Parse(expr) then Compile(ast, ctx). ctx uses
// QualifyFields:false (bare backticked columns, e.g. `Regions__c`) so the SQL is
// readable and stable, FieldTypes marks Regions__c as multipicklist, and
// FunctionExecutables carries an INCLUDES bundle (INCLUDES is an L1 runtime
// bundle, absent from the L0 default catalog — the compiler fails closed without
// it, mirroring production). The bundle registry Clones the default catalog, so
// every base function's arity still resolves through the same context.

type analyticsToSQLGoldenRow struct {
	name string
	expr string

	wantSQL string

	// wantErrContains, when non-empty, asserts an error whose message contains
	// this substring and asserts the row produced no SQL.
	wantErrContains string

	// wantParseErr asserts the row fails at Parse (before Compile). Used for the
	// MIN/MAX aggregate-parse divergence rows.
	wantParseErrContains string
}

// The multipicklist LHS every INCLUDES row resolves to under QualifyFields:false.
const analyticsIncludesFieldSQL = "`Regions__c`"

// ajc is the single-token analytics INCLUDES predicate: the compiler passes the
// RHS as an inline SQL literal/expression string (NOT a "?" placeholder), so the
// golden expectation is built from the SAME helper the compiler uses, with the
// literal value baked in. valueSQL is the already-SQL-quoted RHS, e.g. "'North'"
// or a backticked column "`WantedTag__c`".
func ajc(valueSQL string) string {
	return utils.MultiPicklistJSONContainsSQL(analyticsIncludesFieldSQL, valueSQL, true)
}

// ajcField builds the single-token predicate over an explicit field SQL (used by
// the field-arg-is-literal row where the LHS is itself a quoted literal).
func ajcField(fieldSQL, valueSQL string) string {
	return utils.MultiPicklistJSONContainsSQL(fieldSQL, valueSQL, true)
}

// ajcAll wraps the all-token AND form exactly as MultiPicklistJSONContainsAllSQL
// does (single predicate returned unwrapped; >=2 AND-joined inside parens),
// again with inline literal token SQL (no placeholders).
func ajcAll(fieldSQL string, valueSQLs ...string) string {
	return utils.MultiPicklistJSONContainsAllSQL(fieldSQL, valueSQLs)
}

func analyticsToSQLGoldenRows() []analyticsToSQLGoldenRow {
	return []analyticsToSQLGoldenRow{
		// ------------------------------------------------------------------
		// INCLUDES — the carve target. Exhaustive over the string-literal
		// multi-token path (compileIncludesMultiTokenSQL) and the single-value
		// compiled-RHS path (utils.MultiPicklistJSONContainsSQL, compiler:1351).
		// The analytics compiler bakes each RHS token in as an inline SQL literal
		// via utils.EscapeSQLString — there are NO bound args on any of these.
		// ------------------------------------------------------------------

		// Three-token string literal -> MULTI-TOKEN path. AND-joined, one inline
		// JSON_QUOTE('<token>') per token; tokens trimmed+deduped by
		// dedupeTrimmedFormulaStrings.
		{
			name:    "INCLUDES/multi_token_three",
			expr:    `INCLUDES(Regions__c, "North;East;West")`,
			wantSQL: ajcAll(analyticsIncludesFieldSQL, "'North'", "'East'", "'West'"),
		},
		{
			name:    "INCLUDES/multi_token_two",
			expr:    `INCLUDES(Regions__c, "North;East")`,
			wantSQL: ajcAll(analyticsIncludesFieldSQL, "'North'", "'East'"),
		},

		// Single-token string literal, NO semicolon -> single-value path
		// (compiler:1351). RHS is compiled to the inline literal 'North'.
		{
			name:    "INCLUDES/single_token_string_literal",
			expr:    `INCLUDES(Regions__c, "North")`,
			wantSQL: ajc("'North'"),
		},

		// Compiled-RHS: bare identifier (dynamic column). The RHS column SQL is
		// substituted directly into JSON_QUOTE(...). No literal quoting.
		{
			name:    "INCLUDES/dynamic_identifier_rhs",
			expr:    `INCLUDES(Regions__c, WantedTag__c)`,
			wantSQL: ajc("`WantedTag__c`"),
		},

		// Compiled-RHS: integer literal. ODDITY: INCLUDES is declared string/string
		// in the bundle spec, but the compiler does not type-check the compiled RHS
		// on the single-value path, so a NumberLit flows straight through as the
		// bare SQL token 42 inside JSON_QUOTE(42). Captured as-is.
		{
			name:    "INCLUDES/integer_literal_rhs_passthrough",
			expr:    `INCLUDES(Regions__c, 42)`,
			wantSQL: ajc("42"),
		},

		// Single-token string literal with surrounding whitespace, NO semicolon ->
		// single-value path. ODDITY: whitespace is NOT trimmed on this path
		// (contrast the multi-token path, which trims each token). The literal is
		// emitted verbatim including the padding.
		{
			name:    "INCLUDES/single_literal_whitespace_not_trimmed",
			expr:    `INCLUDES(Regions__c, "  North  ")`,
			wantSQL: ajc("'  North  '"),
		},

		// Empty string RHS -> no semicolon -> single-value path with an empty
		// inline literal ''.
		{
			name:    "INCLUDES/empty_string_rhs",
			expr:    `INCLUDES(Regions__c, "")`,
			wantSQL: ajc("''"),
		},

		// RHS is exactly ";" -> HAS a semicolon so it takes the multi-token path,
		// but split+trim+dedup yields ZERO tokens ->
		// MultiPicklistJSONContainsAllSQL(field, nil) == "0 = 1". ODDITY: a
		// semicolon-only RHS compiles to a constant-false predicate. Captured as-is.
		{
			name:    "INCLUDES/semicolon_only_rhs_is_constant_false",
			expr:    `INCLUDES(Regions__c, ";")`,
			wantSQL: "0 = 1",
		},

		// RHS "North;;East" -> empty middle token dropped by dedupeTrimmed -> 2
		// tokens.
		{
			name:    "INCLUDES/multi_token_empty_middle_dropped",
			expr:    `INCLUDES(Regions__c, "North;;East")`,
			wantSQL: ajcAll(analyticsIncludesFieldSQL, "'North'", "'East'"),
		},

		// RHS "North; East ;West" -> each token trimmed -> 3 clean tokens.
		{
			name:    "INCLUDES/multi_token_trimmed",
			expr:    `INCLUDES(Regions__c, "North; East ;West")`,
			wantSQL: ajcAll(analyticsIncludesFieldSQL, "'North'", "'East'", "'West'"),
		},

		// RHS "North;North" -> HAS a semicolon so multi-token path, but dedup
		// collapses to ONE token. MultiPicklistJSONContainsAllSQL returns the single
		// predicate UNWRAPPED (no parens), so this is byte-identical to the
		// single-token literal form. Pins the dedup+unwrap.
		{
			name:    "INCLUDES/multi_token_deduped_to_single_unwrapped",
			expr:    `INCLUDES(Regions__c, "North;North")`,
			wantSQL: ajc("'North'"),
		},

		// Field arg is ALSO a string literal with a semicolon. ODDITY: unlike the
		// kernel emitter (which re-appends the field's bound args once per token),
		// the analytics compiler bakes the field's literal SQL in ONCE and reuses
		// that same field-SQL fragment for every token — no per-token duplication,
		// because there are no bound args to duplicate. The field literal's own
		// semicolon does NOT trigger token-splitting (only the RHS is split).
		{
			name: "INCLUDES/field_literal_reused_per_token_no_arg_duplication",
			expr: `INCLUDES("North;East", "North;East")`,
			wantSQL: "(" +
				ajcField("'North;East'", "'North'") + " AND " +
				ajcField("'North;East'", "'East'") + ")",
		},

		// RHS literal carrying SQL metacharacters -> single-value path, escaped by
		// utils.EscapeSQLString (single-quote doubled, backslash doubled). Pins the
		// inline-literal escaping the kernel emitter never exercises (it binds args).
		{
			name:    "INCLUDES/single_literal_sql_escaped",
			expr:    `INCLUDES(Regions__c, "O'Reilly")`,
			wantSQL: ajc("'O''Reilly'"),
		},

		// INCLUDES arity errors: the SPEC arity gate (checkFunctionArity via the
		// bundle SpecRegistry) fires before any SQL is built.
		{
			name:            "INCLUDES/error_arity_one",
			expr:            `INCLUDES(Regions__c)`,
			wantErrContains: "INCLUDES expects 2 args, got 1",
		},
		{
			name:            "INCLUDES/error_arity_three",
			expr:            `INCLUDES(Regions__c, "A", "B")`,
			wantErrContains: "INCLUDES expects 2 args, got 3",
		},

		// ------------------------------------------------------------------
		// Base breadth. One representative row per base-function case in
		// compileFuncCall, plus operator / null / concat forms handled directly
		// by compileBinaryOp / compileUnaryOp / compileLogicalOp. These MUST NOT
		// drift when the INCLUDES branch is carved out.
		// ------------------------------------------------------------------

		{name: "base/UPPER", expr: `UPPER(Name)`, wantSQL: "UPPER(`Name`)"},
		{name: "base/LOWER", expr: `LOWER(Name)`, wantSQL: "LOWER(`Name`)"},
		{name: "base/LEN_to_CHAR_LENGTH", expr: `LEN(Name)`, wantSQL: "CHAR_LENGTH(`Name`)"},
		// IF lowers to a searched CASE (not MySQL IF()). ODDITY vs kernel, which
		// emits IF(cond, then, else); analytics uses CASE WHEN ... THEN ... ELSE.
		{name: "base/IF_to_searched_CASE", expr: `IF(Amount > 0, 1, 2)`, wantSQL: "CASE WHEN (`Amount` > 0) THEN 1 ELSE 2 END"},
		{name: "base/TODAY_to_CURDATE", expr: `TODAY()`, wantSQL: "CURDATE()"},
		{name: "base/NOW", expr: `NOW()`, wantSQL: "NOW()"},
		// DATE_ADD wraps the day count in extra parens: INTERVAL (5) DAY.
		{name: "base/DATE_ADD", expr: `DATE_ADD(CreatedDate, 5)`, wantSQL: "DATE(DATE_ADD(`CreatedDate`, INTERVAL (5) DAY))"},
		{
			name: "base/DATE_ADD_MONTHS_sf_clamp",
			expr: `__DATE_ADD_MONTHS__(CreatedDate, 3)`,
			wantSQL: "DATE(GREATEST(DATE_ADD(`CreatedDate`, INTERVAL 3 MONTH), " +
				"IFNULL(DATE_SUB(DATE_ADD(IFNULL(DATE_ADD(`CreatedDate`, INTERVAL 1 DAY), " +
				"DATE('9999-12-31')), INTERVAL 3 MONTH), INTERVAL 1 DAY), DATE('9999-12-31'))))",
		},
		{name: "base/DATE_ADD_MONTHS_target_clamp", expr: `__DATE_ADD_MONTHS_TARGET_CLAMP__(CreatedDate, 3)`, wantSQL: "DATE(DATE_ADD(`CreatedDate`, INTERVAL 3 MONTH))"},
		{name: "base/DATE_DIFF_DAYS", expr: `__DATE_DIFF_DAYS__(CloseDate, CreatedDate)`, wantSQL: "DATEDIFF(`CloseDate`, `CreatedDate`)"},
		// CONTAINS -> LIKE CONCAT('%', x, '%') (inline, no args). ODDITY vs kernel
		// ("x LIKE ?" + arg %foo%).
		{name: "base/CONTAINS_fn_to_LIKE_CONCAT", expr: `CONTAINS(Name, "foo")`, wantSQL: "(`Name` LIKE CONCAT('%', 'foo', '%'))"},
		// BEGINS -> LIKE CONCAT(prefix, '%'). Analytics has BEGINS (prefix match)
		// but no STARTS_WITH/ENDS_WITH function cases.
		{name: "base/BEGINS_fn_to_LIKE_CONCAT", expr: `BEGINS(Name, "foo")`, wantSQL: "(`Name` LIKE CONCAT('foo', '%'))"},
		// NOT -> "(NOT x)". ODDITY vs kernel ("NOT(x)").
		{name: "base/NOT_fn_paren_form", expr: `NOT(IsActive)`, wantSQL: "(NOT `IsActive`)"},
		{name: "base/AND_variadic", expr: `AND(A, B)`, wantSQL: "(`A` AND `B`)"},
		{name: "base/OR_variadic", expr: `OR(A, B)`, wantSQL: "(`A` OR `B`)"},
		// ISBLANK on an unknown-typed field -> CAST(... AS CHAR) = '' fallback.
		{name: "base/ISBLANK_unknown_cast_fallback", expr: `ISBLANK(Name)`, wantSQL: "(IFNULL(CAST(`Name` AS CHAR) = '', TRUE))"},
		{name: "base/ISNULL_fn", expr: `ISNULL(Name)`, wantSQL: "(`Name` IS NULL)"},
		// NULLVALUE -> IFNULL(field, def). ODDITY vs kernel (COALESCE(NULLIF(...))).
		{name: "base/NULLVALUE_to_IFNULL", expr: `NULLVALUE(Name, "x")`, wantSQL: "IFNULL(`Name`, 'x')"},
		// BLANKVALUE -> IFNULL(NULLIF(field, ''), def). Eval-only in the kernel.
		{name: "base/BLANKVALUE_to_IFNULL_NULLIF", expr: `BLANKVALUE(Name, "x")`, wantSQL: "IFNULL(NULLIF(`Name`, ''), 'x')"},
		{name: "base/CASE", expr: `CASE(Stage, "a", 1, "b", 2, 9)`, wantSQL: "CASE `Stage` WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 9 END"},
		{name: "base/YEAR", expr: `YEAR(CreatedDate)`, wantSQL: "YEAR(`CreatedDate`)"},
		{name: "base/MONTH", expr: `MONTH(CreatedDate)`, wantSQL: "MONTH(`CreatedDate`)"},
		{name: "base/DAY", expr: `DAY(CreatedDate)`, wantSQL: "DAY(`CreatedDate`)"},
		// LPAD/RPAD are plain 3-arg passthroughs here (no guard wrapper). ODDITY vs
		// kernel, whose LPAD/RPAD emit an IF(...) NULL-guard and repeat args.
		{name: "base/LPAD_plain", expr: `LPAD(Name, 5, "*")`, wantSQL: "LPAD(`Name`, 5, '*')"},
		{name: "base/RPAD_plain", expr: `RPAD(Name, 5, "*")`, wantSQL: "RPAD(`Name`, 5, '*')"},

		// Functions that are EVAL-ONLY (error rows) in the kernel golden but DO
		// have a SQL lowering in analytics — the core base-coverage divergence.
		{name: "base/ROUND", expr: `ROUND(Amount, 2)`, wantSQL: "ROUND(`Amount`, 2)"},
		{name: "base/ABS", expr: `ABS(Amount)`, wantSQL: "ABS(`Amount`)"},
		{name: "base/CEILING_to_CEIL", expr: `CEILING(Amount)`, wantSQL: "CEIL(`Amount`)"},
		{name: "base/MOD", expr: `MOD(Amount, 2)`, wantSQL: "MOD(`Amount`, 2)"},
		{name: "base/TEXT_numeric_cast", expr: `TEXT(Amount)`, wantSQL: "COALESCE(CAST(`Amount` AS CHAR), '')"},
		{name: "base/LEFT", expr: `LEFT(Name, 2)`, wantSQL: "LEFT(`Name`, 2)"},
		{name: "base/RIGHT", expr: `RIGHT(Name, 2)`, wantSQL: "RIGHT(`Name`, 2)"},
		{name: "base/MID_to_SUBSTRING", expr: `MID(Name, 2, 3)`, wantSQL: "SUBSTRING(`Name`, 2, 3)"},
		{name: "base/DATE_to_STR_TO_DATE", expr: `DATE(2026, 1, 1)`, wantSQL: "STR_TO_DATE(CONCAT(2026,'-',1,'-',1), '%Y-%m-%d')"},
		{name: "base/DATEVALUE_to_DATE", expr: `DATEVALUE(CreatedDate)`, wantSQL: "DATE(`CreatedDate`)"},
		{name: "base/DATETIMEVALUE_to_TIMESTAMP", expr: `DATETIMEVALUE(CreatedDate)`, wantSQL: "TIMESTAMP(`CreatedDate`)"},
		{name: "base/SUBSTITUTE_to_REPLACE", expr: `SUBSTITUTE(Name, "a", "b")`, wantSQL: "REPLACE(`Name`, 'a', IFNULL('b', ''))"},
		{name: "base/FIND_to_LOCATE", expr: `FIND("x", Name)`, wantSQL: "LOCATE('x', `Name`)"},
		{name: "base/FIND_to_LOCATE_with_start", expr: `FIND("x", Name, 3)`, wantSQL: "LOCATE('x', `Name`, 3)"},
		{name: "base/VALUE_to_CAST_DECIMAL", expr: `VALUE(Name)`, wantSQL: "CAST(`Name` AS DECIMAL(18,6))"},
		{name: "base/FLOOR", expr: `FLOOR(Amount)`, wantSQL: "FLOOR(`Amount`)"},
		// ISNUMBER -> REGEXP. ODDITY: the emitted regex carries an escaped backslash
		// pair (\\.) so the literal decimal-point class survives to TiDB.
		{name: "base/ISNUMBER_to_REGEXP", expr: `ISNUMBER(Name)`, wantSQL: "(`Name` REGEXP '^-?[0-9]+(\\\\.[0-9]+)?$')"},
		{name: "base/TRIM", expr: `TRIM(Name)`, wantSQL: "TRIM(`Name`)"},
		// BR -> a literal newline SQL string.
		{name: "base/BR_newline_literal", expr: `BR()`, wantSQL: "'\\n'"},

		// dslPassthrough allowlist: COALESCE / CONCAT emitted verbatim.
		{name: "passthrough/COALESCE", expr: `COALESCE(A, B)`, wantSQL: "COALESCE(`A`, `B`)"},
		{name: "passthrough/CONCAT", expr: `CONCAT(A, B)`, wantSQL: "CONCAT(`A`, `B`)"},

		// Operator / null / literal forms handled directly by the walkers.
		// `== null` / `!= null` lower to IS NULL / IS NOT NULL, matching the kernel
		// emitter, Salesforce, and the Go evaluator (blank = NULL is true). NX#3908
		// fixed the prior raw `(field = NULL)` / `(field != NULL)` emission, which
		// under SQL three-valued logic was always UNKNOWN and took the wrong branch.
		{name: "op/eq_null_rewritten_is_null", expr: `Name == null`, wantSQL: "(`Name` IS NULL)"},
		{name: "op/neq_null_rewritten_is_not_null", expr: `Name != null`, wantSQL: "(`Name` IS NOT NULL)"},
		{name: "op/and_lt_gt", expr: `Amount > 0 && Amount < 100`, wantSQL: "((`Amount` > 0) AND (`Amount` < 100))"},
		{name: "op/or_eq", expr: `Name == "x" || Name == "y"`, wantSQL: "((`Name` = 'x') OR (`Name` = 'y'))"},
		{name: "op/arithmetic_plus", expr: `Amount + 1`, wantSQL: "(`Amount` + 1)"},
		// ODDITY vs kernel: the "&" Salesforce concat operator lowers to CONCAT(...)
		// here (correct string concat), whereas the kernel normalizes "&" to "+".
		{name: "op/ampersand_concat_to_CONCAT", expr: `Name & "x"`, wantSQL: "CONCAT(`Name`, 'x')"},
		{name: "op/exponent_to_POWER", expr: `Amount ^ 2`, wantSQL: "POWER(`Amount`, 2)"},
		{name: "op/ne_angle_bracket", expr: `Amount <> 5`, wantSQL: "(`Amount` != 5)"},
		{name: "op/unary_minus", expr: `-Amount`, wantSQL: "(-`Amount`)"},
		// Empty-string equality on an unknown-typed field -> blank-aware CAST
		// fallback (compileSalesforceEmptyStringComparison), same shape as ISBLANK.
		{name: "op/eq_empty_string_unknown_cast_fallback", expr: `Foo__c = ""`, wantSQL: "(IFNULL(CAST(`Foo__c` AS CHAR) = '', TRUE))"},

		// Aggregate marker: emitted as a ##AGG## placeholder the DDL builder later
		// rewrites into a JOIN subquery. Pins the marker format.
		{name: "aggregate/SUM_child_field_marker", expr: `SUM(Child__c.Amount__c)`, wantSQL: "##AGG##SUM##Child__c##Amount__c##_##"},

		// ------------------------------------------------------------------
		// Error rows: functions with no analytics SQL lowering, and arity guards.
		// Pinned so a future carve that accidentally teaches one a SQL form (or
		// drops the error) is caught.
		// ------------------------------------------------------------------

		// TRUTHY IS SUPPORTED in the kernel walker ("(x != 0)") but is UNSUPPORTED
		// in analytics — pinned as an error row to lock that divergence.
		{name: "error/TRUTHY_unsupported", expr: `TRUTHY(IsActive)`, wantErrContains: "unsupported function: TRUTHY"},
		{name: "error/ISPICKVAL_unsupported_without_bundle", expr: `ISPICKVAL(Status, "x")`, wantErrContains: "unsupported function: ISPICKVAL"},
		{name: "error/HYPERLINK_unsupported_without_bundle", expr: `HYPERLINK(A, B)`, wantErrContains: "unsupported function: HYPERLINK"},
		{name: "error/unknown_function", expr: `FOOBAR(Name)`, wantErrContains: "unsupported function: FOOBAR"},
		// SUM over a bare (non-Object.Field) arg parses to a FuncCall{SUM}, which
		// has no compileFuncCall case -> unsupported. (The aggregate marker form
		// requires an Object.Field child arg, pinned above.)
		{name: "error/SUM_bare_arg_unsupported", expr: `SUM(Amount)`, wantErrContains: "unsupported function: SUM"},
		// SUBSTITUTE's 4-arg (instance-number) form is a valid catalog arity but
		// TiDB REPLACE cannot express it, so the compiler fails loudly.
		{name: "error/SUBSTITUTE_instance_number_unsupported", expr: `SUBSTITUTE(Name, "a", "b", 2)`, wantErrContains: "instance-number 4th argument is not supported"},
		// Local arity guards on the DSL-only helpers (non-catalog message form).
		{name: "error/ISNUMBER_arity", expr: `ISNUMBER(A, B)`, wantErrContains: "ISNUMBER requires 1 argument"},
		{name: "error/FLOOR_arity", expr: `FLOOR(A, B)`, wantErrContains: "FLOOR requires 1 argument"},
		{name: "error/TRIM_arity", expr: `TRIM(A, B)`, wantErrContains: "TRIM requires 1 argument"},
		{name: "error/VALUE_arity", expr: `VALUE(A, B)`, wantErrContains: "VALUE requires 1 argument"},
		// Catalog arity guard (spec message form) on a cataloged function.
		{name: "error/ROUND_arity_from_catalog", expr: `ROUND(Amount)`, wantErrContains: "ROUND expects 2 args, got 1"},

		// ------------------------------------------------------------------
		// PARSE-level divergences: MIN/MAX with two scalar args never reach the
		// compileFuncCall MIN/MAX case, because the parser routes MIN/MAX/SUM/COUNT/
		// AVG through parseAggregateOrFunc, which interprets the 2nd comma-arg as an
		// aggregate foreign key and rejects it. So the LEAST/GREATEST lowering in
		// compileFuncCall (case "MAX","MIN") is UNREACHABLE from Parse+Compile for
		// this call shape. Pinned as parse-error rows so the unreachability is
		// documented and locked. See the report for the reachability note.
		// ------------------------------------------------------------------
		{name: "parse/MIN_two_args_is_aggregate_reject", expr: `MIN(A, B)`, wantParseErrContains: "aggregate foreign key requires a child object field argument"},
		{name: "parse/MAX_two_args_is_aggregate_reject", expr: `MAX(A, B)`, wantParseErrContains: "aggregate foreign key requires a child object field argument"},

		// ------------------------------------------------------------------
		// NULL-STRICT CONTROL FLOW (null provenance). What NULL means at an IF/CASE
		// branch-selecting boundary.
		//
		// SQL has one NULL; Salesforce has two things it could mean. A BLANK
		// operand makes a comparison FALSE and selects the else arm. Undefined
		// arithmetic (x/0) is #Error and poisons the whole formula. A plain
		// `CASE WHEN c THEN a ELSE b END` absorbs both as "false", which is
		// correct for the first and a silent wrong answer for the second — the
		// production defect where a 0/0 prepayment ratio bought an unearned
		// discount tier.
		//
		// The compiler propagates NULL strictly ONLY when it can prove the
		// condition's NULL can have come from nothing but arithmetic. These
		// four rows pin both sides of that decision. The unchanged rows are as
		// load-bearing as the changed ones: they are what makes the gate a gate
		// rather than a blanket.
		//
		// NOTE the added FieldTypes entries below (NullStrict*): the numeric
		// ones are what #4152 blank-as-zero coalesces, and that COALESCE is
		// precisely why a division over them can only ever NULL via 0/0.
		// ------------------------------------------------------------------

		// Field denominator: the divisor can be 0 or blank-as-zero 0, so the
		// quotient can be undefined. NULL means #Error here => propagate it.
		{
			name: "nullstrict/IF_field_denominator_division_propagates",
			expr: `IF(NullStrictNum__c / NullStrictDen__c > 0.5, 1, 2)`,
			wantSQL: "CASE (((COALESCE(`NullStrictNum__c`, 0) / COALESCE(`NullStrictDen__c`, 0)) > 0.5)) " +
				"WHEN TRUE THEN 1 WHEN FALSE THEN 2 END",
		},

		// Non-zero number literal denominator: the quotient is total, so the
		// condition cannot be undefined. Nothing to propagate => today's SQL.
		{
			name:    "nullstrict/IF_literal_denominator_unchanged",
			expr:    `IF(NullStrictNum__c / 10 > 0.5, 1, 2)`,
			wantSQL: "CASE WHEN ((COALESCE(`NullStrictNum__c`, 0) / 10) > 0.5) THEN 1 ELSE 2 END",
		},

		// A blank-capable text leaf sits in the same condition as the division.
		// Now NULL is ambiguous, so the compiler must NOT guess: a blank text
		// comparison is FALSE in Salesforce, and blanking the formula here
		// would be wrong in the OPPOSITE direction from the null provenance defect.
		{
			name: "nullstrict/IF_blank_leaf_in_condition_unchanged",
			expr: `IF(AND(NullStrictText__c = "Direct", NullStrictNum__c / NullStrictDen__c > 0.5), 1, 2)`,
			wantSQL: "CASE WHEN ((`NullStrictText__c` = 'Direct') AND " +
				"((COALESCE(`NullStrictNum__c`, 0) / COALESCE(`NullStrictDen__c`, 0)) > 0.5)) " +
				"THEN 1 ELSE 2 END",
		},

		// A CASE subject is NOT made strict, deliberately: it cannot be bound
		// once, and a second substitution reintroduces exponential growth when
		// gated CASEs nest. See TestCompileCaseSubjectStaysAbsorbing.
		{
			name: "nullstrict/CASE_error_capable_subject_stays_absorbing",
			expr: `CASE(NullStrictNum__c / NullStrictDen__c, 1, 10, 2, 20, 99)`,
			wantSQL: "CASE (COALESCE(`NullStrictNum__c`, 0) / COALESCE(`NullStrictDen__c`, 0)) " +
				"WHEN 1 THEN 10 WHEN 2 THEN 20 ELSE 99 END",
		},

		// An even-arity CASE has no ELSE, so on a no-match it returns NULL that
		// no operand produced. Every leaf in this condition is blank-safe, so
		// without the nil-Default blank mark the gate reads the whole thing as
		// pure #Error and fires — blanking a row whose division is total.
		// NULL means "no branch matched" here, which is an absent value, not
		// #Error, so the gate must stay shut and the emission is today's.
		{
			name: "nullstrict/IF_case_without_else_arm_unchanged",
			expr: `IF(CASE(NullStrictAlt__c, 1, 10, 2, 20) + NullStrictNum__c / NullStrictDen__c > 0, 1, 2)`,
			wantSQL: "CASE WHEN ((CASE COALESCE(`NullStrictAlt__c`, 0) WHEN 1 THEN 10 WHEN 2 THEN 20 END" +
				" + (COALESCE(`NullStrictNum__c`, 0) / COALESCE(`NullStrictDen__c`, 0))) > 0)" +
				" THEN 1 ELSE 2 END",
		},
	}
}

func TestFormulaDSLToSQLByteParityGolden(t *testing.T) {
	execs := analyticsGoldenINCLUDESExecutables(t)

	// FieldTypes marks the multi-picklist field so the INCLUDES rows resolve a
	// multipicklist LHS. QualifyFields:false keeps identifiers as bare backticked
	// columns. The context is shared and read-only across rows (no map mutation),
	// so ordering is deterministic.
	newCtx := func() *CompileContext {
		return &CompileContext{
			QualifyFields: false,
			FieldTypes: map[string]string{
				"Regions__c": string(constants.FieldTypeMultiPicklist),
				// null provenance nullstrict rows only. Deliberately distinct identifiers
				// so adding them cannot perturb any pre-existing row: no other
				// row in this file references a NullStrict* field.
				"NullStrictNum__c":  string(constants.FieldTypeDouble),
				"NullStrictDen__c":  string(constants.FieldTypeDouble),
				"NullStrictAlt__c":  string(constants.FieldTypeDouble),
				"NullStrictText__c": string(constants.FieldTypeString),
			},
			FunctionExecutables: execs,
		}
	}

	for _, row := range analyticsToSQLGoldenRows() {
		row := row
		t.Run(row.name, func(t *testing.T) {
			ast, parseErr := Parse(row.expr)

			if row.wantParseErrContains != "" {
				if parseErr == nil {
					t.Fatalf("expr %q: expected parse error containing %q, got nil (ast=%#v)",
						row.expr, row.wantParseErrContains, ast)
				}
				if !strings.Contains(parseErr.Error(), row.wantParseErrContains) {
					t.Fatalf("expr %q: parse error = %q, want substring %q",
						row.expr, parseErr.Error(), row.wantParseErrContains)
				}
				return
			}

			if parseErr != nil {
				t.Fatalf("expr %q: unexpected parse error: %v", row.expr, parseErr)
			}

			gotSQL, err := Compile(ast, newCtx())

			if row.wantErrContains != "" {
				if err == nil {
					t.Fatalf("expr %q: expected compile error containing %q, got nil (sql=%q)",
						row.expr, row.wantErrContains, gotSQL)
				}
				if !strings.Contains(err.Error(), row.wantErrContains) {
					t.Fatalf("expr %q: compile error = %q, want substring %q",
						row.expr, err.Error(), row.wantErrContains)
				}
				if gotSQL != "" {
					t.Fatalf("expr %q: expected empty SQL on error, got %q", row.expr, gotSQL)
				}
				return
			}

			if err != nil {
				t.Fatalf("expr %q: unexpected compile error: %v", row.expr, err)
			}
			if gotSQL != row.wantSQL {
				t.Fatalf("expr %q SQL mismatch:\n got: %s\nwant: %s", row.expr, gotSQL, row.wantSQL)
			}
		})
	}
}

// analyticsGoldenINCLUDESExecutables builds the INCLUDES L1 runtime bundle
// registry the golden compiles under. INCLUDES is absent from the L0 default
// catalog, so the compiler fails closed without it (mirroring production). The
// bundle registry Clones the default catalog, so every base function's arity
// still resolves through the same context. Mirrors testINCLUDESFunctionExecutables
// in formula_dsl_compiler_test.go; kept local so the golden is self-contained.
func analyticsGoldenINCLUDESExecutables(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
}