<?php

declare(strict_types=1);

namespace Drupal\native_observability_dashboard\Service;

use Drupal\Core\Database\Connection;
use Drupal\native_observability_dashboard\ValueObject\TimeRange;

/**
 * Reads raw trace metrics from storage.
 */
final class TraceMetricsReader {

  /**
   * Defines the main trace storage table name.
   */
  private const TRACE_TABLE = 'native_observability_trace';

  /**
   * Defines the span storage table used for outbound HTTP requests.
   */
  private const SPANS_TABLE = 'native_observability_spans';

  /**
   * Creates a new trace metrics reader instance.
   *
   * @param \Drupal\Core\Database\Connection $database
   *   The active database connection.
   */
  public function __construct(
    private readonly Connection $database,
  ) {
  }

  /**
   * Returns the total number of traces in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return int
   *   The number of traces found in the range.
   */
  public function getTraceCount(TimeRange $range): int {
    return $this->countByRange($range);
  }

  /**
   * Returns the total number of AJAX traces in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return int
   *   The number of AJAX traces found in the range.
   */
  public function getAjaxTraceCount(TimeRange $range): int {
    return $this->countByRange($range, TRUE);
  }

  /**
   * Returns the total number of error traces in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return int
   *   The number of error traces found in the range.
   */
  public function getErrorTraceCount(TimeRange $range): int {
    return $this->countByRange($range, NULL, TRUE);
  }

  /**
   * Returns the total number of AJAX error traces in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return int
   *   The number of AJAX error traces found in the range.
   */
  public function getAjaxErrorTraceCount(TimeRange $range): int {
    return $this->countByRange($range, TRUE, TRUE);
  }

  /**
   * Calculates the AJAX error rate in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return float
   *   The calculated AJAX error rate percentage.
   */
  public function getAjaxErrorRate(TimeRange $range): float {
    $ajaxTraceCount = $this->getAjaxTraceCount($range);

    if ($ajaxTraceCount === 0) {
      return 0.0;
    }

    return round(($this->getAjaxErrorTraceCount($range) / $ajaxTraceCount) * 100, 2);
  }

  /**
   * Returns the average response duration in milliseconds.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   *
   * @return float
   *   The average duration in milliseconds.
   */
  public function getAverageDuration(TimeRange $range, ?bool $ajaxOnly = NULL): float {
    return $this->averageDurationByRange($range, $ajaxOnly);
  }

  /**
   * Returns the average AJAX response duration in milliseconds.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return float
   *   The average AJAX duration in milliseconds.
   */
  public function getAjaxAverageDuration(TimeRange $range): float {
    return $this->averageDurationByRange($range, TRUE);
  }

  /**
   * Returns the calculated P95 duration in milliseconds.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return float
   *   The P95 duration in milliseconds.
   */
  public function getP95Duration(TimeRange $range): float {
    return $this->p95DurationByRange($range);
  }

  /**
   * Returns the average requests per second.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return float
   *   The average requests per second.
   */
  public function getRequestsPerSecond(TimeRange $range): float {
    $total = $this->getTraceCount($range);

    return round($total / max(1, $range->getDurationInSeconds()), 2);
  }

  /**
   * Returns the average AJAX requests per second.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return float
   *   The average AJAX requests per second.
   */
  public function getAjaxRequestsPerSecond(TimeRange $range): float {
    $total = $this->getAjaxTraceCount($range);

    return round($total / max(1, $range->getDurationInSeconds()), 2);
  }

  /**
   * Builds a trace count time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getTraceCountSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildCountSeries($range, $bucketSize);
  }

  /**
   * Builds an AJAX trace count time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getAjaxTraceCountSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildCountSeries($range, $bucketSize, TRUE);
  }

  /**
   * Builds a request-per-second time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getRequestsPerSecondSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildRequestsPerSecondSeries($range, $bucketSize);
  }

  /**
   * Builds an AJAX request-per-second time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getAjaxRequestsPerSecondSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildRequestsPerSecondSeries($range, $bucketSize, TRUE);
  }

  /**
   * Builds an average duration time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getAverageDurationSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildAverageDurationSeries($range, $bucketSize);
  }

  /**
   * Builds an AJAX average duration time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getAjaxAverageDurationSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildAverageDurationSeries($range, $bucketSize, TRUE);
  }

  /**
   * Builds a P95 duration time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getP95DurationSeries(TimeRange $range, int $bucketSize = 300): array {
    $series = [];

    foreach ($this->buildBuckets($range, $bucketSize) as [$bucketStart, $bucketEnd]) {
      $query = $this->database->select(self::TRACE_TABLE, 't');
      $query->fields('t', ['duration_ms']);
      $query->condition('started_at', $bucketStart, '>=');
      $query->condition('started_at', $bucketEnd, '<');
      $query->orderBy('duration_ms', 'ASC');

      $durations = array_map('floatval', $query->execute()->fetchCol());

      if ($durations === []) {
        $value = 0.0;
      }
      else {
        $index = (int) ceil(count($durations) * 0.95) - 1;
        $index = max(0, min($index, count($durations) - 1));
        $value = round($durations[$index], 2);
      }

      $series[] = [
        'label' => $this->formatBucketLabel($bucketStart, $bucketEnd),
        'value' => $value,
      ];
    }

    return $series;
  }

  /**
   * Builds an error rate time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getErrorRateSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildErrorRateSeries($range, $bucketSize);
  }

  /**
   * Builds an AJAX error rate time series.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of buckets with label and value.
   */
  public function getAjaxErrorRateSeries(TimeRange $range, int $bucketSize = 300): array {
    return $this->buildErrorRateSeries($range, $bucketSize, TRUE);
  }

