//go:build integration

package analytics

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

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

func TestBlankCheckSingleReferenceRuntimeParity(t *testing.T) {
	db, cleanup := openFormulaViewAggregateParityDB(t)
	defer cleanup()

	mustExec(t, db, "CREATE TABLE `BlankEmission` ("+
		"`n` INT NOT NULL PRIMARY KEY, "+
		"`TextValue` VARCHAR(64) NULL, "+
		"`UnknownValue` VARCHAR(64) NULL)")
	for _, row := range []struct {
		n     int
		value any
	}{
		{n: 1, value: nil},
		{n: 2, value: ""},
		{n: 3, value: "present"},
	} {
		mustExec(t, db,
			"INSERT INTO `BlankEmission` (`n`,`TextValue`,`UnknownValue`) VALUES (?,?,?)",
			row.n, row.value, row.value)
	}

	compileSQL := func(source string, fieldTypes map[string]string) string {
		t.Helper()
		ast, err := Parse(source)
		if err != nil {
			t.Fatalf("Parse(%q): %v", source, err)
		}
		sqlText, err := Compile(ast, &CompileContext{
			TableAlias:    "t",
			QualifyFields: true,
			FieldTypes:    fieldTypes,
		})
		if err != nil {
			t.Fatalf("Compile(%q): %v", source, err)
		}
		return sqlText
	}
	isBlank := compileSQL(`ISBLANK(TextValue)`, map[string]string{
		"TextValue": string(constants.FieldTypeString),
	})
	emptyEquality := compileSQL(`TextValue = ""`, map[string]string{
		"TextValue": string(constants.FieldTypeString),
	})
	unknown := compileSQL(`ISBLANK(UnknownValue)`, nil)

	rows, err := db.Query(fmt.Sprintf(
		"SELECT `n`, %s, %s, %s FROM `BlankEmission` t ORDER BY `n`",
		isBlank, emptyEquality, unknown))
	if err != nil {
		t.Fatalf("run blank-check parity query: %v", err)
	}
	defer rows.Close()

	want := map[int]bool{1: true, 2: true, 3: false}
	for rows.Next() {
		var n int
		var gotISBLANK, gotEquality, gotUnknown bool
		if err := rows.Scan(&n, &gotISBLANK, &gotEquality, &gotUnknown); err != nil {
			t.Fatalf("scan blank-check row: %v", err)
		}
		for name, got := range map[string]bool{
			"ISBLANK string": gotISBLANK,
			"empty equality": gotEquality,
			"ISBLANK cast":   gotUnknown,
		} {
			if got != want[n] {
				t.Fatalf("row %d %s = %v, want %v", n, name, got, want[n])
			}
		}
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("iterate blank-check rows: %v", err)
	}
}

