package analytics

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

	"github.com/nexuscrm/backend/internal/domain/ports"
	"github.com/nexuscrm/backend/internal/features/metadata"
	"github.com/nexuscrm/backend/internal/features/permissions"
	"github.com/nexuscrm/backend/internal/infrastructure/persistence"
	pkgErrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/shared/pkg/api"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	zlog "github.com/rs/zerolog/log"
	"golang.org/x/sync/errgroup"
)

// QueryService handles all query operations
type QueryService struct {
	repo        ports.QueryRepository
	metadata    *metadata.MetadataService
	permissions *permissions.PermissionService
	validator   *SecurityValidator
	logger      *logging.Logger
}

// NewQueryService creates a new QueryService
func NewQueryService(
	repo ports.QueryRepository,
	metadata *metadata.MetadataService,
	permissions *permissions.PermissionService,
	logger *logging.Logger,
) *QueryService {
	return &QueryService{
		repo:        repo,
		metadata:    metadata,
		permissions: permissions,
		validator:   NewSecurityValidator(permissions, metadata),
		logger:      logger,
	}
}

func scopedReadBypassesUserAccess(ctx context.Context, objectName string) bool {
	return constants.GetSecurityContext(ctx) >= constants.SystemContext ||
		constants.IsScopedInternalAccess(ctx, objectName, constants.PermRead)
}

func (qs *QueryService) checkObjectReadAccess(ctx context.Context, objectName string, currentUser *models.UserSession) error {
	if scopedReadBypassesUserAccess(ctx, objectName) {
		if qs.permissions.IsBlockedObject(ctx, objectName, constants.PermRead) {
			return pkgErrors.NewPermissionError("read", objectName)
		}
		return nil
	}
	if !qs.permissions.CheckObjectPermissionWithUser(ctx, objectName, constants.PermRead, currentUser) {
		return pkgErrors.NewPermissionError("read", objectName)
	}
	return nil
}

func (qs *QueryService) readRLSResult(ctx context.Context, schema *models.ObjectMetadata, objectName string, currentUser *models.UserSession) *permissions.RLSResult {
	if scopedReadBypassesUserAccess(ctx, objectName) {
		return nil
	}
	return qs.permissions.BuildRLSResult(ctx, schema, objectName, currentUser)
}

