<?php
/**
 * Database clean-up: rows found with SQL, deleted one by one through WordPress or WooCommerce.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\DatabaseCleanup;

defined( 'ABSPATH' ) || exit;

/**
 * Counts with one query, then deletes each row through its owner's API (wp_delete_post(),
 * wp_delete_comment(), WooCommerce CRUD …), so hooks run and caches are cleared. Each row is
 * checked again right before its API call; a row that no longer qualifies is left alone, and an
 * error thrown by another plugin's hook skips that row.
 *
 * The SQL is built here from one `FROM … WHERE …` fragment, so paging and re-checking live in
 * one place. Extra arguments (see BaseCheck for the rest):
 * - `from`: callable( ApiRowsCheck $check ): array{0: string, 1: array} — `FROM … WHERE …` with
 *   placeholders, and its arguments (it can read `$check->threshold()`).
 * - `id_column`: the ID column expression, e.g. `v.ID` (alias.column).
 * - `sample_select`: columns for the sample, e.g. `v.ID, v.post_title` (aliases, columns, commas).
 * - `sample_map`: callable( object[] $rows ): array{columns: array<string, string>, rows: array<int, array<string, string>>}.
 * - `delete`: callable( int $id ): bool — delete one row through the API.
 * - `finish`: optional callable(): void — after a step that deleted something (cache flushes).
 * - `api_batch`: rows per step at the default batch (int).
 *
 * @since 1.0.0
 */
class ApiRowsCheck extends BaseCheck {

	/**
	 * The FROM … WHERE fragment and its arguments, or null when the check is set up wrong.
	 *
	 * @return array{0: string, 1: array<int, mixed>}|null
	 */
	private function from(): ?array {
		if ( ! is_callable( $this->args['from'] ?? null ) || ! preg_match( '/^[a-z]{1,8}\.[A-Za-z_]{1,64}$/', (string) ( $this->args['id_column'] ?? '' ) ) ) {
			return null;
		}
		$from = call_user_func( $this->args['from'], $this );
		if ( ! is_array( $from ) || ! isset( $from[0], $from[1] ) || ! is_string( $from[0] ) || ! is_array( $from[1] ) ) {
			return null;
		}
		return array( $from[0], array_values( $from[1] ) );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param array<string, mixed> $state  State from the last call.
	 * @param float                $budget Seconds to spend.
	 * @return array{count: int, done: bool, state: array<string, mixed>}
	 */
	public function count( array $state, float $budget ): array {
		global $wpdb;
		$from  = $this->from();
		$count = 0;
		if ( $from ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- live count; the fragment holds placeholders only.
			$count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) {$from[0]}", $from[1] ) );
		}
		return array(
			'count' => max( 0, $count ),
			'done'  => true,
			'state' => array(),
		);
	}

	/**
	 * IDs above a cursor, lowest first.
	 *
	 * @param int $after Cursor.
	 * @param int $limit Rows.
	 * @return int[]
	 */
	private function find( int $after, int $limit ): array {
		global $wpdb;
		$from = $this->from();
		if ( ! $from ) {
			return array();
		}
		$id = (string) $this->args['id_column'];
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- IDs to delete through the API; $id is a validated alias.column.
		return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare( "SELECT {$id} {$from[0]} AND {$id} > %d ORDER BY {$id} LIMIT %d", array_merge( $from[1], array( $after, $limit ) ) ) ) );
	}

	/**
	 * Whether a row still qualifies (right before its delete).
	 *
	 * @param int $row_id Row ID.
	 * @return bool
	 */
	private function still( int $row_id ): bool {
		global $wpdb;
		$from = $this->from();
		if ( ! $from ) {
			return false;
		}
		$id = (string) $this->args['id_column'];
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- re-check right before the delete.
		return (bool) $wpdb->get_var( $wpdb->prepare( "SELECT 1 {$from[0]} AND {$id} = %d", array_merge( $from[1], array( $row_id ) ) ) );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param array<string, mixed> $state  State from the last call.
	 * @param int                  $batch  Rows per statement.
	 * @param float                $budget Seconds to spend.
	 * @return array{deleted: int, skipped: int, notes: array<string, int>, done: bool, state: array<string, mixed>}
	 */
	public function clean( array $state, int $batch, float $budget ): array {
		$out = self::cleaned( $state, false );
		if ( ! $this->from() || ! is_callable( $this->args['delete'] ?? null ) ) {
			return self::cleaned( $state, true );
		}
		$start = microtime( true );
		$limit = self::api_batch( $batch, max( 1, (int) ( $this->args['api_batch'] ?? 25 ) ) );
		$after = isset( $state['after'] ) ? (int) $state['after'] : 0;
		do {
			$ids = $this->find( $after, $limit );
			if ( ! $ids ) {
				$out['done'] = true;
				break;
			}
			foreach ( $ids as $id ) {
				$after = max( $after, $id );
				if ( ! $this->still( $id ) ) {
					continue; // Changed since it was found: leave it.
				}
				try {
					if ( call_user_func( $this->args['delete'], $id ) ) {
						++$out['deleted'];
					} else {
						++$out['skipped'];
						$out['notes']['not_deleted'] = ( $out['notes']['not_deleted'] ?? 0 ) + 1;
					}
				} catch ( \Throwable $e ) {
					unset( $e );
					++$out['skipped'];
					$out['notes']['error'] = ( $out['notes']['error'] ?? 0 ) + 1;
				}
				if ( ! self::time_left( $start, $budget ) ) {
					break 2;
				}
			}
		} while ( self::time_left( $start, $budget ) );
		if ( $out['deleted'] && is_callable( $this->args['finish'] ?? null ) ) {
			call_user_func( $this->args['finish'] );
		}
		$out['state'] = array( 'after' => $after );
		return $out;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int $limit Rows (at most 10).
	 * @return array{columns: array<string, string>, rows: array<int, array<string, string>>}
	 */
	public function sample( int $limit = 10 ): array {
		global $wpdb;
		$empty  = array(
			'columns' => array(),
			'rows'    => array(),
		);
		$from   = $this->from();
		$select = (string) ( $this->args['sample_select'] ?? '' );
		if ( ! $from || ! is_callable( $this->args['sample_map'] ?? null ) || ! preg_match( '/^[A-Za-z0-9_., ]{1,500}$/', $select ) ) {
			return $empty;
		}
		$id = (string) $this->args['id_column'];
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- a few rows for the sample; $select is validated.
		$rows   = (array) $wpdb->get_results( $wpdb->prepare( "SELECT {$select} {$from[0]} ORDER BY {$id} LIMIT %d", array_merge( $from[1], array( max( 1, min( 10, $limit ) ) ) ) ) );
		$sample = call_user_func( $this->args['sample_map'], $rows );
		return is_array( $sample ) && isset( $sample['columns'], $sample['rows'] ) ? $sample : $empty;
	}
}