<?php

declare(strict_types=1);

namespace Drupal\native_observability_dashboard\Service;

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

/**
 * Builds derived database observability metrics for dashboard consumption.
 */
final class DatabaseObservabilityMetricsBuilder {

  private const TRACE_TABLE = 'native_observability_trace';
  private const SPANS_TABLE = 'native_observability_spans';
  private const MAX_REQUEST_SUMMARY_SPANS = 2500;

  /**
   * Creates a new metrics builder instance.
   */
  public function __construct(
    private readonly Connection $database,
  ) {
  }

  /**
   * Builds database observability metrics for the selected range.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected dashboard time range.
   *
   * @return array
   *   A structured metrics payload.
   */
  public function build(TimeRange $range): array {
    $metrics = $this->buildEmptyMetrics($range);

    if (
      !$this->database->schema()->tableExists(self::SPANS_TABLE)
      || !$this->database->schema()->tableExists(self::TRACE_TABLE)
    ) {
      return $metrics;
    }

    $query = $this->database->select(self::SPANS_TABLE, 's');
    $query->fields('s', ['request_id', 'started_at_ms', 'payload']);
    $query->leftJoin(self::TRACE_TABLE, 't', 't.request_id = s.request_id');
    $query->addField('t', 'path', 'trace_path');
    $query->addField('t', 'route_name', 'trace_route_name');
    $query->addField('t', 'method', 'trace_method');
    $query->addField('t', 'duration_ms', 'trace_duration_ms');
    $query->condition('s.name', 'database.request_summary');
    $query->condition('s.category', 'database');
    $query->condition('s.started_at_ms', $range->getStartTimestamp() * 1000, '>=');
    $query->condition('s.started_at_ms', $range->getEndTimestamp() * 1000, '<=');
    $query->orderBy('s.started_at_ms', 'ASC');
    $query->range(0, self::MAX_REQUEST_SUMMARY_SPANS);

    foreach ($query->execute() as $row) {
      $payload = $this->decodePayload($row->payload ?? NULL);
      $database = is_array($payload['database'] ?? NULL) ? $payload['database'] : [];
      if ($database === []) {
        continue;
      }

      $requestId = (string) ($row->request_id ?? '');
      $routeName = trim((string) ($row->trace_route_name ?? ''));
      $path = trim((string) ($row->trace_path ?? ''));
      $method = trim((string) ($row->trace_method ?? 'GET'));
      $requestDuration = (float) ($row->trace_duration_ms ?? 0);
      $dbTime = (float) ($database['total_query_time_ms'] ?? 0);
      $queryCount = (int) ($database['query_count'] ?? 0);
      $slowQueryCount = (int) ($database['slow_query_count'] ?? 0);
      $startedAt = (int) round(((int) ($row->started_at_ms ?? 0)) / 1000);
      $bucket = $this->resolveBucketTimestamp($range, $startedAt);
      $routeKey = $routeName !== '' ? $routeName : ($path !== '' ? $path : 'Unknown route');

      $metrics['availability']['has_request_summaries'] = TRUE;
      $metrics['summary']['request_count']++;
      $metrics['summary']['db_time_sum_ms'] += $dbTime;
      $metrics['summary']['query_count_sum'] += $queryCount;
      $metrics['summary']['slow_query_sum'] += $slowQueryCount;

      if ($slowQueryCount > 0) {
        $metrics['summary']['slow_request_count']++;
      }

      $metrics['trend'][$bucket]['db_time_sum'] += $dbTime;
      $metrics['trend'][$bucket]['query_count_sum'] += $queryCount;
      $metrics['trend'][$bucket]['slow_query_events'] += $slowQueryCount;
      $metrics['trend'][$bucket]['request_count']++;

      if (!isset($metrics['routes'][$routeKey])) {
        $metrics['routes'][$routeKey] = [
          'route_name' => $routeName,
          'path' => $path,
          'method' => $method,
          'request_count' => 0,
          'db_time_sum_ms' => 0.0,
          'query_count_sum' => 0,
          'slow_request_count' => 0,
          'slow_query_sum' => 0,
          'request_duration_sum_ms' => 0.0,
          'db_times' => [],
        ];
      }

      $metrics['routes'][$routeKey]['request_count']++;
      $metrics['routes'][$routeKey]['db_time_sum_ms'] += $dbTime;
      $metrics['routes'][$routeKey]['query_count_sum'] += $queryCount;
      $metrics['routes'][$routeKey]['slow_query_sum'] += $slowQueryCount;
      $metrics['routes'][$routeKey]['request_duration_sum_ms'] += $requestDuration;
      $metrics['routes'][$routeKey]['db_times'][] = $dbTime;

      if ($slowQueryCount > 0) {
        $metrics['routes'][$routeKey]['slow_request_count']++;
      }

      $metrics['request_samples'][] = [
        'request_id' => $requestId,
        'route_name' => $routeName,
        'path' => $path,
        'request_duration_ms' => round($requestDuration, 2),
        'db_time_ms' => round($dbTime, 2),
        'query_count' => $queryCount,
        'slow_query_count' => $slowQueryCount,
      ];
    }

    $requestCount = max(1, $metrics['summary']['request_count']);
    $metrics['summary']['average_db_time_ms'] = round($metrics['summary']['db_time_sum_ms'] / $requestCount, 2);
    $metrics['summary']['average_query_count'] = round($metrics['summary']['query_count_sum'] / $requestCount, 2);
    $metrics['summary']['slow_query_incidence'] = round(($metrics['summary']['slow_request_count'] / $requestCount) * 100, 2);

    return $metrics;
  }

