package rest

import (
	"encoding/json"
	"fmt"
	"net/http"
	"sort"
	"strconv"
	"strings"

	"github.com/gin-gonic/gin"
	"github.com/nexuscrm/backend/internal/application/services"
	"github.com/nexuscrm/backend/internal/features/analytics"
	"github.com/nexuscrm/backend/internal/features/permissions"
	"github.com/nexuscrm/backend/pkg/apierr"
	"github.com/nexuscrm/backend/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"golang.org/x/text/cases"
	"golang.org/x/text/language"
)

const (
	reportTableName = constants.TableReport
	folderTable     = constants.TableReportFolder
)

// AnalyticsHandler handles analytics and reporting requests
type AnalyticsHandler struct {
	reportSvc    *analytics.ReportService
	dashboardHdl *DashboardHandler
	svcMgr       *services.ServiceManager
}

// NewAnalyticsHandler creates a new handler
func NewAnalyticsHandler(reportSvc *analytics.ReportService, dashboardHdl *DashboardHandler, svcMgr *services.ServiceManager) *AnalyticsHandler {
	return &AnalyticsHandler{
		reportSvc:    reportSvc,
		dashboardHdl: dashboardHdl,
		svcMgr:       svcMgr,
	}
}

// getSchema returns the ObjectMetadata for the given object, or nil if the
// metadata service is unavailable (e.g. in tests).
func (h *AnalyticsHandler) getSchema(c *gin.Context, objectName string) *models.ObjectMetadata {
	if h.svcMgr != nil && h.svcMgr.Metadata != nil && objectName != "" {
		return h.svcMgr.Metadata.GetSchema(c.Request.Context(), objectName)
	}
	return nil
}

// RegisterRoutes registers the analytics routes
func (h *AnalyticsHandler) RegisterRoutes(group *gin.RouterGroup) {
	group.GET("/reports", h.ListReports)
	group.POST("/reports", h.CreateReport)
	group.POST("/report-execution", h.RunReportExecution)
	group.POST("/reports/execute", h.ExecuteReportDefinition)
	group.GET("/reports/:reportId", h.RunReport)
	group.POST("/reports/:reportId", h.RunReport) // Support POST for runtime filters in future
	group.PATCH("/reports/:reportId", h.UpdateReport)
	group.DELETE("/reports/:reportId", h.DeleteReport)
	group.GET("/reports/:reportId/describe", h.DescribeReport)
}

// RunReport executes a report
func (h *AnalyticsHandler) RunReport(c *gin.Context) {
	user := MustGetUser(c)

	reportID, ok := RequireParam(c, "reportId")
	if !ok {
		return
	}

	result, err := h.reportSvc.RunReport(c.Request.Context(), reportID, user, nil)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	schema := h.getSchema(c, result.ReportMetadata.SourceObject)
	sfResult := toSalesforceReportResult(result, schema)
	c.JSON(http.StatusOK, sfResult)
}

type reportExecutionRequest struct {
	ReportID       string            `json:"reportId,omitempty"`
	ReportMetadata *sfReportMetadata `json:"reportMetadata,omitempty"`
	Limit          int               `json:"limit,omitempty"`
}

// RunReportExecution executes either a saved report or an ad-hoc report
// definition override using the stable POST endpoint consumed by the frontend.
func (h *AnalyticsHandler) RunReportExecution(c *gin.Context) {
	user := MustGetUser(c)

	var req reportExecutionRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeInvalidField, err.Error()))
		return
	}
	if req.ReportID == "" && req.ReportMetadata == nil {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, "reportId or reportMetadata is required"))
		return
	}

	var def *models.ReportDefinition
	var err error
	if req.ReportID != "" {
		def, err = h.reportSvc.GetReportMetadata(c.Request.Context(), req.ReportID, user)
		if err != nil {
			RespondAppError(c, err)
			return
		}
	} else {
		def = &models.ReportDefinition{}
	}

	if req.ReportMetadata != nil {
		mergeExecutionMetadata(def, *req.ReportMetadata)
	}
	if def.SourceObject == "" {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, "reportMetadata.sourceObject is required"))
		return
	}
	if def.ReportFormat == "" {
		if len(def.Grouping) > 0 {
			def.ReportFormat = "SUMMARY"
		} else {
			def.ReportFormat = "TABULAR"
		}
	}
	if def.ReportType == "" && def.SourceObject != "" {
		def.ReportType = def.SourceObject + "List"
	}

	result, err := h.reportSvc.RunReportDefinition(c.Request.Context(), def, user, nil, req.Limit)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	schema := h.getSchema(c, result.ReportMetadata.SourceObject)
	c.JSON(http.StatusOK, toSalesforceReportResult(result, schema))
}

// DescribeReport returns report metadata in Salesforce wire format
func (h *AnalyticsHandler) DescribeReport(c *gin.Context) {
	user := MustGetUser(c)

	reportID, ok := RequireParam(c, "reportId")
	if !ok {
		return
	}

	def, err := h.reportSvc.GetReportMetadata(c.Request.Context(), reportID, user)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	// Salesforce wire format: wrap in reportMetadata envelope
	c.JSON(http.StatusOK, gin.H{
		"reportMetadata": defToSFMetadata(def),
	})
}

