package analytics

import (
	"context"
	"encoding/json"
	"fmt"
	"runtime/debug"
	"strings"

	"github.com/nexuscrm/backend/internal/features/metadata"
	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/shared/pkg/models"
)

// crtJoin mirrors the recursive JSON shape stored in _System_ReportType.Join.
// Salesforce serializes outerJoin as a string, so we keep the raw string form
// here rather than unmarshaling to bool; truthiness is checked in the walker.
type crtJoin struct {
	OuterJoin    string   `json:"outerJoin"`
	Relationship string   `json:"relationship"`
	Join         *crtJoin `json:"join,omitempty"`
}

// crtSectionsEntry mirrors the per-section shape from
// _System_ReportType.Sections — `table` uses the dot-notation path from the
// base object through any nested relationships.
type crtSectionColumn struct {
	Field string `json:"field"`
	Table string `json:"table"`
}

type crtSectionEntry struct {
	Columns []crtSectionColumn `json:"columns"`
}

// maxJoinDepth is the hard depth cap declared in design §4.4. Anything beyond
// this gets degraded to the base-object-only shape with a structured warning.
const maxJoinDepth = 3

// childAliases are the monotonic aliases assigned to joined tables in walk
// order. Base object takes the first letter of its lowercased name; children
// get t2, t3, t4. The t{N} pattern matches the existing hardcoded join
// templates in report_join_templates.go.
var childAliases = []string{"t2", "t3", "t4", "t5"}

// crtSynthesizer turns a *models.SystemReportType into a *JoinTemplate. It
// never errors — unrecoverable inputs produce (nil, warnings) so the caller
// can drop through to the single-table fallthrough. Warnings carry one of the
// closed-set CRTWarningCode values declared in crt_warnings.go.
type crtSynthesizer struct {
	metadata crtMetadataLookup
	logger   *logging.Logger
}

// newCRTSynthesizer wires the synthesizer against the real MetadataService.
// Unexported (HIGH-8): the only production caller is newCRTResolver in the
// same package, so an exported constructor returning an unexported type is
// a lint/usability mismatch.
func newCRTSynthesizer(md *metadata.MetadataService, logger *logging.Logger) *crtSynthesizer {
	return &crtSynthesizer{metadata: newCRTMetadataAdapter(md), logger: logger}
}

// newCRTSynthesizerWithLookup is the test-only constructor that accepts a
// narrow schema fake.
func newCRTSynthesizerWithLookup(md crtMetadataLookup, logger *logging.Logger) *crtSynthesizer {
	return &crtSynthesizer{metadata: md, logger: logger}
}

// Synthesize returns a JoinTemplate plus a non-nil slice of warnings. A nil
// template means the CRT is unusable and the caller should fall through to
// single-table handling.
func (s *crtSynthesizer) Synthesize(ctx context.Context, crt *models.SystemReportType) (tmpl *JoinTemplate, warnings []CRTWarning) {
	warnings = []CRTWarning{}
	defer func() {
		if r := recover(); r != nil {
			// CRITICAL-2: keep the panic value and stack trace server-side
			// only. The API-facing warning message must be a fixed string so
			// we never leak internal panic details (addresses, paths,
			// recovered values) into the JSON response.
			if s.logger != nil {
				s.logger.Error(fmt.Sprintf("crt synthesizer panic recovered: %v\n%s", r, debug.Stack()), nil)
			}
			warnings = append(warnings, CRTWarning{
				Code:    WarnCRTJoinParseFailed,
				Message: "internal synthesis error; see server logs",
			})
			tmpl = nil
		}
	}()

	if crt == nil || strings.TrimSpace(crt.BaseObject) == "" {
		return nil, append(warnings, CRTWarning{
			Code:    WarnCRTBaseObjectMissing,
			Message: "CRT has empty BaseObject",
		})
	}

	baseSchema := s.metadata.GetSchema(ctx, crt.BaseObject)
	if baseSchema == nil {
		return nil, append(warnings, CRTWarning{
			Code:         WarnCRTBaseObjectMissing,
			Message:      fmt.Sprintf("base object %q not found in metadata", crt.BaseObject),
			SourceObject: crt.FullName,
		})
	}

	primaryTable := strings.ToLower(baseSchema.APIName)
	primaryAlias := primaryAliasFor(baseSchema.APIName)
	tmpl = &JoinTemplate{
		PrimaryTable: primaryTable,
		PrimaryAlias: primaryAlias,
		Columns:      make(map[string]QualifiedColumn),
	}

	// aliasByPath tracks the Sections `table` dot-path -> SQL alias for
	// column resolution. The base entry uses the raw BaseObject name as the
	// dot-path prefix so Sections entries of the form `BaseObject.Rel.…`
	// line up cleanly with walker state.
	aliasByPath := map[string]string{
		crt.BaseObject:                   primaryAlias,
		strings.ToLower(crt.BaseObject):  primaryAlias,
		strings.ToLower(primaryTable):    primaryAlias,
	}

	// Parse Join (nil-tolerant). An empty or "null" payload means the CRT
	// is single-table; unparseable JSON degrades to single-table with a
	// warning so the column catalog can still flow through.
	joinRoot, joinWarn := parseCRTJoin(crt.Join)
	if joinWarn != nil {
		joinWarn.SourceObject = crt.FullName
		warnings = append(warnings, *joinWarn)
	}

	if joinRoot != nil {
		joinWarnings := s.walkJoins(ctx, crt, joinRoot, baseSchema, tmpl, aliasByPath)
		warnings = append(warnings, joinWarnings...)
	}

	// Parse and hydrate Sections columns.
	sectionWarnings := s.applySections(crt, tmpl, aliasByPath)
	warnings = append(warnings, sectionWarnings...)

	return tmpl, warnings
}

