package analytics

import (
	"context"
	"strings"
	"testing"

	"github.com/nexuscrm/shared/pkg/models"
	"pgregory.net/rapid"
)

// fakeSchemaLookup is a hand-rolled stand-in for *metadata.MetadataService.
// We do not use the real service because constructing one pulls in a DB,
// cache warmer, and background refresher — the resolver only needs a
// (objectName -> ObjectMetadata) map.
type fakeSchemaLookup struct {
	schemas map[string]*models.ObjectMetadata
}

func (f *fakeSchemaLookup) GetSchema(_ context.Context, apiName string) *models.ObjectMetadata {
	if f == nil {
		return nil
	}
	if s, ok := f.schemas[apiName]; ok {
		return s
	}
	// Case-insensitive fallback — matches real MetadataService.GetSchema which
	// normalises apiName to lowercase before lookup.
	for k, v := range f.schemas {
		if strings.EqualFold(k, apiName) {
			return v
		}
	}
	return nil
}

func newObj(name string, fields ...models.FieldMetadata) *models.ObjectMetadata {
	obj := &models.ObjectMetadata{APIName: name, Fields: fields}
	obj.FieldsMap = make(map[string]*models.FieldMetadata, len(fields))
	for i := range obj.Fields {
		obj.FieldsMap[strings.ToLower(obj.Fields[i].APIName)] = &obj.Fields[i]
	}
	return obj
}

func plainField(name string) models.FieldMetadata {
	return models.FieldMetadata{APIName: name, Type: models.FieldType("Text")}
}

func refField(name string, ref string) models.FieldMetadata {
	return models.FieldMetadata{APIName: name, Type: models.FieldType("Lookup"), ReferenceTo: []string{ref}}
}

// userReportTemplate returns a JoinTemplate that matches the shape of
// CRT `Campaigns_with_Campaign_Members_and_more_fields__c` — Campaign joined
// to CampaignMember. This is what the later wiring agent will synthesize from
// the CRT row for report 00ORC000003Gyxl2AC.
func userReportTemplate() *JoinTemplate {
	return &JoinTemplate{
		PrimaryTable: "campaign",
		PrimaryAlias: "c",
		Joins: []JoinStep{
			{Table: "campaignmember", Alias: "cm", JoinType: "INNER JOIN", OnLeft: "cm.CampaignId", OnRight: "c.Id"},
		},
		Columns: map[string]QualifiedColumn{},
	}
}

func userReportSchemaLookup() *fakeSchemaLookup {
	return &fakeSchemaLookup{
		schemas: map[string]*models.ObjectMetadata{
			"lead": newObj("lead",
				plainField("Company"),
				plainField("FirstName"),
				plainField("LastName"),
				plainField("Email"),
				plainField("Phone"),
				plainField("Replacing_Database__c"),
				plainField("CloudServices__c"),
				plainField("First_Conversion__c"),
				plainField("Key_Message__c"),
			),
			"contact": newObj("contact",
				plainField("FirstName"),
				plainField("LastName"),
				plainField("Email"),
				plainField("Phone"),
				// Note: contact has neither *Conversion nor Key_Message, so the
				// bare-custom-field heuristic must uniquely resolve to lead.
			),
		},
	}
}

