package app

import (
	"context"
	"fmt"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
)

func (store *Store) ListAppWebhookDeliveriesForUser(ctx context.Context, userID string, workspaceID string, installationID string, limit int) ([]AppWebhookDeliveryView, error) {
	workspaceMember, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return nil, err
	}
	canInspectWorkspace := access.HasWorkspaceAdminRole(domain.WorkspaceRole(workspaceMember.WorkspaceRole))

	normalizedLimit := limit
	switch {
	case normalizedLimit <= 0:
		normalizedLimit = 25
	case normalizedLimit > 100:
		normalizedLimit = 100
	}

	trimmedInstallationID := strings.TrimSpace(installationID)

	rows, err := store.db.Query(ctx, `
SELECT
    receipt.id,
    receipt.service_installation_id,
    definition.id,
    definition.slug,
    definition.name,
    receipt.delivery_key,
    receipt.event_type,
    receipt.payload_excerpt,
    receipt.matched_subscription_count,
    receipt.status,
    receipt.processed_at,
    receipt.error_detail,
    receipt.created_at,
    receipt.updated_at
FROM service_webhook_receipts receipt
JOIN service_installations installation
  ON installation.id = receipt.service_installation_id
 AND installation.service_kind = 'app'
JOIN service_definitions definition
  ON definition.id = installation.service_definition_id
 AND definition.service_kind = installation.service_kind
WHERE installation.workspace_id = $1
  AND ($2 = '' OR receipt.service_installation_id = $2)
  AND (
    $4 OR EXISTS (
      SELECT 1
      FROM service_event_deliveries principal_delivery
      WHERE principal_delivery.workspace_id = installation.workspace_id
        AND principal_delivery.service_installation_id = receipt.service_installation_id
        AND principal_delivery.service_webhook_receipt_id = receipt.id
        AND principal_delivery.owner_member_id_snapshot = $3
    )
  )
ORDER BY receipt.created_at DESC, receipt.id DESC
LIMIT $5
`, strings.TrimSpace(workspaceID), trimmedInstallationID, workspaceMember.Member.ID, canInspectWorkspace, normalizedLimit)
	if err != nil {
		return nil, fmt.Errorf("list app webhook deliveries: %w", err)
	}
	defer rows.Close()

	deliveries := make([]AppWebhookDeliveryView, 0)
	receiptIDs := make([]string, 0)
	for rows.Next() {
		var (
			delivery     AppWebhookDeliveryView
			receiptID    string
			matchedCount int
		)
		if err := rows.Scan(
			&delivery.ID,
			&delivery.InstallationID,
			&delivery.AppID,
			&delivery.AppSlug,
			&delivery.AppName,
			&delivery.DeliveryKey,
			&delivery.EventType,
			&delivery.PayloadExcerpt,
			&matchedCount,
			&delivery.Outcome,
			&delivery.ProcessedAt,
			&delivery.ErrorMessage,
			&delivery.CreatedAt,
			&delivery.UpdatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan app webhook delivery: %w", err)
		}
		receiptID = strings.TrimSpace(delivery.ID)
		deliveries = append(deliveries, delivery)
		receiptIDs = append(receiptIDs, receiptID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate app webhook deliveries: %w", err)
	}
	rows.Close()

	matchedByReceipt, err := store.loadMatchedWebhookSubscriptionsForReceipts(ctx, receiptIDs)
	if err != nil {
		return nil, err
	}
	for index := range deliveries {
		deliveries[index].MatchedSubscriptions = matchedByReceipt[receiptIDs[index]]
		if deliveries[index].MatchedSubscriptions == nil {
			deliveries[index].MatchedSubscriptions = []MatchedWebhookSubscription{}
		}
	}

	if !canInspectWorkspace && len(deliveries) > 0 {
		visibleSubscriptionIDs, err := store.listPrincipalAppWebhookDeliverySubscriptionIDs(
			ctx,
			workspaceID,
			workspaceMember.Member.ID,
			receiptIDs,
		)
		if err != nil {
			return nil, err
		}
		for index := range deliveries {
			principalSubscriptions := visibleSubscriptionIDs[receiptIDs[index]]
			filtered := make([]MatchedWebhookSubscription, 0, len(deliveries[index].MatchedSubscriptions))
			for _, subscription := range deliveries[index].MatchedSubscriptions {
				if _, visible := principalSubscriptions[strings.TrimSpace(subscription.ID)]; visible {
					filtered = append(filtered, subscription)
				}
			}
			deliveries[index].MatchedSubscriptions = filtered
		}
	}
	return deliveries, nil
}