// walkJoins walks the crtJoin tree breadth-first, stopping at maxJoinDepth.
// Each step resolves the named relationship against the parent schema using
// the local findChildRelationship helper, then appends a JoinStep.
func (s *crtSynthesizer) walkJoins(ctx context.Context, crt *models.SystemReportType, root *crtJoin, baseSchema *models.ObjectMetadata, tmpl *JoinTemplate, aliasByPath map[string]string) []CRTWarning {
	var warnings []CRTWarning

	type stackEntry struct {
		node     *crtJoin
		parent   *models.ObjectMetadata
		pathKey  string // dot-path from base object to this node (inclusive of the node's relationship)
		depth    int
	}

	queue := []stackEntry{{node: root, parent: baseSchema, pathKey: crt.BaseObject, depth: 0}}

	for len(queue) > 0 {
		cur := queue[0]
		queue = queue[1:]
		if cur.node == nil {
			continue
		}

		if cur.depth >= maxJoinDepth {
			warnings = append(warnings, CRTWarning{
				Code:         WarnCRTJoinDepthExceeded,
				Message:      fmt.Sprintf("join depth exceeded hard cap %d", maxJoinDepth),
				SourceObject: crt.FullName,
			})
			continue
		}

		rel := strings.TrimSpace(cur.node.Relationship)
		if rel == "" {
			continue
		}

		childObj, childFK := findChildRelationship(ctx, s.metadata, cur.parent, rel)
		if childObj == nil {
			warnings = append(warnings, CRTWarning{
				Code:         WarnCRTAnchorObjectMissing,
				Message:      fmt.Sprintf("child relationship %q not found on %q", rel, cur.parent.APIName),
				SourceObject: crt.FullName,
			})
			continue
		}

		if len(tmpl.Joins) >= len(childAliases) {
			warnings = append(warnings, CRTWarning{
				Code:         WarnCRTJoinDepthExceeded,
				Message:      "join alias pool exhausted",
				SourceObject: crt.FullName,
			})
			continue
		}

		alias := childAliases[len(tmpl.Joins)]
		joinType := "INNER JOIN"
		if strings.EqualFold(strings.TrimSpace(cur.node.OuterJoin), "true") {
			joinType = "LEFT JOIN"
		}

		parentAlias := aliasByPath[cur.pathKey]
		if parentAlias == "" {
			// Fall back to the primary alias — parent path not registered.
			parentAlias = tmpl.PrimaryAlias
		}

		tmpl.Joins = append(tmpl.Joins, JoinStep{
			Table:    strings.ToLower(childObj.APIName),
			Alias:    alias,
			JoinType: joinType,
			OnLeft:   fmt.Sprintf("%s.%s", alias, childFK),
			OnRight:  fmt.Sprintf("%s.Id", parentAlias),
		})

		nextPath := cur.pathKey + "." + rel
		aliasByPath[nextPath] = alias
		aliasByPath[strings.ToLower(nextPath)] = alias
		aliasByPath[strings.ToLower(childObj.APIName)] = alias
		aliasByPath[childObj.APIName] = alias

		if cur.node.Join != nil {
			queue = append(queue, stackEntry{
				node:    cur.node.Join,
				parent:  childObj,
				pathKey: nextPath,
				depth:   cur.depth + 1,
			})
		}
	}

	return warnings
}

