<?php
/**
 * Database clean-up: term links to posts that are gone.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\DatabaseCleanup\Checks;

use SmartWP\AdminPowerups\Modules\DatabaseCleanup\BaseCheck;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\OrphanRowsCheck;

defined( 'ABSPATH' ) || exit;

/**
 * `term_relationships` rows whose object (a post) no longer exists, only for registered
 * taxonomies used by post types alone. User taxonomies (such as Customer tags), link categories
 * and unregistered taxonomies are skipped: their objects aren't posts, or we can't tell. The
 * missing-post condition is part of the DELETE; term counts are recounted afterwards. Count,
 * clean and sample walk the links in object ID ranges (primary key, gaps skipped), filtering the
 * taxonomies through the join, so no query reads more than one range.
 *
 * @since 1.0.0
 */
class RelationshipObjects extends BaseCheck {

	/**
	 * Taxonomies checked: registered, every object type a post type.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function taxonomies(): array {
		$types = get_post_types();
		$out   = array();
		foreach ( get_taxonomies( array(), 'objects' ) as $name => $taxonomy ) {
			$objects = array_values( array_filter( (array) $taxonomy->object_type, 'is_string' ) );
			if ( 'link_category' === $name || ! $objects || array_diff( $objects, $types ) ) {
				continue;
			}
			$out[] = (string) $name;
		}
		return $out;
	}

	/**
	 * Object IDs per range: a fifth of the `apwc_database_cleanup_scan_chunk` value (10,000 at the
	 * default; 1,000–40,000).
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function chunk(): int {
		return max( 1000, intdiv( OrphanRowsCheck::chunk(), 5 ) );
	}

	/**
	 * FROM … WHERE for orphan links of the checked taxonomies inside one object ID range
	 * (placeholders + arguments). The range on the primary key (object_id, term_taxonomy_id) comes
	 * first, so every query reads at most one range of links, never a whole taxonomy.
	 *
	 * @param string[] $taxonomies Taxonomies (not empty).
	 * @param int      $after      Range start (exclusive).
	 * @param int      $upper      Range end (inclusive).
	 * @return array{0: string, 1: array<int, mixed>}
	 */
	private static function range_sql( array $taxonomies, int $after, int $upper ): array {
		global $wpdb;
		return array(
			'FROM %i c INNER JOIN %i tt ON tt.term_taxonomy_id = c.term_taxonomy_id LEFT JOIN %i p ON p.ID = c.object_id WHERE c.object_id > %d AND c.object_id <= %d AND tt.taxonomy IN (' . self::placeholders( count( $taxonomies ), '%s' ) . ') AND p.ID IS NULL',
			array_merge( array( $wpdb->term_relationships, $wpdb->term_taxonomy, $wpdb->posts, $after, $upper ), $taxonomies ),
		);
	}

