<?php
/**
 * Scanner — finds products/categories with missing content.
 *
 * Used by:
 *   • Dashboard widget (summary)
 *   • Bulk panel "Missing" tab (lists with Fix Now actions)
 *
 * All queries use direct SQL (prepared) for speed on large catalogs.
 *
 * @package CatalogPilot\AiStudio
 */

namespace CatalogPilot\AiStudio;

defined( 'ABSPATH' ) || exit;

final class Scanner {

	/**
	 * Return counts + sample product IDs for each "missing" field.
	 *
	 * @param int $sample_limit How many IDs to include per bucket (for "Fix Now").
	 * @return array{
	 *     products: array<string, int>,
	 *     categories: array<string, int>,
	 *     ids: array<string, int[]>,
	 *     generated_at: int
	 * }
	 */
	public static function scan( $sample_limit = 500 ) {
		$sample_limit = max( 1, min( 2000, (int) $sample_limit ) );

		$products   = self::scan_products( $sample_limit );
		$categories = self::scan_categories( $sample_limit );

		return array(
			'products'     => $products['counts'],
			'categories'   => $categories['counts'],
			'ids'          => array(
				'missing_short_desc' => $products['ids']['missing_short_desc'],
				'missing_long_desc'  => $products['ids']['missing_long_desc'],
				'missing_image'      => $products['ids']['missing_image'],
				'missing_alt'        => $products['ids']['missing_alt'],
				'missing_seo'        => $products['ids']['missing_seo'],
				'missing_meta_title' => $products['ids']['missing_meta_title'],
				'missing_meta_desc'  => $products['ids']['missing_meta_desc'],
				'missing_faq'        => $products['ids']['missing_faq'],
				'missing_cat_desc'   => $categories['ids']['missing_cat_desc'],
			),
			'generated_at' => time(),
		);
	}

