package analytics

import (
	"strings"
	"testing"

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

// These tests pin the #4152 blank-as-zero lowering: every NUMERIC field read
// (bare, cross-ref, deep-ref, rollup-resolved) compiles through
// COALESCE(read, 0), while non-numeric and unknown-typed reads keep NULL
// untouched. The blank-function interactions are pinned explicitly because
// they are the deliberate semantic consequences of the vendor's BlankAsZero
// default rather than accidents of the emission.

func TestCompileBlankAsZeroWrapsNumericReads(t *testing.T) {
	cases := []struct {
		name       string
		formula    string
		fieldTypes map[string]string
		want       string
	}{
		{
			name:       "double bare read",
			formula:    `Amount__c`,
			fieldTypes: map[string]string{"Amount__c": string(constants.FieldTypeDouble)},
			want:       "COALESCE(t.`Amount__c`, 0)",
		},
		{
			name:       "currency arithmetic wraps each operand",
			formula:    `Amount__c + Fee__c`,
			fieldTypes: map[string]string{"Amount__c": string(constants.FieldTypeCurrency), "Fee__c": string(constants.FieldTypeCurrency)},
			want:       "(COALESCE(t.`Amount__c`, 0) + COALESCE(t.`Fee__c`, 0))",
		},
		{
			name:       "int comparison",
			formula:    `Seats__c > 5`,
			fieldTypes: map[string]string{"Seats__c": string(constants.FieldTypeInt)},
			want:       "(COALESCE(t.`Seats__c`, 0) > 5)",
		},
		{
			// A blank numeric never satisfies a strict > against a positive
			// threshold (0 > 5 is false, NULL > 5 was not-true) — but it DOES
			// newly satisfy <=; the wrap is the point, the caller audits usage.
			name:       "unknown type stays bare (fail open to null propagation)",
			formula:    `Mystery__c + 1`,
			fieldTypes: nil,
			want:       "(t.`Mystery__c` + 1)",
		},
		{
			name:       "string read stays bare",
			formula:    `Label__c`,
			fieldTypes: map[string]string{"Label__c": string(constants.FieldTypeString)},
			want:       "t.`Label__c`",
		},
		{
			// Booleans are excluded on purpose: active-formula booleans surface
			// through storage as tinyint 1/0, and coalescing a blank one to 0
			// would silently read "unset" as "false".
			name:       "boolean read stays bare",
			formula:    `Enabled__c`,
			fieldTypes: map[string]string{"Enabled__c": string(constants.FieldTypeBoolean)},
			want:       "t.`Enabled__c`",
		},
		{
			name:       "date read stays bare",
			formula:    `Due__c`,
			fieldTypes: map[string]string{"Due__c": string(constants.FieldTypeDate)},
			want:       "t.`Due__c`",
		},
	}

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

func TestCompileBlankAsZeroCrossRefWrapsNumericReads(t *testing.T) {
	ast, err := Parse(`Parent__r.Amount__c + 1`)
	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.Amount__c": string(constants.FieldTypeCurrency)},
	})
	if err != nil {
		t.Fatalf("Compile() error = %v", err)
	}
	want := "(COALESCE(j_parent.`Amount__c`, 0) + 1)"
	if got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

// TestCompileBlankAsZeroBlankValueNumericSkipsEmptyStringArm pins the
// BLANKVALUE consequence. For a numeric operand the empty-string NULLIF arm is
// dropped: the read is already coalesced, so BLANKVALUE returns the coerced 0
// — never the default — matching vendor BlankAsZero. Keeping NULLIF would be
// actively wrong here: TiDB coerces ” to 0 in a numeric comparison, so
// NULLIF(read, ”) would turn every legally stored 0 into the default.
// String-like operands keep the empty-string arm untouched.
func TestCompileBlankAsZeroBlankValueNumericSkipsEmptyStringArm(t *testing.T) {
	got := compileFormulaForTest(t, `BLANKVALUE(Amount__c, 5)`, map[string]string{
		"Amount__c": string(constants.FieldTypeDouble),
	})
	want := "IFNULL(COALESCE(t.`Amount__c`, 0), 5)"
	if got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}

	gotStr := compileFormulaForTest(t, `BLANKVALUE(Label__c, "x")`, map[string]string{
		"Label__c": string(constants.FieldTypeString),
	})
	wantStr := "IFNULL(NULLIF(t.`Label__c`, ''), 'x')"
	if gotStr != wantStr {
		t.Fatalf("Compile() = %q, want %q", gotStr, wantStr)
	}
}