// Query executes a query based on a QueryRequest
func (qs *QueryService) Query(
	ctx context.Context,
	req api.QueryRequest,
	currentUser *models.UserSession,
) (*models.QueryResult, error) {
	reqID := requestIDFromCtx(ctx)
	zlog.Info().Str("request_id", reqID).Str("object", req.ObjectAPIName).Str("user", queryUserID(currentUser)).Msg("QueryService.Query")

	// Check permissions. SystemContext and metadata-owned standard-action read
	// scopes bypass user read/RLS while still enforcing blocked-object policy.
	if err := qs.checkObjectReadAccess(ctx, req.ObjectAPIName, currentUser); err != nil {
		return nil, err
	}

	schema := qs.metadata.GetSchema(ctx, req.ObjectAPIName)
	if schema == nil {
		return nil, pkgErrors.NewNotFoundError("Object", req.ObjectAPIName)
	}

	// SystemContext and scoped internal read bypass RLS and user FLS.
	bypassUserVisibility := scopedReadBypassesUserAccess(ctx, req.ObjectAPIName)
	rlsResult := qs.readRLSResult(ctx, schema, req.ObjectAPIName, currentUser)

	// Build visible field list. This is the set of columns the repository
	// will SELECT from the physical storage table.
	//
	// PersistableFields filters out schema-level virtuals (IsVirtual=true),
	// which is what keeps SystemPermission projections (Permissions<X>)
	// out of raw SELECT statements — the refactor's main concern. It does
	// NOT filter formula / rollup fields — those use their own type
	// gating downstream (formula_view_service.go routes them through TiDB
	// views; getStorageColumns in bulk_insert.go adds an explicit
	// FieldTypeFormula / RollupSummary skip). The composition is:
	//   PersistableFields  — excludes decorator-emitted virtuals
	//   + formula/rollup type checks where needed — excludes computed types
	// For the query SELECT list, virtuals are materialized post-fetch by
	// projectPermissionSetGrantFields / hydrateVirtualFields.
	visibleFields := qs.metadata.GetSystemFields(ctx, req.ObjectAPIName)
	persistable := models.PersistableFields(schema)
	fieldSet := make(map[string]bool, len(persistable))
	for _, field := range persistable {
		fieldSet[field.APIName] = true
	}

	// Add custom fields that are visible — SystemContext sees all fields
	// EXCEPT security-sensitive ones (ConsumerSecret, Token, Password, etc.)
	for _, field := range persistable {
		isSystem := field.IsSystem || field.IsNameField
		if !isSystem && (bypassUserVisibility && !permissions.IsSecuritySensitiveField(req.ObjectAPIName, field.APIName) || !bypassUserVisibility && qs.permissions.CheckFieldVisibilityWithUser(ctx, req.ObjectAPIName, field.APIName, currentUser)) {
			visibleFields = append(visibleFields, field.APIName)
			if field.IsPolymorphic {
				typeCol := persistence.GetPolymorphicTypeColumnName(field.APIName)
				if fieldSet[typeCol] {
					visibleFields = append(visibleFields, typeCol)
				}
			}
		}
	}

	// Canonicalize SelectFields to match schema PascalCase (SOQL is case-insensitive)
	canonicalizedFields := canonicalizeFieldNames(schema, req.SelectFields)

	// Convert API DTO to Domain Model for Repository
	// Detect xMruAccessedAt sentinel sort field — resolve to MRU JOIN ordering
	sortField := req.SortField
	sortDirection := req.SortDirection
	var mruUserID string
	if sortField == "xMruAccessedAt" && currentUser != nil {
		mruUserID = currentUser.ID
		sortField = ""
		sortDirection = ""
	}

	domainReq := models.QueryRequest{
		ObjectAPIName: req.ObjectAPIName,
		FilterExpr:    req.FilterExpr,
		SortField:     sortField,
		SortDirection: sortDirection,
		Limit:         req.Limit,
		Offset:        req.Offset,
		ScanAll:       req.ScanAll,
		SelectFields:  canonicalizedFields,
		MRUUserID:     mruUserID,
	}
	// Map Criteria
	if req.Criteria != nil {
		domainReq.Criteria = make([]models.QueryCriterion, len(req.Criteria))
		for i, c := range req.Criteria {
			domainReq.Criteria[i] = models.QueryCriterion{
				Field: c.Field,
				Op:    c.Op,
				Val:   c.Val,
			}
		}
	}
	// Map OrderBy
	if req.OrderBy != nil {
		domainReq.OrderBy = make([]models.SortCriterion, len(req.OrderBy))
		for i, o := range req.OrderBy {
			domainReq.OrderBy[i] = models.SortCriterion{
				Field:     o.Field,
				Direction: o.Direction,
			}
		}
	}

	// CHECK: Is this an Analytics/Aggregate Query?
	isAgg, analyticsQuery, err := qs.buildAnalyticsQuery(req)
	if err != nil {
		return nil, err
	}
	if isAgg {
		// Execute Analytics Query
		result, err := qs.RunAnalytics(ctx, *analyticsQuery, currentUser)
		if err != nil {
			return nil, err
		}

		// Transform Result to Standard QueryResult
		aggRecords := make([]models.SObject, 0)

		if rows, ok := result.([]models.SObject); ok {
			// GROUP BY Results
			aggRecords = make([]models.SObject, len(rows))

			groupByField := ""
			if analyticsQuery.GroupBy != nil {
				groupByField = *analyticsQuery.GroupBy
			}

			for i, row := range rows {
				newRow := models.SObject{}

				// Map "val" → "expr0" (standard SFDC aggregate alias)
				if v, ok := row["val"]; ok {
					newRow["expr0"] = v
				}

				// Map "name" → GroupBy field name
				// RunAnalytics hydrates lookup IDs to names automatically.
				if v, ok := row["name"]; ok {
					if groupByField != "" {
						newRow[groupByField] = v
					}
				}

				aggRecords[i] = newRow
			}
		} else {
			// Single Scalar Result (e.g. COUNT())
			// Wrap in a single record with "expr0" key (standard SFDC behavior for unnamed aggregates)
			aggRecords = append(aggRecords, models.SObject{
				"expr0": result,
			})
		}

		return &models.QueryResult{
			TotalSize: len(aggRecords),
			Done:      true,
			Records:   aggRecords,
		}, nil
	}

	queryFields := buildQueryFields(schema, domainReq, visibleFields)

	// Normal Query: Delegate to Repository
	queryResult, err := qs.repo.Find(ctx, schema, domainReq, rlsResult, queryFields)
	if err != nil {
		return nil, err
	}

	// Hydrate virtual fields (formulas, booleans)
	queryResult.Records = qs.hydrateVirtualFields(ctx, queryResult.Records, schema, queryFields, currentUser)

	// Virtual-field projection from backing stores into returned records.
	// Driven by the decorated schema's virtual-field declarations, not a
	// PermissionSet-specific branch: the generic engine checks whether the
	// object's schema declares any virtual fields of a handled kind and
	// only then invokes the matching projector.
	//
	// The gate fires when either (a) the caller selected * (no SelectFields)
	// or (b) the caller explicitly requested a name that resolves to a
	// virtual field on this schema. Case-insensitive resolution piggy-backs
	// on FieldsMap so lowercase / mixed-case inputs work uniformly.
	if qs.permissions != nil {
		systemPermissionVirtuals := models.VirtualFieldsByKind(schema, constants.VirtualFieldSystemPermission)
		if len(systemPermissionVirtuals) > 0 {
			projectFields := systemPermissionVirtuals
			if len(req.SelectFields) > 0 {
				projectFields = make([]models.FieldMetadata, 0)
				added := make(map[string]bool)
				for _, requested := range req.SelectFields {
					f := models.FindField(schema, requested)
					if f != nil && f.IsVirtual && f.VirtualKind == constants.VirtualFieldSystemPermission {
						key := strings.ToLower(f.APIName)
						if !added[key] {
							projectFields = append(projectFields, *f)
							added[key] = true
						}
					}
				}
			}
			if len(projectFields) > 0 {
				if err := qs.projectPermissionSetGrantFields(ctx, queryResult.Records, projectFields); err != nil {
					return nil, err
				}
			}
		}
	}

	// Execute subqueries for child relationships
	if len(req.SubQueries) > 0 {
		domainSubQueries := make([]models.SubQueryRequest, len(req.SubQueries))
		for i, sq := range req.SubQueries {
			domainSubQueries[i] = models.SubQueryRequest{
				RelationshipName: sq.RelationshipName,
				SelectFields:     sq.SelectFields,
				FilterExpr:       sq.FilterExpr,
				Limit:            sq.Limit,
			}
			if len(sq.OrderBy) > 0 {
				domainSubQueries[i].OrderBy = make([]models.SortCriterion, len(sq.OrderBy))
				for j, o := range sq.OrderBy {
					domainSubQueries[i].OrderBy[j] = models.SortCriterion{Field: o.Field, Direction: o.Direction}
				}
			}
		}
		queryResult.Records = qs.executeSubQueries(ctx, queryResult.Records, schema, domainSubQueries, currentUser)
	}

	return queryResult, nil
}