// loadMatchedWebhookSubscriptionsForReceipts returns the per-receipt matched
// subscription snapshot derived from the canonical service event deliveries
// ledger. Missing receipts simply have no entry in the returned map.
func (store *Store) loadMatchedWebhookSubscriptionsForReceipts(ctx context.Context, receiptIDs []string) (map[string][]MatchedWebhookSubscription, error) {
	normalizedReceiptIDs := normalizeNonEmptyStrings(receiptIDs)
	if len(normalizedReceiptIDs) == 0 {
		return map[string][]MatchedWebhookSubscription{}, nil
	}
	receiptIDsCTE, receiptIDArgs, _, err := coredb.BuildStringValuesCTE(
		"requested_webhook_receipts",
		"receipt_id",
		normalizedReceiptIDs,
		1,
	)
	if err != nil {
		return nil, fmt.Errorf("build app webhook delivery matched subscription query: %w", err)
	}
	query := fmt.Sprintf(`
WITH %s
SELECT delivery.service_webhook_receipt_id,
       delivery.service_event_subscription_id,
       delivery.delivery_scope_id,
       delivery.external_resource_ref
FROM requested_webhook_receipts requested
JOIN service_event_deliveries delivery
  ON delivery.service_webhook_receipt_id = requested.receipt_id
ORDER BY requested.receipt_id ASC, delivery.created_at ASC, delivery.id ASC
`, receiptIDsCTE)
	rows, err := store.db.Query(ctx, query, receiptIDArgs...)
	if err != nil {
		return nil, fmt.Errorf("load app webhook delivery matched subscriptions: %w", err)
	}
	defer rows.Close()
	matchedByReceipt := make(map[string][]MatchedWebhookSubscription, len(normalizedReceiptIDs))
	for rows.Next() {
		var receiptID, subscriptionID, deliveryScopeID, resourceIdentifier string
		if err := rows.Scan(&receiptID, &subscriptionID, &deliveryScopeID, &resourceIdentifier); err != nil {
			return nil, fmt.Errorf("scan app webhook delivery matched subscription: %w", err)
		}
		receiptID = strings.TrimSpace(receiptID)
		subscriptionID = strings.TrimSpace(subscriptionID)
		deliveryScopeID = strings.TrimSpace(deliveryScopeID)
		resourceIdentifier = strings.TrimSpace(resourceIdentifier)
		if receiptID == "" || subscriptionID == "" {
			continue
		}
		matchedByReceipt[receiptID] = append(matchedByReceipt[receiptID], MatchedWebhookSubscription{
			ID:                 subscriptionID,
			ThreadID:           deliveryScopeID,
			ResourceIdentifier: resourceIdentifier,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate app webhook delivery matched subscriptions: %w", err)
	}
	return matchedByReceipt, nil
}

// listPrincipalAppWebhookDeliverySubscriptionIDs returns the per-receipt
// subscription ids whose immutable delivery owner snapshot belongs to the
// current human principal. The caller uses this bounded result to avoid leaking
// another member's matched-subscription metadata on a shared webhook receipt.
func (store *Store) listPrincipalAppWebhookDeliverySubscriptionIDs(
	ctx context.Context,
	workspaceID string,
	memberID string,
	receiptIDs []string,
) (map[string]map[string]struct{}, error) {
	visible := make(map[string]map[string]struct{})
	normalizedReceiptIDs := normalizeNonEmptyStrings(receiptIDs)
	if len(normalizedReceiptIDs) == 0 {
		return visible, nil
	}

	receiptIDsCTE, receiptIDArgs, _, err := coredb.BuildStringValuesCTE(
		"requested_webhook_receipts",
		"receipt_id",
		normalizedReceiptIDs,
		3,
	)
	if err != nil {
		return nil, fmt.Errorf("build app webhook delivery receipt ids: %w", err)
	}
	query := fmt.Sprintf(`
WITH %s
SELECT principal_delivery.service_webhook_receipt_id,
       principal_delivery.service_event_subscription_id
FROM requested_webhook_receipts requested
JOIN service_event_deliveries principal_delivery
  ON principal_delivery.service_webhook_receipt_id = requested.receipt_id
WHERE principal_delivery.workspace_id = $1
  AND principal_delivery.owner_member_id_snapshot = $2
`, receiptIDsCTE)
	args := append([]any{strings.TrimSpace(workspaceID), strings.TrimSpace(memberID)}, receiptIDArgs...)
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list principal app webhook delivery subscriptions: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var receiptID, subscriptionID string
		if err := rows.Scan(&receiptID, &subscriptionID); err != nil {
			return nil, fmt.Errorf("scan principal app webhook delivery subscription: %w", err)
		}
		receiptID = strings.TrimSpace(receiptID)
		subscriptionID = strings.TrimSpace(subscriptionID)
		if receiptID == "" || subscriptionID == "" {
			continue
		}
		if visible[receiptID] == nil {
			visible[receiptID] = make(map[string]struct{})
		}
		visible[receiptID][subscriptionID] = struct{}{}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate principal app webhook delivery subscriptions: %w", err)
	}
	return visible, nil
}