package persistence

import (
	"context"
	"fmt"
	"strconv"
	"strings"

	domainrls "github.com/nexuscrm/backend/internal/domain/rls"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/formula"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/rs/zerolog/log"
)

// RunAnalytics executes an aggregation query
func (r *QueryRepository) RunAnalytics(ctx context.Context, tableSchema *models.ObjectMetadata, q models.AnalyticsQuery, rlsResult *domainrls.Result) (interface{}, error) {
	// Use WithMetadata to enable alias resolution where necessary
	readTable := r.resolveReadTable(ctx, tableSchema)
	builder := query.From(readTable).WithMetadata(tableSchema)

	if err := r.applyAnalyticsFilters(builder, tableSchema, q, rlsResult, readTable); err != nil {
		return nil, err
	}

	if err := r.applyAnalyticsOperation(builder, tableSchema, q); err != nil {
		return nil, err
	}

	return r.executeAndFormatAnalytics(ctx, builder, q)
}

func (r *QueryRepository) applyAnalyticsFilters(builder *query.Builder, tableSchema *models.ObjectMetadata, q models.AnalyticsQuery, rlsResult *domainrls.Result, readTable string) error {
	if schemaHasIsDeleted(tableSchema) {
		builder.ExcludeDeleted()
	}
	builder.SkipID()

	if q.FilterExpr != "" {
		sqlWhere, args, err := formula.ToSQLWithMapper(q.FilterExpr, newFieldMapper(tableSchema))
		if err != nil {
			return apperrors.NewValidationError("filterExpr", fmt.Sprintf("invalid filter expression: %v", err))
		}
		builder.WhereRaw(sqlWhere, args)
	}
	if rlsWhere := domainrls.ToSQLFilter(rlsResult, readTable); rlsWhere != "" {
		builder.WhereRaw(rlsWhere, nil)
	}

	if q.Having != "" {
		replacements := buildFieldReplacements(tableSchema)
		resolvedHaving := q.Having
		if len(replacements) > 0 {
			resolvedHaving = replaceIdentifiers(resolvedHaving, replacements)
		}
		builder.HavingRaw(resolvedHaving, nil)
	}

	if len(q.Criteria) > 0 {
		for _, c := range q.Criteria {
			if isValidFieldName(c.Field) && isValidFilterOp(c.Op) {
				builder.WhereOp(c.Field, c.Op, c.Val)
			}
		}
	}

	return nil
}

func (r *QueryRepository) applyAnalyticsOperation(builder *query.Builder, tableSchema *models.ObjectMetadata, q models.AnalyticsQuery) error {
	switch q.Operation {
	case OpCount:
		builder.AddSelectRaw("COUNT(*) as val")

	case OpGroupBy:
		agg := "COUNT(*)"
		if q.Field != nil {
			if !isValidFieldName(*q.Field) {
				return apperrors.NewValidationError("field", fmt.Sprintf("invalid aggregation field: %s", *q.Field))
			}
			aggFunc := "SUM"
			if q.AggregateType != "" {
				validAggs := map[string]bool{"SUM": true, "AVG": true, "MIN": true, "MAX": true, "COUNT": true}
				if validAggs[strings.ToUpper(q.AggregateType)] {
					aggFunc = strings.ToUpper(q.AggregateType)
				}
			}
			aggCol := resolvePhysicalName(tableSchema, *q.Field)
			if aggFunc == "COUNT" {
				agg = fmt.Sprintf("COUNT(`%s`)", aggCol)
			} else {
				agg = fmt.Sprintf("%s(`%s`)", aggFunc, aggCol)
			}
		}

		if !isValidFieldName(*q.GroupBy) {
			return apperrors.NewValidationError("groupBy", fmt.Sprintf("invalid group by field: %s", *q.GroupBy))
		}

		groupByCol := resolvePhysicalName(tableSchema, *q.GroupBy)

		builder.AddSelectRaw(fmt.Sprintf("`%s` as name", groupByCol))
		builder.AddSelectRaw(fmt.Sprintf("%s as val", agg))
		builder.GroupByRaw(fmt.Sprintf("`%s`", groupByCol))

		if q.SortField != "" {
			if !isValidFieldName(q.SortField) {
				builder.OrderBy("`val`", "DESC")
			} else {
				field := q.SortField
				direction := q.SortDirection
				if direction != "ASC" && direction != "DESC" {
					direction = "DESC"
				}
				if field == "name" || field == "val" || field == "value" {
					sortCol := field
					if field == "value" {
						sortCol = "val"
					}
					builder.OrderBy(fmt.Sprintf("`%s`", sortCol), direction)
				} else {
					builder.OrderBy(field, direction)
				}
			}
		} else {
			builder.OrderBy("`val`", "DESC")
		}

		limit := constants.DefaultSOSLLimit
		if q.Limit > 0 {
			limit = q.Limit
		}
		builder.Limit(limit)

	default: // sum, avg, min, max
		validOps := map[string]bool{
			"SUM": true, "AVG": true, "MIN": true, "MAX": true,
		}
		op := strings.ToUpper(q.Operation)
		if !validOps[op] {
			return apperrors.NewValidationError("operation", fmt.Sprintf("unsupported analytics operation: %s", q.Operation))
		}
		if q.Field == nil || !isValidFieldName(*q.Field) {
			return apperrors.NewValidationError("field", fmt.Sprintf("invalid analytics field: %v", q.Field))
		}
		aggCol := resolvePhysicalName(tableSchema, *q.Field)
		builder.AddSelectRaw(fmt.Sprintf("%s(`%s`) as val", op, aggCol))
	}
	return nil
}

func (r *QueryRepository) executeAndFormatAnalytics(ctx context.Context, builder *query.Builder, q models.AnalyticsQuery) (interface{}, error) {
	queryP := builder.Build()
	log.Debug().Str("sql", queryP.SQL).Any("params", queryP.Params).Msg("DEBUG_ANALYTICS_SQL")

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, queryP.SQL, queryP.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	results, err := query.ScanRowsToSObjects(rows)
	if err != nil {
		return nil, err
	}

	// Post-process to ensure numeric types for 'val'
	for _, row := range results {
		if v, ok := row["val"]; ok {
			if s, isStr := v.(string); isStr {
				if f, err := strconv.ParseFloat(s, 64); err == nil {
					row["val"] = f
				}
			}
		}
	}

	if q.Operation == OpGroupBy {
		return results, nil
	}

	if len(results) > 0 {
		return results[0]["val"], nil
	}

	return 0, nil
}