// defToSFMetadata converts internal ReportDefinition → Salesforce reportMetadata wire format
func defToSFMetadata(def *models.ReportDefinition) gin.H {
	// SF-imported reports store format ("Summary"/"Tabular") in ReportType.
	// Use SourceObject-derived type when ReportType is a format name.
	rtType := def.ReportType
	isFormatName := strings.EqualFold(rtType, "Summary") || strings.EqualFold(rtType, "Tabular") || strings.EqualFold(rtType, "Matrix")
	if isFormatName && def.SourceObject != "" {
		rtType = def.SourceObject + "List"
	}
	meta := gin.H{
		"id":           def.ID,
		"name":         def.Name,
		"reportFormat": def.ReportFormat,
		"reportType":   gin.H{"type": rtType, "label": def.SourceObject},
	}
	if def.Scope != "" {
		meta["scope"] = def.Scope
	}
	if def.SourceObject != "" {
		// Return the resolved object name so the frontend can query the correct table.
		// Strip SF report type suffixes: "CampaignWithCampaignMembers" → "Campaign"
		resolved := def.SourceObject
		if i := strings.Index(resolved, "With"); i > 0 {
			resolved = resolved[:i]
		}
		for _, suffix := range []string{"FieldAuditHistory", "AuditHistory", "Activity", "List"} {
			if strings.HasSuffix(resolved, suffix) {
				resolved = strings.TrimSuffix(resolved, suffix)
				break
			}
		}
		meta["sourceObject"] = resolved
	}
	if len(def.Columns) > 0 {
		meta["detailColumns"] = def.Columns
	}
	if len(def.Filters) > 0 {
		meta["reportFilters"] = def.Filters
	}
	if len(def.Grouping) > 0 {
		groups := make([]gin.H, len(def.Grouping))
		for i, g := range def.Grouping {
			groups[i] = gin.H{"name": g, "sortOrder": "Asc", "dateGranularity": "NONE"}
		}
		meta["groupingsDown"] = groups
	}
	if def.StandardDateFilter != nil {
		meta["standardDateFilter"] = def.StandardDateFilter
	}
	return meta
}

// ---------------------------------------------------------------------------
// toSalesforceReportResult — internal ReportResult → Salesforce Analytics API
// ---------------------------------------------------------------------------

func toSalesforceReportResult(result *models.ReportResult, schema *models.ObjectMetadata) gin.H {
	def := &result.ReportMetadata

	reportFormat := strings.ToUpper(def.ReportFormat)
	if reportFormat == "" {
		if strings.EqualFold(def.ReportType, "summary") {
			reportFormat = "SUMMARY"
		} else {
			reportFormat = "TABULAR"
		}
	}

	isSummaryExec := reportFormat == "SUMMARY" && len(def.Grouping) == 1 &&
		looksLikeAggregatedRows(result.Rows)

	isMultiGroupSummary := reportFormat == "SUMMARY" && len(def.Grouping) > 0 &&
		!isSummaryExec

	meta := defToSFMetadata(def)
	meta["reportFormat"] = reportFormat

	aggKeys := buildAggregateKeys(def.Aggregates)
	meta["aggregates"] = aggKeys

	cols := effectiveColumns(def, result.Rows, isSummaryExec)
	if len(cols) > 0 {
		meta["detailColumns"] = cols
	}

	extended := buildExtendedMetadata(cols, def.Grouping, def.Aggregates, schema)
	if len(result.Warnings) > 0 {
		// Copy structured CRT degradation warnings into the
		// reportExtendedMetadata envelope per design §4.11. The frontend
		// ignores unknown fields on this object, so this is forward-
		// compatible.
		extended["warnings"] = result.Warnings
	}
	meta["reportExtendedMetadata"] = extended

	// totalCount is the unpaginated row count — falls back to len(rows) when
	// the execution layer didn't compute it (non-CRT path).
	totalCount := result.TotalCount
	if totalCount == 0 {
		totalCount = len(result.Rows)
	}

	if isSummaryExec {
		return buildSummaryResponse(meta, result.Rows, def, totalCount)
	}
	if isMultiGroupSummary {
		return buildMultiGroupSummaryResponse(meta, result.Rows, def, cols, totalCount, result.AggregateDedupColumn, schema)
	}
	return buildTabularResponse(meta, result.Rows, cols, totalCount, schema)
}

func buildTabularResponse(meta gin.H, rows []models.SObject, cols []string, totalCount int, schema *models.ObjectMetadata) gin.H {
	sfRows := make([]gin.H, 0, len(rows))
	for _, row := range rows {
		cells := make([]gin.H, len(cols))
		for j, col := range cols {
			val := resolveColumnValue(row, col, schema)
			cells[j] = gin.H{"label": formatCellValue(val), "value": val}
		}
		sfRows = append(sfRows, gin.H{"dataCells": cells})
	}

	return gin.H{
		"reportMetadata": meta,
		"factMap": gin.H{
			"T!T": gin.H{
				"rows":       sfRows,
				"aggregates": []gin.H{{"label": fmt.Sprintf("%d", totalCount), "value": totalCount}},
			},
		},
		"groupingsDown": gin.H{"groupings": []gin.H{}},
		"hasDetailRows": len(rows) > 0,
		"allData":       len(rows) >= totalCount,
	}
}

func buildSummaryResponse(meta gin.H, rows []models.SObject, def *models.ReportDefinition, totalCount int) gin.H {
	groupings := make([]gin.H, 0, len(rows))
	factMap := make(gin.H, len(rows)+1)
	var grandTotal float64

	for i, row := range rows {
		name := formatCellValue(row["name"])
		val, _ := utils.ToFloat64(row["val"])
		key := fmt.Sprintf("%d", i)

		groupings = append(groupings, gin.H{"label": name, "value": name, "key": key})
		factMap[key+"!T"] = gin.H{
			"aggregates": []gin.H{{"label": formatNumber(val), "value": val}},
			"rows":       []gin.H{},
		}
		grandTotal += val
	}

	grandAggs := []gin.H{{"label": fmt.Sprintf("%d", totalCount), "value": totalCount}}
	if len(def.Aggregates) > 0 {
		grandAggs = append(grandAggs, gin.H{"label": formatNumber(grandTotal), "value": grandTotal})
	}
	factMap["T!T"] = gin.H{"aggregates": grandAggs, "rows": []gin.H{}}

	return gin.H{
		"reportMetadata": meta,
		"factMap":        factMap,
		"groupingsDown":  gin.H{"groupings": groupings},
		"hasDetailRows":  false,
	}
}