// executeSubQueries resolves child relationship subqueries and attaches results to parent records.
// For each subquery, it finds the child object and FK field by scanning all schemas for reference
// fields that point to the parent object and whose RelationshipName matches.
func (qs *QueryService) executeSubQueries(
	ctx context.Context,
	parentRecords []models.SObject,
	parentSchema *models.ObjectMetadata,
	subQueries []models.SubQueryRequest,
	currentUser *models.UserSession,
) []models.SObject {
	if len(parentRecords) == 0 || len(subQueries) == 0 {
		return parentRecords
	}

	// Collect parent IDs
	parentIDs := make([]string, 0, len(parentRecords))
	parentIDSet := make(map[string]int, len(parentRecords)) // id -> index
	for i, rec := range parentRecords {
		if id, ok := rec[constants.FieldID].(string); ok && id != "" {
			parentIDs = append(parentIDs, id)
			parentIDSet[id] = i
		}
	}
	if len(parentIDs) == 0 {
		return parentRecords
	}

	allSchemas := qs.metadata.GetSchemas(ctx)

	for _, sq := range subQueries {
		// Resolve relationship name to child object + FK field
		childObj, fkField := qs.resolveChildRelationship(allSchemas, parentSchema.APIName, sq.RelationshipName)
		if childObj == "" || fkField == "" {
			qs.logger.Warn(fmt.Sprintf("Could not resolve child relationship %q on %s", sq.RelationshipName, parentSchema.APIName))
			continue
		}

		// Check permissions on child object. Scoped internal read uses the same
		// generic helper as the top-level query path.
		if err := qs.checkObjectReadAccess(ctx, childObj, currentUser); err != nil {
			continue
		}

		childSchema := qs.metadata.GetSchema(ctx, childObj)
		if childSchema == nil {
			continue
		}

		// Build a filter expression: fkField IN ('id1', 'id2', ...)
		var idLiterals []string
		for _, id := range parentIDs {
			idLiterals = append(idLiterals, fmt.Sprintf("'%s'", strings.ReplaceAll(id, "'", "\\'")))
		}
		fkFilter := fmt.Sprintf("%s in [%s]", fkField, strings.Join(idLiterals, ", "))
		combinedFilter := fkFilter
		if sq.FilterExpr != "" {
			combinedFilter = fmt.Sprintf("(%s) and (%s)", fkFilter, sq.FilterExpr)
		}

		childReq := models.QueryRequest{
			ObjectAPIName: childObj,
			FilterExpr:    combinedFilter,
			SelectFields:  sq.SelectFields,
			OrderBy:       sq.OrderBy,
		}
		// Propagate limit to bound the child record fetch.
		// Apply an absolute ceiling of 10 000 to prevent runaway queries
		// when the parent set is large (e.g. 2 000 parents × 200 = 400 000).
		const maxChildLimit = 10000
		if sq.Limit > 0 {
			childReq.Limit = sq.Limit * len(parentIDs)
		} else {
			// Default safety cap: 200 child records per parent
			childReq.Limit = 200 * len(parentIDs)
		}
		if childReq.Limit > maxChildLimit {
			childReq.Limit = maxChildLimit
		}
		// Ensure the FK field and Id are selected so we can group results
		hasFk := false
		hasId := false
		for _, f := range childReq.SelectFields {
			if strings.EqualFold(f, fkField) {
				hasFk = true
			}
			if strings.EqualFold(f, constants.FieldID) {
				hasId = true
			}
		}
		if !hasFk {
			childReq.SelectFields = append(childReq.SelectFields, fkField)
		}
		if !hasId {
			childReq.SelectFields = append([]string{constants.FieldID}, childReq.SelectFields...)
		}

		// Build visible fields for the child query
		childVisibleFields := qs.metadata.GetSystemFields(ctx, childObj)
		bypassChildUserVisibility := scopedReadBypassesUserAccess(ctx, childObj)
		for _, field := range childSchema.Fields {
			isSystem := field.IsSystem || field.IsNameField
			if !isSystem && (bypassChildUserVisibility && !permissions.IsSecuritySensitiveField(childObj, field.APIName) || !bypassChildUserVisibility && qs.permissions.CheckFieldVisibilityWithUser(ctx, childObj, field.APIName, currentUser)) {
				childVisibleFields = append(childVisibleFields, field.APIName)
			}
		}

		childRLSResult := qs.readRLSResult(ctx, childSchema, childObj, currentUser)

		queryFields := buildQueryFields(childSchema, childReq, childVisibleFields)

		childResult, err := qs.repo.Find(ctx, childSchema, childReq, childRLSResult, queryFields)
		if err != nil {
			qs.logger.Warn(fmt.Sprintf("Subquery execution failed for %s: %v", sq.RelationshipName, err))
			continue
		}

		// Group child records by parent ID
		grouped := make(map[string][]models.SObject)
		for _, childRec := range childResult.Records {
			if parentID, ok := childRec[fkField].(string); ok && parentID != "" {
				grouped[parentID] = append(grouped[parentID], childRec)
			}
		}

		// Apply per-parent limit if specified
		if sq.Limit > 0 {
			for pid, recs := range grouped {
				if len(recs) > sq.Limit {
					grouped[pid] = recs[:sq.Limit]
				}
			}
		}

		// Attach to parent records
		for i, rec := range parentRecords {
			parentID, ok := rec[constants.FieldID].(string)
			if !ok || parentID == "" {
				continue
			}
			childRecs := grouped[parentID]
			if childRecs == nil {
				childRecs = []models.SObject{}
			}
			parentRecords[i][sq.RelationshipName] = map[string]interface{}{
				"totalSize": len(childRecs),
				"done":      true,
				"records":   childRecs,
			}
		}
	}

	return parentRecords
}

