<?php

/**
 * Workflow queue for deferred execution.
 *
 * @package AfroIT_Messaging_WorkflowAutomations
 */

namespace AfroIT\Messaging\WorkflowAutomations\Workflows;
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table afromewo_workflow_queue; no WP object-cache API for these rows.

/**
 * Class WorkflowQueue
 *
 * Manages workflow queue jobs (enqueue, dequeue, process).
 */
final class WorkflowQueue {

	private const TABLE = 'afromewo_workflow_queue';

	private const STATUS_PENDING = 'pending';
	private const STATUS_ACCEPTED = 'accepted';
	private const STATUS_RUNNING = 'running';
	private const STATUS_WAITING = 'waiting';
    private const STATUS_PAUSED = 'paused';
	private const STATUS_COMPLETED = 'completed';
	private const STATUS_FAILED = 'failed';

	/**
	 * @var \wpdb
	 */
	private \wpdb $wpdb;

	/**
	 * @var WorkflowEngine|null
	 */
	private ?WorkflowEngine $engine = null;

	/**
	 * Constructor.
	 *
	 * @param \wpdb|null $wpdb WordPress database instance.
	 */
	public function __construct( ?\wpdb $wpdb = null ) {
		$this->wpdb = $wpdb ?? $GLOBALS['wpdb'];
	}

	/**
	 * Set workflow engine for execution (resolves circular dependency).
	 *
	 * @param WorkflowEngine $engine Workflow engine.
	 * @return void
	 */
	public function setWorkflowEngine( WorkflowEngine $engine ): void {
		$this->engine = $engine;
	}

	/**
	 * Get table name.
	 *
	 * @return string
	 */
	public function getTableName(): string {
		return $this->wpdb->prefix . self::TABLE;
	}

	/**
	 * Enqueue a workflow job.
	 *
	 * @param int    $workflow_id Workflow ID.
	 * @param array  $context     Context data.
	 * @param string $run_at      Run at datetime (Y-m-d H:i:s).
     * @param int|null $edit_id   Edit ID to update existing job.
	 * @return int|false Insert ID or false.
	 */
	public function enqueue( int $workflow_id, array $context, string $run_at = '', $edit_id = null ) {
		$wpdb  = $this->wpdb;
		$table  = $this->getTableName();
		$run_at = $run_at ?: current_time( 'mysql' );
		$merged_context = $context;
		if ( $workflow_id > 0 ) {
			$merged_context['workflow_id'] = $workflow_id;
		}
		$context_json = wp_json_encode( $merged_context );

        if ( $edit_id !== null ) {
            $wpdb->update(
                $table,
                array(
                    'context_json' => $context_json,
                    'run_at'       => $run_at,
                ),
                array( 'id' => $edit_id ),
                array( '%s', '%s' ),
                array( '%d' )
            );
            return $edit_id;
        }

		$result = $wpdb->insert(
			$table,
			array(
				'remote_workflow_uuid'  => null,
				'remote_execution_uuid' => null,
				'context_json'          => $context_json,
				'run_at'                => $run_at,
				'status'                => self::STATUS_PENDING,
				'created_at'            => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Get pending jobs due for execution.
	 *
	 * @param int $limit Max jobs to fetch.
	 * @return array<int, WorkflowJob>
	 */
	public function getPendingJobs( int $limit = 10 ): array {
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		$now   = current_time( 'mysql' );

		$results = $wpdb->get_results( 
            $wpdb->prepare(
				"SELECT * FROM %i WHERE (status = %s OR status = %s) AND run_at <= %s ORDER BY run_at ASC LIMIT %d",
                $table,
                self::STATUS_PENDING,
                self::STATUS_PAUSED,
                $now,
                $limit,
			) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name is controlled (plugin-owned).
		);

		return array_map( function( $result ) {
			return WorkflowJob::fromRow( $result );
		}, is_array( $results ) ? $results : array() );
	}

	/**
	 * Find queue rows by status.
	 *
	 * @param string $status Status.
	 * @param int    $limit  Maximum rows.
	 * @return array<int, WorkflowJob>
	 */
	public function findByStatus( string $status, int $limit = 20 ): array {
		$wpdb    = $this->wpdb;
		$table   = $this->getTableName();
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE status = %s ORDER BY created_at DESC LIMIT %d",
				$table,
				$status,
				$limit
			)
		);

		return array_map(
			static function( $result ) {
				return WorkflowJob::fromRow( $result );
			},
			is_array( $results ) ? $results : array()
		);
	}

	/**
	 * Find remote execution rows related to an order ID.
	 *
	 * @param int $order_id Order ID.
	 * @param int $limit    Maximum rows.
	 * @return array<int, WorkflowJob>
	 */
	public function findByOrderId( int $order_id, int $limit = 20 ): array {
		if ( $order_id <= 0 ) {
			return array();
		}

		$wpdb    = $this->wpdb;
		$table   = $this->getTableName();
		$pattern = sprintf( '%%"order_id":%d%%', $order_id );
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE context_json LIKE %s ORDER BY created_at DESC LIMIT %d",
				$table,
				$pattern,
				$limit
			)
		);

