<?php

declare(strict_types=1);

namespace Drupal\native_observability_dashboard\Service;

use Drupal\Core\Database\Connection;
use Drupal\Core\Extension\ModuleHandlerInterface;
use Drupal\Core\StringTranslation\StringTranslationTrait;
use Drupal\native_observability_cache_observer\Service\CacheObserverEnvironmentSnapshotManager;
use Drupal\native_observability_cache_observer\Service\CacheObserverStorage;
use Drupal\native_observability_dashboard\ValueObject\TimeRange;
use Drupal\Core\Url;

/**
 * Builds the cache runtime health dashboard section.
 *
 * This builder is intentionally defensive:
 * - It must work even when the cache observer submodule is not enabled.
 * - It must never assume that cache observer services are always available.
 * - It exposes only high-level system cache signals for the selected range.
 */
final class CacheObservabilitySectionBuilder {

  use StringTranslationTrait;

  /**
   * Creates a new cache observability section builder instance.
   *
   * @param \Drupal\Core\Extension\ModuleHandlerInterface $moduleHandler
   *   The module handler service.
   * @param \Drupal\Core\Database\Connection $database
   *   The database connection used to aggregate cache observer events.
   * @param CacheObservabilityMetricsBuilder $metricsBuilder
   *   The derived cache metrics builder.
   * @param \Drupal\native_observability_cache_observer\Service\CacheObserverStorage|null $cacheObserverStorage
   *   The optional cache observer storage service.
   * @param \Drupal\native_observability_cache_observer\Service\CacheObserverEnvironmentSnapshotManager|null $snapshotManager
   *   The optional cache environment snapshot manager.
   */
  public function __construct(
    private readonly ModuleHandlerInterface $moduleHandler,
    private readonly Connection $database,
    private readonly CacheObservabilityMetricsBuilder $metricsBuilder,
    private readonly ?CacheObserverStorage $cacheObserverStorage = NULL,
    private readonly ?CacheObserverEnvironmentSnapshotManager $snapshotManager = NULL,
  ) {
  }

  /**
   * Builds the full cache observability section payload.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange|null $range
   *   The selected dashboard time range.
   *
   * @return array
   *   A render-ready section payload.
   */
  public function build(?TimeRange $range = NULL): array {
    $range ??= TimeRange::createDefault();

    $section = [
      'enabled' => FALSE,
      'id' => 'cache_observability',
      'title' => $this->t('Cache Runtime Health'),
      'subtitle' => $this->t('High-level cache status and backend configuration for the selected environment.'),
      'time_range' => $range->getLabel(),
      'environment_notice' => [
        'enabled' => FALSE,
        'status' => 'info',
        'title' => '',
        'message' => '',
      ],
      'runtime_records' => [
        'enabled' => FALSE,
        'title' => $this->t('Cache Runtime Records'),
        'description' => $this->t('Inspect cache-related records that are not primarily represented through the route-centric overview.'),
        'headers' => [],
        'rows' => [],
        'export_url' => '',
        'empty_message' => $this->t('No cache runtime records are available for the selected range.'),
      ],
      'health' => [],
      'signals' => [
        'enabled' => FALSE,
        'title' => $this->t('Cache Signals'),
        'description' => $this->t('Availability of response cache headers, cacheability metadata and invalidation signals in the selected time range.'),
        'items' => [],
      ],
      'behavior' => [
        'enabled' => FALSE,
        'title' => $this->t('Cache Behavior Overview'),
        'description' => $this->t('Derived hit, miss and cacheability behavior extracted from synthetic HTTP request summary spans.'),
        'overall' => [],
        'layers' => [],
      ],
      'cacheability' => [
        'enabled' => FALSE,
        'title' => $this->t('Cacheability Analysis'),
        'description' => $this->t('Derived cacheability metrics based on request summary span metadata and cache observer events collected during the selected range.'),
        'summary' => [],
        'distributions' => [],
      ],
      'invalidations' => [
        'enabled' => FALSE,
        'title' => $this->t('Invalidation Analysis'),
        'description' => $this->t('Derived invalidation pressure indicators across the selected time range.'),
        'summary' => [],
      ],
      'insights' => [
        'enabled' => FALSE,
        'title' => $this->t('Cache Insights'),
        'items' => [],
      ],
      'efficiency_score' => [
        'enabled' => FALSE,
        'title' => $this->t('Cache Efficiency Score'),
        'value' => 0,
        'max' => 100,
        'status' => 'warning',
        'breakdown' => [],
      ],
      'activity_trend' => [
        'enabled' => FALSE,
        'title' => $this->t('Cache Activity Trend'),
        'description' => $this->t('Track response cache signals, cacheability checks and invalidation activity across the selected time range.'),
        'chart_id' => 'cache-activity-trend',
        'series' => [],
        'summary' => [
          'total_events' => 0,
          'response_cache_events' => 0,
          'cacheability_events' => 0,
          'invalidation_events' => 0,
        ],
        'reason' => '',
        'help_message' => '',
      ],
      'reason' => '',
      'help_message' => '',
    ];

    // Stop early when the cache observer module is not enabled.
    if (!$this->moduleHandler->moduleExists('native_observability_cache_observer')) {
      $section['reason'] = $this->t('The Cache Observer module is not enabled.');
      $section['help_message'] = $this->t('Enable the Cache Observer submodule to expose cache runtime health insights on the dashboard.');
      return $section;
    }

    // Stop early when the environment snapshot service is not available.
    if (!$this->snapshotManager) {
      $section['reason'] = $this->t('The cache snapshot service is not currently available.');
      $section['help_message'] = $this->t('Rebuild the container and verify that the Cache Observer module is installed correctly.');
      return $section;
    }

    $snapshot = $this->snapshotManager->getSnapshot();

    $section['enabled'] = TRUE;
    $section['health'] = $this->buildHealthSection($snapshot);
    $section['environment_notice'] = $this->buildEnvironmentNotice($snapshot, $section['health']);

    $metrics = $this->metricsBuilder->build($range);

    $section['signals'] = $this->buildSignalsSection($metrics);
    $section['behavior'] = $this->buildBehaviorSection($metrics);
    $section['cacheability'] = $this->buildCacheabilitySection($metrics);
    $section['invalidations'] = $this->buildInvalidationsSection($metrics);
    $section['insights'] = $this->buildInsightsSection($metrics);
    $section['efficiency_score'] = $this->buildEfficiencyScoreSection($metrics);
    $section['activity_trend'] = $this->buildActivityTrend($range, $metrics);
    $section['runtime_records'] = $this->buildRuntimeRecordsSection($range);

    return $section;
  }

