<?php

namespace WPSN\Core\Queue;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

final class WPDBQueue implements QueueInterface
{
    private string $table;
    private Logger $logger;

    /**
     * Throttle the stale-job requeue query.
     *
     * On large queues, running the stale-job UPDATE on every claim can be
     * expensive. We do it best-effort, at most once per minute.
     */
    private const STALE_REQUEUE_LOCK_TRANSIENT = 'wpsn_queue_stale_requeue_lock';

    public function __construct(Logger $logger)
    {
        global $wpdb;
        $this->table = $wpdb->prefix . 'wpsn_queue_jobs';
        $this->logger = $logger;
    }

    /**
     * Ensure the queue table exists before running read-only queries.
     *
     * Some screens (e.g., recovery / diagnostics) may call counts()
     * before any enqueue/claim happens. On fresh installs that would
     * trigger a DB error if the table has not been created yet.
     */
    private function ensureSchema(): void
    {
        static $did = false;
        if ($did) {
            return;
        }
        $did = true;

        QueueInstaller::maybeInstall();

        global $wpdb;
        $pattern = $wpdb->esc_like($this->table);
        $prev = $wpdb->suppress_errors(true);
        $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $pattern));
        $wpdb->suppress_errors($prev);

        if (!is_string($exists) || $exists !== $this->table) {
            // The option may be stale or the table got dropped. Reinstall.
            QueueInstaller::install();
        }
    }

    /**
     * Re-queue stale running jobs (best-effort safety), throttled.
     */
    private function maybeRequeueStaleRunning(string $now): void
    {
        static $did = false;
        if ($did) {
            return;
        }
        $did = true;

        // Avoid doing this UPDATE too frequently across requests.
        if (get_transient(self::STALE_REQUEUE_LOCK_TRANSIENT)) {
            return;
        }

        // 60s throttle.
        set_transient(self::STALE_REQUEUE_LOCK_TRANSIENT, '1', 60);

        global $wpdb;

        $staleBefore = gmdate('Y-m-d H:i:s', time() - 600);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$this->table} SET status = %s, locked_at = NULL, locked_by = NULL, updated_at = %s WHERE status = %s AND locked_at IS NOT NULL AND locked_at <> '' AND locked_at < %s",
                'pending',
                $now,
                'running',
                $staleBefore
            )
        );
    }

    /**
     * @param array<string,mixed> $payload
     */
    public function enqueue(string $jobType, array $payload, int $runAtTimestamp = 0): int
    {
        global $wpdb;

        // Ensure queue schema is up to date even if enqueue happens before `init`.
        QueueInstaller::maybeInstall();

        $jobType = sanitize_key($jobType);
        if ($jobType === '') {
            return 0;
        }
        /**
         * Filter the run_at timestamp (UTC) before enqueue.
         *
         * This allows a "direct" execution mode to clamp small delays to "now"
         * so that job chains can complete within a single request without relying on WP-Cron.
         *
         * @param int $runAtTimestamp Unix timestamp (UTC) or 0 for immediate.
         * @param string $jobType
         * @param array<string,mixed> $payload
         */
        $runAtTimestamp = (int) apply_filters('wpsn/queue/run_at', (int) $runAtTimestamp, (string) $jobType, (array) $payload);

        $now = gmdate('Y-m-d H:i:s');
        $runAt = $runAtTimestamp > 0 ? gmdate('Y-m-d H:i:s', $runAtTimestamp) : $now;

        $priority = 10;
        if (isset($payload['__priority'])) {
            $priority = (int) $payload['__priority'];
            unset($payload['__priority']);
        }
        $priority = max(0, min(100, $priority));

        $data = [
            'job_type' => $jobType,
            'priority' => $priority,
            'payload' => wp_json_encode($payload),
            'status' => 'pending',
            'attempts' => 0,
            'run_at' => $runAt,
            'locked_at' => null,
            'locked_by' => null,
            'last_error' => null,
            'created_at' => $now,
            'updated_at' => $now,
        ];

        $formats = [
            '%s', // job_type
            '%d', // priority
            '%s', // payload
            '%s', // status
            '%d', // attempts
            '%s', // run_at
            '%s', // locked_at
            '%s', // locked_by
            '%s', // last_error
            '%s', // created_at
            '%s', // updated_at
        ];

        $wpdb->insert($this->table, $data, $formats);

        $id = (int) $wpdb->insert_id;

        /**
         * Fires after a queue job is enqueued.
         *
         * @param int $jobId
         * @param string $jobType
         * @param array<string,mixed> $payload
         * @param int $runAtTimestamp
         */
        do_action('wpsn/queue/enqueued', (int) $id, (string) $jobType, (array) $payload, (int) $runAtTimestamp);

        return $id;
    }

    /**
     * @return array<string,mixed>|null
     */
    public function claimNext(): ?array
    {
        global $wpdb;

        // Ensure queue schema is up to date.
        QueueInstaller::maybeInstall();

        $now = gmdate('Y-m-d H:i:s');

        $this->maybeRequeueStaleRunning($now);

        // Atomic claim (avoid race between SELECT and UPDATE on busy sites).
        $lockedBy = wp_generate_uuid4();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $updated = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$this->table}
                 SET status = %s, locked_at = %s, locked_by = %s, attempts = attempts + 1, updated_at = %s
                 WHERE status = %s AND run_at <= %s
                 ORDER BY priority ASC, run_at ASC, id ASC
                 LIMIT 1",
                'running',
                $now,
                $lockedBy,
                $now,
                'pending',
                $now
            )
        );

        if ((int) $updated !== 1) {
            return null;
        }

        // Re-fetch the claimed row by lock token.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $job = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->table} WHERE locked_by = %s AND status = %s ORDER BY id DESC LIMIT 1",
                $lockedBy,
                'running'
            ),
            ARRAY_A
        );

        if (!is_array($job)) {
            return null;
        }

        $payload = [];
        if (isset($job['payload'])) {
            $decoded = json_decode((string) $job['payload'], true);
            if (is_array($decoded)) {
                $payload = $decoded;
            }
        }

        $job['payload_decoded'] = $payload;

        return $job;
    }

    /**
     * @param array<int,string> $jobTypes
     * @return array<string,mixed>|null
     */
    public function claimNextByTypes(array $jobTypes): ?array
    {
        $jobTypes = is_array($jobTypes) ? $jobTypes : [];
        $jobTypes = array_values(array_filter(array_map('sanitize_key', $jobTypes), static fn($v) => $v !== ''));
        if (empty($jobTypes)) {
            return $this->claimNext();
        }

        global $wpdb;

        // Ensure queue schema is up to date.
        QueueInstaller::maybeInstall();

        $now = gmdate('Y-m-d H:i:s');

        $this->maybeRequeueStaleRunning($now);

        $lockedBy = wp_generate_uuid4();
        $placeholders = implode(',', array_fill(0, count($jobTypes), '%s'));
        $args = array_merge(['running', $now, $lockedBy, $now, 'pending', $now], $jobTypes);

        // Atomic claim with IN(job_type).
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder
        $sql = $wpdb->prepare(
            "UPDATE {$this->table}
             SET status = %s, locked_at = %s, locked_by = %s, attempts = attempts + 1, updated_at = %s
             WHERE status = %s AND run_at <= %s AND job_type IN ({$placeholders})
             ORDER BY priority ASC, run_at ASC, id ASC
             LIMIT 1",
            $args
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $updated = $wpdb->query($sql);

        if ((int) $updated !== 1) {
            return null;
        }

        // Re-fetch the claimed row by lock token.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $job = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->table} WHERE locked_by = %s AND status = %s ORDER BY id DESC LIMIT 1",
                $lockedBy,
                'running'
            ),
            ARRAY_A
        );

        if (!is_array($job)) {
            return null;
        }

        $payload = [];
        if (isset($job['payload'])) {
            $decoded = json_decode((string) $job['payload'], true);
            if (is_array($decoded)) {
                $payload = $decoded;
            }
        }

        $job['payload_decoded'] = $payload;
        return $job;
    }

    public function markDone(int $jobId): void
    {
        global $wpdb;
        $now = gmdate('Y-m-d H:i:s');

        $wpdb->update(
            $this->table,
            [
                'status' => 'done',
                'locked_at' => null,
                'locked_by' => null,
                'last_error' => null,
                'updated_at' => $now,
            ],
            ['id' => $jobId],
            ['%s', '%s', '%s', '%s', '%s'],
            ['%d']
        );
    }

    public function markFailed(int $jobId, string $errorMessage): void
    {
        global $wpdb;
        $now = gmdate('Y-m-d H:i:s');

        $wpdb->update(
            $this->table,
            [
                'status' => 'failed',
                'locked_at' => null,
                'locked_by' => null,
                'last_error' => sanitize_text_field($errorMessage),
                'updated_at' => $now,
            ],
            ['id' => $jobId],
            ['%s', '%s', '%s', '%s', '%s'],
            ['%d']
        );
    }

    public function retryLater(int $jobId, int $delaySeconds, string $errorMessage = ''): void
    {
        global $wpdb;
        $now = gmdate('Y-m-d H:i:s');
        $runAt = gmdate('Y-m-d H:i:s', time() + max(5, $delaySeconds));

        $wpdb->update(
            $this->table,
            [
                'status' => 'pending',
                'run_at' => $runAt,
                'locked_at' => null,
                'locked_by' => null,
                'last_error' => $errorMessage !== '' ? sanitize_text_field($errorMessage) : null,
                'updated_at' => $now,
            ],
            ['id' => $jobId],
            ['%s', '%s', '%s', '%s', '%s', '%s'],
            ['%d']
        );
    }

    /**
     * @return array<string,int>
     */
    public function counts(): array
    {
        global $wpdb;

        $this->ensureSchema();

        $out = [
            'pending' => 0,
            'running' => 0,
            'done' => 0,
            'failed' => 0,
        ];

        $prev = $wpdb->suppress_errors(true);
        $rows = $wpdb->get_results("SELECT status, COUNT(*) as c FROM {$this->table} GROUP BY status", ARRAY_A);
        $err = $wpdb->last_error;
        $wpdb->suppress_errors($prev);

        if (!is_array($rows) || $err) {
            return $out;
        }

        if (is_array($rows)) {
            foreach ($rows as $r) {
                $status = isset($r['status']) ? (string) $r['status'] : '';
                $c = isset($r['c']) ? (int) $r['c'] : 0;
                if ($status !== '') {
                    $out[$status] = $c;
                }
            }
        }

        return $out;
    }

    public function clearFailed(): int
    {
        global $wpdb;

        $this->ensureSchema();

        $prev = $wpdb->suppress_errors(true);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$this->table} WHERE status = %s",
                'failed'
            )
        );
        $wpdb->suppress_errors($prev);

        return (int) $deleted;
    }

    public function requeueFailed(): int
    {
        global $wpdb;

        $this->ensureSchema();

        $now = gmdate('Y-m-d H:i:s');

        $prev = $wpdb->suppress_errors(true);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $updated = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$this->table}
                 SET status = %s, run_at = %s, locked_at = NULL, locked_by = NULL, last_error = NULL, attempts = 0, updated_at = %s
                 WHERE status = %s",
                'pending',
                $now,
                $now,
                'failed'
            )
        );
        $wpdb->suppress_errors($prev);

        return (int) $updated;
    }

}
