package createsourceguard

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

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

type standardActionFlowForCreateSource struct {
	ID       string
	Name     string
	Metadata string
}

type createFromSourceFlowOwnerKey struct {
	sourceObject string
	targetObject string
	actionType   string
}

type createFromSourceFlowOwner struct {
	ID                string
	Name              string
	assignedFields    map[string]string
	allAssignedFields map[string]string
}

func getActiveStandardActionFlowsForCreateSource(ctx context.Context, queryer Queryer) ([]standardActionFlowForCreateSource, error) {
	query := fmt.Sprintf(`
SELECT sa.Id,
       sa.Name,
       f.Metadata
  FROM %s AS sa
  JOIN %s AS fd
    ON fd.DeveloperName = sa.FlowDeveloperName
   AND fd.IsDeleted = 0
  JOIN %s AS f
    ON f.Id = fd.ActiveVersionId
   AND f.IsDeleted = 0
 WHERE sa.IsActive = 1 AND sa.IsDeleted = 0
   AND sa.FlowDeveloperName IS NOT NULL
   AND sa.FlowDeveloperName <> ''`,
		constants.TableStandardAction, constants.TableFlowDefinition, constants.TableFlow,
	)
	rows, err := queryer.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query active standard action flows for create-from-source guard: %w", err)
	}
	defer rows.Close()

	var out []standardActionFlowForCreateSource
	for rows.Next() {
		var id, name, metadata sql.NullString
		if err := rows.Scan(&id, &name, &metadata); err != nil {
			return nil, fmt.Errorf("could not scan active standard action flow for create-from-source guard: %w", err)
		}
		out = append(out, standardActionFlowForCreateSource{
			ID:       strings.TrimSpace(id.String),
			Name:     strings.TrimSpace(name.String),
			Metadata: metadata.String,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("could not iterate active standard action flows for create-from-source guard: %w", err)
	}
	return out, nil
}

func buildCreateFromSourceFlowOwnerIndex(flows []standardActionFlowForCreateSource) map[createFromSourceFlowOwnerKey][]createFromSourceFlowOwner {
	out := make(map[createFromSourceFlowOwnerKey][]createFromSourceFlowOwner)
	for _, flow := range flows {
		if flow.ID == "" || flow.Name == "" || strings.TrimSpace(flow.Metadata) == "" {
			continue
		}
		var metadata models.FlowMetadata
		if err := json.Unmarshal([]byte(flow.Metadata), &metadata); err != nil {
			continue
		}
		for _, create := range metadata.RecordCreates {
			ref := create.FieldMapping
			if ref == nil {
				continue
			}
			targetObject := strings.TrimSpace(ref.TargetObject)
			if targetObject == "" {
				targetObject = strings.TrimSpace(create.Object)
			}
			key := createFromSourceFlowOwnerKeyForTuple(ref.SourceObject, targetObject, ref.ActionType)
			if key.sourceObject == "" || key.targetObject == "" || key.actionType == "" {
				continue
			}
			out[key] = append(out[key], createFromSourceFlowOwner{
				ID:                flow.ID,
				Name:              flow.Name,
				assignedFields:    flowRecordCreateSourceBoundAssignedFieldSet(metadata, create),
				allAssignedFields: flowRecordCreateAssignedFieldSet(create),
			})
		}
	}
	return out
}

func createFromSourceFlowOwnerKeyForMapping(mapping effectiveCreateFromSourceMapping) createFromSourceFlowOwnerKey {
	return createFromSourceFlowOwnerKeyForTuple(mapping.SourceObject, mapping.TargetObject, mapping.ActionType)
}

func createFromSourceFlowOwnerKeyForTuple(sourceObject, targetObject, actionType string) createFromSourceFlowOwnerKey {
	return createFromSourceFlowOwnerKey{
		sourceObject: strings.ToLower(strings.TrimSpace(sourceObject)),
		targetObject: strings.ToLower(strings.TrimSpace(targetObject)),
		actionType:   strings.TrimSpace(actionType),
	}
}

func flowRecordCreateSourceBoundAssignedFieldSet(metadata models.FlowMetadata, create models.FlowRecordCreate) map[string]string {
	if create.FieldMapping == nil {
		return nil
	}
	sourceIDRefs := flowFieldMappingSourceIDReferences(metadata, create.FieldMapping)
	if len(sourceIDRefs) == 0 {
		return nil
	}
	out := make(map[string]string, len(create.InputAssignments))
	for _, assignment := range create.InputAssignments {
		field := strings.TrimSpace(assignment.Field)
		if field == "" {
			continue
		}
		ref, ok := flowElementReference(assignment.Value)
		if !ok {
			continue
		}
		if _, sourceBound := sourceIDRefs[normalizeFlowElementReference(ref)]; !sourceBound {
			continue
		}
		out[strings.ToLower(field)] = field
	}
	return out
}

func flowRecordCreateAssignedFieldSet(create models.FlowRecordCreate) map[string]string {
	out := make(map[string]string, len(create.InputAssignments))
	for _, assignment := range create.InputAssignments {
		field := strings.TrimSpace(assignment.Field)
		if field == "" {
			continue
		}
		out[strings.ToLower(field)] = field
	}
	return out
}

func flowFieldMappingSourceIDReferences(metadata models.FlowMetadata, ref *models.FlowFieldMappingRef) map[string]struct{} {
	out := map[string]struct{}{}
	if ref == nil {
		return out
	}
	sourceObject := strings.TrimSpace(ref.SourceObject)
	sourceReference := strings.TrimSpace(ref.SourceReference)
	if sourceReference != "" {
		out[normalizeFlowElementReference(sourceReference+".Id")] = struct{}{}
	}
	for _, lookup := range metadata.RecordLookups {
		if sourceObject != "" && !strings.EqualFold(strings.TrimSpace(lookup.Object), sourceObject) {
			continue
		}
		if sourceReference != "" && !flowReferencesEqual(lookup.OutputReference, sourceReference) {
			continue
		}
		for _, filter := range lookup.Filters {
			if !strings.EqualFold(strings.TrimSpace(filter.Field), constants.FieldID) ||
				!strings.EqualFold(strings.TrimSpace(filter.Operator), "EqualTo") {
				continue
			}
			sourceIDRef, ok := flowElementReference(filter.Value)
			if !ok {
				continue
			}
			out[normalizeFlowElementReference(sourceIDRef)] = struct{}{}
		}
	}
	return out
}

func flowElementReference(value interface{}) (string, bool) {
	m, ok := value.(map[string]interface{})
	if !ok {
		return "", false
	}
	ref, ok := m["elementReference"].(string)
	if !ok {
		return "", false
	}
	ref = strings.TrimSpace(ref)
	if ref == "" {
		return "", false
	}
	return ref, true
}

func flowReferencesEqual(a, b string) bool {
	return normalizeFlowElementReference(a) == normalizeFlowElementReference(b)
}

func normalizeFlowElementReference(ref string) string {
	return strings.TrimPrefix(strings.TrimSpace(ref), "$")
}

func flowOwnerOwnsCreateFromSourceMapping(owner createFromSourceFlowOwner, mapping effectiveCreateFromSourceMapping) bool {
	mapping.SourceLookupField = strings.TrimSpace(mapping.SourceLookupField)
	if mapping.SourceLookupField == "" {
		return false
	}
	if mapping.FieldMappingWritesSourceLookup {
		return true
	}
	_, ok := owner.assignedFields[strings.ToLower(mapping.SourceLookupField)]
	return ok
}