package sfcrmappservice

import (
	"context"
	"encoding/json"
	"fmt"
	"io"
	"net/http"
	"sort"
	"strings"
	"time"

	catalogsemantic "github.com/pingcap-inc/nexuscrm-contract/pkg/catalog/consumerv1"
	controlplane "github.com/pingcap-inc/nexuscrm-contract/pkg/controlplane"
	commonsemantic "github.com/pingcap-inc/nexuscrm-contract/pkg/runtime/commonv1"
	querysemantic "github.com/pingcap-inc/nexuscrm-contract/pkg/runtime/queryv1"
)

var analyticsHistorySelectedFields = []string{
	"Id", "Field", "OldValue", "NewValue", "DataType", "CreatedDate", "CreatedById",
}

type temporaryReportDefinitionRequest struct {
	ReportMetadata temporaryReportDefinition `json:"reportMetadata"`
}

type temporaryReportDefinition struct {
	Name          string                    `json:"name"`
	ReportType    temporaryReportType       `json:"reportType"`
	ReportFormat  string                    `json:"reportFormat"`
	DetailColumns []string                  `json:"detailColumns"`
	ReportFilters []canonicalReportFilter   `json:"reportFilters,omitempty"`
	GroupingsDown []temporaryReportGrouping `json:"groupingsDown,omitempty"`
}

type temporaryReportType struct {
	Type string `json:"type"`
}

type temporaryReportGrouping struct {
	Name            string `json:"name"`
	DateGranularity string `json:"dateGranularity,omitempty"`
}

type historyReportSource struct {
	SourceRef        string
	ParentObject     string
	ParentIDColumn   string
	ParentNameColumn string
	Columns          []historyReportColumn
}

type historyReportColumn struct {
	Name               string
	Label              string
	Type               string
	ParentField        string
	HistoricalSnapshot bool
}

type opportunityHistoryColumnConfig struct {
	Name               string `json:"name"`
	FieldAPIName       string `json:"fieldAPIName"`
	HistoricalSnapshot bool   `json:"historicalSnapshot,omitempty"`
}

func (client *RuntimeAnalyticsClient) executeReportDefinition(
	ctx context.Context,
	bearer string,
	authority commonsemantic.Receipts,
	maximum int64,
	writer http.ResponseWriter,
	request *http.Request,
) (map[string]any, error) {
	request.Body = http.MaxBytesReader(writer, request.Body, maximum)
	decoder := json.NewDecoder(request.Body)
	decoder.DisallowUnknownFields()
	var envelope temporaryReportDefinitionRequest
	if err := decoder.Decode(&envelope); err != nil || requireJSONEOF(decoder) != nil {
		return nil, errAnalyticsInvalid
	}
	definition := envelope.ReportMetadata
	definition.Name = strings.TrimSpace(definition.Name)
	definition.ReportType.Type = strings.TrimSpace(definition.ReportType.Type)
	definition.ReportFormat = strings.ToUpper(strings.TrimSpace(definition.ReportFormat))
	if definition.Name == "" || definition.ReportType.Type == "" || len(definition.DetailColumns) == 0 ||
		(definition.ReportFormat != "TABULAR" && definition.ReportFormat != "SUMMARY") {
		return nil, errAnalyticsInvalid
	}

	view, objects, err := client.temporaryReportMetadata(ctx, authority)
	if err != nil {
		return nil, err
	}
	source, err := historyReportSourceFromMetadata(definition.ReportType.Type, view)
	if err != nil {
		return nil, err
	}
	if err := normalizeTemporaryReportDefinition(&definition, source); err != nil {
		return nil, err
	}

	var rows []map[string]any
	var total int
	switch source.SourceRef {
	case "AccountHistory", "OpportunityFieldHistory":
		rows, total, err = client.executeFieldHistoryReport(ctx, bearer, authority, definition, source)
	case "OpportunityHistory":
		rows, total, err = client.executeOpportunityHistoryReport(ctx, bearer, authority, definition, source)
	default:
		err = errAnalyticsInvalid
	}
	if err != nil {
		return nil, err
	}

	objects, err = historyReportObjectSpecs(objects, source)
	if err != nil {
		return nil, err
	}
	grouping := make([]string, len(definition.GroupingsDown))
	for index := range definition.GroupingsDown {
		grouping[index] = definition.GroupingsDown[index].Name
	}
	canonicalDefinition := canonicalReportDefinition{
		SourceObject: source.ParentObject,
		SourceRef:    source.SourceRef,
		DisplayMode:  definition.ReportFormat,
		Columns:      append([]string(nil), definition.DetailColumns...),
		Filters:      append([]canonicalReportFilter(nil), definition.ReportFilters...),
		Grouping:     append([]string(nil), grouping...),
	}
	document := canonicalReportDocument{
		Schema:            "nexuscrm.report-result/v1",
		ReportID:          "",
		Name:              definition.Name,
		DefinitionVersion: "temporary",
		DefinitionDigest:  "temporary",
		Definition:        canonicalDefinition,
		DisplayMode:       definition.ReportFormat,
		Columns:           append([]string(nil), definition.DetailColumns...),
		Grouping:          grouping,
		Rows:              rows,
		TotalCount:        total,
	}
	projection, err := projectCanonicalReport(document, objects)
	if err != nil {
		return nil, err
	}
	metadata, ok := projection["reportMetadata"].(map[string]any)
	if !ok {
		return nil, errAnalyticsAuthority
	}
	delete(metadata, "definitionVersion")
	delete(metadata, "definitionDigest")
	delete(metadata, "sourceRef")
	delete(metadata, "scope")
	return projection, nil
}