// TestCompileBlankAsZeroGuardedDivisionShape pins the audited guard idiom
// end-to-end: A / (1 - B) with B blank must lower so the division computes
// A / 1 (the vendor value) instead of NULL-propagating.
func TestCompileBlankAsZeroGuardedDivisionShape(t *testing.T) {
	got := compileFormulaForTest(t, `Amount__c / (1 - Rate__c)`, map[string]string{
		"Amount__c": string(constants.FieldTypeDouble),
		"Rate__c":   string(constants.FieldTypePercent),
	})
	want := "(COALESCE(t.`Amount__c`, 0) / (1 - (COALESCE(t.`Rate__c`, 0) / 100.0)))"
	if got != want {
		t.Fatalf("Compile() = %q, want %q", got, want)
	}
}

// TestCompileBlankAsZeroBlankFnFamilyOverNumeric pins the remaining
// blank-function shapes over a coalesced numeric read: NULLVALUE never takes
// its default, ISNULL is vacuously false, and TEXT renders the coerced zero
// rather than an empty string.
func TestCompileBlankAsZeroBlankFnFamilyOverNumeric(t *testing.T) {
	types := map[string]string{"Amount__c": string(constants.FieldTypeDouble)}

	got := compileFormulaForTest(t, `NULLVALUE(Amount__c, 5)`, types)
	if want := "IFNULL(COALESCE(t.`Amount__c`, 0), 5)"; got != want {
		t.Fatalf("NULLVALUE Compile() = %q, want %q", got, want)
	}

	got = compileFormulaForTest(t, `ISNULL(Amount__c)`, types)
	if want := "(COALESCE(t.`Amount__c`, 0) IS NULL)"; got != want {
		t.Fatalf("ISNULL Compile() = %q, want %q", got, want)
	}

	got = compileFormulaForTest(t, `TEXT(Amount__c)`, types)
	if want := "COALESCE(CAST(COALESCE(t.`Amount__c`, 0) AS CHAR), '')"; got != want {
		t.Fatalf("TEXT Compile() = %q, want %q", got, want)
	}
}

// TestResolveAggregateMarkersMinMaxStayBare pins the MIN/MAX exclusion from
// the aggregate coalesce: the child field's type is unknown at
// marker-resolution time and a date MIN/MAX must not be coerced to 0.
func TestResolveAggregateMarkersMinMaxStayBare(t *testing.T) {
	marker, err := compileAggregate(&AggregateExpr{
		Func:        "MAX",
		ChildObject: "WorkLog__c",
		ChildField:  "Logged_On__c",
	}, nil)
	if err != nil {
		t.Fatalf("compileAggregate(max) error = %v", err)
	}
	sql, err := NewDSLViewBuilder().resolveAggregateMarkers(
		marker+" AS `LatestLog__c`",
		ViewBuildConfig{AggJoins: []AggregateJoinSpec{{
			Alias:       "rsq0",
			ChildObject: "WorkLog__c",
			ChildTable:  "worklog__c",
			AggFunc:     "MAX",
			AggField:    "Logged_On__c",
			ForeignKey:  "ParentIssue__c",
			ParentKey:   "Id",
			OutputAlias: "agg_0",
		}}},
		aggregateJoinReference,
	)
	if err != nil {
		t.Fatalf("resolveAggregateMarkers() error = %v", err)
	}
	if !strings.Contains(sql, "rsq0.`agg_0` AS `LatestLog__c`") || strings.Contains(sql, "COALESCE(rsq0") {
		t.Fatalf("MAX aggregate must stay bare:\n%s", sql)
	}
}

// TestCompileBlankAsZeroBlankValueOverAggregate pins the aggregate operand
// classification: BLANKVALUE over a coalesced SUM must not keep the
// empty-string NULLIF arm (which would coerce the coalesced 0 back to NULL on
// the storage engine and take the default). MIN keeps the blank-aware shape —
// its child type is unknown and may be a date.
func TestCompileBlankAsZeroBlankValueOverAggregate(t *testing.T) {
	ast, err := Parse(`BLANKVALUE(SUM(Line__c.Amount__c), 5)`)
	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.Contains(sql, "NULLIF") {
		t.Fatalf("BLANKVALUE over SUM must not keep the empty-string arm:\n%s", sql)
	}
	if !strings.HasPrefix(sql, "IFNULL(") {
		t.Fatalf("BLANKVALUE over SUM should lower to IFNULL over the aggregate marker:\n%s", sql)
	}

	astMin, err := Parse(`BLANKVALUE(MIN(Line__c.When__c), "n/a")`)
	if err != nil {
		t.Fatalf("Parse(min) error = %v", err)
	}
	sqlMin, err := Compile(astMin, &CompileContext{TableAlias: "t", QualifyFields: true})
	if err != nil {
		t.Fatalf("Compile(min) error = %v", err)
	}
	if !strings.Contains(sqlMin, "NULLIF") {
		t.Fatalf("BLANKVALUE over MIN keeps the blank-aware shape (unknown child type):\n%s", sqlMin)
	}
}