// TODO(bandaid): All grouping and aggregation is done in memory over the full
// result set. For large reports this is O(n) memory + CPU. Should push grouping
// and aggregation down to SQL (GROUP BY with aggregate functions) when possible.
func buildMultiGroupSummaryResponse(meta gin.H, rows []models.SObject, def *models.ReportDefinition, cols []string, totalCount int, dedupByAgg map[string]string, schema *models.ObjectMetadata) gin.H {
	factMap := make(gin.H)
	grandAggs := computeAggregatesWithDedup(rows, def.Aggregates, dedupByAgg, schema)
	// Override only the RowCount-typed aggregate with the true total — SUM
	// / AVG / MIN / MAX aggregates still reflect the LIMIT-capped in-memory
	// rows (TODO: push them into SQL so they match SF on large result sets).
	// Earlier versions blindly overwrote position 0 which clobbered SUM
	// aggregates when RowCount was not first in the report definition.
	overrideRowCountAggregate(grandAggs, def.Aggregates, totalCount)
	factMap["T!T"] = gin.H{"aggregates": grandAggs, "rows": []gin.H{}}
	groupings := buildSummaryGroupingLevel(rows, def, cols, factMap, 0, "", schema)

	return gin.H{
		"reportMetadata": meta,
		"factMap":        factMap,
		"groupingsDown":  gin.H{"groupings": groupings},
		"hasDetailRows":  len(rows) > 0,
	}
}

type summaryGroupBucket struct {
	label string
	rows  []models.SObject
}

func buildSummaryGroupingLevel(rows []models.SObject, def *models.ReportDefinition, cols []string, factMap gin.H, level int, keyPrefix string, schema *models.ObjectMetadata) []gin.H {
	if level >= len(def.Grouping) {
		return []gin.H{}
	}

	groupField := resolveGroupingFieldName(def.Grouping[level], schema)
	bucketMap := make(map[string]*summaryGroupBucket)
	bucketOrder := make([]string, 0)

	for _, row := range rows {
		label := formatCellValue(resolveColumnValue(row, groupField, schema))
		if label == "" {
			label = "(blank)"
		}
		bucket, ok := bucketMap[label]
		if !ok {
			bucket = &summaryGroupBucket{label: label}
			bucketMap[label] = bucket
			bucketOrder = append(bucketOrder, label)
		}
		bucket.rows = append(bucket.rows, row)
	}

	groupings := make([]gin.H, 0, len(bucketOrder))
	isLeafLevel := level == len(def.Grouping)-1

	for index, label := range bucketOrder {
		bucket := bucketMap[label]
		key := fmt.Sprintf("%d", index)
		if keyPrefix != "" {
			key = keyPrefix + "_" + key
		}

		factEntry := gin.H{
			"aggregates": computeAggregates(bucket.rows, def.Aggregates, schema),
			"rows":       []gin.H{},
		}
		if isLeafLevel {
			factEntry["rows"] = buildDetailRows(bucket.rows, cols, schema)
		}
		// Ensure factMap is properly populated for multi-level grouped report subtotals
		factMapKey := key + "!T"
		factMap[factMapKey] = factEntry

		grouping := gin.H{
			"key":   key,
			"label": fmt.Sprintf("%s (%d)", label, len(bucket.rows)),
			"value": label,
		}
		if !isLeafLevel {
			grouping["groupings"] = buildSummaryGroupingLevel(bucket.rows, def, cols, factMap, level+1, key, schema)
		}
		groupings = append(groupings, grouping)
	}

	return groupings
}

func buildDetailRows(rows []models.SObject, cols []string, schema *models.ObjectMetadata) []gin.H {
	sfRows := make([]gin.H, 0, len(rows))
	for _, row := range rows {
		cells := make([]gin.H, len(cols))
		for j, col := range cols {
			val := resolveColumnValue(row, col, schema)
			cells[j] = gin.H{"label": formatCellValue(val), "value": val}
		}
		sfRows = append(sfRows, gin.H{"dataCells": cells})
	}
	return sfRows
}

// TODO(bandaid): Always computes SUM regardless of agg.Type. AVG, MAX, and MIN
// aggregate types are silently treated as SUM. Needs a switch on agg.Type to
// compute the correct aggregate function.
// overrideRowCountAggregate mutates aggregates in place, replacing whichever
// slot corresponds to the RowCount aggregate with the true unpaginated
// count. computeAggregates prepends RowCount if aggDefs doesn't carry one,
// so the slot index depends on aggDefs content — we mirror the same logic
// here so the two paths stay in sync.
func overrideRowCountAggregate(aggregates []gin.H, aggDefs []models.ReportAggregate, totalCount int) {
	if len(aggregates) == 0 {
		return
	}
	rowCountValue := gin.H{"label": fmt.Sprintf("%d", totalCount), "value": totalCount}

	hasRowCount := false
	rowCountIdx := -1
	for i, a := range aggDefs {
		if a.Field == "" && strings.EqualFold(a.Type, "RowCount") {
			hasRowCount = true
			rowCountIdx = i
			break
		}
	}
	if !hasRowCount {
		// computeAggregates prepended RowCount at position 0.
		aggregates[0] = rowCountValue
		return
	}
	// aggDefs had RowCount, so computeAggregates kept the original order.
	// Shift by the prepend offset (0 when hasRowCount is true).
	if rowCountIdx >= 0 && rowCountIdx < len(aggregates) {
		aggregates[rowCountIdx] = rowCountValue
	}
}

func computeAggregates(rows []models.SObject, aggDefs []models.ReportAggregate, schema *models.ObjectMetadata) []gin.H {
	return computeAggregatesWithDedup(rows, aggDefs, nil, schema)
}

