package expression

import (
	"strings"
	"testing"
)

// TestISBLANKSQLEmission verifies that the SQL walker's formula ISBLANK case
// stays on the Salesforce formula nil-or-empty contract.
func TestISBLANKSQLEmission(t *testing.T) {
	sql, _, err := ToSQL("ISBLANK(Name)")
	if err != nil {
		t.Fatalf("ToSQL(\"ISBLANK(Name)\") failed: %v", err)
	}
	if !strings.Contains(sql, "CAST") {
		t.Errorf("ISBLANK SQL predicate must include CAST for numeric coercion safety; got: %s", sql)
	}
	if strings.Contains(sql, "REPLACE(") {
		t.Errorf("formula ISBLANK must not normalize whitespace-only strings; got: %s", sql)
	}
}

func TestFLOWISBLANKSQLEmission(t *testing.T) {
	sql, _, err := ToSQL("FLOWISBLANK(Name)")
	if err != nil {
		t.Fatalf("ToSQL(\"FLOWISBLANK(Name)\") failed: %v", err)
	}
	if !strings.Contains(sql, "CAST") {
		t.Errorf("FLOWISBLANK SQL predicate must include CAST for numeric coercion safety; got: %s", sql)
	}
	if !strings.Contains(sql, "REPLACE(") {
		t.Errorf("FLOWISBLANK SQL predicate must normalize whitespace-only strings; got: %s", sql)
	}
	for _, token := range []string{"CHAR(9)", "CHAR(10)", "CHAR(13)"} {
		if !strings.Contains(sql, token) {
			t.Errorf("FLOWISBLANK SQL predicate missing %s whitespace normalization; got: %s", token, sql)
		}
	}
}

func TestSQLWalkerUsesFormulaSpecArity(t *testing.T) {
	tests := []struct {
		name     string
		expr     string
		contains string
	}{
		{
			name:     "zero arg formula function rejects extra argument",
			expr:     "TODAY(1)",
			contains: "TODAY expects 0 args",
		},
		{
			name:     "generic function emission rejects too many args",
			expr:     `UPPER(Name, "x")`,
			contains: "UPPER expects 1 args",
		},
		{
			name:     "variadic function rejects below minimum",
			expr:     "AND()",
			contains: "AND expects >=1 args",
		},
		{
			name:     "predicate-backed function uses catalog predicate",
			expr:     `CASE(StageName, "Open", 1, "Closed", 0)`,
			contains: "CASE requires an even number of arguments",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			_, _, err := ToSQL(tt.expr)
			if err == nil {
				t.Fatalf("ToSQL(%q) error = nil, want arity error", tt.expr)
			}
			if !strings.Contains(err.Error(), tt.contains) {
				t.Fatalf("ToSQL(%q) error = %q, want it to contain %q", tt.expr, err.Error(), tt.contains)
			}
		})
	}
}

func TestSQLWalkerRejectsMemberPathIdentifiersByDefault(t *testing.T) {
	_, _, err := ToSQLWithMapper("Account.Industry == 'Technology'", func(identifier string) string {
		t.Fatalf("mapper should not be called for default member path lowering, got %q", identifier)
		return identifier
	})
	if err == nil || !strings.Contains(err.Error(), "unsupported member path") {
		t.Fatalf("ToSQLWithMapper member path error = %v, want fail-closed unsupported member path", err)
	}
}

func TestSQLWalkerMapsMemberPathIdentifiersWhenOptedIn(t *testing.T) {
	sql, args, err := ToSQLWithOptions("Account.Industry == 'Technology'", SQLOptions{
		AllowMemberPaths: true,
		Mapper: func(identifier string) string {
			if identifier != "Account.Industry" {
				t.Fatalf("mapper identifier = %q, want full relationship path", identifier)
			}
			return "`j_account`.`Industry`"
		},
	})
	if err != nil {
		t.Fatalf("ToSQLWithOptions relationship path: %v", err)
	}
	if sql != "(`j_account`.`Industry` = ?)" {
		t.Fatalf("sql = %q, want relationship path predicate", sql)
	}
	if len(args) != 1 || args[0] != "Technology" {
		t.Fatalf("args = %#v, want [Technology]", args)
	}
}

func TestSQLWalkerKeepsFlowHelperArityLocalAndUnsupportedFunctionsFailClosed(t *testing.T) {
	_, _, err := ToSQL("FLOWISBLANK()")
	if err == nil || !strings.Contains(err.Error(), "FLOWISBLANK requires 1 argument") {
		t.Fatalf("ToSQL(FLOWISBLANK()) error = %v, want Flow helper arity error", err)
	}

	_, _, err = ToSQL(`INCLUDES(Regions__c, "North")`)
	if err == nil || !strings.Contains(err.Error(), "unsupported function: INCLUDES") {
		t.Fatalf("ToSQL(INCLUDES(...)) error = %v, want unsupported function without L1 bundle lowerer", err)
	}
}

