<?php
/**
 * Cached media library scanner for alt text health.
 *
 * @package AIAltTextGenerator
 * @since 1.4.0
 */

namespace AIAltTextGenerator;

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

/**
 * Performance-safe scanner with cached aggregates.
 *
 * @since 1.4.0
 */
class Scanner {

	private const CACHE_KEY = 'wpps_ai_alt_scan_summary_v2';
	private const CACHE_TTL = HOUR_IN_SECONDS;

	/**
	 * Get cached scan summary.
	 *
	 * @param bool $force_refresh Bypass cache.
	 * @param bool $full          Include expensive duplicate/Elementor counts.
	 * @return array<string, int|string>
	 */
	public static function getSummary( bool $force_refresh = false, bool $full = false ): array {
		if ( ! $force_refresh ) {
			$cached = get_transient( self::CACHE_KEY );
			if ( is_array( $cached ) ) {
				if ( $full || empty( $cached['fast_only'] ) ) {
					return $cached;
				}
			}
		}

		$summary = Performance::measure(
			'library_scan',
			static fn() => self::buildSummary( $full ),
			[ 'full' => $full ]
		);
		set_transient( self::CACHE_KEY, $summary, self::CACHE_TTL );

		return $summary;
	}

	public static function invalidateCache(): void {
		delete_transient( self::CACHE_KEY );
	}

