package analytics

import (
	"fmt"
	"regexp"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/nexuscrm/shared/pkg/queryspec"
)

// Aggregate Regex: Matches COUNT(Field), SUM(Field), etc. with an optional
// SOQL aggregate alias (the parser normalizes `AS alias` to ` alias`).
var aggRegex = regexp.MustCompile(`(?i)^\s*(COUNT|SUM|AVG|MIN|MAX)\((.*)\)(?:\s+(?:AS\s+)?([A-Za-z_][A-Za-z0-9_]*))?\s*$`)

type analyticsResultShape int

const (
	analyticsResultShapeAggregateRows analyticsResultShape = iota
	analyticsResultShapeCountTotalSize
)

type analyticsQueryPlan struct {
	query            models.AnalyticsQuery
	resultShape      analyticsResultShape
	outputKey        string
	aggregateOutputs []analyticsAggregateOutput
}

type analyticsAggregateOutput struct {
	resultKey string
	outputKey string
}

type queryFilterLowering struct {
	expression string
	criteria   []queryspec.QueryCriterion
}

func lowerQueryFilterPredicate(predicate queryspec.Predicate) (queryFilterLowering, error) {
	lowering, err := queryspec.LowerPredicate(predicate, queryspec.PredicateLoweringPreferCriteria)
	if err != nil {
		return queryFilterLowering{}, err
	}
	return queryFilterLowering{expression: lowering.Expression, criteria: lowering.Criteria}, nil
}

func lowerQueryExpressionPredicate(predicate queryspec.Predicate) (string, error) {
	lowering, err := queryspec.LowerPredicate(predicate, queryspec.PredicateLoweringExpressionOnly)
	if err != nil {
		return "", err
	}
	return lowering.Expression, nil
}

func modelQueryCriteria(criteria []queryspec.QueryCriterion) []models.QueryCriterion {
	if len(criteria) == 0 {
		return nil
	}
	out := make([]models.QueryCriterion, len(criteria))
	for i, c := range criteria {
		out[i] = models.QueryCriterion{
			Field: c.Field,
			Op:    c.Op,
			Val:   c.Val,
		}
	}
	return out
}