func (client *RuntimeAnalyticsClient) temporaryReportMetadata(
	ctx context.Context,
	authority commonsemantic.Receipts,
) (catalogsemantic.AppMetadataViewV3, map[string]searchObjectSpec, error) {
	revision := authority.CatalogRevision
	view, err := client.appMetadata(ctx, catalogsemantic.GetRequest{MinCatalogRevision: &revision})
	if err != nil {
		return catalogsemantic.AppMetadataViewV3{}, nil, err
	}
	if err := catalogsemantic.ValidateAppMetadataViewV3(view); err != nil || view.AppID != AppID {
		return catalogsemantic.AppMetadataViewV3{}, nil, errAnalyticsAuthority
	}
	if err := client.sameAppMetadataAuthority(ctx, view, authority); err != nil {
		return catalogsemantic.AppMetadataViewV3{}, nil, err
	}
	objects, err := searchObjectSpecsFromView(view)
	if err != nil {
		return catalogsemantic.AppMetadataViewV3{}, nil, errAnalyticsAuthority
	}
	return view, objects, nil
}

func historyReportSourceFromMetadata(
	sourceRef string,
	view catalogsemantic.AppMetadataViewV3,
) (historyReportSource, error) {
	switch sourceRef {
	case "AccountHistory":
		return newFieldHistoryReportSource("AccountHistory", "Account", "AccountId", "AccountName"), nil
	case "OpportunityFieldHistory":
		return newFieldHistoryReportSource("OpportunityFieldHistory", "Opportunity", "OpportunityId", "OpportunityName"), nil
	case "OpportunityHistory":
		extensions, err := opportunityHistoryColumnsFromView(view)
		if err != nil {
			return historyReportSource{}, err
		}
		return newOpportunityHistoryReportSource(extensions), nil
	default:
		return historyReportSource{}, errAnalyticsInvalid
	}
}

func newFieldHistoryReportSource(sourceRef, parentObject, parentIDColumn, parentNameColumn string) historyReportSource {
	return historyReportSource{
		SourceRef: sourceRef, ParentObject: parentObject,
		ParentIDColumn: parentIDColumn, ParentNameColumn: parentNameColumn,
		Columns: []historyReportColumn{
			{Name: "Id", Label: "History ID", Type: "string"},
			{Name: parentIDColumn, Label: parentObject + " ID", Type: "string", ParentField: "Id"},
			{Name: parentNameColumn, Label: parentObject + " Name", Type: "string", ParentField: "Name"},
			{Name: "Field", Label: "Field", Type: "string"},
			{Name: "OldValue", Label: "Old Value", Type: "string"},
			{Name: "NewValue", Label: "New Value", Type: "string"},
			{Name: "DataType", Label: "Data Type", Type: "string"},
			{Name: "CreatedDate", Label: "Created Date", Type: "datetime"},
			{Name: "CreatedById", Label: "Created By", Type: "string"},
		},
	}
}