	/**
	 * @return array<string, int|string>
	 */
	private static function buildSummary( bool $full = false ): array {
		global $wpdb;

		$mimes      = AltTextGenerator::getAllowedMimes();
		$mime_place = implode( ',', array_fill( 0, count( $mimes ), '%s' ) );
		$mime_args  = array_merge( [ 'attachment', 'inherit' ], $mimes );

		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(ID) FROM {$wpdb->posts}
				WHERE post_type = %s AND post_status = %s
				AND post_mime_type IN ($mime_place)",
				...$mime_args
			)
		);

		$decorative = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT p.ID)
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} dm ON dm.post_id = p.ID
					AND dm.meta_key = %s AND dm.meta_value = '1'
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)",
				AttachmentMeta::META_DECORATIVE,
				...$mime_args
			)
		);

		$missing = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT p.ID)
				FROM {$wpdb->posts} p
				LEFT JOIN {$wpdb->postmeta} am ON am.post_id = p.ID AND am.meta_key = '_wp_attachment_image_alt'
				LEFT JOIN {$wpdb->postmeta} dm ON dm.post_id = p.ID
					AND dm.meta_key = %s AND dm.meta_value = '1'
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)
				AND dm.post_id IS NULL
				AND (am.meta_value IS NULL OR am.meta_value = '')",
				AttachmentMeta::META_DECORATIVE,
				...$mime_args
			)
		);

		$pending_review = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT p.ID)
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} sm ON sm.post_id = p.ID AND sm.meta_key = %s
				INNER JOIN {$wpdb->postmeta} st ON st.post_id = p.ID
					AND st.meta_key = %s
					AND st.meta_value IN ('generated', 'needs_review')
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)",
				AttachmentMeta::META_SUGGESTION,
				AttachmentMeta::META_STATUS,
				...$mime_args
			)
		);

		$weak = self::countWeakAlt( $mimes );
		$with_alt = max( 0, $total - $missing - $decorative );
		$optimized = max( 0, $with_alt - $weak );

		return [
			'total'             => $total,
			'missing'           => $missing,
			'decorative'        => $decorative,
			'weak'              => $weak,
			'optimized'         => $optimized,
			'pending_review'    => $pending_review,
			'duplicates'        => $full ? DuplicateDetector::countDuplicates() : 0,
			'elementor_missing' => $full && defined( 'ELEMENTOR_VERSION' ) && class_exists( ElementorIntegration::class ) ? ElementorIntegration::countMissingInElementor() : 0,
			'last_scan'         => gmdate( 'Y-m-d H:i:s' ),
			'fast_only'         => $full ? 0 : 1,
		];
	}

	/**
	 * Count weak alt text using SQL heuristics (fast approximate).
	 *
	 * @param list<string> $mimes
	 */
	private static function countWeakAlt( array $mimes ): int {
		global $wpdb;

		$mime_place = implode( ',', array_fill( 0, count( $mimes ), '%s' ) );
		$weak_sql = QualityScorer::weakAltSqlClause( 'am.meta_value' );

		$args = array_merge(
			[ AttachmentMeta::META_DECORATIVE, 'attachment', 'inherit' ],
			$mimes
		);

		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- weak clause is allowlisted words via esc_sql(); MIME list is %s placeholders.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT p.ID)
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} am ON am.post_id = p.ID
					AND am.meta_key = '_wp_attachment_image_alt'
					AND am.meta_value != ''
				LEFT JOIN {$wpdb->postmeta} dm ON dm.post_id = p.ID
					AND dm.meta_key = %s AND dm.meta_value = '1'
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)
				AND dm.post_id IS NULL
				AND ({$weak_sql})",
				...$args
			)
		);
	}

	/**
	 * Get attachment IDs missing alt text (paginated).
	 *
	 * @return list<int>
	 */
	public static function getMissingIds( int $limit = 100, int $offset = 0 ): array {
		global $wpdb;

		$mimes      = AltTextGenerator::getAllowedMimes();
		$mime_place = implode( ',', array_fill( 0, count( $mimes ), '%s' ) );
		$args       = array_merge(
			[ AttachmentMeta::META_DECORATIVE, 'attachment', 'inherit' ],
			$mimes,
			[ $limit, $offset ]
		);

		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT p.ID
				FROM {$wpdb->posts} p
				LEFT JOIN {$wpdb->postmeta} am ON am.post_id = p.ID AND am.meta_key = '_wp_attachment_image_alt'
				LEFT JOIN {$wpdb->postmeta} dm ON dm.post_id = p.ID
					AND dm.meta_key = %s AND dm.meta_value = '1'
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)
				AND dm.post_id IS NULL
				AND (am.meta_value IS NULL OR am.meta_value = '')
				ORDER BY p.ID ASC
				LIMIT %d OFFSET %d",
				...$args
			)
		);

		return array_map( 'intval', $ids ?: [] );
	}

	/**
	 * Get attachments pending review.
	 *
	 * @return list<int>
	 */
	public static function getPendingReviewIds( int $limit = 50, int $offset = 0 ): array {
		global $wpdb;

		$mimes      = AltTextGenerator::getAllowedMimes();
		$mime_place = implode( ',', array_fill( 0, count( $mimes ), '%s' ) );
		$args       = array_merge(
			[ AttachmentMeta::META_SUGGESTION, AttachmentMeta::META_STATUS, 'attachment', 'inherit' ],
			$mimes,
			[ $limit, $offset ]
		);

		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT p.ID
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} sm ON sm.post_id = p.ID AND sm.meta_key = %s
				INNER JOIN {$wpdb->postmeta} st ON st.post_id = p.ID
					AND st.meta_key = %s
					AND st.meta_value IN ('generated', 'needs_review')
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)
				ORDER BY p.ID DESC
				LIMIT %d OFFSET %d",
				...$args
			)
		);

		return array_map( 'intval', $ids ?: [] );
	}

	/**
	 * @return list<array{attachment_id: int, current_alt: string, suggestion: string, status: string}>
	 */
	public static function getPendingReviewRows( int $limit = 20, int $offset = 0, string $search = '' ): array {
		global $wpdb;

		$mimes      = AltTextGenerator::getAllowedMimes();
		$mime_place = implode( ',', array_fill( 0, count( $mimes ), '%s' ) );
		$search_sql = '';
		$search_arg = [];

		if ( trim( $search ) !== '' ) {
			$like         = '%' . $wpdb->esc_like( trim( $search ) ) . '%';
			$search_sql   = ' AND (CAST(p.ID AS CHAR) LIKE %s OR sm.meta_value LIKE %s OR am.meta_value LIKE %s)';
			$search_arg   = [ $like, $like, $like ];
		}

		$args = array_merge(
			[ AttachmentMeta::META_SUGGESTION, AttachmentMeta::META_STATUS, 'attachment', 'inherit' ],
			$mimes,
			$search_arg,
			[ $limit, $offset ]
		);

		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- optional search clause uses %s placeholders only.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DISTINCT p.ID AS attachment_id,
					COALESCE(am.meta_value, '') AS current_alt,
					sm.meta_value AS suggestion,
					st.meta_value AS status
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} sm ON sm.post_id = p.ID AND sm.meta_key = %s
				INNER JOIN {$wpdb->postmeta} st ON st.post_id = p.ID
					AND st.meta_key = %s
					AND st.meta_value IN ('generated', 'needs_review')
				LEFT JOIN {$wpdb->postmeta} am ON am.post_id = p.ID AND am.meta_key = '_wp_attachment_image_alt'
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)
				{$search_sql}
				ORDER BY p.ID DESC
				LIMIT %d OFFSET %d",
				...$args
			),
			ARRAY_A
		);

		return array_values(
			array_map(
				static function ( array $row ): array {
					return [
						'attachment_id' => (int) ( $row['attachment_id'] ?? 0 ),
						'current_alt'   => (string) ( $row['current_alt'] ?? '' ),
						'suggestion'    => (string) ( $row['suggestion'] ?? '' ),
						'status'        => (string) ( $row['status'] ?? '' ),
					];
				},
				is_array( $rows ) ? $rows : []
			)
		);
	}

	public static function countPendingReview( string $search = '' ): int {
		global $wpdb;

		$mimes      = AltTextGenerator::getAllowedMimes();
		$mime_place = implode( ',', array_fill( 0, count( $mimes ), '%s' ) );
		$search_sql = '';
		$search_arg = [];

		if ( trim( $search ) !== '' ) {
			$like         = '%' . $wpdb->esc_like( trim( $search ) ) . '%';
			$search_sql   = ' AND (CAST(p.ID AS CHAR) LIKE %s OR sm.meta_value LIKE %s OR am.meta_value LIKE %s)';
			$search_arg   = [ $like, $like, $like ];
		}

		$args = array_merge(
			[ AttachmentMeta::META_SUGGESTION, AttachmentMeta::META_STATUS, 'attachment', 'inherit' ],
			$mimes,
			$search_arg
		);

		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- optional search clause uses %s placeholders only.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT p.ID)
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} sm ON sm.post_id = p.ID AND sm.meta_key = %s
				INNER JOIN {$wpdb->postmeta} st ON st.post_id = p.ID
					AND st.meta_key = %s
					AND st.meta_value IN ('generated', 'needs_review')
				LEFT JOIN {$wpdb->postmeta} am ON am.post_id = p.ID AND am.meta_key = '_wp_attachment_image_alt'
				WHERE p.post_type = %s AND p.post_status = %s
				AND p.post_mime_type IN ($mime_place)
				{$search_sql}",
				...$args
			)
		);
	}
}
