package analytics

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"regexp"
	"strconv"
	"strings"
	"time"

	"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"
)

// ReportService manages report execution
type ReportService struct {
	queryService ports.QueryService
	metadata     *metadata.MetadataService
	permissions  *permissions.PermissionService // may be nil in tests
	logger       *logging.Logger
	// crtResolver is the CRT dispatch hook installed by the service wiring
	// layer. Nil in unit tests that do not exercise the CRT path — the
	// executeReport dispatcher short-circuits when it is nil, preserving
	// pre-CRT behavior.
	crtResolver CRTResolver
	// standardObjs auto-routes known object names before the legacy
	// resolveSourceObject string-trim heuristic; may be nil in tests.
	standardObjs *StandardObjectResolver
	// rawDB is a narrow *sql.DB handle used for reading Report columns that
	// are not registered in the metadata registry (e.g. BooleanFilter).
	// Nil in tests — the BooleanFilter path degrades silently.
	rawDB ReportRawDB
}

// ReportServiceOption is the functional-option type for NewReportService.
// Adding new dependencies to ReportService goes through this surface so
// every existing caller — especially tests — keeps compiling without
// update.
type ReportServiceOption func(*ReportService)

// WithCRTResolver installs the CRT dispatch hook. See design §4.1.
func WithCRTResolver(r CRTResolver) ReportServiceOption {
	return func(s *ReportService) { s.crtResolver = r }
}

// WithRawDB installs a raw sql.DB handle used for reading Report columns
// that are not registered in the metadata registry (e.g. BooleanFilter).
// Tests that don't need the BooleanFilter path can leave this unset.
func WithRawDB(db ReportRawDB) ReportServiceOption {
	return func(s *ReportService) { s.rawDB = db }
}