// resolveChildRelationship scans all schemas to find a child object with a reference field
// pointing to parentObject whose RelationshipName matches the given name.
func (qs *QueryService) resolveChildRelationship(
	allSchemas []*models.ObjectMetadata,
	parentObject string,
	relationshipName string,
) (childObjectAPIName string, fkFieldAPIName string) {
	for _, schema := range allSchemas {
		for _, field := range schema.Fields {
			if !strings.EqualFold(string(field.Type), string(constants.FieldTypeReference)) {
				continue
			}
			// Check if this reference field points to the parent object
			pointsToParent := false
			for _, ref := range field.ReferenceTo {
				if strings.EqualFold(ref, parentObject) {
					pointsToParent = true
					break
				}
			}
			if !pointsToParent {
				continue
			}
			// Check if the relationship name matches
			if field.RelationshipName != nil && strings.EqualFold(*field.RelationshipName, relationshipName) {
				return schema.APIName, field.APIName
			}
		}
	}
	return "", ""
}

// QueryWithFilter executes a query with a formula expression filter
func (qs *QueryService) QueryWithFilter(
	ctx context.Context,
	objectName string,
	filterExpr string,
	currentUser *models.UserSession,
	orderBy string,
	orderDirection string,
	limit int,
) (*models.QueryResult, error) {
	req := api.QueryRequest{
		ObjectAPIName: objectName,
		FilterExpr:    filterExpr,
		SortField:     orderBy,
		SortDirection: orderDirection,
		Limit:         limit,
	}
	return qs.Query(ctx, req, currentUser)
}