		return array_map(
			static function( $result ) {
				return WorkflowJob::fromRow( $result );
			},
			is_array( $results ) ? $results : array()
		);
	}

	/**
	 * Mark job as running.
	 *
	 * @param int $job_id Job ID.
	 * @return bool
	 */
	public function markRunning( int $job_id ): bool {
		return $this->updateStatus( $job_id, self::STATUS_RUNNING );
	}

	/**
	 * Mark job as completed.
	 *
	 * @param int $job_id Job ID.
	 * @return bool
	 */
	public function markCompleted( int $job_id ): bool {
		return $this->updateStatus( $job_id, self::STATUS_COMPLETED );
	}

	/**
	 * Mark job as failed.
	 *
	 * @param int $job_id Job ID.
	 * @return bool
	 */
	public function markFailed( int $job_id ): bool {
		return $this->updateStatus( $job_id, self::STATUS_FAILED );
	}

	/**
	 * Mark job as paused.
	 *
	 * @param int $job_id Job ID.
	 * @return bool
	 */
	public function markPaused( int $job_id ): bool {
		return $this->updateStatus( $job_id, self::STATUS_PAUSED );
	}

	/**
	 * Update job status.
	 *
	 * @param int    $job_id Job ID.
	 * @param string $status Status.
	 * @return bool
	 */
	private function updateStatus( int $job_id, string $status ): bool {
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		return (bool) $wpdb->update(
			$table,
			array( 'status' => $status ),
			array( 'id' => $job_id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Update execution results for a job (persist step progress).
	 *
	 * @param int   $job_id   Job ID.
	 * @param array $execution Execution data (e.g. ['steps' => [...]]).
	 * @return bool
	 */
	public function updateExecutionJson( int $job_id, array $execution ): bool {
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		$json  = wp_json_encode( $execution );
		return (bool) $wpdb->update(
			$table,
			array( 'execution_json' => $json ),
			array( 'id' => $job_id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Find a queue job by ID.
	 *
	 * @param int $job_id Job ID.
	 * @return WorkflowJob|null
	 */
	public function findById( int $job_id ): ?WorkflowJob {
		if ( $job_id <= 0 ) {
			return null;
		}

		$wpdb = $this->wpdb;
		$row  = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE id = %d LIMIT 1',
				$this->getTableName(),
				$job_id
			)
		);

		return $row ? WorkflowJob::fromRow( $row ) : null;
	}

	/**
	 * Enqueue a local execution (plugin-side engine).
	 *
	 * @param Workflow             $workflow Workflow.
	 * @param array<string, mixed> $context  Context.
	 * @return int|false Insert ID or false.
	 */
	public function enqueueLocalExecution( Workflow $workflow, array $context ) {
		$wpdb    = $this->wpdb;
		$table   = $this->getTableName();
		$now     = current_time( 'mysql' );
		$context['workflow_id'] = $workflow->getId();

		$remote_workflow_uuid = trim( $workflow->getRemoteWorkflowUuid() );
		if ( '' === $remote_workflow_uuid ) {
			$remote_workflow_uuid = 'local-' . $workflow->getId();
		}

		$result = $wpdb->insert(
			$table,
			array(
				'remote_workflow_uuid'  => $remote_workflow_uuid,
				'remote_execution_uuid' => 'local-' . wp_generate_uuid4(),
				'context_json'          => wp_json_encode( $context ),
				'execution_json'        => wp_json_encode( array() ),
				'run_at'                => $now,
				'status'                => self::STATUS_PENDING,
				'created_at'            => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Find an existing local execution for an event + workflow pair.
	 *
	 * @param string $event_id    Event id (evt_...).
	 * @param int    $workflow_id Local workflow ID.
	 * @return WorkflowJob|null
	 */
	public function findLocalByEventAndWorkflow( string $event_id, int $workflow_id ): ?WorkflowJob {
		$event_id    = sanitize_text_field( $event_id );
		$workflow_id = absint( $workflow_id );
		if ( '' === $event_id || $workflow_id <= 0 ) {
			return null;
		}

		$wpdb       = $this->wpdb;
		$like_event = '%' . $wpdb->esc_like( '"event_id":"' . $event_id . '"' ) . '%';
		$like_wf    = '%' . $wpdb->esc_like( '"workflow_id":' . $workflow_id ) . '%';
		$row        = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE context_json LIKE %s AND context_json LIKE %s ORDER BY id DESC LIMIT 1',
				$this->getTableName(),
				$like_event,
				$like_wf
			)
		);

		return $row ? WorkflowJob::fromRow( $row ) : null;
	}

	/**
	 * Persist status, run_at, context, and logs for a job.
	 *
	 * @param WorkflowJob          $job    Job (updated in memory).
	 * @param array<string, mixed> $fields Fields to write.
	 * @return bool
	 */
	public function updateJobState( WorkflowJob $job, array $fields ): bool {
		$allowed = array( 'status', 'run_at', 'context_json', 'execution_json', 'remote_execution_uuid', 'remote_workflow_uuid' );
		$data    = array();
		$formats = array();

		foreach ( $allowed as $key ) {
			if ( ! array_key_exists( $key, $fields ) ) {
				continue;
			}

			$value = $fields[ $key ];
			if ( in_array( $key, array( 'context_json', 'execution_json' ), true ) && is_array( $value ) ) {
				$value = wp_json_encode( $value );
			}

			$data[ $key ] = $value;
			$formats[]    = '%s';
			$job->{$key}  = (string) $value;
		}

		if ( empty( $data ) ) {
			return false;
		}

		$result = $this->wpdb->update(
			$this->getTableName(),
			$data,
			array( 'id' => $job->id ),
			$formats,
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Claim due local waiting/pending executions for the plugin engine.
	 *
	 * Only rows whose context contains `_execution` (local engine state) are claimed,
	 * so remote-tracked executions are never processed locally.
	 *
	 * @param int $limit Max jobs.
	 * @return array<int, WorkflowJob>
	 */
	public function claimDueLocalExecutions( int $limit = 20 ): array {
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		$now   = current_time( 'mysql' );
		$like  = '%' . $wpdb->esc_like( '"_execution"' ) . '%';

		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i
				WHERE status IN (%s, %s)
				AND run_at <= %s
				AND context_json LIKE %s
				ORDER BY run_at ASC, id ASC
				LIMIT %d",
				$table,
				self::STATUS_WAITING,
				self::STATUS_PENDING,
				$now,
				$like,
				$limit
			)
		);

		if ( ! is_array( $results ) || empty( $results ) ) {
			return array();
		}

		$claimed = array();
		foreach ( $results as $row ) {
			$job = WorkflowJob::fromRow( $row );
			$updated = $wpdb->update(
				$table,
				array( 'status' => self::STATUS_RUNNING ),
				array(
					'id'     => $job->id,
					'status' => $job->status,
				),
				array( '%s' ),
				array( '%d', '%s' )
			);

			if ( $updated ) {
				$job->status = self::STATUS_RUNNING;
				$claimed[]   = $job;
			}
		}

		return $claimed;
	}

	/**
	 * Whether any local executions are due (waiting/pending with runtime state).
	 *
	 * @return bool
	 */
	public function hasDueLocalExecutions(): bool {
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		$now   = current_time( 'mysql' );
		$like  = '%' . $wpdb->esc_like( '"_execution"' ) . '%';
		$found = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM %i
				WHERE status IN (%s, %s)
				AND run_at <= %s
				AND context_json LIKE %s
				LIMIT 1",
				$table,
				self::STATUS_WAITING,
				self::STATUS_PENDING,
				$now,
				$like
			)
		);

		return null !== $found && '' !== (string) $found;
	}

	/**
	 * Process pending jobs.
	 *
	 * @param int $limit Max jobs to process.
	 * @return int Number of jobs processed.
	 */
	public function processPending( int $limit = 10 ): int {
		return 0;
	}

	public function deleteByWorkflowRemoteUuid( string $remote_workflow_uuid ): bool {
		if ( '' === $remote_workflow_uuid ) {
			return false;
		}
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		$sql = $wpdb->prepare(
			"DELETE FROM %i WHERE remote_workflow_uuid = %s",
			$table,
			$remote_workflow_uuid
		);

		$result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return false !== $result && (int) $result > 0;
	}

    /**
     * Log execution result.
     *
     * @param WorkflowJob $job Job.
     * @param int $step_index Step index.
     * @param string $type Step type.
     * @param string $executed_at Executed at datetime.
     * @param string $status Status.
     * @return bool
     */
    public function logExecutionResult(WorkflowJob $job, int $step_index, string $type,  string $status = 'completed'): bool {
        $execution_results = $job->getExecutionResults();
        $executed_at = current_time('mysql');
        $execution_results[] = compact('step_index', 'type', 'executed_at', 'status');
       
        $job->execution_json = json_encode($execution_results);
        return $this->updateExecutionJson($job->id, $execution_results);
    }
}