package analytics

import (
	"encoding/json"
	"strings"
	"testing"

	"github.com/nexuscrm/kernel/pkg/formula/spec"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
)

func TestIncludesSQLStorageProof_ProductionCompilerUsesCompatiblePredicate(t *testing.T) {
	sql := productionIncludesSQL(t, "Hot")
	wantSQL := utils.MultiPicklistJSONContainsSQL("t.`Tags__c`", "'Hot'", true)
	if sql != wantSQL {
		t.Fatalf("production INCLUDES SQL = %q, want %q", sql, wantSQL)
	}

	for _, tc := range []struct {
		name           string
		raw            *string
		wantProduction bool
		wantWholeToken bool
		expectMismatch bool
	}{
		{name: "canonical exact token", raw: proofStringPtr(proofCanonicalJSONArray(t, []string{"Hot"})), wantProduction: true, wantWholeToken: true, expectMismatch: false},
		{name: "canonical substring rejected", raw: proofStringPtr(proofCanonicalJSONArray(t, []string{"Hotter"})), wantProduction: false, wantWholeToken: false, expectMismatch: false},
		{name: "legacy exact token", raw: proofStringPtr(proofLegacyJSONString(t, "Hot;Warm")), wantProduction: true, wantWholeToken: true, expectMismatch: false},
		{name: "legacy substring rejected", raw: proofStringPtr(proofLegacyJSONString(t, "Hotter")), wantProduction: false, wantWholeToken: false, expectMismatch: false},
		{name: "null field", raw: nil, wantProduction: false, wantWholeToken: false, expectMismatch: false},
	} {
		t.Run(tc.name, func(t *testing.T) {
			got := productionCompatiblePredicateTruth(tc.raw, "Hot")
			if got != tc.wantProduction {
				t.Fatalf("production SQL %q on raw=%v returned %v, want production result %v", sql, rawString(tc.raw), got, tc.wantProduction)
			}
			if mismatch := got != tc.wantWholeToken; mismatch != tc.expectMismatch {
				t.Fatalf("production SQL %q on raw=%v differs from whole-token result outside an expected false-positive case", sql, rawString(tc.raw))
			}
		})
	}
}

func TestIncludesSQLStorageProof_ProductionPredicateDistinguishesJSONEnvelope(t *testing.T) {
	sql := productionIncludesSQL(t, "Others")
	wantSQL := utils.MultiPicklistJSONContainsSQL("t.`Tags__c`", "'Others'", true)
	if sql != wantSQL {
		t.Fatalf("production INCLUDES SQL = %q, want %q", sql, wantSQL)
	}

	canonicalRaw := proofCanonicalJSONArray(t, []string{"Others"})
	legacyRaw := proofLegacyJSONString(t, `["Others"]`)
	if canonicalRaw == legacyRaw {
		t.Fatalf("canonical array raw = %q, want different bytes from JSON string envelope %q", canonicalRaw, legacyRaw)
	}

	if got := productionCompatiblePredicateTruth(&canonicalRaw, "Others"); got != true {
		t.Fatalf("production SQL %q on canonical raw=%q returned %v, want match", sql, canonicalRaw, got)
	}
	if got := productionCompatiblePredicateTruth(&legacyRaw, "Others"); got != false {
		t.Fatalf("production SQL %q on legacy raw=%q returned %v, want no match for JSON-looking scalar payload", sql, legacyRaw, got)
	}
	if got := productionCompatiblePredicateTruth(&legacyRaw, `["Others"]`); got != true {
		t.Fatalf("production SQL %q on legacy raw=%q returned %v, want match for the literal scalar payload", sql, legacyRaw, got)
	}
}

func TestIncludesSQLStorageProof_ProductionSQLLiteralEscapesRHS(t *testing.T) {
	sql := productionIncludesSQL(t, `O'Reilly \ North .*`)
	if !strings.Contains(sql, "JSON_CONTAINS(") || !strings.Contains(sql, "JSON_QUOTE(") || !strings.Contains(sql, "t.`Tags__c`") {
		t.Fatalf("production INCLUDES SQL = %q, want JSON_CONTAINS predicate over Tags__c", sql)
	}
	if !strings.Contains(sql, "O''Reilly") {
		t.Fatalf("production INCLUDES SQL = %q, want SQL-escaped quote", sql)
	}
	if !strings.Contains(sql, `\\`) {
		t.Fatalf("production INCLUDES SQL = %q, want SQL-escaped backslash", sql)
	}
	if !strings.Contains(sql, `.*`) {
		t.Fatalf("production INCLUDES SQL = %q, want RHS metacharacters preserved as string literal bytes", sql)
	}
}

func productionIncludesSQL(t *testing.T, rhs string) string {
	t.Helper()
	ast, err := Parse("INCLUDES(Tags__c, " + formulaStringLiteral(t, rhs) + ")")
	if err != nil {
		t.Fatalf("Parse INCLUDES: %v", err)
	}
	sql, err := Compile(ast, &CompileContext{
		TableAlias:    "t",
		QualifyFields: true,
		FieldTypes: map[string]string{
			"Tags__c": string(constants.FieldTypeMultiPicklist),
		},
		FunctionRegistry: includesFunctionRegistry(t),
	})
	if err != nil {
		t.Fatalf("Compile INCLUDES: %v", err)
	}
	return sql
}

func includesFunctionRegistry(t *testing.T) *spec.Registry {
	t.Helper()
	registry := spec.DefaultRegistry().Clone()
	if _, ok := registry.Lookup("INCLUDES"); ok {
		return registry
	}
	err := registry.Register(spec.FunctionSpec{
		Name:    "INCLUDES",
		MinArgs: 2,
		MaxArgs: 2,
		Return:  spec.ReturnSpec{Kind: spec.ReturnFixed, Fixed: spec.TypeBoolean},
	})
	if err != nil {
		t.Fatalf("register INCLUDES spec: %v", err)
	}
	return registry
}

func proofCanonicalJSONArray(t *testing.T, values []string) string {
	t.Helper()
	raw, err := json.Marshal(values)
	if err != nil {
		t.Fatalf("json.Marshal(%#v): %v", values, err)
	}
	return string(raw)
}

func formulaStringLiteral(t *testing.T, value string) string {
	t.Helper()
	raw, err := json.Marshal(value)
	if err != nil {
		t.Fatalf("json.Marshal(%q): %v", value, err)
	}
	return string(raw)
}

func proofLegacyJSONString(t *testing.T, value string) string {
	t.Helper()
	raw, err := json.Marshal(value)
	if err != nil {
		t.Fatalf("json.Marshal(%q): %v", value, err)
	}
	return string(raw)
}

func productionCompatiblePredicateTruth(raw *string, rhs string) bool {
	if raw == nil {
		return false
	}
	var items []string
	if err := json.Unmarshal([]byte(*raw), &items); err == nil {
		for _, item := range items {
			if item == rhs {
				return true
			}
		}
		return false
	}

	var scalar string
	if err := json.Unmarshal([]byte(*raw), &scalar); err != nil {
		return false
	}
	if strings.TrimSpace(scalar) == "" {
		return false
	}
	for _, item := range strings.Split(scalar, ";") {
		if item == rhs {
			return true
		}
	}
	return false
}

func proofStringPtr(value string) *string {
	return &value
}

func rawString(raw *string) string {
	if raw == nil {
		return "<nil>"
	}
	return *raw
}