func newOpportunityHistoryReportSource(extensions []opportunityHistoryColumnConfig) historyReportSource {
	source := historyReportSource{
		SourceRef: "OpportunityHistory", ParentObject: "Opportunity",
		ParentIDColumn: "OpportunityId", ParentNameColumn: "OpportunityName",
		Columns: []historyReportColumn{
			{Name: "Id", Label: "History ID", Type: "string"},
			{Name: "OpportunityId", Label: "Opportunity ID", Type: "string", ParentField: "Id"},
			{Name: "OpportunityName", Label: "Opportunity Name", Type: "string", ParentField: "Name"},
			{Name: "OwnerId", Label: "Owner ID", Type: "string", ParentField: "OwnerId"},
			{Name: "OwnerName", Label: "Owner Name", Type: "string"},
			{Name: "OwnerRole", Label: "Owner Role", Type: "string"},
			{Name: "FromStageName", Label: "From Stage", Type: "picklist"},
			{Name: "StageName", Label: "Stage", Type: "picklist", ParentField: "StageName", HistoricalSnapshot: true},
			{Name: "StageDuration", Label: "Stage Duration", Type: "double"},
			{Name: "LastStageChangeDate", Label: "Last Stage Change Date", Type: "datetime"},
			{Name: "Probability", Label: "Probability", Type: "percent", ParentField: "Probability", HistoricalSnapshot: true},
			{Name: "CloseDate", Label: "Close Date", Type: "date", ParentField: "CloseDate", HistoricalSnapshot: true},
			{Name: "ForecastCategory", Label: "Forecast Category", Type: "picklist", ParentField: "ForecastCategory", HistoricalSnapshot: true},
			{Name: "CreatedDate", Label: "Created Date", Type: "datetime"},
		},
	}
	for _, extension := range extensions {
		source.Columns = append(source.Columns, historyReportColumn{
			Name: extension.Name, Label: extension.Name, Type: "string",
			ParentField: extension.FieldAPIName, HistoricalSnapshot: extension.HistoricalSnapshot,
		})
	}
	return source
}

func opportunityHistoryColumnsFromView(view catalogsemantic.AppMetadataViewV3) ([]opportunityHistoryColumnConfig, error) {
	var payload []byte
	for _, component := range view.EffectiveComponents {
		if component.ComponentKind != catalogsemantic.EffectiveReportTypeComponentKind ||
			component.BusinessKey != "OpportunityHistory" {
			continue
		}
		if payload != nil || component.ComponentFormatVersion != 1 {
			return nil, errAnalyticsAuthority
		}
		payload = component.CanonicalBehavior
	}
	if payload == nil {
		return nil, errAnalyticsAuthority
	}
	var declaration controlplane.ReportTypeDeclaration
	decoder := json.NewDecoder(strings.NewReader(string(payload)))
	decoder.DisallowUnknownFields()
	if err := decoder.Decode(&declaration); err != nil || decoder.Decode(&struct{}{}) != io.EOF ||
		declaration.FullName != "OpportunityHistory" || declaration.BaseObject != "Opportunity" ||
		!declaration.Deployed || len(declaration.Metadata) == 0 {
		return nil, errAnalyticsAuthority
	}
	var metadata struct {
		VirtualSource struct {
			SourceRef     string `json:"sourceRef"`
			Configuration struct {
				Columns []opportunityHistoryColumnConfig `json:"opportunityHistoryColumns"`
			} `json:"configuration"`
		} `json:"virtualSource"`
	}
	decoder = json.NewDecoder(strings.NewReader(string(declaration.Metadata)))
	decoder.DisallowUnknownFields()
	if err := decoder.Decode(&metadata); err != nil || decoder.Decode(&struct{}{}) != io.EOF ||
		metadata.VirtualSource.SourceRef != "OpportunityHistory" {
		return nil, errAnalyticsAuthority
	}
	result := append([]opportunityHistoryColumnConfig(nil), metadata.VirtualSource.Configuration.Columns...)
	if len(result) > querysemantic.MaxSelectedFields-4 {
		return nil, errAnalyticsAuthority
	}
	seenNames := map[string]struct{}{}
	for _, name := range []string{
		"Id", "OpportunityId", "OpportunityName", "OwnerId", "OwnerName", "OwnerRole",
		"FromStageName", "StageName", "StageDuration", "LastStageChangeDate",
		"Probability", "CloseDate", "ForecastCategory", "CreatedDate",
	} {
		seenNames[strings.ToLower(name)] = struct{}{}
	}
	seenFields := map[string]struct{}{}
	for index := range result {
		result[index].Name = strings.TrimSpace(result[index].Name)
		result[index].FieldAPIName = strings.TrimSpace(result[index].FieldAPIName)
		nameKey, fieldKey := strings.ToLower(result[index].Name), strings.ToLower(result[index].FieldAPIName)
		if !searchObjectIdentifierPattern.MatchString(result[index].Name) ||
			!searchObjectIdentifierPattern.MatchString(result[index].FieldAPIName) {
			return nil, errAnalyticsAuthority
		}
		if _, duplicate := seenNames[nameKey]; duplicate {
			return nil, errAnalyticsAuthority
		}
		if _, duplicate := seenFields[fieldKey]; duplicate {
			return nil, errAnalyticsAuthority
		}
		seenNames[nameKey], seenFields[fieldKey] = struct{}{}, struct{}{}
	}
	return result, nil
}

