<?php

declare(strict_types=1);

namespace Drupal\native_observability_database_observer\Service;

use Drupal\Core\Database\Connection;
use Drupal\Core\Database\Query\SelectInterface;
use Drupal\Core\Datetime\DateFormatterInterface;
use Symfony\Component\DependencyInjection\Attribute\Autowire;

/**
 * Provides storage helpers for database observer raw rows.
 *
 * This storage is used only for persisted slow-query rows and related RAW
 * listing operations. Aggregated request counters must not be stored here.
 */
final class DatabaseObserverStorage {

  private const TABLE = 'native_observability_database_query';

  /**
   * Creates a new storage helper instance.
   */
  public function __construct(
    #[Autowire(service: 'database')]
    private readonly Connection $database,
    #[Autowire(service: 'date.formatter')]
    private readonly DateFormatterInterface $dateFormatter,
  ) {
  }

  /**
   * Checks whether the RAW storage table exists.
   */
  public function tableExists(): bool {
    return $this->database->schema()->tableExists(self::TABLE);
  }

  /**
   * Inserts a single slow-query RAW record.
   */
  public function insert(array $record): void {
    if (!$this->tableExists()) {
      return;
    }

    $this->database->insert(self::TABLE)
      ->fields([
        'request_id' => $record['request_id'] ?? NULL,
        'trace_id' => $record['trace_id'] ?? NULL,
        'route_name' => $record['route_name'] ?? NULL,
        'method' => $record['method'] ?? NULL,
        'path' => $record['path'] ?? NULL,
        'relation_type' => $record['relation_type'] ?? 'request_bound',
        'connection_key' => $record['connection_key'] ?? 'default',
        'connection_target' => $record['connection_target'] ?? 'default',
        'duration_ms' => (float) ($record['duration_ms'] ?? 0),
        'is_slow' => !empty($record['is_slow']) ? 1 : 0,
        'query_hash' => $record['query_hash'] ?? NULL,
        'query_text' => $record['query_text'] ?? NULL,
        'caller' => $record['caller'] ?? NULL,
        'created_at' => (int) ($record['created_at'] ?? time()),
        'payload' => isset($record['payload'])
          ? json_encode($record['payload'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE)
          : NULL,
      ])
      ->execute();
  }

  /**
   * Counts rows for the current filter set.
   */
  public function count(array $filters = []): int {
    if (!$this->tableExists()) {
      return 0;
    }

    $query = $this->database->select(self::TABLE, 'q');
    $query->addExpression('COUNT(q.id)', 'count');
    $this->applyFilters($query, $filters);

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

  /**
   * Returns a filtered page of rows for the RAW listing.
   */
  public function search(array $filters = [], int $limit = 50, int $offset = 0): array {
    if (!$this->tableExists()) {
      return [];
    }

    $query = $this->database->select(self::TABLE, 'q')
      ->fields('q')
      ->range(max(0, $offset), max(1, $limit))
      ->orderBy('created_at', 'DESC');

    $this->applyFilters($query, $filters);

    $rows = [];
    foreach ($query->execute()->fetchAllAssoc('id') as $row) {
      $decodedPayload = [];
      if (!empty($row->payload)) {
        $decoded = json_decode((string) $row->payload, TRUE);
        $decodedPayload = is_array($decoded) ? $decoded : [];
      }

      $rows[] = [
        'id' => (int) $row->id,
        'request_id' => (string) ($row->request_id ?? ''),
        'trace_id' => $row->trace_id !== NULL ? (int) $row->trace_id : NULL,
        'route_name' => (string) ($row->route_name ?? ''),
        'method' => (string) ($row->method ?? ''),
        'path' => (string) ($row->path ?? ''),
        'relation_type' => (string) ($row->relation_type ?? 'request_bound'),
        'connection_key' => (string) ($row->connection_key ?? 'default'),
        'connection_target' => (string) ($row->connection_target ?? 'default'),
        'duration_ms' => (float) $row->duration_ms,
        'is_slow' => (int) $row->is_slow,
        'query_hash' => (string) ($row->query_hash ?? ''),
        'query_text' => !empty($row->query_text) ? (string) $row->query_text : '',
        'caller' => (string) ($row->caller ?? ''),
        'created_at' => (int) $row->created_at,
        'created_at_formatted' => $this->dateFormatter->format((int) $row->created_at, 'short'),
        'payload' => $decodedPayload,
      ];
    }

    return $rows;
  }

  /**
   * Loads a single RAW row.
   */
  public function load(int $id): ?array {
    if (!$this->tableExists()) {
      return NULL;
    }

    $row = $this->database->select(self::TABLE, 'q')
      ->fields('q')
      ->condition('id', $id)
      ->execute()
      ->fetchAssoc();

    if (!$row) {
      return NULL;
    }

    $payload = [];
    if (!empty($row['payload'])) {
      $decoded = json_decode((string) $row['payload'], TRUE);
      $payload = is_array($decoded) ? $decoded : [];
    }

    return [
      'id' => (int) $row['id'],
      'request_id' => (string) ($row['request_id'] ?? ''),
      'trace_id' => $row['trace_id'] !== NULL ? (int) $row['trace_id'] : NULL,
      'route_name' => (string) ($row['route_name'] ?? ''),
      'method' => (string) ($row['method'] ?? ''),
      'path' => (string) ($row['path'] ?? ''),
      'relation_type' => (string) ($row['relation_type'] ?? 'request_bound'),
      'connection_key' => (string) ($row['connection_key'] ?? 'default'),
      'connection_target' => (string) ($row['connection_target'] ?? 'default'),
      'duration_ms' => (float) $row['duration_ms'],
      'is_slow' => (int) $row['is_slow'],
      'query_hash' => (string) ($row['query_hash'] ?? ''),
      'query_text' => !empty($row['query_text']) ? (string) $row['query_text'] : '',
      'caller' => (string) ($row['caller'] ?? ''),
      'created_at' => (int) $row['created_at'],
      'created_at_formatted' => $this->dateFormatter->format((int) $row['created_at'], 'short'),
      'payload' => $payload,
    ];
  }

  /**
   * Deletes rows matched by the provided filter set.
   */
  public function deleteByFilters(array $filters = [], int $limit = 5000): int {
    if (!$this->tableExists()) {
      return 0;
    }

    $query = $this->database->select(self::TABLE, 'q')
      ->fields('q', ['id'])
      ->range(0, max(1, $limit));

    $this->applyFilters($query, $filters);

    $ids = $query->execute()->fetchCol();
    if (!$ids) {
      return 0;
    }

    return (int) $this->database->delete(self::TABLE)
      ->condition('id', $ids, 'IN')
      ->execute();
  }

  /**
   * Deletes all stored rows.
   */
  public function deleteAll(): int {
    if (!$this->tableExists()) {
      return 0;
    }

    return (int) $this->database->delete(self::TABLE)->execute();
  }

  /**
   * Deletes rows older than the provided timestamp.
   */
  public function deleteOlderThan(int $timestamp): int {
    if (!$this->tableExists()) {
      return 0;
    }

    return (int) $this->database->delete(self::TABLE)
      ->condition('created_at', $timestamp, '<')
      ->execute();
  }

  /**
   * Applies reusable filters to a select query.
   */
  private function applyFilters(SelectInterface $query, array $filters): void {
    if (!empty($filters['search'])) {
      $group = $query->orConditionGroup()
        ->condition('path', '%' . $filters['search'] . '%', 'LIKE')
        ->condition('route_name', '%' . $filters['search'] . '%', 'LIKE')
        ->condition('request_id', '%' . $filters['search'] . '%', 'LIKE')
        ->condition('query_hash', '%' . $filters['search'] . '%', 'LIKE');
      $query->condition($group);
    }

    if (!empty($filters['relation_type'])) {
      $query->condition('relation_type', $filters['relation_type']);
    }

    if (isset($filters['is_slow']) && $filters['is_slow'] !== '') {
      $query->condition('is_slow', (int) $filters['is_slow']);
    }

    if (!empty($filters['created_at_min'])) {
      $query->condition('created_at', (int) $filters['created_at_min'], '>=');
    }

    if (!empty($filters['created_at_max'])) {
      $query->condition('created_at', (int) $filters['created_at_max'], '<=');
    }

  }

}
