package analytics

import (
	"context"
	"strings"
	"testing"

	"github.com/nexuscrm/backend/internal/features/metadata"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/mock"
	"github.com/stretchr/testify/require"
)

// buildIntegrationSchemas returns the metadata fixture the Test A / Test B
// integration tests share. Includes Campaign, CampaignMember, Lead, Contact
// plus the scoped-entity objects used by Test B. CampaignMember.CampaignId
// carries a ReferenceTo + RelationshipName("CampaignMembers") so the real
// MetadataService.buildRelationshipSnapshot populates Campaign's child
// relationship index — this is what lets the CRT synthesizer's
// findChildRelationship walk succeed against the real adapter (BLOCKER-3
// regression guard).
func buildIntegrationSchemas() []*models.ObjectMetadata {
	campaign := &models.ObjectMetadata{
		APIName:   "Campaign",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	for _, n := range []string{"Id", "Name", "ParentId"} {
		campaign.Fields = append(campaign.Fields, models.FieldMetadata{APIName: n})
	}

	cm := &models.ObjectMetadata{
		APIName:   "CampaignMember",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	campaignRel := "CampaignMembers"
	cm.Fields = append(cm.Fields, models.FieldMetadata{
		APIName:          "CampaignId",
		Type:             "reference",
		ReferenceTo:      []string{"Campaign"},
		RelationshipName: &campaignRel,
	})
	for _, n := range []string{
		"Id", "LeadOrContactId",
		"FirstName", "LastName", "Email", "Phone",
		"First_Conversion__c", "Key_Message__c",
	} {
		cm.Fields = append(cm.Fields, models.FieldMetadata{APIName: n})
	}

	lead := &models.ObjectMetadata{
		APIName:   "Lead",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	for _, n := range []string{
		"Id", "Company", "FirstName", "LastName", "Email", "Phone",
		"Replacing_Database__c", "CloudServices__c",
	} {
		// NOTE: First_Conversion__c and Key_Message__c are DELIBERATELY
		// omitted here — see §4.8 step 3 "fail-soft" bare polymorphic
		// lookup. In production these exist on the real Lead schema and
		// would resolve via the Case-3 heuristic; the test fixture omits
		// them to exercise the NULL placeholder path.
		lead.Fields = append(lead.Fields, models.FieldMetadata{APIName: n})
	}

	contact := &models.ObjectMetadata{
		APIName:   "Contact",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	for _, n := range []string{"Id", "AccountId", "FirstName", "LastName", "Email"} {
		contact.Fields = append(contact.Fields, models.FieldMetadata{APIName: n})
	}

	// Scoped entity fixture for Test B.
	poc := &models.ObjectMetadata{
		APIName:   "PoC__c",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	poc.Fields = append(poc.Fields,
		models.FieldMetadata{APIName: "Id"},
		models.FieldMetadata{APIName: "Name"},
		models.FieldMetadata{APIName: "Opportunity__c", ReferenceTo: []string{"Opportunity"}},
	)

	opp := &models.ObjectMetadata{
		APIName:   "Opportunity",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	for _, n := range []string{"Id", "Name"} {
		opp.Fields = append(opp.Fields, models.FieldMetadata{APIName: n})
	}

	// Nested fixture for Test C — A__c exists, B__c intentionally does not.
	aObj := &models.ObjectMetadata{
		APIName:   "A__c",
		FieldsMap: map[string]*models.FieldMetadata{},
	}
	aObj.Fields = append(aObj.Fields, models.FieldMetadata{APIName: "Id"})

	out := []*models.ObjectMetadata{campaign, cm, lead, contact, poc, opp, aObj}
	for _, o := range out {
		for i := range o.Fields {
			o.FieldsMap[strings.ToLower(o.Fields[i].APIName)] = &o.Fields[i]
		}
	}
	return out
}

func newIntegrationMetadata(t *testing.T) *metadata.MetadataService {
	t.Helper()
	repo := newUnitMockMetaRepo(buildIntegrationSchemas()...)
	return metadata.NewMetadataService(repo, nil, nil)
}

// --- Test A: user's target report (issue #612) ---------------------------
//
// BLOCKER-4 rewrite: this test drives the REAL resolver + synthesizer +
// loader + polymorphic marker expander end-to-end. It used to install a
// hand-built mockCRTResolver whose stub template bypassed every code path
// the PR was supposed to exercise — so it could not have caught BLOCKER-3
// (collectChildCandidates was a no-op in production). The new harness:
//
//   - wires a real NewCRTLoader over a mockQueryService that returns a
//     realistic _System_ReportType row for the user's CRT.
//   - wires a real NewCRTResolver + real crtSynthesizer against a real
//     MetadataService built from buildIntegrationSchemas (which seeds
//     CampaignMember → Campaign as a proper FK + RelationshipName so the
//     MetadataService.GetChildRelationships path returns CampaignMember
//     under the "Campaign" key — exactly the production discovery path).
//   - drives svc.RunReportDefinition and captures ExecuteRawSQL, asserting
//     the generated SQL carries base, cm join, AND the polymorphic lead
//     join installed by the marker expander.
func TestCRTIntegration_A_TargetReport_612(t *testing.T) {
	ctx := context.Background()

	// mockQueryService serves two roles:
	//   (a) mockCRTLoader underneath NewCRTLoader — Query() returns the
	//       _System_ReportType row.
	//   (b) ExecuteRawSQL captures the final JOIN SQL.
	mqs := new(mockQueryService)

	// Stub CRT loader directly — avoids needing a sqlmock DB in the integration
	// path. The loader-SQL path is covered by crt_loader_test.go.
	fakeCRT := &mockCRTRepo{row: &models.SystemReportType{
		ID:         "rt-612",
		FullName:   "Campaigns_with_Campaign_Members_and_more_fields",
		Label:      "Campaigns with Members",
		BaseObject: "Campaign",
		Deployed:   true,
		Sections:   `[{"columns":[{"field":"Name","table":"Campaign"}]}]`,
		Join:       `{"outerJoin":"false","relationship":"CampaignMembers"}`,
		Metadata:   "{}",
	}}

	md := newIntegrationMetadata(t)
	crtResolver := newCRTResolverForTest(fakeCRT, newCRTMetadataAdapter(md), buildStdResolver(), nil)

	svc := NewReportService(mqs, md, nil, nil, WithCRTResolver(crtResolver))

	def := &models.ReportDefinition{
		ID:           "rpt-612",
		Name:         "Campaigns with Members and more fields",
		SourceObject: "Campaigns_with_Campaign_Members_and_more_fields__c",
		ReportType:   "tabular",
		ReportFormat: "Tabular",
		Columns: []string{
			"CampaignMember_ContactOrLead.Lead.Company",
			"CampaignMember_ContactOrLead.LastName",
			"CampaignMember_ContactOrLead.FirstName",
			"CampaignMember_ContactOrLead.Email",
			"CampaignMember_ContactOrLead.Phone",
			"CampaignMember_ContactOrLead.Lead.Replacing_Database__c",
			"CampaignMember_ContactOrLead.Lead.CloudServices__c",
			"CampaignMember_ContactOrLead.First_Conversion__c",
			"CampaignMember_ContactOrLead.Key_Message__c",
		},
	}

	// Capture ExecuteRawSQL arguments.
	var capturedSQL string
	mqs.On("ExecuteRawSQL",
		mock.Anything, mock.AnythingOfType("string"),
		mock.AnythingOfType("[]interface {}"), mock.Anything,
	).Run(func(args mock.Arguments) {
		capturedSQL = args.String(1)
	}).Return([]models.SObject{
		{"Name": "row1"}, {"Name": "row2"}, {"Name": "row3"},
	}, nil)

	result, err := svc.RunReportDefinition(ctx, def, &models.UserSession{ID: "u1"}, nil, 2000)
	require.NoError(t, err)
	require.NotNil(t, result)
	assert.Len(t, result.Rows, 3)

	// SQL assertions — case-insensitive because the query builder may adjust
	// casing.
	lowerSQL := strings.ToLower(capturedSQL)
	assert.Contains(t, lowerSQL, "from `campaign`", "must target campaign as primary table")
	assert.Contains(t, lowerSQL, "`campaignmember`", "must carry the CM join from the synthesizer walk (BLOCKER-3 regression guard)")
	assert.Contains(t, lowerSQL, "`lead`", "polymorphic lead join must be installed from markers")
	assert.Contains(t, lowerSQL, "left join `lead`", "lead join must be LEFT JOIN")

	// Each resolved report column should project into SELECT. The synthesizer
	// walk picks `t2` for the first join (CampaignMember) and the marker
	// expander installs `l` for the polymorphic lead side.
	assert.Contains(t, capturedSQL, "`l`.`Company`")
	assert.Contains(t, capturedSQL, "`l`.`Replacing_Database__c`")
	assert.Contains(t, capturedSQL, "`l`.`CloudServices__c`")
	// cm-denormalized fields resolve onto the synthesizer-assigned cm alias.
	assert.Contains(t, capturedSQL, "`t2`.`LastName`")
	assert.Contains(t, capturedSQL, "`t2`.`FirstName`")
	assert.Contains(t, capturedSQL, "`t2`.`Email`")
	assert.Contains(t, capturedSQL, "`t2`.`Phone`")
	// First_Conversion__c and Key_Message__c are intentionally missing from
	// the fixture's Lead schema so the Case-3 heuristic falls through and
	// emits NULL placeholders — §4.8 step 3 fail-soft path. Real production
	// lead schemas carry these fields and the heuristic would resolve them.
	// The NULL placeholder's result alias is derived from the last dot
	// segment of the original column path (joinResultAlias fallback).
	assert.Contains(t, capturedSQL, "NULL as `First_Conversion__c`")
	assert.Contains(t, capturedSQL, "NULL as `Key_Message__c`")
}

// --- Test B: synthetic CustomEntity scoped -------------------------------

func TestCRTIntegration_B_SyntheticScoped(t *testing.T) {
	ctx := context.Background()

	// Build the resolver against the real narrow-interface fake so we
	// exercise the synthesizeSynthetic C1 path end-to-end.
	md := newFakeMetadata()
	md.addObject("", "PoC__c", "Id", "Name", "Opportunity__c->Opportunity:Opp_rel")
	md.addObject("", "Opportunity", "Id", "Name")

	resolver := newCRTResolverForTest(&mockCRTRepo{}, md, buildStdResolver(), nil)

	mqs := new(mockQueryService)
	svc := NewReportService(mqs, newIntegrationMetadata(t), nil, nil, WithCRTResolver(resolver))

	def := &models.ReportDefinition{
		ID:           "rpt-poc",
		Name:         "PoC scoped",
		SourceObject: "CustomEntity$PoC__c@Opportunity.Opportunity__c",
		ReportType:   "tabular",
		ReportFormat: "Tabular",
		Columns:      []string{"Name"},
	}

	var capturedSQL string
	mqs.On("ExecuteRawSQL",
		mock.Anything, mock.AnythingOfType("string"),
		mock.AnythingOfType("[]interface {}"), mock.Anything,
	).Run(func(args mock.Arguments) {
		capturedSQL = args.String(1)
	}).Return([]models.SObject{}, nil)

	result, err := svc.RunReportDefinition(ctx, def, &models.UserSession{ID: "u1"}, nil, 2000)
	require.NoError(t, err)
	require.NotNil(t, result)
	assert.Empty(t, result.Warnings)

	lowerSQL := strings.ToLower(capturedSQL)
	assert.Contains(t, lowerSQL, "from `poc__c`")
	assert.Contains(t, lowerSQL, "left join `opportunity`")
}

// --- Test C: graceful degradation — nested synthetic ---------------------

func TestCRTIntegration_C_NestedDegraded(t *testing.T) {
	ctx := context.Background()

	md := newFakeMetadata()
	md.addObject("", "A__c", "Id", "Name")
	// B__c intentionally missing.

	resolver := newCRTResolverForTest(&mockCRTRepo{}, md, buildStdResolver(), nil)

	mqs := new(mockQueryService)
	svc := NewReportService(mqs, newIntegrationMetadata(t), nil, nil, WithCRTResolver(resolver))

	def := &models.ReportDefinition{
		ID:           "rpt-nested",
		Name:         "Nested synthetic",
		SourceObject: "CustomEntityCustomEntity$A__c$B__c",
		ReportType:   "tabular",
		ReportFormat: "Tabular",
		Columns:      []string{"Name"},
	}

	mqs.On("ExecuteRawSQL",
		mock.Anything, mock.AnythingOfType("string"),
		mock.AnythingOfType("[]interface {}"), mock.Anything,
	).Return([]models.SObject{}, nil)

	result, err := svc.RunReportDefinition(ctx, def, &models.UserSession{ID: "u1"}, nil, 2000)
	require.NoError(t, err)
	require.NotNil(t, result)

	// Template was base-only against a__c.
	require.Len(t, result.Warnings, 1, "exactly one nested-unsupported warning")
	w := result.Warnings[0]
	assert.Equal(t, models.CRTWarningCode("crt_nested_unsupported"), w.Code,
		"must emit the closed-set code from shared/pkg/models/analytics.go")
	assert.Equal(t, WarnCRTNestedUnsupported, w.Code,
		"analytics-local alias must resolve to the same value")
}