package packs

import (
	"context"
	"regexp"
	"testing"

	sqlmock "github.com/DATA-DOG/go-sqlmock"

	kernelconnector "github.com/nexuscrm/kernel/pkg/connector"
	connector "github.com/pingcap-inc/nexuscrm-contract/pkg/connector"
)

// ---------------------------------------------------------------------------
// TestGetFieldMappings_LegacyByteIdentical
// ---------------------------------------------------------------------------

// TestGetFieldMappings_LegacyByteIdentical asserts that after the
// _System_ObjectFieldMapping table gains the Direction column (defaulting to
// 'legacy' for existing rows), GetFieldMappings with AND Direction = 'legacy'
// returns the same rows and ApplyFieldMappings produces byte-identical output.
func TestGetFieldMappings_LegacyByteIdentical(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	legacyCols := []string{"Source", "SourceField", "TargetField", "DefaultValue", "SourceLookupField"}
	rows := sqlmock.NewRows(legacyCols).
		AddRow("standard", "FirstName", "FirstName", nil, nil).
		AddRow("standard", "LastName", "LastName", nil, nil)

	mock.ExpectQuery("FROM _System_ObjectFieldMapping").
		WithArgs("Lead", "Contact", "convertLead").
		WillReturnRows(rows)

	mappings, err := getFieldMappings(db, "Lead", "Contact", "convertLead")
	if err != nil {
		t.Fatalf("getFieldMappings: %v", err)
	}
	if len(mappings) != 2 {
		t.Fatalf("expected 2 mappings, got %d", len(mappings))
	}
	if mappings[0].SourceField != "FirstName" || mappings[0].TargetField != "FirstName" {
		t.Errorf("unexpected mapping[0]: %+v", mappings[0])
	}
	if mappings[1].SourceField != "LastName" || mappings[1].TargetField != "LastName" {
		t.Errorf("unexpected mapping[1]: %+v", mappings[1])
	}

	source := map[string]interface{}{"FirstName": "Jane", "LastName": "Doe"}
	target := map[string]interface{}{}
	connector.ApplyFieldMappings(mappings, source, target)

	if target["FirstName"] != "Jane" {
		t.Errorf("target[FirstName] = %v, want Jane", target["FirstName"])
	}
	if target["LastName"] != "Doe" {
		t.Errorf("target[LastName] = %v, want Doe", target["LastName"])
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

// ---------------------------------------------------------------------------
// Validator tests
// ---------------------------------------------------------------------------

func TestValidateConnectorFieldMappingRow_Valid(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    connector.DirectionInbound,
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err != nil {
		t.Errorf("expected valid row to pass, got: %v", err)
	}
}

func TestValidateConnectorFieldMappingRow_DirectionLegacyRejected(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    "legacy",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
		t.Error("expected error for Direction='legacy', got nil")
	}
}

func TestValidateConnectorFieldMappingRow_DirectionBlankRejected(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    "",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
		t.Error("expected error for blank Direction, got nil")
	}
}

func TestValidateConnectorFieldMappingRow_DirectionUnknownRejected(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    "both",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
		t.Error("expected error for unknown Direction, got nil")
	}
}

func TestValidateConnectorFieldMappingRow_ConflictPolicyUnknownRejected(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:         "hubspot",
		SourceObject:   "Contact",
		TargetObject:   "Contact",
		ActionType:     "syncContact",
		SourceField:    "email",
		TargetField:    "Email",
		Direction:      connector.DirectionInbound,
		ConflictPolicy: "merge",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
		t.Error("expected error for unknown ConflictPolicy, got nil")
	}
}

func TestValidateConnectorFieldMappingRow_ConflictPolicyEmptyAllowed(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:         "hubspot",
		SourceObject:   "Contact",
		TargetObject:   "Contact",
		ActionType:     "syncContact",
		SourceField:    "email",
		TargetField:    "Email",
		Direction:      connector.DirectionInbound,
		ConflictPolicy: "",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err != nil {
		t.Errorf("empty ConflictPolicy should be allowed, got: %v", err)
	}
}

