<?php
|
namespace JVBase\managers\queue;
|
if (!defined('ABSPATH')) {
|
exit;
|
}
|
|
final class Metrics
|
{
|
public function __construct(
|
private \wpdb $db,
|
private string $queueTable,
|
private string $statsTable
|
) {}
|
|
private function sinceDate(int $hours): string
|
{
|
return gmdate('Y-m-d H:i:s', time() - ($hours * HOUR_IN_SECONDS));
|
}
|
|
/* ---------- Live metrics (raw queue table) ---------- */
|
|
public function liveSummary(int $hours = 24): array
|
{
|
$since = $this->sinceDate($hours);
|
|
$sql = "
|
SELECT
|
COUNT(*) AS total,
|
SUM(outcome = 'success') AS success,
|
SUM(outcome = 'partial') AS partial,
|
SUM(outcome IN ('failed', 'failed_permanent')) AS failed,
|
SUM(state IN ('pending','scheduled','processing')) AS active
|
FROM {$this->queueTable}
|
WHERE created_at >= %s
|
";
|
|
return (array) $this->db->get_row(
|
$this->db->prepare($sql, $since),
|
ARRAY_A
|
);
|
}
|
|
|
public function byHour(int $hours = 24): array
|
{
|
$since = $this->sinceDate($hours);
|
|
$sql = "
|
SELECT
|
HOUR(created_at) AS hour,
|
COUNT(*) AS total,
|
SUM(outcome = 'success') AS success,
|
SUM(outcome IN ('failed','failed_permanent')) AS failed
|
FROM {$this->queueTable}
|
WHERE created_at >= %s
|
GROUP BY hour
|
ORDER BY hour ASC
|
";
|
|
$rows = $this->db->get_results(
|
$this->db->prepare($sql, $since),
|
ARRAY_A
|
);
|
|
$result = [];
|
foreach ($rows as $row) {
|
$key = str_pad($row['hour'], 2, '0', STR_PAD_LEFT);
|
$result[$key] = [
|
'total' => (int) $row['total'],
|
'success' => (int) $row['success'],
|
'failed' => (int) $row['failed'],
|
];
|
}
|
|
return $result;
|
}
|
|
|
public function liveByUser(int $hours = 24, int $limit = 100): array
|
{
|
$since = $this->sinceDate($hours);
|
|
$sql = "
|
SELECT
|
user_id,
|
COUNT(*) AS total,
|
SUM(outcome = 'success') AS success,
|
SUM(outcome IN ('failed','failed_permanent')) AS failed,
|
AVG(retries) AS avg_retries
|
FROM {$this->queueTable}
|
WHERE created_at >= %s
|
GROUP BY user_id
|
ORDER BY total DESC
|
LIMIT %d
|
";
|
|
return $this->db->get_results(
|
$this->db->prepare($sql, $since, $limit),
|
ARRAY_A
|
);
|
}
|
|
public function liveQueueHealth(): array
|
{
|
$sql = "
|
SELECT
|
SUM(state = 'pending') AS pending,
|
SUM(state = 'scheduled') AS scheduled,
|
SUM(state = 'processing') AS processing,
|
MIN(scheduled_at) AS oldest_scheduled,
|
SUM(
|
state = 'processing'
|
AND started_at < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
|
) AS stuck
|
FROM {$this->queueTable}
|
";
|
|
return (array) $this->db->get_row($sql, ARRAY_A);
|
}
|
|
|
public function liveByType(int $hours = 24): array
|
{
|
$since = gmdate('Y-m-d H:i:s', time() - $hours * HOUR_IN_SECONDS);
|
|
$sql = "
|
SELECT
|
type,
|
COUNT(*) AS total,
|
SUM(outcome = 'success') AS success,
|
SUM(outcome = 'partial') AS partial,
|
SUM(outcome IN ('failed','failed_permanent')) AS failed,
|
AVG(TIMESTAMPDIFF(SECOND, started_at, completed_at)) AS avg_duration
|
FROM {$this->queueTable}
|
WHERE completed_at IS NOT NULL
|
AND created_at >= %s
|
GROUP BY type
|
";
|
|
return $this->db->get_results(
|
$this->db->prepare($sql, $since),
|
ARRAY_A
|
);
|
}
|
|
|
/* ---------- Historical metrics (stats table) ---------- */
|
|
public function dailySummary(
|
\DateTimeInterface $from,
|
\DateTimeInterface $to
|
): array {
|
$sql = "
|
SELECT
|
date,
|
SUM(total_operations) AS total,
|
SUM(successful_operations) AS success,
|
SUM(partial_operations) AS partial,
|
SUM(failed_operations) AS failed,
|
SUM(failed_permanent_operations) AS failed_permanent,
|
SUM(total_items_processed) AS items,
|
AVG(average_duration) AS avg_duration,
|
MAX(max_duration) AS max_duration
|
FROM {$this->statsTable}
|
WHERE date BETWEEN %s AND %s
|
GROUP BY date
|
ORDER BY date
|
";
|
|
return $this->db->get_results(
|
$this->db->prepare(
|
$sql,
|
$from->format('Y-m-d'),
|
$to->format('Y-m-d')
|
),
|
ARRAY_A
|
);
|
}
|
|
|
public function dailyByType(
|
\DateTimeInterface $from,
|
\DateTimeInterface $to
|
): array {
|
$sql = "
|
SELECT
|
date,
|
type,
|
total_operations,
|
successful_operations,
|
partial_operations,
|
failed_operations,
|
failed_permanent_operations,
|
average_duration,
|
max_duration
|
FROM {$this->statsTable}
|
WHERE date BETWEEN %s AND %s
|
ORDER BY date, type
|
";
|
|
return $this->db->get_results(
|
$this->db->prepare(
|
$sql,
|
$from->format('Y-m-d'),
|
$to->format('Y-m-d')
|
),
|
ARRAY_A
|
);
|
}
|
}
|
