package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	pkgerrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
)

// RollupRepository handles the dynamic SQL execution required for calculating and applying rollups.
// It bypasses the standard PersistenceService to avoid circular dependencies and overhead for system calculations.
type RollupRepository struct {
	db *sql.DB
}

// NewRollupRepository creates a new RollupRepository
func NewRollupRepository(db *sql.DB) *RollupRepository {
	return &RollupRepository{
		db: db,
	}
}

// GetExecutor returns the transactional executor if present in context, or the DB connection
func (r *RollupRepository) GetExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// CalculateRollup executes the aggregation query and returns the raw result.
// It supports COUNT, SUM, MIN, MAX, AVG.
// The query is constructed dynamically based on metadata configuration.
func (r *RollupRepository) CalculateRollup(ctx context.Context, calcType, summaryObject, summaryField, relationField, parentID string, filter *string) (interface{}, error) {
	// 1. Build Aggregation Query
	// SELECT FUNC(field) FROM child WHERE rel_field = ? AND IsDeleted = false

	if !isValidFieldName(summaryObject) {
		return nil, pkgerrors.NewValidationError("summaryObject", fmt.Sprintf("invalid summary object: %s", summaryObject))
	}
	if !isValidFieldName(summaryField) && summaryField != "*" {
		return nil, pkgerrors.NewValidationError("summaryField", fmt.Sprintf("invalid summary field: %s", summaryField))
	}
	if !isValidFieldName(relationField) {
		return nil, pkgerrors.NewValidationError("relationField", fmt.Sprintf("invalid relation field: %s", relationField))
	}

	funcMap := map[string]string{
		RollupTypeCount: RollupTypeCount,
		RollupTypeSum:   RollupTypeSum,
		RollupTypeMin:   RollupTypeMin,
		RollupTypeMax:   RollupTypeMax,
		RollupTypeAvg:   RollupTypeAvg,
	}

	aggFunc, ok := funcMap[strings.ToUpper(calcType)]
	if !ok {
		return nil, pkgerrors.NewValidationError("calcType", fmt.Sprintf("unsupported rollup type: %s", calcType))
	}

	aggExpression := "*"
	if calcType != RollupTypeCount {
		aggExpression = fmt.Sprintf("`%s`", summaryField)
	}

	// Using Builder
	builder := query.From(summaryObject).
		SkipID().
		AddSelectRaw(fmt.Sprintf("%s(%s)", aggFunc, aggExpression)).
		Where(relationField, parentID).
		WhereRaw(fmt.Sprintf("`%s` = ?", constants.ColIsDeleted), []interface{}{false})

	// Add optional filter
	if filter != nil && *filter != "" {
		// Note: Filter safety is validated by Service before passed here,
		// but we trust the inputs at Repository layer generally.
		// Service adds validation.
		builder.WhereRaw(*filter, nil)
	}

	qr := builder.Build()

	// 2. Execute Query
	exec := r.GetExecutor(ctx)
	row := exec.QueryRowContext(ctx, qr.SQL, qr.Params...)

	var scanDest interface{}
	// For SUM/AVG on empty set, SQL returns NULL. We need to handle that.
	var nullFloat sql.NullFloat64
	var nullString sql.NullString // For MIN/MAX on dates/text

	// Decide scan destination based on expected return type from DB perspective
	switch calcType {
	case RollupTypeCount:
		// Count always returns a number (int)
		if err := row.Scan(&scanDest); err != nil {
			return nil, err
		}
		// Convert byte array or other types to int/float if needed, but usually driver handles scalar
		// TiDB/MySQL driver usually returns int64 for COUNT
		return scanDest, nil
	case RollupTypeSum, RollupTypeAvg:
		// Return float/decimal
		if err := row.Scan(&nullFloat); err != nil {
			return nil, err
		}
		if nullFloat.Valid {
			return nullFloat.Float64, nil
		}
		return 0, nil // Default for Sum/Avg is 0
	case RollupTypeMin, RollupTypeMax:
		// Could be number, date, or text
		if err := row.Scan(&nullString); err != nil {
			return nil, err
		}
		if nullString.Valid {
			return nullString.String, nil
		}
		return nil, nil // Default for Min/Max is null
	}

	return 0, nil
}

// UpdateParentRollup updates the target field on the parent record with the calculated value.
func (r *RollupRepository) UpdateParentRollup(ctx context.Context, parentObjName, parentID, targetField string, value interface{}) error {
	if !isValidFieldName(parentObjName) {
		return pkgerrors.NewValidationError("parentObjName", fmt.Sprintf("invalid parent object name: %s", parentObjName))
	}
	if !isValidFieldName(targetField) {
		return pkgerrors.NewValidationError("targetField", fmt.Sprintf("invalid target field: %s", targetField))
	}

	// Update query: UPDATE parent SET field = ? WHERE Id = ?
	qr := query.Update(parentObjName).
		Set(map[string]interface{}{targetField: value}).
		WhereRaw(fmt.Sprintf("`%s` = ?", constants.ColID), []interface{}{parentID}).
		Build()

	// value can be nil, int, float, string
	exec := r.GetExecutor(ctx)
	_, execErr := exec.ExecContext(ctx, qr.SQL, qr.Params...)

	if execErr != nil {
		return execErr
	}
	return nil
}

// GetAllParentIDs retrieves all record IDs for a given object (for bulk recalculation)
func (r *RollupRepository) GetAllParentIDs(ctx context.Context, objectName string) ([]string, error) {
	if !isValidFieldName(objectName) {
		return nil, pkgerrors.NewValidationError("objectName", fmt.Sprintf("invalid object name: %s", objectName))
	}

	qr := query.From(objectName).
		AddSelectRaw(constants.ColID).
		WhereRaw(fmt.Sprintf("`%s` = ?", constants.ColIsDeleted), []interface{}{false}).
		Limit(100000).
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, pkgerrors.NewInternalError("failed to query parent IDs", err)
	}
	defer rows.Close()

	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, pkgerrors.NewInternalError("failed to scan row", err)
		}
		ids = append(ids, id)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return ids, nil
}