<?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
		);
	}
}