package connectorreaders

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

	connector "github.com/pingcap-inc/nexuscrm-contract/pkg/connector"
)

// EffectiveFieldMapping is the complete business-key context plus the
// precedence-resolved mapping consumed by Catalog. It is generic metadata, not
// connector/provider configuration.
type EffectiveFieldMapping struct {
	SourceObject      string
	TargetObject      string
	ActionType        string
	SourceField       string
	TargetField       string
	DefaultValue      string
	ValueMap          string
	OnUnmappedValue   string
	SourceLookupField string
}

// GetAllFieldMappingsContext resolves every active legacy mapping context in
// one transaction-bound read. Its per-context source-field precedence is the
// same as GetFieldMappings; Catalog uses it to compile one immutable revision
// instead of letting Runtime query the live table.
func GetAllFieldMappingsContext(
	ctx context.Context,
	exec interface {
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	},
) ([]EffectiveFieldMapping, error) {
	query := fmt.Sprintf(
		`SELECT SourceObject, TargetObject, ActionType, Source, SourceField, TargetField, DefaultValue, ValueMap, OnUnmappedValue, SourceLookupField
		 FROM %s
		 WHERE IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		   AND Direction = 'legacy'
		 ORDER BY SourceObject ASC, TargetObject ASC, ActionType ASC, %s, SortOrder ASC, SourceField ASC, TargetField ASC`,
		tableObjectFieldMapping,
		fieldMappingSourcePrecedenceSQL(),
	)
	rows, err := exec.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("failed to query complete field mapping closure: %w", err)
	}
	defer rows.Close()
	result := make([]EffectiveFieldMapping, 0)
	seen := make(map[string]struct{})
	for rows.Next() {
		var value EffectiveFieldMapping
		var source string
		var defaultValue, valueMap, onUnmappedValue, sourceLookup sql.NullString
		if err := rows.Scan(&value.SourceObject, &value.TargetObject, &value.ActionType, &source,
			&value.SourceField, &value.TargetField, &defaultValue, &valueMap, &onUnmappedValue, &sourceLookup); err != nil {
			return nil, fmt.Errorf("failed to scan complete field mapping closure: %w", err)
		}
		key := value.SourceObject + "\x00" + value.TargetObject + "\x00" + value.ActionType + "\x00" + value.SourceField
		if _, exists := seen[key]; exists {
			continue
		}
		seen[key] = struct{}{}
		if defaultValue.Valid {
			value.DefaultValue = defaultValue.String
		}
		if valueMap.Valid {
			value.ValueMap = valueMap.String
		}
		if onUnmappedValue.Valid {
			value.OnUnmappedValue = onUnmappedValue.String
		}
		if sourceLookup.Valid {
			value.SourceLookupField = sourceLookup.String
		}
		result = append(result, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate complete field mapping closure: %w", err)
	}
	return result, nil
}

// GetAllInboundConnectorFieldMappingsContext resolves the complete effective
// inbound connector mapping closure in one transaction-bound read. Exact
// inbound rows take precedence over bidirectional rows for the same source
// field. Legacy and outbound-only rows are deliberately excluded: provider
// effects remain owned by Integration.
func GetAllInboundConnectorFieldMappingsContext(
	ctx context.Context,
	exec interface {
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	},
) ([]connector.ConnectorFieldMappingInput, error) {
	return getAllInboundConnectorFieldMappingsContext(ctx, exec, false, "")
}

// GetAllPackInboundConnectorFieldMappingsForOwnerContext resolves one
// source-backed, non-customized inbound owner closure used by trusted
// pack-owned Flows. The verified release owner is explicit: IsCustomized=0
// alone is not evidence that a row belongs to the requesting pack.
func GetAllPackInboundConnectorFieldMappingsForOwnerContext(
	ctx context.Context,
	exec interface {
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	},
	owner string,
) ([]connector.ConnectorFieldMappingInput, error) {
	owner = strings.TrimSpace(owner)
	if owner == "" {
		return nil, fmt.Errorf("complete pack-authored inbound connector field mapping owner is required")
	}
	return getAllInboundConnectorFieldMappingsContext(ctx, exec, true, owner)
}