// TestDateAddMonthsTwoReferenceRuntimeParity checks the engine facts the pure
// Go calendar proof cannot: TiDB's DATE_ADD clamp, GREATEST NULL propagation,
// and the 9999-12-31 overflow fallback.
//
// Run with:
//
//	go test -tags integration ./nexuscrm-analytics/ -run TestDateAddMonthsTwoReferenceRuntimeParity
//
// against a local TiDB (TIDB_HOST/TIDB_PORT, default 127.0.0.1:4001). The
// shared integration harness skips when the server is unreachable.
func TestDateAddMonthsTwoReferenceRuntimeParity(t *testing.T) {
	db, cleanup := openFormulaViewAggregateParityDB(t)
	defer cleanup()

	mustExec(t, db, "CREATE TABLE `DateAddEmission` ("+
		"`n` INT NOT NULL PRIMARY KEY, "+
		"`D__c` DATETIME(6) NULL, "+
		"`Months__c` INT NULL)")

	type testRow struct {
		n         int
		date      any
		months    any
		want      string
		wantValid bool
	}
	rows := []testRow{
		{n: 1, date: "2019-02-28 12:34:56.123456", months: 1, want: "2019-03-31", wantValid: true},
		{n: 2, date: "2024-02-29", months: 12, want: "2025-02-28", wantValid: true},
		{n: 3, date: "2024-01-30", months: 1, want: "2024-02-29", wantValid: true},
		{n: 4, date: "2024-04-30", months: -1, want: "2024-03-31", wantValid: true},
		{n: 5, date: "9999-12-31", months: -1, want: "9999-11-30", wantValid: true},
		{n: 6, date: "9999-12-31", months: 0, want: "9999-12-31", wantValid: true},
		{n: 7, date: "9999-12-31", months: 1},
		{n: 8, date: nil, months: -1},
		// Blank numeric operands read as zero in active formulas (#4152).
		{n: 9, date: "2024-06-15", months: nil, want: "2024-06-15", wantValid: true},
		// Candidate 1 is valid while the shifted candidate overflows its month add.
		{n: 10, date: "9998-01-31", months: 23, want: "9999-12-31", wantValid: true},
		// A non-month-end DateTime also returns a Date rather than preserving time.
		{n: 11, date: "2024-06-15 08:09:10.654321", months: 1, want: "2024-07-15", wantValid: true},
	}
	for _, row := range rows {
		mustExec(t, db,
			"INSERT INTO `DateAddEmission` (`n`,`D__c`,`Months__c`) VALUES (?,?,?)",
			row.n, row.date, row.months)
	}

	ast, err := Parse(`__DATE_ADD_MONTHS__(D__c, Months__c)`)
	if err != nil {
		t.Fatalf("Parse(): %v", err)
	}
	compiled, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		FieldTypes: map[string]string{
			"D__c":      string(constants.FieldTypeDatetime),
			"Months__c": string(constants.FieldTypeInt),
		},
	})
	if err != nil {
		t.Fatalf("Compile(): %v", err)
	}

	mustExec(t, db, fmt.Sprintf(
		"CREATE VIEW `v_date_add_emission` AS SELECT t.`n`, %s AS `Value` FROM `DateAddEmission` t",
		compiled))
	var dataType string
	if err := db.QueryRow(
		"SELECT `DATA_TYPE` FROM `INFORMATION_SCHEMA`.`COLUMNS` " +
			"WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'v_date_add_emission' AND `COLUMN_NAME` = 'Value'",
	).Scan(&dataType); err != nil {
		t.Fatalf("read inferred DATE_ADD_MONTHS view type: %v", err)
	}
	if dataType != "date" {
		t.Fatalf("DATE_ADD_MONTHS inferred view type = %q, want date", dataType)
	}

	query := "SELECT `n`, DATE_FORMAT(`Value`, '%Y-%m-%d'), " +
		"DATE_FORMAT(`Value`, '%H:%i:%s.%f') FROM `v_date_add_emission` ORDER BY `n`"
	resultRows, err := db.Query(query)
	if err != nil {
		t.Fatalf("run DATE_ADD_MONTHS parity query: %v\nquery: %s", err, query)
	}
	defer resultRows.Close()

	type resultValue struct {
		date sql.NullString
		time sql.NullString
	}
	got := map[int]resultValue{}
	for resultRows.Next() {
		var n int
		var dateValue, timeValue sql.NullString
		if err := resultRows.Scan(&n, &dateValue, &timeValue); err != nil {
			t.Fatalf("scan: %v", err)
		}
		got[n] = resultValue{date: dateValue, time: timeValue}
	}
	if err := resultRows.Err(); err != nil {
		t.Fatalf("iterate: %v", err)
	}

	for _, row := range rows {
		value, ok := got[row.n]
		if !ok {
			t.Fatalf("row %d missing", row.n)
		}
		if value.date.Valid != row.wantValid {
			t.Fatalf("row %d validity = %v, want %v (value %q)",
				row.n, value.date.Valid, row.wantValid, value.date.String)
		}
		if value.date.Valid && value.date.String != row.want {
			t.Fatalf("row %d value = %q, want %q", row.n, value.date.String, row.want)
		}
		if value.time.Valid && value.time.String != "00:00:00.000000" {
			t.Fatalf("row %d retained time %q, want midnight Date result", row.n, value.time.String)
		}
	}
}