// ReportRawDB is the narrow *sql.DB surface the report service uses for
// metadata-bypassing reads. Both *sql.DB and *sql.Tx satisfy it.
type ReportRawDB interface {
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

// WithStandardObjectResolver installs the auto-scanned standard-object
// resolver consulted by (*ReportService).resolveSourceObject. See §4.9.
func WithStandardObjectResolver(std *StandardObjectResolver) ReportServiceOption {
	return func(s *ReportService) { s.standardObjs = std }
}

const defaultReportExecutionLimit = 2000

// NewReportService creates a new ReportService. permSvc may be nil in tests; when
// nil, the "myTeam" scope degrades to "mine" (no role-hierarchy expansion).
// The variadic ReportServiceOption surface is how new dependencies (CRT
// resolver, standard-object resolver) attach without churning the ~30 test
// call sites that construct the service with the historical 4-argument
// form.
func NewReportService(queryService ports.QueryService, metadataSvc *metadata.MetadataService, permSvc *permissions.PermissionService, logger *logging.Logger, opts ...ReportServiceOption) *ReportService {
	s := &ReportService{
		queryService: queryService,
		metadata:     metadataSvc,
		permissions:  permSvc,
		logger:       logger,
	}
	for _, opt := range opts {
		opt(s)
	}
	// MEDIUM-1: surface the nil-resolver configuration so operators can
	// detect an incomplete wiring quickly (stored CRTs will 404 until a
	// resolver is installed via WithCRTResolver).
	if s.crtResolver == nil && s.logger != nil {
		s.logger.Warn("CRT resolver not wired; CRT reports will 404 for stored types")
	}
	return s
}

// RunReport executes a report by ID with optional filters
func (s *ReportService) RunReport(ctx context.Context, reportID string, user *models.UserSession, runtimeFilters map[string]interface{}) (*models.ReportResult, error) {
	def, err := s.loadReportDefinition(ctx, reportID, user)
	if err != nil {
		return nil, err
	}

	return s.RunReportDefinition(ctx, def, user, runtimeFilters, 0)
}

// ExecuteReportDefinition executes a report definition directly without loading it from persistence.
func (s *ReportService) ExecuteReportDefinition(ctx context.Context, def *models.ReportDefinition, user *models.UserSession, runtimeFilters map[string]interface{}) (*models.ReportResult, error) {
	if def == nil {
		return nil, pkgErrors.NewValidationError("reportMetadata", "report definition is required")
	}
	return s.RunReportDefinition(ctx, def, user, runtimeFilters, 0)
}

// GetReportMetadata returns only the report definition without execution
func (s *ReportService) GetReportMetadata(ctx context.Context, reportID string, user *models.UserSession) (*models.ReportDefinition, error) {
	return s.loadReportDefinition(ctx, reportID, user)
}

// RunReportDefinition executes an in-memory report definition without requiring
// a saved Report record.
func (s *ReportService) RunReportDefinition(ctx context.Context, def *models.ReportDefinition, user *models.UserSession, runtimeFilters map[string]interface{}, limit int) (*models.ReportResult, error) {
	if def == nil {
		return nil, pkgErrors.NewInternalError("report definition is required", nil)
	}
	return s.executeReport(ctx, def, user, runtimeFilters, normalizeReportExecutionLimit(limit))
}

func (s *ReportService) loadReportDefinition(ctx context.Context, reportID string, user *models.UserSession) (*models.ReportDefinition, error) {
	result, err := s.queryService.Query(ctx, api.QueryRequest{
		ObjectAPIName: constants.TableReport,
		Criteria:      []api.QueryCriterion{{Field: "Id", Op: "=", Val: reportID}},
		Limit:         1,
	}, user)
	if err != nil {
		return nil, pkgErrors.NewInternalError("failed to load report definition", err)
	}
	if len(result.Records) == 0 {
		return nil, pkgErrors.NewNotFoundError("Report", reportID)
	}

	reportRow := result.Records[0]

	// BooleanFilter is a physical column on Report but is not registered in
	// _System_Field (the metadata registry only carries the 26 fields the
	// bootstrap script seeds). QueryService selects only registered columns,
	// so BooleanFilter never lands in reportRow. Patch it in via a raw read
	// so the filter compiler in the single-table path can see OR semantics.
	// Falls through silently if the supplementary query fails — the report
	// then executes with AND-only filters, matching pre-patch behavior.
	if bf, ok := s.fetchReportBooleanFilter(ctx, reportID); ok {
		reportRow["BooleanFilter"] = bf
	}
	return s.parseReportDefinition(reportRow)
}

// fetchReportBooleanFilter reads the Report.BooleanFilter column directly
// from the injected raw DB handle, bypassing both the metadata registry
// (which does not list BooleanFilter) and the FLS-validating QueryService.
// Returns ("", false) when the column is empty, the row doesn't exist, or
// rawDB wasn't wired — the caller degrades to the original AND-only path.
func (s *ReportService) fetchReportBooleanFilter(ctx context.Context, reportID string) (string, bool) {
	if s.rawDB == nil {
		return "", false
	}
	var bf sql.NullString
	err := s.rawDB.QueryRowContext(ctx,
		"SELECT `BooleanFilter` FROM `Report` WHERE `Id` = ? AND `IsDeleted` = 0 LIMIT 1",
		reportID).Scan(&bf)
	if err != nil || !bf.Valid || bf.String == "" {
		return "", false
	}
	return bf.String, true
}

func normalizeReportExecutionLimit(limit int) int {
	if limit <= 0 || limit > defaultReportExecutionLimit {
		return defaultReportExecutionLimit
	}
	return limit
}

// getJSONBytes extracts a JSON column value from an SObject.
// TiDB/MySQL JSON columns are returned as []byte by the Go driver, but the
// persistence layer may also store them as string. Handle both.
func getJSONBytes(row models.SObject, key string) []byte {
	val, ok := row[key]
	if !ok || val == nil {
		return nil
	}
	switch v := val.(type) {
	case []byte:
		return v
	case string:
		if v == "" || v == "null" {
			return nil
		}
		return []byte(v)
	default:
		// Unexpected type — try JSON marshal as last resort
		b, err := json.Marshal(v)
		if err != nil {
			logging.Default.Warn(fmt.Sprintf("getJSONBytes: json.Marshal failed for key %q (type %T): %v", key, v, err))
			return nil
		}
		return b
	}
}

func (s *ReportService) parseReportDefinition(row models.SObject) (*models.ReportDefinition, error) {
	// Older rows may have stored the format under "Format". Newer rows derive
	// the format from grouping when no dedicated format column exists.
	reportFormat := row.GetString("Format")
	if reportFormat == "" {
		reportFormat = row.GetString("ReportFormat")
	}

	def := &models.ReportDefinition{
		ID:            row.GetString("Id"),
		Name:          row.GetString("Name"),
		SourceObject:  row.GetString("SourceObject"),
		ReportType:    row.GetString("ReportType"),
		ReportFormat:  reportFormat,
		Scope:         row.GetString("Scope"),
		BooleanFilter: row.GetString("BooleanFilter"),
	}

	// JSON-typed columns may arrive as already-parsed []interface{} (from schema_utils)
	// or as raw JSON strings (from plain query). getJSONString handles both.
	if colStr := getJSONString(row, "Columns"); colStr != "" {
		if err := json.Unmarshal([]byte(colStr), &def.Columns); err != nil {
			return nil, pkgErrors.NewInternalError(fmt.Sprintf("failed to parse columns (val=%s)", colStr), err)
		}
	}
	if filterStr := getJSONString(row, "Filters"); filterStr != "" {
		if err := json.Unmarshal([]byte(filterStr), &def.Filters); err != nil {
			// Filters format mismatch is non-fatal — skip
			def.Filters = nil
		}
	}
	if groupStr := getJSONString(row, "Grouping"); groupStr != "" {
		if err := parseGrouping(groupStr, &def.Grouping); err != nil {
			return nil, pkgErrors.NewInternalError(fmt.Sprintf("failed to parse grouping (val=%s)", groupStr), err)
		}
	}
	if aggStr := getJSONString(row, "Aggregates"); aggStr != "" {
		if err := parseAggregates(aggStr, &def.Aggregates); err != nil {
			return nil, pkgErrors.NewInternalError(fmt.Sprintf("failed to parse aggregates (val=%s)", aggStr), err)
		}
	}

	// Parse StandardDateFilter (JSON with column, durationValue, startDate, endDate)
	if sdfStr := getJSONString(row, "StandardDateFilter"); sdfStr != "" {
		var sdf models.StandardDateFilter
		if err := json.Unmarshal([]byte(sdfStr), &sdf); err == nil && sdf.Column != "" {
			def.StandardDateFilter = &sdf
		}
	}
	if def.ReportFormat == "" {
		switch {
		case strings.EqualFold(def.ReportType, "matrix"):
			def.ReportFormat = "MATRIX"
		case strings.EqualFold(def.ReportType, "summary"), len(def.Grouping) > 0:
			def.ReportFormat = "SUMMARY"
		default:
			def.ReportFormat = "TABULAR"
		}
	}

	return def, nil
}

// getJSONString returns a JSON string for the given key from an SObject.
// If the value is already a string, returns it directly.
// If the schema layer already parsed the JSON column into a Go structure
// ([]interface{}, map[string]interface{}, etc.), re-marshals it back to JSON.
func getJSONString(row models.SObject, key string) string {
	val, ok := row[key]
	if !ok || val == nil {
		return ""
	}
	if s, ok := val.(string); ok {
		return s
	}
	b, err := json.Marshal(val)
	if err != nil {
		logging.Default.Warn(fmt.Sprintf("getJSONString: json.Marshal failed for key %q (type %T): %v", key, val, err))
		return ""
	}
	return string(b)
}

// parseGrouping handles both formats stored in DB:
//   - Simple: `["StageName", "Region"]`
//   - Salesforce-imported: `[{"name": "StageName", "dateGranularity": "None", ...}]`
func parseGrouping(raw string, out *[]string) error {
	var simple []string
	if err := json.Unmarshal([]byte(raw), &simple); err == nil {
		*out = simple
		return nil
	}
	var objects []map[string]interface{}
	if err := json.Unmarshal([]byte(raw), &objects); err != nil {
		return err
	}
	names := make([]string, 0, len(objects))
	for _, obj := range objects {
		if n, ok := obj["name"].(string); ok && n != "" {
			names = append(names, n)
		}
	}
	*out = names
	return nil
}

// parseAggregates handles both formats stored in DB:
//   - Salesforce-imported: `["RowCount", "s!Amount"]`
//   - Internal: `[{"field": "Amount", "type": "SUM"}]`
func parseAggregates(raw string, out *[]models.ReportAggregate) error {
	var structured []models.ReportAggregate
	if err := json.Unmarshal([]byte(raw), &structured); err == nil {
		if len(structured) > 0 && (structured[0].Field != "" || structured[0].Type != "") {
			*out = structured
			return nil
		}
	}
	var keys []string
	if err := json.Unmarshal([]byte(raw), &keys); err != nil {
		return err
	}
	aggs := make([]models.ReportAggregate, len(keys))
	for i, k := range keys {
		if strings.HasPrefix(k, "s!") {
			aggs[i] = models.ReportAggregate{Field: k[2:], Type: "SUM"}
		} else if strings.HasPrefix(k, "a!") {
			aggs[i] = models.ReportAggregate{Field: k[2:], Type: "AVG"}
		} else if strings.HasPrefix(k, "mx!") {
			aggs[i] = models.ReportAggregate{Field: k[3:], Type: "MAX"}
		} else if strings.HasPrefix(k, "mn!") {
			aggs[i] = models.ReportAggregate{Field: k[3:], Type: "MIN"}
		} else {
			aggs[i] = models.ReportAggregate{Field: "", Type: k}
		}
	}
	*out = aggs
	return nil
}

// SFColumnToField maps Salesforce Analytics standard column references to SObject field names.
var SFColumnToField = map[string]string{
	"ACCOUNT_NAME":      "AccountId",
	"OPPORTUNITY_NAME":  "Name",
	"CONTACT_NAME":      "Name",
	"CASE_NUMBER":       "CaseNumber",
	"CUST_NAME":         "Name",
	"FULL_NAME":         "Name",
	"FIRST_NAME":        "FirstName",
	"LAST_NAME":         "LastName",
	"PROBABILITY":       "Probability",
	"AMOUNT":            "Amount",
	"CLOSE_DATE":        "CloseDate",
	"CREATED_DATE":      "CreatedDate",
	"LAST_UPDATE":       "LastModifiedDate",
	"NEXT_STEP":         "NextStep",
	"LEAD_SOURCE":       "LeadSource",
	"FORECAST_CATEGORY": "ForecastCategoryName",
	"FISCAL_QUARTER":    "FiscalQuarter",
	"FISCAL_YEAR":       "FiscalYear",
	"FISCAL":            "Fiscal",
	"STAGE_NAME":        "StageName",
	"TYPE":              "Type",
	"DESCRIPTION":       "Description",
	"ALIAS":             "Alias",
	"EMAIL":             "Email",
	"PHONE":             "Phone",
	"TITLE":             "Title",
	"COMPANY":           "Company",
	"STATUS":            "Status",
	"SUBJECT":           "Subject",
	"PRIORITY":          "Priority",
	"RATING":            "Rating",
	"INDUSTRY":          "Industry",
	"WEBSITE":           "Website",
	"OWNER":             "OwnerId",
	"RECORD_TYPE":       "RecordTypeId",
	"RECORDTYPE":        "RecordTypeId",
	"DUE_DATE":          "ActivityDate",
	"CAMPAIGN_NAME":     "Name",
	"CAMPAIGN_STATUS":   "Status",
	"START_DATE":        "StartDate",
	"END_DATE":          "EndDate",
	"ORDER_STATUS":      "Status",
	"COMPANY_ACCT":      "Company",
	"OPP_NAME":          "Name",
	"OPP_ACCT_NAME":     "AccountId",
	"CONVERTED_DATE":    "ConvertedDate",
	"LEAD_OWNER":        "OwnerId",
	"LEAD_ID":           "Id",
	"CONTACT_ID":        "ContactId",
	"OPPORTUNITY_ID":    "Id",
	"LEAD_STATUS":       "Status",
	"COUNTRY_CODE":      "CountryCode",
	"COUNTRY":           "Country",
	"STATE_CODE":        "StateCode",
	"STATE":             "State",
	"CITY":              "City",
	"STREET":            "Street",
	"POSTAL_CODE":          "PostalCode",
	"ACCOUNT_OWNER":        "AccountId_OwnerId",
	"AGE":                  "Age__c",
	"LAST_ACTIVITY":        "LastActivityDate",
	"CAMPAIGN_SOURCE":      "CampaignId",
	"ORDER_EFFECTIVE_DATE": "OrderEffectiveDate",
}

// resolveSFColumn maps a Salesforce Analytics column reference to a DB field name.
func resolveSFColumn(col string) string {
	if dot := strings.LastIndex(col, "."); dot >= 0 {
		return col[dot+1:]
	}
	if mapped, ok := SFColumnToField[col]; ok {
		return mapped
	}
	return col
}

// sfReportTypeToObject maps SF cross-object report type identifiers to their primary query object.
var sfReportTypeToObject = map[string]string{
	"OpportunityLead":      "lead",
	"OpportunityCampaign":  "opportunity",
	"OpportunityProduct":   "opportunitylineitem",
	"CampaignContact":      "contact",
	"CampaignLead":         "lead",
	"CampaignLeadLifetime": "lead",
	"ContactActivity":      "contact",
	"AccountActivity":      "account",
	"LeadActivity":         "lead",
	"OpportunityActivity":  "opportunity",
}

// resolveSourceObject maps SF report type identifiers to CRM object API names.
// Package-level entry point preserved for callers (dashboard_service.go,
// existing tests) that do not hold a ReportService; it ignores the auto-
// scanned standard-object resolver and falls back to the legacy heuristic.
func resolveSourceObject(sourceObj string) string {
	return resolveSourceObjectCore(sourceObj)
}

// resolveSourceObject on ReportService consults the auto-scanned
// StandardObjectResolver before the legacy string-trim heuristic. See
// design §4.9.
func (s *ReportService) resolveSourceObject(sourceObj string) string {
	if s != nil && s.standardObjs != nil {
		if canonical := s.standardObjs.Lookup(sourceObj); canonical != "" {
			return canonical
		}
	}
	return resolveSourceObjectCore(sourceObj)
}

func resolveSourceObjectCore(sourceObj string) string {
	if mapped, ok := sfReportTypeToObject[sourceObj]; ok {
		return mapped
	}
	if strings.Contains(sourceObj, "CustomEntity") {
		if dollar := strings.Index(sourceObj, "$"); dollar >= 0 {
			rest := sourceObj[dollar+1:]
			if at := strings.Index(rest, "@"); at >= 0 {
				rest = rest[:at]
			}
			return rest
		}
	}
	if i := strings.Index(sourceObj, "With"); i > 0 {
		sourceObj = sourceObj[:i]
	}
	for _, suffix := range []string{"FieldAuditHistory", "AuditHistory", "Activity", "List"} {
		if strings.HasSuffix(sourceObj, suffix) {
			sourceObj = strings.TrimSuffix(sourceObj, suffix)
			break
		}
	}
	return sourceObj
}

func (s *ReportService) executeReport(ctx context.Context, def *models.ReportDefinition, user *models.UserSession, runtimeFilters map[string]interface{}, limit int) (*models.ReportResult, error) {
	// Check for cross-object JOIN template — dispatch to JOIN path if found.
	// We key on SourceObject (not ReportType) because SF-synced reports store the
	// report type identifier (e.g. "CampaignLeadLifetime") in SourceObject.
	if tmpl := LookupJoinTemplate(def.SourceObject); tmpl != nil {
		return s.executeJoinReport(ctx, def, tmpl, user, runtimeFilters, limit)
	}

	// CRT dispatch hook (design §4.1 step 2). When the SourceObject refers
	// to a stored Custom Report Type or one of the synthetic "CustomEntity$…"
	// shapes, the resolver hands back a synthesized *JoinTemplate plus any
	// degradation warnings. Nil template → the resolver declined and we
	// continue down the single-table path.
	if s.crtResolver != nil {
		crtTmpl, warnings, err := s.crtResolver.Resolve(ctx, def.SourceObject, user)
		if err != nil {
			return nil, err
		}
		if crtTmpl != nil {
			filterCols := make([]string, 0, len(def.Filters))
			for _, f := range def.Filters {
				filterCols = append(filterCols, f.Column)
			}
			decorated := decorateTemplateForCRT(ctx, crtTmpl, def.Columns, filterCols, newCRTMetadataAdapter(s.metadata))
			result, execErr := s.executeJoinReport(ctx, def, decorated, user, runtimeFilters, limit)
			if execErr != nil {
				return nil, execErr
			}
			if len(warnings) > 0 {
				result.Warnings = append(result.Warnings, warnings...)
			}
			return result, nil
		}
	}

	// Normalise SourceObject: SF synced data stores report type strings (e.g. "LeadList")
	// rather than plain object names ("Lead"). Resolve before querying.
	objectAPIName := s.resolveSourceObject(def.SourceObject)

	// Resolve all SF column references to DB field names upfront.
	// SF Analytics uses SCREAMING_SNAKE_CASE ("CLOSE_DATE"); DB uses PascalCase ("CloseDate").
	// Filter out cross-object fields that don't exist on the target table.
	var schema *models.ObjectMetadata
	if s.metadata != nil {
		schema = s.metadata.GetSchema(ctx, objectAPIName)
	}
	knownFields := make(map[string]bool)
	if schema != nil {
		for _, f := range schema.Fields {
			knownFields[f.APIName] = true
			if f.PhysicalName != "" {
				knownFields[f.PhysicalName] = true
			}
		}
	}
	resolvedGrouping := make([]string, 0, len(def.Grouping))
	for _, g := range def.Grouping {
		resolved := resolveSFColumn(g)
		if schema == nil || knownFields[resolved] {
			resolvedGrouping = append(resolvedGrouping, resolved)
		}
		// Skip cross-object fields (e.g. ALIAS → Alias on User, not on Opportunity)
	}
	resolvedAggregates := make([]models.ReportAggregate, len(def.Aggregates))
	for i, a := range def.Aggregates {
		resolvedAggregates[i] = models.ReportAggregate{
			Field: resolveSFColumn(a.Field),
			Type:  a.Type,
		}
	}

	// 3. Execution Strategy based on Format
	isSummary := strings.EqualFold(def.ReportFormat, "summary") ||
		strings.EqualFold(def.ReportType, "summary")
	if isSummary && len(resolvedGrouping) > 0 {
		// Only use the pure analytics (GROUP BY) path when there are no detail columns.
		// If the report has detail columns, fall through to the multi-group path
		// which fetches actual rows for the table display.
		if len(resolvedGrouping) == 1 && len(def.Columns) == 0 {
			criteria := s.buildCriteria(ctx, def.Filters, objectAPIName, user)
			criteria = s.appendRuntimeFilters(ctx, criteria, objectAPIName, runtimeFilters)
			criteria = s.applyShowMeScope(ctx, criteria, def.Scope, objectAPIName, user)

			analyticQuery := models.AnalyticsQuery{
				ObjectAPIName: objectAPIName,
				Operation:     constants.AnalyticsOperationGroupBy,
				GroupBy:       &resolvedGrouping[0],
				Criteria:      criteria,
			}
			if len(resolvedAggregates) > 0 {
				if resolvedAggregates[0].Field != "" {
					analyticQuery.Field = &resolvedAggregates[0].Field
				}
				analyticQuery.AggregateType = resolvedAggregates[0].Type
			}
			res, err := s.queryService.RunAnalytics(ctx, analyticQuery, user)
			if err != nil {
				return nil, err
			}

			rows, ok := res.([]models.SObject)
			if !ok {
				return nil, pkgErrors.NewInternalError("unexpected analytics result format", nil)
			}

			resultDef := *def
			resultDef.Grouping = resolvedGrouping
			resultDef.Aggregates = resolvedAggregates
			return &models.ReportResult{
				ReportMetadata: resultDef,
				Rows:           rows,
			}, nil
		}
	}

	// Default: Tabular (Rows)
	criteria := s.buildCriteria(ctx, def.Filters, objectAPIName, user)
	criteria = s.appendRuntimeFilters(ctx, criteria, objectAPIName, runtimeFilters)
	criteria = s.applyShowMeScope(ctx, criteria, def.Scope, objectAPIName, user)

	// Apply StandardDateFilter (e.g. "THIS_FISCAL_QUARTER" on CloseDate)
	if sdf := def.StandardDateFilter; sdf != nil && sdf.Column != "" {
		dateField := resolveSFColumn(sdf.Column)
		startDate, endDate := s.normalizeDateBoundsForField(ctx, objectAPIName, dateField, sdf.StartDate, sdf.EndDate)
		if startDate != "" {
			criteria = append(criteria, models.QueryCriterion{Field: dateField, Op: ">=", Val: startDate})
		}
		if endDate != "" {
			criteria = append(criteria, models.QueryCriterion{Field: dateField, Op: "<=", Val: endDate})
		}
	}

	// Resolve SF column references to DB field names for SelectFields.
	// Lookup fields (e.g. AccountId) get the _Name display column in resolvedColumns
	// but the base field in selectFields so the query engine hydrates the name.
	var selectFields []string
	var resolvedColumns []string // Column names for display (matched to row data keys)
	seen := make(map[string]bool)
	if len(def.Columns) > 0 {
		for _, col := range def.Columns {
			resolved := resolveSFColumn(col)
			if seen[resolved] {
				continue
			}
			seen[resolved] = true
			selectFields = append(selectFields, resolved)
			// For lookup/reference fields ending in "Id", display the _Name column
			if strings.HasSuffix(resolved, "Id") && resolved != "Id" {
				resolvedColumns = append(resolvedColumns, resolved+"_Name")
			} else {
				resolvedColumns = append(resolvedColumns, resolved)
			}
		}
	}
	// Also include grouping fields so chart data can be computed from rows
	for _, g := range resolvedGrouping {
		if !seen[g] {
			selectFields = append(selectFields, g)
			seen[g] = true
		}
	}

	// Map to API DTO — filters handle volume, 2000 matches SF behavior
	apiReq := api.QueryRequest{
		ObjectAPIName: objectAPIName,
		Limit:         limit,
		SelectFields:  selectFields, // Only fetch report columns + grouping fields
	}

	// When the report carries a non-trivial BooleanFilter (e.g. "1 OR 2 OR 3"),
	// compile the numbered filter list into a formula expression and use it
	// as FilterExpr. The report service's Criteria slice is AND-only, so any
	// OR-containing expression would silently evaluate as AND if we kept
	// using it. Falls back to the plain Criteria path when BooleanFilter is
	// absent or only uses AND.
	// Pre-resolve lookup-field contains/notContain filters into concrete
	// Id IN lists. Without this the formula path produces LIKE against a
	// column that stores IDs, silently matching nothing.
	lookupFrags, lookupWarnings := s.buildLookupFilterFragments(ctx, def.Filters, objectAPIName, user)
	if expr := compileReportBooleanFilter(ctx, def, criteria, s.newPicklistResolverForService(), objectAPIName, lookupFrags); expr != "" {
		apiReq.FilterExpr = expr
	} else if criteria != nil {
		apiReq.Criteria = make([]api.QueryCriterion, len(criteria))
		for i, c := range criteria {
			apiReq.Criteria[i] = api.QueryCriterion{
				Field: c.Field,
				Op:    c.Op,
				Val:   c.Val,
			}
		}
	}

	var retryWarnings []models.CRTWarning
	retryWarnings = append(retryWarnings, lookupWarnings...)
	queryRes, err := s.queryService.Query(ctx, apiReq, user)
	if err != nil && !def.DegradeOnError {
		return nil, err
	}
	if err != nil {
		// Retry 1: drop SelectFields (unknown column in SELECT)
		if len(selectFields) > 0 {
			droppedFields := make([]string, len(selectFields))
			copy(droppedFields, selectFields)
			apiReq.SelectFields = nil
			resolvedColumns = nil
			queryRes, err = s.queryService.Query(ctx, apiReq, user)
			if err == nil {
				s.logger.Warn(fmt.Sprintf("report retry: dropped SelectFields %v", droppedFields))
				retryWarnings = append(retryWarnings, models.CRTWarning{
					Code:         WarnReportSelectFieldsDropped,
					Message:      fmt.Sprintf("SelectFields dropped on retry: %s", strings.Join(droppedFields, ", ")),
					SourceObject: def.SourceObject,
				})
			}
		}
		// Retry 2: progressively remove criteria that reference unknown columns
		for err != nil && len(apiReq.Criteria) > 0 {
			var colErr *pkgErrors.ColumnNotFoundError
			if errors.As(err, &colErr) && colErr.ColumnName != "" {
				// Typed error: remove the criterion whose field matches the column name
				removed := false
				for i, c := range apiReq.Criteria {
					if c.Field == colErr.ColumnName || strings.EqualFold(c.Field, colErr.ColumnName) {
						s.logger.Warn(fmt.Sprintf("report retry: dropped criterion %q (column not found)", c.Field))
						retryWarnings = append(retryWarnings, models.CRTWarning{
							Code:         WarnReportCriterionDropped,
							Message:      fmt.Sprintf("Criterion on field %q dropped: column not found", c.Field),
							SourceObject: c.Field,
						})
						apiReq.Criteria = append(apiReq.Criteria[:i], apiReq.Criteria[i+1:]...)
						removed = true
						break
					}
				}
				if !removed {
					// Column name from error didn't match any criterion — drop all
					s.logger.Warn(fmt.Sprintf("report retry: dropped all %d criteria (column %q not in criteria list)", len(apiReq.Criteria), colErr.ColumnName))
					retryWarnings = append(retryWarnings, models.CRTWarning{
						Code:         WarnReportAllCriteriaDropped,
						Message:      fmt.Sprintf("All %d criteria dropped: column %q not matched to any criterion", len(apiReq.Criteria), colErr.ColumnName),
						SourceObject: def.SourceObject,
					})
					apiReq.Criteria = nil
				}
			} else {
				// Untyped error — can't identify which criterion failed, drop all
				s.logger.Warn(fmt.Sprintf("report retry: dropped all %d criteria (untyped error)", len(apiReq.Criteria)))
				retryWarnings = append(retryWarnings, models.CRTWarning{
					Code:         WarnReportAllCriteriaDropped,
					Message:      fmt.Sprintf("All %d criteria dropped on retry: unrecognized query error", len(apiReq.Criteria)),
					SourceObject: def.SourceObject,
				})
				apiReq.Criteria = nil
			}
			queryRes, err = s.queryService.Query(ctx, apiReq, user)
		}
		if err != nil {
			return nil, err
		}
	}

	rows := queryRes.Records

	// Replace def fields with resolved names so the frontend can match row keys
	resultDef := *def
	if len(resolvedColumns) > 0 {
		resultDef.Columns = resolvedColumns
	}
	resultDef.Grouping = resolvedGrouping
	resultDef.Aggregates = resolvedAggregates

	// Propagate the unpaginated total so "Total Records" matches SF even
	// when the result set exceeds the execution limit. QueryRepository
	// already ran a COUNT(*) when fetching >limit rows; reading TotalSize
	// here avoids a second round-trip. Falls back to len(rows) when the
	// count wasn't issued (result fit within the page).
	totalCount := queryRes.TotalSize
	if totalCount == 0 {
		totalCount = len(rows)
	}

	return &models.ReportResult{
		ReportMetadata: resultDef,
		Rows:           rows,
		TotalCount:     totalCount,
		Warnings:       retryWarnings,
	}, nil
}

// compileReportBooleanFilter turns a SF-style numbered BooleanFilter into a
// formula expression suitable for QueryRequest.FilterExpr. Each numbered
// token ("1", "2", ...) is substituted with a parenthesized fragment built
// from the same criteria that were already resolved into the Criteria slice,
// so filter ops (equals/LIKE/IN/etc), field mapping, and StandardDateFilter
// appendages all stay in sync.
//
// Returns "" when no compilation is needed — the caller then falls back to
// passing Criteria as a pure AND list. Compilation is triggered only when:
//   - BooleanFilter is non-empty AND
//   - its upper-cased form contains the substring "OR" (AND-only expressions
//     are equivalent to the default AND semantics)
//
// Fragments use SF-ish infix syntax (`field = "value"`) which the formula
// normalizer rewrites into expr-lang's `==`. Unknown operators fall back to
// equality — conservative enough that a malformed filter simply drops its
// contribution instead of breaking the whole query.
func compileReportBooleanFilter(ctx context.Context, def *models.ReportDefinition, resolvedCriteria []models.QueryCriterion, resolver *picklistResolver, objectAPIName string, lookupFrags map[int]string) string {
	expr := strings.TrimSpace(def.BooleanFilter)
	// Synthesize an AND expression when no BooleanFilter was set but a
	// filter uses multi-value contains — the default AND Criteria path
	// can't express the OR-of-LIKE chain needed for those values.
	if expr == "" {
		if !filtersNeedFormulaCompilation(def.Filters) {
			return ""
		}
		if len(def.Filters) == 0 {
			return ""
		}
		tokens := make([]string, len(def.Filters))
		for i := range def.Filters {
			tokens[i] = strconv.Itoa(i + 1)
		}
		expr = strings.Join(tokens, " AND ")
	} else if !strings.Contains(strings.ToUpper(expr), "OR") && !filtersNeedFormulaCompilation(def.Filters) {
		return ""
	}
	// Criteria may differ in length from def.Filters when some filters were
	// dropped (unresolvable column, Show-Me-scope appended, etc). Prefer the
	// original def.Filters list so token indices match the BooleanFilter
	// expression SF wrote.
	if len(def.Filters) == 0 {
		return ""
	}
	_ = resolvedCriteria // reserved for future use; scope filter merging goes here

	// Replace highest numbers first so "12" doesn't match "1".
	for i := len(def.Filters); i >= 1; i-- {
		var frag string
		if f, ok := lookupFrags[i-1]; ok {
			frag = f
		} else {
			frag = reportFilterToFormula(ctx, def.Filters[i-1], resolver, objectAPIName)
		}
		if frag == "" {
			// Unsupported operator or empty column — leave the token alone;
			// the formula parser will reject it and we fall back to AND.
			return ""
		}
		pattern := regexp.MustCompile(`\b` + strconv.Itoa(i) + `\b`)
		expr = pattern.ReplaceAllString(expr, "("+frag+")")
	}
	// expr-lang's parser doesn't recognize SF-style uppercase "AND"/"OR"
	// keywords — only "and"/"or" or "&&"/"||". Replace the top-level
	// operators in-place, preserving any OR/AND that appear inside string
	// literals (unlikely in SF BooleanFilter but defense-in-depth).
	expr = replaceBoolOperatorsOutsideLiterals(expr)
	return expr
}

// replaceBoolOperatorsOutsideLiterals swaps whole-word "AND"/"OR" tokens
// for "&&"/"||". Anything inside single or double quotes is left alone.
func replaceBoolOperatorsOutsideLiterals(s string) string {
	var b strings.Builder
	inSingle, inDouble := false, false
	i := 0
	for i < len(s) {
		ch := s[i]
		if ch == '\'' && !inDouble {
			inSingle = !inSingle
			b.WriteByte(ch)
			i++
			continue
		}
		if ch == '"' && !inSingle {
			inDouble = !inDouble
			b.WriteByte(ch)
			i++
			continue
		}
		if inSingle || inDouble {
			b.WriteByte(ch)
			i++
			continue
		}
		// Match whole words OR / AND. Previous char must not be ident char;
		// next char must not be ident char.
		prevIsIdent := i > 0 && isIdentByte(s[i-1])
		if !prevIsIdent && i+2 <= len(s) && s[i:i+2] == "OR" && (i+2 == len(s) || !isIdentByte(s[i+2])) {
			b.WriteString("||")
			i += 2
			continue
		}
		if !prevIsIdent && i+3 <= len(s) && s[i:i+3] == "AND" && (i+3 == len(s) || !isIdentByte(s[i+3])) {
			b.WriteString("&&")
			i += 3
			continue
		}
		b.WriteByte(ch)
		i++
	}
	return b.String()
}

func isIdentByte(c byte) bool {
	return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_'
}

// filtersNeedFormulaCompilation reports whether any filter requires the
// formula-based compile path to express correctly. Currently only
// multi-value `contains`/`notContain` needs this — the default AND
// Criteria builder produces a single LIKE '%v1,v2%' literal which never
// matches anything.
func filtersNeedFormulaCompilation(filters []models.ReportFilter) bool {
	for _, f := range filters {
		op := strings.ToLower(f.Operator)
		if op != "contains" && op != "notcontain" && op != "doesnotcontain" {
			continue
		}
		if !strings.Contains(f.Value, ",") {
			continue
		}
		if len(splitAndTrimMulti(f.Value)) > 1 {
			return true
		}
	}
	return false
}

// reportFilterToFormula emits a single-predicate formula fragment. Field
// names are resolved via resolveSFColumn so stale SF aliases (CLOSE_DATE,
// COUNTRY_CODE, etc) line up with the physical column and inherit the same
// mapper the repository applies to its other expressions.
//
// Salesforce treats comma-separated values on equals/notEqual as an IN/
// NOT IN membership check (the filter UI exposes multi-select that way).
// Emit an expr-lang `in` expression so the compiled SQL uses IN(...) and
// matches SF row counts.
func reportFilterToFormula(ctx context.Context, f models.ReportFilter, resolver *picklistResolver, objectAPIName string) string {
	col := resolveSFColumn(f.Column)
	if col == "" {
		return ""
	}
	// Apply picklist label → API value translation so filter values align
	// with what the DB stores. Safe no-op for non-picklist fields and when
	// the resolver or object is not wired.
	resolveOne := func(v string) string {
		if resolver == nil || objectAPIName == "" {
			return v
		}
		return resolver.resolve(ctx, objectAPIName, col, v)
	}
	op := strings.ToLower(f.Operator)
	if (op == "equals" || op == "eq" || op == "=" ||
		op == "notequal" || op == "neq" || op == "!=" || op == "<>") && strings.Contains(f.Value, ",") {
		parts := splitAndTrimMulti(f.Value)
		if len(parts) > 1 {
			quoted := make([]string, len(parts))
			for i, p := range parts {
				quoted[i] = `"` + escapeFormulaString(resolveOne(p)) + `"`
			}
			list := "[" + strings.Join(quoted, ",") + "]"
			if op == "equals" || op == "eq" || op == "=" {
				return fmt.Sprintf(`%s in %s`, col, list)
			}
			return fmt.Sprintf(`!(%s in %s)`, col, list)
		}
	}
	// Multi-value contains: SF treats comma-separated literals as an OR
	// chain of CONTAINS clauses. notContain is the De Morgan dual (AND of
	// NOT CONTAINS).
	if (op == "contains" || op == "notcontain" || op == "doesnotcontain") && strings.Contains(f.Value, ",") {
		parts := splitAndTrimMulti(f.Value)
		if len(parts) > 1 {
			fragments := make([]string, len(parts))
			if op == "contains" {
				for i, p := range parts {
					fragments[i] = fmt.Sprintf(`CONTAINS(%s, "%s")`, col, escapeFormulaString(resolveOne(p)))
				}
				return "(" + strings.Join(fragments, " OR ") + ")"
			}
			for i, p := range parts {
				fragments[i] = fmt.Sprintf(`NOT(CONTAINS(%s, "%s"))`, col, escapeFormulaString(resolveOne(p)))
			}
			return "(" + strings.Join(fragments, " AND ") + ")"
		}
	}
	val := escapeFormulaString(resolveOne(f.Value))
	switch op {
	case "equals", "eq", "=":
		return fmt.Sprintf(`%s = "%s"`, col, val)
	case "notequal", "neq", "!=", "<>":
		return fmt.Sprintf(`%s != "%s"`, col, val)
	case "greaterthan", "gt":
		return fmt.Sprintf(`%s > "%s"`, col, val)
	case "greaterorequal", "gte", ">=":
		return fmt.Sprintf(`%s >= "%s"`, col, val)
	case "lessthan", "lt":
		return fmt.Sprintf(`%s < "%s"`, col, val)
	case "lessorequal", "lte", "<=":
		return fmt.Sprintf(`%s <= "%s"`, col, val)
	case "contains":
		return fmt.Sprintf(`CONTAINS(%s, "%s")`, col, val)
	case "notcontain", "doesnotcontain":
		return fmt.Sprintf(`NOT(CONTAINS(%s, "%s"))`, col, val)
	case "startswith":
		return fmt.Sprintf(`BEGINS(%s, "%s")`, col, val)
	}
	// Unknown operator — fall through to equality so a malformed filter
	// still participates but doesn't nuke the whole expression.
	return fmt.Sprintf(`%s = "%s"`, col, val)
}

func splitAndTrimMulti(s string) []string {
	raw := strings.Split(s, ",")
	out := make([]string, 0, len(raw))
	for _, p := range raw {
		p = strings.TrimSpace(p)
		if p != "" {
			out = append(out, p)
		}
	}
	return out
}

func escapeFormulaString(s string) string {
	// Formula string literals use double quotes; escape embedded quotes and
	// backslashes so the parser doesn't trip on user-supplied filter values.
	s = strings.ReplaceAll(s, `\`, `\\`)
	s = strings.ReplaceAll(s, `"`, `\"`)
	return s
}

// buildLookupFilterFragments pre-resolves each filter that targets a lookup
// field with a string operator, producing a formula-compatible
// `<fk> in [...]` (or `!(<fk> in [...])`) fragment. The map is keyed by
// the filter index (0-based) so callers can substitute the fragment in
// place of the default compiled one.
//
// Filters that aren't lookup-qualified or whose target resolution fails
// are omitted — the caller falls back to reportFilterToFormula.
func (s *ReportService) buildLookupFilterFragments(ctx context.Context, filters []models.ReportFilter, objectAPIName string, user *models.UserSession) (map[int]string, []models.CRTWarning) {
	if s == nil || s.metadata == nil || len(filters) == 0 || objectAPIName == "" {
		return nil, nil
	}
	out := make(map[int]string)
	var warnings []models.CRTWarning
	for i, f := range filters {
		if !isLookupStringFilter(f.Operator) {
			continue
		}
		ids, ok := s.resolveLookupFilterIDs(ctx, objectAPIName, f, user)
		if !ok {
			if s.logger != nil {
				s.logger.Warn(fmt.Sprintf("report lookup filter dropped: column=%s operator=%s object=%s", f.Column, f.Operator, objectAPIName))
			}
			warnings = append(warnings, models.CRTWarning{
				Code:         WarnReportLookupFilterDropped,
				Message:      fmt.Sprintf("Filter on %q (%s) was not applied: lookup field resolution failed", f.Column, f.Operator),
				SourceObject: objectAPIName,
			})
			continue
		}
		fk := resolveSFColumn(f.Column)
		if fk == "" {
			continue
		}
		if len(ids) == 0 {
			// Zero matches on the referenced object. Inject an always-
			// false fragment so the boolean combinator propagates the
			// emptiness correctly (`col = "__no_match__"`).
			out[i] = fmt.Sprintf(`%s = "__no_match__"`, fk)
			continue
		}
		quoted := make([]string, len(ids))
		for j, id := range ids {
			quoted[j] = `"` + escapeFormulaString(id) + `"`
		}
		list := "[" + strings.Join(quoted, ",") + "]"
		op := strings.ToLower(f.Operator)
		if op == "notcontain" || op == "doesnotcontain" {
			out[i] = fmt.Sprintf(`!(%s in %s)`, fk, list)
		} else {
			out[i] = fmt.Sprintf(`%s in %s`, fk, list)
		}
	}
	return out, warnings
}

// isLookupStringFilter reports whether the filter operator is a string
// comparison that, when applied to a reference (lookup) field, should
// actually hit the referenced object's Name column rather than the raw
// ID. Equality operators are excluded because SF stores the ID directly
// and an equals filter on a lookup is already correct.
func isLookupStringFilter(op string) bool {
	switch strings.ToLower(op) {
	case "contains", "notcontain", "doesnotcontain", "startswith":
		return true
	}
	return false
}

// resolveLookupFilterIDs pre-queries the target object of a lookup field
// for Ids whose Name matches the filter pattern(s). Returns (ids, true)
// when the filter column actually is a lookup on objectAPIName; otherwise
// returns (nil, false) and the caller falls through to the normal path.
//
// Multi-value comma-separated values are treated as an OR chain across
// contains clauses — SF's filter UI multi-selects this way.
func (s *ReportService) resolveLookupFilterIDs(ctx context.Context, objectAPIName string, f models.ReportFilter, user *models.UserSession) ([]string, bool) {
	if s.metadata == nil {
		return nil, false
	}
	fk := resolveSFColumn(f.Column)
	if fk == "" {
		return nil, false
	}
	schema := s.metadata.GetSchema(ctx, objectAPIName)
	if schema == nil {
		return nil, false
	}
	var fkField *models.FieldMetadata
	for i := range schema.Fields {
		fld := &schema.Fields[i]
		if strings.EqualFold(fld.APIName, fk) || strings.EqualFold(fld.PhysicalName, fk) {
			fkField = fld
			break
		}
	}
	if fkField == nil {
		return nil, false
	}
	if strings.ToLower(string(fkField.Type)) != "reference" {
		return nil, false
	}
	if len(fkField.ReferenceTo) == 0 {
		return nil, false
	}
	target := fkField.ReferenceTo[0]

	// Build IN-list by querying the target for names that match any of
	// the comma-separated patterns. For `contains` and `startsWith`,
	// wrap each pattern appropriately.
	vals := splitAndTrimMulti(f.Value)
	if len(vals) == 0 {
		vals = []string{f.Value}
	}
	patterns := make([]interface{}, 0, len(vals))
	for _, v := range vals {
		switch strings.ToLower(f.Operator) {
		case "contains", "notcontain", "doesnotcontain":
			patterns = append(patterns, "%"+v+"%")
		case "startswith":
			patterns = append(patterns, v+"%")
		default:
			patterns = append(patterns, "%"+v+"%")
		}
	}
	// Each pattern is ORed. Build a criterion list the repo can consume.
	// The query service doesn't support OR in Criteria, so assemble a
	// FilterExpr using expr-lang's `||`.
	//
	// For notContain/doesNotContain we still use `contains` to find
	// matching Ids, then the caller inverts with NOT IN.
	clauses := make([]string, 0, len(patterns))
	for _, p := range patterns {
		clauses = append(clauses, fmt.Sprintf(`CONTAINS(Name, %q)`, strings.Trim(p.(string), "%")))
	}
	filterExpr := strings.Join(clauses, " || ")

	req := api.QueryRequest{
		ObjectAPIName: target,
		SelectFields:  []string{"Id"},
		FilterExpr:    filterExpr,
		Limit:         5000,
	}
	res, err := s.queryService.Query(ctx, req, user)
	if err != nil || res == nil {
		return nil, false
	}
	ids := make([]string, 0, len(res.Records))
	for _, row := range res.Records {
		if id := row.GetString("Id"); id != "" {
			ids = append(ids, id)
		}
	}
	return ids, true
}

// applyShowMeScope appends OwnerId criteria based on the report's "Show me" scope.
//   - "" or "all"       → no filter
//   - "mine"            → OwnerId = current user
//   - "myTeam"          → OwnerId IN (current user + role subordinates)
//
// If the source object's schema does not expose an OwnerId field, scope is
// silently skipped so the query still runs (consistent with SF behavior for
// objects without ownership). If permSvc is nil or role expansion fails,
// "myTeam" gracefully degrades to "mine" so reports never silently leak data.
func (s *ReportService) applyShowMeScope(ctx context.Context, criteria []models.QueryCriterion, scope string, objectAPIName string, user *models.UserSession) []models.QueryCriterion {
	if user == nil {
		return criteria
	}
	normalized := strings.ToLower(strings.TrimSpace(scope))
	if normalized == "" || normalized == "all" {
		return criteria
	}

	// Skip scope filtering when the target object has no OwnerId column.
	if s.metadata != nil {
		if schema := s.metadata.GetSchema(ctx, objectAPIName); schema != nil {
			hasOwner := false
			for _, f := range schema.Fields {
				if f.APIName == "OwnerId" || f.PhysicalName == "OwnerId" {
					hasOwner = true
					break
				}
			}
			if !hasOwner {
				return criteria
			}
		}
	}

	switch normalized {
	case "mine":
		return append(criteria, models.QueryCriterion{Field: "OwnerId", Op: "=", Val: user.ID})
	case "myteam":
		ownerIDs := []string{user.ID}
		if s.permissions != nil && user.RoleID != nil && *user.RoleID != "" {
			subs, err := s.permissions.GetSubordinateUserIDs(ctx, *user.RoleID)
			if err == nil && len(subs) > 0 {
				ownerIDs = append(ownerIDs, subs...)
			}
		}
		if len(ownerIDs) == 1 {
			return append(criteria, models.QueryCriterion{Field: "OwnerId", Op: "=", Val: ownerIDs[0]})
		}
		return append(criteria, models.QueryCriterion{Field: "OwnerId", Op: "IN", Val: ownerIDs})
	}
	return criteria
}

func (s *ReportService) normalizeDateBoundsForField(ctx context.Context, objectAPIName, fieldAPIName, start, end string) (string, string) {
	if !s.isDateTimeField(ctx, objectAPIName, fieldAPIName) {
		return start, end
	}
	if isDateOnlyFilterBound(start) {
		start = start + " 00:00:00"
	}
	if isDateOnlyFilterBound(end) {
		end = end + " 23:59:59"
	}
	return start, end
}

func (s *ReportService) isDateTimeField(ctx context.Context, objectAPIName, fieldAPIName string) bool {
	switch strings.ToLower(strings.TrimSpace(fieldAPIName)) {
	case "createddate", "lastmodifieddate", "systemmodstamp":
		return true
	}

	if s == nil || s.metadata == nil || objectAPIName == "" || fieldAPIName == "" {
		return false
	}

	schema := s.metadata.GetSchema(ctx, objectAPIName)
	if schema == nil {
		return false
	}

	field := persistence.FindField(schema, fieldAPIName)
	if field == nil {
		return false
	}

	if strings.EqualFold(string(field.Type), string(constants.FieldTypeDatetime)) {
		return true
	}
	return field.LogicalType != nil && strings.EqualFold(string(*field.LogicalType), string(constants.FieldTypeDatetime))
}

func isDateOnlyFilterBound(value string) bool {
	if len(value) != len("2006-01-02") {
		return false
	}
	_, err := time.Parse("2006-01-02", value)
	return err == nil
}

// newPicklistResolverForService builds a picklistResolver backed by the
// ReportService's MetadataService. Returns a resolver that is a no-op when
// s.metadata is nil (matches the existing tolerance of nil metadata across
// report execution — unit tests construct ReportService without metadata).
//
// See issue #476 for why this exists: report filter values arrive as
// user-facing labels ("Free Trial") but the DB stores API values
// ("free_trial"). A single resolver applied at every filter entry point
// fixes every report/dashboard/runtime filter path at once.
func (s *ReportService) newPicklistResolverForService() *picklistResolver {
	if s == nil || s.metadata == nil {
		return newPicklistResolver(nil)
	}
	return newPicklistResolver(func(ctx context.Context, objectAPIName string) *models.ObjectMetadata {
		return s.metadata.GetSchema(ctx, objectAPIName)
	})
}

func (s *ReportService) buildCriteria(ctx context.Context, filters []models.ReportFilter, objectAPIName string, user *models.UserSession) []models.QueryCriterion {
	if len(filters) == 0 {
		return nil
	}

	resolver := s.newPicklistResolverForService()

	criteria := make([]models.QueryCriterion, 0, len(filters))
	for _, f := range filters {
		// Cross-object lookup filter: when the column is a reference field
		// (e.g. Primary_Campaign_Source_1__c pointing at Campaign) and the
		// operator is a string op like `contains`, Salesforce resolves by
		// joining to the target and filtering on its Name column. Our
		// single-table path has no JOIN surface, so we pre-query the
		// target object for matching Ids and rewrite the filter to
		// `<fk> IN (ids)`. Handles multi-value comma-separated values
		// as an OR over `contains` clauses (SF `contains` semantics).
		if s.metadata != nil && isLookupStringFilter(f.Operator) {
			ids, ok := s.resolveLookupFilterIDs(ctx, objectAPIName, f, user)
			if ok {
				fk := resolveSFColumn(f.Column)
				if len(ids) == 0 {
					// Filter matched zero rows on the referenced object.
					// Inject an unsatisfiable criterion so the report
					// returns nothing (matching SF semantics).
					criteria = append(criteria, models.QueryCriterion{Field: fk, Op: "=", Val: "__no_match__"})
					continue
				}
				vals := make([]interface{}, len(ids))
				for i, id := range ids {
					vals[i] = id
				}
				inOp := "IN"
				if strings.EqualFold(f.Operator, "notContain") || strings.EqualFold(f.Operator, "doesNotContain") || strings.EqualFold(f.Operator, "notEqual") || strings.EqualFold(f.Operator, "neq") {
					inOp = "NOT IN"
				}
				criteria = append(criteria, models.QueryCriterion{Field: fk, Op: inOp, Val: vals})
				continue
			}
		}

		field := resolveSFColumn(f.Column)

		op := "="
		switch f.Operator {
		case "equals", "eq":
			op = "="
		case "notEqual", "neq":
			op = "!="
		case "contains":
			op = "LIKE"
		case "notContain", "doesNotContain":
			op = "NOT LIKE"
		case "greaterThan", "gt":
			op = ">"
		case "greaterOrEqual", "gte":
			op = ">="
		case "lessThan", "lt":
			op = "<"
		case "lessOrEqual", "lte":
			op = "<="
		}

		val := f.Value

		// Convert SF boolean strings to DB integer values
		if strings.EqualFold(val, "true") {
			val = "1"
		} else if strings.EqualFold(val, "false") {
			val = "0"
		}

		// Handle comma-separated multi-value (e.g. "Open,Assigned,Working")
		if (op == "=" || op == "!=") && strings.Contains(val, ",") {
			parts := strings.Split(val, ",")
			vals := make([]interface{}, len(parts))
			for j, p := range parts {
				vals[j] = resolver.resolve(ctx, objectAPIName, field, strings.TrimSpace(p))
			}
			inOp := "IN"
			if op == "!=" {
				inOp = "NOT IN"
			}
			criteria = append(criteria, models.QueryCriterion{Field: field, Op: inOp, Val: vals})
			continue
		}


		// Resolve single-value picklist labels → API values. Non-picklist
		// fields and unmatched values pass through unchanged.
		val = resolver.resolve(ctx, objectAPIName, field, val)

		if f.Operator == "contains" || f.Operator == "notContain" || f.Operator == "doesNotContain" {
			val = fmt.Sprintf("%%%s%%", val)
		}

		criteria = append(criteria, models.QueryCriterion{Field: field, Op: op, Val: val})
	}

	return criteria
}


func (s *ReportService) appendRuntimeFilters(ctx context.Context, criteria []models.QueryCriterion, objectAPIName string, filters map[string]interface{}) []models.QueryCriterion {
	if filters == nil {
		return criteria
	}

	resolver := s.newPicklistResolverForService()

	for k, v := range filters {
		switch k {
		case "__date_range":
			continue
		case "startDate":
			continue
		default:
			valStr := fmt.Sprintf("%v", v)
			// Resolve picklist labels → API values (same rationale as buildCriteria).
			// Runtime filters use the same user-facing labels as definition filters,
			// so they need the same translation. See issue #476.
			valStr = resolver.resolveMulti(ctx, objectAPIName, k, valStr)
			criteria = append(criteria, models.QueryCriterion{
				Field: k,
				Op:    "=",
				Val:   valStr,
			})
		}
	}

	// Handle Date Range
	if dr, ok := filters["__date_range"].(map[string]interface{}); ok {
		field, _ := dr["field"].(string)
		start := dr["start"]
		end := dr["end"]

		if field != "" {
			if start != nil {
				startStr := fmt.Sprintf("%v", start)
				// Handle time.Time formatting if needed, but %v might work for simple ISO or similar if stringer?
				// Usually DB expects 'YYYY-MM-DD...'
				if t, ok := start.(time.Time); ok {
					startStr = t.Format("2006-01-02 15:04:05")
				}
				criteria = append(criteria, models.QueryCriterion{
					Field: field,
					Op:    ">=",
					Val:   startStr,
				})
			}
			if end != nil {
				endStr := fmt.Sprintf("%v", end)
				if t, ok := end.(time.Time); ok {
					endStr = t.Format("2006-01-02 15:04:05")
				}
				criteria = append(criteria, models.QueryCriterion{
					Field: field,
					Op:    "<=",
					Val:   endStr,
				})
			}
		}
	}

	return criteria
}