<?php
/**
 * Workflow Storage Class
 *
 * Handles database schema creation and CRUD operations for workflows.
 *
 * @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 Sequensy\Exceptions\StorageException;
use Sequensy\ValueObjects\WorkflowConfig;
use wpdb;

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

/**
 * Class WorkflowStorage
 *
 * Provides database schema management and data access layer for workflows.
 */
class WorkflowStorage {

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

	/**
	 * Table name for workflows.
	 *
	 * @var string
	 */
	private string $table_workflows;

	/**
	 * Table name for workflow meta.
	 *
	 * @var string
	 */
	private string $table_workflow_meta;

	/**
	 * Table name for activity log.
	 *
	 * @var string
	 */
	private string $table_activity_log;

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

	/**
	 * Table name for workflow versions.
	 *
	 * @var string
	 */
	private string $table_versions;

	/**
	 * 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;

		// Set table names with WordPress prefix.
		$this->table_workflows     = $wpdb->prefix . 'sequensy_workflows';
		$this->table_workflow_meta = $wpdb->prefix . 'sequensy_workflow_meta';
		$this->table_activity_log  = $wpdb->prefix . 'sequensy_activity_log';
		$this->table_executions    = $wpdb->prefix . 'sequensy_workflow_executions';
		$this->table_versions      = $wpdb->prefix . 'sequensy_workflow_versions';
	}

	/**
	 * Create database tables.
	 *
	 * Uses dbDelta for idempotent table creation.
	 * Called on plugin activation.
	 */
	public static function create_tables(): void {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();

		$table_workflows     = $wpdb->prefix . 'sequensy_workflows';
		$table_workflow_meta = $wpdb->prefix . 'sequensy_workflow_meta';
		$table_activity_log  = $wpdb->prefix . 'sequensy_activity_log';
		$table_executions    = $wpdb->prefix . 'sequensy_workflow_executions';
		$table_versions      = $wpdb->prefix . 'sequensy_workflow_versions';

		// SQL for workflows table (draft_config holds unpublished changes)
		$sql_workflows = "CREATE TABLE $table_workflows (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(255) NOT NULL,
			description text,
			trigger_type varchar(100) NOT NULL,
			draft_config longtext NOT NULL,
			published_version_id bigint(20) unsigned DEFAULT NULL,
			last_published_at datetime DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'inactive',
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY trigger_type (trigger_type)
		) $charset_collate;";

