<?php

declare(strict_types=1);

namespace Drupal\native_observability_dashboard\Service;

use Drupal\Core\Extension\ModuleHandlerInterface;
use Drupal\Core\StringTranslation\StringTranslationTrait;
use Drupal\native_observability_dashboard\ValueObject\TimeRange;
use Drupal\native_observability_database_observer\Service\DatabaseObserverStorage;

/**
 * Builds the database observability dashboard section.
 */
final class DatabaseObservabilitySectionBuilder {

  use StringTranslationTrait;

  /**
   * Creates a new database observability section builder instance.
   */
  public function __construct(
    private readonly ModuleHandlerInterface $moduleHandler,
    private readonly DatabaseObservabilityMetricsBuilder $metricsBuilder,
    private readonly TraceMetricsReader $traceMetricsReader,
    private readonly ?DatabaseObserverStorage $databaseObserverStorage = NULL,
  ) {
  }

  /**
   * Builds the full database observability section payload.
   */
  public function build(?TimeRange $range = NULL): array {
    $range ??= TimeRange::createDefault();

    $section = [
      'enabled' => FALSE,
      'id' => 'database_observability',
      'title' => $this->t('Database Observability'),
      'subtitle' => $this->t('Database timing, route-level pressure, and slow query evidence for the selected time range.'),
      'time_range' => $range->getLabel(),
      'reason' => '',
      'help_message' => '',
      'has_data' => FALSE,
      'empty_message' => $this->t('No database observability data is available for the selected time range yet.'),
      'overview_cards' => [],
      'pressure_trend' => [
        'enabled' => FALSE,
        'title' => $this->t('Database Pressure Trend'),
        'chart_id' => 'database-pressure-trend',
        'series' => [],
        'labels' => [],
      ],
      'route_table' => [
        'enabled' => FALSE,
        'title' => $this->t('Most DB-Expensive Routes'),
        'headers' => [],
        'rows' => [],
      ],
      'request_comparison' => [
        'enabled' => FALSE,
        'title' => $this->t('DB vs Request Time'),
        'chart_id' => 'database-route-scatter',
        'points' => [],
      ],
      'hotspots' => [
        'enabled' => FALSE,
        'title' => $this->t('Slow Query Hotspots'),
        'query_hashes' => [],
        'routes' => [],
      ],
      'latest_records' => [
        'enabled' => FALSE,
        'title' => $this->t('Latest Slow Query Records'),
        'threshold_label' => '',
        'headers' => [],
        'rows' => [],
        'empty_message' => $this->t('No slow query records are available for the selected time range.'),
      ],
    ];

    if (!$this->moduleHandler->moduleExists('native_observability_database_observer')) {
      $section['reason'] = $this->t('The Database Observer module is not enabled.');
      $section['help_message'] = $this->t('Enable the Database Observer submodule to populate this tab with database timing, route-level summaries and slow query evidence.');
      return $section;
    }

    $section['enabled'] = TRUE;

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

    $section['overview_cards'] = $this->buildOverviewCards($metrics, count($rawRecords));
    $section['pressure_trend'] = $this->buildPressureTrend($metrics);
    $section['route_table'] = $this->buildRouteTable($metrics);
    $section['request_comparison'] = $this->buildRequestComparison($metrics);
    $section['hotspots'] = $this->buildHotspots($rawRecords);
    $section['latest_records'] = $this->buildLatestRecords($rawRecords);
    $section['has_data'] = !empty($metrics['summary']['request_count']) || $rawRecords !== [];

    return $section;
  }

  /**
   * Builds the overview cards.
   *
   * @param array<string, mixed> $metrics
   *   The aggregated database metrics.
   * @param int $storedSlowQueryRecords
   *   The number of stored slow query records.
   *
   * @return array
   *   The overview card definitions.
   */
  private function buildOverviewCards(array $metrics, int $storedSlowQueryRecords): array {
    $summary = $metrics['summary'] ?? [];

    return [
      [
        'title' => $this->t('Average DB time / request'),
        'value' => number_format((float) ($summary['average_db_time_ms'] ?? 0), 1) . ' ms',
        'description' => $this->t('How much time each request spends in database work.'),
      ],
      [
        'title' => $this->t('Average queries / request'),
        'value' => number_format((float) ($summary['average_query_count'] ?? 0), 1),
        'description' => $this->t('Average database statements executed per request.'),
      ],
      [
        'title' => $this->t('Slow query incidence'),
        'value' => number_format((float) ($summary['slow_query_incidence'] ?? 0), 1) . '%',
        'description' => $this->t('How often requests hit at least one slow query.'),
      ],
      [
        'title' => $this->t('Stored slow query records'),
        'value' => (string) $storedSlowQueryRecords,
        'description' => $this->t('Persisted evidence above the configured threshold.'),
      ],
    ];
  }