// TestLPAD_SQLEmitsCoalesce verifies that ToSQL wraps the first LPAD argument in
// COALESCE so that a nullable field reference becomes ” rather than NULL in
// MySQL/TiDB, matching the runtime evaluator's nil-text-as-empty-string semantics.
func TestLPAD_SQLEmitsCoalesce(t *testing.T) {
	sql, _, err := ToSQL(`LPAD(NullableField, 8, "0")`)
	if err != nil {
		t.Fatalf("ToSQL LPAD: unexpected error %v", err)
	}
	if !strings.Contains(sql, "COALESCE") {
		t.Errorf("LPAD SQL must wrap arg0 in COALESCE for nil-text parity; got: %s", sql)
	}
}

// TestRPAD_SQLEmitsCoalesce verifies that ToSQL wraps the first RPAD argument in
// COALESCE so that a nullable field reference becomes ” rather than NULL in
// MySQL/TiDB, matching the runtime evaluator's nil-text-as-empty-string semantics.
func TestRPAD_SQLEmitsCoalesce(t *testing.T) {
	sql, _, err := ToSQL(`RPAD(NullableField, 8, " ")`)
	if err != nil {
		t.Fatalf("ToSQL RPAD: unexpected error %v", err)
	}
	if !strings.Contains(sql, "COALESCE") {
		t.Errorf("RPAD SQL must wrap arg0 in COALESCE for nil-text parity; got: %s", sql)
	}
}

// TestLPAD_SQLNonNullAgreement verifies that ToSQL produces a well-formed LPAD
// expression for a non-null literal, confirming the COALESCE wrapper does not
// break the normal (non-nil) path.
func TestLPAD_SQLNonNullAgreement(t *testing.T) {
	sql, _, err := ToSQL(`LPAD("abc", 6, "0")`)
	if err != nil {
		t.Fatalf("ToSQL LPAD literal: unexpected error %v", err)
	}
	if !strings.Contains(sql, "LPAD") {
		t.Errorf("LPAD SQL should still contain LPAD call; got: %s", sql)
	}
	if !strings.Contains(sql, "COALESCE") {
		t.Errorf("LPAD SQL should still contain COALESCE wrapper; got: %s", sql)
	}
}

// TestLPAD_SQLEmitsNegativeLenGuard verifies that ToSQL wraps the LPAD call in
// an IF guard so that non-positive len produces ” instead of NULL, matching
// the runtime evaluator's len<=0 → "" semantics.
func TestLPAD_SQLEmitsNegativeLenGuard(t *testing.T) {
	sql, _, err := ToSQL(`LPAD(Name, 0, "0")`)
	if err != nil {
		t.Fatalf("ToSQL LPAD zero-len: unexpected error %v", err)
	}
	if !strings.Contains(sql, "IF(") {
		t.Errorf("LPAD SQL must contain IF guard for non-positive len; got: %s", sql)
	}
	if !strings.Contains(sql, "<= 0") {
		t.Errorf("LPAD SQL IF guard must check <= 0; got: %s", sql)
	}
}

// TestRPAD_SQLEmitsNegativeLenGuard verifies that ToSQL wraps the RPAD call in
// an IF guard so that non-positive len produces ” instead of NULL, matching
// the runtime evaluator's len<=0 → "" semantics.
func TestRPAD_SQLEmitsNegativeLenGuard(t *testing.T) {
	sql, _, err := ToSQL(`RPAD(Name, 0, " ")`)
	if err != nil {
		t.Fatalf("ToSQL RPAD zero-len: unexpected error %v", err)
	}
	if !strings.Contains(sql, "IF(") {
		t.Errorf("RPAD SQL must contain IF guard for non-positive len; got: %s", sql)
	}
	if !strings.Contains(sql, "<= 0") {
		t.Errorf("RPAD SQL IF guard must check <= 0; got: %s", sql)
	}
}

// TestLPAD_SQLEmptyPadReturnsNull verifies that the SQL walker emits an outer
// IF guard checking padstr = ” that returns NULL, matching the runtime
// evaluator which returns nil when pad == "".
func TestLPAD_SQLEmptyPadReturnsNull(t *testing.T) {
	sql, _, err := ToSQL(`LPAD(Name, 5, "")`)
	if err != nil {
		t.Fatalf("ToSQL LPAD empty pad: unexpected error %v", err)
	}
	// The outermost IF must check the pad argument against ''
	if !strings.Contains(sql, "= ''") {
		t.Errorf("LPAD SQL must contain pad = '' guard; got: %s", sql)
	}
	// That guard must return NULL (not '' or some other value)
	if !strings.Contains(sql, "NULL") {
		t.Errorf("LPAD SQL pad-empty guard must return NULL; got: %s", sql)
	}
	// The pad guard must appear before the len guard in the emitted SQL
	padIdx := strings.Index(sql, "= ''")
	lenIdx := strings.Index(sql, "<= 0")
	if padIdx == -1 || lenIdx == -1 || padIdx > lenIdx {
		t.Errorf("LPAD SQL pad-empty guard must appear before len<=0 guard; got: %s", sql)
	}
}