	/**
	 * Smallest object ID minus one and the largest (index-only on the primary key).
	 *
	 * @return array{0: int, 1: int}
	 */
	private static function bounds(): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- live bounds of the range walk.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT MIN(object_id) AS lo, MAX(object_id) AS hi FROM %i', $wpdb->term_relationships ) );
		if ( ! is_object( $row ) || null === $row->hi ) {
			return array( 0, 0 );
		}
		return array( (int) $row->lo - 1, (int) $row->hi ); // From just below the first ID (0 included).
	}

	/**
	 * The cursor after a range: just below the next object ID that has links (one index lookup),
	 * so gaps in the IDs are jumped over.
	 *
	 * @param int $upper End of the range just done.
	 * @param int $max   Largest object ID.
	 * @return int
	 */
	private static function skip( int $upper, int $max ): int {
		global $wpdb;
		if ( $upper >= $max ) {
			return $max;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- one index lookup.
		$next = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(object_id) FROM %i WHERE object_id > %d', $wpdb->term_relationships, $upper ) );
		return null === $next ? $max : max( $upper, (int) $next - 1 );
	}

	/**
	 * Whether the last query failed.
	 *
	 * @phpstan-impure
	 * @return bool
	 */
	private static function failed(): bool {
		global $wpdb;
		return '' !== (string) $wpdb->last_error;
	}

	/**
	 * The walk's position: `cursor` and `max` (set on the first call; a state from an older version
	 * starts over, which only re-reads links).
	 *
	 * @param array<string, mixed> $state State.
	 * @return array<string, mixed>
	 */
	private static function position( array $state ): array {
		if ( isset( $state['cursor'], $state['max'] ) ) {
			return $state;
		}
		list( $lo, $hi ) = self::bounds();
		return array(
			'cursor' => $lo,
			'max'    => $hi,
			'count'  => isset( $state['count'] ) ? (int) $state['count'] : 0,
		);
	}

	/**
	 * {@inheritDoc} Walks object ID ranges, bounded by the budget.
	 *
	 * @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;
		$taxonomies = self::taxonomies();
		$state      = self::position( $state );
		if ( ! $taxonomies ) {
			$state['cursor'] = $state['max'];
		}
		$start = microtime( true );
		$chunk = self::chunk();
		while ( (int) $state['cursor'] < (int) $state['max'] ) {
			$upper               = min( (int) $state['cursor'] + $chunk, (int) $state['max'] );
			list( $from, $args ) = self::range_sql( $taxonomies, (int) $state['cursor'], $upper );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- live count; $from is a constant fragment of placeholders (values passed to prepare()).
			$state['count'] += (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) {$from}", $args ) );
			$state['cursor'] = self::skip( $upper, (int) $state['max'] );
			if ( ! self::time_left( $start, $budget ) ) {
				break;
			}
		}
		return array(
			'count' => (int) $state['count'],
			'done'  => (int) $state['cursor'] >= (int) $state['max'],
			'state' => $state,
		);
	}

	/**
	 * {@inheritDoc} Walks object ID ranges; in each, deletes every orphan link of up to a batch of
	 * links' objects (the missing-post condition is checked again inside the DELETE), then recounts
	 * every term those links pointed at.
	 *
	 * @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 {
		global $wpdb;
		$out        = self::cleaned( $state, false );
		$start      = microtime( true );
		$chunk      = self::chunk();
		$taxonomies = self::taxonomies();
		$state      = self::position( $state );
		if ( ! $taxonomies ) {
			$state['cursor'] = $state['max'];
		}
		$tax_in = self::placeholders( count( $taxonomies ), '%s' );
		while ( (int) $state['cursor'] < (int) $state['max'] ) {
			$upper               = min( (int) $state['cursor'] + $chunk, (int) $state['max'] );
			list( $from, $args ) = self::range_sql( $taxonomies, (int) $state['cursor'], $upper );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $from is a constant fragment of placeholders (values passed to prepare()).
			$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT c.object_id AS o {$from} ORDER BY c.object_id LIMIT %d", array_merge( $args, array( $batch ) ) ) );
			if ( self::failed() ) {
				break;
			}
			if ( ! $rows ) {
				$state['cursor'] = self::skip( $upper, (int) $state['max'] );
			} else {
				$objects = array_values( array_unique( array_map( static fn( $row ) => (int) $row->o, $rows ) ) );
				$in      = self::placeholders( count( $objects ) );
				$where   = "c.object_id IN ({$in}) AND tt.taxonomy IN ({$tax_in}) AND p.ID IS NULL";
				$where_a = array_merge( array( $wpdb->term_relationships, $wpdb->term_taxonomy, $wpdb->posts ), $objects, $taxonomies );
				// Every term these objects' orphan links point at (all of them go below), for the recount.
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where holds only placeholders (one per ID / taxonomy); every value is passed to prepare().
				$terms = (array) $wpdb->get_results( $wpdb->prepare( "SELECT DISTINCT c.term_taxonomy_id AS t, tt.taxonomy AS x FROM %i c INNER JOIN %i tt ON tt.term_taxonomy_id = c.term_taxonomy_id LEFT JOIN %i p ON p.ID = c.object_id WHERE {$where}", $where_a ) );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where holds only placeholders (values passed to prepare()); the missing-post condition is checked again inside the DELETE.
				$gone            = $wpdb->query( $wpdb->prepare( "DELETE c FROM %i c INNER JOIN %i tt ON tt.term_taxonomy_id = c.term_taxonomy_id LEFT JOIN %i p ON p.ID = c.object_id WHERE {$where}", $where_a ) );
				$out['deleted'] += max( 0, (int) $gone );
				if ( self::failed() ) {
					break;
				}
				$by_taxonomy = array();
				foreach ( $terms as $term ) {
					$by_taxonomy[ (string) $term->x ][] = (int) $term->t;
				}
				foreach ( $by_taxonomy as $taxonomy => $tt_ids ) {
					wp_update_term_count_now( $tt_ids, $taxonomy );
					foreach ( $objects as $object_id ) {
						wp_cache_delete( $object_id, $taxonomy . '_relationships' );
					}
				}
				// Every orphan link of these objects went, so the cursor moves past them; a short
				// batch means the range is done.
				$state['cursor'] = count( $rows ) < $batch ? self::skip( $upper, (int) $state['max'] ) : max( $objects );
			}
			if ( ! self::time_left( $start, $budget ) ) {
				break;
			}
		}
		$out['done']  = (int) $state['cursor'] >= (int) $state['max'];
		$out['state'] = $state;
		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;
		$limit      = max( 1, min( 10, $limit ) );
		$out        = array();
		$taxonomies = self::taxonomies();
		$state      = self::position( array() );
		$start      = microtime( true );
		$chunk      = self::chunk();
		$found      = 0;
		// The first ranges with orphan links, for at most 2 seconds.
		while ( $taxonomies && $found < $limit && (int) $state['cursor'] < (int) $state['max'] && self::time_left( $start, 2.0 ) ) {
			$upper               = min( (int) $state['cursor'] + $chunk, (int) $state['max'] );
			list( $from, $args ) = self::range_sql( $taxonomies, (int) $state['cursor'], $upper );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- a few rows for the sample; $from is a constant fragment of placeholders (values passed to prepare()).
			foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT c.object_id AS o, c.term_taxonomy_id AS t, tt.taxonomy AS x {$from} ORDER BY c.object_id LIMIT %d", array_merge( $args, array( $limit - $found ) ) ) ) as $row ) {
				$out[] = array(
					'object'   => self::cell( $row->o ),
					'taxonomy' => self::cell( $row->x ),
					'term'     => self::cell( $row->t ),
				);
			}
			$state['cursor'] = self::skip( $upper, (int) $state['max'] );
			$found           = count( $out );
		}
		return array(
			'columns' => array(
				'object'   => __( 'Missing post ID', 'admin-powerups-for-woocommerce' ),
				'taxonomy' => __( 'Taxonomy', 'admin-powerups-for-woocommerce' ),
				'term'     => __( 'Term taxonomy ID', 'admin-powerups-for-woocommerce' ),
			),
			'rows'    => $out,
		);
	}
}