package analytics

import (
	"strings"
	"testing"
	"time"

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

// TestCompileBlankChecksStayLinear guards the two blank lowerings that used to
// substitute a compiled operand twice. Comparing emitted SQL per source byte,
// rather than only absolute lengths, makes any per-level multiplier visible:
// linear growth keeps the ratio flat while the former 2^depth emission makes it
// grow exponentially.
func TestCompileBlankChecksStayLinear(t *testing.T) {
	shapes := map[string]func(int) string{
		"empty_string_comparison": func(depth int) string {
			inner := "Str__c"
			for i := 0; i < depth; i++ {
				inner = `IF(` + inner + ` = "", "a", "b")`
			}
			return inner
		},
		"isblank": func(depth int) string {
			inner := "Str__c"
			for i := 0; i < depth; i++ {
				inner = `IF(ISBLANK(` + inner + `), "a", "b")`
			}
			return inner
		},
	}

	for name, build := range shapes {
		t.Run(name, func(t *testing.T) {
			ratio := func(depth int) float64 {
				source := build(depth)
				sql := compileFormulaForTest(t, source, map[string]string{
					"Str__c": string(constants.FieldTypeString),
				})
				return float64(len(sql)) / float64(len(source))
			}

			shallow, deep := ratio(4), ratio(14)
			if deep > 1.5*shallow {
				t.Fatalf("%s: emitted SQL per source byte grew %.2f -> %.2f over depths 4..14 "+
					"(%.2fx); a compiled blank operand is being substituted more than once",
					name, shallow, deep, deep/shallow)
			}
		})
	}
}

func TestCompileDateAddMonthsUsesTwoReferenceIdentity(t *testing.T) {
	want := "DATE(GREATEST(DATE_ADD(t.`D__c`, INTERVAL 3 MONTH), " +
		"IFNULL(DATE_SUB(DATE_ADD(IFNULL(DATE_ADD(t.`D__c`, INTERVAL 1 DAY), " +
		"DATE('9999-12-31')), INTERVAL 3 MONTH), INTERVAL 1 DAY), DATE('9999-12-31'))))"
	for _, fieldType := range []constants.SchemaFieldType{
		constants.FieldTypeDate,
		constants.FieldTypeDatetime,
	} {
		t.Run(string(fieldType), func(t *testing.T) {
			got := compileFormulaForTest(t, `__DATE_ADD_MONTHS__(D__c, 3)`, map[string]string{
				"D__c": string(fieldType),
			})
			if got != want {
				t.Fatalf("Compile() = %q, want %q", got, want)
			}
			if count := strings.Count(got, "t.`D__c`"); count != 2 {
				t.Fatalf("date operand appears %d times, want exactly 2 in %q", count, got)
			}
		})
	}
}

// TestCompileDateAddMonthsGrowthBaseStaysTwo pins the deliberate residual
// multiplier. TiDB Server has no executable scalar binding/LATERAL primitive,
// so the Salesforce month-end identity still needs two textual references to
// the date operand. The guard proves it stays at two rather than regressing to
// the former four-reference CASE (which emitted 2.9 MB at depth 8).
func TestCompileDateAddMonthsGrowthBaseStaysTwo(t *testing.T) {
	build := func(depth int) string {
		inner := "D__c"
		for i := 0; i < depth; i++ {
			inner = "__DATE_ADD_MONTHS__(" + inner + ", 1)"
		}
		return inner
	}
	ratio := func(depth int) float64 {
		source := build(depth)
		sql := compileFormulaForTest(t, source, map[string]string{
			"D__c": string(constants.FieldTypeDate),
		})
		wantOccurrences := 1 << depth
		if got := strings.Count(sql, "t.`D__c`"); got != wantOccurrences {
			t.Fatalf("depth %d date operand occurrences = %d, want %d", depth, got, wantOccurrences)
		}
		return float64(len(sql)) / float64(len(source))
	}

	shallow, deep := ratio(4), ratio(8)
	// Four additional levels at a 2x multiplier grow raw SQL about 16x and,
	// after normalizing by the roughly doubled source, the ratio about 8x. The
	// 12x bound leaves formatting headroom but rejects a 3x or 4x emitter.
	if deep > 12*shallow {
		t.Fatalf("emitted SQL per source byte grew %.2f -> %.2f over depths 4..8 "+
			"(%.2fx); DATE_ADD_MONTHS exceeds its two-reference contract",
			shallow, deep, deep/shallow)
	}
}

func TestDateAddMonthsTwoReferenceIdentityMatchesSalesforceCalendar(t *testing.T) {
	edges := []struct {
		date      string
		months    int
		want      string
		wantValid bool
	}{
		{date: "2019-02-28", months: 1, want: "2019-03-31", wantValid: true},
		{date: "2024-02-29", months: 12, want: "2025-02-28", wantValid: true},
		{date: "2024-01-30", months: 1, want: "2024-02-29", wantValid: true},
		{date: "2024-04-30", months: -1, want: "2024-03-31", wantValid: true},
		{date: "9999-12-31", months: -1, want: "9999-11-30", wantValid: true},
		{date: "9999-12-31", months: 0, want: "9999-12-31", wantValid: true},
		{date: "9999-12-31", months: 1, wantValid: false},
		{date: "9998-01-31", months: 23, want: "9999-12-31", wantValid: true},
		{date: "1000-01-01", months: -1, want: "0999-12-01", wantValid: true},
		{date: "1000-01-01", months: 0, want: "1000-01-01", wantValid: true},
		{date: "0001-01-01", months: -1, wantValid: false},
		{date: "0001-01-01", months: 0, want: "0001-01-01", wantValid: true},
	}
	for _, edge := range edges {
		t.Run(edge.date+"/months", func(t *testing.T) {
			date := mustEmissionTestDate(t, edge.date)
			got, valid := dateAddMonthsTwoReferenceModel(date, edge.months)
			if valid != edge.wantValid {
				t.Fatalf("identity validity = %v, want %v (value %v)", valid, edge.wantValid, got)
			}
			if valid && got.Format("2006-01-02") != edge.want {
				t.Fatalf("identity = %s, want %s", got.Format("2006-01-02"), edge.want)
			}
		})
	}

	for _, date := range []time.Time{
		time.Date(2019, time.February, 28, 12, 34, 56, 123456000, time.UTC),
		time.Date(2024, time.June, 15, 8, 9, 10, 654321000, time.UTC),
	} {
		got, valid := dateAddMonthsTwoReferenceModel(date, 1)
		if !valid {
			t.Fatalf("DateTime identity unexpectedly invalid for %s", date)
		}
		if got.Hour() != 0 || got.Minute() != 0 || got.Second() != 0 || got.Nanosecond() != 0 {
			t.Fatalf("DateTime identity retained a time component: input=%s result=%s", date, got)
		}
	}

	// Exercise every calendar day in representative leap/century and SQL DATE
	// boundary years across positive and negative month shifts. This is a pure
	// calendar proof; the opt-in integration test separately checks TiDB's
	// DATE_ADD/GREATEST/NULL behavior.
	for _, year := range []int{1, 1000, 1600, 1900, 1999, 2000, 2001, 2004, 2099, 2400, 9998, 9999} {
		for month := time.January; month <= time.December; month++ {
			for day := 1; day <= emissionTestLastDay(year, month); day++ {
				date := time.Date(year, month, day, 0, 0, 0, 0, time.UTC)
				for months := -36; months <= 36; months++ {
					want, wantValid := salesforceAddMonthsModel(date, months)
					got, gotValid := dateAddMonthsTwoReferenceModel(date, months)
					if gotValid != wantValid || (gotValid && !got.Equal(want)) {
						t.Fatalf("identity mismatch for %s %+d months: got=(%s,%v) want=(%s,%v)",
							date.Format("2006-01-02"), months,
							got.Format("2006-01-02"), gotValid,
							want.Format("2006-01-02"), wantValid)
					}
				}
			}
		}
	}
}

func mustEmissionTestDate(t *testing.T, value string) time.Time {
	t.Helper()
	date, err := time.Parse("2006-01-02", value)
	if err != nil {
		t.Fatalf("parse date %q: %v", value, err)
	}
	return date
}

func salesforceAddMonthsModel(date time.Time, months int) (time.Time, bool) {
	ordinary, valid := sqlDateAddMonthsClampModel(date, months)
	if !valid {
		return time.Time{}, false
	}
	if date.Day() == emissionTestLastDay(date.Year(), date.Month()) {
		return time.Date(ordinary.Year(), ordinary.Month(), emissionTestLastDay(ordinary.Year(), ordinary.Month()),
			0, 0, 0, 0, time.UTC), true
	}
	return ordinary, true
}

func dateAddMonthsTwoReferenceModel(date time.Time, months int) (time.Time, bool) {
	candidate1, valid := sqlDateAddMonthsClampModel(date, months)
	if !valid {
		// SQL GREATEST propagates candidate 1's NULL even if candidate 2 is
		// representable.
		return time.Time{}, false
	}

	shifted := date.AddDate(0, 0, 1)
	if date.Year() == 9999 && date.Month() == time.December && date.Day() == 31 {
		// Mirrors IFNULL(DATE_ADD(date, INTERVAL 1 DAY), DATE('9999-12-31')).
		shifted = date
	}
	candidate2, valid := sqlDateAddMonthsClampModel(shifted, months)
	if !valid {
		// A representable candidate 1 plus an overflowing shifted candidate can
		// only occur at the upper SQL DATE boundary, where the anchored answer is
		// the maximum date supplied by the outer IFNULL.
		candidate2 = time.Date(9999, time.December, 31, 0, 0, 0, 0, time.UTC)
	} else {
		candidate2 = candidate2.AddDate(0, 0, -1)
		if candidate2.Year() < 1 || candidate2.Year() > 9999 {
			candidate2 = time.Date(9999, time.December, 31, 0, 0, 0, 0, time.UTC)
		}
	}
	if candidate2.After(candidate1) {
		return candidate2, true
	}
	return candidate1, true
}

func sqlDateAddMonthsClampModel(date time.Time, months int) (time.Time, bool) {
	targetMonthIndex := date.Year()*12 + int(date.Month()) - 1 + months
	if targetMonthIndex < 12 || targetMonthIndex > 9999*12+11 {
		return time.Time{}, false
	}
	targetYear := targetMonthIndex / 12
	targetMonth := time.Month(targetMonthIndex%12 + 1)
	targetDay := date.Day()
	if last := emissionTestLastDay(targetYear, targetMonth); targetDay > last {
		targetDay = last
	}
	return time.Date(targetYear, targetMonth, targetDay, 0, 0, 0, 0, time.UTC), true
}

func emissionTestLastDay(year int, month time.Month) int {
	return time.Date(year, month+1, 0, 0, 0, 0, 0, time.UTC).Day()
}