// computeAggregatesWithDedup is the general form. dedupByAgg, when non-nil,
// maps aggregate field name → SObject row key to dedupe by before summing.
// This matches Salesforce semantics where primary-entity SUM/AVG/MIN/MAX
// ignores JOIN fan-out: a report over Opportunity LEFT JOIN Lead that
// returns 116 joined rows (69 distinct opps × ~1.7 leads each) sums
// Opportunity.Amount over the 69 distinct opportunities, not the 116
// inflated rows. Fields that aren't in dedupByAgg (joined-entity fields,
// counts) are summed raw.
func computeAggregatesWithDedup(rows []models.SObject, aggDefs []models.ReportAggregate, dedupByAgg map[string]string, schema *models.ObjectMetadata) []gin.H {
	// Must produce values in EXACTLY the same order as buildAggregateKeys():
	// - If RowCount is already in aggDefs → preserve original order
	// - If RowCount is NOT in aggDefs → prepend it, then original order
	hasRowCount := false
	for _, a := range aggDefs {
		if a.Field == "" && strings.EqualFold(a.Type, "RowCount") {
			hasRowCount = true
			break
		}
	}

	ordered := make([]models.ReportAggregate, 0, len(aggDefs)+1)
	if !hasRowCount {
		ordered = append(ordered, models.ReportAggregate{Field: "", Type: "RowCount"})
	}
	ordered = append(ordered, aggDefs...)

	result := make([]gin.H, 0, len(ordered))
	for _, agg := range ordered {
		if agg.Field == "" && strings.EqualFold(agg.Type, "RowCount") {
			result = append(result, gin.H{
				"label": fmt.Sprintf("%d", len(rows)),
				"value": len(rows),
			})
			continue
		}

		field := resolveAggregateField(agg.Field, schema)
		dedupKey := ""
		if dedupByAgg != nil {
			if k, ok := dedupByAgg[agg.Field]; ok {
				dedupKey = k
			}
		}
		var total float64
		if dedupKey != "" {
			seen := make(map[string]struct{}, len(rows))
			for _, row := range rows {
				id, _ := row[dedupKey].(string)
				if id == "" {
					// Row is missing the dedup id (shouldn't happen for
					// joined primary rows, but be defensive). Fall back to
					// summing this row uniquely.
					f, _ := utils.ToFloat64(resolveColumnValue(row, field, schema))
					total += f
					continue
				}
				if _, dup := seen[id]; dup {
					continue
				}
				seen[id] = struct{}{}
				f, _ := utils.ToFloat64(resolveColumnValue(row, field, schema))
				total += f
			}
		} else {
			for _, row := range rows {
				val := resolveColumnValue(row, field, schema)
				f, _ := utils.ToFloat64(val)
				total += f
			}
		}

		result = append(result, gin.H{
			"label": formatNumber(total),
			"value": total,
		})
	}
	return result
}

// TODO(bandaid): Strips cross-object prefixes with a heuristic dot-split.
// Doesn't validate the field exists on the source object. For true cross-object
// aggregates (e.g. SUM of a parent field), this silently resolves to a
// non-existent column and the aggregate evaluates to 0.
func resolveAggregateField(field string, schema *models.ObjectMetadata) string {
	field = strings.TrimSuffix(field, ".CONVERT")
	if idx := strings.Index(field, "."); idx >= 0 {
		field = field[idx+1:]
	}
	if alias := resolveColumnAlias(field, schema); alias != "" {
		return alias
	}
	pascal := utils.SnakeToPascal(field)
	if pascal != field {
		return pascal
	}
	return field
}

func resolveGroupingFieldName(field string, schema *models.ObjectMetadata) string {
	return resolveAggregateField(field, schema)
}

// --- Salesforce response helpers ---

func buildAggregateKeys(aggs []models.ReportAggregate) []string {
	keys := make([]string, 0, len(aggs)+1)
	hasRowCount := false
	for _, a := range aggs {
		k := sfAggregateKey(a)
		keys = append(keys, k)
		if k == "RowCount" {
			hasRowCount = true
		}
	}
	if !hasRowCount {
		keys = append([]string{"RowCount"}, keys...)
	}
	return keys
}

func sfAggregateKey(agg models.ReportAggregate) string {
	if agg.Field == "" {
		return agg.Type
	}
	switch strings.ToUpper(agg.Type) {
	case "SUM":
		return "s!" + agg.Field
	case "AVG":
		return "a!" + agg.Field
	case "MAX":
		return "mx!" + agg.Field
	case "MIN":
		return "mn!" + agg.Field
	default:
		return agg.Type
	}
}

func buildExtendedMetadata(cols []string, grouping []string, aggs []models.ReportAggregate, schema *models.ObjectMetadata) gin.H {
	dci := make(gin.H, len(cols))
	for _, col := range cols {
		dci[col] = gin.H{"label": resolveFieldLabel(col, schema), "dataType": resolveFieldDataType(col, schema)}
	}

	gci := make(gin.H, len(grouping))
	for _, group := range grouping {
		gci[group] = gin.H{"label": resolveFieldLabel(group, schema), "dataType": resolveFieldDataType(group, schema)}
	}

	aci := gin.H{"RowCount": gin.H{"label": "Record Count", "dataType": "int"}}
	for _, a := range aggs {
		k := sfAggregateKey(a)
		if k != "RowCount" {
			aci[k] = gin.H{"label": friendlyAggLabel(a, schema), "dataType": resolveFieldDataType(a.Field, schema)}
		}
	}

	return gin.H{"detailColumnInfo": dci, "groupingColumnInfo": gci, "aggregateColumnInfo": aci}
}

// crossObjectDisplayAliases maps report column aliases to their result-row
// keys for cross-object columns where the display key cannot be derived from
// single-object schema metadata alone. These are columns where the SQL query
// joins across objects and the result key encodes the join path.
var crossObjectDisplayAliases = map[string]string{
	"FULL_NAME":       "OwnerId_Name",           // Owner display name, not record Name
	"OWNER":           "OwnerId_Name",           // Owner display name (nil-schema safe)
	"ACCOUNT_OWNER":   "AccountId_OwnerId_Name",  // Nested FK: Account → Owner → Name
	"CAMPAIGN_SOURCE": "CampaignId_Name",         // Campaign display name via FK
	"RECORD_TYPE":     "RecordTypeId_Name",        // RecordType display name via FK
	"RECORDTYPE":      "RecordTypeId_Name",        // Alias variant
}

