<?php

/**
 * Workflow repository for persistence.
 *
 * @package AfroIT_Messaging_WorkflowAutomations
 */

namespace AfroIT\Messaging\WorkflowAutomations\Workflows;


use AfroIT\Messaging\WorkflowAutomations\Logging\Log;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table afromewo_workflows; no WP object-cache API for these rows.

/**
 * Class WorkflowRepository
 *
 * Handles workflow storage and retrieval.
 */
final class WorkflowRepository {


	/**
	 * @var \wpdb
	 */
	private \wpdb $wpdb;

	/**
	 * Constructor.
	 *
	 * @param \wpdb|null $wpdb WordPress database instance.
	 */
	public function __construct( ?\wpdb $wpdb = null ) {
		$this->wpdb = $wpdb ?? $GLOBALS['wpdb'];
	}

	/**
	 * Get table name.
	 *
	 * @return string
	 */
	public function getTableName(): string {
		return $this->wpdb->prefix . 'afromewo_workflows';
	}

	/**
	 * Find workflows by trigger.
	 *
	 * @param string $trigger Trigger type.
	 * @return Workflow[]
	 */
	public function findByTrigger( string $trigger ): array {
		$wpdb    = $this->wpdb;
		$table   = $this->getTableName();
		//$trigger  = $this->wpdb->esc_like( $trigger );
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE `trigger` = %s AND is_active = 1",
				$table,
				$trigger
			)
		);

		if ( ! is_array( $results ) ) {
			return array();
		}

		return array_map( array( Workflow::class, 'fromRow' ), $results );
	}

	/**
	 * Find all workflows.
	 *
	 * @return Workflow[]
	 */
	public function findAll(): array {
		$wpdb    = $this->wpdb;
		$table   = $this->getTableName();
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i ORDER BY id DESC",
				$table
			)
		);

		if ( ! is_array( $results ) ) {
			return array();
		}

		return array_map( array( Workflow::class, 'fromRow' ), $results );
	}

	/**
	 * Count total workflows.
	 *
	 * @return int
	 */
	public function count(): int {
		$wpdb  = $this->wpdb;
		$table = $this->getTableName();
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i",
				$table
			)
		);
	}

	/**
	 * Find workflows with pagination, ordered by id DESC.
	 *
	 * @param int $limit  Number of items per page.
	 * @param int $offset Offset for pagination.
	 * @return Workflow[]
	 */
	public function findAllPaginated( int $limit, int $offset ): array {
		$wpdb    = $this->wpdb;
		$table   = $this->getTableName();
		$limit   = absint( $limit );
		$offset  = absint( $offset );
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d",
				$table,
				$limit,
				$offset
			)
		);

		if ( ! is_array( $results ) ) {
			return array();
		}

		return array_map( array( Workflow::class, 'fromRow' ), $results );
	}

	/**
	 * Find workflow by ID.
	 *
	 * @param int $id Workflow ID.
	 * @return Workflow|null
	 */
	public function findById( int $id ): ?Workflow {
		$wpdb   = $this->wpdb;
		$table  = $this->getTableName();
		$result = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE id = %d",
				$table,
				$id
			)
		);

		return $result ? Workflow::fromRow( $result ) : null;
	}

	/**
	 * Insert a new workflow.
	 *
	 * @param string $name       Workflow name.
	 * @param string $trigger    Trigger type.
	 * @param array  $steps      Steps array (will be JSON-encoded).
	 * @param array  $conditions Conditions array (will be JSON-encoded). Default empty.
	 * @param bool   $is_active  Whether workflow is active. Default true.
	 * @param string $remote_workflow_uuid Remote workflow UUID.
	 * @return int Inserted workflow ID.
	 */
	public function insert( string $name, string $trigger, array $steps, array $conditions = array(), bool $is_active = true, string $remote_workflow_uuid = '' ): int {
		$wpdb       = $this->wpdb;
		$table      = $this->getTableName();
		$now        = current_time( 'mysql' );
		$steps_json = wp_json_encode( $steps );
		$cond_json  = wp_json_encode( $conditions );

		$wpdb->insert(
			$table,
			array(
				'name'       => $name,
				'trigger'    => $trigger,
				'conditions' => $cond_json,
				'steps'      => $steps_json,
				'is_active'  => $is_active ? 1 : 0,
				'remote_workflow_uuid' => $remote_workflow_uuid,
				'created_at' => $now,
				'updated_at' => $now,
			),
			array( '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s' )
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Update an existing workflow.
	 *
	 * @param int    $id         Workflow ID.
	 * @param string $name       Workflow name.
	 * @param string $trigger    Trigger type.
	 * @param array  $steps      Steps array.
	 * @param array  $conditions Conditions array.
	 * @param bool   $is_active  Whether workflow is active.
	 * @param string $remote_workflow_uuid Remote workflow UUID.
	 * @return bool True on success.
	 */
	public function update( int $id, string $name, string $trigger, array $steps, array $conditions = array(), bool $is_active = true, string $remote_workflow_uuid = '' ): bool {
		$wpdb       = $this->wpdb;
		$table      = $this->getTableName();
		$now        = current_time( 'mysql' );
		$steps_json = wp_json_encode( $steps );
		$cond_json  = wp_json_encode( $conditions );

		$result = $wpdb->update(
			$table,
			array(
				'name'       => $name,
				'trigger'    => $trigger,
				'conditions' => $cond_json,
				'steps'      => $steps_json,
				'is_active'  => $is_active ? 1 : 0,
				'remote_workflow_uuid' => $remote_workflow_uuid,
				'updated_at' => $now,
			),
			array( 'id' => $id ),
			array( '%s', '%s', '%s', '%s', '%d', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Update workflow active status.
	 *
	 * @param int  $id        Workflow ID.
	 * @param bool $is_active Whether workflow is active.
	 * @return bool True on success.
	 */
	public function updateActiveStatus( int $id, bool $is_active ): bool {
		$wpdb  = $this->wpdb;
		$table  = $this->getTableName();
		$result = $wpdb->update(
			$table,
			array(
				'is_active'  => $is_active ? 1 : 0,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete a workflow by ID.
	 *
	 * @param int $id Workflow ID.
	 * @return bool True on success.
	 */
	public function delete( int $id ): bool {
		$wpdb     = $this->wpdb;
		$table    = $this->getTableName();
		$workflow = $this->findById( $id );
		if ( ! $workflow ) {
			return false;
		}
		$remote_workflow_uuid = $workflow->getRemoteWorkflowUuid();

		$result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );

		if ( '' !== $remote_workflow_uuid ) {
			$queue = new WorkflowQueue( $wpdb );
			$queue->deleteByWorkflowRemoteUuid( $remote_workflow_uuid );
		}

		return false !== $result;
	}

    public function getUniqueTriggers(): array {
		$wpdb    = $this->wpdb;
        $table   = $this->getTableName();
        $results = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT DISTINCT `trigger` FROM %i',
				$table
			)
		);
        return array_column( $results, 'trigger' );
    }
}