func normalizeTemporaryReportDefinition(definition *temporaryReportDefinition, source historyReportSource) error {
	columns := make(map[string]historyReportColumn, len(source.Columns))
	for _, column := range source.Columns {
		columns[strings.ToLower(column.Name)] = column
	}
	seen := map[string]struct{}{}
	for index, name := range definition.DetailColumns {
		column, ok := columns[strings.ToLower(strings.TrimSpace(name))]
		if !ok {
			return errAnalyticsInvalid
		}
		key := strings.ToLower(column.Name)
		if _, duplicate := seen[key]; duplicate {
			return errAnalyticsInvalid
		}
		seen[key] = struct{}{}
		definition.DetailColumns[index] = column.Name
	}
	seen = map[string]struct{}{}
	for index := range definition.GroupingsDown {
		column, ok := columns[strings.ToLower(strings.TrimSpace(definition.GroupingsDown[index].Name))]
		if !ok || strings.TrimSpace(definition.GroupingsDown[index].DateGranularity) != "" &&
			!strings.EqualFold(definition.GroupingsDown[index].DateGranularity, "none") {
			return errAnalyticsInvalid
		}
		key := strings.ToLower(column.Name)
		if _, duplicate := seen[key]; duplicate {
			return errAnalyticsInvalid
		}
		seen[key] = struct{}{}
		definition.GroupingsDown[index].Name = column.Name
	}
	if definition.ReportFormat == "TABULAR" && len(definition.GroupingsDown) != 0 ||
		definition.ReportFormat == "SUMMARY" && len(definition.GroupingsDown) == 0 {
		return errAnalyticsInvalid
	}
	for index := range definition.ReportFilters {
		column, ok := columns[strings.ToLower(strings.TrimSpace(definition.ReportFilters[index].Column))]
		if !ok || strings.TrimSpace(definition.ReportFilters[index].Value) == "" {
			return errAnalyticsInvalid
		}
		definition.ReportFilters[index].Column = column.Name
		if _, ok := historyReportComparison(definition.ReportFilters[index].Operator); !ok {
			return errAnalyticsInvalid
		}
	}
	return nil
}

func historyReportComparison(operator string) (querysemantic.ComparisonOperator, bool) {
	switch strings.ToLower(strings.TrimSpace(operator)) {
	case "equals", "eq", "=":
		return querysemantic.ComparisonEqual, true
	case "notequal", "neq", "!=", "<>":
		return querysemantic.ComparisonNotEqual, true
	case "greaterthan", "gt", ">":
		return querysemantic.ComparisonGreaterThan, true
	case "greaterorequal", "gte", ">=":
		return querysemantic.ComparisonGreaterOrEqual, true
	case "lessthan", "lt", "<":
		return querysemantic.ComparisonLessThan, true
	case "lessorequal", "lte", "<=":
		return querysemantic.ComparisonLessOrEqual, true
	case "contains":
		return querysemantic.ComparisonContains, true
	case "startswith":
		return querysemantic.ComparisonStartsWith, true
	default:
		return "", false
	}
}