func TestValidateConnectorFieldMappingRow_ValueMapValidPasses(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "lifecycle_stage",
		TargetField:  "Stage__c",
		Direction:    connector.DirectionInbound,
		ValueMap:     `{"lead":"Open","customer":"Won","true":true,"1":1}`,
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err != nil {
		t.Errorf("valid ValueMap should pass, got: %v", err)
	}
}

func TestValidateConnectorFieldMappingRow_ValueMapInvalidRejected(t *testing.T) {
	base := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "lifecycle_stage",
		TargetField:  "Stage__c",
		Direction:    connector.DirectionInbound,
	}
	cases := []struct {
		name     string
		valueMap string
	}{
		{"array", `["lead","Open"]`},
		{"empty object", `{}`},
		{"blank source key", `{"":"Open"}`},
		{"duplicate source key", `{"lead":"Open","lead":"Won"}`},
		{"nested target value", `{"lead":{"status":"Open"}}`},
		{"null target value", `{"lead":null}`},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			row := base
			row.ValueMap = tc.valueMap
			if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
				t.Errorf("expected ValueMap %s to be rejected", tc.name)
			}
		})
	}
}

func TestValidateConnectorFieldMappingRow_RequiredFieldsMissing(t *testing.T) {
	base := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    connector.DirectionInbound,
	}
	cases := []struct {
		name   string
		mutate func(*connectorFieldMappingInput)
	}{
		{"missing Source", func(r *connectorFieldMappingInput) { r.Source = "" }},
		{"missing SourceObject", func(r *connectorFieldMappingInput) { r.SourceObject = "" }},
		{"missing TargetObject", func(r *connectorFieldMappingInput) { r.TargetObject = "" }},
		{"missing ActionType", func(r *connectorFieldMappingInput) { r.ActionType = "" }},
		{"missing SourceField", func(r *connectorFieldMappingInput) { r.SourceField = "" }},
	}
	for _, tc := range cases {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			r := base
			tc.mutate(&r)
			if err := kernelconnector.ValidateConnectorFieldMappingRow(r); err == nil {
				t.Errorf("expected error for %s, got nil", tc.name)
			}
		})
	}
}

func TestValidateConnectorFieldMappingRow_TransformExpressionEmptyAllowed(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:              "hubspot",
		SourceObject:        "Contact",
		TargetObject:        "Contact",
		ActionType:          "syncContact",
		SourceField:         "email",
		TargetField:         "Email",
		Direction:           connector.DirectionInbound,
		TransformExpression: "",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err != nil {
		t.Errorf("empty TransformExpression should be allowed, got: %v", err)
	}
}

func TestValidateConnectorFieldMappingRow_TransformExpressionValidPasses(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:              "hubspot",
		SourceObject:        "Contact",
		TargetObject:        "Contact",
		ActionType:          "syncContact",
		SourceField:         "email",
		TargetField:         "Email",
		Direction:           connector.DirectionInbound,
		TransformExpression: "UPPER(email)",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err != nil {
		t.Errorf("valid TransformExpression UPPER(email) should pass, got: %v", err)
	}
}

func TestValidateConnectorFieldMappingRow_TransformExpressionInvalidRejected(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:              "hubspot",
		SourceObject:        "Contact",
		TargetObject:        "Contact",
		ActionType:          "syncContact",
		SourceField:         "email",
		TargetField:         "Email",
		Direction:           connector.DirectionInbound,
		TransformExpression: "UPPER(",
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
		t.Error("expected error for malformed TransformExpression UPPER(, got nil")
	}
}

func TestValidateConnectorFieldMappingRow_PriorStateFunctionsRejected(t *testing.T) {
	restricted := []string{
		"ISCHANGED(Name)", "PRIORVALUE(Name)", "ISNEW()", "ISCLONE()",
		"ISCHANGED (Name)", "PRIORVALUE (Name)", "ISNEW ()", "ISCLONE ()",
		"ischanged(Name)", "priorvalue(Name)",
		// block-comment evasion — the validator normalizes before walking the
		// AST, so the comment is stripped and the prior-state magic identifier
		// is still detected (a raw "ISCHANGED(" scan would miss these).
		"IF(ISCHANGED/*x*/(Name), \"A\", \"B\")",
		"PRIORVALUE/*c*/(Name)",
	}
	for _, expr := range restricted {
		row := connectorFieldMappingInput{
			Source:              "test-pack",
			SourceObject:        "Contact",
			TargetObject:        "Contact",
			ActionType:          "syncContact",
			SourceField:         "email",
			TargetField:         "Email",
			Direction:           connector.DirectionInbound,
			TransformExpression: expr,
		}
		if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
			t.Errorf("expected error for restricted TransformExpression %q, got nil", expr)
		}
	}
}

