<?php
/**
 * Version Storage Class
 *
 * Handles CRUD operations for workflow versions.
 *
 * @package Sequensy
 * @since 2.0.0
 */

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

declare(strict_types=1);

namespace Sequensy\Storage;

use Sequensy\ValueObjects\WorkflowConfig;
use wpdb;

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

/**
 * Class VersionStorage
 *
 * Provides data access layer for workflow version records.
 * Versions are immutable snapshots of workflow configuration at publish time.
 */
class VersionStorage {

	/**
	 * Maximum number of versions to retain per workflow.
	 *
	 * @var int
	 */
	private const MAX_VERSIONS = 10;

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

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

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

	/**
	 * 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_versions  = $wpdb->prefix . 'sequensy_workflow_versions';
		$this->table_workflows = $wpdb->prefix . 'sequensy_workflows';
	}

	/**
	 * Create a new version from the current draft config.
	 *
	 * Copies the draft_config from the workflow to a new version record.
	 * Automatically increments version number and cleans up old versions.
	 *
	 * @param int    $workflow_id Workflow ID.
	 * @param string $notes       Optional notes describing the version.
	 * @param int    $user_id     Optional user ID who created the version.
	 * @return int|false Version ID on success, false on failure.
	 */
	public function create_version( int $workflow_id, string $notes = '', ?int $user_id = null ): int|false {
		// Get the current draft config from the workflow.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$draft_config = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT draft_config FROM {$this->table_workflows} WHERE id = %d",
				$workflow_id
			)
		);

		if ( ! $draft_config ) {
			return false;
		}

		// Get the next version number.
		$next_version = $this->get_next_version_number( $workflow_id );

		// Insert the new version.
		$result = $this->wpdb->insert(
			$this->table_versions,
			array(
				'workflow_id'    => $workflow_id,
				'version_number' => $next_version,
				'config'         => $draft_config,
				'notes'          => sanitize_textarea_field( $notes ),
				'created_by'     => $user_id,
			),
			array( '%d', '%d', '%s', '%s', '%d' )
		);

		if ( ! $result ) {
			return false;
		}

		$version_id = $this->wpdb->insert_id;

		// Cleanup old versions (keep only the last MAX_VERSIONS).
		$this->cleanup_old_versions( $workflow_id, self::MAX_VERSIONS );

		return $version_id;
	}

	/**
	 * Get the next version number for a workflow.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @return int Next version number.
	 */
	private function get_next_version_number( int $workflow_id ): int {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$max_version = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT MAX(version_number) FROM {$this->table_versions} WHERE workflow_id = %d",
				$workflow_id
			)
		);

		return ( $max_version ? (int) $max_version : 0 ) + 1;
	}

	/**
	 * Get versions for a workflow.
	 *
	 * Returns versions ordered by version_number descending (newest first).
	 *
	 * @param int $workflow_id Workflow ID.
	 * @param int $limit       Maximum number of versions to return.
	 * @param int $offset      Offset for pagination.
	 * @return array Array of version objects.
	 */
	public function get_versions( int $workflow_id, int $limit = 10, int $offset = 0 ): array {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$versions = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT v.*, u.display_name as created_by_name
				FROM {$this->table_versions} v
				LEFT JOIN {$this->wpdb->users} u ON v.created_by = u.ID
				WHERE v.workflow_id = %d
				ORDER BY v.version_number DESC
				LIMIT %d OFFSET %d",
				$workflow_id,
				$limit,
				$offset
			)
		);

		// Cast IDs to int.
		foreach ( $versions as $version ) {
			$version->id             = (int) $version->id;
			$version->workflow_id    = (int) $version->workflow_id;
			$version->version_number = (int) $version->version_number;
			$version->created_by     = $version->created_by ? (int) $version->created_by : null;
		}

		return $versions;
	}

	/**
	 * Get a specific version by ID.
	 *
	 * @param int $version_id Version ID.
	 * @return object|null Version object with parsed config, or null if not found.
	 */
	public function get_version( int $version_id ): ?object {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$version = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT v.*, u.display_name as created_by_name
				FROM {$this->table_versions} v
				LEFT JOIN {$this->wpdb->users} u ON v.created_by = u.ID
				WHERE v.id = %d",
				$version_id
			)
		);

		if ( $version ) {
			$version->id             = (int) $version->id;
			$version->workflow_id    = (int) $version->workflow_id;
			$version->version_number = (int) $version->version_number;
			$version->created_by     = $version->created_by ? (int) $version->created_by : null;

			// Parse config to WorkflowConfig object.
			if ( ! empty( $version->config ) ) {
				$parsed = WorkflowConfig::from_json( $version->config );
				if ( ! is_wp_error( $parsed ) ) {
					$version->parsed_config = $parsed;
				}
			}
		}

		return $version;
	}

	/**
	 * Restore a version by copying its config to the workflow's draft_config.
	 *
	 * Does NOT create a new version - the user must publish to create a new version.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @param int $version_id  Version ID to restore.
	 * @return bool True on success, false on failure.
	 */
	public function restore_version( int $workflow_id, int $version_id ): bool {
		// Verify version belongs to workflow.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$version_config = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT config FROM {$this->table_versions} WHERE id = %d AND workflow_id = %d",
				$version_id,
				$workflow_id
			)
		);

		if ( ! $version_config ) {
			return false;
		}

		// Update the workflow's draft_config with the version's config.
		$result = $this->wpdb->update(
			$this->table_workflows,
			array( 'draft_config' => $version_config ),
			array( 'id' => $workflow_id ),
			array( '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete old versions beyond the retention limit.
	 *
	 * Keeps the newest versions up to $keep_count, deletes the rest.
	 * Never deletes the currently published version.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @param int $keep_count  Number of versions to keep.
	 * @return int Number of deleted versions.
	 */
	public function cleanup_old_versions( int $workflow_id, int $keep_count = 10 ): int {
		// Get the published version ID to protect it from deletion.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$published_version_id = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT published_version_id FROM {$this->table_workflows} WHERE id = %d",
				$workflow_id
			)
		);

		// Get version IDs to delete (all except newest $keep_count and published).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$versions_to_delete = $this->wpdb->get_col(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->table_versions}
				WHERE workflow_id = %d
				ORDER BY version_number DESC
				LIMIT 99999 OFFSET %d",
				$workflow_id,
				$keep_count
			)
		);

		if ( empty( $versions_to_delete ) ) {
			return 0;
		}

		// Remove the published version from deletion list.
		if ( $published_version_id ) {
			$versions_to_delete = array_diff( $versions_to_delete, array( $published_version_id ) );
		}

		if ( empty( $versions_to_delete ) ) {
			return 0;
		}

		// Delete the old versions.
		$ids_placeholder = implode( ',', array_fill( 0, count( $versions_to_delete ), '%d' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$deleted = $this->wpdb->query(
			$this->wpdb->prepare(
				"DELETE FROM {$this->table_versions} WHERE id IN ($ids_placeholder)",
				...array_map( 'intval', $versions_to_delete )
			)
		);

		return $deleted ? (int) $deleted : 0;
	}

	/**
	 * Get version count for a workflow.
	 *
	 * @param int $workflow_id Workflow ID.
	 * @return int Version count.
	 */
	public function get_version_count( int $workflow_id ): int {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table_versions} WHERE workflow_id = %d",
				$workflow_id
			)
		);
	}

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

		return $result ? (int) $result : 0;
	}
}