  /**
   * Returns the chart bucket size in seconds.
   */
  public function getBucketSize(TimeRange $range): int {
    $seconds = $range->getDurationInSeconds();

    if ($seconds <= 1800) {
      return 300;
    }

    if ($seconds <= 21600) {
      return 900;
    }

    return 3600;
  }

  /**
   * Builds an empty metrics payload.
   */
  private function buildEmptyMetrics(TimeRange $range): array {
    $bucketSize = $this->getBucketSize($range);
    $trend = [];

    for ($cursor = $range->getStartTimestamp(); $cursor <= $range->getEndTimestamp(); $cursor += $bucketSize) {
      $bucket = $this->resolveBucketTimestamp($range, $cursor);
      $trend[$bucket] = [
        'timestamp' => $bucket,
        'label' => date('H:i', $bucket),
        'db_time_sum' => 0.0,
        'query_count_sum' => 0,
        'slow_query_events' => 0,
        'request_count' => 0,
      ];
    }

    return [
      'availability' => [
        'has_request_summaries' => FALSE,
      ],
      'summary' => [
        'request_count' => 0,
        'db_time_sum_ms' => 0.0,
        'query_count_sum' => 0,
        'slow_query_sum' => 0,
        'slow_request_count' => 0,
        'average_db_time_ms' => 0.0,
        'average_query_count' => 0.0,
        'slow_query_incidence' => 0.0,
      ],
      'trend' => $trend,
      'routes' => [],
      'request_samples' => [],
    ];
  }

  /**
   * Resolves the aligned bucket timestamp.
   */
  private function resolveBucketTimestamp(TimeRange $range, int $timestamp): int {
    $bucketSize = $this->getBucketSize($range);
    $offset = max(0, $timestamp - $range->getStartTimestamp());

    return $range->getStartTimestamp() + ((int) floor($offset / $bucketSize) * $bucketSize);
  }

  /**
   * Decodes a stored payload into a normalized array.
   */
  private function decodePayload(mixed $payload): array {
    if (!is_string($payload) || $payload === '') {
      return [];
    }

    $decoded = json_decode($payload, TRUE);

    return is_array($decoded) ? $decoded : [];
  }

}