// buildAnalyticsQuery keeps this branch's neutral queryspec.QuerySpec input and
// main's analyticsQueryPlan return (the COUNT()-shape fix: resultShape distinguishes
// bare COUNT() → totalSize from COUNT(field) → expr0 AggregateResult row).
func (qs *QueryService) buildAnalyticsQuery(req queryspec.QuerySpec) (bool, *analyticsQueryPlan, error) {
	// A query is an aggregate if it has a GROUP BY clause OR uses aggregate functions in SELECT
	hasGroupBy := req.GroupBy != ""

	var aggOp string
	var aggField string
	var aggType string
	var aggAlias string
	var aggregates []models.AnalyticsAggregate
	var aggregateOutputs []analyticsAggregateOutput
	var nonAggregateFields []string
	resultShape := analyticsResultShapeAggregateRows

	// Scan SelectFields for Aggregates
	for _, field := range req.SelectFields {
		matches := aggRegex.FindStringSubmatch(field)
		if len(matches) == 4 {
			// matches[1] = OP (COUNT, SUM...), matches[2] = Field
			op := strings.ToUpper(matches[1])
			f := strings.TrimSpace(matches[2])
			alias := strings.TrimSpace(matches[3])

			if op != "COUNT" && f == "" {
				return false, nil, apperrors.NewValidationError("SelectFields", fmt.Sprintf("%s aggregate requires a field", op))
			}

			// Map to normalized operation names required by RunAnalytics (count, sum, avg)
			switch op {
			case "COUNT":
				aggOp = constants.AnalyticsOperationCount
			case "SUM":
				aggOp = constants.AnalyticsOperationSum
			case "AVG":
				aggOp = constants.AnalyticsOperationAvg
			case "MIN":
				aggOp = "min"
			case "MAX":
				aggOp = "max"
			}
			if len(aggregates) == 0 {
				aggType = op
				aggField = f
				aggAlias = alias
			}
			aggregateIndex := len(aggregates)
			aggregates = append(aggregates, models.AnalyticsAggregate{
				Type:  op,
				Field: f,
			})
			aggregateOutputs = append(aggregateOutputs, analyticsAggregateOutput{
				resultKey: aggregateResultKey(aggregateIndex, len(aggregates)),
				outputKey: aggregateOutputKey(alias, aggregateIndex),
			})
			continue
		}
		if trimmed := strings.TrimSpace(field); trimmed != "" {
			nonAggregateFields = append(nonAggregateFields, trimmed)
		}
	}

	if hasGroupBy || len(aggregates) > 0 {
		for _, field := range nonAggregateFields {
			if hasGroupBy && strings.EqualFold(field, req.GroupBy) {
				continue
			}
			return false, nil, apperrors.NewValidationError("SelectFields", fmt.Sprintf("field %q must appear in GROUP BY or be aggregated", field))
		}
	}

	if !hasGroupBy && aggOp == constants.AnalyticsOperationCount && aggField == "" && aggAlias == "" {
		resultShape = analyticsResultShapeCountTotalSize
	}

	// Case 1: If Group By exists, Operation MUST be 'group_by' for RunAnalytics to handle it correctly (it will look at AggregateType)
	if hasGroupBy {
		aggOp = constants.AnalyticsOperationGroupBy
	} else if aggOp == "" {
		// Case 2: No Group By, No Aggregate -> Not an analytics query
		return false, nil, nil
	}

	// Case 2: Aggregate without Group By (e.g. SELECT COUNT(Id) FROM Opp)
	if !hasGroupBy && aggOp == "" {
		return false, nil, nil // Not an aggregate query
	}

	filter, err := lowerQueryFilterPredicate(req.Filter)
	if err != nil {
		return false, nil, err
	}
	havingExpr, err := lowerQueryExpressionPredicate(req.Having)
	if err != nil {
		return false, nil, err
	}

	// Construct AnalyticsQuery
	aq := &models.AnalyticsQuery{
		ObjectAPIName: req.ObjectAPIName,
		Operation:     aggOp,
		FilterExpr:    filter.expression,
		GroupBy:       nil,
		Field:         nil,
		AggregateType: aggType,
		Having:        havingExpr,
		ScanAll:       req.ScanAll,
	}

	if req.GroupBy != "" {
		s := req.GroupBy
		aq.GroupBy = &s
	}

	if aggField != "" {
		aq.Field = &aggField
	}
	if len(aggregates) > 1 {
		aq.Aggregates = aggregates
	}
	if len(filter.criteria) > 0 {
		aq.Criteria = modelQueryCriteria(filter.criteria)
	}

	return true, &analyticsQueryPlan{
		query:            *aq,
		resultShape:      resultShape,
		outputKey:        aggregateOutputKey(aggAlias, 0),
		aggregateOutputs: finalizeAggregateOutputs(aggregateOutputs),
	}, nil
}

func aggregateOutputKey(alias string, index int) string {
	alias = strings.TrimSpace(alias)
	if alias != "" {
		return alias
	}
	return fmt.Sprintf("expr%d", index)
}

func aggregateResultKey(index int, aggregateCount int) string {
	if aggregateCount == 1 {
		return "val"
	}
	return fmt.Sprintf("agg%d", index)
}

func finalizeAggregateOutputs(outputs []analyticsAggregateOutput) []analyticsAggregateOutput {
	if len(outputs) == 0 {
		return []analyticsAggregateOutput{{resultKey: "val", outputKey: "expr0"}}
	}
	if len(outputs) == 1 {
		outputs[0].resultKey = "val"
		return outputs
	}
	for i := range outputs {
		outputs[i].resultKey = fmt.Sprintf("agg%d", i)
	}
	return outputs
}