func Test_ResolveReportColumn_UserReportGoldens(t *testing.T) {
	tmpl := userReportTemplate()
	md := userReportSchemaLookup()

	cases := []struct {
		col      string
		wantExpr string
		wantOK   bool
	}{
		// Denormalized on cm — resolves to `cm`.<Field>.
		{"CampaignMember_ContactOrLead.LastName", "`cm`.`LastName`", true},
		{"CampaignMember_ContactOrLead.FirstName", "`cm`.`FirstName`", true},
		{"CampaignMember_ContactOrLead.Email", "`cm`.`Email`", true},
		{"CampaignMember_ContactOrLead.Phone", "`cm`.`Phone`", true},

		// Explicit Lead. branch — synthesized marker.
		{"CampaignMember_ContactOrLead.Lead.Company", leadJoinMarker + "Company", true},
		{"CampaignMember_ContactOrLead.Lead.Replacing_Database__c", leadJoinMarker + "Replacing_Database__c", true},
		{"CampaignMember_ContactOrLead.Lead.CloudServices__c", leadJoinMarker + "CloudServices__c", true},

		// Bare custom fields — exist only on lead schema, so resolver picks lead.
		{"CampaignMember_ContactOrLead.First_Conversion__c", leadJoinMarker + "First_Conversion__c", true},
		{"CampaignMember_ContactOrLead.Key_Message__c", leadJoinMarker + "Key_Message__c", true},
	}

	for _, tc := range cases {
		t.Run(tc.col, func(t *testing.T) {
			expr, alias, ok := resolveReportColumnImpl(context.Background(), tc.col, tmpl, md)
			if ok != tc.wantOK {
				t.Fatalf("ok = %v, want %v (expr=%q)", ok, tc.wantOK, expr)
			}
			if expr != tc.wantExpr {
				t.Fatalf("expr = %q, want %q", expr, tc.wantExpr)
			}
			if alias == "" {
				t.Fatalf("result alias must not be empty for resolved column")
			}
		})
	}
}

func Test_ResolveReportColumn_LegacyAliases(t *testing.T) {
	tmpl := &JoinTemplate{
		PrimaryTable: "account",
		PrimaryAlias: "a",
		Joins:        nil,
		Columns:      map[string]QualifiedColumn{},
	}
	// Every legacy alias must resolve to a non-empty select expression.
	for code := range legacyAliasMap {
		t.Run(code, func(t *testing.T) {
			expr, _, ok := resolveReportColumnImpl(context.Background(), code, tmpl, nil)
			if !ok {
				t.Fatalf("legacy alias %q did not resolve", code)
			}
			if expr == "" {
				t.Fatalf("legacy alias %q resolved to empty expr", code)
			}
		})
	}
}

func Test_ResolveReportColumn_DotNotationAgainstTemplateTables(t *testing.T) {
	// Mirrors Q7 patterns from crt-research-data: `Campaign.Name`, `CampaignMember.Status`.
	tmpl := &JoinTemplate{
		PrimaryTable: "campaign",
		PrimaryAlias: "c",
		Joins: []JoinStep{
			{Table: "campaignmember", Alias: "cm", JoinType: "INNER JOIN", OnLeft: "cm.CampaignId", OnRight: "c.Id"},
		},
		Columns: map[string]QualifiedColumn{},
	}
	cases := []struct {
		col      string
		wantExpr string
	}{
		{"Campaign.Name", "`c`.`Name`"},
		{"CampaignMember.Status", "`cm`.`Status`"},
		{"Campaign.CreatedDate", "`c`.`CreatedDate`"},
		{"CampaignMember.HasResponded", "`cm`.`HasResponded`"},
	}
	for _, tc := range cases {
		t.Run(tc.col, func(t *testing.T) {
			expr, _, ok := resolveReportColumnImpl(context.Background(), tc.col, tmpl, nil)
			if !ok {
				t.Fatalf("expected ok for %q", tc.col)
			}
			if expr != tc.wantExpr {
				t.Fatalf("expr = %q, want %q", expr, tc.wantExpr)
			}
		})
	}
}