// TestRPAD_SQLEmptyPadReturnsNull verifies that the SQL walker emits an outer
// IF guard checking padstr = ” that returns NULL, matching the runtime
// evaluator which returns nil when pad == "".
func TestRPAD_SQLEmptyPadReturnsNull(t *testing.T) {
	sql, _, err := ToSQL(`RPAD(Name, 5, "")`)
	if err != nil {
		t.Fatalf("ToSQL RPAD empty pad: unexpected error %v", err)
	}
	if !strings.Contains(sql, "= ''") {
		t.Errorf("RPAD SQL must contain pad = '' guard; got: %s", sql)
	}
	if !strings.Contains(sql, "NULL") {
		t.Errorf("RPAD SQL pad-empty guard must return NULL; got: %s", sql)
	}
	padIdx := strings.Index(sql, "= ''")
	lenIdx := strings.Index(sql, "<= 0")
	if padIdx == -1 || lenIdx == -1 || padIdx > lenIdx {
		t.Errorf("RPAD SQL pad-empty guard must appear before len<=0 guard; got: %s", sql)
	}
}

// TestLPAD_SQLEmptyPadAndZeroLenReturnsNull verifies that when both pad=” and
// len=0, the SQL returns NULL (pad-empty guard wins), matching the runtime
// evaluator which also returns nil in that case because pad check runs first.
func TestLPAD_SQLEmptyPadAndZeroLenReturnsNull(t *testing.T) {
	sql, _, err := ToSQL(`LPAD(Name, 0, "")`)
	if err != nil {
		t.Fatalf("ToSQL LPAD empty pad zero len: unexpected error %v", err)
	}
	// Pad guard must be present and return NULL
	if !strings.Contains(sql, "= ''") {
		t.Errorf("LPAD SQL (pad='', len=0) must contain pad = '' guard; got: %s", sql)
	}
	if !strings.Contains(sql, "NULL") {
		t.Errorf("LPAD SQL (pad='', len=0) pad-empty guard must return NULL; got: %s", sql)
	}
	// Pad guard must precede len guard so NULL wins over ''
	padIdx := strings.Index(sql, "= ''")
	lenIdx := strings.Index(sql, "<= 0")
	if padIdx == -1 || lenIdx == -1 || padIdx > lenIdx {
		t.Errorf("LPAD SQL (pad='', len=0) pad guard must precede len guard; got: %s", sql)
	}
}

// TestLPAD_SQLNullLenEmitsNullGuard verifies that the SQL walker emits an IS NULL
// guard for the len argument, matching runtime which returns nil when len is nil.
func TestLPAD_SQLNullLenEmitsNullGuard(t *testing.T) {
	sql, _, err := ToSQL(`LPAD(Name, NullableLen__c, "0")`)
	if err != nil {
		t.Fatalf("ToSQL LPAD nullable len: unexpected error %v", err)
	}
	if !strings.Contains(sql, "IS NULL") {
		t.Errorf("LPAD SQL must emit IS NULL guard for nullable len; got: %s", sql)
	}
	if !strings.Contains(sql, "NULL") {
		t.Errorf("LPAD SQL IS NULL guard must return NULL; got: %s", sql)
	}
	// IS NULL guard for len must appear before <= 0 guard
	nullIdx := strings.Index(sql, "IS NULL")
	lenIdx := strings.Index(sql, "<= 0")
	if nullIdx == -1 || lenIdx == -1 || nullIdx > lenIdx {
		t.Errorf("LPAD SQL len IS NULL guard must precede len<=0 guard; got: %s", sql)
	}
}

// TestRPAD_SQLNullPadstrEmitsNullGuard verifies that the SQL walker emits an IS NULL
// guard for the padstr argument, matching runtime which returns nil when padstr is nil.
func TestRPAD_SQLNullPadstrEmitsNullGuard(t *testing.T) {
	sql, _, err := ToSQL(`RPAD(Name, 10, NullablePad__c)`)
	if err != nil {
		t.Fatalf("ToSQL RPAD nullable padstr: unexpected error %v", err)
	}
	if !strings.Contains(sql, "IS NULL") {
		t.Errorf("RPAD SQL must emit IS NULL guard for nullable padstr; got: %s", sql)
	}
	if !strings.Contains(sql, "NULL") {
		t.Errorf("RPAD SQL IS NULL guard must return NULL; got: %s", sql)
	}
	// IS NULL guard for padstr must appear before = '' guard
	nullIdx := strings.Index(sql, "IS NULL")
	emptyIdx := strings.Index(sql, "= ''")
	if nullIdx == -1 || emptyIdx == -1 || nullIdx > emptyIdx {
		t.Errorf("RPAD SQL padstr IS NULL guard must precede padstr = '' guard; got: %s", sql)
	}
}