package connector_inbound

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

	"github.com/pingcap-inc/nexuscrm-connectors/features/connector/events"
)

const (
	outboundEchoLookbackDefault = 2 * time.Minute
	outboundEchoRecentLimit     = 20
)

type OutboundEchoProbeInput struct {
	Namespace   string
	ObjectKind  string
	SourceID    string
	Property    string
	Value       string
	EventID     string
	EventTime   time.Time
	Lookback    time.Duration
	ResultLimit int
}

type OutboundEchoEvidence struct {
	Found         bool
	Mismatch      bool
	AttemptID     string
	Property      string
	OutboundValue string
}

type OutboundEchoProbe interface {
	ProbeRecentOutboundEcho(context.Context, OutboundEchoProbeInput) (OutboundEchoEvidence, error)
}

type SQLOutboundEchoProbe struct {
	DB       *sql.DB
	Lookback time.Duration
}

func (p SQLOutboundEchoProbe) ProbeRecentOutboundEcho(ctx context.Context, in OutboundEchoProbeInput) (OutboundEchoEvidence, error) {
	if p.DB == nil {
		return OutboundEchoEvidence{}, nil
	}
	property := strings.TrimSpace(in.Property)
	sourceID := strings.TrimSpace(in.SourceID)
	objectKind := strings.TrimSpace(in.ObjectKind)
	if property == "" || sourceID == "" || objectKind == "" {
		return OutboundEchoEvidence{}, nil
	}
	lookback := in.Lookback
	if lookback <= 0 {
		lookback = p.Lookback
	}
	if lookback <= 0 {
		lookback = outboundEchoLookbackDefault
	}
	limit := in.ResultLimit
	if limit <= 0 || limit > outboundEchoRecentLimit {
		limit = outboundEchoRecentLimit
	}

	rows, err := p.DB.QueryContext(ctx, `
SELECT Id, RequestBodyBase64
  FROM _System_ConnectorPushAttempt
 WHERE Status = 'finalized'
   AND RequestMethod = 'PATCH'
   AND TargetObject = ?
   AND RemoteId = ?
   AND CreatedDate >= DATE_SUB(?, INTERVAL ? SECOND)
 ORDER BY CreatedDate DESC, Id DESC
 LIMIT ?`,
		objectKind,
		sourceID,
		in.EventTime.UTC(),
		int(lookback.Seconds()),
		limit,
	)
	if err != nil {
		// Older or non-CRM runtimes may not have connector-push durability. This
		// evidence is advisory, so absence of the table disables enrichment.
		if strings.Contains(strings.ToLower(err.Error()), "_system_connectorpushattempt") ||
			strings.Contains(strings.ToLower(err.Error()), "doesn't exist") ||
			strings.Contains(strings.ToLower(err.Error()), "no such table") {
			return OutboundEchoEvidence{}, nil
		}
		return OutboundEchoEvidence{}, err
	}
	defer rows.Close()

	for rows.Next() {
		var attemptID, bodyB64 string
		if err := rows.Scan(&attemptID, &bodyB64); err != nil {
			return OutboundEchoEvidence{}, err
		}
		outboundValue, ok := connectorPushBodyProperty(bodyB64, property)
		if !ok {
			continue
		}
		return OutboundEchoEvidence{
			Found:         true,
			Mismatch:      outboundValue != strings.TrimSpace(in.Value),
			AttemptID:     attemptID,
			Property:      property,
			OutboundValue: outboundValue,
		}, nil
	}
	if err := rows.Err(); err != nil {
		return OutboundEchoEvidence{}, err
	}
	return OutboundEchoEvidence{}, nil
}

func connectorPushBodyProperty(bodyB64, property string) (string, bool) {
	raw, err := base64.StdEncoding.DecodeString(strings.TrimSpace(bodyB64))
	if err != nil {
		return "", false
	}
	var body struct {
		Properties map[string]any `json:"properties"`
	}
	if err := json.Unmarshal(raw, &body); err != nil || body.Properties == nil {
		return "", false
	}
	value, ok := body.Properties[property]
	if !ok {
		return "", false
	}
	return scalarString(value)
}

func (w *Worker) buildFlowInputs(ctx context.Context, ev *connector_events.ConnectorEvent, objectKind, sourceID string, userPayload json.RawMessage, details map[string]json.RawMessage, requireRecord bool, coalescedPayloads []coalescedPayloadInput) (map[string]any, error) {
	enrichedPayload, err := w.payloadWithOutboundEchoEvidence(ctx, ev, objectKind, sourceID, userPayload)
	if err != nil {
		return nil, err
	}
	return buildFlowInputs(ev, objectKind, sourceID, enrichedPayload, details, requireRecord, coalescedPayloads)
}

func (w *Worker) payloadWithOutboundEchoEvidence(ctx context.Context, ev *connector_events.ConnectorEvent, objectKind, sourceID string, userPayload json.RawMessage) (json.RawMessage, error) {
	if w == nil || w.outboundEchoProbe == nil {
		return userPayload, nil
	}
	var payload map[string]any
	if err := json.Unmarshal(userPayload, &payload); err != nil {
		return nil, fmt.Errorf("payload unmarshal for outbound echo evidence: %w", err)
	}
	property, _ := scalarString(payload["propertyName"])
	value, _ := scalarString(payload["propertyValue"])
	if strings.TrimSpace(property) == "" {
		return userPayload, nil
	}
	evidence, err := w.outboundEchoProbe.ProbeRecentOutboundEcho(ctx, OutboundEchoProbeInput{
		Namespace:  ev.Namespace,
		ObjectKind: objectKind,
		SourceID:   sourceID,
		Property:   property,
		Value:      value,
		EventID:    ev.ID,
		EventTime:  ev.CreatedDate,
	})
	if err != nil {
		return nil, err
	}
	if !evidence.Found {
		return userPayload, nil
	}
	payload["nexusRecentOutboundPropertyEcho"] = evidence.Mismatch
	payload["nexusRecentOutboundPropertyName"] = evidence.Property
	payload["nexusRecentOutboundPropertyValue"] = evidence.OutboundValue
	payload["nexusRecentOutboundPropertyAttemptId"] = evidence.AttemptID
	out, err := json.Marshal(payload)
	if err != nil {
		return nil, fmt.Errorf("payload marshal for outbound echo evidence: %w", err)
	}
	return out, nil
}

func scalarString(v any) (string, bool) {
	switch t := v.(type) {
	case nil:
		return "", true
	case string:
		return strings.TrimSpace(t), true
	case float64, bool, json.Number:
		return strings.TrimSpace(fmt.Sprint(t)), true
	default:
		return "", false
	}
}

var _ OutboundEchoProbe = SQLOutboundEchoProbe{}