package serviceactor

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

const (
	WebhookReceiptStatusReceived  = "received"
	WebhookReceiptStatusProcessed = "processed"
)

// WebhookReceipt records one inbound webhook receipt for a service actor.
type WebhookReceipt struct {
	ID                       string
	WorkspaceID              string
	ServiceDefinitionID      string
	ServiceInstallationID    string
	ServiceConnectionID      string
	ServiceConnectionRef     string
	ServiceWebhookEndpointID string
	ServiceKind              string
	EndpointKind             string
	DeliveryKey              string
	EventType                string
	EventAction              string
	EventID                  string
	EventKey                 string
	ExternalResourceType     string
	ExternalResourceKey      string
	ExternalResourceRef      string
	ExternalResourceURL      string
	ReceivedAt               time.Time
	ProcessedAt              *time.Time
	Status                   string
	MatchedSubscriptionCount int
	PayloadExcerpt           string
	Payload                  []byte
	NormalizedEvent          []byte
	ErrorCode                string
	ErrorDetail              string
	CreatedAt                time.Time
	UpdatedAt                time.Time
}

// WebhookReceiptReserve describes the canonical receipt identity and ingress
// snapshot to reserve before dispatching a service event.
type WebhookReceiptReserve struct {
	ID                       string
	WorkspaceID              string
	ServiceDefinitionID      string
	ServiceInstallationID    string
	ServiceConnectionID      string
	ServiceConnectionRef     string
	ServiceWebhookEndpointID string
	ServiceKind              string
	EndpointKind             string
	DeliveryKey              string
	EventType                string
	EventAction              string
	EventID                  string
	EventKey                 string
	ExternalResourceType     string
	ExternalResourceKey      string
	ExternalResourceRef      string
	ExternalResourceURL      string
	PayloadExcerpt           string
	Payload                  []byte
	NormalizedEvent          []byte
	Now                      time.Time
}

// WebhookReceiptOutcome records the final processing state for one receipt.
type WebhookReceiptOutcome struct {
	ID                       string
	MatchedSubscriptionCount int
	Status                   string
	ErrorCode                string
	ErrorDetail              string
	NormalizedEvent          []byte
	Now                      time.Time
}

// ReserveWebhookReceipt reserves one inbound receipt, returning the existing
// receipt when the service has already seen the delivery key.
func (store *TxStore) ReserveWebhookReceipt(ctx context.Context, params WebhookReceiptReserve) (WebhookReceipt, bool, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return WebhookReceipt{}, false, err
	}
	return reserveWebhookReceipt(ctx, execer, params)
}

// ResetWebhookReceiptForProcessing reclaims a stale received receipt for a new
// processing attempt while keeping the same receipt identity.
func (store *TxStore) ResetWebhookReceiptForProcessing(ctx context.Context, params WebhookReceiptReserve) (WebhookReceipt, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return WebhookReceipt{}, err
	}
	return resetWebhookReceiptForProcessing(ctx, execer, params)
}

// CompleteWebhookReceipt records the final processing outcome.
func (store *TxStore) CompleteWebhookReceipt(ctx context.Context, params WebhookReceiptOutcome) error {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return err
	}
	return completeWebhookReceipt(ctx, execer, params)
}

