<?php

declare(strict_types=1);

namespace Drupal\native_observability_dashboard\Service;

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

/**
 * Builds the span breakdown dashboard section.
 *
 * Aggregates span category durations (database, http.client, drupal overhead)
 * from the native_observability_spans table for the selected time range.
 * Drupal overhead is derived as the residual between average total trace
 * duration and the sum of known span category averages.
 *
 * When the active range has no span data, falls back to the most recent
 * available window and signals the fallback via is_fallback/fallback_label.
 *
 * This builder is intentionally defensive:
 * - Returns enabled: false when the spans table does not exist or is empty.
 * - Never breaks the dashboard pipeline on missing data.
 */
final class SpanBreakdownSectionBuilder {

  use StringTranslationTrait;

  /**
   * The spans DB table name.
   */
  private const SPANS_TABLE = 'native_observability_spans';

  /**
   * The traces DB table name.
   */
  private const TRACE_TABLE = 'native_observability_trace';

  /**
   * Span categories tracked in the breakdown.
   */
  private const TRACKED_CATEGORIES = ['database', 'http.client'];

  /**
   * Creates a new span breakdown section builder instance.
   *
   * @param \Drupal\Core\Database\Connection $database
   *   The active database connection.
   * @param \Drupal\native_observability_dashboard\Service\OptionalSectionResolver $optionalSectionResolver
   *   Resolves optional section availability based on active modules.
   */
  public function __construct(
    private readonly Connection $database,
    private readonly OptionalSectionResolver $optionalSectionResolver,
  ) {
  }

  /**
   * Builds the span breakdown section payload.
   *
   * Tries the active range first. If no span data is found, falls back to
   * the most recent available window of the same duration.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange|null $range
   *   The selected dashboard time range.
   *
   * @return array
   *   The section payload ready for Twig rendering.
   */
  public function build(?TimeRange $range = NULL): array {
    if (!$this->optionalSectionResolver->isSpanBreakdownAvailable()) {
      return [
        'enabled' => FALSE,
        'title' => (string) $this->t('Performance Map'),
        'reason' => (string) $this->t('The span recorder module is not enabled.'),
      ];
    }

    if ($range === NULL) {
      return [
        'enabled' => FALSE,
        'title' => (string) $this->t('Performance Map'),
        'reason' => (string) $this->t('No time range selected.'),
      ];
    }

    $startMs = $range->getStartTimestamp() * 1000;
    $endMs = $range->getEndTimestamp() * 1000;
    $rangeDurationMs = $range->getDurationInSeconds() * 1000;

    $categoryAverages = $this->queryCategoryAveragesForWindow($startMs, $endMs);
    $isFallback = FALSE;
    $fallbackLabel = '';
    $effectiveStartTs = $range->getStartTimestamp();
    $effectiveEndTs = $range->getEndTimestamp();

    if (empty($categoryAverages)) {
      $latestMs = $this->findLatestSpanMs();

      if ($latestMs === NULL) {
        return [
          'enabled' => FALSE,
          'title' => (string) $this->t('Performance Map'),
          'reason' => (string) $this->t('No span data recorded yet.'),
        ];
      }

      $fallbackEndMs = $latestMs;
      $fallbackStartMs = $latestMs - $rangeDurationMs;
      $categoryAverages = $this->queryCategoryAveragesForWindow(
        $fallbackStartMs,
        $fallbackEndMs,
      );

      if (empty($categoryAverages)) {
        return [
          'enabled' => FALSE,
          'title' => (string) $this->t('Performance Map'),
          'reason' => (string) $this->t('No span data recorded for the selected time range.'),
        ];
      }

      $isFallback = TRUE;
      $fallbackLabel = $this->buildFallbackLabel($latestMs);
      $effectiveStartTs = (int) floor($fallbackStartMs / 1000);
      $effectiveEndTs = (int) floor($fallbackEndMs / 1000);

      $fallbackRange = new TimeRange(
        $effectiveStartTs,
        $effectiveEndTs,
        $range->getLabel(),
        $range->getKey(),
      );
      $traceAvg = $this->queryTraceAverageDuration($fallbackRange);
    }
    else {
      $traceAvg = $this->queryTraceAverageDuration($range);
    }

    $knownSum = array_sum(array_column($categoryAverages, 'avg_duration_ms'));
    $overhead = max(0.0, round($traceAvg - $knownSum, 2));

    $breakdown = $categoryAverages;
    $breakdown[] = [
      'category' => 'drupal',
      'label' => (string) $this->t('Drupal overhead'),
      'avg_duration_ms' => $overhead,
      'count' => 0,
    ];

    $ajaxAvg = $this->queryAjaxAverageDuration($effectiveStartTs, $effectiveEndTs);
    if ($ajaxAvg > 0.0) {
      $breakdown[] = [
        'category' => 'ajax',
        'label' => (string) $this->t('AJAX'),
        'avg_duration_ms' => $ajaxAvg,
        'count' => 0,
      ];
    }

    $httpAvg = $this->queryHttpAverageDuration($effectiveStartTs, $effectiveEndTs);
    if ($httpAvg > 0.0) {
      $breakdown[] = [
        'category' => 'http',
        'label' => (string) $this->t('HTTP Requests'),
        'avg_duration_ms' => $httpAvg,
        'count' => 0,
      ];
    }

    $payload = [
      'enabled' => TRUE,
      'title' => (string) $this->t('Performance Map'),
      'time_range' => $range->getLabel(),
      'trace_avg_ms' => $traceAvg,
      'breakdown' => $breakdown,
      'is_fallback' => $isFallback,
    ];

    if ($isFallback && $fallbackLabel !== '') {
      $payload['fallback_label'] = $fallbackLabel;
    }

    return $payload;
  }