func Test_ResolveReportColumn_ThreeSegmentCrossJoin(t *testing.T) {
	tmpl := &JoinTemplate{
		PrimaryTable: "opportunity",
		PrimaryAlias: "o",
		Joins: []JoinStep{
			{Table: "account", Alias: "a", JoinType: "LEFT JOIN", OnLeft: "a.Id", OnRight: "o.AccountId"},
		},
		Columns: map[string]QualifiedColumn{},
	}
	md := &fakeSchemaLookup{
		schemas: map[string]*models.ObjectMetadata{
			"opportunity": newObj("opportunity", refField("OwnerId", "user"), refField("AccountId", "account")),
			"user":        newObj("user", plainField("Name")),
			"account":     newObj("account", plainField("Name"), refField("OwnerId", "user")),
		},
	}

	// Classic Opportunity.Owner.Name — must not panic and must return ok=true.
	// Resolver now emits a __LOOKUP_JOIN__ marker; ExpandLookupJoinMarkers
	// rewrites it into a real qualified column downstream. The marker
	// payload must carry the origin alias, FK column, target table, and
	// target field — check each segment.
	expr, _, ok := resolveReportColumnImpl(context.Background(), "Opportunity.OwnerId.Name", tmpl, md)
	if !ok {
		t.Fatalf("Opportunity.OwnerId.Name did not resolve; expr=%q", expr)
	}
	if !strings.HasPrefix(expr, lookupJoinMarker) {
		t.Fatalf("expected lookup-join marker, got %q", expr)
	}
	if !strings.Contains(expr, "|OwnerId|user|Name") {
		t.Fatalf("marker payload missing FK/target/field, got %q", expr)
	}

	// Account FK traversal onto User.
	expr2, _, ok2 := resolveReportColumnImpl(context.Background(), "Account.OwnerId.Name", tmpl, md)
	if !ok2 {
		t.Fatalf("Account.OwnerId.Name did not resolve; expr=%q", expr2)
	}
	if !strings.HasPrefix(expr2, lookupJoinMarker) {
		t.Fatalf("expected lookup-join marker, got %q", expr2)
	}
	if !strings.Contains(expr2, "|OwnerId|user|Name") {
		t.Fatalf("account path marker missing expected payload, got %q", expr2)
	}
}

func Test_ResolveReportColumn_UnknownInputsDoNotPanic(t *testing.T) {
	cases := []string{"", ".", "..", "...", "Foo.", ".Bar", "no_such_column", "a.b.c.d.e"}
	for _, c := range cases {
		t.Run(c, func(t *testing.T) {
			defer func() {
				if r := recover(); r != nil {
					t.Fatalf("panic on %q: %v", c, r)
				}
			}()
			_, _, ok := resolveReportColumnImpl(context.Background(), c, userReportTemplate(), nil)
			if ok {
				// Not strictly an error — some inputs may legitimately resolve
				// — but these shouldn't.
				t.Logf("note: %q unexpectedly resolved ok=true", c)
			}
		})
	}
}

func Test_ResolveReportColumn_NilTemplate(t *testing.T) {
	// Legacy aliases with no template still resolve (fall back to bare-column form).
	for code := range legacyAliasMap {
		if _, _, ok := resolveReportColumnImpl(context.Background(), code, nil, nil); !ok {
			// With nil template, legacy aliases mapping to dot paths cannot
			// be fully resolved; bare ones should.
			if !strings.Contains(legacyAliasMap[code], ".") {
				t.Fatalf("legacy %q failed with nil template", code)
			}
		}
	}
	// Template-dependent paths must fail-soft.
	if _, _, ok := resolveReportColumnImpl(context.Background(), "Campaign.Name", nil, nil); ok {
		t.Fatalf("dot-path with nil template should not resolve")
	}
	// Polymorphic prefix also fails without a template.
	if _, _, ok := resolveReportColumnImpl(context.Background(), "CampaignMember_ContactOrLead.LastName", nil, nil); ok {
		t.Fatalf("poly prefix with nil template should not resolve")
	}
}

// CRITICAL-1: SQL injection via polymorphic marker payload. Any malformed
// input that would otherwise slip into a backticked SQL identifier MUST be
// rejected with ok=false (fail-soft / NULL placeholder). The real attack
// vector is a saved Report whose Columns entry contains an embedded
// backtick that defeats backtick quoting.
func Test_ResolveReportColumn_RejectsSQLInjectionAttempts(t *testing.T) {
	tmpl := userReportTemplate()
	md := userReportSchemaLookup()
	ctx := context.Background()
	cases := []string{
		// Embedded backtick (defeats `...` quoting).
		"CampaignMember_ContactOrLead.Lead.Name`; DROP TABLE users--",
		"CampaignMember_ContactOrLead.Lead.Name` OR 1=1--",
		// Embedded whitespace + boolean.
		"CampaignMember_ContactOrLead.Lead.Name OR 1=1",
		// Embedded semicolon + comment.
		"Account.Name`); --",
		// Two-segment dot-notation with unsafe leaf.
		"Campaign.Name`;DROP",
	}
	for _, col := range cases {
		t.Run(col, func(t *testing.T) {
			_, _, ok := resolveReportColumnImpl(ctx, col, tmpl, md)
			if ok {
				t.Fatalf("attacker input %q unexpectedly resolved ok=true — SQL injection regression!", col)
			}
		})
	}

	// Sanity: a clean column must STILL resolve after the validator lands.
	expr, _, ok := resolveReportColumnImpl(ctx, "CampaignMember_ContactOrLead.Lead.Company", tmpl, md)
	if !ok || expr == "" {
		t.Fatalf("clean column broken by validator: ok=%v expr=%q", ok, expr)
	}
}