// SearchSingleObject searches a specific object with filtering and pagination
func (qs *QueryService) SearchSingleObject(
	ctx context.Context,
	objectName string,
	term string,
	searchFields []string,
	returnFields []string,
	limit int,
	offset int,
	orderBy string,
	orderDir string,
	filterExpr string,
	currentUser *models.UserSession,
) ([]models.SObject, error) {
	if err := qs.checkObjectReadAccess(ctx, objectName, currentUser); err != nil {
		return []models.SObject{}, nil
	}

	schema := qs.metadata.GetSchema(ctx, objectName)
	if schema == nil {
		return nil, pkgErrors.NewNotFoundError("Object", objectName)
	}

	// 1. Determine Search Fields (Fields to check against 'term')
	// 1. Strict Search Fields: We rely on the caller (Handler) to provide fields (expanded from scope).
	// If none provided, we search nothing.
	finalSearchFields := searchFields

	if len(finalSearchFields) == 0 {
		return []models.SObject{}, nil
	}

	// 2. Determine Select Fields (Fields to return)
	fieldsToSelect := returnFields
	if len(fieldsToSelect) == 0 {
		fieldsToSelect = []string{"Id"}

		var nameField string
		for _, f := range schema.Fields {
			if f.IsNameField {
				nameField = f.APIName
				break
			}
		}

		if nameField != "" {
			fieldsToSelect = append(fieldsToSelect, nameField)
		}
	} else {
		// Ensure Id is always selected
		hasId := false
		for _, f := range fieldsToSelect {
			if strings.EqualFold(f, constants.FieldID) {
				hasId = true
				break
			}
		}
		if !hasId {
			fieldsToSelect = append([]string{constants.FieldID}, fieldsToSelect...)
		}
	}
	fieldsToSelect = expandCompoundReturnFields(schema, fieldsToSelect)

	// Apply field-level read permissions to both the search field list and
	// the SELECT projection: compound-name expansion adds member fields
	// (e.g. Salutation/FirstName/LastName) from metadata. FieldPermissions
	// is the SF-modeled SSoT for read access, and `Query()` already routes
	// reads through `CheckFieldVisibilityWithUser` for the same fact (Ian
	// P0 on PR #1963 / issue #1455). SystemContext / scoped internal reads
	// bypass user FLS the same way `Query()` does.
	bypassUserVisibility := scopedReadBypassesUserAccess(ctx, objectName)
	if !bypassUserVisibility {
		finalSearchFields = qs.filterFieldsByVisibility(ctx, objectName, finalSearchFields, currentUser)
		fieldsToSelect = qs.filterFieldsByVisibility(ctx, objectName, fieldsToSelect, currentUser)
		if len(finalSearchFields) == 0 {
			return []models.SObject{}, nil
		}
	}

	if limit <= 0 {
		limit = 500
	}

	rlsResult := qs.readRLSResult(ctx, schema, objectName, currentUser)

	// Delegate to Repository
	results, err := qs.repo.Search(ctx, schema, term, finalSearchFields, fieldsToSelect, limit, offset, orderBy, orderDir, filterExpr, rlsResult)
	if err != nil {
		return []models.SObject{}, err
	}

	return results, nil
}