// crossObjectLabels provides display labels for cross-object columns
// where lookupFieldMetadata resolves to the wrong field's label.
var crossObjectLabels = map[string]string{
	"FULL_NAME":       "Opportunity Owner",
	"OWNER":           "Owner",
	"ACCOUNT_OWNER":   "Account Owner",
	"CAMPAIGN_SOURCE": "Campaign Source",
	"RECORD_TYPE":     "Record Type",
	"RECORDTYPE":      "Record Type",
	"ACCOUNT_NAME":    "Account Name",
	"OPP_ACCT_NAME":   "Account Name",
	"LEAD_OWNER":      "Lead Owner",
}

// resolveFieldLabel returns the display label for a report column by looking up
// the field's Label in the object schema metadata. Falls back to string cleanup
// for columns that don't resolve to a known field (e.g. CRT computed columns).
func resolveFieldLabel(col string, schema *models.ObjectMetadata) string {
	if label, ok := crossObjectLabels[col]; ok {
		return label
	}
	if fm := lookupFieldMetadata(col, schema); fm != nil {
		return fm.Label
	}
	return fallbackColumnLabel(col)
}

// resolveFieldDataType returns the SF-compatible data type string for a report
// column by looking up the field's Type in the object schema metadata.
func resolveFieldDataType(col string, schema *models.ObjectMetadata) string {
	if fm := lookupFieldMetadata(col, schema); fm != nil {
		return metadataTypeToSF(fm.Type)
	}
	return "string"
}

// resolveColumnAlias derives the result-row key for a SCREAMING_SNAKE_CASE
// report column using metadata. For reference fields the display column follows
// the pattern {FieldAPIName}_Name; for non-reference fields it returns the API
// name directly. Returns "" if no resolution is possible.
func resolveColumnAlias(col string, schema *models.ObjectMetadata) string {
	// Cross-object display aliases take priority — these encode join-path
	// knowledge that can't be derived from single-object schema metadata.
	if alias, ok := crossObjectDisplayAliases[col]; ok {
		return alias
	}
	apiName, ok := analytics.SFColumnToField[col]
	if !ok {
		return ""
	}
	if schema != nil && schema.FieldsMap != nil {
		if fm, ok := schema.FieldsMap[apiName]; ok {
			if constants.IsReferenceType(fm.Type) {
				return apiName + "_Name"
			}
		}
	}
	return apiName
}

// lookupFieldMetadata resolves a report column name to its FieldMetadata entry.
// It handles SCREAMING_SNAKE_CASE (via SFColumnToField), dot-notation cross-
// object columns, and direct API names.
func lookupFieldMetadata(col string, schema *models.ObjectMetadata) *models.FieldMetadata {
	if schema == nil || schema.FieldsMap == nil {
		return nil
	}
	// Try SFColumnToField mapping first (SCREAMING_SNAKE_CASE → API name)
	base := strings.TrimSuffix(col, ".CONVERT")
	if apiName, ok := analytics.SFColumnToField[base]; ok {
		if fm, ok := schema.FieldsMap[apiName]; ok {
			return fm
		}
	}
	// Try direct API name lookup
	if fm, ok := schema.FieldsMap[col]; ok {
		return fm
	}
	if fm, ok := schema.FieldsMap[base]; ok {
		return fm
	}
	// Try dot-notation: "Opportunity.Product_Family__c" → "Product_Family__c"
	if idx := strings.LastIndex(base, "."); idx >= 0 {
		field := base[idx+1:]
		if fm, ok := schema.FieldsMap[field]; ok {
			return fm
		}
	}
	return nil
}

// metadataTypeToSF maps a FieldMetadata.Type to Salesforce Analytics type strings.
func metadataTypeToSF(t models.FieldType) string {
	switch t {
	case constants.FieldTypeDate, constants.FieldTypeDatetime:
		return "date"
	case constants.FieldTypeCurrency:
		return "currency"
	case constants.FieldTypePercent:
		return "percent"
	case constants.FieldTypeBoolean:
		return "boolean"
	case constants.FieldTypeDouble:
		return "int"
	default:
		return "string"
	}
}

// fallbackColumnLabel provides string cleanup for columns that don't resolve
// to a known field in the schema (e.g. CRT computed or cross-object columns).
func fallbackColumnLabel(col string) string {
	name := col
	if idx := strings.Index(name, "."); idx >= 0 {
		name = name[idx+1:]
	}
	name = strings.TrimSuffix(name, ".CONVERT")
	name = strings.TrimSuffix(name, "__c")
	name = strings.ReplaceAll(name, "_", " ")
	// Split PascalCase: "ForecastQuarter" → "Forecast Quarter"
	name = splitPascalCase(name)
	return cases.Title(language.English).String(name)
}

// splitPascalCase inserts spaces before uppercase letters that follow lowercase
// letters: "ForecastQuarter" → "Forecast Quarter", "OwnerId" → "Owner Id".
func splitPascalCase(s string) string {
	var b strings.Builder
	b.Grow(len(s) + 4)
	for i, r := range s {
		if i > 0 && r >= 'A' && r <= 'Z' {
			prev := s[i-1]
			if prev >= 'a' && prev <= 'z' {
				b.WriteByte(' ')
			}
		}
		b.WriteRune(r)
	}
	return b.String()
}

func friendlyAggLabel(a models.ReportAggregate, schema *models.ObjectMetadata) string {
	if fm := lookupFieldMetadata(a.Field, schema); fm != nil {
		return fm.Label
	}
	field := a.Field
	field = strings.TrimSuffix(field, ".CONVERT")
	if idx := strings.Index(field, "."); idx >= 0 {
		field = field[idx+1:]
	}
	field = strings.TrimSuffix(field, "__c")
	field = strings.ReplaceAll(field, "_", " ")
	return cases.Title(language.English).String(field)
}