// applySections walks Sections and keys each column into tmpl.Columns by both
// the bare field name and the `<table>.<field>` dot-notation alias. Columns
// whose `table` does not correspond to a known alias are skipped with a
// once-per-table warning so the response still carries the resolvable columns.
func (s *crtSynthesizer) applySections(crt *models.SystemReportType, tmpl *JoinTemplate, aliasByPath map[string]string) []CRTWarning {
	var warnings []CRTWarning
	if strings.TrimSpace(crt.Sections) == "" {
		return warnings
	}
	var sections []crtSectionEntry
	if err := json.Unmarshal([]byte(crt.Sections), &sections); err != nil {
		return append(warnings, CRTWarning{
			Code:         WarnCRTSectionsParseFailed,
			Message:      fmt.Sprintf("sections JSON parse failed: %v", err),
			SourceObject: crt.FullName,
		})
	}

	// CRITICAL-1: Sections is JSON saved by any user who can save a Report
	// or deploy a ReportType, and its Field/Table values ultimately land in
	// backticked SQL identifiers. Validate every field + every dot-segment
	// of table against isSafeSQLIdent before emitting a QualifiedColumn.
	unknownTables := make(map[string]struct{})
	unsafeTables := make(map[string]struct{})
	for _, sec := range sections {
		for _, col := range sec.Columns {
			if col.Field == "" {
				continue
			}
			// Field may be a bare identifier ("Name") or a dotted path
			// ("Lead.Company") that Salesforce Sections uses for cross-
			// object projection on the same table row. Both are safe to
			// interpolate into backticks as long as every segment passes
			// isSafeSQLIdent — no backticks, no whitespace, no semicolons.
			if !isSafeDottedPath(col.Field) {
				// Warn at most once per (offending) table to avoid flooding
				// the response warnings.
				if _, seen := unsafeTables[col.Table]; !seen {
					unsafeTables[col.Table] = struct{}{}
					warnings = append(warnings, CRTWarning{
						Code:         WarnCRTColumnUnresolved,
						Message:      fmt.Sprintf("section column for table %q rejected (unsafe identifier)", col.Table),
						SourceObject: crt.FullName,
					})
				}
				continue
			}
			if !isSafeDottedPath(col.Table) {
				if _, seen := unsafeTables[col.Table]; !seen {
					unsafeTables[col.Table] = struct{}{}
					warnings = append(warnings, CRTWarning{
						Code:         WarnCRTColumnUnresolved,
						Message:      fmt.Sprintf("section table %q rejected (unsafe identifier)", col.Table),
						SourceObject: crt.FullName,
					})
				}
				continue
			}
			alias := lookupAlias(aliasByPath, col.Table)
			if alias == "" {
				if _, seen := unknownTables[col.Table]; !seen {
					unknownTables[col.Table] = struct{}{}
					warnings = append(warnings, CRTWarning{
						Code:         WarnCRTColumnUnresolved,
						Message:      fmt.Sprintf("section table %q not in join graph", col.Table),
						SourceObject: crt.FullName,
					})
				}
				continue
			}
			qc := QualifiedColumn{TableAlias: alias, Column: col.Field}
			tmpl.Columns[col.Field] = qc
			dotKey := col.Table + "." + col.Field
			tmpl.Columns[dotKey] = qc
		}
	}
	return warnings
}

// isSafeDottedPath accepts empty strings (the Sections base path can be
// empty for some synthetic shapes) and otherwise requires every dot-segment
// to pass isSafeSQLIdent.
func isSafeDottedPath(path string) bool {
	if path == "" {
		return true
	}
	for _, seg := range strings.Split(path, ".") {
		if !isSafeSQLIdent(seg) {
			return false
		}
	}
	return true
}

func lookupAlias(aliasByPath map[string]string, table string) string {
	if table == "" {
		return ""
	}
	if a, ok := aliasByPath[table]; ok {
		return a
	}
	if a, ok := aliasByPath[strings.ToLower(table)]; ok {
		return a
	}
	// Fall back: try the last dot-segment (Sections frequently uses
	// `Base.Rel.SubRel` and the walker may have only registered by object
	// API name).
	if idx := strings.LastIndex(table, "."); idx != -1 {
		tail := table[idx+1:]
		if a, ok := aliasByPath[tail]; ok {
			return a
		}
		if a, ok := aliasByPath[strings.ToLower(tail)]; ok {
			return a
		}
	}
	return ""
}

