package analytics

import (
	"context"
	"fmt"
	"log"
	"strings"
	"sync"

	"github.com/nexuscrm/kernel/pkg/schemautil"
	"github.com/nexuscrm/shared/pkg/models"
)

// ResolveReportColumn turns a single entry from Report.Columns into
// (selectExpr, resultAlias, ok). ok==false means the column could not be
// resolved — the caller should emit NULL in its place. This function never
// panics and is pure with respect to its inputs (aside from DEBUG logging
// of unknown columns).
//
// Inputs may be nil:
//   - tmpl == nil: dot-notation and template-qualified paths fail-soft.
//   - md   == nil: FK traversal (three-segment cross-join) is skipped.
//
// SECURITY: every string that becomes part of a SQL identifier is validated
// against isSafeSQLIdent before it is emitted into a selectExpr or marker
// payload. Callers that save CRT Sections or Report.Columns are NOT trusted
// — an insider that can save a Report must not be able to inject SQL via
// the column path. Any failure path here returns ok=false (fail-soft).
func ResolveReportColumn(ctx context.Context, col string, tmpl *JoinTemplate, md MetadataSource) (selectExpr string, resultAlias string, ok bool) {
	return resolveReportColumnImpl(ctx, col, tmpl, newCRTMetadataAdapter(md))
}

func resolveReportColumnImpl(ctx context.Context, col string, tmpl *JoinTemplate, md crtMetadataLookup) (selectExpr string, resultAlias string, ok bool) {
	col = strings.TrimSpace(col)
	if col == "" {
		return "", "", false
	}

	// 1. Flat column (no dot). This also covers SCREAMING_SNAKE.
	if !strings.Contains(col, ".") {
		if !isSafeSQLIdent(col) {
			logUnknownColumn(col, "flat-unsafe-ident")
			return "", "", false
		}
		if tmpl != nil {
			if qc, found := tmpl.Columns[col]; found {
				return qualifiedColumnToSelect(qc), deriveAlias(col), true
			}
			return fmt.Sprintf("`%s`.`%s`", tmpl.PrimaryAlias, col), deriveAlias(col), true
		}
		logUnknownColumn(col, "flat-no-template")
		return "", "", false
	}

	// 2. Dot-notation path.
	segments := strings.Split(col, ".")
	// Guard against things like "." or ".Foo" — empty segments.
	for _, s := range segments {
		if s == "" {
			logUnknownColumn(col, "empty-segment")
			return "", "", false
		}
	}

	if tmpl == nil {
		logUnknownColumn(col, "dot-no-template")
		return "", "", false
	}

	// Direct dot-key lookup in template columns (e.g. "Campaign.Name" keyed as-is).
	// The lookup value is a QualifiedColumn built by the synthesizer using
	// already-validated inputs; no extra check needed here.
	if qc, found := tmpl.Columns[col]; found {
		return qualifiedColumnToSelect(qc), deriveAlias(col), true
	}

	switch len(segments) {
	case 2:
		return resolveTwoSegment(segments[0], segments[1], tmpl, col)
	case 3:
		return resolveThreeSegment(ctx, segments[0], segments[1], segments[2], tmpl, md, col)
	default:
		// 4+ segment paths are out of scope per §2 non-goal #3.
		logUnknownColumn(col, "too-many-segments")
		return "", "", false
	}
}

const (
	// lookupJoinMarker is emitted by resolveThreeSegment when a 3-segment
	// path resolves to a parent/lookup traversal that needs a runtime
	// LEFT JOIN. Payload shape:
	//   __LOOKUP_JOIN__:<originAlias>|<fkCol>|<targetTable>|<targetField>
	// ExpandLookupJoinMarkers (in crt_polymorphic_joins.go) installs the
	// join with a deterministic alias and rewrites the marker to the real
	// `<alias>.<field>` form.
	lookupJoinMarker = "__LOOKUP_JOIN__:"
)

func resolveTwoSegment(prefix, field string, tmpl *JoinTemplate, original string) (string, string, bool) {
	alias, ok := findTableAlias(tmpl, prefix)
	if !ok {
		logUnknownColumn(original, "unknown-table-prefix")
		return "", "", false
	}
	if !isSafeSQLIdent(field) {
		logUnknownColumn(original, "two-seg-unsafe-field")
		return "", "", false
	}
	return fmt.Sprintf("`%s`.`%s`", alias, field), deriveAlias(original), true
}