func (client *RuntimeAnalyticsClient) executeFieldHistoryReport(
	ctx context.Context,
	bearer string,
	authority commonsemantic.Receipts,
	definition temporaryReportDefinition,
	source historyReportSource,
) ([]map[string]any, int, error) {
	parentID, predicate, err := fieldHistoryReportPredicate(definition.ReportFilters, source.ParentIDColumn)
	if err != nil || parentID == "" {
		return nil, 0, errAnalyticsInvalid
	}
	response, err := client.queryHistory(ctx, bearer, authority, querysemantic.HistoryRequest{
		ParentObjectID: source.ParentObject, SelectedFields: analyticsHistorySelectedFields,
		ParentRecordID: parentID, Filter: predicate, Limit: querysemantic.MaxHistoryRows,
		PageSize: querysemantic.MaxHistoryRows, SortDescending: true,
	})
	if err != nil {
		return nil, 0, err
	}
	if !response.Done || response.TotalSize != uint64(len(response.Rows)) {
		return nil, 0, errAnalyticsAuthority
	}
	parent, err := client.queryOneRecord(ctx, bearer, authority, source.ParentObject, parentID, []string{"Id", "OwnerId", "Name"})
	if err != nil {
		return nil, 0, err
	}
	rows := make([]map[string]any, 0, len(response.Rows))
	for _, item := range response.Rows {
		if item.ParentID != parentID {
			return nil, 0, errAnalyticsAuthority
		}
		rows = append(rows, map[string]any{
			"Id": item.ID, source.ParentIDColumn: parentID, source.ParentNameColumn: parent["Name"],
			"Field": item.Field, "OldValue": historyReportValue(item.OldValue), "NewValue": historyReportValue(item.NewValue),
			"DataType": item.DataType, "CreatedDate": item.CreatedDate, "CreatedById": item.CreatedByID,
		})
	}
	return rows, len(rows), nil
}

func fieldHistoryReportPredicate(filters []canonicalReportFilter, parentIDColumn string) (string, querysemantic.Predicate, error) {
	parentID := ""
	predicates := make([]querysemantic.Predicate, 0, len(filters))
	for _, filter := range filters {
		operator, ok := historyReportComparison(filter.Operator)
		if !ok {
			return "", querysemantic.Predicate{}, errAnalyticsInvalid
		}
		if strings.EqualFold(filter.Column, parentIDColumn) {
			if parentID != "" || operator != querysemantic.ComparisonEqual {
				return "", querysemantic.Predicate{}, errAnalyticsInvalid
			}
			parentID = filter.Value
			continue
		}
		switch strings.ToLower(filter.Column) {
		case "id", "field", "createddate", "createdbyid":
		default:
			return "", querysemantic.Predicate{}, errAnalyticsInvalid
		}
		value := filter.Value
		predicates = append(predicates, querysemantic.Predicate{
			Kind: querysemantic.PredicateFieldComparison, Field: filter.Column, Operator: operator,
			Value: &querysemantic.Scalar{String: &value},
		})
	}
	if len(predicates) == 0 {
		return parentID, querysemantic.Predicate{}, nil
	}
	return parentID, combinePredicates(querysemantic.PredicateAnd, predicates), nil
}

