<?php
/**
 * Orphaned transient timeout rows cleanup task.
 *
 * @package DatabaseCleanup
 */

namespace DatabaseCleanup\Includes\Tasks;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class Task_Orphaned_Transient_Timeouts extends Abstract_Cleanup_Task {

	public function get_id(): string {
		return 'orphaned_transient_timeouts';
	}

	public function get_label(): string {
		return __( 'Orphaned transient timeouts', 'wp-powersuite' );
	}

	public function get_description(): string {
		return __( 'Remove _transient_timeout_* rows whose transient value row is missing.', 'wp-powersuite' );
	}

	public function is_safe(): bool {
		return true;
	}

	public function scan( array $settings ): array {
		global $wpdb;

		unset( $settings );

		$timeout_like = $wpdb->esc_like( '_transient_timeout_' ) . '%';
		$site_timeout = $wpdb->esc_like( '_site_transient_timeout_' ) . '%';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$regular = (int) $wpdb->get_var(
			$this->prepare_with_plugin_option_exclusion(
				"SELECT COUNT(*) FROM {$wpdb->options} t
				LEFT JOIN {$wpdb->options} o ON o.option_name = CONCAT('_transient_', SUBSTRING(t.option_name, 20))
				WHERE t.option_name LIKE %s
				AND t.option_name NOT LIKE %s
				AND o.option_id IS NULL",
				[ $timeout_like, $site_timeout ],
				't.option_name'
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$site = (int) $wpdb->get_var(
			$this->prepare_with_plugin_option_exclusion(
				"SELECT COUNT(*) FROM {$wpdb->options} t
				LEFT JOIN {$wpdb->options} o ON o.option_name = CONCAT('_site_transient_', SUBSTRING(t.option_name, 24))
				WHERE t.option_name LIKE %s
				AND o.option_id IS NULL",
				[ $site_timeout ],
				't.option_name'
			)
		);

		$count = $regular + $site;

		return [
			'count'           => $count,
			'estimated_bytes' => $this->estimate_row_bytes( $count, 128 ),
			'details'         => [
				'regular' => $regular,
				'site'    => $site,
			],
		];
	}

	public function clean( array $settings, int $batch_size, mixed $cursor = null ): array {
		global $wpdb;

		unset( $settings );

		$phase   = is_array( $cursor ) ? (string) ( $cursor['phase'] ?? 'regular' ) : 'regular';
		$offset  = is_array( $cursor ) ? absint( $cursor['offset'] ?? 0 ) : 0;
		$deleted = 0;
		$bytes   = 0;

		if ( $phase === 'regular' ) {
			$timeout_like = $wpdb->esc_like( '_transient_timeout_' ) . '%';
			$site_timeout = $wpdb->esc_like( '_site_transient_timeout_' ) . '%';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_col(
				$this->prepare_with_plugin_option_exclusion(
					"SELECT t.option_id FROM {$wpdb->options} t
					LEFT JOIN {$wpdb->options} o ON o.option_name = CONCAT('_transient_', SUBSTRING(t.option_name, 20))
					WHERE t.option_name LIKE %s
					AND t.option_name NOT LIKE %s
					AND o.option_id IS NULL
					ORDER BY t.option_id ASC
					LIMIT %d OFFSET %d",
					[ $timeout_like, $site_timeout, $batch_size, $offset ],
					't.option_name'
				)
			);

			foreach ( (array) $rows as $option_id ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$row = $wpdb->get_row( $wpdb->prepare( "SELECT option_name FROM {$wpdb->options} WHERE option_id = %d", (int) $option_id ), ARRAY_A );
				$name = is_array( $row ) ? (string) ( $row['option_name'] ?? '' ) : '';
				if ( $name !== '' && delete_option( $name ) ) {
					++$deleted;
					$bytes += 128;
				}
			}

			if ( count( (array) $rows ) < $batch_size ) {
				return [
					'deleted' => $deleted,
					'done'    => false,
					'cursor'  => [ 'phase' => 'site', 'offset' => 0 ],
					'bytes'   => $bytes,
				];
			}

			return [
				'deleted' => $deleted,
				'done'    => false,
				'cursor'  => [ 'phase' => 'regular', 'offset' => $offset + $batch_size ],
				'bytes'   => $bytes,
			];
		}

		$site_timeout = $wpdb->esc_like( '_site_transient_timeout_' ) . '%';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_col(
			$this->prepare_with_plugin_option_exclusion(
				"SELECT t.option_id FROM {$wpdb->options} t
				LEFT JOIN {$wpdb->options} o ON o.option_name = CONCAT('_site_transient_', SUBSTRING(t.option_name, 24))
				WHERE t.option_name LIKE %s
				AND o.option_id IS NULL
				ORDER BY t.option_id ASC
				LIMIT %d OFFSET %d",
				[ $site_timeout, $batch_size, $offset ],
				't.option_name'
			)
		);

		foreach ( (array) $rows as $option_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$row = $wpdb->get_row( $wpdb->prepare( "SELECT option_name FROM {$wpdb->options} WHERE option_id = %d", (int) $option_id ), ARRAY_A );
			$name = is_array( $row ) ? (string) ( $row['option_name'] ?? '' ) : '';
			if ( $name !== '' && delete_option( $name ) ) {
				++$deleted;
				$bytes += 128;
			}
		}

		$done = count( (array) $rows ) < $batch_size;

		return [
			'deleted' => $deleted,
			'done'    => $done,
			'cursor'  => $done ? null : [ 'phase' => 'site', 'offset' => $offset + $batch_size ],
			'bytes'   => $bytes,
		];
	}
}
