<?php
/**
 * Sitemap Query
 *
 * Optimized database queries for sitemap generation.
 *
 * @package SEOvaultSEO
 */

namespace SEOvaultSEO\Sitemap;

use SEOvaultSEO\Core\PostMeta;
use SEOvaultSEO\Utils\Db;

/**
 * Query helpers for sitemap data
 */
class Query {

	/**
	 * Get public post types that should appear in sitemaps.
	 *
	 * @return array
	 */
	public static function get_public_post_types() {
		$types = get_post_types(
			array(
				'public' => true,
			),
			'names'
		);
		unset( $types['attachment'] );
		return array_values( $types );
	}

	/**
	 * Get public taxonomies.
	 *
	 * @return array
	 */
	public static function get_public_taxonomies() {
		$taxonomies = get_taxonomies(
			array(
				'public' => true,
			),
			'names'
		);
		return array_values( $taxonomies );
	}

	/**
	 * Get post IDs for a post type page.
	 *
	 * @param string $post_type Post type.
	 * @param int    $page Page number.
	 * @param int    $per_page Per page.
	 * @return array
	 */
	public static function get_post_type_ids( $post_type, $page, $per_page ) {
		global $wpdb;

		$post_type = sanitize_key( $post_type );
		$page      = max( 1, (int) $page );
		$per_page  = max( 1, (int) $per_page );

		$noindex_ids = array_values( array_map( 'absint', self::get_noindexed_post_ids( $post_type ) ) );
		$noindex_in  = '';
		$query_args  = array( $post_type );
		if ( ! empty( $noindex_ids ) ) {
			$noindex_in = ' AND p.ID NOT IN (' . Db::prepare_in_list( $noindex_ids ) . ')';
			$query_args = array_merge( $query_args, $noindex_ids );
		}
		$query_args[] = $per_page;
		$query_args[] = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic NOT IN fragment contains placeholders only; values are passed to prepare().
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT p.ID FROM {$wpdb->posts} p
				WHERE p.post_type = %s
				AND p.post_status = 'publish'
				AND p.post_password = ''
				{$noindex_in}
				ORDER BY p.post_modified DESC
				LIMIT %d OFFSET %d",
				$query_args
			)
		);
		// phpcs:enable

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

	/**
	 * Get post count for a post type.
	 *
	 * @param string $post_type Post type.
	 * @return int
	 */
	public static function get_post_type_count( $post_type ) {
		global $wpdb;

		$post_type   = sanitize_key( $post_type );
		$noindex_ids = array_values( array_map( 'absint', self::get_noindexed_post_ids( $post_type ) ) );
		$noindex_in  = '';
		$query_args  = array( $post_type );
		if ( ! empty( $noindex_ids ) ) {
			$noindex_in = ' AND ID NOT IN (' . Db::prepare_in_list( $noindex_ids ) . ')';
			$query_args = array_merge( $query_args, $noindex_ids );
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic NOT IN fragment contains placeholders only; values are passed to prepare().
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->posts}
				WHERE post_type = %s
				AND post_status = 'publish'
				AND post_password = ''
				{$noindex_in}",
				$query_args
			)
		);
		// phpcs:enable

		return $count;
	}

	/**
	 * Get term IDs for a taxonomy page.
	 *
	 * @param string $taxonomy Taxonomy.
	 * @param int    $page Page number.
	 * @param int    $per_page Per page.
	 * @return array
	 */
	public static function get_term_ids( $taxonomy, $page, $per_page ) {
		global $wpdb;

		$taxonomy = sanitize_key( $taxonomy );
		$page     = max( 1, (int) $page );
		$per_page = max( 1, (int) $per_page );

		$noindex_ids = array_values( array_map( 'absint', self::get_noindexed_term_ids( $taxonomy ) ) );
		$noindex_in  = '';
		$query_args  = array( $taxonomy );
		if ( ! empty( $noindex_ids ) ) {
			$noindex_in = ' AND t.term_id NOT IN (' . Db::prepare_in_list( $noindex_ids ) . ')';
			$query_args = array_merge( $query_args, $noindex_ids );
		}
		$query_args[] = $per_page;
		$query_args[] = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic NOT IN fragment contains placeholders only; values are passed to prepare().
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT t.term_id FROM {$wpdb->terms} t
				INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
				WHERE tt.taxonomy = %s
				{$noindex_in}
				ORDER BY t.term_id ASC
				LIMIT %d OFFSET %d",
				$query_args
			)
		);
		// phpcs:enable

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

	/**
	 * Get term count for a taxonomy.
	 *
	 * @param string $taxonomy Taxonomy.
	 * @return int
	 */
	public static function get_term_count( $taxonomy ) {
		global $wpdb;

		$taxonomy    = sanitize_key( $taxonomy );
		$noindex_ids = array_values( array_map( 'absint', self::get_noindexed_term_ids( $taxonomy ) ) );
		$noindex_in  = '';
		$query_args  = array( $taxonomy );
		if ( ! empty( $noindex_ids ) ) {
			$noindex_in = ' AND t.term_id NOT IN (' . Db::prepare_in_list( $noindex_ids ) . ')';
			$query_args = array_merge( $query_args, $noindex_ids );
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic NOT IN fragment contains placeholders only; values are passed to prepare().
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->terms} t
				INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
				WHERE tt.taxonomy = %s
				{$noindex_in}",
				$query_args
			)
		);
		// phpcs:enable

		return $count;
	}

	/**
	 * Get user IDs for a page.
	 *
	 * @param int $page Page number.
	 * @param int $per_page Per page.
	 * @return array
	 */
	public static function get_user_ids( $page, $per_page ) {
		global $wpdb;

		$page     = max( 1, (int) $page );
		$per_page = max( 1, (int) $per_page );
		$offset   = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT p.post_author FROM {$wpdb->posts} p
				WHERE p.post_status = 'publish'
				AND p.post_type != 'attachment'
				ORDER BY p.post_author ASC
				LIMIT %d, %d",
				$offset,
				$per_page
			)
		);
		// phpcs:enable

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

	/**
	 * Get user count (authors with published posts).
	 *
	 * @return int
	 */
	public static function get_user_count() {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			"SELECT COUNT(DISTINCT post_author) FROM {$wpdb->posts}
			WHERE post_status = 'publish'
			AND post_type != 'attachment'"
		);
		// phpcs:enable

		return $count;
	}

	/**
	 * Check if a post is indexed.
	 *
	 * @param int $post_id Post ID.
	 * @return bool
	 */
	public static function is_post_indexed( $post_id ) {
		$post_id = (int) $post_id;
		if ( $post_id <= 0 ) {
			return false;
		}

		$noindex = get_post_meta( $post_id, '_seovault_robots_noindex', true );
		if ( '' === $noindex || false === $noindex ) {
			return true;
		}
		return ! in_array( $noindex, array( '1', 1, true ), true );
	}

	/**
	 * Check if a term is indexed.
	 *
	 * @param int $term_id Term ID.
	 * @return bool
	 */
	public static function is_term_indexed( $term_id ) {
		$term_id = (int) $term_id;
		if ( $term_id <= 0 ) {
			return false;
		}

		$noindex = get_term_meta( $term_id, '_seovault_robots_noindex', true );
		if ( '' === $noindex || false === $noindex ) {
			return true;
		}
		return ! in_array( $noindex, array( '1', 1, true ), true );
	}

	/**
	 * Get noindexed post IDs for a post type.
	 *
	 * @param string $post_type Post type.
	 * @return array
	 */
	public static function get_noindexed_post_ids( $post_type ) {
		global $wpdb;

		$post_type = sanitize_key( $post_type );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT pm.post_id FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE pm.meta_key = %s
				AND pm.meta_value = '1'
				AND p.post_type = %s
				AND p.post_status = 'publish'",
				'_seovault_robots_noindex',
				$post_type
			)
		);
		// phpcs:enable

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

	/**
	 * Get noindexed term IDs for a taxonomy.
	 *
	 * @param string $taxonomy Taxonomy.
	 * @return array
	 */
	public static function get_noindexed_term_ids( $taxonomy ) {
		global $wpdb;

		$taxonomy = sanitize_key( $taxonomy );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT tm.term_id FROM {$wpdb->termmeta} tm
				INNER JOIN {$wpdb->term_taxonomy} tt ON tm.term_id = tt.term_id
				WHERE tm.meta_key = %s
				AND tm.meta_value = '1'
				AND tt.taxonomy = %s",
				'_seovault_robots_noindex',
				$taxonomy
			)
		);
		// phpcs:enable

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