func (client *RuntimeAnalyticsClient) executeOpportunityHistoryReport(
	ctx context.Context,
	bearer string,
	authority commonsemantic.Receipts,
	definition temporaryReportDefinition,
	source historyReportSource,
) ([]map[string]any, int, error) {
	parentID := ""
	for _, filter := range definition.ReportFilters {
		operator, _ := historyReportComparison(filter.Operator)
		if strings.EqualFold(filter.Column, source.ParentIDColumn) {
			if parentID != "" || operator != querysemantic.ComparisonEqual {
				return nil, 0, errAnalyticsInvalid
			}
			parentID = filter.Value
			continue
		}
		return nil, 0, errAnalyticsInvalid
	}
	if parentID == "" {
		return nil, 0, errAnalyticsInvalid
	}

	snapshotFields := map[string]struct{}{"StageName": {}, "Probability": {}, "CloseDate": {}, "ForecastCategory": {}}
	parentFields := []string{"Id", "Name", "OwnerId", "StageName", "Probability", "CloseDate", "ForecastCategory"}
	for _, column := range source.Columns {
		if column.ParentField == "" {
			continue
		}
		parentFields = appendUniqueFold(parentFields, column.ParentField)
		if column.HistoricalSnapshot {
			snapshotFields[column.ParentField] = struct{}{}
		}
	}
	parent, err := client.queryOneRecord(ctx, bearer, authority, source.ParentObject, parentID, parentFields)
	if err != nil {
		return nil, 0, err
	}
	history, err := client.queryHistory(ctx, bearer, authority, querysemantic.HistoryRequest{
		ParentObjectID: source.ParentObject, SelectedFields: analyticsHistorySelectedFields,
		ParentRecordID: parentID, Limit: querysemantic.MaxHistoryRows, PageSize: querysemantic.MaxHistoryRows,
	})
	if err != nil {
		return nil, 0, err
	}
	if !history.Done || history.TotalSize != uint64(len(history.Rows)) {
		return nil, 0, errAnalyticsAuthority
	}
	events := make([]querysemantic.HistoryRow, 0, len(history.Rows))
	for _, event := range history.Rows {
		if event.ParentID != parentID {
			return nil, 0, errAnalyticsAuthority
		}
		if _, relevant := snapshotFields[event.Field]; relevant {
			events = append(events, event)
		}
	}
	if len(events) == 0 {
		return []map[string]any{}, 0, nil
	}
	sort.SliceStable(events, func(left, right int) bool {
		if events[left].CreatedDate == events[right].CreatedDate {
			return events[left].ID < events[right].ID
		}
		return events[left].CreatedDate < events[right].CreatedDate
	})

	owner := map[string]any{}
	role := map[string]any{}
	if historyReportNeedsColumn(definition, "OwnerName") || historyReportNeedsColumn(definition, "OwnerRole") {
		ownerID := analyticsString(parent["OwnerId"])
		if ownerID == "" {
			return nil, 0, errAnalyticsAuthority
		}
		owner, err = client.queryOneRecord(ctx, bearer, authority, "User", ownerID, []string{"Id", "Name", "UserRoleId"})
		if err != nil {
			return nil, 0, err
		}
		if historyReportNeedsColumn(definition, "OwnerRole") {
			roleID := analyticsString(owner["UserRoleId"])
			if roleID != "" {
				role, err = client.queryOneRecord(ctx, bearer, authority, "UserRole", roleID, []string{"Id", "Name"})
				if err != nil {
					return nil, 0, err
				}
			}
		}
	}

	state := make(map[string]any, len(snapshotFields))
	for field := range snapshotFields {
		state[field] = parent[field]
	}
	seeded := map[string]struct{}{}
	for _, event := range events {
		if _, ok := seeded[event.Field]; ok {
			continue
		}
		seeded[event.Field] = struct{}{}
		state[event.Field] = historyReportValue(event.OldValue)
	}
	lastStageDate := ""
	for _, event := range events {
		if event.Field == "StageName" && event.CreatedDate > lastStageDate {
			lastStageDate = event.CreatedDate
		}
	}
	rows := make([]map[string]any, 0)
	previousStageDate := ""
	for start := 0; start < len(events); {
		end := start + 1
		for end < len(events) && events[end].CreatedDate == events[start].CreatedDate {
			end++
		}
		for _, event := range events[start:end] {
			state[event.Field] = historyReportValue(event.NewValue)
		}
		for _, event := range events[start:end] {
			if event.Field != "StageName" {
				continue
			}
			duration, err := opportunityStageDuration(event.CreatedDate, previousStageDate)
			if err != nil {
				return nil, 0, errAnalyticsAuthority
			}
			row := map[string]any{
				"Id": event.ID, "OpportunityId": parentID, "OpportunityName": parent["Name"],
				"OwnerId": parent["OwnerId"], "OwnerName": owner["Name"], "OwnerRole": role["Name"],
				"FromStageName": historyReportValue(event.OldValue), "StageName": historyReportValue(event.NewValue),
				"StageDuration": duration, "LastStageChangeDate": lastStageDate,
				"Probability": historyReportStringValue(state["Probability"]), "CloseDate": state["CloseDate"],
				"ForecastCategory": state["ForecastCategory"], "CreatedDate": event.CreatedDate,
			}
			for _, column := range source.Columns {
				if column.ParentField == "" || row[column.Name] != nil {
					continue
				}
				if column.HistoricalSnapshot {
					row[column.Name] = state[column.ParentField]
				} else {
					row[column.Name] = parent[column.ParentField]
				}
			}
			rows = append(rows, row)
			previousStageDate = event.CreatedDate
		}
		start = end
	}
	sort.SliceStable(rows, func(left, right int) bool {
		leftDate, rightDate := analyticsFormat(rows[left]["CreatedDate"]), analyticsFormat(rows[right]["CreatedDate"])
		if leftDate == rightDate {
			return analyticsFormat(rows[left]["Id"]) < analyticsFormat(rows[right]["Id"])
		}
		return leftDate > rightDate
	})
	return rows, len(rows), nil
}