func effectiveColumns(def *models.ReportDefinition, rows []models.SObject, isSummary bool) []string {
	if isSummary {
		return nil
	}
	if len(def.Columns) > 0 {
		return def.Columns
	}
	if len(rows) == 0 {
		return nil
	}
	cols := make([]string, 0, len(rows[0]))
	for k := range rows[0] {
		if k != "attributes" && k != "_metadata" {
			cols = append(cols, k)
		}
	}
	sort.Strings(cols)
	return cols
}

func resolveColumnValue(row models.SObject, col string, schema *models.ObjectMetadata) interface{} {
	if v, ok := row[col]; ok {
		return v
	}
	// CRT polymorphic / dot-notation columns ship with dots in the report
	// definition (e.g. "CampaignMember_ContactOrLead.Lead.Company") but the
	// SQL result alias collapses them to underscores (MySQL-friendly). Try
	// that form before falling back to per-segment guesses.
	if strings.Contains(col, ".") {
		underscored := strings.ReplaceAll(col, ".", "_")
		if v, ok := row[underscored]; ok {
			return v
		}
	}
	// Strip .CONVERT suffix (Salesforce currency conversion indicator)
	base := strings.TrimSuffix(col, ".CONVERT")
	if base != col {
		if v := resolveColumnValue(row, base, schema); v != nil {
			return v
		}
	}
	// Metadata-driven alias resolution: map SCREAMING_SNAKE_CASE → API field
	// name, then for reference fields try {FieldAPIName}_Name.
	if mapped := resolveColumnAlias(col, schema); mapped != "" {
		if v, ok := row[mapped]; ok {
			return v
		}
		// Reference fields often store the display name under {FK}_Name
		if v, ok := row[mapped+"_Name"]; ok {
			return v
		}
	}
	if mapped := resolveColumnAlias(base, schema); base != col && mapped != "" {
		if v, ok := row[mapped]; ok {
			return v
		}
		if v, ok := row[mapped+"_Name"]; ok {
			return v
		}
	}
	// Dot notation cross-object: "Account.Region__c" → try field part directly
	if idx := strings.LastIndex(col, "."); idx >= 0 {
		field := col[idx+1:]
		if v, ok := row[field]; ok {
			return v
		}
		// Also try stripped suffix: "Account.Latest_Month_ARR__c.CONVERT"
		field = strings.TrimSuffix(field, ".CONVERT")
		if v, ok := row[field]; ok {
			return v
		}
	}
	// Convert SCREAMING_SNAKE_CASE to PascalCase and try
	pascal := utils.SnakeToPascal(col)
	if pascal != col {
		if v, ok := row[pascal]; ok {
			return v
		}
	}
	// Reference fields store display names under {FK}_Name; try this pattern
	// before falling back to the expensive case-insensitive scan.
	if v, ok := row[col+"_Name"]; ok {
		return v
	}
	// TODO(bandaid): O(n) case-insensitive scan over all row keys. For a report
	// with R rows × C columns this is O(R×C×K) where K is key count. Should
	// build a lowercase-key index once per row or, better, normalize field
	// names at query time so exact-match always succeeds.
	lower := strings.ToLower(strings.TrimSuffix(col, ".CONVERT"))
	for k, v := range row {
		if strings.ToLower(k) == lower {
			return v
		}
	}
	return nil
}

func looksLikeAggregatedRows(rows []models.SObject) bool {
	if len(rows) == 0 {
		return false
	}
	_, hasName := rows[0]["name"]
	_, hasVal := rows[0]["val"]
	return hasName && hasVal
}

func formatCellValue(v interface{}) string {
	if v == nil {
		return ""
	}
	return fmt.Sprintf("%v", v)
}

func formatNumber(v float64) string {
	if v == float64(int64(v)) {
		return fmt.Sprintf("%d", int64(v))
	}
	return fmt.Sprintf("%.2f", v)
}

// ReportResponse represents a report in Salesforce wire format
type ReportResponse struct {
	ID            string `json:"id"`
	Name          string `json:"name"`
	DeveloperName string `json:"developerName"`
	FolderID      string `json:"folderId,omitempty"`
	Description   string `json:"description,omitempty"`
	ReportType    string `json:"reportType,omitempty"`
	SourceObject  string `json:"sourceObject,omitempty"`
}

func toReportResponse(r models.SObject) ReportResponse {
	return ReportResponse{
		ID:            utils.GetSObjectString(r, "Id"),
		Name:          utils.GetSObjectString(r, "Name"),
		DeveloperName: utils.GetSObjectString(r, "DeveloperName"),
		FolderID:      utils.GetSObjectString(r, "FolderId"),
		Description:   utils.GetSObjectString(r, "Description"),
		ReportType:    utils.GetSObjectString(r, "ReportType"),
		SourceObject:  utils.GetSObjectString(r, "SourceObject"),
	}
}

// ListReports handles GET /analytics/reports
func (h *AnalyticsHandler) ListReports(c *gin.Context) {
	user := MustGetUser(c)

	limit := 200
	if l := c.Query("limit"); l != "" {
		if parsed, err := strconv.Atoi(l); err == nil && parsed > 0 && parsed <= 2000 {
			limit = parsed
		}
	}

	ctx := c.Request.Context()
	result, err := h.svcMgr.QuerySvc.QueryWithFilter(ctx, reportTableName, "", user, "Name", "ASC", limit)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	reports := make([]ReportResponse, 0, len(result.Records))
	for _, r := range result.Records {
		reports = append(reports, toReportResponse(r))
	}

	c.JSON(http.StatusOK, gin.H{
		"reports":   reports,
		"totalSize": len(reports),
	})
}

