<?php
/**
 * Execution Storage Class
 *
 * Handles CRUD operations for workflow executions (per-action queueing state).
 *
 * @package Sequensy
 * @since 2.0.0
 */

// phpcs:disable WordPress.DB.PreparedSQL -- Storage class uses $wpdb with proper prepare().
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders -- Dynamic WHERE clause with matching values array.
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Exception messages.

declare(strict_types=1);

namespace Sequensy\Storage;

use wpdb;

// If this file is called directly, abort.
if ( ! defined( 'WPINC' ) ) {
	die;
}

/**
 * Class ExecutionStorage
 *
 * Provides data access layer for workflow execution records.
 * Each execution represents a single run of a workflow with its state.
 */
class ExecutionStorage {

	/**
	 * WordPress database instance.
	 *
	 * @var wpdb
	 */
	private wpdb $wpdb;

	/**
	 * Table name for workflow executions.
	 *
	 * @var string
	 */
	private string $table_executions;

	/**
	 * Constructor.
	 *
	 * @param wpdb|null $wpdb WordPress database instance. Falls back to global $wpdb.
	 */
	public function __construct( ?wpdb $wpdb = null ) {
		if ( null === $wpdb ) {
			global $wpdb;
		}
		$this->wpdb             = $wpdb;
		$this->table_executions = $wpdb->prefix . 'sequensy_workflow_executions';
	}

	/**
	 * Create a new execution record.
	 *
	 * @param int    $workflow_id     Workflow ID.
	 * @param array  $context         Initial namespaced context (with Trigger data).
	 * @param int    $total_actions   Total number of actions in the workflow.
	 * @param int    $version_id      Version ID being executed.
	 * @param int    $version_number  Version number being executed.
	 * @param string $entry_action_id Entry action ID for graph-based execution.
	 * @return int|false Execution ID on success, false on failure.
	 */
	public function create_execution(
		int $workflow_id,
		array $context,
		int $total_actions,
		int $version_id,
		int $version_number,
		string $entry_action_id = ''
	): int|false {
		$result = $this->wpdb->insert(
			$this->table_executions,
			array(
				'workflow_id'       => absint( $workflow_id ),
				'version_id'        => absint( $version_id ),
				'version_number'    => absint( $version_number ),
				'status'            => 'pending',
				'current_action_id' => sanitize_text_field( $entry_action_id ),
				'total_actions'     => absint( $total_actions ),
				'context'           => wp_json_encode( $context ),
				'started_at'        => current_time( 'mysql' ),
			),
			array( '%d', '%d', '%d', '%s', '%s', '%d', '%s', '%s' )
		);

		return $result ? $this->wpdb->insert_id : false;
	}