  /**
   * Returns the slowest endpoints in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $limit
   *   The maximum number of rows to return.
   *
   * @return array
   *   A list of endpoint rows for UI rendering.
   */
  public function getTopSlowEndpoints(TimeRange $range, int $limit = 5): array {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', ['path', 'route_name']);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');

    $query->addExpression('COUNT(t.id)', 'request_count');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->addExpression('MAX(t.duration_ms)', 'max_duration');
    $query->addExpression('MAX(t.started_at)', 'last_seen');

    $query->groupBy('t.path');
    $query->groupBy('t.route_name');
    $query->orderBy('avg_duration', 'DESC');
    $query->range(0, $limit);

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    return array_map(static function (array $row): array {
      return [
        'path' => $row['path'] ?? '',
        'route_name' => $row['route_name'] ?? '',
        'request_count' => (int) ($row['request_count'] ?? 0),
        'avg_duration' => round((float) ($row['avg_duration'] ?? 0), 2),
        'max_duration' => round((float) ($row['max_duration'] ?? 0), 2),
        'last_seen' => (int) ($row['last_seen'] ?? 0),
      ];
    }, $rows);
  }

  /**
   * Returns the most active AJAX endpoints in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $limit
   *   The maximum number of rows to return.
   *
   * @return array
   *   A list of endpoint rows for UI rendering.
   */
  public function getTopAjaxEndpoints(TimeRange $range, int $limit = 5): array {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', ['path', 'route_name']);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');
    $query->condition('is_ajax', 1);

    $query->addExpression('COUNT(t.id)', 'request_count');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->addExpression('MAX(t.started_at)', 'last_seen');
    $query->addExpression('SUM(CASE WHEN t.status_code >= 500 THEN 1 ELSE 0 END)', 'error_count');

    $query->groupBy('t.path');
    $query->groupBy('t.route_name');
    $query->orderBy('request_count', 'DESC');
    $query->range(0, $limit);

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    return array_map(static function (array $row): array {
      return [
        'path' => $row['path'] ?? '',
        'route_name' => $row['route_name'] ?? '',
        'request_count' => (int) ($row['request_count'] ?? 0),
        'avg_duration' => round((float) ($row['avg_duration'] ?? 0), 2),
        'error_count' => (int) ($row['error_count'] ?? 0),
        'last_seen' => (int) ($row['last_seen'] ?? 0),
      ];
    }, $rows);
  }

  /**
   * Returns the full slow endpoint dataset for the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $limit
   *   The maximum number of rows to return.
   *
   * @return array
   *   The normalized dataset rows.
   */
  public function getSlowEndpointDataset(TimeRange $range, int $limit = 250): array {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', ['path', 'route_name']);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');

    $query->addExpression('COUNT(t.id)', 'request_count');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->addExpression('SUM(CASE WHEN t.status_code >= 500 THEN 1 ELSE 0 END)', 'error_count');

    $query->groupBy('t.path');
    $query->groupBy('t.route_name');
    $query->orderBy('avg_duration', 'DESC');
    $query->range(0, $limit);

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    return array_map(function (array $row) use ($range): array {
      $path = (string) ($row['path'] ?? '');
      $routeName = (string) ($row['route_name'] ?? '');
      $requestCount = (int) ($row['request_count'] ?? 0);
      $averageDuration = round((float) ($row['avg_duration'] ?? 0), 2);
      $errorCount = (int) ($row['error_count'] ?? 0);
      $samples = $this->getTraceSamplesForEndpoint($range, $path, $routeName, NULL);

      return [
        'path' => $path !== '' ? $path : '—',
        'route_name' => $routeName !== '' ? $routeName : '—',
        'request_count' => number_format($requestCount),
        'request_count_raw' => $requestCount,
        'avg_duration' => number_format($averageDuration, 2) . ' ms',
        'avg_duration_raw' => $averageDuration,
        'error_count' => number_format($errorCount),
        'error_count_raw' => $errorCount,
        'detail' => [
          'type' => 'slow_endpoints',
          'title' => $path !== '' ? $path : 'Unnamed internal route',
          'subtitle' => $routeName !== '' ? $routeName : 'Anonymous route',
          'charts' => [
            $this->buildMiniTimelineChart($samples, 'duration_ms'),
          ],
          'summary' => [
            'Range' => $range->getLabel(),
            'Calls' => number_format($requestCount),
            'Average duration' => number_format($averageDuration, 2) . ' ms',
            'Errors' => number_format($errorCount),
          ],
          'table_title' => 'Captured requests',
          'table_columns' => ['Trace ID', 'Timestamp', 'Path / Route', 'Status', 'Duration', 'AJAX'],
          'table_rows' => array_map(fn(array $sample): array => [
            'id' => 'trace-' . ($sample['trace_id'] ?? 0),
            'label' => (string) ($sample['trace_id'] ?? 0),
            'cells' => [
              (string) ($sample['trace_id'] ?? 0),
              (string) ($sample['started_at_label'] ?? '—'),
              (string) ($sample['path'] ?? '—') . "\n" . (string) ($sample['route_name'] ?? '—'),
              (string) ($sample['status_code'] ?? '—'),
              (string) ($sample['duration_ms'] ?? '0') . ' ms',
              !empty($sample['is_ajax']) ? 'Yes' : 'No',
            ],
            'detail' => $sample,
          ], $samples),
          'detail_title' => 'Live trace details',
          'detail_description' => 'Select a captured request to inspect the full trace payload and correlation metadata.',
        ],
      ];
    }, $rows);
  }

  /**
   * Returns the full AJAX endpoint dataset for the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $limit
   *   The maximum number of rows to return.
   *
   * @return array
   *   The normalized dataset rows.
   */
  public function getAjaxEndpointDataset(TimeRange $range, int $limit = 250): array {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', ['path', 'route_name']);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');
    $query->condition('is_ajax', 1);

    $query->addExpression('COUNT(t.id)', 'request_count');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->addExpression('SUM(CASE WHEN t.status_code >= 500 THEN 1 ELSE 0 END)', 'error_count');

    $query->groupBy('t.path');
    $query->groupBy('t.route_name');
    $query->orderBy('request_count', 'DESC');
    $query->range(0, $limit);

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    return array_map(function (array $row) use ($range): array {
      $path = (string) ($row['path'] ?? '');
      $routeName = (string) ($row['route_name'] ?? '');
      $requestCount = (int) ($row['request_count'] ?? 0);
      $averageDuration = round((float) ($row['avg_duration'] ?? 0), 2);
      $errorCount = (int) ($row['error_count'] ?? 0);
      $samples = $this->getTraceSamplesForEndpoint($range, $path, $routeName, TRUE);

      return [
        'path' => $path !== '' ? $path : '—',
        'route_name' => $routeName !== '' ? $routeName : '—',
        'request_count' => number_format($requestCount),
        'request_count_raw' => $requestCount,
        'avg_duration' => number_format($averageDuration, 2) . ' ms',
        'avg_duration_raw' => $averageDuration,
        'error_count' => number_format($errorCount),
        'error_count_raw' => $errorCount,
        'detail' => [
          'type' => 'ajax_endpoints',
          'title' => $path !== '' ? $path : 'Unnamed AJAX endpoint',
          'subtitle' => $routeName !== '' ? $routeName : 'Anonymous route',
          'charts' => [
            $this->buildMiniTimelineChart($samples, 'duration_ms'),
          ],
          'summary' => [
            'Range' => $range->getLabel(),
            'AJAX calls' => number_format($requestCount),
            'Average duration' => number_format($averageDuration, 2) . ' ms',
            'Errors' => number_format($errorCount),
          ],
          'table_title' => 'Captured AJAX requests',
          'table_columns' => ['Trace ID', 'Timestamp', 'Path / Route', 'Status', 'Duration', 'Parent'],
          'table_rows' => array_map(fn(array $sample): array => [
            'id' => 'trace-' . ($sample['trace_id'] ?? 0),
            'label' => (string) ($sample['trace_id'] ?? 0),
            'cells' => [
              (string) ($sample['trace_id'] ?? 0),
              (string) ($sample['started_at_label'] ?? '—'),
              (string) ($sample['path'] ?? '—') . "\n" . (string) ($sample['route_name'] ?? '—'),
              (string) ($sample['status_code'] ?? '—'),
              (string) ($sample['duration_ms'] ?? '0') . ' ms',
              (string) ($sample['parent_request_id'] ?? '—'),
            ],
            'detail' => $sample,
          ], $samples),
          'detail_title' => 'Live AJAX trace details',
          'detail_description' => 'Select a captured AJAX request to inspect the concrete trace payload, parent request correlation, and execution metadata.',
        ],
      ];
    }, $rows);
  }

  /**
   * Returns the outbound HTTP request dataset for the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $limit
   *   The maximum number of rows to return.
   *
   * @return array
   *   The normalized external request dataset rows.
   */
  public function getExternalRequestDataset(TimeRange $range, int $limit = 250): array {
    if (!$this->database->schema()->tableExists(self::SPANS_TABLE)) {
      return [];
    }

    $query = $this->database->select(self::SPANS_TABLE, 's');
    $spanFields = ['id', 'trace_id', 'span_id', 'name', 'category', 'duration_ms', 'payload', 'started_at_ms'];

    if ($this->database->schema()->fieldExists(self::SPANS_TABLE, 'request_id')) {
      $spanFields[] = 'request_id';
    }

    $query->fields('s', $spanFields);
    $query->condition('started_at_ms', $range->getStartTimestamp() * 1000, '>=');
    $query->condition('started_at_ms', $range->getEndTimestamp() * 1000, '<=');
    $query->orderBy('started_at_ms', 'DESC');

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    $aggregated = [];

    foreach ($rows as $row) {
      $payload = $this->decodePayload((string) ($row['payload'] ?? ''));

      $category = (string) ($row['category'] ?? '');
      $type = (string) ($payload['type'] ?? '');

      if (
        $category !== 'http_client' &&
        $category !== 'http.client' &&
        $category !== 'external.http' &&
        $type !== 'http_client' &&
        $type !== 'http.client' &&
        $type !== 'external.http'
      ) {
        continue;
      }

      $target = (string) (
        $payload['target']
        ?? $payload['url']
        ?? $payload['uri']
        ?? $payload['path']
        ?? $row['name']
        ?? 'Unknown target'
      );
      $method = strtoupper((string) ($payload['method'] ?? 'GET'));
      $statusCode = (int) ($payload['status_code'] ?? 0);
      $duration = (float) ($row['duration_ms'] ?? 0);
      $hasError = $statusCode >= 400;
      $key = $method . '|' . $target;

      if (!isset($aggregated[$key])) {
        $aggregated[$key] = [
          'target' => $target,
          'method' => $method,
          'request_count_raw' => 0,
          'avg_duration_accumulator' => 0.0,
          'error_count_raw' => 0,
          'samples' => [],
        ];
      }

      $aggregated[$key]['request_count_raw']++;
      $aggregated[$key]['avg_duration_accumulator'] += $duration;
      $aggregated[$key]['error_count_raw'] += $hasError ? 1 : 0;

      if (count($aggregated[$key]['samples']) < 25) {
        $aggregated[$key]['samples'][] = [
          'span_storage_id' => (int) ($row['id'] ?? 0),
          'span_id' => (string) ($row['span_id'] ?? ''),
          'trace_id' => (int) ($row['trace_id'] ?? 0),
          'request_id' => (string) ($row['request_id'] ?? ''),
          'target' => $target,
          'method' => $method,
          'status_code' => $statusCode,
          'duration_ms' => $duration,
          'started_at_label' => $this->formatTimestampFromMilliseconds((int) ($row['started_at_ms'] ?? 0)),
          'payload_pretty' => $this->prettyPrintPayload((string) ($row['payload'] ?? '')),
          'summary' => [
            'Span ID' => (string) ($row['span_id'] ?? '—'),
            'Trace ID' => (string) ($row['trace_id'] ?? '0'),
            'Request ID' => (string) ($row['request_id'] ?? '—'),
            'Method' => $method,
            'Target' => $target,
            'Status code' => $statusCode > 0 ? (string) $statusCode : '—',
            'Duration' => number_format($duration, 2) . ' ms',
            'Started at' => $this->formatTimestampFromMilliseconds((int) ($row['started_at_ms'] ?? 0)),
          ],
        ];
      }
    }

    foreach ($aggregated as &$item) {
      $count = max(1, (int) $item['request_count_raw']);
      $average = round($item['avg_duration_accumulator'] / $count, 2);

      $samples = $item['samples'];
      $item['request_count'] = number_format((int) $item['request_count_raw']);
      $item['avg_duration'] = number_format($average, 2) . ' ms';
      $item['avg_duration_raw'] = $average;
      $item['error_count'] = number_format((int) $item['error_count_raw']);
      $item['detail'] = [
        'type' => 'external_requests',
        'title' => $item['target'],
        'subtitle' => $item['method'] . ' outbound traffic',
        'charts' => [
          $this->buildMiniTimelineChart($samples, 'duration_ms'),
        ],
        'summary' => [
          'Method' => $item['method'],
          'External calls' => number_format((int) $item['request_count_raw']),
          'Average duration' => number_format($average, 2) . ' ms',
          'Errors' => number_format((int) $item['error_count_raw']),
        ],
        'table_title' => 'Captured external calls',
        'table_columns' => ['Span ID', 'Trace ID', 'Method', 'Status', 'Started at', 'Duration'],
        'table_rows' => array_map(static fn(array $sample): array => [
          'id' => 'span-' . ($sample['span_id'] ?: $sample['span_storage_id']),
          'label' => (string) ($sample['span_id'] ?: $sample['span_storage_id']),
          'cells' => [
            (string) ($sample['span_id'] ?: $sample['span_storage_id']),
            (string) ($sample['trace_id'] ?? '0'),
            (string) ($sample['method'] ?? 'GET'),
            ($sample['status_code'] ?? 0) > 0 ? (string) $sample['status_code'] : '—',
            (string) ($sample['started_at_label'] ?? '—'),
            number_format((float) ($sample['duration_ms'] ?? 0), 2) . ' ms',
          ],
          'detail' => $sample,
        ], $samples),
        'detail_title' => 'Live external call details',
        'detail_description' => 'Select an outbound span to inspect the exact payload captured by the HTTP client span recorder.',
      ];

      unset($item['avg_duration_accumulator'], $item['samples']);
    }
    unset($item);

    uasort($aggregated, static fn(array $a, array $b): int => $b['request_count_raw'] <=> $a['request_count_raw']);

    return array_values(array_slice($aggregated, 0, $limit));
  }

  /**
   * Returns the most recent failed requests in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $limit
   *   The maximum number of rows to return.
   *
   * @return array
   *   A list of recent failures.
   */
  public function getRecentFailures(TimeRange $range, int $limit = 5): array {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', [
      'path',
      'route_name',
      'status_code',
      'duration_ms',
      'started_at',
      'is_ajax',
    ]);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');
    $query->condition('status_code', 500, '>=');
    $query->orderBy('started_at', 'DESC');
    $query->range(0, $limit);

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    return array_map(static function (array $row): array {
      return [
        'path' => $row['path'] ?? '',
        'route_name' => $row['route_name'] ?? '',
        'status_code' => (int) ($row['status_code'] ?? 0),
        'duration_ms' => (int) ($row['duration_ms'] ?? 0),
        'started_at' => (int) ($row['started_at'] ?? 0),
        'is_ajax' => !empty($row['is_ajax']),
      ];
    }, $rows);
  }

  /**
   * Counts traces in the selected range, optionally filtering AJAX and errors.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   * @param bool $errorsOnly
   *   Whether to filter only server error traces.
   *
   * @return int
   *   The number of matching traces.
   */
  private function countByRange(TimeRange $range, ?bool $ajaxOnly = NULL, bool $errorsOnly = FALSE): int {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');

    if ($ajaxOnly !== NULL) {
      $query->condition('is_ajax', $ajaxOnly ? 1 : 0);
    }

    if ($errorsOnly) {
      $query->condition('status_code', 500, '>=');
    }

    return (int) $query->countQuery()->execute()->fetchField();
  }

  /**
   * Calculates the average duration in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   *
   * @return float
   *   The calculated average duration.
   */
  private function averageDurationByRange(TimeRange $range, ?bool $ajaxOnly = NULL): float {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');

    if ($ajaxOnly !== NULL) {
      $query->condition('is_ajax', $ajaxOnly ? 1 : 0);
    }

    $query->addExpression('AVG(duration_ms)', 'avg_duration');

    return round((float) $query->execute()->fetchField(), 2);
  }

  /**
   * Calculates the P95 duration in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return float
   *   The calculated P95 duration.
   */
  private function p95DurationByRange(TimeRange $range): float {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', ['duration_ms']);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');
    $query->orderBy('duration_ms', 'ASC');

    $durations = array_map('floatval', $query->execute()->fetchCol());

    if ($durations === []) {
      return 0.0;
    }

    $index = (int) ceil(count($durations) * 0.95) - 1;
    $index = max(0, min($index, count($durations) - 1));

    return round($durations[$index], 2);
  }

  /**
   * Builds a count series in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   *
   * @return array
   *   The generated series.
   */
  private function buildCountSeries(TimeRange $range, int $bucketSize, ?bool $ajaxOnly = NULL): array {
    $series = [];

    foreach ($this->buildBuckets($range, $bucketSize) as [$bucketStart, $bucketEnd]) {
      $query = $this->database->select(self::TRACE_TABLE, 't');
      $query->condition('started_at', $bucketStart, '>=');
      $query->condition('started_at', $bucketEnd, '<');

      if ($ajaxOnly !== NULL) {
        $query->condition('is_ajax', $ajaxOnly ? 1 : 0);
      }

      $count = (int) $query->countQuery()->execute()->fetchField();

      $series[] = [
        'label' => $this->formatBucketLabel($bucketStart, $bucketEnd),
        'value' => $count,
      ];
    }

    return $series;
  }

  /**
   * Builds a requests-per-second series in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   *
   * @return array
   *   The generated series.
   */
  private function buildRequestsPerSecondSeries(TimeRange $range, int $bucketSize, ?bool $ajaxOnly = NULL): array {
    $series = [];

    foreach ($this->buildBuckets($range, $bucketSize) as [$bucketStart, $bucketEnd]) {
      $query = $this->database->select(self::TRACE_TABLE, 't');
      $query->condition('started_at', $bucketStart, '>=');
      $query->condition('started_at', $bucketEnd, '<');

      if ($ajaxOnly !== NULL) {
        $query->condition('is_ajax', $ajaxOnly ? 1 : 0);
      }

      $count = (int) $query->countQuery()->execute()->fetchField();

      $series[] = [
        'label' => $this->formatBucketLabel($bucketStart, $bucketEnd),
        'value' => round($count / max(1, $bucketEnd - $bucketStart), 2),
      ];
    }

    return $series;
  }

  /**
   * Builds an average duration series in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   *
   * @return array
   *   The generated series.
   */
  private function buildAverageDurationSeries(TimeRange $range, int $bucketSize, ?bool $ajaxOnly = NULL): array {
    $series = [];

    foreach ($this->buildBuckets($range, $bucketSize) as [$bucketStart, $bucketEnd]) {
      $query = $this->database->select(self::TRACE_TABLE, 't');
      $query->condition('started_at', $bucketStart, '>=');
      $query->condition('started_at', $bucketEnd, '<');

      if ($ajaxOnly !== NULL) {
        $query->condition('is_ajax', $ajaxOnly ? 1 : 0);
      }

      $query->addExpression('AVG(duration_ms)', 'avg_duration');

      $series[] = [
        'label' => $this->formatBucketLabel($bucketStart, $bucketEnd),
        'value' => round((float) $query->execute()->fetchField(), 2),
      ];
    }

    return $series;
  }

  /**
   * Builds an error rate series in the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   * @param bool|null $ajaxOnly
   *   Whether to filter only AJAX traces, non-AJAX traces, or leave unfiltered.
   *
   * @return array
   *   The generated series.
   */
  private function buildErrorRateSeries(TimeRange $range, int $bucketSize, ?bool $ajaxOnly = NULL): array {
    $series = [];

    foreach ($this->buildBuckets($range, $bucketSize) as [$bucketStart, $bucketEnd]) {
      $totalQuery = $this->database->select(self::TRACE_TABLE, 't');
      $totalQuery->condition('started_at', $bucketStart, '>=');
      $totalQuery->condition('started_at', $bucketEnd, '<');

      $errorQuery = $this->database->select(self::TRACE_TABLE, 't');
      $errorQuery->condition('started_at', $bucketStart, '>=');
      $errorQuery->condition('started_at', $bucketEnd, '<');
      $errorQuery->condition('status_code', 500, '>=');

      if ($ajaxOnly !== NULL) {
        $ajaxValue = $ajaxOnly ? 1 : 0;
        $totalQuery->condition('is_ajax', $ajaxValue);
        $errorQuery->condition('is_ajax', $ajaxValue);
      }

      $total = (int) $totalQuery->countQuery()->execute()->fetchField();
      $errors = (int) $errorQuery->countQuery()->execute()->fetchField();

      $series[] = [
        'label' => $this->formatBucketLabel($bucketStart, $bucketEnd),
        'value' => $total > 0 ? round(($errors / $total) * 100, 2) : 0.0,
      ];
    }

    return $series;
  }

  /**
   * Builds time buckets for the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucketSize
   *   The bucket size in seconds.
   *
   * @return array
   *   A list of [start, end] buckets.
   */
  private function buildBuckets(TimeRange $range, int $bucketSize): array {
    $buckets = [];

    for ($bucketStart = $range->getStartTimestamp(); $bucketStart < $range->getEndTimestamp(); $bucketStart += $bucketSize) {
      $bucketEnd = min($bucketStart + $bucketSize, $range->getEndTimestamp());
      $buckets[] = [$bucketStart, $bucketEnd];
    }

    return $buckets;
  }

  /**
   * Formats a bucket label for chart rendering.
   *
   * @param int $bucketStart
   *   The bucket start timestamp.
   * @param int $bucketEnd
   *   The bucket end timestamp.
   *
   * @return string
   *   The formatted label.
   */
  private function formatBucketLabel(int $bucketStart, int $bucketEnd): string {
    return date('H:i', $bucketStart) . '–' . date('H:i', $bucketEnd);
  }

  /**
   * Loads representative trace samples for a specific endpoint.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param string $path
   *   The normalized request path.
   * @param string $routeName
   *   The resolved route name.
   * @param bool|null $ajaxOnly
   *   Whether to restrict results to AJAX traces, non-AJAX traces, or both.
   * @param int $limit
   *   The maximum number of trace samples to return.
   *
   * @return array<int, array<string, mixed>>
   *   A list of representative trace samples for the endpoint.
   */
  private function getTraceSamplesForEndpoint(TimeRange $range, string $path, string $routeName, ?bool $ajaxOnly = NULL, int $limit = 25): array {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->fields('t', [
      'id',
      'request_id',
      'method',
      'path',
      'route_name',
      'status_code',
      'duration_ms',
      'started_at',
      'ended_at',
      'is_ajax',
      'uid',
      'client_ip',
      'payload',
    ]);
    $query->condition('started_at', $range->getStartTimestamp(), '>=');
    $query->condition('started_at', $range->getEndTimestamp(), '<=');
    $query->condition('path', $path);

    if ($routeName !== '') {
      $query->condition('route_name', $routeName);
    }
    else {
      $query->isNull('route_name');
    }

    if ($ajaxOnly !== NULL) {
      $query->condition('is_ajax', $ajaxOnly ? 1 : 0);
    }

    $query->orderBy('started_at', 'DESC');
    $query->range(0, $limit);

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    return array_map(function (array $row): array {
      $payload = $this->decodePayload((string) ($row['payload'] ?? ''));

      return [
        'trace_id' => (int) ($row['id'] ?? 0),
        'request_id' => (string) ($row['request_id'] ?? ''),
        'method' => (string) ($row['method'] ?? 'GET'),
        'path' => (string) ($row['path'] ?? '—'),
        'route_name' => (string) ($row['route_name'] ?? '—') ?: '—',
        'status_code' => (int) ($row['status_code'] ?? 0),
        'duration_ms' => (int) ($row['duration_ms'] ?? 0),
        'started_at_label' => $this->formatTimestamp((int) ($row['started_at'] ?? 0)),
        'ended_at_label' => !empty($row['ended_at']) ? $this->formatTimestamp((int) $row['ended_at']) : '—',
        'is_ajax' => !empty($row['is_ajax']),
        'parent_request_id' => (string) ($payload['parent_request_id'] ?? ''),
        'payload_pretty' => $this->prettyPrintPayload((string) ($row['payload'] ?? '')),
        'summary' => [
          'Trace ID' => (string) ($row['id'] ?? '0'),
          'Request ID' => (string) ($row['request_id'] ?? '—'),
          'Method' => (string) ($row['method'] ?? 'GET'),
          'Path' => (string) ($row['path'] ?? '—'),
          'Route' => (string) ($row['route_name'] ?? '—') ?: '—',
          'Status code' => (string) ($row['status_code'] ?? '—'),
          'Duration' => (string) ($row['duration_ms'] ?? '0') . ' ms',
          'Started' => $this->formatTimestamp((int) ($row['started_at'] ?? 0)),
          'Ended' => !empty($row['ended_at']) ? $this->formatTimestamp((int) $row['ended_at']) : '—',
          'AJAX request' => !empty($row['is_ajax']) ? 'Yes' : 'No',
          'User ID' => isset($row['uid']) ? (string) $row['uid'] : '—',
          'Client IP' => (string) ($row['client_ip'] ?? '—'),
          'Parent request' => (string) ($payload['parent_request_id'] ?? '—'),
        ],
      ];
    }, $rows);
  }

  /**
   * Decodes a stored JSON payload into an array.
   *
   * @param string $payload
   *   The raw JSON payload.
   *
   * @return array<string, mixed>
   *   The decoded payload, or an empty array when invalid.
   */
  private function decodePayload(string $payload): array {
    $payload = trim($payload);
    if ($payload === '') {
      return [];
    }

    $decoded = json_decode($payload, TRUE);
    return is_array($decoded) ? $decoded : [];
  }

  /**
   * Pretty-prints a JSON payload for diagnostics output.
   *
   * @param string $payload
   *   The raw JSON payload.
   *
   * @return string
   *   The formatted payload string.
   */
  private function prettyPrintPayload(string $payload): string {
    $decoded = $this->decodePayload($payload);

    if ($decoded === []) {
      return trim($payload);
    }

    return json_encode($decoded, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) ?: trim($payload);
  }

  /**
   * Formats a UNIX timestamp for display.
   *
   * @param int $timestamp
   *   The UNIX timestamp.
   *
   * @return string
   *   A human-readable date label.
   */
  private function formatTimestamp(int $timestamp): string {
    if ($timestamp <= 0) {
      return '—';
    }

    return gmdate('d/m/Y H:i:s', $timestamp);
  }

  /**
   * Formats a millisecond timestamp for display.
   *
   * @param int $timestampMs
   *   The UNIX timestamp in milliseconds.
   *
   * @return string
   *   A human-readable date label.
   */
  private function formatTimestampFromMilliseconds(int $timestampMs): string {
    if ($timestampMs <= 0) {
      return '—';
    }

    return gmdate('d/m/Y H:i:s', (int) floor($timestampMs / 1000));
  }

  /**
   * Builds a compact timeline chart data structure.
   *
   * @param array<int, array<string, mixed>> $samples
   *   The source sample rows.
   * @param string $labelKey
   *   The numeric field to plot.
   *
   * @return array<int, array<string, int|string>>
   *   Normalized chart points.
   */
  private function buildMiniTimelineChart(array $samples, string $labelKey = 'duration_ms'): array {
    $points = [];

    foreach (array_reverse($samples) as $sample) {
      $points[] = [
        'label' => (string) ($sample['started_at_label'] ?? '—'),
        'value' => (float) ($sample[$labelKey] ?? 0),
      ];
    }

    return [
      'type' => 'line',
      'title' => 'Timeline overview',
      'series' => $points,
    ];
  }

  /**
   * Returns span category breakdown for a single request.
   *
   * Spans are correlated by request_id because trace_id backfill happens
   * after span recording. Loads all spans for the request, groups by
   * category, and returns avg duration and count per category.
   *
   * @param string $requestId
   *   The request ID to look up.
   *
   * @return array
   *   Rows with category, avg_duration_ms, count. Empty array when no spans.
   */
  public function getSpanBreakdownForRequest(string $requestId): array {
    if ($requestId === '' || !$this->database->schema()->tableExists(self::SPANS_TABLE)) {
      return [];
    }

    $result = $this->getSpanBreakdownBatch([$requestId]);
    return $result[$requestId] ?? [];
  }

  /**
   * Returns span category breakdown for multiple requests in one query.
   *
   * Uses a single IN query to avoid N+1 patterns when enriching many trace
   * samples at once. Results are keyed by request_id.
   *
   * @param string[] $requestIds
   *   The request IDs to look up.
   *
   * @return array<string, array>
   *   Breakdown rows keyed by request_id. Each value is an array of
   *   [category, label, avg_duration_ms, count].
   */
  public function getSpanBreakdownBatch(array $requestIds): array {
    $requestIds = array_values(array_filter($requestIds, static fn(string $id): bool => $id !== ''));

    if ($requestIds === [] || !$this->database->schema()->tableExists(self::SPANS_TABLE)) {
      return [];
    }

    $query = $this->database->select(self::SPANS_TABLE, 's');
    $query->fields('s', ['request_id', 'category']);
    $query->addExpression('AVG(s.duration_ms)', 'avg_duration_ms');
    $query->addExpression('COUNT(*)', 'count');
    $query->condition('s.request_id', $requestIds, 'IN');
    $query->isNotNull('s.duration_ms');
    $query->groupBy('s.request_id');
    $query->groupBy('s.category');

    $rows = $query->execute()->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    $labels = [
      'database' => 'Database',
      'http.client' => 'External HTTP',
      'http' => 'HTTP summary',
      'drupal' => 'Drupal overhead',
    ];

    $result = [];
    foreach ($rows as $row) {
      $requestId = (string) ($row['request_id'] ?? '');
      $category = (string) ($row['category'] ?? '');

      if ($requestId === '') {
        continue;
      }

      $result[$requestId][] = [
        'category' => $category,
        'label' => $labels[$category] ?? $category,
        'avg_duration_ms' => round((float) ($row['avg_duration_ms'] ?? 0), 2),
        'count' => (int) ($row['count'] ?? 0),
      ];
    }

    return $result;
  }

}