// parseCRTJoin unmarshals the Join JSON. Returns (nil, nil) for an empty or
// literal-null payload, (nil, warning) for unparseable input, and (root, nil)
// on success.
func parseCRTJoin(raw string) (*crtJoin, *CRTWarning) {
	s := strings.TrimSpace(raw)
	if s == "" || s == "null" || s == "{}" {
		return nil, nil
	}
	var root crtJoin
	if err := json.Unmarshal([]byte(s), &root); err != nil {
		return nil, &CRTWarning{
			Code:    WarnCRTJoinParseFailed,
			Message: fmt.Sprintf("join JSON parse failed: %v", err),
		}
	}
	if root.Relationship == "" && root.Join == nil {
		return nil, nil
	}
	return &root, nil
}

// findChildRelationship locates the child ObjectMetadata + FK column for a
// given relationship name on the parent schema. It is an inline helper kept
// here rather than being added to metadata_service.go — the underlying
// MetadataService.GetChildRelationships lists children but does not expose a
// "resolve relationship name" call, and growing that service is out of scope
// for Wave 2. The helper walks each child's fields looking for a FK whose
// RelationshipName (or APIName-derived `__r` name) matches, with case-
// insensitive fallbacks for the managed-package namespace-prefixed variants.
func findChildRelationship(ctx context.Context, md crtMetadataLookup, parent *models.ObjectMetadata, relationshipName string) (*models.ObjectMetadata, string) {
	if parent == nil || relationshipName == "" {
		return nil, ""
	}
	target := strings.ToLower(strings.TrimSpace(relationshipName))

	// First probe: if the parent schema carries a childRelationshipsMap via
	// metadata lookup, walk the candidates and match by relationshipName on
	// a FK field that points back at parent.
	candidates := collectChildCandidates(ctx, md, parent)

	for _, child := range candidates {
		for i := range child.Fields {
			f := &child.Fields[i]
			if !fieldPointsAt(f, parent.APIName) {
				continue
			}
			if matchRelationshipName(f, target) {
				return child, f.APIName
			}
		}
	}

	// Second probe: if no direct match, pick any child whose FK points at
	// the parent and whose inferred relationship name matches the target
	// after stripping the __r suffix. This handles managed-package cases
	// where the relationship name is synthesized from the API name.
	for _, child := range candidates {
		for i := range child.Fields {
			f := &child.Fields[i]
			if !fieldPointsAt(f, parent.APIName) {
				continue
			}
			inferred := inferRelationshipName(f, child.APIName)
			if strings.EqualFold(inferred, relationshipName) {
				return child, f.APIName
			}
		}
	}

	return nil, ""
}

func collectChildCandidates(ctx context.Context, md crtMetadataLookup, parent *models.ObjectMetadata) []*models.ObjectMetadata {
	// BLOCKER-3 fix: the primary discovery path delegates to a crtChildLister
	// (implemented in production by crtMetadataAdapter, which calls
	// MetadataService.GetChildRelationships). Previously this function held
	// a dead `for f := range parent.Fields { _ = f }` loop that computed
	// nothing, so every stored-CRT report that reached this path silently
	// degraded to base-object-only — the 306 stored CRTs this PR was
	// supposed to fix never got their joins installed.
	seen := map[string]struct{}{}
	var out []*models.ObjectMetadata

	if cl, ok := md.(crtChildLister); ok {
		for _, child := range cl.ListChildren(ctx, parent.APIName) {
			if child == nil {
				continue
			}
			key := strings.ToLower(child.APIName)
			if _, dup := seen[key]; dup {
				continue
			}
			seen[key] = struct{}{}
			out = append(out, child)
		}
	}
	return out
}

func fieldPointsAt(f *models.FieldMetadata, parentAPIName string) bool {
	if f == nil || len(f.ReferenceTo) == 0 {
		return false
	}
	for _, ref := range f.ReferenceTo {
		if strings.EqualFold(ref, parentAPIName) {
			return true
		}
	}
	return false
}

func matchRelationshipName(f *models.FieldMetadata, target string) bool {
	if f.RelationshipName != nil && strings.EqualFold(*f.RelationshipName, target) {
		return true
	}
	return false
}

func inferRelationshipName(_ *models.FieldMetadata, childAPIName string) string {
	// CRT Join.relationship on the parent side is always the forward,
	// pluralized child name (e.g. Campaign → "CampaignMembers"). The
	// FK field's own RelationshipName points back at the parent ("Campaign"),
	// so we deliberately ignore it here. For custom objects SF also accepts
	// the "__r" form of the child API name.
	if strings.HasSuffix(childAPIName, "__c") {
		return strings.TrimSuffix(childAPIName, "__c") + "__r"
	}
	return childAPIName + "s"
}

func primaryAliasFor(apiName string) string {
	trimmed := strings.TrimSpace(apiName)
	if trimmed == "" {
		return "t1"
	}
	return strings.ToLower(string(trimmed[0]))
}