func getAllInboundConnectorFieldMappingsContext(
	ctx context.Context,
	exec interface {
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	},
	packAuthored bool,
	owner string,
) ([]connector.ConnectorFieldMappingInput, error) {
	customizationPredicate := ""
	sourcePredicate := "AND COALESCE(Source, '') <> ''"
	precedence := fieldMappingSourcePrecedenceSQL()
	args := []interface{}{}
	if packAuthored {
		customizationPredicate = "AND COALESCE(IsCustomized, 0) = 0"
		sourcePredicate = "AND Source = ?"
		precedence = "Source ASC"
		args = append(args, owner)
	}
	query := fmt.Sprintf(
		`SELECT Source, SourceObject, TargetObject, ActionType, SourceField,
		        TargetField, DefaultValue, SortOrder, Direction,
		        TransformExpression, ConflictPolicy, ValueMap, OnUnmappedValue,
		        SourceLookupField
		 FROM %s
		 WHERE IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		   AND Direction IN ('%s', '%s')
		   %s
		   %s
		 ORDER BY Source ASC, SourceObject ASC, TargetObject ASC, ActionType ASC,
		          (CASE WHEN Direction = '%s' THEN 1 ELSE 0 END) ASC,
		          %s, SortOrder ASC, SourceField ASC, TargetField ASC`,
		tableObjectFieldMapping,
		directionInbound,
		directionBidirectional,
		sourcePredicate,
		customizationPredicate,
		directionBidirectional,
		precedence,
	)
	rows, err := exec.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query complete inbound connector field mapping closure (packAuthored=%t): %w", packAuthored, err)
	}
	defer rows.Close()

	result := make([]connector.ConnectorFieldMappingInput, 0)
	seenSourceFields := make(map[string]struct{})
	for rows.Next() {
		var value connector.ConnectorFieldMappingInput
		var defaultValue, transformExpression, conflictPolicy, valueMap, onUnmappedValue, sourceLookupField sql.NullString
		if err := rows.Scan(
			&value.Source, &value.SourceObject, &value.TargetObject, &value.ActionType,
			&value.SourceField, &value.TargetField, &defaultValue, &value.SortOrder,
			&value.Direction, &transformExpression, &conflictPolicy, &valueMap,
			&onUnmappedValue, &sourceLookupField,
		); err != nil {
			return nil, fmt.Errorf("failed to scan complete inbound connector field mapping closure: %w", err)
		}
		if sourceLookupField.Valid && strings.TrimSpace(sourceLookupField.String) != "" {
			return nil, fmt.Errorf("inbound connector field mapping contains legacy source lookup field")
		}
		key := strings.Join([]string{
			connectorFieldMappingKeySegment(value.Source),
			connectorFieldMappingKeySegment(value.SourceObject),
			connectorFieldMappingKeySegment(value.TargetObject),
			connectorFieldMappingKeySegment(value.ActionType),
			connectorFieldMappingKeySegment(value.SourceField),
		}, "\x00")
		if _, exists := seenSourceFields[key]; exists {
			continue
		}
		seenSourceFields[key] = struct{}{}
		if defaultValue.Valid {
			value.DefaultValue = defaultValue.String
		}
		if transformExpression.Valid {
			value.TransformExpression = transformExpression.String
		}
		if conflictPolicy.Valid {
			value.ConflictPolicy = conflictPolicy.String
		}
		if valueMap.Valid {
			value.ValueMap = valueMap.String
		}
		if onUnmappedValue.Valid {
			value.OnUnmappedValue = onUnmappedValue.String
		}
		value.Direction = directionInbound
		result = append(result, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate complete inbound connector field mapping closure (packAuthored=%t): %w", packAuthored, err)
	}
	if err := connector.ValidateConnectorFieldMappingInputSet(result); err != nil {
		return nil, fmt.Errorf("validate complete inbound connector field mapping closure (packAuthored=%t): %w", packAuthored, err)
	}
	return result, nil
}

// tableObjectFieldMapping is the physical table backing object field mappings.
// Inlined here (exact value "_System_ObjectFieldMapping") to keep this package
// stdlib-only; it mirrors the bootstrap local const of the same name.
const tableObjectFieldMapping = "_System_ObjectFieldMapping"

func fieldMappingSourcePrecedenceSQL() string {
	// IsCustomized is the admin override signal. Upgraded admin-authored rows
	// may still carry Source='legacy', so runtime readers must prefer them over
	// seed-owned rows instead of relying on owner alone.
	return `CASE
		   WHEN IsCustomized = 1 THEN 0
		   WHEN Source LIKE 'tenant:%' THEN 1
		   WHEN Source NOT IN ('standard', 'legacy') THEN 2
		   WHEN Source = 'standard' THEN 3
		   WHEN Source = 'legacy' THEN 4
		   ELSE 5
		 END, Source ASC`
}

// GetFieldMappingsByTargetContext returns every active row in
// _System_ObjectFieldMapping for (targetObject, actionType), preserving
// SourceObject on each row. Consumers group rows by SourceObject +
// SourceLookupField to decide which parent record to retrieve per group.
func GetFieldMappingsByTargetContext(
	ctx context.Context,
	exec interface {
		QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	},
	targetObject,
	actionType string,
) ([]connector.FieldMappingWithSource, error) {
	query := fmt.Sprintf(
		`SELECT Source, SourceObject, SourceField, TargetField, DefaultValue, SourceLookupField
			 FROM %s
			 WHERE TargetObject = ? AND ActionType = ?
		   AND IsActive = 1 AND IsDeleted = 0
		   AND COALESCE(ProjectionOnly, 0) = 0
		   AND Direction = 'legacy'
		 ORDER BY %s, SortOrder ASC, SourceObject ASC, SourceField ASC, TargetField ASC`,
		tableObjectFieldMapping,
		fieldMappingSourcePrecedenceSQL(),
	)

	rows, err := exec.QueryContext(ctx, query, targetObject, actionType)
	if err != nil {
		return nil, fmt.Errorf("failed to query field mappings by target: %w", err)
	}
	defer rows.Close()

	var mappings []connector.FieldMappingWithSource
	seen := map[string]bool{}
	for rows.Next() {
		var m connector.FieldMappingWithSource
		var source string
		var defaultVal, sourceLookup sql.NullString
		if err := rows.Scan(&source, &m.SourceObject, &m.SourceField, &m.TargetField, &defaultVal, &sourceLookup); err != nil {
			return nil, fmt.Errorf("failed to scan field mapping by target: %w", err)
		}
		key := m.SourceObject + "\x00" + m.SourceField
		if seen[key] {
			continue
		}
		seen[key] = true
		if defaultVal.Valid {
			m.DefaultValue = defaultVal.String
		}
		if sourceLookup.Valid {
			m.SourceLookupField = sourceLookup.String
		}
		mappings = append(mappings, m)
	}

	return mappings, nil
}

// GetFieldMappingsByTarget returns every active row in
// _System_ObjectFieldMapping for (targetObject, actionType), preserving
// SourceObject on each row. Consumers group rows by SourceObject +
// SourceLookupField to decide which parent record to retrieve per group.
func GetFieldMappingsByTarget(db *sql.DB, targetObject, actionType string) ([]connector.FieldMappingWithSource, error) {
	return GetFieldMappingsByTargetContext(context.Background(), db, targetObject, actionType)
}