package catalogreconcile

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

	"github.com/nexuscrm/db/pkg/connectorreaders"
	"github.com/pingcap-inc/nexuscrm-contract/pkg/seedrows"
)

type effectiveStandardActionRow struct {
	id        string
	ownerPack string
	active    bool
	deleted   bool
	def       seedrows.StandardActionDef
}

func (r *RepositoryMetadataReader) GetEffectiveStandardActions(
	ctx context.Context,
	anchorNames []string,
) ([]seedrows.StandardActionDef, error) {
	if r == nil || r.MetadataRepository == nil || len(anchorNames) == 0 {
		return nil, fmt.Errorf("effective standard-action owner anchors are required")
	}
	executor := r.GetExecutor(ctx)
	rows, err := executor.QueryContext(ctx, `
SELECT Id, Name, Label, COALESCE(Description, ''), FlowDeveloperName,
       COALESCE(Category, ''), COALESCE(RequiredPermissions, '[]'),
       COALESCE(TargetObjects, '[]'), COALESCE(RendererId, ''),
       IsAtomic, SortOrder, COALESCE(OwnerPack, ''),
       COALESCE(VisibilityCondition, ''),
       COALESCE(PreviewFlowDeveloperName, ''),
       HeaderFallbackEligible, IsActive, IsDeleted
FROM _System_StandardAction
ORDER BY Name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	all := make([]effectiveStandardActionRow, 0)
	byName := make(map[string]int)
	for rows.Next() {
		var (
			row                                      effectiveStandardActionRow
			requiredPermissionsRaw, targetObjectsRaw string
			visibility, preview                      string
			atomic, headerFallback, active, deleted  int
		)
		if err := rows.Scan(
			&row.id, &row.def.Name, &row.def.Label, &row.def.Description,
			&row.def.FlowDeveloperName, &row.def.Category,
			&requiredPermissionsRaw, &targetObjectsRaw, &row.def.RendererID,
			&atomic, &row.def.SortOrder, &row.ownerPack, &visibility, &preview,
			&headerFallback, &active, &deleted,
		); err != nil {
			return nil, err
		}
		if err := json.Unmarshal([]byte(requiredPermissionsRaw), &row.def.RequiredPermissions); err != nil {
			return nil, fmt.Errorf("decode standard action %q required permissions: %w", row.def.Name, err)
		}
		if err := json.Unmarshal([]byte(targetObjectsRaw), &row.def.TargetObjects); err != nil {
			return nil, fmt.Errorf("decode standard action %q target objects: %w", row.def.Name, err)
		}
		row.def.IsAtomic = atomic != 0
		row.active = active != 0
		row.deleted = deleted != 0
		headerValue := headerFallback != 0
		row.def.HeaderFallbackEligible = &headerValue
		if visibility != "" {
			row.def.VisibilityCondition = &visibility
		}
		if preview != "" {
			row.def.PreviewFlowDeveloperName = &preview
		}
		key := strings.ToLower(strings.TrimSpace(row.def.Name))
		if key == "" {
			return nil, fmt.Errorf("effective standard-action row has empty name")
		}
		if _, duplicate := byName[key]; duplicate {
			return nil, fmt.Errorf("effective standard-action rows contain duplicate name %q", row.def.Name)
		}
		byName[key] = len(all)
		all = append(all, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	var ownerPack string
	for _, name := range anchorNames {
		index, found := byName[strings.ToLower(strings.TrimSpace(name))]
		if !found {
			return nil, fmt.Errorf("AppRelease standard-action owner anchor %q is absent", name)
		}
		candidate := strings.TrimSpace(all[index].ownerPack)
		if candidate == "" {
			return nil, fmt.Errorf("AppRelease standard-action owner anchor %q has no pack owner", name)
		}
		if ownerPack == "" {
			ownerPack = candidate
		} else if ownerPack != candidate {
			return nil, fmt.Errorf("AppRelease standard-action owner anchors span multiple packs")
		}
	}

	selected := make([]*effectiveStandardActionRow, 0)
	selectedIDs := make(map[string]*effectiveStandardActionRow)
	for index := range all {
		row := &all[index]
		if row.ownerPack != ownerPack || !row.active || row.deleted {
			continue
		}
		if _, duplicate := selectedIDs[row.id]; duplicate {
			return nil, fmt.Errorf("effective standard-action rows contain duplicate id %q", row.id)
		}
		selectedIDs[row.id] = row
		selected = append(selected, row)
	}
	if len(selected) == 0 {
		return nil, fmt.Errorf("effective standard-action owner %q has no active definitions", ownerPack)
	}
	if err := readEffectiveStandardActionSchema(ctx, executor, selectedIDs); err != nil {
		return nil, err
	}
	if err := readEffectiveStandardActionDefaults(ctx, executor, selectedIDs); err != nil {
		return nil, err
	}
	if err := readEffectiveStandardActionGuards(ctx, executor, selectedIDs); err != nil {
		return nil, err
	}
	if err := readEffectiveStandardActionScopes(ctx, executor, selectedIDs); err != nil {
		return nil, err
	}
	if err := enrichEffectiveStandardActionPrefills(ctx, executor, selectedIDs); err != nil {
		return nil, err
	}
	result := make([]seedrows.StandardActionDef, 0, len(selected))
	for _, row := range selected {
		sort.Slice(row.def.Inputs, func(left, right int) bool {
			if row.def.Inputs[left].SortOrder != row.def.Inputs[right].SortOrder {
				return row.def.Inputs[left].SortOrder < row.def.Inputs[right].SortOrder
			}
			return row.def.Inputs[left].APIName < row.def.Inputs[right].APIName
		})
		sort.Slice(row.def.Outputs, func(left, right int) bool {
			if row.def.Outputs[left].SortOrder != row.def.Outputs[right].SortOrder {
				return row.def.Outputs[left].SortOrder < row.def.Outputs[right].SortOrder
			}
			return row.def.Outputs[left].APIName < row.def.Outputs[right].APIName
		})
		sort.Slice(row.def.FieldDefaults, func(left, right int) bool {
			if row.def.FieldDefaults[left].Priority != row.def.FieldDefaults[right].Priority {
				return row.def.FieldDefaults[left].Priority < row.def.FieldDefaults[right].Priority
			}
			leftKey := row.def.FieldDefaults[left].TargetObject + "\x00" + row.def.FieldDefaults[left].FieldAPIName
			rightKey := row.def.FieldDefaults[right].TargetObject + "\x00" + row.def.FieldDefaults[right].FieldAPIName
			return leftKey < rightKey
		})
		sort.Slice(row.def.InputGuards, func(left, right int) bool {
			if row.def.InputGuards[left].SortOrder != row.def.InputGuards[right].SortOrder {
				return row.def.InputGuards[left].SortOrder < row.def.InputGuards[right].SortOrder
			}
			return row.def.InputGuards[left].Condition < row.def.InputGuards[right].Condition
		})
		sort.Slice(row.def.InternalAccessScopes, func(left, right int) bool {
			leftKey := row.def.InternalAccessScopes[left].ObjectAPIName + "\x00" + row.def.InternalAccessScopes[left].Operation
			rightKey := row.def.InternalAccessScopes[right].ObjectAPIName + "\x00" + row.def.InternalAccessScopes[right].Operation
			return leftKey < rightKey
		})
		result = append(result, row.def)
	}
	sort.Slice(result, func(left, right int) bool {
		return strings.ToLower(result[left].Name) < strings.ToLower(result[right].Name)
	})
	return result, nil
}

func enrichEffectiveStandardActionPrefills(
	ctx context.Context,
	executor queryExecutor,
	selected map[string]*effectiveStandardActionRow,
) error {
	needsMappings := false
	for _, row := range selected {
		for _, fields := range [][]seedrows.StandardActionSchemaDef{row.def.Inputs, row.def.Outputs} {
			for _, field := range fields {
				if effectiveStandardActionFieldNeedsPrefill(field) {
					needsMappings = true
					break
				}
			}
			if needsMappings {
				break
			}
		}
		if needsMappings {
			break
		}
	}
	if !needsMappings {
		return nil
	}

	// Reuse the complete transaction-bound closure consumed by Catalog instead
	// of querying one mapping per action input. Its stable order already applies
	// the runtime owner/customization precedence, so the first source for one
	// source-object/target-object/action/target-field tuple is authoritative.
	mappings, err := connectorreaders.GetAllFieldMappingsContext(ctx, executor)
	if err != nil {
		return fmt.Errorf("derive effective standard-action prefills: %w", err)
	}
	prefillByTarget := make(map[string]string, len(mappings))
	for _, mapping := range mappings {
		key := effectiveStandardActionMappingTargetKey(
			mapping.SourceObject,
			mapping.TargetObject,
			mapping.ActionType,
			mapping.TargetField,
		)
		if mapping.SourceField == "" {
			continue
		}
		if _, exists := prefillByTarget[key]; !exists {
			prefillByTarget[key] = mapping.SourceField
		}
	}

	for _, row := range selected {
		enrichEffectiveStandardActionFields(row.def.Inputs, prefillByTarget)
		enrichEffectiveStandardActionFields(row.def.Outputs, prefillByTarget)
	}
	return nil
}

func enrichEffectiveStandardActionFields(
	fields []seedrows.StandardActionSchemaDef,
	prefillByTarget map[string]string,
) {
	for index := range fields {
		field := &fields[index]
		if !effectiveStandardActionFieldNeedsPrefill(*field) {
			continue
		}
		field.PrefillSourceFieldAPIName = prefillByTarget[effectiveStandardActionMappingTargetKey(
			field.MappingSourceObject,
			field.TargetObject,
			field.MappingActionType,
			field.TargetFieldAPIName,
		)]
	}
}

func effectiveStandardActionFieldNeedsPrefill(field seedrows.StandardActionSchemaDef) bool {
	return field.PrefillSourceFieldAPIName == "" &&
		field.MappingSourceObject != "" &&
		field.TargetObject != "" &&
		field.MappingActionType != "" &&
		field.TargetFieldAPIName != ""
}

func effectiveStandardActionMappingTargetKey(
	sourceObject string,
	targetObject string,
	actionType string,
	targetField string,
) string {
	return strings.ToLower(strings.Join([]string{
		strings.TrimSpace(sourceObject),
		strings.TrimSpace(targetObject),
		strings.TrimSpace(actionType),
		strings.TrimSpace(targetField),
	}, "\x00"))
}

type queryExecutor interface {
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
}

func readEffectiveStandardActionSchema(
	ctx context.Context,
	executor queryExecutor,
	selected map[string]*effectiveStandardActionRow,
) error {
	rows, err := executor.QueryContext(ctx, `
SELECT StandardActionId, Direction, APIName, DataType, COALESCE(ReferenceTo, ''),
       COALESCE(Label, ''), COALESCE(UISection, ''), COALESCE(UIMode, ''),
       COALESCE(TargetObject, ''), COALESCE(TargetFieldAPIName, ''),
       COALESCE(PrefillSourceFieldAPIName, ''), COALESCE(RequiredPicklistTag, ''),
       COALESCE(MappingSourceObject, ''), COALESCE(MappingActionType, ''),
       IsCollection, IsRequired, COALESCE(Description, ''), SortOrder
FROM _System_ActionSchema
WHERE IsActive = 1 AND IsDeleted = 0
ORDER BY StandardActionId, Direction, SortOrder, APIName`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var actionID, direction string
		var value seedrows.StandardActionSchemaDef
		var collection, required int
		if err := rows.Scan(
			&actionID, &direction, &value.APIName, &value.DataType,
			&value.ReferenceTo, &value.Label, &value.UISection, &value.UIMode,
			&value.TargetObject, &value.TargetFieldAPIName,
			&value.PrefillSourceFieldAPIName, &value.RequiredPicklistTag,
			&value.MappingSourceObject, &value.MappingActionType,
			&collection, &required, &value.Description, &value.SortOrder,
		); err != nil {
			return err
		}
		parent := selected[actionID]
		if parent == nil {
			continue
		}
		value.IsCollection = collection != 0
		value.IsRequired = required != 0
		switch direction {
		case "input":
			parent.def.Inputs = append(parent.def.Inputs, value)
		case "output":
			parent.def.Outputs = append(parent.def.Outputs, value)
		default:
			return fmt.Errorf("standard action %q has unsupported schema direction %q", parent.def.Name, direction)
		}
	}
	return rows.Err()
}

func readEffectiveStandardActionDefaults(
	ctx context.Context,
	executor queryExecutor,
	selected map[string]*effectiveStandardActionRow,
) error {
	rows, err := executor.QueryContext(ctx, `
SELECT d.StandardActionId, d.TargetObject, d.FieldAPIName, d.ValueFormula,
       COALESCE(d.Condition, ''), d.Priority
FROM _System_ActionFieldDefault d
WHERE d.IsActive = 1 AND d.IsDeleted = 0
ORDER BY d.StandardActionId, d.Priority, d.TargetObject, d.FieldAPIName`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var actionID string
		var value seedrows.StandardActionDefaultDef
		if err := rows.Scan(
			&actionID, &value.TargetObject, &value.FieldAPIName,
			&value.ValueFormula, &value.Condition, &value.Priority,
		); err != nil {
			return err
		}
		if parent := selected[actionID]; parent != nil {
			parent.def.FieldDefaults = append(parent.def.FieldDefaults, value)
		}
	}
	return rows.Err()
}

func readEffectiveStandardActionGuards(
	ctx context.Context,
	executor queryExecutor,
	selected map[string]*effectiveStandardActionRow,
) error {
	rows, err := executor.QueryContext(ctx, `
SELECT g.StandardActionId, g.Condition, COALESCE(g.RequiredPermission, ''),
       COALESCE(g.RequiredObject, ''), COALESCE(g.RequiredOperation, ''),
       COALESCE(g.ErrorMessage, ''), COALESCE(g.GatedInput, ''), g.SortOrder
FROM _System_ActionInputGuard g
WHERE g.IsActive = 1 AND g.IsDeleted = 0
ORDER BY g.StandardActionId, g.SortOrder, g.Condition`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var actionID string
		var value seedrows.StandardActionGuardDef
		if err := rows.Scan(
			&actionID, &value.Condition, &value.RequiredPermission,
			&value.RequiredObject, &value.RequiredOperation, &value.ErrorMessage,
			&value.GatedInput, &value.SortOrder,
		); err != nil {
			return err
		}
		if parent := selected[actionID]; parent != nil {
			parent.def.InputGuards = append(parent.def.InputGuards, value)
		}
	}
	return rows.Err()
}

func readEffectiveStandardActionScopes(
	ctx context.Context,
	executor queryExecutor,
	selected map[string]*effectiveStandardActionRow,
) error {
	rows, err := executor.QueryContext(ctx, `
SELECT StandardActionId, ObjectApiName, Operation, COALESCE(Reason, '')
FROM _System_StandardActionInternalAccessScope
WHERE IsActive = 1 AND IsDeleted = 0
ORDER BY StandardActionId, ObjectApiName, Operation`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var actionID string
		var value seedrows.StandardActionInternalAccessScopeDef
		if err := rows.Scan(
			&actionID, &value.ObjectAPIName, &value.Operation, &value.Reason,
		); err != nil {
			return err
		}
		if parent := selected[actionID]; parent != nil {
			parent.def.InternalAccessScopes = append(parent.def.InternalAccessScopes, value)
		}
	}
	return rows.Err()
}

var _ EffectiveStandardActionMetadataReader = (*RepositoryMetadataReader)(nil)