	/**
	 * Get an execution by ID.
	 *
	 * @param int $execution_id Execution ID.
	 * @return object|null Execution object with decoded context, or null if not found.
	 */
	public function get_execution( int $execution_id ): ?object {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property, not user input.
		$execution = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table_executions} WHERE id = %d",
				$execution_id
			)
		);

		if ( $execution ) {
			// Cast IDs to int for strict type compatibility (database returns strings).
			$execution->id             = (int) $execution->id;
			$execution->workflow_id    = (int) $execution->workflow_id;
			$execution->version_id     = (int) $execution->version_id;
			$execution->version_number = (int) $execution->version_number;

			if ( ! empty( $execution->context ) ) {
				$execution->context = json_decode( $execution->context, true );
			}
		}

		return $execution;
	}

	/**
	 * Update execution progress after an action completes.
	 *
	 * Updates the context with action output and sets the current action ID
	 * to the next action to be executed.
	 *
	 * @param int    $execution_id  Execution ID.
	 * @param string $next_action_id Next action ID to execute.
	 * @param array  $context        Updated namespaced context.
	 * @return bool True on success, false on failure.
	 */
	public function update_execution_progress( int $execution_id, string $next_action_id, array $context ): bool {
		$result = $this->wpdb->update(
			$this->table_executions,
			array(
				'current_action_id' => sanitize_text_field( $next_action_id ),
				'context'           => wp_json_encode( $context ),
				'status'            => 'running',
			),
			array( 'id' => $execution_id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Mark an execution as running.
	 *
	 * Called when the first action starts executing.
	 *
	 * @param int $execution_id Execution ID.
	 * @return bool True on success, false on failure.
	 */
	public function start_execution( int $execution_id ): bool {
		$result = $this->wpdb->update(
			$this->table_executions,
			array(
				'status'     => 'running',
				'started_at' => current_time( 'mysql' ),
			),
			array( 'id' => $execution_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Mark an execution as completed.
	 *
	 * Called when all actions have finished successfully.
	 *
	 * @param int $execution_id Execution ID.
	 * @return bool True on success, false on failure.
	 */
	public function complete_execution( int $execution_id ): bool {
		$result = $this->wpdb->update(
			$this->table_executions,
			array(
				'status'       => 'completed',
				'completed_at' => current_time( 'mysql' ),
			),
			array( 'id' => $execution_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Mark an execution as failed.
	 *
	 * Called when an action fails after exhausting retries.
	 *
	 * @param int    $execution_id  Execution ID.
	 * @param string $error_message Error message describing the failure.
	 * @return bool True on success, false on failure.
	 */
	public function fail_execution( int $execution_id, string $error_message ): bool {
		$result = $this->wpdb->update(
			$this->table_executions,
			array(
				'status'        => 'failed',
				'error_message' => sanitize_textarea_field( $error_message ),
				'completed_at'  => current_time( 'mysql' ),
			),
			array( 'id' => $execution_id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Get executions with filters and pagination.
	 *
	 * @param array $filters Filters (workflow_id, status).
	 * @param int   $limit   Number of results to return.
	 * @param int   $offset  Offset for pagination.
	 * @return array Array of execution objects.
	 */
	public function get_executions( array $filters = array(), int $limit = 20, int $offset = 0 ): array {
		// Build WHERE clause with hardcoded column names.
		$where  = array( '1=1' );
		$values = array();

		if ( ! empty( $filters['workflow_id'] ) ) {
			$where[]  = 'workflow_id = %d';
			$values[] = absint( $filters['workflow_id'] );
		}

		if ( ! empty( $filters['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_text_field( $filters['status'] );
		}

		$where_clause = implode( ' AND ', $where );
		$values[]     = $limit;
		$values[]     = $offset;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders -- Table name is a class property; where_clause and values built from literal strings above.
		$executions = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table_executions}"
				. " WHERE $where_clause ORDER BY created_at DESC"
				. ' LIMIT %d OFFSET %d',
				$values
			)
		);

		// Cast IDs to int and decode context JSON for each execution.
		foreach ( $executions as $execution ) {
			// Cast IDs to int for strict type compatibility (database returns strings).
			$execution->id             = (int) $execution->id;
			$execution->workflow_id    = (int) $execution->workflow_id;
			$execution->version_id     = (int) $execution->version_id;
			$execution->version_number = (int) $execution->version_number;

			if ( ! empty( $execution->context ) ) {
				$execution->context = json_decode( $execution->context, true );
			}
		}

		return $executions;
	}

	/**
	 * Get total count of executions matching filters.
	 *
	 * @param array $filters Filters (workflow_id, status).
	 * @return int Total count.
	 */
	public function get_executions_count( array $filters = array() ): int {
		// Build WHERE clause with hardcoded column names.
		$where  = array( '1=1' );
		$values = array();

		if ( ! empty( $filters['workflow_id'] ) ) {
			$where[]  = 'workflow_id = %d';
			$values[] = absint( $filters['workflow_id'] );
		}

		if ( ! empty( $filters['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_text_field( $filters['status'] );
		}

		$where_clause = implode( ' AND ', $where );

		// Always use prepare() - pass 1 as dummy value when no filters to satisfy prepare() requirement.
		if ( empty( $values ) ) {
			$values[] = 1;
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property; where_clause built from literal strings above.
			return (int) $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->table_executions} WHERE $where_clause AND %d=%d",
					$values[0],
					$values[0]
				)
			);
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders -- Table name is a class property; where_clause and values built from literal strings above.
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table_executions} WHERE $where_clause",
				$values
			)
		);
	}

	/**
	 * Delete an execution record.
	 *
	 * @param int $execution_id Execution ID.
	 * @return bool True on success, false on failure.
	 */
	public function delete_execution( int $execution_id ): bool {
		$result = $this->wpdb->delete(
			$this->table_executions,
			array( 'id' => $execution_id ),
			array( '%d' )
		);

		return false !== $result && $result > 0;
	}

	/**
	 * Delete all executions for a workflow.
	 *
	 * Called when a workflow is deleted.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @return int Number of deleted rows.
	 */
	public function delete_workflow_executions( int $workflow_id ): int {
		$result = $this->wpdb->delete(
			$this->table_executions,
			array( 'workflow_id' => $workflow_id ),
			array( '%d' )
		);

		return $result ? $result : 0;
	}

	/**
	 * Cleanup old completed/failed executions.
	 *
	 * Deletes execution records older than specified number of days.
	 *
	 * @param int $days Number of days to retain. Default 30.
	 * @return int Number of deleted rows.
	 */
	public function cleanup_old_executions( int $days = 30 ): int {
		$cutoff_date = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// Only delete completed or failed executions.
		$result = $this->wpdb->query(
			$this->wpdb->prepare(
				"DELETE FROM {$this->table_executions}
				WHERE status IN ('completed', 'failed')
				AND created_at < %s",
				$cutoff_date
			)
		);

		return $result ? $result : 0;
	}

	/**
	 * Mark executions stuck in 'running' status as failed.
	 *
	 * @param int $hours Number of hours after which running executions are considered stale.
	 * @return int Number of updated rows.
	 */
	public function cleanup_stale( int $hours = 24 ): int {
		$result = $this->wpdb->query(
			$this->wpdb->prepare(
				"UPDATE {$this->table_executions} SET status = 'failed', error_message = 'Execution timed out'
				WHERE status = 'running' AND started_at < DATE_SUB(NOW(), INTERVAL %d HOUR)",
				$hours
			)
		);

		return false !== $result ? (int) $result : 0;
	}

	/**
	 * Get execution statistics.
	 *
	 * @param int|null $workflow_id Optional workflow ID to filter by.
	 * @return array Statistics with counts by status.
	 */
	public function get_execution_stats( ?int $workflow_id = null ): array {
		// Always use prepare() for consistency.
		if ( $workflow_id ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property.
			$results = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT status, COUNT(*) as count
					FROM {$this->table_executions}
					WHERE workflow_id = %d
					GROUP BY status",
					absint( $workflow_id )
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property.
			$results = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT status, COUNT(*) as count
					FROM {$this->table_executions}
					WHERE %d=%d
					GROUP BY status",
					1,
					1
				)
			);
		}

		$stats = array(
			'pending'   => 0,
			'running'   => 0,
			'completed' => 0,
			'failed'    => 0,
			'total'     => 0,
		);

		foreach ( $results as $row ) {
			$stats[ $row->status ] = (int) $row->count;
			$stats['total']       += (int) $row->count;
		}

		return $stats;
	}
}
