package analytics

import (
	"context"
	"fmt"
	"strings"

	"github.com/nexuscrm/backend/internal/domain/ports"
	"github.com/nexuscrm/backend/internal/features/metadata"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/shared/pkg/models"
)

// RollupService handles rollup summary field calculations
type RollupService struct {
	repo      ports.RollupRepository
	metadata  *metadata.MetadataService
	txManager ports.TransactionManager
	logger    *logging.Logger
}

// NewRollupService creates a new RollupService
func NewRollupService(repo ports.RollupRepository, metadata *metadata.MetadataService, txManager ports.TransactionManager, logger *logging.Logger) *RollupService {
	return &RollupService{
		repo:      repo,
		metadata:  metadata,
		txManager: txManager,
		logger:    logger,
	}
}

// ProcessRollups orchestration method.
// The ctx parameter ensures rollup updates participate in the caller's transaction for ACID compliance (if injected).
func (rs *RollupService) ProcessRollups(ctx context.Context, childObjName string, childRecord models.SObject) error {
	affected, err := rs.FindAffectedRollups(ctx, childObjName, childRecord)
	if err != nil {
		return err
	}

	for _, item := range affected {
		newVal, err := rs.CalculateRollup(ctx, item)
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("failed to calculate rollup %s.%s", item.ParentObjName, item.RollupField.APIName), err)
		}

		// Direct Update of Parent via Repository
		if err := rs.repo.UpdateParentRollup(ctx, item.ParentObjName, item.ParentID, item.RollupField.APIName, newVal); err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("failed to update parent rollup %s", item.ParentID), err)
		}
	}
	return nil
}

// AffectedRollup represents a rollup calculation that needs to be performed
type AffectedRollup struct {
	ParentObjName string
	ParentID      string
	RollupField   models.FieldMetadata
}

// FindAffectedRollups identifies parent records that need rollup recalculation
// based on a change to a child record.
func (rs *RollupService) FindAffectedRollups(ctx context.Context, childObjName string, childRecord models.SObject) ([]AffectedRollup, error) {
	var affected []AffectedRollup

	// iterate over all schemas to find objects that have a rollup summary pointing to this child object
	schemas := rs.metadata.GetSchemas(ctx)
	for _, parentSchema := range schemas {
		for _, field := range parentSchema.Fields {
			// Check if it's a Rollup Summary field
			if field.RollupConfig == nil {
				continue
			}

			// Check if it matches this child object
			if field.RollupConfig.SummaryObject != childObjName {
				continue
			}

			// Find the relationship field value on the child record
			relField := field.RollupConfig.RelationshipField
			if relField == "" {
				// Strict: RelationshipField is required on rollup config.
				continue
			}

			// Get Parent ID from Child Record
			parentIDVal, ok := childRecord[relField]
			if !ok || parentIDVal == nil {
				continue // No parent linked, nothing to roll up
			}

			parentID, ok := parentIDVal.(string)
			if !ok || parentID == "" {
				continue
			}

			affected = append(affected, AffectedRollup{
				ParentObjName: parentSchema.APIName,
				ParentID:      parentID,
				RollupField:   field,
			})
		}
	}

	return affected, nil
}

// CalculateRollup performs the aggregation query and returns the value
func (rs *RollupService) CalculateRollup(ctx context.Context, rollup AffectedRollup) (interface{}, error) {
	config := rollup.RollupField.RollupConfig

	// Repo handles defaults: SUM/AVG → 0, MIN/MAX → null.

	// Validate Filter Logic
	if config.Filter != nil && *config.Filter != "" {
		filter := *config.Filter
		// Validate filter contains only safe SQL expression characters
		if !isValidSQLFilter(filter) {
			return nil, apperrors.NewValidationError("Filter", "invalid filter expression: contains potentially unsafe characters")
		}
	}

	return rs.repo.CalculateRollup(
		ctx,
		config.CalcType,
		config.SummaryObject,
		config.SummaryField,
		config.RelationshipField,
		rollup.ParentID,
		config.Filter,
	)
}

