//go:build integration

package analytics

import (
	"database/sql"
	"fmt"
	"testing"

	_ "github.com/go-sql-driver/mysql"
)

// TestNullStrictRuntimeParity is the RUNTIME oracle for null provenance.
//
// Compile-level assertions prove the emitted SQL changed shape. Only this test
// proves the change produces the right VALUES on a real engine, which is the
// claim that actually matters: the whole defect exists because TiDB renders
// 0/0 as NULL and a plain branch-selecting CASE then reads that NULL as "false".
//
// Run with:
//
//	go test -tags integration ./nexuscrm-analytics/ -run TestNullStrictRuntimeParity
//
// against a local TiDB (TIDB_HOST/TIDB_PORT, default 127.0.0.1:4001). Skips
// when unreachable. Do NOT point this at staging or production.
func TestNullStrictRuntimeParity(t *testing.T) {
	db, cleanup := openFormulaViewAggregateParityDB(t)
	defer cleanup()

	// The engine premise the whole fix rests on. If this ever stops holding,
	// every value assertion below is meaningless, so assert it first.
	var zeroDivIsNull, nullDivIsNull bool
	if err := db.QueryRow("SELECT (0/0) IS NULL, (NULL/NULL) IS NULL").Scan(&zeroDivIsNull, &nullDivIsNull); err != nil {
		t.Fatalf("probe undefined-division semantics: %v", err)
	}
	if !zeroDivIsNull || !nullDivIsNull {
		t.Fatalf("engine premise broken: 0/0 IS NULL = %v, NULL/NULL IS NULL = %v (both must be true)",
			zeroDivIsNull, nullDivIsNull)
	}

	mustExec(t, db, "CREATE TABLE `Quote` ("+
		"`n` INT NOT NULL PRIMARY KEY, "+
		"`Product_Family__c` VARCHAR(64) NULL, "+
		"`Sales_Channel_Type__c` VARCHAR(64) NULL, "+
		"`Years__c` DECIMAL(18,6) NULL, "+
		"`Prepaid__c` DECIMAL(18,6) NULL, "+
		"`MinCommit__c` DECIMAL(18,6) NULL)")

	type quoteRow struct {
		n           int
		family      any
		channel     any
		year        any
		prepay      any
		mincom      any
		wantOldNull bool
		wantOld     float64
		wantNewNull bool
		wantNew     float64
		meaning     string
	}
	rows := []quoteRow{
		{n: 1, family: "Tier B", channel: "Direct", year: nil, prepay: nil, mincom: nil,
			wantOldNull: true, wantNewNull: true,
			meaning: "non-Cloud takes the literal-NULL outer ELSE; unchanged by this PR"},
		{n: 2, family: "TiDB Cloud", channel: "Direct", year: nil, prepay: nil, mincom: nil,
			wantOld: 3.000, wantNewNull: true,
			meaning: "THE FIX: the 11 production quotes. NULL/NULL ratio no longer buys a 3% tier"},
		{n: 3, family: "TiDB Cloud", channel: "Direct", year: nil, prepay: 50000.0, mincom: 50000.0,
			wantOld: 5.000, wantNew: 5.000,
			meaning: "ratio exactly 1 -> the earned 5% tier; unchanged"},
		{n: 4, family: "TiDB Cloud", channel: "Indirect", year: nil, prepay: nil, mincom: 40000.0,
			wantOld: 0.000, wantNew: 0.000,
			meaning: "blank prepay reads as 0 (#4152), ratio 0 < 0.5 -> 0; unchanged"},
		{n: 5, family: "TiDB Cloud", channel: nil, year: nil, prepay: nil, mincom: 40000.0,
			wantOld: 0.000, wantNew: 0.000,
			meaning: "REGRESSION GUARD: a blank channel must NOT blank the formula. " +
				"Salesforce reads blank text as a FALSE comparison and takes the Indirect arm. " +
				"This is what the dropped leaf-normalization prerequisite was meant to protect, " +
				"and it holds without it because the channel condition contains no arithmetic"},
		{n: 6, family: "TiDB Cloud", channel: "Direct", year: nil, prepay: 0.0, mincom: 0.0,
			wantOld: 3.000, wantNewNull: true,
			meaning: "explicit 0/0, the same defect reached through stored zeros rather than blanks"},
		{n: 7, family: "TiDB Cloud", channel: "Direct", year: 2.0, prepay: 50000.0, mincom: 100000.0,
			wantOld: 6.000, wantNew: 6.000,
			meaning: "REGRESSION GUARD: 2-year term (3%) + ratio 0.5 arm (3%) = 6%; unchanged"},
	}
	for _, r := range rows {
		mustExec(t, db,
			"INSERT INTO `Quote` (`n`,`Product_Family__c`,`Sales_Channel_Type__c`,"+
				"`Years__c`,`Prepaid__c`,`MinCommit__c`) "+
				"VALUES (?,?,?,?,?,?)",
			r.n, r.family, r.channel, r.year, r.prepay, r.mincom)
	}

	// NEW: compiled live by the compiler under test, so this test cannot pass
	// against a stale hand-copied string.
	ast, err := Parse(tieredBonusFormula)
	if err != nil {
		t.Fatalf("Parse(tieredBonusFormula): %v", err)
	}
	compiled, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		JoinAliases:   map[string]string{},
		FieldTypes:    nullProvenanceFieldTypes(),
	})
	if err != nil {
		t.Fatalf("Compile(tieredBonusFormula): %v", err)
	}
	newExpr := fromFormulaDomainSQL(compiled, "percent")

	query := fmt.Sprintf(
		"SELECT `n`, ROUND(%s, 3), ROUND(%s, 3) FROM `Quote` t ORDER BY `n`",
		tieredBonusHeadEmittedSQL, newExpr)
	resultRows, err := db.Query(query)
	if err != nil {
		t.Fatalf("run null provenance parity query: %v\nquery: %s", err, query)
	}
	defer resultRows.Close()

	got := map[int][2]sql.NullFloat64{}
	for resultRows.Next() {
		var n int
		var oldVal, newVal sql.NullFloat64
		if err := resultRows.Scan(&n, &oldVal, &newVal); err != nil {
			t.Fatalf("scan: %v", err)
		}
		got[n] = [2]sql.NullFloat64{oldVal, newVal}
	}
	if err := resultRows.Err(); err != nil {
		t.Fatalf("iterate: %v", err)
	}

	for _, r := range rows {
		pair, ok := got[r.n]
		if !ok {
			t.Fatalf("row %d missing from result set", r.n)
		}
		checkValue(t, r.n, "old", pair[0], r.wantOldNull, r.wantOld, r.meaning)
		checkValue(t, r.n, "new", pair[1], r.wantNewNull, r.wantNew, r.meaning)
	}
}

