<?php
/**
 * Queue data access layer.
 *
 * @package NaveenCodesImageOptimizer
 */

namespace NaveenCodesImageOptimizer;

defined( 'ABSPATH' ) || exit;

/**
 * Handles the custom queue table.
 */
class Queue {

	/**
	 * Logger instance.
	 *
	 * @var Logger
	 */
	protected $logger;

	/**
	 * Constructor.
	 *
	 * @param Logger $logger Logger.
	 */
	public function __construct( Logger $logger ) {
		$this->logger = $logger;
	}

	/**
	 * Resolve the queue table name.
	 *
	 * @return string
	 */
	public function get_table_name() {
		global $wpdb;

		return $wpdb->prefix . 'naveencodes_queue';
	}

	/**
	 * Create or upgrade the queue table.
	 *
	 * @return void
	 */
	public function create_table() {
		global $wpdb;

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

		$charset_collate = $wpdb->get_charset_collate();
		$table           = $this->get_table_name();
		$sql             = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			attachment_id bigint(20) unsigned NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			retries smallint(5) unsigned NOT NULL DEFAULT 0,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY attachment_id (attachment_id),
			KEY status (status),
			KEY updated_at (updated_at)
		) {$charset_collate};";

		dbDelta( $sql );
	}

	/**
	 * Add or refresh a queue item.
	 *
	 * @param int  $attachment_id Attachment ID.
	 * @param bool $force         Force reset existing job.
	 * @return bool
	 */
	public function enqueue_attachment( $attachment_id, $force = false ) {
		global $wpdb;

		$attachment_id = absint( $attachment_id );

		if ( ! $attachment_id ) {
			return false;
		}

		$table = $this->get_table_name();
		$item  = $this->get_item( $attachment_id );
		$now   = current_time( 'mysql' );

		if ( $item && ! $force ) {
			if ( in_array( $item['status'], array( 'pending', 'processing', 'completed' ), true ) ) {
				return false;
			}

			return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes to the plugin's own queue table.
				$table,
				array(
					'status'     => 'pending',
					'retries'    => 0,
					'updated_at' => $now,
				),
				array( 'attachment_id' => $attachment_id ),
				array( '%s', '%d', '%s' ),
				array( '%d' )
			);
		}

		if ( $item && $force ) {
			return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes to the plugin's own queue table.
				$table,
				array(
					'status'     => 'pending',
					'retries'    => 0,
					'updated_at' => $now,
				),
				array( 'attachment_id' => $attachment_id ),
				array( '%s', '%d', '%s' ),
				array( '%d' )
			);
		}

		return false !== $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes to the plugin's own queue table.
			$table,
			array(
				'attachment_id' => $attachment_id,
				'status'        => 'pending',
				'retries'       => 0,
				'created_at'    => $now,
				'updated_at'    => $now,
			),
			array( '%d', '%s', '%d', '%s', '%s' )
		);
	}

	/**
	 * Get one queue item by attachment ID.
	 *
	 * @param int $attachment_id Attachment ID.
	 * @return array|null
	 */
	public function get_item( $attachment_id ) {
		global $wpdb;

		$item  = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reads from the plugin's own queue table using a sanitized table identifier and prepared value placeholder.
			$wpdb->prepare(
				'SELECT * FROM `' . esc_sql( $this->get_table_name() ) . '` WHERE attachment_id = %d LIMIT 1',
				absint( $attachment_id )
			),
			ARRAY_A
		);

		return is_array( $item ) ? $item : null;
	}

	/**
	 * Claim pending items for processing.
	 *
	 * @param int $limit Batch size.
	 * @return array
	 */
	public function claim_batch( $limit ) {
		global $wpdb;

		$limit = max( 1, absint( $limit ) );

		$this->reset_stale_processing();

		$items = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reads from the plugin's own queue table using a sanitized table identifier and prepared placeholders.
			$wpdb->prepare(
				'SELECT attachment_id FROM `' . esc_sql( $this->get_table_name() ) . '` WHERE status = %s ORDER BY created_at ASC LIMIT %d',
				'pending',
				$limit
			)
		);

		if ( empty( $items ) ) {
			return array();
		}

		foreach ( $items as $attachment_id ) {
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes to the plugin's own queue table.
				$this->get_table_name(),
				array(
					'status'     => 'processing',
					'updated_at' => current_time( 'mysql' ),
				),
				array(
					'attachment_id' => absint( $attachment_id ),
					'status'        => 'pending',
				),
				array( '%s', '%s' ),
				array( '%d', '%s' )
			);
		}

		return array_map( 'absint', $items );
	}

	/**
	 * Mark an attachment as complete.
	 *
	 * @param int $attachment_id Attachment ID.
	 * @return void
	 */
	public function mark_completed( $attachment_id ) {
		$this->update_status( $attachment_id, 'completed' );
	}

	/**
	 * Retry or fail a queue item.
	 *
	 * @param int $attachment_id Attachment ID.
	 * @param int $max_retries   Allowed retries.
	 * @return string
	 */
	public function mark_retry_or_failed( $attachment_id, $max_retries ) {
		global $wpdb;

		$table = $this->get_table_name();
		$item  = $this->get_item( $attachment_id );

		if ( ! $item ) {
			return 'failed';
		}

		$retries = absint( $item['retries'] ) + 1;
		$status  = $retries > absint( $max_retries ) ? 'failed' : 'pending';

		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes to the plugin's own queue table.
			$table,
			array(
				'status'     => $status,
				'retries'    => $retries,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'attachment_id' => absint( $attachment_id ) ),
			array( '%s', '%d', '%s' ),
			array( '%d' )
		);

		return $status;
	}

	/**
	 * Delete queue item when an attachment is removed.
	 *
	 * @param int $attachment_id Attachment ID.
	 * @return void
	 */
	public function remove_attachment( $attachment_id ) {
		global $wpdb;

		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes from the plugin's own queue table.
			$this->get_table_name(),
			array( 'attachment_id' => absint( $attachment_id ) ),
			array( '%d' )
		);
	}

	/**
	 * Get queue counts by status.
	 *
	 * @return array
	 */
	public function get_status_counts() {
		global $wpdb;

		$results  = $wpdb->get_results( 'SELECT status, COUNT(*) AS total FROM `' . esc_sql( $this->get_table_name() ) . '` GROUP BY status', ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Aggregation against the plugin's own queue table with a sanitized table identifier.
		$defaults = array(
			'pending'    => 0,
			'processing' => 0,
			'completed'  => 0,
			'failed'     => 0,
		);

		foreach ( (array) $results as $row ) {
			$status = sanitize_key( $row['status'] ?? '' );

			if ( isset( $defaults[ $status ] ) ) {
				$defaults[ $status ] = absint( $row['total'] ?? 0 );
			}
		}

		$defaults['total'] = array_sum( $defaults );

		return $defaults;
	}

	/**
	 * Get queue items for one status.
	 *
	 * @param string $status Queue status.
	 * @param int    $limit  Max rows to return.
	 * @return array
	 */
	public function get_items_by_status( $status, $limit = 15 ) {
		global $wpdb;

		$status = sanitize_key( $status );
		$limit  = max( 1, absint( $limit ) );

		if ( ! in_array( $status, array( 'pending', 'processing', 'completed', 'failed' ), true ) ) {
			return array();
		}

		$items = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Reads from the plugin's own queue table using a sanitized table identifier and prepared placeholders.
			$wpdb->prepare(
				'SELECT * FROM `' . esc_sql( $this->get_table_name() ) . '` WHERE status = %s ORDER BY updated_at DESC LIMIT %d',
				$status,
				$limit
			),
			ARRAY_A
		);

		return is_array( $items ) ? $items : array();
	}

	/**
	 * Reset stale items that were left in processing.
	 *
	 * @param int $minutes Consider jobs stale after this many minutes.
	 * @return void
	 */
	public function reset_stale_processing( $minutes = 20 ) {
		global $wpdb;

		$minutes    = max( 1, absint( $minutes ) );
		$stale_time = gmdate( 'Y-m-d H:i:s', time() - ( $minutes * MINUTE_IN_SECONDS ) );

		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Update against the plugin's own queue table with a sanitized table identifier and prepared placeholders.
			$wpdb->prepare(
				'UPDATE `' . esc_sql( $this->get_table_name() ) . '` SET status = %s, updated_at = %s WHERE status = %s AND updated_at < %s',
				'pending',
				current_time( 'mysql' ),
				'processing',
				$stale_time
			)
		);
	}

	/**
	 * Update an item's status.
	 *
	 * @param int    $attachment_id Attachment ID.
	 * @param string $status        Queue status.
	 * @return void
	 */
	protected function update_status( $attachment_id, $status ) {
		global $wpdb;

		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes to the plugin's own queue table.
			$this->get_table_name(),
			array(
				'status'     => sanitize_key( $status ),
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'attachment_id' => absint( $attachment_id ) ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}
}