// filterFieldsByVisibility drops fields the current user cannot read per
// FieldPermissions. Used after compound-name expansion to ensure admin-hidden
// member fields (e.g. FirstName/LastName when the compound `Name` is
// permitted) are not searched or returned. SystemContext / scoped internal
// reads should call this only when intended; `SearchSingleObject`'s caller
// gates the call behind `scopedReadBypassesUserAccess`.
func (qs *QueryService) filterFieldsByVisibility(ctx context.Context, objectAPIName string, fields []string, currentUser *models.UserSession) []string {
	if qs.permissions == nil || len(fields) == 0 {
		return fields
	}
	out := fields[:0]
	for _, f := range fields {
		if qs.permissions.CheckFieldVisibilityWithUser(ctx, objectAPIName, f, currentUser) {
			out = append(out, f)
		}
	}
	return out
}

func expandCompoundReturnFields(schema *models.ObjectMetadata, fields []string) []string {
	if schema == nil || len(fields) == 0 {
		return fields
	}

	expanded := make([]string, 0, len(fields))
	seen := make(map[string]struct{}, len(fields))
	add := func(apiName string) {
		if apiName == "" {
			return
		}
		if _, ok := seen[apiName]; ok {
			return
		}
		seen[apiName] = struct{}{}
		expanded = append(expanded, apiName)
	}

	for _, field := range fields {
		add(field)
		// Single SSoT for compound-shape expansion (Ian R10/R11 P0 directive
		// on PR #1963 / issue #1455). The helper reads
		// `fieldprovenance.ClassifyCompound` first, falls back to the
		// metadata-driven `packspec.CompoundFieldComponents`, and filters
		// to schema-existing members. Replaces the prior FirstName/LastName
		// hardcoded fallback.
		isCompound, members := metadata.ExpandCompoundFieldMembers(schema, field)
		if !isCompound {
			continue
		}
		for _, member := range members {
			add(member)
		}
	}

	return expanded
}

// GlobalSearch searches across all objects (Parallel Execution)
func (qs *QueryService) GlobalSearch(ctx context.Context, term string, currentUser *models.UserSession) ([]models.SearchResult, error) {
	schemas := qs.metadata.GetSchemas(ctx)
	results := make([]models.SearchResult, 0)
	var mu sync.Mutex

	// Filter searchable schemas first
	var searchableSchemas []*models.ObjectMetadata
	for _, schema := range schemas {
		if metadata.IsUserSearchableObject(schema) {
			searchableSchemas = append(searchableSchemas, schema)
		}
	}

	// Use errgroup for bounded concurrency
	g, ctx := errgroup.WithContext(ctx)
	g.SetLimit(10)

	for _, schema := range searchableSchemas {
		s := schema // capture loop variable
		g.Go(func() error {
			// Check read permission, including scoped standard-action access.
			if err := qs.checkObjectReadAccess(ctx, s.APIName, currentUser); err != nil {
				return nil
			}

			// Push down limit: Global search only shows top 5 per object
			matches, err := qs.SearchSingleObject(ctx, s.APIName, term, nil, nil, 5, 0, "", "", "", currentUser)
			if err != nil {
				qs.logger.Warn(fmt.Sprintf("Search failed for %s: %v", s.APIName, err))
				return nil
			}

			if len(matches) > 0 {
				mu.Lock()
				results = append(results, models.SearchResult{
					ObjectAPIName: s.APIName,
					ObjectLabel:   s.PluralLabel,
					Icon:          s.Icon,
					Matches:       matches,
				})
				mu.Unlock()
			}
			return nil
		})
	}

	if err := g.Wait(); err != nil {
		return nil, err
	}

	return results, nil
}