func Test_ResolveReportColumn_NilMetadata(t *testing.T) {
	tmpl := userReportTemplate()
	// Bare custom field with nil metadata fails-soft.
	_, _, ok := resolveReportColumnImpl(context.Background(), "CampaignMember_ContactOrLead.First_Conversion__c", tmpl, nil)
	if ok {
		t.Fatalf("bare custom field should not resolve without metadata")
	}
	// Three-segment cross-join with nil metadata also fails-soft.
	_, _, ok = resolveReportColumnImpl(context.Background(), "Campaign.SomeFK.Name", tmpl, nil)
	if ok {
		t.Fatalf("three-segment path should not resolve without metadata")
	}
	// Explicit Lead.<field> still resolves via marker contract even with nil md.
	expr, _, ok := resolveReportColumnImpl(context.Background(), "CampaignMember_ContactOrLead.Lead.Company", tmpl, nil)
	if !ok || expr != leadJoinMarker+"Company" {
		t.Fatalf("explicit Lead. path: ok=%v expr=%q", ok, expr)
	}
}

// TestProperty_ColumnPathResolverIsTotal enforces §7.1 property #4: for any
// short random string from a realistic alphabet, ResolveReportColumn returns
// a valid (string, string, bool) triple without panicking.
func TestProperty_ColumnPathResolverIsTotal(t *testing.T) {
	// Silence the debug sink so the property test does not flood stdout.
	restore := setUnknownColumnSinkForTest(func(string, string) {})
	defer restore()

	templates := []*JoinTemplate{
		nil,
		{}, // empty
		{
			PrimaryTable: "lead",
			PrimaryAlias: "l",
			Columns:      map[string]QualifiedColumn{},
		},
		userReportTemplate(),
		{
			PrimaryTable: "opportunity",
			PrimaryAlias: "o",
			Joins: []JoinStep{
				{Table: "account", Alias: "a", JoinType: "LEFT JOIN", OnLeft: "a.Id", OnRight: "o.AccountId"},
				{Table: "user", Alias: "u", JoinType: "LEFT JOIN", OnLeft: "u.Id", OnRight: "o.OwnerId"},
			},
			Columns: map[string]QualifiedColumn{},
		},
	}

	md := &fakeSchemaLookup{
		schemas: map[string]*models.ObjectMetadata{
			"opportunity": newObj("opportunity", refField("OwnerId", "user"), refField("AccountId", "account")),
			"user":        newObj("user", plainField("Name")),
			"account":     newObj("account", plainField("Name")),
			"lead":        newObj("lead", plainField("Company")),
			"contact":     newObj("contact", plainField("AccountId")),
		},
	}

	rapid.Check(t, func(rt *rapid.T) {
		col := rapid.StringMatching(`[A-Za-z0-9_.$@]{0,200}`).Draw(rt, "col")
		for _, tmpl := range templates {
			func() {
				defer func() {
					if r := recover(); r != nil {
						rt.Fatalf("panic on col=%q tmpl=%+v: %v", col, tmpl, r)
					}
				}()
				expr, alias, ok := resolveReportColumnImpl(context.Background(), col, tmpl, md)
				if ok && expr == "" {
					rt.Fatalf("ok=true but empty expr for col=%q", col)
				}
				if ok && alias == "" {
					rt.Fatalf("ok=true but empty alias for col=%q", col)
				}
			}()
		}
	})
}