func resolveThreeSegment(ctx context.Context, obj, rel, field string, tmpl *JoinTemplate, md crtMetadataLookup, original string) (string, string, bool) {
	alias, ok := findTableAlias(tmpl, obj)
	if !ok {
		logUnknownColumn(original, "three-seg-unknown-root")
		return "", "", false
	}
	if md == nil {
		logUnknownColumn(original, "three-seg-no-metadata")
		return "", "", false
	}
	// Walk the FK: look up `rel` on obj's schema as a field name first, then
	// as a relationship name (e.g. "Parent" → ParentId with
	// RelationshipName=="Parent"). Confirm it resolves to a reference and the
	// target schema actually has `field`. Fail-soft on any mismatch.
	schema := md.GetSchema(ctx, obj)
	if schema == nil {
		logUnknownColumn(original, "three-seg-root-schema-missing")
		return "", "", false
	}
	fkField := schemautil.FindField(schema, rel)
	if fkField == nil || len(fkField.ReferenceTo) == 0 {
		fkField = findLookupByRelationshipName(schema, rel)
	}
	if fkField == nil || len(fkField.ReferenceTo) == 0 {
		logUnknownColumn(original, "three-seg-not-a-reference")
		return "", "", false
	}
	targetObj := fkField.ReferenceTo[0]
	targetSchema := md.GetSchema(ctx, targetObj)
	if targetSchema == nil {
		logUnknownColumn(original, "three-seg-target-schema-missing")
		return "", "", false
	}
	if schemautil.FindField(targetSchema, field) == nil {
		logUnknownColumn(original, "three-seg-target-field-missing")
		return "", "", false
	}
	// Validate every string that will reach SQL interpolation. All three come
	// from metadata but defense-in-depth is cheap.
	if !isSafeSQLIdent(fkField.APIName) || !isSafeSQLIdent(field) || !isSafeSQLIdent(targetObj) {
		logUnknownColumn(original, "three-seg-unsafe-ident")
		return "", "", false
	}
	// Emit a __LOOKUP_JOIN__ marker so ExpandLookupJoinMarkers (called from
	// decorate) can install the actual join and rewrite the expression to a
	// real qualified column. Marker payload:
	//   originAlias : alias of the root object in the current template
	//   fkCol       : FK column name on originAlias
	//   targetTable : physical table to join
	//   field       : column on the target table
	marker := fmt.Sprintf("%s%s|%s|%s|%s", lookupJoinMarker, alias, fkField.APIName, strings.ToLower(targetObj), field)
	return marker, deriveAlias(original), true
}

// findLookupByRelationshipName returns the first field on schema whose
// RelationshipName matches relName (case-insensitive). This lets dot-notation
// paths like "Campaign.Parent.Name" locate ParentId via SF relationship-name
// conventions rather than raw field names.
func findLookupByRelationshipName(schema *models.ObjectMetadata, relName string) *models.FieldMetadata {
	if schema == nil || relName == "" {
		return nil
	}
	for i := range schema.Fields {
		f := &schema.Fields[i]
		if f.RelationshipName == nil || *f.RelationshipName == "" {
			continue
		}
		if strings.EqualFold(*f.RelationshipName, relName) && len(f.ReferenceTo) > 0 {
			return f
		}
	}
	return nil
}

func findTableAlias(tmpl *JoinTemplate, name string) (string, bool) {
	if tmpl == nil {
		return "", false
	}
	if strings.EqualFold(name, tmpl.PrimaryTable) || strings.EqualFold(name, tmpl.PrimaryAlias) {
		return tmpl.PrimaryAlias, true
	}
	for _, j := range tmpl.Joins {
		if strings.EqualFold(name, j.Table) || strings.EqualFold(name, j.Alias) {
			return j.Alias, true
		}
	}
	return "", false
}

func qualifiedColumnToSelect(qc QualifiedColumn) string {
	if qc.SelectExpr != "" {
		return qc.SelectExpr
	}
	return fmt.Sprintf("`%s`.`%s`", qc.TableAlias, qc.Column)
}

// deriveAlias produces a stable result-alias string for an incoming column
// path. The goal is to round-trip the original path through the response so
// the frontend can key back into it.
func deriveAlias(col string) string {
	return strings.ReplaceAll(col, ".", "_")
}

// unknownColumnSink is the DEBUG-channel fallback for §10 decision #2
// ("minimal seed + log unknowns"). It is a package-level function that
// tests may swap through setUnknownColumnSinkForTest (HIGH-7 race fix);
// production callers always go through the default stdlib-log sink. The
// mutex protects concurrent reads/writes so a property test swapping the
// sink cannot race a production resolve on another goroutine.
var (
	unknownColumnSinkMu sync.RWMutex
	unknownColumnSink   = defaultUnknownColumnSink
)

func defaultUnknownColumnSink(col, reason string) {
	// Keep the log one-line + trimmed — callers run this per report column
	// and we do not want to flood stderr in hot paths.
	if len(col) > 120 {
		col = col[:120] + "…"
	}
	log.Printf("crt_column_path: unresolved col=%q reason=%s", col, reason)
}

func logUnknownColumn(col, reason string) {
	unknownColumnSinkMu.RLock()
	sink := unknownColumnSink
	unknownColumnSinkMu.RUnlock()
	if sink != nil {
		sink(col, reason)
	}
}