<?php
/**
 * Shared helpers for cleanup tasks.
 *
 * @package DatabaseCleanup
 */

namespace DatabaseCleanup\Includes\Tasks;

use DatabaseCleanup\Includes\Cleanup_Task_Interface;
use DatabaseCleanup\Includes\Protected_Items;

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

abstract class Abstract_Cleanup_Task implements Cleanup_Task_Interface {

	/**
	 * @param array<string, mixed> $settings
	 * @return list<string>
	 */
	protected function excluded_post_types( array $settings ): array {
		$builtin = Protected_Items::protected_post_types();
		$extra   = [];

		if ( ! empty( $settings['excluded_post_types'] ) && is_array( $settings['excluded_post_types'] ) ) {
			foreach ( $settings['excluded_post_types'] as $type ) {
				$key = sanitize_key( (string) $type );
				if ( $key !== '' ) {
					$extra[] = $key;
				}
			}
		}

		return array_values( array_unique( array_merge( $builtin, $extra ) ) );
	}

	/**
	 * @param list<string> $types
	 */
	protected function sql_post_type_exclusion( array $types ): string {
		if ( $types === [] ) {
			return '';
		}

		global $wpdb;

		$escaped = array_map(
			static fn( string $type ): string => $wpdb->prepare( '%s', $type ),
			$types
		);

		return ' AND post_type NOT IN (' . implode( ',', $escaped ) . ')';
	}

	protected function estimate_row_bytes( int $count, int $avg_bytes ): int {
		return max( 0, $count ) * max( 0, $avg_bytes );
	}

	/**
	 * Count deletable duplicate meta rows without correlated subqueries.
	 *
	 * @return array{count:int,details:array<string, mixed>}
	 */
	protected function scan_duplicate_meta( string $table, string $parent_col, int $large_threshold = 250000 ): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$table_rows = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );

		if ( $table_rows > $large_threshold ) {
			return [
				'count'   => 0,
				'details' => [
					'scan_deferred' => true,
					'reason'        => sprintf(
						/* translators: %s: formatted row count */
						__( 'This table has %s rows. Use the meta browser to inspect duplicates safely.', 'wp-powersuite' ),
						number_format_i18n( $table_rows )
					),
				],
			];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$count = (int) $wpdb->get_var(
			"SELECT COALESCE(SUM(cnt - 1), 0) FROM (
				SELECT COUNT(*) AS cnt
				FROM {$table}
				GROUP BY {$parent_col}, meta_key, MD5(meta_value)
				HAVING cnt > 1
			) duplicates"
		);

		return [
			'count'   => max( 0, $count ),
			'details' => [],
		];
	}

	/**
	 * Delete duplicate meta rows in batches, keeping the lowest primary key per group.
	 *
	 * @return array{deleted:int,done:bool,cursor:mixed,bytes:int}
	 */
	protected function clean_duplicate_meta( string $table, string $pk, string $parent_col, string $meta_type, int $batch_size, mixed $cursor = null ): array {
		global $wpdb;

		$after_id = is_array( $cursor ) ? absint( $cursor['after_id'] ?? 0 ) : 0;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$meta_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT main.{$pk} FROM {$table} main
				WHERE main.{$pk} > %d
				AND EXISTS (
					SELECT 1 FROM (
						SELECT {$parent_col} AS parent_id, meta_key, MD5(meta_value) AS vhash, MIN({$pk}) AS min_pk, COUNT(*) AS cnt
						FROM {$table}
						GROUP BY {$parent_col}, meta_key, MD5(meta_value)
						HAVING cnt > 1
					) dupg
					WHERE dupg.parent_id = main.{$parent_col}
						AND dupg.meta_key = main.meta_key
						AND dupg.vhash = MD5(main.meta_value)
						AND main.{$pk} > dupg.min_pk
				)
				ORDER BY main.{$pk} ASC
				LIMIT %d",
				$after_id,
				$batch_size
			)
		);

		$deleted = 0;
		$bytes   = 0;
		$last_id = $after_id;

		foreach ( (array) $meta_ids as $id ) {
			$id = (int) $id;
			if ( $id <= 0 ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$row = $wpdb->get_row( $wpdb->prepare( "SELECT meta_key, meta_value FROM {$table} WHERE {$pk} = %d", $id ), ARRAY_A );
			if ( is_array( $row ) ) {
				if ( Protected_Items::is_protected_meta_key( (string) ( $row['meta_key'] ?? '' ), $meta_type ) ) {
					$last_id = max( $last_id, $id );
					continue;
				}

				$bytes += strlen( (string) ( $row['meta_key'] ?? '' ) ) + strlen( (string) ( $row['meta_value'] ?? '' ) ) + 32;
			}

			if ( delete_metadata_by_mid( $meta_type, $id ) ) {
				++$deleted;
			}

			$last_id = max( $last_id, $id );
		}

		$done = $meta_ids === [] || count( (array) $meta_ids ) < $batch_size;

		return [
			'deleted' => $deleted,
			'done'    => $done,
			'cursor'  => $done ? null : [ 'after_id' => $last_id ],
			'bytes'   => $bytes,
		];
	}

	/**
	 * Append WP PowerSuite option exclusions to a prepared statement.
	 *
	 * Never embed a prepared SQL fragment inside another prepare() call — literal % signs
	 * in LIKE patterns break wpdb::prepare() and can trigger HTML admin notices during AJAX.
	 *
	 * @param array<int, mixed> $params
	 */
	protected function prepare_with_plugin_option_exclusion( string $sql, array $params, string $column = 'option_name' ): string {
		global $wpdb;

		$sql .= " AND {$column} NOT LIKE %s AND {$column} NOT LIKE %s";
		$sql .= " AND {$column} NOT LIKE %s AND {$column} NOT LIKE %s";
		$sql .= " AND {$column} NOT LIKE %s AND {$column} NOT LIKE %s";
		$sql .= " AND {$column} NOT LIKE %s AND {$column} NOT LIKE %s";
		$sql .= " AND {$column} NOT LIKE %s AND {$column} NOT LIKE %s";

		return $wpdb->prepare(
			$sql,
			...array_merge(
				$params,
				[
					$wpdb->esc_like( 'wpps_' ) . '%',
					$wpdb->esc_like( 'wp_powersuite_' ) . '%',
					$wpdb->esc_like( '_transient_wpps_' ) . '%',
					$wpdb->esc_like( '_transient_wp_powersuite_' ) . '%',
					$wpdb->esc_like( '_transient_timeout_wpps_' ) . '%',
					$wpdb->esc_like( '_transient_timeout_wp_powersuite_' ) . '%',
					$wpdb->esc_like( '_site_transient_wpps_' ) . '%',
					$wpdb->esc_like( '_site_transient_wp_powersuite_' ) . '%',
					$wpdb->esc_like( '_site_transient_timeout_wpps_' ) . '%',
					$wpdb->esc_like( '_site_transient_timeout_wp_powersuite_' ) . '%',
				]
			)
		);
	}
}