func TestValidateConnectorFieldMappingRow_SessionGlobalsRejected(t *testing.T) {
	globals := []string{
		"$User.Name", "$Profile.Name", "$UserRole.Name",
		"$Organization.Name", "$Api.Session_ID",
		"$System.OriginDateTime", "$Setup.MyCustomSetting__c",
		"$Permission.ViewSetup", "$ObjectType.Account",
		"$Label.MyLabel", "$CustomMetadata.MyType.MyRecord",
		"$RecordType.Name",
	}
	for _, expr := range globals {
		row := connectorFieldMappingInput{
			Source:              "test-pack",
			SourceObject:        "Contact",
			TargetObject:        "Contact",
			ActionType:          "syncContact",
			SourceField:         "email",
			TargetField:         "Email",
			Direction:           connector.DirectionInbound,
			TransformExpression: expr,
		}
		if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
			t.Errorf("expected error for session global TransformExpression %q, got nil", expr)
		}
	}
}

func TestValidateConnectorFieldMappingRow_RecordGlobalAllowed(t *testing.T) {
	allowed := []string{
		"$Record.Email",
		"UPPER($Record.Email)",
		"record.Email",          // lowercase record alias resolves like $Record
		"email",                 // bare field reference — present in flat sourceRecord
		"UPPER(email)",          // builtin over a bare field
		"LEFT(email, 5)",        // builtin with multiple args
		`UPPER("Account.Name")`, // dot inside a string literal is not a relation
	}
	for _, expr := range allowed {
		row := connectorFieldMappingInput{
			Source:              "test-pack",
			SourceObject:        "Contact",
			TargetObject:        "Contact",
			ActionType:          "syncContact",
			SourceField:         "email",
			TargetField:         "Email",
			Direction:           connector.DirectionInbound,
			TransformExpression: expr,
		}
		if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err != nil {
			t.Errorf("$Record-based TransformExpression %q should be allowed, got: %v", expr, err)
		}
	}
}

func TestValidateConnectorFieldMappingRow_BareNamespacesRejected(t *testing.T) {
	// Bare (non-$) runtime namespaces the connector executor does not inject:
	// the engine reserves lowercase user/prior/env/var as grammar roots, none of
	// which exist in a flat Record-only context, so a dotted access on them
	// faults with "unknown name" at runtime. The earlier $-prefixed-global regex
	// never saw these because they carry no $ sigil.
	rejected := []string{
		"user.Name",
		"prior.Status",
		"env.SomeVar",
		"var.SomeVar",
		`IF(prior.Status == "x", 1, 2)`,
	}
	for _, expr := range rejected {
		row := connectorFieldMappingInput{
			Source:              "test-pack",
			SourceObject:        "Contact",
			TargetObject:        "Contact",
			ActionType:          "syncContact",
			SourceField:         "email",
			TargetField:         "Email",
			Direction:           connector.DirectionInbound,
			TransformExpression: expr,
		}
		if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
			t.Errorf("bare-namespace TransformExpression %q should be rejected (not injected at runtime), got nil", expr)
		}
	}
}

func TestValidateConnectorFieldMappingRow_RelationshipPathRejected(t *testing.T) {
	rejected := []string{
		// $Record-qualified traversal
		"$Record.Account.Name",
		"UPPER($Record.Account.AnnualRevenue)",
		"$Record.Owner.Email",
		// multi-hop $Record chain
		"$Record.Account__r.Owner__r.Name",
		// bare relationship roots (no $Record. prefix) — these pass
		// ValidateSyntax but fault at runtime with no Fetcher; the prior
		// narrow regex missed them entirely.
		"Account.Name",
		"Owner.Email",
		"Parent.Name",
		"UPPER(Account.Name)",
		"Account__r.Industry",
		"Account__r.Owner__r.Name",
	}
	for _, expr := range rejected {
		row := connectorFieldMappingInput{
			Source:              "test-pack",
			SourceObject:        "Contact",
			TargetObject:        "Contact",
			ActionType:          "syncContact",
			SourceField:         "email",
			TargetField:         "Email",
			Direction:           connector.DirectionInbound,
			TransformExpression: expr,
		}
		if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
			t.Errorf("relationship-path TransformExpression %q should be rejected (no Fetcher at runtime), got nil", expr)
		}
	}
}

