<?php

namespace FleekDash\Core;

/**
 * Deadlock Safe Query Helper
 *
 * Wraps database queries with retry logic to handle MySQL deadlocks gracefully.
 * Implements exponential backoff with jitter for optimal retry behavior.
 *
 * @package FleekDash\Core
 * @since 2.2.0
 */
class DeadlockSafeQuery
{
    /**
     * Maximum number of retry attempts
     */
    const MAX_RETRIES = 3;

    /**
     * Base delay between retries in microseconds (50ms)
     */
    const BASE_DELAY_US = 50000;

    /**
     * Maximum delay between retries in microseconds (500ms)
     */
    const MAX_DELAY_US = 500000;

    /**
     * MySQL deadlock error code
     */
    const DEADLOCK_ERROR_CODE = 1213;

    /**
     * MySQL lock wait timeout error code
     */
    const LOCK_TIMEOUT_ERROR_CODE = 1205;

    /**
     * Execute a query with deadlock retry logic
     *
     * Improved deadlock handling to prevent WordPress from logging retry attempts
     * WordPress logs errors automatically when $wpdb->last_error is set.
     * We suppress error logging during retries and only log if all retries fail.
     *
     * @param callable $query_callback Callback that executes the query (receives $wpdb)
     * @param string $description Optional description for logging
     * @return mixed Query result or false on failure
     */
    public static function execute(callable $query_callback, string $description = 'Query'): mixed
    {
        global $wpdb;

        $attempts = 0;
        $last_error = null;

        while ($attempts < self::MAX_RETRIES) {
            $attempts++;

            try {
                // Clear any previous error state
                $wpdb->last_error = '';
                $wpdb->last_query = '';

                // Execute the query
                $result = $query_callback($wpdb);

                // Check for deadlock error immediately after query
                $is_deadlock = self::isDeadlockError($wpdb->last_error);

                if ($is_deadlock) {
                    $last_error = $wpdb->last_error;

                    if ($attempts < self::MAX_RETRIES) {
                        // Clear error immediately to prevent WordPress from logging it
                        // WordPress logs errors in shutdown hook, so clearing now prevents logging
                        $wpdb->last_error = '';
                        $wpdb->last_query = '';

                        // Log retry attempt (but not to debug.log - use our own logging)
                        self::logRetry($description, $attempts, $last_error);
                        self::sleepWithJitter($attempts);
                        continue;
                    }
                }

                // Query succeeded
                if ($attempts > 1) {
                    self::logSuccess($description, $attempts);
                }

                return $result;

            } catch (\Exception $e) {
                $last_error = $e->getMessage();

                if (self::isDeadlockError($last_error) && $attempts < self::MAX_RETRIES) {
                    // Clear error to prevent WordPress from logging it
                    $wpdb->last_error = '';
                    $wpdb->last_query = '';

                    self::logRetry($description, $attempts, $last_error);
                    self::sleepWithJitter($attempts);
                    continue;
                }

                // Non-deadlock error or max retries reached
                break;
            }
        }

        // Max retries reached or non-recoverable error
        // Only log if all retries failed (don't log during retries)
        // WordPress will log this final error, which is acceptable
        self::logFailure($description, $attempts, $last_error);

        return false;
    }

    /**
     * Execute a DELETE query with deadlock safety
     *
     * @param string $sql Prepared SQL query
     * @param string $description Optional description for logging
     * @return int|false Number of rows affected or false on failure
     */
    public static function delete(string $sql, string $description = 'DELETE'): int|false
    {
        return self::execute(
            function ($wpdb) use ($sql) {
                return $wpdb->query($sql);
            },
            $description
        );
    }

    /**
     * Execute multiple DELETE queries as a batch with deadlock safety
     *
     * Increased delay between queries to reduce lock contention
     * Multiple concurrent requests deleting the same options cause deadlocks.
     * Longer delays help reduce simultaneous lock attempts.
     *
     * @param array $queries Array of prepared SQL queries
     * @param string $description Optional description for logging
     * @return array Results array with 'success' and 'total_deleted' keys
     */
    public static function batchDelete(array $queries, string $description = 'Batch DELETE'): array
    {
        $total_deleted = 0;
        $success = true;

        foreach ($queries as $index => $sql) {
            $result = self::delete($sql, "{$description} #{$index}");

            if ($result === false) {
                $success = false;
                // Continue with other queries even if one fails
            } else {
                $total_deleted += $result;
            }

            // Increased delay between queries to reduce lock contention
            // Multiple concurrent requests can cause deadlocks when deleting same options
            // Longer delay helps reduce simultaneous lock attempts
            // Only delay if not the last query
            if ($index < count($queries) - 1) {
                usleep(50000); // 50ms (increased from 10ms)
            }
        }

        return [
            'success' => $success,
            'total_deleted' => $total_deleted,
            'query_count' => count($queries),
        ];
    }

    /**
     * Check if error message indicates a deadlock
     *
     * @param string $error Error message
     * @return bool True if deadlock error
     */
    private static function isDeadlockError(string $error): bool
    {
        if (empty($error)) {
            return false;
        }

        // Check for common deadlock patterns
        $patterns = [
            'Deadlock found',
            'deadlock',
            'Lock wait timeout',
            'lock timeout',
            'try restarting transaction',
        ];

        foreach ($patterns as $pattern) {
            if (stripos($error, $pattern) !== false) {
                return true;
            }
        }

        return false;
    }

    /**
     * Sleep with exponential backoff and jitter
     *
     * @param int $attempt Current attempt number
     */
    private static function sleepWithJitter(int $attempt): void
    {
        // Exponential backoff: 50ms, 100ms, 200ms, etc.
        $base_delay = self::BASE_DELAY_US * pow(2, $attempt - 1);

        // Add random jitter (±25% of base delay)
        $jitter = rand(-intval($base_delay * 0.25), intval($base_delay * 0.25));

        // Cap at max delay
        $delay = min($base_delay + $jitter, self::MAX_DELAY_US);

        usleep(max(0, $delay));
    }

    /**
     * Log retry attempt
     *
     * @param string $description Query description
     * @param int $attempt Attempt number
     * @param string $error Error message
     */
    private static function logRetry(string $description, int $attempt, string $error): void
    {
        if (\function_exists('fleekdash_log_warn')) {
            \fleekdash_log_warn(
                sprintf(
                    '[DeadlockSafeQuery] Retry %d/%d for "%s" after deadlock: %s',
                    $attempt,
                    self::MAX_RETRIES,
                    $description,
                    substr($error, 0, 100)
                )
            );
        }
    }

    /**
     * Log successful recovery
     *
     * @param string $description Query description
     * @param int $attempts Total attempts needed
     */
    private static function logSuccess(string $description, int $attempts): void
    {
        if (\function_exists('fleekdash_log_info')) {
            \fleekdash_log_info(
                sprintf(
                    '[DeadlockSafeQuery] "%s" succeeded after %d attempts',
                    $description,
                    $attempts
                )
            );
        }
    }

    /**
     * Log failure after all retries exhausted
     *
     * @param string $description Query description
     * @param int $attempts Total attempts made
     * @param string|null $error Last error message
     */
    private static function logFailure(string $description, int $attempts, ?string $error): void
    {
        if (\function_exists('fleekdash_log_error')) {
            \fleekdash_log_error(
                sprintf(
                    '[DeadlockSafeQuery] "%s" failed after %d attempts. Last error: %s',
                    $description,
                    $attempts,
                    $error ?? 'Unknown'
                )
            );
        }
    }
}
