<?php
namespace Smart_Media_Cleanup;

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

class Attachment_Usage {

	/**
	 * Returns true if the attachment is used by any post other than $exclude_post_id.
	 */
	public static function is_used_elsewhere( $attachment_id, $exclude_post_id = 0 ) {
		$attachment_id   = absint( $attachment_id );
		$exclude_post_id = absint( $exclude_post_id );

		if ( ! $attachment_id ) {
			return false;
		}

		// 1. Attachment is directly attached to a post via its own post_parent.
		//    WordPress sets this when an image is uploaded while editing a post.
		if ( self::check_own_post_parent( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'own_post_parent' );
		}

		// 2. Used as post_parent by another post (attachment is a child container).
		if ( self::check_post_parent( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'post_parent' );
		}

		// 3. Used as featured image by another post.
		if ( self::check_featured_image( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'featured_image' );
		}

		// 4. Referenced in configured meta fields (post meta).
		if ( self::check_meta_fields( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'meta' );
		}

		// 5. Referenced in taxonomy term meta fields (e.g. JetEngine fields on taxonomy terms).
		if ( self::check_term_meta_fields( $attachment_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'term_meta' );
		}

		// 6. Referenced in post_content or post_excerpt.
		if ( self::check_post_content( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'post_content' );
		}

		// 7. Referenced in Elementor page builder data.
		if ( self::check_elementor( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'elementor' );
		}

		// 8. Referenced in theme mods / customizer or site icon.
		if ( self::check_theme_mods( $attachment_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'theme_mods' );
		}

		// 9. Referenced in SEO plugin meta (Yoast, RankMath) — auto-detected.
		if ( self::check_seo_plugins( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'seo_plugin' );
		}

		// 10. Referenced in Minorank SEO plugin (URL-based storage).
		if ( self::check_minorank( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'minorank' );
		}

		// 11. Referenced in WooCommerce product galleries or category thumbnails.
		if ( self::check_woocommerce( $attachment_id, $exclude_post_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'woocommerce' );
		}

		// 12. Referenced in wp_options via configured meta keys (ACF Options pages, JetEngine global fields, etc.).
		if ( self::check_options_fields( $attachment_id ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
			return apply_filters( 'smc_attachment_is_used', true, $attachment_id, $exclude_post_id, 'options' );
		}

		// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
		return apply_filters( 'smc_attachment_is_used', false, $attachment_id, $exclude_post_id, 'none' );
	}

	/**
	 * Checks if the attachment itself has a post_parent pointing to an existing post.
	 * WordPress sets post_parent when an image is uploaded while editing a post.
	 */
	private static function check_own_post_parent( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		$sql = "SELECT post_parent FROM {$wpdb->posts}
			WHERE ID = %d
			AND post_parent != 0
			AND post_parent != %d
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
		$parent_id = (int) $wpdb->get_var( $wpdb->prepare( $sql, $attachment_id, $exclude_post_id ) );
		if ( ! $parent_id ) {
			return false;
		}

		// Verify the parent post still exists and is not trashed.
		$parent_status = get_post_status( $parent_id );
		return $parent_status && ! in_array( $parent_status, array( 'trash', 'auto-draft' ), true );
	}

	private static function check_post_parent( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		$sql = "SELECT ID FROM {$wpdb->posts}
			WHERE post_parent = %d
			AND ID != %d
			AND post_status NOT IN ('trash', 'auto-draft')
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $attachment_id, $exclude_post_id ) );
	}

	private static function check_featured_image( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		$sql = "SELECT post_id FROM {$wpdb->postmeta}
			WHERE meta_key = '_thumbnail_id'
			AND meta_value = %s
			AND post_id != %d
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, (string) $attachment_id, $exclude_post_id ) );
	}

	private static function check_meta_fields( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		$settings  = Plugin::instance()->get_settings()->get();
		$meta_keys = array_filter( (array) $settings['meta_keys'] );

		if ( empty( $meta_keys ) ) {
			return false;
		}

		$id           = $attachment_id;
		$placeholders = implode( ', ', array_fill( 0, count( $meta_keys ), '%s' ) );

		$values = array_merge(
			$meta_keys,
			array(
				$exclude_post_id,
				(string) $id,          // exact single value
				'%,' . $id . ',%',     // middle of CSV: 111,ID,222
				$id . ',%',            // first in CSV:  ID,222,333
				'%,' . $id,            // last in CSV:   111,222,ID
				'%"' . $id . '"%',     // JSON quoted:   ["ID"] or {"id":"ID"}
				'%[' . $id . ',%',     // first in JSON array: [ID,222]
				'%,' . $id . ']%',     // last in JSON array:  [111,ID]
				'%[' . $id . ']%',     // only item:     [ID]
				'%i:' . $id . ';%',    // PHP serialized integer: i:ID;
			)
		);

		$sql = "SELECT post_id FROM {$wpdb->postmeta}
			WHERE meta_key IN ({$placeholders})
			AND post_id != %d
			AND (
				meta_value = %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
			)
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	private static function check_term_meta_fields( $attachment_id ) {
		global $wpdb;

		$settings  = Plugin::instance()->get_settings()->get();
		$meta_keys = array_filter( (array) $settings['meta_keys'] );

		if ( empty( $meta_keys ) ) {
			return false;
		}

		$id           = $attachment_id;
		$placeholders = implode( ', ', array_fill( 0, count( $meta_keys ), '%s' ) );

		$values = array_merge(
			$meta_keys,
			array(
				(string) $id,
				'%,' . $id . ',%',
				$id . ',%',
				'%,' . $id,
				'%"' . $id . '"%',
				'%[' . $id . ',%',
				'%,' . $id . ']%',
				'%[' . $id . ']%',
				'%i:' . $id . ';%',
			)
		);

		$sql = "SELECT term_id FROM {$wpdb->termmeta}
			WHERE meta_key IN ({$placeholders})
			AND (
				meta_value = %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
			)
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	private static function check_post_content( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		$likes = array();

		// 1. CSS class added by WP editor to every inserted image — most reliable.
		$likes[] = '%wp-image-' . $attachment_id . '%';

		$url = wp_get_attachment_url( $attachment_id );
		if ( $url ) {
			// 2. Full URL with extension stripped — matches all resized variants
			//    e.g. photo.jpg also matches photo-800x533.jpg.
			$base    = preg_replace( '/\.[^.]+$/', '', $url );
			$likes[] = '%' . $wpdb->esc_like( $base ) . '%';

			// 3. Path without domain — handles http↔https mismatches and
			//    domain changes that happen during site imports/migrations.
			$path = preg_replace( '#^https?://[^/]+#', '', $base );
			if ( $path && $path !== $base ) {
				$likes[] = '%' . $wpdb->esc_like( $path ) . '%';
			}
		}

		// Build OR clauses searching both post_content and post_excerpt.
		$or_clauses = array();
		$values     = array();
		foreach ( $likes as $like ) {
			$or_clauses[] = 'post_content LIKE %s';
			$or_clauses[] = 'post_excerpt LIKE %s';
			$values[]     = $like;
			$values[]     = $like;
		}
		$values[] = $exclude_post_id;

		$where = implode( ' OR ', $or_clauses );

		$sql = "SELECT ID FROM {$wpdb->posts}
			WHERE ( {$where} )
			AND ID != %d
			AND post_status NOT IN ('trash', 'auto-draft')
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Checks Elementor's _elementor_data meta for the attachment ID.
	 * Elementor stores image IDs as {"id":123} inside its JSON structure.
	 */
	private static function check_elementor( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		// Elementor serializes image widgets as "id":123 in _elementor_data.
		$like_id    = '%"id":' . $attachment_id . '%';
		$like_id_sp = '%"id": ' . $attachment_id . '%';
		// Background images and gallery items use "url" — also search by URL.
		$url      = wp_get_attachment_url( $attachment_id );
		$like_url = $url ? '%' . $wpdb->esc_like( $url ) . '%' : null;

		$values = array( $exclude_post_id, $like_id, $like_id_sp );
		$url_clause = '';
		if ( $like_url ) {
			$url_clause = 'OR meta_value LIKE %s';
			$values[]   = $like_url;
		}

		$sql = "SELECT post_id FROM {$wpdb->postmeta}
			WHERE meta_key = '_elementor_data'
			AND post_id != %d
			AND (
				meta_value LIKE %s
				OR meta_value LIKE %s
				{$url_clause}
			)
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Checks Minorank SEO plugin references.
	 * Minorank stores URLs (not IDs) in two places:
	 *   - _minorank_og_image postmeta (per-post OG image URL)
	 *   - minorank_local_business wp_option (JSON with logo_url)
	 */
	private static function check_minorank( $attachment_id, $exclude_post_id ) {
		// Only run if Minorank is active.
		if ( ! defined( 'MINORANK_VERSION' ) && ! class_exists( 'Minorank_Meta', false ) ) {
			return false;
		}

		$url = wp_get_attachment_url( $attachment_id );
		if ( ! $url ) {
			return false;
		}

		// 1. Per-post OG image stored in _minorank_og_image as a URL string.
		global $wpdb;
		// Use path-only search to handle http↔https mismatches.
		$path = preg_replace( '#^https?://[^/]+#', '', $url );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$found = $wpdb->get_var( $wpdb->prepare(
			"SELECT post_id FROM {$wpdb->postmeta}
			WHERE meta_key = '_minorank_og_image'
			AND post_id != %d
			AND meta_value LIKE %s
			LIMIT 1",
			$exclude_post_id,
			'%' . $wpdb->esc_like( $path ) . '%'
		) );

		if ( $found ) {
			return true;
		}

		// 2. Site-wide Local Business schema stored in wp_options as JSON with logo_url.
		$local_business = get_option( 'minorank_local_business' );
		if ( ! empty( $local_business ) ) {
			$data = is_array( $local_business ) ? $local_business : json_decode( $local_business, true );
			if ( is_array( $data ) && ! empty( $data['logo_url'] ) ) {
				$logo_path = preg_replace( '#^https?://[^/]+#', '', $data['logo_url'] );
				if ( $path && $logo_path && $path === $logo_path ) {
					return true;
				}
			}
		}

		return false;
	}

	/**
	 * Checks SEO plugin meta fields — auto-detects Yoast SEO and RankMath.
	 * No manual configuration needed by the user.
	 */
	private static function check_seo_plugins( $attachment_id, $exclude_post_id ) {
		global $wpdb;

		$meta_keys = array();

		// Yoast SEO.
		if ( defined( 'WPSEO_VERSION' ) || class_exists( 'WPSEO_Options', false ) ) {
			$meta_keys = array_merge( $meta_keys, array(
				'_yoast_wpseo_opengraph-image-id',
				'_yoast_wpseo_twitter-image-id',
			) );
		}

		// RankMath SEO.
		if ( defined( 'RANK_MATH_VERSION' ) || class_exists( 'RankMath', false ) ) {
			$meta_keys = array_merge( $meta_keys, array(
				'rank_math_facebook_image_id',
				'rank_math_twitter_image_id',
			) );
		}

		if ( empty( $meta_keys ) ) {
			return false;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $meta_keys ), '%s' ) );
		$values       = array_merge( $meta_keys, array( $exclude_post_id, (string) $attachment_id ) );

		$sql = "SELECT post_id FROM {$wpdb->postmeta}
			WHERE meta_key IN ({$placeholders})
			AND post_id != %d
			AND meta_value = %s
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Checks WooCommerce-specific image storage automatically — no user config needed.
	 * Covers product gallery (_product_image_gallery postmeta) and product category
	 * / tag thumbnails (thumbnail_id termmeta).
	 */
	private static function check_woocommerce( $attachment_id, $exclude_post_id ) {
		if ( ! class_exists( 'WooCommerce', false ) && ! defined( 'WC_VERSION' ) ) {
			return false;
		}

		global $wpdb;

		$id = $attachment_id;

		// Product gallery — stored as a comma-separated list of IDs in postmeta.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$found = $wpdb->get_var( $wpdb->prepare(
			"SELECT post_id FROM {$wpdb->postmeta}
			WHERE meta_key = '_product_image_gallery'
			AND post_id != %d
			AND (
				meta_value = %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
				OR meta_value LIKE %s
			)
			LIMIT 1",
			$exclude_post_id,
			(string) $id,
			'%,' . $id . ',%',
			$id . ',%',
			'%,' . $id
		) );

		if ( $found ) {
			return true;
		}

		// Product category / tag thumbnail — stored as thumbnail_id in termmeta.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return (bool) $wpdb->get_var( $wpdb->prepare(
			"SELECT term_id FROM {$wpdb->termmeta}
			WHERE meta_key = 'thumbnail_id'
			AND meta_value = %s
			LIMIT 1",
			(string) $id
		) );
	}

	/**
	 * Checks wp_options for configured meta keys.
	 * Covers ACF Options page fields (stored as option_name = key or options_key)
	 * and JetEngine / other plugins that store global image settings in wp_options.
	 */
	private static function check_options_fields( $attachment_id ) {
		global $wpdb;

		$settings  = Plugin::instance()->get_settings()->get();
		$meta_keys = array_filter( (array) $settings['meta_keys'] );

		if ( empty( $meta_keys ) ) {
			return false;
		}

		// ACF Options pages prefix field names with 'options_'. Include both variants.
		$option_names = array();
		foreach ( $meta_keys as $key ) {
			$option_names[] = $key;
			$option_names[] = 'options_' . $key;
		}

		$id           = $attachment_id;
		$placeholders = implode( ', ', array_fill( 0, count( $option_names ), '%s' ) );

		$values = array_merge(
			$option_names,
			array(
				(string) $id,
				'%,' . $id . ',%',
				$id . ',%',
				'%,' . $id,
				'%"' . $id . '"%',
				'%[' . $id . ',%',
				'%,' . $id . ']%',
				'%[' . $id . ']%',
				'%i:' . $id . ';%',
			)
		);

		$sql = "SELECT option_id FROM {$wpdb->options}
			WHERE option_name IN ({$placeholders})
			AND (
				option_value = %s
				OR option_value LIKE %s
				OR option_value LIKE %s
				OR option_value LIKE %s
				OR option_value LIKE %s
				OR option_value LIKE %s
				OR option_value LIKE %s
				OR option_value LIKE %s
				OR option_value LIKE %s
			)
			LIMIT 1";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (bool) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Checks WordPress customizer / theme mods and site-wide options for images.
	 */
	private static function check_theme_mods( $attachment_id ) {
		// WordPress site icon stored in wp_options (not a theme mod).
		if ( (int) get_option( 'site_icon' ) === $attachment_id ) {
			return true;
		}

		$mods = get_theme_mods();
		if ( empty( $mods ) ) {
			return false;
		}

		// Theme mod keys that store attachment IDs.
		$id_keys = array( 'custom_logo', 'header_image_data' );
		foreach ( $id_keys as $key ) {
			if ( isset( $mods[ $key ] ) && (int) $mods[ $key ] === $attachment_id ) {
				return true;
			}
		}

		// Theme mod keys that store URLs — match by URL.
		$url_keys = array( 'header_image', 'background_image' );
		$url      = wp_get_attachment_url( $attachment_id );
		if ( $url ) {
			foreach ( $url_keys as $key ) {
				if ( isset( $mods[ $key ] ) && $mods[ $key ] === $url ) {
					return true;
				}
			}
		}

		return false;
	}

	// -------------------------------------------------------------------------
	// Bulk helpers — used by the orphan scanner for performance.
	// -------------------------------------------------------------------------

	/**
	 * Returns a flat array of every attachment ID that is in use somewhere on the site.
	 * Runs ~12 queries regardless of library size. Used by the orphan scanner to
	 * avoid the per-attachment query loop.
	 *
	 * NOTE: does NOT replace is_used_elsewhere(). The delete safety check still calls
	 * is_used_elsewhere() per-attachment to ensure nothing slips through.
	 */
	public static function get_all_used_ids() {
		global $wpdb;
		$used = array();

		// 1. Attachments with a valid post_parent set by WordPress at upload time.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_col(
			"SELECT p.ID FROM {$wpdb->posts} p
			INNER JOIN {$wpdb->posts} par ON par.ID = p.post_parent
			WHERE p.post_type = 'attachment'
			AND p.post_parent > 0
			AND par.post_status NOT IN ('trash', 'auto-draft')"
		);
		$used = array_merge( $used, array_map( 'intval', (array) $rows ) );

		// 2. Any post/page/CPT that uses an attachment ID as its own post_parent.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_col(
			"SELECT DISTINCT post_parent FROM {$wpdb->posts}
			WHERE post_parent > 0
			AND post_status NOT IN ('trash', 'auto-draft')"
		);
		$used = array_merge( $used, array_map( 'intval', (array) $rows ) );

		// 3. Featured images (_thumbnail_id postmeta).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_col(
			"SELECT DISTINCT meta_value FROM {$wpdb->postmeta}
			WHERE meta_key = '_thumbnail_id' AND meta_value > 0"
		);
		$used = array_merge( $used, array_map( 'intval', (array) $rows ) );

		// 4+5+6. User-configured meta keys — postmeta, termmeta, wp_options.
		$settings  = Plugin::instance()->get_settings()->get();
		$meta_keys = array_filter( (array) $settings['meta_keys'] );

		if ( ! empty( $meta_keys ) ) {
			$ph = implode( ', ', array_fill( 0, count( $meta_keys ), '%s' ) );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_col( $wpdb->prepare(
				"SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key IN ({$ph}) AND meta_value != ''", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				$meta_keys
			) );
			foreach ( (array) $rows as $raw ) {
				$used = array_merge( $used, self::extract_ids_from_value( $raw ) );
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_col( $wpdb->prepare(
				"SELECT meta_value FROM {$wpdb->termmeta} WHERE meta_key IN ({$ph}) AND meta_value != ''", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				$meta_keys
			) );
			foreach ( (array) $rows as $raw ) {
				$used = array_merge( $used, self::extract_ids_from_value( $raw ) );
			}

			// wp_options: check both 'key' and 'options_key' (ACF Options page prefix).
			$opt_names = array();
			foreach ( $meta_keys as $key ) {
				$opt_names[] = $key;
				$opt_names[] = 'options_' . $key;
			}
			$opt_ph = implode( ', ', array_fill( 0, count( $opt_names ), '%s' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_col( $wpdb->prepare(
				"SELECT option_value FROM {$wpdb->options} WHERE option_name IN ({$opt_ph})", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				$opt_names
			) );
			foreach ( (array) $rows as $raw ) {
				$used = array_merge( $used, self::extract_ids_from_value( $raw ) );
			}
		}

		// 7. Post content / excerpt — match wp-image-{id} CSS class added by the WP editor.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$content = $wpdb->get_col(
			"SELECT CONCAT( COALESCE(post_content,''), ' ', COALESCE(post_excerpt,'') )
			FROM {$wpdb->posts}
			WHERE post_status NOT IN ('trash', 'auto-draft')
			AND post_type NOT IN ('attachment', 'revision')"
		);
		if ( $content ) {
			preg_match_all( '/wp-image-(\d+)/', implode( ' ', $content ), $m );
			if ( ! empty( $m[1] ) ) {
				$used = array_merge( $used, array_map( 'intval', $m[1] ) );
			}
		}

		// 8. Elementor — extract image IDs from all _elementor_data JSON blobs.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$el_rows = $wpdb->get_col(
			"SELECT meta_value FROM {$wpdb->postmeta}
			WHERE meta_key = '_elementor_data'
			AND meta_value != '' AND meta_value != 'null'"
		);
		foreach ( (array) $el_rows as $json ) {
			preg_match_all( '/"id"\s*:\s*(\d+)/', $json, $m );
			if ( ! empty( $m[1] ) ) {
				$used = array_merge( $used, array_map( 'intval', $m[1] ) );
			}
		}

		// 9. Theme mods / site icon.
		$site_icon = (int) get_option( 'site_icon' );
		if ( $site_icon ) {
			$used[] = $site_icon;
		}
		$mods = get_theme_mods();
		if ( ! empty( $mods['custom_logo'] ) ) {
			$used[] = (int) $mods['custom_logo'];
		}

		// 10. SEO plugins — Yoast and RankMath ID-based postmeta.
		$seo_keys = array();
		if ( defined( 'WPSEO_VERSION' ) || class_exists( 'WPSEO_Options', false ) ) {
			$seo_keys = array_merge( $seo_keys, array( '_yoast_wpseo_opengraph-image-id', '_yoast_wpseo_twitter-image-id' ) );
		}
		if ( defined( 'RANK_MATH_VERSION' ) || class_exists( 'RankMath', false ) ) {
			$seo_keys = array_merge( $seo_keys, array( 'rank_math_facebook_image_id', 'rank_math_twitter_image_id' ) );
		}
		if ( ! empty( $seo_keys ) ) {
			$seo_ph = implode( ', ', array_fill( 0, count( $seo_keys ), '%s' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
			$rows = $wpdb->get_col( $wpdb->prepare(
				"SELECT DISTINCT meta_value FROM {$wpdb->postmeta} WHERE meta_key IN ({$seo_ph}) AND meta_value > 0", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				$seo_keys
			) );
			$used = array_merge( $used, array_map( 'intval', (array) $rows ) );
		}

		// 11. WooCommerce — product galleries and category/tag thumbnails.
		if ( class_exists( 'WooCommerce', false ) || defined( 'WC_VERSION' ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$rows = $wpdb->get_col(
				"SELECT meta_value FROM {$wpdb->postmeta}
				WHERE meta_key = '_product_image_gallery' AND meta_value != ''"
			);
			foreach ( (array) $rows as $raw ) {
				$used = array_merge( $used, self::extract_ids_from_value( $raw ) );
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$rows = $wpdb->get_col(
				"SELECT DISTINCT meta_value FROM {$wpdb->termmeta}
				WHERE meta_key = 'thumbnail_id' AND meta_value > 0"
			);
			$used = array_merge( $used, array_map( 'intval', (array) $rows ) );
		}

		// 12. Minorank SEO — URL-based storage; resolve each URL to an attachment ID.
		if ( defined( 'MINORANK_VERSION' ) || class_exists( 'Minorank_Meta', false ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$og_urls = $wpdb->get_col(
				"SELECT DISTINCT meta_value FROM {$wpdb->postmeta}
				WHERE meta_key = '_minorank_og_image' AND meta_value != ''"
			);
			foreach ( (array) $og_urls as $url ) {
				$id = attachment_url_to_postid( $url );
				if ( $id ) {
					$used[] = $id;
				}
			}

			$lb = get_option( 'minorank_local_business' );
			if ( ! empty( $lb ) ) {
				$lb_data = is_array( $lb ) ? $lb : json_decode( $lb, true );
				if ( is_array( $lb_data ) && ! empty( $lb_data['logo_url'] ) ) {
					$id = attachment_url_to_postid( $lb_data['logo_url'] );
					if ( $id ) {
						$used[] = $id;
					}
				}
			}
		}

		return array_unique( array_filter( $used ) );
	}

	/**
	 * Extracts integer attachment IDs from a raw meta value.
	 * Handles plain integers, comma-separated IDs, JSON arrays, and PHP-serialized arrays.
	 */
	private static function extract_ids_from_value( $raw ) {
		if ( empty( $raw ) || ! is_string( $raw ) ) {
			return array();
		}

		$raw = trim( $raw );

		// Plain integer.
		if ( ctype_digit( $raw ) ) {
			$id = (int) $raw;
			return $id > 0 ? array( $id ) : array();
		}

		// JSON array or object.
		if ( '{' === $raw[0] || '[' === $raw[0] ) {
			$decoded = json_decode( $raw, true );
			if ( is_array( $decoded ) ) {
				$ids = array();
				array_walk_recursive( $decoded, function ( $val ) use ( &$ids ) {
					if ( is_numeric( $val ) && (int) $val > 0 ) {
						$ids[] = (int) $val;
					}
				} );
				return $ids;
			}
		}

		// PHP serialized.
		if ( is_serialized( $raw ) ) {
			// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.serialize_unserialize
			$data = @unserialize( $raw );
			if ( is_array( $data ) ) {
				$ids = array();
				array_walk_recursive( $data, function ( $val ) use ( &$ids ) {
					if ( is_numeric( $val ) && (int) $val > 0 ) {
						$ids[] = (int) $val;
					}
				} );
				return $ids;
			}
			if ( is_numeric( $data ) && (int) $data > 0 ) {
				return array( (int) $data );
			}
		}

		// Comma-separated integers: "123,456,789".
		if ( preg_match( '/^[\d\s,]+$/', $raw ) ) {
			$ids = array_filter( array_map( 'intval', preg_split( '/[\s,]+/', $raw ) ) );
			return array_values( $ids );
		}

		return array();
	}
}