  /**
   * Queries average duration per span category for an explicit window.
   *
   * @param int $startMs
   *   Window start in milliseconds.
   * @param int $endMs
   *   Window end in milliseconds.
   *
   * @return array
   *   Rows with category, label, avg_duration_ms, count.
   */
  private function queryCategoryAveragesForWindow(int $startMs, int $endMs): array {
    $query = $this->database->select(self::SPANS_TABLE, 's');
    $query->fields('s', ['category']);
    $query->addExpression('AVG(s.duration_ms)', 'avg_duration_ms');
    $query->addExpression('COUNT(*)', 'count');
    $query->condition('s.started_at_ms', $startMs, '>=');
    $query->condition('s.started_at_ms', $endMs, '<=');
    $query->condition('s.category', self::TRACKED_CATEGORIES, 'IN');
    $query->isNotNull('s.duration_ms');
    $query->groupBy('s.category');
    $query->orderBy('avg_duration_ms', 'DESC');

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

    $labels = [
      'database' => (string) $this->t('Database'),
      'http.client' => (string) $this->t('Outbound HTTP'),
    ];

    return array_map(static function (array $row) use ($labels): array {
      $category = (string) ($row['category'] ?? '');
      return [
        'category' => $category,
        'label' => $labels[$category] ?? $category,
        'avg_duration_ms' => round((float) ($row['avg_duration_ms'] ?? 0), 2),
        'count' => (int) ($row['count'] ?? 0),
      ];
    }, $rows);
  }

  /**
   * Returns the most recent span timestamp in milliseconds, or NULL.
   *
   * @return int|null
   *   The MAX started_at_ms value, or NULL when no valid rows exist.
   */
  private function findLatestSpanMs(): ?int {
    $query = $this->database->select(self::SPANS_TABLE, 's');
    $query->addExpression('MAX(s.started_at_ms)', 'latest_ms');
    $query->condition('s.category', self::TRACKED_CATEGORIES, 'IN');
    $query->isNotNull('s.duration_ms');

    $value = $query->execute()->fetchField();

    return $value !== NULL && $value !== FALSE ? (int) $value : NULL;
  }

  /**
   * Returns the average duration of non-AJAX HTTP traces in the window.
   *
   * @param int $startTs
   *   Window start as Unix timestamp in seconds.
   * @param int $endTs
   *   Window end as Unix timestamp in seconds.
   *
   * @return float
   *   Average non-AJAX trace duration in milliseconds, or 0.0 when none found.
   */
  private function queryHttpAverageDuration(int $startTs, int $endTs): float {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->condition('t.started_at', $startTs, '>=');
    $query->condition('t.started_at', $endTs, '<=');
    $query->condition('t.is_ajax', 0);

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

  /**
   * Returns the average duration of AJAX-originating traces in the window.
   *
   * Uses the trace table (which has a dedicated is_ajax column) rather than
   * parsing span JSON payloads, so the query is fast and index-friendly.
   *
   * @param int $startTs
   *   Window start as Unix timestamp in seconds.
   * @param int $endTs
   *   Window end as Unix timestamp in seconds.
   *
   * @return float
   *   Average AJAX trace duration in milliseconds, or 0.0 when none found.
   */
  private function queryAjaxAverageDuration(int $startTs, int $endTs): float {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->condition('t.started_at', $startTs, '>=');
    $query->condition('t.started_at', $endTs, '<=');
    $query->condition('t.is_ajax', 1);

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

  /**
   * Builds a human-readable fallback label from the latest span timestamp.
   *
   * @param int $latestMs
   *   The most recent span timestamp in milliseconds.
   *
   * @return string
   *   A label such as "Showing data from 3 hours ago".
   */
  private function buildFallbackLabel(int $latestMs): string {
    $ageSeconds = time() - (int) floor($latestMs / 1000);
    $ageSeconds = max(0, $ageSeconds);

    if ($ageSeconds < 3600) {
      $minutes = (int) round($ageSeconds / 60);
      $unit = $minutes === 1 ? 'minute' : 'minutes';
      $age = "$minutes $unit";
    }
    elseif ($ageSeconds < 86400) {
      $hours = (int) round($ageSeconds / 3600);
      $unit = $hours === 1 ? 'hour' : 'hours';
      $age = "$hours $unit";
    }
    else {
      $days = (int) round($ageSeconds / 86400);
      $unit = $days === 1 ? 'day' : 'days';
      $age = "$days $unit";
    }

    return "Showing data from $age ago";
  }

  /**
   * Returns the average trace duration for the given time range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected time range.
   *
   * @return float
   *   Average trace duration in milliseconds.
   */
  private function queryTraceAverageDuration(TimeRange $range): float {
    $query = $this->database->select(self::TRACE_TABLE, 't');
    $query->addExpression('AVG(t.duration_ms)', 'avg_duration');
    $query->condition('t.started_at', $range->getStartTimestamp(), '>=');
    $query->condition('t.started_at', $range->getEndTimestamp(), '<=');

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

}