  /**
   * Builds the database pressure trend chart payload.
   *
   * @param array<string, mixed> $metrics
   *   The aggregated database metrics.
   *
   * @return array
   *   The chart payload.
   */
  private function buildPressureTrend(array $metrics): array {
    $trend = $metrics['trend'] ?? [];
    if (empty($trend)) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('Database Pressure Trend'),
        'chart_id' => 'database-pressure-trend',
        'series' => [],
        'labels' => [],
      ];
    }

    $labels = [];
    $avgDbTimeSeries = [];
    $avgQueryCountSeries = [];
    $slowQueryEventSeries = [];

    foreach ($trend as $bucket) {
      $requestCount = max(1, (int) ($bucket['request_count'] ?? 0));

      $labels[] = (string) ($bucket['label'] ?? '');
      $avgDbTimeSeries[] = round(((float) ($bucket['db_time_sum'] ?? 0)) / $requestCount, 2);
      $avgQueryCountSeries[] = round(((float) ($bucket['query_count_sum'] ?? 0)) / $requestCount, 2);
      $slowQueryEventSeries[] = (int) ($bucket['slow_query_events'] ?? 0);
    }

    return [
      'enabled' => TRUE,
      'title' => $this->t('Database Pressure Trend'),
      'chart_id' => 'database-pressure-trend',
      'labels' => $labels,
      'series' => [
        [
          'name' => 'Average DB Time',
          'type' => 'line',
          'data' => $avgDbTimeSeries,
        ],
        [
          'name' => 'Average Query Count',
          'type' => 'line',
          'data' => $avgQueryCountSeries,
        ],
        [
          'name' => 'Slow Query Events',
          'type' => 'bar',
          'data' => $slowQueryEventSeries,
        ],
      ],
    ];
  }

  /**
   * Builds the Most DB-expensive routes table.
   *
   * @param array<string, mixed> $metrics
   *   The aggregated database metrics.
   * @param array<int, array<string, mixed>> $rawRecords
   *   Raw database observer records for modal payload generation.
   *
   * @return array
   *   Render-ready table structure.
   */
  private function buildRouteTable(array $metrics, array $rawRecords = []): array {
    $routes = $metrics['routes'] ?? [];

    if (empty($routes)) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('Most DB-Expensive Routes'),
        'headers' => [],
        'rows' => [],
      ];
    }

    $rows = [];

    foreach ($routes as $route) {
      $routeName = (string) ($route['route_name'] ?? 'unknown');
      $path = (string) ($route['path'] ?? '');
      $requestCount = (int) ($route['request_count'] ?? 0);
      $avgDbTime = $requestCount > 0 ? round(((float) ($route['db_time_sum_ms'] ?? 0)) / $requestCount, 2) : 0;
      $avgQueries = $requestCount > 0 ? round(((float) ($route['query_count_sum'] ?? 0)) / $requestCount, 2) : 0;
      $slowRate = $requestCount > 0 ? round((((int) ($route['slow_request_count'] ?? 0)) / $requestCount) * 100, 2) : 0;
      $p95DbTime = $this->calculatePercentile($route['db_times'] ?? [], 95);

      $routeRecords = array_values(array_filter($rawRecords, static function (array $record) use ($routeName): bool {
        return (string) ($record['route_name'] ?? '') === $routeName;
      }));

      $rows[] = [
        'route' => $routeName,
        'path' => $path,
        'requests' => $requestCount,
        'avg_db_time' => $avgDbTime . ' ms',
        'p95_db_time' => $p95DbTime . ' ms',
        'avg_queries' => number_format($avgQueries, 1),
        'slow_rate' => number_format($slowRate, 1) . '%',
        'modal_payload' => $this->buildModalPayload(
          (string) $this->t('Slow queries for route: @route', ['@route' => $routeName]),
          $routeRecords
        ),
      ];
    }

    usort($rows, static function (array $a, array $b): int {
      return (int) $b['requests'] <=> (int) $a['requests'];
    });

    return [
      'enabled' => TRUE,
      'title' => $this->t('Most DB-Expensive Routes'),
      'headers' => [
        'Route',
        'Requests',
        'Avg DB Time',
        'P95 DB Time',
        'Avg Queries',
        'Slow Req Rate',
      ],
      'rows' => $rows,
    ];
  }

  /**
   * Builds the DB vs request time comparison chart payload.
   *
   * @param array<string, mixed> $metrics
   *   The aggregated database metrics.
   *
   * @return array
   *   The chart payload.
   */
  private function buildRequestComparison(array $metrics): array {
    $routes = $metrics['routes'] ?? [];
    if (empty($routes)) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('DB vs Request Time'),
        'chart_id' => 'database-route-scatter',
        'points' => [],
      ];
    }

    $points = [];

    foreach ($routes as $route) {
      $requestCount = (int) ($route['request_count'] ?? 0);
      if ($requestCount <= 0) {
        continue;
      }

      $points[] = [
        'label' => (string) ($route['route_name'] ?? $route['path'] ?? 'Unknown route'),
        'request_duration_ms' => round(((float) ($route['request_duration_sum_ms'] ?? 0)) / $requestCount, 2),
        'db_time_ms' => round(((float) ($route['db_time_sum_ms'] ?? 0)) / $requestCount, 2),
        'request_count' => $requestCount,
        'slow_rate' => round((((int) ($route['slow_request_count'] ?? 0)) / $requestCount) * 100, 2),
      ];
    }

    return [
      'enabled' => !empty($points),
      'title' => $this->t('DB vs Request Time'),
      'chart_id' => 'database-route-scatter',
      'points' => $points,
    ];
  }

  /**
   * Builds the slow query hotspots section.
   *
   * @param array $records
   *   The raw slow query records.
   *
   * @return array
   *   Hotspot structures.
   */
  private function buildHotspots(array $records): array {
    if (empty($records)) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('Slow Query Hotspots'),
        'query_hashes' => [],
        'routes' => [],
      ];
    }

    $hashStats = [];
    $routeStats = [];

    foreach ($records as $record) {
      $hash = (string) ($record['query_hash'] ?? 'unknown');
      $route = (string) ($record['route_name'] ?? 'unknown');
      $duration = (float) ($record['duration_ms'] ?? 0);

      if (!isset($hashStats[$hash])) {
        $hashStats[$hash] = ['count' => 0, 'max' => 0, 'records' => []];
      }
      $hashStats[$hash]['count']++;
      $hashStats[$hash]['max'] = max($hashStats[$hash]['max'], $duration);
      $hashStats[$hash]['records'][] = $record;

      if (!isset($routeStats[$route])) {
        $routeStats[$route] = ['count' => 0, 'max' => 0, 'records' => []];
      }
      $routeStats[$route]['count']++;
      $routeStats[$route]['max'] = max($routeStats[$route]['max'], $duration);
      $routeStats[$route]['records'][] = $record;
    }

    $queryHashes = [];
    foreach ($hashStats as $hash => $data) {
      $queryHashes[] = [
        'hash' => $hash,
        'count' => $data['count'],
        'max' => $data['max'],
        'modal_payload' => $this->buildModalPayload(
          (string) $this->t('Slow queries for hash: @hash', ['@hash' => $hash]),
          $data['records']
        ),
      ];
    }

    usort($queryHashes, static function (array $a, array $b): int {
      return $b['count'] <=> $a['count'];
    });

    $routes = [];
    foreach ($routeStats as $route => $data) {
      $routes[] = [
        'route' => $route,
        'count' => $data['count'],
        'max' => $data['max'],
        'modal_payload' => $this->buildModalPayload(
          (string) $this->t('Slow queries for route: @route', ['@route' => $route]),
          $data['records']
        ),
      ];
    }

    usort($routes, static function (array $a, array $b): int {
      return $b['count'] <=> $a['count'];
    });

    return [
      'enabled' => TRUE,
      'title' => $this->t('Slow Query Hotspots'),
      'query_hashes' => array_slice($queryHashes, 0, 5),
      'routes' => array_slice($routes, 0, 5),
    ];
  }

  /**
   * Builds the latest slow query records table.
   */
  private function buildLatestRecords(array $records): array {
    if (empty($records)) {
      return [
        'enabled' => FALSE,
        'title' => $this->t('Latest Slow Query Records'),
        'headers' => [],
        'rows' => [],
        'empty_message' => $this->t('No slow query records are available for the selected time range.'),
      ];
    }

    $rows = [];
    $thresholdValue = NULL;

    foreach ($records as $record) {
      $duration = (float) ($record['duration_ms'] ?? 0);
      $hash = (string) ($record['query_hash'] ?? '');
      $caller = (string) ($record['caller'] ?? '');
      $connection = (string) (($record['connection_key'] ?? '') ?: ($record['connection_target'] ?? ''));
      $thresholdValue ??= $record['threshold_ms'] ?? NULL;

      $rows[] = [
        'time' => (string) ($record['created_at'] ?? ''),
        'duration' => $duration . ' ms',
        'route' => (string) ($record['route_name'] ?? ''),
        'connection' => $connection,
        'hash' => $hash,
        'caller' => $caller,
        'modal_payload' => $this->buildModalPayload(
          (string) $this->t('Slow query record'),
          [$record]
        ),
      ];
    }

    return [
      'enabled' => TRUE,
      'title' => $this->t('Latest Slow Query Records'),
      'threshold_label' => $thresholdValue !== NULL ? 'Threshold: ' . $thresholdValue . ' ms' : '',
      'headers' => [
        'Time',
        'Duration',
        'Route',
        'Connection',
        'Query Hash',
        'Caller',
        'Actions',
      ],
      'rows' => $rows,
    ];
  }

  /**
   * Loads raw slow query records from database observer storage if available.
   *
   * @param \Drupal\native_observability_dashboard\ValueObject\TimeRange $range
   *   The selected time range.
   *
   * @return array
   *   A list of raw records.
   */
  private function loadRawSlowQueryRecords(TimeRange $range): array {
    // If database observer module is not enabled, return empty dataset.
    if (!$this->moduleHandler->moduleExists('native_observability_database_observer')) {
      return [];
    }

    // If storage service is not available, return empty dataset.
    if ($this->databaseObserverStorage === NULL) {
      return [];
    }

    $filters = [
      'created_at_min' => $range->getStartTimestamp(),
      'created_at_max' => $range->getEndTimestamp(),
      'is_slow' => 1,
    ];

    try {
      if (method_exists($this->databaseObserverStorage, 'loadRecords')) {
        return $this->databaseObserverStorage->loadRecords($filters);
      }

      if (method_exists($this->databaseObserverStorage, 'loadByTimeRange')) {
        return $this->databaseObserverStorage->loadByTimeRange(
          $range->getStartTimestamp(),
          $range->getEndTimestamp()
        );
      }

      return [];
    }
    catch (\Throwable $e) {
      // Never break dashboard rendering.
      return [];
    }
  }

  /**
   * Calculates a percentile from a numeric array.
   *
   * @param array $values
   *   The input numeric values.
   * @param int $percentile
   *   The percentile to calculate.
   *
   * @return float
   *   The calculated percentile.
   */
  private function calculatePercentile(array $values, int $percentile): float {
    $values = array_values(array_filter($values, static fn($value): bool => is_numeric($value)));

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

    sort($values, SORT_NUMERIC);

    $index = (int) ceil(($percentile / 100) * count($values)) - 1;
    $index = max(0, min($index, count($values) - 1));

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

  /**
   * Builds modal payload rows for the database detail dialog.
   *
   * @param string $title
   *   The modal title.
   * @param array<int, array<string, mixed>> $records
   *   Source database observer records.
   *
   * @return array<string, mixed>
   *   Normalized modal payload.
   */
  private function buildModalPayload(string $title, array $records): array {
    $normalizedRows = [];

    foreach ($records as $record) {
      $normalizedRows[] = [
        'time' => (string) ($record['created_at'] ?? ''),
        'duration' => (string) (($record['duration_ms'] ?? 0) . ' ms'),
        'route' => (string) ($record['route_name'] ?? ''),
        'connection' => (string) (($record['connection_key'] ?? '') ?: ($record['connection_target'] ?? '')),
        'hash' => (string) ($record['query_hash'] ?? ''),
        'caller' => (string) ($record['caller'] ?? ''),
      ];
    }

    return [
      'title' => $title,
      'summary' => [
        'primary_label' => (string) $this->t('Records'),
        'primary_value' => (string) count($normalizedRows),
        'secondary_label' => (string) $this->t('Slow queries only'),
        'secondary_value' => (string) $this->t('Yes'),
      ],
      'headers' => [
        (string) $this->t('Time'),
        (string) $this->t('Duration'),
        (string) $this->t('Route'),
        (string) $this->t('Connection'),
        (string) $this->t('Query Hash'),
        (string) $this->t('Caller'),
      ],
      'rows' => $normalizedRows,
      'empty_message' => (string) $this->t('No slow query records are available.'),
    ];
  }

}