// RunAnalytics executes an analytics query
func (qs *QueryService) RunAnalytics(ctx context.Context, analyticsQuery models.AnalyticsQuery, currentUser *models.UserSession) (interface{}, error) {
	objectName := analyticsQuery.ObjectAPIName
	bypassUserVisibility := scopedReadBypassesUserAccess(ctx, objectName)

	if err := qs.checkObjectReadAccess(ctx, objectName, currentUser); err != nil {
		return nil, err
	}

	schema := qs.metadata.GetSchema(ctx, objectName)
	if schema == nil {
		return nil, pkgErrors.NewNotFoundError("Object", objectName)
	}

	// FLS Check: Ensure user has visibility to the aggregated field
	if analyticsQuery.Field != nil {
		if bypassUserVisibility {
			if permissions.IsSecuritySensitiveField(objectName, *analyticsQuery.Field) {
				return nil, pkgErrors.NewPermissionError("read field "+*analyticsQuery.Field+" on", objectName)
			}
		} else if !qs.permissions.CheckFieldVisibilityWithUser(ctx, objectName, *analyticsQuery.Field, currentUser) {
			return nil, pkgErrors.NewPermissionError("read field "+*analyticsQuery.Field+" on", objectName)
		}
	}
	// FLS Check: Ensure user has visibility to the group by field
	if analyticsQuery.GroupBy != nil {
		if bypassUserVisibility {
			if permissions.IsSecuritySensitiveField(objectName, *analyticsQuery.GroupBy) {
				return nil, pkgErrors.NewPermissionError("read field "+*analyticsQuery.GroupBy+" on", objectName)
			}
		} else if !qs.permissions.CheckFieldVisibilityWithUser(ctx, objectName, *analyticsQuery.GroupBy, currentUser) {
			return nil, pkgErrors.NewPermissionError("read field "+*analyticsQuery.GroupBy+" on", objectName)
		}
	}

	rlsResult := qs.readRLSResult(ctx, schema, objectName, currentUser)

	// Delegate to Repository (Pass Schema for Alias Resolution)
	val, err := qs.repo.RunAnalytics(ctx, schema, analyticsQuery, rlsResult)
	if err != nil {
		return nil, err
	}

	if analyticsQuery.Operation == constants.AnalyticsOperationGroupBy {
		results, ok := val.([]models.SObject)
		if !ok {
			return val, nil
		}

		// Check if group_by field is a Lookup - if so, hydrate names
		if analyticsQuery.GroupBy != nil {
			groupByField := *analyticsQuery.GroupBy
			results = qs.hydrateAnalyticsGroupNames(ctx, results, schema, groupByField)
		}

		return results, nil
	}

	// val is scalar (float64, int64, etc)
	return val, nil
}