func TestCalendarPrimitiveActiveFrozenRangeParity(t *testing.T) {
	db, cleanup := openFormulaViewAggregateParityDB(t)
	defer cleanup()

	mustExec(t, db, "CREATE TABLE `DateTargetClampParity` ("+
		"`n` INT NOT NULL PRIMARY KEY, `D__c` DATE NOT NULL, `Months__c` INT NOT NULL)")
	rows := []struct {
		n      int
		date   string
		months int
	}{
		{n: 1, date: "2019-02-28", months: 1},
		{n: 2, date: "9999-12-31", months: 0},
		{n: 3, date: "9999-12-31", months: 1},
		{n: 4, date: "1000-01-01", months: -1},
		{n: 5, date: "0001-01-01", months: -1},
		{n: 6, date: "0000-01-01", months: -1},
	}
	for _, row := range rows {
		mustExec(t, db,
			"INSERT INTO `DateTargetClampParity` (`n`,`D__c`,`Months__c`) VALUES (?,?,?)",
			row.n, row.date, row.months)
	}

	ast, err := Parse(`__DATE_ADD_MONTHS_TARGET_CLAMP__(D__c, Months__c)`)
	if err != nil {
		t.Fatalf("Parse(): %v", err)
	}
	compiled, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		FieldTypes: map[string]string{
			"D__c":      string(constants.FieldTypeDate),
			"Months__c": string(constants.FieldTypeInt),
		},
	})
	if err != nil {
		t.Fatalf("Compile(): %v", err)
	}

	resultRows, err := db.Query(fmt.Sprintf(
		"SELECT `n`, DATE_FORMAT(%s, '%%Y-%%m-%%d') FROM `DateTargetClampParity` t ORDER BY `n`",
		compiled))
	if err != nil {
		t.Fatalf("run target-clamp parity query: %v", err)
	}
	defer resultRows.Close()

	active := expression.NewEngine()
	for _, row := range rows {
		if !resultRows.Next() {
			t.Fatalf("SQL result missing row %d", row.n)
		}
		var n int
		var frozen sql.NullString
		if err := resultRows.Scan(&n, &frozen); err != nil {
			t.Fatalf("scan row %d: %v", row.n, err)
		}
		formula := fmt.Sprintf(`__DATE_ADD_MONTHS_TARGET_CLAMP__("%s", %d)`, row.date, row.months)
		activeValue, err := active.Evaluate(formula, nil)
		if err != nil {
			t.Fatalf("active Evaluate(%q): %v", formula, err)
		}
		if frozen.Valid != (activeValue != nil) {
			t.Fatalf("row %d validity differs: active=%#v frozen=%#v", n, activeValue, frozen)
		}
		activeString, ok := activeValue.(string)
		if activeValue != nil && !ok {
			t.Fatalf("row %d active value has type %T, want string or nil", n, activeValue)
		}
		if frozen.Valid && frozen.String != activeString {
			t.Fatalf("row %d differs: active=%#v frozen=%#v", n, activeValue, frozen.String)
		}
	}
	if err := resultRows.Err(); err != nil {
		t.Fatalf("iterate: %v", err)
	}
	if err := resultRows.Close(); err != nil {
		t.Fatalf("close target-clamp rows: %v", err)
	}

	diffAST, err := Parse(`__DATE_DIFF_DAYS__(D__c, D__c)`)
	if err != nil {
		t.Fatalf("Parse day difference: %v", err)
	}
	diffSQL, err := Compile(diffAST, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		FieldTypes: map[string]string{
			"D__c": string(constants.FieldTypeDate),
		},
	})
	if err != nil {
		t.Fatalf("Compile day difference: %v", err)
	}
	var frozenDiff sql.NullInt64
	if err := db.QueryRow(fmt.Sprintf(
		"SELECT %s FROM `DateTargetClampParity` t WHERE `n` = 6", diffSQL,
	)).Scan(&frozenDiff); err != nil {
		t.Fatalf("run year-0000 day difference: %v", err)
	}
	activeDiff, err := active.Evaluate(
		`__DATE_DIFF_DAYS__("0000-01-01", "0000-01-01")`, nil,
	)
	if err != nil {
		t.Fatalf("active year-0000 day difference: %v", err)
	}
	if !frozenDiff.Valid || activeDiff != frozenDiff.Int64 {
		t.Fatalf("year-0000 day difference differs: active=%#v frozen=%#v", activeDiff, frozenDiff)
	}
}