	/**
	 * Product-level missing content scan.
	 *
	 * Fields checked:
	 *   • missing_short_desc — post_excerpt empty
	 *   • missing_long_desc  — post_content empty
	 *   • missing_image      — no featured image (_thumbnail_id)
	 *   • missing_alt        — featured image set but alt text empty
	 *   • missing_seo        — no Yoast/RankMath/AIOSEO meta description
	 *
	 * @return array{counts: array<string,int>, ids: array<string,int[]>}
	 */
	private static function scan_products( $sample_limit ) {
		global $wpdb;

		$counts = array(
			'total'               => 0,
			'missing_short_desc'  => 0,
			'missing_long_desc'   => 0,
			'missing_image'       => 0,
			'missing_alt'         => 0,
			'missing_seo'         => 0,
			'missing_meta_title'  => 0,
			'missing_meta_desc'   => 0,
			'missing_faq'         => 0,
		);
		$ids = array(
			'missing_short_desc'  => array(),
			'missing_long_desc'   => array(),
			'missing_image'       => array(),
			'missing_alt'         => array(),
			'missing_seo'         => array(),
			'missing_meta_title'  => array(),
			'missing_meta_desc'   => array(),
			'missing_faq'         => array(),
		);

		// Total published products.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['total'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type=%s AND post_status=%s",
			'product',
			'publish'
		) );
		if ( 0 === $counts['total'] ) {
			return array( 'counts' => $counts, 'ids' => $ids );
		}

		// Missing short description (post_excerpt).
		$ids['missing_short_desc'] = array_map(
			'intval',
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
			$wpdb->get_col( $wpdb->prepare(
				"SELECT ID FROM {$wpdb->posts}
				 WHERE post_type='product' AND post_status='publish'
				   AND (post_excerpt IS NULL OR TRIM(post_excerpt)='')
				 ORDER BY ID DESC LIMIT %d",
				$sample_limit
			) )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['missing_short_desc'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts}
			 WHERE post_type=%s AND post_status=%s
			   AND (post_excerpt IS NULL OR TRIM(post_excerpt)='')",
			'product',
			'publish'
		) );

		// Missing long description (post_content).
		$ids['missing_long_desc'] = array_map(
			'intval',
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
			$wpdb->get_col( $wpdb->prepare(
				"SELECT ID FROM {$wpdb->posts}
				 WHERE post_type='product' AND post_status='publish'
				   AND (post_content IS NULL OR TRIM(post_content)='')
				 ORDER BY ID DESC LIMIT %d",
				$sample_limit
			) )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['missing_long_desc'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts}
			 WHERE post_type=%s AND post_status=%s
			   AND (post_content IS NULL OR TRIM(post_content)='')",
			'product',
			'publish'
		) );

		// Missing featured image — no _thumbnail_id meta row with a numeric value.
		$ids['missing_image'] = array_map(
			'intval',
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
			$wpdb->get_col( $wpdb->prepare(
				"SELECT p.ID FROM {$wpdb->posts} p
				 LEFT JOIN {$wpdb->postmeta} pm
				        ON pm.post_id=p.ID AND pm.meta_key='_thumbnail_id'
				 WHERE p.post_type='product' AND p.post_status='publish'
				   AND (pm.meta_value IS NULL OR pm.meta_value='' OR pm.meta_value='0')
				 ORDER BY p.ID DESC LIMIT %d",
				$sample_limit
			) )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['missing_image'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts} p
			 LEFT JOIN {$wpdb->postmeta} pm
			        ON pm.post_id=p.ID AND pm.meta_key=%s
			 WHERE p.post_type=%s AND p.post_status=%s
			   AND (pm.meta_value IS NULL OR pm.meta_value='' OR pm.meta_value='0')",
			'_thumbnail_id',
			'product',
			'publish'
		) );

		// Missing alt on featured image.
		$ids['missing_alt'] = array_map(
			'intval',
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
			$wpdb->get_col( $wpdb->prepare(
				"SELECT p.ID FROM {$wpdb->posts} p
				 INNER JOIN {$wpdb->postmeta} pm_thumb
				        ON pm_thumb.post_id=p.ID AND pm_thumb.meta_key='_thumbnail_id'
				       AND pm_thumb.meta_value!='' AND pm_thumb.meta_value!='0'
				 LEFT JOIN {$wpdb->postmeta} pm_alt
				        ON pm_alt.post_id=pm_thumb.meta_value
				       AND pm_alt.meta_key='_wp_attachment_image_alt'
				 WHERE p.post_type='product' AND p.post_status='publish'
				   AND (pm_alt.meta_value IS NULL OR TRIM(pm_alt.meta_value)='')
				 ORDER BY p.ID DESC LIMIT %d",
				$sample_limit
			) )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['missing_alt'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->postmeta} pm_thumb
			        ON pm_thumb.post_id=p.ID AND pm_thumb.meta_key=%s
			       AND pm_thumb.meta_value!='' AND pm_thumb.meta_value!='0'
			 LEFT JOIN {$wpdb->postmeta} pm_alt
			        ON pm_alt.post_id=pm_thumb.meta_value
			       AND pm_alt.meta_key=%s
			 WHERE p.post_type=%s AND p.post_status=%s
			   AND (pm_alt.meta_value IS NULL OR TRIM(pm_alt.meta_value)='')",
			'_thumbnail_id',
			'_wp_attachment_image_alt',
			'product',
			'publish'
		) );

		// Missing SEO — check all three common plugins.
		$seo_keys = self::seo_meta_keys();
		if ( ! empty( $seo_keys ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $seo_keys ), '%s' ) );

			$sql = "SELECT p.ID FROM {$wpdb->posts} p
			        LEFT JOIN {$wpdb->postmeta} pm
			               ON pm.post_id=p.ID AND pm.meta_key IN ($placeholders)
			                  AND pm.meta_value!=''
			        WHERE p.post_type='product' AND p.post_status='publish'
			          AND pm.meta_id IS NULL
			        ORDER BY p.ID DESC LIMIT %d";

			$args   = array_merge( $seo_keys, array( $sample_limit ) );
			$ids['missing_seo'] = array_map( 'intval', $wpdb->get_col( $wpdb->prepare( $sql, $args ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.

			$count_sql = "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
			              LEFT JOIN {$wpdb->postmeta} pm
			                     ON pm.post_id=p.ID AND pm.meta_key IN ($placeholders)
			                        AND pm.meta_value!=''
			              WHERE p.post_type='product' AND p.post_status='publish'
			                AND pm.meta_id IS NULL";
			$counts['missing_seo'] = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $seo_keys ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.
		}

		// Missing meta title / meta description — active SEO plugin's own key.
		$title_key = self::seo_meta_title_key();
		if ( '' !== $title_key ) {
			$ids['missing_meta_title'] = array_map(
				'intval',
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Prepared with placeholders; scanner reads live WooCommerce data.
				$wpdb->get_col( $wpdb->prepare(
					"SELECT p.ID FROM {$wpdb->posts} p
					 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id=p.ID AND pm.meta_key=%s AND pm.meta_value!=''
					 WHERE p.post_type='product' AND p.post_status='publish' AND pm.meta_id IS NULL
					 ORDER BY p.ID DESC LIMIT %d",
					$title_key,
					$sample_limit
				) )
			);
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.
			$counts['missing_meta_title'] = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->posts} p
				 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id=p.ID AND pm.meta_key=%s AND pm.meta_value!=''
				 WHERE p.post_type=%s AND p.post_status=%s AND pm.meta_id IS NULL",
				$title_key,
				'product',
				'publish'
			) );
		}
		$desc_key = self::seo_meta_desc_key();
		if ( '' !== $desc_key ) {
			$ids['missing_meta_desc'] = array_map(
				'intval',
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.
				$wpdb->get_col( $wpdb->prepare(
					"SELECT p.ID FROM {$wpdb->posts} p
					 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id=p.ID AND pm.meta_key=%s AND pm.meta_value!=''
					 WHERE p.post_type='product' AND p.post_status='publish' AND pm.meta_id IS NULL
					 ORDER BY p.ID DESC LIMIT %d",
					$desc_key,
					$sample_limit
				) )
			);
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.
			$counts['missing_meta_desc'] = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->posts} p
				 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id=p.ID AND pm.meta_key=%s AND pm.meta_value!=''
				 WHERE p.post_type=%s AND p.post_status=%s AND pm.meta_id IS NULL",
				$desc_key,
				'product',
				'publish'
			) );
		}

		// Missing FAQ — no _cpais_faq meta or empty JSON array.
		$ids['missing_faq'] = array_map(
			'intval',
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.
			$wpdb->get_col( $wpdb->prepare(
				"SELECT p.ID FROM {$wpdb->posts} p
				 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id=p.ID AND pm.meta_key=%s AND pm.meta_value NOT IN ('','[]','null')
				 WHERE p.post_type='product' AND p.post_status='publish' AND pm.meta_id IS NULL
				 ORDER BY p.ID DESC LIMIT %d",
				'_cpais_faq',
				$sample_limit
			) )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared with placeholders; scanner reads live WooCommerce data.
		$counts['missing_faq'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts} p
			 LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id=p.ID AND pm.meta_key=%s AND pm.meta_value NOT IN ('','[]','null')
			 WHERE p.post_type=%s AND p.post_status=%s AND pm.meta_id IS NULL",
			'_cpais_faq',
			'product',
			'publish'
		) );

		return array( 'counts' => $counts, 'ids' => $ids );
	}

	/** Returns the active SEO plugin's meta-title key (or '' if none). */
	private static function seo_meta_title_key() {
		$id = \CatalogPilot\AiStudio\SEO::detect();
		switch ( $id ) {
			case \CatalogPilot\AiStudio\SEO::INT_YOAST:
				return '_yoast_wpseo_title';
			case \CatalogPilot\AiStudio\SEO::INT_RANKMATH:
				return 'rank_math_title';
			case \CatalogPilot\AiStudio\SEO::INT_AIOSEO:
				return '_aioseo_title';
		}
		return '_cpais_seo_title';
	}

	/** Returns the active SEO plugin's meta-description key (or '' if none). */
	private static function seo_meta_desc_key() {
		$id = \CatalogPilot\AiStudio\SEO::detect();
		switch ( $id ) {
			case \CatalogPilot\AiStudio\SEO::INT_YOAST:
				return '_yoast_wpseo_metadesc';
			case \CatalogPilot\AiStudio\SEO::INT_RANKMATH:
				return 'rank_math_description';
			case \CatalogPilot\AiStudio\SEO::INT_AIOSEO:
				return '_aioseo_description';
		}
		return '_cpais_seo_description';
	}

	/**
	 * Category-level missing description scan.
	 *
	 * @return array{counts: array<string,int>, ids: array<string,int[]>}
	 */
	private static function scan_categories( $sample_limit ) {
		global $wpdb;

		$counts = array(
			'total'             => 0,
			'missing_cat_desc'  => 0,
		);
		$ids = array(
			'missing_cat_desc' => array(),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['total'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->term_taxonomy} WHERE taxonomy=%s",
			'product_cat'
		) );
		if ( 0 === $counts['total'] ) {
			return array( 'counts' => $counts, 'ids' => $ids );
		}

		$ids['missing_cat_desc'] = array_map(
			'intval',
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
			$wpdb->get_col( $wpdb->prepare(
				"SELECT term_id FROM {$wpdb->term_taxonomy}
				 WHERE taxonomy='product_cat'
				   AND (description IS NULL OR TRIM(description)='')
				 ORDER BY term_id DESC LIMIT %d",
				$sample_limit
			) )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Product scanner: live WooCommerce read, per-request freshness required.
		$counts['missing_cat_desc'] = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->term_taxonomy}
			 WHERE taxonomy=%s
			   AND (description IS NULL OR TRIM(description)='')",
			'product_cat'
		) );

		return array( 'counts' => $counts, 'ids' => $ids );
	}

	/**
	 * Meta keys used by supported SEO plugins for the meta-description field.
	 * Presence in ANY of these means the product has SEO content.
	 *
	 * @return string[]
	 */
	private static function seo_meta_keys() {
		// Delegate to SEO::detect() — keeps detection logic in one place.
		$id = \CatalogPilot\AiStudio\SEO::detect();
		switch ( $id ) {
			case \CatalogPilot\AiStudio\SEO::INT_YOAST:
				return array( '_yoast_wpseo_metadesc' );
			case \CatalogPilot\AiStudio\SEO::INT_RANKMATH:
				return array( 'rank_math_description' );
			case \CatalogPilot\AiStudio\SEO::INT_AIOSEO:
				return array( '_aioseo_description' );
			default:
				// No SEO plugin detected — fall back to checking all keys we know.
				return array(
					'_yoast_wpseo_metadesc',
					'rank_math_description',
					'_aioseo_description',
				);
		}
	}
}
