<?php
/**
 * Image ALT media index manager.
 *
 * @package SmartyPress_AI_Engine
 */

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

/**
 * Builds a lightweight indexed snapshot for bulk metadata generation.
 */
class SmartyPress_AI_Image_ALT_Index_Manager {

	const OPTION_NAME       = 'smartypress_ai_image_alt_index';
	const DB_VERSION       = '1.0.0';
	const DB_VERSION_OPTION = 'smartypress_ai_image_alt_index_db_version';
	const CACHE_GROUP      = 'smartypress_ai_image_alt_index';
	const STATS_CACHE_KEY  = 'stats';

	/**
	 * Create or update index table when needed.
	 *
	 * @return void
	 */
	public static function maybe_create_table() {
		if ( get_option( self::DB_VERSION_OPTION ) === self::DB_VERSION ) {
			return;
		}

		self::create_table();
	}

	/**
	 * Create or update index table.
	 *
	 * @return void
	 */
	public static function create_table() {
		global $wpdb;

		$table   = self::table_name();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			attachment_id bigint(20) unsigned NOT NULL,
			has_alt tinyint(1) unsigned NOT NULL DEFAULT 0,
			has_caption tinyint(1) unsigned NOT NULL DEFAULT 0,
			has_title tinyint(1) unsigned NOT NULL DEFAULT 0,
			has_description tinyint(1) unsigned NOT NULL DEFAULT 0,
			generated tinyint(1) unsigned NOT NULL DEFAULT 0,
			failed tinyint(1) unsigned NOT NULL DEFAULT 0,
			primary_post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			primary_post_type varchar(32) NOT NULL DEFAULT '',
			indexed_at datetime NOT NULL,
			PRIMARY KEY  (attachment_id),
			KEY has_alt (has_alt),
			KEY failed (failed),
			KEY generated (generated),
			KEY primary_post_type (primary_post_type),
			KEY indexed_at (indexed_at)
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, false );
	}

	/**
	 * Drop index table.
	 *
	 * @return void
	 */
	public static function drop_table() {
		global $wpdb;

		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table_name() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
		delete_option( self::DB_VERSION_OPTION );
		delete_option( self::OPTION_NAME );
	}

	/**
	 * Get index table name.
	 *
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;

		return $wpdb->prefix . 'smartypress_ai_image_alt_index';
	}

	/**
	 * Constructor.
	 */
	public function __construct() {
		self::maybe_create_table();
	}

	/**
	 * Start a new indexing run.
	 *
	 * @param array $filters Index filters.
	 * @return array
	 */
	public function start( array $filters ) {
		global $wpdb;

		$filters = $this->sanitize_filters( $filters );
		$table   = self::table_name();

		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		wp_cache_delete( self::STATS_CACHE_KEY, self::CACHE_GROUP );

		$state = array(
			'id'             => wp_generate_uuid4(),
			'status'         => 'running',
			'processed'      => 0,
			'indexed'        => 0,
			'total_estimate' => $this->count_candidate_attachments( $filters ),
			'last_id'        => 0,
			'filters'        => $filters,
			'stats'          => $this->empty_stats(),
			'queue_total'    => 0,
			'stale'          => false,
			'started_at'     => current_time( 'mysql' ),
			'updated_at'     => current_time( 'mysql' ),
		);

		update_option( self::OPTION_NAME, $state, false );

		return $state;
	}

	/**
	 * Process next index batch.
	 *
	 * @param int $batch_size Batch size.
	 * @return array
	 */
	public function process_next( $batch_size = 250 ) {
		$state = $this->get();

		if ( empty( $state ) || 'running' !== ( $state['status'] ?? '' ) ) {
			return array(
				'success' => false,
				'error'   => __( 'No running Image ALT indexing job found.', 'smartypress-ai-engine' ),
			);
		}

		$batch_size = max( 50, min( 500, absint( $batch_size ) ) );
		$filters    = $this->sanitize_filters( $state['filters'] ?? array() );
		$ids        = $this->query_candidate_attachment_ids( $filters, absint( $state['last_id'] ?? 0 ), $batch_size );

		if ( empty( $ids ) ) {
			$state['status']     = 'completed';
			$state['stats']       = $this->get_stats();
			$state['queue_total'] = $this->count_for_generation( $filters );
			$state['finished_at'] = current_time( 'mysql' );
			$state['updated_at']  = current_time( 'mysql' );
			update_option( self::OPTION_NAME, $state, false );

			return array(
				'success' => true,
				'index'   => $state,
			);
		}

		foreach ( $ids as $attachment_id ) {
			$state['last_id'] = max( absint( $state['last_id'] ?? 0 ), absint( $attachment_id ) );
			$state['processed']++;

			$row = $this->build_index_row( $attachment_id, $filters );
			if ( empty( $row ) ) {
				continue;
			}

			$this->upsert_row( $row );
			$state['indexed']++;
		}

		$state['stats']       = $this->get_stats();
		$state['queue_total'] = $this->count_for_generation( $filters );
		$state['updated_at']  = current_time( 'mysql' );
		update_option( self::OPTION_NAME, $state, false );

		return array(
			'success' => true,
			'index'   => $state,
		);
	}

	/**
	 * Cancel current indexing run.
	 *
	 * @return array
	 */
	public function cancel() {
		$state = $this->get();

		if ( empty( $state ) ) {
			return array( 'status' => 'idle' );
		}

		$state['status']      = 'cancelled';
		$state['finished_at'] = current_time( 'mysql' );
		$state['updated_at']  = current_time( 'mysql' );
		update_option( self::OPTION_NAME, $state, false );

		return $state;
	}

	/**
	 * Get current index state.
	 *
	 * @return array
	 */
	public function get() {
		$state = get_option( self::OPTION_NAME, array() );

		if ( ! is_array( $state ) ) {
			return array();
		}

		if ( ! empty( $state ) && empty( $state['stats'] ) ) {
			$state['stats'] = $this->get_stats();
		}

		return $state;
	}

	/**
	 * Mark current index as stale.
	 *
	 * @return void
	 */
	public function mark_stale() {
		$state = $this->get();

		if ( empty( $state ) ) {
			return;
		}

		$state['stale']      = true;
		$state['updated_at'] = current_time( 'mysql' );
		update_option( self::OPTION_NAME, $state, false );
	}

	/**
	 * Get indexed stats.
	 *
	 * @return array
	 */
	public function get_stats() {
		global $wpdb;

		$cached_stats = wp_cache_get( self::STATS_CACHE_KEY, self::CACHE_GROUP );
		if ( false !== $cached_stats ) {
			return $cached_stats;
		}

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT COUNT(*) AS total, SUM(has_alt) AS with_alt, SUM(generated) AS generated, SUM(failed) AS failed FROM %i',
				$table
			),
			ARRAY_A
		);

		$total    = absint( $row['total'] ?? 0 );
		$with_alt = absint( $row['with_alt'] ?? 0 );

		$stats = array(
			'total'       => $total,
			'with_alt'    => $with_alt,
			'without_alt' => max( 0, $total - $with_alt ),
			'generated'   => absint( $row['generated'] ?? 0 ),
			'failed'      => absint( $row['failed'] ?? 0 ),
		);

		wp_cache_set( self::STATS_CACHE_KEY, $stats, self::CACHE_GROUP, MINUTE_IN_SECONDS );

		return $stats;
	}

	/**
	 * Count indexed items that match a bulk filter.
	 *
	 * @param array $filters Bulk filters.
	 * @return int
	 */
	public function count_for_generation( array $filters ) {
		global $wpdb;

		$table  = self::table_name();
		$filter = $this->get_generation_filter_key( $this->sanitize_filters( $filters ) );

		if ( 'has_alt' === $filter ) {
			return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE has_alt = 1', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}

		if ( 'missing_caption' === $filter ) {
			return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE has_caption = 0', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}

		if ( 'all' === $filter ) {
			return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}

		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE has_alt = 0', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Query next indexed attachment IDs for bulk generation.
	 *
	 * @param array $filters            Bulk filters.
	 * @param int   $last_attachment_id Last processed attachment ID.
	 * @param int   $limit              Number of IDs.
	 * @return int[]
	 */
	public function query_generation_ids( array $filters, $last_attachment_id = 0, $limit = 5 ) {
		global $wpdb;

		$table              = self::table_name();
		$filter             = $this->get_generation_filter_key( $this->sanitize_filters( $filters ) );
		$last_attachment_id = absint( $last_attachment_id );
		$limit              = max( 1, min( 20, absint( $limit ) ) );

		if ( 'has_alt' === $filter ) {
			$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT attachment_id FROM %i WHERE has_alt = 1 AND attachment_id > %d ORDER BY attachment_id ASC LIMIT %d', $table, $last_attachment_id, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		} elseif ( 'missing_caption' === $filter ) {
			$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT attachment_id FROM %i WHERE has_caption = 0 AND attachment_id > %d ORDER BY attachment_id ASC LIMIT %d', $table, $last_attachment_id, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		} elseif ( 'all' === $filter ) {
			$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT attachment_id FROM %i WHERE attachment_id > %d ORDER BY attachment_id ASC LIMIT %d', $table, $last_attachment_id, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		} else {
			$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT attachment_id FROM %i WHERE has_alt = 0 AND attachment_id > %d ORDER BY attachment_id ASC LIMIT %d', $table, $last_attachment_id, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}

		return array_map( 'absint', $ids );
	}

	/**
	 * Check whether the current index can be used for generation.
	 *
	 * @return bool
	 */
	public function is_ready() {
		$state = $this->get();

		return ! empty( $state )
			&& 'completed' === ( $state['status'] ?? '' )
			&& empty( $state['stale'] );
	}

	/**
	 * Get public post type options for UI.
	 *
	 * @return array
	 */
	public function get_post_type_options() {
		$post_types = get_post_types( array( 'public' => true ), 'objects' );
		$options    = array();

		foreach ( $post_types as $name => $post_type ) {
			if ( in_array( $name, array( 'attachment' ), true ) ) {
				continue;
			}

			$options[] = array(
				'value' => $name,
				'label' => $post_type->labels->singular_name ?: $post_type->label,
			);
		}

		return $options;
	}

	/**
	 * Get default post types for index filters.
	 *
	 * @return array
	 */
	public function get_default_post_types() {
		$defaults = function_exists( 'smartypress_ai_get_default_enabled_post_types' )
			? smartypress_ai_get_default_enabled_post_types()
			: array( 'post', 'page' );

		return $this->sanitize_post_types( $defaults );
	}

	/**
	 * Build WHERE clause for indexed generation rows.
	 *
	 * @param array $filters Filters.
	 * @return string
	 */
	private function get_generation_filter_key( array $filters ) {
		$filter = sanitize_key( $filters['filter'] ?? 'missing_alt' );

		return in_array( $filter, array( 'has_alt', 'missing_caption', 'all' ), true )
			? $filter
			: 'missing_alt';
	}

	/**
	 * Build one index row.
	 *
	 * @param int   $attachment_id Attachment ID.
	 * @param array $filters       Filters.
	 * @return array
	 */
	private function build_index_row( $attachment_id, array $filters ) {
		$attachment = get_post( $attachment_id );

		if ( ! $attachment || ! wp_attachment_is_image( $attachment_id ) ) {
			return array();
		}

		$primary_post = $this->find_primary_related_post( $attachment_id, $filters['post_types'] ?? array() );
		if ( empty( $primary_post ) ) {
			return array();
		}

		$alt   = get_post_meta( $attachment_id, '_wp_attachment_image_alt', true );
		$error = get_post_meta( $attachment_id, 'smartypress_ai_alt_last_error', true );

		return array(
			'attachment_id'       => absint( $attachment_id ),
			'has_alt'             => '' !== trim( (string) $alt ) ? 1 : 0,
			'has_caption'         => '' !== trim( (string) $attachment->post_excerpt ) ? 1 : 0,
			'has_title'           => '' !== trim( (string) $attachment->post_title ) ? 1 : 0,
			'has_description'     => '' !== trim( (string) $attachment->post_content ) ? 1 : 0,
			'generated'           => get_post_meta( $attachment_id, 'smartypress_ai_alt_generated', true ) ? 1 : 0,
			'failed'              => ( '' !== trim( (string) $error ) && '' === trim( (string) $alt ) ) ? 1 : 0,
			'primary_post_id'     => absint( $primary_post->ID ),
			'primary_post_type'   => sanitize_key( $primary_post->post_type ),
			'indexed_at'          => current_time( 'mysql' ),
		);
	}

	/**
	 * Insert or update index row.
	 *
	 * @param array $row Row data.
	 * @return void
	 */
	private function upsert_row( array $row ) {
		global $wpdb;

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->replace(
			$table,
			$row,
			array( '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%s', '%s' )
		);
		wp_cache_delete( self::STATS_CACHE_KEY, self::CACHE_GROUP );
	}

	/**
	 * Find first related post within selected post types.
	 *
	 * @param int   $attachment_id Attachment ID.
	 * @param array $post_types    Post types.
	 * @return WP_Post|null
	 */
	private function find_primary_related_post( $attachment_id, array $post_types ) {
		$post_types = $this->sanitize_post_types( $post_types );
		if ( empty( $post_types ) ) {
			return null;
		}

		$parent_id = absint( get_post_field( 'post_parent', $attachment_id ) );
		if ( $parent_id ) {
			$parent = get_post( $parent_id );
			if ( $parent instanceof WP_Post && in_array( $parent->post_type, $post_types, true ) ) {
				return $parent;
			}
		}

		$featured = get_posts(
			array(
				'post_type'      => $post_types,
				'post_status'    => array( 'publish', 'draft', 'pending', 'future', 'private' ),
				'posts_per_page' => 1,
				'fields'         => 'all',
				'meta_query'     => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
					array(
						'key'   => '_thumbnail_id',
						'value' => absint( $attachment_id ),
					),
				),
			)
		);

		if ( ! empty( $featured ) ) {
			return $featured[0];
		}

		$url       = wp_get_attachment_url( $attachment_id );
		$filename  = $url ? wp_basename( $url ) : '';
		$needle_id = sprintf( '"id":%d', absint( $attachment_id ) );
		$queries   = array_values( array_filter( array( $needle_id, $filename ) ) );

		foreach ( $queries as $query ) {
			$matches = get_posts(
				array(
					'post_type'      => $post_types,
					'post_status'    => array( 'publish', 'draft', 'pending', 'future', 'private' ),
					'posts_per_page' => 1,
					's'              => $query,
				)
			);

			foreach ( $matches as $post ) {
				if ( false !== strpos( (string) $post->post_content, $query ) ) {
					return $post;
				}
			}
		}

		return null;
	}

	/**
	 * Query candidate attachment IDs with keyset pagination.
	 *
	 * @param array $filters   Filters.
	 * @param int   $last_id   Last attachment ID.
	 * @param int   $limit     Limit.
	 * @return int[]
	 */
	private function query_candidate_attachment_ids( array $filters, $last_id, $limit ) {
		global $wpdb;

		if ( '' !== $filters['search'] ) {
			$search = '%' . $wpdb->esc_like( $filters['search'] ) . '%';
			return array_map(
				'absint',
				$wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
					$wpdb->prepare(
						'SELECT ID FROM %i WHERE post_type = %s AND post_status = %s AND post_mime_type LIKE %s AND ID > %d AND (post_title LIKE %s OR post_name LIKE %s OR guid LIKE %s) ORDER BY ID ASC LIMIT %d',
						$wpdb->posts,
						'attachment',
						'inherit',
						'image/%',
						absint( $last_id ),
						$search,
						$search,
						$search,
						absint( $limit )
					)
				)
			);
		}

		return array_map(
			'absint',
			$wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare(
					'SELECT ID FROM %i WHERE post_type = %s AND post_status = %s AND post_mime_type LIKE %s AND ID > %d ORDER BY ID ASC LIMIT %d',
					$wpdb->posts,
					'attachment',
					'inherit',
					'image/%',
					absint( $last_id ),
					absint( $limit )
				)
			)
		);
	}

	/**
	 * Count candidate image attachments before post type filtering.
	 *
	 * @param array $filters Filters.
	 * @return int
	 */
	private function count_candidate_attachments( array $filters ) {
		global $wpdb;

		if ( '' !== $filters['search'] ) {
			$search = '%' . $wpdb->esc_like( $filters['search'] ) . '%';
			return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE post_type = %s AND post_status = %s AND post_mime_type LIKE %s AND (post_title LIKE %s OR post_name LIKE %s OR guid LIKE %s)',
					$wpdb->posts,
					'attachment',
					'inherit',
					'image/%',
					$search,
					$search,
					$search
				)
			);
		}

		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE post_type = %s AND post_status = %s AND post_mime_type LIKE %s',
				$wpdb->posts,
				'attachment',
				'inherit',
				'image/%'
			)
		);
	}

	/**
	 * Sanitize filters.
	 *
	 * @param array $filters Filters.
	 * @return array
	 */
	private function sanitize_filters( array $filters ) {
		$post_types = $this->sanitize_post_types( $filters['post_types'] ?? array() );

		return array(
			'filter'     => sanitize_key( $filters['filter'] ?? 'missing_alt' ),
			'search'     => sanitize_text_field( $filters['search'] ?? '' ),
			'post_types' => ! empty( $post_types ) ? $post_types : $this->get_default_post_types(),
		);
	}

	/**
	 * Sanitize post type list.
	 *
	 * @param array $post_types Post types.
	 * @return array
	 */
	private function sanitize_post_types( $post_types ) {
		$post_types = is_array( $post_types ) ? $post_types : array();
		$available  = array_keys( get_post_types( array( 'public' => true ), 'names' ) );
		$available  = array_diff( $available, array( 'attachment' ) );
		$sanitized  = array();

		foreach ( $post_types as $post_type ) {
			$post_type = sanitize_key( $post_type );
			if ( in_array( $post_type, $available, true ) ) {
				$sanitized[] = $post_type;
			}
		}

		return array_values( array_unique( $sanitized ) );
	}

	/**
	 * Empty stats shape.
	 *
	 * @return array
	 */
	private function empty_stats() {
		return array(
			'total'       => 0,
			'with_alt'    => 0,
			'without_alt' => 0,
			'generated'   => 0,
			'failed'      => 0,
		);
	}
}