// sfReportMetadata is the Salesforce reportMetadata wire format for create/update requests.
type sfReportMetadata struct {
	ID                 string                     `json:"id,omitempty"`
	Name               string                     `json:"name"`
	DeveloperName      string                     `json:"developerName,omitempty"`
	FolderID           string                     `json:"folderId,omitempty"`
	Description        string                     `json:"description,omitempty"`
	SourceObject       string                     `json:"sourceObject,omitempty"`
	ReportType         interface{}                `json:"reportType,omitempty"`
	ReportFormat       string                     `json:"reportFormat,omitempty"`
	Scope              string                     `json:"scope,omitempty"`
	DetailColumns      []string                   `json:"detailColumns,omitempty"`
	ReportFilters      []models.ReportFilter      `json:"reportFilters,omitempty"`
	GroupingsDown      []map[string]interface{}   `json:"groupingsDown,omitempty"`
	StandardDateFilter *models.StandardDateFilter `json:"standardDateFilter,omitempty"`
}

// reportMetadataRequest is the only accepted request envelope (Salesforce spec).
type reportMetadataRequest struct {
	ReportMetadata sfReportMetadata `json:"reportMetadata"`
}

func buildReportDefinition(meta sfReportMetadata) (*models.ReportDefinition, error) {
	if meta.Name == "" {
		return nil, fmt.Errorf("reportMetadata.name is required")
	}

	reportType := extractReportType(meta.ReportType)
	sourceObject := meta.SourceObject
	if sourceObject == "" {
		sourceObject = strings.TrimSuffix(reportType, "List")
		sourceObject = strings.TrimSuffix(sourceObject, "Report")
	}
	if sourceObject == "" {
		return nil, fmt.Errorf("reportMetadata.reportType or reportMetadata.sourceObject is required")
	}

	def := &models.ReportDefinition{
		ID:           meta.ID,
		Name:         meta.Name,
		SourceObject: sourceObject,
		ReportType:   reportType,
		ReportFormat: meta.ReportFormat,
		Scope:        meta.Scope,
		Columns:      append([]string(nil), meta.DetailColumns...),
		Filters:      append([]models.ReportFilter(nil), meta.ReportFilters...),
	}
	if def.ReportType == "" && def.SourceObject != "" {
		def.ReportType = def.SourceObject + "List"
	}

	if meta.StandardDateFilter != nil {
		sdf := *meta.StandardDateFilter
		def.StandardDateFilter = &sdf
	}

	if len(meta.GroupingsDown) > 0 {
		def.Grouping = make([]string, 0, len(meta.GroupingsDown))
		for _, grouping := range meta.GroupingsDown {
			if name, ok := grouping["name"].(string); ok && name != "" {
				def.Grouping = append(def.Grouping, name)
			}
		}
	}

	return def, nil
}

// extractReportType normalises reportType: accepts string or {type: string} object.
func extractReportType(v interface{}) string {
	switch t := v.(type) {
	case string:
		return t
	case map[string]interface{}:
		if s, ok := t["type"].(string); ok {
			return s
		}
	}
	return ""
}

func extractGroupingNames(groupings []map[string]interface{}) []string {
	names := make([]string, 0, len(groupings))
	for _, g := range groupings {
		if n, ok := g["name"].(string); ok && n != "" {
			names = append(names, n)
		}
	}
	return names
}

func mergeExecutionMetadata(def *models.ReportDefinition, meta sfReportMetadata) {
	if meta.Name != "" {
		def.Name = meta.Name
	}
	if meta.SourceObject != "" {
		def.SourceObject = meta.SourceObject
	}
	if meta.ReportType != nil {
		if rt := extractReportType(meta.ReportType); rt != "" {
			def.ReportType = rt
			if def.SourceObject == "" {
				sourceObject := strings.TrimSuffix(rt, "List")
				sourceObject = strings.TrimSuffix(sourceObject, "Report")
				if sourceObject != "" {
					def.SourceObject = sourceObject
				}
			}
		}
	}
	if meta.ReportFormat != "" {
		def.ReportFormat = meta.ReportFormat
	}
	if meta.Scope != "" {
		// Override the persisted scope so preview requests can reflect the user's
		// live "Show me" selection (mine / myTeam / all) without forcing a save.
		// applyShowMeScope only reacts to 'mine' / 'myTeam'; any other value
		// (empty, legacy SF values) is a no-op, matching the "All" default.
		def.Scope = meta.Scope
	}
	if meta.DetailColumns != nil {
		def.Columns = meta.DetailColumns
	}
	if meta.ReportFilters != nil {
		def.Filters = meta.ReportFilters
	}
	if meta.GroupingsDown != nil {
		def.Grouping = extractGroupingNames(meta.GroupingsDown)
	}
	if meta.StandardDateFilter != nil {
		def.StandardDateFilter = meta.StandardDateFilter
	}
}

// ExecuteReportDefinition handles POST /analytics/reports/execute
func (h *AnalyticsHandler) ExecuteReportDefinition(c *gin.Context) {
	user := MustGetUser(c)

	var req reportMetadataRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, err.Error()))
		return
	}

	def, err := buildReportDefinition(req.ReportMetadata)
	if err != nil {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, err.Error()))
		return
	}
	if def.ReportFormat == "" {
		if len(def.Grouping) > 0 {
			def.ReportFormat = "SUMMARY"
		} else {
			def.ReportFormat = "TABULAR"
		}
	}

	result, err := h.reportSvc.ExecuteReportDefinition(c.Request.Context(), def, user, nil)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	schema := h.getSchema(c, result.ReportMetadata.SourceObject)
	c.JSON(http.StatusOK, toSalesforceReportResult(result, schema))
}