  /**
   * Builds the cache runtime records table section.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected time range.
   *
   * @return array
   *   The runtime records section payload.
   */
  private function buildRuntimeRecordsSection(TimeRange $range): array {
    $headers = [
      $this->t('Time'),
      $this->t('Event type'),
      $this->t('Layer'),
      $this->t('Method'),
      $this->t('Context'),
      $this->t('Status'),
      $this->t('Actions'),
    ];

    $storage = $this->cacheObserverStorage ?? NULL;

    if (!$storage) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('Cache Runtime Records'),
        'description' => $this->t('Inspect cache-related records that are not primarily represented through the route-centric overview.'),
        'headers' => $headers,
        'rows' => [],
        'export_url' => '',
        'empty_message' => $this->t('The cache observer storage service is not available.'),
      ];
    }

    if (!method_exists($storage, 'search')) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('Cache Runtime Records'),
        'description' => $this->t('Inspect cache-related records that are not primarily represented through the route-centric overview.'),
        'headers' => $headers,
        'rows' => [],
        'export_url' => '',
        'empty_message' => $this->t('The configured cache observer storage service does not expose a compatible search method.'),
      ];
    }

    $records = $storage->search([
      'created_at_min' => $range->getStartTimestamp(),
      'created_at_max' => $range->getEndTimestamp(),
      'sort' => 'created_at',
      'direction' => 'DESC',
    ], 200, 0);

    $rows = [];

    foreach ($records as $record) {
      $payload = is_array($record['payload'] ?? NULL) ? $record['payload'] : [];

      $routeName = (string) ($record['route_name'] ?? $payload['route_name'] ?? '');
      $path = (string) ($record['path'] ?? $payload['path'] ?? $payload['request_uri'] ?? '');
      $normalizedPath = strtolower(trim($path));

      $isDashboardRecord =
        str_contains($normalizedPath, '/admin/reports/native-observability') ||
        str_contains($normalizedPath, '/dashboard/cache') ||
        str_contains($normalizedPath, '/dashboard');

      // Exclude route-centric records and dashboard self-observation records,
      // but keep generic cache runtime records even if they contain a path.
      if ($routeName !== '' || $isDashboardRecord) {
        continue;
      }

      $context = (string) ($record['cache_layer'] ?? $payload['cache_context'] ?? $path ?: 'n/a');
      $status = (string) ($record['status'] ?? $payload['cacheability_status'] ?? $payload['dynamic_page_cache_status'] ?? 'n/a');
      $method = (string) ($record['method'] ?? $payload['method'] ?? 'GET');
      $recordId = (string) ($record['id'] ?? '');

      $rows[] = [
        'id' => $recordId,
        'time' => (string) ($record['created_at_formatted'] ?? date('Y-m-d H:i:s', (int) ($record['created_at'] ?? time()))),
        'event_type' => (string) ($record['event_type'] ?? 'n/a'),
        'cache_layer' => (string) ($record['cache_layer'] ?? 'n/a'),
        'method' => $method,
        'context' => $context,
        'status' => $status,
        'details' => [
          'id' => $recordId,
          'time' => (string) ($record['created_at_formatted'] ?? date('Y-m-d H:i:s', (int) ($record['created_at'] ?? time()))),
          'event_type' => (string) ($record['event_type'] ?? 'n/a'),
          'cache_layer' => (string) ($record['cache_layer'] ?? 'n/a'),
          'method' => $method,
          'context' => $context,
          'status' => $status,
          'payload' => $payload,
        ],
      ];
    }

    return [
      'enabled' => TRUE,
      'title' => $this->t('Cache Runtime Records'),
      'description' => $this->t('Inspect cache-related records that are not primarily represented through the route-centric overview.'),
      'headers' => $headers,
      'rows' => $rows,
      'export_url' => Url::fromRoute(
        'native_observability_dashboard.cache_runtime_records_export',
        [],
        [
          'query' => ['range' => $range->getKey()],
        ]
      )->toString(),
      'empty_message' => $this->t('No cache runtime records are available for the selected range.'),
    ];
  }

  /**
   * Builds the cache health overview payload.
   *
   * @param array $snapshot
   *   The cache environment snapshot.
   *
   * @return array
   *   The cache health payload.
   */
  private function buildHealthSection(array $snapshot): array {
    $bins = $snapshot['cache']['bins'] ?? [];
    $modules = $snapshot['cache']['modules'] ?? [];
    $pageCacheSettings = $snapshot['cache']['page_cache'] ?? [];

    $primaryBackend = $this->buildPrimaryBackend($bins);
    $pageCache = $this->buildPageCacheStatus($bins);
    $dynamicCache = $this->buildDynamicCacheStatus($bins);

    $isLimited =
      ($pageCache['status'] ?? '') === 'warning'
      || ($dynamicCache['status'] ?? '') === 'warning';

    return [
      'is_limited' => $isLimited,
      'primary_backend' => $primaryBackend,
      'page_cache' => $pageCache,
      'dynamic_cache' => $dynamicCache,
      'modules' => [
        'label' => $this->t('Cache Modules'),
        'items' => [
          [
            'name' => $this->t('Redis'),
            'enabled' => !empty($modules['redis']),
          ],
          [
            'name' => $this->t('Memcache'),
            'enabled' => !empty($modules['memcache']),
          ],
          [
            'name' => $this->t('APCu'),
            'enabled' => !empty($modules['apcu']),
          ],
        ],
      ],
      'advanced_details' => [
        'title' => $this->t('Advanced cache backend details'),
        'open_by_default' => $isLimited,
        'items' => [
          [
            'label' => $this->t('Default backend class'),
            'value' => (string) ($bins['default']['class'] ?? 'n/a'),
          ],
          [
            'label' => $this->t('Page cache backend class'),
            'value' => (string) ($bins['page']['class'] ?? 'n/a'),
          ],
          [
            'label' => $this->t('Dynamic page cache backend class'),
            'value' => (string) ($bins['dynamic_page_cache']['class'] ?? 'n/a'),
          ],
          [
            'label' => $this->t('Page cache max age'),
            'value' => (string) ($pageCacheSettings['page_cache_maximum_age'] ?? 0),
          ],
          [
            'label' => $this->t('Database driver'),
            'value' => (string) ($snapshot['database']['driver'] ?? 'n/a'),
          ],
          [
            'label' => $this->t('Database version'),
            'value' => (string) ($snapshot['database']['version'] ?? 'n/a'),
          ],
          [
            'label' => $this->t('PHP version'),
            'value' => (string) ($snapshot['php_version'] ?? 'n/a'),
          ],
          [
            'label' => $this->t('Drupal version'),
            'value' => (string) ($snapshot['drupal_version'] ?? 'n/a'),
          ],
        ],
      ],
    ];
  }

  /**
   * Builds a clear environment notice for limited HTTP cache environments.
   *
   * @param array $snapshot
   *   The cache environment snapshot.
   * @param array $health
   *   The built health section payload.
   *
   * @return array
   *   The environment notice payload.
   */
  private function buildEnvironmentNotice(array $snapshot, array $health): array {
    $pageType = (string) ($snapshot['cache']['bins']['page']['type'] ?? 'unknown');
    $dynamicType = (string) ($snapshot['cache']['bins']['dynamic_page_cache']['type'] ?? 'unknown');

    $pageDisabled = $pageType === 'null';
    $dynamicDisabled = $dynamicType === 'null';

    if (!$pageDisabled && !$dynamicDisabled) {
      return [
        'enabled' => FALSE,
        'status' => 'info',
        'title' => '',
        'message' => '',
      ];
    }

    return [
      'enabled' => TRUE,
      'status' => 'warning',
      'title' => $this->t('HTTP cache is limited in this environment'),
      'message' => $this->t('Page Cache and Dynamic Page Cache are currently disabled or backed by a null backend. Cache observability data is still collected, but HTTP cache efficiency should be interpreted as development-oriented rather than production-ready.'),
    ];
  }

  /**
   * Builds the primary backend card payload.
   *
   * @param array $bins
   *   The detected cache bins.
   *
   * @return array
   *   The primary backend payload.
   */
  private function buildPrimaryBackend(array $bins): array {
    $default = $bins['default'] ?? [];
    $type = (string) ($default['type'] ?? 'unknown');

    return [
      'label' => $this->t('Primary Backend'),
      'value' => ucfirst($type),
      'status' => $type === 'null' ? 'warning' : 'ok',
      'hint' => $type === 'null'
        ? $this->t('Null backend detected')
        : $this->t('Default cache bin backend'),
    ];
  }

  /**
   * Builds the page cache status card payload.
   *
   * @param array $bins
   *   The detected cache bins.
   *
   * @return array
   *   The page cache payload.
   */
  private function buildPageCacheStatus(array $bins): array {
    $page = $bins['page'] ?? [];
    $type = (string) ($page['type'] ?? 'unknown');

    if ($type === 'null') {
      return [
        'label' => $this->t('Page Cache'),
        'value' => $this->t('Disabled'),
        'status' => 'warning',
        'hint' => $this->t('Null backend detected'),
      ];
    }

    return [
      'label' => $this->t('Page Cache'),
      'value' => ucfirst($type),
      'status' => 'ok',
      'hint' => $this->t('HTTP page cache backend'),
    ];
  }

  /**
   * Builds the dynamic page cache status card payload.
   *
   * @param array $bins
   *   The detected cache bins.
   *
   * @return array
   *   The dynamic page cache payload.
   */
  private function buildDynamicCacheStatus(array $bins): array {
    $dynamic = $bins['dynamic_page_cache'] ?? [];
    $type = (string) ($dynamic['type'] ?? 'unknown');

    if ($type === 'null') {
      return [
        'label' => $this->t('Dynamic Page Cache'),
        'value' => $this->t('Disabled'),
        'status' => 'warning',
        'hint' => $this->t('Null backend detected'),
      ];
    }

    return [
      'label' => $this->t('Dynamic Page Cache'),
      'value' => ucfirst($type),
      'status' => 'ok',
      'hint' => $this->t('Dynamic page cache backend'),
    ];
  }

  /**
   * Builds the cache signals section.
   *
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The cache signals section payload.
   */
  private function buildSignalsSection(array $metrics): array {
    return [
      'enabled' => TRUE,
      'title' => $this->t('Cache Signals'),
      'description' => $this->t('Availability of response cache headers, cacheability metadata and invalidation signals in the selected time range.'),
      'items' => [
        [
          'label' => $this->t('Response cache headers detected'),
          'value' => !empty($metrics['signals']['response_cache_headers_detected']),
        ],
        [
          'label' => $this->t('Cacheability metadata detected'),
          'value' => !empty($metrics['signals']['cacheability_detected']),
        ],
        [
          'label' => $this->t('Invalidation signals detected'),
          'value' => !empty($metrics['signals']['invalidations_detected']),
        ],
        [
          'label' => $this->t('HTTP cache activity detected'),
          'value' => !empty($metrics['signals']['http_cache_activity_detected']),
        ],
      ],
    ];
  }

  /**
   * Builds the cache behavior section.
   *
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The cache behavior section payload.
   */
  private function buildBehaviorSection(array $metrics): array {
    $pageCache = $metrics['behavior']['page_cache'] ?? [];
    $dynamicPageCache = $metrics['behavior']['dynamic_page_cache'] ?? [];
    $overall = $metrics['behavior']['overall'] ?? [];

    return [
      'enabled' => TRUE,
      'title' => $this->t('Cache Behavior Overview'),
      'description' => $this->t('Derived hit, miss and cacheability behavior extracted from synthetic HTTP request summary spans.'),
      'overall' => [
        'hits' => (int) ($overall['hits'] ?? 0),
        'misses' => (int) ($overall['misses'] ?? 0),
        'uncacheable' => (int) ($overall['uncacheable'] ?? 0),
        'bypass' => (int) ($overall['bypass'] ?? 0),
        'other' => (int) ($overall['other'] ?? 0),
        'total' => (int) ($overall['total'] ?? 0),
        'hit_rate' => (float) ($overall['hit_rate'] ?? 0.0),
        'miss_rate' => (float) ($overall['miss_rate'] ?? 0.0),
        'signals_detected' => !empty($overall['signals_detected']),
      ],
      'layers' => [
        'page_cache' => [
          'label' => $this->t('Page Cache'),
          'hits' => (int) ($pageCache['hits'] ?? 0),
          'misses' => (int) ($pageCache['misses'] ?? 0),
          'uncacheable' => (int) ($pageCache['uncacheable'] ?? 0),
          'bypass' => (int) ($pageCache['bypass'] ?? 0),
          'other' => (int) ($pageCache['other'] ?? 0),
          'total' => (int) ($pageCache['total'] ?? 0),
          'hit_rate' => (float) ($pageCache['hit_rate'] ?? 0.0),
          'miss_rate' => (float) ($pageCache['miss_rate'] ?? 0.0),
          'signals_detected' => !empty($pageCache['signals_detected']),
        ],
        'dynamic_page_cache' => [
          'label' => $this->t('Dynamic Page Cache'),
          'hits' => (int) ($dynamicPageCache['hits'] ?? 0),
          'misses' => (int) ($dynamicPageCache['misses'] ?? 0),
          'uncacheable' => (int) ($dynamicPageCache['uncacheable'] ?? 0),
          'bypass' => (int) ($dynamicPageCache['bypass'] ?? 0),
          'other' => (int) ($dynamicPageCache['other'] ?? 0),
          'total' => (int) ($dynamicPageCache['total'] ?? 0),
          'hit_rate' => (float) ($dynamicPageCache['hit_rate'] ?? 0.0),
          'miss_rate' => (float) ($dynamicPageCache['miss_rate'] ?? 0.0),
          'signals_detected' => !empty($dynamicPageCache['signals_detected']),
        ],
      ],
    ];
  }

  /**
   * Builds the cacheability analysis section.
   *
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The cacheability section payload.
   */
  private function buildCacheabilitySection(array $metrics): array {
    $cacheability = $metrics['cacheability'] ?? [];

    return [
      'enabled' => TRUE,
      'title' => $this->t('Cacheability Analysis'),
      'description' => $this->t('Derived cacheability metrics based on request summary span metadata and cache observer events collected during the selected range.'),
      'summary' => [
        'total_checks' => (int) ($cacheability['total_checks'] ?? 0),
        'cacheable_count' => (int) ($cacheability['cacheable_count'] ?? 0),
        'uncacheable_count' => (int) ($cacheability['uncacheable_count'] ?? 0),
        'unknown_count' => (int) ($cacheability['unknown_count'] ?? 0),
        'cacheable_ratio' => (float) ($cacheability['ratios']['cacheable_ratio'] ?? 0.0),
        'uncacheable_ratio' => (float) ($cacheability['ratios']['uncacheable_ratio'] ?? 0.0),
        'avg_tags' => (float) ($cacheability['averages']['avg_tags'] ?? 0.0),
        'avg_contexts' => (float) ($cacheability['averages']['avg_contexts'] ?? 0.0),
        'avg_max_age' => (float) ($cacheability['averages']['avg_max_age'] ?? 0.0),
      ],
      'distributions' => [
        'max_age' => $cacheability['distributions']['max_age'] ?? [],
        'tags' => $cacheability['distributions']['tags'] ?? [],
        'contexts' => $cacheability['distributions']['contexts'] ?? [],
      ],
    ];
  }

  /**
   * Builds the invalidation analysis section.
   *
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The invalidation section payload.
   */
  private function buildInvalidationsSection(array $metrics): array {
    $invalidations = $metrics['invalidations'] ?? [];

    return [
      'enabled' => TRUE,
      'title' => $this->t('Invalidation Analysis'),
      'description' => $this->t('Derived invalidation pressure indicators across the selected time range.'),
      'summary' => [
        'total_events' => (int) ($invalidations['total_events'] ?? 0),
        'invalidated_tags' => (int) ($invalidations['invalidated_tags'] ?? 0),
        'rate_per_hour' => (float) ($invalidations['rate_per_hour'] ?? 0.0),
      ],
    ];
  }

  /**
   * Builds the insights section.
   *
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The insights section payload.
   */
  private function buildInsightsSection(array $metrics): array {
    return [
      'enabled' => !empty($metrics['insights']),
      'title' => $this->t('Cache Insights'),
      'items' => $metrics['insights'] ?? [],
    ];
  }

  /**
   * Builds the efficiency score section.
   *
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The efficiency score section payload.
   */
  private function buildEfficiencyScoreSection(array $metrics): array {
    $score = $metrics['efficiency_score'] ?? [];

    return [
      'enabled' => TRUE,
      'title' => $this->t('Cache Efficiency Score'),
      'value' => (int) ($score['value'] ?? 0),
      'max' => (int) ($score['max'] ?? 100),
      'status' => (string) ($score['status'] ?? 'warning'),
      'breakdown' => $score['breakdown'] ?? [],
    ];
  }

  /**
   * Builds the activity trend payload for the selected dashboard range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param array $metrics
   *   The derived metrics payload.
   *
   * @return array
   *   The cache activity trend payload.
   */
  private function buildActivityTrend(TimeRange $range, array $metrics): array {
    $bucketSize = $this->resolveBucketSize($range);
    $bucketFormat = $this->resolveBucketLabelFormat($range);
    $buckets = $this->initializeBuckets($range, $bucketSize, $bucketFormat);

    $summary = [
      'total_events' => 0,
      'response_cache_events' => 0,
      'cacheability_events' => 0,
      'invalidation_events' => 0,
    ];

    if ($this->database->schema()->tableExists('native_observability_spans')) {
      $query = $this->database->select('native_observability_spans', 's')
        ->fields('s', ['started_at_ms', 'payload'])
        ->condition('s.name', 'http.request_summary')
        ->condition('s.category', 'http')
        ->condition('s.started_at_ms', $range->getStartTimestamp() * 1000, '>=')
        ->condition('s.started_at_ms', $range->getEndTimestamp() * 1000, '<=')
        ->orderBy('s.id', 'ASC')
        ->range(0, 1500);

      foreach ($query->execute() as $row) {
        $payload = json_decode((string) ($row->payload ?? ''), TRUE);
        if (!is_array($payload)) {
          continue;
        }

        $cache = is_array($payload['cache'] ?? NULL) ? $payload['cache'] : [];
        if ($cache === []) {
          continue;
        }

        $timestamp = (int) floor(((int) ($row->started_at_ms ?? 0)) / 1000);
        $bucketKey = $this->normalizeBucketKey($timestamp, $range->getStartTimestamp(), $bucketSize);

        if (!isset($buckets[$bucketKey])) {
          continue;
        }

        if (!empty($cache['page_cache_status']) || !empty($cache['dynamic_page_cache_status'])) {
          $summary['response_cache_events']++;
          $summary['total_events']++;
          $buckets[$bucketKey]['response_cache_events']++;
        }

        if (!empty($cache['cacheability_status'])) {
          $summary['cacheability_events']++;
          $summary['total_events']++;
          $buckets[$bucketKey]['cacheability_events']++;
        }
      }
    }

    if ($this->database->schema()->tableExists('native_observability_cache_event')) {
      $query = $this->database->select('native_observability_cache_event', 'e')
        ->fields('e', ['created_at'])
        ->condition('e.event_type', 'tag_invalidation')
        ->condition('e.created_at', $range->getStartTimestamp(), '>=')
        ->condition('e.created_at', $range->getEndTimestamp(), '<=')
        ->orderBy('e.id', 'ASC');

      foreach ($query->execute() as $row) {
        $createdAt = (int) ($row->created_at ?? 0);
        $bucketKey = $this->normalizeBucketKey($createdAt, $range->getStartTimestamp(), $bucketSize);

        if (!isset($buckets[$bucketKey])) {
          continue;
        }

        $summary['invalidation_events']++;
        $summary['total_events']++;
        $buckets[$bucketKey]['invalidation_events']++;
      }
    }

    return [
      'enabled' => TRUE,
      'title' => $this->t('Cache Activity Trend'),
      'description' => $this->t('Track response cache signals, cacheability checks and invalidation activity across the selected time range.'),
      'chart_id' => 'cache-activity-trend',
      'series' => [
        'response_cache_events' => $this->buildSeriesFromBuckets($buckets, 'response_cache_events'),
        'cacheability_events' => $this->buildSeriesFromBuckets($buckets, 'cacheability_events'),
        'invalidation_events' => $this->buildSeriesFromBuckets($buckets, 'invalidation_events'),
      ],
      'summary' => $summary,
      'reason' => '',
      'help_message' => '',
    ];
  }

  /**
   * Resolves the most suitable chart bucket size in seconds.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return int
   *   The bucket size in seconds.
   */
  private function resolveBucketSize(TimeRange $range): int {
    return match ($range->getKey()) {
      '15m', '30m' => 300,
      '1h', '6h' => 900,
      '24h' => 3600,
      '7d' => 86400,
      default => $range->getDurationInSeconds() <= 7200 ? 300 : 3600,
    };
  }

  /**
   * Resolves the chart bucket label format.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return string
   *   The label date format.
   */
  private function resolveBucketLabelFormat(TimeRange $range): string {
    return match ($range->getKey()) {
      '15m', '30m', '1h', '6h', '24h' => 'H:i',
      '7d' => 'd M',
      default => 'H:i',
    };
  }

  /**
   * Initializes the time buckets used to draw the chart.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   * @param int $bucket_size
   *   The bucket size in seconds.
   * @param string $bucket_format
   *   The bucket label format.
   *
   * @return array
   *   Initialized buckets keyed by bucket start timestamp.
   */
  private function initializeBuckets(TimeRange $range, int $bucket_size, string $bucket_format): array {
    $buckets = [];
    $cursor = $range->getStartTimestamp();

    while ($cursor <= $range->getEndTimestamp()) {
      $bucket_key = $this->normalizeBucketKey($cursor, $range->getStartTimestamp(), $bucket_size);

      $buckets[$bucket_key] = [
        'label' => date($bucket_format, $bucket_key),
        'response_cache_events' => 0,
        'cacheability_events' => 0,
        'invalidation_events' => 0,
      ];

      $cursor += $bucket_size;
    }

    return $buckets;
  }

  /**
   * Normalizes a raw timestamp to its bucket start timestamp.
   *
   * @param int $timestamp
   *   The original event timestamp.
   * @param int $start
   *   The range start timestamp.
   * @param int $bucket_size
   *   The bucket size in seconds.
   *
   * @return int
   *   The normalized bucket timestamp.
   */
  private function normalizeBucketKey(int $timestamp, int $start, int $bucket_size): int {
    $offset = max(0, $timestamp - $start);
    $bucket_index = (int) floor($offset / $bucket_size);

    return $start + ($bucket_index * $bucket_size);
  }

  /**
   * Builds a chart-ready series from initialized buckets.
   *
   * @param array $buckets
   *   The initialized chart buckets.
   * @param string $metric_key
   *   The metric key to extract.
   *
   * @return array
   *   A chart-ready series payload.
   */
  private function buildSeriesFromBuckets(array $buckets, string $metric_key): array {
    $series = [];

    foreach ($buckets as $bucket) {
      $series[] = [
        'label' => $bucket['label'],
        'value' => (int) ($bucket[$metric_key] ?? 0),
      ];
    }

    return $series;
  }

}