// isValidSQLFilter validates that a SQL filter expression contains only safe characters.
// It rejects expressions that could be used for SQL injection attacks.
// Allowed: field names, operators, literals, and common SQL keywords.
func isValidSQLFilter(filter string) bool {
	// Reject empty filters
	if filter == "" {
		return false
	}

	// Reject dangerous SQL keywords/patterns (case-insensitive check)
	lowerFilter := strings.ToLower(filter)
	dangerousPatterns := []string{
		";",                  // Statement terminator
		"--",                 // Comment
		"/*",                 // Block comment
		"drop ",              // DDL
		"delete ",            // DML (not in WHERE context)
		"insert ",            // DML
		"update ",            // DML
		"truncate ",          // DDL
		"alter ",             // DDL
		"create ",            // DDL
		"exec ",              // Execute
		"execute ",           // Execute
		"union ",             // UNION attacks
		"information_schema", // Schema probing
		"sleep(",             // Time-based attacks
		"benchmark(",         // Time-based attacks
		"load_file(",         // File access
		"into outfile",       // File write
		"into dumpfile",      // File write
	}

	for _, pattern := range dangerousPatterns {
		if strings.Contains(lowerFilter, pattern) {
			return false
		}
	}

	return true
}

// RecalculateAll batch-recalculates all rollup fields for a given parent object.
// This is used during data migration to ensure rollup consistency after bulk data load.
// Returns the number of records recalculated.
func (rs *RollupService) RecalculateAll(ctx context.Context, parentObjName string) (int, error) {
	rs.logger.Info(fmt.Sprintf("Starting rollup recalculation for %s", parentObjName))

	// Get schema for this parent object
	schema := rs.metadata.GetSchema(ctx, parentObjName)
	if schema == nil {
		return 0, apperrors.NewNotFoundError("Object", parentObjName)
	}

	// Find all rollup summary fields on this parent object
	var rollupFields []models.FieldMetadata
	for _, field := range schema.Fields {
		if field.RollupConfig != nil {
			rollupFields = append(rollupFields, field)
		}
	}

	if len(rollupFields) == 0 {
		rs.logger.Info(fmt.Sprintf("No rollup summary fields found on %s", parentObjName))
		return 0, nil
	}

	rs.logger.Info(fmt.Sprintf("Found %d rollup summary fields on %s", len(rollupFields), parentObjName))

	// Get all parent IDs that need recalculation
	parentIDs, err := rs.repo.GetAllParentIDs(ctx, parentObjName)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to get parent IDs", err)
	}

	if len(parentIDs) == 0 {
		rs.logger.Info(fmt.Sprintf("No records found in %s", parentObjName))
		return 0, nil
	}

	rs.logger.Info(fmt.Sprintf("Processing %d records in %s", len(parentIDs), parentObjName))

	// Process in batches of 100
	batchSize := 100
	recalculated := 0

	for i := 0; i < len(parentIDs); i += batchSize {
		end := i + batchSize
		if end > len(parentIDs) {
			end = len(parentIDs)
		}
		batch := parentIDs[i:end]
		batchCount := 0

		// Use WithTransaction for batch processing (Context is required for WithTransaction now)
		err := rs.txManager.WithTransaction(ctx, func(txCtx context.Context) error {
			for _, parentID := range batch {
				allRollupsSucceeded := true
				for _, rollupField := range rollupFields {
					rollup := AffectedRollup{
						ParentObjName: parentObjName,
						ParentID:      parentID,
						RollupField:   rollupField,
					}

					newVal, err := rs.CalculateRollup(txCtx, rollup)
					if err != nil {
						rs.logger.Error(fmt.Sprintf("Failed to calculate rollup %s for parent %s", rollupField.APIName, parentID), err)
						allRollupsSucceeded = false
						continue // Skip this rollup but continue
					}

					if err := rs.repo.UpdateParentRollup(txCtx, parentObjName, parentID, rollupField.APIName, newVal); err != nil {
						rs.logger.Error(fmt.Sprintf("Failed to update rollup %s for parent %s", rollupField.APIName, parentID), err)
						allRollupsSucceeded = false
						continue // Skip this rollup but continue
					}
				}
				if allRollupsSucceeded {
					batchCount++
				}
			}
			return nil
		})

		if err != nil {
			rs.logger.Error("Transaction failed for batch", err)
		} else {
			recalculated += batchCount
		}

		rs.logger.Info(fmt.Sprintf("Processed batch %d-%d/%d", i+1, end, len(parentIDs)))
	}

	rs.logger.Info(fmt.Sprintf("RecalculateAll: Completed %d records for %s", recalculated, parentObjName))
	return recalculated, nil
}