// CreateReport handles POST /analytics/reports
func (h *AnalyticsHandler) CreateReport(c *gin.Context) {
	user := MustGetUser(c)

	var req reportMetadataRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, err.Error()))
		return
	}
	meta := req.ReportMetadata

	if meta.Name == "" {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, "reportMetadata.name is required"))
		return
	}

	reportType := extractReportType(meta.ReportType)

	// Derive sourceObject from reportType (e.g. "AccountList" → "Account")
	sourceObject := strings.TrimSuffix(reportType, "List")
	sourceObject = strings.TrimSuffix(sourceObject, "Report")
	if sourceObject == "" {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeRequiredFieldMissing, "reportMetadata.reportType is required"))
		return
	}

	developerName := meta.DeveloperName
	if developerName == "" {
		developerName = utils.SanitizeDeveloperName(meta.Name)
	}

	ctx := c.Request.Context()

	data := models.SObject{
		"Name":          meta.Name,
		"DeveloperName": developerName,
		"SourceObject":  sourceObject,
		"ReportType":    reportType,
	}
	if meta.FolderID != "" {
		// Creating a report in a folder requires Manage access on that folder
		if h.svcMgr.FolderAccess != nil && !h.svcMgr.FolderAccess.CheckAccess(ctx, user, meta.FolderID, permissions.FolderAccessManage) {
			c.JSON(http.StatusForbidden, apierr.SFErrorResponse(apierr.SFCodeForbidden, "You do not have Manage access on the target folder"))
			return
		}
		data["FolderId"] = meta.FolderID
	}
	if meta.Description != "" {
		data["Description"] = meta.Description
	}
	if meta.Scope != "" {
		data["Scope"] = meta.Scope
	}

	// Persist expanded metadata as JSON columns
	if len(meta.DetailColumns) > 0 {
		b, _ := json.Marshal(meta.DetailColumns)
		data["Columns"] = string(b)
	}
	if len(meta.ReportFilters) > 0 {
		b, _ := json.Marshal(meta.ReportFilters)
		data["Filters"] = string(b)
	}
	if len(meta.GroupingsDown) > 0 {
		// Extract grouping field names for internal storage
		names := extractGroupingNames(meta.GroupingsDown)
		if len(names) > 0 {
			b, _ := json.Marshal(names)
			data["Grouping"] = string(b)
		}
	}
	if meta.StandardDateFilter != nil {
		b, _ := json.Marshal(meta.StandardDateFilter)
		data["StandardDateFilter"] = string(b)
	}

	createdRecord, err := h.svcMgr.Persistence.Insert(ctx, reportTableName, data, user)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	c.JSON(http.StatusCreated, gin.H{"id": utils.GetSObjectString(createdRecord, "Id")})
}

// UpdateReport handles PATCH /analytics/reports/:reportId
func (h *AnalyticsHandler) UpdateReport(c *gin.Context) {
	user := MustGetUser(c)

	reportID, ok := RequireParam(c, "reportId")
	if !ok {
		return
	}

	var req reportMetadataRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeInvalidField, err.Error()))
		return
	}
	meta := req.ReportMetadata

	updates := models.SObject{}

	if meta.Name != "" {
		updates["Name"] = meta.Name
	}
	if meta.Description != "" {
		updates["Description"] = meta.Description
	}
	if meta.DeveloperName != "" {
		updates["DeveloperName"] = meta.DeveloperName
	}
	if meta.ReportType != nil {
		rt := extractReportType(meta.ReportType)
		if rt != "" {
			updates["ReportType"] = rt
			// Also update SourceObject for consistency
			sourceObject := strings.TrimSuffix(rt, "List")
			sourceObject = strings.TrimSuffix(sourceObject, "Report")
			updates["SourceObject"] = sourceObject
		}
	}
	if meta.FolderID != "" {
		// Moving a report to another folder requires Manage access on the target folder
		ctx := c.Request.Context()
		if h.svcMgr.FolderAccess != nil && !h.svcMgr.FolderAccess.CheckAccess(ctx, user, meta.FolderID, permissions.FolderAccessManage) {
			c.JSON(http.StatusForbidden, apierr.SFErrorResponse(apierr.SFCodeForbidden, "You do not have Manage access on the target folder"))
			return
		}
		updates["FolderId"] = meta.FolderID
	}
	if meta.Scope != "" {
		updates["Scope"] = meta.Scope
	}

	// Expanded metadata as JSON
	if len(meta.DetailColumns) > 0 {
		b, _ := json.Marshal(meta.DetailColumns)
		updates["Columns"] = string(b)
	}
	if len(meta.ReportFilters) > 0 {
		b, _ := json.Marshal(meta.ReportFilters)
		updates["Filters"] = string(b)
	}
	if len(meta.GroupingsDown) > 0 {
		names := extractGroupingNames(meta.GroupingsDown)
		if len(names) > 0 {
			b, _ := json.Marshal(names)
			updates["Grouping"] = string(b)
		}
	}
	if meta.StandardDateFilter != nil {
		b, _ := json.Marshal(meta.StandardDateFilter)
		updates["StandardDateFilter"] = string(b)
	}

	if len(updates) == 0 {
		c.JSON(http.StatusBadRequest, apierr.SFErrorResponse(apierr.SFCodeInvalidField, "No fields to update"))
		return
	}

	ctx := c.Request.Context()
	if err := h.svcMgr.Persistence.Update(ctx, reportTableName, reportID, updates, user); err != nil {
		RespondAppError(c, err)
		return
	}

	record, err := h.svcMgr.Persistence.Retrieve(ctx, reportTableName, reportID, user)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	c.JSON(http.StatusOK, toReportResponse(record))
}

// DeleteReport handles DELETE /analytics/reports/:reportId
func (h *AnalyticsHandler) DeleteReport(c *gin.Context) {
	user := MustGetUser(c)

	reportID, ok := RequireParam(c, "reportId")
	if !ok {
		return
	}

	ctx := c.Request.Context()
	err := h.svcMgr.Persistence.Delete(ctx, reportTableName, reportID, user)
	if err != nil {
		RespondAppError(c, err)
		return
	}

	c.Status(http.StatusNoContent)
}