package analytics

import (
	"context"
	"reflect"
	"regexp"
	"sort"
	"strings"

	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/kernel/pkg/recordlocks"
	domainSchema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

var (
	rollupUnsafeDottedFilterPath = regexp.MustCompile(`(?i)\b[A-Za-z_][A-Za-z0-9_]*(?:__r)?\.[A-Za-z_]`)
	rollupFilterFunctionCall     = regexp.MustCompile(`(?i)\b([A-Za-z_][A-Za-z0-9_]*)\s*\(`)
)

// Only deterministic filter helpers may keep the update-specific relevance path
// precise. Raw SQL functions outside this list may be volatile or unsupported,
// so they must fail closed to the full parent lock/recompute path.
var rollupDeterministicFilterFunctions = map[string]struct{}{
	"abs":         {},
	"blankvalue":  {},
	"ceil":        {},
	"ceiling":     {},
	"char_length": {},
	"coalesce":    {},
	"concat":      {},
	"date":        {},
	"day":         {},
	"floor":       {},
	"ifnull":      {},
	"isblank":     {},
	"ispickval":   {},
	"isnull":      {},
	"length":      {},
	"lower":       {},
	"ltrim":       {},
	"month":       {},
	"nullif":      {},
	"nullvalue":   {},
	"round":       {},
	"rtrim":       {},
	"substr":      {},
	"substring":   {},
	"text":        {},
	"trim":        {},
	"upper":       {},
	"year":        {},
}

var rollupSQLFilterKeywords = map[string]struct{}{
	"and":      {},
	"as":       {},
	"between":  {},
	"case":     {},
	"distinct": {},
	"else":     {},
	"end":      {},
	"false":    {},
	"in":       {},
	"is":       {},
	"like":     {},
	"not":      {},
	"null":     {},
	"or":       {},
	"then":     {},
	"true":     {},
	"when":     {},
}

type rollupUpdateAffectedSet struct {
	finalAffected []AffectedRollup
	oldAffected   []AffectedRollup
	merged        []AffectedRollup
}

func (rs *RollupService) AffectedParentRowsForUpdate(ctx context.Context, childObjName string, finalRecord, oldRecord models.SObject) ([]recordlocks.ParentKey, error) {
	affected, err := rs.affectedRollupsForUpdate(ctx, childObjName, finalRecord, oldRecord)
	if err != nil {
		return nil, err
	}
	for _, group := range groupAffectedRollupsByParent(affected.merged) {
		if err := rs.augmentRollupHistorySequencePlan(ctx, group.ParentObjName, group.Items); err != nil {
			return nil, err
		}
	}
	keys := make([]recordlocks.ParentKey, 0, len(affected.merged))
	for _, group := range groupAffectedRollupsByParent(affected.merged) {
		keys = append(keys, recordlocks.ParentKey{ObjectName: group.ParentObjName, RecordID: group.ParentID})
	}
	return recordlocks.OrderedParentKeys(keys), nil
}

func (rs *RollupService) affectedRollupsForUpdate(ctx context.Context, childObjName string, finalRecord, oldRecord models.SObject) (rollupUpdateAffectedSet, error) {
	childSchema := rs.metadata.GetSchema(ctx, childObjName)
	changed := rollupUpdateChangedFields(finalRecord, oldRecord)
	deps := rollupChildDependencies(ctx, rs.metadata, childObjName)

	var finalAffected []AffectedRollup
	var oldAffected []AffectedRollup
	for _, dep := range deps {
		if !rollupUpdateRelevanceForDependency(childSchema, dep.RollupField, changed) {
			continue
		}
		if item, ok := affectedRollupForRecord(dep, finalRecord); ok {
			finalAffected = append(finalAffected, item)
		}
		if item, ok := affectedRollupForRecord(dep, oldRecord); ok {
			oldAffected = append(oldAffected, item)
		}
	}

	sort.Sort(affectedRollupByParentKey(finalAffected))
	sort.Sort(affectedRollupByParentKey(oldAffected))
	return rollupUpdateAffectedSet{
		finalAffected: finalAffected,
		oldAffected:   oldAffected,
		merged:        mergeAffectedRollups(finalAffected, oldAffected),
	}, nil
}

func affectedRollupForRecord(dep domainSchema.RollupChildDependency, record models.SObject) (AffectedRollup, bool) {
	config := dep.RollupField.RollupConfig
	if config == nil {
		return AffectedRollup{}, false
	}
	relField := strings.TrimSpace(config.RelationshipField)
	if relField == "" {
		return AffectedRollup{}, false
	}
	parentIDVal, ok := record[relField]
	if !ok || parentIDVal == nil {
		return AffectedRollup{}, false
	}
	parentID, ok := parentIDVal.(string)
	if !ok || strings.TrimSpace(parentID) == "" {
		return AffectedRollup{}, false
	}
	return AffectedRollup{
		ParentObjName: dep.ParentObjectAPIName,
		ParentID:      parentID,
		RollupField:   dep.RollupField,
	}, true
}

func mergeAffectedRollups(first, second []AffectedRollup) []AffectedRollup {
	seen := make(map[affectedRollupDedupKey]struct{}, len(first)+len(second))
	merged := make([]AffectedRollup, 0, len(first)+len(second))
	add := func(item AffectedRollup) {
		key := affectedRollupDedupKey{
			parentObj:   item.ParentObjName,
			parentID:    item.ParentID,
			rollupField: item.RollupField.APIName,
		}
		if _, exists := seen[key]; exists {
			return
		}
		seen[key] = struct{}{}
		merged = append(merged, item)
	}
	for _, item := range first {
		add(item)
	}
	for _, item := range second {
		add(item)
	}
	sort.Sort(affectedRollupByParentKey(merged))
	return merged
}

func rollupUpdateRelevanceForDependency(childSchema *models.ObjectMetadata, rollupField models.FieldMetadata, changed map[string]struct{}) bool {
	config := rollupField.RollupConfig
	if config == nil {
		return false
	}
	if rollupUpdateFieldChanged(changed, config.RelationshipField) {
		return true
	}
	if rollupUpdateFieldChanged(changed, constants.FieldIsDeleted) {
		return true
	}
	filterFields, filterSafe := rollupFilterDependencyFields(config.Filter)
	if !filterSafe {
		return true
	}
	for _, fieldName := range filterFields {
		field := models.FindField(childSchema, fieldName)
		if field == nil || rollupUpdateDependencyFieldUnsafe(field) || rollupUpdateFieldChanged(changed, fieldName) {
			return true
		}
	}

	if strings.EqualFold(strings.TrimSpace(config.CalcType), "COUNT") {
		return false
	}
	summaryField := strings.TrimSpace(config.SummaryField)
	if summaryField == "" || summaryField == "*" {
		return true
	}
	field := models.FindField(childSchema, summaryField)
	if field == nil || rollupUpdateSummarySourceUnsafe(field) {
		return true
	}
	return rollupUpdateFieldChanged(changed, summaryField)
}

func rollupFilterDependencyFields(filter *string) ([]string, bool) {
	if filter == nil || strings.TrimSpace(*filter) == "" {
		return nil, true
	}
	raw := *filter
	if !rollupFilterFunctionCallsSafe(raw) {
		return nil, false
	}
	if rollupUnsafeDottedFilterPath.MatchString(raw) {
		return nil, false
	}
	if strings.Contains(raw, "`") {
		return rollupBacktickedFilterDependencyFields(raw)
	}
	refs, err := formula.CollectCurrentObjectFieldReferences(raw)
	if err != nil {
		return nil, false
	}
	return refs, true
}

func rollupBacktickedFilterDependencyFields(raw string) ([]string, bool) {
	seen := map[string]struct{}{}
	refs := make([]string, 0)
	add := func(ref string) bool {
		ref = strings.TrimSpace(ref)
		if ref == "" || strings.Contains(ref, ".") {
			return false
		}
		key := rollupUpdateFieldKey(ref)
		if _, ok := seen[key]; ok {
			return true
		}
		seen[key] = struct{}{}
		refs = append(refs, ref)
		return true
	}

	for i := 0; i < len(raw); {
		switch raw[i] {
		case '\'', '"':
			quote := raw[i]
			i++
			closed := false
			for i < len(raw) {
				if raw[i] == '\\' && i+1 < len(raw) {
					i += 2
					continue
				}
				if raw[i] == quote {
					i++
					closed = true
					break
				}
				i++
			}
			if !closed {
				return nil, false
			}
		case '`':
			i++
			start := i
			var b strings.Builder
			closed := false
			for i < len(raw) {
				if raw[i] == '`' {
					if i+1 < len(raw) && raw[i+1] == '`' {
						b.WriteString(raw[start:i])
						b.WriteByte('`')
						i += 2
						start = i
						continue
					}
					b.WriteString(raw[start:i])
					if !add(b.String()) {
						return nil, false
					}
					i++
					closed = true
					break
				}
				i++
			}
			if !closed {
				return nil, false
			}
		default:
			if rollupSQLIdentifierStart(raw[i]) {
				start := i
				i++
				for i < len(raw) && rollupSQLIdentifierPart(raw[i]) {
					i++
				}
				if rollupBareSQLIdentifierIsDependency(raw, start, i) && !add(raw[start:i]) {
					return nil, false
				}
				continue
			}
			i++
		}
	}
	if len(refs) == 0 {
		return nil, false
	}
	return refs, true
}

func rollupSQLIdentifierStart(ch byte) bool {
	return ch == '_' || ch >= 'A' && ch <= 'Z' || ch >= 'a' && ch <= 'z'
}

func rollupSQLIdentifierPart(ch byte) bool {
	return rollupSQLIdentifierStart(ch) || ch >= '0' && ch <= '9'
}

func rollupBareSQLIdentifierIsDependency(raw string, start, end int) bool {
	token := strings.ToLower(strings.TrimSpace(raw[start:end]))
	if token == "" {
		return false
	}
	if _, ok := rollupSQLFilterKeywords[token]; ok {
		return false
	}
	for i := end; i < len(raw); i++ {
		if raw[i] == ' ' || raw[i] == '\t' || raw[i] == '\n' || raw[i] == '\r' {
			continue
		}
		return raw[i] != '('
	}
	return true
}

func rollupFilterFunctionCallsSafe(raw string) bool {
	for _, match := range rollupFilterFunctionCall.FindAllStringSubmatch(raw, -1) {
		if len(match) < 2 {
			continue
		}
		name := strings.ToLower(strings.TrimSpace(match[1]))
		if name == "in" {
			continue
		}
		if _, ok := rollupDeterministicFilterFunctions[name]; !ok {
			return false
		}
	}
	return true
}

func rollupUpdateDependencyFieldUnsafe(field *models.FieldMetadata) bool {
	if field == nil {
		return true
	}
	if rollupSystemGeneratedUpdateDependency(field.APIName) {
		return true
	}
	return rollupUpdateSummarySourceUnsafe(field)
}

func rollupUpdateSummarySourceUnsafe(field *models.FieldMetadata) bool {
	if field == nil {
		return true
	}
	if rollupSystemGeneratedUpdateDependency(field.APIName) {
		return true
	}
	if field.IsVirtual || field.ProjectionConfig != nil || field.Formula != nil {
		return true
	}
	if strings.EqualFold(string(constants.NormalizeFieldType(string(field.Type))), string(constants.FieldTypeFormula)) {
		return true
	}
	return formulastorage.IsActiveComputedField(field)
}

func rollupSystemGeneratedUpdateDependency(fieldName string) bool {
	switch strings.ToLower(strings.TrimSpace(fieldName)) {
	case strings.ToLower(constants.FieldLastModifiedDate),
		strings.ToLower(constants.FieldLastModifiedByID),
		strings.ToLower(constants.FieldSystemModstamp):
		return true
	default:
		return false
	}
}

func rollupUpdateChangedFields(finalRecord, oldRecord models.SObject) map[string]struct{} {
	changed := map[string]struct{}{}
	for key, finalValue := range finalRecord {
		if oldValue, ok := oldRecord[key]; !ok || !reflect.DeepEqual(oldValue, finalValue) {
			changed[rollupUpdateFieldKey(key)] = struct{}{}
		}
	}
	for key, oldValue := range oldRecord {
		if finalValue, ok := finalRecord[key]; !ok || !reflect.DeepEqual(oldValue, finalValue) {
			changed[rollupUpdateFieldKey(key)] = struct{}{}
		}
	}
	return changed
}

func rollupUpdateFieldChanged(changed map[string]struct{}, fieldName string) bool {
	if len(changed) == 0 {
		return false
	}
	_, ok := changed[rollupUpdateFieldKey(fieldName)]
	return ok
}

func rollupUpdateFieldKey(fieldName string) string {
	return strings.ToLower(strings.TrimSpace(fieldName))
}