func reserveWebhookReceipt(ctx context.Context, execer coredb.QueryExecer, params WebhookReceiptReserve) (WebhookReceipt, bool, error) {
	installationID := trim(params.ServiceInstallationID)
	deliveryKey := trim(params.DeliveryKey)
	if installationID == "" || deliveryKey == "" {
		return WebhookReceipt{}, false, badRequest("serviceInstallationId and deliveryKey are required")
	}
	if err := validateWebhookReceiptReserve(params); err != nil {
		return WebhookReceipt{}, false, err
	}
	if existing, found, err := loadWebhookReceiptByDeliveryKeyForUpdate(ctx, execer, installationID, deliveryKey); err != nil {
		return WebhookReceipt{}, false, err
	} else if found {
		return existing, false, nil
	}
	receiptID := trim(params.ID)
	if receiptID == "" {
		var err error
		receiptID, err = identifiers.NewEntityID("svcwhr")
		if err != nil {
			return WebhookReceipt{}, false, fmt.Errorf("generate service webhook receipt id: %w", err)
		}
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	_, err := execer.Exec(ctx, `
INSERT INTO service_webhook_receipts (
    id, workspace_id, service_definition_id, service_installation_id,
    service_connection_id, service_connection_ref, service_webhook_endpoint_id,
    service_kind, endpoint_kind, delivery_key, event_type, event_action,
    event_id, event_key, external_resource_type, external_resource_key,
    external_resource_ref, external_resource_url, received_at, status,
    matched_subscription_count, payload_excerpt, payload, normalized_event,
    error_code, error_detail, created_at, updated_at
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7,
    $8, $9, $10, $11, $12,
    $13, $14, $15, $16,
    $17, $18, $19, 'received',
    0, $20, $21, $22,
    '', '', $19, $19
)
`, receiptID, trim(params.WorkspaceID), trim(params.ServiceDefinitionID), installationID,
		nullableString(trim(params.ServiceConnectionID)), trim(params.ServiceConnectionRef), nullableString(trim(params.ServiceWebhookEndpointID)),
		trim(params.ServiceKind), trim(params.EndpointKind), deliveryKey, trim(params.EventType), trim(params.EventAction),
		trim(params.EventID), trim(params.EventKey), trim(params.ExternalResourceType), trim(params.ExternalResourceKey),
		trim(params.ExternalResourceRef), trim(params.ExternalResourceURL), now, trim(params.PayloadExcerpt), nullableBytes(params.Payload), nullableBytes(params.NormalizedEvent))
	if err != nil {
		if coredb.IsUniqueViolation(err) {
			if existing, found, loadErr := loadWebhookReceiptByDeliveryKeyForUpdate(ctx, execer, installationID, deliveryKey); loadErr != nil {
				return WebhookReceipt{}, false, loadErr
			} else if found {
				return existing, false, nil
			}
		}
		return WebhookReceipt{}, false, fmt.Errorf("insert service webhook receipt: %w", err)
	}
	receipt, err := loadWebhookReceipt(ctx, execer, receiptID)
	return receipt, true, err
}

func resetWebhookReceiptForProcessing(ctx context.Context, execer coredb.QueryExecer, params WebhookReceiptReserve) (WebhookReceipt, error) {
	receiptID := trim(params.ID)
	if receiptID == "" {
		return WebhookReceipt{}, badRequest("receipt id is required")
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if err := validateWebhookReceiptReserve(params); err != nil {
		return WebhookReceipt{}, err
	}
	_, err := execer.Exec(ctx, `
UPDATE service_webhook_receipts
SET workspace_id = $2,
    service_definition_id = $3,
    service_installation_id = $4,
    service_connection_id = $5,
    service_connection_ref = $6,
    service_webhook_endpoint_id = $7,
    service_kind = $8,
    endpoint_kind = $9,
    delivery_key = $10,
    event_type = $11,
    event_action = $12,
    event_id = $13,
    event_key = $14,
    external_resource_type = $15,
    external_resource_key = $16,
    external_resource_ref = $17,
    external_resource_url = $18,
    received_at = $19,
    processed_at = NULL,
    status = 'received',
    matched_subscription_count = 0,
    payload_excerpt = $20,
    payload = $21,
    normalized_event = $22,
    error_code = '',
    error_detail = '',
    updated_at = $19
WHERE id = $1
`, receiptID, trim(params.WorkspaceID), trim(params.ServiceDefinitionID), trim(params.ServiceInstallationID),
		nullableString(trim(params.ServiceConnectionID)), trim(params.ServiceConnectionRef), nullableString(trim(params.ServiceWebhookEndpointID)),
		trim(params.ServiceKind), trim(params.EndpointKind), trim(params.DeliveryKey), trim(params.EventType), trim(params.EventAction),
		trim(params.EventID), trim(params.EventKey), trim(params.ExternalResourceType), trim(params.ExternalResourceKey),
		trim(params.ExternalResourceRef), trim(params.ExternalResourceURL), now, trim(params.PayloadExcerpt), nullableBytes(params.Payload), nullableBytes(params.NormalizedEvent))
	if err != nil {
		return WebhookReceipt{}, fmt.Errorf("reset service webhook receipt: %w", err)
	}
	return WebhookReceipt{ID: receiptID}, nil
}

func completeWebhookReceipt(ctx context.Context, execer coredb.QueryExecer, params WebhookReceiptOutcome) error {
	receiptID := trim(params.ID)
	if receiptID == "" {
		return nil
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	status := trim(params.Status)
	if status == "" {
		status = WebhookReceiptStatusProcessed
	}
	_, err := execer.Exec(ctx, `
UPDATE service_webhook_receipts
SET processed_at = $2,
    status = $3,
    matched_subscription_count = $4,
    normalized_event = COALESCE($5, normalized_event),
    error_code = $6,
    error_detail = $7,
    updated_at = $2
WHERE id = $1
`, receiptID, now, status, params.MatchedSubscriptionCount, nullableBytes(params.NormalizedEvent), trim(params.ErrorCode), trim(params.ErrorDetail))
	if err != nil {
		return fmt.Errorf("complete service webhook receipt: %w", err)
	}
	return nil
}

func validateWebhookReceiptReserve(params WebhookReceiptReserve) error {
	if trim(params.WorkspaceID) == "" || trim(params.ServiceDefinitionID) == "" || trim(params.ServiceInstallationID) == "" ||
		trim(params.ServiceKind) == "" || trim(params.EndpointKind) == "" || trim(params.DeliveryKey) == "" || trim(params.EventType) == "" {
		return badRequest("service webhook receipt workspace, service identity, endpoint kind, delivery key, and event type are required")
	}
	return nil
}

func loadWebhookReceipt(ctx context.Context, queryer coredb.QueryRower, receiptID string) (WebhookReceipt, error) {
	row := queryer.QueryRow(ctx, webhookReceiptSelectSQL()+` WHERE id = $1`, trim(receiptID))
	receipt, err := scanWebhookReceipt(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return WebhookReceipt{}, notFound("service webhook receipt not found")
		}
		return WebhookReceipt{}, fmt.Errorf("load service webhook receipt: %w", err)
	}
	return receipt, nil
}

func loadWebhookReceiptByDeliveryKeyForUpdate(ctx context.Context, queryer coredb.QueryRower, serviceInstallationID string, deliveryKey string) (WebhookReceipt, bool, error) {
	row := queryer.QueryRow(ctx, webhookReceiptSelectSQL()+`
WHERE service_installation_id = $1
  AND delivery_key = $2
FOR UPDATE
`, trim(serviceInstallationID), trim(deliveryKey))
	receipt, err := scanWebhookReceipt(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return WebhookReceipt{}, false, nil
		}
		return WebhookReceipt{}, false, fmt.Errorf("load service webhook receipt by delivery key: %w", err)
	}
	return receipt, true, nil
}

func webhookReceiptSelectSQL() string {
	return `
SELECT id, workspace_id, service_definition_id, service_installation_id,
       COALESCE(service_connection_id, ''), service_connection_ref,
       COALESCE(service_webhook_endpoint_id, ''), service_kind, endpoint_kind,
       delivery_key, event_type, event_action, event_id, event_key,
       external_resource_type, external_resource_key, external_resource_ref,
       external_resource_url, received_at, processed_at, status,
       matched_subscription_count, payload_excerpt, payload, normalized_event,
       error_code, error_detail, created_at, updated_at
FROM service_webhook_receipts`
}

func scanWebhookReceipt(row coredb.Row) (WebhookReceipt, error) {
	var receipt WebhookReceipt
	var processedAt sql.NullTime
	var matchedCount int64
	var payload, normalized []byte
	if err := row.Scan(
		&receipt.ID,
		&receipt.WorkspaceID,
		&receipt.ServiceDefinitionID,
		&receipt.ServiceInstallationID,
		&receipt.ServiceConnectionID,
		&receipt.ServiceConnectionRef,
		&receipt.ServiceWebhookEndpointID,
		&receipt.ServiceKind,
		&receipt.EndpointKind,
		&receipt.DeliveryKey,
		&receipt.EventType,
		&receipt.EventAction,
		&receipt.EventID,
		&receipt.EventKey,
		&receipt.ExternalResourceType,
		&receipt.ExternalResourceKey,
		&receipt.ExternalResourceRef,
		&receipt.ExternalResourceURL,
		&receipt.ReceivedAt,
		&processedAt,
		&receipt.Status,
		&matchedCount,
		&receipt.PayloadExcerpt,
		&payload,
		&normalized,
		&receipt.ErrorCode,
		&receipt.ErrorDetail,
		&receipt.CreatedAt,
		&receipt.UpdatedAt,
	); err != nil {
		return WebhookReceipt{}, err
	}
	receipt.ID = trim(receipt.ID)
	receipt.WorkspaceID = trim(receipt.WorkspaceID)
	receipt.ServiceDefinitionID = trim(receipt.ServiceDefinitionID)
	receipt.ServiceInstallationID = trim(receipt.ServiceInstallationID)
	receipt.ServiceConnectionID = trim(receipt.ServiceConnectionID)
	receipt.ServiceConnectionRef = trim(receipt.ServiceConnectionRef)
	receipt.ServiceWebhookEndpointID = trim(receipt.ServiceWebhookEndpointID)
	receipt.ServiceKind = trim(receipt.ServiceKind)
	receipt.EndpointKind = trim(receipt.EndpointKind)
	receipt.DeliveryKey = trim(receipt.DeliveryKey)
	receipt.EventType = trim(receipt.EventType)
	receipt.Status = trim(receipt.Status)
	receipt.ProcessedAt = ptrFromNullTime(processedAt)
	receipt.MatchedSubscriptionCount = int(matchedCount)
	receipt.Payload = payload
	receipt.NormalizedEvent = normalized
	return receipt, nil
}