// hydrateAnalyticsGroupNames resolves lookup field IDs to human-readable names for analytics group_by results
func (qs *QueryService) hydrateAnalyticsGroupNames(ctx context.Context, results []models.SObject, schema *models.ObjectMetadata, groupByField string) []models.SObject {
	// Find the field definition
	var fieldDef *models.FieldMetadata
	for i := range schema.Fields {
		if strings.EqualFold(schema.Fields[i].APIName, groupByField) {
			fieldDef = &schema.Fields[i]
			break
		}
	}

	// Check if it's a lookup field - MUST check nil first
	if fieldDef == nil {
		return results
	}

	isLookup := strings.EqualFold(string(fieldDef.Type), string(constants.FieldTypeReference))
	if !isLookup {
		return results
	}

	// Get the referenced object from metadata
	var refObject string
	if len(fieldDef.ReferenceTo) > 0 {
		refObject = fieldDef.ReferenceTo[0]
	} else {
		// Strict Mode: If ReferenceTo is missing on a Lookup field, we cannot hydrate.
		// We do NOT guess based on field name anymore.
		return results
	}

	// Collect unique IDs from results
	ids := make([]string, 0, len(results))
	for _, r := range results {
		if name, ok := r["name"]; ok && name != nil {
			idStr := fmt.Sprintf("%v", name)
			if idStr != "" {
				ids = append(ids, idStr)
			}
		}
	}

	if len(ids) == 0 {
		return results
	}

	// Get the schema for the referenced object to find name and ID fields
	refSchema := qs.metadata.GetSchema(ctx, refObject)
	if refSchema == nil {
		return results
	}

	// Determine ID field from metadata (not hardcoded)
	idField := getLookupIdField(refSchema)

	// Find name field from metadata
	nameField := constants.FieldName
	for _, f := range refSchema.Fields {
		if f.IsNameField {
			nameField = f.APIName
			break
		}
	}

	// Fetch names using GetLookupNames with metadata-driven ID field
	lookupResults, err := qs.repo.GetLookupNames(ctx, refSchema, ids, idField, nameField)
	if err != nil {
		return results // Fallback to original on error
	}

	// Build ID -> Name map using the metadata-driven ID field
	nameMap := make(map[string]string)
	for _, lr := range lookupResults {
		id := fmt.Sprintf("%v", lr[idField])
		name := fmt.Sprintf("%v", lr[nameField])
		if id != "" && id != "<nil>" && name != "" && name != "<nil>" {
			nameMap[id] = name
		}
	}

	// Replace IDs with names
	for i := range results {
		if name, ok := results[i]["name"]; ok && name != nil {
			idStr := fmt.Sprintf("%v", name)
			if resolvedName, found := nameMap[idStr]; found {
				results[i]["name"] = resolvedName
			}
		}
	}

	return results
}

// requestIDFromCtx extracts the request ID from context for log correlation.
func requestIDFromCtx(ctx context.Context) string {
	if id, ok := ctx.Value(constants.ContextKeyRequestID).(string); ok {
		return id
	}
	return ""
}

// queryUserID safely extracts user ID for logging.
func queryUserID(user *models.UserSession) string {
	if user == nil {
		return "system"
	}
	return user.ID
}

// canonicalizeFieldNames maps SOQL field names (case-insensitive) to their
// canonical PascalCase form from the object schema. Unrecognized names pass through.
func canonicalizeFieldNames(schema *models.ObjectMetadata, fields []string) []string {
	if len(fields) == 0 || schema == nil {
		return fields
	}
	lookup := make(map[string]string, len(schema.Fields))
	for _, f := range schema.Fields {
		lookup[strings.ToLower(f.APIName)] = f.APIName
	}
	out := make([]string, len(fields))
	for i, f := range fields {
		if canonical, ok := lookup[strings.ToLower(f)]; ok {
			out[i] = canonical
		} else {
			out[i] = f
		}
	}
	return out
}

// ExecuteRawSQL executes a raw SQL query with parameters (Validated and Secured)
func (qs *QueryService) ExecuteRawSQL(ctx context.Context, sql string, params []interface{}, user *models.UserSession) ([]models.SObject, error) {
	// Validate and Rewrite SQL (RLS/FLS)
	// This ensures users can only see what they are allowed to see.
	// Note: Raw SQL from QueryService does not use context variables, so pass nil.
	safeSQL, safeParams, err := qs.validator.ValidateAndRewrite(ctx, sql, params, user, nil)
	if err != nil {
		return nil, pkgErrors.NewInternalError("security validation failed", err)
	}

	// Delegate to Repository
	return qs.repo.ExecuteRawSQL(ctx, safeSQL, safeParams)
}

// projectPermissionSetGrantFields hydrates selected SystemPermission virtual
// fields on PermissionSet rows from the _System_PermissionSetPermission
// junction. Uses a single batched grants-lookup — O(N rows + 1 query).
func (qs *QueryService) projectPermissionSetGrantFields(ctx context.Context, records []models.SObject, fields []models.FieldMetadata) error {
	if len(records) == 0 {
		return nil
	}
	if len(fields) == 0 {
		return nil
	}
	ids := make([]string, 0, len(records))
	for _, r := range records {
		if id, ok := r[constants.FieldID].(string); ok && id != "" {
			ids = append(ids, id)
		}
	}
	grantsByID, err := qs.permissions.GetPermissionSetGrantsMany(ctx, ids)
	if err != nil {
		return err
	}
	for _, r := range records {
		id, _ := r[constants.FieldID].(string)
		permissions.ProjectPermissionGrantFieldsOntoRecord(r, fields, grantsByID[id])
	}
	return nil
}