func TestValidateConnectorFieldMappingRow_BlankTargetFieldRejected(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "",
		Direction:    connector.DirectionInbound,
	}
	if err := kernelconnector.ValidateConnectorFieldMappingRow(row); err == nil {
		t.Error("expected error for blank TargetField, got nil")
	}
}

// ---------------------------------------------------------------------------
// Writer tests (sqlmock)
// ---------------------------------------------------------------------------

func TestUpsertConnectorFieldMapping_DeterministicID(t *testing.T) {
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    connector.DirectionInbound,
	}

	id1 := connectorFieldMappingIDForInput(row)
	id2 := connectorFieldMappingIDForInput(row)
	if id1 != id2 {
		t.Errorf("ID not stable: %q != %q", id1, id2)
	}
	if id1 == "" {
		t.Error("ID must not be empty")
	}
}

func TestUpsertConnectorFieldMapping_Canonicalization(t *testing.T) {
	// Whitespace- and case-distinct inputs must collapse to the same ID because
	// _System_ObjectFieldMapping's business-key columns use case-insensitive DB
	// collation.
	row1 := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    connector.DirectionInbound,
	}
	row2 := connectorFieldMappingInput{
		Source:       "  HubSpot  ",
		SourceObject: " contact ",
		TargetObject: "CONTACT",
		ActionType:   "SyncContact ",
		SourceField:  " EMAIL",
		TargetField:  "Email",
		Direction:    " INBOUND ",
	}
	id1 := connectorFieldMappingIDForInput(row1)
	id2 := connectorFieldMappingIDForInput(row2)
	if id1 != id2 {
		t.Errorf("whitespace-distinct inputs produced different IDs: %q vs %q", id1, id2)
	}
}

func TestUpsertConnectorFieldMapping_AsymmetricDirectionBothInsert(t *testing.T) {
	// Two rows with the same business key but different Direction values must
	// produce different IDs (proves the unique index includes Direction).
	base := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
	}
	inbound := base
	inbound.Direction = connector.DirectionInbound
	outbound := base
	outbound.Direction = connector.DirectionOutbound

	idIn := connectorFieldMappingIDForInput(inbound)
	idOut := connectorFieldMappingIDForInput(outbound)
	if idIn == idOut {
		t.Errorf("inbound and outbound must have distinct IDs; both got %q", idIn)
	}
}

func TestUpsertConnectorFieldMapping_SameKeyUpserts(t *testing.T) {
	// Two calls with the same business key → single sqlmock exec (upsert, not insert+insert).
	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    connector.DirectionInbound,
	}

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	// Expect two ExecContext calls (each call fires one SQL).
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_ObjectFieldMapping")).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO _System_ObjectFieldMapping")).
		WillReturnResult(sqlmock.NewResult(1, 1))

	if err := upsertConnectorFieldMapping(context.Background(), db, row); err != nil {
		t.Fatalf("first upsert: %v", err)
	}
	if err := upsertConnectorFieldMapping(context.Background(), db, row); err != nil {
		t.Fatalf("second upsert: %v", err)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations unmet: %v", err)
	}
}

func TestUpsertConnectorFieldMapping_RejectsInvalidDirection(t *testing.T) {
	db, _, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	row := connectorFieldMappingInput{
		Source:       "hubspot",
		SourceObject: "Contact",
		TargetObject: "Contact",
		ActionType:   "syncContact",
		SourceField:  "email",
		TargetField:  "Email",
		Direction:    "legacy",
	}
	if err := upsertConnectorFieldMapping(context.Background(), db, row); err == nil {
		t.Error("expected error for Direction='legacy', got nil")
	}
}