func checkValue(t *testing.T, n int, which string, got sql.NullFloat64, wantNull bool, want float64, meaning string) {
	t.Helper()
	if wantNull {
		if got.Valid {
			t.Fatalf("row %d %s_pct = %v, want NULL (%s)", n, which, got.Float64, meaning)
		}
		return
	}
	if !got.Valid {
		t.Fatalf("row %d %s_pct = NULL, want %.3f (%s)", n, which, want, meaning)
	}
	if got.Float64 != want {
		t.Fatalf("row %d %s_pct = %.3f, want %.3f (%s)", n, which, got.Float64, want, meaning)
	}
}

// tieredBonusHeadEmittedSQL is the emission this compiler produced for
// tieredBonusFormula BEFORE the null provenance fix, captured verbatim from a pristine copy
// of the module at the branch base. It is frozen on purpose: it is the "before"
// half of the runtime comparison, and re-deriving it from the patched compiler
// would make the comparison vacuous.
const tieredBonusHeadEmittedSQL = "(CASE WHEN ((COALESCE(t.`Product_Family__c`, '') = 'TiDB Cloud') OR " +
	"(COALESCE(t.`Product_Family__c`, '') = 'Tier_A')) THEN CASE WHEN " +
	"(t.`Sales_Channel_Type__c` = 'Direct') THEN (CASE WHEN " +
	"(COALESCE(t.`Years__c`, 0) = 2) THEN 0.03 ELSE CASE WHEN " +
	"(COALESCE(t.`Years__c`, 0) >= 3) THEN 0.05 ELSE 0 END END + CASE WHEN " +
	"((COALESCE(t.`Prepaid__c`, 0) / COALESCE(t.`MinCommit__c`, 0)) = 1) " +
	"THEN 0.05 ELSE CASE WHEN " +
	"((COALESCE(t.`Prepaid__c`, 0) / COALESCE(t.`MinCommit__c`, 0)) < 0.5) " +
	"THEN 0 ELSE 0.03 END END) ELSE (CASE WHEN " +
	"(COALESCE(t.`Years__c`, 0) = 2) THEN 0.015 ELSE CASE WHEN " +
	"(COALESCE(t.`Years__c`, 0) >= 3) THEN 0.025 ELSE 0 END END + CASE WHEN " +
	"((COALESCE(t.`Prepaid__c`, 0) / COALESCE(t.`MinCommit__c`, 0)) = 1) " +
	"THEN 0.025 ELSE CASE WHEN " +
	"((COALESCE(t.`Prepaid__c`, 0) / COALESCE(t.`MinCommit__c`, 0)) < 0.5) " +
	"THEN 0 ELSE 0.015 END END) END ELSE NULL END) * 100"