func (client *RuntimeAnalyticsClient) queryHistory(
	ctx context.Context,
	bearer string,
	authority commonsemantic.Receipts,
	request querysemantic.HistoryRequest,
) (querysemantic.HistoryResponse, error) {
	revision := authority.CatalogRevision
	request.MinCatalogRevision = &revision
	if err := querysemantic.ValidateHistoryRequest(ctx, request); err != nil {
		return querysemantic.HistoryResponse{}, err
	}
	response, err := client.history(ctx, bearer, request)
	if err != nil {
		return querysemantic.HistoryResponse{}, err
	}
	if err := querysemantic.ValidateHistoryResponse(response); err != nil {
		return querysemantic.HistoryResponse{}, err
	}
	if err := sameAnalyticsAuthority(authority, response.Receipts); err != nil {
		return querysemantic.HistoryResponse{}, err
	}
	return response, nil
}

func (client *RuntimeAnalyticsClient) queryOneRecord(
	ctx context.Context,
	bearer string,
	authority commonsemantic.Receipts,
	objectID, recordID string,
	fields []string,
) (map[string]any, error) {
	page, err := client.queryRecordPage(ctx, bearer, authority, querysemantic.Request{
		ObjectID: objectID, Selections: analyticsSelections(fields...),
		Filter: analyticsStringPredicate("Id", recordID), Limit: 1,
	})
	if err != nil {
		return nil, err
	}
	if page.TotalSizeUnknown || !page.Done || page.TotalSize != 1 || len(page.Records) != 1 {
		return nil, errAnalyticsNotFound
	}
	return page.Records[0], nil
}

func historyReportObjectSpecs(
	objects map[string]searchObjectSpec,
	source historyReportSource,
) (map[string]searchObjectSpec, error) {
	result := make(map[string]searchObjectSpec, len(objects))
	for key, object := range objects {
		object.Fields = append([]searchFieldSpec(nil), object.Fields...)
		result[key] = object
	}
	key := strings.ToLower(source.ParentObject)
	object, ok := result[key]
	if !ok {
		return nil, errAnalyticsAuthority
	}
	physicalFields := make(map[string]searchFieldSpec, len(object.Fields))
	for _, field := range object.Fields {
		physicalFields[strings.ToLower(field.APIName)] = field
	}
	for _, column := range source.Columns {
		// Salesforce's temporary virtual report sources describe their projected
		// values as strings even when one cell (StageDuration) carries a JSON
		// number. Keep that L1 wire fact separate from the physical parent field's
		// Catalog type.
		field := searchFieldSpec{APIName: column.Name, Label: column.Label, Type: "string"}
		if column.ParentField != "" {
			if _, exists := physicalFields[strings.ToLower(column.ParentField)]; !exists {
				return nil, errAnalyticsAuthority
			}
		}
		replaced := false
		for index := range object.Fields {
			if strings.EqualFold(object.Fields[index].APIName, column.Name) {
				object.Fields[index] = field
				replaced = true
				break
			}
		}
		if !replaced {
			object.Fields = append(object.Fields, field)
		}
	}
	result[key] = object
	return result, nil
}

func historyReportNeedsColumn(definition temporaryReportDefinition, name string) bool {
	for _, column := range definition.DetailColumns {
		if strings.EqualFold(column, name) {
			return true
		}
	}
	for _, grouping := range definition.GroupingsDown {
		if strings.EqualFold(grouping.Name, name) {
			return true
		}
	}
	return false
}

func appendUniqueFold(values []string, value string) []string {
	for _, existing := range values {
		if strings.EqualFold(existing, value) {
			return values
		}
	}
	return append(values, value)
}

func historyReportValue(value *string) any {
	if value == nil {
		return nil
	}
	return *value
}

func historyReportStringValue(value any) any {
	if value == nil {
		return nil
	}
	return analyticsFormat(value)
}

func opportunityStageDuration(current, previous string) (any, error) {
	if previous == "" {
		return nil, nil
	}
	currentTime, err := time.Parse(time.RFC3339Nano, current)
	if err != nil {
		return nil, err
	}
	previousTime, err := time.Parse(time.RFC3339Nano, previous)
	if err != nil || currentTime.Before(previousTime) {
		return nil, fmt.Errorf("invalid opportunity history duration")
	}
	return currentTime.Sub(previousTime).Hours() / 24, nil
}