		// SQL for workflow versions table
		$sql_versions = "CREATE TABLE $table_versions (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			workflow_id bigint(20) unsigned NOT NULL,
			version_number int NOT NULL,
			config longtext NOT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			created_by bigint(20) unsigned DEFAULT NULL,
			notes text,
			PRIMARY KEY  (id),
			KEY workflow_id (workflow_id),
			UNIQUE KEY workflow_version (workflow_id, version_number)
		) $charset_collate;";

		// SQL for workflow meta table
		$sql_workflow_meta = "CREATE TABLE $table_workflow_meta (
			meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			workflow_id bigint(20) unsigned NOT NULL,
			meta_key varchar(255) NOT NULL,
			meta_value longtext,
			PRIMARY KEY  (meta_id),
			KEY workflow_id (workflow_id),
			KEY meta_key (meta_key)
		) $charset_collate;";

		// SQL for activity log table (includes execution_id for per-action queueing)
		$sql_activity_log = "CREATE TABLE $table_activity_log (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			workflow_id bigint(20) unsigned NOT NULL,
			execution_id bigint(20) unsigned NULL,
			job_id bigint(20) NULL,
			level varchar(20) NOT NULL DEFAULT 'info',
			message text NOT NULL,
			context longtext,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY workflow_id (workflow_id),
			KEY execution_id (execution_id),
			KEY job_id (job_id),
			KEY level (level),
			KEY created_at (created_at)
		) $charset_collate;";

		// SQL for workflow executions table (per-action queueing state)
		$sql_executions = "CREATE TABLE $table_executions (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			workflow_id bigint(20) unsigned NOT NULL,
			version_id bigint(20) unsigned NOT NULL,
			version_number int NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			current_action_id varchar(100) NOT NULL DEFAULT '',
			total_actions int NOT NULL DEFAULT 0,
			context longtext NOT NULL,
			error_message text,
			started_at datetime DEFAULT NULL,
			completed_at datetime DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY workflow_id (workflow_id),
			KEY version_id (version_id),
			KEY status (status),
			KEY created_at (created_at)
		) $charset_collate;";

		// Include upgrade.php for dbDelta
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		// Execute table creation
		dbDelta( $sql_workflows );
		dbDelta( $sql_versions );
		dbDelta( $sql_workflow_meta );
		dbDelta( $sql_activity_log );
		dbDelta( $sql_executions );
	}

	/**
	 * Create a new workflow.
	 *
	 * @param array $data Workflow data (name, description, trigger_type, config, status).
	 * @return int|false Workflow ID on success, false on failure.
	 * @throws StorageException If database insert fails.
	 */
	public function create_workflow( array $data ): int|false {
		$defaults = array(
			'name'         => '',
			'description'  => '',
			'trigger_type' => '',
			'config'       => '{}',
			'status'       => 'inactive',
		);

		$data = wp_parse_args( $data, $defaults );

		// Validate required fields
		if ( empty( $data['name'] ) || empty( $data['trigger_type'] ) ) {
			return false;
		}

		// Ensure config is JSON string
		if ( is_array( $data['config'] ) ) {
			$data['config'] = wp_json_encode( $data['config'] );
		}

		$result = $this->wpdb->insert(
			$this->table_workflows,
			array(
				'name'         => sanitize_text_field( $data['name'] ),
				'description'  => sanitize_textarea_field( $data['description'] ),
				'trigger_type' => sanitize_text_field( $data['trigger_type'] ),
				'draft_config' => $data['config'],
				'status'       => sanitize_text_field( $data['status'] ),
			),
			array( '%s', '%s', '%s', '%s', '%s' )
		);

		// Check for database errors.
		if ( $this->wpdb->last_error ) {
			throw StorageException::queryFailed(
				'INSERT',
				$this->table_workflows,
				$this->wpdb->last_error,
				array( 'workflow_name' => $data['name'] )
			);
		}

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

	/**
	 * Get a workflow by ID.
	 *
	 * @param int $id Workflow ID.
	 * @return object|null Workflow object or null if not found.
	 * @throws StorageException If database query fails.
	 */
	public function get_workflow( int $id ): ?object {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property, not user input.
		$workflow = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table_workflows} WHERE id = %d",
				$id
			)
		);

		// Check for database errors.
		if ( $this->wpdb->last_error ) {
			throw StorageException::queryFailed(
				'SELECT',
				$this->table_workflows,
				$this->wpdb->last_error,
				array( 'workflow_id' => $id )
			);
		}

		if ( $workflow ) {
			// Cast ID to int for strict type compatibility (database returns strings).
			$workflow->id = (int) $workflow->id;

			// Cast version IDs to int if present.
			if ( isset( $workflow->published_version_id ) ) {
				$workflow->published_version_id = $workflow->published_version_id
					? (int) $workflow->published_version_id
					: null;
			}

			// Parse draft_config
			if ( ! empty( $workflow->draft_config ) ) {
				$parsed = WorkflowConfig::from_json( $workflow->draft_config );
				if ( is_wp_error( $parsed ) ) {
					// Log error and return null for invalid config
					wp_trigger_error(
						__METHOD__,
						sprintf( 'Invalid workflow config for ID %d: %s', $id, $parsed->get_error_message() ),
						E_USER_WARNING
					);
					return null;
				}
				$workflow->draft_config = $parsed;
			}
		}

		return $workflow;
	}

	/**
	 * Update a workflow.
	 *
	 * @param int   $id   Workflow ID.
	 * @param array $data Workflow data to update.
	 * @return bool True on success, false on failure.
	 * @throws StorageException If database update fails.
	 */
	public function update_workflow( int $id, array $data ): bool {
		// Prepare update data
		$update_data = array();
		$format      = array();

		if ( isset( $data['name'] ) ) {
			$update_data['name'] = sanitize_text_field( $data['name'] );
			$format[]            = '%s';
		}

		if ( isset( $data['description'] ) ) {
			$update_data['description'] = sanitize_textarea_field( $data['description'] );
			$format[]                   = '%s';
		}

		if ( isset( $data['trigger_type'] ) ) {
			$update_data['trigger_type'] = sanitize_text_field( $data['trigger_type'] );
			$format[]                    = '%s';
		}

		if ( isset( $data['config'] ) ) {
			if ( is_array( $data['config'] ) ) {
				$update_data['draft_config'] = wp_json_encode( $data['config'] );
			} else {
				$update_data['draft_config'] = $data['config'];
			}
			$format[] = '%s';
		}

		if ( isset( $data['status'] ) ) {
			$update_data['status'] = sanitize_text_field( $data['status'] );
			$format[]              = '%s';
		}

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

		$result = $this->wpdb->update(
			$this->table_workflows,
			$update_data,
			array( 'id' => $id ),
			$format,
			array( '%d' )
		);

		// Check for database errors.
		if ( $this->wpdb->last_error ) {
			throw StorageException::queryFailed(
				'UPDATE',
				$this->table_workflows,
				$this->wpdb->last_error,
				array( 'workflow_id' => $id )
			);
		}

		return false !== $result;
	}

	/**
	 * Delete a workflow.
	 *
	 * @param int $id Workflow ID.
	 * @return bool True on success, false on failure.
	 * @throws StorageException If database delete fails.
	 */
	public function delete_workflow( int $id ): bool {
		// Delete workflow meta
		$this->wpdb->delete(
			$this->table_workflow_meta,
			array( 'workflow_id' => $id ),
			array( '%d' )
		);

		// Check for meta deletion errors.
		if ( $this->wpdb->last_error ) {
			throw StorageException::queryFailed(
				'DELETE',
				$this->table_workflow_meta,
				$this->wpdb->last_error,
				array( 'workflow_id' => $id )
			);
		}

		// Delete workflow
		$result = $this->wpdb->delete(
			$this->table_workflows,
			array( 'id' => $id ),
			array( '%d' )
		);

		// Check for workflow deletion errors.
		if ( $this->wpdb->last_error ) {
			throw StorageException::queryFailed(
				'DELETE',
				$this->table_workflows,
				$this->wpdb->last_error,
				array( 'workflow_id' => $id )
			);
		}

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

	/**
	 * Get workflows with filters and pagination.
	 *
	 * @param array $filters Filters (status, trigger_type).
	 * @param int   $limit   Number of results to return.
	 * @param int   $offset  Offset for pagination.
	 * @return array Array of workflow objects.
	 * @throws StorageException If database query fails.
	 */
	public function get_workflows( 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['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_text_field( $filters['status'] );
		}

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

		$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.
		$workflows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table_workflows} WHERE $where_clause " .
				'ORDER BY created_at DESC LIMIT %d OFFSET %d',
				$values
			)
		);

		// Check for database errors.
		if ( $this->wpdb->last_error ) {
			throw StorageException::queryFailed(
				'SELECT',
				$this->table_workflows,
				$this->wpdb->last_error,
				array( 'filters' => $filters )
			);
		}

		// Parse draft_config to WorkflowConfig for each workflow
		$valid_workflows = array();
		foreach ( $workflows as $workflow ) {
			// Cast ID to int for strict type compatibility (database returns strings).
			$workflow->id = (int) $workflow->id;

			// Cast version IDs to int if present.
			if ( isset( $workflow->published_version_id ) ) {
				$workflow->published_version_id = $workflow->published_version_id
					? (int) $workflow->published_version_id
					: null;
			}

			if ( ! empty( $workflow->draft_config ) ) {
				$parsed = WorkflowConfig::from_json( $workflow->draft_config );
				if ( is_wp_error( $parsed ) ) {
					// Skip workflows with invalid config
					wp_trigger_error(
						__METHOD__,
						sprintf( 'Invalid workflow config for ID %d: %s', $workflow->id, $parsed->get_error_message() ),
						E_USER_WARNING
					);
					continue;
				}
				$workflow->draft_config = $parsed;
			}
			$valid_workflows[] = $workflow;
		}

		return $valid_workflows;
	}

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

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

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

		$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_workflows} 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_workflows} WHERE $where_clause",
				$values
			)
		);
	}

	/**
	 * Activate a workflow.
	 *
	 * @param int $id Workflow ID.
	 * @return bool True on success, false on failure.
	 */
	public function activate_workflow( int $id ): bool {
		return $this->update_workflow( $id, array( 'status' => 'active' ) );
	}

	/**
	 * Deactivate a workflow, optionally with a reason.
	 *
	 * Sets the workflow to inactive. When a reason is provided, stores it in meta for UI display.
	 * Used when automatically deactivating workflows (e.g., package deactivated, integration disconnected).
	 *
	 * @param int    $id     Workflow ID.
	 * @param string $reason Optional reason for deactivating the workflow.
	 * @return bool True on success, false on failure.
	 */
	public function deactivate_workflow( int $id, string $reason = '' ): bool {
		$updated = $this->update_workflow( $id, array( 'status' => 'inactive' ) );

		if ( $updated && '' !== $reason ) {
			$this->update_workflow_meta( $id, 'deactivate_reason', sanitize_text_field( $reason ) );
			$this->update_workflow_meta( $id, 'deactivated_at', current_time( 'mysql' ) );
		}

		return $updated;
	}

	/**
	 * Get workflows that use a specific integration.
	 *
	 * Searches workflow configurations for triggers and actions that belong to the given integration.
	 *
	 * @param string $integration Integration slug (e.g., 'mailchimp', 'kit').
	 * @return array Array of workflow objects using this integration.
	 */
	public function get_workflows_using_integration( string $integration ): array {
		// Get all active workflows.
		$workflows = $this->get_workflows( array( 'status' => 'active' ), 1000, 0 );

		$matching_workflows = array();

		foreach ( $workflows as $workflow ) {
			if ( ! $workflow->draft_config instanceof \Sequensy\ValueObjects\WorkflowConfig ) {
				continue;
			}

			// Check if the trigger uses this integration.
			$trigger = $workflow->draft_config->get_trigger();
			if ( $trigger && $trigger->get_integration() === $integration ) {
				$matching_workflows[] = $workflow;
				continue; // Already added, skip action check.
			}

			// Check if any action uses this integration.
			$actions = $workflow->draft_config->get_actions();
			foreach ( $actions as $action ) {
				if ( $action->get_integration() === $integration ) {
					$matching_workflows[] = $workflow;
					break; // Found a match, no need to check other actions.
				}
			}
		}

		return $matching_workflows;
	}

	/**
	 * Clear the deactivation reason meta when a workflow is reactivated.
	 *
	 * @param int $id Workflow ID.
	 * @return bool True on success, false on failure.
	 */
	public function clear_deactivate_reason( int $id ): bool {
		$this->delete_workflow_meta( $id, 'deactivate_reason' );
		$this->delete_workflow_meta( $id, 'deactivated_at' );
		return true;
	}

	/**
	 * Get the deactivation reason for a workflow.
	 *
	 * @param int $id Workflow ID.
	 * @return string|null Deactivation reason or null if not deactivated.
	 */
	public function get_deactivate_reason( int $id ): ?string {
		$reason = $this->get_workflow_meta( $id, 'deactivate_reason' );
		return is_string( $reason ) ? $reason : null;
	}

	/**
	 * Validate workflow configuration.
	 *
	 * @param mixed $config Configuration to validate (array or JSON string).
	 * @return array Array with 'valid' boolean and 'errors' array.
	 */
	public function validate_workflow_config( mixed $config ): array {
		$errors = array();

		// Decode if JSON string
		if ( is_string( $config ) ) {
			$config = json_decode( $config, true );
			if ( json_last_error() !== JSON_ERROR_NONE ) {
				$errors[] = 'Invalid JSON format';
				return array(
					'valid'  => false,
					'errors' => $errors,
				);
			}
		}

		// Check required fields
		if ( ! isset( $config['trigger'] ) ) {
			$errors[] = 'Missing trigger configuration';
		}

		if ( ! isset( $config['actions'] ) || ! is_array( $config['actions'] ) ) {
			$errors[] = 'Missing or invalid actions array';
		}

		// Validate trigger structure
		if ( isset( $config['trigger'] ) ) {
			if ( ! isset( $config['trigger']['type'] ) ) {
				$errors[] = 'Trigger missing type field';
			}
		}

		// Validate actions structure
		if ( isset( $config['actions'] ) && is_array( $config['actions'] ) ) {
			foreach ( $config['actions'] as $index => $action ) {
				if ( ! isset( $action['type'] ) ) {
					$errors[] = "Action at index $index missing type field";
				}
				if ( ! isset( $action['config'] ) ) {
					$errors[] = "Action at index $index missing config field";
				}
			}
		}

		return array(
			'valid'  => empty( $errors ),
			'errors' => $errors,
		);
	}

	/**
	 * Add workflow meta.
	 *
	 * @param int    $workflow_id Workflow ID.
	 * @param string $key         Meta key.
	 * @param mixed  $value       Meta value.
	 * @return int|false Meta ID on success, false on failure.
	 */
	public function add_workflow_meta( int $workflow_id, string $key, mixed $value ): int|false {
		// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- Custom table column names, not WP_Query meta query.
		$result = $this->wpdb->insert(
			$this->table_workflow_meta,
			array(
				'workflow_id' => $workflow_id,
				'meta_key'    => sanitize_text_field( $key ),
				'meta_value'  => maybe_serialize( $value ),
			),
			array( '%d', '%s', '%s' )
		);

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

	/**
	 * Get workflow meta.
	 *
	 * @param int    $workflow_id Workflow ID.
	 * @param string $key         Meta key.
	 * @return mixed Meta value or null if not found.
	 */
	public function get_workflow_meta( int $workflow_id, string $key ): mixed {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property, not user input.
		$meta = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT meta_value FROM {$this->table_workflow_meta} WHERE workflow_id = %d AND meta_key = %s LIMIT 1",
				$workflow_id,
				sanitize_text_field( $key )
			)
		);

		return $meta ? maybe_unserialize( $meta ) : null;
	}

	/**
	 * Update workflow meta.
	 *
	 * @param int    $workflow_id Workflow ID.
	 * @param string $key         Meta key.
	 * @param mixed  $value       Meta value.
	 * @return bool True on success, false on failure.
	 */
	public function update_workflow_meta( int $workflow_id, string $key, mixed $value ): bool {
		// Check if meta exists.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is a class property, not user input.
		$exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT meta_id FROM {$this->table_workflow_meta} WHERE workflow_id = %d AND meta_key = %s LIMIT 1",
				$workflow_id,
				sanitize_text_field( $key )
			)
		);

		if ( $exists ) {
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- Custom table column names.
			$result = $this->wpdb->update(
				$this->table_workflow_meta,
				array( 'meta_value' => maybe_serialize( $value ) ),
				array(
					'workflow_id' => $workflow_id,
					'meta_key'    => sanitize_text_field( $key ),
				),
				array( '%s' ),
				array( '%d', '%s' )
			);
		} else {
			$result = $this->add_workflow_meta( $workflow_id, $key, $value );
		}

		return false !== $result;
	}

	/**
	 * Delete workflow meta.
	 *
	 * @param int    $workflow_id Workflow ID.
	 * @param string $key         Meta key.
	 * @return bool True on success, false on failure.
	 */
	public function delete_workflow_meta( int $workflow_id, string $key ): bool {
		// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Custom table column name.
		$result = $this->wpdb->delete(
			$this->table_workflow_meta,
			array(
				'workflow_id' => $workflow_id,
				'meta_key'    => sanitize_text_field( $key ),
			),
			array( '%d', '%s' )
		);

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

	/**
	 * Get the published configuration for a workflow.
	 *
	 * Returns the config from the currently published version.
	 * Returns null if the workflow has never been published.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @return WorkflowConfig|null Published config or null if not published.
	 */
	public function get_published_config( int $workflow_id ): ?WorkflowConfig {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are class properties.
		$config_json = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT v.config FROM {$this->table_versions} v
				INNER JOIN {$this->table_workflows} w ON w.published_version_id = v.id
				WHERE w.id = %d",
				$workflow_id
			)
		);

		if ( ! $config_json ) {
			return null;
		}

		$parsed = WorkflowConfig::from_json( $config_json );
		if ( is_wp_error( $parsed ) ) {
			wp_trigger_error(
				__METHOD__,
				sprintf(
					'Invalid published config for workflow ID %d: %s',
					$workflow_id,
					$parsed->get_error_message()
				),
				E_USER_WARNING
			);
			return null;
		}

		return $parsed;
	}

	/**
	 * Check if a workflow has unpublished changes.
	 *
	 * Compares the draft_config to the published version's config.
	 * Returns true if they differ, or if no version has been published yet.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @return bool True if unpublished changes exist.
	 */
	public function has_unpublished_changes( int $workflow_id ): bool {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are class properties.
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT w.draft_config, w.published_version_id, v.config as published_config
				FROM {$this->table_workflows} w
				LEFT JOIN {$this->table_versions} v ON w.published_version_id = v.id
				WHERE w.id = %d",
				$workflow_id
			)
		);

		if ( ! $row ) {
			return false;
		}

		// Never published = has unpublished changes.
		if ( ! $row->published_version_id ) {
			return true;
		}

		// Compare draft to published.
		return $row->draft_config !== $row->published_config;
	}

	/**
	 * Update the published version reference for a workflow.
	 *
	 * Called after creating a new version to point the workflow to it.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @param int $version_id  Version ID.
	 * @return bool True on success, false on failure.
	 */
	public function set_published_version( int $workflow_id, int $version_id ): bool {
		$result = $this->wpdb->update(
			$this->table_workflows,
			array(
				'published_version_id' => $version_id,
				'last_published_at'    => current_time( 'mysql' ),
			),
			array( 'id' => $workflow_id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Get the table name for versions.
	 *
	 * @return string Table name.
	 */
	public function get_versions_table(): string {
		return $this->table_versions;
	}
}
