<?php

namespace AIPT\Api\Chatbot\Jobs;

use AIPT\Api\Chatbot\ChatbotLogger;
use AIPT\Api\Constants\ChatbotConstants;
use AIPT\Core\ErrorHandler;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class ChatbotJobRepository {

    private $wpdb;
    private string $table;

    public function __construct() {
        global $wpdb;

        $this->wpdb  = $wpdb;
        $this->table = $wpdb->prefix . 'aipt_chatbot_jobs';
    }

    /**
     * Create a pending chatbot job row (idempotent on
     * uniq_session_request) and return its job_uuid.
     *
     * Back-compat wrapper around {@see self::createOrFindPendingJob()} for
     * callers that only need the UUID. Prefer the array-returning variant
     * when the caller must distinguish a fresh insert from an idempotent
     * retry (e.g. to skip re-scheduling an already-queued Action Scheduler
     * action).
     *
     * @throws \InvalidArgumentException When session_id is not a v4 UUID,
     *                                   client_request_id is empty or
     *                                   exceeds the 64-char schema cap, or
     *                                   payload is not array-shaped.
     */
    public function createPendingJob( string $session_id, string $client_request_id, array $payload ): string {
        return $this->createOrFindPendingJob( $session_id, $client_request_id, $payload )['job_uuid'];
    }

    /**
     * Create-or-find variant of {@see self::createPendingJob()} that also
     * reports whether the row was freshly inserted by this call (true) or
     * already existed from a prior request that hit the same
     * (session_id, client_request_id) idempotency key (false).
     *
     * @return array{job_uuid:string, was_fresh:bool}
     *
     * @throws \InvalidArgumentException Same conditions as createPendingJob().
     * @throws \RuntimeException        When the INSERT itself fails.
     */
    public function createOrFindPendingJob( string $session_id, string $client_request_id, array $payload ): array {

        if ( 1 !== preg_match( '/^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i', $session_id ) ) {
            throw new \InvalidArgumentException( 'createPendingJob: session_id is not a v4 UUID.' );
        }

        $client_request_id = trim( $client_request_id );
        if ( '' === $client_request_id ) {
            throw new \InvalidArgumentException( 'createPendingJob: client_request_id is empty.' );
        }

        if ( strlen( $client_request_id ) > 64 ) {
            throw new \InvalidArgumentException( 'createPendingJob: client_request_id exceeds 64 chars.' );
        }

        if ( 1 !== preg_match( '/^[A-Za-z0-9_\-]+$/', $client_request_id ) ) {
            throw new \InvalidArgumentException( 'createPendingJob: client_request_id charset (allowed: A-Z a-z 0-9 _ -).' );
        }
        $job_uuid     = \wp_generate_uuid4();
        $status_token = \wp_generate_password( ChatbotConstants::STATUS_TOKEN_LENGTH, false );
        $payload_json = \wp_json_encode( $payload );

        if ( ! is_string( $payload_json ) || '' === $payload_json ) {
            $payload_json = '{}';
        }

        $now = \current_time( 'mysql' );

        $insert_sql = $this->wpdb->prepare(
            "INSERT INTO {$this->table}
             (job_uuid,session_id,client_request_id,status_token,status,payload,attempts,created_at)
             VALUES (%s,%s,%s,%s,%s,%s,%d,%s)
             ON DUPLICATE KEY UPDATE job_uuid = job_uuid",
            $job_uuid,
            $session_id,
            $client_request_id,
            $status_token,
            'pending',
            $payload_json,
            0,
            $now
        );

        $result = $this->wpdb->query( $insert_sql );

        if ( false === $result ) {
            throw new \RuntimeException( 'Failed to create chatbot job.' );
        }

        if ( 1 === (int) $result ) {
            return array(
                'job_uuid'  => $job_uuid,
                'was_fresh' => true,
            );
        }

        $existing_uuid = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT job_uuid FROM {$this->table} WHERE session_id = %s AND client_request_id = %s LIMIT 1",
                $session_id,
                $client_request_id
            )
        );

        if ( ! is_string( $existing_uuid ) || '' === $existing_uuid ) {
            throw new \RuntimeException(
                sprintf(
                    'ChatbotJobRepository::createOrFindPendingJob — INSERT reported ON DUPLICATE KEY but no row matched (session_id=%s, client_request_id=%s). Concurrent delete or transient DB failure suspected.',
                    \esc_html( $session_id ),
                    \esc_html( $client_request_id )
                )
            );
        }

        return array(
            'job_uuid'  => $existing_uuid,
            'was_fresh' => false,
        );
    }

    public function findByUuid( string $job_uuid ): ?array {

        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->table} WHERE job_uuid = %s LIMIT 1",
                $job_uuid
            ),
            ARRAY_A
        );

        return is_array( $row ) ? $row : null;
    }

    public function findByUuidWithToken( string $job_uuid, string $status_token ): ?array {
        $row = $this->findByUuid( $job_uuid );

        if ( null === $row || ! isset( $row['status_token'] ) ) {
            return null;
        }

        return hash_equals( (string) $row['status_token'], $status_token ) ? $row : null;
    }

    public function claimAtomic( string $job_uuid, int $claim_ttl_seconds ): ?string {
        $claim_ttl_seconds = max( 1, $claim_ttl_seconds );
        $claim_token       = \wp_generate_uuid4();
        $claimed_at        = \current_time( 'mysql' );
        $expires_at        = $this->formatTimestamp( \time() + $claim_ttl_seconds );

        $sql = $this->wpdb->prepare(
            "UPDATE {$this->table}
             SET claim_token = %s,
                 claimed_at = %s,
                 expires_at = %s,
                 status = 'processing',
                 attempts = attempts + 1
             WHERE job_uuid = %s AND claim_token IS NULL AND status = 'pending'
             LIMIT 1",
            $claim_token,
            $claimed_at,
            $expires_at,
            $job_uuid
        );

        $updated = $this->queryWithDeadlockRetry( $sql );

        return (int) $updated > 0 ? $claim_token : null;
    }

    public function releaseClaim( string $job_uuid, string $claim_token ): bool {

        $updated = $this->wpdb->update(
            $this->table,
            [
                'status'       => 'pending',
                'claim_token'  => null,
                'claimed_at'   => null,
                'expires_at'   => null,
            ],
            [
                'job_uuid'    => $job_uuid,
                'claim_token' => $claim_token,
                'status'      => 'processing',
            ],
            [ '%s', '%s', '%s', '%s' ],
            [ '%s', '%s', '%s' ]
        );

        return (int) $updated > 0;
    }

    public function markCompleted( string $job_uuid, string $claim_token, array $response_payload ): bool {
        $response_json = \wp_json_encode( $response_payload );

        if ( ! is_string( $response_json ) || '' === $response_json ) {
            $response_json = '{}';
        }

        $updated = $this->wpdb->update(
            $this->table,
            [
                'status'       => 'completed',
                'response'     => $response_json,
                'completed_at' => \current_time( 'mysql' ),
                'claim_token'  => null,
                'claimed_at'   => null,
                'expires_at'   => null,
                'error_code'   => null,
                'error_detail' => null,
            ],
            [
                'job_uuid'    => $job_uuid,
                'claim_token' => $claim_token,
                'status'      => 'processing',
            ],
            [ '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ],
            [ '%s', '%s', '%s' ]
        );

        return (int) $updated > 0;
    }

    public function markFailed( string $job_uuid, string $claim_token, string $error_code, ?string $error_detail = null ): bool {

        $updated = $this->wpdb->update(
            $this->table,
            [
                'status'       => 'failed',
                'error_code'   => \sanitize_key( $error_code ),
                'error_detail' => $this->sanitizeErrorDetail( $error_detail ),
                'claim_token'  => null,
                'claimed_at'   => null,
                'expires_at'   => null,
            ],
            [
                'job_uuid'    => $job_uuid,
                'claim_token' => $claim_token,
                'status'      => 'processing',
            ],
            [ '%s', '%s', '%s', '%s', '%s', '%s' ],
            [ '%s', '%s', '%s' ]
        );

        return (int) $updated > 0;
    }

    public function markRequiresUserRetry( string $job_uuid, string $claim_token, string $error_code, int $backoff_seconds ): bool {
        $next_attempt_at = $this->formatTimestamp( \time() + max( 0, $backoff_seconds ) );

        $updated = $this->wpdb->update(
            $this->table,
            [
                'status'          => 'requires_user_retry',
                'error_code'      => \sanitize_key( $error_code ),
                'next_attempt_at' => $next_attempt_at,
                'claim_token'     => null,
                'claimed_at'      => null,
                'expires_at'      => null,
            ],
            [
                'job_uuid'    => $job_uuid,
                'claim_token' => $claim_token,
                'status'      => 'processing',
            ],
            [ '%s', '%s', '%s', '%s', '%s', '%s' ],
            [ '%s', '%s', '%s' ]
        );

        return (int) $updated > 0;
    }

    /**
     * Reclaim stale claims (worker crashed / timed out past claim TTL).
     *
     * Returns the list of job UUIDs that were reclaimed so the caller can
     * reschedule a fresh Action Scheduler action for each. Without that
     * follow-up scheduling the original single-action was consumed when
     * the worker started and the row would sit in `pending` forever
     * because the queue is otherwise event-driven (no periodic drain).
     *
     * @param int $stale_grace_seconds Grace period after expires_at before reclaim.
     * @return string[] Reclaimed job UUIDs (caller schedules new AS actions).
     */
    /**
     * Transition a job from `pending` → `failed` without a claim token.
     *
     * Used by EnqueueService when AsyncRunner::scheduleJob() fails right
     * after createPendingJob(). The standard markFailed() requires a
     * claim_token to prove ownership, but at this point the row was just
     * created and no worker has — or can — claim it (the AS action that
     * would let a worker claim wasn't actually scheduled). Without this
     * dedicated transition the row would sit in pending forever because
     * reclaimStaleClaims() only handles rows that DID acquire a claim
     * and then went stale.
     */
    public function markScheduleFailed( string $job_uuid, string $error_code, ?string $error_detail = null ): bool {
        $updated = $this->wpdb->update(
            $this->table,
            [
                'status'       => 'failed',
                'error_code'   => \sanitize_key( $error_code ),
                'error_detail' => $this->sanitizeErrorDetail( $error_detail ),
                'completed_at' => \current_time( 'mysql' ),
            ],
            [
                'job_uuid'    => $job_uuid,
                'status'      => 'pending',
                'claim_token' => null,
            ],
            [ '%s', '%s', '%s', '%s' ],
            [ '%s', '%s', '%s' ]
        );

        return (int) $updated > 0;
    }

    public function reclaimStaleClaims( int $stale_grace_seconds ): array {
        $threshold = $this->formatTimestamp( \time() - max( 0, $stale_grace_seconds ) );

        $candidate_uuids = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT job_uuid FROM {$this->table} WHERE claim_token IS NOT NULL AND expires_at < %s",
                $threshold
            )
        );

        if ( empty( $candidate_uuids ) ) {
            return [];
        }

        $updated = $this->wpdb->query(
            $this->wpdb->prepare(
                "UPDATE {$this->table}
                 SET status = 'pending',
                     claim_token = NULL,
                     claimed_at = NULL,
                     expires_at = NULL
                 WHERE claim_token IS NOT NULL AND expires_at < %s",
                $threshold
            )
        );

        if ( false === $updated || (int) $updated <= 0 ) {
            return [];
        }

        $reclaimed = array_values( array_filter( array_map( 'strval', $candidate_uuids ) ) );

        if ( ! empty( $reclaimed ) ) {
            ChatbotLogger::warn( 'stale_claims_reclaimed', [ 'count' => count( $reclaimed ) ] );
        }

        return $reclaimed;
    }

    private function queryWithDeadlockRetry( string $sql ) {
        $attempt = 0;

        do {

            $result = $this->wpdb->query( $sql );

            if ( false !== $result ) {
                return $result;
            }

            $error = strtolower( (string) $this->wpdb->last_error );
            if ( false === strpos( $error, 'deadlock' ) && false === strpos( $error, 'lock wait timeout' ) ) {
                return false;
            }

            usleep( (int) ( pow( 2, $attempt ) * 100000 ) );
            $attempt++;
        } while ( $attempt < 3 );

        return false;
    }

    private function sanitizeErrorDetail( ?string $error_detail ): ?string {
        if ( null === $error_detail || '' === $error_detail ) {
            return null;
        }

		$sanitized = ErrorHandler::sanitize_error_for_logging( $error_detail );

		$sanitized = preg_replace(
			'/\b(?:SELECT|INSERT|UPDATE|DELETE)\b[^;]*?\b(?:FROM|INTO|SET|VALUES)\b[^;]*;?/i',
			'[SQL_REMOVED]',
			$sanitized
		);
		$sanitized = \wp_strip_all_tags( $sanitized );
        $sanitized = preg_replace( '/\s+/', ' ', $sanitized );
        $sanitized = is_string( $sanitized ) ? trim( $sanitized ) : '';

        return '' === $sanitized ? null : substr( $sanitized, 0, 2000 );
    }

    private function formatTimestamp( int $timestamp ): string {
        return \gmdate( 'Y-m-d H:i:s', $timestamp );
    }
}
