package analytics

import (
	"context"
	"errors"
	"strings"
	"testing"

	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestValidationActiveFormulaResolverUsesRequestBoundFunctionAuthority(t *testing.T) {
	mode := "active"
	dateReturnType := validationFormulaFieldTypePtr(constants.FieldTypeDate)
	schema := &models.ObjectMetadata{
		APIName: "Contract",
		Fields: []models.FieldMetadata{
			{APIName: "StartDate", Type: constants.FieldTypeDate},
			{APIName: "ContractTerm", Type: constants.FieldTypeInt},
			{
				APIName: "EndDate", Type: constants.FieldTypeFormula,
				Formula:     validationFormulaStringPtr("ADDMONTHS(StartDate, ContractTerm)"),
				FormulaMode: &mode, ReturnType: dateReturnType,
			},
		},
	}
	bundle, err := formula.ParseFunctionBundlePayload([]byte(`{
		"functions": [{
			"name": "ADDMONTHS", "minArgs": 2, "maxArgs": 2,
			"return": {"kind": "fixed", "type": "date"},
			"args": [{"dateLikeLiteral": true}, {"type": "number"}],
			"evaluator": {
				"kind": "formula_expr_v1", "params": ["arg0", "arg1"],
				"body": "__DATE_ADD_MONTHS__(arg0, arg1)"
			}
		}]
	}`))
	if err != nil {
		t.Fatal(err)
	}
	registry, err := formula.ExecutableFunctionRegistryWithBundles([]formula.FunctionBundle{bundle})
	if err != nil {
		t.Fatal(err)
	}

	viewSvc := newValidationFormulaViewService(t, schema)
	viewSvc.SetFunctionExecutableResolver(func(context.Context) (*formula.ExecutableFunctionRegistry, error) {
		return registry, nil
	})
	queryExec := &validationFormulaQueryExecStub{rows: []models.SObject{{"Value": "2027-01-31"}}}
	resolver := NewValidationActiveFormulaResolver(viewSvc, queryExec)
	value, err := resolver(
		context.Background(), schema,
		models.SObject{"StartDate": "2026-01-31", "ContractTerm": 12},
		&schema.Fields[2],
	)
	if err != nil {
		t.Fatalf("resolver(ADDMONTHS) error = %v", err)
	}
	if value != "2027-01-31" {
		t.Fatalf("resolver(ADDMONTHS) value = %#v", value)
	}
	if strings.Contains(queryExec.lastSQL, "ADDMONTHS") || !strings.Contains(queryExec.lastSQL, "DATE_ADD") {
		t.Fatalf("resolver(ADDMONTHS) SQL = %q, want bundle-lowered date arithmetic", queryExec.lastSQL)
	}

	viewSvc.SetFunctionExecutableResolver(func(context.Context) (*formula.ExecutableFunctionRegistry, error) {
		return nil, errors.New("revision authority unavailable")
	})
	_, err = resolver(context.Background(), schema, models.SObject{}, &schema.Fields[2])
	if err == nil || !strings.Contains(err.Error(), "revision authority unavailable") {
		t.Fatalf("resolver missing authority error = %v, want fail-closed request error", err)
	}
}

func TestValidationActiveFormulaResolverCompilesCompilerOnlyFunctions(t *testing.T) {
	mode := "active"
	stringReturnType := validationFormulaFieldTypePtr(constants.FieldTypeString)
	boolReturnType := validationFormulaFieldTypePtr(constants.FieldTypeBoolean)

	schema := &models.ObjectMetadata{
		APIName: "Probe__c",
		Fields: []models.FieldMetadata{
			{APIName: "Input__c", Type: constants.FieldTypeString},
			{
				APIName:     "LineBreak__c",
				Type:        constants.FieldTypeFormula,
				Formula:     validationFormulaStringPtr("BR()"),
				FormulaMode: &mode,
				ReturnType:  stringReturnType,
			},
			{
				APIName:     "IsNumeric__c",
				Type:        constants.FieldTypeFormula,
				Formula:     validationFormulaStringPtr("ISNUMBER(Input__c)"),
				FormulaMode: &mode,
				ReturnType:  boolReturnType,
			},
		},
	}

	viewSvc := newValidationFormulaViewService(t, schema)
	queryExec := &validationFormulaQueryExecStub{rows: []models.SObject{{"Value": "\n"}}}
	resolver := NewValidationActiveFormulaResolver(viewSvc, queryExec)
	if resolver == nil {
		t.Fatal("NewValidationActiveFormulaResolver() = nil")
	}

	value, err := resolver(context.Background(), schema, models.SObject{"Input__c": "42"}, &schema.Fields[1])
	if err != nil {
		t.Fatalf("resolver(BR) error = %v", err)
	}
	if value != "\n" {
		t.Fatalf("resolver(BR) value = %#v, want newline", value)
	}
	if !strings.Contains(queryExec.lastSQL, "SELECT '\\n' AS `Value`") {
		t.Fatalf("resolver(BR) SQL = %q, want compiled BR newline", queryExec.lastSQL)
	}

	queryExec.rows = []models.SObject{{"Value": true}}
	value, err = resolver(context.Background(), schema, models.SObject{"Input__c": "42"}, &schema.Fields[2])
	if err != nil {
		t.Fatalf("resolver(ISNUMBER) error = %v", err)
	}
	if value != true {
		t.Fatalf("resolver(ISNUMBER) value = %#v, want true", value)
	}
	if !strings.Contains(queryExec.lastSQL, "REGEXP '^-?[0-9]+(\\\\.[0-9]+)?$'") {
		t.Fatalf("resolver(ISNUMBER) SQL = %q, want numeric REGEXP predicate", queryExec.lastSQL)
	}
}

func TestValidationActiveFormulaResolverRewritesBareNameToActiveDisplayField(t *testing.T) {
	mode := "active"
	stringReturnType := validationFormulaFieldTypePtr(constants.FieldTypeString)

	schema := &models.ObjectMetadata{
		APIName: "CaseLike__c",
		Fields: []models.FieldMetadata{
			{
				APIName:     "Subject__c",
				Type:        constants.FieldTypeFormula,
				Formula:     validationFormulaStringPtr(`"Ticket-1"`),
				FormulaMode: &mode,
				ReturnType:  stringReturnType,
				IsNameField: true,
			},
			{
				APIName:     "Echo__c",
				Type:        constants.FieldTypeFormula,
				Formula:     validationFormulaStringPtr("Name"),
				FormulaMode: &mode,
				ReturnType:  stringReturnType,
			},
		},
	}

	viewSvc := newValidationFormulaViewService(t, schema)
	queryExec := &validationFormulaQueryExecStub{rows: []models.SObject{{"Value": "Ticket-1"}}}
	resolver := NewValidationActiveFormulaResolver(viewSvc, queryExec)

	value, err := resolver(context.Background(), schema, models.SObject{"Subject__c": "Ticket-1"}, &schema.Fields[1])
	if err != nil {
		t.Fatalf("resolver(Name rewrite) error = %v", err)
	}
	if value != "Ticket-1" {
		t.Fatalf("resolver(Name rewrite) value = %#v, want Ticket-1", value)
	}
	if !strings.Contains(queryExec.lastSQL, "t.`Subject__c` AS `Value`") {
		t.Fatalf("resolver(Name rewrite) SQL = %q, want Subject__c projection", queryExec.lastSQL)
	}
	if strings.Contains(queryExec.lastSQL, "t.`Name` AS `Value`") {
		t.Fatalf("resolver(Name rewrite) SQL = %q, want no raw Name column read", queryExec.lastSQL)
	}
}

func TestValidationActiveFormulaResolverBuildsDeepJoinChains(t *testing.T) {
	mode := "active"
	rootToA := "A"
	aToB := "B"
	stringReturnType := validationFormulaFieldTypePtr(constants.FieldTypeString)

	rootSchema := &models.ObjectMetadata{
		APIName: "Root__c",
		Fields: []models.FieldMetadata{
			{
				APIName:          "AId",
				Type:             constants.FieldTypeReference,
				ReferenceTo:      []string{"A__c"},
				RelationshipName: &rootToA,
			},
			{
				APIName:     "ProjectedName__c",
				Type:        constants.FieldTypeFormula,
				Formula:     validationFormulaStringPtr("A.B.Name"),
				FormulaMode: &mode,
				ReturnType:  stringReturnType,
			},
		},
	}
	aSchema := &models.ObjectMetadata{
		APIName: "A__c",
		Fields: []models.FieldMetadata{
			{
				APIName:          "BId",
				Type:             constants.FieldTypeReference,
				ReferenceTo:      []string{"B__c"},
				RelationshipName: &aToB,
			},
		},
	}
	bSchema := &models.ObjectMetadata{
		APIName: "B__c",
		Fields: []models.FieldMetadata{
			{APIName: "Name", Type: constants.FieldTypeString},
		},
	}

	viewSvc := newValidationFormulaViewService(t, rootSchema, aSchema, bSchema)
	queryExec := &validationFormulaQueryExecStub{rows: []models.SObject{{"Value": "leaf-two"}}}
	resolver := NewValidationActiveFormulaResolver(viewSvc, queryExec)

	value, err := resolver(context.Background(), rootSchema, models.SObject{"AId": "a-001"}, &rootSchema.Fields[1])
	if err != nil {
		t.Fatalf("resolver(deep join) error = %v", err)
	}
	if value != "leaf-two" {
		t.Fatalf("resolver(deep join) value = %#v, want leaf-two", value)
	}
	if !strings.Contains(queryExec.lastSQL, "LEFT JOIN `a__c`") || !strings.Contains(queryExec.lastSQL, "LEFT JOIN `b__c`") {
		t.Fatalf("resolver(deep join) SQL = %q, want chained LEFT JOINs", queryExec.lastSQL)
	}
}

func newValidationFormulaViewService(t *testing.T, schemas ...*models.ObjectMetadata) *FormulaViewService {
	t.Helper()
	metaSvc := metadata.NewMetadataService(&validationFormulaMetadataRepo{schemas: schemas}, nil, nil)
	if err := metaSvc.RefreshCache(context.Background()); err != nil {
		t.Fatalf("RefreshCache() error = %v", err)
	}
	return &FormulaViewService{metadata: metaSvc}
}

type validationFormulaMetadataRepo struct {
	ports.MetadataRepository
	schemas []*models.ObjectMetadata
}

func (r *validationFormulaMetadataRepo) GetAllSchemas(ctx context.Context) ([]*models.ObjectMetadata, error) {
	return r.schemas, nil
}

func (r *validationFormulaMetadataRepo) GetSchemaByAPIName(ctx context.Context, apiName string) (*models.ObjectMetadata, error) {
	for _, schema := range r.schemas {
		if schema != nil && strings.EqualFold(schema.APIName, apiName) {
			return schema, nil
		}
	}
	return nil, nil
}

func (r *validationFormulaMetadataRepo) GetAllRelationships(ctx context.Context) ([]*models.Relationship, error) {
	return nil, nil
}

func (r *validationFormulaMetadataRepo) GetAllFlowDefinitions(ctx context.Context) ([]*models.FlowDefinition, error) {
	return nil, nil
}

func (r *validationFormulaMetadataRepo) GetAllValidationRules(ctx context.Context) ([]*models.ValidationRule, error) {
	return nil, nil
}

type validationFormulaQueryExecStub struct {
	rows    []models.SObject
	lastSQL string
}

func (s *validationFormulaQueryExecStub) ExecuteRawSQL(ctx context.Context, sql string, params []interface{}) ([]models.SObject, error) {
	s.lastSQL = sql
	return s.rows, nil
}

func validationFormulaFieldTypePtr(v constants.SchemaFieldType) *models.FieldType {
	fieldType := models.FieldType(v)
	return &fieldType
}

func validationFormulaStringPtr(v string) *string {
	return &v
}